View: AR_BATCH_REMIT_V

Product:	AR - Receivables
Description:
Implementation/DBA Data:	APPS.AR_BATCH_REMIT_V

View Text

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

Columns