Hi All,
Please find below the Query to identify if transactions has been posted to GL. This will be helpful when validating transactions during the Month End Period Closure.
SELECT
RA_CUST_TRX.STATUS_TRX,
GL.SHORT_NAME,
RA_CUST_TRX.INVOICE_CURRENCY_CODE TRX_CURR,
RA_CUST_TRX.TRX_NUMBER,
RA_CUST_TRX.TRX_DATE,
RA_CUST_TRX.BILLING_DATE,
GJH.NAME GL_JE_HEADER_NAME,
GJH.DESCRIPTION GL_HDR_DESC,
GJH.JE_BATCH_ID,
GJL.JE_LINE_NUM,
RA_CUST_TRX.CUST_TRX_TYPE_ID,
RA_CUST_TRX.BILL_TO_CUSTOMER_ID,
RA_CUST_TRX.BATCH_ID,
RA_CUST_TRX.BATCH_SOURCE_ID,
RA_CUST_TRX.TERM_ID,
RA_CUST_TRX_DIST.AMOUNT AR_INV_DIST_AMOUNT,
GJL.CODE_COMBINATION_ID GL_JE_LINE_CCID,
RA_CUST_TRX_DIST.CODE_COMBINATION_ID AR_INV_DIST_CCID,
GCC.SEGMENT1
|| ‘.’
|| GCC.SEGMENT2
|| ‘.’
|| GCC.SEGMENT3
|| ‘.’
|| GCC.SEGMENT4
|| ‘.’
|| GCC.SEGMENT5
|| ‘.’
|| GCC.SEGMENT6
|| ‘.’
|| GCC.SEGMENT7
|| ‘.’
|| GCC.SEGMENT8
AR_INV_DIST_CODE_COMBINATION
FROM AR.RA_CUSTOMER_TRX_ALL RA_CUST_TRX,
AR.RA_CUST_TRX_LINE_GL_DIST_ALL RA_CUST_TRX_DIST,
XLA.XLA_TRANSACTION_ENTITIES XTE,
XLA.XLA_AE_HEADERS XAH,
XLA.XLA_AE_LINES XAL,
XLA.XLA_DISTRIBUTION_LINKS XDL,
GL.GL_JE_HEADERS GJH,
GL.GL_JE_LINES GJL,
GL.GL_LEDGERS GL,
GL.GL_CODE_COMBINATIONS GCC,
GL.GL_IMPORT_REFERENCES GL_IMP_REF
WHERE 1 = 1
AND XTE.ENTITY_CODE = ‘TRANSACTIONS’
AND XAL.ACCOUNTING_CLASS_CODE = ‘REVENUE’
AND RA_CUST_TRX.CUSTOMER_TRX_ID = RA_CUST_TRX_DIST.CUSTOMER_TRX_ID
AND XTE.SOURCE_ID_INT_1 = RA_CUST_TRX.CUSTOMER_TRX_ID
AND GJH.JE_HEADER_ID = GL_IMP_REF.JE_HEADER_ID
AND GJL.JE_HEADER_ID = GJH.JE_HEADER_ID
AND GJL.JE_LINE_NUM = GL_IMP_REF.JE_LINE_NUM
AND GCC.CODE_COMBINATION_ID = RA_CUST_TRX_DIST.CODE_COMBINATION_ID
AND GL.CHART_OF_ACCOUNTS_ID = GCC.CHART_OF_ACCOUNTS_ID
AND XTE.LEDGER_ID = GL.LEDGER_ID
AND XTE.APPLICATION_ID = 222
AND XAH.ENTITY_ID = XTE.ENTITY_ID
AND XAL.AE_HEADER_ID = XAH.AE_HEADER_ID
AND XDL.AE_HEADER_ID = XAH.AE_HEADER_ID
AND XDL.AE_LINE_NUM = XAL.AE_LINE_NUM
AND XDL.SOURCE_DISTRIBUTION_ID_NUM_1 = RA_CUST_TRX_DIST.CUST_TRX_LINE_GL_DIST_ID
AND XDL.APPLICATION_ID = 222
AND GL_IMP_REF.GL_SL_LINK_ID = XAL.GL_SL_LINK_ID
— AND RA_CUST_TRX.TRX_NUMBER = :P_ENTER_TRX_NUMBER
— AND TRX.CUSTOMER_TRX_ID = :P_ENTER_CUSTOMER_TRX_ID
— AND TRX.BATCH_SOURCE_ID = :P_BATCH_SOURCE_ID
Related posts:
SQL Query to drill down from the Oracle apps R12 FA Assets mapping to GL
AR SQL Query to get Open Invoices for a Customer or for all Customers where payment status is Open
Oracle apps R12 Query to check Customer Transaction Balances
Oracle apps R12 Query to get the Credit Memo Balance Due
Query to get AGIS – Intercompany Matched or unmatched Balances for a particular Account level details
SQL Query to check if AP invoices is posted or not
Query to get the Receipt Class and Receipt Method setup in the system
SQL query to identify AP cancelled invoices having distribution lines