VIEW: APPS.AR_BATCH_REMIT_V

Source

View Text - Preformatted

SELECT BT.ROWID ,BT.NAME NAME ,BT.BATCH_ID BATCH_ID ,RT.MEANING TYPE ,BT.BATCH_SOURCE_ID BATCH_SOURCE_ID ,BT.CURRENCY_CODE CURRENCY_CODE ,BT.BATCH_DATE BATCH_DATE ,BT.GL_DATE GL_DATE ,BT.RECEIPT_CLASS_ID RECEIPT_CLASS_ID ,BT.RECEIPT_METHOD_ID RECEIPT_METHOD_ID ,BT.REMIT_METHOD_CODE REMIT_METHOD_CODE ,RC.NAME RECEIPT_CLASS_NAME ,RM.NAME RECEIPT_METHOD_NAME ,BT.CONTROL_COUNT CONTROL_COUNT ,BT.CONTROL_AMOUNT CONTROL_AMOUNT ,BT.WITH_RECOURSE_FLAG WITH_RECOURSE_FLAG ,BT.AUTO_TRANS_PROGRAM_ID AUTO_TRANS_PROGRAM_ID ,PP1.FRIENDLY_NAME AUTO_TRANS_PROGRAM_NAME ,BT.AUTO_PRINT_PROGRAM_ID AUTO_PRINT_PROGRAM_ID ,PP2.FRIENDLY_NAME AUTO_PRINT_PROGRAM_NAME ,BT.MEDIA_REFERENCE MEDIA_REFERENCE ,BT.REMIT_BANK_ACCT_USE_ID REMIT_BANK_ACCT_USE_ID ,CBA.CHECK_DIGITS ,CBA.BANK_ACCOUNT_NAME BANK_ACCOUNT_NAME ,CBA.BANK_ACCOUNT_NUM BANK_ACCOUNT_NUMBER ,BT.REMITTANCE_BANK_BRANCH_ID REMITTANCE_BANK_BRANCH_ID ,BranchParty.party_name BANK_BRANCH_NAME ,BankParty.party_name BANK_NAME ,BranchOrgProfile.bank_or_branch_number BANK_BRANCH_NUM ,BankOrgProfile.bank_or_branch_number BANK_NUMBER ,CBA.CURRENCY_CODE BANK_CURRENCY ,BT.BANK_DEPOSIT_NUMBER BANK_DEPOSIT_NUMBER ,BT.COMMENTS COMMENTS ,BT.STATUS STATUS ,LS.MEANING ,BT.BATCH_APPLIED_STATUS BATCH_APPLIED_STATUS ,BT.OPERATION_REQUEST_ID REQUEST_ID ,BT.ATTRIBUTE_CATEGORY ,BT.ATTRIBUTE1 ,BT.ATTRIBUTE2 ,BT.ATTRIBUTE3 ,BT.ATTRIBUTE4 ,BT.ATTRIBUTE5 ,BT.ATTRIBUTE6 ,BT.ATTRIBUTE7 ,BT.ATTRIBUTE8 ,BT.ATTRIBUTE9 ,BT.ATTRIBUTE10 ,BT.CREATED_BY ,BT.CREATION_DATE ,BT.LAST_UPDATED_BY ,BT.LAST_UPDATE_DATE ,BT.LAST_UPDATE_LOGIN ,BT.ORG_ID FROM AR_BATCHES BT, AR_RECEIPT_CLASSES RC, AR_RECEIPT_METHODS RM, AR_RECEIPT_METHOD_ACCOUNTS_ALL RMA, AP_PAYMENT_PROGRAMS PP1, AP_PAYMENT_PROGRAMS PP2, AR_LOOKUPS LRM, AR_LOOKUPS LS, AR_LOOKUPS RT, HZ_PARTIES BranchParty, HZ_PARTIES BankParty, HZ_RELATIONSHIPS BRRel, HZ_ORGANIZATION_PROFILES BankOrgProfile, HZ_ORGANIZATION_PROFILES BranchOrgProfile, CE_BANK_ACCOUNTS CBA, CE_BANK_ACCT_USES_ALL BA WHERE RT.LOOKUP_TYPE = 'BR_REMITTANCE_TYPE' AND RT.LOOKUP_CODE = BT.TYPE AND BT.TYPE = 'BR_REMITTANCE' and BT.REMIT_METHOD_CODE = LRM.LOOKUP_CODE and LRM.LOOKUP_TYPE = 'REMITTANCE_METHOD' and LS.LOOKUP_TYPE = 'BATCH_APPLIED_STATUS' and LS.LOOKUP_CODE = BT.BATCH_APPLIED_STATUS AND BT.REMIT_BANK_ACCT_USE_ID = BA.BANK_ACCT_USE_ID and BT.ORG_ID = BA.ORG_ID and BT.REMITTANCE_BANK_BRANCH_ID = CBA.BANK_BRANCH_ID and RMA.REMIT_BANK_ACCT_USE_ID = BT.REMIT_BANK_ACCT_USE_ID and RMA.RECEIPT_METHOD_ID = BT.RECEIPT_METHOD_ID and RMA.ORG_ID = BT.ORG_ID and BT.RECEIPT_CLASS_ID = RC.RECEIPT_CLASS_ID and BT.RECEIPT_METHOD_ID= RM.RECEIPT_METHOD_ID and PP1.PROGRAM_ID(+) = BT.AUTO_TRANS_PROGRAM_ID and PP1.PROGRAM_TYPE(+) = 'REMIT_TRANSMIT' and PP2.PROGRAM_ID(+) = BT.AUTO_PRINT_PROGRAM_ID and PP2.PROGRAM_TYPE(+) = 'REMIT_PRINT' AND BranchParty.party_id = BT.remittance_bank_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 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 BA.bank_account_id = CBA.bank_account_id (+) ORDER BY BT.BATCH_ID,BT.NAME

