WorkingScripts

select OCCURSLEVEL as level ,FIELDNUM as taborder, LBLTEXT as label ,
RECNAME as record ,FIELDNAME as field
from PSFSSYS.PSPNLFIELD
where PNLNAME ='PORTAL_HPCOMP'order by OCCURSLEVEL, FIELDNUM

SELECT PROJECTNAME, VERSION, PROJECTDESCR, TGTSERVERNAME, TGTDBNAME, TGTOPRID,
TGTOPRACCT, COMPRELEASE, SRCCOMPRELDTTM, TGTCOMPRELDTTM, COMPRELDTTM, KEEPTGT,
TGTORIENTATION, COMPARETYPE,
COMMITLIMIT, REPORTFILTER, MAINTPROJ, LASTUPDDTTM, LASTUPDOPRID, RELEASELABEL,
RELEASEDTTM, OBJECTOWNERID, DESCRLONG FROM PSPROJECTDEFN

SELECT RECNAME, FIELDNUM, FIELDNAME,
INTEGER(USEEDIT / 8) - INTEGER (USEEDIT / 16) * 2 as "ADD", 
INTEGER(USEEDIT / 128) - INTEGER(USEEDIT / 256)*2 as "CHANGE", 
INTEGER(USEEDIT / 1024) - INTEGER(USEEDIT/ 2048)*2  as "DELETE"
FROM PSRECFIELD 
     WHERE  RECNAME = 'PSOPRDEFN' 
             and USEEDIT <> 0       
             AND (INTEGER (USEEDIT / 128) - INTEGER (USEEDIT / 256) * 2 <> 0
             OR INTEGER (USEEDIT / 8) - INTEGER (USEEDIT / 16) * 2 <> 0
             OR INTEGER (USEEDIT / 1024) - INTEGER (USEEDIT / 2048) * 2 <> 0)
            ORDER BY RECNAME, FIELDNUM;

-- GET RECORD NAME DEFINITION
  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  RTRIM (CHAR (B.LENGTH))|| '.'||CHAR (B.DECIMALPOS)          
             ELSE CHAR (B.LENGTH)       
         END  AS FLDLEN,
         CASE          
            WHEN (A.USEEDIT/ 256)/ 2 <>  (A.USEEDIT/ 256)/ 2.0       
                  THEN 'YES' ELSE 'NO'
         END  AS REQ,
         CASE          
             WHEN A.USEEDIT/ 2 <> A.USEEDIT/ 2.0       
                  THEN             'KEY'          
             WHEN (A.USEEDIT/ 2)/ 2 <> (A.USEEDIT/ 2)/ 2.0       
                  THEN             'DUP'
             WHEN (A.USEEDIT/ 16)/ 2 <>(A.USEEDIT/16)/ 2.0
                  THEN 'ALT'          
             ELSE NULL       
        END  AS KEY_TYPE, 
        CASE
            WHEN (A.USEEDIT / 64) / 2 <> (A.USEEDIT/64)/2.0       
                  THEN             'DESC'
            WHEN (A.USEEDIT / 2 <> A.USEEDIT / 2.0       
                  OR (A.USEEDIT / 2) / 2 <> (A.USEEDIT / 2) / 2.0       
                  OR (A.USEEDIT / 16) / 2 <> (A.USEEDIT / 16) / 2.0)       
                  AND (A.USEEDIT / 64) / 2 = (A.USEEDIT / 64) / 2.0       
                  THEN             'ASC'
           ELSE             NULL       
        END  AS DIR,
        CASE
            WHEN (A.USEEDIT / 2048) / 2 <> (A.USEEDIT / 2048) / 2.0       
                 THEN             'YES'
            ELSE             'NO'
        END  AS SRCH,
        CASE
           WHEN (A.USEEDIT / 32) / 2 <> (A.USEEDIT / 32) / 2.0
                THEN             'YES'
           ELSE             'NO'
        END  AS LIST,
        CASE          WHEN (A.USEEDIT / 4)/ 2 <>(A.USEEDIT / 4)/ 2.0       
            THEN             'YES'
            ELSE             'NO'
        END  AS SYS, 
        CASE
            WHEN A.DEFRECNAME = ''       
             THEN             A.DEFFIELDNAME
             ELSE             RTRIM(A.DEFRECNAME)|| '.'|| A.DEFFIELDNAME
        END  AS DEFAULT_VALUE, 
        CASE
            WHEN (A.USEEDIT / 8) / 2 <> (A.USEEDIT / 8) / 2.0
                 AND (A.USEEDIT / 128) / 2 = (A.USEEDIT / 128) / 2.0       
                 AND (A.USEEDIT / 1024) / 2 = (A.USEEDIT / 1024) / 2.0       
            THEN             'A'
            WHEN (A.USEEDIT / 8) / 2 <> (A.USEEDIT / 8) / 2.0       
                  AND (A.USEEDIT / 128) / 2 <> (A.USEEDIT / 128) / 2.0       
                  AND (A.USEEDIT / 1024) / 2 = (A.USEEDIT / 1024) / 2.0
            THEN             'AC'
            WHEN (A.USEEDIT / 8) / 2 <> (A.USEEDIT / 8) / 2.0
                  AND (A.USEEDIT / 128) / 2 <> (A.USEEDIT / 128) / 2.0
                  AND (A.USEEDIT / 1024) / 2 <> (A.USEEDIT / 1024) / 2.0       
            THEN             'ACD'
            WHEN (A.USEEDIT / 8) / 2 = (A.USEEDIT / 8) / 2.0
                  AND (A.USEEDIT / 128) / 2 <> (A.USEEDIT / 128) / 2.0       
                  AND (A.USEEDIT / 1024) / 2 = (A.USEEDIT / 1024) / 2.0
            THEN             'C'
            WHEN (A.USEEDIT / 8) / 2 = (A.USEEDIT / 8) / 2.0
                 AND (A.USEEDIT / 128) / 2 <> (A.USEEDIT / 128) / 2.0
                 AND (A.USEEDIT / 1024) / 2 <> (A.USEEDIT / 1024) / 2.0
                 THEN             'CD'
            WHEN (A.USEEDIT / 8) / 2 = (A.USEEDIT / 8) / 2.0       
                 AND (A.USEEDIT / 128) / 2 = (A.USEEDIT / 128) / 2.0
                 AND (A.USEEDIT / 1024) / 2 <> (A.USEEDIT / 1024) / 2.0
           THEN             'D'
           ELSE             NULL       
           END  AS AUDT, CASE          WHEN (A.USEEDIT / 16384) / 2 <>          (A.USEEDIT / 16384) / 2.0       THEN             'PROMPT'          WHEN (A.USEEDIT / 512) / 2 <>          (A.USEEDIT / 512) / 2.0       THEN             'XLAT'          WHEN (A.USEEDIT / 8192) / 2 <>          (A.USEEDIT / 8192) / 2.0       THEN             'Y/N'          ELSE             NULL       END  AS EDIT, A.EDITTABLE  AS PROMPT_TABLE, A.SETCNTRLFLD  AS       SET_CONTROL_FLD, CASE          WHEN (A.USEEDIT / 4096) / 2 <>          (A.USEEDIT / 4096) / 2.0       THEN             'YES'          ELSE             'NO'       END  AS REASONABLE_DT, CASE          WHEN (A.USEEDIT       / 32768)       / 2 <>          (A.USEEDIT       / 32768)       / 2.0       THEN             'YES'          ELSE             'NO'       END  AS AUTO_UPDT, CASE          WHEN (A.USEEDIT / 262144) / 2 <> (A.USEEDIT / 262144) / 2.0       THEN             'FROM'          WHEN (A.USEEDIT / 524288) / 2 <> (A.USEEDIT / 524288) / 2.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 = 'PSOPRDEFN' AND A.FIELDNAME = B.FIELDNAMEORDER BY FIELDNUM

