JL_BR_AR_COLL_DOCS_SAB_V

Details

• Schema: FUSION

• Object owner: JL

• Object type: VIEW

Columns

Name

ROW_ID DOCUMENT_ID BORDERO_ID PAYMENT_SCHEDULE_ID DOCUMENT_STATUS ORIGIN_TYPE DUE_DATE SELECTION_DATE PORTFOLIO_CODE BATCH_SOURCE_ID RECEIPT_METHOD_ID CUSTOMER_TRX_ID TERMS_SEQUENCE_NUMBER DOCUMENT_TYPE BANK_ACCT_USE_ID PREVIOUS_DOC_STATUS OUR_NUMBER BANK_USE COLLECTOR_BANK_PARTY_ID COLLECTOR_BRANCH_PARTY_ID FACTORING_RATE FACTORING_RATE_PERIOD FACTORING_AMOUNT FACTORING_DATE CANCELLATION_DATE BANK_INSTRUCTION_CODE1 BANK_INSTRUCTION_CODE2 NUM_DAYS_INSTRUCTION BANK_CHARGE_AMOUNT CASH_CCID BANK_CHARGES_CCID COLL_ENDORSEMENTS_CCID BILLS_COLLECTION_CCID CALCULATED_INTEREST_CCID INTEREST_WRITEOFF_CCID ABATEMENT_WRITEOFF_CCID ABATEMENT_REVENUE_CCID INTEREST_REVENUE_CCID CALCULATED_INTEREST_RECTRX_ID INTEREST_WRITEOFF_RECTRX_ID INTEREST_REVENUE_RECTRX_ID ABATEMENT_WRITEOFF_RECTRX_ID ABATE_REVENUE_RECTRX_ID ORG_ID ATTRIBUTE_CATEGORY ATTRIBUTE1 ATTRIBUTE2 ATTRIBUTE3 ATTRIBUTE4 ATTRIBUTE5 ATTRIBUTE6 ATTRIBUTE7 ATTRIBUTE8 ATTRIBUTE9 ATTRIBUTE10 ATTRIBUTE11 ATTRIBUTE12 ATTRIBUTE13 ATTRIBUTE14 ATTRIBUTE15 LAST_UPDATE_DATE LAST_UPDATED_BY CREATION_DATE CREATED_BY LAST_UPDATE_LOGIN BANK_INSTRUCTION_TYPE1 BANK_INSTRUCTION_CODE1_DSP BANK_INSTRUCTION_TYPE2 BANK_INSTRUCTION_CODE2_DSP TRADE_NOTE_NUMBER_DSP AMOUNT_DSP TRX_NUMBER_DSP RECEIPT_METHOD_DSP BATCH_SOURCE_DSP DOCUMENT_STATUS_DSP 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 CD.ROWID ROW_ID , CD.DOCUMENT_ID DOCUMENT_ID , CD.BORDERO_ID BORDERO_ID , CD.PAYMENT_SCHEDULE_ID PAYMENT_SCHEDULE_ID , CD.DOCUMENT_STATUS DOCUMENT_STATUS , CD.ORIGIN_TYPE ORIGIN_TYPE , CD.DUE_DATE DUE_DATE , CD.SELECTION_DATE SELECTION_DATE , CD.PORTFOLIO_CODE PORTFOLIO_CODE , CD.BATCH_SOURCE_ID BATCH_SOURCE_ID , CD.RECEIPT_METHOD_ID RECEIPT_METHOD_ID , CD.CUSTOMER_TRX_ID CUSTOMER_TRX_ID , CD.TERMS_SEQUENCE_NUMBER TERMS_SEQUENCE_NUMBER , CD.DOCUMENT_TYPE DOCUMENT_TYPE , CD.BANK_ACCT_USE_ID BANK_ACCT_USE_ID , CD.PREVIOUS_DOC_STATUS PREVIOUS_DOC_STATUS , CD.OUR_NUMBER OUR_NUMBER , CD.BANK_USE BANK_USE , CD.COLLECTOR_BANK_PARTY_ID COLLECTOR_BANK_PARTY_ID , CD.COLLECTOR_BRANCH_PARTY_ID COLLECTOR_BRANCH_PARTY_ID , CD.FACTORING_RATE FACTORING_RATE , CD.FACTORING_RATE_PERIOD FACTORING_RATE_PERIOD , CD.FACTORING_AMOUNT FACTORING_AMOUNT , CD.FACTORING_DATE FACTORING_DATE , CD.CANCELLATION_DATE CANCELLATION_DATE , CD.BANK_INSTRUCTION_CODE1 BANK_INSTRUCTION_CODE1 , CD.BANK_INSTRUCTION_CODE2 BANK_INSTRUCTION_CODE2 , CD.NUM_DAYS_INSTRUCTION NUM_DAYS_INSTRUCTION , CD.BANK_CHARGE_AMOUNT BANK_CHARGE_AMOUNT , CD.CASH_CCID CASH_CCID , CD.BANK_CHARGES_CCID BANK_CHARGES_CCID , CD.COLL_ENDORSEMENTS_CCID COLL_ENDORSEMENTS_CCID , CD.BILLS_COLLECTION_CCID BILLS_COLLECTION_CCID , CD.CALCULATED_INTEREST_CCID CALCULATED_INTEREST_CCID , CD.INTEREST_WRITEOFF_CCID INTEREST_WRITEOFF_CCID , CD.ABATEMENT_WRITEOFF_CCID ABATEMENT_WRITEOFF_CCID , CD.ABATEMENT_REVENUE_CCID ABATEMENT_REVENUE_CCID , CD.INTEREST_REVENUE_CCID INTEREST_REVENUE_CCID , CD.CALCULATED_INTEREST_RECTRX_ID CALCULATED_INTEREST_RECTRX_ID , CD.INTEREST_WRITEOFF_RECTRX_ID INTEREST_WRITEOFF_RECTRX_ID , CD.INTEREST_REVENUE_RECTRX_ID INTEREST_REVENUE_RECTRX_ID , CD.ABATEMENT_WRITEOFF_RECTRX_ID ABATEMENT_WRITEOFF_RECTRX_ID , CD.ABATE_REVENUE_RECTRX_ID ABATE_REVENUE_RECTRX_ID , CD.ORG_ID ORG_ID , CD.ATTRIBUTE_CATEGORY ATTRIBUTE_CATEGORY , CD.ATTRIBUTE1 ATTRIBUTE1 , CD.ATTRIBUTE2 ATTRIBUTE2 , CD.ATTRIBUTE3 ATTRIBUTE3 , CD.ATTRIBUTE4 ATTRIBUTE4 , CD.ATTRIBUTE5 ATTRIBUTE5 , CD.ATTRIBUTE6 ATTRIBUTE6 , CD.ATTRIBUTE7 ATTRIBUTE7 , CD.ATTRIBUTE8 ATTRIBUTE8 , CD.ATTRIBUTE9 ATTRIBUTE9 , CD.ATTRIBUTE10 ATTRIBUTE10 , CD.ATTRIBUTE11 ATTRIBUTE11 , CD.ATTRIBUTE12 ATTRIBUTE12 , CD.ATTRIBUTE13 ATTRIBUTE13 , CD.ATTRIBUTE14 ATTRIBUTE14 , CD.ATTRIBUTE15 ATTRIBUTE15 , CD.LAST_UPDATE_DATE LAST_UPDATE_DATE , CD.LAST_UPDATED_BY LAST_UPDATED_BY , CD.CREATION_DATE CREATION_DATE , CD.CREATED_BY CREATED_BY , CD.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 , PS.TRX_NUMBER||'-'||TO_CHAR(PS.TERMS_SEQUENCE_NUMBER) TRADE_NOTE_NUMBER_DSP , PS.AMOUNT_DUE_REMAINING AMOUNT_DSP , CT.TRX_NUMBER TRX_NUMBER_DSP , RM.NAME RECEIPT_METHOD_DSP , BS.NAME BATCH_SOURCE_DSP , LV.MEANING DOCUMENT_STATUS_DSP ,CD.OBJECT_VERSION_NUMBER OBJECT_VERSION_NUMBER ,CD.COLL_BATCH_ID COLL_BATCH_ID, CD.BILL_TO_SITE_ID BILL_TO_SITE_ID, CD.PARTY_ID PARTY_ID, CD.INTEREST_TYPE INTEREST_TYPE ,CD.INTEREST_RATE_AMOUNT INTEREST_RATE_AMOUNT ,CD.INTEREST_PERIOD INTEREST_PERIOD , CD.INTEREST_FORMULA INTEREST_FORMULA , CD.GRACE_DAYS GRACE_DAYS , CD.PENALTY_TYPE PENALTY_TYPE , CD.PENALTY_RATE_AMOUNT PENALTY_RATE_AMOUNT FROM JL_BR_AR_COLLECTION_DOCS CD, JL_BR_AR_BANK_INSTRUCTIONS BI, JL_BR_AR_BANK_INSTRUCTIONS BI2, AR_PAYMENT_SCHEDULES_ALL PS, RA_CUSTOMER_TRX_ALL CT, AR_RECEIPT_METHODS RM, AR_BATCH_SOURCES BS, FND_LOOKUPS LV , 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 AcctUse.BANK_ACCT_USE_ID = CD.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 (BI.BANK_INSTRUCTION_CODE (+) = CD.BANK_INSTRUCTION_CODE1 and ((BI.BANK_PARTY_ID IS NULL) OR (HzPartyBank.PARTY_ID = BI.BANK_PARTY_ID))) and (BI2.BANK_INSTRUCTION_CODE (+) =CD.BANK_INSTRUCTION_CODE2 AND ((BI2.BANK_PARTY_ID IS NULL) OR (HzPartyBank.PARTY_ID = BI2.BANK_PARTY_ID))) and PS.PAYMENT_SCHEDULE_ID = CD.PAYMENT_SCHEDULE_ID AND CT.CUSTOMER_TRX_ID = PS.CUSTOMER_TRX_ID AND RM.RECEIPT_METHOD_ID = CD.RECEIPT_METHOD_ID AND BS.BATCH_SOURCE_ID = CD.BATCH_SOURCE_ID AND LV.LOOKUP_CODE (+) = CD.DOCUMENT_STATUS AND CD.DOCUMENT_STATUS <> 'CANCELED' AND LV.LOOKUP_TYPE (+) = 'JLBR_AR_COLLECTION_DOCS_STATUS' AND LV.ENABLED_FLAG = 'Y' AND NVL(LV.START_DATE_ACTIVE,trunc(SYSDATE)) <= trunc(SYSDATE) AND NVL(LV.END_DATE_ACTIVE,trunc(SYSDATE)) >= trunc(SYSDATE)