eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.OPI_DBI_PTP_BASELINE_PKG dependencies on BIS_COLLECTION_UTILITIES

Line 87: BIS_COLLECTION_UTILITIES.put_line('Get list of organizations to be processed.');

83: END IF;
84:
85:
86: --get list of organizations, for which cost/conv rate need to be collected.
87: BIS_COLLECTION_UTILITIES.put_line('Get list of organizations to be processed.');
88: l_stmt_num := 10;
89:
90: select org1.organization_id, nvl(org2.existing_flag, 0), org1.from_date
91: bulk collect into l_org_list, l_existing_list, l_from_dates

Line 131: BIS_COLLECTION_UTILITIES.put_line('l_new_orgs=' || l_new_orgs);

127: IF l_existing_orgs IS NOT NULL THEN
128: l_existing_orgs := '(' || substrb(l_existing_orgs, 1, instrb(l_existing_orgs, ',', -1, 1)-1) || ')';
129: END IF;
130:
131: BIS_COLLECTION_UTILITIES.put_line('l_new_orgs=' || l_new_orgs);
132: BIS_COLLECTION_UTILITIES.put_line('l_existing_orgs=' || l_existing_orgs);
133:
134: --collect cost for new organizations
135: --two rows inserted for each new item-org as:

Line 132: BIS_COLLECTION_UTILITIES.put_line('l_existing_orgs=' || l_existing_orgs);

128: l_existing_orgs := '(' || substrb(l_existing_orgs, 1, instrb(l_existing_orgs, ',', -1, 1)-1) || ')';
129: END IF;
130:
131: BIS_COLLECTION_UTILITIES.put_line('l_new_orgs=' || l_new_orgs);
132: BIS_COLLECTION_UTILITIES.put_line('l_existing_orgs=' || l_existing_orgs);
133:
134: --collect cost for new organizations
135: --two rows inserted for each new item-org as:
136: -- row 1: from_date = global_start_date, to_date = baseline from_date, cost = 0

Line 139: BIS_COLLECTION_UTILITIES.put_line('Collect item cost for new organizations.');

