This AR Receipts Register query retrieves detailed information on customer cash receipts from Oracle Receivables.
#sqlquery
SELECT
--SQL4Fusion (An Orbit Analytics Project)
--This query retrieves detailed information on customer cash receipts
HAOU.NAME ORGANIZATION_NAME,
ABA.NAME BATCH_NAME,
HP.PARTY_NAME CUSTOMER_NAME,
HCA.ACCOUNT_NUMBER CUSTOMER_NUMBER,
ACRA.RECEIPT_NUMBER,
CBA.CURRENCY_CODE,
NVL (DECODE ( ACRHA.STATUS,'REVERSED',ACRHA.AMOUNT * -1,ACRHA.AMOUNT),0 ) RECEIPT_AMOUNT,
ACRHA.STATUS RECEIPT_HISTORY_STATUS,
NVL (DECODE (ACRHA.STATUS,'REVERSED',ACRHA.ACCTD_AMOUNT * -1,ACRHA.ACCTD_AMOUNT),0 ) FUNCTIONAL_RECEIPT_AMOUNT,
NVL (DECODE (ACRHA.STATUS,'REVERSED',ACRHA.FACTOR_DISCOUNT_AMOUNT * -1,ACRHA.FACTOR_DISCOUNT_AMOUNT),0 ) FACTOR_DISCOUNT_AMOUNT,
NVL (DECODE (ACRHA.STATUS,'REVERSED',ACRHA.ACCTD_FACTOR_DISCOUNT_AMOUNT * -1,ACRHA.ACCTD_FACTOR_DISCOUNT_AMOUNT),0)FUNCTIONAL_FACTOR_DISCOUNT_AMOUNT,
ACRA.EXCHANGE_RATE,
GCC.SEGMENT1||'-'||GCC.SEGMENT2||'-'||GCC.SEGMENT3||'-'||GCC.SEGMENT4||'-'||GCC.SEGMENT5 GL_ACCOUNT,
ACRA.EXCHANGE_DATE,
ACRA.EXCHANGE_RATE_TYPE,
FDS.NAME DOCUMENT_SEQUENCE_NAME,
ACRA.DOC_SEQUENCE_VALUE DOCUMENT_SEQUENCE_VALUE,
ACRA.DEPOSIT_DATE,
ACRA.RECEIPT_DATE,
ACRA.STATUS RECEIPT_STATUS,
ACRA.MISC_PAYMENT_SOURCE MISCELLANEOUS_PAYMENT_SOURCE,
JCVTT.TAX_TYPE_CODE,
ACRA.REFERENCE_TYPE ,
ACRA.ANTICIPATED_CLEARING_DATE,
CBBV.BANK_NAME,
CBBV.BANK_NAME_ALT ALTERNATE_BANK_NAME,
CBBV.BANK_BRANCH_NAME,
CBBV.BANK_BRANCH_NAME_ALT ALTERNATE_BANK_BRANCH_NAME,
CBBV.BANK_NUMBER,
CBBV.BRANCH_NUMBER,
CBA.BANK_ACCOUNT_NAME,
CBA.BANK_ACCOUNT_NAME_ALT ALTERNATE_BANK_ACCOUNT_NAME,
ARM.NAME RECEIPT_METHOD,
ACRHA.GL_DATE,
AL.MEANING RECEIPT_TYPE
FROM
AR_CASH_RECEIPTS_ALL ACRA,
AR_CASH_RECEIPT_HISTORY_ALL ACRHA,
JA_CN_VAT_TAX_TYPES JCVTT,
CE_BANK_ACCT_USES_ALL CBAUA,
CE_BANK_ACCOUNTS CBA,
CE_BANK_BRANCHES_V CBBV,
AR_RECEIPT_METHODS ARM,
GL_CODE_COMBINATIONS GCC,
HZ_CUST_ACCOUNTS HCA,
HZ_PARTIES HP,
AR_BATCHES_ALL ABA,
FND_DOCUMENT_SEQUENCES FDS,
AR_LOOKUPS AL,
HR_ALL_ORGANIZATION_UNITS HAOU
WHERE
1 = 1
AND ACRA.CASH_RECEIPT_ID = ACRHA.CASH_RECEIPT_ID
AND ACRA.VAT_TAX_ID = JCVTT.VAT_TAX_TYPE_ID(+)
AND ACRA.REMIT_BANK_ACCT_USE_ID = CBAUA.BANK_ACCT_USE_ID
AND CBAUA.BANK_ACCOUNT_ID = CBA.BANK_ACCOUNT_ID
AND CBA.BANK_BRANCH_ID = CBBV.BRANCH_PARTY_ID
AND ACRA.RECEIPT_METHOD_ID = ARM.RECEIPT_METHOD_ID
AND ACRHA.ACCOUNT_CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
AND ACRA.PAY_FROM_CUSTOMER = HCA.CUST_ACCOUNT_ID
AND HCA.PARTY_ID = HP.PARTY_ID
AND ACRHA.BATCH_ID = ABA.BATCH_ID(+)
AND ACRA.DOC_SEQUENCE_ID = FDS.DOC_SEQUENCE_ID(+)
AND AL.LOOKUP_TYPE = 'PAYMENT_CATEGORY_TYPE'
AND AL.LOOKUP_CODE = ACRA.TYPE
AND ACRA.ORG_ID = HAOU.ORGANIZATION_ID
AND NVL (ACRA.CONFIRMED_FLAG, 'Y') = 'Y'