PeopleSoft Grid Fun

I’ve been re-engineering an overly complicated page for the last year.  It has multiple grids on it – and it suffers hugely from the software anti-patterns Shotgun Surgery and Feature Creep to name a few.

For this first go-around of re-engineering / refactoring I’ve been pushing code into an App Package.  The page is maxed out regarding the ability to add objects – I don’t know if there’s a Christmas Tree design anti-pattern out there but if there is the page would be the poster child for it.

And the business wants more information.

I’ve designed it to use a secondary page for input, and all the code including the page activate code to be written into the App Package.

The page is going to be very basic – a grid plus the delivered secondary page OK and Cancel buttons.  It’s the grid that’s been fun to deal with.

I need to relabel the grid columns, set columns to be editable or not – and hide columns based on data values.  A perfect candidate for using the Grid Class SetProperties method, right?  Based on the documentation:

Use this method to set multiple properties (column enabled, column visibility, and column label) for one or more columns in a grid.

Here’s the problem with PeopleSoft documentation.  Let me show you the example supplied:

Grid Class Methods

&ARProp= CreateArrayRept(CreateArrayRept("", 4), 0);
&ARProp.Push(CreateArray("JOB_DETAIL", "Y", "Y", "Job Detail"));
&ARProp.Push(CreateArray("JOB_TIME", "Y", "Y", "Job Time"));

Uhm.  I’m using this in an App Package class.  I have to declare all my variables.  This method of the Grid class requires a four-dimensional array.

Okay – so a two dimensional array is declared:

array of array of <some type>  <array name>

I figured a four dimensional array is declared:

array of array of array of array of <some type> <array name>

Tried it – and it worked… I could declare it, create it, push data into it.

Grid.SetProperties threw an error every time I tried to use that array.

Left it alone for a couple of days – did some other things – circled back.  Looked for examples where it’s used.  And found my perfect example in HRS_COMMON.HRS_CONTENT.UI.ContentGridLayout:

Local array of array of string &ARProps;
&ARProps = CreateArrayRept(CreateArrayRept(“”, 4), 0);
&ARProps.Push(CreateArray(Column Name, Column Enabled Y N, Column Visible Y N, Column Label));

If &ARProps.Len > 0 Then

Uhm.  Okay – so that array declaration looks… like it’s not what I’m expecting.  My C experience makes me not trust what I’m seeing – I’ve declared a two dimensional array, right?

It’s the CreateArrayRept method that looks to be the secret sauce here.  It’s created four sub-arrays in the second dimension of &ARProps.  And if arrays tend to give you a headache, thinking of that likely brings on a migraine.

So I refactored my code – made changes based on the above example – it works.



I keep telling people who come to me for help that it’s always the simple things. That little gotcha you forgot, glossed over, didn’t think about…

A simple thing. An easy peasy every day thing. Disable a column in a PeopleSoft page.

The code resides in an Application Package:

method MyMethod
  /+ &grid_name as String +/ Local array of array of string &enable;   


  Local Grid &mygrid = GetGrid(@&my_page, &_grid);


The above method gets called by a test method in another App Package – I’m using a tool called PSunit for my unit testing:

method Test_MyMethod
   %This.Msg(" ");
   %This.Msg("Test_GridUtils: Test_MyMethod: " | &WRK_TBL);

I attached a test page to the PSUnit component. I figured I’d be able to see the results after I fired the test.The test ran fine. No errors. But… the grid on the test page never changed.

I spent a couple of hours between meetings trying to get the grid on the test page to work. Everything worked fine. Just… no change to the grid. Worse the grid showed the data I sent to it as part of the test, it was the disabling of columns and the hiding of columns that didn’t take place.

After checking and rechecking syntax, spelling, specifications of the methods I was using I finally re-read the specification of the Grid Class in PeopleCode and found this line:

The attributes you set for displaying a page grid remain in effect only while the page is active.


Placed the call to the method in the page activate event for the page.


A simple thing.


When Recruiting Goes Too Far

