[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
UPDATED_SCHEDULE CONSTANT INTEGER := 2;
SELECT mps_transaction_id,
schedule_quantity,
schedule_date,
rowid
FROM mrp_schedule_dates dates
WHERE exists
(SELECT NULL
FROM bom_calendar_dates cal1,
bom_calendar_dates cal2,
mtl_parameters param,
mtl_system_items sys
WHERE cal1.calendar_code = param.calendar_code
AND cal1.exception_set_id =
param.calendar_exception_set_id
AND cal1.calendar_date = TRUNC(SYSDATE)
AND cal2.calendar_code = param.calendar_code
AND cal2.exception_set_id =
param.calendar_exception_set_id
AND cal2.seq_num = cal1.next_seq_num +
(DECODE(sys.auto_reduce_mps,
MPS_AUTO_PAST_DUE,
0,
MPS_AUTO_DEMAND_TF,
DECODE(sys.demand_time_fence_code,
USER_TF,
CEIL(
NVL(sys.demand_time_fence_days, 0)),
CUM_TOTAL_LT,
CEIL(
NVL(sys.cumulative_total_lead_time,
0)),
CUM_MFG_LT,
CEIL(
NVL(sys.cum_manufacturing_lead_time,
0)),
TOTAL_LT,
CEIL(NVL(sys.full_lead_time, 0))),
MPS_AUTO_PLANNING_TF,
DECODE(sys.planning_time_fence_code,
USER_TF,
CEIL(
NVL(sys.planning_time_fence_days,
0)),
CUM_TOTAL_LT,
CEIL(
NVL(sys.cumulative_total_lead_time,
0)),
CUM_MFG_LT,
CEIL(NVL(
sys.cum_manufacturing_lead_time,0)),
TOTAL_LT,
CEIL(NVL(sys.full_lead_time, 0)))))
AND dates.schedule_date < cal2.calendar_date
AND param.organization_id = dates.organization_id
AND sys.organization_id = dates.organization_id
AND sys.auto_reduce_mps <> MPS_AUTO_NONE
AND sys.auto_reduce_mps is not null
AND sys.inventory_item_id = dates.inventory_item_id)
AND dates.schedule_level = UPDATED_SCHEDULE
AND dates.supply_demand_type = MTL_SUPPLY_TYPE
AND dates.schedule_quantity <> 0
AND dates.rate_end_date is NULL
AND dates.organization_id = DECODE(arg_sched_mgr, SYS_YES,
dates.organization_id, arg_org_id)
AND dates.schedule_designator = DECODE(arg_sched_mgr,
SYS_YES, dates.schedule_designator,
arg_sched_desig);
var_watch_id := mrp_print_pk.start_watch('GEN-updated',
arg_request_id,
arg_user_id,
'ENTITY',
'mrp_schedule_dates',
'N');
INSERT INTO mrp_schedule_consumptions(
transaction_id,
relief_type,
disposition_type,
disposition_id,
line_num,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
order_date,
order_quantity,
relief_quantity,
schedule_date,
program_application_id,
program_id,
program_update_date)
VALUES (
transaction_id,
MPS_RELIEF,
R_AUTO_REDUCE,
NULL,
NULL,
SYSDATE,
arg_user_id,
SYSDATE,
arg_user_id,
arg_user_id,
sched_date,
sched_quantity,
sched_quantity,
sched_date,
NULL,
NULL,
NULL);
UPDATE mrp_schedule_dates
SET schedule_quantity = 0,
last_updated_by = arg_user_id,
last_update_date = SYSDATE
WHERE rowid = sched_rowid;
DELETE mrp_schedule_dates dates
WHERE exists
(SELECT NULL
FROM bom_calendar_dates cal1,
bom_calendar_dates cal2,
mtl_parameters param,
mtl_system_items sys
WHERE cal1.calendar_code = param.calendar_code
AND cal1.exception_set_id =
param.calendar_exception_set_id
AND cal1.calendar_date = TRUNC(SYSDATE)
AND cal2.calendar_code = param.calendar_code
AND cal2.exception_set_id =
param.calendar_exception_set_id
AND cal2.seq_num = cal1.next_seq_num +
(DECODE(sys.auto_reduce_mps,
MPS_AUTO_PAST_DUE,
0,
MPS_AUTO_DEMAND_TF,
DECODE(sys.demand_time_fence_code,
USER_TF,
CEIL(
NVL(sys.demand_time_fence_days, 0)),
CUM_TOTAL_LT,
CEIL(
NVL(sys.cumulative_total_lead_time,
0)),
CUM_MFG_LT,
CEIL(NVL(
sys.cum_manufacturing_lead_time,0)),
TOTAL_LT,
CEIL(NVL(sys.full_lead_time, 0))),
MPS_AUTO_PLANNING_TF,
DECODE(sys.planning_time_fence_code,
USER_TF,
CEIL(NVL(
sys.planning_time_fence_days, 0)),
CUM_TOTAL_LT,
CEIL(NVL(
sys.cumulative_total_lead_time, 0)),
CUM_MFG_LT,
CEIL(NVL(
sys.cum_manufacturing_lead_time,0)),
TOTAL_LT,
CEIL(NVL(sys.full_lead_time, 0)))))
AND dates.rate_end_date < cal2.calendar_date
AND param.organization_id = dates.organization_id
AND sys.organization_id = dates.organization_id
AND sys.auto_reduce_mps <> MPS_AUTO_NONE
AND sys.auto_reduce_mps is not null
AND sys.inventory_item_id = dates.inventory_item_id)
AND dates.schedule_level = UPDATED_SCHEDULE
AND dates.supply_demand_type = MTL_SUPPLY_TYPE
AND dates.rate_end_date is NOT NULL
AND dates.organization_id = DECODE(arg_sched_mgr, SYS_YES,
dates.organization_id, arg_org_id)
AND dates.schedule_designator = DECODE(arg_sched_mgr,
SYS_YES, dates.schedule_designator,
arg_sched_desig);
var_watch_id := mrp_print_pk.start_watch('GEN-updated',
arg_request_id,
arg_user_id,
'ENTITY',
'mrp_schedule_dates',
'N');
UPDATE mrp_schedule_dates dates
SET last_update_date = SYSDATE,
last_updated_by = arg_user_id,
dates.schedule_date =
(SELECT cal2.calendar_date
FROM bom_calendar_dates cal1,
bom_calendar_dates cal2,
mtl_parameters param,
mtl_system_items sys
WHERE cal1.calendar_code = param.calendar_code
AND cal1.exception_set_id =
param.calendar_exception_set_id
AND cal1.calendar_date = TRUNC(SYSDATE)
AND cal2.calendar_code = param.calendar_code
AND cal2.exception_set_id =
param.calendar_exception_set_id
AND cal2.seq_num = cal1.next_seq_num +
(DECODE(sys.auto_reduce_mps,
MPS_AUTO_PAST_DUE,
0,
MPS_AUTO_DEMAND_TF,
DECODE(sys.demand_time_fence_code,
USER_TF,
CEIL(NVL(
sys.demand_time_fence_days, 0)),
CUM_TOTAL_LT,
CEIL(NVL(
sys.cumulative_total_lead_time, 0)),
CUM_MFG_LT,
CEIL(NVL(
sys.cum_manufacturing_lead_time,
0)),
TOTAL_LT,
CEIL(NVL(sys.full_lead_time, 0))),
MPS_AUTO_PLANNING_TF,
DECODE(sys.planning_time_fence_code,
USER_TF,
CEIL(NVL(
sys.planning_time_fence_days,
0)),
CUM_TOTAL_LT,
CEIL(NVL(
sys.cumulative_total_lead_time,
0)),
CUM_MFG_LT,
CEIL(NVL(
sys.cum_manufacturing_lead_time,
0)),
TOTAL_LT,
CEIL(NVL(sys.full_lead_time, 0)))))
AND dates.rate_end_date >= cal2.calendar_date
AND dates.schedule_date < cal2.calendar_date
AND param.organization_id = dates.organization_id
AND sys.organization_id = dates.organization_id
AND sys.auto_reduce_mps <> MPS_AUTO_NONE
AND sys.auto_reduce_mps is not null
AND sys.inventory_item_id = dates.inventory_item_id)
WHERE exists
(SELECT NULL
FROM bom_calendar_dates cal1,
bom_calendar_dates cal2,
mtl_parameters param,
mtl_system_items sys
WHERE cal1.calendar_code = param.calendar_code
AND cal1.exception_set_id =
param.calendar_exception_set_id
AND cal1.calendar_date = TRUNC(SYSDATE)
AND cal2.calendar_code = param.calendar_code
AND cal2.exception_set_id =
param.calendar_exception_set_id
AND cal2.seq_num = cal1.next_seq_num +
(DECODE(sys.auto_reduce_mps,
MPS_AUTO_PAST_DUE,
0,
MPS_AUTO_DEMAND_TF,
DECODE(sys.demand_time_fence_code,
USER_TF,
CEIL(NVL(
sys.demand_time_fence_days, 0)),
CUM_TOTAL_LT,
CEIL(NVL(
sys.cumulative_total_lead_time, 0)),
CUM_MFG_LT,
CEIL(NVL(
sys.cum_manufacturing_lead_time,
0)),
TOTAL_LT,
CEIL(NVL(sys.full_lead_time, 0))),
MPS_AUTO_PLANNING_TF,
DECODE(sys.planning_time_fence_code,
USER_TF,
CEIL(NVL(
sys.planning_time_fence_days,
0)),
CUM_TOTAL_LT,
CEIL(NVL(
sys.cumulative_total_lead_time,
0)),
CUM_MFG_LT,
CEIL(NVL(
sys.cum_manufacturing_lead_time,
0)),
TOTAL_LT,
CEIL(NVL(sys.full_lead_time, 0)))))
AND dates.rate_end_date >= cal2.calendar_date
AND dates.schedule_date < cal2.calendar_date
AND param.organization_id = dates.organization_id
AND sys.organization_id = dates.organization_id
AND sys.auto_reduce_mps <> MPS_AUTO_NONE
AND sys.auto_reduce_mps is not null
AND sys.inventory_item_id = dates.inventory_item_id)
AND dates.schedule_level = UPDATED_SCHEDULE
AND dates.supply_demand_type = MTL_SUPPLY_TYPE
AND dates.rate_end_date is NOT NULL
AND dates.organization_id = DECODE(arg_sched_mgr, SYS_YES,
dates.organization_id, arg_org_id)
AND dates.schedule_designator = DECODE(arg_sched_mgr,
SYS_YES, dates.schedule_designator,
arg_sched_desig);