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'