This AR Miscellaneous Cash Distributions SQL query retrieves detailed information on miscellaneous cash receipt distributions from Oracle Receivables (AR). It focuses on the relationship between receipts and their distributions, including accounting, reversal, and period details.
#sqlquery
SELECT
--SQL4Fusion (An Orbit Analytics Project)
--This SQL query retrieves detailed information on miscellaneous cash receipt distributions
HAOU.NAME ORGANIZATION_NAME,
ACRA.RECEIPT_NUMBER,
GL.CURRENCY_CODE,
ACRA.AMOUNT RECEIPT_AMOUNT,
ACRA.STATUS RECEIPT_STATUS,
ACRA.MISC_PAYMENT_SOURCE MISCELLANEOUS_PAYMENT_SOURCE,
ARTA.NAME RECEIPT_ACTIVITY,
NVL (AMCDA.AMOUNT, 0) DISTRIBUTION_AMOUNT,
NVL (AMCDA.ACCTD_AMOUNT, 0) JOURNAL_ENTRY_AMOUNT,
ACRA.EXCHANGE_DATE,
ACRA.AMOUNT * NVL (ACRA.EXCHANGE_RATE,1) FOREIGN_RECEIPT_AMOUNT,
AMCDA.COMMENTS DISTRIBUTION_COMMENTS,
AMCDA.APPLY_DATE DISTRIBUTION_DATE,
ACRA.COMMENTS RECEIPT_COMMENTS,
ACRA.RECEIPT_DATE ,
ACRA.DEPOSIT_DATE RECEIPT_DEPOSIT_DATE,
AMCDA.GL_DATE RECEIPT_GL_DATE,
AMCDA.GL_POSTED_DATE RECEIPT_GL_POSTED_DATE,
GP.PERIOD_NAME RECEIPT_PERIOD_NAME,
GP.PERIOD_NUM RECEIPT_PERIOD_NUMBER,
GP.QUARTER_NUM RECEIPT_PERIOD_QUARTER,
GP.START_DATE RECEIPT_PERIOD_START_DATE,
GP.PERIOD_YEAR RECEIPT_PERIOD_YEAR,
CBA.BANK_ACCOUNT_NAME REMITTANCE_BANK_ACCOUNT,
CBA.BANK_ACCOUNT_NUM REMITTANCE_BANK_ACCOUNT_NUMBER,
ACRA.REVERSAL_DATE,
AMCDA.REVERSAL_GL_DATE ,
GP1.PERIOD_NAME REVERSAL_PERIOD_NAME,
ACRA.EXCHANGE_RATE,
ACRA.EXCHANGE_RATE_TYPE ,
GCC.ACCOUNT_TYPE,
GL.ALC_LEDGER_TYPE_CODE REPORTING_CURRENCY_TYPE,
ACRHA.NOTE_STATUS,
ACRA.REVERSAL_CATEGORY,
ACRA.REVERSAL_REASON_CODE
FROM
AR_MISC_CASH_DISTRIBUTIONS_ALL AMCDA,
AR_CASH_RECEIPTS_ALL ACRA,
AR_CASH_RECEIPT_HISTORY_ALL ACRHA,
GL_CODE_COMBINATIONS GCC,
AR_RECEIVABLES_TRX_ALL ARTA,
GL_LEDGERS GL,
GL_PERIODS GP,
--GL_LEDGERS GL,
GL_PERIODS GP1,
CE_BANK_ACCOUNTS CBA,
HR_ALL_ORGANIZATION_UNITS HAOU
WHERE
1 = 1
AND AMCDA.CASH_RECEIPT_ID = ACRA.CASH_RECEIPT_ID
AND ACRA.CASH_RECEIPT_ID = ACRHA.CASH_RECEIPT_ID
AND ACRHA.CURRENT_RECORD_FLAG || '' = 'Y'
AND AMCDA.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID(+)
AND ACRA.RECEIVABLES_TRX_ID = ARTA.RECEIVABLES_TRX_ID(+)
AND ACRA.SET_OF_BOOKS_ID = GL.LEDGER_ID
AND GL.ACCOUNTED_PERIOD_TYPE = GP.PERIOD_TYPE
AND GP.ADJUSTMENT_PERIOD_FLAG = 'N'
AND GP.PERIOD_SET_NAME || '' = GL.PERIOD_SET_NAME
AND AMCDA.GL_DATE BETWEEN GP.START_DATE AND GP.END_DATE
AND GP1.ADJUSTMENT_PERIOD_FLAG(+) = 'N'
AND NVL(GP1.PERIOD_SET_NAME,GL.PERIOD_SET_NAME) = GL.PERIOD_SET_NAME
AND NVL (GP1.PERIOD_TYPE,GL.ACCOUNTED_PERIOD_TYPE ) = GL.ACCOUNTED_PERIOD_TYPE
AND AMCDA.REVERSAL_GL_DATE BETWEEN GP1.START_DATE(+) AND GP1.END_DATE(+)
AND ACRA.REMITTANCE_BANK_ACCOUNT_ID = CBA.BANK_ACCOUNT_ID(+)
AND NVL (ACRA.CONFIRMED_FLAG, 'Y') = 'Y'
AND ACRA.ORG_ID = ARTA.ORG_ID
AND ARTA.ORG_ID = AMCDA.ORG_ID
--AND AMCDA.ORG_ID = ARTA.ORG_ID
AND HAOU.ORGANIZATION_ID = ARTA.ORG_ID