[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select count(1)
from lns_loan_extensions
where loan_id = P_LOAN_ID and
STATUS = 'PENDING';
select STATUS
from lns_loan_extensions
where LOAN_EXT_ID = P_LOAN_EXT_ID;
select LOAN_STATUS, nvl(custom_payments_flag, 'N'), nvl(CURRENT_PHASE, 'TERM')
from lns_loan_headers_all
where loan_id = P_LOAN_ID;
if (P_ACTION = 'INSERT') then
if (l_loan_status <> 'ACTIVE' and
l_loan_status <> 'APPROVED' and
l_loan_status <> 'DEFAULT' and
l_loan_status <> 'DELINQUENT' and
l_loan_status <> 'FUNDING_ERROR' and
l_loan_status <> 'IN_FUNDING') then
-- LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'ERROR: Invalid loan status.');
elsif (P_ACTION = 'UPDATE') then
if (l_loan_status <> 'ACTIVE' and
l_loan_status <> 'APPROVED' and
l_loan_status <> 'DEFAULT' and
l_loan_status <> 'DELINQUENT' and
l_loan_status <> 'FUNDING_ERROR' and
l_loan_status <> 'IN_FUNDING') then
-- LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'ERROR: Invalid loan status.');
select
ext.LOAN_ID,
ext.OLD_INSTALLMENTS,
ext.NEW_TERM,
ext.NEW_TERM_PERIOD,
ext.NEW_BALLOON_TYPE,
ext.NEW_BALLOON_AMOUNT,
ext.NEW_AMORT_TERM,
ext.NEW_MATURITY_DATE,
ext.NEW_INSTALLMENTS,
ext.EXT_RATE,
ext.EXT_SPREAD,
ext.EXT_IO_FLAG,
ext.STATUS
from lns_loan_extensions ext
where ext.LOAN_EXT_ID = P_LOAN_EXT_ID;
select loan.LOAN_STATUS, nvl(loan.custom_payments_flag, 'N'), nvl(loan.CURRENT_PHASE, 'TERM')
from lns_loan_headers_all loan,
lns_loan_extensions ext
where ext.LOAN_EXT_ID = P_LOAN_EXT_ID and
ext.loan_id = loan.loan_id;
| This procedure inserts/updates loan extension in lns_loan_extensions table
|
| CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
| None
|
| CALLS PROCEDURES/FUNCTIONS (local to this package body)
| LogMessage
|
| PARAMETERS
| P_API_VERSION IN Standard in parameter
| P_INIT_MSG_LIST IN Standard in parameter
| P_COMMIT IN Standard in parameter
| P_VALIDATION_LEVEL IN Standard in parameter
| P_LOAN_EXT_REC IN OUT NOCOPY LNS_EXT_LOAN_PUB.LOAN_EXT_REC record
| X_RETURN_STATUS OUT NOCOPY Standard out parameter
| X_MSG_COUNT OUT NOCOPY Standard out parameter
| X_MSG_DATA OUT NOCOPY Standard out parameter
|
| KNOWN ISSUES
| None
|
| NOTES
|
| MODIFICATION HISTORY
| Date Author Description of Changes
| 09-25-2007 scherkas Created
|
*=======================================================================*/
PROCEDURE SAVE_LOAN_EXTENSION(
P_API_VERSION IN NUMBER,
P_INIT_MSG_LIST IN VARCHAR2,
P_COMMIT IN VARCHAR2,
P_VALIDATION_LEVEL IN NUMBER,
P_LOAN_EXT_REC IN OUT NOCOPY LNS_EXT_LOAN_PUB.LOAN_EXT_REC,
X_RETURN_STATUS OUT NOCOPY VARCHAR2,
X_MSG_COUNT OUT NOCOPY NUMBER,
X_MSG_DATA OUT NOCOPY VARCHAR2)
IS
/*-----------------------------------------------------------------------+
| Local Variable Declarations and initializations |
+-----------------------------------------------------------------------*/
l_api_name CONSTANT VARCHAR2(30) := 'SAVE_LOAN_EXTENSION';
VALIDATE_EXTN(P_LOAN_EXT_REC, 'INSERT');
VALIDATE_EXTN(P_LOAN_EXT_REC, 'UPDATE');
SELECT
'Y' into l_is_exist
FROM
lns_loan_extensions
WHERE
loan_ext_id = P_LOAN_EXT_REC.LOAN_EXT_ID;
LogMessage(FND_LOG.LEVEL_STATEMENT, 'Inserting into lns_loan_extensions...');
select lns_loan_extensions_s.NEXTVAL into P_LOAN_EXT_REC.LOAN_EXT_ID from dual;
insert into lns_loan_extensions(
LOAN_EXT_ID,
LOAN_ID,
DESCRIPTION,
OLD_TERM,
OLD_TERM_PERIOD,
OLD_BALLOON_TYPE,
OLD_BALLOON_AMOUNT,
OLD_AMORT_TERM,
OLD_MATURITY_DATE,
OLD_INSTALLMENTS,
EXT_TERM,
EXT_TERM_PERIOD,
EXT_BALLOON_TYPE,
EXT_BALLOON_AMOUNT,
EXT_AMORT_TERM,
EXT_RATE,
EXT_SPREAD,
EXT_IO_FLAG,
EXT_INDEX_DATE,
NEW_TERM,
NEW_TERM_PERIOD,
NEW_BALLOON_TYPE,
NEW_BALLOON_AMOUNT,
NEW_AMORT_TERM,
NEW_MATURITY_DATE,
NEW_INSTALLMENTS,
STATUS,
APPR_REJECT_DATE,
APPR_REJECT_BY,
OBJECT_VERSION_NUMBER,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN)
values(
P_LOAN_EXT_REC.LOAN_EXT_ID,
P_LOAN_EXT_REC.LOAN_ID,
P_LOAN_EXT_REC.DESCRIPTION,
l_NEW_TERM_REC.OLD_TERM,
l_NEW_TERM_REC.OLD_TERM_PERIOD,
l_NEW_TERM_REC.OLD_BALLOON_TYPE,
l_NEW_TERM_REC.OLD_BALLOON_AMOUNT,
l_NEW_TERM_REC.OLD_AMORT_TERM,
l_NEW_TERM_REC.OLD_MATURITY_DATE,
l_NEW_TERM_REC.OLD_INSTALLMENTS,
P_LOAN_EXT_REC.EXT_TERM,
P_LOAN_EXT_REC.EXT_TERM_PERIOD,
P_LOAN_EXT_REC.EXT_BALLOON_TYPE,
P_LOAN_EXT_REC.EXT_BALLOON_AMOUNT,
P_LOAN_EXT_REC.EXT_AMORT_TERM,
P_LOAN_EXT_REC.EXT_RATE,
P_LOAN_EXT_REC.EXT_SPREAD,
P_LOAN_EXT_REC.EXT_IO_FLAG,
P_LOAN_EXT_REC.EXT_INDEX_DATE,
l_NEW_TERM_REC.NEW_TERM,
l_NEW_TERM_REC.NEW_TERM_PERIOD,
l_NEW_TERM_REC.NEW_BALLOON_TYPE,
l_NEW_TERM_REC.NEW_BALLOON_AMOUNT,
l_NEW_TERM_REC.NEW_AMORT_TERM,
l_NEW_TERM_REC.NEW_MATURITY_DATE,
l_NEW_TERM_REC.NEW_INSTALLMENTS,
'PENDING',
null,
null,
1,
sysdate,
LNS_UTILITY_PUB.CREATED_BY,
sysdate,
LNS_UTILITY_PUB.LAST_UPDATED_BY,
LNS_UTILITY_PUB.LAST_UPDATE_LOGIN);
update LNS_LOAN_EXTENSIONS set
DESCRIPTION = P_LOAN_EXT_REC.DESCRIPTION,
OLD_TERM = l_NEW_TERM_REC.OLD_TERM,
OLD_TERM_PERIOD = l_NEW_TERM_REC.OLD_TERM_PERIOD,
OLD_BALLOON_TYPE = l_NEW_TERM_REC.OLD_BALLOON_TYPE,
OLD_BALLOON_AMOUNT = l_NEW_TERM_REC.OLD_BALLOON_AMOUNT,
OLD_AMORT_TERM = l_NEW_TERM_REC.OLD_AMORT_TERM,
OLD_MATURITY_DATE = l_NEW_TERM_REC.OLD_MATURITY_DATE,
OLD_INSTALLMENTS = l_NEW_TERM_REC.OLD_INSTALLMENTS,
EXT_TERM = P_LOAN_EXT_REC.EXT_TERM,
EXT_TERM_PERIOD = P_LOAN_EXT_REC.EXT_TERM_PERIOD,
EXT_BALLOON_TYPE = P_LOAN_EXT_REC.EXT_BALLOON_TYPE,
EXT_BALLOON_AMOUNT = P_LOAN_EXT_REC.EXT_BALLOON_AMOUNT,
EXT_AMORT_TERM = P_LOAN_EXT_REC.EXT_AMORT_TERM,
EXT_RATE = P_LOAN_EXT_REC.EXT_RATE,
EXT_SPREAD = P_LOAN_EXT_REC.EXT_SPREAD,
EXT_IO_FLAG = P_LOAN_EXT_REC.EXT_IO_FLAG,
EXT_INDEX_DATE = P_LOAN_EXT_REC.EXT_INDEX_DATE,
NEW_TERM = l_NEW_TERM_REC.NEW_TERM,
NEW_TERM_PERIOD = l_NEW_TERM_REC.NEW_TERM_PERIOD,
NEW_BALLOON_TYPE = l_NEW_TERM_REC.NEW_BALLOON_TYPE,
NEW_BALLOON_AMOUNT = l_NEW_TERM_REC.NEW_BALLOON_AMOUNT,
NEW_AMORT_TERM = l_NEW_TERM_REC.NEW_AMORT_TERM,
NEW_MATURITY_DATE = l_NEW_TERM_REC.NEW_MATURITY_DATE,
NEW_INSTALLMENTS = l_NEW_TERM_REC.NEW_INSTALLMENTS,
LAST_UPDATE_DATE = sysdate,
LAST_UPDATED_BY = LNS_UTILITY_PUB.LAST_UPDATED_BY,
LAST_UPDATE_LOGIN = LNS_UTILITY_PUB.LAST_UPDATE_LOGIN
where LOAN_EXT_ID = P_LOAN_EXT_REC.LOAN_EXT_ID;
| This procedure approves loan extension and updates loan term data in
| lns_loan_headers_all from lns_loan_extensions table
|
| CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
| None
|
| CALLS PROCEDURES/FUNCTIONS (local to this package body)
| LogMessage
|
| PARAMETERS
| P_API_VERSION IN Standard in parameter
| P_INIT_MSG_LIST IN Standard in parameter
| P_COMMIT IN Standard in parameter
| P_VALIDATION_LEVEL IN Standard in parameter
| P_LOAN_EXT_ID IN Loan extension ID
| X_RETURN_STATUS OUT NOCOPY Standard out parameter
| X_MSG_COUNT OUT NOCOPY Standard out parameter
| X_MSG_DATA OUT NOCOPY Standard out parameter
|
| KNOWN ISSUES
| None
|
| NOTES
|
| MODIFICATION HISTORY
| Date Author Description of Changes
| 09-25-2007 scherkas Created
|
*=======================================================================*/
PROCEDURE APPROVE_LOAN_EXTENSION(
P_API_VERSION IN NUMBER,
P_INIT_MSG_LIST IN VARCHAR2,
P_COMMIT IN VARCHAR2,
P_VALIDATION_LEVEL IN NUMBER,
P_LOAN_EXT_ID IN NUMBER,
X_RETURN_STATUS OUT NOCOPY VARCHAR2,
X_MSG_COUNT OUT NOCOPY NUMBER,
X_MSG_DATA OUT NOCOPY VARCHAR2)
IS
/*-----------------------------------------------------------------------+
| Local Variable Declarations and initializations |
+-----------------------------------------------------------------------*/
l_api_name CONSTANT VARCHAR2(30) := 'APPROVE_LOAN_EXTENSION';
select
ext.LOAN_ID,
ext.OLD_INSTALLMENTS,
ext.NEW_TERM,
ext.NEW_TERM_PERIOD,
ext.NEW_BALLOON_TYPE,
ext.NEW_BALLOON_AMOUNT,
ext.NEW_AMORT_TERM,
ext.NEW_MATURITY_DATE,
ext.NEW_INSTALLMENTS,
ext.EXT_RATE,
ext.EXT_SPREAD,
ext.EXT_IO_FLAG,
ext.EXT_INDEX_DATE,
loan.OBJECT_VERSION_NUMBER,
term.term_id,
nvl(loan.custom_payments_flag, 'N'),
ext.DESCRIPTION,
ext.EXT_TERM,
ext.EXT_TERM_PERIOD
from lns_loan_extensions ext,
lns_loan_headers_all loan,
lns_terms term
where ext.LOAN_EXT_ID = P_LOAN_EXT_ID and
loan.LOAN_ID = ext.LOAN_ID and
term.loan_id = loan.LOAN_ID;
select RATE_ID,
CURRENT_INTEREST_RATE,
BEGIN_INSTALLMENT_NUMBER,
END_INSTALLMENT_NUMBER,
INDEX_RATE,
SPREAD,
INTEREST_ONLY_FLAG
from lns_rate_schedules
where term_id = p_term_id and
END_DATE_ACTIVE is null and
phase = 'TERM'
order by END_INSTALLMENT_NUMBER desc;
LNS_LOAN_HEADER_PUB.UPDATE_LOAN(P_OBJECT_VERSION_NUMBER => l_OBJECT_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);
update lns_rate_schedules
set END_INSTALLMENT_NUMBER = l_NEW_INSTALLMENTS
where term_id = l_TERM_ID and
RATE_ID = l_RATE_ID;
LogMessage(FND_LOG.LEVEL_STATEMENT, 'Inserting into LNS_RATE_SCHEDULES...');
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
,PHASE)
VALUES
(LNS_RATE_SCHEDULES_S.nextval
,l_TERM_ID
,l_EXT_RATE
,l_EXT_SPREAD
,(l_EXT_RATE+l_EXT_SPREAD)
,sysdate
,null
,lns_utility_pub.created_by
,sysdate
,lns_utility_pub.last_updated_by
,sysdate
,lns_utility_pub.LAST_UPDATE_LOGIN
,1
,l_EXT_INDEX_DATE
,l_END_INSTALLMENT+1
,l_NEW_INSTALLMENTS
,l_EXT_IO_FLAG
,'TERM');
update lns_rate_schedules
set END_INSTALLMENT_NUMBER = l_NEW_INSTALLMENTS
where term_id = l_TERM_ID and
RATE_ID = l_RATE_ID;
delete from lns_rate_schedules
where term_id = l_TERM_ID and
RATE_ID = l_RATE_ID;
delete from LNS_CUSTOM_PAYMNT_SCHEDS
where loan_id = l_LOAN_ID
and DUE_DATE > l_NEW_MATURITY_DATE;
update LNS_LOAN_EXTENSIONS
set STATUS = 'APPROVED',
APPR_REJECT_DATE = sysdate,
APPR_REJECT_BY = LNS_UTILITY_PUB.USER_ID,
LAST_UPDATE_DATE = sysdate,
LAST_UPDATED_BY = LNS_UTILITY_PUB.LAST_UPDATED_BY,
LAST_UPDATE_LOGIN = LNS_UTILITY_PUB.LAST_UPDATE_LOGIN,
LAST_BILLED_INSTALLMENT = l_last_billed_installment
where LOAN_EXT_ID = P_LOAN_EXT_ID;
FND_MSG_PUB.DELETE_MSG(FND_MSG_PUB.COUNT_MSG);
update LNS_LOAN_EXTENSIONS
set STATUS = 'REJECTED',
APPR_REJECT_DATE = sysdate,
APPR_REJECT_BY = LNS_UTILITY_PUB.USER_ID,
LAST_UPDATE_DATE = sysdate,
LAST_UPDATED_BY = LNS_UTILITY_PUB.LAST_UPDATED_BY,
LAST_UPDATE_LOGIN = LNS_UTILITY_PUB.LAST_UPDATE_LOGIN
where LOAN_EXT_ID = P_LOAN_EXT_ID;
select
loan.loan_start_date,
loan.loan_term,
loan.LOAN_TERM_PERIOD,
loan.BALLOON_PAYMENT_TYPE,
loan.BALLOON_PAYMENT_AMOUNT,
loan.AMORTIZED_TERM,
loan.LOAN_MATURITY_DATE,
term.loan_payment_frequency,
term.term_id,
term.amortization_frequency,
trunc(term.first_payment_date),
decode(trunc(term.first_payment_date) - trunc(loan.loan_start_date), 0, 'N', 'Y'), -- calculate in advance or arrears
nvl(term.PAYMENT_CALC_METHOD, 'EQUAL_PAYMENT'),
trunc(nvl(term.prin_first_pay_date, term.first_payment_date)),
nvl(term.prin_payment_frequency, term.loan_payment_frequency),
decode(trunc(nvl(term.prin_first_pay_date, term.first_payment_date)) - trunc(loan.loan_start_date), 0, 'N', 'Y'),
nvl(loan.custom_payments_flag, 'N')
from lns_loan_headers_all loan,
lns_terms term
where loan.loan_id = P_LOAN_ID and
loan.loan_id = term.loan_id;
select max(end_installment_number)
from LNS_RATE_SCHEDULES
where term_id = P_TERM_ID and
phase = 'TERM' and
trunc(nvl(END_DATE_ACTIVE,(sysdate+1))) > trunc(sysdate);
select count(1)
from LNS_CUSTOM_PAYMNT_SCHEDS
where loan_id = P_LOAN_ID
and DUE_DATE <= P_MATURITY_DATE;