135: --two rows inserted for each new item-org as:
136: -- row 1: from_date = global_start_date, to_date = baseline from_date, cost = 0
137: -- row 2: from_date = baseline from_date, to_date = null, cost = item cost from cst_item_costs
138: IF l_new_orgs IS NOT NULL THEN
139: BIS_COLLECTION_UTILITIES.put_line('Collect item cost for new organizations.');
140: l_stmt_num := 30;
141: l_stmt := ' insert into OPI_DBI_PTP_COST
142: (
143: FROZEN_FLAG,

Line 240: BIS_COLLECTION_UTILITIES.put_line('Collect conversion rate new organizations.');

236: ;
237: EXECUTE IMMEDIATE l_stmt USING g_global_start_date, 1, g_sysdate, g_last_updated_by, g_sysdate, g_last_updated_by, g_last_update_login, g_sysdate, -1, 1, g_sysdate, g_last_updated_by, g_sysdate, g_last_updated_by, g_last_update_login, g_sysdate;
238: --get conversion rate for new organizations
239: -- added secondary currency support
240: BIS_COLLECTION_UTILITIES.put_line('Collect conversion rate new organizations.');
241: FOR i IN l_org_list.FIRST..l_org_list.LAST LOOP
242: IF l_existing_list(i) = 0 THEN
243: l_stmt_num := 40;
244: SELECT gsob.currency_code

Line 362: BIS_COLLECTION_UTILITIES.writeMissingRateHeader;

358: EXECUTE IMMEDIATE l_stmt USING -1, l_org_list(i), l_rate, l_secondary_rate, l_currency_code, l_from_dates(i), 1, g_sysdate, g_last_updated_by, g_sysdate, g_last_updated_by, g_last_update_login;
359: ELSE
360: IF (l_missing_flag = 0) THEN
361: l_missing_flag := 1;
362: BIS_COLLECTION_UTILITIES.writeMissingRateHeader;
363: END IF;
364: IF (l_rate = -1) THEN
365: BIS_COLLECTION_UTILITIES.writeMissingRate(
366: g_global_rate_type,

Line 365: BIS_COLLECTION_UTILITIES.writeMissingRate(

361: l_missing_flag := 1;
362: BIS_COLLECTION_UTILITIES.writeMissingRateHeader;
363: END IF;
364: IF (l_rate = -1) THEN
365: BIS_COLLECTION_UTILITIES.writeMissingRate(
366: g_global_rate_type,
367: l_currency_code,
368: g_global_currency_code,
369: g_sysdate

Line 373: BIS_COLLECTION_UTILITIES.writeMissingRate(

369: g_sysdate
370: );
371: END IF;
372: IF (l_secondary_rate = -1) THEN
373: BIS_COLLECTION_UTILITIES.writeMissingRate(
374: g_global_rate_type,
375: l_currency_code,
376: g_secondary_currency_code,
377: g_sysdate

Line 388: BIS_COLLECTION_UTILITIES.put_line('Collect item cost for existing organizations.');

384: END IF;
385:
386: --get cost/conversion rate for existing organizations
387: IF l_existing_orgs IS NOT NULL THEN
388: BIS_COLLECTION_UTILITIES.put_line('Collect item cost for existing organizations.');
389: l_stmt_num := 70;
390: l_stmt := '
391: update OPI_DBI_PTP_COST
392: set frozen_flag = null,

Line 499: BIS_COLLECTION_UTILITIES.put_line('Collect conversion rate for existing organizations.');

495: where source = -1
496: ;
497:
498: --get conversion rate for existing organizations
499: BIS_COLLECTION_UTILITIES.put_line('Collect conversion rate for existing organizations.');
500: l_stmt_num := 100;
501: l_stmt := '
502: update OPI_DBI_PTP_CONV
503: set frozen_flag = null,

Line 623: BIS_COLLECTION_UTILITIES.writeMissingRateHeader;

619: EXECUTE IMMEDIATE l_stmt USING l_org_list(i), l_rate, l_secondary_rate, g_sysdate, -1, l_currency_code, 1, g_sysdate, g_last_updated_by, g_sysdate, g_last_updated_by, g_last_update_login;
620: ELSE
621: IF (l_missing_flag = 0) THEN
622: l_missing_flag := 1;
623: BIS_COLLECTION_UTILITIES.writeMissingRateHeader;
624: END IF;
625: IF (l_rate = -1) THEN
626: BIS_COLLECTION_UTILITIES.writeMissingRate(
627: g_global_rate_type,

Line 626: BIS_COLLECTION_UTILITIES.writeMissingRate(

622: l_missing_flag := 1;
623: BIS_COLLECTION_UTILITIES.writeMissingRateHeader;
624: END IF;
625: IF (l_rate = -1) THEN
626: BIS_COLLECTION_UTILITIES.writeMissingRate(
627: g_global_rate_type,
628: l_currency_code,
629: g_global_currency_code,
630: g_sysdate

Line 634: BIS_COLLECTION_UTILITIES.writeMissingRate(

630: g_sysdate
631: );
632: END IF;
633: IF (l_secondary_rate = -1) THEN
634: BIS_COLLECTION_UTILITIES.writeMissingRate(
635: g_global_rate_type,
636: l_currency_code,
637: g_secondary_currency_code,
638: g_sysdate

Line 660: BIS_COLLECTION_UTILITIES.put_line('There are missing currency rate. Program stops. Please check output file for more details.');

656: END IF;
657:
658: EXCEPTION
659: WHEN cost_conversion_rate_exception THEN
660: BIS_COLLECTION_UTILITIES.put_line('There are missing currency rate. Program stops. Please check output file for more details.');
661: retcode := g_error;
662: RAISE cost_conversion_rate_exception;
663: WHEN OTHERS THEN
664: BIS_COLLECTION_UTILITIES.put_line('Fail to collect cost/conversion rate for discrete manufacturing organizations.');

Line 664: BIS_COLLECTION_UTILITIES.put_line('Fail to collect cost/conversion rate for discrete manufacturing organizations.');

660: BIS_COLLECTION_UTILITIES.put_line('There are missing currency rate. Program stops. Please check output file for more details.');
661: retcode := g_error;
662: RAISE cost_conversion_rate_exception;
663: WHEN OTHERS THEN
664: BIS_COLLECTION_UTILITIES.put_line('Fail to collect cost/conversion rate for discrete manufacturing organizations.');
665: BIS_COLLECTION_UTILITIES.put_line('Error out at stmt_num ' || l_stmt_num || ' in Get_Discrete_Cost_and_Rate.');
666: retcode := g_error;
667: errbuf := SQLERRM;
668: RAISE cost_conversion_rate_exception;

Line 665: BIS_COLLECTION_UTILITIES.put_line('Error out at stmt_num ' || l_stmt_num || ' in Get_Discrete_Cost_and_Rate.');

661: retcode := g_error;
662: RAISE cost_conversion_rate_exception;
663: WHEN OTHERS THEN
664: BIS_COLLECTION_UTILITIES.put_line('Fail to collect cost/conversion rate for discrete manufacturing organizations.');
665: BIS_COLLECTION_UTILITIES.put_line('Error out at stmt_num ' || l_stmt_num || ' in Get_Discrete_Cost_and_Rate.');
666: retcode := g_error;
667: errbuf := SQLERRM;
668: RAISE cost_conversion_rate_exception;
669: END Get_Discrete_Cost_and_Rate;

Line 689: BIS_COLLECTION_UTILITIES.put_line('Get list of Process-enabled organizations to be processed.');

685: l_missing_flag NUMBER := 0;
686: l_stmt_num NUMBER := 0;
687: BEGIN
688: --get list of organizations, for which cost need to be collected.
689: BIS_COLLECTION_UTILITIES.put_line('Get list of Process-enabled organizations to be processed.');
690: l_stmt_num := 10;
691:
692: -- following statement is same as for discrete, except limiting organizations to process-enabled
693:

Line 719: BIS_COLLECTION_UTILITIES.put_line(TO_CHAR(SQL%ROWCOUNT) || ' orgs identified for cost determination.');

715: where org1.organization_id = org2.organization_id (+)
716: and org1.organization_id = mp.organization_id
717: and mp.process_enabled_flag = 'Y';
718:
719: BIS_COLLECTION_UTILITIES.put_line(TO_CHAR(SQL%ROWCOUNT) || ' orgs identified for cost determination.');
720:
721: IF l_org_list.count <> 0 THEN
722: FOR i IN l_org_list.FIRST..l_org_list.LAST LOOP
723: IF l_existing_list(i) = 0 THEN

Line 733: BIS_COLLECTION_UTILITIES.put_line('Collect item cost for new Process-Enabled organizations.');

729: END IF;
730:
731: IF l_new_orgs IS NOT NULL THEN
732: l_new_orgs := '(' || substrb(l_new_orgs, 1, instrb(l_new_orgs, ',', -1, 1)-1) || ')';
733: BIS_COLLECTION_UTILITIES.put_line('Collect item cost for new Process-Enabled organizations.');
734: l_stmt_num := 30;
735: l_stmt := 'INSERT INTO opi_pmi_cost_param_gtmp
736: (
737: item_id,

Line 755: BIS_COLLECTION_UTILITIES.put_line(TO_CHAR(SQL%ROWCOUNT) || ' org items identified for costing.');

751: w.mtl_organization_id IN ' || l_new_orgs;
752:
753: EXECUTE IMMEDIATE l_stmt;
754:
755: BIS_COLLECTION_UTILITIES.put_line(TO_CHAR(SQL%ROWCOUNT) || ' org items identified for costing.');
756:
757: opi_pmi_cost.get_cost;
758:
759: END IF;

Line 763: BIS_COLLECTION_UTILITIES.put_line('Adding item cost for new Process-Enabled organizations...');

759: END IF;
760:
761: IF l_existing_orgs IS NOT NULL THEN
762: l_existing_orgs := '(' || substrb(l_existing_orgs, 1, instrb(l_existing_orgs, ',', -1, 1)-1) || ')';
763: BIS_COLLECTION_UTILITIES.put_line('Adding item cost for new Process-Enabled organizations...');
764: l_stmt_num := 35;
765: l_stmt := 'INSERT INTO opi_pmi_cost_param_gtmp
766: (
767: item_id,

Line 785: BIS_COLLECTION_UTILITIES.put_line(TO_CHAR(SQL%ROWCOUNT) || ' org items identified for costing.');

781: w.mtl_organization_id IN ' || l_existing_orgs;
782:
783: EXECUTE IMMEDIATE l_stmt;
784:
785: BIS_COLLECTION_UTILITIES.put_line(TO_CHAR(SQL%ROWCOUNT) || ' org items identified for costing.');
786:
787: opi_pmi_cost.get_cost;
788:
789: END IF;

Line 791: BIS_COLLECTION_UTILITIES.put_line('l_new_orgs=' || l_new_orgs);

787: opi_pmi_cost.get_cost;
788:
789: END IF;
790:
791: BIS_COLLECTION_UTILITIES.put_line('l_new_orgs=' || l_new_orgs);
792: BIS_COLLECTION_UTILITIES.put_line('l_existing_orgs=' || l_existing_orgs);
793:
794: --collect cost for new organizations
795: --two rows inserted for each new item-org as:

Line 792: BIS_COLLECTION_UTILITIES.put_line('l_existing_orgs=' || l_existing_orgs);

788:
789: END IF;
790:
791: BIS_COLLECTION_UTILITIES.put_line('l_new_orgs=' || l_new_orgs);
792: BIS_COLLECTION_UTILITIES.put_line('l_existing_orgs=' || l_existing_orgs);
793:
794: --collect cost for new organizations
795: --two rows inserted for each new item-org as:
796: -- row 1: from_date = global_start_date, to_date = baseline from_date, cost = 0

Line 895: BIS_COLLECTION_UTILITIES.put_line(TO_CHAR(SQL%ROWCOUNT) || ' costs collected for new Process orgs.');

891: g_last_updated_by, g_last_update_login, g_sysdate, -1, 2,
892: g_sysdate, g_last_updated_by, g_sysdate, g_last_updated_by,
893: g_last_update_login, g_sysdate;
894:
895: BIS_COLLECTION_UTILITIES.put_line(TO_CHAR(SQL%ROWCOUNT) || ' costs collected for new Process orgs.');
896:
897: END IF; -- l_new_orgs IS NOT NULL
898:
899: --get cost/conversion rate for existing organizations

Line 901: BIS_COLLECTION_UTILITIES.put_line('Collect item cost for existing organizations.');

897: END IF; -- l_new_orgs IS NOT NULL
898:
899: --get cost/conversion rate for existing organizations
900: IF l_existing_orgs IS NOT NULL THEN
901: BIS_COLLECTION_UTILITIES.put_line('Collect item cost for existing organizations.');
902: l_stmt_num := 70;
903: l_stmt := '
904: update OPI_DBI_PTP_COST
905: set frozen_flag = null,

Line 987: BIS_COLLECTION_UTILITIES.put_line(TO_CHAR(SQL%ROWCOUNT) || ' Process Org costs merged into costing fact.');

983: )
984: ';
985: EXECUTE IMMEDIATE l_stmt USING -1, g_sysdate, 2, g_sysdate, g_last_updated_by, g_sysdate, g_last_updated_by, g_last_update_login;
986:
987: BIS_COLLECTION_UTILITIES.put_line(TO_CHAR(SQL%ROWCOUNT) || ' Process Org costs merged into costing fact.');
988:
989: --shift date back to global_start_date for new items
990: l_stmt :='
991: update OPI_DBI_PTP_COST

Line 1014: BIS_COLLECTION_UTILITIES.put_line('Fail to collect cost for Process Manufacturing organizations.');

1010: END IF; -- l_existing_orgs IS NOT NULL
1011:
1012: EXCEPTION
1013: WHEN OTHERS THEN
1014: BIS_COLLECTION_UTILITIES.put_line('Fail to collect cost for Process Manufacturing organizations.');
1015: BIS_COLLECTION_UTILITIES.put_line('Error out at stmt_num ' || l_stmt_num || ' in Get_Process_Cost');
1016: retcode := g_error;
1017: errbuf := SQLERRM;
1018: RAISE cost_conversion_rate_exception;

Line 1015: BIS_COLLECTION_UTILITIES.put_line('Error out at stmt_num ' || l_stmt_num || ' in Get_Process_Cost');

1011:
1012: EXCEPTION
1013: WHEN OTHERS THEN
1014: BIS_COLLECTION_UTILITIES.put_line('Fail to collect cost for Process Manufacturing organizations.');
1015: BIS_COLLECTION_UTILITIES.put_line('Error out at stmt_num ' || l_stmt_num || ' in Get_Process_Cost');
1016: retcode := g_error;
1017: errbuf := SQLERRM;
1018: RAISE cost_conversion_rate_exception;
1019:

Line 1064: BIS_COLLECTION_UTILITIES.put_line('Start baseline collection.');

1060: ;
1061: l_baseline_record l_baseline_info%ROWTYPE;
1062: BEGIN
1063: --setup
1064: BIS_COLLECTION_UTILITIES.put_line('Start baseline collection.');
1065: l_segment_num := 10;
1066: /* we don't truncate table here at first run. purge/deletion should be provided separately*/
1067:
1068: IF BIS_COLLECTION_UTILITIES.SETUP(

Line 1068: IF BIS_COLLECTION_UTILITIES.SETUP(

1064: BIS_COLLECTION_UTILITIES.put_line('Start baseline collection.');
1065: l_segment_num := 10;
1066: /* we don't truncate table here at first run. purge/deletion should be provided separately*/
1067:
1068: IF BIS_COLLECTION_UTILITIES.SETUP(
1069: p_object_name => 'OPI_DBI_PTP_PLAN_F'
1070: ) = false then
1071: BIS_COLLECTION_UTILITIES.put_line('Fail to initialize through BIS_COLLECTION_UTILITIES.SETUP.');
1072: retcode := g_error;

Line 1071: BIS_COLLECTION_UTILITIES.put_line('Fail to initialize through BIS_COLLECTION_UTILITIES.SETUP.');

1067:
1068: IF BIS_COLLECTION_UTILITIES.SETUP(
1069: p_object_name => 'OPI_DBI_PTP_PLAN_F'
1070: ) = false then
1071: BIS_COLLECTION_UTILITIES.put_line('Fail to initialize through BIS_COLLECTION_UTILITIES.SETUP.');
1072: retcode := g_error;
1073: errbuf := 'Program stops.';
1074: RAISE intialization_exception;
1075: End if;

Line 1078: BIS_COLLECTION_UTILITIES.put_line('Check global variables.');

1074: RAISE intialization_exception;
1075: End if;
1076:
1077: l_segment_num := 20;
1078: BIS_COLLECTION_UTILITIES.put_line('Check global variables.');
1079: l_list(1) := 'BIS_GLOBAL_START_DATE';
1080: l_list(2) := 'BIS_PRIMARY_CURRENCY_CODE';
1081: l_list(3) := 'BIS_PRIMARY_RATE_TYPE';
1082:

Line 1084: BIS_COLLECTION_UTILITIES.put_line('Missing global parameters. Please setup global_start_date and primary_currency_code first.');

1080: l_list(2) := 'BIS_PRIMARY_CURRENCY_CODE';
1081: l_list(3) := 'BIS_PRIMARY_RATE_TYPE';
1082:
1083: IF (NOT BIS_COMMON_PARAMETERS.CHECK_GLOBAL_PARAMETERS(l_list)) THEN
1084: BIS_COLLECTION_UTILITIES.put_line('Missing global parameters. Please setup global_start_date and primary_currency_code first.');
1085: retcode := g_error;
1086: errbuf := 'Program stops.';
1087: RAISE intialization_exception;
1088: END IF;

Line 1091: BIS_COLLECTION_UTILITIES.put_line('Initialize global variables.');

1087: RAISE intialization_exception;
1088: END IF;
1089:
1090: --initialize global variables
1091: BIS_COLLECTION_UTILITIES.put_line('Initialize global variables.');
1092: l_segment_num := 30;
1093: BEGIN
1094: g_sysdate := trunc(sysdate);
1095: g_created_by := nvl(fnd_global.user_id, -1);

Line 1108: BIS_COLLECTION_UTILITIES.put_line('global_start_date = ' || TO_CHAR(g_global_start_date, 'DD-MON-YYYY') || '.');

1104: IF NOT fnd_installation.get_app_info( 'OPI', l_status, l_industry, g_opi_schema) THEN
1105: RAISE intialization_exception;
1106: END IF;
1107: g_degree := bis_common_parameters.get_degree_of_parallelism;
1108: BIS_COLLECTION_UTILITIES.put_line('global_start_date = ' || TO_CHAR(g_global_start_date, 'DD-MON-YYYY') || '.');
1109: g_global_rate_type := bis_common_parameters.get_rate_type;
1110: BIS_COLLECTION_UTILITIES.put_line('The primary rate type is ' || g_global_rate_type);
1111:
1112: -- secondary currency support

Line 1110: BIS_COLLECTION_UTILITIES.put_line('The primary rate type is ' || g_global_rate_type);

1106: END IF;
1107: g_degree := bis_common_parameters.get_degree_of_parallelism;
1108: BIS_COLLECTION_UTILITIES.put_line('global_start_date = ' || TO_CHAR(g_global_start_date, 'DD-MON-YYYY') || '.');
1109: g_global_rate_type := bis_common_parameters.get_rate_type;
1110: BIS_COLLECTION_UTILITIES.put_line('The primary rate type is ' || g_global_rate_type);
1111:
1112: -- secondary currency support
1113: g_secondary_rate_type := bis_common_parameters.get_secondary_rate_type;
1114: g_secondary_currency_code :=

Line 1124: BIS_COLLECTION_UTILITIES.PUT_LINE ('The global secondary currency code setup is incorrect. The secondary currency code cannot be null when the secondary rate type is defined and vice versa.');

1120: g_secondary_rate_type IS NOT NULL)
1121: OR (g_secondary_currency_code IS NOT NULL AND
1122: g_secondary_rate_type IS NULL) ) THEN
1123:
1124: BIS_COLLECTION_UTILITIES.PUT_LINE ('The global secondary currency code setup is incorrect. The secondary currency code cannot be null when the secondary rate type is defined and vice versa.');
1125:
1126: RAISE intialization_exception;
1127:
1128: END IF;

Line 1133: BIS_COLLECTION_UTILITIES.put_line('Fail to initialize global variable values. Please re-run the concurrent request set.');

1129:
1130:
1131: EXCEPTION
1132: WHEN others THEN
1133: BIS_COLLECTION_UTILITIES.put_line('Fail to initialize global variable values. Please re-run the concurrent request set.');
1134: retcode := g_error;
1135: errbuf := SQLERRM;
1136: RAISE intialization_exception;
1137: END;

Line 1143: BIS_COLLECTION_UTILITIES.put_line('re-synchronize OPI_PTP_SUM_STG_MV with OPI_DBI_PTP_PLAN_STG.');

1139: --if fail to refresh OPI_PTP_SUM_STG_MV last time, try to refresh it again
1140: BEGIN
1141: select stop_reason_code into l_refresh from opi_dbi_run_log_curr where etl_id = 7 and source = 1;
1142: IF l_refresh = 1 THEN
1143: BIS_COLLECTION_UTILITIES.put_line('re-synchronize OPI_PTP_SUM_STG_MV with OPI_DBI_PTP_PLAN_STG.');
1144: EXECUTE IMMEDIATE 'ALTER SESSION FORCE PARALLEL QUERY';
1145: DBMS_MVIEW.REFRESH('OPI_PTP_SUM_STG_MV','?',parallelism => g_degree);
1146: EXECUTE IMMEDIATE 'ALTER SESSION DISABLE PARALLEL QUERY';
1147: END IF;

Line 1168: BIS_COLLECTION_UTILITIES.put_line('Fail to refresh materialized view OPI_PTP_SUM_STG_MV. Please fix the problem and re-run the concurrent request set.');

1164: values
1165: (null, 1, null, null, null, 7, 2, null, g_sysdate, g_last_updated_by, g_sysdate, g_last_updated_by, g_last_update_login);
1166: commit;
1167: WHEN OTHERS THEN
1168: BIS_COLLECTION_UTILITIES.put_line('Fail to refresh materialized view OPI_PTP_SUM_STG_MV. Please fix the problem and re-run the concurrent request set.');
1169: retcode := g_error;
1170: errbuf := SQLERRM;
1171: RAISE intialization_exception;
1172: END;

Line 1176: --BIS_COLLECTION_UTILITIES.put_line('Check missing date against time dimension.');

1172: END;
1173:
1174: --check missing date in time dimension
1175: --some logic here is commented out, because ISC APS extraction has checked dangling key against time dimension.
1176: --BIS_COLLECTION_UTILITIES.put_line('Check missing date against time dimension.');
1177: BIS_COLLECTION_UTILITIES.put_line('Check scheduled baseline collections.');
1178: l_segment_num := 40;
1179: BEGIN
1180: select min(from_date)

Line 1177: BIS_COLLECTION_UTILITIES.put_line('Check scheduled baseline collections.');

1173:
1174: --check missing date in time dimension
1175: --some logic here is commented out, because ISC APS extraction has checked dangling key against time dimension.
1176: --BIS_COLLECTION_UTILITIES.put_line('Check missing date against time dimension.');
1177: BIS_COLLECTION_UTILITIES.put_line('Check scheduled baseline collections.');
1178: l_segment_num := 40;
1179: BEGIN
1180: select min(from_date)
1181: into l_from_date

Line 1195: BIS_COLLECTION_UTILITIES.put_line('Fail to retreive following collection information: minimum from_date and maximum to_date.');

1191: ) boundary
1192: ;
1193: EXCEPTION
1194: WHEN others THEN
1195: BIS_COLLECTION_UTILITIES.put_line('Fail to retreive following collection information: minimum from_date and maximum to_date.');
1196: retcode := g_error;
1197: errbuf := SQLERRM;
1198: RAISE intialization_exception;
1199: END;

Line 1205: BIS_COLLECTION_UTILITIES.put_line('There is no scheduled collection. Program exits normally.');

1201: /*
1202: l_from_date = null, meaning that there is no scheduled collection to be processed. just simply exit.
1203: */
1204: IF l_from_date is NULL THEN
1205: BIS_COLLECTION_UTILITIES.put_line('There is no scheduled collection. Program exits normally.');
1206: BIS_COLLECTION_UTILITIES.WRAPUP(
1207: p_status => TRUE,
1208: p_count => 0,
1209: p_message => 'There is no scheduled collection.'

Line 1206: BIS_COLLECTION_UTILITIES.WRAPUP(

1202: l_from_date = null, meaning that there is no scheduled collection to be processed. just simply exit.
1203: */
1204: IF l_from_date is NULL THEN
1205: BIS_COLLECTION_UTILITIES.put_line('There is no scheduled collection. Program exits normally.');
1206: BIS_COLLECTION_UTILITIES.WRAPUP(
1207: p_status => TRUE,
1208: p_count => 0,
1209: p_message => 'There is no scheduled collection.'
1210: );

Line 1217: BIS_COLLECTION_UTILITIES.put_line('Collect latest APS snapshot.');

1213: END IF;
1214:
1215: --change per bug 3422479
1216: --call ISC APS collection program to get latest APS data
1217: BIS_COLLECTION_UTILITIES.put_line('Collect latest APS snapshot.');
1218: l_segment_num := 65;
1219: l_isc_return_code := ISC_DBI_MSC_OBJECTS_C.LOAD_BASES;
1220: IF (l_isc_return_code <> 1 ) THEN
1221: --BIS_COLLECTION_UTILITIES.put_line('Fail to collect latest APS snapshot.');

Line 1221: --BIS_COLLECTION_UTILITIES.put_line('Fail to collect latest APS snapshot.');

1217: BIS_COLLECTION_UTILITIES.put_line('Collect latest APS snapshot.');
1218: l_segment_num := 65;
1219: l_isc_return_code := ISC_DBI_MSC_OBJECTS_C.LOAD_BASES;
1220: IF (l_isc_return_code <> 1 ) THEN
1221: --BIS_COLLECTION_UTILITIES.put_line('Fail to collect latest APS snapshot.');
1222: retcode := g_error;
1223: errbuf := 'Fail to collect latest APS snapshot.';
1224: RAISE isc_collection_exception;
1225: END IF;

Line 1229: BIS_COLLECTION_UTILITIES.put_line('Check if one organization exists in more than one baselines.');

1225: END IF;
1226:
1227: --validate information against baseline setup
1228: --Check if one organization exists in more than one baseline as scheduled
1229: BIS_COLLECTION_UTILITIES.put_line('Check if one organization exists in more than one baselines.');
1230: l_segment_num := 50;
1231: select count(*)
1232: into l_count
1233: from

Line 1252: BIS_COLLECTION_UTILITIES.put_line('There are organizations existing in more than one baseline, which is not allowed. Please verify baseline setup.');

1248: where num_of_plans > 1
1249: ;
1250:
1251: IF l_count > 0 THEN
1252: BIS_COLLECTION_UTILITIES.put_line('There are organizations existing in more than one baseline, which is not allowed. Please verify baseline setup.');
1253: retcode := g_error;
1254: errbuf := 'Organizations exist in more than one baseline.';
1255: RAISE collection_parameter_exception;
1256: END IF;

Line 1259: BIS_COLLECTION_UTILITIES.put_line('Check from_date against plan run date.');

1255: RAISE collection_parameter_exception;
1256: END IF;
1257:
1258: --Check from_date against plan run date
1259: BIS_COLLECTION_UTILITIES.put_line('Check from_date against plan run date.');
1260: BEGIN
1261: select count(*)
1262: into l_count
1263: from OPI_DBI_BASELINE_SCHEDULES sched,

Line 1281: BIS_COLLECTION_UTILITIES.put_line('Some APS plans have plan run date post to associated baseline collection from_date. Please reset the from_date.');

1277: RAISE collection_parameter_exception;
1278: END;
1279:
1280: IF l_count <> 0 THEN
1281: BIS_COLLECTION_UTILITIES.put_line('Some APS plans have plan run date post to associated baseline collection from_date. Please reset the from_date.');
1282: BIS_COLLECTION_UTILITIES.put_line(RPAD('BASELINE NAME', 20, ' ') || ' ' ||
1283: RPAD('PLAN NAME', 20, ' ') || ' ' ||
1284: RPAD('FROM DATE', 20, ' ') || ' ' ||
1285: RPAD('PLAN RUN DATE', 20, ' ')

Line 1282: BIS_COLLECTION_UTILITIES.put_line(RPAD('BASELINE NAME', 20, ' ') || ' ' ||

1278: END;
1279:
1280: IF l_count <> 0 THEN
1281: BIS_COLLECTION_UTILITIES.put_line('Some APS plans have plan run date post to associated baseline collection from_date. Please reset the from_date.');
1282: BIS_COLLECTION_UTILITIES.put_line(RPAD('BASELINE NAME', 20, ' ') || ' ' ||
1283: RPAD('PLAN NAME', 20, ' ') || ' ' ||
1284: RPAD('FROM DATE', 20, ' ') || ' ' ||
1285: RPAD('PLAN RUN DATE', 20, ' ')
1286: );

Line 1287: BIS_COLLECTION_UTILITIES.put_line(RPAD('-', 20, '-') || ' ' ||

1283: RPAD('PLAN NAME', 20, ' ') || ' ' ||
1284: RPAD('FROM DATE', 20, ' ') || ' ' ||
1285: RPAD('PLAN RUN DATE', 20, ' ')
1286: );
1287: BIS_COLLECTION_UTILITIES.put_line(RPAD('-', 20, '-') || ' ' ||
1288: RPAD('-', 20, '-') || ' ' ||
1289: RPAD('-', 20, '-') || ' ' ||
1290: RPAD('-', 20, '-')
1291: );

Line 1310: BIS_COLLECTION_UTILITIES.put_line(l_strings(i));

1306: and sched.schedule_type = 1
1307: ;
1308:
1309: FOR i IN l_strings.FIRST..l_strings.LAST LOOP
1310: BIS_COLLECTION_UTILITIES.put_line(l_strings(i));
1311: END LOOP;
1312: retcode := g_error;
1313: errbuf := 'Program stops.';
1314: RAISE collection_parameter_exception;

Line 1318: BIS_COLLECTION_UTILITIES.put_line('Lock rows of schedules to be processed.');

1314: RAISE collection_parameter_exception;
1315: END IF;
1316:
1317: --lock schedules to be processed
1318: BIS_COLLECTION_UTILITIES.put_line('Lock rows of schedules to be processed.');
1319: l_segment_num := 60;
1320: lock table opi_dbi_baseline_definitions in exclusive mode;
1321:
1322:

Line 1324: BIS_COLLECTION_UTILITIES.put_line('Start loading new data');

1320: lock table opi_dbi_baseline_definitions in exclusive mode;
1321:
1322:
1323: --start loading new data
1324: BIS_COLLECTION_UTILITIES.put_line('Start loading new data');
1325: l_segment_num := 70;
1326: BEGIN
1327: select def.baseline_name, sched.baseline_id, def.last_collected_date
1328: bulk collect into l_strings, l_baseline_ids, l_collected_dates

Line 1337: BIS_COLLECTION_UTILITIES.put_line('Fail to retreive baseline information.');

1333: and def.baseline_id = sched.baseline_id
1334: ;
1335: EXCEPTION
1336: WHEN others THEN
1337: BIS_COLLECTION_UTILITIES.put_line('Fail to retreive baseline information.');
1338: retcode := g_error;
1339: errbuf := SQLERRM;
1340: RAISE load_exception;
1341: END;

Line 1344: BIS_COLLECTION_UTILITIES.put_line('Prepare SQL statements.');

1340: RAISE load_exception;
1341: END;
1342:
1343: --prepare statements
1344: BIS_COLLECTION_UTILITIES.put_line('Prepare SQL statements.');
1345: l_segment_num := 80;
1346: IF l_baseline_ids.count <> 0 THEN
1347: l_create_tables.extend(l_baseline_ids.count);
1348: l_insert_tables.extend(l_baseline_ids.count);

Line 1368: BIS_COLLECTION_UTILITIES.put_line('Create temporary regular tables.');

1364: l_drop_tables(i) := 'DROP TABLE OPI_DBI_PTP_TMP_' || l_baseline_ids(i);
1365: END LOOP;
1366: END IF;
1367:
1368: BIS_COLLECTION_UTILITIES.put_line('Create temporary regular tables.');
1369: l_segment_num := 90;
1370: IF l_create_tables.count <> 0 THEN
1371: FOR i IN l_create_tables.FIRST..l_create_tables.LAST LOOP
1372: l_stmt := l_create_tables(i) || ' (' ||

Line 1387: BIS_COLLECTION_UTILITIES.put_line('...'|| l_create_tables(i));

1383: 'LAST_UPDATE_DATE DATE NOT NULL, ' ||
1384: 'LAST_UPDATED_BY NUMBER NOT NULL, ' ||
1385: 'LAST_UPDATE_LOGIN NUMBER)';
1386: BEGIN
1387: BIS_COLLECTION_UTILITIES.put_line('...'|| l_create_tables(i));
1388: EXECUTE IMMEDIATE l_stmt;
1389: EXCEPTION
1390: WHEN tablename_exist_exception THEN
1391: BIS_COLLECTION_UTILITIES.put_line('Temporary regular table already exists for baseline ' || l_strings(i) || '. Cleanup temporary table.');

Line 1391: BIS_COLLECTION_UTILITIES.put_line('Temporary regular table already exists for baseline ' || l_strings(i) || '. Cleanup temporary table.');

1387: BIS_COLLECTION_UTILITIES.put_line('...'|| l_create_tables(i));
1388: EXECUTE IMMEDIATE l_stmt;
1389: EXCEPTION
1390: WHEN tablename_exist_exception THEN
1391: BIS_COLLECTION_UTILITIES.put_line('Temporary regular table already exists for baseline ' || l_strings(i) || '. Cleanup temporary table.');
1392: EXECUTE IMMEDIATE 'truncate table opi_dbi_ptp_tmp_' || l_baseline_ids(i);
1393: WHEN others THEN
1394: BIS_COLLECTION_UTILITIES.put_line('Fail to create temporary regular table for baseline ' || l_strings(i) || '.');
1395: retcode := g_error;

Line 1394: BIS_COLLECTION_UTILITIES.put_line('Fail to create temporary regular table for baseline ' || l_strings(i) || '.');

1390: WHEN tablename_exist_exception THEN
1391: BIS_COLLECTION_UTILITIES.put_line('Temporary regular table already exists for baseline ' || l_strings(i) || '. Cleanup temporary table.');
1392: EXECUTE IMMEDIATE 'truncate table opi_dbi_ptp_tmp_' || l_baseline_ids(i);
1393: WHEN others THEN
1394: BIS_COLLECTION_UTILITIES.put_line('Fail to create temporary regular table for baseline ' || l_strings(i) || '.');
1395: retcode := g_error;
1396: errbuf := SQLERRM;
1397: RAISE load_exception;
1398: END;

Line 1402: BIS_COLLECTION_UTILITIES.put_line('Insert new data into temporary regular tables.');

1398: END;
1399: END LOOP;
1400: END IF;
1401:
1402: BIS_COLLECTION_UTILITIES.put_line('Insert new data into temporary regular tables.');
1403: l_segment_num := 100;
1404: l_stmt := 'INSERT /*+ append ';
1405: IF l_baseline_ids.count <> 0 THEN
1406: FOR i IN l_baseline_ids.FIRST..l_baseline_ids.LAST LOOP

Line 1503: BIS_COLLECTION_UTILITIES.put_line('Fail to insert new data into temporary regular table.');

1499: EXECUTE IMMEDIATE l_stmt USING g_sysdate, g_last_updated_by, g_sysdate, g_last_updated_by, g_last_update_login, g_sysdate;
1500: null;
1501: EXCEPTION
1502: WHEN others THEN
1503: BIS_COLLECTION_UTILITIES.put_line('Fail to insert new data into temporary regular table.');
1504: retcode := g_error;
1505: errbuf := SQLERRM;
1506: RAISE load_exception;
1507: END;

Line 1513: BIS_COLLECTION_UTILITIES.put_line('Add new partitions if necessary.');

1509:
1510: /*commit explicitly for direct load*/
1511: commit;
1512:
1513: BIS_COLLECTION_UTILITIES.put_line('Add new partitions if necessary.');
1514: l_segment_num := 110;
1515: IF l_add_partitions.count <> 0 THEN
1516: FOR i IN l_add_partitions.FIRST..l_add_partitions.LAST LOOP
1517: BEGIN

Line 1519: BIS_COLLECTION_UTILITIES.put_line('...'|| l_add_partitions(i));

1515: IF l_add_partitions.count <> 0 THEN
1516: FOR i IN l_add_partitions.FIRST..l_add_partitions.LAST LOOP
1517: BEGIN
1518: IF l_collected_dates(i) IS NULL THEN
1519: BIS_COLLECTION_UTILITIES.put_line('...'|| l_add_partitions(i));
1520: EXECUTE IMMEDIATE l_add_partitions(i);
1521: END IF;
1522: EXCEPTION
1523: WHEN partition_exist_exception or value_exist_exception THEN

Line 1524: BIS_COLLECTION_UTILITIES.put_line('Partition exists already for baseline ' || l_strings(i) || '. No action.');

1520: EXECUTE IMMEDIATE l_add_partitions(i);
1521: END IF;
1522: EXCEPTION
1523: WHEN partition_exist_exception or value_exist_exception THEN
1524: BIS_COLLECTION_UTILITIES.put_line('Partition exists already for baseline ' || l_strings(i) || '. No action.');
1525: WHEN others THEN
1526: BIS_COLLECTION_UTILITIES.put_line('Fail to add partition to baseline staging table for baseline ' || l_strings(i) || '.');
1527: retcode := g_error;
1528: errbuf := SQLERRM;

Line 1526: BIS_COLLECTION_UTILITIES.put_line('Fail to add partition to baseline staging table for baseline ' || l_strings(i) || '.');

1522: EXCEPTION
1523: WHEN partition_exist_exception or value_exist_exception THEN
1524: BIS_COLLECTION_UTILITIES.put_line('Partition exists already for baseline ' || l_strings(i) || '. No action.');
1525: WHEN others THEN
1526: BIS_COLLECTION_UTILITIES.put_line('Fail to add partition to baseline staging table for baseline ' || l_strings(i) || '.');
1527: retcode := g_error;
1528: errbuf := SQLERRM;
1529: RAISE load_exception;
1530: END;

Line 1534: BIS_COLLECTION_UTILITIES.put_line('Swap partitions with corresponding temporary regular tables.');

1530: END;
1531: END LOOP;
1532: END IF;
1533:
1534: BIS_COLLECTION_UTILITIES.put_line('Swap partitions with corresponding temporary regular tables.');
1535: l_segment_num := 120;
1536: IF l_swap_partitions.count <> 0 THEN
1537: FOR i IN l_swap_partitions.FIRST..l_swap_partitions.LAST LOOP
1538: BEGIN

Line 1539: BIS_COLLECTION_UTILITIES.put_line('...'|| l_swap_partitions(i));

1535: l_segment_num := 120;
1536: IF l_swap_partitions.count <> 0 THEN
1537: FOR i IN l_swap_partitions.FIRST..l_swap_partitions.LAST LOOP
1538: BEGIN
1539: BIS_COLLECTION_UTILITIES.put_line('...'|| l_swap_partitions(i));
1540: EXECUTE IMMEDIATE l_swap_partitions(i);
1541: EXCEPTION
1542: WHEN others THEN
1543: BIS_COLLECTION_UTILITIES.put_line('Fail to exchange partition for baseline ' || l_strings(i) || '.');

Line 1543: BIS_COLLECTION_UTILITIES.put_line('Fail to exchange partition for baseline ' || l_strings(i) || '.');

1539: BIS_COLLECTION_UTILITIES.put_line('...'|| l_swap_partitions(i));
1540: EXECUTE IMMEDIATE l_swap_partitions(i);
1541: EXCEPTION
1542: WHEN others THEN
1543: BIS_COLLECTION_UTILITIES.put_line('Fail to exchange partition for baseline ' || l_strings(i) || '.');
1544: retcode := g_error;
1545: errbuf := SQLERRM;
1546: RAISE load_exception;
1547: END;

Line 1551: BIS_COLLECTION_UTILITIES.put_line('Drop temporary regular tables.');

1547: END;
1548: END LOOP;
1549: END IF;
1550:
1551: BIS_COLLECTION_UTILITIES.put_line('Drop temporary regular tables.');
1552: l_segment_num := 130;
1553: IF l_drop_tables.count <> 0 THEN
1554: FOR i IN l_drop_tables.FIRST..l_drop_tables.LAST LOOP
1555: BEGIN

Line 1556: BIS_COLLECTION_UTILITIES.put_line('...'|| l_drop_tables(i));

1552: l_segment_num := 130;
1553: IF l_drop_tables.count <> 0 THEN
1554: FOR i IN l_drop_tables.FIRST..l_drop_tables.LAST LOOP
1555: BEGIN
1556: BIS_COLLECTION_UTILITIES.put_line('...'|| l_drop_tables(i));
1557: EXECUTE IMMEDIATE l_drop_tables(i);
1558: EXCEPTION
1559: WHEN others THEN
1560: retcode := g_error;

Line 1580: BIS_COLLECTION_UTILITIES.put_line('Fail to determine if there is need to cleanup/archive data.');

1576: and sched.baseline_id = def.baseline_id
1577: ;
1578: EXCEPTION
1579: WHEN others THEN
1580: BIS_COLLECTION_UTILITIES.put_line('Fail to determine if there is need to cleanup/archive data.');
1581: retcode := g_error;
1582: errbuf := SQLERRM;
1583: RAISE archive_cleanup_exception;
1584: END;

Line 1588: BIS_COLLECTION_UTILITIES.put_line('Clean up data in baseline fact table.');

1584: END;
1585:
1586: l_segment_num := 140;
1587: IF l_delete > 0 THEN
1588: BIS_COLLECTION_UTILITIES.put_line('Clean up data in baseline fact table.');
1589: BEGIN
1590: delete from OPI_DBI_PTP_PLAN_F
1591: where
1592: rowid in

Line 1603: BIS_COLLECTION_UTILITIES.put_line('Fail to delete old data from baseline fact table.');

1599: and f.transaction_date >= sched.from_date
1600: );
1601: EXCEPTION
1602: WHEN others THEN
1603: BIS_COLLECTION_UTILITIES.put_line('Fail to delete old data from baseline fact table.');
1604: retcode := g_error;
1605: errbuf := SQLERRM;
1606: RAISE archive_cleanup_exception;
1607: END;

Line 1612: BIS_COLLECTION_UTILITIES.put_line('Archive data into baseline fact table.');

1608: END IF;
1609:
1610: l_segment_num := 150;
1611: IF l_archive > 0 THEN
1612: BIS_COLLECTION_UTILITIES.put_line('Archive data into baseline fact table.');
1613: BEGIN
1614: insert /*+ append parallel(OPI_DBI_PTP_PLAN_F) */
1615: into OPI_DBI_PTP_PLAN_F
1616: (

Line 1660: BIS_COLLECTION_UTILITIES.put_line('Fail to archive data into baseline fact table.');

1656: --only new rows inserted being reported to wrapup procedure
1657: l_count := SQL%ROWCOUNT;
1658: EXCEPTION
1659: WHEN others THEN
1660: BIS_COLLECTION_UTILITIES.put_line('Fail to archive data into baseline fact table.');
1661: retcode := g_error;
1662: errbuf := SQLERRM;
1663: RAISE archive_cleanup_exception;
1664: END;

Line 1669: BIS_COLLECTION_UTILITIES.put_line('Collect cost/conversion rate information.');

1665: END IF;
1666:
1667: --put call to get cost/conv collection here
1668: l_segment_num := 160;
1669: BIS_COLLECTION_UTILITIES.put_line('Collect cost/conversion rate information.');
1670: Get_Discrete_Cost_and_Rate(errbuf, retcode);
1671: Get_Process_Cost(errbuf, retcode);
1672:
1673: --Archive collection history

Line 1675: BIS_COLLECTION_UTILITIES.put_line('Archive collection history into log table, update baseline setup tables.');

1671: Get_Process_Cost(errbuf, retcode);
1672:
1673: --Archive collection history
1674:
1675: BIS_COLLECTION_UTILITIES.put_line('Archive collection history into log table, update baseline setup tables.');
1676: BEGIN
1677: l_segment_num := 170;
1678: insert into OPI_DBI_PTP_LOG
1679: (

Line 1839: BIS_COLLECTION_UTILITIES.put_line('synchronize OPI_PTP_SUM_STG_MV with OPI_DBI_PTP_PLAN_STG.');

1835: --explict commit
1836: COMMIT;
1837:
1838: --synchronize OPI_PTP_SUM_STG_MV with OPI_DBI_PTP_PLAN_STG
1839: BIS_COLLECTION_UTILITIES.put_line('synchronize OPI_PTP_SUM_STG_MV with OPI_DBI_PTP_PLAN_STG.');
1840: l_segment_num := 180;
1841: --analyze table first per performance team's advice
1842: FND_STATS.GATHER_TABLE_STATS(errbuf,retcode,'OPI','OPI_DBI_PTP_PLAN_STG');
1843: BEGIN

Line 1857: BIS_COLLECTION_UTILITIES.put_line('Fail to refresh materialized view OPI_PTP_SUM_STG_MV. Please fix the problem and re-run the concurrent request set.');

1853: update opi_dbi_run_log_curr
1854: set stop_reason_code = 1
1855: where etl_id = 7 and source = 1;
1856: commit;
1857: BIS_COLLECTION_UTILITIES.put_line('Fail to refresh materialized view OPI_PTP_SUM_STG_MV. Please fix the problem and re-run the concurrent request set.');
1858: RAISE mv_refresh_exception;
1859: END;
1860:
1861: BIS_COLLECTION_UTILITIES.put_line('Successfully collect baseline data on ' || TO_CHAR(g_sysdate, 'DD-MON-YYYY') || '.');

Line 1861: BIS_COLLECTION_UTILITIES.put_line('Successfully collect baseline data on ' || TO_CHAR(g_sysdate, 'DD-MON-YYYY') || '.');

1857: BIS_COLLECTION_UTILITIES.put_line('Fail to refresh materialized view OPI_PTP_SUM_STG_MV. Please fix the problem and re-run the concurrent request set.');
1858: RAISE mv_refresh_exception;
1859: END;
1860:
1861: BIS_COLLECTION_UTILITIES.put_line('Successfully collect baseline data on ' || TO_CHAR(g_sysdate, 'DD-MON-YYYY') || '.');
1862: BIS_COLLECTION_UTILITIES.WRAPUP(
1863: p_status => TRUE,
1864: p_count => l_count,
1865: p_message => 'Successfully collect baseline data on ' || TO_CHAR(g_sysdate, 'DD-MON-YYYY') || '.'

Line 1862: BIS_COLLECTION_UTILITIES.WRAPUP(

1858: RAISE mv_refresh_exception;
1859: END;
1860:
1861: BIS_COLLECTION_UTILITIES.put_line('Successfully collect baseline data on ' || TO_CHAR(g_sysdate, 'DD-MON-YYYY') || '.');
1862: BIS_COLLECTION_UTILITIES.WRAPUP(
1863: p_status => TRUE,
1864: p_count => l_count,
1865: p_message => 'Successfully collect baseline data on ' || TO_CHAR(g_sysdate, 'DD-MON-YYYY') || '.'
1866: );

Line 1873: BIS_COLLECTION_UTILITIES.put_line('Error out at segment ' || l_segment_num || '.');

1869: return;
1870: EXCEPTION
1871: WHEN intialization_exception or collection_parameter_exception or isc_collection_exception THEN
1872: ROLLBACK;
1873: BIS_COLLECTION_UTILITIES.put_line('Error out at segment ' || l_segment_num || '.');
1874: BIS_COLLECTION_UTILITIES.put_line(errbuf);
1875: BIS_COLLECTION_UTILITIES.WRAPUP(
1876: p_status => FALSE,
1877: p_message => 'Failed to collect baseline data.'

Line 1874: BIS_COLLECTION_UTILITIES.put_line(errbuf);

1870: EXCEPTION
1871: WHEN intialization_exception or collection_parameter_exception or isc_collection_exception THEN
1872: ROLLBACK;
1873: BIS_COLLECTION_UTILITIES.put_line('Error out at segment ' || l_segment_num || '.');
1874: BIS_COLLECTION_UTILITIES.put_line(errbuf);
1875: BIS_COLLECTION_UTILITIES.WRAPUP(
1876: p_status => FALSE,
1877: p_message => 'Failed to collect baseline data.'
1878: );

Line 1875: BIS_COLLECTION_UTILITIES.WRAPUP(

1871: WHEN intialization_exception or collection_parameter_exception or isc_collection_exception THEN
1872: ROLLBACK;
1873: BIS_COLLECTION_UTILITIES.put_line('Error out at segment ' || l_segment_num || '.');
1874: BIS_COLLECTION_UTILITIES.put_line(errbuf);
1875: BIS_COLLECTION_UTILITIES.WRAPUP(
1876: p_status => FALSE,
1877: p_message => 'Failed to collect baseline data.'
1878: );
1879: WHEN load_exception or archive_cleanup_exception or update_log_exception or cost_conversion_rate_exception THEN

Line 1881: BIS_COLLECTION_UTILITIES.put_line('Error out at segment ' || l_segment_num || '.');

1877: p_message => 'Failed to collect baseline data.'
1878: );
1879: WHEN load_exception or archive_cleanup_exception or update_log_exception or cost_conversion_rate_exception THEN
1880: ROLLBACK;
1881: BIS_COLLECTION_UTILITIES.put_line('Error out at segment ' || l_segment_num || '.');
1882: BIS_COLLECTION_UTILITIES.put_line(errbuf);
1883: IF l_segment_num >= 90 THEN
1884: IF l_drop_tables.count <> 0 THEN
1885: FOR i IN l_drop_tables.FIRST..l_drop_tables.LAST LOOP

Line 1882: BIS_COLLECTION_UTILITIES.put_line(errbuf);

1878: );
1879: WHEN load_exception or archive_cleanup_exception or update_log_exception or cost_conversion_rate_exception THEN
1880: ROLLBACK;
1881: BIS_COLLECTION_UTILITIES.put_line('Error out at segment ' || l_segment_num || '.');
1882: BIS_COLLECTION_UTILITIES.put_line(errbuf);
1883: IF l_segment_num >= 90 THEN
1884: IF l_drop_tables.count <> 0 THEN
1885: FOR i IN l_drop_tables.FIRST..l_drop_tables.LAST LOOP
1886: BIS_COLLECTION_UTILITIES.put_line('...' || l_drop_tables(i));

Line 1886: BIS_COLLECTION_UTILITIES.put_line('...' || l_drop_tables(i));

1882: BIS_COLLECTION_UTILITIES.put_line(errbuf);
1883: IF l_segment_num >= 90 THEN
1884: IF l_drop_tables.count <> 0 THEN
1885: FOR i IN l_drop_tables.FIRST..l_drop_tables.LAST LOOP
1886: BIS_COLLECTION_UTILITIES.put_line('...' || l_drop_tables(i));
1887: BEGIN
1888: EXECUTE IMMEDIATE l_drop_tables(i);
1889: EXCEPTION
1890: --if data has been dropped, ignore the other

Line 1897: BIS_COLLECTION_UTILITIES.WRAPUP(

1893: END;
1894: END LOOP;
1895: END IF;
1896: END IF;
1897: BIS_COLLECTION_UTILITIES.WRAPUP(
1898: p_status => FALSE,
1899: p_message => 'Failed to collect baseline data.'
1900: );
1901: WHEN others THEN

Line 1903: BIS_COLLECTION_UTILITIES.put_line('Error out at segment ' || l_segment_num || '.');

1899: p_message => 'Failed to collect baseline data.'
1900: );
1901: WHEN others THEN
1902: ROLLBACK;
1903: BIS_COLLECTION_UTILITIES.put_line('Error out at segment ' || l_segment_num || '.');
1904: retcode := g_error;
1905: errbuf := SQLERRM;
1906: BIS_COLLECTION_UTILITIES.put_line(errbuf);
1907: BIS_COLLECTION_UTILITIES.WRAPUP(

Line 1906: BIS_COLLECTION_UTILITIES.put_line(errbuf);

1902: ROLLBACK;
1903: BIS_COLLECTION_UTILITIES.put_line('Error out at segment ' || l_segment_num || '.');
1904: retcode := g_error;
1905: errbuf := SQLERRM;
1906: BIS_COLLECTION_UTILITIES.put_line(errbuf);
1907: BIS_COLLECTION_UTILITIES.WRAPUP(
1908: p_status => FALSE,
1909: p_message => 'Failed to collect baseline data.'
1910: );

Line 1907: BIS_COLLECTION_UTILITIES.WRAPUP(

1903: BIS_COLLECTION_UTILITIES.put_line('Error out at segment ' || l_segment_num || '.');
1904: retcode := g_error;
1905: errbuf := SQLERRM;
1906: BIS_COLLECTION_UTILITIES.put_line(errbuf);
1907: BIS_COLLECTION_UTILITIES.WRAPUP(
1908: p_status => FALSE,
1909: p_message => 'Failed to collect baseline data.'
1910: );
1911: END Extract_Baseline;

Line 1976: BIS_COLLECTION_UTILITIES.PUT_LINE('Starting Materialized Views Refresh for Production to Plan...');

1972: BEGIN
1973:
1974: l_stmt_num := 10;
1975: g_degree := bis_common_parameters.get_degree_of_parallelism;
1976: BIS_COLLECTION_UTILITIES.PUT_LINE('Starting Materialized Views Refresh for Production to Plan...');
1977:
1978: l_stmt_num := 20;
1979: REFRESH_CBN_MV(errbuf, retcode);
1980: BIS_COLLECTION_UTILITIES.PUT_LINE('Refresh OPI_PTP_CBN_MV finished ...');

Line 1980: BIS_COLLECTION_UTILITIES.PUT_LINE('Refresh OPI_PTP_CBN_MV finished ...');

1976: BIS_COLLECTION_UTILITIES.PUT_LINE('Starting Materialized Views Refresh for Production to Plan...');
1977:
1978: l_stmt_num := 20;
1979: REFRESH_CBN_MV(errbuf, retcode);
1980: BIS_COLLECTION_UTILITIES.PUT_LINE('Refresh OPI_PTP_CBN_MV finished ...');
1981:
1982: l_stmt_num := 30;
1983: REFRESH_ITEM_F_MV(errbuf, retcode);
1984: BIS_COLLECTION_UTILITIES.PUT_LINE('Refresh OPI_PTP_ITEM_F_MV finished ...');

Line 1984: BIS_COLLECTION_UTILITIES.PUT_LINE('Refresh OPI_PTP_ITEM_F_MV finished ...');

1980: BIS_COLLECTION_UTILITIES.PUT_LINE('Refresh OPI_PTP_CBN_MV finished ...');
1981:
1982: l_stmt_num := 30;
1983: REFRESH_ITEM_F_MV(errbuf, retcode);
1984: BIS_COLLECTION_UTILITIES.PUT_LINE('Refresh OPI_PTP_ITEM_F_MV finished ...');
1985:
1986: l_stmt_num := 40;
1987: REFRESH_SUM_F_MV(errbuf, retcode);
1988: BIS_COLLECTION_UTILITIES.PUT_LINE('Refresh OPI_PTP_SUM_F_MV finished ...');

Line 1988: BIS_COLLECTION_UTILITIES.PUT_LINE('Refresh OPI_PTP_SUM_F_MV finished ...');

1984: BIS_COLLECTION_UTILITIES.PUT_LINE('Refresh OPI_PTP_ITEM_F_MV finished ...');
1985:
1986: l_stmt_num := 40;
1987: REFRESH_SUM_F_MV(errbuf, retcode);
1988: BIS_COLLECTION_UTILITIES.PUT_LINE('Refresh OPI_PTP_SUM_F_MV finished ...');
1989:
1990: l_stmt_num := 50;
1991: REFRESH_SUM_STG_MV(errbuf, retcode);
1992: BIS_COLLECTION_UTILITIES.PUT_LINE('Refresh OPI_PTP_SUM_STG_MV finished ...');

Line 1992: BIS_COLLECTION_UTILITIES.PUT_LINE('Refresh OPI_PTP_SUM_STG_MV finished ...');

1988: BIS_COLLECTION_UTILITIES.PUT_LINE('Refresh OPI_PTP_SUM_F_MV finished ...');
1989:
1990: l_stmt_num := 50;
1991: REFRESH_SUM_STG_MV(errbuf, retcode);
1992: BIS_COLLECTION_UTILITIES.PUT_LINE('Refresh OPI_PTP_SUM_STG_MV finished ...');
1993:
1994: l_stmt_num := 60;
1995: REFRESH_RPT_BND_MV(errbuf, retcode);
1996: BIS_COLLECTION_UTILITIES.PUT_LINE('Refresh OPI_PTP_RPT_BND_MV finished ...');

Line 1996: BIS_COLLECTION_UTILITIES.PUT_LINE('Refresh OPI_PTP_RPT_BND_MV finished ...');

1992: BIS_COLLECTION_UTILITIES.PUT_LINE('Refresh OPI_PTP_SUM_STG_MV finished ...');
1993:
1994: l_stmt_num := 60;
1995: REFRESH_RPT_BND_MV(errbuf, retcode);
1996: BIS_COLLECTION_UTILITIES.PUT_LINE('Refresh OPI_PTP_RPT_BND_MV finished ...');
1997: retcode := 0;
1998:
1999: EXCEPTION
2000: WHEN OTHERS THEN

Line 2004: BIS_COLLECTION_UTILITIES.PUT_LINE('OPI_DBI_PTP_REFRESH_PKG.REFRESH - Error at statement ('

2000: WHEN OTHERS THEN
2001: retcode := SQLCODE;
2002: errbuf := SQLERRM;
2003:
2004: BIS_COLLECTION_UTILITIES.PUT_LINE('OPI_DBI_PTP_REFRESH_PKG.REFRESH - Error at statement ('
2005: || to_char(l_stmt_num)
2006: || ')');
2007:
2008: BIS_COLLECTION_UTILITIES.PUT_LINE('Error Number: ' || retcode);

Line 2008: BIS_COLLECTION_UTILITIES.PUT_LINE('Error Number: ' || retcode);

2004: BIS_COLLECTION_UTILITIES.PUT_LINE('OPI_DBI_PTP_REFRESH_PKG.REFRESH - Error at statement ('
2005: || to_char(l_stmt_num)
2006: || ')');
2007:
2008: BIS_COLLECTION_UTILITIES.PUT_LINE('Error Number: ' || retcode);
2009: BIS_COLLECTION_UTILITIES.PUT_LINE('Error Message: ' || errbuf);
2010: END REFRESH;
2011:
2012: END OPI_DBI_PTP_BASELINE_PKG;

Line 2009: BIS_COLLECTION_UTILITIES.PUT_LINE('Error Message: ' || errbuf);

2005: || to_char(l_stmt_num)
2006: || ')');
2007:
2008: BIS_COLLECTION_UTILITIES.PUT_LINE('Error Number: ' || retcode);
2009: BIS_COLLECTION_UTILITIES.PUT_LINE('Error Message: ' || errbuf);
2010: END REFRESH;
2011:
2012: END OPI_DBI_PTP_BASELINE_PKG;