View: AR_BATCHES_V

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

View Text

SELECT BT.ROWID ROW_ID
, BT.SET_OF_BOOKS_ID SET_OF_BOOKS_ID
, BT.NAME NAME
, BT.BATCH_ID BATCH_ID
, DECODE( BT.TYPE
, 'MANUAL'
, DECODE( BT.BATCH_APPLIED_STATUS
, 'PROCESSED'
, 'MANUAL_REGULAR'
, 'POSTBATCH_WAITING'
, 'MANUAL_QUICK'
, 'IN_PROCESS'
, 'MANUAL_QUICK'
, 'NULL' )
, BT.TYPE ) TYPE
, BT.BATCH_SOURCE_ID BATCH_SOURCE_ID
, BT.CREATED_BY CREATED_BY
, BT.CREATION_DATE CREATION_DATE
, BT.LAST_UPDATED_BY LAST_UPDATED_BY
, BT.LAST_UPDATE_DATE LAST_UPDATE_DATE
, BT.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
, BT.PROGRAM_APPLICATION_ID PROGRAM_APPLICATION_ID
, BT.PROGRAM_ID PROGRAM_ID
, BT.PROGRAM_UPDATE_DATE PROGRAM_UPDATE_DATE
, BS.NAME BATCH_SOURCE_NAME
, BS.AUTO_BATCH_NUMBERING AUTO_BATCH_NUMBERING
, BT.CURRENCY_CODE CURRENCY_CODE
, BT.BATCH_DATE BATCH_DATE
, BT.GL_DATE GL_DATE
, BT.DEPOSIT_DATE DEPOSIT_DATE
, BT.CLOSED_DATE CLOSED_DATE
, BT.EXCHANGE_DATE EXCHANGE_DATE
, BT.EXCHANGE_RATE EXCHANGE_RATE
, BT.EXCHANGE_RATE_TYPE EXCHANGE_RATE_TYPE
, GD.USER_CONVERSION_TYPE EXCHANGE_RATE_TYPE_DSP
, 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.TRANSMISSION_ID TRANSMISSION_ID
, TR.TRANSMISSION_NAME TRANSMISSION_NAME
, LB.LOCKBOX_ID LOCKBOX_ID
, LB.LOCKBOX_NUMBER LOCKBOX_NUMBER
, BT.LOCKBOX_BATCH_NAME LOCKBOX_BATCH_NAME
, BT.MEDIA_REFERENCE MEDIA_REFERENCE
, DECODE(BT.REMIT_METHOD_CODE
, NULL
, NULL
, LRM.MEANING) RECEIPT_METHOD_CODE_MEANING
, BT.REMIT_BANK_ACCT_USE_ID REMIT_BANK_ACCT_USE_ID
, BT.REMITTANCE_BANK_BRANCH_ID REMITTANCE_BANK_BRANCH_ID
, DECODE(BT.REMITTANCE_BANK_BRANCH_ID
, NULL
, BRANCHPARTY2.PARTY_ID
, BRANCHPARTY.PARTY_ID) BANK_BRANCH_ID
, DECODE(BT.REMITTANCE_BANK_BRANCH_ID
, NULL
, BANKPARTY2.PARTY_NAME
, BANKPARTY.PARTY_NAME) BANK_NAME
, DECODE(BT.REMITTANCE_BANK_BRANCH_ID
, NULL
, BRANCHPARTY2.PARTY_NAME
, BRANCHPARTY.PARTY_NAME) BANK_BRANCH_NAME
, DECODE(BT.REMITTANCE_BANK_BRANCH_ID
, NULL
, BANKCA2.CLASS_CODE
, BANKCA.CLASS_CODE) INSTITUTION_TYPE
, CBA.BANK_ACCOUNT_NUM BANK_ACCOUNT_NUMBER
, BT.BANK_DEPOSIT_NUMBER BANK_DEPOSIT_NUMBER
, BT.COMMENTS COMMENTS
, BT.STATUS STATUS
, BT.BATCH_APPLIED_STATUS BATCH_APPLIED_STATUS
, DECODE(BT.STATUS
, NULL
, NULL
, LST.MEANING) BATCH_STATUS_MEANING
, DECODE(BT.BATCH_APPLIED_STATUS
, NULL
, NULL
, LBST.MEANING) BATCH_APPLIED_STATUS_MEANING
, BT.REQUEST_ID REQUEST_ID
, BT.OPERATION_REQUEST_ID OPERATION_REQUEST_ID
, BT.TRANSMISSION_REQUEST_ID TRANSMISSION_REQUEST_ID
, RMA.OVERRIDE_REMIT_ACCOUNT_FLAG OVERRIDE_REMIT_BANK
, RC.REMIT_FLAG REMITTANCE_REQUIRED_FLAG
, RC.CREATION_STATUS RECEIPT_CREATION_STATUS
, RC.CONFIRM_FLAG CONFIRM_REQUIRED_FLAG
, DECODE(RC.CREATION_STATUS
, NULL
, NULL
, LRC.MEANING) RECEIPT_CREATION_STATUS_DSP
, CBA.CURRENCY_CODE BANK_CURRENCY
, BT.PURGED_CHILDREN_FLAG PURGED_CHILDREN_FLAG
, BT.ATTRIBUTE_CATEGORY ATTRIBUTE_CATEGORY
, BT.ATTRIBUTE1 ATTRIBUTE1
, BT.ATTRIBUTE2 ATTRIBUTE2
, BT.ATTRIBUTE3 ATTRIBUTE3
, BT.ATTRIBUTE4 ATTRIBUTE4
, BT.ATTRIBUTE5 ATTRIBUTE5
, BT.ATTRIBUTE6 ATTRIBUTE6
, BT.ATTRIBUTE7 ATTRIBUTE7
, BT.ATTRIBUTE8 ATTRIBUTE8
, BT.ATTRIBUTE9 ATTRIBUTE9
, BT.ATTRIBUTE10 ATTRIBUTE10
, BT.ATTRIBUTE11 ATTRIBUTE11
, BT.ATTRIBUTE12 ATTRIBUTE12
, BT.ATTRIBUTE13 ATTRIBUTE13
, BT.ATTRIBUTE14 ATTRIBUTE14
, BT.ATTRIBUTE15 ATTRIBUTE15
, BT.ORG_ID
FROM AR_BATCHES BT
, AR_BATCH_SOURCES_ALL BS
, AR_RECEIPT_CLASSES RC
, AR_RECEIPT_METHODS RM
, AR_RECEIPT_METHOD_ACCOUNTS_ALL RMA
, AR_TRANSMISSIONS_ALL TR
, AR_LOCKBOXES LB
, AR_LOOKUPS LRM
, AR_LOOKUPS LST
, AR_LOOKUPS LBST
, AR_LOOKUPS LRC
, GL_DAILY_CONVERSION_TYPES GD
, CE_BANK_ACCOUNTS CBA
, CE_BANK_ACCT_USES BA
, HZ_PARTIES BRANCHPARTY
, HZ_PARTIES BANKPARTY
, HZ_RELATIONSHIPS BRREL
, HZ_CODE_ASSIGNMENTS BANKCA
, HZ_PARTIES BRANCHPARTY2
, HZ_PARTIES BANKPARTY2
, HZ_RELATIONSHIPS BRREL2
, HZ_CODE_ASSIGNMENTS BANKCA2
WHERE BS.BATCH_SOURCE_ID(+) = BT.BATCH_SOURCE_ID
AND BS.ORG_ID(+) = BT.ORG_ID
AND RC.RECEIPT_CLASS_ID(+) = BT.RECEIPT_CLASS_ID
AND RC.CREATION_METHOD_CODE(+) NOT IN ('BR'
, 'BR_REMIT')
AND RM.RECEIPT_METHOD_ID(+) = BT.RECEIPT_METHOD_ID
AND TR.TRANSMISSION_ID(+) = BT.TRANSMISSION_ID
AND TR.ORG_ID(+) = BT.ORG_ID
AND GD.CONVERSION_TYPE(+) = BT.EXCHANGE_RATE_TYPE
AND LB.LOCKBOX_ID(+) = BT.LOCKBOX_ID
AND LRM.LOOKUP_CODE = NVL(BT.REMIT_METHOD_CODE
, 'STANDARD')
AND LRM.LOOKUP_TYPE = 'REMITTANCE_METHOD'
AND BA.BANK_ACCT_USE_ID (+) = BT.REMIT_BANK_ACCT_USE_ID
AND BA.ORG_ID(+) = BT.ORG_ID
AND RMA.REMIT_BANK_ACCT_USE_ID(+) = BT.REMIT_BANK_ACCT_USE_ID
AND RMA.ORG_ID(+) = BT.ORG_ID
AND RMA.PRIMARY_FLAG(+) = 'Y'
AND RMA.RECEIPT_METHOD_ID(+) = BT.RECEIPT_METHOD_ID
AND LST.LOOKUP_CODE = NVL(BT.STATUS
, 'OOB')
AND LST.LOOKUP_TYPE = 'BATCH_STATUS'
AND LBST.LOOKUP_TYPE(+) = 'BATCH_APPLIED_STATUS'
AND LBST.LOOKUP_CODE(+) = BT.BATCH_APPLIED_STATUS
AND LRC.LOOKUP_TYPE(+) = 'RECEIPT_CREATION_STATUS'
AND LRC.LOOKUP_CODE(+) = RC.CREATION_STATUS
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 BANKCA.CLASS_CATEGORY (+) = 'BANK_INSTITUTION_TYPE'
AND BANKCA.OWNER_TABLE_NAME (+) = 'HZ_PARTIES'
AND BANKCA.OWNER_TABLE_ID (+) = BANKPARTY.PARTY_ID
AND BRANCHPARTY2.PARTY_ID (+) = CBA.BANK_BRANCH_ID
AND BRREL2.OBJECT_ID = BANKPARTY2.PARTY_ID (+)
AND BRREL2.SUBJECT_ID (+) = BRANCHPARTY2.PARTY_ID
AND BRREL2.RELATIONSHIP_TYPE (+) = 'BANK_AND_BRANCH'
AND BRREL2.RELATIONSHIP_CODE (+) = 'BRANCH_OF'
AND BRREL2.SUBJECT_TABLE_NAME (+) = 'HZ_PARTIES'
AND BRREL2.SUBJECT_TYPE (+) = 'ORGANIZATION'
AND BRREL2.OBJECT_TABLE_NAME (+) = 'HZ_PARTIES'
AND BRREL2.OBJECT_TYPE (+) = 'ORGANIZATION'
AND BANKCA2.CLASS_CATEGORY (+) = 'BANK_INSTITUTION_TYPE'
AND BANKCA2.OWNER_TABLE_NAME (+) = 'HZ_PARTIES'
AND BANKCA2.OWNER_TABLE_ID (+) = BANKPARTY2.PARTY_ID
AND BA.BANK_ACCOUNT_ID = CBA.BANK_ACCOUNT_ID (+)

Columns