[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select count(1) into g_sys_opt_num from LNS_SYSTEM_OPTIONS where ORG_ID = g_org_id;
select BATCH_SOURCE_ID,
DAYS_TOBILL_BEFORE_DUE_DATE,
TRX_TYPE_ID,
DAYS_TOGL_AFTER_DUE_DATE,
COMBINE_INT_PRIN_FLAG,
INTEREST_TRX_TYPE_ID,
FEE_TRX_TYPE_ID,
RECEIVABLES_TRX_ID,
ADJUST_USSGL_TRX_CODE,
FORGIVENESS_REC_TRX_ID
into g_batch_source_id,
g_days_to_bill_before_dd,
g_trx_type_id,
g_day_togl_after_dd,
g_multiple_lines,
g_int_trx_type_id,
g_fee_trx_type_id,
g_receivables_trx_id,
g_USSGL_TRANSACTION_CODE,
g_forgiveness_rec_trx_id
FROM LNS_SYSTEM_OPTIONS
WHERE ORG_ID = g_org_id;
select
head.OBJECT_VERSION_NUMBER,
head.loan_id,
head.loan_number,
head.LAST_PAYMENT_NUMBER,
head.LAST_AMORTIZATION_ID,
nvl(head.CURRENT_PHASE, 'TERM')
from
LNS_LOAN_HEADERS head
where
head.loan_id = P_LOAN_ID;
select max(PAYMENT_NUMBER)
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 max(AMORTIZATION_SCHEDULE_ID)
from LNS_AMORTIZATION_SCHEDS
where LOAN_ID = P_LOAN_ID
and (REVERSED_FLAG is null or REVERSED_FLAG = 'N')
and nvl(PHASE, 'TERM') = nvl(P_PHASE, 'TERM')
and PAYMENT_NUMBER =
nvl((select max(PAYMENT_NUMBER)
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')), 0);
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, 'Successfully update LNS_LOAN_HEADERS_ALL');
select
head.OBJECT_VERSION_NUMBER,
head.loan_id,
head.loan_number
from
LNS_PAY_SUM_V sum,
LNS_LOAN_HEADERS head
where
head.loan_id = nvl(P_LOAN_ID, head.loan_id) and
head.loan_id = sum.loan_id and
--karamach --Bug5295446
--head.loan_status = 'ACTIVE' and
head.loan_status NOT IN ('INCOMPLETE','DELETED','REJECTED','PENDING','APPROVED','PAIDOFF') and
head.last_payment_number is not null and
head.last_amortization_id is not null and
sum.total_principal_balance <= 0;
select nvl(sum(total_remaining_amount),0)
from LNS_AM_SCHEDS_V
where loan_id = P_LOAN_ID and
reversed_code = 'N';
update lns_fee_assignments
set end_date_active = P_PAYOFF_DATE
where loan_id = l_loan_id
and (end_date_active is null OR end_date_active > P_PAYOFF_DATE);
LogMessage(FND_LOG.LEVEL_STATEMENT, 'lns_fee_assignments is updated');
update lns_fee_schedules
set billed_flag = 'Y'
where loan_id = l_loan_id
and active_flag = 'Y'
and billed_flag = 'N'
and object_version_number = object_version_number + 1;
LogMessage(FND_LOG.LEVEL_STATEMENT, 'lns_fee_schedules is updated');
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, 'Successfully update LNS_LOAN_HEADERS_ALL');
select
head.OBJECT_VERSION_NUMBER,
head.loan_id,
head.loan_number
from
LNS_LOAN_HEADERS head
where
head.loan_status = 'PAIDOFF' and
((select nvl(sum(total_remaining_amount),0)
from LNS_AM_SCHEDS_V
where loan_id = head.loan_id and
reversed_code = 'N') > 0);
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, 'Successfully updated LNS_LOAN_HEADERS_ALL');
SELECT
FVL.DESCRIPTION
FROM LNS_SYSTEM_OPTIONS LSO
,gl_sets_of_books SB
,FND_ID_FLEX_STRUCTURES_VL STR
,FND_ID_FLEX_SEGMENTS_VL SEG
,fnd_flex_value_sets FVS
,FND_FLEX_VALUES_VL FVL
,FND_SEGMENT_ATTRIBUTE_VALUES SAV
,GL_CODE_COMBINATIONS GL
WHERE STR.APPLICATION_ID = 101 -- GENERAL LEDGER
and lso.set_of_books_id = sb.set_of_books_id
and sb.chart_of_accounts_id = STR.id_flex_num
and gl.chart_of_accounts_id = sb.chart_of_accounts_id
and SEG.FLEX_VALUE_SET_ID = FVS.FLEX_VALUE_SET_ID
and FVS.FLEX_VALUE_SET_ID = FVL.FLEX_VALUE_SET_ID
and str.id_flex_num = SEG.ID_FLEX_NUM
and STR.id_flex_num = sav.id_flex_num
and STR.ID_FLEX_CODE='GL#'
and seg.id_flex_code ='GL#'
and STR.enabled_flag = 'Y'
and LSO.org_id = P_ORG_ID
and gl.code_combination_id = P_CC_ID
and fvl.flex_value = (decode(sav.application_column_name,
'SEGMENT1', GL.segment1,
'SEGMENT2', GL.segment2,
'SEGMENT3', GL.segment3,
'SEGMENT4', GL.segment4,
'SEGMENT5', GL.segment5))
and (('' IS NULL) OR (structured_hierarchy_level IN
(SELECT h.hierarchy_id
FROM fnd_flex_hierarchies_vl h
WHERE h.flex_value_set_id = FVL.flex_value_set_id
AND h.hierarchy_name like '')))
and exists
( SELECT 1
FROM FND_SEGMENT_ATTRIBUTE_TYPES T
WHERE T.APPLICATION_ID = SAV.APPLICATION_ID
AND T.ID_FLEX_CODE = SAV.ID_FLEX_CODE
AND T.SEGMENT_ATTRIBUTE_TYPE = SAV.SEGMENT_ATTRIBUTE_TYPE
AND GLOBAL_FLAG = 'N'
and SAV.ID_FLEX_CODE='GL#'
and SAV.APPLICATION_ID=101
and sav.segment_attribute_type = 'GL_BALANCING'
and attribute_value = 'Y'
);
select COPY_DOC_NUMBER_FLAG,
AUTO_TRX_NUMBERING_FLAG
FROM RA_BATCH_SOURCES
where BATCH_SOURCE_ID = P_SOURCE_ID;
select LNS_TRX_NUMBER_S.NEXTVAL
from dual;
select meaning
from LNS_LOOKUPS
where lookup_type = 'PAYMENT_APPLICATION_TYPE' and
lookup_code = P_LOOKUP_CODE;
select CODE_COMBINATION_ID,
DISTRIBUTION_PERCENT,
USSGL_TRANSACTION_CODE
from lns_distributions
where LOAN_ID = P_LOAN_ID and
account_type = 'DR' and
account_name = 'PRINCIPAL_RECEIVABLE' and
line_type = 'PRIN';
select CODE_COMBINATION_ID,
DISTRIBUTION_PERCENT,
USSGL_TRANSACTION_CODE
from lns_distributions
where LOAN_ID = P_LOAN_ID and
account_type = 'DR' and
account_name = 'INTEREST_RECEIVABLE' and
line_type = 'INT';
select CODE_COMBINATION_ID,
DISTRIBUTION_PERCENT,
USSGL_TRANSACTION_CODE
from lns_distributions
where LOAN_ID = P_LOAN_ID and
account_type = 'DR' and
account_name = 'FEE_RECEIVABLE' and
line_type = 'FEE';
select CODE_COMBINATION_ID,
DISTRIBUTION_PERCENT,
USSGL_TRANSACTION_CODE
from lns_distributions
where LOAN_ID = P_LOAN_ID and
account_type = 'CR' and
account_name = 'LOAN_RECEIVABLE' and
line_type = 'PRIN';
select CODE_COMBINATION_ID,
DISTRIBUTION_PERCENT,
USSGL_TRANSACTION_CODE
from lns_distributions
where LOAN_ID = P_LOAN_ID and
account_type = 'CR' and
account_name = 'INTEREST_INCOME' and
line_type = 'INT';
select CODE_COMBINATION_ID,
DISTRIBUTION_PERCENT,
USSGL_TRANSACTION_CODE
from lns_distributions
where LOAN_ID = P_LOAN_ID and
account_type = 'CR' and
account_name = 'FEE_INCOME' and
line_type = 'FEE' and
fee_id = P_FEE_ID;
SELECT trx_header_id, trx_line_id, error_message, invalid_value
from ar_trx_errors_gt;
select
trx.customer_trx_id,
trx.trx_number,
lines.customer_trx_line_id,
psa.payment_schedule_id,
psa.due_date,
lines.extended_amount,
lines.INTERFACE_LINE_ATTRIBUTE5,
lines.INTERFACE_LINE_ATTRIBUTE6
from RA_CUSTOMER_TRX trx,
RA_CUSTOMER_TRX_LINES lines,
ar_payment_schedules_all psa
where
trx.batch_id = P_BATCH_ID
and trx.customer_trx_id = lines.customer_trx_id
and trx.customer_trx_id = psa.customer_trx_id(+) -- outer join is for case when invoice is created incomplete
and lines.line_type = 'LINE'
ORDER BY lines.customer_trx_line_id;
select REFERENCE_TYPE_ID
from LNS_LOAN_HEADERS
where loan_id = P_LOAN_ID;
select site_use_id
from hz_cust_site_uses
where cust_acct_site_id = P_SITE_ID
and site_use_code = 'BILL_TO'
and status = 'A';
LogMessage(FND_LOG.LEVEL_STATEMENT, 'Fetching inserted ar invoice details...');
/* inserting new record into LNS_AMORTIZATION_LINES */
LogMessage(FND_LOG.LEVEL_STATEMENT, 'Inserting new record into LNS_AMORTIZATION_LINES w following values:');
LNS_AMORTIZATION_LINES_PKG.Insert_Row(
X_AMORTIZATION_LINE_ID => l_amortization_line_id
,P_AMORTIZATION_SCHEDULE_ID => P_LOAN_REC.NEXT_AMORTIZATION_ID
,P_LOAN_ID => P_LOAN_REC.LOAN_ID
,P_LINE_TYPE => l_line_type
,P_AMOUNT => l_amount
,P_CUST_TRX_ID => l_customer_trx_id
,P_CUST_TRX_LINE_ID => l_customer_trx_line_id
,P_FEE_ID => l_new_lines_tbl(l_Count3).LINE_REF_ID
,P_OBJECT_VERSION_NUMBER => 1
,P_FEE_SCHEDULE_ID => l_new_lines_tbl(l_Count3).FEE_SCHEDULE_ID);
LogMessage(FND_LOG.LEVEL_STATEMENT, 'Successfully inserted record into LNS_AMORTIZATION_LINES');
/* Update interest and fee trxs with principal_trx_id as RELATED_CUSTOMER_TRX_ID */
if g_multiple_lines = 'N' and l_principal_trx_id is not null then
if l_interest_trx_id is not null then
update RA_CUSTOMER_TRX_ALL set RELATED_CUSTOMER_TRX_ID = l_principal_trx_id where CUSTOMER_TRX_ID = l_interest_trx_id;
update RA_CUSTOMER_TRX_ALL set RELATED_CUSTOMER_TRX_ID = l_principal_trx_id where CUSTOMER_TRX_ID = l_fee_trx_id;
/* Update amortization table with new principal, interest and fee ids */
LogMessage(FND_LOG.LEVEL_STATEMENT, 'Updating LNS_AMORTIZATION_SCHEDS with new trx ids:');
LNS_AMORTIZATION_SCHEDS_PKG.Update_Row(
P_AMORTIZATION_SCHEDULE_ID => P_LOAN_REC.NEXT_AMORTIZATION_ID
,P_PRINCIPAL_TRX_ID => l_principal_trx_id
,P_INTEREST_TRX_ID => l_interest_trx_id
,P_FEE_TRX_ID => l_fee_trx_id);
LogMessage(FND_LOG.LEVEL_STATEMENT, 'Update successfull!');
select name, CREDIT_MEMO_BATCH_SOURCE_ID
from RA_BATCH_SOURCES
where batch_source_id = P_BATCH_SOURCE_ID;
select name
from RA_BATCH_SOURCES
where batch_source_id = P_BATCH_SOURCE_ID;
l_cm_line_tbl.delete;
select ABS(AMOUNT_DUE_REMAINING)
from ar_payment_schedules
where CASH_RECEIPT_ID = P_RECEIPT_ID
and status = 'OP'
and class = 'PMT';
select party.party_name,
loan.loan_number,
loan.LOAN_CURRENCY,
to_char(P_PRIN_AMOUNT_DUE, FND_CURRENCY.SAFE_GET_FORMAT_MASK(loan.LOAN_CURRENCY,50)),
to_char(P_INT_AMOUNT_DUE, FND_CURRENCY.SAFE_GET_FORMAT_MASK(loan.LOAN_CURRENCY,50)),
to_char(P_FEE_AMOUNT_DUE, FND_CURRENCY.SAFE_GET_FORMAT_MASK(loan.LOAN_CURRENCY,50)),
to_char((P_PRIN_AMOUNT_DUE + P_INT_AMOUNT_DUE + P_FEE_AMOUNT_DUE), FND_CURRENCY.SAFE_GET_FORMAT_MASK(loan.LOAN_CURRENCY,50))
from lns_loan_headers loan,
hz_parties party
where party.party_id = loan.PRIMARY_BORROWER_ID and
loan.loan_id = P_LOAN_ID;
select party_name from hz_parties party where party_id = P_BORROWER_ID;
select loan_number from lns_loan_headers where loan_id = P_LOAN_ID;
select name
from hr_all_organization_units_tl
where ORGANIZATION_ID = P_ORG_ID and
language(+) = userenv('LANG');
select FND_CURRENCY.SAFE_GET_FORMAT_MASK(loan.LOAN_CURRENCY,50) mask
from lns_loan_headers_all loan
where loan.loan_id = pLoanId;
qry_string := 'SELECT loan.loan_id, ' ||
'payment_summary.next_payment_amortization_id, ' ||
'fnd_date.date_to_chardate(sysdate) bill_date, ' ||
'loan.loan_number, ' ||
'loan.LOAN_DESCRIPTION, ' ||
'(am.PAYMENT_NUMBER || decode(am.PARENT_AMORTIZATION_ID, null, '''', ''M'')) next_payment_number, ' ||
'fnd_date.date_to_chardate(am.DUE_DATE) next_payment_due_date, ' ||
'to_char(am.PRINCIPAL_AMOUNT, :CURRENCY_FORMAT1) f_next_payment_principal_due, ' ||
'to_char(am.INTEREST_AMOUNT, :CURRENCY_FORMAT2) f_next_payment_interest_due, ' ||
'to_char(am.FEE_AMOUNT, :CURRENCY_FORMAT3) f_next_payment_fee_due, ' ||
'to_char((am.PRINCIPAL_AMOUNT+am.INTEREST_AMOUNT+am.FEE_AMOUNT), :CURRENCY_FORMAT4) f_next_payment_total_due, ' ||
'am.PRINCIPAL_AMOUNT next_payment_principal_due, ' ||
'am.INTEREST_AMOUNT next_payment_interest_due, ' ||
'am.FEE_AMOUNT next_payment_fee_due, ' ||
'(am.PRINCIPAL_AMOUNT+am.INTEREST_AMOUNT+am.FEE_AMOUNT) next_payment_total_due, ' ||
'party.party_name Borrower_Name, ' ||
'loc.address1 || '' '' || loc.city || '' '' || loc.state || '' '' || loc.postal_code || '' '' || terr.TERRITORY_SHORT_NAME Primary_Address, ' ||
'party.jgzz_fiscal_code tax_id, ' ||
'org.name operating_unit, ' ||
'loan_type.loan_type_name loan_type, ' ||
'loan_class.meaning loan_class, ' ||
'loan.loan_term || '' '' || periodlkup.meaning loan_term, ' ||
'to_char(LNS_FIN_UTILS.getActiveRate(loan.loan_id)) current_interest_rate, ' ||
'fnd_date.date_to_chardate(loan.loan_maturity_date) loan_maturity_date, ' ||
'to_char(payment_summary.total_principal_balance, :CURRENCY_FORMAT5) f_remaining_balance_amount, ' ||
'to_char(payment_summary.principal_paid_todate, :CURRENCY_FORMAT6) f_principal_paid_todate, ' ||
'to_char(payment_summary.interest_paid_todate, :CURRENCY_FORMAT7) f_interest_paid_todate, ' ||
'to_char(payment_summary.fee_paid_todate, :CURRENCY_FORMAT8) f_fee_paid_todate, ' ||
'to_char(payment_summary.total_prin_paid_todate, :CURRENCY_FORMAT9) f_total_prin_paid_todate, ' ||
'to_char(payment_summary.principal_paid_ytd, :CURRENCY_FORMAT10) f_principal_paid_ytd, ' ||
'to_char(payment_summary.interest_paid_ytd, :CURRENCY_FORMAT11) f_interest_paid_ytd, ' ||
'to_char(payment_summary.fee_paid_ytd, :CURRENCY_FORMAT12) f_fee_paid_ytd, ' ||
'to_char(payment_summary.principal_overdue, :CURRENCY_FORMAT13) f_principal_overdue, ' ||
'to_char(payment_summary.interest_overdue, :CURRENCY_FORMAT14) f_interest_overdue, ' ||
'to_char(payment_summary.fee_overdue, :CURRENCY_FORMAT15) f_fee_overdue, ' ||
'to_char(payment_summary.total_overdue, :CURRENCY_FORMAT16) f_total_overdue, ' ||
'payment_summary.total_principal_balance remaining_balance_amount, ' ||
'payment_summary.principal_paid_todate principal_paid_todate, ' ||
'payment_summary.interest_paid_todate interest_paid_todate, ' ||
'payment_summary.fee_paid_todate fee_paid_todate, ' ||
'payment_summary.total_prin_paid_todate total_prin_paid_todate, ' ||
'payment_summary.principal_paid_ytd principal_paid_ytd, ' ||
'payment_summary.interest_paid_ytd interest_paid_ytd, ' ||
'payment_summary.fee_paid_ytd fee_paid_ytd, ' ||
'payment_summary.principal_overdue principal_overdue, ' ||
'payment_summary.interest_overdue interest_overdue, ' ||
'payment_summary.fee_overdue fee_overdue, ' ||
'payment_summary.total_overdue total_overdue, ' ||
'payment_summary.number_overdue_bills number_of_overdue_payments, ' ||
'fnd_date.date_to_chardate(payment_summary.last_overdue_date) last_overdue_date, ' ||
'to_char(payment_summary.last_payment_amount, :CURRENCY_FORMAT17) f_last_payment_amount, ' ||
'payment_summary.last_payment_amount last_payment_amount, ' ||
'fnd_date.date_to_chardate(payment_summary.last_payment_date) last_payment_date, ' ||
'(LNS_FIN_UTILS.getNumberInstallments(loan.loan_id, nvl(loan.current_phase, ''TERM'')) - payment_summary.next_payment_number) remaining_number_of_payments, ' ||
'contact_person.party_name PRIMARY_LOAN_CONTACT, ' ||
'loan.LOAN_CURRENCY LOAN_CURRENCY_CODE, ' ||
'curr.name LOAN_CURRENCY_MEANING, ' ||
'loan_subtype.meaning loan_subtype, ' ||
'nvl(loan.current_phase, ''TERM'') phase, ' ||
'rate_type.meaning rate_type, ' ||
'int_rate_hdr.INTEREST_RATE_NAME index_rate, ' ||
'rate_freq.meaning floating_frequency, ' ||
'pay_freq.meaning payment_frequency, ' ||
'fnd_date.date_to_chardate(terms.LOCK_IN_DATE) lock_date, ' ||
'fnd_date.date_to_chardate(terms.LOCK_TO_DATE) lock_exp_date, ' ||
'terms.CEILING_RATE Ceiling_Rate, ' ||
'terms.FLOOR_RATE floor_rate, ' ||
'loan.open_to_term_flag open_to_term, ' ||
'open_to_term.meaning open_to_term_flag, ' ||
'product.loan_product_name loan_product, ' ||
'fnd_date.date_to_chardate(rate_sched1.INDEX_DATE) open_index_date, ' ||
'rate_sched1.CURRENT_INTEREST_RATE open_interest_rate, ' ||
'fnd_date.date_to_chardate(rate_sched2.INDEX_DATE) term_index_date, ' ||
'rate_sched2.CURRENT_INTEREST_RATE term_interest_rate, ' ||
'loan.MULTIPLE_FUNDING_FLAG, ' ||
-- adding disbursement schedule
'CURSOR ' ||
'(select head.DISB_HEADER_ID, ' ||
'head.DISBURSEMENT_NUMBER, ' ||
'fnd_date.date_to_chardate(head.TARGET_DATE) TARGET_DATE, ' ||
'fnd_date.date_to_chardate(head.PAYMENT_REQUEST_DATE) PAYMENT_REQUEST_DATE, ' ||
'head.HEADER_PERCENT, ' ||
'to_char(head.HEADER_AMOUNT, :CURRENCY_FORMAT18) f_header_amount, ' ||
'fnd_date.date_to_chardate((select max(DISBURSEMENT_DATE) from lns_disb_lines where DISB_HEADER_ID = head.DISB_HEADER_ID)) DISBURSEMENT_DATE, ' ||
'fund_status.meaning status, ' ||
'fund_act.meaning activity_name ' ||
'from lns_disb_headers head, ' ||
'lns_lookups fund_status, ' ||
'lns_lookups fund_act ' ||
'where head.loan_id = loan.loan_id and ' ||
'fund_status.lookup_type(+) = ''FUNDING_STATUS'' and ' ||
'fund_status.lookup_code(+) = head.STATUS and ' ||
'fund_act.lookup_type(+) = ''DISB_ACTIVITY'' and ' ||
'fund_act.lookup_code(+) = head.ACTIVITY_CODE) ' ||
'AS Disbursement_Schedule, ' ||
-- adding payment history
'CURSOR ' ||
'(select amortization_schedule_id, ' ||
'loan_id, ' ||
'payment_number, ' ||
'fnd_date.date_to_chardate(creation_date) bill_date, ' ||
'fnd_date.date_to_chardate(last_applied_date) paid_date, ' ||
'fnd_date.date_to_chardate(due_date) due_date, ' ||
'to_char(principal_amount, :CURRENCY_FORMAT19) f_principal_amount, ' ||
'to_char(interest_amount, :CURRENCY_FORMAT20) f_interest_amount, ' ||
'to_char(fee_amount, :CURRENCY_FORMAT21) f_fee_amount, ' ||
'to_char(total_billed_amount, :CURRENCY_FORMAT22) f_total_billed_amount, ' ||
'to_char((PRINCIPAL_PAID + INTEREST_PAID + FEE_PAID), :CURRENCY_FORMAT23) f_total_payment_amount, ' ||
'principal_amount principal_amount, ' ||
'interest_amount interest_amount, ' ||
'fee_amount fee_amount, ' ||
'total_billed_amount total_billed_amount, ' ||
'(PRINCIPAL_PAID + INTEREST_PAID + FEE_PAID) total_payment_amount ' ||
'from lns_am_scheds_v payment_history ' ||
'where payment_history.loan_id = loan.loan_id and ' ||
'nvl(payment_history.PHASE, ''TERM'') = nvl(loan.CURRENT_PHASE, ''TERM'') and ' ||
'payment_history.reversed_code = ''N'' and ' ||
'(payment_history.payment_number between (payment_summary.next_payment_number - 2) and payment_summary.next_payment_number) and ' ||
'payment_history.AMORTIZATION_SCHEDULE_ID < payment_summary.next_payment_amortization_id ' ||
'order by payment_history.AMORTIZATION_SCHEDULE_ID) ' ||
'AS Recent_Payment_History ' ||
'FROM lns_loan_headers_all loan, ' ||
'hz_parties party, ' ||
'fnd_territories_tl terr, ' ||
'hr_all_organization_units_tl org, ' ||
'LNS_PAYMENTS_SUMMARY_V payment_summary, ' ||
'lns_amortization_scheds am, ' ||
'hz_locations loc, ' ||
'hz_cust_acct_sites_all acct_site, ' ||
'hz_party_sites site, ' ||
'fnd_currencies_tl curr, ' ||
'hz_parties contact_person, ' ||
'lns_loan_types_vl loan_type, ' ||
'lns_lookups loan_class, ' ||
'lns_lookups periodlkup, ' ||
'lns_lookups loan_subtype, ' ||
'lns_terms terms, ' ||
'lns_int_rate_headers int_rate_hdr, ' ||
'lns_lookups rate_type, ' ||
'lns_lookups rate_freq, ' ||
'lns_lookups pay_freq, ' ||
'fnd_lookups open_to_term, ' ||
'lns_loan_products_vl product, ' ||
'lns_rate_schedules rate_sched1, ' ||
'lns_rate_schedules rate_sched2 ' ||
'WHERE party.party_id = loan.primary_borrower_id and ' ||
'org.organization_id = loan.org_id and ' ||
'org.language = userenv(''LANG'') and ' ||
'acct_site.cust_acct_site_id = loan.bill_to_acct_site_id and ' ||
'acct_site.org_id = loan.org_id and ' ||
'site.party_site_id = acct_site.party_site_id and ' ||
'site.location_id = loc.location_id and ' ||
'loc.country = terr.TERRITORY_CODE and ' ||
'terr.language = userenv(''LANG'') and ' ||
'loan.LOAN_CURRENCY = curr.currency_code and ' ||
'curr.language = userenv(''LANG'') and ' ||
'loan.contact_pers_party_id = contact_person.party_id(+) and ' ||
'loan_type.loan_type_id = loan.loan_type_id AND ' ||
'loan_class.lookup_type = ''LOAN_CLASS'' AND ' ||
'loan_class.lookup_code = loan.loan_class_code AND ' ||
'periodlkup.lookup_type = ''PERIOD'' and ' ||
'periodlkup.lookup_code = loan.loan_term_period and ' ||
'loan_subtype.lookup_type = ''LOAN_SUBTYPE'' and ' ||
'loan_subtype.lookup_code = loan.loan_subtype and ' ||
'loan.loan_id = payment_summary.loan_id and ' ||
'loan.loan_id = terms.loan_id and ' ||
'rate_type.lookup_type = ''RATE_TYPE'' and ' ||
'rate_type.lookup_code = terms.RATE_TYPE and ' ||
'terms.INDEX_RATE_ID = int_rate_hdr.INTEREST_RATE_ID(+) and ' ||
'rate_freq.lookup_type(+) = ''FREQUENCY'' and ' ||
'rate_freq.lookup_code(+) = terms.RATE_CHANGE_FREQUENCY and ' ||
'pay_freq.lookup_type = ''FREQUENCY'' and ' ||
'pay_freq.lookup_code = terms.LOAN_PAYMENT_FREQUENCY and ' ||
'open_to_term.lookup_type = ''YES_NO'' and ' ||
'open_to_term.lookup_code = nvl(loan.open_to_term_flag, ''N'') and ' ||
'loan.product_id = product.LOAN_PRODUCT_ID(+) and ' ||
'rate_sched1.term_id(+) = terms.term_id and ' ||
'rate_sched1.PHASE(+) = ''OPEN'' and ' ||
'rate_sched2.term_id(+) = terms.term_id and ' ||
'rate_sched2.PHASE(+) = ''TERM'' and ' ||
'loan.loan_id = am.loan_id and ' ||
'am.AMORTIZATION_SCHEDULE_ID = payment_summary.next_payment_amortization_id and ' ||
'loan.loan_id = :LOAN_ID';
/* Update amortization table */
LogMessage(FND_LOG.LEVEL_STATEMENT, 'Updating record in LNS_AMORTIZATION_SCHEDS...') ;
LNS_AMORTIZATION_SCHEDS_PKG.Update_Statement(
P_AMORTIZATION_SCHEDULE_ID => p_amortization_schedule_id
,P_STATEMENT_XML => result);
LogMessage(FND_LOG.LEVEL_STATEMENT, 'Update successfull!');
select max(head.funded_amount) - nvl(sum(am.PRINCIPAL_AMOUNT), 0)
from
LNS_AMORTIZATION_SCHEDS am,
lns_loan_headers head
where
head.loan_id = P_LOAN_ID
and head.loan_id = am.LOAN_ID
and (am.REVERSED_FLAG is null or am.REVERSED_FLAG = 'N')
and nvl(am.PHASE, 'TERM') = nvl(head.CURRENT_PHASE, 'TERM');
select nvl(count(1),0)
from lns_fee_assignments
where begin_installment_number = 0
and end_installment_number = 0
and end_date_active is null
and billing_option = 'ORIGINATION'
and (loan_id = P_LOAN_ID
-- Bug#6961250, The loanId is NULL if for the disbFees in lns_fee_assignments.
-- So to check any existed 0th installment fees for a loan, also check existed fees for disb_header_id.
OR disb_header_id in (select disb_header_id from lns_disb_headers where loan_id = P_LOAN_ID)
);
select STATEMENT_XML
from LNS_LOAN_HEADERS 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;
select OBJECT_VERSION_NUMBER
from LNS_LOAN_HEADERS
where LOAN_ID = P_LOAN_ID;
select nvl(floating_flag, 'N')
from lns_rate_schedules rs
,lns_terms t
where t.loan_id = p_loan_id
and t.term_id = rs.term_id
and rs.end_date_active is null
and rs.phase = p_phase
and p_installment between rs.begin_installment_number and rs.end_installment_number;
LogMessage(FND_LOG.LEVEL_STATEMENT, 'copying new rate ID for insert into Amortization Schedueles' || l_amortization_rec.RATE_ID);
UPDATE LNS_FEE_SCHEDULES
SET
BILLED_FLAG = 'Y',
last_update_date = LNS_UTILITY_PUB.LAST_UPDATE_DATE,
last_updated_by = LNS_UTILITY_PUB.LAST_UPDATED_BY,
last_update_login = LNS_UTILITY_PUB.LAST_UPDATE_LOGIN
WHERE
FEE_SCHEDULE_ID = l_fee_tbl(l_Count).fee_schedule_id;
LogMessage(FND_LOG.LEVEL_STATEMENT, 'Successfully update LNS_FEE_SCHEDULES');
/* inserting new record into LNS_AMORTIZATION_SCHEDS */
l_prin_balance := null;
LogMessage(FND_LOG.LEVEL_STATEMENT, 'Inserting new record into LNS_AMORTIZATION_SCHEDS w following values:');
LNS_AMORTIZATION_SCHEDS_PKG.Insert_Row(
X_AMORTIZATION_SCHEDULE_ID => P_LOAN_REC.NEXT_AMORTIZATION_ID
,P_LOAN_ID => P_LOAN_REC.LOAN_ID
,P_PAYMENT_NUMBER => P_LOAN_REC.NEXT_PAYMENT_NUMBER
,P_DUE_DATE => P_LOAN_REC.NEXT_PAYMENT_DUE_DATE
,P_LATE_DATE => P_LOAN_REC.NEXT_PAYMENT_LATE_DATE
,P_PRINCIPAL_AMOUNT => P_LOAN_REC.NEXT_PRINCIPAL_AMOUNT
,P_INTEREST_AMOUNT => P_LOAN_REC.NEXT_INTEREST_AMOUNT
,P_FEE_AMOUNT => P_LOAN_REC.NEXT_FEE_AMOUNT
,P_REVERSED_FLAG => 'N'
,P_RATE_ID => P_LOAN_REC.RATE_ID
,P_OBJECT_VERSION_NUMBER => 1
,P_PRINCIPAL_BALANCE => l_prin_balance
,P_PHASE => P_LOAN_REC.CURRENT_PHASE);
LogMessage(FND_LOG.LEVEL_STATEMENT, 'Successfully inserted record into LNS_AMORTIZATION_SCHEDS');
LNS_LOAN_HEADER_PUB.UPDATE_LOAN(P_OBJECT_VERSION_NUMBER => P_LOAN_REC.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);
LogMessage(FND_LOG.LEVEL_STATEMENT, 'Successfully update LNS_LOAN_HEADERS_ALL');
select TERM_ID,
OBJECT_VERSION_NUMBER
from LNS_TERMS
where LOAN_ID = P_LOAN_ID;
select nvl(CURRENT_PHASE, 'TERM')
from LNS_LOAN_HEADERS
where LOAN_ID = P_LOAN_ID;
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);
LogMessage(FND_LOG.LEVEL_STATEMENT, 'Successfully update LNS_LOAN_HEADERS_ALL');
/* getting terms version for future update */
open term_version_cur(P_LOAN_NEXT_DD_REC.LOAN_ID);
LNS_TERMS_PUB.update_term(P_OBJECT_VERSION_NUMBER => l_version_number,
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, 'Successfully update LNS_TERMS');
select
head.LOAN_ID,
head.LOAN_NUMBER,
head.LOAN_DESCRIPTION,
head.OBJECT_VERSION_NUMBER,
head.FUNDED_AMOUNT,
head.BILL_TO_ACCT_SITE_ID,
decode(head.CURRENT_PHASE, 'OPEN', term.OPEN_FIRST_PAYMENT_DATE, term.FIRST_PAYMENT_DATE),
decode(head.CURRENT_PHASE, 'OPEN', term.OPEN_NEXT_PAYMENT_DATE, term.NEXT_PAYMENT_DUE_DATE),
nvl(head.BILLED_FLAG, 'N'),
head.LOAN_CURRENCY,
head.CUST_ACCOUNT_ID,
decode(head.CURRENT_PHASE, 'OPEN', 'N', head.CUSTOM_PAYMENTS_FLAG),
decode(head.CURRENT_PHASE, 'OPEN', term.OPEN_PAYMENT_FREQUENCY, term.LOAN_PAYMENT_FREQUENCY),
term.NUMBER_GRACE_DAYS,
term.PAYMENT_APPLICATION_ORDER,
head.EXCHANGE_RATE_TYPE,
head.EXCHANGE_DATE,
head.EXCHANGE_RATE,
head.ORG_ID,
head.LEGAL_ENTITY_ID,
nvl(head.CURRENT_PHASE, 'TERM'),
nvl(head.FORGIVENESS_FLAG, 'N'),
nvl(head.FORGIVENESS_PERCENT, 0)
from LNS_LOAN_HEADERS head,
LNS_TERMS term
where head.LOAN_STATUS in ('ACTIVE', 'DEFAULT', 'DELINQUENT')
and head.loan_id = term.loan_id
and LNS_BILLING_UTIL_PUB.LAST_PAYMENT_NUMBER(head.LOAN_ID) < LNS_FIN_UTILS.getNumberInstallments(head.LOAN_ID, nvl(head.current_phase, 'TERM'))
and (head.BILLED_FLAG is null or head.BILLED_FLAG = 'N')
and head.PRIMARY_BORROWER_ID = nvl(P_BORROWER_ID, head.PRIMARY_BORROWER_ID)
and head.LOAN_ID = nvl(P_LOAN_ID, head.LOAN_ID)
and (trunc(decode(head.CURRENT_PHASE, 'OPEN', term.OPEN_NEXT_PAYMENT_DATE, term.NEXT_PAYMENT_DUE_DATE)) - trunc(sysdate))
>= nvl(P_FROM_DAYS_TO_DD, trunc(decode(head.CURRENT_PHASE, 'OPEN', term.OPEN_NEXT_PAYMENT_DATE, term.NEXT_PAYMENT_DUE_DATE)) - trunc(sysdate))
and (trunc(decode(head.CURRENT_PHASE, 'OPEN', term.OPEN_NEXT_PAYMENT_DATE, term.NEXT_PAYMENT_DUE_DATE)) - trunc(sysdate))
<= nvl(P_TO_DAYS_TO_DD, trunc(decode(head.CURRENT_PHASE, 'OPEN', term.OPEN_NEXT_PAYMENT_DATE, term.NEXT_PAYMENT_DUE_DATE)) - trunc(sysdate))
ORDER BY head.LOAN_ID;
/* bill all selected loans */
FOR l_Count1 IN 1..l_loans_to_bill_tbl.COUNT LOOP
LogMessage(FND_LOG.LEVEL_PROCEDURE, ' ');
select
head.LOAN_ID,
head.LOAN_NUMBER,
head.OBJECT_VERSION_NUMBER,
LNS_BILLING_UTIL_PUB.LAST_PAYMENT_NUMBER(head.LOAN_ID) + 1,
head.CUSTOM_PAYMENTS_FLAG
from LNS_LOAN_HEADERS head,
LNS_TERMS term
where head.LOAN_STATUS in ('ACTIVE', 'DEFAULT', 'DELINQUENT')
and head.loan_id = term.loan_id
and (head.BILLED_FLAG = 'Y' or head.BILLED_FLAG is null) -- scherkas; fix for bug 5687852
| 01-20-2006 raverma delete row from rate_scheds for variable rate loan
| 06-16-2006 karamach Removed code that deleted the rate sch row from lns_rate_schedules
| and added fix in LNS_FINANCIAL_B.pls floatingRatePostProcessing procedure to update existing row to fix bug5331888
| 07-31-2006 karamach Added code to update active_flag to N in lns_fee_schedules for unbilled manual fees to fix bug5397345
*=======================================================================*/
PROCEDURE REVERSE_LAST_AMORTIZATION(
P_API_VERSION IN NUMBER,
P_INIT_MSG_LIST IN VARCHAR2,
P_COMMIT IN VARCHAR2,
P_VALIDATION_LEVEL IN NUMBER,
P_LOAN_ID IN NUMBER,
P_REBILL_FLAG IN VARCHAR2,
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) := 'REVERSE_LAST_AMORTIZATION';
select PAYMENT_NUMBER,
DUE_DATE,
AMORTIZATION_SCHEDULE_ID,
RATE_ID
from LNS_AMORTIZATION_SCHEDS
where LOAN_ID = P_LOAN_ID
and AMORTIZATION_SCHEDULE_ID = LNS_BILLING_UTIL_PUB.LAST_AMORTIZATION_SCHED(LOAN_ID);
select
trx.trx_number,
trx.customer_trx_id,
psa.payment_schedule_id,
lines.CUST_TRX_LINE_ID,
lines.LINE_TYPE,
lines.AMOUNT,
(psa.AMOUNT_DUE_ORIGINAL - psa.AMOUNT_DUE_REMAINING),
trx.org_id
from
RA_CUSTOMER_TRX_ALL trx,
lns_amortization_lines lines,
ar_payment_schedules psa
where
trx.customer_trx_id = lines.CUST_TRX_ID and
trx.customer_trx_id = psa.customer_trx_id and
lines.LOAN_ID = P_LOAN_ID and
lines.AMORTIZATION_SCHEDULE_ID = P_AMORTIZATION_ID;
select
LOAN_NUMBER,
OBJECT_VERSION_NUMBER,
CUSTOM_PAYMENTS_FLAG
from LNS_LOAN_HEADERS
where LOAN_ID = P_LOAN_ID;
select rate_type
from LNS_TERMS
where LOAN_ID = P_LOAN_ID;
/* getting object version and custom flag for further loan update */
open loan_version_cur(P_LOAN_ID);
delete from LNS_AMORTIZATION_SCHEDS
where loan_id = P_LOAN_ID and
REAMORTIZE_FROM_INSTALLMENT = l_last_payment_number;
LogMessage(FND_LOG.LEVEL_STATEMENT, 'Deleted!');
/* Update amortization table */
LogMessage(FND_LOG.LEVEL_STATEMENT, 'Updating record in LNS_AMORTIZATION_SCHEDS...') ;
LNS_AMORTIZATION_SCHEDS_PKG.Update_Row(
P_AMORTIZATION_SCHEDULE_ID => l_amortization_schedule_id
,P_REVERSED_FLAG => 'Y'
,P_REVERSED_DATE => sysdate);
LogMessage(FND_LOG.LEVEL_STATEMENT, 'Update successfull!');
LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'Failed to insert missing custom schedule row.');
UPDATE LNS_FEE_SCHEDULES SET ACTIVE_FLAG = 'N', LAST_UPDATE_DATE = sysdate, LAST_UPDATED_BY = lns_utility_pub.last_updated_by, LAST_UPDATE_LOGIN = lns_utility_pub.last_update_login, OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER+1
WHERE LOAN_ID = P_LOAN_ID AND BILLED_FLAG = 'N' AND FEE_INSTALLMENT = l_LAST_PAYMENT_NUMBER;
select PAYMENT_NUMBER,
DUE_DATE,
AMORTIZATION_SCHEDULE_ID
from LNS_AMORTIZATION_SCHEDS
where LOAN_ID = P_LOAN_ID
and AMORTIZATION_SCHEDULE_ID = LNS_BILLING_UTIL_PUB.LAST_AMORTIZATION_SCHED(LOAN_ID);
select
trx.trx_number,
trx.customer_trx_id,
psa.payment_schedule_id,
lines.CUST_TRX_LINE_ID,
lines.LINE_TYPE,
lines.AMOUNT,
(psa.AMOUNT_DUE_ORIGINAL - psa.AMOUNT_DUE_REMAINING),
trx.org_id
from
RA_CUSTOMER_TRX_ALL trx,
lns_amortization_lines lines,
ar_payment_schedules psa
where
trx.customer_trx_id = lines.CUST_TRX_ID and
trx.customer_trx_id = psa.customer_trx_id and
lines.LOAN_ID = P_LOAN_ID and
lines.AMORTIZATION_SCHEDULE_ID = P_AMORTIZATION_ID and
lines.LINE_TYPE = p_line_type;
select
LOAN_NUMBER,
OBJECT_VERSION_NUMBER,
CUSTOM_PAYMENTS_FLAG
from LNS_LOAN_HEADERS
where LOAN_ID = P_LOAN_ID;
/* getting object version and custom flag for further loan update */
open loan_version_cur(P_LOAN_ID);
select
head.LOAN_ID,
head.LOAN_NUMBER,
head.LOAN_DESCRIPTION,
head.LOAN_CURRENCY,
head.CUST_ACCOUNT_ID,
head.BILL_TO_ACCT_SITE_ID,
term.NUMBER_GRACE_DAYS,
term.PAYMENT_APPLICATION_ORDER,
decode(am.AMORTIZATION_SCHEDULE_ID, null, -1, am.AMORTIZATION_SCHEDULE_ID) AMORTIZATION_SCHEDULE_ID,
head.EXCHANGE_RATE_TYPE,
head.EXCHANGE_DATE,
head.EXCHANGE_RATE,
head.ORG_ID,
head.LEGAL_ENTITY_ID,
head.FUNDED_AMOUNT,
nvl(head.CURRENT_PHASE, 'TERM'),
nvl(head.FORGIVENESS_FLAG, 'N'),
nvl(head.FORGIVENESS_PERCENT, 0)
from LNS_LOAN_HEADERS head,
LNS_TERMS term,
LNS_AMORTIZATION_SCHEDS am
where head.LOAN_STATUS in ('ACTIVE', 'DEFAULT', 'DELINQUENT')
and head.LOAN_ID = P_LOAN_ID
and head.loan_id = term.loan_id
and head.loan_id = am.loan_id(+)
and am.PARENT_AMORTIZATION_ID(+) is null
and am.PAYMENT_NUMBER(+) = P_PAYMENT_NUMBER
and (am.REVERSED_FLAG is null or am.REVERSED_FLAG = 'N')
and nvl(am.PHASE(+), 'TERM') = nvl(head.CURRENT_PHASE, 'TERM');
/* Inserting new record into LNS_AMORTIZATION_SCHEDS */
l_prin_balance := null;
LogMessage(FND_LOG.LEVEL_STATEMENT, 'Inserting new record into LNS_AMORTIZATION_SCHEDS w following values:');
LNS_AMORTIZATION_SCHEDS_PKG.Insert_Row(
X_AMORTIZATION_SCHEDULE_ID => l_loan_rec.NEXT_AMORTIZATION_ID
,P_LOAN_ID => l_loan_rec.LOAN_ID
,P_PAYMENT_NUMBER => l_loan_rec.NEXT_PAYMENT_NUMBER
,P_DUE_DATE => l_loan_rec.NEXT_PAYMENT_DUE_DATE
,P_LATE_DATE => l_loan_rec.NEXT_PAYMENT_LATE_DATE
,P_PRINCIPAL_AMOUNT => l_loan_rec.NEXT_PRINCIPAL_AMOUNT
,P_INTEREST_AMOUNT => l_loan_rec.NEXT_INTEREST_AMOUNT
,P_FEE_AMOUNT => l_loan_rec.NEXT_FEE_AMOUNT
,P_REVERSED_FLAG => 'N'
,P_OBJECT_VERSION_NUMBER => 1
,P_PARENT_AMORTIZATION_ID => l_loan_rec.PARENT_AMORTIZATION_ID
,P_PRINCIPAL_BALANCE => l_prin_balance
,P_PHASE => l_loan_rec.CURRENT_PHASE);
LogMessage(FND_LOG.LEVEL_STATEMENT, 'Successfully inserted record into LNS_AMORTIZATION_SCHEDS');
l_bill_lines_tbl.delete;
select LOAN_ID,
PAYMENT_NUMBER,
DUE_DATE,
PARENT_AMORTIZATION_ID,
REVERSED_CODE
from LNS_AM_SCHEDS_V
where AMORTIZATION_SCHEDULE_ID = P_AMORTIZATION_ID;
select
trx.trx_number,
trx.customer_trx_id,
psa.payment_schedule_id,
lines.CUST_TRX_LINE_ID,
lines.LINE_TYPE,
lines.AMOUNT,
(psa.AMOUNT_DUE_ORIGINAL - psa.AMOUNT_DUE_REMAINING),
trx.org_id
from
RA_CUSTOMER_TRX_ALL trx,
lns_amortization_lines lines,
ar_payment_schedules psa
where
trx.customer_trx_id = lines.CUST_TRX_ID and
trx.customer_trx_id = psa.customer_trx_id and
lines.LOAN_ID = P_LOAN_ID and
lines.AMORTIZATION_SCHEDULE_ID = P_AMORTIZATION_ID;
LNS_AMORTIZATION_SCHEDS_PKG.Update_Row(
P_AMORTIZATION_SCHEDULE_ID => P_AMORTIZATION_ID
,P_REVERSED_FLAG => 'Y'
,P_REVERSED_DATE => sysdate);
LogMessage(FND_LOG.LEVEL_STATEMENT, 'Update successfull!');
SELECT so.set_of_books_id
FROM lns_system_options so
,gl_sets_of_books sb
WHERE sb.set_of_books_id = so.set_of_books_id;
select
loan.loan_number,
loan.LOAN_DESCRIPTION,
lines.REQUESTED_AMOUNT,
lines.reference_number,
lines.REC_ADJUSTMENT_NUMBER,
nvl(lines.payment_schedule_id, psa.payment_schedule_id),
psa.AMOUNT_DUE_REMAINING,
lines.LOAN_LINE_ID,
psa.TRX_DATE,
psa.GL_DATE,
trx.org_id,
trx.legal_entity_id,
lines.REC_ADJUSTMENT_ID,
nvl(lines.installment_number, 1)
from LNS_LOAN_HEADERS loan,
RA_CUSTOMER_TRX trx,
ar_payment_schedules psa,
lns_loan_lines lines
where loan.loan_id = P_LOAN_ID and
loan.loan_id = lines.loan_id and
lines.reference_type = 'RECEIVABLE' and
lines.REFERENCE_ID = trx.CUSTOMER_TRX_ID and
lines.REFERENCE_NUMBER = trx.trx_number and
lines.end_date is null and
trx.CUSTOMER_TRX_ID = psa.CUSTOMER_TRX_ID and
nvl(lines.installment_number, 1) = psa.terms_sequence_number
order by lines.LOAN_LINE_ID;
LNS_LOAN_LINE_PUB.UPDATE_LINE_ADJUSTMENT_NUMBER(
p_init_msg_list => FND_API.G_FALSE
,p_loan_id => P_LOAN_ID
,p_loan_line_id => l_line_id
,p_rec_adjustment_number => l_adj_number
,p_rec_adjustment_id => l_adj_id
,P_PAYMENT_SCHEDULE_ID => l_payment_schedule_id
,P_INSTALLMENT_NUMBER => l_installment_number
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data);
LogMessage(FND_LOG.LEVEL_STATEMENT, 'Successfully updated LNS_LOAN_LINES');
select
LOAN_NUMBER, nvl(CURRENT_PHASE, 'TERM')
from LNS_LOAN_HEADERS
where LOAN_ID = P_LOAN_ID;
UPDATE LNS_FEE_SCHEDULES
SET
BILLED_FLAG = 'Y',
last_update_date = LNS_UTILITY_PUB.LAST_UPDATE_DATE,
last_updated_by = LNS_UTILITY_PUB.LAST_UPDATED_BY,
last_update_login = LNS_UTILITY_PUB.LAST_UPDATE_LOGIN
WHERE
FEE_SCHEDULE_ID = l_fee_tbl(l_Count).fee_schedule_id;
LogMessage(FND_LOG.LEVEL_STATEMENT, 'Successfully update LNS_FEE_SCHEDULES');
select
loan.loan_number,
loan.LOAN_DESCRIPTION,
nvl(loan.FORGIVENESS_FLAG, 'N'),
nvl(loan.FORGIVENESS_PERCENT, 0),
am.PAYMENT_NUMBER,
am.PRINCIPAL_TRX_ID,
psa.payment_schedule_id,
nvl(psa.AMOUNT_DUE_ORIGINAL, 0),
psa.TRX_DATE,
psa.GL_DATE,
nvl(loan.CURRENT_PHASE, 'TERM'),
psa.org_id,
loan.LOAN_CURRENCY
from LNS_LOAN_HEADERS loan,
lns_amortization_scheds am,
ar_payment_schedules psa
where
loan.loan_id = P_LOAN_ID and
loan.LAST_AMORTIZATION_ID = am.AMORTIZATION_SCHEDULE_ID and
loan.loan_id = am.loan_id and
am.PRINCIPAL_TRX_ID = psa.CUSTOMER_TRX_ID(+);