select
busum.MFG_ORG_CD,
busum.RTL_ORG_CD,
busum.ORG_BSNS_UNIT_ID,
busum.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,
count(decode(sign(busum.ON_HAND_QTY_PRMRY), 1, 1, NULL)) as IN_STCK_BU_ITM_DAYS,
count(decode(sign(busum.ON_HAND_QTY_PRMRY - invrule.MIN_STCK_ON_HAND_QTY_PRMRY), -1, 1, NULL)) as UNDR_STCK_BU_ITM_DAYS,
avg(busum.ON_HAND_QTY_PRMRY * 7) as ON_HAND_QTY_PRMRY,
avg(busum.ON_HAND_QTY_ALT * 7) as ON_HAND_QTY_ALT,
sum(nvl(busum.RECVD_QTY_PRMRY, 0)) as RECVD_QTY_PRMRY,
sum(nvl(busum.RECVD_QTY_ALT, 0)) as RECVD_QTY_ALT,
avg(busum.IN_TRANSIT_QTY_PRMRY * 7) as IN_TRANSIT_QTY_PRMRY,
avg(busum.IN_TRANSIT_QTY_ALT * 7) as IN_TRANSIT_QTY_ALT,
avg(busum.BCK_ORDR_QTY_PRMRY * 7) as BCK_ORDR_QTY_PRMRY,
avg(busum.BCK_ORDR_QTY_ALT * 7) as BCK_ORDR_QTY_ALT,
avg(busum.QLTY_HOLD_QTY_PRMRY * 7) as QLTY_HOLD_QTY_PRMRY,
avg(busum.QLTY_HOLD_QTY_ALT * 7) as QLTY_HOLD_QTY_ALT,
avg(busum.ON_HAND_NET_COST_AMT * 7) as ON_HAND_NET_COST_AMT,
avg(busum.ON_HAND_NET_COST_AMT_RPT * 7) as ON_HAND_NET_COST_AMT_RPT,
sum(nvl(busum.RECVD_NET_COST_AMT, 0)) as RECVD_NET_COST_AMT,
sum(nvl(busum.RECVD_NET_COST_AMT_RPT, 0)) as RECVD_NET_COST_AMT_RPT,
avg(busum.IN_TRANSIT_NET_COST_AMT * 7) as IN_TRANSIT_NET_COST_AMT,
avg(busum.IN_TRANSIT_NET_COST_AMT_RPT * 7) as IN_TRANSIT_NET_COST_AMT_RPT,
avg(busum.BCKORDR_NET_COST_AMT * 7) as BCKORDR_NET_COST_AMT,
avg(busum.BCKORDR_NET_COST_AMT_RPT * 7) as BCKORDR_NET_COST_AMT_RPT,
avg(busum.QLTY_HOLD_NET_COST_AMT * 7) as QLTY_HOLD_NET_COST_AMT,
avg(busum.QLTY_HOLD_NET_COST_AMT_RPT * 7) as QLTY_HOLD_NET_COST_AMT_RPT,
avg(busum.ON_HAND_RTL_AMT * 7) as ON_HAND_RTL_AMT,
avg(busum.ON_HAND_RTL_AMT_RPT * 7) as ON_HAND_RTL_AMT_RPT,
sum(nvl(busum.RECVD_RTL_AMT, 0)) as RECVD_RTL_AMT,
sum(nvl(busum.RECVD_RTL_AMT_RPT, 0)) as RECVD_RTL_AMT_RPT,
avg(busum.IN_TRANSIT_RTL_AMT * 7) as IN_TRANSIT_RTL_AMT,
avg(busum.IN_TRANSIT_RTL_AMT_RPT * 7) as IN_TRANSIT_RTL_AMT_RPT,
avg(busum.BCKORDR_RTL_AMT * 7) as BCKORDR_RTL_AMT,
avg(busum.BCKORDR_RTL_AMT_RPT * 7) as BCKORDR_RTL_AMT_RPT,
avg(busum.QLTY_HOLD_RTL_AMT * 7) as QLTY_HOLD_RTL_AMT,
avg(busum.QLTY_HOLD_RTL_AMT_RPT * 7) as QLTY_HOLD_RTL_AMT_RPT,
count(busum.ON_HAND_QTY_PRMRY * 7) as CNT_ON_HAND_QTY_PRMRY,
count(busum.ON_HAND_QTY_ALT * 7) as CNT_ON_HAND_QTY_ALT,
count(nvl(busum.RECVD_QTY_PRMRY, 0)) as CNT_RECVD_QTY_PRMRY,
count(nvl(busum.RECVD_QTY_ALT, 0)) as CNT_RECVD_QTY_ALT,
count(busum.IN_TRANSIT_QTY_PRMRY * 7) as CNT_IN_TRANSIT_QTY_PRMRY,
count(busum.IN_TRANSIT_QTY_ALT * 7) as CNT_IN_TRANSIT_QTY_ALT,
count(busum.BCK_ORDR_QTY_PRMRY * 7) as CNT_BCK_ORDR_QTY_PRMRY,
count(busum.BCK_ORDR_QTY_ALT * 7) as CNT_BCK_ORDR_QTY_ALT,
count(busum.QLTY_HOLD_QTY_PRMRY * 7) as CNT_QLTY_HOLD_QTY_PRMRY,
count(busum.QLTY_HOLD_QTY_ALT * 7) as CNT_QLTY_HOLD_QTY_ALT,
count(busum.ON_HAND_NET_COST_AMT * 7) as CNT_ON_HAND_NET_COST_AMT,
count(busum.ON_HAND_NET_COST_AMT_RPT * 7) as CNT_ON_HAND_NET_COST_AMT_RPT,
count(nvl(busum.RECVD_NET_COST_AMT, 0)) as CNT_RECVD_NET_COST_AMT,
count(nvl(busum.RECVD_NET_COST_AMT_RPT, 0)) as CNT_RECVD_NET_COST_AMT_RPT,
count(busum.IN_TRANSIT_NET_COST_AMT * 7) as CNT_IN_TRANSIT_NET_COST_AMT,
count(busum.IN_TRANSIT_NET_COST_AMT_RPT * 7) as CNT_IN_TRANSIT_NET_CST_AMT_RPT,
count(busum.BCKORDR_NET_COST_AMT * 7) as CNT_BCKORDR_NET_COST_AMT,
count(busum.BCKORDR_NET_COST_AMT_RPT * 7) as CNT_BCKORDR_NET_COST_AMT_RPT,
count(busum.QLTY_HOLD_NET_COST_AMT * 7) as CNT_QLTY_HOLD_NET_COST_AMT,
count(busum.QLTY_HOLD_NET_COST_AMT_RPT * 7) as CNT_QLTY_HOLD_NET_COST_AMT_RPT,
count(busum.ON_HAND_RTL_AMT * 7) as CNT_ON_HAND_RTL_AMT,
count(busum.ON_HAND_RTL_AMT_RPT * 7) as CNT_ON_HAND_RTL_AMT_RPT,
count(nvl(busum.RECVD_RTL_AMT, 0)) as CNT_RECVD_RTL_AMT,
count(nvl(busum.RECVD_RTL_AMT_RPT, 0)) as CNT_RECVD_RTL_AMT_RPT,
count(busum.IN_TRANSIT_RTL_AMT * 7) as CNT_IN_TRANSIT_RTL_AMT,
count(busum.IN_TRANSIT_RTL_AMT_RPT * 7) as CNT_IN_TRANSIT_RTL_AMT_RPT,
count(busum.BCKORDR_RTL_AMT * 7) as CNT_BCKORDR_RTL_AMT,
count(busum.BCKORDR_RTL_AMT_RPT * 7) as CNT_BCKORDR_RTL_AMT_RPT,
count(busum.QLTY_HOLD_RTL_AMT * 7) as CNT_QLTY_HOLD_RTL_AMT,
count(busum.QLTY_HOLD_RTL_AMT_RPT * 7) as CNT_QLTY_HOLD_RTL_AMT_RPT
from
ddr_bs_inv_sku_bu_day_mv busum,
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,
ddr_r_inv_rule_dn_mv invrule
where timehier.mfg_org_cd = busum.mfg_org_cd
and timehier.day_cd = busum.day_cd
and timehier.clndr_cd = CONCAT(busum.mfg_org_cd, '-BSNS')
and mfghier.mfg_sku_item_id = busum.mfg_sku_item_id
and rtlhier.rtl_sku_item_id = busum.rtl_sku_item_id
and orghier.org_bsns_unit_id = busum.org_bsns_unit_id
and orghier.org_cd = busum.rtl_org_cd
and invrule.rtl_org_cd = orghier.org_cd
and invrule.bsns_unit_cd = orghier.bsns_unit_cd
and invrule.glbl_item_id = busum.glbl_item_id
and invrule.glbl_item_id_typ = busum.glbl_item_id_typ
and invrule.rtl_sku_item_nbr = rtlhier.rtl_sku_item_nbr
group by
timehier.BSNS_WK_ID,
timehier.BSNS_MNTH_ID,
timehier.BSNS_YR_ID,
busum.MFG_ORG_CD,
busum.RTL_ORG_CD,
busum.ORG_BSNS_UNIT_ID,
busum.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
SELECT
BUSUM.MFG_ORG_CD
,
BUSUM.RTL_ORG_CD
,
BUSUM.ORG_BSNS_UNIT_ID
,
BUSUM.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
,
COUNT(DECODE(SIGN(BUSUM.ON_HAND_QTY_PRMRY)
, 1
, 1
, NULL)) AS IN_STCK_BU_ITM_DAYS
,
COUNT(DECODE(SIGN(BUSUM.ON_HAND_QTY_PRMRY - INVRULE.MIN_STCK_ON_HAND_QTY_PRMRY)
, -1
, 1
, NULL)) AS UNDR_STCK_BU_ITM_DAYS
,
AVG(BUSUM.ON_HAND_QTY_PRMRY * 7) AS ON_HAND_QTY_PRMRY
,
AVG(BUSUM.ON_HAND_QTY_ALT * 7) AS ON_HAND_QTY_ALT
,
SUM(NVL(BUSUM.RECVD_QTY_PRMRY
, 0)) AS RECVD_QTY_PRMRY
,
SUM(NVL(BUSUM.RECVD_QTY_ALT
, 0)) AS RECVD_QTY_ALT
,
AVG(BUSUM.IN_TRANSIT_QTY_PRMRY * 7) AS IN_TRANSIT_QTY_PRMRY
,
AVG(BUSUM.IN_TRANSIT_QTY_ALT * 7) AS IN_TRANSIT_QTY_ALT
,
AVG(BUSUM.BCK_ORDR_QTY_PRMRY * 7) AS BCK_ORDR_QTY_PRMRY
,
AVG(BUSUM.BCK_ORDR_QTY_ALT * 7) AS BCK_ORDR_QTY_ALT
,
AVG(BUSUM.QLTY_HOLD_QTY_PRMRY * 7) AS QLTY_HOLD_QTY_PRMRY
,
AVG(BUSUM.QLTY_HOLD_QTY_ALT * 7) AS QLTY_HOLD_QTY_ALT
,
AVG(BUSUM.ON_HAND_NET_COST_AMT * 7) AS ON_HAND_NET_COST_AMT
,
AVG(BUSUM.ON_HAND_NET_COST_AMT_RPT * 7) AS ON_HAND_NET_COST_AMT_RPT
,
SUM(NVL(BUSUM.RECVD_NET_COST_AMT
, 0)) AS RECVD_NET_COST_AMT
,
SUM(NVL(BUSUM.RECVD_NET_COST_AMT_RPT
, 0)) AS RECVD_NET_COST_AMT_RPT
,
AVG(BUSUM.IN_TRANSIT_NET_COST_AMT * 7) AS IN_TRANSIT_NET_COST_AMT
,
AVG(BUSUM.IN_TRANSIT_NET_COST_AMT_RPT * 7) AS IN_TRANSIT_NET_COST_AMT_RPT
,
AVG(BUSUM.BCKORDR_NET_COST_AMT * 7) AS BCKORDR_NET_COST_AMT
,
AVG(BUSUM.BCKORDR_NET_COST_AMT_RPT * 7) AS BCKORDR_NET_COST_AMT_RPT
,
AVG(BUSUM.QLTY_HOLD_NET_COST_AMT * 7) AS QLTY_HOLD_NET_COST_AMT
,
AVG(BUSUM.QLTY_HOLD_NET_COST_AMT_RPT * 7) AS QLTY_HOLD_NET_COST_AMT_RPT
,
AVG(BUSUM.ON_HAND_RTL_AMT * 7) AS ON_HAND_RTL_AMT
,
AVG(BUSUM.ON_HAND_RTL_AMT_RPT * 7) AS ON_HAND_RTL_AMT_RPT
,
SUM(NVL(BUSUM.RECVD_RTL_AMT
, 0)) AS RECVD_RTL_AMT
,
SUM(NVL(BUSUM.RECVD_RTL_AMT_RPT
, 0)) AS RECVD_RTL_AMT_RPT
,
AVG(BUSUM.IN_TRANSIT_RTL_AMT * 7) AS IN_TRANSIT_RTL_AMT
,
AVG(BUSUM.IN_TRANSIT_RTL_AMT_RPT * 7) AS IN_TRANSIT_RTL_AMT_RPT
,
AVG(BUSUM.BCKORDR_RTL_AMT * 7) AS BCKORDR_RTL_AMT
,
AVG(BUSUM.BCKORDR_RTL_AMT_RPT * 7) AS BCKORDR_RTL_AMT_RPT
,
AVG(BUSUM.QLTY_HOLD_RTL_AMT * 7) AS QLTY_HOLD_RTL_AMT
,
AVG(BUSUM.QLTY_HOLD_RTL_AMT_RPT * 7) AS QLTY_HOLD_RTL_AMT_RPT
,
COUNT(BUSUM.ON_HAND_QTY_PRMRY * 7) AS CNT_ON_HAND_QTY_PRMRY
,
COUNT(BUSUM.ON_HAND_QTY_ALT * 7) AS CNT_ON_HAND_QTY_ALT
,
COUNT(NVL(BUSUM.RECVD_QTY_PRMRY
, 0)) AS CNT_RECVD_QTY_PRMRY
,
COUNT(NVL(BUSUM.RECVD_QTY_ALT
, 0)) AS CNT_RECVD_QTY_ALT
,
COUNT(BUSUM.IN_TRANSIT_QTY_PRMRY * 7) AS CNT_IN_TRANSIT_QTY_PRMRY
,
COUNT(BUSUM.IN_TRANSIT_QTY_ALT * 7) AS CNT_IN_TRANSIT_QTY_ALT
,
COUNT(BUSUM.BCK_ORDR_QTY_PRMRY * 7) AS CNT_BCK_ORDR_QTY_PRMRY
,
COUNT(BUSUM.BCK_ORDR_QTY_ALT * 7) AS CNT_BCK_ORDR_QTY_ALT
,
COUNT(BUSUM.QLTY_HOLD_QTY_PRMRY * 7) AS CNT_QLTY_HOLD_QTY_PRMRY
,
COUNT(BUSUM.QLTY_HOLD_QTY_ALT * 7) AS CNT_QLTY_HOLD_QTY_ALT
,
COUNT(BUSUM.ON_HAND_NET_COST_AMT * 7) AS CNT_ON_HAND_NET_COST_AMT
,
COUNT(BUSUM.ON_HAND_NET_COST_AMT_RPT * 7) AS CNT_ON_HAND_NET_COST_AMT_RPT
,
COUNT(NVL(BUSUM.RECVD_NET_COST_AMT
, 0)) AS CNT_RECVD_NET_COST_AMT
,
COUNT(NVL(BUSUM.RECVD_NET_COST_AMT_RPT
, 0)) AS CNT_RECVD_NET_COST_AMT_RPT
,
COUNT(BUSUM.IN_TRANSIT_NET_COST_AMT * 7) AS CNT_IN_TRANSIT_NET_COST_AMT
,
COUNT(BUSUM.IN_TRANSIT_NET_COST_AMT_RPT * 7) AS CNT_IN_TRANSIT_NET_CST_AMT_RPT
,
COUNT(BUSUM.BCKORDR_NET_COST_AMT * 7) AS CNT_BCKORDR_NET_COST_AMT
,
COUNT(BUSUM.BCKORDR_NET_COST_AMT_RPT * 7) AS CNT_BCKORDR_NET_COST_AMT_RPT
,
COUNT(BUSUM.QLTY_HOLD_NET_COST_AMT * 7) AS CNT_QLTY_HOLD_NET_COST_AMT
,
COUNT(BUSUM.QLTY_HOLD_NET_COST_AMT_RPT * 7) AS CNT_QLTY_HOLD_NET_COST_AMT_RPT
,
COUNT(BUSUM.ON_HAND_RTL_AMT * 7) AS CNT_ON_HAND_RTL_AMT
,
COUNT(BUSUM.ON_HAND_RTL_AMT_RPT * 7) AS CNT_ON_HAND_RTL_AMT_RPT
,
COUNT(NVL(BUSUM.RECVD_RTL_AMT
, 0)) AS CNT_RECVD_RTL_AMT
,
COUNT(NVL(BUSUM.RECVD_RTL_AMT_RPT
, 0)) AS CNT_RECVD_RTL_AMT_RPT
,
COUNT(BUSUM.IN_TRANSIT_RTL_AMT * 7) AS CNT_IN_TRANSIT_RTL_AMT
,
COUNT(BUSUM.IN_TRANSIT_RTL_AMT_RPT * 7) AS CNT_IN_TRANSIT_RTL_AMT_RPT
,
COUNT(BUSUM.BCKORDR_RTL_AMT * 7) AS CNT_BCKORDR_RTL_AMT
,
COUNT(BUSUM.BCKORDR_RTL_AMT_RPT * 7) AS CNT_BCKORDR_RTL_AMT_RPT
,
COUNT(BUSUM.QLTY_HOLD_RTL_AMT * 7) AS CNT_QLTY_HOLD_RTL_AMT
,
COUNT(BUSUM.QLTY_HOLD_RTL_AMT_RPT * 7) AS CNT_QLTY_HOLD_RTL_AMT_RPT
FROM
DDR_BS_INV_SKU_BU_DAY_MV BUSUM
,
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
,
DDR_R_INV_RULE_DN_MV INVRULE
WHERE TIMEHIER.MFG_ORG_CD = BUSUM.MFG_ORG_CD
AND TIMEHIER.DAY_CD = BUSUM.DAY_CD
AND TIMEHIER.CLNDR_CD = CONCAT(BUSUM.MFG_ORG_CD
, '-BSNS')
AND MFGHIER.MFG_SKU_ITEM_ID = BUSUM.MFG_SKU_ITEM_ID
AND RTLHIER.RTL_SKU_ITEM_ID = BUSUM.RTL_SKU_ITEM_ID
AND ORGHIER.ORG_BSNS_UNIT_ID = BUSUM.ORG_BSNS_UNIT_ID
AND ORGHIER.ORG_CD = BUSUM.RTL_ORG_CD
AND INVRULE.RTL_ORG_CD = ORGHIER.ORG_CD
AND INVRULE.BSNS_UNIT_CD = ORGHIER.BSNS_UNIT_CD
AND INVRULE.GLBL_ITEM_ID = BUSUM.GLBL_ITEM_ID
AND INVRULE.GLBL_ITEM_ID_TYP = BUSUM.GLBL_ITEM_ID_TYP
AND INVRULE.RTL_SKU_ITEM_NBR = RTLHIER.RTL_SKU_ITEM_NBR
GROUP BY
TIMEHIER.BSNS_WK_ID
,
TIMEHIER.BSNS_MNTH_ID
,
TIMEHIER.BSNS_YR_ID
,
BUSUM.MFG_ORG_CD
,
BUSUM.RTL_ORG_CD
,
BUSUM.ORG_BSNS_UNIT_ID
,
BUSUM.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
|
|
|