[Home] [Help]
PACKAGE BODY: APPS.INV_DIAG_MMTT
Source
1 package body INV_DIAG_MMTT as
2 /* $Header: INVDT02B.pls 120.0.12000000.1 2007/06/22 01:25:57 musinha noship $ */
3 PROCEDURE init is
4 BEGIN
5 --fnd_file.put_line(fnd_file.log,'@@@ diag_msn init'||to_char(SYSDATE, 'DD-MON-YYYY HH24:MI:SS'));
6 null;
7 END init;
8
9 PROCEDURE cleanup IS
10 BEGIN
11 -- test writer could insert special cleanup code here
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 l_msg varchar2(1000);
26 dummy_num NUMBER;
27 row_limit NUMBER;
28 l_txn_id NUMBER;
29 l_org_id NUMBER;
30 l_acct_period_id NUMBER;
31 l_acct_period varchar2(15);
32 l_proc_flag varchar2(1);
33
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_acct_period :=JTF_DIAGNOSTIC_ADAPTUTIL.getInputValue('AcctPeriod',inputs);
41 l_proc_flag :=JTF_DIAGNOSTIC_ADAPTUTIL.getInputValue('ErrorFlag',inputs);
42 row_limit :=INV_DIAG_GRP.g_max_row;
43
44 reportStr :='';
45 if l_org_id is null and l_acct_period is null then
46 reportStr := ' For All Organizations and Acct Period';
47 elsif l_org_id is not null and l_acct_period is null then
48 reportStr := ' For All Acct Period';
49 end if;
50
51 if l_proc_flag = 'A' then
52 l_proc_flag := null;
53 end if;
54
55 if l_proc_flag is not null then
56 reportStr := reportStr||' with process flag '||l_proc_flag;
57 end if;
58
59
60 if l_org_id is not null and l_acct_period is not null then
61 begin
62 SELECT acct_period_id
63 into l_acct_period_id
64 FROM org_acct_periods
65 WHERE organization_id = l_org_id
66 AND period_name = l_acct_period;
67 exception
68 when others then
69 JTF_DIAGNOSTIC_COREAPI.ActionErrorPrint('Invalid Input Parameters. ');
70 errStr := 'Invalid Account period '||SQLCODE||' '||substrb(sqlerrm,1,1000);
71 fixInfo := 'Enter a valid account period';
72 statusStr := 'FAILURE';
73 isFatal := 'SUCCESS';
74 goto l_test_end;
75 end;
76 end if;
77
78 JTF_DIAGNOSTIC_ADAPTUTIL.addStringToReport('OrgID input :'||l_org_id||' Account Period: '||l_acct_period||'('||l_acct_period_id||')');
79 JTF_DIAGNOSTIC_COREAPI.BRPrint;
80
81 sqltxt := 'SELECT fa.application_name "Application Name" '||
82 ' , fa.application_short_name "Application|Shortname" '||
83 ' , fcp.concurrent_processor_name "Name" '||
84 ' , fcq.user_concurrent_queue_name "Manager" '||
85 ' , NVL( fcq.target_node,''n/a'') "Node" '||
86 ' , fcq.running_processes "Actual" '||
87 ' , fcq.max_processes "Target" '||
88 ' FROM fnd_concurrent_queues_vl fcq '||
89 ' , fnd_application_vl fa '||
90 ' , fnd_concurrent_processors fcp '||
91 'WHERE fa.application_id = fcq.application_id '||
92 ' AND fcq.application_id = fcp.application_id '||
93 ' AND fcq.concurrent_processor_id = fcp.concurrent_processor_id '||
94 ' AND fa.application_short_name IN ( ''INV'' ) '||
95 'ORDER BY fcp.application_id DESC '||
96 ', fcp.concurrent_processor_id '||
97 ', fcp.concurrent_processor_name';
98
99 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, 'Concurrent Managers related to Inventory');
100 statusStr := 'SUCCESS';
101 isFatal := 'FALSE';
102
103
104 sqltxt :='SELECT DISTINCT period_name "Period|Name" '||
105 ' , oap.acct_period_id "Period|Id" '||
106 ' , mp.organization_code "Organization|Code" '||
107 ' , mmtt.organization_id "Organization|Id" '||
108 ' , TO_CHAR( period_start_date, ''DD-MON-YYYY'' ) "Start Date" '||
109 ' , TO_CHAR( period_close_date, ''DD-MON-YYYY'' ) "Close Date" '||
110 ' , TO_CHAR( schedule_close_date, ''DD-MON-YYYY'' ) "Scheduled |Close Date" '||
111 ' , open_flag "Open" '||
112 ' , description "Description" '||
113 ' , period_set_name "GL Period Set|Name" '||
114 ' , period_name "GL Period|Name" '||
115 ' , period_year "GL Period|Year" '||
116 ' FROM mtl_material_transactions_temp mmtt, mtl_parameters mp '||
117 ' , org_acct_periods oap '||
118 ' WHERE NVL( mmtt.transaction_status,1 ) != 2 '||
119 ' AND mmtt.organization_id=mp.organization_id(+) '||
120 ' AND mmtt.acct_period_id=oap.acct_period_id(+)';
121 if l_org_id is not null then
122 sqltxt :=sqltxt||' and mmtt.organization_id = '||l_org_id;
123 end if;
124
125 if l_acct_period_id is not null then
126 sqltxt := sqltxt||' and mmtt.acct_period_id = '||l_acct_period_id;
127 end if;
128
129 sqltxt := sqltxt||' ORDER BY mp.organization_code, oap.acct_period_id ';
130
131 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, 'Period Information ');
132 statusStr := 'SUCCESS';
133 isFatal := 'FALSE';
134
135 sqltxt := 'SELECT ';
136 if l_org_id is null then
137 sqltxt :=sqltxt||'organization_code || '' ('' ||mmtt.organization_id|| '')'' "Organization|Code (Id)", ';
138 end if;
139
140 if l_acct_period_id is null then
141 sqltxt :=sqltxt||'period_name "Period|Name", mmtt.acct_period_id "Period|Id",';
142 end if;
143
144 sqltxt := sqltxt||' transaction_header_id "Txn|Header Id" '||
145 ',transaction_temp_id "Txn|Temp Id" '||
146 ',TO_CHAR( transaction_date, ''DD-MON-RR'' ) "Txn|Date" '||
147 ',DECODE(transaction_mode,1,''Online'' '||
148 ' ,2,''Concurrent'' '||
149 ' ,3,''Background'' '||
150 ' ,transaction_mode) '||
151 ' ||'' ('' ||transaction_mode|| '')'' "Transaction|Mode" '||
152 ',DECODE(transaction_status,1,''Pending'' '||
153 ' ,2,''Allocated'' '||
154 ' ,3,''Pending'' '||
155 ' ,NULL,''Pending'' '||
156 ' ,transaction_status) '||
157 ' ||'' ('' ||transaction_status|| '')'' "Transaction|Status" '||
158 ',process_flag "Process|Flag" '||
159 ',lock_flag "Lock|Flag" '||
160 ',error_code '||
161 ',error_explanation '||
162 ',TO_CHAR( mmtt.last_update_date, ''DD-MON-RR HH24:MI'') "Last Updated" '||
163 ',mif.item_number '||
164 '||'' (''||mmtt.inventory_item_id||'')'' "Item (Id)" '||
165 ',item_description "Item Description" '||
166 ',revision "Rev" '||
167 ',lot_number "Lot" '||
168 ',serial_number "Serial|Number" '||
169 ',mmtt.cost_group_id "Cost|Group Id" '||
170 ',mmtt.subinventory_code "Subinv" '||
171 ',mil.description '||
172 '||'' (''||mmtt.locator_id||'') '' "Stock|Locator (Id)" '||
173 ',transfer_subinventory "Transfer|Subinv" '||
174 ',transfer_to_location "Transfer|Location" '||
175 ',transaction_quantity "Txn Qty" '||
176 ',primary_quantity "Primary|Qty" '||
177 ',transaction_uom "Txn|UoM" '||
178 ',mtt.transaction_type_name '||
179 '||'' (''||mmtt.transaction_type_id||'')'' "Txn Type (Id)" '||
180 ',ml.meaning '||
181 '||'' (''||mmtt.transaction_action_id||'')'' "Txn Action Type (Id)" '||
182 'FROM mtl_material_transactions_temp mmtt '||
183 ',mtl_transaction_types mtt '||
184 ',mtl_item_flexfields mif '||
185 ',mfg_lookups ml '||
186 ',mtl_item_locations_kfv mil';
187
188 if l_org_id is null then
189 sqltxt :=sqltxt||' ,mtl_parameters mp ';
190 end if;
191 if l_acct_period_id is null then
192 sqltxt :=sqltxt||' ,org_acct_periods oap ';
193 end if;
194
195 sqltxt := sqltxt||' WHERE NVL(transaction_status,1)!=2 '||
196 ' AND mmtt.transaction_type_id=mtt.transaction_type_id '||
197 ' AND mmtt.organization_id=mif.organization_id(+) '||
198 ' AND mmtt.inventory_item_id=mif.inventory_item_id(+) '||
199 ' AND mmtt.transaction_action_id=ml.lookup_code '||
200 ' AND ml.lookup_type=''MTL_TRANSACTION_ACTION'' '||
201 ' AND mmtt.locator_id=mil.inventory_location_id(+) '||
202 ' AND mmtt.organization_id=mil.organization_id(+)';
203 if l_org_id is null then
204 sqltxt :=sqltxt||' AND mmtt.organization_id=mp.organization_id(+) ';
205 else
206 sqltxt :=sqltxt||' and mmtt.organization_id = '||l_org_id;
207 end if;
208
209 if l_acct_period_id is not null then
210 sqltxt := sqltxt||' and mmtt.acct_period_id = '||l_acct_period_id;
211 else
212 sqltxt := sqltxt||' AND mmtt.acct_period_id=oap.acct_period_id(+) ';
213 end if;
214
215 if l_proc_flag is not null then
216 sqltxt := sqltxt||' AND process_flag= '''||l_proc_flag||''' ';
217 end if;
218
219 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, 'Pending Material Transactions '||reportStr);
220 statusStr := 'SUCCESS';
221 isFatal := 'FALSE';
222
223
224 sqltxt := 'SELECT mmtt.transaction_temp_id "Txn|Temp Id" '||
225 ' , mmtt.transaction_quantity "Txn Qty" '||
226 ' , mmtt.primary_quantity "Primary|Txn Qty" '||
227 ' , mmtt.transaction_uom "Txn UoM" '||
228 ' , mmtt.subinventory_code "SubInventory" '||
229 ' , mmtt.error_code "Error|Code" '||
230 ' , mmtt.error_explanation "Error|Explanation" '||
231 ' , mmtt.item_description "Item Description" '||
232 ' FROM mtl_material_transactions_temp mmtt '||
233 'WHERE ( ABS( transaction_quantity )*100000 <= 1 '||
234 ' OR ABS( primary_quantity )* 100000 <= 1 )';
235
236 if l_org_id is not null then
237 sqltxt :=sqltxt||' and mmtt.organization_id = '||l_org_id;
238 end if;
239
240 if l_acct_period_id is not null then
241 sqltxt := sqltxt||' and mmtt.acct_period_id = '||l_acct_period_id;
242 end if;
243
244 if l_proc_flag is not null then
245 sqltxt := sqltxt||' AND process_flag= '''||l_proc_flag||''' ';
246 end if;
247
248 sqltxt :=sqltxt||' ORDER BY transaction_temp_id';
249
250 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, 'Pending Txns with Transaction or Primary Quantity Below Minimum'||reportStr);
251 statusStr := 'SUCCESS';
252 isFatal := 'FALSE';
253
254 sqltxt := 'SELECT mmtt.transaction_temp_id "Txn|Temp Id" '||
255 ' , mmtt.item_description "Item Description" '||
256 ' , mmtt.inventory_item_id "Inventory|Item Id" '||
257 ' FROM mtl_material_transactions_temp mmtt '||
258 ' WHERE NVL( mmtt.transaction_status, 1 ) != 2 '||
259 ' AND mmtt.item_description like ''% '' ';
260
261 if l_org_id is not null then
262 sqltxt :=sqltxt||' and mmtt.organization_id = '||l_org_id;
263 end if;
264
265 if l_acct_period_id is not null then
266 sqltxt := sqltxt||' and mmtt.acct_period_id = '||l_acct_period_id;
267 end if;
268
269 if l_proc_flag is not null then
270 sqltxt := sqltxt||' AND process_flag= '''||l_proc_flag||''' ';
271 end if;
272
273 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, 'Items with Trailing Spaces in Description'||reportStr);
274 statusStr := 'SUCCESS';
275 isFatal := 'FALSE';
276
277 sqltxt := 'SELECT mif.item_number "Item" '||
278 ' , moq.inventory_item_id "Item Id" '||
279 ' , SUM( moq.transaction_quantity ) "Txn Qty" '||
280 ' , moq.subinventory_code "Subinv" '||
281 ' , moq.locator_id "Locator Id" '||
282 ' , mil.concatenated_segments "Locator" '||
283 ' , mil.description "Locator Desc" '||
284 ' , moq.revision "Revision" '||
285 ' , moq.lot_number "Lot Number" '||
286 ' FROM mtl_onhand_quantities_detail moq , mtl_item_flexfields mif '||
287 ' , mtl_item_locations_kfv mil '||
288 'WHERE moq.inventory_item_id = mif.inventory_item_id(+) '||
289 ' AND moq.organization_id = mif.organization_id(+) '||
290 ' AND moq.organization_id = mil.organization_id(+) '||
291 ' AND moq.locator_id = mil.inventory_location_id(+) ';
292 if l_org_id is not null then
293 sqltxt :=sqltxt||' and moq.organization_id = '||l_org_id;
294 end if;
295
296 sqltxt :=sqltxt||' AND moq.inventory_item_id '||
297 ' IN ( SELECT DISTINCT mmtt.inventory_item_id '||
298 ' FROM mtl_material_transactions_temp mmtt '||
299 ' WHERE NVL( mmtt.transaction_status, 1 ) !=2 ';
300
301 if l_org_id is not null then
302 sqltxt :=sqltxt||' and mmtt.organization_id = '||l_org_id;
303 end if;
304
305 if l_acct_period_id is not null then
306 sqltxt := sqltxt||' and mmtt.acct_period_id = '||l_acct_period_id;
307 end if;
308
309 if l_proc_flag is not null then
310 sqltxt := sqltxt||' AND mmtt.process_flag= '''||l_proc_flag||''' ';
311 end if;
312
313 sqltxt := sqltxt||')'||
314 'GROUP BY mif.item_number, moq.inventory_item_id '||
315 ' , moq.subinventory_code, moq.locator_id '||
316 ' , mil.concatenated_segments, mil.description '||
317 ' , moq.revision, moq.lot_number '||
318 'ORDER BY mif.item_number, moq.inventory_item_id '||
319 ' , moq.subinventory_code, moq.locator_id '||
320 ' , mil.concatenated_segments, mil.description '||
321 ' , moq.revision, moq.lot_number';
322
323 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, 'On-hand Quantities of Items Associated with Pending Txns in MMTT'||reportStr);
324 statusStr := 'SUCCESS';
325 isFatal := 'FALSE';
326
327 sqltxt := 'SELECT DISTINCT( msi.secondary_inventory_name ) "Name" '||
328 ' , msi.description "Description" '||
329 ' , msi.disable_date "Disable|Date" '||
330 ' , DECODE( msi.reservable_type, 1, ''Yes'', 2, ''No'', '||
331 ' msi.reservable_type) "Reservable|Type" '||
332 ' , DECODE( msi.locator_type '||
333 ' ,1, ''None'' '||
334 ' ,2, ''Prespecified'' '||
335 ' ,3, ''Dynamic'' '||
336 ' ,4, ''SubInv Level'' '||
337 ' ,5, ''Item Level'', msi.locator_type) '||
338 ' || '' (''||msi.locator_type||'')'' "Locator|Control" '||
339 ' , DECODE( msi.availability_type '||
340 ' ,1, ''Nettable'' '||
341 ' ,2, ''Non-Nettable'',msi.availability_type ) "Availability|Type" '||
342 ' , DECODE( msi.inventory_atp_code, 1, ''Included in atp'' '||
343 ' , 2, ''Not included in atp'' '||
344 ' , msi.inventory_atp_code ) "Include|in ATP" '||
345 ' , DECODE( msi.asset_inventory, 1, ''Yes'', 2, ''No'', '||
346 ' msi.asset_inventory ) "Asset|Inventory" '||
347 ' , DECODE( msi.quantity_tracked, 1, ''Yes'', 2, ''No'', '||
348 ' msi.quantity_tracked ) "Quantity|Tracked" '||
349 ' FROM mtl_secondary_inventories msi '||
350 'WHERE (msi.organization_id, msi.secondary_inventory_name ) IN '||
351 ' ( SELECT mmtt.organization_id, NVL(mmtt.subinventory_code,-99) '||
352 ' FROM mtl_material_transactions_temp mmtt '||
353 ' WHERE NVL( mmtt.transaction_status, 1 ) != 2 ';
354 if l_org_id is not null then
355 sqltxt :=sqltxt||' and mmtt.organization_id = '||l_org_id;
356 end if;
357
358 if l_acct_period_id is not null then
359 sqltxt := sqltxt||' and mmtt.acct_period_id = '||l_acct_period_id;
360 end if;
361
362 sqltxt := sqltxt||' UNION '||
363 ' SELECT NVL( mmtt.transfer_organization, mmtt.organization_id ) '||
364 ' ,NVL( mmtt.transfer_subinventory,-99 )'||
365 ' FROM mtl_material_transactions_temp mmtt '||
366 ' WHERE NVL( mmtt.transaction_status, 1 ) != 2 ';
367 if l_org_id is not null then
368 sqltxt :=sqltxt||' and mmtt.organization_id = '||l_org_id;
369 end if;
370
371 if l_acct_period_id is not null then
372 sqltxt := sqltxt||' and mmtt.acct_period_id = '||l_acct_period_id;
373 end if;
374 sqltxt := sqltxt||' ) '||
375 ' ORDER BY secondary_inventory_name';
376
377 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, 'Subinventories Associated with Pending Txns in MMTT'||reportStr);
378 statusStr := 'SUCCESS';
379 isFatal := 'FALSE';
380
381 sqltxt := 'SELECT mp.organization_code "Organization|Code" '||
382 ' , mmtt.organization_id "Organization|ID" '||
383 ' , oap.period_name "Period Name" '||
384 ' , mmtt.acct_period_id "Period ID" '||
385 ' , mmtt.process_flag "Process Flag" '||
386 ' , mmtt.lock_flag "Lock Flag" '||
387 ' , COUNT(*) "Count" '||
388 ' FROM mtl_material_transactions_temp mmtt, mtl_parameters mp '||
389 ' , org_acct_periods oap '||
390 'WHERE NVL( mmtt.transaction_status,1 ) != 2 '||
391 ' AND mmtt.organization_id=mp.organization_id(+) '||
392 ' AND mmtt.acct_period_id=oap.acct_period_id(+) ';
393 if l_org_id is not null then
394 sqltxt :=sqltxt||' and mmtt.organization_id = '||l_org_id;
395 end if;
396
397 if l_acct_period_id is not null then
398 sqltxt := sqltxt||' and mmtt.acct_period_id = '||l_acct_period_id;
399 end if;
400
401 sqltxt := sqltxt||' GROUP BY mp.organization_code, mmtt.organization_id '||
402 ' , oap.period_name, mmtt.acct_period_id '||
403 ' , mmtt.process_flag, mmtt.lock_flag '||
404 'ORDER BY mp.organization_code, mmtt.organization_id '||
405 ' , mmtt.acct_period_id, mmtt.process_flag';
406
407 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, 'Over view of All Pending Material Transactions');
408 statusStr := 'SUCCESS';
409 isFatal := 'FALSE';
410
411 sqltxt :='SELECT process_flag "Process Flag" '||
412 ' , COUNT(*) "Count" '||
413 ' FROM mtl_material_transactions_temp '||
414 'WHERE NVL( transaction_status, 1 ) != 2';
415 if l_org_id is not null then
416 sqltxt :=sqltxt||' and organization_id = '||l_org_id;
417 end if;
418
419 if l_acct_period_id is not null then
420 sqltxt := sqltxt||' and acct_period_id = '||l_acct_period_id;
421 end if;
422 sqltxt := sqltxt||'GROUP BY process_flag '||
423 'ORDER BY process_flag ';
424
425 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, 'Distinct process_flag for All Pending Txns'||reportStr);
426 statusStr := 'SUCCESS';
427 isFatal := 'FALSE';
428
429
430 sqltxt := 'SELECT lock_flag "Lock Flag" , COUNT(*) "Count" '||
431 ' FROM mtl_material_transactions_temp '||
432 'WHERE NVL( transaction_status, 1 ) != 2';
433 if l_org_id is not null then
434 sqltxt :=sqltxt||' and organization_id = '||l_org_id;
435 end if;
436
437 if l_acct_period_id is not null then
438 sqltxt := sqltxt||' and acct_period_id = '||l_acct_period_id;
439 end if;
440
441 sqltxt := sqltxt||'GROUP BY lock_flag '||
442 'ORDER BY lock_flag ';
443
444 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, 'Distinct lock_flag for All Pending Txns'||reportStr);
445 statusStr := 'SUCCESS';
446 isFatal := 'FALSE';
447
448 sqltxt := 'SELECT process_flag "Process Flag" '||
449 ' , DECODE( NVL( transaction_status, ''-99'' ) '||
450 ' , ''1'', ''Pending'' '||
451 ' , ''2'', ''Allocated'' '||
452 ' , ''-99'', ''Pending'' '||
453 ' , transaction_status ) '||
454 ' || '' ('' ||NVL( TO_CHAR( transaction_status ), ''null'') '||
455 ' || '' )'' "Transaction Status" '||
456 ' , COUNT(*) "Count" '||
457 ' FROM mtl_material_transactions_temp ';
458 if l_org_id is not null then
459 sqltxt :=sqltxt||' where organization_id = '||l_org_id;
460 end if;
461
462 if l_acct_period_id is not null then
463 sqltxt := sqltxt||' and acct_period_id = '||l_acct_period_id;
464 end if;
465
466 sqltxt := sqltxt||'GROUP BY process_flag, transaction_status '||
467 'ORDER BY process_flag, transaction_status ';
468
469 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, 'Distinct process_flag, transaction_status for All Pending Txns'||reportStr);
470 statusStr := 'SUCCESS';
471 isFatal := 'FALSE';
472
473 sqltxt := 'SELECT process_flag "Process Flag" '||
474 ' , ml.meaning || '' ('' || mmtt.transaction_mode || '')'' '||
475 ' "Transaction Mode" '||
476 ' , COUNT(*) "Count" '||
477 ' FROM mtl_material_transactions_temp mmtt, mfg_lookups ml '||
478 'WHERE NVL( mmtt.transaction_status, 1 ) != 2 '||
479 ' AND ml.lookup_type(+) = ''MTL_TRANSACTION_MODE'' '||
480 ' AND mmtt.transaction_mode = ml.lookup_code(+)';
481 if l_org_id is not null then
482 sqltxt :=sqltxt||' and mmtt.organization_id = '||l_org_id;
483 end if;
484
485 if l_acct_period_id is not null then
486 sqltxt := sqltxt||' and mmtt.acct_period_id = '||l_acct_period_id;
487 end if;
488 sqltxt := sqltxt||'GROUP BY process_flag, ml.meaning, mmtt.transaction_mode '||
489 'ORDER BY process_flag, ml.meaning, mmtt.transaction_mode ';
490
491 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, 'Distinct process_flag, transaction_mode for All Pending Txns');
492 statusStr := 'SUCCESS';
493 isFatal := 'FALSE';
494
495
496
497 sqltxt :='SELECT mmtt.process_flag "Process Flag" '||
498 ' , tt.transaction_type_name '||
499 ' ||'' ( ''||mmtt.transaction_type_id||'' )'' '||
500 ' "Txn Type (Id)" '||
501 ' , ml.meaning '||
502 ' ||'' ( ''||mmtt.transaction_action_id||'' )'' '||
503 ' "Txn Action (Id)" '||
504 ' , COUNT(*) "Count" '||
505 ' FROM mtl_material_transactions_temp mmtt '||
506 ' , mtl_transaction_types tt '||
507 ' , mfg_lookups ml '||
508 'WHERE NVL( mmtt.transaction_status, 1 ) != 2 '||
509 ' AND mmtt.transaction_type_id = tt.transaction_type_id(+) '||
510 ' AND mmtt.transaction_action_id = ml.lookup_code '||
511 ' AND ml.lookup_type = ''MTL_TRANSACTION_ACTION'' ';
512 if l_org_id is not null then
513 sqltxt :=sqltxt||' and mmtt.organization_id = '||l_org_id;
514 end if;
515
516 if l_acct_period_id is not null then
517 sqltxt := sqltxt||' and mmtt.acct_period_id = '||l_acct_period_id;
518 end if;
519
520 sqltxt := sqltxt||' GROUP BY mmtt.process_flag '||
521 ' , tt.transaction_type_name, mmtt.transaction_type_id '||
522 ' , ml.meaning, mmtt.transaction_action_id '||
523 ' ORDER BY mmtt.process_flag '||
524 ' , tt.transaction_type_name '||
525 ' ||'' ( ''||mmtt.transaction_type_id||'' )'' '||
526 ' , ml.meaning '||
527 ' ||'' ( ''||mmtt.transaction_action_id||'' )'' ';
528
529 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, 'Distinct process_flag, transaction_type for All Pending Txns'||reportStr);
530 statusStr := 'SUCCESS';
531 isFatal := 'FALSE';
532
533 sqltxt := 'SELECT COUNT(*) '||
534 ' FROM mtl_material_transactions_temp mmtt, mtl_system_items_b msib '||
535 ' WHERE NVL( mmtt.transaction_status, 1 ) != 2 '||
536 ' AND mmtt.organization_id = msib.organization_id '||
537 ' AND mmtt.inventory_item_id = msib.inventory_item_id '||
538 ' AND msib.serial_number_control_code > 1 ';
539
540 if l_org_id is not null then
541 sqltxt :=sqltxt||' and mmtt.organization_id = '||l_org_id;
542 end if;
543
544 if l_acct_period_id is not null then
545 sqltxt := sqltxt||' and mmtt.acct_period_id = '||l_acct_period_id;
546 end if;
547
548 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, 'Number of Pending txns that refer to a serial-controlled item'||reportStr);
549 statusStr := 'SUCCESS';
550 isFatal := 'FALSE';
551
552 sqltxt := 'SELECT mmtt.transaction_temp_id "Txn|Temp Id" '||
553 ' , mmtt.transaction_quantity "Txn Qty" '||
554 ' , mmtt.primary_quantity "Primary|Txn Qty" '||
555 ' , mmtt.transaction_uom "Txn UoM" '||
556 ' , msnt.fm_serial_number "From|Serial#" '||
557 ' , msnt.to_serial_number "To|Serial#" '||
558 ' , msnt.serial_prefix "Serial|Prefix" '||
559 ' , msnt.error_code "Error Code" '||
560 ' , msnt.parent_serial_number "Parent|Serial#" '||
561 ' , msnt.group_header_id "Group|Header Id" '||
562 ' , mmtt.item_description "Item Description" '||
563 ' , mmtt.serial_number "Serial#" '||
564 ' , DECODE( mmtt.item_serial_control_code, '||
565 ' 1, ''No serial number control'', '||
566 ' 2, ''Predefined serial numbers'', '||
567 ' 5, ''Dynamic entry at inventory receipt'', '||
568 ' 6, ''Dynamic entry at sales order issue'', '||
569 ' mmtt.item_serial_control_code ) '||
570 ' ||'' (''||mmtt.item_serial_control_code||'')'' "Item Serial Control" '||
571 ' , mmtt.next_serial_number "Next Serial#" '||
572 ' , mmtt.serial_alpha_prefix "Serial|Alpha Prefix" '||
573 'FROM mtl_material_transactions_temp mmtt '||
574 ' , mtl_serial_numbers_temp msnt '||
575 'WHERE NVL( mmtt.transaction_status, 1 ) != 2 ';
576 if l_org_id is not null then
577 sqltxt :=sqltxt||' and mmtt.organization_id = '||l_org_id;
578 end if;
579
580 if l_acct_period_id is not null then
581 sqltxt := sqltxt||' and mmtt.acct_period_id = '||l_acct_period_id;
582 end if;
583 sqltxt := sqltxt||' AND mmtt.transaction_temp_id = msnt.transaction_temp_id'||
584 ' ORDER BY mmtt.transaction_temp_id';
585 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, 'Serial Number Information from Table MSNT');
586 statusStr := 'SUCCESS';
587 isFatal := 'FALSE';
588
589 sqltxt := 'SELECT mif.item_number '||
590 ' ||'' (''|| msn.inventory_item_id||'')'' "Item (Id)" '||
591 ' , msn.serial_number "Serial|Number" '||
592 ' , ml.meaning '||
593 ' ||'' (''||msn.current_status||'')'' "Current|Status" '||
594 ' , msn.group_mark_id "Group|Mark Id" '||
595 ' , msn.line_mark_id "Line|Mark Id" '||
596 ' , msn.lot_line_mark_id "Lot Line|Mark Id" '||
597 ' , mp.organization_Code "Current|Org Code" '||
598 ' , msn.current_organization_id "Current|Org Id" '||
599 ' , msn.current_subinventory_code "Current|Subinventory" '||
600 ' , msn.current_locator_id "Current|Locator Id" '||
601 ' , mil.concatenated_segments "Current|Locator" '||
602 ' , mil.description "Current|Locator Desc" '||
603 ' FROM mtl_serial_numbers msn, mtl_item_flexfields mif '||
604 ' , mtl_parameters mp, mtl_item_locations_kfv mil '||
605 ' , mfg_lookups ml '||
606 'WHERE msn.inventory_item_id = mif.inventory_item_id '||
607 ' AND msn.current_organization_id = mif.organization_id '||
608 ' AND msn.current_organization_id = mp.organization_id(+) '||
609 ' AND msn.current_locator_id = mil.inventory_location_id(+) '||
610 ' AND msn.current_organization_id = mil.organization_id(+) '||
611 ' AND msn.current_status = ml.lookup_code(+) '||
612 ' AND ''SERIAL_NUM_STATUS'' = ml.lookup_type(+) '||
613 ' AND msn.inventory_item_id IN '||
614 ' ( SELECT DISTINCT( inventory_item_id ) '||
615 ' FROM mtl_material_transactions_temp mmtt '||
616 ' WHERE NVL( mmtt.transaction_status,1 ) != 2';
617 if l_org_id is not null then
618 sqltxt :=sqltxt||' and mmtt.organization_id = '||l_org_id;
619 end if;
620
621 if l_acct_period_id is not null then
622 sqltxt := sqltxt||' and mmtt.acct_period_id = '||l_acct_period_id;
623 end if;
624
625 if l_proc_flag is not null then
626 sqltxt := sqltxt||' AND mmtt.process_flag= '''||l_proc_flag||''' ';
627 end if;
628
629 sqltxt := sqltxt|| ') ORDER BY mif.item_number, msn.serial_number';
630
631 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, 'Serial Number Information from MSN for Pending Txns'||reportStr);
632 statusStr := 'SUCCESS';
633 isFatal := 'FALSE';
634
635 sqltxt := 'SELECT COUNT(*) '||
636 ' FROM mtl_material_transactions_temp mmtt, mtl_system_items_b msib '||
637 ' WHERE NVL( mmtt.transaction_status, 1 ) != 2 '||
638 ' AND mmtt.organization_id = msib.organization_id '||
639 ' AND mmtt.inventory_item_id = msib.inventory_item_id '||
640 ' AND msib.lot_control_code = 2 ';
641 if l_org_id is not null then
642 sqltxt :=sqltxt||' and mmtt.organization_id = '||l_org_id;
643 end if;
644
645 if l_acct_period_id is not null then
646 sqltxt := sqltxt||' and mmtt.acct_period_id = '||l_acct_period_id;
647 end if;
648
649 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, 'Number of Pending txns that refer to a lot-controlled item'||reportStr );
650 statusStr := 'SUCCESS';
651 isFatal := 'FALSE';
652
653 sqltxt := 'SELECT mmtt.transaction_temp_id "Txn|Temp Id" '||
654 ' , mmtt.transaction_quantity "Txn Qty" '||
655 ' , mmtt.primary_quantity "Primary|Txn Qty" '||
656 ' , mmtt.transaction_uom "Txn UoM" '||
657 ' , mtlt.lot_number "Lot|Number" '||
658 ' , mtlt.lot_expiration_date "Lot Expiration|Date" '||
659 ' , mtlt.error_code "Lot Error Code" '||
660 ' , mtlt.serial_transaction_temp_id "Serial Txn|Temp Id" '||
661 ' , mmtt.item_description "Item|Description" '||
662 ' FROM mtl_material_transactions_temp mmtt '||
663 ' , mtl_transaction_lots_temp mtlt '||
664 ' WHERE NVL( mmtt.transaction_status, 1 ) != 2 '||
665 ' AND mmtt.transaction_temp_id = mtlt.transaction_temp_id ';
666
667 if l_org_id is not null then
668 sqltxt :=sqltxt||' and mmtt.organization_id = '||l_org_id;
669 end if;
670
671 if l_acct_period_id is not null then
672 sqltxt := sqltxt||' and mmtt.acct_period_id = '||l_acct_period_id;
673 end if;
674
675 if l_proc_flag is not null then
676 sqltxt := sqltxt||' AND mmtt.process_flag= '''||l_proc_flag||''' ';
677 end if;
678 sqltxt := sqltxt||' ORDER BY mmtt.transaction_temp_id';
679
680 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, 'Lot Information from MTLT for Pending Txns'||reportStr);
681 statusStr := 'SUCCESS';
682 isFatal := 'FALSE';
683
684 sqltxt := 'SELECT COUNT(*) '||
685 ' FROM mtl_material_transactions_temp mmtt, mtl_system_items_b msib '||
686 'WHERE mmtt.organization_id = msib.organization_id '||
687 ' AND mmtt.inventory_item_id = msib.inventory_item_id '||
688 ' AND NVL( mmtt.transaction_status, 1 ) != 2 '||
689 ' AND msib.revision_qty_control_code = 2 ';
690 if l_org_id is not null then
691 sqltxt :=sqltxt||' and mmtt.organization_id = '||l_org_id;
692 end if;
693
694 if l_acct_period_id is not null then
695 sqltxt := sqltxt||' and mmtt.acct_period_id = '||l_acct_period_id;
696 end if;
697
698 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, 'Number of Pending txns that refer to a revision-controlled item'||reportStr );
699 statusStr := 'SUCCESS';
700 isFatal := 'FALSE';
701
702 sqltxt := 'SELECT mif.item_number "Item|Number" '||
703 ' , mir.inventory_item_id "Item Id" '||
704 ' , mir.revision "Revision" '||
705 ' , mir.change_notice "Change Notice" '||
706 ' , mir.ecn_initiation_date "ECN Initiation|Date" '||
707 ' , mir.implementation_date "Implementation|Date" '||
708 ' , mir.effectivity_date "Effectivity|Date" '||
709 ' FROM mtl_item_revisions mir, mtl_item_flexfields mif '||
710 'WHERE mir.organization_id = mif.organization_id '||
711 ' AND mir.inventory_item_id = mif.inventory_item_id(+) '||
712 ' AND mif.revision_qty_control_code = ''2'' ';
713 if l_org_id is not null then
714 sqltxt :=sqltxt||' and mir.organization_id = '||l_org_id;
715 end if;
716 sqltxt := sqltxt||' AND mir.inventory_item_id IN '||
717 ' ( SELECT DISTINCT( inventory_item_id ) '||
718 ' FROM mtl_material_transactions_temp mmtt '||
719 ' WHERE NVL( mmtt.transaction_status, 1 ) != 2 ';
720 if l_org_id is not null then
721 sqltxt :=sqltxt||' and mmtt.organization_id = '||l_org_id;
722 end if;
723
724 if l_acct_period_id is not null then
725 sqltxt := sqltxt||' and mmtt.acct_period_id = '||l_acct_period_id;
726 end if;
727
728 if l_proc_flag is not null then
729 sqltxt := sqltxt||' AND mmtt.process_flag= '''||l_proc_flag||''' ';
730 end if;
731
732 sqltxt := sqltxt||') '||
733 ' ORDER BY mif.item_number, mir.revision';
734
735 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, 'Revision Information for Pending Txns'||reportStr);
736 statusStr := 'SUCCESS';
737 isFatal := 'FALSE';
738
739 sqltxt := 'SELECT DISTINCT mif.item_number "Item Number" '||
740 ' ,mmtt.inventory_item_id "Item Id" '||
741 ' ,primary_uom_code "Primary|UoM" '||
742 ' ,mif.inventory_item_flag "Inventory|Item Flag" '||
743 ' ,mif.stock_enabled_flag "Stock|Flag" '||
744 ' ,mif.mtl_transactions_enabled_flag "Transactable|Flag" '||
745 ' ,mif.costing_enabled_flag "Costing|Flag" '||
746 ' ,mif.inventory_asset_flag "Inventory|Asset Flag" '||
747 ' ,DECODE( mif.lot_control_code, 1, ''N'' , 2, ''Y'' '||
748 ' , mif.lot_control_code ) "Lot|Control" '||
749 ' ,ml.meaning||'' (''||mif.serial_number_control_code||'')'' "Serial|Control" '||
750 ' ,DECODE( TO_CHAR(mif.revision_qty_control_code) '||
751 ' , ''1'', ''No'' '||
752 ' , ''2'', ''Yes'' '||
753 ' ,mif.revision_qty_control_code ) "Revision|Control" '||
754 ' ,DECODE( TO_CHAR(mif.location_control_code) '||
755 ' , ''1'', ''None'' '||
756 ' , ''2'', ''Prespecified'' '||
757 ' , ''3'', ''Dynamic'' '||
758 ' , ''4'', ''Determine at Subinv Level'' '||
759 ' , ''5'', ''Determine at Item Level'' '||
760 ' , mif.location_control_code ) '||
761 ' || '' (''||mif.location_control_code||'')'' "Location|Control" '||
762 ' ,DECODE( mif.restrict_subinventories_code, 1, ''Y'' '||
763 ' , 2, ''N'' '||
764 ' ,mif.restrict_subinventories_code ) "Restricted|Subinvs" '||
765 ' ,DECODE( mif.restrict_locators_code, 1, ''Y'', 2, ''N'' '||
766 ' ,mif.restrict_locators_code ) "Restricted|Locators" '||
767 ' FROM mtl_material_transactions_temp mmtt '||
768 ' , mtl_item_flexfields mif , mfg_lookups ml '||
769 'WHERE mmtt.organization_id = mif.organization_id '||
770 ' AND mmtt.inventory_item_id = mif.inventory_item_id(+) '||
771 ' AND mif.serial_number_control_code = ml.lookup_code(+) '||
772 ' AND ''MTL_SERIAL_NUMBER'' = ml.lookup_type(+) ';
773 if l_org_id is not null then
774 sqltxt :=sqltxt||' and mmtt.organization_id = '||l_org_id;
775 end if;
776
777 if l_acct_period_id is not null then
778 sqltxt := sqltxt||' and mmtt.acct_period_id = '||l_acct_period_id;
779 end if;
780
781 if l_proc_flag is not null then
782 sqltxt := sqltxt||' AND mmtt.process_flag= '''||l_proc_flag||''' ';
783 end if;
784
785 sqltxt := sqltxt||' ORDER BY mif.item_number';
786
787 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, 'Item Associated with Pending Txns in MMTT'||reportStr);
788 statusStr := 'SUCCESS';
789 isFatal := 'FALSE';
790
791 sqltxt := 'SELECT process_flag "Process Flag" '||
792 ' , error_code "Error Code" '||
793 ' , error_explanation "Error Explanation" '||
794 ' , COUNT(*) "Count" '||
795 ' FROM mtl_material_transactions_temp '||
796 'WHERE NVL( transaction_status, 1 ) != 2';
797 if l_org_id is not null then
798 sqltxt :=sqltxt||' and organization_id = '||l_org_id;
799 end if;
800
801 if l_acct_period_id is not null then
802 sqltxt := sqltxt||' and acct_period_id = '||l_acct_period_id;
803 end if;
804
805 if l_proc_flag is not null then
806 sqltxt := sqltxt||' AND process_flag= '''||l_proc_flag||''' ';
807 end if;
808
809 sqltxt := sqltxt||'GROUP BY process_flag, error_code, error_explanation '||
810 ' ORDER BY process_flag ';
811
812 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, 'Distinct Types of Errors in MMTT');
813 statusStr := 'SUCCESS';
814 isFatal := 'FALSE';
815
816
817 /**
818 else
819 JTF_DIAGNOSTIC_COREAPI.ActionErrorPrint('Invalid Input parameters');
820 statusStr := 'FAILURE';
821 errStr := 'org_id null';
822 fixInfo := 'Org or OrdID input is required ';
823 isFatal := 'SUCCESS';
824 end if;
825 **/
826 -- construct report
827 <<l_test_end>>
828 report := JTF_DIAGNOSTIC_ADAPTUTIL.constructReport(statusStr,errStr,fixInfo,isFatal);
829 reportClob := JTF_DIAGNOSTIC_ADAPTUTIL.getReportClob;
830 END runTest;
831
832 PROCEDURE getComponentName(name OUT NOCOPY VARCHAR2) IS
833 BEGIN
834 name := 'Pending Transactions (MMTT)';
835 END getComponentName;
836
837 PROCEDURE getTestDesc(descStr OUT NOCOPY VARCHAR2) IS
838 BEGIN
839 descStr := 'Pending Transactions in MMTT';
840 END getTestDesc;
841
842 PROCEDURE getTestName(name OUT NOCOPY VARCHAR2) IS
843 BEGIN
844 name := 'Pending Transactions (MMTT)';
845 END getTestName;
846
847 PROCEDURE getDependencies (package_names OUT NOCOPY JTF_DIAG_DEPENDTBL) IS
848 tempDependencies JTF_DIAG_DEPENDTBL;
849
850 BEGIN
851 package_names := JTF_DIAGNOSTIC_ADAPTUTIL.initDependencyTable;
852 END getDependencies;
853
854 PROCEDURE isDependencyPipelined (str OUT NOCOPY VARCHAR2) IS
855 BEGIN
856 str := 'FALSE';
857 END isDependencyPipelined;
858
859
860 PROCEDURE getOutputValues(outputValues OUT NOCOPY JTF_DIAG_OUTPUTTBL) IS
861 tempOutput JTF_DIAG_OUTPUTTBL;
862 BEGIN
863 tempOutput := JTF_DIAGNOSTIC_ADAPTUTIL.initOutputTable;
864 outputValues := tempOutput;
865 EXCEPTION
866 when others then
867 outputValues := JTF_DIAGNOSTIC_ADAPTUTIL.initOutputTable;
868 END getOutputValues;
869
870
871 PROCEDURE getDefaultTestParams(defaultInputValues OUT NOCOPY JTF_DIAG_INPUTTBL) IS
872 tempInput JTF_DIAG_INPUTTBL;
873 BEGIN
874 tempInput := JTF_DIAGNOSTIC_ADAPTUTIL.initinputtable;
875 --tempInput := JTF_DIAGNOSTIC_ADAPTUTIL.addInput(tempInput,'testout','');
876 tempInput := JTF_DIAGNOSTIC_ADAPTUTIL.addInput(tempInput,'OrgId','LOV-oracle.apps.inv.diag.lov.OrganizationLov');
877 tempInput := JTF_DIAGNOSTIC_ADAPTUTIL.addInput(tempInput,'AcctPeriod','LOV-oracle.apps.inv.diag.lov.PeriodLov');
878 tempInput := JTF_DIAGNOSTIC_ADAPTUTIL.addInput(tempInput,'ErrorFlag','LOV-oracle.apps.inv.diag.lov.MMTTErroredAllLov');
879 defaultInputValues := tempInput;
880 EXCEPTION
881 when others then
882 defaultInputValues := JTF_DIAGNOSTIC_ADAPTUTIL.initinputtable;
883 END getDefaultTestParams;
884
885 Function getTestMode return INTEGER IS
886 BEGIN
887 return JTF_DIAGNOSTIC_ADAPTUTIL.ADVANCED_MODE;
888
889 END getTestMode;
890
891 END;