[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select nvl(max(am.PAYMENT_NUMBER), 0)
from LNS_AMORTIZATION_SCHEDS am,
lns_loan_headers head
where am.LOAN_ID = P_LOAN_ID and
am.LOAN_ID = head.LOAN_ID
and (am.REVERSED_FLAG is null or am.REVERSED_FLAG = 'N')
and am.PARENT_AMORTIZATION_ID is null
and nvl(am.PHASE, 'TERM') = nvl(head.CURRENT_PHASE, 'TERM');
select nvl(max(am.PAYMENT_NUMBER), 0)
from LNS_AMORTIZATION_SCHEDS am,
lns_loan_headers head
where am.LOAN_ID = P_LOAN_ID
and am.LOAN_ID = head.LOAN_ID
and (am.REVERSED_FLAG is null or am.REVERSED_FLAG = 'N')
and am.PARENT_AMORTIZATION_ID is null
and am.REAMORTIZATION_AMOUNT is null
and nvl(am.PHASE, 'TERM') = nvl(P_PHASE, nvl(head.CURRENT_PHASE, 'TERM'));
select nvl(max(am.PAYMENT_NUMBER), -1)
from LNS_AMORTIZATION_SCHEDS am,
lns_loan_headers head
where am.LOAN_ID = P_LOAN_ID and
am.LOAN_ID = head.LOAN_ID
and (am.REVERSED_FLAG is null or am.REVERSED_FLAG = 'N')
-- and am.PARENT_AMORTIZATION_ID is null
and am.REAMORTIZATION_AMOUNT is null -- fix for bug 7422383
and nvl(am.PHASE, 'TERM') = nvl(head.CURRENT_PHASE, 'TERM');
select nvl(max(am.PAYMENT_NUMBER), -1)
from LNS_AMORTIZATION_SCHEDS am,
lns_loan_headers head
where am.LOAN_ID = P_LOAN_ID and
am.LOAN_ID = head.LOAN_ID
and (am.REVERSED_FLAG is null or am.REVERSED_FLAG = 'N')
-- and am.PARENT_AMORTIZATION_ID is null
and am.REAMORTIZATION_AMOUNT is null -- fix for bug 7422383
and nvl(am.PHASE, 'TERM') = nvl(head.CURRENT_PHASE, 'TERM');
SELECT nvl(count(1),0)
INTO l_zero_inst_count
FROM lns_fee_schedules schd
,lns_fees_all struct
,lns_loan_headers_all loan
WHERE loan.loan_id = P_LOAN_ID
AND schd.loan_id = loan.loan_id
AND schd.fee_id = struct.fee_id
AND struct.fee_type = 'EVENT_ORIGINATION'
AND schd.fee_installment = 0
AND schd.active_flag = 'Y'
AND schd.phase = nvl(loan.CURRENT_PHASE, 'TERM');
select nvl(max(am.PAYMENT_NUMBER), -1)
from LNS_AMORTIZATION_SCHEDS am,
lns_loan_headers head
where am.LOAN_ID = P_LOAN_ID and
am.LOAN_ID = head.LOAN_ID
and (am.REVERSED_FLAG is null or am.REVERSED_FLAG = 'N')
and am.PARENT_AMORTIZATION_ID is null
and am.REAMORTIZATION_AMOUNT is null -- fix for bug 7422383
and nvl(am.PHASE, 'TERM') = nvl(head.CURRENT_PHASE, 'TERM');
select nvl(max(PAYMENT_NUMBER), -1)
from LNS_AMORTIZATION_SCHEDS
where LOAN_ID = P_LOAN_ID
and (REVERSED_FLAG is null or REVERSED_FLAG = 'N')
and REAMORTIZATION_AMOUNT is null
and nvl(PHASE, 'TERM') = nvl(P_PHASE, 'TERM');
select nvl(max(am.AMORTIZATION_SCHEDULE_ID), -1)
from LNS_AMORTIZATION_SCHEDS am,
lns_loan_headers head
where am.LOAN_ID = P_LOAN_ID
and am.LOAN_ID = head.LOAN_ID
and am.PAYMENT_NUMBER = LAST_PAYMENT_NUMBER(am.LOAN_ID)
and (am.REVERSED_FLAG is null or am.REVERSED_FLAG = 'N')
and am.PARENT_AMORTIZATION_ID is null
and nvl(am.PHASE, 'TERM') = nvl(head.CURRENT_PHASE, 'TERM');
select trunc(max(DUE_DATE))
from LNS_AMORTIZATION_SCHEDS am,
lns_loan_headers head
where am.LOAN_ID = P_LOAN_ID
and am.LOAN_ID = head.LOAN_ID
and (am.REVERSED_FLAG is null or am.REVERSED_FLAG = 'N')
and am.PARENT_AMORTIZATION_ID is null
and am.REAMORTIZATION_AMOUNT is null
and nvl(am.PHASE, 'TERM') = nvl(P_PHASE, nvl(head.CURRENT_PHASE, 'TERM'));
select loan.funded_amount -
(select nvl(sum(rec.amount_applied), 0)
from
ar_receivable_applications_all rec,
LNS_AMORTIZATION_SCHEDS am
where
rec.application_type = 'CASH' and
trunc(rec.apply_date) <= trunc(P_DATE) and
rec.APPLIED_CUSTOMER_TRX_ID = am.principal_trx_id and
rec.org_id = loan.org_id and
am.loan_id = loan.loan_id and
am.principal_trx_id is not null and
nvl(am.PHASE, 'TERM') = nvl(loan.CURRENT_PHASE, 'TERM'))
from lns_loan_headers_all loan
where loan.loan_id = P_LOAN_ID;
| ('INCOMPLETE','REJECTED','DELETED','PENDING','APPROVED','IN_FUNDING','FUNDING_ERROR')
| and remaining amount for the other loan statuses.
|
| CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
| None
|
| CALLS PROCEDURES/FUNCTIONS (local to this package body)
| LogMessage
|
| PARAMETERS
| P_LOAN_ID IN Loan ID
|
| KNOWN ISSUES
| None
|
| NOTES
| Any interesting aspect of the code in the package body which needs
| to be stated.
|
| MODIFICATION HISTORY
| Date Author Description of Changes
| 22-APR-2009 MBOLLI Created for bug#8545962
|
*=======================================================================*/
FUNCTION GET_LOAN_REMAIN_AMOUNT(P_LOAN_ID IN NUMBER) return NUMBER
IS
/*-----------------------------------------------------------------------+
| Local Variable Declarations and initializations |
+-----------------------------------------------------------------------*/
l_api_name CONSTANT VARCHAR2(30) := 'GET_LOAN_REMAIN_AMOUNT';
select TOTAL_PRINCIPAL_BALANCE
from LNS_PAY_SUM_V
where loan_id = P_LOAN_ID;
SELECT
(CASE WHEN LnsLoanHeaderEO.loan_status in ('INCOMPLETE','REJECTED','DELETED','PENDING','APPROVED','IN_FUNDING','FUNDING_ERROR','CANCELLED') OR LnsLoanHeaderEO.FUNDED_AMOUNT = 0 THEN LnsLoanHeaderEO.requested_amount
ELSE
LnsLoanHeaderEO.FUNDED_AMOUNT -
(select nvl(SUM(abs(nvl(psa_prin.AMOUNT_APPLIED, 0))) - SUM(nvl(psa_prin.AMOUNT_ADJUSTED, 0)) + SUM(abs(nvl(psa_prin.AMOUNT_CREDITED, 0))), 0)
from
LNS_AMORTIZATION_SCHEDS am
,ar_payment_schedules_all psa_prin
where
am.loan_id = LnsLoanHeaderEO.loan_id
and am.PHASE = LnsLoanHeaderEO.CURRENT_PHASE
and am.PAYMENT_NUMBER <= LnsLoanHeaderEO.LAST_PAYMENT_NUMBER
and (am.REVERSED_FLAG is null or am.REVERSED_FLAG = 'N')
and am.REAMORTIZATION_AMOUNT is null
and psa_prin.customer_trx_id = am.principal_trx_id)
END)
FROM LNS_LOAN_HEADERS_ALL LnsLoanHeaderEO
WHERE loan_id = P_LOAN_ID;
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 REAMORTIZATION_AMOUNT is null
AND nvl(phase, 'TERM') = 'TERM';
select nvl(RA_TERM_ID , 5)
from lns_system_options
where org_id = MO_GLOBAL.GET_CURRENT_ORG_ID();
SELECT line.DUE_DAYS, term.NAME
FROM RA_TERMS term,
ra_terms_lines line
WHERE line.TERM_ID = P_TERM_ID and
line.DUE_DAYS is not null and
line.DUE_DATE is null and
line.DUE_DAY_OF_MONTH is null and
line.DUE_MONTHS_FORWARD is null and
line.RELATIVE_AMOUNT = 100 and
term.term_id = line.TERM_ID and
term.BILLING_CYCLE_ID IS NULL and
(SELECT count(1) FROM ra_terms_lines WHERE term_id = line.TERM_ID) = 1;
SELECT NAME
FROM RA_TERMS
WHERE TERM_ID = P_TERM_ID;