[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT plan_id
FROM msc_plans
WHERE compile_designator = l_default_plan_name;
SELECT count(*)
FROM msd_dp_ascp_scenarios_v
WHERE demand_plan_id = l_dp_plan_id
AND scenario_id = l_default_scenario_id;
SELECT organization_id, sr_instance_id
FROM msc_plan_organizations
WHERE plan_id = l_plan_id;
SELECT count(*)
FROM msc_plan_schedules
WHERE plan_id = l_plan_id
AND organization_id = l_org_id
AND sr_instance_id = l_instance_id
AND input_schedule_id = l_default_scenario_id
AND designator_type = 7;
SELECT count(*)
FROM msc_plan_schedules
WHERE plan_id = l_plan_id
AND input_schedule_id in ( SELECT scenario_id
FROM msd_dp_ascp_scenarios_v
WHERE demand_plan_id = l_dp_plan_id)
AND designator_type = 7;
insert into msc_plan_schedules (
PLAN_ID,
ORGANIZATION_ID,
INPUT_SCHEDULE_ID,
SR_INSTANCE_ID,
INPUT_TYPE,
DESIGNATOR_TYPE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY )
values (
l_plan_id,
l_org_id,
l_default_scenario_id,
l_instance_id,
1,
7,
sysdate,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.USER_ID );
select nvl(dmd_priority_scenario_id,-999) into l_dmd_prty_scen_id
from msd_dp_scenarios
where demand_plan_id = p_demand_plan_id
and scenario_id = p_scenario_id;
select sr_inventory_item_id into l_sr_inventory_item_id
from msc_apps_instances mai, msc_system_items msi
where msi.plan_id = -1
and msi.sr_instance_id = p_sr_instance_id
and msi.organization_id = mai.validation_org_id
and msi.inventory_item_id = p_inventory_item_id
and mai.instance_id = p_sr_instance_id;
select quantity into l_priority
from msd_dp_scenario_entries
where demand_plan_id = p_demand_plan_id
and scenario_id = l_dmd_prty_scen_id
and decode(time_lvl_id, 9, 1, 1, 2, 3) = p_bucket_type
and time_lvl_val_from = p_start_time
and TIME_LVL_VAL_TO = p_end_time
and to_number(INSTANCE) = p_sr_instance_id
and to_number(decode(ltrim(sr_product_lvl_pk, '.0123456789'), null, sr_product_lvl_pk, -1)) = l_sr_inventory_item_id
and decode(demand_class_lvl_id,40, null,34, decode(demand_class,'-777', null,demand_class),demand_class) = p_demand_class
and rownum < 2;
SELECT scenario_id
FROM msd_dp_scenarios a
WHERE
a.demand_plan_id = p_demand_plan_id
AND NOT EXISTS (SELECT 1
FROM msd_dp_scenarios b
WHERE
b.demand_plan_id = a.demand_plan_id
AND b.dmd_priority_scenario_id = a.scenario_id);
SELECT dmd_priority_scenario_id
FROM msd_dp_scenarios
WHERE
demand_plan_id = p_demand_plan_id
AND dmd_priority_scenario_id is not null;
x_sql_stmt := 'DELETE from msd_dp_scn_entries_denorm ' ||
'WHERE demand_plan_id = ' || p_demand_plan_id || ' ' ||
'AND scenario_id ' || x_scenario_id_list;
/* Bug# 10160412 - Commit if number of records deleted are > 1M */
IF (sql%rowcount > 1000000)
THEN
commit;
x_sql_stmt := 'INSERT INTO msd_dp_scn_entries_denorm( ' ||
'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, ' ||
'sr_ship_to_loc_id, ' ||
'sr_customer_id, ' ||
'sr_zone_id, ' ||
'priority, ' ||
'dp_uom_code, ' ||
'ascp_uom_code, ' ||
'demand_class, ' ||
'unit_price, ' ||
'creation_date, ' ||
'created_by, ' ||
'last_update_login ) ' ||
'SELECT ' || p_demand_plan_id || ', ' ||
'fcst_sce.scenario_id, ' ||
'fcst_sce.demand_id, ' ||
'fcst_sce.bucket_type, ' ||
'fcst_sce.start_time, ' ||
'fcst_sce.end_time, ' ||
'fcst_sce.quantity, ' ||
'fcst_sce.sr_organization_id, ' ||
'fcst_sce.sr_instance_id, ' ||
'fcst_sce.sr_inventory_item_id, ' ||
'fcst_sce.error_type, ' ||
'fcst_sce.forecast_error, ' ||
'fcst_sce.inventory_item_id, ' ||
'fcst_sce.sr_ship_to_loc_id, ' ||
'fcst_sce.sr_customer_id, ' ||
'fcst_sce.sr_zone_id, ' ||
'dmpr_sce.quantity, ' ||
'fcst_sce.dp_uom_code, ' ||
'fcst_sce.ascp_uom_code, ' ||
'decode (fcst_sce.demand_class,''-100'', null, fcst_sce.demand_class), ' ||
'fcst_sce.unit_price, ' ||
'''' || sysdate || ''',' ||
FND_GLOBAL.USER_ID || ',' ||
FND_GLOBAL.LOGIN_ID || ' ' ||
'FROM ' ||
'(SELECT mdas.scenario_id SCENARIO_ID, ' ||
'mdas.demand_id DEMAND_ID, ' ||
'mdas.bucket_type BUCKET_TYPE, ' ||
'mdas.start_time START_TIME, ' ||
'mdas.end_time END_TIME, ' ||
'mdas.quantity QUANTITY, ' ||
'mdas.sr_organization_id SR_ORGANIZATION_ID, ' ||
'mdas.sr_instance_id SR_INSTANCE_ID, ' ||
'mdas.sr_inventory_item_id SR_INVENTORY_ITEM_ID, ' ||
'mdas.error_type ERROR_TYPE, ' ||
'mdas.forecast_error FORECAST_ERROR, ' ||
'mdas.inventory_item_id INVENTORY_ITEM_ID, ' ||
'mdas.sr_ship_to_loc_id SR_SHIP_TO_LOC_ID, ' ||
'mdas.sr_customer_id SR_CUSTOMER_ID, ' ||
'mdas.sr_zone_id SR_ZONE_ID, ' ||
'mdas.dp_uom_code DP_UOM_CODE, ' ||
'mdas.ascp_uom_code ASCP_UOM_CODE, ' ||
'nvl(mdas.demand_class,''-100'') DEMAND_CLASS, ' ||
'mdas.unit_price UNIT_PRICE, ' ||
'mdas.dmd_priority_scenario_id DMD_PRIORITY_SCENARIO_ID, ' ||
'mdas.time_lvl_id TIME_LVL_ID ' ||
'FROM msd_dp_ascp_scn_entries_v mdas ' ||
'WHERE mdas.demand_plan_id = ' || p_demand_plan_id || ' ' ||
'AND mdas.scenario_id ' || x_scenario_id_list || ') fcst_sce, ' ||
'(SELECT /*+ INDEX(mdse MSD_DP_SCN_ENTRIES_N1)*/ ' ||
'mdse.scenario_id SCENARIO_ID, ' ||
'mdse.time_lvl_id TIME_LVL_ID, ' ||
'mdse.time_lvl_val_from START_TIME, ' ||
'mdse.time_lvl_val_to END_TIME, ' ||
'max(mdse.quantity) QUANTITY, ' ||
'to_number(mdse.instance) SR_INSTANCE_ID, ' ||
'to_number(decode(ltrim(sr_product_lvl_pk, ''.0123456789''), ' ||
'null, ' ||
'sr_product_lvl_pk, ' ||
'-1)) SR_INVENTORY_ITEM_ID, ' ||
'nvl(decode(mdse.demand_class_lvl_id, ' ||
'40, ' ||
'null, ' ||
'34, ' ||
'decode(mdse.demand_class, ' ||
'''-777'', ' ||
'null, ' ||
'mdse.demand_class), ' ||
'mdse.demand_class), ' ||
'''-100'') DEMAND_CLASS ' ||
'from msd_dp_scenarios mds, ' ||
'msd_dp_scenario_entries mdse ' ||
'WHERE mds.demand_plan_id = ' || p_demand_plan_id || ' ' ||
'AND mds.scenario_id ' || x_dmd_pri_scenario_id_list ||
'AND mds.demand_plan_id = mdse.demand_plan_id ' ||
'AND mds.scenario_id = mdse.scenario_id ' ||
'AND mds.last_revision = mdse.revision ' ||
'GROUP BY mdse.scenario_id, ' ||
'mdse.time_lvl_id, ' ||
'mdse.time_lvl_val_from, ' ||
'mdse.time_lvl_val_to, ' ||
'mdse.instance, ' ||
'mdse.SR_PRODUCT_LVL_PK, ' ||
'mdse.demand_class_lvl_id, ' ||
'mdse.demand_class) dmpr_sce ' ||
'WHERE fcst_sce.dmd_priority_scenario_id = dmpr_sce.scenario_id (+) ' ||
'AND fcst_sce.time_lvl_id = dmpr_sce.time_lvl_id (+) ' ||
'AND fcst_sce.start_time = dmpr_sce.start_time (+) ' ||
'AND fcst_sce.end_time = dmpr_sce.end_time (+) ' ||
'AND fcst_sce.sr_instance_id = dmpr_sce.sr_instance_id (+) ' ||
'AND fcst_sce.sr_inventory_item_id = dmpr_sce.sr_inventory_item_id (+) ' ||
'AND fcst_sce.demand_class = dmpr_sce.demand_class (+) ';
x_sql_stmt := 'DELETE from msd_dp_planning_pct_denorm ' ||
'WHERE demand_plan_id = ' || p_demand_plan_id || ' ' ||
'AND dp_scenario_id ' || x_scenario_id_list;
/*Bug# 10160412 - Commit if number of records deleted are > 1M */
IF (sql%rowcount > 1000000)
THEN
commit;
x_sql_stmt := 'INSERT INTO msd_dp_planning_pct_denorm( ' ||
'demand_plan_id , ' ||
'dp_scenario_id , ' ||
'component_sequence_id , ' ||
'orig_component_sequence_id , ' ||
'bill_sequence_id , ' ||
'sr_instance_id , ' ||
'organization_id , ' ||
'inventory_item_id , ' ||
'assembly_item_id , ' ||
'date_to , ' ||
'date_from , ' ||
'planning_factor , ' ||
'plan_percentage_type , ' ||
'creation_date , ' ||
'created_by , ' ||
'last_update_login ' ||
') ' ||
'SELECT ' ||
'demand_plan_id , ' ||
'dp_scenario_id , ' ||
'component_sequence_id , ' ||
'orig_component_sequence_id , ' ||
'bill_sequence_id , ' ||
'sr_instance_id , ' ||
'organization_id , ' ||
'inventory_item_id , ' ||
'assembly_item_id , ' ||
'date_to , ' ||
'date_from , ' ||
'planning_factor , ' ||
'plan_percentage_type , ' ||
'''' || sysdate || ''',' ||
FND_GLOBAL.USER_ID || ',' ||
FND_GLOBAL.LOGIN_ID ||
' FROM msd_dp_planning_percentages_v ' ||
'WHERE demand_plan_id = ' || p_demand_plan_id || ' ' ||
'AND dp_scenario_id ' || x_scenario_id_list;
MSD_DP_PLANNING_PCT_DENORM after populating them to update statistics
*/
commit;