XX Material Receipts Register

SELECT RSH1.SHIPMENT_HEADER_ID
      ,to_number(RSH1.RECEIPT_NUM) RECEIPTNO
      ,TO_CHAR(RSH1.CREATION_DATE,'DD-MM-YYYY') CRTDATE
      ,RCT1.TRANSACTION_DATE
      ,RSH1.SHIPMENT_NUM DCNO
      ,RSH1.SHIPPED_DATE DCDATE
      ,APS.VENDOR_NAME SUPPLIER
      ,HOU.NAME OPUNIT
      ,RSL1.ITEM_DESCRIPTION ITMDESC
      ,MSIB.SEGMENT1 ITMCODE
      ,RCT1.ATTRIBUTE3 MANUFACTURER
      ,RSL1.VENDOR_ITEM_NUM SUPITM
      ,PHA.SEGMENT1 PONO
      ,PHA.CREATION_DATE PODATE
      ,PHA.CURRENCY_CODE POCURRENCY
      ,RSL1.PO_LINE_ID
      ,RSL1.ITEM_ID
      ,POL.LINE_NUM POLNUM
      ,POL.QUANTITY POQTY
      ,POL.UNIT_MEAS_LOOKUP_CODE POUOM
      ,POL.ATTRIBUTE1 PROJECT
      --,RCT1.ATTRIBUTE1 INVORDCQTY
      ,RCT1.VENDOR_LOT_NUM MFRBATCHORLOT
      ,RCT1.TRANSACTION_ID
      --,RCT1.TRANSACTION_TYPE
      ,RSL1.QUANTITY_RECEIVED RECEIVEDQTY
      ,RSL1.UNIT_OF_MEASURE RCVUOM
      ,RCT1.QUANTITY RTQTY
      ,RCT1.UNIT_OF_MEASURE RTUOM
      ,RCT1.ATTRIBUTE2 NOOFPACKS
      ,RCT1.PO_UNIT_PRICE
      ,RCT1.SHIPMENT_LINE_ID
      ,PAPF.FULL_NAME BUYER
      ,JRL.EXCISE_INVOICE_NO
      ,JRL.EXCISE_INVOICE_DATE
      ,NVL(RCT1.CURRENCY_CONVERSION_RATE,1) CURRENCY_CONVERSION_RATE
FROM RCV_SHIPMENT_HEADERS RSH1
    ,AP_SUPPLIERS APS
    ,ORG_ORGANIZATION_DEFINITIONS OOD
    ,HR_OPERATING_UNITS HOU
    ,RCV_SHIPMENT_LINES RSL1
    ,MTL_SYSTEM_ITEMS_B MSIB
    ,PO_HEADERS_ALL PHA
    ,PER_ALL_PEOPLE_F PAPF
    ,PO_LINES_ALL POL
    ,RCV_TRANSACTIONS RCT1
    ,JAI_RCV_LINES JRL
WHERE RSH1.RECEIPT_SOURCE_CODE='VENDOR'
AND to_number(RSH1.RECEIPT_NUM) BETWEEN to_number(NVL(:P_RECEIPT_NUM_FROM,RSH1.RECEIPT_NUM) )
                    AND to_number(NVL(:P_RECEIPT_NUM_TO,RSH1.RECEIPT_NUM))
AND TRUNC(RCT1.TRANSACTION_DATE) BETWEEN 
    NVL(:P_CREATION_DATE_FROM,TRUNC(RCT1.TRANSACTION_DATE))
    AND NVL(:P_CREATION_DATE_TO,TRUNC(RCT1.TRANSACTION_DATE))
AND RSH1.VENDOR_ID=APS.VENDOR_ID
AND APS.VENDOR_NAME=NVL(:P_SUPPLIER,APS.VENDOR_NAME)
AND RSH1.SHIP_TO_ORG_ID=OOD.ORGANIZATION_ID
AND OOD.ORGANIZATION_CODE=NVL(:P_ORG,OOD.ORGANIZATION_CODE)
AND OOD.OPERATING_UNIT=HOU.ORGANIZATION_ID
&LP_WHERE
AND RSH1.SHIPMENT_HEADER_ID=RSL1.SHIPMENT_HEADER_ID
AND RSL1.ITEM_ID=MSIB.INVENTORY_ITEM_ID(+)
AND RSL1.TO_ORGANIZATION_ID=MSIB.ORGANIZATION_ID(+)
AND RSL1.PO_HEADER_ID=PHA.PO_HEADER_ID
AND PHA.SEGMENT1=NVL(:P_PO_NUM,PHA.SEGMENT1)
AND RSL1.PO_LINE_ID=POL.PO_LINE_ID
AND RSL1.SHIPMENT_LINE_ID=RCT1.SHIPMENT_LINE_ID
AND RCT1.TRANSACTION_TYPE='RECEIVE'
AND PHA.AGENT_ID=PAPF.PERSON_ID
AND RSL1.SHIPMENT_LINE_ID=JRL.SHIPMENT_LINE_ID(+)
ORDER BY 2,10,16 asc