Note: Cross posted on LinkedIn:

<You know who you are – don’t pretend you don’t.  You’ve stalked me for years.  I’ve asked, I’ve pleaded – no relief, no response, no cognizance that I am nothing but a potential payday from you. 

My patience has ended. 

I’ve tacked and my beam is abaft your stern… no mercy, no quarter…

Mr. Mowett, Mr. Pullings, starboard battery!> 

I don’t read your emails anymore.  They go into a file that I don’t look into.  I’ve told you I’m not interested – multiple times – politely – just get me off your list.  Unsubscribe me.  Remove me.  Delete my profile.  I don’t care what you need to do but just stop sending me emails.

No!  I do NOT want to relocate to <some urban hell hole that will eat up any salary I make in taxes and will end up costing me money>…

No!  I do NOT want to take a position with <fill in government agency> that will require me to have <fill in some obscure security review> for a three month position that is below what I was doing a decade ago…

I’ve explained to you, patiently, using monosyllabic language why.  What I am currently doing.  The reasons I don’t want the position.

Yes, I was stupid.  Naïve.  Trusting that a recruiter would realize an individual with decades of experience wouldn’t want to take an entry level help desk job – pre trepanning/major skull injury/stroke/long term Oxy deprivation.

One last email was sent.  No longer polite, stern.  Steely.  Telling rather than trying to ask, no longer attempting to bring a sense of bonhomie into the ‘experience’.

No more.  It’s time to raise the black flag – to fling open the locker containing the cutlasses.

I’m going to start writing about you, your company and my requests to be removed on every single site I can think of:

  • Glassdoor
  • LinkedIn
  • Twitter

Hell – I’ll even open a Fakebook account to display my emails imploring you to remove me from your list…

For the love of God – stop sending me your emails.  I.  Am.  Not.  Interested.

That round that went ‘cross your bow matey?  Ranging shot.  Gun captains, on command – fire for effect lads…

Application Package Weirdness

I’m in an environment that is at PTools level 8.53.  I’ve got a App Package with three classes, and I’m using PSUnit to test the various class methods before I use them in as part of a project I’m working on.

I’ve got a method that simply finds the next Saturday from the current date.  It’s very simple:

* Sets the amend effective date to the
* next Saturday following the current system date.
method SetNextSaturday
Local string &sql = “SELECT NEXT_DAY(sysdate,’SATURDAY’) from dual”;

SQLExec(&sql, &amend_effdt);


The output bind variable &amend_effdt is defined as a private instance for the class.  The method SetNextSaturday is invoked within the class constructor, meaning that as soon as the class object is instantiated the method gets called.

My test is also very simple:

method Run_DateUtils
Local ZG_NEG_AMEND:AmendNegotiation:DateUtils &util = create ZG_NEG_AMEND:AmendNegotiation:DateUtils();
Local date &amend_dt = &util.amendEffDt;
Local string &sql = “SELECT NEXT_DAY(sysdate,’SATURDAY’) from dual”;
Local date &next_sat;
SQLExec(&sql, &next_sat);
%This.AssertDatesEqual(&amend_dt, &next_sat, “Method DateUtils not deriving amend date correctly!”)

When tested this error is thrown:

Test [ZG_NEG_AMEND_TEST:TestDateUtils] failed!
SqlExec: parameter 2 is neither a record object nor a name. (2,284)

Odd.  The SQL runs perfectly in SQL Navigator.

And then I looked at the error explanation.  Parameter 2 is a problem.  Hmmmm.

So I refactored by declaring a local date variable in the method, then assign it’s value to the instance variable.  That worked.  That’s also ugly.

This has also been a problem since 2008 PTools version 8.44 based on this post.  It seems that a property cannot be an output argument of a function, and that an instance variable is treated as a property.

What makes this more confusing is apparently an instance variable is fine for input.  It can be read from, just not written to.

Excel VBA Error #1004 – Excel cannot access the file

