Introduction:

This document outlines how to reconcile Oracle General Ledger (GL) and Oracle Payables (AP) for Purchase Invoice analysis by using a specific SQL script to drill down to the line level transaction details. It is designed for Oracle General Ledger and Oracle Payables version 12.1.3 and later, and it helps in matching AP Invoice details to Purchase Orders and Receipts, providing a comprehensive reconciliation of GL account balances.

Key Steps to Use the Script:

  1. Parameters:
    • Ledger ID: The identifier for the ledger.
    • From Date: The start date for the period.
    • To Date: The end date for the period.
  2. SQL Query Breakdown:
    • The script fetches essential details from multiple AP and GL tables such as GL_JE_HEADERS, GL_JE_LINES, GL_CODE_COMBINATIONS, XLA_TRANSACTION_ENTITIES, and others.
    • GL Code Combination: Helps to match the transactions to the GL accounts and provides the corresponding GL account.
    • Entity Code: Identifies the type of transaction (e.g., AP_INVOICES, AP_PAYMENTS, AP_PREPAY).
    • Invoice Details: The script retrieves detailed invoice information like INVOICE_NUM, INVOICE_DATE, PARTY_CODE, and PARTY_NAME based on the corresponding entity type (Invoice, Payment, Prepayment).
    • PO Details: For invoice distribution related to a Purchase Order (PO), it will fetch the PO_Number linked to the invoice line.
  3. Important Joins:
    • The query joins the GL tables with AP tables (e.g., AP_INVOICE_DISTRIBUTIONS_ALL, AP_INVOICES_ALL) to link accounting entries with invoice details.
    • Specific joins are used to match invoices to their corresponding PO lines and receipts, ensuring that the reconciliation is accurate.
  4. Script: Run the Script with the parameters Ledger id, From date and To date as parameter
    SELECT NULL ENTITY
    ,GL.NAME LEDGER_NAME
    ,GJH.JE_SOURCE
    ,GJH.JE_CATEGORY
    ,GL.CURRENCY_CODE CURRENCY
    ,GCC.segment3 ACCOUNT
    , (SELECT FFVL.DESCRIPTION
    FROM APPS.FND_FLEX_VALUES_VL FFVL,
    APPS.FND_FLEX_VALUE_SETS FFVS
    WHERE FFVL.FLEX_VALUE =GCC.SEGMENT3
    AND FFVL.FLEX_VALUE_SET_ID =FFVS.FLEX_VALUE_SET_ID
    AND FFVS.FLEX_VALUE_SET_NAME = ‘TCL_Account’
    ) “ACCOUNT_DESC”
    ,(SELECT DECODE(SUBSTR(compiled_value_attributes,5,1),’A’,’Asset’,
    ‘E’,’Expense’,’R’,’Revenue’,’O’,’Others’,’L’,’Liability’,’None’)
    FROM fnd_flex_value_sets a
    ,fnd_flex_values b
    ,fnd_flex_values_tl c
    WHERE flex_value_set_name=’TCL_Account’
    AND a.flex_value_set_id=b.flex_value_set_id
    AND b.flex_value_id = c.flex_value_id
    AND b.flex_value = gcc.segment3
    AND b.enabled_flag=’Y’
    AND rownum=1) EXPENSE_TYPE
    ,gjh.period_name
    ,GJH.POSTED_DATE POSTED_DATE
    ,DECODE (XTE.ENTITY_CODE,’AP_INVOICES’,
    (SELECT INVOICE_NUM
    FROM AP.AP_INVOICE_DISTRIBUTIONS_ALL AIDA,
    APPS.AP_INVOICES_ALL AIA
    WHERE AIDA.INVOICE_ID =AIA.INVOICE_ID
    AND AIDA.INVOICE_ID =XTE.SOURCE_ID_INT_1
    AND ROWNUM =1
    ),’AP_PAYMENTS’,
    (SELECT AIA.INVOICE_NUM
    FROM AP.AP_PAYMENT_HIST_DISTS APHD,
    APPS.AP_INVOICE_PAYMENTS_ALL AIPA,
    APPS.AP_INVOICES_ALL AIA
    WHERE APHD.INVOICE_PAYMENT_ID =AIPA.INVOICE_PAYMENT_ID
    AND AIPA.INVOICE_ID =AIA.INVOICE_ID
    AND APHD.PAYMENT_HIST_DIST_ID =XTE.SOURCE_ID_INT_1
    AND ROWNUM =1
    ),’AP_PREPAY’,
    (SELECT INVOICE_NUM
    FROM APPS.AP_INVOICES_ALL
    WHERE INVOICE_ID=XTE.SOURCE_ID_INT_1
    )) “INVOICE_NUM”
    ,DECODE (XTE.ENTITY_CODE,’AP_INVOICES’,
    (SELECT AIA.INVOICE_DATE
    FROM AP.AP_INVOICE_DISTRIBUTIONS_ALL AIDA,
    APPS.AP_INVOICES_ALL AIA
    WHERE AIDA.INVOICE_ID =AIA.INVOICE_ID
    AND AIDA.INVOICE_ID =XTE.SOURCE_ID_INT_1
    AND ROWNUM =1
    ),’AP_PAYMENTS’,
    (SELECT AIA.INVOICE_DATE
    FROM AP.AP_PAYMENT_HIST_DISTS APHD,
    APPS.AP_INVOICE_PAYMENTS_ALL AIPA,
    APPS.AP_INVOICES_ALL AIA
    WHERE APHD.INVOICE_PAYMENT_ID =AIPA.INVOICE_PAYMENT_ID
    AND AIPA.INVOICE_ID =AIA.INVOICE_ID
    AND APHD.PAYMENT_HIST_DIST_ID =XTE.SOURCE_ID_INT_1
    AND ROWNUM =1
    ),’AP_PREPAY’,
    (SELECT INVOICE_DATE
    FROM APPS.AP_INVOICES_ALL
    WHERE INVOICE_ID=XTE.SOURCE_ID_INT_1
    )) “INVOICE_DATE”
    ,
    DECODE (XTE.ENTITY_CODE,’AP_INVOICES’,
    (SELECT PV.SEGMENT1
    FROM APPS.ap_suppliers PV,
    AP.AP_INVOICE_DISTRIBUTIONS_ALL AIDA,
    APPS.AP_INVOICES_ALL AIA
    WHERE AIDA.INVOICE_ID = XTE.SOURCE_ID_INT_1
    AND AIDA.INVOICE_ID =AIA.INVOICE_ID
    AND AIA.VENDOR_ID =PV.VENDOR_ID
    AND ROWNUM =1
    ) ,’AP_PAYMENTS’,
    (SELECT PV.SEGMENT1
    FROM APPS.ap_suppliers PV,
    APPS.AP_INVOICES_ALL AIA,
    AP.AP_PAYMENT_HIST_DISTS APHD ,
    APPS.AP_INVOICE_PAYMENTS_ALL AIPA
    WHERE APHD.PAYMENT_HIST_DIST_ID = XTE.SOURCE_ID_INT_1
    AND APHD.INVOICE_PAYMENT_ID = AIPA.INVOICE_PAYMENT_ID
    AND AIPA.INVOICE_ID =AIA.INVOICE_ID
    –AND aida.invoice_id =aia.invoice_id
    AND AIA.VENDOR_ID=PV.VENDOR_ID
    AND ROWNUM =1
    ) ,’AP_PREPAY’,
    (SELECT PV.SEGMENT1
    FROM APPS.ap_suppliers PV,
    APPS.AP_INVOICES_ALL AIA
    WHERE 1 =1
    AND AIA.INVOICE_ID = XTE.SOURCE_ID_INT_1
    AND AIA.VENDOR_ID=PV.VENDOR_ID
    AND ROWNUM =1
    ) ) “PARTY_CODE”
    ,
    DECODE (XTE.ENTITY_CODE,’AP_INVOICES’, NVL(
    (SELECT PV.VENDOR_NAME
    FROM APPS.ap_suppliers PV,
    AP.AP_INVOICE_DISTRIBUTIONS_ALL AIDA,
    APPS.AP_INVOICES_ALL AIA
    WHERE AIDA.INVOICE_ID = XTE.SOURCE_ID_INT_1
    AND AIDA.INVOICE_ID =AIA.INVOICE_ID
    AND AIA.VENDOR_ID =PV.VENDOR_ID
    AND ROWNUM =1
    ),
    (SELECT HP.PARTY_NAME
    FROM AP.AP_INVOICE_DISTRIBUTIONS_ALL AIDA,
    APPS.AP_INVOICES_ALL AIA,
    APPS.HZ_PARTIES HP,
    APPS.HZ_PARTY_SITES HPS,
    –XLA.XLA_DISTRIBUTION_LINKS XDL1,
    APPS.AP_INVOICE_LINES_ALL APL
    WHERE 1=1
    –and XDL1.SOURCE_DISTRIBUTION_TYPE = ‘AP_INV_DIST’
    AND AIDA.INVOICE_ID = SOURCE_ID_INT_1
    AND AIDA.INVOICE_ID =AIA.INVOICE_ID
    AND AIA.INVOICE_ID =APL.INVOICE_ID
    AND APL.INVOICE_ID =AIDA.INVOICE_ID
    AND AIA.PARTY_ID =HP.PARTY_ID
    AND AIA.PARTY_SITE_ID =HPS.PARTY_SITE_ID(+)
    –AND XDL1.SOURCE_DISTRIBUTION_ID_NUM_1=XDL.SOURCE_DISTRIBUTION_ID_NUM_1
    AND ROWNUM =1
    )),’AP_PAYMENTS’, NVL(
    (SELECT PV.VENDOR_NAME
    FROM APPS.ap_suppliers PV,
    APPS.AP_INVOICES_ALL AIA,
    AP.AP_PAYMENT_HIST_DISTS APHD ,
    APPS.AP_INVOICE_PAYMENTS_ALL AIPA
    WHERE APHD.PAYMENT_HIST_DIST_ID = XTE.SOURCE_ID_INT_1
    AND APHD.INVOICE_PAYMENT_ID = AIPA.INVOICE_PAYMENT_ID
    AND AIPA.INVOICE_ID =AIA.INVOICE_ID
    –AND aida.invoice_id =aia.invoice_id
    AND AIA.VENDOR_ID=PV.VENDOR_ID
    AND ROWNUM =1
    ),
    (SELECT HP.PARTY_NAME
    FROM AP.AP_PAYMENT_HIST_DISTS APHD,
    APPS.AP_INVOICE_PAYMENTS_ALL AIPA,
    APPS.AP_INVOICES_ALL AIA,
    APPS.HZ_PARTIES HP
    WHERE APHD.INVOICE_PAYMENT_ID =AIPA.INVOICE_PAYMENT_ID
    AND AIPA.INVOICE_ID =AIA.INVOICE_ID
    AND AIA.PARTY_ID =HP.PARTY_ID
    AND APHD.PAYMENT_HIST_DIST_ID =XTE.SOURCE_ID_INT_1
    AND ROWNUM =1
    )),’AP_PREPAY’, NVL(
    (SELECT PV.VENDOR_NAME
    FROM APPS.ap_suppliers PV,
    APPS.AP_INVOICES_ALL AIA
    WHERE 1 =1
    AND AIA.INVOICE_ID = XTE.SOURCE_ID_INT_1
    AND AIA.VENDOR_ID=PV.VENDOR_ID
    AND ROWNUM =1
    ),
    (SELECT HP.PARTY_NAME
    FROM AP.AP_INVOICE_DISTRIBUTIONS_ALL AIDA,
    APPS.AP_INVOICES_ALL AIA,
    APPS.HZ_PARTIES HP,
    APPS.HZ_PARTY_SITES HPS,
    APPS.AP_INVOICE_LINES_ALL APL
    WHERE 1 =1
    AND AIDA.INVOICE_ID = SOURCE_ID_INT_1
    AND AIDA.INVOICE_ID =AIA.INVOICE_ID
    AND AIA.INVOICE_ID =APL.INVOICE_ID
    AND APL.INVOICE_ID =AIDA.INVOICE_ID
    AND AIA.PARTY_ID =HP.PARTY_ID
    AND AIA.PARTY_SITE_ID =HPS.PARTY_SITE_ID(+)
    AND ROWNUM =1
    )) ) “PARTY_NAME”
    ,XAH.DOC_SEQUENCE_VALUE “Voucher_num”
    ,DECODE (XTE.ENTITY_CODE,’AP_INVOICES’,
    (SELECT AIA.DESCRIPTION
    FROM AP.AP_INVOICE_DISTRIBUTIONS_ALL AIDA,
    APPS.AP_INVOICES_ALL AIA
    WHERE AIDA.INVOICE_ID =AIA.INVOICE_ID
    AND AIDA.INVOICE_ID =XTE.SOURCE_ID_INT_1
    AND ROWNUM =1
    ),’AP_PAYMENTS’,
    (SELECT AIA.DESCRIPTION
    FROM AP.AP_PAYMENT_HIST_DISTS APHD,
    APPS.AP_INVOICE_PAYMENTS_ALL AIPA,
    APPS.AP_INVOICES_ALL AIA
    WHERE APHD.INVOICE_PAYMENT_ID =AIPA.INVOICE_PAYMENT_ID
    AND AIPA.INVOICE_ID =AIA.INVOICE_ID
    AND APHD.PAYMENT_HIST_DIST_ID =XTE.SOURCE_ID_INT_1
    AND ROWNUM =1
    ),’AP_PREPAY’,
    (SELECT DESCRIPTION
    FROM APPS.AP_INVOICES_ALL
    WHERE INVOICE_ID=XTE.SOURCE_ID_INT_1
    )) “INVOICE_HEADER_DESCRIPTION”
    ,
    DECODE (XTE.ENTITY_CODE,’AP_INVOICES’,
    (SELECT PHA.SEGMENT1
    FROM AP.AP_INVOICE_DISTRIBUTIONS_ALL AIDA,
    PO.PO_DISTRIBUTIONS_ALL PDA,
    PO.PO_HEADERS_ALL PHA
    WHERE AIDA.PO_DISTRIBUTION_ID = PDA.PO_DISTRIBUTION_ID
    AND PDA.PO_HEADER_ID = PHA.PO_HEADER_ID
    AND AIDA.INVOICE_ID = XTE.SOURCE_ID_INT_1
    AND ROWNUM =1
    )) PO_Number
    ,NULL ACCOUNT_TYPE
    ,NULL RECEIPT_NUMBER
    ,NULL RECEIPT_DATE
    ,
    DECODE (XTE.ENTITY_CODE,’AP_INVOICES’,
    (SELECT FU.USER_NAME
    FROM AP.AP_INVOICE_DISTRIBUTIONS_ALL AIDA,
    APPS.AP_INVOICES_ALL AIA,
    APPS.FND_USER FU
    WHERE AIDA.INVOICE_ID =AIA.INVOICE_ID
    AND AIA.CREATED_BY =FU.USER_ID
    AND AIDA.INVOICE_ID =XTE.SOURCE_ID_INT_1
    AND ROWNUM =1
    ),’AP_PAYMENTS’,
    (SELECT FU.USER_NAME
    FROM AP.AP_PAYMENT_HIST_DISTS APHD,
    APPS.AP_INVOICE_PAYMENTS_ALL AIPA,
    APPS.AP_INVOICES_ALL AIA,
    APPS.FND_USER FU
    WHERE APHD.INVOICE_PAYMENT_ID =AIPA.INVOICE_PAYMENT_ID
    AND AIPA.INVOICE_ID =AIA.INVOICE_ID
    AND AIA.CREATED_BY =FU.USER_ID
    AND APHD.PAYMENT_HIST_DIST_ID =XTE.SOURCE_ID_INT_1
    AND ROWNUM =1
    ),’AP_PREPAY’,
    (SELECT FU.USER_NAME
    FROM APPS.AP_INVOICES_ALL AIA,
    APPS.FND_USER FU
    WHERE AIA.CREATED_BY=FU.USER_ID
    AND INVOICE_ID =XTE.SOURCE_ID_INT_1
    AND ROWNUM =1
    )) “INVOICE_PROCESSER_NAME”
    ,XTE.ENTITY_CODE EVENT_TYPE_CODE
    ,
    NULL EXPENSE_TYPE_MAIN
    , GJL.JE_LINE_NUM JE_NUMBER
    , GJH.name JOURNAL_NAME
    , GJH.DESCRIPTION je_DESCRIPTION
    , GJH.CURRENCY_CODE ENTERED_CURRENCY_CODE
    ,NULL PROJECT
    ,NULL PROJECT_DESC
    ,NULL FACILITY
    ,NULL FACILITY_DESC
    , GJH.STATUS
    , GJL.ENTERED_DR
    , GJL.ENTERED_CR
    ,NULL ENTERED_BALANCE
    ,NULL PERIOD_ACTUAL_DR
    ,NULL PERIOD_ACTUAL_CR
    ,NULL PERIOD_BALANCE
    FROM GL.GL_JE_HEADERS GJH
    ,GL.GL_JE_LINES GJL
    ,GL.GL_CODE_COMBINATIONS GCC
    ,GL.GL_LEDGERS GL
    ,GL.GL_IMPORT_REFERENCES GIR
    ,XLA.XLA_AE_LINES XAL
    ,XLA.XLA_AE_HEADERS XAH
    –,XLA.XLA_DISTRIBUTION_LINKS XDL
    ,XLA.XLA_TRANSACTION_ENTITIES XTE
    WHERE 1=1–GJH.je_header_id=10315596
    AND GJH.JE_SOURCE = ‘Payables’
    — and gjh.PERIOD_NAME=’Dec23-24′
    AND GJH.JE_HEADER_ID = GJL.JE_HEADER_ID
    AND GJL.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
    AND GJL.LEDGER_ID = GL.LEDGER_ID
    AND GL.LEDGER_ID = NVL(:PRIMARY_LEDGER_ID,GL.LEDGER_ID)
    AND TRUNC(GJL.EFFECTIVE_DATE) BETWEEN TO_DATE(:FROM_PERIOD,’YYYY/MM/DD’) AND TO_DATE(:TO_PERIOD,’YYYY/MM/DD’)
    AND GJL.JE_HEADER_ID = GIR.JE_HEADER_ID
    AND GJL.JE_LINE_NUM = GIR.JE_LINE_NUM
    AND GJL.GL_SL_LINK_ID = GIR.GL_SL_LINK_ID
    AND GJL.GL_SL_LINK_TABLE = GIR.GL_SL_LINK_TABLE
    AND GIR.GL_SL_LINK_ID = XAL.GL_SL_LINK_ID
    AND GIR.GL_SL_LINK_TABLE = XAL.GL_SL_LINK_TABLE
    AND XAL.AE_HEADER_ID = XAH.AE_HEADER_ID
    AND XAL.APPLICATION_ID = XAH.APPLICATION_ID
    –AND XAL.APPLICATION_ID = XDL.APPLICATION_ID
    –AND XAL.AE_HEADER_ID = XDL.AE_HEADER_ID
    –AND XAL.AE_LINE_NUM = XDL.AE_LINE_NUM
    AND XAH.ENTITY_ID = XTE.ENTITY_ID
    AND XAH.APPLICATION_ID = XTE.APPLICATION_ID
    –and XTE.ENTITY_CODE=’AP_INVOICES’
  5. Script Output:
    • The result will provide transaction-level details, such as:
      • Invoice Number and Invoice Date.
      • Account Information: Includes details like ACCOUNT_DESC, EXPENSE_TYPE, JE_NUMBER, and JOURNAL_NAME.
      • Vendor Details: Including PARTY_CODE, PARTY_NAME (Vendor name), and other related information.
      • PO Details: If the invoice is matched to a PO, the script will return the PO Number and other PO-specific information.
      • Invoice Description and Voucher Number for tracking and reconciliation purposes.
  6. Conclusion:
    • By running this script with appropriate parameters, users will be able to perform an in-depth reconciliation between GL balances and the AP subledger (invoice and PO transactions).
    • This will ensure that the amounts recorded in GL for liability accounts match the details in AP, particularly focusing on matching PO invoices to the Purchase Register.Example Use Case:

      Scenario: A user needs to reconcile a GL liability account against the AP subledger for a specific period, verifying that PO-matched invoices are accurately recorded.

      Execution:

      • The user enters the Ledger ID, From Date, and To Date in the query parameters.
      • The script runs and returns a detailed report showing which invoices are linked to which GL account, detailing the specific transactions and their statuses (e.g., AP Invoice, AP Payment).

      By leveraging this script, the process of validating AP invoices and reconciling them with the GL becomes more streamlined and accurate, reducing manual reconciliation effort.

Recent Posts

Start typing and press Enter to search