[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', nvl(ass.fee,fee.fee), 'VARIABLE', nvl(ass.fee,fee.fee)/100 * head.header_amount)
,decode(ass.rate_type, 'FIXED', nvl(ass.fee,fee.fee), 'VARIABLE', lns_fee_engine.calculateFee(ass.fee_id, head.disb_header_id, head.LOAN_ID))
,fee.fee_description
,ass.fee_basis
,nvl(ass.begin_installment_number, 0) -- fix for bug 8928398
,nvl(ass.phase, 'TERM')
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', nvl(ass.fee,fee.fee), 'VARIABLE', nvl(ass.fee,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 ass.fee_id = fee.fee_id
and loan_id = c_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;
/* Now, Conversion fees inserted into feeSchedules table when this fee is assigned
elsif p_phase = 'TERM' then
OPEN c_ConversionFees(p_loan_id);
select sched.fee_id
,sched.fee_description
,nvl(sched.phase, 'TERM')
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 = c_loan_id )
and am.reamortization_amount is null
and sched.fee_installment = c_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 = c_loan_id
and sched.active_flag = 'Y'
and sched.billed_flag = 'Y'
and nvl(sched.phase, 'TERM') = nvl(c_phase, 'TERM');
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_description
,fees.fee_type
,fees.fee_category
-- Bug#8915683, Now the FeeAssignment is updated for all fees. So use the assignment feeAmount
,decode(fees.rate_type, 'FIXED', nvl(Assgn.fee,fees.fee), 'VARIABLE', nvl(Assgn.fee,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')
,assgn.disb_header_id
,nvl(assgn.phase, 'TERM')
,nvl(assgn.CUSTOM_PROCEDURE, fees.CUSTOM_PROCEDURE)
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 nvl(assgn.phase, 'TERM') = c_phase
and fees.fee_id = nvl(p_fee_id, fees.fee_id)
and assgn.billing_option = nvl(p_billing_option, assgn.billing_option);
SELECT fees.fee_id
,fees.fee_name
,fees.fee_description
,fees.fee_type
,fees.fee_category
,decode(fees.rate_type, 'FIXED', nvl(Assgn.fee,fees.fee), 'VARIABLE', nvl(Assgn.fee,fees.fee)/100)
,fees.fee_basis
,assgn.billing_option
,fees.rate_type
,nvl(assgn.begin_installment_number,0)
,nvl(assgn.end_installment_number,0)
,dh.disb_header_id
,dh.target_date
,dh.header_amount
,nvl(assgn.phase, 'TERM')
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 assgn.disb_header_id = dh.disb_header_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 = 'EVENT'
and fees.fee_type = 'EVENT_FUNDING'
and assgn.begin_installment_number <= nvl(p_installment_no, assgn.begin_installment_number)
and assgn.end_installment_number >= nvl(p_installment_no, assgn.end_installment_number)
and nvl(assgn.phase, 'TERM') = c_phase;
select requested_amount, nvl(current_phase, 'TERM')
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 = c_fee_id
and disb_header_id = c_disb_header_id
and nvl(phase, 'TERM') = c_phase;
select nvl(BEGIN_INSTALLMENT_NUMBER,0)
from lns_fee_assignments
where fee_id = c_fee_id
and loan_id = c_loan_id
and nvl(phase, 'TERM') = c_phase;
select lh.loan_status into l_loan_status
from lns_loan_headers_all lh
where lh.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')
,struct.billing_option
,sched.phase
from lns_fee_schedules sched
,lns_fees struct
where sched.loan_id = c_loan_id
and sched.fee_id = struct.fee_id
and fee_installment = c_installment
and nvl(phase, 'TERM') = c_phase
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 = c_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 = c_installment)
or exists
(select 'X'
from lns_amortization_scheds am
,lns_amortization_lines lines
where lines.loan_id = c_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 = c_installment));
select sched.fee_id
,sched.fee_amount - nvl(sched.waived_amount, 0)
,struct.fee_name
,struct.fee_description
,sched.phase
from lns_fee_schedules sched
,lns_fees_all struct
where disb_header_id = c_disb_header_id
and sched.fee_id = struct.fee_id
and nvl(sched.phase, 'TERM') = c_phase;
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')
,decode(struct.fee_category, 'MANUAL', nvl(struct.fee_editable_flag, 'N'), 'N')
,struct.billing_option
,sched.phase
from lns_fee_schedules sched
,lns_fees struct
where sched.loan_id = c_loan_id
and sched.fee_id = struct.fee_id
and fee_installment = c_installment
and nvl(phase, 'TERM') = c_phase
and active_flag = 'Y'
and billed_flag = 'N' -- deduce this based on parent records
and (not exists
(select 'X'
from lns_amortization_scheds am
,lns_amortization_lines lines
where lines.loan_id = c_loan_id
and lines.fee_schedule_id = sched.fee_schedule_id
and am.loan_id = lines.loan_id
and am.payment_number = c_installment));
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')
,decode(struct.fee_category, 'MANUAL', nvl(struct.fee_editable_flag, 'N'), 'N')
,struct.billing_option
,sched.phase
from lns_fee_schedules sched
,lns_fees struct
where sched.loan_id = c_loan_id
and sched.fee_id = struct.fee_id
and fee_installment = c_installment
and nvl(phase, 'TERM') = c_phase
and active_flag = 'Y'
and struct.fee_category = 'MANUAL';
SELECT 'X'
FROM LNS_FEE_SCHEDULES schd
WHERE schd.fee_id = c_fee_id
AND schd.fee_installment = c_installment
AND schd.loan_id = c_loan_id
AND schd.ACTIVE_FLAG = 'Y'
--AND schd.billed_flag = 'N'
AND schd.phase = c_phase
AND NVL(schd.disb_header_id, -1) = NVL(c_disb_header_id, -1);
SELECT 'X'
FROM LNS_FEE_SCHEDULES schd
WHERE schd.fee_id = c_fee_id
AND schd.fee_installment = c_installment
AND schd.loan_id = c_loan_id
AND schd.ACTIVE_FLAG = 'Y'
AND schd.phase = c_phase
AND NVL(schd.disb_header_id, -1) = NVL(c_disb_header_id, -1);
select LNS_BILLING_UTIL_PUB.LAST_PAYMENT_NUMBER_EXT(p_loan_id)
into l_last_installment
from dual;
l_recur_fees_tbl.delete;
l_orig_fees_tbl.delete;
l_memo_fees_tbl.delete;
l_funding_fees_tbl.delete;
|| PUBLIC PROCEDURE updateFeeSchedule
||
|| DESCRIPTION
|| Overview: this procedure will validate and update a table of fees to
|| the fee_schedule table
||
|| PSEUDO CODE/LOGIC
||
|| PARAMETERS
|| Parameter: p_fees_tbl => represents a table of fees
|| p_loan_id => loan_id
||
|| Return value:
|| standard
|| KNOWN ISSUES
||
|| NOTES
||
|| MODIFICATION HISTORY
|| Date Author Description of Changes
|| 1/7/2005 8:40PM raverma Created
||
*=======================================================================*/
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
,P_DISB_HEADER_ID => p_fees_tbl(k).DISB_HEADER_ID
,P_PHASE => p_fees_tbl(k).PHASE);
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
,P_PHASE => p_fees_tbl(k).PHASE);
|| 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);
updateCount NUMBER;
l_update_fee_calc_tbl LNS_FEE_ENGINE.FEE_CALC_TBL;
l_inserted_fees number;
select nvl(sum(fee_amount), 0)
from lns_fee_schedules sched
,lns_fees fees
where sched.loan_id = c_loan_id
and sched.fee_id = fees.fee_id
and sched.fee_installment = c_installment
and sched.active_flag = 'Y'
and sched.billed_flag = 'Y' -- deduce this based on parent records
and fees.fee_category = c_category
and fees.fee_type = c_type
and nvl(sched.phase, 'TERM') = c_phase
and fees.BILLING_OPTION = nvl(C_BILLING_OPTION, fees.BILLING_OPTION)
and (exists
(select 'X'
from lns_amortization_scheds am
,lns_amortization_lines lines
where am.loan_id = c_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 = c_installment));
l_inserted_fees := 0;
updateCount := 0;
l_write_fee_calc_tbl.delete;
l_update_fee_calc_tbl.delete;
SELECT fee_schedule_id, billed_flag INTO l_fee_schedule_id, l_billed_flag
FROM lns_fee_schedules
WHERE loan_id = p_loan_id
AND fee_id = l_fee_calc_tbl(f).fee_id
AND fee_installment = l_fee_calc_tbl(f).fee_installment
AND nvl(phase, 'TERM') = l_fee_calc_tbl(f).phase
AND active_flag = 'Y'
AND billed_flag = 'N';
logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - Inserting calculated fee with fee_id as ' ||l_fee_calc_tbl(f).FEE_ID);
logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' -The feeSchd '||l_fee_schedule_id||' is already billed. So dont update the record' );
updateCount := updateCount + 1;
l_update_fee_calc_tbl(updateCount) := l_fee_calc_tbl(f);
l_update_fee_calc_tbl(updateCount).FEE_SCHEDULE_ID := l_fee_schedule_id;
logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' Total No of Updatable Records are '||updateCount );
l_inserted_fees := l_inserted_fees + 1;
x_fees_tbl(l_inserted_fees) := l_write_fee_calc_tbl(k);
IF (updateCount > 0) THEN
lns_fee_engine.updateFeeSchedule(p_init_msg_list => p_init_msg_list
,p_commit => p_commit
,p_loan_id => p_loan_id
,p_fees_tbl => l_update_fee_calc_tbl
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data);
FND_MESSAGE.SET_NAME('LNS', 'LNS_FEE_UPDATE_FAILURE');
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 = c_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
,P_DISB_HEADER_ID => null
,P_PHASE => 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'' ';
updateCount NUMBER;
deleteCount NUMBER;
l_update_fee_calc_tbl LNS_FEE_ENGINE.FEE_CALC_TBL;
select DUE_DATE
from LNS_AMORTIZATION_SCHEDS
where LOAN_ID = P_LOAN_ID
and PAYMENT_NUMBER = p_payment_number
and PARENT_AMORTIZATION_ID is null
and (REVERSED_FLAG is null or REVERSED_FLAG = 'N');
select (ACTUAL_BALANCE - THEORETICAL_BALANCE)
from LNS_PRIN_TRX_ACTIVITIES_V
where loan_id = p_loan_id and
trunc(ACTIVITY_DATE) <= trunc(p_date)
and ACTIVITY_CODE not in ('DISBURSEMENT', 'INVOICE_ADDED')
order by activity_date desc, display_order desc;
select (THEORETICAL_BALANCE - ACTUAL_BALANCE)
from LNS_INT_TRX_ACTIVITIES_V
where loan_id = p_loan_id
and trunc(ACTIVITY_DATE) <= trunc(p_date)
order by activity_date desc, display_order desc;
updateCount := 0;
deleteCount := 0;
l_write_fee_calc_tbl.delete;
l_update_fee_calc_tbl.delete;
SELECT fee_schedule_id INTO l_fee_schedule_id
FROM lns_fee_schedules
WHERE loan_id = p_loan_id
AND fee_id = l_fee_calc_tbl(1).fee_id
AND fee_installment = l_fee_calc_tbl(1).fee_installment
AND nvl(phase, 'TERM') = l_fee_calc_tbl(1).phase
AND active_flag = 'Y'
AND billed_flag = 'N';
logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ' - Inserting fee with fee_id = ' || l_fee_calc_tbl(1).FEE_ID);
updateCount := updateCount + 1;
l_update_fee_calc_tbl(updateCount) := l_fee_calc_tbl(1);
l_update_fee_calc_tbl(updateCount).FEE_SCHEDULE_ID := l_fee_schedule_id;
delete from lns_fee_schedules
WHERE loan_id = p_loan_id
AND fee_id = l_late_fee_structure(1).fee_id
AND fee_installment = l_current_installment
AND nvl(phase, 'TERM') = l_late_fee_structure(1).phase
AND active_flag = 'Y'
AND billed_flag = 'N';
deleteCount := deleteCount + 1;
logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ' Total No of Updatable Records = ' || updateCount );
logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ' Total No of Deleted Records = ' || deleteCount );
IF (updateCount > 0) THEN
logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ' Calling updateFeeSchedule...');
lns_fee_engine.updateFeeSchedule(p_init_msg_list => p_init_msg_list
,p_commit => p_commit
,p_loan_id => p_loan_id
,p_fees_tbl => l_update_fee_calc_tbl
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data);
FND_MESSAGE.SET_NAME('LNS', 'LNS_FEE_UPDATE_FAILURE');
select loan_id, current_phase
from lns_loan_headers
where loan_status in ('ACTIVE', 'DELINQUENT', 'DEFAULT')
and primary_borrower_id = p_borrower_id;
select loan_id, current_phase
from lns_loan_headers
where loan_status in ('ACTIVE', 'DELINQUENT', 'DEFAULT');
select current_phase
from lns_loan_headers
where loan_id = c_loan_id
and 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';
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')
,nvl(sched.phase, 'TERM')
from lns_fee_schedules sched
,lns_fees struct
where sched.loan_id = c_loan_id
and sched.fee_id = struct.fee_id
and fee_installment = c_installment
and struct.FEE_CATEGORY = 'EVENT'
and struct.FEE_TYPE = 'EVENT_ORIGINATION'
and struct.BILLING_OPTION = 'SUBMIT_FOR_APPROVAL'
and active_flag = 'Y'
and billed_flag = c_billed_flag
and (not exists
(select 'X'
from lns_amortization_scheds am
,lns_amortization_lines lines
where lines.loan_id = c_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 = c_installment
and am.amortization_schedule_id = (select max(amortization_schedule_id)
from lns_amortization_lines amlines2
where amlines2.fee_schedule_id = lines.fee_schedule_id)
)
or exists
(select 'X'
from lns_amortization_scheds am
,lns_amortization_lines lines
where lines.loan_id = c_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 = c_installment
and am.amortization_schedule_id = (select max(amortization_schedule_id)
from lns_amortization_lines amlines2
where amlines2.fee_schedule_id = lines.fee_schedule_id)
));
|| Overview: this procedure will update the feeInstallments(begin and end) for the given disb_header_id
||
||
|| PSEUDO CODE/LOGIC
||
|| PARAMETERS
|| Parameter: p_disb_header_id => disbursement header id
||
|| Return value:
|| standard
|| KNOWN ISSUES
||
|| NOTES
||
|| MODIFICATION HISTORY
|| Date Author Description of Changes
|| 16-FEB-2010 mbolli Bug#9255294 - Created
*=======================================================================*/
procedure SET_DISB_FEES_INSTALL(p_init_msg_list in varchar2
,p_disb_header_id in varchar2
,x_return_status out nocopy varchar2
,x_msg_count out nocopy number
,x_msg_data out nocopy varchar2)
is
l_api_name varchar2(50);
-- Update the feeInstallment of the fundignFees of this disbursement
LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'Updating the feeAssignment installments of disb_hdr_id: '||p_disb_header_id);
UPDATE lns_fee_assignments
SET begin_installment_number = l_inst_no
,end_installment_number = l_inst_no
,object_version_number = object_version_number + 1
,last_updated_by = LNS_UTILITY_PUB.last_updated_by
,last_update_date = LNS_UTILITY_PUB.last_update_date
,last_update_login = LNS_UTILITY_PUB.last_update_login
WHERE disb_header_id =p_disb_header_id;
LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'Updated feeAssignments '||SQL%ROWCOUNT);