PeopleSoft Tech Spec SQLs

----------RECNAME AND RECFIELD LEVEL SPECS GENERATION
SELECT RECNAME,FIELDNAME,
(CASE
WHEN USEEDIT=8388609 THEN 'K'
WHEN USEEDIT=8407329 OR USEEDIT=8390945 THEN 'K/S'
WHEN USEEDIT=8388673 THEN 'K/D' ELSE ' '
END) AS KSA
,(CASE WHEN USEEDIT=8407329 OR USEEDIT=8390945 THEN 'L' ELSE ' ' END) AS LISTBOX
,(CASE WHEN USEEDIT=8407329 OR USEEDIT=8390945 OR USEEDIT=8405248 THEN 'R' ELSE ' ' END) AS REQUIRED
,(CASE WHEN DEFRECNAME=' ' THEN DEFFIELDNAME ELSE DEFRECNAME||'.'||DEFFIELDNAME END) AS DEFAULTV,EDITTABLE FROM PSRECFIELDDB WHERE RECNAME IN
(
SELECT DISTINCT OBJECTVALUE1 FROM PSPROJECTITEM
WHERE PROJECTNAME = 'SCB_GF_DEPTID' AND OBJECTID1 = 1 AND OBJECTTYPE=0
)
ORDER BY RECNAME,FIELDNUM

----------RECNAME LEVEL SPECS
SELECT DISTINCT RECNAME,DECODE(RECTYPE,0,'Table',7,'Temp Table','DEF') AS RECTYPE,'A','New Record' FROM PSRECDEFN WHERE RECNAME IN
(
SELECT DISTINCT OBJECTVALUE1 FROM PSPROJECTITEM
WHERE PROJECTNAME = 'SCB_GF_DEPTID' AND OBJECTID1 = 1 AND OBJECTTYPE=0
)
ORDER BY RECNAME;
COMMIT;
-----------------------FIELD DETAILS - PROIDED THE PROJECT NAME

SELECT
F.FIELDNAME
,( CASE WHEN F.FIELDTYPE = 3 THEN 'Signed Number'
WHEN F.FIELDTYPE = 2 THEN 'Number'
WHEN F.FIELDTYPE = 0 THEN 'Character' END) AS FIELDTYPE
,( CASE WHEN F.FIELDTYPE = 3 THEN TO_CHAR(F.LENGTH-F.DECIMALPOS-1)||','||TO_CHAR(F.DECIMALPOS)
WHEN F.FIELDTYPE = 2 THEN TO_CHAR(F.LENGTH-F.DECIMALPOS)||','||TO_CHAR(F.DECIMALPOS)
WHEN F.FIELDTYPE = 0 THEN TO_CHAR(F.LENGTH) END) AS LENGTH
,L.LONGNAME
,L.SHORTNAME
,'A'
,'NEW FIELD'
FROM PSDBFIELD F,PSDBFLDLABL L WHERE F.FIELDNAME IN (
SELECT DISTINCT OBJECTVALUE1 FROM PSPROJECTITEM P WHERE PROJECTNAME = 'SCB_GF_DEPTID' AND OBJECTID1 = 6 AND OBJECTTYPE=2
)
AND F.FIELDNAME = L.FIELDNAME
AND L.DEFAULT_LABEL = 1
AND F.FIELDNAME LIKE 'SCB%'

------------------------------------------------XLAT VALUES BASED ON THE PRJ NAME-------------------
SELECT FIELDNAME
,FIELDVALUE AS XLATVALUE
,XLATLONGNAME AS LONGNAME
,XLATSHORTNAME AS SHORTNAME
,'A'
,'NEW XLAT'
FROM PSXLATITEM WHERE FIELDNAME IN (
SELECT DISTINCT OBJECTVALUE1 FROM PSPROJECTITEM P WHERE PROJECTNAME = 'SCB_GF_DEPTID' AND OBJECTID1 = 6 AND OBJECTTYPE=2 )
--------------------------------------------------PAGE NAMES----------------------------------------------------

SELECT * FROM PSPROJECTITEM P WHERE PROJECTNAME = 'SCB_GF_DEPTID' AND OBJECTTYPE=8 AND OBJECTID1 = 1
SELECT MESSAGE_SET_NBR,MESSAGE_NBR,'A',MESSAGE_TEXT FROM PSMSGCATDEFN WHERE MESSAGE_SET_NBR = 22300
------------------------------------------------------------------------------------