VIEW: APPS.DDR_BS_ITM_BU_ASSC_DAY_MV#

Source

View Text - Preformatted

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

View Text - HTML Formatted

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