PJO_APPROVED_PLAN_DETAILS_V

Details

• Schema: FUSION

• Object owner: PJO

• Object type: VIEW

Query

SQL_Statement

SELECT MAX(PROJECT_ID) PROJECT_ID, MAX(DECODE(PLANNED_FOR_CODE, 'COST', DECODE(APPROVED_COST_PLAN_TYPE_FLAG || CURRENT_PLAN_STATUS_FLAG, 'YY', PLAN_TYPE_ID,NULL ), 'COST_AND_REV_TOGETHER', DECODE(APPROVED_COST_PLAN_TYPE_FLAG || CURRENT_PLAN_STATUS_FLAG, 'YY', PLAN_TYPE_ID,NULL ), NULL)) APPROVED_COST_PLAN_TYPE_ID, MAX(DECODE(PLANNED_FOR_CODE, 'REVENUE', DECODE(APPROVED_REV_PLAN_TYPE_FLAG || CURRENT_PLAN_STATUS_FLAG, 'YY', PLAN_TYPE_ID,NULL ), 'COST_AND_REV_TOGETHER', DECODE(APPROVED_REV_PLAN_TYPE_FLAG || CURRENT_PLAN_STATUS_FLAG, 'YY', PLAN_TYPE_ID,NULL ), NULL)) APPROVED_REV_PLAN_TYPE_ID, MAX(DECODE(PLANNED_FOR_CODE, 'COST', DECODE(PRIMARY_COST_FORECAST_FLAG || CURRENT_PLAN_STATUS_FLAG, 'YY', PLAN_TYPE_ID,NULL ), 'COST_AND_REV_TOGETHER', DECODE(PRIMARY_COST_FORECAST_FLAG || CURRENT_PLAN_STATUS_FLAG, 'YY', PLAN_TYPE_ID,NULL ), NULL)) PRIMARY_COST_PLAN_TYPE_ID, MAX(DECODE(PLANNED_FOR_CODE, 'REVENUE', DECODE(PRIMARY_REV_FORECAST_FLAG || CURRENT_PLAN_STATUS_FLAG, 'YY', PLAN_TYPE_ID,NULL ), 'COST_AND_REV_TOGETHER', DECODE(PRIMARY_REV_FORECAST_FLAG || CURRENT_PLAN_STATUS_FLAG, 'YY', PLAN_TYPE_ID,NULL ), NULL)) PRIMARY_REV_PLAN_TYPE_ID, MAX(DECODE(PLANNED_FOR_CODE, 'COST', DECODE(APPROVED_COST_PLAN_TYPE_FLAG || CURRENT_PLAN_STATUS_FLAG, 'YY', PLAN_VERSION_ID,NULL ), 'COST_AND_REV_TOGETHER', DECODE(APPROVED_COST_PLAN_TYPE_FLAG || CURRENT_PLAN_STATUS_FLAG, 'YY', PLAN_VERSION_ID,NULL ), NULL)) APPR_COST_PLAN_CURR_VERSION_ID, MAX(DECODE(PLANNED_FOR_CODE, 'REVENUE', DECODE(APPROVED_REV_PLAN_TYPE_FLAG || CURRENT_PLAN_STATUS_FLAG, 'YY', PLAN_VERSION_ID,NULL ), 'COST_AND_REV_TOGETHER', DECODE(APPROVED_REV_PLAN_TYPE_FLAG || CURRENT_PLAN_STATUS_FLAG, 'YY', PLAN_VERSION_ID,NULL ), NULL)) APPR_REV_PLAN_CURR_VERSION_ID, MAX(DECODE(PLANNED_FOR_CODE, 'COST', DECODE(PRIMARY_COST_FORECAST_FLAG || CURRENT_PLAN_STATUS_FLAG, 'YY', PLAN_VERSION_ID,NULL ), 'COST_AND_REV_TOGETHER', DECODE(PRIMARY_COST_FORECAST_FLAG || CURRENT_PLAN_STATUS_FLAG, 'YY', PLAN_VERSION_ID,NULL ), NULL)) PRIM_COST_PLAN_CURR_VERSION_ID, MAX(DECODE(PLANNED_FOR_CODE, 'REVENUE', DECODE(PRIMARY_REV_FORECAST_FLAG || CURRENT_PLAN_STATUS_FLAG, 'YY', PLAN_VERSION_ID,NULL ), 'COST_AND_REV_TOGETHER', DECODE(PRIMARY_REV_FORECAST_FLAG || CURRENT_PLAN_STATUS_FLAG, 'YY', PLAN_VERSION_ID,NULL ), NULL)) PRIM_REV_PLAN_CURR_VERSION_ID, MAX(DECODE(PLANNED_FOR_CODE, 'COST', DECODE(APPROVED_COST_PLAN_TYPE_FLAG || ORIGINAL_FLAG, 'YY', PLAN_VERSION_ID,NULL ), 'COST_AND_REV_TOGETHER', DECODE(APPROVED_COST_PLAN_TYPE_FLAG || ORIGINAL_FLAG, 'YY', PLAN_VERSION_ID,NULL ), NULL)) APPR_COST_PLAN_ORIG_VERSION_ID, MAX(DECODE(PLANNED_FOR_CODE, 'REVENUE', DECODE(APPROVED_REV_PLAN_TYPE_FLAG || ORIGINAL_FLAG, 'YY', PLAN_VERSION_ID,NULL ), 'COST_AND_REV_TOGETHER', DECODE(APPROVED_REV_PLAN_TYPE_FLAG || ORIGINAL_FLAG, 'YY', PLAN_VERSION_ID,NULL ), NULL)) APPR_REV_PLAN_ORIG_VERSION_ID, MAX(DECODE(PLANNED_FOR_CODE, 'COST', DECODE(PRIMARY_COST_FORECAST_FLAG || CURRENT_PLAN_STATUS_FLAG, 'YP', PLAN_VERSION_ID,NULL ), 'COST_AND_REV_TOGETHER', DECODE(PRIMARY_COST_FORECAST_FLAG || CURRENT_PLAN_STATUS_FLAG, 'YP', PLAN_VERSION_ID,NULL ), NULL)) PRIM_COST_PLAN_PRI_VERSION_ID, MAX(DECODE(PLANNED_FOR_CODE, 'REVENUE', DECODE(PRIMARY_REV_FORECAST_FLAG || CURRENT_PLAN_STATUS_FLAG, 'YP', PLAN_VERSION_ID,NULL ), 'COST_AND_REV_TOGETHER', DECODE(PRIMARY_REV_FORECAST_FLAG || CURRENT_PLAN_STATUS_FLAG, 'YP', PLAN_VERSION_ID,NULL ), NULL)) PRIM_REV_PLAN_PRI_VERSION_ID FROM ( SELECT PPV.PROJECT_ID, PPV.PLAN_TYPE_ID, PPV.PLAN_VERSION_ID, PPT.APPROVED_COST_PLAN_TYPE_FLAG, PPT.APPROVED_REV_PLAN_TYPE_FLAG, PPT.PRIMARY_COST_FORECAST_FLAG, PPT.PRIMARY_REV_FORECAST_FLAG, PPV.CURRENT_PLAN_STATUS_FLAG, PPV.ORIGINAL_FLAG, PPV.PLAN_STATUS_CODE, PPO.PLANNED_FOR_CODE FROM PJO_PLAN_VERSIONS_B PPV, PJO_PLANNING_OPTIONS PPO, PJO_PLAN_TYPES_B PPT WHERE PPV.PROJECT_ID = PPO.PROJECT_ID AND PPV.PLAN_VERSION_ID = PPO.PLAN_VERSION_ID AND PPV.PLAN_TYPE_ID = PPT.PLAN_TYPE_ID AND PPO.PLAN_TYPE_ID = PPT.PLAN_TYPE_ID AND ( PPV.CURRENT_PLAN_STATUS_FLAG IN ('Y','P') OR PPV.ORIGINAL_FLAG = 'Y') AND PPV.PLAN_STATUS_CODE = 'B')