eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.PN_VAR_RENT_INV_V

Source

View Text - Preformatted


SELECT "ROW_ID","VAR_RENT_INV_ID","ADJUST_NUM","VAR_RENT_ID","PERIOD_ID","INVOICE_DATE","TOT_ACT_VOL","ACT_PER_RENT","CONSTR_ACTUAL_RENT","FOR_PER_RENT","CUMULATIVE_FOR_RENT","ACTUAL_FORECASTED_RENT","CUMULATIVE_ACT_FOR_RENT","FORECASTED_TERM_STATUS","ABATEMENT_APPL","REC_ABATEMENT","REC_ABATEMENT_OVERRIDE","NEGATIVE_RENT","PREV_INVOICED_AMOUNT","TOT_VAR_RENT","ACTUAL_INVOICED_AMOUNT","PRES_INVOICED_AMOUNT","CUMULATIVE_ACT_RENT","ACTUAL_TERM_STATUS","VARIANCE_TERM_STATUS","ACTUAL_EXP_CODE","FORECASTED_EXP_CODE","VARIANCE_EXP_CODE","COMMENTS","ATTRIBUTE_CATEGORY","ATTRIBUTE1","ATTRIBUTE2","ATTRIBUTE3","ATTRIBUTE4","ATTRIBUTE5","ATTRIBUTE6","ATTRIBUTE7","ATTRIBUTE8","ATTRIBUTE9","ATTRIBUTE10","ATTRIBUTE11","ATTRIBUTE12","ATTRIBUTE13","ATTRIBUTE14","ATTRIBUTE15","CREATION_DATE","CREATED_BY","LAST_UPDATE_DATE","LAST_UPDATED_BY","LAST_UPDATE_LOGIN","INV_SCHEDULE_DATE","ORG_ID","INV_START_DATE","INV_END_DATE","ADJUSTED_STATUS","TRUE_UP_FLAG" FROM ( SELECT rent_inv.ROWID as ROW_ID, rent_inv.var_rent_inv_id, rent_inv.adjust_num, rent_inv.var_rent_id, rent_inv.period_id, rent_inv.invoice_date, rent_inv.tot_act_vol, rent_inv.act_per_rent, rent_inv.constr_actual_rent, rent_inv.for_per_rent, TO_NUMBER(DECODE(rent_inv.for_per_rent ,NULL,NULL ,SUM(decode(rent_inv.adjust_num ,0,rent_inv.for_per_rent ,0)) OVER (PARTITION BY rent_inv.period_id ORDER BY rent_inv.period_id, rent_inv.invoice_date ROWS UNBOUNDED PRECEDING))) AS CUMULATIVE_FOR_RENT, (rent_inv.constr_actual_rent - rent_inv.for_per_rent) AS ACTUAL_FORECASTED_RENT, TO_NUMBER(DECODE((rent_inv.constr_actual_rent - rent_inv.for_per_rent) ,NULL,NULL ,SUM(rent_inv.actual_invoiced_amount) OVER (PARTITION BY rent_inv.period_id ORDER BY rent_inv.period_id,rent_inv.invoice_date ROWS UNBOUNDED PRECEDING) - (select SUM(distinct FOR_PER_RENT) from PN_VAR_RENT_INV_ALL where period_id = rent_inv.period_id and invoice_date <= RENT_INV.INVOICE_DATE))) AS CUMULATIVE_ACT_FOR_RENT, rent_inv.forecasted_term_status, rent_inv.abatement_appl, rent_inv.rec_abatement, rent_inv.rec_abatement_override, rent_inv.negative_rent, (SUM(TO_NUMBER(DECODE(rent_inv.ACTUAL_EXP_CODE ,'Y',rent_inv.actual_invoiced_amount ,0))) OVER (PARTITION BY rent_inv.period_id, rent_inv.invoice_date ORDER BY rent_inv.adjust_num, rent_inv.invoice_date)) AS PREV_INVOICED_AMOUNT, (SUM(rent_inv.ACTUAL_INVOICED_AMOUNT) OVER (PARTITION BY rent_inv.period_id, rent_inv.invoice_date ORDER BY rent_inv.adjust_num, rent_inv.invoice_date)) AS TOT_VAR_RENT, (SUM(rent_inv.ACTUAL_INVOICED_AMOUNT) OVER (PARTITION BY rent_inv.period_id, rent_inv.invoice_date ORDER BY rent_inv.adjust_num, rent_inv.invoice_date)) AS ACTUAL_INVOICED_AMOUNT, pn_var_rent_calc_pkg.new_term_amount(rent_inv.invoice_date,rent_inv.period_id,rent_inv.var_rent_inv_id) AS PRES_INVOICED_AMOUNT, TO_NUMBER(DECODE(rent_inv.actual_invoiced_amount ,NULL,NULL, SUM(rent_inv.actual_invoiced_amount) OVER (PARTITION BY rent_inv.period_id ORDER BY rent_inv.period_id,rent_inv.invoice_date ROWS UNBOUNDED PRECEDING))) AS CUMULATIVE_ACT_RENT, rent_inv.actual_term_status, rent_inv.variance_term_status, rent_inv.actual_exp_code, rent_inv.forecasted_exp_code, rent_inv.variance_exp_code, rent_inv.comments, rent_inv.ATTRIBUTE_CATEGORY, rent_inv.ATTRIBUTE1, rent_inv.ATTRIBUTE2, rent_inv.ATTRIBUTE3, rent_inv.ATTRIBUTE4, rent_inv.ATTRIBUTE5, rent_inv.ATTRIBUTE6, rent_inv.ATTRIBUTE7, rent_inv.ATTRIBUTE8, rent_inv.ATTRIBUTE9, rent_inv.ATTRIBUTE10, rent_inv.ATTRIBUTE11, rent_inv.ATTRIBUTE12, rent_inv.ATTRIBUTE13, rent_inv.ATTRIBUTE14, rent_inv.ATTRIBUTE15, rent_inv.creation_date, rent_inv.created_by, rent_inv.last_update_date, rent_inv.last_updated_by, rent_inv.last_update_login, pn_var_rent_calc_pkg.inv_sch_date(rent_inv.invoice_date,rent_inv.var_rent_id,rent_inv.period_id) as inv_schedule_date, rent_inv.org_id, pn_var_rent_calc_pkg.inv_start_date(rent_inv.invoice_date,rent_inv.var_rent_id,rent_inv.period_id) as inv_start_date, pn_var_rent_calc_pkg.inv_end_date(rent_inv.invoice_date,rent_inv.var_rent_id,rent_inv.period_id) as inv_end_date, (decode(rent_inv.adjust_num, 0,'N' ,'Y')) as adjusted_status, 'N' as TRUE_UP_FLAG FROM pn_var_rent_inv rent_inv, pn_var_periods_all per WHERE per.period_id = rent_inv.period_id AND rent_inv.true_up_amt is null ) rent_inv_temp WHERE rent_inv_temp.adjust_num = (SELECT max(inv.adjust_num) FROM pn_var_rent_inv_all inv WHERE inv.period_id = rent_inv_temp.period_id AND inv.invoice_date = rent_inv_temp.invoice_date AND inv.true_up_amt IS NULL) UNION ALL SELECT "ROW_ID","VAR_RENT_INV_ID","ADJUST_NUM","VAR_RENT_ID","PERIOD_ID","INVOICE_DATE","TOT_ACT_VOL","ACT_PER_RENT","CONSTR_ACTUAL_RENT","FOR_PER_RENT","CUMULATIVE_FOR_RENT","ACTUAL_FORECASTED_RENT","CUMULATIVE_ACT_FOR_RENT","FORECASTED_TERM_STATUS","ABATEMENT_APPL","REC_ABATEMENT","REC_ABATEMENT_OVERRIDE","NEGATIVE_RENT","PREV_INVOICED_AMOUNT","TOT_VAR_RENT","ACTUAL_INVOICED_AMOUNT","PRES_INVOICED_AMOUNT","CUMULATIVE_ACT_RENT","ACTUAL_TERM_STATUS","VARIANCE_TERM_STATUS","ACTUAL_EXP_CODE","FORECASTED_EXP_CODE","VARIANCE_EXP_CODE","COMMENTS","ATTRIBUTE_CATEGORY","ATTRIBUTE1","ATTRIBUTE2","ATTRIBUTE3","ATTRIBUTE4","ATTRIBUTE5","ATTRIBUTE6","ATTRIBUTE7","ATTRIBUTE8","ATTRIBUTE9","ATTRIBUTE10","ATTRIBUTE11","ATTRIBUTE12","ATTRIBUTE13","ATTRIBUTE14","ATTRIBUTE15","CREATION_DATE","CREATED_BY","LAST_UPDATE_DATE","LAST_UPDATED_BY","LAST_UPDATE_LOGIN","INV_SCHEDULE_DATE","ORG_ID","INV_START_DATE","INV_END_DATE","ADJUSTED_STATUS","TRUE_UP_FLAG" FROM (SELECT rent_inv.ROWID as ROW_ID, rent_inv.var_rent_inv_id, rent_inv.adjust_num, rent_inv.var_rent_id, rent_inv.period_id, rent_inv.invoice_date, rent_inv.tot_act_vol, rent_inv.act_per_rent, rent_inv.constr_actual_rent, rent_inv.for_per_rent, TO_NUMBER(DECODE(rent_inv.for_per_rent ,NULL,NULL ,SUM(decode(rent_inv.adjust_num ,0,rent_inv.for_per_rent ,0)) OVER (PARTITION BY rent_inv.period_id ORDER BY rent_inv.period_id, rent_inv.invoice_date ROWS UNBOUNDED PRECEDING))) AS CUMULATIVE_FOR_RENT, (rent_inv.constr_actual_rent - rent_inv.for_per_rent) AS ACTUAL_FORECASTED_RENT, TO_NUMBER(DECODE((rent_inv.constr_actual_rent - rent_inv.for_per_rent) ,NULL,NULL ,SUM(rent_inv.actual_invoiced_amount) OVER (PARTITION BY rent_inv.period_id ORDER BY rent_inv.period_id,rent_inv.invoice_date ROWS UNBOUNDED PRECEDING) - (select SUM(distinct FOR_PER_RENT) from PN_VAR_RENT_INV_ALL where period_id = rent_inv.period_id and invoice_date <= RENT_INV.INVOICE_DATE))) AS CUMULATIVE_ACT_FOR_RENT, rent_inv.forecasted_term_status, rent_inv.abatement_appl, rent_inv.rec_abatement, rent_inv.rec_abatement_override, rent_inv.negative_rent, (SUM(TO_NUMBER(DECODE(rent_inv.ACTUAL_EXP_CODE ,'Y',rent_inv.actual_invoiced_amount ,0))) OVER (PARTITION BY rent_inv.period_id, rent_inv.invoice_date ORDER BY rent_inv.adjust_num, rent_inv.invoice_date)) AS PREV_INVOICED_AMOUNT, (SUM(rent_inv.ACTUAL_INVOICED_AMOUNT) OVER (PARTITION BY rent_inv.period_id, rent_inv.invoice_date ORDER BY rent_inv.adjust_num, rent_inv.invoice_date)) AS TOT_VAR_RENT, (SELECT sum(actual_invoiced_amount) FROM pn_var_rent_inv_all WHERE var_rent_inv_id <= rent_inv.var_rent_inv_id AND period_id = rent_inv.period_id) AS ACTUAL_INVOICED_AMOUNT, ((SUM(TO_NUMBER(decode(rent_inv.variance_exp_code,'Y',0 ,(DECODE(rent_inv.ACTUAL_EXP_CODE ,'Y',0 ,rent_inv.ACTUAL_INVOICED_AMOUNT))))) OVER (PARTITION BY rent_inv.period_id, rent_inv.invoice_date ORDER BY rent_inv.adjust_num, rent_inv.invoice_date)) ) AS PRES_INVOICED_AMOUNT, (SELECT sum(actual_invoiced_amount) FROM pn_var_rent_inv_all WHERE var_rent_inv_id <= rent_inv.var_rent_inv_id AND period_id = rent_inv.period_id) AS CUMULATIVE_ACT_RENT, rent_inv.actual_term_status, rent_inv.variance_term_status, rent_inv.actual_exp_code, rent_inv.forecasted_exp_code, rent_inv.variance_exp_code, rent_inv.comments, rent_inv.ATTRIBUTE_CATEGORY, rent_inv.ATTRIBUTE1, rent_inv.ATTRIBUTE2, rent_inv.ATTRIBUTE3, rent_inv.ATTRIBUTE4, rent_inv.ATTRIBUTE5, rent_inv.ATTRIBUTE6, rent_inv.ATTRIBUTE7, rent_inv.ATTRIBUTE8, rent_inv.ATTRIBUTE9, rent_inv.ATTRIBUTE10, rent_inv.ATTRIBUTE11, rent_inv.ATTRIBUTE12, rent_inv.ATTRIBUTE13, rent_inv.ATTRIBUTE14, rent_inv.ATTRIBUTE15, rent_inv.creation_date, rent_inv.created_by, rent_inv.last_update_date, rent_inv.last_updated_by, rent_inv.last_update_login, pn_var_rent_calc_pkg.inv_sch_date(rent_inv.invoice_date,rent_inv.var_rent_id,rent_inv.period_id) as inv_schedule_date, rent_inv.org_id, greatest(decode(to_char(per.start_date,'DD-MON'),'01-JAN',per.start_date,(select start_date from pn_var_periods_all where period_id = rent_inv.period_id)), (SELECT per.start_date from dual)) inv_start_date, least(decode(to_char(per.start_date,'DD-MON'),'01-JAN',per.end_date,(select end_date from pn_var_periods_all where period_id = rent_inv.period_id)),(SELECT per.end_date from dual)) inv_end_date, (decode(rent_inv.adjust_num, 0,'N' ,'Y')) as adjusted_status, 'Y' as TRUE_UP_FLAG FROM pn_var_periods_all per, pn_var_rent_inv rent_inv WHERE rent_inv.period_id = per.period_id AND rent_inv.true_up_amt is not null ) rent_inv_temp WHERE rent_inv_temp.adjust_num = (SELECT max(inv.adjust_num) FROM pn_var_rent_inv_all inv WHERE inv.period_id = rent_inv_temp.period_id AND inv.invoice_date = rent_inv_temp.invoice_date AND inv.true_up_amt is not null)

