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