[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select_on_hand EXCEPTION;
select_wip EXCEPTION;
update_beginning_inv EXCEPTION;
update_open_purchase_orders EXCEPTION;
update_open_purchase_reqs EXCEPTION;
update_mrp_purchase_orders EXCEPTION;
update_open_discrete_jobs EXCEPTION;
update_mrp_repetitive_scheds EXCEPTION;
update_mrp_discrete_jobs EXCEPTION;
update_master_sched_discrete EXCEPTION;
update_master_sched_repetitive EXCEPTION;
select_past_due_mds EXCEPTION;
update_past_due_mds EXCEPTION;
select_inventory_values EXCEPTION;
update_inventory_values EXCEPTION;
SELECT rowid,
date1,
date2,
number1,
number2,
number3,
number4,
number5,
number6,
number7,
number8
FROM mrp_form_query
WHERE query_id = arg_query_id
ORDER BY date1;
| Select calendar defaults |
+--------------------------*/
mrp_calendar.select_calendar_defaults(
arg_org_id,
arg_calendar_code,
arg_exception_set_id);
update mrp_form_query
set char1 = arg_compile_desig,
char2 = arg_sched_desig,
number12 = arg_org_id
where query_id = arg_query_id;
/*SELECT NVL(SUM((sys.nettable_inventory_quantity
+ sys.nonnettable_inventory_quantity)
* NVL(cst.item_cost, 0)), 0)
/ 1000*//*2417274*/
SELECT NVL(SUM((sys.nettable_inventory_quantity)
* NVL(cst.item_cost, 0)), 0)
/ 1000
INTO begin_inv
FROM cst_item_costs cst,
mrp_system_items sys
WHERE cst.organization_id = sys.organization_id
AND cst.inventory_item_id = sys.inventory_item_id
AND ( cst.cost_type_id = arg_cost_type
OR
(
(cst.cost_type_id = arg_def_cost_type)
AND
(NOT EXISTS
(SELECT 'Primary Cost Type Row'
FROM cst_item_costs cst1
WHERE cst1.inventory_item_id = cst.inventory_item_id
AND cst1.organization_id = arg_org_id
AND cst1.cost_type_id = arg_cost_type)
)
)
)
AND sys.compile_designator = arg_compile_desig
AND sys.organization_id = arg_org_id;
RAISE select_on_hand;
SELECT NVL(SUM(NVL(wip.net_quantity, 0)
* NVL(cst.item_cost, 0)), 0)
/ 1000
INTO issued_inv
FROM cst_item_costs cst,
mrp_wip_components wip
WHERE cst.organization_id = wip.organization_id
AND cst.inventory_item_id = wip.inventory_item_id
AND ( cst.cost_type_id = arg_cost_type
OR
(
(cst.cost_type_id = arg_def_cost_type)
AND
(NOT EXISTS
(SELECT 'Primary Cost Type Row'
FROM cst_item_costs cst1
WHERE cst1.inventory_item_id = cst.inventory_item_id
AND cst1.organization_id = arg_org_id
AND cst1.cost_type_id = arg_cost_type)
)
)
)
AND wip.compile_designator = arg_compile_desig
AND wip.organization_id = arg_org_id
AND wip.wip_entity_type IN (1, 3)
AND DECODE(wip.wip_entity_type,
1, 1, wip.supply_demand_type) =
DECODE(wip.wip_entity_type, 1, 1, 1);
RAISE select_wip;
UPDATE mrp_form_query
SET number1 = begin_inv + issued_inv
WHERE query_id = arg_query_id
AND date2 IS NOT NULL;
RAISE update_beginning_inv;
UPDATE mrp_form_query query
SET number2 =
(SELECT NVL(SUM(rec.new_order_quantity
* NVL(cst.item_cost, 0)), 0)
/ 1000
FROM cst_item_costs cst,
mrp_recommendations rec
WHERE cst.organization_id = rec.organization_id
AND cst.inventory_item_id = rec.inventory_item_id
AND ( cst.cost_type_id = arg_cost_type
OR
(
(cst.cost_type_id = arg_def_cost_type)
AND
(NOT EXISTS
(SELECT 'Primary Cost Type Row'
FROM cst_item_costs cst1
WHERE cst1.inventory_item_id = cst.inventory_item_id
AND cst1.organization_id = arg_org_id
AND cst1.cost_type_id = arg_cost_type)
)
)
)
AND rec.new_schedule_date >= query.date1
AND rec.new_schedule_date < query.date2
AND rec.disposition_status_type = 1
AND rec.order_type IN (1, 8)
AND rec.compile_designator = arg_compile_desig
AND rec.organization_id = arg_org_id)
WHERE query_id = arg_query_id;
RAISE update_open_purchase_orders;
UPDATE mrp_form_query query
SET number3 =
(SELECT NVL(SUM(rec.new_order_quantity
* NVL(cst.item_cost,0)), 0)
/ 1000
FROM cst_item_costs cst,
mrp_recommendations rec
WHERE cst.organization_id = rec.organization_id
AND cst.inventory_item_id = rec.inventory_item_id
AND ( cst.cost_type_id = arg_cost_type
OR
(
(cst.cost_type_id = arg_def_cost_type)
AND
(NOT EXISTS
(SELECT 'Primary Cost Type Row'
FROM cst_item_costs cst1
WHERE cst1.inventory_item_id = cst.inventory_item_id
AND cst1.organization_id = arg_org_id
AND cst1.cost_type_id = arg_cost_type)
)
)
)
AND rec.new_schedule_date >= query.date1
AND rec.new_schedule_date < query.date2
AND rec.disposition_status_type = 1
AND rec.order_type = 2
AND rec.compile_designator = arg_compile_desig
AND rec.organization_id = arg_org_id)
WHERE query_id = arg_query_id;
RAISE update_open_purchase_reqs;
UPDATE mrp_form_query query
SET number4 =
(SELECT NVL(SUM(rec.new_order_quantity
* NVL(cst.item_cost, 0)), 0)
/ 1000
FROM cst_item_costs cst,
mrp_system_items sys,
mrp_recommendations rec
WHERE cst.organization_id = sys.organization_id
AND cst.inventory_item_id = sys.inventory_item_id
AND ( cst.cost_type_id = arg_cost_type
OR
(
(cst.cost_type_id = arg_def_cost_type)
AND
(NOT EXISTS
(SELECT 'Primary Cost Type Row'
FROM cst_item_costs cst1
WHERE cst1.inventory_item_id = cst.inventory_item_id
AND cst1.organization_id = arg_org_id
AND cst1.cost_type_id = arg_cost_type)
)
)
)
AND sys.inventory_item_id = rec.inventory_item_id
AND sys.compile_designator = rec.compile_designator
AND sys.organization_id = rec.organization_id
AND sys.planning_make_buy_code = 2
AND rec.new_schedule_date >= query.date1
AND rec.new_schedule_date < query.date2
AND rec.disposition_status_type = 1
AND rec.order_type = 5
AND rec.compile_designator = arg_compile_desig
AND rec.organization_id = arg_org_id)
WHERE query_id = arg_query_id;
RAISE update_mrp_purchase_orders;
UPDATE mrp_form_query query
SET number5 =
(SELECT NVL(SUM(rec.new_order_quantity
* (NVL(cst.tl_resource, 0)
+ NVL(cst.tl_overhead, 0)
+ NVL(cst.tl_material_overhead, 0)
+ NVL(cst.tl_outside_processing, 0))), 0)
/ 1000
FROM cst_item_costs cst,
mrp_recommendations rec
WHERE cst.inventory_item_id = rec.inventory_item_id
AND cst.organization_id = rec.organization_id
AND ( cst.cost_type_id = arg_cost_type
OR
(
(cst.cost_type_id = arg_def_cost_type)
AND
(NOT EXISTS
(SELECT 'Primary Cost Type Row'
FROM cst_item_costs cst1
WHERE cst1.inventory_item_id = cst.inventory_item_id
AND cst1.organization_id = arg_org_id
AND cst1.cost_type_id = arg_cost_type)
)
)
)
AND rec.new_wip_start_date >= query.date1
AND rec.new_wip_start_date < query.date2
AND rec.disposition_status_type = 1
AND rec.order_type in (3, 27)
AND rec.compile_designator = arg_compile_desig
AND rec.organization_id = arg_org_id)
WHERE query_id = arg_query_id;
RAISE update_open_discrete_jobs;
UPDATE mrp_form_query query
SET number6 =
(SELECT NVL(SUM(NVL(rec.daily_rate, 0)
* (NVL(cst.tl_resource, 0)
+ NVL(cst.tl_overhead, 0)
+ NVL(cst.tl_material_overhead, 0)
+ NVL(cst.tl_outside_processing, 0))), 0)
/ 1000
FROM cst_item_costs cst,
bom_calendar_dates cal,
mrp_recommendations rec
WHERE cst.inventory_item_id = rec.inventory_item_id
AND cst.organization_id = rec.organization_id
AND ( cst.cost_type_id = arg_cost_type
OR
(
(cst.cost_type_id = arg_def_cost_type)
AND
(NOT EXISTS
(SELECT 'Primary Cost Type Row'
FROM cst_item_costs cst1
WHERE cst1.inventory_item_id = cst.inventory_item_id
AND cst1.organization_id = arg_org_id
AND cst1.cost_type_id = arg_cost_type)
)
)
)
AND rec.last_unit_start_date >= query.date1
AND rec.first_unit_start_date < query.date2
AND cal.calendar_date BETWEEN
GREATEST(rec.first_unit_start_date,
query.date1)
AND
LEAST(rec.last_unit_start_date,
(query.date2 - 1))
AND cal.calendar_code = arg_calendar_code
AND cal.exception_set_id = arg_exception_set_id
AND cal.seq_num IS NOT NULL
AND rec.disposition_status_type = 1
AND rec.order_type = 4
AND rec.compile_designator = arg_compile_desig
AND rec.organization_id = arg_org_id)
WHERE query_id = arg_query_id;
RAISE update_mrp_repetitive_scheds;
UPDATE mrp_form_query query
SET number7 =
(SELECT NVL(SUM(rec.new_order_quantity
* (NVL(cst.tl_resource, 0)
+ NVL(cst.tl_overhead, 0)
+ NVL(cst.tl_material_overhead, 0)
+ NVL(cst.tl_outside_processing, 0))), 0)
/ 1000
FROM cst_item_costs cst,
mrp_system_items sys,
mrp_recommendations rec
WHERE cst.inventory_item_id = sys.inventory_item_id
AND cst.organization_id = sys.organization_id
AND ( cst.cost_type_id = arg_cost_type
OR
(
(cst.cost_type_id = arg_def_cost_type)
AND
(NOT EXISTS
(SELECT 'Primary Cost Type Row'
FROM cst_item_costs cst1
WHERE cst1.inventory_item_id = cst.inventory_item_id
AND cst1.organization_id = arg_org_id
AND cst1.cost_type_id = arg_cost_type)
)
)
)
AND sys.inventory_item_id = rec.inventory_item_id
AND sys.compile_designator = rec.compile_designator
AND sys.organization_id = rec.organization_id
AND sys.planning_make_buy_code = 1
AND rec.new_schedule_date >= query.date1
AND rec.new_schedule_date < query.date2
AND rec.disposition_status_type = 1
AND rec.order_type = 5
AND rec.compile_designator = arg_compile_desig
AND rec.organization_id = arg_org_id)
WHERE query_id = arg_query_id;
RAISE update_mrp_discrete_jobs;
UPDATE mrp_form_query query
SET number8 =
(SELECT NVL(SUM(dates.schedule_quantity * cst.item_cost), 0)
/ 1000
FROM mrp_schedule_dates dates,
cst_item_costs cst,
mrp_system_items sys,
mrp_schedule_designators sched
WHERE cst.organization_id = sys.organization_id
AND cst.inventory_item_id = sys.inventory_item_id
AND ( cst.cost_type_id = arg_cost_type
OR
(
(cst.cost_type_id = arg_def_cost_type)
AND
(NOT EXISTS
(SELECT 'Primary Cost Type Row'
FROM cst_item_costs cst1
WHERE cst1.inventory_item_id = cst.inventory_item_id
AND cst1.organization_id = arg_org_id
AND cst1.cost_type_id = arg_cost_type)
)
)
)
AND dates.organization_id = sys.organization_id
AND dates.inventory_item_id = sys.inventory_item_id
AND dates.schedule_date >= query.date1
AND dates.schedule_date < query.date2
AND dates.schedule_level = 3
AND dates.schedule_designator = arg_sched_desig
AND sched.schedule_designator = arg_sched_desig
AND sched.organization_id = arg_org_id
AND sys.repetitive_type = 1
AND sys.compile_designator = arg_compile_desig
AND sys.organization_id = arg_org_id)
WHERE query_id = arg_query_id;
RAISE update_master_sched_discrete;
UPDATE mrp_form_query query
SET number8 =
(SELECT query.number8 +
NVL(SUM(cst.item_cost
* dates.repetitive_daily_rate), 0)
/ 1000
FROM bom_calendar_dates cal,
mrp_schedule_dates dates,
cst_item_costs cst,
mrp_system_items sys,
mrp_schedule_designators sched
WHERE cst.organization_id = sys.organization_id
AND cst.inventory_item_id = sys.inventory_item_id
AND ( cst.cost_type_id = arg_cost_type
OR
(
(cst.cost_type_id = arg_def_cost_type)
AND
(NOT EXISTS
(SELECT 'Primary Cost Type Row'
FROM cst_item_costs cst1
WHERE cst1.inventory_item_id = cst.inventory_item_id
AND cst1.organization_id = arg_org_id
AND cst1.cost_type_id = arg_cost_type)
)
)
)
AND dates.organization_id = sys.organization_id
AND dates.inventory_item_id = sys.inventory_item_id
AND dates.rate_end_date >= query.date1
AND dates.schedule_date < query.date2
AND dates.schedule_level = 3
AND dates.schedule_designator = arg_sched_desig
AND sched.schedule_designator = arg_sched_desig
AND sched.organization_id = arg_org_id
AND cal.calendar_date BETWEEN
GREATEST(dates.schedule_date,
query.date1)
AND
LEAST(dates.rate_end_date,
(query.date2 - 1))
AND cal.calendar_code = arg_calendar_code
AND cal.exception_set_id = arg_exception_set_id
AND cal.seq_num IS NOT NULL
AND sys.repetitive_type = 2
AND sys.compile_designator = arg_compile_desig
AND sys.organization_id = arg_org_id)
WHERE query_id = arg_query_id;
RAISE update_master_sched_repetitive;
RAISE select_inventory_values;
SELECT NVL(SUM(cst.item_cost
* NVL(dates.repetitive_daily_rate,
dates.schedule_quantity)), 0) / 1000
INTO past_due_master_schedule
FROM bom_calendar_dates cal,
mrp_schedule_dates dates,
cst_item_costs cst,
mrp_system_items sys,
mrp_schedule_designators sched
WHERE cst.organization_id = sys.organization_id
AND cst.inventory_item_id = sys.inventory_item_id
AND ( cst.cost_type_id = arg_cost_type
OR
(
(cst.cost_type_id = arg_def_cost_type)
AND
(NOT EXISTS
(SELECT 'Primary Cost Type Row'
FROM cst_item_costs cst1
WHERE cst1.inventory_item_id = cst.inventory_item_id
AND cst1.organization_id = arg_org_id
AND cst1.cost_type_id = arg_cost_type)
)
)
)
AND dates.organization_id = sys.organization_id
AND dates.inventory_item_id = sys.inventory_item_id
AND NVL(dates.rate_end_date, dates.schedule_date)
< start_date_this_period
AND dates.schedule_level = 3
AND dates.schedule_designator = arg_sched_desig
AND sched.schedule_designator = arg_sched_desig
AND sched.organization_id = arg_org_id
AND cal.calendar_date BETWEEN dates.schedule_date
AND NVL(dates.rate_end_date, dates.schedule_date)
AND cal.calendar_code = arg_calendar_code
AND cal.exception_set_id = arg_exception_set_id
AND cal.seq_num IS NOT NULL
AND sys.compile_designator = arg_compile_desig
AND sys.organization_id = arg_org_id;
RAISE select_past_due_mds;
UPDATE mrp_form_query
SET number8 = master_schedule
WHERE rowid = my_rowid;
RAISE update_past_due_mds;
| Update cumulative master schedule |
+----------------------------------*/
cum_master_schedule := cum_master_schedule + master_schedule;
UPDATE mrp_form_query q
SET number9 = end_inv_this_period,
number10 = period_turns,
number11 = cum_turns
WHERE rowid = my_rowid;
RAISE update_inventory_values;
WHEN select_on_hand THEN
raise_application_error(-20000,
'Cannot select on-hand beginning inventory value');
WHEN select_wip THEN
raise_application_error(-20000,
'Cannot select WIP beginning inventory value');
WHEN update_beginning_inv THEN
raise_application_error(-20000,
'Cannot update beginning inventory value');
WHEN update_open_purchase_orders THEN
raise_application_error(-20000,
'Cannot update open purchase order values');
WHEN update_open_purchase_reqs THEN
raise_application_error(-20000,
'Cannot update open purchase requisition values');
WHEN update_mrp_purchase_orders THEN
raise_application_error(-20000,
'Cannot update MRP planned purchase order values');
WHEN update_open_discrete_jobs THEN
raise_application_error(-20000,
'Cannot update open discrete job values');
WHEN update_mrp_repetitive_scheds THEN
raise_application_error(-20000,
'Cannot update suggested repetitive schedule values');
WHEN update_mrp_discrete_jobs THEN
raise_application_error(-20000,
'Cannot update MRP planned discrete job values');
WHEN update_master_sched_discrete THEN
raise_application_error(-20000,
'Cannot update master schedule, discrete item values');
WHEN update_master_sched_repetitive THEN
raise_application_error(-20000,
'Cannot update master schedule, discrete item values');
WHEN select_inventory_values THEN
raise_application_error(-20000,
'Cannot select inventory values');
WHEN update_inventory_values THEN
raise_application_error(-20000,
'Cannot update inventory values');
WHEN select_past_due_mds THEN
raise_application_error(-2000,
'Cannot select past due MDS');
WHEN update_past_due_mds THEN
raise_application_error(-2000,
'Cannot update past due MDS');