This AR miscellaneous cash receipt accounting details query retrieves details of miscellaneous AR cash receipts, including receipt status, type, amounts, accounting distributions, ledger and GL account information, and corresponding journal and batch details from Receivables and Subledger Accounting.
#sqlquery
SELECT
--SQL4Fusion (An Orbit Analytics Project)
--This query retrieves details of miscellaneous cash receipts accounting details
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,
AR_MISC_CASH_DISTRIBUTIONS_ALL AMCDA
WHERE 1=1
-- RECEIPT_NUMBER = '49'
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 ADA.SOURCE_ID=AMCDA.MISC_CASH_DISTRIBUTION_ID
AND ACRA.CASH_RECEIPT_ID = AMCDA.CASH_RECEIPT_ID
AND XTE.ENTITY_CODE = 'RECEIPTS'
AND XAL.ACCOUNTING_CLASS_CODE = 'MISC_CASH'
AND ACRA.TYPE = 'MISC'