VIEW: APPS.AR_BATCHES_V

Source

View Text - Preformatted

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 (+)

View Text - HTML Formatted

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 (+)