[Home] [Help]
View: PN_ACT_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_ACT_RENT_DETAILS_V
|
View Text
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))
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 |