Thursday 20 August 2015

PO to Invoice detail query in oracle apps

/* Formatted on 8/20/2015 11:38:11 AM (QP5 v5.240.12305.39446) */
SELECT A.ORG_ID "ORG ID",
       E.VENDOR_NAME "VENDOR NAME",
       UPPER (E.VENDOR_TYPE_LOOKUP_CODE) "VENDOR TYPE",
       F.VENDOR_SITE_CODE "VENDOR SITE",
       F.ADDRESS_LINE1 "ADDRESS",
       F.CITY "CITY",
       F.COUNTRY "COUNTRY",
       TO_CHAR (TRUNC (D.CREATION_DATE)) "PO DATE",
       D.SEGMENT1 "PO NUMBER",
       D.TYPE_LOOKUP_CODE "PO TYPE",
       C.QUANTITY_ORDERED "QTY ORDERED",
       C.QUANTITY_CANCELLED "QTY CANCALLED",
       G.ITEM_DESCRIPTION "ITEM DESCRIPTION",
       G.UNIT_PRICE "UNIT PRICE",
         (NVL (C.QUANTITY_ORDERED, 0) - NVL (C.QUANTITY_CANCELLED, 0))
       * NVL (G.UNIT_PRICE, 0)
          "PO Line Amount",
       (SELECT DECODE (PH.APPROVED_FLAG, 'Y', 'Approved')
          FROM PO.PO_HEADERS_ALL PH
         WHERE PH.PO_HEADER_ID = D.PO_HEADER_ID)
          "PO STATUS",
       A.INVOICE_TYPE_LOOKUP_CODE "INVOICE TYPE",
       A.INVOICE_AMOUNT "INVOICE AMOUNT",
       TO_CHAR (TRUNC (A.INVOICE_DATE)) "INVOICE DATE",
       A.INVOICE_NUM "INVOICE NUMBER",
       (SELECT DECODE (X.MATCH_STATUS_FLAG, 'A', 'Approved')
          FROM AP.AP_INVOICE_DISTRIBUTIONS_ALL X
         WHERE X.INVOICE_DISTRIBUTION_ID = B.INVOICE_DISTRIBUTION_ID)
          "Invoice Approved?",
       A.AMOUNT_PAID,
       H.AMOUNT,
       I.CHECK_NUMBER "CHEQUE NUMBER",
       TO_CHAR (TRUNC (I.CHECK_DATE)) "PAYMENT DATE"
  FROM AP.AP_INVOICES_ALL A,
       AP.AP_INVOICE_DISTRIBUTIONS_ALL B,
       PO.PO_DISTRIBUTIONS_ALL C,
       PO.PO_HEADERS_ALL D,
       ap_suppliers E,
       ap_supplier_sites_all F,
       PO.PO_LINES_ALL G,
       AP.AP_INVOICE_PAYMENTS_ALL H,
       AP.AP_CHECKS_ALL I
 WHERE     A.INVOICE_ID = B.INVOICE_ID
       AND B.PO_DISTRIBUTION_ID = C.PO_DISTRIBUTION_ID(+)
       AND C.PO_HEADER_ID = D.PO_HEADER_ID(+)
       AND E.VENDOR_ID(+) = D.VENDOR_ID
       AND F.VENDOR_SITE_ID(+) = D.VENDOR_SITE_ID
       AND D.PO_HEADER_ID = G.PO_HEADER_ID
       AND C.PO_LINE_ID = G.PO_LINE_ID
       AND A.INVOICE_ID = H.INVOICE_ID
       AND H.CHECK_ID = I.CHECK_ID
       AND F.VENDOR_SITE_ID = I.VENDOR_SITE_ID
       AND C.PO_HEADER_ID IS NOT NULL
       AND A.PAYMENT_STATUS_FLAG = 'Y'
       AND D.TYPE_LOOKUP_CODE != 'BLANKET'
       AND D.SEGMENT1 = 'PO_Number'

No comments:

Post a Comment