JL_BR_AR_COLLECTION_DOCS_IOB

Details

Schema: FUSION
Object owner: JL
Object type: VIEW

Columns

Name
ROW_ID DOCUMENT_ID BORDERO_ID PAYMENT_SCHEDULE_ID DOCUMENT_STATUS DOCUMENT_STATUS_MEANING PREVIOUS_DOC_STATUS PORTFOLIO_CODE OUR_NUMBER DOC_TYPE_MEANING BANK_ACCT_USE_ID COLLECTOR_BANK_NUMBER COLLECTOR_BRANCH_NUMBER COLLECTOR_BANK_PARTY_ID COLLECTOR_BRANCH_PARTY_ID BANK_INSTRUCTION_CODE1 BANK_INSTRUCTION_CODE2 BANK_CHARGES_CCID CASH_CCID REMITTANCE_BANK_PARTY_ID REMITTANCE_BANK_NUMBER REMITTANCE_BANK_NAME REMITTANCE_BRANCH_PARTY_ID REMITTANCE_BRANCH_NUMBER REMITTANCE_BRANCH_NAME BANK_ACCOUNT_NUM BORDERO_NUMBER REMITTANCE_DATE BANK_CHARGE_AMOUNT BANK_INSTRUCTION1 BANK_INSTRUCTION2 LAST_UPDATE_DATE LAST_UPDATED_BY CREATION_DATE CREATED_BY LAST_UPDATE_LOGIN ORG_ID OBJECT_VERSION_NUMBER COLL_BATCH_ID BILL_TO_SITE_ID PARTY_ID INTEREST_TYPE INTEREST_RATE_AMOUNT INTEREST_PERIOD INTEREST_FORMULA GRACE_DAYS PENALTY_TYPE PENALTY_RATE_AMOUNT

Name

ROW_ID

DOCUMENT_ID

BORDERO_ID

PAYMENT_SCHEDULE_ID

DOCUMENT_STATUS

DOCUMENT_STATUS_MEANING

PREVIOUS_DOC_STATUS

PORTFOLIO_CODE

OUR_NUMBER

DOC_TYPE_MEANING

BANK_ACCT_USE_ID

COLLECTOR_BANK_NUMBER

COLLECTOR_BRANCH_NUMBER

COLLECTOR_BANK_PARTY_ID

COLLECTOR_BRANCH_PARTY_ID

BANK_INSTRUCTION_CODE1

BANK_INSTRUCTION_CODE2

BANK_CHARGES_CCID

CASH_CCID

REMITTANCE_BANK_PARTY_ID

REMITTANCE_BANK_NUMBER

REMITTANCE_BANK_NAME

REMITTANCE_BRANCH_PARTY_ID

REMITTANCE_BRANCH_NUMBER

REMITTANCE_BRANCH_NAME

BANK_ACCOUNT_NUM

BORDERO_NUMBER

REMITTANCE_DATE

BANK_CHARGE_AMOUNT

BANK_INSTRUCTION1

BANK_INSTRUCTION2

LAST_UPDATE_DATE

LAST_UPDATED_BY

CREATION_DATE

CREATED_BY

LAST_UPDATE_LOGIN

ORG_ID

OBJECT_VERSION_NUMBER

COLL_BATCH_ID

BILL_TO_SITE_ID

PARTY_ID

INTEREST_TYPE

INTEREST_RATE_AMOUNT

INTEREST_PERIOD

INTEREST_FORMULA

GRACE_DAYS

PENALTY_TYPE

PENALTY_RATE_AMOUNT

Query

SQL_Statement
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 ,JLCOLL.OBJECT_VERSION_NUMBER OBJECT_VERSION_NUMBER ,JLCOLL.COLL_BATCH_ID COLL_BATCH_ID, JLCOLL.BILL_TO_SITE_ID BILL_TO_SITE_ID, JLCOLL.PARTY_ID PARTY_ID, JLCOLL.INTEREST_TYPE INTEREST_TYPE ,JLCOLL.INTEREST_RATE_AMOUNT INTEREST_RATE_AMOUNT ,JLCOLL.INTEREST_PERIOD INTEREST_PERIOD , JLCOLL.INTEREST_FORMULA INTEREST_FORMULA , JLCOLL.GRACE_DAYS GRACE_DAYS , JLCOLL.PENALTY_TYPE PENALTY_TYPE , JLCOLL.PENALTY_RATE_AMOUNT PENALTY_RATE_AMOUNT 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 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.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 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,trunc(SYSDATE)) <= trunc(SYSDATE) AND NVL(LV.END_DATE_ACTIVE,trunc(SYSDATE)) >= trunc(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)))

SQL_Statement

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 ,JLCOLL.OBJECT_VERSION_NUMBER OBJECT_VERSION_NUMBER ,JLCOLL.COLL_BATCH_ID COLL_BATCH_ID, JLCOLL.BILL_TO_SITE_ID BILL_TO_SITE_ID, JLCOLL.PARTY_ID PARTY_ID, JLCOLL.INTEREST_TYPE INTEREST_TYPE ,JLCOLL.INTEREST_RATE_AMOUNT INTEREST_RATE_AMOUNT ,JLCOLL.INTEREST_PERIOD INTEREST_PERIOD ,

JLCOLL.INTEREST_FORMULA INTEREST_FORMULA , JLCOLL.GRACE_DAYS GRACE_DAYS , JLCOLL.PENALTY_TYPE PENALTY_TYPE , JLCOLL.PENALTY_RATE_AMOUNT PENALTY_RATE_AMOUNT

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 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.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 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,trunc(SYSDATE)) <= trunc(SYSDATE) AND NVL(LV.END_DATE_ACTIVE,trunc(SYSDATE)) >= trunc(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)))