create view adw.com_item_hier_suv as 

select item_hier.com_item_seq_i, item_hier.ent_co_c, item_hier.ent_co_sub_c, item_hier.dept_i,item_hier.class_i, item_hier.sbcl_i, item_hier.item_i, item_hier.com_item_type_c, item_hier.com_item_crte_d, item_hier.com_item_desc_t, item_hier.com_item_stat_c, item_hier.item_actv_d, item_hier.com_item_src_del_f, item_hier.dir_class_seq_i, item_hier.dir_sbcl_seq_i, item_hier.dir_item_i, item_hier.dir_cost_a, item_hier.dir_vend_seq_i, item_hier.src_sys_c, item_hier.sbcl_seq_i, item_hier.styl_seq_i, item_hier.item_colr_seq_i, item_hier.item_ptrn_seq_i, item_hier.mdse_prslt_seq_i, item_hier.item_size_seq_i, item_hier.crte_d, item_hier.crte_ti,
latest_div.div_i,latest_div.div_n,latest_mdse.mdse_grp_i,latest_mdse.mdse_grp_n
from adw.com_item_hier_perf item_hier 
left outer join (select  dept.div_seq_i, dept.dept_i  from adw.dept dept  join
 (select dept_i,max(eff_d) dp_eff_d from adw.dept group by dept_i) curr_dept_i   on  dept.dept_i=curr_dept_i.dept_i    and                      dept.eff_d=curr_dept_i.dp_eff_d   ) latest_dept on item_hier.dept_i=latest_dept.dept_i left outer join 
(select div.mdse_grp_seq_i,div.div_i,div.div_n,div.div_seq_i,div.eff_d	from adw.div div
join
(select div_i,max(eff_d) di_eff_d from adw.div group by div_i) curr_div_i on div.div_i = curr_div_i.div_i and
div.eff_d= curr_div_i.di_eff_d) latest_div  on latest_dept.div_seq_i=latest_div.div_seq_i
left outer join
(select mdse.mdse_grp_i, mdse.mdse_grp_seq_i,mdse.mdse_grp_n from adw.mdse_grp mdse join
(select mdse_grp_i,max(eff_d) m_eff_d from adw. mdse_grp group by mdse_grp_i) curr_mdse_grp_i
on mdse. mdse_grp_i = curr_mdse_grp_i.mdse_grp_i  and mdse.eff_d= curr_mdse_grp_i.m_eff_d) latest_mdse
on latest_div.mdse_grp_seq_i=latest_mdse.mdse_grp_seq_i