VIEW: APPS.PN_FOR_RENT_DETAILS_V

Source

View Text - Preformatted

SELECT gd.group_date ,gd.invoice_date ,summ.line_item_id ,summ.tot_for_vol tot_vol ,pn_variable_amount_pkg.get_prorated_bkpt(vr.cumulative_vol, gd.grp_start_date, gd.grp_end_date, per.start_date, per.end_date, bkpt_det.period_bkpt_vol_start, bkpt_det.group_bkpt_vol_start, gd.proration_factor) bkpt_vol_start ,pn_variable_amount_pkg.get_prorated_bkpt(vr.cumulative_vol, gd.grp_start_date, gd.grp_end_date, per.start_date, per.end_date, bkpt_det.period_bkpt_vol_end, bkpt_det.group_bkpt_vol_end, gd.proration_factor) bkpt_vol_end ,(pn_variable_amount_pkg.get_prorated_bkpt(vr.cumulative_vol, gd.grp_start_date, gd.grp_end_date, per.start_date, per.end_date, bkpt_det.period_bkpt_vol_end, bkpt_det.group_bkpt_vol_end, gd.proration_factor) - pn_variable_amount_pkg.get_prorated_bkpt(vr.cumulative_vol, gd.grp_start_date, gd.grp_end_date, per.start_date, per.end_date, bkpt_det.period_bkpt_vol_start, bkpt_det.group_bkpt_vol_start, gd.proration_factor) ) vol_appl ,bkpt_det.bkpt_rate ,(pn_variable_amount_pkg.get_prorated_bkpt(vr.cumulative_vol, gd.grp_start_date, gd.grp_end_date, per.start_date, per.end_date, bkpt_det.period_bkpt_vol_end, bkpt_det.group_bkpt_vol_end, gd.proration_factor) - pn_variable_amount_pkg.get_prorated_bkpt(vr.cumulative_vol, gd.grp_start_date, gd.grp_end_date, per.start_date, per.end_date, bkpt_det.period_bkpt_vol_start, bkpt_det.group_bkpt_vol_start, gd.proration_factor) ) * bkpt_det.bkpt_rate var_rent ,gd.actual_exp_code ,gd.forecasted_exp_code ,gd.variance_exp_code ,'FORECASTED' var_rent_type ,vr.org_id FROM pn_var_rents vr, pn_var_periods_all per, pn_var_grp_dates_all gd, pn_var_rent_summ_all summ, (select det.period_bkpt_vol_start, det.group_bkpt_vol_start, det.group_bkpt_vol_end, det.period_bkpt_vol_end, det.bkpt_rate, head.breakpoint_type, head.line_item_id from pn_var_bkpts_det_all det, pn_var_bkpts_head_all head WHERE head.bkpt_header_id = det.bkpt_header_id and head.breakpoint_type = 'STRATIFIED') bkpt_det WHERE vr.var_rent_id = per.var_rent_id AND vr.invoice_on = 'FORECASTED' AND per.period_id = gd.period_id AND per.period_id = summ.period_id AND gd.grp_date_id = summ.grp_date_id AND summ.period_id = gd.period_id AND bkpt_det.line_item_id = summ.line_item_id AND pn_variable_amount_pkg.get_prorated_bkpt(vr.cumulative_vol, gd.grp_start_date, gd.grp_end_date, per.start_date, per.end_date, bkpt_det.period_bkpt_vol_start, bkpt_det.group_bkpt_vol_start, gd.proration_factor) < summ.tot_for_vol AND nvl(pn_variable_amount_pkg.get_prorated_bkpt( vr.cumulative_vol, gd.grp_start_date, gd.grp_end_date, per.start_date, per.end_date, bkpt_det.period_bkpt_vol_end, bkpt_det.group_bkpt_vol_end, gd.proration_factor), summ.tot_for_vol) < summ.tot_for_vol UNION ALL SELECT gd.group_date ,gd.invoice_date ,summ.line_item_id ,summ.tot_for_vol tot_vol ,pn_variable_amount_pkg.get_prorated_bkpt( vr.cumulative_vol, gd.grp_start_date, gd.grp_end_date, per.start_date, per.end_date, bkpt_det.period_bkpt_vol_start, bkpt_det.group_bkpt_vol_start, gd.proration_factor) bkpt_vol_start ,pn_variable_amount_pkg.get_prorated_bkpt( vr.cumulative_vol, gd.grp_start_date, gd.grp_end_date, per.start_date, per.end_date, bkpt_det.period_bkpt_vol_end, bkpt_det.group_bkpt_vol_end, gd.proration_factor) bkpt_vol_end ,DECODE(bkpt_det.breakpoint_type, 'SLIDING', summ.tot_for_vol, (summ.tot_for_vol- pn_variable_amount_pkg.get_prorated_bkpt( vr.cumulative_vol, gd.grp_start_date, gd.grp_end_date, per.start_date, per.end_date, bkpt_det.period_bkpt_vol_start, bkpt_det.group_bkpt_vol_start, gd.proration_factor)) ) vol_appl ,bkpt_det.bkpt_rate ,DECODE(bkpt_det.breakpoint_type, 'SLIDING', summ.tot_for_vol, (summ.tot_for_vol- pn_variable_amount_pkg.get_prorated_bkpt( vr.cumulative_vol, gd.grp_start_date, gd.grp_end_date, per.start_date, per.end_date, bkpt_det.period_bkpt_vol_start, bkpt_det.group_bkpt_vol_start, gd.proration_factor)) ) * bkpt_det.bkpt_rate var_rent ,gd.actual_exp_code ,gd.forecasted_exp_code ,gd.variance_exp_code ,'FORECASTED' var_rent_type ,vr.org_id FROM pn_var_rents vr, pn_var_periods_all per, pn_var_grp_dates_all gd, pn_var_rent_summ_all summ, (select det.period_bkpt_vol_start, det.group_bkpt_vol_start, det.group_bkpt_vol_end, det.period_bkpt_vol_end, det.bkpt_rate, head.breakpoint_type, head.line_item_id from pn_var_bkpts_det_all det, pn_var_bkpts_head head where det.bkpt_header_id = head.bkpt_header_id ) bkpt_det WHERE vr.var_rent_id = per.var_rent_id AND per.period_id = gd.period_id AND summ.period_id = per.period_id AND summ.grp_date_id = gd.grp_date_id AND summ.period_id = gd.period_id AND bkpt_det.line_item_id = summ.line_item_id AND vr.invoice_on = 'FORECASTED' AND pn_variable_amount_pkg.get_prorated_bkpt(vr.cumulative_vol, gd.grp_start_date, gd.grp_end_date, per.start_date, per.end_date, bkpt_det.period_bkpt_vol_start, bkpt_det.group_bkpt_vol_start, gd.proration_factor) < summ.tot_for_vol AND nvl(pn_variable_amount_pkg.get_prorated_bkpt( vr.cumulative_vol, gd.grp_start_date, gd.grp_end_date, per.start_date, per.end_date, bkpt_det.period_bkpt_vol_end, bkpt_det.group_bkpt_vol_end, gd.proration_factor), tot_for_vol) >= summ.tot_for_vol

