[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT plans.compile_designator, plans.organization_id, plans.sr_instance_id,
desig.inventory_atp_flag, desig.production
INTO l_val_planname,l_val_orgid,l_val_instanceid,l_val_inventory_atp_flag,
l_val_production
FROM msc_plans plans, msc_designators desig
WHERE plans.curr_plan_type in (1,2,3)
AND plans.organization_id = desig.organization_id
AND plans.sr_instance_id = desig.sr_instance_id
AND plans.compile_designator = desig.designator
AND NVL(desig.disable_date, TRUNC(SYSDATE)+1) > TRUNC(SYSDATE)
AND plans.organization_selection <> 1
AND NVL(plans.copy_plan_id,-1) = -1
AND NVL(desig.copy_designator_id, -1) = -1
AND plans.plan_id = planId;
SELECT calendar_date
INTO l_val_anchordate
FROM msc_calendar_dates dates,
msc_trading_partners mtp
WHERE dates.calendar_code = mtp.calendar_code
AND dates.exception_set_id = mtp.calendar_exception_set_id
AND mtp.sr_instance_id = dates.sr_instance_id
AND mtp.sr_tp_id = l_val_orgid
AND mtp.sr_instance_id = l_val_instanceid
AND dates.calendar_date <= TRUNC(sysdate)
AND dates.calendar_date = anchordate;
SELECT lookup_code
INTO l_val_launchsnapshot
FROM mfg_lookups
WHERE lookup_type = 'MSC_LAUNCH_SNAPSHOT'
AND lookup_code in decode(lookup_code,1,1,2,2,3,
decode((select count(*) from msc_plan_schedules
where plan_id = planId
and rownum = 1
and designator_type = 7
and input_type = 1),0,1,3) )
AND lookup_code = decode(launchsnapshot,'FULL',1,'NO',2,'DP_ONLY',3,-1);
SELECT lookup_code
INTO l_val_launchplanner
FROM MFG_LOOKUPS
WHERE lookup_type = 'SYS_YES_NO'
AND ((lookup_code = 1 AND l_val_launchsnapshot in (1,3,4)) OR
(l_val_launchsnapshot = 2))
AND lookup_code = decode(launchplanner, 'Y', msc_ws_common.sys_yes, msc_ws_common.sys_no);
SELECT decode(archiveCurrVersPlan, 'Y', msc_ws_common.sys_yes, msc_ws_common.sys_no)
INTO l_val_archivePlan
FROM dual;
SELECT lookup_code
INTO l_val_enable24x7atp
FROM MFG_LOOKUPS
WHERE LOOKUP_TYPE = 'MSC_24X7_PURGE'
AND (( LOOKUP_CODE IN (1,2,3) and NVL(l_val_inventory_atp_flag,2) = 1 )
OR LOOKUP_CODE=2)
AND LOOKUP_CODE = decode(enable24x7atp,'YES_PURGE',1,'NO',2,'YES_NO_PURGE',3,-1);
SELECT lookup_code
INTO l_val_releasereschedules
FROM MFG_LOOKUPS
WHERE lookup_type='SYS_YES_NO'
AND (NVL(l_val_production,2)=1 or lookup_code=2)
AND lookup_code = decode(releasereschedules,'Y',msc_ws_common.sys_yes, msc_ws_common.sys_no);
SELECT to_number(decode(snapstaticentities, 'Y', msc_ws_common.sys_yes, msc_ws_common.sys_no))
INTO l_val_snapstaticentities
FROM dual;
SELECT plans.plan_id, plans.compile_designator, plans.organization_id, plans.sr_instance_id
INTO l_val_planid, l_val_planname, l_val_orgid, l_val_instanceid
FROM msc_plans plans, msc_designators desig
WHERE plans.curr_plan_type in (1,2,3)
AND plans.organization_id = desig.organization_id
AND plans.sr_instance_id = desig.sr_instance_id
AND plans.compile_designator = desig.designator
AND NVL(desig.disable_date, TRUNC(SYSDATE)+1) > TRUNC(SYSDATE)
AND plans.organization_selection <> 1
AND NVL(plans.copy_plan_id,-1) = -1
AND NVL(desig.copy_designator_id, -1) = -1
AND plans.plan_id = planId;
SELECT
1 INTO l_Dummy
FROM
msc_plans
WHERE
curr_plan_type IN (1, 2, 3) AND
plan_id = PlanId;
SELECT
1 INTO l_Dummy
FROM
msd_dp_ascp_scenarios_v
WHERE
global_scenario_flag = 'Y' AND
scenario_name <> PlanName AND
scenario_id = SchId;
SELECT level_id INTO l_scenario_lvl_geo
FROM msd_dp_scn_output_levels_v
WHERE
scenario_id = SchId AND
level_id IN (11,15,41,42,30);
SELECT level_id INTO l_scenario_lvl_item
FROM msd_dp_scn_output_levels_v
WHERE
scenario_id = SchId AND
level_id IN (34, 40);
SELECT
desig.designator_type,
decode(desig.designator_type, 6, desig.update_type, -1)
INTO
DesigType,
FcstShipTo
FROM
msc_designators desig ,
fnd_lookups lu
WHERE
( (desig.designator_type = 6 AND desig.forecast_set_id is null) OR
(desig.designator_type IN (1,2,3,4,5,8) )
) AND
trunc(nvl(desig.disable_date, trunc(sysdate) + 1)) > trunc(sysdate) AND
( desig.designator <> PlanName OR desig.designator_type = 1 ) AND
desig.organization_id = OrgId AND
desig.sr_instance_id = InsId AND
desig.designator_id = SchId AND
lu.lookup_code(+) = desig.update_type AND
lu.lookup_type(+) = 'MSC_SHIP_TO'
UNION
SELECT
7, -1
FROM
msd_dp_ascp_scenarios_v
WHERE
global_scenario_flag ='N' AND
scenario_name <> PlanName AND
scenario_id = SchId AND
( sr_instance_id = -23453 OR
sr_instance_id = InsId )
UNION
SELECT
desig.designator_type,
decode(desig.designator_type, 6, desig.update_type, -1)
FROM
msc_designators desig,
fnd_lookups lu,
msc_plan_organizations_v mpo
WHERE
( (desig.designator_type = 6 AND desig.forecast_set_id is null) OR
(desig.designator_type IN (2,3,4,5,8))
) AND
trunc(nvl(desig.disable_date, trunc(sysdate) + 1)) > trunc(sysdate) AND
mpo.organization_id = desig.organization_id AND
mpo.sr_instance_id = desig.sr_instance_id AND
mpo.compile_designator = desig.designator AND
mpo.planned_organization = OrgId AND
mpo.sr_instance_id = InsId AND
desig.designator <> PlanName AND
desig.designator_id = SchId AND
lu.lookup_code(+) = desig.update_type AND
lu.lookup_type(+) = 'MSC_SHIP_TO'
UNION
SELECT
desig.designator_type,
decode(desig.designator_type, 6, desig.update_type, -1)
FROM
msc_designators desig,
fnd_lookups lu,
msc_item_sourcing mis,
msc_plans mp
WHERE
( (desig.designator_type = 6 AND desig.forecast_set_id is null) OR
(desig.designator_type IN (2,3,4,5,8))
) AND
trunc(nvl(desig.disable_date, trunc(sysdate) + 1)) > trunc(sysdate) AND
mis.plan_id = mp.plan_id AND
mp.organization_id = desig.organization_id AND
mp.sr_instance_id = desig.sr_instance_id AND
mp.compile_designator =desig.designator AND
mis.source_organization_id = OrgId AND
mis.sr_instance_id2 = InsId AND
desig.designator <> PlanName AND
desig.designator_id = SchId AND
lu.lookup_code(+) = desig.update_type AND
lu.lookup_type(+) = 'MSC_SHIP_TO';
SELECT
designator_type INTO DesigType
FROM
msc_designators
WHERE
trunc(nvl(disable_date, trunc(sysdate) + 1)) > trunc(sysdate) AND
designator <> PlanName AND
organization_id = OrgId AND
sr_instance_id = InsId AND
designator_id = SchId AND
designator_type not in (1,6,7,8)
UNION
SELECT
desig.designator_type
FROM
msc_designators desig,
msc_plan_organizations_v mpo
WHERE
desig.designator_type not in (1,6,7,8) AND
trunc(nvl(desig.disable_date, trunc(sysdate) + 1)) > trunc(sysdate) AND
mpo.organization_id = desig.organization_id AND
mpo.sr_instance_id = desig.sr_instance_id AND
mpo.compile_designator = desig.designator AND
mpo.planned_organization = OrgId AND
mpo.sr_instance_id = InsId AND
desig.designator <> PlanName AND
desig.designator_id = SchId;
MSC_WS_COMMON.UPDATE_PLAN_OPTIONS(PlanId, ItemSimulationSetId, l_Overwrite);
l_String := MSC_WS_COMMON.INSERT_ALL_SCHEDULES(
PlanId, l_InsId, UserId,
g_IGlbDmdSchTbl, g_ILocDmdSchTbl, g_ILocSupSchTbl);
l_String := MSC_WS_COMMON.INSERT_OR_UPDATE_ALL_SCHS(
PlanId, l_InsId, UserId,
g_IGlbDmdSchTbl, g_ILocDmdSchTbl, g_ILocSupSchTbl);