VIEW: APPS.PA_PROJ_FINPLAN_TYPES_V

Source

View Text - Preformatted

SELECT lk.meaning as fin_plan_preference, -1 as fin_plan_type_id, c.budget_type as name, c.description as description, c.PREDEFINED_FLAG as pre_defined_flag, c.ENABLE_WF_FLAG as enable_wf_flag, null as plan_class_code, null as migrated_frm_bdgt_typ_code, null as approved_cost_plan_type_flag, null as approved_rev_plan_type_flag, 'BUDGET_TYPE' as FP_type_code, c.budget_type_code as budget_type_code, c.budget_amount_code as budget_amount_code, null as primary_cost_forecast_flag, null as primary_rev_forecast_flag, null as use_for_workplan_flag, -1 as proj_fp_option_id FROM pa_budget_types c ,pa_lookups lk WHERE c.budget_type_code <> 'FORECASTING_BUDGET_TYPE' AND trunc(sysdate) BETWEEN c.start_date_active AND nvl(c.end_date_active,trunc(sysdate)) AND lk.lookup_type = 'BUDGET AMOUNT' AND lk.lookup_code = c.budget_amount_code AND not exists (SELECT 1 FROM pa_fin_plan_types_b a, pa_proj_fp_options b WHERE b.fin_plan_option_level_code='PLAN_TYPE' AND a.fin_plan_type_id = b.fin_plan_type_id AND a.migrated_frm_bdgt_typ_code = c.budget_type_code ) UNION SELECT lk.meaning as fin_plan_preference, pt.fin_plan_type_id as fin_plan_type_id, pt.name as name, pt.description as description, pt.pre_defined_flag as pre_defined_flag, pt.enable_wf_flag as enable_wf_flag, pt.plan_class_code as plan_class_code, pt.migrated_frm_bdgt_typ_code as migrated_frm_bdgt_typ_code, pt.approved_cost_plan_type_flag as approved_cost_plan_type_flag, pt.approved_rev_plan_type_flag as approved_rev_plan_type_flag, 'PLAN_TYPE' as fp_type_code, null as budget_type_code, po.fin_plan_preference_code as budget_amount_code, pt.primary_cost_forecast_flag as primary_cost_forecast_flag, pt.primary_rev_forecast_flag as primary_rev_forecast_flag, pt.use_for_workplan_flag as use_for_workplan_flag, po.proj_fp_options_id as proj_fp_option_id FROM pa_fin_plan_types_vl pt, pa_proj_fp_options po, pa_lookups lk WHERE po.fin_plan_option_level_code='PLAN_TYPE' AND pt.fin_plan_type_id = po.fin_plan_type_id AND trunc(sysdate) BETWEEN pt.start_date_active AND nvl(pt.end_date_active,trunc(sysdate)) AND nvl(pt.fin_plan_type_code,'-99 ') <> 'ORG_FORECAST' AND lk.lookup_type = 'FIN_PLAN_PREFERENCE' AND lk.lookup_code = po.fin_plan_preference_code

View Text - HTML Formatted

SELECT LK.MEANING AS FIN_PLAN_PREFERENCE
, -1 AS FIN_PLAN_TYPE_ID
, C.BUDGET_TYPE AS NAME
, C.DESCRIPTION AS DESCRIPTION
, C.PREDEFINED_FLAG AS PRE_DEFINED_FLAG
, C.ENABLE_WF_FLAG AS ENABLE_WF_FLAG
, NULL AS PLAN_CLASS_CODE
, NULL AS MIGRATED_FRM_BDGT_TYP_CODE
, NULL AS APPROVED_COST_PLAN_TYPE_FLAG
, NULL AS APPROVED_REV_PLAN_TYPE_FLAG
, 'BUDGET_TYPE' AS FP_TYPE_CODE
, C.BUDGET_TYPE_CODE AS BUDGET_TYPE_CODE
, C.BUDGET_AMOUNT_CODE AS BUDGET_AMOUNT_CODE
, NULL AS PRIMARY_COST_FORECAST_FLAG
, NULL AS PRIMARY_REV_FORECAST_FLAG
, NULL AS USE_FOR_WORKPLAN_FLAG
, -1 AS PROJ_FP_OPTION_ID
FROM PA_BUDGET_TYPES C
, PA_LOOKUPS LK
WHERE C.BUDGET_TYPE_CODE <> 'FORECASTING_BUDGET_TYPE'
AND TRUNC(SYSDATE) BETWEEN C.START_DATE_ACTIVE
AND NVL(C.END_DATE_ACTIVE
, TRUNC(SYSDATE))
AND LK.LOOKUP_TYPE = 'BUDGET AMOUNT'
AND LK.LOOKUP_CODE = C.BUDGET_AMOUNT_CODE
AND NOT EXISTS (SELECT 1
FROM PA_FIN_PLAN_TYPES_B A
, PA_PROJ_FP_OPTIONS B
WHERE B.FIN_PLAN_OPTION_LEVEL_CODE='PLAN_TYPE'
AND A.FIN_PLAN_TYPE_ID = B.FIN_PLAN_TYPE_ID
AND A.MIGRATED_FRM_BDGT_TYP_CODE = C.BUDGET_TYPE_CODE ) UNION SELECT LK.MEANING AS FIN_PLAN_PREFERENCE
, PT.FIN_PLAN_TYPE_ID AS FIN_PLAN_TYPE_ID
, PT.NAME AS NAME
, PT.DESCRIPTION AS DESCRIPTION
, PT.PRE_DEFINED_FLAG AS PRE_DEFINED_FLAG
, PT.ENABLE_WF_FLAG AS ENABLE_WF_FLAG
, PT.PLAN_CLASS_CODE AS PLAN_CLASS_CODE
, PT.MIGRATED_FRM_BDGT_TYP_CODE AS MIGRATED_FRM_BDGT_TYP_CODE
, PT.APPROVED_COST_PLAN_TYPE_FLAG AS APPROVED_COST_PLAN_TYPE_FLAG
, PT.APPROVED_REV_PLAN_TYPE_FLAG AS APPROVED_REV_PLAN_TYPE_FLAG
, 'PLAN_TYPE' AS FP_TYPE_CODE
, NULL AS BUDGET_TYPE_CODE
, PO.FIN_PLAN_PREFERENCE_CODE AS BUDGET_AMOUNT_CODE
, PT.PRIMARY_COST_FORECAST_FLAG AS PRIMARY_COST_FORECAST_FLAG
, PT.PRIMARY_REV_FORECAST_FLAG AS PRIMARY_REV_FORECAST_FLAG
, PT.USE_FOR_WORKPLAN_FLAG AS USE_FOR_WORKPLAN_FLAG
, PO.PROJ_FP_OPTIONS_ID AS PROJ_FP_OPTION_ID
FROM PA_FIN_PLAN_TYPES_VL PT
, PA_PROJ_FP_OPTIONS PO
, PA_LOOKUPS LK
WHERE PO.FIN_PLAN_OPTION_LEVEL_CODE='PLAN_TYPE'
AND PT.FIN_PLAN_TYPE_ID = PO.FIN_PLAN_TYPE_ID
AND TRUNC(SYSDATE) BETWEEN PT.START_DATE_ACTIVE
AND NVL(PT.END_DATE_ACTIVE
, TRUNC(SYSDATE))
AND NVL(PT.FIN_PLAN_TYPE_CODE
, '-99 ') <> 'ORG_FORECAST'
AND LK.LOOKUP_TYPE = 'FIN_PLAN_PREFERENCE'
AND LK.LOOKUP_CODE = PO.FIN_PLAN_PREFERENCE_CODE