[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
|| 06/16/2006 11:35AM karamach Added code to check and update only the existing rate sch row
|| when begin_installment_number = end_installment_number = p_installment_number for the rate schedule row being processed
|| as part of the fix for bug5331888
||
*=======================================================================*/
procedure floatingRatePostProcessing(p_loan_id IN NUMBER
,p_init_msg_list IN VARCHAR2
,p_commit IN VARCHAR2
,p_installment_number IN NUMBER
,p_period_begin_date IN DATE
,p_interest_adjustment_freq IN VARCHAR2
,p_annualized_interest_rate IN NUMBER
,p_rate_id IN OUT NOCOPY NUMBER
,p_phase IN VARCHAR2
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2)
is
l_next_rate_change date;
select begin_installment_number, end_installment_number
from lns_rate_schedules where rate_id = pRateId;
update lns_terms
set next_rate_change_date = l_next_rate_change
,open_projected_rate = p_annualized_interest_rate
,last_update_date = sysdate
,last_updated_by = lns_utility_pub.user_id
where loan_id = p_loan_id;
update lns_terms
set next_rate_change_date = l_next_rate_change
,term_projected_rate = p_annualized_interest_rate
,last_update_date = sysdate
,last_updated_by = lns_utility_pub.user_id
where loan_id = p_loan_id;
logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' (l_begin_inst_num = l_end_inst_num = p_installment_number) - NO need to insert new row - updating the existing rate schedule ' || p_rate_id);
update lns_rate_schedules
set current_interest_rate = p_annualized_interest_rate
,index_rate = p_annualized_interest_rate - nvl(spread,0)
where rate_id = p_rate_id;
logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' update the existing row as the next row and insert new row for the current rate sch');
update lns_rate_schedules
set begin_installment_number = begin_installment_number + 1
,current_interest_rate = spread
,index_rate = null
where rate_id = p_rate_id;
select LNS_RATE_SCHEDULES_S.NEXTVAL into l_new_rate_id
from dual;
insert into lns_rate_schedules(RATE_ID
,TERM_ID
,INDEX_RATE
,SPREAD
,CURRENT_INTEREST_RATE
,START_DATE_ACTIVE
,END_DATE_ACTIVE
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATE_LOGIN
,OBJECT_VERSION_NUMBER
,INDEX_DATE
,BEGIN_INSTALLMENT_NUMBER
,END_INSTALLMENT_NUMBER
,INTEREST_ONLY_FLAG
,FLOATING_FLAG
,PHASE)
(select
l_new_rate_id
,TERM_ID
,p_annualized_interest_rate - nvl(spread,0)
,SPREAD
,p_annualized_interest_rate --make sure you only insert spread overtop of CIR
,START_DATE_ACTIVE
,END_DATE_ACTIVE
,CREATED_BY
,sysdate
,LAST_UPDATED_BY
,sysdate
,LAST_UPDATE_LOGIN
,1
,INDEX_DATE
,p_installment_number
,p_installment_number
,INTEREST_ONLY_FLAG
,FLOATING_FLAG
,PHASE
from lns_rate_schedules
where rate_id = p_rate_id);
update lns_rate_schedules
set current_interest_rate = p_annualized_interest_rate
,index_rate = p_annualized_interest_rate - nvl(spread,0)
where rate_id = p_rate_id;
SELECT TERM_ID
FROM LNS_TERMS
WHERE LOAN_ID = p_Loan_id;
select decode(loan.loan_class_code,
'DIRECT', (select count(1) from lns_disb_headers where loan_id = p_loan_id and status is null and PAYMENT_REQUEST_DATE is not null),
'ERS', (select count(1) from lns_loan_lines where loan_id = p_loan_id and (status is null or status = 'PENDING') and end_date is null))
from lns_loan_headers_all loan
where loan.loan_id = p_loan_id;
l_amort_tbl.delete;
if l_loan_details.LOAN_STATUS NOT IN ('INCOMPLETE','DELETED','REJECTED','PENDING') and
p_based_on_terms <> 'CURRENT' --and l_loan_details.LOAN_PHASE = 'TERM'
then
logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Calling LOAD_ORIGINAL_SCHEDULE...');
l_fees_tbl.delete;
l_loan_details.LOAN_STATUS NOT IN ('INCOMPLETE','DELETED','REJECTED','PENDING','APPROVED')
then
l_num_records := l_amort_tbl.count;
l_amort_tbl.delete;
SELECT nvl(h.custom_payments_flag, 'N')
FROM lns_loan_headers_all h
WHERE loan_id = p_loan_id;
select payment_number installment_number
,due_date due_date
from lns_custom_paymnt_scheds
where loan_id = p_loan_id
and payment_number = p_installment;
delete
from lns_amortization_scheds
where loan_id = p_loan_id
and reamortization_amount is not null
and reamortize_from_installment is not null;
logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' insert record into amortizations');
insert into LNS_AMORTIZATION_SCHEDS(amortization_schedule_id
,loan_id
,reamortization_amount
,reamortize_from_installment
,reamortize_to_installment
,created_by
,creation_date
,last_updated_by
,last_update_date
,object_version_number)
values
(LNS_AMORTIZATION_SCHEDS_S.NEXTVAL
,p_loan_id
,l_actual_balance
,p_installment_number
,null
,lns_utility_pub.created_by
,lns_utility_pub.creation_date
,lns_utility_pub.last_updated_by
,lns_utility_pub.last_update_date
,1);
logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' insert record into amortizations');
insert into LNS_AMORTIZATION_SCHEDS(amortization_schedule_id
,loan_id
,reamortization_amount
,reamortize_from_installment
,reamortize_to_installment
,created_by
,creation_date
,last_updated_by
,last_update_date
,object_version_number)
values
(LNS_AMORTIZATION_SCHEDS_S.NEXTVAL
,p_loan_id
,l_actual_balance
,p_installment_number
,null
,lns_utility_pub.created_by
,lns_utility_pub.creation_date
,lns_utility_pub.last_updated_by
,lns_utility_pub.last_update_date
,1);
select
cust.DUE_DATE
,nvl(cust.PRINCIPAL_AMOUNT, 0)
,nvl(cust.INTEREST_AMOUNT, 0)
,cust.installment_begin_balance
,cust.installment_end_balance
--cust.FEE_AMOUNT
from LNS_CUSTOM_PAYMNT_SCHEDS cust
where cust.LOAN_ID = P_LOAN_ID and
cust.PAYMENT_NUMBER = P_PAYMENT_NUMBER;
l_fee_structures.delete;
l_fees_tbl.delete;
select interest_rate
from lns_int_rate_lines
where start_date_active <= p_rate_date
and end_date_active >= p_rate_date
and interest_rate_id = p_rate_id;
select spread
,rate_id
from lns_rate_schedules rs
,lns_terms t
where t.loan_id = p_loan_id
and t.term_id = rs.term_id
and phase = 'OPEN';
select ACTIVITY_DATE
from LNS_PRIN_TRX_ACTIVITIES_V
where loan_id = p_loan_id
and PAYMENT_NUMBER = p_installment_number
and PARENT_AMORTIZATION_ID is null
and ACTIVITY_CODE in ('BILLING', 'START');
select PRINCIPAL_AMOUNT, PAYMENT_NUMBER
from lns_amortization_scheds
where loan_id = p_loan_id
and PAYMENT_NUMBER > 0
and PAYMENT_NUMBER <= p_installment_number
and (REVERSED_FLAG is null or REVERSED_FLAG = 'N')
and PARENT_AMORTIZATION_ID is null
and REAMORTIZE_TO_INSTALLMENT is null
and PRINCIPAL_AMOUNT > 0
and nvl(PHASE, 'TERM') = 'TERM'
order by PAYMENT_NUMBER desc;
select FUNDED_AMOUNT, nvl(CAP_INT_AMOUNT, 0)
from LNS_AMORTIZATION_SCHEDS
where loan_id = p_loan_id
and PAYMENT_NUMBER = p_installment_number
and (REVERSED_FLAG is null or REVERSED_FLAG = 'N')
and PARENT_AMORTIZATION_ID is null
and nvl(PHASE, 'TERM') = 'TERM';
select decode(loan.loan_class_code,
'DIRECT', (select count(1) from lns_disb_headers where loan_id = p_loan_id and status is null and PAYMENT_REQUEST_DATE is not null),
'ERS', (select count(1) from lns_loan_lines where loan_id = p_loan_id and (status is null or status = 'PENDING') and end_date is null))
from lns_loan_headers_all loan
where loan.loan_id = p_loan_id;
select nvl(BILL_ON_APPR_AMOUNT_FLAG, 'N')
from lns_loan_headers_all
where loan_id = p_loan_id;
l_fees_tbl.delete;
l_fees_tbl.delete;
select ACTIVITY_DATE
from LNS_PRIN_TRX_ACTIVITIES_V
where loan_id = p_loan_id
and PAYMENT_NUMBER = p_installment_number
and PARENT_AMORTIZATION_ID is null
and ACTIVITY_CODE in ('BILLING', 'START');
select PRINCIPAL_AMOUNT, FUNDED_AMOUNT, nvl(CAP_INT_AMOUNT, 0)
from lns_amortization_scheds
where loan_id = p_loan_id
and PAYMENT_NUMBER > 0
and PAYMENT_NUMBER <= p_installment_number
and (REVERSED_FLAG is null or REVERSED_FLAG = 'N')
and PARENT_AMORTIZATION_ID is null
and REAMORTIZE_TO_INSTALLMENT is null
--and PRINCIPAL_AMOUNT > 0
and nvl(PHASE, 'TERM') = 'TERM'
order by PAYMENT_NUMBER desc;
select decode(loan.loan_class_code,
'DIRECT', (select count(1) from lns_disb_headers where loan_id = p_loan_id and status is null and PAYMENT_REQUEST_DATE is not null),
'ERS', (select count(1) from lns_loan_lines where loan_id = p_loan_id and (status is null or status = 'PENDING') and end_date is null))
from lns_loan_headers_all loan
where loan.loan_id = p_loan_id;
select nvl(BILL_ON_APPR_AMOUNT_FLAG, 'N')
from lns_loan_headers_all
where loan_id = p_loan_id;
l_fees_tbl.delete;
l_fees_tbl.delete;
select ACTIVITY_DATE
from LNS_PRIN_TRX_ACTIVITIES_V
where loan_id = p_loan_id
and PAYMENT_NUMBER = p_installment_number
and PARENT_AMORTIZATION_ID is null
and ACTIVITY_CODE in ('BILLING', 'START');
select (PRINCIPAL_AMOUNT + INTEREST_AMOUNT + nvl(CURR_CAP_INT_AMOUNT, 0)),
nvl(FUNDED_AMOUNT, 0),
nvl(CAP_INT_AMOUNT, 0),
amortization_schedule_id
from lns_amortization_scheds
where loan_id = p_loan_id
and PAYMENT_NUMBER > 0
and PAYMENT_NUMBER = p_installment_number
and (REVERSED_FLAG is null or REVERSED_FLAG = 'N')
and PARENT_AMORTIZATION_ID is null
and REAMORTIZE_TO_INSTALLMENT is null
--and PRINCIPAL_AMOUNT > 0
and nvl(PHASE, 'TERM') = 'TERM';
select min(PAYMENT_NUMBER)
from LNS_AMORTIZATION_SCHEDS
where loan_id = p_loan_id
and PAYMENT_NUMBER > 0
and (REVERSED_FLAG is null or REVERSED_FLAG = 'N')
and PARENT_AMORTIZATION_ID is null
and nvl(PHASE, 'TERM') = 'TERM';
select decode(loan.loan_class_code,
'DIRECT', (select count(1) from lns_disb_headers where loan_id = p_loan_id and status is null and PAYMENT_REQUEST_DATE is not null),
'ERS', (select count(1) from lns_loan_lines where loan_id = p_loan_id and (status is null or status = 'PENDING') and end_date is null))
from lns_loan_headers_all loan
where loan.loan_id = p_loan_id;
select nvl(sum(am_lines.amount), 0)
from lns_amortization_lines am_lines,
RA_CUSTOMER_TRX_LINES_ALL trx_lines
where am_lines.loan_id = p_loan_id
and am_lines.amortization_schedule_id = p_amortization_schedule_id
and am_lines.line_type = 'INT'
and am_lines.cust_trx_line_id = trx_lines.CUSTOMER_TRX_LINE_ID
and trx_lines.description not like '%Normal Interest%';
select nvl(BILL_ON_APPR_AMOUNT_FLAG, 'N')
from lns_loan_headers_all
where loan_id = p_loan_id;
l_fees_tbl.delete;
l_fees_tbl.delete;
select nvl(sum(header_amount), 0)
from lns_disb_headers
where loan_id = p_loan_id
and trunc(payment_request_date) >= p_from_date
and trunc(payment_request_date) < p_to_date;
l_fees_tbl.delete;
l_fees_tbl.delete;
select nvl(sum(header_amount), 0)
from lns_disb_headers
where loan_id = p_loan_id;
-- delete predicted records based on ORIGINAL amortization
if p_based_on_terms = 'CURRENT' and
l_loan_details.LOAN_STATUS NOT IN ('INCOMPLETE','DELETED','REJECTED','PENDING','APPROVED')
then
l_num_records := l_amort_tbl.count;
l_amort_tbl.delete;
select rate_id
,current_interest_rate
,nvl(spread, 0)
,trunc(start_date_active)
,trunc(end_date_active)
,begin_installment_number
,end_installment_number
,nvl(interest_only_flag, 'N')
,nvl(floating_flag, 'N')
from lns_loan_headers_all h,
lns_terms t,
lns_rate_schedules rs
where h.loan_id = p_loan_id
and h.loan_id = t.loan_id
and t.term_id = rs.term_id
and rs.end_date_active is null
and nvl(phase, 'TERM') = p_phase
order by begin_installment_number
,start_date_active;
SELECT h.loan_id
,decode(p_phase, 'TERM', h.loan_term, 'OPEN', h.open_loan_term, h.loan_term) TERM
,decode(p_phase, 'TERM', h.loan_term_period, 'OPEN', h.open_loan_term_period, h.loan_term_period) TERM_PERIOD
,decode(p_phase, 'TERM', decode(h.balloon_payment_type, 'TERM', h.amortized_term, 'AMOUNT', h.loan_term, h.amortized_term), 'OPEN', h.open_loan_term) AMORT_TERM
,decode(p_phase, 'TERM', decode(h.balloon_payment_type, 'TERM', h.amortized_term_period, 'AMOUNT', h.loan_term_period, h.amortized_term_period), 'OPEN', h.open_loan_term_period) AMORT_TERM_PERIOD
,decode(h.balloon_payment_type, 'TERM', 0, 'AMOUNT', h.balloon_payment_amount, 0) BALLOON_PAYMENT_AMT
,decode(p_phase, 'TERM', t.amortization_frequency, 'OPEN', t.loan_payment_frequency) AMORT_FREQ
,decode(p_phase, 'TERM', t.loan_payment_frequency, 'OPEN', t.open_payment_frequency, t.loan_payment_frequency) PAY_FREQ
,decode(p_phase, 'TERM', trunc(h.loan_start_date), 'OPEN' , trunc(h.open_loan_start_date), trunc(h.loan_start_date)) START_DATE
,decode(p_phase, 'TERM', trunc(t.first_payment_date), 'OPEN' , trunc(t.open_first_payment_date), trunc(t.first_payment_date)) FIRST_PAY_DATE
,h.requested_amount REQUEST_AMOUNT
,h.funded_amount FUNDED_AMOUNT
,lns_financials.getRemainingBalance(p_loan_id) BALANCE
--,decode(p_based_on_terms, 'CURRENT', lns_financials.getRemainingBalance(p_loan_id), 'ORIGINAL', h.requested_amount) BALANCE -- see bug #3881401
,decode(p_phase, 'TERM', trunc(h.loan_maturity_date), 'OPEN', trunc(h.open_maturity_date), trunc(h.loan_maturity_date)) MATURITY_DATE
,NVL(t.reamortize_over_payment, 'N')
,NVL(t.reamortize_under_payment, 'N')
,NVL(t.reamortize_with_interest, 'N')
,LNS_BILLING_UTIL_PUB.LAST_PAYMENT_NUMBER(p_loan_id, p_phase) LAST_PAY_NUM
--,decode(p_based_on_terms, 'CURRENT', LNS_BILLING_UTIL_PUB.LAST_PAYMENT_NUMBER(p_loan_id),'ORIGINAL', 1) LAST_PAY_NUM
,decode(nvl(t.day_count_method, 'PERIODIC30_360'), 'PERIODIC30_360', '30/360', t.day_count_method) DAY_COUNT
,decode(p_phase, 'TERM', decode(trunc(t.first_payment_date) - trunc(h.loan_start_date), 0, 'N', 'Y')
, 'OPEN', decode(trunc(t.open_first_payment_date) - trunc(h.open_loan_start_date), 0, 'N', 'Y')
, decode(trunc(t.first_payment_date) - trunc(h.loan_start_date), 0, 'N', 'Y')) ARREARS
,nvl(h.custom_payments_flag, 'N') CUSTOM
,h.loan_status LOAN_STATUS
,h.loan_currency CURRENCY
,curr.precision PRECISION
,h.OPEN_TO_TERM_FLAG OPEN_TO_TERM_FLAG
,h.OPEN_TO_TERM_EVENT OPEN_TO_TERM_EVENT
,h.MULTIPLE_FUNDING_FLAG MULTIPLE_FUNDING_FLAG
,h.SECONDARY_STATUS SECONDARY_STATUS
,t.RATE_TYPE RATE_TYPE
,t.CEILING_RATE TERM_CEILING_RATE
,t.FLOOR_RATE TERM_FLOOR_RATE
,t.PERCENT_INCREASE TERM_PERCENT_INCREASE
,t.PERCENT_INCREASE_LIFE TERM_PERCENT_INCREASE_LIFE
,t.FIRST_PERCENT_INCREASE TERM_FIRST_PERCENT_INCREASE
,t.OPEN_PERCENT_INCREASE OPEN_PERCENT_INCREASE
,t.OPEN_PERCENT_INCREASE_LIFE OPEN_PERCENT_INCREASE_LIFE
,t.OPEN_FIRST_PERCENT_INCREASE OPEN_FIRST_PERCENT_INCREASE
,t.OPEN_CEILING_RATE OPEN_CEILING_RATE
,t.OPEN_FLOOR_RATE OPEN_FLOOR_RATE
,t.OPEN_PROJECTED_RATE OPEN_PROJECTED_RATE
,t.TERM_PROJECTED_RATE TERM_PROJECTED_RATE
,t.rate_change_frequency TERM_RATE_CHG_FREQ
,t.rate_change_frequency OPEN_RATE_CHG_FREQ
,t.INDEX_RATE_ID OPEN_INDEX_RATE_ID
,t.INDEX_RATE_ID TERM_INDEX_RATE_ID
,t.OPEN_INDEX_DATE OPEN_INDEX_DATE
,t.TERM_INDEX_DATE TERM_INDEX_DATE
,decode(p_phase, 'TERM', t.TERM_PROJECTED_RATE, t.OPEN_PROJECTED_RATE) INITIAL_INTEREST_RATE
,nvl(lns_fin_utils.getActiveRate(h.loan_id), decode(p_phase, 'TERM', t.TERM_PROJECTED_RATE, t.OPEN_PROJECTED_RATE)) LAST_INTEREST_RATE
,nvl(t.FIRST_RATE_CHANGE_DATE, t.NEXT_RATE_CHANGE_DATE) FIRST_RATE_CHANGE_DATE
,t.NEXT_RATE_CHANGE_DATE NEXT_RATE_CHANGE_DATE
,t.CALCULATION_METHOD
,t.INTEREST_COMPOUNDING_FREQ
,decode(p_phase, 'TERM', decode(p_based_on_terms,
'CURRENT', decode(nvl(h.custom_payments_flag, 'N'), 'Y', nvl(t.PAYMENT_CALC_METHOD, 'CUSTOM'),
'N', nvl(t.PAYMENT_CALC_METHOD, 'EQUAL_PAYMENT')),
decode(nvl(h.custom_payments_flag, 'N'), 'Y', nvl(t.ORIG_PAY_CALC_METHOD, 'CUSTOM'),
'N', nvl(t.PAYMENT_CALC_METHOD, 'EQUAL_PAYMENT'))
), null)
,t.ORIG_PAY_CALC_METHOD
,decode(p_phase, 'TERM', trunc(nvl(t.prin_first_pay_date, t.first_payment_date)), 'OPEN', null, trunc(nvl(t.prin_first_pay_date, t.first_payment_date)))
,nvl(t.prin_payment_frequency, t.loan_payment_frequency)
,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
,nvl(t.PENAL_INT_RATE, 0)
,nvl(t.PENAL_INT_GRACE_DAYS, 0)
,nvl(h.CURRENT_PHASE, 'TERM')
,nvl(t.REAMORTIZE_ON_FUNDING, 'REST')
,nvl(h.ADD_REQUESTED_AMOUNT, 0)
,nvl(t.CALC_ADD_INT_UNPAID_PRIN, 'N')
,nvl(t.CALC_ADD_INT_UNPAID_INT, 'N')
,nvl(t.CAPITALIZE_INT, 'N')
,nvl(t.FLUCTUATE_EQ_PAY_AMOUNT, 'N')
FROM lns_loan_headers_all h
,lns_terms t
,fnd_currencies curr
WHERE h.loan_id = p_loan_id
AND t.loan_id = h.loan_id
AND curr.currency_code = h.loan_currency;
SELECT nvl(reamortization_amount, 0)
,nvl(reamortize_from_installment, 0)
,nvl(reamortize_to_installment, 0)
FROM lns_loan_headers_all lnh,
lns_amortization_scheds amort1
WHERE lnh.loan_id = amort1.loan_id(+)
AND lnh.loan_id = p_loan_id
AND amort1.reamortization_amount > 0;
select nvl(sum(amort.PRINCIPAL_AMOUNT),0) -- billed principal
,nvl(sum(amort.PRINCIPAL_REMAINING),0) -- unpaid principal
,nvl(sum(amort.INTEREST_REMAINING),0) -- unpaid interest
from LNS_AM_SCHEDS_V amort
where amort.Loan_id = p_loan_id
and amort.REVERSED_CODE = 'N'
and amort.phase = p_phase;
select decode(LNS_BILLING_UTIL_PUB.LAST_PAYMENT_NUMBER(lnh.loan_id),
0,
decode(lnh.current_phase, 'TERM', lnh.loan_start_date, 'OPEN', lnh.open_loan_start_date),
(select max(due_date)
from lns_amortization_scheds
where reversed_flag = 'N'
and loan_id = lnh.loan_id
and phase = lnh.current_phase))
from lns_loan_headers lnh
where lnh.loan_id = p_loan_id;
select max(activity_date)
from LNS_REC_ACT_CASH_CM_V
where activity_code = 'PMT'
and loan_id = p_loan_id;
select LAST_BILLED_INSTALLMENT
from LNS_LOAN_EXTENSIONS
where loan_id = p_loan_id
and STATUS = 'APPROVED'
order by LOAN_EXT_ID desc;
select OLD_TERM,
OLD_TERM_PERIOD,
OLD_BALLOON_TYPE,
OLD_BALLOON_AMOUNT,
OLD_AMORT_TERM,
OLD_MATURITY_DATE,
OLD_INSTALLMENTS
from LNS_LOAN_EXTENSIONS
where loan_id = p_loan_id
and STATUS = 'APPROVED'
order by LOAN_EXT_ID;
|| 3. update the term/term period - in terms of payment frequency,
|| -- the term will change to X MONTHS if TERM was > MONTHS
||
|| Parameter: p_loan_id = loan_id
||
|| Source Tables: LNS_LOAN_HEADERS_ALL, LNS_DISB_HEADERS
|| LNS_TERMS
||
|| Target Tables: LNS_LOAN_HEADERS_ALL, LNS_TERMS
||
|| Return value: Standard Oracle API
||
|| MODIFICATION HISTORY
|| Date Author Description of Changes
|| 02/14/2005 11:35AM raverma Created
*=======================================================================*/
procedure shiftLoan(p_loan_id in number
,p_init_msg_list IN VARCHAR2
,p_commit IN VARCHAR2
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2)
is
l_api_name varchar2(25);
select
CASE
WHEN (nvl(loan.CURRENT_PHASE, 'TERM') = 'OPEN') THEN
sign(trunc(loan.OPEN_MATURITY_DATE) - (select trunc(max(PAYMENT_REQUEST_DATE)) from LNS_DISB_HEADERS where LOAN_ID = loan.LOAN_ID))
WHEN (nvl(loan.CURRENT_PHASE, 'TERM') = 'TERM' and loan.MULTIPLE_FUNDING_FLAG = 'N') THEN
sign(trunc(loan.LOAN_MATURITY_DATE) - (select trunc(max(PAYMENT_REQUEST_DATE)) from LNS_DISB_HEADERS where LOAN_ID = loan.LOAN_ID))
ELSE
1
END
from lns_loan_headers loan
where loan.LOAN_ID = p_loan_id;
select t.open_payment_frequency
,decode(h.current_phase, 'OPEN', h.open_maturity_date, h.loan_maturity_date)
,nvl(h.custom_payments_flag, 'N')
,h.open_to_term_flag
,decode(h.current_phase, 'OPEN', h.open_loan_term, h.loan_term)
,decode(h.current_phase, 'OPEN', h.open_loan_term_period, h.loan_term_period)
,h.OBJECT_VERSION_NUMBER
,t.object_VERSION_NUMBER
,t.term_id
,h.current_phase
from lns_terms t
,lns_loan_headers h
where h.loan_id = p_loan_id
and h.loan_id = t.loan_id;
select max(payment_request_date)
from lns_disb_headers
where loan_id = p_loan_id;
lns_terms_pub.update_term(p_object_version_number => l_terms_version_number
,p_init_msg_list => fnd_api.g_false
,p_loan_term_rec => l_term_rec
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data);
lns_loan_header_pub.update_loan(p_object_version_number => l_version_number
,p_loan_header_rec => l_loan_header_rec
,p_init_msg_list => fnd_api.g_false
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data);
lns_loan_header_pub.update_loan(p_object_version_number => l_version_number
,p_loan_header_rec => l_loan_header_rec
,p_init_msg_list => fnd_api.g_false
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data);
elsif p_loan_details.loan_status IN ('INCOMPLETE','DELETED','REJECTED','PENDING') then
FND_MESSAGE.Set_Name('LNS', 'LNS_INVOICE_SUMMARY_ERROR');
select nvl(sum(sched.fee_amount), 0)
from lns_fee_schedules sched
,lns_fees fees
where sched.loan_id = p_loan_id
and sched.fee_id = fees.fee_id
and sched.active_flag = 'Y'
and sched.billed_flag = 'N'
and sched.phase = p_phase
and sched.fee_installment <= decode(p_installment, -1, 1, (p_installment+1))
and fees.fee_category <> 'RECUR';
select nvl(h.current_phase, 'TERM')
from lns_loan_headers h
where h.loan_id = p_loan_id;
select ACTIVITY_DATE
from LNS_PRIN_TRX_ACTIVITIES_V
where loan_id = p_loan_id
and PAYMENT_NUMBER = p_installment_number
and PARENT_AMORTIZATION_ID is null
and ACTIVITY_CODE in ('BILLING', 'START');
select nvl(DEFERRED_INT_AMOUNT, 0), nvl(CAP_INT_AMOUNT, 0)
from LNS_AMORTIZATION_SCHEDS
where loan_id = p_loan_id
and PAYMENT_NUMBER = p_installment_number
and (REVERSED_FLAG is null or REVERSED_FLAG = 'N')
and PARENT_AMORTIZATION_ID is null
and nvl(PHASE, 'TERM') = 'TERM';
select
nvl(sum(SCHED.PRINCIPAL_REMAINING),0)
,nvl(sum(SCHED.INTEREST_REMAINING),0)
,nvl(sum(SCHED.FEE_REMAINING),0)
into l_principal_unpaid
,l_interest_unpaid
,l_fees_unpaid
from LNS_AM_SCHEDS_V SCHED
,LNS_LOAN_HEADERS H
where H.loan_id = p_loan_id and
H.loan_id = Sched.loan_id and
SCHED.reversed_code = 'N' and
nvl(sched.phase, 'TERM') = nvl(h.current_phase, 'TERM');
select to_number(to_char(p_period_start_date, 'YYYY')) into l_year1 from dual;
select to_number(to_char(p_period_end_date, 'YYYY')) into l_year2 from dual;
select to_number(to_char(p_period_start_date, 'YYYY')) into l_year1 from dual;
select to_number(to_char(p_period_end_date, 'YYYY')) into l_year2 from dual;
select rs.current_interest_rate into l_rate
from lns_rate_schedules rs,
lns_terms t,
lns_loan_headers_all h
where h.loan_id = p_loan_id
and h.loan_id = t.loan_id
and rs.term_id = t.term_id
and rs.start_date_active <= sysdate
and rs.end_date_active >= sysdate;
' Select Loan_Status ' ||
' From lns_loan_headers_all ' ||
' where loan_id = :p_loan_id'
into l_loan_status
using p_loan_id;
' Select ' || l_column ||
' From ' || l_table ||
' where loan_id = :p_loan_id'
into l_balance
using p_loan_id;
select
decode(loan.loan_class_code,
'DIRECT', (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.phase = 'TERM' and */
disb_hdr.disb_header_id = disb_line.disb_header_id and disb_line.STATUS = 'FULLY_FUNDED' and
trunc(disb_line.DISBURSEMENT_DATE) <= trunc(p_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 lines.end_date is null and
trunc(lines.ADJUSTMENT_DATE) <= trunc(p_date)))
from lns_loan_headers_all loan
where loan.loan_id = p_loan_id;
select
decode(loan.loan_class_code,
'DIRECT', (select nvl(sum(disb_hdr.HEADER_AMOUNT), 0) from lns_disb_headers disb_hdr
where disb_hdr.loan_id = loan.loan_id and
disb_hdr.STATUS is null and
(disb_hdr.PAYMENT_REQUEST_DATE is null or trunc(disb_hdr.PAYMENT_REQUEST_DATE) <= trunc(p_date))),
'ERS', (select nvl(sum(lines.REQUESTED_AMOUNT), 0) from lns_loan_lines lines
where lines.loan_id = loan.loan_id and (lines.STATUS is null or lines.STATUS = 'PENDING') and
lines.end_date is null))
from lns_loan_headers_all loan
where loan.loan_id = p_loan_id;
select
decode(loan.loan_class_code,
'DIRECT', loan.requested_amount + nvl(loan.ADD_REQUESTED_AMOUNT, 0) /* +
(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(p_date))*/,
'ERS', (select nvl(sum(lines.REQUESTED_AMOUNT), 0) from lns_loan_lines lines
where lines.loan_id = loan.loan_id and (lines.STATUS is null or lines.STATUS = 'PENDING') and
lines.end_date is null))
from lns_loan_headers_all loan
where loan.loan_id = p_loan_id;
select nvl(BILL_ON_APPR_AMOUNT_FLAG, 'N')
from lns_loan_headers_all
where loan_id = p_loan_id;
select * from
(select p_from_date activity_date
,nvl(sum(line_amount), 0)
,1 display_order
from lns_disb_lines lines
where disb_header_id in (select disb_header_id from lns_disb_headers where loan_id = p_loan_id)
and trunc(disbursement_date) <= p_from_date
UNION
select trunc(line.disbursement_date) activity_date
,nvl(sum(inv.amount), 0)
,2 display_order
from AP_INVOICE_PAYMENTS_ALL inv
,lns_disb_headers head
,lns_disb_lines line
where head.loan_id = p_loan_id
and line.disb_header_id = head.disb_header_id
and line.invoice_id is not null
and line.invoice_id = inv.invoice_id
and line.status IN ('PARTIALLY_FUNDED', 'FULLY_FUNDED')
and trunc(line.disbursement_date) > p_from_date
and trunc(line.disbursement_date) < p_to_date
group by trunc(line.disbursement_date))
order by display_order, activity_date;
select * from
(select p_from_date activity_date
,nvl(sum(header_amount),0)
,1 display_order
from lns_disb_headers
where loan_id = p_loan_id
and trunc(payment_request_date) <= p_from_date
UNION
select payment_request_date activity_date
,nvl(sum(header_amount),0)
,2 display_order
from lns_disb_headers
where loan_id = p_loan_id
and trunc(payment_request_date) > p_from_date
and trunc(payment_request_date) < p_to_date
group by payment_request_date
UNION
select lines.*
from
(select p_from_date activity_date
,nvl(sum(REQUESTED_AMOUNT),0)
,3 display_order
from lns_loan_lines
where loan_id = p_loan_id
and (status is null or status = 'PENDING')
and end_date is null) lines,
lns_loan_headers_all loan
where loan.loan_id = p_loan_id
and loan.LOAN_CLASS_CODE = 'ERS')
order by display_order, activity_date;
select open_loan_start_date
from lns_loan_headers
where loan_id = p_loan_id;
select curr.precision
from lns_loan_headers_all h
,fnd_currencies curr
where h.loan_id = p_loan_id
and curr.currency_code = h.loan_currency;
select trunc(loan_start_date) activity_date,
funded_amount activity_amount,
funded_amount ending_balance
from lns_loan_headers
where loan_id = p_loan_id
union all
select trunc(activity_date) activity_date,
sum(activity_amount) activity_amount,
LNS_BILLING_UTIL_PUB.LOAN_BALANCE_BY_DATE(P_LOAN_ID, activity_date) --min(balance_by_activity_date) ending_balance
from LNS_REC_ACT_CASH_CM_V rav
where rav.loan_id = p_loan_id and
line_type_code = 'PRIN' and
(activity_code in ('PMT', 'ADJ') or (activity_code = 'CM' and activity_number like 'NET%'))
group by activity_date
union all
select trunc(loan_maturity_date) activity_date
,null
,lns_financials.getRemainingBalance(p_loan_id)
from lns_loan_headers
where loan_id = p_loan_id
order by activity_date asc;
select nvl(current_phase,'TERM') current_phase
from lns_loan_headers
where loan_id = p_loan_id;
select
trunc(ACTIVITY_DATE),
ACTIVITY_CODE,
ACTIVITY_AMOUNT,
THEORETICAL_BALANCE,
ACTUAL_BALANCE,
DAYS_LATE,
display_order
from LNS_PRIN_TRX_ACTIVITIES_V
where loan_id = p_loan_id and
trunc(ACTIVITY_DATE) >= trunc(p_start_date) and
trunc(ACTIVITY_DATE) <= trunc(p_end_date) and
ACTIVITY_CODE in ('START', 'DUE', 'DISBURSEMENT', 'INVOICE_ADDED')
order by activity_date, display_order, LOAN_AMORTIZATION_ID;
select curr.precision
from lns_loan_headers_all h
,fnd_currencies curr
where h.loan_id = p_loan_id
and curr.currency_code = h.loan_currency;
select
trunc(ACTIVITY_DATE),
ACTIVITY_CODE,
ACTIVITY_AMOUNT,
INTEREST_RATE,
THEORETICAL_BALANCE,
ACTUAL_BALANCE,
DAYS_LATE,
display_order
from LNS_PRIN_TRX_ACTIVITIES_V
where loan_id = p_loan_id and
trunc(ACTIVITY_DATE) >= trunc(p_start_date) and
trunc(ACTIVITY_DATE) < trunc(p_end_date) and
ACTIVITY_CODE not in ('DISBURSEMENT', 'INVOICE_ADDED')
order by activity_date, display_order;
select
trunc(ACTIVITY_DATE),
ACTIVITY_CODE,
ACTIVITY_AMOUNT,
INTEREST_RATE,
THEORETICAL_BALANCE,
ACTUAL_BALANCE,
DAYS_LATE,
display_order
from LNS_INT_TRX_ACTIVITIES_V
where loan_id = p_loan_id and
trunc(ACTIVITY_DATE) >= trunc(p_start_date) and
trunc(ACTIVITY_DATE) < trunc(p_end_date)
order by activity_date, display_order;
select decode(p_target, 'UNPAID_PRIN', nvl(term.CALC_ADD_INT_UNPAID_PRIN, 'Y'), 'UNPAID_INT', nvl(term.CALC_ADD_INT_UNPAID_INT, 'Y'))
from lns_loan_headers loan,
lns_terms term
where loan.loan_id = p_loan_id and
loan.loan_id = term.loan_id;
select curr.precision
from lns_loan_headers_all h
,fnd_currencies curr
where h.loan_id = p_loan_id
and curr.currency_code = h.loan_currency;
select to_char(l_num) into l_str from dual;
SELECT nvl(DOCUMENT_XML, empty_clob())
FROM LNS_LOAN_DOCUMENTS
WHERE source_id = p_loan_id and
SOURCE_TABLE = 'LNS_LOAN_HEADERS_ALL' and
DOCUMENT_TYPE = 'LOAN_AGREEMENT' AND
VERSION = 1;
l_nl := dbms_xslprocessor.selectNodes(dbms_xmldom.makeNode(l_doc),'/LNSAGREEMENT/ROWSET/ROW/RATE_SCHEDULE/RATE_SCHEDULE_ROW');
l_nl := dbms_xslprocessor.selectNodes(dbms_xmldom.makeNode(l_doc),'/LNSAGREEMENT/ROWSET/ROW/AMORTIZATION/AMORTIZATION_ROW');
select
trunc(ACTIVITY_DATE),
trunc(DUE_DATE),
ACTIVITY_CODE,
ACTIVITY_AMOUNT,
THEORETICAL_BALANCE,
ACTUAL_BALANCE,
DAYS_LATE,
display_order
from LNS_PRIN_TRX_ACTIVITIES_V
where loan_id = p_loan_id and
payment_number = p_payment_number and
DAYS_LATE < 0 and
ACTIVITY_CODE in ('PMT', 'CM', 'ADJUSTMENT')
order by activity_date, display_order, LOAN_AMORTIZATION_ID;
select nvl(term.CALC_EARLY_PAY_CR, 'N')
from lns_loan_headers loan,
lns_terms term
where loan.loan_id = p_loan_id and
loan.loan_id = term.loan_id;
select curr.precision
from lns_loan_headers_all h
,fnd_currencies curr
where h.loan_id = p_loan_id
and curr.currency_code = h.loan_currency;