[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT organization_id
FROM mtl_parameters mtl
WHERE mtl.calendar_code = cal_code
AND mtl.calendar_exception_set_id = except_set_id;
SELECT 'x' into string_buffer
FROM dual
WHERE exists
(SELECT schedule_date
FROM mrp_schedule_dates
WHERE organization_id = org_id);
SELECT 'x' into string_buffer
FROM dual
WHERE exists
(SELECT forecast_date
FROM mrp_forecast_dates
WHERE organization_id = org_id);
SELECT min(calendar_date), max(calendar_date)
INTO min_date, max_date
FROM bom_calendar_dates
WHERE exception_set_id = except_set_id
AND calendar_code = cal_code;
SELECT 'x' INTO string_buffer
FROM dual
WHERE NOT EXISTS
(SELECT schedule_date
FROM mrp_schedule_dates
WHERE organization_id = org_id
AND schedule_date < min_date
AND schedule_level=2)
AND NOT EXISTS
(SELECT schedule_date
FROM mrp_schedule_dates
WHERE organization_id = org_id
AND DECODE(rate_end_date, NULL, schedule_date,
rate_end_date) > max_date
AND schedule_level=2);
SELECT 'x' INTO string_buffer
FROM dual
WHERE NOT EXISTS
(SELECT forecast_date
FROM mrp_forecast_dates
WHERE organization_id = org_id
AND forecast_date < min_date)
AND NOT EXISTS
(SELECT forecast_date
FROM mrp_forecast_dates
WHERE organization_id = org_id
AND DECODE(rate_end_date, NULL, forecast_date,
rate_end_date) > max_date);
UPDATE mrp_schedule_dates dates
SET /* dates.last_update_date = SYSDATE,
dates.last_updated_by = user_id, */
dates.schedule_workdate =
(SELECT DECODE(desig.schedule_type, 1, prior_date,
next_date)
FROM bom_calendar_dates cal,
mrp_schedule_designators desig
WHERE cal.calendar_code = cal_code
AND cal.exception_set_id = except_set_id
/*2285842 AND cal.calendar_date = dates.schedule_workdate*/
AND cal.calendar_date = dates.schedule_date
AND desig.organization_id
in (select organization_id
from mrp_plan_organizations_v orgs
where orgs.compile_designator =
dates.schedule_designator
and orgs.planned_organization =
dates.organization_id
union all
select org_id
from dual)
AND desig.schedule_designator =
dates.schedule_designator)
WHERE dates.organization_id = org_id
AND dates.schedule_level = 2;
UPDATE mrp_schedule_dates dates
SET dates.last_update_date = SYSDATE,
dates.last_updated_by = user_id,
dates.schedule_date = schedule_workdate
WHERE ((organization_id, schedule_designator) IN
(SELECT org_id, desig.schedule_designator
FROM mrp_schedule_designators desig,
mrp_plan_organizations_v orgs
WHERE orgs.planned_organization (+) = org_id
AND desig.organization_id = NVL(orgs.organization_id,
org_id)
AND desig.schedule_designator =
orgs.compile_designator (+)
AND desig.schedule_type = 2) OR
dates.rate_end_date IS NOT NULL)
AND dates.organization_id = org_id;
UPDATE mrp_schedule_dates dates
SET /* dates.last_update_date = SYSDATE,
dates.last_updated_by = user_id, */
dates.rate_end_date =
(SELECT GREATEST(prior_date, dates.schedule_date)
FROM bom_calendar_dates cal
WHERE cal.calendar_code = cal_code
AND cal.exception_set_id = except_set_id
AND cal.calendar_date = dates.rate_end_date)
WHERE dates.rate_end_date IS NOT NULL
AND dates.schedule_level = 2
AND dates.organization_id = org_id;
UPDATE mrp_forecast_dates dates
SET /* dates.last_update_date = SYSDATE,
dates.last_updated_by = user_id, */
dates.forecast_date =
(SELECT prior_date
FROM bom_calendar_dates cal
WHERE cal.calendar_code = cal_code
AND cal.exception_set_id = except_set_id
AND cal.calendar_date = dates.forecast_date)
WHERE dates.bucket_type = 1
AND dates.organization_id = org_id;
UPDATE mrp_forecast_dates dates
SET /* dates.last_update_date = SYSDATE,
dates.last_updated_by = user_id, */
dates.rate_end_date =
(SELECT prior_date
FROM bom_calendar_dates cal
WHERE cal.calendar_code = cal_code
AND cal.exception_set_id = except_set_id
AND cal.calendar_date = dates.rate_end_date)
WHERE dates.rate_end_date IS NOT NULL
AND dates.bucket_type = 1
AND organization_id = org_id;
UPDATE mrp_forecast_dates dates
SET /* dates.last_update_date = SYSDATE,
dates.last_updated_by = user_id, */
dates.forecast_date =
(SELECT MAX(week_start_date)
FROM bom_cal_week_start_dates cal
WHERE cal.calendar_code = cal_code
AND cal.exception_set_id = except_set_id
AND cal.week_start_date <= dates.forecast_date)
WHERE dates.bucket_type = 2
AND dates.organization_id = org_id;
UPDATE mrp_forecast_dates dates
SET /* dates.last_update_date = SYSDATE,
dates.last_updated_by = user_id, */
dates.rate_end_date =
(SELECT MAX(week_start_date)
FROM bom_cal_week_start_dates cal
WHERE cal.calendar_code = cal_code
AND cal.exception_set_id = except_set_id
AND cal.week_start_date <= dates.rate_end_date)
WHERE dates.rate_end_date IS NOT NULL
AND dates.bucket_type = 2
AND dates.organization_id = org_id;
UPDATE mrp_forecast_dates dates
SET /* dates.last_update_date = SYSDATE,
dates.last_updated_by = user_id, */
dates.forecast_date =
(SELECT MAX(period_start_date)
FROM bom_period_start_dates cal
WHERE cal.calendar_code = cal_code
AND cal.exception_set_id = except_set_id
AND cal.period_start_date <= dates.forecast_date)
WHERE dates.bucket_type = 3
AND dates.organization_id = org_id;
UPDATE mrp_forecast_dates dates
SET /* dates.last_update_date = SYSDATE,
dates.last_updated_by = user_id, */
dates.rate_end_date =
(SELECT MAX(period_start_date)
FROM bom_period_start_dates cal
WHERE cal.calendar_code = cal_code
AND cal.exception_set_id = except_set_id
AND cal.period_start_date <= dates.rate_end_date)
WHERE dates.rate_end_date IS NOT NULL
AND dates.bucket_type = 3
AND dates.organization_id = org_id;
UPDATE mrp_plans
SET data_completion_date = NULL,
plan_completion_date = NULL
WHERE (compile_designator , organization_id) in
(SELECT compile_designator , organization_id
FROM mrp_plan_organizations_v
WHERE
planned_organization = org_id);