The Spirit Of The Age

The following SQL can be used to detemine an Employee's current age based on their birthdate.

select PER.EMPLID,
       PER.BIRTHDATE,
       case
         when TO_CHAR(PER.BIRTHDATE,'MMDD') <= TO_CHAR(SYSDATE,'MMDD')
          then TO_NUMBER(TO_CHAR(SYSDATE,'YYYY')) - TO_NUMBER(TO_CHAR(PER.BIRTHDATE,'YYYY'))
          else TO_NUMBER(TO_CHAR(SYSDATE,'YYYY')) - TO_NUMBER(TO_CHAR(PER.BIRTHDATE,'YYYY')) - 1
       end as AGE
  from PS_PERSONAL_DATA PER

Feel free to replace SYSDATE with whatever date you want to calculate the ages at. That's the spirit of the age...

Finding a Time Admin Instance Number

SQL to find the Time Admin instance number for a particular run.

-- -------------------------------------------------------
-- Get the instance number for the Time Admin temp tables
-- prompting for the process instance number.
-- -------------------------------------------------------  
  select '1' as INSTANCE
    from PS_TL_IPT11
   where PROCESS_INSTANCE = :PROCESS_INSTANCE
union
  select '2' as INSTANCE
    from PS_TL_IPT12
   where PROCESS_INSTANCE = :PROCESS_INSTANCE
union
  select '3' as INSTANCE
    from PS_TL_IPT13
   where PROCESS_INSTANCE = :PROCESS_INSTANCE
union
  select '4' as INSTANCE
    from PS_TL_IPT14
   where PROCESS_INSTANCE = :PROCESS_INSTANCE
union
  select '5' as INSTANCE
    from PS_TL_IPT15
   where PROCESS_INSTANCE = :PROCESS_INSTANCE
union
  select '6' as INSTANCE
    from PS_TL_IPT16
   where PROCESS_INSTANCE = :PROCESS_INSTANCE
union
  select '7' as INSTANCE
    from PS_TL_IPT17
   where PROCESS_INSTANCE = :PROCESS_INSTANCE
union
  select '8' as INSTANCE
    from PS_TL_IPT18
   where PROCESS_INSTANCE = :PROCESS_INSTANCE
union
  select '9' as INSTANCE
    from PS_TL_IPT19
   where PROCESS_INSTANCE = :PROCESS_INSTANCE
union
  select '10' as INSTANCE
    from PS_TL_IPT110
   where PROCESS_INSTANCE = :PROCESS_INSTANCE
union
  select '11' as INSTANCE
    from PS_TL_IPT111
   where PROCESS_INSTANCE = :PROCESS_INSTANCE
union
  select '12'
    from PS_TL_IPT112
   where PROCESS_INSTANCE = :PROCESS_INSTANCE
 union
  select '13' as INSTANCE
    from PS_TL_IPT113
   where PROCESS_INSTANCE = :PROCESS_INSTANCE

Batchman

SQL to determine how many members are in each of the Batches for a Time Admin run. The example shown below is for instance 7. Change this number to be the instance of the run you are interested in.

select batch_num,
           count(*)
   from PS_TL_TA_BATCH7
 group by BATCH_NUM
 order by BATCH_NUM Asc

Note that you can limit the maximum size of a batch from: -

Home > Setup HRMS > Install > Product and Country Specific > Time and Labor Installation

To see which rules are being processed for a particular batch you can use the following SQL: -

select BATCH_NUM,
       RULE_PGM_ID,
       PRIORITY,
       TL_RULE_ID
  from PS_TL_RULE_MAP7
 where BATCH_NUM = :BATCH_NUM
 order by BATCH_NUM,
          PRIORITY

You can use the following SQL to work out the approximate percentage complete of a Time Admin run.

select ROUND( ( ( ( select SUM(BA.END_DT - BA.START_DT)
                      from PS_TL_TA_BATCH7 BA,
                           PS_TL_RULE_MAP7 RM
                    where BA.BATCH_NUM <= :COMPLETED_BATCHES
                      and BA.BATCH_NUM = RM.BATCH_NUM
                  ) /
                  ( select SUM(BA.END_DT - BA.START_DT)
                      from PS_TL_TA_BATCH7 BA,
                           PS_TL_RULE_MAP7 RM
                     where BA.BATCH_NUM = RM.BATCH_NUM
                  ) 
                ) * 100
              ), 2
            ) AS PERCENT_COMPLETE
  from DUAL

