Thursday, May 29, 2014

Evaluate True, for large report processing

Report Requirement

The processing required for many reports or file exports (csv) I've been creating have many data collection calls.  Sometimes multiple tables can be joined to reduce the number of unique calls but often the there are times where data is not found or the returned data needs some analysis.  If the design also calls for different reporting or actions based on the analysis it forces you to make many individual calls.  

In my example we are using one large SQL to query to get a initial set of Employee ID's.  With each employee ID I as asked to do about 10 other steps of data collection and analysis each step could lead to one of the following results:
  • Skip to the next Employee ID
  • Write the report row and skip to next Employee ID
  • Continue Processing
  • Insert Data 
My initial build was using a while loop through the employees with if conditions inside however it became apparent that my nested IF conditions were getting out of hand.

My Solution 

The solution I found to create very clean and readable code that also matched the design documentation was to use an EVALUATE TRUE statement.

As you can see the &report record is a derived record that is used to accumulate the data as we go.  I used local functions to do the mini calls if I the requirement is to skip an employee when a record is not found the function simply returns FALSE.  This will stop the processing of any more WHEN statements in the EVALUATE.  In some cases I return a whole record if the data is needed for future calls and in this case I do my check for a null record returned within the WHEN statement.

Main Loop Processing

/* Data & Records */
Local Record &admApplProg, &extAcadDataA, &extAcadDataB, &mcmFaErlyAvg;
Local Record &report;
/* Processing Variables */
Local boolean &resumeEmplid;

&admApplProg = CreateRecord(Record.ADM_APPL_PROG);
&Sql = CreateSQL(SQL.SQL1, &admApplProg);
While &Sql.Fetch(&admApplProg)
   &resumeEmplid = True;
   &report = CreateRecord(Record.MCM_AD_FFA_RPT);
   &report.EMPLID.Value = &admApplProg.EMPLID.Value;
   &report.ADM_APPL_NBR.Value = &admApplProg.ADM_APPL_NBR.Value;
   &report.ACAD_CAREER.Value = &admApplProg.ACAD_CAREER.Value;
   &report.ACAD_PROG.Value = &admApplProg.ACAD_PROG.Value;
   &report.PROG_ACTION.Value = &admApplProg.PROG_ACTION.Value;
   
   Evaluate True
   When &resumeEmplid
      /* Drop if EXT_ORG_ID not found */
      &extAcadDataA = getMaxExtAcadData(&report, &admApplProg.EMPLID.Value);
      If None(&extAcadDataA) Then
         &resumeEmplid = False;
      End-If;
   When &resumeEmplid
      /* Valid Canadian Citizen */
      &resumeEmplid = isValidCndApplicant(&REPORT, &extAcadDataA);
   When &resumeEmplid
      /* retrieve an Education Source */
      &resumeEmplid = hasEducSrc(&report);
   When &resumeEmplid
      /* Read Adm_APPL_PLAN */
      &resumeEmplid = hasAcadPlan(&report, &admApplProg);
   When &resumeEmplid
      /* Retrieve the students averages */
      &resumeEmplid = hasMcmAvg(&report, &admApplProg);
   When &resumeEmplid
      /* read Early Financial Aid value*/
      &mcmFaErlyAvg = getErlyAvg(&report, &admApplProg);
      If None(&mcmFaErlyAvg) Then
         &resumeEmplid = False;
      End-If;
   When &resumeEmplid
      /* 9. Define the FA Year*/
      &resumeEmplid = hasFaYear(&report, &admApplProg);
   When &resumeEmplid
      /* Write the report row */
      writeRecordToFile(&report);
   End-Evaluate;
      
End-While;

Functions

Here you'll see some functions that are returning records and some that return the Boolean of successful data retrieval or not.  I have not include all the functions just some of the examples.  One thing you will notice is that I'm passing the main &report record into the functions.  This is because PeopleCode uses pass by reference so you can set values in the local fuction &REPORT will be retained after your function returns to the mainline.

