VIEW: APPS.AR_BR_BATCHES_V

Source

View Text - Preformatted

SELECT BATCH.ROWID, BATCH.BATCH_ID, BATCH.SET_OF_BOOKS_ID, BATCH.LAST_UPDATE_DATE, BATCH.LAST_UPDATED_BY, BATCH.CREATION_DATE, BATCH.CREATED_BY, BATCH.LAST_UPDATE_LOGIN, BATCH.NAME, BATCH.BATCH_DATE, BATCH.GL_DATE, BATCH.TYPE, BATCH.BATCH_SOURCE_ID, BS.NAME, AL_STATUS.LOOKUP_CODE, AL_STATUS.MEANING, BATCH.CURRENCY_CODE, BATCH.EXCHANGE_RATE_TYPE, BATCH.EXCHANGE_DATE, BATCH.EXCHANGE_RATE, BATCH.COMMENTS, BATCH.ISSUE_DATE, BATCH.MATURITY_DATE, BATCH.SPECIAL_INSTRUCTIONS, BATCH.SELECTION_CRITERIA_ID, BATCH.ATTRIBUTE_CATEGORY, BATCH.ATTRIBUTE1, BATCH.ATTRIBUTE2, BATCH.ATTRIBUTE3, BATCH.ATTRIBUTE4, BATCH.ATTRIBUTE5, BATCH.ATTRIBUTE6, BATCH.ATTRIBUTE7, BATCH.ATTRIBUTE8, BATCH.ATTRIBUTE9, BATCH.ATTRIBUTE10, BATCH.ATTRIBUTE11, BATCH.ATTRIBUTE12, BATCH.ATTRIBUTE13, BATCH.ATTRIBUTE14, BATCH.ATTRIBUTE15, BATCH.REQUEST_ID, BATCH.PROGRAM_APPLICATION_ID, BATCH.PROGRAM_ID, BATCH.PROGRAM_UPDATE_DATE, BATCH.ORG_ID, SC.DUE_DATE_LOW, SC.DUE_DATE_HIGH, SC.TRX_DATE_LOW, SC.TRX_DATE_HIGH, SC.CUST_TRX_TYPE_ID, CT.NAME , SC.TRX_NUMBER_LOW, SC.TRX_NUMBER_HIGH , SC.RECEIPT_METHOD_ID , RM.NAME, SC.CUSTOMER_CLASS_CODE , AL_CLASS.MEANING, SC.CUSTOMER_CATEGORY_CODE , AL_CAT.MEANING , SC.CUSTOMER_ID, CUST_ACCT.ACCOUNT_NUMBER, SUBSTRB(PARTY.PARTY_NAME,1,50), SC.SITE_USE_ID , SU.LOCATION , SC.BANK_BRANCH_ID, BankParty.party_name BANK_NAME FROM RA_BATCHES BATCH, RA_BATCH_SOURCES_ALL BS, AR_LOOKUPS AL_STATUS, AR_LOOKUPS AL_CLASS, AR_LOOKUPS AL_CAT, AR_SELECTION_CRITERIA SC, RA_CUST_TRX_TYPES_ALL CT, HZ_CUST_ACCOUNTS_ALL CUST_ACCT, HZ_PARTIES PARTY, HZ_CUST_SITE_USES SU, AR_RECEIPT_METHODS RM, HZ_PARTIES BranchParty, HZ_PARTIES BankParty, HZ_RELATIONSHIPS BRRel WHERE BATCH.SELECTION_CRITERIA_ID = SC.SELECTION_CRITERIA_ID(+) AND BATCH.BATCH_PROCESS_STATUS = AL_STATUS.LOOKUP_CODE(+) AND AL_STATUS.LOOKUP_TYPE(+) = 'RA_BATCH_PROCESS_STATUS' AND SC.CUSTOMER_CLASS_CODE = AL_CLASS.LOOKUP_CODE(+) AND AL_CLASS.LOOKUP_TYPE(+)= 'CUSTOMER CLASS' AND SC.CUSTOMER_CATEGORY_CODE = AL_CAT.LOOKUP_CODE(+) AND AL_CAT.LOOKUP_TYPE(+)= 'CUSTOMER_CATEGORY' AND SC.CUST_TRX_TYPE_ID = CT.CUST_TRX_TYPE_ID(+) AND SC.RECEIPT_METHOD_ID = RM.RECEIPT_METHOD_ID (+) AND BATCH.BATCH_SOURCE_ID = BS.BATCH_SOURCE_ID AND SC.CUSTOMER_ID = CUST_ACCT.CUST_ACCOUNT_ID(+) AND CUST_ACCT.PARTY_ID = PARTY.PARTY_ID(+) AND SC.SITE_USE_ID= SU.SITE_USE_ID(+) AND BATCH.TYPE = 'BR' AND SC.bank_branch_id = BranchParty.party_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 nvl(BS.ORG_ID,BATCH.ORG_ID) = BATCH.ORG_ID AND nvl(CT.ORG_ID, BATCH.ORG_ID) = BATCH.ORG_ID AND nvl(CUST_ACCT.ORG_ID, BATCH.ORG_ID) = BATCH.ORG_ID

View Text - HTML Formatted

