Po detail query

select distinct poh.segment1 po_number,
       poh.REVISION_NUM,
       poh.COMMENTS Description,
       poh.TYPE_LOOKUP_CODE po_type,
       poh.AUTHORIZATION_STATUS PO_status,
       poh.CREATION_DATE order_date,
       asp.VENDOR_NAME,
       assa.VENDOR_SITE_CODE,
       poh.CURRENCY_CODE,
       sum(nvl(pol.UNIT_PRICE,0)*nvl(pol.QUANTITY,0)) amount,
       ppx.FULL_NAME buyer,
       poh.attribute4 po_category,
       rsh.RECEIPT_NUM,
       aia.INVOICE_NUM
from po_headers_all poh,
po_lines_all pol,
--po_line_locations_all plla,
ap_suppliers asp,
ap_supplier_sites_all assa,
per_people_x ppx,
rcv_shipment_lines rsl,
rcv_shipment_headers rsh,
ap_invoice_lines_all aila,
ap_invoices_all aia
where poh.po_header_id=pol.PO_HEADER_ID
--and poh.PO_HEADER_ID=plla.PO_HEADER_ID
--and pol.PO_LINE_ID=plla.PO_LINE_ID
and poh.VENDOR_ID=asp.VENDOR_ID
and poh.VENDOR_SITE_ID=assa.VENDOR_SITE_ID
and poh.AGENT_ID=ppx.PERSON_ID
and poh.PO_HEADER_ID=rsl.PO_HEADER_ID(+)
and pol.PO_LINE_ID=nvl(rsl.PO_LINE_ID,pol.PO_LINE_ID)
--and plla.LINE_LOCATION_ID=nvl(rsl.PO_LINE_LOCATION_ID,plla.LINE_LOCATION_ID)
--and plla.SHIP_TO_ORGANIZATION_ID=nvl(rsh.ship_to_org_id,plla.SHIP_TO_ORGANIZATION_ID)
and rsl.SHIPMENT_HEADER_ID=rsh.SHIPMENT_HEADER_ID(+)
and rsl.SHIPMENT_LINE_ID=aila.RCV_SHIPMENT_LINE_ID(+)
and poh.PO_HEADER_ID=nvl(aila.PO_HEADER_ID,poh.PO_HEADER_ID)
and pol.PO_LINE_ID=nvl(aila.PO_LINE_ID,pol.PO_LINE_ID)
and aila.INVOICE_ID=aia.INVOICE_ID(+)
and poh.segment1='115000007'
and poh.TYPE_LOOKUP_CODE='STANDARD'
group by 
       poh.segment1,
       poh.REVISION_NUM,
       poh.COMMENTS,
       poh.TYPE_LOOKUP_CODE,
       poh.AUTHORIZATION_STATUS,
       poh.CREATION_DATE,
       asp.VENDOR_NAME,
       assa.VENDOR_SITE_CODE,
       poh.CURRENCY_CODE,
       ppx.FULL_NAME,
       poh.attribute4,
       rsh.RECEIPT_NUM,
       aia.INVOICE_NUM
order by 1,13,14

----------------------------------------------------------------------------------------------



select distinct poh.segment1 po_number,
       null RELEASE_NUM,
       poh.REVISION_NUM,
       poh.COMMENTS Description,
       poh.TYPE_LOOKUP_CODE po_type,
       poh.AUTHORIZATION_STATUS PO_status,
       poh.CREATION_DATE order_date,
       asp.VENDOR_NAME,
       assa.VENDOR_SITE_CODE,
       poh.CURRENCY_CODE,
--       sum(nvl(pol.UNIT_PRICE,0)*nvl(pol.QUANTITY,0)) amount,
       NVL(SUM(nvl(pol.UNIT_PRICE,0)*NVL(rsl.QUANTITY_RECEIVED,0)),sum(nvl(pol.UNIT_PRICE,0)*nvl(pol.QUANTITY,0))) amount,
       ppx.FULL_NAME buyer,
       poh.attribute4 po_category,
       rsh.RECEIPT_NUM,
       aia.INVOICE_NUM
