[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
|| 6. if success then update loan_stats = 'PAIDOFF'
||
||
|| MODIFICATION HISTORY
|| Date Author Description of Changes
|| 10/06/2004 1:51PM raverma Created
*=======================================================================*/
procedure processPayoff(p_api_version IN NUMBER
,p_init_msg_list IN VARCHAR2
,p_loan_id in number
,p_payoff_date in date
,p_cash_receipt_ids in LNS_PAYOFF_PUB.CASH_RECEIPT_TBL
,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_loan_header_rec LNS_LOAN_HEADER_PUB.loan_header_rec_type; -- to update the loan header
select receipt_number, currency_code
from ar_cash_receipts
where cash_receipt_id = p_cash_receipt_id;
select OBJECT_VERSION_NUMBER
from lns_loan_headers
where loan_id = p_loan_id;
select
head.Loan_currency
,nvl(head.exchange_rate, 1)
,head.exchange_date
,head.exchange_rate_type
,head.current_phase
from LNS_LOAN_HEADERS head
where head.loan_id = p_loan_id and
head.loan_status in ('ACTIVE', 'DELINQUENT', 'DEFAULT');
select rap.receivable_application_id
,rap.cash_receipt_id
,rap.amount_applied -- this is in loan / transacation currency
,trx.customer_trx_id
,trx.payment_schedule_id
,rac.receipt_number
,rac.currency_code
,rac.exchange_rate
,rac.exchange_date
,rac.exchange_rate_type
,lam.interest_trx_id -- get this in order to create new interest document
,lam.principal_trx_id
,lam.fee_trx_id
from ar_receivable_applications rap
,ar_cash_receipts rac
,lns_amortization_scheds lam
,ar_payment_schedules trx
where rap.cash_receipt_id = rac.cash_receipt_id
and rap.applied_customer_trx_id = trx.customer_trx_id
and trx.customer_trx_id = lam.interest_trx_id
and lam.payment_number = LNS_BILLING_UTIL_PUB.LAST_PAYMENT_NUMBER(p_loan_id)
and lam.reversed_flag = 'N'
and lam.reamortization_amount is null
and lam.parent_amortization_id is null
and lam.loan_id = p_loan_id
and rap.display = 'Y'
and rap.status = 'APP';
select sched.fee_amount
,fees.fee_name
,sched.fee_installment
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';
SELECT sb.currency_code
FROM lns_system_options so,
gl_sets_of_books sb
WHERE sb.set_of_books_id = so.set_of_books_id;
select amount_due_original + l_payoff_tbl2(i).unbilled_amount
into l_new_interest
from ar_payment_schedules
where payment_schedule_id = l_app_pay_sched_id;
select interest_amount + l_payoff_tbl2(i).unbilled_amount
into l_new_interest
from lns_amortization_scheds
where loan_id = p_loan_id
and reamortization_amount is null
and parent_amortization_id is null
--and reversed_flag = 'N'
and payment_number = LNS_BILLING_UTIL_PUB.LAST_PAYMENT_NUMBER(p_loan_id);
select 'X'
into l_found
from ar_receivable_applications rap
where rap.cash_receipt_id = l_cash_receipt_ids(j).cash_receipt_id
and rap.applied_payment_schedule_id = l_invoices_tbl(k).payment_schedule_id
and rap.display = 'Y'
and rap.status = 'APP';
select total_principal_balance into l_final_balance
from LNS_PAYMENTS_SUMMARY_V
where loan_id = p_loan_id;
LNS_LOAN_HEADER_PUB.UPDATE_LOAN(P_OBJECT_VERSION_NUMBER => l_object_version,
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);
FND_MESSAGE.SET_NAME('LNS', 'LNS_UPDATE_HEADER_ERROR');
update lns_fee_schedules
set billed_flag = 'Y'
where loan_id = p_loan_id
and active_flag = 'Y'
and billed_flag = 'N'
and object_version_number = object_version_number + 1;
update lns_fee_assignments
set end_date_active = p_payoff_date
where loan_id = p_loan_id
and (end_date_active is null OR end_date_active > p_payoff_date);
LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ' - header update status: ' || l_return_status);
select ps.customer_trx_id
,payment_schedule_id
,payment_number
,trx_number
,tty.name
,amount_due_remaining
,am.due_date
,lns_utility_pub.get_lookup_meaning('PAYMENT_APPLICATION_TYPE', decode(ps.customer_trx_id, principal_trx_Id, 'PRIN', interest_trx_id, 'INT', fee_trx_id, 'FEE')) line_type
from lns_amortization_scheds am
,ar_payment_schedules ps
,ra_cust_trx_types tty
where (am.principal_trx_id = ps.customer_trx_id OR
am.interest_trx_id = ps.customer_trx_id OR
am.fee_trx_id = ps.customer_trx_id) and
ps.cust_trx_type_id = tty.cust_trx_type_id and
ps.amount_due_remaining > 0 and
am.reamortization_amount is null and
am.reversed_flag <> 'Y' and
am.loan_id = p_loan_id
order by payment_number, line_type;
select loan_number
,LNS_BILLING_UTIL_PUB.LAST_PAYMENT_NUMBER(p_loan_id)
from lns_loan_headers
where loan_id = p_loan_id;
select interest_amount
from lns_amortization_scheds
where loan_id = p_loan_id
and reversed_flag <> 'Y'
and reamortization_amount is null
and payment_number = LNS_BILLING_UTIL_PUB.LAST_PAYMENT_NUMBER(p_loan_id);
select sched.fee_amount
,fees.fee_name
,sched.fee_installment
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';
select meaning into l_credit_name
from ar_lookups
where lookup_type = 'INV/CM'
and lookup_code = 'CM';
select ABS(AMOUNT_DUE_REMAINING)
from ar_payment_schedules
where CASH_RECEIPT_ID = P_RECEIPT_ID
and status = 'OP'
and class = 'PMT';
select due_date
from ar_payment_schedules
where payment_schedule_id = p_payment_schedule_id;
select
DAYS_TOGL_AFTER_DUE_DATE
into g_day_togl_after_dd
FROM LNS_SYSTEM_OPTIONS
WHERE ORG_ID = MO_GLOBAL.GET_CURRENT_ORG_ID() ;
select CR.CURRENCY_CODE RECEIPT_CURRENCY_CODE,
(select SUM(DECODE(app.status,'UNAPP',NVL(app.amount_applied,0),0)) amt from AR_RECEIVABLE_APPLICATIONS app where app.cash_receipt_id = CR.CASH_RECEIPT_ID) RECEIPT_CURRENCY_AMOUNT,
CR.EXCHANGE_RATE_TYPE,
CR.EXCHANGE_DATE,
CR.EXCHANGE_RATE
from AR_CASH_RECEIPTS CR
where CR.cash_receipt_id = p_recpt_id;
select loan.loan_currency LOAN_CURRENCY_CODE
from LNS_LOAN_HEADERS_ALL loan
where loan.loan_id = p_loanId;
select lnh.exchange_rate_type
,lnh.exchange_rate
,lnh.exchange_date
,lnh.loan_currency
from lns_loan_headers lnh
where loan_id = p_loan_id;
SELECT sb.currency_code
FROM lns_system_options so,
gl_sets_of_books sb
WHERE sb.set_of_books_id = so.set_of_books_id;