SELECT "A4"."CLNDR_MNTH_ID" "CLNDR_MNTH_ID",
"A3"."ORG_RGN_ID" "ORG_RGN_ID",
"A3"."ORG_CHAIN_ID" "ORG_CHAIN_ID",
"A3"."MKT_AREA_ID" "MKT_AREA_ID",
"A3"."CHNL_TYP_CD" "CHNL_TYP_CD",
"A5"."ITM_TYP" "ITM_TYP",
"A2"."MFG_ITEM_CLASS_ID" "MFG_ITEM_CLASS_ID",
"A1"."RTL_ITEM_CLASS_ID" "RTL_ITEM_CLASS_ID",
"A5"."SLS_TYP_CD" "SLS_TYP_CD",
"A4"."CLNDR_MNTH_ID" "CLNDR_DIM_ID",
'MNTH' "CLNDR_DIM_LVL",
DECODE(GROUPING_ID("A3"."ORG_CHAIN_ID","A3"."ORG_RGN_ID"),2,"A3"."ORG_RGN_ID",1,"A3"."ORG_CHAIN_ID") "ORG_DIM_ID",
DECODE(GROUPING_ID("A3"."ORG_CHAIN_ID","A3"."ORG_RGN_ID"),2,'RGN',1,'CHN') "ORG_DIM_LVL",
"A2"."MFG_ITEM_CLASS_ID" "MFG_ITEM_DIM_ID",
DECODE("A2"."MFG_ITEM_CLASS_ID",NULL,NULL,'CLS') "MFG_ITEM_DIM_LVL",
"A1"."RTL_ITEM_CLASS_ID" "RTL_ITEM_DIM_ID",
DECODE("A1"."RTL_ITEM_CLASS_ID",NULL,NULL,'CLS') "RTL_ITEM_DIM_LVL",
COUNT(*) "CNT_TOTAL",
SUM(DECODE(SIGN("A5"."SLS_QTY_PRMRY"),1,1,NULL)) "ACT_BU_ITM_DAYS_SCANNED",
COUNT(DECODE(SIGN("A5"."SLS_QTY_PRMRY"),1,1,NULL)) "CNT_ACT_BU_ITM_DAYS_SCANNED",
SUM("A5"."SLS_QTY_PRMRY") "SLS_QTY_PRMRY",
SUM("A5"."SLS_QTY_ALT") "SLS_QTY_ALT",
SUM("A5"."SLS_AMT") "SLS_AMT",
SUM("A5"."SLS_AMT_RPT") "SLS_AMT_RPT",
SUM("A5"."SLS_COST_AMT") "SLS_COST_AMT",
SUM("A5"."SLS_COST_AMT_RPT") "SLS_COST_AMT_RPT",
SUM("A5"."RTRN_QTY_PRMRY") "RTRN_QTY_PRMRY",
SUM("A5"."RTRN_QTY_ALT") "RTRN_QTY_ALT",
SUM("A5"."RTRN_AMT") "RTRN_AMT",
SUM("A5"."RTRN_AMT_RPT") "RTRN_AMT_RPT",
SUM("A5"."RTRN_COST_AMT") "RTRN_COST_AMT",
SUM("A5"."RTRN_COST_AMT_RPT") "RTRN_COST_AMT_RPT",
COUNT("A5"."SLS_QTY_PRMRY") "CNT_SLS_QTY_PRMRY",
COUNT("A5"."SLS_QTY_ALT") "CNT_SLS_QTY_ALT",
COUNT("A5"."SLS_AMT") "CNT_SLS_AMT",
COUNT("A5"."SLS_AMT_RPT") "CNT_SLS_AMT_RPT",
COUNT("A5"."SLS_COST_AMT") "CNT_SLS_COST_AMT",
COUNT("A5"."SLS_COST_AMT_RPT") "CNT_SLS_COST_AMT_RPT",
COUNT("A5"."RTRN_QTY_PRMRY") "CNT_RTRN_QTY_PRMRY",
COUNT("A5"."RTRN_QTY_ALT") "CNT_RTRN_QTY_ALT",
COUNT("A5"."RTRN_AMT") "CNT_RTRN_AMT",
COUNT("A5"."RTRN_AMT_RPT") "CNT_RTRN_AMT_RPT",
COUNT("A5"."RTRN_COST_AMT") "CNT_RTRN_COST_AMT",
COUNT("A5"."RTRN_COST_AMT_RPT") "CNT_RTRN_COST_AMT_RPT",
GROUPING_ID("A2"."MFG_ITEM_CLASS_ID","A1"."RTL_ITEM_CLASS_ID","A3"."ORG_RGN_ID","A3"."ORG_CHAIN_ID","A4"."CLNDR_MNTH_ID","A3"."MKT_AREA_ID","A3"."CHNL_TYP_CD","A5"."ITM_TYP","A5"."SLS_TYP_CD") "GROUPING_ID"
FROM "DDR_B_RTL_SL_RTN_ITM_DAY" "A5",
"DDR_R_CLNDR_DAY_DN_MV" "A4",
"DDR_R_ORG_BU_DN_MV" "A3",
"DDR_R_MFG_ITEM_SKU_DN_MV" "A2",
"DDR_R_RTL_ITEM_SKU_DN_MV" "A1"
WHERE "A4"."DAY_CD" ="A5"."DAY_CD"
AND "A4"."CLNDR_DESC"="A5"."MFG_ORG_CD"
||'-CLNDR'
AND "A2"."MFG_SKU_ITEM_ID" ="A5"."MFG_SKU_ITEM_ID"
AND "A1"."RTL_SKU_ITEM_ID" ="A5"."RTL_SKU_ITEM_ID"
AND "A3"."ORG_BSNS_UNIT_ID"="A5"."ORG_BSNS_UNIT_ID"
GROUP BY GROUPING SETS (("A4"."CLNDR_MNTH_ID", "A3"."ORG_RGN_ID", "A2"."MFG_ITEM_CLASS_ID", "A1"."RTL_ITEM_CLASS_ID", "A3"."MKT_AREA_ID", "A3"."CHNL_TYP_CD", "A5"."ITM_TYP", "A5"."SLS_TYP_CD"), ("A4"."CLNDR_MNTH_ID", "A3"."ORG_CHAIN_ID", "A2"."MFG_ITEM_CLASS_ID", "A1"."RTL_ITEM_CLASS_ID", "A3"."MKT_AREA_ID", "A3"."CHNL_TYP_CD", "A5"."ITM_TYP", "A5"."SLS_TYP_CD"))
SELECT "A4"."CLNDR_MNTH_ID" "CLNDR_MNTH_ID"
,
"A3"."ORG_RGN_ID" "ORG_RGN_ID"
,
"A3"."ORG_CHAIN_ID" "ORG_CHAIN_ID"
,
"A3"."MKT_AREA_ID" "MKT_AREA_ID"
,
"A3"."CHNL_TYP_CD" "CHNL_TYP_CD"
,
"A5"."ITM_TYP" "ITM_TYP"
,
"A2"."MFG_ITEM_CLASS_ID" "MFG_ITEM_CLASS_ID"
,
"A1"."RTL_ITEM_CLASS_ID" "RTL_ITEM_CLASS_ID"
,
"A5"."SLS_TYP_CD" "SLS_TYP_CD"
,
"A4"."CLNDR_MNTH_ID" "CLNDR_DIM_ID"
,
'MNTH' "CLNDR_DIM_LVL"
,
DECODE(GROUPING_ID("A3"."ORG_CHAIN_ID"
, "A3"."ORG_RGN_ID")
, 2
, "A3"."ORG_RGN_ID"
, 1
, "A3"."ORG_CHAIN_ID") "ORG_DIM_ID"
,
DECODE(GROUPING_ID("A3"."ORG_CHAIN_ID"
, "A3"."ORG_RGN_ID")
, 2
, 'RGN'
, 1
, 'CHN') "ORG_DIM_LVL"
,
"A2"."MFG_ITEM_CLASS_ID" "MFG_ITEM_DIM_ID"
,
DECODE("A2"."MFG_ITEM_CLASS_ID"
, NULL
, NULL
, 'CLS') "MFG_ITEM_DIM_LVL"
,
"A1"."RTL_ITEM_CLASS_ID" "RTL_ITEM_DIM_ID"
,
DECODE("A1"."RTL_ITEM_CLASS_ID"
, NULL
, NULL
, 'CLS') "RTL_ITEM_DIM_LVL"
,
COUNT(*) "CNT_TOTAL"
,
SUM(DECODE(SIGN("A5"."SLS_QTY_PRMRY")
, 1
, 1
, NULL)) "ACT_BU_ITM_DAYS_SCANNED"
,
COUNT(DECODE(SIGN("A5"."SLS_QTY_PRMRY")
, 1
, 1
, NULL)) "CNT_ACT_BU_ITM_DAYS_SCANNED"
,
SUM("A5"."SLS_QTY_PRMRY") "SLS_QTY_PRMRY"
,
SUM("A5"."SLS_QTY_ALT") "SLS_QTY_ALT"
,
SUM("A5"."SLS_AMT") "SLS_AMT"
,
SUM("A5"."SLS_AMT_RPT") "SLS_AMT_RPT"
,
SUM("A5"."SLS_COST_AMT") "SLS_COST_AMT"
,
SUM("A5"."SLS_COST_AMT_RPT") "SLS_COST_AMT_RPT"
,
SUM("A5"."RTRN_QTY_PRMRY") "RTRN_QTY_PRMRY"
,
SUM("A5"."RTRN_QTY_ALT") "RTRN_QTY_ALT"
,
SUM("A5"."RTRN_AMT") "RTRN_AMT"
,
SUM("A5"."RTRN_AMT_RPT") "RTRN_AMT_RPT"
,
SUM("A5"."RTRN_COST_AMT") "RTRN_COST_AMT"
,
SUM("A5"."RTRN_COST_AMT_RPT") "RTRN_COST_AMT_RPT"
,
COUNT("A5"."SLS_QTY_PRMRY") "CNT_SLS_QTY_PRMRY"
,
COUNT("A5"."SLS_QTY_ALT") "CNT_SLS_QTY_ALT"
,
COUNT("A5"."SLS_AMT") "CNT_SLS_AMT"
,
COUNT("A5"."SLS_AMT_RPT") "CNT_SLS_AMT_RPT"
,
COUNT("A5"."SLS_COST_AMT") "CNT_SLS_COST_AMT"
,
COUNT("A5"."SLS_COST_AMT_RPT") "CNT_SLS_COST_AMT_RPT"
,
COUNT("A5"."RTRN_QTY_PRMRY") "CNT_RTRN_QTY_PRMRY"
,
COUNT("A5"."RTRN_QTY_ALT") "CNT_RTRN_QTY_ALT"
,
COUNT("A5"."RTRN_AMT") "CNT_RTRN_AMT"
,
COUNT("A5"."RTRN_AMT_RPT") "CNT_RTRN_AMT_RPT"
,
COUNT("A5"."RTRN_COST_AMT") "CNT_RTRN_COST_AMT"
,
COUNT("A5"."RTRN_COST_AMT_RPT") "CNT_RTRN_COST_AMT_RPT"
,
GROUPING_ID("A2"."MFG_ITEM_CLASS_ID"
, "A1"."RTL_ITEM_CLASS_ID"
, "A3"."ORG_RGN_ID"
, "A3"."ORG_CHAIN_ID"
, "A4"."CLNDR_MNTH_ID"
, "A3"."MKT_AREA_ID"
, "A3"."CHNL_TYP_CD"
, "A5"."ITM_TYP"
, "A5"."SLS_TYP_CD") "GROUPING_ID"
FROM "DDR_B_RTL_SL_RTN_ITM_DAY" "A5"
,
"DDR_R_CLNDR_DAY_DN_MV" "A4"
,
"DDR_R_ORG_BU_DN_MV" "A3"
,
"DDR_R_MFG_ITEM_SKU_DN_MV" "A2"
,
"DDR_R_RTL_ITEM_SKU_DN_MV" "A1"
WHERE "A4"."DAY_CD" ="A5"."DAY_CD"
AND "A4"."CLNDR_DESC"="A5"."MFG_ORG_CD"
||'-CLNDR'
AND "A2"."MFG_SKU_ITEM_ID" ="A5"."MFG_SKU_ITEM_ID"
AND "A1"."RTL_SKU_ITEM_ID" ="A5"."RTL_SKU_ITEM_ID"
AND "A3"."ORG_BSNS_UNIT_ID"="A5"."ORG_BSNS_UNIT_ID"
GROUP BY GROUPING SETS (("A4"."CLNDR_MNTH_ID"
, "A3"."ORG_RGN_ID"
, "A2"."MFG_ITEM_CLASS_ID"
, "A1"."RTL_ITEM_CLASS_ID"
, "A3"."MKT_AREA_ID"
, "A3"."CHNL_TYP_CD"
, "A5"."ITM_TYP"
, "A5"."SLS_TYP_CD")
, ("A4"."CLNDR_MNTH_ID"
, "A3"."ORG_CHAIN_ID"
, "A2"."MFG_ITEM_CLASS_ID"
, "A1"."RTL_ITEM_CLASS_ID"
, "A3"."MKT_AREA_ID"
, "A3"."CHNL_TYP_CD"
, "A5"."ITM_TYP"
, "A5"."SLS_TYP_CD"))
|
|
|