What it does is mutiplies the number of employees in the completed batches by the number of rules for each, does the same for the total, divides one by the other, multiplies the result by a 100 and bingo! Not perfect but better than a poke in the eye with a stick to help you work out how much time you need to wait.

Get Table Details

Sometimes when you're documenting you need to get a list of fields, fieldnames, types and their length. This little piece of SQL marries an Oracle system table and two PeopleSoft tables to furnish this information.

select ATC.COLUMN_ID,
       ATC.COLUMN_NAME,
       LAB.LONGNAME,
       CASE
         WHEN FLD.FIELDTYPE = 0 THEN 'Character'
         WHEN FLD.FIELDTYPE = 1 THEN 'Long Character'
         WHEN FLD.FIELDTYPE = 2 THEN 'Number'
         WHEN FLD.FIELDTYPE = 3 THEN 'Signed Number'
         WHEN FLD.FIELDTYPE = 4 THEN 'Date'
         WHEN FLD.FIELDTYPE = 5 THEN 'Time'
         WHEN FLD.FIELDTYPE = 6 THEN 'Date Time'
         WHEN FLD.FIELDTYPE = 8 THEN 'Image or Attachment'
         WHEN FLD.FIELDTYPE = 9 THEN 'ImageReference'
         ELSE                        'Unknown'
       END as TYPE,
       FLD.LENGTH
  from ALL_TAB_COLUMNS ATC,
       PSDBFLDLABL     LAB,
       PSDBFIELD       FLD
 where ATC.TABLE_NAME    = 'PS_JOB'
   and ATC.COLUMN_NAME   = LAB.FIELDNAME
   and LAB.DEFAULT_LABEL = 1
   and LAB.FIELDNAME     = FLD.FIELDNAME
 order by ATC.COLUMN_ID Asc

SQL to Find the Rules that SQL contain a Given SQL Object

select TL_RULE_ID
 from PS_TL_RULE_STEPS
where SQL_ID = 'XXX'

Getting the Mass Time Run Control Parameters

Getting the mass time run-control parameters is a bit tricky as it has no run-control table as such. Rather there are a number of base tables that are populated by the run page and used to run the process. The following SQL allows you to examine these tables to allow you to extrapolate what was entered.

Get the process instances of the Mass time process.

To get at the data you need you require the process instance numbers of the ST_LIB process. This allows you to lookup the other details you need.

-- -------------------------------------
-- Get process instances for ST Lib job.
-- -------------------------------------
select PRCS.PRCSINSTANCE,
       PRCS.OPRID,
       PRCS.PRCSJOBNAME,
       PRCS.PRCSNAME,
       PRCS.RUNCNTLID,
       XLAT.XLATLONGNAME,
       to_char(PRCS.BEGINDTTM,'yyyy-mm-dd-hh24.mi.ss') as Began,
       to_char(PRCS.ENDDTTM,'yyyy-mm-dd-hh24.mi.ss')   as Ended,
       (((TO_NUMBER(TO_CHAR(PRCS.ENDDTTM, 'J')) - TO_NUMBER(TO_CHAR(PRCS.BEGINDTTM, 'J'))) * 86400)+(TO_NUMBER(TO_CHAR(PRCS.ENDDTTM, 'SSSSS')) - TO_NUMBER(TO_CHAR(PRCS.BEGINDTTM, 'SSSSS')))) as DURSEC
  from PSPRCSRQST   PRCS,
       XLATTABLE_VW XLAT
