Array Oddities Creating the empty array

I've found that the arrays in peoplecode have a few oddities that you really need to pay attention to when you creating them.

Defining Array

Specific type

Local Array of String &arrayString;

Any type

Local Array of Any &arrayAny;

Instantiate your Array

If you use the CreateArray(); method you must pass in at least one value of the array type you defined UNLESS you are instantiating and Any array you can call it without a value to create an empty array.

&arrayString = CreateArray("Hello World");

&arrayAny = CreateArray();

Empty Array of a specific Type

If you want to create an empty array of a specific data type you need to use a different method CreateArrayRept(); This method has two parameters the type of object and the number of times you want that object repeated into the array.

Local Record &record;
Local Number #
Local Array of Record &records;
Local Array of Number &numbers;
&arrayString = CreateArrayRept("", 0);
&records = CreateArrayRept(&record, 0);
&numbers = CreateArrayRept(&number, 0);

CSV Headers for File Layout

I couldn't find a nice way to build a CSV header using the file layout.  You seem to always need a record to send to the file layout in order to write a different segment.  So I found this solution that uses the record field names to write the header.

Function getHeader(&rec) Returns string
   Local string &hdr;
   For &i = 1 To &rec.FieldCount
      SQLExec("select label_id from psrecfield where recname = :1 and fieldname = :2", &rec.name, &rec.GetField(&i).name, &labelId);
      &label = &rec.GetField(&i).GetLongLabel(&labelId);
      If None(&label) Then
         &label = &rec.GetField(&i).GetLongLabel(&rec.GetField(&i).name);
         If None(&label) Then
            &label = &rec.GetField(&i).name;
         End-If;
      End-If;
      &header = &header | """" | &label | """";
      If &i < &rec.FieldCount Then
         &hdr = &hdr | ",";
      End-If;
   End-For;
   Return &hdr;
End-Function;
/* main */
Local Record &recRpt = CreateRecord(Record.MCM_ADSDD_RPT);
Local File &ExportFile;
&ExportFile = GetFile("c:\temp\myfile.csv", "W", %FilePath_Absolute);
If &ExportFile.IsOpen Then
   &ExportFile.WriteLine(getHeader(&recRpt));
   While ...
End-If;

This will first check if there is a specific label set on the record version of the field. If that value is blank it will attempt to get the label with the same name as the field itself. If there still is no value just use the field name.

Setting a field named "NAME"

If you need to reference a field on a record called "NAME" you'll get errors because peoplesoft gets confused with the reference to name as a property and not a field. To get around this use the GetField method.

&record.NAME.Value = "Dr. Jones";

&record.GetField(Field.NAME).Value = "Dr. Jones";

Building a String

Sometimes you want to build a string with multiple variables inserted into that string.  This is easily done with the SQL functions using the :1 :2 place holders.  You can also build them for messages with %1 place holders.  I've been looking for a way to do it in plain old peoplecode.  Here is the only solution I've come across and it uses the function MsgGetText.

Solution 1

Local String &str;
Local String &prefix = "Mr."
Local String &lName = "Jones"
&str = "Hello " | &prefix | " " | &lname | ". Welcome to Peoplecode.";

Solution 2

Local String &str;
Local String &prefix = "Mr."
Local String &lName = "Jones"
&str = MsgGetText(0, 0, "Hello %1 %2. Welcome to Peoplecode.", &prefix, &lname);

Both solutions would output in the follow format:
Hello Mr. Jones. Welcome to Peoplecode.

Peoplecode and the dates returned from oracle

The built in peoplecode function DATE() claims it needs the format YYYYMMDD however it must automatically strip special characters because if you pass in the standard format Oracle is returning dates in YYYY-MM-DD to this method it works fine.

Here is my sample code that passed and returned the expected values.

Sample

Local date &testDate;
Local number &testYear, &testMonth, &testDay, &testWeekday;
   MessageBox(0, "", 0, 0, " Mess with dates %1 ", &payCalendar.PAY_END_DT.Value);
&testDate = Date(&payCalendar.PAY_END_DT.Value);
&testYear = Year(&testDate);
&testMonth = Month(&testDate);
&testDay = Day(&testDate);
&testWeekday = Weekday(&testDate);
MessageBox(0, "", 0, 0, " date = %5 / Year=%1, Month=%2, Day=%3, Weekday=%4 ", &testYear, &testMonth, &testDay, &testWeekday, &testDate);

