[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select period_id, start_date, end_date, org_id
from pn_var_periods
where var_rent_id = l_var_rent_id;
select distinct period_id
from pn_var_periods
where var_rent_id = p_chg_var_rent_id
and (start_date between p_start and p_end
or end_date between p_start and p_end);
select *
from pn_var_constraints
where period_id = p_old_periodId
ORDER BY constr_cat_code, type_code, amount;
INSERT INTO pn_var_constraints_all (
constraint_id,
constraint_num,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
period_id,
constr_cat_code,
type_code,
amount,
comments,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
org_id,
constr_template_id,
agreement_template_id,
constr_default_id
) values (
pn_var_constraints_s.nextval,
l_constr_num,
sysdate,
NVL(fnd_profile.value('USER_ID'),0),
sysdate,
NVL(fnd_profile.value('USER_ID'),0),
NVL(fnd_profile.value('USER_ID'),0),
c1_rec.period_id,
c3_rec.constr_cat_code,
c3_rec.type_code,
c3_rec.amount,
c3_rec.comments,
c3_rec.attribute_category,
c3_rec.attribute1,
c3_rec.attribute2,
c3_rec.attribute3,
c3_rec.attribute4,
c3_rec.attribute5,
c3_rec.attribute6,
c3_rec.attribute7,
c3_rec.attribute8,
c3_rec.attribute9,
c3_rec.attribute10,
c3_rec.attribute11,
c3_rec.attribute12,
c3_rec.attribute13,
c3_rec.attribute14,
c3_rec.attribute15,
c3_rec.org_id,
c3_rec.constr_template_id,
c3_rec.agreement_template_id,
c3_rec.constr_default_id
);
select period_id, start_date, end_date,
org_id, proration_factor
from pn_var_periods
where var_rent_id = l_var_rent_id;
select distinct period_id
from pn_var_periods
where var_rent_id = p_chg_var_rent_id
and (start_date between p_start and p_end
or end_date between p_start and p_end);
select *
from pn_var_lines
where period_id = p_old_periodId
ORDER BY sales_type_code, item_category_code;
select *
from pn_var_bkpts_head
where period_id = p_old_periodId;
select *
from pn_var_bkpts_det
where bkpt_header_id = p_bkptheadid;
SELECT pn_var_lines_s.nextval into l_lineitemid from dual;
INSERT INTO pn_var_lines_all (
line_item_id,
line_item_num,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
period_id,
sales_type_code,
item_category_code,
comments,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
org_id,
line_template_id,
agreement_template_id,
line_default_id
) values (
l_lineitemid,
l_line_num,
sysdate,
NVL(fnd_profile.value('USER_ID'),0),
sysdate,
NVL(fnd_profile.value('USER_ID'),0),
NVL(fnd_profile.value('USER_ID'),0),
c1_rec.period_id,
c3_rec.sales_type_code,
c3_rec.item_category_code,
c3_rec.comments,
c3_rec.attribute_category,
c3_rec.attribute1,
c3_rec.attribute2,
c3_rec.attribute3,
c3_rec.attribute4,
c3_rec.attribute5,
c3_rec.attribute6,
c3_rec.attribute7,
c3_rec.attribute8,
c3_rec.attribute9,
c3_rec.attribute10,
c3_rec.attribute11,
c3_rec.attribute12,
c3_rec.attribute13,
c3_rec.attribute14,
c3_rec.attribute15,
c3_rec.org_id,
c3_rec.line_template_id,
c3_rec.agreement_template_id,
c3_rec.line_default_id
);
SELECT pn_var_bkpts_head_s.nextval into l_bkptheadid from dual;
INSERT INTO pn_var_bkpts_head_all (
bkpt_header_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
line_item_id,
period_id,
break_type,
base_rent_type,
natural_break_rate,
base_rent,
breakpoint_type,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
org_id
) values (
l_bkptheadid,
sysdate,
NVL(fnd_profile.value('USER_ID'),0),
sysdate,
NVL(fnd_profile.value('USER_ID'),0),
NVL(fnd_profile.value('USER_ID'),0),
l_lineitemid,
c1_rec.period_id,
c4_rec.break_type,
c4_rec.base_rent_type,
c4_rec.natural_break_rate,
c4_rec.base_rent,
c4_rec.breakpoint_type,
c4_rec.attribute_category,
c4_rec.attribute1,
c4_rec.attribute2,
c4_rec.attribute3,
c4_rec.attribute4,
c4_rec.attribute5,
c4_rec.attribute6,
c4_rec.attribute7,
c4_rec.attribute8,
c4_rec.attribute9,
c4_rec.attribute10,
c4_rec.attribute11,
c4_rec.attribute12,
c4_rec.attribute13,
c4_rec.attribute14,
c4_rec.attribute15,
c4_rec.org_id
);
INSERT INTO pn_var_bkpts_det_all (
bkpt_detail_id,
bkpt_detail_num,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
bkpt_header_id,
bkpt_start_date,
bkpt_end_date,
period_bkpt_vol_start,
period_bkpt_vol_end,
group_bkpt_vol_start,
group_bkpt_vol_end,
bkpt_rate,
comments,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
org_id
) values (
pn_var_bkpts_det_s.nextval,
l_bkdt_num,
sysdate,
NVL(fnd_profile.value('USER_ID'),0),
sysdate,
NVL(fnd_profile.value('USER_ID'),0),
NVL(fnd_profile.value('USER_ID'),0),
l_bkptheadid,
l_line_start_date,
l_line_end_date,
c5_rec.period_bkpt_vol_start,
c5_rec.period_bkpt_vol_end,
c5_rec.group_bkpt_vol_start,
c5_rec.group_bkpt_vol_end,
c5_rec.bkpt_rate,
c5_rec.comments,
c5_rec.attribute_category,
c5_rec.attribute1,
c5_rec.attribute2,
c5_rec.attribute3,
c5_rec.attribute4,
c5_rec.attribute5,
c5_rec.attribute6,
c5_rec.attribute7,
c5_rec.attribute8,
c5_rec.attribute9,
c5_rec.attribute10,
c5_rec.attribute11,
c5_rec.attribute12,
c5_rec.attribute13,
c5_rec.attribute14,
c5_rec.attribute15,
c5_rec.org_id
);
select commencement_date
from pn_var_rents_all
where var_rent_id = l_var_rent_id;
select a.grp_date_id,
b.line_item_id,
c.period_id,
c.start_date,
c.end_date,
a.group_date,
a.invoice_date invoicing_date,
a.reptg_due_date reporting_date
from pn_var_grp_dates_all a,
pn_var_lines_all b,
pn_var_periods_all c
where a.var_rent_id = l_var_rent_id
and a.period_id = b.period_id
and b.period_id = c.period_id;
select d.vol_hist_id vol_hist_id,
d.line_item_id line_item_id,
d.period_id period_id,
d.start_date start_date,
d.end_date end_date,
d.grp_date_id grp_date_id,
d.group_date group_date,
d.actual_gl_account_id actual_gl_account_id,
d.actual_amount actual_amount,
d.daily_actual_amount daily_actual_amount,
d.vol_hist_status_code vol_hist_status_code,
d.report_type_code report_type_code,
d.certified_by certified_by,
d.actual_exp_code actual_exp_code,
d.for_gl_account_id for_gl_account_id,
d.forecasted_amount forecasted_amount,
d.forecasted_exp_code forecasted_exp_code,
d.variance_exp_code variance_exp_code,
d.comments comments,
d.attribute_category attribute_category,
d.attribute1 attribute1,
d.attribute2 attribute2,
d.attribute3 attribute3,
d.attribute4 attribute4,
d.attribute5 attribute5,
d.attribute6 attribute6,
d.attribute7 attribute7,
d.attribute8 attribute8,
d.attribute9 attribute9,
d.attribute10 attribute10,
d.attribute11 attribute11,
d.attribute12 attribute12,
d.attribute13 attribute13,
d.attribute14 attribute14,
d.attribute15 attribute15,
d.org_id org_id
from pn_var_grp_dates_all a,
pn_var_lines_all b,
pn_var_periods_all c,
pn_var_vol_hist_all d
where a.var_rent_id = l_chg_var_rent_id
and a.period_id = b.period_id
and b.period_id = c.period_id
and d.period_id = c.period_id
and d.line_item_id = b.line_item_id
and d.grp_date_id = a.grp_date_id
and (d.start_date between p_start_date and p_end_date
or d.end_date between p_start_date and p_end_date)
and a.grp_start_date >= p_comm_date;
INSERT into pn_var_vol_hist_all (
vol_hist_id,
vol_hist_num,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
line_item_id,
period_id,
start_date,
end_date,
grp_date_id,
group_date,
reporting_date,
due_date,
invoicing_date,
actual_gl_account_id,
actual_amount,
daily_actual_amount,
vol_hist_status_code,
report_type_code,
certified_by,
actual_exp_code,
for_gl_account_id,
forecasted_amount,
forecasted_exp_code,
variance_exp_code,
comments,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
org_id
) values (
pn_var_vol_hist_s.nextval,
l_vol_num,
sysdate,
NVL(fnd_profile.value('USER_ID'),0),
sysdate,
NVL(fnd_profile.value('USER_ID'),0),
NVL(fnd_profile.value('USER_ID'),0),
c1_rec.line_item_id,
c1_rec.period_id,
l_start_date,
l_end_date,
c1_rec.grp_date_id,
c1_rec.group_date,
c1_rec.reporting_date,
l_null,
c1_rec.invoicing_date,
l_null,
l_actual_amount,
c2_rec.daily_actual_amount,
c2_rec.vol_hist_status_code,
c2_rec.report_type_code,
l_null,
l_null,
l_null,
l_null,
l_null,
l_null,
c2_rec.comments,
c2_rec.attribute_category,
c2_rec.attribute1,
c2_rec.attribute2,
c2_rec.attribute3,
c2_rec.attribute4,
c2_rec.attribute5,
c2_rec.attribute6,
c2_rec.attribute7,
c2_rec.attribute8,
c2_rec.attribute9,
c2_rec.attribute10,
c2_rec.attribute11,
c2_rec.attribute12,
c2_rec.attribute13,
c2_rec.attribute14,
c2_rec.attribute15,
c2_rec.org_id
);
SELECT a.var_rent_id,
a.period_id,
a.grp_date_id,
a.grp_start_date,
a.grp_end_date,
a.group_date,
(a.grp_end_date - a.grp_start_date)+1 no_of_group_days,
a.invoice_date
,a.proration_factor
,b.start_date
,b.end_date
,c.commencement_date
,c.proration_rule --Chris.T. 10FEB2004
FROM pn_var_grp_dates_all a
,pn_var_periods_all b
,pn_var_rents_all c
WHERE c.var_rent_id = ip_var_rent_id
AND c.var_rent_id = a.var_rent_id
AND a.period_id = b.period_id
AND a.period_id = NVL(ip_period_id,a.period_id)
ORDER by grp_start_date;
SELECT bkpt.bkpt_detail_id bkpt_detail_id,
bkpt.bkpt_start_date bkpt_start_date,
bkpt.bkpt_end_date bkpt_end_date,
bkpt.group_bkpt_vol_start group_bkpt_vol_start,
bkpt.group_bkpt_vol_end group_bkpt_vol_end,
bkpt.period_bkpt_vol_start period_bkpt_vol_start, --Chris.T. 10FEB2004
bkpt.period_bkpt_vol_end period_bkpt_vol_end, --Chris.T. 10FEB2004
bkpt.bkpt_rate bkpt_rate,
bkpt.bkpt_header_id bkpt_header_id,
head.line_item_id line_item_id
FROM pn_var_bkpts_head_all head,
pn_var_bkpts_det_all bkpt
WHERE head.bkpt_header_id = bkpt.bkpt_header_id
AND head.period_id = ip_period_id
AND head.line_item_id = NVL(p_line_item_id,line_item_id)
ORDER by head.line_item_id,bkpt.bkpt_start_date, bkpt.bkpt_rate, bkpt.group_bkpt_vol_start;
SELECT 1
FROM pn_var_transactions_all
WHERE grp_date_id = p_grp_date_id
AND bkpt_detail_id = p_bkpt_detail_id;
SELECT distinct a.period_id
,a.group_date
,a.line_item_id
,a.bkpt_start_date
,b.item_category_code
,b.sales_type_code
FROM pn_var_transactions_all a
,pn_var_lines_all b
WHERE a.var_rent_id = ip_var_rent_id
AND a.line_item_id = NVL(p_line_item_id,b.line_item_id)
AND a.line_item_id = b.line_item_id --24SEP03 Chris.T.
ORDER BY b.item_category_code ,b.sales_type_code ,a.group_date; -- 11DEC03 Chris.T.
SELECT 1
FROM pn_var_transactions_all a
WHERE a.var_rent_id = p_var_rent_id
AND a.bkpt_end_date = p_start_date - 1
AND a.bkpt_rate not in (select b.bkpt_rate
FROM pn_var_transactions_all b
where b.var_rent_id = ip_var_rent_id
and b.bkpt_start_date = p_start_date);
SELECT 'x'
FROM DUAL
WHERE EXISTS( SELECT var_rent_id
FROM pn_var_transactions_all
WHERE var_rent_id = ip_var_rent_id
AND line_item_id = ip_line_item_id
AND bkpt_detail_id = ip_bkpt_detail_id
AND grp_date_id = ip_grp_date_id);
SELECT min(grp_start_date) fy_start_date
, ADD_MONTHS(min(grp_start_date), 12) - 1 fy_end_date
, ADD_MONTHS(max(grp_end_date), -12) + 1 ly_start_date
, max(grp_end_date) ly_end_date
FROM pn_var_grp_dates_all
WHERE var_rent_id = p_var_rent_id;
SELECT min(grp_start_date)
, min(grp_start_date)+364
, max(grp_end_date) - 364
, max(grp_end_date)
*/
/*CURSOR update_365_days_bkpt(p_ly_365_end_dt DATE)
IS
SELECT *
FROM pn_var_transactions_all
WHERE var_rent_id = p_var_rent_id
AND bkpt_end_date = p_ly_365_end_dt
AND NVL(pr_grp_blended_vol_end,0) <> 0;
CURSOR update_365_days_bkpt_strat(p_ly_365_end_dt DATE)
IS
SELECT *
FROM pn_var_transactions_all
WHERE var_rent_id = p_var_rent_id
AND bkpt_end_date = p_ly_365_end_dt
AND NVL(pr_grp_blended_vol_end,0) = 0;*/
SELECT termination_date, cumulative_vol
INTO l_vr_term_dt, l_cumulative_vol
FROM pn_var_rents_all
WHERE var_rent_id = p_var_rent_id;
SELECT MAX(end_date), ADD_MONTHS(MAX(end_date), -12)+1
INTO l_ly_365_end_dt, l_ly_365_start_dt
FROM pn_var_periods_all
WHERE var_rent_id = p_var_rent_id;
PNP_DEBUG_PKG.DEBUG('insert pn_var_Transactions');
/* dbms_output.put_line('Step - 5 - Insert'); */
PNP_DEBUG_PKG.DEBUG('inserting into pn_var_Transactions');
/*INSERT INTO pn_var_transactions_all (
transaction_id
,grp_date_id
,bkpt_detail_id
,var_rent_id
,line_item_id
,period_id
,period_start_date
,period_end_date
,group_date
,invoice_date
,bkpt_start_date
,bkpt_end_date
,no_of_group_days
,no_of_bkpt_days
,prorated_grp_vol_start
,prorated_grp_vol_end
,pr_grp_blended_vol_start
,pr_grp_blended_vol_end
,bkpt_rate
,reset_group_id
,proration_reset_group_id
,proration_rule_factor
,last_update_date
,last_updated_by
,creation_date
,created_by
,last_update_login
,org_id
)values(
pn_var_transactions_s.nextval
,c_grp.grp_date_id
,c_bkpt.bkpt_detail_id
,p_VAR_RENT_ID
,c_bkpt.line_item_id
,c_grp.period_id
,l_period_start
,l_period_end
,c_grp.group_date
,c_grp.invoice_date
,l_start_date
,l_end_date
,c_grp.no_of_group_days
,l_calc_days
,l_prorate_start
,l_prorate_end
,l_pr_prorate_start
,l_pr_prorate_end
,c_bkpt.bkpt_rate
,l_reset_group_id
,l_pro_reset_group_id
,l_factor
,sysdate
,NVL(fnd_profile.value('USER_ID'),0)
,sysdate
,NVL(fnd_profile.value('USER_ID'),0)
,NVL(fnd_profile.value('USER_ID'),0)
,to_number(decode(substr(userenv('CLIENT_INFO'),1,1),' ',null,substr(userenv('CLIENT_INFO'),1,10)))
);*/
PNP_DEBUG_PKG.DEBUG('Rows Inserted='||to_char(sql%rowcount));
/*dbms_output.put_line('Step - 5 - update'); */
PNP_DEBUG_PKG.DEBUG('update pn_var_Transactions');
/*UPDATE pn_var_transactions_all
SET no_of_group_days =c_grp.no_of_group_days
,no_of_bkpt_days =l_calc_days
,prorated_grp_vol_start =l_prorate_start
,prorated_grp_vol_end =l_prorate_end
,pr_grp_blended_vol_start =l_pr_prorate_start
,pr_grp_blended_vol_end =l_pr_prorate_end
,proration_rule_factor = l_factor
,bkpt_rate = c_bkpt.bkpt_rate
,last_update_date =sysdate
,last_updated_by = NVL(fnd_profile.value('USER_ID'),0)
,last_update_login = NVL(fnd_profile.value('USER_ID'),0 )
WHERE var_rent_id = p_var_rent_id
AND grp_date_id = c_grp.grp_date_id
AND line_item_id = c_bkpt.line_item_id
AND bkpt_detail_id = c_bkpt.bkpt_detail_id
AND bkpt_start_date = l_start_date
AND bkpt_end_date = l_end_date;*/
DBMS_OUTPUT.PUT_LINE(' Step1 Rows Updated='||to_char(sql%rowcount));*/
SELECT MAX(period_id)
INTO l_last_complete_period_id
FROM pn_var_periods_all
WHERE var_rent_id = p_var_rent_id
AND l_ly_365_start_date BETWEEN start_date AND end_date;
pnp_debug_pkg.debug('Update pn_var_Transactions with reset flag= '||l_reset_flag);
/*UPDATE pn_var_transactions_all
SET reset_group_id = l_reset_group_id_cnt --24-JUL-03 Chris T--
WHERE var_rent_id = p_VAR_RENT_ID
AND period_id = c_flag.period_id
AND line_item_id = c_flag.line_item_id
AND bkpt_start_date = c_flag.bkpt_start_date;
/*UPDATE pn_var_transactions_all
SET proration_reset_group_id = l_pro_reset_group_id_cnt
WHERE var_rent_id = p_VAR_RENT_ID
AND period_id = c_flag.period_id
AND line_item_id = c_flag.line_item_id
AND bkpt_start_date = c_flag.bkpt_start_date;
SELECT invg_freq_code
INTO l_invg_freq
FROM pn_var_rent_dates_all
WHERE var_rent_id = p_var_rent_id;
/*FOR i4 IN update_365_days_bkpt(l_ly_365_end_dt)
LOOP
FOR i2 IN csr_get_groups (p_var_rent_id,p_period_id)
LOOP
l_commencement_date := i2.commencement_date;
SELECT COUNT(*)
INTO l_cnt
FROM pn_var_transactions_all
WHERE var_rent_id = p_var_rent_id
AND bkpt_end_date = l_ly_365_end_dt
AND NVL(pr_grp_blended_vol_end, 0) <> 0;
SELECT SUM(no_of_bkpt_days)/l_cnt
INTO l_bkpt_days1
FROM pn_var_transactions_all
WHERE var_rent_id = p_var_rent_id
AND NVL(proration_reset_group_id, 0) = NVL(i4.proration_reset_group_id, 0)
--AND NVL(reset_group_id, 0) = NVL(i4.reset_group_id, 0)
AND NVL(pr_grp_blended_vol_end, 0) <> 0;
SELECT DECODE(l_invg_freq, 'MON', 0, l_prorate_start) + ((SUM(pr_grp_blended_vol_start) /
SUM(no_of_bkpt_days)) * l_bkpt_days2),
DECODE(l_invg_freq, 'MON', 0, l_prorate_end) + ((SUM(pr_grp_blended_vol_end) /
SUM(no_of_bkpt_days)) * l_bkpt_days2)
INTO l_prorate_start, l_prorate_end
FROM pn_var_transactions_all
WHERE var_rent_id = p_var_rent_id
AND NVL(proration_reset_group_id, 0) <> NVL(i4.proration_reset_group_id, 0)
--AND NVL(reset_group_id, 0) <> NVL(i4.reset_group_id, 0)
AND bkpt_end_date BETWEEN ADD_MONTHS(i4.bkpt_start_date, -11) AND i4.bkpt_start_date
AND NVL(pr_grp_blended_vol_end, 0) <> 0;
UPDATE pn_var_transactions_all
SET prorated_grp_vol_start = l_prorate_start
,prorated_grp_vol_end = l_prorate_end
,pr_grp_blended_vol_start = l_prorate_start
,pr_grp_blended_vol_end = l_prorate_end
,last_update_date = SYSDATE
,last_updated_by = NVL(FND_PROFILE.VALUE('USER_ID'),0)
,last_update_login = NVL(FND_PROFILE.VALUE('USER_ID'),0 )
WHERE transaction_id = i4.transaction_id;
DBMS_OUTPUT.PUT_LINE(' Step2 Rows Updated='||TO_CHAR(SQL%ROWCOUNT));
/*FOR i4 IN update_365_days_bkpt_strat(l_ly_365_end_dt)
LOOP
FOR i2 IN csr_get_groups (p_var_rent_id,p_period_id)
LOOP
l_commencement_date := i2.commencement_date;
SELECT COUNT(*)
INTO l_cnt
FROM pn_var_transactions_all
WHERE var_rent_id = p_var_rent_id
AND bkpt_end_date = l_ly_365_end_dt
AND NVL(pr_grp_blended_vol_end, 0) = 0;
SELECT SUM(no_of_bkpt_days)/l_cnt
INTO l_bkpt_days1
FROM pn_var_transactions_all
WHERE var_rent_id = p_var_rent_id
AND NVL(proration_reset_group_id, 0) = NVL(i4.proration_reset_group_id, 0)
--AND NVL(reset_group_id, 0) = NVL(i4.reset_group_id, 0)
AND NVL(pr_grp_blended_vol_end, 0) = 0;
SELECT DECODE(l_invg_freq, 'MON', 0, l_prorate_start) + ((SUM(pr_grp_blended_vol_start) /
SUM(no_of_bkpt_days)) * l_bkpt_days2),
DECODE(l_invg_freq, 'MON', 0, l_prorate_end) + ((SUM(pr_grp_blended_vol_end) /
SUM(no_of_bkpt_days)) * l_bkpt_days2)
INTO l_prorate_start, l_prorate_end
FROM pn_var_transactions_all
WHERE var_rent_id = p_var_rent_id
AND NVL(proration_reset_group_id, 0) <> NVL(i4.proration_reset_group_id, 0)
--AND NVL(reset_group_id, 0) <> NVL(i4.reset_group_id, 0)
AND bkpt_end_date BETWEEN ADD_MONTHS(i4.bkpt_start_date, -11) AND i4.bkpt_start_date
AND NVL(pr_grp_blended_vol_end, 0) = 0;
UPDATE pn_var_transactions_all
SET prorated_grp_vol_start = l_prorate_start
,prorated_grp_vol_end = l_prorate_end
,pr_grp_blended_vol_start = l_prorate_start
,pr_grp_blended_vol_end = l_prorate_end
,last_update_date = SYSDATE
,last_updated_by = NVL(FND_PROFILE.VALUE('USER_ID'),0)
,last_update_login = NVL(FND_PROFILE.VALUE('USER_ID'),0 )
WHERE transaction_id = i4.transaction_id;
DBMS_OUTPUT.PUT_LINE(' Step2 Rows Updated='||TO_CHAR(SQL%ROWCOUNT));
pnp_debug_pkg.log(' Call to Update Blended Period Volume - Start and END');
pnp_debug_pkg.debug('Call to Update Blended Period Volume - Start and END');
update_blended_period(p_var_rent_id => p_var_rent_id,
p_start_date => l_commencement_date,
p_proration_rule => l_proration_rule);
update_blended_period(p_var_rent_id => p_var_rent_id);
pnp_debug_pkg.log(' Call update_ytd_bkpts');
pnp_debug_pkg.debug('Call update_ytd_bkpts');
update_ytd_bkpts ( p_var_rent_id => p_var_rent_id,
p_period_id => p_period_id);
| PROCEDURE UPDATE_YTD_BKPTS
|
| DESCRIPTION
| This procedure will add the grup breakpoints to arrive at the YTD breakpoints
| the summation is reset whenever there is rate change between groups.
| In case of proration rule being combined sales with no proration or
| combined year sales with proration we will add across the
| periods. i.e the summation does not reset when the period changes. In all
| other cases the ytd summation resets when the period changes.
|
| SCOPE - PUBLIC
|
| EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
|
| ARGUMENTS : IN:
| P_VAR_RENT_ID
|
| OUT:
|
| RETURNS : None
|
|
| MODIFICATION HISTORY
|
| 16-MAR-2003 graghuna o Created
+===========================================================================*/
PROCEDURE update_ytd_bkpts(p_var_rent_id IN NUMBER,
p_period_id IN NUMBER,
p_start_date IN DATE ,
p_end_date IN DATE)
IS
/* Chris.T. 13-Aug-03 -- To accomodate Proration rules -- Start*/
CURSOR get_periods_cur IS
SELECT pvt.period_id,pvt.start_date,pvt.end_date,pvt.partial_period
,pvr.proration_rule
FROM pn_var_periods_all pvt
,pn_var_rents_all pvr
WHERE pvt.var_Rent_id = p_var_rent_id
AND pvt.var_rent_id = pvr.var_rent_id
ORDER by pvt.start_date;
SELECT pvt.*
,pvl.sales_type_code
,pvl.item_category_code
,pvr.proration_rule
,per.partial_period
,per.start_date
,per.end_date
FROM pn_var_transactions_all pvt
,pn_var_lines_all pvl
,pn_var_periods_all per
,pn_var_rents_all pvr
WHERE pvt.var_rent_id = p_var_rent_id
AND per.period_id = NVL(p_period_id,per.period_id)
AND pvt.period_id = per.period_id
AND pvt.period_id = pvl.period_id
AND per.var_rent_id = pvr.var_rent_id
AND pvt.line_item_id = pvl.line_item_id
AND pvt.bkpt_start_date >= NVL(p_start_date,pvt.bkpt_start_date)
AND pvt.bkpt_end_date <= NVL(p_end_date , pvt.bkpt_end_date)
ORDER BY pvl.sales_type_code,pvl.item_category_code,pvt.period_id,
pvt.reset_group_id,pvt.bkpt_rate,pvt.bkpt_start_date,pvt.pr_grp_blended_vol_start;
SELECT pvt.*
,pvl.sales_type_code
,pvl.item_category_code
,pvr.proration_rule
,per.partial_period
,per.start_date
,per.end_date
FROM pn_var_transactions_all pvt
,pn_var_lines_all pvl
,pn_var_periods_all per
,pn_var_rents_all pvr
WHERE pvt.var_rent_id = p_var_rent_id
AND per.period_id = NVL(p_period_id,per.period_id)
AND pvt.period_id = per.period_id
AND pvt.period_id = pvl.period_id
AND per.var_rent_id = pvr.var_rent_id
AND pvt.line_item_id = pvl.line_item_id
AND pvt.bkpt_start_date >= NVL(p_start_date,pvt.bkpt_start_date)
AND pvt.bkpt_end_date <= NVL(p_end_date , pvt.bkpt_end_date)
AND pvr.proration_rule NOT IN ('STD','NP')
ORDER BY pvl.sales_type_code,pvl.item_category_code,
pvt.proration_reset_group_id,pvt.bkpt_rate,
pvt.bkpt_start_date,pvt.pr_grp_blended_vol_start;*/
SELECT max(group_date), min(group_date), min(grp_start_date)+364
FROM pn_var_grp_dates_all
WHERE var_rent_id = p_var_rent_id;
SELECT max(group_date)
FROM pn_var_grp_dates_all
WHERE var_rent_id = p_var_rent_id
AND period_id = p_period_id;
SELECT group_date
FROM pn_var_grp_dates_all
WHERE var_rent_id = p_var_rent_id
--AND period_id = p_period_id
AND grp_start_date <= p_min_grp_dt_364
AND grp_end_date >= p_min_grp_dt_364;
SELECT period_id,partial_period
FROM pn_var_periods_all
WHERE period_id = (SELECT max(period_id)
FROM pn_var_periods_all
WHERE var_rent_id = p_var_rent_id);
pnp_debug_pkg.debug('UPDATE_YTD_BKPTS (+)');
UPDATE pn_var_transactions_all
SET ytd_group_vol_start = l_summ_vol_start,
ytd_group_vol_end = l_summ_vol_end
WHERE grp_date_id = pn_var_trx_rec.grp_date_id
AND bkpt_start_date = pn_var_trx_rec.bkpt_start_date
AND bkpt_rate = pn_var_trx_rec.bkpt_rate
AND line_item_id = pn_var_trx_rec.line_item_id
AND bkpt_detail_id = pn_var_trx_rec.bkpt_detail_id
AND reset_group_id = pn_var_trx_rec.reset_group_id;
pnp_debug_pkg.debug('Rows Updated = '||to_char(sql%rowcount));
UPDATE pn_var_transactions_all
SET pr_ytd_blended_vol_start = l_pro_bkpt_vol_start,
pr_ytd_blended_vol_end = l_pro_bkpt_vol_end
WHERE grp_date_id = pn_var_trx_rec.grp_date_id
AND bkpt_start_date = pn_var_trx_rec.bkpt_start_date
AND bkpt_rate = pn_var_trx_rec.bkpt_rate
AND line_item_id = pn_var_trx_rec.line_item_id
AND bkpt_detail_id = pn_var_trx_rec.bkpt_detail_id
AND proration_reset_group_id = pn_var_trx_rec.proration_reset_group_id;
pnp_debug_pkg.debug('Rows Updated = '||to_char(sql%rowcount));
UPDATE pn_var_transactions_all
SET pr_ytd_blended_vol_start = l_pro_bkpt_vol_start,
pr_ytd_blended_vol_end = l_pro_bkpt_vol_end,
ytd_group_vol_start = l_pro_bkpt_vol_start,
ytd_group_vol_end = l_pro_bkpt_vol_end
WHERE grp_date_id = pn_var_trx_rec.grp_date_id
AND bkpt_start_date = pn_var_trx_rec.bkpt_start_date
AND bkpt_rate = pn_var_trx_rec.bkpt_rate
AND line_item_id = pn_var_trx_rec.line_item_id
AND bkpt_detail_id = pn_var_trx_rec.bkpt_detail_id
AND proration_reset_group_id = pn_var_trx_rec.proration_reset_group_id;
pnp_debug_pkg.debug('Rows Updated = '||to_char(sql%rowcount));
UPDATE pn_var_transactions_all
SET invoice_flag = l_pro_invoice_flag
WHERE bkpt_start_date = pn_var_trx_rec.bkpt_start_date
AND line_item_id = pn_var_trx_rec.line_item_id
AND NVL(proration_reset_group_id,0) = NVL(pn_var_trx_rec.proration_reset_group_id, 0);
pnp_debug_pkg.debug('Current Updated for N,I = '||to_char(sql%rowcount));
/* dbms_output.put_line(' UPDATE for N or I, SQL%ROWCOUNT := '||SQL%ROWCOUNT);
UPDATE pn_var_transactions_all
SET invoice_flag = l_pro_invoice_flag
WHERE group_date = l_old_group_date
AND line_item_id = l_old_line_item_id
AND NVL(proration_reset_group_id, 0) = NVL(l_old_pro_reset_group_id, 0);
pnp_debug_pkg.debug('Previous Updated for F,L = '||to_char(sql%rowcount));
/* dbms_output.put_line(' UPDATE for F or L, SQL%ROWCOUNT := '||SQL%ROWCOUNT);
UPDATE pn_var_transactions_all
SET invoice_flag = NULL
WHERE var_rent_id = p_var_rent_id
AND invoice_flag = 'L';
UPDATE pn_var_transactions_all
SET invoice_flag = NULL
WHERE var_rent_id = p_var_rent_id
AND invoice_flag = 'F';
UPDATE pn_var_transactions_all
SET invoice_flag = 'N'
WHERE bkpt_start_date = pn_var_trx_rec.bkpt_start_date
AND line_item_id = pn_var_trx_rec.line_item_id
AND NVL(proration_reset_group_id, 0) = NVL(pn_var_trx_rec.proration_reset_group_id, 0);
/* dbms_output.put_line(' Current Updated for N,I = '||to_char(sql%rowcount));
pnp_debug_pkg.debug('Current Updated for N,I = '||to_char(sql%rowcount));
UPDATE pn_var_transactions_all
SET invoice_flag = 'I'
WHERE bkpt_start_date = pn_var_trx_rec.bkpt_start_date
AND line_item_id = pn_var_trx_rec.line_item_id
AND NVL(proration_reset_group_id, 0) = NVL(pn_var_trx_rec.proration_reset_group_id, 0);
UPDATE pn_var_transactions_all
SET invoice_flag = 'C'
WHERE bkpt_start_date = pn_var_trx_rec.bkpt_start_date
AND line_item_id = pn_var_trx_rec.line_item_id
AND NVL(proration_reset_group_id, 0) = NVL(pn_var_trx_rec.proration_reset_group_id, 0);
END update_ytd_bkpts;
SELECT a.bkpt_rate
FROM pn_var_transactions_all a
,pn_var_lines_all b
WHERE a.var_rent_id = p_var_rent_id
AND a.period_id = NVL(p_period_id,a.period_id)
AND a.line_item_id = b.line_item_id
AND b.item_category_code = p_item_category_code
AND b.sales_type_code = p_sales_type_code
AND a.bkpt_start_date = NVL( p_start_date,a.bkpt_start_date)
AND a.bkpt_end_date = NVL( p_end_date , a.bkpt_end_date)
ORDER BY a.period_id,a.bkpt_start_date, a.prorated_grp_vol_start; /*25-JUL-03 Chris T*/
| PROCEDURE update_blended_period
|
| DESCRIPTION
|
| SCOPE : PUBLIC
|
| EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
|
| ARGUMENTS : IN:
| P_VAR_RENT_ID
|
| OUT:
|
| MODIFICATION HISTORY
|
| 24-JUL-2003 CTHANGAI o Created
+===========================================================================*/
PROCEDURE update_blended_period ( p_var_rent_id IN NUMBER)
IS
/*CURSOR get_prorated_grp_vol_cur IS
SELECT period_id
,line_item_id
,reset_group_id
,bkpt_rate
,ROUND(SUM(prorated_grp_vol_start),2) blend_period_start
,ROUND(SUM(prorated_grp_vol_end),2) blend_period_end
FROM pn_var_transactions_all
WHERE var_rent_id = p_var_rent_id
GROUP BY period_id
,line_item_id
,reset_group_id
,bkpt_rate;
SELECT period_id
,line_item_id
,reset_group_id
,bkpt_rate
,prorated_grp_vol_start blend_period_start
,prorated_grp_vol_end blend_period_end
FROM pn_var_transactions_all
WHERE var_rent_id = p_var_rent_id;*/
UPDATE pn_var_transactions_all
SET blended_period_vol_start = get_prorated_grp_vol_rec.blend_period_start
,blended_period_vol_end = get_prorated_grp_vol_rec.blend_period_end
WHERE var_rent_id = p_var_rent_id
AND period_id = get_prorated_grp_vol_rec.period_id
AND line_item_id = get_prorated_grp_vol_rec.line_item_id
AND reset_group_id = get_prorated_grp_vol_rec.reset_group_id
AND bkpt_rate = get_prorated_grp_vol_rec.bkpt_rate;
UPDATE pn_var_transactions_all
SET blended_period_vol_start = get_prorated_grp_vol_rec.blend_period_start
,blended_period_vol_end = get_prorated_grp_vol_rec.blend_period_end
WHERE var_rent_id = p_var_rent_id
AND period_id = get_prorated_grp_vol_rec.period_id
AND line_item_id = get_prorated_grp_vol_rec.line_item_id
AND reset_group_id = get_prorated_grp_vol_rec.reset_group_id
AND bkpt_rate = get_prorated_grp_vol_rec.bkpt_rate;
END update_blended_period;
PROCEDURE update_blended_period (p_var_rent_id IN NUMBER,
p_start_date IN DATE,
p_proration_rule IN VARCHAR2)
IS
l_partial_period_id NUMBER;
SELECT period_id,start_date,end_date,partial_period
FROM pn_var_periods_all
WHERE var_rent_id = p_var_rent_id
AND start_Date = p_start_date ; -- p_start date = VR_agreement_start_date
SELECT period_id,Start_date,end_date,partial_period
FROM pn_var_periods_all
WHERE var_rent_id = p_var_rent_id
AND start_Date = p_date ; -- p_date = partial_period_end_date +1;
SELECT proration_reset_group_id
,a.bkpt_rate
,b.item_category_code
,b.sales_type_code
,min(a.group_date) min_group_date
,max(a.group_date) max_group_date
,sum(a.prorated_grp_vol_start) sum_grp_vol_start
,sum(a.prorated_grp_vol_end) sum_grp_vol_end
FROM pn_var_transactions_all a
,pn_var_lines_all b
WHERE a.var_rent_id = p_var_rent_id
AND a.group_date <= p_end_date -- end date of the complete period
AND a.line_item_id = b.line_item_id
GROUP BY a.proration_reset_group_id,
a.bkpt_rate
,b.item_category_code
,b.sales_type_code;
SELECT period_id
,line_item_id
,reset_group_id
,bkpt_rate
--,min(group_date) min_group_date
--,max(group_date) max_group_date
,min(bkpt_start_date) min_group_date
,max(bkpt_end_date) max_group_date
,ROUND(SUM(prorated_grp_vol_start),2) blend_period_start
,ROUND(SUM(prorated_grp_vol_end),2) blend_period_end
FROM pn_var_transactions_all
WHERE var_rent_id = p_var_rent_id
AND group_date > ip_end_date -- end date of the complete period
GROUP BY period_id
,line_item_id
,reset_group_id
,bkpt_rate;
SELECT min(group_date)
,max(group_date)
FROM pn_var_transactions_all
WHERE var_rent_id = p_var_rent_id
AND proration_reset_group_id = p_reset_group_id;
IS SELECT *
FROM pn_var_transactions_all trx
WHERE trx.var_rent_id = p_var_rent_id
AND trx.group_date >= p_min_grp_dt
AND trx.group_date <= p_max_grp_dt
AND trx.proration_reset_group_id = p_pr_re_grp_id
AND trx.bkpt_rate = p_bkpt_rate
AND EXISTS (SELECT 'Partial Month'
FROM pn_var_grp_dates_all grp
WHERE grp.grp_date_id = trx.grp_date_id
AND grp.grp_end_date > p_vr_term_dt);
IS SELECT *
FROM pn_var_transactions_all trx
WHERE trx.var_rent_id = p_var_rent_id
AND trx.bkpt_start_date = p_bkpt_st_dt
AND trx.blended_period_vol_start = p_period_from
AND trx.blended_period_vol_end = p_period_to;
IS SELECT *
FROM pn_var_transactions_all trx
WHERE trx.var_rent_id = p_var_rent_id
AND trx.bkpt_end_date = p_bkpt_end_dt
AND trx.blended_period_vol_start = p_period_from
AND trx.blended_period_vol_end = p_period_to;*/
SELECT termination_date
INTO l_vr_term_dt
FROM pn_var_rents_all
WHERE var_rent_id = p_var_rent_id;
SELECT DECODE(invg_freq_code, 'MON', 1,
'QTR', 3,
'SA', 6,
'YR', 12,
NULL)
INTO l_invg_freq_code
FROM pn_var_rent_dates_all
WHERE var_rent_id = p_var_rent_id;
SELECT grp_end_date
INTO l_grp_end_dt
FROM pn_var_grp_dates_all
WHERE var_rent_id = p_var_rent_id
AND group_date = annual_blended_bkpts_rec.max_group_date;
UPDATE pn_var_transactions_all trx
SET trx.pr_grp_blended_vol_start = l_group_from
,trx.pr_grp_blended_vol_end = l_group_to
,trx.blended_period_vol_start = l_period_from
,trx.blended_period_vol_end = l_period_to
,trx.invoice_flag = 'P'
WHERE var_rent_id = p_var_rent_id
AND group_date >= annual_blended_bkpts_rec.min_group_date
AND group_date <= annual_blended_bkpts_rec.max_group_date
AND proration_reset_group_id = annual_blended_bkpts_rec.proration_reset_group_id
AND bkpt_rate = annual_blended_bkpts_rec.bkpt_rate
AND bkpt_end_date <= l_complete_period_end_date;
/* DBMS_OUTPUT.PUT_LINE('Number Of Records Updated: '||SQL%ROWCOUNT);
UPDATE pn_var_transactions_all trx
SET trx.PR_GRP_BLENDED_VOL_START = l_group_from
,trx.PR_GRP_BLENDED_VOL_END = l_group_to
,trx.BLENDED_PERIOD_VOL_START = l_period_from
,trx.BLENDED_PERIOD_VOL_END = l_period_to
,trx.invoice_flag = 'P'
WHERE transaction_id = i.transaction_id;
/* DBMS_OUTPUT.PUT_LINE('Number Of First Partial Records Updated: '||SQL%ROWCOUNT);
UPDATE pn_var_transactions_all trx
SET trx.PR_GRP_BLENDED_VOL_START = l_group_from
,trx.PR_GRP_BLENDED_VOL_END = l_group_to
,trx.BLENDED_PERIOD_VOL_START = l_period_from
,trx.BLENDED_PERIOD_VOL_END = l_period_to
,trx.invoice_flag = 'P'
WHERE transaction_id = i.transaction_id;
/* DBMS_OUTPUT.PUT_LINE('Number Of Last Partial Records Updated: '||SQL%ROWCOUNT);
SELECT SUM(pr_grp_blended_vol_start), SUM(pr_grp_blended_vol_end)
INTO l_new_period_from, l_new_period_to
FROM pn_var_transactions_all
WHERE var_rent_id = p_var_rent_id
AND group_date >= annual_blended_bkpts_rec.min_group_date
AND group_date <= annual_blended_bkpts_rec.max_group_date
AND proration_reset_group_id = annual_blended_bkpts_rec.proration_reset_group_id
AND bkpt_rate = annual_blended_bkpts_rec.bkpt_rate
AND bkpt_end_date <= l_complete_period_end_date
AND blended_period_vol_start = l_period_from
AND blended_period_vol_end = l_period_to;
UPDATE pn_var_transactions_all trx
SET trx.blended_period_vol_start = l_new_period_from
,trx.blended_period_vol_end = l_new_period_to
,trx.invoice_flag = 'P'
WHERE trx.var_rent_id = p_var_rent_id
AND trx.group_date >= annual_blended_bkpts_rec.min_group_date
AND trx.group_date <= annual_blended_bkpts_rec.max_group_date
AND trx.proration_reset_group_id = annual_blended_bkpts_rec.proration_reset_group_id
AND trx.bkpt_rate = annual_blended_bkpts_rec.bkpt_rate
AND trx.bkpt_end_date <= l_complete_period_end_date
AND trx.blended_period_vol_start = l_period_from
AND trx.blended_period_vol_end = l_period_to;
/* DBMS_OUTPUT.PUT_LINE('Number Of CYNP Records Updated: '||SQL%ROWCOUNT);
UPDATE pn_var_transactions_all
SET blended_period_vol_start = l_period_from
,blended_period_vol_end = l_period_to
,invoice_flag = 'P' --denote first partial or complete period
WHERE var_rent_id = p_var_rent_id
AND group_date >= annual_blended_bkpts_rec.min_group_date
AND group_date <= annual_blended_bkpts_rec.max_group_date
AND proration_reset_group_id = annual_blended_bkpts_rec.proration_reset_group_id
AND bkpt_rate = annual_blended_bkpts_rec.bkpt_rate;
/* DBMS_OUTPUT.PUT_LINE(' Number Of Records Updated For CYP: '||SQL%ROWCOUNT);
UPDATE pn_var_transactions_all
SET blended_period_vol_start = l_period_from
,blended_period_vol_end = l_period_to
,PR_GRP_BLENDED_VOL_START = l_group_from
,PR_GRP_BLENDED_VOL_END = l_group_to
WHERE var_rent_id = p_var_rent_id
AND period_id = get_prorated_grp_vol_rec.period_id
AND line_item_id = get_prorated_grp_vol_rec.line_item_id
AND reset_group_id = get_prorated_grp_vol_rec.reset_group_id
AND bkpt_rate = get_prorated_grp_vol_rec.bkpt_rate;
UPDATE pn_var_transactions_all trx
SET trx.PR_GRP_BLENDED_VOL_START = l_group_from
,trx.PR_GRP_BLENDED_VOL_END = l_group_to
,trx.BLENDED_PERIOD_VOL_START = l_period_from
,trx.BLENDED_PERIOD_VOL_END = l_period_to
WHERE transaction_id = i.transaction_id;
/* DBMS_OUTPUT.PUT_LINE(' Number Of Partial Records Updated: '||SQL%ROWCOUNT);
UPDATE pn_var_transactions_all
SET blended_period_vol_start = get_prorated_grp_vol_rec.blend_period_start
,blended_period_vol_end = get_prorated_grp_vol_rec.blend_period_end
WHERE var_rent_id = p_var_rent_id
AND period_id = get_prorated_grp_vol_rec.period_id
AND line_item_id = get_prorated_grp_vol_rec.line_item_id
AND reset_group_id = get_prorated_grp_vol_rec.reset_group_id
AND bkpt_rate = get_prorated_grp_vol_rec.bkpt_rate;
END update_blended_period;
SELECT *
FROM pn_var_rents_all
WHERE var_rent_id = p_old_var_rent_id;
SELECT *
FROM pn_var_rent_dates_all
WHERE var_rent_id = p_old_var_rent_id;
SELECT 'x' period_exists
FROM dual
WHERE EXISTS ( SELECT period_id
FROM pn_var_periods_all
WHERE var_rent_id = p_var_rent_id);
SELECT chg_cal_var_rent_id
FROM pn_var_rents_all
WHERE var_rent_id = p_var_rent_id;
SELECT max(period_id) period_id
FROM pn_var_periods_all
WHERE var_rent_id = p_var_rent_id
AND partial_period = 'N';
SELECT group_date
FROM pn_var_grp_dates_all
WHERE var_rent_id = p_var_rent_id
AND ip_grp_date between grp_start_date and grp_end_date;
SELECT min(grp_start_date) + 364 enddate
FROM pn_var_grp_dates_all
WHERE var_rent_id = p_var_rent_id ;
SELECT group_date
FROM pn_var_grp_dates_all
WHERE var_rent_id = p_var_rent_id
AND ip_grp_date between grp_start_date and grp_end_date;
SELECT max(grp_end_date) - 364 startdate
FROM pn_var_grp_dates_all
WHERE var_rent_id = p_var_rent_id ;
SELECT *
FROM pn_var_line_defaults_all
WHERE var_rent_id = p_old_var_rent_id
AND line_end_date > p_effective_date;
SELECT *
FROM pn_var_bkhd_defaults_all
WHERE line_default_id = ip_line_default_id
AND var_rent_id = p_old_var_rent_id
AND bkhd_end_date > p_effective_date;
SELECT *
FROM pn_var_bkdt_defaults_all
WHERE var_rent_id = p_old_var_rent_id
AND bkhd_default_id = ip_bkhd_default_id
AND bkdt_end_date > p_effective_date;
PN_VAR_LINE_DEFAULTS_PKG.INSERT_ROW (
X_ROWID => l_rowid,
X_LINE_DEFAULT_ID => l_line_default_id,
X_LINE_NUM => l_line_num,
X_VAR_RENT_ID => p_new_var_rent_id,
X_SALES_TYPE_CODE => source_rec.sales_type_code,
X_ITEM_CATEGORY_CODE => source_rec.item_category_code ,
X_LINE_TEMPLATE_ID => source_rec.line_template_id,
X_AGREEMENT_TEMPLATE_ID => source_rec.agreement_template_id,
X_LINE_START_DATE => l_start_date,
X_LINE_END_DATE => source_rec.line_end_date,
X_PROCESSED_FLAG => NULL,
X_CREATION_DATE => sysdate,
X_CREATED_BY => NVL(FND_PROFILE.VALUE('USER_ID'),1),
X_LAST_UPDATE_DATE => sysdate,
X_LAST_UPDATED_BY => NVL(FND_PROFILE.VALUE('USER_ID'),1),
X_LAST_UPDATE_LOGIN => NVL(FND_PROFILE.VALUE('LOGIN_ID'),1),
X_ORG_ID => source_rec.org_id,
X_ATTRIBUTE_CATEGORY => source_rec.ATTRIBUTE_CATEGORY,
X_ATTRIBUTE1 => source_rec.ATTRIBUTE1,
X_ATTRIBUTE2 => source_rec.ATTRIBUTE2,
X_ATTRIBUTE3 => source_rec.ATTRIBUTE3,
X_ATTRIBUTE4 => source_rec.ATTRIBUTE4,
X_ATTRIBUTE5 => source_rec.ATTRIBUTE5,
X_ATTRIBUTE6 => source_rec.ATTRIBUTE6,
X_ATTRIBUTE7 => source_rec.ATTRIBUTE7,
X_ATTRIBUTE8 => source_rec.ATTRIBUTE8,
X_ATTRIBUTE9 => source_rec.ATTRIBUTE9,
X_ATTRIBUTE10 => source_rec.ATTRIBUTE10,
X_ATTRIBUTE11 => source_rec.ATTRIBUTE11,
X_ATTRIBUTE12 => source_rec.ATTRIBUTE12,
X_ATTRIBUTE13 => source_rec.ATTRIBUTE13,
X_ATTRIBUTE14 => source_rec.ATTRIBUTE14,
X_ATTRIBUTE15 => source_rec.ATTRIBUTE15);
PN_VAR_BKHD_DEFAULTS_PKG.INSERT_ROW (
X_ROWID => l_rowid,
X_BKHD_DEFAULT_ID => l_bkhd_default_id,
X_BKHD_DETAIL_NUM => l_bkhd_detail_num,
X_LINE_DEFAULT_ID => l_line_default_id,
X_BKPT_HEAD_TEMPLATE_ID => bkhd_source_rec.bkpt_head_template_id,
X_AGREEMENT_TEMPLATE_ID => bkhd_source_rec.agreement_template_id,
X_BKHD_START_DATE => l_start_date,
X_BKHD_END_DATE => bkhd_source_rec.bkhd_end_date,
X_BREAK_TYPE => bkhd_source_rec.break_type,
X_BASE_RENT_TYPE => bkhd_source_rec.base_rent_type,
X_NATURAL_BREAK_RATE => bkhd_source_rec.natural_break_rate,
X_BASE_RENT => bkhd_source_rec.base_rent,
X_BREAKPOINT_TYPE => bkhd_source_rec.breakpoint_type,
X_BREAKPOINT_LEVEL => bkhd_source_rec.breakpoint_level,
X_PROCESSED_FLAG => NULL,
X_VAR_RENT_ID => p_new_var_rent_id,
X_CREATION_DATE => sysdate,
X_CREATED_BY => NVL(FND_PROFILE.VALUE('USER_ID'),1),
X_LAST_UPDATE_DATE => sysdate,
X_LAST_UPDATED_BY => NVL(FND_PROFILE.VALUE('USER_ID'),1),
X_LAST_UPDATE_LOGIN => NVL(FND_PROFILE.VALUE('LOGIN_ID'),1),
X_ORG_ID => source_rec.org_id,
X_ATTRIBUTE_CATEGORY => bkhd_source_rec.ATTRIBUTE_CATEGORY,
X_ATTRIBUTE1 => bkhd_source_rec.ATTRIBUTE1,
X_ATTRIBUTE2 => bkhd_source_rec.ATTRIBUTE2,
X_ATTRIBUTE3 => bkhd_source_rec.ATTRIBUTE3,
X_ATTRIBUTE4 => bkhd_source_rec.ATTRIBUTE4,
X_ATTRIBUTE5 => bkhd_source_rec.ATTRIBUTE5,
X_ATTRIBUTE6 => bkhd_source_rec.ATTRIBUTE6,
X_ATTRIBUTE7 => bkhd_source_rec.ATTRIBUTE7,
X_ATTRIBUTE8 => bkhd_source_rec.ATTRIBUTE8,
X_ATTRIBUTE9 => bkhd_source_rec.ATTRIBUTE9,
X_ATTRIBUTE10 => bkhd_source_rec.ATTRIBUTE10,
X_ATTRIBUTE11 => bkhd_source_rec.ATTRIBUTE11,
X_ATTRIBUTE12 => bkhd_source_rec.ATTRIBUTE12,
X_ATTRIBUTE13 => bkhd_source_rec.ATTRIBUTE13,
X_ATTRIBUTE14 => bkhd_source_rec.ATTRIBUTE14,
X_ATTRIBUTE15 => bkhd_source_rec.ATTRIBUTE15);
PN_VAR_BKDT_DEFAULTS_PKG.INSERT_ROW (
X_ROWID => l_rowid,
X_BKDT_DEFAULT_ID => l_bkhd_default_id,
X_BKDT_DETAIL_NUM => l_bkdt_detail_num,
X_BKHD_DEFAULT_ID => l_bkhd_default_id,
X_BKDT_START_DATE => l_start_date,
X_BKDT_END_DATE => bkdt_source_rec.bkdt_end_date,
X_PERIOD_BKPT_VOL_START => bkdt_source_rec.period_bkpt_vol_start,
X_PERIOD_BKPT_VOL_END => bkdt_source_rec.period_bkpt_vol_end,
X_GROUP_BKPT_VOL_START => bkdt_source_rec.group_bkpt_vol_start,
X_GROUP_BKPT_VOL_END => bkdt_source_rec.group_bkpt_vol_end,
X_BKPT_RATE => bkdt_source_rec.bkpt_rate,
X_PROCESSED_FLAG => NULL,
X_VAR_RENT_ID => p_new_var_rent_id,
X_CREATION_DATE => sysdate,
X_CREATED_BY => NVL(FND_PROFILE.VALUE('USER_ID'),1),
X_LAST_UPDATE_DATE => sysdate,
X_LAST_UPDATED_BY => NVL(FND_PROFILE.VALUE('USER_ID'),1),
X_LAST_UPDATE_LOGIN => NVL(FND_PROFILE.VALUE('LOGIN_ID'),1),
X_ORG_ID => source_rec.org_id,
X_ANNUAL_BASIS_AMOUNT => bkdt_source_rec.annual_basis_amount,
X_ATTRIBUTE_CATEGORY => bkdt_source_rec.ATTRIBUTE_CATEGORY,
X_ATTRIBUTE1 => bkdt_source_rec.ATTRIBUTE1,
X_ATTRIBUTE2 => bkdt_source_rec.ATTRIBUTE2,
X_ATTRIBUTE3 => bkdt_source_rec.ATTRIBUTE3,
X_ATTRIBUTE4 => bkdt_source_rec.ATTRIBUTE4,
X_ATTRIBUTE5 => bkdt_source_rec.ATTRIBUTE5,
X_ATTRIBUTE6 => bkdt_source_rec.ATTRIBUTE6,
X_ATTRIBUTE7 => bkdt_source_rec.ATTRIBUTE7,
X_ATTRIBUTE8 => bkdt_source_rec.ATTRIBUTE8,
X_ATTRIBUTE9 => bkdt_source_rec.ATTRIBUTE9,
X_ATTRIBUTE10 => bkdt_source_rec.ATTRIBUTE10,
X_ATTRIBUTE11 => bkdt_source_rec.ATTRIBUTE11,
X_ATTRIBUTE12 => bkdt_source_rec.ATTRIBUTE12,
X_ATTRIBUTE13 => bkdt_source_rec.ATTRIBUTE13,
X_ATTRIBUTE14 => bkdt_source_rec.ATTRIBUTE14,
X_ATTRIBUTE15 => bkdt_source_rec.ATTRIBUTE15
);
select * from pn_var_constr_defaults_all
where var_rent_id = p_old_var_rent_id
AND constr_end_date > p_effective_date;
pn_var_constr_defaults_pkg.insert_row (
X_ROWID => l_rowid,
X_CONSTR_DEFAULT_ID => l_consDefid,
X_CONSTR_DEFAULT_NUM => l_consNum,
X_VAR_RENT_ID => p_new_var_rent_id,
X_AGREEMENT_TEMPLATE_ID => c_crec.agreement_template_id,
X_CONSTR_TEMPLATE_ID => c_crec.constr_template_id ,
X_CONSTR_START_DATE => l_start_date,
X_CONSTR_END_DATE => c_crec.CONSTR_END_DATE,
X_CONSTR_CAT_CODE => c_crec.CONSTR_CAT_CODE,
X_TYPE_CODE => c_crec.type_code,
X_AMOUNT => c_crec.amount,
X_CREATION_DATE => sysdate,
X_CREATED_BY => NVL(fnd_profile.value('USER_ID'),0),
X_LAST_UPDATE_DATE => sysdate,
X_LAST_UPDATED_BY => NVL(fnd_profile.value('USER_ID'),0),
X_LAST_UPDATE_LOGIN => NVL(fnd_profile.value('LOGIN_ID'),0),
X_ORG_ID => c_crec.ORG_ID,
X_ATTRIBUTE_CATEGORY => c_crec.ATTRIBUTE_CATEGORY,
X_ATTRIBUTE1 => c_crec.ATTRIBUTE1,
X_ATTRIBUTE2 => c_crec.ATTRIBUTE2,
X_ATTRIBUTE3 => c_crec.ATTRIBUTE3,
X_ATTRIBUTE4 => c_crec.ATTRIBUTE4,
X_ATTRIBUTE5 => c_crec.ATTRIBUTE5,
X_ATTRIBUTE6 => c_crec.ATTRIBUTE6,
X_ATTRIBUTE7 => c_crec.ATTRIBUTE7,
X_ATTRIBUTE8 => c_crec.ATTRIBUTE8,
X_ATTRIBUTE9 => c_crec.ATTRIBUTE9,
X_ATTRIBUTE10 => c_crec.ATTRIBUTE10,
X_ATTRIBUTE11 => c_crec.ATTRIBUTE11,
X_ATTRIBUTE12 => c_crec.ATTRIBUTE12,
X_ATTRIBUTE13 => c_crec.ATTRIBUTE13,
X_ATTRIBUTE14 => c_crec.ATTRIBUTE14,
X_ATTRIBUTE15 => c_crec.ATTRIBUTE15
);
SELECT invoice_date,
inv_start_date
,inv_end_date
,inv_schedule_date
FROM pn_var_grp_dates_all
WHERE var_rent_id = p_var_rent_id
AND ip_date between inv_start_date and inv_end_date;
SELECT decode(actual_Exp_code ,'Y',NVL(actual_invoiced_amount,0),0) invoiced_amt
,invoice_date
FROM pn_var_rent_inv_all
WHERE var_rent_id = p_var_rent_id
AND invoice_date >= ip_invoice_date;
SELECT max(invoice_date) invoice_date
FROM pn_var_grp_dates_all
WHERE var_rent_id = p_var_rent_id;
SELECT period_id
FROM pn_var_periods_all
WHERE var_rent_id = p_var_rent_id
AND ip_date between start_date and end_date ;
SELECT max(adjust_num) adjust_num
FROM pn_var_rent_inv_all
WHERE period_id = ip_period_id
AND invoice_date = ip_invoice_date;
SELECT invoice_date
from pn_var_grp_dates_all
where var_rent_id = p_var_rent_id
and p_effective_date between inv_start_date and inv_end_date ;
PN_VAR_RENT_INV_PKG.INSERT_ROW (
X_ROWID => l_rowid,
X_VAR_RENT_INV_ID => l_rent_inv_id,
X_ADJUST_NUM => l_adjust_num,
X_INVOICE_DATE => l_invoice_create_date,
X_FOR_PER_RENT => NULL,
X_TOT_ACT_VOL => NULL,
X_ACT_PER_RENT => NULL,
X_CONSTR_ACTUAL_RENT => NULL,
X_ABATEMENT_APPL => NULL,
X_REC_ABATEMENT => NULL,
X_REC_ABATEMENT_OVERRIDE => NULL,
X_NEGATIVE_RENT => NULL,
X_ACTUAL_INVOICED_AMOUNT => round(l_credit_amount,2),
X_PERIOD_ID => l_period_id,
X_VAR_RENT_ID => p_var_rent_id,
X_FORECASTED_TERM_STATUS => 'N',
X_VARIANCE_TERM_STATUS => 'N',
X_ACTUAL_TERM_STATUS => 'N',
X_FORECASTED_EXP_CODE => 'N',
X_VARIANCE_EXP_CODE => 'N',
X_ACTUAL_EXP_CODE => 'N',
X_COMMENTS => 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_CREATION_DATE => sysdate,
X_CREATED_BY => NVL(fnd_profile.value('USER_ID'),0),
X_LAST_UPDATE_DATE => sysdate,
X_LAST_UPDATED_BY => NVL(fnd_profile.value('USER_ID'),0),
X_LAST_UPDATE_LOGIN => NVL(fnd_profile.value('LOGIN_ID'),0),
X_ORG_ID => NVL(fnd_profile.value('org_id') ,239)
);