"Invalid Value" on a PeopleSoft Page? Check the Case of the Corresponding Field Object

Have you ever had the situation where you know that the data in your lookup table is accurate, yet any time you try to select a value from that table via a drop-down list, you get the "Invalid value" error when you try to save the data?  It's most likely because your database column contains mixed-case data, but the field object that you defined in PeopleSoft App Designer to represent that data requires uppercase data. Here's how I discovered and fixed the problem.

Creating Views of External Database Tables in Peoplesoft

Here are the steps to creating a view in PeopleSoft of data that exists in a database table that is external to your PeopleSoft database.

1. Create a view in the external database of the data you need access to in PeopleSoft.
2. Create a view in the PeopleSoft database of the view that you created in step 1.
3. Create a view record in App Designer of the view you created in step 2.
4. Build your view record in App Designer
5. Save your scripts from steps 1 and 2 so that you can use them to create the same views in the other database environments in your development path when you migrate your new project forward to production.

Using Oracle SQL notation, and using the view Y_ACAD_PROG_VW for my example, I'll explain steps 1 thru 3 below. (To clarify: this process works just as well if the external database happens to be a PeopleSoft database itself.)

Disabling PeopleSoft Grid Using "Page Field Name" Property

After thinking there were no good reasons for using the the “Page Field Name” property of a PeopleSoft object, I finally found one. Rather than using overly complex (for this purpose) Rowset/Row/Record/Field logic to disable each field in a grid, it is possible to use the Page Field Name in simpler logic to disable each column in the grid.

In this case, when a student accesses my page, I want all of the objects to be editable, but when their parent accesses the page, the only thing the parent should be able to do, other than see the student's information, is click a “Validate Student Information” button on the page. So, for parents, I need to disable several edit boxes, radio buttons, and grid columns. If I use the Page Field Name of the Grid and Grid Column objects, then disabling the grid columns is fairly simple.

Assuming the grid's Page Field Name value is “FAMILY_GRID” and the column's Page Field Name value is “FAM_MEM_NAME” , you can use the following code

&famGrid = GetGrid(Page.MY_PAGE, "FAMILY_GRID");

&famGridFamMemName = &famGrid.GetColumn("FAM_MEM_NAME");

&famGridFamMemName.enabled = False;

to get the first column in your grid to be disabled (read-only), as shown below.

PeopleSoft Trace Example: Inserting NULL into Non-Nullable Columns

When PeopleSoft comes across a column with null value in an insert or update situation, it ignores it. If the column itself does not allow null values, it can be confusing what’s going on. The following trace snippet, showing an insert of 13 values into the Y_SHC_PLAN table, illustrates the problem:

INSERT INTO PS_Y_SHC_PLAN (STRM,Y_PLAN_NUMBER,ACTIVE_FLAG,Y_PLAN_TYPE,AMT,Y_POLICY_CODE,Y_NUMBER_COVERED,MAR_STATUS,Y_UCA_SUBCODE,Y_MATCH_MARITAL,Y_REQ_DAY_ELIG,Y_REQ_ZERO_HRS,Y_AFC_MONTHS) VALUES (:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13)
Bind-1 type=2 length=4 value=2105
Bind-2 type=2 length=3 value=001
Bind-3 type=2 length=1 value=A
Bind-4 type=2 length=1 value=D
Bind-5 type=19 length=3 value=212
Bind-6 type=2 length=14 value=SSA BYS LLINDD
Bind-7 type=19 length=1 value=1
Bind-8 type=2 length=1 value=S
Bind-9 type=2 length=5 value=80025
Bind-10 type=2 length=1 value=Y
Bind-11 type=2 length=1 value=Y
Bind-12 type=2 length=1 value=N
Bind-13 type=2 length=1 value=0

The problem is that there are actually 15 columns in the table. App Designer and the database both show the two “missing” columns to be EFFDT_FROM and EFFDT_TO, but neither column shows up in the insert statement. To confuse matters more, the trace file shows that the insert failed because, at first glance, PeopleSoft doesn’t seem to know anything about the two missing but required columns. The error in the trace file is

Return: 1400 - ORA-01400: cannot insert NULL into ("SYSADM"."PS_Y_SHC_PLAN"."EFFDT_FROM")

In reality, though, PeopleSoft knew all about the columns, but it simply chose to ignore them (to leave them out of the insert statement)—it turns out—because their values were null. Specifically, in the section of PeopleCode that tries to update or insert Y_SHC_PLAN records, a SELECT statement had attempted to populate the EFFDT_FROM and EFFDT_TO fields from values in the PS_TERM_TBL, but it had been unsuccessful, because the specified term did not exist in PS_TERM_TBL.

We’ve now fixed the code to throw an error if a user specifies a term which does not exist in the PS_TERM_TBL. And, after a bit of head scratching, we now know why PeopleSoft behaved the way it did by ignoring the null-valued columns.

PSDBOWNER and Why I Can't Login to my New Database

We copied our upgrade database to our development backup database this morning. I had to apply about 13 bundles to the upgrade database to get it current on maintenance.

The next step is to copy the customizations from our dev database into our dev backup database. But this morning after the dev backup database was created, I couldn't login to it using App Designer, even though I could login using the same userid and password to the upgrade database (from which dev backup was a copy).

Does anyone know where this is going? Well, I'm sort of new to PeopleSoft, so I was bordering on the verge of panic, but I found someone else on our staff who has been doing PeopleSoft for a long time.

He said, "Did you update the DBOWNER table?"

And that's all it was. The PS.PSDBOWNER.DBNAME still had the name of the upgrade database in it. Once I updated that, I was able to login and do a compare between the dev and dev backup databases.

To perform a full database compare

1. In App Designer, select Tools->Compare and Report->To Database
   
2. Specify the database in the login dialog and wait for all the permission lists, etc. to load.
   
3. When the Compare and Report Dialog comes up, click the Options button.
   
4. When the Upgrade Options dialog comes up, click on the Compare Options tab.
   
5. In the Compare Type area of the Compare Options tab, click the Database radio button.

A full database compare takes a long time.

Now I'm just waiting for my compare report to run...

Peoplesoft Financial Aid: BYU Institutional Application

For about the past 4 months I've been working on BYU customizations to the PeopleSoft-delivered financial aid module. Most of the time has been spent on what we call the Institutional Application. I'm not sure why Peoplesoft doesn't already have something like this!

Students will be able to apply online for various forms of Financial Aid. The Institutional App displays different options based on whether the student is an undergraduate, law student, etc. Based on the various options the student selects on the page, various To-Do List items are created.

Many To-Do list items contain a message that says something like 'Please take a copy of your 2006 Federal tax forms to the Financial Aid Office.' However, in certain cases, the information can be provided online; therefore, some of the To-Do list items are custom pages as well. A custom cross-reference table identifies the custom pages and, with the help of about 10 lines of code, which checklist item codes will transfer to the appropriate custom page. Some of the information a student can provide online include other schools attended, other sources of financial aid, or whether the student served an LDS mission.

The greatest complication to the Institutional Application is the fact that a student can make an additional request for financial aid if any of the loans or grants for his or her initial request has already been originated.

I spent most of the day working on this functionality, and I think it's ready to go--but what do I know? We'll see what the users think.