[Home] [Help]
PACKAGE BODY: APPS.INV_DIAG_PI_GEN
Source
1 PACKAGE BODY INV_DIAG_PI_GEN AS
2 /* $Header: INVDA06B.pls 120.0.12000000.1 2007/06/22 17:13:42 musinha noship $ */
3
4 ------------------------------------------------------------
5 -- procedure to initialize test datastructures
6 -- executed prior to test run leave body as null otherwize
7 ------------------------------------------------------------
8 PROCEDURE init IS
9 BEGIN
10 -- test writer could insert special setup code here
11 null;
12 END init;
13
14 ------------------------------------------------------------
15 -- procedure to cleanup any test datastructures that were setup in the init
16 -- procedure call executes after test run leave body as null otherwize
17 ------------------------------------------------------------
18 PROCEDURE cleanup IS
19 BEGIN
20 -- test writer could insert special cleanup code here
21 NULL;
22 END cleanup;
23
24 ------------------------------------------------------------
25 -- procedure to execute the PLSQL test
26 -- the inputs needed for the test are passed in and a report object and CLOB are
27 -- returned.
28 -- note the way that support API writes to the report CLOB.
29 ------------------------------------------------------------
30 PROCEDURE runtest(inputs IN JTF_DIAG_INPUTTBL,
31 report OUT NOCOPY JTF_DIAG_REPORT,
32 reportClob OUT NOCOPY CLOB) IS
33 reportStr LONG;
34 counter NUMBER;
35 dummy_v2t JTF_DIAGNOSTIC_COREAPI.v2t;
36 c_userid VARCHAR2(50);
37 statusStr VARCHAR2(50);
38 errStr VARCHAR2(4000);
39 fixInfo VARCHAR2(4000);
40 isFatal VARCHAR2(50);
41 dummy_num NUMBER;
42 sqltxt VARCHAR2 (9999);
43 l_phy_inv_id NUMBER;
44 l_org_id NUMBER;
45 l_count NUMBER;
46
47 CURSOR c_phy_inv (cp_n_org_id IN NUMBER, cp_n_phy_inv_id IN NUMBEr) IS
48 SELECT count(*)
49 FROM MTL_PHYSICAL_INVENTORIES
50 WHERE organization_id = cp_n_org_id
51 AND physical_inventory_id = cp_n_phy_inv_id;
52
53
54
55 BEGIN
56 JTF_DIAGNOSTIC_ADAPTUTIL.setUpVars;
57 JTF_DIAGNOSTIC_ADAPTUTIL.addStringToReport('@html');
58 JTF_DIAGNOSTIC_COREAPI.insert_style_sheet;
59
60 l_phy_inv_id := JTF_DIAGNOSTIC_ADAPTUTIL.getInputValue('PhyInvId',inputs);
61 l_org_id := JTF_DIAGNOSTIC_ADAPTUTIL.getInputValue('OrgId',inputs);
62 l_count := 0;
63
64 sqltxt := ' SELECT o.name "Name" ' ||
65 ' , DECODE( o.type#, 9, ''Spec'', 11, ''Body'', o.type# ) "Type" ' ||
66 ' , SUBSTR( s.source, INSTR( s.source, ''$Header'',1 ,1)+9, 12 ) "Filename" ' ||
67 ' , SUBSTR( s.source , ' ||
68 ' INSTR( s.source ,''.'',10,1)+5, ' ||
69 ' INSTR( s.source ,'' '',10,3)- ' ||
70 ' INSTR( s.source ,'' '',10,2) ) "Version" ' ||
71 ' , DECODE( o.status, 0, ''N/A'', 1, ''VALID'', ''INVALID'' ) "Status" ' ||
72 ' FROM sys.source$ s, sys.obj$ o, sys.user$ u ' ||
73 ' WHERE u.name = ''APPS'' ' ||
74 ' AND o.owner# = u.user# ' ||
75 ' AND s.obj# = o.obj# ' ||
76 ' AND s.line = 2 ' ||
77 ' AND o.name IN ( ''INVADPT1'', ' ||
78 ' ''INV_CG_UPGRADE'', ' ||
79 ' ''INV_COST_GROUP_PVT'', ' ||
80 ' ''INV_LPN_TRX_PUB'', ' ||
81 ' ''INV_PHY_INV_LOVS'', ' ||
82 ' ''INV_QUANTITY_TREE_PVT'', ' ||
83 ' ''INV_TRX_MGR'', ' ||
84 ' ''INV_UI_ITEM_SUB_LOC_LOVS'' ) ' ||
85 ' ORDER BY o.name, o.type# ';
86
87 dummy_num := JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,'Version of Inventory Key Packages');
88
89 JTF_DIAGNOSTIC_ADAPTUTIL.addStringToReport(reportClob,' <BR>Important Notes Releated to Physical Inventory');
90 JTF_DIAGNOSTIC_ADAPTUTIL.addStringToReport(reportClob,' <BR> Note , 131795.1, : Inventory Product Information > Physical Inventory ');
91 JTF_DIAGNOSTIC_ADAPTUTIL.addStringToReport(reportClob,' <BR> Note , 69125.1, : Latest Inventory news ');
92 JTF_DIAGNOSTIC_ADAPTUTIL.addStringToReport(reportClob,' <BR> Note , 204946.1, : Manufacturing And Distribution Recommended Patch Strategy ');
93 JTF_DIAGNOSTIC_ADAPTUTIL.addStringToReport(reportClob,' <BR> Note , 246934.1, : White Paper: Understanding Physical Inventory ');
94 JTF_DIAGNOSTIC_ADAPTUTIL.addStringToReport(reportClob,' <BR> Note , 146869.1, : How To Create Physical Inventory TAGs ');
95 JTF_DIAGNOSTIC_ADAPTUTIL.addStringToReport(reportClob,' <BR> Note , 105286.1, : Steps to define and complete a Physical Inventory ');
96 JTF_DIAGNOSTIC_ADAPTUTIL.addStringToReport(reportClob,' <BR> Note , 114296.1, : Physical Inventory FAQ ');
97 JTF_DIAGNOSTIC_ADAPTUTIL.addStringToReport(reportClob,' <BR> Note , 66957.1, : Oracle Inventory Users Guide, Release 11i <BR>');
98
99
100 IF l_org_id IS NOT NULL AND l_phy_inv_id IS NOT NULL THEN
101
102 OPEN c_phy_inv(l_org_id, l_phy_inv_id);
103 FETCH c_phy_inv INTO l_count;
104 CLOSE c_phy_inv;
105
106 END IF;
107
108 IF l_count IS NULL OR l_count <> 1 THEN
109 JTF_DIAGNOSTIC_ADAPTUTIL.addStringToReport(reportClob,'Please execute the report with organization and physical inventory information');
110 JTF_DIAGNOSTIC_COREAPI.errorprint('Error: '|| 'Invalid Physical Inventory Item and Organization Combination');
111 JTF_DIAGNOSTIC_COREAPI.ActionErrorPrint('Please enter correct physical inventory and organization ids');
112 statusStr := 'FAILURE';
113 errStr := 'Invalid Physical Inventory Item and Organization Combination';
114 fixInfo := 'Please enter right combination of Physical Inventory Item and Organization';
115 isFatal := 'FALSE';
116 report := JTF_DIAGNOSTIC_ADAPTUTIL.constructReport(statusStr,errStr,fixInfo,isFatal);
117 reportClob := JTF_DIAGNOSTIC_ADAPTUTIL.getReportClob;
118 RETURN;
119 END IF;
120
121 sqltxt := ' SELECT mif.item_number "Item" ' ||
122 ' ,moq.inventory_item_id "Item Id" ' ||
123 ' ,SUM( moq.transaction_quantity ) "SUM(Txn Qty)" ' ||
124 ' ,SUM( moq.primary_transaction_quantity ) "SUM(Prim Txn Qty)" ' ||
125 ' ,moq.transaction_uom_code "Txn UoM" ' ||
126 ' ,moq.subinventory_code "Subinv" ' ||
127 ' ,moq.locator_id "Locator Id" ' ||
128 ' ,mil.concatenated_segments "Locator" ' ||
129 ' ,mil.description "Locator Desc" ' ||
130 ' ,moq.revision "Revision" ' ||
131 ' ,moq.lot_number "Lot Number" ' ||
132 ' FROM mtl_onhand_quantities_detail moq, mtl_item_flexfields mif ' ||
133 ' , mtl_item_locations_kfv mil ' ||
134 ' WHERE moq.organization_id = ' || l_org_id ||
135 ' AND moq.inventory_item_id = mif.inventory_item_id(+) ' ||
136 ' AND moq.organization_id = mif.organization_id(+) ' ||
137 ' AND moq.organization_id = mil.organization_id(+) ' ||
138 ' AND moq.locator_id = mil.inventory_location_id(+) ' ||
139 ' AND moq.inventory_item_id ' ||
140 ' IN ( SELECT DISTINCT mpa.inventory_item_id ' ||
141 ' FROM mtl_physical_adjustments mpa ' ||
142 ' WHERE mpa.organization_id = ' || l_org_id ||
143 ' AND mpa.physical_inventory_id = ' || l_phy_inv_id ||')' ||
144 ' GROUP BY mif.item_number, moq.inventory_item_id ' ||
145 ' ,moq.transaction_uom_code, moq.subinventory_code, moq.locator_id ' ||
146 ' ,mil.concatenated_segments, mil.description ' ||
147 ' ,moq.revision, moq.lot_number ' ||
148 ' ORDER BY mif.item_number, moq.inventory_item_id ' ||
149 ' ,moq.transaction_uom_code, moq.subinventory_code, moq.locator_id ' ||
150 ' ,mil.concatenated_segments, mil.description ' ||
151 ' ,moq.revision, moq.lot_number ';
152
153 dummy_num := JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,'Onhand information');
154
155 sqltxt := ' SELECT mpa.organization_id ' ||
156 ' , mpa.physical_inventory_id ' ||
157 ' , mpa.adjustment_id ' ||
158 ' , mpa.inventory_item_id ' ||
159 ' , msi.segment1 ' ||
160 ' , mpa.subinventory_name ' ||
161 ' , mpa.serial_number ' ||
162 ' , msi.serial_number_control_code ' ||
163 ' , mpa.revision ' ||
164 ' , mpa.lot_number ' ||
165 ' , mpa.locator_id ' ||
166 ' , mpa.approval_status ' ||
167 ' , mpa.adjustment_quantity ' ||
168 ' FROM mtl_physical_adjustments mpa ' ||
169 ' , mtl_system_items msi ' ||
170 ' WHERE mpa.organization_id = ' || l_org_id ||
171 ' AND mpa.physical_inventory_id = ' || l_phy_inv_id ||
172 ' AND mpa.organization_id = msi.organization_id ' ||
173 ' AND mpa.inventory_item_id = msi.inventory_item_id ' ||
174 ' AND msi.serial_number_control_code IN (2,5) ' ||
175 ' AND mpa.serial_number IS NULL ' ||
176 ' AND ( mpa.approval_status = 1 OR mpa.approval_status IS NULL ) ' ||
177 ' ORDER BY mpa.organization_id, mpa.physical_inventory_id, mpa.adjustment_id ';
178
179 dummy_num := JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,'MPA records for serial controlled items without serial numbers');
180
181 sqltxt := ' SELECT mpa.* ' ||
182 ' FROM mtl_physical_adjustments mpa ' ||
183 ' WHERE mpa.organization_id = ' || l_org_id ||
184 ' AND mpa.physical_inventory_id = ' || l_phy_inv_id ||
185 ' AND mpa.adjustment_quantity <> 0 ' ||
186 ' AND mpa.locator_id IS NOT NULL ' ||
187 ' AND NOT EXISTS ' ||
188 ' ( SELECT 1 ' ||
189 ' FROM mtl_item_locations mil ' ||
190 ' WHERE mil.organization_id = mpa.organization_id ' ||
191 ' AND mil.subinventory_code = mpa.subinventory_name ' ||
192 ' AND mil.inventory_location_id = mpa.locator_id ) ' ||
193 ' ORDER BY mpa.adjustment_id ';
194
195 dummy_num := JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,'MPA records with invalid locator_id in onhand');
196
197 sqltxt := ' SELECT mpa.organization_id ' ||
198 ' , mpa.physical_inventory_id ' ||
199 ' , mpa.adjustment_id ' ||
200 ' , mpa.inventory_item_id ' ||
201 ' , msi.segment1 ' ||
202 ' , mpa.subinventory_name ' ||
203 ' , mpa.lot_number ' ||
204 ' , mpa.serial_number ' ||
205 ' , msi.serial_number_control_code ' ||
206 ' , mpa.revision ' ||
207 ' , mpa.locator_id ' ||
208 ' , mpa.approval_status ' ||
209 ' , mpa.adjustment_quantity ' ||
210 ' FROM mtl_physical_adjustments mpa ' ||
211 ' , mtl_system_items msi ' ||
212 ' WHERE mpa.organization_id = ' || l_org_id ||
213 ' AND mpa.physical_inventory_id = ' || l_phy_inv_id ||
214 ' AND mpa.organization_id = msi.organization_id ' ||
215 ' AND mpa.inventory_item_id = msi.inventory_item_id ' ||
216 ' AND msi.lot_control_code = 2 ' ||
217 ' AND mpa.lot_number IS NULL ' ||
218 ' AND ( mpa.approval_status = 1 OR mpa.approval_status IS NULL ) ' ||
219 ' ORDER BY mpa.organization_id, mpa.physical_inventory_id, mpa.adjustment_id ';
220
221 dummy_num := JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,'MPA records of lot controlled item without lot number');
222
223 sqltxt := ' SELECT physical_adjustment_id ' ||
224 ' , inventory_item_id ' ||
225 ' , subinventory_code ' ||
226 ' , locator_id ' ||
227 ' , revision ' ||
228 ' , primary_quantity ' ||
229 ' , last_update_date ' ||
230 ' FROM mtl_material_transactions ' ||
231 ' WHERE physical_adjustment_id IN ' ||
232 ' ( SELECT physical_adjustment_id ' ||
233 ' FROM mtl_material_transactions ' ||
237 ' GROUP BY physical_adjustment_id ' ||
234 ' WHERE organization_id = ' || l_org_id ||
235 ' AND transaction_source_id = ' || l_phy_inv_id ||
236 ' AND transaction_type_id = 8 ' ||
238 ' HAVING COUNT(*) > 1 ) ';
239
240 dummy_num := JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,'Duplicate Physical Adjustment transactions');
241
242 sqltxt := ' SELECT physical_adjustment_id ' ||
243 ' , inventory_item_id ' ||
244 ' , subinventory_code ' ||
245 ' , locator_id ' ||
246 ' , revision ' ||
247 ' , primary_quantity ' ||
248 ' , last_update_date ' ||
249 ' FROM mtl_material_transactions_temp ' ||
250 ' WHERE organization_id = ' || l_org_id ||
251 ' AND transaction_source_id = ' || l_phy_inv_id ||
252 ' AND transaction_action_id = 8 ' ||
253 ' AND physical_adjustment_id IS NOT NULL ' ||
254 ' AND physical_adjustment_id IN ' ||
255 ' ( SELECT physical_adjustment_id ' ||
256 ' FROM mtl_material_transactions_temp ' ||
257 ' WHERE organization_id = ' || l_org_id ||
258 ' AND transaction_source_id = ' || l_phy_inv_id ||
259 ' AND transaction_type_id = 8 ' ||
260 ' GROUP BY physical_adjustment_id ' ||
261 ' HAVING COUNT(*) > 1 ) ';
262
263 dummy_num := JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,'Duplicate Pending Physical Adjustment transactions');
264
265 sqltxt := ' SELECT physical_adjustment_id ' ||
266 ' , transaction_source_id ' ||
267 ' , inventory_item_id ' ||
268 ' , subinventory_code ' ||
269 ' , locator_id ' ||
270 ' , revision ' ||
271 ' , primary_quantity ' ||
272 ' , last_update_date ' ||
273 ' FROM mtl_material_transactions_temp mmtt ' ||
274 ' WHERE organization_id = ' || l_org_id ||
275 ' AND transaction_source_id = ' || l_phy_inv_id ||
276 ' AND transaction_action_id = 8 ' ||
277 ' AND physical_adjustment_id IS NOT NULL ' ||
278 ' AND EXISTS ' ||
279 ' ( SELECT 1 ' ||
280 ' FROM mtl_material_transactions mmt ' ||
281 ' WHERE mmt.organization_id = ' || l_org_id ||
282 ' AND mmt.transaction_source_id = ' || l_phy_inv_id ||
283 ' AND mmt.physical_adjustment_id IS NOT NULL ' ||
284 ' AND mmt.physical_adjustment_id = mmtt.physical_adjustment_id ' ||
285 ' AND mmt.transaction_source_id = mmtt.transaction_source_id ' ||
286 ' AND mmt.transaction_action_id = mmtt.transaction_action_id ) ';
287
288 dummy_num := JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,'Pending Physical Adjustment transactions duplicate of completed transaction');
289
290 sqltxt := ' SELECT mpa.adjustment_id, mpa.physical_inventory_id ' ||
291 ' , mpa.inventory_item_id, mpa.organization_id, mpa.count_quantity ' ||
292 ' , SUM( mpit.tag_quantity_at_standard_uom ) total_tag ' ||
293 ' FROM mtl_physical_inventory_tags mpit ' ||
294 ' , mtl_physical_adjustments mpa ' ||
295 ' WHERE mpa.organization_id = ' || l_org_id ||
296 ' AND mpa.physical_inventory_id = ' || l_phy_inv_id ||
297 ' AND mpa.physical_inventory_id = mpit.physical_inventory_id ' ||
298 ' AND mpa.adjustment_id = mpit.adjustment_id ' ||
299 ' AND NVL( mpa.approval_status, 1 ) = 1 ' ||
300 ' AND mpit.void_flag = 2 ' ||
301 ' GROUP BY mpa.adjustment_id, mpa.physical_inventory_id ' ||
302 ' , mpa.inventory_item_id, mpa.organization_id, mpa.count_quantity ' ||
303 ' HAVING count_quantity <> SUM( mpit.tag_quantity_at_standard_uom ) ';
304
305 dummy_num := JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,'Mismatch between MPA count and Standard UOM count');
306
307 sqltxt := ' SELECT mpi.physical_inventory_name ' ||
308 ' , mpi.description ' ||
309 ' , mpi.physical_inventory_date ' ||
310 ' , mpi.approval_required ' ||
311 ' , ml.meaning ' ||
312 ' , mpi.approval_tolerance_pos ' ||
313 ' , mpi.approval_tolerance_neg ' ||
314 ' , mpi.cost_variance_pos ' ||
315 ' , mpi.cost_variance_neg ' ||
316 ' , mpi.all_subinventories_flag ' ||
317 ' , mpi.snapshot_complete ' ||
318 ' , mpi.last_adjustment_date ' ||
319 ' , mpi.adjustments_posted ' ||
320 ' , mpi.freeze_date ' ||
321 ' , mpi.dynamic_tag_entry_flag ' ||
322 ' , mpi.total_adjustment_value ' ||
323 ' , mpi.next_tag_number ' ||
324 ' , mpi.tag_number_increments ' ||
325 ' , mpi.number_of_skus ' ||
326 ' FROM mtl_physical_inventories_v mpi ' ||
327 ' , mfg_lookups ml ' ||
328 ' WHERE mpi.organization_id = ' || l_org_id ||
329 ' AND mpi.physical_inventory_id = ' || l_phy_inv_id ||
330 ' AND mpi.approval_required = ml.lookup_code(+) ' ||
331 ' AND ml.lookup_type = ''MTL_APPROVAL_REQ'' ';
332
333 dummy_num := JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,'Physical inventory information');
334
335 sqltxt := ' SELECT mpit.tag_number ' ||
336 ' ,mpit.tag_id ' ||
337 ' ,mpit.void_flag ' ||
338 ' ,mpit.adjustment_id ' ||
339 ' ,mpit.inventory_item_id ' ||
340 ' ,mpit.tag_quantity ' ||
341 ' ,mpit.tag_uom ' ||
342 ' ,mpit.tag_quantity_at_standard_uom ' ||
343 ' ,mpit.standard_uom ' ||
347 ' ,mpit.lot_number ' ||
344 ' ,mpit.subinventory ' ||
345 ' ,mpit.locator_id ' ||
346 ' ,mpit.lot_serial_controls ' ||
348 ' ,mpit.lot_expiration_date ' ||
349 ' ,mpit.revision ' ||
350 ' ,mpit.serial_num ' ||
351 ' ,mpit.parent_lpn_id ' ||
352 ' ,mpit.outermost_lpn_id ' ||
353 ' ,mpit.cost_group_id ' ||
354 ' FROM mtl_physical_inventory_tags mpit ' ||
355 ' WHERE mpit.organization_id = ' || l_org_id ||
356 ' AND mpit.physical_inventory_id = ' || l_phy_inv_id ||
357 ' ORDER BY mpit.tag_number ' ||
358 ' ';
359
360 dummy_num := JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,'Physical inventory tag information');
361
362 sqltxt := ' SELECT DECODE( approval_status, 1, ''Approved'' ' ||
363 ' , 2, ''Rejected'' ' ||
364 ' , 3, ''Posted'' ' ||
365 ' , NULL, ''No Status entered'' ' ||
366 ' , approval_status ) || '' ( '' ||approval_status||'' )'' "Approval Status ( Id )" ' ||
367 ' , COUNT(*) "Count" ' ||
368 ' FROM mtl_physical_adjustments ' ||
369 ' WHERE organization_id = ' || l_org_id ||
370 ' AND physical_inventory_id = ' || l_phy_inv_id ||
371 ' GROUP BY approval_status ' ||
372 ' ORDER BY approval_status ';
373
374 dummy_num := JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,'Count of distinct adjustment approval_status');
375
376 sqltxt := ' SELECT mmt.transaction_id ' ||
377 ' ,mmt.costed_flag ' ||
378 ' ,mmt.last_update_date ' ||
379 ' ,mmt.inventory_item_id ' ||
380 ' ,mmt.transaction_quantity ' ||
381 ' ,mmt.transaction_uom ' ||
382 ' ,mmt.revision ' ||
383 ' FROM mtl_material_transactions mmt ' ||
384 ' WHERE mmt.organization_id = ' || l_org_id ||
385 ' AND mmt.transaction_source_id = ' || l_phy_inv_id ||
386 ' AND mmt.transaction_type_id = 8 ' ||
387 ' ORDER BY mmt.transaction_id ' ||
388 ' ';
389
390 dummy_num := JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,'Adjustment transaction information');
391
392 sqltxt := ' SELECT mpa.adjustment_id ' ||
393 ' ,mpa.approval_status ' ||
394 ' ,mpa.inventory_item_id ' ||
395 ' ,mpa.subinventory_name ' ||
396 ' ,mpa.locator_id ' ||
397 ' ,mpa.system_quantity ' ||
398 ' ,mpa.count_quantity ' ||
399 ' ,mpa.adjustment_quantity ' ||
400 ' ,mpa.revision ' ||
401 ' ,mpa.lot_number ' ||
402 ' ,mpa.lot_expiration_date ' ||
403 ' ,mpa.lot_serial_controls ' ||
404 ' ,mpa.serial_number ' ||
405 ' ,mpa.actual_cost ' ||
406 ' ,mpa.cost_group_id ' ||
407 ' ,mpa.automatic_approval_code ' ||
408 ' ,mpa.gl_adjust_account ' ||
409 ' ,mpa.parent_lpn_id ' ||
410 ' ,mpa.outermost_lpn_id ' ||
411 ' FROM mtl_physical_adjustments mpa ' ||
412 ' WHERE mpa.organization_id = ' || l_org_id ||
413 ' AND mpa.physical_inventory_id = ' || l_phy_inv_id ||
414 ' ORDER BY mpa.adjustment_id ' ||
415 ' ';
416
417 dummy_num := JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,'Adjustment information');
418
419 sqltxt := ' SELECT mmtt.transaction_temp_id "Txn|Temp Id" ' ||
420 ' , transaction_header_id "Txn|Header Id" ' ||
421 ' , mmtt.source_code "Source Code" ' ||
422 ' , mif.item_number ||'' (''|| mmtt.inventory_item_id ||'')'' "Item (Id)" ' ||
423 ' , subinventory_code "Subinv" ' ||
424 ' , locator_id "Stock Locator" ' ||
425 ' , revision "Rev" ' ||
426 ' , TO_CHAR( transaction_date, ''DD-MON-RR'' ) "Txn Date" ' ||
427 ' , mmtt.transaction_quantity "Txn Qty" ' ||
428 ' , mmtt.primary_quantity "Primary|Qty" ' ||
429 ' , transaction_uom "Txn UoM" ' ||
430 ' , transaction_cost "Txn Cost" ' ||
431 ' , tt.transaction_type_name ||'' (''||mmtt.transaction_type_id||'')'' "Txn Type (Id)" ' ||
432 ' , ml.meaning ||'' (''|| mmtt.transaction_action_id ||'')'' "Txn Action (Id)" ' ||
433 ' , st.transaction_source_type_name ||'' (''|| mmtt.transaction_source_type_id ||'')'' "Txn Source Type (Id)" ' ||
434 ' , transaction_source_id "Txn Source Id" ' ||
435 ' , process_flag "Process|Flag" ' ||
436 ' , lock_flag "Lock|Flag" ' ||
437 ' , DECODE( transaction_mode,1, ''Online'' ' ||
438 ' ,2, ''Concurrent'' ' ||
439 ' ,3, ''Background'' ' ||
440 ' , transaction_mode ) || '' ('' || transaction_mode ||'')'' "Transaction|Mode" ' ||
441 ' , mmtt.request_id "Request|Id" ' ||
442 ' , TO_CHAR( mmtt.last_update_date, ''DD-MON-RR HH24:MI'' ) "Last updated" ' ||
443 ' , transfer_subinventory "Transfer|Subinv" ' ||
444 ' , transfer_to_location "Transfer to|Location" ' ||
445 ' , mmtt.error_code "Error|Code" ' ||
446 ' , error_explanation "Error|Explanation" ' ||
447 ' FROM mtl_material_transactions_temp mmtt ' ||
448 ' , mtl_item_flexfields mif ' ||
449 ' , mtl_transaction_types tt ' ||
450 ' , mtl_txn_source_types st ' ||
451 ' , mfg_lookups ml ' ||
452 ' WHERE mmtt.organization_id = ' || l_org_id ||
453 ' AND mmtt.transaction_source_id = ' || l_phy_inv_id ||
457 ' AND mmtt.transaction_type_id = tt.transaction_type_id(+) ' ||
454 ' AND mmtt.transaction_type_id = 8 ' ||
455 ' AND mmtt.inventory_item_id = mif.inventory_item_id(+) ' ||
456 ' AND mmtt.organization_id = mif.organization_id(+) ' ||
458 ' AND mmtt.transaction_source_type_id = st.transaction_source_type_id(+) ' ||
459 ' AND mmtt.transaction_action_id = ml.lookup_code ' ||
460 ' AND ml.lookup_type = ''MTL_TRANSACTION_ACTION'' ' ||
461 ' ORDER BY mmtt.transaction_temp_id, transaction_header_id ';
462
463 dummy_num := JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,'Pending Adjustment transactions');
464
465 sqltxt := ' SELECT transaction_temp_id "Txn|Temp Id" ' ||
466 ' , transaction_header_id "Txn|Header Id" ' ||
467 ' , source_code "Source|Code" ' ||
468 ' , mif.item_number ||'' (''|| mmtt.inventory_item_id ||'')'' "Item (Id)" ' ||
469 ' , subinventory_code "Subinv" ' ||
470 ' , locator_id "Locator|Id" ' ||
471 ' , revision "Rev" ' ||
472 ' , TO_CHAR( transaction_date, ''DD-MON-RR'' ) "Txn Date" ' ||
473 ' , transaction_quantity "Txn Qty" ' ||
474 ' , primary_quantity "Primary|Qty" ' ||
475 ' , transaction_uom "Txn UoM" ' ||
476 ' , transaction_cost "Txn Cost" ' ||
477 ' , tt.transaction_type_name ||'' (''||mmtt.transaction_type_id||'')'' "Txn Type (Id)" ' ||
478 ' , ml.meaning ||'' (''|| mmtt.transaction_action_id ||'')'' "Txn Action (Id)" ' ||
479 ' , st.transaction_source_type_name ||'' (''|| mmtt.transaction_source_type_id ||'')'' "Txn Source Type (Id)" ' ||
480 ' , transaction_source_id "Txn Source|Id" ' ||
481 ' , process_flag "Process|Flag" ' ||
482 ' , lock_flag "Lock|Flag" ' ||
483 ' , DECODE( transaction_mode,1,''Online'' ' ||
484 ' ,2,''Concurrent'' ' ||
485 ' ,3,''Background'' ' ||
486 ' , transaction_mode ) || '' ('' || transaction_mode ||'')'' "Transaction|Mode" ' ||
487 ' , mmtt.request_id "Request|Id" ' ||
488 ' , TO_CHAR(mmtt.last_update_date,''DD-MON-RR HH24:MI'') "Last updated" ' ||
489 ' , transfer_subinventory "Transfer|Subinv" ' ||
490 ' , transfer_to_location "Transfer to|Location" ' ||
491 ' , error_code "Error|Code" ' ||
492 ' , error_explanation "Error|Explanation" ' ||
493 ' FROM mtl_material_transactions_temp mmtt ' ||
494 ' , mtl_item_flexfields mif ' ||
495 ' , mtl_transaction_types tt ' ||
496 ' , mtl_txn_source_types st ' ||
497 ' , mfg_lookups ml ' ||
498 ' WHERE mmtt.organization_id = ' || l_org_id ||
499 ' AND mmtt.transaction_type_id != 8 AND mmtt.inventory_item_id = mif.inventory_item_id(+) ' ||
500 ' AND mmtt.organization_id = mif.organization_id(+) ' ||
501 ' AND mmtt.transaction_type_id = tt.transaction_type_id(+) ' ||
502 ' AND mmtt.transaction_source_type_id = st.transaction_source_type_id(+) ' ||
503 ' AND mmtt.transaction_action_id = ml.lookup_code ' ||
504 ' AND ml.lookup_type = ''MTL_TRANSACTION_ACTION'' ' ||
505 ' AND mmtt.subinventory_code IN ( SELECT mps.subinventory ' ||
506 ' FROM mtl_physical_subinventories mps ' ||
507 ' WHERE mps.organization_id = ' || l_org_id ||
508 ' AND mps.physical_inventory_id = ' || l_phy_inv_id || ' ) ' ||
509 ' ORDER BY transaction_temp_id, transaction_header_id ' ||
510 ' ';
511
512 dummy_num := JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,'Pending Non Physical Inventory Transactions');
513
514 sqltxt := ' SELECT transaction_header_id "Txn|Header Id" ' ||
515 ' , mti.transaction_interface_id "Txn IntFace|Id" ' ||
516 ' , mif.item_number ||'' (''|| mti.inventory_item_id ||'')'' "Item (Id)" ' ||
517 ' , item_segment1 "Item|Segment1" ' ||
518 ' , subinventory_code "Subinv" ' ||
519 ' , loc_segment1 ||'' ''|| loc_segment2 ||'' ''|| loc_segment3 "Loc_Segment| 1-3" ' ||
520 ' , locator_id "Locator|Id" ' ||
521 ' , revision "Rev" ' ||
522 ' , mti.transaction_quantity "Txn Qty" ' ||
523 ' , mti.primary_quantity "Primary|Qty" ' ||
524 ' , transaction_uom "Txn UoM" ' ||
525 ' , transaction_cost "Txn Cost" ' ||
526 ' , transaction_type_name ||'' (''|| transaction_type_id ||'')'' "Txn Type (Id)" ' ||
527 ' , transaction_action_name ||'' (''|| transaction_action_id ||'')'' "Txn Action (Id)" ' ||
528 ' , transaction_source_type_name ||'' (''|| transaction_source_type_id ||'')'' "Txn Source Type (Id)" ' ||
529 ' , transaction_source_name ||'' (''|| transaction_source_id ||'')'' "Txn Source (Id)" ' ||
530 ' , trx_source_line_id "Txn Source|Line Id" ' ||
531 ' , cost_group_id "Cost|Group Id" ' ||
532 ' , TO_CHAR( transaction_date, ''DD-MON-RR HH24:MI'' ) "Txn Date" ' ||
533 ' , transaction_reference "Txn Reference" ' ||
534 ' , transfer_subinventory "Transfer|Subinv" ' ||
535 ' , transfer_organization_code ||'' (''|| transfer_organization ||'')'' "Transfer|Organization" ' ||
536 ' , mti.request_id "Request Id" ' ||
537 ' , mti.source_code "Source|Code" ' ||
538 ' , mti.source_line_id "Source|Line Id" ' ||
539 ' , source_header_id "Source|Header Id" ' ||
540 ' , mti.distribution_account_id "Distribution|Account Id" ' ||
541 ' , mti.process_flag_desc ||'' ('' || mti.process_flag || '')'' "Process Flag" ' ||
542 ' , transaction_mode_desc ||'' ('' || transaction_mode || '')'' "Txn Mode" ' ||
543 ' , lock_flag_desc ||'' ('' || lock_flag || '')'' "Lock|Flag" ' ||
547 ' FROM mtl_transactions_interface_v mti ' ||
544 ' , TO_CHAR( mti.last_update_date, ''DD-MON-RR HH24:MI'' ) "Last updated" ' ||
545 ' , mti.error_code "Error Code" ' ||
546 ' , error_explanation "Error Explanation" ' ||
548 ' , mtl_item_flexfields mif ' ||
549 ' WHERE mti.organization_id = ' || l_org_id ||
550 ' AND mti.organization_id = mif.organization_id(+) ' ||
551 ' AND mti.inventory_item_id = mif.inventory_item_id(+) ' ||
552 ' AND mti.subinventory_code IN ( SELECT mps.subinventory ' ||
553 ' FROM mtl_physical_subinventories mps ' ||
554 ' WHERE mps.organization_id = ' || l_org_id ||
555 ' AND mps.physical_inventory_id = ' || l_phy_inv_id || ') ' ||
556 ' ORDER BY transaction_header_id, mti.transaction_interface_id ' ||
557 ' ';
558
559 dummy_num := JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,'Inventory Interface Transactions');
560
561 sqltxt := ' SELECT COUNT(*) "Count" ' ||
562 ' , transaction_type_name ||'' ( ''||transaction_type_id||'' )'' "Txn Type (Id)" ' ||
563 ' FROM mtl_transactions_interface_v mti ' ||
564 ' WHERE organization_id = ' || l_org_id ||
565 ' GROUP BY transaction_type_name, transaction_type_id ' ||
566 ' ORDER BY COUNT(*) DESC, transaction_type_name, transaction_type_id ';
567
568 dummy_num := JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,'Interfaced Types of Transactions');
569
570 sqltxt := ' SELECT COUNT(*) ' ||
571 ' FROM mtl_physical_adjustments ' ||
572 ' WHERE organization_id = ' || l_org_id ||
573 ' AND physical_inventory_id = ' || l_phy_inv_id ||
574 ' AND adjustment_quantity <> 0 ' ||
575 ' AND ( approval_status NOT IN (2, 3) OR approval_status IS NULL ) ';
576
577 dummy_num := JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,'Total number of unprocessed adjustments');
578
579 sqltxt := ' SELECT COUNT(*) ' ||
580 ' FROM mtl_material_transactions ' ||
581 ' WHERE organization_id = ' || l_org_id ||
582 ' AND transaction_source_id = ' || l_phy_inv_id ||
583 ' AND transaction_type_id = 8 ';
584
585 dummy_num := JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,'Total number of processed adjustments');
586
587 sqltxt := ' SELECT DISTINCT mif.item_number "Item Number" ' ||
588 ' ,mmtt.inventory_item_id "Item Id" ' ||
589 ' ,primary_uom_code "Primary|UoM" ' ||
590 ' ,mif.inventory_item_flag "Inventory|Item Flag" ' ||
591 ' ,mif.stock_enabled_flag "Stock|Flag" ' ||
592 ' ,mif.mtl_transactions_enabled_flag "Transactable|Flag" ' ||
593 ' ,mif.costing_enabled_flag "Costing|Flag" ' ||
594 ' ,mif.inventory_asset_flag "Inventory|Asset Flag" ' ||
595 ' ,mif.purchasing_enabled_flag "Purchasing|Enabled|Flag" ' ||
596 ' ,mif.purchasing_item_flag "Purchasing|Item|Flag" ' ||
597 ' ,DECODE( mif.lot_control_code, 1, ''N'' , 2, ''Y'' ' ||
598 ' , mif.lot_control_code ) ' ||
599 ' || '' (''||mif.lot_control_code||'')'' "Lot|Control" ' ||
600 ' ,ml.meaning||'' (''||mif.serial_number_control_code||'')'' "Serial|Control" ' ||
601 ' ,DECODE( TO_CHAR(mif.revision_qty_control_code) , ''1'', ''No'' ' ||
602 ' , ''2'', ''Yes'' ' ||
603 ' , mif.revision_qty_control_code ) ' ||
604 ' || '' (''||mif.revision_qty_control_code||'')'' "Revision|Control" ' ||
605 ' ,DECODE( TO_CHAR(mif.location_control_code) ' ||
606 ' ,''1'', ''None'' ' ||
607 ' ,''2'', ''Prespecified'' ' ||
608 ' ,''3'', ''Dynamic'' ' ||
609 ' ,''4'', ''Determine at Subinv Level'' ' ||
610 ' ,''5'', ''Determine at Item Level'' ' ||
611 ' , mif.location_control_code ) ' ||
612 ' || '' (''||mif.location_control_code||'')'' "Location|Control" ' ||
613 ' ,DECODE( mif.restrict_subinventories_code, 1, ''Yes'' ' ||
614 ' , 2, ''No'' ' ||
615 ' ,mif.restrict_subinventories_code ) "Restricted|Subinvs" ' ||
616 ' ,DECODE( mif.restrict_locators_code, 1, ''Yes'', 2, ''No'' ' ||
617 ' ,mif.restrict_locators_code ) ' ||
618 ' || '' (''||mif.restrict_locators_code||'')'' "Restricted|Locators" ' ||
619 ' ,mif.last_update_date ' ||
620 ' FROM mtl_material_transactions_temp mmtt ' ||
621 ' ,mtl_item_flexfields mif ' ||
622 ' ,mfg_lookups ml ' ||
623 ' WHERE mmtt.organization_id = ' || l_org_id ||
624 ' AND mmtt.transaction_source_id = ' || l_phy_inv_id ||
625 ' AND mmtt.transaction_type_id = 8 ' ||
626 ' AND mmtt.inventory_item_id = mif.inventory_item_id(+) ' ||
627 ' AND mif.serial_number_control_code = ml.lookup_code(+) ' ||
628 ' AND ''MTL_SERIAL_NUMBER'' = ml.lookup_type(+) ' ||
629 ' ORDER BY mif.item_number ';
630
631 dummy_num := JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,'Pending adjustment item information');
632
633 sqltxt := ' SELECT subinventory ' ||
634 ' FROM mtl_physical_subinventories ' ||
635 ' WHERE organization_id = ' || l_org_id ||
636 ' AND physical_inventory_id = ' || l_phy_inv_id ||
637 ' ORDER BY subinventory ';
638
639 dummy_num := JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,'Physical Inventory Subinventories');
640
641 sqltxt := ' SELECT msi.* ' ||
642 ' FROM mtl_secondary_inventories msi ' ||
643 ' WHERE msi.organization_id = ' || l_org_id ||
644 ' ORDER BY msi.secondary_inventory_name ';
645
649 ' FROM mtl_secondary_inventories msi ' ||
646 dummy_num := JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,'Organization Subinventories');
647
648 sqltxt := ' SELECT msi.* ' ||
650 ' WHERE ( msi.secondary_inventory_name, msi.organization_id ) IN ' ||
651 ' ( SELECT mps.subinventory, mps.organization_id ' ||
652 ' FROM mtl_physical_subinventories mps ' ||
653 ' WHERE mps.organization_id = ' || l_org_id ||
654 ' AND mps.physical_inventory_id = ' || l_phy_inv_id || ' ) ' ||
655 ' ORDER BY msi.secondary_inventory_name ';
656
657 dummy_num := JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,'Subinventories restricted to physical inventory');
658
659 reportStr := 'The test completed as expected';
660 JTF_DIAGNOSTIC_ADAPTUTIL.addStringToReport(reportClob,reportStr);
661 statusStr := 'SUCCESS';
662 report := JTF_DIAGNOSTIC_ADAPTUTIL.constructReport(statusStr,errStr,fixInfo,isFatal);
663 reportClob := JTF_DIAGNOSTIC_ADAPTUTIL.getReportClob;
664
665 EXCEPTION
666 WHEN OTHERS THEN
667 JTF_DIAGNOSTIC_COREAPI.errorprint('Error: '||sqlerrm);
668 JTF_DIAGNOSTIC_COREAPI.ActionErrorPrint('This is the exception handler');
669 statusStr := 'FAILURE';
670 errStr := sqlerrm ||' occurred in script Exception handled';
671 fixInfo := 'Unexpected Exception in INVDA06B.pls';
672 isFatal := 'FALSE';
673 report := JTF_DIAGNOSTIC_ADAPTUTIL.constructReport(statusStr,errStr,fixInfo,isFatal);
674 reportClob := JTF_DIAGNOSTIC_ADAPTUTIL.getReportClob;
675 END runTest;
676
677 ------------------------------------------------------------
678 -- procedure to report name back to framework
679 ------------------------------------------------------------
680 PROCEDURE getComponentName(name OUT NOCOPY VARCHAR2) IS
681 BEGIN
682 name := 'Accuracy';
683 END getComponentName;
684
685 ------------------------------------------------------------
686 -- procedure to report test description back to framework
687 ------------------------------------------------------------
688 PROCEDURE getTestDesc(descStr OUT NOCOPY VARCHAR2) IS
689 BEGIN
690 descStr := 'Physical Inventory Information';
691 END getTestDesc;
692
693 ------------------------------------------------------------
694 -- procedure to report test name back to framework
695 ------------------------------------------------------------
696 PROCEDURE getTestName(name OUT NOCOPY VARCHAR2) IS
697 BEGIN
698 name := 'Physical Inventory Information';
699 END getTestName;
700
701 ------------------------------------------------------------
702 -- procedure to provide the default parameters for the test case.
703 -- please note the paramters have to be registered through the UI
704 -- before basic tests can be run.
705 --
706 ------------------------------------------------------------
707 PROCEDURE getDefaultTestParams(defaultInputValues OUT NOCOPY JTF_DIAG_INPUTTBL) IS
708 tempInput JTF_DIAG_INPUTTBL;
709 BEGIN
710
711 tempInput := JTF_DIAGNOSTIC_ADAPTUTIL.initinputtable;
712 tempInput := JTF_DIAGNOSTIC_ADAPTUTIL.addInput(tempInput,'OrgId','LOV-oracle.apps.inv.diag.lov.OrganizationLov');
713 tempInput := JTF_DIAGNOSTIC_ADAPTUTIL.addInput(tempInput,'PhyInvId','LOV-oracle.apps.inv.diag.lov.PhysInvLov');
714 defaultInputValues := tempInput;
715 EXCEPTION
716 when others then
717 defaultInputValues := JTF_DIAGNOSTIC_ADAPTUTIL.initinputtable;
718 END getDefaultTestParams;
719 PROCEDURE getDependencies (package_names OUT NOCOPY JTF_DIAG_DEPENDTBL) IS
720 tempDependencies JTF_DIAG_DEPENDTBL;
721
722 BEGIN
723 package_names := JTF_DIAGNOSTIC_ADAPTUTIL.initDependencyTable;
724 END getDependencies;
725
726 PROCEDURE isDependencyPipelined (str OUT NOCOPY VARCHAR2) IS
727 BEGIN
728 str := 'FALSE';
729 END isDependencyPipelined;
730
731 PROCEDURE getOutputValues(outputValues OUT NOCOPY JTF_DIAG_OUTPUTTBL) IS
732 tempOutput JTF_DIAG_OUTPUTTBL;
733 BEGIN
734 tempOutput := JTF_DIAGNOSTIC_ADAPTUTIL.initOutputTable;
735 outputValues := tempOutput;
736 EXCEPTION
737 when others then
738 outputValues := JTF_DIAGNOSTIC_ADAPTUTIL.initOutputTable;
739 END getOutputValues;
740
741 Function getTestMode return INTEGER IS
742 BEGIN
743 return JTF_DIAGNOSTIC_ADAPTUTIL.ADVANCED_MODE;
744
745 END getTestMode;
746
747 END INV_DIAG_PI_GEN;