VIEW: APPS.PN_ACT_RENT_DETAILS_V

Source

View Text - Preformatted

SELECT gd.group_date ,gd.invoice_date ,summ.line_item_id ,(summ.tot_act_vol - nvl(summ.tot_ded,0)) 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 ,'ACTUAL' 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 det.bkpt_header_id = head.bkpt_header_id and head.breakpoint_type = 'STRATIFIED' ) 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 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_act_vol- nvl(summ.tot_ded,0)) 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_act_vol- nvl(summ.tot_ded,0))) < (summ.tot_act_vol- nvl(summ.tot_ded,0)) UNION ALL SELECT gd.group_date ,gd.invoice_date ,summ.line_item_id ,(summ.tot_act_vol - nvl(summ.tot_ded,0)) 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) ,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_act_vol- nvl(summ.tot_ded,0)), ( (summ.tot_act_vol- nvl(summ.tot_ded,0))- (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_act_vol- nvl(summ.tot_ded,0)), ( (summ.tot_act_vol- nvl(summ.tot_ded,0))- (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 ,'ACTUAL' 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 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 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_act_vol- nvl(summ.tot_ded,0)) 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_act_vol - nvl(summ.tot_ded,0))) > = (summ.tot_act_vol- nvl(summ.tot_ded,0))

View Text - HTML Formatted

SELECT GD.GROUP_DATE
, GD.INVOICE_DATE
, SUMM.LINE_ITEM_ID
, (SUMM.TOT_ACT_VOL - NVL(SUMM.TOT_DED
, 0)) 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
, 'ACTUAL' 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 DET.BKPT_HEADER_ID = HEAD.BKPT_HEADER_ID
AND HEAD.BREAKPOINT_TYPE = 'STRATIFIED' ) 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 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_ACT_VOL- NVL(SUMM.TOT_DED
, 0))
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_ACT_VOL- NVL(SUMM.TOT_DED
, 0))) < (SUMM.TOT_ACT_VOL- NVL(SUMM.TOT_DED
, 0)) UNION ALL SELECT GD.GROUP_DATE
, GD.INVOICE_DATE
, SUMM.LINE_ITEM_ID
, (SUMM.TOT_ACT_VOL - NVL(SUMM.TOT_DED
, 0)) 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)
, 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_ACT_VOL- NVL(SUMM.TOT_DED
, 0))
, ( (SUMM.TOT_ACT_VOL- NVL(SUMM.TOT_DED
, 0))- (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_ACT_VOL- NVL(SUMM.TOT_DED
, 0))
, ( (SUMM.TOT_ACT_VOL- NVL(SUMM.TOT_DED
, 0))- (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
, 'ACTUAL' 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 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 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_ACT_VOL- NVL(SUMM.TOT_DED
, 0))
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_ACT_VOL - NVL(SUMM.TOT_DED
, 0))) > = (SUMM.TOT_ACT_VOL- NVL(SUMM.TOT_DED
, 0))