SELECT
Max(DAY) day,
Max(FISCAL_WEEK_NAME) FISCAL_WEEK_NAME,
Max(FISCAL_QUARTER_NAME) FISCAL_QUARTER_NAME,
Max(FISCAL_YEAR_NAME) FISCAL_year_NAME,
DAY_id,
FISCAL_WEEK_id,
FISCAL_QUARTER_id,
FISCAL_YEAR_id,
plant_fk_key,
entity_fk_key,
period_profile,
effective_date,
expiration_date,
sustain_aspect_fk_key,
site_sustain_fk_key,
average_planned_cost,
usage_uom,
SUM(planned_usage) planned_usage,
COUNT(planned_usage) planned_usage_count,
SUM(ALLOCATED_PLANNED_USAGE) ALLOCATED_PLANNED_USAGE,
COUNT(ALLOCATED_PLANNED_USAGE) ALLOCATED_PLANNED_USAGE_COUNT,
COUNT(*) TOTAL_COUNT,
GROUPING_ID (DAY_id,
FISCAL_WEEK_id,
FISCAL_QUARTER_id,
FISCAL_YEAR_id,
plant_fk_key,
entity_fk_key,
period_profile,
effective_date,
expiration_date,
sustain_aspect_fk_key,
site_sustain_fk_key,
average_planned_cost,
usage_uom)GROUPING_ID,
DECODE (GROUPING_ID(DAY_id,
FISCAL_WEEK_id,
FISCAL_QUARTER_id,
FISCAL_YEAR_id),
7,DAY_id,
11,FISCAL_WEEK_id,
13,FISCAL_QUARTER_id,
14, FISCAL_YEAR_id) TIME_DIM_KEY,
DECODE (GROUPING_ID(DAY_id,
FISCAL_WEEK_id,
FISCAL_QUARTER_id,
FISCAL_YEAR_id),
7,10,
11,9,
13,7,
14,6)TIME_DIM_LEVEL
FROM MTH_ENTITY_PLANNED_USAGE_HR_MV
GROUP BY GROUPING SETS ((DAY_id,
plant_fk_key,
entity_fk_key,
period_profile,
effective_date,
expiration_date,
sustain_aspect_fk_key,
site_sustain_fk_key,
average_planned_cost,
usage_uom),
(
FISCAL_WEEK_id,
plant_fk_key,
entity_fk_key,
period_profile,
effective_date,
expiration_date,
sustain_aspect_fk_key,
site_sustain_fk_key,
average_planned_cost,
usage_uom),
(
FISCAL_QUARTER_id,
plant_fk_key,
entity_fk_key,
period_profile,
effective_date,
expiration_date,
sustain_aspect_fk_key,
site_sustain_fk_key,
average_planned_cost,
usage_uom),
(
FISCAL_YEAR_id,
plant_fk_key,
entity_fk_key,
period_profile,
effective_date,
expiration_date,
sustain_aspect_fk_key,
site_sustain_fk_key,
average_planned_cost,
usage_uom))
SELECT
MAX(DAY) DAY
,
MAX(FISCAL_WEEK_NAME) FISCAL_WEEK_NAME
,
MAX(FISCAL_QUARTER_NAME) FISCAL_QUARTER_NAME
,
MAX(FISCAL_YEAR_NAME) FISCAL_YEAR_NAME
,
DAY_ID
,
FISCAL_WEEK_ID
,
FISCAL_QUARTER_ID
,
FISCAL_YEAR_ID
,
PLANT_FK_KEY
,
ENTITY_FK_KEY
,
PERIOD_PROFILE
,
EFFECTIVE_DATE
,
EXPIRATION_DATE
,
SUSTAIN_ASPECT_FK_KEY
,
SITE_SUSTAIN_FK_KEY
,
AVERAGE_PLANNED_COST
,
USAGE_UOM
,
SUM(PLANNED_USAGE) PLANNED_USAGE
,
COUNT(PLANNED_USAGE) PLANNED_USAGE_COUNT
,
SUM(ALLOCATED_PLANNED_USAGE) ALLOCATED_PLANNED_USAGE
,
COUNT(ALLOCATED_PLANNED_USAGE) ALLOCATED_PLANNED_USAGE_COUNT
,
COUNT(*) TOTAL_COUNT
,
GROUPING_ID (DAY_ID
,
FISCAL_WEEK_ID
,
FISCAL_QUARTER_ID
,
FISCAL_YEAR_ID
,
PLANT_FK_KEY
,
ENTITY_FK_KEY
,
PERIOD_PROFILE
,
EFFECTIVE_DATE
,
EXPIRATION_DATE
,
SUSTAIN_ASPECT_FK_KEY
,
SITE_SUSTAIN_FK_KEY
,
AVERAGE_PLANNED_COST
,
USAGE_UOM)GROUPING_ID
,
DECODE (GROUPING_ID(DAY_ID
,
FISCAL_WEEK_ID
,
FISCAL_QUARTER_ID
,
FISCAL_YEAR_ID)
,
7
, DAY_ID
,
11
, FISCAL_WEEK_ID
,
13
, FISCAL_QUARTER_ID
,
14
, FISCAL_YEAR_ID) TIME_DIM_KEY
,
DECODE (GROUPING_ID(DAY_ID
,
FISCAL_WEEK_ID
,
FISCAL_QUARTER_ID
,
FISCAL_YEAR_ID)
,
7
, 10
,
11
, 9
,
13
, 7
,
14
, 6)TIME_DIM_LEVEL
FROM MTH_ENTITY_PLANNED_USAGE_HR_MV
GROUP BY GROUPING SETS ((DAY_ID
,
PLANT_FK_KEY
,
ENTITY_FK_KEY
,
PERIOD_PROFILE
,
EFFECTIVE_DATE
,
EXPIRATION_DATE
,
SUSTAIN_ASPECT_FK_KEY
,
SITE_SUSTAIN_FK_KEY
,
AVERAGE_PLANNED_COST
,
USAGE_UOM)
,
(
FISCAL_WEEK_ID
,
PLANT_FK_KEY
,
ENTITY_FK_KEY
,
PERIOD_PROFILE
,
EFFECTIVE_DATE
,
EXPIRATION_DATE
,
SUSTAIN_ASPECT_FK_KEY
,
SITE_SUSTAIN_FK_KEY
,
AVERAGE_PLANNED_COST
,
USAGE_UOM)
,
(
FISCAL_QUARTER_ID
,
PLANT_FK_KEY
,
ENTITY_FK_KEY
,
PERIOD_PROFILE
,
EFFECTIVE_DATE
,
EXPIRATION_DATE
,
SUSTAIN_ASPECT_FK_KEY
,
SITE_SUSTAIN_FK_KEY
,
AVERAGE_PLANNED_COST
,
USAGE_UOM)
,
(
FISCAL_YEAR_ID
,
PLANT_FK_KEY
,
ENTITY_FK_KEY
,
PERIOD_PROFILE
,
EFFECTIVE_DATE
,
EXPIRATION_DATE
,
SUSTAIN_ASPECT_FK_KEY
,
SITE_SUSTAIN_FK_KEY
,
AVERAGE_PLANNED_COST
,
USAGE_UOM))
|
|
|