JL_BR_AR_SELECT_ACCOUNTS

Details

Schema: FUSION
Object owner: JL
Object type: VIEW

Columns

Name
ROW_ID SELECT_ACCOUNT_ID SELECTION_CONTROL_ID BANK_ACCT_USE_ID GL_DATE SELECTION_DATE CANCELLATION_DATE FORMAT_DATE REMITTANCE_DATE PORTFOLIO_CODE MIN_DOCUMENT_AMOUNT MAX_DOCUMENT_AMOUNT MIN_REMITTANCE_AMOUNT MAX_REMITTANCE_AMOUNT BANK_INSTRUCTION_CODE1 BANK_INSTRUCTION_CODE2 BANK_CHARGE_AMOUNT BATCH_SOURCE_ID PERCENTAGE_DISTRIBUTION ORG_ID ATTRIBUTE_CATEGORY ATTRIBUTE1 ATTRIBUTE2 ATTRIBUTE3 ATTRIBUTE4 ATTRIBUTE5 ATTRIBUTE6 ATTRIBUTE7 ATTRIBUTE8 ATTRIBUTE9 ATTRIBUTE10 ATTRIBUTE11 ATTRIBUTE12 ATTRIBUTE13 ATTRIBUTE14 ATTRIBUTE15 LAST_UPDATE_DATE LAST_UPDATED_BY CREATION_DATE CREATED_BY LAST_UPDATE_LOGIN BANK_INSTRUCTION_TYPE1 BANK_INSTRUCTION_CODE1_DSP BANK_INSTRUCTION_TYPE2 BANK_INSTRUCTION_CODE2_DSP BANK_NUMBER BANK_PARTY_ID BANK_NAME BRANCH_NUMBER BRANCH_PARTY_ID BANK_BRANCH_NAME BANK_BRANCH_ID BATCH_SOURCE_ID_DSP BANK_ACCOUNT_NUM OBJECT_VERSION_NUMBER

Name

ROW_ID

SELECT_ACCOUNT_ID

SELECTION_CONTROL_ID

BANK_ACCT_USE_ID

GL_DATE

SELECTION_DATE

CANCELLATION_DATE

FORMAT_DATE

REMITTANCE_DATE

PORTFOLIO_CODE

MIN_DOCUMENT_AMOUNT

MAX_DOCUMENT_AMOUNT

MIN_REMITTANCE_AMOUNT

MAX_REMITTANCE_AMOUNT

BANK_INSTRUCTION_CODE1

BANK_INSTRUCTION_CODE2

BANK_CHARGE_AMOUNT

BATCH_SOURCE_ID

PERCENTAGE_DISTRIBUTION

ORG_ID

ATTRIBUTE_CATEGORY

ATTRIBUTE1

ATTRIBUTE2

ATTRIBUTE3

ATTRIBUTE4

ATTRIBUTE5

ATTRIBUTE6

ATTRIBUTE7

ATTRIBUTE8

ATTRIBUTE9

ATTRIBUTE10

ATTRIBUTE11

ATTRIBUTE12

ATTRIBUTE13

ATTRIBUTE14

ATTRIBUTE15

LAST_UPDATE_DATE

LAST_UPDATED_BY

CREATION_DATE

CREATED_BY

LAST_UPDATE_LOGIN

BANK_INSTRUCTION_TYPE1

BANK_INSTRUCTION_CODE1_DSP

BANK_INSTRUCTION_TYPE2

BANK_INSTRUCTION_CODE2_DSP

BANK_NUMBER

BANK_PARTY_ID

BANK_NAME

BRANCH_NUMBER

BRANCH_PARTY_ID

BANK_BRANCH_NAME

BANK_BRANCH_ID

BATCH_SOURCE_ID_DSP

BANK_ACCOUNT_NUM

OBJECT_VERSION_NUMBER

Query

SQL_Statement
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 ,SA.OBJECT_VERSION_NUMBER OBJECT_VERSION_NUMBER 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 trunc(SYSDATE) between TRUNC(HzOrgProfileBranch. effective_start_date) and NVL(TRUNC(HzOrgProfileBranch.effective_end_date), trunc(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 trunc(SYSDATE) between TRUNC(HzCodeAssignmentBranch. start_date_active) and NVL(TRUNC(HzCodeAssignmentBranch.end_date_active), trunc(SYSDATE)+1) and HzPartyBank.PARTY_TYPE = 'ORGANIZATION' and /HzPartyBank.Country = 'BR' and/ HzPartyBank.status = 'A' and HzOrgProfileBank.PARTY_ID = HzPartyBank.PARTY_ID and trunc(SYSDATE) between TRUNC(HzOrgProfileBank.effective_start_date) and NVL(TRUNC(HzOrgProfileBank.effective_end_date), trunc(SYSDATE)+1) and trunc(SYSDATE) between TRUNC(HzCodeAssignmentBank. start_date_active) and NVL(TRUNC(HzCodeAssignmentBank.end_date_active), trunc(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 AND BS.ORG_ID (+) = SA.ORG_ID

SQL_Statement

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 ,SA.OBJECT_VERSION_NUMBER OBJECT_VERSION_NUMBER 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 trunc(SYSDATE) between TRUNC(HzOrgProfileBranch. effective_start_date) and NVL(TRUNC(HzOrgProfileBranch.effective_end_date), trunc(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 trunc(SYSDATE) between TRUNC(HzCodeAssignmentBranch. start_date_active) and NVL(TRUNC(HzCodeAssignmentBranch.end_date_active), trunc(SYSDATE)+1) and HzPartyBank.PARTY_TYPE = 'ORGANIZATION' and /*HzPartyBank.Country = 'BR' and*/ HzPartyBank.status = 'A' and HzOrgProfileBank.PARTY_ID = HzPartyBank.PARTY_ID and trunc(SYSDATE) between TRUNC(HzOrgProfileBank.effective_start_date) and NVL(TRUNC(HzOrgProfileBank.effective_end_date), trunc(SYSDATE)+1) and trunc(SYSDATE) between TRUNC(HzCodeAssignmentBank. start_date_active) and NVL(TRUNC(HzCodeAssignmentBank.end_date_active), trunc(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 AND BS.ORG_ID (+) = SA.ORG_ID