VIEW: APPS.AR_CUSTOMER_BILLS_TRX_V

Source

View Text - Preformatted

SELECT C.ROWID , BS.NAME , BS.BATCH_SOURCE_ID , BS.AUTO_TRX_NUMBERING_FLAG , /* Transaction Type */ CTT.NAME , CTT.CUST_TRX_TYPE_ID , CTT.SIGNED_FLAG , CTT.DRAWEE_ISSUED_FLAG , CTT.MAGNETIC_FORMAT_CODE , CTT.POST_TO_GL , CTT.DEFAULT_PRINTING_OPTION , CTT.FORMAT_PROGRAM_ID , /* Customer TRX */ C.CUSTOMER_TRX_ID , C.PRINTING_LAST_PRINTED , C.CUSTOMER_REFERENCE , C.DOC_SEQUENCE_VALUE , C.DOC_SEQUENCE_ID , C.COMMENTS , C.INVOICE_CURRENCY_CODE , C.TRX_DATE , C.BR_AMOUNT , C.BR_UNPAID_FLAG , C.BR_ON_HOLD_FLAG , C.TERM_DUE_DATE , C.TRX_NUMBER , C.OLD_TRX_NUMBER , C.SPECIAL_INSTRUCTIONS , C.printing_option , C.COMPLETE_FLAG , c.OVERRIDE_REMIT_ACCOUNT_FLAG , c.exchange_rate , c.exchange_rate_type , c.exchange_date , c.attribute_category , c.attribute1 , c.attribute2 , c.attribute3 , c.attribute4 , c.attribute5 , c.attribute6 , c.attribute7 , c.attribute8 , c.attribute9 , c.attribute10 , c.attribute11 , c.attribute12 , c.attribute13 , c.attribute14 , c.attribute15 , c.created_from , /* Transaction Batch */ c.batch_id , /* Contact */ substrb(party_cont.person_LAST_NAME,1,50) || ', ' || substrb(party_cont.PERSON_FIRST_NAME,1,40), ACCT_ROLE.CUST_ACCOUNT_ROLE_ID, /* Drawee */ C.DRAWEE_ID , SUBSTRB(PARTY.PARTY_NAME,1,50) , CUST_ACCT.ACCOUNT_NUMBER , PARTY.JGZZ_FISCAL_CODE , PARTY.TAX_REFERENCE , CUST_ACCT.CUSTOMER_CLASS_CODE , PARTY.CATEGORY_CODE , CCAT.MEANING , /* Site */ SU.LOCATION , C.DRAWEE_SITE_USE_ID , acct_site.cust_acct_site_id , loc.ADDRESS1 , loc.ADDRESS2 , /* loc.ADDRESS3 || loc.CITY || loc.PROVINCE || loc.POSTAL_CODE || loc.STATE || ft.TERRITORY_code, */ DECODE(acct_site.cust_acct_site_id , NULL , NULL , ARH_ADDR_PKG.FORMAT_LAST_ADDRESS_LINE(loc.ADDRESS_STYLE, loc.ADDRESS3 , loc.ADDRESS4 , loc.CITY , loc.county , loc.STATE , loc.PROVINCE , ft.TERRITORY_SHORT_NAME , loc.POSTAL_CODE ) ) , loc.ADDRESS3 , loc.CITY , loc.POSTAL_CODE , loc.STATE , NVL(loc.STATE , loc.PROVINCE ) , FT.TERRITORY_CODE , SU.PRIMARY_FLAG , /* Drawee Bank Name */ BankParty.party_name , BranchOrgProfile.bank_or_branch_number, BankOrgProfile.bank_or_branch_number, BranchParty.party_name, ABA.BANK_ACCOUNT_NUM , ABA.BANk_ACCOUNT_NAME , ABA.currency_code , BranchParty.address1, BranchParty.address2, BranchParty.address3 || BranchParty.city || BranchParty.province || BranchParty.postal_code || BranchParty.state, BranchParty.address3, BranchParty.city, BranchParty.postal_code, BranchParty.province, BranchParty.state, NVL(BranchParty.province, BranchParty.state), BranchParty.country, NULL , /* Remittance Bank Account */ RemitBankParty.party_name, RemitBranchOrgProfile.bank_or_branch_number, RemitBankOrgProfile.bank_or_branch_number, RemitBranchParty.party_name, CBA.BANK_ACCOUNT_NUM , CBA.BANk_ACCOUNT_NAME , CBA.currency_code , RemitBranchParty.address1, RemitBranchParty.address2, RemitBranchParty.address3 || RemitBranchParty.city || RemitBranchParty.province || RemitBranchParty.postal_code || RemitBranchParty.state, RemitBranchParty.address3, RemitBranchParty.city, RemitBranchParty.postal_code, RemitBranchParty.province, RemitBranchParty.state, NVL(RemitBranchParty.province, RemitBranchParty.state), RemitBranchParty.country, RABA.BANK_ACCOUNT_ID , C.REMIT_BANK_ACCT_USE_ID , rah.status , ars.meaning , ARPT_SQL_FUNC_UTIL.get_orig_gl_date(C.CUSTOMER_TRX_ID), rah.posting_control_id , C.remittance_batch_id, C.CREATED_BY, C.CREATION_DATE, C.LAST_UPDATED_BY, C.LAST_UPDATE_DATE, C.LAST_UPDATE_LOGIN, C.ORG_ID, C.LEGAL_ENTITY_ID, C.PAYMENT_TRXN_EXTENSION_ID FROM RA_BATCH_SOURCES_ALL BS , RA_CUST_TRX_TYPES_ALL CTT , RA_CUSTOMER_TRX C , HZ_CUST_ACCT_SITES ACCT_SITE , HZ_PARTY_SITES PARTY_SITE , HZ_LOCATIONS LOC , HZ_CUST_ACCOUNT_ROLES ACCT_ROLE, HZ_PARTIES PARTY_CONT , HZ_RELATIONSHIPS REL , HZ_CUST_SITE_USES su , FND_TERRITORIES_VL ft , IBY_EXT_BANK_ACCOUNTS ABA , HZ_CUST_ACCOUNTS CUST_ACCT , HZ_PARTIES PARTY , AR_LOOKUPS ARs , AR_LOOKUPS CCAT , AR_TRANSACTION_HISTORY RAH , CE_BANK_ACCOUNTS CBA, CE_BANK_ACCT_USES_ou_v RABA, HZ_PARTIES BranchParty, HZ_PARTIES BankParty, HZ_RELATIONSHIPS BRRel, HZ_ORGANIZATION_PROFILES BankOrgProfile, HZ_ORGANIZATION_PROFILES BranchOrgProfile, HZ_PARTIES RemitBranchParty, HZ_PARTIES RemitBankParty, HZ_RELATIONSHIPS RemitBRRel, HZ_ORGANIZATION_PROFILES RemitBankOrgProfile, HZ_ORGANIZATION_PROFILES RemitBranchOrgProfile, iby_trxn_extensions_v Iby WHERE c.batch_source_id = bs.batch_source_id and c.org_id = bs.org_id and c.cust_trx_type_id = ctt.cust_trx_type_id and c.org_id = ctt.org_id and c.drawee_site_use_id = su.site_use_id (+) and c.org_id = su.org_id (+) and su.cust_acct_site_id = acct_site.cust_acct_site_id (+) and su.org_id = ACCT_SITE.org_id (+) and su.org_id = acct_site.org_id (+) and acct_site.party_site_id = party_site.party_site_id(+) and loc.location_id(+) = party_site.location_id and c.payment_trxn_extension_id = iby.trxn_extension_id (+) and c.drawee_id = cust_acct.cust_account_id and cust_acct.party_id = party.party_id and c.remit_bank_acct_use_id = raba.bank_acct_use_id (+) and c.org_id = raba.org_id (+) and c.drawee_contact_id = acct_role.cust_account_role_id (+) and acct_role.party_id = rel.party_id(+) and rel.subject_table_name(+) = 'HZ_PARTIES' and rel.object_table_name(+) = 'HZ_PARTIES' and rel.directional_flag(+) = 'F' and acct_role.role_type(+) = 'CONTACT' and rel.subject_id = party_cont.party_id(+) and loc.country = ft.territory_code (+) and rah.customer_trx_id = c.customer_trx_id and rah.org_id = c.org_id and rah.current_record_flag = 'Y' and ars.lookup_code = rah.status and ars.lookup_type = 'TRANSACTION_HISTORY_STATUS' and ccat.lookup_type (+) = 'CUSTOMER_CATEGORY' and ccat.lookup_code (+) = party.category_code AND BranchParty.party_id (+) = ABA.branch_id AND BRRel.object_id = BankParty.party_id (+) AND BRRel.subject_id (+) = BranchParty.party_id AND BRRel.relationship_type (+) = 'BANK_AND_BRANCH' AND BRRel.relationship_code (+) = 'BRANCH_OF' AND BRRel.subject_table_name (+) = 'HZ_PARTIES' AND BRRel.subject_type (+) = 'ORGANIZATION' AND BRRel.object_table_name (+) = 'HZ_PARTIES' AND BRRel.object_type (+) = 'ORGANIZATION' AND RemitBranchParty.party_id (+) = CBA.bank_branch_id AND RemitBRRel.object_id = RemitBankParty.party_id (+) AND RemitBRRel.subject_id (+) = RemitBranchParty.party_id AND RemitBRRel.relationship_type (+) = 'BANK_AND_BRANCH' AND RemitBRRel.relationship_code (+) = 'BRANCH_OF' AND RemitBRRel.subject_table_name (+) = 'HZ_PARTIES' AND RemitBRRel.subject_type (+) = 'ORGANIZATION' AND RemitBRRel.object_table_name (+) = 'HZ_PARTIES' AND RemitBRRel.object_type (+) = 'ORGANIZATION' AND BankOrgProfile.PARTY_ID (+) = BankParty.PARTY_ID AND BranchOrgProfile.PARTY_ID (+) = BranchParty.PARTY_ID AND SYSDATE BETWEEN NVL(TRUNC(BankOrgProfile.effective_start_date), SYSDATE-1) AND NVL(TRUNC(BankOrgProfile.effective_end_date), SYSDATE+1) AND SYSDATE BETWEEN NVL(TRUNC(BranchOrgProfile.effective_start_date), SYSDATE-1) AND NVL(TRUNC(BranchOrgProfile.effective_end_date), SYSDATE+1) AND RemitBankOrgProfile.PARTY_ID (+) = RemitBankParty.PARTY_ID AND RemitBranchOrgProfile.PARTY_ID (+) = RemitBranchParty.PARTY_ID AND SYSDATE BETWEEN NVL(TRUNC(RemitBankOrgProfile.effective_start_date), SYSDATE-1) AND NVL(TRUNC(RemitBankOrgProfile.effective_end_date), SYSDATE+1) AND SYSDATE BETWEEN NVL(TRUNC(RemitBranchOrgProfile.effective_start_date), SYSDATE-1) AND NVL(TRUNC(RemitBranchOrgProfile.effective_end_date), SYSDATE+1) AND RABA.bank_account_id = CBA.bank_account_id (+) AND DECODE(iby.instrument_type, 'BANKACCOUNT',iby.instrument_id, NULL) = ABA.ext_bank_account_id(+)

