[Home] [Help]
PACKAGE BODY: APPS.WIP_DIAG_WOL_FLOW
Source
1 package body WIP_DIAG_WOL_FLOW as
2 /* $Header: WIPDWOLB.pls 120.0.12000000.1 2007/07/10 11:08:37 mraman noship $ */
3
4 PROCEDURE Uncosted_mat_txn_wol(inputs IN JTF_DIAG_INPUTTBL,
5 report OUT NOCOPY JTF_DIAG_REPORT,
6 reportClob OUT NOCOPY CLOB) IS
7 reportStr LONG; -- REPORT
8 sqltxt VARCHAR2(9999); -- SQL select statement
9 c_username VARCHAR2(50); -- accept input for username
10 statusStr VARCHAR2(50); -- SUCCESS or FAILURE
11 errStr VARCHAR2(4000); -- error message
12 fixInfo VARCHAR2(4000); -- fix tip
13 isFatal VARCHAR2(50); -- TRUE or FALSE
14 dummy_num NUMBER;
15 row_limit NUMBER;
16 l_org_id NUMBER;
17 where_clause VARCHAR2(4000) := NULL; -- where clause
18 BEGIN
19 JTF_DIAGNOSTIC_ADAPTUTIL.setUpVars;
20 JTF_DIAGNOSTIC_ADAPTUTIL.addStringToReport('@html');
21 JTF_DIAGNOSTIC_COREAPI.insert_style_sheet;
22 row_limit := 1000;
23 -- accept input
24 l_org_id := JTF_DIAGNOSTIC_ADAPTUTIL.getInputValue('OrganizationId',inputs);
25
26 If l_org_id is not null then
27 where_clause := ' and organization_id = ' || l_org_id || ' ';
28 reportStr := ' Organization Id = ' || l_org_id || ' <BR>';
29 JTF_DIAGNOSTIC_ADAPTUTIL.addStringToReport(reportClob,reportStr);
30 end if;
31 sqltxt :=
32 'select '||
33 ' mmt1.transaction_source_id, '||
34 ' mmt1.transaction_id, '||
35 ' mmt1.organization_id, '||
36 ' mmt1.completion_transaction_id, '||
37 ' mmt1.move_transaction_id, '||
38 ' nvl(mmt1.costed_flag, ''Y'') costed_flag, '||
39 ' decode(mmt1.transaction_action_id, '||
40 ' 1, ''Issue'', '||
41 ' 2, ''Subinv Xfr'', '||
42 ' 3, ''Org Xfr'', '||
43 ' 4, ''Cycle Count Adj'', '||
44 ' 5, ''Plan Xfr'', '||
45 ' 21, ''Intransit Shpmt'', '||
46 ' 24, ''Cost Update'', '||
47 ' 27, ''Receipt'', '||
48 ' 28, ''Stg Xfr'', '||
49 ' 30, ''Wip scrap'', '||
50 ' 31, ''Assy Complete'', '||
51 ' 32, ''Assy return'', '||
52 ' 33, ''-ve CompIssue'', '||
53 ' 34, ''-ve CompReturn'', '||
54 ' 40, ''Inv Lot Split'', '||
55 ' 41, ''Inv Lot Merge'', '||
56 ' 42, ''Inv Lot Translate'', '||
57 ' 42, ''Inv Lot Translate'', '||
58 ' transaction_action_id) txn_action_meaning, '||
59 ' mmt1.error_code, '||
60 ' substrb(mmt1.error_explanation,1,50) err_explain '||
61 ' from mtl_material_transactions mmt1 '||
62 ' where mmt1.transaction_action_id in (1, 27, 33, 34) '||
63 ' and mmt1.transaction_source_type_id = 5 '||
64 ' and mmt1.flow_schedule = ''Y'' '||
65 ' and mmt1.costed_flag = ''E'' '||
66 ' and mmt1.completion_transaction_id is not null '||
67 ' and mmt1.transaction_source_id is not null '||
68 where_clause ||
69 ' and exists (select 1 '||
70 ' from mtl_material_transactions mmt2 '||
71 ' where mmt2.transaction_action_id in (30, 31, 32) '||
72 ' and mmt2.transaction_source_type_id = 5 '||
73 ' and mmt2.completion_transaction_id = '||
74 ' mmt1.completion_transaction_id '||
75 ' and mmt2.flow_schedule = ''Y'' '||
76 ' and mmt2.costed_flag in (''N'', ''E'') '||
77 where_clause ||
78 ' ) '||
79 ' and exists (select 1 '||
80 ' from wip_flow_schedules wfs '||
81 ' where wfs.wip_entity_id = mmt1.transaction_source_id '||
82 ' and wfs.organization_id = mmt1.organization_id) '||
83 ' order by transaction_source_id, transaction_action_id';
84
85
86 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
87 'Component transaction(s) in MMT erred during costing with Parent transaction either' ||
88 ' not costed or erred in MMT (error: CST_INVALID_WIP).',true,null,'Y',row_limit);
89
90 IF (dummy_num = row_limit) THEN
91 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR> Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file. <BR>');
92 END IF;
93
94 If ( dummy_num > 0) then
95
96 reportStr := 'The rows returned above signify data inconsistency in component transaction(s).' ||
97 ' These are component transactions in MMT for Work Order-less / Flow for which the parent assembly' ||
98 ' transaction is either not costed or erred.';
99 JTF_DIAGNOSTIC_COREAPI.ErrorPrint(reportStr);
100 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>');
101
102 end if;
103
104 statusStr := 'SUCCESS';
105 isFatal := 'FALSE';
106 fixInfo := 'OK';
107 errStr :='No Error!';
108
109
110 sqltxt :=
111 'select '||
112 ' mmt1.transaction_source_id, '||
113 ' mmt1.transaction_id, '||
114 ' mmt1.organization_id, '||
115 ' mmt1.completion_transaction_id, '||
116 ' mmt1.move_transaction_id, '||
117 ' nvl(mmt1.costed_flag, ''Y'') costed_flag, '||
118 ' decode(mmt1.transaction_action_id, '||
119 ' 1, ''Issue'', '||
120 ' 2, ''Subinv Xfr'', '||
121 ' 3, ''Org Xfr'', '||
122 ' 4, ''Cycle Count Adj'', '||
123 ' 5, ''Plan Xfr'', '||
124 ' 21, ''Intransit Shpmt'', '||
125 ' 24, ''Cost Update'', '||
126 ' 27, ''Receipt'', '||
127 ' 28, ''Stg Xfr'', '||
128 ' 30, ''Wip scrap'', '||
129 ' 31, ''Assy Complete'', '||
130 ' 32, ''Assy return'', '||
131 ' 33, ''-ve CompIssue'', '||
132 ' 34, ''-ve CompReturn'', '||
133 ' 40, ''Inv Lot Split'', '||
134 ' 41, ''Inv Lot Merge'', '||
135 ' 42, ''Inv Lot Translate'', '||
136 ' 42, ''Inv Lot Translate'', '||
137 ' transaction_action_id) txn_action_meaning, '||
138 ' mmt1.error_code, '||
139 ' substrb(mmt1.error_explanation,1,50) err_explain '||
140 ' from mtl_material_transactions mmt1 '||
141 ' where mmt1.transaction_action_id in (1, 27, 33, 34) '||
142 ' and mmt1.transaction_source_type_id = 5 '||
143 ' and mmt1.flow_schedule = ''Y'' '||
144 ' and mmt1.costed_flag = ''E'' '||
145 ' and mmt1.completion_transaction_id is not null '||
146 ' and mmt1.transaction_source_id is not null '||
147 where_clause ||
148 ' and not exists (select 1 '||
149 ' from mtl_material_transactions mmt2 '||
150 ' where mmt2.transaction_action_id in (30, 31, 32) '||
151 ' and mmt2.transaction_source_type_id = 5 '||
152 ' and mmt2.completion_transaction_id = '||
153 ' mmt1.completion_transaction_id '||
154 ' and mmt2.flow_schedule = ''Y'' '||
155 where_clause ||
156 ' ) '||
157 ' order by transaction_source_id, transaction_action_id';
158
159
160 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
161 'Component transaction(s) in MMT erred during costing with missing Parent transaction in MMT (error: CST_INVALID_WIP).',true,null,'Y',row_limit);
162
163 IF (dummy_num = row_limit) THEN
164 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR> Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file. <BR>');
165 END IF;
166
167 If ( dummy_num > 0) then
168
169 reportStr := 'The rows returned above signify data inconsistency among component transaction(s). These are component transactions' || ' in MMT for Work Order-less / Flow transactions for which the parent assembly transaction is missing in MMT.';
170 JTF_DIAGNOSTIC_COREAPI.ErrorPrint(reportStr);
171 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>');
172
173 end if;
174
175 statusStr := 'SUCCESS';
176 isFatal := 'FALSE';
177 fixInfo := 'OK';
178 errStr :='No Error!';
179
180 sqltxt :=
181 'select mmt.transaction_source_id, '||
182 ' mmt.transaction_id, '||
183 ' mmt.organization_id, '||
184 ' mmt.completion_transaction_id, '||
185 ' mmt.move_transaction_id, '||
186 ' nvl(mmt.costed_flag, ''Y''), '||
187 ' decode(mmt.transaction_action_id, '||
188 ' 1, ''Issue'', '||
189 ' 2, ''Subinv Xfr'', '||
190 ' 3, ''Org Xfr'', '||
191 ' 4, ''Cycle Count Adj'', '||
192 ' 5, ''Plan Xfr'', '||
193 ' 21, ''Intransit Shpmt'', '||
194 ' 24, ''Cost Update'', '||
195 ' 27, ''Receipt'', '||
196 ' 28, ''Stg Xfr'', '||
197 ' 30, ''Wip scrap'', '||
198 ' 31, ''Assy Complete'', '||
199 ' 32, ''Assy return'', '||
200 ' 33, ''-ve CompIssue'', '||
201 ' 34, ''-ve CompReturn'', '||
202 ' 40, ''Inv Lot Split'', '||
203 ' 41, ''Inv Lot Merge'', '||
204 ' 42, ''Inv Lot Translate'', '||
205 ' 42, ''Inv Lot Translate'', '||
206 ' transaction_action_id) txn_action_meaning, '||
207 ' mmt.error_code, '||
208 ' mmt.error_explanation '||
209 'from mtl_material_transactions mmt '||
210 'where mmt.transaction_action_id not in (30, 31, 32) /* All Non parent transactions */ '||
211 'and mmt.transaction_source_type_id = 5 /* WIP */'||
212 'and mmt.flow_schedule = ''Y'' '||
213 'and mmt.costed_flag in (''N'', ''E'') '||
214 where_clause ||
215 'and exists ( select 1 '||
216 ' from mtl_material_transactions mmt1 '||
217 ' where mmt1.transaction_action_id in (30, 31, 32) '||
218 ' /* Parent Transactions */ '||
219 ' and mmt1.transaction_source_type_id = 5 /* WIP */ '||
220 ' and mmt1.flow_schedule = ''Y'' '||
221 ' and mmt1.costed_flag is null /* Parent is costed */ '||
222 ' and mmt1.completion_transaction_id = '||
223 ' mmt.completion_transaction_id '||
224 where_clause ||
225 ' ) '||
226 'order by transaction_source_id, transaction_action_id';
227
228
229 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
230 'Component transaction(s) in MMT Uncosted/erred during costing with costed Parent transaction in MMT (error: CST_INVALID_WIP).',true,null,'Y',row_limit);
231
232 IF (dummy_num = row_limit) THEN
233 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR> Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file. <BR>');
234 END IF;
235
236 If ( dummy_num > 0) then
237
238 reportStr := 'The rows returned above signify data inconsistency among component transaction(s). These are component transactions' || ' in MMT for Work Order-less / Flow transactions for which the parent assembly transaction is costed.';
239 JTF_DIAGNOSTIC_COREAPI.ErrorPrint(reportStr);
240 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>');
241
242 end if;
243
244 statusStr := 'SUCCESS';
245 isFatal := 'FALSE';
246 fixInfo := 'OK';
247 errStr :='No Error!';
248
249 sqltxt :=
250 ' select '||
251 ' mmt.transaction_source_id, '||
252 ' mmt.transaction_id, '||
253 ' mmt.organization_id, '||
254 ' mmt.completion_transaction_id, '||
255 ' mmt.move_transaction_id, '||
256 ' nvl(mmt.costed_flag, ''Y'') costed_flag, '||
257 ' decode(mmt.transaction_action_id, '||
258 ' 1, ''Issue'', '||
259 ' 2, ''Subinv Xfr'', '||
260 ' 3, ''Org Xfr'', '||
261 ' 4, ''Cycle Count Adj'', '||
262 ' 5, ''Plan Xfr'', '||
263 ' 21, ''Intransit Shpmt'', '||
264 ' 24, ''Cost Update'', '||
265 ' 27, ''Receipt'', '||
266 ' 28, ''Stg Xfr'', '||
267 ' 30, ''Wip scrap'', '||
268 ' 31, ''Assy Complete'', '||
269 ' 32, ''Assy return'', '||
270 ' 33, ''-ve CompIssue'', '||
271 ' 34, ''-ve CompReturn'', '||
272 ' 40, ''Inv Lot Split'', '||
273 ' 41, ''Inv Lot Merge'', '||
274 ' 42, ''Inv Lot Translate'', '||
275 ' 42, ''Inv Lot Translate'', '||
276 ' transaction_action_id) txn_action_meaning, '||
277 ' mmt.error_code, '||
278 ' substrb(mmt.error_explanation,1,50) err_explain '||
279 ' from mtl_material_transactions mmt '||
283 ' and mmt.flow_schedule = ''Y'' '||
280 ' where mmt.transaction_action_id in (1,27,33,34,30,31,32) '||
281 ' and mmt.transaction_type_id in (17,35, 43,44,90,38,48) '||
282 ' and mmt.transaction_source_type_id = 5 '||
284 ' and mmt.costed_flag in (''N'', ''E'') '||
285 ' and mmt.transaction_source_id is not null '||
286 where_clause ||
287 ' and not exists (select 1 '||
288 ' from wip_flow_schedules wfs '||
289 ' where wfs.wip_entity_id = mmt.transaction_source_id '||
290 ' and wfs.organization_id = mmt.organization_id '||
291 ' ) '||
292 ' order by transaction_source_id, transaction_action_id';
293
294
295 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
296 'Transaction(s) in MMT Uncosted/erred during costing with missing flow schedule (error: CST_INVALID_WIP).',true,null,'Y',row_limit);
297
298 IF (dummy_num = row_limit) THEN
299 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR> Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file. <BR>');
300 END IF;
301
302 If ( dummy_num > 0) then
303
304 reportStr := 'The rows returned above signify data inconsistency among MMT transaction(s) for Work Order-less / Flow' ||
305 ' transactions. These are transactions in MMT for Work Order-less / Flow transactions for which the flow schedule is missing.';
306 JTF_DIAGNOSTIC_COREAPI.ErrorPrint(reportStr);
307 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>');
308
309 end if;
310
311 statusStr := 'SUCCESS';
312 isFatal := 'FALSE';
313 fixInfo := 'OK';
314 errStr :='No Error!';
315
316
317 sqltxt :=
318 ' select '||
319 ' mmt.transaction_source_id, '||
320 ' mmt.transaction_id, '||
321 ' mmt.organization_id, '||
322 ' mmt.completion_transaction_id, '||
323 ' mmt.move_transaction_id, '||
324 ' nvl(mmt.costed_flag, ''Y'') costed_flag, '||
325 ' decode(mmt.transaction_action_id, '||
326 ' 1, ''Issue'', '||
327 ' 2, ''Subinv Xfr'', '||
328 ' 3, ''Org Xfr'', '||
329 ' 4, ''Cycle Count Adj'', '||
330 ' 5, ''Plan Xfr'', '||
331 ' 21, ''Intransit Shpmt'', '||
332 ' 24, ''Cost Update'', '||
333 ' 27, ''Receipt'', '||
334 ' 28, ''Stg Xfr'', '||
335 ' 30, ''Wip scrap'', '||
336 ' 31, ''Assy Complete'', '||
337 ' 32, ''Assy return'', '||
338 ' 33, ''-ve CompIssue'', '||
339 ' 34, ''-ve CompReturn'', '||
340 ' 40, ''Inv Lot Split'', '||
341 ' 41, ''Inv Lot Merge'', '||
342 ' 42, ''Inv Lot Translate'', '||
343 ' 42, ''Inv Lot Translate'', '||
344 ' transaction_action_id) txn_action_meaning, '||
345 ' mmt.error_code, '||
346 ' substrb(mmt.error_explanation,1,50) err_explain '||
347 ' from mtl_material_transactions mmt '||
348 ' where mmt.transaction_action_id in (1, 27, 33, 34) '||
349 ' and mmt.transaction_source_type_id = 5 '||
350 ' and mmt.flow_schedule = ''Y'' '||
351 ' and mmt.costed_flag in (''N'', ''E'') '||
352 ' and mmt.transaction_source_id is not NULL '||
353 ' and mmt.completion_transaction_id is not null '||
354 where_clause ||
355 ' and exists (select 1 '||
356 ' from mtl_material_transactions mmt1 '||
357 ' WHERE mmt1.transaction_action_id in (30, 31, 32) '||
358 ' and mmt1.transaction_source_type_id = 5 '||
359 ' and mmt1.completion_transaction_id = mmt.completion_transaction_id '||
360 ' and mmt1.flow_schedule = ''Y'' '||
361 ' and mmt1.transaction_source_id <> mmt.transaction_source_id '||
362 ' ) '||
363 ' order by transaction_source_id, transaction_action_id ';
364
365 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
366 'Component transaction(s) in MMT erred during costing due to backflush against an incorrect flow schedule with Parent transaction either not costed or erred in MMT (error: CST_INVALID_WIP).',true,null,'Y',row_limit);
367
368 IF (dummy_num = row_limit) THEN
369 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR> Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file. <BR>');
370 END IF;
371
372 If ( dummy_num > 0) then
373
374 reportStr := 'The rows returned above signify data inconsistency among MMT component transaction(s) for'||
375 ' Work Order-less / Flow transactions. These are component transactions in MMT' ||
376 ' for Work Order-less / Flow transactions with incorrect transaction_source_id.';
377 JTF_DIAGNOSTIC_COREAPI.ErrorPrint(reportStr);
378 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>');
379
380 end if;
381
382 statusStr := 'SUCCESS';
383 isFatal := 'FALSE';
384 fixInfo := 'OK';
385 errStr :='No Error!';
386 <<l_test_end>>
387 report := JTF_DIAGNOSTIC_ADAPTUTIL.constructReport(statusStr,errStr,fixInfo,isFatal);
388 reportClob := JTF_DIAGNOSTIC_ADAPTUTIL.getReportClob;
389
390 END Uncosted_mat_txn_wol;
391
392 PROCEDURE Pending_res_txn_wol(inputs IN JTF_DIAG_INPUTTBL,
393 report OUT NOCOPY JTF_DIAG_REPORT,
394 reportClob OUT NOCOPY CLOB) IS
395 reportStr LONG; -- REPORT
396 sqltxt VARCHAR2(9999); -- SQL select statement
397 c_username VARCHAR2(50); -- accept input for username
398 statusStr VARCHAR2(50); -- SUCCESS or FAILURE
399 errStr VARCHAR2(4000); -- error message
400 fixInfo VARCHAR2(4000); -- fix tip
404 l_org_id NUMBER;
401 isFatal VARCHAR2(50); -- TRUE or FALSE
402 dummy_num NUMBER;
403 row_limit NUMBER;
405 where_clause VARCHAR2(4000) := NULL; -- where clause
406 BEGIN
407 JTF_DIAGNOSTIC_ADAPTUTIL.setUpVars;
408 JTF_DIAGNOSTIC_ADAPTUTIL.addStringToReport('@html');
409 JTF_DIAGNOSTIC_COREAPI.insert_style_sheet;
410 -- accept input
411 l_org_id := JTF_DIAGNOSTIC_ADAPTUTIL.getInputValue('OrganizationId',inputs);
412
413 If l_org_id is not null then
414 where_clause := ' and organization_id = ' || l_org_id || ' ';
415 reportStr := ' Organization Id = ' || l_org_id || ' <BR>';
416 JTF_DIAGNOSTIC_ADAPTUTIL.addStringToReport(reportClob,reportStr);
417 end if;
418
419 sqltxt :=
420 'select wip_entity_id, '||
421 ' decode(to_char(completion_transaction_id), null, ''?'', '''') miss_comp, '||
422 ' organization_id '||
423 ' transaction_id, '||
424 ' completion_transaction_id, '||
425 ' process_status '||
426 'from wip_cost_txn_interface '||
427 'where wip_entity_id in '||
428 ' (select mmt1.transaction_source_id '||
429 ' from mtl_material_transactions mmt1 '||
430 ' where mmt1.transaction_source_type_id = 5 /* WIP */'||
431 ' and mmt1.flow_schedule = ''Y'' '||
432 ' and mmt1.costed_flag in (''N'', ''E'') '||
433 ' and mmt1.error_code = ''CST_INVALID_WIP'' '||
434 where_clause ||
435 ' ) '||
436 where_clause ||
437 'order by wip_entity_id, transaction_id';
438
439
440 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
441 'Pending Resource transaction(s) due to UnCosted/erred MMT transaction.',true,null,'Y',row_limit);
442
443 IF (dummy_num = row_limit) THEN
444 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR> Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file. <BR>');
445 END IF;
446
447 If ( dummy_num > 0) then
448
449 reportStr := 'The rows returned above signify data inconsistency in resource transaction(s) for Work Order-less / Flow transactions. These are pending transactions in WCTI for uncosted / erred Work Order-less / Flow transactions in MMT.';
450 JTF_DIAGNOSTIC_COREAPI.ErrorPrint(reportStr);
451 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>');
452
453 end if;
454
455 statusStr := 'SUCCESS';
456 isFatal := 'FALSE';
457 fixInfo := 'OK';
458 errStr :='No Error!';
459
460
461 sqltxt :=
462 ' select wcti.wip_entity_name ScheduleNumber, '||
463 ' wcti.wip_entity_id, '||
464 ' wcti.organization_id, '||
465 ' wcti.department_code, '||
466 ' wcti.operation_seq_num, '||
467 ' wcti.resource_seq_num, '||
468 ' wcti.resource_code, '||
469 ' wcti.transaction_id, '||
470 ' wcti.completion_transaction_id '||
471 ' from wip_cost_txn_interface wcti '||
472 ' where entity_type = 4 -- Flow '||
473 where_clause ||
474 ' and exists (select 1 '||
475 ' from mtl_material_transactions mmt '||
476 ' where mmt.transaction_action_id in (30, 31, 32) '||
477 ' and mmt.transaction_source_type_id = 5 /* WIP */'||
478 ' and mmt.flow_schedule = ''Y'' '||
479 ' and mmt.costed_flag is null /* Parent is costed */'||
480 ' and mmt.completion_transaction_id = wcti.completion_transaction_id '||
481 where_clause || ' )';
482
483
484 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
485 'Work Order-less / Flow Pending Resource transaction(s) with Costed parent MMT transaction.',true,null,'Y',row_limit);
486
487 IF (dummy_num = row_limit) THEN
488 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR> Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file. <BR>');
489 END IF;
490
491 If ( dummy_num > 0) then
492
493 reportStr := 'The rows returned above signify data inconsistency in resource transaction(s) for Work Order-less / Flow transactions. These are transactions in WCTI for which parent Work Order-less / Flow transactions is costed.';
494 JTF_DIAGNOSTIC_COREAPI.ErrorPrint(reportStr);
495 JTF_DIAGNOSTIC_COREAPI.ActionErrorPrint('Records listed above are not eligible for Costing as Parent transaction is already costed.<BR>' ||
496 ' In this event, variances must have been posted. You should thus post a manual journal entry to transfer the value back from variance account' ||
497 ' into WIP valuation acounts. Finally, delete the corresponding pending resource transaction(s) record.<BR><BR>');
498
499 end if;
500
501 statusStr := 'SUCCESS';
502 isFatal := 'FALSE';
503 fixInfo := 'OK';
504 errStr :='No Error!';
505
506 <<l_test_end>>
507 report := JTF_DIAGNOSTIC_ADAPTUTIL.constructReport(statusStr,errStr,fixInfo,isFatal);
508 reportClob := JTF_DIAGNOSTIC_ADAPTUTIL.getReportClob;
509
510 END Pending_res_txn_wol;
511
512 PROCEDURE Invalid_txn_mti_wol(inputs IN JTF_DIAG_INPUTTBL,
513 report OUT NOCOPY JTF_DIAG_REPORT,
514 reportClob OUT NOCOPY CLOB) IS
515 reportStr LONG; -- REPORT
516 sqltxt VARCHAR2(9999); -- SQL select statement
517 c_username VARCHAR2(50); -- accept input for username
518 statusStr VARCHAR2(50); -- SUCCESS or FAILURE
519 errStr VARCHAR2(4000); -- error message
520 fixInfo VARCHAR2(4000); -- fix tip
521 isFatal VARCHAR2(50); -- TRUE or FALSE
522 dummy_num NUMBER;
523 row_limit NUMBER;
524 l_item_id NUMBER;
525 l_org_id NUMBER;
526 where_clause VARCHAR2(4000) := NULL; -- where clause
527 BEGIN
528 JTF_DIAGNOSTIC_ADAPTUTIL.setUpVars;
529 JTF_DIAGNOSTIC_ADAPTUTIL.addStringToReport('@html');
530 JTF_DIAGNOSTIC_COREAPI.insert_style_sheet;
531 -- accept input
535 where_clause := ' and organization_id = ' || l_org_id || ' ';
532 l_org_id := JTF_DIAGNOSTIC_ADAPTUTIL.getInputValue('OrganizationId',inputs);
533
534 If l_org_id is not null then
536 reportStr := ' Organization Id = ' || l_org_id || ' <BR>';
537 JTF_DIAGNOSTIC_ADAPTUTIL.addStringToReport(reportClob,reportStr);
538 end if;
539
540 /* Bug 5731956: Added if condition to check existence of column COMPLETION_TRANSACTION_ID*/
541 if JTF_DIAGNOSTIC_COREAPI.Column_Exists('MTL_TRANSACTIONS_INTERFACE','COMPLETION_TRANSACTION_ID', 'INV') = 'Y' then
542 sqltxt :=
543 'SELECT transaction_source_id, '||
544 ' organization_id, '||
545 ' transaction_interface_id, '||
546 ' parent_id, '||
547 ' completion_transaction_id '||
548 'FROM mtl_transactions_interface mti '||
549 'WHERE mti.transaction_source_type_id = 5 '||
550 'AND mti.transaction_action_id IN (1, 27, 33, 34) '||
551 'AND mti.flow_schedule = ''Y'' '||
552 where_clause ||
553 'AND NOT EXISTS '||
554 ' (SELECT 1 FROM mtl_transactions_interface mti2 '||
555 ' WHERE mti2.organization_id = mti.organization_id '||
556 ' AND mti2.transaction_source_type_id = 5 '||
557 ' AND mti2.completion_transaction_id = mti.completion_transaction_id '||
558 ' AND mti2.transaction_action_id NOT IN (1, 27, 33, 34) '||
559 ' AND mti2.transaction_interface_id = mti.parent_id' ||
560 where_clause || ' ) '||
561 'AND NOT EXISTS '||
562 ' (SELECT 1 FROM mtl_material_transactions mmt '||
563 ' WHERE mmt.organization_id = mti.organization_id '||
564 ' AND mmt.transaction_source_type_id = 5 '||
565 ' AND mmt.completion_transaction_id = mti.completion_transaction_id '||
566 ' AND mmt.costed_flag IN (''N'',''E'') '||
567 ' AND mmt.transaction_action_id NOT IN (1, 27, 33, 34)) ';
568
569 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
570 'Component transaction(s) in transactions open interface whose associated assembly transaction '||
571 'is costed or missing in material transactions table(MMT).',true,null,'Y',row_limit);
572
573 IF (dummy_num = row_limit) THEN
574 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR> Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file. <BR>');
575 END IF;
576
577 If ( dummy_num > 0) then
578
579 reportStr := 'The rows returned above signify data inconsistency in component transaction(s) for Work Order-less / Flow transactions.' ||
580 ' These are component transactions in MTI for which parent Work Order-less / Flow transactions is costed or missing.';
581 JTF_DIAGNOSTIC_COREAPI.ErrorPrint(reportStr);
582 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>');
583
584 end if;
585
586 statusStr := 'SUCCESS';
587 isFatal := 'FALSE';
588 fixInfo := 'OK';
589 errStr :='No Error!';
590
591
592 sqltxt :=
593 'SELECT transaction_source_id, '||
594 ' organization_id, '||
595 ' transaction_interface_id, '||
596 ' parent_id, '||
597 ' completion_transaction_id '||
598 'FROM mtl_transactions_interface mti '||
599 'WHERE mti.transaction_source_type_id = 5 '||
600 'AND mti.transaction_action_id IN (1, 27, 33, 34) '||
601 'AND mti.flow_schedule = ''Y'' '||
602 where_clause ||
603 'AND NOT EXISTS '||
604 ' (SELECT 1 FROM mtl_transactions_interface mti2 '||
605 ' WHERE mti2.organization_id = mti.organization_id '||
606 ' AND mti2.transaction_source_type_id = 5 '||
607 ' AND mti2.completion_transaction_id = mti.completion_transaction_id '||
608 ' AND mti2.transaction_action_id NOT IN (1, 27, 33, 34) '||
609 ' AND mti2.transaction_interface_id = mti.parent_id' ||
610 where_clause || ' ) '||
611 'AND EXISTS '||
612 ' (SELECT 1 FROM mtl_material_transactions mmt '||
613 ' WHERE mmt.organization_id = mti.organization_id '||
614 ' AND mmt.transaction_source_type_id = 5 '||
615 ' AND mmt.completion_transaction_id = mti.completion_transaction_id '||
616 ' AND mmt.costed_flag IN (''N'',''E'') '||
617 ' AND mmt.transaction_action_id NOT IN (1, 27, 33, 34)) ';
618
619
620 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, 'Component transaction(s) in transactions open interface whose associated assembly transaction is not costed.',true,null,'Y',row_limit);
621
622 IF (dummy_num = row_limit) THEN
623 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR> Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file. <BR>');
624 END IF;
625
626 If ( dummy_num > 0) then
627
628 reportStr := 'The rows returned above signify data inconsistency in component transaction(s) for Work Order-less / Flow transactions.' ||
629 ' These are component transactions in MTI for which parent Work Order-less / Flow transactions is not costed.';
630 JTF_DIAGNOSTIC_COREAPI.ErrorPrint(reportStr);
631 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>');
632
633 end if;
634 else
635 reportStr := 'This instance is not able to run this test as it is not on the required patchset level.';
636 JTF_DIAGNOSTIC_COREAPI.WarningPrint(reportStr);
640 statusStr := 'SUCCESS';
637 JTF_DIAGNOSTIC_COREAPI.ActionWarningPrint('No action required.');
638 end if;
639
641 isFatal := 'FALSE';
642 fixInfo := 'OK';
643 errStr :='No Error!';
644
645
646 <<l_test_end>>
647 report := JTF_DIAGNOSTIC_ADAPTUTIL.constructReport(statusStr,errStr,fixInfo,isFatal);
648 reportClob := JTF_DIAGNOSTIC_ADAPTUTIL.getReportClob;
649
650 END Invalid_txn_mti_wol;
651
652 PROCEDURE Dup_mat_txn_mti(inputs IN JTF_DIAG_INPUTTBL,
653 report OUT NOCOPY JTF_DIAG_REPORT,
654 reportClob OUT NOCOPY CLOB) IS
655 reportStr LONG; -- REPORT
656 sqltxt VARCHAR2(9999); -- SQL select statement
657 c_username VARCHAR2(50); -- accept input for username
658 statusStr VARCHAR2(50); -- SUCCESS or FAILURE
659 errStr VARCHAR2(4000); -- error message
660 fixInfo VARCHAR2(4000); -- fix tip
661 isFatal VARCHAR2(50); -- TRUE or FALSE
662 dummy_num NUMBER;
663 row_limit NUMBER;
664 l_org_id NUMBER;
665 where_clause VARCHAR2(4000) := NULL; -- where clause
666 BEGIN
667 JTF_DIAGNOSTIC_ADAPTUTIL.setUpVars;
668 JTF_DIAGNOSTIC_ADAPTUTIL.addStringToReport('@html');
669 JTF_DIAGNOSTIC_COREAPI.insert_style_sheet;
670 row_limit := 1000;
671 -- accept input
672 l_org_id := JTF_DIAGNOSTIC_ADAPTUTIL.getInputValue('OrganizationId',inputs);
673
674 If l_org_id is not null then
675 where_clause := ' and organization_id = ' || l_org_id || ' ';
676 reportStr := ' Organization Id = ' || l_org_id || ' <BR>';
677 JTF_DIAGNOSTIC_ADAPTUTIL.addStringToReport(reportClob,reportStr);
678 end if;
679
680 sqltxt :=
681 'SELECT organization_id, '||
682 ' transaction_source_id, '||
683 ' transaction_type_id, '||
684 ' inventory_item_id, '||
685 ' primary_quantity, '||
686 ' subinventory_code, '||
687 ' locator_id '||
688 ' completion_transaction_id, '||
689 ' Count(*) '||
690 'FROM mtl_material_transactions '||
691 'WHERE transaction_source_type_id = 5 '||
692 'AND completion_transaction_id IS NOT null '||
693 'AND flow_schedule = ''Y'' '||
694 where_clause ||
695 'HAVING Count(*) > 1 '||
696 'GROUP BY organization_id, '||
697 ' transaction_source_id, '||
698 ' transaction_type_id, '||
699 ' inventory_item_id, '||
700 ' primary_quantity, '||
701 ' subinventory_code, '||
702 ' locator_id, '||
703 ' completion_transaction_id';
704
705
706 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
707 'Duplicate material transactions.',true,null,'Y',row_limit);
708
709 IF (dummy_num = row_limit) THEN
710 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR> Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file. <BR>');
711 END IF;
712
713 If ( dummy_num > 0) then
714
715 reportStr := 'The rows returned above signify data inconsistency in Work Order-less / Flow transactions. These are duplicate material transactions.';
716 JTF_DIAGNOSTIC_COREAPI.ErrorPrint(reportStr);
717 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>');
718
719 end if;
720 statusStr := 'SUCCESS';
721 isFatal := 'FALSE';
722 fixInfo := 'OK';
723 errStr :='No Error!';
724
725
726 <<l_test_end>>
727 report := JTF_DIAGNOSTIC_ADAPTUTIL.constructReport(statusStr,errStr,fixInfo,isFatal);
728 reportClob := JTF_DIAGNOSTIC_ADAPTUTIL.getReportClob;
729
730 END Dup_mat_txn_mti;
731
732 END;