PO Internal Requisitions/Deliveries Discrepancy

with
req_qry as
(
select
 hou.name operating_unit,
 prha.segment1 req_number,
 xxen_util.meaning(prha.type_lookup_code,'REQUISITION TYPE',201) req_type,
 prla.creation_date req_date,
 papf.full_name requestor,
 prla.line_num line_number,
 pspa.order_source_id order_source_Id,
 msiv.concatenated_segments item,
 prla.item_revision item_revision,
 msiv.description item_description,
 ood.organization_name source_organization,
 prla.source_subinventory source_subinventory,
 prla.unit_meas_lookup_code uom,
 prla.unit_price unit_price,
 prla.quantity - nvl(prla.quantity_cancelled,0) quantity_requested,
 prla.quantity_delivered quantity_delivered,
 prla.quantity_delivered * nvl(prla.unit_price,0) amount_delivered,
 prla.requisition_header_id,
 prla.requisition_line_id
from
 po_requisition_headers prha,
 po_requisition_lines prla,
 mtl_system_items_vl msiv,
 per_all_people_f papf,
 org_organization_definitions ood,
 po_system_parameters pspa,
 hr_operating_units hou
where
 1=1 and
 hou.organization_id = prha.org_id and
 pspa.org_id = prha.org_id and
 prla.requisition_header_id = prha.requisition_header_id and
 prla.source_type_code = 'INVENTORY' and
 nvl(prha.transferred_to_oe_flag,'N') = 'Y' and
 prla.source_organization_id = ood.organization_id and
 papf.person_id = prla.to_person_id and
 msiv.inventory_item_id = prla.item_id and
 nvl(msiv.organization_id,prla.source_organization_id) = prla.source_organization_id and
 trunc(sysdate) between papf.effective_start_date and papf.effective_end_date and
 prla.quantity_delivered > 0
),
ship_qry as
(
select
 oola.order_source_id,
 oola.orig_sys_document_ref,
 oola.orig_sys_line_ref,
 oola.source_document_id,
 oola.source_document_line_id,
 sum(oola.shipped_quantity) quantity_shipped,
 sum(nvl(oola.unit_selling_price,0) * nvl(oola.shipped_quantity,0)) amount_shipped
from
 oe_order_lines_all oola,
 oe_order_headers_all ooha
where
 oola.header_id = ooha.header_id and
 oola.shipped_quantity is not null
group by
 oola.order_source_id,
 oola.orig_sys_document_ref,
 oola.orig_sys_line_ref,
 oola.source_document_id,
 oola.source_document_line_id
)
--
-- Main Query Starts Here
--
select
 req.operating_unit,
 req.req_number,
 req.req_type,
 req.req_date,
 req.requestor,
 req.line_number,
 req.item,
 req.item_revision,
 req.item_description,
 req.source_organization,
 req.source_subinventory,
 req.unit_price,
 req.uom,
 req.quantity_requested,
 shp.quantity_shipped,
 shp.amount_shipped,
 req.quantity_delivered,
 req.amount_delivered,
 shp.quantity_shipped - nvl(req.quantity_delivered,0) quantity_variance,
 shp.amount_shipped - nvl(req.amount_delivered,0) cost_variance
from
 req_qry  req,
 ship_qry shp
where
 req.order_source_id = shp.order_source_id (+) and
 req.req_number = shp.orig_sys_document_ref (+) and
 req.line_number = shp.orig_sys_line_ref (+) and
 req.requisition_header_id = shp.source_document_id (+) and
 req.requisition_line_id = shp.source_document_line_id (+)
order by
 case :p_orderby
 when 'REQUESTOR' then req.requestor
 when 'SUBINVENTORY' then req.source_subinventory
 else null
 end,
 req.req_date,
 req.req_number