from po_headers_all poh,
po_lines_all pol,
--po_line_locations_all plla,
ap_suppliers asp,
ap_supplier_sites_all assa,
per_people_x ppx,
rcv_shipment_lines rsl,
rcv_shipment_headers rsh,
ap_invoice_lines_all aila,
ap_invoices_all aia
where poh.po_header_id=pol.PO_HEADER_ID
--and poh.PO_HEADER_ID=plla.PO_HEADER_ID
--and pol.PO_LINE_ID=plla.PO_LINE_ID
and poh.VENDOR_ID=asp.VENDOR_ID
and poh.VENDOR_SITE_ID=assa.VENDOR_SITE_ID
and poh.AGENT_ID=ppx.PERSON_ID
and poh.PO_HEADER_ID=rsl.PO_HEADER_ID(+)
and pol.PO_LINE_ID=nvl(rsl.PO_LINE_ID,pol.PO_LINE_ID)
--and plla.LINE_LOCATION_ID=nvl(rsl.PO_LINE_LOCATION_ID,plla.LINE_LOCATION_ID)
--and plla.SHIP_TO_ORGANIZATION_ID=nvl(rsh.ship_to_org_id,plla.SHIP_TO_ORGANIZATION_ID)
and rsl.SHIPMENT_HEADER_ID=rsh.SHIPMENT_HEADER_ID(+)
and poh.PO_HEADER_ID=aila.PO_HEADER_ID(+)
and pol.PO_LINE_ID=nvl(aila.PO_LINE_ID,pol.PO_LINE_ID)
and nvl(rsl.SHIPMENT_LINE_ID,-12)=nvl(aila.RCV_SHIPMENT_LINE_ID,nvl(rsl.SHIPMENT_LINE_ID,-12))
and aila.INVOICE_ID=aia.INVOICE_ID(+)
and trunc(poh.CREATION_DATE) between trunc(:p_from) and trunc(:p_to)
--and poh.segment1='115000007'
and poh.TYPE_LOOKUP_CODE='STANDARD'
group by 
       poh.segment1,
       poh.REVISION_NUM,
       poh.COMMENTS,
       poh.TYPE_LOOKUP_CODE,
       poh.AUTHORIZATION_STATUS,
       poh.CREATION_DATE,
       asp.VENDOR_NAME,
       assa.VENDOR_SITE_CODE,
       poh.CURRENCY_CODE,
       ppx.FULL_NAME,
       poh.attribute4,
       rsh.RECEIPT_NUM,
       aia.INVOICE_NUM
union all
select distinct poh.segment1 po_number,
       pra.RELEASE_NUM,
       poh.REVISION_NUM,
       poh.COMMENTS Description,
       poh.TYPE_LOOKUP_CODE po_type,
       poh.AUTHORIZATION_STATUS PO_status,
       poh.CREATION_DATE order_date,
       asp.VENDOR_NAME,
       assa.VENDOR_SITE_CODE,
       poh.CURRENCY_CODE,
--       sum(nvl(pol.UNIT_PRICE,0)*nvl(pol.QUANTITY,0)) amount,
       NVL(SUM(nvl(pol.UNIT_PRICE,0)*NVL(rsl.QUANTITY_RECEIVED,0)),sum(nvl(pol.UNIT_PRICE,0)*nvl(pol.QUANTITY,0))) amount,
       ppx.FULL_NAME buyer,
       poh.attribute4 po_category,
       rsh.RECEIPT_NUM,
       aia.INVOICE_NUM
from po_headers_all poh,
     PO_RELEASES_ALL PRA,
     po_lines_all pol,
--     po_line_locations_all plla,
     ap_suppliers asp,
     ap_supplier_sites_all assa,
     per_people_x ppx,
     rcv_shipment_lines rsl,
     rcv_shipment_headers rsh,
     ap_invoice_lines_all aila,
     ap_invoices_all aia