View Text - HTML Formatted

SELECT GD.GROUP_DATE
, GD.INVOICE_DATE
, SUMM.LINE_ITEM_ID
, SUMM.TOT_FOR_VOL TOT_VOL
, PN_VARIABLE_AMOUNT_PKG.GET_PRORATED_BKPT(VR.CUMULATIVE_VOL
, GD.GRP_START_DATE
, GD.GRP_END_DATE
, PER.START_DATE
, PER.END_DATE
, BKPT_DET.PERIOD_BKPT_VOL_START
, BKPT_DET.GROUP_BKPT_VOL_START
, GD.PRORATION_FACTOR) BKPT_VOL_START
, PN_VARIABLE_AMOUNT_PKG.GET_PRORATED_BKPT(VR.CUMULATIVE_VOL
, GD.GRP_START_DATE
, GD.GRP_END_DATE
, PER.START_DATE
, PER.END_DATE
, BKPT_DET.PERIOD_BKPT_VOL_END
, BKPT_DET.GROUP_BKPT_VOL_END
, GD.PRORATION_FACTOR) BKPT_VOL_END
, (PN_VARIABLE_AMOUNT_PKG.GET_PRORATED_BKPT(VR.CUMULATIVE_VOL
, GD.GRP_START_DATE
, GD.GRP_END_DATE
, PER.START_DATE
, PER.END_DATE
, BKPT_DET.PERIOD_BKPT_VOL_END
, BKPT_DET.GROUP_BKPT_VOL_END
, GD.PRORATION_FACTOR) - PN_VARIABLE_AMOUNT_PKG.GET_PRORATED_BKPT(VR.CUMULATIVE_VOL
, GD.GRP_START_DATE
, GD.GRP_END_DATE
, PER.START_DATE
, PER.END_DATE
, BKPT_DET.PERIOD_BKPT_VOL_START
, BKPT_DET.GROUP_BKPT_VOL_START
, GD.PRORATION_FACTOR) ) VOL_APPL
, BKPT_DET.BKPT_RATE
, (PN_VARIABLE_AMOUNT_PKG.GET_PRORATED_BKPT(VR.CUMULATIVE_VOL
, GD.GRP_START_DATE
, GD.GRP_END_DATE
, PER.START_DATE
, PER.END_DATE
, BKPT_DET.PERIOD_BKPT_VOL_END
, BKPT_DET.GROUP_BKPT_VOL_END
, GD.PRORATION_FACTOR) - PN_VARIABLE_AMOUNT_PKG.GET_PRORATED_BKPT(VR.CUMULATIVE_VOL
, GD.GRP_START_DATE
, GD.GRP_END_DATE
, PER.START_DATE
, PER.END_DATE
, BKPT_DET.PERIOD_BKPT_VOL_START
, BKPT_DET.GROUP_BKPT_VOL_START
, GD.PRORATION_FACTOR) ) * BKPT_DET.BKPT_RATE VAR_RENT
, GD.ACTUAL_EXP_CODE
, GD.FORECASTED_EXP_CODE
, GD.VARIANCE_EXP_CODE
, 'FORECASTED' VAR_RENT_TYPE
, VR.ORG_ID
FROM PN_VAR_RENTS VR
, PN_VAR_PERIODS_ALL PER
, PN_VAR_GRP_DATES_ALL GD
, PN_VAR_RENT_SUMM_ALL SUMM
, (SELECT DET.PERIOD_BKPT_VOL_START
, DET.GROUP_BKPT_VOL_START
, DET.GROUP_BKPT_VOL_END
, DET.PERIOD_BKPT_VOL_END
, DET.BKPT_RATE
, HEAD.BREAKPOINT_TYPE
, HEAD.LINE_ITEM_ID
FROM PN_VAR_BKPTS_DET_ALL DET
, PN_VAR_BKPTS_HEAD_ALL HEAD
WHERE HEAD.BKPT_HEADER_ID = DET.BKPT_HEADER_ID
AND HEAD.BREAKPOINT_TYPE = 'STRATIFIED') BKPT_DET
WHERE VR.VAR_RENT_ID = PER.VAR_RENT_ID
AND VR.INVOICE_ON = 'FORECASTED'
AND PER.PERIOD_ID = GD.PERIOD_ID
AND PER.PERIOD_ID = SUMM.PERIOD_ID
AND GD.GRP_DATE_ID = SUMM.GRP_DATE_ID
AND SUMM.PERIOD_ID = GD.PERIOD_ID
AND BKPT_DET.LINE_ITEM_ID = SUMM.LINE_ITEM_ID
AND PN_VARIABLE_AMOUNT_PKG.GET_PRORATED_BKPT(VR.CUMULATIVE_VOL
, GD.GRP_START_DATE
, GD.GRP_END_DATE
, PER.START_DATE
, PER.END_DATE
, BKPT_DET.PERIOD_BKPT_VOL_START
, BKPT_DET.GROUP_BKPT_VOL_START
, GD.PRORATION_FACTOR) < SUMM.TOT_FOR_VOL
AND NVL(PN_VARIABLE_AMOUNT_PKG.GET_PRORATED_BKPT( VR.CUMULATIVE_VOL
, GD.GRP_START_DATE
, GD.GRP_END_DATE
, PER.START_DATE
, PER.END_DATE
, BKPT_DET.PERIOD_BKPT_VOL_END
, BKPT_DET.GROUP_BKPT_VOL_END
, GD.PRORATION_FACTOR)
, SUMM.TOT_FOR_VOL) < SUMM.TOT_FOR_VOL UNION ALL SELECT GD.GROUP_DATE
, GD.INVOICE_DATE
, SUMM.LINE_ITEM_ID
, SUMM.TOT_FOR_VOL TOT_VOL
, PN_VARIABLE_AMOUNT_PKG.GET_PRORATED_BKPT( VR.CUMULATIVE_VOL
, GD.GRP_START_DATE
, GD.GRP_END_DATE
, PER.START_DATE
, PER.END_DATE
, BKPT_DET.PERIOD_BKPT_VOL_START
, BKPT_DET.GROUP_BKPT_VOL_START
, GD.PRORATION_FACTOR) BKPT_VOL_START
, PN_VARIABLE_AMOUNT_PKG.GET_PRORATED_BKPT( VR.CUMULATIVE_VOL
, GD.GRP_START_DATE
, GD.GRP_END_DATE
, PER.START_DATE
, PER.END_DATE
, BKPT_DET.PERIOD_BKPT_VOL_END
, BKPT_DET.GROUP_BKPT_VOL_END
, GD.PRORATION_FACTOR) BKPT_VOL_END
, DECODE(BKPT_DET.BREAKPOINT_TYPE
, 'SLIDING'
, SUMM.TOT_FOR_VOL
, (SUMM.TOT_FOR_VOL- PN_VARIABLE_AMOUNT_PKG.GET_PRORATED_BKPT( VR.CUMULATIVE_VOL
, GD.GRP_START_DATE
, GD.GRP_END_DATE
, PER.START_DATE
, PER.END_DATE
, BKPT_DET.PERIOD_BKPT_VOL_START
, BKPT_DET.GROUP_BKPT_VOL_START
, GD.PRORATION_FACTOR)) ) VOL_APPL
, BKPT_DET.BKPT_RATE
, DECODE(BKPT_DET.BREAKPOINT_TYPE
, 'SLIDING'
, SUMM.TOT_FOR_VOL
, (SUMM.TOT_FOR_VOL- PN_VARIABLE_AMOUNT_PKG.GET_PRORATED_BKPT( VR.CUMULATIVE_VOL
, GD.GRP_START_DATE
, GD.GRP_END_DATE
, PER.START_DATE
, PER.END_DATE
, BKPT_DET.PERIOD_BKPT_VOL_START
, BKPT_DET.GROUP_BKPT_VOL_START
, GD.PRORATION_FACTOR)) ) * BKPT_DET.BKPT_RATE VAR_RENT
, GD.ACTUAL_EXP_CODE
, GD.FORECASTED_EXP_CODE
, GD.VARIANCE_EXP_CODE
, 'FORECASTED' VAR_RENT_TYPE
, VR.ORG_ID
FROM PN_VAR_RENTS VR
, PN_VAR_PERIODS_ALL PER
, PN_VAR_GRP_DATES_ALL GD
, PN_VAR_RENT_SUMM_ALL SUMM
, (SELECT DET.PERIOD_BKPT_VOL_START
, DET.GROUP_BKPT_VOL_START
, DET.GROUP_BKPT_VOL_END
, DET.PERIOD_BKPT_VOL_END
, DET.BKPT_RATE
, HEAD.BREAKPOINT_TYPE
, HEAD.LINE_ITEM_ID
FROM PN_VAR_BKPTS_DET_ALL DET
, PN_VAR_BKPTS_HEAD HEAD
WHERE DET.BKPT_HEADER_ID = HEAD.BKPT_HEADER_ID ) BKPT_DET
WHERE VR.VAR_RENT_ID = PER.VAR_RENT_ID
AND PER.PERIOD_ID = GD.PERIOD_ID
AND SUMM.PERIOD_ID = PER.PERIOD_ID
AND SUMM.GRP_DATE_ID = GD.GRP_DATE_ID
AND SUMM.PERIOD_ID = GD.PERIOD_ID
AND BKPT_DET.LINE_ITEM_ID = SUMM.LINE_ITEM_ID
AND VR.INVOICE_ON = 'FORECASTED'
AND PN_VARIABLE_AMOUNT_PKG.GET_PRORATED_BKPT(VR.CUMULATIVE_VOL
, GD.GRP_START_DATE
, GD.GRP_END_DATE
, PER.START_DATE
, PER.END_DATE
, BKPT_DET.PERIOD_BKPT_VOL_START
, BKPT_DET.GROUP_BKPT_VOL_START
, GD.PRORATION_FACTOR) < SUMM.TOT_FOR_VOL
AND NVL(PN_VARIABLE_AMOUNT_PKG.GET_PRORATED_BKPT( VR.CUMULATIVE_VOL
, GD.GRP_START_DATE
, GD.GRP_END_DATE
, PER.START_DATE
, PER.END_DATE
, BKPT_DET.PERIOD_BKPT_VOL_END
, BKPT_DET.GROUP_BKPT_VOL_END
, GD.PRORATION_FACTOR)
, TOT_FOR_VOL) >= SUMM.TOT_FOR_VOL