VIEW: APPS.DDR_BS_MFG_SHIP_ITM_BU_WK_MV#

Source

View Text - Preformatted

SELECT ship.BSNS_UNIT_ID, ship.SHIP_TO_ORG_CD, ship.SHIP_TO_BSNS_UNIT_ID, orghier.ORG_RGN_ID, orghier.ORG_CHAIN_ID, 
         shiptoorghier.ORG_RGN_ID "SHIP_TO_RGN_ID", shiptoorghier.ORG_CHAIN_ID "SHIP_TO_CHAIN_ID", mfghier.MFG_ITEM_ID, mfghier.MFG_ITEM_CLASS_ID, 
         timehier.BSNS_WK_ID, timehier.BSNS_MNTH_ID, timehier.BSNS_YR_ID, 
	count(*) as CNT_TOTAL, 
	sum(nvl(ship.SHIP_QTY_PRMRY, 0)) as SHIP_QTY_PRMRY,
	sum(nvl(ship.SHIP_QTY_ALT, 0)) as SHIP_QTY_ALT, 
	sum(nvl(ship.SHIP_AMT, 0)) as SHIP_AMT, 
	sum(nvl(ship.SHIP_COST, 0)) as SHIP_COST,  
	count(nvl(ship.SHIP_QTY_PRMRY, 0)) as CNT_SHIP_QTY_PRMRY, 
	count(nvl(ship.SHIP_QTY_ALT, 0)) as CNT_SHIP_QTY_ALT, 
	count(nvl(ship.SHIP_AMT, 0)) as CNT_SHIP_AMT, 
	count(nvl(ship.SHIP_COST, 0)) as CNT_SHIP_COST 
	FROM 
	ddr_b_mfg_ship_item_day ship, 
	ddr_r_base_day_dn_mv  timehier, 
	ddr_r_mfg_item_sku_dn_mv mfghier, 
	ddr_r_org_bu_dn_mv orghier, 
	ddr_r_org_bu_dn_mv shiptoorghier
	WHERE 
  timehier.day_cd = ship.day_cd 
	and   timehier.clndr_cd = CONCAT(timehier.mfg_org_cd, '-BSNS') 
	and   mfghier.mfg_sku_item_id = ship.mfg_sku_item_id 
	and   orghier.org_bsns_unit_id = ship.bsns_unit_id 
	and   shiptoorghier.org_bsns_unit_id = ship.ship_to_bsns_unit_id
	GROUP BY 
	timehier.BSNS_WK_ID, timehier.BSNS_MNTH_ID, timehier.BSNS_YR_ID, ship.SHIP_TO_ORG_CD, ship.BSNS_UNIT_ID, ship.SHIP_TO_BSNS_UNIT_ID, orghier.ORG_RGN_ID, 
  orghier.ORG_CHAIN_ID, shiptoorghier.ORG_RGN_ID, shiptoorghier.ORG_CHAIN_ID, mfghier.MFG_ITEM_ID, mfghier.MFG_ITEM_CLASS_ID

View Text - HTML Formatted

SELECT SHIP.BSNS_UNIT_ID
, SHIP.SHIP_TO_ORG_CD
, SHIP.SHIP_TO_BSNS_UNIT_ID
, ORGHIER.ORG_RGN_ID
, ORGHIER.ORG_CHAIN_ID
, SHIPTOORGHIER.ORG_RGN_ID "SHIP_TO_RGN_ID"
, SHIPTOORGHIER.ORG_CHAIN_ID "SHIP_TO_CHAIN_ID"
, MFGHIER.MFG_ITEM_ID
, MFGHIER.MFG_ITEM_CLASS_ID
, TIMEHIER.BSNS_WK_ID
, TIMEHIER.BSNS_MNTH_ID
, TIMEHIER.BSNS_YR_ID
, COUNT(*) AS CNT_TOTAL
, SUM(NVL(SHIP.SHIP_QTY_PRMRY
, 0)) AS SHIP_QTY_PRMRY
, SUM(NVL(SHIP.SHIP_QTY_ALT
, 0)) AS SHIP_QTY_ALT
, SUM(NVL(SHIP.SHIP_AMT
, 0)) AS SHIP_AMT
, SUM(NVL(SHIP.SHIP_COST
, 0)) AS SHIP_COST
, COUNT(NVL(SHIP.SHIP_QTY_PRMRY
, 0)) AS CNT_SHIP_QTY_PRMRY
, COUNT(NVL(SHIP.SHIP_QTY_ALT
, 0)) AS CNT_SHIP_QTY_ALT
, COUNT(NVL(SHIP.SHIP_AMT
, 0)) AS CNT_SHIP_AMT
, COUNT(NVL(SHIP.SHIP_COST
, 0)) AS CNT_SHIP_COST FROM DDR_B_MFG_SHIP_ITEM_DAY SHIP
, DDR_R_BASE_DAY_DN_MV TIMEHIER
, DDR_R_MFG_ITEM_SKU_DN_MV MFGHIER
, DDR_R_ORG_BU_DN_MV ORGHIER
, DDR_R_ORG_BU_DN_MV SHIPTOORGHIER WHERE TIMEHIER.DAY_CD = SHIP.DAY_CD AND TIMEHIER.CLNDR_CD = CONCAT(TIMEHIER.MFG_ORG_CD
, '-BSNS') AND MFGHIER.MFG_SKU_ITEM_ID = SHIP.MFG_SKU_ITEM_ID AND ORGHIER.ORG_BSNS_UNIT_ID = SHIP.BSNS_UNIT_ID AND SHIPTOORGHIER.ORG_BSNS_UNIT_ID = SHIP.SHIP_TO_BSNS_UNIT_ID GROUP BY TIMEHIER.BSNS_WK_ID
, TIMEHIER.BSNS_MNTH_ID
, TIMEHIER.BSNS_YR_ID
, SHIP.SHIP_TO_ORG_CD
, SHIP.BSNS_UNIT_ID
, SHIP.SHIP_TO_BSNS_UNIT_ID
, ORGHIER.ORG_RGN_ID
, ORGHIER.ORG_CHAIN_ID
, SHIPTOORGHIER.ORG_RGN_ID
, SHIPTOORGHIER.ORG_CHAIN_ID
, MFGHIER.MFG_ITEM_ID
, MFGHIER.MFG_ITEM_CLASS_ID