Output was

 Mess with dates 2013-01-07  (0,0)
 date = 2013-01-07 / Year=2013, Month=1, Day=7, Weekday=2  (0,0)

Effective date checking

%EffDtCheck

The %EffDtCheck construct expands into an effective date subquery suitable for a Where clause.

Only use the %effdtchk when a table DOES NOT have effective sequence effseq in the key.

Syntax

%EffDtCheck(recordname [correlation_id1], correlation_id2, as_of_date)

Example

&q = select 'x' from ps_genl_deduction A where A.emplid = :1 and %EffDtCheck(:2 B, A, %CurrentDateIn)
SQLExec(&q, &emplid, Record.GENL_DEDUCTION, &result);

Expands to:

select 'x' from ps_genl_deduction A where A.emplid = '0001' 
   where A.EFFDT = (SELECT MAX(EFFDT) FROM PS_GENL_DEDUCTION 
      WHERE B.KEYFIELD1 = A.KEYFIELD1 AND B.KEYFIELD2 = A.KEYFIELD2 AND. . . 
      AND B.EFFDT <= %DATEIN([current date]))

   

Function Library

I can be a bit of a minimalist when writing code I also firmly believe in the DRY(don't repeat yourself) principle.  This is what makes me a firm believer in using and reusing functions.  In peoplecode there are many function libraries to do work for you but you can also create you own and here's how.

1. Create a new dynamic/derived record MY_TST_FUNCS
2. Add a field related to the function(s) you wish to included AMOUNT
3. Right click on the field and edit the Peoplecode
4. Make sure you write your code in the Field Formula of the field.
   e.g. 
   

   /******************************************************
    Function   : doubleAmount
    Purpose    : Double the amount given
    Parameters : &AMOUNT
    Returns    : Number
   ******************************************************/
   Function doubleAmount(&AMOUNT) Returns number;
      Local number &newAmount;
      &newAmount = &AMOUNT + &AMOUNT;
      Return &newAmount;
   End-Function;

5. Using the function you just need to Declare it at the top of you block of code
   e.g.  

   /* Functions */
   Declare Function doubleAmount PeopleCode MY_TST_FUNCS.AMOUNT FieldFormula;
   Local number &amt, &doubled;
   &amt = 2;
   &doubled = doubleAmount(&amt);

You can create any number of functions under any number of fields. For more examples look at the record FUNCLIB_HR.

NOTE: Values are passed by reference so if you change a value in the function it will have changed the original after you have returned from that function.

Simple Row Exists logic

Here is a nice quick way to find if a row exists in peoplecode.

Function hasGenlDedCd(&EMPLOYEE, &COMPANY, &DEDCD) Returns boolean;
   Local Record &rec = CreateRecord(Record.GENL_DED_CD);
   Local string &exists = "";
   &rec.EMPLID.Value = &EMPLOYEE;
   &rec.COMPANY.Value = &COMPANY;
   &rec.DEDCD.Value = &DEDCD;
   SQLExec("select 'x' from %Table(:1) where %keyEqual(:1)", &rec, &exists);
   Return All(&exists);
End-Function;

Null date in a Record

Today I was trying to create a new record that had a EFFDT and one other date field that needed to be NULL.  During the update oracle failed to identify null as a date and gave the error "ORA-01841: (full) year must be between -4713 and +9999, and not be 0".  The solution was to not set the date field to null but instead use Date function with the value 0.

   Local Record &rec = CreateRecord(Record.GENL_DEDUCTION);
   &rec.EMPLID.Value = &MCMADSJB.EMPLID.Value;
   &rec.COMPANY.Value = &MCMADSJB.company.value;
   &rec.DEDCD.Value = &MCMADSJB.company.value;
   If &MCMADSJB.effdt.value > &MCMADSMAP.effdt.value Then
      &rec.EFFDT.Value = Date(&MCMADSJB.effdt.value);
   Else
      &rec.EFFDT.Value = Date(&MCMADSMAP.effdt.value);
   End-If;
   &rec.DEDUCTION_END_DT.Value = Date(0);
   &result = &rec.Insert();

The Date(0) will correctly make the database field null.