[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT SUM(vh.actual_amount) actual_amt,
vh.grp_date_id
FROM pn_var_vol_hist_all vh,
pn_var_grp_dates_all gd
WHERE vh.line_item_id = p_line_item_id
AND vh.grp_date_id = gd.grp_date_id
AND vh.period_id = gd.period_id
AND NVL(gd.actual_exp_code,'N') = 'N'
AND gd.period_id = p_period_id
AND vh.actual_amount is not null
AND gd.grp_end_date <= p_period_date
GROUP BY vh.grp_date_id, vh.group_date
ORDER BY vh.group_date;
SELECT SUM(vh.forecasted_amount) forecasted_amt,
vh.grp_date_id
FROM pn_var_vol_hist_all vh,
pn_var_grp_dates_all gd
WHERE vh.line_item_id = p_line_item_id
AND vh.grp_date_id = gd.grp_date_id
AND vh.period_id = gd.period_id
AND NVL(gd.forecasted_exp_code,'N') = 'N'
AND gd.period_id = p_period_id
AND vh.forecasted_amount is not null
AND gd.grp_end_date <= p_period_date
GROUP BY vh.grp_date_id, vh.group_date
ORDER BY vh.group_date;
SELECT SUM(vh.actual_amount) actual_amt,
vh.grp_date_id,
vh.line_item_id
FROM pn_var_vol_hist_all vh,
pn_var_grp_dates_all gd
WHERE vh.grp_date_id = gd.grp_date_id
AND vh.period_id = gd.period_id
AND gd.period_id = p_period_id
AND vh.line_item_id = p_line_item_id
AND gd.invoice_date = p_invoice_date
AND NVL(gd.variance_exp_code,'N') = 'N'
AND NVL(gd.forecasted_exp_code,'N') = 'Y'
AND vh.actual_amount is not null
AND gd.grp_end_date <= p_period_date
GROUP BY vh.line_item_id, vh.grp_date_id, vh.group_date
ORDER BY vh.line_item_id, vh.group_date;
SELECT SUM(vh.actual_amount) actual_amt,
vh.grp_date_id,
vh.line_item_id
FROM pn_var_vol_hist_all vh,
pn_var_grp_dates_all gd
WHERE vh.grp_date_id = gd.grp_date_id
AND vh.period_id = gd.period_id
AND DECODE(p_invoice_on,'ACTUAL',NVL(gd.actual_exp_code,'N'),
'FORECASTED',NVL(gd.variance_exp_code,'N')) = 'Y'
AND gd.period_id = p_period_id
AND vh.line_item_id = p_line_item_id
AND gd.group_date >= p_min_group_date
GROUP BY vh.line_item_id, vh.grp_date_id, vh.group_date
ORDER BY vh.line_item_id, vh.group_date;
SELECT det.period_bkpt_vol_start,
det.period_bkpt_vol_end,
det.group_bkpt_vol_start,
det.group_bkpt_vol_end,
det.bkpt_rate,
head.breakpoint_TYPE,
head.line_item_id
FROM pn_var_bkpts_head_all head,
pn_var_bkpts_det_all det
WHERE det.bkpt_header_id = head.bkpt_header_id
AND head.line_item_id = p_line_item_id
ORDER BY det.period_bkpt_vol_start;
SELECT SUM(actual_amount) cum_actual_vol,
SUM(forecasted_amount) cum_for_vol,
line_item_id,
grp_date_id,
group_date
FROM pn_var_vol_hist_all
WHERE line_item_id = p_line_item_id
GROUP BY line_item_id,grp_date_id,group_date
ORDER BY line_item_id,group_date;
SELECT grp_date_id,
proration_factor,
invoice_date,
group_date,
grp_start_date,
grp_end_date
FROM pn_var_grp_dates_all
WHERE period_id = p_period_id;
SELECT line_item_id,
grp_date_id,
SUM(deduction_amount) deduction_amt
FROM pn_var_deductions_all
WHERE line_item_id = p_line_item_id
GROUP BY line_item_id,grp_date_id;
SELECT lines.line_item_num,
lines.period_id,
lines.line_item_id
FROM pn_var_periods_all per,
pn_var_lines_all lines
WHERE lines.period_id= per.period_id
AND per.var_rent_id = p_var_rent_id
AND per.period_id = p_period_id
AND lines.line_item_id = NVL(ip_line_item_id,lines.line_item_id);
/* intialize the global variables for later use when inserting/updating into
pn_var_rent_SUMm */
g_var_rent_id := p_var_rent_id;
invoice_tbl.delete;
/* Insert/Update the invoices for the period */
Insert_invoice(p_calc_TYPE => p_calc_TYPE,
p_period_id => p_period_id,
p_var_rent_id => p_var_rent_id);
SELECT MIN(gd.group_date) min_group_date
FROM pn_var_vol_hist_all vh,
pn_var_grp_dates_all gd
WHERE vh.grp_date_id = gd.grp_date_id
AND vh.period_id = gd.period_id
AND DECODE(p_invoice_on,'ACTUAL',NVL(gd.actual_exp_code,'N'),
'FORECASTED',NVL(gd.variance_exp_code,'N')) = 'Y'
AND gd.period_id = p_period_id
AND vh.line_item_id = p_line_item_id
AND exists (SELECT null
FROM pn_var_vol_hist_all vh1
WHERE vh1.period_id = gd.period_id
AND vh1.grp_date_id = gd.grp_date_id
AND DECODE(p_invoice_on,'ACTUAL' ,NVL( vh1.actual_exp_code,'N'),
'FORECASTED',NVL( vh1.variance_exp_code,'N')
) = 'N'
AND vh1.line_item_id = vh.line_item_id
AND vh1.actual_amount is not null);
/* Insert/Update pn_var_rent_SUM_all */
process_rent(P_VAR_RENT_ID => g_var_rent_id,
P_PERIOD_ID => g_period_id ,
P_LINE_ITEM_ID => p_line_item_id,
P_INVOICE_DATE => g_invoice_date,
P_GROUP_DATE => l_group_date,
P_TOT_VOL => l_volume ,
P_TOT_DED => l_tot_ded,
P_VAR_RENT => ROUND(l_variable_rent,g_precision),
P_GRP_DATE_ID => p_grp_date_id,
P_CALC_TYPE => p_calc_TYPE,
P_CUMULATIVE => p_cumulative);
SELECT NVL(decode(g_rent_TYPE,'FORECASTED',SUM(for_var_rent),SUM(act_var_rent)),0)
FROM pn_var_rent_summ_all
WHERE line_item_id = p_line_item_id
AND group_date < p_group_date;
/* insert into PL/SQL table invoice_tbl all the invoices dates
for the period for which rent has been calculated. Info
needed to update pn_var_Rent_inv table for the new amounts */
FOR i in 1 .. invoice_tbl.COUNT
LOOP
if invoice_tbl(i).invoice_date = p_invoice_date AND
invoice_tbl(i).period_id = p_period_id then
l_invoice_date := invoice_tbl(i).invoice_date;
select pn_var_rent_SUMm_s.nextval
into l_var_rent_SUMm_id
from dual;
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_FOR_VOL
,TOT_DED
,ACT_VAR_RENT
,FOR_VAR_RENT
,GRP_DATE_ID
,GROUP_DATE
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_LOGIN
,ORG_ID)
VALUES
(L_VAR_RENT_SUMM_ID
,P_VAR_RENT_ID
,P_PERIOD_ID
,P_LINE_ITEM_ID
,P_INVOICE_DATE
,L_TOT_ACT_VOL
,L_TOT_FOR_VOL
,P_TOT_DED
,round(L_ACT_VAR_RENT,g_precision)
,round(L_FOR_VAR_RENT,g_precision)
,P_GRP_DATE_ID
,P_GROUP_DATE
,SYSDATE
,NVL(fnd_profile.value('USER_ID'),0)
,SYSDATE
,NVL(fnd_profile.value('USER_ID'),0)
,NVL(fnd_profile.value('LOGIN_ID'),0)
,g_org_id);
/* update pn_var_rent_SUMm for the combination of line_item_id AND grp_date_id*/
UPDATE pn_var_rent_SUMm_all
SET tot_act_vol = decode(g_rent_TYPE,'ACTUAL',p_tot_vol,tot_act_vol),
tot_ded = decode(g_rent_TYPE,'ACTUAL',p_tot_ded,tot_ded),
act_var_rent = decode(g_rent_TYPE,'ACTUAL',round(l_var_rent,g_precision),
round(act_var_rent,g_precision)),
tot_for_vol = decode(g_rent_TYPE,'FORECASTED',p_tot_vol,tot_for_vol),
for_var_rent = decode(g_rent_TYPE,'FORECASTED',round(l_var_rent,g_precision),
round(act_var_rent,g_precision)),
last_update_date = SYSDATE,
last_updated_by = NVL(fnd_profile.value('USER_ID'),0),
last_update_login = NVL(fnd_profile.value('LOGIN_ID'),0)
WHERE line_item_id = p_line_item_id
AND grp_date_id = p_grp_date_id;
PROCEDURE Insert_invoice(p_calc_TYPE IN VARCHAR2,
p_period_id IN NUMBER,
p_var_rent_id IN NUMBER)
IS
CURSOR csr_get_rent(ip_period_id NUMBER,
ip_invoice_date DATE) is
SELECT ROUND(SUM(act_var_rent),g_precision) actual_rent,
ROUND(SUM(for_var_rent),g_precision) for_rent,
ROUND( decode( SUM(act_var_rent),null,SUM(act_var_rent),
apply_constraints(ip_period_id,SUM(act_var_rent))
),g_precision
)constr_act_rent,
(SUM(tot_act_vol) - SUM(tot_ded)) actual_volume
FROM pn_var_rent_summ_all
WHERE period_id= ip_period_id
AND invoice_date = ip_invoice_date;
SELECT inv_rent.rowid,
inv_rent.var_rent_inv_id,
inv_rent.adjust_num,
inv_rent.for_per_rent,
inv_rent.abatement_appl,
inv_rent.negative_rent,
inv_rent.rec_abatement,
inv_rent.rec_abatement_override,
inv_rent.forecasted_term_status,
inv_rent.forecasted_exp_code,
inv_rent.actual_exp_code,
inv_rent.variance_exp_code
FROM pn_var_rent_inv_all inv_rent
WHERE inv_rent.period_id = ip_period_id
AND inv_rent.invoice_date = ip_invoice_date
AND inv_rent.adjust_num = (SELECT MAX(inv.adjust_num)
FROM pn_var_rent_inv_all inv
WHERE inv.invoice_date = inv_rent.invoice_date
AND inv.period_id = inv_rent.period_id);
SELECT (summ.tot_act_vol - NVL(summ.tot_ded,0)) cum_act_vol
FROM pn_var_rent_summ_all summ
WHERE summ.group_date = (SELECT MAX(summ1.group_date)
FROM pn_var_rent_summ_all summ1
WHERE summ1.invoice_date = ip_invoice_date
AND summ1.period_id = ip_period_id)
AND summ.period_id = ip_period_id;
l_insert BOOLEAN := FALSE;
pnp_debug_pkg.log('pn_variable_amount_pkg.Insert_Invoice (+) ');
/* Insert/Update pn_var_rent_inv only for those invoice dates for
which calculation has been done by checking if that invoice
date AND period id exists in invoice_tbl. This table has been populated
in procedure process_rent */
FOR i in 1.. invoice_tbl.COUNT
LOOP
open csr_get_rent (invoice_tbl(i).period_id, invoice_tbl(i).invoice_date);
l_insert := TRUE;
l_insert := FALSE;
l_insert := TRUE;
l_insert := FALSE;
IF l_insert THEN
pnp_debug_pkg.log('Insert_Invoice - inserting into pn_var_rent_inv');
PN_VAR_RENT_INV_PKG.INSERT_ROW (
X_ROWID => l_rowid_out,
X_VAR_RENT_INV_ID => l_rent_inv_id,
X_ADJUST_NUM => l_adjust_num,
X_INVOICE_DATE => invoice_tbl(i).invoice_date,
X_FOR_PER_RENT => l_forecast_rent,
X_TOT_ACT_VOL => l_actual_volume,
X_ACT_PER_RENT => l_actual_rent,
X_CONSTR_ACTUAL_RENT => l_constr_act_rent,
X_ABATEMENT_APPL => l_abt_appl,
X_REC_ABATEMENT => l_rec_abatement,
X_REC_ABATEMENT_OVERRIDE => l_rec_abatement_override,
X_NEGATIVE_RENT => l_negative_rent,
X_ACTUAL_INVOICED_AMOUNT => l_actual_invoiced_amt,
X_PERIOD_ID => invoice_tbl(i).period_id,
X_VAR_RENT_ID => p_var_rent_id,
X_FORECASTED_TERM_STATUS => l_for_term_status,
X_VARIANCE_TERM_STATUS => 'N',
X_ACTUAL_TERM_STATUS => 'N',
X_FORECASTED_EXP_CODE => l_for_exp_code,
X_VARIANCE_EXP_CODE => 'N',
X_ACTUAL_EXP_CODE => 'N',
X_COMMENTS => null,
X_ATTRIBUTE_CATEGORY => null,
X_ATTRIBUTE1 => null,
X_ATTRIBUTE2 => null,
X_ATTRIBUTE3 => null,
X_ATTRIBUTE4 => null,
X_ATTRIBUTE5 => null,
X_ATTRIBUTE6 => null,
X_ATTRIBUTE7 => null,
X_ATTRIBUTE8 => null,
X_ATTRIBUTE9 => null,
X_ATTRIBUTE10 => null,
X_ATTRIBUTE11 => null,
X_ATTRIBUTE12 => null,
X_ATTRIBUTE13 => null,
X_ATTRIBUTE14 => null,
X_ATTRIBUTE15 => null,
X_CREATION_DATE => SYSDATE,
X_CREATED_BY => NVL(fnd_profile.value('USER_ID'),0),
X_LAST_UPDATE_DATE => SYSDATE,
X_LAST_UPDATED_BY => NVL(fnd_profile.value('USER_ID'),0),
X_LAST_UPDATE_LOGIN => NVL(fnd_profile.value('LOGIN_ID'),0),
X_ORG_ID => g_org_id );
pnp_debug_pkg.log('Insert_Invoice - Updating PN_VAR_RENT_INV ');
/* Delete payment terms from pn_payment_terms created
for the combination of var_rent_inv_id AND rent_TYPE
that are in the draft status since we are recalculating
AND updating the invoice for forecasted rent*/
DELETE from pn_payment_terms_all
WHERE var_rent_inv_id = l_var_rent_inv_id
AND status <> c_payment_term_status_approved
AND var_rent_TYPE = l_rent_TYPE;
UPDATE pn_var_rent_inv_all
SET for_per_rent = l_forecast_rent,
act_per_rent = l_actual_rent,
constr_actual_rent = l_constr_act_rent,
tot_act_vol = ROUND(l_actual_volume,g_precision), -- bug # 6007571
forecasted_term_status = decode(l_rent_TYPE,'FORECASTED','N',forecasted_term_status),
variance_term_status = decode(l_rent_TYPE,'VARIANCE','N',variance_term_status),
actual_term_status = decode(l_rent_TYPE,'ACTUAL','N',actual_term_status),
last_update_date = SYSDATE,
last_updated_by = NVL(fnd_profile.value('USER_ID'),0),
last_update_login = NVL(fnd_profile.value('LOGIN_ID'),0)
WHERE rowid = l_rowid;
pnp_debug_pkg.log('pn_variable_amount_pkg.Insert_Invoice (-)');
pnp_debug_pkg.log('Error in pn_variable_amount_pkg.Insert_invoice :'||TO_CHAR(sqlcode)||' : '||sqlerrm);
END Insert_invoice;
SELECT inv.rowid,
inv.adjust_num,
inv.var_rent_inv_id,
inv.constr_actual_rent,
inv.actual_invoiced_amount,
inv.abatement_appl,
inv.negative_rent,
inv.rec_abatement,
inv.rec_abatement_override,
inv.invoice_date
FROM pn_var_rent_inv_all inv
WHERE inv.var_rent_id = p_var_rent_id
AND inv.constr_actual_rent is not null
AND decode(g_invoice_on,'ACTUAL',inv.actual_exp_code,'FORECASTED',inv.variance_exp_code)='N'
ORDER BY inv.period_id,inv.invoice_date,inv.adjust_num;
SELECT NVL(abatement_amount,0),
negative_rent
FROM pn_var_rents_all
WHERE var_rent_id = ip_var_rent_id;
SELECT SUM(inv.abatement_appl)
FROM pn_var_rent_inv_all inv
WHERE inv.adjust_num =(SELECT MAX(inv1.adjust_num)
FROM pn_var_rent_inv_all inv1
WHERE inv1.invoice_date = inv.invoice_date
AND inv1.var_rent_id = inv.var_rent_id
AND decode(g_invoice_on,'ACTUAL',inv1.actual_exp_code,
'FORECASTED',inv1.variance_exp_code)='Y')
AND inv.var_rent_id = ip_var_rent_id;
SELECT MAX(invoice_date)
FROM pn_var_grp_dates_all
WHERE var_rent_id = p_var_rent_id;
SELECT ABS(NVL(SUM(constr_actual_rent),0)) l_negative_avialable
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)
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(actual_invoiced_amount),0)
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 < ip_adjust_num;
DELETE from pn_payment_terms_all
WHERE status <> c_payment_term_status_approved
AND var_rent_inv_id = rec_get_inv.var_rent_inv_id
AND var_rent_TYPE = decode(g_invoice_on,'ACTUAL','ACTUAL','FORECASTED','VARIANCE');
UPDATE pn_var_rent_inv_all
SET abatement_appl = l_abatement_applied,
actual_invoiced_amount = l_actual_invoice_amt,
negative_rent = l_negative_rent,
actual_term_status = 'N',
variance_term_status = 'N',
last_update_date = SYSDATE,
last_updated_by = NVL(fnd_profile.value('USER_ID'),0),
last_update_login = NVL(fnd_profile.value('LOGIN_ID'),0)
WHERE rowid = rec_get_inv.rowid;
SELECT TYPE_code,
amount
FROM pn_var_constraints_all
WHERE period_id = p_period_id
AND constr_cat_code = 'VARENT';
SELECT 'Y'
INTO l_varrent_exists
FROM dual
WHERE EXISTS (SELECT null
FROM pn_var_rent_summ_all
WHERE line_item_id = p_line_item_id
AND grp_date_id = p_grp_date_id);
SELECT distinct actual_exp_code,forecasted_exp_code,variance_exp_code
INTO p_actual_flag,p_forecasted_flag,p_variance_flag
FROM pn_var_grp_dates_all
WHERE period_id = p_period_id
AND invoice_date = p_invoice_date;
grd_date_tbl.delete;
deduction_tbl.delete;
bkpt_range_tbl.delete;
cum_vol_tbl.delete;
SELECT cumulative_vol,
invoice_on,
negative_rent
INTO p_cumulative,
p_invoice_on,
p_negative_rent
FROM pn_var_rents_all
WHERE var_rent_id = p_var_rent_id;
SELECT 'Y'
INTO l_term_exists
FROM DUAL
WHERE exists ( SELECT null
FROM pn_payment_terms_all
WHERE var_rent_inv_id = p_var_rent_inv_id
AND var_rent_TYPE IN ('ACTUAL', 'VARIANCE'));
SELECT 'Y'
INTO l_term_exists
FROM DUAL
WHERE exists ( SELECT null
FROM pn_payment_terms_all
WHERE var_rent_inv_id = p_var_rent_inv_id
AND var_rent_TYPE = p_var_rent_TYPE);
SELECT 'Y'
INTO l_volume_exists
FROM dual
WHERE not exists (SELECT null
FROM pn_var_rent_summ_all summ,
(SELECT gd.period_id,
lines.line_item_id,
gd.grp_date_id
FROM pn_var_lines_all lines,
pn_var_grp_dates_all gd
WHERE gd.period_id = lines.period_id
AND gd.period_id= p_period_id
AND gd.invoice_date =p_invoice_date) itemp
WHERE SUMm.grp_date_id = itemp.grp_date_id
AND SUMm.line_item_id = itemp.line_item_id
GROUP by itemp.period_id,itemp.line_item_id,itemp.grp_date_id
HAVING ((SUM(SUMm.tot_act_vol) is null AND p_var_rent_TYPE = 'ACTUAL') OR
(SUM(SUMm.tot_for_vol) is null AND p_var_rent_TYPE = 'FORECASTED'))
);
SELECT 'Y' term_exists
FROM dual
WHERE EXISTS (SELECT null
FROM pn_var_rent_inv_all inv
WHERE inv.forecasted_exp_code = decode(p_var_rent_TYPE,'FORECASTED','N',inv.forecasted_exp_code)
AND inv.actual_exp_code = decode(p_var_rent_TYPE,'ACTUAL','N',inv.actual_exp_code)
AND inv.variance_exp_code = decode(p_var_rent_TYPE,'VARIANCE','N',inv.variance_exp_code)
AND inv.invoice_date < ( SELECT inv1.invoice_date
FROM pn_var_rent_inv_all inv1
WHERE inv1.var_rent_inv_id = p_var_rent_inv_id)
AND inv.period_id = nvl(p_period_id,inv.period_id)
AND inv.var_rent_id = p_var_rent_id);
SELECT 'Y' term_exists
FROM dual
WHERE EXISTS (SELECT null
FROM pn_var_rent_inv_all inv
WHERE inv.actual_exp_code = 'N'
AND inv.forecasted_exp_code = 'N'
AND inv.variance_exp_code = 'N'
AND inv.invoice_date < ( SELECT inv1.invoice_date
FROM pn_var_rent_inv_all inv1
WHERE inv1.var_rent_inv_id = p_var_rent_inv_id)
AND inv.period_id = nvl(p_period_id,inv.period_id)
AND inv.var_rent_id = p_var_rent_id);
SELECT pvp.period_id
FROM pn_var_rents_all pvr , pn_var_periods_all pvp
WHERE pvr.var_rent_id = p_var_rent_id
AND pvr.var_rent_id = pvp.var_rent_id
AND proration_rule IN ('FY', 'FLY')
AND pvp.start_date = pvr.commencement_date;
SELECT period_id
FROM pn_var_rent_inv_all
WHERE var_rent_inv_id = p_var_rent_inv_id;
SELECT SUM(actual_invoiced_amount)
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 < ip_adjust_num;
SELECT summ.tot_act_vol,
summ.tot_for_vol,
summ.tot_ded,
decode(vrent.cumulative_vol ,'N','N','T','N','Y')
FROM pn_var_rent_summ_all summ,
pn_var_rents_all vrent
WHERE summ.line_item_id = p_line_item_id
AND summ.group_date = p_group_date
AND summ.var_rent_id = vrent.var_rent_id;
SELECT nvl(summ.tot_act_vol,0),
nvl(summ.tot_for_vol,0),
nvl(summ.tot_ded,0)
FROM pn_var_rent_summ_all summ
WHERE summ.group_date =(SELECT max(summ1.group_date)
FROM pn_var_rent_summ_all summ1
WHERE summ1.group_date < p_group_date
AND summ1.line_item_id = p_line_item_id)
AND summ.line_item_id = p_line_item_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 = p_org_id or p_org_id is null)
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 = p_org_id or p_org_id is null)
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 distinct invoice_date
FROM pn_var_grp_dates_all
WHERE period_id = ip_period_id
AND invoice_date = NVL(p_invoice_date,invoice_date)
ORDER BY invoice_date;
SELECT currency_code,
set_of_books_id
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 'Y'
FROM dual
WHERE exists (SELECT null
FROM pn_var_vol_hist_all
WHERE line_item_id = ip_line_item_id
AND grp_date_id = ip_grp_date_id);
SELECT 'Y'
FROM dual
WHERE exists (SELECT null
FROM pn_var_vol_hist_all vh,
pn_var_grp_dates_all gd
WHERE vh.period_id = gd.period_id
AND vh.grp_date_id = gd.grp_date_id
AND gd.period_id = ip_period_id
AND gd.invoice_date = ip_invoice_date
AND vh.variance_exp_code = decode(ip_rent_TYPE,'VARIANCE','N',vh.variance_exp_code)
AND vh.forecasted_exp_code = decode(ip_rent_TYPE,'FORECASTED','N',vh.forecasted_exp_code)
AND vh.actual_exp_code = decode(ip_rent_TYPE,'ACTUAL','N',vh.actual_exp_code)
AND ((ip_rent_TYPE = 'VARIANCE' AND vh.actual_amount is not null) OR
ip_rent_TYPE in('FORECASTED','ACTUAL'))
);
SELECT inv.adjust_num,
inv.forecasted_exp_code,
inv.variance_exp_code,
inv.rowid,
inv.var_rent_inv_id
FROM pn_var_rent_inv_all inv
WHERE inv.period_id = ip_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.period_id = ip_period_id
AND inv1.invoice_date = ip_invoice_date);
l_delete BOOLEAN := FALSE;
/* Delete from pn_var_rent_SUMm */
IF l_any_vol_exists = 'N' THEN
DELETE from pn_var_rent_SUMm_all
WHERE grp_date_id = p_grp_date_id
AND line_item_id = p_line_item_id;
l_delete := FALSE;
l_delete := TRUE;
l_delete := TRUE;
l_delete := TRUE;
l_delete := TRUE;
IF l_delete THEN
/* Does any volume history exist for the invoice date,period AND rent TYPE */
OPEN csr_vol_exists(p_period_id,p_invoice_date,l_rent_TYPE);
/* Delete from pn_payment_terms if a payment term exists */
DELETE from pn_payment_terms_all
WHERE var_rent_inv_id = l_var_rent_inv_id
AND var_rent_TYPE = l_rent_TYPE
AND status <> c_payment_term_status_approved;
/* Delete from pn_var_rent_inv */
DELETE from pn_var_rent_inv_all
WHERE rowid = l_rowid;
SELECT actual_exp_code,forecasted_exp_code,variance_exp_code
FROM pn_var_rent_inv_all
WHERE period_id = ip_period_id
AND invoice_date = ip_invoice_date
AND true_up_amt IS NULL
AND adjust_num = (select max(adjust_num) FROM pn_var_rent_inv_all
WHERE period_id = ip_period_id
AND invoice_date = ip_invoice_date
AND true_up_amt IS NULL -- Bug # 5991106
);
SELECT actual_exp_code,forecasted_exp_code,variance_exp_code
FROM pn_var_rent_inv_all
WHERE period_id = ip_period_id
AND invoice_date = ip_invoice_date
AND true_up_amt IS NOT NULL
AND adjust_num = (select max(adjust_num) FROM pn_var_rent_inv_all
WHERE period_id = ip_period_id
AND invoice_date = ip_invoice_date
AND true_up_amt IS NOT NULL -- Bug # 5991106
);