[Home] [Help]
35: p_table_owner varchar2(30);
36: BEGIN
37: JTF_DIAGNOSTIC_ADAPTUTIL.setUpVars;
38: JTF_DIAGNOSTIC_ADAPTUTIL.addStringToReport('@html');
39: JTF_DIAGNOSTIC_COREAPI.insert_style_sheet;
40: row_limit := 1000;
41: -- accept input
42: l_org_id := JTF_DIAGNOSTIC_ADAPTUTIL.getInputValue('Organization Id',inputs);
43: l_job_id := JTF_DIAGNOSTIC_ADAPTUTIL.getInputValue('Job Id',inputs);
82: 'where ' || we_dyn_where_clause || ' wdj.wip_entity_id = we.wip_entity_id '||
83: 'and wdj.date_released is null '||
84: 'and WDJ.STATUS_TYPE IN (3, 4, 5, 6, 14, 15) ';
85:
86: dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
87: 'Jobs/Schedules with invalid Release Date.',true,null,'Y',row_limit);
88:
89: IF (dummy_num = row_limit) THEN
90: JTF_DIAGNOSTIC_COREAPI.Line_Out('
Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file.
');
86: dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
87: 'Jobs/Schedules with invalid Release Date.',true,null,'Y',row_limit);
88:
89: IF (dummy_num = row_limit) THEN
90: JTF_DIAGNOSTIC_COREAPI.Line_Out('
Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file.
');
91: END IF;
92:
93: IF (dummy_num > 0) THEN
94: reportStr := 'The rows returned above signify that there are released jobs with null date released.';
91: END IF;
92:
93: IF (dummy_num > 0) THEN
94: reportStr := 'The rows returned above signify that there are released jobs with null date released.';
95: JTF_DIAGNOSTIC_COREAPI.ErrorPrint(reportStr);
96: JTF_DIAGNOSTIC_COREAPI.ActionErrorLink('Please refer to Metalink note ', 402202.1, 'to get the root-cause patch and steps to correct the data.
');
97: END IF;
98:
99:
92:
93: IF (dummy_num > 0) THEN
94: reportStr := 'The rows returned above signify that there are released jobs with null date released.';
95: JTF_DIAGNOSTIC_COREAPI.ErrorPrint(reportStr);
96: JTF_DIAGNOSTIC_COREAPI.ActionErrorLink('Please refer to Metalink note ', 402202.1, 'to get the root-cause patch and steps to correct the data.
');
97: END IF;
98:
99:
100: -- 2 This script will identify all jobs where quantity completed on job is not in sync with Inventory
117: ' and mmt.organization_id = wdj.organization_id '||
118: ' and mmt.inventory_item_id = wdj.primary_item_id '||
119: ' and mmt.transaction_action_id in (31,32)) order by we.wip_entity_id ';
120:
121: dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
122: 'Jobs/Schedules with invalid Job Quantity Completed',true,null,'Y',row_limit);
123:
124: IF (dummy_num = row_limit) THEN
125: JTF_DIAGNOSTIC_COREAPI.Line_Out('
Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file.
');
121: dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
122: 'Jobs/Schedules with invalid Job Quantity Completed',true,null,'Y',row_limit);
123:
124: IF (dummy_num = row_limit) THEN
125: JTF_DIAGNOSTIC_COREAPI.Line_Out('
Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file.
');
126: END IF;
127:
128: IF (dummy_num > 0) THEN
129: reportStr := 'The rows returned above signify that there are jobs where quantity completed on job is not in sync with Inventory.';
126: END IF;
127:
128: IF (dummy_num > 0) THEN
129: reportStr := 'The rows returned above signify that there are jobs where quantity completed on job is not in sync with Inventory.';
130: JTF_DIAGNOSTIC_COREAPI.ErrorPrint(reportStr);
131: 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.
');
132: END IF;
133:
134:
127:
128: IF (dummy_num > 0) THEN
129: reportStr := 'The rows returned above signify that there are jobs where quantity completed on job is not in sync with Inventory.';
130: JTF_DIAGNOSTIC_COREAPI.ErrorPrint(reportStr);
131: 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.
');
132: END IF;
133:
134:
135: -- 3 This script will identify all jobs where Resource Start and End Dates falls outside of Operation Start and End Dates
155: ' and ( (wop.first_unit_start_date > wor.start_date) '||
156: ' or '||
157: ' (wop.last_unit_completion_date < wor.completion_date)) order by wop.organization_id, wop.wip_entity_id ';
158:
159: dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
160: 'Jobs/Schedules with invalid Job Resource Start and End Date. ',true,null,'Y',row_limit);
161:
162: IF (dummy_num = row_limit) THEN
163: JTF_DIAGNOSTIC_COREAPI.Line_Out('
Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file.
');
159: dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
160: 'Jobs/Schedules with invalid Job Resource Start and End Date. ',true,null,'Y',row_limit);
161:
162: IF (dummy_num = row_limit) THEN
163: JTF_DIAGNOSTIC_COREAPI.Line_Out('
Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file.
');
164: END IF;
165:
166: IF (dummy_num > 0) THEN
167: reportStr := 'The rows returned above signify that there are resources on Jobs/Schedules where Start and End Dates falls outside of Operation Start and End Dates.';
164: END IF;
165:
166: IF (dummy_num > 0) THEN
167: reportStr := 'The rows returned above signify that there are resources on Jobs/Schedules where Start and End Dates falls outside of Operation Start and End Dates.';
168: JTF_DIAGNOSTIC_COREAPI.ErrorPrint(reportStr);
169: JTF_DIAGNOSTIC_COREAPI.ActionErrorLink('Please refer to Metalink note ', 402202.1, 'to get the root-cause patch and steps to correct the data.
');
170: END IF;
171:
172: --4 This script will identify all jobs where the quantity issued of material requirements is not in sync with Inventory
165:
166: IF (dummy_num > 0) THEN
167: reportStr := 'The rows returned above signify that there are resources on Jobs/Schedules where Start and End Dates falls outside of Operation Start and End Dates.';
168: JTF_DIAGNOSTIC_COREAPI.ErrorPrint(reportStr);
169: JTF_DIAGNOSTIC_COREAPI.ActionErrorLink('Please refer to Metalink note ', 402202.1, 'to get the root-cause patch and steps to correct the data.
');
170: END IF;
171:
172: --4 This script will identify all jobs where the quantity issued of material requirements is not in sync with Inventory
173: sqltxt :=
189: ' HAVING Sum(mmt.primary_quantity) <> wro.quantity_issued*(-1) '||
190: 'GROUP BY wdj.wip_entity_id, substr(we.wip_entity_name,1,15), we.organization_id, wro.inventory_item_id, wro.operation_seq_num, '||
191: ' wro.quantity_per_assembly, wro.required_quantity, wro.quantity_issued ';
192:
193: dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
194: 'Jobs with Quantity_Issued in material requirements not in sync with Inventory.',true,null,'Y',row_limit);
195:
196: IF (dummy_num = row_limit) THEN
197: JTF_DIAGNOSTIC_COREAPI.Line_Out('
Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file.
');
193: dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
194: 'Jobs with Quantity_Issued in material requirements not in sync with Inventory.',true,null,'Y',row_limit);
195:
196: IF (dummy_num = row_limit) THEN
197: JTF_DIAGNOSTIC_COREAPI.Line_Out('
Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file.
');
198: END IF;
199:
200: IF (dummy_num > 0) THEN
201: reportStr := 'The rows returned above signify that there are jobs where issued quantity of material requirements is not in sync with Inventory.';
198: END IF;
199:
200: IF (dummy_num > 0) THEN
201: reportStr := 'The rows returned above signify that there are jobs where issued quantity of material requirements is not in sync with Inventory.';
202: JTF_DIAGNOSTIC_COREAPI.ErrorPrint(reportStr);
203: JTF_DIAGNOSTIC_COREAPI.ActionErrorLink('Please refer to Metalink note ', 402202.1, 'to get the root-cause patch and steps to correct the data.
');
204: END IF;
205:
206: --5 This script will identify unreleased jobs that have quantities on operation.
199:
200: IF (dummy_num > 0) THEN
201: reportStr := 'The rows returned above signify that there are jobs where issued quantity of material requirements is not in sync with Inventory.';
202: JTF_DIAGNOSTIC_COREAPI.ErrorPrint(reportStr);
203: JTF_DIAGNOSTIC_COREAPI.ActionErrorLink('Please refer to Metalink note ', 402202.1, 'to get the root-cause patch and steps to correct the data.
');
204: END IF;
205:
206: --5 This script will identify unreleased jobs that have quantities on operation.
207: sqltxt :=
236: ' OR wo.quantity_scrapped <> 0 '||
237: ' OR wo.quantity_rejected <> 0 '||
238: ' OR wo.quantity_completed <> 0) ';
239:
240: dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
241: 'Unreleased jobs that have quantities on operation.',true,null,'Y',row_limit);
242:
243: IF (dummy_num = row_limit) THEN
244: JTF_DIAGNOSTIC_COREAPI.Line_Out('
Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file.
');
240: dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
241: 'Unreleased jobs that have quantities on operation.',true,null,'Y',row_limit);
242:
243: IF (dummy_num = row_limit) THEN
244: JTF_DIAGNOSTIC_COREAPI.Line_Out('
Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file.
');
245: END IF;
246:
247: IF (dummy_num > 0) THEN
248: reportStr := 'The rows returned above signify that there are unreleased jobs that have quantities on operation.';
245: END IF;
246:
247: IF (dummy_num > 0) THEN
248: reportStr := 'The rows returned above signify that there are unreleased jobs that have quantities on operation.';
249: JTF_DIAGNOSTIC_COREAPI.ErrorPrint(reportStr);
250: JTF_DIAGNOSTIC_COREAPI.ActionErrorLink('Please refer to Metalink note ', 402202.1, 'to get the root-cause patch and steps to correct the data.
');
251: END IF;
252:
253: --6 This script will identify all released jobs that do not have any quantites on any operation.
246:
247: IF (dummy_num > 0) THEN
248: reportStr := 'The rows returned above signify that there are unreleased jobs that have quantities on operation.';
249: JTF_DIAGNOSTIC_COREAPI.ErrorPrint(reportStr);
250: JTF_DIAGNOSTIC_COREAPI.ActionErrorLink('Please refer to Metalink note ', 402202.1, 'to get the root-cause patch and steps to correct the data.
');
251: END IF;
252:
253: --6 This script will identify all released jobs that do not have any quantites on any operation.
254: sqltxt :=
292: 'AND wo1.organization_id = wo.organization_id '||
293: 'AND wo1.wip_entity_id = wo.wip_entity_id '||
294: 'AND wo1.OPERATION_SEQ_NUM <= wo.PREVIOUS_OPERATION_SEQ_NUM ))';
295:
296: dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
297: 'Released jobs that do not have any quantites on any operation.',true,null,'Y',row_limit);
298:
299: IF (dummy_num = row_limit) THEN
300: JTF_DIAGNOSTIC_COREAPI.Line_Out('
Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file.
');
296: dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
297: 'Released jobs that do not have any quantites on any operation.',true,null,'Y',row_limit);
298:
299: IF (dummy_num = row_limit) THEN
300: JTF_DIAGNOSTIC_COREAPI.Line_Out('
Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file.
');
301: END IF;
302:
303: IF (dummy_num > 0) THEN
304: reportStr := 'The rows returned above signify that there are released jobs that do not have any quantites on any operation.';
301: END IF;
302:
303: IF (dummy_num > 0) THEN
304: reportStr := 'The rows returned above signify that there are released jobs that do not have any quantites on any operation.';
305: JTF_DIAGNOSTIC_COREAPI.ErrorPrint(reportStr);
306: JTF_DIAGNOSTIC_COREAPI.ActionErrorLink('Please refer to Metalink note ', 402202.1, 'to get the root-cause patch and steps to correct the data.
');
307: END IF;
308:
309:
302:
303: IF (dummy_num > 0) THEN
304: reportStr := 'The rows returned above signify that there are released jobs that do not have any quantites on any operation.';
305: JTF_DIAGNOSTIC_COREAPI.ErrorPrint(reportStr);
306: JTF_DIAGNOSTIC_COREAPI.ActionErrorLink('Please refer to Metalink note ', 402202.1, 'to get the root-cause patch and steps to correct the data.
');
307: END IF;
308:
309:
310: --6.5 This script identifies all jobs that have operation quantities not in sync with move transaction quantities
460: ' )) ' ||
461: ' ) ' ||
462: ' ) ' ;
463:
464: dummy_num := JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
465: 'Jobs that have operation quantities not in sync with move transaction quantities.',true,null,'Y',row_limit);
466:
467: IF (dummy_num = row_limit) THEN
468: JTF_DIAGNOSTIC_COREAPI.Line_Out('
Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file.
');
464: dummy_num := JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
465: 'Jobs that have operation quantities not in sync with move transaction quantities.',true,null,'Y',row_limit);
466:
467: IF (dummy_num = row_limit) THEN
468: JTF_DIAGNOSTIC_COREAPI.Line_Out('
Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file.
');
469: END IF;
470:
471: if dummy_num > 0 then
472: reportStr := 'The rows returned above signify that Operation quantites are not in sync with Move Transactions. ';
469: END IF;
470:
471: if dummy_num > 0 then
472: reportStr := 'The rows returned above signify that Operation quantites are not in sync with Move Transactions. ';
473: JTF_DIAGNOSTIC_COREAPI.ErrorPrint(reportStr);
474: JTF_DIAGNOSTIC_COREAPI.ActionErrorLink('Please refer to Metalink note ', 402202.1, 'to get the root-cause patch and steps to correct the data.
');
475: end if;
476:
477: --7 This script identifies all records that are Orphan in the WIP tables.
470:
471: if dummy_num > 0 then
472: reportStr := 'The rows returned above signify that Operation quantites are not in sync with Move Transactions. ';
473: JTF_DIAGNOSTIC_COREAPI.ErrorPrint(reportStr);
474: JTF_DIAGNOSTIC_COREAPI.ActionErrorLink('Please refer to Metalink note ', 402202.1, 'to get the root-cause patch and steps to correct the data.
');
475: end if;
476:
477: --7 This script identifies all records that are Orphan in the WIP tables.
478: sqltxt :=
506: ' NOT EXISTS (SELECT 1 FROM wip_entities we '||
507: ' WHERE ' || we_dyn_where_clause || ' wo.wip_entity_id = we.wip_entity_id '||
508: ' AND wo.organization_id = we.organization_id) ';
509:
510: dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
511: 'Records Orphan in the WIP tables.',true,null,'Y',row_limit);
512:
513: IF (dummy_num = row_limit) THEN
514: JTF_DIAGNOSTIC_COREAPI.Line_Out('
Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file.
');
510: dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
511: 'Records Orphan in the WIP tables.',true,null,'Y',row_limit);
512:
513: IF (dummy_num = row_limit) THEN
514: JTF_DIAGNOSTIC_COREAPI.Line_Out('
Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file.
');
515: END IF;
516:
517: IF (dummy_num > 0) THEN
518: reportStr := 'The rows returned above signify that there are orphan records exist in WIP table(s). ';
515: END IF;
516:
517: IF (dummy_num > 0) THEN
518: reportStr := 'The rows returned above signify that there are orphan records exist in WIP table(s). ';
519: JTF_DIAGNOSTIC_COREAPI.ErrorPrint(reportStr);
520: JTF_DIAGNOSTIC_COREAPI.ActionErrorLink('Please refer to Metalink note ', 402202.1, 'to get the root-cause patch and steps to correct the data.
');
521: END IF;
522:
523:
516:
517: IF (dummy_num > 0) THEN
518: reportStr := 'The rows returned above signify that there are orphan records exist in WIP table(s). ';
519: JTF_DIAGNOSTIC_COREAPI.ErrorPrint(reportStr);
520: JTF_DIAGNOSTIC_COREAPI.ActionErrorLink('Please refer to Metalink note ', 402202.1, 'to get the root-cause patch and steps to correct the data.
');
521: END IF;
522:
523:
524: -- 8 This script identifies all the jobs that have multiple PO Move resources in an operation.
539: 'HAVING Count(*) > 1 '||
540: 'GROUP BY wdj.wip_entity_id, substr(we.wip_entity_name,1,15), '||
541: ' wdj.organization_id, wo.operation_seq_num ';
542:
543: dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
544: 'Jobs that have multiple PO Move resources in an operation.',true,null,'Y',row_limit);
545:
546: IF (dummy_num = row_limit) THEN
547: JTF_DIAGNOSTIC_COREAPI.Line_Out('
Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file.
');
543: dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
544: 'Jobs that have multiple PO Move resources in an operation.',true,null,'Y',row_limit);
545:
546: IF (dummy_num = row_limit) THEN
547: JTF_DIAGNOSTIC_COREAPI.Line_Out('
Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file.
');
548: END IF;
549:
550: IF (dummy_num > 0) THEN
551: reportStr := 'The rows returned above signify that there are multiple PO move resources in an operation.';
548: END IF;
549:
550: IF (dummy_num > 0) THEN
551: reportStr := 'The rows returned above signify that there are multiple PO move resources in an operation.';
552: JTF_DIAGNOSTIC_COREAPI.ErrorPrint(reportStr);
553: JTF_DIAGNOSTIC_COREAPI.ActionErrorLink('Please refer to Metalink note ', 402202.1, 'to get the root-cause patch and steps to correct the data.
');
554: END IF;
555:
556:
549:
550: IF (dummy_num > 0) THEN
551: reportStr := 'The rows returned above signify that there are multiple PO move resources in an operation.';
552: JTF_DIAGNOSTIC_COREAPI.ErrorPrint(reportStr);
553: JTF_DIAGNOSTIC_COREAPI.ActionErrorLink('Please refer to Metalink note ', 402202.1, 'to get the root-cause patch and steps to correct the data.
');
554: END IF;
555:
556:
557: --9 This script check if there are any trailing spaces in the text fields on Discrete Job for a particular Job.
630: if sqltext2 is not null then
631: sqltxt := sqltext2;
632: end if;
633:
634: dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
635: 'Trailing spaces in the text fields for a Discrete Job',true,null,'Y',row_limit);
636:
637: IF (dummy_num > 0) THEN
638: reportStr := 'The rows returned above signify that there are trailing spaces in the text fields for Discrete Job.';
635: 'Trailing spaces in the text fields for a Discrete Job',true,null,'Y',row_limit);
636:
637: IF (dummy_num > 0) THEN
638: reportStr := 'The rows returned above signify that there are trailing spaces in the text fields for Discrete Job.';
639: JTF_DIAGNOSTIC_COREAPI.ErrorPrint(reportStr);
640: JTF_DIAGNOSTIC_COREAPI.ActionErrorLink('Please refer to Metalink note ', 402202.1, 'to get the root-cause patch and steps to correct the data.
');
641: END IF;
642: END IF;
643:
636:
637: IF (dummy_num > 0) THEN
638: reportStr := 'The rows returned above signify that there are trailing spaces in the text fields for Discrete Job.';
639: JTF_DIAGNOSTIC_COREAPI.ErrorPrint(reportStr);
640: JTF_DIAGNOSTIC_COREAPI.ActionErrorLink('Please refer to Metalink note ', 402202.1, 'to get the root-cause patch and steps to correct the data.
');
641: END IF;
642: END IF;
643:
644: --COMPLETION SUBINVENTORY / LOCATOR
657: ' WHERE wdj.completion_subinventory = mi.secondary_inventory_name '||
658: ' AND wdj.organization_id = mi.organization_id '||
659: ' AND mi.secondary_inventory_name <> ''AX_INTRANS'' AND Nvl(mi.disable_date,Trunc(SYSDATE+1)) > Trunc(SYSDATE)) ';
660:
661: dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
662: 'Jobs/Schedules with invalid Completion Subinventory',true,null,'Y',row_limit);
663:
664: IF (dummy_num = row_limit) THEN
665: JTF_DIAGNOSTIC_COREAPI.Line_Out('
Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file.
');
661: dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
662: 'Jobs/Schedules with invalid Completion Subinventory',true,null,'Y',row_limit);
663:
664: IF (dummy_num = row_limit) THEN
665: JTF_DIAGNOSTIC_COREAPI.Line_Out('
Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file.
');
666: END IF;
667:
668: IF (dummy_num > 0) THEN
669: reportStr := 'The rows returned above signify that there are jobs having invalid completion subinventory.';
666: END IF;
667:
668: IF (dummy_num > 0) THEN
669: reportStr := 'The rows returned above signify that there are jobs having invalid completion subinventory.';
670: JTF_DIAGNOSTIC_COREAPI.ErrorPrint(reportStr);
671: reportStr := 'Please query up each job in the discrete job form, and update the completion subinventory/locator of the problematic job to a valid value.
';
672: JTF_DIAGNOSTIC_COREAPI.ActionErrorPrint(reportStr);
673: END IF;
674:
668: IF (dummy_num > 0) THEN
669: reportStr := 'The rows returned above signify that there are jobs having invalid completion subinventory.';
670: JTF_DIAGNOSTIC_COREAPI.ErrorPrint(reportStr);
671: reportStr := 'Please query up each job in the discrete job form, and update the completion subinventory/locator of the problematic job to a valid value.
';
672: JTF_DIAGNOSTIC_COREAPI.ActionErrorPrint(reportStr);
673: END IF;
674:
675:
676: --11. completion subinventory not valid for the assembly. assembly has "restrict subinventories" enabled and
690: ' WHERE wdj.completion_subinventory = mi.secondary_inventory '||
691: ' AND wdj.organization_id = mi.organization_id '||
692: ' AND wdj.primary_item_id = mi.inventory_item_id) ';
693:
694: dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
695: 'Jobs/Schedules with invalid Completion Subinventory - Not part of "Restricted Subinventories"',true,null,'Y',row_limit);
696:
697: IF (dummy_num = row_limit) THEN
698: JTF_DIAGNOSTIC_COREAPI.Line_Out('
Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file.
');
694: dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
695: 'Jobs/Schedules with invalid Completion Subinventory - Not part of "Restricted Subinventories"',true,null,'Y',row_limit);
696:
697: IF (dummy_num = row_limit) THEN
698: JTF_DIAGNOSTIC_COREAPI.Line_Out('
Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file.
');
699: END IF;
700:
701: IF (dummy_num > 0) THEN
702: reportStr := 'The rows returned above signify that there are jobs having completion subinventory that is not part of "Restricted Subinventories". ';
699: END IF;
700:
701: IF (dummy_num > 0) THEN
702: reportStr := 'The rows returned above signify that there are jobs having completion subinventory that is not part of "Restricted Subinventories". ';
703: JTF_DIAGNOSTIC_COREAPI.ErrorPrint(reportStr);
704: reportStr := 'Please query up each job in the discrete job form, and update the completion subinventory/locator of the problematic job to a valid value.
';
705: JTF_DIAGNOSTIC_COREAPI.ActionErrorPrint(reportStr);
706: END IF;
707:
701: IF (dummy_num > 0) THEN
702: reportStr := 'The rows returned above signify that there are jobs having completion subinventory that is not part of "Restricted Subinventories". ';
703: JTF_DIAGNOSTIC_COREAPI.ErrorPrint(reportStr);
704: reportStr := 'Please query up each job in the discrete job form, and update the completion subinventory/locator of the problematic job to a valid value.
';
705: JTF_DIAGNOSTIC_COREAPI.ActionErrorPrint(reportStr);
706: END IF;
707:
708: -- 12. completion subinventory has invalid material status
709: sqltxt :=
719: ' wdj.organization_id, wdj.primary_item_id, '||
720: ' wdj.completion_subinventory, '||
721: ' NULL, NULL, NULL, ''Z'') <> ''Y'' ';
722:
723: dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
724: 'Jobs/Schedules with invalid Completion Subinventory - Invalid Material Status',true,null,'Y',row_limit);
725:
726: IF (dummy_num = row_limit) THEN
727: JTF_DIAGNOSTIC_COREAPI.Line_Out('
Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file.
');
723: dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
724: 'Jobs/Schedules with invalid Completion Subinventory - Invalid Material Status',true,null,'Y',row_limit);
725:
726: IF (dummy_num = row_limit) THEN
727: JTF_DIAGNOSTIC_COREAPI.Line_Out('
Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file.
');
728: END IF;
729:
730: IF (dummy_num > 0) THEN
731: reportStr := 'The rows returned above signify that there are jobs having completion subinventory with invalid material status.';
728: END IF;
729:
730: IF (dummy_num > 0) THEN
731: reportStr := 'The rows returned above signify that there are jobs having completion subinventory with invalid material status.';
732: JTF_DIAGNOSTIC_COREAPI.ErrorPrint(reportStr);
733: reportStr := 'Please query up each job in the discrete job form, and update the completion subinventory/locator of the problematic job to a valid value.
';
734: JTF_DIAGNOSTIC_COREAPI.ActionErrorPrint(reportStr);
735: END IF;
736:
730: IF (dummy_num > 0) THEN
731: reportStr := 'The rows returned above signify that there are jobs having completion subinventory with invalid material status.';
732: JTF_DIAGNOSTIC_COREAPI.ErrorPrint(reportStr);
733: reportStr := 'Please query up each job in the discrete job form, and update the completion subinventory/locator of the problematic job to a valid value.
';
734: JTF_DIAGNOSTIC_COREAPI.ActionErrorPrint(reportStr);
735: END IF;
736:
737: -- 13. completion locator missing
738: sqltxt :=
747: ' wdj.completion_subinventory, '||
748: ' wdj.primary_item_id) <> 1 '||
749: 'AND completion_locator_id IS NULL ';
750:
751: dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
752: 'Jobs/Schedules with missing Completion Locator',true,null,'Y',row_limit);
753:
754: IF (dummy_num = row_limit) THEN
755: JTF_DIAGNOSTIC_COREAPI.Line_Out('
Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file.
');
751: dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
752: 'Jobs/Schedules with missing Completion Locator',true,null,'Y',row_limit);
753:
754: IF (dummy_num = row_limit) THEN
755: JTF_DIAGNOSTIC_COREAPI.Line_Out('
Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file.
');
756: END IF;
757:
758: IF (dummy_num > 0) THEN
759: reportStr := 'The rows returned above signify that there are jobs with null completion locator.';
756: END IF;
757:
758: IF (dummy_num > 0) THEN
759: reportStr := 'The rows returned above signify that there are jobs with null completion locator.';
760: JTF_DIAGNOSTIC_COREAPI.ErrorPrint(reportStr);
761: reportStr := 'Please query up each job in the discrete job form, and update the completion subinventory/locator of the problematic job to a valid value.
';
762: JTF_DIAGNOSTIC_COREAPI.ActionErrorPrint(reportStr);
763: END IF;
764:
758: IF (dummy_num > 0) THEN
759: reportStr := 'The rows returned above signify that there are jobs with null completion locator.';
760: JTF_DIAGNOSTIC_COREAPI.ErrorPrint(reportStr);
761: reportStr := 'Please query up each job in the discrete job form, and update the completion subinventory/locator of the problematic job to a valid value.
';
762: JTF_DIAGNOSTIC_COREAPI.ActionErrorPrint(reportStr);
763: END IF;
764:
765: -- 14. completion locator was supposed to be null but populated
766: sqltxt :=
775: ' wdj.completion_subinventory, '||
776: ' wdj.primary_item_id) = 1 '||
777: 'AND completion_locator_id IS NOT NULL ';
778:
779: dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
780: 'Jobs/Schedules with non locator controlled Completion Subinventory but Completion Locator populated.',true,null,'Y',row_limit);
781:
782: IF (dummy_num = row_limit) THEN
783: JTF_DIAGNOSTIC_COREAPI.Line_Out('
Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file.
');
779: dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
780: 'Jobs/Schedules with non locator controlled Completion Subinventory but Completion Locator populated.',true,null,'Y',row_limit);
781:
782: IF (dummy_num = row_limit) THEN
783: JTF_DIAGNOSTIC_COREAPI.Line_Out('
Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file.
');
784: END IF;
785:
786: IF (dummy_num > 0) THEN
787: reportStr := 'The rows returned above signify that there are jobs with non locator controlled Completion Subinventory but Completion Locator populated.';
784: END IF;
785:
786: IF (dummy_num > 0) THEN
787: reportStr := 'The rows returned above signify that there are jobs with non locator controlled Completion Subinventory but Completion Locator populated.';
788: JTF_DIAGNOSTIC_COREAPI.ErrorPrint(reportStr);
789: reportStr := 'Please query up each job in the discrete job form, and update the completion subinventory/locator of the problematic job to a valid value.
';
790: JTF_DIAGNOSTIC_COREAPI.ActionErrorPrint(reportStr);
791: END IF;
792:
786: IF (dummy_num > 0) THEN
787: reportStr := 'The rows returned above signify that there are jobs with non locator controlled Completion Subinventory but Completion Locator populated.';
788: JTF_DIAGNOSTIC_COREAPI.ErrorPrint(reportStr);
789: reportStr := 'Please query up each job in the discrete job form, and update the completion subinventory/locator of the problematic job to a valid value.
';
790: JTF_DIAGNOSTIC_COREAPI.ActionErrorPrint(reportStr);
791: END IF;
792:
793: --15. completion subinventory NULL but completion locator populated
794: sqltxt :=
800: 'AND wdj.status_type IN (1,3,4) '||
801: 'AND wdj.completion_subinventory IS NULL '||
802: 'AND wdj.completion_locator_id IS NOT NULL ';
803:
804: dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
805: 'Jobs/Schedules with a completion locator but no Completion Subinventory.',true,null,'Y',row_limit);
806:
807: IF (dummy_num = row_limit) THEN
808: JTF_DIAGNOSTIC_COREAPI.Line_Out('
Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file.
');
804: dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
805: 'Jobs/Schedules with a completion locator but no Completion Subinventory.',true,null,'Y',row_limit);
806:
807: IF (dummy_num = row_limit) THEN
808: JTF_DIAGNOSTIC_COREAPI.Line_Out('
Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file.
');
809: END IF;
810:
811: IF (dummy_num > 0) THEN
812: reportStr := 'The rows returned above signify that there are jobs with a completion locator but no Completion Subinventory.';
809: END IF;
810:
811: IF (dummy_num > 0) THEN
812: reportStr := 'The rows returned above signify that there are jobs with a completion locator but no Completion Subinventory.';
813: JTF_DIAGNOSTIC_COREAPI.ErrorPrint(reportStr);
814: reportStr := 'Please query up each job in the discrete job form, and update the completion subinventory/locator of the problematic job to a valid value.
';
815: JTF_DIAGNOSTIC_COREAPI.ActionErrorPrint(reportStr);
816: END IF;
817:
811: IF (dummy_num > 0) THEN
812: reportStr := 'The rows returned above signify that there are jobs with a completion locator but no Completion Subinventory.';
813: JTF_DIAGNOSTIC_COREAPI.ErrorPrint(reportStr);
814: reportStr := 'Please query up each job in the discrete job form, and update the completion subinventory/locator of the problematic job to a valid value.
';
815: JTF_DIAGNOSTIC_COREAPI.ActionErrorPrint(reportStr);
816: END IF;
817:
818: -- 16. completion locator not valid
819: sqltxt :=
832: ' AND wdj.organization_id = mil.organization_id '||
833: ' AND wdj.completion_subinventory = mil.subinventory_code '||
834: ' AND Nvl(mil.disable_date, trunc(sysdate+1)) > trunc(sysdate)) ';
835:
836: dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
837: 'Jobs/Schedules with invalid Completion Locator ',true,null,'Y',row_limit);
838:
839: IF (dummy_num = row_limit) THEN
840: JTF_DIAGNOSTIC_COREAPI.Line_Out('
Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file.
');
836: dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
837: 'Jobs/Schedules with invalid Completion Locator ',true,null,'Y',row_limit);
838:
839: IF (dummy_num = row_limit) THEN
840: JTF_DIAGNOSTIC_COREAPI.Line_Out('
Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file.
');
841: END IF;
842:
843: IF (dummy_num > 0) THEN
844: reportStr := 'The rows returned above signify that there are jobs having invalid completion locator.';
841: END IF;
842:
843: IF (dummy_num > 0) THEN
844: reportStr := 'The rows returned above signify that there are jobs having invalid completion locator.';
845: JTF_DIAGNOSTIC_COREAPI.ErrorPrint(reportStr);
846: reportStr := 'Please query up each job in the discrete job form, and update the completion subinventory/locator of the problematic job to a valid value.
';
847: JTF_DIAGNOSTIC_COREAPI.ActionErrorPrint(reportStr);
848: END IF;
849:
843: IF (dummy_num > 0) THEN
844: reportStr := 'The rows returned above signify that there are jobs having invalid completion locator.';
845: JTF_DIAGNOSTIC_COREAPI.ErrorPrint(reportStr);
846: reportStr := 'Please query up each job in the discrete job form, and update the completion subinventory/locator of the problematic job to a valid value.
';
847: JTF_DIAGNOSTIC_COREAPI.ActionErrorPrint(reportStr);
848: END IF;
849:
850: -- 17. completion locator not valid for the assembly. assembly has "restrict locators" enabled and
851: -- this locator is not part of it.
866: ' WHERE wdj.completion_locator_id = msl.secondary_locator '||
867: ' AND wdj.organization_id = msl.organization_id '||
868: ' AND wdj.primary_item_id = msl.inventory_item_id) ';
869:
870: dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
871: 'Jobs/Schedules with invalid Completion Locator - Not part of "Restricted Locators"',true,null,'Y',row_limit);
872:
873: IF (dummy_num = row_limit) THEN
874: JTF_DIAGNOSTIC_COREAPI.Line_Out('
Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file.
');
870: dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
871: 'Jobs/Schedules with invalid Completion Locator - Not part of "Restricted Locators"',true,null,'Y',row_limit);
872:
873: IF (dummy_num = row_limit) THEN
874: JTF_DIAGNOSTIC_COREAPI.Line_Out('
Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file.
');
875: END IF;
876:
877: IF (dummy_num > 0) THEN
878: reportStr := 'The rows returned above signify that there are jobs having completion locator that is not part of "Restricted Locators".';
875: END IF;
876:
877: IF (dummy_num > 0) THEN
878: reportStr := 'The rows returned above signify that there are jobs having completion locator that is not part of "Restricted Locators".';
879: JTF_DIAGNOSTIC_COREAPI.ErrorPrint(reportStr);
880: reportStr := 'Please query up each job in the discrete job form, and update the completion subinventory/locator of the problematic job to a valid value.
';
881: JTF_DIAGNOSTIC_COREAPI.ActionErrorPrint(reportStr);
882: END IF;
883:
877: IF (dummy_num > 0) THEN
878: reportStr := 'The rows returned above signify that there are jobs having completion locator that is not part of "Restricted Locators".';
879: JTF_DIAGNOSTIC_COREAPI.ErrorPrint(reportStr);
880: reportStr := 'Please query up each job in the discrete job form, and update the completion subinventory/locator of the problematic job to a valid value.
';
881: JTF_DIAGNOSTIC_COREAPI.ActionErrorPrint(reportStr);
882: END IF;
883:
884: -- 18.completion locator has invalid material status
885: sqltxt :=
897: ' wdj.organization_id, wdj.primary_item_id, '||
898: ' wdj.completion_subinventory, wdj.completion_locator_id, '||
899: ' NULL, NULL, ''Z'') <> ''Y'' ';
900:
901: dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
902: 'Jobs/Schedules with invalid Completion Locator - Invalid Material Status',true,null,'Y',row_limit);
903:
904: IF (dummy_num = row_limit) THEN
905: JTF_DIAGNOSTIC_COREAPI.Line_Out('
Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file.
');
901: dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
902: 'Jobs/Schedules with invalid Completion Locator - Invalid Material Status',true,null,'Y',row_limit);
903:
904: IF (dummy_num = row_limit) THEN
905: JTF_DIAGNOSTIC_COREAPI.Line_Out('
Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file.
');
906: END IF;
907:
908: IF (dummy_num > 0) THEN
909: reportStr := 'The rows returned above signify that there are jobs having completion locator with invalid material status.';
906: END IF;
907:
908: IF (dummy_num > 0) THEN
909: reportStr := 'The rows returned above signify that there are jobs having completion locator with invalid material status.';
910: JTF_DIAGNOSTIC_COREAPI.ErrorPrint(reportStr);
911: reportStr := 'Please query up each job in the discrete job form, and update the completion subinventory/locator of the problematic job to a valid value.
';
912: JTF_DIAGNOSTIC_COREAPI.ActionErrorPrint(reportStr);
913: END IF;
914:
908: IF (dummy_num > 0) THEN
909: reportStr := 'The rows returned above signify that there are jobs having completion locator with invalid material status.';
910: JTF_DIAGNOSTIC_COREAPI.ErrorPrint(reportStr);
911: reportStr := 'Please query up each job in the discrete job form, and update the completion subinventory/locator of the problematic job to a valid value.
';
912: JTF_DIAGNOSTIC_COREAPI.ActionErrorPrint(reportStr);
913: END IF;
914:
915: --SUPPLY SUBINVENTORY / LOCATOR
916: -----------------------------
929: ' AND wro.organization_id = mi.organization_id '||
930: ' AND mi.secondary_inventory_name <> ''AX_INTRANS'' '||
931: ' AND Nvl(mi.disable_date,Trunc(SYSDATE+1)) > Trunc(SYSDATE)) ';
932:
933: dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
934: 'Jobs/Schedules with invalid Supply Subinventory',true,null,'Y',row_limit);
935:
936: IF (dummy_num = row_limit) THEN
937: JTF_DIAGNOSTIC_COREAPI.Line_Out('
Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file.
');
933: dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
934: 'Jobs/Schedules with invalid Supply Subinventory',true,null,'Y',row_limit);
935:
936: IF (dummy_num = row_limit) THEN
937: JTF_DIAGNOSTIC_COREAPI.Line_Out('
Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file.
');
938: END IF;
939:
940: IF (dummy_num > 0) THEN
941: reportStr := 'The rows returned above signify that there are jobs having invalid supply subinventory.';
938: END IF;
939:
940: IF (dummy_num > 0) THEN
941: reportStr := 'The rows returned above signify that there are jobs having invalid supply subinventory.';
942: JTF_DIAGNOSTIC_COREAPI.ErrorPrint(reportStr);
943: reportStr := 'Please query up each job in the material requirements form, and update the supply subinventory/locator of the problematic component to a valid value.
';
944: JTF_DIAGNOSTIC_COREAPI.ActionErrorPrint(reportStr);
945: END IF;
946:
940: IF (dummy_num > 0) THEN
941: reportStr := 'The rows returned above signify that there are jobs having invalid supply subinventory.';
942: JTF_DIAGNOSTIC_COREAPI.ErrorPrint(reportStr);
943: reportStr := 'Please query up each job in the material requirements form, and update the supply subinventory/locator of the problematic component to a valid value.
';
944: JTF_DIAGNOSTIC_COREAPI.ActionErrorPrint(reportStr);
945: END IF;
946:
947: --20. supply subinventory not valid for the component. component has "restrict subinventories" enabled and
948: -- this subinventory is not part of it.
961: ' WHERE wro.supply_subinventory = mi.secondary_inventory '||
962: ' AND wro.organization_id = mi.organization_id '||
963: ' AND wro.inventory_item_id = mi.inventory_item_id) ';
964:
965: dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
966: 'Jobs/Schedules with invalid Supply Subinventory - Not part of '||
967: ' "Restricted Subinventories".',true,null,'Y',row_limit);
968:
969: IF (dummy_num = row_limit) THEN
966: 'Jobs/Schedules with invalid Supply Subinventory - Not part of '||
967: ' "Restricted Subinventories".',true,null,'Y',row_limit);
968:
969: IF (dummy_num = row_limit) THEN
970: JTF_DIAGNOSTIC_COREAPI.Line_Out('
Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file.
');
971: END IF;
972:
973: IF (dummy_num > 0) THEN
974: reportStr := 'The rows returned above signify that there are jobs having supply subinventory that is not part of "Restricted Subinventories".';
972:
973: IF (dummy_num > 0) THEN
974: reportStr := 'The rows returned above signify that there are jobs having supply subinventory that is not part of "Restricted Subinventories".';
975:
976: JTF_DIAGNOSTIC_COREAPI.ErrorPrint(reportStr);
977: reportStr := 'Please query up each job in the material requirements form, and update the supply subinventory/locator of the problematic component to a valid value.
';
978: JTF_DIAGNOSTIC_COREAPI.ActionErrorPrint(reportStr);
979: END IF;
980:
974: reportStr := 'The rows returned above signify that there are jobs having supply subinventory that is not part of "Restricted Subinventories".';
975:
976: JTF_DIAGNOSTIC_COREAPI.ErrorPrint(reportStr);
977: reportStr := 'Please query up each job in the material requirements form, and update the supply subinventory/locator of the problematic component to a valid value.
';
978: JTF_DIAGNOSTIC_COREAPI.ActionErrorPrint(reportStr);
979: END IF;
980:
981: --21. supply subinventory has invalid material status
982: sqltxt :=
992: ' wro.organization_id, wro.inventory_item_id, '||
993: ' wro.supply_subinventory, '||
994: ' NULL, NULL, NULL, ''Z'') <> ''Y'' ';
995:
996: dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
997: 'Jobs/Schedules with invalid Supply Subinventory - Invalid Material Status',true,null,'Y',row_limit);
998:
999: IF (dummy_num = row_limit) THEN
1000: JTF_DIAGNOSTIC_COREAPI.Line_Out('
Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file.
');
996: dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
997: 'Jobs/Schedules with invalid Supply Subinventory - Invalid Material Status',true,null,'Y',row_limit);
998:
999: IF (dummy_num = row_limit) THEN
1000: JTF_DIAGNOSTIC_COREAPI.Line_Out('
Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file.
');
1001: END IF;
1002:
1003: IF (dummy_num > 0) THEN
1004: reportStr := 'The rows returned above signify that there are jobs having supply subinventory with invalid material status.';
1001: END IF;
1002:
1003: IF (dummy_num > 0) THEN
1004: reportStr := 'The rows returned above signify that there are jobs having supply subinventory with invalid material status.';
1005: JTF_DIAGNOSTIC_COREAPI.ErrorPrint(reportStr);
1006: reportStr := 'Please query up each job in the material requirements form, and update the supply subinventory/locator of the problematic component to a valid value.
';
1007: JTF_DIAGNOSTIC_COREAPI.ActionErrorPrint(reportStr);
1008: END IF;
1009:
1003: IF (dummy_num > 0) THEN
1004: reportStr := 'The rows returned above signify that there are jobs having supply subinventory with invalid material status.';
1005: JTF_DIAGNOSTIC_COREAPI.ErrorPrint(reportStr);
1006: reportStr := 'Please query up each job in the material requirements form, and update the supply subinventory/locator of the problematic component to a valid value.
';
1007: JTF_DIAGNOSTIC_COREAPI.ActionErrorPrint(reportStr);
1008: END IF;
1009:
1010: --22. supply locator locator missing
1011: sqltxt :=
1020: ' wro.supply_subinventory, '||
1021: ' wro.inventory_item_id) <> 1 '||
1022: 'AND wro.supply_locator_id IS NULL ';
1023:
1024: dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
1025: 'Jobs/Schedules with Supply Locator Missing',true,null,'Y',row_limit);
1026:
1027: IF (dummy_num = row_limit) THEN
1028: JTF_DIAGNOSTIC_COREAPI.Line_Out('
Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file.
');
1024: dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
1025: 'Jobs/Schedules with Supply Locator Missing',true,null,'Y',row_limit);
1026:
1027: IF (dummy_num = row_limit) THEN
1028: JTF_DIAGNOSTIC_COREAPI.Line_Out('
Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file.
');
1029: END IF;
1030:
1031: IF (dummy_num > 0) THEN
1032: reportStr := 'The rows returned above signify that there are jobs where supply locator is missing.';
1029: END IF;
1030:
1031: IF (dummy_num > 0) THEN
1032: reportStr := 'The rows returned above signify that there are jobs where supply locator is missing.';
1033: JTF_DIAGNOSTIC_COREAPI.ErrorPrint(reportStr);
1034: reportStr := 'Please query up each job in the material requirements form, and update the supply subinventory/locator of the problematic component to a valid value.
';
1035: JTF_DIAGNOSTIC_COREAPI.ActionErrorPrint(reportStr);
1036: END IF;
1037:
1031: IF (dummy_num > 0) THEN
1032: reportStr := 'The rows returned above signify that there are jobs where supply locator is missing.';
1033: JTF_DIAGNOSTIC_COREAPI.ErrorPrint(reportStr);
1034: reportStr := 'Please query up each job in the material requirements form, and update the supply subinventory/locator of the problematic component to a valid value.
';
1035: JTF_DIAGNOSTIC_COREAPI.ActionErrorPrint(reportStr);
1036: END IF;
1037:
1038: --23. supply locator was supposed to be null but populated
1039: sqltxt :=
1048: ' wro.supply_subinventory, '||
1049: ' wro.inventory_item_id) = 1 '||
1050: 'AND wro.supply_locator_id IS NOT NULL ';
1051:
1052: dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
1053: 'Jobs/Schedules with non locator controlled Supply Subinventory but Supply Locator populated.',true,null,'Y',row_limit);
1054:
1055: IF (dummy_num = row_limit) THEN
1056: JTF_DIAGNOSTIC_COREAPI.Line_Out('
Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file.
');
1052: dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
1053: 'Jobs/Schedules with non locator controlled Supply Subinventory but Supply Locator populated.',true,null,'Y',row_limit);
1054:
1055: IF (dummy_num = row_limit) THEN
1056: JTF_DIAGNOSTIC_COREAPI.Line_Out('
Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file.
');
1057: END IF;
1058:
1059: IF (dummy_num > 0) THEN
1060: reportStr := 'The rows returned above signify that there are jobs with non locator controlled Supply Subinventory but Supply Locator populated.';
1057: END IF;
1058:
1059: IF (dummy_num > 0) THEN
1060: reportStr := 'The rows returned above signify that there are jobs with non locator controlled Supply Subinventory but Supply Locator populated.';
1061: JTF_DIAGNOSTIC_COREAPI.ErrorPrint(reportStr);
1062: reportStr := 'Please query up each job in the material requirements form, and update the supply subinventory/locator of the problematic component to a valid value.
';
1063: JTF_DIAGNOSTIC_COREAPI.ActionErrorPrint(reportStr);
1064: END IF;
1065:
1059: IF (dummy_num > 0) THEN
1060: reportStr := 'The rows returned above signify that there are jobs with non locator controlled Supply Subinventory but Supply Locator populated.';
1061: JTF_DIAGNOSTIC_COREAPI.ErrorPrint(reportStr);
1062: reportStr := 'Please query up each job in the material requirements form, and update the supply subinventory/locator of the problematic component to a valid value.
';
1063: JTF_DIAGNOSTIC_COREAPI.ActionErrorPrint(reportStr);
1064: END IF;
1065:
1066: --24. supply subinventory NULL but supply locator populated
1067: sqltxt :=
1073: 'AND wdj.status_type IN (1,3,4) '||
1074: 'AND wro.supply_subinventory IS NULL '||
1075: 'AND wro.supply_locator_id IS NOT NULL ';
1076:
1077: dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
1078: 'Jobs/Schedules with a Supply Locator but no Supply Subinventory',true,null,'Y',row_limit);
1079:
1080: IF (dummy_num = row_limit) THEN
1081: JTF_DIAGNOSTIC_COREAPI.Line_Out('
Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file.
');
1077: dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
1078: 'Jobs/Schedules with a Supply Locator but no Supply Subinventory',true,null,'Y',row_limit);
1079:
1080: IF (dummy_num = row_limit) THEN
1081: JTF_DIAGNOSTIC_COREAPI.Line_Out('
Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file.
');
1082: END IF;
1083:
1084: IF (dummy_num > 0) THEN
1085: reportStr := 'The rows returned above signify that there are jobs where supply locator is populated but supply subinventory is null.';
1082: END IF;
1083:
1084: IF (dummy_num > 0) THEN
1085: reportStr := 'The rows returned above signify that there are jobs where supply locator is populated but supply subinventory is null.';
1086: JTF_DIAGNOSTIC_COREAPI.ErrorPrint(reportStr);
1087: reportStr := 'Please query up each job in the material requirements form, and update the supply subinventory/locator of the problematic component to a valid value.
';
1088: JTF_DIAGNOSTIC_COREAPI.ActionErrorPrint(reportStr);
1089: END IF;
1090:
1084: IF (dummy_num > 0) THEN
1085: reportStr := 'The rows returned above signify that there are jobs where supply locator is populated but supply subinventory is null.';
1086: JTF_DIAGNOSTIC_COREAPI.ErrorPrint(reportStr);
1087: reportStr := 'Please query up each job in the material requirements form, and update the supply subinventory/locator of the problematic component to a valid value.
';
1088: JTF_DIAGNOSTIC_COREAPI.ActionErrorPrint(reportStr);
1089: END IF;
1090:
1091: --25. supply locator not valid
1092: sqltxt :=
1105: ' AND wro.organization_id = mil.organization_id '||
1106: ' AND wro.supply_subinventory = mil.subinventory_code '||
1107: ' AND Nvl(mil.disable_date, trunc(sysdate+1)) > trunc(sysdate)) ';
1108:
1109: dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
1110: 'Jobs/Schedules with invalid Supply Locator ',true,null,'Y',row_limit);
1111:
1112: IF (dummy_num = row_limit) THEN
1113: JTF_DIAGNOSTIC_COREAPI.Line_Out('
Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file.
');
1109: dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
1110: 'Jobs/Schedules with invalid Supply Locator ',true,null,'Y',row_limit);
1111:
1112: IF (dummy_num = row_limit) THEN
1113: JTF_DIAGNOSTIC_COREAPI.Line_Out('
Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file.
');
1114: END IF;
1115:
1116: IF (dummy_num > 0) THEN
1117: reportStr := 'The rows returned above signify that there are jobs where supply locator is not valid.';
1114: END IF;
1115:
1116: IF (dummy_num > 0) THEN
1117: reportStr := 'The rows returned above signify that there are jobs where supply locator is not valid.';
1118: JTF_DIAGNOSTIC_COREAPI.ErrorPrint(reportStr);
1119: reportStr := 'Please query up each job in the material requirements form, and update the supply subinventory/locator of the problematic component to a valid value.
';
1120: JTF_DIAGNOSTIC_COREAPI.ActionErrorPrint(reportStr);
1121: END IF;
1122:
1116: IF (dummy_num > 0) THEN
1117: reportStr := 'The rows returned above signify that there are jobs where supply locator is not valid.';
1118: JTF_DIAGNOSTIC_COREAPI.ErrorPrint(reportStr);
1119: reportStr := 'Please query up each job in the material requirements form, and update the supply subinventory/locator of the problematic component to a valid value.
';
1120: JTF_DIAGNOSTIC_COREAPI.ActionErrorPrint(reportStr);
1121: END IF;
1122:
1123: --26. Supply locator not valid for the component. component has "restrict locators" enabled and
1124: -- this locator is not part of it.
1139: ' WHERE wro.supply_locator_id = msl.secondary_locator '||
1140: ' AND wro.organization_id = msl.organization_id '||
1141: ' AND wro.inventory_item_id = msl.inventory_item_id) ';
1142:
1143: dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
1144: 'Jobs/Schedules with invalid Supply Locator - Not part of "Restricted Locators"',true,null,'Y',row_limit);
1145:
1146: IF (dummy_num = row_limit) THEN
1147: JTF_DIAGNOSTIC_COREAPI.Line_Out('
Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file.
');
1143: dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
1144: 'Jobs/Schedules with invalid Supply Locator - Not part of "Restricted Locators"',true,null,'Y',row_limit);
1145:
1146: IF (dummy_num = row_limit) THEN
1147: JTF_DIAGNOSTIC_COREAPI.Line_Out('
Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file.
');
1148: END IF;
1149:
1150: IF (dummy_num > 0) THEN
1151: reportStr := 'The rows returned above signify that there are jobs where supply locator is populated but it is not part of "Restricted Locators".';
1148: END IF;
1149:
1150: IF (dummy_num > 0) THEN
1151: reportStr := 'The rows returned above signify that there are jobs where supply locator is populated but it is not part of "Restricted Locators".';
1152: JTF_DIAGNOSTIC_COREAPI.ErrorPrint(reportStr);
1153: reportStr := 'Please query up each job in the material requirements form, and update the supply subinventory/locator of the problematic component to a valid value.
';
1154: JTF_DIAGNOSTIC_COREAPI.ActionErrorPrint(reportStr);
1155: END IF;
1156:
1150: IF (dummy_num > 0) THEN
1151: reportStr := 'The rows returned above signify that there are jobs where supply locator is populated but it is not part of "Restricted Locators".';
1152: JTF_DIAGNOSTIC_COREAPI.ErrorPrint(reportStr);
1153: reportStr := 'Please query up each job in the material requirements form, and update the supply subinventory/locator of the problematic component to a valid value.
';
1154: JTF_DIAGNOSTIC_COREAPI.ActionErrorPrint(reportStr);
1155: END IF;
1156:
1157: --27. supply locator has invalid material status
1158: sqltxt :=
1170: ' wro.organization_id, wro.inventory_item_id, '||
1171: ' wro.supply_subinventory, wro.supply_locator_id, '||
1172: ' NULL, NULL, ''Z'') <> ''Y'' ';
1173:
1174: dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
1175: 'Jobs/Schedules invalid Supply Locator - Invalid Material Status',true,null,'Y',row_limit);
1176:
1177: IF (dummy_num = row_limit) THEN
1178: JTF_DIAGNOSTIC_COREAPI.Line_Out('
Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file.
');
1174: dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
1175: 'Jobs/Schedules invalid Supply Locator - Invalid Material Status',true,null,'Y',row_limit);
1176:
1177: IF (dummy_num = row_limit) THEN
1178: JTF_DIAGNOSTIC_COREAPI.Line_Out('
Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file.
');
1179: END IF;
1180:
1181: IF (dummy_num > 0) THEN
1182: reportStr := 'The rows returned above signify that there are jobs having supply locator with invalid material status.';
1179: END IF;
1180:
1181: IF (dummy_num > 0) THEN
1182: reportStr := 'The rows returned above signify that there are jobs having supply locator with invalid material status.';
1183: JTF_DIAGNOSTIC_COREAPI.ErrorPrint(reportStr);
1184: reportStr := 'Please query up each job in the material requirements form, and update the supply subinventory/locator of the problematic component to a valid value.
';
1185: JTF_DIAGNOSTIC_COREAPI.ActionErrorPrint(reportStr);
1186: END IF;
1187:
1181: IF (dummy_num > 0) THEN
1182: reportStr := 'The rows returned above signify that there are jobs having supply locator with invalid material status.';
1183: JTF_DIAGNOSTIC_COREAPI.ErrorPrint(reportStr);
1184: reportStr := 'Please query up each job in the material requirements form, and update the supply subinventory/locator of the problematic component to a valid value.
';
1185: JTF_DIAGNOSTIC_COREAPI.ActionErrorPrint(reportStr);
1186: END IF;
1187:
1188: statusStr := 'SUCCESS';
1189: isFatal := 'FALSE';
1217: l_check_failed_close_jobs varchar2(2000);
1218: BEGIN
1219: JTF_DIAGNOSTIC_ADAPTUTIL.setUpVars;
1220: JTF_DIAGNOSTIC_ADAPTUTIL.addStringToReport('@html');
1221: JTF_DIAGNOSTIC_COREAPI.insert_style_sheet;
1222: row_limit := 1000;
1223: -- accept input
1224: l_org_id := JTF_DIAGNOSTIC_ADAPTUTIL.getInputValue('Organization Id',inputs);
1225: l_job_id := JTF_DIAGNOSTIC_ADAPTUTIL.getInputValue('Job Id',inputs);
1281: ' where ' || we_dyn_where_clause || ' we.lpn_id = lpn.lpn_id '||
1282: ' and lpn.lpn_context = 2 '||
1283: ' and wip_entity_id in ' || l_check_failed_close_jobs ;
1284:
1285: dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
1286: 'Jobs failed in job close process due to pending transactions',true,null,'Y',row_limit);
1287:
1288: IF (dummy_num = row_limit) THEN
1289: JTF_DIAGNOSTIC_COREAPI.Line_Out('
Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file.
');
1285: dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
1286: 'Jobs failed in job close process due to pending transactions',true,null,'Y',row_limit);
1287:
1288: IF (dummy_num = row_limit) THEN
1289: JTF_DIAGNOSTIC_COREAPI.Line_Out('
Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file.
');
1290: END IF;
1291:
1292: IF (dummy_num > 0) THEN
1293: reportStr := 'The rows returned above signify that jobs are failed to close due to pending transactions.';
1290: END IF;
1291:
1292: IF (dummy_num > 0) THEN
1293: reportStr := 'The rows returned above signify that jobs are failed to close due to pending transactions.';
1294: JTF_DIAGNOSTIC_COREAPI.ErrorPrint(reportStr);
1295: reportStr := 'Check the output and process the pending transactions against the job so that job can be closed.
';
1296: JTF_DIAGNOSTIC_COREAPI.ActionErrorPrint(reportStr);
1297: END IF;
1298:
1292: IF (dummy_num > 0) THEN
1293: reportStr := 'The rows returned above signify that jobs are failed to close due to pending transactions.';
1294: JTF_DIAGNOSTIC_COREAPI.ErrorPrint(reportStr);
1295: reportStr := 'Check the output and process the pending transactions against the job so that job can be closed.
';
1296: JTF_DIAGNOSTIC_COREAPI.ActionErrorPrint(reportStr);
1297: END IF;
1298:
1299: --2 Jobs that are not closed but entity_type updated
1300: sqltxt :=
1331: ' and wdj.status_type <> 12 '||
1332: ' and we.entity_type in (3,7,8) -- closed DJ, closed EAM, closed LBJ '||
1333: ' order by 1,2 ' ;
1334:
1335: dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
1336: 'Jobs that are not closed but its entity_type updated to closed',true,null,'Y',row_limit);
1337:
1338: IF (dummy_num = row_limit) THEN
1339: JTF_DIAGNOSTIC_COREAPI.Line_Out('
Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file.
');
1335: dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
1336: 'Jobs that are not closed but its entity_type updated to closed',true,null,'Y',row_limit);
1337:
1338: IF (dummy_num = row_limit) THEN
1339: JTF_DIAGNOSTIC_COREAPI.Line_Out('
Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file.
');
1340: END IF;
1341:
1342: IF (dummy_num > 0) THEN
1343: reportStr := 'The rows returned above signify that there are jobs that are not closed but its entity_type updated to closed.';
1340: END IF;
1341:
1342: IF (dummy_num > 0) THEN
1343: reportStr := 'The rows returned above signify that there are jobs that are not closed but its entity_type updated to closed.';
1344: JTF_DIAGNOSTIC_COREAPI.ErrorPrint(reportStr);
1345: 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 .
');
1346: END IF;
1347:
1348: --3 Jobs that are closed but entity_type not updated
1341:
1342: IF (dummy_num > 0) THEN
1343: reportStr := 'The rows returned above signify that there are jobs that are not closed but its entity_type updated to closed.';
1344: JTF_DIAGNOSTIC_COREAPI.ErrorPrint(reportStr);
1345: 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 .
');
1346: END IF;
1347:
1348: --3 Jobs that are closed but entity_type not updated
1349: sqltxt :=
1380: ' and wdj.status_type = 12 '||
1381: ' and we.entity_type not in (3,7,8) '||
1382: ' order by 1,2';
1383:
1384: dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
1385: 'Jobs that are closed but its entity_type not updated to closed',true,null,'Y',row_limit);
1386:
1387: IF (dummy_num = row_limit) THEN
1388: JTF_DIAGNOSTIC_COREAPI.Line_Out('
Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file.
');
1384: dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
1385: 'Jobs that are closed but its entity_type not updated to closed',true,null,'Y',row_limit);
1386:
1387: IF (dummy_num = row_limit) THEN
1388: JTF_DIAGNOSTIC_COREAPI.Line_Out('
Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file.
');
1389: END IF;
1390:
1391: IF (dummy_num > 0) THEN
1392: reportStr := 'The rows returned above signify that there are jobs that are closed but its entity_type not updated to closed.';
1389: END IF;
1390:
1391: IF (dummy_num > 0) THEN
1392: reportStr := 'The rows returned above signify that there are jobs that are closed but its entity_type not updated to closed.';
1393: JTF_DIAGNOSTIC_COREAPI.ErrorPrint(reportStr);
1394: 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.
');
1395: END IF;
1396:
1397: statusStr := 'SUCCESS';
1390:
1391: IF (dummy_num > 0) THEN
1392: reportStr := 'The rows returned above signify that there are jobs that are closed but its entity_type not updated to closed.';
1393: JTF_DIAGNOSTIC_COREAPI.ErrorPrint(reportStr);
1394: 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.
');
1395: END IF;
1396:
1397: statusStr := 'SUCCESS';
1398: isFatal := 'FALSE';