[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'
and sched.phase = p_phase
and sched.fee_installment <= decode(p_installment, -1, 1, (p_installment+1))
and fees.fee_category <> 'RECUR';
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 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
,ps.TRX_DATE
,ps.GL_DATE
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, amount_due_remaining;
select loan_number
,LNS_BILLING_UTIL_PUB.LAST_PAYMENT_NUMBER(p_loan_id)
,current_phase
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'
and sched.phase = p_phase
and sched.fee_installment <= decode(p_installment, -1, 1, (p_installment+1))
and fees.fee_category <> 'RECUR';
select nvl(DAYS_TOGL_AFTER_DUE_DATE, 0)
into l_day_togl_after_dd
FROM LNS_SYSTEM_OPTIONS
WHERE ORG_ID = MO_GLOBAL.GET_CURRENT_ORG_ID();
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;
select curr.precision
from fnd_currencies curr,
lns_loan_headers_all loan
where loan.loan_id = P_LOAN_ID and
curr.currency_code = loan.loan_currency;
select loan_number
,LNS_BILLING_UTIL_PUB.LAST_PAYMENT_NUMBER(p_loan_id)
,current_phase
,LOAN_CLASS_CODE
,FUNDED_AMOUNT
from lns_loan_headers
where loan_id = 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'
and sched.phase = p_phase
and sched.fee_installment <= decode(p_installment, -1, 1, (p_installment+1))
and fees.fee_category <> 'RECUR';
select lines.LOAN_LINE_ID,
lines.REFERENCE_ID,
lines.PAYMENT_SCHEDULE_ID,
lines.REFERENCE_NUMBER,
trx_type.name || ' - ' || trx_type_lkup.meaning,
lines.REQUESTED_AMOUNT,
lines.INSTALLMENT_NUMBER
from lns_loan_lines lines,
RA_CUSTOMER_TRX_ALL cust_trx,
RA_CUST_TRX_TYPES trx_type,
ar_lookups trx_type_lkup
where lines.status = 'APPROVED'
and lines.REFERENCE_ID <> -1
and lines.LOAN_ID = P_LOAN_ID
and cust_trx.customer_trx_id = lines.reference_id
and cust_trx.cust_trx_type_id = trx_type.CUST_TRX_TYPE_ID
and trx_type_lkup.lookup_type = 'INV/CM'
and trx_type_lkup.lookup_code = trx_type.type
order by lines.LOAN_LINE_ID;
select ps.customer_trx_id
,payment_schedule_id
,payment_number
,trx_number
,tty.name
,amount_due_remaining
,decode(ps.customer_trx_id, principal_trx_Id, 'PRIN', interest_trx_id, 'INT', fee_trx_id, 'FEE')
,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
,ps.due_date
,ps.TRX_DATE
,ps.GL_DATE
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 nvl(DAYS_TOGL_AFTER_DUE_DATE, 0), SET_OF_BOOKS_ID
into l_day_togl_after_dd, l_sob
FROM LNS_SYSTEM_OPTIONS
WHERE ORG_ID = MO_GLOBAL.GET_CURRENT_ORG_ID();
select count(1)
from LNS_LOAN_CANCELLATIONS
where loan_id = P_LOAN_ID and
STATUS = 'PENDING';
select STATUS, CANCELLATION_DATE
from LNS_LOAN_CANCELLATIONS
where LOAN_CANCELLATION_ID = P_LOAN_CANCELLATION_ID;
select LOAN_STATUS
from lns_loan_headers_all
where loan_id = P_LOAN_ID;
select LOAN_STATUS
from lns_loan_headers_all
where loan_id = P_LOAN_ID;
select LOAN_ID,
STATUS,
CANCELLATION_DATE
from LNS_LOAN_CANCELLATIONS
where LOAN_CANCELLATION_ID = P_LOAN_CANCELLATION_ID;
select PURPOSE_CODE
,BILLED_FLAG
,AMOUNT_TO_PROCESS
from LNS_LOAN_CANCEL_DETAILS
where LOAN_CANCELLATION_ID = P_LOAN_CANCELLATION_ID;
l_loan_header_rec LNS_LOAN_HEADER_PUB.loan_header_rec_type; -- to update the loan header
select curr.precision
from fnd_currencies curr,
lns_loan_headers_all loan
where loan.loan_id = P_LOAN_ID and
curr.currency_code = loan.loan_currency;
select OBJECT_VERSION_NUMBER
from LNS_LOAN_HEADERS
where LOAN_ID = P_LOAN_ID;
if p_action = 'INSERT' then
validateLoanCancelRequest(p_loan_id => P_LOAN_ID
,p_cancellation_date => p_cancellation_date
,p_LOAN_CANCELLATION_ID => null
,p_invoices_tbl => p_cancel_details_tbl);
if p_action = 'INSERT' then
if p_LOAN_CANCELLATION_ID is null then
select LNS_LOAN_CANCELLATIONS_S.NEXTVAL into p_LOAN_CANCELLATION_ID from dual;
LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Inserting into LNS_LOAN_CANCELLATIONS...');
insert into LNS_LOAN_CANCELLATIONS
(LOAN_CANCELLATION_ID
,LOAN_ID
,DESCRIPTION
,STATUS
,CANCELLATION_DATE
,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_CANCELLATION_ID
,P_LOAN_ID
,l_description
,'PENDING'
,p_cancellation_date
,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_CANCELLATIONS set
DESCRIPTION = l_description
,OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1
,LAST_UPDATE_DATE = sysdate
,LAST_UPDATED_BY = LNS_UTILITY_PUB.LAST_UPDATED_BY
,LAST_UPDATE_LOGIN = LNS_UTILITY_PUB.LAST_UPDATE_LOGIN
where LOAN_CANCELLATION_ID = p_LOAN_CANCELLATION_ID;
if p_action = 'INSERT' then
if p_cancel_details_tbl(i).LOAN_CANCEL_DETAIL_ID is null then
select LNS_LOAN_CANCEL_DETAILS_S.NEXTVAL into l_LOAN_CANCEL_DETAIL_ID from dual;
LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Inserting into LNS_LOAN_CANCEL_DETAILS...');
insert into LNS_LOAN_CANCEL_DETAILS
(LOAN_CANCEL_DETAIL_ID
,LOAN_CANCELLATION_ID
,LOAN_ID
,INVOICE_NUMBER
,INSTALLMENT_NUMBER
,TRANSACTION_TYPE
,PURPOSE_CODE
,PURPOSE_DESC
,AMOUNT_DUE
,PERCENT_TO_PROCESS
,AMOUNT_TO_PROCESS
,DUE_DATE
,TRX_DATE
,GL_DATE
,BILLED_FLAG
,ACTION
,LOAN_LINE_ID
,PAYMENT_SCHEDULE_ID
,CUST_TRX_ID
,ADJUSTMENT_NUMBER
,ADJUSTMENT_ID
,OBJECT_VERSION_NUMBER
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN)
VALUES
(p_cancel_details_tbl(i).LOAN_CANCEL_DETAIL_ID
,p_LOAN_CANCELLATION_ID
,P_LOAN_ID
,p_cancel_details_tbl(i).INVOICE_NUMBER
,p_cancel_details_tbl(i).INSTALLMENT_NUMBER
,p_cancel_details_tbl(i).TRANSACTION_TYPE
,p_cancel_details_tbl(i).PURPOSE_CODE
,p_cancel_details_tbl(i).PURPOSE
,p_cancel_details_tbl(i).REMAINING_AMOUNT
,p_cancel_details_tbl(i).PERCENT_TO_PROCESS
,p_cancel_details_tbl(i).AMOUNT_TO_PROCESS
,p_cancel_details_tbl(i).DUE_DATE
,p_cancel_details_tbl(i).TRX_DATE
,p_cancel_details_tbl(i).GL_DATE
,p_cancel_details_tbl(i).BILLED_FLAG
,p_cancel_details_tbl(i).ACTION
,p_cancel_details_tbl(i).LOAN_LINE_ID
,p_cancel_details_tbl(i).PAYMENT_SCHEDULE_ID
,p_cancel_details_tbl(i).CUST_TRX_ID
,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_CANCEL_DETAILS set
PERCENT_TO_PROCESS = p_cancel_details_tbl(i).PERCENT_TO_PROCESS
,AMOUNT_TO_PROCESS = p_cancel_details_tbl(i).AMOUNT_TO_PROCESS
,ACTION = p_cancel_details_tbl(i).ACTION
,OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1
,LAST_UPDATE_DATE = sysdate
,LAST_UPDATED_BY = LNS_UTILITY_PUB.LAST_UPDATED_BY
,LAST_UPDATE_LOGIN = LNS_UTILITY_PUB.LAST_UPDATE_LOGIN
where LOAN_CANCEL_DETAIL_ID = p_cancel_details_tbl(i).LOAN_CANCEL_DETAIL_ID;
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);
LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Successfully update LNS_LOAN_HEADERS_ALL');
l_loan_header_rec LNS_LOAN_HEADER_PUB.loan_header_rec_type; -- to update the loan header
select loan.loan_id,
loan.loan_number,
loan.org_id,
sys.CANCELLATION_REC_TRX_ID,
loan.LOAN_CLASS_CODE,
nvl(loan.DISABLE_BILLING_FLAG, 'N')
from lns_loan_headers loan,
LNS_SYSTEM_OPTIONS sys,
LNS_LOAN_CANCELLATIONS cncl
where cncl.LOAN_CANCELLATION_ID = P_LOAN_CANCELLATION_ID and
cncl.LOAN_ID = loan.LOAN_ID and
loan.org_id = sys.org_id;
select
LOAN_CANCEL_DETAIL_ID
,PURPOSE_CODE
,PURPOSE_DESC
,PERCENT_TO_PROCESS
,AMOUNT_TO_PROCESS
,DUE_DATE
,TRX_DATE
,GL_DATE
,BILLED_FLAG
,ACTION
,LOAN_LINE_ID
,PAYMENT_SCHEDULE_ID
,CUST_TRX_ID
from LNS_LOAN_CANCEL_DETAILS
where LOAN_CANCELLATION_ID = P_LOAN_CANCELLATION_ID;
select OBJECT_VERSION_NUMBER
from LNS_LOAN_HEADERS
where LOAN_ID = P_LOAN_ID;
select ps.customer_trx_id
,ps.payment_schedule_id
,decode(ps.customer_trx_id, am.principal_trx_Id, 'PRIN', am.interest_trx_id, 'INT', am.fee_trx_id, 'FEE')
,ps.trx_date
,ps.gl_date
from lns_loan_headers loan
,lns_amortization_scheds am
,ar_payment_schedules ps
where loan.loan_id = P_LOAN_ID and
loan.LAST_AMORTIZATION_ID = am.AMORTIZATION_SCHEDULE_ID and
ps.customer_trx_id in (am.principal_trx_id, am.interest_trx_id, am.fee_trx_id);
update LNS_LOAN_LINES set
STATUS = 'REJECTED'
,LAST_UPDATE_DATE = sysdate
,LAST_UPDATED_BY = LNS_UTILITY_PUB.LAST_UPDATED_BY
,LAST_UPDATE_LOGIN = LNS_UTILITY_PUB.LAST_UPDATE_LOGIN
,APPR_REJECT_DATE = sysdate
,APPR_REJECT_BY = LNS_UTILITY_PUB.LAST_UPDATED_BY
where LOAN_ID = l_loan_id and
(STATUS is null or STATUS = 'PENDING');
update LNS_LOAN_CANCEL_DETAILS set
ADJUSTMENT_NUMBER = l_adj_number
,ADJUSTMENT_ID = l_adj_id
,OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1
,LAST_UPDATE_DATE = sysdate
,LAST_UPDATED_BY = LNS_UTILITY_PUB.LAST_UPDATED_BY
,LAST_UPDATE_LOGIN = LNS_UTILITY_PUB.LAST_UPDATE_LOGIN
where LOAN_CANCEL_DETAIL_ID = l_invoices_tbl(i).LOAN_CANCEL_DETAIL_ID;
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);
update LNS_LOAN_CANCEL_DETAILS set
CUST_TRX_ID = l_invoices_tbl(i).CUST_TRX_ID
,PAYMENT_SCHEDULE_ID = l_invoices_tbl(i).PAYMENT_SCHEDULE_ID
,TRX_DATE = l_invoices_tbl(i).TRX_DATE
,GL_DATE = l_invoices_tbl(i).GL_DATE
,OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1
,LAST_UPDATE_DATE = sysdate
,LAST_UPDATED_BY = LNS_UTILITY_PUB.LAST_UPDATED_BY
,LAST_UPDATE_LOGIN = LNS_UTILITY_PUB.LAST_UPDATE_LOGIN
where LOAN_CANCEL_DETAIL_ID = l_invoices_tbl(i).LOAN_CANCEL_DETAIL_ID;
update LNS_LOAN_CANCELLATIONS set
STATUS = 'APPROVED'
,APPR_REJECT_DATE = sysdate
,APPR_REJECT_BY = LNS_UTILITY_PUB.USER_ID
,OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1
,LAST_UPDATE_DATE = sysdate
,LAST_UPDATED_BY = LNS_UTILITY_PUB.LAST_UPDATED_BY
,LAST_UPDATE_LOGIN = LNS_UTILITY_PUB.LAST_UPDATE_LOGIN
where LOAN_CANCELLATION_ID = p_LOAN_CANCELLATION_ID;
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);
LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Successfully update LNS_LOAN_HEADERS_ALL');
l_loan_header_rec LNS_LOAN_HEADER_PUB.loan_header_rec_type; -- to update the loan header
select loan.LOAN_ID, loan.OBJECT_VERSION_NUMBER
from LNS_LOAN_HEADERS loan, LNS_LOAN_CANCELLATIONS cncl
where cncl.LOAN_CANCELLATION_ID = p_LOAN_CANCELLATION_ID and
cncl.LOAN_ID = loan.LOAN_ID;
update LNS_LOAN_CANCELLATIONS set
STATUS = 'REJECTED'
,APPR_REJECT_DATE = sysdate
,APPR_REJECT_BY = LNS_UTILITY_PUB.USER_ID
,OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1
,LAST_UPDATE_DATE = sysdate
,LAST_UPDATED_BY = LNS_UTILITY_PUB.LAST_UPDATED_BY
,LAST_UPDATE_LOGIN = LNS_UTILITY_PUB.LAST_UPDATE_LOGIN
where LOAN_CANCELLATION_ID = p_LOAN_CANCELLATION_ID;
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);
LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Successfully update LNS_LOAN_HEADERS_ALL');
select
spl.LOAN_ID
,spl.DESCRIPTION
,spl.STATUS
,spl.PRODUCT_ID
,spl.ORG_ID
,spl.LOAN_NUMBER
,spl.LEGAL_ENTITY_ID
,spl.LOAN_CLASS_CODE
,spl.LOAN_TYPE_ID
,spl.LOAN_PURPOSE_CODE
,spl.LOAN_DESCRIPTION
,spl.LOAN_APPLICATION_DATE
,spl.LOAN_CURRENCY
,spl.EXCHANGE_RATE_TYPE
,spl.EXCHANGE_DATE
,spl.EXCHANGE_RATE
,spl.LOAN_ASSIGNED_TO
,spl.PRIMARY_BORROWER_ID
,spl.CUST_ACCOUNT_ID
,spl.BILL_TO_ACCT_SITE_ID
,spl.contact_rel_party_id
,spl.CONTACT_PERS_PARTY_ID
,spl.REFERENCE_TYPE_ID
,spl.REQUESTED_AMOUNT
,spl.LOAN_START_DATE
,spl.LOAN_TERM
,spl.LOAN_TERM_PERIOD
,spl.BALLOON_PAYMENT_TYPE
,spl.BALLOON_PAYMENT_AMOUNT
,spl.BALLOON_TERM
,spl.LOAN_SUBTYPE
,spl.CREDIT_REVIEW_FLAG
,spl.COLLATERAL_PERCENT
,spl.RATE_TYPE
,spl.INDEX_RATE_ID
,spl.PAYMENT_CALC_METHOD
,spl.DAY_COUNT_METHOD
,spl.AMORTIZATION_FREQUENCY
,spl.CALCULATION_METHOD
,spl.INTEREST_COMPOUNDING_FREQ
,spl.PRIN_PAYMENT_FREQUENCY
,spl.TERM_INDEX_DATE
,spl.INDEX_RATE
,spl.SPREAD
,spl.INT_RATE
,spl.CUSTOM_SCHEDULE
,party. party_name
from LNS_LOAN_SPLITS spl,
HZ_PARTIES party
where spl.LOAN_SPLIT_ID = p_LOAN_SPLIT_ID and
party.party_id = spl.PRIMARY_BORROWER_ID;
SELECT psa.payment_schedule_id,
am.AMORTIZATION_SCHEDULE_ID
FROM ar_payment_schedules_all psa,
LNS_LOAN_HEADERS_ALL loan,
lns_amortization_scheds am
WHERE loan.loan_id = am.loan_id and
am.AMORTIZATION_SCHEDULE_ID = loan.LAST_AMORTIZATION_ID and
am.PAYMENT_NUMBER = loan.LAST_PAYMENT_NUMBER and
loan.loan_id = P_LOAN_ID and
psa.CUSTOMER_TRX_ID = am.principal_trx_id;
select loan_number
from lns_loan_headers_all
where loan_id = P_LOAN_ID;
select TRX_TYPE_ID INTO l_LOAN_DTL_REC.trx_type_id
FROM LNS_SYSTEM_OPTIONS
WHERE ORG_ID = l_ORG_ID;
update LNS_LOAN_SPLITS set
NEW_AM_SCHED_ID = l_new_am_sched_id
,NEW_LOAN_ID = l_new_loan_id
,LOAN_NUMBER = l_LOAN_NUMBER
,STATUS = 'APPROVED'
,APPR_REJECT_DATE = sysdate
,APPR_REJECT_BY = LNS_UTILITY_PUB.USER_ID
,OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1
,LAST_UPDATE_DATE = sysdate
,LAST_UPDATED_BY = LNS_UTILITY_PUB.LAST_UPDATED_BY
,LAST_UPDATE_LOGIN = LNS_UTILITY_PUB.LAST_UPDATE_LOGIN
where LOAN_SPLIT_ID = p_LOAN_SPLIT_ID;
FND_MSG_PUB.DELETE_MSG(FND_MSG_PUB.COUNT_MSG);
update LNS_LOAN_SPLITS set
STATUS = 'REJECTED'
,APPR_REJECT_DATE = sysdate
,APPR_REJECT_BY = LNS_UTILITY_PUB.USER_ID
,OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1
,LAST_UPDATE_DATE = sysdate
,LAST_UPDATED_BY = LNS_UTILITY_PUB.LAST_UPDATED_BY
,LAST_UPDATE_LOGIN = LNS_UTILITY_PUB.LAST_UPDATE_LOGIN
where LOAN_SPLIT_ID = p_LOAN_SPLIT_ID;