This AR miscellaneous receipts register query retrieves detailed information about miscellaneous cash receipts in Oracle Receivables (AR). It includes receipt details, customer information, bank and account data, tax details, and receipt history.
#sqlquery
SELECT
--SQL4Fusion (An Orbit Analytics Project)
--This query retrieves detailed information about miscellaneous cash receipts
HAOU.NAME ORGANIZATION_NAME,
ABA.NAME BATCH_NAME,
HP.PARTY_NAME CUSTOMER_NAME,
HCA.ACCOUNT_NUMBER CUSTOMER_NUMBER,
ACRA.RECEIPT_NUMBER,
ACRA.CURRENCY_CODE,
DECODE(ACRHA.STATUS,'REVERSED',ACRHA.AMOUNT * -1,ACRHA.AMOUNT) RECEIPT_AMOUNT,
DECODE(ACRHA.STATUS,'REVERSED',ACRHA.ACCTD_AMOUNT * -1,ACRHA.ACCTD_AMOUNT) FUNCTIONAL_RECEIPT_AMOUNT,
DECODE(ACRHA.STATUS, 'REVERSED',ACRHA.FACTOR_DISCOUNT_AMOUNT * -1,ACRHA.FACTOR_DISCOUNT_AMOUNT) FACTOR_DISCOUNT_AMOUNT,
DECODE(ACRHA.STATUS, 'REVERSED',ACRHA.ACCTD_FACTOR_DISCOUNT_AMOUNT * -1,ACRHA.ACCTD_FACTOR_DISCOUNT_AMOUNT) FUNCTIONAL_FACTOR_DISCOUNT_AMOUNT,
DECODE(ARD.SOURCE_TYPE, 'TAX', NULL, AMCDA.PERCENT) MISCELLANEOUS_PERCENT,
DECODE(SIGN (AMCDA.AMOUNT),1,ARD.AMOUNT_CR,-1,SIGN (AMCDA.AMOUNT) * ARD.AMOUNT_DR,0 ) MISCELLANEOUS_AMOUNT,
ACRHA.STATUS RECEIPT_HISTORY_STATUS,
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,
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,
DECODE(ARD.SOURCE_TYPE,'TAX',JCVTT.TAX_TYPE_CODE, ARD.SOURCE_TYPE) TAX_CODE
FROM
AR_CASH_RECEIPTS_ALL ACRA,
JA_CN_VAT_TAX_TYPES JCVTT,
AR_CASH_RECEIPT_HISTORY_ALL ACRHA,
CE_BANK_ACCT_USES_ALL CBAUA,
CE_BANK_ACCOUNTS CBA,
CE_BANK_BRANCHES_V CBBV,
AR_RECEIPT_METHODS ARM,
AR_BATCHES_ALL ABA,
GL_CODE_COMBINATIONS GCC,
HZ_CUST_ACCOUNTS HCA,
HZ_PARTIES HP,
AR_MISC_CASH_DISTRIBUTIONS_ALL AMCDA,
AR_DISTRIBUTIONS_ALL ARD,
AR_SYSTEM_PARAMETERS_ALL ASPA,
FND_DOCUMENT_SEQUENCES FDS,
HR_ALL_ORGANIZATION_UNITS HAOU
WHERE
1 = 1
AND ACRA.VAT_TAX_ID = JCVTT.VAT_TAX_TYPE_ID(+)
AND ACRA.CASH_RECEIPT_ID = ACRHA.CASH_RECEIPT_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.BATCH_ID = ABA.BATCH_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 ACRA.CASH_RECEIPT_ID = AMCDA.CASH_RECEIPT_ID
AND AMCDA.MISC_CASH_DISTRIBUTION_ID = ARD.SOURCE_ID
AND ACRA.ORG_ID = ASPA.ORG_ID
AND ACRA.DOC_SEQUENCE_ID = FDS.DOC_SEQUENCE_ID(+)
-- AND ACRHA.FIRST_POSTED_RECORD_FLAG = 'Y'
AND ARD.SOURCE_TABLE = 'MCD'
AND ACRA.TYPE = 'MISC'
AND ACRA.ORG_ID = HAOU.ORGANIZATION_ID