Current XLAT Values for a Field

The following field selects current active values for a field from the XLAT table.

-- ---------------
-- Dump XLAT Table
-- ---------------
select XLAT.FIELDVALUE,
       XLAT.XLATLONGNAME,
       XLAT.XLATSHORTNAME
  from XLATTABLE_VW XLAT
 where XLAT.FIELDNAME    = 'EMPL_STATUS'
   and XLAT.EFFDT = (select max(XLAT1.EFFDT)
                       from XLATTABLE_VW XLAT1                    
                      where XLAT1.FIELDNAME  = XLAT.FIELDNAME
                        and XLAT1.FIELDVALUE = XLAT.FIELDVALUE
                        and XLAT1.EFFDT <= SYSDATE)
   and XLAT.EFF_STATUS = 'A'