PJS_ALLOC_FP_PROJ_PLAN_F_V

Details

• Schema: FUSION

• Object owner: PJS

• Object type: VIEW

Columns

Name
PLAN_CLASS_CODE PLAN_VERSION_ID PROJECT_ID PROJECT_ELEMENT_ID PLAN_TYPE_ID PERIOD_SET_NAME PERIOD_TYPE PERIOD_NAME PERIOD_YEAR PERIOD_START_DATE PERIOD_END_DATE RBS_ELEMENT_ID RBS_VERSION_ID CURRENCY_TYPE RAW_COST BRDND_COST REVENUE LABOR_QUANTITY CALENDAR_TYPE

Query

SQL_Statement

SELECT plan_rbs.PLAN_CLASS_CODE ,plan_rbs.PLAN_VERSION_ID ,plan_rbs.PROJECT_ID ,plan_rbs.PROJECT_ELEMENT_ID ,plan_rbs.PLAN_TYPE_ID ,plan_rbs.PERIOD_SET_NAME ,plan_rbs.PERIOD_TYPE ,plan_rbs.PERIOD_NAME ,plan_rbs.PERIOD_YEAR ,plan_rbs.PERIOD_START_DATE ,plan_rbs.PERIOD_END_DATE ,decode(plan_rbs.RBS_VERSION_ID, 0, 0, accum.RBS_ELEMENT_ID) RBS_ELEMENT_ID ,plan_rbs.RBS_VERSION_ID ,plan_rbs.CURRENCY_TYPE ,SUM(plan_rbs.RAW_COST) RAW_COST ,SUM(plan_rbs.BRDND_COST) BRDND_COST ,SUM(plan_rbs.REVENUE) REVENUE ,SUM(plan_rbs.LABOR_QUANTITY) LABOR_QUANTITY ,plan_rbs.CALENDAR_TYPE FROM ( SELECT PLAN.TXN_ACCUM_HEADER_ID ,rbs_hdr.rbs_version_id ,PLAN.PLAN_CLASS_CODE ,PLAN.PLAN_VERSION_ID ,PLAN.PROJECT_ID ,PLAN.PROJECT_ELEMENT_ID ,PLAN.CALENDAR_TYPE ,PLAN.CURRENCY_TYPE ,pvd.plan_type_id ,cal.PERIOD_SET_NAME ,cal.PERIOD_TYPE ,TIME.CAL_PERIOD_NAME PERIOD_NAME ,TIME.CAL_YEAR_NAME PERIOD_YEAR ,TIME.CAL_PERIOD_START_DATE PERIOD_START_DATE ,TIME.CAL_PERIOD_END_DATE PERIOD_END_DATE ,PLAN.RAW_COST ,PLAN.BRDND_COST ,PLAN.REVENUE ,PLAN.LABOR_QUANTITY FROM ( SELECT base.PLAN_CLASS_CODE ,base.PLAN_VERSION_ID ,base.TXN_ACCUM_HEADER_ID ,base.business_unit_id ,base.PROJECT_ID ,base.TASK_ID PROJECT_ELEMENT_ID ,base.CALENDAR_TYPE ,base.CURRENCY_TYPE ,base.period_name ,base.RAW_COST ,base.BRDN_COST BRDND_COST ,base.REVENUE ,DECODE(base.RESOURCE_CLASS_ID, 1, base.QUANTITY, to_number(NULL)) LABOR_QUANTITY FROM PJS_FP_BASE_PLAN base UNION ALL SELECT opt.PLAN_CLASS_CODE ,opt.PLAN_VERSION_ID ,opt.TXN_ACCUM_HEADER_ID ,opt.business_unit_id ,opt.PROJECT_ID ,opt.TASK_ID PROJECT_ELEMENT_ID ,opt.CALENDAR_TYPE ,opt.CURRENCY_TYPE ,opt.period_name ,opt.RAW_COST ,opt.BRDN_COST BRDND_COST ,opt.REVENUE ,DECODE(opt.RESOURCE_CLASS_ID, 1, opt.QUANTITY, to_number(NULL)) LABOR_QUANTITY FROM PJS_FP_OPT_PLAN opt ) PLAN ,PJS_RBS_HEADER rbs_hdr ,PJS_BU_INFO bu ,PJS_TIME_D TIME ,PJS_TIME_CAL_NAME cal ,pjs_plan_version_d pvd ,PJS_PLAN_TYPES pt WHERE PLAN.project_id = rbs_hdr.project_id AND PLAN.business_unit_id = bu.business_unit_id AND DECODE(PLAN.CALENDAR_TYPE, 'P', BU.PA_CALENDAR_ID, 'G', bu.gl_calendar_id) = TIME.calendar_id AND PLAN.period_name = TIME.CAL_PERIOD_NAME AND TIME.CALENDAR_ID = cal.CALENDAR_ID AND PLAN.project_id = pvd.project_id AND PLAN.plan_version_id = pvd.plan_version_id AND ( DECODE(pvd.PLAN_CLASS_CODE, 'B', pvd.CURRENT_BUDGET_FLAG, 'F', pvd.CURRENT_FORECAST_FLAG) = 'Y' OR pvd.PRIOR_FORECAST_FLAG = 'Y' ) AND pvd.project_id = pt.project_id AND pt.PLAN_TYPE_NUMBER IN (1,2) AND ( pvd.plan_type_id = pt.COST_PLAN_TYPE_ID OR pvd.plan_type_id = pt.REVENUE_PLAN_TYPE_ID ) ) plan_rbs ,PJF_RBS_TXN_ACCUM_MAP accum ,PJS_RBS_D rbs WHERE plan_rbs.TXN_ACCUM_HEADER_ID = accum.TXN_ACCUM_HEADER_ID(+) AND plan_rbs.rbs_version_id = accum.rbs_version_id(+) AND accum.rbs_version_id = rbs.rbs_version_id(+) AND accum.RBS_ELEMENT_ID = rbs.PARENT_ELEMENT_ID(+) AND 'Y' = rbs.OLAP_SELF_NODE_FLAG(+) GROUP BY plan_rbs.PLAN_CLASS_CODE ,plan_rbs.PLAN_VERSION_ID ,plan_rbs.PROJECT_ID ,plan_rbs.PROJECT_ELEMENT_ID ,plan_rbs.PLAN_TYPE_ID ,plan_rbs.PERIOD_SET_NAME ,plan_rbs.PERIOD_TYPE ,plan_rbs.PERIOD_NAME ,plan_rbs.PERIOD_YEAR ,plan_rbs.PERIOD_START_DATE ,plan_rbs.PERIOD_END_DATE ,decode(plan_rbs.RBS_VERSION_ID, 0, 0, accum.RBS_ELEMENT_ID) ,plan_rbs.RBS_VERSION_ID ,plan_rbs.CURRENCY_TYPE ,plan_rbs.CALENDAR_TYPE