Friday, 14 November 2014

AP Hold Detail Process

/* Formatted on 11/14/2014 4:44:17 PM (QP5 v5.115.810.9015) */
SELECT   API.INVOICE_ID,
         API.INVOICE_NUM,
         APS.VENDOR_NAME AS SUPPLIER,
         APSS.VENDOR_SITE_CODE AS SUPPLIER_SITE,
         API.INVOICE_AMOUNT,
         API.INVOICE_DATE,
         API.GL_DATE,
         APH.HOLD_DATE,
         APH.HELD_BY,
         APH.HOLD_REASON,
         APH.RELEASE_REASON
  FROM   AP_HOLDS_ALL APH,
         AP_INVOICES_ALL API,
         AP_SUPPLIERS APS,
         AP_SUPPLIER_SITES_ALL APSS
 WHERE       API.VENDOR_ID = APS.VENDOR_ID
         AND API.VENDOR_SITE_ID = APSS.VENDOR_SITE_ID
         AND API.INVOICE_ID = APH.INVOICE_ID(+)
         AND APH.RELEASE_LOOKUP_CODE IS NULL
         AND API.INVOICE_ID = :P1
UNION
SELECT   API.INVOICE_ID,
         API.INVOICE_NUM,
         APS.VENDOR_NAME AS SUPPLIER,
         APSS.VENDOR_SITE_CODE AS SUPPLIER_SITE,
         API.INVOICE_AMOUNT,
         API.INVOICE_DATE,
         API.GL_DATE,
         APH.HOLD_DATE,
         APH.HELD_BY,
         APH.HOLD_REASON,
         APH.RELEASE_REASON
  FROM   AP_HOLDS_ALL APH,
         AP_INVOICES_ALL API,
         AP_SUPPLIERS APS,
         AP_SUPPLIER_SITES_ALL APSS
 WHERE       API.VENDOR_ID = APS.VENDOR_ID
         AND API.VENDOR_SITE_ID = APSS.VENDOR_SITE_ID
         AND APH.HOLD_ID = (SELECT   MAX (APH.HOLD_ID)
                              FROM   AP_HOLDS_ALL APH
                             WHERE   API.INVOICE_ID = APH.INVOICE_ID)
         AND API.INVOICE_ID = APH.INVOICE_ID(+)
         AND API.INVOICE_ID = :P1

1 comment:

  1. Regards
    Sridevi Koduru (Senior Oracle Apps Trainer Oracleappstechnical.com)
    LinkedIn profile - https://in.linkedin.com/in/sridevi-koduru-9b876a8b
    Please Contact for One to One Online Training on Oracle Apps Technical, Financials, SCM, Oracle Manufacturing, BI Publisher, OAF, ADF, SQL, PL/SQL, D2K at sridevikoduru@oracleappstechnical.com | +91 - 9581017828.

    ReplyDelete