AR Bank Remittance Batch Management (Oracle Fusion SQL)

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

This AR Bank Remittance Batch Management query retrieves bills receivable remittance batch details. It includes information such as the batch name, status, remittance method, GL date, recourse flag, receipt method.

#sqlquery

SELECT
  --SQL4Fusion (An Orbit Analytics Project)
  --This query retrieves bills receivable remittance batch details
  HAOU.NAME ORGANIZATION_NAME,
  ABA.NAME BATCH_NAME,
  ABA.BATCH_DATE,
  AL1.MEANING BATCH_STATUS,
  ABA.BANK_DEPOSIT_NUMBER,
  ABA.GL_DATE,
  DECODE(ABA.WITH_RECOURSE_FLAG,'Y','Yes', 'N','No','WITH_RECOURSE_FLAG') RECOURSE,
  AL2.MEANING REMIT_METHOD_NAME,
  CBA.BANK_ACCOUNT_NAME REMIT_BANK_ACCOUNT,
  CBBV.BANK_NAME,
  CBBV.BANK_BRANCH_NAME,
  CBBV.BANK_INSTITUTION_TYPE
FROM
  AR_BATCHES_ALL ABA,
  AR_RECEIPT_METHODS ARM,
  AR_LOOKUPS AL1,
  AR_LOOKUPS AL2,
  CE_BANK_ACCT_USES_ALL CBAU,
  CE_BANK_ACCOUNTS CBA,
  CE_BANK_BRANCHES_V CBBV,
  HR_ALL_ORGANIZATION_UNITS HAOU
WHERE
  1 = 1
  AND ABA.RECEIPT_METHOD_ID=ARM.RECEIPT_METHOD_ID(+) 
  AND AL1.LOOKUP_CODE= ABA.BATCH_APPLIED_STATUS
  AND AL1.LOOKUP_TYPE = 'BATCH_APPLIED_STATUS'
  AND AL2.LOOKUP_CODE= ABA.REMIT_METHOD_CODE
  AND AL2.LOOKUP_TYPE= 'REMITTANCE_METHOD'
  AND ABA.REMIT_BANK_ACCT_USE_ID = CBAU.BANK_ACCT_USE_ID 
  AND CBAU.BANK_ACCOUNT_ID = CBA.BANK_ACCOUNT_ID
  AND CBBV.BRANCH_PARTY_ID = CBA.BANK_BRANCH_ID
  AND HAOU.ORGANIZATION_ID = CBAU.ORG_ID
  AND ABA.TYPE = 'REMITTANCE'