Coded Languages

DoSaveNow() saves and SetReEdit(True) nets the rebound

noreply@blogger.com (George Ternent) — Tue, 04 Feb 2014 10:11:00 +0000

Recently I had a problem with DoSaveNow() not doing what it said on the tin and actually saving a component. Initially this was worked around by using CommitWork() instead, but ultimately this stopped working as well.

The solution was to use SetReEdit prior to doing the save. e.g.

SetReEdit(True);
DoSaveNow();

Set ReEdit switches re-edit mode on and off. When it is on, definitional edits (such as translate table and prompt table edits), as well as FieldEdit PeopleCode, are run on each editable field in the component when the component is saved.Whey the DoSaveNow failed to save without it and why it succeeded with it, I haven't got a Scooby's. But as it sorted out my save issue and stopped me swearing at the screen, I'm no complaining!

It only works.

Recurrences

noreply@blogger.com (George Ternent) — Wed, 23 Oct 2013 14:00:00 +0000

To identify the users and processes associate with a particular recurrence in PeopleSoft, you can use the following Query.

select distinct MON.RECURNAME,
                MON.OPRID,
                DFN.OPRDEFNDESC,
                MON.PRCSNAME
  from PS_TY_PRCSMON_HIST MON,
       PSOPRDEFN          DFN
 where RECURNAME like '%_0410'
   and MON.OPRID = DFN.OPRID
 order by case
            when MON.RECURNAME = 'TY_WEEKLY_MON_0410' then 1
            when MON.RECURNAME = 'TY_WEEKLY_TUE_0410' then 2
            when MON.RECURNAME = 'TY_WEEKLY_WED_0410' then 3
            when MON.RECURNAME = 'TY_WEEKLY_THU_0410' then 4
            when MON.RECURNAME = 'TY_WEEKLY_FRI_0410' then 5
            when MON.RECURNAME = 'TY_WEEKLY_SAT_0410' then 6
            when MON.RECURNAME = 'TY_WEEKLY_SUN_0410' then 7
          end,
          MON.RECURNAME,
          MON.OPRID,
          MON.PRCSNAME

Changing the 'like' as required.

A Simple File Load

noreply@blogger.com (George Ternent) — Wed, 19 Jun 2013 15:00:00 +0000

What follows is a simple piece of PeopleCode to upload a flat file into a table in PeopleSoft.

Local File &FILE;
Local Record &REC;
Local Rowset &FRS;

&FILE = GetFile(TY_TL_GRPLD_AET.FILENAME, "R", %FilePath_Absolute);
&REC = CreateRecord(Record.TY_TL_GRPLD);
&SQL = CreateSQL("%Insert(:1)");

If Not &FILE.IsOpen Then
   Error (TY_TL_GRPLD_AET.FILENAME | " failed file open");
Else
   If Not &FILE.SetFileLayout(FileLayout.TY_TL_GRPLD) Then
      Error ("TY_TL_GRPLD: failed SetFilelayout");
   Else
      &FRS = &FILE.ReadRowset();
      If &FILE.IsError Then
         Error ("Error reading rowset");
      End-If;
      While &FRS <> Null
         &FRS.GetRow(1).TY_TL_GRPLD.CopyFieldsTo(&REC);
         &SQL.execute(&REC);
         &FRS = &FILE.ReadRowset();
         If &FILE.IsError Then
            Error ("Error reading rowset");
         End-If;
      End-While;
   End-If;
   &FILE.Close();
End-If;

One thing to make sure you do is to set the Qualifier to optional in the File Layout Definition properties. If you fail to do so and don't encapsulate your variables with your definition qualifier, e.g. double quotes, then the process will fail with a garbage error message along the lines of "cannot insert NULL into". You have been warned.

ISNUMBER

noreply@blogger.com (George Ternent) — Wed, 13 Feb 2013 12:11:00 +0000

If you have a text field that you want to convert to a number that has a non numeric value in it, then the conversion will fail with an invalid number error. What you need is a function that determines if the string is a number, so that you can decide whether to convert or not. Sadly Oracle SQL doesn't have one.

All is not lost however, with a cunning use of translate you can suss out if a string is a number or not.

Let's say you wanted to convert JOBCODE in JOB into a number when it is numeric, and zero when it is not. The following SQL will do this for you quite nicely.

select distinct JOBCODE,
                ( case
                    when translate(JOBCODE, '_0123456789', '_') is null then to_number(JOBCODE)
                    else                                                     0
                 end )
  from PS_JOB

The Spirit Of The Age

noreply@blogger.com (George Ternent) — Tue, 05 Feb 2013 12:06:00 +0000

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

T&L Prior Period Access (PPA)

noreply@blogger.com (George Ternent) — Wed, 26 Dec 2012 17:26:00 +0000

I recently had an issue where group leaders couldn't modify historic sessions, which they couldn't originally edit due to Christmas shutdown. Bah humbug!

In PeopleSoft the ability to amend old sessions is governed by the members row security class and the T&L Operator Security table TL_OPR_SECURITY.

The following SQL can be used to detemine the row security classes that operators, without unlimited access, have to give them Prior Period Access to specified sessions.

select distinct OPD.ROWSECCLASS,
                OPS.PPA_ACCESS,
                OPS.PPA_ALLOW
  from PSROLEUSER_VW      RUSR,
       PSOPRDEFN          OPD,
       PS_TL_OPR_SECURITY OPS
 where RUSR.OPRID     in ( select distinct OPRID
                             from PS_TL_RAPID_HEADER
                            where DESCR like 'MF%-12-2012%')
   and RUSR.OPRID       = OPD.OPRID
   and OPD.ROWSECCLASS  = OPS.ROWSECCLASS
   and OPS.PPA_ALLOW   != 0

