[Home] [Help]
PACKAGE BODY: APPS.INV_DIAG_DTXN
Source
1 package body INV_DIAG_DTXN as
2 /* $Header: INVDTD1B.pls 120.1 2008/02/21 21:17:21 musinha noship $ */
3 PROCEDURE init is
4 BEGIN
5 null;
6 END init;
7
8 PROCEDURE cleanup IS
9 BEGIN
10 -- test writer could insert special cleanup code here
11 NULL;
12 END cleanup;
13
14 PROCEDURE runtest(inputs IN JTF_DIAG_INPUTTBL,
15 report OUT NOCOPY JTF_DIAG_REPORT,
16 reportClob OUT NOCOPY CLOB) IS
17 reportStr LONG; -- REPORT
18 sqltxt VARCHAR2(9999); -- SQL select statement
19 c_username VARCHAR2(50); -- accept input for username
20 statusStr VARCHAR2(50); -- SUCCESS or FAILURE
21 errStr VARCHAR2(4000); -- error message
22 fixInfo VARCHAR2(4000); -- fix tip
23 isFatal VARCHAR2(50); -- TRUE or FALSE
24 dummy_num NUMBER;
25 row_limit NUMBER;
26 l_txn_id NUMBER;
27 l_org_id NUMBER;
28 l_acct_period_id NUMBER;
29 l_script varchar2(30);
30 l_proc_flag varchar2(1);
31 l_src_line_id NUMBER;
32
33 cursor c_overshipline is
34 select wdd1.source_line_id from
35 ( select inventory_item_id, trx_source_line_id, organization_id, sum(abs(transaction_quantity)) mmt_qty
36 from mtl_material_transactions
37 where picking_line_id is not null
38 and transaction_source_type_id = 8
39 group by inventory_item_id, trx_source_line_id, organization_id
40 ) mmt ,
41 ( select wdd.source_header_number, wdd.source_line_id, wdd.inventory_item_id,
42 wdd.organization_id, sum(wdd.shipped_quantity) shp_qty
43 from wsh_delivery_details wdd
44 where wdd.source_code = 'OE'
45 and wdd.released_status = 'C'
46 and wdd.serial_number is null
47 group by wdd.source_header_number, wdd.source_line_id, wdd.inventory_item_id, wdd.organization_id
48 ) wdd1
49 where mmt.mmt_qty > wdd1.shp_qty
50 and mmt.trx_source_line_id = wdd1.source_line_id
51 and mmt.inventory_item_id = wdd1.inventory_item_id
52 and mmt.organization_id = wdd1.organization_id
53 and mmt.organization_id = nvl(l_org_id, mmt.organization_id);
54
55 -- Bug 6690548: Removed the table mtl_item_flexfields from the FROM clause
56 -- of the following query as it was causing performance issues. And also added
57 -- the where clause to check that the organization's primary cost method is not standard.
58 cursor c_cstgrp is
59 SELECT DISTINCT moqd.inventory_item_id,mp.organization_id, mp.default_cost_group_id
60 FROM mtl_onhand_quantities_detail moqd,
61 mtl_parameters mp
62 --mtl_item_flexfields mif
63 WHERE moqd.organization_id = nvl(l_org_id, moqd.organization_id)
64 AND moqd.cost_group_id <> mp.default_cost_group_id
65 and moqd.organization_id = mp.organization_id
66 and mp.primary_cost_method <> 1;
67
68
69 BEGIN
70 JTF_DIAGNOSTIC_ADAPTUTIL.setUpVars;
71 JTF_DIAGNOSTIC_ADAPTUTIL.addStringToReport('@html');
72 JTF_DIAGNOSTIC_COREAPI.insert_style_sheet;
73 -- accept input
74 l_org_id := JTF_DIAGNOSTIC_ADAPTUTIL.getInputValue('OrgId',inputs);
75 l_script :=JTF_DIAGNOSTIC_ADAPTUTIL.getInputValue('ScriptName',inputs);
76 l_proc_flag :=JTF_DIAGNOSTIC_ADAPTUTIL.getInputValue('ProcFlag',inputs);
77 row_limit :=INV_DIAG_GRP.g_max_row;
78
79 if l_script = 'acct_period' then
80
81 sqltxt := 'SELECT organization_code || '' ('' ||mmt.organization_id|| '')'' "Organization|Code (Id)" '||
82 ',TO_CHAR( transaction_date, ''DD-MON-RR'' ) "Txn Date" '||
83 ',mmt.acct_period_id "MMT Acct period" '||
84 ',oap.acct_period_id "OAP Acct period" '||
85 ',mtst.transaction_source_type_name ||'' (''||mmt.transaction_source_type_id||'')'' "Txn Source Type (Id)" '||
86 ',mtt.transaction_type_name ||'' (''||mmt.transaction_type_id||'')'' "Txn Type (Id)" '||
87 ',ml.meaning ||'' (''||mmt.transaction_action_id||'')'' "Txn Action Type (Id)" '||
88 ',TO_CHAR( mmt.last_update_date, ''DD-MON-RR HH24:MI'') "Last Updated" '||
89 ',mif.item_number ||'' (''||mmt.inventory_item_id||'')'' "Item (Id)" '||
90 ',mif.description "Item Description" '||
91 ',revision "Rev" '||
92 ',mmt.cost_group_id "Cost Group Id" '||
93 ',mmt.subinventory_code "Subinv" '||
94 ',mil.description ||'' (''||mmt.locator_id||'') '' "Stock|Locator (Id)" '||
95 ',transfer_subinventory "Transfer Subinv" '||
96 ',transfer_locator_id "Transfer Location" '||
97 ',transaction_quantity "Txn Qty" '||
98 ',primary_quantity "Primary Qty" '||
99 ',transaction_uom "Txn UoM" '||
100 'FROM mtl_material_transactions mmt '||
101 ',mtl_transaction_types mtt '||
102 ',mtl_txn_source_types mtst '||
103 ',mtl_item_flexfields mif '||
104 ',mfg_lookups ml '||
105 ',mtl_item_locations_kfv mil '||
106 ',org_acct_periods oap '||
107 ',mtl_parameters mp '||
108 'WHERE mmt.transaction_type_id=mtt.transaction_type_id '||
109 'AND mmt.transaction_source_Type_id = mtst.transaction_source_type_id '||
110 'AND mmt.organization_id=mif.organization_id(+) '||
111 'AND mmt.inventory_item_id=mif.inventory_item_id(+) '||
112 'AND mmt.transaction_action_id=ml.lookup_code '||
113 'AND ml.lookup_type=''MTL_TRANSACTION_ACTION'' '||
114 'AND mmt.locator_id=mil.inventory_location_id(+) '||
115 'AND mmt.organization_id=mil.organization_id(+) '||
116 'AND mmt.organization_id = mp.organization_id(+) '||
117 'AND oap.organization_id = mmt.organization_id '||
118 'AND mmt.transaction_date BETWEEN trunc(oap.period_start_date) and trunc(oap.schedule_close_date) '||
119 'AND nvl(mmt.acct_period_id,-1) <> nvl(oap.acct_period_id,0)';
120
121 if l_org_id is not null then
122 sqltxt :=sqltxt||' and mmt.organization_id = '||l_org_id;
123 end if;
124
125 sqltxt := 'select * from ('||sqltxt||') WHERE ROWNUM <= '||row_limit;
126
127 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, 'Transactions with Incorrect Account Period in MMT ');
128
129 elsif l_script = 'lotleading' then
130 sqltxt :='select mp.organization_code|| '' (''||mln.organization_id ||'')'' "Organization|Code (Id)" '||
131 ',mif.item_number|| '' (''||mln.inventory_item_id||'')'' "Item (Id)" , '||
132 'lot_number "Lot number" '||
133 'from mtl_lot_numbers mln, '||
134 'mtl_parameters mp,mtl_item_flexfields mif '||
135 'where lot_number <> ltrim(lot_number) '||
136 'and mln.organization_id = mp.organization_id(+) '||
137 'and mln.inventory_item_id = mif.inventory_item_id(+) '||
138 'and mln.organization_id = mif.organization_id(+)';
139
140 if l_org_id is not null then
141 sqltxt :=sqltxt||' and mln.organization_id = '||l_org_id;
142 end if;
143
144 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, 'Lot Number with leading space in mtl_lot_numbers ');
145
146 sqltxt :='select mp.organization_code|| '' (''||mtln.organization_id ||'')'' "Organization|Code (Id)" '||
147 ',mif.item_number|| '' (''||mtln.inventory_item_id||'')'' "Item (Id)", '||
148 'lot_number "Lot number" '||
149 'from mtl_transaction_lot_numbers mtln, '||
150 'mtl_parameters mp,mtl_item_flexfields mif '||
151 'where lot_number <> ltrim(lot_number) '||
152 'and mtln.organization_id = mp.organization_id(+) '||
153 'and mtln.inventory_item_id = mif.inventory_item_id(+) '||
154 'and mtln.organization_id = mif.organization_id(+)';
155
156 if l_org_id is not null then
157 sqltxt :=sqltxt||' and mtln.organization_id = '||l_org_id;
158 end if;
159
160 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, 'Lot Number with leading space in mtl_transaction_lot_numbers ');
161
162 sqltxt :='select mp.organization_code|| '' (''||moqd.organization_id ||'')'' "Organization|Code (Id)" '||
163 ',mif.item_number|| '' (''||moqd.inventory_item_id||'')'' "Item (Id)" , '||
164 'lot_number "Lot number" '||
165 'from mtl_onhand_quantities_detail moqd, '||
166 'mtl_parameters mp,mtl_item_flexfields mif '||
167 'where lot_number <> ltrim(lot_number) '||
168 'and moqd.organization_id = mp.organization_id(+) '||
169 'and moqd.inventory_item_id = mif.inventory_item_id(+) '||
170 'and moqd.organization_id = mif.organization_id(+)';
171
172 if l_org_id is not null then
173 sqltxt :=sqltxt||' and moqd.organization_id = '||l_org_id;
174 end if;
175
176 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, 'Lot Number with leading space in mtl_onhand_quantities_detail ');
177
178 sqltxt :='select mp.organization_code|| '' (''||mr.organization_id ||'')'' "Organization|Code (Id)" '||
179 ',mif.item_number|| '' (''||mr.inventory_item_id||'')'' "Item (Id)", '||
180 'lot_number "Lot number" '||
181 'from mtl_reservations mr, '||
182 'mtl_parameters mp,mtl_item_flexfields mif '||
183 'where lot_number <> ltrim(lot_number) '||
184 'and mr.organization_id = mp.organization_id(+) '||
185 'and mr.inventory_item_id = mif.inventory_item_id(+) '||
186 'and mr.organization_id = mif.organization_id(+)';
187
188 if l_org_id is not null then
189 sqltxt :=sqltxt||' and mr.organization_id = '||l_org_id;
190 end if;
191
192 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, 'Lot Number with leading space in mtl_reservations ');
193
194 elsif l_script = 'neg_bal' then
195 sqltxt :='select mp.organization_code|| '' (''||mti.organization_id ||'')'' "OrganizationCode (Id)" '||
196 ',mif.item_number|| '' (''||mti.inventory_item_id||'')'' item '||
197 ',rev,sub,onhand,qty_avail,transaction_quantity '||
198 'from '||
199 ' (select mti.organization_id ,mti.inventory_item_id ,mti.revision rev, '||
200 ' mti.subinventory_code sub '||
201 ' ,INV_DIAG_GRP.CHECK_ONHAND(mti.inventory_item_id, mti.organization_id,mti.revision,mti.subinventory_code,mti.locator_id) onhand '||
202 ' ,INV_DIAG_GRP.CHECK_AVAIL(mti.inventory_item_id,mti.organization_id ,mti.revision ,mti.subinventory_code,mti.locator_id) qty_avail '||
203 ' ,transaction_quantity '||
204 ' from mtl_transactions_interface mti '||
205 ' group by mti.inventory_item_id,mti.organization_id,mti.revision, '||
206 ' mti.subinventory_code,mti.locator_id,transaction_quantity '||
207 ' order by mti.inventory_item_id) mti '||
208 ', mtl_parameters mp '||
209 ',mtl_item_flexfields mif '||
210 'where qty_avail < 0 '||
211 'and mti.organization_id = mp.organization_id '||
212 'and mti.inventory_item_id = mif.inventory_item_id '||
213 'and mti.organization_id = mif.organization_id ';
214
215 if l_org_id is not null then
216 sqltxt :=sqltxt||' and mti.organization_id = '||l_org_id;
217 end if;
218 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, 'Onhand Information Associated with Transactions stuck in MTI with Negative Balance Error');
219
220 sqltxt := 'select mp.organization_code|| '' (''||mti.organization_id ||'')'' "OrganizationCode (Id)" '||
221 ',mif.item_number|| '' (''||mti.inventory_item_id||'')'' item '||
222 ',rev,sub,onhand,qty_avail,transaction_quantity '||
223 'from '||
224 ' (select mti.organization_id ,mti.inventory_item_id ,mti.revision rev, '||
225 ' mti.subinventory_code sub '||
226 ' ,INV_DIAG_GRP.CHECK_ONHAND(mti.inventory_item_id, mti.organization_id,mti.revision,mti.subinventory_code,mti.locator_id) onhand '||
227 ' ,INV_DIAG_GRP.CHECK_AVAIL(mti.inventory_item_id,mti.organization_id ,mti.revision ,mti.subinventory_code,mti.locator_id) qty_avail '||
228 ' ,transaction_quantity '||
229 ' from mtl_material_transactions_temp mti '||
230 ' group by mti.inventory_item_id,mti.organization_id,mti.revision, '||
231 ' mti.subinventory_code,mti.locator_id,transaction_quantity '||
232 ' order by mti.inventory_item_id) mti '||
233 ', mtl_parameters mp '||
234 ',mtl_item_flexfields mif '||
235 'where qty_avail < 0 '||
236 'and mti.organization_id = mp.organization_id '||
237 'and mti.inventory_item_id = mif.inventory_item_id '||
238 'and mti.organization_id = mif.organization_id ';
239
240 if l_org_id is not null then
241 sqltxt :=sqltxt||' and mti.organization_id = '||l_org_id;
242 end if;
243 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, 'Onhand Information Associated with Transactions stuck in MMTT with Negative Balance Error');
244
245
246 elsif l_script = 'mut_dups' then
247 sqltxt :='select * from ( '||
248 'select transaction_id "Txn Id" , serial_number "Serial number" , '||
249 'mif.item_number ||'' (''||mut.inventory_item_id ||'')'' "Item (Id)", '||
250 'mp.organization_code ||'' (''|| mut.organization_id||'')'' "Org code (Id)" ,count(*) '||
251 'from mtl_unit_transactions mut, '||
252 'mtl_item_flexfields mif , '||
253 'mtl_parameters mp '||
254 'where mut.inventory_item_id = mif.inventory_item_id(+) '||
255 'and mut.organization_id = mif.organization_id (+) '||
256 'and mut.organization_id = mp.organization_id (+) '||
257 'and transaction_id >0 '||
258 'group by mut.transaction_id,mut.serial_number,mif.item_number,mut.inventory_item_id ,mp.organization_code,mut.organization_id '||
259 'having count(*) > 1) '||
260 'where rownum <= '||row_limit;
261
262 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, 'Duplicate Transactions in MTL_UNIT_TRANSACTIONS (max count: '||row_limit||' )');
263
267 'wdd1.source_header_number "Order Number", wdd1.source_line_id "Source line Id", '||
264 elsif l_script = 'overshipped' then
265 sqltxt :='select mp.organization_code|| '' (''||mmt.organization_id ||'')'' "Organization|Code (Id)" '||
266 ',mif.item_number|| '' (''||mmt.inventory_item_id||'')'' "Item (Id)" , '||
268 'mmt.mmt_qty "MMT Qty", wdd1.shp_qty "Shp qty",mmt.mmt_qty -wdd1.shp_qty "Diff Qty" FROM '||
269 '( select inventory_item_id, trx_source_line_id, organization_id, sum(abs(transaction_quantity)) mmt_qty '||
270 ' from mtl_material_transactions '||
271 ' where picking_line_id is not null '||
272 ' and transaction_source_type_id = 8 '||
273 ' group by inventory_item_id, trx_source_line_id, organization_id) mmt , '||
274 '( select wdd.source_header_number, wdd.source_line_id, wdd.inventory_item_id, '||
275 ' wdd.organization_id, sum(wdd.shipped_quantity) shp_qty '||
276 ' from wsh_delivery_details wdd '||
277 ' where wdd.source_code = ''OE'' '||
278 ' and wdd.released_status = ''C'' '||
279 ' and wdd.serial_number is null '||
280 ' group by wdd.source_header_number, wdd.source_line_id, wdd.inventory_item_id, wdd.organization_id) wdd1, '||
281 ' mtl_parameters mp,mtl_item_flexfields mif '||
282 ' where mmt.mmt_qty > wdd1.shp_qty '||
283 ' and mmt.trx_source_line_id = wdd1.source_line_id '||
284 ' and mmt.inventory_item_id = wdd1.inventory_item_id '||
285 ' and mmt.organization_id = wdd1.organization_id '||
286 ' and mmt.organization_id = mp.organization_id(+) '||
287 ' and mmt.inventory_item_id = mif.inventory_item_id(+) '||
288 ' and mmt.organization_id = mif.organization_id(+)';
289
290 if l_org_id is not null then
291 sqltxt :=sqltxt||' and mmt.organization_id = '||l_org_id;
292 end if;
293 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, 'Order Lines with Shipped Qty < Interfaced Qty (MMT) ');
294
295 for c1 in c_overshipline
296 loop
297 sqltxt:= 'select mif.item_number|| '' (''||mif.inventory_item_id||'')'' "Item (Id)" , '||
298 ' rsh.receipt_num "Receipt num", '||
299 ' rct.transaction_type "Transaction type", '||
300 ' rct.transaction_id transaction_id, '||
301 ' rct.quantity quantity, '||
302 ' rsl.item_id item_id, '||
303 ' rct.LAST_UPDATE_DATE LAST_UPDATE_DATE, '||
304 ' rct.LAST_UPDATED_BY LAST_UPDATED_BY, '||
305 ' rct.CREATION_DATE CREATION_DATE, '||
306 ' rct.CREATED_BY CREATED_BY, '||
307 ' rct.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN '||
308 'from rcv_transactions rct, '||
309 ' rcv_shipment_headers rsh, '||
310 ' rcv_shipment_lines rsl, '||
311 ' po_requisition_lines prl, '||
312 ' po_requisition_headers prh, '||
313 ' oe_order_lines_all oel, '||
314 ' oe_order_headers_all oeh, '||
315 ' mtl_item_Flexfields mif '||
316 'where rct.requisition_line_id = prl.requisition_line_id '||
317 'and rct.shipment_header_id = rsh.shipment_header_id '||
318 'and rct.shipment_line_id = rsl.shipment_line_id '||
319 'and oel.orig_sys_line_ref = to_char(prl.line_num) '||
320 'and oeh.orig_sys_document_ref = prh.segment1 '||
321 'and oel.header_id = oeh.header_id '||
322 'and prl.requisition_header_id = prh.requisition_header_id '||
323 'and rsl.item_id = mif.inventory_item_id(+) '||
324 'and rsl.to_organization_id = mif.organization_id(+) '||
325 'and oel.line_id = '||c1.source_line_id;
326
327 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, 'Receipt details for ship line id '||c1.source_line_id);
328 end loop;
329 elsif l_script = 'invfixcg' then
330 sqltxt :='SELECT mp.organization_code||'' (''||organization_id||'')'' "Organization code (Id)" '||
331 ', default_cost_group_id "Default Cost group Id" '||
332 'FROM mtl_parameters mp '||
333 'WHERE mp.primary_cost_method <> 1 ';
334
335 if l_org_id is not null then
336 sqltxt :=sqltxt||' and mp.organization_id = '||l_org_id;
337 end if;
338 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, 'Organization Default Cost Group Id ');
339
340 -- Bug 6690548: Added the where clause to check that the organization's primary cost method is not standard.
341 sqltxt :='SELECT mp.organization_code|| '' (''||moqd.organization_id ||'')'' "Organization|Code (Id)" '||
342 ',mif.item_number|| '' (''||moqd.inventory_item_id||'')'' "Item (Id)" '||
343 ',moqd.cost_group_id "Cost group Id" '||
344 ',mp.default_cost_group_id "Default Cost Group Id" '||
345 'FROM '||
346 'mtl_onhand_quantities_detail moqd, '||
347 'mtl_parameters mp, '||
348 'mtl_item_flexfields mif '||
349 'WHERE moqd.cost_group_id <> mp.default_cost_group_id '||
350 'and moqd.inventory_item_id = mif.inventory_item_id '||
351 'and moqd.organization_id = mp.organization_id '||
352 'and mp.primary_cost_method <> 1 '||
353 'and moqd.organization_id = mif.organization_id ';
354
355 if l_org_id is not null then
356 sqltxt :=sqltxt||' and moqd.organization_id = '||l_org_id;
357 end if;
358 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, 'Cost_group_id in MOQD is not the same as the default cost group id of the org ');
359
363 ' from cst_item_costs cic '||
360 for c2 in c_cstgrp
361 loop
362 sqltxt :='select count(1) '||
364 ' where cic.inventory_item_id = '||c2.inventory_item_id||
365 ' and cic.cost_type_id = '||c2.organization_id;
366
367 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, 'Count of records in cst_item_costs for the item-org '||c2.inventory_item_id||'-'||c2.organization_id);
368
369 -- Bug 6690548: Modified the where clause to retrieve only those records which have incorrect cost_group_id.
370 sqltxt :='select layer_id "Layer Id", '||
371 ' mp.organization_code|| '' (''||cql.organization_id ||'')'' "Organization|Code (Id)" '||
372 ',mif.item_number|| '' (''||cql.inventory_item_id||'')'' "Item (Id)", '||
373 ' cost_group_id "Cost Group Id", '||
374 ' layer_quantity "Layer Qty", '||
375 ' PL_MATERIAL, '||
376 ' PL_MATERIAL_OVERHEAD, '||
377 ' PL_RESOURCE, '||
378 ' PL_OUTSIDE_PROCESSING, '||
379 ' PL_OVERHEAD, '||
380 ' TL_MATERIAL, '||
381 ' TL_MATERIAL_OVERHEAD, '||
382 ' TL_RESOURCE, '||
383 ' TL_OUTSIDE_PROCESSING, '||
384 ' TL_OVERHEAD, '||
385 ' MATERIAL_COST, '||
386 ' MATERIAL_OVERHEAD_COST , '||
387 ' RESOURCE_COST, '||
388 ' OUTSIDE_PROCESSING_COST, '||
389 ' OVERHEAD_COST, '||
390 ' PL_ITEM_COST, '||
391 ' TL_ITEM_COST, '||
392 ' ITEM_COST, '||
393 ' UNBURDENED_COST, '||
394 ' BURDEN_COST, '||
395 ' CREATE_TRANSACTION_ID '||
396 'from '||
397 'cst_quantity_layers CQL, '||
398 'mtl_parameters mp, '||
399 'mtl_item_flexfields mif '||
400 'WHERE cql.organization_id = '||c2.organization_id ||
401 'AND cql.cost_group_id <> '||c2.default_cost_group_id ||
402 'AND cql.inventory_item_id = '||c2.inventory_item_id ||
403 'and cql.inventory_item_id = mif.inventory_item_id (+) '||
404 'and cql.organization_id = mif.organization_id (+)';
405
406 sqltxt := 'select * from ('||sqltxt||') WHERE ROWNUM <= '||row_limit;
407
408 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, 'Records in cst_quantity_layers for the item-org with incorrect cost_group '||c2.inventory_item_id||'-'||c2.organization_id);
409
410 sqltxt :='SELECT cicd.cost_element_id, '||
411 ' cicd.level_type, '||
412 ' cicd.last_update_date, '||
413 ' cicd.last_updated_by, '||
414 ' cicd.creation_date, '||
415 ' cicd.created_by, '||
416 ' cicd.request_id, '||
417 ' cicd.program_application_id, '||
418 ' cicd.program_id, '||
419 ' cicd.item_cost '||
420 'FROM cst_item_cost_details cicd '||
421 'WHERE cicd.inventory_item_id= '||c2.inventory_item_id ||
422 'AND cicd.organization_id = '||c2.organization_id ||
423 'AND cicd.cost_type_id = 2';
424
425 sqltxt := 'select * from ('||sqltxt||') WHERE ROWNUM <= '||row_limit;
426
427 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, 'Records in cst_item_cost_details for the item-org '||c2.inventory_item_id||'-'||c2.organization_id);
428
429 end loop;
430
431 elsif l_script = 'txn_src_mismatch' then
432 sqltxt :='select mmt.transaction_id "Txn Id" '||
433 ', mif.item_number ||'' (''|| mmt.inventory_item_id ||'')'' "Item (Id)" '||
434 ', mmt.transaction_date "Txn Date" '||
435 ', mmt.acct_period_id "Period Id" '||
436 ', mmt.transaction_quantity "Txn Qty" '||
437 ', mmt.primary_quantity "Prim Qty" '||
438 ', mmt.transaction_uom "Uom" '||
439 ', tt.transaction_type_name ||'' (''||mmt.transaction_type_id||'')'' "Txn Type (Id)" '||
440 ', mmt.subinventory_code "Subinv" '||
441 ', mmt.locator_id "Stock Locator" '||
442 ', mmt.revision "Rev" '||
443 ', mmt.costed_flag "Costed Flag" '||
444 ', mmt.creation_date "Created" '||
445 ', mmt.last_update_date "Last Updated" '||
446 ', ml.meaning || '' ('' ||mmt.transaction_action_id|| '')'' "Txn Action (Id)" '||
447 ', st.transaction_source_type_name ||'' (''|| mmt.transaction_source_type_id ||'')'' "Txn Source Type (Id)" '||
448 ', mmt.transaction_source_id "Txn Source Id" '||
449 ', mmt.transaction_source_name "Txn Source" '||
450 ', mmt.source_code "Source|Code" '||
451 ', mmt.source_line_id "Source Line Id" '||
452 ', mmt.request_id "Txn Request Id" '||
453 ', mmt.operation_seq_num "Operation|Seq Num" '||
454 ', mmt.transfer_transaction_id "Transfer Txn Id" '||
455 ', mmt.transfer_organization_id "Transfer Organization Id" '||
456 ', mmt.transfer_subinventory "Transfer Subinv" '||
457 ', mmt.shipment_number '||
458 'from mtl_material_transactions mmt '||
459 ', mtl_item_flexfields mif '||
460 ', mtl_transaction_types tt '||
461 ', mtl_txn_source_types st '||
462 ', mfg_lookups ml '||
463 'where '||
464 'mmt.organization_id = mif.organization_id(+) '||
465 'AND mmt.transaction_type_id = tt.transaction_type_id(+) '||
466 'AND mmt.transaction_source_type_id = st.transaction_source_type_id(+) '||
467 'AND mmt.transaction_action_id=ml.lookup_code '||
471 'and mmt.transaction_type_id = 50 '||
468 'AND ml.lookup_type = ''MTL_TRANSACTION_ACTION'' '||
469 'AND mmt.transaction_source_type_id = 8 '||
470 'and mmt.transaction_action_id = 2 '||
472 'and mmt.primary_quantity > 0 '||
473 'and mmt.transaction_id in ( '||
474 'select transfer_transaction_id from '||
475 'mtl_material_transactions '||
476 'where transaction_Source_type_id=8 '||
477 'and transaction_action_id=2 '||
478 'and transaction_type_id=50 '||
479 'and primary_quantity < 0)';
480
481 if l_org_id is not null then
482 sqltxt :=sqltxt||' and mmt.organization_id = '||l_org_id;
483 end if;
484 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, 'Txns in MMT not having source type of Internal Requistion for Internal order sub-transfer');
485
486 sqltxt :='select mmt.transaction_id "Txn Id" '||
487 ', mif.item_number ||'' (''|| mmt.inventory_item_id ||'')'' "Item (Id)" '||
488 ', mmt.transaction_date "Txn Date" '||
489 ', mmt.acct_period_id "Period Id" '||
490 ', mmt.transaction_quantity "Txn Qty" '||
491 ', mmt.primary_quantity "Prim Qty" '||
492 ', mmt.transaction_uom "Uom" '||
493 ', tt.transaction_type_name ||'' (''||mmt.transaction_type_id||'')'' "Txn Type (Id)" '||
494 ', mmt.subinventory_code "Subinv" '||
495 ', mmt.locator_id "Stock Locator" '||
496 ', mmt.revision "Rev" '||
497 ', mmt.costed_flag "Costed Flag" '||
498 ', mmt.creation_date "Created" '||
499 ', mmt.last_update_date "Last Updated" '||
500 ', ml.meaning || '' ('' ||mmt.transaction_action_id|| '')'' "Txn Action (Id)" '||
501 ', st.transaction_source_type_name ||'' (''|| mmt.transaction_source_type_id ||'')'' "Txn Source Type (Id)" '||
502 ', mmt.transaction_source_id "Txn Source Id" '||
503 ', mmt.transaction_source_name "Txn Source" '||
504 ', mmt.source_code "Source|Code" '||
505 ', mmt.source_line_id "Source Line Id" '||
506 ', mmt.request_id "Txn Request Id" '||
507 ', mmt.operation_seq_num "Operation|Seq Num" '||
508 ', mmt.transfer_transaction_id "Transfer Txn Id" '||
509 ', mmt.transfer_organization_id "Transfer Organization Id" '||
510 ', mmt.transfer_subinventory "Transfer Subinv" '||
511 ', mmt.shipment_number '||
512 'from mtl_material_transactions mmt '||
513 ', mtl_item_flexfields mif '||
514 ', mtl_transaction_types tt '||
515 ', mtl_txn_source_types st '||
516 ', mfg_lookups ml '||
517 'where '||
518 'mmt.organization_id = mif.organization_id(+) '||
519 'AND mmt.transaction_type_id = tt.transaction_type_id(+) '||
520 'AND mmt.transaction_source_type_id = st.transaction_source_type_id(+) '||
521 'AND mmt.transaction_action_id=ml.lookup_code '||
522 'AND ml.lookup_type = ''MTL_TRANSACTION_ACTION'' '||
523 'and mmt.transaction_source_type_id=8 '||
524 'and mmt.transaction_quantity >0 and mmt.transaction_action_id in (3,12) '||
525 'and not exists(select 1 from mtl_sales_orders where '||
526 'sales_order_id=transaction_source_id)';
527
528 if l_org_id is not null then
529 sqltxt :=sqltxt||' and mmt.organization_id = '||l_org_id;
530 end if;
531 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, 'Txns in MMT not having source type of Internal Requistion for Internal order Intransit receipt');
532
533 elsif l_script = 'duplicat_txn' then
534 sqltxt :='select a.transaction_interface_id , '||
535 'a.picking_line_id from '||
536 'mtl_material_transactions b, mtl_transactions_interface a '||
537 'where a.picking_line_id = b.picking_line_id '||
538 'and a.trx_source_line_id = b.trx_source_line_id '||
539 'and a.inventory_item_id = b.inventory_item_id '||
540 'and b.transaction_type_id = a.transaction_type_id '||
541 'and b.transaction_source_type_id in (2,8) '||
542 'and b.picking_line_id is not null';
543
544 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, 'Transactions duplicate in MMT and MTI ');
545
549 'where a.picking_line_id = b.picking_line_id '||
546 sqltxt :='select a.transaction_interface_id , '||
547 'a.picking_line_id from '||
548 'mtl_material_transactions_temp b, mtl_transactions_interface a '||
550 'and a.trx_source_line_id = b.trx_source_line_id '||
551 'and a.inventory_item_id = b.inventory_item_id '||
552 'and b.transaction_type_id = a.transaction_type_id '||
553 'and b.transaction_source_type_id in (2,8) '||
554 'and b.picking_line_id is not null ';
555
556
557 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, 'Transactions duplicate in MMTT and MTI');
558
559 sqltxt := 'select a.transaction_temp_id, '||
560 'a.picking_line_id from '||
561 'mtl_material_transactions b, mtl_material_transactions_temp a '||
562 'where a.picking_line_id = b.picking_line_id '||
563 'and a.trx_source_line_id = b.trx_source_line_id '||
564 'and a.inventory_item_id = b.inventory_item_id '||
565 'and b.transaction_type_id = a.transaction_type_id '||
566 'and b.transaction_source_type_id in ( 2,8) '||
567 'and b.picking_line_id is not null';
568
569
570 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, 'Transactions duplicate in MMTT and MTT');
571
572 elsif l_script = 'transfer_txn' then
573 sqltxt :='select a.transaction_id "Issue txn id" '||
574 ', b.transaction_id "Receipt txn id" '||
575 ', mp1.organization_code || '' ('' ||mp1.organization_id|| '')'' "Organization Code (Id)" '||
576 ', mp2.organization_code || '' ('' ||mp2.organization_id|| '')'' "Transfer Org Code (Id)" '||
577 ', mtt.transaction_type_name ||'' (''||a.transaction_type_id||'')'' "Txn Type (Id)" '||
578 ',a.costed_flag "Costed flag" '||
579 ',mif.item_number ||'' (''||a.inventory_item_id||'')'' "Item (Id)" '||
580 ' from mtl_material_transactions a,mtl_material_transactions b , '||
581 ' mtl_parameters mp1, '||
582 ' mtl_parameters mp2, '||
583 ' mtl_item_flexfields mif, '||
584 ' mtl_transaction_types mtt '||
585 ' WHERE '||
586 ' a.inventory_item_id = mif.inventory_item_id(+) '||
587 ' AND a.organization_id = mif.organization_id(+) '||
588 ' AND a.organization_id = mp1.organization_id(+) '||
589 ' AND b.organization_id = mp2.organization_id(+) '||
590 ' AND a.transfer_transaction_id is null '||
591 ' and a.transaction_id=b.transaction_id - 1 '||
592 ' and a.inventory_item_id = b.inventory_item_id '||
593 ' and a.transaction_action_id = b.transaction_action_id '||
594 ' AND a.transaction_type_id = mtt.transaction_type_id (+) '||
595 ' and a.transaction_quantity < 0 '||
596 ' and a.transaction_action_id in (3,2,28)';
597
598 if l_org_id is not null then
599 sqltxt :=sqltxt||' and a.organization_id = '||l_org_id;
600 end if;
601 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, 'Transfer Transaction null');
602
603
604 end if;
605
606 statusStr := 'SUCCESS';
607 isFatal := 'FALSE';
608
609 -- construct report
610 report := JTF_DIAGNOSTIC_ADAPTUTIL.constructReport(statusStr,errStr,fixInfo,isFatal);
611 reportClob := JTF_DIAGNOSTIC_ADAPTUTIL.getReportClob;
612 END runTest;
613
614 PROCEDURE getComponentName(name OUT NOCOPY VARCHAR2) IS
615 BEGIN
616 name := 'Transaction Diagnostic Scripts ';
617 END getComponentName;
618
619 PROCEDURE getTestDesc(descStr OUT NOCOPY VARCHAR2) IS
620 BEGIN
621 descStr := 'Diagnostic Scripts for Transactions';
622 END getTestDesc;
623
624 PROCEDURE getTestName(name OUT NOCOPY VARCHAR2) IS
625 BEGIN
626 name := 'Transaction Diagnostic Scripts ';
627 END getTestName;
628
629 PROCEDURE getDependencies (package_names OUT NOCOPY JTF_DIAG_DEPENDTBL) IS
630 tempDependencies JTF_DIAG_DEPENDTBL;
631
632 BEGIN
633 package_names := JTF_DIAGNOSTIC_ADAPTUTIL.initDependencyTable;
634 END getDependencies;
635
636 PROCEDURE isDependencyPipelined (str OUT NOCOPY VARCHAR2) IS
637 BEGIN
638 str := 'FALSE';
639 END isDependencyPipelined;
640
641
642 PROCEDURE getOutputValues(outputValues OUT NOCOPY JTF_DIAG_OUTPUTTBL) IS
643 tempOutput JTF_DIAG_OUTPUTTBL;
644 BEGIN
645 tempOutput := JTF_DIAGNOSTIC_ADAPTUTIL.initOutputTable;
646 outputValues := tempOutput;
647 EXCEPTION
648 when others then
649 outputValues := JTF_DIAGNOSTIC_ADAPTUTIL.initOutputTable;
650 END getOutputValues;
651
652
653 PROCEDURE getDefaultTestParams(defaultInputValues OUT NOCOPY JTF_DIAG_INPUTTBL) IS
654 tempInput JTF_DIAG_INPUTTBL;
655 BEGIN
656 tempInput := JTF_DIAGNOSTIC_ADAPTUTIL.initinputtable;
657 --tempInput := JTF_DIAGNOSTIC_ADAPTUTIL.addInput(tempInput,'testout','');
658 tempInput := JTF_DIAGNOSTIC_ADAPTUTIL.addInput(tempInput,'OrgId','LOV-oracle.apps.inv.diag.lov.OrganizationLov');
659 tempInput := JTF_DIAGNOSTIC_ADAPTUTIL.addInput(tempInput,'ScriptName','LOV-oracle.apps.inv.diag.lov.TxnDiagScriptsLov');
660 defaultInputValues := tempInput;
661 EXCEPTION
662 when others then
663 defaultInputValues := JTF_DIAGNOSTIC_ADAPTUTIL.initinputtable;
664 END getDefaultTestParams;
665
666 Function getTestMode return INTEGER IS
667 BEGIN
668 return JTF_DIAGNOSTIC_ADAPTUTIL.ADVANCED_MODE;
669
670 END getTestMode;
671 END;