VIEW: APPS.DDR_BS_ORDR_ITM_BU_WK_MV#

Source

View Text - Preformatted

select                   
  ordr.MFG_ORG_CD,
  ordr.RTL_ORG_CD,
  ordr.ORG_BSNS_UNIT_ID,
  ordr.ITM_TYP,
  orghier.ORG_RGN_ID,
  orghier.ORG_CHAIN_ID,
  mfghier.MFG_ITEM_ID,
  mfghier.MFG_ITEM_CLASS_ID,
  rtlhier.RTL_ITEM_ID,
  rtlhier.RTL_ITEM_CLASS_ID,
  timehier.BSNS_WK_ID,
  timehier.BSNS_MNTH_ID,
  timehier.BSNS_YR_ID,
  count(*) as CNT_TOTAL,
  sum(nvl(ordr.ORDR_QTY_PRMRY, 0)) as ORDR_QTY_PRMRY,
  sum(nvl(ordr.ORDR_QTY_ALT, 0)) as ORDR_QTY_ALT,
  sum(nvl(ordr.ORDR_AMT, 0)) as ORDR_AMT,
  sum(nvl(ordr.ORDR_AMT_RPT, 0)) as ORDR_AMT_RPT,
  count(nvl(ordr.ORDR_QTY_PRMRY, 0)) as CNT_ORDR_QTY_PRMRY,
  count(nvl(ordr.ORDR_QTY_ALT, 0)) as CNT_ORDR_QTY_ALT,
  count(nvl(ordr.ORDR_AMT, 0)) as CNT_ORDR_AMT,
  count(nvl(ordr.ORDR_AMT_RPT, 0)) as CNT_ORDR_AMT_RPT
from
  ddr_b_rtl_ordr_item_day           ordr,
  ddr_r_base_day_dn_mv              timehier,
  ddr_r_mfg_item_sku_dn_mv          mfghier,
  ddr_r_rtl_item_sku_dn_mv          rtlhier,
  ddr_r_org_bu_dn_mv                orghier
where timehier.mfg_org_cd = ordr.mfg_org_cd
and   timehier.day_cd = ordr.day_cd
and   timehier.clndr_cd = CONCAT(ordr.mfg_org_cd, '-BSNS')
and   mfghier.mfg_sku_item_id = ordr.mfg_sku_item_id
and   rtlhier.rtl_sku_item_id = ordr.rtl_sku_item_id
and   orghier.org_bsns_unit_id = ordr.org_bsns_unit_id
group by
  timehier.BSNS_WK_ID,
  timehier.BSNS_MNTH_ID,
  timehier.BSNS_YR_ID,
  ordr.MFG_ORG_CD,
  ordr.RTL_ORG_CD,
  ordr.ORG_BSNS_UNIT_ID,
  ordr.ITM_TYP,
  orghier.ORG_RGN_ID,
  orghier.ORG_CHAIN_ID,
  mfghier.MFG_ITEM_ID,
  mfghier.MFG_ITEM_CLASS_ID,
  rtlhier.RTL_ITEM_ID,
  rtlhier.RTL_ITEM_CLASS_ID

View Text - HTML Formatted

SELECT ORDR.MFG_ORG_CD
, ORDR.RTL_ORG_CD
, ORDR.ORG_BSNS_UNIT_ID
, ORDR.ITM_TYP
, ORGHIER.ORG_RGN_ID
, ORGHIER.ORG_CHAIN_ID
, MFGHIER.MFG_ITEM_ID
, MFGHIER.MFG_ITEM_CLASS_ID
, RTLHIER.RTL_ITEM_ID
, RTLHIER.RTL_ITEM_CLASS_ID
, TIMEHIER.BSNS_WK_ID
, TIMEHIER.BSNS_MNTH_ID
, TIMEHIER.BSNS_YR_ID
, COUNT(*) AS CNT_TOTAL
, SUM(NVL(ORDR.ORDR_QTY_PRMRY
, 0)) AS ORDR_QTY_PRMRY
, SUM(NVL(ORDR.ORDR_QTY_ALT
, 0)) AS ORDR_QTY_ALT
, SUM(NVL(ORDR.ORDR_AMT
, 0)) AS ORDR_AMT
, SUM(NVL(ORDR.ORDR_AMT_RPT
, 0)) AS ORDR_AMT_RPT
, COUNT(NVL(ORDR.ORDR_QTY_PRMRY
, 0)) AS CNT_ORDR_QTY_PRMRY
, COUNT(NVL(ORDR.ORDR_QTY_ALT
, 0)) AS CNT_ORDR_QTY_ALT
, COUNT(NVL(ORDR.ORDR_AMT
, 0)) AS CNT_ORDR_AMT
, COUNT(NVL(ORDR.ORDR_AMT_RPT
, 0)) AS CNT_ORDR_AMT_RPT FROM DDR_B_RTL_ORDR_ITEM_DAY ORDR
, DDR_R_BASE_DAY_DN_MV TIMEHIER
, DDR_R_MFG_ITEM_SKU_DN_MV MFGHIER
, DDR_R_RTL_ITEM_SKU_DN_MV RTLHIER
, DDR_R_ORG_BU_DN_MV ORGHIER WHERE TIMEHIER.MFG_ORG_CD = ORDR.MFG_ORG_CD AND TIMEHIER.DAY_CD = ORDR.DAY_CD AND TIMEHIER.CLNDR_CD = CONCAT(ORDR.MFG_ORG_CD
, '-BSNS') AND MFGHIER.MFG_SKU_ITEM_ID = ORDR.MFG_SKU_ITEM_ID AND RTLHIER.RTL_SKU_ITEM_ID = ORDR.RTL_SKU_ITEM_ID AND ORGHIER.ORG_BSNS_UNIT_ID = ORDR.ORG_BSNS_UNIT_ID GROUP BY TIMEHIER.BSNS_WK_ID
, TIMEHIER.BSNS_MNTH_ID
, TIMEHIER.BSNS_YR_ID
, ORDR.MFG_ORG_CD
, ORDR.RTL_ORG_CD
, ORDR.ORG_BSNS_UNIT_ID
, ORDR.ITM_TYP
, ORGHIER.ORG_RGN_ID
, ORGHIER.ORG_CHAIN_ID
, MFGHIER.MFG_ITEM_ID
, MFGHIER.MFG_ITEM_CLASS_ID
, RTLHIER.RTL_ITEM_ID
, RTLHIER.RTL_ITEM_CLASS_ID