This CE Auto Reconciliation Execution Report retrieves reconciliation details of receipts, bank statements, distributions, and exceptions, including transaction info, bank account, and reconciliation status.
#sqlquery
SELECT
--SQL4Fusion (An Orbit Analytics Project)
--This query Shows reconciliation status, receipts, bank statements, distributions, and exceptions.
CSH.STATEMENT_NUMBER,
CSH.STATEMENT_DATE,
CBA.BANK_ACCOUNT_NAME,
ACR.RECEIPT_NUMBER,
ACR.RECEIPT_DATE,
ACR.STATUS RECEIPT_STATUS,
ARM.NAME RECEIPT_METHOD_NAME,
GCC.SEGMENT1 || '.' || GCC.SEGMENT2 || '.' || GCC.SEGMENT3
|| '.' || GCC.SEGMENT4 || '.' || GCC.SEGMENT5 GL_ACCOUNT,
CASE
WHEN CRE.EXCEPTION_TYPE IS NULL THEN 'RECONCILED'
ELSE CRE.EXCEPTION_TYPE
END RECONCILIATION_STATUS,
AMC.AMOUNT DISTRIBUTION_AMOUNT,
ACR.AMOUNT RECEIPT_AMOUNT,
CRE.TRANSACTION_SOURCE EXCEPTION_SOURCE
FROM
AR_CASH_RECEIPTS_ALL ACR,
AR_MISC_CASH_DISTRIBUTIONS_ALL AMC,
AR_RECEIPT_METHODS ARM,
CE_RECON_EXCEPTIONS CRE,
CE_STATEMENT_LINES CSL,
CE_STATEMENT_HEADERS CSH,
CE_BANK_ACCOUNTS CBA,
GL_CODE_COMBINATIONS GCC
WHERE
ACR.CASH_RECEIPT_ID = AMC.CASH_RECEIPT_ID
AND ACR.RECEIPT_METHOD_ID = ARM.RECEIPT_METHOD_ID
AND CRE.TRANSACTION_ID = ACR.CASH_RECEIPT_ID
AND CRE.STATEMENT_LINE_ID = CSL.STATEMENT_LINE_ID
AND CSL.STATEMENT_HEADER_ID = CSH.STATEMENT_HEADER_ID
AND CSH.BANK_ACCOUNT_ID = CBA.BANK_ACCOUNT_ID
AND AMC.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
ORDER BY
CSH.STATEMENT_DATE,
CSH.STATEMENT_NUMBER,
ACR.RECEIPT_DATE;