If you are curious about my exists check on function hasFaOffer I have previously blogged this example.

/* Identify last school attended */
Function getMaxExtAcadData(&REPORT) Returns Record;
   Local SQL &SqlExtAcad;
   Local Record &extAcadData;
   Local String &saveOrgId;
   
   &extAcadData = CreateRecord(Record.EXT_ACAD_DATA);
   &SqlExtAcad = CreateSQL(SQL.MAX_EXT, &extAcadData, &RECORD.EMPLID.value);
   While &SqlExtAcad.Fetch(&extAcadData)
      If None(&saveOrgId) Then
         &saveOrgId = &extAcadData.EXT_ORG_ID.Value;
      Else
         /* if more then one EXT_ORG_ID is found report and skip process */
         If &saveOrgId <> &extAcadData.EXT_ORG_ID.Value Then 
            &REPORT.REASON.value = "Verify EXT_ORG_ID";
            Return Null;
         End-If;
      End-If;
   End-While;
   If None(&extAcadData.EXT_ORG_ID.Value) Then
      Return Null;
   End-If;
   Return &extAcadData;
End-Function;

Function getErlyAvg(&REPORT, &AAP) Returns Record;
   Local string &qry, &result;
   Local Record &record = CreateRecord(Record.MCM_FA_ERLY_AVG);
   &qry = "%SelectAll(:1 A) A WHERE ...";
   SQLExec(&qry, &record, ... , &record);
   If All(&record.INSTITUTION.Value) Then
      &REPORT.MCM_FA_AWARD_VALUE.value = &record.MCM_FA_AWARD_VALUE.Value;
      &REPORT.MCM_FA_MIN_SCH_AVG.value = &record.MCM_FA_MIN_SCH_AVG.Value;
      &REPORT.MCM_FA_MAX_SCH_AVG.value = &record.MCM_FA_MAX_SCH_AVG.Value;
      Return &record;
   Else
      Return Null;
   End-If;
End-Function;

Function hasFaYear(&REPORT, &AAP) Returns boolean;
   Local string &result;
   
   SQLExec(SQL.MCM_0784_SQL3, &AAP.EMPLID.value, ... , &result);
   &REPORT.AID_YEAR.value = &result;
   Return True;
End-Function;

Function hasFaOffer(&REPORT, &AAP) Returns boolean;
   Local string &exists;
   Local number &result;
   
   SQLExec(SQL.MCM_0784_SQL4, &AAP.EMPLID.value, ... , &exists, &result);
   If All(&exists) Then
      &REPORT.EARLY_OFFER_TOTAL.value = &result;
      Return True;
   Else
      Return False;
   End-If;
End-Function;


Proof of Concept

Here is a small little code snippet I used as a proof of concept

Function getTrue(&array) Returns boolean;
   MessageBox(0, "", 0, 0, " getTrue Function ");
   &array.Push("True");
   Return True;
End-Function;

Function getFalse(&array) Returns boolean;
   MessageBox(0, "", 0, 0, " getFalse Function ");
   &array.Push("False");
   Return False;
End-Function;

Local boolean &resume = True;
Local array of string &evalCount;

&evalCount = CreateArrayRept("", 0);

Evaluate True
When &resume
   MessageBox(0, "", 0, 0, " Evaluate 1 ");
   &evalCount.Push("1");
When &resume
   MessageBox(0, "", 0, 0, " Evaluate 2 ");
   &evalCount.Push("2");
   &resume = getTrue(&evalCount);
When &resume
   MessageBox(0, "", 0, 0, " Evaluate 3 ");
   &evalCount.Push("3");
When &resume
   MessageBox(0, "", 0, 0, " Evaluate 4 ");
   &evalCount.Push("4");
   &resume = getFalse(&evalCount);
When &resume
   MessageBox(0, "", 0, 0, " Evaluate 5 ");
   &evalCount.Push("5");
