VIEW: APPS.LNS_INT_TRX_ACTIVITIES_V

Source

View Text - Preformatted

SELECT loan.loan_id ,to_number(null) ,0 ,trunc(loan.LOAN_START_DATE) ,to_number(null) ,to_number(null) ,to_number(null) ,to_number(null) ,to_number(null) ,to_number(null) ,null ,'START' ,0 ,null ,trunc(loan.LOAN_START_DATE) ,0 ,0 ,0 ,loan.ORG_ID ,0 ,(select nvl(rates.current_interest_rate, 0) from lns_rate_schedules rates, lns_terms term WHERE term.loan_id = loan.loan_id and term.term_id = rates.term_id and nvl(rates.phase, 'TERM') = 'TERM' and 1 between rates.begin_installment_number and rates.end_installment_number and rates.END_DATE_ACTIVE is null) from lns_loan_headers_all loan UNION ALL select loan.loan_id ,am.AMORTIZATION_SCHEDULE_ID ,am.PAYMENT_NUMBER ,trunc(am.DUE_DATE) ,am.PRINCIPAL_AMOUNT ,am.INTEREST_AMOUNT ,am.FEE_AMOUNT ,am.PARENT_AMORTIZATION_ID ,am.interest_trx_id ,to_number(null) ,null ,'BILLING' ,am.INTEREST_AMOUNT ,null ,decode(sign(trunc(am.CREATION_DATE)-trunc(am.DUE_DATE)), 1, trunc(am.DUE_DATE), trunc(am.CREATION_DATE)) ,(select nvl(sum(rec1.amount_applied), 0) from ar_receivable_applications_all rec1, RA_CUSTOMER_TRX_ALL trx1, LNS_AMORTIZATION_SCHEDS am1 where rec1.application_type in ('CASH', 'CM') and rec1.CUSTOMER_TRX_ID = trx1.CUSTOMER_TRX_ID(+) and trunc(rec1.apply_date) < decode(sign(trunc(am.CREATION_DATE)-trunc(am.DUE_DATE)), 1, trunc(am.DUE_DATE), trunc(am.CREATION_DATE)) and rec1.APPLIED_CUSTOMER_TRX_ID = am1.interest_trx_id and am1.loan_id = loan.loan_id and am1.interest_trx_id is not null and (am1.REVERSED_FLAG is null or am1.REVERSED_FLAG = 'N') and am1.REAMORTIZATION_AMOUNT is null and nvl(am1.phase, 'TERM') = 'TERM') - (select nvl(sum(adj1.amount), 0) from ar_adjustments_all adj1, LNS_AMORTIZATION_SCHEDS am1 where trunc(adj1.apply_date) < decode(sign(trunc(am.CREATION_DATE)-trunc(am.DUE_DATE)), 1, trunc(am.DUE_DATE), trunc(am.CREATION_DATE)) and adj1.CUSTOMER_TRX_ID = am1.interest_trx_id and am1.loan_id = loan.loan_id and am1.interest_trx_id is not null and (am1.REVERSED_FLAG is null or am1.REVERSED_FLAG = 'N') and am1.REAMORTIZATION_AMOUNT is null and nvl(am1.phase, 'TERM') = 'TERM') - (select nvl(sum(psa.TAX_ORIGINAL), 0) from LNS_AMORTIZATION_SCHEDS am1, ar_payment_schedules_all psa where am1.LOAN_ID = loan.loan_id and trunc(am1.DUE_DATE) < decode(sign(trunc(am.CREATION_DATE)-trunc(am.DUE_DATE)), 1, trunc(am.DUE_DATE), trunc(am.CREATION_DATE)) and (am1.REVERSED_FLAG is null or am1.REVERSED_FLAG = 'N') and am1.REAMORTIZATION_AMOUNT is null and nvl(am1.phase, 'TERM') = 'TERM' and psa.customer_trx_id = am1.interest_trx_id) ,(select nvl(sum(am1.INTEREST_AMOUNT), 0) from LNS_AMORTIZATION_SCHEDS am1 where am1.LOAN_ID = am.loan_id and trunc(am1.DUE_DATE) < decode(sign(trunc(am.CREATION_DATE)-trunc(am.DUE_DATE)), 1, trunc(am.DUE_DATE), trunc(am.CREATION_DATE)) and (am1.REVERSED_FLAG is null or am1.REVERSED_FLAG = 'N')and am1.REAMORTIZATION_AMOUNT is null and nvl(am1.phase, 'TERM') = 'TERM') ,0 ,loan.ORG_ID ,decode(am.PARENT_AMORTIZATION_ID, null, 1, 2) ,(select nvl(rates.current_interest_rate, 0) from lns_rate_schedules rates, lns_terms term where term.loan_id = loan.loan_id and term.term_id = rates.term_id and nvl(rates.phase, 'TERM') = 'TERM' and decode(am.PAYMENT_NUMBER, 0, 1, am.PAYMENT_NUMBER) between rates.begin_installment_number and rates.end_installment_number and rates.END_DATE_ACTIVE is null) from lns_loan_headers_all loan ,LNS_AMORTIZATION_SCHEDS am where loan.loan_id = am.loan_id and (am.REVERSED_FLAG is null or am.REVERSED_FLAG = 'N') and am.REAMORTIZATION_AMOUNT is null and nvl(am.phase, 'TERM') = 'TERM' UNION ALL select loan.loan_id ,am.AMORTIZATION_SCHEDULE_ID ,am.PAYMENT_NUMBER ,trunc(am.DUE_DATE) ,am.PRINCIPAL_AMOUNT ,am.INTEREST_AMOUNT ,am.FEE_AMOUNT ,am.PARENT_AMORTIZATION_ID ,am.interest_trx_id ,null ,null ,'DUE' ,am.INTEREST_AMOUNT ,null ,trunc(am.DUE_DATE) ,(select nvl(sum(rec1.amount_applied), 0) from ar_receivable_applications_all rec1, RA_CUSTOMER_TRX_ALL trx1, LNS_AMORTIZATION_SCHEDS am1 where rec1.application_type in ('CASH', 'CM') and rec1.CUSTOMER_TRX_ID = trx1.CUSTOMER_TRX_ID(+) and trunc(rec1.apply_date) <= trunc(am.DUE_DATE) and rec1.APPLIED_CUSTOMER_TRX_ID = am1.interest_trx_id and am1.loan_id = loan.loan_id and am1.interest_trx_id is not null and (am1.REVERSED_FLAG is null or am1.REVERSED_FLAG = 'N') and am1.REAMORTIZATION_AMOUNT is null and nvl(am1.phase, 'TERM') = 'TERM') - (select nvl(sum(adj1.amount), 0) from ar_adjustments_all adj1, LNS_AMORTIZATION_SCHEDS am1 where trunc(adj1.apply_date) <= trunc(am.DUE_DATE) and adj1.CUSTOMER_TRX_ID = am1.interest_trx_id and am1.loan_id = loan.loan_id and am1.interest_trx_id is not null and (am1.REVERSED_FLAG is null or am1.REVERSED_FLAG = 'N') and am1.REAMORTIZATION_AMOUNT is null and nvl(am1.phase, 'TERM') = 'TERM') - (select nvl(sum(psa.TAX_ORIGINAL), 0) from LNS_AMORTIZATION_SCHEDS am1, ar_payment_schedules_all psa where am1.LOAN_ID = loan.loan_id and trunc(am1.DUE_DATE) <= trunc(am.DUE_DATE) and (am1.REVERSED_FLAG is null or am1.REVERSED_FLAG = 'N') and am1.REAMORTIZATION_AMOUNT is null and nvl(am1.phase, 'TERM') = 'TERM' and psa.customer_trx_id = am1.interest_trx_id) ,(select nvl(sum(am1.INTEREST_AMOUNT), 0) from LNS_AMORTIZATION_SCHEDS am1 where am1.LOAN_ID = am.loan_id and trunc(am1.DUE_DATE) <= trunc(am.DUE_DATE) and (am1.REVERSED_FLAG is null or am1.REVERSED_FLAG = 'N') and am1.REAMORTIZATION_AMOUNT is null and nvl(am1.phase, 'TERM') = 'TERM') ,0 ,loan.ORG_ID ,decode(am.PARENT_AMORTIZATION_ID, null, 10, 11) ,(select nvl(rates.current_interest_rate, 0) from lns_rate_schedules rates, lns_terms term where term.loan_id = loan.loan_id and term.term_id = rates.term_id and nvl(rates.phase, 'TERM') = 'TERM' and decode(am.PAYMENT_NUMBER, 0, 1, am.PAYMENT_NUMBER) between rates.begin_installment_number and rates.end_installment_number and rates.END_DATE_ACTIVE is null) from lns_loan_headers_all loan ,LNS_AMORTIZATION_SCHEDS am where loan.loan_id = am.loan_id and (am.REVERSED_FLAG is null or am.REVERSED_FLAG = 'N') and am.REAMORTIZATION_AMOUNT is null UNION ALL select loan.loan_id ,am.AMORTIZATION_SCHEDULE_ID ,am.PAYMENT_NUMBER ,trunc(am.DUE_DATE) ,am.PRINCIPAL_AMOUNT ,am.INTEREST_AMOUNT ,am.FEE_AMOUNT ,am.PARENT_AMORTIZATION_ID ,trx.customer_trx_id ,trx_psa.payment_schedule_id ,trx.trx_number ,rec_psa.class ,-rec.amount_applied ,rec_psa.trx_number ,trunc(rec.apply_date) ,(select nvl(sum(rec1.amount_applied), 0) from ar_receivable_applications_all rec1, RA_CUSTOMER_TRX_ALL trx1, LNS_AMORTIZATION_SCHEDS am1 where rec1.application_type in ('CASH', 'CM') and rec1.CUSTOMER_TRX_ID = trx1.CUSTOMER_TRX_ID(+) and trunc(rec1.apply_date) <= trunc(rec.apply_date) and rec1.APPLIED_CUSTOMER_TRX_ID = am1.interest_trx_id and am1.loan_id = loan.loan_id and am1.interest_trx_id is not null and (am1.REVERSED_FLAG is null or am1.REVERSED_FLAG = 'N') and am1.REAMORTIZATION_AMOUNT is null and nvl(am1.phase, 'TERM') = 'TERM') - (select nvl(sum(adj1.amount), 0) from ar_adjustments_all adj1, LNS_AMORTIZATION_SCHEDS am1 where trunc(adj1.apply_date) <= trunc(rec.apply_date) and adj1.CUSTOMER_TRX_ID = am1.interest_trx_id and am1.loan_id = loan.loan_id and am1.interest_trx_id is not null and (am1.REVERSED_FLAG is null or am1.REVERSED_FLAG = 'N') and am1.REAMORTIZATION_AMOUNT is null and nvl(am1.phase, 'TERM') = 'TERM') - (select nvl(sum(psa.TAX_ORIGINAL), 0) from LNS_AMORTIZATION_SCHEDS am1, ar_payment_schedules_all psa where am1.LOAN_ID = loan.loan_id and trunc(am1.DUE_DATE) <= trunc(rec.apply_date) and (am1.REVERSED_FLAG is null or am1.REVERSED_FLAG = 'N') and am1.REAMORTIZATION_AMOUNT is null and nvl(am1.phase, 'TERM') = 'TERM' and psa.customer_trx_id = am1.interest_trx_id) ,(select nvl(sum(am1.INTEREST_AMOUNT), 0) from LNS_AMORTIZATION_SCHEDS am1 where am1.LOAN_ID = am.loan_id and trunc(am1.DUE_DATE) <= trunc(rec.apply_date) and (am1.REVERSED_FLAG is null or am1.REVERSED_FLAG = 'N') and am1.REAMORTIZATION_AMOUNT is null and nvl(am1.phase, 'TERM') = 'TERM') ,trunc(rec.apply_date)-trunc(am.DUE_DATE) ,loan.ORG_ID ,5 ,(select nvl(rates.current_interest_rate, 0) from lns_rate_schedules rates, lns_terms term where term.loan_id = loan.loan_id and term.term_id = rates.term_id and nvl(rates.phase, 'TERM') = 'TERM' and decode(am.PAYMENT_NUMBER, 0, 1, am.PAYMENT_NUMBER) between rates.begin_installment_number and rates.end_installment_number and rates.END_DATE_ACTIVE is null) from RA_CUSTOMER_TRX_ALL trx ,lns_loan_headers_all loan ,LNS_AMORTIZATION_SCHEDS am ,ar_payment_schedules_all trx_psa ,ar_payment_schedules_all rec_psa ,ar_receivable_applications_all rec where loan.loan_id = am.loan_id and (am.REVERSED_FLAG is null or am.REVERSED_FLAG = 'N') and am.REAMORTIZATION_AMOUNT is null and trx.customer_trx_id = am.interest_trx_id and trx.customer_trx_id = trx_psa.customer_trx_id and trx.customer_trx_id = rec.APPLIED_CUSTOMER_TRX_ID and rec.payment_schedule_id = rec_psa.payment_schedule_id and rec.display = 'Y' UNION ALL select loan.loan_id ,am.AMORTIZATION_SCHEDULE_ID ,am.PAYMENT_NUMBER ,trunc(am.DUE_DATE) ,am.PRINCIPAL_AMOUNT ,am.INTEREST_AMOUNT ,am.FEE_AMOUNT ,am.PARENT_AMORTIZATION_ID ,trx.customer_trx_id ,trx_psa.payment_schedule_id ,trx.trx_number ,'ADJUSTMENT' ,adj.amount ,adj.adjustment_number ,trunc(adj.apply_date) ,(select nvl(sum(rec1.amount_applied), 0) from ar_receivable_applications_all rec1, RA_CUSTOMER_TRX_ALL trx1, LNS_AMORTIZATION_SCHEDS am1 where rec1.application_type in ('CASH', 'CM') and rec1.CUSTOMER_TRX_ID = trx1.CUSTOMER_TRX_ID(+) and trunc(rec1.apply_date) <= trunc(adj.apply_date) and rec1.APPLIED_CUSTOMER_TRX_ID = am1.interest_trx_id and am1.loan_id = loan.loan_id and am1.interest_trx_id is not null and (am1.REVERSED_FLAG is null or am1.REVERSED_FLAG = 'N') and am1.REAMORTIZATION_AMOUNT is null and nvl(am1.phase, 'TERM') = 'TERM') - (select nvl(sum(adj1.amount), 0) from ar_adjustments_all adj1, LNS_AMORTIZATION_SCHEDS am1 where trunc(adj1.apply_date) <= trunc(adj.apply_date) and adj1.CUSTOMER_TRX_ID = am1.interest_trx_id and am1.loan_id = loan.loan_id and am1.interest_trx_id is not null and (am1.REVERSED_FLAG is null or am1.REVERSED_FLAG = 'N') and am1.REAMORTIZATION_AMOUNT is null and nvl(am1.phase, 'TERM') = 'TERM') - (select nvl(sum(psa.TAX_ORIGINAL), 0) from LNS_AMORTIZATION_SCHEDS am1, ar_payment_schedules_all psa where am1.LOAN_ID = loan.loan_id and trunc(am1.DUE_DATE) <= trunc(adj.apply_date) and (am1.REVERSED_FLAG is null or am1.REVERSED_FLAG = 'N') and am1.REAMORTIZATION_AMOUNT is null and nvl(am1.phase, 'TERM') = 'TERM' and psa.customer_trx_id = am1.interest_trx_id) ,(select nvl(sum(am1.INTEREST_AMOUNT), 0) from LNS_AMORTIZATION_SCHEDS am1 where am1.LOAN_ID = am.loan_id and trunc(am1.DUE_DATE) <= trunc(adj.apply_date) and (am1.REVERSED_FLAG is null or am1.REVERSED_FLAG = 'N') and am1.REAMORTIZATION_AMOUNT is null and nvl(am1.phase, 'TERM') = 'TERM') ,trunc(adj.apply_date)-trunc(am.DUE_DATE) ,loan.ORG_ID ,5 ,(select nvl(rates.current_interest_rate, 0) from lns_rate_schedules rates, lns_terms term where term.loan_id = loan.loan_id and term.term_id = rates.term_id and nvl(rates.phase, 'TERM') = 'TERM' and decode(am.PAYMENT_NUMBER, 0, 1, am.PAYMENT_NUMBER) between rates.begin_installment_number and rates.end_installment_number and rates.END_DATE_ACTIVE is null) from RA_CUSTOMER_TRX_ALL trx ,ar_payment_schedules_all trx_psa ,ar_adjustments_all adj ,AR_RECEIVABLES_TRX_ALL rec_trx ,lns_loan_headers_all loan ,LNS_AMORTIZATION_SCHEDS am where loan.loan_id = am.loan_id and trx.customer_trx_id = am.interest_trx_id and trx.customer_trx_id = trx_psa.customer_trx_id and trx_psa.payment_schedule_id = adj.payment_schedule_id and adj.RECEIVABLES_TRX_ID = rec_trx.RECEIVABLES_TRX_ID and adj.ORG_ID = rec_trx.ORG_ID and adj.postable = 'Y' and adj.status = 'A'

