VIEW: APPS.DDR_BS_EXCPTN_SKU_BU_WK_MV#

Source

View Text - Preformatted

select excptn.MFG_ORG_CD
,      excptn.RTL_ORG_CD
,      excptn.EXCPTN_TYP
,      excptn.ORG_BSNS_UNIT_ID
,      orghier.CHNL_TYP_CD
,      orghier.ORG_RGN_ID
,      mfghier.MFG_SKU_ITEM_ID
,      mfghier.MFG_ITEM_CLASS_ID
,      rtlhier.RTL_SKU_ITEM_ID
,      timehier.BSNS_WK_ID
,      count(*) as CNT_TOTAL
,      1 as EXCPTN_CNT
,      sum(nvl(excptn.EXCPTN_QTY, 0)) as EXCPTN_QTY
,      sum(nvl(excptn.EXCPTN_AMT, 0)) as EXCPTN_AMT
,      count(nvl(excptn.EXCPTN_QTY, 0)) as CNT_EXCPTN_QTY
,      count(nvl(excptn.EXCPTN_AMT, 0)) as CNT_EXCPTN_AMT
from   ddr_b_excptn_item_day                excptn
,      ddr_r_base_day_dn_mv                 timehier
,      ddr_r_org_bu_dn_mv                   orghier
,      ddr_r_mfg_item_sku_dn_mv             mfghier
,      ddr_r_rtl_item_sku_dn_mv             rtlhier
where timehier.mfg_org_cd = excptn.mfg_org_cd
and   timehier.day_cd = excptn.day_cd
and   timehier.clndr_cd = CONCAT(excptn.mfg_org_cd, '-BSNS')
and   mfghier.mfg_sku_item_id = excptn.mfg_sku_item_id
and   rtlhier.rtl_sku_item_id = excptn.rtl_sku_item_id
and   orghier.org_bsns_unit_id = excptn.org_bsns_unit_id
group by timehier.BSNS_WK_ID
,        excptn.MFG_ORG_CD
,        excptn.RTL_ORG_CD
,        excptn.EXCPTN_TYP
,        excptn.ORG_BSNS_UNIT_ID
,        orghier.CHNL_TYP_CD
,        orghier.ORG_RGN_ID
,        mfghier.MFG_SKU_ITEM_ID
,        mfghier.MFG_ITEM_CLASS_ID
,        rtlhier.RTL_SKU_ITEM_ID

View Text - HTML Formatted

SELECT EXCPTN.MFG_ORG_CD
, EXCPTN.RTL_ORG_CD
, EXCPTN.EXCPTN_TYP
, EXCPTN.ORG_BSNS_UNIT_ID
, ORGHIER.CHNL_TYP_CD
, ORGHIER.ORG_RGN_ID
, MFGHIER.MFG_SKU_ITEM_ID
, MFGHIER.MFG_ITEM_CLASS_ID
, RTLHIER.RTL_SKU_ITEM_ID
, TIMEHIER.BSNS_WK_ID
, COUNT(*) AS CNT_TOTAL
, 1 AS EXCPTN_CNT
, SUM(NVL(EXCPTN.EXCPTN_QTY
, 0)) AS EXCPTN_QTY
, SUM(NVL(EXCPTN.EXCPTN_AMT
, 0)) AS EXCPTN_AMT
, COUNT(NVL(EXCPTN.EXCPTN_QTY
, 0)) AS CNT_EXCPTN_QTY
, COUNT(NVL(EXCPTN.EXCPTN_AMT
, 0)) AS CNT_EXCPTN_AMT FROM DDR_B_EXCPTN_ITEM_DAY EXCPTN
, DDR_R_BASE_DAY_DN_MV TIMEHIER
, DDR_R_ORG_BU_DN_MV ORGHIER
, DDR_R_MFG_ITEM_SKU_DN_MV MFGHIER
, DDR_R_RTL_ITEM_SKU_DN_MV RTLHIER WHERE TIMEHIER.MFG_ORG_CD = EXCPTN.MFG_ORG_CD AND TIMEHIER.DAY_CD = EXCPTN.DAY_CD AND TIMEHIER.CLNDR_CD = CONCAT(EXCPTN.MFG_ORG_CD
, '-BSNS') AND MFGHIER.MFG_SKU_ITEM_ID = EXCPTN.MFG_SKU_ITEM_ID AND RTLHIER.RTL_SKU_ITEM_ID = EXCPTN.RTL_SKU_ITEM_ID AND ORGHIER.ORG_BSNS_UNIT_ID = EXCPTN.ORG_BSNS_UNIT_ID GROUP BY TIMEHIER.BSNS_WK_ID
, EXCPTN.MFG_ORG_CD
, EXCPTN.RTL_ORG_CD
, EXCPTN.EXCPTN_TYP
, EXCPTN.ORG_BSNS_UNIT_ID
, ORGHIER.CHNL_TYP_CD
, ORGHIER.ORG_RGN_ID
, MFGHIER.MFG_SKU_ITEM_ID
, MFGHIER.MFG_ITEM_CLASS_ID
, RTLHIER.RTL_SKU_ITEM_ID