SELECT
/*+ NO_STATS_GSETS */
"A5"."CLNDR_MNTH_ID" "CLNDR_MNTH_ID",
"A2"."ORG_CHAIN_ID" "ORG_CHAIN_ID",
"A1"."ORG_CHAIN_ID" "SHIP_TO_CHAIN_ID",
"A4"."MFG_ITEM_CLASS_ID" "MFG_ITEM_CLASS_ID",
"A3"."RTL_ITEM_CLASS_ID" "RTL_ITEM_CLASS_ID",
"A5"."CLNDR_MNTH_ID" "CLNDR_DIM_ID",
'MNTH' "CLNDR_DIM_LVL",
"A2"."ORG_CHAIN_ID" "ORG_DIM_ID",
'CHN' "ORG_DIM_LVL",
"A4"."MFG_ITEM_CLASS_ID" "MFG_ITEM_DIM_ID",
'CLS' "MFG_ITEM_DIM_LVL",
"A3"."RTL_ITEM_CLASS_ID" "RTL_ITEM_DIM_ID",
'CLS' "RTL_ITEM_DIM_LVL",
"A6"."ITM_TYP" "ITEM_TYPE",
COUNT(*) "CNT_TOTAL",
SUM("A6"."SHIP_QTY_PRMRY") "SHIP_QTY_PRMRY",
SUM("A6"."SHIP_QTY_ALT") "SHIP_QTY_ALT",
SUM("A6"."SHIP_AMT") "SHIP_AMT",
SUM("A6"."SHIP_AMT_RPT") "SHIP_AMT_RPT",
SUM("A6"."SHIP_COST") "SHIP_COST",
SUM("A6"."SHIP_COST_RPT") "SHIP_COST_RPT",
COUNT("A6"."SHIP_QTY_PRMRY") "CNT_SHIP_QTY_PRMRY",
COUNT("A6"."SHIP_QTY_ALT") "CNT_SHIP_QTY_ALT",
COUNT("A6"."SHIP_AMT") "CNT_SHIP_AMT",
COUNT("A6"."SHIP_AMT_RPT") "CNT_SHIP_AMT_RPT",
COUNT("A6"."SHIP_COST") "CNT_SHIP_COST",
COUNT("A6"."SHIP_COST_RPT") "CNT_SHIP_COST_RPT",
GROUPING_ID("A6"."ITM_TYP","A4"."MFG_ITEM_CLASS_ID","A3"."RTL_ITEM_CLASS_ID","A5"."CLNDR_MNTH_ID","A2"."ORG_CHAIN_ID","A1"."ORG_CHAIN_ID") "GROUPING_ID"
FROM "DDR_B_RTL_SHIP_ITEM_DAY" "A6",
"DDR_R_CLNDR_DAY_DN_MV" "A5",
"DDR_R_MFG_ITEM_SKU_DN_MV" "A4",
"DDR_R_RTL_ITEM_SKU_DN_MV" "A3",
"DDR_R_ORG_BU_DN_MV" "A2",
"DDR_R_ORG_BU_DN_MV" "A1"
WHERE "A5"."DAY_CD" ="A6"."DAY_CD"
AND "A5"."CLNDR_DESC"="A6"."MFG_ORG_CD"
||'-CLNDR'
AND "A4"."MFG_SKU_ITEM_ID" ="A6"."MFG_SKU_ITEM_ID"
AND "A3"."RTL_SKU_ITEM_ID" ="A6"."RTL_SKU_ITEM_ID"
AND "A2"."ORG_BSNS_UNIT_ID"="A6"."ORG_BSNS_UNIT_ID"
and "A1"."ORG_BSNS_UNIT_ID"="A6"."SHIP_TO_BSNS_UNIT_ID"
GROUP BY GROUPING SETS (("A6"."ITM_TYP", "A2"."ORG_CHAIN_ID", "A1"."ORG_CHAIN_ID", "A5"."CLNDR_MNTH_ID", "A4"."MFG_ITEM_CLASS_ID", "A3"."RTL_ITEM_CLASS_ID"), ("A6"."ITM_TYP", "A1"."ORG_CHAIN_ID", "A5"."CLNDR_MNTH_ID", "A4"."MFG_ITEM_CLASS_ID", "A3"."RTL_ITEM_CLASS_ID"), ("A6"."ITM_TYP", "A2"."ORG_CHAIN_ID", "A5"."CLNDR_MNTH_ID", "A4"."MFG_ITEM_CLASS_ID", "A3"."RTL_ITEM_CLASS_ID"))
SELECT
/*+ NO_STATS_GSETS */
"A5"."CLNDR_MNTH_ID" "CLNDR_MNTH_ID"
,
"A2"."ORG_CHAIN_ID" "ORG_CHAIN_ID"
,
"A1"."ORG_CHAIN_ID" "SHIP_TO_CHAIN_ID"
,
"A4"."MFG_ITEM_CLASS_ID" "MFG_ITEM_CLASS_ID"
,
"A3"."RTL_ITEM_CLASS_ID" "RTL_ITEM_CLASS_ID"
,
"A5"."CLNDR_MNTH_ID" "CLNDR_DIM_ID"
,
'MNTH' "CLNDR_DIM_LVL"
,
"A2"."ORG_CHAIN_ID" "ORG_DIM_ID"
,
'CHN' "ORG_DIM_LVL"
,
"A4"."MFG_ITEM_CLASS_ID" "MFG_ITEM_DIM_ID"
,
'CLS' "MFG_ITEM_DIM_LVL"
,
"A3"."RTL_ITEM_CLASS_ID" "RTL_ITEM_DIM_ID"
,
'CLS' "RTL_ITEM_DIM_LVL"
,
"A6"."ITM_TYP" "ITEM_TYPE"
,
COUNT(*) "CNT_TOTAL"
,
SUM("A6"."SHIP_QTY_PRMRY") "SHIP_QTY_PRMRY"
,
SUM("A6"."SHIP_QTY_ALT") "SHIP_QTY_ALT"
,
SUM("A6"."SHIP_AMT") "SHIP_AMT"
,
SUM("A6"."SHIP_AMT_RPT") "SHIP_AMT_RPT"
,
SUM("A6"."SHIP_COST") "SHIP_COST"
,
SUM("A6"."SHIP_COST_RPT") "SHIP_COST_RPT"
,
COUNT("A6"."SHIP_QTY_PRMRY") "CNT_SHIP_QTY_PRMRY"
,
COUNT("A6"."SHIP_QTY_ALT") "CNT_SHIP_QTY_ALT"
,
COUNT("A6"."SHIP_AMT") "CNT_SHIP_AMT"
,
COUNT("A6"."SHIP_AMT_RPT") "CNT_SHIP_AMT_RPT"
,
COUNT("A6"."SHIP_COST") "CNT_SHIP_COST"
,
COUNT("A6"."SHIP_COST_RPT") "CNT_SHIP_COST_RPT"
,
GROUPING_ID("A6"."ITM_TYP"
, "A4"."MFG_ITEM_CLASS_ID"
, "A3"."RTL_ITEM_CLASS_ID"
, "A5"."CLNDR_MNTH_ID"
, "A2"."ORG_CHAIN_ID"
, "A1"."ORG_CHAIN_ID") "GROUPING_ID"
FROM "DDR_B_RTL_SHIP_ITEM_DAY" "A6"
,
"DDR_R_CLNDR_DAY_DN_MV" "A5"
,
"DDR_R_MFG_ITEM_SKU_DN_MV" "A4"
,
"DDR_R_RTL_ITEM_SKU_DN_MV" "A3"
,
"DDR_R_ORG_BU_DN_MV" "A2"
,
"DDR_R_ORG_BU_DN_MV" "A1"
WHERE "A5"."DAY_CD" ="A6"."DAY_CD"
AND "A5"."CLNDR_DESC"="A6"."MFG_ORG_CD"
||'-CLNDR'
AND "A4"."MFG_SKU_ITEM_ID" ="A6"."MFG_SKU_ITEM_ID"
AND "A3"."RTL_SKU_ITEM_ID" ="A6"."RTL_SKU_ITEM_ID"
AND "A2"."ORG_BSNS_UNIT_ID"="A6"."ORG_BSNS_UNIT_ID"
AND "A1"."ORG_BSNS_UNIT_ID"="A6"."SHIP_TO_BSNS_UNIT_ID"
GROUP BY GROUPING SETS (("A6"."ITM_TYP"
, "A2"."ORG_CHAIN_ID"
, "A1"."ORG_CHAIN_ID"
, "A5"."CLNDR_MNTH_ID"
, "A4"."MFG_ITEM_CLASS_ID"
, "A3"."RTL_ITEM_CLASS_ID")
, ("A6"."ITM_TYP"
, "A1"."ORG_CHAIN_ID"
, "A5"."CLNDR_MNTH_ID"
, "A4"."MFG_ITEM_CLASS_ID"
, "A3"."RTL_ITEM_CLASS_ID")
, ("A6"."ITM_TYP"
, "A2"."ORG_CHAIN_ID"
, "A5"."CLNDR_MNTH_ID"
, "A4"."MFG_ITEM_CLASS_ID"
, "A3"."RTL_ITEM_CLASS_ID"))
|
|
|