SELECT
RES_TXN.PRODUCT_FK_KEY,
IT_DENORM.LEVEL9_FK_KEY,
SUM(RES_TXN.USAGE_QTY)USAGE_QTY,
COUNT(RES_TXN.USAGE_QTY)USAGE_QTY_COUNT,
SUM(RES_TXN.USAGE_QTY*RES_TXN.RESOURCE_COST)USAGE_VAL,
COUNT(RES_TXN.USAGE_QTY*RES_TXN.RESOURCE_COST)USAGE_VAL_COUNT,
TIME_D.DAY_ID,
TIME_D.CALENDAR_MONTH_ID,
COUNT(*) TOTAL_COUNT
FROM
MTH_RESOURCE_TXN_F RES_TXN,
MTH_GREGORIAN_CALENDAR TIME_D,
MTH_ITEM_DENORM_D IT_DENORM
WHERE
TRUNC(RES_TXN.PLANNED_COMPLETION_DATE) = TIME_D.DAY
AND IT_DENORM.HIERARCHY_ID = -5
AND RES_TXN.PRODUCT_FK_KEY = IT_DENORM.ITEM_FK_KEY
GROUP BY LEVEL9_FK_KEY,RES_TXN.PRODUCT_FK_KEY,CALENDAR_MONTH_ID,DAY_ID
SELECT
RES_TXN.PRODUCT_FK_KEY
,
IT_DENORM.LEVEL9_FK_KEY
,
SUM(RES_TXN.USAGE_QTY)USAGE_QTY
,
COUNT(RES_TXN.USAGE_QTY)USAGE_QTY_COUNT
,
SUM(RES_TXN.USAGE_QTY*RES_TXN.RESOURCE_COST)USAGE_VAL
,
COUNT(RES_TXN.USAGE_QTY*RES_TXN.RESOURCE_COST)USAGE_VAL_COUNT
,
TIME_D.DAY_ID
,
TIME_D.CALENDAR_MONTH_ID
,
COUNT(*) TOTAL_COUNT
FROM
MTH_RESOURCE_TXN_F RES_TXN
,
MTH_GREGORIAN_CALENDAR TIME_D
,
MTH_ITEM_DENORM_D IT_DENORM
WHERE
TRUNC(RES_TXN.PLANNED_COMPLETION_DATE) = TIME_D.DAY
AND IT_DENORM.HIERARCHY_ID = -5
AND RES_TXN.PRODUCT_FK_KEY = IT_DENORM.ITEM_FK_KEY
GROUP BY LEVEL9_FK_KEY
, RES_TXN.PRODUCT_FK_KEY
, CALENDAR_MONTH_ID
, DAY_ID
|
|
|