VIEW: APPS.LNS_PRIN_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 ,'Loan starts' ,trunc(loan.LOAN_START_DATE) ,decode(loan.loan_class_code, 'DIRECT', decode(nvl(loan.BILL_ON_APPR_AMOUNT_FLAG, 'N'), 'Y', loan.requested_amount + nvl((select sum(adj.ADJUSTMENT_AMOUNT) from LNS_LOAN_AMOUNT_ADJS adj WHERE adj.LOAN_ID = loan.loan_id and adj.STATUS = 'APPROVED' and trunc(adj.EFFECTIVE_DATE) <= trunc(loan.LOAN_START_DATE)), 0), (select nvl(sum(disb_line.LINE_AMOUNT), 0) from lns_disb_lines disb_line, lns_disb_headers disb_hdr where disb_hdr.loan_id = loan.loan_id and disb_hdr.disb_header_id = disb_line.disb_header_id and disb_line.STATUS = 'FULLY_FUNDED' and trunc(disb_line.DISBURSEMENT_DATE) <= trunc(loan.LOAN_START_DATE))), 'ERS', (select nvl(sum(lines.REQUESTED_AMOUNT), 0) from lns_loan_lines lines where lines.loan_id = loan.loan_id and lines.STATUS = 'APPROVED' and trunc(lines.ADJUSTMENT_DATE) <= trunc(loan.LOAN_START_DATE))) ,decode(loan.loan_class_code, 'DIRECT', decode(nvl(loan.BILL_ON_APPR_AMOUNT_FLAG, 'N'), 'Y', loan.requested_amount + nvl((select sum(adj.ADJUSTMENT_AMOUNT) from LNS_LOAN_AMOUNT_ADJS adj where adj.LOAN_ID = loan.loan_id and adj.STATUS = 'APPROVED' and trunc(adj.EFFECTIVE_DATE) <= trunc(loan.LOAN_START_DATE)), 0), (select nvl(sum(disb_line.LINE_AMOUNT), 0) from lns_disb_lines disb_line, lns_disb_headers disb_hdr where disb_hdr.loan_id = loan.loan_id and disb_hdr.disb_header_id = disb_line.disb_header_id and disb_line.STATUS = 'FULLY_FUNDED' and trunc(disb_line.DISBURSEMENT_DATE) <= trunc(loan.LOAN_START_DATE))), 'ERS', (select nvl(sum(lines.REQUESTED_AMOUNT), 0) from lns_loan_lines lines where lines.loan_id = loan.loan_id and lines.STATUS = 'APPROVED' and trunc(lines.ADJUSTMENT_DATE) <= trunc(loan.LOAN_START_DATE))) ,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) from lns_loan_headers_all loan ,lns_terms term where loan.loan_id = term.loan_id UNION ALL select loan.loan_id ,to_number(null) ,to_number(null) ,to_date(null) ,to_number(null) ,to_number(null) ,to_number(null) ,to_number(null) ,to_number(null) ,to_number(null) ,null ,decode(loan.loan_class_code, 'DIRECT', 'DISBURSEMENT', 'ERS', 'INVOICE_ADDED') ,decode(nvl(loan.BILL_ON_APPR_AMOUNT_FLAG, 'N'), 'N', decode(loan.loan_class_code, 'DIRECT', disb_line.LINE_AMOUNT, 'ERS', lines.REQUESTED_AMOUNT), 0) ,decode(loan.loan_class_code, 'DIRECT', 'Funded disbursement ' || disb_hdr.DESCRIPTION, 'ERS', 'Added invoice ' || lines.REFERENCE_NUMBER) ,decode(loan.loan_class_code, 'DIRECT', trunc(disb_line.DISBURSEMENT_DATE), 'ERS', trunc(lines.ADJUSTMENT_DATE)) ,decode(loan.loan_class_code, 'DIRECT', decode(nvl(loan.BILL_ON_APPR_AMOUNT_FLAG, 'N'), 'Y', loan.requested_amount + (select nvl(sum(adj.ADJUSTMENT_AMOUNT), 0) from LNS_LOAN_AMOUNT_ADJS adj where adj.LOAN_ID = loan.loan_id and adj.STATUS = 'APPROVED' and trunc(adj.EFFECTIVE_DATE) <= trunc(disb_line.DISBURSEMENT_DATE)), (select nvl(sum(disb_line1.LINE_AMOUNT), 0) from lns_disb_lines disb_line1, lns_disb_headers disb_hdr where disb_hdr.loan_id = loan.loan_id and disb_hdr.disb_header_id = disb_line1.disb_header_id and disb_line1.STATUS = 'FULLY_FUNDED' and trunc(disb_line1.DISBURSEMENT_DATE) <= trunc(disb_line.DISBURSEMENT_DATE))), 'ERS', (select nvl(sum(lines1.REQUESTED_AMOUNT), 0) from lns_loan_lines lines1 where lines1.loan_id = loan.loan_id and nvl(lines1.STATUS, 'APPROVED') = 'APPROVED' and trunc(nvl(lines1.ADJUSTMENT_DATE, loan.loan_start_date)) <= trunc(lines.ADJUSTMENT_DATE))) + (select nvl(sum(psa_prin.TAX_ORIGINAL), 0) from LNS_AMORTIZATION_SCHEDS am1, ar_payment_schedules_all psa_prin where am1.LOAN_ID = loan.loan_id and trunc(am1.DUE_DATE) <= decode(loan.loan_class_code, 'DIRECT', trunc(disb_line.DISBURSEMENT_DATE), 'ERS', trunc(lines.ADJUSTMENT_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_prin.customer_trx_id = am1.principal_trx_id) + (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(loan.loan_class_code, 'DIRECT', trunc(disb_line.DISBURSEMENT_DATE), 'ERS', trunc(lines.ADJUSTMENT_DATE)) and rec1.APPLIED_CUSTOMER_TRX_ID = am1.principal_trx_id and am1.loan_id = loan.loan_id and am1.principal_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(loan.loan_class_code, 'DIRECT', trunc(disb_line.DISBURSEMENT_DATE), 'ERS', trunc(lines.ADJUSTMENT_DATE)) and adj1.CUSTOMER_TRX_ID = am1.principal_trx_id and am1.loan_id = loan.loan_id and am1.principal_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') ,decode(loan.loan_class_code, 'DIRECT', decode(nvl(loan.BILL_ON_APPR_AMOUNT_FLAG, 'N'), 'Y', loan.requested_amount + (select nvl(sum(adj.ADJUSTMENT_AMOUNT), 0) from LNS_LOAN_AMOUNT_ADJS adj where adj.LOAN_ID = loan.loan_id and adj.STATUS = 'APPROVED' and trunc(adj.EFFECTIVE_DATE) <= trunc(disb_line.DISBURSEMENT_DATE)), (select nvl(sum(disb_line1.LINE_AMOUNT), 0) from lns_disb_lines disb_line1, lns_disb_headers disb_hdr where disb_hdr.loan_id = loan.loan_id and disb_hdr.disb_header_id = disb_line1.disb_header_id and disb_line1.STATUS = 'FULLY_FUNDED' and trunc(disb_line1.DISBURSEMENT_DATE) <= trunc(disb_line.DISBURSEMENT_DATE))), 'ERS', (select nvl(sum(lines1.REQUESTED_AMOUNT), 0) from lns_loan_lines lines1 where lines1.loan_id = loan.loan_id and lines1.STATUS = 'APPROVED' and trunc(lines1.ADJUSTMENT_DATE) <= trunc(lines.ADJUSTMENT_DATE))) - (select nvl(sum(am1.PRINCIPAL_AMOUNT), 0) from LNS_AMORTIZATION_SCHEDS am1 where am1.LOAN_ID = loan.loan_id and trunc(am1.DUE_DATE) <= decode(loan.loan_class_code, 'DIRECT', trunc(disb_line.DISBURSEMENT_DATE), 'ERS', trunc(lines.ADJUSTMENT_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 ,1 ,0 from lns_loan_headers_all loan ,lns_terms term ,lns_disb_headers disb_hdr ,lns_disb_lines disb_line ,lns_loan_lines lines where loan.loan_id = term.loan_id and loan.loan_id = disb_hdr.loan_id(+) and disb_hdr.DISB_HEADER_ID = disb_line.DISB_HEADER_ID(+) and disb_line.DISBURSEMENT_DATE(+) is not null and decode(loan.loan_class_code, 'DIRECT', disb_line.STATUS, 'ERS', lines.STATUS) = decode(loan.loan_class_code, 'DIRECT', 'FULLY_FUNDED', 'ERS', 'APPROVED') and loan.loan_id = lines.loan_id(+) and disb_hdr.phase(+) = '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.principal_trx_id ,to_number(null) ,decode(am.PARENT_AMORTIZATION_ID, null, 'Installment ' || am.PAYMENT_NUMBER, 'Manual Installment ' || am.PAYMENT_NUMBER) ,'BILLING' ,(am.PRINCIPAL_AMOUNT+am.INTEREST_AMOUNT+am.FEE_AMOUNT) ,decode(am.PARENT_AMORTIZATION_ID, null, 'Billed scheduled installment ' || am.PAYMENT_NUMBER, 'Billed manual installment ' || am.PAYMENT_NUMBER) ,decode(sign(trunc(am.CREATION_DATE)-trunc(am.DUE_DATE)), 1, trunc(am.DUE_DATE), trunc(am.CREATION_DATE)) ,decode(loan.loan_class_code, 'DIRECT', decode(nvl(loan.BILL_ON_APPR_AMOUNT_FLAG, 'N'), 'Y', loan.requested_amount + (select nvl(sum(adj.ADJUSTMENT_AMOUNT), 0) from LNS_LOAN_AMOUNT_ADJS adj where adj.LOAN_ID = loan.loan_id and adj.STATUS = 'APPROVED' and trunc(adj.EFFECTIVE_DATE) <= decode(sign(trunc(am.CREATION_DATE)-trunc(am.DUE_DATE)), 1, trunc(am.DUE_DATE), trunc(am.CREATION_DATE))), (select nvl(sum(disb_line.LINE_AMOUNT), 0) from lns_disb_lines disb_line, lns_disb_headers disb_hdr where disb_hdr.loan_id = loan.loan_id and disb_hdr.disb_header_id = disb_line.disb_header_id and disb_line.STATUS = 'FULLY_FUNDED' and trunc(disb_line.DISBURSEMENT_DATE) <= decode(sign(trunc(am.CREATION_DATE)-trunc(am.DUE_DATE)), 1, trunc(am.DUE_DATE), trunc(am.CREATION_DATE)))), 'ERS', (select nvl(sum(lines.REQUESTED_AMOUNT), 0) from lns_loan_lines lines where lines.loan_id = loan.loan_id and lines.STATUS = 'APPROVED' and trunc(lines.ADJUSTMENT_DATE) <= decode(sign(trunc(am.CREATION_DATE)-trunc(am.DUE_DATE)), 1, trunc(am.DUE_DATE), trunc(am.CREATION_DATE)))) + (select nvl(sum(psa_prin.TAX_ORIGINAL), 0) from LNS_AMORTIZATION_SCHEDS am1, ar_payment_schedules_all psa_prin 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_prin.customer_trx_id = am1.principal_trx_id) + (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.principal_trx_id and am1.loan_id = loan.loan_id and am1.principal_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.principal_trx_id and am1.loan_id = loan.loan_id and am1.principal_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') ,decode(loan.loan_class_code, 'DIRECT', decode(nvl(loan.BILL_ON_APPR_AMOUNT_FLAG, 'N'), 'Y', loan.requested_amount + (select nvl(sum(adj.ADJUSTMENT_AMOUNT), 0) from LNS_LOAN_AMOUNT_ADJS adj where adj.LOAN_ID = loan.loan_id and adj.STATUS = 'APPROVED' and trunc(adj.EFFECTIVE_DATE) <= decode(sign(trunc(am.CREATION_DATE)-trunc(am.DUE_DATE)), 1, trunc(am.DUE_DATE), trunc(am.CREATION_DATE))), (select nvl(sum(disb_line.LINE_AMOUNT), 0) from lns_disb_lines disb_line, lns_disb_headers disb_hdr where disb_hdr.loan_id = loan.loan_id and disb_hdr.disb_header_id = disb_line.disb_header_id and disb_line.STATUS = 'FULLY_FUNDED' and trunc(disb_line.DISBURSEMENT_DATE) <= decode(sign(trunc(am.CREATION_DATE)-trunc(am.DUE_DATE)), 1, trunc(am.DUE_DATE), trunc(am.CREATION_DATE)))), 'ERS', (select nvl(sum(lines.REQUESTED_AMOUNT), 0) from lns_loan_lines lines where lines.loan_id = loan.loan_id and lines.STATUS = 'APPROVED' and trunc(lines.ADJUSTMENT_DATE) <= decode(sign(trunc(am.CREATION_DATE)-trunc(am.DUE_DATE)), 1, trunc(am.DUE_DATE), trunc(am.CREATION_DATE)))) - (select nvl(sum(am1.PRINCIPAL_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, 3, 4) ,(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) from lns_loan_headers_all loan ,LNS_AMORTIZATION_SCHEDS am ,lns_terms term where loan.loan_id = am.loan_id and loan.loan_id = term.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.principal_trx_id ,null ,null ,'DUE' ,(am.PRINCIPAL_AMOUNT+am.INTEREST_AMOUNT+am.FEE_AMOUNT) ,decode(am.PARENT_AMORTIZATION_ID, null, 'Due scheduled installment ' || am.PAYMENT_NUMBER, 'Due manual installment ' || am.PAYMENT_NUMBER) ,trunc(am.DUE_DATE) ,decode(loan.loan_class_code, 'DIRECT', decode(nvl(loan.BILL_ON_APPR_AMOUNT_FLAG, 'N'), 'Y', loan.requested_amount + (select nvl(sum(adj.ADJUSTMENT_AMOUNT), 0) from LNS_LOAN_AMOUNT_ADJS adj where adj.LOAN_ID = loan.loan_id and adj.STATUS = 'APPROVED' and trunc(adj.EFFECTIVE_DATE) <= trunc(am.DUE_DATE)), (select nvl(sum(disb_line.LINE_AMOUNT), 0) from lns_disb_lines disb_line, lns_disb_headers disb_hdr where disb_hdr.loan_id = loan.loan_id and disb_hdr.disb_header_id = disb_line.disb_header_id and disb_line.STATUS = 'FULLY_FUNDED' and trunc(disb_line.DISBURSEMENT_DATE) <= trunc(am.DUE_DATE))), 'ERS', (select nvl(sum(lines.REQUESTED_AMOUNT), 0) from lns_loan_lines lines where lines.loan_id = loan.loan_id and lines.STATUS = 'APPROVED' and trunc(lines.ADJUSTMENT_DATE) <= trunc(am.DUE_DATE))) + (select nvl(sum(psa_prin.TAX_ORIGINAL), 0) from LNS_AMORTIZATION_SCHEDS am1, ar_payment_schedules_all psa_prin 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_prin.customer_trx_id = am1.principal_trx_id) + (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.principal_trx_id and am1.loan_id = loan.loan_id and am1.principal_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.principal_trx_id and am1.loan_id = loan.loan_id and am1.principal_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') ,decode(loan.loan_class_code, 'DIRECT', decode(nvl(loan.BILL_ON_APPR_AMOUNT_FLAG, 'N'), 'Y', loan.requested_amount + (select nvl(sum(adj.ADJUSTMENT_AMOUNT), 0) from LNS_LOAN_AMOUNT_ADJS adj where adj.LOAN_ID = loan.loan_id and adj.STATUS = 'APPROVED' and trunc(adj.EFFECTIVE_DATE) <= trunc(am.DUE_DATE)), (select nvl(sum(disb_line.LINE_AMOUNT), 0) from lns_disb_lines disb_line, lns_disb_headers disb_hdr where disb_hdr.loan_id = loan.loan_id and disb_hdr.disb_header_id = disb_line.disb_header_id and disb_line.STATUS = 'FULLY_FUNDED' and trunc(disb_line.DISBURSEMENT_DATE) <= trunc(am.DUE_DATE))), 'ERS', (select nvl(sum(lines.REQUESTED_AMOUNT), 0) from lns_loan_lines lines where lines.loan_id = loan.loan_id and lines.STATUS = 'APPROVED' and trunc(lines.ADJUSTMENT_DATE) <= trunc(am.DUE_DATE))) - (select nvl(sum(am1.PRINCIPAL_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) from lns_loan_headers_all loan ,LNS_AMORTIZATION_SCHEDS am ,lns_terms term where loan.loan_id = am.loan_id and loan.loan_id = term.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 ,trx.customer_trx_id ,trx_psa.payment_schedule_id ,trx.trx_number ,rec_psa.class ,-rec.amount_applied ,decode(rec_psa.class, 'PMT', 'Applied receipt ', 'CM', 'Applied credit memo ') || rec_psa.trx_number ,trunc(rec.apply_date) ,decode(loan.loan_class_code, 'DIRECT', decode(nvl(loan.BILL_ON_APPR_AMOUNT_FLAG, 'N'), 'Y', loan.requested_amount + (select nvl(sum(adj.ADJUSTMENT_AMOUNT), 0) from LNS_LOAN_AMOUNT_ADJS adj where adj.LOAN_ID = loan.loan_id and adj.STATUS = 'APPROVED' and trunc(adj.EFFECTIVE_DATE) <= trunc(rec.apply_date)), (select nvl(sum(disb_line.LINE_AMOUNT), 0) from lns_disb_lines disb_line, lns_disb_headers disb_hdr where disb_hdr.loan_id = loan.loan_id and disb_hdr.disb_header_id = disb_line.disb_header_id and disb_line.STATUS = 'FULLY_FUNDED' and trunc(disb_line.DISBURSEMENT_DATE) <= trunc(rec.apply_date))), 'ERS', (select nvl(sum(lines.REQUESTED_AMOUNT), 0) from lns_loan_lines lines where lines.loan_id = loan.loan_id and lines.STATUS = 'APPROVED' and trunc(lines.ADJUSTMENT_DATE) <= trunc(rec.apply_date))) + (select nvl(sum(psa_prin.TAX_ORIGINAL), 0) from LNS_AMORTIZATION_SCHEDS am1, ar_payment_schedules_all psa_prin 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_prin.customer_trx_id = am1.principal_trx_id) + (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.principal_trx_id and am1.loan_id = loan.loan_id and am1.principal_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.principal_trx_id and am1.loan_id = loan.loan_id and am1.principal_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') ,decode(loan.loan_class_code, 'DIRECT', decode(nvl(loan.BILL_ON_APPR_AMOUNT_FLAG, 'N'), 'Y', loan.requested_amount + (select nvl(sum(adj.ADJUSTMENT_AMOUNT), 0) from LNS_LOAN_AMOUNT_ADJS adj where adj.LOAN_ID = loan.loan_id and adj.STATUS = 'APPROVED' and trunc(adj.EFFECTIVE_DATE) <= trunc(rec.apply_date)), (select nvl(sum(disb_line.LINE_AMOUNT), 0) from lns_disb_lines disb_line, lns_disb_headers disb_hdr where disb_hdr.loan_id = loan.loan_id and disb_hdr.disb_header_id = disb_line.disb_header_id and disb_line.STATUS = 'FULLY_FUNDED' and trunc(disb_line.DISBURSEMENT_DATE) <= trunc(rec.apply_date))), 'ERS', (select nvl(sum(lines.REQUESTED_AMOUNT), 0) from lns_loan_lines lines where lines.loan_id = loan.loan_id and lines.STATUS = 'APPROVED' and trunc(lines.ADJUSTMENT_DATE) <= trunc(rec.apply_date))) - (select nvl(sum(am1.PRINCIPAL_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) from RA_CUSTOMER_TRX_ALL trx ,lns_loan_headers_all loan ,lns_terms term ,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 loan.loan_id = term.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.principal_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' 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 ,trx.customer_trx_id ,trx_psa.payment_schedule_id ,trx.trx_number ,'ADJUSTMENT' ,adj.amount ,'Applied adjustment ' || adj.adjustment_number ,trunc(adj.apply_date) ,decode(loan.loan_class_code, 'DIRECT', decode(nvl(loan.BILL_ON_APPR_AMOUNT_FLAG, 'N'), 'Y', loan.requested_amount + (select nvl(sum(adj.ADJUSTMENT_AMOUNT), 0) from LNS_LOAN_AMOUNT_ADJS adj where adj.LOAN_ID = loan.loan_id and adj.STATUS = 'APPROVED' and trunc(adj.EFFECTIVE_DATE) <= trunc(adj.apply_date)), (select nvl(sum(disb_line.LINE_AMOUNT), 0) from lns_disb_lines disb_line, lns_disb_headers disb_hdr where disb_hdr.loan_id = loan.loan_id and disb_hdr.disb_header_id = disb_line.disb_header_id and disb_line.STATUS = 'FULLY_FUNDED' and trunc(disb_line.DISBURSEMENT_DATE) <= trunc(adj.apply_date))), 'ERS', (select nvl(sum(lines.REQUESTED_AMOUNT), 0) from lns_loan_lines lines where lines.loan_id = loan.loan_id and lines.STATUS = 'APPROVED' and trunc(lines.ADJUSTMENT_DATE) <= trunc(adj.apply_date))) + (select nvl(sum(psa_prin.TAX_ORIGINAL), 0) from LNS_AMORTIZATION_SCHEDS am1, ar_payment_schedules_all psa_prin 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_prin.customer_trx_id = am1.principal_trx_id) + (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.principal_trx_id and am1.loan_id = loan.loan_id and am1.principal_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.principal_trx_id and am1.loan_id = loan.loan_id and am1.principal_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') ,decode(loan.loan_class_code, 'DIRECT', decode(nvl(loan.BILL_ON_APPR_AMOUNT_FLAG, 'N'), 'Y', loan.requested_amount + (select nvl(sum(adj.ADJUSTMENT_AMOUNT), 0) from LNS_LOAN_AMOUNT_ADJS adj where adj.LOAN_ID = loan.loan_id and adj.STATUS = 'APPROVED' and trunc(adj.EFFECTIVE_DATE) <= trunc(adj.apply_date)), (select nvl(sum(disb_line.LINE_AMOUNT), 0) from lns_disb_lines disb_line, lns_disb_headers disb_hdr where disb_hdr.loan_id = loan.loan_id and disb_hdr.disb_header_id = disb_line.disb_header_id and disb_line.STATUS = 'FULLY_FUNDED' and trunc(disb_line.DISBURSEMENT_DATE) <= trunc(adj.apply_date))), 'ERS', (select nvl(sum(lines.REQUESTED_AMOUNT), 0) from lns_loan_lines lines where lines.loan_id = loan.loan_id and lines.STATUS = 'APPROVED' and trunc(lines.ADJUSTMENT_DATE) <= trunc(adj.apply_date))) - (select nvl(sum(am1.PRINCIPAL_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) 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_terms term ,LNS_AMORTIZATION_SCHEDS am where loan.loan_id = am.loan_id and loan.loan_id = term.loan_id and trx.customer_trx_id = am.principal_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' and nvl(am.phase, 'TERM') = 'TERM'

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
, 'LOAN STARTS'
, TRUNC(LOAN.LOAN_START_DATE)
, DECODE(LOAN.LOAN_CLASS_CODE
, 'DIRECT'
, DECODE(NVL(LOAN.BILL_ON_APPR_AMOUNT_FLAG
, 'N')
, 'Y'
, LOAN.REQUESTED_AMOUNT + NVL((SELECT SUM(ADJ.ADJUSTMENT_AMOUNT)
FROM LNS_LOAN_AMOUNT_ADJS ADJ
WHERE ADJ.LOAN_ID = LOAN.LOAN_ID
AND ADJ.STATUS = 'APPROVED'
AND TRUNC(ADJ.EFFECTIVE_DATE) <= TRUNC(LOAN.LOAN_START_DATE))
, 0)
, (SELECT NVL(SUM(DISB_LINE.LINE_AMOUNT)
, 0)
FROM LNS_DISB_LINES DISB_LINE
, LNS_DISB_HEADERS DISB_HDR
WHERE DISB_HDR.LOAN_ID = LOAN.LOAN_ID
AND DISB_HDR.DISB_HEADER_ID = DISB_LINE.DISB_HEADER_ID
AND DISB_LINE.STATUS = 'FULLY_FUNDED'
AND TRUNC(DISB_LINE.DISBURSEMENT_DATE) <= TRUNC(LOAN.LOAN_START_DATE)))
, 'ERS'
, (SELECT NVL(SUM(LINES.REQUESTED_AMOUNT)
, 0)
FROM LNS_LOAN_LINES LINES
WHERE LINES.LOAN_ID = LOAN.LOAN_ID
AND LINES.STATUS = 'APPROVED'
AND TRUNC(LINES.ADJUSTMENT_DATE) <= TRUNC(LOAN.LOAN_START_DATE)))
, DECODE(LOAN.LOAN_CLASS_CODE
, 'DIRECT'
, DECODE(NVL(LOAN.BILL_ON_APPR_AMOUNT_FLAG
, 'N')
, 'Y'
, LOAN.REQUESTED_AMOUNT + NVL((SELECT SUM(ADJ.ADJUSTMENT_AMOUNT)
FROM LNS_LOAN_AMOUNT_ADJS ADJ
WHERE ADJ.LOAN_ID = LOAN.LOAN_ID
AND ADJ.STATUS = 'APPROVED'
AND TRUNC(ADJ.EFFECTIVE_DATE) <= TRUNC(LOAN.LOAN_START_DATE))
, 0)
, (SELECT NVL(SUM(DISB_LINE.LINE_AMOUNT)
, 0)
FROM LNS_DISB_LINES DISB_LINE
, LNS_DISB_HEADERS DISB_HDR
WHERE DISB_HDR.LOAN_ID = LOAN.LOAN_ID
AND DISB_HDR.DISB_HEADER_ID = DISB_LINE.DISB_HEADER_ID
AND DISB_LINE.STATUS = 'FULLY_FUNDED'
AND TRUNC(DISB_LINE.DISBURSEMENT_DATE) <= TRUNC(LOAN.LOAN_START_DATE)))
, 'ERS'
, (SELECT NVL(SUM(LINES.REQUESTED_AMOUNT)
, 0)
FROM LNS_LOAN_LINES LINES
WHERE LINES.LOAN_ID = LOAN.LOAN_ID
AND LINES.STATUS = 'APPROVED'
AND TRUNC(LINES.ADJUSTMENT_DATE) <= TRUNC(LOAN.LOAN_START_DATE)))
, 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)
FROM LNS_LOAN_HEADERS_ALL LOAN
, LNS_TERMS TERM
WHERE LOAN.LOAN_ID = TERM.LOAN_ID UNION ALL SELECT LOAN.LOAN_ID
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_DATE(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, NULL
, DECODE(LOAN.LOAN_CLASS_CODE
, 'DIRECT'
, 'DISBURSEMENT'
, 'ERS'
, 'INVOICE_ADDED')
, DECODE(NVL(LOAN.BILL_ON_APPR_AMOUNT_FLAG
, 'N')
, 'N'
, DECODE(LOAN.LOAN_CLASS_CODE
, 'DIRECT'
, DISB_LINE.LINE_AMOUNT
, 'ERS'
, LINES.REQUESTED_AMOUNT)
, 0)
, DECODE(LOAN.LOAN_CLASS_CODE
, 'DIRECT'
, 'FUNDED DISBURSEMENT ' || DISB_HDR.DESCRIPTION
, 'ERS'
, 'ADDED INVOICE ' || LINES.REFERENCE_NUMBER)
, DECODE(LOAN.LOAN_CLASS_CODE
, 'DIRECT'
, TRUNC(DISB_LINE.DISBURSEMENT_DATE)
, 'ERS'
, TRUNC(LINES.ADJUSTMENT_DATE))
, DECODE(LOAN.LOAN_CLASS_CODE
, 'DIRECT'
, DECODE(NVL(LOAN.BILL_ON_APPR_AMOUNT_FLAG
, 'N')
, 'Y'
, LOAN.REQUESTED_AMOUNT + (SELECT NVL(SUM(ADJ.ADJUSTMENT_AMOUNT)
, 0)
FROM LNS_LOAN_AMOUNT_ADJS ADJ
WHERE ADJ.LOAN_ID = LOAN.LOAN_ID
AND ADJ.STATUS = 'APPROVED'
AND TRUNC(ADJ.EFFECTIVE_DATE) <= TRUNC(DISB_LINE.DISBURSEMENT_DATE))
, (SELECT NVL(SUM(DISB_LINE1.LINE_AMOUNT)
, 0)
FROM LNS_DISB_LINES DISB_LINE1
, LNS_DISB_HEADERS DISB_HDR
WHERE DISB_HDR.LOAN_ID = LOAN.LOAN_ID
AND DISB_HDR.DISB_HEADER_ID = DISB_LINE1.DISB_HEADER_ID
AND DISB_LINE1.STATUS = 'FULLY_FUNDED'
AND TRUNC(DISB_LINE1.DISBURSEMENT_DATE) <= TRUNC(DISB_LINE.DISBURSEMENT_DATE)))
, 'ERS'
, (SELECT NVL(SUM(LINES1.REQUESTED_AMOUNT)
, 0)
FROM LNS_LOAN_LINES LINES1
WHERE LINES1.LOAN_ID = LOAN.LOAN_ID
AND NVL(LINES1.STATUS
, 'APPROVED') = 'APPROVED'
AND TRUNC(NVL(LINES1.ADJUSTMENT_DATE
, LOAN.LOAN_START_DATE)) <= TRUNC(LINES.ADJUSTMENT_DATE))) + (SELECT NVL(SUM(PSA_PRIN.TAX_ORIGINAL)
, 0)
FROM LNS_AMORTIZATION_SCHEDS AM1
, AR_PAYMENT_SCHEDULES_ALL PSA_PRIN
WHERE AM1.LOAN_ID = LOAN.LOAN_ID
AND TRUNC(AM1.DUE_DATE) <= DECODE(LOAN.LOAN_CLASS_CODE
, 'DIRECT'
, TRUNC(DISB_LINE.DISBURSEMENT_DATE)
, 'ERS'
, TRUNC(LINES.ADJUSTMENT_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_PRIN.CUSTOMER_TRX_ID = AM1.PRINCIPAL_TRX_ID) + (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(LOAN.LOAN_CLASS_CODE
, 'DIRECT'
, TRUNC(DISB_LINE.DISBURSEMENT_DATE)
, 'ERS'
, TRUNC(LINES.ADJUSTMENT_DATE))
AND REC1.APPLIED_CUSTOMER_TRX_ID = AM1.PRINCIPAL_TRX_ID
AND AM1.LOAN_ID = LOAN.LOAN_ID
AND AM1.PRINCIPAL_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(LOAN.LOAN_CLASS_CODE
, 'DIRECT'
, TRUNC(DISB_LINE.DISBURSEMENT_DATE)
, 'ERS'
, TRUNC(LINES.ADJUSTMENT_DATE))
AND ADJ1.CUSTOMER_TRX_ID = AM1.PRINCIPAL_TRX_ID
AND AM1.LOAN_ID = LOAN.LOAN_ID
AND AM1.PRINCIPAL_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')
, DECODE(LOAN.LOAN_CLASS_CODE
, 'DIRECT'
, DECODE(NVL(LOAN.BILL_ON_APPR_AMOUNT_FLAG
, 'N')
, 'Y'
, LOAN.REQUESTED_AMOUNT + (SELECT NVL(SUM(ADJ.ADJUSTMENT_AMOUNT)
, 0)
FROM LNS_LOAN_AMOUNT_ADJS ADJ
WHERE ADJ.LOAN_ID = LOAN.LOAN_ID
AND ADJ.STATUS = 'APPROVED'
AND TRUNC(ADJ.EFFECTIVE_DATE) <= TRUNC(DISB_LINE.DISBURSEMENT_DATE))
, (SELECT NVL(SUM(DISB_LINE1.LINE_AMOUNT)
, 0)
FROM LNS_DISB_LINES DISB_LINE1
, LNS_DISB_HEADERS DISB_HDR
WHERE DISB_HDR.LOAN_ID = LOAN.LOAN_ID
AND DISB_HDR.DISB_HEADER_ID = DISB_LINE1.DISB_HEADER_ID
AND DISB_LINE1.STATUS = 'FULLY_FUNDED'
AND TRUNC(DISB_LINE1.DISBURSEMENT_DATE) <= TRUNC(DISB_LINE.DISBURSEMENT_DATE)))
, 'ERS'
, (SELECT NVL(SUM(LINES1.REQUESTED_AMOUNT)
, 0)
FROM LNS_LOAN_LINES LINES1
WHERE LINES1.LOAN_ID = LOAN.LOAN_ID
AND LINES1.STATUS = 'APPROVED'
AND TRUNC(LINES1.ADJUSTMENT_DATE) <= TRUNC(LINES.ADJUSTMENT_DATE))) - (SELECT NVL(SUM(AM1.PRINCIPAL_AMOUNT)
, 0)
FROM LNS_AMORTIZATION_SCHEDS AM1
WHERE AM1.LOAN_ID = LOAN.LOAN_ID
AND TRUNC(AM1.DUE_DATE) <= DECODE(LOAN.LOAN_CLASS_CODE
, 'DIRECT'
, TRUNC(DISB_LINE.DISBURSEMENT_DATE)
, 'ERS'
, TRUNC(LINES.ADJUSTMENT_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
, 1
, 0
FROM LNS_LOAN_HEADERS_ALL LOAN
, LNS_TERMS TERM
, LNS_DISB_HEADERS DISB_HDR
, LNS_DISB_LINES DISB_LINE
, LNS_LOAN_LINES LINES
WHERE LOAN.LOAN_ID = TERM.LOAN_ID
AND LOAN.LOAN_ID = DISB_HDR.LOAN_ID(+)
AND DISB_HDR.DISB_HEADER_ID = DISB_LINE.DISB_HEADER_ID(+)
AND DISB_LINE.DISBURSEMENT_DATE(+) IS NOT NULL
AND DECODE(LOAN.LOAN_CLASS_CODE
, 'DIRECT'
, DISB_LINE.STATUS
, 'ERS'
, LINES.STATUS) = DECODE(LOAN.LOAN_CLASS_CODE
, 'DIRECT'
, 'FULLY_FUNDED'
, 'ERS'
, 'APPROVED')
AND LOAN.LOAN_ID = LINES.LOAN_ID(+)
AND DISB_HDR.PHASE(+) = '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.PRINCIPAL_TRX_ID
, TO_NUMBER(NULL)
, DECODE(AM.PARENT_AMORTIZATION_ID
, NULL
, 'INSTALLMENT ' || AM.PAYMENT_NUMBER
, 'MANUAL INSTALLMENT ' || AM.PAYMENT_NUMBER)
, 'BILLING'
, (AM.PRINCIPAL_AMOUNT+AM.INTEREST_AMOUNT+AM.FEE_AMOUNT)
, DECODE(AM.PARENT_AMORTIZATION_ID
, NULL
, 'BILLED SCHEDULED INSTALLMENT ' || AM.PAYMENT_NUMBER
, 'BILLED MANUAL INSTALLMENT ' || AM.PAYMENT_NUMBER)
, DECODE(SIGN(TRUNC(AM.CREATION_DATE)-TRUNC(AM.DUE_DATE))
, 1
, TRUNC(AM.DUE_DATE)
, TRUNC(AM.CREATION_DATE))
, DECODE(LOAN.LOAN_CLASS_CODE
, 'DIRECT'
, DECODE(NVL(LOAN.BILL_ON_APPR_AMOUNT_FLAG
, 'N')
, 'Y'
, LOAN.REQUESTED_AMOUNT + (SELECT NVL(SUM(ADJ.ADJUSTMENT_AMOUNT)
, 0)
FROM LNS_LOAN_AMOUNT_ADJS ADJ
WHERE ADJ.LOAN_ID = LOAN.LOAN_ID
AND ADJ.STATUS = 'APPROVED'
AND TRUNC(ADJ.EFFECTIVE_DATE) <= DECODE(SIGN(TRUNC(AM.CREATION_DATE)-TRUNC(AM.DUE_DATE))
, 1
, TRUNC(AM.DUE_DATE)
, TRUNC(AM.CREATION_DATE)))
, (SELECT NVL(SUM(DISB_LINE.LINE_AMOUNT)
, 0)
FROM LNS_DISB_LINES DISB_LINE
, LNS_DISB_HEADERS DISB_HDR
WHERE DISB_HDR.LOAN_ID = LOAN.LOAN_ID
AND DISB_HDR.DISB_HEADER_ID = DISB_LINE.DISB_HEADER_ID
AND DISB_LINE.STATUS = 'FULLY_FUNDED'
AND TRUNC(DISB_LINE.DISBURSEMENT_DATE) <= DECODE(SIGN(TRUNC(AM.CREATION_DATE)-TRUNC(AM.DUE_DATE))
, 1
, TRUNC(AM.DUE_DATE)
, TRUNC(AM.CREATION_DATE))))
, 'ERS'
, (SELECT NVL(SUM(LINES.REQUESTED_AMOUNT)
, 0)
FROM LNS_LOAN_LINES LINES
WHERE LINES.LOAN_ID = LOAN.LOAN_ID
AND LINES.STATUS = 'APPROVED'
AND TRUNC(LINES.ADJUSTMENT_DATE) <= DECODE(SIGN(TRUNC(AM.CREATION_DATE)-TRUNC(AM.DUE_DATE))
, 1
, TRUNC(AM.DUE_DATE)
, TRUNC(AM.CREATION_DATE)))) + (SELECT NVL(SUM(PSA_PRIN.TAX_ORIGINAL)
, 0)
FROM LNS_AMORTIZATION_SCHEDS AM1
, AR_PAYMENT_SCHEDULES_ALL PSA_PRIN
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_PRIN.CUSTOMER_TRX_ID = AM1.PRINCIPAL_TRX_ID) + (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.PRINCIPAL_TRX_ID
AND AM1.LOAN_ID = LOAN.LOAN_ID
AND AM1.PRINCIPAL_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.PRINCIPAL_TRX_ID
AND AM1.LOAN_ID = LOAN.LOAN_ID
AND AM1.PRINCIPAL_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')
, DECODE(LOAN.LOAN_CLASS_CODE
, 'DIRECT'
, DECODE(NVL(LOAN.BILL_ON_APPR_AMOUNT_FLAG
, 'N')
, 'Y'
, LOAN.REQUESTED_AMOUNT + (SELECT NVL(SUM(ADJ.ADJUSTMENT_AMOUNT)
, 0)
FROM LNS_LOAN_AMOUNT_ADJS ADJ
WHERE ADJ.LOAN_ID = LOAN.LOAN_ID
AND ADJ.STATUS = 'APPROVED'
AND TRUNC(ADJ.EFFECTIVE_DATE) <= DECODE(SIGN(TRUNC(AM.CREATION_DATE)-TRUNC(AM.DUE_DATE))
, 1
, TRUNC(AM.DUE_DATE)
, TRUNC(AM.CREATION_DATE)))
, (SELECT NVL(SUM(DISB_LINE.LINE_AMOUNT)
, 0)
FROM LNS_DISB_LINES DISB_LINE
, LNS_DISB_HEADERS DISB_HDR
WHERE DISB_HDR.LOAN_ID = LOAN.LOAN_ID
AND DISB_HDR.DISB_HEADER_ID = DISB_LINE.DISB_HEADER_ID
AND DISB_LINE.STATUS = 'FULLY_FUNDED'
AND TRUNC(DISB_LINE.DISBURSEMENT_DATE) <= DECODE(SIGN(TRUNC(AM.CREATION_DATE)-TRUNC(AM.DUE_DATE))
, 1
, TRUNC(AM.DUE_DATE)
, TRUNC(AM.CREATION_DATE))))
, 'ERS'
, (SELECT NVL(SUM(LINES.REQUESTED_AMOUNT)
, 0)
FROM LNS_LOAN_LINES LINES
WHERE LINES.LOAN_ID = LOAN.LOAN_ID
AND LINES.STATUS = 'APPROVED'
AND TRUNC(LINES.ADJUSTMENT_DATE) <= DECODE(SIGN(TRUNC(AM.CREATION_DATE)-TRUNC(AM.DUE_DATE))
, 1
, TRUNC(AM.DUE_DATE)
, TRUNC(AM.CREATION_DATE)))) - (SELECT NVL(SUM(AM1.PRINCIPAL_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
, 3
, 4)
, (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)
FROM LNS_LOAN_HEADERS_ALL LOAN
, LNS_AMORTIZATION_SCHEDS AM
, LNS_TERMS TERM
WHERE LOAN.LOAN_ID = AM.LOAN_ID
AND LOAN.LOAN_ID = TERM.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.PRINCIPAL_TRX_ID
, NULL
, NULL
, 'DUE'
, (AM.PRINCIPAL_AMOUNT+AM.INTEREST_AMOUNT+AM.FEE_AMOUNT)
, DECODE(AM.PARENT_AMORTIZATION_ID
, NULL
, 'DUE SCHEDULED INSTALLMENT ' || AM.PAYMENT_NUMBER
, 'DUE MANUAL INSTALLMENT ' || AM.PAYMENT_NUMBER)
, TRUNC(AM.DUE_DATE)
, DECODE(LOAN.LOAN_CLASS_CODE
, 'DIRECT'
, DECODE(NVL(LOAN.BILL_ON_APPR_AMOUNT_FLAG
, 'N')
, 'Y'
, LOAN.REQUESTED_AMOUNT + (SELECT NVL(SUM(ADJ.ADJUSTMENT_AMOUNT)
, 0)
FROM LNS_LOAN_AMOUNT_ADJS ADJ
WHERE ADJ.LOAN_ID = LOAN.LOAN_ID
AND ADJ.STATUS = 'APPROVED'
AND TRUNC(ADJ.EFFECTIVE_DATE) <= TRUNC(AM.DUE_DATE))
, (SELECT NVL(SUM(DISB_LINE.LINE_AMOUNT)
, 0)
FROM LNS_DISB_LINES DISB_LINE
, LNS_DISB_HEADERS DISB_HDR
WHERE DISB_HDR.LOAN_ID = LOAN.LOAN_ID
AND DISB_HDR.DISB_HEADER_ID = DISB_LINE.DISB_HEADER_ID
AND DISB_LINE.STATUS = 'FULLY_FUNDED'
AND TRUNC(DISB_LINE.DISBURSEMENT_DATE) <= TRUNC(AM.DUE_DATE)))
, 'ERS'
, (SELECT NVL(SUM(LINES.REQUESTED_AMOUNT)
, 0)
FROM LNS_LOAN_LINES LINES
WHERE LINES.LOAN_ID = LOAN.LOAN_ID
AND LINES.STATUS = 'APPROVED'
AND TRUNC(LINES.ADJUSTMENT_DATE) <= TRUNC(AM.DUE_DATE))) + (SELECT NVL(SUM(PSA_PRIN.TAX_ORIGINAL)
, 0)
FROM LNS_AMORTIZATION_SCHEDS AM1
, AR_PAYMENT_SCHEDULES_ALL PSA_PRIN
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_PRIN.CUSTOMER_TRX_ID = AM1.PRINCIPAL_TRX_ID) + (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.PRINCIPAL_TRX_ID
AND AM1.LOAN_ID = LOAN.LOAN_ID
AND AM1.PRINCIPAL_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.PRINCIPAL_TRX_ID
AND AM1.LOAN_ID = LOAN.LOAN_ID
AND AM1.PRINCIPAL_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')
, DECODE(LOAN.LOAN_CLASS_CODE
, 'DIRECT'
, DECODE(NVL(LOAN.BILL_ON_APPR_AMOUNT_FLAG
, 'N')
, 'Y'
, LOAN.REQUESTED_AMOUNT + (SELECT NVL(SUM(ADJ.ADJUSTMENT_AMOUNT)
, 0)
FROM LNS_LOAN_AMOUNT_ADJS ADJ
WHERE ADJ.LOAN_ID = LOAN.LOAN_ID
AND ADJ.STATUS = 'APPROVED'
AND TRUNC(ADJ.EFFECTIVE_DATE) <= TRUNC(AM.DUE_DATE))
, (SELECT NVL(SUM(DISB_LINE.LINE_AMOUNT)
, 0)
FROM LNS_DISB_LINES DISB_LINE
, LNS_DISB_HEADERS DISB_HDR
WHERE DISB_HDR.LOAN_ID = LOAN.LOAN_ID
AND DISB_HDR.DISB_HEADER_ID = DISB_LINE.DISB_HEADER_ID
AND DISB_LINE.STATUS = 'FULLY_FUNDED'
AND TRUNC(DISB_LINE.DISBURSEMENT_DATE) <= TRUNC(AM.DUE_DATE)))
, 'ERS'
, (SELECT NVL(SUM(LINES.REQUESTED_AMOUNT)
, 0)
FROM LNS_LOAN_LINES LINES
WHERE LINES.LOAN_ID = LOAN.LOAN_ID
AND LINES.STATUS = 'APPROVED'
AND TRUNC(LINES.ADJUSTMENT_DATE) <= TRUNC(AM.DUE_DATE))) - (SELECT NVL(SUM(AM1.PRINCIPAL_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)
FROM LNS_LOAN_HEADERS_ALL LOAN
, LNS_AMORTIZATION_SCHEDS AM
, LNS_TERMS TERM
WHERE LOAN.LOAN_ID = AM.LOAN_ID
AND LOAN.LOAN_ID = TERM.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
, TRX.CUSTOMER_TRX_ID
, TRX_PSA.PAYMENT_SCHEDULE_ID
, TRX.TRX_NUMBER
, REC_PSA.CLASS
, -REC.AMOUNT_APPLIED
, DECODE(REC_PSA.CLASS
, 'PMT'
, 'APPLIED RECEIPT '
, 'CM'
, 'APPLIED CREDIT MEMO ') || REC_PSA.TRX_NUMBER
, TRUNC(REC.APPLY_DATE)
, DECODE(LOAN.LOAN_CLASS_CODE
, 'DIRECT'
, DECODE(NVL(LOAN.BILL_ON_APPR_AMOUNT_FLAG
, 'N')
, 'Y'
, LOAN.REQUESTED_AMOUNT + (SELECT NVL(SUM(ADJ.ADJUSTMENT_AMOUNT)
, 0)
FROM LNS_LOAN_AMOUNT_ADJS ADJ
WHERE ADJ.LOAN_ID = LOAN.LOAN_ID
AND ADJ.STATUS = 'APPROVED'
AND TRUNC(ADJ.EFFECTIVE_DATE) <= TRUNC(REC.APPLY_DATE))
, (SELECT NVL(SUM(DISB_LINE.LINE_AMOUNT)
, 0)
FROM LNS_DISB_LINES DISB_LINE
, LNS_DISB_HEADERS DISB_HDR
WHERE DISB_HDR.LOAN_ID = LOAN.LOAN_ID
AND DISB_HDR.DISB_HEADER_ID = DISB_LINE.DISB_HEADER_ID
AND DISB_LINE.STATUS = 'FULLY_FUNDED'
AND TRUNC(DISB_LINE.DISBURSEMENT_DATE) <= TRUNC(REC.APPLY_DATE)))
, 'ERS'
, (SELECT NVL(SUM(LINES.REQUESTED_AMOUNT)
, 0)
FROM LNS_LOAN_LINES LINES
WHERE LINES.LOAN_ID = LOAN.LOAN_ID
AND LINES.STATUS = 'APPROVED'
AND TRUNC(LINES.ADJUSTMENT_DATE) <= TRUNC(REC.APPLY_DATE))) + (SELECT NVL(SUM(PSA_PRIN.TAX_ORIGINAL)
, 0)
FROM LNS_AMORTIZATION_SCHEDS AM1
, AR_PAYMENT_SCHEDULES_ALL PSA_PRIN
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_PRIN.CUSTOMER_TRX_ID = AM1.PRINCIPAL_TRX_ID) + (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.PRINCIPAL_TRX_ID
AND AM1.LOAN_ID = LOAN.LOAN_ID
AND AM1.PRINCIPAL_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.PRINCIPAL_TRX_ID
AND AM1.LOAN_ID = LOAN.LOAN_ID
AND AM1.PRINCIPAL_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')
, DECODE(LOAN.LOAN_CLASS_CODE
, 'DIRECT'
, DECODE(NVL(LOAN.BILL_ON_APPR_AMOUNT_FLAG
, 'N')
, 'Y'
, LOAN.REQUESTED_AMOUNT + (SELECT NVL(SUM(ADJ.ADJUSTMENT_AMOUNT)
, 0)
FROM LNS_LOAN_AMOUNT_ADJS ADJ
WHERE ADJ.LOAN_ID = LOAN.LOAN_ID
AND ADJ.STATUS = 'APPROVED'
AND TRUNC(ADJ.EFFECTIVE_DATE) <= TRUNC(REC.APPLY_DATE))
, (SELECT NVL(SUM(DISB_LINE.LINE_AMOUNT)
, 0)
FROM LNS_DISB_LINES DISB_LINE
, LNS_DISB_HEADERS DISB_HDR
WHERE DISB_HDR.LOAN_ID = LOAN.LOAN_ID
AND DISB_HDR.DISB_HEADER_ID = DISB_LINE.DISB_HEADER_ID
AND DISB_LINE.STATUS = 'FULLY_FUNDED'
AND TRUNC(DISB_LINE.DISBURSEMENT_DATE) <= TRUNC(REC.APPLY_DATE)))
, 'ERS'
, (SELECT NVL(SUM(LINES.REQUESTED_AMOUNT)
, 0)
FROM LNS_LOAN_LINES LINES
WHERE LINES.LOAN_ID = LOAN.LOAN_ID
AND LINES.STATUS = 'APPROVED'
AND TRUNC(LINES.ADJUSTMENT_DATE) <= TRUNC(REC.APPLY_DATE))) - (SELECT NVL(SUM(AM1.PRINCIPAL_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)
FROM RA_CUSTOMER_TRX_ALL TRX
, LNS_LOAN_HEADERS_ALL LOAN
, LNS_TERMS TERM
, 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 LOAN.LOAN_ID = TERM.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.PRINCIPAL_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'
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
, TRX.CUSTOMER_TRX_ID
, TRX_PSA.PAYMENT_SCHEDULE_ID
, TRX.TRX_NUMBER
, 'ADJUSTMENT'
, ADJ.AMOUNT
, 'APPLIED ADJUSTMENT ' || ADJ.ADJUSTMENT_NUMBER
, TRUNC(ADJ.APPLY_DATE)
, DECODE(LOAN.LOAN_CLASS_CODE
, 'DIRECT'
, DECODE(NVL(LOAN.BILL_ON_APPR_AMOUNT_FLAG
, 'N')
, 'Y'
, LOAN.REQUESTED_AMOUNT + (SELECT NVL(SUM(ADJ.ADJUSTMENT_AMOUNT)
, 0)
FROM LNS_LOAN_AMOUNT_ADJS ADJ
WHERE ADJ.LOAN_ID = LOAN.LOAN_ID
AND ADJ.STATUS = 'APPROVED'
AND TRUNC(ADJ.EFFECTIVE_DATE) <= TRUNC(ADJ.APPLY_DATE))
, (SELECT NVL(SUM(DISB_LINE.LINE_AMOUNT)
, 0)
FROM LNS_DISB_LINES DISB_LINE
, LNS_DISB_HEADERS DISB_HDR
WHERE DISB_HDR.LOAN_ID = LOAN.LOAN_ID
AND DISB_HDR.DISB_HEADER_ID = DISB_LINE.DISB_HEADER_ID
AND DISB_LINE.STATUS = 'FULLY_FUNDED'
AND TRUNC(DISB_LINE.DISBURSEMENT_DATE) <= TRUNC(ADJ.APPLY_DATE)))
, 'ERS'
, (SELECT NVL(SUM(LINES.REQUESTED_AMOUNT)
, 0)
FROM LNS_LOAN_LINES LINES
WHERE LINES.LOAN_ID = LOAN.LOAN_ID
AND LINES.STATUS = 'APPROVED'
AND TRUNC(LINES.ADJUSTMENT_DATE) <= TRUNC(ADJ.APPLY_DATE))) + (SELECT NVL(SUM(PSA_PRIN.TAX_ORIGINAL)
, 0)
FROM LNS_AMORTIZATION_SCHEDS AM1
, AR_PAYMENT_SCHEDULES_ALL PSA_PRIN
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_PRIN.CUSTOMER_TRX_ID = AM1.PRINCIPAL_TRX_ID) + (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.PRINCIPAL_TRX_ID
AND AM1.LOAN_ID = LOAN.LOAN_ID
AND AM1.PRINCIPAL_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.PRINCIPAL_TRX_ID
AND AM1.LOAN_ID = LOAN.LOAN_ID
AND AM1.PRINCIPAL_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')
, DECODE(LOAN.LOAN_CLASS_CODE
, 'DIRECT'
, DECODE(NVL(LOAN.BILL_ON_APPR_AMOUNT_FLAG
, 'N')
, 'Y'
, LOAN.REQUESTED_AMOUNT + (SELECT NVL(SUM(ADJ.ADJUSTMENT_AMOUNT)
, 0)
FROM LNS_LOAN_AMOUNT_ADJS ADJ
WHERE ADJ.LOAN_ID = LOAN.LOAN_ID
AND ADJ.STATUS = 'APPROVED'
AND TRUNC(ADJ.EFFECTIVE_DATE) <= TRUNC(ADJ.APPLY_DATE))
, (SELECT NVL(SUM(DISB_LINE.LINE_AMOUNT)
, 0)
FROM LNS_DISB_LINES DISB_LINE
, LNS_DISB_HEADERS DISB_HDR
WHERE DISB_HDR.LOAN_ID = LOAN.LOAN_ID
AND DISB_HDR.DISB_HEADER_ID = DISB_LINE.DISB_HEADER_ID
AND DISB_LINE.STATUS = 'FULLY_FUNDED'
AND TRUNC(DISB_LINE.DISBURSEMENT_DATE) <= TRUNC(ADJ.APPLY_DATE)))
, 'ERS'
, (SELECT NVL(SUM(LINES.REQUESTED_AMOUNT)
, 0)
FROM LNS_LOAN_LINES LINES
WHERE LINES.LOAN_ID = LOAN.LOAN_ID
AND LINES.STATUS = 'APPROVED'
AND TRUNC(LINES.ADJUSTMENT_DATE) <= TRUNC(ADJ.APPLY_DATE))) - (SELECT NVL(SUM(AM1.PRINCIPAL_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)
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_TERMS TERM
, LNS_AMORTIZATION_SCHEDS AM
WHERE LOAN.LOAN_ID = AM.LOAN_ID
AND LOAN.LOAN_ID = TERM.LOAN_ID
AND TRX.CUSTOMER_TRX_ID = AM.PRINCIPAL_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'
AND NVL(AM.PHASE
, 'TERM') = 'TERM'