SELECT a.business_unit ,a.common_id ,b.item_nbr_charge ,i.language_cd ,i.descr ,d.descrshort FROM ps_item_sf a ,ps_item_xref b ,PS_TERM_VAL_LANG d ,ps_bus_unit_tbl_sf e ,PS_ITEM_TYPE_TBL f ,PS_ITEM_TYPE_LNG i WHERE f.setid=i.setid AND f.item_type=i.item_type AND f.effdt=i.effdt AND a.business_unit=b.business_unit AND a.common_id = b.common_id AND a.sa_id_type = b.sa_id_type AND a.item_nbr=b.item_nbr_charge AND a.item_term=d.strm AND a.business_unit=e.business_unit AND a.item_type=f.item_type AND f.effdt=( SELECT MAX(g.effdt) FROM PS_ITEM_TYPE_TBL g WHERE f.setid=g.setid AND f.item_type=g.item_type AND f.effdt <= %CurrentDateIn AND f.eff_status='A' ) AND f.setid=( SELECT h.setid FROM ps_set_cntrl_rec h WHERE h.setcntrlvalue=e.institution AND h.recname='ITEM_TYPE_TBL')
|