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