eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.LNS_PAYMENTS_SUMMARY_V

Source

View Text - Preformatted


SELECT loan.loan_id, ( CASE WHEN(loan.loan_status = 'ACTIVE' OR loan.loan_status = 'DEFAULT' OR loan.loan_status = 'DELINQUENT' OR loan.loan_status = 'INCOMPLETE' OR loan.loan_status = 'PENDING' OR loan.loan_status = 'APPROVED') THEN loan.last_amortization_id ELSE NULL END), ( CASE WHEN(loan.loan_status = 'ACTIVE' OR loan.loan_status = 'DEFAULT' OR loan.loan_status = 'DELINQUENT' OR loan.loan_status = 'INCOMPLETE' OR loan.loan_status = 'PENDING' OR loan.loan_status = 'APPROVED') THEN loan.last_payment_number ELSE NULL END), ( CASE WHEN(loan.loan_status = 'ACTIVE' OR loan.loan_status = 'DEFAULT' OR loan.loan_status = 'DELINQUENT' OR loan.loan_status = 'INCOMPLETE' OR loan.loan_status = 'PENDING' OR loan.loan_status = 'APPROVED') THEN (SELECT due_date FROM lns_amortization_scheds WHERE amortization_schedule_id = loan.last_amortization_id) ELSE to_date(NULL) END), ( CASE WHEN(loan.loan_status = 'ACTIVE' OR loan.loan_status = 'DEFAULT' OR loan.loan_status = 'DELINQUENT' OR loan.loan_status = 'INCOMPLETE' OR loan.loan_status = 'PENDING' OR loan.loan_status = 'APPROVED') THEN (SELECT nvl(SUM(principal_amount), 0) FROM lns_amortization_scheds am WHERE loan_id = loan.loan_id AND(reversed_flag IS NULL OR reversed_flag = 'N') AND reamortization_amount IS NULL AND payment_number = loan.last_payment_number AND nvl(phase, 'TERM') = nvl(loan.current_phase, 'TERM')) ELSE 0 END), ( CASE WHEN(loan.loan_status = 'ACTIVE' OR loan.loan_status = 'DEFAULT' OR loan.loan_status = 'DELINQUENT' OR loan.loan_status = 'INCOMPLETE' OR loan.loan_status = 'PENDING' OR loan.loan_status = 'APPROVED') THEN (SELECT nvl(SUM(interest_amount), 0) FROM lns_amortization_scheds am WHERE loan_id = loan.loan_id AND(reversed_flag IS NULL OR reversed_flag = 'N') AND reamortization_amount IS NULL AND payment_number = loan.last_payment_number AND nvl(phase, 'TERM') = nvl(loan.current_phase, 'TERM')) ELSE 0 END), ( CASE WHEN(loan.loan_status = 'ACTIVE' OR loan.loan_status = 'DEFAULT' OR loan.loan_status = 'DELINQUENT' OR loan.loan_status = 'INCOMPLETE' OR loan.loan_status = 'PENDING' OR loan.loan_status = 'APPROVED') THEN (SELECT nvl(SUM(fee_amount), 0) FROM lns_amortization_scheds am WHERE loan_id = loan.loan_id AND(reversed_flag IS NULL OR reversed_flag = 'N') AND reamortization_amount IS NULL AND payment_number = loan.last_payment_number AND nvl(phase, 'TERM') = nvl(loan.current_phase, 'TERM')) ELSE 0 END), ( CASE WHEN(loan.loan_status = 'ACTIVE' OR loan.loan_status = 'DEFAULT' OR loan.loan_status = 'DELINQUENT' OR loan.loan_status = 'INCOMPLETE' OR loan.loan_status = 'PENDING' OR loan.loan_status = 'APPROVED') THEN (SELECT nvl(SUM(principal_amount), 0) + nvl(SUM(interest_amount), 0) + nvl(SUM(fee_amount), 0) FROM lns_amortization_scheds am WHERE loan_id = loan.loan_id AND(reversed_flag IS NULL OR reversed_flag = 'N') AND reamortization_amount IS NULL AND payment_number = loan.last_payment_number AND nvl(phase, 'TERM') = nvl(loan.current_phase, 'TERM')) ELSE 0 END), (SELECT nvl(SUM(psa.amount_applied), 0) FROM ar_payment_schedules_all psa, lns_amortization_scheds am1 WHERE psa.org_id = loan.org_id AND psa.customer_trx_id = am1.principal_trx_id AND am1.loan_id = loan.loan_id AND am1.principal_trx_id IS NOT NULL AND((am1.payment_number = loan.last_payment_number AND am1.amortization_schedule_id <= loan.last_amortization_id) OR(am1.payment_number < loan.last_payment_number)) AND nvl(am1.phase, 'TERM') = nvl(loan.current_phase, 'TERM')), (SELECT nvl(SUM(psa.amount_applied), 0) FROM ar_payment_schedules_all psa, lns_amortization_scheds am1 WHERE psa.org_id = loan.org_id AND psa.customer_trx_id = am1.interest_trx_id AND am1.loan_id = loan.loan_id AND am1.interest_trx_id IS NOT NULL AND am1.payment_number <= loan.last_payment_number AND nvl(am1.phase, 'TERM') = nvl(loan.current_phase, 'TERM')), (SELECT nvl(SUM(psa.amount_applied), 0) FROM ar_payment_schedules_all psa, lns_amortization_scheds am1 WHERE psa.org_id = loan.org_id AND psa.customer_trx_id = am1.fee_trx_id AND am1.loan_id = loan.loan_id AND am1.fee_trx_id IS NOT NULL AND am1.payment_number <= loan.last_payment_number AND nvl(am1.phase, 'TERM') = nvl(loan.current_phase, 'TERM')), (SELECT nvl(SUM(psa.amount_applied), 0) FROM ar_payment_schedules_all psa, lns_amortization_scheds am1 WHERE psa.org_id = loan.org_id AND psa.customer_trx_id = am1.principal_trx_id AND am1.loan_id = loan.loan_id AND am1.principal_trx_id IS NOT NULL AND am1.payment_number <= loan.last_payment_number AND nvl(am1.phase, 'TERM') = nvl(loan.current_phase, 'TERM')), loan.funded_amount - (SELECT nvl(SUM(psa.amount_applied), 0) FROM ar_payment_schedules_all psa, lns_amortization_scheds am1 WHERE psa.org_id = loan.org_id AND psa.customer_trx_id = am1.principal_trx_id AND am1.loan_id = loan.loan_id AND am1.principal_trx_id IS NOT NULL AND am1.payment_number <= loan.last_payment_number AND nvl(am1.phase, 'TERM') = nvl(loan.current_phase, 'TERM')), loan.funded_amount - (SELECT nvl(SUM(psa.amount_applied), 0) FROM ar_payment_schedules_all psa, lns_amortization_scheds am1 WHERE psa.org_id = loan.org_id AND psa.customer_trx_id = am1.principal_trx_id AND am1.loan_id = loan.loan_id AND am1.principal_trx_id IS NOT NULL AND((am1.payment_number = loan.last_payment_number AND am1.amortization_schedule_id <= loan.last_amortization_id) OR(am1.payment_number < loan.last_payment_number)) AND nvl(am1.phase, 'TERM') = nvl(loan.current_phase, 'TERM')), loan.funded_amount, (SELECT nvl(SUM(rec.amount_applied), 0) FROM lns_amortization_scheds am, ar_receivable_applications_all rec WHERE rec.org_id = loan.org_id AND rec.application_type = 'CASH' AND rec.applied_customer_trx_id = am.principal_trx_id AND am.principal_trx_id IS NOT NULL AND am.loan_id = loan.loan_id AND(TRUNC(rec.apply_date) BETWEEN TRUNC(sysdate, 'YYYY') AND TRUNC(add_months(sysdate, 12), 'YYYY') -1) AND nvl(am.phase, 'TERM') = nvl(loan.current_phase, 'TERM')), (SELECT nvl(SUM(rec.amount_applied), 0) FROM lns_amortization_scheds am, ar_receivable_applications_all rec WHERE rec.org_id = loan.org_id AND rec.application_type = 'CASH' AND rec.applied_customer_trx_id = am.interest_trx_id AND am.interest_trx_id IS NOT NULL AND am.loan_id = loan.loan_id AND(TRUNC(rec.apply_date) BETWEEN TRUNC(sysdate, 'YYYY') AND TRUNC(add_months(sysdate, 12), 'YYYY') -1) AND nvl(am.phase, 'TERM') = nvl(loan.current_phase, 'TERM')), (SELECT nvl(SUM(rec.amount_applied), 0) FROM lns_amortization_scheds am, ar_receivable_applications_all rec WHERE rec.org_id = loan.org_id AND rec.application_type = 'CASH' AND rec.applied_customer_trx_id = am.fee_trx_id AND am.fee_trx_id IS NOT NULL AND am.loan_id = loan.loan_id AND(TRUNC(rec.apply_date) BETWEEN TRUNC(sysdate, 'YYYY') AND TRUNC(add_months(sysdate, 12), 'YYYY') -1) AND nvl(am.phase, 'TERM') = nvl(loan.current_phase, 'TERM')), (SELECT nvl(SUM(rec.amount_applied), 0) FROM lns_amortization_scheds am, ar_receivable_applications_all rec WHERE rec.org_id = loan.org_id AND rec.application_type = 'CASH' AND rec.applied_customer_trx_id = am.principal_trx_id AND am.principal_trx_id IS NOT NULL AND am.loan_id = loan.loan_id AND(TRUNC(rec.apply_date) BETWEEN TRUNC(add_months(sysdate, -12), 'YYYY') AND TRUNC(TRUNC(sysdate, 'YYYY') -1)) AND nvl(am.phase, 'TERM') = nvl(loan.current_phase, 'TERM')), (SELECT nvl(SUM(rec.amount_applied), 0) FROM lns_amortization_scheds am, ar_receivable_applications_all rec WHERE rec.org_id = loan.org_id AND rec.application_type = 'CASH' AND rec.applied_customer_trx_id = am.interest_trx_id AND am.interest_trx_id IS NOT NULL AND am.loan_id = loan.loan_id AND(TRUNC(rec.apply_date) BETWEEN TRUNC(add_months(sysdate, -12), 'YYYY') AND TRUNC(TRUNC(sysdate, 'YYYY') -1)) AND nvl(am.phase, 'TERM') = nvl(loan.current_phase, 'TERM')), (SELECT nvl(SUM(rec.amount_applied), 0) FROM lns_amortization_scheds am, ar_receivable_applications_all rec WHERE rec.org_id = loan.org_id AND rec.application_type = 'CASH' AND rec.applied_customer_trx_id = am.fee_trx_id AND am.fee_trx_id IS NOT NULL AND am.loan_id = loan.loan_id AND(TRUNC(rec.apply_date) BETWEEN TRUNC(add_months(sysdate, -12), 'YYYY') AND TRUNC(TRUNC(sysdate, 'YYYY') -1)) AND nvl(am.phase, 'TERM') = nvl(loan.current_phase, 'TERM')), ( CASE WHEN(loan.loan_status = 'ACTIVE' OR loan.loan_status = 'DEFAULT' OR loan.loan_status = 'DELINQUENT' OR loan.loan_status = 'INCOMPLETE' OR loan.loan_status = 'PENDING' OR loan.loan_status = 'APPROVED') THEN nvl( (SELECT SUM(sch.amount_due_remaining) FROM ar_payment_schedules_all sch, lns_amortization_scheds am WHERE sch.org_id = loan.org_id AND am.loan_id = loan.loan_id AND sch.customer_trx_id = am.principal_trx_id AND TRUNC(am.due_date) < TRUNC(sysdate) AND(am.reversed_flag IS NULL OR am.reversed_flag = 'N') AND nvl(am.phase, 'TERM') = nvl(loan.current_phase, 'TERM') AND am.reamortization_amount IS NULL), 0) ELSE 0 END), ( CASE WHEN(loan.loan_status = 'ACTIVE' OR loan.loan_status = 'DEFAULT' OR loan.loan_status = 'DELINQUENT' OR loan.loan_status = 'INCOMPLETE' OR loan.loan_status = 'PENDING' OR loan.loan_status = 'APPROVED') THEN nvl( (SELECT SUM(sch.amount_due_remaining) FROM ar_payment_schedules_all sch, lns_amortization_scheds am WHERE sch.org_id = loan.org_id AND am.loan_id = loan.loan_id AND sch.customer_trx_id = am.interest_trx_id AND TRUNC(am.due_date) < TRUNC(sysdate) AND(am.reversed_flag IS NULL OR am.reversed_flag = 'N') AND nvl(am.phase, 'TERM') = nvl(loan.current_phase, 'TERM') AND am.reamortization_amount IS NULL), 0) ELSE 0 END), ( CASE WHEN(loan.loan_status = 'ACTIVE' OR loan.loan_status = 'DEFAULT' OR loan.loan_status = 'DELINQUENT' OR loan.loan_status = 'INCOMPLETE' OR loan.loan_status = 'PENDING' OR loan.loan_status = 'APPROVED') THEN nvl( (SELECT SUM(sch.amount_due_remaining) FROM ar_payment_schedules_all sch, lns_amortization_scheds am WHERE sch.org_id = loan.org_id AND am.loan_id = loan.loan_id AND sch.customer_trx_id = am.fee_trx_id AND TRUNC(am.due_date) < TRUNC(sysdate) AND(am.reversed_flag IS NULL OR am.reversed_flag = 'N') AND nvl(am.phase, 'TERM') = nvl(loan.current_phase, 'TERM') AND am.reamortization_amount IS NULL), 0) ELSE 0 END), ( CASE WHEN(loan.loan_status = 'ACTIVE' OR loan.loan_status = 'DEFAULT' OR loan.loan_status = 'DELINQUENT' OR loan.loan_status = 'INCOMPLETE' OR loan.loan_status = 'PENDING' OR loan.loan_status = 'APPROVED') THEN nvl( (SELECT SUM(sch.amount_due_remaining) FROM ar_payment_schedules_all sch, lns_amortization_scheds am WHERE sch.org_id = loan.org_id AND am.loan_id = loan.loan_id AND((sch.customer_trx_id = am.principal_trx_id) OR(sch.customer_trx_id = am.interest_trx_id) OR(sch.customer_trx_id = am.fee_trx_id)) AND TRUNC(am.due_date) < TRUNC(sysdate) AND(am.reversed_flag IS NULL OR am.reversed_flag = 'N') AND nvl(am.phase, 'TERM') = nvl(loan.current_phase, 'TERM') AND am.reamortization_amount IS NULL), 0) ELSE 0 END), ( CASE WHEN(loan.loan_status = 'ACTIVE' OR loan.loan_status = 'DEFAULT' OR loan.loan_status = 'DELINQUENT' OR loan.loan_status = 'INCOMPLETE' OR loan.loan_status = 'PENDING' OR loan.loan_status = 'APPROVED') THEN nvl( (SELECT COUNT(1) FROM lns_amortization_scheds am WHERE loan_id = loan.loan_id AND TRUNC(due_date) < TRUNC(sysdate) AND(reversed_flag IS NULL OR reversed_flag = 'N') AND reamortization_amount IS NULL AND nvl(am.phase, 'TERM') = nvl(loan.current_phase, 'TERM') AND nvl( (SELECT SUM(amount_due_remaining) FROM ar_payment_schedules_all WHERE org_id = loan.org_id AND((customer_trx_id = am.principal_trx_id) OR(customer_trx_id = am.interest_trx_id) OR(customer_trx_id = am.fee_trx_id))), 0) > 0), 0) ELSE 0 END), ( CASE WHEN(loan.loan_status = 'ACTIVE' OR loan.loan_status = 'DEFAULT' OR loan.loan_status = 'DELINQUENT' OR loan.loan_status = 'INCOMPLETE' OR loan.loan_status = 'PENDING' OR loan.loan_status = 'APPROVED') THEN (SELECT MAX(due_date) FROM lns_amortization_scheds am WHERE loan_id = loan.loan_id AND TRUNC(due_date) < TRUNC(sysdate) AND(reversed_flag IS NULL OR reversed_flag = 'N') AND reamortization_amount IS NULL AND nvl(am.phase, 'TERM') = nvl(loan.current_phase, 'TERM') AND nvl( (SELECT SUM(amount_due_remaining) FROM ar_payment_schedules_all WHERE org_id = loan.org_id AND((customer_trx_id = am.principal_trx_id) OR(customer_trx_id = am.interest_trx_id) OR(customer_trx_id = am.fee_trx_id))), 0) > 0) ELSE to_date(NULL) END), ( CASE WHEN(loan.loan_status = 'ACTIVE' OR loan.loan_status = 'DEFAULT' OR loan.loan_status = 'DELINQUENT' OR loan.loan_status = 'INCOMPLETE' OR loan.loan_status = 'PENDING' OR loan.loan_status = 'APPROVED') THEN (SELECT MIN(due_date) FROM lns_amortization_scheds am WHERE loan_id = loan.loan_id AND TRUNC(due_date) < TRUNC(sysdate) AND(reversed_flag IS NULL OR reversed_flag = 'N') AND reamortization_amount IS NULL AND nvl(am.phase, 'TERM') = nvl(loan.current_phase, 'TERM') AND nvl( (SELECT SUM(amount_due_remaining) FROM ar_payment_schedules_all WHERE org_id = loan.org_id AND((customer_trx_id = am.principal_trx_id) OR(customer_trx_id = am.interest_trx_id) OR(customer_trx_id = am.fee_trx_id))), 0) > 0) ELSE to_date(NULL) END), nvl( (SELECT amount_applied FROM ar_receivable_applications_all WHERE receivable_application_id = (SELECT MAX(receivable_application_id) FROM ar_receivable_applications_all rec, lns_amortization_scheds am WHERE am.loan_id = loan.loan_id AND rec.org_id = loan.org_id AND rec.applied_customer_trx_id IN(am.principal_trx_id, am.interest_trx_id, am.fee_trx_id) AND rec.display = 'Y' AND nvl(am.phase, 'TERM') = nvl(loan.current_phase, 'TERM') AND rec.application_type = 'CASH')), 0), (SELECT apply_date FROM ar_receivable_applications_all WHERE receivable_application_id = (SELECT MAX(receivable_application_id) FROM ar_receivable_applications_all rec, lns_amortization_scheds am WHERE am.loan_id = loan.loan_id AND rec.org_id = loan.org_id AND rec.applied_customer_trx_id IN(am.principal_trx_id, am.interest_trx_id, am.fee_trx_id) AND rec.display = 'Y' AND nvl(am.phase, 'TERM') = nvl(loan.current_phase, 'TERM') AND rec.application_type = 'CASH') ), loan.org_id, loan.legal_entity_id, nvl(loan.current_phase, 'TERM') FROM lns_loan_headers_all loan

