25/03/2014

How to Handle a Job Definition with a Deleted Process Definition

We are currently in the middle of a Financials 8.9 to 9.2 Upgrade. One of the changes delivered is the old delivered Journal Post COBOL (GLPPPOST) has been replaced with a new App Engine called GL_JP. The old COBOL was included in a custom Job Definition. Following the 9.2 upgrade process we were left with our Job Definition that still included the old Process Definition, even though it was deleted as part of the upgrade. Following the upgrade one of my colleagues tried to delete the old Process Definition from the Job Definition and replace it with the new one. But they received this error when trying to save:
"The attempt to use CreateProcessRequest was unsuccessful because the system was unable to find the Process Name/Process Type from the Process Scheduler Definition table. Verify the Process Name/Process Type passed as parameters are valid. If this is a single process,check if the process is found in the Process Definition. If this is a Job(with process type of 'PSJob), check if the job is found in the Job Definition."
This is due to some SaveEdit processing where the Job Definition tries to verify if there is any recursive references in the Job. Because it does this in SaveEdit, it appears it is trying to verify the old version of the Job Definition with the old Process Definition, which of course doesn't exist. (I think this is because Jobs and Process Definitions are cached. I may be wrong on this however.) So how to get around it? I found a reference on the Oracle Support website mentioning the workaround of recreating the Job Definition. But if it is a complicated Job Definition with many Job Items, I think the simplest way to get around the issue is to:
  1. Temporarily re-create the Process Definition
  2. Delete this Process from the Job (because the Process Definition now exists, the SaveEdit processing will run to success)
  3. Add the new Process Definition to the Job
  4. Delete the Process Definition. To do this you need to:
    1. Create a temporary Application Designer project definition
    2. Add the Process Definition
    3. On the Upgrade Tab for Process Definitions change the Action for this Process Definition from Copy to Delete
    4. Save the Project to file (take note of location where you save it)
    5. Choose Tools, Copy Project, From File... and select the file you just created
    6. It will likely prompt you that the Definition already exists in the Database. If it does select Use Project Definition from: File and click OK
    7. Then click Copy to copy the project from file, deleting the Process Definition you created in the first step above.
You are then left with a Job Definition which includes the new Process Definition. And the old Process Definition again has been deleted.

This gets around the issue following the upgrade. I think the better way is to not have the issue to begin with. I think modifying the upgrade steps so the Job Definition gets updated prior to the old Process Definition being deleted would be better. Which is what I will be telling another colleague who is responsible for managing the upgrade.

05/03/2014

Attachments issue in Google Chrome with PeopleTools version 8.53.10

I was just assigned a PeopleTools 8.53 issue where an attachment can't be added in Google Chrome web browser. The attachment modal dialog is displayed and you can choose the file to upload. But when you click the Upload button the dialog disappears but the screen remains blue and nothing is clickable/editable. A search on Oracle Support returns a solution: https://support.oracle.com/epmos/faces/DocContentDisplay?_afrLoop=11708355275664&id=1628372.1&_afrWindowMode=0&_adf.ctrl-state=zuq8xq9w7_4 The solution is a temporary workaround as the bug is not due to be fixed until PeopleTools 8.54. The workaround as provided works but in some situations (most notably the home page) the code throws the error Uncaught ReferenceError: bLoadCompleted is not defined. 

So instead of adding the line of code as suggested

|| bLoadCompleted 

a better solution would be to add this

|| (typeof bLoadCompleted != "undefined" && bLoadCompleted)

Using the typeof bLoadCompleted != "undefined" prevents the Uncaught ReferenceError: bLoadCompleted is not defined.  from being displayed. And now your web browsers debugging console won't get filled up with errors.

22/10/2013

Setting Parameters for PS Utilities Favorites

My last post I talked about the PS Utilities Chrome Extension. Since it's initial release the PS Utilities extension has been updated with some new features such as
  1. adding the toolbar to the sign in page,
  2. a Quick Logins menu item on the sign in page to allow you to store credentials and quickly sign in to an environment,
  3. managing separate PeopleSoft environments,
  4. adding help pages,
  5. and improving the overall User Interface.
