[Home] [Help]
PACKAGE BODY: APPS.INV_DIAG_ITEM
Source
1 package body INV_DIAG_ITEM as
2 /* $Header: INVDI01B.pls 120.0.12000000.1 2007/06/22 00:51:51 musinha noship $ */
3
4 PROCEDURE init is
5 BEGIN
6 -- test writer
7 null;
8 END init;
9
10 PROCEDURE cleanup IS
11 BEGIN
12 -- test writer could insert special cleanup code here
13 NULL;
14 END cleanup;
15
16 PROCEDURE runtest(inputs IN JTF_DIAG_INPUTTBL,
17 report OUT NOCOPY JTF_DIAG_REPORT,
18 reportClob OUT NOCOPY CLOB) IS
19 reportStr LONG;
20 counter NUMBER;
21 dummy_v2t JTF_DIAGNOSTIC_COREAPI.v2t;
22 c_userid VARCHAR2(50);
23 statusStr VARCHAR2(50);
24 errStr VARCHAR2(4000);
25 fixInfo VARCHAR2(4000);
26 isFatal VARCHAR2(50);
27 dummy_num NUMBER;
28 sqltxt VARCHAR2 (9999);
29 l_item_id NUMBER;
30 l_org_id NUMBER;
31 l_txn_id NUMBER;
32 item_err NUMBER := 0 ;
33 l_count NUMBER;
34 l_row_limit NUMBER;
35 l_resp fnd_responsibility_tl.Responsibility_Name%type :='Inventory';
36
37 CURSOR c_item_valid (cp_n_item_id IN NUMBER, cp_n_org_id IN NUMBER) IS
38 SELECT count(*)
39 FROM mtl_system_items_b
40 WHERE organization_id = cp_n_org_id
41 AND inventory_item_id = cp_n_item_id;
42
43 CURSOR c_item_info (cp_n_item_id IN NUMBER, cp_n_org_id IN NUMBER) IS
44 SELECT *
45 FROM mtl_system_items_b
46 WHERE organization_id = cp_n_org_id
47 AND inventory_item_id = cp_n_item_id;
48
49 l_item c_item_info%ROWTYPE;
50
51 BEGIN
52
53 JTF_DIAGNOSTIC_ADAPTUTIL.setUpVars;
54 JTF_DIAGNOSTIC_ADAPTUTIL.addStringToReport('@html');
55 JTF_DIAGNOSTIC_COREAPI.insert_style_sheet;
56
57 /* -- check whether user has 'Inventory' responsibilty to execute diagnostics script.
58 IF NOT INV_DIAG_GRP.check_responsibility(p_responsibility_name => l_resp) THEN -- l_resp = 'Inventory'
59 JTF_DIAGNOSTIC_COREAPI.ActionErrorPrint(' You do not have the privilege to run this Diagnostics.');
60 statusStr := 'FAILURE';
61 errStr := 'This test requires Inventory Responsibility Role';
62 fixInfo := 'Please contact your sysadmin to get Inventory Responsibility';
63 isFatal := 'FALSE';
64 report := JTF_DIAGNOSTIC_ADAPTUTIL.constructReport(statusStr,errStr,fixInfo,isFatal);
65 reportClob := JTF_DIAGNOSTIC_ADAPTUTIL.getReportClob;
66 RETURN;
67 END IF; */
68
69 l_item_id := JTF_DIAGNOSTIC_ADAPTUTIL.getInputValue('ItemId',inputs);
70 l_org_id := JTF_DIAGNOSTIC_ADAPTUTIL.getInputValue('OrgId',inputs);
71 l_count := 0 ;
72 l_row_limit := INV_DIAG_GRP.g_max_row;
73
74 IF l_item_id IS NOT NULL AND l_org_id IS NOT NULL THEN
75 OPEN c_item_valid (l_item_id, l_org_id);
76 FETCH c_item_valid INTO l_count;
77 CLOSE c_item_valid;
78 END IF;
79
80 IF l_count IS NULL OR l_count <> 1 THEN
81 JTF_DIAGNOSTIC_COREAPI.errorprint('Error: '|| 'Invalid Item and Organization Combination');
82 JTF_DIAGNOSTIC_COREAPI.ActionErrorPrint('Invalid Input Arguments');
83 statusStr := 'FAILURE';
84 errStr := 'Invalid Item and Organization Combination';
85 fixInfo := 'Please enter right combination of Item and Organization';
86 isFatal := 'FALSE';
87 report := JTF_DIAGNOSTIC_ADAPTUTIL.constructReport(statusStr,errStr,fixInfo,isFatal);
88 reportClob := JTF_DIAGNOSTIC_ADAPTUTIL.getReportClob;
89 RETURN;
90 END IF;
91
92 INV_DIAG_GRP.g_inv_diag_item_tbl.delete;
93 INV_DIAG_GRP.g_inv_diag_item_tbl(1).inventory_item_id :=l_item_id;
94 INV_DIAG_GRP.g_inv_diag_item_tbl(1).org_id := l_org_id;
95
96 -- Collect the item informatation in a local variable
97 OPEN c_item_info (l_item_id, l_org_id);
98 FETCH c_item_info INTO l_item;
99 CLOSE c_item_info;
100
101
102 if INV_DIAG_GRP.g_grp_name is null then --standard alone test for item
103 --fnd_file.put_line(fnd_file.log,'@@@ item grp null');
104 sqltxt := 'SELECT language "Language" '||
105 ', description "Description" '||
106 ', long_description "Long Description" '||
107 ' FROM mtl_system_items_tl '||
108 ' WHERE organization_id = '||l_org_id||
109 ' AND inventory_item_id = '||l_item_id||
110 ' ORDER BY language';
111
112 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,'Item descriptions in all installed languages');
113
114 sqltxt := 'SELECT mp.organization_code||'' (''||mp.organization_id||'')'' "Organization|Code (Id)" '||
115 ',mpm.organization_code||'' (''||mp.master_organization_id||'' )'' "Master Org| Code (Id)" '||
116 ',mpc.organization_code||'' (''||mp.cost_organization_id||'' )'' "Cost Org|Code (Id)" '||
117 ',mp.wms_enabled_flag "WMS|Enabled" '||
118 ',DECODE(mp.negative_inv_receipt_code,1,''Yes'', ''No'') "Negative|Balances|Allowed" '||
119 ',DECODE(mp.serial_number_generation,1,''At organization level'', 2,''At item level'', 3,''User Defined'', '||
120 ' mp.serial_number_generation) "Serial Number|Generation" '||
121 ',DECODE(mp.lot_number_uniqueness,1,''Unique for item'', 2,''No uniqueness'', mp.lot_number_uniqueness) "Lot Number|Uniqueness" '||
122 ',DECODE(mp.lot_number_generation,1,''At organization level'', 2,''At item level'', 3,''User Defined'', '||
123 ' mp.lot_number_generation) "Lot Number Generation" '||
124 ',DECODE(mp.serial_number_type,1,''Unique within inventory items'', 2,''Unique within organization'', 3,''Unique across organizations'', '||
125 ' mp.serial_number_type) "Serial Number Type" '||
126 ',DECODE(mp.stock_locator_control_code,1,''None'', 2,''Prespecified'', 3,''Dynamic entry'', 4,''At subinventory level'', 5,''At item level'', '||
127 ' mp.stock_locator_control_code) "Locator|Control" '||
128 ',DECODE(mp.primary_cost_method,1,''Standard'', 2,''Average'', 3,''Periodic Average'',4,''Periodic Incremental LIFO'', 5,''FIFO'', 6,''LIFO'', mp.primary_cost_method) "Primary Cost Method" '||
129 ',mp.default_cost_group_id "Default|Cost Group|Id" '||
130 ',mp.wsm_enabled_flag "WSM|Enabled" '||
131 ',mp.process_enabled_flag "Process|Enabled" '||
132 ',DECODE( TO_CHAR( NVL(mp.project_reference_enabled, 2)),''1'', ''Yes'', ''2'', ''No'' , TO_CHAR( mp.project_reference_enabled ) )|| '' ('' ||mp.project_reference_enabled||'')'' "Project Reference Enabled" '||
133 ' FROM mtl_parameters mp '||
134 ',mtl_parameters mpc '||
135 ',mtl_parameters mpm '||
136 'WHERE mp.cost_organization_id=mpc.organization_id '||
137 'AND mp.master_organization_id=mpm.organization_id '||
138 'AND mp.organization_id IN (SELECT organization_id '||
139 ' FROM mtl_system_items_b '||
140 ' WHERE inventory_item_id='||l_item_id||' )';
141
142 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,'Inventory organization information ');
143 sqltxt :='SELECT user_group_name "Group" '||
144 ', user_attribute_name "Attribute Name" '||
145 ', control_level_dsp "Controlled at" '||
146 ', user_attribute_value "Attribute Value" '||
147 'FROM mtl_item_attribute_values_v '||
148 'WHERE organization_id = '||l_org_id||
149 'AND inventory_item_id = '||l_item_id||
150 'ORDER BY user_group_name, user_attribute_name';
151
152 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,'Item attribute values');
153
154 -- displaying restricted subinventory information when the item is restricted pre-defined list of subinventories
155 IF NVL(l_item.restrict_subinventories_code,1) = 1 THEN
156
157 sqltxt :='SELECT msi.secondary_inventory_name "Name" ' ||
158 ', msi.description "Description" ' ||
159 ', TO_CHAR( msi.disable_date, ''DD-Mon-RR'' ) "Disable|Date" ' ||
160 ', DECODE( msi.reservable_type, 1, ''Yes'', 2, ''No'', ' ||
161 ' msi.reservable_type) "Reservable|Type" ' ||
162 ', DECODE( msi.locator_type ' ||
163 ' ,1, ''None'' ' ||
164 ' ,2, ''Prespecified'' ' ||
165 ' ,3, ''Dynamic'' ' ||
166 ' ,4, ''SubInv Level'' ' ||
167 ' ,5, ''Item Level'', msi.locator_type) ' ||
168 ' || '' (''||msi.locator_type||'')'' "Locator|Control" ' ||
169 ', DECODE( msi.availability_type, 1, ''Nettable'' ' ||
170 ' ,2, ''Non-Nettable'' ' ||
171 ' ,msi.availability_type ) "Availability|Type" ' ||
172 ', DECODE( msi.inventory_atp_code, 1, ''Included'' ' ||
173 ' , 2, ''Not included'' ' ||
174 ' , msi.inventory_atp_code ) "Include|in ATP" ' ||
175 ', DECODE( msi.asset_inventory, 1, ''Yes'', 2, ''No'', ' ||
176 ' msi.asset_inventory ) "Asset|Inventory" ' ||
177 ', DECODE( msi.quantity_tracked, 1, ''Yes'', 2, ''No'', ' ||
178 ' msi.quantity_tracked ) "Quantity|Tracked" ' ||
179 ', msi.picking_order "Picking|Order" ' ||
180 ', DECODE( msi.source_type, 1,''Inventory'' ' ||
181 ' , 2,''Supplier'' ' ||
182 ' , 3,''Subinventory'' ' ||
183 ' , msi.source_type ) ' ||
184 ' || '' ( ''||msi.source_type||'')'' "Source|Type" ' ||
185 ', default_cost_group_id "Default|Cost Group Id" ' ||
186 ' FROM mtl_secondary_inventories msi ' ||
187 ' WHERE (msi.organization_id, msi.secondary_inventory_name ) IN ' ||
188 '( SELECT misi.organization_id, misi.secondary_inventory ' ||
189 ' FROM mtl_item_sub_inventories misi ' ||
190 ' WHERE misi.organization_id =' ||l_org_id ||
191 ' AND inventory_item_id ='||l_item_id || ')' ;
192
193 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,'Subinventories the item is restricted to');
194
195 END IF;
196
197 sqltxt :='SELECT msi.secondary_inventory_name "Name" ' ||
198 ', msi.description "Description" ' ||
199 ', TO_CHAR( msi.disable_date, ''DD-Mon-RR'' ) "Disable|Date" ' ||
200 ', DECODE( msi.reservable_type, 1, ''Yes'', 2, ''No'', ' ||
201 ' msi.reservable_type) "Reservable|Type" ' ||
202 ', DECODE( msi.locator_type ' ||
203 ' ,1, ''None'' ' ||
204 ' ,2, ''Prespecified'' ' ||
205 ' ,3, ''Dynamic'' ' ||
206 ' ,4, ''SubInv Level'' ' ||
207 ' ,5, ''Item Level'', msi.locator_type) ' ||
208 ' || '' (''||msi.locator_type||'')'' "Locator|Control" ' ||
209 ', DECODE( msi.availability_type, 1, ''Nettable'' ' ||
210 ' ,2, ''Non-Nettable'' ' ||
211 ' ,msi.availability_type ) "Availability|Type" ' ||
212 ', DECODE( msi.inventory_atp_code, 1, ''Included'' ' ||
213 ' , 2, ''Not included'' ' ||
214 ' , msi.inventory_atp_code ) "Include|in ATP" ' ||
215 ', DECODE( msi.asset_inventory, 1, ''Yes'', 2, ''No'', ' ||
216 ' msi.asset_inventory ) "Asset|Inventory" ' ||
217 ', DECODE( msi.quantity_tracked, 1, ''Yes'', 2, ''No'' ' ||
218 ' , msi.quantity_tracked ) "Quantity|Tracked" ' ||
219 ', msi.picking_order "Picking|Order" ' ||
220 ', DECODE( msi.source_type, 1,''Inventory'' ' ||
221 ' , 2,''Supplier'' ' ||
222 ' , 3,''Subinventory'' ' ||
223 ' , msi.source_type ) ' ||
224 ' || '' ( ''||msi.source_type||'')'' "Source|Type" ' ||
225 ', default_cost_group_id "Default|Cost Group Id" ' ||
226 ' FROM mtl_secondary_inventories msi ' ||
227 ' WHERE (msi.organization_id, msi.secondary_inventory_name ) IN ' ||
228 '( SELECT DISTINCT moq.organization_id, moq.subinventory_code' ||
229 ' FROM mtl_onhand_quantities_detail moq' ||
230 ' WHERE moq.organization_id = '|| l_org_id ||
231 ' AND moq.inventory_item_id = '|| l_item_id ||')' ||
232 ' ORDER BY msi.secondary_inventory_name';
233
234 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,'Onhand Subinventory Information ');
235
236 sqltxt := ' SELECT micv.category_set_name "Category Set" ' ||
237 ' , micv.category_set_id "Category Set Id" ' ||
238 ' , DECODE( micv.control_level, 1, ''Master'', 2, ''Org'', micv.control_level ) ' ||
239 ' "Control Level" ' ||
240 ' , micv.category_concat_segs "Category" ' ||
241 ' , micv.category_id "Category Id" ' ||
242 ' FROM mtl_item_categories_v micv ' ||
243 ' WHERE micv.organization_id = '|| l_org_id ||
244 ' AND micv.inventory_item_id = '|| l_item_id;
245
246 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,'Item categories');
247
248 sqltxt :=' SELECT micgv.concatenated_segments "Group Name" ' ||
249 ' , msi.item_catalog_group_id "Group id" ' ||
250 ' , micgv.description "Description" ' ||
251 ' , TO_CHAR( micgv.start_date_active, ''DD-MON-RR'' ) "Start Date Active" ' ||
252 ' , TO_CHAR( micgv.end_date_active, ''DD-MON-RR'' ) "End Date Active" ' ||
253 ' , TO_CHAR( micgv.inactive_date, ''DD-MON-RR'' ) "Inactive Date" ' ||
254 ' FROM mtl_system_items_b msi ' ||
255 ' , mtl_item_catalog_groups_kfv micgv ' ||
256 ' WHERE msi.organization_id ='|| l_org_id ||
257 ' AND inventory_item_id = '|| l_item_id ||
258 ' AND msi.item_catalog_group_id = micgv.item_catalog_group_id ' ||
259 ' ORDER BY 1,2';
260
261 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,'Catalogs item assigned to');
262
263 sqltxt :=' SELECT mdev.element_sequence "Element|Sequence" ' ||
264 ' , mdev.element_name "Element Name" ' ||
265 ' , mdev.element_value "Element Value" ' ||
266 ' , mde.description "Description" ' ||
267 ' , mde.required_element_flag "Required" ' ||
268 ' , mde.default_element_flag "Defaulted" ' ||
269 ' FROM mtl_descriptive_elements mde ' ||
270 ' , mtl_descr_element_values mdev ' ||
271 ' , mtl_system_items_b msi ' ||
272 ' WHERE msi.organization_id = '|| l_org_id ||
273 ' AND msi.inventory_item_id = '|| l_item_id ||
274 ' AND msi.inventory_item_id = mdev.inventory_item_id ' ||
275 ' AND mde.item_catalog_group_id = msi.item_catalog_group_id ' ||
276 ' AND mde.element_name = mdev.element_name ' ||
277 ' AND mdev.element_value IS NOT NULL ' ||
278 ' ORDER BY mdev.element_sequence';
279
280 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,'Catalog descriptive elements');
281
282 -- Displaying serial information only for serial conrolled item
283 IF NVL(l_item.serial_number_control_code,2) <> 1 THEN
284
285 sqltxt := 'SELECT * FROM ( ' ||
286 ' SELECT msn.serial_number "Serial|Number" ' ||
287 ' , ml.meaning || '' ( '' || msn.current_status || '' )'' ' ||
288 ' "Current Status (Id)" ' ||
289 ' , msn.current_subinventory_code "Current|Subinventory" ' ||
290 ' , msn.current_locator_id "Current|Locator Id" ' ||
291 ' , msn.cost_group_id "Cost Group|Id" ' ||
292 ' , msn.lpn_id "LPN Id" ' ||
293 ' , msn.group_mark_id "Group Mark|Id" ' ||
294 ' , msn.line_mark_id "Line Mark|Id" ' ||
295 ' , msn.lot_line_mark_id "Lot Line Mark|Id" ' ||
296 ' , TO_CHAR( msn.last_update_date, ''DD-MON-RR HH24:MI'' ) "Last|Updated" ' ||
297 ' FROM mtl_serial_numbers msn ' ||
298 ' , mfg_lookups ml ' ||
299 ' WHERE msn.current_organization_id = '|| l_org_id ||
300 ' AND msn.inventory_item_id = '|| l_item_id ||
301 ' AND msn.current_status = ml.lookup_code(+) ' ||
302 ' AND ''SERIAL_NUM_STATUS'' = ml.lookup_type(+) ' ||
303 ' ORDER BY msn.last_update_date DESC ' ||
304 ' ) WHERE ROWNUM <= ' || l_row_limit ;
305
306 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,'Serial number');
307
308 END IF;
309
310 -- Displaying item revision only for revision controlled item
311 IF NVL(l_item.revision_qty_control_code,2) <> 1 THEN
312
313 sqltxt := ' SELECT * FROM ( ' ||
314 ' SELECT revision "Revision" ' ||
315 ' , TO_CHAR( creation_date, ''DD-MON-RR HH24:MI'' ) "Creation Date" ' ||
316 ' , change_notice "ECO Name" ' ||
317 ' , TO_CHAR( implementation_date, ''DD-MON-RR HH24:MI'' ) "Implementation Date" ' ||
318 ' , TO_CHAR( effectivity_date, ''DD-MON-RR HH24:MI'' ) "Effectivity Date" ' ||
319 ' FROM mtl_item_revisions ' ||
320 ' WHERE organization_id = '|| l_org_id ||
321 ' AND inventory_item_id = '|| l_item_id ||
322 ' ORDER BY revision ' ||
323 ' ) WHERE ROWNUM <= ' || l_row_limit ;
324
325 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,'Item revisions');
326
327 END IF;
328
329 -- Displaying lot information only for lote controlled item
330 IF NVL(l_item.lot_control_code,2) <> 1 THEN
331
332 sqltxt :=' SELECT * FROM ( ' ||
333 ' SELECT lot_number "Lot Number" ' ||
334 ', status_code ||'' (''|| status_id ||'')'' "Status (Id)" ' ||
335 ', TO_CHAR( expiration_date, ''DD-MON-RR HH24:MI'' ) "Expiration Date" ' ||
336 ', DECODE( disable_flag, 1, ''Yes'', 2, ''No'', disable_flag ) "Disabled" ' ||
337 ', description "Description" ' ||
338 ' FROM mtl_lot_numbers_all_v ' ||
339 ' WHERE organization_id = '|| l_org_id ||
340 ' AND inventory_item_id = '|| l_item_id ||
341 ' ORDER BY lot_number ' ||
342 ' ) WHERE ROWNUM <= ' || l_row_limit ;
343
344 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,'Lot Number' );
345
346 END IF;
347
348 sqltxt := 'SELECT subinventory_code "Subinventory" ' ||
349 ' , DECODE( default_type, 1, ''Shipping'', 2, ''Receiving'', 3, ''Move Order Receipt'' ' ||
350 ' , default_type ) ' ||
351 ' ||'' (''||default_type||'')'' "Default Type" ' ||
352 ' , TO_CHAR( last_update_date, ''DD-MON-RR'' ) "Last updated" ' ||
353 ' FROM mtl_item_sub_defaults ' ||
354 ' WHERE organization_id = '|| l_org_id ||
355 ' AND inventory_item_id = '|| l_item_id ||
356 ' ORDER BY subinventory_code, default_type';
357
358 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,'Item transactions defaults: subinventory' );
359
360 sqltxt := 'SELECT mild.subinventory_code "Subinventory" ' ||
361 ' , milv.concatenated_segments "Locator" ' ||
362 ' , milv.inventory_location_id "Locator Id" ' ||
363 ' , DECODE( default_type, 1, ''Shipping'', 2, ''Receiving'', 3, ''Move Order Receipt'' ' ||
364 ' , default_type ) ' ||
365 ' ||'' (''||default_type||'')'' "Default Type" ' ||
366 ' , TO_CHAR( disable_date, ''DD-MON-RR'' ) "Disable Date" ' ||
367 ' , TO_CHAR( mild.last_update_date, ''DD-MON-RR'' ) "Last updated" ' ||
368 ' FROM mtl_item_loc_defaults mild ' ||
369 ' , mtl_item_locations_kfv milv ' ||
370 ' WHERE mild.organization_id = '|| l_org_id ||
371 ' AND mild.inventory_item_id = '|| l_item_id ||
372 ' AND mild.organization_id = milv.organization_id ' ||
373 ' AND mild.locator_id = milv.inventory_location_id ' ||
374 ' ORDER BY mild.subinventory_code, milv.concatenated_segments' ;
375
376 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,'Item transactions defaults: locators');
377
378 sqltxt := ' SELECT mif.item_number "Item" ' ||
379 ' , moq.inventory_item_id "Item Id" ' ||
380 ' , SUM( moq.transaction_quantity ) "Txn Qty" ' ||
381 ' , moq.subinventory_code "Subinv" ' ||
382 ' , moq.locator_id "Locator Id" ' ||
383 ' , mil.concatenated_segments "Locator" ' ||
384 ' , mil.description "Locator Desc" ' ||
385 ' , moq.revision "Revision" ' ||
386 ' , moq.lot_number "Lot Number" ' ||
387 ' FROM mtl_onhand_quantities_detail moq ' ||
388 ' , mtl_item_flexfields mif ' ||
389 ' , mtl_item_locations_kfv mil ' ||
390 ' WHERE moq.organization_id = ' || l_org_id ||
391 ' AND moq.inventory_item_id = ' || l_item_id ||
392 ' AND moq.inventory_item_id = mif.inventory_item_id(+) ' ||
393 ' AND moq.organization_id = mif.organization_id(+) ' ||
394 ' AND moq.organization_id = mil.organization_id(+) ' ||
395 ' AND moq.locator_id = mil.inventory_location_id(+) ' ||
396 ' GROUP BY mif.item_number, moq.inventory_item_id ' ||
397 ' , moq.subinventory_code, moq.locator_id ' ||
398 ' , mil.concatenated_segments, mil.description ' ||
399 ' , moq.revision, moq.lot_number ' ||
400 ' ORDER BY mif.item_number, moq.inventory_item_id ' ||
401 ' , moq.subinventory_code, moq.locator_id ' ||
402 ' , mil.concatenated_segments, mil.description ' ||
403 ' , moq.revision, moq.lot_number ';
404
405 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,'Item Onhand Quantity');
406
407 sqltxt := ' SELECT * FROM ( ' ||
408 ' SELECT TO_CHAR( requirement_date, ''DD-MON-RR'' ) "REQUIREMENT_DATE" ' ||
409 ' , reservation_id ' ||
410 ' , reservation_quantity ' ||
411 ' , primary_reservation_quantity ' ||
412 ' , detailed_quantity ' ||
413 ' , demand_source_type_id ' ||
414 ' , demand_source_name ' ||
415 ' , demand_source_header_id ' ||
416 ' , demand_source_line_id ' ||
417 ' , demand_source_delivery ' ||
418 ' , revision ' ||
419 ' , subinventory_code ' ||
420 ' , locator_id ' ||
421 ' , lot_number "LOT|NUMBER" ' ||
422 ' , serial_number "SERIAL|NUMBER" ' ||
423 ' , lpn_id ' ||
424 ' , TO_CHAR( creation_date, ''DD-MON-RR'' ) "CREATION_DATE" ' ||
425 ' , TO_CHAR( last_update_date, ''DD-MON-RR'' ) "LAST_UPDATE_DATE" ' ||
426 ' FROM mtl_reservations ' ||
427 ' WHERE organization_id = ' || l_org_id ||
428 ' AND inventory_item_id = ' || l_item_id ||
429 ' ORDER BY requirement_date DESC ' ||
430 ' ) WHERE ROWNUM <= ' || l_row_limit ;
431
432 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,'Item Reservations');
433
434 sqltxt := ' SELECT demand_id ' ||
435 ' , demand_source_name ' ||
436 ' , inventory_item_id ' ||
437 ' , line_item_quantity ' ||
438 ' , line_item_reservation_qty ' ||
439 ' , reservation_quantity ' ||
440 ' , primary_uom_quantity ' ||
441 ' , requirement_date ' ||
442 ' , revision ' ||
443 ' , subinventory ' ||
444 ' , locator_id ' ||
445 ' , lot_number "LOT|NUMBER" ' ||
446 ' , serial_number ' ||
447 ' , TO_CHAR( creation_date, ''DD-MON-RR'' ) "creation_date" ' ||
448 ' , TO_CHAR( last_update_date, ''DD-MON-RR'' ) "last_update_date" ' ||
449 ' FROM mtl_demand ' ||
450 ' WHERE organization_id = ' || l_org_id ||
451 ' AND inventory_item_id = ' || l_item_id ||
452 ' ORDER BY requirement_date ';
453
454 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,'Item Demand');
455
456 -- intentionally not joining to MIF due ot performance
457 sqltxt := ' SELECT * FROM ( ' ||
458 ' SELECT mtiv.transaction_interface_id "Transaction|Interface Id" ' ||
459 ' , mtiv.item_segment1 "Item" ' ||
460 ' , mtiv.inventory_item_id "Item Id" ' ||
461 ' , mttv.transaction_type_name ' ||
462 ' ||'' (''||mtiv.transaction_type_id||'')'' "Transaction|Type Name (Id)" ' ||
463 ' , mtiv.transaction_quantity "Transaction|Quantity" ' ||
464 ' , mtiv.transaction_mode_desc||'' ('' ||transaction_mode || '')'' "Transaction|Mode" ' ||
465 ' , mtiv.process_flag_desc||'' ('' ||mtiv.process_flag || '')'' "Process|Flag" ' ||
466 ' , lock_flag_desc||'' ('' || lock_flag || '')'' "Lock|Flag" ' ||
467 ' , TO_CHAR( mtiv.last_update_date, ''DD-MON-RR HH24:MI'' ) "Last updated" ' ||
468 ' , mtiv.error_code "Error Code" ' ||
469 ' , error_explanation "Error Explanation" ' ||
470 ' FROM mtl_transactions_interface_v mtiv ' ||
471 ' , mtl_trx_types_view mttv ' ||
472 ' WHERE mtiv.organization_id = ' || l_org_id ||
473 ' AND mtiv.inventory_item_id = ' || l_item_id ||
474 ' AND mtiv.transaction_type_id = mttv.transaction_type_id ' ||
475 ' ) WHERE ROWNUM <= ' || l_row_limit ;
476
477 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,'Item transactions at Interface');
478
479 sqltxt := ' SELECT * FROM ( ' ||
480 ' SELECT transaction_header_id "Txn|Header Id" ' ||
481 ' ,transaction_temp_id "Txn|Temp Id" ' ||
482 ' ,TO_CHAR( transaction_date, ''DD-MON-RR'' ) "Transaction|Date" ' ||
483 ' ,DECODE(transaction_mode,1,''Online'' ' ||
484 ' ,2,''Concurrent'' ' ||
485 ' ,3,''Background'' ' ||
486 ' ,transaction_mode) ' ||
487 ' ||'' (''||transaction_mode||'')'' "Transaction|Mode" ' ||
488 ' ,DECODE(transaction_status,1,''Pending'' ' ||
489 ' ,2,''Allocated'' ' ||
490 ' ,3,''Pending'' ' ||
491 ' ,NULL,''Pending'' ' ||
492 ' ,transaction_status) ' ||
493 ' ||'' (''||transaction_status||'')'' "Transaction|Status" ' ||
494 ' ,process_flag "Process|Flag" ' ||
495 ' ,lock_flag "Lock|Flag" ' ||
496 ' ,error_code ' ||
497 ' ,error_explanation ' ||
498 ' ,TO_CHAR( mmtt.last_update_date, ''DD-MON-RR HH24:MI'') "Last Updated" ' ||
499 ' ,mif.item_number ' ||
500 ' ||'' (''||mmtt.inventory_item_id||'')'' "Item (Id)" ' ||
501 ' ,item_description "Item Description" ' ||
502 ' ,revision "Rev" ' ||
503 ' ,lot_number "Lot" ' ||
504 ' ,serial_number "Serial|Number" ' ||
505 ' ,mmtt.cost_group_id "Cost|Group Id" ' ||
506 ' ,mmtt.subinventory_code "Subinv" ' ||
507 ' ,mil.description ' ||
508 ' ||'' (''||mmtt.locator_id||'') '' "Stock|Locator (Id)" ' ||
509 ' ,transfer_subinventory "Transfer|Subinv" ' ||
510 ' ,transfer_to_location "Transfer|Location" ' ||
511 ' ,transaction_quantity "Txn Qty" ' ||
512 ' ,primary_quantity "Primary|Qty" ' ||
513 ' ,transaction_uom "Txn|UoM" ' ||
514 ' ,mtt.transaction_type_name ' ||
515 ' ||'' (''||mmtt.transaction_type_id||'')'' "Txn Type (Id)" ' ||
516 ' ,ml.meaning ' ||
517 ' ||'' (''||mmtt.transaction_action_id||'')'' "Txn Action Type (Id)" ' ||
518 ' FROM mtl_material_transactions_temp mmtt ' ||
519 ' ,mtl_transaction_types mtt ' ||
520 ' ,mtl_item_flexfields mif ' ||
521 ' ,mfg_lookups ml ' ||
522 ' ,mtl_item_locations_kfv mil ' ||
523 ' WHERE mmtt.organization_id = ' || l_org_id ||
524 ' AND mmtt.inventory_item_id = ' || l_item_id ||
525 ' AND mmtt.transaction_type_id=mtt.transaction_type_id ' ||
526 ' AND mmtt.organization_id=mif.organization_id(+) ' ||
527 ' AND mmtt.inventory_item_id=mif.inventory_item_id(+) ' ||
528 ' AND mmtt.transaction_action_id=ml.lookup_code ' ||
529 ' AND ml.lookup_type=''MTL_TRANSACTION_ACTION'' ' ||
530 ' AND mmtt.locator_id=mil.inventory_location_id(+) ' ||
531 ' AND mmtt.organization_id=mil.organization_id(+) ' ||
532 ' ORDER BY 1,2 ' ||
533 ' ) WHERE ROWNUM <= ' || l_row_limit ;
534
535 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,'Pending Item Transactions ');
536
537 sqltxt := ' SELECT * FROM ( ' ||
538 ' SELECT mmt.transaction_id "Txn Id" ' ||
539 ' , TO_CHAR( mmt.transaction_date, ''DD-MON-RR'' ) "Txn Date" ' ||
540 ' , mmt.acct_period_id "Account|Period Id" ' ||
541 ' , mmt.transaction_quantity "Txn Qty" ' ||
542 ' , mmt.primary_quantity "Pri Qty" ' ||
543 ' , mmt.transaction_uom "Uom" ' ||
544 ' , tt.transaction_type_name ||'' (''||mmt.transaction_type_id||'')'' "Txn Type (Id)" ' ||
545 ' , mmt.subinventory_code "Subinv" ' ||
546 ' , mmt.locator_id "Locator|Id" ' ||
547 ' , mmt.revision "Rev" ' ||
548 ' , mmt.distribution_account_id "Distribution|Account Id" ' ||
549 ' , mmt.costed_flag "Costed|Flag" ' ||
550 ' , mmt.shipment_costed "Shipment|Costed" ' ||
551 ' , mmt.cost_group_id "Cost Group|Id" ' ||
552 ' , mmt.transfer_cost_group_id "Transfer|Cost Group Id" ' ||
553 ' , mmt.transaction_group_id "Txn Group Id" ' ||
554 ' , mmt.transaction_set_id "Txn Set Id" ' ||
555 ' , mmt.transaction_action_id "Txn Action Id" ' ||
556 ' , mmt.completion_transaction_id "Completion|Txn Id" ' ||
557 ' , st.transaction_source_type_name ||'' (''|| mmt.transaction_source_type_id ||'')'' "Txn Source Type (Id)" ' ||
558 ' , mmt.transaction_source_id "Txn Source Id" ' ||
559 ' , mmt.transaction_source_name "Txn Source" ' ||
560 ' , mmt.source_code "Source|Code" ' ||
561 ' , mmt.source_line_id "Source|Line Id" ' ||
562 ' , mmt.request_id "Txn|Request Id" ' ||
563 ' , mmt.operation_seq_num "Operation|Seq Num" ' ||
564 ' , mmt.transfer_transaction_id "Transfer|Txn Id" ' ||
565 ' , mmt.move_transaction_id "Move|Txn Id" ' ||
566 ' , mmt.transfer_organization_id "Transfer|Organization Id" ' ||
567 ' , mmt.transfer_subinventory "Transfer|Subinv" ' ||
568 ' , mmt.shipment_number "Shipment|Number" ' ||
569 ' , TO_CHAR( mmt.last_update_date, ''DD-MON-RR HH24:MI'' ) "Last updated" ' ||
570 ' , mmt.error_code "Error Code" ' ||
571 ' , mmt.error_explanation "Error Explanation" ' ||
572 ' FROM mtl_material_transactions mmt ' ||
573 ' , mtl_transaction_types tt ' ||
574 ' , mtl_txn_source_types st ' ||
575 ' WHERE mmt.organization_id = ' || l_org_id ||
576 ' AND mmt.inventory_item_id = ' || l_item_id ||
577 ' AND mmt.costed_flag IS NOT NULL ' ||
578 ' AND mmt.transaction_type_id = tt.transaction_type_id(+) ' ||
579 ' AND mmt.transaction_source_type_id = st.transaction_source_type_id(+) ' ||
580 ' ORDER BY mmt.transaction_id ' ||
581 ' ) WHERE ROWNUM <= ' || l_row_limit ;
582
583 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,'Uncosted Transactions ');
584
585 sqltxt := ' SELECT DISTINCT( mpi.physical_inventory_name ) "PhyInv.|Name" ' ||
586 ' , mpi.physical_inventory_id "PhyInv.|Id" ' ||
587 ' , TO_CHAR( mpi.physical_inventory_date, ''DD-MON-RR'' ) "PhyInv.|Date" ' ||
588 ' , mpa.approval_status "Adj.Approval|Status" ' ||
589 ' , COUNT(*) "Approved|Adjustments" ' ||
590 ' FROM mtl_physical_adjustments mpa ' ||
591 ' , mtl_physical_inventories mpi ' ||
592 ' WHERE mpi.organization_id = mpa.organization_id ' ||
593 ' AND mpi.physical_inventory_id = mpa.physical_inventory_id ' ||
594 ' AND mpi.organization_id = ' || l_org_id ||
595 ' AND mpa.inventory_item_id = ' || l_item_id ||
596 ' AND mpa.approval_status = 3 ' ||
597 ' GROUP BY mpi.physical_inventory_name, mpi.physical_inventory_id ' ||
598 ' , mpi.physical_inventory_date, mpa.approval_status ';
599
600 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,'Item Physical Inventory Adjustments');
601
602 sqltxt := ' SELECT mcch.cycle_count_header_name "Cycle Count|Name" ' ||
603 ' , mcce.cycle_count_header_id "Cycle Count|Id" ' ||
604 ' , mac.abc_class_name "ABC Class|Name" ' ||
605 ' , mcci.abc_class_id "ABC Class|Id" ' ||
606 ' , TO_CHAR( mcci.item_last_schedule_date, ''DD-MON-RR'' ) "Item Last|Scheduled Date" ' ||
607 ' , COUNT(*) "Completed|Cycle Count| Entries" ' ||
608 ' FROM mtl_cycle_count_items mcci ' ||
609 ' , mtl_cycle_count_headers mcch ' ||
610 ' , mtl_abc_classes mac ' ||
611 ' , mtl_cycle_count_entries mcce ' ||
612 ' WHERE mcce.organization_id = ' || l_org_id ||
613 ' AND mcce.inventory_item_id = ' || l_item_id ||
614 ' AND mcce.cycle_count_header_id = mcch.cycle_count_header_id ' ||
615 ' AND mcce.inventory_item_id = mcci.inventory_item_id ' ||
616 ' AND mcce.cycle_count_header_id = mcci.cycle_count_header_id ' ||
617 ' AND mcci.abc_class_id = mac.abc_class_id ' ||
618 ' AND mac.organization_id = mcce.organization_id ' ||
619 ' AND mcce.entry_status_code = 5 ' ||
620 ' GROUP BY mcch.cycle_count_header_name, mcce.cycle_count_header_id ' ||
621 ' , mac.abc_class_name, mcci.abc_class_id ' ||
622 ' , mcci.item_last_schedule_date ' ||
623 ' ORDER BY mcch.cycle_count_header_name ';
624
625 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,'Item Cycle Count');
626
627
628
629 reportStr := 'The test completed as expected';
630 JTF_DIAGNOSTIC_ADAPTUTIL.addStringToReport(reportClob,reportStr);
631 statusStr := 'SUCCESS';
632 report := JTF_DIAGNOSTIC_ADAPTUTIL.constructReport(statusStr,errStr,fixInfo,isFatal);
633 reportClob := JTF_DIAGNOSTIC_ADAPTUTIL.getReportClob;
634
635 else
636
637 -- if no item input, do nothing
638 statusStr := ''; -- 'SUCCESS';
639 errStr := ''; --Test failure message displayed here';
640 fixInfo := ''; -- 'Fixing the test suggestions here';
641 isFatal := ''; -- 'FALSE';
642
643 END IF;
644
645 EXCEPTION
646 when others then
647 JTF_DIAGNOSTIC_COREAPI.errorprint('Error: '||sqlerrm);
648 JTF_DIAGNOSTIC_COREAPI.ActionErrorPrint('This is the exception handler');
649 statusStr := 'FAILURE';
650 errStr := sqlerrm ||' occurred in script Exception handled';
651 fixInfo := 'Unexpected Exception in INVDI01B.pls';
652 isFatal := 'FALSE';
653 report := JTF_DIAGNOSTIC_ADAPTUTIL.constructReport(statusStr,errStr,fixInfo,isFatal);
654 reportClob := JTF_DIAGNOSTIC_ADAPTUTIL.getReportClob;
655 END runTest;
656
657
658 PROCEDURE getComponentName(name OUT NOCOPY VARCHAR2) IS
659 BEGIN
660 --name := 'Inventory Item';
661 name := 'Item Data Collection';
662 END getComponentName;
663
664 PROCEDURE getTestDesc(descStr OUT NOCOPY VARCHAR2) IS
665 BEGIN
666 descStr := 'Inventory organization information that have the given item assigned';
667 END getTestDesc;
668
669 PROCEDURE getTestName(name OUT NOCOPY VARCHAR2) IS
670 BEGIN
671 name := 'Item Data Collection';
672 END getTestName;
673
674 PROCEDURE getDependencies (package_names OUT NOCOPY JTF_DIAG_DEPENDTBL) IS
675 tempDependencies JTF_DIAG_DEPENDTBL;
676
677 BEGIN
678 package_names := JTF_DIAGNOSTIC_ADAPTUTIL.initDependencyTable;
679 END getDependencies;
680
681 PROCEDURE isDependencyPipelined (str OUT NOCOPY VARCHAR2) IS
682 BEGIN
683 str := 'FALSE';
684 END isDependencyPipelined;
685
686
687 PROCEDURE getOutputValues(outputValues OUT NOCOPY JTF_DIAG_OUTPUTTBL) IS
688 tempOutput JTF_DIAG_OUTPUTTBL;
689 BEGIN
690 tempOutput := JTF_DIAGNOSTIC_ADAPTUTIL.initOutputTable;
691 outputValues := tempOutput;
692 EXCEPTION
693 when others then
694 outputValues := JTF_DIAGNOSTIC_ADAPTUTIL.initOutputTable;
695 END getOutputValues;
696
697
698 PROCEDURE getDefaultTestParams(defaultInputValues OUT NOCOPY JTF_DIAG_INPUTTBL) IS
699 tempInput JTF_DIAG_INPUTTBL;
700 BEGIN
701 tempInput := JTF_DIAGNOSTIC_ADAPTUTIL.initinputtable;
702 tempInput := JTF_DIAGNOSTIC_ADAPTUTIL.addInput(tempInput,'OrgId','LOV-oracle.apps.inv.diag.lov.OrganizationLov');
703 tempInput := JTF_DIAGNOSTIC_ADAPTUTIL.addInput(tempInput,'ItemId','LOV-oracle.apps.inv.diag.lov.ItemLov');
704 defaultInputValues := tempInput;
705 EXCEPTION
706 when others then
707 defaultInputValues := JTF_DIAGNOSTIC_ADAPTUTIL.initinputtable;
708 END getDefaultTestParams;
709
710 Function getTestMode return INTEGER IS
711 BEGIN
712 return JTF_DIAGNOSTIC_ADAPTUTIL.ADVANCED_MODE;
713
714 END getTestMode;
715
716 END INV_DIAG_ITEM;