View Text - HTML Formatted

SELECT LOAN.LOAN_ID
, ( CASE WHEN(LOAN.LOAN_STATUS = 'ACTIVE' OR LOAN.LOAN_STATUS = 'DEFAULT' OR LOAN.LOAN_STATUS = 'DELINQUENT' OR LOAN.LOAN_STATUS = 'INCOMPLETE' OR LOAN.LOAN_STATUS = 'PENDING' OR LOAN.LOAN_STATUS = 'APPROVED') THEN LOAN.LAST_AMORTIZATION_ID ELSE NULL END)
, ( CASE WHEN(LOAN.LOAN_STATUS = 'ACTIVE' OR LOAN.LOAN_STATUS = 'DEFAULT' OR LOAN.LOAN_STATUS = 'DELINQUENT' OR LOAN.LOAN_STATUS = 'INCOMPLETE' OR LOAN.LOAN_STATUS = 'PENDING' OR LOAN.LOAN_STATUS = 'APPROVED') THEN LOAN.LAST_PAYMENT_NUMBER ELSE NULL END)
, ( CASE WHEN(LOAN.LOAN_STATUS = 'ACTIVE' OR LOAN.LOAN_STATUS = 'DEFAULT' OR LOAN.LOAN_STATUS = 'DELINQUENT' OR LOAN.LOAN_STATUS = 'INCOMPLETE' OR LOAN.LOAN_STATUS = 'PENDING' OR LOAN.LOAN_STATUS = 'APPROVED') THEN (SELECT DUE_DATE
FROM LNS_AMORTIZATION_SCHEDS
WHERE AMORTIZATION_SCHEDULE_ID = LOAN.LAST_AMORTIZATION_ID) ELSE TO_DATE(NULL) END)
, ( CASE WHEN(LOAN.LOAN_STATUS = 'ACTIVE' OR LOAN.LOAN_STATUS = 'DEFAULT' OR LOAN.LOAN_STATUS = 'DELINQUENT' OR LOAN.LOAN_STATUS = 'INCOMPLETE' OR LOAN.LOAN_STATUS = 'PENDING' OR LOAN.LOAN_STATUS = 'APPROVED') THEN (SELECT NVL(SUM(PRINCIPAL_AMOUNT)
, 0)
FROM LNS_AMORTIZATION_SCHEDS AM
WHERE LOAN_ID = LOAN.LOAN_ID AND(REVERSED_FLAG IS NULL OR REVERSED_FLAG = 'N')
AND REAMORTIZATION_AMOUNT IS NULL
AND PAYMENT_NUMBER = LOAN.LAST_PAYMENT_NUMBER
AND NVL(PHASE
, 'TERM') = NVL(LOAN.CURRENT_PHASE
, 'TERM')) ELSE 0 END)
, ( CASE WHEN(LOAN.LOAN_STATUS = 'ACTIVE' OR LOAN.LOAN_STATUS = 'DEFAULT' OR LOAN.LOAN_STATUS = 'DELINQUENT' OR LOAN.LOAN_STATUS = 'INCOMPLETE' OR LOAN.LOAN_STATUS = 'PENDING' OR LOAN.LOAN_STATUS = 'APPROVED') THEN (SELECT NVL(SUM(INTEREST_AMOUNT)
, 0)
FROM LNS_AMORTIZATION_SCHEDS AM
WHERE LOAN_ID = LOAN.LOAN_ID AND(REVERSED_FLAG IS NULL OR REVERSED_FLAG = 'N')
AND REAMORTIZATION_AMOUNT IS NULL
AND PAYMENT_NUMBER = LOAN.LAST_PAYMENT_NUMBER
AND NVL(PHASE
, 'TERM') = NVL(LOAN.CURRENT_PHASE
, 'TERM')) ELSE 0 END)
, ( CASE WHEN(LOAN.LOAN_STATUS = 'ACTIVE' OR LOAN.LOAN_STATUS = 'DEFAULT' OR LOAN.LOAN_STATUS = 'DELINQUENT' OR LOAN.LOAN_STATUS = 'INCOMPLETE' OR LOAN.LOAN_STATUS = 'PENDING' OR LOAN.LOAN_STATUS = 'APPROVED') THEN (SELECT NVL(SUM(FEE_AMOUNT)
, 0)
FROM LNS_AMORTIZATION_SCHEDS AM
WHERE LOAN_ID = LOAN.LOAN_ID AND(REVERSED_FLAG IS NULL OR REVERSED_FLAG = 'N')
AND REAMORTIZATION_AMOUNT IS NULL
AND PAYMENT_NUMBER = LOAN.LAST_PAYMENT_NUMBER
AND NVL(PHASE
, 'TERM') = NVL(LOAN.CURRENT_PHASE
, 'TERM')) ELSE 0 END)
, ( CASE WHEN(LOAN.LOAN_STATUS = 'ACTIVE' OR LOAN.LOAN_STATUS = 'DEFAULT' OR LOAN.LOAN_STATUS = 'DELINQUENT' OR LOAN.LOAN_STATUS = 'INCOMPLETE' OR LOAN.LOAN_STATUS = 'PENDING' OR LOAN.LOAN_STATUS = 'APPROVED') THEN (SELECT NVL(SUM(PRINCIPAL_AMOUNT)
, 0) + NVL(SUM(INTEREST_AMOUNT)
, 0) + NVL(SUM(FEE_AMOUNT)
, 0)
FROM LNS_AMORTIZATION_SCHEDS AM
WHERE LOAN_ID = LOAN.LOAN_ID AND(REVERSED_FLAG IS NULL OR REVERSED_FLAG = 'N')
AND REAMORTIZATION_AMOUNT IS NULL
AND PAYMENT_NUMBER = LOAN.LAST_PAYMENT_NUMBER
AND NVL(PHASE
, 'TERM') = NVL(LOAN.CURRENT_PHASE
, 'TERM')) ELSE 0 END)
, (SELECT NVL(SUM(PSA.AMOUNT_APPLIED)
, 0)
FROM AR_PAYMENT_SCHEDULES_ALL PSA
, LNS_AMORTIZATION_SCHEDS AM1
WHERE PSA.ORG_ID = LOAN.ORG_ID
AND PSA.CUSTOMER_TRX_ID = AM1.PRINCIPAL_TRX_ID
AND AM1.LOAN_ID = LOAN.LOAN_ID
AND AM1.PRINCIPAL_TRX_ID IS NOT NULL AND((AM1.PAYMENT_NUMBER = LOAN.LAST_PAYMENT_NUMBER
AND AM1.AMORTIZATION_SCHEDULE_ID <= LOAN.LAST_AMORTIZATION_ID) OR(AM1.PAYMENT_NUMBER < LOAN.LAST_PAYMENT_NUMBER))
AND NVL(AM1.PHASE
, 'TERM') = NVL(LOAN.CURRENT_PHASE
, 'TERM'))
, (SELECT NVL(SUM(PSA.AMOUNT_APPLIED)
, 0)
FROM AR_PAYMENT_SCHEDULES_ALL PSA
, LNS_AMORTIZATION_SCHEDS AM1
WHERE PSA.ORG_ID = LOAN.ORG_ID
AND PSA.CUSTOMER_TRX_ID = AM1.INTEREST_TRX_ID
AND AM1.LOAN_ID = LOAN.LOAN_ID
AND AM1.INTEREST_TRX_ID IS NOT NULL
AND AM1.PAYMENT_NUMBER <= LOAN.LAST_PAYMENT_NUMBER
AND NVL(AM1.PHASE
, 'TERM') = NVL(LOAN.CURRENT_PHASE
, 'TERM'))
, (SELECT NVL(SUM(PSA.AMOUNT_APPLIED)
, 0)
FROM AR_PAYMENT_SCHEDULES_ALL PSA
, LNS_AMORTIZATION_SCHEDS AM1
WHERE PSA.ORG_ID = LOAN.ORG_ID
AND PSA.CUSTOMER_TRX_ID = AM1.FEE_TRX_ID
AND AM1.LOAN_ID = LOAN.LOAN_ID
AND AM1.FEE_TRX_ID IS NOT NULL
AND AM1.PAYMENT_NUMBER <= LOAN.LAST_PAYMENT_NUMBER
AND NVL(AM1.PHASE
, 'TERM') = NVL(LOAN.CURRENT_PHASE
, 'TERM'))
, (SELECT NVL(SUM(PSA.AMOUNT_APPLIED)
, 0)
FROM AR_PAYMENT_SCHEDULES_ALL PSA
, LNS_AMORTIZATION_SCHEDS AM1
WHERE PSA.ORG_ID = LOAN.ORG_ID
AND PSA.CUSTOMER_TRX_ID = AM1.PRINCIPAL_TRX_ID
AND AM1.LOAN_ID = LOAN.LOAN_ID
AND AM1.PRINCIPAL_TRX_ID IS NOT NULL
AND AM1.PAYMENT_NUMBER <= LOAN.LAST_PAYMENT_NUMBER
AND NVL(AM1.PHASE
, 'TERM') = NVL(LOAN.CURRENT_PHASE
, 'TERM'))
, LOAN.FUNDED_AMOUNT - (SELECT NVL(SUM(PSA.AMOUNT_APPLIED)
, 0)
FROM AR_PAYMENT_SCHEDULES_ALL PSA
, LNS_AMORTIZATION_SCHEDS AM1
WHERE PSA.ORG_ID = LOAN.ORG_ID
AND PSA.CUSTOMER_TRX_ID = AM1.PRINCIPAL_TRX_ID
AND AM1.LOAN_ID = LOAN.LOAN_ID
AND AM1.PRINCIPAL_TRX_ID IS NOT NULL
AND AM1.PAYMENT_NUMBER <= LOAN.LAST_PAYMENT_NUMBER
AND NVL(AM1.PHASE
, 'TERM') = NVL(LOAN.CURRENT_PHASE
, 'TERM'))
, LOAN.FUNDED_AMOUNT - (SELECT NVL(SUM(PSA.AMOUNT_APPLIED)
, 0)
FROM AR_PAYMENT_SCHEDULES_ALL PSA
, LNS_AMORTIZATION_SCHEDS AM1
WHERE PSA.ORG_ID = LOAN.ORG_ID
AND PSA.CUSTOMER_TRX_ID = AM1.PRINCIPAL_TRX_ID
AND AM1.LOAN_ID = LOAN.LOAN_ID
AND AM1.PRINCIPAL_TRX_ID IS NOT NULL AND((AM1.PAYMENT_NUMBER = LOAN.LAST_PAYMENT_NUMBER
AND AM1.AMORTIZATION_SCHEDULE_ID <= LOAN.LAST_AMORTIZATION_ID) OR(AM1.PAYMENT_NUMBER < LOAN.LAST_PAYMENT_NUMBER))
AND NVL(AM1.PHASE
, 'TERM') = NVL(LOAN.CURRENT_PHASE
, 'TERM'))
, LOAN.FUNDED_AMOUNT
, (SELECT NVL(SUM(REC.AMOUNT_APPLIED)
, 0)
FROM LNS_AMORTIZATION_SCHEDS AM
, AR_RECEIVABLE_APPLICATIONS_ALL REC
WHERE REC.ORG_ID = LOAN.ORG_ID
AND REC.APPLICATION_TYPE = 'CASH'
AND REC.APPLIED_CUSTOMER_TRX_ID = AM.PRINCIPAL_TRX_ID
AND AM.PRINCIPAL_TRX_ID IS NOT NULL
AND AM.LOAN_ID = LOAN.LOAN_ID AND(TRUNC(REC.APPLY_DATE) BETWEEN TRUNC(SYSDATE
, 'YYYY')
AND TRUNC(ADD_MONTHS(SYSDATE
, 12)
, 'YYYY') -1)
AND NVL(AM.PHASE
, 'TERM') = NVL(LOAN.CURRENT_PHASE
, 'TERM'))
, (SELECT NVL(SUM(REC.AMOUNT_APPLIED)
, 0)
FROM LNS_AMORTIZATION_SCHEDS AM
, AR_RECEIVABLE_APPLICATIONS_ALL REC
WHERE REC.ORG_ID = LOAN.ORG_ID
AND REC.APPLICATION_TYPE = 'CASH'
AND REC.APPLIED_CUSTOMER_TRX_ID = AM.INTEREST_TRX_ID
AND AM.INTEREST_TRX_ID IS NOT NULL
AND AM.LOAN_ID = LOAN.LOAN_ID AND(TRUNC(REC.APPLY_DATE) BETWEEN TRUNC(SYSDATE
, 'YYYY')
AND TRUNC(ADD_MONTHS(SYSDATE
, 12)
, 'YYYY') -1)
AND NVL(AM.PHASE
, 'TERM') = NVL(LOAN.CURRENT_PHASE
, 'TERM'))
, (SELECT NVL(SUM(REC.AMOUNT_APPLIED)
, 0)
FROM LNS_AMORTIZATION_SCHEDS AM
, AR_RECEIVABLE_APPLICATIONS_ALL REC
WHERE REC.ORG_ID = LOAN.ORG_ID
AND REC.APPLICATION_TYPE = 'CASH'
AND REC.APPLIED_CUSTOMER_TRX_ID = AM.FEE_TRX_ID
AND AM.FEE_TRX_ID IS NOT NULL
AND AM.LOAN_ID = LOAN.LOAN_ID AND(TRUNC(REC.APPLY_DATE) BETWEEN TRUNC(SYSDATE
, 'YYYY')
AND TRUNC(ADD_MONTHS(SYSDATE
, 12)
, 'YYYY') -1)
AND NVL(AM.PHASE
, 'TERM') = NVL(LOAN.CURRENT_PHASE
, 'TERM'))
, (SELECT NVL(SUM(REC.AMOUNT_APPLIED)
, 0)
FROM LNS_AMORTIZATION_SCHEDS AM
, AR_RECEIVABLE_APPLICATIONS_ALL REC
WHERE REC.ORG_ID = LOAN.ORG_ID
AND REC.APPLICATION_TYPE = 'CASH'
AND REC.APPLIED_CUSTOMER_TRX_ID = AM.PRINCIPAL_TRX_ID
AND AM.PRINCIPAL_TRX_ID IS NOT NULL
AND AM.LOAN_ID = LOAN.LOAN_ID AND(TRUNC(REC.APPLY_DATE) BETWEEN TRUNC(ADD_MONTHS(SYSDATE
, -12)
, 'YYYY')
AND TRUNC(TRUNC(SYSDATE
, 'YYYY') -1))
AND NVL(AM.PHASE
, 'TERM') = NVL(LOAN.CURRENT_PHASE
, 'TERM'))
, (SELECT NVL(SUM(REC.AMOUNT_APPLIED)
, 0)
FROM LNS_AMORTIZATION_SCHEDS AM
, AR_RECEIVABLE_APPLICATIONS_ALL REC
WHERE REC.ORG_ID = LOAN.ORG_ID
AND REC.APPLICATION_TYPE = 'CASH'
AND REC.APPLIED_CUSTOMER_TRX_ID = AM.INTEREST_TRX_ID
AND AM.INTEREST_TRX_ID IS NOT NULL
AND AM.LOAN_ID = LOAN.LOAN_ID AND(TRUNC(REC.APPLY_DATE) BETWEEN TRUNC(ADD_MONTHS(SYSDATE
, -12)
, 'YYYY')
AND TRUNC(TRUNC(SYSDATE
, 'YYYY') -1))
AND NVL(AM.PHASE
, 'TERM') = NVL(LOAN.CURRENT_PHASE
, 'TERM'))
, (SELECT NVL(SUM(REC.AMOUNT_APPLIED)
, 0)
FROM LNS_AMORTIZATION_SCHEDS AM
, AR_RECEIVABLE_APPLICATIONS_ALL REC
WHERE REC.ORG_ID = LOAN.ORG_ID
AND REC.APPLICATION_TYPE = 'CASH'
AND REC.APPLIED_CUSTOMER_TRX_ID = AM.FEE_TRX_ID
AND AM.FEE_TRX_ID IS NOT NULL
AND AM.LOAN_ID = LOAN.LOAN_ID AND(TRUNC(REC.APPLY_DATE) BETWEEN TRUNC(ADD_MONTHS(SYSDATE
, -12)
, 'YYYY')
AND TRUNC(TRUNC(SYSDATE
, 'YYYY') -1))
AND NVL(AM.PHASE
, 'TERM') = NVL(LOAN.CURRENT_PHASE
, 'TERM'))
, ( CASE WHEN(LOAN.LOAN_STATUS = 'ACTIVE' OR LOAN.LOAN_STATUS = 'DEFAULT' OR LOAN.LOAN_STATUS = 'DELINQUENT' OR LOAN.LOAN_STATUS = 'INCOMPLETE' OR LOAN.LOAN_STATUS = 'PENDING' OR LOAN.LOAN_STATUS = 'APPROVED') THEN NVL( (SELECT SUM(SCH.AMOUNT_DUE_REMAINING)
FROM AR_PAYMENT_SCHEDULES_ALL SCH
, LNS_AMORTIZATION_SCHEDS AM
WHERE SCH.ORG_ID = LOAN.ORG_ID
AND AM.LOAN_ID = LOAN.LOAN_ID
AND SCH.CUSTOMER_TRX_ID = AM.PRINCIPAL_TRX_ID
AND TRUNC(AM.DUE_DATE) < TRUNC(SYSDATE) AND(AM.REVERSED_FLAG IS NULL OR AM.REVERSED_FLAG = 'N')
AND NVL(AM.PHASE
, 'TERM') = NVL(LOAN.CURRENT_PHASE
, 'TERM')
AND AM.REAMORTIZATION_AMOUNT IS NULL)
, 0) ELSE 0 END)
, ( CASE WHEN(LOAN.LOAN_STATUS = 'ACTIVE' OR LOAN.LOAN_STATUS = 'DEFAULT' OR LOAN.LOAN_STATUS = 'DELINQUENT' OR LOAN.LOAN_STATUS = 'INCOMPLETE' OR LOAN.LOAN_STATUS = 'PENDING' OR LOAN.LOAN_STATUS = 'APPROVED') THEN NVL( (SELECT SUM(SCH.AMOUNT_DUE_REMAINING)
FROM AR_PAYMENT_SCHEDULES_ALL SCH
, LNS_AMORTIZATION_SCHEDS AM
WHERE SCH.ORG_ID = LOAN.ORG_ID
AND AM.LOAN_ID = LOAN.LOAN_ID
AND SCH.CUSTOMER_TRX_ID = AM.INTEREST_TRX_ID
AND TRUNC(AM.DUE_DATE) < TRUNC(SYSDATE) AND(AM.REVERSED_FLAG IS NULL OR AM.REVERSED_FLAG = 'N')
AND NVL(AM.PHASE
, 'TERM') = NVL(LOAN.CURRENT_PHASE
, 'TERM')
AND AM.REAMORTIZATION_AMOUNT IS NULL)
, 0) ELSE 0 END)
, ( CASE WHEN(LOAN.LOAN_STATUS = 'ACTIVE' OR LOAN.LOAN_STATUS = 'DEFAULT' OR LOAN.LOAN_STATUS = 'DELINQUENT' OR LOAN.LOAN_STATUS = 'INCOMPLETE' OR LOAN.LOAN_STATUS = 'PENDING' OR LOAN.LOAN_STATUS = 'APPROVED') THEN NVL( (SELECT SUM(SCH.AMOUNT_DUE_REMAINING)
FROM AR_PAYMENT_SCHEDULES_ALL SCH
, LNS_AMORTIZATION_SCHEDS AM
WHERE SCH.ORG_ID = LOAN.ORG_ID
AND AM.LOAN_ID = LOAN.LOAN_ID
AND SCH.CUSTOMER_TRX_ID = AM.FEE_TRX_ID
AND TRUNC(AM.DUE_DATE) < TRUNC(SYSDATE) AND(AM.REVERSED_FLAG IS NULL OR AM.REVERSED_FLAG = 'N')
AND NVL(AM.PHASE
, 'TERM') = NVL(LOAN.CURRENT_PHASE
, 'TERM')
AND AM.REAMORTIZATION_AMOUNT IS NULL)
, 0) ELSE 0 END)
, ( CASE WHEN(LOAN.LOAN_STATUS = 'ACTIVE' OR LOAN.LOAN_STATUS = 'DEFAULT' OR LOAN.LOAN_STATUS = 'DELINQUENT' OR LOAN.LOAN_STATUS = 'INCOMPLETE' OR LOAN.LOAN_STATUS = 'PENDING' OR LOAN.LOAN_STATUS = 'APPROVED') THEN NVL( (SELECT SUM(SCH.AMOUNT_DUE_REMAINING)
FROM AR_PAYMENT_SCHEDULES_ALL SCH
, LNS_AMORTIZATION_SCHEDS AM
WHERE SCH.ORG_ID = LOAN.ORG_ID
AND AM.LOAN_ID = LOAN.LOAN_ID AND((SCH.CUSTOMER_TRX_ID = AM.PRINCIPAL_TRX_ID) OR(SCH.CUSTOMER_TRX_ID = AM.INTEREST_TRX_ID) OR(SCH.CUSTOMER_TRX_ID = AM.FEE_TRX_ID))
AND TRUNC(AM.DUE_DATE) < TRUNC(SYSDATE) AND(AM.REVERSED_FLAG IS NULL OR AM.REVERSED_FLAG = 'N')
AND NVL(AM.PHASE
, 'TERM') = NVL(LOAN.CURRENT_PHASE
, 'TERM')
AND AM.REAMORTIZATION_AMOUNT IS NULL)
, 0) ELSE 0 END)
, ( CASE WHEN(LOAN.LOAN_STATUS = 'ACTIVE' OR LOAN.LOAN_STATUS = 'DEFAULT' OR LOAN.LOAN_STATUS = 'DELINQUENT' OR LOAN.LOAN_STATUS = 'INCOMPLETE' OR LOAN.LOAN_STATUS = 'PENDING' OR LOAN.LOAN_STATUS = 'APPROVED') THEN NVL( (SELECT COUNT(1)
FROM LNS_AMORTIZATION_SCHEDS AM
WHERE LOAN_ID = LOAN.LOAN_ID
AND TRUNC(DUE_DATE) < TRUNC(SYSDATE) AND(REVERSED_FLAG IS NULL OR REVERSED_FLAG = 'N')
AND REAMORTIZATION_AMOUNT IS NULL
AND NVL(AM.PHASE
, 'TERM') = NVL(LOAN.CURRENT_PHASE
, 'TERM')
AND NVL( (SELECT SUM(AMOUNT_DUE_REMAINING)
FROM AR_PAYMENT_SCHEDULES_ALL
WHERE ORG_ID = LOAN.ORG_ID AND((CUSTOMER_TRX_ID = AM.PRINCIPAL_TRX_ID) OR(CUSTOMER_TRX_ID = AM.INTEREST_TRX_ID) OR(CUSTOMER_TRX_ID = AM.FEE_TRX_ID)))
, 0) > 0)
, 0) ELSE 0 END)
, ( CASE WHEN(LOAN.LOAN_STATUS = 'ACTIVE' OR LOAN.LOAN_STATUS = 'DEFAULT' OR LOAN.LOAN_STATUS = 'DELINQUENT' OR LOAN.LOAN_STATUS = 'INCOMPLETE' OR LOAN.LOAN_STATUS = 'PENDING' OR LOAN.LOAN_STATUS = 'APPROVED') THEN (SELECT MAX(DUE_DATE)
FROM LNS_AMORTIZATION_SCHEDS AM
WHERE LOAN_ID = LOAN.LOAN_ID
AND TRUNC(DUE_DATE) < TRUNC(SYSDATE) AND(REVERSED_FLAG IS NULL OR REVERSED_FLAG = 'N')
AND REAMORTIZATION_AMOUNT IS NULL
AND NVL(AM.PHASE
, 'TERM') = NVL(LOAN.CURRENT_PHASE
, 'TERM')
AND NVL( (SELECT SUM(AMOUNT_DUE_REMAINING)
FROM AR_PAYMENT_SCHEDULES_ALL
WHERE ORG_ID = LOAN.ORG_ID AND((CUSTOMER_TRX_ID = AM.PRINCIPAL_TRX_ID) OR(CUSTOMER_TRX_ID = AM.INTEREST_TRX_ID) OR(CUSTOMER_TRX_ID = AM.FEE_TRX_ID)))
, 0) > 0) ELSE TO_DATE(NULL) END)
, ( CASE WHEN(LOAN.LOAN_STATUS = 'ACTIVE' OR LOAN.LOAN_STATUS = 'DEFAULT' OR LOAN.LOAN_STATUS = 'DELINQUENT' OR LOAN.LOAN_STATUS = 'INCOMPLETE' OR LOAN.LOAN_STATUS = 'PENDING' OR LOAN.LOAN_STATUS = 'APPROVED') THEN (SELECT MIN(DUE_DATE)
FROM LNS_AMORTIZATION_SCHEDS AM
WHERE LOAN_ID = LOAN.LOAN_ID
AND TRUNC(DUE_DATE) < TRUNC(SYSDATE) AND(REVERSED_FLAG IS NULL OR REVERSED_FLAG = 'N')
AND REAMORTIZATION_AMOUNT IS NULL
AND NVL(AM.PHASE
, 'TERM') = NVL(LOAN.CURRENT_PHASE
, 'TERM')
AND NVL( (SELECT SUM(AMOUNT_DUE_REMAINING)
FROM AR_PAYMENT_SCHEDULES_ALL
WHERE ORG_ID = LOAN.ORG_ID AND((CUSTOMER_TRX_ID = AM.PRINCIPAL_TRX_ID) OR(CUSTOMER_TRX_ID = AM.INTEREST_TRX_ID) OR(CUSTOMER_TRX_ID = AM.FEE_TRX_ID)))
, 0) > 0) ELSE TO_DATE(NULL) END)
, NVL( (SELECT AMOUNT_APPLIED
FROM AR_RECEIVABLE_APPLICATIONS_ALL
WHERE RECEIVABLE_APPLICATION_ID = (SELECT MAX(RECEIVABLE_APPLICATION_ID)
FROM AR_RECEIVABLE_APPLICATIONS_ALL REC
, LNS_AMORTIZATION_SCHEDS AM
WHERE AM.LOAN_ID = LOAN.LOAN_ID
AND REC.ORG_ID = LOAN.ORG_ID
AND REC.APPLIED_CUSTOMER_TRX_ID IN(AM.PRINCIPAL_TRX_ID
, AM.INTEREST_TRX_ID
, AM.FEE_TRX_ID)
AND REC.DISPLAY = 'Y'
AND NVL(AM.PHASE
, 'TERM') = NVL(LOAN.CURRENT_PHASE
, 'TERM')
AND REC.APPLICATION_TYPE = 'CASH'))
, 0)
, (SELECT APPLY_DATE
FROM AR_RECEIVABLE_APPLICATIONS_ALL
WHERE RECEIVABLE_APPLICATION_ID = (SELECT MAX(RECEIVABLE_APPLICATION_ID)
FROM AR_RECEIVABLE_APPLICATIONS_ALL REC
, LNS_AMORTIZATION_SCHEDS AM
WHERE AM.LOAN_ID = LOAN.LOAN_ID
AND REC.ORG_ID = LOAN.ORG_ID
AND REC.APPLIED_CUSTOMER_TRX_ID IN(AM.PRINCIPAL_TRX_ID
, AM.INTEREST_TRX_ID
, AM.FEE_TRX_ID)
AND REC.DISPLAY = 'Y'
AND NVL(AM.PHASE
, 'TERM') = NVL(LOAN.CURRENT_PHASE
, 'TERM')
AND REC.APPLICATION_TYPE = 'CASH') )
, LOAN.ORG_ID
, LOAN.LEGAL_ENTITY_ID
, NVL(LOAN.CURRENT_PHASE
, 'TERM')
FROM LNS_LOAN_HEADERS_ALL LOAN