Reccomended Application Engine Debug Settings

Open the PROCESS and go to the Override Options tab. Set Parameter List to Append and enter the following for it.

-TRACE 3 -TOOLSTRACE 3 -TOOLSTRACEPC 2060

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.

Anatomy Of A Query

The following tables are used to construct a Query in PeopleSoft.

PSQRYACCLSTAET Query Access List State Record
PSQRYACCLSTRECS Query Access Record List
PSQRYBIND Query Prompt
PSQRYBINDLANG Query Prompt Alternate Lang.
PSQRYCRITERIA Query Criteria
PSQRYDEFN Query Definition
PSQRYDEFNLANG Query Definition Alt. Language
PSQRYDEL Query Definition
PSQRYEXECLOG Query RunTime Log
PSQRYEXPR Query Expression
PSQRYFAVORITES Query Manager Favorites Table
PSQRYFIELD Query Field
PSQRYFIELDLANG Query Field Alternate Language
PSQRYFLAGS Query Global Flags Table
PSQRYRECORD Query Record
PSQRYSELECT Query Select
PSQRYSTATS Query RunTime Statistics

Finding a component in the Portal

The following SQL allows you to avoid the usual dog and pony chase when trying to find where a component is located in the PeopleSoft Portal.

SELECT --P.PORTAL_NAME,
       --P.PORTAL_REFTYPE,
       --P.PORTAL_OBJNAME,
       A.*, 
       P.PORTAL_LABEL,
       R.PATH,
       P.PORTAL_URLTEXT
FROM   
(
SELECT DISTINCT
       U.ROLEUSER  ,
       C.MENUNAME  ,
       C.PNLGRPNAME,
       C.MARKET
FROM   PSROLEUSER  U
JOIN   PSROLECLASS A
ON     A.ROLENAME = U.ROLENAME
JOIN   PSAUTHITEM  B
ON     B.CLASSID = A.CLASSID
JOIN   PSMENUITEM  C
ON     C.MENUNAME = B.MENUNAME
AND    C.BARNAME  = B.BARNAME
AND    C.ITEMNAME = B.BARITEMNAME 
) A
JOIN   PSPRSMDEFN P
ON     P.PORTAL_URI_SEG1 = A.MENUNAME
AND    P.PORTAL_URI_SEG2 = A.PNLGRPNAME
AND    P.PORTAL_URI_SEG3 = A.MARKET
JOIN   
(
SELECT CONNECT_BY_ROOT(PORTAL_NAME)    ROOT_NAME,
       CONNECT_BY_ROOT(PORTAL_REFTYPE) ROOT_REFTYPE,
       CONNECT_BY_ROOT(PORTAL_OBJNAME) ROOT_OBJNAME,
       PORTAL_OBJNAME,
       RTRIM(REVERSE(SYS_CONNECT_BY_PATH(REVERSE(PORTAL_LABEL),' > ')),' > ') PATH
FROM   PSPRSMDEFN
CONNECT 
BY NOCYCLE PRIOR PORTAL_NAME        = PORTAL_NAME
       AND PRIOR PORTAL_PRNTOBJNAME = PORTAL_OBJNAME
)      R
ON     R.ROOT_NAME    = P.PORTAL_NAME
AND    R.ROOT_REFTYPE = P.PORTAL_REFTYPE
AND    R.ROOT_OBJNAME = P.PORTAL_OBJNAME
WHERE  A.ROLEUSER = 'username'
AND    P.PORTAL_NAME     = 'EMPLOYEE'
AND    P.PORTAL_REFTYPE  = 'C'
AND    R.PORTAL_OBJNAME  = 'PORTAL_ROOT_OBJECT'
AND    A.PNLGRPNAME      = 'component'

Thanks to Rob for this one.

Oracle Date Display Format

To change the format that a date is displayed in Oracle use the set NLS_DATE_FORMAT command. For example, if you wanted to show hours minutes and seconds with a date time stamp you could use: -

alter session set NLS_DATE_FORMAT = 'DD-MON-YYYY HH:MI'

before executing your query.

Gentleman Of Verona were amazing tonight. One of the best Belgian bands I've heard for years. Come to think of it, one of the best bands, period, I've heard in years.

High octane music delivered with cruise missile precision, by a band who were tight as a kangeroo's khyber, with a female vocalist who kicked ass bigtime.

Reminiscent of L7, Hole early Yeah, Yeah, Yeahs and Susie And The Banshees this is a band to watch out for in the future. Their new album Brutally Honest is out now on CD and, to be brutally honest, it's bloody brilliant. Their first album is available for download from iTunes and is also of a high quality. If you like good music, buy them!