View: JL_BR_AR_COLLECTION_DOCS_IOB_V

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

View Text

SELECT JLCOLL.ROWID ROW_ID
, JLCOLL.DOCUMENT_ID DOCUMENT_ID
, JLCOLL.BORDERO_ID BORDERO_ID
, JLCOLL.PAYMENT_SCHEDULE_ID PAYMENT_SCHEDULE_ID
, LV.LOOKUP_CODE DOCUMENT_STATUS
, LV.MEANING DOCUMENT_STATUS_MEANING
, JLCOLL.PREVIOUS_DOC_STATUS PREVIOUS_DOC_STATUS
, JLCOLL.PORTFOLIO_CODE PORTFOLIO_CODE
, JLCOLL.OUR_NUMBER OUR_NUMBER
, DT.MEANING DOC_TYPE_MEANING
, JLCOLL.BANK_ACCT_USE_ID BANK_ACCT_USE_ID
, NULL COLLECTOR_BANK_NUMBER
, NULL COLLECTOR_BRANCH_NUMBER
, JLCOLL.COLLECTOR_BANK_PARTY_ID COLLECTOR_BANK_PARTY_ID
, JLCOLL.COLLECTOR_BRANCH_PARTY_ID COLLECTOR_BRANCH_PARTY_ID
, JLCOLL.BANK_INSTRUCTION_CODE1 BANK_INSTRUCTION_CODE1
, JLCOLL.BANK_INSTRUCTION_CODE2 BANK_INSTRUCTION_CODE2
, JLCOLL.BANK_CHARGES_CCID BANK_CHARGES_CCID
, JLCOLL.CASH_CCID CASH_CCID
, HZPARTYBANK.PARTY_ID REMITTANCE_BANK_PARTY_ID
, HZORGPROFILEBANK.BANK_OR_BRANCH_NUMBER REMITTANCE_BANK_NUMBER
, HZPARTYBANK.PARTY_NAME REMITTANCE_BANK_NAME
, HZPARTYBRANCH.PARTY_ID REMITTANCE_BRANCH_PARTY_ID
, HZORGPROFILEBRANCH.BANK_OR_BRANCH_NUMBER REMITTANCE_BRANCH_NUMBER
, HZPARTYBRANCH.PARTY_NAME REMITTANCE_BRANCH_NAME
, CEBANKACCOUNT.BANK_ACCOUNT_NUM BANK_ACCOUNT_NUM
, BORD.SEQUENTIAL_NUMBER_GENERATION BORDERO_NUMBER
, BORD.REMITTANCE_DATE REMITTANCE_DATE
, JLCOLL.BANK_CHARGE_AMOUNT
, BI.DESCRIPTION BANK_INSTRUCTION1
, ABI.DESCRIPTION BANK_INSTRUCTION2
, JLCOLL.LAST_UPDATE_DATE LAST_UPDATE_DATE
, JLCOLL.LAST_UPDATED_BY LAST_UPDATED_BY
, JLCOLL.CREATION_DATE CREATION_DATE
, JLCOLL.CREATED_BY CREATED_BY
, JLCOLL.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
, JLCOLL.ORG_ID ORG_ID
FROM JL_BR_AR_COLLECTION_DOCS JLCOLL
, FND_LOOKUPS LV
, FND_LOOKUPS DT
, JL_BR_AR_BORDEROS_ALL BORD
, JL_BR_AR_BANK_INSTRUCTIONS BI
, JL_BR_AR_BANK_INSTRUCTIONS ABI
, 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 JLCOLL.BANK_ACCT_USE_ID = ACCTUSE.BANK_ACCT_USE_ID
AND CEBANKACCOUNT.BANK_ACCOUNT_ID = ACCTUSE.BANK_ACCOUNT_ID
AND CEBANKACCOUNT.BANK_BRANCH_ID = HZPARTYBRANCH.PARTY_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 JLCOLL.BORDERO_ID = BORD.BORDERO_ID
AND DT.LOOKUP_TYPE = 'JLBR_AR_DOCUMENT_TYPE'
AND DT.LOOKUP_CODE = JLCOLL.DOCUMENT_TYPE
AND LV.LOOKUP_TYPE = 'JLBR_AR_COLLECTION_DOCS_STATUS'
AND LV.LOOKUP_CODE = JLCOLL.DOCUMENT_STATUS
AND NVL(LV.START_DATE_ACTIVE
, SYSDATE) <= SYSDATE
AND NVL(LV.END_DATE_ACTIVE
, SYSDATE) >= SYSDATE
AND LV.ENABLED_FLAG = 'Y'
AND (JLCOLL.BANK_INSTRUCTION_CODE1 = BI.BANK_INSTRUCTION_CODE(+)
AND ((BI.BANK_PARTY_ID IS NULL) OR (HZPARTYBANK.PARTY_ID = BI.BANK_PARTY_ID)))
AND (JLCOLL.BANK_INSTRUCTION_CODE2 = ABI.BANK_INSTRUCTION_CODE (+)
AND ((ABI.BANK_PARTY_ID IS NULL) OR (HZPARTYBANK.PARTY_ID = ABI.BANK_PARTY_ID)))

Columns