View Text - HTML Formatted

SELECT "ROW_ID"
, "VAR_RENT_INV_ID"
, "ADJUST_NUM"
, "VAR_RENT_ID"
, "PERIOD_ID"
, "INVOICE_DATE"
, "TOT_ACT_VOL"
, "ACT_PER_RENT"
, "CONSTR_ACTUAL_RENT"
, "FOR_PER_RENT"
, "CUMULATIVE_FOR_RENT"
, "ACTUAL_FORECASTED_RENT"
, "CUMULATIVE_ACT_FOR_RENT"
, "FORECASTED_TERM_STATUS"
, "ABATEMENT_APPL"
, "REC_ABATEMENT"
, "REC_ABATEMENT_OVERRIDE"
, "NEGATIVE_RENT"
, "PREV_INVOICED_AMOUNT"
, "TOT_VAR_RENT"
, "ACTUAL_INVOICED_AMOUNT"
, "PRES_INVOICED_AMOUNT"
, "CUMULATIVE_ACT_RENT"
, "ACTUAL_TERM_STATUS"
, "VARIANCE_TERM_STATUS"
, "ACTUAL_EXP_CODE"
, "FORECASTED_EXP_CODE"
, "VARIANCE_EXP_CODE"
, "COMMENTS"
, "ATTRIBUTE_CATEGORY"
, "ATTRIBUTE1"
, "ATTRIBUTE2"
, "ATTRIBUTE3"
, "ATTRIBUTE4"
, "ATTRIBUTE5"
, "ATTRIBUTE6"
, "ATTRIBUTE7"
, "ATTRIBUTE8"
, "ATTRIBUTE9"
, "ATTRIBUTE10"
, "ATTRIBUTE11"
, "ATTRIBUTE12"
, "ATTRIBUTE13"
, "ATTRIBUTE14"
, "ATTRIBUTE15"
, "CREATION_DATE"
, "CREATED_BY"
, "LAST_UPDATE_DATE"
, "LAST_UPDATED_BY"
, "LAST_UPDATE_LOGIN"
, "INV_SCHEDULE_DATE"
, "ORG_ID"
, "INV_START_DATE"
, "INV_END_DATE"
, "ADJUSTED_STATUS"
, "TRUE_UP_FLAG"
FROM ( SELECT RENT_INV.ROWID AS ROW_ID
, RENT_INV.VAR_RENT_INV_ID
, RENT_INV.ADJUST_NUM
, RENT_INV.VAR_RENT_ID
, RENT_INV.PERIOD_ID
, RENT_INV.INVOICE_DATE
, RENT_INV.TOT_ACT_VOL
, RENT_INV.ACT_PER_RENT
, RENT_INV.CONSTR_ACTUAL_RENT
, RENT_INV.FOR_PER_RENT
, TO_NUMBER(DECODE(RENT_INV.FOR_PER_RENT
, NULL
, NULL
, SUM(DECODE(RENT_INV.ADJUST_NUM
, 0
, RENT_INV.FOR_PER_RENT
, 0)) OVER (PARTITION BY RENT_INV.PERIOD_ID ORDER BY RENT_INV.PERIOD_ID
, RENT_INV.INVOICE_DATE ROWS UNBOUNDED PRECEDING))) AS CUMULATIVE_FOR_RENT
, (RENT_INV.CONSTR_ACTUAL_RENT - RENT_INV.FOR_PER_RENT) AS ACTUAL_FORECASTED_RENT
, TO_NUMBER(DECODE((RENT_INV.CONSTR_ACTUAL_RENT - RENT_INV.FOR_PER_RENT)
, NULL
, NULL
, SUM(RENT_INV.ACTUAL_INVOICED_AMOUNT) OVER (PARTITION BY RENT_INV.PERIOD_ID ORDER BY RENT_INV.PERIOD_ID
, RENT_INV.INVOICE_DATE ROWS UNBOUNDED PRECEDING) - (SELECT SUM(DISTINCT FOR_PER_RENT)
FROM PN_VAR_RENT_INV_ALL
WHERE PERIOD_ID = RENT_INV.PERIOD_ID
AND INVOICE_DATE <= RENT_INV.INVOICE_DATE))) AS CUMULATIVE_ACT_FOR_RENT
, RENT_INV.FORECASTED_TERM_STATUS
, RENT_INV.ABATEMENT_APPL
, RENT_INV.REC_ABATEMENT
, RENT_INV.REC_ABATEMENT_OVERRIDE
, RENT_INV.NEGATIVE_RENT
, (SUM(TO_NUMBER(DECODE(RENT_INV.ACTUAL_EXP_CODE
, 'Y'
, RENT_INV.ACTUAL_INVOICED_AMOUNT
, 0))) OVER (PARTITION BY RENT_INV.PERIOD_ID
, RENT_INV.INVOICE_DATE ORDER BY RENT_INV.ADJUST_NUM
, RENT_INV.INVOICE_DATE)) AS PREV_INVOICED_AMOUNT
, (SUM(RENT_INV.ACTUAL_INVOICED_AMOUNT) OVER (PARTITION BY RENT_INV.PERIOD_ID
, RENT_INV.INVOICE_DATE ORDER BY RENT_INV.ADJUST_NUM
, RENT_INV.INVOICE_DATE)) AS TOT_VAR_RENT
, (SUM(RENT_INV.ACTUAL_INVOICED_AMOUNT) OVER (PARTITION BY RENT_INV.PERIOD_ID
, RENT_INV.INVOICE_DATE ORDER BY RENT_INV.ADJUST_NUM
, RENT_INV.INVOICE_DATE)) AS ACTUAL_INVOICED_AMOUNT
, PN_VAR_RENT_CALC_PKG.NEW_TERM_AMOUNT(RENT_INV.INVOICE_DATE
, RENT_INV.PERIOD_ID
, RENT_INV.VAR_RENT_INV_ID) AS PRES_INVOICED_AMOUNT
, TO_NUMBER(DECODE(RENT_INV.ACTUAL_INVOICED_AMOUNT
, NULL
, NULL
, SUM(RENT_INV.ACTUAL_INVOICED_AMOUNT) OVER (PARTITION BY RENT_INV.PERIOD_ID ORDER BY RENT_INV.PERIOD_ID
, RENT_INV.INVOICE_DATE ROWS UNBOUNDED PRECEDING))) AS CUMULATIVE_ACT_RENT
, RENT_INV.ACTUAL_TERM_STATUS
, RENT_INV.VARIANCE_TERM_STATUS
, RENT_INV.ACTUAL_EXP_CODE
, RENT_INV.FORECASTED_EXP_CODE
, RENT_INV.VARIANCE_EXP_CODE
, RENT_INV.COMMENTS
, RENT_INV.ATTRIBUTE_CATEGORY
, RENT_INV.ATTRIBUTE1
, RENT_INV.ATTRIBUTE2
, RENT_INV.ATTRIBUTE3
, RENT_INV.ATTRIBUTE4
, RENT_INV.ATTRIBUTE5
, RENT_INV.ATTRIBUTE6
, RENT_INV.ATTRIBUTE7
, RENT_INV.ATTRIBUTE8
, RENT_INV.ATTRIBUTE9
, RENT_INV.ATTRIBUTE10
, RENT_INV.ATTRIBUTE11
, RENT_INV.ATTRIBUTE12
, RENT_INV.ATTRIBUTE13
, RENT_INV.ATTRIBUTE14
, RENT_INV.ATTRIBUTE15
, RENT_INV.CREATION_DATE
, RENT_INV.CREATED_BY
, RENT_INV.LAST_UPDATE_DATE
, RENT_INV.LAST_UPDATED_BY
, RENT_INV.LAST_UPDATE_LOGIN
, PN_VAR_RENT_CALC_PKG.INV_SCH_DATE(RENT_INV.INVOICE_DATE
, RENT_INV.VAR_RENT_ID
, RENT_INV.PERIOD_ID) AS INV_SCHEDULE_DATE
, RENT_INV.ORG_ID
, PN_VAR_RENT_CALC_PKG.INV_START_DATE(RENT_INV.INVOICE_DATE
, RENT_INV.VAR_RENT_ID
, RENT_INV.PERIOD_ID) AS INV_START_DATE
, PN_VAR_RENT_CALC_PKG.INV_END_DATE(RENT_INV.INVOICE_DATE
, RENT_INV.VAR_RENT_ID
, RENT_INV.PERIOD_ID) AS INV_END_DATE
, (DECODE(RENT_INV.ADJUST_NUM
, 0
, 'N'
, 'Y')) AS ADJUSTED_STATUS
, 'N' AS TRUE_UP_FLAG
FROM PN_VAR_RENT_INV RENT_INV
, PN_VAR_PERIODS_ALL PER
WHERE PER.PERIOD_ID = RENT_INV.PERIOD_ID
AND RENT_INV.TRUE_UP_AMT IS NULL ) RENT_INV_TEMP
WHERE RENT_INV_TEMP.ADJUST_NUM = (SELECT MAX(INV.ADJUST_NUM)
FROM PN_VAR_RENT_INV_ALL INV
WHERE INV.PERIOD_ID = RENT_INV_TEMP.PERIOD_ID
AND INV.INVOICE_DATE = RENT_INV_TEMP.INVOICE_DATE
AND INV.TRUE_UP_AMT IS NULL) UNION ALL SELECT "ROW_ID"
, "VAR_RENT_INV_ID"
, "ADJUST_NUM"
, "VAR_RENT_ID"
, "PERIOD_ID"
, "INVOICE_DATE"
, "TOT_ACT_VOL"
, "ACT_PER_RENT"
, "CONSTR_ACTUAL_RENT"
, "FOR_PER_RENT"
, "CUMULATIVE_FOR_RENT"
, "ACTUAL_FORECASTED_RENT"
, "CUMULATIVE_ACT_FOR_RENT"
, "FORECASTED_TERM_STATUS"
, "ABATEMENT_APPL"
, "REC_ABATEMENT"
, "REC_ABATEMENT_OVERRIDE"
, "NEGATIVE_RENT"
, "PREV_INVOICED_AMOUNT"
, "TOT_VAR_RENT"
, "ACTUAL_INVOICED_AMOUNT"
, "PRES_INVOICED_AMOUNT"
, "CUMULATIVE_ACT_RENT"
, "ACTUAL_TERM_STATUS"
, "VARIANCE_TERM_STATUS"
, "ACTUAL_EXP_CODE"
, "FORECASTED_EXP_CODE"
, "VARIANCE_EXP_CODE"
, "COMMENTS"
, "ATTRIBUTE_CATEGORY"
, "ATTRIBUTE1"
, "ATTRIBUTE2"
, "ATTRIBUTE3"
, "ATTRIBUTE4"
, "ATTRIBUTE5"
, "ATTRIBUTE6"
, "ATTRIBUTE7"
, "ATTRIBUTE8"
, "ATTRIBUTE9"
, "ATTRIBUTE10"
, "ATTRIBUTE11"
, "ATTRIBUTE12"
, "ATTRIBUTE13"
, "ATTRIBUTE14"
, "ATTRIBUTE15"
, "CREATION_DATE"
, "CREATED_BY"
, "LAST_UPDATE_DATE"
, "LAST_UPDATED_BY"
, "LAST_UPDATE_LOGIN"
, "INV_SCHEDULE_DATE"
, "ORG_ID"
, "INV_START_DATE"
, "INV_END_DATE"
, "ADJUSTED_STATUS"
, "TRUE_UP_FLAG"
FROM (SELECT RENT_INV.ROWID AS ROW_ID
, RENT_INV.VAR_RENT_INV_ID
, RENT_INV.ADJUST_NUM
, RENT_INV.VAR_RENT_ID
, RENT_INV.PERIOD_ID
, RENT_INV.INVOICE_DATE
, RENT_INV.TOT_ACT_VOL
, RENT_INV.ACT_PER_RENT
, RENT_INV.CONSTR_ACTUAL_RENT
, RENT_INV.FOR_PER_RENT
, TO_NUMBER(DECODE(RENT_INV.FOR_PER_RENT
, NULL
, NULL
, SUM(DECODE(RENT_INV.ADJUST_NUM
, 0
, RENT_INV.FOR_PER_RENT
, 0)) OVER (PARTITION BY RENT_INV.PERIOD_ID ORDER BY RENT_INV.PERIOD_ID
, RENT_INV.INVOICE_DATE ROWS UNBOUNDED PRECEDING))) AS CUMULATIVE_FOR_RENT
, (RENT_INV.CONSTR_ACTUAL_RENT - RENT_INV.FOR_PER_RENT) AS ACTUAL_FORECASTED_RENT
, TO_NUMBER(DECODE((RENT_INV.CONSTR_ACTUAL_RENT - RENT_INV.FOR_PER_RENT)
, NULL
, NULL
, SUM(RENT_INV.ACTUAL_INVOICED_AMOUNT) OVER (PARTITION BY RENT_INV.PERIOD_ID ORDER BY RENT_INV.PERIOD_ID
, RENT_INV.INVOICE_DATE ROWS UNBOUNDED PRECEDING) - (SELECT SUM(DISTINCT FOR_PER_RENT)
FROM PN_VAR_RENT_INV_ALL
WHERE PERIOD_ID = RENT_INV.PERIOD_ID
AND INVOICE_DATE <= RENT_INV.INVOICE_DATE))) AS CUMULATIVE_ACT_FOR_RENT
, RENT_INV.FORECASTED_TERM_STATUS
, RENT_INV.ABATEMENT_APPL
, RENT_INV.REC_ABATEMENT
, RENT_INV.REC_ABATEMENT_OVERRIDE
, RENT_INV.NEGATIVE_RENT
, (SUM(TO_NUMBER(DECODE(RENT_INV.ACTUAL_EXP_CODE
, 'Y'
, RENT_INV.ACTUAL_INVOICED_AMOUNT
, 0))) OVER (PARTITION BY RENT_INV.PERIOD_ID
, RENT_INV.INVOICE_DATE ORDER BY RENT_INV.ADJUST_NUM
, RENT_INV.INVOICE_DATE)) AS PREV_INVOICED_AMOUNT
, (SUM(RENT_INV.ACTUAL_INVOICED_AMOUNT) OVER (PARTITION BY RENT_INV.PERIOD_ID
, RENT_INV.INVOICE_DATE ORDER BY RENT_INV.ADJUST_NUM
, RENT_INV.INVOICE_DATE)) AS TOT_VAR_RENT
, (SELECT SUM(ACTUAL_INVOICED_AMOUNT)
FROM PN_VAR_RENT_INV_ALL
WHERE VAR_RENT_INV_ID <= RENT_INV.VAR_RENT_INV_ID
AND PERIOD_ID = RENT_INV.PERIOD_ID) AS ACTUAL_INVOICED_AMOUNT
, ((SUM(TO_NUMBER(DECODE(RENT_INV.VARIANCE_EXP_CODE
, 'Y'
, 0
, (DECODE(RENT_INV.ACTUAL_EXP_CODE
, 'Y'
, 0
, RENT_INV.ACTUAL_INVOICED_AMOUNT))))) OVER (PARTITION BY RENT_INV.PERIOD_ID
, RENT_INV.INVOICE_DATE ORDER BY RENT_INV.ADJUST_NUM
, RENT_INV.INVOICE_DATE)) ) AS PRES_INVOICED_AMOUNT
, (SELECT SUM(ACTUAL_INVOICED_AMOUNT)
FROM PN_VAR_RENT_INV_ALL
WHERE VAR_RENT_INV_ID <= RENT_INV.VAR_RENT_INV_ID
AND PERIOD_ID = RENT_INV.PERIOD_ID) AS CUMULATIVE_ACT_RENT
, RENT_INV.ACTUAL_TERM_STATUS
, RENT_INV.VARIANCE_TERM_STATUS
, RENT_INV.ACTUAL_EXP_CODE
, RENT_INV.FORECASTED_EXP_CODE
, RENT_INV.VARIANCE_EXP_CODE
, RENT_INV.COMMENTS
, RENT_INV.ATTRIBUTE_CATEGORY
, RENT_INV.ATTRIBUTE1
, RENT_INV.ATTRIBUTE2
, RENT_INV.ATTRIBUTE3
, RENT_INV.ATTRIBUTE4
, RENT_INV.ATTRIBUTE5
, RENT_INV.ATTRIBUTE6
, RENT_INV.ATTRIBUTE7
, RENT_INV.ATTRIBUTE8
, RENT_INV.ATTRIBUTE9
, RENT_INV.ATTRIBUTE10
, RENT_INV.ATTRIBUTE11
, RENT_INV.ATTRIBUTE12
, RENT_INV.ATTRIBUTE13
, RENT_INV.ATTRIBUTE14
, RENT_INV.ATTRIBUTE15
, RENT_INV.CREATION_DATE
, RENT_INV.CREATED_BY
, RENT_INV.LAST_UPDATE_DATE
, RENT_INV.LAST_UPDATED_BY
, RENT_INV.LAST_UPDATE_LOGIN
, PN_VAR_RENT_CALC_PKG.INV_SCH_DATE(RENT_INV.INVOICE_DATE
, RENT_INV.VAR_RENT_ID
, RENT_INV.PERIOD_ID) AS INV_SCHEDULE_DATE
, RENT_INV.ORG_ID
, GREATEST(DECODE(TO_CHAR(PER.START_DATE
, 'DD-MON')
, '01-JAN'
, PER.START_DATE
, (SELECT START_DATE
FROM PN_VAR_PERIODS_ALL
WHERE PERIOD_ID = RENT_INV.PERIOD_ID))
, (SELECT PER.START_DATE
FROM DUAL)) INV_START_DATE
, LEAST(DECODE(TO_CHAR(PER.START_DATE
, 'DD-MON')
, '01-JAN'
, PER.END_DATE
, (SELECT END_DATE
FROM PN_VAR_PERIODS_ALL
WHERE PERIOD_ID = RENT_INV.PERIOD_ID))
, (SELECT PER.END_DATE
FROM DUAL)) INV_END_DATE
, (DECODE(RENT_INV.ADJUST_NUM
, 0
, 'N'
, 'Y')) AS ADJUSTED_STATUS
, 'Y' AS TRUE_UP_FLAG
FROM PN_VAR_PERIODS_ALL PER
, PN_VAR_RENT_INV RENT_INV
WHERE RENT_INV.PERIOD_ID = PER.PERIOD_ID
AND RENT_INV.TRUE_UP_AMT IS NOT NULL ) RENT_INV_TEMP
WHERE RENT_INV_TEMP.ADJUST_NUM = (SELECT MAX(INV.ADJUST_NUM)
FROM PN_VAR_RENT_INV_ALL INV
WHERE INV.PERIOD_ID = RENT_INV_TEMP.PERIOD_ID
AND INV.INVOICE_DATE = RENT_INV_TEMP.INVOICE_DATE
AND INV.TRUE_UP_AMT IS NOT NULL)