where PRCS.RUNSTATUS    = XLAT.FIELDVALUE
  and PRCS.PRCSNAME     = 'TL_ST_LIB'
  and XLAT.FIELDNAME    = 'RUNSTATUS'
  and XLAT.EFFDT        = (select max(XLAT1.EFFDT)
                             from XLATTABLE_VW XLAT1
                            where XLAT1.FIELDNAME  = XLAT.FIELDNAME
                              and XLAT1.FIELDVALUE = XLAT.FIELDVALUE
                              and XLAT1.EFFDT <= SYSDATE)
  and XLAT.EFF_STATUS = 'A'
  and PRCS.BEGINDTTM  between to_date('2009-05-19-01.00.00','YYYY-MM-DD-HH24.MI.SS')
                          and to_date('2009-05-19-23.59.00','YYYY-MM-DD-HH24.MI.SS')
  and PRCS.ENDDTTM is not null
  and (((TO_NUMBER(TO_CHAR(PRCS.ENDDTTM, 'J')) - TO_NUMBER(TO_CHAR(PRCS.BEGINDTTM, 'J'))) * 86400)+(TO_NUMBER(TO_CHAR(PRCS.ENDDTTM, 'SSSSS')) - TO_NUMBER(TO_CHAR(PRCS.BEGINDTTM, 'SSSSS')))) > 500
-- order by PRCS.BEGINDTTM Desc
order by DURSEC Desc

Select Time Reporters

Next use your newly acquired process instance number to work out what was entered on the Select Time Reporters page.

-- -----------------------------------------------------------------------------
-- 1. Select Time Reporters Page
-- -----------------------------------------------------------------------------
-- Note that it is not possible to determine the parameters entered on this page
-- as  the  only  thing  stored  in  PeopleSoft is the employee IDs selected who
-- matched the criteria.
--
-- To  allow an educated guess to be made as to what they were the following SQL
-- dumps  the  mebers  prompt  field  details  out, so if for example you have a 
-- number of members returned who have the same group, then it stands a good
-- chance that this group was entered.
-- -----------------------------------------------------------------------------
-- Get Time Reporter(s) who were selected.
-- ------------------------------------------
select GDT.GROUP_ID,
       EDT.EMPLID,
       upper(NAM.LAST_NAME) as LAST_NAME,
       NAM.FIRST_NAME,
       JOB.BUSINESS_UNIT,
       JOB.JOBCODE,
       JOB.DEPTID,
       JOB.TY_LOCAL_ID
  from PS_TL_WRKGRP_TBL WRK,
       PS_TL_EMPL_DATA  EDT,
       PS_TL_MASS_TR    MAS,
       PS_JOB           JOB,
       PS_TL_GROUP_DTL  GDT,
       PS_NAMES         NAM
 where WRK.EFFDT            = ( select max(WRK1.EFFDT)
                                  from PS_TL_WRKGRP_TBL WRK1
                                 where WRK.WORKGROUP = WRK1.WORKGROUP
                                   and WRK1.EFFDT   <= SYSDATE )
   and WRK.WORKGROUP        = EDT.WORKGROUP
   and EDT.EFFDT            = ( select max(EDT1.EFFDT)
                                  from PS_TL_EMPL_DATA EDT1
                                 where EDT.EMPLID   = EDT1.EMPLID
                                   and EDT.EMPL_RCD = EDT1.EMPL_RCD
                                   and EDT1.EFFDT  <= SYSDATE )
  and EDT.TIME_RPTG_STATUS   = 'A'
  and EDT.EMPLID             = MAS.EMPLID
  and EDT.EMPL_RCD           = MAS.EMPL_RCD
  and MAS.ST_INSTANCE        = ( select PRCS.RUNCNTLID as ST_INSTANCE
                                   from PSPRCSRQST PRCS
                                  where PRCS.PRCSINSTANCE = :PRCSINSTANCE )
  and EDT.EMPLID             = JOB.EMPLID
  and EDT.EMPL_RCD           = JOB.EMPL_RCD
  and JOB.EFFDT              = (select max(JOB1.EFFDT)
                                  from PS_JOB JOB1
                                 where JOB.EMPLID   = JOB1.EMPLID
                                   and JOB.EMPL_RCD = JOB1.EMPL_RCD
                                   and JOB1.EFFDT  <= SYSDATE)
  and JOB.EFFSEQ             = (select max(JOB2.EFFSEQ)
                                  from PS_JOB JOB2
                                 where JOB.EMPLID   = JOB2.EMPLID
                                   and JOB.EMPL_RCD = JOB2.EMPL_RCD
                                   and JOB.EFFDT = JOB2.EFFDT)
  and JOB.EMPLID             = GDT.EMPLID
  and JOB.EMPL_RCD           = GDT.EMPL_RCD
  and GDT.GROUP_ID not like '%ALL'
  and GDT.GROUP_ID not like '%TST'
  and GDT.GROUP_ID not like 'MFAL%'
  and GDT.GROUP_ID not like 'MFTA%'
  and GDT.GROUP_ID       <> 'MFSEE'
  and GDT.EMPLID             = NAM.EMPLID
  and NAM.NAME_TYPE          = 'PRI'
  and NAM.EFFDT              = ( select max(NAM1.EFFDT)
                                   from PS_NAMES NAM1
                                  where NAM.EMPLID    = NAM1.EMPLID
                                    and NAM.NAME_TYPE = NAM1.NAME_TYPE
                                    and NAM.EFFDT    <= SYSDATE )
