VIEW: APPS.PA_FIN_PLAN_CREATE_PLAN_NOBV_V

Source

View Text - Preformatted

SELECT po.project_id, pt.fin_plan_type_code, pt.name as plan_type_name, po.fin_plan_preference_code, DECODE(po.fin_plan_preference_code, 'COST_AND_REV_SEP', po.cost_fin_plan_level_code, null) as cost_plan_level_code, DECODE(po.fin_plan_preference_code, 'COST_AND_REV_SEP', po.revenue_fin_plan_level_code, null) as revenue_plan_level_code, DECODE(po.fin_plan_preference_code, 'COST_ONLY', po.cost_fin_plan_level_code, 'REVENUE_ONLY', po.revenue_fin_plan_level_code, 'COST_AND_REV_SAME', po.all_fin_plan_level_code, null) as plan_level_code, DECODE(po.fin_plan_preference_code, 'COST_AND_REV_SEP', po.cost_time_phased_code, null) as cost_time_phased_code, DECODE(po.fin_plan_preference_code, 'COST_AND_REV_SEP', po.revenue_time_phased_code, null) as revenue_time_phased_code, DECODE(po.fin_plan_preference_code, 'COST_ONLY', po.cost_time_phased_code, 'REVENUE_ONLY', po.revenue_time_phased_code, 'COST_AND_REV_SAME', po.all_time_phased_code, null) as time_phased_code, DECODE(po.fin_plan_preference_code, 'COST_AND_REV_SEP', po.cost_resource_list_id, null) as cost_resource_list_id, DECODE(po.fin_plan_preference_code, 'COST_AND_REV_SEP', po.revenue_resource_list_id, null) as revenue_resource_list_id, DECODE(po.fin_plan_preference_code, 'COST_ONLY', po.cost_resource_list_id, 'REVENUE_ONLY', po.revenue_resource_list_id, 'COST_AND_REV_SAME', po.all_resource_list_id, null) as resource_list_id, DECODE(po.fin_plan_preference_code, 'COST_AND_REV_SEP', DECODE(po.cost_time_phased_code, 'P', PA_FIN_PLAN_CREATE_VER_GLOBAL.get_pa_current_start_period (pa_fin_plan_create_ver_global.get_project_id), 'G', PA_FIN_PLAN_CREATE_VER_GLOBAL.get_gl_current_start_period (pa_fin_plan_create_ver_global.get_project_id), null), null) as cost_start_period_name, DECODE(po.fin_plan_preference_code, 'COST_AND_REV_SEP', DECODE(po.revenue_time_phased_code, 'P', PA_FIN_PLAN_CREATE_VER_GLOBAL.get_pa_current_start_period (pa_fin_plan_create_ver_global.get_project_id), 'G', PA_FIN_PLAN_CREATE_VER_GLOBAL.get_gl_current_start_period (pa_fin_plan_create_ver_global.get_project_id), null), null) as revenue_start_period_name, DECODE(po.fin_plan_preference_code, 'COST_ONLY', DECODE(po.cost_time_phased_code, 'P', PA_FIN_PLAN_CREATE_VER_GLOBAL.get_pa_current_start_period (pa_fin_plan_create_ver_global.get_project_id), 'G', PA_FIN_PLAN_CREATE_VER_GLOBAL.get_gl_current_start_period (pa_fin_plan_create_ver_global.get_project_id), null), 'REVENUE_ONLY', DECODE(po.revenue_time_phased_code, 'P', PA_FIN_PLAN_CREATE_VER_GLOBAL.get_pa_current_start_period (pa_fin_plan_create_ver_global.get_project_id), 'G', PA_FIN_PLAN_CREATE_VER_GLOBAL.get_gl_current_start_period (pa_fin_plan_create_ver_global.get_project_id), null), 'COST_AND_REV_SAME', DECODE(po.all_time_phased_code, 'P', PA_FIN_PLAN_CREATE_VER_GLOBAL.get_pa_current_start_period (pa_fin_plan_create_ver_global.get_project_id), 'G', PA_FIN_PLAN_CREATE_VER_GLOBAL.get_gl_current_start_period (pa_fin_plan_create_ver_global.get_project_id), null), null) as start_period_name, DECODE(po.fin_plan_preference_code, 'COST_AND_REV_SEP', DECODE(po.cost_time_phased_code, 'P', PA_FIN_PLAN_CREATE_VER_GLOBAL.get_pa_current_end_period (pa_fin_plan_create_ver_global.get_project_id), 'G', PA_FIN_PLAN_CREATE_VER_GLOBAL.get_gl_current_end_period (pa_fin_plan_create_ver_global.get_project_id), null), null) as cost_end_period_name, DECODE(po.fin_plan_preference_code, 'COST_AND_REV_SEP', DECODE(po.revenue_time_phased_code, 'P', PA_FIN_PLAN_CREATE_VER_GLOBAL.get_pa_current_end_period (pa_fin_plan_create_ver_global.get_project_id), 'G', PA_FIN_PLAN_CREATE_VER_GLOBAL.get_gl_current_end_period (pa_fin_plan_create_ver_global.get_project_id), null), null) as revenue_end_period_name, DECODE(po.fin_plan_preference_code, 'COST_ONLY', DECODE(po.cost_time_phased_code, 'P', PA_FIN_PLAN_CREATE_VER_GLOBAL.get_pa_current_end_period (pa_fin_plan_create_ver_global.get_project_id), 'G', PA_FIN_PLAN_CREATE_VER_GLOBAL.get_gl_current_end_period (pa_fin_plan_create_ver_global.get_project_id), null), 'REVENUE_ONLY', DECODE(po.revenue_time_phased_code, 'P', PA_FIN_PLAN_CREATE_VER_GLOBAL.get_pa_current_end_period (pa_fin_plan_create_ver_global.get_project_id), 'G', PA_FIN_PLAN_CREATE_VER_GLOBAL.get_gl_current_end_period (pa_fin_plan_create_ver_global.get_project_id), null), 'COST_AND_REV_SAME', DECODE(po.all_time_phased_code, 'P', PA_FIN_PLAN_CREATE_VER_GLOBAL.get_pa_current_end_period (pa_fin_plan_create_ver_global.get_project_id), 'G', PA_FIN_PLAN_CREATE_VER_GLOBAL.get_gl_current_end_period (pa_fin_plan_create_ver_global.get_project_id), null), null) as end_period_name, NULL as attribute_category, NULL as attribute1, NULL as attribute2, NULL as attribute3, NULL as attribute4, NULL as attribute5, NULL as attribute6, NULL as attribute7, NULL as attribute8, NULL as attribute9, NULL as attribute10, NULL as attribute11, NULL as attribute12, NULL as attribute13, NULL as attribute14, NULL as attribute15 from pa_proj_fp_options po, pa_fin_plan_types_vl pt WHERE po.project_id = pa_fin_plan_create_ver_global.get_project_id and po.fin_plan_option_level_code = 'PLAN_TYPE' and po.fin_plan_type_id = pt.fin_plan_type_id and pt.fin_plan_type_id = pa_fin_plan_create_ver_global.get_fin_plan_type_id

