eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.JL_BR_AR_SELECT_ACCOUNTS_V

Source

View Text - Preformatted


SELECT SA.ROWID ROW_ID , SA.SELECT_ACCOUNT_ID SELECT_ACCOUNT_ID , SA.SELECTION_CONTROL_ID SELECTION_CONTROL_ID , SA.BANK_ACCT_USE_ID BANK_ACCT_USE_ID , SA.GL_DATE GL_DATE , SA.SELECTION_DATE SELECTION_DATE , SA.CANCELLATION_DATE CANCELLATION_DATE , SA.FORMAT_DATE FORMAT_DATE , SA.REMITTANCE_DATE REMITTANCE_DATE , SA.PORTFOLIO_CODE PORTFOLIO_CODE , SA.MIN_DOCUMENT_AMOUNT MIN_DOCUMENT_AMOUNT , SA.MAX_DOCUMENT_AMOUNT MAX_DOCUMENT_AMOUNT , SA.MIN_REMITTANCE_AMOUNT MIN_REMITTANCE_AMOUNT , SA.MAX_REMITTANCE_AMOUNT MAX_REMITTANCE_AMOUNT , SA.BANK_INSTRUCTION_CODE1 BANK_INSTRUCTION_CODE1 , SA.BANK_INSTRUCTION_CODE2 BANK_INSTRUCTION_CODE2 , SA.BANK_CHARGE_AMOUNT BANK_CHARGE_AMOUNT , SA.BATCH_SOURCE_ID BATCH_SOURCE_ID , SA.PERCENTAGE_DISTRIBUTION PERCENTAGE_DISTRIBUTION , SA.ORG_ID ORG_ID , SA.ATTRIBUTE_CATEGORY ATTRIBUTE_CATEGORY , SA.ATTRIBUTE1 ATTRIBUTE1 , SA.ATTRIBUTE2 ATTRIBUTE2 , SA.ATTRIBUTE3 ATTRIBUTE3 , SA.ATTRIBUTE4 ATTRIBUTE4 , SA.ATTRIBUTE5 ATTRIBUTE5 , SA.ATTRIBUTE6 ATTRIBUTE6 , SA.ATTRIBUTE7 ATTRIBUTE7 , SA.ATTRIBUTE8 ATTRIBUTE8 , SA.ATTRIBUTE9 ATTRIBUTE9 , SA.ATTRIBUTE10 ATTRIBUTE10 , SA.ATTRIBUTE11 ATTRIBUTE11 , SA.ATTRIBUTE12 ATTRIBUTE12 , SA.ATTRIBUTE13 ATTRIBUTE13 , SA.ATTRIBUTE14 ATTRIBUTE14 , SA.ATTRIBUTE15 ATTRIBUTE15 , SA.LAST_UPDATE_DATE LAST_UPDATE_DATE , SA.LAST_UPDATED_BY LAST_UPDATED_BY , SA.CREATION_DATE CREATION_DATE , SA.CREATED_BY CREATED_BY , SA.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN, BI.BANK_INSTRUCTION_TYPE BANK_INSTRUCTION_TYPE1 , BI.DESCRIPTION BANK_INSTRUCTION_CODE1_DSP , BI2.BANK_INSTRUCTION_TYPE BANK_INSTRUCTION_TYPE2 , BI2.DESCRIPTION BANK_INSTRUCTION_CODE2_DSP , HzOrgProfileBank.BANK_OR_BRANCH_NUMBER BANK_NUMBER , HzPartyBank.PARTY_ID BANK_PARTY_ID , HzPartyBank.PARTY_NAME BANK_NAME , HzOrgProfileBranch.BANK_OR_BRANCH_NUMBER BRANCH_NUMBER , HzPartyBranch.PARTY_ID BRANCH_PARTY_ID , HzPartyBranch.PARTY_NAME BANK_BRANCH_NAME , CeBankAccount.BANK_BRANCH_ID BANK_BRANCH_ID , BS.NAME BATCH_SOURCE_ID_DSP, CeBankAccount.BANK_ACCOUNT_NUM BANK_ACCOUNT_NUM FROM JL_BR_AR_SELECT_ACCOUNTS SA, JL_BR_AR_BANK_INSTRUCTIONS BI, JL_BR_AR_BANK_INSTRUCTIONS BI2, AR_BATCH_SOURCES BS, CE_BANK_ACCOUNTS CeBankAccount, CE_BANK_ACCT_USES AcctUse, HZ_PARTIES HzPartyBank, HZ_ORGANIZATION_PROFILES HzOrgProfileBank, HZ_ORGANIZATION_PROFILES HzOrgProfileBranch, HZ_CODE_ASSIGNMENTS HzCodeAssignmentBank , HZ_PARTIES HzPartyBranch, HZ_CODE_ASSIGNMENTS HzCodeAssignmentBranch, HZ_RELATIONSHIPS HzRelationshipBR WHERE AcctUse.BANK_ACCT_USE_ID = SA.BANK_ACCT_USE_ID AND AcctUse.BANK_ACCOUNT_ID = CeBankAccount.BANK_ACCOUNT_ID AND HzPartyBranch.party_id = CeBankAccount.bank_branch_id and HzPartyBranch.PARTY_TYPE = 'ORGANIZATION' and HzOrgProfileBranch.PARTY_ID = HzPartyBranch.PARTY_ID and SYSDATE between TRUNC(HzOrgProfileBranch. effective_start_date) and NVL(TRUNC(HzOrgProfileBranch.effective_end_date), SYSDATE+1) and HzCodeAssignmentBranch.CLASS_CATEGORY = 'BANK_INSTITUTION_TYPE' and HzCodeAssignmentBranch.CLASS_CODE = 'BANK_BRANCH' and HzCodeAssignmentBranch.OWNER_TABLE_NAME = 'HZ_PARTIES' and HzCodeAssignmentBranch.OWNER_TABLE_ID = HzPartyBranch.PARTY_ID and SYSDATE between TRUNC(HzCodeAssignmentBranch. start_date_active) and NVL(TRUNC(HzCodeAssignmentBranch.end_date_active), SYSDATE+1) and HzPartyBank.PARTY_TYPE = 'ORGANIZATION' and /*HzPartyBank.Country = 'BR' and*/ HzPartyBank.status = 'A' and HzOrgProfileBank.PARTY_ID = HzPartyBank.PARTY_ID and SYSDATE between TRUNC(HzOrgProfileBank.effective_start_date) and NVL(TRUNC(HzOrgProfileBank.effective_end_date), SYSDATE+1) and SYSDATE between TRUNC(HzCodeAssignmentBank. start_date_active) and NVL(TRUNC(HzCodeAssignmentBank.end_date_active), SYSDATE+1) and HzCodeAssignmentBank.CLASS_CATEGORY = 'BANK_INSTITUTION_TYPE' and HzCodeAssignmentBank.CLASS_CODE = 'BANK' and HzCodeAssignmentBank.OWNER_TABLE_NAME = 'HZ_PARTIES' and HzCodeAssignmentBank.OWNER_TABLE_ID = HzPartyBank.PARTY_ID and HzRelationshipBR.SUBJECT_ID = HzPartyBranch.PARTY_ID and HzRelationshipBR.OBJECT_ID = HzPartyBank.PARTY_ID and HzRelationshipBR.RELATIONSHIP_CODE = 'BRANCH_OF' and HzRelationshipBR.RELATIONSHIP_TYPE = 'BANK_AND_BRANCH' and HzRelationshipBR.SUBJECT_TABLE_NAME = 'HZ_PARTIES' and HzRelationshipBR.SUBJECT_TYPE = 'ORGANIZATION' and HzRelationshipBR.OBJECT_TABLE_NAME = 'HZ_PARTIES' and HzRelationshipBR.OBJECT_TYPE = 'ORGANIZATION' and (BI.BANK_INSTRUCTION_CODE (+) = SA.BANK_INSTRUCTION_CODE1 AND ((BI.BANK_PARTY_ID IS NULL) OR (HzPartyBank.PARTY_ID = BI.BANK_PARTY_ID))) AND (BI2.BANK_INSTRUCTION_CODE (+) =SA.BANK_INSTRUCTION_CODE2 AND ((BI2.BANK_PARTY_ID IS NULL) OR (HzPartyBank.PARTY_ID = BI2.BANK_PARTY_ID))) AND BS.BATCH_SOURCE_ID (+) = SA.BATCH_SOURCE_ID

