XX Goods Receipt Note

SELECT RSH1.SHIPMENT_HEADER_ID
      ,to_number(RSH1.RECEIPT_NUM) RECEIPTNO
      ,TO_CHAR(RSH1.CREATION_DATE,'DD-MM-YYYY') RCTDATE
      ,TRUNC(RCT1.TRANSACTION_DATE) TRDT
      ,DECODE(RSH1.SHIPMENT_NUM||RSH1.SHIPPED_DATE,NULL,RSH1.COMMENTS,RSH1.SHIPMENT_NUM||','||RSH1.SHIPPED_DATE) DCNONDATE
      ,APS.VENDOR_NAME SUPPLIER
      ,APS.SEGMENT1 SUPPLIERCODE
      ,HOU.NAME OPUNIT
      ,HOU.ORGANIZATION_ID
      ,PAPF.FULL_NAME RECEIVEDBY
      ,RSL1.ITEM_DESCRIPTION ITMDESC
      ,MSIB.SEGMENT1 ITMCODE
      ,RCT1.ATTRIBUTE3 MANUFACTURER
      ,PHA.SEGMENT1 PONO
      ,PHA.CREATION_DATE PODATE
      ,RSL1.PO_LINE_ID
      ,POL.QUANTITY POQTY
      ,POL.UNIT_MEAS_LOOKUP_CODE POUOM
      ,RCT1.ATTRIBUTE1 INVORDCQTY
      ,RCT1.VENDOR_LOT_NUM MFRBATCHORLOT
      ,RCT1.TRANSACTION_ID
      ,RCT1.TRANSACTION_TYPE
      ,(RSL1.QUANTITY_RECEIVED||'-'||RCT1.UNIT_OF_MEASURE) RECEIVEDQTY
      ,RCT1.ATTRIBUTE2 NOOFPACKS
      ,RCT1.SHIPMENT_LINE_ID
FROM RCV_SHIPMENT_HEADERS RSH1
    ,AP_SUPPLIERS APS
    ,ORG_ORGANIZATION_DEFINITIONS OOD
    ,HR_OPERATING_UNITS HOU
    ,FND_USER FU
    ,PER_ALL_PEOPLE_F PAPF
    ,RCV_SHIPMENT_LINES RSL1
    ,MTL_SYSTEM_ITEMS_B MSIB
    ,PO_HEADERS_ALL PHA
    ,PO_LINES_ALL POL
    ,RCV_TRANSACTIONS RCT1
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(RSH1.CREATION_DATE) BETWEEN 
    NVL(:P_CREATION_DATE_FROM,TRUNC(RSH1.CREATION_DATE))
    AND NVL(:P_CREATION_DATE_TO,TRUNC(RSH1.CREATION_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.OPERATING_UNIT=HOU.ORGANIZATION_ID
AND HOU.ORGANIZATION_ID=DECODE(:P_RECEIPT_NUM_FROM||:P_SUPPLIER,NULL,FND_PROFILE.VALUE('ORG_ID'),:P_SUPPLIER,FND_PROFILE.VALUE('ORG_ID'),HOU.ORGANIZATION_ID)
AND RSH1.CREATED_BY=FU.USER_ID
AND FU.USER_NAME=PAPF.EMPLOYEE_NUMBER
AND TRUNC(SYSDATE) BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE
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.ORG_ID=DECODE(:P_RECEIPT_NUM_FROM||:P_SUPPLIER,NULL,FND_PROFILE.VALUE('ORG_ID'),PHA.ORG_ID)
AND RSL1.PO_LINE_ID=POL.PO_LINE_ID
AND RSL1.SHIPMENT_LINE_ID=RCT1.SHIPMENT_LINE_ID
AND RCT1.TRANSACTION_TYPE='RECEIVE'
ORDER BY 2,11,15 asc

===================================================
SELECT HAOU.ORGANIZATION_ID
              ,HL.ADDRESS_LINE_1
              ,HL.ADDRESS_LINE_2
              ,HL.ADDRESS_LINE_3
              ,HL.COUNTRY
              ,HL.POSTAL_CODE
              ,HL.TELEPHONE_NUMBER_1
              ,HL.TELEPHONE_NUMBER_2
              ,HL.TELEPHONE_NUMBER_3
              ,HL.LOC_INFORMATION14
              ,HL.LOC_INFORMATION15
              ,HL.LOC_INFORMATION16
FROM HR_ALL_ORGANIZATION_UNITS HAOU
           ,HR_LOCATIONS HL
WHERE HAOU.LOCATION_ID=HL.LOCATION_ID