[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
x_sql := 'SELECT tql.lorder ' ||
' FROM ' || x_table_name || ' tql, ';
x_sql := 'SELECT cf.computed_name ' ||
' FROM ' || x_table_name || ' tqs, ';
x_sql :='SELECT tg_res, months_number, inputs_column, is_default ' ||
' FROM ' || x_tgroup_res ||
' WHERE tg_res_id = ' || p_time_res;
x_sql :='SELECT time_bucket, aggregation_method ' ||
' FROM ' || x_dm_wiz_dm_def ||
' WHERE dm_or_template = 2 ' ||
' AND is_active = 1 ' ||
' AND rownum < 2 ';
p_time_from_clause := ' (SELECT min(datet) start_time, max(datet) end_time ' ||
' FROM ' || x_inputs || ' GROUP BY ' || x_inputs_column ||
' ) inp ';
x_sql := 'SELECT count(*) FROM ' || x_inputs || ' WHERE ' || x_inputs_column || ' = 1 ';
x_sql := 'SELECT count(*) FROM ' || x_inputs || ' WHERE ' || x_inputs_column || ' = 1 ';
SELECT min(instance_id)
FROM msc_apps_instances
WHERE instance_type <> 3
AND validation_org_id IS NOT NULL;
select nvl((select 1 from msd_dp_scn_output_levels_v
WHERE demand_plan_id = 5555555
and scenario_id = data_profile_id + 5555555
and level_id = 1),0) from dual;
select nvl((select 1 from msd_dp_scn_output_levels_v
WHERE demand_plan_id = 5555555
and scenario_id = data_profile_id + 5555555
and level_id = 11),0) from dual;
select nvl((select 1 from msd_dp_scn_output_levels_v
WHERE demand_plan_id = 5555555
and scenario_id = data_profile_id + 5555555
and level_id = 7),0) from dual;
SELECT mtil.sr_tp_id
INTO x_sr_customer_pk
FROM
msc_tp_id_lid mtil
WHERE
mtil.sr_cust_account_number = x_account_number
and mtil.sr_instance_id = p_sr_instance_id;
SELECT mr.region_id
INTO x_sr_zone_pk
FROM msc_regions mr
WHERE
mr.zone = x_zone
AND mr.sr_instance_id = p_sr_instance_id;
x_select_clause VARCHAR2(3000) := NULL;
x_insert_clause VARCHAR2(1000) := NULL;
x_sql := 'SELECT id, presentation_type, view_name, ' ||
' time_res_id, unit_id, index_id, data_scale, ' ||
' integration_type, export_type, last_export_date ' ||
' FROM ' || x_table_name ||
' WHERE upper(query_name) = ''' || x_export_data_profile || '''';
x_sql := 'SELECT count(1) FROM dba_objects ' ||
' WHERE owner = upper(''' || x_schema || ''')' ||
' AND object_type IN (''VIEW'', ''MATERIALIZED VIEW'') ' ||
' AND object_name = upper(''' || x_view_name || ''')';
x_select_clause := ' SELECT ' || x_demand_plan_id || ' , ' ||
x_scenario_id || ' , ' ||
' rownum - 1 , ';
x_select_clause := x_select_clause || x_bucket_type || ' , '
|| x_start_time || ' , '
|| x_end_time || ' , ';
x_select_clause := x_select_clause || ' msi.sr_instance_id, ';
x_select_clause := x_select_clause || x_sr_organization_id || ' , ' ||
' msi.sr_inventory_item_id, ';
x_select_clause := x_select_clause || x_sr_ship_to_loc_id || ' , ' ||
x_sr_customer_id || ' , ' ||
x_sr_zone_id || ' , ';
x_select_clause := x_select_clause || x_sr_demand_class || ' , ' ||
' msi.inventory_item_id, ';
x_select_clause := x_select_clause || '''' || x_uom_code || ''', ' ||
' msi.uom_code, ';
x_select_clause := x_select_clause || ' msi.list_price * ((100 - msi.average_discount)/100), ';
x_select_clause := x_select_clause || x_quantity || ' , ';
x_select_clause := x_select_clause || ' NULL , NULL , ';
x_select_clause := x_select_clause || '''' || x_error_type || ''' , exp.' || x_error_column || ' , ';
x_select_clause := x_select_clause || ' NULL , ';
x_select_clause := x_select_clause || ' exp.' || x_demand_priority_column || ' , ';
x_select_clause := x_select_clause || ' NULL , ';
x_select_clause := x_select_clause || ' NULL , ';
x_select_clause := x_select_clause || ' exp.' || x_is_pf_level || ' , ';
x_select_clause := x_select_clause || ' nvl (msi.ato_forecast_control, 3) , ';
x_select_clause := x_select_clause || ' NULL, NULL, NULL, NULL, ';
x_select_clause := x_select_clause || ' exp.acry_mape_spf_insamp * 100, ';
x_select_clause := x_select_clause || ' exp.acry_mape_spf_outsamp * 100, ';
x_select_clause := x_select_clause || ' exp.spf_fore_vol * 100, ';
x_select_clause := x_select_clause || ' exp.spf_glob_prop, ';
x_select_clause := x_select_clause || ' sysdate, ' ||
' FND_GLOBAL.USER_ID, ' ||
' FND_GLOBAL.LOGIN_ID ';
x_insert_clause := 'INSERT INTO MSD_DP_SCN_ENTRIES_DENORM ( ' ||
' DEMAND_PLAN_ID, ' ||
' SCENARIO_ID, ' ||
' DEMAND_ID, ' ||
' BUCKET_TYPE, ' ||
' START_TIME, ' ||
' END_TIME, ' ||
' SR_INSTANCE_ID, ' ||
' SR_ORGANIZATION_ID, ' ||
' SR_INVENTORY_ITEM_ID, ' ||
' SR_SHIP_TO_LOC_ID, ' ||
' SR_CUSTOMER_ID, ' ||
' SR_ZONE_ID, ' ||
' DEMAND_CLASS, ' ||
' INVENTORY_ITEM_ID, ' ||
' DP_UOM_CODE, ' ||
' ASCP_UOM_CODE, ' ||
' UNIT_PRICE, ' ||
' QUANTITY, ' ||
' ERROR_TYPE, ' ||
' FORECAST_ERROR, ' ||
' PRIORITY, ' ||
' PF_NAME, ' || /* sjagathe - Added for Product Family Forecast Support */
' REQUEST_ID, ' || /* sjagathe - Added for Product Family Forecast Support */
' MAPE_IN_SAMPLE, ' || /* sjagathe - Added for SPF Upload Forecast and Metrics */
' MAPE_OUT_SAMPLE, ' || /* sjagathe - Added for SPF Upload Forecast and Metrics */
' FORECAST_VOLATILITY, ' || /* sjagathe - Added for SPF Upload Forecast and Metrics */
' AVG_DEMAND, ' || /* sjagathe - Added for SPF Upload Forecast and Metrics */
' CREATION_DATE, ' ||
' CREATED_BY, ' ||
' LAST_UPDATE_LOGIN )';
x_large_sql := x_insert_clause || x_select_clause || x_from_clause || x_where_clause;
x_inner_view := '(SELECT SDATE, '
|| x_prd_level;
/* Delete all data in the denorm for the export data profile */
DELETE FROM MSD_DP_SCN_ENTRIES_DENORM
WHERE demand_plan_id = x_demand_plan_id
AND scenario_id = x_scenario_id;
/* Insert forecast data into denorm table */
EXECUTE IMMEDIATE x_large_sql;
INSERT INTO MSD_DP_SCN_ENTRIES_DENORM (
DEMAND_PLAN_ID,
SCENARIO_ID,
DEMAND_ID,
BUCKET_TYPE,
START_TIME,
END_TIME,
SR_INSTANCE_ID,
SR_ORGANIZATION_ID,
SR_INVENTORY_ITEM_ID,
SR_SHIP_TO_LOC_ID,
SR_CUSTOMER_ID,
SR_ZONE_ID,
DEMAND_CLASS,
INVENTORY_ITEM_ID,
DP_UOM_CODE,
ASCP_UOM_CODE,
UNIT_PRICE,
QUANTITY,
ERROR_TYPE,
FORECAST_ERROR,
PRIORITY,
PF_NAME,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN )
SELECT /*+ ORDERED */
x_demand_plan_id,
x_scenario_id,
x_num_rows + rownum - 1,
x_bucket_type,
entries.start_time,
entries.end_time,
entries.sr_instance_id,
entries.sr_organization_id,
msi.sr_inventory_item_id,
entries.sr_ship_to_loc_id,
entries.sr_customer_id,
entries.sr_zone_id,
entries.demand_class,
msi.inventory_item_id,
x_uom_code,
msi.uom_code,
msi.list_price * ((100 - msi.average_discount)/100),
entries.quantity,
null,
null,
null,
null,
sysdate,
FND_GLOBAL.USER_ID,
FND_GLOBAL.LOGIN_ID
FROM (SELECT
sr_instance_id,
pf_name,
sr_organization_id,
sr_ship_to_loc_id,
sr_customer_id,
sr_zone_id,
demand_class,
start_time,
end_time,
sum(quantity) QUANTITY
FROM msd_dp_scn_entries_denorm
WHERE scenario_id = x_scenario_id
GROUP BY sr_instance_id,
pf_name,
sr_organization_id,
sr_ship_to_loc_id,
sr_customer_id,
sr_zone_id,
demand_class,
start_time,
end_time) entries,
msc_system_items msi
WHERE msi.plan_id = -1
AND msi.sr_instance_id = entries.sr_instance_id
AND msi.organization_id = entries.sr_organization_id
AND msi.item_name = entries.pf_name;
INSERT INTO MSD_DP_SCN_ENTRIES_DENORM (
DEMAND_PLAN_ID,
SCENARIO_ID,
DEMAND_ID,
BUCKET_TYPE,
START_TIME,
END_TIME,
SR_INSTANCE_ID,
SR_ORGANIZATION_ID,
SR_INVENTORY_ITEM_ID,
SR_SHIP_TO_LOC_ID,
SR_CUSTOMER_ID,
SR_ZONE_ID,
DEMAND_CLASS,
INVENTORY_ITEM_ID,
DP_UOM_CODE,
ASCP_UOM_CODE,
UNIT_PRICE,
QUANTITY,
ERROR_TYPE,
FORECAST_ERROR,
PRIORITY,
PF_NAME,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN )
SELECT /*+ ORDERED */
x_demand_plan_id,
x_scenario_id,
x_num_rows + rownum - 1,
x_bucket_type,
entries.start_time,
entries.end_time,
entries.sr_instance_id,
entries.sr_organization_id,
msi.sr_inventory_item_id,
entries.sr_ship_to_loc_id,
entries.sr_customer_id,
entries.sr_zone_id,
entries.demand_class,
msi.inventory_item_id,
x_uom_code,
msi.uom_code,
msi.list_price * ((100 - msi.average_discount)/100),
entries.quantity,
null,
null,
null,
null,
sysdate,
FND_GLOBAL.USER_ID,
FND_GLOBAL.LOGIN_ID
FROM (SELECT
sr_instance_id,
pf_name,
sr_organization_id,
sr_ship_to_loc_id,
sr_customer_id,
sr_zone_id,
demand_class,
start_time,
end_time,
sum(quantity) QUANTITY
FROM msd_dp_scn_entries_denorm
WHERE scenario_id = x_scenario_id
GROUP BY sr_instance_id,
pf_name,
sr_organization_id,
sr_ship_to_loc_id,
sr_customer_id,
sr_zone_id,
demand_class,
start_time,
end_time) entries,
msc_apps_instances mai,
msc_system_items msi
WHERE mai.instance_id = entries.sr_instance_id
AND msi.plan_id = -1
AND msi.sr_instance_id = mai.instance_id
AND msi.organization_id = mai.validation_org_id
AND msi.item_name = entries.pf_name;
/* Delete Product Family members with forecast control none */
DELETE FROM MSD_DP_SCN_ENTRIES_DENORM
WHERE demand_plan_id = x_demand_plan_id
AND scenario_id = x_scenario_id
AND request_id = 3;
/* Delete all data in the metrics table for the export data profile */
DELETE FROM MSC_DMD_SCN_METRICS
WHERE plan_id = -1
AND scenario_id = x_scenario_id;
/* Aggregate metrics data from denorm and insert into metrics table */
INSERT INTO MSC_DMD_SCN_METRICS NOLOGGING (
PLAN_ID, SCENARIO_ID, INVENTORY_ITEM_ID, ORGANIZATION_ID, SR_INSTANCE_ID,
MAPE_IN_SAMPLE, MAPE_OUT_SAMPLE, FORECAST_VOLATILITY, AVG_DEMAND,
CREATED_BY, CREATION_DATE, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN )
SELECT
-1, x_scenario_id, exp.inventory_item_id, exp.sr_organization_id, exp.sr_instance_id,
avg(exp.mape_in_sample), avg(exp.mape_out_sample), avg(exp.forecast_volatility), avg(exp.avg_demand),
FND_GLOBAL.USER_ID, systimestamp, systimestamp, FND_GLOBAL.USER_ID, FND_GLOBAL.LOGIN_ID
FROM msd_dp_scn_entries_denorm exp
WHERE exp.scenario_id = x_scenario_id
GROUP BY exp.inventory_item_id, exp.sr_organization_id, exp.sr_instance_id;
x_select_clause VARCHAR2(3000) := NULL;
x_insert_clause VARCHAR2(1000) := NULL;
x_sql := 'SELECT id, presentation_type, view_name, ' ||
' time_res_id, unit_id, index_id, data_scale, ' ||
' integration_type, export_type ' ||
' FROM ' || x_table_name ||
' WHERE lower(query_name) = ''' || x_pctg_exp_dp || '''';
x_sql := 'SELECT count(1) FROM dba_objects ' ||
' WHERE owner = upper(''' || x_schema || ''')' ||
' AND object_type IN (''VIEW'', ''MATERIALIZED VIEW'') ' ||
' AND object_name = upper(''' || x_view_name || ''')';
x_sql := 'SELECT id ' ||
' FROM ' || x_table_name ||
' WHERE lower(query_name) = ''' || x_fcst_exp_dp || '''';
x_select_clause := ' SELECT ' || x_demand_plan_id || ' , ' ||
x_scenario_id || ' , ';
x_select_clause := x_select_clause || x_start_time || ' , '
|| x_end_time || ' , ';
x_select_clause := x_select_clause || ' msi.sr_instance_id, ';
x_select_clause := x_select_clause || x_sr_organization_id || ' , ' ||
' msi.inventory_item_id, ';
x_select_clause := x_select_clause || ' MSD_DP_PLANNING_PERCENTAGES_S.nextval, '
|| ' mbc.component_sequence_id, '
|| ' mb.bill_sequence_id, '
|| ' pitem.inventory_item_id, ';
x_select_clause := x_select_clause || x_pctg_column || ' , ';
x_select_clause := x_select_clause || '1, ';
x_select_clause := x_select_clause || ' sysdate, ' ||
' FND_GLOBAL.USER_ID, ' ||
' FND_GLOBAL.LOGIN_ID ';
x_insert_clause := 'INSERT INTO MSD_DP_PLANNING_PCT_DENORM ( ' ||
' DEMAND_PLAN_ID, ' ||
' DP_SCENARIO_ID, ' ||
' DATE_FROM, ' ||
' DATE_TO, ' ||
' SR_INSTANCE_ID, ' ||
' ORGANIZATION_ID, ' ||
' INVENTORY_ITEM_ID, ' ||
' COMPONENT_SEQUENCE_ID, ' ||
' ORIG_COMPONENT_SEQUENCE_ID, ' ||
' BILL_SEQUENCE_ID, ' ||
' ASSEMBLY_ITEM_ID, ' ||
' PLANNING_FACTOR, ' ||
' PLAN_PERCENTAGE_TYPE, ' ||
' CREATION_DATE, ' ||
' CREATED_BY, ' ||
' LAST_UPDATE_LOGIN )';
x_large_sql := x_insert_clause || x_select_clause || x_from_clause || x_where_clause;
x_inner_view := '(SELECT SDATE, '
|| x_prd_level;
/* Delete all data in the denorm for the export data profile */
DELETE FROM MSD_DP_PLANNING_PCT_DENORM
WHERE demand_plan_id = x_demand_plan_id
AND dp_scenario_id = x_scenario_id;
/* Insert planning percentages into denorm table */
EXECUTE IMMEDIATE x_large_sql;
x_small_sql := 'SELECT id FROM ' || x_schema || '.TRANSFER_QUERY WHERE lower(query_name) = :1 ';
EXECUTE IMMEDIATE 'SELECT max(demand_id) FROM msd_dp_scn_entries_denorm WHERE scenario_id = :1'
INTO x_max_demand_id
USING x_ind_scenario_id;
UPDATE msd_dp_scn_entries_denorm
SET scenario_id = x_ind_scenario_id,
demand_id = demand_id + x_max_demand_id
WHERE scenario_id = x_dep_scenario_id;
EXECUTE IMMEDIATE 'SELECT query_name FROM ' || x_schema || '.TRANSFER_QUERY WHERE application_id = :1 '
INTO x_export_data_profile
USING p_export_data_profile_wai;
EXECUTE IMMEDIATE 'SELECT computed_name FROM ' || x_schema || '.COMPUTED_FIELDS WHERE application_id = :1 '
INTO x_ind_fcst_series
USING p_ind_fcst_series_wai;
EXECUTE IMMEDIATE 'SELECT computed_name FROM ' || x_schema || '.COMPUTED_FIELDS WHERE application_id = :1 '
INTO x_dep_fcst_series
USING p_dep_fcst_series_wai;
SELECT ITEM, ORGANIZATION, PARENT_ITEM, TOP_ATO_MODEL,DATE_FROM, DATE_TO, PLANNING_FACTOR
FROM MSD_DP_PLANNING_PCT_TMP ORDER BY SERIAL;
C_INSERT_BATCH_SIZE NUMBER := 120000;
x_select_clause VARCHAR2(4000) := NULL;
x_insert_clause VARCHAR2(1000) := NULL;
x_sql := 'SELECT tq.id, tq.presentation_type, tq.view_name, ' ||
' tq.time_res_id, tq.unit_id, tq.index_id, tq.data_scale, ' ||
' tq.integration_type, tq.export_type, ' ||
' tl.user_id, ph.data_hint ' ||
' FROM ' || x_schema || '.TRANSFER_QUERY tq, ' || x_schema || '.TRANSFER_LIST tl, ' ||
' ' || x_schema || '.PROFILE_HINTS ph ' ||
' WHERE lower(query_name) = ''' || x_pctg_exp_dp || '''' ||
' AND tl.id = tq.transfer_id AND ph.profile_id(+) = tq.id ';
x_sql := 'SELECT count(1) FROM dba_objects ' ||
' WHERE owner = upper(''' || x_schema || ''')' ||
' AND object_type IN (''VIEW'', ''MATERIALIZED VIEW'') ' ||
' AND object_name = upper(''' || x_view_name || ''')';
x_sql := 'SELECT id ' ||
' FROM ' || x_schema || '.transfer_query ' ||
' WHERE lower(query_name) = ''' || x_fcst_exp_dp || '''';
x_select_clause := 'SELECT ' || case when x_profile_hint is not null then '/*+ ' || x_profile_hint || ' */ ' end ||
replace(x_start_time, 'SD', 'min(inp.datet)') || ' as date_from, ' ||
replace(x_end_time, 'ED', 'max(inp.datet)') || ' as date_to, ' ;
x_from_clause := ' FROM (SELECT DISTINCT ITEM_ID, LOCATION_ID, T_EP_CTO_ID ';
x_sql := 'select to_char(min(from_sales_date), ''DD-MON-RRRR'') from ' || x_schema || '.INT_DATE_' || x_profile_id || '_' || x_profile_user;
x_sql := 'select to_char(max(to_sales_date), ''DD-MON-RRRR'') from ' || x_schema || '.INT_DATE_' || x_profile_id || '_' || x_profile_user;
x_sql := 'select lorder from ' || x_schema || '.transfer_query_levels where id = ' || x_profile_id || ' order by lorder';
x_select_clause := x_select_clause || 'T_COMB_LIST.LEVEL' || x_value || ', ';
x_sql := 'select exp_template from ' || x_schema || '.computed_fields where lower(computed_name) = ''' || x_option_series_iname || '''';
x_select_clause := x_select_clause || x_value || ' as ' || x_option_series_iname || ', ';
x_sql := 'select exp_template from ' || x_schema || '.computed_fields where lower(computed_name) = ''' || x_parent_series_iname || '''';
x_select_clause := x_select_clause || x_value || ' as ' || x_parent_series_iname || ', ';
x_sql := 'select exp_template from ' || x_schema || '.computed_fields where lower(computed_name) = ''' || x_pctg_series_iname || '''';
x_select_clause := x_select_clause || x_value || ' as ' || x_pctg_series_iname || ' ';
x_select_clause := x_select_clause || ',decode(dem.ATO_FORECAST_CONTROL,3,NULL,DEM.TOP_ATO_MODEL_ID) AS TOP_ATO_MODEL_ID';
x_select_clause := x_select_clause || ',NULL AS TOP_ATO_MODEL_ID';
x_inner_view := x_inner_view || ') AS ' || x_select_clause || x_from_clause || x_where_clause || x_group_by_clause;
x_insert_clause := 'INSERT into MSD_DP_PLANNING_PCT_TMP' ||
' (SERIAL, ITEM, ORGANIZATION, PARENT_ITEM,TOP_ATO_MODEL,DATE_FROM, DATE_TO, PLANNING_FACTOR) ';
x_select_clause := ' SELECT ROWNUM, ITEM, ORGANIZATION, PARENT_ITEM, TOP_ATO_MODEL,DATE_FROM, DATE_TO, PLN_PCT ' ||
' FROM ( SELECT ' || x_prd_level || ' AS ITEM, ' ||
case when x_is_global_fcst = 1 then '''-1''' else x_org_level end || ' AS ORGANIZATION, ' ||
x_parent_item_level || ' AS PARENT_ITEM, TOP_ATO_MODEL_ID as TOP_ATO_MODEL,DATE_FROM, DATE_TO, ' ||
x_pctg_column || ' AS PLN_PCT ';
x_from_clause := 'FROM ( SELECT DATE_FROM, DATE_TO, ' ||
x_prd_level || ', ' ||
case when x_is_global_fcst = 1 then '' else x_org_level || ', ' end ||
x_parent_item_level || ', TOP_ATO_MODEL_ID , ' ||
case when x_publish_variant = 1 then
'SUM(' || x_parent_series_iname || ') ' || x_parent_series_iname ||
', SUM(' || x_option_series_iname || ') ' || x_option_series_iname || ', '
end ||
'AVG(' || x_pctg_series_iname || ') ' || x_pctg_series_iname || ' FROM ';
x_large_sql := x_insert_clause || x_select_clause || x_from_clause || x_schema || '.' || x_inner_view_name || x_group_by_clause;
/* Insert planning percentages into temp table */
EXECUTE IMMEDIATE x_large_sql;
/* Delete all data in the denorm for the export data profile */
DELETE FROM MSD_DP_PLANNING_PCT_DENORM WHERE demand_plan_id = x_demand_plan_id AND dp_scenario_id = x_scenario_id;
x_insert_clause := 'INSERT INTO MSD_DP_PLANNING_PCT_DENORM ( ' ||
' DEMAND_PLAN_ID, ' ||
' DP_SCENARIO_ID, ' ||
' DATE_FROM, ' ||
' DATE_TO, ' ||
' SR_INSTANCE_ID, ' ||
' ORGANIZATION_ID, ' ||
' INVENTORY_ITEM_ID, ' ||
' COMPONENT_SEQUENCE_ID, ' ||
' ORIG_COMPONENT_SEQUENCE_ID, ' ||
' BILL_SEQUENCE_ID, ' ||
' ASSEMBLY_ITEM_ID, ' ||
' BASE_MODEL_ITEM_ID, ' ||
' PLANNING_FACTOR, ' ||
' PLAN_PERCENTAGE_TYPE, ' ||
' CREATION_DATE, ' ||
' CREATED_BY, ' ||
' LAST_UPDATE_LOGIN ) ';
x_select_clause := ' SELECT ' || x_demand_plan_id || ' , ' ||
x_scenario_id || ' , ' ||
':1, :2, msi.sr_instance_id,' || x_sr_organization_id || ', msi.inventory_item_id, ' ||
'MSD_DP_PLANNING_PERCENTAGES_S.nextval, ' ||
'mbc.component_sequence_id, ' ||
'mb.bill_sequence_id, ' ||
'pitem.inventory_item_id, ' ||
':3,:4, 1, sysdate, FND_GLOBAL.USER_ID, FND_GLOBAL.LOGIN_ID ';
x_large_sql := x_insert_clause || x_select_clause || x_from_clause || x_where_clause;
IF ( PLN_FACTORS.COUNT >= C_INSERT_BATCH_SIZE ) THEN
IF x_is_global_fcst = 1 THEN
FORALL I IN PLN_FACTORS.FIRST..PLN_FACTORS.LAST
EXECUTE IMMEDIATE x_large_sql
using PLN_FACTORS(I).DATE_FROM, PLN_FACTORS(I).DATE_TO, PLN_FACTORS(I).TOP_ATO_MODEL, PLN_FACTORS(I).PLANNING_FACTOR,
PLN_FACTORS(I).ITEM, PLN_FACTORS(I).PARENT_ITEM;
PLN_FACTORS.DELETE;
EXECUTE IMMEDIATE 'SELECT query_name FROM ' || x_schema || '.TRANSFER_QUERY WHERE application_id = :1 '
INTO x_pp_export_data_profile
USING p_pp_export_data_profile_wai;
EXECUTE IMMEDIATE 'SELECT query_name FROM ' || x_schema || '.TRANSFER_QUERY WHERE application_id = :1 '
INTO x_fcst_export_data_profile
USING p_fcst_export_data_profile_wai;
EXECUTE IMMEDIATE 'SELECT computed_name FROM ' || x_schema || '.COMPUTED_FIELDS WHERE application_id = :1 '
INTO x_pctg_series
USING p_pctg_series_wai;
EXECUTE IMMEDIATE 'SELECT computed_name FROM ' || x_schema || '.COMPUTED_FIELDS WHERE application_id = :1 '
INTO x_parent_item_series
USING p_parent_item_series_wai;
EXECUTE IMMEDIATE 'SELECT computed_name FROM ' || x_schema || '.COMPUTED_FIELDS WHERE application_id = :1 '
INTO x_option_item_series
USING p_option_item_series_wai;
EXECUTE IMMEDIATE 'SELECT query_name FROM ' || x_schema || '.TRANSFER_QUERY WHERE application_id = :1 '
INTO x_ind_export_data_profile
USING p_ind_export_data_profile_wai;
EXECUTE IMMEDIATE 'SELECT query_name FROM ' || x_schema || '.TRANSFER_QUERY WHERE application_id = :1 '
INTO x_dep_export_data_profile
USING p_dep_export_data_profile_wai;
EXECUTE IMMEDIATE 'SELECT computed_name FROM ' || x_schema || '.COMPUTED_FIELDS WHERE application_id = :1 '
INTO x_ind_fcst_series
USING p_ind_fcst_series_wai;
EXECUTE IMMEDIATE 'SELECT computed_name FROM ' || x_schema || '.COMPUTED_FIELDS WHERE application_id = :1 '
INTO x_dep_fcst_series
USING p_dep_fcst_series_wai;
* Data is deleted first in the table against the scenario_id
* then re-loaded with the new data.
* Info about the loading of the data is logged in integ_status table.
*/
PROCEDURE UPLOAD_SPF_METRICS (
p_dp_metrics_appid IN VARCHAR2,
p_dp_final_forecast_appid IN VARCHAR2 )
AS
TYPE CUR_TYPE IS REF CURSOR;
x_select_clause VARCHAR2(3000) := NULL;
x_insert_clause VARCHAR2(1000) := NULL;
x_sql := 'SELECT id, presentation_type, view_name, query_name, ' ||
' time_res_id, unit_id, index_id, data_scale, ' ||
' integration_type, export_type, last_export_date ' ||
' FROM ' || x_table_name ||
' WHERE upper(application_id) = ''' || x_dp_metrics_appid || '''';
x_sql := 'SELECT id ' ||
' FROM ' || x_table_name ||
' WHERE upper(application_id) = ''' || x_dp_final_forecast_appid || '''';
x_sql := 'SELECT count(1) FROM dba_objects ' ||
' WHERE owner = upper(''' || x_schema || ''')' ||
' AND object_type IN (''VIEW'', ''MATERIALIZED VIEW'') ' ||
' AND object_name = upper(''' || x_view_name || ''')';
x_select_clause := ' ( SELECT ' || '-1, '
|| x_scenario_id || ', '
|| ' METRICS_TB.inventory_item_id, '
|| ' METRICS_TB.organization_id, '
|| ' METRICS_TB.sr_instance_id, '
|| ' METRICS_TB.acry_mape_spf_insamp, '
|| ' METRICS_TB.acry_mape_spf_outsamp, '
|| ' METRICS_TB.spf_fore_vol, '
|| ' METRICS_TB.spf_glob_prop, '
|| x_fnd_user_id || ', '
|| ' SYSTIMESTAMP, '
|| ' SYSTIMESTAMP, '
|| x_fnd_user_id || ', '
|| ' FND_GLOBAL.LOGIN_ID ' ;
x_from_clause := ' FROM ( SELECT '
|| ' msi.inventory_item_id inventory_item_id, '
|| ' msi.organization_id organization_id, '
|| ' msi.sr_instance_id sr_instance_id, '
|| ' avg(exp.acry_mape_spf_insamp)*100 acry_mape_spf_insamp, '
|| ' avg(exp.acry_mape_spf_outsamp)*100 acry_mape_spf_outsamp, '
|| ' avg(exp.spf_fore_vol)*100 spf_fore_vol, '
|| ' avg(exp.spf_glob_prop) spf_glob_prop '
|| ' FROM ' || x_schema || '.' || x_view_name
|| ' exp, msc_system_items msi, msc_trading_partners mtp_org ';
x_insert_clause := ' INSERT /*+ APPEND */ INTO MSC_DMD_SCN_METRICS nologging ( ' ||
' PLAN_ID, ' ||
' SCENARIO_ID, ' ||
' INVENTORY_ITEM_ID, ' ||
' ORGANIZATION_ID, ' ||
' SR_INSTANCE_ID, ' ||
' MAPE_IN_SAMPLE, ' ||
' MAPE_OUT_SAMPLE, ' ||
' FORECAST_VOLATILITY, ' ||
' AVG_DEMAND, '||
' CREATED_BY, ' ||
' CREATION_DATE, ' ||
' LAST_UPDATE_DATE, ' ||
' LAST_UPDATED_BY, ' ||
' LAST_UPDATE_LOGIN ) ';
x_large_sql := x_insert_clause || x_select_clause || x_from_clause || x_where_clause;
/* Delete all data in the denorm for the export data profile */
DELETE FROM MSC_DMD_SCN_METRICS
WHERE plan_id = -1
AND scenario_id = x_scenario_id;
/* Insert metrics into MSC_DMD_SCN_METRICS table */
EXECUTE IMMEDIATE x_large_sql;
* then updates msd_dp_scn_entries_denorm (quantity column) and
* msc_dmd_scn_metrics (mape_in_sample,mape_out_sample,forecast_volatility,
* avg_demand columns) tables with new values in the 5 series
* (SPF Final Forecast, SPF Forecast MAPE (In Sample), SPF Forecast MAPE (Out of Sample),
* SPF Forecast Volatility, SPF Average Demand)
* Arguments -
* 1. SPF Upload Final Forecast data profile appl id
* 2. SPF Upload Metrics data profile appl id
* 3. SPF Final Forecast series appl id
*/
PROCEDURE UPLOAD_SPF_INC_FORECAST (
p_dp_final_forecast_appid IN VARCHAR2,
p_dp_metrics_appid IN VARCHAR2,
p_ff_series_appid IN VARCHAR2 DEFAULT NULL)
AS
TYPE CUR_TYPE IS REF CURSOR;
x_select_clause VARCHAR2(3000) := NULL;
x_insert_clause VARCHAR2(1000) := NULL;
x_sql := 'SELECT id, presentation_type, view_name, query_name, ' ||
' time_res_id, unit_id, index_id, data_scale, ' ||
' integration_type, export_type, last_export_date ' ||
' FROM ' || x_table_name ||
' WHERE upper(application_id) = ''' || x_dp_metrics_appid || '''';
x_sql := 'SELECT id, presentation_type, view_name, query_name, ' ||
' time_res_id, unit_id, index_id, data_scale, ' ||
' integration_type, export_type, last_export_date ' ||
' FROM ' || x_table_name ||
' WHERE upper(application_id) = ''' || x_dp_final_forecast_appid || '''';
x_sql := 'SELECT count(1) FROM dba_objects ' ||
' WHERE owner = upper(''' || x_schema || ''')' ||
' AND object_type IN (''VIEW'', ''MATERIALIZED VIEW'') ' ||
' AND object_name = upper(''' || x_metrics_view_name || ''')';
x_sql := 'SELECT count(1) FROM dba_objects ' ||
' WHERE owner = upper(''' || x_schema || ''')' ||
' AND object_type IN (''VIEW'', ''MATERIALIZED VIEW'') ' ||
' AND object_name = upper(''' || x_ffcast_view_name || ''')';
x_small_sql := ' SELECT TO_CHAR(MAX(STATUS_DATE),''DD-MM-YYYY HH24-MI-SS'') FROM ' || x_schema || '.INTEG_STATUS '
|| ' WHERE SUBSTR(STAGE,9) = ''' || x_ffcast_query_name || ''''
|| ' AND upper(STATUS) = ''SUCCEEDED'''
|| ' AND upper(PROCESS) = ''UPLOAD FORECAST''' ;
x_insert_clause := ' Insert /*+ APPEND */ into MSD_SPF_MATRIX_COMB nologging ('
|| ' T_EP_ITEM_EP_ID, '
|| ' T_EP_ORGANIZATION_EP_ID, '
|| ' SDATE, '
|| ' INVENTORY_ITEM_ID, '
|| ' SR_ORGANIZATION_ID, '
|| ' SR_INSTANCE_ID ) ';
x_select_clause := ' ( SELECT ITEMS.T_EP_ITEM_EP_ID, LOC.T_EP_ORGANIZATION_EP_ID, SPF.SALES_DATE, '
|| 'TEI.EBS_ITEM_DEST_KEY, MTP_ORG.SR_TP_ID, MTP_ORG.SR_INSTANCE_ID ' ;
x_where_clause := ' WHERE SPF.LAST_UPDATE_DATE > to_date(''' || x_last_upld_time || ''',''DD-MM-YYYY HH24-MI-SS'') '
|| ' AND SPF.ITEM_ID = ITEMS.ITEM_ID '
|| ' AND TEI.T_EP_ITEM_EP_ID = ITEMS.T_EP_ITEM_EP_ID '
|| ' AND SPF.LOCATION_ID = LOC.LOCATION_ID '
|| ' AND ORG.T_EP_ORGANIZATION_EP_ID = LOC.T_EP_ORGANIZATION_EP_ID '
|| ' AND MTP_ORG.PARTNER_TYPE = 3 '
|| ' AND MTP_ORG.ORGANIZATION_CODE = ORG.ORGANIZATION '
|| ' GROUP BY ITEMS.T_EP_ITEM_EP_ID, LOC.T_EP_ORGANIZATION_EP_ID, SPF.SALES_DATE, '
|| ' TEI.EBS_ITEM_DEST_KEY, MTP_ORG.SR_TP_ID, MTP_ORG.SR_INSTANCE_ID )' ;
x_large_sql := x_insert_clause || x_select_clause || x_from_clause || x_where_clause;
/* Insert item/org combinations into MSD_SPF_MATRIX_COMB table */
EXECUTE IMMEDIATE x_large_sql ;
x_small_sql := ' select replace(lower(' || x_schema || '.GET_EXP_WITH_NO_TOKEN(exp_template)),''safe_division'','''
|| x_schema || '.safe_division'') from '
|| x_schema || '.COMPUTED_FIELDS where application_id = ''COMPUTED_FIELD:4330''';
x_small_sql := ' select replace(lower(' || x_schema || '.GET_EXP_WITH_NO_TOKEN(exp_template)),''safe_division'','''
|| x_schema || '.safe_division'') from '
|| x_schema || '.COMPUTED_FIELDS where application_id = ''COMPUTED_FIELD:4329''';
x_small_sql := ' select replace(lower(' || x_schema || '.GET_EXP_WITH_NO_TOKEN(exp_template)),''safe_division'','''
|| x_schema || '.safe_division'') from '
|| x_schema || '.COMPUTED_FIELDS where application_id = ''COMPUTED_FIELD:4328''';
x_small_sql := ' select replace(lower(' || x_schema || '.GET_EXP_WITH_NO_TOKEN(exp_template)),''safe_division'','''
|| x_schema || '.safe_division'') from '
|| x_schema || '.COMPUTED_FIELDS where application_id = '|| '''' || p_ff_series_appid || '''' ;
x_small_sql := ' select replace(lower(' || x_schema || '.GET_EXP_WITH_NO_TOKEN(exp_template)),''safe_division'','''
|| x_schema || '.safe_division'') from '
|| x_schema || '.COMPUTED_FIELDS where application_id = ''COMPUTED_FIELD:4327''';
x_small_sql := ' select replace(lower(' || x_schema || '.GET_EXP_WITH_NO_TOKEN(exp_template)),''safe_division'','''
|| x_schema || '.safe_division'') from '
|| x_schema || '.COMPUTED_FIELDS where application_id = ''COMPUTED_FIELD:4326''';
x_small_sql := 'select to_char(FH2.START_FORECAST_DATE,''DD-MM-YYYY''),to_char(FH2.LAST_FORECAST_DATE,''DD-MM-YYYY'') from ' || x_schema || '.FORECAST_HISTORY FH2 '
|| ' where fh2.time_sig = (select max(fh.TIME_SIG) from '
|| x_schema || '.FORECAST_HISTORY FH, ' || x_schema || '.ENGINE_PROFILES EP '
|| ' where EP.ENGINE_PROFILES_ID = FH.ENGINE_PROFILES_ID '
|| ' and EP.APPLICATION_ID = ''ENGINE_PROFILE:121'')' ;
x_sql := ' SELECT METRICS_TBL.inventory_item_id, '
|| ' METRICS_TBL.sr_organization_id, '
|| ' METRICS_TBL.sr_instance_id, '
|| ' avg(METRICS_TBL.spf_fmin)*100, '
|| ' avg(METRICS_TBL.spf_fmout)*100, '
|| ' avg(METRICS_TBL.spf_fv)*100, '
|| ' avg(METRICS_TBL.spf_ad) '
|| ' FROM (SELECT comb.inventory_item_id inventory_item_id, '
|| ' comb.sr_organization_id sr_organization_id, '
|| ' comb.sr_instance_id sr_instance_id, '
|| ' branch_data.sales_date, '
|| x_se_spf_fmin ||' spf_fmin, '
|| x_se_spf_fmout ||' spf_fmout, '
|| x_se_spf_fv ||' spf_fv, '
|| x_se_spf_ad ||' spf_ad '
|| ' FROM (select inventory_item_id, sr_organization_id, sr_instance_id, '
|| ' t_ep_item_ep_id, t_ep_organization_ep_id '
|| ' from msd_spf_matrix_comb '
|| ' group by inventory_item_id, sr_organization_id, sr_instance_id, '
|| ' t_ep_item_ep_id, t_ep_organization_ep_id ) comb, '
|| x_schema || '.t_ep_spf_data branch_data, '
|| x_schema || '.t_ep_spf_matrix, '
|| x_schema || '.items, '
|| x_schema || '.location loc'
|| ' WHERE comb.t_ep_item_ep_id = items.t_ep_item_ep_id'
|| ' AND comb.t_ep_organization_ep_id = loc.t_ep_organization_ep_id'
|| ' AND branch_data.t_ep_spf_id = t_ep_spf_matrix.t_ep_spf_id '
|| ' AND branch_data.item_id = items.item_id'
|| ' AND branch_data.sales_date BETWEEN to_date(''' || x_fcst_start_date || ''',''DD-MM-YYYY'')'
|| ' AND to_date(''' || x_fcst_last_date || ''',''DD-MM-YYYY'')'
|| ' AND branch_data.location_id = loc.location_id '
|| ' AND t_ep_spf_matrix.item_id = items.item_id'
|| ' AND t_ep_spf_matrix.location_id = loc.location_id '
|| ' GROUP BY comb.inventory_item_id, comb.sr_organization_id, comb.sr_instance_id, branch_data.sales_date) METRICS_TBL'
|| ' GROUP BY metrics_tbl.inventory_item_id, metrics_tbl.sr_organization_id, metrics_tbl.sr_instance_id ';
/* BULK update 4 series(metrics) columns of msc_dmd_scn_metrics table */
/*FORALL i IN metrics_table.first..metrics_table.last
UPDATE msc_dmd_scn_metrics
SET
mape_in_sample = metrics_table(i).metrics_mape,
mape_out_sample = metrics_table(i).acry_mape_spf_outsamp ,
forecast_volatility = metrics_table(i).spf_fore_vol ,
avg_demand = metrics_table(i).glob_prop,
last_update_date = SYSTIMESTAMP,
last_updated_by = x_fnd_user_id,
last_update_login = x_fnd_login_id
WHERE
plan_id = -1
AND inventory_item_id = metrics_table(i).inventory_item_id
AND organization_id = metrics_table(i).organization_id
AND sr_instance_id = metrics_table(i).sr_instance_id
AND scenario_id = x_ffcast_profile_id + C_SCENARIO_ID_OFFSET ;*/
UPDATE msc_dmd_scn_metrics
SET
mape_in_sample = metrics_table(i).metrics_mape,
mape_out_sample = metrics_table(i).acry_mape_spf_outsamp ,
forecast_volatility = metrics_table(i).spf_fore_vol ,
avg_demand = metrics_table(i).glob_prop,
last_update_date = SYSTIMESTAMP,
last_updated_by = x_fnd_user_id,
last_update_login = x_fnd_login_id
WHERE
plan_id = -1
AND inventory_item_id = metrics_table(i).inventory_item_id
AND organization_id = metrics_table(i).organization_id
AND sr_instance_id = metrics_table(i).sr_instance_id
AND scenario_id = x_ffcast_profile_id + C_SCENARIO_ID_OFFSET ;
x_sql := ' SELECT comb.inventory_item_id, comb.sr_organization_id, comb.sr_instance_id, comb.sdate, '
|| x_se_spf_ff
|| ' FROM msd_spf_matrix_comb comb, '
|| x_schema || '.t_ep_spf_data branch_data, '
|| x_schema || '.t_ep_spf_matrix, '
|| x_schema || '.items, '
|| x_schema || '.location loc'
|| ' WHERE comb.t_ep_item_ep_id = items.t_ep_item_ep_id'
|| ' AND comb.t_ep_organization_ep_id = loc.t_ep_organization_ep_id'
|| ' AND branch_data.t_ep_spf_id = t_ep_spf_matrix.t_ep_spf_id '
|| ' and branch_data.item_id = t_ep_spf_matrix.item_id '
|| ' and branch_data.location_id = t_ep_spf_matrix.location_id '
|| ' AND comb.sdate = branch_data.sales_date '
|| ' AND comb.sdate = branch_data.sales_date '
|| ' AND branch_data.item_id = items.item_id'
|| ' AND branch_data.location_id = loc.location_id '
|| ' GROUP BY comb.inventory_item_id, comb.sr_organization_id, comb.sr_instance_id, comb.sdate' ;
/* BULK update qty column of msd_dp_scn_entries_denorm table */
/*FORALL i IN forecast_table.first..forecast_table.last
UPDATE msd_dp_scn_entries_denorm
SET
quantity = forecast_table(i).fcst_spf_final,
last_update_login = x_fnd_login_id
WHERE
inventory_item_id = forecast_table(i).inventory_item_id
AND sr_organization_id = forecast_table(i).sr_organization_id
AND sr_instance_id = forecast_table(i).sr_instance_id
AND start_time = forecast_table(i).start_time
AND scenario_id = x_ffcast_profile_id + C_SCENARIO_ID_OFFSET ;*/
UPDATE msd_dp_scn_entries_denorm
SET
quantity = forecast_table(i).fcst_spf_final,
last_update_login = x_fnd_login_id
WHERE
inventory_item_id = forecast_table(i).inventory_item_id
AND sr_organization_id = forecast_table(i).sr_organization_id
AND sr_instance_id = forecast_table(i).sr_instance_id
AND start_time = forecast_table(i).start_time
AND scenario_id = x_ffcast_profile_id + C_SCENARIO_ID_OFFSET ;
x_sql := 'SELECT ''Running'' FROM ' || x_dem_schema
|| '.WF_PROCESS_LOG WHERE SCHEMA_ID = (select schema_id from ' || x_dem_schema
|| '.wf_schemas where application_id = ''' || p_wf_appid || ''')'
|| ' AND status = 1 '
|| ' AND step_id <> ''' || p_wf_stepid || ''''
|| ' AND rownum = 1 ' ;
x_sql := 'SELECT TRIM(QUERY_NAME) FROM ' ||x_dem_schema
|| '.TRANSFER_QUERY WHERE APPLICATION_ID = '''
|| p_dp_final_forecast_appid || '''';
x_sql := ' SELECT MAX(STATUS_DATE) FROM ' || x_dem_schema
|| '.INTEG_STATUS WHERE SUBSTR(STAGE,9) = ''' || x_dp_name || ''''
|| ' AND upper(STATUS) = ''SUCCEEDED'''
|| ' AND upper(PROCESS) = ''UPLOAD FORECAST''';
x_sql := ' select max(fh.TIME_SIG) from '
|| x_dem_schema || '.FORECAST_HISTORY FH, ' || x_dem_schema || '.ENGINE_PROFILES EP '
|| ' where EP.ENGINE_PROFILES_ID = FH.ENGINE_PROFILES_ID '
|| ' and EP.APPLICATION_ID = ''ENGINE_PROFILE:121''';
var_sql := 'SELECT phase_code, status_code FROM fnd_concurrent_requests' || p_db_link || ' WHERE request_id = :req_id';
* 2. Get column names for levels selected in DP, Default:"SPF-cMRO Export Failure Rates"
* 3. Delete from AHL_PLANNING_FACTORS table on source
* 4. Insert into AHL_PLANNING_FACTORS table on source
*
* ------------ PARAMETERS LIST ----------------
* p_sr_instance_id : Instance Id
* p_export_data_profile : Demantra Export data profile appl id
* p_workflow_lookup_code : Demantra Workflow EBS lookup code
*
*/
PROCEDURE UPLOAD_CMRO_PLN_FCTRS (
errbuf OUT NOCOPY VARCHAR2,
retcode OUT NOCOPY VARCHAR2,
p_sr_instance_id IN NUMBER,
p_export_data_profile IN VARCHAR2,
p_workflow_lookup_code IN VARCHAR2 ,
p_synchronous IN NUMBER DEFAULT C_YES,
p_check_interval IN NUMBER DEFAULT 60,
p_time_out IN NUMBER DEFAULT 1440
)
IS
/*** DATA TYPES ***/
TYPE LVL_REC IS RECORD (level_name VARCHAR2(100));
x_select_clause VARCHAR2(3000) := NULL;
x_insert_clause VARCHAR2(1000) := NULL;
IF (g_req_table.COUNT <> 0) THEN g_req_table.DELETE; END IF;
/* START - Fetch the levels selected in the 'SPF-cMRO Export Failure Rates' data profile. */
l_sql := 'SELECT NVL(SUM(id),0) FROM ' || x_dem_schema|| '.transfer_query WHERE '
|| 'upper(application_id) = upper(''' || p_export_data_profile || ''') ' ;
l_sql := 'SELECT gt.table_label FROM '
|| x_dem_schema|| '.transfer_query tq, '
|| x_dem_schema|| '.transfer_query_levels tql, '
|| x_dem_schema|| '.group_tables gt '
|| 'WHERE '
|| 'upper(tq.application_id) = upper(''' || p_export_data_profile || ''') '
|| 'AND tql.id = tq.id '
|| 'AND gt.group_table_id = tql.level_id order by lorder ' ;
msd_dem_common_utilities.log_debug ('Levels selected in Data Profile.');
l_sql := 'SELECT id, presentation_type, view_name, query_name, ' ||
' time_res_id, unit_id, index_id, data_scale, ' ||
' integration_type, export_type, last_export_date ' ||
' FROM ' || x_table_name ||
' WHERE upper(application_id) = upper(''' || p_export_data_profile || ''') ';
x_small_sql := 'SELECT count(1) FROM ' || x_dem_schema || '.transfer_query_series WHERE id = '|| x_profile_id;
l_sql := 'SELECT computed_name FROM ' || x_dem_schema || '.computed_fields cf, ' || x_dem_schema || '.transfer_query_series tqs'
|| ' WHERE tqs.id = '|| x_profile_id || ' AND cf.forecast_type_id = tqs.series_id ';
l_sql := 'SELECT count(1) FROM dba_objects ' ||
' WHERE owner = upper(''' || x_dem_schema || ''')' ||
' AND object_type IN (''VIEW'', ''MATERIALIZED VIEW'') ' ||
' AND object_name = upper(''' || x_view_name || ''')';
/* END - Fetch the levels selected in the 'Export SPF Planning factors' data profile. */
/*** START - Check basic error conditions ***/
IF (x_profile_id IS NULL)
THEN
raise_application_error (-20004, 'Error: msd_dem_upload_forecast.upload_cmro_pln_fctrs - Unable to get export data profile id');
l_sql := 'select to_number(parameter_value) from msd_dem_setup_parameters where parameter_name like ''MSD_SPF_MASTER_ORG''';
x_select_clause := ' SELECT inner_qry.* FROM( SELECT distinct ' || x_MI_level || 'MI, ' ;
x_select_clause := x_select_clause || ' decode(' || x_SPF_MT_level || ',''0'',NULL,''-777'',NULL,lkup_MT.lookup_code) MT, ' ;
x_select_clause := x_select_clause || 'decode(' || x_AG_level || ',''0'',NULL,''-777'',NULL,'|| x_AG_level || ') AG, ' ;
x_select_clause := x_select_clause || 'NULL' || ' AG, ' ;
x_select_clause := x_select_clause || 'decode(' || x_AGA1_level || ',''0'',NULL,''-777'',NULL,'|| x_AGA1_level || ') AGA1, ' ;
x_select_clause := x_select_clause || 'NULL' || ' AGA1, ' ;
x_select_clause := x_select_clause || ' decode(' || x_AGA2_level || ',''0'',NULL,''-777'',NULL,lkup_AG.lookup_code) AGA2, ' ;
x_select_clause := x_select_clause || 'NULL' || ' AGA2, ' ;
x_select_clause := x_select_clause || 'decode(' || x_CC_level || ',''0'',NULL,''-777'',NULL,'|| x_CC_level || ') CC, ' ;
x_select_clause := x_select_clause || 'NULL' || ' CC, ' ;
x_select_clause := x_select_clause || 'decode(' || x_SPF_VT_level || ',''0'',NULL,''-777'',NULL,lkup_vt.lookup_code) VT, ' ;
x_select_clause := x_select_clause || 'decode(' || x_SPF_VST_level || ',''0'',NULL,''-777'',NULL,'|| x_SPF_VST_level || ') VST, ' ;
x_select_clause := x_select_clause || 'NULL' || ' VST, ' ;
x_select_clause := x_select_clause || 'decode(' || x_IT_level || ',''Material'',''I'',''Product'',''I'',''Resource'',''R'')' || ' IT, ' ;
x_select_clause := REPLACE(x_select_clause, x_MI_level, 'decode(' || x_IT_level || ',''Resource'',' || x_MI_level || ',NULL)');
x_select_clause := x_select_clause
|| ' msi.sr_inventory_item_id SR_INV_ITEM_ID, '
|| x_series_name || ', '
|| 'decode(' || x_IT_level || ',''Material'',msi.uom_code,''Product'',msi.uom_code,''Resource'',NULL)' || ' UOM, '
|| '''MSD''' || ', '
|| ' systimestamp LAST_UPDATE_DATE, '
|| x_fnd_user_id || ' LAST_UPDATED_BY, '
|| ' systimestamp CREATION_DATE, '
|| x_fnd_user_id || ' CREATED_BY, '
|| ' fnd_global.login_id ' ;
|| ' (select meaning,lookup_code from fnd_lookup_values_vl' || var_D2S_dblink ||' where lookup_type = ''AHL_MAINTENANCE_SOURCE_TYPE'') lkup_MT '
|| ',(select meaning,lookup_code from fnd_lookup_values_vl' || var_D2S_dblink ||' where lookup_type = ''AHL_PLANNING_VISIT_TYPE'') lkup_vt ';
x_from_clause := x_from_clause || ', (select meaning,lookup_code from fnd_lookup_values_vl' || var_D2S_dblink
||' where lookup_type = ''AHL_FLT_OPERATIONS_TYPE'') lkup_ag ';
x_from_clause := x_from_clause || ', (select meaning,lookup_code from fnd_lookup_values_vl' || var_D2S_dblink
||' where lookup_type = ''AHL_VWP_STAGE_TYPE'') lkup_vst ';
x_insert_clause := ' INSERT INTO AHL_PLANNING_FACTORS' || var_D2S_dblink || ' (' ||
' CMRO_RESOURCE_NAME, ' || -- master_item level
' MAINTENANCE_TYPE_CODE, ' || -- spf_maintenance_type level
' FLEET_NAME, ' || -- asset_group level
' OPERATING_ORG_ID,' || -- asset_group_attribute_1 level
' OPERATIONS_TYPE_CODE, ' || -- asset_group_attribute_2 level
' MR_TITLE, ' || -- class_code level
' VISIT_TYPE_CODE, ' || -- spf_visit_type level
' VISIT_STAGE_TYPE_CODE, ' || -- spf_visit_stage_type level
' ITEM_RESOURCE_FLAG, ' || -- item_type level
' INVENTORY_ITEM_ID, ' ||
' USAGE, ' ||
' UOM_CODE, ' ||
' SOURCE_APPLICATION, ' ||
' LAST_UPDATE_DATE, '||
' LAST_UPDATED_BY, ' ||
' CREATION_DATE, ' ||
' CREATED_BY, ' ||
' LAST_UPDATE_LOGIN ) ' ;
l_large_sql := x_insert_clause || x_select_clause || x_from_clause || x_where_clause ;
/* START - Delete from AHL_PLANNING_FACTORS table */
x_small_sql := 'Delete from AHL_PLANNING_FACTORS' || var_D2S_dblink ;
msd_dem_common_utilities.log_debug ('Delete sql - ' || x_small_sql);
/* END - Delete from AHL_PLANNING_FACTORS table */
/* To bypass ORA-02069 error */
x_small_sql := 'ALTER SESSION SET GLOBAL_NAMES = TRUE';
/* START - Inserting into AHL_PLANNING_FACTORS table */
msd_dem_common_utilities.log_debug (' ');
msd_dem_common_utilities.log_debug ('Executing insert sql - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
msd_dem_common_utilities.log_debug ('Executed insert sql - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
/* END - Inserting into AHL_PLANNING_FACTORS table */
msd_dem_common_utilities.log_debug ('Exiting: msd_dem_upload_forecast.upload_cmro_pln_fctrs - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
EXECUTE IMMEDIATE 'SELECT query_name FROM ' || x_schema || '.TRANSFER_QUERY WHERE application_id = :1 '
INTO x_ind_export_data_profile
USING p_ind_export_data_profile_wai;
EXECUTE IMMEDIATE 'SELECT query_name FROM ' || x_schema || '.TRANSFER_QUERY WHERE application_id = :1 '
INTO x_wod_export_data_profile
USING p_wod_export_data_profile_wai;
EXECUTE IMMEDIATE 'SELECT computed_name FROM ' || x_schema || '.COMPUTED_FIELDS WHERE application_id = :1 '
INTO x_ind_fcst_series
USING p_ind_fcst_series_wai;
EXECUTE IMMEDIATE 'SELECT computed_name FROM ' || x_schema || '.COMPUTED_FIELDS WHERE application_id = :1 '
INTO x_wod_fcst_series
USING p_wod_fcst_series_wai;
x_small_sql := 'SELECT id FROM ' || x_schema || '.TRANSFER_QUERY WHERE lower(query_name) = :1 ';
/* First update the forecast error value for work order data profile's data in the denorm table before merge */
EXECUTE IMMEDIATE 'SELECT dbname FROM ' || x_schema || '.computed_fields WHERE application_id = :1'
INTO x_fcst_acry_column
USING p_wod_fcst_acry_series_wai;
EXECUTE IMMEDIATE ' UPDATE MSD_DP_SCN_ENTRIES_DENORM d '
|| ' SET forecast_error = nvl((SELECT 100 * ' || x_fcst_acry_column
|| ' FROM msc_trading_partners mtp, '
|| ' msc_system_items msi, '
|| x_schema || '.T_EP_ITEM tei, '
|| x_schema || '.T_EP_ORGANIZATION teo, '
|| x_schema || '.MDP_MATRIX mm '
|| ' WHERE mtp.partner_type = 3 '
|| ' AND mtp.sr_instance_id = d.sr_instance_id '
|| ' AND mtp.sr_tp_id = d.sr_organization_id '
|| ' AND msi.plan_id = -1 '
|| ' AND msi.sr_instance_id = d.sr_instance_id '
|| ' AND msi.organization_id = d.sr_organization_id '
|| ' AND msi.sr_inventory_item_id = d.sr_inventory_item_id '
|| ' AND tei.item = msi.item_name '
|| ' AND tei.t_ep_i_att_10_ep_id = 1 '
|| ' AND teo.organization = mtp.organization_code '
|| ' AND mm.t_ep_item_ep_id = tei.t_ep_item_ep_id '
|| ' AND mm.t_ep_organization_ep_id = teo.t_ep_organization_ep_id '
|| ' AND rownum < 2 ), forecast_error) '
|| ' WHERE d.scenario_id = ' || x_wod_scenario_id;
EXECUTE IMMEDIATE 'SELECT max(demand_id) FROM msd_dp_scn_entries_denorm WHERE scenario_id = :1'
INTO x_max_demand_id
USING x_ind_scenario_id;
USING (SELECT rownum rn, demand_plan_id, scenario_id, demand_id, bucket_type, start_time, end_time, quantity,
sr_organization_id, sr_instance_id, sr_inventory_item_id, error_type, forecast_error, inventory_item_id,
dp_uom_code, ascp_uom_code, unit_price, creation_date, created_by, last_update_login
FROM msd_dp_scn_entries_denorm
WHERE scenario_id = x_wod_scenario_id) d2
ON ( d1.scenario_id = x_ind_scenario_id
AND d1.sr_organization_id = d2.sr_organization_id
AND d1.sr_instance_id = d2.sr_instance_id
AND d1.inventory_item_id = d2.inventory_item_id
AND d1.start_time = d2.start_time )
WHEN MATCHED THEN
UPDATE
SET d1.quantity = d1.quantity + d2.quantity,
d1.forecast_error = (d1.forecast_error + d2.forecast_error)/2
WHEN NOT MATCHED THEN
INSERT (DEMAND_PLAN_ID, SCENARIO_ID, DEMAND_ID, BUCKET_TYPE, START_TIME, END_TIME, QUANTITY,
SR_ORGANIZATION_ID, SR_INSTANCE_ID, SR_INVENTORY_ITEM_ID, ERROR_TYPE, FORECAST_ERROR, INVENTORY_ITEM_ID,
DP_UOM_CODE, ASCP_UOM_CODE, UNIT_PRICE, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN)
VALUES (d2.demand_plan_id, x_ind_scenario_id, x_max_demand_id + d2.rn, d2.bucket_type, d2.start_time, d2.end_time, d2.quantity,
d2.sr_organization_id, d2.sr_instance_id, d2.sr_inventory_item_id, d2.error_type, d2.forecast_error, d2.inventory_item_id,
d2.dp_uom_code, d2.ascp_uom_code, d2.unit_price, d2.creation_date, d2.created_by, d2.last_update_login);
/* Delete the forecast data with scenario id of x_wod_scenario_id */
DELETE FROM msd_dp_scn_entries_denorm
WHERE scenario_id = x_wod_scenario_id;
EXECUTE IMMEDIATE 'SELECT query_name FROM ' || x_schema || '.TRANSFER_QUERY WHERE application_id = :1 '
INTO x_export_data_profile
USING p_dp_spf_fcst_metrics_appid;
EXECUTE IMMEDIATE 'SELECT computed_name FROM ' || x_schema || '.COMPUTED_FIELDS WHERE application_id = :1 '
INTO x_acry_series_iname
USING p_acry_series_wai;