[Home] [Help]
View: PN_FOR_RENT_DETAILS_V
Product: | PN - Property Manager |
Description: | View for viewing actual variable rent details by line item and group date. |
Implementation/DBA Data: |
APPS.PN_FOR_RENT_DETAILS_V
|
View Text
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
Columns
Name |
GROUP_DATE |
INVOICE_DATE |
LINE_ITEM_ID |
TOT_VOL |
BKPT_VOL_START |
BKPT_VOL_END |
VOL_APPL |
BKPT_RATE |
VAR_RENT |
ACTUAL_EXP_CODE |
FORECASTED_EXP_CODE |
VARIANCE_EXP_CODE |
VAR_RENT_TYPE |
ORG_ID |