VIEW: APPS.AR_BR_MAIN_REMIT_BATCH_V

Source

View Text - Preformatted

SELECT C.ROWID , /* Remittance */ BA.BATCH_ID , /* Transaction Type */ CTT.NAME , PS.CUST_TRX_TYPE_ID , CTT.SIGNED_FLAG , CTT.DRAWEE_ISSUED_FLAG , /* Customer TRX */ PS.PAYMENT_SCHEDULE_ID , C.CUSTOMER_TRX_ID , C.PRINTING_LAST_PRINTED , C.COMMENTS , PS.INVOICE_CURRENCY_CODE, PS.TRX_DATE , PS.AMOUNT_DUE_REMAINING , PS.ACCTD_AMOUNT_DUE_REMAINING, PS.DUE_DATE , PS.TRX_NUMBER , C.SPECIAL_INSTRUCTIONS , PS.ATTRIBUTE_CATEGORY , PS.ATTRIBUTE1 , PS.ATTRIBUTE2 , PS.ATTRIBUTE3 , PS.ATTRIBUTE4 , PS.ATTRIBUTE5 , PS.ATTRIBUTE6 , PS.ATTRIBUTE7 , PS.ATTRIBUTE8 , PS.ATTRIBUTE9 , PS.ATTRIBUTE10 , PS.ATTRIBUTE11 , PS.ATTRIBUTE12 , PS.ATTRIBUTE13 , PS.ATTRIBUTE14 , PS.ATTRIBUTE15 , /* Drawee */ SUBSTRB(PARTY.PARTY_NAME,1,50), CUST_ACCT.ACCOUNT_NUMBER, PARTY.JGZZ_FISCAL_CODE , CUST_ACCT.CUSTOMER_CLASS_CODE , PARTY.CATEGORY_CODE, PC.NAME , /* Drawee Site */ LOC.ADDRESS1 || LOC.ADDRESS2 || LOC.ADDRESS3, LOC.CITY , LOC.POSTAL_CODE , LOC.STATE , LOC.COUNTRY , /* Drawee Bank */ IBY.BANK_NAME DRAWEE_BANK_NAME , IBY.BANK_NUMBER DRAWEE_BANK_NUMBER , /* Drawee Branch */ IBY.BANK_BRANCH_NAME DRAWEE_BRANCH_NAME , NULL DRAWEE_BRANCH_CITY , NULL DRAWEE_BRANCH_STATE , NULL DRAWEE_BRANCH_COUNTRY , NULL DRAWEE_BRANCH_POSTAL_CODE , NULL DRAWEE_BRANCH_NUMBER , /* Remittance Bank */ RemitBankParty.party_name, /* Standard Who Columns */ C.CREATED_BY , C.CREATION_DATE , C.LAST_UPDATED_BY , C.LAST_UPDATE_DATE , C.LAST_UPDATE_LOGIN FROM AR_PAYMENT_SCHEDULES PS, AR_TRANSACTION_HISTORY RAH, HZ_CUST_ACCOUNTS CUST_ACCT, HZ_PARTIES PARTY, RA_CUST_TRX_TYPES CTT, RA_CUSTOMER_TRX C, HZ_CUSTOMER_PROFILES CP, HZ_CUST_PROFILE_CLASSES PC, HZ_CUST_ACCT_SITES ACCT_SITE, HZ_PARTY_SITES PARTY_SITE, HZ_LOCATIONS LOC, HZ_CUST_SITE_USES SU , IBY_TRXN_EXTENSIONS_V IBY , AR_BATCHES BA , AR_LOOKUPS ARS , CE_BANK_ACCOUNTS CBA, CE_BANK_ACCT_USES_ou_v RABA, HZ_PARTIES RemitBranchParty, HZ_PARTIES RemitBankParty, HZ_RELATIONSHIPS RemitBRRel, HZ_ORGANIZATION_PROFILES RemitBankOrgProfile, HZ_ORGANIZATION_PROFILES RemitBranchOrgProfile WHERE BA.batch_id = PS.reserved_value AND PS.reserved_type = 'REMITTANCE' AND RAH.current_record_flag = 'Y' AND RAH.customer_trx_id = c.customer_trx_id AND c.drawee_id = CUST_ACCT.cust_account_id AND CUST_ACCT.party_id = PARTY.party_id AND RAH.customer_trx_id = PS.customer_trx_id AND PS.cust_trx_type_id = ctt.cust_trx_type_id and cust_acct.cust_account_id = cp.cust_account_id and cp.profile_class_id = pc.profile_class_id and cp.site_use_id is NULL and c.drawee_site_use_id = su.site_use_id and su.cust_acct_site_id = acct_site.cust_acct_site_id and acct_site.party_site_id = party_site.party_site_id and loc.location_id = party_site.location_id and cust_acct.cust_account_id=acct_site.cust_account_id and iby.trxn_extension_id(+) = c.payment_trxn_extension_id and c.remit_bank_acct_use_id = raba.bank_acct_use_id(+) and rah.status = ars.lookup_code and ars.lookup_type = 'TRANSACTION_HISTORY_STATUS' and ctt.signed_flag IN ('Y','N') and ctt.drawee_issued_flag IN ('Y','N') 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 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 (+) UNION SELECT C.ROWID , /* Remittance */ BA.BATCH_ID , /* Transaction Type */ CTT.NAME , PS.CUST_TRX_TYPE_ID , CTT.SIGNED_FLAG , CTT.DRAWEE_ISSUED_FLAG , /* Customer TRX */ PS.PAYMENT_SCHEDULE_ID , C.CUSTOMER_TRX_ID , C.PRINTING_LAST_PRINTED , C.COMMENTS , PS.INVOICE_CURRENCY_CODE, PS.TRX_DATE , ARP_BR_REMIT_FUNCTION.GET_AMOUNT(rah.transaction_history_id,rah.status,c.customer_trx_id), ARP_BR_REMIT_FUNCTION.GET_ACCTD_AMOUNT(rah.transaction_history_id,rah.status,c.customer_trx_id), PS.DUE_DATE , PS.TRX_NUMBER , C.SPECIAL_INSTRUCTIONS , PS.ATTRIBUTE_CATEGORY , PS.ATTRIBUTE1 , PS.ATTRIBUTE2 , PS.ATTRIBUTE3 , PS.ATTRIBUTE4 , PS.ATTRIBUTE5 , PS.ATTRIBUTE6 , PS.ATTRIBUTE7 , PS.ATTRIBUTE8 , PS.ATTRIBUTE9 , PS.ATTRIBUTE10 , PS.ATTRIBUTE11 , PS.ATTRIBUTE12 , PS.ATTRIBUTE13 , PS.ATTRIBUTE14 , PS.ATTRIBUTE15 , /* Drawee */ SUBSTRB(PARTY.PARTY_NAME,1,50), CUST_ACCT.ACCOUNT_NUMBER, PARTY.JGZZ_FISCAL_CODE , CUST_ACCT.CUSTOMER_CLASS_CODE , PARTY.CATEGORY_CODE, PC.NAME , /* Drawee Site */ LOC.ADDRESS1 || LOC.ADDRESS2 || LOC.ADDRESS3, LOC.CITY , LOC.POSTAL_CODE , LOC.STATE , LOC.COUNTRY , /* Drawee Bank */ IBY.BANK_NAME DRAWEE_BANK_NAME , IBY.BANK_NUMBER DRAWEE_BANK_NUMBER , /* Drawee Branch */ IBY.BANK_BRANCH_NAME DRAWEE_BRANCH_NAME , NULL DRAWEE_BRANCH_CITY , NULL DRAWEE_BRANCH_STATE , NULL DRAWEE_BRANCH_COUNTRY , NULL DRAWEE_BRANCH_POSTAL_CODE , NULL DRAWEE_BRANCH_NUMBER , /* Remittance Bank */ RemitBankParty.party_name, /* Standard Who Columns */ C.CREATED_BY , C.CREATION_DATE , C.LAST_UPDATED_BY , C.LAST_UPDATE_DATE , C.LAST_UPDATE_LOGIN FROM AR_PAYMENT_SCHEDULES PS, AR_TRANSACTION_HISTORY RAH, HZ_CUST_ACCOUNTS CUST_ACCT, HZ_PARTIES PARTY, RA_CUST_TRX_TYPES CTT, RA_CUSTOMER_TRX C, HZ_CUSTOMER_PROFILES CP, HZ_CUST_PROFILE_CLASSES PC, HZ_CUST_ACCT_SITES ACCT_SITE, HZ_PARTY_SITES PARTY_SITE, HZ_LOCATIONS LOC, HZ_CUST_SITE_USES SU , IBY_TRXN_EXTENSIONS_V IBY , AR_BATCHES BA , AR_LOOKUPS ARS, CE_BANK_ACCOUNTS CBA, CE_BANK_ACCT_USES_ou_v RABA, HZ_PARTIES RemitBranchParty, HZ_PARTIES RemitBankParty, HZ_RELATIONSHIPS RemitBRRel, HZ_ORGANIZATION_PROFILES RemitBankOrgProfile, HZ_ORGANIZATION_PROFILES RemitBranchOrgProfile WHERE ba.batch_id = rah.batch_id AND ((ba.remit_method_code = 'STANDARD' AND rah.event = 'REMITTED') OR (ba.remit_method_code = 'FACTORING' AND ba.with_recourse_flag = 'Y' AND RAH.event = 'FACTORED') OR (ba.remit_method_code = 'FACTORING' AND ba.with_recourse_flag = 'N' AND RAH.event = 'CLOSED')) AND RAH.customer_trx_id = c.customer_trx_id AND c.drawee_id = CUST_ACCT.cust_account_id AND CUST_ACCT.party_id = PARTY.party_id AND RAH.customer_trx_id = PS.customer_trx_id AND PS.cust_trx_type_id = ctt.cust_trx_type_id and cust_acct.cust_account_id = cp.cust_account_id and cp.profile_class_id = pc.profile_class_id and cp.site_use_id is NULL and c.drawee_site_use_id = su.site_use_id and su.cust_acct_site_id = acct_site.cust_acct_site_id and acct_site.party_site_id = party_site.party_site_id and loc.location_id = party_site.location_id and cust_acct.cust_account_id=acct_site.cust_account_id and iby.trxn_extension_id(+) = c.payment_trxn_extension_id and c.remit_bank_acct_use_id = raba.bank_acct_use_id(+) and rah.status = ars.lookup_code and ars.lookup_type = 'TRANSACTION_HISTORY_STATUS' and ctt.signed_flag IN ('Y','N') and ctt.drawee_issued_flag IN ('Y','N') 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 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 (+)

