PO Internal Requisitions/Deliveries Discrepancy
Description
Categories: Enginatics
Repository: Github
Repository: Github
Application: Purchasing
Description: Internal Requisitions/Deliveries Discrepancy Report
Source: Internal Requisitions/Deliveries Discrepancy Report (XML)
Short Name: POXRQSDD_XML
Description: Internal Requisitions/Deliveries Discrepancy Report
Source: Internal Requisitions/Deliveries Discrepancy Report (XML)
Short Name: POXRQSDD_XML
Run
PO Internal Requisitions/Deliveries Discrepancy and other Oracle EBS reports with Blitz Report™ on our demo environment
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 |
Parameter Name | SQL text | Validation | |
---|---|---|---|
Operating Unit |
|
LOV | |
Requisition Number From |
|
LOV | |
Requisition Number To |
|
LOV | |
Requester |
|
LOV Oracle | |
Requisition Date From |
|
Date | |
Requisition Date To |
|
Date | |
Sort By |
|
LOV Oracle |