[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT number1, rowid
FROM mrp_form_query
WHERE query_id = arg_query_id1
ORDER BY date1;
INSERT INTO mrp_form_query
(QUERY_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
DATE1, -- valid work date
DATE2, -- next valid work date
NUMBER1, -- daily quantity
NUMBER3, -- daily cum qty
NUMBER5, -- orig: copied schedule
NUMBER6, -- orig: MPS plan
NUMBER7, -- orig: manual entry
NUMBER8, -- orig: forecast
NUMBER9, -- orig: sales order
NUMBER11, -- orig: exploded
NUMBER12) -- orig: interorg order
SELECT
arg_query_id1,
sysdate,
-1,
sysdate,
-1,
dates.calendar_date,
dates.next_date,
-- daily quantity
NVL(SUM(DECODE(arg_quantity_type, TYPE_CURRENT_QTY,
sched.current_quantity,
sched.original_quantity)),
0),
-- cumulative quantity
0,
-- orig: copied schedule
NVL(SUM(DECODE(arg_quantity_type, TYPE_CURRENT_QTY,
sched.copied_sched_qty,
sched.original_copied_sched_qty)),
0),
-- orig: MPS plan
NVL(SUM(DECODE(arg_quantity_type, TYPE_CURRENT_QTY,
sched.mps_plan_qty,
sched.original_mps_plan_qty)),
0),
-- orig: manual entry
NVL(SUM(DECODE(arg_quantity_type, TYPE_CURRENT_QTY,
sched.manual_qty,
sched.original_manual_qty)),
0),
-- orig: forecast
NVL(SUM(DECODE(arg_quantity_type, TYPE_CURRENT_QTY,
sched.forecast_qty,
sched.original_forecast_qty)),
0),
-- orig: sales order
NVL(SUM(DECODE(arg_quantity_type, TYPE_CURRENT_QTY,
sched.sales_order_qty,
sched.original_sales_order_qty)),
0),
-- orig: exploded
NVL(SUM(DECODE(arg_quantity_type, TYPE_CURRENT_QTY,
sched.exploded_qty,
sched.original_exploded_qty)),
0),
-- orig: interorg order
NVL(SUM(DECODE(arg_quantity_type, TYPE_CURRENT_QTY,
sched.interorg_qty,
sched.original_interorg_qty)),
0)
FROM bom_calendar_dates dates,
mrp_daily_schedules_v sched,
mtl_parameters param
WHERE param.organization_id = arg_org_id
AND param.calendar_exception_set_id = dates.exception_set_id
AND param.calendar_code = dates.calendar_code
AND sched.organization_id (+)= arg_org_id
AND sched.schedule_designator (+)= arg_schedule_designator
AND sched.inventory_item_id (+)= arg_inventory_item_id
AND sched.schedule_level (+)= arg_version_type
AND sched.bucket_date (+)= dates.calendar_date
AND sched.schedule_date (+)>= arg_start_date
AND dates.calendar_date BETWEEN var_start_date
AND arg_cutoff_date
AND dates.seq_num is not NULL
GROUP BY arg_query_id1, dates.calendar_date, dates.next_date;
INSERT INTO mrp_form_query
(QUERY_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
DATE1, -- week start date
DATE2, -- next week start date
NUMBER1, -- weekly quantity
NUMBER3, -- weekly cum qty
NUMBER5, -- orig: copied schedule
NUMBER6, -- orig: MPS plan
NUMBER7, -- orig: manual entry
NUMBER8, -- orig: forecast
NUMBER9, -- orig: sales order
NUMBER11, -- orig: exploded
NUMBER12) -- orig: interorg order
SELECT
arg_query_id2,
sysdate,
-1,
sysdate,
-1,
dates.week_start_date,
dates.next_date,
-- weekly quantity
NVL(SUM(DECODE(arg_quantity_type, TYPE_CURRENT_QTY,
sched.current_quantity,
sched.original_quantity)),
0),
-- cumulative quantity
0,
-- orig: copied schedule
NVL(SUM(DECODE(arg_quantity_type, TYPE_CURRENT_QTY,
sched.copied_sched_qty,
sched.original_copied_sched_qty)),
0),
-- orig: MPS plan
NVL(SUM(DECODE(arg_quantity_type, TYPE_CURRENT_QTY,
sched.mps_plan_qty,
sched.original_mps_plan_qty)),
0),
-- orig: manual entry
NVL(SUM(DECODE(arg_quantity_type, TYPE_CURRENT_QTY,
sched.manual_qty,
sched.original_manual_qty)),
0),
-- orig: forecast
NVL(SUM(DECODE(arg_quantity_type, TYPE_CURRENT_QTY,
sched.forecast_qty,
sched.original_forecast_qty)),
0),
-- orig: sales order
NVL(SUM(DECODE(arg_quantity_type, TYPE_CURRENT_QTY,
sched.sales_order_qty,
sched.original_sales_order_qty)),
0),
-- orig: exploded
NVL(SUM(DECODE(arg_quantity_type, TYPE_CURRENT_QTY,
sched.exploded_qty,
sched.original_exploded_qty)),
0),
-- orig: interorg order
NVL(SUM(DECODE(arg_quantity_type, TYPE_CURRENT_QTY,
sched.interorg_qty,
sched.original_interorg_qty)),
0)
FROM bom_cal_week_start_dates dates,
mrp_daily_schedules_v sched,
mtl_parameters param
WHERE param.organization_id = arg_org_id
AND param.calendar_exception_set_id = dates.exception_set_id
AND param.calendar_code = dates.calendar_code
AND sched.organization_id = arg_org_id
AND sched.schedule_designator = arg_schedule_designator
AND sched.inventory_item_id = arg_inventory_item_id
AND sched.schedule_level = arg_version_type
AND sched.bucket_date >= dates.week_start_date
AND sched.bucket_date <
DECODE(dates.next_date, dates.week_start_date, var_last_cal_date,
dates.next_date)
AND sched.schedule_date BETWEEN arg_start_date
AND arg_cutoff_date
GROUP BY arg_query_id2, dates.week_start_date, dates.next_date;
INSERT INTO mrp_form_query
(QUERY_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
DATE1, -- week start date
DATE2, -- next week start date
NUMBER1, -- weekly quantity
NUMBER3, -- weekly cum qty
NUMBER5, -- orig: copied schedule
NUMBER6, -- orig: MPS plan
NUMBER7, -- orig: manual entry
NUMBER8, -- orig: forecast
NUMBER9, -- orig: sales order
NUMBER11, -- orig: exploded
NUMBER12) -- orig: interorg order
SELECT
arg_query_id1,
sysdate,
-1,
sysdate,
-1,
dates.week_start_date,
dates.next_date,
NVL(SUM(query.number1), 0), -- weekly quantity
0, -- cumulative quantity
NVL(SUM(query.number5), 0), -- orig: copied schedule
NVL(SUM(query.number6), 0), -- orig: MPS plan
NVL(SUM(query.number7), 0), -- orig: manual entry
NVL(SUM(query.number8), 0), -- orig: forecast
NVL(SUM(query.number9), 0), -- orig: sales order
NVL(SUM(query.number11), 0), -- orig: exploded
NVL(SUM(query.number12), 0) -- orig: interorg order
FROM bom_cal_week_start_dates dates,
mrp_form_query query,
mtl_parameters param
WHERE param.organization_id = arg_org_id
AND param.calendar_exception_set_id = dates.exception_set_id
AND param.calendar_code = dates.calendar_code
AND query.query_id (+)= arg_query_id2
AND query.date1 (+)= dates.week_start_date
AND dates.week_start_date BETWEEN var_start_date
AND arg_cutoff_date
GROUP BY arg_query_id1, dates.week_start_date, dates.next_date;
INSERT INTO mrp_form_query
(QUERY_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
DATE1, -- period start date
DATE2, -- next period start date
NUMBER1, -- period quantity
NUMBER3, -- period cum qty
NUMBER5, -- orig: copied schedule
NUMBER6, -- orig: MPS plan
NUMBER7, -- orig: manual entry
NUMBER8, -- orig: forecast
NUMBER9, -- orig: sales order
NUMBER11, -- orig: exploded
NUMBER12) -- orig: interorg order
SELECT
arg_query_id2,
sysdate,
-1,
sysdate,
-1,
dates.period_start_date,
dates.next_date,
-- period quantity
NVL(SUM(DECODE(arg_quantity_type, TYPE_CURRENT_QTY,
sched.current_quantity,
sched.original_quantity)),
0),
-- cumulative quantity
0,
-- orig: copied schedule
NVL(SUM(DECODE(arg_quantity_type, TYPE_CURRENT_QTY,
sched.copied_sched_qty,
sched.original_copied_sched_qty)),
0),
-- orig: MPS plan
NVL(SUM(DECODE(arg_quantity_type, TYPE_CURRENT_QTY,
sched.mps_plan_qty,
sched.original_mps_plan_qty)),
0),
-- orig: manual entry
NVL(SUM(DECODE(arg_quantity_type, TYPE_CURRENT_QTY,
sched.manual_qty,
sched.original_manual_qty)),
0),
-- orig: forecast
NVL(SUM(DECODE(arg_quantity_type, TYPE_CURRENT_QTY,
sched.forecast_qty,
sched.original_forecast_qty)),
0),
-- orig: sales order
NVL(SUM(DECODE(arg_quantity_type, TYPE_CURRENT_QTY,
sched.sales_order_qty,
sched.original_sales_order_qty)),
0),
-- orig: exploded
NVL(SUM(DECODE(arg_quantity_type, TYPE_CURRENT_QTY,
sched.exploded_qty,
sched.original_exploded_qty)),
0),
-- orig: interorg order
NVL(SUM(DECODE(arg_quantity_type, TYPE_CURRENT_QTY,
sched.interorg_qty,
sched.original_interorg_qty)),
0)
FROM bom_period_start_dates dates,
mrp_daily_schedules_v sched,
mtl_parameters param
WHERE param.organization_id = arg_org_id
AND param.calendar_exception_set_id = dates.exception_set_id
AND param.calendar_code = dates.calendar_code
AND sched.organization_id = arg_org_id
AND sched.schedule_designator = arg_schedule_designator
AND sched.inventory_item_id = arg_inventory_item_id
AND sched.schedule_level = arg_version_type
AND sched.bucket_date >= dates.period_start_date
AND sched.bucket_date <
DECODE(dates.next_date, dates.period_start_date,
var_last_cal_date, dates.next_date)
AND sched.schedule_date BETWEEN arg_start_date
AND arg_cutoff_date
GROUP BY arg_query_id2, dates.period_start_date, dates.next_date;
INSERT INTO mrp_form_query
(QUERY_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
DATE1, -- period start date
DATE2, -- next period start date
NUMBER1, -- period quantity
NUMBER3, -- period cum qty
NUMBER5, -- orig: copied schedule
NUMBER6, -- orig: MPS plan
NUMBER7, -- orig: manual entry
NUMBER8, -- orig: forecast
NUMBER9, -- orig: sales order
NUMBER11, -- orig: exploded
NUMBER12) -- orig: interorg order
SELECT
arg_query_id1,
sysdate,
-1,
sysdate,
-1,
dates.period_start_date,
dates.next_date,
NVL(SUM(query.number1), 0), -- period quantity
0, -- cumulative quantity
NVL(SUM(query.number5), 0), -- orig: copied schedule
NVL(SUM(query.number6), 0), -- orig: MPS plan
NVL(SUM(query.number7), 0), -- orig: manual entry
NVL(SUM(query.number8), 0), -- orig: forecast
NVL(SUM(query.number9), 0), -- orig: sales order
NVL(SUM(query.number11), 0), -- orig: exploded
NVL(SUM(query.number12), 0) -- orig: interorg order
FROM bom_period_start_dates dates,
mrp_form_query query,
mtl_parameters param
WHERE param.organization_id = arg_org_id
AND param.calendar_exception_set_id = dates.exception_set_id
AND param.calendar_code = dates.calendar_code
AND query.query_id (+)= arg_query_id2
AND query.date1 (+)= dates.period_start_date
AND dates.period_start_date BETWEEN var_start_date
AND arg_cutoff_date
GROUP BY arg_query_id1, dates.period_start_date, dates.next_date;
SELECT NVL(SUM(DECODE(arg_quantity_type, TYPE_CURRENT_QTY,
sched.current_quantity,
sched.original_quantity)),
0),
NVL(SUM(DECODE(arg_quantity_type, TYPE_CURRENT_QTY,
sched.copied_sched_qty,
sched.original_copied_sched_qty)),
0),
NVL(SUM(DECODE(arg_quantity_type, TYPE_CURRENT_QTY,
sched.mps_plan_qty,
sched.original_mps_plan_qty)),
0),
NVL(SUM(DECODE(arg_quantity_type, TYPE_CURRENT_QTY,
sched.manual_qty,
sched.original_manual_qty)),
0),
NVL(SUM(DECODE(arg_quantity_type, TYPE_CURRENT_QTY,
sched.forecast_qty,
sched.original_forecast_qty)),
0),
NVL(SUM(DECODE(arg_quantity_type, TYPE_CURRENT_QTY,
sched.sales_order_qty,
sched.original_sales_order_qty)),
0),
NVL(SUM(DECODE(arg_quantity_type, TYPE_CURRENT_QTY,
sched.exploded_qty,
sched.original_exploded_qty)),
0),
NVL(SUM(DECODE(arg_quantity_type, TYPE_CURRENT_QTY,
sched.interorg_qty,
sched.original_interorg_qty)),
0)
INTO var_tmp_quantity,
var_tmp_copied_sched,
var_tmp_mps_plan,
var_tmp_manual,
var_tmp_forecast,
var_tmp_sales_order,
var_tmp_exploded,
var_tmp_interorg
FROM mrp_daily_schedules_v sched
WHERE sched.organization_id = arg_org_id
AND sched.schedule_designator = arg_schedule_designator
AND sched.inventory_item_id = arg_inventory_item_id
AND sched.schedule_level = arg_version_type
AND sched.bucket_date < arg_start_date;
UPDATE MRP_FORM_QUERY
SET NUMBER1 = NUMBER1 + var_tmp_quantity,
NUMBER5 = NUMBER5 + var_tmp_copied_sched,
NUMBER6 = NUMBER6 + var_tmp_mps_plan,
NUMBER7 = NUMBER7 + var_tmp_manual,
NUMBER8 = NUMBER8 + var_tmp_forecast,
NUMBER9 = NUMBER9 + var_tmp_sales_order,
NUMBER11 = NUMBER11 + var_tmp_exploded,
NUMBER12 = NUMBER12 + var_tmp_interorg
WHERE QUERY_ID = arg_query_id1
AND DATE1 = var_start_date;
UPDATE mrp_form_query
SET number3 = var_cum_quantity
WHERE rowid = var_rowid;
SELECT MRP_FORM_QUERY_S.NEXTVAL
INTO X_query_id1
FROM dual;
SELECT MRP_FORM_QUERY_S.NEXTVAL
INTO X_query_id2
FROM dual;
SELECT NVL(item_cost,0)
INTO X_cost
FROM cst_item_costs_for_gl_view
WHERE organization_id = X_org_id
AND inventory_item_id = X_inventory_item_id;
SELECT NVL(maximum_bom_level, 20)
FROM BOM_PARAMETERS
WHERE organization_id = X_organization_id;