Product: | PA - Projects |
---|---|
Description: | This view is used for data to be downloaded to excel sheet for Financial Planning. |
Implementation/DBA Data: | APPS.PA_FP_WEBADI_CONTENTS_V |
SELECT PRA.BUDGET_VERSION_ID
, PPFO.FIN_PLAN_PREFERENCE_CODE
, PP.NAME
, PRA.RESOURCE_ASSIGNMENT_ID
, PTK.TASK_NUMBER
, PTK.TASK_NAME
, DECODE(PRLM2.PARENT_MEMBER_ID
, NULL
, PRLM2.ALIAS
, PRLM1.ALIAS)
, DECODE(PRLM2.PARENT_MEMBER_ID
, NULL
, DECODE(PRL.GROUP_RESOURCE_TYPE_ID
, 0
, PRLM2.ALIAS
, NULL)
, PRLM2.ALIAS)
, TO_DATE(NULL)
, TO_DATE(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, PMVL.AMOUNT_TYPE_NAME
, P.AMOUNT_TYPE_CODE
, P.AMOUNT_SUBTYPE_CODE
, PMVL.AMOUNT_TYPE_ID
, P.CURRENCY_CODE
, P.PRECEDING_PERIODS_AMOUNT
, P.SUCCEEDING_PERIODS_AMOUNT
, P.PERIOD_AMOUNT1
, P.PERIOD_AMOUNT2
, P.PERIOD_AMOUNT3
, P.PERIOD_AMOUNT4
, P.PERIOD_AMOUNT5
, P.PERIOD_AMOUNT6
, P.PERIOD_AMOUNT7
, P.PERIOD_AMOUNT8
, P.PERIOD_AMOUNT9
, P.PERIOD_AMOUNT10
, P.PERIOD_AMOUNT11
, P.PERIOD_AMOUNT12
, P.PERIOD_AMOUNT13
, P.PERIOD_AMOUNT14
, P.PERIOD_AMOUNT15
, P.PERIOD_AMOUNT16
, P.PERIOD_AMOUNT17
, P.PERIOD_AMOUNT18
, P.PERIOD_AMOUNT19
, P.PERIOD_AMOUNT20
, P.PERIOD_AMOUNT21
, P.PERIOD_AMOUNT22
, P.PERIOD_AMOUNT23
, P.PERIOD_AMOUNT24
, P.PERIOD_AMOUNT25
, P.PERIOD_AMOUNT26
, P.PERIOD_AMOUNT27
, P.PERIOD_AMOUNT28
, P.PERIOD_AMOUNT29
, P.PERIOD_AMOUNT30
, P.PERIOD_AMOUNT31
, P.PERIOD_AMOUNT32
, P.PERIOD_AMOUNT33
, P.PERIOD_AMOUNT34
, P.PERIOD_AMOUNT35
, P.PERIOD_AMOUNT36
, P.PERIOD_AMOUNT37
, P.PERIOD_AMOUNT38
, P.PERIOD_AMOUNT39
, P.PERIOD_AMOUNT40
, P.PERIOD_AMOUNT41
, P.PERIOD_AMOUNT42
, P.PERIOD_AMOUNT43
, P.PERIOD_AMOUNT44
, P.PERIOD_AMOUNT45
, P.PERIOD_AMOUNT46
, P.PERIOD_AMOUNT47
, P.PERIOD_AMOUNT48
, P.PERIOD_AMOUNT49
, P.PERIOD_AMOUNT50
, P.PERIOD_AMOUNT51
, P.PERIOD_AMOUNT52
, DECODE(NVL(PRA.UNIT_OF_MEASURE
, 'NONE')
, 'NONE'
, NULL
, (SELECT MEANING
FROM PA_LOOKUPS
WHERE LOOKUP_TYPE = 'UNIT'
AND LOOKUP_CODE = NVL(PRA.UNIT_OF_MEASURE
, 'HOURS')))
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, TO_NUMBER(NULL)
, TO_DATE(NULL)
, TO_NUMBER(NULL)
, TO_DATE(NULL)
, TO_NUMBER(NULL)
, TO_DATE(NULL)
, TO_NUMBER(NULL)
, TO_DATE(NULL)
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, DECODE(DECODE(P.AMOUNT_SUBTYPE_CODE
, 'RAW_COST'
, PPFO.PROJFUNC_COST_RATE_TYPE
, 'BURDENED_COST'
, PPFO.PROJFUNC_COST_RATE_TYPE
, 'REVENUE'
, PPFO.PROJFUNC_REV_RATE_TYPE
, NULL)
, NULL
, NULL
, (SELECT PCTV1.USER_CONVERSION_TYPE
FROM PA_CONVERSION_TYPES_V PCTV1
WHERE PCTV1.CONVERSION_TYPE= NVL(DECODE(P.AMOUNT_SUBTYPE_CODE
, 'RAW_COST'
, PPFO.PROJFUNC_COST_RATE_TYPE
, 'BURDENED_COST'
, PPFO.PROJFUNC_COST_RATE_TYPE
, 'REVENUE'
, PPFO.PROJFUNC_REV_RATE_TYPE
, NULL)
, 'CORPORATE')) )
, DECODE(DECODE(P.AMOUNT_SUBTYPE_CODE
, 'RAW_COST'
, DECODE(PPFO.PROJFUNC_COST_RATE_TYPE
, 'USER'
, NULL
, PPFO.PROJFUNC_COST_RATE_DATE_TYPE)
, 'BURDENED_COST'
, DECODE(PPFO.PROJFUNC_COST_RATE_TYPE
, 'USER'
, NULL
, PPFO.PROJFUNC_COST_RATE_DATE_TYPE)
, 'REVENUE'
, DECODE(PPFO.PROJFUNC_REV_RATE_TYPE
, 'USER'
, NULL
, PPFO.PROJFUNC_REV_RATE_DATE_TYPE)
, NULL )
, NULL
, NULL
, (SELECT PLK_D1.MEANING
FROM PA_LOOKUPS PLK_D1
WHERE PLK_D1.LOOKUP_TYPE='PA_FP_RATE_DATE_TYPE'
AND PLK_D1.LOOKUP_CODE=NVL(DECODE(P.AMOUNT_SUBTYPE_CODE
, 'RAW_COST'
, DECODE(PPFO.PROJFUNC_COST_RATE_TYPE
, 'USER'
, NULL
, PPFO.PROJFUNC_COST_RATE_DATE_TYPE)
, 'BURDENED_COST'
, DECODE(PPFO.PROJFUNC_COST_RATE_TYPE
, 'USER'
, NULL
, PPFO.PROJFUNC_COST_RATE_DATE_TYPE)
, 'REVENUE'
, DECODE(PPFO.PROJFUNC_REV_RATE_TYPE
, 'USER'
, NULL
, PPFO.PROJFUNC_REV_RATE_DATE_TYPE)
, NULL)
, 'FIXED_DATE')) )
, DECODE(DECODE(P.AMOUNT_SUBTYPE_CODE
, 'RAW_COST'
, PPFO.PROJECT_COST_RATE_TYPE
, 'BURDENED_COST'
, PPFO.PROJECT_COST_RATE_TYPE
, 'REVENUE'
, PPFO.PROJECT_REV_RATE_TYPE
, NULL)
, NULL
, NULL
, (SELECT PCTV2.USER_CONVERSION_TYPE
FROM PA_CONVERSION_TYPES_V PCTV2
WHERE PCTV2.CONVERSION_TYPE= NVL(DECODE(P.AMOUNT_SUBTYPE_CODE
, 'RAW_COST'
, PPFO.PROJECT_COST_RATE_TYPE
, 'BURDENED_COST'
, PPFO.PROJECT_COST_RATE_TYPE
, 'REVENUE'
, PPFO.PROJECT_REV_RATE_TYPE
, NULL)
, 'CORPORATE')) )
, DECODE(DECODE(P.AMOUNT_SUBTYPE_CODE
, 'RAW_COST'
, DECODE(PPFO.PROJECT_COST_RATE_TYPE
, 'USER'
, NULL
, PPFO.PROJECT_COST_RATE_DATE_TYPE)
, 'BURDENED_COST'
, DECODE(PPFO.PROJECT_COST_RATE_TYPE
, 'USER'
, NULL
, PPFO.PROJECT_COST_RATE_DATE_TYPE)
, 'REVENUE'
, DECODE(PPFO.PROJECT_REV_RATE_TYPE
, 'USER'
, NULL
, PPFO.PROJECT_REV_RATE_DATE_TYPE)
, NULL )
, NULL
, NULL
, (SELECT PLK_D2.MEANING
FROM PA_LOOKUPS PLK_D2
WHERE PLK_D2.LOOKUP_TYPE='PA_FP_RATE_DATE_TYPE'
AND PLK_D2.LOOKUP_CODE=NVL(DECODE(P.AMOUNT_SUBTYPE_CODE
, 'RAW_COST'
, DECODE(PPFO.PROJECT_COST_RATE_TYPE
, 'USER'
, NULL
, PPFO.PROJECT_COST_RATE_DATE_TYPE)
, 'BURDENED_COST'
, DECODE(PPFO.PROJECT_COST_RATE_TYPE
, 'USER'
, NULL
, PPFO.PROJECT_COST_RATE_DATE_TYPE)
, 'REVENUE'
, DECODE(PPFO.PROJECT_REV_RATE_TYPE
, 'USER'
, NULL
, PPFO.PROJECT_REV_RATE_DATE_TYPE)
, NULL)
, 'FIXED_DATE')) )
, DECODE(P.AMOUNT_SUBTYPE_CODE
, 'RAW_COST'
, DECODE(PPFO.PROJFUNC_COST_RATE_TYPE
, 'USER'
, PFTC.PROJFUNC_COST_EXCHANGE_RATE
, TO_NUMBER(NULL))
, 'BURDENED_COST'
, DECODE(PPFO.PROJFUNC_COST_RATE_TYPE
, 'USER'
, PFTC.PROJFUNC_COST_EXCHANGE_RATE
, TO_NUMBER(NULL))
, 'REVENUE'
, DECODE(PPFO.PROJFUNC_REV_RATE_TYPE
, 'USER'
, PFTC.PROJFUNC_REV_EXCHANGE_RATE
, TO_NUMBER(NULL))
, NULL )
, DECODE(P.AMOUNT_SUBTYPE_CODE
, 'RAW_COST'
, DECODE(PPFO.PROJFUNC_COST_RATE_DATE_TYPE
, 'FIXED_DATE'
, PPFO.PROJFUNC_COST_RATE_DATE
, TO_DATE(NULL))
, 'BURDENED_COST'
, DECODE(PPFO.PROJFUNC_COST_RATE_DATE_TYPE
, 'FIXED_DATE'
, PPFO.PROJFUNC_COST_RATE_DATE
, TO_DATE(NULL))
, 'REVENUE'
, DECODE(PPFO.PROJFUNC_REV_RATE_DATE_TYPE
, 'FIXED_DATE'
, PPFO.PROJFUNC_REV_RATE_DATE
, TO_DATE(NULL))
, NULL )
, DECODE(P.AMOUNT_SUBTYPE_CODE
, 'RAW_COST'
, DECODE(PPFO.PROJECT_COST_RATE_TYPE
, 'USER'
, PFTC.PROJECT_COST_EXCHANGE_RATE
, TO_NUMBER(NULL))
, 'BURDENED_COST'
, DECODE(PPFO.PROJECT_COST_RATE_TYPE
, 'USER'
, PFTC.PROJECT_COST_EXCHANGE_RATE
, TO_NUMBER(NULL))
, 'REVENUE'
, DECODE(PPFO.PROJECT_REV_RATE_TYPE
, 'USER'
, PFTC.PROJECT_REV_EXCHANGE_RATE
, TO_NUMBER(NULL))
, NULL )
, DECODE(P.AMOUNT_SUBTYPE_CODE
, 'RAW_COST'
, DECODE(PPFO.PROJECT_COST_RATE_DATE_TYPE
, 'FIXED_DATE'
, PPFO.PROJECT_COST_RATE_DATE
, TO_DATE(NULL))
, 'BURDENED_COST'
, DECODE(PPFO.PROJECT_COST_RATE_DATE_TYPE
, 'FIXED_DATE'
, PPFO.PROJECT_COST_RATE_DATE
, TO_DATE(NULL))
, 'REVENUE'
, DECODE(PPFO.PROJECT_REV_RATE_DATE_TYPE
, 'FIXED_DATE'
, PPFO.PROJECT_REV_RATE_DATE
, TO_DATE(NULL))
, NULL )
FROM PA_BUDGET_VERSIONS PBV
, PA_PROJ_FP_OPTIONS PPFO
, PA_PROJECTS_ALL PP
, PA_PROJ_PERIODS_DENORM P
, PA_AMOUNT_TYPES_VL PMVL
, PA_RESOURCE_LISTS PRL
, PA_RESOURCE_ASSIGNMENTS PRA
, PA_TASKS PTK
, PA_FP_TXN_CURRENCIES PFTC
, PA_RESOURCE_LIST_MEMBERS PRLM1
, PA_RESOURCE_LIST_MEMBERS PRLM2
WHERE PPFO.FIN_PLAN_VERSION_ID =PRA.BUDGET_VERSION_ID
AND PPFO.FIN_PLAN_OPTION_LEVEL_CODE = 'PLAN_VERSION'
AND PPFO.PROJECT_ID = PRA.PROJECT_ID
AND PPFO.FIN_PLAN_TYPE_ID = PBV.FIN_PLAN_TYPE_ID
AND PA_FIN_PLAN_UTILS.GET_TIME_PHASED_CODE(PRA.BUDGET_VERSION_ID) IN ('P'
, 'G')
AND PP.PROJECT_ID = PRA.PROJECT_ID
AND PRA.TASK_ID = PTK.TASK_ID(+)
AND PRLM2.PARENT_MEMBER_ID = PRLM1.RESOURCE_LIST_MEMBER_ID(+)
AND PRLM2.RESOURCE_LIST_MEMBER_ID = PRA.RESOURCE_LIST_MEMBER_ID
AND PMVL.AMOUNT_TYPE_ID=P.AMOUNT_SUBTYPE_ID
AND P.BUDGET_VERSION_ID=PRA.BUDGET_VERSION_ID
AND P.OBJECT_ID=PRA.RESOURCE_ASSIGNMENT_ID
AND P.PERIOD_PROFILE_ID=PBV.PERIOD_PROFILE_ID
AND PRA.RESOURCE_ASSIGNMENT_ID = P.RESOURCE_ASSIGNMENT_ID
AND PRA.RESOURCE_ASSIGNMENT_TYPE = 'USER_ENTERED'
AND P.OBJECT_TYPE_CODE='RES_ASSIGNMENT'
AND P.CURRENCY_TYPE = 'TRANSACTION'
AND PFTC.TXN_CURRENCY_CODE = P.CURRENCY_CODE
AND PFTC.FIN_PLAN_VERSION_ID = PPFO.FIN_PLAN_VERSION_ID
AND PFTC.PROJ_FP_OPTIONS_ID = PPFO.PROJ_FP_OPTIONS_ID
AND PBV.BUDGET_VERSION_ID = PRA.BUDGET_VERSION_ID
AND PRL.RESOURCE_LIST_ID = PBV.RESOURCE_LIST_ID UNION SELECT PRA.BUDGET_VERSION_ID
, PPFO.FIN_PLAN_PREFERENCE_CODE
, PP.NAME
, PRA.RESOURCE_ASSIGNMENT_ID
, PTK.TASK_NUMBER
, PTK.TASK_NAME
, DECODE(PRLM2.PARENT_MEMBER_ID
, NULL
, PRLM2.ALIAS
, PRLM1.ALIAS)
, DECODE(PRLM2.PARENT_MEMBER_ID
, NULL
, DECODE(PRL.GROUP_RESOURCE_TYPE_ID
, 0
, PRLM2.ALIAS
, NULL)
, PRLM2.ALIAS)
, TO_DATE(NULL)
, TO_DATE(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, PMVL.AMOUNT_TYPE_NAME
, NULL
, PMVL.AMOUNT_TYPE_CODE
, PMVL.AMOUNT_TYPE_ID
, PFTC.TXN_CURRENCY_CODE
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, DECODE(NVL(PRA.UNIT_OF_MEASURE
, 'NONE')
, 'NONE'
, NULL
, (SELECT MEANING
FROM PA_LOOKUPS
WHERE LOOKUP_TYPE = 'UNIT'
AND LOOKUP_CODE = NVL(PRA.UNIT_OF_MEASURE
, 'HOURS')))
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, TO_NUMBER(NULL)
, TO_DATE(NULL)
, TO_NUMBER(NULL)
, TO_DATE(NULL)
, TO_NUMBER(NULL)
, TO_DATE(NULL)
, TO_NUMBER(NULL)
, TO_DATE(NULL)
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, DECODE(DECODE(PMVL.AMOUNT_TYPE_CODE
, 'RAW_COST'
, PPFO.PROJFUNC_COST_RATE_TYPE
, 'BURDENED_COST'
, PPFO.PROJFUNC_COST_RATE_TYPE
, 'REVENUE'
, PPFO.PROJFUNC_REV_RATE_TYPE
, NULL)
, NULL
, NULL
, (SELECT PCTV1.USER_CONVERSION_TYPE
FROM PA_CONVERSION_TYPES_V PCTV1
WHERE PCTV1.CONVERSION_TYPE= NVL(DECODE(PMVL.AMOUNT_TYPE_CODE
, 'RAW_COST'
, PPFO.PROJFUNC_COST_RATE_TYPE
, 'BURDENED_COST'
, PPFO.PROJFUNC_COST_RATE_TYPE
, 'REVENUE'
, PPFO.PROJFUNC_REV_RATE_TYPE
, NULL)
, 'CORPORATE')) )
, DECODE(DECODE(PMVL.AMOUNT_TYPE_CODE
, 'RAW_COST'
, DECODE(PPFO.PROJFUNC_COST_RATE_TYPE
, 'USER'
, NULL
, PPFO.PROJFUNC_COST_RATE_DATE_TYPE)
, 'BURDENED_COST'
, DECODE(PPFO.PROJFUNC_COST_RATE_TYPE
, 'USER'
, NULL
, PPFO.PROJFUNC_COST_RATE_DATE_TYPE)
, 'REVENUE'
, DECODE(PPFO.PROJFUNC_REV_RATE_TYPE
, 'USER'
, NULL
, PPFO.PROJFUNC_REV_RATE_DATE_TYPE)
, NULL )
, NULL
, NULL
, (SELECT PLK_D1.MEANING
FROM PA_LOOKUPS PLK_D1
WHERE PLK_D1.LOOKUP_TYPE='PA_FP_RATE_DATE_TYPE'
AND PLK_D1.LOOKUP_CODE=NVL(DECODE(PMVL.AMOUNT_TYPE_CODE
, 'RAW_COST'
, DECODE(PPFO.PROJFUNC_COST_RATE_TYPE
, 'USER'
, NULL
, PPFO.PROJFUNC_COST_RATE_DATE_TYPE)
, 'BURDENED_COST'
, DECODE(PPFO.PROJFUNC_COST_RATE_TYPE
, 'USER'
, NULL
, PPFO.PROJFUNC_COST_RATE_DATE_TYPE)
, 'REVENUE'
, DECODE(PPFO.PROJFUNC_REV_RATE_TYPE
, 'USER'
, NULL
, PPFO.PROJFUNC_REV_RATE_DATE_TYPE)
, NULL)
, 'FIXED_DATE')) )
, DECODE(DECODE(PMVL.AMOUNT_TYPE_CODE
, 'RAW_COST'
, PPFO.PROJECT_COST_RATE_TYPE
, 'BURDENED_COST'
, PPFO.PROJECT_COST_RATE_TYPE
, 'REVENUE'
, PPFO.PROJECT_REV_RATE_TYPE
, NULL)
, NULL
, NULL
, (SELECT PCTV2.USER_CONVERSION_TYPE
FROM PA_CONVERSION_TYPES_V PCTV2
WHERE PCTV2.CONVERSION_TYPE= NVL(DECODE(PMVL.AMOUNT_TYPE_CODE
, 'RAW_COST'
, PPFO.PROJECT_COST_RATE_TYPE
, 'BURDENED_COST'
, PPFO.PROJECT_COST_RATE_TYPE
, 'REVENUE'
, PPFO.PROJECT_REV_RATE_TYPE
, NULL)
, 'CORPORATE')) )
, DECODE(DECODE(PMVL.AMOUNT_TYPE_CODE
, 'RAW_COST'
, DECODE(PPFO.PROJECT_COST_RATE_TYPE
, 'USER'
, NULL
, PPFO.PROJECT_COST_RATE_DATE_TYPE)
, 'BURDENED_COST'
, DECODE(PPFO.PROJECT_COST_RATE_TYPE
, 'USER'
, NULL
, PPFO.PROJECT_COST_RATE_DATE_TYPE)
, 'REVENUE'
, DECODE(PPFO.PROJECT_REV_RATE_TYPE
, 'USER'
, NULL
, PPFO.PROJECT_REV_RATE_DATE_TYPE)
, NULL )
, NULL
, NULL
, (SELECT PLK_D2.MEANING
FROM PA_LOOKUPS PLK_D2
WHERE PLK_D2.LOOKUP_TYPE='PA_FP_RATE_DATE_TYPE'
AND PLK_D2.LOOKUP_CODE=NVL(DECODE(PMVL.AMOUNT_TYPE_CODE
, 'RAW_COST'
, DECODE(PPFO.PROJECT_COST_RATE_TYPE
, 'USER'
, NULL
, PPFO.PROJECT_COST_RATE_DATE_TYPE)
, 'BURDENED_COST'
, DECODE(PPFO.PROJECT_COST_RATE_TYPE
, 'USER'
, NULL
, PPFO.PROJECT_COST_RATE_DATE_TYPE)
, 'REVENUE'
, DECODE(PPFO.PROJECT_REV_RATE_TYPE
, 'USER'
, NULL
, PPFO.PROJECT_REV_RATE_DATE_TYPE)
, NULL)
, 'FIXED_DATE')) )
, DECODE(PMVL.AMOUNT_TYPE_CODE
, 'RAW_COST'
, DECODE(PPFO.PROJFUNC_COST_RATE_TYPE
, 'USER'
, PFTC.PROJFUNC_COST_EXCHANGE_RATE
, TO_NUMBER(NULL))
, 'BURDENED_COST'
, DECODE(PPFO.PROJFUNC_COST_RATE_TYPE
, 'USER'
, PFTC.PROJFUNC_COST_EXCHANGE_RATE
, TO_NUMBER(NULL))
, 'REVENUE'
, DECODE(PPFO.PROJFUNC_REV_RATE_TYPE
, 'USER'
, PFTC.PROJFUNC_REV_EXCHANGE_RATE
, TO_NUMBER(NULL))
, NULL )
, DECODE(PMVL.AMOUNT_TYPE_CODE
, 'RAW_COST'
, DECODE(PPFO.PROJFUNC_COST_RATE_DATE_TYPE
, 'FIXED_DATE'
, PPFO.PROJFUNC_COST_RATE_DATE
, TO_DATE(NULL))
, 'BURDENED_COST'
, DECODE(PPFO.PROJFUNC_COST_RATE_DATE_TYPE
, 'FIXED_DATE'
, PPFO.PROJFUNC_COST_RATE_DATE
, TO_DATE(NULL))
, 'REVENUE'
, DECODE(PPFO.PROJFUNC_REV_RATE_DATE_TYPE
, 'FIXED_DATE'
, PPFO.PROJFUNC_REV_RATE_DATE
, TO_DATE(NULL))
, NULL )
, DECODE(PMVL.AMOUNT_TYPE_CODE
, 'RAW_COST'
, DECODE(PPFO.PROJECT_COST_RATE_TYPE
, 'USER'
, PFTC.PROJECT_COST_EXCHANGE_RATE
, TO_NUMBER(NULL))
, 'BURDENED_COST'
, DECODE(PPFO.PROJECT_COST_RATE_TYPE
, 'USER'
, PFTC.PROJECT_COST_EXCHANGE_RATE
, TO_NUMBER(NULL))
, 'REVENUE'
, DECODE(PPFO.PROJECT_REV_RATE_TYPE
, 'USER'
, PFTC.PROJECT_REV_EXCHANGE_RATE
, TO_NUMBER(NULL))
, NULL )
, DECODE(PMVL.AMOUNT_TYPE_CODE
, 'RAW_COST'
, DECODE(PPFO.PROJECT_COST_RATE_DATE_TYPE
, 'FIXED_DATE'
, PPFO.PROJECT_COST_RATE_DATE
, TO_DATE(NULL))
, 'BURDENED_COST'
, DECODE(PPFO.PROJECT_COST_RATE_DATE_TYPE
, 'FIXED_DATE'
, PPFO.PROJECT_COST_RATE_DATE
, TO_DATE(NULL))
, 'REVENUE'
, DECODE(PPFO.PROJECT_REV_RATE_DATE_TYPE
, 'FIXED_DATE'
, PPFO.PROJECT_REV_RATE_DATE
, TO_DATE(NULL))
, NULL )
FROM PA_PROJ_FP_OPTIONS PPFO
, PA_PROJECTS_ALL PP
, PA_RESOURCE_ASSIGNMENTS PRA
, PA_TASKS PTK
, PA_RESOURCE_LISTS PRL
, PA_BUDGET_VERSIONS PBV
, PA_FP_TXN_CURRENCIES PFTC
, PA_AMOUNT_TYPES_VL PMVL
, PA_RESOURCE_LIST_MEMBERS PRLM1
, PA_RESOURCE_LIST_MEMBERS PRLM2
WHERE PPFO.FIN_PLAN_VERSION_ID=PRA.BUDGET_VERSION_ID
AND PPFO.PROJECT_ID = PRA.PROJECT_ID
AND PPFO.FIN_PLAN_TYPE_ID = PBV.FIN_PLAN_TYPE_ID
AND PPFO.FIN_PLAN_OPTION_LEVEL_CODE = 'PLAN_VERSION'
AND PA_FIN_PLAN_UTILS.GET_TIME_PHASED_CODE(PRA.BUDGET_VERSION_ID) IN ('P'
, 'G')
AND PP.PROJECT_ID = PRA.PROJECT_ID
AND PRA.RESOURCE_ASSIGNMENT_ID NOT IN ( SELECT PBL.RESOURCE_ASSIGNMENT_ID
FROM PA_BUDGET_LINES PBL
WHERE PBL.BUDGET_VERSION_ID = PRA.BUDGET_VERSION_ID)
AND PRA.RESOURCE_ASSIGNMENT_TYPE = 'USER_ENTERED'
AND PRA.TASK_ID = PTK.TASK_ID(+)
AND PRLM2.PARENT_MEMBER_ID = PRLM1.RESOURCE_LIST_MEMBER_ID(+)
AND PRLM2.RESOURCE_LIST_MEMBER_ID = PRA.RESOURCE_LIST_MEMBER_ID
AND PMVL.AMOUNT_TYPE_CODE IN ('RAW_COST'
, 'BURDENED_COST'
, 'REVENUE'
, 'QUANTITY')
AND PPFO.PROJ_FP_OPTIONS_ID=PFTC.PROJ_FP_OPTIONS_ID
AND DECODE(PPFO.FIN_PLAN_PREFERENCE_CODE
, 'COST_ONLY'
, PFTC.DEFAULT_COST_CURR_FLAG
, 'REVENUE_ONLY'
, PFTC.DEFAULT_REV_CURR_FLAG
, PFTC.DEFAULT_ALL_CURR_FLAG)= 'Y'
AND PBV.BUDGET_VERSION_ID = PRA.BUDGET_VERSION_ID
AND PRL.RESOURCE_LIST_ID = PBV.RESOURCE_LIST_ID UNION SELECT PRA.BUDGET_VERSION_ID
, PPFO.FIN_PLAN_PREFERENCE_CODE
, P.NAME
, PRA.RESOURCE_ASSIGNMENT_ID
, PTK.TASK_NUMBER
, PTK.TASK_NAME
, DECODE(PRLM2.PARENT_MEMBER_ID
, NULL
, PRLM2.ALIAS
, PRLM1.ALIAS)
, DECODE(PRLM2.PARENT_MEMBER_ID
, NULL
, DECODE(PRL.GROUP_RESOURCE_TYPE_ID
, 0
, PRLM2.ALIAS
, NULL)
, PRLM2.ALIAS)
, NVL(B.START_DATE
, NVL(PTK.START_DATE
, P.START_DATE))
, NVL(B.END_DATE
, NVL(PTK.COMPLETION_DATE
, P.COMPLETION_DATE))
, B.QUANTITY
, B.TXN_RAW_COST
, B.TXN_BURDENED_COST
, B.TXN_REVENUE
, 'QUANTITY'
, 'QUANTITY'
, 'QUANTITY'
, 215
, NVL(B.TXN_CURRENCY_CODE
, PFTC.TXN_CURRENCY_CODE)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, DECODE(NVL(PRA.UNIT_OF_MEASURE
, 'NONE')
, 'NONE'
, NULL
, (SELECT MEANING
FROM PA_LOOKUPS
WHERE LOOKUP_TYPE = 'UNIT'
AND LOOKUP_CODE = NVL(PRA.UNIT_OF_MEASURE
, 'HOURS')))
, B.DESCRIPTION
, DECODE(B.CHANGE_REASON_CODE
, NULL
, NULL
, (SELECT MEANING
FROM PA_LOOKUPS
WHERE LOOKUP_TYPE='BUDGET CHANGE REASON'
AND LOOKUP_CODE= NVL(B.CHANGE_REASON_CODE
, ( SELECT LOOKUP_CODE
FROM PA_LOOKUPS
WHERE LOOKUP_TYPE='BUDGET CHANGE REASON'
AND ROWNUM=1))) )
, DECODE(NVL(B.PROJFUNC_COST_RATE_TYPE
, PPFO.PROJFUNC_COST_RATE_TYPE)
, NULL
, NULL
, (SELECT PCTV1.USER_CONVERSION_TYPE
FROM PA_CONVERSION_TYPES_V PCTV1
WHERE PCTV1.CONVERSION_TYPE=NVL(NVL(B.PROJFUNC_COST_RATE_TYPE
, PPFO.PROJFUNC_COST_RATE_TYPE)
, 'CORPORATE')) )
, DECODE( NVL(B.PROJFUNC_COST_RATE_DATE_TYPE
, DECODE(NVL(B.PROJFUNC_COST_RATE_TYPE
, PPFO.PROJFUNC_COST_RATE_TYPE)
, 'USER'
, NULL
, PPFO.PROJFUNC_COST_RATE_DATE_TYPE))
, NULL
, NULL
, (SELECT PLK_D1.MEANING
FROM PA_LOOKUPS PLK_D1
WHERE PLK_D1.LOOKUP_TYPE='PA_FP_RATE_DATE_TYPE'
AND PLK_D1.LOOKUP_CODE=NVL(NVL(B.PROJFUNC_COST_RATE_DATE_TYPE
, DECODE(NVL(B.PROJFUNC_COST_RATE_TYPE
, PPFO.PROJFUNC_COST_RATE_TYPE)
, 'USER'
, NULL
, PPFO.PROJFUNC_COST_RATE_DATE_TYPE))
, 'FIXED_DATE')) )
, DECODE(NVL(B.PROJECT_COST_RATE_TYPE
, PPFO.PROJECT_COST_RATE_TYPE)
, NULL
, NULL
, (SELECT PCTV2.USER_CONVERSION_TYPE
FROM PA_CONVERSION_TYPES_V PCTV2
WHERE PCTV2.CONVERSION_TYPE=NVL(NVL(B.PROJECT_COST_RATE_TYPE
, PPFO.PROJECT_COST_RATE_TYPE)
, 'CORPORATE')) )
, DECODE( NVL(B.PROJECT_COST_RATE_DATE_TYPE
, DECODE(NVL(B.PROJECT_COST_RATE_TYPE
, PPFO.PROJECT_COST_RATE_TYPE)
, 'USER'
, NULL
, PPFO.PROJECT_COST_RATE_DATE_TYPE))
, NULL
, NULL
, (SELECT PLK_D2.MEANING
FROM PA_LOOKUPS PLK_D2
WHERE PLK_D2.LOOKUP_TYPE='PA_FP_RATE_DATE_TYPE'
AND PLK_D2.LOOKUP_CODE=NVL(NVL(B.PROJECT_COST_RATE_DATE_TYPE
, DECODE(NVL(B.PROJECT_COST_RATE_TYPE
, PPFO.PROJECT_COST_RATE_TYPE)
, 'USER'
, NULL
, PPFO.PROJECT_COST_RATE_DATE_TYPE))
, 'FIXED_DATE')) )
, DECODE(NVL(B.PROJFUNC_REV_RATE_TYPE
, PPFO.PROJFUNC_REV_RATE_TYPE)
, NULL
, NULL
, (SELECT PCTV3.USER_CONVERSION_TYPE
FROM PA_CONVERSION_TYPES_V PCTV3
WHERE PCTV3.CONVERSION_TYPE=NVL(NVL(B.PROJFUNC_REV_RATE_TYPE
, PPFO.PROJFUNC_REV_RATE_TYPE)
, 'CORPORATE')) )
, DECODE( NVL(B.PROJFUNC_REV_RATE_DATE_TYPE
, DECODE(NVL(B.PROJFUNC_REV_RATE_TYPE
, PPFO.PROJFUNC_REV_RATE_TYPE)
, 'USER'
, NULL
, PPFO.PROJFUNC_REV_RATE_DATE_TYPE))
, NULL
, NULL
, (SELECT PLK_D3.MEANING
FROM PA_LOOKUPS PLK_D3
WHERE PLK_D3.LOOKUP_TYPE='PA_FP_RATE_DATE_TYPE'
AND PLK_D3.LOOKUP_CODE=NVL(NVL(B.PROJFUNC_REV_RATE_DATE_TYPE
, DECODE(NVL(B.PROJFUNC_REV_RATE_TYPE
, PPFO.PROJFUNC_REV_RATE_TYPE)
, 'USER'
, NULL
, PPFO.PROJFUNC_REV_RATE_DATE_TYPE))
, 'FIXED_DATE')) )
, DECODE(NVL(B.PROJECT_REV_RATE_TYPE
, PPFO.PROJECT_REV_RATE_TYPE)
, NULL
, NULL
, (SELECT PCTV4.USER_CONVERSION_TYPE
FROM PA_CONVERSION_TYPES_V PCTV4
WHERE PCTV4.CONVERSION_TYPE=NVL(NVL(B.PROJECT_REV_RATE_TYPE
, PPFO.PROJECT_REV_RATE_TYPE)
, 'CORPORATE')) )
, DECODE( NVL(B.PROJECT_REV_RATE_DATE_TYPE
, DECODE(NVL(B.PROJECT_REV_RATE_TYPE
, PPFO.PROJECT_REV_RATE_TYPE)
, 'USER'
, NULL
, PPFO.PROJECT_REV_RATE_DATE_TYPE))
, NULL
, NULL
, (SELECT PLK_D4.MEANING
FROM PA_LOOKUPS PLK_D4
WHERE PLK_D4.LOOKUP_TYPE='PA_FP_RATE_DATE_TYPE'
AND PLK_D4.LOOKUP_CODE=NVL(NVL(B.PROJECT_REV_RATE_DATE_TYPE
, DECODE(NVL(B.PROJECT_REV_RATE_TYPE
, PPFO.PROJECT_REV_RATE_TYPE)
, 'USER'
, NULL
, PPFO.PROJECT_REV_RATE_DATE_TYPE))
, 'FIXED_DATE')) )
, NVL(B.PROJFUNC_COST_EXCHANGE_RATE
, DECODE(NVL(B.PROJFUNC_COST_RATE_TYPE
, PPFO.PROJFUNC_COST_RATE_TYPE)
, 'USER'
, PFTC.PROJFUNC_COST_EXCHANGE_RATE
, TO_NUMBER(NULL)))
, NVL(B.PROJFUNC_COST_RATE_DATE
, DECODE(NVL(B.PROJFUNC_COST_RATE_DATE_TYPE
, PPFO.PROJFUNC_COST_RATE_DATE_TYPE)
, 'FIXED_DATE'
, PPFO.PROJFUNC_COST_RATE_DATE
, TO_DATE(NULL)))
, NVL(B.PROJECT_COST_EXCHANGE_RATE
, DECODE(NVL(B.PROJECT_COST_RATE_TYPE
, PPFO.PROJECT_COST_RATE_TYPE)
, 'USER'
, PFTC.PROJECT_COST_EXCHANGE_RATE
, TO_NUMBER(NULL)))
, NVL(B.PROJECT_COST_RATE_DATE
, DECODE(NVL(B.PROJECT_COST_RATE_DATE_TYPE
, PPFO.PROJECT_COST_RATE_DATE_TYPE)
, 'FIXED_DATE'
, PPFO.PROJECT_COST_RATE_DATE
, TO_DATE(NULL)))
, NVL(B.PROJFUNC_REV_EXCHANGE_RATE
, DECODE(NVL(B.PROJFUNC_REV_RATE_TYPE
, PPFO.PROJFUNC_REV_RATE_TYPE)
, 'USER'
, PFTC.PROJFUNC_REV_EXCHANGE_RATE
, TO_NUMBER(NULL)))
, NVL(B.PROJFUNC_REV_RATE_DATE
, DECODE(NVL(B.PROJFUNC_REV_RATE_DATE_TYPE
, PPFO.PROJFUNC_REV_RATE_DATE_TYPE)
, 'FIXED_DATE'
, PPFO.PROJFUNC_REV_RATE_DATE
, TO_DATE(NULL)))
, NVL(B.PROJECT_REV_EXCHANGE_RATE
, DECODE(NVL(B.PROJECT_REV_RATE_TYPE
, PPFO.PROJECT_REV_RATE_TYPE)
, 'USER'
, PFTC.PROJECT_REV_EXCHANGE_RATE
, TO_NUMBER(NULL)))
, NVL(B.PROJECT_REV_RATE_DATE
, DECODE(NVL(B.PROJECT_REV_RATE_DATE_TYPE
, PPFO.PROJECT_REV_RATE_DATE_TYPE)
, 'FIXED_DATE'
, PPFO.PROJECT_REV_RATE_DATE
, TO_DATE(NULL)))
, B.ATTRIBUTE_CATEGORY
, B.ATTRIBUTE1
, B.ATTRIBUTE2
, B.ATTRIBUTE3
, B.ATTRIBUTE4
, B.ATTRIBUTE5
, B.ATTRIBUTE6
, B.ATTRIBUTE7
, B.ATTRIBUTE8
, B.ATTRIBUTE9
, B.ATTRIBUTE10
, B.ATTRIBUTE11
, B.ATTRIBUTE12
, B.ATTRIBUTE13
, B.ATTRIBUTE14
, B.ATTRIBUTE15
, NULL
, NULL
, NULL
, NULL
, TO_NUMBER(NULL)
, TO_DATE(NULL)
, TO_NUMBER(NULL)
, TO_DATE(NULL)
FROM PA_PROJECTS_ALL P
, PA_BUDGET_LINES B
, PA_RESOURCE_ASSIGNMENTS PRA
, PA_TASKS PTK
, PA_RESOURCE_LISTS PRL
, PA_BUDGET_VERSIONS PBV
, PA_FP_TXN_CURRENCIES PFTC
, PA_PROJ_FP_OPTIONS PPFO
, PA_RESOURCE_LIST_MEMBERS PRLM1
, PA_RESOURCE_LIST_MEMBERS PRLM2
WHERE P.PROJECT_ID = PRA.PROJECT_ID
AND B.RESOURCE_ASSIGNMENT_ID(+)=PRA.RESOURCE_ASSIGNMENT_ID
AND PRA.TASK_ID=PTK.TASK_ID(+)
AND PRLM2.PARENT_MEMBER_ID=PRLM1.RESOURCE_LIST_MEMBER_ID(+)
AND PRLM2.RESOURCE_LIST_MEMBER_ID=PRA.RESOURCE_LIST_MEMBER_ID
AND PRA.RESOURCE_ASSIGNMENT_TYPE = 'USER_ENTERED'
AND PPFO.PROJECT_ID = PRA.PROJECT_ID
AND PPFO.FIN_PLAN_TYPE_ID = PBV.FIN_PLAN_TYPE_ID
AND PPFO.FIN_PLAN_VERSION_ID = PRA.BUDGET_VERSION_ID
AND PPFO.FIN_PLAN_OPTION_LEVEL_CODE = 'PLAN_VERSION'
AND PA_FIN_PLAN_UTILS.GET_TIME_PHASED_CODE(PRA.BUDGET_VERSION_ID) NOT IN ('P'
, 'G')
AND PPFO.PROJ_FP_OPTIONS_ID=PFTC.PROJ_FP_OPTIONS_ID
AND DECODE(PPFO.FIN_PLAN_PREFERENCE_CODE
, 'COST_ONLY'
, PFTC.DEFAULT_COST_CURR_FLAG
, 'REVENUE_ONLY'
, PFTC.DEFAULT_REV_CURR_FLAG
, PFTC.DEFAULT_ALL_CURR_FLAG)= 'Y'
AND PBV.BUDGET_VERSION_ID = PRA.BUDGET_VERSION_ID
AND PRL.RESOURCE_LIST_ID = PBV.RESOURCE_LIST_ID