View Text - HTML Formatted

SELECT C.ROWID
, BS.NAME
, BS.BATCH_SOURCE_ID
, BS.AUTO_TRX_NUMBERING_FLAG
, /* TRANSACTION TYPE */ CTT.NAME
, CTT.CUST_TRX_TYPE_ID
, CTT.SIGNED_FLAG
, CTT.DRAWEE_ISSUED_FLAG
, CTT.MAGNETIC_FORMAT_CODE
, CTT.POST_TO_GL
, CTT.DEFAULT_PRINTING_OPTION
, CTT.FORMAT_PROGRAM_ID
, /* CUSTOMER TRX */ C.CUSTOMER_TRX_ID
, C.PRINTING_LAST_PRINTED
, C.CUSTOMER_REFERENCE
, C.DOC_SEQUENCE_VALUE
, C.DOC_SEQUENCE_ID
, C.COMMENTS
, C.INVOICE_CURRENCY_CODE
, C.TRX_DATE
, C.BR_AMOUNT
, C.BR_UNPAID_FLAG
, C.BR_ON_HOLD_FLAG
, C.TERM_DUE_DATE
, C.TRX_NUMBER
, C.OLD_TRX_NUMBER
, C.SPECIAL_INSTRUCTIONS
, C.PRINTING_OPTION
, C.COMPLETE_FLAG
, C.OVERRIDE_REMIT_ACCOUNT_FLAG
, C.EXCHANGE_RATE
, C.EXCHANGE_RATE_TYPE
, C.EXCHANGE_DATE
, C.ATTRIBUTE_CATEGORY
, C.ATTRIBUTE1
, C.ATTRIBUTE2
, C.ATTRIBUTE3
, C.ATTRIBUTE4
, C.ATTRIBUTE5
, C.ATTRIBUTE6
, C.ATTRIBUTE7
, C.ATTRIBUTE8
, C.ATTRIBUTE9
, C.ATTRIBUTE10
, C.ATTRIBUTE11
, C.ATTRIBUTE12
, C.ATTRIBUTE13
, C.ATTRIBUTE14
, C.ATTRIBUTE15
, C.CREATED_FROM
, /* TRANSACTION BATCH */ C.BATCH_ID
, /* CONTACT */ SUBSTRB(PARTY_CONT.PERSON_LAST_NAME
, 1
, 50) || '
, ' || SUBSTRB(PARTY_CONT.PERSON_FIRST_NAME
, 1
, 40)
, ACCT_ROLE.CUST_ACCOUNT_ROLE_ID
, /* DRAWEE */ C.DRAWEE_ID
, SUBSTRB(PARTY.PARTY_NAME
, 1
, 50)
, CUST_ACCT.ACCOUNT_NUMBER
, PARTY.JGZZ_FISCAL_CODE
, PARTY.TAX_REFERENCE
, CUST_ACCT.CUSTOMER_CLASS_CODE
, PARTY.CATEGORY_CODE
, CCAT.MEANING
, /* SITE */ SU.LOCATION
, C.DRAWEE_SITE_USE_ID
, ACCT_SITE.CUST_ACCT_SITE_ID
, LOC.ADDRESS1
, LOC.ADDRESS2
, /* LOC.ADDRESS3 || LOC.CITY || LOC.PROVINCE || LOC.POSTAL_CODE || LOC.STATE || FT.TERRITORY_CODE
, */ DECODE(ACCT_SITE.CUST_ACCT_SITE_ID
, NULL
, NULL
, ARH_ADDR_PKG.FORMAT_LAST_ADDRESS_LINE(LOC.ADDRESS_STYLE
, LOC.ADDRESS3
, LOC.ADDRESS4
, LOC.CITY
, LOC.COUNTY
, LOC.STATE
, LOC.PROVINCE
, FT.TERRITORY_SHORT_NAME
, LOC.POSTAL_CODE ) )
, LOC.ADDRESS3
, LOC.CITY
, LOC.POSTAL_CODE
, LOC.STATE
, NVL(LOC.STATE
, LOC.PROVINCE )
, FT.TERRITORY_CODE
, SU.PRIMARY_FLAG
, /* DRAWEE BANK NAME */ BANKPARTY.PARTY_NAME
, BRANCHORGPROFILE.BANK_OR_BRANCH_NUMBER
, BANKORGPROFILE.BANK_OR_BRANCH_NUMBER
, BRANCHPARTY.PARTY_NAME
, ABA.BANK_ACCOUNT_NUM
, ABA.BANK_ACCOUNT_NAME
, ABA.CURRENCY_CODE
, BRANCHPARTY.ADDRESS1
, BRANCHPARTY.ADDRESS2
, BRANCHPARTY.ADDRESS3 || BRANCHPARTY.CITY || BRANCHPARTY.PROVINCE || BRANCHPARTY.POSTAL_CODE || BRANCHPARTY.STATE
, BRANCHPARTY.ADDRESS3
, BRANCHPARTY.CITY
, BRANCHPARTY.POSTAL_CODE
, BRANCHPARTY.PROVINCE
, BRANCHPARTY.STATE
, NVL(BRANCHPARTY.PROVINCE
, BRANCHPARTY.STATE)
, BRANCHPARTY.COUNTRY
, NULL
, /* REMITTANCE BANK ACCOUNT */ REMITBANKPARTY.PARTY_NAME
, REMITBRANCHORGPROFILE.BANK_OR_BRANCH_NUMBER
, REMITBANKORGPROFILE.BANK_OR_BRANCH_NUMBER
, REMITBRANCHPARTY.PARTY_NAME
, CBA.BANK_ACCOUNT_NUM
, CBA.BANK_ACCOUNT_NAME
, CBA.CURRENCY_CODE
, REMITBRANCHPARTY.ADDRESS1
, REMITBRANCHPARTY.ADDRESS2
, REMITBRANCHPARTY.ADDRESS3 || REMITBRANCHPARTY.CITY || REMITBRANCHPARTY.PROVINCE || REMITBRANCHPARTY.POSTAL_CODE || REMITBRANCHPARTY.STATE
, REMITBRANCHPARTY.ADDRESS3
, REMITBRANCHPARTY.CITY
, REMITBRANCHPARTY.POSTAL_CODE
, REMITBRANCHPARTY.PROVINCE
, REMITBRANCHPARTY.STATE
, NVL(REMITBRANCHPARTY.PROVINCE
, REMITBRANCHPARTY.STATE)
, REMITBRANCHPARTY.COUNTRY
, RABA.BANK_ACCOUNT_ID
, C.REMIT_BANK_ACCT_USE_ID
, RAH.STATUS
, ARS.MEANING
, ARPT_SQL_FUNC_UTIL.GET_ORIG_GL_DATE(C.CUSTOMER_TRX_ID)
, RAH.POSTING_CONTROL_ID
, C.REMITTANCE_BATCH_ID
, C.CREATED_BY
, C.CREATION_DATE
, C.LAST_UPDATED_BY
, C.LAST_UPDATE_DATE
, C.LAST_UPDATE_LOGIN
, C.ORG_ID
, C.LEGAL_ENTITY_ID
, C.PAYMENT_TRXN_EXTENSION_ID
FROM RA_BATCH_SOURCES_ALL BS
, RA_CUST_TRX_TYPES_ALL CTT
, RA_CUSTOMER_TRX C
, HZ_CUST_ACCT_SITES ACCT_SITE
, HZ_PARTY_SITES PARTY_SITE
, HZ_LOCATIONS LOC
, HZ_CUST_ACCOUNT_ROLES ACCT_ROLE
, HZ_PARTIES PARTY_CONT
, HZ_RELATIONSHIPS REL
, HZ_CUST_SITE_USES SU
, FND_TERRITORIES_VL FT
, IBY_EXT_BANK_ACCOUNTS ABA
, HZ_CUST_ACCOUNTS CUST_ACCT
, HZ_PARTIES PARTY
, AR_LOOKUPS ARS
, AR_LOOKUPS CCAT
, AR_TRANSACTION_HISTORY RAH
, CE_BANK_ACCOUNTS CBA
, CE_BANK_ACCT_USES_OU_V RABA
, HZ_PARTIES BRANCHPARTY
, HZ_PARTIES BANKPARTY
, HZ_RELATIONSHIPS BRREL
, HZ_ORGANIZATION_PROFILES BANKORGPROFILE
, HZ_ORGANIZATION_PROFILES BRANCHORGPROFILE
, HZ_PARTIES REMITBRANCHPARTY
, HZ_PARTIES REMITBANKPARTY
, HZ_RELATIONSHIPS REMITBRREL
, HZ_ORGANIZATION_PROFILES REMITBANKORGPROFILE
, HZ_ORGANIZATION_PROFILES REMITBRANCHORGPROFILE
, IBY_TRXN_EXTENSIONS_V IBY
WHERE C.BATCH_SOURCE_ID = BS.BATCH_SOURCE_ID
AND C.ORG_ID = BS.ORG_ID
AND C.CUST_TRX_TYPE_ID = CTT.CUST_TRX_TYPE_ID
AND C.ORG_ID = CTT.ORG_ID
AND C.DRAWEE_SITE_USE_ID = SU.SITE_USE_ID (+)
AND C.ORG_ID = SU.ORG_ID (+)
AND SU.CUST_ACCT_SITE_ID = ACCT_SITE.CUST_ACCT_SITE_ID (+)
AND SU.ORG_ID = ACCT_SITE.ORG_ID (+)
AND SU.ORG_ID = ACCT_SITE.ORG_ID (+)
AND ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID(+)
AND LOC.LOCATION_ID(+) = PARTY_SITE.LOCATION_ID
AND C.PAYMENT_TRXN_EXTENSION_ID = IBY.TRXN_EXTENSION_ID (+)
AND C.DRAWEE_ID = CUST_ACCT.CUST_ACCOUNT_ID
AND CUST_ACCT.PARTY_ID = PARTY.PARTY_ID
AND C.REMIT_BANK_ACCT_USE_ID = RABA.BANK_ACCT_USE_ID (+)
AND C.ORG_ID = RABA.ORG_ID (+)
AND C.DRAWEE_CONTACT_ID = ACCT_ROLE.CUST_ACCOUNT_ROLE_ID (+)
AND ACCT_ROLE.PARTY_ID = REL.PARTY_ID(+)
AND REL.SUBJECT_TABLE_NAME(+) = 'HZ_PARTIES'
AND REL.OBJECT_TABLE_NAME(+) = 'HZ_PARTIES'
AND REL.DIRECTIONAL_FLAG(+) = 'F'
AND ACCT_ROLE.ROLE_TYPE(+) = 'CONTACT'
AND REL.SUBJECT_ID = PARTY_CONT.PARTY_ID(+)
AND LOC.COUNTRY = FT.TERRITORY_CODE (+)
AND RAH.CUSTOMER_TRX_ID = C.CUSTOMER_TRX_ID
AND RAH.ORG_ID = C.ORG_ID
AND RAH.CURRENT_RECORD_FLAG = 'Y'
AND ARS.LOOKUP_CODE = RAH.STATUS
AND ARS.LOOKUP_TYPE = 'TRANSACTION_HISTORY_STATUS'
AND CCAT.LOOKUP_TYPE (+) = 'CUSTOMER_CATEGORY'
AND CCAT.LOOKUP_CODE (+) = PARTY.CATEGORY_CODE
AND BRANCHPARTY.PARTY_ID (+) = ABA.BRANCH_ID
AND BRREL.OBJECT_ID = BANKPARTY.PARTY_ID (+)
AND BRREL.SUBJECT_ID (+) = BRANCHPARTY.PARTY_ID
AND BRREL.RELATIONSHIP_TYPE (+) = 'BANK_AND_BRANCH'
AND BRREL.RELATIONSHIP_CODE (+) = 'BRANCH_OF'
AND BRREL.SUBJECT_TABLE_NAME (+) = 'HZ_PARTIES'
AND BRREL.SUBJECT_TYPE (+) = 'ORGANIZATION'
AND BRREL.OBJECT_TABLE_NAME (+) = 'HZ_PARTIES'
AND BRREL.OBJECT_TYPE (+) = 'ORGANIZATION'
AND REMITBRANCHPARTY.PARTY_ID (+) = CBA.BANK_BRANCH_ID
AND REMITBRREL.OBJECT_ID = REMITBANKPARTY.PARTY_ID (+)
AND REMITBRREL.SUBJECT_ID (+) = REMITBRANCHPARTY.PARTY_ID
AND REMITBRREL.RELATIONSHIP_TYPE (+) = 'BANK_AND_BRANCH'
AND REMITBRREL.RELATIONSHIP_CODE (+) = 'BRANCH_OF'
AND REMITBRREL.SUBJECT_TABLE_NAME (+) = 'HZ_PARTIES'
AND REMITBRREL.SUBJECT_TYPE (+) = 'ORGANIZATION'
AND REMITBRREL.OBJECT_TABLE_NAME (+) = 'HZ_PARTIES'
AND REMITBRREL.OBJECT_TYPE (+) = 'ORGANIZATION'
AND BANKORGPROFILE.PARTY_ID (+) = BANKPARTY.PARTY_ID
AND BRANCHORGPROFILE.PARTY_ID (+) = BRANCHPARTY.PARTY_ID
AND SYSDATE BETWEEN NVL(TRUNC(BANKORGPROFILE.EFFECTIVE_START_DATE)
, SYSDATE-1)
AND NVL(TRUNC(BANKORGPROFILE.EFFECTIVE_END_DATE)
, SYSDATE+1)
AND SYSDATE BETWEEN NVL(TRUNC(BRANCHORGPROFILE.EFFECTIVE_START_DATE)
, SYSDATE-1)
AND NVL(TRUNC(BRANCHORGPROFILE.EFFECTIVE_END_DATE)
, SYSDATE+1)
AND REMITBANKORGPROFILE.PARTY_ID (+) = REMITBANKPARTY.PARTY_ID
AND REMITBRANCHORGPROFILE.PARTY_ID (+) = REMITBRANCHPARTY.PARTY_ID
AND SYSDATE BETWEEN NVL(TRUNC(REMITBANKORGPROFILE.EFFECTIVE_START_DATE)
, SYSDATE-1)
AND NVL(TRUNC(REMITBANKORGPROFILE.EFFECTIVE_END_DATE)
, SYSDATE+1)
AND SYSDATE BETWEEN NVL(TRUNC(REMITBRANCHORGPROFILE.EFFECTIVE_START_DATE)
, SYSDATE-1)
AND NVL(TRUNC(REMITBRANCHORGPROFILE.EFFECTIVE_END_DATE)
, SYSDATE+1)
AND RABA.BANK_ACCOUNT_ID = CBA.BANK_ACCOUNT_ID (+)
AND DECODE(IBY.INSTRUMENT_TYPE
, 'BANKACCOUNT'
, IBY.INSTRUMENT_ID
, NULL) = ABA.EXT_BANK_ACCOUNT_ID(+)