[Home] [Help]
36:
37: BEGIN
38: JTF_DIAGNOSTIC_ADAPTUTIL.setUpVars;
39: JTF_DIAGNOSTIC_ADAPTUTIL.addStringToReport('@html');
40: JTF_DIAGNOSTIC_COREAPI.insert_style_sheet;
41:
42: /*Initializing local vars */
43: row_limit :=1000; /* Set Row Limit to 1000 (i.e.) Max Number of records to be fetched by each sql*/
44: l_org_exists :=0; /* Initialize to zero */
57: where organization_id=l_org_id;
58: Exception
59: When others Then
60: l_org_exists :=0;
61: JTF_DIAGNOSTIC_COREAPI.errorprint('Invalid Organization Id');
62: JTF_DIAGNOSTIC_COREAPI.ActionErrorPrint(' Please either provide a valid value for the Organization Id or leave it blank. ');
63: statusStr := 'FAILURE';
64: isFatal := 'TRUE';
65: fixInfo := ' Please review the error message below and take corrective action. ';
58: Exception
59: When others Then
60: l_org_exists :=0;
61: JTF_DIAGNOSTIC_COREAPI.errorprint('Invalid Organization Id');
62: JTF_DIAGNOSTIC_COREAPI.ActionErrorPrint(' Please either provide a valid value for the Organization Id or leave it blank. ');
63: statusStr := 'FAILURE';
64: isFatal := 'TRUE';
65: fixInfo := ' Please review the error message below and take corrective action. ';
66: errStr := ' Invalid value for input field Organization Id ';
75: End If; /* End of l_org_id is not null */
76:
77: /* Input ECO/MCO name is mandatory.*/
78: If l_eco_name is NULL then
79: JTF_DIAGNOSTIC_COREAPI.Errorprint('Input ECO Name is mandatory.');
80: JTF_DIAGNOSTIC_COREAPI.ActionErrorPrint('Please provide a valid value for the ECO Name.');
81: statusStr := 'FAILURE';
82: isFatal := 'TRUE';
83: fixInfo := ' Please review the error message below and take corrective action. ';
76:
77: /* Input ECO/MCO name is mandatory.*/
78: If l_eco_name is NULL then
79: JTF_DIAGNOSTIC_COREAPI.Errorprint('Input ECO Name is mandatory.');
80: JTF_DIAGNOSTIC_COREAPI.ActionErrorPrint('Please provide a valid value for the ECO Name.');
81: statusStr := 'FAILURE';
82: isFatal := 'TRUE';
83: fixInfo := ' Please review the error message below and take corrective action. ';
84: errStr :=' Invalid value for input field ECO Name. It is a mandatory input. ';
122: End;
123:
124:
125: If (l_eco_exists = 0) and (l_mco_exists = 0) Then /* no eco or mco exists */
126: JTF_DIAGNOSTIC_COREAPI.errorprint('Invalid combination of ECO Name and Organization.');
127: JTF_DIAGNOSTIC_COREAPI.ActionErrorPrint(' Please provide valid values for the ECO Name and Organization Id. ');
128: statusStr := 'FAILURE';
129: isFatal := 'TRUE';
130: fixInfo := ' Please review the error message below and take corrective action. ';
123:
124:
125: If (l_eco_exists = 0) and (l_mco_exists = 0) Then /* no eco or mco exists */
126: JTF_DIAGNOSTIC_COREAPI.errorprint('Invalid combination of ECO Name and Organization.');
127: JTF_DIAGNOSTIC_COREAPI.ActionErrorPrint(' Please provide valid values for the ECO Name and Organization Id. ');
128: statusStr := 'FAILURE';
129: isFatal := 'TRUE';
130: fixInfo := ' Please review the error message below and take corrective action. ';
131: errStr := ' Invalid values for input fields ECO Name and/or Organization Id. ';
137: Elsif l_eco_exists =1 Then /* run the scripts if the ECO is exists */
138:
139: /* Get the application installation info. References to Data Dictionary Objects without schema name
140: included in WHERE predicate are not allowed (GSCC Check: file.sql.47). Schema name has to be passed
141: as an input parameter to JTF_DIAGNOSTIC_COREAPI.Column_Exists API. */
142:
143: l_ret_status := fnd_installation.get_app_info ('ENG'
144: , l_status
145: , l_industry
144: , l_status
145: , l_industry
146: , l_oracle_schema
147: );
148: /*JTF_DIAGNOSTIC_COREAPI.Line_Out(' l_oracle_schema: '||l_oracle_schema);*/
149:
150: /* SQL to fetch ECO Header Details */
151: sqltxt := 'SELECT ' ||
152: ' EEC.CHANGE_NOTICE "CHANGE NOTICE" '||
269: end if;
270: sqltxt :=sqltxt||' and rownum < '||row_limit;
271: sqltxt :=sqltxt||' order by mp1.organization_code ';
272:
273: num_rows:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, 'Eco Headers ');
274: If (num_rows = row_limit -1 ) Then
275: JTF_DIAGNOSTIC_COREAPI.Line_Out('
Output of the above table is limited to the first '||(row_limit-1)||' rows to prevent an excessively big output file.
');
276: End If;
277: statusStr := 'SUCCESS';
271: sqltxt :=sqltxt||' order by mp1.organization_code ';
272:
273: num_rows:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, 'Eco Headers ');
274: If (num_rows = row_limit -1 ) Then
275: JTF_DIAGNOSTIC_COREAPI.Line_Out('
Output of the above table is limited to the first '||(row_limit-1)||' rows to prevent an excessively big output file.
');
276: End If;
277: statusStr := 'SUCCESS';
278: isFatal := 'FALSE';
279:
402: end if;
403: sqltxt :=sqltxt||' and rownum < '||row_limit;
404: sqltxt :=sqltxt||' order by mp1.organization_code, mif1.padded_item_number,eri.revised_item_sequence_id';
405:
406: num_rows:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, 'Eco Revised Items ');
407: If (num_rows = row_limit -1 ) Then
408: JTF_DIAGNOSTIC_COREAPI.Line_Out('
Output of the above table is limited to the first '||(row_limit-1)||' rows to prevent an excessively big output file.
');
409: End If;
410: statusStr:= 'SUCCESS';
404: sqltxt :=sqltxt||' order by mp1.organization_code, mif1.padded_item_number,eri.revised_item_sequence_id';
405:
406: num_rows:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, 'Eco Revised Items ');
407: If (num_rows = row_limit -1 ) Then
408: JTF_DIAGNOSTIC_COREAPI.Line_Out('
Output of the above table is limited to the first '||(row_limit-1)||' rows to prevent an excessively big output file.
');
409: End If;
410: statusStr:= 'SUCCESS';
411: isFatal := 'FALSE';
412:
417: , l_status
418: , l_industry
419: , l_oracle_schema
420: );
421: /*JTF_DIAGNOSTIC_COREAPI.Line_Out(' l_oracle_schema: '||l_oracle_schema);*/
422:
423: /* SQL to fetch revised components from bom_components_b table*/
424: sqltxt := ' SELECT eri.change_notice "CHANGE NOTICE" '||
425: ' ,MP1.ORGANIZATION_CODE "ORGANIZATION CODE" '||
558: sqltxt :=sqltxt||' and rownum < '||row_limit;
559: sqltxt :=sqltxt||' order by mp1.organization_code,mif1.padded_item_number,eri.revised_item_sequence_id, '||
560: ' BCB.operation_seq_num, BCB.item_num';
561:
562: num_rows:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, ' Eco Revised Components ');
563: If (num_rows = row_limit -1 ) Then
564: JTF_DIAGNOSTIC_COREAPI.Line_Out('
Output of the above table is limited to the first '||(row_limit-1)||' rows to prevent an excessively big output file.
');
565: End If;
566: statusStr:= 'SUCCESS';
560: ' BCB.operation_seq_num, BCB.item_num';
561:
562: num_rows:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, ' Eco Revised Components ');
563: If (num_rows = row_limit -1 ) Then
564: JTF_DIAGNOSTIC_COREAPI.Line_Out('
Output of the above table is limited to the first '||(row_limit-1)||' rows to prevent an excessively big output file.
');
565: End If;
566: statusStr:= 'SUCCESS';
567: isFatal := 'FALSE';
568:
614: sqltxt :=sqltxt||' and rownum < '||row_limit;
615: sqltxt :=sqltxt||' order by mp1.organization_code,mif1.padded_item_number,eri.revised_item_sequence_id, '||
616: ' BCB.operation_seq_num, BCB.item_num';
617:
618: num_rows:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, ' Eco Revised Components (Contd 1..) ');
619: If (num_rows = row_limit -1 ) Then
620: JTF_DIAGNOSTIC_COREAPI.Line_Out('
Output of the above table is limited to the first '||(row_limit-1)||' rows to prevent an excessively big output file.
');
621: End If;
622: statusStr:= 'SUCCESS';
616: ' BCB.operation_seq_num, BCB.item_num';
617:
618: num_rows:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, ' Eco Revised Components (Contd 1..) ');
619: If (num_rows = row_limit -1 ) Then
620: JTF_DIAGNOSTIC_COREAPI.Line_Out('
Output of the above table is limited to the first '||(row_limit-1)||' rows to prevent an excessively big output file.
');
621: End If;
622: statusStr:= 'SUCCESS';
623: isFatal := 'FALSE';
624:
689: sqltxt :=sqltxt||' order by mp1.organization_code,mif1.padded_item_number,eri.revised_item_sequence_id, '||
690: ' BCB.operation_seq_num, BCB.item_num,mif2.padded_item_number, BCB.component_sequence_id,'||
691: ' brd.component_reference_designator';
692:
693: num_rows:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, ' Reference Designators on Eco Revised Components ');
694: If (num_rows = row_limit -1 ) Then
695: JTF_DIAGNOSTIC_COREAPI.Line_Out('
Output of the above table is limited to the first '||(row_limit-1)||' rows to prevent an excessively big output file.
');
696: End If;
697: statusStr:= 'SUCCESS';
691: ' brd.component_reference_designator';
692:
693: num_rows:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, ' Reference Designators on Eco Revised Components ');
694: If (num_rows = row_limit -1 ) Then
695: JTF_DIAGNOSTIC_COREAPI.Line_Out('
Output of the above table is limited to the first '||(row_limit-1)||' rows to prevent an excessively big output file.
');
696: End If;
697: statusStr:= 'SUCCESS';
698: isFatal := 'FALSE';
699:
772: sqltxt :=sqltxt||' order by mp1.organization_code,mif1.padded_item_number, eri.revised_item_sequence_id, '||
773: ' BCB.operation_seq_num, BCB.item_num,mif2.padded_item_number, BCB.component_sequence_id,'||
774: ' bsco.substitute_component_id';
775:
776: num_rows:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, ' Substitute Components on Eco Revised Components');
777: If (num_rows = row_limit -1 ) Then
778: JTF_DIAGNOSTIC_COREAPI.Line_Out('
Output of the above table is limited to the first '||(row_limit-1)||' rows to prevent an excessively big output file.
');
779: End If;
780: statusStr:= 'SUCCESS';
774: ' bsco.substitute_component_id';
775:
776: num_rows:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, ' Substitute Components on Eco Revised Components');
777: If (num_rows = row_limit -1 ) Then
778: JTF_DIAGNOSTIC_COREAPI.Line_Out('
Output of the above table is limited to the first '||(row_limit-1)||' rows to prevent an excessively big output file.
');
779: End If;
780: statusStr:= 'SUCCESS';
781: isFatal := 'FALSE';
782:
903: sqltxt :=sqltxt||' and rownum < '||row_limit;
904: sqltxt :=sqltxt||' order by mp1.organization_code,mif1.padded_item_number, '||
905: ' eri.revised_item_sequence_id, bos.operation_seq_num ';
906:
907: num_rows:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, ' Eco Revised Operations ');
908: If (num_rows = row_limit -1 ) Then
909: JTF_DIAGNOSTIC_COREAPI.Line_Out('
Output of the above table is limited to the first '||(row_limit-1)||' rows to prevent an excessively big output file.
');
910: End If;
911: statusStr:= 'SUCCESS';
905: ' eri.revised_item_sequence_id, bos.operation_seq_num ';
906:
907: num_rows:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, ' Eco Revised Operations ');
908: If (num_rows = row_limit -1 ) Then
909: JTF_DIAGNOSTIC_COREAPI.Line_Out('
Output of the above table is limited to the first '||(row_limit-1)||' rows to prevent an excessively big output file.
');
910: End If;
911: statusStr:= 'SUCCESS';
912: isFatal := 'FALSE';
913:
1001: sqltxt :=sqltxt||' and rownum < '||row_limit;
1002: sqltxt :=sqltxt||' order by mp1.organization_code,mif1.padded_item_number, eri.revised_item_sequence_id,'||
1003: ' bos.operation_seq_num, bore.resource_seq_num, br.resource_code ';
1004:
1005: num_rows:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, ' Resources on Eco Revised Operations ');
1006: If (num_rows = row_limit -1 ) Then
1007: JTF_DIAGNOSTIC_COREAPI.Line_Out('
Output of the above table is limited to the first '||(row_limit-1)||' rows to prevent an excessively big output file.
');
1008: End If;
1009: statusStr:= 'SUCCESS';
1003: ' bos.operation_seq_num, bore.resource_seq_num, br.resource_code ';
1004:
1005: num_rows:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, ' Resources on Eco Revised Operations ');
1006: If (num_rows = row_limit -1 ) Then
1007: JTF_DIAGNOSTIC_COREAPI.Line_Out('
Output of the above table is limited to the first '||(row_limit-1)||' rows to prevent an excessively big output file.
');
1008: End If;
1009: statusStr:= 'SUCCESS';
1010: isFatal := 'FALSE';
1011:
1059: end if;
1060: sqltxt :=sqltxt||' and rownum < '||row_limit;
1061: sqltxt :=sqltxt||' order by mp1.organization_code,ecor.revision ';
1062:
1063: num_rows:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, ' Eco Revisions');
1064: If (num_rows = row_limit -1 ) Then
1065: JTF_DIAGNOSTIC_COREAPI.Line_Out('
Output of the above table is limited to the first '||(row_limit-1)||' rows to prevent an excessively big output file.
');
1066: End If;
1067: statusStr:= 'SUCCESS';
1061: sqltxt :=sqltxt||' order by mp1.organization_code,ecor.revision ';
1062:
1063: num_rows:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, ' Eco Revisions');
1064: If (num_rows = row_limit -1 ) Then
1065: JTF_DIAGNOSTIC_COREAPI.Line_Out('
Output of the above table is limited to the first '||(row_limit-1)||' rows to prevent an excessively big output file.
');
1066: End If;
1067: statusStr:= 'SUCCESS';
1068: isFatal := 'FALSE';
1069:
1108: sqltxt :=sqltxt||' and rownum < '||row_limit;
1109: sqltxt :=sqltxt||' order by mp1.organization_code, '||
1110: ' mif1.padded_item_number, ecsd.scheduled_date ';
1111:
1112: num_rows:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, ' Eco Schedule History ');
1113: If (num_rows = row_limit -1 ) Then
1114: JTF_DIAGNOSTIC_COREAPI.Line_Out('
Output of the above table is limited to the first '||(row_limit-1)||' rows to prevent an excessively big output file.
');
1115: End If;
1116: statusStr:= 'SUCCESS';
1110: ' mif1.padded_item_number, ecsd.scheduled_date ';
1111:
1112: num_rows:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, ' Eco Schedule History ');
1113: If (num_rows = row_limit -1 ) Then
1114: JTF_DIAGNOSTIC_COREAPI.Line_Out('
Output of the above table is limited to the first '||(row_limit-1)||' rows to prevent an excessively big output file.
');
1115: End If;
1116: statusStr:= 'SUCCESS';
1117: isFatal := 'FALSE';
1118:
1174: end if;
1175: sqltxt :=sqltxt||' and rownum < '||row_limit;
1176: sqltxt :=sqltxt||' order by mp1.organization_code,mif1.padded_item_number,eri.revised_item_sequence_id,mrir.process_revision';
1177:
1178: num_rows:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, 'Routing Revisions of Revised Items');
1179: If (num_rows = row_limit -1 ) Then
1180: JTF_DIAGNOSTIC_COREAPI.Line_Out('
Output of the above table is limited to the first '||(row_limit-1)||' rows to prevent an excessively big output file.
');
1181: End If;
1182: statusStr:= 'SUCCESS';
1176: sqltxt :=sqltxt||' order by mp1.organization_code,mif1.padded_item_number,eri.revised_item_sequence_id,mrir.process_revision';
1177:
1178: num_rows:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, 'Routing Revisions of Revised Items');
1179: If (num_rows = row_limit -1 ) Then
1180: JTF_DIAGNOSTIC_COREAPI.Line_Out('
Output of the above table is limited to the first '||(row_limit-1)||' rows to prevent an excessively big output file.
');
1181: End If;
1182: statusStr:= 'SUCCESS';
1183: isFatal := 'FALSE';
1184:
1205: end if;
1206: sqltxt :=sqltxt||' and rownum < '||row_limit;
1207: sqltxt :=sqltxt||' order by WFI.ITEM_KEY, WFN.NOTIFICATION_ID,WFN.STATUS ';
1208:
1209: num_rows:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, 'Eco Workflow Notifications ');
1210: If (num_rows = row_limit -1 ) Then
1211: JTF_DIAGNOSTIC_COREAPI.Line_Out('
Output of the above table is limited to the first '||(row_limit-1)||' rows to prevent an excessively big output file.
');
1212: End If;
1213: statusStr := 'SUCCESS';
1207: sqltxt :=sqltxt||' order by WFI.ITEM_KEY, WFN.NOTIFICATION_ID,WFN.STATUS ';
1208:
1209: num_rows:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, 'Eco Workflow Notifications ');
1210: If (num_rows = row_limit -1 ) Then
1211: JTF_DIAGNOSTIC_COREAPI.Line_Out('
Output of the above table is limited to the first '||(row_limit-1)||' rows to prevent an excessively big output file.
');
1212: End If;
1213: statusStr := 'SUCCESS';
1214: isFatal := 'FALSE';
1215:
1330: end if;
1331: sqltxt :=sqltxt||' and rownum < '||row_limit;
1332: sqltxt :=sqltxt||' order by WFI.ITEM_KEY, WFN.NOTIFICATION_ID,WFN.STATUS ';
1333:
1334: num_rows:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, 'Eco Workflow Notification Results ');
1335: If (num_rows = row_limit -1 ) Then
1336: JTF_DIAGNOSTIC_COREAPI.Line_Out('
Output of the above table is limited to the first '||(row_limit-1)||' rows to prevent an excessively big output file.
');
1337: End If;
1338: statusStr := 'SUCCESS';
1332: sqltxt :=sqltxt||' order by WFI.ITEM_KEY, WFN.NOTIFICATION_ID,WFN.STATUS ';
1333:
1334: num_rows:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, 'Eco Workflow Notification Results ');
1335: If (num_rows = row_limit -1 ) Then
1336: JTF_DIAGNOSTIC_COREAPI.Line_Out('
Output of the above table is limited to the first '||(row_limit-1)||' rows to prevent an excessively big output file.
');
1337: End If;
1338: statusStr := 'SUCCESS';
1339: isFatal := 'FALSE';
1340:
1370: sqltxt :=sqltxt||' and rownum < '||row_limit;
1371: sqltxt :=sqltxt||' order by mp1.organization_code, eeal.approval_list_name, '||
1372: ' eeav.approver_name ';
1373:
1374: num_rows:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, ' Eco Approvers ');
1375: If (num_rows = row_limit -1 ) Then
1376: JTF_DIAGNOSTIC_COREAPI.Line_Out('
Output of the above table is limited to the first '||(row_limit-1)||' rows to prevent an excessively big output file.
');
1377: End If;
1378: statusStr:= 'SUCCESS';
1372: ' eeav.approver_name ';
1373:
1374: num_rows:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, ' Eco Approvers ');
1375: If (num_rows = row_limit -1 ) Then
1376: JTF_DIAGNOSTIC_COREAPI.Line_Out('
Output of the above table is limited to the first '||(row_limit-1)||' rows to prevent an excessively big output file.
');
1377: End If;
1378: statusStr:= 'SUCCESS';
1379: isFatal := 'FALSE';
1380:
1415: end if;
1416: sqltxt :=sqltxt||' and rownum < '||row_limit;
1417: sqltxt :=sqltxt||' order by mp1.organization_code, wfr.display_name ';
1418:
1419: num_rows:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, ' Eco Workflow Approver Roles (from wf_roles view) ');
1420: If (num_rows = row_limit -1 ) Then
1421: JTF_DIAGNOSTIC_COREAPI.Line_Out('
Output of the above table is limited to the first '||(row_limit-1)||' rows to prevent an excessively big output file.
');
1422: End If;
1423: statusStr:= 'SUCCESS';
1417: sqltxt :=sqltxt||' order by mp1.organization_code, wfr.display_name ';
1418:
1419: num_rows:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, ' Eco Workflow Approver Roles (from wf_roles view) ');
1420: If (num_rows = row_limit -1 ) Then
1421: JTF_DIAGNOSTIC_COREAPI.Line_Out('
Output of the above table is limited to the first '||(row_limit-1)||' rows to prevent an excessively big output file.
');
1422: End If;
1423: statusStr:= 'SUCCESS';
1424: isFatal := 'FALSE';
1425:
1460: end if;
1461: sqltxt :=sqltxt||' and rownum < '||row_limit;
1462: sqltxt :=sqltxt||' order by mp1.organization_code, wfu.display_name ';
1463:
1464: num_rows:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, ' Eco Workflow Approvers (from wf_users view) ');
1465: If (num_rows = row_limit -1 ) Then
1466: JTF_DIAGNOSTIC_COREAPI.Line_Out('
Output of the above table is limited to the first '||(row_limit-1)||' rows to prevent an excessively big output file.
');
1467: End If;
1468: statusStr:= 'SUCCESS';
1462: sqltxt :=sqltxt||' order by mp1.organization_code, wfu.display_name ';
1463:
1464: num_rows:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, ' Eco Workflow Approvers (from wf_users view) ');
1465: If (num_rows = row_limit -1 ) Then
1466: JTF_DIAGNOSTIC_COREAPI.Line_Out('
Output of the above table is limited to the first '||(row_limit-1)||' rows to prevent an excessively big output file.
');
1467: End If;
1468: statusStr:= 'SUCCESS';
1469: isFatal := 'FALSE';
1470: /* End of records from wf_users table about the ECO Approvers.*/
1503: end if;
1504: sqltxt :=sqltxt||' and rownum < '||row_limit;
1505: sqltxt :=sqltxt||' order by mp1.organization_code, wfur.user_name, wfur.role_name ';
1506:
1507: num_rows:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, ' Association between WF Users, Roles(from wf_user_roles view) ');
1508: If (num_rows = row_limit -1 ) Then
1509: JTF_DIAGNOSTIC_COREAPI.Line_Out('
Output of the above table is limited to the first '||(row_limit-1)||' rows to prevent an excessively big output file.
');
1510: End If;
1511: statusStr:= 'SUCCESS';
1505: sqltxt :=sqltxt||' order by mp1.organization_code, wfur.user_name, wfur.role_name ';
1506:
1507: num_rows:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, ' Association between WF Users, Roles(from wf_user_roles view) ');
1508: If (num_rows = row_limit -1 ) Then
1509: JTF_DIAGNOSTIC_COREAPI.Line_Out('
Output of the above table is limited to the first '||(row_limit-1)||' rows to prevent an excessively big output file.
');
1510: End If;
1511: statusStr:= 'SUCCESS';
1512: isFatal := 'FALSE';
1513: /* End of records from wf_user_roles table */
1546: end if;
1547: sqltxt :=sqltxt||' and rownum < '||row_limit;
1548: sqltxt :=sqltxt||' order by mp1.organization_code, osv.version_number,ose.d_child_name';
1549:
1550: num_rows:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, ' Eco Organization Hierarchy Structure ');
1551: If (num_rows = row_limit -1 ) Then
1552: JTF_DIAGNOSTIC_COREAPI.Line_Out('
Output of the above table is limited to the first '||(row_limit-1)||' rows to prevent an excessively big output file.
');
1553: End If;
1554: statusStr:= 'SUCCESS';
1548: sqltxt :=sqltxt||' order by mp1.organization_code, osv.version_number,ose.d_child_name';
1549:
1550: num_rows:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, ' Eco Organization Hierarchy Structure ');
1551: If (num_rows = row_limit -1 ) Then
1552: JTF_DIAGNOSTIC_COREAPI.Line_Out('
Output of the above table is limited to the first '||(row_limit-1)||' rows to prevent an excessively big output file.
');
1553: End If;
1554: statusStr:= 'SUCCESS';
1555: isFatal := 'FALSE';
1556:
1601:
1602: sqltxt :=sqltxt||' )and rownum < '||row_limit||
1603: ' order by mp.organization_code, mbp.item, mbp.plan_name, mbp.use_up_date ';
1604:
1605: num_rows:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, ' Eco Revised Items Use Up Details ');
1606: If (num_rows = row_limit -1 ) Then
1607: JTF_DIAGNOSTIC_COREAPI.Line_Out('
Output of the above table is limited to the first '||(row_limit-1)||' rows to prevent an excessively big output file.
');
1608: End If;
1609: statusStr:= 'SUCCESS';
1603: ' order by mp.organization_code, mbp.item, mbp.plan_name, mbp.use_up_date ';
1604:
1605: num_rows:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, ' Eco Revised Items Use Up Details ');
1606: If (num_rows = row_limit -1 ) Then
1607: JTF_DIAGNOSTIC_COREAPI.Line_Out('
Output of the above table is limited to the first '||(row_limit-1)||' rows to prevent an excessively big output file.
');
1608: End If;
1609: statusStr:= 'SUCCESS';
1610: isFatal := 'FALSE';
1611: /* End of SQL to fetch use_up details of revised items*/
1610: isFatal := 'FALSE';
1611: /* End of SQL to fetch use_up details of revised items*/
1612:
1613: <
1614: JTF_DIAGNOSTIC_COREAPI.Line_Out('
This data collection script completed as expected
');
1615: report := JTF_DIAGNOSTIC_ADAPTUTIL.constructReport(statusStr,errStr,fixInfo,isFatal);
1616: reportClob := JTF_DIAGNOSTIC_ADAPTUTIL.getReportClob;
1617:
1618: Elsif l_mco_exists =1 Then /* run these scripts if the MCO is exists */
1619:
1620: /* SQL to fetch mass change order details */
1621: /* Get the application installation info. References to Data Dictionary Objects without schema name
1622: included in WHERE predicate are not allowed (GSCC Check: file.sql.47). Schema name has to be passed
1623: as an input parameter to JTF_DIAGNOSTIC_COREAPI.Column_Exists API. */
1624:
1625: l_ret_status := fnd_installation.get_app_info ('ENG'
1626: , l_status
1627: , l_industry
1626: , l_status
1627: , l_industry
1628: , l_oracle_schema
1629: );
1630: /*JTF_DIAGNOSTIC_COREAPI.Line_Out(' l_oracle_schema: '||l_oracle_schema);*/
1631:
1632: sqltxt :='SELECT '||
1633: ' EECIV.CHANGE_NOTICE "Mass Change Number" '||
1634: ' ,MP.ORGANIZATION_CODE "Org Code" '||
1685:
1686: sqltxt :=sqltxt||' and rownum < '||row_limit||
1687: ' order by mp.organization_code ';
1688:
1689: num_rows:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, ' Mass Change Orders ');
1690: If (num_rows = row_limit -1 ) Then
1691: JTF_DIAGNOSTIC_COREAPI.Line_Out('
Output of the above table is limited to the first '||(row_limit-1)||' rows to prevent an excessively big output file.
');
1692: End If;
1693: statusStr:= 'SUCCESS';
1687: ' order by mp.organization_code ';
1688:
1689: num_rows:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, ' Mass Change Orders ');
1690: If (num_rows = row_limit -1 ) Then
1691: JTF_DIAGNOSTIC_COREAPI.Line_Out('
Output of the above table is limited to the first '||(row_limit-1)||' rows to prevent an excessively big output file.
');
1692: End If;
1693: statusStr:= 'SUCCESS';
1694: isFatal := 'FALSE';
1695:
1794:
1795: sqltxt :=sqltxt||' and rownum < '||row_limit||
1796: ' order by mp.organization_code,mif.item_number,bico.operation_seq_num,bico.item_num ';
1797:
1798: num_rows:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, ' Component Changes on Mass Change Orders ');
1799: If (num_rows = row_limit -1 ) Then
1800: JTF_DIAGNOSTIC_COREAPI.Line_Out('
Output of the above table is limited to the first '||(row_limit-1)||' rows to prevent an excessively big output file.
');
1801: End If;
1802: statusStr:= 'SUCCESS';
1796: ' order by mp.organization_code,mif.item_number,bico.operation_seq_num,bico.item_num ';
1797:
1798: num_rows:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, ' Component Changes on Mass Change Orders ');
1799: If (num_rows = row_limit -1 ) Then
1800: JTF_DIAGNOSTIC_COREAPI.Line_Out('
Output of the above table is limited to the first '||(row_limit-1)||' rows to prevent an excessively big output file.
');
1801: End If;
1802: statusStr:= 'SUCCESS';
1803: isFatal := 'FALSE';
1804:
1804:
1805: /* End of SQL to fetch component changes on mass change orders */
1806:
1807: <
1808: JTF_DIAGNOSTIC_COREAPI.Line_Out('
This data collection script completed as expected
');
1809: report := JTF_DIAGNOSTIC_ADAPTUTIL.constructReport(statusStr,errStr,fixInfo,isFatal);
1810: reportClob := JTF_DIAGNOSTIC_ADAPTUTIL.getReportClob;
1811:
1812: End If; /* if l_eco_exists != 0 or l_mco_exists !=0 */
1814: End If; /* if l_eco_name is not null */
1815:
1816: EXCEPTION
1817: when others then
1818: JTF_DIAGNOSTIC_COREAPI.errorprint('Error: '||sqlerrm);
1819: JTF_DIAGNOSTIC_COREAPI.ActionErrorPrint('If this error repeats, please contact Oracle Support Services');
1820: statusStr := 'FAILURE';
1821: errStr := sqlerrm ||' occurred in script. ';
1822: fixInfo := 'Unexpected Exception in BOMDGCNB.pls';
1815:
1816: EXCEPTION
1817: when others then
1818: JTF_DIAGNOSTIC_COREAPI.errorprint('Error: '||sqlerrm);
1819: JTF_DIAGNOSTIC_COREAPI.ActionErrorPrint('If this error repeats, please contact Oracle Support Services');
1820: statusStr := 'FAILURE';
1821: errStr := sqlerrm ||' occurred in script. ';
1822: fixInfo := 'Unexpected Exception in BOMDGCNB.pls';
1823: isFatal := 'FALSE';