View: PA_FIN_PLAN_ADJ_TOTAL_V

Product:	PA - Projects
Description:	The pa_fin_plan_adj_total_v view store the total adjusted amounts by period for one adjustment reason.
Implementation/DBA Data:	APPS.PA_FIN_PLAN_ADJ_TOTAL_V

View Text

SELECT B.AMOUNT_TYPE_ID
, B.AMOUNT_TYPE_NAME
, B.AMOUNT_TYPE_CODE
, B.AMOUNT_TYPE_CODE||'_ADJ' AS AMOUNT_SUBTYPE_CODE
, NVL(A.PRECEDING_PERIODS_AMOUNT
, 0)
, NVL(A.SUCCEEDING_PERIODS_AMOUNT
, 0)
, NVL(A.PERIOD_AMOUNT1
, 0)
, NVL(A.PERIOD_AMOUNT2
, 0)
, NVL(A.PERIOD_AMOUNT3
, 0)
, NVL(A.PERIOD_AMOUNT4
, 0)
, NVL(A.PERIOD_AMOUNT5
, 0)
, NVL(A.PERIOD_AMOUNT6
, 0)
, NVL(A.PERIOD_AMOUNT7
, 0)
, NVL(A.PERIOD_AMOUNT8
, 0)
, NVL(A.PERIOD_AMOUNT9
, 0)
, NVL(A.PERIOD_AMOUNT10
, 0)
, NVL(A.PERIOD_AMOUNT11
, 0)
, NVL(A.PERIOD_AMOUNT12
, 0)
, NVL(A.PERIOD_AMOUNT13
, 0)
, (NVL(A.PERIOD_AMOUNT1
, 0)+ NVL(A.PERIOD_AMOUNT2
, 0)+ NVL(A.PERIOD_AMOUNT3
, 0)+ NVL(A.PERIOD_AMOUNT4
, 0)+ NVL(A.PERIOD_AMOUNT5
, 0)+ NVL(A.PERIOD_AMOUNT6
, 0)) AS TOTAL
, (NVL(A.PERIOD_AMOUNT1
, 0)+ NVL(A.PERIOD_AMOUNT2
, 0)+ NVL(A.PERIOD_AMOUNT3
, 0)+ NVL(A.PERIOD_AMOUNT4
, 0)+ NVL(A.PERIOD_AMOUNT5
, 0)+ NVL(A.PERIOD_AMOUNT6
, 0)+ NVL(A.PERIOD_AMOUNT7
, 0)+ NVL(A.PERIOD_AMOUNT8
, 0)+ NVL(A.PERIOD_AMOUNT9
, 0)+ NVL(A.PERIOD_AMOUNT10
, 0)+ NVL(A.PERIOD_AMOUNT11
, 0)+ NVL(A.PERIOD_AMOUNT12
, 0)+ NVL(A.PERIOD_AMOUNT13
, 0)) AS TOTAL_PA
FROM PA_FP_PERIOD_VALUES_V A
, PA_AMOUNT_TYPES_VL B
, PA_RESOURCE_ASSIGNMENTS E
, PA_AMOUNT_TYPES_VL D
WHERE E.BUDGET_VERSION_ID = PA_FIN_PLAN_VIEW_GLOBAL.GET_VERSION_ID()
AND E.RESOURCE_ASSIGNMENT_TYPE = 'PROJECT'
AND A.BUDGET_VERSION_ID = PA_FIN_PLAN_VIEW_GLOBAL.GET_VERSION_ID()
AND A.RESOURCE_ASSIGNMENT_ID= E.RESOURCE_ASSIGNMENT_ID
AND A.OBJECT_TYPE_CODE ='RES_ASSIGNMENT'
AND A.OBJECT_ID = E.RESOURCE_ASSIGNMENT_ID
AND B.AMOUNT_TYPE_CODE = A.AMOUNT_TYPE_CODE
AND B.PLAN_ADJUSTABLE_FLAG = 'Y'
AND D.AMOUNT_TYPE_CODE = A.AMOUNT_SUBTYPE_CODE
AND D.PLAN_ADJ_AMOUNT_FLAG = 'Y' UNION SELECT B.AMOUNT_TYPE_ID
, B.AMOUNT_TYPE_NAME
, B.AMOUNT_TYPE_CODE
, B.AMOUNT_TYPE_CODE||'_ADJ' AS AMOUNT_SUBTYPE_CODE
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
FROM PA_AMOUNT_TYPES_VL B
WHERE B.PLAN_ADJUSTABLE_FLAG = 'Y'
AND NOT EXISTS (SELECT 'X'
FROM PA_FP_PERIOD_VALUES_V A
, PA_AMOUNT_TYPES_VL D
, PA_RESOURCE_ASSIGNMENTS E WHERE E.RESOURCE_ASSIGNMENT_TYPE = 'PROJECT'
AND E.BUDGET_VERSION_ID = PA_FIN_PLAN_VIEW_GLOBAL.GET_VERSION_ID()
AND A.BUDGET_VERSION_ID = PA_FIN_PLAN_VIEW_GLOBAL.GET_VERSION_ID()
AND B.AMOUNT_TYPE_CODE = A.AMOUNT_TYPE_CODE
AND D.AMOUNT_TYPE_CODE = A.AMOUNT_SUBTYPE_CODE
AND D.PLAN_ADJ_AMOUNT_FLAG = 'Y'
AND A.RESOURCE_ASSIGNMENT_ID= E.RESOURCE_ASSIGNMENT_ID
AND A.OBJECT_ID = E.RESOURCE_ASSIGNMENT_ID
AND A.OBJECT_TYPE_CODE ='RES_ASSIGNMENT') ORDER BY 1

Columns