This is primarily a PeopleSoft nVision post – but it also pertains to Excel developers.  For those not familiar with it, nVision is a wrapper provided by Oracle/PeopleSoft whereby the Excel application can be used by PeopleSoft processes.  What gets produced is an Excel workbook.

Excel is installed on an application server, and is called via the nVision wrapper.  A number of our nVision layouts (Excel workbooks) have VBA macro code associated with them.

A shift in providing reports to consumers was recently made within the company.  Up til recently the reports were available from shares on the application server where nVision/Excel was running.  That’s been changed – the reports now have to be made available on a separate file server share.

And to make things both simpler as well as complex, the older style UNC path would no longer be allowed; instead all paths have to be DFS pointers.

Our users started running into random problems shortly after the change.  The common error was:

Error Source: Microsoft Office Excel.  Error #1004 – Description: Microsoft Office Excel cannot access the file ‘some file name’. There are several possible reasons:

The file name or path does not exist.

The file is being used by another program.

The workbook you are trying to save has the same name as a currently open workbook.

VBA Help Message # 1001004.

I was able to pin point what code was throwing the error, and it was in a section that does the following:

  1. Create a new workbook from a template
  2. Save the new workbook with a unique file name
  3. Copy some text from the source workbook
  4. Paste it into the new workbook
  5. Run some more vba to make the new worksheets pretty
  6. Save the new workbook and close it out

Rinse and repeat another several hundred times.  It took a couple of tries but from what I determined the error would always get thrown when attempting some action on the new (target) workbook.  And since that workbook was now being created in a remote share, DFS was the culprit.

That is a reasonable assumption based on how DFS works.  That’s not a topic for this post – if you want more Microsoft has an article here.  Note this line from the link – DFS requires Domain Name System (DNS) and Active Directory replication are working properly.

I see DNS and I think HTTP, network packets, domain controllers and RPC.  A far too complex environment for VBA to be operating in.

So to resolve the issue I changed where the work was being done.  Instead of saving the new workbook over the wire to the final destination and then doing more work to it via VBA; the work is back to being done in the same place the source workbook is.  That path is guaranteed by getting the ThisWorkbook.Path value of the source workbook.

So the above list is back to getting accomplished locally.  Once step 6 is complete there are two more items to the task list:

  • Use FIleSystemObject method CopyFile – and put a copy of the new workbook in the new reports share using DFS
  • Then user FileSystemObject method DeleteFile to get rid of the local copy of the new workbook.

No more random errors and the users are back to being happy.  And company policy is maintained.

Excel VBA – Execute macro code in another workbook

File this in the realm of why would I ever do that – then maybe after I explain you’ll see it makes sense.

I do this in situations where I’m ‘exploding’ data into reports.  And while this is more of a PeopleSoft nVision trick, I don’t see why it wouldn’t be useable in other applications.

Let me set up the scenario.  I have a chunk of data dumped into a workbook – I’m going to call it wbSource.  Inside that workbook is a macro that is going to run thru a worksheet and select a section of it based on some value in a column.

I copy and then paste the subset of data into another worksheet.  Nice, but I want it in another workbook.

Okay.  A bit of work here, I create another workbook and use it as a template.  That workbook has whatever base formatting I want along with macro code of its own.

Inside my wbSource I have the following code:

Private Function CreateTargetWorkbook() As Workbook

Dim wbTarget As Workbook
Set wbTarget = Workbooks.Add()
ActiveWorkbook.SaveAs Filename:=, FileFormat:=FileFormatNum
Set CreateTargetWorkbook = wbTarget
Set wbTarget = Nothing
End Function

The above function gets called this way:

Dim wbTarget As Workbook
Dim strRunCommand as String
Set wbTarget = CreateTargetWorkbook

I do my copy and paste code here…

strRunCommand = “‘” &  & “‘!Report_Main”
Application.Run strRunCommand

The italics make it a bit hard to read.  The string you create has a single quote surrounded by double quotes, the fully qualified path and file name, then another double quote, a single quote, an exclamation point, then the name of the macro that gets run followed by an ending double quote.  That gets added as a parameter to the Application.Run command.

