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_SUMM_V

Source

View Text - Preformatted


SELECT summ.var_rent_id, summ.period_id, summ.line_item_id, summ.invoice_date, SUM(summ.tot_act_vol) actual_volume, SUM(summ.tot_for_vol) forecasted_volume, To_number(Decode(SUM(summ.tot_for_vol), NULL, NULL, SUM(SUM(summ.tot_for_vol)) over ( PARTITION BY summ.line_item_id ORDER BY summ.invoice_date ROWS unbounded preceding))), Nvl(SUM(summ.tot_ded), 0) deductions, ( SUM(summ.tot_act_vol) - Nvl(SUM(summ.tot_ded), 0) ) net_actual_volume, To_number( Decode(( SUM(summ.tot_act_vol) - Nvl(SUM(summ.tot_ded), 0) ), NULL, NULL, SUM(SUM(summ.tot_act_vol) - Nvl(SUM(summ.tot_ded), 0)) over ( PARTITION BY summ.line_item_id ORDER BY summ.invoice_date ROWS unbounded preceding))), SUM(summ.act_var_rent - Nvl(first_yr_rent, 0)) act_var_rent, To_number(Decode(SUM(summ.act_var_rent - Nvl(first_yr_rent, 0)), NULL, NULL, SUM(SUM( summ.act_var_rent - Nvl(first_yr_rent, 0))) over ( PARTITION BY summ.line_item_id ORDER BY summ.invoice_date ROWS unbounded preceding))), SUM(summ.for_var_rent - Nvl(first_yr_rent, 0)) for_var_rent, To_number(Decode(SUM(summ.for_var_rent - Nvl(first_yr_rent, 0)), NULL, NULL, SUM(SUM( summ.for_var_rent - Nvl(first_yr_rent, 0))) over ( PARTITION BY summ.line_item_id ORDER BY summ.invoice_date ROWS unbounded preceding))), pn_var_rent_calc_pkg.Inv_sch_date(summ.invoice_date, summ.var_rent_id, summ.period_id) inv_schedule_date, pn_var_rent_calc_pkg.Inv_start_date(summ.invoice_date, summ.var_rent_id, summ.period_id) inv_start_date, pn_var_rent_calc_pkg.Inv_end_date(summ.invoice_date, summ.var_rent_id, summ.period_id) inv_end_date, summ.org_id, 0, 'N' FROM pn_var_rent_summ summ GROUP BY summ.var_rent_id, summ.period_id, summ.line_item_id, summ.invoice_date, summ.org_id UNION ALL SELECT summ.var_rent_id, summ.period_id, summ.line_item_id, summ.invoice_date, 0 actual_volume, 0 forecasted_volume, 0 cumulative_for_volume, 0 deductions, 0 net_actual_volume, 0 cumulative_act_volume, Nvl(summ.trueup_var_rent, 0) act_var_rent, Nvl(summ.trueup_var_rent, 0) cumulative_act_rent, 0 for_var_rent, 0, pn_var_rent_calc_pkg.Inv_sch_date(summ.invoice_date, summ.var_rent_id, summ.period_id) inv_schedule_date, pn_var_rent_calc_pkg.Inv_start_date(summ.invoice_date, summ.var_rent_id, summ.period_id) inv_start_date, pn_var_rent_calc_pkg.Inv_end_date(summ.invoice_date, summ.var_rent_id, summ.period_id) inv_end_date, summ.org_id, summ.trueup_var_rent, 'Y' FROM pn_var_rent_summ summ WHERE pn_var_rent_calc_pkg.Trueup_rent(summ.var_rent_id, summ.period_id, summ.grp_date_id) IS NOT NULL UNION ALL SELECT summ.var_rent_id, Decode(summ.first_yr_rent, 0, summ.period_id, (SELECT summ1.period_id FROM pn_var_rent_summ_all summ1, pn_var_periods_all per WHERE per.period_id = summ1.period_id AND summ1.var_rent_id = summ.var_rent_id AND per.period_num = 1 GROUP BY summ1.period_id)), summ.line_item_id, summ.invoice_date, pn_var_rent_calc_pkg.First_yr_sales(summ.var_rent_id, summ.line_item_id) ACTUAL_VOLUME, 0 FORECASTED_VOLUME, 0 CUMULATIVE_FOR_VOLUME, pn_var_rent_calc_pkg.First_yr_deductions(summ.var_rent_id, summ.line_item_id) deductions, pn_var_rent_calc_pkg.Fy_net_sales(summ.var_rent_id, summ.line_item_id) NET_ACTUAL_VOLUME, pn_var_rent_calc_pkg.Fy_net_sales(summ.var_rent_id, summ.line_item_id) CUMULATIVE_ACT_VOLUME, SUM(summ.first_yr_rent) act_var_rent, SUM(summ.first_yr_rent), NULL for_var_rent, NULL, pn_var_rent_calc_pkg.Inv_sch_date(summ.invoice_date, summ.var_rent_id, summ.period_id) inv_schedule_date, pn_var_rent_calc_pkg.Inv_start_date(summ.invoice_date, summ.var_rent_id, summ.period_id) inv_start_date, pn_var_rent_calc_pkg.Inv_end_date(summ.invoice_date, summ.var_rent_id, summ.period_id) inv_end_date, summ.org_id, summ.trueup_var_rent, 'N' from pn_var_rent_summ summ WHERE summ.first_yr_rent > 0 AND EXISTS ( SELECT 'X' FROM pn_var_rent_summ_all summ1, pn_var_periods_all per WHERE summ1.var_rent_id = summ.var_rent_id and per.period_num = 1 AND per.period_id = summ1.period_id ) GROUP BY summ.var_rent_id, summ.period_id, summ.line_item_id, summ.invoice_date, summ.first_yr_rent, summ.org_id, summ.trueup_var_rent

