[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT DECODE(arg_plan_id, -1, sysdate, trunc(plan_start_date)),
DECODE(arg_plan_id, -1, sysdate+365, trunc(curr_cutoff_date))
FROM msc_plans
WHERE plan_id = arg_plan_id;
SELECT cal.calendar_date
FROM msc_calendar_dates cal,
msc_trading_partners tp
WHERE tp.sr_tp_id = arg_plan_organization_id
AND tp.sr_instance_id = arg_plan_instance_id
AND tp.calendar_exception_set_id = cal.exception_set_id
AND tp.partner_type = 3
AND tp.calendar_code = cal.calendar_code
AND tp.sr_instance_id = cal.sr_instance_id
AND cal.calendar_date BETWEEN TRUNC(p_start_date) AND TRUNC(p_end_date)
ORDER BY cal.calendar_date;
SELECT
mfq.number5 item_id,
mfq.number6 org_id,
mfq.number3 inst_id,
DECODE(ms.order_type,
PURCHASE_ORDER, PURCHASE_ORDER_OFF,
PURCH_REQ,
decode(ms.source_organization_id, null, EXT_PURCH_REQ_OFF,
INT_PURCH_REQ_OFF),
WORK_ORDER, WIP_OFF,
PLANNED_ARRIVAL, PLANNED_ARRIVAL_OFF,
NONSTD_JOB, WIP_OFF,
RECEIPT_PURCH_ORDER, RECEIVING_OFF,
INTRANSIT_SHIPMENT,
decode(ms.source_organization_id, null, EXT_TRANSIT_OFF,
INT_TRANSIT_OFF),
INTRANSIT_RECEIPT, RECEIVING_OFF,
BEG_ON_HAND, BEG_ON_HAND_OFF,
PLANNED_ORDER,
decode(nvl(ms.source_organization_id, ms.organization_id),
ms.organization_id,
PLANNED_MAKE_OFF,
PLANNED_BUY_OFF)
) offset,
DECODE(ms.order_type,
PURCHASE_ORDER, CURRENT_S_RECEIPT_OFF,
PURCH_REQ, CURRENT_S_RECEIPT_OFF,
WORK_ORDER, CURRENT_S_RECEIPT_OFF,
RECEIPT_PURCH_ORDER, CURRENT_S_RECEIPT_OFF,
INTRANSIT_SHIPMENT, CURRENT_S_RECEIPT_OFF,
INTRANSIT_RECEIPT, CURRENT_S_RECEIPT_OFF,
0 ) offset2,
decode(ms.order_type,
PLANNED_ARRIVAL, ms.source_organization_id,
PURCH_REQ, nvl(ms.source_organization_id, -1),
INTRANSIT_SHIPMENT, nvl(ms.source_organization_id,-2),
-1) sub_org_id,
nvl(ms.firm_date,ms.new_schedule_date) new_date,
ms.old_schedule_date old_date,
SUM(DECODE(msi.base_item_id,NULL,
DECODE(ms.disposition_status_type,2, 0,
nvl(ms.firm_quantity,ms.new_order_quantity)),
nvl(ms.firm_quantity,ms.new_order_quantity))) new_quantity,
SUM(NVL(ms.old_order_quantity,0)) quantity1,
SUM(DECODE(msi.base_item_id,NULL,
DECODE(ms.disposition_status_type,2, 0,
nvl(ms.firm_quantity,ms.new_order_quantity)),
nvl(ms.firm_quantity,ms.new_order_quantity)) *
nvl(msi.unit_weight,0)) weight,
SUM(DECODE(msi.base_item_id,NULL,
DECODE(ms.disposition_status_type,2, 0,
nvl(ms.firm_quantity,ms.new_order_quantity)),
nvl(ms.firm_quantity,ms.new_order_quantity)) *
nvl(msi.unit_volume,0)) volume,
sum(NVL(ms.old_order_quantity,0)*nvl(msi.unit_weight,0)) quantity2,
sum(NVL(ms.old_order_quantity,0)*nvl(msi.unit_volume,0)) quantity3
FROM msc_form_query mfq,
msc_system_items msi,
msc_supplies ms
WHERE ms.plan_id = msi.plan_id
AND ms.inventory_item_id = msi.inventory_item_id
AND ms.organization_id = msi.organization_id
AND ms.sr_instance_id = msi.sr_instance_id
AND msi.plan_id = mfq.number4
AND msi.inventory_item_id = mfq.number1
AND msi.organization_id = mfq.number2
AND msi.sr_instance_id = mfq.number3
AND mfq.query_id = arg_query_id
AND mfq.number7 <> NODE_GL_FORECAST_ITEM
AND (arg_query_type <> NO_INT_SHIPMENT or
(arg_query_type = NO_INT_SHIPMENT and
ms.order_type <> PLANNED_ARRIVAL and
not(ms.order_type = PURCH_REQ and ms.source_organization_id is not null)))
GROUP BY
mfq.number5,
mfq.number6,
mfq.number3,
DECODE(ms.order_type,
PURCHASE_ORDER, PURCHASE_ORDER_OFF,
PURCH_REQ,
decode(ms.source_organization_id, null, EXT_PURCH_REQ_OFF,
INT_PURCH_REQ_OFF),
WORK_ORDER, WIP_OFF,
PLANNED_ARRIVAL, PLANNED_ARRIVAL_OFF,
NONSTD_JOB, WIP_OFF,
RECEIPT_PURCH_ORDER, RECEIVING_OFF,
INTRANSIT_SHIPMENT,
decode(ms.source_organization_id, null, EXT_TRANSIT_OFF,
INT_TRANSIT_OFF),
INTRANSIT_RECEIPT, RECEIVING_OFF,
BEG_ON_HAND, BEG_ON_HAND_OFF,
PLANNED_ORDER,
decode(nvl(ms.source_organization_id, ms.organization_id),
ms.organization_id,
PLANNED_MAKE_OFF,
PLANNED_BUY_OFF)
),
DECODE(ms.order_type,
PURCHASE_ORDER, CURRENT_S_RECEIPT_OFF,
PURCH_REQ, CURRENT_S_RECEIPT_OFF,
WORK_ORDER, CURRENT_S_RECEIPT_OFF,
RECEIPT_PURCH_ORDER, CURRENT_S_RECEIPT_OFF,
INTRANSIT_SHIPMENT, CURRENT_S_RECEIPT_OFF,
INTRANSIT_RECEIPT, CURRENT_S_RECEIPT_OFF,
0),
decode(ms.order_type,
PLANNED_ARRIVAL, ms.source_organization_id,
PURCH_REQ, nvl(ms.source_organization_id, -1),
INTRANSIT_SHIPMENT, nvl(ms.source_organization_id,-2), -1),
nvl(ms.firm_date,ms.new_schedule_date),
ms.old_schedule_date
UNION ALL
SELECT mfq.number5 item_id,
mfq.number6 org_id,
mfq.number3 inst_id,
DECODE(md.origination_type,
CONS_KIT_DEMAND, KIT_DEMAND_OFF,
WORK_ORDER_DEMAND, UNC_KIT_DEMAND_OFF,
FORECAST, FORECAST_OFF,
SALES_ORDER,
decode(nvl(md.demand_source_type,2), 2, EXT_SALES_ORDER_OFF,
INT_SALES_ORDER_OFF),
PLANNED_ORDER_DEMAND,
decode(nvl(md.source_organization_id,md.organization_id),
md.organization_id,
UNC_KIT_DEMAND_OFF,
REQUEST_SHIPMENT_OFF),
EXPIRE_LOT_DEMAND, EXPIRE_LOT_OFF,
INTER_ORG_DEMAND, UNC_OTHER_DEMAND_OFF,
PLANNED_ORDER_SCRAP, SCRAP_DEMAND_OFF,
DISCRETE_JOB_SCRAP, SCRAP_DEMAND_OFF,
PURCHASE_ORDER_SCRAP, SCRAP_DEMAND_OFF,
PURCH_REQ_SCRAP, SCRAP_DEMAND_OFF,
RECEIPT_PO_SCRAP, SCRAP_DEMAND_OFF,
INTRANSIT_SHIPMENT_SCRAP, SCRAP_DEMAND_OFF,
OTHER_DEMAND_OFF) offset,
DECODE(md.origination_type,
WORK_ORDER_DEMAND, KIT_DEMAND_OFF,
SALES_ORDER, decode(md.demand_source_type, 8,
REQUEST_SHIPMENT_OFF,EXP_DEMAND_OFF),
FORECAST, EXP_DEMAND_OFF,
INTER_ORG_DEMAND, OTHER_DEMAND_OFF,
0) offset2,
decode(md.origination_type, SALES_ORDER,
decode(nvl(md.demand_source_type,2), 8, md.source_organization_id, -1),
PLANNED_ORDER_DEMAND,
decode(nvl(md.source_organization_id,
md.organization_id), md.organization_id,
-1,
md.source_organization_id),
-1) sub_org_id,
nvl(md.firm_date,md.using_assembly_demand_date) new_date,
nvl(md.old_using_assembly_demand_date,md.using_assembly_demand_date) old_date, -- unconstr date
SUM(DECODE(md.origination_type,
29,nvl(md.probability,1)*
nvl(md.firm_quantity,md.using_requirement_quantity),
nvl(md.firm_quantity,md.using_requirement_quantity))) new_quantity,
SUM(decode(md.origination_type,
29, nvl(md.unmet_quantity,0),
30, decode(md.demand_source_type, 8,
nvl(old_using_requirement_quantity,0),
nvl(md.unmet_quantity,0)),
using_requirement_quantity)) quantity1, -- unconstrain qty
SUM(DECODE(md.origination_type,
29,nvl(md.probability,1)*
nvl(md.firm_quantity,md.using_requirement_quantity),
nvl(md.firm_quantity,md.using_requirement_quantity)) *
nvl(msi.unit_weight,0)) weight,
SUM(DECODE(md.origination_type,
29,nvl(md.probability,1)*
nvl(md.firm_quantity,md.using_requirement_quantity),
nvl(md.firm_quantity,md.using_requirement_quantity)) *
nvl(msi.unit_volume,0)) volume,
SUM(decode(md.origination_type,
29, nvl(md.unmet_quantity,0),
30, decode(md.demand_source_type, 8,
nvl(old_using_requirement_quantity,0),
nvl(md.unmet_quantity,0)),
using_requirement_quantity) *
nvl(msi.unit_weight,0)) quantity2, -- unconstr weight
SUM(decode(md.origination_type,
29, nvl(md.unmet_quantity,0),
30, decode(md.demand_source_type, 8,
nvl(old_using_requirement_quantity,0),
nvl(md.unmet_quantity,0)),
using_requirement_quantity) *
nvl(msi.unit_volume,0)) quantity3 -- unconstr volume
FROM msc_form_query mfq,
msc_system_items msi,
msc_demands md
WHERE md.plan_id = mfq.number4
AND md.inventory_item_id = mfq.number1
AND md.organization_id = mfq.number2
AND md.sr_instance_id = mfq.number3
AND msi.plan_id = md.plan_id
AND msi.inventory_item_id = md.inventory_item_id
AND msi.organization_id = md.organization_id
AND msi.sr_instance_id = md.sr_instance_id
AND mfq.query_id = arg_query_id
AND mfq.number7 <> NODE_GL_FORECAST_ITEM
AND md.organization_id <> -1 -- no global forecast rows
AND (arg_query_type <> NO_INT_SHIPMENT or
(arg_query_type = NO_INT_SHIPMENT and
not(md.origination_type = PLANNED_ORDER_DEMAND and
nvl(md.source_organization_id,md.organization_id) <> md.organization_id) and
not(md.origination_type = SALES_ORDER and
nvl(md.demand_source_type,2) = 8)))
AND not exists (
select 'cancelled IR'
from msc_supplies mr
where md.origination_type = 30
and md.disposition_id = mr.transaction_id
and md.plan_id = mr.plan_id
and md.sr_instance_id = mr.sr_instance_id
and mr.disposition_status_type = 2)
GROUP BY
mfq.number5,
mfq.number6,
mfq.number3,
DECODE(md.origination_type,
CONS_KIT_DEMAND, KIT_DEMAND_OFF,
WORK_ORDER_DEMAND, UNC_KIT_DEMAND_OFF,
FORECAST, FORECAST_OFF,
SALES_ORDER,
decode(nvl(md.demand_source_type,2), 2, EXT_SALES_ORDER_OFF,
INT_SALES_ORDER_OFF),
PLANNED_ORDER_DEMAND,
decode(nvl(md.source_organization_id,md.organization_id),
md.organization_id,
UNC_KIT_DEMAND_OFF,
REQUEST_SHIPMENT_OFF),
EXPIRE_LOT_DEMAND, EXPIRE_LOT_OFF,
INTER_ORG_DEMAND, UNC_OTHER_DEMAND_OFF,
PLANNED_ORDER_SCRAP, SCRAP_DEMAND_OFF,
DISCRETE_JOB_SCRAP, SCRAP_DEMAND_OFF,
PURCHASE_ORDER_SCRAP, SCRAP_DEMAND_OFF,
PURCH_REQ_SCRAP, SCRAP_DEMAND_OFF,
RECEIPT_PO_SCRAP, SCRAP_DEMAND_OFF,
INTRANSIT_SHIPMENT_SCRAP, SCRAP_DEMAND_OFF,
OTHER_DEMAND_OFF),
DECODE(md.origination_type,
WORK_ORDER_DEMAND, KIT_DEMAND_OFF,
SALES_ORDER, decode(md.demand_source_type, 8,
REQUEST_SHIPMENT_OFF,EXP_DEMAND_OFF),
FORECAST, EXP_DEMAND_OFF,
INTER_ORG_DEMAND, OTHER_DEMAND_OFF,
0),
decode(md.origination_type, SALES_ORDER,
decode(nvl(md.demand_source_type,2), 8, md.source_organization_id, -1),
PLANNED_ORDER_DEMAND,
decode(nvl(md.source_organization_id,md.organization_id),
md.organization_id,
-1,
md.source_organization_id),
-1),
nvl(md.old_using_assembly_demand_date,md.using_assembly_demand_date),
nvl(md.firm_date,md.using_assembly_demand_date)
UNION ALL -- for planned shipments and outbound in transit
--5084210, pos from purchase order/purchase req with supplier modeled as org
SELECT mfq.number5 item_id,
mfq.number6 org_id,
mfq.number3 inst_id,
decode(ms.order_type, PLANNED_ARRIVAL, PLANNED_SHIPMENT_OFF,
PURCHASE_ORDER,PLANNED_SHIPMENT_OFF,
PURCH_REQ,PLANNED_SHIPMENT_OFF,
INTRANSIT_SHIPMENT, OUTBOUND_OFF) offset,
0 offset2,
ms.organization_id sub_org_id,
ms.new_ship_date new_date,
ms.new_ship_date old_date,
sum(nvl(ms.firm_quantity,ms.new_order_quantity)) new_quantity,
0 quantity1,
sum(nvl(ms.firm_quantity,ms.new_order_quantity) *
nvl(msi.unit_weight,0)) weight,
sum(nvl(ms.firm_quantity,ms.new_order_quantity) *
nvl(msi.unit_volume,0)) volume,
0 quantity2,
0 quantity3
FROM msc_system_items msi,
msc_supplies ms,
msc_form_query mfq
WHERE msi.plan_id = ms.plan_id
AND msi.inventory_item_id = ms.inventory_item_id
AND msi.organization_id = ms.source_organization_id
AND msi.sr_instance_id = ms.source_sr_instance_id
AND ms.order_type in (PLANNED_ARRIVAL,INTRANSIT_SHIPMENT, PURCHASE_ORDER,PURCH_REQ)
AND ms.plan_id = mfq.number4
AND ms.inventory_item_id = mfq.number1
AND ms.source_organization_id = mfq.number2
AND ms.source_sr_instance_id = mfq.number3
AND mfq.query_id = arg_query_id
AND mfq.number7 <> NODE_GL_FORECAST_ITEM
AND arg_query_type <> NO_INT_SHIPMENT
and ms.source_organization_id <> ms.organization_id
and (ms.order_type <> PURCH_REQ or
(ms.order_type = PURCH_REQ and ms.supplier_id is not null))
GROUP BY
mfq.number5,
mfq.number6,
mfq.number3,
decode(ms.order_type, PLANNED_ARRIVAL, PLANNED_SHIPMENT_OFF,
PURCHASE_ORDER,PLANNED_SHIPMENT_OFF,
PURCH_REQ,PLANNED_SHIPMENT_OFF,
INTRANSIT_SHIPMENT, OUTBOUND_OFF),
ms.organization_id,
ms.new_ship_date
UNION ALL -- for requested arrival. in msc_demands, for request shipment
SELECT mfq.number5 item_id, -- source_org_id actually store dest org id
mfq.number6 org_id, -- while org_id store source org id
mfq.number3 inst_id,
REQUEST_ARRIVAL_OFF offset,
0 offset2,
md.organization_id sub_org_id,
nvl(md.firm_date,md.planned_inbound_due_date) new_date,
nvl(md.firm_date,md.planned_inbound_due_date) old_date,
sum(nvl(md.firm_quantity,
nvl(md.old_using_requirement_quantity,md.using_requirement_quantity))) new_quantity,
0 quantity1,
sum(nvl(md.firm_quantity,
nvl(md.old_using_requirement_quantity,md.using_requirement_quantity)) *
nvl(msi.unit_weight,0)) weight,
sum(nvl(md.firm_quantity,nvl(md.old_using_requirement_quantity,md.using_requirement_quantity)) *
nvl(msi.unit_volume,0)) volume,
0 quantity2,
0 quantity3
FROM msc_form_query mfq,
msc_system_items msi,
msc_demands md
WHERE md.plan_id = mfq.number4
AND md.inventory_item_id = mfq.number1
AND md.source_organization_id = mfq.number2
AND md.sr_instance_id = mfq.number3
AND ((md.origination_type = PLANNED_ORDER_DEMAND and
md.source_organization_id <> md.organization_id ) or
(md.origination_type = 30 and md.demand_source_type =8 ))
AND msi.plan_id = md.plan_id
AND msi.inventory_item_id = md.inventory_item_id
AND msi.organization_id = md.source_organization_id
AND msi.sr_instance_id = md.sr_instance_id
AND mfq.query_id = arg_query_id
AND mfq.number7 <> NODE_GL_FORECAST_ITEM
AND arg_query_type <> NO_INT_SHIPMENT
GROUP BY
mfq.number5,
mfq.number6,
mfq.number3,
REQUEST_ARRIVAL_OFF,
md.organization_id,
nvl(md.firm_date,md.planned_inbound_due_date)
UNION ALL
SELECT mfq.number5 item_id,
mfq.number6 org_id,
mfq.number3 inst_id,
SAFETY_STOCK_OFF offset,
0 offset2,
safety.organization_id sub_org_id,
safety.period_start_date new_date,
safety.period_start_date old_date,
sum(safety.safety_stock_quantity) new_quantity,
0 quantity1,
sum(safety.safety_stock_quantity * nvl(msi.unit_weight,0)) weight,
sum(safety.safety_stock_quantity * nvl(msi.unit_volume,0)) volume,
0 quantity2,
0 quantity3
FROM msc_safety_stocks safety,
msc_system_items msi,
msc_form_query mfq
WHERE trunc(safety.period_start_date) <= last_date
AND safety.organization_id = mfq.number2
AND safety.sr_instance_id = mfq.number3
AND safety.plan_id = mfq.number4
AND safety.inventory_item_id = mfq.number1
AND mfq.query_id = arg_query_id
AND mfq.number7 <> NODE_GL_FORECAST_ITEM
AND msi.plan_id = safety.plan_id
AND msi.inventory_item_id = safety.inventory_item_id
AND msi.organization_id = safety.organization_id
AND msi.sr_instance_id = safety.sr_instance_id
GROUP BY mfq.number5,
mfq.number6,
mfq.number3,
safety.period_start_date,
safety.organization_id
UNION ALL -- for target and max qty
SELECT mfq.number5 item_id,
mfq.number6 org_id,
mfq.number3 inst_id,
MAX_QTY_OFF offset,
TARGET_QTY_OFF offset2,
mil.organization_id sub_org_id,
mil.inventory_date new_date,
mil.inventory_date old_date,
sum(nvl(mil.max_quantity,0)) new_quantity,
sum(nvl(mil.target_quantity,0)) quantity1,
sum(nvl(mil.max_quantity,0) * nvl(msi.unit_weight,0)) weight,
sum(nvl(mil.max_quantity,0) * nvl(msi.unit_volume,0)) volume,
sum(nvl(mil.target_quantity,0) * nvl(msi.unit_weight,0)) quantity2,
sum(nvl(mil.target_quantity,0) * nvl(msi.unit_volume,0)) quantity3
FROM msc_inventory_levels mil,
msc_system_items msi,
msc_form_query mfq
WHERE trunc(mil.inventory_date) <= last_date
AND mil.organization_id = mfq.number2
AND mil.sr_instance_id = mfq.number3
AND mil.plan_id = mfq.number4
AND mil.inventory_item_id = mfq.number1
AND mfq.query_id = arg_query_id
AND mfq.number7 <> NODE_GL_FORECAST_ITEM
AND msi.plan_id = mil.plan_id
AND msi.inventory_item_id = mil.inventory_item_id
AND msi.organization_id = mil.organization_id
AND msi.sr_instance_id = mil.sr_instance_id
GROUP BY mfq.number5,mfq.number6,mfq.number3,
mil.inventory_date, mil.organization_id
UNION ALL
--------------------------------------------------------------------
-- This select will ensure that all selected items get into cursor
-- even though they do not have any activity
---------------------------------------------------------------------
SELECT mfq.number5 item_id,
mfq.number6 org_id,
mfq.number3 inst_id,
BEG_ON_HAND_OFF offset,
0 offset2,
-1 sub_org_id,
to_date(1, 'J') new_date,
to_date(1, 'J') old_date,
0 new_quantity,
0 quantity1,
0 weight,
0 volume,
0 quantity2,
0 quantity3
FROM msc_form_query mfq
WHERE mfq.query_id = arg_query_id
AND mfq.number7 <> NODE_GL_FORECAST_ITEM
ORDER BY
1, 2, 3,7,8,4,5,6;
SELECT msi.calculate_atp
FROM msc_system_items msi,
msc_form_query mfq
WHERE msi.inventory_item_id = mfq.number1
AND msi.organization_id = mfq.number2
AND msi.plan_id = arg_plan_id
AND msi.sr_instance_id = mfq.number3
AND mfq.query_id = arg_query_id
and mfq.number5 = p_item_id
and mfq.number6 = p_org_id
and mfq.number3 = p_inst_id;
INSERT INTO msc_drp_hori_plans(
query_id,
organization_id,
sr_instance_id,
inventory_item_id,
row_type,
sub_org_id,
horizontal_plan_type,
bucket_date,
last_update_date,
last_updated_by,
creation_date,
created_by,
quantity,
weight,
volume)
VALUES (
arg_query_id,
p_org_id,
p_inst_id,
p_item_id,
row_header_type(a), -- row_type
-1, -- sub org id
1, -- non enterprise view
var_dates(bkt),
SYSDATE,
-1,
SYSDATE,
-1,
bkt_quantity(bkt),
bkt_weight(bkt),
bkt_volume(bkt));
INSERT INTO msc_drp_hori_plans(
query_id,
organization_id,
sr_instance_id,
inventory_item_id,
row_type,
sub_org_id,
horizontal_plan_type,
bucket_date,
last_update_date,
last_updated_by,
creation_date,
created_by,
quantity,
weight,
volume)
VALUES (
arg_query_id,
p_org_id,
p_inst_id,
p_item_id,
sub_row_header(a).row_type,
sub_row_header(a).sub_org_id,
1, -- non enterprise view
var_dates(bkt),
SYSDATE,
-1,
SYSDATE,
-1,
bkt_quantity(bkt),
bkt_weight(bkt),
bkt_volume(bkt));
INSERT INTO msc_drp_hori_plans(
query_id,
organization_id,
sr_instance_id,
inventory_item_id,
row_type,
sub_org_id,
horizontal_plan_type,
bucket_date,
last_update_date,
last_updated_by,
creation_date,
created_by,
quantity,
weight,
volume)
VALUES (
arg_query_id,
p_org_id,
p_inst_id,
p_item_id,
etp_bkt_row_type(a), -- row_type
-1, -- sub org id
10, -- enterprise view
SYSDATE, -- bucket date
SYSDATE,
-1,
SYSDATE,
-1,
etp_bkt_quantity(a),
etp_bkt_weight(a),
etp_bkt_volume(a));
sub_row_header.delete;
sub_row_detail.delete;
select char1, char2
from msc_form_query
where query_id = p_query_id
and number5 = p_item_id
and number6 = p_org_id
and number3 = p_inst_id;
select distinct md.demand_priority
from msc_demands md,
msc_form_query mfq_item,
msc_plans mp
where md.plan_id = p_plan_id
and md.inventory_item_id = mfq_item.number1
and md.organization_id = mfq_item.number2
and md.sr_instance_id = mfq_item.number3
and mfq_item.query_id = p_query_id
and mfq_item.number5 = p_item_id
and mfq_item.number6 = p_org_id
and mfq_item.number3 = p_inst_id
and mfq_item.number7 <> 6 -- NODE_GL_FORECAST_ITEM
and ((p_start_date <> trunc(mp.curr_start_date) and
trunc(md.using_assembly_demand_date) between p_start_date
and p_end_date) or
(p_start_date = trunc(mp.curr_start_date) and
trunc(md.using_assembly_demand_date) <= p_end_date))
and mp.plan_id = md.plan_id
order by md.demand_priority ;
select md.demand_priority,
sum(nvl(md.firm_quantity,md.using_requirement_quantity))
from msc_demands md,
msc_form_query mfq_item,
msc_plans mp
where md.plan_id = p_plan_id
and md.inventory_item_id = mfq_item.number1
and md.organization_id = mfq_item.number2
and md.sr_instance_id = mfq_item.number3
and mfq_item.number7 <> 6 -- NODE_GL_FORECAST_ITEM
and mfq_item.query_id = p_query_id
and mfq_item.number5 = p_item_id
and mfq_item.number6 = p_org_id
and mfq_item.number3 = p_inst_id
and ((p_start_date <> trunc(mp.curr_start_date) and
trunc(md.using_assembly_demand_date) between p_start_date
and p_end_date) or
(p_start_date = trunc(mp.curr_start_date) and
trunc(md.using_assembly_demand_date) <= p_end_date))
and mp.plan_id = md.plan_id
group by md.demand_priority
order by md.demand_priority;