[Home] [Help]
MATERIALIZED VIEW: APPS.DDR_BS_ITM_BU_ASSC_DAY_MV
Source
select assoc.MFG_ORG_CD
, assoc.RTL_ORG_CD
, assoc.RTL_BSNS_UNIT_ID
, mfghier.MFG_ITEM_ID
, rtlhier.RTL_ITEM_ID
, timehier.CLNDR_CD
, timehier.DAY_CD
, count(*) as TOTAL_BU_ITM_DAYS
from ddr_r_item_bsns_unt_assc assoc
, ddr_r_base_day_dn_mv timehier
, ddr_r_mfg_item_sku_dn_mv mfghier
, ddr_r_rtl_item_sku_dn_mv rtlhier
where timehier.mfg_org_cd = assoc.mfg_org_cd
and timehier.clndr_cd = CONCAT(assoc.mfg_org_cd, '-BSNS')
and timehier.clndr_dt between nvl(assoc.eff_from_dt, to_date('01-JAN-1900')) and nvl(assoc.eff_to_dt, to_date('31-DEC-9999'))
and mfghier.mfg_sku_item_id = assoc.mfg_sku_item_id
and rtlhier.mfg_org_cd = assoc.mfg_org_cd
and rtlhier.org_cd = assoc.rtl_org_cd
and rtlhier.glbl_item_id = assoc.glbl_item_id
and rtlhier.glbl_item_id_typ = assoc.glbl_item_id_typ
and timehier.clndr_dt between nvl(rtlhier.eff_from_dt, to_date('01-JAN-1900')) and nvl(rtlhier.eff_to_dt, to_date('31-DEC-9999'))
group by assoc.MFG_ORG_CD
, assoc.RTL_ORG_CD
, assoc.RTL_BSNS_UNIT_ID
, mfghier.MFG_ITEM_ID
, rtlhier.RTL_ITEM_ID
, timehier.CLNDR_CD
, timehier.DAY_CD