[Home] [Help]
PACKAGE BODY: APPS.WIP_DIAG_DISCRETE_LOT
Source
1 package body WIP_DIAG_DISCRETE_LOT as
2 /* $Header: WIPDJOBB.pls 120.0.12020000.2 2012/09/06 11:24:05 sisankar ship $ */
3
4 PROCEDURE Uncosted_mat_txn_wdj ( p_org_id IN NUMBER,
5 report OUT NOCOPY JTF_DIAG_REPORT,
6 reportClob OUT NOCOPY CLOB
7 ) IS
8
9 where_clause1 varchar2(999);
10 where_clause2 varchar2(999);
11 row_limit NUMBER;
12 BEGIN
13
14 where_clause := null ;
15 row_limit := 1000;
16
17 if p_org_id is not null then
18 where_clause := ' and we.organization_id = ' || p_org_id || ' ';
19 where_clause1 := ' and wdj.organization_id = ' || p_org_id || ' ';
20 where_clause2 := ' and wrs.organization_id = ' || p_org_id || ' ';
21 reportStr := '<U> Organization Id = ' || p_org_id || ' </U><BR>';
22 JTF_DIAGNOSTIC_ADAPTUTIL.addStringToReport(reportClob,reportStr);
23 end if ;
24
25 sqltxt :=
26 'select transaction_source_id wip_entity_id, ' ||
27 ' decode(entity_type,1, ''1=Discrete Job'',' ||
28 ' 2, ''2=Repetitive Assly'',' ||
29 ' 3, ''3=Closed Discr Job'',' ||
30 ' 4, ''4=Flow Schedule'',' ||
31 ' 5, ''5=Lot Based Job'',' ||
32 ' entity_type) entity_type,' ||
33 ' mmt.organization_id, transaction_date, move_transaction_id, completion_transaction_id,' ||
34 ' transaction_type_id,' ||
35 ' decode(transaction_action_id,' ||
36 ' 1, ''Issue'',' ||
37 ' 2, ''Subinv Xfr'',' ||
38 ' 3, ''Org Xfr'',' ||
39 ' 4, ''Cycle Count Adj'',' ||
40 ' 5, ''Issue'',' ||
41 ' 21, ''Intransit Shpmt'',' ||
42 ' 24, ''Cost Update'',' ||
43 ' 27, ''Receipt'',' ||
44 ' 28, ''Stg Xfr'',' ||
45 ' 30, ''Wip scrap'',' ||
46 ' 31, ''Assy Complete'',' ||
47 ' 32, ''Assy return'',' ||
48 ' 33, ''-ve CompIssue'',' ||
49 ' 34, ''-ve CompReturn'',' ||
50 ' 40, ''Inv Lot Split'',' ||
51 ' 41, ''Inv Lot Merge'',' ||
52 ' 42, ''Inv Lot Translate'',' ||
53 ' 42, ''Inv Lot Translate'',' ||
54 ' transaction_action_id) txn_action_meaning' ||
55 ' from mtl_material_transactions mmt, ' ||
56 ' wip_entities we' ||
57 ' where mmt.transaction_source_type_id = 5' ||
58 ' and mmt.costed_flag = ''E''' ||
59 ' and mmt.error_code = ''CST_INVALID_JOB_DATE''' ||
60 ' and mmt.transaction_source_id = we.wip_entity_id' ||
61 ' and mmt.organization_id = we.organization_id' ||
62 where_clause ||
63 ' and we.entity_type in (1,5) ' ||
64 ' union ' ||
65 'select mmt.transaction_source_id wip_entity_id , ' ||
66 ' ''1=Discrete Job'' entity_type ,' ||
67 ' mmt.organization_id, transaction_date, move_transaction_id, completion_transaction_id,' ||
68 ' transaction_type_id,' ||
69 ' decode(transaction_action_id,' ||
70 ' 1, ''Issue'',' ||
71 ' 2, ''Subinv Xfr'',' ||
72 ' 3, ''Org Xfr'',' ||
73 ' 4, ''Cycle Count Adj'',' ||
74 ' 5, ''Issue'',' ||
75 ' 21, ''Intransit Shpmt'',' ||
76 ' 24, ''Cost Update'',' ||
77 ' 27, ''Receipt'',' ||
78 ' 28, ''Stg Xfr'',' ||
79 ' 30, ''Wip scrap'',' ||
80 ' 31, ''Assy Complete'',' ||
81 ' 32, ''Assy return'',' ||
82 ' 33, ''-ve CompIssue'',' ||
83 ' 34, ''-ve CompReturn'',' ||
84 ' 40, ''Inv Lot Split'',' ||
85 ' 41, ''Inv Lot Merge'',' ||
86 ' 42, ''Inv Lot Translate'',' ||
87 ' 42, ''Inv Lot Translate'',' ||
88 ' transaction_action_id) txn_action_meaning ' ||
89 ' from mtl_material_transactions mmt, ' ||
90 ' wip_discrete_jobs wdj' ||
91 ' where mmt.transaction_source_type_id = 5' ||
92 ' and mmt.costed_flag = ''N''' ||
93 ' and mmt.transaction_source_id = wdj.wip_entity_id' ||
94 ' and mmt.organization_id = wdj.organization_id' ||
95 where_clause1 ||
96 ' and mmt.transaction_date < wdj.date_released ' ||
97 ' union ' ||
98 'select mmt.transaction_source_id wip_entity_id , ' ||
99 ' ''2=Repetitive Assly'' entity_type ,' ||
100 ' mmt.organization_id, mmt.transaction_date, mmt.move_transaction_id, mmt.completion_transaction_id,' ||
101 ' mmt.transaction_type_id,' ||
102 ' decode(mmt.transaction_action_id,' ||
103 ' 1, ''Issue'',' ||
104 ' 2, ''Subinv Xfr'',' ||
105 ' 3, ''Org Xfr'',' ||
106 ' 4, ''Cycle Count Adj'',' ||
107 ' 5, ''Issue'',' ||
108 ' 21, ''Intransit Shpmt'',' ||
109 ' 24, ''Cost Update'',' ||
110 ' 27, ''Receipt'',' ||
111 ' 28, ''Stg Xfr'',' ||
112 ' 30, ''Wip scrap'',' ||
113 ' 31, ''Assy Complete'',' ||
114 ' 32, ''Assy return'',' ||
115 ' 33, ''-ve CompIssue'',' ||
116 ' 34, ''-ve CompReturn'',' ||
117 ' 40, ''Inv Lot Split'',' ||
118 ' 41, ''Inv Lot Merge'',' ||
119 ' 42, ''Inv Lot Translate'',' ||
120 ' 42, ''Inv Lot Translate'',' ||
121 ' mmt.transaction_action_id) txn_action_meaning ' ||
122 ' from mtl_material_transactions mmt, ' ||
123 ' mtl_material_txn_allocations mmta, ' ||
124 ' wip_repetitive_schedules wrs' ||
125 ' where mmt.transaction_id = mmta.transaction_id ' ||
126 ' and mmt.transaction_source_type_id = 5' ||
127 ' and mmt.costed_flag = ''N''' ||
128 ' and mmt.transaction_source_id = wrs.wip_entity_id' ||
129 ' and mmta.repetitive_schedule_id = wrs.repetitive_schedule_id ' ||
130 ' and mmt.organization_id = wrs.organization_id' ||
131 where_clause2 ||
132 ' and mmt.transaction_date < wrs.date_released ' ||
133 ' order by 1, 3 ' ;
134
135 dummy_num := JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
136 ' Transactions where transaction_date is before the job release date ',true,null,'Y',row_limit) ;
137
138 IF (dummy_num = row_limit) THEN
139 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR> Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file. <BR>');
140 END IF;
141
142 if dummy_num > 0 then
143 reportStr := 'The rows returned above indicates transaction date before Job/Schedule Release Date.' ;
144 JTF_DIAGNOSTIC_COREAPI.ErrorPrint(reportStr);
145 JTF_DIAGNOSTIC_COREAPI.ActionErrorLink('Please refer to metalink note ', 402202.1, ' to get the root-cause patch and steps to correct the data.<BR> <BR>') ;
146
147 /*
148 if apps_ver = '11.5.10' then
149
150 reportStr := '<BR> Action:' ;
151 JTF_DIAGNOSTIC_ADAPTUTIL.addStringToReport(reportClob,reportStr);
152
153 reportStr := '<BR> For Release 11.5.10 :' ;
154 JTF_DIAGNOSTIC_ADAPTUTIL.addStringToReport(reportClob,reportStr);
155
156 JTF_DIAGNOSTIC_COREAPI.ActionErrorLink('<BR>Please refer to metalink note ', 402202.1, ' to get the root-cause patch and steps to correct the data.<BR> <BR>') ;
157
158 --JTF_DIAGNOSTIC_COREAPI.ErrorPrint(reportStr) ;
159 -- JTF_DIAGNOSTIC_ADAPTUTIL.addStringToReport(reportClob,reportStr);
160
161 elsif apps_ver = '11.5.9' then
162
163 -- reportStr := '<BR> For Release 11.5.9 :' ;
164 -- JTF_DIAGNOSTIC_ADAPTUTIL.addStringToReport(reportClob,reportStr);
165 reportStr := '<BR> Root cause patch not available. If issue can be reproduced at will, please log a service request againt WIP with steps to reproduce' ;
166 -- JTF_DIAGNOSTIC_ADAPTUTIL.addStringToReport(reportClob,reportStr);
167 JTF_DIAGNOSTIC_COREAPI.ErrorPrint(reportStr);
168
169 reportStr := '<BR>Execute procedure WIP_WDJ_DFIX_UNCOSTED_MAT.update_mmt_for_jobs(organization_id, wip_entity_id) ' ;
170 JTF_DIAGNOSTIC_COREAPI.ErrorPrint(reportStr);
171 -- JTF_DIAGNOSTIC_ADAPTUTIL.addStringToReport(reportClob,reportStr);
172 end if ;
173 **/
174
175 end if ;
176
177
178 sqltxt := ' SELECT ' ||
179 ' WDJ.WIP_ENTITY_ID, ' ||
180 ' WE.WIP_ENTITY_NAME,' ||
181 ' decode(we.entity_type, ' ||
182 ' 1, ''Discrete Job'',' ||
183 ' 2, ''Repetitive Schedule'',' ||
184 ' 3, ''Closed Discrete Job'',' ||
185 ' 4, ''Flow/Work Order-less'',' ||
186 ' 5, ''Lot Based Job'',' ||
187 ' 6, ''EAM Job'',' ||
188 ' we.entity_type) entity_type , ' ||
189 ' WDJ.ORGANIZATION_ID, ' ||
190 ' OAP.acct_period_id,' ||
191 ' WDJ.DATE_RELEASED,' ||
192 ' WAC.CLASS_TYPE ' ||
193 ' FROM WIP_ACCOUNTING_CLASSES WAC, ' ||
194 ' ORG_ACCT_PERIODS OAP, ' ||
195 ' WIP_DISCRETE_JOBS WDJ,' ||
196 ' WIP_ENTITIES WE' ||
197 ' WHERE WDJ.STATUS_TYPE IN (3, 4, 5, 6, 7, 14, 15) ' ||
198 ' AND WE.ENTITY_TYPE IN (1,3,5) ' ||
199 ' AND WAC.CLASS_CODE = WDJ.CLASS_CODE ' ||
200 ' AND WDJ.ORGANIZATION_ID = WAC.ORGANIZATION_ID ' ||
201 ' AND OAP.ORGANIZATION_ID = WDJ.ORGANIZATION_ID ' ||
202 ' AND WDJ.WIP_ENTITY_ID = WE.WIP_ENTITY_ID' ||
203 ' AND WDJ.ORGANIZATION_ID = WE.ORGANIZATION_ID' ||
204 ' AND OAP.OPEN_FLAG = ''Y'' ' ||
205 ' AND OAP.PERIOD_CLOSE_DATE IS NULL ' ||
206 ' AND OAP.SCHEDULE_CLOSE_DATE >= NVL(WDJ.DATE_RELEASED, WDJ.CREATION_DATE) ' ||
207 ' AND WAC.CLASS_TYPE != 2 ' ||
208 where_clause ||
209 ' AND NOT EXISTS ' ||
210 ' ( ' ||
211 ' SELECT ''X'' FROM WIP_PERIOD_BALANCES WPB ' ||
212 ' WHERE WPB.REPETITIVE_SCHEDULE_ID IS NULL ' ||
213 ' AND WPB.WIP_ENTITY_ID = WDJ.WIP_ENTITY_ID ' ||
214 ' AND WPB.ORGANIZATION_ID = WDJ.ORGANIZATION_ID ' ||
215 ' AND WPB.ACCT_PERIOD_ID = OAP.ACCT_PERIOD_ID)' ||
216 ' order by we.wip_entity_name, we.organization_id' ;
217
218 dummy_num := JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
219 ' Transactions erred due to missing record in WIP_PERIOD_BALANCES (error: CST_NO_BALANCE_ROW)',true,null,'Y',row_limit) ;
220
221 IF (dummy_num = row_limit) THEN
222 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR> Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file. <BR>');
223 END IF;
224
225 if dummy_num > 0 then
226 reportStr := 'The rows returned above indicate record is missing in WIP_PERIOD_BALANCES.' ;
227 JTF_DIAGNOSTIC_COREAPI.ErrorPrint(reportStr);
228 JTF_DIAGNOSTIC_COREAPI.ActionErrorLink('Please refer to metalink note ', 402202.1, ' to get the root-cause patch and steps to correct the data.<BR> <BR>') ;
229
230 /*
231 reportStr := '<BR> Action:' ;
232 JTF_DIAGNOSTIC_ADAPTUTIL.addStringToReport(reportClob,reportStr);
233
234 reportStr := reportStr || '<BR> 1.Execute procedure WIP_WDJ_DFIX_UNCOSTED_MAT.create_wpb(organization_id, wip_entity_id)';
235 -- JTF_DIAGNOSTIC_ADAPTUTIL.addStringToReport(reportClob,reportStr);
236
237 reportStr := reportStr || '<BR> The values for the parameters can be obtained using the above output.' ;
238 -- JTF_DIAGNOSTIC_ADAPTUTIL.addStringToReport(reportClob,reportStr);
239
240 reportStr := reportStr || '<BR> 2. Please log a service request with steps to reproduce if Client can reproduce issue at will.' ;
241 -- JTF_DIAGNOSTIC_ADAPTUTIL.addStringToReport(reportClob,reportStr);
242 JTF_DIAGNOSTIC_COREAPI.ErrorPrint(reportStr);
243 */
244
245
246 sqltxt :=
247 'select mmtt.transaction_header_id, ' ||
248 ' decode(mmtt.TRANSACTION_TYPE_ID,35,''Component Issue'',''Component Return'') Transaction, ' ||
249 ' substr(we.WIP_ENTITY_NAME,1,20) Job, ' ||
250 ' mmtt.transaction_source_id WIP_ENTITY_ID, ' ||
251 ' mmtt.inventory_item_id ITEM_ID, ' ||
252 ' substr(msi.segment1,1,20) COMPONENT, ' ||
253 ' mmtt.SUBINVENTORY_CODE SUBINVENTORY, ' ||
254 ' substr(mmtt.locator_segments,1,20) LOC_SEGMENTS, ' ||
255 ' mmtt.item_primary_uom_code UOM, ' ||
256 ' mmtt.number_of_lots_entered NO_LOTS_ENTERED, ' ||
257 ' mtlt.transaction_quantity MTLT_TRX_QUANTITY, ' ||
258 ' mtlt.primary_quantity MTLT_PRI_QUANTITY, ' ||
259 ' mtlt.lot_number MTLT_LOT_NUMBER, ' ||
260 ' mmtt.number_of_lots_entered - nvl(mmtt.transaction_quantity,0) QTY_TOBE_ADJUSTED ' ||
261 ' from ' ||
262 ' mtl_material_transactions_temp mmtt, ' ||
263 ' mtl_transaction_lots_temp mtlt, ' ||
264 ' mtl_system_items msi, ' ||
265 ' wip_entities we ' ||
266 ' where ' ||
267 ' msi.inventory_item_id = mmtt.inventory_item_id ' ||
268 ' AND mmtt.TRANSACTION_SOURCE_ID = we.wip_entity_id ' ||
269 ' AND msi.ORGANIZATION_ID = mmtt.organization_id ' ||
270 where_clause ||
271 ' AND nvl(mmtt.TRANSACTION_QUANTITY,0) < mmtt.number_of_lots_entered ' ||
272 ' AND mmtt.TRANSACTION_SOURCE_TYPE_ID = 5 ' ||
273 ' AND mmtt.TRANSACTION_ACTION_ID in (1,27) ' ||
274 ' AND mmtt.TRANSACTION_TYPE_ID in (35,43,33,34) ' ||
275 ' AND mmtt.number_of_lots_entered is NOT NULL ' ||
276 ' AND (mmtt.move_transaction_id is NOT NULL or completion_transaction_id is NOT NULL) ' ||
277 ' AND mmtt.PROCESS_FLAG = ''E'' ' ||
278 ' AND mmtt.error_code = ''BF_LOT_ERROR'' ' ||
279 ' AND mmtt.item_serial_controL_code = 1 -- not serial controled ' ||
280 ' AND mmtt.transaction_temp_id = mtlt.transaction_temp_id(+) ' ||
281 ' order by we.WIP_ENTITY_ID, mmtt.transaction_header_id, mmtt.transaction_temp_id ' ;
282
283 IF (dummy_num = row_limit) THEN
284 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR> Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file. <BR>');
285 END IF;
286
287 dummy_num := JTF_DIAGNOSTIC_COREAPI.display_sql( sqltxt, 'Stuck Pending Material Transaction',true,null,'Y',row_limit) ;
288 if dummy_num > 0 then
289 reportStr := 'The rows returned above indicates Lots can''t be derived successfully.' ;
290 JTF_DIAGNOSTIC_COREAPI.ErrorPrint(reportStr);
291 JTF_DIAGNOSTIC_COREAPI.ActionErrorLink('Please refer to metalink note ', 402202.1, ' to get the root-cause patch and steps to correct the data.<BR> <BR>') ;
292 end if ;
293
294
295 end if ;
296 END uncosted_mat_txn_wdj ;
297
298 procedure corrupt_osp_txn_wdj ( p_org_id IN NUMBER,
299 report OUT NOCOPY JTF_DIAG_REPORT,
300 reportClob OUT NOCOPY CLOB
301 ) IS
302 l_len number ;
303 row_limit NUMBER;
304 BEGIN
305 where_clause := null ;
306 row_limit := 1000;
307
308 if p_org_id is not null then
309 where_clause := ' and organization_id = ' || p_org_id || ' ';
310 reportStr := '<U> Organization Id = ' || p_org_id || ' </U><BR>';
311 JTF_DIAGNOSTIC_ADAPTUTIL.addStringToReport(reportClob,reportStr);
312 end if ;
313
314 sqltxt :=
315 ' SELECT TRANSACTION_ID,' ||
316 ' INTERFACE_TRANSACTION_ID,' ||
317 ' REQUEST_ID,' ||
318 ' PROGRAM_APPLICATION_ID,' ||
319 ' PROGRAM_ID,' ||
320 ' TRANSACTION_DATE,' ||
321 ' TRANSACTION_TYPE,' ||
322 ' QUANTITY,' ||
323 ' UNIT_OF_MEASURE,' ||
324 ' PO_HEADER_ID,' ||
325 ' WIP_ENTITY_ID,' ||
326 ' WIP_OPERATION_SEQ_NUM,' ||
327 ' ORGANIZATION_ID' ||
328 ' FROM RCV_TRANSACTIONS RT' ||
329 ' WHERE DESTINATION_TYPE_CODE = ''SHOP FLOOR''' ||
330 ' AND WIP_ENTITY_ID IS NOT NULL' ||
331 ' AND WIP_OPERATION_SEQ_NUM IS NOT NULL' ||
332 ' AND WIP_RESOURCE_SEQ_NUM IS NOT NULL' ||
333 where_clause ||
334 ' AND EXISTS (SELECT 1 FROM WIP_TRANSACTIONS WT' ||
335 ' WHERE WT.RCV_TRANSACTION_ID = RT.TRANSACTION_ID' ||
336 ' AND WT.WIP_ENTITY_ID = RT.WIP_ENTITY_ID' ||
337 ' AND WT.OPERATION_SEQ_NUM = RT.WIP_OPERATION_SEQ_NUM' ||
338 ' AND WT.RESOURCE_SEQ_NUM = RT.WIP_RESOURCE_SEQ_NUM' ||
339 ' AND WT.TRANSACTION_TYPE = 3' ||
340 ' AND WT.PRIMARY_QUANTITY = 0' ||
341 ' AND WT.ACTUAL_RESOURCE_RATE = 0 )' ||
342 ' AND NOT EXISTS (SELECT 1 FROM WIP_TRANSACTIONS WT' ||
343 ' WHERE WT.RCV_TRANSACTION_ID = RT.TRANSACTION_ID' ||
344 ' AND WT.WIP_ENTITY_ID = RT.WIP_ENTITY_ID' ||
345 ' AND WT.OPERATION_SEQ_NUM = RT.WIP_OPERATION_SEQ_NUM' ||
346 ' AND WT.RESOURCE_SEQ_NUM = RT.WIP_RESOURCE_SEQ_NUM' ||
347 ' AND WT.TRANSACTION_TYPE = 3' ||
348 ' AND WT.PRIMARY_QUANTITY <> 0' ||
349 ' AND WT.ACTUAL_RESOURCE_RATE <> 0 )' ||
350 ' AND EXISTS (SELECT 1 FROM WIP_DISCRETE_JOBS WDJ' ||
351 ' WHERE WDJ.WIP_ENTITY_ID = RT.WIP_ENTITY_ID' ||
352 ' AND WDJ.ORGANIZATION_ID = RT.ORGANIZATION_ID' ||
353 ' AND WDJ.JOB_TYPE = 1' ||
354 ' AND WDJ.STATUS_TYPE IN (3,4))' ||
355 ' AND NOT EXISTS (SELECT 1 FROM WIP_COST_TXN_INTERFACE WCTI' ||
356 ' WHERE WCTI.SOURCE_LINE_ID = RT.INTERFACE_TRANSACTION_ID ) ' ;
357
358 dummy_num := JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
359 ' OSP receipts whose corresponding jobs were not charged',true,null,'Y',row_limit) ;
360
361 IF (dummy_num = row_limit) THEN
362 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR> Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file. <BR>');
363 END IF;
364
365 if dummy_num > 0 then
366 reportStr := 'The rows returned above indicate OSP Resource is not charged after items are delivered to ShopFloor.' ;
367 JTF_DIAGNOSTIC_COREAPI.ErrorPrint(reportStr);
368 JTF_DIAGNOSTIC_COREAPI.ActionErrorPrint('Please open a service request against Oracle Work in Process for the data-fix and root-cause, and upload the output of this diagnostic test.<BR><BR>');
369 end if ;
370
371 END corrupt_osp_txn_wdj ;
372
373 procedure dup_mat_txn_wdj ( p_org_id IN NUMBER,
374 report OUT NOCOPY JTF_DIAG_REPORT,
375 reportClob OUT NOCOPY CLOB
376 ) IS
377 row_limit NUMBER;
378 BEGIN
379 where_clause := null ;
380 row_limit := 1000;
381
382 if p_org_id is not null then
383 where_clause := ' and organization_id = ' || p_org_id || ' ';
384 reportStr := '<U> Organization Id = ' || p_org_id || ' </U><BR>';
385 JTF_DIAGNOSTIC_ADAPTUTIL.addStringToReport(reportClob,reportStr);
386 end if ;
387
388 sqltxt :=
389 'SELECT organization_id, ' ||
390 ' transaction_source_id,' ||
391 ' transaction_type_id,' ||
392 ' inventory_item_id, ' ||
393 ' primary_quantity,' ||
394 ' subinventory_code,' ||
395 ' locator_id' ||
396 ' completion_transaction_id,' ||
397 ' source_line_id,' ||
398 ' Count(*) ' ||
399 ' FROM mtl_material_transactions ' ||
400 ' WHERE transaction_source_type_id = 5 ' ||
401 where_clause ||
402 ' AND completion_transaction_id IS NOT null ' ||
403 'HAVING Count(*) > 1 ' ||
404 'GROUP BY organization_id, ' ||
405 ' transaction_source_id,' ||
406 ' transaction_type_id,' ||
407 ' inventory_item_id, ' ||
408 ' primary_quantity,' ||
409 ' subinventory_code,' ||
410 ' locator_id,' ||
411 ' completion_transaction_id,'||
412 ' source_line_id';
413
414 dummy_num := JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
415 ' Duplicate material transactions in case of WIP completion ',true,null,'Y',row_limit) ;
416
417 IF (dummy_num = row_limit) THEN
418 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR> Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file. <BR>');
419 END IF;
420
421 if dummy_num > 0 then
422 reportStr := 'The rows returned above indicates duplicate transactions.' ;
423 JTF_DIAGNOSTIC_COREAPI.ErrorPrint(reportStr);
424 JTF_DIAGNOSTIC_COREAPI.ActionErrorPrint('Please open a service request against Oracle Work in Process for the data-fix and root-cause, and upload the output of this diagnostic test.<BR><BR> ');
425 end if ;
426
427 sqltxt :=
428 'SELECT organization_id, ' ||
429 ' transaction_source_id,' ||
430 ' transaction_type_id,' ||
431 ' inventory_item_id, ' ||
432 ' primary_quantity,' ||
433 ' subinventory_code,' ||
434 ' locator_id,' ||
435 ' move_transaction_id,' ||
436 ' source_line_id,' ||
437 ' Count(*) ' ||
438 ' FROM mtl_material_transactions ' ||
439 ' WHERE transaction_source_type_id = 5 ' ||
440 ' AND completion_transaction_id IS NULL ' ||
441 ' AND move_transaction_id IS NOT NULL ' ||
442 where_clause ||
443 ' HAVING Count(*) > 1 ' ||
444 ' GROUP BY organization_id, ' ||
445 ' transaction_source_id,' ||
446 ' transaction_type_id,' ||
447 ' inventory_item_id, ' ||
448 ' primary_quantity,' ||
449 ' subinventory_code,' ||
450 ' locator_id,' ||
451 ' move_transaction_id,' ||
452 ' source_line_id';
453 dummy_num := JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
454 ' Duplicate backflush records for wip move transactions ',true,null,'Y',row_limit) ;
455
456 IF (dummy_num = row_limit) THEN
457 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR> Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file. <BR>');
458 END IF;
459
460 if dummy_num > 0 then
461 reportStr := 'The rows returned above indicates duplicate backflush records originating from Move Transaction.' ;
462 JTF_DIAGNOSTIC_COREAPI.ErrorPrint(reportStr);
463 JTF_DIAGNOSTIC_COREAPI.ActionErrorPrint('Please open a service request against Oracle Work in Process for the data-fix and root-cause, and upload the output of this diagnostic test.<BR><BR>');
464 end if ;
465 END dup_mat_txn_wdj ;
466
467 BEGIN
468
469 apps_ver := JTF_DIAGNOSTIC_COREAPI.Get_DB_Apps_Version ;
470
471 END WIP_DIAG_DISCRETE_LOT ;