VIEW: APPS.JL_BR_AR_COLLECTION_DOCS_IOB_V

Source

View Text - Preformatted

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)))

View Text - HTML Formatted

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)))