1 package body INV_DIAG_TDUMP as
2 /* $Header: INVDT01B.pls 120.0.12000000.1 2007/06/22 01:24:03 musinha noship $ */
3
4 PROCEDURE init is
5 BEGIN
6 null;
7 END init;
8
9 PROCEDURE cleanup IS
10 BEGIN
11 null;
12 NULL;
13 END cleanup;
14
15 PROCEDURE runtest(inputs IN JTF_DIAG_INPUTTBL,
16 report OUT NOCOPY JTF_DIAG_REPORT,
17 reportClob OUT NOCOPY CLOB) IS
18 reportStr LONG; -- REPORT
19 sqltxt VARCHAR2(9999); -- SQL select statement
20 c_username VARCHAR2(50); -- accept input for username
21 statusStr VARCHAR2(50); -- SUCCESS or FAILURE
22 errStr VARCHAR2(4000); -- error message
23 fixInfo VARCHAR2(4000); -- fix tip
24 isFatal VARCHAR2(50); -- TRUE or FALSE
25 dummy_num NUMBER;
26 row_limit NUMBER;
27 l_txn_id NUMBER;
28 l_org_id NUMBER;
29 l_item_id NUMBER;
30 l_sn VARCHAR2(30);
31 l_lot VARCHAR2(30);
32 l_script varchar2(30);
33 l_proc_flag varchar2(1);
34 BEGIN
35 JTF_DIAGNOSTIC_ADAPTUTIL.setUpVars;
36 JTF_DIAGNOSTIC_ADAPTUTIL.addStringToReport('@html');
37 JTF_DIAGNOSTIC_COREAPI.insert_style_sheet;
38 -- accept input
39 l_org_id := JTF_DIAGNOSTIC_ADAPTUTIL.getInputValue('OrgId',inputs);
40 l_txn_id := JTF_DIAGNOSTIC_ADAPTUTIL.getInputValue('TransactionId',inputs);
41 l_item_id :=JTF_DIAGNOSTIC_ADAPTUTIL.getInputValue('ItemId',inputs);
42 l_script :=JTF_DIAGNOSTIC_ADAPTUTIL.getInputValue('TableName',inputs);
43 l_proc_flag :=JTF_DIAGNOSTIC_ADAPTUTIL.getInputValue('ProcFlag',inputs);
44 l_sn := JTF_DIAGNOSTIC_ADAPTUTIL.getInputValue('SerialNum',inputs);
45 l_lot := JTF_DIAGNOSTIC_ADAPTUTIL.getInputValue('LotNum',inputs);
46
47 row_limit :=INV_DIAG_GRP.g_max_row;
48
49 JTF_DIAGNOSTIC_ADAPTUTIL.addStringToReport('OrgID input :'||l_org_id||' Table name '||l_script);
50 JTF_DIAGNOSTIC_COREAPI.BRPrint;
51 if l_script = 'mmt' then
52 if l_txn_id is not null then
53 sqltxt := 'select * from MTL_MATERIAL_TRANSACTIONS where transaction_id ='||l_txn_id;
54 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, 'Dump of MMT for transaction '||l_txn_id);
55 statusStr := 'SUCCESS';
56 elsif l_item_id is not null then
57 sqltxt := 'select * from MTL_MATERIAL_TRANSACTIONS where inventory_item_id ='||l_item_id||
58 ' order by transaction_id';
59 sqltxt := 'select * from ('||sqltxt||') WHERE ROWNUM <= '||row_limit;
60
61 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, 'Dump of MTL_MATERIAL_TRANSACTIONS for item id '||l_item_id);
62 statusStr := 'SUCCESS';
63 else
64 JTF_DIAGNOSTIC_COREAPI.ActionErrorPrint('Parameter input is requred to get dump of MTL_MATERIAL_TRANSACTIONS!');
65 statusStr := 'FAILURE';
66 errStr := 'This test failed as: no input';
67 fixInfo := 'Please enter at least one of the following parameters: TransactionId or ItemId';
68 isFatal := 'SUCCESS';
69 end if;
70 elsif l_script = 'mmtt' then
71 if l_txn_id is not null or l_item_id is not null or l_proc_flag = 'E' or l_org_id is not null then
72 sqltxt := 'select * from mtl_material_transactions_temp where 1 = 1';
73 reportStr := ' For';
74 if l_txn_id is not null then
75 sqltxt := sqltxt||' and transaction_temp_id = '||l_txn_id;
76 reportStr := reportStr||' - transaction_temp_id '||l_txn_id;
77 end if;
78 if l_item_id is not null then
79 sqltxt := sqltxt||' and inventory_item_id = '||l_item_id;
80 reportStr := ' - Inventory Item Id '||l_item_id;
81 end if;
82 if l_proc_flag ='E' then
83 sqltxt := sqltxt||' and process_flag =''E'' ';
84 reportStr := ' - Errored';
85 end if;
86 if l_org_id is not null then
87 sqltxt := sqltxt||' and organization_id = '||l_org_id;
88 reportStr := ' - Organization id'||l_org_id;
89 end if;
90
91 sqltxt := 'select * from ('||sqltxt||') WHERE ROWNUM <= '||row_limit;
92
93 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, 'Dump of MTL_MATERIAL_TRANSACTIONS_TEMP'||reportStr||' Transactions');
94 else
95
96 sqltxt := 'select * from mtl_material_transactions_temp where rownum <= '||row_limit;
97 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, 'Dump of MTL_MATERIAL_TRANSACTIONS_TEMP for All Pending Transactions ');
98 statusStr := 'SUCCESS';
99 end if;
100 elsif l_script = 'mti' then
101 if l_txn_id is not null or l_item_id is not null or l_proc_flag = 'E' or l_org_id is not null then
102 sqltxt := 'select * from mtl_transactions_interface where 1 = 1';
103 reportStr := ' For';
104 if l_txn_id is not null then
105 sqltxt := sqltxt||' and transaction_header_id = '||l_txn_id;
106 reportStr := reportStr||' - transaction_header_id '||l_txn_id;
107 end if;
108 if l_item_id is not null then
109 sqltxt := sqltxt||' and inventory_item_id = '||l_item_id;
110 reportStr := reportStr||' - Inventory Item Id '||l_item_id;
111 end if;
112 if l_proc_flag ='E' then
113 sqltxt := sqltxt||' and process_flag =3 ';
114 reportStr := reportStr||' - Errored';
115 end if;
116 if l_org_id is not null then
117 sqltxt := sqltxt||' and organization_id = '||l_org_id;
118 reportStr := reportStr||' - Organization id'||l_org_id;
119 end if;
120
121 sqltxt := 'select * from ('||sqltxt||') WHERE ROWNUM <= '||row_limit;
122 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, 'Dump of MTL_TRANSACTIONS_INTERFACE'||reportStr||' Transactions');
123 else
124 sqltxt := 'select * from mtl_transactions_interface where rownum <= '||row_limit;
125 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, 'Dump of MTL_TRANSACTIONS_INTERFACE for All Pending Transactions ');
126 statusStr := 'SUCCESS';
127 end if;
128 elsif l_script = 'mut' then
129 if l_txn_id is not null or l_item_id is not null or l_proc_flag = 'E' or l_org_id is not null
130 or l_sn is not null then
131 sqltxt := 'select * from MTL_UNIT_TRANSACTIONS where 1 = 1';
132 reportStr := ' For';
133 if l_txn_id is not null then
134 sqltxt := sqltxt||' and transaction_id = '||l_txn_id;
135 reportStr := reportStr||' - transaction_id '||l_txn_id;
136 end if;
137 if l_item_id is not null then
138 sqltxt := sqltxt||' and inventory_item_id = '||l_item_id;
139 reportStr := reportStr||' - Inventory Item Id '||l_item_id;
140 end if;
141 if l_org_id is not null then
142 sqltxt := sqltxt||' and organization_id = '||l_org_id;
143 reportStr := reportStr||' - Organization id'||l_org_id;
144 end if;
145 if l_sn is not null then
146 sqltxt := sqltxt||' and serial_number = '''||l_sn||'''';
147 reportStr := reportStr||' - Serial Number '||l_sn;
148 end if;
149
150 sqltxt := 'select * from ('||sqltxt||') WHERE ROWNUM <= '||row_limit;
151 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, 'Dump of MTL_UNIT_TRANSACTIONS'||reportStr||' Transactions');
152 else
153 JTF_DIAGNOSTIC_COREAPI.ActionErrorPrint('Parameter input is requred to get dump of MTL_UNIT_TRANSACTIONS!');
154 statusStr := 'FAILURE';
155 errStr := 'This test failed as: no input';
156 fixInfo := 'Please enter at least one of the following parameters Org, Item, TransactionId, Serial Number';
157 isFatal := 'SUCCESS';
158 end if;
159 elsif l_script = 'mtln' then
160 if l_txn_id is not null or l_item_id is not null or l_proc_flag = 'E' or l_org_id is not null
161 or l_lot is not null then
162 sqltxt := 'select * from MTL_TRANSACTION_LOT_NUMBERS where 1 = 1';
163 reportStr := ' For';
164 if l_txn_id is not null then
165 sqltxt := sqltxt||' and transaction_id = '||l_txn_id;
166 reportStr := reportStr||' - transaction_id '||l_txn_id;
167 end if;
168 if l_item_id is not null then
169 sqltxt := sqltxt||' and inventory_item_id = '||l_item_id;
170 reportStr := reportStr||' - Inventory Item Id '||l_item_id;
171 end if;
172 if l_org_id is not null then
173 sqltxt := sqltxt||' and organization_id = '||l_org_id;
174 reportStr := reportStr||' - Organization id'||l_org_id;
175 end if;
176 if l_lot is not null then
177 sqltxt := sqltxt||' and lot_number = '''||l_lot||'''';
178 reportStr := reportStr||' - Lot '||l_lot;
179 end if;
180
181 sqltxt := 'select * from ('||sqltxt||') WHERE ROWNUM <= '||row_limit;
182 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, 'Dump of MTL_TRANSACTION_LOT_NUMBERS'||reportStr||' Transactions');
183 else
184 JTF_DIAGNOSTIC_COREAPI.ActionErrorPrint('Parameter input is requred to get dump of MTL_TRANSACTION_LOT_NUMBERS!');
185 statusStr := 'FAILURE';
186 errStr := 'This test failed as: no input';
187 fixInfo := 'Please enter at least one of the following parameters Org, Item, TransactionId, Lot Number';
188 isFatal := 'SUCCESS';
189 end if;
190 elsif l_script ='msnt' then
191 if l_txn_id is not null or l_item_id is not null or l_proc_flag = 'E' or l_org_id is not null
192 or l_sn is not null then
193 sqltxt := 'select * from MTL_SERIAL_NUMBERS_TEMP where 1 = 1';
194 reportStr := ' For';
195 if l_txn_id is not null then
196 sqltxt := sqltxt||' and transaction_temp_id = '||l_txn_id;
197 reportStr := reportStr||' - transaction_temp_id '||l_txn_id;
198 end if;
199 if l_sn is not null then
200 sqltxt := sqltxt||' and ( fm_serial_number = '''||l_sn||''''||
201 ' or to_serial_number = '''||l_sn||''')';
202 reportStr := reportStr||' - Serial Number '||l_sn;
203 end if;
204 sqltxt := 'select * from ('||sqltxt||') WHERE ROWNUM <= '||row_limit;
205 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, 'Dump of MTL_SERIAL_NUMBERS_TEMP'||reportStr||' Transactions');
206
207 else
208 sqltxt := 'select * from MTL_SERIAL_NUMBERS_TEMP where rownum <= '||row_limit;
209 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, 'Dump of MTL_SERIAL_NUMBERS_TEMP for All Pending Transactions ');
210 statusStr := 'SUCCESS';
211 end if;
212 elsif l_script ='mtlt' then
213 if l_txn_id is not null or l_item_id is not null or l_proc_flag = 'E' or l_org_id is not null
214 or l_lot is not null then
215 sqltxt := 'select * from MTL_TRANSACTION_LOTS_TEMP where 1 = 1';
216 reportStr := ' For';
217 if l_txn_id is not null then
218 sqltxt := sqltxt||' and transaction_temp_id = '||l_txn_id;
219 reportStr := reportStr||' - transaction_temp_id '||l_txn_id;
220 end if;
221 if l_lot is not null then
222 sqltxt := sqltxt||' and lot_number = '''||l_lot||'''';
223 reportStr := reportStr||' - Lot '||l_lot;
224 end if;
225 sqltxt := 'select * from ('||sqltxt||') WHERE ROWNUM <= '||row_limit;
226 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, 'Dump of MTL_TRANSACTION_LOTS_TEMP'||reportStr||' Transactions');
227
228 else
229 sqltxt := 'select * from MTL_SERIAL_NUMBERS_TEMP where rownum <= '||row_limit;
230 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, 'Dump of MTL_TRANSACTION_LOTS_TEMP for All Pending Transactions ');
231 statusStr := 'SUCCESS';
232 end if;
233 end if;
234
235 -- construct report
236 report := JTF_DIAGNOSTIC_ADAPTUTIL.constructReport(statusStr,errStr,fixInfo,isFatal);
237 reportClob := JTF_DIAGNOSTIC_ADAPTUTIL.getReportClob;
238
239 END runTest;
240
241 PROCEDURE getComponentName(name OUT NOCOPY VARCHAR2) IS
242 BEGIN
243 name := 'Dump of Transaction Tables';
244 END getComponentName;
245
246 PROCEDURE getTestDesc(descStr OUT NOCOPY VARCHAR2) IS
247 BEGIN
248 descStr := 'Get Dump from Transaction Tables';
249 END getTestDesc;
250
251 PROCEDURE getTestName(name OUT NOCOPY VARCHAR2) IS
252 BEGIN
253 name := 'Dump of Transaction Tables';
254 END getTestName;
255
256 PROCEDURE getDependencies (package_names OUT NOCOPY JTF_DIAG_DEPENDTBL) IS
257 tempDependencies JTF_DIAG_DEPENDTBL;
258
259 BEGIN
260 package_names := JTF_DIAGNOSTIC_ADAPTUTIL.initDependencyTable;
261 END getDependencies;
262
263 PROCEDURE isDependencyPipelined (str OUT NOCOPY VARCHAR2) IS
264 BEGIN
265 str := 'FALSE';
266 END isDependencyPipelined;
267
268
269 PROCEDURE getOutputValues(outputValues OUT NOCOPY JTF_DIAG_OUTPUTTBL) IS
270 tempOutput JTF_DIAG_OUTPUTTBL;
271 BEGIN
272 tempOutput := JTF_DIAGNOSTIC_ADAPTUTIL.initOutputTable;
273 outputValues := tempOutput;
274 EXCEPTION
275 when others then
276 outputValues := JTF_DIAGNOSTIC_ADAPTUTIL.initOutputTable;
277 END getOutputValues;
278
279
280 PROCEDURE getDefaultTestParams(defaultInputValues OUT NOCOPY JTF_DIAG_INPUTTBL) IS
281 tempInput JTF_DIAG_INPUTTBL;
282 BEGIN
283 tempInput := JTF_DIAGNOSTIC_ADAPTUTIL.initinputtable;
284 tempInput := JTF_DIAGNOSTIC_ADAPTUTIL.addInput(tempInput,'OrgId','LOV-oracle.apps.inv.diag.lov.OrganizationLov');
285 tempInput := JTF_DIAGNOSTIC_ADAPTUTIL.addInput(tempInput,'TableName','LOV-oracle.apps.inv.diag.lov.TxnTablesLov');
286 tempInput := JTF_DIAGNOSTIC_ADAPTUTIL.addInput(tempInput,'TransactionId','');
287 tempInput := JTF_DIAGNOSTIC_ADAPTUTIL.addInput(tempInput,'ItemId','LOV-oracle.apps.inv.diag.lov.ItemLov');
288 tempInput := JTF_DIAGNOSTIC_ADAPTUTIL.addInput(tempInput,'ProcFlag','LOV-oracle.apps.inv.diag.lov.ErroredAllLov');
289 tempInput := JTF_DIAGNOSTIC_ADAPTUTIL.addInput(tempInput,'SerialNum','LOV-oracle.apps.inv.diag.lov.SerialLov');
290 tempInput := JTF_DIAGNOSTIC_ADAPTUTIL.addInput(tempInput,'LotNum','LOV-oracle.apps.inv.diag.lov.LotLov');
291 defaultInputValues := tempInput;
292 EXCEPTION
293 when others then
294 defaultInputValues := JTF_DIAGNOSTIC_ADAPTUTIL.initinputtable;
295 END getDefaultTestParams;
296
297 Function getTestMode return INTEGER IS
298 BEGIN
299 return JTF_DIAGNOSTIC_ADAPTUTIL.ADVANCED_MODE;
300
301 END getTestMode;
302
303 END;
[Home] [Help]