12/08/2015

Save PeopleCode to File

I've worked with a few people who perform a search for a semi-colon character ( ; ) in PeopleCode in the Find In dialog box and use the Save PeopleCode to File option. They do this so all PeopleCode can be saved to a file. And then whenever they want to perform a search they will search the file instead of using the Find In dialog box again. Using a text editor to search the file is usually MUCH quicker than using the Find In dialog box.

I think people use semi-colon for their search because they think every piece of PeopleCode must have one. This in fact is wrong. Here is a 30 line piece of Record FieldChange PeopleCode that will save and compile using PeopleTools 8.54.13. (Granted - it is non-sensical. But I'm just proving the point that not all PeopleCode must have a semi-colon.)

This PeopleCode program makes use of a wide variety of built-in functions and language constructs, such as:
  • If, Else, End-If
  • Evaluate, When, When-Other, End-Evaluate
  • Upper, Lower, String
  • Abs, Max, AccruableDays
  • %Date
  • For, End-For, Exit
  • Try, Throw, Catch, End-Try
  • CreateJavaObject, CreateException
  • Error, MsgGet
  • RECORD.FIELD notation
The moral of this story is: keep in mind that not all PeopleCode has a to have a semi-colon. And if you use the Find In PeopleCode, semi-colon ( ; ), Save PeopleCode to File option, realise that you may not have all your PeopleCode saved to your file. Even though it can be slow, I use the Find In dialog box. I just time other things around it, like writing a blog post.

/* Comment of course */
Evaluate PSOPRDEFN.OPRID
When = "VP1"
   If PSOPRDEFN.OPRID = Upper("cc") And
         "y" <> "z" Then
      If ("a" = "B") Then
         While 1 = 1
            PSOPRDEFN.OPRID = Lower("AA")
         End-While
      End-If
   End-If
   <* Comments too *>
When = "PS"
   If Abs(1.0) = Max(AccruableDays(%Date, %Date, 0)) Then
      Return
   End-If
When-Other
   For &intCounter = 1 To PSOPRDEFN.FAILEDLOGINS
      try
         If &intCounter = Round(100.12912, 0) Or
               String(&intCounter) = CreateJavaObject("String", "Hello").concat(" World") Then
            Exit
         Else
            throw CreateException(2, 160, "'%1' doesn't support property or method '%2'", "SomeClass", "SomeMethod")
         End-If
      catch Exception &exc
         Error MsgGet(0, 0, "hello world!")
      end-try
   End-For
End-Evaluate

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.