SELECT fieldvalue, eff_status, xlatlongname     FROM psxlatitem i     WHERE fieldname = 'RUNSTATUS'     AND effdt = (         SELECT max(effdt)         FROM   psxlatitem i1         WHERE  i1.fieldname = i.fieldname         AND    i1.fieldvalue = i.fieldvalue         AND    i1.effdt <= CURRENT DATE)    ORDER BY INTEGER(fieldvalue)

-- Get Query DefinitionSELECT OPRID, QRYNAME, DESCR, VERSION, LASTUPDDTTM, LASTUPDOPRID, QRYAPPROVED, CREATEOPRID,       CREATEDTTM,  QRYDISABLED,       QRYFOLDER, DESCRLONG  FROM PSFSSYS.PSQRYDEFN    where QRYNAME = '?'    and oprid = '?'with ur;

-- Get Query Bind VariablesSELECT OPRID, QRYNAME, BNDNAME, BNDNUM, FIELDNAME, HDGTYPE, HEADING, FIELDTYPE       , LENGTH, DECIMALPOS, FORMAT, EDITTABLE, USECOUNT, USEEDIT  FROM PSFSSYS.PSQRYBIND    where QRYNAME like '?%'    and oprid = '?'    with ur;

-- Get Query CriteriaSELECT OPRID, QRYNAME, SELNUM, CRTNUM, COMBTYPE, NEGATION, LPARENLVL,       LCRTSELNUM, LCRTFLDNUM, CONDTYPE, EXPRTYPE, R1CRTSELNUM, R1CRTFLDNUM,       R1CRTEXPNUM, R2CRTSELNUM, R2CRTFLDNUM, R2CRTEXPNUM, RPARENLVL,       QRYOJSELNUM  FROM PSFSSYS.PSQRYCRITERIA    where QRYNAME = '?'    and oprid = '?'with ur;

-- Get Query Output Field DefinitionsSELECT OPRID, QRYNAME, QRYFLDNAME, SELNUM, FLDNUM, RECNAME, FIELDNAME,       FLDRCDNUM, FLDEXPNUM, HDGTYPE, HEADING, COLUMNNUM, GROUPBYNUM,       ORDERBYNUM, ORDERBYDIR, TTLTYPE, SUBTTLNUM, USECOUNT, XLATTYPE,       XLATEXPRTYPE, XLATFLDNUM, XLATEXPNUM, AGGREGATEFUNC  FROM PSFSSYS.PSQRYFIELD    where QRYNAME  like '?%'     with ur;