PeopleSoft Talk with Marc Weintraub: PeopleSoft in the Cloud featuring J...

Dynamic prompt dates in PS Query

I had an ex colleague asking me for help with question on how to schedule to a PS Query which has two date prompts (namely Start Date and End Date).

Query should be scheduled to run every month and prompt values should be based on the month it is running. e.g: Start Date prompt value should be the first day of previous calendar month and End Date prompt value should be the last day of previous calendar month.

While I'm not sure if there is any PS delivered way to do this, I can think of below options to overcome this problem.

1. Change the PS Query criteria so that it takes expression as parameter instead of prompt. Use expression LAST_DAY(ADD_MONTHS(SYSDATE,-2))+1 for first day of previous calendar month and LAST_DAY(ADD_MONTHS(SYSDATE,-1)) for last day of previous calendar month (Of course I'm assuming it is Oracle DB). You might have to change the expression if you are on any other DB.

2.  Schedule a custom AE/SQL or any other tools to update the run control parameter stored in the table PS_QUERY_RUN_PARM.

In my humble opinion first option is more elegant than the other.

If you have a better solution for this problem, please comment on this post.

Compare Report Output in HTML

I had a requirement to send the project compare report output as html and I remember doing this a while back but couldn't remember the steps. So I did a bit of "googling" and listed below are the tricks to generate the compare report in html.

In Registry Editor, drill down to HKEY_CURRENT_USER > Software > PeopleSoft > PeopleTools> PSIDE

Look for the DWORD UpgHtmlOutput and set it to value 1

Courtesy:peoplesoft.ittoolbox.com