One feature there from the beginning has been adding Favorites that span your PeopleSoft environments. This makes managing Favorites so much easier. You can skip adding Favorites using the delivered functionality in each individual environment. (Inevitably they always got out of synch for me.) Instead you can use the PS Utilities Favorites functionality. You can add a Favorite once in one environment and the favorite will then show up in each environment you have PS Utilities active.

One great thing is you can add parameters to customize your Favorites. Open the PS Utilities options page and click on the Favorite tab (or choose Manage Favorites from the Favorites menu item.) In the table at the bottom the fourth column is the Parameters tab. The value must begin with a question mark (?) and then include name/value pairs separated with an equal sign (=) and series of pairs separated by an ampersand (&). (See Query Strings and Percent Encoding for more information.) In PeopleSoft the name is almost always the name of the field used in the Search record. For example, if I want to always link to my User Profile, I would include ?OPRID=NYETMAN in the parameters column for the MAINTAIN_SECURITY, USER_MAINT, GBL Favorite entry.

One handy name/value pair you can use is ACTION=A. Most components are registered in the Portal Registry in Update Display mode. But if you include ACTION=A then the component processor switches to Add mode instead (assuming the component has Add mode enabled and you have security access to Add mode.). If you use ACTION=A by itself you will likely be presented with the Add Search page (where you normally have to enter values to uniquely distinguish the new entry you are adding.) But sometimes (like the Personal Data component in PS HCM) that value is pre-populated (NEW in this case) which means the component logic will create the new value automatically. To skip this you can include EMPLID=NEW in your parameter list to jump right into the component in Add mode. The full parameter value for this favorite would be ?ACTION=A&EMPLID=NEW.

The Favorites menu item is probably one of my favorite and most used pieces of the PS Utilities extension.

05/07/2013

Chrome Extension for PeopleSoft

My friend and former colleague Uffe Graakjaer has just published a new Chrome Extension called PS Utilities that might be of interest to PeopleSoft users. It can be found here.

The extension is great for developers, administrators, testers, etc. who
  1. use Google Chrome web browser
  2. to access multiple PeopleSoft environments
  3. with different User IDs.
The main features are available through a new toolbar that can be positioned anywhere on the screen. The toolbar is configurable and can include
  1. the User ID that you are logged in as
  2. the name of the PeopleSoft Environment that you are logged in to
  3. a Shortcuts menu which, unlike Favourites, can span all environments
  4. a link to display menu/component/page information normally accessed through CTRL-J keyboard combination.
I recommend checking it out. Uffe plans to add more features soon. Any ideas for new features (or feedback on the current extension) would also be appreciated.

03/05/2012

Issue with BI Publisher/XML Publisher Word Add-in

Came across a problem yesterday with the Oracle BI Publisher (XML Publisher) add-in for Microsoft Word. The main issue was receiving a Macro error when using any of the Oracle BI Publisher menu items (such as Data, Load Sample XML Data…) I've found references on website forums stating that corrupt .exd files might be the culprit. This seemed to be the case for me. If you come across such an issue, here are the steps that I followed to fix the issue:

  1. Close all open Word windows. (Sometimes I had to shut down the PC completely.)
  2. Open My Computer and from the Tools menu choose Folder Options… On the View Tab, scroll down through the Advanced Settings: list and ensure the Hide protected operating systems files (Recommended) is NOT ticked. Press OK. 
  3. Now go Start, Run… and enter the following %userprofile%\Application Data\Microsoft\Forms and press the OK button. 
  4. In the window that opens up find the file that ends with .exd. Right click the file and choose Rename. Keep the filename as is and add .old to the end of it. (The next time this file is needed a new version will get placed here.) If there is more than one file that ends with .exd then rename all of these. Close the Window.
  5. If you had Hide protected operating systems files (Recommended) ticked all along, follow the instructions in step 3 to go back and tick that setting again. 
  6. Now start up Word and see if BI Publisher/XML Publisher is working. If not, there may possibly be other .exd files in other locations that are corrupt. Seek those out and try renaming them. Or there might be another unrelated problem. I'd start by reinstalling the Oracle BI Publisher Desktop.
