View: JL_BR_AR_COLL_DOCS_SAB_V

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

View Text

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

Columns