where poh.po_header_id=pol.PO_HEADER_ID
and poh.TYPE_LOOKUP_CODE='BLANKET'
and poh.PO_HEADER_ID=pra.PO_HEADER_ID
--and poh.PO_HEADER_ID=plla.PO_HEADER_ID
--and pol.PO_LINE_ID=plla.PO_LINE_ID
and poh.VENDOR_ID=asp.VENDOR_ID
and poh.VENDOR_SITE_ID=assa.VENDOR_SITE_ID
and poh.AGENT_ID=ppx.PERSON_ID
and poh.PO_HEADER_ID=rsl.PO_HEADER_ID(+)
and pra.PO_RELEASE_ID=nvl(rsl.PO_RELEASE_ID,pra.PO_RELEASE_ID)
and pol.PO_LINE_ID=nvl(rsl.PO_LINE_ID,pol.PO_LINE_ID)
--and plla.LINE_LOCATION_ID=nvl(rsl.PO_LINE_LOCATION_ID,plla.LINE_LOCATION_ID)
--and plla.SHIP_TO_ORGANIZATION_ID=nvl(rsh.ship_to_org_id,plla.SHIP_TO_ORGANIZATION_ID)
--and poh.PO_HEADER_ID=nvl(aila.PO_HEADER_ID,poh.PO_HEADER_ID)
and poh.PO_HEADER_ID=aila.PO_HEADER_ID(+)
and pra.PO_RELEASE_ID=nvl(aila.PO_RELEASE_ID,pra.PO_RELEASE_ID)
and pol.PO_LINE_ID=nvl(aila.PO_LINE_ID,pol.PO_LINE_ID)
and rsl.SHIPMENT_HEADER_ID=rsh.SHIPMENT_HEADER_ID(+)
and nvl(rsl.SHIPMENT_LINE_ID,-12)=nvl(aila.RCV_SHIPMENT_LINE_ID,nvl(rsl.SHIPMENT_LINE_ID,-12))
and aila.INVOICE_ID=aia.INVOICE_ID(+)
and trunc(poh.CREATION_DATE) between trunc(:p_from) and trunc(:p_to)
--and poh.segment1='115000001'
group by 
       poh.segment1,
       pra.RELEASE_NUM,

       poh.REVISION_NUM,
       poh.COMMENTS,
       poh.TYPE_LOOKUP_CODE,
       poh.AUTHORIZATION_STATUS,
       poh.CREATION_DATE,
       asp.VENDOR_NAME,
       assa.VENDOR_SITE_CODE,
       poh.CURRENCY_CODE,
       ppx.FULL_NAME,
       poh.attribute4,
       rsh.RECEIPT_NUM,
       aia.INVOICE_NUM
order by 1,2,13,14

-------------------------------------------------------FullQuery

select distinct 
       poh.segment1 po_number,
       null RELEASE_NUM,
       poh.REVISION_NUM,
       msib.SEGMENT1 item_code,
       msib.DESCRIPTION item_desc,
       mc.SEGMENT1 item_category,
       msib.PRIMARY_UOM_CODE uom,
       poh.AUTHORIZATION_STATUS PO_status,
       poh.CREATION_DATE order_date,
       asp.VENDOR_NAME,
       assa.VENDOR_SITE_CODE,
       poh.CURRENCY_CODE,
       pol.UNIT_PRICE,
       nvl(pol.QUANTITY,0) QUANTITY,
       poh.TYPE_LOOKUP_CODE po_type,
       sum(nvl(rsl.QUANTITY_RECEIVED,0)) QUANTITY_RECEIVED,
       (nvl(pol.QUANTITY,0)-sum(nvl(rsl.QUANTITY_RECEIVED,0))) remaining_qty,
       ppx.FULL_NAME buyer,
       poh.attribute4 po_category
from po_headers_all poh,
     po_lines_all pol,
     po_line_locations_all plla,
     mtl_system_items_b msib,
     mtl_item_categories mic,
     mtl_categories mc,
     ap_suppliers asp,
     ap_supplier_sites_all assa,
     per_people_x ppx,
     rcv_shipment_lines rsl,
     rcv_shipment_headers rsh
where poh.po_header_id=pol.PO_HEADER_ID
and poh.TYPE_LOOKUP_CODE='STANDARD'
and poh.PO_HEADER_ID=plla.PO_HEADER_ID
and pol.PO_LINE_ID=plla.PO_LINE_ID
and pol.ITEM_ID=msib.INVENTORY_ITEM_ID
and plla.SHIP_TO_ORGANIZATION_ID=msib.ORGANIZATION_ID 
and msib.INVENTORY_ITEM_ID=mic.INVENTORY_ITEM_ID
and msib.ORGANIZATION_ID=mic.ORGANIZATION_ID  
and mic.CATEGORY_ID=mc.CATEGORY_ID
and poh.VENDOR_ID=asp.VENDOR_ID
and poh.VENDOR_SITE_ID=assa.VENDOR_SITE_ID
and poh.AGENT_ID=ppx.PERSON_ID
and poh.PO_HEADER_ID=rsl.PO_HEADER_ID(+)
and pol.PO_LINE_ID=nvl(rsl.PO_LINE_ID,pol.PO_LINE_ID)
and plla.LINE_LOCATION_ID=nvl(rsl.PO_LINE_LOCATION_ID,plla.LINE_LOCATION_ID)
and rsl.SHIPMENT_HEADER_ID=rsh.SHIPMENT_HEADER_ID(+)
AND RSL.TO_ORGANIZATION_ID=346
and to_char(poh.CREATION_DATE,'dd/mm/yyyy') between '01/04/2015' and '31/03/2016'
group by poh.segment1,
       poh.REVISION_NUM,
       msib.SEGMENT1,
       msib.DESCRIPTION,
       mc.SEGMENT1,
       msib.PRIMARY_UOM_CODE,
       poh.AUTHORIZATION_STATUS,
       poh.CREATION_DATE,
       asp.VENDOR_NAME,
       assa.VENDOR_SITE_CODE,
       poh.CURRENCY_CODE,
       pol.UNIT_PRICE,
       pol.QUANTITY,
       poh.TYPE_LOOKUP_CODE,
       ppx.FULL_NAME,
       poh.attribute4
