VIEW: APPS.DDR_SV_EXCPTN_MV#

Source

View Text - Preformatted

select excptn.BSNS_WK_ID
,      excptn.RTL_ORG_CD
,      excptn.ORG_RGN_ID
,      excptn.CHNL_TYP_CD
,      excptn.EXCPTN_TYP
,      excptn.MFG_ITEM_CLASS_ID
,      excptn.BSNS_WK_ID as BSNS_CLNDR_DIM_ID
,      decode (excptn.BSNS_WK_ID, 
               NULL, NULL,
               'WK') as BSNS_CLNDR_DIM_LVL
,      excptn.ORG_RGN_ID as ORG_DIM_ID
,      decode (excptn.ORG_RGN_ID, 
               NULL, NULL,
               'RGN') as ORG_DIM_LVL
,      excptn.MFG_ITEM_CLASS_ID as MFG_ITEM_DIM_ID
,      decode (excptn.MFG_ITEM_CLASS_ID, 
               NULL, NULL,
               'CLS') as MFG_ITEM_DIM_LVL
,      count(*) as CNT_TOTAL
,      sum(excptn.EXCPTN_CNT) as EXCPTN_CNT
,      sum(excptn.EXCPTN_QTY) as EXCPTN_QTY
,      sum(excptn.EXCPTN_AMT) as EXCPTN_AMT
,      count(excptn.EXCPTN_CNT) as CNT_EXCPTN_CNT
,      count(excptn.EXCPTN_QTY) as CNT_EXCPTN_QTY
,      count(excptn.EXCPTN_AMT) as CNT_EXCPTN_AMT
,      grouping_id (excptn.EXCPTN_TYP, excptn.MFG_ITEM_CLASS_ID, 
                    excptn.RTL_ORG_CD, excptn.ORG_RGN_ID,
                    excptn.BSNS_WK_ID, excptn.CHNL_TYP_CD) GROUPING_ID
from ddr_bs_excptn_sku_bu_wk_mv       excptn
group by
  Grouping sets((excptn.EXCPTN_TYP, excptn.BSNS_WK_ID, excptn.MFG_ITEM_CLASS_ID, excptn.ORG_RGN_ID, excptn.CHNL_TYP_CD),
                (excptn.EXCPTN_TYP, excptn.BSNS_WK_ID, excptn.MFG_ITEM_CLASS_ID, excptn.RTL_ORG_CD, excptn.CHNL_TYP_CD))

View Text - HTML Formatted

SELECT EXCPTN.BSNS_WK_ID
, EXCPTN.RTL_ORG_CD
, EXCPTN.ORG_RGN_ID
, EXCPTN.CHNL_TYP_CD
, EXCPTN.EXCPTN_TYP
, EXCPTN.MFG_ITEM_CLASS_ID
, EXCPTN.BSNS_WK_ID AS BSNS_CLNDR_DIM_ID
, DECODE (EXCPTN.BSNS_WK_ID
, NULL
, NULL
, 'WK') AS BSNS_CLNDR_DIM_LVL
, EXCPTN.ORG_RGN_ID AS ORG_DIM_ID
, DECODE (EXCPTN.ORG_RGN_ID
, NULL
, NULL
, 'RGN') AS ORG_DIM_LVL
, EXCPTN.MFG_ITEM_CLASS_ID AS MFG_ITEM_DIM_ID
, DECODE (EXCPTN.MFG_ITEM_CLASS_ID
, NULL
, NULL
, 'CLS') AS MFG_ITEM_DIM_LVL
, COUNT(*) AS CNT_TOTAL
, SUM(EXCPTN.EXCPTN_CNT) AS EXCPTN_CNT
, SUM(EXCPTN.EXCPTN_QTY) AS EXCPTN_QTY
, SUM(EXCPTN.EXCPTN_AMT) AS EXCPTN_AMT
, COUNT(EXCPTN.EXCPTN_CNT) AS CNT_EXCPTN_CNT
, COUNT(EXCPTN.EXCPTN_QTY) AS CNT_EXCPTN_QTY
, COUNT(EXCPTN.EXCPTN_AMT) AS CNT_EXCPTN_AMT
, GROUPING_ID (EXCPTN.EXCPTN_TYP
, EXCPTN.MFG_ITEM_CLASS_ID
, EXCPTN.RTL_ORG_CD
, EXCPTN.ORG_RGN_ID
, EXCPTN.BSNS_WK_ID
, EXCPTN.CHNL_TYP_CD) GROUPING_ID FROM DDR_BS_EXCPTN_SKU_BU_WK_MV EXCPTN GROUP BY GROUPING SETS((EXCPTN.EXCPTN_TYP
, EXCPTN.BSNS_WK_ID
, EXCPTN.MFG_ITEM_CLASS_ID
, EXCPTN.ORG_RGN_ID
, EXCPTN.CHNL_TYP_CD)
, (EXCPTN.EXCPTN_TYP
, EXCPTN.BSNS_WK_ID
, EXCPTN.MFG_ITEM_CLASS_ID
, EXCPTN.RTL_ORG_CD
, EXCPTN.CHNL_TYP_CD))