This AR receipt accounting details query retrieves AR receipt details along with their accounting and GL journal information, including receipt status, type, amounts, accounting entries, GL accounts, ledger, batch, and journal details for receipts posted to Receivables.
#sqlquery
SELECT
--SQL4Fusion (An Orbit Analytics Project)
--This query retrieves complete accounting and journal details for receivables cash receipts
ACRA.RECEIPT_NUMBER,
ACRA.CURRENCY_CODE,
ACRA.AMOUNT RECEIPT_AMOUNT,
DECODE(ACRA.STATUS,'APP', 'APPLIED','UNAPP','UNAPPLIED','UNID','UNIDENTIFIED','REV','REVERSE','NSF','INSUFFICIENT FUNDS',ACRA.STATUS) RECEIPT_STATUS,
ACRA.TYPE RECEIPT_TYPE,
ACRA.RECEIPT_DATE,
XTE.ENTITY_CODE,
XAH.EVENT_TYPE_CODE,
XAH.GL_TRANSFER_STATUS_CODE,
XAH.JE_CATEGORY_NAME,
XAH.BALANCE_TYPE_CODE,
XAH.AE_HEADER_ID,
GJB.NAME BATCH_NAME,
DECODE(GJL.STATUS,'P','POSTED','U','UNPOSTED', GJL.STATUS) BATCH_STATUS,
GJH.NAME JOURNAL_NAME,
GJH.JE_SOURCE,
GJH.JE_CATEGORY,
GJH.STATUS JOURNAL_STATUS,
DECODE(GJH.ACTUAL_FLAG,'A','ACTUAL','B','BUDGET','E','ENCUMBRANCE',GJH.ACTUAL_FLAG) BALANCE_TYPE,
GL.NAME LEDGER_NAME,
GCC.SEGMENT1 || '-' || GCC.SEGMENT2 || '-' || GCC.SEGMENT3 || '-' ||
GCC.SEGMENT4 || '-' || GCC.SEGMENT5 GL_ACCOUNT,
XAL.ACCOUNTING_CLASS_CODE,
XAL.AE_LINE_NUM,
XAL.ACCOUNTED_DR ACCOUNTED_DEBIT_AMOUNT,
XAL.ACCOUNTED_CR ACCOUNTED_CREDIT_AMOUNT,
XAL.ACCOUNTING_DATE
FROM
AR_CASH_RECEIPTS_ALL ACRA,
XLA_TRANSACTION_ENTITIES XTE,
XLA_EVENTS XLE,
XLA_AE_HEADERS XAH,
XLA_AE_LINES XAL,
XLA_DISTRIBUTION_LINKS XDL,
AR_DISTRIBUTIONS_ALL ADA,
GL_LEDGERS GL,
GL_CODE_COMBINATIONS GCC,
GL_IMPORT_REFERENCES GIR,
GL_JE_LINES GJL,
GL_JE_HEADERS GJH,
GL_JE_BATCHES GJB
WHERE 1=1
AND ACRA.CASH_RECEIPT_ID = XTE.SOURCE_ID_INT_1
AND XTE.ENTITY_ID = XLE.ENTITY_ID
AND XTE.APPLICATION_ID = XLE.APPLICATION_ID
AND XLE.EVENT_ID = XAH.EVENT_ID
AND XLE.APPLICATION_ID = XAH.APPLICATION_ID
AND XAH.AE_HEADER_ID = XAL.AE_HEADER_ID
AND XAH.APPLICATION_ID = XAL.APPLICATION_ID
AND XAH.AE_HEADER_ID = XDL.AE_HEADER_ID
AND XAL.AE_LINE_NUM = XDL.AE_LINE_NUM
AND XDL.SOURCE_DISTRIBUTION_TYPE = 'AR_DISTRIBUTIONS_ALL'
AND XDL.SOURCE_DISTRIBUTION_ID_NUM_1 = ADA.LINE_ID
AND XAL.LEDGER_ID = GL.LEDGER_ID
AND XAL.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
AND XAL.GL_SL_LINK_TABLE = GIR.GL_SL_LINK_TABLE
AND XAL.GL_SL_LINK_ID = GIR.GL_SL_LINK_ID
AND GIR.JE_HEADER_ID = GJL.JE_HEADER_ID
AND GIR.JE_LINE_NUM = GJL.JE_LINE_NUM
AND GJL.JE_HEADER_ID = GJH.JE_HEADER_ID
AND GJH.JE_BATCH_ID = GJB.JE_BATCH_ID
AND XTE.ENTITY_CODE = 'RECEIPTS'
AND XAL.ACCOUNTING_CLASS_CODE = 'RECEIVABLE'