This AR Bank Charges Report SQL query retrieves bank charge configuration details related to interbank fund transfers, including both customer and supplier banks, from Oracle Financials.
#sqlquery
SELECT
--SQL4Fusion (An Orbit Analytics Project).
--This query retrieves bank charge configuration details related to interbank fund transfers, including both customer and supplier banks
HAOU.NAME ORGANIZATION_NAME,
ABC.TRANSFERRING_BANK_NAME CUSTOMER_BANK_NAME,
ABC.TRANSFERRING_BANK,
ABC.TRANSFERRING_BRANCH,
ABC.RECEIVING_BANK_NAME SUPPLIER_BANK_NAME,
ABC.RECEIVING_BANK,
ABC.RECEIVING_BRANCH,
ABC.SETTLEMENT_PRIORITY,
ABC.CURRENCY_CODE,
ABCL.TRANS_AMOUNT_FROM TRANSFER_AMOUNT_FROM,
ABCL.TRANS_AMOUNT_TO TRANSFER_AMOUNT_TO,
ABCL.START_DATE TRANSFER_START_DATE,
ABCL.END_DATE TRANSFER_END_DATE,
ABCL.BANK_CHARGE_STANDARD,
ABCL.BANK_CHARGE_NEGOTIATED,
CBBV.BANK_NUMBER TRANSFER_BANK_NUMBER,
CBBV.BANK_NAME TRANSFER_BANK_NAME,
CBBV.BRANCH_NUMBER TRANSFER_BANK_BRANCH_NUMBER,
CBBV.BANK_BRANCH_NAME TRANSFER_BANK_BRANCH_NAME,
CBBV2.BANK_NUMBER RECEIVE_BANK_NUMBER,
CBBV2.BANK_NAME RECEIVE_BANK_NAME,
CBBV2.BRANCH_NUMBER RECEIVE_BANK_BRANCH_NUMBER,
CBBV2.BANK_BRANCH_NAME RECEIVE_BANK_BRANCH_NAME
FROM
AP_BANK_CHARGES ABC,
AP_BANK_CHARGE_LINES ABCL,
CE_BANK_BRANCHES_V CBBV,
CE_BANK_BRANCHES_V CBBV2,
CE_BANK_ACCOUNTS CBA,
CE_BANK_ACCT_USES_ALL CBAUA,
HR_ALL_ORGANIZATION_UNITS HAOU
WHERE
1 = 1
AND ABC.BANK_CHARGE_ID = ABCL.BANK_CHARGE_ID
AND ABC.TRANSFERRING_BANK_BRANCH_ID = CBBV.BRANCH_PARTY_ID(+)
AND ABC.RECEIVING_BANK_BRANCH_ID = CBBV2.BRANCH_PARTY_ID(+)
AND CBBV.BRANCH_PARTY_ID = CBA.BANK_BRANCH_ID(+)
AND CBA.BANK_ACCOUNT_ID = CBAUA.BANK_ACCOUNT_ID(+)
AND CBAUA.ORG_ID = HAOU.ORGANIZATION_ID(+)