union all
select distinct 
       poh.segment1 po_number,
       pra.RELEASE_NUM,
       poh.REVISION_NUM,
       msib.SEGMENT1 item_code,
       msib.DESCRIPTION item_desc,
       mc.SEGMENT1 item_category,
       msib.PRIMARY_UOM_CODE uom,
       poh.AUTHORIZATION_STATUS PO_status,
       poh.CREATION_DATE order_date,
       asp.VENDOR_NAME,
       assa.VENDOR_SITE_CODE,
       poh.CURRENCY_CODE,
       pol.UNIT_PRICE,
       sum(nvl(pda.QUANTITY_ORDERED,0)) QUANTITY,
       poh.TYPE_LOOKUP_CODE po_type,
       sum(nvl(rsl.QUANTITY_RECEIVED,0)) QUANTITY_RECEIVED,
       (sum(nvl(pda.QUANTITY_ORDERED,0))-sum(nvl(rsl.QUANTITY_RECEIVED,0))) remaining_qty,
       ppx.FULL_NAME buyer,
       poh.attribute4 po_category
from po_headers_all poh,
     PO_RELEASES_ALL PRA,
     po_lines_all pol,
     po_line_locations_all plla,
     po_distributions_all pda,
     mtl_system_items_b msib,
     mtl_item_categories mic,
     mtl_categories mc,
     ap_suppliers asp,
     ap_supplier_sites_all assa,
     per_people_x ppx,
     rcv_shipment_lines rsl,
     rcv_shipment_headers rsh
where poh.po_header_id=pol.PO_HEADER_ID
and poh.TYPE_LOOKUP_CODE='BLANKET'
and poh.PO_HEADER_ID=pra.PO_HEADER_ID
and poh.PO_HEADER_ID=plla.PO_HEADER_ID
and pol.PO_LINE_ID=plla.PO_LINE_ID
and poh.PO_HEADER_ID=pda.PO_HEADER_ID
and pra.PO_RELEASE_ID=pda.PO_RELEASE_ID
and pol.PO_LINE_ID=pda.PO_LINE_ID
and plla.LINE_LOCATION_ID=pda.LINE_LOCATION_ID
and pol.ITEM_ID=msib.INVENTORY_ITEM_ID
and plla.SHIP_TO_ORGANIZATION_ID=msib.ORGANIZATION_ID 
and msib.INVENTORY_ITEM_ID=mic.INVENTORY_ITEM_ID
and msib.ORGANIZATION_ID=mic.ORGANIZATION_ID  
and mic.CATEGORY_ID=mc.CATEGORY_ID
and poh.VENDOR_ID=asp.VENDOR_ID
and poh.VENDOR_SITE_ID=assa.VENDOR_SITE_ID
and poh.AGENT_ID=ppx.PERSON_ID
and poh.PO_HEADER_ID=rsl.PO_HEADER_ID(+)
and pra.PO_RELEASE_ID=nvl(rsl.PO_RELEASE_ID,pra.PO_RELEASE_ID)
and pol.PO_LINE_ID=nvl(rsl.PO_LINE_ID,pol.PO_LINE_ID)
and plla.LINE_LOCATION_ID=nvl(rsl.PO_LINE_LOCATION_ID,plla.LINE_LOCATION_ID)
and pda.PO_DISTRIBUTION_ID=nvl(rsl.PO_DISTRIBUTION_ID,pda.PO_DISTRIBUTION_ID)
AND RSL.TO_ORGANIZATION_ID=346
and rsl.SHIPMENT_HEADER_ID=rsh.SHIPMENT_HEADER_ID(+)
--and trunc(poh.CREATION_DATE) between trunc(:p_from) and trunc(:p_to)
and to_char(poh.CREATION_DATE,'dd/mm/yyyy') between '01/04/2015' and '31/03/2016'
group by poh.segment1,
       pra.RELEASE_NUM,
       poh.REVISION_NUM,
       msib.SEGMENT1,
       msib.DESCRIPTION,
       mc.SEGMENT1,
       msib.PRIMARY_UOM_CODE,
       poh.AUTHORIZATION_STATUS,
       poh.CREATION_DATE,
       asp.VENDOR_NAME,
       assa.VENDOR_SITE_CODE,
       poh.CURRENCY_CODE,
       pol.UNIT_PRICE,
       poh.TYPE_LOOKUP_CODE,
       ppx.FULL_NAME,
       poh.attribute4
   
