[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT
line.line_item_id
FROM
pn_var_lines_all line
WHERE
line.var_rent_id = p_vr_id AND
line.bkpt_update_flag = 'Y'
/*FOR UPDATE NOWAIT*/;
SELECT
line.line_item_id
FROM
pn_var_lines_all line
WHERE
line.var_rent_id = p_vr_id AND
line.sales_vol_update_flag = 'Y'
/*FOR UPDATE NOWAIT*/;
SELECT
line.line_item_id
FROM
pn_var_lines_all line
WHERE
line.var_rent_id = p_vr_id
/*FOR UPDATE NOWAIT*/;
SELECT proration_rule
FROM pn_var_rents_all
WHERE var_rent_id = p_var_rent_id;
SELECT proration_rule
FROM pn_var_rents_all vr
,pn_var_periods_all prd
WHERE vr.var_rent_id = prd.var_rent_id
AND prd.period_id = p_period_id;
SELECT
trx_header_id
FROM
pn_var_trx_headers_all
WHERE
var_rent_id = p_vr_id AND
period_id = p_period_id AND
line_item_id = p_line_item_id AND
grp_date_id = p_grp_date_id AND
calc_prd_start_date = p_calc_prd_st_dt AND
calc_prd_end_date = p_calc_prd_end_dt;
SELECT
trx_detail_id
FROM
pn_var_trx_details_all
WHERE
trx_header_id = p_trx_hdr_id AND
bkpt_detail_id = p_bkpt_dtl_id;
PROCEDURE insert_trx_hdr(p_trx_header_id IN OUT NOCOPY NUMBER
,p_var_rent_id IN NUMBER
,p_period_id IN NUMBER
,p_line_item_id IN NUMBER
,p_grp_date_id IN NUMBER
,p_calc_prd_start_date IN DATE
,p_calc_prd_end_date IN DATE
,p_var_rent_summ_id IN NUMBER
,p_line_item_group_id IN NUMBER
,p_reset_group_id IN NUMBER
,p_proration_factor IN NUMBER
,p_reporting_group_sales IN NUMBER
,p_prorated_group_sales IN NUMBER
,p_ytd_sales IN NUMBER
,p_fy_proration_sales IN NUMBER
,p_ly_proration_sales IN NUMBER
,p_percent_rent_due IN NUMBER
,p_ytd_percent_rent IN NUMBER
,p_calculated_rent IN NUMBER
,p_prorated_rent_due IN NUMBER
,p_invoice_flag IN VARCHAR2
,p_org_id IN NUMBER
,p_last_update_date IN DATE
,p_last_updated_by IN NUMBER
,p_creation_date IN DATE
,p_created_by IN NUMBER
,p_last_update_login IN NUMBER) IS
BEGIN
INSERT INTO pn_var_trx_headers_all
(trx_header_id
,var_rent_id
,period_id
,line_item_id
,grp_date_id
,calc_prd_start_date
,calc_prd_end_date
,var_rent_summ_id
,line_item_group_id
,reset_group_id
,proration_factor
,reporting_group_sales
,prorated_group_sales
,ytd_sales
,fy_proration_sales
,ly_proration_sales
,percent_rent_due
,ytd_percent_rent
,calculated_rent
,prorated_rent_due
,invoice_flag
,org_id
,last_update_date
,last_updated_by
,creation_date
,created_by
,last_update_login)
VALUES
(pn_var_trx_headers_S.NEXTVAL
,p_var_rent_id
,p_period_id
,p_line_item_id
,p_grp_date_id
,p_calc_prd_start_date
,p_calc_prd_end_date
,p_var_rent_summ_id
,p_line_item_group_id
,p_reset_group_id
,round(p_proration_factor,10)
,p_reporting_group_sales
,p_prorated_group_sales
,p_ytd_sales
,p_fy_proration_sales
,p_ly_proration_sales
,round(p_percent_rent_due,g_precision) /*Bug # 6031202*/
,round(p_ytd_percent_rent,g_precision)
,round(p_calculated_rent,g_precision)
,round(p_prorated_rent_due,g_precision)
,p_invoice_flag
,p_org_id
,SYSDATE
,NVL(fnd_global.user_id,0)
,SYSDATE
,NVL(fnd_global.user_id,0)
,NVL(fnd_global.login_id,0))
RETURNING trx_header_id INTO p_trx_header_id;
END insert_trx_hdr;
PROCEDURE insert_trx_dtl(p_trx_detail_id IN OUT NOCOPY NUMBER
,p_trx_header_id IN NUMBER
,p_bkpt_detail_id IN NUMBER
,p_bkpt_rate IN NUMBER
,p_prorated_grp_vol_start IN NUMBER
,p_prorated_grp_vol_end IN NUMBER
,p_fy_pr_grp_vol_start IN NUMBER
,p_fy_pr_grp_vol_end IN NUMBER
,p_ly_pr_grp_vol_start IN NUMBER
,p_ly_pr_grp_vol_end IN NUMBER
,p_pr_grp_blended_vol_start IN NUMBER
,p_pr_grp_blended_vol_end IN NUMBER
,p_ytd_group_vol_start IN NUMBER
,p_ytd_group_vol_end IN NUMBER
,p_blended_period_vol_start IN NUMBER
,p_blended_period_vol_end IN NUMBER
,p_org_id IN NUMBER
,p_last_update_date IN DATE
,p_last_updated_by IN NUMBER
,p_creation_date IN DATE
,p_created_by IN NUMBER
,p_last_update_login IN NUMBER) IS
BEGIN
INSERT INTO pn_var_trx_details_all
(trx_detail_id
,trx_header_id
,bkpt_detail_id
,bkpt_rate
,prorated_grp_vol_start
,prorated_grp_vol_end
,fy_pr_grp_vol_start
,fy_pr_grp_vol_end
,ly_pr_grp_vol_start
,ly_pr_grp_vol_end
,pr_grp_blended_vol_start
,pr_grp_blended_vol_end
,ytd_group_vol_start
,ytd_group_vol_end
,blended_period_vol_start
,blended_period_vol_end
,org_id
,last_update_date
,last_updated_by
,creation_date
,created_by
,last_update_login)
VALUES
(pn_var_trx_details_S.NEXTVAL
,p_trx_header_id
,p_bkpt_detail_id
,p_bkpt_rate
,p_prorated_grp_vol_start
,p_prorated_grp_vol_end
,p_fy_pr_grp_vol_start
,p_fy_pr_grp_vol_end
,p_ly_pr_grp_vol_start
,p_ly_pr_grp_vol_end
,p_pr_grp_blended_vol_start
,p_pr_grp_blended_vol_end
,p_ytd_group_vol_start
,p_ytd_group_vol_end
,p_blended_period_vol_start
,p_blended_period_vol_end
,p_org_id
,SYSDATE
,NVL(fnd_global.user_id,0)
,SYSDATE
,NVL(fnd_global.user_id,0)
,NVL(fnd_global.login_id,0))
RETURNING trx_detail_id INTO p_trx_detail_id;
END insert_trx_dtl;
SELECT 1
FROM dual
WHERE EXISTS
(SELECT
trx_header_id
FROM
pn_var_trx_headers_all
WHERE
var_rent_id = p_vr_id AND
line_item_group_id IS NULL
AND rownum = 1);
SELECT
NVL(line.sales_type_code, 'NULL') AS sales_type_code
,NVL(line.item_category_code, 'NULL') AS item_category_code
FROM
pn_var_lines_all line
WHERE
line.var_rent_id = p_vr_id
GROUP BY
NVL(line.sales_type_code, 'NULL')
,NVL(line.item_category_code, 'NULL');
UPDATE
pn_var_trx_headers_all
SET
line_item_group_id = l_line_grp_id
WHERE
line_item_id IN
( SELECT
line.line_item_id
FROM
pn_var_lines_all line
WHERE
line.var_rent_id = p_var_rent_id AND
NVL(line.sales_type_code, 'NULL')
= NVL(line_typ_rec.sales_type_code, 'NULL') AND
NVL(line.item_category_code, 'NULL')
= NVL(line_typ_rec.item_category_code, 'NULL')
);
SELECT
trx_header_id
,calc_prd_start_date
,line_item_group_id
FROM
pn_var_trx_headers_all
WHERE
var_rent_id = p_vr_id
ORDER BY
line_item_group_id
,calc_prd_start_date;
SELECT
bkpt_rate
FROM
pn_var_trx_details_all
WHERE
trx_header_id = p_trx_hrd_id
ORDER BY
prorated_grp_vol_start;
trx_hdr_tbl.DELETE;
reset_ctr_tbl.DELETE;
rate_tbl_1.DELETE;
rate_tbl_2.DELETE;
rate_tbl_1.DELETE;
rate_tbl_2.DELETE;
/* update trx hdr, set reset grp ID */
FORALL hdr_rec IN 1..trx_hdr_tbl.COUNT
UPDATE
pn_var_trx_headers_all
SET
reset_group_id = reset_ctr_tbl(hdr_rec)
WHERE
trx_header_id = trx_hdr_tbl(hdr_rec);
SELECT
vr.var_rent_id
,vr.commencement_date
,vr.termination_date
,vr.proration_rule
FROM
pn_var_rents_all vr
WHERE
vr.var_rent_id = p_vr_id;
SELECT
hdr.trx_header_id
,hdr.calc_prd_start_date
,hdr.calc_prd_end_date
FROM
pn_var_trx_headers_all hdr
WHERE
hdr.var_rent_id = p_vr_id AND
p_date BETWEEN (hdr.calc_prd_start_date + 1) AND hdr.calc_prd_end_date;
SELECT
prd.period_id
,prd.partial_period
FROM
pn_var_periods_all prd
WHERE
prd.var_rent_id = p_var_rent_id AND
prd.end_date = p_term_date;
UPDATE
pn_var_trx_headers_all
SET
invoice_flag = NULL
WHERE
var_rent_id = p_var_rent_id AND
invoice_flag IN ('N', 'I');
UPDATE
pn_var_trx_headers_all
SET
invoice_flag = 'N'
WHERE
var_rent_id = p_var_rent_id AND
period_id = l_last_period_id;
UPDATE
pn_var_trx_headers_all
SET
invoice_flag = 'I'
WHERE
var_rent_id = p_var_rent_id AND
calc_prd_end_date = l_vr_termination_date;
UPDATE
pn_var_trx_details_all
SET
ly_pr_grp_vol_start = NULL
,ly_pr_grp_vol_end = NULL
WHERE
trx_header_id IN (SELECT
trx_header_id
FROM
pn_var_trx_headers_all
WHERE
var_rent_id = p_var_rent_id);
UPDATE
pn_var_trx_details_all
SET
ly_pr_grp_vol_start = prorated_grp_vol_start
,ly_pr_grp_vol_end = prorated_grp_vol_end
WHERE
trx_header_id IN (SELECT
trx_header_id
FROM
pn_var_trx_headers_all
WHERE
var_rent_id = p_var_rent_id AND
calc_prd_start_date >= l_ly_start_date);
UPDATE
pn_var_trx_details_all
SET
ly_pr_grp_vol_start = prorated_grp_vol_start * l_proration_factor
,ly_pr_grp_vol_end = prorated_grp_vol_end * l_proration_factor
WHERE
trx_header_id = trx_rec.trx_header_id;
SELECT
vr.var_rent_id
,vr.commencement_date
,vr.termination_date
,vr.proration_rule
FROM
pn_var_rents_all vr
WHERE
vr.var_rent_id = p_vr_id;
SELECT
hdr.trx_header_id
,hdr.calc_prd_start_date
,hdr.calc_prd_end_date
FROM
pn_var_trx_headers_all hdr
WHERE
hdr.var_rent_id = p_vr_id AND
p_date BETWEEN hdr.calc_prd_start_date AND (hdr.calc_prd_end_date - 1);
SELECT
prd.period_id
,prd.partial_period
FROM
pn_var_periods_all prd
WHERE
prd.var_rent_id = p_var_rent_id AND
prd.start_date = p_comm_date;
UPDATE
pn_var_trx_headers_all
SET
invoice_flag = NULL
WHERE
var_rent_id = p_var_rent_id AND
invoice_flag IN ('N', 'I');
UPDATE
pn_var_trx_headers_all
SET
invoice_flag = 'N'
WHERE
var_rent_id = p_var_rent_id AND
period_id = (SELECT
prd.period_id
FROM
pn_var_periods_all prd
WHERE
prd.var_rent_id = p_var_rent_id AND
prd.start_date = l_vr_commencement_date AND
prd.partial_period = 'Y');
UPDATE
pn_var_trx_headers_all
SET
invoice_flag = 'I'
WHERE
var_rent_id = p_var_rent_id AND
l_fy_end_date BETWEEN calc_prd_start_date AND calc_prd_end_date;
UPDATE
pn_var_trx_details_all
SET
fy_pr_grp_vol_start = NULL
,fy_pr_grp_vol_end = NULL
WHERE
trx_header_id IN (SELECT
trx_header_id
FROM
pn_var_trx_headers_all
WHERE
var_rent_id = p_var_rent_id);
UPDATE
pn_var_trx_details_all
SET
fy_pr_grp_vol_start = prorated_grp_vol_start
,fy_pr_grp_vol_end = prorated_grp_vol_end
WHERE
trx_header_id IN (SELECT
trx_header_id
FROM
pn_var_trx_headers_all
WHERE
var_rent_id = p_var_rent_id AND
calc_prd_end_date <= l_fy_end_date);
UPDATE
pn_var_trx_details_all
SET
fy_pr_grp_vol_start = prorated_grp_vol_start * l_proration_factor
,fy_pr_grp_vol_end = prorated_grp_vol_end * l_proration_factor
WHERE
trx_header_id = trx_rec.trx_header_id;
SELECT
vr.var_rent_id
,vr.commencement_date
,vr.termination_date
,vr.proration_rule
FROM
pn_var_rents_all vr
WHERE
vr.var_rent_id = p_vr_id;
SELECT
period_id
,start_date
,end_date
,partial_period
FROM
pn_var_periods_all
WHERE
var_rent_id = p_vr_id
ORDER BY
start_date;
SELECT
SUM(grp.proration_factor) proration_factor_sum
FROM
pn_var_grp_dates_all grp
WHERE
prd.period_id = p_prd_id
GROUP BY
grp.period_id;
SELECT
SUM(grp.proration_factor) proration_factor_sum
FROM
pn_var_grp_dates_all grp
,pn_var_periods_all prd
WHERE
prd.period_id = p_prd_id AND
grp.period_id = prd.period_id AND
grp.grp_end_date <= prd.end_date
GROUP BY
grp.period_id;
SELECT /*+ LEADING(hdr) */
hdr.line_item_group_id
,dtl.bkpt_rate
,SUM(dtl.prorated_grp_vol_start) AS blended_period_vol_start
,SUM(dtl.prorated_grp_vol_end) AS blended_period_vol_end
FROM
pn_var_trx_headers_all hdr
,pn_var_trx_details_all dtl
WHERE
hdr.var_rent_id = p_vr_id AND
hdr.period_id IN (p_part_prd_ID, p_full_prd_ID) AND
dtl.trx_header_id = hdr.trx_header_id
GROUP BY
hdr.line_item_group_id
,dtl.bkpt_rate;
SELECT
hdr.trx_header_id
,grp.grp_date_id
,grp.proration_factor AS grp_prorat_factor
,hdr.proration_factor AS calc_prd_prorat_factor
FROM
pn_var_trx_headers_all hdr
,pn_var_grp_dates_all grp
WHERE
hdr.var_rent_id = p_vr_id AND
hdr.period_id = p_prd_id /*AND
hdr.calc_prd_start_date = p_prd_st_dt*/ AND
grp.grp_date_id = hdr.grp_date_id;
SELECT
hdr.trx_header_id
,grp.grp_date_id
,grp.proration_factor AS grp_prorat_factor
,hdr.proration_factor AS calc_prd_prorat_factor
FROM
pn_var_trx_headers_all hdr
,pn_var_grp_dates_all grp
WHERE
hdr.var_rent_id = p_vr_id AND
hdr.period_id = p_prd_id /*AND
hdr.calc_prd_end_date = p_prd_end_dt*/ AND
grp.grp_date_id = hdr.grp_date_id;
l_context := 'CYP - update invoice_flag';
UPDATE
pn_var_trx_headers_all
SET
invoice_flag = NULL
WHERE
var_rent_id = p_var_rent_id AND
invoice_flag = 'P';
UPDATE
pn_var_trx_headers_all
SET
invoice_flag = 'P'
WHERE
trx_header_id IN (SELECT
trx_header_id
FROM
pn_var_trx_headers_all
WHERE
var_rent_id = p_var_rent_id AND
period_id IN (l_part_prd_id, l_full_prd_id)
);
l_context := 'CYP - update pr_grp_blended_vol_start - end';
UPDATE
pn_var_trx_details_all
SET
pr_grp_blended_vol_start = NULL
,pr_grp_blended_vol_end = NULL
WHERE
trx_header_id IN (SELECT
trx_header_id
FROM
pn_var_trx_headers_all
WHERE
var_rent_id = p_var_rent_id);
UPDATE
pn_var_trx_details_all
SET
pr_grp_blended_vol_start = prorated_grp_vol_start
,pr_grp_blended_vol_end = prorated_grp_vol_end
WHERE
trx_header_id IN (SELECT
trx_header_id
FROM
pn_var_trx_headers_all
WHERE
var_rent_id = p_var_rent_id AND
period_id IN (l_part_prd_id, l_full_prd_id)
);
l_context := 'CYP - update blended_period_vol_start - end';
UPDATE
pn_var_trx_details_all
SET
blended_period_vol_start = cyp_rec.blended_period_vol_start
,blended_period_vol_end = cyp_rec.blended_period_vol_end
WHERE
trx_header_id IN (SELECT
trx_header_id
FROM
pn_var_trx_headers_all
WHERE
var_rent_id = p_var_rent_id AND
period_id IN (l_part_prd_id, l_full_prd_id) AND
line_item_group_id = cyp_rec.line_item_group_id
) AND
bkpt_rate = cyp_rec.bkpt_rate;
l_context := 'CYNP - update invoice_flag';
UPDATE
pn_var_trx_headers_all
SET
invoice_flag = NULL
WHERE
var_rent_id = p_var_rent_id AND
invoice_flag = 'P';
UPDATE
pn_var_trx_headers_all
SET
invoice_flag = 'P'
WHERE
trx_header_id IN (SELECT
trx_header_id
FROM
pn_var_trx_headers_all
WHERE
var_rent_id = p_var_rent_id AND
period_id IN (l_part_prd_id, l_full_prd_id)
);
:= 'CYNP - update pr_grp_blended_vol_start - end, blended_period_vol_start - end';
UPDATE
pn_var_trx_details_all
SET
pr_grp_blended_vol_start = NULL
,pr_grp_blended_vol_end = NULL
WHERE
trx_header_id IN (SELECT
trx_header_id
FROM
pn_var_trx_headers_all
WHERE
var_rent_id = p_var_rent_id);
UPDATE
pn_var_trx_details_all
SET
blended_period_vol_start
= (cynp_rec.blended_period_vol_start / (l_part_prd_length + l_full_prd_length))
* l_full_prd_length
,blended_period_vol_end
= (cynp_rec.blended_period_vol_end /(l_part_prd_length + l_full_prd_length))
* l_full_prd_length
,pr_grp_blended_vol_start
= (cynp_rec.blended_period_vol_start /(l_part_prd_length + l_full_prd_length))
* (l_full_prd_length / (l_part_prd_length + l_full_prd_length))
,pr_grp_blended_vol_end
= (cynp_rec.blended_period_vol_end /(l_part_prd_length + l_full_prd_length))
* (l_full_prd_length / (l_part_prd_length + l_full_prd_length))
WHERE
trx_header_id IN (SELECT
trx_header_id
FROM
pn_var_trx_headers_all
WHERE
var_rent_id = p_var_rent_id AND
period_id IN (l_part_prd_id, l_full_prd_id)AND
line_item_group_id = cynp_rec.line_item_group_id
) AND
bkpt_rate = cynp_rec.bkpt_rate;
:= 'CYNP - update pr_grp_blended_vol_start - end for first/last partial';
/* update first partial calc sub period pr_grp_blended_vol_start - end */
FOR first_part_rec IN first_partial_cs_c( p_vr_id => p_var_rent_id
,p_prd_id => l_part_prd_id
,p_prd_st_dt => l_part_prd_start_dt)
LOOP
UPDATE
pn_var_trx_details_all
SET
pr_grp_blended_vol_start
= pr_grp_blended_vol_start
* first_part_rec.grp_prorat_factor
* first_part_rec.calc_prd_prorat_factor
,pr_grp_blended_vol_end
= pr_grp_blended_vol_end
* first_part_rec.grp_prorat_factor
* first_part_rec.calc_prd_prorat_factor
WHERE
trx_header_id = first_part_rec.trx_header_id;
/* update last partial calc sub period pr_grp_blended_vol_start - end */
FOR last_part_rec IN last_partial_cs_c( p_vr_id => p_var_rent_id
,p_prd_id => l_full_prd_id
,p_prd_end_dt => l_full_prd_end_dt)
LOOP
UPDATE
pn_var_trx_details_all
SET
pr_grp_blended_vol_start
= pr_grp_blended_vol_start
* last_part_rec.grp_prorat_factor
* last_part_rec.calc_prd_prorat_factor
,pr_grp_blended_vol_end
= pr_grp_blended_vol_end
* last_part_rec.grp_prorat_factor
* last_part_rec.calc_prd_prorat_factor
WHERE
trx_header_id = last_part_rec.trx_header_id;
SELECT
vr.org_id
,vr.var_rent_id
,vr.commencement_date
,vr.termination_date
,vr.proration_rule
,vr.cumulative_vol
FROM
pn_var_rents_all vr
WHERE
vr.var_rent_id = p_vr_id;
SELECT
period_id
,start_date
,end_date
,partial_period
FROM
pn_var_periods_all
WHERE
var_rent_id = p_vr_id
ORDER BY
start_date;
/* get the line items to update */
CURSOR lines_c(p_vr_id IN NUMBER) IS
SELECT
period_id
,line_item_id
FROM
pn_var_lines_all
WHERE
var_rent_id = p_vr_id AND
bkpt_update_flag = 'Y'
ORDER BY
period_id
,line_item_id;
/* get the line items to update */
CURSOR lines_cs_c ( p_vr_id IN NUMBER
,p_part_prd_id IN NUMBER
,p_full_prd_id IN NUMBER) IS
SELECT
period_id
,line_item_id
FROM
pn_var_lines_all
WHERE
var_rent_id = p_vr_id AND
bkpt_update_flag = 'Y' AND
period_id NOT IN (p_part_prd_id, p_full_prd_id)
ORDER BY
period_id
,line_item_id;
SELECT /*+ LEADING(hdr) */
dtl.trx_detail_id
,SUM(prorated_grp_vol_start) OVER
(PARTITION BY
hdr.period_id
,hdr.line_item_id
,hdr.reset_group_id
,pbd.group_bkpt_vol_start
,pbd.group_bkpt_vol_end
ORDER BY
hdr.calc_prd_start_date
ROWS UNBOUNDED PRECEDING) AS ytd_group_vol_start
,SUM(prorated_grp_vol_end) OVER
(PARTITION BY
hdr.period_id
,hdr.line_item_id
,hdr.reset_group_id
,pbd.group_bkpt_vol_start
,pbd.group_bkpt_vol_end
ORDER BY
hdr.calc_prd_start_date
ROWS UNBOUNDED PRECEDING) AS ytd_group_vol_end
FROM
pn_var_trx_headers_all hdr
,pn_var_trx_details_all dtl
,PN_VAR_BKPTS_DET_ALL pbd
,pn_var_bkpts_head_all pbh
WHERE
hdr.var_rent_id = p_vr_id AND
hdr.period_id = p_prd_ID AND
hdr.line_item_id = p_line_ID AND
dtl.trx_header_id = hdr.trx_header_id
and pbd.var_rent_id = hdr.var_rent_id
and pbd.bkpt_rate = dtl.bkpt_rate
and pbd.bkpt_header_id = pbh.bkpt_header_id
and pbd.bkpt_detail_id = dtl.bkpt_detail_id
and pbh.line_item_id = hdr.line_item_id
and pbh.period_id = hdr.period_id /* 8616530 */
ORDER BY
hdr.period_id
,hdr.line_item_id
,hdr.calc_prd_start_date;
SELECT /*+ LEADING(hdr) */
dtl.trx_detail_id
,SUM(prorated_grp_vol_start) OVER
(PARTITION BY
hdr.period_id
,hdr.line_item_id
,hdr.reset_group_id
,dtl.bkpt_rate
ORDER BY
hdr.calc_prd_start_date
ROWS UNBOUNDED PRECEDING) AS ytd_group_vol_start
,SUM(prorated_grp_vol_end) OVER
(PARTITION BY
hdr.period_id
,hdr.line_item_id
,hdr.reset_group_id
,dtl.bkpt_rate
ORDER BY
hdr.calc_prd_start_date
ROWS UNBOUNDED PRECEDING) AS ytd_group_vol_end
FROM
pn_var_trx_headers_all hdr
,pn_var_trx_details_all dtl
WHERE
hdr.var_rent_id = p_vr_id AND
hdr.period_id = p_prd_ID AND
hdr.line_item_id = p_line_ID AND
dtl.trx_header_id = hdr.trx_header_id
ORDER BY
hdr.period_id
,hdr.line_item_id
,hdr.calc_prd_start_date;
SELECT /*+ LEADING(hdr) */
dtl.trx_detail_id
,SUM(pr_grp_blended_vol_start) OVER
(PARTITION BY
hdr.line_item_group_id
,dtl.bkpt_rate
ORDER BY
hdr.calc_prd_start_date
ROWS UNBOUNDED PRECEDING) AS ytd_group_vol_start
,SUM(pr_grp_blended_vol_end) OVER
(PARTITION BY
hdr.line_item_group_id
,dtl.bkpt_rate
ORDER BY
hdr.calc_prd_start_date
ROWS UNBOUNDED PRECEDING) AS ytd_group_vol_end
FROM
pn_var_trx_headers_all hdr
,pn_var_trx_details_all dtl
WHERE
hdr.var_rent_id = p_vr_id AND
hdr.period_id IN (p_part_prd_id, p_full_prd_id) AND
dtl.trx_header_id = hdr.trx_header_id
ORDER BY
hdr.line_item_group_id
,hdr.calc_prd_start_date;
select distinct v.var_rent_id
from pn_var_rents_all v, pn_var_bkhd_defaults_all hd,
pn_var_bkdt_defaults_all dd
WHERE V.VAR_RENT_ID = HD.VAR_RENT_ID
and v.var_rent_id = p_vr_ID
and hd.bkhd_default_id = dd.bkhd_default_id
and hd.breakpoint_type = 'STRATIFIED'
and exists (select 'Y'
from pn_var_bkdt_defaults_all dd2
where dd2.bkhd_default_id = dd.bkhd_default_id
and dd2.bkdt_default_id <> dd.bkdt_default_id
and dd2.bkpt_rate = dd.bkpt_rate
AND DD2.BKDT_START_DATE = DD.BKDT_START_DATE
and dd2.bkdt_end_date = dd.bkdt_end_date);
trx_detail_t.DELETE;
ytd_grp_vol_start_t.DELETE;
ytd_grp_vol_end_t.DELETE;
UPDATE
pn_var_trx_details_all
SET
ytd_group_vol_start = ytd_grp_vol_start_t(i)
,ytd_group_vol_end = ytd_grp_vol_end_t(i)
WHERE
trx_detail_id = trx_detail_t(i);
trx_detail_t.DELETE;
ytd_grp_vol_start_t.DELETE;
ytd_grp_vol_end_t.DELETE;
UPDATE
pn_var_trx_details_all
SET
ytd_group_vol_start = ytd_grp_vol_start_t(i)
,ytd_group_vol_end = ytd_grp_vol_end_t(i)
WHERE
trx_detail_id = trx_detail_t(i);
trx_detail_t.DELETE;
ytd_grp_vol_start_t.DELETE;
ytd_grp_vol_end_t.DELETE;
UPDATE
pn_var_trx_details_all
SET
ytd_group_vol_start = ytd_grp_vol_start_t(i)
,ytd_group_vol_end = ytd_grp_vol_end_t(i)
WHERE
trx_detail_id = trx_detail_t(i);
SELECT
vr.org_id
,vr.var_rent_id
,vr.commencement_date
,vr.termination_date
,vr.proration_rule
,vr.cumulative_vol
FROM
pn_var_rents_all vr
WHERE
vr.var_rent_id = p_vr_id;
SELECT
period_id
FROM
pn_var_periods_all
WHERE
var_rent_id = p_vr_id
ORDER BY
start_date;
SELECT /*+ LEADING(hdr) */
hdr.period_id
,hdr.line_item_id
,hdr.reset_group_id
,dtl.bkpt_rate
,dtl.bkpt_detail_id
,SUM(prorated_grp_vol_start) AS blended_period_vol_start
,SUM(prorated_grp_vol_end) AS blended_period_vol_end
FROM
pn_var_trx_headers_all hdr
,pn_var_trx_details_all dtl
WHERE
hdr.var_rent_id = p_vr_id AND
hdr.period_id = p_prd_id AND
hdr.line_item_id IN (SELECT
line_item_id
FROM
pn_var_lines_all
WHERE
var_rent_id = p_vr_id AND
period_id = p_prd_id AND
bkpt_update_flag = 'Y') AND
dtl.trx_header_id = hdr.trx_header_id
GROUP BY
hdr.period_id
,hdr.line_item_id
,hdr.reset_group_id
,dtl.bkpt_rate
,dtl.bkpt_detail_id; --bug#8631183
SELECT period_id
FROM pn_var_periods_all
WHERE period_id = p_period_id
AND period_num=1
AND partial_period='Y';
SELECT
prd.period_id
,prd.partial_period
FROM
pn_var_periods_all prd,
pn_var_rents_all var
WHERE
prd.period_id = p_period_id AND
prd.var_rent_id = var.var_rent_id AND
prd.end_date = var.termination_date AND
prd.partial_period='Y';
l_period_t.DELETE;
UPDATE
pn_var_trx_details_all
SET
blended_period_vol_start = rec.blended_period_vol_start
,blended_period_vol_end = rec.blended_period_vol_end
WHERE
trx_header_id IN
(SELECT
trx_header_id
FROM
pn_var_trx_headers_all
WHERE
var_rent_id = p_var_rent_id AND
period_id = rec.period_id AND
line_item_id = rec.line_item_id AND
reset_group_id = rec.reset_group_id) AND
bkpt_rate = rec.bkpt_rate
AND bkpt_detail_id = rec.bkpt_detail_id; --bug#8631183
PROCEDURE delete_transactions( p_var_rent_id IN NUMBER
,p_period_id IN NUMBER
,p_line_item_id IN NUMBER) IS
BEGIN
IF p_line_item_id IS NOT NULL AND
p_period_id IS NOT NULL AND
p_var_rent_id IS NOT NULL
THEN
pnp_debug_pkg.log('Deleting for lines');
DELETE
pn_var_trx_details_all
WHERE
trx_header_id IN
( SELECT
trx_header_id
FROM
pn_var_trx_headers_all
WHERE
var_rent_id = p_var_rent_id AND
period_id = p_period_id AND
line_item_id = p_line_item_id );
DELETE
pn_var_trx_headers_all
WHERE
var_rent_id = p_var_rent_id AND
period_id = p_period_id AND
line_item_id = p_line_item_id;
DELETE
pn_var_trx_details_all
WHERE
trx_header_id IN
( SELECT
trx_header_id
FROM
pn_var_trx_headers_all
WHERE
var_rent_id = p_var_rent_id AND
period_id = p_period_id);
DELETE
pn_var_trx_headers_all
WHERE
var_rent_id = p_var_rent_id AND
period_id = p_period_id;
DELETE
pn_var_trx_details_all
WHERE
trx_header_id IN
( SELECT
trx_header_id
FROM
pn_var_trx_headers_all
WHERE
var_rent_id = p_var_rent_id);
DELETE
pn_var_trx_headers_all
WHERE
var_rent_id = p_var_rent_id;
END delete_transactions;
SELECT
vr.org_id
,vr.var_rent_id
,vr.commencement_date
,vr.termination_date
,vr.proration_rule
,vr.cumulative_vol
FROM
pn_var_rents_all vr
WHERE
vr.var_rent_id = p_vr_id;
SELECT
period_id
FROM
pn_var_trx_headers_all
WHERE
var_rent_id = p_vr_id
MINUS
SELECT
period_id
FROM
pn_var_periods_all
WHERE
var_rent_id = p_vr_id AND
status IS NULL;
SELECT
prd.var_rent_id
,prd.period_id
,prd.start_date
,prd.end_date
FROM
pn_var_periods_all prd
WHERE
prd.var_rent_id = p_vr_id AND
prd.status IS NULL
ORDER BY
prd.start_date;
SELECT
line_item_id
FROM
pn_var_trx_headers_all
WHERE
var_rent_id = p_vr_id AND
period_id = p_prd_id
MINUS
SELECT
line_item_id
FROM
pn_var_lines_all
WHERE
var_rent_id = p_vr_id AND
period_id = p_prd_id;
SELECT *
FROM pn_var_trx_headers_all
WHERE var_rent_id = ip_vr_id
AND period_id = ip_prd_id;
SELECT
grp.grp_date_id
,grp.grp_start_date
,grp.grp_end_date
,grp.group_date
,grp.invoice_date
,grp.proration_factor
FROM
pn_var_grp_dates_all grp
WHERE
grp.period_id = p_prd_id
ORDER BY
grp.grp_start_date;
SELECT
grp.grp_date_id
,grp.grp_start_date
,grp.grp_end_date
,grp.group_date
,grp.invoice_date
,grp.proration_factor
FROM
pn_var_grp_dates_all grp
,pn_var_periods_all prd
WHERE
prd.period_id = p_prd_id AND
grp.period_id = prd.period_id AND
grp.grp_end_date <= prd.end_date
ORDER BY
grp.grp_start_date;
SELECT
line.line_item_id
,line.line_default_id
FROM
pn_var_lines_all line
WHERE
line.period_id = p_prd_id AND
line.bkpt_update_flag = 'Y' AND
EXISTS (SELECT null
FROM pn_var_bkpts_det_all
WHERE bkpt_header_id IN ( SELECT bkpt_header_id
FROM pn_var_bkpts_head_all
WHERE line_item_id = line.line_item_id))
ORDER BY
line_item_id;*/
SELECT
line.line_item_id
,line.line_default_id
FROM
pn_var_lines_all line,
pn_var_bkpts_head_all bph
WHERE
line.period_id = p_prd_id AND
line.bkpt_update_flag = 'Y' AND
bph.period_id = line.period_id AND
EXISTS (SELECT null
FROM pn_var_bkpts_det_all bpd
WHERE bpd.bkpt_header_id = bph.bkpt_header_id
AND rownum = 1)
order BY line_item_id;
SELECT
bkpt.bkpt_detail_id
,bkpt.bkpt_start_date
,bkpt.bkpt_end_date
,bkpt.group_bkpt_vol_start
,bkpt.group_bkpt_vol_end
,bkpt.period_bkpt_vol_start
,bkpt.period_bkpt_vol_end
,bkpt.bkpt_rate
FROM
pn_var_bkpts_head_all head
,pn_var_bkpts_det_all bkpt
WHERE
head.line_item_id = p_line_item_id AND
bkpt.bkpt_header_id = head.bkpt_header_id
ORDER BY
bkpt.bkpt_start_date
,bkpt.group_bkpt_vol_start;
SELECT /*+ LEADING(hdr) */
hdr.line_item_id
,hdr.calc_prd_start_date
,hdr.calc_prd_end_date
,hdr.period_id
,dtls.bkpt_rate
,hdr.reset_group_id
,hdr.trueup_rent_due
FROM
pn_var_trx_headers_all hdr
,pn_var_trx_details_all dtls
WHERE
hdr.trx_header_id = dtls.trx_header_id AND
hdr.var_rent_id = p_var_rent_id
ORDER BY
hdr.line_item_id
,hdr.calc_prd_start_date
,dtls.bkpt_rate;
l_line_items_lock4bkpt_t.DELETE;
for_data_t.DELETE;
trueup_table.DELETE;
periods_table.DELETE;
/* assume we will not create/update trx */
l_trx_create_upd_flag := FALSE;
/* delete trx records for periods that do not exist anymore */
FOR del_rec IN chk_for_del_prd_c(p_vr_id => p_var_rent_id) LOOP
l_trx_create_upd_flag := TRUE;
pn_var_trx_pkg.delete_transactions
( p_var_rent_id => p_var_rent_id
,p_period_id => del_rec.period_id
,p_line_item_id => NULL);
/* delete trx records for a contracted period */
BEGIN
DELETE FROM pn_var_trx_headers_all
WHERE
var_rent_id = p_var_rent_id AND
calc_prd_end_date > l_vr_termination_date;
/* delete trx records for lines that do not exist anymore in this period */
FOR del_rec IN chk_for_del_line_c( p_vr_id => p_var_rent_id
,p_prd_id => period_rec.period_id) LOOP
l_trx_create_upd_flag := TRUE;
pn_var_trx_pkg.delete_transactions
( p_var_rent_id => p_var_rent_id
,p_period_id => period_rec.period_id
,p_line_item_id => del_rec.line_item_id);
groups_cur_tbl.DELETE;
/* create trx for all line items that have bkpts updated */
FOR line_item_rec IN line_items_c(p_prd_id => period_rec.period_id) LOOP
l_trx_create_upd_flag := TRUE;
pn_var_trx_pkg.delete_transactions
( p_var_rent_id => p_var_rent_id
,p_period_id => period_rec.period_id
,p_line_item_id => line_item_rec.line_item_id);
bkpts_tbl.DELETE;
pn_var_trx_pkg.insert_trx_hdr
(p_trx_header_id => l_trx_hdr_id
,p_var_rent_id => period_rec.var_rent_id
,p_period_id => period_rec.period_id
,p_line_item_id => line_item_rec.line_item_id
,p_grp_date_id => groups_cur_tbl(grp_rec).grp_date_id
,p_calc_prd_start_date => l_calc_prd_start_dt
,p_calc_prd_end_date => l_calc_prd_end_dt
,p_var_rent_summ_id => NULL
,p_line_item_group_id => line_item_rec.line_default_id
,p_reset_group_id => NULL
,p_proration_factor => l_proration_factor
,p_reporting_group_sales => NULL
,p_prorated_group_sales => NULL
,p_ytd_sales => NULL
,p_fy_proration_sales => NULL
,p_ly_proration_sales => NULL
,p_percent_rent_due => NULL
,p_ytd_percent_rent => NULL
,p_calculated_rent => NULL
,p_prorated_rent_due => NULL
,p_invoice_flag => NULL
,p_org_id => l_org_id
,p_last_update_date => NULL
,p_last_updated_by => NULL
,p_creation_date => NULL
,p_created_by => NULL
,p_last_update_login => NULL);
pn_var_trx_pkg.insert_trx_dtl
(p_trx_detail_id => l_trx_dtl_id
,p_trx_header_id => l_trx_hdr_id
,p_bkpt_detail_id => bkpts_tbl(bkpt_rec - 1).
bkpt_dtls_tbl(bkpt_dtl_rec).
bkpt_detail_id
,p_bkpt_rate => bkpts_tbl(bkpt_rec - 1).
bkpt_dtls_tbl(bkpt_dtl_rec).
bkpt_rate
,p_prorated_grp_vol_start => l_prorated_grp_vol_start
,p_prorated_grp_vol_end => l_prorated_grp_vol_end
,p_fy_pr_grp_vol_start => NULL
,p_fy_pr_grp_vol_end => NULL
,p_ly_pr_grp_vol_start => NULL
,p_ly_pr_grp_vol_end => NULL
,p_pr_grp_blended_vol_start => NULL
,p_pr_grp_blended_vol_end => NULL
,p_ytd_group_vol_start => NULL
,p_ytd_group_vol_end => NULL
,p_blended_period_vol_start => NULL
,p_blended_period_vol_end => NULL
,p_org_id => l_org_id
,p_last_update_date => NULL
,p_last_updated_by => NULL
,p_creation_date => NULL
,p_created_by => NULL
,p_last_update_login => NULL);
pn_var_trx_pkg.insert_trx_hdr
(p_trx_header_id => l_trx_hdr_id
,p_var_rent_id => period_rec.var_rent_id
,p_period_id => period_rec.period_id
,p_line_item_id => line_item_rec.line_item_id
,p_grp_date_id => groups_cur_tbl(grp_rec).grp_date_id
,p_calc_prd_start_date => l_calc_prd_start_dt
,p_calc_prd_end_date => l_calc_prd_end_dt
,p_var_rent_summ_id => NULL
,p_line_item_group_id => line_item_rec.line_default_id
,p_reset_group_id => NULL
,p_proration_factor => l_proration_factor
,p_reporting_group_sales => NULL
,p_prorated_group_sales => NULL
,p_ytd_sales => NULL
,p_fy_proration_sales => NULL
,p_ly_proration_sales => NULL
,p_percent_rent_due => NULL
,p_ytd_percent_rent => NULL
,p_calculated_rent => NULL
,p_prorated_rent_due => NULL
,p_invoice_flag => NULL
,p_org_id => l_org_id
,p_last_update_date => NULL
,p_last_updated_by => NULL
,p_creation_date => NULL
,p_created_by => NULL
,p_last_update_login => NULL);
pn_var_trx_pkg.insert_trx_dtl
(p_trx_detail_id => l_trx_dtl_id
,p_trx_header_id => l_trx_hdr_id
,p_bkpt_detail_id => bkpts_tbl(l_curr_bkpt_ctr).
bkpt_dtls_tbl(bkpt_dtl_rec).
bkpt_detail_id
,p_bkpt_rate => bkpts_tbl(l_curr_bkpt_ctr).
bkpt_dtls_tbl(bkpt_dtl_rec).
bkpt_rate
,p_prorated_grp_vol_start => l_prorated_grp_vol_start
,p_prorated_grp_vol_end => l_prorated_grp_vol_end
,p_fy_pr_grp_vol_start => NULL
,p_fy_pr_grp_vol_end => NULL
,p_ly_pr_grp_vol_start => NULL
,p_ly_pr_grp_vol_end => NULL
,p_pr_grp_blended_vol_start => NULL
,p_pr_grp_blended_vol_end => NULL
,p_ytd_group_vol_start => NULL
,p_ytd_group_vol_end => NULL
,p_blended_period_vol_start => NULL
,p_blended_period_vol_end => NULL
,p_org_id => l_org_id
,p_last_update_date => NULL
,p_last_updated_by => NULL
,p_creation_date => NULL
,p_created_by => NULL
,p_last_update_login => NULL);
/* get the grp IDs right if any trx was updated */
IF l_trx_create_upd_flag THEN
pnp_debug_pkg.log('Trx updated');
UPDATE
pn_var_trx_headers_all
SET
REPORTING_GROUP_SALES_FOR = for_data_t(i).REPORTING_GROUP_SALES_FOR
,PRORATED_GROUP_SALES_FOR = for_data_t(i).PRORATED_GROUP_SALES_FOR
,YTD_SALES_FOR = for_data_t(i).YTD_SALES_FOR
,CALCULATED_RENT_FOR = round(for_data_t(i).CALCULATED_RENT_FOR,g_precision) /*Bug # 6031202*/
,PERCENT_RENT_DUE_FOR = round(for_data_t(i).PERCENT_RENT_DUE_FOR,g_precision)
,YTD_PERCENT_RENT_FOR = round(for_data_t(i).YTD_PERCENT_RENT_FOR,g_precision)
WHERE var_rent_id = for_data_t(i).var_rent_id AND
grp_date_id = for_data_t(i).grp_date_id AND
line_item_id = for_data_t(i).line_item_id AND
reset_group_id = for_data_t(i).reset_group_id;
UPDATE
pn_var_trx_headers_all
SET
trueup_rent_due = round(trueup_table(i).trueup_rent_due,g_precision) /*Bug # 6031202*/
WHERE
var_rent_id = p_var_rent_id AND
line_item_id = trueup_table(i).line_item_id AND
calc_prd_start_date = trueup_table(i).calc_prd_start_date AND
calc_prd_end_date = trueup_table(i).calc_prd_end_date AND
reset_group_id = trueup_table(i).reset_group_id AND
period_id = periods_table(j).period_id;
/* reset the bkpt_update_flag */
FORALL line_rec IN 1..l_line_items_lock4bkpt_t.COUNT
UPDATE
pn_var_lines_all
SET
bkpt_update_flag = NULL
,sales_vol_update_flag = 'Y'
WHERE
line_item_id = l_line_items_lock4bkpt_t(line_rec);
SELECT
SUM(actual_amount) AS calc_prd_actual_volume
FROM
pn_var_vol_hist_all sales
WHERE
sales.period_id = p_prd_id AND
sales.line_item_id = p_line_id AND
sales.grp_date_id = p_grp_id AND
vol_hist_status_code = pn_var_trx_pkg.G_SALESVOL_STATUS_APPROVED;
SELECT
sales.actual_amount
,sales.start_date
,sales.end_date
FROM
pn_var_vol_hist_all sales
WHERE
sales.period_id = p_prd_id AND
sales.line_item_id = p_line_id AND
sales.grp_date_id = p_grp_id AND
sales.start_date <= p_end_dt AND
sales.end_date >= p_start_dt AND
vol_hist_status_code = pn_var_trx_pkg.G_SALESVOL_STATUS_APPROVED;
SELECT
grp.grp_start_date
,grp.grp_end_date
FROM
pn_var_grp_dates_all grp
WHERE
grp.grp_date_id = p_grp_id;
SELECT
SUM(actual_amount) AS calc_prd_actual_volume
FROM
pn_var_vol_hist_all sales
WHERE
sales.period_id = p_prd_id AND
sales.line_item_id = p_line_id AND
sales.grp_date_id = p_grp_id AND
vol_hist_status_code = pn_var_trx_pkg.G_SALESVOL_STATUS_APPROVED;
SELECT
sales.actual_amount
,sales.start_date
,sales.end_date
FROM
pn_var_vol_hist_all sales
WHERE
sales.period_id = p_prd_id AND
sales.line_item_id = p_line_id AND
sales.grp_date_id = p_grp_id AND
sales.start_date <= p_end_dt AND
sales.end_date >= p_start_dt AND
vol_hist_status_code = pn_var_trx_pkg.G_SALESVOL_STATUS_APPROVED;
SELECT
grp.grp_start_date
,grp.grp_end_date
FROM
pn_var_grp_dates_all grp
WHERE
grp.grp_date_id = p_grp_id;
SELECT
vr.var_rent_id
,vr.commencement_date
,vr.termination_date
,vr.proration_rule
FROM
pn_var_rents_all vr
WHERE
vr.var_rent_id = p_vr_id;
SELECT
prd.period_id
,prd.partial_period
FROM
pn_var_periods_all prd
WHERE
prd.var_rent_id = p_var_rent_id AND
prd.end_date = p_term_date;
UPDATE
pn_var_trx_headers_all
SET
ly_proration_sales = NULL
WHERE
var_rent_id = p_var_rent_id;
UPDATE
pn_var_trx_headers_all hdr
SET
hdr.ly_proration_sales = hdr.prorated_group_sales
WHERE
hdr.var_rent_id = p_var_rent_id AND
hdr.calc_prd_start_date >= l_ly_start_date;
UPDATE
pn_var_trx_headers_all
SET
ly_proration_sales
= pn_var_trx_pkg.get_calc_prd_sales( var_rent_id
,period_id
,line_item_id
,grp_date_id
,l_ly_start_date
,calc_prd_end_date)
WHERE
var_rent_id = p_var_rent_id AND
l_ly_start_date BETWEEN (calc_prd_start_date + 1)
AND calc_prd_end_date;
SELECT
vr.var_rent_id
,vr.commencement_date
,vr.termination_date
,vr.proration_rule
FROM
pn_var_rents_all vr
WHERE
vr.var_rent_id = p_vr_id;
SELECT
prd.period_id
,prd.partial_period
FROM
pn_var_periods_all prd
WHERE
prd.var_rent_id = p_var_rent_id AND
prd.start_date = p_comm_date;
UPDATE
pn_var_trx_headers_all
SET
fy_proration_sales = NULL
WHERE
var_rent_id = p_var_rent_id;
UPDATE
pn_var_trx_headers_all hdr
SET
hdr.fy_proration_sales = hdr.prorated_group_sales
WHERE
hdr.var_rent_id = p_var_rent_id AND
hdr.calc_prd_end_date <= l_fy_end_date;
UPDATE
pn_var_trx_headers_all
SET
fy_proration_sales
= pn_var_trx_pkg.get_calc_prd_sales( var_rent_id
,period_id
,line_item_id
,grp_date_id
,calc_prd_start_date
,l_fy_end_date)
WHERE
var_rent_id = p_var_rent_id AND
l_fy_end_date BETWEEN calc_prd_start_date
AND (calc_prd_end_date - 1);
SELECT
vr.var_rent_id
,vr.proration_rule
FROM
pn_var_rents_all vr
WHERE
vr.var_rent_id = p_vr_id;
/* get the line items with updated sales for FY, LY, FLY, STD, NP */
CURSOR lines_c(p_vr_id IN NUMBER) IS
SELECT
period_id
,line_item_id
FROM
pn_var_lines_all
WHERE
var_rent_id = p_vr_id AND
sales_vol_update_flag = 'Y'
ORDER BY
period_id
,line_item_id;
/* get the line items with updated sales for CYP, CYNP */
CURSOR lines_cs_c( p_vr_id IN NUMBER
,p_part_prd_id IN NUMBER
,p_full_prd_id IN NUMBER) IS
SELECT
period_id
,line_item_id
FROM
pn_var_lines_all
WHERE
var_rent_id = p_vr_id AND
sales_vol_update_flag = 'Y' AND
period_id NOT IN (p_part_prd_id, p_full_prd_id)
ORDER BY
period_id
,line_item_id;
SELECT
period_id
,start_date
,end_date
,partial_period
FROM
pn_var_periods_all
WHERE
var_rent_id = p_vr_id
ORDER BY
start_date;
SELECT
hdr.trx_header_id
,SUM(hdr.prorated_group_sales) OVER
(PARTITION BY
hdr.period_id
,hdr.line_item_id
,hdr.reset_group_id
ORDER BY
hdr.calc_prd_start_date
ROWS UNBOUNDED PRECEDING) AS ytd_sales
FROM
pn_var_trx_headers_all hdr
WHERE
hdr.var_rent_id = p_vr_id AND
hdr.period_id = p_prd_ID AND
hdr.line_item_id = p_line_ID
ORDER BY
hdr.period_id
,hdr.line_item_id
,hdr.calc_prd_start_date;
SELECT
hdr.trx_header_id
,SUM(hdr.prorated_group_sales) OVER
(PARTITION BY
hdr.line_item_group_id
ORDER BY
hdr.calc_prd_start_date
ROWS UNBOUNDED PRECEDING) AS ytd_sales
FROM
pn_var_trx_headers_all hdr
WHERE
hdr.var_rent_id = p_vr_id AND
hdr.period_id IN (p_part_prd_id, p_full_prd_id)
ORDER BY
hdr.line_item_group_id
,hdr.calc_prd_start_date;
trx_hdr_t.DELETE;
ytd_sales_t.DELETE;
UPDATE
pn_var_trx_headers_all
SET
ytd_sales = ytd_sales_t(i)
WHERE
trx_header_id = trx_hdr_t(i);
trx_hdr_t.DELETE;
ytd_sales_t.DELETE;
UPDATE
pn_var_trx_headers_all
SET
ytd_sales = ytd_sales_t(i)
WHERE
trx_header_id = trx_hdr_t(i);
trx_hdr_t.DELETE;
ytd_sales_t.DELETE;
UPDATE
pn_var_trx_headers_all
SET
ytd_sales = ytd_sales_t(i)
WHERE
trx_header_id = trx_hdr_t(i);
SELECT
vr.org_id
,vr.var_rent_id
,vr.commencement_date
,vr.termination_date
,vr.proration_rule
,vr.cumulative_vol
FROM
pn_var_rents_all vr
WHERE
vr.var_rent_id = p_vr_id;
/* get the line items with updated sales */
CURSOR lines_c(p_vr_id IN NUMBER) IS
SELECT
period_id
,line_item_id
FROM
pn_var_lines_all
WHERE
var_rent_id = p_vr_id AND
sales_vol_update_flag = 'Y'
ORDER BY
period_id
,line_item_id;
/* get the calc periods to update sales data */
CURSOR calc_periods_c( p_vr_id IN NUMBER
,p_prd_id IN NUMBER
,p_line_id IN NUMBER) IS
SELECT
hdr.trx_header_id
,hdr.var_rent_id
,hdr.period_id
,hdr.line_item_id
,hdr.grp_date_id
,hdr.calc_prd_start_date
,hdr.calc_prd_end_date
FROM
pn_var_trx_headers_all hdr
WHERE
hdr.var_rent_id = p_vr_id AND
hdr.period_id = p_prd_id AND
hdr.line_item_id = p_line_id
ORDER BY
hdr.period_id
,hdr.line_item_id
,hdr.calc_prd_start_date
,hdr.calc_prd_end_date;
/* lock the lines with updated sales */
l_line_items_lock4salesvol_t.DELETE;
trx_hdr_t.DELETE;
reporting_grp_sales_t.DELETE;
prorate_grp_sales_t.DELETE;
update the trx headers with the sales */
IF trx_hdr_t.COUNT > 0 THEN
FORALL i IN trx_hdr_t.FIRST..trx_hdr_t.LAST
UPDATE
pn_var_trx_headers_all
SET
reporting_group_sales = reporting_grp_sales_t(i)
,prorated_group_sales = prorate_grp_sales_t(i)
WHERE
trx_header_id = trx_hdr_t(i);
UPDATE
pn_var_lines_all
SET
sales_vol_update_flag = NULL
WHERE
line_item_id = l_line_items_lock4salesvol_t(line_rec);
/* UN-lock the lines with updated sales */
COMMIT;
SELECT
SUM(forecasted_amount) AS calc_prd_forecasted_volume
FROM
pn_var_vol_hist_all sales
WHERE
sales.period_id = p_prd_id AND
sales.line_item_id = p_line_id AND
sales.grp_date_id = p_grp_id;
SELECT
sales.forecasted_amount
,sales.start_date
,sales.end_date
FROM
pn_var_vol_hist_all sales
WHERE
sales.period_id = p_prd_id AND
sales.line_item_id = p_line_id AND
sales.grp_date_id = p_grp_id AND
sales.start_date <= p_end_dt AND
sales.end_date >= p_start_dt;
SELECT
grp.grp_start_date
,grp.grp_end_date
FROM
pn_var_grp_dates_all grp
WHERE
grp.grp_date_id = p_grp_id;
SELECT
SUM(forecasted_amount) AS calc_prd_forecasted_volume
FROM
pn_var_vol_hist_all sales
WHERE
sales.period_id = p_prd_id AND
sales.line_item_id = p_line_id AND
sales.grp_date_id = p_grp_id;
SELECT
sales.forecasted_amount
,sales.start_date
,sales.end_date
FROM
pn_var_vol_hist_all sales
WHERE
sales.period_id = p_prd_id AND
sales.line_item_id = p_line_id AND
sales.grp_date_id = p_grp_id AND
sales.start_date <= p_end_dt AND
sales.end_date >= p_start_dt;
SELECT
grp.grp_start_date
,grp.grp_end_date
FROM
pn_var_grp_dates_all grp
WHERE
grp.grp_date_id = p_grp_id;
SELECT
vr.var_rent_id
,vr.cumulative_vol
FROM
pn_var_rents_all vr
WHERE
vr.var_rent_id = p_vr_id;
/* get the line items with updated sales for FY, LY, FLY, STD, NP */
CURSOR lines_c(p_vr_id IN NUMBER) IS
SELECT
period_id
,line_item_id
FROM
pn_var_lines_all
WHERE
var_rent_id = p_vr_id AND
sales_vol_update_flag = 'Y'
ORDER BY
period_id
,line_item_id;
SELECT
hdr.trx_header_id
,SUM(hdr.prorated_group_sales_for) OVER
(PARTITION BY
hdr.period_id
,hdr.line_item_id
,hdr.reset_group_id
ORDER BY
hdr.calc_prd_start_date
ROWS UNBOUNDED PRECEDING) AS ytd_sales_for
FROM
pn_var_trx_headers_all hdr
WHERE
hdr.var_rent_id = p_vr_id AND
hdr.period_id = p_prd_ID AND
hdr.line_item_id = p_line_ID
ORDER BY
hdr.period_id
,hdr.line_item_id
,hdr.calc_prd_start_date;
trx_hdr_t.DELETE;
ytd_sales_for_t.DELETE;
UPDATE
pn_var_trx_headers_all
SET
ytd_sales_for = ytd_sales_for_t(i)
WHERE
trx_header_id = trx_hdr_t(i);
SELECT
vr.org_id
,vr.var_rent_id
,vr.commencement_date
,vr.termination_date
,vr.proration_rule
,vr.cumulative_vol
FROM
pn_var_rents_all vr
WHERE
vr.var_rent_id = p_vr_id;
/* get the line items with updated sales */
CURSOR lines_c(p_vr_id IN NUMBER) IS
SELECT
period_id
,line_item_id
FROM
pn_var_lines_all
WHERE
var_rent_id = p_vr_id AND
sales_vol_update_flag = 'Y'
ORDER BY
period_id
,line_item_id;
/* get the calc periods to update sales data */
CURSOR calc_periods_c( p_vr_id IN NUMBER
,p_prd_id IN NUMBER
,p_line_id IN NUMBER) IS
SELECT
hdr.trx_header_id
,hdr.var_rent_id
,hdr.period_id
,hdr.line_item_id
,hdr.grp_date_id
,hdr.calc_prd_start_date
,hdr.calc_prd_end_date
FROM
pn_var_trx_headers_all hdr
WHERE
hdr.var_rent_id = p_vr_id AND
hdr.period_id = p_prd_id AND
hdr.line_item_id = p_line_id
ORDER BY
hdr.period_id
,hdr.line_item_id
,hdr.calc_prd_start_date
,hdr.calc_prd_end_date;
/* lock the lines with updated sales */
l_line_items_lock4salesvol_t.DELETE;
trx_hdr_t.DELETE;
reporting_grp_sales_t.DELETE;
prorate_grp_sales_t.DELETE;
update the trx headers with the sales */
IF trx_hdr_t.COUNT > 0 THEN
FORALL i IN trx_hdr_t.FIRST..trx_hdr_t.LAST
UPDATE
pn_var_trx_headers_all
SET
reporting_group_sales_for = reporting_grp_sales_t(i)
,prorated_group_sales_for = prorate_grp_sales_t(i)
WHERE
trx_header_id = trx_hdr_t(i);
UPDATE
pn_var_lines_all
SET
sales_vol_update_flag = NULL
WHERE
line_item_id = l_line_items_lock4salesvol_t(line_rec);*/
/* UN-lock the lines with updated sales */
COMMIT;
SELECT
SUM(deduction_amount) AS calc_prd_dedc
FROM
pn_var_deductions_all dedc
WHERE
dedc.period_id = p_prd_id AND
dedc.line_item_id = p_line_id AND
dedc.grp_date_id = p_grp_id;
SELECT
dedc.deduction_amount
,dedc.start_date
,dedc.end_date
FROM
pn_var_deductions_all dedc
WHERE
dedc.period_id = p_prd_id AND
dedc.line_item_id = p_line_id AND
dedc.grp_date_id = p_grp_id AND
dedc.start_date <= p_end_dt AND
dedc.end_date >= p_start_dt;
SELECT
grp.grp_start_date
,grp.grp_end_date
FROM
pn_var_grp_dates_all grp
WHERE
grp.grp_date_id = p_grp_id;
SELECT
SUM(deduction_amount) AS calc_prd_dedc
FROM
pn_var_deductions_all dedc
WHERE
dedc.period_id = p_prd_id AND
dedc.line_item_id = p_line_id AND
dedc.grp_date_id = p_grp_id;
SELECT
dedc.deduction_amount
,dedc.start_date
,dedc.end_date
FROM
pn_var_deductions_all dedc
WHERE
dedc.period_id = p_prd_id AND
dedc.line_item_id = p_line_id AND
dedc.grp_date_id = p_grp_id AND
dedc.start_date <= p_end_dt AND
dedc.end_date >= p_start_dt;
SELECT
grp.grp_start_date
,grp.grp_end_date
FROM
pn_var_grp_dates_all grp
WHERE
grp.grp_date_id = p_grp_id;
SELECT
vr.var_rent_id
,vr.commencement_date
,vr.termination_date
,vr.proration_rule
FROM
pn_var_rents_all vr
WHERE
vr.var_rent_id = p_vr_id;
UPDATE
pn_var_trx_headers_all
SET
ly_proration_deductions = NULL
WHERE
var_rent_id = p_var_rent_id;
UPDATE
pn_var_trx_headers_all hdr
SET
hdr.ly_proration_deductions = hdr.prorated_group_deductions
WHERE
hdr.var_rent_id = p_var_rent_id AND
hdr.calc_prd_start_date >= l_ly_start_date;
UPDATE
pn_var_trx_headers_all
SET
ly_proration_deductions
= pn_var_trx_pkg.get_calc_prd_dedc( var_rent_id
,period_id
,line_item_id
,grp_date_id
,l_ly_start_date
,calc_prd_end_date)
WHERE
var_rent_id = p_var_rent_id AND
l_ly_start_date BETWEEN (calc_prd_start_date + 1)
AND calc_prd_end_date;
SELECT
vr.var_rent_id
,vr.commencement_date
,vr.termination_date
,vr.proration_rule
FROM
pn_var_rents_all vr
WHERE
vr.var_rent_id = p_vr_id;
UPDATE
pn_var_trx_headers_all
SET
fy_proration_deductions = NULL
WHERE
var_rent_id = p_var_rent_id;
UPDATE
pn_var_trx_headers_all hdr
SET
hdr.fy_proration_deductions = hdr.prorated_group_deductions
WHERE
hdr.var_rent_id = p_var_rent_id AND
hdr.calc_prd_end_date <= l_fy_end_date;
UPDATE
pn_var_trx_headers_all
SET
fy_proration_deductions
= pn_var_trx_pkg.get_calc_prd_dedc( var_rent_id
,period_id
,line_item_id
,grp_date_id
,calc_prd_start_date
,l_fy_end_date)
WHERE
var_rent_id = p_var_rent_id AND
l_fy_end_date BETWEEN calc_prd_start_date
AND (calc_prd_end_date - 1);
SELECT
vr.var_rent_id
,vr.proration_rule
FROM
pn_var_rents_all vr
WHERE
vr.var_rent_id = p_vr_id;
/* get the line items with updated deductions for FY, LY, FLY, STD, NP */
CURSOR lines_c(p_vr_id IN NUMBER) IS
SELECT
period_id
,line_item_id
FROM
pn_var_lines_all
WHERE
var_rent_id = p_vr_id
ORDER BY
period_id
,line_item_id;
/* get the line items with updated deductions for CYP, CYNP */
CURSOR lines_cs_c( p_vr_id IN NUMBER
,p_part_prd_id IN NUMBER
,p_full_prd_id IN NUMBER) IS
SELECT
period_id
,line_item_id
FROM
pn_var_lines_all
WHERE
var_rent_id = p_vr_id AND
period_id NOT IN (p_part_prd_id, p_full_prd_id)
ORDER BY
period_id
,line_item_id;
SELECT
period_id
,start_date
,end_date
,partial_period
FROM
pn_var_periods_all
WHERE
var_rent_id = p_vr_id
ORDER BY
start_date;
SELECT
hdr.trx_header_id
,SUM(hdr.prorated_group_deductions) OVER
(PARTITION BY
hdr.period_id
,hdr.line_item_id
,hdr.reset_group_id
ORDER BY
hdr.calc_prd_start_date
ROWS UNBOUNDED PRECEDING) AS ytd_deductions
FROM
pn_var_trx_headers_all hdr
WHERE
hdr.var_rent_id = p_vr_id AND
hdr.period_id = p_prd_ID AND
hdr.line_item_id = p_line_ID
ORDER BY
hdr.period_id
,hdr.line_item_id
,hdr.calc_prd_start_date;
SELECT
hdr.trx_header_id
,SUM(hdr.prorated_group_deductions) OVER
(PARTITION BY
hdr.line_item_group_id
ORDER BY
hdr.calc_prd_start_date
ROWS UNBOUNDED PRECEDING) AS ytd_deductions
FROM
pn_var_trx_headers_all hdr
WHERE
hdr.var_rent_id = p_vr_id AND
hdr.period_id IN (p_part_prd_id, p_full_prd_id)
ORDER BY
hdr.line_item_group_id
,hdr.calc_prd_start_date;
trx_hdr_t.DELETE;
ytd_deductions_t.DELETE;
UPDATE
pn_var_trx_headers_all
SET
ytd_deductions = ytd_deductions_t(i)
WHERE
trx_header_id = trx_hdr_t(i);
trx_hdr_t.DELETE;
ytd_deductions_t.DELETE;
UPDATE
pn_var_trx_headers_all
SET
ytd_deductions = ytd_deductions_t(i)
WHERE
trx_header_id = trx_hdr_t(i);
trx_hdr_t.DELETE;
ytd_deductions_t.DELETE;
UPDATE
pn_var_trx_headers_all
SET
ytd_deductions = ytd_deductions_t(i)
WHERE
trx_header_id = trx_hdr_t(i);
SELECT
vr.org_id
,vr.var_rent_id
,vr.commencement_date
,vr.termination_date
,vr.proration_rule
,vr.cumulative_vol
FROM
pn_var_rents_all vr
WHERE
vr.var_rent_id = p_vr_id;
SELECT
period_id
,line_item_id
FROM
pn_var_lines_all
WHERE
var_rent_id = p_vr_id
ORDER BY
period_id
,line_item_id;
/* get the calc periods to update deductions data */
CURSOR calc_periods_c( p_vr_id IN NUMBER
,p_prd_id IN NUMBER
,p_line_id IN NUMBER) IS
SELECT
hdr.trx_header_id
,hdr.var_rent_id
,hdr.period_id
,hdr.line_item_id
,hdr.grp_date_id
,hdr.calc_prd_start_date
,hdr.calc_prd_end_date
FROM
pn_var_trx_headers_all hdr
WHERE
hdr.var_rent_id = p_vr_id AND
hdr.period_id = p_prd_id AND
hdr.line_item_id = p_line_id
ORDER BY
hdr.period_id
,hdr.line_item_id
,hdr.calc_prd_start_date
,hdr.calc_prd_end_date;
l_line_items_t.DELETE;
trx_hdr_t.DELETE;
reporting_grp_dedc_t.DELETE;
prorate_grp_dedc_t.DELETE;
update the trx headers with the deductions */
IF trx_hdr_t.COUNT > 0 THEN
FORALL i IN trx_hdr_t.FIRST..trx_hdr_t.LAST
UPDATE
pn_var_trx_headers_all
SET
reporting_group_deductions = reporting_grp_dedc_t(i)
,prorated_group_deductions = prorate_grp_dedc_t(i)
WHERE
trx_header_id = trx_hdr_t(i);