[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT 1 FROM DUAL WHERE EXISTS
(SELECT
trx_header_id
FROM
pn_var_trx_headers_all
WHERE
var_rent_id = p_vr_id AND
period_id = p_prd_id AND
reporting_group_sales IS NOT NULL);
SELECT
invoice_date
FROM
pn_var_grp_dates_all g,
pn_var_periods_all p
WHERE
g.var_rent_id = p_vr_id AND
g.period_id = p_prd_id AND
g.period_id = p.period_id AND
g.grp_end_date <= p.end_date
MINUS
SELECT
g.invoice_date
FROM
pn_var_trx_headers_all t,
pn_var_grp_dates_all g,
pn_var_periods_all p
WHERE
t.grp_date_id = g.grp_date_id AND
t.var_rent_id = p_vr_id AND
t.period_id = p_prd_id AND
t.period_id = p.period_id AND
g.grp_end_date <= p.end_date AND
t.reporting_group_sales IS NOT NULL;
SELECT
NVL(SUM(percent_rent_due),0) AS billed_rent
,NVL(SUM(trueup_rent_due),0) 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_vr_id AND
period_id = p_prd_id
GROUP BY
line_item_id
ORDER BY
line_item_id;
SELECT
NVL(SUM(percent_rent_due),0) AS billed_rent
,line_item_id
FROM
pn_var_periods_all prd,
pn_var_rents_all var,
pn_var_trx_headers_all trx
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
trx.var_rent_id = prd.var_rent_id AND
trx.period_id = prd.period_id
GROUP BY
trx.line_item_id
ORDER BY
trx.line_item_id;
SELECT
period_id
FROM
pn_var_periods_all prd
WHERE
prd.var_rent_id = p_vr_id AND
prd.period_num = 2;
UPDATE
pn_var_rent_summ_all
SET
trueup_var_rent = 0
WHERE
var_rent_id = p_var_rent_id AND
period_id = p_period_id AND
line_item_id = trueup_rec.line_item_id;
UPDATE
pn_var_rent_summ_all
SET
trueup_var_rent = round((trueup_rec.trueup_rent - l_billed_rent), g_precision)
WHERE
var_rent_id = p_var_rent_id AND
period_id = p_period_id AND
line_item_id = trueup_rec.line_item_id AND
grp_date_id = (SELECT
grp_date_id
FROM
pn_var_grp_dates_all
WHERE
period_id = p_period_id AND
grp_end_date = trueup_rec.trueup_date)
RETURNING
var_rent_summ_id
INTO
l_vr_summ_id;
PROCEDURE insert_invoice_trueup( p_var_rent_id IN NUMBER
,p_period_id IN NUMBER) IS
/* get invoice dates for a period */
CURSOR trueup_inv_dates_c( p_vr_id IN NUMBER
,p_prd_id IN NUMBER
,p_new_termn_date DATE) IS
SELECT
MAX(invoice_date) AS trueup_invoice_date
,NVL(SUM(tot_act_vol), 0) AS total_actual_sales
,NVL(SUM(trueup_var_rent), 0) AS total_trueup_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;
SELECT
var_rent_inv_id
,var_rent_id
,period_id
,invoice_date
,adjust_num
,true_up_status
,true_up_exp_code
,true_up_amt
,tot_act_vol
,act_per_rent
,actual_invoiced_amount
,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
/*true_up_amt <> 0 AND*/
true_up_status IS NOT NULL AND
true_up_exp_code IS NOT NULL
ORDER BY adjust_num DESC;
SELECT
NVL(SUM(act_var_rent), 0) AS total_period_rent
,NVL(SUM(trueup_var_rent), 0) AS total_trueup_rent
FROM
pn_var_rent_summ_all
WHERE
var_rent_id = p_vr_id AND
period_id = p_prd_id;
SELECT
NVL(SUM(actual_invoiced_amount), 0) AS prev_inv_trueup_amt
FROM
pn_var_rent_inv_all
WHERE
var_rent_id = p_vr_id AND
period_id = p_prd_id AND
(true_up_amt IS NULL OR true_up_exp_code = 'Y');
SELECT org_id, termination_date
FROM pn_var_rents_all
WHERE var_rent_id = p_vr_id;
pnp_debug_pkg.log('+++++++Insert_invoice_trueup++++++++');
/* invoice updateable? */
IF NVL(inv2upd_rec.true_up_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 => l_max_adjust_num
,x_invoice_date => inv_rec.trueup_invoice_date
,x_for_per_rent => NULL
,x_tot_act_vol => inv_rec.total_actual_sales
,x_act_per_rent => l_total_period_unabt_rent
,x_constr_actual_rent => l_total_period_unabt_rent
,x_abatement_appl => l_allowance
,x_rec_abatement => l_tot_abatement
,x_rec_abatement_override => l_rec_abatement_override
,x_negative_rent => 0
,x_actual_invoiced_amount => l_total_period_rent - l_prev_inv_trueup_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_true_up_amount => l_total_period_rent - l_prev_inv_trueup_rent
,x_true_up_status => 'N'
,x_true_up_exp_code => 'N'
,x_org_id => l_org_id );
/* 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(l_total_period_rent, l_precision) - round(l_prev_inv_trueup_rent, l_precision)) <> 0
OR round(l_total_period_rent, l_precision) <> round(l_tot_period_rent, l_precision)) THEN
/* create new invoice for difference amt */
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.trueup_invoice_date
,x_for_per_rent => NULL
,x_tot_act_vol => inv_rec.total_actual_sales
,x_act_per_rent => l_total_period_unabt_rent
,x_constr_actual_rent => l_total_period_unabt_rent
,x_abatement_appl => l_allowance
,x_rec_abatement => l_tot_abatement
,x_rec_abatement_override => l_rec_abatement_override
,x_negative_rent => 0
,x_actual_invoiced_amount => (l_total_period_rent - l_prev_inv_trueup_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_true_up_amount => (l_total_period_rent - l_prev_inv_trueup_rent)
,x_true_up_status => 'N'
,x_true_up_exp_code => 'N'
,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;
/* update the invoice */
UPDATE
pn_var_rent_inv_all
SET
act_per_rent = l_total_period_unabt_rent
,constr_actual_rent = l_total_period_unabt_rent
,actual_invoiced_amount = (l_total_period_rent - l_prev_inv_trueup_rent)
,true_up_amt = (l_total_period_rent - l_prev_inv_trueup_rent)
,tot_act_vol = ROUND(inv_rec.total_actual_sales, l_precision) -- bug # 6007571
,actual_term_status = 'N'
,abatement_appl = l_allowance
,rec_abatement = l_tot_abatement
,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;
END insert_invoice_trueup;
SELECT ORDER_OF_APPL_CODE, termination_date, org_id
FROM PN_VAR_RENTS_ALL abat
WHERE abat.var_rent_id = ip_var_rent_id;
SELECT * FROM
(SELECT var_rent_inv_id, constr_actual_rent, true_up_amt,
true_up_exp_code, invoice_date
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.period_id = inv2.period_id
AND inv1.invoice_date = inv2.invoice_date)
AND TRUE_UP_AMT IS NULL
ORDER BY INVOICE_DATE)
UNION ALL
SELECT
NULL as var_rent_inv_id
,(SELECT NVL((SUM(act_var_rent) + NVL(SUM(trueup_var_rent), 0) - NVL(SUM(first_yr_rent), 0)), 0)
FROM
pn_var_rent_summ_all
WHERE
var_rent_id = ip_var_rent_id AND
period_id = ip_period_id
) AS constr_actual_rent
,(SELECT NVL((SUM(act_var_rent) + NVL(SUM(trueup_var_rent), 0) - NVL(SUM(first_yr_rent), 0)), 0)
FROM
pn_var_rent_summ_all
WHERE
var_rent_id = ip_var_rent_id AND
period_id = ip_period_id
) AS true_up_amt
,'N' true_up_exp_code
, invoice_date
FROM
pn_var_rent_summ_all summ,
pn_var_periods_all per,
pn_var_rents_all vr
WHERE
summ.period_id = per.period_id AND
vr.var_rent_id = per.var_rent_id AND
(per.period_num <> 1 OR
vr.proration_rule NOT IN ('FY', 'FLY')) AND
summ.var_rent_id = ip_var_rent_id AND
summ.period_id = ip_period_id
AND summ.group_date = (select max(group_date) from pn_var_rent_summ_all
where period_id = ip_period_id)
AND G_IS_TU_CONC_FLAG = 'T'
UNION ALL
SELECT
NULL as var_rent_inv_id
,(SELECT NVL(SUM(first_yr_rent), 0)
FROM
pn_var_rent_summ_all summ,
pn_var_periods_all per
WHERE
per.period_id = summ.period_id AND
summ.var_rent_id = ip_var_rent_id AND
per.period_num = 2
)
+
(SELECT NVL(SUM(trueup_var_rent), 0)
FROM
pn_var_rent_summ_all
WHERE
var_rent_id = ip_var_rent_id AND
period_id = ip_period_id
)
as const_t
,(SELECT NVL(SUM(first_yr_rent), 0)
FROM
pn_var_rent_summ_all summ,
pn_var_periods_all per
WHERE
per.period_id = summ.period_id AND
summ.var_rent_id = ip_var_rent_id AND
per.period_num = 2
)
+
(SELECT NVL(SUM(trueup_var_rent), 0)
FROM
pn_var_rent_summ_all
WHERE
var_rent_id = ip_var_rent_id AND
period_id = ip_period_id
)
AS true_up_amt
,'N' true_up_exp_code
, invoice_date
FROM
pn_var_rent_summ_all summ,
pn_var_periods_all per,
pn_var_rents_all vr
WHERE
summ.period_id = per.period_id AND
vr.var_rent_id = per.var_rent_id AND
vr.proration_rule IN ('FY', 'FLY') AND
per.period_num = 1 AND
summ.var_rent_id = ip_var_rent_id AND
summ.period_id = ip_period_id
AND summ.group_date = (select max(group_date) from pn_var_rent_summ_all
where period_id = ip_period_id)
AND G_IS_TU_CONC_FLAG = 'T';
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')
AND
(NVL(true_up_amt, 0) = 0 AND
true_up_status IS NULL AND
true_up_exp_code IS NULL);
SELECT
NVL(SUM(actual_invoiced_amount), 0) AS prev_inv_trueup_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 true_up_exp_code = 'Y' OR variance_exp_code = 'Y') AND
/*true_up_amt <> 0 AND */
true_up_status IS NOT NULL AND
true_up_exp_code IS NOT NULL;
SELECT NVL(amount, 0) rolling_allow
,allowance_applied allow_applied
,start_date
,end_date
,abatement_id
FROM PN_VAR_ABAT_DEFAULTS_ALL
WHERE var_rent_id = ip_var_rent_id
AND type_code = pn_var_rent_calc_pkg.G_ABAT_TYPE_CODE_ALLO
ORDER BY start_date;
SELECT DECODE(invoice_on,
pn_var_rent_calc_pkg.G_INV_ON_ACTUAL, actual_exp_code,
pn_var_rent_calc_pkg.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;
SELECT SUM(actual_invoiced_amount) tot_nc_rent
FROM pn_var_rent_inv_all
WHERE var_rent_id = ip_var_rent_id
AND period_id = ip_period_id
AND true_up_amt IS NULL
AND true_up_status IS NULL
AND true_up_exp_code IS NULL;
SELECT *
FROM pn_var_rent_inv_all
WHERE var_rent_inv_id = ip_vr_inv_id;
/* update the invoice */
IF (p_flag = 'CALCULATE') THEN
IF (inv_rec.true_up_amt IS NULL) THEN
--Non true up invoice
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_precision) - round(l_prev_inv_exp,l_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 => round(inv_all_rec.act_per_rent,l_precision),
x_constr_actual_rent => round(inv_all_rec.constr_actual_rent,l_precision),
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 => (round(l_abated_rent,l_precision) - round(l_prev_inv_exp,l_precision)),
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 );
UPDATE
pn_var_rent_inv_all
SET
actual_invoiced_amount = (l_abated_rent - l_prev_inv_exp)
,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
,true_up_amt
,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.period_id = inv2.period_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 period_id = p_period_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 period_id = p_period_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 = l_negative_rent
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.period_id = inv2.period_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 = l_neg_rent_def
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
,true_up_amt
,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.period_id = inv2.period_id
AND inv1.invoice_date = inv2.invoice_date)
AND TRUE_UP_AMT IS NULL
UNION ALL
SELECT
(SELECT NVL(SUM(act_var_rent), 0)
FROM
pn_var_rent_summ_all
WHERE
var_rent_id = ip_var_rent_id AND
period_id = ip_period_id
) AS actual_invoiced_amount
, invoice_date
, null rec_abatement
, null rec_abatement_override
,(SELECT NVL(SUM(act_var_rent), 0)
FROM
pn_var_rent_summ_all
WHERE
var_rent_id = ip_var_rent_id AND
period_id = ip_period_id
) AS true_up_amt
,NULL AS VAR_RENT_INV_ID
FROM PN_VAR_RENT_SUMM_ALL
WHERE ip_inv_id IS NULL
AND var_rent_id = ip_var_rent_id
AND period_id = ip_period_id
AND group_date = (select max(group_date) from pn_var_rent_summ_all
where period_id = ip_period_id)
AND G_IS_TU_CONC_FLAG = 'T';
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)
AND inv.true_up_amt is NULL;
SELECT MIN(inv_start_date)inv_start_date,
MAX(inv_end_date) inv_end_date
FROM pn_var_grp_dates_all
WHERE var_rent_id = ip_var_rent_id
AND period_id = ip_period_id;
SELECT negative_rent
FROM pn_var_rents_all
WHERE var_rent_id = ip_var_rent_id;
SELECT REPTG_FREQ_CODE
FROM pn_var_rent_dates_all
WHERE var_rent_id = ip_var_rent_id;
SELECT COUNT(UNIQUE(invoice_date)) num_inv
FROM pn_var_rent_inv_all
WHERE var_rent_id = ip_var_rent_id
AND period_id = ip_period_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;
SELECT proration_rule
FROM pn_var_rents_all
WHERE var_rent_id = ip_var_rent_id;
SELECT SUM(rec_abatement) AS AMOUNT
FROM pn_var_rent_inv_all inv1
WHERE period_id = ip_period_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
AND true_up_amt IS NULL)
AND true_up_amt IS NULL;
SELECT rec_abatement_override
FROM pn_var_rent_inv_all
WHERE period_id = p_period_id
AND true_up_amt is NOT NULL
AND adjust_num = (SELECT max(adjust_num)
FROM PN_VAR_RENT_INV_ALL
WHERE period_id = p_period_id
AND TRUE_UP_AMT IS NOT NULL);
UPDATE pn_var_rent_inv_all
SET rec_abatement = l_total_abat
WHERE var_rent_inv_id = inv_rec.var_rent_inv_id;
SELECT inv1.abatement_appl
,inv1.invoice_date
,inv1.var_rent_inv_id
,inv1.true_up_amt
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.period_id = inv2.period_id
AND inv1.invoice_date = inv2.invoice_date)
AND TRUE_UP_AMT IS NULL
UNION ALL
SELECT
0 abatement_appl
, invoice_date
, null var_rent_inv_id
,(SELECT NVL(SUM(act_var_rent), 0)
FROM
pn_var_rent_summ_all
WHERE
var_rent_id = ip_var_rent_id AND
period_id = ip_period_id
) AS true_up_amt
FROM PN_VAR_RENT_SUMM_ALL
WHERE ip_inv_id IS NULL
AND var_rent_id = ip_var_rent_id
AND period_id = ip_period_id
AND group_date = (select max(group_date) from pn_var_rent_summ_all
where period_id = ip_period_id)
AND G_IS_TU_CONC_FLAG = 'T';
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_start_date
AND NVL(end_date, ip_inv_end_date) >= ip_inv_end_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 MIN(inv_start_date)inv_start_date,
MAX(inv_end_date) inv_end_date
FROM pn_var_grp_dates_all
WHERE var_rent_id = ip_var_rent_id
AND period_id = ip_period_id;
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 = l_allow_applied_inv
WHERE var_rent_inv_id = inv_rec.var_rent_inv_id;
UPDATE pn_var_abat_defaults_all
SET allowance_applied = l_allow_tu_t(i).allow_applied
WHERE abatement_id = l_allow_tu_t(i).abatement_id;
SELECT invoice_date
,true_up_amt
,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)
AND TRUE_UP_AMT IS NULL
UNION ALL
SELECT
invoice_date
,(SELECT NVL(SUM(act_var_rent), 0)
FROM
pn_var_rent_summ_all
WHERE
var_rent_id = ip_var_rent_id AND
period_id = ip_period_id
) AS true_up_amt
,null var_rent_inv_id
FROM PN_VAR_RENT_SUMM_ALL
WHERE ip_inv_id IS NULL
AND var_rent_id = ip_var_rent_id
AND period_id = ip_period_id
AND group_date = (select max(group_date) from pn_var_rent_summ_all
where period_id = ip_period_id)
AND G_IS_TU_CONC_FLAG = 'T';
SELECT MIN(grp_start_date) min_date
FROM pn_var_grp_dates_all
WHERE period_id = ip_period_id
AND var_rent_id = ip_var_rent_id;
SELECT MAX(grp_end_date) max_date
FROM pn_var_grp_dates_all
WHERE period_id = ip_period_id
AND var_rent_id = ip_var_rent_id;
SELECT var_rent_inv_id
FROM pn_var_rent_inv_all
WHERE period_id = ip_period_id
AND true_up_amt is NOT NULL
AND adjust_num = 0;
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
proration_rule
,cumulative_vol
,negative_rent
,commencement_date
,termination_date
,org_id
FROM
pn_var_rents_all
WHERE
var_rent_id = p_vr_id;
SELECT
org_id
,period_id
,start_date
,end_date
,partial_period
FROM
pn_var_periods_all
WHERE
var_rent_id = p_vr_id AND
end_date <= p_prd_date
AND NVL(status, pn_var_rent_calc_pkg.G_PERIOD_ACTIVE_STATUS)
<> pn_var_rent_calc_pkg.G_PERIOD_REVERSED_STATUS;
SELECT period_id
FROM pn_var_periods_all
WHERE var_rent_id = ip_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
line_item_id
FROM
pn_var_trx_headers_all
WHERE
var_rent_id = p_vr_id AND
period_id = p_prd_id
GROUP BY
line_item_id;
SELECT
MIN(calc_prd_start_date) AS trueup_start_date
,MAX(calc_prd_end_date) AS trueup_end_date
,reset_group_id
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
GROUP BY
reset_group_id
ORDER BY
trueup_start_date;
SELECT
ytd_sales
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
calc_prd_end_date = p_end_dt;
SELECT
ytd_group_vol_start AS trueup_bkpt_vol_start
,ytd_group_vol_end AS trueup_bkpt_vol_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
period_id = p_prd_id AND
line_item_id = p_line_id AND
reset_group_id = p_reset_grp_id AND
calc_prd_end_date = p_end_dt)
ORDER BY
trueup_bkpt_vol_start;
SELECT
bkhd.bkpt_header_id
,bkhd.breakpoint_type
FROM
pn_var_bkpts_head_all bkhd
WHERE
bkhd.line_item_id = p_line_id AND
bkhd_start_date <= p_end_dt AND
bkhd_end_date >= p_start_dt;
G_ABATEMENT_APPLIED.DELETE;
G_ALLOWANCE_APPLIED.DELETE;
G_ABATED_RENT.DELETE;
G_UNABATED_RENT.DELETE;
G_TOT_ABATEMENT.DELETE;
UPDATE
pn_var_trx_headers_all
SET
trueup_rent_due = 0
WHERE
var_rent_id = p_var_rent_id AND
period_id = prd_rec.period_id AND
line_item_id = line_rec.line_item_id;
trueup_bkpt_t.DELETE;
/* update the line trueup rent */
l_line_trueup_rent := l_line_trueup_rent + l_part_trueup_rent;
UPDATE
pn_var_trx_headers_all
SET
trueup_rent_due = round(l_line_trueup_rent,g_precision)
WHERE
var_rent_id = p_var_rent_id AND
period_id = prd_rec.period_id AND
line_item_id = line_rec.line_item_id AND
calc_prd_end_date = prd_rec.end_date;
pn_var_trueup_pkg.insert_invoice_trueup
( p_var_rent_id => p_var_rent_id
,p_period_id => prd_rec.period_id);
SELECT
vr.var_rent_id
FROM
pn_leases_all lease
,pn_var_rents_all vr
WHERE
vr.cumulative_vol = 'T' AND
vr.lease_id = lease.lease_id AND
lease.lease_num BETWEEN l_lease_num_low AND l_lease_num_high AND
vr.rent_num BETWEEN l_vr_num_low AND l_vr_num_high
ORDER BY
vr.rent_num;
SELECT
vr.var_rent_id
FROM
pn_leases_all lease
,pn_var_rents_all vr
WHERE
vr.cumulative_vol = pn_var_rent_calc_pkg.G_CALC_TRUE_UP AND
vr.lease_id = lease.lease_id AND
lease.lease_num BETWEEN l_lease_num_low AND l_lease_num_high AND
vr.rent_num BETWEEN l_vr_num_low AND l_vr_num_high AND
vr.location_id IN
(SELECT
location_id
FROM
pn_locations_all
START WITH location_id = p_building_id
CONNECT BY PRIOR location_id = parent_location_id)
ORDER BY
vr.rent_num;
SELECT DISTINCT
loc.location_id
FROM
pn_locations_all loc
,pn_properties_all prop
WHERE
loc.property_id = prop.property_id AND
prop.property_code = p_prop_code;
SELECT MAX(lease_num)
INTO l_lease_num_high
FROM pn_leases;
SELECT MAX(rent_num)
INTO l_vr_num_high
FROM pn_var_rents;