Once the called macro code finishes control is passed back to the code in the source/caller workbook.

Now about why I go thru this.  First of all it follows good programming practice in that this follows the Principle of least privilege.  Code that knows how to section and subsection data is now separate from code that knows how to beautify a worksheet.  It also makes it easier to debug.  And later if changes are needed to the way the page is displayed, you don’t take a chance of breaking the code that chunks thru the data.  Vice versa if a change in terms of data occurs.

PSUNIT – a few odds and ends

This is a quick follow-up to the earlier posts I’ve made about PSUnit.  To get more familiarity with the tool I had started to build tests against a piece of a project I had just finished up and moved into production.

I was confident the code in the project was good as I had run test files against it continuously as I was coding.

A PSUnit test found a bug.

Ouch.  Glad I found it and not someone else, and it’s good to have found it now while other parts of the project are still outstanding.  And I’m kicking myself for not having used the PSUnit tool while I was writing the code for the project.

One more thing.  I’ve zipped up a project based on the code provided in the readme file you get if you install the PSUnit tool.  It’s available here.  I created it going thru the readme step by step – the code in the zip file is the result.  It was built using PTools 8.51.

PSUnit – Adding Tests

In my previous post I showed the various Assert tests that come with the PSUnit project.  I decided to add another test to the code.

There is only a single boolean Assert test – which checks if the value is True.  But there are times when a boolean False is the correct response.  So I added an AssertFalse test to the code:

AssertFalse(&isTrue As boolean, &onFail As string);

If the value in &isTrue is True, then the test fails.

Clearly you need to have pulled the PSUnit project into a PeopleSoft database instance and created the tables.  From here, start App Designer and open the TTS_UNITTEST Application Package.

You want to click on the TestBase app class and view the PeopleCode associated with it.

In the class TestBase I added the method definition:

  • method AssertFalse(&isTrue As boolean, &onFail As string);

Then I added the code for the method:

/* This was added to test for boolean False values */
method AssertFalse
   /+ &isTrue as Boolean, +/
   /+ &onFail as String +/
   If (&isTrue) Then
      throw create TTS_UNITTEST:Exceptions:AssertFailed(&onFail);

How is this useful?  Let’s take an example of checking for a valid emplid.  I have a method:

method isEmplidValid
   /+ &empID as String +/
   /+ Returns Boolean +/
   Local boolean &_rtn = False;
   Local string &_msg;
   Local SQL &_sql;
   &_sql = GetSQL(SQL.VALID_EMPLID_CHECK, &empID);
   While &_sql.Fetch(&_msg)
      If &_msg = “X” Then
         &_rtn = True;
   Return &_rtn;

