SELECT
ship.BSNS_MNTH_ID,
ship.ORG_CHAIN_ID,
ship.SHIP_TO_CHAIN_ID,
ship.MFG_ITEM_CLASS_ID,
ship.BSNS_MNTH_ID as BSNS_CLNDR_DIM_ID,
'MNTH' as BSNS_CLNDR_DIM_LVL,
ship.ORG_CHAIN_ID as ORG_DIM_ID,
'CHN' as ORG_DIM_LVL,
ship.MFG_ITEM_CLASS_ID as MFG_ITEM_DIM_ID,
'CLS' as MFG_ITEM_DIM_LVL,
count(*) as CNT_TOTAL,
sum(ship.SHIP_QTY_PRMRY) as SHIP_QTY_PRMRY,
sum(ship.SHIP_QTY_ALT) as SHIP_QTY_ALT,
sum(ship.SHIP_AMT) as SHIP_AMT,
sum(ship.SHIP_COST) as SHIP_COST,
count(ship.SHIP_QTY_PRMRY) as CNT_SHIP_QTY_PRMRY,
count(ship.SHIP_QTY_ALT) as CNT_SHIP_QTY_ALT,
count(ship.SHIP_AMT) as CNT_SHIP_AMT,
count(ship.SHIP_COST) as CNT_SHIP_COST,
grouping_id (ship.MFG_ITEM_CLASS_ID, ship.BSNS_MNTH_ID, ship.ORG_CHAIN_ID, ship.SHIP_TO_CHAIN_ID) GROUPING_ID
from
ddr_bs_mfg_ship_itm_bu_wk_mv ship
group by
Grouping sets((ship.ORG_CHAIN_ID, ship.BSNS_MNTH_ID, ship.MFG_ITEM_CLASS_ID),
(ship.SHIP_TO_CHAIN_ID, ship.BSNS_MNTH_ID, ship.MFG_ITEM_CLASS_ID),
(ship.ORG_CHAIN_ID, ship.SHIP_TO_CHAIN_ID, ship.BSNS_MNTH_ID, ship.MFG_ITEM_CLASS_ID))
SELECT
SHIP.BSNS_MNTH_ID
,
SHIP.ORG_CHAIN_ID
,
SHIP.SHIP_TO_CHAIN_ID
,
SHIP.MFG_ITEM_CLASS_ID
,
SHIP.BSNS_MNTH_ID AS BSNS_CLNDR_DIM_ID
,
'MNTH' AS BSNS_CLNDR_DIM_LVL
,
SHIP.ORG_CHAIN_ID AS ORG_DIM_ID
,
'CHN' AS ORG_DIM_LVL
,
SHIP.MFG_ITEM_CLASS_ID AS MFG_ITEM_DIM_ID
,
'CLS' AS MFG_ITEM_DIM_LVL
,
COUNT(*) AS CNT_TOTAL
,
SUM(SHIP.SHIP_QTY_PRMRY) AS SHIP_QTY_PRMRY
,
SUM(SHIP.SHIP_QTY_ALT) AS SHIP_QTY_ALT
,
SUM(SHIP.SHIP_AMT) AS SHIP_AMT
,
SUM(SHIP.SHIP_COST) AS SHIP_COST
,
COUNT(SHIP.SHIP_QTY_PRMRY) AS CNT_SHIP_QTY_PRMRY
,
COUNT(SHIP.SHIP_QTY_ALT) AS CNT_SHIP_QTY_ALT
,
COUNT(SHIP.SHIP_AMT) AS CNT_SHIP_AMT
,
COUNT(SHIP.SHIP_COST) AS CNT_SHIP_COST
,
GROUPING_ID (SHIP.MFG_ITEM_CLASS_ID
, SHIP.BSNS_MNTH_ID
, SHIP.ORG_CHAIN_ID
, SHIP.SHIP_TO_CHAIN_ID) GROUPING_ID
FROM
DDR_BS_MFG_SHIP_ITM_BU_WK_MV SHIP
GROUP BY
GROUPING SETS((SHIP.ORG_CHAIN_ID
, SHIP.BSNS_MNTH_ID
, SHIP.MFG_ITEM_CLASS_ID)
,
(SHIP.SHIP_TO_CHAIN_ID
, SHIP.BSNS_MNTH_ID
, SHIP.MFG_ITEM_CLASS_ID)
,
(SHIP.ORG_CHAIN_ID
, SHIP.SHIP_TO_CHAIN_ID
, SHIP.BSNS_MNTH_ID
, SHIP.MFG_ITEM_CLASS_ID))
|
|
|