|
SELECT DISTINCT R.RECNAME , RF.FIELDNAME , R.OBJECTOWNERID , X.XLATLONGNAME FROM PSRECDEFN R , PSDBFIELD F , PSRECFIELDALL RF , PSXLATITEM X WHERE RF.RECNAME = R.RECNAME AND RF.FIELDNAME = F.FIELDNAME AND RF.FIELDNAME IN ( SELECT LM_USERID_FIELDS FROM PS_LM_USER_FLD_TBL) AND F.LENGTH >= 30 AND R.RECTYPE=0 AND X.FIELDNAME = 'OBJECTOWNERID' AND X.FIELDVALUE= R.OBJECTOWNERID AND X.EFFDT = ( SELECT MAX(X2.EFFDT) FROM PSXLATITEM X2 WHERE X2.FIELDNAME=X.FIELDNAME AND X2.FIELDVALUE = X.FIELDVALUE) AND NOT EXISTS ( SELECT 'X' FROM PSRECDEFN L WHERE L.RELLANGRECNAME = R.RECNAME) AND NOT EXISTS ( SELECT 'X' FROM PSAEAPPLSTATE A WHERE A.AE_STATE_RECNAME = R.RECNAME) AND R.RECNAME NOT LIKE '%RUN%' AND R.RECNAME NOT LIKE '%TAO%' AND R.RECNAME NOT LIKE '%TEMP%' AND R.RECNAME NOT LIKE '%TMP%' AND R.RECNAME NOT LIKE '%AUDIT%'
|