[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT 1
FROM msc_plans mp
WHERE mp.plan_id = p_plan_Id
AND mp.compile_designator = p_designator;
SELECT mp.plan_id
FROM msc_plans mp
WHERE mp.compile_designator = p_designator;
SELECT organization_id, sr_instance_id,
curr_Plan_Type, curr_Overwrite_Option,
request_id, plan_completion_date, summary_flag,
compile_designator
FROM msc_plans
WHERE plan_id = p_plan_id;
SELECT plan_id, plan_completion_date,request_id, summary_flag
FROM msc_plans
WHERE compile_designator = p_designator
AND organization_id = p_org_id
AND sr_instance_id = p_instance_id;
select desig.designator_id,
desig.description,
desig.inventory_atp_flag,
desig.launch_workflow_flag,
desig.production,
desig.disable_date,
desig.organization_id,
desig.sr_instance_id,
plans.curr_plan_type,
plans.curr_overwrite_option
from msc_designators desig, msc_plans plans
where desig.designator = plans.compile_designator
and plans.plan_id= p_plan_id
and plans.organization_id = desig.organization_id
and plans.sr_instance_id = desig.sr_instance_id;
SELECT designator_id
FROM msc_designators
WHERE designator = p_designator
AND organization_id = p_org_id
AND sr_instance_id = p_instance_id;
update msc_plans
set release_reschedules = nvl(arg_release_reschedules,2),
calculate_liability = nvl(p_calculate_liabilty,2)
--where plan_id = arg_plan_id;
-- Delete the temp. plan because, for any plan,
-- only one temp. plan can exist at any instance.
-- ----------------------------------------------
MSC_UTIL.msc_Debug('deleting temp_plan '||var_temp_plan_id);
msc_copy_plan_options.delete_temp_plan(errbuf, retcode, var_temp_desig_id, TRUE);
update msc_designators
set purge_current_plan = decode(p_24x7atp,3,2,1)
where designator_id = var_desig_id;
| Update msc_plans with plan horizon date |
+----------------------------------------*/
MSC_UTIL.msc_Debug('******About to Launch Plan******');
update msc_demands
set atp_synchronization_flag = 0
where plan_id = arg_plan_id;
| Update msc_parameters with anchor date |
+---------------------------------------*/
UPDATE msc_parameters
SET repetitive_anchor_date = TO_DATE(arg_anchor_date, 'YYYY/MM/DD HH24:MI:SS')
WHERE (organization_id,sr_instance_id) IN (select organization_id, sr_instance_id
from msc_plan_organizations
Where plan_id = v_plan_id);
| Insert subinventories into msc_sub_inventories |
| that are defined after options are defined |
+-----------------------------------------------*/
BEGIN
INSERT INTO MSC_SUB_INVENTORIES
(SUB_INVENTORY_CODE,
ORGANIZATION_ID,
SR_INSTANCE_ID,
PLAN_ID,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
NETTING_TYPE)
SELECT msi.sub_inventory_code,
mpo.organization_id,
mpo.sr_instance_id,
v_plan_id,
SYSDATE,
1,
-1,
SYSDATE,
1,
msi.netting_type
FROM msc_sub_inventories msi,
msc_plan_organizations mpo
WHERE NOT EXISTS
(SELECT NULL
FROM MSC_SUB_INVENTORIES SUB
WHERE SUB.ORGANIZATION_ID = mpo.organization_id
AND sub.sr_instance_id = mpo.sr_instance_id
AND SUB.plan_id = mpo.plan_id
AND SUB.sub_inventory_code = msi.sub_inventory_code)
AND msi.ORGANIZATION_ID = mpo.organization_id
AND msi.sr_instance_id = mpo.sr_instance_id
AND msi.plan_id = -1
AND mpo.plan_id = v_plan_id;
INSERT INTO MSC_SUB_INVENTORIES
(SUB_INVENTORY_CODE,
ORGANIZATION_ID,
SR_INSTANCE_ID,
PLAN_ID,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
NETTING_TYPE)
SELECT
msi.sub_inventory_code,
mpo.organization_id,
mpo.sr_instance_id,
v_new_plan_id, --arg_plan_id,
SYSDATE,
1,
-1,
SYSDATE,
1,
msi.netting_type
FROM msc_sub_inventories msi,
msc_plan_organizations mpo
WHERE NOT EXISTS
(SELECT NULL
FROM msc_sub_inventories sub
WHERE sub.organization_id = mpo.organization_id
AND sub.sr_instance_id = mpo.sr_instance_id
AND sub.plan_id = mpo.plan_id
AND sub.sub_inventory_code = msi.sub_inventory_code)
AND msi.organization_id = mpo.organization_id
AND msi.sr_instance_id = mpo.sr_instance_id
AND msi.plan_id = -1
--AND mpo.plan_id = arg_plan_id;
SELECT plan_start_date
INTO v_plan_completion_date
FROM msc_plans
WHERE plan_id = v_plan_id;
UPDATE msc_plans
SET planning_mode = DP_SCN_ONLY_SNP_MODE
WHERE plan_id = v_plan_id;
UPDATE msc_plans
SET planning_mode = NULL
WHERE plan_id = v_plan_id;
UPDATE msc_plans
SET GENERATE_INLINE_FORECAST= SYS_YES
WHERE plan_id = v_plan_id;
UPDATE msc_plans
SET GENERATE_INLINE_FORECAST= SYS_NO
WHERE plan_id = v_plan_id;
UPDATE msc_plans /* for 24x7 ATP */
SET request_id = var_snapshot_req_id
WHERE plan_id = v_plan_id;
UPDATE msc_plans /* for 24x7 ATP */
SET request_id = var_snapshot_req_id
WHERE plan_id = v_plan_id;
SELECT NVL(production, SYS_NO)
INTO var_production1
FROM msc_designators
where organization_id = v_rec_c1.organization_id
AND sr_instance_id = v_rec_c1.sr_instance_id
AND (designator,designator_type) in
(select compile_designator, plan_type
from msc_plans
Where plan_id = v_plan_id);
update msc_plans
set archive_flag = p_archive_flag
where plan_id = v_plan_id;
Select Compile_Designator, Curr_Plan_Type,
Organization_Id, Sr_Instance_Id,Copy_Plan_Id,
Last_Update_Date, Last_Updated_By, Last_Update_Login,
Append_Planned_Orders, Assignment_Set_Id, Attribute_Category,
Attribute1, Attribute2, Attribute3, Attribute4,
Attribute5, Attribute6, Attribute7, Attribute8,
Attribute9, Attribute10, Attribute11, Attribute12,
Attribute13, Attribute14, Attribute15, Backward_Days,
Bill_Of_Resources, Bottleneck_Res_Group, Company_Agg_Level,
Consider_Po, Consider_Reservations, Consider_Wip,
Created_By, Creation_Date, Curr_Included_Items, Curr_Schedule_Designator,
Curr_Schedule_Type, Curr_Snapshot_Lock, Curr_Split_Demands,
Cutoff_Date, Daily_Trans_Constraints, Data_Completion_Date,
Data_Start_Date, Dem_Priority_Rule_Id, Demand_Time_Fence_Flag,
Enable_Closest_Qty_Pegging, Enable_Priority_Pegging,
Enforce_Cap_Constraints, Enforce_Dem_Due_Dates,
Enforce_Sl_Constraints, Enforce_Src_Constraints,
Forward_Days, Full_Pegging, Hard_Pegging_Level, Included_Items,
Kpi_Refresh, Lot_For_Lot, Max_Wf_Except_Id, Min_Wf_Except_Id,
Monthly_Cutoff_Bucket, Objective_Weight_6,
Objective_Weight_7, Objective_Weight_8, Objective_Weight_9,
Objective_Weight_10, Online_Planner_Completion_Date,
Online_Planner_Start_Date, Online_Replan, Operation_Schedule_Type,
Organization_Selection, Overwrite_Option, Parent_Plan_Id,
Part_Include_Type, Penalty_Cost_1, Penalty_Cost_2, Penalty_Cost_3,
Penalty_Cost_4, Penalty_Cost_5, Penalty_Cost_6, Penalty_Cost_7,
Penalty_Cost_8, Penalty_Cost_9, Penalty_Cost_10, Period_Trans_Constraints,
Plan_Capacity_Flag, Plan_Completion_Date, Plan_Safety_Stock,
Plan_Start_Date, Planned_Refreshes, Planned_Resources,
Planning_Time_Fence_Flag, Product_Agg_Level, Production_Flag,
Program_Application_Id, Program_Id, Program_Update_Date, Purge,
Qtrly_Cutoff_Bucket, Refresh_Number, Request_Id, Reservation_Level,
Revision, Schedule_Designator, Schedule_Type, Simulation_Set,
Slack_Allowed_Flag_1, Slack_Allowed_Flag_2, Slack_Allowed_Flag_3,
Slack_Allowed_Flag_4, Slack_Allowed_Flag_5, Slack_Allowed_Flag_6,
Slack_Allowed_Flag_7, Slack_Allowed_Flag_8, Slack_Allowed_Flag_9,
Slack_Allowed_Flag_10, Snapshot_Lock, Split_Demands,
Start_Date, Status, Summary_Flag, Tp_Agg_Level,
Weekly_Trans_Constraints,
curr_start_date,
curr_cutoff_date,
release_reschedules
From Msc_Plans
Where Plan_Id = p_plan_id;
Select Designator_Id,description, Inventory_Atp_Flag, launch_workflow_flag,
Production, Disable_Date,organization_id, sr_instance_id,
organization_selection,last_update_date,last_updated_by,last_update_login,nvl(purge_current_plan,2) purge_current_plan
From Msc_Designators
Where (Designator, Organization_Id, Sr_Instance_Id) =
( Select Compile_Designator, Organization_Id, Sr_Instance_Id
From Msc_Plans
Where Plan_Id=p_plan_id);
select designator
from msc_designators
where designator = p_designator;
-- To delete the Plan OPTIONS to enable
-- to copy fresh plan options.
-- ------------------------------------
MSC_COPY_PLAN_OPTIONS.delete_plan_options
(errbuf, retcode, p_temp_plan_id);
select '#'||nvl(substr(to_char(msc_24x7_plan_name_s.nextval),-9),msc_24x7_plan_name_s.nextval)
into lv_plan_name
from dual;
update msc_plans
set compile_designator = decode(v_org_desig_cur.purge_current_plan,2,lv_plan_name, nvl(substr(to_char(p_temp_plan_id),-10),p_temp_plan_id)),
-- copy_plan_id=p_temp_plan_id,
copy_plan_id=decode(v_org_desig_cur.purge_current_plan,2,-1,-2),
plan_completion_date = decode(v_org_desig_cur.purge_current_plan,2,v_org_plan_name_Cur.plan_completion_date,null),
request_id = decode(v_org_desig_cur.purge_current_plan,2,v_org_plan_name_Cur.request_id,null),
data_completion_date = decode(v_org_desig_cur.purge_current_plan,2,v_org_plan_name_Cur.data_completion_date,null),
summary_flag = decode(v_org_desig_cur.purge_current_plan,2,v_org_plan_name_Cur.summary_flag,null)
where plan_id = p_org_plan_id;
update msc_designators
set designator = decode(v_org_desig_cur.purge_current_plan,2,lv_plan_name,
nvl(substr(to_char(p_temp_plan_id),-10),p_temp_plan_id)),
-- copy_designator_id = v_temp_desig_cur.designator_id
copy_designator_id = decode(v_org_desig_cur.purge_current_plan,2,-1,-2),
inventory_atp_flag = decode(v_org_desig_cur.purge_current_plan,2,2,inventory_atp_flag),
description = decode(v_org_desig_cur.purge_current_plan,2,substr(description,1,39)||'-'||v_org_plan_name_Cur.compile_designator,description)
where designator = v_org_plan_name_Cur.compile_designator
and organization_id = v_org_desig_cur.organization_id
and sr_instance_id = v_org_desig_cur.sr_instance_id;
--v_org_Desig_Cur.organization_selection, -- > p_dest_org_selection
v_org_plan_name_Cur.curr_Plan_Type, -- > p_dest_plan_type
v_org_desig_Cur.Inventory_Atp_Flag, -- > p_dest_atp
v_org_desig_Cur.production, -- > p_dest_production
v_org_desig_Cur.launch_workflow_flag, -- > p_dest_notifications
v_org_desig_Cur.disable_date, -- > p_dest_inactive_on
v_org_desig_Cur.organization_id , -- > p_organization_id
v_org_desig_Cur.sr_instance_id); -- > p_sr_instance_id
Update msc_plans
set copy_plan_id = -1,
Last_Update_Date = v_temp_plan_name_cur.Last_Update_Date,
Last_Updated_By = v_temp_plan_name_cur.Last_Updated_By,
Last_Update_Login = v_temp_plan_name_cur.Last_Update_Login,
Append_Planned_Orders = v_temp_plan_name_cur.Append_Planned_Orders,
Assignment_Set_Id = v_temp_plan_name_cur.Assignment_Set_Id,
Attribute_Category = v_temp_plan_name_cur.Attribute_Category,
Attribute1 = v_temp_plan_name_cur.Attribute1,
Attribute2 = v_temp_plan_name_cur.Attribute2,
Attribute3 = v_temp_plan_name_cur.Attribute3,
Attribute4 = v_temp_plan_name_cur.Attribute4,
Attribute5 = v_temp_plan_name_cur.Attribute5,
Attribute6 = v_temp_plan_name_cur.Attribute6,
Attribute7 = v_temp_plan_name_cur.Attribute7,
Attribute8 = v_temp_plan_name_cur.Attribute8,
Attribute9 = v_temp_plan_name_cur.Attribute9,
Attribute10 = v_temp_plan_name_cur.Attribute10,
Attribute11 = v_temp_plan_name_cur.Attribute11,
Attribute12 = v_temp_plan_name_cur.Attribute12,
Attribute13 = v_temp_plan_name_cur.Attribute13,
Attribute14 = v_temp_plan_name_cur.Attribute14,
Attribute15 = v_temp_plan_name_cur.Attribute15,
Backward_Days = v_temp_plan_name_cur.Backward_Days,
Bill_Of_Resources = v_temp_plan_name_cur.Bill_Of_Resources,
Bottleneck_Res_Group = v_temp_plan_name_cur.Bottleneck_Res_Group,
Company_Agg_Level = v_temp_plan_name_cur.Company_Agg_Level,
Consider_Po = v_temp_plan_name_cur.Consider_Po,
Consider_Reservations = v_temp_plan_name_cur.Consider_Reservations,
Consider_Wip = v_temp_plan_name_cur.Consider_Wip,
Created_By = v_temp_plan_name_cur.Created_By,
Creation_Date = v_temp_plan_name_cur.Creation_Date,
Curr_Included_Items = v_temp_plan_name_cur.Curr_Included_Items,
Curr_Schedule_Designator= v_temp_plan_name_cur.Curr_Schedule_Designator,
Curr_Schedule_Type = v_temp_plan_name_cur.Curr_Schedule_Type,
Curr_Snapshot_Lock = v_temp_plan_name_cur.Curr_Snapshot_Lock,
Curr_Split_Demands = v_temp_plan_name_cur.Curr_Split_Demands,
Cutoff_Date = v_temp_plan_name_cur.Cutoff_Date,
Daily_Trans_Constraints = v_temp_plan_name_cur.Daily_Trans_Constraints,
Data_Completion_Date = v_temp_plan_name_cur.Data_Completion_Date,
Data_Start_Date = v_temp_plan_name_cur.Data_Start_Date,
Dem_Priority_Rule_Id = v_temp_plan_name_cur.Dem_Priority_Rule_Id,
Demand_Time_Fence_Flag = v_temp_plan_name_cur.Demand_Time_Fence_Flag,
Enable_Closest_Qty_Pegging = v_temp_plan_name_cur.Enable_Closest_Qty_Pegging,
Enable_Priority_Pegging = v_temp_plan_name_cur.Enable_Priority_Pegging,
Enforce_Cap_Constraints = v_temp_plan_name_cur.Enforce_Cap_Constraints,
Enforce_Dem_Due_Dates = v_temp_plan_name_cur.Enforce_Dem_Due_Dates,
Enforce_Sl_Constraints = v_temp_plan_name_cur.Enforce_Sl_Constraints,
Enforce_Src_Constraints = v_temp_plan_name_cur.Enforce_Src_Constraints,
Forward_Days = v_temp_plan_name_cur.Forward_Days,
Full_Pegging = v_temp_plan_name_cur.Full_Pegging,
Hard_Pegging_Level = v_temp_plan_name_cur.Hard_Pegging_Level,
Included_Items = v_temp_plan_name_cur.Included_Items,
Kpi_Refresh = v_temp_plan_name_cur.Kpi_Refresh,
Lot_For_Lot = v_temp_plan_name_cur.Lot_For_Lot,
Max_Wf_Except_Id = v_temp_plan_name_cur.Max_Wf_Except_Id,
Min_Wf_Except_Id = v_temp_plan_name_cur.Min_Wf_Except_Id,
Monthly_Cutoff_Bucket = v_temp_plan_name_cur.Monthly_Cutoff_Bucket,
Objective_Weight_6 = v_temp_plan_name_cur.Objective_Weight_6,
Objective_Weight_7 = v_temp_plan_name_cur.Objective_Weight_7,
Objective_Weight_8 = v_temp_plan_name_cur.Objective_Weight_8,
Objective_Weight_9 = v_temp_plan_name_cur.Objective_Weight_9,
Objective_Weight_10 = v_temp_plan_name_cur.Objective_Weight_10,
Online_Planner_Completion_Date = v_temp_plan_name_cur.Online_Planner_Completion_Date,
Online_Planner_Start_Date = v_temp_plan_name_cur.Online_Planner_Start_Date,
Online_Replan = v_temp_plan_name_cur.Online_Replan,
Operation_Schedule_Type = v_temp_plan_name_cur.Operation_Schedule_Type,
Organization_Selection = v_temp_plan_name_cur.Organization_Selection,
Overwrite_Option = v_temp_plan_name_cur.Overwrite_Option,
Parent_Plan_Id = v_temp_plan_name_cur.Parent_Plan_Id,
Part_Include_Type = v_temp_plan_name_cur.Part_Include_Type,
Penalty_Cost_1 = v_temp_plan_name_cur.Penalty_Cost_1,
Penalty_Cost_2 = v_temp_plan_name_cur.Penalty_Cost_2,
Penalty_Cost_3 = v_temp_plan_name_cur.Penalty_Cost_3,
Penalty_Cost_4 = v_temp_plan_name_cur.Penalty_Cost_4,
Penalty_Cost_5 = v_temp_plan_name_cur.Penalty_Cost_5,
Penalty_Cost_6 = v_temp_plan_name_cur.Penalty_Cost_6,
Penalty_Cost_7 = v_temp_plan_name_cur.Penalty_Cost_7,
Penalty_Cost_8 = v_temp_plan_name_cur.Penalty_Cost_8,
Penalty_Cost_9 = v_temp_plan_name_cur.Penalty_Cost_9,
Penalty_Cost_10 = v_temp_plan_name_cur.Penalty_Cost_10,
Period_Trans_Constraints= v_temp_plan_name_cur.Period_Trans_Constraints,
Plan_Capacity_Flag = v_temp_plan_name_cur.Plan_Capacity_Flag,
Plan_Completion_Date = v_temp_plan_name_cur.Plan_Completion_Date,
Plan_Safety_Stock = v_temp_plan_name_cur.Plan_Safety_Stock,
Plan_Start_Date = v_temp_plan_name_cur.Plan_Start_Date,
Planned_Refreshes = v_temp_plan_name_cur.Planned_Refreshes,
Planned_Resources = v_temp_plan_name_cur.Planned_Resources,
Planning_Time_Fence_Flag= v_temp_plan_name_cur.Planning_Time_Fence_Flag,
Product_Agg_Level = v_temp_plan_name_cur.Product_Agg_Level,
Production_Flag = v_temp_plan_name_cur.Production_Flag,
Program_Application_Id = v_temp_plan_name_cur.Program_Application_Id,
Program_Id = v_temp_plan_name_cur.Program_Id,
Program_Update_Date = v_temp_plan_name_cur.Program_Update_Date,
Purge = v_temp_plan_name_cur.Purge,
Qtrly_Cutoff_Bucket = v_temp_plan_name_cur.Qtrly_Cutoff_Bucket,
Refresh_Number = v_temp_plan_name_cur.Refresh_Number,
Request_Id = v_temp_plan_name_cur.Request_Id,
Reservation_Level = v_temp_plan_name_cur.Reservation_Level,
Revision = v_temp_plan_name_cur.Revision,
Schedule_Designator = v_temp_plan_name_cur.Schedule_Designator,
Schedule_Type = v_temp_plan_name_cur.Schedule_Type,
Simulation_Set = v_temp_plan_name_cur.Simulation_Set,
Slack_Allowed_Flag_1 = v_temp_plan_name_cur.Slack_Allowed_Flag_1,
Slack_Allowed_Flag_2 = v_temp_plan_name_cur.Slack_Allowed_Flag_2,
Slack_Allowed_Flag_3 = v_temp_plan_name_cur.Slack_Allowed_Flag_3,
Slack_Allowed_Flag_4 = v_temp_plan_name_cur.Slack_Allowed_Flag_4,
Slack_Allowed_Flag_5 = v_temp_plan_name_cur.Slack_Allowed_Flag_5,
Slack_Allowed_Flag_6 = v_temp_plan_name_cur.Slack_Allowed_Flag_6,
Slack_Allowed_Flag_7 = v_temp_plan_name_cur.Slack_Allowed_Flag_7,
Slack_Allowed_Flag_8 = v_temp_plan_name_cur.Slack_Allowed_Flag_8,
Slack_Allowed_Flag_9 = v_temp_plan_name_cur.Slack_Allowed_Flag_9,
Slack_Allowed_Flag_10 = v_temp_plan_name_cur.Slack_Allowed_Flag_10,
Snapshot_Lock = v_temp_plan_name_cur.Snapshot_Lock,
Split_Demands = v_temp_plan_name_cur.Split_Demands,
Start_Date = v_temp_plan_name_cur.Start_Date,
Status = v_temp_plan_name_cur.Status,
Summary_Flag = v_temp_plan_name_cur.Summary_Flag,
Tp_Agg_Level = v_temp_plan_name_cur.Tp_Agg_Level,
Weekly_Trans_Constraints= v_temp_plan_name_cur.Weekly_Trans_Constraints,
curr_start_date = v_temp_plan_name_cur.curr_start_date,
curr_cutoff_date = v_temp_plan_name_cur.curr_cutoff_date,
release_reschedules = v_temp_plan_name_cur.release_reschedules
where plan_id = p_temp_plan_id;
update msc_designators
set copy_designator_id = -1,
last_update_date = v_temp_plan_name_Cur.last_update_date,
last_updated_by = v_temp_plan_name_Cur.last_updated_by,
last_update_login = v_temp_plan_name_Cur.last_update_login,
request_id = v_temp_plan_name_cur.request_id,
program_application_id = v_temp_plan_name_cur.program_application_id,
program_id = v_temp_plan_name_cur.program_id,
program_update_date = v_temp_plan_name_cur.program_update_date
where (designator,organization_id,sr_instance_id)
= ( select compile_designator,organization_id,sr_instance_id
from msc_plans
where plan_id=p_temp_plan_id);
Update MSC_PLAN_ORGANIZATIONS
set plan_completion_date = v_temp_plan_name_cur.Plan_Completion_Date,
last_updated_by = v_temp_plan_name_cur.last_updated_by,
last_update_date = v_temp_plan_name_cur.last_update_date,
last_update_login = v_temp_plan_name_cur.last_update_login,
request_id = v_temp_plan_name_cur.request_id,
program_application_id = v_temp_plan_name_cur.program_application_id,
program_id = v_temp_plan_name_cur.program_id,
program_update_date = v_temp_plan_name_cur.program_update_date
Where plan_id = p_temp_plan_id;
Update MSC_PLAN_SCHEDULES
set last_updated_by = v_temp_plan_name_cur.last_updated_by,
last_update_date = v_temp_plan_name_cur.last_update_date,
last_update_login = v_temp_plan_name_cur.last_update_login,
request_id = v_temp_plan_name_cur.request_id,
program_application_id = v_temp_plan_name_cur.program_application_id,
program_id = v_temp_plan_name_cur.program_id,
program_update_date = v_temp_plan_name_cur.program_update_date
Where plan_id = p_temp_plan_id;
Update MSC_SUB_INVENTORIES
set last_updated_by = v_temp_plan_name_cur.last_updated_by,
last_update_date = v_temp_plan_name_cur.last_update_date,
last_update_login = v_temp_plan_name_cur.last_update_login,
request_id = v_temp_plan_name_cur.request_id,
program_application_id = v_temp_plan_name_cur.program_application_id,
program_id = v_temp_plan_name_cur.program_id,
program_update_date = v_temp_plan_name_cur.program_update_date
Where plan_id = p_temp_plan_id;
SELECT ROWID
FROM MSC_USER_PREFERENCE_VALUES
WHERE key IN ('ASCP_PLAN_ID','DRP_PLAN_ID')
AND value = p_plan_id
FOR UPDATE OF value NOWAIT;
SELECT ROWID
FROM MSC_PLAN_SCHEDULES
WHERE input_schedule_id = p_designator_id
AND organization_id = p_organization_id
AND sr_instance_id = p_sr_instance_id
FOR UPDATE OF input_schedule_id NOWAIT;
UPDATE MSC_USER_PREFERENCE_VALUES
SET VALUE=p_temp_plan_id
WHERE ROWID=l_rowlist(I);
l_rowlist.delete;
Update MSC_PLAN_SCHEDULES
SET input_schedule_id = v_temp_desig_cur.designator_id
WHERE ROWID=l_rowlist(I);
Update msc_plans_other
set ref_plan_id = p_temp_plan_id
where ref_plan_id = p_org_plan_id;
-- To delete the Original Plan,
-- ---------------------------------
-- Modified (forward port) for the bug # 3021850
if nvl(v_org_desig_cur.purge_current_plan,1) = 1 THEN
MSC_UTIL.msc_Debug('Purging Original plan...' );
MSC_COPY_PLAN_OPTIONS.delete_temp_plan
(errbuf, retcode, v_org_desig_cur.designator_id,FALSE);
SELECT request_id
, compile_designator
FROM msc_plans
WHERE plan_id = p_plan_id;
Update msc_plans.planning_mode with arg_launch_scheduler
if arg_launch_scheduler == DS_EXP_ONLY (DS Exception only mode)
then msc_plans.planning_mode will be set to 1.
In all other cases msc_plans.planning_mode will be set to NULL
================================================================*/
BEGIN
update msc_plans
set planning_mode = decode(arg_launch_scheduler, DS_EXP_ONLY, 1,
DS_OLS_ONLY, DS_OLS_ONLY,
NULL),
curr_ols_horizon_days = arg_ols_horizon_days,
ols_frozen_horizon_days = arg_frozen_horizon_days
where plan_id = arg_plan_id;
| Insert subinventories into msc_sub_inventories |
| that are defined after options are defined |
+-----------------------------------------------*/
if (arg_launch_scheduler <> DS_OLS_ONLY) then
BEGIN
INSERT INTO MSC_SUB_INVENTORIES
( SUB_INVENTORY_CODE
, ORGANIZATION_ID
, SR_INSTANCE_ID
, PLAN_ID
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_LOGIN
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, NETTING_TYPE
)
(
SELECT msi.sub_inventory_code
, mpo.organization_id
, mpo.sr_instance_id
, v_plan_id
, SYSDATE
, 1
, -1
, SYSDATE
, 1
, msi.netting_type
FROM msc_sub_inventories msi
, msc_plan_organizations mpo
WHERE NOT EXISTS (SELECT NULL
FROM MSC_SUB_INVENTORIES SUB
WHERE SUB.ORGANIZATION_ID = mpo.organization_id
AND sub.sr_instance_id = mpo.sr_instance_id
AND SUB.plan_id = mpo.plan_id
AND SUB.sub_inventory_code = msi.sub_inventory_code)
AND msi.ORGANIZATION_ID = mpo.organization_id
AND msi.sr_instance_id = mpo.sr_instance_id
AND msi.plan_id = -1
AND mpo.plan_id = v_plan_id
);
SELECT nvl(request_id, NULL_VALUE)
INTO l_request_id
FROM msc_plans
WHERE plan_id = l_plan_id;
SELECT nvl(request_id, NULL_VALUE)
INTO l_request_id
FROM msc_plans
WHERE plan_id = l_plan_id;
SELECT planning_mode
INTO l_planning_mode
FROM msc_plans
WHERE plan_id = l_plan_id;
SELECT nvl(request_id, NULL_VALUE)
INTO l_request_id
FROM msc_plans
WHERE plan_id = l_plan_id;
SELECT nvl(request_id, NULL_VALUE)
INTO l_request_id
FROM msc_plans
WHERE plan_id = l_plan_id;
DELETE FROM msc_user_notes mun
WHERE mun.plan_id = p_plan_id
AND NOT exists (SELECT 1 -- sup.plan_id,sup.transaction_id
FROM msc_supplies sup
WHERE plan_id = mun.plan_id
AND sup.sr_instance_id = mun.sr_instance_id
AND sup.transaction_id = mun.transaction_id);
/*delete from msc_user_notes
where plan_id = p_plan_id and
(plan_id,transaction_id) not in (select plan_id,transaction_id
from msc_supplies
where plan_id = p_plan_id); */