View Text - HTML Formatted

SELECT PO.PROJECT_ID
, PT.FIN_PLAN_TYPE_CODE
, PT.NAME AS PLAN_TYPE_NAME
, PO.FIN_PLAN_PREFERENCE_CODE
, DECODE(PO.FIN_PLAN_PREFERENCE_CODE
, 'COST_AND_REV_SEP'
, PO.COST_FIN_PLAN_LEVEL_CODE
, NULL) AS COST_PLAN_LEVEL_CODE
, DECODE(PO.FIN_PLAN_PREFERENCE_CODE
, 'COST_AND_REV_SEP'
, PO.REVENUE_FIN_PLAN_LEVEL_CODE
, NULL) AS REVENUE_PLAN_LEVEL_CODE
, DECODE(PO.FIN_PLAN_PREFERENCE_CODE
, 'COST_ONLY'
, PO.COST_FIN_PLAN_LEVEL_CODE
, 'REVENUE_ONLY'
, PO.REVENUE_FIN_PLAN_LEVEL_CODE
, 'COST_AND_REV_SAME'
, PO.ALL_FIN_PLAN_LEVEL_CODE
, NULL) AS PLAN_LEVEL_CODE
, DECODE(PO.FIN_PLAN_PREFERENCE_CODE
, 'COST_AND_REV_SEP'
, PO.COST_TIME_PHASED_CODE
, NULL) AS COST_TIME_PHASED_CODE
, DECODE(PO.FIN_PLAN_PREFERENCE_CODE
, 'COST_AND_REV_SEP'
, PO.REVENUE_TIME_PHASED_CODE
, NULL) AS REVENUE_TIME_PHASED_CODE
, DECODE(PO.FIN_PLAN_PREFERENCE_CODE
, 'COST_ONLY'
, PO.COST_TIME_PHASED_CODE
, 'REVENUE_ONLY'
, PO.REVENUE_TIME_PHASED_CODE
, 'COST_AND_REV_SAME'
, PO.ALL_TIME_PHASED_CODE
, NULL) AS TIME_PHASED_CODE
, DECODE(PO.FIN_PLAN_PREFERENCE_CODE
, 'COST_AND_REV_SEP'
, PO.COST_RESOURCE_LIST_ID
, NULL) AS COST_RESOURCE_LIST_ID
, DECODE(PO.FIN_PLAN_PREFERENCE_CODE
, 'COST_AND_REV_SEP'
, PO.REVENUE_RESOURCE_LIST_ID
, NULL) AS REVENUE_RESOURCE_LIST_ID
, DECODE(PO.FIN_PLAN_PREFERENCE_CODE
, 'COST_ONLY'
, PO.COST_RESOURCE_LIST_ID
, 'REVENUE_ONLY'
, PO.REVENUE_RESOURCE_LIST_ID
, 'COST_AND_REV_SAME'
, PO.ALL_RESOURCE_LIST_ID
, NULL) AS RESOURCE_LIST_ID
, DECODE(PO.FIN_PLAN_PREFERENCE_CODE
, 'COST_AND_REV_SEP'
, DECODE(PO.COST_TIME_PHASED_CODE
, 'P'
, PA_FIN_PLAN_CREATE_VER_GLOBAL.GET_PA_CURRENT_START_PERIOD (PA_FIN_PLAN_CREATE_VER_GLOBAL.GET_PROJECT_ID)
, 'G'
, PA_FIN_PLAN_CREATE_VER_GLOBAL.GET_GL_CURRENT_START_PERIOD (PA_FIN_PLAN_CREATE_VER_GLOBAL.GET_PROJECT_ID)
, NULL)
, NULL) AS COST_START_PERIOD_NAME
, DECODE(PO.FIN_PLAN_PREFERENCE_CODE
, 'COST_AND_REV_SEP'
, DECODE(PO.REVENUE_TIME_PHASED_CODE
, 'P'
, PA_FIN_PLAN_CREATE_VER_GLOBAL.GET_PA_CURRENT_START_PERIOD (PA_FIN_PLAN_CREATE_VER_GLOBAL.GET_PROJECT_ID)
, 'G'
, PA_FIN_PLAN_CREATE_VER_GLOBAL.GET_GL_CURRENT_START_PERIOD (PA_FIN_PLAN_CREATE_VER_GLOBAL.GET_PROJECT_ID)
, NULL)
, NULL) AS REVENUE_START_PERIOD_NAME
, DECODE(PO.FIN_PLAN_PREFERENCE_CODE
, 'COST_ONLY'
, DECODE(PO.COST_TIME_PHASED_CODE
, 'P'
, PA_FIN_PLAN_CREATE_VER_GLOBAL.GET_PA_CURRENT_START_PERIOD (PA_FIN_PLAN_CREATE_VER_GLOBAL.GET_PROJECT_ID)
, 'G'
, PA_FIN_PLAN_CREATE_VER_GLOBAL.GET_GL_CURRENT_START_PERIOD (PA_FIN_PLAN_CREATE_VER_GLOBAL.GET_PROJECT_ID)
, NULL)
, 'REVENUE_ONLY'
, DECODE(PO.REVENUE_TIME_PHASED_CODE
, 'P'
, PA_FIN_PLAN_CREATE_VER_GLOBAL.GET_PA_CURRENT_START_PERIOD (PA_FIN_PLAN_CREATE_VER_GLOBAL.GET_PROJECT_ID)
, 'G'
, PA_FIN_PLAN_CREATE_VER_GLOBAL.GET_GL_CURRENT_START_PERIOD (PA_FIN_PLAN_CREATE_VER_GLOBAL.GET_PROJECT_ID)
, NULL)
, 'COST_AND_REV_SAME'
, DECODE(PO.ALL_TIME_PHASED_CODE
, 'P'
, PA_FIN_PLAN_CREATE_VER_GLOBAL.GET_PA_CURRENT_START_PERIOD (PA_FIN_PLAN_CREATE_VER_GLOBAL.GET_PROJECT_ID)
, 'G'
, PA_FIN_PLAN_CREATE_VER_GLOBAL.GET_GL_CURRENT_START_PERIOD (PA_FIN_PLAN_CREATE_VER_GLOBAL.GET_PROJECT_ID)
, NULL)
, NULL) AS START_PERIOD_NAME
, DECODE(PO.FIN_PLAN_PREFERENCE_CODE
, 'COST_AND_REV_SEP'
, DECODE(PO.COST_TIME_PHASED_CODE
, 'P'
, PA_FIN_PLAN_CREATE_VER_GLOBAL.GET_PA_CURRENT_END_PERIOD (PA_FIN_PLAN_CREATE_VER_GLOBAL.GET_PROJECT_ID)
, 'G'
, PA_FIN_PLAN_CREATE_VER_GLOBAL.GET_GL_CURRENT_END_PERIOD (PA_FIN_PLAN_CREATE_VER_GLOBAL.GET_PROJECT_ID)
, NULL)
, NULL) AS COST_END_PERIOD_NAME
, DECODE(PO.FIN_PLAN_PREFERENCE_CODE
, 'COST_AND_REV_SEP'
, DECODE(PO.REVENUE_TIME_PHASED_CODE
, 'P'
, PA_FIN_PLAN_CREATE_VER_GLOBAL.GET_PA_CURRENT_END_PERIOD (PA_FIN_PLAN_CREATE_VER_GLOBAL.GET_PROJECT_ID)
, 'G'
, PA_FIN_PLAN_CREATE_VER_GLOBAL.GET_GL_CURRENT_END_PERIOD (PA_FIN_PLAN_CREATE_VER_GLOBAL.GET_PROJECT_ID)
, NULL)
, NULL) AS REVENUE_END_PERIOD_NAME
, DECODE(PO.FIN_PLAN_PREFERENCE_CODE
, 'COST_ONLY'
, DECODE(PO.COST_TIME_PHASED_CODE
, 'P'
, PA_FIN_PLAN_CREATE_VER_GLOBAL.GET_PA_CURRENT_END_PERIOD (PA_FIN_PLAN_CREATE_VER_GLOBAL.GET_PROJECT_ID)
, 'G'
, PA_FIN_PLAN_CREATE_VER_GLOBAL.GET_GL_CURRENT_END_PERIOD (PA_FIN_PLAN_CREATE_VER_GLOBAL.GET_PROJECT_ID)
, NULL)
, 'REVENUE_ONLY'
, DECODE(PO.REVENUE_TIME_PHASED_CODE
, 'P'
, PA_FIN_PLAN_CREATE_VER_GLOBAL.GET_PA_CURRENT_END_PERIOD (PA_FIN_PLAN_CREATE_VER_GLOBAL.GET_PROJECT_ID)
, 'G'
, PA_FIN_PLAN_CREATE_VER_GLOBAL.GET_GL_CURRENT_END_PERIOD (PA_FIN_PLAN_CREATE_VER_GLOBAL.GET_PROJECT_ID)
, NULL)
, 'COST_AND_REV_SAME'
, DECODE(PO.ALL_TIME_PHASED_CODE
, 'P'
, PA_FIN_PLAN_CREATE_VER_GLOBAL.GET_PA_CURRENT_END_PERIOD (PA_FIN_PLAN_CREATE_VER_GLOBAL.GET_PROJECT_ID)
, 'G'
, PA_FIN_PLAN_CREATE_VER_GLOBAL.GET_GL_CURRENT_END_PERIOD (PA_FIN_PLAN_CREATE_VER_GLOBAL.GET_PROJECT_ID)
, NULL)
, NULL) AS END_PERIOD_NAME
, NULL AS ATTRIBUTE_CATEGORY
, NULL AS ATTRIBUTE1
, NULL AS ATTRIBUTE2
, NULL AS ATTRIBUTE3
, NULL AS ATTRIBUTE4
, NULL AS ATTRIBUTE5
, NULL AS ATTRIBUTE6
, NULL AS ATTRIBUTE7
, NULL AS ATTRIBUTE8
, NULL AS ATTRIBUTE9
, NULL AS ATTRIBUTE10
, NULL AS ATTRIBUTE11
, NULL AS ATTRIBUTE12
, NULL AS ATTRIBUTE13
, NULL AS ATTRIBUTE14
, NULL AS ATTRIBUTE15
FROM PA_PROJ_FP_OPTIONS PO
, PA_FIN_PLAN_TYPES_VL PT
WHERE PO.PROJECT_ID = PA_FIN_PLAN_CREATE_VER_GLOBAL.GET_PROJECT_ID
AND PO.FIN_PLAN_OPTION_LEVEL_CODE = 'PLAN_TYPE'
AND PO.FIN_PLAN_TYPE_ID = PT.FIN_PLAN_TYPE_ID
AND PT.FIN_PLAN_TYPE_ID = PA_FIN_PLAN_CREATE_VER_GLOBAL.GET_FIN_PLAN_TYPE_ID