order by GDT.GROUP_ID,
         JOB.DEPTID,
         EDT.EMPLID

Select Time Reporting Method

Now get the dates entered and how the time was reported.

-- -----------------------------------------------------------------------------
-- 2. Select Time Reporting Method Page
-- -----------------------------------------------------------------------------
-- Get  the  dates  entered, how time was reported and whether Offdays should be
-- included in the schedule.
--
-- If just one date was selected then the End Date will be blank and start date
-- will  correspond to 'Date:'. If not then start date will be the 'From:' date
-- entered and end date will be the 'To:'.
-- ----------------------------------------------------------------------------
select SESS.START_DATE,
       SESS.END_DATE,
       XLAT.XLATLONGNAME as HOW_TO_REPORT_TIME,
       SESS.APPLY_OFFDAY_IND
  from PS_TL_MASS_SESSION SESS,
       XLATTABLE_VW       XLAT
 where SESS.ST_INSTANCE      = ( select PRCS.RUNCNTLID as ST_INSTANCE
                                   from PSPRCSRQST PRCS
                                  where PRCS.PRCSINSTANCE = :PRCSINSTANCE )
   and SESS.MASS_USER_ACTION = XLAT.FIELDVALUE
   and XLAT.FIELDNAME        = 'MASS_USER_ACTION'
   and XLAT.EFFDT            = (select max(XLAT1.EFFDT)
                                  from XLATTABLE_VW XLAT1
                                  where XLAT1.FIELDNAME  = XLAT.FIELDNAME
                                    and XLAT1.FIELDVALUE = XLAT.FIELDVALUE
                                    and XLAT1.EFFDT <= SYSDATE)

And finally get the TRC details and Use Profile entered.

-- -----------------------------------------------------------------------------
-- 3. Report Time Page
-- -----------------------------------------------------------------------------

-- -----------------
-- 3.1 Get Taskgroup
-- -----------------
select MASS.TASKGROUP
  from PS_TL_MASS_SESSION MASS
where MASS.ST_INSTANCE = ( select PRCS.RUNCNTLID as ST_INSTANCE
                             from PSPRCSRQST PRCS
                            where PRCS.PRCSINSTANCE = :PRCSINSTANCE )

-- --------------------------------------------------------------
-- 3.2 Get Time Reporting Code details entered and 'Use Profile'.
-- --------------------------------------------------------------
select MT.TRC,
       MT.TL_QUANTITY,
       MT.COUNTRY,
       MT.CURRENCY_CD,
       MT.STATE,
       MT.LOCALITY,
       MT.COMP_RATECD,
       MT.BILLABLE_IND,
       MT.OVERRIDE_RATE,
       XLAT.XLATLONGNAME as PROFILE
  from PS_TL_MASS_TRANS  MT,
       XLATTABLE_VW      XLAT
 where MT.ST_INSTANCE       = ( select PRCS.RUNCNTLID as ST_INSTANCE
                                  from PSPRCSRQST PRCS
                                 where PRCS.PRCSINSTANCE = :PRCSINSTANCE )
   and MT.USE_PROFILE_IND   = XLAT.FIELDVALUE
   and XLAT.FIELDNAME       = 'USE_PROFILE_IND'
   and XLAT.EFFDT           = (select max(XLAT1.EFFDT)
                                 from XLATTABLE_VW XLAT1
                                where XLAT1.FIELDNAME  = XLAT.FIELDNAME
                                  and XLAT1.FIELDVALUE = XLAT.FIELDVALUE
                                  and XLAT1.EFFDT <= SYSDATE) 

