[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
vSql := 'Select ''X'' ' ||
' From lns_assignments ' ||
'Where exists ' ||
' (Select assignment_id ' ||
' From lns_assignments ' ||
' Where loan_id = :a1 ' ||
' and assignment_type = :b1)';
select ass.fee_id
,decode(ass.rate_type, 'FIXED', fee.fee, 'VARIABLE', fee.fee/100 * head.header_amount)
,fee.fee_description
,ass.fee_basis
from lns_fee_assignments ass
,lns_disb_headers head
,lns_fees_all fee
where ass.loan_id is null
and ass.disb_header_id = head.disb_header_id
and fee.fee_id = ass.fee_id
and ass.disb_header_id = p_disb_head_id;
select ass.fee_id
,decode(ass.rate_type, 'FIXED', fee.fee, 'VARIABLE', fee.fee/100)
,fee.fee_description
,ass.fee_basis
from lns_fee_assignments ass
,lns_fees_all fee
where ass.fee_type = 'EVENT_CONVERSION'
and loan_id = p_loan_id;
select sum(l.line_amount)
from lns_disb_lines l
,lns_disb_headers h
where h.disb_header_id = l.disb_header_id
and l.status = 'FULLY_FUNDED'
and h.loan_id = p_loan_id;
select requested_amount
from lns_loan_headers_all
where loan_id = p_loan_id;
select nvl(last_payment_number, 0)
from lns_loan_headers_all
where loan_id = p_loan_id;
update lns_fee_schedules
set billed_flag = 'Y'
where disb_header_id = p_disb_head_id;
select sched.fee_id
,sched.fee_description
from lns_fee_schedules sched
,lns_fees fees
,lns_amortization_lines lines
,lns_amortization_scheds am
where fees.fee_id = sched.fee_id
and lines.fee_schedule_id = sched.fee_schedule_id
and lines.amortization_schedule_id = am.amortization_schedule_id
and am.amortization_schedule_id =
(select max(am2.amortization_schedule_id)
from lns_amortization_scheds am2
where am2.reversed_flag = 'Y'
and am2.loan_id = p_loan_id )
and am.reamortization_amount is null
and sched.fee_installment = p_installment
and ((fees.fee_category = 'MANUAL')
OR (fees.fee_category = 'EVENT' AND fees.fee_type = 'EVENT_ORIGINATION'))
and sched.loan_id = am.loan_id
and am.loan_id = p_loan_id
and sched.active_flag = 'Y'
and sched.billed_flag = 'Y';
SELECT fees.fee_id
,fees.fee_name
,fees.fee_type
,fees.fee_category
,decode(fees.rate_type, 'FIXED', fees.fee, 'VARIABLE', fees.fee/100)
,fees.fee_basis
,fees.billing_option
,fees.rate_type
,fees.number_grace_days
,fees.minimum_overdue_amount
,0
,0
,nvl(fees.fee_editable_flag,'N')
,nvl(fees.fee_waivable_flag,'N')
from lns_fees_all fees
where fees.fee_id = p_fee_id;
SELECT fees.fee_id
,fees.fee_name
,fees.fee_type
,fees.fee_category
,decode(fees.fee_category, 'MEMO', decode(fees.rate_type, 'FIXED', Assgn.fee, 'VARIABLE', Assgn.fee/100), decode(fees.rate_type, 'FIXED', fees.fee, 'VARIABLE', fees.fee/100))
,fees.fee_basis
,assgn.billing_option
,fees.rate_type
,fees.number_grace_days
,fees.minimum_overdue_amount
,fees.fee_basis_rule
,nvl(assgn.begin_installment_number,0)
,nvl(assgn.end_installment_number,0)
,nvl(fees.fee_editable_flag,'N')
,nvl(fees.fee_waivable_flag,'N')
from lns_fee_assignments assgn
,lns_fees_all fees
where assgn.loan_id = nvl(p_loan_id, assgn.loan_id)
and assgn.fee_id = fees.fee_id
and nvl(trunc(assgn.end_date_active), trunc(sysdate) + 1) >= trunc(sysdate)
and nvl(trunc(assgn.start_date_active), trunc(sysdate) - 1) <= trunc(sysdate)
and (((fees.fee_category = nvl(p_fee_category, fees.fee_category) and
fees.fee_type = nvl(p_fee_type, fees.fee_type)))
OR
((fees.fee_category = nvl(p_fee_category, fees.fee_category) and fees.fee_type is null)))
AND assgn.begin_installment_number <= nvl(p_installment, assgn.begin_installment_number)
AND assgn.end_installment_number >= nvl(p_installment, assgn.end_installment_number)
and fees.fee_id = nvl(p_fee_id, fees.fee_id);
SELECT fees.fee_id
,fees.fee_name
,fees.fee_type
,fees.fee_category
,decode(fees.rate_type, 'FIXED', fees.fee, 'VARIABLE', fees.fee/100)
,fees.fee_basis
,assgn.billing_option
,fees.rate_type
,0
,0
,dh.disb_header_id
,dh.target_date
,dh.header_amount
from lns_fee_assignments assgn
,lns_fees_all fees
,lns_disb_headers dh
where dh.loan_id = nvl(p_loan_id, dh.loan_id)
and dh.disb_header_id = nvl(p_disb_header_id, dh.disb_header_id)
and fees.fee_id = nvl(p_fee_id, fees.fee_id)
and assgn.fee_id = fees.fee_id
and nvl(trunc(assgn.end_date_active), trunc(sysdate) + 1) >= trunc(sysdate)
and nvl(trunc(assgn.start_date_active), trunc(sysdate) - 1) <= trunc(sysdate)
and (((fees.fee_category = nvl(p_fee_category, fees.fee_category) and
fees.fee_type = nvl(p_fee_type, fees.fee_type)))
OR
((fees.fee_category = nvl(p_fee_category, fees.fee_category) and fees.fee_type is null)))
and assgn.disb_header_id = dh.disb_header_id
and dh.target_date >= nvl(p_from_date, dh.target_date -1)
and dh.target_date < nvl(p_to_date, dh.target_date + 1);
select requested_amount
from lns_loan_headers_all
where loan_id = p_loan_id;
select header_amount
from lns_disb_headers
where disb_header_id = p_disb_header_id;
select nvl(BEGIN_INSTALLMENT_NUMBER,0)
from lns_fee_assignments
where fee_id = p_fee_id
and loan_id = p_loan_id;
SELECT fndc.precision
FROM lns_loan_headers_all lnh
,fnd_currencies fndc
WHERE lnh.loan_id = p_loan_id
and lnh.loan_currency = fndc.currency_code;
l_fees_tbl(f).fee_schedule_id := -1; --assign this AFTER insert into fee_schedules
select sched.fee_schedule_id
,sched.fee_id
,sched.fee_amount - nvl(sched.waived_amount, 0)
,struct.fee_name
,struct.fee_category
,struct.fee_type
,sched.fee_installment
,struct.fee_description
,sched.fee_waivable_flag -- should be struct right
,decode(struct.fee_category, 'MANUAL', 'Y', 'N')
,nvl(struct.fee_editable_flag, 'N')
from lns_fee_schedules sched
,lns_fees struct
where sched.loan_id = p_loan_id
and sched.fee_id = struct.fee_id
and fee_installment = p_installment
and active_flag = 'Y'
and billed_flag = 'N' -- deduce this based on parent records
-- Bug#6961250 commented below line as for disbFees, disb_header_id is
-- NOT NULL
-- and disb_header_id is null
and (not exists
(select 'X'
from lns_amortization_scheds am
,lns_amortization_lines lines
where lines.loan_id = p_loan_id
and lines.fee_schedule_id = sched.fee_schedule_id
and am.loan_id = lines.loan_id
and NVL(am.reversed_flag, 'N') = 'N'
and am.payment_number = p_installment)
or exists
(select 'X'
from lns_amortization_scheds am
,lns_amortization_lines lines
where lines.loan_id = p_loan_id
and lines.fee_schedule_id = sched.fee_schedule_id
and am.loan_id = lines.loan_id
and am.reversed_flag = 'Y'
and am.payment_number = p_installment));
select sched.fee_id
,sched.fee_amount - nvl(sched.waived_amount, 0)
,struct.fee_name
,struct.fee_description
from lns_fee_schedules sched
,lns_fees_all struct
where disb_header_id = p_disb_header_id
and sched.fee_id = struct.fee_id;
select fee.fee_description
,fee.fee_name
,ass.fee
,fee.fee_category
,fee.fee_type
from lns_fee_assignments ass
,lns_fees_all fee
where ass.loan_id = p_loan_id
and fee.fee_id = ass.fee_id
and ass.fee_type = 'EVENT_CONVERSION';
select LNS_BILLING_UTIL_PUB.LAST_PAYMENT_NUMBER_EXT(p_loan_id) + 1
into l_last_installment
from dual;
l_virtual_fees_tbl.delete;
procedure updateFeeSchedule(p_init_msg_list in varchar2
,p_commit in varchar2
,p_loan_id in number
,p_fees_tbl IN LNS_FEE_ENGINE.FEE_CALC_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);
select fee_schedule_id
from lns_fee_schedules
where loan_id = p_loan_id
and fee_id = p_fee_id
and billed_flag = 'N'
and active_flag = 'Y';
select nvl(current_phase, 'TERM')
from lns_loan_headers
where loan_id = p_loan_id;
SELECT fndc.precision
FROM lns_loan_headers lnh
,fnd_currencies fndc
WHERE lnh.loan_id = p_loan_id
and lnh.loan_currency = fndc.currency_code;
l_api_name := 'updateFeeSchedule';
SAVEPOINT updateFeeSchedule;
LNS_FEE_SCHEDULES_PKG.UPDATE_ROW(P_FEE_SCHEDULE_ID => l_fee_schedule_id
,P_FEE_ID => p_fees_tbl(k).FEE_ID
,P_LOAN_ID => p_loan_id
,P_FEE_AMOUNT => round(p_fees_tbl(k).FEE_AMOUNT,l_precision)
,P_FEE_INSTALLMENT => p_fees_tbl(k).FEE_INSTALLMENT
,P_FEE_DESCRIPTION => p_fees_tbl(k).FEE_DESCRIPTION
,P_ACTIVE_FLAG => p_fees_tbl(k).ACTIVE_FLAG --'Y'
,P_BILLED_FLAG => p_fees_tbl(k).BILLED_FLAG --'N'
,P_FEE_WAIVABLE_FLAG => p_fees_tbl(k).FEE_WAIVABLE_FLAG
,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 => null
,P_PROGRAM_ID => null
,P_REQUEST_ID => null
,P_OBJECT_VERSION_NUMBER => 1);
ROLLBACK TO updateFeeSchedule;
ROLLBACK TO updateFeeSchedule;
ROLLBACK TO updateFeeSchedule;
end updateFeeSchedule;
select nvl(current_phase, 'TERM')
from lns_loan_headers_all
where loan_id = p_loan_id;
select lns_fee_schedule_s.nextval
into l_fee_schedule_id
from dual;
LNS_FEE_SCHEDULES_PKG.INSERT_ROW(X_FEE_SCHEDULE_ID => l_fee_schedule_id
,P_FEE_ID => p_fees_tbl(k).FEE_ID
,P_LOAN_ID => p_loan_id
,P_FEE_AMOUNT => round(p_fees_tbl(k).FEE_AMOUNT, l_loan_details.currency_precision)
,P_FEE_INSTALLMENT => p_fees_tbl(k).FEE_INSTALLMENT
,P_FEE_DESCRIPTION => p_fees_tbl(k).FEE_DESCRIPTION
,P_ACTIVE_FLAG => 'Y'
,P_BILLED_FLAG => 'N'
,P_FEE_WAIVABLE_FLAG => p_fees_tbl(k).FEE_WAIVABLE_FLAG
,P_WAIVED_AMOUNT => null
,P_CREATED_BY => lns_utility_pub.created_by
,P_CREATION_DATE => lns_utility_pub.creation_date
,P_LAST_UPDATED_BY => lns_utility_pub.last_updated_by
,P_LAST_UPDATE_DATE => lns_utility_pub.last_update_date
,P_LAST_UPDATE_LOGIN => null
,P_PROGRAM_ID => null
,P_REQUEST_ID => null
,P_OBJECT_VERSION_NUMBER => 1
,P_DISB_HEADER_ID => p_fees_tbl(k).DISB_HEADER_ID);
|| x_fees_tbl => table of records inserted
||
|| Return value:
|| standard
|| KNOWN ISSUES
||
|| NOTES
||
|| MODIFICATION HISTORY
|| Date Author Description of Changes
|| 12/1/2004 8:40PM raverma Created
*=======================================================================*/
procedure processFees(p_init_msg_list in varchar2
,p_commit in varchar2
,p_loan_id in number
,p_installment_number in number
,p_fee_basis_tbl in LNS_FEE_ENGINE.FEE_BASIS_TBL
,p_fee_structures in LNS_FEE_ENGINE.FEE_STRUCTURE_TBL
,x_fees_tbl OUT NOCOPY LNS_FEE_ENGINE.FEE_CALC_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);
select nvl(sum(fee_amount), 0)
from lns_fee_schedules sched
,lns_fees fees
where sched.loan_id = p_loan_id
and sched.fee_id = fees.fee_id
and sched.fee_installment = p_installment
and sched.active_flag = 'Y'
and sched.billed_flag = 'Y' -- deduce this based on parent records
and fees.fee_category = p_category
and fees.fee_type = p_type
and (exists
(select 'X'
from lns_amortization_scheds am
,lns_amortization_lines lines
where am.loan_id = p_loan_id
and am.amortization_schedule_id = lines.amortization_schedule_id
and lines.fee_schedule_id = sched.fee_schedule_id
and NVL(am.reversed_flag, 'N') = 'N'
and am.payment_number = p_installment));
select sched.fee_amount - nvl(sched.waived_amount, 0) amount_remaining
,nvl(sched.waived_amount, 0) previously_waived
from lns_fee_schedules sched
where sched.loan_id = p_loan_id
and sched.fee_schedule_id = p_fee_schedule_id
and sched.fee_waivable_flag = 'Y'
and sched.active_flag = 'Y'
and sched.billed_flag = 'N';
lns_fee_schedules_pkg.update_row(P_FEE_SCHEDULE_ID => p_fee_schedule_id
,P_FEE_ID => null
,P_LOAN_ID => p_loan_id
,P_FEE_AMOUNT => null
,P_FEE_INSTALLMENT => null
,P_FEE_DESCRIPTION => null
,P_ACTIVE_FLAG => null
,P_BILLED_FLAG => null
,P_FEE_WAIVABLE_FLAG => null
,P_WAIVED_AMOUNT => p_waive_amount + l_waived_amount
,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 => null);
vPLSQL := 'SELECT decode(:p_waived_flag, ''Y'', nvl(sum(sched.waived_amount),0), nvl(sum(sched.fee_amount) - sum(sched.waived_amount),0))' ||
' from lns_fee_schedules sched ' ||
--' ,lns_fee_assignments assign ' ||
--' where assgn.fee_id = sched.fee_id ' ||
' Where sched.loan_id = :p_loan_id ' ||
' and sched.billed_flag = :p_billed_flag ' ||
' and sched.active_flag = ''Y'' ';
select LNS_BILLING_UTIL_PUB.LAST_PAYMENT_NUMBER_EXT(p_loan_id)
into l_last_installment
from dual;
vPlSql := 'select ' ||
' nvl(sum(amount_due_remaining),0) ' ||
' from lns_amortization_scheds am ' ||
' ,ar_payment_schedules ps ' ||
' ,lns_fee_assignments fass ' ||
' ,lns_fees fees ' ||
' where am.loan_id = :p_loan_id and ' ||
' am.payment_number = :p_installment and ' ||
' fees.fee_id = :p_fee_id and ' ||
' ps.amount_due_remaining > 0 and ' ||
' am.loan_id = fass.loan_id and ' ||
' fees.fee_id = fass.fee_id and ' ||
' am.reamortization_amount is null and ' ||
' am.reversed_flag <> ''Y'' and ' ||
' am.due_date + nvl(fees.number_grace_days, 0) < trunc(sysdate) ' ||
' and (not exists ' ||
' (select ''X'' ' ||
' from lns_fee_schedules sched ' ||
' where sched.loan_id = am.loan_id ' ||
' and fee_id = fees.fee_id ' ||
' and fee_installment = am.payment_number + 1' ||
' and billed_flag = ''N'' ' ||
' and active_flag = ''Y'' )) and ' ||
' (am.principal_trx_id = ps.customer_trx_id ';
select loan_id
from lns_loan_headers
where loan_status in ('ACTIVE', 'DELINQUENT', 'DEFAULT')
and primary_borrower_id = p_borrower_id;
select loan_id
from lns_loan_headers
where loan_status in ('ACTIVE', 'DELINQUENT', 'DEFAULT');
select count(1) into l_fee_records1
from lns_fee_schedules
where active_flag = 'Y'
and billed_flag = 'N';
select count(1) into l_fee_records2
from lns_fee_schedules
where active_flag = 'Y'
and billed_flag = 'N';