I'm working on a query bound for Discoverer which pulls the aggregated supply and demand for an item and buckets it into months. So for any given item, I need to show the item, onhand, cost, aggregated supply (planned orders, requisitions, pos), and aggregated demand (planned order demand, jobs) - all bucketed by months.
The code below works okay to find all of the data for July, but I also need to show August and September. I'm thinking I could use a union but am reluctant because the query already runs kind of slow and I'm not sure if I'm on the right track.
----------------------------------------
Database Server
----------------------------------------
RDBMS : 10.2.0.3.0
Oracle Applications : 11.5.9
-Tracy
select
item.inventory_item_id, item.organization_code, item.item, item.description
, item.make_buy,item.planner_code
, planned.compile_designator, planned.order_type_text, sum(planned.quantity_rate)planned_total
, planned.mrp_sugg_due_month
, sum(job.required_quantity-job.quantity_issued)job_open, job.required_month
, onhand.total_qoh
, purchase.item_revision prev, purchase.promised_month, purchase.ship_to_organization_id
, sum((purchase.quantity-purchase.quantity_cancelled)-purchase.quantity_received)po_open
, req.item_revision rrev, req.destination_organization_id, req.org_id, req.need_by_month
, sum((req.quantity-req.quantity_cancelled)-req.quantity_delivered)req_open
, cost.item_cost,cost.cost
from
--item--
(select mtl.inventory_item_id, mtl.segment1 item,mtl.description,decode(mtl.planning_make_buy_code,1,'Make',2,'Buy') make_buy
,mtl.organization_id, mtp.organization_code, mtl.planner_code
,to_char(add_months(sysdate,+1),'YYYY_MM')month1, to_char(add_months(sysdate,+2),'YYYY_MM')month2
,to_char(add_months(sysdate,+3),'YYYY_MM')month3
from inv.mtl_system_items_b mtl, inv.mtl_parameters mtp
where mtl.organization_id = mtp.organization_id
)item,
--planned orders - 3 months --
(select compile_designator,organization_id,inventory_item_id,order_type_text,nvl(quantity_rate,0)quantity_rate,new_due_date
,to_char(trunc(new_due_date,'MM'),'YYYY_MM')mrp_sugg_due_month
from apps.mrp_orders_sc_v
where order_type_text in ('Planned order','Planned order demand')
and to_char(trunc(new_due_date,'MM'),'YYYY_MM') <= to_char(add_months(:Month,+2),'YYYY_MM')
and to_char(trunc(new_due_date,'MM'),'YYYY_MM') >= to_char(:Month,'YYYY_MM')
)planned,
--jobs - 3 months--
(select organization_id,wip_entity_name job, inventory_item_id,concatenated_segments,nvl(required_quantity,0)required_quantity
,nvl(quantity_issued,0)quantity_issued, date_required,to_char(trunc(date_required,'MM'),'YYYY_MM') required_month
,wip_entity_id,creation_date, wip_job_status
from apps.wip_requirement_ops_inq_v
where primary_item_id <>inventory_item_id
and wip_job_status not in ('Closed','Cancelled','Complete')
and to_char(trunc(date_required,'MM'),'YYYY_MM') <= to_char(add_months(:Month,+2),'YYYY_MM')
and to_char(trunc(date_required,'MM'),'YYYY_MM') >= to_char(:Month,'YYYY_MM')
)job,
--qty onhand--
(select inventory_item_id,organization_id,sum(nvl(transaction_quantity,0))total_qoh
from inv.mtl_onhand_quantities_detail
group by inventory_item_id, organization_id
)onhand,
-- po - 3 months--
(select pol.item_id, pol.item_revision, nvl(pll.quantity,0)quantity, nvl(pll.quantity_received,0)quantity_received
, nvl(pll.quantity_rejected,0),nvl(pll.quantity_cancelled,0)quantity_cancelled,poh.segment1 po_num
,pll.promised_date, to_char(trunc(pll.promised_date,'MM'),'YYYY_MM')promised_month
,pll.shipment_num,pll.ship_to_organization_id
from po.po_lines_all pol, po.po_headers_all poh, po.po_line_locations_all pll
where poh.po_header_id = pol.po_header_id
and pol.po_header_id = pll.po_header_id
and pol.po_line_id = pll.po_line_id
and pol.cancel_flag != 'Y'
and pol.item_id is not null
and to_char(trunc(pll.promised_date,'MM'),'YYYY_MM')<= to_char(add_months(:Month,+2),'YYYY_MM')
and to_char(trunc(pll.promised_date,'MM'),'YYYY_MM')>= to_char(:Month,'YYYY_MM')
)purchase,
--reqs - 3 months--
(select prh.segment1 req_number,nvl(prl.quantity,0)quantity,nvl(prl.quantity_delivered,0)quantity_delivered
,nvl(prl.quantity_cancelled,0)quantity_cancelled
,prl.destination_organization_id,prl.org_id,prl.item_id,prl.item_revision,prl.need_by_date
,to_char(trunc(prl.need_by_date,'MM'),'YYYY_MM')need_by_month
from po.po_requisition_headers_all prh, po.po_requisition_lines_all prl
where prh.requisition_header_id = prl.requisition_header_id(+)
and nvl(prl.cancel_flag,'N') !='Y'
and prh.authorization_status != 'CANCELLED'
and to_char(trunc(prl.need_by_date,'MM'),'YYYY_MM') <= to_char(add_months(:Month,+2),'YYYY_MM')
and to_char(trunc(prl.need_by_date,'MM'),'YYYY_MM') >= to_char(:Month,'YYYY_MM')
)req,
--cost--
(select msib.inventory_item_id,msib.organization_id,cqm.material_cost,cic.item_cost
,(case when cqm.material_cost=0 then cic.item_cost else cqm.material_cost end) cost, cqm.cost_group_id
from inv.mtl_system_items_b msib
,(select cql.cost_group_id,cql.inventory_item_id,cql.organization_id,cql.layer_quantity,cql.material_cost,mp.organization_code
from bom.cst_quantity_layers cql, inv.mtl_parameters mp
where mp.default_cost_group_id = cql.cost_group_id) cqm
,bom.cst_item_costs cic
where msib.inventory_item_id = cqm.inventory_item_id(+)
and msib.organization_id = cqm.organization_id(+)
and msib.inventory_item_id = cic.inventory_item_id(+)
and msib.organization_id = cic.organization_id(+)
)cost
where item.inventory_item_id = job.inventory_item_id(+)
and item.organization_id = job.organization_id(+)
and item.month1 = job.required_month(+) -- 2009_07 --
and item.inventory_item_id = onhand.inventory_item_id(+)
and item.organization_id = onhand.organization_id(+)
and item.inventory_item_id = purchase.item_id(+)
and item.month1 = purchase.promised_month(+) -- 2009_07 --
and item.inventory_item_id = req.item_id(+)
and item.month1 = req.need_by_month(+) -- 2009_07 --
and item.inventory_item_id = cost.inventory_item_id(+)
and item.organization_id = cost.organization_id(+)
and item.inventory_item_id = planned.inventory_item_id(+)
and item.organization_id = planned.organization_id(+)
and item.month1 = planned.mrp_sugg_due_month(+) -- 2009_07 --
and item.make_buy = 'Buy'
and item.item in ('161309040','744L755','150-GFM') --test items --
group by item.inventory_item_id,item.organization_code,item.item,item.description,item.make_buy,item.planner_code
,job.required_month ,onhand.total_qoh , purchase.item_revision, purchase.promised_month
,purchase.ship_to_organization_id ,cost.item_cost,cost.cost
,req.item_revision, req.destination_organization_id,req.org_id,req.need_by_month
,planned.compile_designator,planned.order_type_text,planned.mrp_sugg_due_month
order by item.organization_code,item.item