[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 = 5
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.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 DECODE(launchSnapshot,
'Y' ,MSC_WS_COMMON.SYS_YES, MSC_WS_COMMON.SYS_NO)
INTO l_val_launchSnapshot
FROM DUAL;
SELECT lookup_code
INTO l_val_launchPlanner
FROM MFG_LOOKUPS
WHERE lookup_type = 'SYS_YES_NO'
AND ((lookup_code = 1 AND l_val_launchSnapshot = 1) 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 plans.compile_designator
INTO l_val_planName
FROM msc_plans plans, msc_designators desig
WHERE plans.curr_plan_type = 5
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.plan_id = planId;
SELECT
plans.organization_id,
plans.sr_instance_id,
plans.compile_designator
INTO
OrgId,
InsId,
PlanName
FROM
msc_plans plans,
msc_designators desig
WHERE
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
plans.curr_plan_type in (1,2,3,4,5,8,9) AND
plans.plan_id <> -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 = 5 AND
plan_id = PlanId;
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 (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
scenario_name <> PlanName AND
sr_instance_id = InsId AND
scenario_id = SchId AND
last_revision IS NOT NULL
UNION
SELECT
desig.designator_type,
decode(desig.designator_type, 6, desig.update_type, -1)
FROM
msc_designators desig,
msc_plan_organizations_v mpo,
fnd_lookups lu
WHERE
( (desig.designator_type = 6 AND desig.forecast_set_id is null) OR
(desig.designator_type in (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,
msc_item_sourcing mis,
msc_plans mp,
fnd_lookups lu
WHERE
( (desig.designator_type = 6 AND desig.forecast_set_id is null) OR
(desig.designator_type IN (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';
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);