Tips and Techniques

Query Tip: Updating the Effective Dates of In-Tree Criteria

By Larry Grey • June 22, 2010

One limitation of PeopleSoft Query is that when you use “In Tree” criteria, the effective date of the tree is forever stored in that criteria. When reading through the release value proposition of PeopleTools 8.5, I was thinking that feature to dynamicall prompt tree-nodes at run-time was going to also address this limitation. Alas, that was not the case.

Fortunately, through a little bit of PeopleCode, this can be rectified relatively easily.

Cool. Show me the stuff

I decided to implement this using application engine, since most organizations would want to update the effective dates of the queries periodically (most likely on a nightly basis). To simplify the content of this project, I wrote it to process any queries that have GS in the first two characters. You can look at the contents here.

Step 1

The first step is to create the app engine program and put in some PeopleCode to prove you can open up a query with it. Here is that code.

Step 2 – Find the Criterion

The next step is to walk the query object to find its criterion. Here are the modifications to step 1 to log the fieldname of the first piece of criterion.

Step 3 – Loop through all criterion

Now that we know how to access the criteria in a query, we will want to loop through it to do our work.

Here are the modifications to loop through all the criteria and log the field names.

Step 4 – Evaluate Criterion

Now that we can see all the criteria, we need to process only the in-tree criteria (since that’s all we care about for updating the effective date).

Here are the changes to process the criterion and determine whether it’s in-tree criteria. Note that this code has been put into a new function to simplify readability.

Step 5 – Get the Criteria String

On of the things that isn’t documented well, is exactly where the criteria is stored. I spent a little time walking through the debugger in the application designer to determine that it’s in the expr2constant1 property as a comma separated value string.

Here are the modifications to log that value.

Step 6 – Decompose the criteria string

The next step is to take the contents of the exprconstant1 attribute and break it apart into its component parts. This is done using the split function to create an array of values.

Here are the modifications to accomplish this.

Step 7 – Find the new tree effective date

Now that we’ve decomposed the criteria string and know what tree we’re using, we have the ability to determine the current effective dated version. This is done by selecting against the PSTREEDEFN table using the keys of the tree.

Here are the modifications to find the most recent effective date and apply it to the appropriate place in the criteria string and log it.

Step 8 – Save the updated query

Now that we’ve determined how to update the effective date in the criteria, it’s time to save that change back into the system.

Here are the modifications to accomplish this.

Final Step – Loop through the queries

So far, we’ve only been processing a single, sample query. Now it’s time to expand this code to look at all queries that start with our prefix of GS.

Here are the modifications to accomplish this. And, without further ado, the culmination of our work without showing the diffs.

Limitations / Opportunity for Enhancements

There are several areas where this code should be enhanced as part of putting it into place in your environment. You can find a list of them here.

Labels: query code tree effdt

Stay Updated

Request a Demo

Start your free demo

"Learn how you can reduce risk with rapid threat protection, audit response and access control. All from a single, comprehensive platform"

Trusted by hundreds of leading brands