Hope this helps.

24/04/2012

Using WITH Clause to mimic App Engine Temp Tables

I've been working on an App Engine that makes use of Temp tables and uses the %Table meta-sql. It's sometimes difficult to debug your App Engine SQL because the data in the temp table gets deleted at the end of the run. So I get around this in my SQL client by using the WITH clause

WITH  AS 
(subquery_sql_statement),
 AS
(sql_statement_from_alias_one)

That doesn’t make a lot of sense, so here’s a simple example you can run.

with NY_SSR_TSC_TMP AS
(
SELECT '0' PROCESS_INSTANCE,    1 REPORT_REQUEST_NBR,    1 REQUEST_SEQ_NBR,    1 REPORT_NUMBER,    '9999991' EMPLID,    'PGT' ACAD_CAREER  FROM dual
union
SELECT '1' PROCESS_INSTANCE,    1 REPORT_REQUEST_NBR,    1 REQUEST_SEQ_NBR,    1 REPORT_NUMBER,    '9999992' EMPLID,    'UG' ACAD_CAREER  FROM dual  
)
select * from NY_SSR_TSC_TMP;

So I’ll turn an App Engine sql statement like this (from the delivered SSR_TSC_LIB App Engine - it's a library used by the Campus Solutions Transcript Generation Process):

SELECT Z.REPORT_REQUEST_NBR 
,Z.REQUEST_SEQ_NBR 
,Z.REPORT_NUMBER 
,%Bind(SEQNO) 
,%Bind(PRINT_AREA_CODE) 
,%Bind(SSR_ROWTYPE) 
,A.AID_YEAR 
,A.ITEM_TYPE 
,A.ACAD_CAREER 
,B.DESCR 
,A.DISBURSED_AMOUNT 
,A.ACCEPT_AMOUNT 
,%Bind(TSCRPT_TYPE_AET.AWARD_AID_YEAR_PRT) 
,%Bind(TSCRPT_TYPE_AET.AWARD_AMOUNT_PRT) 
FROM %Table(SSR_TSC_TMP) Z 
, PS_STDNT_AWARDS A 
, PS_ITEM_TYPE_FA B 
WHERE Z.PROCESS_INSTANCE = %ProcessInstance 
AND A.EMPLID = Z.EMPLID 
AND A.INSTITUTION = %Bind(INSTITUTION) 
AND A.ACAD_CAREER = Z.ACAD_CAREER 
AND A.ACCEPT_AMOUNT > 0 
AND A.ITEM_TYPE = B.ITEM_TYPE 
AND A.SETID = B.SETID 
AND A.AID_YEAR = B.AID_YEAR 
AND B.INCLUDE_IN_TSCRPT = 'Y' 
AND A.AWARD_STATUS = 'A' 
AND B.EFF_STATUS = 'A' 
AND B.EFFDT = ( 
SELECT MAX(B1.EFFDT) 
FROM PS_ITEM_TYPE_FA B1 
WHERE B.SETID = B1.SETID 
AND B.ITEM_TYPE = B1.ITEM_TYPE 
AND B.AID_YEAR = B1.AID_YEAR);

into something like this (%Table(SSR_TSC_TMP) changed to NY_SSR_TSC_TMP, %ProcessInstance changed to 0, some %Bind’s removed and the %Bind(INSTITUTION) in the WHERE clause changed to 'ZZZZZ'):

with NY_SSR_TSC_TMP AS
(
SELECT '0' PROCESS_INSTANCE,
1 REPORT_REQUEST_NBR,
1 REQUEST_SEQ_NBR,
1 REPORT_NUMBER,
'9999991' EMPLID,
'PGT' ACAD_CAREER
FROM dual 
)
SELECT Z.REPORT_REQUEST_NBR 
,Z.REQUEST_SEQ_NBR 
,Z.REPORT_NUMBER  
,A.AID_YEAR 
,A.ITEM_TYPE 
,A.ACAD_CAREER 
,B.DESCR 
FROM NY_SSR_TSC_TMP Z 
, PS_STDNT_AWARDS A 
, PS_ITEM_TYPE_FA B 
WHERE Z.PROCESS_INSTANCE = 0 
AND A.EMPLID = Z.EMPLID 
AND A.INSTITUTION = 'ZZZZZ'
AND A.ACAD_CAREER = Z.ACAD_CAREER 
AND A.ACCEPT_AMOUNT > 0 
AND A.ITEM_TYPE = B.ITEM_TYPE 
AND A.SETID = B.SETID 
AND A.AID_YEAR = B.AID_YEAR 
AND B.INCLUDE_IN_TSCRPT = 'Y' 
AND A.AWARD_STATUS = 'A' 
AND B.EFF_STATUS = 'A' 
AND B.EFFDT = ( 
SELECT MAX(B1.EFFDT) 
FROM PS_ITEM_TYPE_FA B1 
WHERE B.SETID = B1.SETID 
AND B.ITEM_TYPE = B1.ITEM_TYPE 
AND B.AID_YEAR = B1.AID_YEAR);

The power of the WITH clause really proves itself out whenever the new dummy table is referenced more than once in your SQL statement.

16/03/2012

Problems with Pagelet Wizard Data Source Parameters

A fellow developer ran into a problem recently when he tried creating a pagelet using the Pagelet Wizard. For the purposes of this post I'll simplify what he was doing in order to get to the heart of the problem. The underlying query he used for the pagelet had 2 prompts. The second prompt used the value of the first prompt to limit the values that are returned. You often see prompts like this where you first select a Business Unit first and then another value from a table where Business Unit is a key. And you need to select the Business Unit first or the 2nd prompt will return no values. When he ran this Query from Query Manager or Query Viewer, the prompts worked as expected.

Later he created the pagelet using this query in the Pagelet Wizard. The prompts from the query were picked up without any problem as Data Source parameters (Step 3 of 6 in the Pagelet Wizard.) By specifying them as "User Specified" for the Usage Type, you get the Customize Pagelet link (the pencil icon on the pagelet border) when it is published to the homepage. On the homepage he could click the pencil icon and the prompts were displayed correctly, but they didn't work. The first prompt could be selected. But the value returned had no impact on the second prompt. After we looked into it we discovered that it was an issue with this customize pagelet component (PTPPB_USER_PREF).

To get around this issue I suggested that a new component needed to be displayed whenever the pencil icon was clicked. Luckily this can be easily done. Whenever a homepage pagelet has been created using the Pagelet Wizard, a new content reference is added to the Portal Registry. One of the options that is available for a pagelet content reference is the content reference for the Edit URL Information. By default the Pagelet Wizard sets this to the delivered component (the one we discovered that doesn't work when using prompts where the first value should be used to limit the second prompt values.) So by overwriting this content reference, we can set it to anything we want. So that's what we did.

The new component was built using derived records and fields. So we could make the prompts work the way we needed them to. We had to write the values selected back to the table that is used to store Customise pagelet values for Pagelet Wizard built pagelets. This was easy enough to figure out. It's the PTPPB_USER_PRMS table, and is simply keyed on the Pagelet name, Pagelet Field and OPRID.

So problem solved, right? Not quite. The client we were working for had decided to allow quite a few users access to the Pagelet Wizard, not just developers or analysts. They figured if users could have access to Query Manager to build Queries, they could also have access to the Pagaelet Wizard to let them display their query results in a nice, easy to view format. This means that almost any Pagelet Wizard user could modify and redeploy this Pagelet. Each time this happens, the Customize content reference would get set to the default, over-writing our custom component's content reference.

This solution really is only an option where Pagelet Wizard is used by developers or analysts and is subject to change control, which is how it was at my previous employer. In this situation support documentation can be created to highlight the fact that if the pagelet is modified, the Customise content reference needs to be reset to our custom component.

In the end, my fellow developer had to customise the delivered Customise component, to make it work correctly. Sometimes you just have to customise, don't you?