To Find Messages with Fields

To Find Messages with Fields 

select DISTINCT X.RECNAME,b.recdescr,

CASE b.RECTYPE

        WHEN 0 THEN 'Table'

        WHEN 1 THEN 'View'

        WHEN 2 THEN 'Derived'

        WHEN 3 THEN 'Sub Record'

        WHEN 5 THEN 'Dynamic View'

        WHEN 6 THEN 'Query View'

        WHEN 7 THEN 'Temporary Table'

        ELSE TO_CHAR(b.RECTYPE)

END  

,a.fieldname

, x.msgname,x.apmsgver ,p.ib_operationname,P.PARAMETERNAME,versionname,h.handlername,h.version

,Y.PACKAGEROOT,Y.APPCLASSID,Y.APPCLASSMETHOD

,h.active_flag from

psdbfield db,psrecfield a,psrecdefn b,psmsgrec x

  left outer join  psoprverdfnparm p on p.msgname=x.msgname and p.ib_msgversion=x.apmsgver

  left outer join PSOPRHDLR H on  H.IB_OPERATIONNAME=P.IB_OPERATIONNAME

  left outer join psoperationAC Y on H.IB_OPERATIONNAME=Y.IB_OPERATIONNAME

where --x.recname in (select distinct a.recname from psrecfield a,psrecdefn b where

--a.fieldname in ('SEX','ETHNIC_GRP_CD','MILITARY_STATUS','DISABILITY_STS_USA') and a.recname=b.recname

--and b.rectype in ('0','1','2')

--)

x.recname=a.recname

and a.fieldname=db.fieldname

and x.recname=b.recname

and a.recname=x.recname

and a.fieldname in ('flds')

-- and x.msgname in ('HIRE_JOB_DETAIL_RESPONSE_TIP','SPECL_PROJECT_SYNC','SPECL_PROJECT_SYNC')

and not exists (select 'y' from psmsgfldovr z

      where  z.msgname=x.msgname and

      z.fieldname in ('flds') ) ;