PeopleSoft Hints

The PeopleSoft password hints table, PSUSERATTR, is not encrypted, so avoid using any hint that you use elsewhere, such as What is your Mother's Maiden name?

If you are forced to use a standard question give a bogus answer not one which will compromise your personal security e.g. What is your favourite Sport? response WIBBLE.

Getting Oracle Table Information

The following SQL gets useful information about tables in an Oracle Database, such as the tablespace used.


select TABLE_NAME,
OWNER,
INITIAL_EXTENT,
PCT_USED,
NEXT_EXTENT,
MIN_EXTENTS,
MAX_EXTENTS,
PCT_INCREASE,
TABLESPACE_NAME
from dba_tables
where TABLE_NAME in ( 'PS_TY_TL_RAP_H_HST',
'PS_TY_TL_RAP_T_HST',
'PS_TY_TL_RPT_T_HST',
'PS_TY_TL_PAY_T_HST',
'PS_TY_SCH_AD_D_HST',
'PS_TY_SCH_MN_S_HST',
'PS_TY_TL_TR_CO_HST' )

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 <> ' '

Dumping an Application Engine's Temp Tables List

Recently I had an issue to look at that required me to list the temporary tables used by the TL Admin application engine. To do this I used the following SQL: -

select *
  from PSAEAPPLTEMPTBL
where AE_APPLID = 'TL_TIMEADMIN'

Blankety blank, blankety blank...

To make a cell blank when zero in Excel you could do so via conditional formatting, setting the font colour to be the same as the background colour when it equals zero.

This technique is a bit limited however, and hits problems if you need to use other conditional formatting or when the background colour varies from cell to cell.

Another more flexible method is to use the following formatting string for your cell.
Just go to Format > Cells > Custom and plonk it in to the Type box.

0;-0;;@

The above format doesn't show a thousands delimiter. To get that in use: -

#,##0;-0;;@

Why Microsoft made this expression so bloody complex I'll never know. But hey, Microsoft are Microsoft, they'd make the complete works of Shakespeare out of a crisis.

Have a play with varients of this for other effects.

Enjoy!

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.

One Step Beyond

One of Time Admin's subprograms, TL_TA000800, calls TL_TA_RULES to apply rules in the order specified on the rule program for each batch. The sections in TL_TA_RULES correspond to each of the active time and labor rules.

When a rule is built the application engine definition tables underlying TL_TA_RULES are updated to reflect any changes made to the rule.

Unfortunately, for a particular rule, the section name and step of the app engine don't correspond to the rule name and step number of the rule itself. Which can make it a little tricky when debugging an abend. The following SQL solves this problem by giving you the rule ID and step for the app. engine section and step that the abend log will give you.

select *
  from ( select TL_RULE_ID,
                TL_RULE_STEP,
                rownum as AE_STEP,
                SQL_ID,
                DESCR100   
           from PS_TL_RULE_STEPS
          where TL_RULE_ID = (select TL_RULE_ID
                                from PS_TL_RULE_DEFN
                               where AE_SECTION = :AE_SECTION)
       )
 where AE_STEP = :STEP