The SQL definition is fairly simple:

   AND A.EFFDT = (

This method validates emplids against the JOB table.  A return of False is a correct data state in this instance.  The Assert test would throw an error even though I should expect some values to return as false.

Next I create the tests.  I have two arrays – one has valid emplids for the organization; the other has invalid emplids.  I can now do both positive and negative testing with this method:

method TestIsEmplidValid
   %This.Msg(“TestFieldsCheckField: TestCheckChars”);
   Local boolean &rtn;
   Local integer &i;
   &rtn = False;
   Local PKGE:EMPLOYEE:CheckEmplid &target = create PKGE:EMPLOYEE:CheckEmplid();
   /* First the positive test */
   For &i = 1 To &_EmplidArray.Len;
      %This.Msg(“TestIsEmplidValid: Assert Test Value: ” | &_EmplidArray [&i]);
      &rtn = &target.isEmplidValid(&_EmplidArray [&i]);
      %This.Assert(&rtn, “Assert method failed”);
   /* Then the negative test */
   For &i = 1 To &_BadEmplidArray.Len;
      %This.Msg(“TestIsEmplidValid: AssertFalse Test Value: ” | &_BadEmplidArray [&i]);
      &rtn = &target.isEmplidValid(&_BadEmplidArray [&i]);
      %This.AssertFalse(&rtn, “AssertFalse method failed”);

Having the AssertFalse method allows me to confirm that my checking routine is working correctly.  I’m able to test that my program can gracefully handle invalid/incorrect data.

PSUnit Assert Tests

PSUnit provides a series of Assert tests.  In the article provided as part of the zipped download Jim Marion goes step by step into the process of creating a test.  However he only uses a single test: AssertStringsEqual.

I dug into the code and listed the tests you can do with PSUnit.

Assert(&isTrue As boolean, &onFail As string);

If the value in &isTrue is False, then the test fails.  From the program notes:

The first argument must be a boolean expression, which should evaluate to true.  If false, PSUnit throws an exception to indicate that the test failed.  The test runner catches the exception, marks the test as having failed, and continues on to the next test.

AssertStringsEqual(&str1 As string, &str2 As string, &onFail As string);

AssertStringsDiffer(&str1 As string, &str2 As string, &onFail As string);

AssertNumbersEqual(&num1 As number, &num2 As number, &onFail As string);

AssertNumbersDiffer(&num1 As number, &num2 As number, &onFail As string);

AssertDatesEqual(&date1 As date, &date2 As date, &onFail As string);

AssertDatesDiffer(&date1 As date, &date2 As date, &onFail As string);

AssertRowsetValuesEqual(&rs1 As Rowset, &rs2 As Rowset, &onFail As string);

Method first checks the ActiveRowCounts between the two rowsets.

Then the method tests that both rowsets come from the same Record.

Finally the method tests the fields in each row of each rowset for equality.

If an Assert test fails, the exception text which includes whatever message you pass on as part of the variable  &onFail gets sent to Class AssertFailed; which then calls the PeopleCode function CreateException.

PeopleSoft PSUnit

I first came across PSUnit in Jim Marion’s book PeopleSoft PeopleTools Tips & Techniques.  It’s a different tool from Test Framework – and you should consider it as complementary to PTF.

Jim has a write up of it here; there is another small explanation of it here.  Both articles give a link to the code, or you can get it here.

PSUnit is a Test Driven Development tool.  With Test Framework you code, create a page, and then put your page into a component.  The component gets applied to a menu which then get set into the registry.

With PSUnit you create a test framework Application Package, which gets plugged into an already existing test page.  Better yet – while Test Framework can help to point out where a transaction throws an exception, PSUnit lets you dig further and find out why.

From Jim Marion’s blog piece PSUnit Unit Testing Framework:

You, the developer, receive a notification from a user that page X of component COMP_X is calculating the wrong values. The user informs you that the calculation and error occur when he/she clicks save. From this information, you speculate that the calculation happens in the SavePreChange or SavePostChange event of the component or some record used in the component. Unfortunately, you are not familiar with this page or component.

With a PeopleCode trace, you are able to identify six potential events. You notice that these events call FUNCLIB’s and App Classes creating a horrendously deep call stack. From what you have in front of you, it is obvious that a quick review of this 3,000+ line trace file won’t provide an easy solution.

At this point you have several options:

Continue to treat COMP_X as a black box, investigating it from the outside.

Dig into the code and speculate as to its purpose.

Configure app server debugging and step through the deep call stack.

Start adding MessageBox statements to the delivered code so you can interrogate the state of the application as it runs.

We will choose the final option, the MessageBox option. Yes, this will require us to modify delivered code, but this modification should have no impact on the behavior of the code. The modification doesn’t concern me as much as forgetting to delete one of those MessageBox statements after I find and fix the problem. And then, once I find the correct combination of MessageBox statements to show the problematic data or logic, I hate to delete them, knowing I may have to visit this code at a future date (sooner then I want, but far enough in the future that I’ve already forgotten how I solved the problem). Wouldn’t it be nice if, once you found the appropriate combination of logging statements, you could just leave them in the code?

You should read the entire post.  I’ll list the various Assert tests that are available in another post.

%d bloggers like this: