AR Invoice query to get transactions posted to GL

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