View: JL_BR_AR_BORDEROS_V

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

View Text

SELECT A.BORDERO_ID
, A.SELECTION_CONTROL_ID
, A.BANK_ACCT_USE_ID
, A.BORDERO_STATUS
, A.SEQUENTIAL_NUMBER_GENERATION
, A.BORDERO_TYPE
, A.TOTAL_COUNT
, A.TOTAL_AMOUNT
, A.SELECTION_DATE
, A.REMITTANCE_DATE
, A.REFUSED_DATE
, A.CANCELLATION_DATE
, A.COLLECTION_DATE
, A.WRITE_OFF_DATE
, A.DATE_IN_RECEIPT
, A.RECEIVED_DATE
, A.OUTPUT_PROGRAM_ID
, A.SELECT_ACCOUNT_ID
, A.OUTPUT_FORMAT
, J.MEANING OUTPUT_FORMAT_DESC
, A.ORG_ID
, A.LAST_UPDATE_DATE
, A.LAST_UPDATED_BY
, A.CREATION_DATE
, A.CREATED_BY
, A.LAST_UPDATE_LOGIN
, HZPARTYBANK.PARTY_NAME BANK_NAME
, HZPARTYBRANCH.PARTY_NAME BANK_BRANCH_NAME
, HZORGPROFILEBRANCH.BANK_OR_BRANCH_NUMBER BRANCH_NUMBER
, HZORGPROFILEBANK.BANK_OR_BRANCH_NUMBER BANK_NUMBER
, HZPARTYBANK.PARTY_ID BANK_PARTY_ID
, D.MEANING BORDERO_TYPE_DESC
, E.MEANING BORDERO_STATUS_DESC
, F.GLOBAL_ATTRIBUTE1 AR_SYS_PARAM_GLOBAL_ATTRIBUTE1
, CEBANKACCOUNT.SECONDARY_ACCOUNT_REFERENCE AP_BNK_ACCTS_GLOBAL_ATTRIBUTE7
, I.PROGRAM_NAME
, CEBANKACCOUNT.BANK_ACCOUNT_NUM
FROM JL_BR_AR_BORDEROS A
, FND_LOOKUPS D
, FND_LOOKUPS E
, AR_SYSTEM_PARAMETERS F
, AP_PAYMENT_PROGRAMS I
, FND_LOOKUPS J
, CE_BANK_ACCOUNTS CEBANKACCOUNT
, CE_BANK_ACCT_USES_ALL 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 CEBANKACCOUNT.BANK_BRANCH_ID = HZPARTYBRANCH.PARTY_ID
AND ACCTUSE.BANK_ACCT_USE_ID = A.BANK_ACCT_USE_ID
AND ACCTUSE.BANK_ACCOUNT_ID = CEBANKACCOUNT.BANK_ACCOUNT_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.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 D.LOOKUP_TYPE = 'JLBR_AR_BORDERO_TYPE'
AND D.LOOKUP_CODE = A.BORDERO_TYPE
AND NVL(D.START_DATE_ACTIVE
, SYSDATE) <= SYSDATE
AND NVL(D.END_DATE_ACTIVE
, SYSDATE) >= SYSDATE
AND D.ENABLED_FLAG = 'Y'
AND E.LOOKUP_TYPE = 'JLBR_AR_BORDERO_STATUS'
AND E.LOOKUP_CODE = A.BORDERO_STATUS
AND NVL(E.START_DATE_ACTIVE
, SYSDATE) <= SYSDATE
AND NVL(E.END_DATE_ACTIVE
, SYSDATE) >= SYSDATE
AND E.ENABLED_FLAG = 'Y'
AND I.PROGRAM_ID = A.OUTPUT_PROGRAM_ID
AND J.LOOKUP_TYPE = 'JLBR_AR_REMITTANCE_MEDIA'
AND J.LOOKUP_CODE = A.OUTPUT_FORMAT
AND NVL(J.START_DATE_ACTIVE
, SYSDATE) <= SYSDATE
AND NVL(J.END_DATE_ACTIVE
, SYSDATE)>= SYSDATE
AND J.ENABLED_FLAG = 'Y'

Columns