PO (Purchase Order) Data

 -------------------------------------Purchase Order Data---------------------------------------------------------------

SELECT

        DISTINCT

        PHA.PO_HEADER_ID

--       ,RT.SHIPMENT_HEADER_ID    

       ,PHA.SEGMENT1 PO_NO

--       ,PHA.RATE    CURRENCY_RATE

       ,HOU.NAME OPERATING_UNIT

       ,(SELECT HOUA.NAME FROM HR_ORGANIZATION_UNITS HOUA    WHERE HOUA.ORGANIZATION_ID = RSH.SHIP_TO_ORG_ID ) SHIP_ORG

       ,PHA.SEGMENT1 || ' - ' || PLA.LINE_NUM PO_LINE_NUMBER       

       ,PLA.QUANTITY PO_QUANTITY

       ,PLA.UNIT_PRICE PO_RATE

       ,PLA.QUANTITY * PLA.UNIT_PRICE PO_VALUE

       ,PHA.CREATION_DATE

       ,PLA.ATTRIBUTE1 BRAND

       ,PLA.ATTRIBUTE2 ORIGIN

       ,PV.VENDOR_NAME SUPPLIER_NAME

       ,PVS.VENDOR_SITE_CODE

       ,(SELECT FULL_NAME

                  FROM PER_ALL_PEOPLE_F

                 WHERE PERSON_ID = PHA.AGENT_ID

                   AND SYSDATE BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE)

        BUYER_NAME

       ,PLA.ITEM_DESCRIPTION ITEM_NAME                                                                        

       ,PHA.ORG_ID

       ,PHA.CURRENCY_CODE

       ,DECODE(PHA.CURRENCY_CODE, 'BDT', 1, PHA.RATE) CURRENCY_RATE

       ,PLL.QUANTITY

       ,PLL.QUANTITY_RECEIVED

       ,PLL.QUANTITY_ACCEPTED

       ,(PLL.QUANTITY - PLL.QUANTITY_RECEIVED) DUE

       ,PLL.QUANTITY_REJECTED

       ,PLL.QUANTITY_BILLED

       ,PLL.QUANTITY_CANCELLED

    --   ,RSH.RECEIPT_NUM MRR_NO

       ,LISTAGG(RSH.RECEIPT_NUM, ',') WITHIN GROUP (ORDER BY RSH.RECEIPT_NUM) "MRR_LIST"          

       ,LISTAGG(RSH.CREATION_DATE, ',') WITHIN GROUP (ORDER BY RSH.CREATION_DATE) "MRR_LIST"           

--       ,RSH.CREATION_DATE MRR_DATE

FROM 

        PO_HEADERS_ALL    PHA,

        PO_LINES_ALL      PLA,

        PO_VENDORS        PV,

        HR_OPERATING_UNITS HOU,

        PO_VENDOR_SITES_ALL PVS,

        PO_LINE_LOCATIONS_ALL   PLL,

        RCV_TRANSACTIONS      RT,

        RCV_SHIPMENT_HEADERS    RSH

WHERE 

    PHA.PO_HEADER_ID = PLA.PO_HEADER_ID

AND PHA.VENDOR_ID = PV.VENDOR_ID   

AND PHA.ORG_ID = HOU.ORGANIZATION_ID 

AND PHA.VENDOR_SITE_ID = PVS.VENDOR_SITE_ID

AND PHA.PO_HEADER_ID = PLL.PO_HEADER_ID

AND PLA.PO_LINE_ID = PLL.PO_LINE_ID

AND PHA.PO_HEADER_ID = RT.PO_HEADER_ID(+)

AND RT.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID(+)

--AND PHA.PO_HEADER_ID = 86686    

AND PHA.CREATION_DATE BETWEEN '30-apr-2016' AND '30-nov-2020' 

--AND RSH.RECEIPT_NUM = 1862

AND RT.TRANSACTION_TYPE(+) = 'RECEIVE'

AND RT.PO_LINE_ID(+) = PLA.PO_LINE_ID

--AND PVS.VENDOR_SITE_CODE = 'PARTY SUPPLY' 

--AND PHA.ORG_ID IN (85)

--AND PHA.SEGMENT1 = 17

GROUP BY

        PHA.PO_HEADER_ID

       ,PHA.SEGMENT1

       ,HOU.NAME

       ,PLA.LINE_NUM

       ,PLA.QUANTITY

       ,PLA.UNIT_PRICE

       ,PHA.CREATION_DATE

       ,PLA.ATTRIBUTE1 

       ,PLA.ATTRIBUTE2 

       ,PV.VENDOR_NAME 

       ,PVS.VENDOR_SITE_CODE

       ,PLA.ITEM_DESCRIPTION                                                                  

       ,PHA.ORG_ID

       ,PLL.QUANTITY

       ,PLL.QUANTITY_RECEIVED

       ,PLL.QUANTITY_ACCEPTED

       ,PLL.QUANTITY_REJECTED

       ,PLL.QUANTITY_BILLED

       ,PLL.QUANTITY_CANCELLED

       ,RSH.SHIP_TO_ORG_ID 

       ,PHA.AGENT_ID  

       ,PHA.CURRENCY_CODE

       ,PHA.RATE

--    ,RSH.CREATION_DATE    

--       ,RSH.RECEIPT_NUM              

ORDER BY  PO_LINE_NUMBER

--ORDER BY PHA.ORG_ID, PLL.LINE_LOCATION_ID

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