[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
,p_update_header IN boolean
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2)
is
l_api_name varchar2(25);
l_skip_update boolean;
SELECT nvl(h.custom_payments_flag, 'N')
FROM lns_loan_headers_all h
WHERE loan_id = p_loan_id;
l_skip_update := true;
l_skip_update := false;
delete
from lns_custom_paymnt_scheds
where loan_id = p_loan_id;
if p_update_header and not l_skip_update then
select object_version_number into l_object_version
from lns_loan_headers_all
where loan_id = p_loan_id;
lns_loan_header_pub.update_loan(p_init_msg_list => FND_API.G_FALSE
,p_loan_header_rec => l_loan_header_rec
,p_object_version_number => l_object_version
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data);
select nvl(sum(sched.fee_amount), 0)
from lns_fee_schedules sched
,lns_fees struct
where sched.loan_id = p_loan_id
and sched.fee_id = struct.fee_id
and fee_installment = p_installment
and active_flag = 'Y';
select loan_start_date, funded_amount
from lns_loan_headers
where loan_id = p_loan_id;
logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - attempting to update header set custom = Y');
select object_version_number into l_object_version
from lns_loan_headers_all
where loan_id = p_loan_id;
lns_loan_header_pub.update_loan(p_init_msg_list => FND_API.G_FALSE
,p_loan_header_rec => l_loan_header_rec
,P_OBJECT_VERSION_NUMBER => l_object_version
,X_RETURN_STATUS => l_return_status
,X_MSG_COUNT => l_msg_count
,X_MSG_DATA => l_msg_data);
logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - header update set custom = Y');
logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - header update ERROR');
FND_MESSAGE.Set_Name('LNS', 'LNS_HEADER_UPDATE_ERROR');
|| PUBLIC PROCEDURE updateCustomSchedule
||
|| DESCRIPTION
||
|| Overview: updates a custom payment schedule for a loan
||
|| Parameter: loan_id => loan id to customize
|| p_custom_tbl => table of records about custom schedule
||
|| Return value: standard API outputs
||
|| Source Tables: NA
||
|| Target Tables: LNS_CUSTOM_PAYMENT_SCHEDULE
||
|| Return value:
||
|| KNOWN ISSUES
||
|| NOTES
||
|| MODIFICATION HISTORY
|| Date Author Description of Changes
|| 12/08/2003 11:35AM raverma Created
||
*=======================================================================*/
procedure updateCustomSchedule(p_custom_tbl IN CUSTOM_TBL
,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
,X_INVALID_INSTALLMENT_NUM OUT NOCOPY NUMBER)
is
l_msg_count NUMBER;
select count(1)
from lns_amortization_scheds
where reamortization_amount is null
and reversed_flag <> 'Y'
and loan_id = p_loan_id
and payment_number > 0
and parent_amortization_id is null;
select custom_schedule_id, object_version_number
from lns_custom_paymnt_scheds
where loan_id = p_loan_id
and payment_number = p_payment_number;
l_api_name := 'updateCustomSchedule';
SAVEPOINT updateCustomSchedule;
lns_custom_pub.updateCustomSched(P_CUSTOM_REC => l_custom_rec
,x_return_status => l_return_status
,x_msg_count => l_msg_Count
,x_msg_data => l_msg_Data);
ROLLBACK TO updateCustomSchedule;
ROLLBACK TO updateCustomSchedule;
ROLLBACK TO updateCustomSchedule;
logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'in update validation');
' Select count(1) ' ||
' From lns_amortization_scheds ' ||
' where loan_id = :p_loan_id ' ||
' and reversed_flag <> ''Y'' ' ||
' and reamortization_amount is null ' ||
' and payment_number > 0 ' ||
' and parent_amortization_id is null '
into l_count
using p_loan_id;
logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'passed update validation');
logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'create / update validation');
LNS_CUSTOM_PAYMNT_SCHEDS_PKG.INSERT_ROW(x_custom_schedule_id => l_custom_id
,P_LOAN_ID => P_CUSTOM_REC.LOAN_ID
,P_PAYMENT_NUMBER => P_CUSTOM_REC.PAYMENT_NUMBER
,P_DUE_DATE => P_CUSTOM_REC.DUE_DATE
,P_PRINCIPAL_AMOUNT => P_CUSTOM_REC.PRINCIPAL_AMOUNT
,P_INTEREST_AMOUNT => P_CUSTOM_REC.INTEREST_AMOUNT
-- ,P_PRINCIPAL_BALANCE => P_CUSTOM_REC.PRINCIPAL_BALANCE
,P_FEE_AMOUNT => P_CUSTOM_REC.FEE_AMOUNT
,P_OTHER_AMOUNT => P_CUSTOM_REC.OTHER_AMOUNT
,p_INSTALLMENT_BEGIN_BALANCE => P_CUSTOM_REC.INSTALLMENT_BEGIN_BALANCE
,p_INSTALLMENT_END_BALANCE => P_CUSTOM_REC.INSTALLMENT_END_BALANCE
,p_CURRENT_TERM_PAYMENT => P_CUSTOM_REC.CURRENT_TERM_PAYMENT
,p_OBJECT_VERSION_NUMBER => 1
,p_ATTRIBUTE_CATEGORY => P_CUSTOM_REC.ATTRIBUTE_CATEGORY
,p_ATTRIBUTE1 => P_CUSTOM_REC.ATTRIBUTE1
,p_ATTRIBUTE2 => P_CUSTOM_REC.ATTRIBUTE2
,p_ATTRIBUTE3 => P_CUSTOM_REC.ATTRIBUTE3
,p_ATTRIBUTE4 => P_CUSTOM_REC.ATTRIBUTE4
,p_ATTRIBUTE5 => P_CUSTOM_REC.ATTRIBUTE5
,p_ATTRIBUTE6 => P_CUSTOM_REC.ATTRIBUTE6
,p_ATTRIBUTE7 => P_CUSTOM_REC.ATTRIBUTE7
,p_ATTRIBUTE8 => P_CUSTOM_REC.ATTRIBUTE8
,p_ATTRIBUTE9 => P_CUSTOM_REC.ATTRIBUTE9
,p_ATTRIBUTE10 => P_CUSTOM_REC.ATTRIBUTE10
,p_ATTRIBUTE11 => P_CUSTOM_REC.ATTRIBUTE11
,p_ATTRIBUTE12 => P_CUSTOM_REC.ATTRIBUTE12
,p_ATTRIBUTE13 => P_CUSTOM_REC.ATTRIBUTE13
,p_ATTRIBUTE14 => P_CUSTOM_REC.ATTRIBUTE14
,p_ATTRIBUTE15 => P_CUSTOM_REC.ATTRIBUTE15
,p_ATTRIBUTE16 => P_CUSTOM_REC.ATTRIBUTE16
,p_ATTRIBUTE17 => P_CUSTOM_REC.ATTRIBUTE17
,p_ATTRIBUTE18 => P_CUSTOM_REC.ATTRIBUTE18
,p_ATTRIBUTE19 => P_CUSTOM_REC.ATTRIBUTE19
,p_ATTRIBUTE20 => P_CUSTOM_REC.ATTRIBUTE20
,p_LOCK_PRIN => P_CUSTOM_REC.LOCK_PRIN
,p_LOCK_INT => P_CUSTOM_REC.LOCK_INT);
logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'In createCustomSched: After call Insert_Row ID' || l_Custom_id );
procedure updateCustomSched(P_CUSTOM_REC IN CUSTOM_SCHED_TYPE
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2)
is
l_msg_count NUMBER;
l_api_name := 'updateCustomSched';
LNS_CUSTOM_PAYMNT_SCHEDS_PKG.Update_Row(p_CUSTOM_SCHEDULE_ID => P_CUSTOM_REC.CUSTOM_SCHEDULE_ID
,p_LOAN_ID => P_CUSTOM_REC.LOAN_ID
,p_PAYMENT_NUMBER => P_CUSTOM_REC.PAYMENT_NUMBER
,p_DUE_DATE => P_CUSTOM_REC.DUE_DATE
,p_PRINCIPAL_AMOUNT => P_CUSTOM_REC.PRINCIPAL_AMOUNT
,p_INTEREST_AMOUNT => P_CUSTOM_REC.INTEREST_AMOUNT
-- ,p_PRINCIPAL_BALANCE => P_CUSTOM_REC.PRINCIPAL_BALANCE
,p_FEE_AMOUNT => P_CUSTOM_REC.FEE_AMOUNT
,p_OTHER_AMOUNT => P_CUSTOM_REC.OTHER_AMOUNT
,p_INSTALLMENT_BEGIN_BALANCE => P_CUSTOM_REC.INSTALLMENT_BEGIN_BALANCE
,p_INSTALLMENT_END_BALANCE => P_CUSTOM_REC.INSTALLMENT_END_BALANCE
,p_CURRENT_TERM_PAYMENT => P_CUSTOM_REC.CURRENT_TERM_PAYMENT
,p_OBJECT_VERSION_NUMBER => l_object_version
,p_ATTRIBUTE_CATEGORY => P_CUSTOM_REC.ATTRIBUTE_CATEGORY
,p_ATTRIBUTE1 => P_CUSTOM_REC.ATTRIBUTE1
,p_ATTRIBUTE2 => P_CUSTOM_REC.ATTRIBUTE2
,p_ATTRIBUTE3 => P_CUSTOM_REC.ATTRIBUTE3
,p_ATTRIBUTE4 => P_CUSTOM_REC.ATTRIBUTE4
,p_ATTRIBUTE5 => P_CUSTOM_REC.ATTRIBUTE5
,p_ATTRIBUTE6 => P_CUSTOM_REC.ATTRIBUTE6
,p_ATTRIBUTE7 => P_CUSTOM_REC.ATTRIBUTE7
,p_ATTRIBUTE8 => P_CUSTOM_REC.ATTRIBUTE8
,p_ATTRIBUTE9 => P_CUSTOM_REC.ATTRIBUTE9
,p_ATTRIBUTE10 => P_CUSTOM_REC.ATTRIBUTE10
,p_ATTRIBUTE11 => P_CUSTOM_REC.ATTRIBUTE11
,p_ATTRIBUTE12 => P_CUSTOM_REC.ATTRIBUTE12
,p_ATTRIBUTE13 => P_CUSTOM_REC.ATTRIBUTE13
,p_ATTRIBUTE14 => P_CUSTOM_REC.ATTRIBUTE14
,p_ATTRIBUTE15 => P_CUSTOM_REC.ATTRIBUTE15
,p_ATTRIBUTE16 => P_CUSTOM_REC.ATTRIBUTE16
,p_ATTRIBUTE17 => P_CUSTOM_REC.ATTRIBUTE17
,p_ATTRIBUTE18 => P_CUSTOM_REC.ATTRIBUTE18
,p_ATTRIBUTE19 => P_CUSTOM_REC.ATTRIBUTE19
,p_ATTRIBUTE20 => P_CUSTOM_REC.ATTRIBUTE20
,p_LOCK_PRIN => P_CUSTOM_REC.LOCK_PRIN
,p_LOCK_INT => P_CUSTOM_REC.LOCK_INT);
logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'In updateCustomSched: After call Insert_Row');
END updateCustomSched;
This procedure will filter the custom tbl from deleted rows
*/
procedure filterCustSchedule(p_custom_tbl in out nocopy LNS_CUSTOM_PUB.custom_tbl)
is
l_custom_tbl LNS_CUSTOM_PUB.custom_tbl;
select RATE_ID, CURRENT_INTEREST_RATE, BEGIN_INSTALLMENT_NUMBER, END_INSTALLMENT_NUMBER
from lns_rate_schedules
where term_id = p_term_id and
END_DATE_ACTIVE is null and
nvl(PHASE, 'TERM') = 'TERM'
order by END_INSTALLMENT_NUMBER desc;
update lns_rate_schedules
set END_INSTALLMENT_NUMBER = p_num_installments
where term_id = p_term_id and
RATE_ID = l_RATE_ID;
update lns_rate_schedules
set END_INSTALLMENT_NUMBER = p_num_installments
where term_id = p_term_id and
RATE_ID = l_RATE_ID;
delete from lns_rate_schedules
where term_id = p_term_id and
RATE_ID = l_RATE_ID;
l_rate_tbl.delete(i);
SELECT h.loan_id
,t.amortization_frequency
,t.loan_payment_frequency
,trunc(h.loan_start_date)
,h.funded_amount
,lns_financials.getRemainingBalance(p_loan_id)
,trunc(h.loan_maturity_date)
,decode(p_based_on_terms, 'CURRENT', LNS_BILLING_UTIL_PUB.LAST_PAYMENT_NUMBER(p_loan_id), 0)
-- ,decode(p_based_on_terms, 'CURRENT', LNS_BILLING_UTIL_PUB.LAST_PAYMENT_NUMBER_EXT(p_loan_id), -1)
,decode(nvl(t.day_count_method, 'PERIODIC30_360'), 'PERIODIC30_360', '30/360', t.day_count_method)
,nvl(h.custom_payments_flag, 'N')
,h.loan_status
,h.loan_currency
,curr.precision
-- ,nvl(t.PAYMENT_CALC_METHOD, 'EQUAL_PAYMENT')
,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'))
)
,t.CALCULATION_METHOD
,t.INTEREST_COMPOUNDING_FREQ
,nvl(t.CUSTOM_CALC_METHOD, 'NONE')
-- ,nvl(t.CUSTOM_CALC_METHOD, decode(nvl(t.PAYMENT_CALC_METHOD, 'EQUAL_PAYMENT'), 'EQUAL_PAYMENT', 'EQUAL_PAYMENT',
-- 'EQUAL_PAYMENT_STANDARD', 'EQUAL_PAYMENT', 'EQUAL_PRINCIPAL', 'EQUAL_PRINCIPAL', 'SEPARATE_SCHEDULES', 'EQUAL_PRINCIPAL'))
,t.ORIG_PAY_CALC_METHOD
,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.INDEX_RATE_ID TERM_INDEX_RATE_ID
,t.TERM_PROJECTED_RATE INITIAL_INTEREST_RATE
,nvl(lns_fin_utils.getActiveRate(h.loan_id), t.TERM_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.TERM_PROJECTED_RATE TERM_PROJECTED_RATE
,nvl(t.PENAL_INT_RATE, 0)
,nvl(t.PENAL_INT_GRACE_DAYS, 0)
FROM lns_loan_headers_all h
,lns_terms t
,fnd_currencies curr
WHERE h.loan_id = p_loan_id
AND h.loan_id = t.loan_id
AND curr.currency_code = h.loan_currency;
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 trunc(max(DUE_DATE))
from lns_amortization_scheds
where loan_id = p_loan_id
and (REVERSED_FLAG is null or REVERSED_FLAG = 'N')
--and parent_amortization_id is null
and REAMORTIZATION_AMOUNT is null
and nvl(phase, 'TERM') = 'TERM';
select
CUSTOM_SCHEDULE_ID,
LOAN_ID,
PAYMENT_NUMBER,
DUE_DATE,
nvl(PRINCIPAL_AMOUNT, 0),
nvl(INTEREST_AMOUNT, 0),
nvl(FEE_AMOUNT, 0),
nvl(OTHER_AMOUNT, 0),
nvl(LOCK_PRIN, 'Y'),
nvl(LOCK_INT, 'Y')
from LNS_CUSTOM_PAYMNT_SCHEDS
where loan_id = p_loan_id
and PAYMENT_NUMBER > p_begin_installment
order by PAYMENT_NUMBER;
select nvl(sum(sched.fee_amount), 0)
from lns_fee_schedules sched
,lns_fees struct
where sched.loan_id = p_loan_id
and sched.fee_id = struct.fee_id
and fee_installment = p_installment
and active_flag = 'Y';
select sum(nvl(fee_amount,0))
from lns_fee_schedules sch,
lns_fees fees
where sch.active_flag = 'Y'
and sch.billed_flag = 'N'
and fees.fee_id = sch.fee_id
and ((fees.fee_category = 'MANUAL')
OR (fees.fee_category = 'EVENT' AND fees.fee_type = 'EVENT_LATE_CHARGE'))
and sch.loan_id = p_loan_id
and fee_installment = p_installment;
select ACTIVITY_DATE
from LNS_PRIN_TRX_ACTIVITIES_V
where loan_id = p_loan_id
and PAYMENT_NUMBER = p_installment_number
and ACTIVITY_CODE in ('BILLING', 'START');
l_orig_fees_tbl.delete;
l_memo_fees_tbl.delete;
l_fees_tbl.delete;
update LNS_CUSTOM_PAYMNT_SCHEDS
set PAYMENT_NUMBER = -1
where loan_id = p_loan_id
and PAYMENT_NUMBER > l_loan_details.LAST_INSTALLMENT_BILLED;
select object_version_number
into l_custom_rec.OBJECT_VERSION_NUMBER
from LNS_CUSTOM_PAYMNT_SCHEDS
where loan_id = p_loan_id and
CUSTOM_SCHEDULE_ID = l_custom_tbl(i).CUSTOM_SCHEDULE_ID;
lns_custom_pub.updateCustomSched(P_CUSTOM_REC => l_custom_rec
,x_return_status => l_return_status
,x_msg_count => l_msg_Count
,x_msg_data => l_msg_Data);
delete from LNS_CUSTOM_PAYMNT_SCHEDS where loan_id = p_loan_id and PAYMENT_NUMBER = -1;
select object_version_number
into l_object_version
from lns_loan_headers_all
where loan_id = p_loan_id;
lns_loan_header_pub.update_loan(p_init_msg_list => FND_API.G_TRUE
,p_loan_header_rec => l_loan_header_rec
,P_OBJECT_VERSION_NUMBER => l_object_version
,X_RETURN_STATUS => l_return_status
,X_MSG_COUNT => l_msg_count
,X_MSG_DATA => l_msg_data);
LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Successfully updated lns_loan_headers_all');
select term_id, object_version_number into l_term_id, l_object_version
from lns_terms
where loan_id = p_loan_id;
LNS_TERMS_PUB.update_term(P_OBJECT_VERSION_NUMBER => l_object_version,
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);
LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Successfully update LNS_TERMS');
delete from LNS_CUSTOM_PAYMNT_SCHEDS
where loan_id = p_loan_id;
select object_version_number
into l_object_version
from lns_loan_headers_all
where loan_id = p_loan_id;
lns_loan_header_pub.update_loan(p_init_msg_list => FND_API.G_TRUE
,p_loan_header_rec => l_loan_header_rec
,P_OBJECT_VERSION_NUMBER => l_object_version
,X_RETURN_STATUS => l_return_status
,X_MSG_COUNT => l_msg_count
,X_MSG_DATA => l_msg_data);
LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Successfully updated lns_loan_headers_all');
select term_id, object_version_number into l_term_id, l_object_version
from lns_terms
where loan_id = p_loan_id;
LNS_TERMS_PUB.update_term(P_OBJECT_VERSION_NUMBER => l_object_version,
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);
LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Successfully update LNS_TERMS');
LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Nothing to update');
delete from LNS_CUSTOM_PAYMNT_SCHEDS
where loan_id = p_loan_id
and due_date > l_maturity_date;
LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Cust Rows might be deleted.');
LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Nothing to update');
select PAYMENT_NUMBER, DUE_DATE
from lns_amortization_scheds
where loan_id = p_loan_id
and (REVERSED_FLAG is null or REVERSED_FLAG = 'N')
and parent_amortization_id is null
and REAMORTIZATION_AMOUNT is null
and nvl(phase, 'TERM') = 'TERM'
order by PAYMENT_NUMBER;
select PAYMENT_NUMBER, DUE_DATE
from LNS_CUSTOM_PAYMNT_SCHEDS
where loan_id = p_loan_id
and PAYMENT_NUMBER >= p_min_payment
order by PAYMENT_NUMBER;
select custom_schedule_id
from lns_custom_paymnt_scheds
where loan_id = p_loan_id
and payment_number = p_payment_number;
LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, 'Failed to insert custom schedule row');