[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT
commencement_date
FROM
pn_var_rents_all
WHERE
var_rent_id = p_vr_id;
SELECT
period_id
,start_date
,end_date
FROM
pn_var_periods_all
WHERE
var_rent_id = p_vr_id AND
start_date = p_st_dt AND
partial_period = 'Y';
SELECT
termination_date
FROM
pn_var_rents_all
WHERE
var_rent_id = p_vr_id;
SELECT
period_id
,start_date
,end_date
FROM
pn_var_periods_all
WHERE
var_rent_id = p_vr_id AND
end_date = p_end_dt AND
partial_period = 'Y';
SELECT
1
FROM dual
WHERE
EXISTS
(SELECT
vol_hist_id
FROM
pn_var_vol_hist_all
WHERE
line_item_id = p_line_id AND
grp_date_id = p_grp_id AND
vol_hist_status_code = pn_var_rent_calc_pkg.G_SALESVOL_STATUS_APPROVED);
SELECT NVL(SUM(percent_rent_due), 0) AS prev_billed_amt
FROM
pn_var_trx_headers_all
WHERE
var_rent_id = p_vr_id AND
period_id = p_prd_id AND
line_item_id = p_line_id AND
reset_group_id = p_rst_grp_id AND
calc_prd_start_date < p_date;
SELECT NVL(SUM(percent_rent_due), 0) AS prev_billed_amt
FROM
pn_var_trx_headers_all
WHERE
var_rent_id = p_vr_id AND
period_id IN (p_part_prd_id, p_full_prd_id) AND
line_item_group_id = p_line_grp_id AND
reset_group_id = p_rst_grp_id AND
calc_prd_start_date < p_date;
SELECT
period_id
FROM
pn_var_periods_all
WHERE
var_rent_id = p_vr_id
ORDER BY
start_date;
SELECT
bkhd.bkpt_header_id
,bkhd.breakpoint_type
FROM
pn_var_bkpts_head_all bkhd
,pn_var_bkpts_det_all bkdt
WHERE
bkdt.bkpt_detail_id = p_bkdt_id AND
bkdt.bkpt_header_id = bkhd.bkpt_header_id;
SELECT
NVL(SUM(fy_proration_sales - NVL(fy_proration_deductions,0) ),0) AS sales
FROM
pn_var_trx_headers_all
WHERE
var_rent_id = p_vr_id AND
line_item_group_id = p_line_item_grp_id AND
calc_prd_start_date <= (ADD_MONTHS(p_vr_comm_date, 12) - 1) ;
SELECT
NVL(SUM(fy_pr_grp_vol_start),0) AS bkpt_start
,NVL(SUM(fy_pr_grp_vol_end),0) AS bkpt_end
,bkpt_rate
FROM
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_vr_id AND
line_item_group_id = p_line_item_grp_id AND
calc_prd_start_date <= (ADD_MONTHS(p_vr_comm_date, 12) - 1))
GROUP BY
bkpt_rate
ORDER BY
bkpt_start;
SELECT
NVL(SUM(proration_factor),0) proration_factor_sum
FROM
pn_var_grp_dates_all
WHERE
period_id IN (SELECT
period_id
FROM
pn_var_periods_all
WHERE
var_rent_id = p_vr_id AND
start_date = p_vr_comm_date);
SELECT
NVL(SUM(ly_proration_sales - NVL(ly_proration_deductions,0) ),0) AS sales
FROM
pn_var_trx_headers_all
WHERE
var_rent_id = p_vr_id AND
line_item_group_id = p_line_item_grp_id AND
calc_prd_end_date >= (ADD_MONTHS(p_vr_term_date, -12) + 1);
SELECT
NVL(SUM(ly_pr_grp_vol_start),0) AS bkpt_start
,NVL(SUM(ly_pr_grp_vol_end),0) AS bkpt_end
,bkpt_rate
FROM
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_vr_id AND
line_item_group_id = p_line_item_grp_id AND
calc_prd_end_date >= (ADD_MONTHS(p_vr_term_date, -12) + 1))
GROUP BY
bkpt_rate
ORDER BY
bkpt_start;
SELECT
NVL(SUM(proration_factor),0) proration_factor_sum
FROM
pn_var_grp_dates_all
WHERE
period_id IN (SELECT
period_id
FROM
pn_var_periods_all
WHERE
var_rent_id = p_vr_id AND
end_date = p_vr_term_date);
SELECT
reptg_freq_code AS report_freq_code
FROM
pn_var_rent_dates_all
WHERE
var_rent_id = p_vr_id;
SELECT
count(period_id) perion_num
FROM
pn_var_periods_all
WHERE
var_rent_id = p_vr_id AND
NVL(status, 'A') <> pn_var_rent_calc_pkg.G_PERIOD_REVERSED_STATUS;
SELECT
prd.period_id
,prd.partial_period
FROM
pn_var_periods_all prd,
pn_var_rents_all var
WHERE
prd.var_rent_id = p_vr_id AND
prd.var_rent_id = var.var_rent_id AND
prd.end_date = var.termination_date;
SELECT
prd.period_id
,prd.partial_period
FROM
pn_var_periods_all prd,
pn_var_rents_all var
WHERE
prd.var_rent_id = p_vr_id AND
prd.var_rent_id = var.var_rent_id AND
prd.start_date = var.commencement_date;
SELECT var_rent_id
FROM pn_var_rent_dates_all
WHERE reptg_freq_code = 'YR'
AND invg_freq_code = 'YR'
AND var_rent_id = p_vr_id;
l_bkpts_t.DELETE;
- update l_trx_hdr_rec.percent_rent_due
- then get the YTD
*/
l_trx_hdr_rec.ytd_percent_rent
:= l_prev_billed + l_trx_hdr_rec.percent_rent_due;
l_trx_hdr_rec.update_flag := 'Y';
SELECT
grp.grp_date_id
,grp.group_date
,grp.invoice_date
,grp.org_id
FROM
pn_var_grp_dates_all grp
WHERE
grp.var_rent_id = p_vr_id AND
grp.period_id = p_prd_id AND
grp.grp_date_id = p_grp_id;
SELECT
NVL(SUM(hdr.percent_rent_due),0)
+ NVL(SUM(DECODE(hdr.invoice_flag
,'I',hdr.prorated_rent_due
,0
)
), 0) AS rent
,NVL(SUM(hdr.prorated_group_sales)
, 0) AS sales
,NVL(SUM(hdr.prorated_group_deductions)
, 0) AS ded
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 AND
hdr.grp_date_id = p_grp_id;
SELECT
var_rent_summ_id
FROM
pn_var_rent_summ_all
WHERE
var_rent_id = p_vr_id AND
period_id = p_prd_id AND
line_item_id = p_line_id AND
grp_date_id = p_grp_id;
pnp_debug_pkg.log('inserting ...');
/* to insert a new summary record */
INSERT INTO
pn_var_rent_summ_all
(var_rent_summ_id
,var_rent_id
,period_id
,line_item_id
,invoice_date
,tot_act_vol
,tot_ded
,act_var_rent
,grp_date_id
,group_date
,last_update_date
,last_updated_by
,creation_date
,created_by
,last_update_login
,org_id)
VALUES
(pn_var_rent_summ_s.NEXTVAL
,p_var_rent_id
,p_period_id
,p_line_item_id
,grp_rec.invoice_date
,summ_rec.sales
,summ_rec.ded
,round(summ_rec.rent,g_precision)
,grp_rec.grp_date_id
,grp_rec.group_date
,SYSDATE
,NVL(fnd_global.user_id, 0)
,SYSDATE
,NVL(fnd_global.user_id, 0)
,NVL(fnd_global.login_id, 0)
,NVL(grp_rec.org_id, g_org_id))
RETURNING
var_rent_summ_id
INTO
l_vr_summ_id;
/* update the summary record */
pnp_debug_pkg.log('updating ...');
UPDATE
pn_var_rent_summ_all
SET
tot_act_vol = summ_rec.sales
,tot_ded = summ_rec.ded
,act_var_rent = round(summ_rec.rent,g_precision)
,last_update_date = SYSDATE
,last_updated_by = NVL(fnd_global.user_id, 0)
,last_update_login = NVL(fnd_global.login_id, 0)
WHERE
var_rent_summ_id = l_vr_summ_id;
UPDATE
pn_var_trx_headers_all hdr
SET
hdr.var_rent_summ_id = l_vr_summ_id
WHERE
hdr.var_rent_id = p_var_rent_id AND
hdr.period_id = p_period_id AND
hdr.line_item_id = p_line_item_id AND
hdr.grp_date_id = p_grp_date_id;
SELECT *
FROM
pn_var_rent_summ_all
WHERE
var_rent_id = p_var_rent_id AND
NVL(first_yr_rent, 0) <> 0;
SELECT var_rent_summ_id
,tot_act_vol
,act_var_rent
,tot_ded
FROM
pn_var_rent_summ_all
WHERE
var_rent_id = p_var_rent_id AND
period_id = p_period_id AND
line_item_id = p_line_item_id;
SELECT trx.line_item_id
FROM pn_var_trx_headers_all trx
WHERE trx.period_id = p_period_id
AND trx.line_item_group_id IN (SELECT line_item_group_id
FROM pn_var_trx_headers_all
WHERE line_item_id = p_line_item_id);
SELECT period_id
FROM pn_var_periods_all
WHERE var_rent_id = p_var_rent_id
AND period_num = 1;
UPDATE
pn_var_rent_summ_all
SET
tot_act_vol = vr_summ_fy_rec.tot_act_vol
,tot_ded = vr_summ_fy_rec.tot_ded
,act_var_rent = round(vr_summ_fy_rec.first_yr_rent,g_precision)
,last_update_date = SYSDATE
,last_updated_by = NVL(fnd_global.user_id, 0)
,last_update_login = NVL(fnd_global.login_id, 0)
WHERE
var_rent_summ_id = l_vr_summ_id;
/* to insert a new summary record */
INSERT INTO
pn_var_rent_summ_all
(var_rent_summ_id
,var_rent_id
,period_id
,line_item_id
,invoice_date
,tot_act_vol
,tot_ded
,act_var_rent
,grp_date_id
,group_date
,last_update_date
,last_updated_by
,creation_date
,created_by
,last_update_login
,org_id
,first_yr_rent)
VALUES
(pn_var_rent_summ_s.NEXTVAL
,p_var_rent_id
,l_period_id
,l_line_item_id
,vr_summ_fy_rec.invoice_date
,vr_summ_fy_rec.tot_act_vol
,vr_summ_fy_rec.tot_ded
,round(vr_summ_fy_rec.first_yr_rent,g_precision)
,vr_summ_fy_rec.grp_date_id
,vr_summ_fy_rec.group_date
,SYSDATE
,NVL(fnd_global.user_id, 0)
,SYSDATE
,NVL(fnd_global.user_id, 0)
,NVL(fnd_global.login_id, 0)
,vr_summ_fy_rec.org_id
,NULL)
RETURNING
var_rent_summ_id
INTO
l_vr_summ_id;
UPDATE
pn_var_trx_headers_all hdr
SET
hdr.var_rent_summ_id = l_vr_summ_id
WHERE
hdr.var_rent_id = p_var_rent_id AND
hdr.period_id = l_period_id AND
hdr.line_item_id = l_line_item_id;
PROCEDURE insert_invoice_firstyr( p_var_rent_id IN NUMBER) IS
/* get invoice dates for a period */
CURSOR invoice_dates_c( p_vr_id IN NUMBER) IS
SELECT
invoice_date
,NVL(SUM(first_yr_rent), 0) AS total_actual_rent
FROM
pn_var_rent_summ_all
WHERE
var_rent_id = p_vr_id AND
first_yr_rent IS NOT NULL
GROUP BY
invoice_date
ORDER BY
invoice_date;
SELECT
var_rent_inv_id
,var_rent_id
,period_id
,invoice_date
,actual_term_status
,actual_exp_code
,adjust_num
,tot_act_vol
,act_per_rent
,actual_invoiced_amount
,constr_actual_rent
,rec_abatement_override
FROM
pn_var_rent_inv_all
WHERE
var_rent_id = p_vr_id AND
period_id = p_prd_id AND
invoice_date = p_inv_dt AND
NVL(true_up_amt, 0) = 0 AND
true_up_status IS NULL AND
true_up_exp_code IS NULL
ORDER BY adjust_num DESC;
SELECT
NVL(SUM(actual_invoiced_amount), 0) AS prev_invoiced_amt
FROM
pn_var_rent_inv_all
WHERE
var_rent_id = p_vr_id AND
period_id = p_prd_id AND
invoice_date = p_inv_dt AND
actual_exp_code = 'Y' AND
NVL(true_up_amt, 0) = 0 AND
true_up_status IS NULL AND
true_up_exp_code IS NULL;
SELECT org_id, commencement_date
FROM pn_var_rents_all
WHERE var_rent_id = p_vr_id;
SELECT
NVL(SUM(fy_proration_sales - NVL(fy_proration_deductions,0) ),0) AS sales
FROM
pn_var_trx_headers_all
WHERE
var_rent_id = p_vr_id AND
calc_prd_start_date <= (ADD_MONTHS(p_vr_comm_date, 12) - 1) ;
SELECT period_id
FROM pn_var_periods_all
WHERE var_rent_id = p_vr_id
AND period_num = 1;
pnp_debug_pkg.log('+++++++++ insert_invoice_firstyr START +++++++++++');
/* invoice updateable? */
IF NVL(inv2upd_rec.actual_exp_code, 'N') <> 'Y' THEN
/* updateable */
l_var_rent_inv_id := inv2upd_rec.var_rent_inv_id;
/* NON - updateable */
l_var_rent_inv_id := NULL;
END IF; /* invoice updateable? */
pnp_debug_pkg.log('inserting the row ...');
pn_var_rent_inv_pkg.insert_row
( x_rowid => l_row_id,
x_var_rent_inv_id => l_var_rent_inv_id,
x_adjust_num => l_max_adjust_num,
x_invoice_date => inv_rec.invoice_date,
x_for_per_rent => NULL,
x_tot_act_vol => l_tot_act_vol,
x_act_per_rent => inv_rec.total_actual_rent,
x_constr_actual_rent => l_constr_rent,
x_abatement_appl => 0,
x_rec_abatement => NULL,
x_rec_abatement_override => l_rec_abatement_override,
x_negative_rent => 0,
x_actual_invoiced_amount => l_constr_rent,
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 => 'created invoice',
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_global.user_id,0),
x_last_update_date => SYSDATE,
x_last_updated_by => NVL(fnd_global.user_id,0),
x_last_update_login => NVL(fnd_global.login_id,0),
x_org_id => l_org_id );
/* no invoice to update - create a new one */
IF l_var_rent_inv_id IS NULL THEN
pnp_debug_pkg.log('l_constr_rent:'||l_constr_rent);
pn_var_rent_inv_pkg.insert_row
( x_rowid => l_row_id,
x_var_rent_inv_id => l_var_rent_inv_id,
x_adjust_num => l_max_adjust_num,
x_invoice_date => inv_rec.invoice_date,
x_for_per_rent => NULL,
x_tot_act_vol => l_tot_act_vol,
x_act_per_rent => inv_rec.total_actual_rent,
x_constr_actual_rent => l_constr_rent,
x_abatement_appl => 0,
x_rec_abatement => NULL,
x_rec_abatement_override => l_rec_abatement_override,
x_negative_rent => 0,
x_actual_invoiced_amount => (l_constr_rent - l_prev_inv_exp_rent),
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 => 'created invoice',
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_global.user_id,0),
x_last_update_date => SYSDATE,
x_last_updated_by => NVL(fnd_global.user_id,0),
x_last_update_login => NVL(fnd_global.login_id,0),
x_org_id => l_org_id );
DELETE
pn_payment_terms_all
WHERE
var_rent_inv_id = l_var_rent_inv_id AND
status <> pn_var_rent_calc_pkg.G_TERM_STATUS_APPROVED AND
var_rent_type = pn_var_rent_calc_pkg.G_INV_ON_ACTUAL;
/* update the invoice */
UPDATE
pn_var_rent_inv_all
SET
act_per_rent = ROUND(inv_rec.total_actual_rent, g_precision)
,constr_actual_rent = ROUND(l_constr_rent, g_precision)
,actual_invoiced_amount = ROUND((l_constr_rent - l_prev_inv_exp_rent), g_precision)
,tot_act_vol = ROUND(l_tot_act_vol,g_precision) -- bug # 6007571
,credit_flag = 'N' -- bug # 5937807
,actual_term_status = 'N'
,last_update_date = SYSDATE
,last_updated_by = NVL(fnd_global.user_id,0)
,last_update_login = NVL(fnd_global.login_id,0)
WHERE
var_rent_inv_id = l_var_rent_inv_id;
pnp_debug_pkg.log('+++++++++ insert_invoice END +++++++++++');
END insert_invoice_firstyr;
PROCEDURE insert_invoice( p_var_rent_id IN NUMBER
,p_period_id IN NUMBER) IS
/* get invoice dates for a period */
CURSOR invoice_dates_c( p_vr_id IN NUMBER
,p_prd_id IN NUMBER
,p_new_termn_date DATE) IS
SELECT
invoice_date
,NVL(SUM(tot_act_vol), 0) AS total_actual_sales
,NVL((SUM(act_var_rent) - NVL(SUM(first_yr_rent), 0)), 0) AS total_actual_rent
FROM
pn_var_rent_summ_all
WHERE
var_rent_id = p_vr_id AND
period_id = p_prd_id AND
invoice_date <= p_new_termn_date
GROUP BY
invoice_date
ORDER BY
invoice_date;
SELECT
var_rent_inv_id
,var_rent_id
,period_id
,invoice_date
,actual_term_status
,actual_exp_code
,adjust_num
,tot_act_vol
,act_per_rent
,actual_invoiced_amount
,constr_actual_rent
,rec_abatement_override
FROM
pn_var_rent_inv_all
WHERE
var_rent_id = p_vr_id AND
period_id = p_prd_id AND
invoice_date = p_inv_dt AND
NVL(true_up_amt, 0) = 0 AND
true_up_status IS NULL AND
true_up_exp_code IS NULL
ORDER BY adjust_num DESC;
SELECT
NVL(SUM(actual_invoiced_amount), 0) AS prev_invoiced_amt
FROM
pn_var_rent_inv_all
WHERE
var_rent_id = p_vr_id AND
period_id = p_prd_id AND
invoice_date = p_inv_dt AND
actual_exp_code = 'Y' AND
NVL(true_up_amt, 0) = 0 AND
true_up_status IS NULL AND
true_up_exp_code IS NULL;
SELECT 1 as vol_exists
FROM DUAL
WHERE exists(
SELECT vol_hist_id
FROM pn_var_vol_hist_all
WHERE grp_date_id in(
SELECT grp_date_id
FROM pn_var_grp_dates_all
WHERE var_rent_id = ip_var_rent_id
AND invoice_date = ip_invoice_date)
AND vol_hist_status_code = pn_var_rent_calc_pkg.G_SALESVOL_STATUS_APPROVED);
SELECT org_id, termination_date
FROM pn_var_rents_all
WHERE var_rent_id = p_vr_id;
SELECT
NVL(SUM(ly_proration_sales - NVL(ly_proration_deductions,0) ),0) AS sales
FROM
pn_var_trx_headers_all
WHERE
var_rent_id = p_vr_id AND
calc_prd_end_date >= (ADD_MONTHS(p_vr_term_date, -12) + 1);
SELECT period_id
FROM pn_var_periods_all
WHERE var_rent_id = p_vr_id
AND end_date = p_termination_date
AND partial_period='Y'; -- bug # 5937807
pnp_debug_pkg.log('+++++++++ insert_invoice START (+) +++++++++++');
/* invoice updateable? */
IF NVL(inv2upd_rec.actual_exp_code, 'N') <> 'Y' THEN
/* updateable */
l_var_rent_inv_id := inv2upd_rec.var_rent_inv_id;
/* NON - updateable */
l_var_rent_inv_id := NULL;
END IF; /* invoice updateable? */
invoice date. However once we have create invoices, and you delete volumes
for that we need to update/adfjust them.
*/
-- check if vol exists. This can't loop more than once.
FOR vol_exists_rec IN vol_exists_c( p_var_rent_id,
inv_rec.invoice_date
) --
LOOP
l_constr_rent := pn_var_rent_calc_pkg.apply_constraints(
p_period_id => p_period_id,
p_invoice_date => inv_rec.invoice_date,
p_actual_rent => inv_rec.total_actual_rent);
pn_var_rent_inv_pkg.insert_row
( x_rowid => l_row_id,
x_var_rent_inv_id => l_var_rent_inv_id,
x_adjust_num => l_max_adjust_num,
x_invoice_date => inv_rec.invoice_date,
x_for_per_rent => NULL,
x_tot_act_vol => l_tot_act_vol,
x_act_per_rent => inv_rec.total_actual_rent,
x_constr_actual_rent => l_constr_rent,
x_abatement_appl => 0,
x_rec_abatement => NULL,
x_rec_abatement_override => l_rec_abatement_override,
x_negative_rent => 0,
x_actual_invoiced_amount => l_constr_rent,
x_period_id => p_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 => 'created invoice',
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_global.user_id,0),
x_last_update_date => SYSDATE,
x_last_updated_by => NVL(fnd_global.user_id,0),
x_last_update_login => NVL(fnd_global.login_id,0),
x_org_id => l_org_id );
/* no invoice to update - create a new one */
IF l_var_rent_inv_id IS NULL THEN
pnp_debug_pkg.log('l_constr_rent:'||l_constr_rent);
pn_var_rent_inv_pkg.insert_row
( x_rowid => l_row_id,
x_var_rent_inv_id => l_var_rent_inv_id,
x_adjust_num => l_max_adjust_num,
x_invoice_date => inv_rec.invoice_date,
x_for_per_rent => NULL,
x_tot_act_vol => l_tot_act_vol,
x_act_per_rent => inv_rec.total_actual_rent,
x_constr_actual_rent => l_constr_rent,
x_abatement_appl => 0,
x_rec_abatement => NULL,
x_rec_abatement_override => l_rec_abatement_override,
x_negative_rent => 0,
x_actual_invoiced_amount => (l_constr_rent - l_prev_inv_exp_rent),
x_period_id => p_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 => 'created invoice',
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_global.user_id,0),
x_last_update_date => SYSDATE,
x_last_updated_by => NVL(fnd_global.user_id,0),
x_last_update_login => NVL(fnd_global.login_id,0),
x_org_id => l_org_id );
DELETE
pn_payment_terms_all
WHERE
var_rent_inv_id = l_var_rent_inv_id AND
status <> pn_var_rent_calc_pkg.G_TERM_STATUS_APPROVED AND
var_rent_type = pn_var_rent_calc_pkg.G_INV_ON_ACTUAL;
/* update the invoice */
UPDATE
pn_var_rent_inv_all
SET
act_per_rent = ROUND(inv_rec.total_actual_rent, g_precision)
,constr_actual_rent = ROUND(l_constr_rent, g_precision)
,actual_invoiced_amount = ROUND((l_constr_rent - l_prev_inv_exp_rent), g_precision)
,tot_act_vol = ROUND(l_tot_act_vol, g_precision) -- bug # 6007571
,credit_flag = 'N' -- bug # 5937807
,actual_term_status = 'N'
,last_update_date = SYSDATE
,last_updated_by = NVL(fnd_global.user_id,0)
,last_update_login = NVL(fnd_global.login_id,0)
WHERE
var_rent_inv_id = l_var_rent_inv_id;
pnp_debug_pkg.log('+++++++++ insert_invoice END +++++++++++');
END insert_invoice;
SELECT TYPE_code,
amount,
cons.constr_start_date const,
grp.inv_start_date grpst
FROM pn_var_constraints_all cons,
pn_var_grp_dates_all grp
WHERE cons.period_id = p_period_id
AND cons.period_id = grp.period_id
AND cons.constr_start_date <= grp.inv_start_date
AND cons.constr_end_date >= grp.inv_end_date
AND grp.invoice_date = p_invoice_date
AND NVL(constr_cat_code, 'VARENT')= 'VARENT';
SELECT TYPE_code,
amount,
cons.constr_start_date const
FROM pn_var_periods_all per, pn_var_constraints_all cons
WHERE per.var_rent_id = (select var_rent_id from pn_var_periods_all where period_id=p_period_id)
AND per.period_id = cons.period_id
AND cons.constr_start_date=(select
MAX(const1.constr_start_date) from pn_var_constraints_all const1
where const1.period_id = cons.period_id)
AND period_num = 1;
SELECT ORDER_OF_APPL_CODE, invoice_on, termination_date
FROM PN_VAR_RENTS_ALL abat
WHERE abat.var_rent_id = ip_var_rent_id;
SELECT var_rent_inv_id, invoice_date, variance_exp_code,
actual_exp_code, constr_actual_rent
FROM pn_var_rent_inv_all inv1
WHERE inv1.var_rent_id = ip_var_rent_id
AND inv1.period_id = ip_period_id
AND inv1.invoice_date <= p_new_termn_date
AND inv1.adjust_num= (
SELECT MAX(adjust_num) from pn_var_rent_inv_all inv2
where inv1.var_rent_id = inv2.var_rent_id
AND inv1.invoice_date = inv2.invoice_date)
ORDER BY invoice_date;
SELECT *
FROM pn_var_rent_inv_all
WHERE var_rent_inv_id = ip_vr_inv_id;
SELECT
NVL(SUM(actual_invoiced_amount), 0) AS prev_invoiced_amt
FROM
pn_var_rent_inv_all
WHERE
var_rent_id = p_vr_id AND
period_id = p_prd_id AND
invoice_date = p_inv_dt AND
(actual_exp_code = 'Y'
OR variance_exp_code = 'Y')--Both can not be Y at the same time
AND
NVL(true_up_amt, 0) = 0 AND
true_up_status IS NULL AND
true_up_exp_code IS NULL;
SELECT DECODE(invoice_on,
G_INV_ON_ACTUAL, actual_exp_code,
G_INV_ON_FORECASTED, variance_exp_code) AS
exp_code,
inv.actual_invoiced_amount
FROM pn_var_rents_all vr,
pn_var_rent_inv_all inv
WHERE vr.var_rent_id = inv.var_rent_id
AND inv.var_rent_inv_id = ip_var_rent_inv_id;
/* update the invoice */
IF (p_flag = 'CALCULATE') THEN
FOR exp_rec IN is_act_or_rec_exp_c(inv_rec.var_rent_inv_id) LOOP
l_is_inv_exp := exp_rec.exp_code;
DELETE
pn_payment_terms_all
WHERE
var_rent_inv_id = inv_rec.var_rent_inv_id AND
status <> pn_var_rent_calc_pkg.G_TERM_STATUS_APPROVED AND
var_rent_type = pn_var_rent_calc_pkg.G_INV_ON_ACTUAL;
UPDATE
pn_var_rent_inv_all
SET
actual_invoiced_amount = ROUND((l_abated_rent - l_prev_inv_exp), g_precision)
,actual_term_status = 'N'
,last_update_date = SYSDATE
,last_updated_by = NVL(fnd_global.user_id,0)
,last_update_login = NVL(fnd_global.login_id,0)
WHERE
var_rent_inv_id = inv_rec.var_rent_inv_id;
pnp_debug_pkg.log('Abatements - inserting');
pn_var_rent_inv_pkg.insert_row
( x_rowid => l_row_id,
x_var_rent_inv_id => l_var_rent_inv_id,
x_adjust_num => inv_all_rec.adjust_num+1,
x_invoice_date => inv_all_rec.invoice_date,
x_for_per_rent => inv_all_rec.for_per_rent,
x_tot_act_vol => inv_all_rec.tot_act_vol,
x_act_per_rent => inv_all_rec.act_per_rent,
x_constr_actual_rent => inv_all_rec.constr_actual_rent,
x_abatement_appl => inv_all_rec.abatement_appl,
x_rec_abatement => inv_all_rec.rec_abatement,
x_rec_abatement_override => inv_all_rec.rec_abatement_override,
x_negative_rent => inv_all_rec.negative_rent,
x_actual_invoiced_amount => (l_abated_rent - l_prev_inv_exp),
x_period_id => inv_all_rec.period_id,
x_var_rent_id => inv_all_rec.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 => 'created invoice',
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_global.user_id,0),
x_last_update_date => SYSDATE,
x_last_updated_by => NVL(fnd_global.user_id,0),
x_last_update_login => NVL(fnd_global.login_id,0),
x_org_id => inv_all_rec.org_id );
SELECT constr_actual_rent
,actual_invoiced_amount
,negative_rent
,invoice_date
,var_rent_inv_id
FROM pn_var_rent_inv_all inv1
WHERE inv1.var_rent_id = ip_var_rent_id
AND inv1.period_id = ip_period_id
AND var_rent_inv_id = ip_inv_id
AND inv1.adjust_num= (
SELECT MAX(adjust_num) from pn_var_rent_inv_all inv2
where inv1.var_rent_id = inv2.var_rent_id
AND inv1.invoice_date = inv2.invoice_date);
SELECT MAX(invoice_date) inv_date
FROM pn_var_grp_dates_all
WHERE var_rent_id = p_var_rent_id;
SELECT ABS(NVL(SUM(constr_actual_rent),0)) negative_available
FROM pn_var_rent_inv_all inv
WHERE inv.var_rent_id = ip_var_rent_id
AND inv.invoice_date < ip_invoice_date
AND inv.adjust_num = (select MAX(inv1.adjust_num)
from pn_var_rent_inv_all inv1
where inv1.var_rent_id = inv.var_rent_id
AND inv1.invoice_date = inv.invoice_date)
AND inv.constr_actual_rent < 0;
SELECT NVL(SUM(negative_rent),0) negative_applied
FROM pn_var_rent_inv_all inv
WHERE inv.var_rent_id = ip_var_rent_id
AND inv.invoice_date < ip_invoice_date
AND inv.adjust_num = (select MAX(inv1.adjust_num)
from pn_var_rent_inv_all inv1
where inv1.var_rent_id = inv.var_rent_id
AND inv1.invoice_date = inv.invoice_date);
SELECT negative_rent
FROM pn_var_rents_all
WHERE var_rent_id = ip_var_rent_id;
SELECT abated_rent,
negative_rent
FROM pn_var_rent_inv_all
WHERE var_rent_inv_id = ip_var_rent_inv_id;
UPDATE pn_var_rent_inv_all
SET abated_rent = l_abated_rent,
negative_rent = L_negative_rent
WHERE var_rent_inv_id = inv_rec.var_rent_inv_id;
SELECT constr_actual_rent
,actual_invoiced_amount
,negative_rent
,invoice_date
,var_rent_inv_id
FROM pn_var_rent_inv_all inv1
WHERE inv1.var_rent_id = ip_var_rent_id
AND inv1.period_id = ip_period_id
AND var_rent_inv_id = ip_inv_id
AND inv1.adjust_num= (
SELECT MAX(adjust_num) from pn_var_rent_inv_all inv2
where inv1.var_rent_id = inv2.var_rent_id
AND inv1.invoice_date = inv2.invoice_date);
SELECT MAX(invoice_date) inv_date
FROM pn_var_grp_dates_all
WHERE var_rent_id = p_var_rent_id
AND period_id = (SELECT max(period_id)
FROM pn_var_periods_all
WHERE var_rent_id = p_var_rent_id
AND NVL(status, pn_var_rent_calc_pkg.G_PERIOD_ACTIVE_STATUS)
<> pn_var_rent_calc_pkg.G_PERIOD_REVERSED_STATUS);
SELECT ABS(NVL(SUM(def_neg_rent),0)) negative_available
FROM pn_var_rent_inv_all inv
WHERE inv.var_rent_id = ip_var_rent_id
AND inv.invoice_date < ip_invoice_date
AND inv.adjust_num = (select MAX(inv1.adjust_num)
from pn_var_rent_inv_all inv1
where inv1.var_rent_id = inv.var_rent_id
AND inv1.invoice_date = inv.invoice_date);
SELECT NVL(SUM(negative_rent),0) negative_applied
FROM pn_var_rent_inv_all inv
WHERE inv.var_rent_id = ip_var_rent_id
AND inv.invoice_date < ip_invoice_date
AND inv.adjust_num = (select MAX(inv1.adjust_num)
from pn_var_rent_inv_all inv1
where inv1.var_rent_id = inv.var_rent_id
AND inv1.invoice_date = inv.invoice_date);
SELECT negative_rent
FROM pn_var_rents_all
WHERE var_rent_id = ip_var_rent_id;
UPDATE pn_var_rent_inv_all
SET negative_rent = ROUND(l_negative_rent, g_precision)
WHERE var_rent_inv_id = inv_rec.var_rent_inv_id;
SELECT invoice_date
,var_rent_inv_id
FROM pn_var_rent_inv_all inv1
WHERE inv1.var_rent_id = ip_var_rent_id
AND inv1.period_id = ip_period_id
AND var_rent_inv_id = ip_inv_id
AND inv1.adjust_num= (
SELECT MAX(adjust_num) from pn_var_rent_inv_all inv2
where inv1.var_rent_id = inv2.var_rent_id
AND inv1.invoice_date = inv2.invoice_date);
SELECT MAX(invoice_date) inv_date
FROM pn_var_grp_dates_all
WHERE var_rent_id = p_var_rent_id
AND period_id = (SELECT max(period_id)
FROM pn_var_periods_all
WHERE var_rent_id = p_var_rent_id
AND NVL(status, pn_var_rent_calc_pkg.G_PERIOD_ACTIVE_STATUS)
<> pn_var_rent_calc_pkg.G_PERIOD_REVERSED_STATUS);
SELECT negative_rent
FROM pn_var_rents_all
WHERE var_rent_id = ip_var_rent_id;
UPDATE pn_var_rent_inv_all
SET def_neg_rent = ROUND(l_neg_rent_def, g_precision)
WHERE var_rent_inv_id = inv_rec.var_rent_inv_id;
SELECT actual_invoiced_amount
,invoice_date
,NVL(rec_abatement,0) rec_abatement
,rec_abatement_override
,var_rent_inv_id
FROM pn_var_rent_inv_all inv1
WHERE inv1.var_rent_id = ip_var_rent_id
AND inv1.period_id = ip_period_id
AND inv1.var_rent_inv_id = ip_inv_id
AND inv1.adjust_num= (
SELECT MAX(adjust_num) from pn_var_rent_inv_all inv2
where inv1.var_rent_id = inv2.var_rent_id
AND inv1.invoice_date = inv2.invoice_date);
SELECT NVL(SUM(amount),0) fixed_abat
FROM PN_VAR_ABAT_DEFAULTS_ALL
WHERE var_rent_id = ip_var_rent_id
AND start_date <= ip_inv_end_date
AND NVL(end_date, ip_inv_end_date) >= ip_inv_start_date
AND type_code = pn_var_rent_calc_pkg.G_ABAT_TYPE_CODE_ABAT;
SELECT EXCESS_ABAT_CODE
FROM PN_VAR_RENTS_ALL ABAT
WHERE abat.var_rent_id = ip_var_rent_id;
SELECT inv_start_date, inv_end_date
FROM pn_var_grp_dates_all
WHERE var_rent_id = ip_var_rent_id
AND invoice_date = ip_invoice_date;
SELECT per.start_date, per.end_date
FROM pn_var_rent_inv_all inv, pn_var_periods_all per
WHERE per.period_id = inv.period_id
AND inv.var_rent_inv_id = ip_var_rent_inv_id;
SELECT inv.var_rent_inv_id
FROM pn_var_rent_inv_all inv, pn_var_periods_all per
WHERE per.period_id = inv.period_id
AND inv.var_rent_id = ip_var_rent_id
AND per.start_date = (SELECT MIN(start_date) from pn_var_periods_all
WHERE var_rent_id = ip_var_rent_id);
SELECT inv.var_rent_inv_id
FROM pn_var_rent_inv_all inv, pn_var_periods_all per
WHERE per.period_id = inv.period_id
AND inv.var_rent_id = ip_var_rent_id
AND per.start_date = (SELECT MAX(start_date) from pn_var_periods_all
WHERE var_rent_id = ip_var_rent_id)
AND inv.invoice_date = (SELECT MAX(invoice_date) FROM pn_var_rent_inv_all
WHERE var_rent_id = ip_var_rent_id);
SELECT negative_rent
FROM pn_var_rents_all
WHERE var_rent_id = ip_var_rent_id;
SELECT proration_rule
FROM pn_var_rents_all
WHERE var_rent_id = ip_var_rent_id;
SELECT ceil(months_between(per.end_date, per.start_date)/decode(vrd.invg_freq_code,'YR', 12, 'SA', 6, 'QTR', 3, 'MON', 1, 1)) num_inv
FROM pn_var_rent_inv_all inv, pn_var_periods_all per, pn_var_rents_all vr, pn_var_rent_dates_all vrd
WHERE per.period_id = inv.period_id
AND inv.var_rent_inv_id = ip_var_rent_inv_id
AND per.var_rent_id = vr.var_rent_id
AND vrd.var_rent_id = vr.var_rent_id;
SELECT inv.invoice_date,
inv.var_rent_inv_id,
inv.period_id
FROM pn_var_rents_all vr,
pn_var_periods_all per,
pn_var_rent_inv_all inv
WHERE per.var_rent_id = vr.var_rent_id
AND inv.period_id = per.period_id
AND per.start_date = (select max(start_date) from pn_var_periods_all per1
where per1.var_rent_id = vr.var_rent_id)
AND inv.invoice_date = (select max(invoice_date) from pn_var_rent_inv_all inv1
where inv1.period_id = per.period_id)
AND vr.var_rent_id = ip_vr_id;
UPDATE pn_var_rent_inv_all
SET rec_abatement = ROUND(l_total_abat, g_precision)
WHERE var_rent_inv_id = inv_rec.var_rent_inv_id;
SELECT inv1.abatement_appl
,inv1.invoice_date
,inv1.var_rent_inv_id
FROM pn_var_rent_inv_all inv1
WHERE var_rent_id = ip_var_rent_id
AND period_id = ip_period_id
AND var_rent_inv_id = ip_inv_id
AND inv1.adjust_num= (
SELECT MAX(adjust_num) from pn_var_rent_inv_all inv2
where inv1.var_rent_id = inv2.var_rent_id
AND inv1.invoice_date = inv2.invoice_date);
SELECT NVL(amount, 0) rolling_allow
,allowance_applied allow_applied
,abatement_id
FROM PN_VAR_ABAT_DEFAULTS_ALL
WHERE var_rent_id = ip_var_rent_id
AND start_date <= ip_inv_end_date
AND NVL(end_date, ip_inv_end_date) >= ip_inv_start_date
AND type_code = pn_var_rent_calc_pkg.G_ABAT_TYPE_CODE_ALLO
ORDER BY start_date;
SELECT inv_start_date, inv_end_date
FROM pn_var_grp_dates_all
WHERE var_rent_id = ip_var_rent_id
AND invoice_date = ip_invoice_date;
SELECT per.start_date, per.end_date
FROM pn_var_rent_inv_all inv, pn_var_periods_all per
WHERE per.period_id = inv.period_id
AND inv.var_rent_inv_id = ip_var_rent_inv_id;
SELECT inv.var_rent_inv_id
FROM pn_var_rent_inv_all inv, pn_var_periods_all per
WHERE per.period_id = inv.period_id
AND inv.var_rent_id = ip_var_rent_id
AND per.start_date = (SELECT MIN(start_date) from pn_var_periods_all
WHERE var_rent_id = ip_var_rent_id);
SELECT inv.var_rent_inv_id
FROM pn_var_rent_inv_all inv, pn_var_periods_all per
WHERE per.period_id = inv.period_id
AND inv.var_rent_id = ip_var_rent_id
AND per.start_date = (SELECT MAX(start_date) from pn_var_periods_all
WHERE var_rent_id = ip_var_rent_id)
AND inv.invoice_date = (SELECT MAX(invoice_date) from pn_var_rent_inv_all
WHERE var_rent_id = ip_var_rent_id);
SELECT proration_rule
FROM pn_var_rents_all
WHERE var_rent_id = ip_var_rent_id;
UPDATE pn_var_abat_defaults_all
SET allowance_applied = NVL(allowance_applied,0)+l_allow_applied
WHERE abatement_id = allow_rec.abatement_id;
UPDATE pn_var_rent_inv_all
SET abatement_appl = ROUND(l_allow_applied_inv, g_precision)
WHERE var_rent_inv_id = inv_rec.var_rent_inv_id;
SELECT invoice_date
,var_rent_inv_id
FROM pn_var_rent_inv_all inv1
WHERE inv1.var_rent_id = ip_var_rent_id
AND inv1.period_id = ip_period_id
AND var_rent_inv_id = ip_inv_id
AND inv1.adjust_num= (
SELECT MAX(adjust_num) from pn_var_rent_inv_all inv2
where inv1.var_rent_id = inv2.var_rent_id
AND inv1.invoice_date = inv2.invoice_date);
SELECT per.start_date, per.end_date
FROM pn_var_rent_inv_all inv, pn_var_periods_all per
WHERE per.period_id = inv.period_id
AND inv.var_rent_inv_id = ip_var_rent_inv_id;
SELECT inv.var_rent_inv_id
FROM pn_var_rent_inv_all inv, pn_var_periods_all per
WHERE per.period_id = inv.period_id
AND inv.var_rent_id = ip_var_rent_id
AND per.start_date = (SELECT MIN(start_date) from pn_var_periods_all
WHERE var_rent_id = ip_var_rent_id);
SELECT inv.var_rent_inv_id
FROM pn_var_rent_inv_all inv, pn_var_periods_all per
WHERE per.period_id = inv.period_id
AND inv.var_rent_id = ip_var_rent_id
AND per.start_date = (SELECT MAX(start_date) from pn_var_periods_all
WHERE var_rent_id = ip_var_rent_id);
SELECT proration_rule
FROM pn_var_rents_all
WHERE var_rent_id = ip_var_rent_id;
UPDATE pn_var_rent_inv_all
SET rec_abatement = ROUND(l_rec_abatement, g_precision)
WHERE var_rent_inv_id = inv_rec.var_rent_inv_id;
UPDATE pn_var_abat_defaults_all
SET allowance_applied =0
WHERE var_rent_id = p_var_rent_id;
UPDATE pn_var_rent_inv_all
SET abatement_appl =0
WHERE var_rent_id = p_var_rent_id;
SELECT
period_id
,start_date
,end_date
,partial_period
FROM
pn_var_periods_all
WHERE
var_rent_id = p_vr_id AND
NVL(status, pn_var_rent_calc_pkg.G_PERIOD_ACTIVE_STATUS)
<> pn_var_rent_calc_pkg.G_PERIOD_REVERSED_STATUS
ORDER BY
start_date;
SELECT
period_id
,start_date
,end_date
,partial_period
FROM
pn_var_periods_all
WHERE
var_rent_id = p_vr_id AND
period_id = p_prd_id
/*Bug#6849764
start_date >= (SELECT start_date
FROM pn_var_periods_all
WHERE period_id = p_prd_id)
Bug#6849764*/
ORDER BY
start_date;
SELECT
period_id
,start_date
,end_date
,partial_period
FROM
pn_var_periods_all
WHERE
var_rent_id = p_vr_id AND
NVL(status, pn_var_rent_calc_pkg.G_PERIOD_ACTIVE_STATUS)
<> pn_var_rent_calc_pkg.G_PERIOD_REVERSED_STATUS AND
start_date < (SELECT start_date
FROM pn_var_periods_all
WHERE period_id = p_prd_id)
ORDER BY
start_date;
SELECT pvp.period_id, pvp.partial_period
FROM pn_var_periods_all pvp, pn_var_rents_all pvr
WHERE pvr.var_rent_id = p_var_rent_id
AND pvp.var_rent_id = pvr.var_rent_id
AND pvp.start_date = pvr.commencement_date;
SELECT period_id
FROM pn_var_periods_all
WHERE var_rent_id=p_var_rent_id
AND period_num=1
AND partial_period='Y';
/* check if trx tables need to be updated for change in bkts */
l_context := 'Call pn_var_trx_pkg.populate_transactions';
/* check if trx tables need to be updated for change in sales volume */
l_context := 'Call pn_var_trx_pkg.populate_sales';
/* check if trx tables need to be updated for change in forecasted sales volume */
l_context := 'Call pn_var_trx_pkg.populate_sales_for';
/* check if trx tables need to be updated for change in sales volume */
l_context := 'Call pn_var_trx_pkg.populate_sales';
l_periods_t.DELETE;
l_trx_hrd_t.DELETE;
UPDATE
pn_var_trx_headers_all
SET
calculated_rent = round(l_trx_hrd_r.calculated_rent,g_precision)
,prorated_rent_due = round(l_trx_hrd_r.prorated_rent_due,g_precision)
,percent_rent_due = round(l_trx_hrd_r.percent_rent_due,g_precision)
,ytd_percent_rent = round(l_trx_hrd_r.ytd_percent_rent,g_precision)
,first_yr_rent = round(l_trx_hrd_r.first_yr_rent,g_precision)
WHERE
trx_header_id = l_trx_hrd_r.trx_header_id;
pnp_debug_pkg.log (' After Update of pn_var_trx_headers_all - Actual Rents ');
l_trx_hrd_for_t.DELETE;
UPDATE
pn_var_trx_headers_all
SET
calculated_rent_for = round(l_trx_hrd_for_r.calculated_rent_for,g_precision)
,percent_rent_due_for = round(l_trx_hrd_for_r.percent_rent_due_for,g_precision)
,ytd_percent_rent_for = round(l_trx_hrd_for_r.ytd_percent_rent_for,g_precision)
WHERE
trx_header_id = l_trx_hrd_for_r.trx_header_id;
pnp_debug_pkg.log (' After update of pn_var_trx_headers_all procedure - g_invoice_on = G_INV_ON_FORECASTED');
l_periods_t.DELETE;
/*Insert Invoice/post summary*/
/* cache the periods*/
IF p_var_rent_id IS NOT NULL AND
p_period_id IS NULL
THEN
OPEN periods_vr_c( p_vr_id => p_var_rent_id);
l_context := 'Call pn_var_rent_calc_pkg.insert_invoice';
pn_var_rent_calc_pkg.insert_invoice
( p_var_rent_id => p_var_rent_id
,p_period_id => l_periods_t(p).period_id);
pn_var_rent_calc_pkg.insert_invoice_for
( p_var_rent_id => p_var_rent_id
,p_period_id => l_periods_t(p).period_id);
/*pn_var_rent_calc_pkg.*/insert_invoice_firstyr( p_var_rent_id => p_var_rent_id );
END IF;/*End insert invoice/post summary*/
inv_date_tab.delete;
select nvl(sum(actual_invoiced_amount),0)
into l_actual_amount
from pn_var_rent_inv_all
where period_id = l_periods_t(p).period_id
and var_rent_id = p_var_rent_id;
/* deletes the draft term for which invoice_date or term template has changed.*/
pn_var_rent_calc_pkg.delete_draft_terms( p_var_rent_id => p_var_rent_id);
If g_calc_type is CALCULATE, we only insert/update the forecasted.
If g_calc_type is RECONCILE, we insert/update both.
*/
PROCEDURE insert_invoice_for( p_var_rent_id IN NUMBER
,p_period_id IN NUMBER) IS
/* get invoice dates for a period */
CURSOR invoice_dates_c( p_vr_id IN NUMBER
,p_prd_id IN NUMBER) IS
SELECT
invoice_date
,NVL(SUM(tot_act_vol), 0) AS total_actual_sales
,NVL(SUM(act_var_rent), 0) AS total_actual_rent
,NVL(SUM(for_var_rent), 0) AS total_forecasted_rent
FROM
pn_var_rent_summ_all
WHERE
var_rent_id = p_vr_id AND
period_id = p_prd_id
GROUP BY
invoice_date
ORDER BY
invoice_date;
SELECT
var_rent_inv_id
,var_rent_id
,period_id
,invoice_date
,forecasted_term_status
,variance_term_status
,variance_exp_code
,forecasted_exp_code
,adjust_num
,tot_act_vol
,act_per_rent
,for_per_rent
,actual_invoiced_amount
,constr_actual_rent
,rec_abatement_override
FROM
pn_var_rent_inv_all
WHERE
var_rent_id = p_vr_id AND
period_id = p_prd_id AND
invoice_date = p_inv_dt
AND
NVL(true_up_amt, 0) = 0 AND
true_up_status IS NULL AND
true_up_exp_code IS NULL
ORDER BY adjust_num DESC;
SELECT
NVL(SUM(actual_invoiced_amount), 0) AS prev_invoiced_amt
FROM
pn_var_rent_inv_all
WHERE
var_rent_id = p_vr_id AND
period_id = p_prd_id AND
invoice_date = p_inv_dt AND
variance_exp_code = 'Y'
AND
NVL(true_up_amt, 0) = 0 AND
true_up_status IS NULL AND
true_up_exp_code IS NULL;
SELECT 1 as vol_exists
FROM DUAL
WHERE exists(
SELECT vol_hist_id
FROM pn_var_vol_hist_all
WHERE grp_date_id in(
SELECT grp_date_id
FROM pn_var_grp_dates_all
WHERE var_rent_id = ip_var_rent_id
AND invoice_date = ip_invoice_date));
SELECT org_id
FROM pn_var_rents_all
WHERE var_rent_id = p_vr_id;
pnp_debug_pkg.log('+++++++++ insert_invoice_for START +++++++++++');
1. No invoice exist. We insert a invoice with forecasted rents.
2. Invoice exists and has not been exported. Update the forecasted calculated rent.
3. Invoice exists and has been exported. Do not update anything.
*/
l_row_id := NULL;
/* invoice updateable? */
IF NVL(inv2upd_rec.forecasted_exp_code, 'N') <> 'Y' THEN
/* updateable */
l_var_rent_inv_id := inv2upd_rec.var_rent_inv_id;
/* NON - updateable */
l_var_rent_inv_id := NULL;
END IF; /* invoice updateable? */
pn_var_rent_inv_pkg.insert_row
( x_rowid => l_row_id,
x_var_rent_inv_id => l_var_rent_inv_id,
x_adjust_num => 0,
x_invoice_date => inv_rec.invoice_date,
x_for_per_rent => inv_rec.total_forecasted_rent,
x_tot_act_vol => NULL,
x_act_per_rent => NULL,
x_constr_actual_rent => NULL,
x_abatement_appl => 0,
x_rec_abatement => NULL,
x_rec_abatement_override => l_rec_abatement_override,
x_negative_rent => 0,
x_actual_invoiced_amount => NULL,
x_period_id => p_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 => 'created invoice',
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_global.user_id,0),
x_last_update_date => SYSDATE,
x_last_updated_by => NVL(fnd_global.user_id,0),
x_last_update_login => NVL(fnd_global.login_id,0),
x_org_id => l_org_id );
/* no invoice to update - We have already created a forcasted invoice and exported it. */
IF l_var_rent_inv_id IS NULL THEN
NULL;
DELETE
pn_payment_terms_all
WHERE
var_rent_inv_id = l_var_rent_inv_id AND
status <> pn_var_rent_calc_pkg.G_TERM_STATUS_APPROVED AND
var_rent_type = pn_var_rent_calc_pkg.G_INV_ON_FORECASTED;
/* update the invoice */
/*Since forcasted rents are not yet exported, we can update them.*/
UPDATE
pn_var_rent_inv_all
SET
for_per_rent = ROUND(inv_rec.total_forecasted_rent, g_precision)
,forecasted_term_status = 'N'
,credit_flag = 'N' -- bug # 5937807
,last_update_date = SYSDATE
,last_updated_by = NVL(fnd_global.user_id,0)
,last_update_login = NVL(fnd_global.login_id,0)
WHERE
var_rent_inv_id = l_var_rent_inv_id;
/* invoice updateable? */
IF NVL(inv2upd_rec.variance_exp_code, 'N') <> 'Y' THEN
/* updateable */
l_var_rent_inv_id := inv2upd_rec.var_rent_inv_id;
/* NON - updateable */
l_var_rent_inv_id := NULL;
END IF; /* invoice updateable? */
/*Only if we are reconciling, do we need to update the last row or insert an adjustment.*/
/* invoice has been created here in the past */
/* get the previously billed amount from approved invoices */
FOR prev_inv_rec IN prev_invoiced_c( p_vr_id => p_var_rent_id
,p_prd_id => p_period_id
,p_inv_dt => inv_rec.invoice_date)
LOOP
l_prev_inv_exp_rent := prev_inv_rec.prev_invoiced_amt;
/* no invoice to update - create a new one */
IF l_var_rent_inv_id IS NULL THEN
/* if there a change in rent */
IF round(inv_rec.total_actual_rent, l_precision) <> round(l_prev_inv_rent, l_precision)
OR round(l_constr_rent, l_precision) <> round(l_constr_prev_rent, l_precision)
THEN
/* create new invoice for difference amt */
/* Forecasted rent is not updated in invoices. So always set it to prev invoiced forcasted ammount*/
pn_var_rent_inv_pkg.insert_row
( x_rowid => l_row_id,
x_var_rent_inv_id => l_var_rent_inv_id,
x_adjust_num => l_max_adjust_num,
x_invoice_date => inv_rec.invoice_date,
x_for_per_rent => l_curr_inv_rent_for,
x_tot_act_vol => inv_rec.total_actual_sales,
x_act_per_rent => inv_rec.total_actual_rent,
x_constr_actual_rent => l_constr_rent,
x_abatement_appl => 0,
x_rec_abatement => NULL,
x_rec_abatement_override => l_rec_abatement_override,
x_negative_rent => 0,
x_actual_invoiced_amount => (l_constr_rent - l_prev_inv_exp_rent),
x_period_id => p_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 => 'created invoice',
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_global.user_id,0),
x_last_update_date => SYSDATE,
x_last_updated_by => NVL(fnd_global.user_id,0),
x_last_update_login => NVL(fnd_global.login_id,0),
x_org_id => l_org_id );
DELETE
pn_payment_terms_all
WHERE
var_rent_inv_id = l_var_rent_inv_id AND
status <> pn_var_rent_calc_pkg.G_TERM_STATUS_APPROVED AND
var_rent_type = pn_var_rent_calc_pkg.G_INV_ON_VARIANCE; --bug#12702776
/* update the invoice */
UPDATE
pn_var_rent_inv_all
SET
act_per_rent = ROUND(inv_rec.total_actual_rent, g_precision)
,constr_actual_rent = ROUND(l_constr_rent, g_precision)
,actual_invoiced_amount = ROUND((l_constr_rent - l_prev_inv_exp_rent), g_precision)
,tot_act_vol = ROUND(inv_rec.total_actual_sales, g_precision) -- bug # 6007571
,credit_flag = 'N' -- bug # 5937807
,variance_term_status = 'N'
,last_update_date = SYSDATE
,last_updated_by = NVL(fnd_global.user_id,0)
,last_update_login = NVL(fnd_global.login_id,0)
WHERE
var_rent_inv_id = l_var_rent_inv_id;
pnp_debug_pkg.log('+++++++++ insert_invoice_for END +++++++++++');
END insert_invoice_for;
SELECT
bkhd.bkpt_header_id
,bkhd.breakpoint_type
FROM
pn_var_bkpts_head_all bkhd
,pn_var_bkpts_det_all bkdt
WHERE
bkdt.bkpt_detail_id = p_bkdt_id AND
bkdt.bkpt_header_id = bkhd.bkpt_header_id;
SELECT
reptg_freq_code AS report_freq_code
FROM
pn_var_rent_dates_all
WHERE
var_rent_id = p_vr_id;
SELECT
count(period_id) perion_num
FROM
pn_var_periods_all
WHERE
var_rent_id = p_vr_id AND
NVL(status, 'A') <> pn_var_rent_calc_pkg.G_PERIOD_REVERSED_STATUS;
SELECT
prd.period_id
,prd.partial_period
FROM
pn_var_periods_all prd,
pn_var_rents_all var
WHERE
prd.var_rent_id = p_vr_id AND
prd.var_rent_id = var.var_rent_id AND
prd.end_date = var.termination_date;
SELECT
prd.period_id
,prd.partial_period
FROM
pn_var_periods_all prd,
pn_var_rents_all var
WHERE
prd.var_rent_id = p_vr_id AND
prd.var_rent_id = var.var_rent_id AND
prd.start_date = var.commencement_date;
l_bkpts_t.DELETE;
- update l_trx_hdr_rec.percent_rent_due
- then get the YTD
*/
l_trx_hdr_rec.ytd_percent_rent_for
:= l_prev_billed + l_trx_hdr_rec.percent_rent_due_for;
l_trx_hdr_rec.update_flag := 'Y';
SELECT NVL(SUM(percent_rent_due_for), 0) AS prev_billed_amt
FROM
pn_var_trx_headers_all
WHERE
var_rent_id = p_vr_id AND
period_id = p_prd_id AND
line_item_id = p_line_id AND
reset_group_id = p_rst_grp_id AND
calc_prd_start_date < p_date;
SELECT dtls.prorated_grp_vol_start,
dtls.prorated_grp_vol_end,
dtls.ytd_group_vol_start,
dtls.ytd_group_vol_end,
dtls.blended_period_vol_start,
dtls.blended_period_vol_end,
hdr.prorated_group_sales_for,
hdr.ytd_sales_for
FROM pn_var_trx_headers_all hdr,
pn_var_trx_details_all dtls
WHERE hdr.trx_header_id=dtls.trx_header_id
AND dtls.trx_detail_id = detail_id;
SELECT var.termination_date,
DECODE(dates.invg_freq_code,'MON',1
,'QTR',3
,'SA' ,6
,'YR' ,12
,NULL) invg_freq_code,
var.proration_rule
FROM PN_VAR_RENTS_ALL var, PN_VAR_RENT_DATES_ALL dates
WHERE var.var_rent_id = vr_id
AND dates.var_rent_id = var.var_rent_id;
SELECT DISTINCT inv_end_date
FROM pn_var_grp_dates_all
WHERE var_rent_id = vr_id
AND inv_start_date = p_inv_start_date;
SELECT period_id, end_date
FROM pn_var_periods_all
WHERE var_rent_id = vr_id
AND partial_period = 'Y'
AND period_num = 1;
SELECT period_id,
end_date,
decode(status,'REVERSED','Y','N') status
FROM pn_var_periods_all
WHERE var_rent_id = vr_id
AND period_id = p_period_id;
SELECT var.proration_rule,
var.commencement_date,
var.termination_date
FROM PN_VAR_RENTS_ALL var
WHERE var.var_rent_id = vr_id;
SELECT period_id, start_date
FROM pn_var_periods_all
WHERE var_rent_id = vr_id
AND partial_period = 'Y'
AND period_num = 1;
SELECT period_id, start_date
FROM pn_var_periods_all
WHERE var_rent_id = vr_id
AND partial_period = 'Y';
SELECT dates.invg_day_of_month,
dates.invg_days_after,
var.termination_date,
var.proration_rule,
DECODE(dates.invg_freq_code,'MON',1
,'QTR',3
,'SA' ,6
,'YR' ,12
,NULL) invg_freq_code
FROM PN_VAR_RENTS_ALL var, PN_VAR_RENT_DATES_ALL dates
WHERE var.var_rent_id = vr_id
AND dates.var_rent_id = var.var_rent_id;
SELECT period_id,
decode(status,'REVERSED','Y','N') status
FROM pn_var_periods_all
WHERE var_rent_id = vr_id
AND period_id = p_period_id;
SELECT period_id, start_date
FROM pn_var_periods_all
WHERE var_rent_id = vr_id
AND partial_period = 'Y'
AND period_num = 1;
SELECT period_id
FROM pn_var_rent_summ_all
WHERE var_rent_id = vr_id
AND nvl(first_yr_rent,0) <> 0
AND rownum < 2;
SELECT
grp.grp_date_id
,grp.group_date
,grp.invoice_date
,grp.org_id
FROM
pn_var_grp_dates_all grp
WHERE
grp.var_rent_id = p_vr_id AND
grp.period_id = p_prd_id AND
grp.grp_date_id = p_grp_id;
SELECT
NVL(SUM(hdr.percent_rent_due_for)
, 0) AS rent
,NVL(SUM(hdr.prorated_group_sales_for)
, 0) AS 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 AND
hdr.grp_date_id = p_grp_id;
SELECT
var_rent_summ_id
FROM
pn_var_rent_summ_all
WHERE
var_rent_id = p_vr_id AND
period_id = p_prd_id AND
line_item_id = p_line_id AND
grp_date_id = p_grp_id;
/* to insert a new summary record */
INSERT INTO
pn_var_rent_summ_all
(var_rent_summ_id
,var_rent_id
,period_id
,line_item_id
,invoice_date
,tot_for_vol
,for_var_rent
,grp_date_id
,group_date
,last_update_date
,last_updated_by
,creation_date
,created_by
,last_update_login
,org_id)
VALUES
(pn_var_rent_summ_s.NEXTVAL
,p_var_rent_id
,p_period_id
,p_line_item_id
,grp_rec.invoice_date
,summ_rec.sales
,round(summ_rec.rent,g_precision)
,grp_rec.grp_date_id
,grp_rec.group_date
,SYSDATE
,NVL(fnd_global.user_id, 0)
,SYSDATE
,NVL(fnd_global.user_id, 0)
,NVL(fnd_global.login_id, 0)
,NVL(grp_rec.org_id, g_org_id))
RETURNING
var_rent_summ_id
INTO
l_vr_summ_id;
/* update the summary record */
UPDATE
pn_var_rent_summ_all
SET
tot_for_vol = summ_rec.sales
,for_var_rent = round(summ_rec.rent,g_precision)
,last_update_date = SYSDATE
,last_updated_by = NVL(fnd_global.user_id, 0)
,last_update_login = NVL(fnd_global.login_id, 0)
WHERE
var_rent_summ_id = l_vr_summ_id;
UPDATE
pn_var_trx_headers_all hdr
SET
hdr.var_rent_summ_id = l_vr_summ_id
WHERE
hdr.var_rent_id = p_var_rent_id AND
hdr.period_id = p_period_id AND
hdr.line_item_id = p_line_item_id AND
hdr.grp_date_id = p_grp_date_id;
SELECT
line_item_id
FROM
pn_var_lines_all
WHERE
var_rent_id = p_vr_id AND
period_id = p_prd_id;
SELECT
grp.grp_date_id
,grp.group_date
,grp.invoice_date
,grp.org_id
FROM
pn_var_grp_dates_all grp
,pn_var_periods_all prd
WHERE
prd.var_rent_id = p_vr_id AND
prd.period_id = p_prd_id AND
grp.period_id = prd.period_id AND
grp.grp_end_date <= prd.end_date;
SELECT
NVL(SUM(hdr.percent_rent_due_for)
, 0) AS rent
,NVL(SUM(hdr.prorated_group_sales_for)
, 0) AS 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 AND
hdr.grp_date_id = p_grp_id;
SELECT
var_rent_summ_id
,tot_for_vol
,for_var_rent
FROM
pn_var_rent_summ_all
WHERE
var_rent_id = p_vr_id AND
period_id = p_prd_id AND
line_item_id = p_line_id AND
grp_date_id = p_grp_id;
l_grp_t.DELETE;
UPDATE
pn_var_rent_summ_all
SET
tot_for_vol = summ_rec.sales
,for_var_rent = round(summ_rec.rent,g_precision)
,last_update_date = SYSDATE
,last_updated_by = NVL(fnd_global.user_id, 0)
,last_update_login = NVL(fnd_global.login_id, 0)
WHERE
var_rent_summ_id = l_vr_summ_id;
/* to insert a new summary record */
pnp_debug_pkg.log('Inserting a new record for var_rent_summ table');
INSERT INTO
pn_var_rent_summ_all
(var_rent_summ_id
,var_rent_id
,period_id
,line_item_id
,invoice_date
,tot_for_vol
,for_var_rent
,grp_date_id
,group_date
,last_update_date
,last_updated_by
,creation_date
,created_by
,last_update_login
,org_id)
VALUES
(pn_var_rent_summ_s.NEXTVAL
,p_var_rent_id
,p_period_id
,line_rec.line_item_id
,l_grp_t(g).invoice_date
,summ_rec.sales
,round(summ_rec.rent,g_precision)
,l_grp_t(g).grp_date_id
,l_grp_t(g).group_date
,SYSDATE
,NVL(fnd_global.user_id, 0)
,SYSDATE
,NVL(fnd_global.user_id, 0)
,NVL(fnd_global.login_id, 0)
,NVL(l_grp_t(g).org_id, g_org_id))
RETURNING
var_rent_summ_id
INTO
l_vr_summ_id;
pnp_debug_pkg.log('Inserted rent summ with id: '||l_vr_summ_id);
UPDATE
pn_var_trx_headers_all hdr
SET
hdr.var_rent_summ_id = l_vr_summ_id
WHERE
hdr.var_rent_id = p_var_rent_id AND
hdr.period_id = p_period_id AND
hdr.line_item_id = line_rec.line_item_id AND
hdr.grp_date_id = l_grp_t(g).grp_date_id;
SELECT pvr.var_rent_id,
pvr.invoice_on,
pvr.cumulative_vol,
pvr.rent_num,
pl.org_id
FROM pn_leases pl,
pn_lease_details_all pld,
pn_var_rents_all pvr,
pn_locations_all ploc
WHERE pl.lease_id = pvr.lease_id
AND pld.lease_id = pvr.lease_id
AND ploc.location_id = pvr.location_id
AND ploc.location_id IN (SELECT location_id
FROM pn_locations_all
START WITH location_id
IN
(SELECT location_id
FROM pn_locations_all
WHERE property_id IN(SELECT property_id
FROM pn_properties_all
WHERE property_code=NVL(l_prop_code,property_code))
--OR property_name=NVL(p_property_name,property_name)) /* 9765197 */
)
CONNECT BY PRIOR location_id=parent_location_id)
AND pl.lease_num >= NVL(p_lease_num_from, pl.lease_num)
AND pl.lease_num <= NVL(p_lease_num_to, pl.lease_num)
AND ploc.location_code >= NVL(p_location_code_from, ploc.location_code)
AND ploc.location_code <= NVL(p_location_code_to, ploc.location_code)
AND pvr.rent_num >= NVL(p_vrent_num_from,pvr.rent_num)
AND pvr.rent_num <= NVL(p_vrent_num_to,pvr.rent_num)
AND pld.responsible_user = NVL(p_responsible_user, pld.responsible_user)
AND pvr.invoice_on = NVL(p_invoice_on,pvr.invoice_on)
AND pl.org_id = NVL(p_org_id,pl.org_id)
ORDER BY pl.lease_id, pvr.var_rent_id;
SELECT pvr.var_rent_id,
pvr.invoice_on,
pvr.cumulative_vol,
pvr.rent_num,
pl.org_id
FROM pn_leases pl,
pn_lease_details_all pld,
pn_var_rents_all pvr,
pn_locations_all ploc
WHERE pl.lease_id = pvr.lease_id
AND pld.lease_id = pvr.lease_id
AND ploc.location_id = pvr.location_id
AND pl.lease_num >= NVL(p_lease_num_from, pl.lease_num)
AND pl.lease_num <= NVL(p_lease_num_to, pl.lease_num)
AND ploc.location_code >= NVL(p_location_code_from, ploc.location_code)
AND ploc.location_code <= NVL(p_location_code_to, ploc.location_code)
AND pvr.rent_num >= NVL(p_vrent_num_from,pvr.rent_num)
AND pvr.rent_num <= NVL(p_vrent_num_to,pvr.rent_num)
AND pld.responsible_user = NVL(p_responsible_user, pld.responsible_user)
AND pvr.invoice_on = NVL(p_invoice_on,pvr.invoice_on)
AND pl.org_id = NVL(p_org_id,pl.org_id)
ORDER BY pl.lease_id, pvr.var_rent_id;
SELECT pvr.var_rent_id,
pvr.invoice_on,
pvr.cumulative_vol,
pvr.rent_num,
pl.org_id
FROM pn_var_rents_all pvr,
pn_leases pl,
pn_lease_details_all pld
WHERE pl.lease_id = pvr.lease_id
AND pld.lease_id = pvr.lease_id
AND pl.lease_num >= NVL(p_lease_num_from, pl.lease_num)
AND pl.lease_num <= NVL(p_lease_num_to, pl.lease_num)
AND pvr.rent_num >= NVL(p_vrent_num_from,pvr.rent_num)
AND pvr.rent_num <= NVL(p_vrent_num_to,pvr.rent_num)
AND pld.responsible_user = NVL(p_responsible_user, pld.responsible_user)
AND pvr.var_rent_id = NVL(p_var_rent_id,pvr.var_rent_id)
AND pvr.invoice_on = NVL(p_invoice_on,pvr.invoice_on)
AND pl.org_id = NVL(p_org_id,pl.org_id)
ORDER BY pl.lease_id, pvr.var_rent_id;
SELECT period_id,
period_num,
start_date,
end_date
FROM pn_var_periods_all
WHERE var_rent_id = ip_var_rent_id
AND period_id = NVL(p_period_id,period_id)
AND start_date <= NVL(fnd_date.canonical_to_date(p_period_date),TO_DATE('12/31/4712','mm/dd/yyyy'))
AND period_num >= NVL(p_period_num_from,period_num)
AND period_num <= NVL(p_period_num_to,period_num);
SELECT PROPERTY_CODE
into l_prop_code
FROM PN_PROPERTIES_ALL
where property_name = p_property_name;
SELECT true_up_amt, actual_invoiced_amount, act_per_rent, for_per_rent, adjust_num
FROM pn_var_rent_inv_all
WHERE var_rent_inv_id = p_var_rent_inv_id;
SELECT SUM(actual_invoiced_amount) actual
FROM pn_var_rent_inv_all
WHERE invoice_date = p_invoice_date
AND period_id = p_period_id
AND var_rent_inv_id <= p_var_rent_inv_id;
SELECT sum(actual_invoiced_amount) prev_amt
FROM pn_var_rent_inv_all rent_inv
WHERE var_rent_inv_id < p_var_rent_inv_id
AND (true_up_amt IS NULL
OR (true_up_amt IS NOT NULL))
AND period_id = p_period_id;
SELECT
NVL(SUM(ly_pr_grp_vol_start),0) AS bkpt_start
,NVL(SUM(ly_pr_grp_vol_end),0) AS bkpt_end
,bkpt_rate
FROM
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_vr_id AND
line_item_group_id = p_line_item_grp_id AND
calc_prd_end_date >= (ADD_MONTHS(p_vr_term_date, -12) + 1))
AND bkpt_rate = p_bkpt_rate
GROUP BY
bkpt_rate
ORDER BY
bkpt_start;
SELECT
NVL(SUM(fy_pr_grp_vol_start),0) AS bkpt_start
,NVL(SUM(fy_pr_grp_vol_end),0) AS bkpt_end
,bkpt_rate
FROM
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_vr_id AND
line_item_group_id = p_line_item_grp_id AND
calc_prd_start_date <= (ADD_MONTHS(p_vr_comm_date, 12) - 1))
AND bkpt_rate = p_bkpt_rate
GROUP BY
bkpt_rate
ORDER BY
bkpt_start;
SELECT pvp.period_id, pvr.commencement_date
FROM pn_var_periods_all pvp, pn_var_rents_all pvr
WHERE pvr.var_rent_id = p_var_rent_id
AND pvp.period_id = p_period_id
AND pvp.partial_period = 'Y'
AND pvp.start_date = pvr.commencement_date;
SELECT pvp.period_id, pvr.termination_date
FROM pn_var_periods_all pvp, pn_var_rents_all pvr
WHERE pvr.var_rent_id = p_var_rent_id
AND pvp.period_id = p_period_id
AND pvp.partial_period = 'Y'
AND pvp.end_date = pvr.termination_date;
SELECT dtls.ytd_group_vol_start,
dtls.pr_grp_blended_vol_start,
dtls.fy_pr_grp_vol_start,
dtls.ly_pr_grp_vol_start,
dtls.bkpt_rate,
hdr.var_rent_id,
hdr.period_id,
hdr.line_item_group_id
FROM pn_var_trx_details_all dtls, pn_var_trx_headers_all hdr
WHERE dtls.trx_detail_id = p_trx_detail_id
AND hdr.trx_header_id = dtls.trx_header_id;
SELECT pvp.period_id, pvr.commencement_date
FROM pn_var_periods_all pvp, pn_var_rents_all pvr
WHERE pvr.var_rent_id = p_var_rent_id
AND pvp.period_id = p_period_id
AND pvp.partial_period = 'Y'
AND pvp.start_date = pvr.commencement_date;
SELECT pvp.period_id, pvr.termination_date
FROM pn_var_periods_all pvp, pn_var_rents_all pvr
WHERE pvr.var_rent_id = p_var_rent_id
AND pvp.period_id = p_period_id
AND pvp.partial_period = 'Y'
AND pvp.end_date = pvr.termination_date;
SELECT dtls.ytd_group_vol_start,
dtls.pr_grp_blended_vol_start,
dtls.fy_pr_grp_vol_start,
dtls.ly_pr_grp_vol_start,
dtls.ytd_group_vol_end,
dtls.pr_grp_blended_vol_end,
dtls.fy_pr_grp_vol_end,
dtls.ly_pr_grp_vol_end,
dtls.bkpt_rate,
hdr.var_rent_id,
hdr.period_id,
hdr.line_item_group_id
FROM pn_var_trx_details_all dtls, pn_var_trx_headers_all hdr
WHERE dtls.trx_detail_id = p_trx_detail_id
AND hdr.trx_header_id = dtls.trx_header_id;
SELECT (NVL(ytd_sales, 0) - NVL(ytd_deductions, 0)) applicable_sales
FROM pn_var_trx_headers_all
WHERE trx_header_id = p_trx_header_id;
SELECT pvp.period_id, pvr.commencement_date
FROM pn_var_periods_all pvp, pn_var_rents_all pvr
WHERE pvr.var_rent_id = p_var_rent_id
AND pvp.period_id = p_period_id
AND pvp.partial_period = 'Y'
AND pvp.start_date = pvr.commencement_date;
SELECT pvp.period_id, pvr.termination_date
FROM pn_var_periods_all pvp, pn_var_rents_all pvr
WHERE pvr.var_rent_id = p_var_rent_id
AND pvp.period_id = p_period_id
AND pvp.partial_period = 'Y'
AND pvp.end_date = pvr.termination_date;
SELECT hdr.trx_header_id,
hdr.calc_prd_start_date,
hdr.period_id,
hdr.var_rent_id,
hdr.line_item_group_id,
dtls.bkpt_rate,
dtls.prorated_grp_vol_start,
dtls.prorated_grp_vol_end,
dtls.ytd_group_vol_start,
dtls.ytd_group_vol_end,
dtls.blended_period_vol_start,
dtls.blended_period_vol_end,
dtls.FY_PR_GRP_VOL_START,
dtls.FY_PR_GRP_VOL_END,
dtls.LY_PR_GRP_VOL_START,
dtls.LY_PR_GRP_VOL_END,
dtls.PR_GRP_BLENDED_VOL_START,
dtls.PR_GRP_BLENDED_VOL_END,
hdr.prorated_group_sales,
hdr.ytd_sales,
hdr.prorated_group_deductions,
hdr.ytd_deductions
FROM pn_var_trx_headers_all hdr,
pn_var_trx_details_all dtls
WHERE hdr.trx_header_id=dtls.trx_header_id
AND dtls.trx_detail_id = detail_id;
SELECT
NVL(SUM(fy_proration_sales - NVL(fy_proration_deductions,0) ),0) AS sales
FROM
pn_var_trx_headers_all
WHERE
var_rent_id = p_vr_id AND
line_item_group_id = p_line_item_grp_id AND
calc_prd_start_date <= (ADD_MONTHS(p_vr_comm_date, 12) - 1) ;
SELECT
NVL(SUM(ly_proration_sales - NVL(ly_proration_deductions,0) ),0) AS sales
FROM
pn_var_trx_headers_all
WHERE
var_rent_id = p_vr_id AND
line_item_group_id = p_line_item_grp_id AND
calc_prd_end_date >= (ADD_MONTHS(p_vr_term_date, -12) + 1);
SELECT pvp.period_id, pvr.commencement_date
FROM pn_var_periods_all pvp, pn_var_rents_all pvr
WHERE pvr.var_rent_id = p_var_rent_id
AND pvp.period_id = p_period_id
AND pvp.partial_period = 'Y'
AND pvp.start_date = pvr.commencement_date;
SELECT pvp.period_id, pvr.termination_date
FROM pn_var_periods_all pvp, pn_var_rents_all pvr
WHERE pvr.var_rent_id = p_var_rent_id
AND pvp.period_id = p_period_id
AND pvp.partial_period = 'Y'
AND pvp.end_date = pvr.termination_date;
SELECT
NVL(SUM(fy_proration_sales),0) AS sales
FROM
pn_var_trx_headers_all
WHERE
var_rent_id = p_vr_id AND
line_item_group_id = p_line_item_grp_id AND
calc_prd_start_date <= (ADD_MONTHS(p_vr_comm_date, 12) - 1) ;
SELECT
NVL(SUM(ly_proration_sales),0) AS sales
FROM
pn_var_trx_headers_all
WHERE
var_rent_id = p_vr_id AND
line_item_group_id = p_line_item_grp_id AND
calc_prd_end_date >= (ADD_MONTHS(p_vr_term_date, -12) + 1);
SELECT hdr.ytd_sales,
hdr.reporting_group_sales,
hdr.prorated_group_sales,
hdr.line_item_group_id,
hdr.period_id,
hdr.var_rent_id
FROM pn_var_trx_headers_all hdr,
pn_var_trx_details_all dtls
WHERE hdr.trx_header_id = dtls.trx_header_id
AND dtls.trx_detail_id = p_trx_detail_id;
SELECT pvp.period_id, pvr.commencement_date
FROM pn_var_periods_all pvp, pn_var_rents_all pvr
WHERE pvr.var_rent_id = p_var_rent_id
AND pvp.period_id = p_period_id
AND pvp.partial_period = 'Y'
AND pvp.start_date = pvr.commencement_date;
SELECT pvp.period_id, pvr.termination_date
FROM pn_var_periods_all pvp, pn_var_rents_all pvr
WHERE pvr.var_rent_id = p_var_rent_id
AND pvp.period_id = p_period_id
AND pvp.partial_period = 'Y'
AND pvp.end_date = pvr.termination_date;
SELECT hdr.ytd_sales,
hdr.reporting_group_sales,
hdr.prorated_group_sales,
hdr.line_item_group_id,
hdr.prorated_group_deductions,
hdr.reporting_group_deductions,
hdr.ytd_deductions,
hdr.period_id,
hdr.var_rent_id
FROM pn_var_trx_headers_all hdr,
pn_var_trx_details_all dtls
WHERE hdr.trx_header_id = dtls.trx_header_id
AND dtls.trx_detail_id = p_trx_detail_id;
SELECT pvp.period_id, pvr.commencement_date
FROM pn_var_periods_all pvp, pn_var_rents_all pvr
WHERE pvr.var_rent_id = p_var_rent_id
AND pvp.period_id = p_period_id
AND pvp.partial_period = 'Y'
AND pvp.start_date = pvr.commencement_date;
SELECT pvp.period_id, pvr.termination_date
FROM pn_var_periods_all pvp, pn_var_rents_all pvr
WHERE pvr.var_rent_id = p_var_rent_id
AND pvp.period_id = p_period_id
AND pvp.partial_period = 'Y'
AND pvp.end_date = pvr.termination_date;
SELECT
NVL(SUM(fy_proration_deductions),0) AS deductions
FROM
pn_var_trx_headers_all
WHERE
var_rent_id = p_vr_id AND
line_item_group_id = p_line_item_grp_id AND
calc_prd_start_date <= (ADD_MONTHS(p_vr_comm_date, 12) - 1) ;
SELECT
NVL(SUM(ly_proration_deductions),0) AS deductions
FROM
pn_var_trx_headers_all
WHERE
var_rent_id = p_vr_id AND
line_item_group_id = p_line_item_grp_id AND
calc_prd_end_date >= (ADD_MONTHS(p_vr_term_date, -12) + 1);
SELECT hdr.prorated_group_deductions,
hdr.reporting_group_deductions,
hdr.ytd_deductions,
hdr.line_item_group_id,
hdr.period_id,
hdr.var_rent_id
FROM pn_var_trx_headers_all hdr,
pn_var_trx_details_all dtls
WHERE hdr.trx_header_id = dtls.trx_header_id
AND dtls.trx_detail_id = p_trx_detail_id;
SELECT pvp.period_id, pvr.commencement_date
FROM pn_var_periods_all pvp, pn_var_rents_all pvr
WHERE pvr.var_rent_id = p_var_rent_id
AND pvp.period_id = p_period_id
AND pvp.partial_period = 'Y'
AND pvp.start_date = pvr.commencement_date;
SELECT pvp.period_id, pvr.termination_date
FROM pn_var_periods_all pvp, pn_var_rents_all pvr
WHERE pvr.var_rent_id = p_var_rent_id
AND pvp.period_id = p_period_id
AND pvp.partial_period = 'Y'
AND pvp.end_date = pvr.termination_date;
SELECT hdr.ytd_sales,
hdr.ytd_deductions,
hdr.line_item_group_id,
hdr.period_id,
hdr.var_rent_id,
hdr.trx_header_id,
hdr.calc_prd_start_date,
hdr.line_item_id
FROM pn_var_trx_headers_all hdr,
pn_var_trx_details_all dtls
WHERE hdr.trx_header_id = dtls.trx_header_id
AND dtls.trx_detail_id = p_trx_detail_id;
SELECT NVL(SUM(hdr.prorated_group_sales), 0) - NVL(SUM(hdr.prorated_group_deductions), 0) sales
FROM pn_var_trx_headers_all hdr
WHERE hdr.period_id = p_period_id
AND hdr.line_item_id = p_line_item_id
AND hdr.calc_prd_start_date <= p_calc_period_start_date
ORDER BY calc_prd_start_date;*/
SELECT pvp.period_id, pvr.commencement_date
FROM pn_var_periods_all pvp, pn_var_rents_all pvr
WHERE pvr.var_rent_id = p_var_rent_id
AND pvp.period_id = p_period_id
AND pvp.partial_period = 'Y'
AND pvp.start_date = pvr.commencement_date;
SELECT pvp.period_id, pvr.termination_date
FROM pn_var_periods_all pvp, pn_var_rents_all pvr
WHERE pvr.var_rent_id = p_var_rent_id
AND pvp.period_id = p_period_id
AND pvp.partial_period = 'Y'
AND pvp.end_date = pvr.termination_date;
SELECT dtls.prorated_grp_vol_start,
dtls.pr_grp_blended_vol_start,
dtls.blended_period_vol_start,
dtls.bkpt_rate,
hdr.var_rent_id,
hdr.period_id,
hdr.line_item_group_id
FROM pn_var_trx_details_all dtls, pn_var_trx_headers_all hdr
WHERE dtls.trx_detail_id = p_trx_detail_id
AND hdr.trx_header_id = dtls.trx_header_id;
SELECT sum(dtls.prorated_grp_vol_start) bkpt_start
FROM pn_var_trx_details_all dtls,
pn_var_trx_headers_all hdr
WHERE hdr.trx_header_id = dtls.trx_header_id
AND hdr.period_id = p_period_id
AND dtls.bkpt_rate = p_bkpt_rate;
SELECT pvp.period_id, pvr.commencement_date
FROM pn_var_periods_all pvp, pn_var_rents_all pvr
WHERE pvr.var_rent_id = p_var_rent_id
AND pvp.period_id = p_period_id
AND pvp.partial_period = 'Y'
AND pvp.start_date = pvr.commencement_date;
SELECT pvp.period_id, pvr.termination_date
FROM pn_var_periods_all pvp, pn_var_rents_all pvr
WHERE pvr.var_rent_id = p_var_rent_id
AND pvp.period_id = p_period_id
AND pvp.partial_period = 'Y'
AND pvp.end_date = pvr.termination_date;
SELECT dtls.prorated_grp_vol_start,
dtls.pr_grp_blended_vol_start,
dtls.blended_period_vol_start,
dtls.prorated_grp_vol_end,
dtls.pr_grp_blended_vol_end,
dtls.blended_period_vol_end,
dtls.bkpt_rate,
hdr.var_rent_id,
hdr.period_id,
hdr.line_item_group_id
FROM pn_var_trx_details_all dtls, pn_var_trx_headers_all hdr
WHERE dtls.trx_detail_id = p_trx_detail_id
AND hdr.trx_header_id = dtls.trx_header_id;
SELECT sum(prorated_grp_vol_start) bkpt_start,
sum(prorated_grp_vol_end) bkpt_end
FROM pn_var_trx_details_all dtls,
pn_var_trx_headers_all hdr
WHERE hdr.trx_header_id = dtls.trx_header_id
AND hdr.period_id = p_period_id
AND dtls.bkpt_rate = p_bkpt_rate;
SELECT pvp.period_id, pvr.commencement_date
FROM pn_var_periods_all pvp, pn_var_rents_all pvr
WHERE pvr.var_rent_id = p_var_rent_id
AND pvp.period_id = p_period_id
AND pvp.partial_period = 'Y'
AND pvp.start_date = pvr.commencement_date;
SELECT pvp.period_id, pvr.termination_date
FROM pn_var_periods_all pvp, pn_var_rents_all pvr
WHERE pvr.var_rent_id = p_var_rent_id
AND pvp.period_id = p_period_id
AND pvp.partial_period = 'Y'
AND pvp.end_date = pvr.termination_date;
SELECT dtls.prorated_grp_vol_start,
dtls.pr_grp_blended_vol_start,
dtls.bkpt_rate,
bkpts.group_bkpt_vol_start,
hdr.var_rent_id,
hdr.period_id,
hdr.line_item_group_id
FROM pn_var_trx_details_all dtls, pn_var_trx_headers_all hdr,
pn_var_bkpts_det_all bkpts
WHERE dtls.trx_detail_id = p_trx_detail_id
AND hdr.trx_header_id = dtls.trx_header_id
AND bkpts.bkpt_detail_id = dtls.bkpt_detail_id;
SELECT pvp.period_id, pvr.commencement_date
FROM pn_var_periods_all pvp, pn_var_rents_all pvr
WHERE pvr.var_rent_id = p_var_rent_id
AND pvp.period_id = p_period_id
AND pvp.partial_period = 'Y'
AND pvp.start_date = pvr.commencement_date;
SELECT pvp.period_id, pvr.termination_date
FROM pn_var_periods_all pvp, pn_var_rents_all pvr
WHERE pvr.var_rent_id = p_var_rent_id
AND pvp.period_id = p_period_id
AND pvp.partial_period = 'Y'
AND pvp.end_date = pvr.termination_date;
SELECT dtls.prorated_grp_vol_start,
dtls.prorated_grp_vol_end,
dtls.pr_grp_blended_vol_start,
dtls.pr_grp_blended_vol_end,
dtls.bkpt_rate,
bkpts.group_bkpt_vol_start,
bkpts.group_bkpt_vol_end,
hdr.var_rent_id,
hdr.period_id,
hdr.line_item_group_id
FROM pn_var_trx_details_all dtls, pn_var_trx_headers_all hdr,
pn_var_bkpts_det_all bkpts
WHERE dtls.trx_detail_id = p_trx_detail_id
AND hdr.trx_header_id = dtls.trx_header_id
AND bkpts.bkpt_detail_id = dtls.bkpt_detail_id;
SELECT pvp.period_id, pvr.commencement_date
FROM pn_var_periods_all pvp, pn_var_rents_all pvr
WHERE pvr.var_rent_id = p_var_rent_id
AND pvp.period_id = p_period_id
AND pvp.partial_period = 'Y'
AND pvp.start_date = pvr.commencement_date;
SELECT pvp.period_id, pvr.termination_date
FROM pn_var_periods_all pvp, pn_var_rents_all pvr
WHERE pvr.var_rent_id = p_var_rent_id
AND pvp.period_id = p_period_id
AND pvp.partial_period = 'Y'
AND pvp.end_date = pvr.termination_date;
SELECT hdr.percent_rent_due,
hdr.first_yr_rent,
hdr.period_id,
hdr.var_rent_id
FROM pn_var_trx_headers_all hdr,
pn_var_trx_details_all dtls
WHERE hdr.trx_header_id = dtls.trx_header_id
AND dtls.trx_detail_id = p_trx_detail_id;
SELECT SUM(first_yr_rent) first_yr_rent
FROM pn_var_trx_headers_all
WHERE var_rent_id = p_var_rent_id;
SELECT (SUM(NVL(prorated_rent_due, 0)) - SUM(NVL(first_yr_rent, 0))) last_yr_rent
FROM pn_var_trx_headers_all
WHERE period_id = p_period_id;
SELECT pvp.period_id, pvr.commencement_date
FROM pn_var_periods_all pvp, pn_var_rents_all pvr
WHERE pvr.var_rent_id = p_var_rent_id
AND pvp.period_id = p_period_id
AND pvp.partial_period = 'Y'
AND pvp.start_date = pvr.commencement_date;
SELECT pvp.period_id, pvr.termination_date
FROM pn_var_periods_all pvp, pn_var_rents_all pvr
WHERE pvr.var_rent_id = p_var_rent_id
AND pvp.period_id = p_period_id
AND pvp.partial_period = 'Y'
AND pvp.end_date = pvr.termination_date;
SELECT hdr.ytd_percent_rent,
hdr.first_yr_rent,
hdr.period_id,
hdr.var_rent_id
FROM pn_var_trx_headers_all hdr,
pn_var_trx_details_all dtls
WHERE hdr.trx_header_id = dtls.trx_header_id
AND dtls.trx_detail_id = p_trx_detail_id;
SELECT SUM(first_yr_rent) first_yr_rent
FROM pn_var_trx_headers_all
WHERE var_rent_id = p_var_rent_id;
SELECT (SUM(NVL(prorated_rent_due, 0)) - SUM(NVL(first_yr_rent, 0))) last_yr_rent
FROM pn_var_trx_headers_all
WHERE period_id = p_period_id;
SELECT
NVL(SUM(fy_proration_sales - NVL(fy_proration_deductions,0) ),0) AS sales
FROM
pn_var_trx_headers_all
WHERE
var_rent_id = p_vr_id AND
line_item_group_id = p_line_item_grp_id AND
calc_prd_start_date <= (ADD_MONTHS(p_vr_comm_date, 12) - 1) ;
SELECT trx.line_item_group_id, pvr.commencement_date
FROM pn_var_trx_headers_all trx,
pn_var_rents_all pvr
WHERE trx.var_rent_id = p_var_rent_id
AND trx.var_rent_id = pvr.var_rent_id
AND trx.line_item_id = p_line_item_id;
SELECT
NVL(SUM(fy_proration_sales),0) AS sales
FROM
pn_var_trx_headers_all
WHERE
var_rent_id = p_vr_id AND
line_item_group_id = p_line_item_grp_id AND
calc_prd_start_date <= (ADD_MONTHS(p_vr_comm_date, 12) - 1) ;
SELECT trx.line_item_group_id, pvr.commencement_date
FROM pn_var_trx_headers_all trx,
pn_var_rents_all pvr
WHERE trx.var_rent_id = p_var_rent_id
AND trx.var_rent_id = pvr.var_rent_id
AND trx.line_item_id = p_line_item_id;
SELECT
NVL(SUM(fy_proration_deductions),0) AS deductions
FROM
pn_var_trx_headers_all
WHERE
var_rent_id = p_vr_id AND
line_item_group_id = p_line_item_grp_id AND
calc_prd_start_date <= (ADD_MONTHS(p_vr_comm_date, 12) - 1) ;
SELECT trx.line_item_group_id, pvr.commencement_date
FROM pn_var_trx_headers_all trx,
pn_var_rents_all pvr
WHERE trx.var_rent_id = p_var_rent_id
AND trx.var_rent_id = pvr.var_rent_id
AND trx.line_item_id = p_line_item_id;
SELECT dtls.ytd_group_vol_start AS trueup_bkpt_vol_start
,dtls.ytd_group_vol_end AS trueup_bkpt_vol_end
,hdr.ytd_sales AS trueup_volume
,hdr.ytd_deductions AS deductions
FROM pn_var_trx_headers_all hdr,
pn_var_trx_details_all dtls
WHERE dtls.trx_detail_id = p_trx_detail_id
AND dtls.bkpt_rate = p_rate
AND dtls.trx_header_id = hdr.trx_header_id;
SELECT sum(NVL(act_var_rent, 0)) + sum(NVL(trueup_var_rent, 0)) - sum(NVL(first_yr_rent,0)) true_up_rent,
sum(NVL(tot_act_vol, 0)) true_up_vol,
sum(NVL(tot_ded, 0)) true_up_deductions
FROM pn_var_rent_summ_all
WHERE period_id = p_period_id;
SELECT distinct gd1.invoice_date,gd1.period_id,decode(temp.inv_dt,NULL,'N','Y')
FROM pn_var_grp_dates_all gd1,
(SELECT gd.invoice_date inv_dt
FROM pn_var_grp_dates_all gd
WHERE EXISTS (SELECT NULL from pn_var_vol_hist_all vol
WHERE vol_hist_status_code = p_status
AND vol.period_id = gd.period_id
AND vol.invoicing_date= gd.invoice_date
)
AND
NOT EXISTS (SELECT NULL from pn_var_rent_inv_all vinv
WHERE vinv.invoice_date=gd.invoice_date
AND vinv.period_id=gd.period_id
)
AND gd.var_rent_id=p_vrent_id
)temp
WHERE gd1.var_rent_id=p_vrent_id
AND gd1.invoice_date=temp.inv_dt(+)
ORDER BY gd1.invoice_date;
SELECT 'Y' fst_prd_flag
FROM dual
WHERE EXISTS
(SELECT period_id FROM pn_var_periods_all vp
WHERE period_num=1
AND var_rent_id=p_vrent_id
AND partial_period='Y'
AND EXISTS (select NULL from pn_var_vol_hist_all vol
where vol_hist_status_code = p_status
and vol.period_id = vp.period_id
and var_rent_id=p_vrent_id
)
AND NOT EXISTS (select NULL from pn_var_rent_inv_all vinv
where vinv.var_rent_id = p_vrent_id
and vinv.period_id = vp.period_id
)
);
SELECT distinct gd1.invoice_date ,gd1.period_id, decode(temp.inv_dt,NULL,'N','Y')
FROM pn_var_grp_dates_all gd1,
pn_var_periods_all vp,
(SELECT gd.invoice_date inv_dt
FROM pn_var_grp_dates_all gd
WHERE EXISTS(select NULL from pn_var_vol_hist_all vol
where vol_hist_status_code = p_status
and vol.period_id = gd.period_id
and vol.invoicing_date= gd.invoice_date
)
AND NOT EXISTS (select NULL from pn_var_rent_inv_all vinv
where vinv.invoice_date=gd.invoice_date
and vinv.period_id=gd.period_id
)
AND gd.var_rent_id=p_vrent_id
)temp
where gd1.var_rent_id=p_vrent_id
and gd1.period_id=vp.period_id
and vp.period_num>1
and gd1.invoice_date=temp.inv_dt(+)
order by gd1.invoice_date;
SELECT org_id
FROM pn_var_rents_all
WHERE var_rent_id =p_var_rent_id;
SELECT distinct pterm.payment_term_id payment_term_id
FROM pn_payment_terms_all pterm,
pn_var_rents_all vrent,
pn_var_rent_inv_all vinv
WHERE vrent.lease_id = pterm.lease_id
AND vrent.var_rent_id = vinv.var_rent_id
AND pterm.start_date <=
(SELECT MAX(gd.grp_end_date)
FROM pn_var_grp_dates_all gd
WHERE gd.period_id = vinv.period_id
AND gd.invoice_date = vinv.invoice_date
)
AND pterm.end_date >=
(SELECT MIN(gd1.grp_start_date)
FROM pn_var_grp_dates_all gd1
WHERE gd1.period_id = vinv.period_id
AND gd1.invoice_date = vinv.invoice_date
)
AND pterm.var_rent_inv_id IS NULL
AND pterm.index_period_id IS NULL
AND vinv.adjust_num = 0
AND vinv.var_rent_inv_id=p_inv_id;
select * from pn_var_abatements_all
where var_rent_inv_id=p_inv_id;
SELECT var_abatement_id,payment_term_id
FROM pn_var_abatements_all
WHERE var_rent_inv_id = -1
AND var_rent_id=p_vrent_id;
SELECT var_rent_inv_id inv_id
FROM pn_var_rent_inv_all
WHERE invoice_date= p_inv_dt
AND var_rent_id = p_var_rent_id
AND period_id = p_prd_id
AND adjust_num = 0;
SELECT var_rent_inv_id
FROM pn_var_rent_inv_all
WHERE var_rent_id=p_var_rent_id
AND period_id IN (SELECT period_id
FROM pn_var_periods_all
WHERE period_num=1
AND var_rent_id=p_var_rent_id
AND partial_period='Y'
)
AND adjust_num=0;
update pn_var_abatements_all
set var_rent_inv_id = l_fst_inv_id
where var_abatement_id = l_min_var_abt_id;
PN_VAR_ABATEMENTS_PKG.INSERT_ROW(
X_ROWID => l_row_id,
X_VAR_ABATEMENT_ID => l_var_abmt_id,
X_VAR_RENT_ID => p_var_rent_id,
X_VAR_RENT_INV_ID => l_inv_id,
X_PAYMENT_TERM_ID => l_pmt_term_id,
X_INCLUDE_TERM => abtmt_inv_rec.include_term,
X_INCLUDE_INCREASES => abtmt_inv_rec.include_increases,
X_UPDATE_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('USER_ID'),-1),
X_ORG_ID => l_org_id );
SELECT org_id
FROM pn_var_rents_all
WHERE var_rent_id =p_var_rent_id;
SELECT prd.period_id
FROM pn_var_periods_all prd ,pn_var_rents_all vrent
WHERE prd.var_rent_id=p_var_rent_id
AND vrent.var_rent_id = prd.var_rent_id
AND prd.end_date = vrent.termination_date
AND partial_period='Y';
SELECT var_rent_inv_id
FROM pn_var_rent_inv_all
WHERE var_rent_id=p_var_rent_id
AND period_id=p_prd_id
AND invoice_date=p_inv_dt
AND adjust_num=0;
SELECT var_rent_inv_id
FROM pn_var_rent_inv_all
WHERE var_rent_id=p_var_rent_id
AND period_id=p_prd_id
AND var_rent_inv_id <> p_inv_id
AND adjust_num=0;
SELECT * FROM pn_var_abatements_all
WHERE var_rent_inv_id=p_inv_id;
SELECT 'Y' abt_exists
FROM dual
WHERE EXISTS ( SELECT NULL
FROM pn_var_abatements_all
WHERE var_rent_inv_id=p_inv_id
AND payment_term_id = p_pmt_id );
SELECT invoice_date
FROM pn_var_grp_dates_all
WHERE var_rent_id = p_var_rent_id
AND period_id = p_prd_id
AND grp_end_date = p_end_dt;
PN_VAR_ABATEMENTS_PKG.INSERT_ROW(
X_ROWID => l_row_id,
X_VAR_ABATEMENT_ID => l_var_abmt_id,
X_VAR_RENT_ID => p_var_rent_id,
X_VAR_RENT_INV_ID => l_last_inv_id,
X_PAYMENT_TERM_ID => l_pmt_term_id,
X_INCLUDE_TERM => abtmt_inv_rec.include_term,
X_INCLUDE_INCREASES => abtmt_inv_rec.include_increases,
X_UPDATE_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('USER_ID'),-1),
X_ORG_ID => l_org_id );
SELECT min(invoice_date) fst_inv_dt
FROM pn_var_grp_dates_all
WHERE var_rent_id= p_var_rent_id ;
SELECT distinct pterm.payment_term_id payment_term_id
FROM pn_payment_terms_all pterm,
pn_var_rents_all vrent,
pn_var_rent_inv_all vinv
WHERE vrent.lease_id = pterm.lease_id
AND vrent.var_rent_id = vinv.var_rent_id
AND pterm.start_date <=
(SELECT MAX(gd.grp_end_date)
FROM pn_var_grp_dates_all gd
WHERE gd.period_id = vinv.period_id
AND gd.invoice_date = vinv.invoice_date
)
AND pterm.end_date >=
(SELECT MIN(gd1.grp_start_date)
FROM pn_var_grp_dates_all gd1
WHERE gd1.period_id = vinv.period_id
AND gd1.invoice_date = vinv.invoice_date
)
AND pterm.var_rent_inv_id IS NULL
AND pterm.index_period_id IS NULL
AND vinv.adjust_num = 0
AND vinv.var_rent_inv_id=p_inv_id;
select * from pn_var_abatements_all
where var_rent_inv_id=p_inv_id;
SELECT var_abatement_id , payment_term_id
FROM pn_var_abatements_all
WHERE var_rent_inv_id = -1
AND var_rent_id=p_var_rent_id;
SELECT var_rent_inv_id inv_id
FROM pn_var_rent_inv_all
WHERE invoice_date=p_inv_dt
AND var_rent_id =p_var_rent_id
AND period_id =p_prd_id
AND adjust_num=0;
SELECT org_id
FROM pn_var_rents_all
WHERE var_rent_id =p_var_rent_id;
UPDATE pn_var_abatements_all
SET var_rent_inv_id=l_inv_id
WHERE var_abatement_id = l_min_var_abt_id;
PN_VAR_ABATEMENTS_PKG.INSERT_ROW(
X_ROWID => l_row_id,
X_VAR_ABATEMENT_ID => l_var_abmt_id,
X_VAR_RENT_ID => p_var_rent_id,
X_VAR_RENT_INV_ID => l_inv_id,
X_PAYMENT_TERM_ID => l_pmt_term_id,
X_INCLUDE_TERM => abtmt_inv_rec.include_term,
X_INCLUDE_INCREASES => abtmt_inv_rec.include_increases,
X_UPDATE_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('USER_ID'),-1),
X_ORG_ID => l_org_id );
SELECT * from pn_var_abatements_all
WHERE var_rent_inv_id = p_inv_id
AND var_rent_id = p_var_rent_id
AND include_increases = 'Y';
SELECT ppt.payment_term_id rent_incr_term_id
FROM pn_index_lease_terms_all pilt,
pn_payment_terms_all ppt ,
pn_index_leases_all pil,
pn_var_rent_inv_all vinv
WHERE pilt.index_lease_id = pil.index_lease_id
AND pil.lease_id = ppt.lease_id
AND pilt.index_period_id = ppt.index_period_id
AND pilt.rent_increase_term_id = ppt.payment_term_id
AND ppt.start_date <= (SELECT MAX(gd.grp_end_date)
FROM pn_var_grp_dates_all gd
WHERE gd.period_id = vinv.period_id
)
AND ppt.end_date >= (SELECT MIN(gd1.grp_start_date)
FROM pn_var_grp_dates_all gd1
WHERE gd1.period_id = vinv.period_id
)
AND pilt.lease_term_id = p_term_id
AND ppt.status='APPROVED'
AND vinv.adjust_num = 0
AND vinv.var_rent_inv_id=p_inv_id;
SELECT 'N'
FROM dual
WHERE NOT EXISTS ( SELECT NULL FROM pn_var_abatements_all
WHERE payment_term_id=pmt_term_id
AND var_rent_inv_id=invoice_id);
SELECT org_id
FROM pn_var_rents_all
WHERE var_rent_id =p_var_rent_id;
SELECT var_rent_inv_id
FROM pn_var_rent_inv_all
WHERE var_rent_id=p_var_rent_id
AND period_id IN (SELECT period_id
FROM pn_var_periods_all
WHERE period_num=1
AND var_rent_id=p_var_rent_id
AND partial_period='Y')
AND adjust_num=0;
PN_VAR_ABATEMENTS_PKG.INSERT_ROW(
X_ROWID => l_row_id,
X_VAR_ABATEMENT_ID => l_var_abmt_id,
X_VAR_RENT_ID => p_var_rent_id,
X_VAR_RENT_INV_ID => l_inv_id,
X_PAYMENT_TERM_ID => rent_incr.rent_incr_term_id,
X_INCLUDE_TERM => 'Y',
X_INCLUDE_INCREASES => 'Y',
X_UPDATE_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('USER_ID'),-1),
X_ORG_ID => l_org_id );
SELECT * from pn_var_abatements_all
WHERE var_rent_inv_id = p_inv_id AND
var_rent_id = p_var_rent_id AND
include_increases = 'Y';
SELECT ppt.payment_term_id rent_incr_term_id
FROM pn_index_lease_terms_all pilt,
pn_payment_terms_all ppt ,
pn_index_leases_all pil,
pn_var_rent_inv_all vinv
WHERE pilt.index_lease_id = pil.index_lease_id
AND pil.lease_id = ppt.lease_id
AND pilt.index_period_id = ppt.index_period_id
AND pilt.rent_increase_term_id = ppt.payment_term_id
AND ppt.start_date <= (SELECT MAX(gd.grp_end_date)
FROM pn_var_grp_dates_all gd
WHERE gd.period_id = vinv.period_id
AND gd.invoice_date = vinv.invoice_date
)
AND ppt.end_date >= (SELECT MIN(gd1.grp_start_date)
FROM pn_var_grp_dates_all gd1
WHERE gd1.period_id = vinv.period_id
AND gd1.invoice_date = vinv.invoice_date
)
AND pilt.lease_term_id = p_term_id
AND ppt.status='APPROVED'
AND vinv.adjust_num = 0
AND vinv.var_rent_inv_id=p_inv_id;
SELECT 'N'
FROM dual
WHERE NOT EXISTS ( SELECT NULL FROM pn_var_abatements_all
WHERE payment_term_id=pmt_term_id
AND var_rent_inv_id=invoice_id);
SELECT var_rent_inv_id inv_id
FROM pn_var_rent_inv_all
WHERE invoice_date=p_inv_dt
AND period_id= p_prd_id
AND var_rent_id= p_var_rent_id
AND adjust_num=0;
SELECT org_id
FROM pn_var_rents_all
WHERE var_rent_id =p_var_rent_id;
PN_VAR_ABATEMENTS_PKG.INSERT_ROW(
X_ROWID => l_row_id,
X_VAR_ABATEMENT_ID => l_var_abmt_id,
X_VAR_RENT_ID => p_var_rent_id,
X_VAR_RENT_INV_ID => l_inv_id,
X_PAYMENT_TERM_ID => rent_incr.rent_incr_term_id,
X_INCLUDE_TERM => 'Y',
X_INCLUDE_INCREASES => 'Y',
X_UPDATE_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('USER_ID'),-1),
X_ORG_ID => l_org_id );
SELECT
line_item_id
FROM
pn_var_lines_all
WHERE
var_rent_id = p_vr_id AND
period_id = p_prd_id;
SELECT
grp.grp_date_id
,grp.group_date
,grp.invoice_date
,grp.org_id
FROM
pn_var_grp_dates_all grp
,pn_var_periods_all prd
WHERE
prd.var_rent_id = p_vr_id AND
prd.period_id = p_prd_id AND
grp.period_id = prd.period_id AND
grp.grp_end_date <= prd.end_date;
SELECT
NVL(SUM(hdr.percent_rent_due),0)
+ NVL(SUM(DECODE(hdr.invoice_flag
,'I',hdr.prorated_rent_due
,0
)
), 0) AS rent
,NVL(SUM(hdr.prorated_group_sales)
, 0) AS sales
,NVL(SUM(hdr.prorated_group_deductions)
, 0) AS ded
,NVL(SUM(hdr.first_yr_rent), 0) AS first_yr_rent
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 AND
hdr.grp_date_id = p_grp_id;
SELECT
var_rent_summ_id
,tot_act_vol
,tot_ded
,act_var_rent
,first_yr_rent
FROM
pn_var_rent_summ_all
WHERE
var_rent_id = p_vr_id AND
period_id = p_prd_id AND
line_item_id = p_line_id AND
grp_date_id = p_grp_id;
l_grp_t.DELETE;
UPDATE
pn_var_rent_summ_all
SET
tot_act_vol = summ_rec.sales
,tot_ded = summ_rec.ded
,act_var_rent = round(summ_rec.rent,g_precision)
,first_yr_rent = round(summ_rec.first_yr_rent,g_precision)
,last_update_date = SYSDATE
,last_updated_by = NVL(fnd_global.user_id, 0)
,last_update_login = NVL(fnd_global.login_id, 0)
WHERE
var_rent_summ_id = l_vr_summ_id;
pnp_debug_pkg.log('before insertion ...');
/* to insert a new summary record */
INSERT INTO
pn_var_rent_summ_all
(var_rent_summ_id
,var_rent_id
,period_id
,line_item_id
,invoice_date
,tot_act_vol
,tot_ded
,act_var_rent
,grp_date_id
,group_date
,last_update_date
,last_updated_by
,creation_date
,created_by
,last_update_login
,org_id
,first_yr_rent)
VALUES
(pn_var_rent_summ_s.NEXTVAL
,p_var_rent_id
,p_period_id
,line_rec.line_item_id
,l_grp_t(g).invoice_date
,summ_rec.sales
,summ_rec.ded
,round(summ_rec.rent,g_precision)
,l_grp_t(g).grp_date_id
,l_grp_t(g).group_date
,SYSDATE
,NVL(fnd_global.user_id, 0)
,SYSDATE
,NVL(fnd_global.user_id, 0)
,NVL(fnd_global.login_id, 0)
,NVL(l_grp_t(g).org_id, g_org_id)
,round(summ_rec.first_yr_rent,g_precision))
RETURNING
var_rent_summ_id
INTO
l_vr_summ_id;
UPDATE
pn_var_trx_headers_all hdr
SET
hdr.var_rent_summ_id = l_vr_summ_id
WHERE
hdr.var_rent_id = p_var_rent_id AND
hdr.period_id = p_period_id AND
hdr.line_item_id = line_rec.line_item_id AND
hdr.grp_date_id = l_grp_t(g).grp_date_id;
SELECT invoice_on
FROM pn_var_rents_all
WHERE var_rent_id = l_var_rent_id
AND invoice_on = 'FORECASTED';
SELECT sum(decode(adjust_num,0,
FOR_PER_RENT,0)) for_var_rent
FROM pn_var_rent_inv_all
WHERE period_id = l_period_id;
SELECT currency_code
FROM gl_sets_of_books
WHERE set_of_books_id = pn_mo_cache_utils.get_profile_value('PN_SET_OF_BOOKS_ID',p_org_ID);
SELECT var_rent_id,period_id,grp_date_id,calc_prd_start_date
FROM pn_var_trx_headers_All
WHERE trx_header_id=p_trx_hdr_id;
SELECT inv_start_date
FROM pn_var_grp_dates_all gd
WHERE gd.period_id = p_prd_id
AND gd.grp_date_id = p_grp_dt_id;
SELECT max(calc_prd_start_date) calc_prd_st_dt
FROM pn_var_trx_headers_all trx
WHERE trx.calc_prd_end_date <= p_inv_end_dt
AND trx.period_id = p_prd_id;
SELECT var_rent_id,period_id,grp_date_id,calc_prd_start_date
FROM pn_var_trx_headers_All
WHERE trx_header_id=p_trx_hdr_id;
SELECT invoice_date
FROM pn_var_grp_dates_all gd
WHERE gd.period_id = p_prd_id
AND gd.grp_date_id = p_grp_dt_id;
SELECT period_id, start_date
FROM pn_var_periods_all
WHERE var_rent_id = vr_id
AND partial_period = 'Y'
AND period_num = 1;
SELECT SUM(rent_inv.ACTUAL_INVOICED_AMOUNT) act_inv_amt
FROM pn_var_rent_inv_all rent_inv
WHERE rent_inv.period_id = p_prd_id
AND (rent_inv.invoice_date = p_inv_dt OR p_inv_dt IS NULL);
SELECT var_rent_id,period_id,grp_date_id,calc_prd_start_date
FROM pn_var_trx_headers_All
WHERE trx_header_id=p_trx_hdr_id;
SELECT invoice_date
FROM pn_var_grp_dates_all gd
WHERE gd.period_id = p_prd_id
AND gd.grp_date_id = p_grp_dt_id;
SELECT period_id, start_date
FROM pn_var_periods_all
WHERE var_rent_id = vr_id
AND partial_period = 'Y'
AND period_num = 1;
SELECT SUM(rent_inv.ACTUAL_INVOICED_AMOUNT) act_inv_amt
FROM pn_var_rent_inv_all rent_inv
WHERE rent_inv.period_id = p_prd_id
AND (rent_inv.invoice_date <= p_inv_dt OR p_inv_dt IS NULL);
SELECT 'y' AS include_flag
FROM DUAL
WHERE NOT EXISTS (SELECT NULL
FROM pn_var_rent_inv_all vinv
WHERE vinv.ACTUAL_INVOICED_AMOUNT <> 0
AND vinv.period_id = p_prd_id
);
PROCEDURE delete_draft_terms( p_var_rent_id IN NUMBER) IS
/* get term template id for variable rent */
CURSOR template_cur IS
SELECT term_template_id
FROM pn_var_rents_all
WHERE var_rent_id = p_var_rent_id;
SELECT
invoice_date, period_id
FROM
pn_var_rent_summ_all
WHERE
var_rent_id = p_vr_id
GROUP BY
invoice_date, period_id
ORDER BY
invoice_date;
SELECT inv.var_rent_inv_id
FROM
pn_var_rent_inv_all inv
WHERE
var_rent_id = p_vr_id AND
period_id = p_prd_id AND
invoice_date = p_inv_dt AND
EXISTS (SELECT term.payment_term_id
FROM pn_payment_terms_all term
WHERE term.var_rent_inv_id = inv.var_rent_inv_id
AND status = 'DRAFT'
AND (start_date <> p_inv_sch_date OR
term_template_id <> p_term_template_id));
SELECT payment_term_id, var_rent_inv_id
FROM pn_payment_terms_all
WHERE var_rent_inv_id IN (SELECT var_rent_inv_id
FROM pn_var_rent_inv_all
WHERE var_rent_id = p_var_rent_id)
AND status = 'DRAFT'
AND (start_date <> p_inv_sch_date OR
term_template_id <> p_term_template_id);*/
DELETE
pn_payment_terms_all
WHERE
var_rent_inv_id = rec.var_rent_inv_id
AND status = 'DRAFT'
AND (start_date <> l_payment_start_date OR
term_template_id <> l_term_template_id);
/* update the invoice */
/*Since forcasted rents are not yet exported, we can update them.*/
UPDATE
pn_var_rent_inv_all
SET
actual_term_status = 'N'
,last_update_date = SYSDATE
,last_updated_by = NVL(fnd_global.user_id,0)
,last_update_login = NVL(fnd_global.login_id,0)
WHERE
var_rent_inv_id = rec.var_rent_inv_id;
/* update the invoice */
/*Since forcasted rents are not yet exported, we can update them.*/
UPDATE
pn_var_rent_inv_all
SET
forecasted_term_status = 'N'
,last_update_date = SYSDATE
,last_updated_by = NVL(fnd_global.user_id,0)
,last_update_login = NVL(fnd_global.login_id,0)
WHERE
var_rent_inv_id = rec.var_rent_inv_id;
/* update the invoice */
/*Since forcasted rents are not yet exported, we can update them.*/
UPDATE
pn_var_rent_inv_all
SET
actual_term_status = 'N'
,last_update_date = SYSDATE
,last_updated_by = NVL(fnd_global.user_id,0)
,last_update_login = NVL(fnd_global.login_id,0)
WHERE
var_rent_inv_id = rec.var_rent_inv_id;
END delete_draft_terms;
SELECT sum(actual_invoiced_amount) AS actual_rent
FROM pn_var_rent_inv_all
WHERE period_id = p_period_id
AND invoice_date = p_invoice_date
AND var_rent_inv_id <= p_var_rent_inv_id
AND true_up_amt IS NULL;
SELECT constr_actual_rent
FROM pn_var_rent_inv_all
WHERE invoice_date = p_invoice_date
AND true_up_amt IS NOT NULL
AND var_rent_inv_id = p_var_rent_inv_id;
SELECT var_rent_id, proration_rule, termination_date
FROM pn_var_rents_all
WHERE var_rent_id = p_varRentId
AND proration_rule = 'FLY'
AND MONTHS_BETWEEN(commencement_date, termination_date) < 24;
SELECT count(*) period_num
FROM pn_var_periods_all
WHERE var_rent_id = p_vr_id
AND term_date > start_date;
SELECT period_id
FROM pn_var_periods_all
WHERE var_rent_id = p_vr_id
AND p_new_term_date BETWEEN start_date AND end_date
AND partial_period = 'Y';
SELECT
prd.period_id
,prd.partial_period
FROM
pn_var_periods_all prd,
pn_var_rents_all var
WHERE
prd.var_rent_id = p_vr_id AND
prd.var_rent_id = var.var_rent_id AND
prd.start_date = var.commencement_date AND
prd.partial_period = 'Y';
SELECT sum(NVL(tot_act_vol, 0)) yr_vol,
sum(NVL(tot_ded, 0)) yr_deductions
FROM pn_var_rent_summ_all
WHERE line_item_id = p_line_item_id;
SELECT
SUM(trueup_rent_due) AS trueup_rent
,MAX(calc_prd_end_date) AS trueup_date
,line_item_id
FROM
pn_var_trx_headers_all
WHERE
var_rent_id = p_var_rent_id AND
period_id = p_period_id
GROUP BY
line_item_id
ORDER BY
line_item_id;
SELECT grp_end_date
FROM pn_var_grp_dates_all
WHERE grp_date_id = p_grp_date_id;
SELECT period_num,var_rent_id
FROM pn_var_periods_all
WHERE period_id = p_period_id;
SELECT sum(pr_grp_blended_vol_start) bkpt_start,
sum(pr_grp_blended_vol_end) bkpt_end
FROM pn_var_trx_details_all dtls,
pn_var_trx_headers_all hdr
WHERE var_rent_id = p_var_rent_id
AND hdr.trx_header_id = dtls.trx_header_id
AND dtls.bkpt_rate = p_bkpt_rate
GROUP BY dtls.bkpt_rate;
SELECT variance_exp_code, actual_exp_code, adjust_num, actual_invoiced_amount, for_per_rent
FROM pn_var_rent_inv_all
WHERE period_id = p_period_id
AND var_rent_inv_id = p_var_rent_inv_id;
SELECT max(calc_prd_end_date) end_date
FROM pn_var_trx_headers_all
WHERE period_id = p_period_id
GROUP BY reset_group_id;
SELECT trueup_rent_due
FROM pn_var_trx_headers_all
WHERE trx_header_id = p_trx_hdr_id;