[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT pld.lease_commencement_date
,pil.index_lease_number
,pil.assessment_date
,pil.commencement_date
,pil.termination_date
,pil.assessment_interval
,pil.relationship_default
,pil.basis_percent_default
FROM pn_index_leases_all pil, pn_lease_details_all pld
WHERE pld.lease_id = pil.lease_id
AND pil.index_lease_id = ip_index_lease_id;
PROCEDURE delete_periods (
p_index_lease_id IN NUMBER
,p_index_period_id IN NUMBER
,p_ignore_approved_terms IN VARCHAR2
,p_new_termination_date IN DATE) AS
CURSOR index_lease_periods (
ip_index_lease_id IN NUMBER
,ip_index_period_id IN NUMBER) IS
SELECT pilp.index_lease_id,
pilp.index_period_id
FROM pn_index_lease_periods_all pilp
WHERE pilp.index_lease_id = ip_index_lease_id
AND ( pilp.index_period_id = ip_index_period_id
OR ip_index_period_id IS NULL);
SELECT pilp.index_period_id
,ppt.payment_term_id
,ppt.lease_id
FROM pn_index_lease_periods_all pilp, pn_payment_terms_all ppt
WHERE pilp.index_lease_id = ip_index_lease_id
AND pilp.index_period_id = ppt.index_period_id
AND pilp.index_period_id = ip_index_period_id;
SELECT 'Y'
FROM DUAL
WHERE EXISTS( SELECT payment_item_id
FROM pn_payment_items_all items,
pn_payment_schedules_all sched
WHERE sched.payment_schedule_id = items.payment_schedule_id
AND items.payment_term_id = p_payment_term_id
AND sched.payment_status_lookup_code = 'APPROVED');
SELECT index_period_id
FROM pn_index_lease_periods_all
WHERE index_lease_id = p_index_lease_id
AND assessment_date <= p_new_termination_date
ORDER BY assessment_date DESC;
DELETE FROM pn_distributions_all
WHERE payment_term_id IN
(SELECT payment_term_id
FROM pn_payment_terms_all
WHERE payment_term_id = il_rec.payment_term_id
AND status<>
pn_index_amount_pkg.c_payment_term_status_approved);
DELETE FROM pn_payment_terms_all
WHERE payment_term_id = il_rec.payment_term_id
AND status <> pn_index_amount_pkg.c_payment_term_status_approved;
DELETE FROM pn_index_lease_terms_all
WHERE rent_increase_term_id = il_rec.payment_term_id
AND approved_flag <> pn_index_amount_pkg.c_payment_term_status_approved;
put_log(' delete periods : payment term id ='||il_rec.payment_term_id);
UPDATE pn_payment_terms_all
SET index_period_id = l_latest_period_id,
index_term_indicator = 'REVERSED'
WHERE payment_term_id = il_rec.payment_term_id;
DELETE FROM pn_payment_items_all
WHERE payment_term_id =il_rec.payment_term_id;
DELETE FROM pn_payment_schedules_all pps
WHERE not exists(SELECT 1
FROM PN_PAYMENT_ITEMS_ALL ppi
WHERE ppi.payment_schedule_id=pps.payment_schedule_id)
AND pps.lease_id=il_rec.lease_id;
DELETE FROM pn_distributions_all
WHERE payment_term_id = il_rec.payment_term_id;
DELETE FROM pn_payment_terms_all
WHERE payment_term_id = il_rec.payment_term_id;
DELETE FROM pn_index_lease_terms_all
where rent_increase_term_id = il_rec.payment_term_id;
DELETE FROM pn_payment_terms_all
WHERE payment_term_id = il_rec.payment_term_id;
DELETE FROM pn_distributions_all
WHERE payment_term_id = il_rec.payment_term_id;
DELETE FROM pn_index_lease_terms_all
WHERE rent_increase_term_id = il_rec.payment_term_id;
DELETE pn_index_lease_periods_all
WHERE index_lease_id = p_index_lease_id
AND index_period_id = il_rec_periods.index_period_id;
END delete_periods;
SELECT ORG_ID
FROM pn_index_leases_all
WHERE index_lease_id = p_index_lease_id;
pn_index_lease_periods_pkg.insert_row (
x_rowid => v_x_rowid
,x_org_id => v_org_id
,x_index_period_id => v_period_id
-- should not be in out??
,x_index_lease_id => p_index_lease_id
,x_line_number => v_period_number
,x_assessment_date => v_curr_asmt_dt
,x_basis_start_date => v_basis_start_date
,x_basis_end_date => v_basis_end_date
,x_last_update_date => SYSDATE
,x_last_updated_by => NVL (fnd_profile.VALUE ('USER_ID'), 0)
,x_creation_date => SYSDATE
,x_created_by => NVL (fnd_profile.VALUE ('USER_ID'), 0)
,x_index_finder_date => v_x_index_finder_date
,x_current_index_line_id => NULL
,x_current_index_line_value => NULL
,x_previous_index_line_id => NULL
,x_previous_index_line_value => NULL
,x_current_basis => NULL
,x_relationship => p_relationship_default
,x_index_percent_change => NULL
,x_basis_percent_change => p_basis_percent_default
,x_unconstraint_rent_due => NULL
,x_constraint_rent_due => NULL
,x_last_update_login => NULL
,x_attribute_category => NULL
,x_attribute1 => NULL
,x_attribute2 => NULL
,x_attribute3 => NULL
,x_attribute4 => NULL
,x_attribute5 => NULL
,x_attribute6 => NULL
,x_attribute7 => NULL
,x_attribute8 => NULL
,x_attribute9 => NULL
,x_attribute10 => NULL
,x_attribute11 => NULL
,x_attribute12 => NULL
,x_attribute13 => NULL
,x_attribute14 => NULL
,x_attribute15 => NULL
,x_index_multiplier => p_index_multiplier);
SELECT pil.index_lease_number
,pil.commencement_date
,pil.termination_date
,pil.assessment_date "DATE_ASSESSED"
,pil.assessment_interval
,pil.relationship_default
,pil.basis_percent_default
FROM pn_index_leases_all pil
WHERE pil.index_lease_id = ip_index_lease_id;
SELECT pilp.line_number
,pilp.basis_start_date
,pilp.basis_end_date
,pilp.index_finder_date
,pilp.assessment_date
FROM pn_index_lease_periods_all pilp
WHERE pilp.index_lease_id = ip_index_lease_id
ORDER BY pilp.line_number;
delete_periods (
p_index_lease_id => p_index_lease_id
,p_index_period_id => NULL
,p_ignore_approved_terms => 'N');
SELECT pil.index_lease_id
,pil.index_lease_number
,pld.lease_commencement_date
,pil.assessment_date
,pil.commencement_date
,pil.termination_date
,pil.assessment_interval
,pil.relationship_default
,pil.basis_percent_default
,pil.index_finder_months
,nvl (pil.index_multiplier,1) "INDEX_MULTIPLIER"
FROM pn_index_leases pil
,pn_lease_details_all pld
WHERE pld.lease_id = pil.lease_id
AND ( pil.index_lease_number = p_index_lease_num
OR p_index_lease_num IS NULL)
ORDER BY pil.index_lease_number;
SELECT pil.index_lease_id
,pil.index_lease_number
,pld.lease_commencement_date
,pil.assessment_date
,pil.commencement_date
,pil.termination_date
,pil.assessment_interval
,pil.relationship_default
,pil.basis_percent_default
,pil.index_finder_months
,nvl (pil.index_multiplier, 1) "INDEX_MULTIPLIER"
FROM pn_index_leases_all pil, pn_lease_details_all pld
WHERE pld.lease_id = pil.lease_id
AND pil.index_lease_id = p_index_lease_id;
SELECT term.payment_term_id
FROM pn_payment_schedules_all sched,
pn_index_leases_all ilease,
pn_payment_terms_all term,
pn_payment_items_all item,
pn_index_lease_periods_all period
WHERE sched.lease_id = ilease.lease_id
AND sched.payment_schedule_id = item.payment_schedule_id
AND item.payment_term_id = term.payment_term_id
AND term.lease_id = ilease.lease_id
AND ilease.index_lease_id = p_index_lease_id
AND period.index_lease_id = ilease.index_lease_id
AND term.index_period_id = period.index_period_id
AND term.index_period_id IS NOT NULL
AND sched.payment_status_lookup_code = 'APPROVED'
AND ilease.commencement_date > p_new_termination_date
AND ilease.termination_date > p_new_termination_date ;
DELETE FROM pn_index_exclude_term_all
WHERE index_lease_id = p_index_lease_id ;
DELETE FROM pn_index_lease_terms_all
WHERE index_lease_id = p_index_lease_id ;
DELETE FROM pn_index_lease_constraints_all
WHERE index_lease_id = p_index_lease_id ;
DELETE FROM pn_payment_items_all
WHERE payment_term_id IN ( SELECT term.payment_term_id
FROM pn_payment_terms_all term,pn_index_lease_periods_all iperiod
WHERE term.index_period_id = iperiod.index_period_id
AND iperiod.index_lease_id = p_index_lease_id
AND term.index_period_id IS NOT NULL);
DELETE FROM pn_payment_schedules_all sched
WHERE sched.payment_status_lookup_code = 'DRAFT'
AND NOT EXISTS( SELECT payment_schedule_id
FROM pn_payment_items_all item
WHERE item.payment_schedule_id = sched.payment_schedule_id);
DELETE FROM pn_distributions_all
WHERE payment_term_id IN (SELECT payment_term_id
FROM pn_payment_terms_all term,pn_index_lease_periods_all period
WHERE term.index_period_id = period.index_period_id
AND period.index_lease_id = p_index_lease_id);
DELETE FROM pn_payment_terms_all
WHERE index_period_id IN (SELECT index_period_id
FROM pn_index_lease_periods_all
WHERE index_lease_id = p_index_lease_id);
DELETE FROM pn_index_lease_periods_all
WHERE index_lease_id = p_index_lease_id ;
DELETE FROM pn_index_leases_all
WHERE index_lease_id = p_index_lease_id ;
put_log(' deleted agreements');
SELECT pld.lease_commencement_date
,pil.index_lease_number
,pil.assessment_date
,pil.commencement_date
,pil.termination_date
,pil.assessment_interval
,pil.relationship_default
,pil.basis_percent_default
,pil.index_finder_months
,nvl (pil.index_multiplier, 1) "INDEX_MULTIPLIER"
FROM pn_index_leases_all pil, pn_lease_details_all pld
WHERE pld.lease_id = pil.lease_id
AND pil.index_lease_id = ip_index_lease_id;
CURSOR il_recs_to_delete (ip_index_lease_id IN NUMBER
,ip_new_termination_date IN DATE)
IS
SELECT pilp.index_period_id
FROM pn_index_lease_periods_all pilp
WHERE pilp.index_lease_id = ip_index_lease_id
AND pilp.assessment_date > ip_new_termination_date;
SELECT MAX (assessment_date)
INTO v_latest_assessment_date
FROM pn_index_lease_periods_all
WHERE index_lease_id = p_index_lease_id;
FOR il_rec IN il_recs_to_delete (p_index_lease_id, v_new_termination_date)
LOOP
pn_index_lease_common_pkg.chk_for_approved_index_periods (
p_index_lease_id => p_index_lease_id
,p_index_lease_period_id => il_rec.index_period_id
,p_msg => v_msg);
FOR il_rec IN il_recs_to_delete (p_index_lease_id, v_new_termination_date)
LOOP
put_log('process new termination date'||il_rec.index_period_id);
delete_periods (
p_index_lease_id => p_index_lease_id
,p_index_period_id => il_rec.index_period_id
,p_ignore_approved_terms => 'ALL'
,p_new_termination_date => p_new_termination_date);
delete_periods (
p_index_lease_id => p_index_lease_id
,p_index_period_id => il_rec.index_period_id
,p_ignore_approved_terms => 'N');
SELECT pilp.index_period_id,
pilp.assessment_date,
pilp.basis_start_date,
ppt.payment_term_id,
ppt.start_date,
ppt.location_id,
ppt.payment_purpose_code,
ppt.frequency_code,
ppt.normalize,
ppt.index_term_indicator,
ppt.status,
ppt.currency_code,
ppt.actual_amount,
ppt.estimated_amount,
decode(ppt.actual_amount,null,ppt.estimated_amount,ppt.actual_amount) term_amount,
ppt.org_id,
ppt.norm_end_date
FROM pn_payment_terms_all ppt,
pn_index_lease_periods_all pilp
WHERE ppt.index_period_id = pilp.index_period_id
AND pilp.index_lease_id = p_index_lease_id
AND ppt.frequency_code = pn_index_amount_pkg.c_spread_frequency_one_time
AND ppt.index_term_indicator not in(pn_index_amount_pkg.c_index_pay_term_type_atlst_bb,
pn_index_amount_pkg.c_index_pay_term_type_backbill)
AND ppt.start_date = ppt.end_date
AND NVL( decode(ppt.actual_amount,null,ppt.estimated_amount,ppt.actual_amount),0 ) <> 0;
SELECT sum(ppi.actual_amount) total_amount
FROM pn_payment_items_all ppi
WHERE ppi.payment_term_id = p_payment_term_id
AND ppi.payment_item_type_lookup_code = 'CASH';
SELECT '1'
INTO v_approved_sch
FROM pn_payment_schedules_all pps
WHERE pps.payment_schedule_id IN
(SELECT ppt.payment_schedule_id
FROM pn_payment_items_all ppt
WHERE ppt.payment_term_id = rec_payment_terms.payment_term_id
AND ppt.export_currency_amount <> 0
AND ppt.payment_item_type_lookup_code = 'CASH')
AND pps.payment_status_lookup_code = 'APPROVED'
AND ROWNUM < 2;
UPDATE pn_payment_terms_all
SET actual_amount = v_new_amount,
last_update_date = SYSDATE,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE payment_term_id = rec_payment_terms.payment_term_id;
UPDATE pn_payment_items_all
SET actual_amount = v_new_amount,
export_currency_amount = v_new_amount,
last_update_date = SYSDATE,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE payment_term_id = rec_payment_terms.payment_term_id
AND export_currency_amount <> 0
AND payment_item_type_lookup_code = 'CASH';
UPDATE pn_payment_terms_all
SET estimated_amount = v_new_amount,
last_update_date = SYSDATE,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE payment_term_id = rec_payment_terms.payment_term_id;
UPDATE pn_payment_items_all
SET actual_amount = v_new_amount,
estimated_amount = v_new_amount,
export_currency_amount = v_new_amount,
last_update_date = SYSDATE,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE payment_term_id = rec_payment_terms.payment_term_id
AND export_currency_amount <> 0
AND payment_item_type_lookup_code = 'CASH';
update of the end date of any index rent payment term that were:
- that are non-approved.
- whose frequency is NOT one time...
end for
*/
CURSOR il_rec (
ip_lease_id IN NUMBER) IS
SELECT pil.index_lease_id
,pil.index_lease_number
,pil.assessment_date
,pil.commencement_date
,pil.termination_date
,pil.assessment_interval
,pil.relationship_default
,pil.basis_percent_default
,pil.rounding_flag
,pil.org_id
,pil.basis_type
,pil.reference_period
FROM pn_index_leases_all pil
WHERE pil.lease_id = ip_lease_id;
SELECT max(index_period_id) last_index_period_id, max(assessment_date) last_assessment_date
FROM pn_index_lease_periods_all
WHERE index_lease_id = p_index_lease_id;
SELECT index_lease_id
FROM pn_index_leases_all
WHERE lease_id = p_lease_id
AND termination_date = p_old_ls_end_date;
SELECT index_period_id
FROM pn_index_lease_periods_all
WHERE index_lease_id = p_index_lease_id
AND assessment_date > p_old_main_lease_term_date;
CURSOR get_term_4_mtm_update(p_index_lease_id NUMBER) IS
SELECT term.payment_term_id payment_term_id,
NVL(term.normalize,'N') normalize
FROM pn_payment_terms_all term,pn_index_lease_periods_all period
WHERE term.index_period_id = period.index_period_id
AND period.index_lease_id = p_index_lease_id
AND term.frequency_code <> pn_index_amount_pkg.c_spread_frequency_one_time
AND term.end_date = p_old_main_lease_term_date;
SELECT *
FROM pn_payment_terms_all
WHERE payment_term_id = p_term_id;
SELECT NVL(plh.lease_extension_end_date, plh.lease_termination_date) old_ls_end_date
FROM pn_lease_details_history plh,
pn_lease_details_all pld
WHERE pld.lease_change_id = plh.new_lease_change_id
AND pld.lease_id = p_lease_id;
SELECT lease_commencement_date
FROM pn_lease_details_all
WHERE lease_id = p_lease_id;
SELECT lease_num
FROM pn_leases_all
WHERE lease_id = p_lease_id;
SELECT *
FROM pn_payment_terms_all
WHERE payment_term_id IN ( SELECT payment_term_id
FROM pn_payment_terms_all terms, pn_index_lease_periods_all period
WHERE terms.index_period_id = period.index_period_id
AND period.index_lease_id = p_index_lease_id)
AND status ='APPROVED'
AND frequency_code <>'OT'
AND end_date = p_old_main_lease_term_date;
UPDATE pn_index_leases_all
SET termination_date = GREATEST(p_new_main_lease_term_date,commencement_date)
,last_update_date = SYSDATE
,last_updated_by = fnd_global.user_id
,last_update_login = fnd_global.login_id
WHERE index_lease_id = c_rec.index_lease_id;
UPDATE pn_payment_terms_all
SET end_date = p_new_main_lease_term_date,
last_update_date = SYSDATE,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE payment_term_id IN
(SELECT ppt.payment_term_id
FROM pn_payment_terms_all ppt, pn_index_lease_periods_all pilp
WHERE pilp.index_period_id = ppt.index_period_id
AND pilp.index_lease_id = c_rec.index_lease_id
AND p_lease_context <> 'CON'
AND ppt.frequency_code <>
pn_index_amount_pkg.c_spread_frequency_one_time
AND ppt.end_date > p_cutoff_date)
AND end_date = p_old_main_lease_term_date;
UPDATE pn_payment_terms_all
SET end_date = NVL(p_term_end_dt,p_new_main_lease_term_date),
last_update_date = SYSDATE,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE payment_term_id IN
(SELECT ppt.payment_term_id
FROM pn_payment_terms_all ppt, pn_index_lease_periods_all pilp
WHERE pilp.index_period_id = ppt.index_period_id
AND pilp.index_lease_id = c_rec.index_lease_id
AND (ppt.status = pn_index_amount_pkg.c_payment_term_status_draft AND
p_lease_context = 'CON')
AND ppt.frequency_code <>
pn_index_amount_pkg.c_spread_frequency_one_time);
UPDATE pn_payment_terms_all
SET end_date = least(NVL(p_term_end_dt,p_new_main_lease_term_date), v_last_period_assess_end_date),
last_update_date = SYSDATE,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE payment_term_id IN
(SELECT ppt.payment_term_id
FROM pn_payment_terms_all ppt, pn_index_lease_periods_all pilp
WHERE pilp.index_period_id = ppt.index_period_id
AND pilp.index_lease_id = c_rec.index_lease_id
AND (ppt.status = pn_index_amount_pkg.c_payment_term_status_draft AND
p_lease_context = 'CON')
AND ppt.frequency_code <>
pn_index_amount_pkg.c_spread_frequency_one_time)
AND index_period_id = v_max_index_period_id;
FOR terms_rec IN get_term_4_mtm_update(c_rec.index_lease_id) LOOP
FOR term_details_rec IN get_term_details(terms_rec.payment_term_id) LOOP
l_term_rec := term_details_rec;
UPDATE pn_payment_terms_all
SET end_date = p_new_main_lease_term_date,
last_update_date = SYSDATE,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE payment_term_id = terms_rec.payment_term_id;
UPDATE pn_index_leases_all
SET termination_date = p_new_main_lease_term_date,
last_update_date = SYSDATE,
last_updated_by = NVL(fnd_profile.value('USER_ID'),0),
last_update_login = NVL(fnd_profile.value('LOGIN_ID'),0)
WHERE index_lease_id = index_leases_rec.index_lease_id;
UPDATE pn_payment_terms_all
SET end_date = p_new_main_lease_term_date,
last_update_date = SYSDATE,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE payment_term_id = l_ri_term_rec.payment_term_id;
SELECT pil.index_lease_id
,pilp.index_period_id
,pilp.basis_start_date
,pilp.basis_end_date
,pilp.line_number
,pil.initial_basis
,pil.retain_initial_basis_flag
FROM pn_leases_all pl, pn_index_leases_all pil, pn_index_lease_periods_all pilp
WHERE pl.lease_id = pil.lease_id
AND pil.index_lease_id = pilp.index_lease_id
AND pl.lease_id=p_lease_id
AND exists(SELECT 'x'
FROM pn_index_leases_all pilx
WHERE ip_payment_end_date >=(SELECT min(pilpx.basis_start_date)
FROM pn_index_lease_periods_all pilpx
WHERE pilpx.index_lease_id=pilx.index_lease_id)
AND pilx.termination_date >= ip_payment_start_date
AND pilx.index_lease_id=pil.index_lease_id
AND (pilx.increase_on=ip_payment_type_code OR
pilx.gross_flag='Y')
);
UPDATE pn_index_leases_all
SET initial_basis = v_initial_basis_amt
,last_update_date = SYSDATE
,last_updated_by = NVL (fnd_profile.VALUE ('USER_ID'), 0)
WHERE index_lease_id = rec1.index_lease_id;
SELECT details.lease_change_id lease_change_id,
det_history.lease_status lease_status_old,
lease.lease_status lease_status_new,
details.lease_commencement_date lease_comm_date,
details.lease_termination_date lease_term_date,
det_history.lease_extension_end_date lease_ext_end_date,
changes.change_commencement_date amd_comm_date
FROM pn_lease_details_all details,
pn_lease_details_history det_history,
pn_lease_changes_all changes,
pn_leases_all lease
WHERE details.lease_id = p_lease_id
AND det_history.lease_id = p_lease_id
AND changes.lease_id = p_lease_id
AND lease.lease_id = p_lease_id
AND details.lease_change_id = det_history.new_lease_change_id
AND changes.lease_change_id = details.lease_change_id;
SELECT MAX(pps.schedule_date) lst_schedule_date
FROM pn_payment_schedules_all pps
WHERE pps.payment_status_lookup_code = 'APPROVED'
AND pps.lease_id = p_lease_id;
SELECT *
FROM pn_payment_terms_all terms
WHERE index_period_id IN ( SELECT index_period_id
FROM pn_index_leases_all
WHERE lease_id = p_lease_id)
AND end_date = p_term_end_date
AND lease_id = p_lease_id
AND frequency_code <> 'OT';
SELECT basis_type,
reference_period
FROM pn_index_leases_all ileases,
pn_index_lease_periods_all periods,
pn_payment_terms_all terms
WHERE ileases.index_lease_id = periods.index_lease_id
AND periods.index_period_id = terms.index_period_id
AND terms.payment_term_id = p_payment_term_id;
pn_schedules_items.Insert_Payment_Term
( p_payment_term_rec => l_term_rec,
x_return_status => x_return_status,
x_return_message => v_msg );
pn_schedules_items.Insert_Payment_Term
( p_payment_term_rec => l_term_rec,
x_return_status => x_return_status,
x_return_message => v_msg );
UPDATE pn_payment_terms_all
SET end_date = l_lease_term_date,
lease_change_id = l_lease_change_id,
last_update_date = SYSDATE,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE payment_term_id = l_term_rec.payment_term_id;
SELECT lease_id FROM pn_index_leases_all
WHERE index_lease_id = p_index_lease_id;
SELECT *
FROM pn_payment_terms_all
WHERE ((index_period_id IN (SELECT index_period_id
FROM pn_index_lease_periods_all
WHERE index_lease_id = p_index_lease_id))
OR
index_period_id IS NULL)
AND end_date = p_old_termination_date
AND frequency_code <>'OT'
AND status = 'APPROVED';
SELECT lease_commencement_date,
lease_termination_date,
ROUND(MONTHS_BETWEEN(lease_termination_date,lease_commencement_date))+1 p_mts
FROM pn_lease_details_all
WHERE lease_id IN (SELECT lease_id
FROM pn_index_leases_all
WHERE index_lease_id = p_index_lease_id);
SELECT payment_schedule_id
FROM pn_payment_schedules_all
WHERE lease_id IN ( SELECT lease_id
FROM pn_payment_terms_all
WHERE payment_term_id = p_payment_term_id)
AND payment_status_lookup_code = 'APPROVED'
AND payment_schedule_id IN (SELECT payment_schedule_id
FROM pn_payment_items_all
WHERE payment_term_id = p_payment_term_id);
SELECT SUM(ppi.actual_amount) AS total_amount
FROM pn_payment_items_all ppi
WHERE ppi.payment_term_id = p_term_id
AND ppi.payment_item_type_lookup_code = 'CASH';
SELECT pps.payment_schedule_id
FROM pn_payment_schedules_all pps
WHERE pps.schedule_date = p_sched_date
AND pps.lease_id IN (SELECT lease_id FROM pn_index_leases_all
WHERE index_lease_id = p_index_lease_id)
AND pps.payment_status_lookup_code = 'DRAFT';
SELECT payment_item_id
FROM pn_payment_items_all
WHERE payment_item_type_lookup_code = 'CASH'
AND payment_schedule_id = p_sched_id
AND payment_term_id = p_payment_term_id;
SELECT var_rent_id
FROM pn_var_rents_all
WHERE lease_id = p1_lease_id;
SELECT UPDATE_NBP_FLAG
FROM PN_PAYMENT_TERMS_ALL
WHERE lease_id = p1_lease_id
FOR UPDATE NOWAIT;
select 'x'
FROM DUAL
where exists (select BKHD_DEFAULT_ID
from pn_var_bkpts_head_all
where period_id IN (select PERIOD_ID
FROM pn_var_periods_all
where VAR_RENT_ID = l_var_rent_id)
AND BKHD_DEFAULT_ID IS NOT NULL);
l_update_nbp_flag VARCHAR2(1);
UPDATE pn_payment_terms_all
SET end_date = p_new_termination_date
WHERE payment_term_id = l_payment_term_rec.payment_term_id;
UPDATE pn_payment_terms_all
SET UPDATE_NBP_FLAG = 'Y'
WHERE payment_term_id = l_payment_term_rec.payment_term_id
AND INCLUDE_IN_VAR_RENT IN ('BASETERM', 'INCLUDE_RI');
UPDATE pn_payment_terms_all
SET end_date = p_new_termination_date,
start_date = p_new_termination_date,
actual_amount = 0
WHERE payment_term_id = l_payment_term_rec.payment_term_id;
UPDATE pn_payment_terms_all
SET UPDATE_NBP_FLAG = 'Y'
WHERE payment_term_id = l_payment_term_rec.payment_term_id
AND INCLUDE_IN_VAR_RENT IN ('BASETERM', 'INCLUDE_RI');
DELETE pn_payment_items_all
WHERE payment_schedule_id IN
(SELECT payment_schedule_id
FROM pn_payment_schedules_all
WHERE lease_id IN (SELECT lease_id FROM pn_payment_terms_all
WHERE payment_term_id = l_payment_term_rec.payment_term_id)
AND schedule_date > p_new_termination_date
AND payment_status_lookup_code IN ('DRAFT', 'ON_HOLD'))
AND payment_term_id = l_payment_term_rec.payment_term_id;
l_sched_tbl.DELETE;
pn_schedules_items.update_cash_item
(p_item_id => l_payment_item_id
,p_term_id => l_payment_term_rec.payment_term_id
,p_sched_id => l_payment_schedule_id
,p_act_amt => l_cash_act_amt);
DELETE pn_payment_items_all
WHERE payment_term_id = l_payment_term_rec.payment_term_id;
DELETE pn_distributions_all
WHERE payment_term_id = l_payment_term_rec.payment_term_id;
DELETE pn_payment_terms_all
WHERE payment_term_id = l_payment_term_rec.payment_term_id;
UPDATE pn_payment_terms_all
SET end_date = p_new_termination_date
WHERE payment_term_id = l_payment_term_rec.payment_term_id;
DELETE pn_payment_items_all
WHERE payment_schedule_id IN
(SELECT payment_schedule_id
FROM pn_payment_schedules_all
WHERE lease_id IN (SELECT lease_id FROM pn_payment_terms_all
WHERE payment_term_id = l_payment_term_rec.payment_term_id)
AND schedule_date > p_new_termination_date
AND payment_status_lookup_code IN ('DRAFT', 'ON_HOLD'))
AND payment_term_id = l_payment_term_rec.payment_term_id;
l_sched_tbl.DELETE;
pn_schedules_items.update_cash_item
( p_item_id => l_payment_item_id
,p_term_id => l_payment_term_rec.payment_term_id
,p_sched_id => l_payment_schedule_id
,p_act_amt => l_cash_act_amt);
l_update_nbp_flag := NULL;
IF terms_rec.UPDATE_NBP_FLAG = 'Y' THEN
l_update_nbp_flag := 'Y';
IF l_update_nbp_flag = 'Y' THEN
FOR var_rec in var_cur(p1_lease_id => l_lease_id)
LOOP
l_var_rent_id := var_rec.var_rent_id;
pnp_debug_pkg.log('Updated Natural Breakpoints for VR - '||var_rec.var_rent_id);
UPDATE pn_payment_terms_all
SET UPDATE_NBP_FLAG = NULL
WHERE lease_id = l_lease_id;