VIEW: APPS.JL_BR_AR_COLL_DOC_DISTS_L_V

Source

View Text - Preformatted

SELECT dist.ORG_ID , dist.DISTRIBUTION_ID JLBR_DISTRIBUTION_ID, dist.DOCUMENT_ID JLBR_DOCUMENT_ID, dist.OCCURRENCE_ID JLBR_OCCURRENCE_ID, dist.DISTRIBUTION_TYPE JLBR_DISTRIBUTION_TYPE, dist.ENTERED_AMT JLBR_ENTERED_AMT, dist.ENTERED_CURRENCY_CODE JLBR_ENTERED_CURRENCY_CODE, dist.ACCTD_AMT JLBR_ACCTD_AMT, dist.CONVERSION_RATE JLBR_CONVERSION_RATE, dist.CONVERSION_DATE JLBR_CONVERSION_DATE, dist.CONVERSION_RATE_TYPE JLBR_CONVERSION_RATE_TYPE, dist.ACCOUNTING_REVERSAL_OPTION JLBR_ACCT_REVERSING_OPTN, dist.REVERSED_DIST_ID JLBR_REVERSED_DIST_ID , dist.REVERSED_DIST_LINK_TYPE JLBR_REVERSED_DIST_LINK_TYPE, dist.PRIOR_DIST_ID JLBR_PRIOR_DIST_ID, dist.PRIOR_DIST_LINK_TYPE JLBR_PRIOR_DIST_LINK_TYPE, priordist.OCCURRENCE_ID JLBR_PRIOR_OCCURRENCE_ID, priordist.DOCUMENT_ID JLBR_PRIOR_DOCUMENT_ID, dist.DISTRIBUTION_LINK_TYPE JLBR_DISTRIBUTION_LINK_TYPE, dist.EVENT_ID, dist.EVENT_DATE, dist.STD_OCCURRENCE_CODE JLBR_STD_OCC_CODE, dist.BORDERO_TYPE JLBR_BORDERO_TYPE, dist.BANK_OCCURRENCE_TYPE JLBR_BANK_OCC_TYPE, dist.DIST_LINE_NUMBER LINE_NUMBER, decode(dist.distribution_type,'JLBR_AR_ENDORSEMENT', decode(dist.bordero_type,'COLLECTION', decode(dist.std_occurrence_code,'REMITTANCE', occ.ENDORSEMENT_DEBIT_CCID, occ.ENDORSEMENT_CREDIT_CCID), 'OCCURRENCE', decode(dist.std_occurrence_code,'REMITTANCE', occ.ENDORSEMENT_DEBIT_CCID, occ.ENDORSEMENT_CREDIT_CCID),NULL), NULL) JLBR_CD_BIL_UND_COLL_CCID , decode(dist.distribution_type,'JLBR_AR_ENDORSEMENT', decode(dist.bordero_type,'FACTORING', decode(dist.std_occurrence_code,'REMITTANCE', occ.ENDORSEMENT_DEBIT_CCID, occ.ENDORSEMENT_CREDIT_CCID),NULL), NULL) JLBR_CD_BIL_UND_DISC_CCID , decode(dist.distribution_type,'JLBR_AR_ENDORSEMENT', decode(dist.bordero_type,'COLLECTION', decode(dist.std_occurrence_code,'REMITTANCE', occ.ENDORSEMENT_CREDIT_CCID, occ.ENDORSEMENT_DEBIT_CCID), 'OCCURRENCE', decode(dist.std_occurrence_code,'REMITTANCE', occ.ENDORSEMENT_CREDIT_CCID, occ.ENDORSEMENT_DEBIT_CCID),NULL), NULL) JLBR_CD_COLL_ENDRSMNT_CCID , decode(dist.distribution_type,'JLBR_AR_ENDORSEMENT', decode(dist.bordero_type,'FACTORING', decode(dist.std_occurrence_code,'REMITTANCE', occ.ENDORSEMENT_CREDIT_CCID, occ.ENDORSEMENT_DEBIT_CCID),NULL), NULL) JLBR_CD_DISC_ENDRSMNT_CCID , decode(dist.distribution_type,'JLBR_AR_BANK_CHARGES', decode(dist.ACCOUNTING_REVERSAL_OPTION,'N', occ.BANK_CHARGES_DEBIT_CCID, decode(dist.std_occurrence_code,'REMITTANCE', occ.BANK_CHARGES_DEBIT_CCID, occ.REVERSE_CHARGES_CRED_CCID)), NULL) JLBR_CD_BANK_CHARGES_CCID , decode(dist.distribution_type,'JLBR_AR_FACTORING_CHARGES', decode(dist.std_occurrence_code,'REMITTANCE', occ.FACTOR_INTEREST_DEBIT_CCID ,occ.FACTOR_INTEREST_CREDIT_CCID) ,NULL) JLBR_CD_FACT_CHARGES_CCID, decode(dist.distribution_type,'JLBR_AR_BANK_CHARGES', decode(dist.ACCOUNTING_REVERSAL_OPTION,'N', occ.BANK_CHARGES_CREDIT_CCID, decode(dist.std_occurrence_code,'REMITTANCE', occ.BANK_CHARGES_CREDIT_CCID, occ.REVERSE_CHARGES_DEB_CCID)), 'JLBR_AR_FACTORING_CHARGES', decode(dist.std_occurrence_code,'REMITTANCE', occ.FACTOR_INTEREST_CREDIT_CCID ,occ.FACTOR_INTEREST_DEBIT_CCID) ,NULL) JLBR_CD_CASH_CCID FROM jl_br_ar_distributns_all dist, jl_br_ar_occurrence_docs_all occ, jl_br_ar_distributns_all priordist WHERE occ.occurrence_id = dist.occurrence_id AND priordist.distribution_id(+) = nvl(dist.prior_dist_id,-88)