Now if only PeopleSoft had just used a good old fashioned run control table for this process...

Listing All PeopleSoft System tables

All PeopleSoft system tables can be dumped with: -

select RECNAME,
       RECDESCR
  from PSRECDEFN
 where RECNAME like 'PS%'
   and RECDESCR <> ' '

Peering into the depths of the PeopleSoft Process Scheduler Queue

On occaision you need to be able to see what's been running in the PeopleSoft Batch queue at a given moment in time. At such times the following SQL comes in quite handy.

-- --------------------------
-- Dump Process Monitor Table
-- --------------------------
select PRCS.OPRID,
       PRCS.PRCSJOBNAME,
       PRCS.PRCSNAME,
       PRCS.RUNCNTLID,
       XLAT.XLATLONGNAME,
       to_char(PRCS.BEGINDTTM,'yyyy-mm-dd-hh24.mi.ss') as Began,
       to_char(PRCS.ENDDTTM,'yyyy-mm-dd-hh24.mi.ss')   as Ended
       (((TO_NUMBER(TO_CHAR(PRCS.ENDDTTM, 'J')) - TO_NUMBER(TO_CHAR(PRCS.BEGINDTTM, 'J'))) * 86400)+(TO_NUMBER(TO_CHAR(PRCS.ENDDTTM, 'SSSSS')) - TO_NUMBER(TO_CHAR(PRCS.BEGINDTTM, 'SSSSS')))) as Dur_Sec
  from PSPRCSRQST   PRCS,
       XLATTABLE_VW XLAT
 where PRCS.BEGINDTTM   > to_date('2009-03-02-01.00.00','YYYY-MM-DD-HH24.MI.SS')
   and PRCS.RUNSTATUS    = XLAT.FIELDVALUE
   and XLAT.FIELDNAME    = 'RUNSTATUS'
   and XLAT.EFFDT = (select max(XLAT1.EFFDT)
                       from XLATTABLE_VW XLAT1                    
                      where XLAT1.FIELDNAME  = XLAT.FIELDNAME
                        and XLAT1.FIELDVALUE = XLAT.FIELDVALUE
                        and XLAT1.EFFDT <= SYSDATE)
   and XLAT.EFF_STATUS = 'A'
 order by PRCS.BEGINDTTM Desc

Be careful when you stare into the void however, the void might stare back at you.

Listing a tables Indexes

select index_name
from dba_indexes
where table_name=<table_name>

SQL to find Rules and associated Application Engine Sections

Get the AE Section name from the Rule ID.

select AE_SECTION
from PS_TL_RULE_DEFN
where TL_RULE_ID = 'XXX'

Get the Rule ID from the AE Section name.

select TL_RULE_ID
from PS_TL_RULE_DEFN
where AE_SECTION = 'XXX'

Current XLAT Values for a Field

The following field selects current active values for a field from the XLAT table.

-- ---------------
-- Dump XLAT Table
-- ---------------
select XLAT.FIELDVALUE,
       XLAT.XLATLONGNAME,
       XLAT.XLATSHORTNAME
  from XLATTABLE_VW XLAT
 where XLAT.FIELDNAME    = 'EMPL_STATUS'
   and XLAT.EFFDT = (select max(XLAT1.EFFDT)
                       from XLATTABLE_VW XLAT1                    
                      where XLAT1.FIELDNAME  = XLAT.FIELDNAME
                        and XLAT1.FIELDVALUE = XLAT.FIELDVALUE
                        and XLAT1.EFFDT <= SYSDATE)
   and XLAT.EFF_STATUS = 'A'

Finding The Current Database Name

Often you want to find out what the database is that you are logged in to. This can be useful when spooling the output of a SQL script to a log file or checking you are in the right environment before executing a script.

Oracle
In Oracle you can get the current database name by querying the table v_$database

i.e.

select name
 from sys.v_$database

SQL Server
The equivalent way of finding it in SQL Server is to use the function DB_Name.

e.g.

select DB_Name()

Finding a table name in SQL Server

To find a table name in SQL Server query the table sysobjects.

For example to list all PeopleSoft tables on the system, you could use: -

