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 invrule.mfg_org_cd = busum.mfg_org_cd
and invrule.rtl_org_cd = busum.rtl_org_cd
and invrule.rtl_bsns_unit_id = busum.org_bsns_unit_id
and invrule.glbl_item_id = busum.glbl_item_id
and invrule.glbl_item_id_typ = busum.glbl_item_id_typ
and timehier.clndr_dt between invrule.eff_from_dt and invrule.eff_to_dt
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
[Home] [Help]