View Text - HTML Formatted

SELECT LOAN.LOAN_ID
, TO_NUMBER(NULL)
, 0
, TRUNC(LOAN.LOAN_START_DATE)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, NULL
, 'START'
, 0
, NULL
, TRUNC(LOAN.LOAN_START_DATE)
, 0
, 0
, 0
, LOAN.ORG_ID
, 0
, (SELECT NVL(RATES.CURRENT_INTEREST_RATE
, 0)
FROM LNS_RATE_SCHEDULES RATES
, LNS_TERMS TERM
WHERE TERM.LOAN_ID = LOAN.LOAN_ID
AND TERM.TERM_ID = RATES.TERM_ID
AND NVL(RATES.PHASE
, 'TERM') = 'TERM'
AND 1 BETWEEN RATES.BEGIN_INSTALLMENT_NUMBER
AND RATES.END_INSTALLMENT_NUMBER
AND RATES.END_DATE_ACTIVE IS NULL)
FROM LNS_LOAN_HEADERS_ALL LOAN UNION ALL SELECT LOAN.LOAN_ID
, AM.AMORTIZATION_SCHEDULE_ID
, AM.PAYMENT_NUMBER
, TRUNC(AM.DUE_DATE)
, AM.PRINCIPAL_AMOUNT
, AM.INTEREST_AMOUNT
, AM.FEE_AMOUNT
, AM.PARENT_AMORTIZATION_ID
, AM.INTEREST_TRX_ID
, TO_NUMBER(NULL)
, NULL
, 'BILLING'
, AM.INTEREST_AMOUNT
, NULL
, DECODE(SIGN(TRUNC(AM.CREATION_DATE)-TRUNC(AM.DUE_DATE))
, 1
, TRUNC(AM.DUE_DATE)
, TRUNC(AM.CREATION_DATE))
, (SELECT NVL(SUM(REC1.AMOUNT_APPLIED)
, 0)
FROM AR_RECEIVABLE_APPLICATIONS_ALL REC1
, RA_CUSTOMER_TRX_ALL TRX1
, LNS_AMORTIZATION_SCHEDS AM1
WHERE REC1.APPLICATION_TYPE IN ('CASH'
, 'CM')
AND REC1.CUSTOMER_TRX_ID = TRX1.CUSTOMER_TRX_ID(+)
AND TRUNC(REC1.APPLY_DATE) < DECODE(SIGN(TRUNC(AM.CREATION_DATE)-TRUNC(AM.DUE_DATE))
, 1
, TRUNC(AM.DUE_DATE)
, TRUNC(AM.CREATION_DATE))
AND REC1.APPLIED_CUSTOMER_TRX_ID = AM1.INTEREST_TRX_ID
AND AM1.LOAN_ID = LOAN.LOAN_ID
AND AM1.INTEREST_TRX_ID IS NOT NULL
AND (AM1.REVERSED_FLAG IS NULL OR AM1.REVERSED_FLAG = 'N')
AND AM1.REAMORTIZATION_AMOUNT IS NULL
AND NVL(AM1.PHASE
, 'TERM') = 'TERM') - (SELECT NVL(SUM(ADJ1.AMOUNT)
, 0)
FROM AR_ADJUSTMENTS_ALL ADJ1
, LNS_AMORTIZATION_SCHEDS AM1
WHERE TRUNC(ADJ1.APPLY_DATE) < DECODE(SIGN(TRUNC(AM.CREATION_DATE)-TRUNC(AM.DUE_DATE))
, 1
, TRUNC(AM.DUE_DATE)
, TRUNC(AM.CREATION_DATE))
AND ADJ1.CUSTOMER_TRX_ID = AM1.INTEREST_TRX_ID
AND AM1.LOAN_ID = LOAN.LOAN_ID
AND AM1.INTEREST_TRX_ID IS NOT NULL
AND (AM1.REVERSED_FLAG IS NULL OR AM1.REVERSED_FLAG = 'N')
AND AM1.REAMORTIZATION_AMOUNT IS NULL
AND NVL(AM1.PHASE
, 'TERM') = 'TERM') - (SELECT NVL(SUM(PSA.TAX_ORIGINAL)
, 0)
FROM LNS_AMORTIZATION_SCHEDS AM1
, AR_PAYMENT_SCHEDULES_ALL PSA
WHERE AM1.LOAN_ID = LOAN.LOAN_ID
AND TRUNC(AM1.DUE_DATE) < DECODE(SIGN(TRUNC(AM.CREATION_DATE)-TRUNC(AM.DUE_DATE))
, 1
, TRUNC(AM.DUE_DATE)
, TRUNC(AM.CREATION_DATE))
AND (AM1.REVERSED_FLAG IS NULL OR AM1.REVERSED_FLAG = 'N')
AND AM1.REAMORTIZATION_AMOUNT IS NULL
AND NVL(AM1.PHASE
, 'TERM') = 'TERM'
AND PSA.CUSTOMER_TRX_ID = AM1.INTEREST_TRX_ID)
, (SELECT NVL(SUM(AM1.INTEREST_AMOUNT)
, 0)
FROM LNS_AMORTIZATION_SCHEDS AM1
WHERE AM1.LOAN_ID = AM.LOAN_ID
AND TRUNC(AM1.DUE_DATE) < DECODE(SIGN(TRUNC(AM.CREATION_DATE)-TRUNC(AM.DUE_DATE))
, 1
, TRUNC(AM.DUE_DATE)
, TRUNC(AM.CREATION_DATE))
AND (AM1.REVERSED_FLAG IS NULL OR AM1.REVERSED_FLAG = 'N')AND AM1.REAMORTIZATION_AMOUNT IS NULL
AND NVL(AM1.PHASE
, 'TERM') = 'TERM')
, 0
, LOAN.ORG_ID
, DECODE(AM.PARENT_AMORTIZATION_ID
, NULL
, 1
, 2)
, (SELECT NVL(RATES.CURRENT_INTEREST_RATE
, 0)
FROM LNS_RATE_SCHEDULES RATES
, LNS_TERMS TERM
WHERE TERM.LOAN_ID = LOAN.LOAN_ID
AND TERM.TERM_ID = RATES.TERM_ID
AND NVL(RATES.PHASE
, 'TERM') = 'TERM'
AND DECODE(AM.PAYMENT_NUMBER
, 0
, 1
, AM.PAYMENT_NUMBER) BETWEEN RATES.BEGIN_INSTALLMENT_NUMBER
AND RATES.END_INSTALLMENT_NUMBER
AND RATES.END_DATE_ACTIVE IS NULL)
FROM LNS_LOAN_HEADERS_ALL LOAN
, LNS_AMORTIZATION_SCHEDS AM
WHERE LOAN.LOAN_ID = AM.LOAN_ID
AND (AM.REVERSED_FLAG IS NULL OR AM.REVERSED_FLAG = 'N')
AND AM.REAMORTIZATION_AMOUNT IS NULL
AND NVL(AM.PHASE
, 'TERM') = 'TERM' UNION ALL SELECT LOAN.LOAN_ID
, AM.AMORTIZATION_SCHEDULE_ID
, AM.PAYMENT_NUMBER
, TRUNC(AM.DUE_DATE)
, AM.PRINCIPAL_AMOUNT
, AM.INTEREST_AMOUNT
, AM.FEE_AMOUNT
, AM.PARENT_AMORTIZATION_ID
, AM.INTEREST_TRX_ID
, NULL
, NULL
, 'DUE'
, AM.INTEREST_AMOUNT
, NULL
, TRUNC(AM.DUE_DATE)
, (SELECT NVL(SUM(REC1.AMOUNT_APPLIED)
, 0)
FROM AR_RECEIVABLE_APPLICATIONS_ALL REC1
, RA_CUSTOMER_TRX_ALL TRX1
, LNS_AMORTIZATION_SCHEDS AM1
WHERE REC1.APPLICATION_TYPE IN ('CASH'
, 'CM')
AND REC1.CUSTOMER_TRX_ID = TRX1.CUSTOMER_TRX_ID(+)
AND TRUNC(REC1.APPLY_DATE) <= TRUNC(AM.DUE_DATE)
AND REC1.APPLIED_CUSTOMER_TRX_ID = AM1.INTEREST_TRX_ID
AND AM1.LOAN_ID = LOAN.LOAN_ID
AND AM1.INTEREST_TRX_ID IS NOT NULL
AND (AM1.REVERSED_FLAG IS NULL OR AM1.REVERSED_FLAG = 'N')
AND AM1.REAMORTIZATION_AMOUNT IS NULL
AND NVL(AM1.PHASE
, 'TERM') = 'TERM') - (SELECT NVL(SUM(ADJ1.AMOUNT)
, 0)
FROM AR_ADJUSTMENTS_ALL ADJ1
, LNS_AMORTIZATION_SCHEDS AM1
WHERE TRUNC(ADJ1.APPLY_DATE) <= TRUNC(AM.DUE_DATE)
AND ADJ1.CUSTOMER_TRX_ID = AM1.INTEREST_TRX_ID
AND AM1.LOAN_ID = LOAN.LOAN_ID
AND AM1.INTEREST_TRX_ID IS NOT NULL
AND (AM1.REVERSED_FLAG IS NULL OR AM1.REVERSED_FLAG = 'N')
AND AM1.REAMORTIZATION_AMOUNT IS NULL
AND NVL(AM1.PHASE
, 'TERM') = 'TERM') - (SELECT NVL(SUM(PSA.TAX_ORIGINAL)
, 0)
FROM LNS_AMORTIZATION_SCHEDS AM1
, AR_PAYMENT_SCHEDULES_ALL PSA
WHERE AM1.LOAN_ID = LOAN.LOAN_ID
AND TRUNC(AM1.DUE_DATE) <= TRUNC(AM.DUE_DATE)
AND (AM1.REVERSED_FLAG IS NULL OR AM1.REVERSED_FLAG = 'N')
AND AM1.REAMORTIZATION_AMOUNT IS NULL
AND NVL(AM1.PHASE
, 'TERM') = 'TERM'
AND PSA.CUSTOMER_TRX_ID = AM1.INTEREST_TRX_ID)
, (SELECT NVL(SUM(AM1.INTEREST_AMOUNT)
, 0)
FROM LNS_AMORTIZATION_SCHEDS AM1
WHERE AM1.LOAN_ID = AM.LOAN_ID
AND TRUNC(AM1.DUE_DATE) <= TRUNC(AM.DUE_DATE)
AND (AM1.REVERSED_FLAG IS NULL OR AM1.REVERSED_FLAG = 'N')
AND AM1.REAMORTIZATION_AMOUNT IS NULL
AND NVL(AM1.PHASE
, 'TERM') = 'TERM')
, 0
, LOAN.ORG_ID
, DECODE(AM.PARENT_AMORTIZATION_ID
, NULL
, 10
, 11)
, (SELECT NVL(RATES.CURRENT_INTEREST_RATE
, 0)
FROM LNS_RATE_SCHEDULES RATES
, LNS_TERMS TERM
WHERE TERM.LOAN_ID = LOAN.LOAN_ID
AND TERM.TERM_ID = RATES.TERM_ID
AND NVL(RATES.PHASE
, 'TERM') = 'TERM'
AND DECODE(AM.PAYMENT_NUMBER
, 0
, 1
, AM.PAYMENT_NUMBER) BETWEEN RATES.BEGIN_INSTALLMENT_NUMBER
AND RATES.END_INSTALLMENT_NUMBER
AND RATES.END_DATE_ACTIVE IS NULL)
FROM LNS_LOAN_HEADERS_ALL LOAN
, LNS_AMORTIZATION_SCHEDS AM
WHERE LOAN.LOAN_ID = AM.LOAN_ID
AND (AM.REVERSED_FLAG IS NULL OR AM.REVERSED_FLAG = 'N')
AND AM.REAMORTIZATION_AMOUNT IS NULL UNION ALL SELECT LOAN.LOAN_ID
, AM.AMORTIZATION_SCHEDULE_ID
, AM.PAYMENT_NUMBER
, TRUNC(AM.DUE_DATE)
, AM.PRINCIPAL_AMOUNT
, AM.INTEREST_AMOUNT
, AM.FEE_AMOUNT
, AM.PARENT_AMORTIZATION_ID
, TRX.CUSTOMER_TRX_ID
, TRX_PSA.PAYMENT_SCHEDULE_ID
, TRX.TRX_NUMBER
, REC_PSA.CLASS
, -REC.AMOUNT_APPLIED
, REC_PSA.TRX_NUMBER
, TRUNC(REC.APPLY_DATE)
, (SELECT NVL(SUM(REC1.AMOUNT_APPLIED)
, 0)
FROM AR_RECEIVABLE_APPLICATIONS_ALL REC1
, RA_CUSTOMER_TRX_ALL TRX1
, LNS_AMORTIZATION_SCHEDS AM1
WHERE REC1.APPLICATION_TYPE IN ('CASH'
, 'CM')
AND REC1.CUSTOMER_TRX_ID = TRX1.CUSTOMER_TRX_ID(+)
AND TRUNC(REC1.APPLY_DATE) <= TRUNC(REC.APPLY_DATE)
AND REC1.APPLIED_CUSTOMER_TRX_ID = AM1.INTEREST_TRX_ID
AND AM1.LOAN_ID = LOAN.LOAN_ID
AND AM1.INTEREST_TRX_ID IS NOT NULL
AND (AM1.REVERSED_FLAG IS NULL OR AM1.REVERSED_FLAG = 'N')
AND AM1.REAMORTIZATION_AMOUNT IS NULL
AND NVL(AM1.PHASE
, 'TERM') = 'TERM') - (SELECT NVL(SUM(ADJ1.AMOUNT)
, 0)
FROM AR_ADJUSTMENTS_ALL ADJ1
, LNS_AMORTIZATION_SCHEDS AM1
WHERE TRUNC(ADJ1.APPLY_DATE) <= TRUNC(REC.APPLY_DATE)
AND ADJ1.CUSTOMER_TRX_ID = AM1.INTEREST_TRX_ID
AND AM1.LOAN_ID = LOAN.LOAN_ID
AND AM1.INTEREST_TRX_ID IS NOT NULL
AND (AM1.REVERSED_FLAG IS NULL OR AM1.REVERSED_FLAG = 'N')
AND AM1.REAMORTIZATION_AMOUNT IS NULL
AND NVL(AM1.PHASE
, 'TERM') = 'TERM') - (SELECT NVL(SUM(PSA.TAX_ORIGINAL)
, 0)
FROM LNS_AMORTIZATION_SCHEDS AM1
, AR_PAYMENT_SCHEDULES_ALL PSA
WHERE AM1.LOAN_ID = LOAN.LOAN_ID
AND TRUNC(AM1.DUE_DATE) <= TRUNC(REC.APPLY_DATE)
AND (AM1.REVERSED_FLAG IS NULL OR AM1.REVERSED_FLAG = 'N')
AND AM1.REAMORTIZATION_AMOUNT IS NULL
AND NVL(AM1.PHASE
, 'TERM') = 'TERM'
AND PSA.CUSTOMER_TRX_ID = AM1.INTEREST_TRX_ID)
, (SELECT NVL(SUM(AM1.INTEREST_AMOUNT)
, 0)
FROM LNS_AMORTIZATION_SCHEDS AM1
WHERE AM1.LOAN_ID = AM.LOAN_ID
AND TRUNC(AM1.DUE_DATE) <= TRUNC(REC.APPLY_DATE)
AND (AM1.REVERSED_FLAG IS NULL OR AM1.REVERSED_FLAG = 'N')
AND AM1.REAMORTIZATION_AMOUNT IS NULL
AND NVL(AM1.PHASE
, 'TERM') = 'TERM')
, TRUNC(REC.APPLY_DATE)-TRUNC(AM.DUE_DATE)
, LOAN.ORG_ID
, 5
, (SELECT NVL(RATES.CURRENT_INTEREST_RATE
, 0)
FROM LNS_RATE_SCHEDULES RATES
, LNS_TERMS TERM
WHERE TERM.LOAN_ID = LOAN.LOAN_ID
AND TERM.TERM_ID = RATES.TERM_ID
AND NVL(RATES.PHASE
, 'TERM') = 'TERM'
AND DECODE(AM.PAYMENT_NUMBER
, 0
, 1
, AM.PAYMENT_NUMBER) BETWEEN RATES.BEGIN_INSTALLMENT_NUMBER
AND RATES.END_INSTALLMENT_NUMBER
AND RATES.END_DATE_ACTIVE IS NULL)
FROM RA_CUSTOMER_TRX_ALL TRX
, LNS_LOAN_HEADERS_ALL LOAN
, LNS_AMORTIZATION_SCHEDS AM
, AR_PAYMENT_SCHEDULES_ALL TRX_PSA
, AR_PAYMENT_SCHEDULES_ALL REC_PSA
, AR_RECEIVABLE_APPLICATIONS_ALL REC
WHERE LOAN.LOAN_ID = AM.LOAN_ID
AND (AM.REVERSED_FLAG IS NULL OR AM.REVERSED_FLAG = 'N')
AND AM.REAMORTIZATION_AMOUNT IS NULL
AND TRX.CUSTOMER_TRX_ID = AM.INTEREST_TRX_ID
AND TRX.CUSTOMER_TRX_ID = TRX_PSA.CUSTOMER_TRX_ID
AND TRX.CUSTOMER_TRX_ID = REC.APPLIED_CUSTOMER_TRX_ID
AND REC.PAYMENT_SCHEDULE_ID = REC_PSA.PAYMENT_SCHEDULE_ID
AND REC.DISPLAY = 'Y' UNION ALL SELECT LOAN.LOAN_ID
, AM.AMORTIZATION_SCHEDULE_ID
, AM.PAYMENT_NUMBER
, TRUNC(AM.DUE_DATE)
, AM.PRINCIPAL_AMOUNT
, AM.INTEREST_AMOUNT
, AM.FEE_AMOUNT
, AM.PARENT_AMORTIZATION_ID
, TRX.CUSTOMER_TRX_ID
, TRX_PSA.PAYMENT_SCHEDULE_ID
, TRX.TRX_NUMBER
, 'ADJUSTMENT'
, ADJ.AMOUNT
, ADJ.ADJUSTMENT_NUMBER
, TRUNC(ADJ.APPLY_DATE)
, (SELECT NVL(SUM(REC1.AMOUNT_APPLIED)
, 0)
FROM AR_RECEIVABLE_APPLICATIONS_ALL REC1
, RA_CUSTOMER_TRX_ALL TRX1
, LNS_AMORTIZATION_SCHEDS AM1
WHERE REC1.APPLICATION_TYPE IN ('CASH'
, 'CM')
AND REC1.CUSTOMER_TRX_ID = TRX1.CUSTOMER_TRX_ID(+)
AND TRUNC(REC1.APPLY_DATE) <= TRUNC(ADJ.APPLY_DATE)
AND REC1.APPLIED_CUSTOMER_TRX_ID = AM1.INTEREST_TRX_ID
AND AM1.LOAN_ID = LOAN.LOAN_ID
AND AM1.INTEREST_TRX_ID IS NOT NULL
AND (AM1.REVERSED_FLAG IS NULL OR AM1.REVERSED_FLAG = 'N')
AND AM1.REAMORTIZATION_AMOUNT IS NULL
AND NVL(AM1.PHASE
, 'TERM') = 'TERM') - (SELECT NVL(SUM(ADJ1.AMOUNT)
, 0)
FROM AR_ADJUSTMENTS_ALL ADJ1
, LNS_AMORTIZATION_SCHEDS AM1
WHERE TRUNC(ADJ1.APPLY_DATE) <= TRUNC(ADJ.APPLY_DATE)
AND ADJ1.CUSTOMER_TRX_ID = AM1.INTEREST_TRX_ID
AND AM1.LOAN_ID = LOAN.LOAN_ID
AND AM1.INTEREST_TRX_ID IS NOT NULL
AND (AM1.REVERSED_FLAG IS NULL OR AM1.REVERSED_FLAG = 'N')
AND AM1.REAMORTIZATION_AMOUNT IS NULL
AND NVL(AM1.PHASE
, 'TERM') = 'TERM') - (SELECT NVL(SUM(PSA.TAX_ORIGINAL)
, 0)
FROM LNS_AMORTIZATION_SCHEDS AM1
, AR_PAYMENT_SCHEDULES_ALL PSA
WHERE AM1.LOAN_ID = LOAN.LOAN_ID
AND TRUNC(AM1.DUE_DATE) <= TRUNC(ADJ.APPLY_DATE)
AND (AM1.REVERSED_FLAG IS NULL OR AM1.REVERSED_FLAG = 'N')
AND AM1.REAMORTIZATION_AMOUNT IS NULL
AND NVL(AM1.PHASE
, 'TERM') = 'TERM'
AND PSA.CUSTOMER_TRX_ID = AM1.INTEREST_TRX_ID)
, (SELECT NVL(SUM(AM1.INTEREST_AMOUNT)
, 0)
FROM LNS_AMORTIZATION_SCHEDS AM1
WHERE AM1.LOAN_ID = AM.LOAN_ID
AND TRUNC(AM1.DUE_DATE) <= TRUNC(ADJ.APPLY_DATE)
AND (AM1.REVERSED_FLAG IS NULL OR AM1.REVERSED_FLAG = 'N')
AND AM1.REAMORTIZATION_AMOUNT IS NULL
AND NVL(AM1.PHASE
, 'TERM') = 'TERM')
, TRUNC(ADJ.APPLY_DATE)-TRUNC(AM.DUE_DATE)
, LOAN.ORG_ID
, 5
, (SELECT NVL(RATES.CURRENT_INTEREST_RATE
, 0)
FROM LNS_RATE_SCHEDULES RATES
, LNS_TERMS TERM
WHERE TERM.LOAN_ID = LOAN.LOAN_ID
AND TERM.TERM_ID = RATES.TERM_ID
AND NVL(RATES.PHASE
, 'TERM') = 'TERM'
AND DECODE(AM.PAYMENT_NUMBER
, 0
, 1
, AM.PAYMENT_NUMBER) BETWEEN RATES.BEGIN_INSTALLMENT_NUMBER
AND RATES.END_INSTALLMENT_NUMBER
AND RATES.END_DATE_ACTIVE IS NULL)
FROM RA_CUSTOMER_TRX_ALL TRX
, AR_PAYMENT_SCHEDULES_ALL TRX_PSA
, AR_ADJUSTMENTS_ALL ADJ
, AR_RECEIVABLES_TRX_ALL REC_TRX
, LNS_LOAN_HEADERS_ALL LOAN
, LNS_AMORTIZATION_SCHEDS AM
WHERE LOAN.LOAN_ID = AM.LOAN_ID
AND TRX.CUSTOMER_TRX_ID = AM.INTEREST_TRX_ID
AND TRX.CUSTOMER_TRX_ID = TRX_PSA.CUSTOMER_TRX_ID
AND TRX_PSA.PAYMENT_SCHEDULE_ID = ADJ.PAYMENT_SCHEDULE_ID
AND ADJ.RECEIVABLES_TRX_ID = REC_TRX.RECEIVABLES_TRX_ID
AND ADJ.ORG_ID = REC_TRX.ORG_ID
AND ADJ.POSTABLE = 'Y'
AND ADJ.STATUS = 'A'