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.MSD_DEM_SOP dependencies on MSD_DEM_COMMON_UTILITIES

Line 33: msd_dem_common_utilities.get_lookup_value ('MSD_DEM_TABLES', 'SUPPLY_PLAN') ||

29: x_plan_id NUMBER := NULL;
30: BEGIN
31:
32: EXECUTE IMMEDIATE 'SELECT plan_id FROM ' ||
33: msd_dem_common_utilities.get_lookup_value ('MSD_DEM_TABLES', 'SUPPLY_PLAN') ||
34: ' WHERE supply_plan_id = ' || p_member_id
35: INTO x_plan_id;
36:
37: RETURN x_plan_id;

Line 58: msd_dem_common_utilities.get_lookup_value ('MSD_DEM_TABLES', 'SUPPLY_PLAN') ||

54: x_plan_type NUMBER := NULL;
55: BEGIN
56:
57: EXECUTE IMMEDIATE 'SELECT plan_type FROM ' ||
58: msd_dem_common_utilities.get_lookup_value ('MSD_DEM_TABLES', 'SUPPLY_PLAN') ||
59: ' WHERE supply_plan_id = ' || p_member_id
60: INTO x_plan_type;
61:
62: RETURN x_plan_type;

Line 628: l_profile_id1 := to_number(msd_dem_common_utilities.get_app_id_text('MSD_DEM_DEMANTRA_OBJECT_ID',

624:
625: /* Setting start and end dates for Purge Plan Data data profile */
626:
627: /* Bug#8224935 - APP ID */ -- nallkuma
628: l_profile_id1 := to_number(msd_dem_common_utilities.get_app_id_text('MSD_DEM_DEMANTRA_OBJECT_ID',
629: 'PROFILE_PURGE_PLAN_DATA',
630: 1,
631: 'id'));
632:

Line 638: msd_dem_common_utilities.log_debug ('Calling API_NOTIFY_APS_INTEGRATION - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));

634: execute immediate l_sql;
635:
636:
637: /* Calling API to notify the application server to refresh its engine */
638: msd_dem_common_utilities.log_debug ('Calling API_NOTIFY_APS_INTEGRATION - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
639:
640: l_sql := 'begin ' || g_schema|| '.API_NOTIFY_APS_INTEGRATION('||l_profile_id1 ||'); end;';
641: execute immediate l_sql;
642:

Line 646: l_profile_id2 := to_number(msd_dem_common_utilities.get_app_id_text('MSD_DEM_DEMANTRA_OBJECT_ID',

642:
643: /* Setting start and end dates for Purge Resource Data data profile*/
644:
645: /* Bug#8224935 - APP ID */ -- nallkuma
646: l_profile_id2 := to_number(msd_dem_common_utilities.get_app_id_text('MSD_DEM_DEMANTRA_OBJECT_ID',
647: 'PROFILE_PURGE_RESOURCE_DATA',
648: 1,
649: 'id'));
650:

Line 656: msd_dem_common_utilities.log_debug ('Calling API_NOTIFY_APS_INTEGRATION - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));

652: execute immediate l_sql;
653:
654:
655: /* Calling API to notify the application server to refresh its engine */
656: msd_dem_common_utilities.log_debug ('Calling API_NOTIFY_APS_INTEGRATION - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
657:
658: l_sql := 'begin ' || g_schema|| '.API_NOTIFY_APS_INTEGRATION('||l_profile_id2 ||'); end;';
659: execute immediate l_sql;
660: -- syenamar

Line 662: msd_dem_common_utilities.log_message('Demantra Schema not set');

658: l_sql := 'begin ' || g_schema|| '.API_NOTIFY_APS_INTEGRATION('||l_profile_id2 ||'); end;';
659: execute immediate l_sql;
660: -- syenamar
661: else
662: msd_dem_common_utilities.log_message('Demantra Schema not set');
663: end if;
664:
665:
666:

Line 712: x_dm_time_bucket := msd_dem_common_utilities.dm_time_level;

708: BEGIN
709: log_debug ('Entering: msd_dem_sop.load_ascp_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
710:
711: /* Get the lowest time bucket */
712: x_dm_time_bucket := msd_dem_common_utilities.dm_time_level;
713: IF (x_dm_time_bucket IS NULL)
714: THEN
715: retcode := -1;
716: errbuf := 'Unable to get lowest time bucket';

Line 890: x_dm_table := msd_dem_common_utilities.get_lookup_value ('MSD_DEM_TABLES', 'DM_WIZ_DM_DEF');

886:
887: log_debug ('Deleting time data from msd_dem_day_dates');
888: EXECUTE IMMEDIATE 'DELETE FROM msd_dem_day_dates';
889:
890: x_dm_table := msd_dem_common_utilities.get_lookup_value ('MSD_DEM_TABLES', 'DM_WIZ_DM_DEF');
891:
892: /* Get the time level info for the active data model */
893: x_sql := 'SELECT time_bucket, first_day_of_week, aggregation_method ' ||
894: ' FROM ' || x_dm_table ||

Line 932: x_source_time_table := msd_dem_common_utilities.get_lookup_value ('MSD_DEM_TABLES', 'INPUTS');

928: END IF;
929:
930: x_end_date := 'trunc(' || x_end_date || ') + 86399/86400 end_date ';
931:
932: x_source_time_table := msd_dem_common_utilities.get_lookup_value ('MSD_DEM_TABLES', 'INPUTS');
933:
934: log_debug ('Inserting time data into msd_dem_dates');
935: x_sql := 'INSERT INTO msd_dem_dates' ||
936: ' (datet, num_of_days, start_date, end_date, last_update_date, last_updated_by, creation_date, created_by, last_update_login) ' ||

Line 1088: ||''''|| msd_dem_common_utilities.get_app_id_text('MSD_DEM_DEMANTRA_OBJECT_ID',

1084: values ('''
1085: ||x_plan_code ||''','
1086: ||''''||p_plan_start_date ||''','
1087: ||''''||sysdate ||''','
1088: ||''''|| msd_dem_common_utilities.get_app_id_text('MSD_DEM_DEMANTRA_OBJECT_ID',
1089: 'LEVEL_ITEM',
1090: 1,
1091: 'table_label') ||''','
1092: ||''||'2' ||','

Line 1106: ||''''|| msd_dem_common_utilities.get_app_id_text('MSD_DEM_DEMANTRA_OBJECT_ID',

1102: values ('''
1103: ||x_plan_code ||''','
1104: ||''''||p_plan_start_date ||''','
1105: ||''''||sysdate ||''','
1106: ||''''|| msd_dem_common_utilities.get_app_id_text('MSD_DEM_DEMANTRA_OBJECT_ID',
1107: 'LEVEL_DEMAND_CLASS',
1108: 1,
1109: 'table_label') ||''','
1110: ||''||'2' ||','

Line 1124: ||''''|| msd_dem_common_utilities.get_app_id_text('MSD_DEM_DEMANTRA_OBJECT_ID',

1120: values ('''
1121: ||x_plan_code ||''','
1122: ||''''||p_plan_start_date ||''','
1123: ||''''||sysdate ||''','
1124: ||''''|| msd_dem_common_utilities.get_app_id_text('MSD_DEM_DEMANTRA_OBJECT_ID',
1125: 'LEVEL_ORGANIZATION',
1126: 1,
1127: 'table_label') ||''','
1128: ||''||'1' ||','

Line 1142: ||''''|| msd_dem_common_utilities.get_app_id_text('MSD_DEM_DEMANTRA_OBJECT_ID',

1138: values ('''
1139: ||x_plan_code ||''','
1140: ||''''||p_plan_start_date ||''','
1141: ||''''||sysdate ||''','
1142: ||''''|| msd_dem_common_utilities.get_app_id_text('MSD_DEM_DEMANTRA_OBJECT_ID',
1143: 'LEVEL_SITE',
1144: 1,
1145: 'table_label') ||''','
1146: ||''||'1' ||','

Line 1160: ||''''|| msd_dem_common_utilities.get_app_id_text('MSD_DEM_DEMANTRA_OBJECT_ID',

1156: values ('''
1157: ||x_plan_code ||''','
1158: ||''''||p_plan_start_date ||''','
1159: ||''''||sysdate ||''','
1160: ||''''|| msd_dem_common_utilities.get_app_id_text('MSD_DEM_DEMANTRA_OBJECT_ID',
1161: 'LEVEL_SALES_CHANNEL',
1162: 1,
1163: 'table_label') ||''','
1164: ||''||'1' ||','

Line 1236: x_dem_version := to_number(msd_dem_common_utilities.get_demantra_version);

1232: where sop_enabled = 1 ';
1233:
1234: -- BUG#9211268 syenamar
1235: --Enable download of RP plans into demantra (7.3 only)
1236: x_dem_version := to_number(msd_dem_common_utilities.get_demantra_version);
1237:
1238: IF (x_dem_version < 7.3) THEN
1239: l_stmt := l_stmt || ' and plan_type not in (101, 102, 103, 105)';
1240: END IF;

Line 1350: l_end_date := msd_dem_common_utilities.get_sno_plan_cutoff_date (p_plan_id);

1346:
1347: if l_plan_type = 6 then
1348:
1349: /* Get the end date for the SNO plan */
1350: l_end_date := msd_dem_common_utilities.get_sno_plan_cutoff_date (p_plan_id);
1351:
1352: msd_dem_query_utilities.get_query(l_retcode, l_stmt, 'MSD_DEM_PLANS', l_instance_id);
1353:
1354: if l_stmt is null then

Line 1438: l_stmt := replace(l_stmt, 'C_RESOURCE_TABLE', msd_dem_common_utilities.get_lookup_value('MSD_DEM_TABLES', 'BIIO_RESOURCES'));

1434: if l_stmt is null then
1435: return;
1436: end if;
1437:
1438: l_stmt := replace(l_stmt, 'C_RESOURCE_TABLE', msd_dem_common_utilities.get_lookup_value('MSD_DEM_TABLES', 'BIIO_RESOURCES'));
1439: l_stmt := replace(l_stmt, 'C_PLAN_NAME', '''' || l_plan_name || '''');
1440: l_stmt := replace(l_stmt, 'C_PLAN_ID', p_plan_id);
1441: l_stmt := replace(l_stmt, 'C_SCHEMANAME', fnd_profile.value('MSD_DEM_SCHEMA'));
1442:

Line 1460: l_end_date := msd_dem_common_utilities.get_sno_plan_cutoff_date (p_plan_id);

1456:
1457: if l_plan_type = 6 then
1458:
1459: /* Get the end date for the SNO plan */
1460: l_end_date := msd_dem_common_utilities.get_sno_plan_cutoff_date (p_plan_id);
1461:
1462: msd_dem_query_utilities.get_query(l_retcode, l_stmt, 'MSD_DEM_SCENARIO_RESOURCES_SNO', l_instance_id);
1463:
1464: if l_stmt is null then

Line 1468: l_stmt := replace(l_stmt, 'C_TARGET_TABLE', msd_dem_common_utilities.get_lookup_value('MSD_DEM_TABLES', 'BIIO_RESOURCES'));

1464: if l_stmt is null then
1465: return;
1466: end if;
1467:
1468: l_stmt := replace(l_stmt, 'C_TARGET_TABLE', msd_dem_common_utilities.get_lookup_value('MSD_DEM_TABLES', 'BIIO_RESOURCES'));
1469: l_stmt := replace(l_stmt, 'C_SRC_TABLE', msd_dem_common_utilities.get_lookup_value('MSD_DEM_TABLES', 'BIIO_RESOURCE_CAPACITY'));
1470: l_stmt := replace(l_stmt, 'C_SCHEMANAME', fnd_profile.value('MSD_DEM_SCHEMA'));
1471:
1472: execute immediate l_stmt;

Line 1469: l_stmt := replace(l_stmt, 'C_SRC_TABLE', msd_dem_common_utilities.get_lookup_value('MSD_DEM_TABLES', 'BIIO_RESOURCE_CAPACITY'));

1465: return;
1466: end if;
1467:
1468: l_stmt := replace(l_stmt, 'C_TARGET_TABLE', msd_dem_common_utilities.get_lookup_value('MSD_DEM_TABLES', 'BIIO_RESOURCES'));
1469: l_stmt := replace(l_stmt, 'C_SRC_TABLE', msd_dem_common_utilities.get_lookup_value('MSD_DEM_TABLES', 'BIIO_RESOURCE_CAPACITY'));
1470: l_stmt := replace(l_stmt, 'C_SCHEMANAME', fnd_profile.value('MSD_DEM_SCHEMA'));
1471:
1472: execute immediate l_stmt;
1473:

Line 1480: l_stmt := replace(l_stmt, 'C_TARGET_TABLE', msd_dem_common_utilities.get_lookup_value('MSD_DEM_TABLES', 'BIIO_RESOURCES'));

1476: if l_stmt is null then
1477: return;
1478: end if;
1479:
1480: l_stmt := replace(l_stmt, 'C_TARGET_TABLE', msd_dem_common_utilities.get_lookup_value('MSD_DEM_TABLES', 'BIIO_RESOURCES'));
1481: l_stmt := replace(l_stmt, 'C_SRC_TABLE', msd_dem_common_utilities.get_lookup_value('MSD_DEM_TABLES', 'BIIO_RESOURCE_CAPACITY'));
1482: l_stmt := replace(l_stmt, 'C_SCHEMANAME', fnd_profile.value('MSD_DEM_SCHEMA'));
1483:
1484: execute immediate l_stmt;

Line 1481: l_stmt := replace(l_stmt, 'C_SRC_TABLE', msd_dem_common_utilities.get_lookup_value('MSD_DEM_TABLES', 'BIIO_RESOURCE_CAPACITY'));

1477: return;
1478: end if;
1479:
1480: l_stmt := replace(l_stmt, 'C_TARGET_TABLE', msd_dem_common_utilities.get_lookup_value('MSD_DEM_TABLES', 'BIIO_RESOURCES'));
1481: l_stmt := replace(l_stmt, 'C_SRC_TABLE', msd_dem_common_utilities.get_lookup_value('MSD_DEM_TABLES', 'BIIO_RESOURCE_CAPACITY'));
1482: l_stmt := replace(l_stmt, 'C_SCHEMANAME', fnd_profile.value('MSD_DEM_SCHEMA'));
1483:
1484: execute immediate l_stmt;
1485:

Line 1492: l_stmt := replace(l_stmt, 'C_TARGET_TABLE', msd_dem_common_utilities.get_lookup_value('MSD_DEM_TABLES', 'BIIO_RESOURCES'));

1488: if l_stmt is null then
1489: return;
1490: end if;
1491:
1492: l_stmt := replace(l_stmt, 'C_TARGET_TABLE', msd_dem_common_utilities.get_lookup_value('MSD_DEM_TABLES', 'BIIO_RESOURCES'));
1493: l_stmt := replace(l_stmt, 'C_SRC_TABLE', msd_dem_common_utilities.get_lookup_value('MSD_DEM_TABLES', 'BIIO_RESOURCE_CAPACITY'));
1494: l_stmt := replace(l_stmt, 'C_SCHEMANAME', fnd_profile.value('MSD_DEM_SCHEMA'));
1495: l_stmt := replace(l_stmt, 'C_PLAN_START_DATE', 'to_date(''' || to_char(l_start_date, 'dd-mm-yyyy') || ''',''dd-mm-yyyy'')');
1496: l_stmt := replace(l_stmt, 'C_PLAN_END_DATE', 'to_date(''' || to_char(l_end_date, 'dd-mm-yyyy') || ''',''dd-mm-yyyy'')');

Line 1493: l_stmt := replace(l_stmt, 'C_SRC_TABLE', msd_dem_common_utilities.get_lookup_value('MSD_DEM_TABLES', 'BIIO_RESOURCE_CAPACITY'));

1489: return;
1490: end if;
1491:
1492: l_stmt := replace(l_stmt, 'C_TARGET_TABLE', msd_dem_common_utilities.get_lookup_value('MSD_DEM_TABLES', 'BIIO_RESOURCES'));
1493: l_stmt := replace(l_stmt, 'C_SRC_TABLE', msd_dem_common_utilities.get_lookup_value('MSD_DEM_TABLES', 'BIIO_RESOURCE_CAPACITY'));
1494: l_stmt := replace(l_stmt, 'C_SCHEMANAME', fnd_profile.value('MSD_DEM_SCHEMA'));
1495: l_stmt := replace(l_stmt, 'C_PLAN_START_DATE', 'to_date(''' || to_char(l_start_date, 'dd-mm-yyyy') || ''',''dd-mm-yyyy'')');
1496: l_stmt := replace(l_stmt, 'C_PLAN_END_DATE', 'to_date(''' || to_char(l_end_date, 'dd-mm-yyyy') || ''',''dd-mm-yyyy'')');
1497:

Line 1952: x_dm_time_bucket := msd_dem_common_utilities.dm_time_level;

1948: RETURN;
1949: END IF;
1950:
1951: /* Get the lowest time bucket */
1952: x_dm_time_bucket := msd_dem_common_utilities.dm_time_level;
1953: IF (x_dm_time_bucket IS NULL) THEN
1954: retcode := -1;
1955: errbuf := 'Unable to get lowest time bucket';
1956: log_message('Error(2): msd_dem_sop.load_plan_data_wo - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS') || ' - ' || errbuf);

Line 2295: l_master_org := to_number(msd_dem_common_utilities.get_parameter_value (

2291: l_master_string := 'decode(itt.sr_instance_id';
2292: l_category_string := 'decode(item_master.sr_instance_id';
2293:
2294: for get_instances_rec in get_instances loop
2295: l_master_org := to_number(msd_dem_common_utilities.get_parameter_value (
2296: get_instances_rec.instance_id,
2297: 'MSD_DEM_MASTER_ORG'));
2298: l_category_set_id := to_number(msd_dem_common_utilities.get_parameter_value (
2299: get_instances_rec.instance_id,

Line 2298: l_category_set_id := to_number(msd_dem_common_utilities.get_parameter_value (

2294: for get_instances_rec in get_instances loop
2295: l_master_org := to_number(msd_dem_common_utilities.get_parameter_value (
2296: get_instances_rec.instance_id,
2297: 'MSD_DEM_MASTER_ORG'));
2298: l_category_set_id := to_number(msd_dem_common_utilities.get_parameter_value (
2299: get_instances_rec.instance_id,
2300: 'MSD_DEM_CATEGORY_SET_NAME'));
2301:
2302: l_master_string := l_master_string || ', ' || get_instances_rec.instance_id || ', ' || l_master_org ;

Line 2581: msd_dem_common_utilities.set_series_options(x_errbuf,x_retcode,g_schema,x_opd_dp,x_series_01,2,0);

2577:
2578: log_message ('INFO: Setting "No load" option and "No Purge" option for series under "other plan data" dp.' );
2579:
2580: -- set to "No load" option and "No Purge" option for the series in the data profile
2581: msd_dem_common_utilities.set_series_options(x_errbuf,x_retcode,g_schema,x_opd_dp,x_series_01,2,0);
2582: msd_dem_common_utilities.set_series_options(x_errbuf,x_retcode,g_schema,x_opd_dp,x_series_02,2,0);
2583: msd_dem_common_utilities.set_series_options(x_errbuf,x_retcode,g_schema,x_opd_dp,x_series_03,2,0);
2584: msd_dem_common_utilities.set_series_options(x_errbuf,x_retcode,g_schema,x_opd_dp,x_series_04,2,0);
2585: msd_dem_common_utilities.set_series_options(x_errbuf,x_retcode,g_schema,x_opd_dp,x_series_05,2,0);

Line 2582: msd_dem_common_utilities.set_series_options(x_errbuf,x_retcode,g_schema,x_opd_dp,x_series_02,2,0);

2578: log_message ('INFO: Setting "No load" option and "No Purge" option for series under "other plan data" dp.' );
2579:
2580: -- set to "No load" option and "No Purge" option for the series in the data profile
2581: msd_dem_common_utilities.set_series_options(x_errbuf,x_retcode,g_schema,x_opd_dp,x_series_01,2,0);
2582: msd_dem_common_utilities.set_series_options(x_errbuf,x_retcode,g_schema,x_opd_dp,x_series_02,2,0);
2583: msd_dem_common_utilities.set_series_options(x_errbuf,x_retcode,g_schema,x_opd_dp,x_series_03,2,0);
2584: msd_dem_common_utilities.set_series_options(x_errbuf,x_retcode,g_schema,x_opd_dp,x_series_04,2,0);
2585: msd_dem_common_utilities.set_series_options(x_errbuf,x_retcode,g_schema,x_opd_dp,x_series_05,2,0);
2586: msd_dem_common_utilities.set_series_options(x_errbuf,x_retcode,g_schema,x_opd_dp,x_series_06,2,0,1);

Line 2583: msd_dem_common_utilities.set_series_options(x_errbuf,x_retcode,g_schema,x_opd_dp,x_series_03,2,0);

2579:
2580: -- set to "No load" option and "No Purge" option for the series in the data profile
2581: msd_dem_common_utilities.set_series_options(x_errbuf,x_retcode,g_schema,x_opd_dp,x_series_01,2,0);
2582: msd_dem_common_utilities.set_series_options(x_errbuf,x_retcode,g_schema,x_opd_dp,x_series_02,2,0);
2583: msd_dem_common_utilities.set_series_options(x_errbuf,x_retcode,g_schema,x_opd_dp,x_series_03,2,0);
2584: msd_dem_common_utilities.set_series_options(x_errbuf,x_retcode,g_schema,x_opd_dp,x_series_04,2,0);
2585: msd_dem_common_utilities.set_series_options(x_errbuf,x_retcode,g_schema,x_opd_dp,x_series_05,2,0);
2586: msd_dem_common_utilities.set_series_options(x_errbuf,x_retcode,g_schema,x_opd_dp,x_series_06,2,0,1);
2587:

Line 2584: msd_dem_common_utilities.set_series_options(x_errbuf,x_retcode,g_schema,x_opd_dp,x_series_04,2,0);

2580: -- set to "No load" option and "No Purge" option for the series in the data profile
2581: msd_dem_common_utilities.set_series_options(x_errbuf,x_retcode,g_schema,x_opd_dp,x_series_01,2,0);
2582: msd_dem_common_utilities.set_series_options(x_errbuf,x_retcode,g_schema,x_opd_dp,x_series_02,2,0);
2583: msd_dem_common_utilities.set_series_options(x_errbuf,x_retcode,g_schema,x_opd_dp,x_series_03,2,0);
2584: msd_dem_common_utilities.set_series_options(x_errbuf,x_retcode,g_schema,x_opd_dp,x_series_04,2,0);
2585: msd_dem_common_utilities.set_series_options(x_errbuf,x_retcode,g_schema,x_opd_dp,x_series_05,2,0);
2586: msd_dem_common_utilities.set_series_options(x_errbuf,x_retcode,g_schema,x_opd_dp,x_series_06,2,0,1);
2587:
2588: ELSIF (nvl(fnd_profile.value('MSD_DEM_ENABLE_SUPPLY_PLAN_WO'), 2) = 2) THEN -- profile set to NO

Line 2585: msd_dem_common_utilities.set_series_options(x_errbuf,x_retcode,g_schema,x_opd_dp,x_series_05,2,0);

2581: msd_dem_common_utilities.set_series_options(x_errbuf,x_retcode,g_schema,x_opd_dp,x_series_01,2,0);
2582: msd_dem_common_utilities.set_series_options(x_errbuf,x_retcode,g_schema,x_opd_dp,x_series_02,2,0);
2583: msd_dem_common_utilities.set_series_options(x_errbuf,x_retcode,g_schema,x_opd_dp,x_series_03,2,0);
2584: msd_dem_common_utilities.set_series_options(x_errbuf,x_retcode,g_schema,x_opd_dp,x_series_04,2,0);
2585: msd_dem_common_utilities.set_series_options(x_errbuf,x_retcode,g_schema,x_opd_dp,x_series_05,2,0);
2586: msd_dem_common_utilities.set_series_options(x_errbuf,x_retcode,g_schema,x_opd_dp,x_series_06,2,0,1);
2587:
2588: ELSIF (nvl(fnd_profile.value('MSD_DEM_ENABLE_SUPPLY_PLAN_WO'), 2) = 2) THEN -- profile set to NO
2589:

Line 2586: msd_dem_common_utilities.set_series_options(x_errbuf,x_retcode,g_schema,x_opd_dp,x_series_06,2,0,1);

2582: msd_dem_common_utilities.set_series_options(x_errbuf,x_retcode,g_schema,x_opd_dp,x_series_02,2,0);
2583: msd_dem_common_utilities.set_series_options(x_errbuf,x_retcode,g_schema,x_opd_dp,x_series_03,2,0);
2584: msd_dem_common_utilities.set_series_options(x_errbuf,x_retcode,g_schema,x_opd_dp,x_series_04,2,0);
2585: msd_dem_common_utilities.set_series_options(x_errbuf,x_retcode,g_schema,x_opd_dp,x_series_05,2,0);
2586: msd_dem_common_utilities.set_series_options(x_errbuf,x_retcode,g_schema,x_opd_dp,x_series_06,2,0,1);
2587:
2588: ELSIF (nvl(fnd_profile.value('MSD_DEM_ENABLE_SUPPLY_PLAN_WO'), 2) = 2) THEN -- profile set to NO
2589:
2590: log_message ('INFO: Setting "OVERRIDE" option and "No Purge" option for series under "other plan data" dp.' );

Line 2593: msd_dem_common_utilities.set_series_options(x_errbuf,x_retcode,g_schema,x_opd_dp,x_series_01,0,0);

2589:
2590: log_message ('INFO: Setting "OVERRIDE" option and "No Purge" option for series under "other plan data" dp.' );
2591:
2592: -- set to "OVERRIDE" option and "No Purge" option for the series in the data profile
2593: msd_dem_common_utilities.set_series_options(x_errbuf,x_retcode,g_schema,x_opd_dp,x_series_01,0,0);
2594: msd_dem_common_utilities.set_series_options(x_errbuf,x_retcode,g_schema,x_opd_dp,x_series_02,0,0);
2595: msd_dem_common_utilities.set_series_options(x_errbuf,x_retcode,g_schema,x_opd_dp,x_series_03,0,0);
2596: msd_dem_common_utilities.set_series_options(x_errbuf,x_retcode,g_schema,x_opd_dp,x_series_04,0,0);
2597: msd_dem_common_utilities.set_series_options(x_errbuf,x_retcode,g_schema,x_opd_dp,x_series_05,0,0);

Line 2594: msd_dem_common_utilities.set_series_options(x_errbuf,x_retcode,g_schema,x_opd_dp,x_series_02,0,0);

2590: log_message ('INFO: Setting "OVERRIDE" option and "No Purge" option for series under "other plan data" dp.' );
2591:
2592: -- set to "OVERRIDE" option and "No Purge" option for the series in the data profile
2593: msd_dem_common_utilities.set_series_options(x_errbuf,x_retcode,g_schema,x_opd_dp,x_series_01,0,0);
2594: msd_dem_common_utilities.set_series_options(x_errbuf,x_retcode,g_schema,x_opd_dp,x_series_02,0,0);
2595: msd_dem_common_utilities.set_series_options(x_errbuf,x_retcode,g_schema,x_opd_dp,x_series_03,0,0);
2596: msd_dem_common_utilities.set_series_options(x_errbuf,x_retcode,g_schema,x_opd_dp,x_series_04,0,0);
2597: msd_dem_common_utilities.set_series_options(x_errbuf,x_retcode,g_schema,x_opd_dp,x_series_05,0,0);
2598: msd_dem_common_utilities.set_series_options(x_errbuf,x_retcode,g_schema,x_opd_dp,x_series_06,0,0,1);

Line 2595: msd_dem_common_utilities.set_series_options(x_errbuf,x_retcode,g_schema,x_opd_dp,x_series_03,0,0);

2591:
2592: -- set to "OVERRIDE" option and "No Purge" option for the series in the data profile
2593: msd_dem_common_utilities.set_series_options(x_errbuf,x_retcode,g_schema,x_opd_dp,x_series_01,0,0);
2594: msd_dem_common_utilities.set_series_options(x_errbuf,x_retcode,g_schema,x_opd_dp,x_series_02,0,0);
2595: msd_dem_common_utilities.set_series_options(x_errbuf,x_retcode,g_schema,x_opd_dp,x_series_03,0,0);
2596: msd_dem_common_utilities.set_series_options(x_errbuf,x_retcode,g_schema,x_opd_dp,x_series_04,0,0);
2597: msd_dem_common_utilities.set_series_options(x_errbuf,x_retcode,g_schema,x_opd_dp,x_series_05,0,0);
2598: msd_dem_common_utilities.set_series_options(x_errbuf,x_retcode,g_schema,x_opd_dp,x_series_06,0,0,1);
2599:

Line 2596: msd_dem_common_utilities.set_series_options(x_errbuf,x_retcode,g_schema,x_opd_dp,x_series_04,0,0);

2592: -- set to "OVERRIDE" option and "No Purge" option for the series in the data profile
2593: msd_dem_common_utilities.set_series_options(x_errbuf,x_retcode,g_schema,x_opd_dp,x_series_01,0,0);
2594: msd_dem_common_utilities.set_series_options(x_errbuf,x_retcode,g_schema,x_opd_dp,x_series_02,0,0);
2595: msd_dem_common_utilities.set_series_options(x_errbuf,x_retcode,g_schema,x_opd_dp,x_series_03,0,0);
2596: msd_dem_common_utilities.set_series_options(x_errbuf,x_retcode,g_schema,x_opd_dp,x_series_04,0,0);
2597: msd_dem_common_utilities.set_series_options(x_errbuf,x_retcode,g_schema,x_opd_dp,x_series_05,0,0);
2598: msd_dem_common_utilities.set_series_options(x_errbuf,x_retcode,g_schema,x_opd_dp,x_series_06,0,0,1);
2599:
2600:

Line 2597: msd_dem_common_utilities.set_series_options(x_errbuf,x_retcode,g_schema,x_opd_dp,x_series_05,0,0);

2593: msd_dem_common_utilities.set_series_options(x_errbuf,x_retcode,g_schema,x_opd_dp,x_series_01,0,0);
2594: msd_dem_common_utilities.set_series_options(x_errbuf,x_retcode,g_schema,x_opd_dp,x_series_02,0,0);
2595: msd_dem_common_utilities.set_series_options(x_errbuf,x_retcode,g_schema,x_opd_dp,x_series_03,0,0);
2596: msd_dem_common_utilities.set_series_options(x_errbuf,x_retcode,g_schema,x_opd_dp,x_series_04,0,0);
2597: msd_dem_common_utilities.set_series_options(x_errbuf,x_retcode,g_schema,x_opd_dp,x_series_05,0,0);
2598: msd_dem_common_utilities.set_series_options(x_errbuf,x_retcode,g_schema,x_opd_dp,x_series_06,0,0,1);
2599:
2600:
2601: END IF;

Line 2598: msd_dem_common_utilities.set_series_options(x_errbuf,x_retcode,g_schema,x_opd_dp,x_series_06,0,0,1);

2594: msd_dem_common_utilities.set_series_options(x_errbuf,x_retcode,g_schema,x_opd_dp,x_series_02,0,0);
2595: msd_dem_common_utilities.set_series_options(x_errbuf,x_retcode,g_schema,x_opd_dp,x_series_03,0,0);
2596: msd_dem_common_utilities.set_series_options(x_errbuf,x_retcode,g_schema,x_opd_dp,x_series_04,0,0);
2597: msd_dem_common_utilities.set_series_options(x_errbuf,x_retcode,g_schema,x_opd_dp,x_series_05,0,0);
2598: msd_dem_common_utilities.set_series_options(x_errbuf,x_retcode,g_schema,x_opd_dp,x_series_06,0,0,1);
2599:
2600:
2601: END IF;
2602:

Line 2685: v_demantra_version := to_number(msd_dem_common_utilities.get_demantra_version);

2681: /* Load Item Location */
2682: load_item_locs (x_plan_id);
2683:
2684: -- delet item population from staging table only in 7.2, if p_delete_item_pop is true
2685: v_demantra_version := to_number(msd_dem_common_utilities.get_demantra_version);
2686: if (v_demantra_version < 7.3 and p_delete_item_pop = 1) then
2687: -- bug#8266960
2688: EXECUTE IMMEDIATE 'DELETE FROM ' || g_schema || '.BIIO_SUPPLY_PLANS_POP WHERE LEVEL_ORDER = 2';
2689: COMMIT;

Line 2911: v_schema_id := to_number(msd_dem_common_utilities.get_app_id_text('MSD_DEM_DEMANTRA_OBJECT_ID',

2907: -- syenamar
2908:
2909: -- get id of download plan scenario data wf
2910: /* Bug#8224935 - APP ID */ -- nallkuma
2911: v_schema_id := to_number(msd_dem_common_utilities.get_app_id_text('MSD_DEM_DEMANTRA_OBJECT_ID',
2912: 'WF_DOWNLOAD_PLAN_SCENARIO_DATA',
2913: 1,
2914: 'schema_id'));
2915: --syenamar

Line 2917: v_demantra_version := to_number(msd_dem_common_utilities.get_demantra_version);

2913: 1,
2914: 'schema_id'));
2915: --syenamar
2916:
2917: v_demantra_version := to_number(msd_dem_common_utilities.get_demantra_version);
2918: IF (v_demantra_version >= 7.3)
2919: THEN
2920: v_not_in_steps := '(''' || nvl(p_wait_step_id, 'Wait') || ''', ''' || nvl(p_exception_step_id, 'Step2') || ''')';
2921: ELSE

Line 2995: msd_dem_common_utilities.log_message ('Entering: msd_dem_sop.collect_sci_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));

2991: AND object_name = 'MDP_MATRIX'
2992: ORDER BY created desc;
2993: BEGIN
2994: BEGIN
2995: msd_dem_common_utilities.log_message ('Entering: msd_dem_sop.collect_sci_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
2996:
2997: OPEN c_get_dm_schema; --jarora
2998: FETCH c_get_dm_schema INTO g_schema;
2999: CLOSE c_get_dm_schema;

Line 3009: l_profile_id := to_number(msd_dem_common_utilities.get_app_id_text ('MSD_DEM_DEMANTRA_OBJECT_ID',

3005: if (g_schema is not null)
3006: then
3007:
3008: -- Bug#6741262 - kkhatri
3009: l_profile_id := to_number(msd_dem_common_utilities.get_app_id_text ('MSD_DEM_DEMANTRA_OBJECT_ID',
3010: 'PROFILE_PURGE_BACKLOG_DATA',
3011: 1,
3012: 'id'));
3013:

Line 3022: msd_dem_common_utilities.log_debug ('Begin get dates - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));

3018:
3019:
3020: /* Get the start date and end dates for collection */
3021:
3022: msd_dem_common_utilities.log_debug ('Begin get dates - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
3023:
3024: IF (p_collection_method = 1) /* Refresh*/
3025: THEN
3026: x_from_date := to_date('01/01/1000', 'DD/MM/YYYY');

Line 3051: msd_dem_common_utilities.log_message ('Error(6): msd_dem_sop.collect_sci_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));

3047: IF (x_from_date > x_to_date)
3048: THEN
3049: retcode := -1;
3050: errbuf := 'From Date should not be greater than To Date.';
3051: msd_dem_common_utilities.log_message ('Error(6): msd_dem_sop.collect_sci_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
3052: msd_dem_common_utilities.log_message (errbuf);
3053: RETURN;
3054: END IF;
3055:

Line 3052: msd_dem_common_utilities.log_message (errbuf);

3048: THEN
3049: retcode := -1;
3050: errbuf := 'From Date should not be greater than To Date.';
3051: msd_dem_common_utilities.log_message ('Error(6): msd_dem_sop.collect_sci_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
3052: msd_dem_common_utilities.log_message (errbuf);
3053: RETURN;
3054: END IF;
3055:
3056: ELSE /* Rolling */

Line 3062: msd_dem_common_utilities.log_message ('Error(7): msd_dem_sop.collect_sci_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));

3058: IF (p_collection_window < 0)
3059: THEN
3060: retcode := -1;
3061: errbuf := 'History Collection Window must be a positive number.';
3062: msd_dem_common_utilities.log_message ('Error(7): msd_dem_sop.collect_sci_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
3063: msd_dem_common_utilities.log_message (errbuf);
3064: RETURN;
3065: ELSE
3066: x_to_date := trunc(sysdate);

Line 3063: msd_dem_common_utilities.log_message (errbuf);

3059: THEN
3060: retcode := -1;
3061: errbuf := 'History Collection Window must be a positive number.';
3062: msd_dem_common_utilities.log_message ('Error(7): msd_dem_sop.collect_sci_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
3063: msd_dem_common_utilities.log_message (errbuf);
3064: RETURN;
3065: ELSE
3066: x_to_date := trunc(sysdate);
3067: x_from_date := x_to_date - p_collection_window + 1;

Line 3072: msd_dem_common_utilities.log_debug ('End get dates - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));

3068: END IF;
3069: END IF;
3070: END IF;
3071:
3072: msd_dem_common_utilities.log_debug ('End get dates - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
3073:
3074:
3075:
3076: IF (x_retcode1 = -1)

Line 3081: msd_dem_common_utilities.log_message ('Error while refreshing Purge Series Data. ');

3077: THEN
3078: retcode := -1;
3079: errbuf := x_errbuf1;
3080:
3081: msd_dem_common_utilities.log_message ('Error while refreshing Purge Series Data. ');
3082: END IF;
3083:
3084: msd_dem_common_utilities.log_debug ('Calling API_MODIFY_INTEG_SERIES_FDATE - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
3085:

Line 3084: msd_dem_common_utilities.log_debug ('Calling API_MODIFY_INTEG_SERIES_FDATE - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));

3080:
3081: msd_dem_common_utilities.log_message ('Error while refreshing Purge Series Data. ');
3082: END IF;
3083:
3084: msd_dem_common_utilities.log_debug ('Calling API_MODIFY_INTEG_SERIES_FDATE - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
3085:
3086: l_sql := 'select datet from '|| g_schema ||'.inputs where datet >= '''||x_from_date||''' and rownum = 1 order by datet asc';
3087: execute immediate l_sql into x_from_date;
3088:

Line 3096: msd_dem_common_utilities.log_message ('For the series, the old data will be purged from ''' || x_from_date ||''' to '''||x_to_date ||'''');

3092: if (x_from_date > x_to_date) then
3093: x_to_date := x_from_date;
3094: end if;
3095:
3096: msd_dem_common_utilities.log_message ('For the series, the old data will be purged from ''' || x_from_date ||''' to '''||x_to_date ||'''');
3097:
3098: l_sql := 'begin ' || g_schema|| '.API_MODIFY_INTEG_SERIES_FDATE('||l_profile_id||', '''|| x_from_date||''' , '''||x_to_date||'''); end;';
3099: execute immediate l_sql;
3100:

Line 3101: msd_dem_common_utilities.log_message ('For the series, options will be NO LOAD and PURGE WITHIN DATE RANGE');

3097:
3098: l_sql := 'begin ' || g_schema|| '.API_MODIFY_INTEG_SERIES_FDATE('||l_profile_id||', '''|| x_from_date||''' , '''||x_to_date||'''); end;';
3099: execute immediate l_sql;
3100:
3101: msd_dem_common_utilities.log_message ('For the series, options will be NO LOAD and PURGE WITHIN DATE RANGE');
3102:
3103: l_tbl_id := to_number(msd_dem_common_utilities.get_app_id_text ('MSD_DEM_DEMANTRA_OBJECT_ID',
3104: 'SERIES_TOTAL_BACKLOG',
3105: 1,

Line 3103: l_tbl_id := to_number(msd_dem_common_utilities.get_app_id_text ('MSD_DEM_DEMANTRA_OBJECT_ID',

3099: execute immediate l_sql;
3100:
3101: msd_dem_common_utilities.log_message ('For the series, options will be NO LOAD and PURGE WITHIN DATE RANGE');
3102:
3103: l_tbl_id := to_number(msd_dem_common_utilities.get_app_id_text ('MSD_DEM_DEMANTRA_OBJECT_ID',
3104: 'SERIES_TOTAL_BACKLOG',
3105: 1,
3106: 'forecast_type_id'));
3107:

Line 3112: l_pdbl_id := to_number(msd_dem_common_utilities.get_app_id_text ('MSD_DEM_DEMANTRA_OBJECT_ID',

3108: l_sql := 'begin ' || g_schema|| '.API_MODIFY_INTEG_SERIES_ATTR('||l_profile_id||', '|| l_tbl_id||', 2, 2); end;';
3109: execute immediate l_sql;
3110:
3111:
3112: l_pdbl_id := to_number(msd_dem_common_utilities.get_app_id_text ('MSD_DEM_DEMANTRA_OBJECT_ID',
3113: 'SERIES_PAST_DUE_BACKLOG',
3114: 1,
3115: 'forecast_type_id'));
3116:

Line 3122: msd_dem_common_utilities.log_debug ('Calling API_NOTIFY_APS_INTEGRATION - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));

3118: execute immediate l_sql;
3119:
3120:
3121: /* Calling API to notify the application server to refresh its engine */
3122: msd_dem_common_utilities.log_debug ('Calling API_NOTIFY_APS_INTEGRATION - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
3123:
3124: l_sql := 'begin ' || g_schema|| '.API_NOTIFY_APS_INTEGRATION('||l_profile_id ||'); end;';
3125: msd_dem_common_utilities.log_debug (l_sql);
3126: execute immediate l_sql;

Line 3125: msd_dem_common_utilities.log_debug (l_sql);

3121: /* Calling API to notify the application server to refresh its engine */
3122: msd_dem_common_utilities.log_debug ('Calling API_NOTIFY_APS_INTEGRATION - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
3123:
3124: l_sql := 'begin ' || g_schema|| '.API_NOTIFY_APS_INTEGRATION('||l_profile_id ||'); end;';
3125: msd_dem_common_utilities.log_debug (l_sql);
3126: execute immediate l_sql;
3127:
3128: l_sql := 'truncate table '|| g_schema ||'.'||l_table_name ;
3129: msd_dem_common_utilities.log_debug (l_sql);

Line 3129: msd_dem_common_utilities.log_debug (l_sql);

3125: msd_dem_common_utilities.log_debug (l_sql);
3126: execute immediate l_sql;
3127:
3128: l_sql := 'truncate table '|| g_schema ||'.'||l_table_name ;
3129: msd_dem_common_utilities.log_debug (l_sql);
3130: execute immediate l_sql;
3131:
3132: l_sql := 'insert into '|| g_schema ||'.'||l_table_name||'(sdate, level1)'||
3133: 'select '''||x_from_date||''', teo.organization from '||g_schema||'.t_ep_organization teo '||

Line 3145: msd_dem_common_utilities.log_debug (l_sql);

3141: ' AND mtp.sr_instance_id = mio.sr_instance_id ' ||
3142: ' AND mtp.sr_tp_id = mio.organization_id '||
3143: ' AND mtp.partner_type = 3) ';
3144:
3145: msd_dem_common_utilities.log_debug (l_sql);
3146:
3147: execute immediate l_sql;
3148:
3149:

Line 3151: msd_dem_common_utilities.log_message('Demantra Schema not set');

3147: execute immediate l_sql;
3148:
3149:
3150: else
3151: msd_dem_common_utilities.log_message('Demantra Schema not set');
3152: end if;
3153: EXCEPTION
3154: WHEN OTHERS THEN
3155: retcode := 1 ;

Line 3157: msd_dem_common_utilities.log_message ('Warning: can not purge old shipment/booking history data.' );

3153: EXCEPTION
3154: WHEN OTHERS THEN
3155: retcode := 1 ;
3156: errbuf := substr(SQLERRM,1,150);
3157: msd_dem_common_utilities.log_message ('Warning: can not purge old shipment/booking history data.' );
3158: msd_dem_common_utilities.log_debug ('Warning: can not purge old shipment/booking history data.' );
3159: msd_dem_common_utilities.log_debug (errbuf);
3160: msd_dem_common_utilities.log_debug ('Exiting: msd_dem_collect_history_data.collect_history_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
3161:

Line 3158: msd_dem_common_utilities.log_debug ('Warning: can not purge old shipment/booking history data.' );

3154: WHEN OTHERS THEN
3155: retcode := 1 ;
3156: errbuf := substr(SQLERRM,1,150);
3157: msd_dem_common_utilities.log_message ('Warning: can not purge old shipment/booking history data.' );
3158: msd_dem_common_utilities.log_debug ('Warning: can not purge old shipment/booking history data.' );
3159: msd_dem_common_utilities.log_debug (errbuf);
3160: msd_dem_common_utilities.log_debug ('Exiting: msd_dem_collect_history_data.collect_history_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
3161:
3162: RETURN;

Line 3159: msd_dem_common_utilities.log_debug (errbuf);

3155: retcode := 1 ;
3156: errbuf := substr(SQLERRM,1,150);
3157: msd_dem_common_utilities.log_message ('Warning: can not purge old shipment/booking history data.' );
3158: msd_dem_common_utilities.log_debug ('Warning: can not purge old shipment/booking history data.' );
3159: msd_dem_common_utilities.log_debug (errbuf);
3160: msd_dem_common_utilities.log_debug ('Exiting: msd_dem_collect_history_data.collect_history_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
3161:
3162: RETURN;
3163: end;

Line 3160: msd_dem_common_utilities.log_debug ('Exiting: msd_dem_collect_history_data.collect_history_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));

3156: errbuf := substr(SQLERRM,1,150);
3157: msd_dem_common_utilities.log_message ('Warning: can not purge old shipment/booking history data.' );
3158: msd_dem_common_utilities.log_debug ('Warning: can not purge old shipment/booking history data.' );
3159: msd_dem_common_utilities.log_debug (errbuf);
3160: msd_dem_common_utilities.log_debug ('Exiting: msd_dem_collect_history_data.collect_history_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
3161:
3162: RETURN;
3163: end;
3164:

Line 3191: msd_dem_common_utilities.log_message ('Error launching concurrent program for SCI BACKLOG Integration Interface ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));

3187:
3188: --commit;
3189: EXCEPTION
3190: WHEN OTHERS THEN
3191: msd_dem_common_utilities.log_message ('Error launching concurrent program for SCI BACKLOG Integration Interface ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
3192: msd_dem_common_utilities.log_message (errbuf);
3193: END;
3194:
3195: BEGIN

Line 3192: msd_dem_common_utilities.log_message (errbuf);

3188: --commit;
3189: EXCEPTION
3190: WHEN OTHERS THEN
3191: msd_dem_common_utilities.log_message ('Error launching concurrent program for SCI BACKLOG Integration Interface ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
3192: msd_dem_common_utilities.log_message (errbuf);
3193: END;
3194:
3195: BEGIN
3196: lv_request_id2 := fnd_request.submit_request('MSD',

Line 3213: msd_dem_common_utilities.log_message ('Error launching concurrent program for SCI OTHER Integration Interface ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));

3209:
3210: --commit;
3211: EXCEPTION
3212: WHEN OTHERS THEN
3213: msd_dem_common_utilities.log_message ('Error launching concurrent program for SCI OTHER Integration Interface ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
3214: msd_dem_common_utilities.log_message (errbuf);
3215: END;
3216:
3217: IF ( lv_request_id1 <> 0 ) AND ( lv_request_id2 <> 0 ) THEN

Line 3214: msd_dem_common_utilities.log_message (errbuf);

3210: --commit;
3211: EXCEPTION
3212: WHEN OTHERS THEN
3213: msd_dem_common_utilities.log_message ('Error launching concurrent program for SCI OTHER Integration Interface ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
3214: msd_dem_common_utilities.log_message (errbuf);
3215: END;
3216:
3217: IF ( lv_request_id1 <> 0 ) AND ( lv_request_id2 <> 0 ) THEN
3218:

Line 3219: msd_dem_common_utilities.log_message ('Successfully launched concurrent programs for SCI Integration Interfaces. Please see the following concurrent programs for the individual request logs. '

3215: END;
3216:
3217: IF ( lv_request_id1 <> 0 ) AND ( lv_request_id2 <> 0 ) THEN
3218:
3219: msd_dem_common_utilities.log_message ('Successfully launched concurrent programs for SCI Integration Interfaces. Please see the following concurrent programs for the individual request logs. '
3220: || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
3221: msd_dem_common_utilities.log_message ('Request ID for the SCI BACKLOG Integration Interface concurrent program is - '|| lv_request_id1 ||' '|| TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
3222: msd_dem_common_utilities.log_message ('Request ID for the SCI OTHER Integration Interface concurrent program is - '|| lv_request_id2 ||' '|| TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
3223:

Line 3221: msd_dem_common_utilities.log_message ('Request ID for the SCI BACKLOG Integration Interface concurrent program is - '|| lv_request_id1 ||' '|| TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));

3217: IF ( lv_request_id1 <> 0 ) AND ( lv_request_id2 <> 0 ) THEN
3218:
3219: msd_dem_common_utilities.log_message ('Successfully launched concurrent programs for SCI Integration Interfaces. Please see the following concurrent programs for the individual request logs. '
3220: || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
3221: msd_dem_common_utilities.log_message ('Request ID for the SCI BACKLOG Integration Interface concurrent program is - '|| lv_request_id1 ||' '|| TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
3222: msd_dem_common_utilities.log_message ('Request ID for the SCI OTHER Integration Interface concurrent program is - '|| lv_request_id2 ||' '|| TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
3223:
3224: ELSE
3225:

Line 3222: msd_dem_common_utilities.log_message ('Request ID for the SCI OTHER Integration Interface concurrent program is - '|| lv_request_id2 ||' '|| TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));

3218:
3219: msd_dem_common_utilities.log_message ('Successfully launched concurrent programs for SCI Integration Interfaces. Please see the following concurrent programs for the individual request logs. '
3220: || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
3221: msd_dem_common_utilities.log_message ('Request ID for the SCI BACKLOG Integration Interface concurrent program is - '|| lv_request_id1 ||' '|| TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
3222: msd_dem_common_utilities.log_message ('Request ID for the SCI OTHER Integration Interface concurrent program is - '|| lv_request_id2 ||' '|| TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
3223:
3224: ELSE
3225:
3226: msd_dem_common_utilities.log_message ('Request ID for the SCI BACKLOG Integration Interface concurrent program is - '|| lv_request_id1 ||' '|| TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));

Line 3226: msd_dem_common_utilities.log_message ('Request ID for the SCI BACKLOG Integration Interface concurrent program is - '|| lv_request_id1 ||' '|| TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));

3222: msd_dem_common_utilities.log_message ('Request ID for the SCI OTHER Integration Interface concurrent program is - '|| lv_request_id2 ||' '|| TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
3223:
3224: ELSE
3225:
3226: msd_dem_common_utilities.log_message ('Request ID for the SCI BACKLOG Integration Interface concurrent program is - '|| lv_request_id1 ||' '|| TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
3227: msd_dem_common_utilities.log_message ('Request ID for the SCI OTHER Integration Interface concurrent program is - '|| lv_request_id2 ||' '|| TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
3228: msd_dem_common_utilities.log_message ('Error launching concurrent programs for SCI Integration Interfaces. Please relaunch the SCI Collections. ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
3229: retcode := -1 ;
3230:

Line 3227: msd_dem_common_utilities.log_message ('Request ID for the SCI OTHER Integration Interface concurrent program is - '|| lv_request_id2 ||' '|| TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));

3223:
3224: ELSE
3225:
3226: msd_dem_common_utilities.log_message ('Request ID for the SCI BACKLOG Integration Interface concurrent program is - '|| lv_request_id1 ||' '|| TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
3227: msd_dem_common_utilities.log_message ('Request ID for the SCI OTHER Integration Interface concurrent program is - '|| lv_request_id2 ||' '|| TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
3228: msd_dem_common_utilities.log_message ('Error launching concurrent programs for SCI Integration Interfaces. Please relaunch the SCI Collections. ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
3229: retcode := -1 ;
3230:
3231: END IF;

Line 3228: msd_dem_common_utilities.log_message ('Error launching concurrent programs for SCI Integration Interfaces. Please relaunch the SCI Collections. ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));

3224: ELSE
3225:
3226: msd_dem_common_utilities.log_message ('Request ID for the SCI BACKLOG Integration Interface concurrent program is - '|| lv_request_id1 ||' '|| TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
3227: msd_dem_common_utilities.log_message ('Request ID for the SCI OTHER Integration Interface concurrent program is - '|| lv_request_id2 ||' '|| TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
3228: msd_dem_common_utilities.log_message ('Error launching concurrent programs for SCI Integration Interfaces. Please relaunch the SCI Collections. ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
3229: retcode := -1 ;
3230:
3231: END IF;
3232:

Line 3235: msd_dem_common_utilities.log_message ('Exiting: msd_dem_sop.collect_sci_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));

3231: END IF;
3232:
3233: COMMIT;
3234:
3235: msd_dem_common_utilities.log_message ('Exiting: msd_dem_sop.collect_sci_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
3236: EXCEPTION
3237: WHEN OTHERS THEN
3238: retcode := -1 ;
3239: errbuf := substr(SQLERRM,1,150);

Line 3240: msd_dem_common_utilities.log_message ('Exception: msd_dem_sop.collect_sci_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));

3236: EXCEPTION
3237: WHEN OTHERS THEN
3238: retcode := -1 ;
3239: errbuf := substr(SQLERRM,1,150);
3240: msd_dem_common_utilities.log_message ('Exception: msd_dem_sop.collect_sci_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
3241: msd_dem_common_utilities.log_message (errbuf);
3242: RETURN;
3243:
3244: END COLLECT_SCI_DATA;

Line 3241: msd_dem_common_utilities.log_message (errbuf);

3237: WHEN OTHERS THEN
3238: retcode := -1 ;
3239: errbuf := substr(SQLERRM,1,150);
3240: msd_dem_common_utilities.log_message ('Exception: msd_dem_sop.collect_sci_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
3241: msd_dem_common_utilities.log_message (errbuf);
3242: RETURN;
3243:
3244: END COLLECT_SCI_DATA;
3245:

Line 3284: msd_dem_common_utilities.log_message ('Entering: msd_dem_sop.launch_sci_data_loads - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));

3280:
3281:
3282: BEGIN
3283:
3284: msd_dem_common_utilities.log_message ('Entering: msd_dem_sop.launch_sci_data_loads - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
3285:
3286: retcode := 0;
3287:
3288: /* SCI BACKLOG */

Line 3315: msd_dem_common_utilities.log_message ('An Error occured in API call MSD_DEM_COLLECT_RETURN_HISTORY.COLLECT_RETURN_HISTORY_DATA,while inserting Total Backlog Data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));

3311: IF l_retcode1 = -1
3312: THEN
3313: retcode := l_retcode1;
3314: errbuf := l_errbuff1;
3315: msd_dem_common_utilities.log_message ('An Error occured in API call MSD_DEM_COLLECT_RETURN_HISTORY.COLLECT_RETURN_HISTORY_DATA,while inserting Total Backlog Data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
3316: RETURN;
3317: END IF;
3318:
3319: EXCEPTION

Line 3323: msd_dem_common_utilities.log_message ('Exception while inserting Total Backlog Data in the table BIIIO_SCI_BACKLOG - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));

3319: EXCEPTION
3320: WHEN OTHERS THEN
3321: retcode := -1 ;
3322: errbuf := substr(SQLERRM,1,150);
3323: msd_dem_common_utilities.log_message ('Exception while inserting Total Backlog Data in the table BIIIO_SCI_BACKLOG - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
3324: msd_dem_common_utilities.log_message (errbuf);
3325: RETURN;
3326: END;
3327:

Line 3324: msd_dem_common_utilities.log_message (errbuf);

3320: WHEN OTHERS THEN
3321: retcode := -1 ;
3322: errbuf := substr(SQLERRM,1,150);
3323: msd_dem_common_utilities.log_message ('Exception while inserting Total Backlog Data in the table BIIIO_SCI_BACKLOG - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
3324: msd_dem_common_utilities.log_message (errbuf);
3325: RETURN;
3326: END;
3327:
3328: /* Past Due Backlog*/

Line 3351: msd_dem_common_utilities.log_message ('An Error occured in API call MSD_DEM_COLLECT_RETURN_HISTORY.COLLECT_RETURN_HISTORY_DATA,while merging Past Due Backlog Data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));

3347: IF l_retcode2 = -1
3348: THEN
3349: retcode := l_retcode2;
3350: errbuf := l_errbuff2;
3351: msd_dem_common_utilities.log_message ('An Error occured in API call MSD_DEM_COLLECT_RETURN_HISTORY.COLLECT_RETURN_HISTORY_DATA,while merging Past Due Backlog Data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
3352: RETURN;
3353: END IF;
3354:
3355:

Line 3360: msd_dem_common_utilities.log_message ('Exception while merging Past Due Backlog Data in the table BIIIO_SCI_BACKLOG - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));

3356: EXCEPTION
3357: WHEN OTHERS THEN
3358: retcode := -1 ;
3359: errbuf := substr(SQLERRM,1,150);
3360: msd_dem_common_utilities.log_message ('Exception while merging Past Due Backlog Data in the table BIIIO_SCI_BACKLOG - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
3361: msd_dem_common_utilities.log_message (errbuf);
3362: RETURN;
3363: END;
3364:

Line 3361: msd_dem_common_utilities.log_message (errbuf);

3357: WHEN OTHERS THEN
3358: retcode := -1 ;
3359: errbuf := substr(SQLERRM,1,150);
3360: msd_dem_common_utilities.log_message ('Exception while merging Past Due Backlog Data in the table BIIIO_SCI_BACKLOG - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
3361: msd_dem_common_utilities.log_message (errbuf);
3362: RETURN;
3363: END;
3364:
3365: IF ( l_retcode1 = 1 ) OR ( l_retcode2 = 1)

Line 3368: msd_dem_common_utilities.log_message ('Warning Text for Total Backlog Insertion is - ' || l_errbuff1);

3364:
3365: IF ( l_retcode1 = 1 ) OR ( l_retcode2 = 1)
3366: THEN
3367: retcode := 1;
3368: msd_dem_common_utilities.log_message ('Warning Text for Total Backlog Insertion is - ' || l_errbuff1);
3369: msd_dem_common_utilities.log_message ('Warning Text for Past Due Backlog Merge is - ' || l_errbuff2);
3370: errbuf := l_errbuff1 || l_errbuff2 ;
3371: END IF;
3372:

Line 3369: msd_dem_common_utilities.log_message ('Warning Text for Past Due Backlog Merge is - ' || l_errbuff2);

3365: IF ( l_retcode1 = 1 ) OR ( l_retcode2 = 1)
3366: THEN
3367: retcode := 1;
3368: msd_dem_common_utilities.log_message ('Warning Text for Total Backlog Insertion is - ' || l_errbuff1);
3369: msd_dem_common_utilities.log_message ('Warning Text for Past Due Backlog Merge is - ' || l_errbuff2);
3370: errbuf := l_errbuff1 || l_errbuff2 ;
3371: END IF;
3372:
3373: /* Call - Level Code Generation Query */

Line 3386: IF (msd_dem_common_utilities.is_use_new_site_format <> 0)

3382: END IF;
3383:
3384: -- bug#7419035, syenamar
3385: -- update level3(customer), replace customer name with the unique partner_id from ASCP
3386: IF (msd_dem_common_utilities.is_use_new_site_format <> 0)
3387: THEN
3388: l_sql_stmnt := ' update '||x_dest_table||' bsb '
3389: ||' set level3 = ( select mtp.partner_id '
3390: ||' from msc_trading_partners mtp '

Line 3395: msd_dem_common_utilities.log_message(l_sql_stmnt);

3391: ||' where mtp.partner_name = bsb.level3 '
3392: ||' and mtp.partner_type = 2 )'
3393: ||' WHERE LEVEL3 <> ''' || msd_dem_sr_util.get_null_code || ''' ';
3394:
3395: msd_dem_common_utilities.log_message(l_sql_stmnt);
3396:
3397: begin
3398: execute immediate l_sql_stmnt;
3399: exception

Line 3409: msd_dem_common_utilities.log_message(l_sql_stmnt);

3405: l_sql_stmnt := ' UPDATE ' || x_dest_table
3406: || ' SET level3 = ''' || msd_dem_sr_util.get_null_code || ''' '
3407: || ' WHERE level3 IS NULL ';
3408:
3409: msd_dem_common_utilities.log_message(l_sql_stmnt);
3410:
3411: begin
3412: execute immediate l_sql_stmnt;
3413: exception

Line 3461: msd_dem_common_utilities.log_message ('An Error occured in API call MSD_DEM_COLLECT_RETURN_HISTORY.COLLECT_RETURN_HISTORY_DATA,while inserting On Hand Inventory Data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));

3457: IF l_retcode1 = -1
3458: THEN
3459: retcode := l_retcode1;
3460: errbuf := l_errbuff1;
3461: msd_dem_common_utilities.log_message ('An Error occured in API call MSD_DEM_COLLECT_RETURN_HISTORY.COLLECT_RETURN_HISTORY_DATA,while inserting On Hand Inventory Data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
3462: RETURN;
3463: END IF;
3464: EXCEPTION
3465: WHEN OTHERS THEN

Line 3468: msd_dem_common_utilities.log_message ('Exception while inserting ON-Hand Inventory Data in the table BIIIO_SCI - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));

3464: EXCEPTION
3465: WHEN OTHERS THEN
3466: retcode := -1 ;
3467: errbuf := substr(SQLERRM,1,150);
3468: msd_dem_common_utilities.log_message ('Exception while inserting ON-Hand Inventory Data in the table BIIIO_SCI - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
3469: msd_dem_common_utilities.log_message (errbuf);
3470: RETURN;
3471:
3472: END;

Line 3469: msd_dem_common_utilities.log_message (errbuf);

3465: WHEN OTHERS THEN
3466: retcode := -1 ;
3467: errbuf := substr(SQLERRM,1,150);
3468: msd_dem_common_utilities.log_message ('Exception while inserting ON-Hand Inventory Data in the table BIIIO_SCI - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
3469: msd_dem_common_utilities.log_message (errbuf);
3470: RETURN;
3471:
3472: END;
3473:

Line 3497: msd_dem_common_utilities.log_message ('An Error occured in API call MSD_DEM_COLLECT_RETURN_HISTORY.COLLECT_RETURN_HISTORY_DATA,while merging Actual Production Data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));

3493: IF l_retcode2 = -1
3494: THEN
3495: retcode := l_retcode2;
3496: errbuf := l_errbuff2;
3497: msd_dem_common_utilities.log_message ('An Error occured in API call MSD_DEM_COLLECT_RETURN_HISTORY.COLLECT_RETURN_HISTORY_DATA,while merging Actual Production Data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
3498: RETURN;
3499: END IF;
3500:
3501:

Line 3506: msd_dem_common_utilities.log_message ('Exception while merging Actual Production Data in the table BIIIO_SCI - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));

3502: EXCEPTION
3503: WHEN OTHERS THEN
3504: retcode := -1 ;
3505: errbuf := substr(SQLERRM,1,150);
3506: msd_dem_common_utilities.log_message ('Exception while merging Actual Production Data in the table BIIIO_SCI - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
3507: msd_dem_common_utilities.log_message (errbuf);
3508: RETURN;
3509: END;
3510:

Line 3507: msd_dem_common_utilities.log_message (errbuf);

3503: WHEN OTHERS THEN
3504: retcode := -1 ;
3505: errbuf := substr(SQLERRM,1,150);
3506: msd_dem_common_utilities.log_message ('Exception while merging Actual Production Data in the table BIIIO_SCI - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
3507: msd_dem_common_utilities.log_message (errbuf);
3508: RETURN;
3509: END;
3510:
3511: IF ( l_retcode1 = 1 ) OR ( l_retcode2 = 1)

Line 3514: msd_dem_common_utilities.log_message ('Warning Text for On-Hand Inventory Insertion is - ' || l_errbuff1);

3510:
3511: IF ( l_retcode1 = 1 ) OR ( l_retcode2 = 1)
3512: THEN
3513: retcode := 1;
3514: msd_dem_common_utilities.log_message ('Warning Text for On-Hand Inventory Insertion is - ' || l_errbuff1);
3515: msd_dem_common_utilities.log_message ('Warning Text for Actual Production Merge is - ' || l_errbuff2);
3516: errbuf := l_errbuff1 || l_errbuff2 ;
3517: END IF;
3518: END IF; -- IF ( p_entity = G_SCI_BACKLOG )

Line 3515: msd_dem_common_utilities.log_message ('Warning Text for Actual Production Merge is - ' || l_errbuff2);

3511: IF ( l_retcode1 = 1 ) OR ( l_retcode2 = 1)
3512: THEN
3513: retcode := 1;
3514: msd_dem_common_utilities.log_message ('Warning Text for On-Hand Inventory Insertion is - ' || l_errbuff1);
3515: msd_dem_common_utilities.log_message ('Warning Text for Actual Production Merge is - ' || l_errbuff2);
3516: errbuf := l_errbuff1 || l_errbuff2 ;
3517: END IF;
3518: END IF; -- IF ( p_entity = G_SCI_BACKLOG )
3519:

Line 3520: msd_dem_common_utilities.log_message ('Exiting: msd_dem_sop.launch_sci_data_loads - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));

3516: errbuf := l_errbuff1 || l_errbuff2 ;
3517: END IF;
3518: END IF; -- IF ( p_entity = G_SCI_BACKLOG )
3519:
3520: msd_dem_common_utilities.log_message ('Exiting: msd_dem_sop.launch_sci_data_loads - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
3521:
3522: EXCEPTION
3523: WHEN OTHERS THEN
3524: retcode := -1 ;

Line 3526: msd_dem_common_utilities.log_message ('Exception: msd_dem_sop.launch_sci_data_loads- ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));

3522: EXCEPTION
3523: WHEN OTHERS THEN
3524: retcode := -1 ;
3525: errbuf := substr(SQLERRM,1,150);
3526: msd_dem_common_utilities.log_message ('Exception: msd_dem_sop.launch_sci_data_loads- ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
3527: msd_dem_common_utilities.log_message (errbuf);
3528: RETURN;
3529:
3530: END LAUNCH_SCI_DATA_LOADS;

Line 3527: msd_dem_common_utilities.log_message (errbuf);

3523: WHEN OTHERS THEN
3524: retcode := -1 ;
3525: errbuf := substr(SQLERRM,1,150);
3526: msd_dem_common_utilities.log_message ('Exception: msd_dem_sop.launch_sci_data_loads- ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
3527: msd_dem_common_utilities.log_message (errbuf);
3528: RETURN;
3529:
3530: END LAUNCH_SCI_DATA_LOADS;
3531:

Line 3550: msd_dem_common_utilities.log_debug ('Entering: msd_dem_sop.load_cost - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));

3546: x_dblink VARCHAR2(100) := NULL;
3547: x_master_org NUMBER := NULL;
3548: BEGIN
3549:
3550: msd_dem_common_utilities.log_debug ('Entering: msd_dem_sop.load_cost - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
3551:
3552: var_schema := fnd_profile.value('MSD_DEM_SCHEMA');
3553: IF (var_schema IS NULL) THEN
3554: retcode:= -1;

Line 3555: msd_dem_common_utilities.log_message ('Error(1): msd_dem_sop.load_cost - Unable to find schema name');

3551:
3552: var_schema := fnd_profile.value('MSD_DEM_SCHEMA');
3553: IF (var_schema IS NULL) THEN
3554: retcode:= -1;
3555: msd_dem_common_utilities.log_message ('Error(1): msd_dem_sop.load_cost - Unable to find schema name');
3556: RETURN;
3557: END IF;
3558:
3559: /* Get dblink for source instance */

Line 3560: msd_dem_common_utilities.get_dblink (x_errbuf, x_retcode, p_sr_instance_id, x_dblink);

3556: RETURN;
3557: END IF;
3558:
3559: /* Get dblink for source instance */
3560: msd_dem_common_utilities.get_dblink (x_errbuf, x_retcode, p_sr_instance_id, x_dblink);
3561: IF (x_retcode = '-1')
3562: THEN
3563: retcode := -1;
3564: errbuf := x_errbuf;

Line 3565: msd_dem_common_utilities.log_message ('Error(1): msd_dem_sop.load_cost - Error in getting dblink' || sysdate);

3561: IF (x_retcode = '-1')
3562: THEN
3563: retcode := -1;
3564: errbuf := x_errbuf;
3565: msd_dem_common_utilities.log_message ('Error(1): msd_dem_sop.load_cost - Error in getting dblink' || sysdate);
3566: msd_dem_common_utilities.log_message (x_errbuf);
3567: RETURN;
3568: END IF;
3569: msd_dem_common_utilities.log_debug ('Source DB Link - ' || x_dblink);

Line 3566: msd_dem_common_utilities.log_message (x_errbuf);

3562: THEN
3563: retcode := -1;
3564: errbuf := x_errbuf;
3565: msd_dem_common_utilities.log_message ('Error(1): msd_dem_sop.load_cost - Error in getting dblink' || sysdate);
3566: msd_dem_common_utilities.log_message (x_errbuf);
3567: RETURN;
3568: END IF;
3569: msd_dem_common_utilities.log_debug ('Source DB Link - ' || x_dblink);
3570:

Line 3569: msd_dem_common_utilities.log_debug ('Source DB Link - ' || x_dblink);

3565: msd_dem_common_utilities.log_message ('Error(1): msd_dem_sop.load_cost - Error in getting dblink' || sysdate);
3566: msd_dem_common_utilities.log_message (x_errbuf);
3567: RETURN;
3568: END IF;
3569: msd_dem_common_utilities.log_debug ('Source DB Link - ' || x_dblink);
3570:
3571: /* Get master organization */
3572: x_master_org := to_number(msd_dem_common_utilities.get_parameter_value(p_sr_instance_id, 'MSD_DEM_MASTER_ORG'));
3573: msd_dem_common_utilities.log_debug ('Source Master Organization from which list price is collected - ' || to_char(x_master_org));

Line 3572: x_master_org := to_number(msd_dem_common_utilities.get_parameter_value(p_sr_instance_id, 'MSD_DEM_MASTER_ORG'));

3568: END IF;
3569: msd_dem_common_utilities.log_debug ('Source DB Link - ' || x_dblink);
3570:
3571: /* Get master organization */
3572: x_master_org := to_number(msd_dem_common_utilities.get_parameter_value(p_sr_instance_id, 'MSD_DEM_MASTER_ORG'));
3573: msd_dem_common_utilities.log_debug ('Source Master Organization from which list price is collected - ' || to_char(x_master_org));
3574:
3575: var_sim_set_id := fnd_profile.value('MSD_DEM_SIMULATION_SET_NAME');
3576: IF (var_sim_set_id IS NULL) THEN

Line 3573: msd_dem_common_utilities.log_debug ('Source Master Organization from which list price is collected - ' || to_char(x_master_org));

3569: msd_dem_common_utilities.log_debug ('Source DB Link - ' || x_dblink);
3570:
3571: /* Get master organization */
3572: x_master_org := to_number(msd_dem_common_utilities.get_parameter_value(p_sr_instance_id, 'MSD_DEM_MASTER_ORG'));
3573: msd_dem_common_utilities.log_debug ('Source Master Organization from which list price is collected - ' || to_char(x_master_org));
3574:
3575: var_sim_set_id := fnd_profile.value('MSD_DEM_SIMULATION_SET_NAME');
3576: IF (var_sim_set_id IS NULL) THEN
3577: var_sim_set_id := nvl(var_sim_set_id,-999);

Line 3579: msd_dem_common_utilities.log_message ('Warning(1): msd_dem_sop.load_cost - Unable to find simulation set name name. Repair Cost will not be loaded.');

3575: var_sim_set_id := fnd_profile.value('MSD_DEM_SIMULATION_SET_NAME');
3576: IF (var_sim_set_id IS NULL) THEN
3577: var_sim_set_id := nvl(var_sim_set_id,-999);
3578: retcode:= 1;
3579: msd_dem_common_utilities.log_message ('Warning(1): msd_dem_sop.load_cost - Unable to find simulation set name name. Repair Cost will not be loaded.');
3580: END IF;
3581:
3582: /*Delete all data (if any) from the staging table */
3583: x_small_sql := 'TRUNCATE TABLE ' || var_schema || '.BIIO_SOP_ITEM_COST';

Line 3638: msd_dem_common_utilities.log_debug ('The query to be executed is -' || sql_statement);

3634: and mia.inventory_item_id(+) = msi.inventory_item_id
3635: and (msi.standard_cost IS NOT NULL OR mis.item_price IS NOT NULL OR mia.repair_cost IS NOT NULL )
3636: group by tei.item';
3637:
3638: msd_dem_common_utilities.log_debug ('The query to be executed is -' || sql_statement);
3639: msd_dem_common_utilities.log_debug ('Executing the query - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
3640:
3641: EXECUTE IMMEDIATE sql_statement;
3642: msd_dem_common_utilities. log_debug ('Executed the query - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));

Line 3639: msd_dem_common_utilities.log_debug ('Executing the query - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));

3635: and (msi.standard_cost IS NOT NULL OR mis.item_price IS NOT NULL OR mia.repair_cost IS NOT NULL )
3636: group by tei.item';
3637:
3638: msd_dem_common_utilities.log_debug ('The query to be executed is -' || sql_statement);
3639: msd_dem_common_utilities.log_debug ('Executing the query - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
3640:
3641: EXECUTE IMMEDIATE sql_statement;
3642: msd_dem_common_utilities. log_debug ('Executed the query - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
3643: msd_dem_common_utilities.log_debug('The number of records inserted is - ' ||SQL%ROWCOUNT);

Line 3642: msd_dem_common_utilities. log_debug ('Executed the query - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));

3638: msd_dem_common_utilities.log_debug ('The query to be executed is -' || sql_statement);
3639: msd_dem_common_utilities.log_debug ('Executing the query - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
3640:
3641: EXECUTE IMMEDIATE sql_statement;
3642: msd_dem_common_utilities. log_debug ('Executed the query - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
3643: msd_dem_common_utilities.log_debug('The number of records inserted is - ' ||SQL%ROWCOUNT);
3644: COMMIT;
3645:
3646: msd_dem_common_utilities.log_debug('The insert statement was successful');

Line 3643: msd_dem_common_utilities.log_debug('The number of records inserted is - ' ||SQL%ROWCOUNT);

3639: msd_dem_common_utilities.log_debug ('Executing the query - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
3640:
3641: EXECUTE IMMEDIATE sql_statement;
3642: msd_dem_common_utilities. log_debug ('Executed the query - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
3643: msd_dem_common_utilities.log_debug('The number of records inserted is - ' ||SQL%ROWCOUNT);
3644: COMMIT;
3645:
3646: msd_dem_common_utilities.log_debug('The insert statement was successful');
3647:

Line 3646: msd_dem_common_utilities.log_debug('The insert statement was successful');

3642: msd_dem_common_utilities. log_debug ('Executed the query - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
3643: msd_dem_common_utilities.log_debug('The number of records inserted is - ' ||SQL%ROWCOUNT);
3644: COMMIT;
3645:
3646: msd_dem_common_utilities.log_debug('The insert statement was successful');
3647:
3648: /* Update Purchase Cost from Source */
3649: sql_statement := 'UPDATE ' || var_schema || '.BIIO_SOP_ITEM_COST target '
3650: || ' SET COST_PURCHASE = nvl((SELECT source.list_price_per_unit FROM mtl_system_items_kfv' || x_dblink || ' source '

Line 3655: msd_dem_common_utilities.log_debug ('The query to be executed is -' || sql_statement);

3651: || ' WHERE source.concatenated_segments = target.level1 '
3652: || ' AND source.organization_id = ' || to_char(x_master_org) || '), target.cost_purchase) '
3653: || ' WHERE cost_purchase IS NULL ';
3654:
3655: msd_dem_common_utilities.log_debug ('The query to be executed is -' || sql_statement);
3656: msd_dem_common_utilities.log_debug ('Executing the query - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
3657:
3658: EXECUTE IMMEDIATE sql_statement;
3659: msd_dem_common_utilities.log_debug ('Executed the query - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));

Line 3656: msd_dem_common_utilities.log_debug ('Executing the query - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));

3652: || ' AND source.organization_id = ' || to_char(x_master_org) || '), target.cost_purchase) '
3653: || ' WHERE cost_purchase IS NULL ';
3654:
3655: msd_dem_common_utilities.log_debug ('The query to be executed is -' || sql_statement);
3656: msd_dem_common_utilities.log_debug ('Executing the query - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
3657:
3658: EXECUTE IMMEDIATE sql_statement;
3659: msd_dem_common_utilities.log_debug ('Executed the query - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
3660: msd_dem_common_utilities.log_debug('The number of records updated is - ' ||SQL%ROWCOUNT);

Line 3659: msd_dem_common_utilities.log_debug ('Executed the query - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));

3655: msd_dem_common_utilities.log_debug ('The query to be executed is -' || sql_statement);
3656: msd_dem_common_utilities.log_debug ('Executing the query - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
3657:
3658: EXECUTE IMMEDIATE sql_statement;
3659: msd_dem_common_utilities.log_debug ('Executed the query - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
3660: msd_dem_common_utilities.log_debug('The number of records updated is - ' ||SQL%ROWCOUNT);
3661: COMMIT;
3662:
3663: msd_dem_common_utilities.log_debug('The update statement was successful');

Line 3660: msd_dem_common_utilities.log_debug('The number of records updated is - ' ||SQL%ROWCOUNT);

3656: msd_dem_common_utilities.log_debug ('Executing the query - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
3657:
3658: EXECUTE IMMEDIATE sql_statement;
3659: msd_dem_common_utilities.log_debug ('Executed the query - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
3660: msd_dem_common_utilities.log_debug('The number of records updated is - ' ||SQL%ROWCOUNT);
3661: COMMIT;
3662:
3663: msd_dem_common_utilities.log_debug('The update statement was successful');
3664:

Line 3663: msd_dem_common_utilities.log_debug('The update statement was successful');

3659: msd_dem_common_utilities.log_debug ('Executed the query - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
3660: msd_dem_common_utilities.log_debug('The number of records updated is - ' ||SQL%ROWCOUNT);
3661: COMMIT;
3662:
3663: msd_dem_common_utilities.log_debug('The update statement was successful');
3664:
3665: IF (launchDownload = 1) THEN
3666: msd_dem_common_utilities.log_debug ('LaunchDownload is set to YES. Launching the workflow');
3667:

Line 3666: msd_dem_common_utilities.log_debug ('LaunchDownload is set to YES. Launching the workflow');

3662:
3663: msd_dem_common_utilities.log_debug('The update statement was successful');
3664:
3665: IF (launchDownload = 1) THEN
3666: msd_dem_common_utilities.log_debug ('LaunchDownload is set to YES. Launching the workflow');
3667:
3668: BEGIN
3669: var_request_id := null;
3670: var_request_id := fnd_request.submit_request ('MSD', 'MSDDEMLDW', NULL, NULL, FALSE,

Line 3672: msd_dem_common_utilities.log_message ('Request Id for "Launch Demantra Workflow" concurrent program - ' || var_request_id);

3668: BEGIN
3669: var_request_id := null;
3670: var_request_id := fnd_request.submit_request ('MSD', 'MSDDEMLDW', NULL, NULL, FALSE,
3671: 'WF_SPF_IMPORT_COSTS');
3672: msd_dem_common_utilities.log_message ('Request Id for "Launch Demantra Workflow" concurrent program - ' || var_request_id);
3673:
3674: EXCEPTION
3675: WHEN OTHERS THEN
3676: retcode := -1;

Line 3678: msd_dem_common_utilities.log_message ('Error(2): msd_dem_sop.load_item_cost - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));

3674: EXCEPTION
3675: WHEN OTHERS THEN
3676: retcode := -1;
3677: errbuf := substr(SQLERRM,1,150);
3678: msd_dem_common_utilities.log_message ('Error(2): msd_dem_sop.load_item_cost - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
3679: msd_dem_common_utilities.log_message ('Error while submitting request for "Launch Demantra Workflow" concurrent program.');
3680: msd_dem_common_utilities.log_message (errbuf);
3681: msd_dem_common_utilities.log_message (' ');
3682: RETURN;

Line 3679: msd_dem_common_utilities.log_message ('Error while submitting request for "Launch Demantra Workflow" concurrent program.');

3675: WHEN OTHERS THEN
3676: retcode := -1;
3677: errbuf := substr(SQLERRM,1,150);
3678: msd_dem_common_utilities.log_message ('Error(2): msd_dem_sop.load_item_cost - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
3679: msd_dem_common_utilities.log_message ('Error while submitting request for "Launch Demantra Workflow" concurrent program.');
3680: msd_dem_common_utilities.log_message (errbuf);
3681: msd_dem_common_utilities.log_message (' ');
3682: RETURN;
3683: END;

Line 3680: msd_dem_common_utilities.log_message (errbuf);

3676: retcode := -1;
3677: errbuf := substr(SQLERRM,1,150);
3678: msd_dem_common_utilities.log_message ('Error(2): msd_dem_sop.load_item_cost - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
3679: msd_dem_common_utilities.log_message ('Error while submitting request for "Launch Demantra Workflow" concurrent program.');
3680: msd_dem_common_utilities.log_message (errbuf);
3681: msd_dem_common_utilities.log_message (' ');
3682: RETURN;
3683: END;
3684:

Line 3681: msd_dem_common_utilities.log_message (' ');

3677: errbuf := substr(SQLERRM,1,150);
3678: msd_dem_common_utilities.log_message ('Error(2): msd_dem_sop.load_item_cost - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
3679: msd_dem_common_utilities.log_message ('Error while submitting request for "Launch Demantra Workflow" concurrent program.');
3680: msd_dem_common_utilities.log_message (errbuf);
3681: msd_dem_common_utilities.log_message (' ');
3682: RETURN;
3683: END;
3684:
3685: ELSE

Line 3686: msd_dem_common_utilities.log_debug ('LaunchDownload set to NO. Not launching the workflow');

3682: RETURN;
3683: END;
3684:
3685: ELSE
3686: msd_dem_common_utilities.log_debug ('LaunchDownload set to NO. Not launching the workflow');
3687: END IF;
3688:
3689: msd_dem_common_utilities.log_debug ('Exiting: msd_dem_sop.load_item_cost - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
3690:

Line 3689: msd_dem_common_utilities.log_debug ('Exiting: msd_dem_sop.load_item_cost - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));

3685: ELSE
3686: msd_dem_common_utilities.log_debug ('LaunchDownload set to NO. Not launching the workflow');
3687: END IF;
3688:
3689: msd_dem_common_utilities.log_debug ('Exiting: msd_dem_sop.load_item_cost - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
3690:
3691: EXCEPTION
3692: WHEN OTHERS THEN
3693: retcode:= -1;

Line 3694: msd_dem_common_utilities.log_message ('Exception: msd_dem_sop.load_cost - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));

3690:
3691: EXCEPTION
3692: WHEN OTHERS THEN
3693: retcode:= -1;
3694: msd_dem_common_utilities.log_message ('Exception: msd_dem_sop.load_cost - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
3695: msd_dem_common_utilities.log_message (substr(SQLERRM,1,150));
3696: Return;
3697: END LOAD_COST;
3698:

Line 3695: msd_dem_common_utilities.log_message (substr(SQLERRM,1,150));

3691: EXCEPTION
3692: WHEN OTHERS THEN
3693: retcode:= -1;
3694: msd_dem_common_utilities.log_message ('Exception: msd_dem_sop.load_cost - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
3695: msd_dem_common_utilities.log_message (substr(SQLERRM,1,150));
3696: Return;
3697: END LOAD_COST;
3698:
3699: