VIEW: APPS.DDR_SV_SHIP_MV#

Source

View Text - Preformatted

select                   
	  ship.BSNS_MNTH_ID,
	  ship.ORG_CHAIN_ID,
    ship.SHIP_TO_CHAIN_ID,
	  ship.MFG_ITEM_CLASS_ID,
	  ship.RTL_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,
	  ship.RTL_ITEM_CLASS_ID as RTL_ITEM_DIM_ID,
	  'CLS' as RTL_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_AMT_RPT) as SHIP_AMT_RPT,
	  sum(ship.SHIP_COST) as SHIP_COST,
	  sum(ship.SHIP_COST_RPT) as SHIP_COST_RPT,
	  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_AMT_RPT) as CNT_SHIP_AMT_RPT,
	  count(ship.SHIP_COST) as CNT_SHIP_COST,
	  count(ship.SHIP_COST_RPT) as CNT_SHIP_COST_RPT,
	  grouping_id (ship.MFG_ITEM_CLASS_ID, ship.RTL_ITEM_CLASS_ID,
	               ship.BSNS_MNTH_ID, ship.ORG_CHAIN_ID, ship.SHIP_TO_CHAIN_ID) GROUPING_ID
	from
	  ddr_bs_ship_itm_bu_wk_mv       ship
  group by
	  Grouping sets((ship.ORG_CHAIN_ID, ship.BSNS_MNTH_ID, ship.MFG_ITEM_CLASS_ID, ship.RTL_ITEM_CLASS_ID),
                  (ship.SHIP_TO_CHAIN_ID, ship.BSNS_MNTH_ID, ship.MFG_ITEM_CLASS_ID, ship.RTL_ITEM_CLASS_ID),
                  (ship.ORG_CHAIN_ID, ship.SHIP_TO_CHAIN_ID, ship.BSNS_MNTH_ID, ship.MFG_ITEM_CLASS_ID, ship.RTL_ITEM_CLASS_ID))

View Text - HTML Formatted

SELECT SHIP.BSNS_MNTH_ID
, SHIP.ORG_CHAIN_ID
, SHIP.SHIP_TO_CHAIN_ID
, SHIP.MFG_ITEM_CLASS_ID
, SHIP.RTL_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
, SHIP.RTL_ITEM_CLASS_ID AS RTL_ITEM_DIM_ID
, 'CLS' AS RTL_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_AMT_RPT) AS SHIP_AMT_RPT
, SUM(SHIP.SHIP_COST) AS SHIP_COST
, SUM(SHIP.SHIP_COST_RPT) AS SHIP_COST_RPT
, 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_AMT_RPT) AS CNT_SHIP_AMT_RPT
, COUNT(SHIP.SHIP_COST) AS CNT_SHIP_COST
, COUNT(SHIP.SHIP_COST_RPT) AS CNT_SHIP_COST_RPT
, GROUPING_ID (SHIP.MFG_ITEM_CLASS_ID
, SHIP.RTL_ITEM_CLASS_ID
, SHIP.BSNS_MNTH_ID
, SHIP.ORG_CHAIN_ID
, SHIP.SHIP_TO_CHAIN_ID) GROUPING_ID FROM DDR_BS_SHIP_ITM_BU_WK_MV SHIP GROUP BY GROUPING SETS((SHIP.ORG_CHAIN_ID
, SHIP.BSNS_MNTH_ID
, SHIP.MFG_ITEM_CLASS_ID
, SHIP.RTL_ITEM_CLASS_ID)
, (SHIP.SHIP_TO_CHAIN_ID
, SHIP.BSNS_MNTH_ID
, SHIP.MFG_ITEM_CLASS_ID
, SHIP.RTL_ITEM_CLASS_ID)
, (SHIP.ORG_CHAIN_ID
, SHIP.SHIP_TO_CHAIN_ID
, SHIP.BSNS_MNTH_ID
, SHIP.MFG_ITEM_CLASS_ID
, SHIP.RTL_ITEM_CLASS_ID))