[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,
SET_OF_BOOKS_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,
g_set_of_books_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
head.loan_status in ('ACTIVE', 'DEFAULT', 'DELINQUENT') and
head.last_payment_number is not null and
head.last_amortization_id is not null and
sum.total_principal_balance <= 0 and
(head.REQUESTED_AMOUNT + nvl(head.ADD_REQUESTED_AMOUNT, 0)) = sum.TOTAL_PRIN_PAID_TODATE;
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' and
activity is null;
select CODE_COMBINATION_ID,
DISTRIBUTION_PERCENT,
USSGL_TRANSACTION_CODE
from lns_distributions
where LOAN_ID = c_loan_id and
fee_id = c_fee_id and
-- The below code criteria retrieves though the disb_header_id is null for fees, which are not disbFees
nvl(disb_header_id, -1) = nvl(c_disb_hdr_id, -1) 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 = 'DR' and
account_name = 'FEE_RECEIVABLE' and
line_type = 'FEE';
select dist.CODE_COMBINATION_ID,
sum(dist.DISTRIBUTION_PERCENT),
dist.USSGL_TRANSACTION_CODE
from lns_distributions dist
where dist.LOAN_ID = P_LOAN_ID and
dist.account_type = 'CR' and
dist.account_name = 'LOAN_RECEIVABLE' and
dist.line_type = 'PRIN' and
nvl(dist.loan_line_id, -1) =
nvl((select max(loan_line_id)
from lns_loan_lines
where status = 'APPROVED'
and LOAN_ID = P_LOAN_ID
and original_flag = 'N'
and adjustment_date <= P_DATE), -1)
group by dist.CODE_COMBINATION_ID, dist.USSGL_TRANSACTION_CODE;
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' and
activity is null;
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 and
nvl(disb_header_id, -1) = nvl(C_DISB_HDR_ID, -1);
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, PRIMARY_SALESREP_ID
from hz_cust_site_uses
where cust_acct_site_id = P_SITE_ID
and site_use_code = 'BILL_TO'
and status = 'A';
SELECT disb_header_id
from lns_fee_schedules
where fee_schedule_id =c_fee_schd_id;
select LNS_AMORTIZATION_SCHEDS_S.NEXTVAL into P_LOAN_REC.NEXT_AMORTIZATION_ID from dual;
/* inserting record into LNS_AMORTIZATION_SCHEDS */
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_PARENT_AMORTIZATION_ID => P_LOAN_REC.PARENT_AMORTIZATION_ID
,P_PRINCIPAL_BALANCE => P_LOAN_REC.PRINCIPAL_BALANCE
,P_PHASE => P_LOAN_REC.CURRENT_PHASE
,P_FUNDED_AMOUNT => P_LOAN_REC.FUNDED_AMOUNT
,P_DEFERRED_INT_AMOUNT => P_LOAN_REC.DEFERRED_INT_AMOUNT
,P_CURR_CAP_INT_AMOUNT => P_LOAN_REC.CURR_CAP_INT_AMOUNT
,P_CAP_INT_AMOUNT => P_LOAN_REC.CAP_INT_AMOUNT);
LogMessage(FND_LOG.LEVEL_STATEMENT, 'Successfully inserted record into LNS_AMORTIZATION_SCHEDS');
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
,P_LINE_DETAILS => l_new_lines_tbl(l_Count3).LINE_DETAILS);
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 LOAN_PRODUCT_NAME from lns_loan_products_vl where LOAN_PRODUCT_ID = P_PRODUCT_ID;
select LOAN_TYPE_NAME from LNS_LOAN_TYPES_VL where LOAN_TYPE_ID = P_TYPE_ID;
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, ' ||
'PAY_SUM.next_payment_amortization_id, ' ||
'to_char(sysdate, ''YYYY-MM-DD'') bill_date, ' ||
'to_char(sysdate, ''YYYY-MM-DD'') report_generation_date, ' ||
'loan.loan_number, ' ||
'loan.LOAN_DESCRIPTION, ' ||
'am.PAYMENT_NUMBER next_payment_number, ' ||
'to_char(am.DUE_DATE, ''YYYY-MM-DD'') 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, ' ||
'to_char(loan.loan_maturity_date, ''YYYY-MM-DD'') loan_maturity_date, ' ||
'to_char(PAY_SUM.total_principal_balance, :CURRENCY_FORMAT5) f_remaining_balance_amount, ' ||
'to_char(PAY_SUM.principal_paid_todate, :CURRENCY_FORMAT6) f_principal_paid_todate, ' ||
'to_char(PAY_SUM.interest_paid_todate, :CURRENCY_FORMAT7) f_interest_paid_todate, ' ||
'to_char(PAY_SUM.fee_paid_todate, :CURRENCY_FORMAT8) f_fee_paid_todate, ' ||
'to_char(PAY_SUM.total_prin_paid_todate, :CURRENCY_FORMAT9) f_total_prin_paid_todate, ' ||
'to_char(PAY_SUM_YEARLY.principal_paid_ytd, :CURRENCY_FORMAT10) f_principal_paid_ytd, ' ||
'to_char(PAY_SUM_YEARLY.interest_paid_ytd, :CURRENCY_FORMAT11) f_interest_paid_ytd, ' ||
'to_char(PAY_SUM_YEARLY.fee_paid_ytd, :CURRENCY_FORMAT12) f_fee_paid_ytd, ' ||
'to_char(PAY_SUM_OVERDUE.principal_overdue, :CURRENCY_FORMAT13) f_principal_overdue, ' ||
'to_char(PAY_SUM_OVERDUE.interest_overdue, :CURRENCY_FORMAT14) f_interest_overdue, ' ||
'to_char(PAY_SUM_OVERDUE.fee_overdue, :CURRENCY_FORMAT15) f_fee_overdue, ' ||
'to_char(PAY_SUM_OVERDUE.total_overdue, :CURRENCY_FORMAT16) f_total_overdue, ' ||
'PAY_SUM.total_principal_balance remaining_balance_amount, ' ||
'PAY_SUM.principal_paid_todate principal_paid_todate, ' ||
'PAY_SUM.interest_paid_todate interest_paid_todate, ' ||
'PAY_SUM.fee_paid_todate fee_paid_todate, ' ||
'PAY_SUM.total_prin_paid_todate total_prin_paid_todate, ' ||
'PAY_SUM_YEARLY.principal_paid_ytd principal_paid_ytd, ' ||
'PAY_SUM_YEARLY.interest_paid_ytd interest_paid_ytd, ' ||
'PAY_SUM_YEARLY.fee_paid_ytd fee_paid_ytd, ' ||
'PAY_SUM_OVERDUE.principal_overdue principal_overdue, ' ||
'PAY_SUM_OVERDUE.interest_overdue interest_overdue, ' ||
'PAY_SUM_OVERDUE.fee_overdue fee_overdue, ' ||
'PAY_SUM_OVERDUE.total_overdue total_overdue, ' ||
'PAY_SUM_OVERDUE.number_overdue_bills number_of_overdue_payments, ' ||
'to_char(PAY_SUM_OVERDUE.last_overdue_date, ''YYYY-MM-DD'') last_overdue_date, ' ||
'to_char(PAY_SUM_OVERDUE.last_payment_amount, :CURRENCY_FORMAT17) f_last_payment_amount, ' ||
'PAY_SUM_OVERDUE.last_payment_amount last_payment_amount, ' ||
'to_char(PAY_SUM_OVERDUE.last_payment_date, ''YYYY-MM-DD'') last_payment_date, ' ||
'(LNS_FIN_UTILS.getNumberInstallments(loan.loan_id, nvl(loan.current_phase, ''TERM'')) - PAY_SUM.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, ' ||
'to_char(terms.LOCK_IN_DATE, ''YYYY-MM-DD'') lock_date, ' ||
'to_char(terms.LOCK_TO_DATE, ''YYYY-MM-DD'') 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, ' ||
'decode(nvl(loan.current_phase, ''TERM''), ''OPEN'',
to_char(rate_sched1.INDEX_DATE, ''YYYY-MM-DD''), null) open_index_date, ' ||
'decode(nvl(loan.current_phase, ''TERM''), ''OPEN'',
rate_sched1.CURRENT_INTEREST_RATE, null) open_interest_rate, ' ||
'decode(nvl(loan.current_phase, ''TERM''), ''OPEN'',
to_char(rate_sched2.INDEX_DATE, ''YYYY-MM-DD''),
to_char(rate_sched1.INDEX_DATE, ''YYYY-MM-DD'')) term_index_date, ' ||
'decode(nvl(loan.current_phase, ''TERM''), ''OPEN'',
rate_sched2.CURRENT_INTEREST_RATE,
rate_sched1.CURRENT_INTEREST_RATE) term_interest_rate, ' ||
'loan.MULTIPLE_FUNDING_FLAG, ' ||
-- adding disbursement schedule
'CURSOR ' ||
'(select head.DISB_HEADER_ID, ' ||
'head.DISBURSEMENT_NUMBER, ' ||
'to_char(head.TARGET_DATE, ''YYYY-MM-DD'') TARGET_DATE, ' ||
'to_char(head.PAYMENT_REQUEST_DATE, ''YYYY-MM-DD'') 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, ' ||
'to_char(creation_date, ''YYYY-MM-DD'') bill_date, ' ||
'to_char(last_applied_date, ''YYYY-MM-DD'') paid_date, ' ||
'to_char(due_date, ''YYYY-MM-DD'') 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 (PAY_SUM.next_payment_number - 2) and PAY_SUM.next_payment_number) and ' ||
'payment_history.AMORTIZATION_SCHEDULE_ID < PAY_SUM.next_payment_amortization_id ' ||
'order by payment_history.AMORTIZATION_SCHEDULE_ID) ' ||
'AS Recent_Payment_History ' ||
'FROM lns_loan_headers_all_vl loan, ' ||
'hz_parties party, ' ||
'fnd_territories_tl terr, ' ||
'hr_all_organization_units_tl org, ' ||
'LNS_PAY_SUM_V PAY_SUM, ' ||
'LNS_PAY_SUM_YEARLY_V PAY_SUM_YEARLY, ' ||
'LNS_PAY_SUM_OVERDUE_V PAY_SUM_OVERDUE, ' ||
'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_vl 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 = PAY_SUM.loan_id and ' ||
'loan.loan_id = PAY_SUM_YEARLY.loan_id and ' ||
'loan.loan_id = PAY_SUM_OVERDUE.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 = nvl(loan.current_phase, ''TERM'') and ' ||
'(decode(am.PAYMENT_NUMBER, 0, rate_sched1.begin_installment_number, -1) = 1 or ' ||
'am.PAYMENT_NUMBER between rate_sched1.begin_installment_number and rate_sched1.end_installment_number) and ' ||
'rate_sched1.end_date_active is null and ' ||
'rate_sched2.term_id = terms.term_id and ' ||
'rate_sched2.PHASE = ''TERM'' and ' ||
'rate_sched2.begin_installment_number = 1 and ' ||
'rate_sched2.end_date_active is null and ' ||
'loan.loan_id = am.loan_id and ' ||
'am.AMORTIZATION_SCHEDULE_ID = PAY_SUM.next_payment_amortization_id and ' ||
'nvl(am.phase, ''TERM'') = nvl(loan.current_phase, ''TERM'') 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 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'
-- Only for Term Pase, check the Event_conversion Fees
OR billing_option = decode(nvl(C_PHASE, 'TERM'), 'TERM','TERM_CONVERSION', null)
)
and loan_id = C_LOAN_ID
and phase = C_PHASE;
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;
select USER_PROFILE_OPTION_NAME into l_profile_name
from FND_PROFILE_OPTIONS_VL
where PROFILE_OPTION_NAME = 'LNS_CREATE_ZERO_INSTAL';
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');
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');
/* bill all selected loans */
LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'Billing loans...');
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),
nvl(head.DISABLE_BILLING_FLAG, 'N')
from LNS_LOAN_HEADERS_VL 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.LOAN_TYPE_ID = nvl(P_TYPE_ID, head.LOAN_TYPE_ID)
and nvl(head.PRODUCT_ID, -1) = nvl(P_PRODUCT_ID, nvl(head.PRODUCT_ID, -1)) -- fix for bug 14153250
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),
decode(am.AMORTIZATION_SCHEDULE_ID, null, head.FUNDED_AMOUNT, am.FUNDED_AMOUNT)
from LNS_LOAN_HEADERS_VL head,
LNS_TERMS term,
LNS_AMORTIZATION_SCHEDS am
where head.LOAN_ID = P_LOAN_ID
-- Bug#6830765 - Enable billing for all loan status, used in Application fee 'SubmitForApproval Fee'
-- and head.LOAN_STATUS in ('ACTIVE', 'DEFAULT', 'DELINQUENT')
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');
select OBJECT_VERSION_NUMBER
from LNS_LOAN_HEADERS
where LOAN_ID = P_LOAN_ID;
/* Inserting new record into LNS_AMORTIZATION_SCHEDS */
l_prin_balance := null;
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 lhdr.loan_status INTO l_lns_status FROM lns_loan_headers lhdr WHERE lhdr.loan_id = l_LOAN_ID;
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),
loan.LOAN_START_DATE
from LNS_LOAN_HEADERS_VL 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
,p_adjustment_date => l_apply_date
,p_original_flag => 'Y'
,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
nvl(loan.FORGIVENESS_FLAG, 'N'),
nvl(loan.FORGIVENESS_PERCENT, 0),
loan.LOAN_CURRENCY
from LNS_LOAN_HEADERS loan
where
loan.loan_id = P_LOAN_ID;
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_VL 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(+);
select loan_number, LOAN_CURRENCY, nvl(EXCHANGE_RATE, 1), nvl(CURRENT_PHASE, 'TERM')
from lns_loan_headers_all
where loan_id = p_loan_id;
select REFERENCE_TYPE_ID
from LNS_LOAN_HEADERS_ALL
where loan_id = P_LOAN_ID;
select NAME
from RA_CUST_TRX_TYPES_ALL
where cust_trx_type_id = p_trx_type_id;
select USER_PROFILE_OPTION_NAME into l_profile_name
from FND_PROFILE_OPTIONS_VL
where PROFILE_OPTION_NAME = 'LNS_CREATE_ZERO_INSTAL';
select
head.LOAN_ID,
head.LOAN_NUMBER,
head.LOAN_DESCRIPTION,
head.OBJECT_VERSION_NUMBER,
head.FUNDED_AMOUNT,
head.BILL_TO_ACCT_SITE_ID,
term.FIRST_PAYMENT_DATE,
term.NEXT_PAYMENT_DUE_DATE,
nvl(head.BILLED_FLAG, 'N'),
head.LOAN_CURRENCY,
head.CUST_ACCOUNT_ID,
head.CUSTOM_PAYMENTS_FLAG,
term.LOAN_PAYMENT_FREQUENCY,
term.NUMBER_GRACE_DAYS,
term.PAYMENT_APPLICATION_ORDER,
head.EXCHANGE_RATE_TYPE,
head.EXCHANGE_DATE,
head.EXCHANGE_RATE,
nvl(head.FORGIVENESS_FLAG, 'N'),
nvl(head.FORGIVENESS_PERCENT, 0),
nvl(head.CURRENT_PHASE,'TERM')
from LNS_LOAN_HEADERS_ALL_VL head,
LNS_TERMS term
where head.loan_id = term.loan_id
and head.LOAN_ID = P_LOAN_ID;
select loan.LAST_AMORTIZATION_ID
from LNS_LOAN_HEADERS_all loan
where loan.loan_id = P_LOAN_ID;
select PAYMENT_APPLICATION_ORDER
from lns_terms
where loan_id = P_LOAN_ID;
select
decode(P_INVOICE_TYPE, 'INT', am.INTEREST_TRX_ID, 'PRIN', am.principal_trx_id, 'FEE', am.FEE_TRX_ID),
psa.PAYMENT_SCHEDULE_ID,
psa.trx_number,
psa.TRX_DATE,
psa.due_date,
psa.gl_date,
psa.amount_due_remaining,
P_INVOICE_TYPE,
psa.invoice_currency_code,
nvl(loan.EXCHANGE_RATE, 1)
from
lns_amortization_scheds am,
lns_loan_headers loan,
ar_payment_schedules psa,
lns_lookups look
where
am.loan_id = P_LOAN_ID and
am.amortization_schedule_id = P_AMORTIZATION_SCHED_ID and
loan.loan_id = am.loan_id and
am.reversed_flag = 'N' and
psa.customer_trx_id = decode(P_INVOICE_TYPE, 'INT', am.INTEREST_TRX_ID, 'PRIN', am.principal_trx_id, 'FEE', am.FEE_TRX_ID) and
psa.amount_due_remaining > 0 and
psa.status = 'OP' and
(am.INTEREST_TRX_ID is not null or am.principal_trx_id is not null or am.FEE_TRX_ID is not null) and
look.lookup_type = 'PAYMENT_APPLICATION_TYPE' and
look.lookup_code = P_INVOICE_TYPE;
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)
and nvl(head.LAST_PAYMENT_NUMBER, 0) < (select max(END_INSTALLMENT_NUMBER) from lns_rate_schedules
where term_id = term.term_id and phase = nvl(head.CURRENT_PHASE, 'TERM'))
and trunc(term.NEXT_PAYMENT_DUE_DATE + nvl(term.NUMBER_GRACE_DAYS, 0)) < trunc(sysdate)
and head.LOAN_ID = P_LOAN_ID;
select 'x'
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 is null or head.BILLED_FLAG = 'N')
and nvl(head.LAST_PAYMENT_NUMBER, 0) < (select max(END_INSTALLMENT_NUMBER) from lns_rate_schedules
where term_id = term.term_id and phase = nvl(head.CURRENT_PHASE, 'TERM'))
and head.LOAN_ID = P_LOAN_ID;
select
psa.customer_trx_id,
psa.PAYMENT_SCHEDULE_ID,
psa.trx_number,
am.payment_number,
decode(psa.customer_trx_id, am.principal_trx_id, 'PRIN', am.interest_trx_id, 'INT', am.fee_trx_id, 'FEE'),
decode(psa.customer_trx_id, am.principal_trx_id, am.principal_amount, am.interest_trx_id, am.interest_amount, am.fee_trx_id, am.fee_amount),
psa.amount_due_remaining,
psa.TRX_DATE,
psa.due_date,
psa.GL_DATE,
psa.invoice_currency_code,
nvl(loan.EXCHANGE_RATE, 1),
trx_type.name
from
lns_amortization_scheds am,
lns_loan_headers loan,
ar_payment_schedules psa,
RA_CUST_TRX_TYPES_ALL trx_type
where
am.loan_id = P_LOAN_ID and
am.amortization_schedule_id = P_AMORTIZATION_SCHED_ID and
loan.loan_id = am.loan_id and
psa.customer_trx_id in (am.principal_trx_id, am.interest_trx_id, am.fee_trx_id) and
(am.INTEREST_TRX_ID is not null or am.principal_trx_id is not null or am.FEE_TRX_ID is not null) and
psa.CUST_TRX_TYPE_ID = trx_type.CUST_TRX_TYPE_ID;
select loan.LAST_AMORTIZATION_ID
from LNS_LOAN_HEADERS_all loan
where loan.loan_id = P_LOAN_ID;
select AMORTIZATION_SCHEDULE_ID
from LNS_AMORTIZATION_SCHEDS
where loan_id = P_LOAN_ID and
AMORTIZATION_SCHEDULE_ID > P_LAST_AM_SCHED and
(REVERSED_FLAG is null or REVERSED_FLAG = 'N')
order by AMORTIZATION_SCHEDULE_ID;
select LOAN_NUMBER,
nvl(current_phase, 'TERM'),
decode(current_phase, 'OPEN', OPEN_LOAN_START_DATE, LOAN_START_DATE)
from LNS_LOAN_HEADERS
where LOAN_ID = P_LOAN_ID;
l_fee_tbl.delete;
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 lines.fee_schedule_id, feeSchd.object_version_number
FROM LNS_AMORTIZATION_SCHEDS scheds,lns_amortization_lines lines, lns_fees_all fee, lns_fee_schedules feeSchd
WHERE scheds.amortization_schedule_id = lines.amortization_schedule_id
AND scheds.amortization_schedule_id = C_AMORTIZATION_ID
AND lines.line_type = 'FEE'
AND fee.fee_id = lines.fee_id
AND fee.billing_option = 'SUBMIT_FOR_APPROVAL'
AND feeSchd.fee_schedule_id = lines.fee_schedule_id
AND feeSchd.billed_flag = 'Y'
AND feeSchd.active_flag = 'Y';
LNS_FEE_SCHEDULES_PKG.UPDATE_ROW(P_FEE_SCHEDULE_ID => l_fee_schedule_id
,P_FEE_ID => null
,P_LOAN_ID => null
,P_FEE_AMOUNT => null
,P_FEE_INSTALLMENT => null
,P_FEE_DESCRIPTION => null
,P_ACTIVE_FLAG => null
,P_BILLED_FLAG => 'N' -- Make BilledFlag to 'N'
,P_FEE_WAIVABLE_FLAG => null
,P_WAIVED_AMOUNT => null
,P_LAST_UPDATED_BY => LNS_UTILITY_PUB.LAST_UPDATED_BY
,P_LAST_UPDATE_DATE => LNS_UTILITY_PUB.LAST_UPDATE_DATE
,P_LAST_UPDATE_LOGIN => LNS_UTILITY_PUB.LAST_UPDATE_LOGIN
,P_PROGRAM_ID => null
,P_REQUEST_ID => null
,P_OBJECT_VERSION_NUMBER => l_obj_ver_num + 1);
LogMessage(FND_LOG.LEVEL_PROCEDURE, 'Updated fee_schedule_id '||l_fee_schedule_id||' successfully');
LogMessage(FND_LOG.LEVEL_PROCEDURE, 'Total No. of submitForApprFee fee_schedule records updated are '||l_count);
SELECT head.DISABLE_BILLING_FLAG
FROM LNS_LOAN_HEADERS_ALL head
where head.loan_id = C_LOAN_ID;
select
loan.loan_id,
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,
lines.ADJUSTMENT_DATE,
psa.GL_DATE,
trx.org_id,
trx.legal_entity_id,
lines.REC_ADJUSTMENT_ID,
nvl(lines.installment_number, 1)
from LNS_LOAN_HEADERS_VL loan,
RA_CUSTOMER_TRX trx,
ar_payment_schedules psa,
lns_loan_lines lines
where lines.LOAN_LINE_ID = P_LOAN_LINE_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;
LNS_LOAN_LINE_PUB.UPDATE_LINE_ADJUSTMENT_NUMBER(
p_init_msg_list => FND_API.G_FALSE
,p_loan_id => l_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
,p_adjustment_date => l_apply_date
,p_original_flag => 'N'
,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');