Just change the DESCR like statement as per the sessions you want to check.

If there aren't too many permission lists you could manually change them at: -

Home > Setup HRMS > Time and Labor Security > TL Permission List Security

Modifying the Days Grace Allowed as required.

Alternatively you could do so by the back end with SQL along the lines of: -

update PS_TL_OPR_SECURITY OPR
   set PPA_ALLOW = 20
 where ROWSECCLASS in ( 'DPG542C1',
                        'DPG723A1'
                        ...
                        'DPE13001',
                        'DPA130Z1' )

With the row security permission lists being the ones you identified with the first SQL.
In this example I've set the allowed days to be 20, if you want to give them unlimited access set it to zero.

Before updating remember to dump out the original values first so you can set them back after the users have finished doing what they need to do with the sessions.

Rounding

noreply@blogger.com (George Ternent) — Thu, 29 Nov 2012 15:37:00 +0000

If you want to round up or down in Oracle use one of the following: -

select 3.14159,
       ROUND(3.14159,2)       as ROUNDED,
       CEIL(3.14159*100)/100  as ROUNDED_UP,
       FLOOR(3.14159*100)/100 as ROUNDED_DOWN
 from DUAL

Replacing 2 in the first example with the number of decimal places you want to round to, and 100 in the other examples with 10 to the power of the number of decimal places you want to round up or down to.

Reccomended Application Engine Debug Settings

noreply@blogger.com (George Ternent) — Fri, 10 Aug 2012 13:51:00 +0000

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

noreply@blogger.com (George Ternent) — Wed, 27 Jun 2012 08:14:00 +0000

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

noreply@blogger.com (George Ternent) — Wed, 27 Jun 2012 07:45:00 +0000

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

noreply@blogger.com (George Ternent) — Wed, 30 Mar 2011 08:58:00 +0000

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

noreply@blogger.com (George Ternent) — Tue, 07 Sep 2010 11:40:00 +0000

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

noreply@blogger.com (George Ternent) — Mon, 19 Jul 2010 10:52:00 +0000

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.

noreply@blogger.com (George Ternent) — Sun, 27 Jun 2010 01:40:00 +0000

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!

Get Table Details

noreply@blogger.com (George Ternent) — Wed, 09 Jun 2010 08:29:00 +0000

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

noreply@blogger.com (George Ternent) — Tue, 08 Jun 2010 06:29:00 +0000


select TL_RULE_ID
 from PS_TL_RULE_STEPS
where SQL_ID = 'XXX'

Getting Details Of A Table's Columns

noreply@blogger.com (George Ternent) — Wed, 05 May 2010 11:06:00 +0000

The following Oracle SQL gets the columns in a table.


-- -------------------
-- Get Table's Columns
-- -------------------
select COLUMN_NAME,
       DATA_TYPE
  from ALL_TAB_COLUMNS
 where TABLE_NAME = 'PS_TY_TL_SDF_EVT'

Changing PeopleSoft Passwords by the Back End

noreply@blogger.com (George Ternent) — Tue, 13 Apr 2010 14:33:00 +0000

If the same password key is used across multiple PeopleSoft environments you can set the password in one account equal to that in another by creating a database link between the two and using the following SQL: -


update PSOPRDEFN@HR89XXX OPXXX
   set OPXXX.OPERPSWD = ( select OP.OPERPSWD
                            from PSOPRDEFN OP
                           where OPXXX.OPRID = OP.OPRID
                        ),
       OPXXX.ACCTLOCK = 0,
       OPXXX.LASTPSWDCHANGE = SYSDATE
 where OPXXX.OPRID = 'USERNAME'

Along with changing the password you also need to set the last password change date, LASTPSWDCHANGE equal to the current date. Otherwise you could change your password, but have it expire on you immediately.

It also makes sense to reset the account locked flag, ACCTLOCK, in case the account has been locked.

Last User Exit To Brooklyn

noreply@blogger.com (George Ternent) — Tue, 26 Jan 2010 15:27:00 +0000

T&L rules can include PeopleCode steps by making them User Exit rules. To create a user exit rule, do the following.

Open the TL_TA_RULES application engine and create a new section for your rule. For this section you must check the Access Pulblic check box.
Add your rule steps to this section.
Navigate to Home > Setup HRMS > Product Realted > Time and Labor > Validation Criteria > AE Section Definition and define your newly created section. The program name is TL_TA_RULES and theprocess type should be Rule (User Exit).
Navigate to Home > Setup HRMS >System Administration > Utilities > Build Time And Labor Rules > Rules and create a rule definition, checking the User Exit box and entering the name of the AE section you created earlier.
Add the new rule to any desired rule programs.
In the DMS script you use to migrate your rule you will need to include the table PS_TL_AE_SECTION which contains user exit section details. The migration project will need to contain your AE Section and any associated steps.

PeopleSoft Hints

noreply@blogger.com (George Ternent) — Mon, 05 Oct 2009 14:15:00 +0000

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

noreply@blogger.com (George Ternent) — Mon, 24 Aug 2009 06:55:00 +0000

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

noreply@blogger.com (George Ternent) — Tue, 26 May 2009 14:25:00 +0000

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

noreply@blogger.com (George Ternent) — Mon, 25 May 2009 10:27:00 +0000

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

noreply@blogger.com (George Ternent) — Mon, 25 May 2009 10:19:00 +0000

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

noreply@blogger.com (George Ternent) — Mon, 02 Mar 2009 15:28:00 +0000

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!