View Text - HTML Formatted

SELECT C.ROWID
, /* REMITTANCE */ BA.BATCH_ID
, /* TRANSACTION TYPE */ CTT.NAME
, PS.CUST_TRX_TYPE_ID
, CTT.SIGNED_FLAG
, CTT.DRAWEE_ISSUED_FLAG
, /* CUSTOMER TRX */ PS.PAYMENT_SCHEDULE_ID
, C.CUSTOMER_TRX_ID
, C.PRINTING_LAST_PRINTED
, C.COMMENTS
, PS.INVOICE_CURRENCY_CODE
, PS.TRX_DATE
, PS.AMOUNT_DUE_REMAINING
, PS.ACCTD_AMOUNT_DUE_REMAINING
, PS.DUE_DATE
, PS.TRX_NUMBER
, C.SPECIAL_INSTRUCTIONS
, PS.ATTRIBUTE_CATEGORY
, PS.ATTRIBUTE1
, PS.ATTRIBUTE2
, PS.ATTRIBUTE3
, PS.ATTRIBUTE4
, PS.ATTRIBUTE5
, PS.ATTRIBUTE6
, PS.ATTRIBUTE7
, PS.ATTRIBUTE8
, PS.ATTRIBUTE9
, PS.ATTRIBUTE10
, PS.ATTRIBUTE11
, PS.ATTRIBUTE12
, PS.ATTRIBUTE13
, PS.ATTRIBUTE14
, PS.ATTRIBUTE15
, /* DRAWEE */ SUBSTRB(PARTY.PARTY_NAME
, 1
, 50)
, CUST_ACCT.ACCOUNT_NUMBER
, PARTY.JGZZ_FISCAL_CODE
, CUST_ACCT.CUSTOMER_CLASS_CODE
, PARTY.CATEGORY_CODE
, PC.NAME
, /* DRAWEE SITE */ LOC.ADDRESS1 || LOC.ADDRESS2 || LOC.ADDRESS3
, LOC.CITY
, LOC.POSTAL_CODE
, LOC.STATE
, LOC.COUNTRY
, /* DRAWEE BANK */ IBY.BANK_NAME DRAWEE_BANK_NAME
, IBY.BANK_NUMBER DRAWEE_BANK_NUMBER
, /* DRAWEE BRANCH */ IBY.BANK_BRANCH_NAME DRAWEE_BRANCH_NAME
, NULL DRAWEE_BRANCH_CITY
, NULL DRAWEE_BRANCH_STATE
, NULL DRAWEE_BRANCH_COUNTRY
, NULL DRAWEE_BRANCH_POSTAL_CODE
, NULL DRAWEE_BRANCH_NUMBER
, /* REMITTANCE BANK */ REMITBANKPARTY.PARTY_NAME
, /* STANDARD WHO COLUMNS */ C.CREATED_BY
, C.CREATION_DATE
, C.LAST_UPDATED_BY
, C.LAST_UPDATE_DATE
, C.LAST_UPDATE_LOGIN
FROM AR_PAYMENT_SCHEDULES PS
, AR_TRANSACTION_HISTORY RAH
, HZ_CUST_ACCOUNTS CUST_ACCT
, HZ_PARTIES PARTY
, RA_CUST_TRX_TYPES CTT
, RA_CUSTOMER_TRX C
, HZ_CUSTOMER_PROFILES CP
, HZ_CUST_PROFILE_CLASSES PC
, HZ_CUST_ACCT_SITES ACCT_SITE
, HZ_PARTY_SITES PARTY_SITE
, HZ_LOCATIONS LOC
, HZ_CUST_SITE_USES SU
, IBY_TRXN_EXTENSIONS_V IBY
, AR_BATCHES BA
, AR_LOOKUPS ARS
, CE_BANK_ACCOUNTS CBA
, CE_BANK_ACCT_USES_OU_V RABA
, HZ_PARTIES REMITBRANCHPARTY
, HZ_PARTIES REMITBANKPARTY
, HZ_RELATIONSHIPS REMITBRREL
, HZ_ORGANIZATION_PROFILES REMITBANKORGPROFILE
, HZ_ORGANIZATION_PROFILES REMITBRANCHORGPROFILE
WHERE BA.BATCH_ID = PS.RESERVED_VALUE
AND PS.RESERVED_TYPE = 'REMITTANCE'
AND RAH.CURRENT_RECORD_FLAG = 'Y'
AND RAH.CUSTOMER_TRX_ID = C.CUSTOMER_TRX_ID
AND C.DRAWEE_ID = CUST_ACCT.CUST_ACCOUNT_ID
AND CUST_ACCT.PARTY_ID = PARTY.PARTY_ID
AND RAH.CUSTOMER_TRX_ID = PS.CUSTOMER_TRX_ID
AND PS.CUST_TRX_TYPE_ID = CTT.CUST_TRX_TYPE_ID
AND CUST_ACCT.CUST_ACCOUNT_ID = CP.CUST_ACCOUNT_ID
AND CP.PROFILE_CLASS_ID = PC.PROFILE_CLASS_ID
AND CP.SITE_USE_ID IS NULL
AND C.DRAWEE_SITE_USE_ID = SU.SITE_USE_ID
AND SU.CUST_ACCT_SITE_ID = ACCT_SITE.CUST_ACCT_SITE_ID
AND ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID
AND LOC.LOCATION_ID = PARTY_SITE.LOCATION_ID
AND CUST_ACCT.CUST_ACCOUNT_ID=ACCT_SITE.CUST_ACCOUNT_ID
AND IBY.TRXN_EXTENSION_ID(+) = C.PAYMENT_TRXN_EXTENSION_ID
AND C.REMIT_BANK_ACCT_USE_ID = RABA.BANK_ACCT_USE_ID(+)
AND RAH.STATUS = ARS.LOOKUP_CODE
AND ARS.LOOKUP_TYPE = 'TRANSACTION_HISTORY_STATUS'
AND CTT.SIGNED_FLAG IN ('Y'
, 'N')
AND CTT.DRAWEE_ISSUED_FLAG IN ('Y'
, 'N')
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 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 (+) UNION SELECT C.ROWID
, /* REMITTANCE */ BA.BATCH_ID
, /* TRANSACTION TYPE */ CTT.NAME
, PS.CUST_TRX_TYPE_ID
, CTT.SIGNED_FLAG
, CTT.DRAWEE_ISSUED_FLAG
, /* CUSTOMER TRX */ PS.PAYMENT_SCHEDULE_ID
, C.CUSTOMER_TRX_ID
, C.PRINTING_LAST_PRINTED
, C.COMMENTS
, PS.INVOICE_CURRENCY_CODE
, PS.TRX_DATE
, ARP_BR_REMIT_FUNCTION.GET_AMOUNT(RAH.TRANSACTION_HISTORY_ID
, RAH.STATUS
, C.CUSTOMER_TRX_ID)
, ARP_BR_REMIT_FUNCTION.GET_ACCTD_AMOUNT(RAH.TRANSACTION_HISTORY_ID
, RAH.STATUS
, C.CUSTOMER_TRX_ID)
, PS.DUE_DATE
, PS.TRX_NUMBER
, C.SPECIAL_INSTRUCTIONS
, PS.ATTRIBUTE_CATEGORY
, PS.ATTRIBUTE1
, PS.ATTRIBUTE2
, PS.ATTRIBUTE3
, PS.ATTRIBUTE4
, PS.ATTRIBUTE5
, PS.ATTRIBUTE6
, PS.ATTRIBUTE7
, PS.ATTRIBUTE8
, PS.ATTRIBUTE9
, PS.ATTRIBUTE10
, PS.ATTRIBUTE11
, PS.ATTRIBUTE12
, PS.ATTRIBUTE13
, PS.ATTRIBUTE14
, PS.ATTRIBUTE15
, /* DRAWEE */ SUBSTRB(PARTY.PARTY_NAME
, 1
, 50)
, CUST_ACCT.ACCOUNT_NUMBER
, PARTY.JGZZ_FISCAL_CODE
, CUST_ACCT.CUSTOMER_CLASS_CODE
, PARTY.CATEGORY_CODE
, PC.NAME
, /* DRAWEE SITE */ LOC.ADDRESS1 || LOC.ADDRESS2 || LOC.ADDRESS3
, LOC.CITY
, LOC.POSTAL_CODE
, LOC.STATE
, LOC.COUNTRY
, /* DRAWEE BANK */ IBY.BANK_NAME DRAWEE_BANK_NAME
, IBY.BANK_NUMBER DRAWEE_BANK_NUMBER
, /* DRAWEE BRANCH */ IBY.BANK_BRANCH_NAME DRAWEE_BRANCH_NAME
, NULL DRAWEE_BRANCH_CITY
, NULL DRAWEE_BRANCH_STATE
, NULL DRAWEE_BRANCH_COUNTRY
, NULL DRAWEE_BRANCH_POSTAL_CODE
, NULL DRAWEE_BRANCH_NUMBER
, /* REMITTANCE BANK */ REMITBANKPARTY.PARTY_NAME
, /* STANDARD WHO COLUMNS */ C.CREATED_BY
, C.CREATION_DATE
, C.LAST_UPDATED_BY
, C.LAST_UPDATE_DATE
, C.LAST_UPDATE_LOGIN
FROM AR_PAYMENT_SCHEDULES PS
, AR_TRANSACTION_HISTORY RAH
, HZ_CUST_ACCOUNTS CUST_ACCT
, HZ_PARTIES PARTY
, RA_CUST_TRX_TYPES CTT
, RA_CUSTOMER_TRX C
, HZ_CUSTOMER_PROFILES CP
, HZ_CUST_PROFILE_CLASSES PC
, HZ_CUST_ACCT_SITES ACCT_SITE
, HZ_PARTY_SITES PARTY_SITE
, HZ_LOCATIONS LOC
, HZ_CUST_SITE_USES SU
, IBY_TRXN_EXTENSIONS_V IBY
, AR_BATCHES BA
, AR_LOOKUPS ARS
, CE_BANK_ACCOUNTS CBA
, CE_BANK_ACCT_USES_OU_V RABA
, HZ_PARTIES REMITBRANCHPARTY
, HZ_PARTIES REMITBANKPARTY
, HZ_RELATIONSHIPS REMITBRREL
, HZ_ORGANIZATION_PROFILES REMITBANKORGPROFILE
, HZ_ORGANIZATION_PROFILES REMITBRANCHORGPROFILE
WHERE BA.BATCH_ID = RAH.BATCH_ID
AND ((BA.REMIT_METHOD_CODE = 'STANDARD'
AND RAH.EVENT = 'REMITTED') OR (BA.REMIT_METHOD_CODE = 'FACTORING'
AND BA.WITH_RECOURSE_FLAG = 'Y'
AND RAH.EVENT = 'FACTORED') OR (BA.REMIT_METHOD_CODE = 'FACTORING'
AND BA.WITH_RECOURSE_FLAG = 'N'
AND RAH.EVENT = 'CLOSED'))
AND RAH.CUSTOMER_TRX_ID = C.CUSTOMER_TRX_ID
AND C.DRAWEE_ID = CUST_ACCT.CUST_ACCOUNT_ID
AND CUST_ACCT.PARTY_ID = PARTY.PARTY_ID
AND RAH.CUSTOMER_TRX_ID = PS.CUSTOMER_TRX_ID
AND PS.CUST_TRX_TYPE_ID = CTT.CUST_TRX_TYPE_ID
AND CUST_ACCT.CUST_ACCOUNT_ID = CP.CUST_ACCOUNT_ID
AND CP.PROFILE_CLASS_ID = PC.PROFILE_CLASS_ID
AND CP.SITE_USE_ID IS NULL
AND C.DRAWEE_SITE_USE_ID = SU.SITE_USE_ID
AND SU.CUST_ACCT_SITE_ID = ACCT_SITE.CUST_ACCT_SITE_ID
AND ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID
AND LOC.LOCATION_ID = PARTY_SITE.LOCATION_ID
AND CUST_ACCT.CUST_ACCOUNT_ID=ACCT_SITE.CUST_ACCOUNT_ID
AND IBY.TRXN_EXTENSION_ID(+) = C.PAYMENT_TRXN_EXTENSION_ID
AND C.REMIT_BANK_ACCT_USE_ID = RABA.BANK_ACCT_USE_ID(+)
AND RAH.STATUS = ARS.LOOKUP_CODE
AND ARS.LOOKUP_TYPE = 'TRANSACTION_HISTORY_STATUS'
AND CTT.SIGNED_FLAG IN ('Y'
, 'N')
AND CTT.DRAWEE_ISSUED_FLAG IN ('Y'
, 'N')
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 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 (+)