View Text - HTML Formatted

SELECT BT.ROWID
, BT.NAME NAME
, BT.BATCH_ID BATCH_ID
, RT.MEANING TYPE
, BT.BATCH_SOURCE_ID BATCH_SOURCE_ID
, BT.CURRENCY_CODE CURRENCY_CODE
, BT.BATCH_DATE BATCH_DATE
, BT.GL_DATE GL_DATE
, BT.RECEIPT_CLASS_ID RECEIPT_CLASS_ID
, BT.RECEIPT_METHOD_ID RECEIPT_METHOD_ID
, BT.REMIT_METHOD_CODE REMIT_METHOD_CODE
, RC.NAME RECEIPT_CLASS_NAME
, RM.NAME RECEIPT_METHOD_NAME
, BT.CONTROL_COUNT CONTROL_COUNT
, BT.CONTROL_AMOUNT CONTROL_AMOUNT
, BT.WITH_RECOURSE_FLAG WITH_RECOURSE_FLAG
, BT.AUTO_TRANS_PROGRAM_ID AUTO_TRANS_PROGRAM_ID
, PP1.FRIENDLY_NAME AUTO_TRANS_PROGRAM_NAME
, BT.AUTO_PRINT_PROGRAM_ID AUTO_PRINT_PROGRAM_ID
, PP2.FRIENDLY_NAME AUTO_PRINT_PROGRAM_NAME
, BT.MEDIA_REFERENCE MEDIA_REFERENCE
, BT.REMIT_BANK_ACCT_USE_ID REMIT_BANK_ACCT_USE_ID
, CBA.CHECK_DIGITS
, CBA.BANK_ACCOUNT_NAME BANK_ACCOUNT_NAME
, CBA.BANK_ACCOUNT_NUM BANK_ACCOUNT_NUMBER
, BT.REMITTANCE_BANK_BRANCH_ID REMITTANCE_BANK_BRANCH_ID
, BRANCHPARTY.PARTY_NAME BANK_BRANCH_NAME
, BANKPARTY.PARTY_NAME BANK_NAME
, BRANCHORGPROFILE.BANK_OR_BRANCH_NUMBER BANK_BRANCH_NUM
, BANKORGPROFILE.BANK_OR_BRANCH_NUMBER BANK_NUMBER
, CBA.CURRENCY_CODE BANK_CURRENCY
, BT.BANK_DEPOSIT_NUMBER BANK_DEPOSIT_NUMBER
, BT.COMMENTS COMMENTS
, BT.STATUS STATUS
, LS.MEANING
, BT.BATCH_APPLIED_STATUS BATCH_APPLIED_STATUS
, BT.OPERATION_REQUEST_ID REQUEST_ID
, BT.ATTRIBUTE_CATEGORY
, BT.ATTRIBUTE1
, BT.ATTRIBUTE2
, BT.ATTRIBUTE3
, BT.ATTRIBUTE4
, BT.ATTRIBUTE5
, BT.ATTRIBUTE6
, BT.ATTRIBUTE7
, BT.ATTRIBUTE8
, BT.ATTRIBUTE9
, BT.ATTRIBUTE10
, BT.CREATED_BY
, BT.CREATION_DATE
, BT.LAST_UPDATED_BY
, BT.LAST_UPDATE_DATE
, BT.LAST_UPDATE_LOGIN
, BT.ORG_ID
FROM AR_BATCHES BT
, AR_RECEIPT_CLASSES RC
, AR_RECEIPT_METHODS RM
, AR_RECEIPT_METHOD_ACCOUNTS_ALL RMA
, AP_PAYMENT_PROGRAMS PP1
, AP_PAYMENT_PROGRAMS PP2
, AR_LOOKUPS LRM
, AR_LOOKUPS LS
, AR_LOOKUPS RT
, HZ_PARTIES BRANCHPARTY
, HZ_PARTIES BANKPARTY
, HZ_RELATIONSHIPS BRREL
, HZ_ORGANIZATION_PROFILES BANKORGPROFILE
, HZ_ORGANIZATION_PROFILES BRANCHORGPROFILE
, CE_BANK_ACCOUNTS CBA
, CE_BANK_ACCT_USES_ALL BA
WHERE RT.LOOKUP_TYPE = 'BR_REMITTANCE_TYPE'
AND RT.LOOKUP_CODE = BT.TYPE
AND BT.TYPE = 'BR_REMITTANCE'
AND BT.REMIT_METHOD_CODE = LRM.LOOKUP_CODE
AND LRM.LOOKUP_TYPE = 'REMITTANCE_METHOD'
AND LS.LOOKUP_TYPE = 'BATCH_APPLIED_STATUS'
AND LS.LOOKUP_CODE = BT.BATCH_APPLIED_STATUS
AND BT.REMIT_BANK_ACCT_USE_ID = BA.BANK_ACCT_USE_ID
AND BT.ORG_ID = BA.ORG_ID
AND BT.REMITTANCE_BANK_BRANCH_ID = CBA.BANK_BRANCH_ID
AND RMA.REMIT_BANK_ACCT_USE_ID = BT.REMIT_BANK_ACCT_USE_ID
AND RMA.RECEIPT_METHOD_ID = BT.RECEIPT_METHOD_ID
AND RMA.ORG_ID = BT.ORG_ID
AND BT.RECEIPT_CLASS_ID = RC.RECEIPT_CLASS_ID
AND BT.RECEIPT_METHOD_ID= RM.RECEIPT_METHOD_ID
AND PP1.PROGRAM_ID(+) = BT.AUTO_TRANS_PROGRAM_ID
AND PP1.PROGRAM_TYPE(+) = 'REMIT_TRANSMIT'
AND PP2.PROGRAM_ID(+) = BT.AUTO_PRINT_PROGRAM_ID
AND PP2.PROGRAM_TYPE(+) = 'REMIT_PRINT'
AND BRANCHPARTY.PARTY_ID = BT.REMITTANCE_BANK_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 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 BA.BANK_ACCOUNT_ID = CBA.BANK_ACCOUNT_ID (+) ORDER BY BT.BATCH_ID
, BT.NAME