PO Invoice Hold Details (SQL Script)

Supply Chain & Manufacturing (SCM) Purchase Order (PO)
1

This PO Invoice Hold Details query displays invoice hold details and related invoice, purchase order, and vendor information, helping users track hold reasons and payment statuses.

#sqlquery

SELECT
  -- SQL4Fusion (An Orbit Analytics Project)
  -- Generate invoice hold details with PO and supplier info.
  PSV.VENDOR_NAME                 SUPPLIER_NAME,
  PSV.SEGMENT1                    SUPPLIER_NUMBER,
  -- Buyer Info
  PU.USERNAME                     BUYER_NAME,
  -- Invoice & Hold Info
  AIA.INVOICE_NUM                 INVOICE_NUMBER,
  NVL(AIA.INVOICE_AMOUNT, 0)      INVOICE_AMOUNT,
  NVL(AIA.AMOUNT_PAID, 0)         INVOICE_AMOUNT_PAID,
  AHA.HOLD_REASON,
  AHA.RELEASE_REASON,
  AHA.HOLD_LOOKUP_CODE            INVOICE_HOLD_TYPE,
  AHA.RELEASE_LOOKUP_CODE         INVOICE_HOLD_RELEASE_TYPE,
  AIA.INVOICE_DATE,
  AIA.CREATION_DATE,
  AHA.HOLD_DATE,
  -- Payment Terms Info
  ATT.NAME                        PAYMENT_TERMS,         
  -- PO / Item Info
  PLA.ITEM_DESCRIPTION,
  PLA.ITEM_REVISION,
  PLA.LIST_PRICE_PER_UNIT,
  NVL(PLA.COMMITTED_AMOUNT, 0)    COMMITTED_AMOUNT,
  -- Shipment Info
  PLLA.SHIPMENT_NUM               SHIPMENT_NUMBER,
  PLLA.SHIPMENT_TYPE,
  PLLA.UNIT_OF_MEASURE_CLASS      UNIT_OF_MEASURE,      
  -- Quantity Info
  NVL(PLLA.QUANTITY, 0)           QUANTITY_ORDERED,
  NVL(PLLA.QUANTITY_RECEIVED, 0)  QUANTITY_RECEIVED,
  NVL(PLLA.QUANTITY_BILLED, 0)    QUANTITY_BILLED,
  NVL(PLLA.QUANTITY_REJECTED, 0)  QUANTITY_REJECTED,
  NVL(PLLA.QUANTITY_CANCELLED, 0) QUANTITY_CANCELLED,
  -- Terms Date
  AIA.TERMS_DATE,
  -- Goods Receipt Date
  AIA.GOODS_RECEIVED_DATE
FROM
  (
    SELECT
      AIA.INVOICE_ID,
      MAX(AHA.HOLD_ID) AS HOLD_ID  
    FROM
      AP_INVOICES_ALL AIA,
      AP_HOLDS_ALL AHA
    WHERE
      AIA.INVOICE_ID = AHA.INVOICE_ID
    GROUP BY
      AIA.INVOICE_ID
  ) LATEST_HOLDS,
  POZ_SUPPLIERS_V           PSV,
  PER_USERS                 PU,
  PER_ALL_PEOPLE_F          PAPF,
  AP_INVOICES_ALL           AIA,
  PO_LINES_ALL              PLA,
  AP_HOLDS_ALL              AHA,
  PO_LINE_LOCATIONS_ALL     PLLA,
  PO_HEADERS_ALL            PHA,
  GL_CODE_COMBINATIONS      GCC,
  AP_TERMS_TL               ATT
WHERE
  1 = 1
  AND AIA.INVOICE_ID         = LATEST_HOLDS.INVOICE_ID
  AND AHA.HOLD_ID            = LATEST_HOLDS.HOLD_ID   
  AND PAPF.PERSON_ID         = PU.PERSON_ID
  AND AIA.INVOICE_ID         = AHA.INVOICE_ID
  AND PLLA.LINE_LOCATION_ID  = AHA.LINE_LOCATION_ID
  AND PLA.PO_LINE_ID         = PLLA.PO_LINE_ID
  AND PHA.PO_HEADER_ID       = PLA.PO_HEADER_ID
  AND PSV.VENDOR_ID          = AIA.VENDOR_ID
  AND AIA.ACCTS_PAY_CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID(+)
  AND ATT.TERM_ID            = NVL(PLLA.TERMS_ID, PHA.TERMS_ID)  
  AND ATT.LANGUAGE           = USERENV('LANG')                   
  AND PAPF.PERSON_ID         = PHA.AGENT_ID
  AND (
    PHA.AGENT_ID IS NULL
    OR TO_CHAR(PAPF.EFFECTIVE_START_DATE, 'YYYYMMDDHH24MISS') ||
       TO_CHAR(PAPF.EFFECTIVE_END_DATE, 'YYYYMMDDHH24MISS') = (
         SELECT MAX(
           TO_CHAR(HRSUB.EFFECTIVE_START_DATE, 'YYYYMMDDHH24MISS') ||
           TO_CHAR(HRSUB.EFFECTIVE_END_DATE, 'YYYYMMDDHH24MISS')
         )
         FROM PER_ALL_PEOPLE_F HRSUB
         WHERE HRSUB.PERSON_ID = PHA.AGENT_ID
       ));