Dynamic Views in Application Designer

Views are a useful feature of SQL databases, letting us create virtual tables based on SQL select statements.  The Peoplesoft Application Designer also lets us create dynamic views, which we may use within the page processor.

What Are Dynamic Views?

We can use SQL views in Peoplesoft pages, PS Query, Application Engine programs, SQR programs, and database browsers like SQLPlus and TOAD because they are database objects.  We can use dynamic views in Peoplesoft pages only because they are Peopletools objects.

After we specify the field layout of an Application Designer record, we can save it as a SQL table, SQL view, dynamic view, derived/work record, subrecord, query view, or temporary table.  Like a SQL view, a dynamic view uses a select statement.  However, Peoplesoft uses a SQL view by referring to it by name.  Peoplesoft uses a dynamic view by using the SQL we wrote for it.

Suppose we create a SQL view called PS_ONLEAVE_VW with this SQL statement.

select EMPLID, DEPTID, JOBCODE from PS_EMPLOYEES where EMPL_STATUS in ('L','P')

When the page processor executes the SQL view, it sends a SQL statement to the database that uses the view we created, like this.

select EMPLID, DEPTID, JOBCODE from PS_ONLEAVE_VW

Suppose we create a dynamic view called PS_ONLEAVE_DVW with the same SQL statement.

select EMPLID, DEPTID, JOBCODE from PS_EMPLOYEES where EMPL_STATUS in ('L','P')

When the page processor executes the dynamic view, it sends a SQL statement to the database that it created from the text of the dynamic view, which may be identical to our definition, like this.

select EMPLID, DEPTID, JOBCODE from PS_EMPLOYEES where EMPL_STATUS in ('L','P')

What Are Dynamic Views For?

(I know prepositions are words I should never end a sentence with.  I was never taught to. That’s a grammatical mistake up with which you should not put.  Thanks, Winston Churchill.)

Rooting around in Peoplebooks tells us how to create a dynamic view and what features it has beyond those a SQL view.  Peoplebooks are more modest in telling us why we would use a dynamic view.  A dynamic view’s select statement may include Peoplesoft’s meta-SQL, and it may be replaced by a different SQL statement while the user is using the page.  I appreciate the power of meta-SQL in the complex SQL we need for Application Engine programs, but I don’t develop for multiple database platforms and have less use for meta-SQL in views.

I was drawn to dynamic views by the opportunity to change its SQL in real time.  Peoplesoft’s page processor cleverly finds effective dates and setids on a page and uses them for related fields.  When there are key dates and fields with other names or more unusual relationships between tables, it can be difficult or impossible to get the page processor to guess them.  Redefining the view after the necessary data is in the component buffer gives us enormous power to control a grid, a prompt table, or more.

Case Study

Peoplesoft Time & Labor has a Timesheet page in which employees can enter data about the hours they have worked.  The TRC field contains Task Reporting Codes, which each organization creates to categorize time; e.g. REG for regular, OVT for overtime, VAC for vacation.

Different employees are eligible to use different sets of TRCs.  Non-exempt employees may use overtime codes, exempt employees may not.  Regular employees may use vacation codes, temporary employees might not.  Peoplesoft assigns employees to workgroups that we define in the effective dated PS_TL_EMPL_DATA table.  Each workgroup has a TRC program (a list of eligible TRCs) defined in the effective dated PS_TL_WRKGRP_TBL.  Each TRC program is defined in PS_TL_TRC_PGM_TBL with TRCs listed in the child table PS_TL_TRCPGELE_TBL, which are effective dated in sync.

The Page PeopleCode for the Timesheet page sets up a pulldown menu for TRCs based on the employee and the dates covered by the timesheet.  It executes SQL that joins all the tables of the previous paragraph, and more, to load the menu.  The record field properties for the TRC field name PS_TL_TRC_TBL as the prompt table, to validate the data entry value.  The TRC table lists all TRCs that anyone might use, making the validation more permissive than the menu offerings.

Another approach would be to set the prompt table to a derived view, and to make the derived view SQL the same as the SQL used to populate the menu.  It’s hard to write a SQL select statement with multiple table joins and multiple subqueries for effective dates for a view.  The page processor might not be able to determine which values to use for bind variables.

I wrote a bolt-on timesheet page.  This seems like reinventing the wheel, but I found the delivered Timesheet component too complex and too often hit by bundles to customize for our needs.  My page used a SQL table record at level 0 called PS_TIMECARD_HEADER, a derived/work record at level 0 called TIMECARD_HDR_WRK, and a SQL table record at level 1 called PS_TIMECARD_ENTRY.  The RowInit Peoplecode in PS_TIMECARD_HEADER uses the employee and date range of the page to perform one-time SQLExec functions to load the employee’s workgroup and TRC program into TIMECARD_WRK.

The record field properties for TIMECARD_ENTRY.TRC use a dynamic view as the prompt table for lookup and validation.  The prompt table offers a selection of valid values and it validates data entry.  Unfortunately, as of Peopletools 8.47, the dropdown list box doesn’t seem to work with dynamic views.  But, the great thing about a derived view is that we can customize it for a particular situation in run time.  It didn’t matter what SQL I used for TRC_DVW, or whether I wrote any SQL at all.

The RowInit Peoplecode for TIMECARD_ENTRY included this command.  It didn’t matter which field’s RowInit I used for it, so I put all my RowInit functions in one Peoplecode program.

TIMECARD_ENTRY.TRC.SqlText = "select A.TRC from PS_TL_TRC_TBL A, PS_TL_TRCPGELE_TBL B where A.EFFDT = (select max(EFFDT) from PS_TL_TRC_TBL where TRC = A.TRC and EFFDT <= to_date('" | TIMECARD_HEADER.PAY_END_DT | "', 'yyyy-mm-dd')) and B.TRC_PROGRAM = '" | TIMECARD_HDR_WRK.TRC_PROGRAM | "' and B.EFFDT = (select max(EFFDT) from PS_TL_TRC_PGM_TBL where TRC_PROGRAM = B.TRC_PROGRAM and EFFDT <= to_date('" | TIMECARD_HEADER.PAY_END_DT | "', 'yyyy-mm-dd')) and B.TRC = A.TRC order by A.TRC";

Here is the same SQL without quotation marks, without date format conversion, and with formatting for readability.

select A.TRC
  from PS_TL_TRC_TBL A, PS_TL_TRCPGELE_TBL B
 where A.EFFDT = (select max(EFFDT)
                    from PS_TL_TRC_TBL
                   where TRC = A.TRC
                     and EFFDT <= TIMECARD_HEADER.PAY_END_DT)
   and B.TRC_PROGRAM = TIMECARD_HDR_WRK.TRC_PROGRAM
   and B.EFFDT = (select max(EFFDT)
                    from PS_TL_TRC_PGM_TBL
                   where TRC_PROGRAM = B.TRC_PROGRAM
                     and EFFDT <= TIMECARD_HEADER.PAY_END_DT)
   and B.TRC = A.TRC
 order by A.TRC

Comments are closed.