select *
from sysobjects
where name like 'PS_%'

PeopleSoft Payroll Interface SQL

/****************************************/
/*                                      */
/* Dump the Payroll Interface Run Table */
/*                                      */
/****************************************/
select *
from PS_PI_RUN_TBL
order by PI_RUN_NUM desc

/*************************/
/*                       */
/* Dump PI Partic table. */
/*                       */
/*************************/
select *
from PS_PI_PARTIC

/*************************************/
/*                                   */
/* Dump PI Partic table, latest run. */
/*                                   */
/*************************************/
select PIP.*
from PS_PI_RUN_TBL PUN,
     PS_PI_PARTIC  PIP
where PUN.PI_RUN_NUM = (select max(PUN1.PI_RUN_NUM)
                         from PS_PI_RUN_TBL PUN1)
 and PUN.PI_RUN_NUM = PIP.PI_RUN_NUM

/*******************************/
/*                             */
/* Dump PI PARTIC detail table */
/*                             */
/*******************************/
select *
from PS_PI_PARTIC_EXPT

/********************************************/
/*                                          */
/* Dump PI Partic detail table, latest run. */
/*                                          */
/********************************************/
select PIE.*
from PS_PI_RUN_TBL PUN,
     PS_PI_PARTIC_EXPT PIE
where PUN.PI_RUN_NUM = (select max(PUN1.PI_RUN_NUM)
                         from PS_PI_RUN_TBL PUN1)
 and PUN.PI_RUN_NUM = PIE.PI_RUN_NUM

/***************************************************/
/*                                                 */
/* Dump Pay Calendars associated with a particular */
/* Payroll Interface run.                          */
/*                                                 */
/***************************************************/
select RUN.PI_RUN_NUM,
     CAL.*
from PS_PI_RUN_TBL RUN,
     PS_PAY_CALENDAR CAL
where RUN.PI_RUN_NUM = 999
 and RUN.RUN_ID     = CAL.RUN_ID

/******************************/
/*                            */
/* Dump the PI Empl ID Table. */
/*                            */
/******************************/
select *
from PS_PI_EMPLID_TBL

/********************************************/
/*                                          */
/* List fields in a Payroll Interface File. */
/*                                          */
/********************************************/
select DEF.PI_FIELD_SEQ,
     DEF.PI_FIELD_ID,
     FLD.DESCR,
     FLD.PI_FIELD_LEN,
     FLD.PS_FIELD1_NM,
     DEF.PI_KEY_FIELD,
     DEF.PI_GROUP_ID
from PS_PI_DEFN_FIELD DEF,
     PS_PI_FIELD_TBL  FLD
where DEF.PI_FILE_ID = 'XXX'
 and DEF.EFFDT        = (select max(DEF1.EFFDT)
                           from PS_PI_DEFN_FIELD DEF1
                          where DEF.PI_FILE_ID = DEF1.PI_FILE_ID
                            and DEF1.EFFDT    <= SYSDATE)    and DEF.PI_SYSTEM_ID = FLD.PI_SYSTEM_ID    and DEF.PI_FIELD_ID  = FLD.PI_FIELD_ID    and FLD.EFFDT        = (select max(FLD1.EFFDT)                              from PS_PI_FIELD_TBL FLD1                             where FLD.PI_SYSTEM_ID = FLD1.PI_SYSTEM_ID                               and FLD.PI_FIELD_ID  = FLD1.PI_FIELD_ID                               and FLD1.EFFDT      <= SYSDATE)

