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

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

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

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

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

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)

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

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

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!

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 Details Of A Table's Columns

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

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

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

1. 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.
2. Add your rule steps to this section.
3. 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).
4. 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.
5. Add the new rule to any desired rule programs.
6. 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

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.