View Text - HTML Formatted

SELECT SA.ROWID ROW_ID
, SA.SELECT_ACCOUNT_ID SELECT_ACCOUNT_ID
, SA.SELECTION_CONTROL_ID SELECTION_CONTROL_ID
, SA.BANK_ACCT_USE_ID BANK_ACCT_USE_ID
, SA.GL_DATE GL_DATE
, SA.SELECTION_DATE SELECTION_DATE
, SA.CANCELLATION_DATE CANCELLATION_DATE
, SA.FORMAT_DATE FORMAT_DATE
, SA.REMITTANCE_DATE REMITTANCE_DATE
, SA.PORTFOLIO_CODE PORTFOLIO_CODE
, SA.MIN_DOCUMENT_AMOUNT MIN_DOCUMENT_AMOUNT
, SA.MAX_DOCUMENT_AMOUNT MAX_DOCUMENT_AMOUNT
, SA.MIN_REMITTANCE_AMOUNT MIN_REMITTANCE_AMOUNT
, SA.MAX_REMITTANCE_AMOUNT MAX_REMITTANCE_AMOUNT
, SA.BANK_INSTRUCTION_CODE1 BANK_INSTRUCTION_CODE1
, SA.BANK_INSTRUCTION_CODE2 BANK_INSTRUCTION_CODE2
, SA.BANK_CHARGE_AMOUNT BANK_CHARGE_AMOUNT
, SA.BATCH_SOURCE_ID BATCH_SOURCE_ID
, SA.PERCENTAGE_DISTRIBUTION PERCENTAGE_DISTRIBUTION
, SA.ORG_ID ORG_ID
, SA.ATTRIBUTE_CATEGORY ATTRIBUTE_CATEGORY
, SA.ATTRIBUTE1 ATTRIBUTE1
, SA.ATTRIBUTE2 ATTRIBUTE2
, SA.ATTRIBUTE3 ATTRIBUTE3
, SA.ATTRIBUTE4 ATTRIBUTE4
, SA.ATTRIBUTE5 ATTRIBUTE5
, SA.ATTRIBUTE6 ATTRIBUTE6
, SA.ATTRIBUTE7 ATTRIBUTE7
, SA.ATTRIBUTE8 ATTRIBUTE8
, SA.ATTRIBUTE9 ATTRIBUTE9
, SA.ATTRIBUTE10 ATTRIBUTE10
, SA.ATTRIBUTE11 ATTRIBUTE11
, SA.ATTRIBUTE12 ATTRIBUTE12
, SA.ATTRIBUTE13 ATTRIBUTE13
, SA.ATTRIBUTE14 ATTRIBUTE14
, SA.ATTRIBUTE15 ATTRIBUTE15
, SA.LAST_UPDATE_DATE LAST_UPDATE_DATE
, SA.LAST_UPDATED_BY LAST_UPDATED_BY
, SA.CREATION_DATE CREATION_DATE
, SA.CREATED_BY CREATED_BY
, SA.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
, BI.BANK_INSTRUCTION_TYPE BANK_INSTRUCTION_TYPE1
, BI.DESCRIPTION BANK_INSTRUCTION_CODE1_DSP
, BI2.BANK_INSTRUCTION_TYPE BANK_INSTRUCTION_TYPE2
, BI2.DESCRIPTION BANK_INSTRUCTION_CODE2_DSP
, HZORGPROFILEBANK.BANK_OR_BRANCH_NUMBER BANK_NUMBER
, HZPARTYBANK.PARTY_ID BANK_PARTY_ID
, HZPARTYBANK.PARTY_NAME BANK_NAME
, HZORGPROFILEBRANCH.BANK_OR_BRANCH_NUMBER BRANCH_NUMBER
, HZPARTYBRANCH.PARTY_ID BRANCH_PARTY_ID
, HZPARTYBRANCH.PARTY_NAME BANK_BRANCH_NAME
, CEBANKACCOUNT.BANK_BRANCH_ID BANK_BRANCH_ID
, BS.NAME BATCH_SOURCE_ID_DSP
, CEBANKACCOUNT.BANK_ACCOUNT_NUM BANK_ACCOUNT_NUM
FROM JL_BR_AR_SELECT_ACCOUNTS SA
, JL_BR_AR_BANK_INSTRUCTIONS BI
, JL_BR_AR_BANK_INSTRUCTIONS BI2
, AR_BATCH_SOURCES BS
, CE_BANK_ACCOUNTS CEBANKACCOUNT
, CE_BANK_ACCT_USES ACCTUSE
, HZ_PARTIES HZPARTYBANK
, HZ_ORGANIZATION_PROFILES HZORGPROFILEBANK
, HZ_ORGANIZATION_PROFILES HZORGPROFILEBRANCH
, HZ_CODE_ASSIGNMENTS HZCODEASSIGNMENTBANK
, HZ_PARTIES HZPARTYBRANCH
, HZ_CODE_ASSIGNMENTS HZCODEASSIGNMENTBRANCH
, HZ_RELATIONSHIPS HZRELATIONSHIPBR
WHERE ACCTUSE.BANK_ACCT_USE_ID = SA.BANK_ACCT_USE_ID
AND ACCTUSE.BANK_ACCOUNT_ID = CEBANKACCOUNT.BANK_ACCOUNT_ID
AND HZPARTYBRANCH.PARTY_ID = CEBANKACCOUNT.BANK_BRANCH_ID
AND HZPARTYBRANCH.PARTY_TYPE = 'ORGANIZATION'
AND HZORGPROFILEBRANCH.PARTY_ID = HZPARTYBRANCH.PARTY_ID
AND SYSDATE BETWEEN TRUNC(HZORGPROFILEBRANCH. EFFECTIVE_START_DATE)
AND NVL(TRUNC(HZORGPROFILEBRANCH.EFFECTIVE_END_DATE)
, SYSDATE+1)
AND HZCODEASSIGNMENTBRANCH.CLASS_CATEGORY = 'BANK_INSTITUTION_TYPE'
AND HZCODEASSIGNMENTBRANCH.CLASS_CODE = 'BANK_BRANCH'
AND HZCODEASSIGNMENTBRANCH.OWNER_TABLE_NAME = 'HZ_PARTIES'
AND HZCODEASSIGNMENTBRANCH.OWNER_TABLE_ID = HZPARTYBRANCH.PARTY_ID
AND SYSDATE BETWEEN TRUNC(HZCODEASSIGNMENTBRANCH. START_DATE_ACTIVE)
AND NVL(TRUNC(HZCODEASSIGNMENTBRANCH.END_DATE_ACTIVE)
, SYSDATE+1)
AND HZPARTYBANK.PARTY_TYPE = 'ORGANIZATION'
AND /*HZPARTYBANK.COUNTRY = 'BR' AND*/ HZPARTYBANK.STATUS = 'A'
AND HZORGPROFILEBANK.PARTY_ID = HZPARTYBANK.PARTY_ID
AND SYSDATE BETWEEN TRUNC(HZORGPROFILEBANK.EFFECTIVE_START_DATE)
AND NVL(TRUNC(HZORGPROFILEBANK.EFFECTIVE_END_DATE)
, SYSDATE+1)
AND SYSDATE BETWEEN TRUNC(HZCODEASSIGNMENTBANK. START_DATE_ACTIVE)
AND NVL(TRUNC(HZCODEASSIGNMENTBANK.END_DATE_ACTIVE)
, SYSDATE+1)
AND HZCODEASSIGNMENTBANK.CLASS_CATEGORY = 'BANK_INSTITUTION_TYPE'
AND HZCODEASSIGNMENTBANK.CLASS_CODE = 'BANK'
AND HZCODEASSIGNMENTBANK.OWNER_TABLE_NAME = 'HZ_PARTIES'
AND HZCODEASSIGNMENTBANK.OWNER_TABLE_ID = HZPARTYBANK.PARTY_ID
AND HZRELATIONSHIPBR.SUBJECT_ID = HZPARTYBRANCH.PARTY_ID
AND HZRELATIONSHIPBR.OBJECT_ID = HZPARTYBANK.PARTY_ID
AND HZRELATIONSHIPBR.RELATIONSHIP_CODE = 'BRANCH_OF'
AND HZRELATIONSHIPBR.RELATIONSHIP_TYPE = 'BANK_AND_BRANCH'
AND HZRELATIONSHIPBR.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
AND HZRELATIONSHIPBR.SUBJECT_TYPE = 'ORGANIZATION'
AND HZRELATIONSHIPBR.OBJECT_TABLE_NAME = 'HZ_PARTIES'
AND HZRELATIONSHIPBR.OBJECT_TYPE = 'ORGANIZATION'
AND (BI.BANK_INSTRUCTION_CODE (+) = SA.BANK_INSTRUCTION_CODE1
AND ((BI.BANK_PARTY_ID IS NULL) OR (HZPARTYBANK.PARTY_ID = BI.BANK_PARTY_ID)))
AND (BI2.BANK_INSTRUCTION_CODE (+) =SA.BANK_INSTRUCTION_CODE2
AND ((BI2.BANK_PARTY_ID IS NULL) OR (HZPARTYBANK.PARTY_ID = BI2.BANK_PARTY_ID)))
AND BS.BATCH_SOURCE_ID (+) = SA.BATCH_SOURCE_ID