#oracle fusion sql
This post shares a ready-to-use SQL query for Oracle Fusion Accounts Receivable that generates a detailed Receipts with Adjustments Report. It helps you extract cleared or adjusted receipts — including bank account, branch, payment method, and transaction details — across your Fusion AR setup.
What this query delivers:
- Organization (company) name, bank and branch details, bank account number & name
- Receipt number, creation status, maturity (due) date, payment (receipt) method
- Receipt currency, functional amount, number of records processed
- Adjustment/transaction number, date, adjustment amount (entered and functional), customer name, adjustment ID
This report is useful for:
- Reconciling receipts and bank accounts
- Auditing AR cash-application history and adjustments
- Generating consolidated receipt/adjustment reports by bank or branch
- Supporting periodic bank reconciliations or AR-to-GL audits
To use: copy the SQL, adjust schema/table aliases if needed, and run it against your Fusion AR schema. If you have questions — or want to extend the query to include more fields (e.g. invoice info, currency conversion, customer site data) — feel free to post your request here.
Happy querying & thanks for contributing!
#sqlquery
SELECT
-- SQL4Fusion (An Orbit Analytics Project)
-- This query generates a detailed Receipts with Adjustments Report, showing receipt and adjustment data across bank accounts
HAOU.NAME ORGANIZATION_NAME,
CBBV.BANK_NAME,
CBBV.BRANCH_NUMBER,
CBA.BANK_ACCOUNT_NUM BANK_ACCOUNT_NUMBER,
CBA.BANK_ACCOUNT_NAME BANK_ACCOUNT_NAME,
AL1.MEANING || ' ' || ACRH.STATUS RECEIPT_CREATION_STATUS,
NVL (
SUM (ACRH.ACCTD_AMOUNT),
0
) FUNCTIONAL_AMOUNT,
COUNT (*) SUM_RECORD_PROCESSED,
ACR.RECEIPT_NUMBER,
TO_CHAR (APSA.DUE_DATE, 'dd-mm-yyyy') MATURITY_DATE,
ARM.NAME RECEIPT_METHOD_NAME,
NVL (ACRH.AMOUNT, 0) AMOUNT,
ACR.CURRENCY_CODE ,
--CBA.CURRENCY_CODE BANK_ACCOUNT_CURRENCY,
--RCTA.INVOICE_CURRENCY_CODE ADJUSTMENT_CURRENCY_CODE,
RCTA.TRX_NUMBER TRANSACTION_NUMBER,
ARAD.ADJUSTMENT_NUMBER ,
RCTA.TRX_DATE TRANSACTION_DATE,
NVL (ROUND (ARAD.AMOUNT, 2), 0) ADJUSTMENT_AMOUNT,
ARAD.ACCTD_AMOUNT ADJUSTMENT_FUNCTIONAL_AMOUNT,
SUBSTRB (HP.PARTY_NAME, 1, 50) ADJUSTMENT_CUSTOMER_NAME,
CBAUA.BANK_ACCT_USE_ID,
ARAD.ADJUSTMENT_ID,
APSA.ORG_ID
FROM
AR_CASH_RECEIPT_HISTORY_ALL ACRH,
AR_CASH_RECEIPTS_ALL ACR,
AR_PAYMENT_SCHEDULES_ALL APSA,
AR_RECEIPT_METHODS ARM,
CE_BANK_ACCT_USES_ALL CBAUA,
CE_BANK_ACCOUNTS CBA,
CE_BANK_BRANCHES_V CBBV,
AR_LOOKUPS AL1,
--AR_LOOKUPS AL2,
RA_CUSTOMER_TRX_ALL RCTA,
AR_ADJUSTMENTS_ALL ARAD,
HZ_CUST_ACCOUNTS HCC,
HZ_PARTIES HP,
HR_ALL_ORGANIZATION_UNITS HAOU
WHERE
1 = 1
AND ACRH.CASH_RECEIPT_ID = ACR.CASH_RECEIPT_ID
AND ACR.CASH_RECEIPT_ID = APSA.CASH_RECEIPT_ID(+)
AND ACR.RECEIPT_METHOD_ID = ARM.RECEIPT_METHOD_ID
AND ACR.REMIT_BANK_ACCT_USE_ID = CBAUA.BANK_ACCT_USE_ID(+)
AND CBAUA.BANK_ACCOUNT_ID = CBA.BANK_ACCOUNT_ID(+)
AND CBBV.BRANCH_PARTY_ID = CBA.BANK_BRANCH_ID
AND ACRH.STATUS = AL1.LOOKUP_CODE
AND AL1.LOOKUP_TYPE = 'RECEIPT_CREATION_STATUS'
--AND AL2.LOOKUP_TYPE = 'REMITTANCE_METHOD'
--AND AL2.LOOKUP_CODE = 'FACTORING'
AND ARAD.ADJUSTMENT_ID > 0
AND ARAD.RECEIVABLES_TRX_ID IS NOT NULL
AND RCTA.CUSTOMER_TRX_ID = ARAD.CUSTOMER_TRX_ID
AND HCC.CUST_ACCOUNT_ID = APSA.CUSTOMER_ID
AND HCC.PARTY_ID = HP.PARTY_ID
AND HAOU.ORGANIZATION_ID = APSA.ORG_ID
AND APSA.PAYMENT_SCHEDULE_ID = ARAD.PAYMENT_SCHEDULE_ID
AND APSA.CUSTOMER_TRX_ID = RCTA.CUSTOMER_TRX_ID
GROUP BY
AL1.MEANING || ' ' || ACRH.STATUS,
CBBV.BANK_NAME,
CBA.BANK_ACCOUNT_NUM,
ACRH.STATUS,
DECODE (
ACRH.FACTOR_FLAG,
'Y',
1,
'N',
2
),
ACR.CURRENCY_CODE,
ACR.RECEIPT_NUMBER,
CBAUA.BANK_ACCT_USE_ID,
CBBV.BRANCH_NUMBER,
CBA.BANK_ACCOUNT_NAME,
CBA.CURRENCY_CODE,
APSA.DUE_DATE,
ARM.NAME,
ACRH.AMOUNT,
ACRH.ACCTD_AMOUNT,
RCTA.INVOICE_CURRENCY_CODE,
RCTA.TRX_NUMBER,
ARAD.ADJUSTMENT_NUMBER,
RCTA.TRX_DATE,
ARAD.AMOUNT,
ARAD.ACCTD_AMOUNT,
HP.PARTY_NAME,
ARAD.ADJUSTMENT_ID,
HAOU.NAME,
APSA.ORG_ID
ORDER BY
CBBV.BANK_NAME,
CBA.BANK_ACCOUNT_NUM,
ACRH.STATUS,
DECODE (
ACRH.FACTOR_FLAG,
'Y',
1,
'N',
2
),
ACR.CURRENCY_CODE,
ACR.RECEIPT_NUMBER