/***************************************************/ 
/*                                                 */
/* List interface files that contain a given Field */
/*                                                 */
/***************************************************/
select DEF.PI_FILE_ID,
        DEF.PI_FIELD_SEQ,
        DEF.PI_FIELD_ID,
        FLD.DESCR,
        FLD.PI_FIELD_LEN,
        FLD.PS_FIELD1_NM, 
        DEF.PI_KEY_FIELD,
        DEF.PI_GROUP_ID
   from PS_PI_DEFN_FIELD DEF,
        PS_PI_FIELD_TBL  FLD
  where DEF.PI_SYSTEM_ID = 'CHR'
    and DEF.PI_FIELD_ID IN ('E001')
    and DEF.EFFDT        = (select max(DEF1.EFFDT)
                              from PS_PI_DEFN_FIELD DEF1
                             where DEF.PI_FILE_ID = DEF1.PI_FILE_ID
                               and DEF1.EFFDT    <= SYSDATE)
    and DEF.PI_SYSTEM_ID = FLD.PI_SYSTEM_ID
    and DEF.PI_FIELD_ID  = FLD.PI_FIELD_ID
    and FLD.EFFDT        = (select max(FLD1.EFFDT)
                              from PS_PI_FIELD_TBL FLD1
                             where FLD.PI_SYSTEM_ID = FLD1.PI_SYSTEM_ID
                               and FLD.PI_FIELD_ID  = FLD1.PI_FIELD_ID
                               and FLD1.EFFDT      <= SYSDATE) 

Roles, Permission Lists & Components

The following SQL identifies which roles/permission lists a user has to access a given component.

select RUSR.OPRID,
       RUSR.ROLENAME,
       RCL.CLASSID,
       MNU.MENUNAME,
       CMP.PNLGRPNAME
  from PSROLEUSER_VW RUSR,
       PSROLECLASS RCL,
       PS_PSACLMENU_VW2 MNU,
       PS_ACLCOMPONENT_V2 CMP
 where RUSR.ROLENAME  = RCL.ROLENAME
   and RUSR.OPRID     = 'oprid'
   and RCL.CLASSID    = MNU.CLASSID
   and MNU.MENUNAME   = CMP.MENUNAME
   and CMP.PNLGRPNAME = 'component'
 order by MNU.CLASSID

SQL to find out which roles have a particular permission list.

select distinct RCL.ROLENAME,
                RCL.CLASSID
           from PSROLEUSER_VW  RUSR,
                PSROLECLASS  RCL
          where RCL.CLASSID = 'TYALLPLS'
       order by RCL.ROLENAME

SQL to list users assigned to a particualr role.

select *
  from PSROLEUSER_VW RUSR
 where RUSR.ROLENAME = 'Rolename'

Current Job Row

I know I can enter the job effective dating code in my sleep, but the following, with a few amendments as needed, should save some time in the future.

select JOB.EMPLID, JOB.EMPL_RCD, JOB.EFFDT, JOB.EFFSEQ, JOB.ACTION, JOB.ACTION_REASON, JOB.EMPL_STATUS, JOB.BUSINESS_UNIT, JOB.COMPANY, JOB.PAYGROUP from PS_JOB JOB where JOB.EFFDT = (select max(JOB1.EFFDT) from PS_JOB JOB1 where JOB.EMPLID = JOB1.EMPLID and JOB.EMPL_RCD = JOB1.EMPL_RCD and JOB1.EFFDT <= SYSDATE) and JOB.EFFSEQ = (select max(JOB2.EFFSEQ) from PS_JOB JOB2 where JOB.EMPLID = JOB2.EMPLID and JOB.EMPL_RCD = JOB2.EMPL_RCD and JOB.EFFDT = JOB2.EFFDT)

Note: The above example is for Oracle, if using SQL Server change SYSDATE to GetDate().

Query Security Record SQL

The following SQL identifes which PeopleSoft records use a particular Query Security Record.

select RECNAME
from PSRECDEFN
where QRYSECRECNAME = 'query security record'

SQL to find a Field in PeopleSoft

This script lists all the tables that contain a given field in PeopleSoft. Just replace fieldname with the name of the field you want to find.

SELECT A.RECNAME,
B.SQLTABLENAME
FROM PSRECFIELD A,
PSRECDEFN B
WHERE A.FIELDNAME = 'fieldname'
AND A.RECNAME = B.RECNAME
AND B.RECTYPE = 0
AND B.RECNAME NOT LIKE 'AUDIT%'
AND B.RECNAME NOT LIKE '%TEO%'
AND B.RECNAME NOT LIKE '%AET'
AND B.RECNAME NOT LIKE '%TAO%'
AND B.RECNAME NOT LIKE '%PERS%'
AND B.RECNAME <> 'PSOPRALIAS'

Set Current Schema

I always forget the syntax for setting the current schema in Oracle. Then again that's why the great Flying Spaghetti Monster created blogs.

alter session set current_schema = sysadm