[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_insert varchar2(3000) := 'INSERT ';
l_select varchar2(3000) := 'SELECT ';
SELECT roundoff_decimal_places
FROM msd_demand_plans
WHERE demand_plan_id = p_demand_plan_id;
select sr_level_pk
from msd_level_values
where level_pk = p_lvl_pk;
select output_period_type into v_time_lvl_id from msd_dp_scenarios
where demand_plan_id = p_demand_plan_id and scenario_id = p_scenario_id;
select m2a_dblink into v_dblink from msc_apps_instances
where instance_id = p_instance_id;
select sr_level_pk
into v_customer_id
from msd_level_values
where level_pk = p_customer_id;
select sr_level_pk
into v_location_id
from msd_level_values
where level_pk = p_location_id;
for v_org_id in (select lv.sr_level_pk
from msd_level_values lv,
(select organization_lvl_pk opk
from msd_dp_scenario_entries
where demand_plan_id = p_demand_plan_id
and scenario_id = p_scenario_id
and revision = p_revision
group by organization_lvl_pk) sce
where lv.level_pk = sce.opk ) LOOP
BEGIN /* Begin of 2 */
/* If user selects organization as a filter condition
then populate forecast set only for this org */
l_sr_org_lvl_pk := NULL;
/* If user selects demand class as filter condition */
l_sr_dcs_lvl_pk := NULL;
select APPS_VER
into l_source_apps_version
from msc_apps_instances
where instance_id = p_instance_id;
v_sql_stmt := ' SELECT'
|| ' process_enabled_flag'
|| ' FROM'
|| ' mtl_parameters'|| v_dblink
|| ' WHERE'
|| ' organization_id = :l_org_id ';
v_sql_stmt := 'select count(*) from mrp_forecast_designators'|| v_dblink ||
' where forecast_designator = :l_forecast_designator '||
' and organization_id = :l_org_id ';
v_sql_stmt := 'insert into mrp_forecast_designators'
|| v_dblink || '( ' ||
'forecast_designator,' ||
'organization_id,' ||
'forecast_set,' ||
'consume_forecast,' ||
'update_type,' ||
'bucket_type,' ||
'last_update_date,' ||
'last_updated_by,' ||
'creation_date,' ||
'created_by,' ||
'demand_class,' ||
'customer_id,' ||
'ship_id' ||
') values (' ||
'''' || replace(p_forecast_designator, '''', '''''') || ''',' || -- forecast_designator,
org_id || ',' || -- organization_id,
'''' || replace(p_forecast_set, '''', '''''') || ''',' || -- forecast_set
'''' || '1' || ''',' || -- consume_forecast,
'decode (' || '''' || v_customer_id || '''' || ', '''',' ||
'decode (' || '''' || v_location_id || '''' || ', '''', 6, 2),' ||
'decode (' || '''' || v_location_id || '''' || ', '''', 4,2)),' || -- update_type
'''' || v_bucket_type || ''',' || -- bucket_type,
'sysdate' || ',' || -- last_update_date,
'''' || '1' || ''',' || -- last_updated_by,
'sysdate, ' || -- creation_date,
'''' || '1' || ''',' || -- created_by
'''' || nvl(l_sr_dcs_lvl_pk,replace(p_demand_class, '''', '''''')) || ''',' || -- demand_class,
'''' || v_customer_id || ''',' || -- customer_id,
'''' || V_location_id || ''')'; -- ship_id
v_sql_stmt := 'delete from mrp_forecast_items' || v_dblink ||
' where forecast_designator = :l_forecast_designator' ||
' and organization_id = :l_org_id ';
v_sql_stmt := 'delete from mrp_forecast_dates ' || v_dblink ||
' where forecast_designator = :l_forecast_designator' ||
' and organization_id = :l_org_id ';
v_sql_stmt := 'update mrp_forecast_designators' || v_dblink || ' ' ||
' set ' ||
'forecast_set = ' || '''' || replace(p_forecast_set, '''', '''''') || ''',' ||
'bucket_type = ' || '''' || v_bucket_type || ''',' ||
'last_update_date = sysdate,' ||
'last_updated_by = 1,' ||
'demand_class = ' || '''' || nvl(l_sr_dcs_lvl_pk,replace(p_demand_class, '''', '''''')) || ''',' ||
'customer_id = ' || '''' || v_customer_id || ''',' ||
'ship_id = ''' || v_location_id || '''' ||
' where forecast_designator = ' || '''' || replace(p_forecast_designator, '''', '''''')
|| '''' ||
' and organization_id = ' || org_id;
v_sql_stmt := 'select count(*) from mrp_forecast_designators' || v_dblink
|| ' ' ||
'where forecast_designator = ''' || replace(p_forecast_set, '''', '''''') || '''' ||
' and organization_id = ' || org_id;
v_sql_stmt := 'insert into mrp_forecast_designators' || v_dblink
|| ' (' ||
'forecast_designator, ' ||
'organization_id, ' ||
'forecast_set, ' ||
'consume_forecast, ' ||
'update_type, ' ||
'bucket_type, ' ||
'last_update_date, ' ||
'last_updated_by, ' ||
'creation_date, ' ||
'created_by, ' ||
'demand_class, ' ||
'customer_id, ' ||
'ship_id ' ||
') values ( ' ||
'''' || replace(p_forecast_set, '''', '''''') || ''',' || -- forecast_designator,
org_id || ',' || -- organization_id,
'NULL,' || -- forecast_set
'1,' || -- consume_forecast,
'decode (' || '''' || v_customer_id || '''' || ', '''',' ||
'decode (' || '''' || v_location_id || '''' || ', '''', 6, 2),' ||
'decode (' || '''' || v_location_id || '''' || ', '''', 4,2)),' || -- update_type
'''' || v_bucket_type || ''',' || -- bucket_type,
'sysdate,' || -- last_update_date,
'1,' || -- last_updated_by,
'sysdate,' || -- creation_date,
'1,' || -- created_by
'''' || nvl(l_sr_dcs_lvl_pk,replace(p_demand_class, '''', '''''')) || ''',' || -- demand_class,
'NULL, ' ||-- customer_id,
'NULL ' ||-- ship_id
')';
v_sql_stmt := 'update mrp_forecast_designators' || v_dblink || ' ' ||
' set bucket_type = ' || '''' || v_bucket_type || ''',' ||
' last_update_date = sysdate, ' ||
' last_updated_by = 1,' ||
' demand_class = ' || '''' || nvl(l_sr_dcs_lvl_pk,replace(p_demand_class, '''', '''''')) || '''' ||
' where forecast_designator = ' || '''' || replace(p_forecast_set, '''', '''''') || '''' ||
' and organization_id = ' || org_id;
v_sql_stmt := 'select level_id from msd_dp_scn_output_levels_v ' ||
'where demand_plan_id = ' || p_demand_plan_id ||
' and scenario_id = ' || p_scenario_id ||
' and owning_dimension_code = ''GEO''';
l_insert := 'INSERT ';
l_select := 'SELECT ';
select level_pk
into org_pk
from msd_level_values
where instance = p_instance_id
and sr_level_pk = org_id
and level_id = 7; -- level_id=7 - organization level
l_insert := l_insert || 'INTO MSD_DP_SCN_ENTRIES_TEMP' ||
' (inventory_item_id, forecast_designator, organization_id, ' ||
' forecast_date, quantity, bucket_type, forecast_end_date) ';
l_select := l_select ||
'sce.sr_product_lvl_pk, ' ||
'''' || p_forecast_designator || ''', ' ||
org_id || ', ' ||
'sce.time_lvl_val_from, ' ||
'ROUND(sum(sce.' || v_quant || ' * decode(sce.PRODUCT_LVL_ID, 1, 1,' ||
' msd_common_utilities.msd_uom_convert(sce.sr_product_lvl_pk, ' ||
' null, sce.total_quantity_uom, lp.base_uom))),' ||
l_round_off || '), ' ||
to_char(v_bucket_type) || ', ' ||
'sce.time_lvl_val_to ' || ' ';
'SELECT level_pk from msd_level_values_v where parent_level_pk = ' || to_char(cus_pk) || ') ';
select dimension_code into dim
from msd_levels
where level_id = p_level_id;
SELECT mdsol.level_id into lvl
FROM msd_dp_scenario_output_levels mdsol
WHERE mdsol.demand_plan_id = p_demand_plan_id
and mdsol.scenario_id = p_scenario_id
and exists (select 1
from msd_levels mlv,
msd_dp_dimensions mdd
where mdd.demand_plan_id = p_demand_plan_id
and mdd.dimension_code = mlv.dimension_code
and mlv.level_id = mdsol.level_id
and mdd.dimension_code = dim);
select min(hierarchy_id) into hier
from
(select hierarchy_id from msd_hierarchy_levels where level_id = p_level_id
INTERSECT
select hierarchy_id from msd_hierarchy_levels where level_id = lvl);
select level_id into lvl2
from msd_hierarchy_levels
where parent_level_id = lvl2
and hierarchy_id = hier;
open lpks for 'select level_pk from msd_level_values_v ' ||
' where level_id = ' || to_char(lvl2) || ' ' ||
' and parent_level_pk in ' || qstr;
/* Clear Temp table before insertion */
DELETE FROM MSD_DP_SCN_ENTRIES_TEMP;
/* Insert Forecast into MSD_DP_SCN_ENTRIES_TEMP table first */
v_sql_stmt := l_insert || l_select || l_from || l_where || l_group_by;
/* Clean up mrp_forecast_interface table before inserting new forecast */
v_sql_stmt := ' DELETE FROM mrp_forecast_interface'|| v_dblink ||
' WHERE forecast_designator = '||
'''' || p_forecast_designator || '''' ||
' and organization_id = nvl(' || org_id ||', organization_id)';
/* Insert Forecast into MRP_FORECAST_INTERFACE table */
-- insert
v_sql_stmt := 'INSERT INTO mrp_forecast_interface' || v_dblink ||
' (inventory_item_id, forecast_designator, organization_id, ' ||
' forecast_date, quantity, process_status, confidence_percentage, ' ||
' bucket_type, forecast_end_date, last_update_date, last_updated_by, ' ||
' creation_date, created_by, workday_control) ' ||
' SELECT inventory_item_id, forecast_designator, organization_id, ' ||
' forecast_end_date, quantity, 2, 100, ' ||
' bucket_type, forecast_end_date, SYSDATE, -1, SYSDATE, -1, ' ||
to_char(p_workday_control) || ' ' ||
' FROM MSD_DP_SCN_ENTRIES_TEMP ';
/* Delete temp table after insert forecast into the source */
DELETE FROM MSD_DP_SCN_ENTRIES_TEMP;