When &resume
   MessageBox(0, "", 0, 0, " Evaluate 6 ");
   &evalCount.Push("6");
   &resume = getTrue(&evalCount);
End-Evaluate;

MessageBox(0, "", 0, 0, " Array %1 ", &evalCount.Join(", "));

Wednesday, May 21, 2014

PeopleSoft Manual Auto Numbering

Record Setup

My setup for an auto numbering in a peoplesoft record was simply to define the next value at save time.  There is another way to do this that includes built in functions and requires more setup in some of the PeopleSoft delivered tables.  However, my approach was for a small number of users and records so I used the simpler approach.  The Record just needs to have an ID value that will trigger some PeopleCode at save time.  Create your new record with the default value with a trigger such as "New".



People Code

Now in the PeopleCode we need to setup some logic in both SearchInit and SavePreChange.  The SearchInit checks the PeopleSoft variable Mode and if it's (A)dd Mode then we are going to make sure ID value is set to the same default value "NEW" because the record default is not used in this case.  The SavePreChange code will be triggered when the new record is attempting to save to the database.  The logic will check for our "NEW" value and if the ID is still that we select the max value on the table and add 1 before saving.

SearchInit

If %Mode = "A" Then
   MCM_MRF_IP_SCN.MCM_IP_ID.Value = "NEW";
   SetSearchDialogBehavior(0);
End-If;

SavePreChange

Local string &nextId;

If MCM_MRF_IP_SCN.MCM_IP_ID.Value = "NEW" Then
   SQLExec("Select max(MCM_IP_ID)+1", &nextId);
   MCM_MRF_IP_SCN.MCM_IP_ID.Value = &nextId;
End-If;

This code is limited to only numeric values in the ID column.  In our environment we are using a char to hold the numeric and our customer wants leading zeros on all the numbers.  There was also a special condition requested for the customer to have obsolete or test data in the table that would have been prefixed with an alpha char i.e. ID = "X01" or test data may be ID = "T01".   I was asked to ignore these cases that would have caused the query to fail anyways because they are not numeric and arithmetic can't be done on the value returned.  The final solution looked like the following:

Query for ONLY numeric ID's and add Leading Zeros

Local string &nextId;
Local string &qry;

If MCM_MRF_IP_SCN.MCM_IP_ID.Value = "NEW" Then
   &qry = "Select max(MCM_IP_ID)+1 from %table(MCM_MRF_IP_SCN) ";
   &qry = &qry + " where REGEXP_LIKE(MCM_IP_ID, '^[[:digit:]]+$') ";
   SQLExec(&qry, &nextId);
   &nextId = Rept("0", 3 - Len(&nextId)) | &nextId;
   MCM_MRF_IP_SCN.MCM_IP_ID.Value = &nextId;
End-If;

The Message Catalog

Define a message

The utility page for creating your messages if found in:
Main Menu > People Tools > Utilities > Administration > Message Catalog

 Message Set is a grouping of related messages each with their own number.  The message text is what will be displayed in the Message log or on the screen.  If the users clicks on Explain in the message log they will get the Message Text and Description from the message catalog.  


Triggering a Message

When you kick out a message from Application Engine it will try to resolve the text using the Set number followed by message number.  If the message is not found the default text in the example below will be provided.  You can also include place holders for both default text and a message catalog version.

MessageBox(style, title, message_set, message_number, default_text [,parameters] )

In this example I created a set 21007 and Message number 7 is an employee Count.


App Engine Message Example

MessageBox(0, "", 21007, 7, "Count = %1 ", &mainCount);


No Catalog Example

In some cases you want to spit out a message during testing but it's not going to be included in the final release of message.  For these the common practice is to use set 0 and message number 0.  We will remove them or comment the messages out before moving to production.

MessageBox(0, "", 0, 0, " Testing Main Counter = %1 ", &mainCount);