[Home] [Help]
565: l_customized varchar2(1);
566:
567: cursor c_customized (p_loan_id number) is
568: SELECT nvl(h.custom_payments_flag, 'N')
569: FROM lns_loan_headers_all h
570: WHERE loan_id = p_loan_id;
571:
572: cursor c_customSchedule(p_loan_id number) is
573: select payment_number installment_number
680: ,x_msg_data => l_msg_data
681: ,x_return_status => l_return_status
682: ,p_col_id => p_loan_id
683: ,p_col_name => 'LOAN_ID'
684: ,p_table_name => 'LNS_LOAN_HEADERS_ALL');
685:
686: if l_return_status <> FND_API.G_RET_STS_SUCCESS then
687: FND_MESSAGE.SET_NAME('LNS', 'LNS_INVALID_VALUE');
688: FND_MESSAGE.SET_TOKEN('PARAMETER', 'LOAN_ID');
1181:
1182: -- this is for get custom dates
1183: cursor c_customized (p_loan_id number) is
1184: SELECT nvl(h.custom_payments_flag, 'N')
1185: FROM lns_loan_headers_all h
1186: WHERE loan_id = p_loan_id;
1187:
1188: cursor c_customSchedule(p_loan_id number, p_installment number) is
1189: select payment_number installment_number
5987: ||
5988: || Overview: procedure will run a loan projection ||
5989: || Parameter: loan_id
5990: ||
5991: || Source Tables: LNS_LOAN_HEADERS, LNS_TERMS, LNS_RATE_SCHEDULES,
5992: || LNS_DISB_HEADERS, LNS_DISB_LINES
5993: ||
5994: || Target Tables: None
5995: ||
6072: -- if there is a return count then some dates are invalid
6073: cursor c_invalid_disb(p_loan_id number) is
6074: select count(1)
6075: from lns_disb_headers dh
6076: ,lns_loan_headers h
6077: where h.loan_id = dh.loan_id
6078: and trunc(dh.payment_request_date) < trunc(h.open_loan_start_date)
6079: and h.loan_id = p_loan_id;
6080:
6105: ,x_msg_data => l_msg_data
6106: ,x_return_status => l_return_status
6107: ,p_col_id => p_loan_id
6108: ,p_col_name => 'LOAN_ID'
6109: ,p_table_name => 'LNS_LOAN_HEADERS_ALL');
6110:
6111: if l_return_status <> FND_API.G_RET_STS_SUCCESS then
6112: FND_MESSAGE.SET_NAME('LNS', 'LNS_INVALID_VALUE');
6113: FND_MESSAGE.SET_TOKEN('PARAMETER', 'LOAN_ID');
6291: ,begin_installment_number
6292: ,end_installment_number
6293: ,nvl(interest_only_flag, 'N')
6294: ,nvl(floating_flag, 'N')
6295: from lns_loan_headers_all h,
6296: lns_terms t,
6297: lns_rate_schedules rs
6298: where h.loan_id = p_loan_id
6299: and h.loan_id = t.loan_id
6843: ,nvl(t.prin_payment_frequency, t.loan_payment_frequency)
6844: ,decode(trunc(nvl(t.prin_first_pay_date, t.first_payment_date)) - trunc(h.loan_start_date), 0, 'N', 'Y') -- calculate in advance or arrears for principal
6845: ,nvl(t.PENAL_INT_RATE, 0)
6846: ,nvl(t.PENAL_INT_GRACE_DAYS, 0)
6847: FROM lns_loan_headers_all h
6848: ,lns_terms t
6849: ,fnd_currencies curr
6850: WHERE h.loan_id = p_loan_id
6851: AND t.loan_id = h.loan_id
6876: CURSOR c_reamortization(p_Loan_id NUMBER) IS
6877: SELECT nvl(reamortization_amount, 0)
6878: ,nvl(reamortize_from_installment, 0)
6879: ,nvl(reamortize_to_installment, 0)
6880: FROM lns_loan_headers_all lnh,
6881: lns_amortization_scheds amort1
6882: WHERE lnh.loan_id = amort1.loan_id(+)
6883: AND lnh.loan_id = p_loan_id
6884: AND amort1.reamortization_amount > 0;
6904: from lns_amortization_scheds
6905: where reversed_flag = 'N'
6906: and loan_id = lnh.loan_id
6907: and phase = lnh.current_phase))
6908: from lns_loan_headers lnh
6909: where lnh.loan_id = p_loan_id;
6910:
6911: -- this cursor is to get the last activity date on the loan
6912: -- payoff processing will use this date
7320: || -- the term will change to X MONTHS if TERM was > MONTHS
7321: ||
7322: || Parameter: p_loan_id = loan_id
7323: ||
7324: || Source Tables: LNS_LOAN_HEADERS_ALL, LNS_DISB_HEADERS
7325: || LNS_TERMS
7326: ||
7327: || Target Tables: LNS_LOAN_HEADERS_ALL, LNS_TERMS
7328: ||
7323: ||
7324: || Source Tables: LNS_LOAN_HEADERS_ALL, LNS_DISB_HEADERS
7325: || LNS_TERMS
7326: ||
7327: || Target Tables: LNS_LOAN_HEADERS_ALL, LNS_TERMS
7328: ||
7329: || Return value: Standard Oracle API
7330: ||
7331: || MODIFICATION HISTORY
7378: sign(trunc(loan.LOAN_MATURITY_DATE) - (select trunc(max(PAYMENT_REQUEST_DATE)) from LNS_DISB_HEADERS where LOAN_ID = loan.LOAN_ID))
7379: ELSE
7380: 1
7381: END
7382: from lns_loan_headers loan
7383: where loan.LOAN_ID = p_loan_id;
7384:
7385:
7386: cursor c_loan_info (p_loan_id number) is
7394: ,t.object_VERSION_NUMBER
7395: ,t.term_id
7396: ,h.current_phase
7397: from lns_terms t
7398: ,lns_loan_headers h
7399: where h.loan_id = p_loan_id
7400: and h.loan_id = t.loan_id;
7401:
7402: cursor c_max_pay_req_date(p_loan_id number) is
7476: end if;
7477: l_new_maturity_Date := l_old_maturity_date;
7478:
7479: loop
7480: -- i will be the number of "TERMS" to add the LNS_LOAN_HEADERS.TERM
7481: i := i + 1;
7482: logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - i ' || l_new_maturity_date);
7483: l_new_maturity_date := lns_fin_utils.getNextDate(p_date => l_new_maturity_date
7484: ,p_interval_type => l_new_frequency
7666: ,x_msg_data => l_msg_data
7667: ,x_return_status => l_return_status
7668: ,p_col_id => p_loan_id
7669: ,p_col_name => 'LOAN_ID'
7670: ,p_table_name => 'LNS_LOAN_HEADERS_ALL');
7671:
7672: if l_return_status <> FND_API.G_RET_STS_SUCCESS then
7673: FND_MESSAGE.SET_NAME('LNS', 'LNS_INVALID_VALUE');
7674: FND_MESSAGE.SET_TOKEN('PARAMETER', 'LOAN_ID');
8262: cursor c_loan_info(p_loan_id number) is
8263: select nvl(h.current_phase, 'TERM')
8264: ,t.rate_type
8265: ,t.index_rate_id
8266: from lns_loan_headers h
8267: ,lns_terms t
8268: where h.loan_id = p_loan_id
8269: and t.loan_id = h.loan_id;
8270:
8393: into l_principal_unpaid
8394: ,l_interest_unpaid
8395: ,l_fees_unpaid
8396: from LNS_AM_SCHEDS_V SCHED
8397: ,LNS_LOAN_HEADERS H
8398: where H.loan_id = p_loan_id and
8399: H.loan_id = Sched.loan_id and
8400: SCHED.reversed_code = 'N' and
8401: nvl(sched.phase, 'TERM') = nvl(h.current_phase, 'TERM');
8891:
8892: select rs.current_interest_rate into l_rate
8893: from lns_rate_schedules rs,
8894: lns_terms t,
8895: lns_loan_headers_all h
8896: where h.loan_id = p_loan_id
8897: and h.loan_id = t.loan_id
8898: and rs.term_id = t.term_id
8899: and rs.start_date_active <= sysdate
9123: -- logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - BEGIN');
9124:
9125: Execute Immediate
9126: ' Select Loan_Status ' ||
9127: ' From lns_loan_headers_all ' ||
9128: ' where loan_id = :p_loan_id'
9129: into l_loan_status
9130: using p_loan_id;
9131:
9130: using p_loan_id;
9131:
9132: if l_loan_status = 'APPROVED' then
9133: l_column := 'REQUESTED_AMOUNT';
9134: l_table := 'LNS_LOAN_HEADERS_ALL';
9135:
9136: elsif l_loan_Status = 'ACTIVE' then
9137: l_column := 'TOTAL_PRINCIPAL_BALANCE';
9138: l_table := 'LNS_PAY_SUM_V';
9148: elsif l_loan_Status = 'PAIDOFF' then
9149: l_column := 'TOTAL_PRINCIPAL_BALANCE';
9150: l_table := 'LNS_PAY_SUM_V';
9151: -- l_column := 'REQUESTED_AMOUNT';
9152: -- l_table := 'LNS_LOAN_HEADERS_ALL';
9153:
9154: elsif l_loan_status = 'PENDING' then
9155: l_column := 'REQUESTED_AMOUNT';
9156: l_table := 'LNS_LOAN_HEADERS_ALL';
9152: -- l_table := 'LNS_LOAN_HEADERS_ALL';
9153:
9154: elsif l_loan_status = 'PENDING' then
9155: l_column := 'REQUESTED_AMOUNT';
9156: l_table := 'LNS_LOAN_HEADERS_ALL';
9157:
9158: elsif l_loan_status = 'INCOMPLETE' then
9159: l_column := 'REQUESTED_AMOUNT';
9160: l_table := 'LNS_LOAN_HEADERS_ALL';
9156: l_table := 'LNS_LOAN_HEADERS_ALL';
9157:
9158: elsif l_loan_status = 'INCOMPLETE' then
9159: l_column := 'REQUESTED_AMOUNT';
9160: l_table := 'LNS_LOAN_HEADERS_ALL';
9161:
9162: elsif l_loan_status = 'IN_FUNDING' then
9163: l_column := 'REQUESTED_AMOUNT';
9164: l_table := 'LNS_LOAN_HEADERS_ALL';
9160: l_table := 'LNS_LOAN_HEADERS_ALL';
9161:
9162: elsif l_loan_status = 'IN_FUNDING' then
9163: l_column := 'REQUESTED_AMOUNT';
9164: l_table := 'LNS_LOAN_HEADERS_ALL';
9165:
9166: elsif l_loan_status = 'FUNDING_ERROR' then
9167: l_column := 'REQUESTED_AMOUNT';
9168: l_table := 'LNS_LOAN_HEADERS_ALL';
9164: l_table := 'LNS_LOAN_HEADERS_ALL';
9165:
9166: elsif l_loan_status = 'FUNDING_ERROR' then
9167: l_column := 'REQUESTED_AMOUNT';
9168: l_table := 'LNS_LOAN_HEADERS_ALL';
9169:
9170: else -- catch any new statuses
9171: l_column := 'REQUESTED_AMOUNT';
9172: l_table := 'LNS_LOAN_HEADERS_ALL';
9168: l_table := 'LNS_LOAN_HEADERS_ALL';
9169:
9170: else -- catch any new statuses
9171: l_column := 'REQUESTED_AMOUNT';
9172: l_table := 'LNS_LOAN_HEADERS_ALL';
9173:
9174: end if;
9175:
9176: Execute Immediate
9425:
9426: cursor c_loan_boundaries(p_loan_id number)
9427: is
9428: select open_loan_start_date
9429: from lns_loan_headers
9430: where loan_id = p_loan_id;
9431:
9432: begin
9433:
9561: || p_calc_method for future use
9562: ||
9563: || Return value: average daily balance for the loan
9564: ||
9565: || Source Tables: LNS_RECEIVABLE_ACTIVITIES_V, LNS_LOAN_HEADERS
9566: ||
9567: || Target Tables: NA
9568: ||
9569: || KNOWN ISSUES
9589: cursor c_balance_history(p_loan_id number) is
9590: select trunc(loan_start_date) activity_date,
9591: funded_amount activity_amount,
9592: funded_amount ending_balance
9593: from lns_loan_headers
9594: where loan_id = p_loan_id
9595: union all
9596: select trunc(activity_date) activity_date,
9597: sum(activity_amount) activity_amount,
9604: union all
9605: select trunc(loan_maturity_date) activity_date
9606: ,null
9607: ,lns_financials.getRemainingBalance(p_loan_id)
9608: from lns_loan_headers
9609: where loan_id = p_loan_id
9610: order by activity_date asc;
9611:
9612: -- this cursor will get the current phase of the loan
9611:
9612: -- this cursor will get the current phase of the loan
9613: cursor c_loan_phase(p_loan_id number) is
9614: select nvl(current_phase,'TERM') current_phase
9615: from lns_loan_headers
9616: where loan_id = p_loan_id;
9617:
9618: l_activity_date date;
9619: l_activity_amount number;