PeopleSoft Wiki

SELECT
A.RECNAME,
A.FIELDNAME,
CASE
WHEN B.FIELDTYPE = 0 THEN
'CHAR'
WHEN B.FIELDTYPE = 1 THEN
'LONG CHAR'
WHEN B.FIELDTYPE = 2 THEN
'NUMBER'
WHEN B.FIELDTYPE = 3 THEN
'SIGNED NBR'
WHEN B.FIELDTYPE = 4 THEN
'DATE'
WHEN B.FIELDTYPE = 5 THEN
'TIME'
WHEN B.FIELDTYPE = 6 THEN
'DATETIME'
WHEN B.FIELDTYPE = 7
OR B.FIELDTYPE = 8 THEN
'IMAGE'
ELSE NULL
END AS FIELDTYPE,
CASE
WHEN B.FIELDTYPE = 2
OR B.FIELDTYPE = 3 THEN
TRIM(TO_CHAR(B.LENGTH)) || '.' || TO_CHAR(B.DECIMALPOS)
ELSE TO_CHAR(B.LENGTH)
END AS FLDLEN,
CASE
WHEN bitand(A.USEEDIT, 256) > 0 THEN
'YES'
ELSE 'NO'
END AS REQ,
CASE
WHEN bitand(A.USEEDIT, 1) > 0 THEN
'KEY'
WHEN bitand(A.USEEDIT, 2) > 0 THEN
'DUP'
WHEN bitand(A.USEEDIT, 16) > 0 THEN
'ALT'
ELSE NULL
END AS KEY_TYPE,
CASE
WHEN bitand(A.USEEDIT, 64) > 0 THEN
'DESC'
WHEN ( bitand(A.USEEDIT, 1) > 0
OR bitand(A.USEEDIT, 2) > 0
OR bitand(A.USEEDIT, 16) > 0 )
AND bitand(A.USEEDIT, 64) = 0 THEN
'ASC'
ELSE NULL
END AS DIR,
CASE
WHEN bitand(A.USEEDIT, 2048) > 0 THEN
'YES'
ELSE 'NO'
END AS SRCH,
CASE
WHEN bitand(A.USEEDIT, 32) > 0 THEN
'YES'
ELSE 'NO'
END AS LIST,
CASE
WHEN bitand(A.USEEDIT, 4) > 0 THEN
'YES'
ELSE 'NO'
END AS SYS,
CASE
WHEN TRIM(A.DEFRECNAME) = '' THEN
A.DEFFIELDNAME
ELSE
TRIM(A.DEFRECNAME) || '.' || A.DEFFIELDNAME
END AS DEFAULT_VALUE,
CASE
WHEN bitand(A.USEEDIT, 8) > 0
AND bitand(A.USEEDIT, 128) = 0
AND bitand(A.USEEDIT, 1024) = 0 THEN
'A'
WHEN bitand(A.USEEDIT, 8) > 0
AND bitand(A.USEEDIT, 128) > 0
AND bitand(A.USEEDIT, 1024) = 0 THEN
'AC'
WHEN bitand(A.USEEDIT, 8) > 0
AND bitand(A.USEEDIT, 128) > 0
AND bitand(A.USEEDIT, 1024) > 0 THEN
'ACD'
WHEN bitand(A.USEEDIT, 8) = 0
AND bitand(A.USEEDIT, 128) > 0
AND bitand(A.USEEDIT, 1024) = 0 THEN
'C'
WHEN bitand(A.USEEDIT, 8) = 0
AND bitand(A.USEEDIT, 128) > 0
AND bitand(A.USEEDIT, 1024) > 0 THEN
'CD'
WHEN bitand(A.USEEDIT, 8) = 0
AND bitand(A.USEEDIT, 128) = 0
AND bitand(A.USEEDIT, 1024) > 0 THEN
'D'
ELSE NULL
END AS AUDT,
CASE
WHEN bitand(A.USEEDIT, 16384) > 0 THEN
'PROMPT'
WHEN bitand(A.USEEDIT, 512) > 0 THEN
'XLAT'
WHEN bitand(A.USEEDIT, 8192) > 0 THEN
'Y/N'
ELSE NULL
END AS EDIT,
A.EDITTABLE AS PROMPT_TABLE,
A.SETCNTRLFLD AS SET_CONTROL_FLD,
CASE
WHEN bitand(A.USEEDIT, 4096) > 0 THEN
'YES'
ELSE 'NO'
END AS REASONABLE_DT,
CASE
WHEN bitand(A.USEEDIT, 32768) > 0 THEN
'YES'
ELSE 'NO'
END AS AUTO_UPDT,
CASE
WHEN bitand(A.USEEDIT, 262144) > 0 THEN
'FROM'
WHEN bitand(A.USEEDIT, 524288) > 0 THEN
'THROUGH'
ELSE NULL
END AS SEARCH_FIELD,
CASE
WHEN A.SUBRECORD = 'Y' THEN
'YES'
ELSE 'NO'
END AS SUBRECORD,
A.LASTUPDDTTM,
A.LASTUPDOPRID
FROM PSRECFIELD A,
PSDBFIELD B
WHERE A.RECNAME in (
Select RTRIM(ITEM.OBJECTVALUE1)
FROM PSPROJECTITEM ITEM, PSRECDEFN DEFN
WHERE ITEM.PROJECTNAME = ('RAHUL_TMP')
AND ITEM.OBJECTTYPE = 0
AND ITEM.OBJECTVALUE1 = DEFN.RECNAME
AND DEFN.LASTUPDDTTM > TO_DATE('2009-09-01', 'YYYY-MM-DD')
)
AND A.FIELDNAME = B.FIELDNAME and
ORDER BY FIELDNUM