Looking for an SQL query to extract AR Customer Bank Account Details in Oracle Fusion?
This post provides a ready-to-use SQL script that retrieves customer bank accounts, payment instruments, party details, payment methods, and account assignments — all aligned with Fusion ERP tables.
This SQL is particularly useful for:
- AR & Finance Reporting
- Customer Master Validation
- Bank Account Reconciliation
- Payment Instrument Analysis
- Data Migration & Cleansing
It returns customer name, account number, branch information, instrument use, payment method, and bank account assignment.
If you’re working on AR Reports, Receivables setups, or Fusion data extraction, this SQL gives you a comprehensive starting point.
Feel free to customize it for your Fusion setup — and if you need help identifying PVOs or dependencies, try the PVO Explorer or Fusion SQL Editor inside SQL4Fusion.
#sqlquery
#OracleFusionSQLquery
SELECT
--SQL4Fusion (An Orbit Analytics Project)
--This query retrieves customer payment instrument and bank account details for funds capture transactions
HP.PARTY_NAME CUSTOMER_NAME,
HP.PARTY_NUMBER CUSTOMER_NUMBER,
HCA.ACCOUNT_NUMBER CUSTOMER_ACCOUNT_NUMBER,
HCA.STATUS CUSTOMER_ACCOUNT_STATUS,
IPIUA.INSTRUMENT_PAYMENT_USE_ID,
IPIUA.PRIMARY_FLAG,
IEPA.BANK_CHARGE_BEARER_CODE,
-- IEPA.CUST_ACCOUNT_ID,
IEPA.DIRDEB_INSTRUCTION_CODE,
-- IEPA.EXT_PAYER_ID,
--IEBA.EXT_BANK_ACCOUNT_ID,
IEBA.BANK_ACCOUNT_NAME,
IEBA.BANK_ACCOUNT_TYPE,
IEBA.CURRENCY_CODE,
IEBA.DESCRIPTION,
--HOP.ORGANIZATION_PROFILE_ID AS ORGANIZATION_PROFILE_ID1,
HOP.ORGANIZATION_NAME ,
IEPA.LOCALINSTR LOCAL_INSTRUMENT_CODE,
IEPA.SERVICE_LEVEL SERVICE_LEVEL_CODE,
IEPA.PURPOSE_CODE
FROM
HZ_PARTIES HP,
HZ_CUST_ACCOUNTS HCA,
IBY_EXTERNAL_PAYERS_ALL IEPA,
IBY_PMT_INSTR_USES_ALL IPIUA,
IBY_EXT_BANK_ACCOUNTS IEBA,
HZ_ORGANIZATION_PROFILES HOP
WHERE HP.PARTY_ID =HCA.PARTY_ID
AND HCA.CUST_ACCOUNT_ID=IEPA.CUST_ACCOUNT_ID(+)
AND IEPA.EXT_PAYER_ID=IPIUA.EXT_PMT_PARTY_ID (+)
AND IPIUA.INSTRUMENT_ID = IEBA.EXT_BANK_ACCOUNT_ID(+)
AND IEBA.BRANCH_ID = HOP.PARTY_ID(+)
AND IPIUA.INSTRUMENT_TYPE = 'BANKACCOUNT'
AND IPIUA.PAYMENT_FLOW = 'FUNDS_CAPTURE'
AND IEPA.ACCT_SITE_USE_ID IS NULL
AND SYSDATE BETWEEN HOP.EFFECTIVE_START_DATE AND HOP.EFFECTIVE_END_DATE