PO receipts query in oracle apps

PO receipts query in oracle apps

In this post , I am sharing the sql script to get the PO and receipt query in oracle apps.  This sql script , will help to get the PO receipt query in oracle apps.

PO receipts query in oracle apps

SELECT

         PO_HEADERS_ALL.SEGMENT1 PO_NUMBER,

         PV.VENDOR_NAME,

         APS.VENDOR_SITE_CODE,

         RCV_SHIPMENT_LINES.ITEM_ID,

         PO_LINES_ALL.UNIT_PRICE,

         PO_LINES_ALL.LINE_NUM,

         RCV_TRANSACTIONS.ORGANIZATION_ID,

         PO_LINE_LOCATIONS_ALL.SHIPMENT_NUM,

         PO_LINE_LOCATIONS_ALL.ORG_ID,

         RCV_TRANSACTIONS.DELIVER_TO_PERSON_ID,

         RCV_TRANSACTIONS.INV_TRANSACTION_ID,

         RCV_TRANSACTIONS.TRANSACTION_TYPE,

         NVL (RCV_TRANSACTIONS.LOCATION_ID,

              PO_LINE_LOCATIONS_ALL.SHIP_TO_LOCATION_ID)

            LOCATION_ID,

         RCV_TRANSACTIONS.ORGANIZATION_ID,

         RCV_TRANSACTIONS.CREATED_BY,

         RCV_TRANSACTIONS.LAST_UPDATED_BY,

         DECODE (RCV_TRANSACTIONS.TRANSACTION_TYPE,

                 'MATCH', PARENT.TRANSACTION_DATE,

                 RCV_TRANSACTIONS.TRANSACTION_DATE)

            TRANSACTION_DATE,

         RCV_TRANSACTIONS.CREATION_DATE,

         RCV_TRANSACTIONS.LAST_UPDATE_DATE,

         RCV_TRANSACTIONS.QUANTITY,

         RCV_TRANSACTIONS.UNIT_OF_MEASURE,

         RCV_TRANSACTIONS.SOURCE_DOC_UNIT_OF_MEASURE,

         CASE

            WHEN RCV_TRANSACTIONS.TRANSACTION_TYPE = 'RECEIVE'

            THEN

               RCV_SHIPMENT_LINES.QUANTITY_RECEIVED

            WHEN RCV_TRANSACTIONS.TRANSACTION_TYPE = 'MATCH'

            THEN

               RCV_SHIPMENT_LINES.QUANTITY_RECEIVED

            ELSE

               RCV_TRANSACTIONS.SOURCE_DOC_QUANTITY

         END

            SOURCE_DOC_QUANTITY,

         RCV_TRANSACTIONS.PO_UNIT_PRICE,

         RCV_TRANSACTIONS.CURRENCY_CODE,

         RCV_TRANSACTIONS.INSPECTION_STATUS_CODE,

         RCV_TRANSACTIONS.COMMENTS,

         RCV_TRANSACTIONS.REASON_ID,

         RCV_SHIPMENT_HEADERS.RECEIPT_NUM,

         RCV_SHIPMENT_LINES.LINE_NUM,

         RCV_TRANSACTIONS.CURRENCY_CONVERSION_DATE,

         PO_HEADERS_ALL.AGENT_ID,

         CONTRACT.SEGMENT1,

         RCV_TRANSACTIONS.PO_LINE_LOCATION_ID,

         RCV_TRANSACTIONS.EMPLOYEE_ID,

         PO_LINES_ALL.LAST_UPDATE_DATE,

         PO_LINE_LOCATIONS_ALL.LAST_UPDATE_DATE,

         RCV_SHIPMENT_HEADERS.LAST_UPDATE_DATE,

         PO_HEADERS_ALL.LAST_UPDATE_DATE,

         PO_LINES_ALL.LINE_TYPE_ID,

         RCV_SHIPMENT_LINES.CATEGORY_ID,

         RCV_TRANSACTIONS.AMOUNT,

         PO_LINE_LOCATIONS_ALL.PAYMENT_TYPE,

         PO_LINE_LOCATIONS_ALL.SHIPMENT_TYPE,

         CASE

            WHEN PO_HEADERS_ALL.TYPE_LOOKUP_CODE = 'BLANKET'

            THEN

               PO_HEADERS_ALL.SEGMENT1

            WHEN BLANKET_AGREEMENT.TYPE_LOOKUP_CODE = 'BLANKET'

            THEN

               BLANKET_AGREEMENT.SEGMENT1

            ELSE

               NULL

         END,

         PO_LINE_LOCATIONS_ALL.CONSIGNED_FLAG,

         '0' AS X_CUSTOM,

         NVL (PARENT.TRANSACTION_TYPE, 0) PARENT_TRANSACTION_TYPE,

         PO_LINE_LOCATIONS_ALL.DAYS_EARLY_RECEIPT_ALLOWED

            DAYS_EARLY_RECEIPT_ALLOWED,

         PO_LINE_LOCATIONS_ALL.DAYS_LATE_RECEIPT_ALLOWED

            DAYS_LATE_RECEIPT_ALLOWED,

         PO_LINE_LOCATIONS_ALL.NEED_BY_DATE,

         PO_LINE_LOCATIONS_ALL.PROMISED_DATE,

         PO_REQUISITION_LINES_ALL.NEED_BY_DATE REQUISITION_NEED_BY_DATE,

         RCV_TRANSACTIONS.SOURCE_DOCUMENT_CODE,

         PO_RELEASES_ALL.RELEASE_NUM,

         RCV_SHIPMENT_LINES.UNIT_OF_MEASURE RCV_UOM

  FROM   PO_HEADERS_ALL,

         PO_LINES_ALL,

         PO_RELEASES_ALL,

         PO_LINE_LOCATIONS_ALL,

         RCV_TRANSACTIONS,

         RCV_SHIPMENT_LINES,

         RCV_SHIPMENT_HEADERS,

         PO_HEADERS_ALL CONTRACT,

         PO_HEADERS_ALL BLANKET_AGREEMENT,

         PO_REQUISITION_LINES_ALL,

         RCV_TRANSACTIONS PARENT,

         PO_VENDORS PV,

         AP_SUPPLIER_SITES_ALL APS

 WHERE   RCV_TRANSACTIONS.PO_LINE_LOCATION_ID =

               PO_LINE_LOCATIONS_ALL.LINE_LOCATION_ID(+)

         AND PO_LINE_LOCATIONS_ALL.PO_RELEASE_ID =

               PO_RELEASES_ALL.PO_RELEASE_ID(+)

         AND RCV_TRANSACTIONS.PO_HEADER_ID = PO_HEADERS_ALL.PO_HEADER_ID(+)

         AND RCV_TRANSACTIONS.VENDOR_ID=PV.VENDOR_ID

         AND RCV_TRANSACTIONS.VENDOR_SITE_ID=APS.VENDOR_SITE_ID

         AND RCV_TRANSACTIONS.PO_LINE_ID = PO_LINES_ALL.PO_LINE_ID(+)

              AND RCV_TRANSACTIONS.TRANSACTION_TYPE IN

                  ('RECEIVE', 'RETURN TO VENDOR', 'CORRECT', 'MATCH')

         AND RCV_TRANSACTIONS.SHIPMENT_HEADER_ID =

               RCV_SHIPMENT_HEADERS.SHIPMENT_HEADER_ID

         AND RCV_TRANSACTIONS.SHIPMENT_HEADER_ID =

               RCV_SHIPMENT_LINES.SHIPMENT_HEADER_ID

         AND RCV_TRANSACTIONS.SHIPMENT_LINE_ID =

               RCV_SHIPMENT_LINES.SHIPMENT_LINE_ID

         AND PO_LINES_ALL.CONTRACT_ID = CONTRACT.PO_HEADER_ID(+)

         AND PO_LINES_ALL.FROM_HEADER_ID = BLANKET_AGREEMENT.PO_HEADER_ID(+)

         AND RCV_TRANSACTIONS.REQUISITION_LINE_ID =

               PO_REQUISITION_LINES_ALL.REQUISITION_LINE_ID(+)

         AND RCV_TRANSACTIONS.PARENT_TRANSACTION_ID =

               PARENT.TRANSACTION_ID(+)

         AND NOT (RCV_SHIPMENT_LINES.PO_HEADER_ID IS NULL

                  AND RCV_SHIPMENT_LINES.SOURCE_DOCUMENT_CODE = 'PO')