----------------------------------------------------------------------------------------

select 
       po_number,
       RELEASE_NUM,
       Release_date,
       REVISION_NUM,
       po_desc,
       po_type,
       PO_status,
       order_date,
       expected_delivery_date,
       pr_number,
       pr_date,
       sum(pr_value) pr_value,
       VENDOR_NAME,
       Vendor_code,
       VENDOR_SITE_CODE,
       CURRENCY_CODE,
       sum(outstanding_qty) outstanding_qty,
       sum(Amount) Amount,
       buyer,
       CLOSED_CODE,
       CANCEL_FLAG,
       creator
from(
select 
       poh.segment1 po_number,
       null RELEASE_NUM,
       null Release_date,
       plla.NEED_BY_DATE,
       poh.REVISION_NUM,
       poh.COMMENTS po_desc,
       poh.TYPE_LOOKUP_CODE po_type,
       poh.AUTHORIZATION_STATUS PO_status,
       poh.CREATION_DATE order_date,
       plla.PROMISED_DATE expected_delivery_date,
       prha.SEGMENT1 pr_number,
       prha.CREATION_DATE pr_date,
       nvl(prla.UNIT_PRICE,0)*nvl(prla.QUANTITY,0) pr_value,
       asp.VENDOR_NAME,
       asp.VENDOR_TYPE_LOOKUP_CODE Vendor_code,
       assa.VENDOR_SITE_CODE,
       poh.CURRENCY_CODE,
       nvl(pol.QUANTITY,0) outstanding_qty,
       pol.UNIT_PRICE*nvl(pol.QUANTITY,0) Amount,
       ppx.FULL_NAME buyer,
       poh.CLOSED_CODE,
       poh.CANCEL_FLAG,
       fu.USER_NAME creator
from po_headers_all poh,
     po_lines_all pol,
     po_line_locations_all plla,
     po_distributions_all pda,
     po_req_distributions_all prda,
     po_requisition_lines_all prla,
     po_requisition_headers_all prha,
     ap_suppliers asp,
     ap_supplier_sites_all assa,
     per_people_x ppx,
     fnd_user fu
where poh.po_header_id=pol.PO_HEADER_ID
and poh.PO_HEADER_ID=plla.PO_HEADER_ID
and pol.PO_LINE_ID=plla.PO_LINE_ID
and poh.po_header_id=pda.PO_HEADER_ID
and pol.PO_LINE_ID=pda.PO_LINE_ID
and plla.LINE_LOCATION_ID=pda.LINE_LOCATION_ID
and nvl(pda.REQ_DISTRIBUTION_ID,-12)=prda.DISTRIBUTION_ID(+)
and nvl(prda.REQUISITION_LINE_ID,-12)=prla.REQUISITION_LINE_ID(+)
and nvl(prla.REQUISITION_HEADER_ID,-12)=prha.REQUISITION_HEADER_ID(+)
and poh.TYPE_LOOKUP_CODE='STANDARD'
and poh.VENDOR_ID=asp.VENDOR_ID
and poh.VENDOR_SITE_ID=assa.VENDOR_SITE_ID
and poh.AGENT_ID=ppx.PERSON_ID
and poh.CREATED_BY=fu.USER_ID
--and poh.CLOSED_CODE='OPEN'
--AND POH.SEGMENT1='115009851'
and to_char(poh.CREATION_DATE,'dd/mm/yyyy') between '01/04/2015' and '31/03/2016'
union all
select 
       poh.segment1 po_number,
       PRA.RELEASE_NUM,
       PRA.Release_date,

       plla.NEED_BY_DATE,