[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT ppt.payment_term_id,
ppt.lease_change_id,
ppt.schedule_day,
ppt.start_date,
ppt.end_date,
ppt.target_date,
ppt.frequency_code,
ppt.normalize,
ppt.actual_amount,
ppt.estimated_amount,
ppt.payment_term_type_code,
ppt.vendor_id,
ppt.vendor_site_id,
ppt.customer_id,
ppt.customer_site_use_id,
ppt.cust_ship_site_id,
ppt.set_of_books_id,
ppt.currency_code,
ppt.rate,
ppt.index_period_id,
ppt.norm_start_date
FROM pn_payment_terms_all ppt
WHERE ppt.lease_id = p_lease_id
AND ppt.var_rent_inv_id IS NULL
AND ppt.period_billrec_id IS NULL
AND EXISTS (SELECT NULL
FROM pn_payment_items_all ppi
WHERE ppt.lease_id = p_lease_id
AND ppi.payment_term_id = ppt.payment_term_id)
UNION
SELECT ppt.payment_term_id,
ppt.lease_change_id,
ppt.schedule_day,
ppt.start_date,
ppt.end_date,
ppt.target_date,
ppt.frequency_code,
ppt.normalize,
ppt.actual_amount,
ppt.estimated_amount,
ppt.payment_term_type_code,
ppt.vendor_id,
ppt.vendor_site_id,
ppt.customer_id,
ppt.customer_site_use_id,
ppt.cust_ship_site_id,
ppt.set_of_books_id,
ppt.currency_code,
ppt.rate,
ppt.index_period_id,
ppt.norm_start_date
FROM pn_payment_terms_all ppt
WHERE ppt.lease_id = p_lease_id
AND ppt.index_period_id IS NOT NULL
AND ppt.status = 'APPROVED'
AND ppt.index_term_indicator NOT IN ('BACKBILL','ATLEAST-BACKBILL')
AND EXISTS (SELECT NULL
FROM pn_payment_items_all ppi
WHERE ppt.lease_id = p_lease_id
AND ppi.payment_term_id = ppt.payment_term_id);
SELECT ppt.payment_term_id,
ppt.lease_change_id,
ppt.schedule_day,
ppt.start_date,
ppt.end_date,
ppt.target_date,
ppt.frequency_code,
ppt.normalize,
ppt.actual_amount,
ppt.estimated_amount,
ppt.payment_term_type_code,
ppt.vendor_id,
ppt.vendor_site_id,
ppt.customer_id,
ppt.customer_site_use_id,
ppt.cust_ship_site_id,
ppt.set_of_books_id,
ppt.currency_code,
ppt.rate,
ppt.norm_start_date,
ppt.norm_end_date
FROM pn_payment_terms_all ppt
WHERE ppt.lease_id = p_lease_id
AND ppt.payment_term_id = p_payment_term_id
AND EXISTS (SELECT NULL
FROM pn_payment_items_all ppi
WHERE ppt.lease_id = p_lease_id
AND ppi.payment_term_id = ppt.payment_term_id)
FOR UPDATE;
SELECT ppt.payment_term_id,
ppt.lease_change_id,
ppt.schedule_day,
ppt.start_date,
ppt.end_date,
ppt.target_date,
ppt.frequency_code,
ppt.normalize,
ppt.actual_amount,
ppt.estimated_amount,
ppt.payment_term_type_code,
ppt.vendor_id,
ppt.vendor_site_id,
ppt.customer_id,
ppt.customer_site_use_id,
ppt.cust_ship_site_id,
ppt.set_of_books_id,
ppt.currency_code,
ppt.rate,
ppt.index_period_id,
ppt.norm_start_date
FROM pn_payment_terms_all ppt
WHERE ppt.lease_id = p_lease_id
AND NVL(ppt.normalize,'N') = 'Y'
AND NVL(ppt.status,'APPROVED') = 'APPROVED'
AND EXISTS (SELECT NULL
FROM pn_payment_items_all ppi
WHERE ppt.lease_id = p_lease_id
AND ppi.payment_term_id = ppt.payment_term_id);
SELECT ppt.payment_term_id,
ppt.lease_change_id,
ppt.schedule_day,
ppt.start_date,
ppt.end_date,
ppt.target_date,
ppt.frequency_code,
ppt.normalize,
ppt.actual_amount,
ppt.estimated_amount,
ppt.payment_term_type_code,
ppt.vendor_id,
ppt.vendor_site_id,
ppt.customer_id,
ppt.customer_site_use_id,
ppt.cust_ship_site_id,
ppt.set_of_books_id,
ppt.currency_code,
ppt.rate,
ppt.index_period_id,
ppt.norm_start_date
FROM pn_payment_terms_all ppt
WHERE ppt.lease_id = p_lease_id
AND ppt.var_rent_inv_id IS NULL
AND ppt.period_billrec_id IS NULL
AND (NVL(ppt.normalize,'N') = 'Y' AND NVL(ppt.norm_end_date, ppt.end_date) > p_cutoff_date) /* 6699877 */
AND EXISTS (SELECT NULL
FROM pn_payment_items_all ppi
WHERE ppt.lease_id = p_lease_id
AND ppi.payment_term_id = ppt.payment_term_id)
UNION
SELECT ppt.payment_term_id,
ppt.lease_change_id,
ppt.schedule_day,
ppt.start_date,
ppt.end_date,
ppt.target_date,
ppt.frequency_code,
ppt.normalize,
ppt.actual_amount,
ppt.estimated_amount,
ppt.payment_term_type_code,
ppt.vendor_id,
ppt.vendor_site_id,
ppt.customer_id,
ppt.customer_site_use_id,
ppt.cust_ship_site_id,
ppt.set_of_books_id,
ppt.currency_code,
ppt.rate,
ppt.index_period_id,
ppt.norm_start_date
FROM pn_payment_terms_all ppt
WHERE ppt.lease_id = p_lease_id
AND ppt.index_period_id IS NOT NULL
AND ppt.status = 'APPROVED'
AND ppt.index_term_indicator NOT IN ('BACKBILL','ATLEAST-BACKBILL')
AND (( ppt.normalize = 'Y' AND NVL(ppt.norm_end_date, ppt.end_date) > p_cutoff_date) OR
( NVL(ppt.normalize,'N') = 'N' AND ppt.end_date > p_cutoff_date))
AND EXISTS (SELECT NULL
FROM pn_payment_items_all ppi
WHERE ppt.lease_id = p_lease_id
AND ppi.payment_term_id = ppt.payment_term_id);
SELECT ppt.payment_term_id,
ppt.lease_change_id,
ppt.schedule_day,
ppt.start_date,
ppt.end_date,
ppt.target_date,
ppt.frequency_code,
ppt.normalize,
ppt.actual_amount,
ppt.estimated_amount,
ppt.payment_term_type_code,
ppt.vendor_id,
ppt.vendor_site_id,
ppt.customer_id,
ppt.customer_site_use_id,
ppt.cust_ship_site_id,
ppt.set_of_books_id,
ppt.currency_code,
ppt.rate,
ppt.norm_start_date,
plc.change_type_lookup_code,
plc.change_commencement_date
FROM pn_payment_terms_all ppt,
pn_lease_changes_all plc
WHERE ppt.lease_id = p_lease_id
AND ppt.lease_change_id = plc.lease_change_id
AND ppt.index_period_id IS NULL
AND ppt.var_rent_inv_id IS NULL
AND ppt.period_billrec_id IS NULL
AND plc.change_type_lookup_code IN ('EDIT', 'AMEND')
AND NOT EXISTS (SELECT NULL
FROM pn_payment_items_all ppi
WHERE ppt.lease_id = p_lease_id
AND ppi.payment_term_id = ppt.payment_term_id);
SELECT ppt.payment_term_id,
ppt.lease_change_id,
ppt.schedule_day,
ppt.start_date,
ppt.end_date,
ppt.target_date,
ppt.frequency_code,
ppt.normalize,
ppt.actual_amount,
ppt.estimated_amount,
ppt.payment_term_type_code,
ppt.vendor_id,
ppt.vendor_site_id,
ppt.customer_id,
ppt.customer_site_use_id,
ppt.cust_ship_site_id,
ppt.set_of_books_id,
ppt.currency_code,
ppt.rate,
ppt.norm_start_date
FROM pn_payment_terms_all ppt
WHERE ppt.payment_term_id = p_term_id
AND NOT EXISTS (SELECT NULL
FROM pn_payment_items_all ppi
WHERE ppt.lease_id = p_lease_id
AND ppi.payment_term_id = p_term_id);
SELECT ppt.payment_term_id,
ppt.lease_change_id,
ppt.schedule_day,
ppt.start_date,
ppt.end_date,
ppt.target_date,
ppt.frequency_code,
ppt.normalize,
ppt.actual_amount,
ppt.estimated_amount,
ppt.payment_term_type_code,
ppt.vendor_id,
ppt.vendor_site_id,
ppt.customer_id,
ppt.customer_site_use_id,
ppt.cust_ship_site_id,
ppt.set_of_books_id,
ppt.currency_code,
ppt.rate,
ppt.norm_start_date
FROM pn_payment_terms_all ppt
WHERE lease_id = p_lease_id
AND ppt.index_period_id IS NULL
AND ppt.var_rent_inv_id IS NULL
AND ppt.period_billrec_id IS NULL
AND NOT EXISTS (SELECT NULL
FROM pn_payment_items_all ppi
WHERE ppt.lease_id = p_lease_id
AND ppi.payment_term_id = ppt.payment_term_id);
SELECT ppt.payment_term_id,
ppt.lease_change_id,
ppt.schedule_day,
ppt.start_date,
ppt.end_date,
ppt.target_date,
ppt.frequency_code,
ppt.normalize,
ppt.actual_amount,
ppt.estimated_amount,
ppt.payment_term_type_code,
ppt.vendor_id,
ppt.vendor_site_id,
ppt.customer_id,
ppt.customer_site_use_id,
ppt.cust_ship_site_id,
ppt.set_of_books_id,
ppt.currency_code,
ppt.rate,
ppt.norm_start_date,
ppt.norm_end_date
FROM pn_payment_terms_all ppt
WHERE ppt.lease_id = p_lease_id
AND ppt.changed_flag = 'Y'
AND EXISTS (SELECT NULL
FROM pn_payment_items_all ppi
WHERE ppt.lease_id = p_lease_id
AND ppi.payment_term_id = ppt.payment_term_id)
FOR UPDATE;
SELECT max(lease_change_id)
FROM pn_lease_changes_all
WHERE lease_id = p_lease_id;
SELECT change_commencement_date
FROM pn_lease_changes_all
WHERE lease_change_id = p_lease_change_id;
SELECT NVL(SUM(ppi.actual_amount),0)
FROM pn_payment_items_all ppi,
pn_payment_schedules_all pps,
pn_payment_terms_all ppt
WHERE ppt.payment_term_id = ppi.payment_term_id
AND ppt.lease_id = p_lease_id
AND ppi.payment_item_type_lookup_code = 'NORMALIZED'
AND pps.payment_schedule_id = ppi.payment_schedule_id
AND pps.payment_status_lookup_code = 'APPROVED'
AND normalize = 'Y'
AND first_day(pps.schedule_date) > first_day(l_commencement_date);
SELECT ppt.lease_id,
pps.schedule_date,
ppi.due_date,
ppi.payment_term_id,
ppi.period_fraction,
ppi.vendor_id,
ppi.customer_id,
ppi.vendor_site_id,
ppi.customer_site_use_id,
ppi.cust_ship_site_id,
ppi.set_of_books_id,
ppi.currency_code,
ppi.export_currency_code,
ppi.rate,
ppi.org_id,
ppi.grouping_rule_id,
sum(ppi.actual_amount) actual_amount,
sum(nvl(ppi.export_currency_amount,0)) export_currency_amount
FROM pn_payment_items_all ppi,
pn_payment_schedules_all pps,
pn_payment_terms_all ppt
WHERE ppt.payment_term_id = ppi.payment_term_id
AND ppt.lease_id = p_lease_id
AND ppt.normalize = 'Y'
AND ppi.payment_item_type_lookup_code = 'NORMALIZED'
AND pps.payment_schedule_id = ppi.payment_schedule_id
AND ppi.actual_amount <> 0
AND first_day(pps.schedule_date) < first_day(l_commencement_date)
AND EXISTS (SELECT 'x'
FROM pn_payment_schedules_all ppsa
WHERE ppsa.lease_id = p_lease_id
AND ppsa.schedule_date = pps.schedule_date
AND first_day(pps.schedule_date) < first_day(l_commencement_date)
AND ppsa.payment_status_lookup_code = 'APPROVED')
GROUP BY ppt.lease_id,
pps.schedule_date,
ppi.due_date,
ppi.payment_term_id,
ppi.period_fraction,
ppi.vendor_id,
ppi.customer_id,
ppi.vendor_site_id,
ppi.customer_site_use_id,
ppi.cust_ship_site_id,
ppi.set_of_books_id,
ppi.currency_code,
ppi.export_currency_code,
ppi.rate,
ppi.org_id,
ppi.grouping_rule_id
HAVING sum(nvl(ppi.actual_amount,0)) <> 0
ORDER BY ppi.payment_term_id,schedule_date;
SELECT name,lease_num,
c.lease_id,
a.payment_term_id,
a.frequency_code,
a.actual_amount,
a.vendor_id,
a.customer_id,
a.vendor_site_id,
a.customer_site_use_id,
a.cust_ship_site_id,
a.set_of_books_id,
a.currency_code,
a.rate,
a.org_id,
a.grouping_rule_id,
sum(decode(payment_item_type_lookup_code,'CASH',b.actual_amount,0)) cash ,
sum(decode(payment_item_type_lookup_code,'NORMALIZED',b.actual_amount,0)) norm,
sum(decode(payment_item_type_lookup_code,'CASH',b.actual_amount,0)) -
sum(decode(payment_item_type_lookup_code,'NORMALIZED',b.actual_amount,0)) diff_act_amt,
sum(decode(payment_item_type_lookup_code,'CASH',b.export_currency_amount,0)) -
sum(decode(payment_item_type_lookup_code,'NORMALIZED',b.export_currency_amount,0)) diff_exp_amt
FROM pn_payment_terms_all a,
pn_payment_items_all b,
pn_leases_all c
WHERE a.payment_term_id = b.payment_term_id
AND a.normalize = 'Y'
AND a.lease_id = c.lease_id
AND c.lease_id = p_lease_id
GROUP by name,lease_num,c.lease_id,a.payment_term_id,a.frequency_code,a.actual_amount,
a.vendor_id,a.customer_id,a.vendor_site_id,a.customer_site_use_id, a.cust_ship_site_id,a.set_of_books_id,
a.currency_code,a.rate,a.org_id,a.grouping_rule_id
HAVING SUM(decode(payment_item_type_lookup_code,'CASH',b.actual_amount,0)) <>
SUM(DECODE(payment_item_type_lookup_code,'NORMALIZED',b.actual_amount,0))
ORDER BY a.payment_term_id;
SELECT MAX(c.payment_item_id)
FROM pn_payment_terms_all a,
pn_payment_schedules_all b,
pn_payment_items_all c
WHERE a.lease_id = p_lease_id
AND a.payment_term_id = p_payment_term_id
AND a.normalize = 'Y'
AND a.payment_term_id = c.payment_term_id
AND b.payment_schedule_id = c.payment_schedule_id
AND b.payment_status_lookup_code = 'DRAFT'
AND c.payment_item_type_lookup_code = 'NORMALIZED';
UPDATE pn_payment_items_all
SET actual_amount = actual_amount + term_rec.diff_act_amt,
export_currency_amount = export_currency_amount + term_rec.diff_exp_amt
WHERE payment_item_id = lp_payment_item_id;
SELECT MIN(due_date)
FROM pn_payment_items_all
WHERE payment_term_id = p_payment_term_id
AND payment_item_type_lookup_code = 'CASH';
SELECT lease_change_id, lease_termination_date, lease_status
FROM pn_lease_details_history
WHERE lease_id = p_lease_id
ORDER BY 1 DESC;
select DECODE(p_freq_code,'QTR',3,'SA',6,'YR',12)
into period_duration from dual;
SELECT MAX(pps.schedule_date), COUNT(pps.schedule_date)
FROM pn_payment_schedules_all pps,
pn_payment_items_all ppi
WHERE ppi.payment_term_id = p_term_id
AND pps.payment_schedule_id = ppi.payment_schedule_id
AND ppi.actual_amount <> 0
AND ppi.payment_item_type_lookup_code = 'CASH';
SELECT First_Day(MIN(pps.schedule_date))
FROM pn_payment_schedules_all pps
WHERE pps.lease_id = p_lease_id
AND First_Day(pps.schedule_date) >= First_Day(p_start_date)
AND TO_CHAR(pps.schedule_date,'DD') = p_sch_day
AND pps.payment_status_lookup_code = 'DRAFT';
SELECT ADD_MONTHS(First_Day(MAX(pps.schedule_date)) , 1)
FROM pn_payment_schedules_all pps
WHERE pps.lease_id = p_lease_id
AND First_Day(pps.schedule_date) >= First_Day(p_start_date)
AND TO_CHAR(pps.schedule_date,'DD') = p_sch_day
AND pps.payment_status_lookup_code = 'APPROVED';
SELECT cal_start
INTO l_cal_yr_st
FROM pn_leases_all
WHERE lease_id = p_lease_id;
select norm_start_date /* 9231686 */
into l_norm_str_dt
from pn_payment_terms_all
where payment_term_id = p_term_id;
/* Get the first date for which a non-zero cash item has to inserted in case of
normalized payment terms. */
p_pro_sch_dt := p_sch_dt;
/* Get the first date for which a non-zero cash item has to inserted in case of
normalized payment terms. */
IF p_lease_context in ('ABS','ADD', 'ADDEDT','ADDAMD') THEN
p_pro_sch_dt := TO_DATE(p_sch_day||'/'||TO_CHAR(p_term_start_dt,'MM/YYYY'),'DD/MM/YYYY');
SELECT payment_schedule_id,
payment_status_lookup_code
FROM pn_payment_schedules_all
WHERE schedule_date = p_sch_dt
AND lease_id = p_lease_id
ORDER BY payment_status_lookup_code DESC;
SELECT org_id
FROM pn_leases_all
WHERE lease_id = p_lease_id;
SELECT sched.payment_schedule_id, payment_status_lookup_code
FROM pn_payment_items_all item,
pn_payment_schedules_all sched
WHERE item.payment_term_id = p_payment_term_id
AND item.payment_schedule_id = sched.payment_schedule_id
AND sched.schedule_date = p_sch_dt;
SELECT pn_payment_schedules_s.NEXTVAL
INTO l_sch_id
FROM DUAL;
INSERT INTO pn_payment_schedules_all
(payment_schedule_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
schedule_date,
lease_id,
lease_change_id,
payment_status_lookup_code,
org_id)
VALUES
(l_sch_id,
SYSDATE,
NVL(fnd_profile.value('USER_ID'),0),
SYSDATE,
NVL(fnd_profile.value('USER_ID'),0),
NVL(fnd_profile.value('LOGIN_ID'),0),
p_sch_dt,
p_lease_id,
p_lc_id,
l_pymnt_st_lkp_cd,
l_org_id);
SELECT payment_schedule_id,
payment_status_lookup_code
INTO l_sch_id, l_pymnt_st_lkp_cd
FROM pn_payment_schedules_all
WHERE lease_id = p_lease_id
AND schedule_date = l_schd_date
AND payment_status_lookup_code = l_pymnt_st_lkp_cd
AND ROWNUM = 1;
pnp_debug_pkg.log('create_schedule: select executed' );
SELECT pn_payment_schedules_s.NEXTVAL INTO l_sch_id
FROM DUAL;
INSERT INTO pn_payment_schedules_all(
payment_schedule_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
schedule_date,
lease_id,
lease_change_id,
payment_status_lookup_code,
org_id)
VALUES (l_sch_id,
SYSDATE,
NVL(fnd_profile.value('USER_ID'),0),
SYSDATE,
NVL(fnd_profile.value('USER_ID'),0),
NVL(fnd_profile.value('LOGIN_ID'),0),
l_schd_date,
p_lease_id,
p_lc_id,
l_pymnt_st_lkp_cd,
l_org_id);
SELECT org_id
FROM pn_payment_terms_all
WHERE payment_term_id = p_term_id;
SELECT recur_bb_calc_date
FROM pn_payment_terms_all
WHERE payment_term_id = p_term_id
AND recur_bb_calc_date IS NOT NULL;
SELECT grouping_rule_id
FROM pn_payment_terms_all
WHERE payment_term_id = p_term_id;
before inserting */
fnd_currency.get_info(p_curr_code, l_precision, l_ext_precision, l_min_acct_unit);
INSERT INTO pn_payment_items_all
(payment_item_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
actual_amount,
estimated_amount,
due_date,
payment_item_type_lookup_code,
payment_term_id,
payment_schedule_id,
period_fraction,
vendor_id,
customer_id,
vendor_site_id,
customer_site_use_id,
cust_ship_site_id,
set_of_books_id,
currency_code,
export_currency_code,
export_currency_amount,
rate,
org_id,
grouping_rule_id)
VALUES
(PN_PAYMENT_ITEMS_S.NEXTVAL,
SYSDATE,
NVL(fnd_profile.value('USER_ID'),0),
SYSDATE,
NVL(fnd_profile.value('USER_ID'),0),
NVL(fnd_profile.value('LOGIN_ID'),0),
ROUND(p_act_amt,l_precision),
ROUND(p_est_amt,l_precision),
l_sch_dt,
'CASH',
p_term_id,
p_sch_id,
1,
p_vendor_id,
p_cust_id,
p_vendor_site_id,
p_cust_site_use_id,
p_cust_ship_site_id,
p_sob_id,
p_curr_code,
p_curr_code,
ROUND(p_act_amt, l_precision),
p_rate,
l_org_id,
l_inv_grp_rule)
RETURNING payment_item_id, actual_amount INTO l_payment_item_id, l_actual_amount;
l_rows_updated NUMBER := 0;
SELECT org_id
FROM pn_payment_terms_all
WHERE payment_term_id = l_term_id;
SELECT distinct schedule_date
FROM pn_payment_items_all ppi,
pn_payment_schedules_all pps
WHERE ppi.payment_term_id = p_term_id
AND ppi.payment_item_type_lookup_code = 'NORMALIZED'
AND pps.payment_schedule_id = ppi.payment_schedule_id
AND pps.payment_status_lookup_code <> 'DRAFT'
and schedule_date >= first_day(l_amd_comn_date)/*Bug 13474500 */
and schedule_date <= (SELECT lease_termination_date from
pn_lease_details_all where lease_id = p_lease_id ); /* 7149537 */
select distinct ppi.payment_schedule_id
from pn_payment_items_all ppi
,pn_payment_schedules_all pps
where exists
(select 1
from pn_payment_items_all ppi , pn_payment_schedules_all pps
where ppi.payment_term_id = p_term_id
and pps.payment_schedule_id = ppi.payment_schedule_id
and payment_status_lookup_code <> 'DRAFT'
and due_date = l_norm_sch_dt
)
and pps.payment_schedule_id = ppi.payment_schedule_id
and ppi.payment_term_id = p_term_id
AND ppi.payment_item_type_lookup_code = 'NORMALIZED'
AND payment_status_lookup_code = 'DRAFT'
and due_date = l_norm_sch_dt
and rownum = 1; /* 7149537 */
SELECT recur_bb_calc_date
FROM pn_payment_terms_all
WHERE payment_term_id = p_term_id
AND recur_bb_calc_date IS NOT NULL ;
SELECT grouping_rule_id
FROM pn_payment_terms_all
WHERE payment_term_id = p_term_id;
SELECT lease_commencement_date
INTO l_lease_commence_dt
FROM pn_lease_details_all
WHERE lease_id=p_lease_id; --13773023
SELECT change_commencement_date
INTO l_amd_comn_date
FROM
(SELECT *
FROM pn_lease_changes_all
WHERE lease_id = p_lease_id
ORDER BY lease_change_id DESC)
WHERE rownum < 2;
Select NVL(SUM(ppi.actual_amount),0)
into l_term_amt
FROM pn_payment_items_all ppi,
pn_payment_schedules_all pps
WHERE ppi.payment_term_id = p_term_id
AND ppi.payment_item_type_lookup_code = 'NORMALIZED'
AND pps.payment_schedule_id = ppi.payment_schedule_id
AND pps.payment_status_lookup_code <> 'DRAFT'
AND to_char(schedule_date,'MON-YY') = to_char(l_amd_comn_date,'MON-YY'); /* 9231686 */
SELECT NVL(SUM(ppi.actual_amount),0)
INTO l_app_amt
FROM pn_payment_items_all ppi,
pn_payment_schedules_all pps
WHERE ppi.payment_term_id = p_term_id
AND ppi.payment_item_type_lookup_code = 'NORMALIZED'
AND pps.payment_schedule_id = ppi.payment_schedule_id
AND pps.payment_status_lookup_code <> 'DRAFT'
and schedule_date >= FIRST_DAY(l_amd_comn_date)
and schedule_date <= (SELECT lease_termination_date from
pn_lease_details_all where lease_id = p_lease_id ); /* 7149537 */
SELECT NVL(SUM(ppi.actual_amount),0)
INTO l_dft_amt
FROM pn_payment_items_all ppi,
pn_payment_schedules_all pps
WHERE ppi.payment_term_id = p_term_id
AND ppi.payment_item_type_lookup_code = 'NORMALIZED'
AND pps.payment_schedule_id = ppi.payment_schedule_id
AND pps.payment_status_lookup_code = 'DRAFT'
AND schedule_date < FIRST_DAY(l_amd_comn_date);
SELECT SUM(ppi.actual_amount)
INTO l_total_cash_amt
FROM pn_payment_items_all ppi
WHERE ppi.payment_term_id = p_term_id
AND ppi.payment_item_type_lookup_code = 'CASH';
SELECT NVL(SUM(ppi.actual_amount),0)
INTO l_la_amt
FROM pn_payment_items_all ppi,
pn_payment_schedules_all pps
WHERE ppi.payment_term_id = p_term_id
AND ppi.payment_item_type_lookup_code = 'NORMALIZED'
AND pps.payment_schedule_id = ppi.payment_schedule_id
AND pps.payment_status_lookup_code <> 'DRAFT'; --Bug#6825797 'APPROVED';
SELECT LEAST(p_norm_end_dt, NVL(LAST_DAY(MAX(pps.schedule_date)), p_norm_end_dt))
INTO l_norm_end_dt
FROM pn_payment_items_all ppi,
pn_payment_schedules_all pps
WHERE ppi.payment_term_id = p_term_id
AND pps.payment_schedule_id = ppi.payment_schedule_id
AND pps.payment_status_lookup_code = 'DRAFT';
SELECT COUNT(*)
INTO l_app_sch
FROM pn_payment_items_all ppi,
pn_payment_schedules_all pps
WHERE ppi.payment_term_id = p_term_id
AND pps.payment_schedule_id = ppi.payment_schedule_id
AND pps.payment_status_lookup_code = 'APPROVED'
AND ppi.payment_item_type_lookup_code = 'CASH'
AND pps.schedule_date between First_Day(p_norm_str_dt) and LAST_DAY(l_norm_end_dt)
AND NOT EXISTS (
SELECT 1
FROM pn_payment_schedules_all ppsi,
pn_payment_items_all ppii
WHERE ppsi.schedule_date = pps.schedule_date
AND ppii.payment_term_id = ppi.payment_term_id
AND ppsi.payment_schedule_id = ppii.payment_schedule_id
AND ppsi.payment_status_lookup_code = 'DRAFT'
);
DELETE pn_payment_items_all ppi
WHERE ppi.payment_schedule_id IN (SELECT pps.payment_schedule_id
FROM pn_payment_items_all ppi1,
pn_payment_schedules_all pps
WHERE ppi1.payment_term_id = p_term_id
AND ppi1.payment_item_type_lookup_code = 'CASH'
AND pps.payment_schedule_id = ppi1.payment_schedule_id
AND pps.payment_status_lookup_code = 'DRAFT'
AND pps.schedule_date >= FIRST_DAY(l_amd_comn_date)) --Draft Schedules should not be deleted before ACD
AND ppi.payment_item_type_lookup_code = 'NORMALIZED'
AND ppi.payment_term_id = p_term_id;
pnp_debug_pkg.log('create_normalize_items - deleted normalized items for EXP');
SELECT NVL(sum(actual_amount),0) /* 9457938 */
into l_act_amt
FROM pn_payment_items_all ppi,
pn_payment_schedules_all pps
WHERE ppi.payment_item_type_lookup_code = 'NORMALIZED'
AND pps.payment_schedule_id = ppi.payment_schedule_id
AND ppi.payment_term_id = p_term_id
AND pps.payment_status_lookup_code <> 'DRAFT'
AND due_date = l_sch_date_1;
select 1 /* 7149537 */
into l_draft_exist
from dual
where exists(select * from pn_payment_items_all
where due_date = l_norm_sch_dt
and payment_term_id = p_term_id
and payment_item_type_lookup_code = 'CASH'
and payment_schedule_id = l_sch_id);
INSERT INTO pn_payment_items_all
(payment_item_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
actual_amount,
estimated_amount,
due_date,
payment_item_type_lookup_code,
payment_term_id,
payment_schedule_id,
period_fraction,
vendor_id,
customer_id,
vendor_site_id,
customer_site_use_id,
cust_ship_site_id,
set_of_books_id,
currency_code,
export_currency_code,
export_currency_amount,
rate,
org_id,
grouping_rule_id)
VALUES
(PN_PAYMENT_ITEMS_S.NEXTVAL,
SYSDATE,
NVL(fnd_profile.value('USER_ID'),0),
SYSDATE,
NVL(fnd_profile.value('USER_ID'),0),
NVL(fnd_profile.value('LOGIN_ID'),0),
l_prec_norm_amt,
NULL,
l_norm_sch_dt,
'NORMALIZED',
p_term_id,
l_sch_id,
1,
p_vendor_id,
p_cust_id,
p_vendor_site_id,
p_cust_site_use_id,
p_cust_ship_site_id,
p_sob_id,
p_curr_code,
p_curr_code,
l_prec_norm_amt,
p_rate,
l_org_id,
l_inv_grp_rule);
pnp_debug_pkg.log('INSERTED');
SELECT NVL(sum(actual_amount),0)
into l_act_amt
FROM pn_payment_items_all ppi,
pn_payment_schedules_all pps
WHERE ppi.payment_item_type_lookup_code = 'NORMALIZED'
AND pps.payment_schedule_id = ppi.payment_schedule_id
AND ppi.payment_term_id = p_term_id
AND pps.payment_status_lookup_code <> 'DRAFT'
AND due_date = l_norm_sch_dt;
UPDATE pn_payment_items_all ppi
SET ppi.actual_amount = l_prec_norm_amt,
ppi.export_currency_amount = l_prec_norm_amt,
ppi.last_update_date = SYSDATE,
ppi.last_updated_by = NVL(fnd_profile.value('USER_ID'),0),
ppi.last_update_login= NVL(fnd_profile.value('LOGIN_ID'),0)
WHERE ppi.payment_schedule_id = l_sch_id_1
AND ppi.payment_item_type_lookup_code = 'NORMALIZED'
AND ppi.payment_term_id = p_term_id;
pnp_debug_pkg.log('UPDATED -l_prec_norm_amt : '||TO_CHAR(l_prec_norm_amt));
UPDATE pn_payment_items_all ppi
SET ppi.actual_amount = l_prec_norm_amt,
ppi.export_currency_amount = l_prec_norm_amt,
ppi.last_update_date = SYSDATE,
ppi.last_updated_by = NVL(fnd_profile.value('USER_ID'),0),
ppi.last_update_login= NVL(fnd_profile.value('LOGIN_ID'),0)
WHERE ppi.payment_schedule_id IN (SELECT pps.payment_schedule_id
FROM pn_payment_schedules_all pps,
pn_payment_items_all ppi1
WHERE pps.payment_schedule_id = ppi1.payment_schedule_id
AND pps.payment_status_lookup_code = 'DRAFT'
AND ppi1.payment_term_id = p_term_id)
AND ppi.payment_item_type_lookup_code = 'NORMALIZED'
AND ppi.payment_term_id = p_term_id;
l_rows_updated := SQL%ROWCOUNT;
l_tot_cum_norm_amt := l_rows_updated * l_prec_norm_amt_upd;
UPDATE pn_payment_items_all ppi
SET ppi.actual_amount = l_prec_norm_amt,
ppi.export_currency_amount = l_prec_norm_amt,
ppi.last_update_date = SYSDATE,
ppi.last_updated_by = NVL(fnd_profile.value('USER_ID'),0),
ppi.last_update_login= NVL(fnd_profile.value('LOGIN_ID'),0)
WHERE ppi.payment_schedule_id = (SELECT pps.payment_schedule_id
FROM pn_payment_schedules_all pps,
pn_payment_items_all ppi1
WHERE pps.payment_schedule_id = ppi1.payment_schedule_id
AND pps.payment_status_lookup_code = 'DRAFT'
AND ppi1.payment_term_id = p_term_id
AND ppi1.payment_item_type_lookup_code = 'NORMALIZED'
AND pps.schedule_date = l_norm_sch_dt)
AND ppi.payment_item_type_lookup_code = 'NORMALIZED'
AND ppi.payment_term_id = p_term_id;
UPDATE pn_payment_items_all ppi
SET ppi.actual_amount = l_prec_norm_amt,
ppi.export_currency_amount = l_prec_norm_amt,
ppi.last_update_date = SYSDATE,
ppi.last_updated_by = NVL(fnd_profile.value('USER_ID'),0),
ppi.last_update_login= NVL(fnd_profile.value('LOGIN_ID'),0)
WHERE ppi.payment_schedule_id = (SELECT pps.payment_schedule_id
FROM pn_payment_schedules_all pps,
pn_payment_items_all ppi1
WHERE pps.payment_schedule_id = ppi1.payment_schedule_id
AND pps.payment_status_lookup_code = 'DRAFT'
AND ppi1.payment_term_id = p_term_id
AND ppi1.payment_item_type_lookup_code = 'NORMALIZED'
AND pps.schedule_date = l_last_sch_dt)
AND ppi.payment_item_type_lookup_code = 'NORMALIZED'
AND ppi.payment_term_id = p_term_id;
UPDATE pn_payment_terms_all
SET event_type_code = p_lease_context
WHERE normalize ='Y'
AND payment_term_id = p_term_id;
SELECT cal_start
INTO l_cal_yr_st_dt
FROM PN_LEASES_ALL
WHERE LEASE_ID = (select distinct lease_id from pn_payment_terms_all where payment_term_id = p_payment_term_id);
/* Insert the Negation/Adjustment amount with different sign */
IF l_pymnt_st_lkp_cd = 'DRAFT' THEN
create_cash_items(p_est_amt => (-1 * l_cash_est_amt),
p_act_amt => (-1 * l_cash_act_amt),
p_sch_dt => p_trgt_dt,
p_sch_id => l_sch_id,
p_term_id => p_payment_term_id,
p_vendor_id => p_vendor_id,
p_cust_id => p_customer_id,
p_vendor_site_id => p_vendor_site_id,
p_cust_site_use_id => p_customer_site_use_id,
p_cust_ship_site_id => p_cust_ship_site_id,
p_sob_id => p_set_of_books_id,
p_curr_code => p_currency_code,
p_rate => p_rate);
PROCEDURE update_cash_item (p_term_id NUMBER,
p_term_str_dt DATE,
p_term_end_dt DATE,
p_schedule_dt DATE,
p_sch_str_dt DATE,
p_sch_end_dt DATE,
p_act_amt NUMBER,
p_est_amt NUMBER,
p_freq NUMBER)
IS
l_cash_act_amt NUMBER;
SELECT currency_code FROM pn_payment_terms_all WHERE payment_term_id = p_term_id;
pnp_debug_pkg.log('update_cash_item +Start+ (+)');
pnp_debug_pkg.log('update_cash_item IN: p_term_id : '||p_term_id);
pnp_debug_pkg.log('update_cash_item IN: p_term_str_dt : '||p_term_str_dt);
pnp_debug_pkg.log('update_cash_item IN: p_term_end_dt : '||p_term_end_dt);
pnp_debug_pkg.log('update_cash_item IN: p_schedule_dt : '||p_schedule_dt);
pnp_debug_pkg.log('update_cash_item IN: p_sch_str_dt : '||p_sch_str_dt);
pnp_debug_pkg.log('update_cash_item IN: p_sch_end_dt : '||p_sch_end_dt);
pnp_debug_pkg.log('update_cash_item IN: p_act_amt : '||p_act_amt);
pnp_debug_pkg.log('update_cash_item IN: p_est_amt : '||p_est_amt);
pnp_debug_pkg.log('update_cash_item IN: p_freq : '||p_freq);
/* update the cash amount for the schedule */
FOR rec IN currency_cur LOOP
fnd_currency.get_info( currency_code => rec.currency_code
,precision => l_precision
,ext_precision => l_ext_precision
,min_acct_unit => l_min_acct_unit);
select nvl(sum(actual_amount),0),
nvl(sum(estimated_amount),0),
nvl(sum(export_currency_amount),0)
into l_cash_act_amt_appr,
l_cash_est_amt_appr,
l_cash_exp_amt_appr
from pn_payment_items_all ppi,
pn_payment_schedules_all pps
where pps.lease_id = g_lease_id
and pps.payment_status_lookup_code = 'APPROVED'
and pps.payment_schedule_id = ppi.payment_schedule_id
and ppi.payment_term_id = p_term_id
and ppi.payment_item_type_lookup_code = 'CASH'
and pps.schedule_date = p_schedule_dt;
UPDATE pn_payment_items_all
SET estimated_amount = l_cash_est_amt - l_cash_est_amt_appr, --Bug 9150650
actual_amount = l_cash_act_amt - l_cash_act_amt_appr,
export_currency_amount = l_cash_act_amt - l_cash_exp_amt_appr,
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 payment_item_id = (SELECT ppi1.payment_item_id
FROM pn_payment_items_all ppi1,
pn_payment_schedules_all pps
WHERE ppi1.payment_term_id = p_term_id
AND ppi1.payment_item_type_lookup_code = 'CASH'
AND pps.payment_status_lookup_code in ('DRAFT','ON_HOLD') --Fix for bug#12427309
AND pps.payment_schedule_id = ppi1.payment_schedule_id
AND pps.schedule_date = p_schedule_dt);
pnp_debug_pkg.log('update_cash_item -End- (-)');
END update_cash_item;
SELECT payment_term_id,
start_date,
end_date,
actual_amount,
estimated_amount,
frequency_code
FROM pn_payment_terms_all
WHERE lease_id = g_lease_id
--AND end_date = p_new_lease_term_date Bug 9150650
AND frequency_code <> 'OT';
SELECT MAX(schedule_date)
FROM pn_payment_schedules_all pps,
pn_payment_items_all ppi
WHERE ppi.payment_term_id = p_payment_term_id
AND ppi.payment_item_type_lookup_code = 'CASH'
AND ppi.actual_amount <> 0
AND pps.payment_schedule_id = ppi.payment_schedule_id
/* 12796135 AND pps.payment_status_lookup_code = 'DRAFT'; */
SELECT cal_start
INTO l_cal_yr_st
FROM pn_leases_all
WHERE lease_id = g_lease_id;
update_cash_item(p_term_id => term.payment_term_id,
p_term_str_dt => term.start_date,
p_term_end_dt => l_term_end_date,--p_new_lease_term_date,--Bug 9150650
p_schedule_dt => l_schedule_date,
p_sch_str_dt => l_sch_str_dt,
p_sch_end_dt => l_sch_end_dt,
p_act_amt => term.actual_amount,
p_est_amt => term.estimated_amount,
p_freq => l_frequency);
UPDATE pn_payment_terms_all
SET norm_start_date = NVL(l_norm_str_dt, p_new_lea_comm_dt),
norm_end_date = g_new_lea_term_dt
WHERE payment_term_id = add_main_cur.payment_term_id;
SELECT 'Y'
INTO l_exists
FROM DUAL
WHERE EXISTS (SELECT NULL
FROM pn_space_assign_cust_all psa
WHERE psa.tenancy_id = p_tenancy_id
AND (EXISTS (SELECT NULL
FROM pn_rec_arcl_dtl_all mst,
pn_rec_arcl_dtlln_all dtl
WHERE mst.area_class_dtl_id = dtl.area_class_dtl_id
AND mst.status = 'LOCKED'
AND dtl.cust_space_assign_id = psa.cust_space_assign_id) OR
EXISTS (SELECT NULL
FROM pn_rec_expcl_dtl_all mst,
pn_rec_expcl_dtlln_all dtl
WHERE mst.expense_class_dtl_id = dtl.expense_class_dtl_id
AND mst.status = 'LOCKED'
AND dtl.cust_space_assign_id = psa.cust_space_assign_id))
);
CURSOR get_delete_tenancies_csr IS
SELECT tenancy_id,
location_id,
occupancy_date,
estimated_occupancy_date,
expiration_date
FROM pn_tenancies_all pnt
WHERE pnt.lease_id = p_lease_id
AND NVL(pnt.occupancy_date, pnt.estimated_occupancy_date) > p_new_lea_term_dt;
CURSOR get_update_tenancies_csr IS
SELECT *
FROM pn_tenancies_all pnt
WHERE pnt.lease_id = p_lease_id
AND pnt.expiration_date > p_new_lea_term_dt;
SELECT location_code,
location_type_lookup_code
FROM pn_locations_all pnl
WHERE pnl.location_id = p_location_id
AND p_start_date BETWEEN pnl.active_start_date AND pnl.active_end_date;
FOR get_delete_tenancies IN get_delete_tenancies_csr
LOOP
l_action := NULL;
l_location_id := get_delete_tenancies.location_id;
l_tenancy_Id := get_delete_tenancies.tenancy_Id;
l_tenancy_str_date := NVL(get_delete_tenancies.occupancy_date,
get_delete_tenancies.estimated_occupancy_date);
l_tenancy_end_date := get_delete_tenancies.expiration_date;
pn_tenancies_pkg.delete_row(
x_tenancy_id => get_delete_tenancies.tenancy_id
);
pn_tenancies_pkg.delete_auto_space_assign(
p_tenancy_id => get_delete_tenancies.tenancy_id
,p_action => l_action
,p_location_id => get_delete_tenancies.location_id
,p_loc_type_code => l_loc_type_code
);
pnp_debug_pkg.log('CON_TEN=>DEL : Tenancy and Space Assignments deleted. l_action: '||l_action);
FOR get_update_tenancies IN get_update_tenancies_csr
LOOP
l_action := NULL;
l_location_id := get_update_tenancies.location_id;
l_tenancy_Id := get_update_tenancies.tenancy_Id;
l_tenancy_str_date := NVL(get_update_tenancies.occupancy_date,
get_update_tenancies.estimated_occupancy_date);
l_tenancy_end_date := get_update_tenancies.expiration_date;
pn_tenancies_pkg.update_auto_space_assign
(
p_location_id => get_update_tenancies.location_id
,p_lease_id => p_lease_id
,p_customer_id => get_update_tenancies.customer_id
,p_cust_site_use_id => get_update_tenancies.customer_site_use_id
,p_cust_assign_start_dt => l_tenancy_str_date
,p_cust_assign_end_dt => p_new_lea_term_dt
,p_recovery_space_std_code => get_update_tenancies.recovery_space_std_code
,p_recovery_type_code => get_update_tenancies.recovery_type_code
,p_fin_oblig_end_date => p_new_lea_term_dt
,p_allocated_pct => get_update_tenancies.allocated_area_pct
,p_tenancy_id => get_update_tenancies.tenancy_id
,p_org_id => get_update_tenancies.org_id
,p_location_id_old => get_update_tenancies.location_id
,p_customer_id_old => get_update_tenancies.customer_id
,p_cust_site_use_id_old => get_update_tenancies.customer_site_use_id
,p_cust_assign_start_dt_old => l_tenancy_str_date
,p_cust_assign_end_dt_old => get_update_tenancies.expiration_date
,p_recovery_space_std_code_old => get_update_tenancies.recovery_space_std_code
,p_recovery_type_code_old => get_update_tenancies.recovery_type_code
,p_fin_oblig_end_date_old => get_update_tenancies.fin_oblig_end_date
,p_allocated_pct_old => get_update_tenancies.allocated_area_pct
,p_action => l_action
,p_msg => l_message
);
pnp_debug_pkg.log('CON_TEN=>UPD : Space Assignments deleted. l_action: '||l_action);
UPDATE pn_tenancies_all
SET expiration_date = p_new_lea_term_dt,
fin_oblig_end_date = p_new_lea_term_dt,
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 tenancy_id = l_tenancy_Id;
SELECT plh.lease_termination_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;
/* If lease is contracted from main lease form then delete payment
items for the lease, for which payment schedules are in draft
status and schedule date is greater than lease termination date. */
OPEN get_old_lea_term_dt;
DELETE pn_payment_items_all
WHERE payment_schedule_id IN (SELECT payment_schedule_id
FROM pn_payment_schedules_all
WHERE lease_id = p_lease_id
AND schedule_date > p_new_lea_term_dt
AND payment_status_lookup_code = 'DRAFT')
AND payment_term_id NOT IN (SELECT payment_term_id
FROM pn_payment_terms_all
WHERE lease_id = p_lease_id
AND var_rent_inv_id IS NOT NULL);
/* Delete payment schedules for the lease which are in draft status
and schedule date is greater than lease termination date. */
pnp_debug_pkg.log('CON - MAIN - Deleting Payment Schedules');
DELETE pn_payment_schedules_all psch
WHERE lease_id = p_lease_id
AND schedule_date > p_new_lea_term_dt
AND payment_status_lookup_code = 'DRAFT'
AND NOT EXISTS (SELECT null
FROM pn_payment_items_all pitm
WHERE pitm.payment_schedule_id = psch.payment_schedule_id);
DELETE pn_payment_items_all
WHERE payment_term_id IN (SELECT payment_term_id
FROM pn_payment_terms_all
WHERE start_date > p_new_lea_term_dt
AND lease_id = p_lease_id)
AND payment_term_id NOT IN (SELECT payment_term_id
FROM pn_payment_terms_all
WHERE lease_id = p_lease_id
AND var_rent_inv_id IS NOT NULL);
/* Delete payment terms for the lease which have term start date
greater than lease termination date. */
pnp_debug_pkg.log('CON - MAIN - Deleting Payment Terms');
DELETE pn_payment_terms_all
WHERE lease_id = p_lease_id
AND start_date > p_new_lea_term_dt
AND var_rent_inv_id IS NULL;
/* Update end date of remaining payment terms for the lease to
lease termination date. */
pnp_debug_pkg.log('CON - MAIN - Updating end date of Payment Terms');
UPDATE pn_payment_terms_all
SET end_date = p_new_lea_term_dt,
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 lease_id = p_lease_id
AND end_date > p_new_lea_term_dt
AND frequency_code <> 'OT';
/* Update the cash items with new amount, for the terms which have
been contracted. */
pnp_debug_pkg.log('CON - MAIN - Updating last non zero cash items with new pro. amt');
UPDATE pn_payment_terms_all
SET norm_end_date = g_new_lea_term_dt
WHERE payment_term_id = con_cur.payment_term_id;
SELECT plh.lease_termination_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 distinct schedule_date
FROM pn_payment_items_all ppi,
pn_payment_schedules_all pps
WHERE ppi.payment_term_id = p_payment_term_id
AND ppi.payment_item_type_lookup_code = 'NORMALIZED'
AND pps.payment_schedule_id = ppi.payment_schedule_id
AND pps.payment_status_lookup_code <> 'DRAFT'
and schedule_date >= pn_schedules_items.FIRST_DAY(l_norm_str_dt)
and schedule_date <= (SELECT lease_termination_date
FROM pn_lease_details_all
WHERE lease_id = p_lease_id);
/* If lease is contracted from main lease form then delete payment
items for the lease, for which payment schedules are in draft
status and schedule date is greater than lease termination date. */
OPEN get_old_lea_term_dt;
DELETE pn_payment_items_all
WHERE payment_schedule_id IN (SELECT payment_schedule_id
FROM pn_payment_schedules_all
WHERE lease_id = p_lease_id
AND schedule_date > l_item_end_dt_tbl(i).item_end_dt
AND payment_status_lookup_code <> 'APPROVED') /* 9547514 */
AND payment_term_id = l_item_end_dt_tbl(i).term_id;
/* Delete payment schedules for the lease which are in draft status
and schedule date is greater than lease termination date. */
pnp_debug_pkg.log('CONTRACTION_BY_ITM_END_DT - MAIN - Deleting Payment Schedules');
DELETE pn_payment_schedules_all psch
WHERE lease_id = p_lease_id
AND schedule_date > p_new_lea_term_dt
AND payment_status_lookup_code <> 'APPROVED' /* 9547514 */
AND NOT EXISTS (SELECT 1
FROM pn_payment_items_all pitm
WHERE pitm.payment_schedule_id = psch.payment_schedule_id);
/* Delete payment terms for the lease which have term start date
greater than lease termination date. */
pnp_debug_pkg.log('CONTRACTION_BY_ITM_END_DT - MAIN - Deleting Payment Terms');
DELETE pn_payment_terms_all term
WHERE lease_id = p_lease_id
AND start_date > p_new_lea_term_dt
AND index_period_id IS NULL
AND NOT EXISTS(SELECT 1
FROM pn_payment_items_all item,
pn_payment_schedules_all schd
WHERE item.payment_term_id = term.payment_term_id
AND item.payment_schedule_id = schd.payment_schedule_id
AND schd.payment_status_lookup_code = 'APPROVED');
/* Update end date of remaining payment terms for the lease to
lease termination date. */
pnp_debug_pkg.log('CONTRACTION_BY_ITM_END_DT - MAIN - Updating end date of Payment Terms');
UPDATE pn_payment_terms_all
SET end_date = l_item_end_dt_tbl(i).item_end_dt,
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 payment_term_id = l_item_end_dt_tbl(i).term_id
AND end_date > l_item_end_dt_tbl(i).item_end_dt
AND frequency_code <> 'OT';
/* Update the cash items with new amount, for the terms which have
been contracted. */
pnp_debug_pkg.log('CONTRACTION_BY_ITM_END_DT - MAIN - Updating last non zero cash items with new pro. amt');
UPDATE pn_payment_terms_all
SET norm_end_date = p_new_lea_term_dt
WHERE payment_term_id = con_cur.payment_term_id;
SELECT plh.lease_termination_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 NVL(plh.lease_extension_end_date,
plh.lease_termination_date) old_term_dt
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 GREATEST( NVL(plh.lease_extension_end_date,
plh.lease_termination_date),
plh.lease_termination_date) old_lease_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 *
FROM pn_payment_terms_all
WHERE lease_id = p_lease_id
AND end_date = p_old_lease_end_date
AND index_period_id IS NULL
AND var_rent_inv_id IS NULL
AND period_billrec_id IS NULL
AND frequency_code <>'OT';
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 First_Day(MIN(pps.schedule_date))
FROM pn_payment_schedules_all pps
WHERE pps.lease_id = p_lease_id
AND First_Day(pps.schedule_date) >= First_Day(p_start_date)
AND TO_CHAR(pps.schedule_date,'DD') = l_schd_day
AND pps.payment_status_lookup_code = 'DRAFT';
UPDATE pn_payment_terms_all
SET norm_end_date = g_new_lea_term_dt
WHERE payment_term_id = con_cur.payment_term_id;
pn_schedules_items.Insert_Payment_Term
( p_payment_term_rec => l_payment_term_rec,
x_return_status => x_return_status,
x_return_message => x_return_message );
pn_schedules_items.Insert_Payment_Term
( p_payment_term_rec => l_payment_term_rec,
x_return_status => x_return_status,
x_return_message => x_return_message );
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_payment_term_rec.payment_term_id;
UPDATE pn_payment_terms_all
SET end_date = g_new_lea_term_dt
WHERE payment_term_id = l_payment_term_rec.payment_term_id;
FOR zero_item IN (SELECT cash.payment_item_id c,
norm.payment_item_id n
FROM
(SELECT *
FROM pn_payment_items_all
WHERE payment_term_id = l_payment_term_rec.payment_term_id
AND export_to_ap_flag IS NULL
AND export_to_ar_flag IS NULL
AND payment_item_type_lookup_code = 'CASH'
AND actual_amount = 0
) cash,
(SELECT *
FROM pn_payment_items_all
WHERE payment_term_id = l_payment_term_rec.payment_term_id
AND export_to_ap_flag IS NULL
AND export_to_ar_flag IS NULL
AND payment_item_type_lookup_code = 'NORMALIZED'
AND actual_amount = 0
) norm
WHERE cash.due_date = norm.due_date
)
LOOP
DELETE
FROM pn_payment_items_all
WHERE payment_item_id IN (zero_item.c,zero_item.n);
UPDATE pn_payment_terms_all
SET norm_end_date = p_new_lea_term_dt
WHERE payment_term_id = l_payment_term_rec.payment_term_id and normalize='Y';
SELECT lease_status
FROM pn_leases_all
WHERE lease_id = p_lease_id;
SELECT ppt.payment_term_id,
ppt.lease_change_id,
ppt.schedule_day,
ppt.start_date,
ppt.end_date,
ppt.target_date,
ppt.frequency_code,
ppt.normalize,
ppt.actual_amount,
ppt.estimated_amount,
ppt.payment_term_type_code,
ppt.vendor_id,
ppt.vendor_site_id,
ppt.customer_id,
ppt.customer_site_use_id,
ppt.cust_ship_site_id,
ppt.set_of_books_id,
ppt.currency_code,
ppt.rate,
ppt.norm_start_date
FROM pn_payment_terms_all ppt
WHERE lease_id = p_lease_id
AND ppt.index_period_id IS NULL
AND ppt.var_rent_inv_id IS NULL
AND ppt.period_billrec_id IS NULL
AND NOT EXISTS (SELECT NULL
FROM pn_payment_items_all ppi
WHERE ppt.lease_id = p_lease_id
AND ppi.payment_term_id = ppt.payment_term_id)
AND frequency_code = 'OT'
AND NVL(normalize,'N') = 'N';
UPDATE pn_payment_terms_all
SET norm_start_date = NVL(l_norm_str_dt, p_new_lea_comm_dt),
norm_end_date = g_new_lea_term_dt
WHERE payment_term_id = abs_cur.payment_term_id;
UPDATE pn_payment_terms_all
SET norm_start_date = l_norm_str_dt,
norm_end_date = g_new_lea_term_dt
WHERE payment_term_id = add_ind_var_cur.payment_term_id;
SELECT MAX(pps.schedule_date)
FROM pn_payment_schedules_all pps,
pn_payment_items_all ppi
WHERE pps.payment_schedule_id = ppi.payment_schedule_id
AND ppi.payment_term_id = p_term_id
AND ppi.actual_amount IS NOT NULL
AND ppi.payment_item_type_lookup_code = 'CASH'
AND First_Day(pps.schedule_date) <= First_Day(p_term_end_dt);
SELECT cal_start
INTO l_cal_yr_st
FROM pn_leases_all
WHERE lease_id = p_lease_id;
update_cash_item(p_term_id => pay_term_con_cur.payment_term_id,
p_term_str_dt => pay_term_con_cur.start_date,
p_term_end_dt => pay_term_con_cur.end_date,
p_schedule_dt => l_lst_cash_sch_dt,
p_sch_str_dt => l_sch_str_dt,
p_sch_end_dt => l_sch_end_dt,
p_act_amt => pay_term_con_cur.actual_amount,
p_est_amt => pay_term_con_cur.estimated_amount,
p_freq => l_frequency);
UPDATE pn_payment_items_all
SET estimated_amount = 0,
actual_amount = 0,
export_currency_amount = 0,
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 payment_item_id IN (SELECT ppi1.payment_item_id
FROM pn_payment_items_all ppi1,
pn_payment_schedules_all pps
WHERE ppi1.payment_term_id = pay_term_con_cur.payment_term_id
AND ppi1.payment_item_type_lookup_code = 'CASH'
AND pps.payment_status_lookup_code IN ('DRAFT','ON_HOLD')
AND pps.payment_schedule_id = ppi1.payment_schedule_id
AND pps.schedule_date > l_lst_cash_sch_dt);
pnp_debug_pkg.log('CONTRACT_PAY_TERM - Updated Cash Items Amt to 0. - Normalize');
DELETE pn_payment_items_all ppi
WHERE ppi.payment_term_id = pay_term_con_cur.payment_term_id
AND ppi.payment_schedule_id IN (SELECT pps1.payment_schedule_id
FROM pn_payment_schedules_all pps1,
pn_payment_items_all ppi1
WHERE ppi1.payment_term_id = pay_term_con_cur.payment_term_id
AND pps1.payment_schedule_id = ppi1.payment_schedule_id
AND pps1.payment_status_lookup_code IN ('DRAFT','ON_HOLD')
AND pps1.schedule_date > l_lst_cash_sch_dt);
DELETE pn_payment_schedules_all pps
WHERE pps.lease_id = p_lease_id
AND pps.payment_schedule_id IN (SELECT pps1.payment_schedule_id
FROM pn_payment_schedules_all pps1
WHERE pps1.lease_id = p_lease_id
AND TO_NUMBER(TO_CHAR(pps1.schedule_date,'DD'))
= pay_term_con_cur.schedule_day
AND pps1.schedule_date > l_lst_cash_sch_dt
AND NOT EXISTS (SELECT NULL
FROM pn_payment_items_all ppi
WHERE ppi.payment_schedule_id
= pps1.payment_schedule_id
)
);
pnp_debug_pkg.log('CONTRACT_PAY_TERM - Deleted Cash Items.');
UPDATE pn_payment_terms_all
SET changed_flag = 'N'
WHERE payment_term_id = pay_term_con_cur.payment_term_id;
SELECT lease_change_id, lease_status
FROM pn_lease_details_history
WHERE lease_id = p_lease_id
UNION
SELECT b.lease_change_id, a.lease_status
FROM pn_leases_all a, pn_lease_details_all b
WHERE a.lease_id = b.lease_id
AND a.lease_id = p_lease_id
ORDER BY 1 DESC;
SELECT payment_schedule_id
FROM pn_payment_schedules_all
WHERE lease_id = p_lease_id
AND schedule_date = p_schedule_date
AND payment_status_lookup_code = 'APPROVED';
PROCEDURE Insert_Payment_Term (p_payment_term_rec IN OUT NOCOPY pn_payment_terms_all%ROWTYPE,
x_return_status OUT NOCOPY VARCHAR2,
x_return_message OUT NOCOPY VARCHAR2)
IS
l_payment_term_id NUMBER := NULL;
SELECT *
FROM pn_distributions_all
WHERE payment_term_id = p_payment_term_id;
pnp_debug_pkg.log('INSERT_PAYMENT_TERM (+) ');
pnt_payment_terms_pkg.Insert_Row
(
x_rowid => l_rowid,
x_payment_term_id => l_payment_term_id,
x_payment_purpose_code => p_payment_term_rec.payment_purpose_code,
x_payment_term_type_code => p_payment_term_rec.payment_term_type_code,
x_frequency_code => p_payment_term_rec.frequency_code,
x_lease_id => p_payment_term_rec.lease_id,
x_lease_change_id => p_payment_term_rec.lease_change_id,
x_start_date => p_payment_term_rec.start_date,
x_end_date => p_payment_term_rec.end_date,
x_vendor_id => p_payment_term_rec.vendor_id,
x_vendor_site_id => p_payment_term_rec.vendor_site_id,
x_customer_id => p_payment_term_rec.customer_id,
x_customer_site_use_id => p_payment_term_rec.customer_site_use_id,
x_target_date => p_payment_term_rec.target_date,
x_actual_amount => p_payment_term_rec.actual_amount,
x_estimated_amount => p_payment_term_rec.estimated_amount,
x_set_of_books_id => p_payment_term_rec.set_of_books_id,
x_currency_code => p_payment_term_rec.currency_code,
x_rate => p_payment_term_rec.rate,
x_normalize => p_payment_term_rec.normalize,
x_location_id => p_payment_term_rec.location_id,
x_schedule_day => p_payment_term_rec.schedule_day,
x_cust_ship_site_id => p_payment_term_rec.cust_ship_site_id,
x_ap_ar_term_id => p_payment_term_rec.ap_ar_term_id,
x_cust_trx_type_id => p_payment_term_rec.cust_trx_type_id,
x_project_id => p_payment_term_rec.project_id,
x_task_id => p_payment_term_rec.task_id,
x_organization_id => p_payment_term_rec.organization_id,
x_expenditure_type => p_payment_term_rec.expenditure_type,
x_expenditure_item_date => p_payment_term_rec.expenditure_item_date,
x_tax_group_id => p_payment_term_rec.tax_group_id,
x_tax_code_id => p_payment_term_rec.tax_code_id,
x_tax_included => p_payment_term_rec.tax_included,
x_distribution_set_id => p_payment_term_rec.distribution_set_id,
x_inv_rule_id => p_payment_term_rec.inv_rule_id,
x_account_rule_id => p_payment_term_rec.account_rule_id,
x_salesrep_id => p_payment_term_rec.salesrep_id,
x_approved_by => p_payment_term_rec.approved_by,
x_status => p_payment_term_rec.status,
x_index_period_id => p_payment_term_rec.index_period_id,
x_index_term_indicator => p_payment_term_rec.index_term_indicator,
x_po_header_id => p_payment_term_rec.po_header_id,
x_cust_po_number => p_payment_term_rec.cust_po_number,
x_receipt_method_id => p_payment_term_rec.receipt_method_id,
x_var_rent_inv_id => p_payment_term_rec.var_rent_inv_id,
x_var_rent_type => p_payment_term_rec.var_rent_type,
x_changed_flag => p_payment_term_rec.changed_flag,
x_term_template_id => p_payment_term_rec.term_template_id,
x_attribute_category => p_payment_term_rec.attribute_category,
x_attribute1 => p_payment_term_rec.attribute1,
x_attribute2 => p_payment_term_rec.attribute2,
x_attribute3 => p_payment_term_rec.attribute3,
x_attribute4 => p_payment_term_rec.attribute4,
x_attribute5 => p_payment_term_rec.attribute5,
x_attribute6 => p_payment_term_rec.attribute6,
x_attribute7 => p_payment_term_rec.attribute7,
x_attribute8 => p_payment_term_rec.attribute8,
x_attribute9 => p_payment_term_rec.attribute9,
x_attribute10 => p_payment_term_rec.attribute10,
x_attribute11 => p_payment_term_rec.attribute11,
x_attribute12 => p_payment_term_rec.attribute12,
x_attribute13 => p_payment_term_rec.attribute13,
x_attribute14 => p_payment_term_rec.attribute14,
x_attribute15 => p_payment_term_rec.attribute15,
x_project_attribute_category => p_payment_term_rec.project_attribute_category,
x_project_attribute1 => p_payment_term_rec.project_attribute1,
x_project_attribute2 => p_payment_term_rec.project_attribute2,
x_project_attribute3 => p_payment_term_rec.project_attribute3,
x_project_attribute4 => p_payment_term_rec.project_attribute4,
x_project_attribute5 => p_payment_term_rec.project_attribute5,
x_project_attribute6 => p_payment_term_rec.project_attribute6,
x_project_attribute7 => p_payment_term_rec.project_attribute7,
x_project_attribute8 => p_payment_term_rec.project_attribute8,
x_project_attribute9 => p_payment_term_rec.project_attribute9,
x_project_attribute10 => p_payment_term_rec.project_attribute10,
x_project_attribute11 => p_payment_term_rec.project_attribute11,
x_project_attribute12 => p_payment_term_rec.project_attribute12,
x_project_attribute13 => p_payment_term_rec.project_attribute13,
x_project_attribute14 => p_payment_term_rec.project_attribute14,
x_project_attribute15 => p_payment_term_rec.project_attribute15,
x_creation_date => SYSDATE,
x_created_by => fnd_global.user_id,
x_last_update_date => SYSDATE,
x_last_updated_by => fnd_global.user_id,
x_last_update_login => fnd_global.login_id,
x_lease_status => p_payment_term_rec.lease_status,
x_org_id => p_payment_term_rec.org_id,
x_rec_agr_line_id => p_payment_term_rec.rec_agr_line_id,
x_period_billrec_id => p_payment_term_rec.period_billrec_id,
x_amount_type => p_payment_term_rec.amount_type,
x_recoverable_flag => p_payment_term_rec.recoverable_flag,
x_parent_term_id => p_payment_term_rec.parent_term_id,
x_index_norm_flag => p_payment_term_rec.index_norm_flag,
x_area => p_payment_term_rec.area,
x_area_type_code => p_payment_term_rec.area_type_code,
x_include_in_var_rent => p_payment_term_rec.INCLUDE_IN_VAR_RENT,
X_TAX_CLASSIFICATION_CODE => p_payment_term_rec.tax_classification_code /*bug#13556228*/
);
pnp_debug_pkg.log('INSERT_PAYMENT_TERM (-) Created Payment Term Id: '||l_payment_term_id);
pn_distributions_pkg.insert_row (
x_rowid => l_dist_rowid
,x_distribution_id => l_distribution_id
,x_account_id => rec_distributions.account_id
,x_payment_term_id => l_payment_term_id
,x_term_template_id => rec_distributions.term_template_id
,x_account_class => rec_distributions.account_class
,x_percentage => rec_distributions.percentage
,x_line_number => rec_distributions.line_number
,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_last_update_login => NVL(fnd_profile.value('LOGIN_ID'),0)
,x_attribute_category => rec_distributions.attribute_category
,x_attribute1 => rec_distributions.attribute1
,x_attribute2 => rec_distributions.attribute2
,x_attribute3 => rec_distributions.attribute3
,x_attribute4 => rec_distributions.attribute4
,x_attribute5 => rec_distributions.attribute5
,x_attribute6 => rec_distributions.attribute6
,x_attribute7 => rec_distributions.attribute7
,x_attribute8 => rec_distributions.attribute8
,x_attribute9 => rec_distributions.attribute9
,x_attribute10 => rec_distributions.attribute10
,x_attribute11 => rec_distributions.attribute11
,x_attribute12 => rec_distributions.attribute12
,x_attribute13 => rec_distributions.attribute13
,x_attribute14 => rec_distributions.attribute14
,x_attribute15 => rec_distributions.attribute15
,x_org_id => rec_distributions.org_id);
pnp_debug_pkg.log('INSERT_PAYMENT_TERM : Created Distribution Id: '||l_distribution_id||
' for Payment term id :'||l_payment_term_id);
pnp_debug_pkg.log('INSERT_PAYMENT_TERM (-)');
END Insert_Payment_Term;
SELECT payment_item_id, actual_amount, estimated_amount
FROM pn_payment_items_all
WHERE payment_schedule_id = p_sch_id
AND payment_term_id = p_payment_term_rec.payment_term_id
AND payment_item_type_lookup_code = 'CASH';
SELECT NVL(SUM(pi.actual_amount),0) amount
FROM pn_payment_items_all pi,
pn_payment_schedules_all ps
WHERE pi.payment_term_id = p_payment_term_rec.payment_term_id
AND pi.payment_schedule_id = ps.payment_schedule_id
AND ps.schedule_date = l_sch_dt
AND ps.payment_status_lookup_code = 'APPROVED'
AND payment_item_type_lookup_code = 'CASH'; -- added for bug #10184924
SELECT cal_start
INTO l_cal_yr_st_dt
FROM PN_LEASES_ALL
WHERE LEASE_ID = (select distinct lease_id from pn_payment_terms_all where payment_term_id = p_payment_term_rec.payment_term_id);
UPDATE pn_payment_items_all
SET actual_amount = ROUND(l_cash_act_amt - l_exist_amount, l_precision),
export_currency_amount = ROUND(l_cash_act_amt - l_exist_amount, l_precision),
estimated_amount = ROUND(l_cash_est_amt,l_precision),
last_update_date = sysdate,
last_updated_by = fnd_global.user_id
WHERE payment_item_id = existing_items_rec.payment_item_id;
DELETE FROM PN_PAYMENT_SCHEDULES_ALL
WHERE payment_schedule_id = l_sch_id
AND NOT EXISTS (select 'Y'
from pn_payment_items_all
where payment_schedule_id = l_sch_id); /* Added Bug 9542483 */
UPDATE pn_payment_items_all
SET actual_amount = ROUND(l_cash_act_amt - l_exist_amount, l_precision),
export_currency_amount = ROUND(l_cash_act_amt - l_exist_amount, l_precision),
estimated_amount = ROUND(l_cash_est_amt, l_precision),
last_update_date = sysdate,
last_updated_by = fnd_global.user_id
WHERE payment_item_id = existing_items_rec.payment_item_id;
DELETE FROM PN_PAYMENT_SCHEDULES_ALL
WHERE payment_schedule_id = l_sch_id
and not exists (select NULL
from pn_payment_items_all
where payment_schedule_id = l_sch_id); /* 9322649 */
pn_schedules_items.Insert_Payment_Term (
p_payment_term_rec => l_payment_term_rec,
x_return_status => x_return_status,
x_return_message => x_return_message);
SELECT *
FROM pn_payment_terms_all
WHERE lease_id = p_lease_id
AND frequency_code <> 'OT'
AND index_period_id IS NULL
AND end_date = p_date;
SELECT MAX(sch.schedule_date)
FROM pn_payment_schedules_all sch,
pn_payment_items_all itm
WHERE itm.payment_term_id = p_term_id
AND itm.payment_schedule_id = sch.payment_schedule_id
AND itm.payment_item_type_lookup_code = 'CASH';
SELECT GREATEST(NVL(plh.lease_extension_end_date, plh.lease_termination_date),
plh.lease_termination_date) lease_term_date_old,
pld.lease_change_id lease_change_id
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;
pnp_debug_pkg.log('ROLLOVER - Update end date of payment term..');
UPDATE pn_payment_terms_all
SET end_date = p_lease_end_date,
lease_status = 'MTM'
WHERE payment_term_id = l_payment_term_rec.payment_term_id;
select payment_status_lookup_code INTO l_payment_status
from pn_payment_schedules_all
where schedule_date = l_last_sch_date
and lease_id = p_lease_id;
pnp_debug_pkg.log('ROLLOVER - Update end date of payment term..');
UPDATE pn_payment_terms_all
SET end_date = p_lease_end_date,
lease_status = 'MTM'
WHERE payment_term_id = l_payment_term_rec.payment_term_id;
SELECT term_history_id, adjustment_type_code
FROM pn_payment_terms_history
WHERE term_history_id = (SELECT max(pth.term_history_id)
FROM pn_payment_terms_history pth
WHERE pth.payment_term_id = p_term_id);
SELECT MIN(ppi.currency_code) currency_code,
MIN(pps.schedule_date) schedule_date,
SUM(DECODE(ppi.payment_item_type_lookup_code, 'CASH',
NVL(ppi.actual_amount,0), 0)) cash_amt,
SUM(DECODE(ppi.payment_item_type_lookup_code, 'CASH',
DECODE(ppt.normalize,'Y',NVL(ppi.actual_amount,0), 0), 0)) cash_norm_amt,
SUM(DECODE(ppi.payment_item_type_lookup_code, 'NORMALIZED',
NVL(ppi.actual_amount,0), 0)) norm_amt
FROM pn_payment_items_all ppi,
pn_payment_schedules_all pps,
pn_payment_terms_all ppt
WHERE ppt.lease_id = g_lease_id
AND ppt.lease_id = pps.lease_id
AND pps.payment_schedule_id = ppi.payment_schedule_id
AND ppt.payment_term_id = ppi.payment_term_id
GROUP BY ppi.currency_code, pps.schedule_date
ORDER BY ppi.currency_code, pps.schedule_date;
SELECT agr.rec_agreement_name
,agr.rec_agreement_num
FROM pn_rec_agreements_all agr
,pn_rec_agr_lines_all line
,pn_payment_terms_all term
WHERE agr.lease_id = g_lease_id
AND agr.rec_agreement_id = line.rec_agreement_id
AND term.payment_term_id = p_term_id
AND line.purpose = term.payment_purpose_code
AND line.type = term.payment_term_type_code
AND line.start_date <= term.end_date
AND line.end_date >= term.start_date;
SELECT pvr.rent_num
FROM pn_var_rents_all pvr
,pn_var_rent_inv_all pvri
,pn_var_abatements_all pva
,pn_payment_terms_all ppt
WHERE ppt.payment_term_id = p_term_id
AND pva.payment_term_id = ppt.payment_term_id
AND pvri.var_rent_inv_id = pva.var_rent_inv_id
AND pvri.invoice_date BETWEEN ppt.start_date
AND ppt.end_date
AND pvr.var_rent_id = pvri.var_rent_id;
PROCEDURE update_term_dates(p_new_lea_term_start_dt DATE DEFAULT NULL,
p_new_lea_term_end_dt DATE,
p_lease_id NUMBER,
p_payment_term_id NUMBER,
p_amount NUMBER DEFAULT NULL)
IS
l_precision NUMBER;
SELECT currency_code
FROM pn_payment_terms_all
WHERE payment_term_id = p_payment_term_id;
pnp_debug_pkg.log('UPDATE TERMS +Start+ (+)');
/* Updates the term with new term start/end date */
UPDATE pn_payment_terms_all
SET start_date = NVL(p_new_lea_term_start_dt, start_date),
end_date = p_new_lea_term_end_dt,
actual_amount = NVL(l_act_amount, actual_amount),
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 payment_term_id = p_payment_term_id;
pnp_debug_pkg.log('UPDATE TERMS +Start+ (-)');
END update_term_dates;
PROCEDURE update_cash_item( p_item_id NUMBER
,p_term_id NUMBER
,p_sched_id NUMBER
,p_act_amt NUMBER)
IS
l_precision NUMBER;
SELECT currency_code
FROM pn_payment_terms_all
WHERE payment_term_id = p_term_id;
pnp_debug_pkg.log('update_cash_item +Start+ (-)');
UPDATE pn_payment_items_all
SET actual_amount = ROUND(actual_amount + NVL(p_act_amt,0), l_precision),
export_currency_amount = ROUND(actual_amount + NVL(p_act_amt,0), l_precision),
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 payment_item_id = p_item_id;
UPDATE pn_payment_items_all
SET actual_amount = ROUND(actual_amount + NVL(p_act_amt,0), l_precision),
export_currency_amount = ROUND(actual_amount + NVL(p_act_amt,0), l_precision),
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 payment_item_id = (SELECT ppi.payment_item_id
FROM pn_payment_items_all ppi,
pn_payment_schedules_all pps
WHERE ppi.payment_term_id = p_term_id
AND ppi.payment_item_type_lookup_code = 'CASH'
AND pps.payment_schedule_id = ppi.payment_schedule_id
AND pps.payment_status_lookup_code = 'DRAFT'
AND pps.payment_schedule_id = p_sched_id);
pnp_debug_pkg.log('update_cash_item +End+ (-)');
END update_cash_item;
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';
/* update the cash amount for the schedule */
l_amt_due_to_term := 0;
PROCEDURE delete_term(p_payment_term_id NUMBER)
AS
BEGIN
pnp_debug_pkg.log('Delete term start (+)');
DELETE pn_payment_items_all
WHERE payment_term_id = p_payment_term_id;
DELETE pn_payment_terms_all
WHERE payment_term_id = p_payment_term_id;
pnp_debug_pkg.log('Delete term End (-)');
END delete_term;
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 pps.payment_schedule_id
FROM pn_payment_schedules_all pps
WHERE pps.schedule_date = p_sched_date
AND pps.lease_id = p_lease_id
AND pps.payment_status_lookup_code = 'DRAFT';
SELECT distinct schedule_date
FROM pn_payment_items_all ppi,
pn_payment_schedules_all pps
WHERE ppi.payment_term_id = p_payment_term_id
AND ppi.payment_item_type_lookup_code = 'NORMALIZED'
AND pps.payment_schedule_id = ppi.payment_schedule_id
AND pps.payment_status_lookup_code <> 'DRAFT'
and schedule_date >= pn_schedules_items.FIRST_DAY(l_amd_comn_date)
and schedule_date <= (SELECT lease_termination_date from
pn_lease_details_all where lease_id = p_lease_id ); /* 7149537 */
SELECT change_commencement_date
INTO l_amd_comn_date
FROM
(SELECT *
FROM pn_lease_changes_all
WHERE lease_id = p_lease_id
ORDER BY lease_change_id DESC)
WHERE rownum < 2;
SELECT lease_commencement_date
INTO l_lease_commence_dt -- Bug 14284343
FROM pn_lease_details_all
WHERE lease_id = p_lease_id;
select schedule_day
INTO l_sch_dy
from pn_payment_terms_all
where payment_term_id = p_payment_term_id; /* 9231686 */
SELECT NVL(First_Day(MIN(pps.schedule_date)),l_amd_comn_date)
into l_nrm_st_dt
FROM pn_payment_schedules_all pps
WHERE pps.lease_id = p_lease_id
AND pps.payment_status_lookup_code = 'DRAFT'
AND TO_CHAR(pps.schedule_date,'DD') = l_sch_dy ;
/* delete items beyond the end date */
DELETE pn_payment_items_all
WHERE payment_schedule_id IN
(SELECT payment_schedule_id
FROM pn_payment_schedules_all
WHERE lease_id = p_lease_id
AND schedule_date > pay_term_con_cur.end_date
AND payment_status_lookup_code IN ('DRAFT', 'ON_HOLD'))
AND payment_term_id = pay_term_con_cur.payment_term_id;
l_sched_tbl.DELETE;
update_cash_item( p_item_id => l_payment_item_id
,p_term_id => p_payment_term_id
,p_sched_id => l_payment_schedule_id
,p_act_amt => l_adj_amount);
DELETE pn_payment_items_all
WHERE payment_schedule_id IN
(SELECT payment_schedule_id
FROM pn_payment_schedules_all
WHERE lease_id = p_lease_id
AND schedule_date > l_lst_cash_sch_dt
AND payment_status_lookup_code IN ('DRAFT', 'ON_HOLD'))
AND payment_term_id = pay_term_con_cur.payment_term_id;
SELECT count(*) into l_count
FROM pn_payment_schedules_all pps,
pn_payment_items_all ppi
WHERE pps.lease_id = p_lease_id
AND pps.schedule_date BETWEEN PN_SCHEDULES_ITEMS.FIRST_DAY(NVL(l_amd_comn_date,l_norm_str_dt)) /* 7149537 */
AND LAST_DAY(g_new_lea_term_dt)
AND pps.payment_status_lookup_code in ( 'DRAFT', 'ON_HOLD' )
AND TO_CHAR(pps.schedule_date,'DD') = pay_term_con_cur.schedule_day
AND ppi.PAYMENT_SCHEDULE_ID(+) = pps.PAYMENT_SCHEDULE_ID
AND ppi.PAYMENT_TERM_ID(+) = pay_term_con_cur.payment_term_id
AND ppi.PAYMENT_ITEM_TYPE_LOOKUP_CODE(+) = 'CASH'
AND ppi.LAST_ADJUSTMENT_TYPE_CODE IS NULL
ORDER BY pps.schedule_date;
UPDATE pn_payment_terms_all
SET changed_flag = 'N'
WHERE payment_term_id = pay_term_con_cur.payment_term_id;
FOR zero_item IN (SELECT cash.payment_item_id c,
norm.payment_item_id n
FROM
(SELECT *
FROM pn_payment_items_all
WHERE payment_term_id = pay_term_con_cur.payment_term_id
AND export_to_ap_flag IS NULL
AND export_to_ar_flag IS NULL
AND payment_item_type_lookup_code = 'CASH'
AND actual_amount = 0
) cash,
(SELECT *
FROM pn_payment_items_all
WHERE payment_term_id = pay_term_con_cur.payment_term_id
AND export_to_ap_flag IS NULL
AND export_to_ar_flag IS NULL
AND payment_item_type_lookup_code = 'NORMALIZED'
AND actual_amount = 0
) norm
WHERE cash.due_date = norm.due_date
)
LOOP
DELETE FROM pn_payment_items_all
WHERE payment_item_id IN (zero_item.c,zero_item.n);
SELECT NVL(plh.lease_termination_date,plh.lease_extension_end_date) lease_termination_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 payment_schedule_id
FROM pn_payment_schedules_all
WHERE lease_id = p_lease_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 lease_status into l_str from pn_leases_all where lease_id = p_lease_id;
SELECT plh.lease_status into l_sts
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 NVL(plh.lease_extension_end_date,plh.lease_termination_date) into l_old_lea_term_dt
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;
/* the start dates and end dates for a term will be updated if and only if
there exists atleast one approved schedule for the term */
IF (l_schedule_exists = 'Y') THEN
/* The term start date lies outside the new lease */
/* Update term with new term dates i.e. new lease end date and amount equal
to 0 ,if the term lies outside the new lease and is a non-normalized term */
update_term_dates(p_new_lea_term_start_dt => p_new_lea_term_dt,
p_new_lea_term_end_dt => p_new_lea_term_dt,
p_lease_id => p_lease_id,
p_payment_term_id => rec.payment_term_id,
p_amount => 0 );
delete_term(p_payment_term_id => rec.payment_term_id);
/* Update term with new term end date if the term lies partially outside the new
lease is a normalized term */
update_term_dates(p_new_lea_term_end_dt => p_new_lea_term_dt,
p_lease_id => p_lease_id,
p_payment_term_id => rec.payment_term_id);
/* call clean up schedules to delete schedules which do not have any item or
are draft and are outside the lease */
pn_retro_adjustment_pkg.cleanup_schedules(p_lease_id);
l_update_nbp_flag VARCHAR2(1);
SELECT pld.lease_commencement_date,
pld.lease_termination_date,
TRUNC(pld.lease_extension_end_date),
pl.payment_term_proration_rule,
pl.lease_status,
pl.lease_class_code,
pl.lease_num,
pl.name
FROM pn_leases_all pl,
pn_lease_details_all pld
WHERE pl.lease_id = p_lease_id
AND pld.lease_id = pl.lease_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);
SELECT det_history.lease_status lease_status_old,
lease.lease_status lease_status_new,
details.lease_termination_date lease_term_date,
det_history.lease_extension_end_date lease_ext_end_date
FROM pn_lease_details_all details,
pn_lease_details_history det_history,
pn_leases_all lease
WHERE details.lease_id = p_lease_id
AND det_history.lease_id = p_lease_id
AND lease.lease_id = p_lease_id
AND details.lease_change_id = det_history.new_lease_change_id;
SELECT 'Y'
FROM DUAL
WHERE NOT EXISTS ( SELECT NULL
FROM pn_index_leases_all ilease, pn_index_lease_periods_all period
WHERE ilease.lease_id = p_lease_id
AND period.index_lease_id = ilease.index_lease_id);
select decode(instr(p_lease_context,':',l_start_pos),0,length(p_lease_context),instr(p_lease_context,':',l_start_pos)-1)
into l_end_pos from dual;
/* call clean up schedules to delete schedules which do not have any item or
are draft and are outside the lease */
pn_retro_adjustment_pkg.cleanup_schedules(p_lease_id);
term_id_tab.delete;
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 => p_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 = p_lease_id;