View Text - HTML Formatted

SELECT DIST.ORG_ID
, DIST.DISTRIBUTION_ID JLBR_DISTRIBUTION_ID
, DIST.DOCUMENT_ID JLBR_DOCUMENT_ID
, DIST.OCCURRENCE_ID JLBR_OCCURRENCE_ID
, DIST.DISTRIBUTION_TYPE JLBR_DISTRIBUTION_TYPE
, DIST.ENTERED_AMT JLBR_ENTERED_AMT
, DIST.ENTERED_CURRENCY_CODE JLBR_ENTERED_CURRENCY_CODE
, DIST.ACCTD_AMT JLBR_ACCTD_AMT
, DIST.CONVERSION_RATE JLBR_CONVERSION_RATE
, DIST.CONVERSION_DATE JLBR_CONVERSION_DATE
, DIST.CONVERSION_RATE_TYPE JLBR_CONVERSION_RATE_TYPE
, DIST.ACCOUNTING_REVERSAL_OPTION JLBR_ACCT_REVERSING_OPTN
, DIST.REVERSED_DIST_ID JLBR_REVERSED_DIST_ID
, DIST.REVERSED_DIST_LINK_TYPE JLBR_REVERSED_DIST_LINK_TYPE
, DIST.PRIOR_DIST_ID JLBR_PRIOR_DIST_ID
, DIST.PRIOR_DIST_LINK_TYPE JLBR_PRIOR_DIST_LINK_TYPE
, PRIORDIST.OCCURRENCE_ID JLBR_PRIOR_OCCURRENCE_ID
, PRIORDIST.DOCUMENT_ID JLBR_PRIOR_DOCUMENT_ID
, DIST.DISTRIBUTION_LINK_TYPE JLBR_DISTRIBUTION_LINK_TYPE
, DIST.EVENT_ID
, DIST.EVENT_DATE
, DIST.STD_OCCURRENCE_CODE JLBR_STD_OCC_CODE
, DIST.BORDERO_TYPE JLBR_BORDERO_TYPE
, DIST.BANK_OCCURRENCE_TYPE JLBR_BANK_OCC_TYPE
, DIST.DIST_LINE_NUMBER LINE_NUMBER
, DECODE(DIST.DISTRIBUTION_TYPE
, 'JLBR_AR_ENDORSEMENT'
, DECODE(DIST.BORDERO_TYPE
, 'COLLECTION'
, DECODE(DIST.STD_OCCURRENCE_CODE
, 'REMITTANCE'
, OCC.ENDORSEMENT_DEBIT_CCID
, OCC.ENDORSEMENT_CREDIT_CCID)
, 'OCCURRENCE'
, DECODE(DIST.STD_OCCURRENCE_CODE
, 'REMITTANCE'
, OCC.ENDORSEMENT_DEBIT_CCID
, OCC.ENDORSEMENT_CREDIT_CCID)
, NULL)
, NULL) JLBR_CD_BIL_UND_COLL_CCID
, DECODE(DIST.DISTRIBUTION_TYPE
, 'JLBR_AR_ENDORSEMENT'
, DECODE(DIST.BORDERO_TYPE
, 'FACTORING'
, DECODE(DIST.STD_OCCURRENCE_CODE
, 'REMITTANCE'
, OCC.ENDORSEMENT_DEBIT_CCID
, OCC.ENDORSEMENT_CREDIT_CCID)
, NULL)
, NULL) JLBR_CD_BIL_UND_DISC_CCID
, DECODE(DIST.DISTRIBUTION_TYPE
, 'JLBR_AR_ENDORSEMENT'
, DECODE(DIST.BORDERO_TYPE
, 'COLLECTION'
, DECODE(DIST.STD_OCCURRENCE_CODE
, 'REMITTANCE'
, OCC.ENDORSEMENT_CREDIT_CCID
, OCC.ENDORSEMENT_DEBIT_CCID)
, 'OCCURRENCE'
, DECODE(DIST.STD_OCCURRENCE_CODE
, 'REMITTANCE'
, OCC.ENDORSEMENT_CREDIT_CCID
, OCC.ENDORSEMENT_DEBIT_CCID)
, NULL)
, NULL) JLBR_CD_COLL_ENDRSMNT_CCID
, DECODE(DIST.DISTRIBUTION_TYPE
, 'JLBR_AR_ENDORSEMENT'
, DECODE(DIST.BORDERO_TYPE
, 'FACTORING'
, DECODE(DIST.STD_OCCURRENCE_CODE
, 'REMITTANCE'
, OCC.ENDORSEMENT_CREDIT_CCID
, OCC.ENDORSEMENT_DEBIT_CCID)
, NULL)
, NULL) JLBR_CD_DISC_ENDRSMNT_CCID
, DECODE(DIST.DISTRIBUTION_TYPE
, 'JLBR_AR_BANK_CHARGES'
, DECODE(DIST.ACCOUNTING_REVERSAL_OPTION
, 'N'
, OCC.BANK_CHARGES_DEBIT_CCID
, DECODE(DIST.STD_OCCURRENCE_CODE
, 'REMITTANCE'
, OCC.BANK_CHARGES_DEBIT_CCID
, OCC.REVERSE_CHARGES_CRED_CCID))
, NULL) JLBR_CD_BANK_CHARGES_CCID
, DECODE(DIST.DISTRIBUTION_TYPE
, 'JLBR_AR_FACTORING_CHARGES'
, DECODE(DIST.STD_OCCURRENCE_CODE
, 'REMITTANCE'
, OCC.FACTOR_INTEREST_DEBIT_CCID
, OCC.FACTOR_INTEREST_CREDIT_CCID)
, NULL) JLBR_CD_FACT_CHARGES_CCID
, DECODE(DIST.DISTRIBUTION_TYPE
, 'JLBR_AR_BANK_CHARGES'
, DECODE(DIST.ACCOUNTING_REVERSAL_OPTION
, 'N'
, OCC.BANK_CHARGES_CREDIT_CCID
, DECODE(DIST.STD_OCCURRENCE_CODE
, 'REMITTANCE'
, OCC.BANK_CHARGES_CREDIT_CCID
, OCC.REVERSE_CHARGES_DEB_CCID))
, 'JLBR_AR_FACTORING_CHARGES'
, DECODE(DIST.STD_OCCURRENCE_CODE
, 'REMITTANCE'
, OCC.FACTOR_INTEREST_CREDIT_CCID
, OCC.FACTOR_INTEREST_DEBIT_CCID)
, NULL) JLBR_CD_CASH_CCID
FROM JL_BR_AR_DISTRIBUTNS_ALL DIST
, JL_BR_AR_OCCURRENCE_DOCS_ALL OCC
, JL_BR_AR_DISTRIBUTNS_ALL PRIORDIST
WHERE OCC.OCCURRENCE_ID = DIST.OCCURRENCE_ID
AND PRIORDIST.DISTRIBUTION_ID(+) = NVL(DIST.PRIOR_DIST_ID
, -88)