select
inv.BSNS_MNTH_ID,
inv.ORG_RGN_ID,
inv.ORG_CHAIN_ID,
inv.MFG_ITEM_CLASS_ID,
inv.RTL_ITEM_CLASS_ID,
inv.ITM_TYP,
inv.BSNS_MNTH_ID as BSNS_CLNDR_DIM_ID,
'MNTH' as BSNS_CLNDR_DIM_LVL,
decode (grouping_id (inv.ORG_CHAIN_ID, inv.ORG_RGN_ID),
2, inv.ORG_RGN_ID,
1, inv.ORG_CHAIN_ID) as ORG_DIM_ID,
decode (grouping_id (inv.ORG_CHAIN_ID, inv.ORG_RGN_ID),
2, 'RGN',
1, 'CHN') as ORG_DIM_LVL,
inv.MFG_ITEM_CLASS_ID as MFG_ITEM_DIM_ID,
'CLS' as MFG_ITEM_DIM_LVL,
inv.RTL_ITEM_CLASS_ID as RTL_ITEM_DIM_ID,
'CLS' as RTL_ITEM_DIM_LVL,
count(*) as CNT_TOTAL,
sum(inv.IN_STCK_BU_ITM_DAYS) as IN_STCK_BU_ITM_DAYS,
count(inv.IN_STCK_BU_ITM_DAYS) as CNT_IN_STCK_BU_ITM_DAYS,
sum(inv.UNDR_STCK_BU_ITM_DAYS) as UNDR_STCK_BU_ITM_DAYS,
count(inv.UNDR_STCK_BU_ITM_DAYS) as CNT_UNDR_STCK_BU_ITM_DAYS,
sum(nvl(inv.ON_HAND_QTY_PRMRY, 0)) as ON_HAND_QTY_PRMRY,
sum(nvl(inv.ON_HAND_QTY_ALT, 0)) as ON_HAND_QTY_ALT,
sum(inv.RECVD_QTY_PRMRY) as RECVD_QTY_PRMRY,
sum(inv.RECVD_QTY_ALT) as RECVD_QTY_ALT,
sum(nvl(inv.IN_TRANSIT_QTY_PRMRY, 0)) as IN_TRANSIT_QTY_PRMRY,
sum(nvl(inv.IN_TRANSIT_QTY_ALT, 0)) as IN_TRANSIT_QTY_ALT,
sum(nvl(inv.BCK_ORDR_QTY_PRMRY, 0)) as BCK_ORDR_QTY_PRMRY,
sum(nvl(inv.BCK_ORDR_QTY_ALT, 0)) as BCK_ORDR_QTY_ALT,
sum(nvl(inv.QLTY_HOLD_QTY_PRMRY, 0)) as QLTY_HOLD_QTY_PRMRY,
sum(nvl(inv.QLTY_HOLD_QTY_ALT, 0)) as QLTY_HOLD_QTY_ALT,
sum(nvl(inv.ON_HAND_NET_COST_AMT, 0)) as ON_HAND_NET_COST_AMT,
sum(nvl(inv.ON_HAND_NET_COST_AMT_RPT, 0)) as ON_HAND_NET_COST_AMT_RPT,
sum(inv.RECVD_NET_COST_AMT) as RECVD_NET_COST_AMT,
sum(inv.RECVD_NET_COST_AMT_RPT) as RECVD_NET_COST_AMT_RPT,
sum(nvl(inv.IN_TRANSIT_NET_COST_AMT, 0)) as IN_TRANSIT_NET_COST_AMT,
sum(nvl(inv.IN_TRANSIT_NET_COST_AMT_RPT, 0)) as IN_TRANSIT_NET_COST_AMT_RPT,
sum(nvl(inv.BCKORDR_NET_COST_AMT, 0)) as BCKORDR_NET_COST_AMT,
sum(nvl(inv.BCKORDR_NET_COST_AMT_RPT, 0)) as BCKORDR_NET_COST_AMT_RPT,
sum(nvl(inv.QLTY_HOLD_NET_COST_AMT, 0)) as QLTY_HOLD_NET_COST_AMT,
sum(nvl(inv.QLTY_HOLD_NET_COST_AMT_RPT, 0)) as QLTY_HOLD_NET_COST_AMT_RPT,
sum(nvl(inv.ON_HAND_RTL_AMT, 0)) as ON_HAND_RTL_AMT,
sum(nvl(inv.ON_HAND_RTL_AMT_RPT, 0)) as ON_HAND_RTL_AMT_RPT,
sum(inv.RECVD_RTL_AMT) as RECVD_RTL_AMT,
sum(inv.RECVD_RTL_AMT_RPT) as RECVD_RTL_AMT_RPT,
sum(nvl(inv.IN_TRANSIT_RTL_AMT, 0)) as IN_TRANSIT_RTL_AMT,
sum(nvl(inv.IN_TRANSIT_RTL_AMT_RPT, 0)) as IN_TRANSIT_RTL_AMT_RPT,
sum(nvl(inv.BCKORDR_RTL_AMT, 0)) as BCKORDR_RTL_AMT,
sum(nvl(inv.BCKORDR_RTL_AMT_RPT, 0)) as BCKORDR_RTL_AMT_RPT,
sum(nvl(inv.QLTY_HOLD_RTL_AMT, 0)) as QLTY_HOLD_RTL_AMT,
sum(nvl(inv.QLTY_HOLD_RTL_AMT_RPT, 0)) as QLTY_HOLD_RTL_AMT_RPT,
count(nvl(inv.ON_HAND_QTY_PRMRY, 0)) as CNT_ON_HAND_QTY_PRMRY,
count(nvl(inv.ON_HAND_QTY_ALT, 0)) as CNT_ON_HAND_QTY_ALT,
count(inv.RECVD_QTY_PRMRY) as CNT_RECVD_QTY_PRMRY,
count(inv.RECVD_QTY_ALT) as CNT_RECVD_QTY_ALT,
count(nvl(inv.IN_TRANSIT_QTY_PRMRY, 0)) as CNT_IN_TRANSIT_QTY_PRMRY,
count(nvl(inv.IN_TRANSIT_QTY_ALT, 0)) as CNT_IN_TRANSIT_QTY_ALT,
count(nvl(inv.BCK_ORDR_QTY_PRMRY, 0)) as CNT_BCK_ORDR_QTY_PRMRY,
count(nvl(inv.BCK_ORDR_QTY_ALT, 0)) as CNT_BCK_ORDR_QTY_ALT,
count(nvl(inv.QLTY_HOLD_QTY_PRMRY, 0)) as CNT_QLTY_HOLD_QTY_PRMRY,
count(nvl(inv.QLTY_HOLD_QTY_ALT, 0)) as CNT_QLTY_HOLD_QTY_ALT,
count(nvl(inv.ON_HAND_NET_COST_AMT, 0)) as CNT_ON_HAND_NET_COST_AMT,
count(nvl(inv.ON_HAND_NET_COST_AMT_RPT, 0)) as CNT_ON_HAND_NET_COST_AMT_RPT,
count(inv.RECVD_NET_COST_AMT) as CNT_RECVD_NET_COST_AMT,
count(inv.RECVD_NET_COST_AMT_RPT) as CNT_RECVD_NET_COST_AMT_RPT,
count(nvl(inv.IN_TRANSIT_NET_COST_AMT, 0)) as CNT_IN_TRANSIT_NET_COST_AMT,
count(nvl(inv.IN_TRANSIT_NET_COST_AMT_RPT, 0)) as CNT_IN_TRANSIT_NET_CST_AMT_RPT,
count(nvl(inv.BCKORDR_NET_COST_AMT, 0)) as CNT_BCKORDR_NET_COST_AMT,
count(nvl(inv.BCKORDR_NET_COST_AMT_RPT, 0)) as CNT_BCKORDR_NET_COST_AMT_RPT,
count(nvl(inv.QLTY_HOLD_NET_COST_AMT, 0)) as CNT_QLTY_HOLD_NET_COST_AMT,
count(nvl(inv.QLTY_HOLD_NET_COST_AMT_RPT, 0)) as CNT_QLTY_HOLD_NET_COST_AMT_RPT,
count(nvl(inv.ON_HAND_RTL_AMT, 0)) as CNT_ON_HAND_RTL_AMT,
count(nvl(inv.ON_HAND_RTL_AMT_RPT, 0)) as CNT_ON_HAND_RTL_AMT_RPT,
count(inv.RECVD_RTL_AMT) as CNT_RECVD_RTL_AMT,
count(inv.RECVD_RTL_AMT_RPT) as CNT_RECVD_RTL_AMT_RPT,
count(nvl(inv.IN_TRANSIT_RTL_AMT, 0)) as CNT_IN_TRANSIT_RTL_AMT,
count(nvl(inv.IN_TRANSIT_RTL_AMT_RPT, 0)) as CNT_IN_TRANSIT_RTL_AMT_RPT,
count(nvl(inv.BCKORDR_RTL_AMT, 0)) as CNT_BCKORDR_RTL_AMT,
count(nvl(inv.BCKORDR_RTL_AMT_RPT, 0)) as CNT_BCKORDR_RTL_AMT_RPT,
count(nvl(inv.QLTY_HOLD_RTL_AMT, 0)) as CNT_QLTY_HOLD_RTL_AMT,
count(nvl(inv.QLTY_HOLD_RTL_AMT_RPT, 0)) as CNT_QLTY_HOLD_RTL_AMT_RPT,
grouping_id (inv.ITM_TYP, inv.MFG_ITEM_CLASS_ID, inv.RTL_ITEM_CLASS_ID,
inv.ORG_RGN_ID, inv.ORG_CHAIN_ID, inv.BSNS_MNTH_ID) GROUPING_ID
from ddr_bs_inv_itm_bu_wk_mv inv
group by
Grouping sets((inv.BSNS_MNTH_ID, inv.ORG_RGN_ID, inv.MFG_ITEM_CLASS_ID, inv.RTL_ITEM_CLASS_ID, inv.ITM_TYP),
(inv.BSNS_MNTH_ID, inv.ORG_CHAIN_ID, inv.MFG_ITEM_CLASS_ID, inv.RTL_ITEM_CLASS_ID, inv.ITM_TYP))
[Home] [Help]