View Text - HTML Formatted

SELECT SUMM.VAR_RENT_ID
, SUMM.PERIOD_ID
, SUMM.LINE_ITEM_ID
, SUMM.INVOICE_DATE
, SUM(SUMM.TOT_ACT_VOL) ACTUAL_VOLUME
, SUM(SUMM.TOT_FOR_VOL) FORECASTED_VOLUME
, TO_NUMBER(DECODE(SUM(SUMM.TOT_FOR_VOL)
, NULL
, NULL
, SUM(SUM(SUMM.TOT_FOR_VOL)) OVER ( PARTITION BY SUMM.LINE_ITEM_ID ORDER BY SUMM.INVOICE_DATE ROWS UNBOUNDED PRECEDING)))
, NVL(SUM(SUMM.TOT_DED)
, 0) DEDUCTIONS
, ( SUM(SUMM.TOT_ACT_VOL) - NVL(SUM(SUMM.TOT_DED)
, 0) ) NET_ACTUAL_VOLUME
, TO_NUMBER( DECODE(( SUM(SUMM.TOT_ACT_VOL) - NVL(SUM(SUMM.TOT_DED)
, 0) )
, NULL
, NULL
, SUM(SUM(SUMM.TOT_ACT_VOL) - NVL(SUM(SUMM.TOT_DED)
, 0)) OVER ( PARTITION BY SUMM.LINE_ITEM_ID ORDER BY SUMM.INVOICE_DATE ROWS UNBOUNDED PRECEDING)))
, SUM(SUMM.ACT_VAR_RENT - NVL(FIRST_YR_RENT
, 0)) ACT_VAR_RENT
, TO_NUMBER(DECODE(SUM(SUMM.ACT_VAR_RENT - NVL(FIRST_YR_RENT
, 0))
, NULL
, NULL
, SUM(SUM( SUMM.ACT_VAR_RENT - NVL(FIRST_YR_RENT
, 0))) OVER ( PARTITION BY SUMM.LINE_ITEM_ID ORDER BY SUMM.INVOICE_DATE ROWS UNBOUNDED PRECEDING)))
, SUM(SUMM.FOR_VAR_RENT - NVL(FIRST_YR_RENT
, 0)) FOR_VAR_RENT
, TO_NUMBER(DECODE(SUM(SUMM.FOR_VAR_RENT - NVL(FIRST_YR_RENT
, 0))
, NULL
, NULL
, SUM(SUM( SUMM.FOR_VAR_RENT - NVL(FIRST_YR_RENT
, 0))) OVER ( PARTITION BY SUMM.LINE_ITEM_ID ORDER BY SUMM.INVOICE_DATE ROWS UNBOUNDED PRECEDING)))
, PN_VAR_RENT_CALC_PKG.INV_SCH_DATE(SUMM.INVOICE_DATE
, SUMM.VAR_RENT_ID
, SUMM.PERIOD_ID) INV_SCHEDULE_DATE
, PN_VAR_RENT_CALC_PKG.INV_START_DATE(SUMM.INVOICE_DATE
, SUMM.VAR_RENT_ID
, SUMM.PERIOD_ID) INV_START_DATE
, PN_VAR_RENT_CALC_PKG.INV_END_DATE(SUMM.INVOICE_DATE
, SUMM.VAR_RENT_ID
, SUMM.PERIOD_ID) INV_END_DATE
, SUMM.ORG_ID
, 0
, 'N'
FROM PN_VAR_RENT_SUMM SUMM GROUP BY SUMM.VAR_RENT_ID
, SUMM.PERIOD_ID
, SUMM.LINE_ITEM_ID
, SUMM.INVOICE_DATE
, SUMM.ORG_ID UNION ALL SELECT SUMM.VAR_RENT_ID
, SUMM.PERIOD_ID
, SUMM.LINE_ITEM_ID
, SUMM.INVOICE_DATE
, 0 ACTUAL_VOLUME
, 0 FORECASTED_VOLUME
, 0 CUMULATIVE_FOR_VOLUME
, 0 DEDUCTIONS
, 0 NET_ACTUAL_VOLUME
, 0 CUMULATIVE_ACT_VOLUME
, NVL(SUMM.TRUEUP_VAR_RENT
, 0) ACT_VAR_RENT
, NVL(SUMM.TRUEUP_VAR_RENT
, 0) CUMULATIVE_ACT_RENT
, 0 FOR_VAR_RENT
, 0
, PN_VAR_RENT_CALC_PKG.INV_SCH_DATE(SUMM.INVOICE_DATE
, SUMM.VAR_RENT_ID
, SUMM.PERIOD_ID) INV_SCHEDULE_DATE
, PN_VAR_RENT_CALC_PKG.INV_START_DATE(SUMM.INVOICE_DATE
, SUMM.VAR_RENT_ID
, SUMM.PERIOD_ID) INV_START_DATE
, PN_VAR_RENT_CALC_PKG.INV_END_DATE(SUMM.INVOICE_DATE
, SUMM.VAR_RENT_ID
, SUMM.PERIOD_ID) INV_END_DATE
, SUMM.ORG_ID
, SUMM.TRUEUP_VAR_RENT
, 'Y'
FROM PN_VAR_RENT_SUMM SUMM
WHERE PN_VAR_RENT_CALC_PKG.TRUEUP_RENT(SUMM.VAR_RENT_ID
, SUMM.PERIOD_ID
, SUMM.GRP_DATE_ID) IS NOT NULL UNION ALL SELECT SUMM.VAR_RENT_ID
, DECODE(SUMM.FIRST_YR_RENT
, 0
, SUMM.PERIOD_ID
, (SELECT SUMM1.PERIOD_ID
FROM PN_VAR_RENT_SUMM_ALL SUMM1
, PN_VAR_PERIODS_ALL PER
WHERE PER.PERIOD_ID = SUMM1.PERIOD_ID
AND SUMM1.VAR_RENT_ID = SUMM.VAR_RENT_ID
AND PER.PERIOD_NUM = 1 GROUP BY SUMM1.PERIOD_ID))
, SUMM.LINE_ITEM_ID
, SUMM.INVOICE_DATE
, PN_VAR_RENT_CALC_PKG.FIRST_YR_SALES(SUMM.VAR_RENT_ID
, SUMM.LINE_ITEM_ID) ACTUAL_VOLUME
, 0 FORECASTED_VOLUME
, 0 CUMULATIVE_FOR_VOLUME
, PN_VAR_RENT_CALC_PKG.FIRST_YR_DEDUCTIONS(SUMM.VAR_RENT_ID
, SUMM.LINE_ITEM_ID) DEDUCTIONS
, PN_VAR_RENT_CALC_PKG.FY_NET_SALES(SUMM.VAR_RENT_ID
, SUMM.LINE_ITEM_ID) NET_ACTUAL_VOLUME
, PN_VAR_RENT_CALC_PKG.FY_NET_SALES(SUMM.VAR_RENT_ID
, SUMM.LINE_ITEM_ID) CUMULATIVE_ACT_VOLUME
, SUM(SUMM.FIRST_YR_RENT) ACT_VAR_RENT
, SUM(SUMM.FIRST_YR_RENT)
, NULL FOR_VAR_RENT
, NULL
, PN_VAR_RENT_CALC_PKG.INV_SCH_DATE(SUMM.INVOICE_DATE
, SUMM.VAR_RENT_ID
, SUMM.PERIOD_ID) INV_SCHEDULE_DATE
, PN_VAR_RENT_CALC_PKG.INV_START_DATE(SUMM.INVOICE_DATE
, SUMM.VAR_RENT_ID
, SUMM.PERIOD_ID) INV_START_DATE
, PN_VAR_RENT_CALC_PKG.INV_END_DATE(SUMM.INVOICE_DATE
, SUMM.VAR_RENT_ID
, SUMM.PERIOD_ID) INV_END_DATE
, SUMM.ORG_ID
, SUMM.TRUEUP_VAR_RENT
, 'N'
FROM PN_VAR_RENT_SUMM SUMM
WHERE SUMM.FIRST_YR_RENT > 0
AND EXISTS ( SELECT 'X'
FROM PN_VAR_RENT_SUMM_ALL SUMM1
, PN_VAR_PERIODS_ALL PER
WHERE SUMM1.VAR_RENT_ID = SUMM.VAR_RENT_ID
AND PER.PERIOD_NUM = 1
AND PER.PERIOD_ID = SUMM1.PERIOD_ID ) GROUP BY SUMM.VAR_RENT_ID
, SUMM.PERIOD_ID
, SUMM.LINE_ITEM_ID
, SUMM.INVOICE_DATE
, SUMM.FIRST_YR_RENT
, SUMM.ORG_ID
, SUMM.TRUEUP_VAR_RENT