eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.INV_DIAG_MTI

Source


1 package body INV_DIAG_MTI as
2 /* $Header: INVDT03B.pls 120.0.12000000.1 2007/06/22 01:27:39 musinha noship $ */
3 
4 PROCEDURE init is
5 BEGIN
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  dummy_num   NUMBER;
26  row_limit   NUMBER;
27  l_txn_id    NUMBER;
28  l_org_id    NUMBER;
29  l_proc_flag number;
30  l_msg       varchar2(1000);
31 
32 BEGIN
33 JTF_DIAGNOSTIC_ADAPTUTIL.setUpVars;
34 JTF_DIAGNOSTIC_ADAPTUTIL.addStringToReport('@html');
35 JTF_DIAGNOSTIC_COREAPI.insert_style_sheet;
36 -- accept input
37 l_org_id := JTF_DIAGNOSTIC_ADAPTUTIL.getInputValue('OrgId',inputs);
38 l_proc_flag :=JTF_DIAGNOSTIC_ADAPTUTIL.getInputValue('ErrorFlag',inputs);
39 row_limit :=INV_DIAG_GRP.g_max_row;
40 
41 reportStr :='';
42 if l_org_id is null then
43    reportStr := ' For All Organizations';
44 end if;
45 
46 if l_proc_flag = 0 then
47    l_proc_flag := null;
48 end if;
49 
50 if l_proc_flag = 3 then
51    reportStr := reportStr||' : process Errored';
52 elsif l_proc_flag = 1 then
53    reportStr := reportStr||' : process Pending';
54 end if;
55 
56 
57 JTF_DIAGNOSTIC_ADAPTUTIL.addStringToReport('OrgID input :'||l_org_id);
58 JTF_DIAGNOSTIC_COREAPI.BRPrint;
59 sqltxt := 'SELECT transaction_header_id "Txn Header Id"  '||
60           '    , mti.transaction_interface_id "Txn IntFace|Id"  '||
61           '    , mif.item_number ||'' (''|| mti.inventory_item_id ||'')'' "Item (Id)"  '||
62           '    , item_segment1 "Item Segment1"  '||
63           '    , subinventory_code "Subinv"  '||
64           '    , loc_segment1 ||'' ''|| loc_segment2 ||'' ''|| loc_segment3 "Loc_Segment  1-3"  '||
65           '    , locator_id "Locator Id"  '||
66           '    , revision "Rev"  '||
67           '    , mti.transaction_quantity "Txn Qty"  '||
68           '    , mti.primary_quantity "Primary Qty"  '||
69           '    , transaction_uom "Txn UoM"  '||
70           '    , transaction_cost "Txn Cost"  '||
71           '    , transaction_type_name ||'' (''|| transaction_type_id ||'')'' "Txn Type (Id)"  '||
72           '    , transaction_action_name ||'' (''|| transaction_action_id ||'')'' "Txn Action (Id)"  '||
73           '    , transaction_source_type_name ||'' (''|| transaction_source_type_id ||'')'' "Txn Source Type (Id)"  '||
74           '    , transaction_source_name ||'' (''|| transaction_source_id ||'')'' "Txn Source (Id)"  '||
75           '    , trx_source_line_id "Txn Source|Line Id"  '||
76           '    , cost_group_id "Cost|Group Id"  '||
77           '    , TO_CHAR( transaction_date, ''DD-MON-RR HH24:MI'' ) "Txn Date"  '||
78           '    , transaction_reference "Txn Reference"  '||
79           '    , transfer_subinventory "Transfer|Subinv"  '||
80           '    , transfer_organization_code ||'' (''|| transfer_organization ||'')'' "Transfer|Organization"  '||
81           '    , mti.request_id "Request Id"  '||
82           '    , mti.source_code "Source|Code"  '||
83           '    , mti.source_line_id "Source Line Id"  '||
84           '    , source_header_id "Source Header Id"  '||
85           '    , mti.distribution_account_id "Distribution Account Id"  '||
86           '    , mti.process_flag_desc ||'' ('' || mti.process_flag || '')'' "Process Flag"  '||
87           '    , transaction_mode_desc ||'' ('' || transaction_mode || '')'' "Txn Mode"  '||
88           '    , lock_flag_desc ||'' ('' || lock_flag || '')'' "Lock Flag"  '||
89           '    , TO_CHAR( mti.last_update_date, ''DD-MON-RR HH24:MI'' ) "Last updated"  '||
90           '    , mti.error_code "Error Code"  '||
91           '    , error_explanation "Error Explanation"  '||
92           ' FROM mtl_transactions_interface_v mti  '||
93           '    , mtl_item_flexfields mif  '||
94           'WHERE mti.organization_id = mif.organization_id(+)  '||
95           '  AND mti.inventory_item_id = mif.inventory_item_id(+) ';
96 
97 if l_org_id is not null then
98    sqltxt :=sqltxt||' and mti.organization_id =  '||l_org_id;
99 end if;
100 
101 if l_proc_flag is not null then
102    sqltxt :=sqltxt||' and  mti.process_flag = '||l_proc_flag;
103 end if;
104 
105 sqltxt := sqltxt||' ORDER BY transaction_header_id, mti.transaction_interface_id';
106 
107 sqltxt := 'select * from ('||sqltxt||') WHERE ROWNUM <= '||row_limit;
108 
109 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, 'Display transaction records in MTI'||reportStr);
110 
111 statusStr := 'SUCCESS';
112 isFatal := 'FALSE';
113 
114 
115 sqltxt := 'SELECT transaction_interface_id "Txn|Interface Id"  '||
116           '    , transaction_quantity "Txn Qty"  '||
117           '    , primary_quantity "Primary|Txn Qty"  '||
118           '    , transaction_uom "Txn UoM"  '||
119           '    , subinventory_code "Subinventory"  '||
120           '    , error_code "Error|Code"  '||
121           '    , error_explanation "Error|Explanation"  '||
122           ' FROM mtl_transactions_interface  '||
123           'WHERE ( ABS( transaction_quantity ) < 0.00001  '||
124           '        OR ABS( primary_quantity ) < 0.00001 )';
125 
126 if l_org_id is not null then
127    sqltxt :=sqltxt||' and organization_id =  '||l_org_id;
128 end if;
129 
130 if l_proc_flag is not null then
131    sqltxt :=sqltxt||' and process_flag = '||l_proc_flag;
132 end if;
133 
134 sqltxt := sqltxt||' ORDER BY transaction_interface_id';
135 
136 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, 'Transactions with Transaction or Primary Quantity Below Minimum'||reportStr);
137 
138 statusStr := 'SUCCESS';
139 isFatal := 'FALSE';
140 
141 
142 
143 sqltxt := 'SELECT transaction_interface_id  '||
144           '     , item_segment1  '||
145           '     , inventory_item_id  '||
146           '  FROM mtl_transactions_interface  '||
147           ' WHERE ( item_segment1 like ''% '' OR item_segment2 like ''% ''  '||
148           '         OR item_segment3 like ''% '' OR item_segment3 like ''% '' )';
149 if l_org_id is not null then
150    sqltxt :=sqltxt||' and organization_id =  '||l_org_id;
151 end if;
152 
153 sqltxt := 'select * from ('||sqltxt||') WHERE ROWNUM <= '||row_limit;
154 
155 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, 'Items with Trailing Spaces '||reportStr);
156 
157 statusStr := 'SUCCESS';
158 isFatal := 'FALSE';
159 
160 
161 sqltxt := 'SELECT mif.item_number "Item"  '||
162           '    , moq.inventory_item_id "Item Id"  '||
163           '    , SUM( moq.transaction_quantity ) "Txn Qty"  '||
164           '    , moq.subinventory_code "Subinv"  '||
165           '    , moq.locator_id "Locator Id"  '||
166           '    , mil.concatenated_segments "Locator"  '||
167           '    , mil.description "Locator Desc"  '||
168           '    , moq.revision "Revision"  '||
169           '    , moq.lot_number "Lot Number"  '||
170           ' FROM mtl_onhand_quantities_detail moq , mtl_item_flexfields mif  '||
171           '    , mtl_item_locations_kfv mil  '||
172           'WHERE moq.inventory_item_id = mif.inventory_item_id(+)  '||
173           '  AND moq.organization_id = mif.organization_id(+)  '||
174           '  AND moq.organization_id = mil.organization_id(+)  '||
175           '  AND moq.locator_id = mil.inventory_location_id(+)  ';
176 if l_org_id is not null then
177    sqltxt :=sqltxt||' and moq.organization_id =  '||l_org_id;
178 end if;
179 sqltxt := sqltxt||'  AND moq.inventory_item_id  '||
180           '      IN ( SELECT DISTINCT mti.inventory_item_id  '||
181           '             FROM mtl_transactions_interface mti  ';
182 if l_org_id is not null then
183    sqltxt :=sqltxt||' WHERE mti.organization_id =  '||l_org_id;
184    if l_proc_flag is not null then
185    sqltxt :=sqltxt||' and  mti.process_flag = '||l_proc_flag;
186    end if;
187 elsif l_proc_flag is not null then
188    sqltxt :=sqltxt||' where  mti.process_flag = '||l_proc_flag;
189 end if;
190 sqltxt := sqltxt||'          )'||
191           ' GROUP BY mif.item_number, moq.inventory_item_id  '||
192           '       , moq.subinventory_code, moq.locator_id  '||
193           '       , mil.concatenated_segments, mil.description  '||
194           '       , moq.revision, moq.lot_number  '||
195           ' ORDER BY mif.item_number, moq.inventory_item_id  '||
196           '       , moq.subinventory_code, moq.locator_id  '||
197           '       , mil.concatenated_segments, mil.description  '||
198           '       , moq.revision, moq.lot_number';
199 
200 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, 'On-hand Quantities of Items Associated with Pending Txns in MTI'||reportStr);
201 
202 statusStr := 'SUCCESS';
203 isFatal := 'FALSE';
204 
205 
206 sqltxt := 'SELECT DISTINCT( msi.secondary_inventory_name ) "Name"   '||
207           '  , msi.description "Description"   '||
208           '  , TO_CHAR( msi.disable_date, ''DD-MON-YYYY'' ) "Disable|Date"   '||
209           '  , DECODE( msi.reservable_type, 1, ''Yes'', 2, ''No'',   '||
210           '            msi.reservable_type) "Reservable|Type"   '||
211           '  , DECODE( msi.locator_type  '||
212           '                ,1, ''None''   '||
213           '                ,2, ''Prespecified''   '||
214           '                ,3, ''Dynamic''   '||
215           '                ,4, ''SubInv Level''   '||
216           '                ,5, ''Item Level'', msi.locator_type)  '||
217           '     || '' (''||msi.locator_type||'')'' "Locator|Control"  '||
218           '  , DECODE( msi.availability_type  '||
219           '              ,1, ''Nettable''  '||
220           '              ,2, ''Non-Nettable'',msi.availability_type ) "Availability|Type"  '||
221           '  , DECODE( msi.inventory_atp_code, 1, ''Included''  '||
222           '                                  , 2, ''Not included''  '||
223           '          , msi.inventory_atp_code ) "Include|in ATP"  '||
224           '  , DECODE( msi.asset_inventory, 1, ''Yes'', 2, ''No'',  '||
225           '            msi.asset_inventory ) "Asset|Inventory"  '||
226           '  , DECODE( msi.quantity_tracked, 1, ''Yes'', 2, ''No'',  '||
227           '            msi.quantity_tracked ) "Quantity|Tracked"   '||
228           '  , default_cost_group_id "Default|Cost Group Id" '||
229           '  ,  DECODE( NVL( subinventory_type, 1 ), 1, ''Storage'', 2,''Receiving'', subinventory_type ) "Type"  '||
230           'FROM mtl_secondary_inventories msi   '||
231           'WHERE (msi.organization_id, msi.secondary_inventory_name ) IN  '||
232           '   ( SELECT mti.organization_id, NVL(mti.subinventory_code,-99)'||
233           '          FROM mtl_transactions_interface mti  ';
234 if l_org_id is not null then
235    sqltxt :=sqltxt||' WHERE mti.organization_id =  '||l_org_id;
236    if l_proc_flag is not null then
237    sqltxt :=sqltxt||' and  mti.process_flag = '||l_proc_flag;
238    end if;
239 elsif l_proc_flag is not null then
240    sqltxt :=sqltxt||' where  mti.process_flag = '||l_proc_flag;
241 end if;
242 sqltxt := sqltxt||'UNION  '||
243           'SELECT NVL( mti.transfer_organization, mti.organization_id )  '||
244           '      ,NVL( mti.transfer_subinventory,-99 ) '||
245           'FROM mtl_transactions_interface mti  ';
246 if l_org_id is not null then
247    sqltxt :=sqltxt||' WHERE mti.organization_id =  '||l_org_id;
248    if l_proc_flag is not null then
249    sqltxt :=sqltxt||' and  mti.process_flag = '||l_proc_flag;
250    end if;
251 elsif l_proc_flag is not null then
252    sqltxt :=sqltxt||' where  mti.process_flag = '||l_proc_flag;
253 end if;
254 
255 sqltxt := sqltxt||') '||
256           ' ORDER BY secondary_inventory_name';
257 sqltxt := 'select * from ('||sqltxt||') WHERE ROWNUM <= '||row_limit;
258 
259 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, 'Subinventories Associated with Pending Txns in MTI'||reportStr);
260 
261 statusStr := 'SUCCESS';
262 isFatal := 'FALSE';
263 
264 sqltxt := 'SELECT mp.organization_code "Organization|Code"  '||
265           '    , mti.organization_id "Organization|Id"  '||
266           '    , DECODE( process_flag, 1, ''Ready''  '||
267           '                          , 2, ''Not Ready''  '||
268           '                          , 3, ''Error''  '||
269           '            , process_flag )   '||
270           '        || '' ('' ||process_flag|| '')'' "Process Flag"             '||
271           '    , DECODE( NVL( lock_flag, 2) , 1,''Locked''  '||
272           '                                 , 2, ''Not Locked'', lock_flag)  '||
273           '      || '' ('' || lock_flag || '')'' "Lock Flag"  '||
274           '    , COUNT(*) "Count"  '||
275           ' FROM mtl_transactions_interface mti  '||
276           '    , mtl_parameters mp  '||
277           'WHERE mti.organization_id = mp.organization_id(+)  ';
278 if l_proc_flag is not null then
279    sqltxt :=sqltxt||' and  mti.process_flag = '||l_proc_flag;
280 end if;
281 
282 sqltxt :=sqltxt||' GROUP BY mp.organization_code, mti.organization_id  '||
283           '       , process_flag, lock_flag  '||
284           ' ORDER BY mp.organization_code, mti.organization_id  '||
285           '       , process_flag, lock_flag';
286 
287 
288 sqltxt := 'select * from ('||sqltxt||') WHERE ROWNUM <= '||row_limit;
289 
290 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, 'Count ALL Inventory organizations in MTI'||reportStr);
291 
292 statusStr := 'SUCCESS';
293 isFatal := 'FALSE';
294 
295 sqltxt := 'SELECT COUNT(*)  '||
296           '  FROM mtl_transactions_interface  ';
297 if l_org_id is not null then
298    sqltxt :=sqltxt||' WHERE organization_id =  '||l_org_id;
299    if l_proc_flag is not null then
300    sqltxt :=sqltxt||' and  process_flag = '||l_proc_flag;
301    end if;
302 elsif l_proc_flag is not null then
303    sqltxt :=sqltxt||' where  process_flag = '||l_proc_flag;
304 end if;
305 
306 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, 'Number of Transaction stuck in MTI'||reportStr);
307 
308 statusStr := 'SUCCESS';
309 isFatal := 'FALSE';
310 
311 
312 sqltxt := 'SELECT COUNT(*) "Count"  '||
313           '   , DECODE( process_flag, 1, ''Ready''  '||
314           '                         , 2, ''Not Ready''  '||
315           '                         , 3, ''Error''  '||
316           '           , process_flag )   '||
317           '   || '' ('' ||process_flag|| '')'' "Process Flag" '||
318           'FROM mtl_transactions_interface  ';
319 if l_org_id is not null then
320    sqltxt :=sqltxt||' WHERE organization_id =  '||l_org_id;
321 end if;
322 sqltxt := sqltxt||' GROUP BY process_flag  '||
323           ' ORDER BY COUNT(*) , process_flag ';
324 
325 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, 'Distinct process_flag for All Txns in MTI'||reportStr);
326 
327 statusStr := 'SUCCESS';
328 isFatal := 'FALSE';
329 
330 sqltxt := 'SELECT COUNT(*) "Count"  '||
331           '    , DECODE( NVL( lock_flag, 2) , ''1'',''Locked'', 2, ''Not Locked'', lock_flag)  '||
332           '      || '' ('' || lock_flag || '')'' "Lock Flag"  '||
333           ' FROM mtl_transactions_interface mti  ';
334 if l_org_id is not null then
335    sqltxt :=sqltxt||' WHERE mti.organization_id =  '||l_org_id;
336 end if;
337 sqltxt := sqltxt||' GROUP BY lock_flag  '||
338           ' ORDER BY COUNT(*) DESC, lock_flag ';
339 
340 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, 'Distinct lock_flag for All Txns in MTI'||reportStr);
341 
342 statusStr := 'SUCCESS';
343 isFatal := 'FALSE';
344 
345 sqltxt := 'SELECT COUNT(*) "Count"  '||
346           '    , transaction_mode_desc || '' ('' ||transaction_mode|| '')'' "Transaction Mode"  '||
347           ' FROM mtl_transactions_interface_v  ';
348 if l_org_id is not null then
349    sqltxt :=sqltxt||' WHERE organization_id =  '||l_org_id;
350 end if;
351 sqltxt := sqltxt||' GROUP BY transaction_mode_desc, transaction_mode  '||
352           ' ORDER BY COUNT(*) , transaction_mode_desc, transaction_mode ';
353 
354 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, 'Distinct transaction_mode for All Txns in MTI'||reportStr);
355 
356 statusStr := 'SUCCESS';
357 isFatal := 'FALSE';
358 
359 sqltxt := 'SELECT COUNT(*) "Count"  '||
363    sqltxt :=sqltxt||' where mti.organization_id =  '||l_org_id;
360           '     , transaction_type_name ||'' ( ''||transaction_type_id||'' )'' "Txn Type (Id)"  '||
361           ' FROM mtl_transactions_interface_v mti  ';
362 if l_org_id is not null then
364 end if;
365 sqltxt := sqltxt||'GROUP BY transaction_type_name, transaction_type_id  '||
366           'ORDER BY COUNT(*) DESC, transaction_type_name, transaction_type_id';
367 
368 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, 'Distinct transaction_type for All Txns in MTI'||reportStr);
369 
370 statusStr := 'SUCCESS';
371 isFatal := 'FALSE';
372 
373 sqltxt := 'SELECT msni.transaction_interface_id "Txn|Interface Id"  '||
374           '    , mti.inventory_item_id "Item Id"  '||
375           '    , msni.fm_serial_number "From|Serial#"  '||
376           '    , msni.to_serial_number "To|Serial#"  '||
377           '    , msni.error_code "Error Code"  '||
378           '    , msni.parent_serial_number "Parent|Serial#"  '||
379           ' FROM mtl_transactions_interface mti  '||
380           '    , mtl_serial_numbers_interface msni  '||
381           'WHERE NVL( mti.transaction_interface_id, -999 ) = msni.transaction_interface_id ';
382 if l_org_id is not null then
383    sqltxt :=sqltxt||' and mti.organization_id =  '||l_org_id;
384 end if;
385 if l_proc_flag is not null then
386    sqltxt :=sqltxt||' and  mti.process_flag = '||l_proc_flag;
387 end if;
388 sqltxt :=sqltxt||'ORDER BY mti.transaction_interface_id';
389 
390 sqltxt := 'select * from ('||sqltxt||') WHERE ROWNUM <= '||row_limit;
391 
392 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, 'Serial Number Information from Table MSNI'||reportStr);
393 
394 statusStr := 'SUCCESS';
395 isFatal := 'FALSE';
396 
397 sqltxt := 'SELECT mif.item_number  '||
398           '       ||'' (''|| msn.inventory_item_id||'')'' "Item (Id)"  '||
399           '    , msn.serial_number "Serial|Number"  '||
400           '    , ml.meaning  '||
401           '       ||'' (''||msn.current_status||'')'' "Current|Status"  '||
402           '    , msn.group_mark_id "Group|Mark Id"  '||
403           '    , msn.line_mark_id "Line|Mark Id"  '||
404           '    , msn.lot_line_mark_id "Lot Line|Mark Id"  '||
405           '    , mp.organization_Code "Current|Org Code"  '||
406           '    , msn.current_organization_id "Current|Org Id"  '||
407           '    , msn.current_subinventory_code "Current|Subinventory"  '||
408           '    , msn.current_locator_id "Current|Locator Id"  '||
409           '    , mil.concatenated_segments "Current|Locator"  '||
410           '    , mil.description "Current|Locator Desc"  '||
411           ' FROM mtl_serial_numbers msn, mtl_item_flexfields mif  '||
412           '    , mtl_parameters mp, mtl_item_locations_kfv mil  '||
413           '    , mfg_lookups ml  '||
414           'WHERE msn.inventory_item_id = mif.inventory_item_id(+)  '||
415           '  AND msn.current_organization_id = mif.organization_id  '||
416           '  AND msn.current_organization_id = mp.organization_id(+)  '||
417           '  AND msn.current_locator_id = mil.inventory_location_id(+)  '||
418           '  AND msn.current_organization_id = mil.organization_id(+)  '||
419           '  AND msn.current_status = ml.lookup_code(+)  '||
420           '  AND ''SERIAL_NUM_STATUS'' = ml.lookup_type(+)  '||
421           '  AND msn.inventory_item_id IN  '||
422           '     ( SELECT DISTINCT( inventory_item_id )  '||
423           '         FROM mtl_transactions_interface mti  ';
424 if l_org_id is not null then
425    sqltxt :=sqltxt||' WHERE mti.organization_id =  '||l_org_id;
426    if l_proc_flag is not null then
427    sqltxt :=sqltxt||' and  mti.process_flag = '||l_proc_flag;
428    end if;
429 elsif l_proc_flag is not null then
430    sqltxt :=sqltxt||' where  mti.process_flag = '||l_proc_flag;
431 end if;
432 sqltxt :=sqltxt||') ORDER BY mif.item_number, msn.serial_number';
433 
434 sqltxt := 'select * from ('||sqltxt||') WHERE ROWNUM <= '||row_limit;
435 
436 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, 'Serial Number Information from MSN for Pending Txns in MTI'||reportStr);
437 
438 statusStr := 'SUCCESS';
439 isFatal := 'FALSE';
440 
441 sqltxt := 'SELECT COUNT(*)  '||
442           '  FROM mtl_transactions_interface mti  '||
443           '     , mtl_system_items_b msib  '||
444           ' WHERE mti.organization_id = msib.organization_id  '||
445           '   AND mti.inventory_item_id = msib.inventory_item_id  '||
446           '   AND msib.serial_number_control_code > 1 ';
447 if l_org_id is not null then
448    sqltxt :=sqltxt||' and mti.organization_id =  '||l_org_id;
449 end if;
450 
451 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, 'Number of Pending txns that refer to a serial-controlled item'||reportStr);
452 
453 statusStr := 'SUCCESS';
454 isFatal := 'FALSE';
455 
456 sqltxt := 'SELECT COUNT(*)  '||
457           '  FROM mtl_transactions_interface mti  '||
458           '     , mtl_system_items_b msib  '||
459           ' WHERE mti.organization_id = msib.organization_id  '||
460           '   AND mti.inventory_item_id = msib.inventory_item_id  '||
461           '   AND msib.lot_control_code = 2 ';
462 if l_org_id is not null then
463    sqltxt :=sqltxt||' and mti.organization_id =  '||l_org_id;
464 end if;
465 
466 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, 'Number of Pending txns that refer to a lot-controlled item'||reportStr);
467 
468 statusStr := 'SUCCESS';
469 isFatal := 'FALSE';
470 
471 sqltxt := 'SELECT mtli.transaction_interface_id "Txn|Interface Id"  '||
472           '   , mti.inventory_item_id "Item Id"  '||
473           '   , mtli.transaction_quantity "Txn Qty"  '||
474           '   , mtli.primary_quantity "Primary|Txn Qty"  '||
475           '   , mtli.lot_number "Lot|Number"  '||
476           '   , mtli.lot_expiration_date "Lot Expiration|Date"  '||
480           'FROM mtl_transactions_interface mti  '||
477           '   , mtli.error_code "Lot Error Code"  '||
478           '   , mtli.serial_transaction_temp_id "Serial Txn|Temp Id"  '||
479           '   , mtli.process_flag "Process|Flag"  '||
481           '   , mtl_transaction_lots_interface mtli  '||
482           'WHERE NVL( mti.transaction_interface_id, -999 ) = mtli.transaction_interface_id ';
483 if l_org_id is not null then
484    sqltxt :=sqltxt||' and mti.organization_id =  '||l_org_id;
485 end if;
486 
487 if l_proc_flag is not null then
488    sqltxt :=sqltxt||' and mti.process_flag = '||l_proc_flag;
489 end if;
490 sqltxt :=sqltxt||'ORDER BY mtli.transaction_interface_id';
491 
492 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, 'Lot Information form MTLI'||reportStr);
493 
494 statusStr := 'SUCCESS';
495 isFatal := 'FALSE';
496 
497 sqltxt := 'SELECT COUNT(*)  '||
498           ' FROM mtl_transactions_interface mti  '||
499           '    , mtl_system_items_b msib  '||
500           'WHERE mti.organization_id = msib.organization_id  '||
501           '  AND mti.inventory_item_id = msib.inventory_item_id  '||
502           '  AND msib.revision_qty_control_code = 2 ';
503 if l_org_id is not null then
504    sqltxt :=sqltxt||' and mti.organization_id =  '||l_org_id;
505 end if;
506 
507 if l_proc_flag is not null then
508    sqltxt :=sqltxt||' and mti.process_flag = '||l_proc_flag;
509 end if;
510 
511 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, 'Number of Pending txns that refer to a revision-controlled item'||reportStr);
512 
513 statusStr := 'SUCCESS';
514 isFatal := 'FALSE';
515 sqltxt := 'SELECT mif.item_number "Item|Number"  '||
516           '    , mir.inventory_item_id "Item Id"  '||
517           '    , mir.revision "Revision"  '||
518           '    , mir.change_notice "Change Notice"  '||
519           '    , TO_CHAR( mir.ecn_initiation_date, ''DD-MON-RR'' ) "ECN Initiation|Date"  '||
520           '    , TO_CHAR( mir.implementation_date, ''DD-MON-RR'' ) "Implementation|Date"  '||
521           '    , TO_CHAR( mir.effectivity_date, ''DD-MON-RR'' ) "Effectivity|Date"  '||
522           ' FROM mtl_item_revisions mir, mtl_item_flexfields mif  '||
523           'WHERE mir.organization_id = mif.organization_id  '||
524           '  AND mir.inventory_item_id = mif.inventory_item_id(+)  '||
525           '  AND mif.revision_qty_control_code = ''2''  ';
526 if l_org_id is not null then
527    sqltxt :=sqltxt||' and mir.organization_id =  '||l_org_id;
528 end if;
529 sqltxt :=sqltxt||'  AND mir.inventory_item_id IN  '||
530           '      ( SELECT DISTINCT( inventory_item_id )  '||
531           '          FROM mtl_transactions_interface mti  ';
532 if l_org_id is not null then
533    sqltxt :=sqltxt||' WHERE mti.organization_id =  '||l_org_id;
534    if l_proc_flag is not null then
535    sqltxt :=sqltxt||' and  mti.process_flag = '||l_proc_flag;
536    end if;
537 elsif l_proc_flag is not null then
538    sqltxt :=sqltxt||' where  mti.process_flag = '||l_proc_flag;
539 end if;
540 sqltxt :=sqltxt||'      )'||
541           '   ORDER BY mif.item_number, mir.revision';
542 
543 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, 'Revision Information'||reportStr);
544 
545 statusStr := 'SUCCESS';
546 isFatal := 'FALSE';
547 
548 
549 sqltxt := 'SELECT DISTINCT mif.item_number "Item Number"  '||
550           '     ,mti.inventory_item_id "Item Id"   '||
551           '     ,primary_uom_code "Primary|UoM"   '||
552           '     ,mif.inventory_item_flag "Inventory|Item Flag"   '||
553           '     ,mif.stock_enabled_flag "Stock|Flag"   '||
554           '     ,mif.mtl_transactions_enabled_flag "Transactable|Flag"   '||
555           '     ,mif.costing_enabled_flag "Costing|Flag"   '||
556           '     ,mif.inventory_asset_flag "Inventory|Asset Flag"   '||
557           '     ,DECODE( mif.lot_control_code, 1, ''N'' , 2, ''Y''   '||
558           '            , mif.lot_control_code )  '||
559           '       || '' (''||mif.lot_control_code||'')'' "Lot|Control"   '||
560           '     ,ml.meaning||'' (''||mif.serial_number_control_code||'')'' "Serial|Control"  '||
561           '     ,DECODE( TO_CHAR(mif.revision_qty_control_code) , ''1'', ''No''   '||
562           '                                                     , ''2'', ''Yes''   '||
563           '             , mif.revision_qty_control_code )  '||
564           '       || '' (''||mif.revision_qty_control_code||'')'' "Revision|Control"  '||
565           '     ,DECODE( TO_CHAR(mif.location_control_code)  '||
566           '                          , ''1'', ''None''  '||
567           '                          , ''2'', ''Prespecified''  '||
568           '                          , ''3'', ''Dynamic''  '||
569           '                          , ''4'', ''Determine at Subinv Level''  '||
570           '                          , ''5'', ''Determine at Item Level''  '||
571           '                   , mif.location_control_code )  '||
572           '       || '' (''||mif.location_control_code||'')'' "Location|Control"  '||
573           '     ,DECODE( mif.restrict_subinventories_code, 1, ''Yes''  '||
574           '                                              , 2, ''No''  '||
575           '             ,mif.restrict_subinventories_code ) "Restricted|Subinvs"  '||
576           '     ,DECODE( mif.restrict_locators_code, 1, ''Yes'', 2, ''No''  '||
577           '             ,mif.restrict_locators_code )  '||
578           '       || '' (''||mif.restrict_locators_code||'')'' "Restricted|Locators"  '||
579           ' FROM mtl_transactions_interface mti  '||
580           '    , mtl_item_flexfields mif  '||
581           '    , mfg_lookups ml  '||
582           'WHERE mti.organization_id = mif.organization_id  '||
583           '  AND mti.inventory_item_id = mif.inventory_item_id(+)  '||
584           '  AND mif.serial_number_control_code = ml.lookup_code(+)  '||
585           '  AND ''MTL_SERIAL_NUMBER'' = ml.lookup_type(+) ';
586 if l_org_id is not null then
587    sqltxt :=sqltxt||' and mti.organization_id =  '||l_org_id;
588 end if;
589 sqltxt :=sqltxt||'ORDER BY mif.item_number';
590 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, 'Item Associated with Pending Txns in MTI'||reportStr);
591 
592 statusStr := 'SUCCESS';
593 isFatal := 'FALSE';
594 
595 
596 sqltxt := 'SELECT COUNT(*) "Count"  '||
597           '    , error_code "Error Code"  '||
598           '    , error_explanation "Error Explanation"  '||
599           ' FROM mtl_transactions_interface  ';
600 if l_org_id is not null then
601    sqltxt :=sqltxt||' WHERE organization_id =  '||l_org_id;
602 end if;
603 sqltxt :=sqltxt||'GROUP BY error_code, error_explanation  '||
604           'ORDER BY COUNT(*) DESC, error_code, error_explanation ';
605 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, 'Distinct Types of Errors in MTI'||reportStr);
606 
607 statusStr := 'SUCCESS';
608 isFatal := 'FALSE';
609 errStr :='';
610 fixInfo :='';
611 
612 
613 /**
614 else
615  JTF_DIAGNOSTIC_COREAPI.ActionErrorPrint('Invalid Input parameters');
616  statusStr := 'FAILURE';
617  errStr := 'org_id null';
618  fixInfo := 'Org or OrdID input is required ';
619  isFatal := 'SUCCESS';
620 end if;
621 **/
622  -- construct report
623  report := JTF_DIAGNOSTIC_ADAPTUTIL.constructReport(statusStr,errStr,fixInfo,isFatal);
624  reportClob := JTF_DIAGNOSTIC_ADAPTUTIL.getReportClob;
625 END runTest;
626 
627 PROCEDURE getComponentName(name OUT NOCOPY VARCHAR2) IS
628 BEGIN
629 name := 'Open Interface Transactions (MTI)';
630 END getComponentName;
631 
632 PROCEDURE getTestDesc(descStr OUT NOCOPY VARCHAR2) IS
633 BEGIN
634 descStr := 'Pending Transactions in MTI';
635 END getTestDesc;
636 
637 PROCEDURE getTestName(name OUT NOCOPY VARCHAR2) IS
638 BEGIN
639 name := 'Open Interface Transactions (MTI)';
640 END getTestName;
641 
642 PROCEDURE getDependencies (package_names OUT NOCOPY JTF_DIAG_DEPENDTBL) IS
643 tempDependencies JTF_DIAG_DEPENDTBL;
644 
645 BEGIN
646     package_names := JTF_DIAGNOSTIC_ADAPTUTIL.initDependencyTable;
647 END getDependencies;
648 
649 PROCEDURE isDependencyPipelined (str OUT NOCOPY VARCHAR2) IS
650 BEGIN
651   str := 'FALSE';
652 END isDependencyPipelined;
653 
654 
655 PROCEDURE getOutputValues(outputValues OUT NOCOPY JTF_DIAG_OUTPUTTBL) IS
656   tempOutput JTF_DIAG_OUTPUTTBL;
657 BEGIN
658   tempOutput := JTF_DIAGNOSTIC_ADAPTUTIL.initOutputTable;
659   outputValues := tempOutput;
660 EXCEPTION
661  when others then
662  outputValues := JTF_DIAGNOSTIC_ADAPTUTIL.initOutputTable;
663 END getOutputValues;
664 
665 
666 PROCEDURE getDefaultTestParams(defaultInputValues OUT NOCOPY JTF_DIAG_INPUTTBL) IS
667 tempInput JTF_DIAG_INPUTTBL;
668 BEGIN
669 tempInput := JTF_DIAGNOSTIC_ADAPTUTIL.initinputtable;
670 tempInput := JTF_DIAGNOSTIC_ADAPTUTIL.addInput(tempInput,'OrgId','LOV-oracle.apps.inv.diag.lov.OrganizationLov');
671 tempInput := JTF_DIAGNOSTIC_ADAPTUTIL.addInput(tempInput,'ErrorFlag','LOV-oracle.apps.inv.diag.lov.MTIErroredAllLov');
672 defaultInputValues := tempInput;
673 EXCEPTION
674 when others then
675 defaultInputValues := JTF_DIAGNOSTIC_ADAPTUTIL.initinputtable;
676 END getDefaultTestParams;
677 
678 Function getTestMode return INTEGER IS
679 BEGIN
680  return JTF_DIAGNOSTIC_ADAPTUTIL.ADVANCED_MODE;
681 
682 END getTestMode;
683 
684 END;