AR Remittance Batch Management Summary (Oracle Fusion SQL)

Enterprise Resource Planning (ERP) Oracle Account Receivables (AR-Fusion-SQL's)
1

This AR Remittance Batch Management Summary query retrieves detailed information about remittance-type AR receipt batches in Oracle Receivables. It includes batch status, remittance method, bank details and so on.

#sqlquery

SELECT
  --SQL4Fusion (An Orbit Analytics Project)
  --This query retrieves detailed information about remittance-type receipt batches
  HAOU.NAME ORGANIZATION_NAME,
  ABA.NAME BATCH_NAME,
  ABA.BATCH_DATE,
  ABA.BATCH_APPLIED_STATUS,
  AL.MEANING BATCH_STATUS,
  ABA.REMIT_METHOD_CODE REMITTANCE_METHOD_CODE, 
  ABA.BANK_DEPOSIT_NUMBER DEPOSIT_NUMBER,
  CBBV.BANK_NAME,
  CBBV.BANK_BRANCH_NAME,
  CBA.BANK_ACCOUNT_NAME REMITTANCE_BANK_ACCOUNT,
  AL2.MEANING REMITTANCE_METHOD,
  ARM.NAME RECEIPT_METHOD,
  ABA.CONTROL_AMOUNT BATCH_CONTROL_AMOUNT,
  ABA.GL_DATE,
  ABA.CURRENCY_CODE,
  CBBV.BANK_INSTITUTION_TYPE
FROM
  AR_BATCHES_ALL ABA,
  AR_RECEIPT_METHODS ARM,
  AR_LOOKUPS AL,
  CE_BANK_BRANCHES_V CBBV,
  CE_BANK_ACCT_USES_ALL CBAUA,
  CE_BANK_ACCOUNTS CBA,
  AR_LOOKUPS AL2,
  HR_ALL_ORGANIZATION_UNITS HAOU
WHERE
  1 = 1
  AND ABA.RECEIPT_METHOD_ID = ARM.RECEIPT_METHOD_ID(+)
  AND AL.LOOKUP_TYPE(+) = 'BATCH_APPLIED_STATUS'
  AND AL.LOOKUP_CODE(+) = ABA.BATCH_APPLIED_STATUS
  AND AL2.LOOKUP_CODE(+) = ABA.REMIT_METHOD_CODE
  AND AL2.LOOKUP_TYPE(+) = 'REMITTANCE_METHOD'
  AND ABA.TYPE = 'REMITTANCE'
  AND CBAUA.BANK_ACCT_USE_ID = ABA.REMIT_BANK_ACCT_USE_ID
  AND CBAUA.BANK_ACCOUNT_ID = CBA.BANK_ACCOUNT_ID
  AND CBBV.BRANCH_PARTY_ID = CBA.BANK_BRANCH_ID
  AND HAOU.ORGANIZATION_ID = ABA.ORG_ID