This AR Automatic Receipts Awaiting Confirmation query provides detailed information on customer cash receipts, including receipt number, date, amount, due date, status, customer and bank account details, tax classification, and site-related attributes.
#sqlquery
SELECT
-- SQL4Fusion (An Orbit Analytics Project)
-- This query provides detailed information on customer cash receipts.
HAOU.NAME ORGANIZATION_NAME,
SUBSTRB (HP.PARTY_NAME, 1, 50) CUSTOMER_NAME,
HCC.ACCOUNT_NUMBER CUSTOMER_NUMBER,
HCSUA.LOCATION CUSTOMER_LOCATION,
CBA.BANK_ACCOUNT_NAME ,
APSA.DUE_DATE ,
ACRA.RECEIPT_NUMBER ,
ACRA.RECEIPT_DATE ,
NVL (ACRA.AMOUNT, 0) RECEIPT_AMOUNT,
ACRA.CURRENCY_CODE ,
ACRA.ORG_ID,
HP.PARTY_ID,
ACRA.CASH_RECEIPT_ID,
ACRHA.NOTE_STATUS,
HCSUA.TAX_CLASSIFICATION,
HCSUA.TAX_ROUNDING_RULE,
ACRA.STATUS RECEIPT_STATUS,
ACRA.REVERSAL_REASON_CODE,
HCC.STATUS CUST_ACCOUNT_STATUS,
HCSUA.STATUS CUST_SITE_STATUS,
HP.CATEGORY_CODE,
HCC.CUSTOMER_TYPE,
APSA.CLASS PAYMENT_SCHEDULE_CLASS,
APSA.STATUS PAYMENT_SCHEDULE_STATUS,
ACRHA.STATUS CASH_RECEIPT_STATUS,
ACRA.REVERSAL_CATEGORY,
HCSUA.SITE_USE_CODE
FROM
AR_CASH_RECEIPT_HISTORY_ALL ACRHA,
AR_CASH_RECEIPTS_ALL ACRA,
AR_PAYMENT_SCHEDULES_ALL APSA,
HZ_CUST_ACCOUNTS HCC,
HZ_PARTIES HP,
HZ_CUST_SITE_USES_ALL HCSUA,
CE_BANK_ACCT_USES_ALL CBAUA,
CE_BANK_ACCOUNTS CBA,
HR_ALL_ORGANIZATION_UNITS HAOU
WHERE
1 = 1
AND ACRHA.CASH_RECEIPT_ID = ACRA.CASH_RECEIPT_ID
AND ACRHA.CURRENT_RECORD_FLAG = 'Y'
AND ACRA.CASH_RECEIPT_ID = APSA.CASH_RECEIPT_ID
AND ACRA.PAY_FROM_CUSTOMER = HCC.CUST_ACCOUNT_ID
AND HCC.PARTY_ID = HP.PARTY_ID
AND ACRA.CUSTOMER_SITE_USE_ID=HCSUA.SITE_USE_ID(+)
AND ACRA.REMIT_BANK_ACCT_USE_ID=CBAUA.BANK_ACCT_USE_ID(+)
AND CBAUA.BANK_ACCOUNT_ID = CBA.BANK_ACCOUNT_ID(+)
AND ACRA.ORG_ID=HAOU.ORGANIZATION_ID