SELECT BATCH.ROWID
, BATCH.BATCH_ID
, BATCH.SET_OF_BOOKS_ID
, BATCH.LAST_UPDATE_DATE
, BATCH.LAST_UPDATED_BY
, BATCH.CREATION_DATE
, BATCH.CREATED_BY
, BATCH.LAST_UPDATE_LOGIN
, BATCH.NAME
, BATCH.BATCH_DATE
, BATCH.GL_DATE
, BATCH.TYPE
, BATCH.BATCH_SOURCE_ID
, BS.NAME
, AL_STATUS.LOOKUP_CODE
, AL_STATUS.MEANING
, BATCH.CURRENCY_CODE
, BATCH.EXCHANGE_RATE_TYPE
, BATCH.EXCHANGE_DATE
, BATCH.EXCHANGE_RATE
, BATCH.COMMENTS
, BATCH.ISSUE_DATE
, BATCH.MATURITY_DATE
, BATCH.SPECIAL_INSTRUCTIONS
, BATCH.SELECTION_CRITERIA_ID
, BATCH.ATTRIBUTE_CATEGORY
, BATCH.ATTRIBUTE1
, BATCH.ATTRIBUTE2
, BATCH.ATTRIBUTE3
, BATCH.ATTRIBUTE4
, BATCH.ATTRIBUTE5
, BATCH.ATTRIBUTE6
, BATCH.ATTRIBUTE7
, BATCH.ATTRIBUTE8
, BATCH.ATTRIBUTE9
, BATCH.ATTRIBUTE10
, BATCH.ATTRIBUTE11
, BATCH.ATTRIBUTE12
, BATCH.ATTRIBUTE13
, BATCH.ATTRIBUTE14
, BATCH.ATTRIBUTE15
, BATCH.REQUEST_ID
, BATCH.PROGRAM_APPLICATION_ID
, BATCH.PROGRAM_ID
, BATCH.PROGRAM_UPDATE_DATE
, BATCH.ORG_ID
, SC.DUE_DATE_LOW
, SC.DUE_DATE_HIGH
, SC.TRX_DATE_LOW
, SC.TRX_DATE_HIGH
, SC.CUST_TRX_TYPE_ID
, CT.NAME
, SC.TRX_NUMBER_LOW
, SC.TRX_NUMBER_HIGH
, SC.RECEIPT_METHOD_ID
, RM.NAME
, SC.CUSTOMER_CLASS_CODE
, AL_CLASS.MEANING
, SC.CUSTOMER_CATEGORY_CODE
, AL_CAT.MEANING
, SC.CUSTOMER_ID
, CUST_ACCT.ACCOUNT_NUMBER
, SUBSTRB(PARTY.PARTY_NAME
, 1
, 50)
, SC.SITE_USE_ID
, SU.LOCATION
, SC.BANK_BRANCH_ID
, BANKPARTY.PARTY_NAME BANK_NAME
FROM RA_BATCHES BATCH
, RA_BATCH_SOURCES_ALL BS
, AR_LOOKUPS AL_STATUS
, AR_LOOKUPS AL_CLASS
, AR_LOOKUPS AL_CAT
, AR_SELECTION_CRITERIA SC
, RA_CUST_TRX_TYPES_ALL CT
, HZ_CUST_ACCOUNTS_ALL CUST_ACCT
, HZ_PARTIES PARTY
, HZ_CUST_SITE_USES SU
, AR_RECEIPT_METHODS RM
, HZ_PARTIES BRANCHPARTY
, HZ_PARTIES BANKPARTY
, HZ_RELATIONSHIPS BRREL
WHERE BATCH.SELECTION_CRITERIA_ID = SC.SELECTION_CRITERIA_ID(+)
AND BATCH.BATCH_PROCESS_STATUS = AL_STATUS.LOOKUP_CODE(+)
AND AL_STATUS.LOOKUP_TYPE(+) = 'RA_BATCH_PROCESS_STATUS'
AND SC.CUSTOMER_CLASS_CODE = AL_CLASS.LOOKUP_CODE(+)
AND AL_CLASS.LOOKUP_TYPE(+)= 'CUSTOMER CLASS'
AND SC.CUSTOMER_CATEGORY_CODE = AL_CAT.LOOKUP_CODE(+)
AND AL_CAT.LOOKUP_TYPE(+)= 'CUSTOMER_CATEGORY'
AND SC.CUST_TRX_TYPE_ID = CT.CUST_TRX_TYPE_ID(+)
AND SC.RECEIPT_METHOD_ID = RM.RECEIPT_METHOD_ID (+)
AND BATCH.BATCH_SOURCE_ID = BS.BATCH_SOURCE_ID
AND SC.CUSTOMER_ID = CUST_ACCT.CUST_ACCOUNT_ID(+)
AND CUST_ACCT.PARTY_ID = PARTY.PARTY_ID(+)
AND SC.SITE_USE_ID= SU.SITE_USE_ID(+)
AND BATCH.TYPE = 'BR'
AND SC.BANK_BRANCH_ID = BRANCHPARTY.PARTY_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 NVL(BS.ORG_ID
, BATCH.ORG_ID) = BATCH.ORG_ID
AND NVL(CT.ORG_ID
, BATCH.ORG_ID) = BATCH.ORG_ID
AND NVL(CUST_ACCT.ORG_ID
, BATCH.ORG_ID) = BATCH.ORG_ID