View: JL_BR_AR_SELECT_ACCOUNTS_V

Product:	JL - Latin America Localizations
Description:
Implementation/DBA Data:	APPS.JL_BR_AR_SELECT_ACCOUNTS_V

View Text

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

Columns