Decoding Project Items Table

As you all might know, PeopleSoft is a table driven application. i.e PeopleSoft stores most of its meta data in the PeopleTools tables/Metadata tables. The same is applicable for the projects you create in application designer. All the objects that you include in the project are stored as a separate row in a PeopleTools metadata table called PSPROJECTITEM. In normal situations you need not bother about these tables as these are purely reserved for PeopleTools internal processing. But there will be some specials scenarios where you need to drill into the metadata tables. This is especially applicable when something wrong happens in production and if you are in charge of analyzing which project or change broke the code line in your production system. I have formulated a sql which will list all the objects with their name and type as it appears in the application designer. I thought of sharing it over here so that anyone looking for similar information is also benefitted. The sql provided below is a simple sql which decodes the object type code used by the PeopleTools. But the real benefit comes if you can take the decoded values and join with other criteria which can directly fetch an impacted project name from the database instead of investing a lot of your valuable time in manually searching for projects. The intention is to give you a hint to start with.

The sql provided below is supposed to work fine for Oracle database and PeopleTools version 8.53. If you have any other database, make the appropriate changes to the sql to fetch the result.

Select

rownum as Serial_Number,

decode(objecttype,

0,'Record',

1,'Index',

2,'Field',

3,'Field Format',

4,'Translate',

5,'Page',

6,'Menu',

7,'Component',

8,'Record PeopleCode',

10,'Query',

11,'Tree Structure',

12,'Tree',

6,'Access Group',

17,'Business Process',

18,'Activity',

19,'Role',

20,'Process Definition',

21,'Server Definition',

22,'Process Type',

23,'Job Definition',

24,'Recurrence Defn',

25,'Message Catalog',

29,'Business Interlink',

30,'SQL',

31,'File Layout',

32,'Component Interface',

33,'App Engine',

34,'App Engine Section',

35,'Message Node',

36,'Message Channel',

37,'App Message',

38,'Approval Rule Set',

39,'Message PeopleCode',

40,'Subscribe PeopleCode',

42,'Comp Intfc PeopleCode',

43,'App Engine PeopleCode',

44,'Page PeopleCode',

45,'Page Field PeopleCode',

46,'Component PeopleCode',

47,'Component Rec PeopleCode',

48,'Comp RecField PeopleCode',

49,'Image',

50,'Stylesheet',

51,'HTML',

53,'Permission List',

54,'Portal Registry Defn',

55,'Portal Folder',

56,'Portal CREF',

57,'App Package',

58,'App Class',

62,'XSLT',

64,'Mobile Page',

68,'File Reference',

69,'File Type',

70,'Archive Object',

71,'Archive Template',

72,'Diagnostic',

73,'Analytic Model',

79,'Service',

80,'Service Operation',

81,'Handler',

82,'Service Oper Version',

83,'Routing',

84,'Queue',

85,'BI Template',

86,'BI Report Defn',

87,'BI File Defn',

88,'XMLP Data Source',

89,'WSDL',

90,'Schema',

91,'Connected Query',

92,'Logical Schema',

93,'Physical Schema',

94,'Relational Schema',

95,'Logical Schema Dep',

96,'Document Schema',

97,'Cube Dimension',

98,'Cube Outline',

99,'Cube Connection',

100,'Cube Template',

101,'Delimited Schema',

102,'Positional Schema',

103,'App DataSet',

104,'Test Framework',

105,'Test Case',

106,'App DataSet Binding',

107,'Feed',

108,'Feed Category',

109,'Feed Data Type',

110,'JSON Schema',

111,'RC Service Defn',

112,'RC Service',

113,'RC Service Config',

114,'RC Layout',

115,'Search Attribute',

116,'Search Definition',

117,'Search Category',

118,'Search Context',

119,'Integration Group') As Object_Type,

objectvalue1||decode(objectvalue2,' ','','.'||objectvalue2)||decode(objectvalue3,' ','','.'||objectvalue3)||decode(objectvalue4,' ','','.'||objectvalue4) As Object_Name

from psprojectitem where projectname = 'YOUR_PROJECTNAME';

Less known properties of Session Class

Session is the one of the API classes provided by PeopleSoft to interact with system variables. PeopleCode developers writing code for component interface must have been very much familiar with the session class. People might have visited the session class documentation in PeopleBooks as well. I have visited the page many a times. But what had surprised me was some of the properties of session class which may prove to be very handy when you work for localization, has never caught my attention. Normally these properties will not catch your attention.

The Session Class has three major classes as its property. The PS Message Class, Regional Settings Class and Trace Setting class. Everyone who might have worked with CI’s might be already familiar with the Message Classes. And the Trace Class is to enable various trace parameters via PeopleCode. What I feel most useful and the less known is the Regional Settings class.

If you are working for a customer who is spread globally and you are asked to write code which will work for each regions, then the Regional Setting class will become handy. Some of the major properties of the regional setting class are ClientTimeZone (returns the time zone of the client), Currency Format, DateFormat, DateSeparator, LanguageCode etc. There are some more properties for this class, which you can get by referring PeopleBooks. So what is the highlight? Each of these properties, take example of DateFormat, will return the format in which dates needs to be displayed for that particular logged in user based on the user preference, browser settings, market and overall settings. So finally the property will return a value specifying which format should be used to display a date for a user in a region using that specific browser.

Similarly the Date Separator method will return the separator that should be used to display dates for a user. You can use these properties and display items to a user as per his own regional settings. Now the next question is how to use these properties. You cannot use the property directly because it is a property of a property of the session class. Below is an example of how to use the properties pertaining to regional settings.

&sSeperator = %Session.RegionalSettings.DateSeparator;

Interested in exploring all the available properties? You can visit the below PeopleBook link to get the details on all the available properties.

http://docs.oracle.com/cd/E25688_01/pt852pbr0/eng/psbooks/tpcr/book.htm

Mouseover Popup Pages in PeopleSoft

When you are browsing over internet, you might have seen web pages which will show a single dotted underline over certain words or texts in the page. Once you hover your mouse over such words, you can see that an explanation of the word pops ups in a small rectangular region as an additional layer over the existing page and once you changes the mouse pointer, the context popup is also gone. Have you ever thought that what happens if this feature is available in PeopleSoft? Yes, now this feature is available in PeopleSoft and is called as Mouse over Popup Pages. Currently PeopleSoft delivers mouse over popup pages for Applicant and Employee details in HRMS and Sold To, Bill To and Ship To customers in PeopleSoft FSCM.

What this means is, in selected pages, whenever you hover over an employee id in the page, a popup page displaying the details of the employee will be displayed to you.

Fig 1. MouseOver popup Page (image from PeopleBooks)

Right now the mouse over popup pages are limited and any addition of the fields to these pages are considered as customization.

Are you interested in creating similar popup pages for fields which are not delivered by PeopleSoft? It is a simple 3 step process. Follow the below steps to have your own popup pages assuming you have a tools version of 8.53 or higher.

Step 1: Create a page with the details you need to be displayed. Make sure the keys of the primary record of this page are also present in the context where you use it.

Step 2: Save the page and set the page type as Popup Page in the page properties.

Fig 2. Page Settings

Step 3: Now open your transaction page and select the field to enable popup. On the Page field properties of the selected field, go to the USE tab and Select Mouse Over Popup Option as Page Popup  and provide the page name you have created in the previous step. Make sure all the keys required for the popup page is present in the context.

Fig 3. Page Field properties

Now that you have enabled a related content as the popup page for a specific field, what if my popup is always constant or I may want to display some sort of help text. If that is the scenario, then it is much simpler. You need not have to create a popup page. Instead you can create a message catalog entry and provide the Mouse Over Pop option as Message Catalog Popup  and provide the message number (step 3 of above process).

Special fields in PeopleSoft

PeopleSoft reserves some of the fields with some special characteristics and those fields behave differently from other fields. These fields are meant to be used only for some specific purposes. I will be discussing about two such fields, OPRID and EFFDT, in this post.

OPRID is the field which stores the operator id of a user in the PeopleSoft system. The users use operator id to log in to the PeopleSoft system. PeopleSoft, hence, has a special processing routine for this field. One of the interesting things you can note is by trying to make the field OPRID as a KEY field to any of the table and try to load the data using component processor. Even though you have more than one row of data for that specific table, PeopleSoft always loads only one row of data for the table. And if you observe, you will note that the one row is the row specific to the logged in user. What it means is, PeopleSoft will automatically append a condition OPRID = %OperatorId in the where clause when the data is loaded from the database. So the big question is, how can I overcome this situation? There is one workaround to overcome this situation. The workaround is to check the List Box Item property of the field in the record field definition. Now if you load the page, you will be able to see the full data list. I am still not sure whether this is a bug or it is designed so.

Another interesting and most widely used field is EFFDT. EFFDT is not a normal date field. PeopleSoft has many in-built logic wrapping this field. So if your purpose is to store a date, it is better you use a different date field. The main specialty of the field is, if you use this field then whenever you load the data on the page it will always display the rows of data which is effective as of today and future. To view the past data, you need to log in with Include History mode. But still you won’t be able to edit the data. To edit the data you need to be visiting the component with Correct History mode. All these are captured automatically by PeopleSoft. Also this field has special treatment in some queries, where the effective dated row is automatically returned by the query. Another advantage of the field is, if you have this field on any of your table, then whenever you add a second row, all the details from the previous row will be automatically populated in the new row as well. If you have noticed, you can see another specialty like, if you have this field on a table, then the same table can be used as level 0 and level 1 record. You can include all the keys upto EFFDT into level 0 and the rest of the fields in level 1. And the search and component behaves like a normal parent child relation.

Embedding external pages on a PeopleSoft page

Although rare, you may have come across a situation where you want to display an external website or a portion of external website within your PeopleSoft transaction page. For instance you have your expense report creation page and you may want to display all the recent policy changes related to expense reimbursement as a scroll or news bar within your PeopleSoft page. And your company might already have hosted the relevant information in your company portal. The traditional options you might try out is to provide a link to the company portal or duplicate the information in PeopleSoft. I will talk about an alternative to handle this scenario, which is displaying the relevant portion of your company portal site within the PeopleSoft page. This option is best suited only if your PeopleSoft transaction page has the necessary real estate to accommodate the external site.

To customize the page generated by People Tools, PeopleSoft has provided a mechanism called “HTML Areas” to include your own custom HTML codes. You can extrapolate this option and can extend this to bring up an external site within the PeopleSoft page. iFrames are one of the best options available to bring up other contents without disturbing the existing landscape or architecture.

Let us take a step by step approach on how to bring up a third party page to PeopleSoft page. For that you have two cases. First one is you know the URL upfront and the second case is the URL to be opened up is dynamic.

Case 1: Static URL

Step 1: Open the page in application designer and draw an HTML area of size that you may want to dedicate for the external site.

Step 2: Open the properties of HTML area and on the HTML tab select the value type as Constant.

Step 3: On the edit area copy paste the below code. Remember to replace the URL with the URL which you may want to bring up on the page.

<iframe src="http://www.google.com"></iframe>

Step 4: Save the page and start testing  J.

Case 2: Dynamic URL

Consider the case of bring google page itself on your PeopleSoft page. But this time you want to bring up the google result page directly based on value specified on another field on the PeopleSoft page. So the target external URL may vary from each transaction in this case. The steps are pretty much same except for minor variations.

Step 1: Open the page in application designer and draw an HTML area of size that you may want to dedicate for the external site.

Step 2: Open the properties of HTML area and on the HTML tab select the value type as Field. Provide the record name and field name of any work record field that you may want to use.

Step 3: Now open your page activate event and insert the below code. Note: The event can vary depending upon your requirement.

Rem Change the &sURL value as per the target page you want to bring up;

&sURL = “http://www.google.com/#bav=on.2,or.&fp=1&q=” |TRANSACTION_RECORD.MY_FIELD.Value ;

DERIVED.HTMLAREA.Value = "<iframe src=""” | &sURL | “””> </iframe> “;

Step 4: Save the code and start testing  J.

Overriding Temporary Table Instance

The temporary table instance in an application engine program is controlled by the PeopleSoft processor and it is assigned whenever you start an application engine program. Recently I have seen some threads in online forums asking whether we can set the temporary table instance while running an application engine program. Well, the answer is both Yes and No.

The default instance of a temporary table is decided by the PeopleSoft processor and it is done when you initiate an application engine program. But PeopleSoft has provided some options to override this instance number via PeopleCode. You have a delivered built in function SetTempTableInstance which overrides the existing temporary table instance. The function will return the previous instance number. Once this function is called the entire % Table construct thereafter will then start using the new instance.

/* Change the temporary table instance to 3 */

&nPriorInstance = SetTempTableInstance(3);

/* Change the instance back once you are done. */

SetTempTableInstance(&nPriorInstance);

You should be very careful while using this function. This function is not provided to entirely override the default instance that was created. If you have a scenario where you want to process same set of data temporarily without affecting the already selected data in the default processing, then you can consider using it. Whenever you use this function, try to revert the changes at the end by using the same function so that your default processing is not affected.

Whatever way you are using this function to override the temporary table instance, the key thing to keep in your mind is that you should have a complete control over the data that is handled in the tables. Otherwise you may see varied behaviors in your output.

Navigational functions in PeopleSoft

To transfer from one page to another page or component programmatically, PeopleSoft has provided many built-in functions. I have seen people getting confused on which function to use for their use case, as most of the functions provide same transfer functionality. Each function has its own use case and scenarios to be used with. The purpose of this article is to make a general awareness on the usage of these built in functions for the real scenarios. Most of the readers must be already familiar with the built-in functions mentioned in this blog, so I am not going to explain each functions in detail. For more details on any of these functions, it is advised that you refer People Books.

Let’s take a look at the navigational functions provided by PeopleSoft.

1.       ViewContentURL

2.       ViewURL

3.       %Response.Redirect

4.       DoModal

5.       DoModalComponent

6.       DoModalX

7.       DoModalXComponent

8.       Transfer

9.       TransferExact

10.   TransferModeless

11.   TransferNode

12.   TransferPage

13.   TransferPortal

Opening External Sites

To open external sites or links from within the PeopleSoft application, you can make use of the functions ViewContentURL, ViewURL and %Response.Redirect method.

 If you use ViewContentURL, then your content will not be warped in the PeopleSoft portal. If you want it to be wrapped, then you may use ViewURL function. The first functions will open the url in a new window.

If you are working with iScripts and want to open another iScript page, then you may use the %Response class method.

ViewContentURL(URL_str | URL.URL_ID);

ViewURL(URL_str | URL.URL_ID [, NewWindow]);

%Response.Redirect(URL_Str);

Passing parameters to the component

If your component is designed to accept parameter values, then you can use the functions ViewContentURL and ViewURL functions to call those components with the parameter values. All you need to do is generate the url to the target component using any of the built in functions such as GenerateComponentContentURL() and append that URL with your parameter values. Now you directly use these appended URL with any of the functions mentioned earlier depending on whether you require portal content or not.

Opening another page/component without losing control from current component

Most of the time you may need to bring up data from another page or component to the current context and without losing the current context level. Suppose you are at a page entering some details about an employee. Now you want to bring up the employee address when user clicks on the hyperlink but does not want to navigate away from the main component, so that when they see/confirm the address they can continue the work with the previous component. To provide these features, PeopleSoft has provided the modal functions. Use the DoModal() and DoModalX() functions to bring up a secondary page modally, these will be useful in displaying additional data, warning pages etc. The DoModalX provides you the flexibility of completely replacing the current component with the target page and once you are done, you will be taken back to the original component.

Use the functions DoModalComponent and DoModalComponentX to bring up an entirely new component modally over the current context.  The DoModalComponentX provides the option for the target component to be displayed as replacement or as a layer over the top.

Another option provided by PeopleSoft is TransferModeless function. Use this function if you want to open a component in a modal window over the current window and yet you want to work on both the components at the same time.

The syntax of the functions mentioned is as below.

TransferModeless

        (MENUNAME.menuname,

        BARNAME.barname,  

        ITEMNAME.menu_itemname,

        PAGE.component_item_name,      

        action [, keylist] [, AutoSearch]);

DoModal(PAGE.pagename, title, xpos, ypos, [level, scrollpath, target_row]);

DoModalComponent

(MENUNAME.menuname,

BARNAME.barname,

ITEMNAME.menuitem_name,

PAGE.component_item_name,

action, RECORD.shared_record_name [, keylist]);

DoModalX(showInModal, cancelButtonName, PAGE.pagename, title, xpos, ypos, [level, scrollpath, target_row]);

DoModalXComponent(showInModal, cancelButtonName,

MENUNAME.menuname,

BARNAME.barname,

ITEMNAME.menuitem_name,

PAGE.component_item_name,

action, RECORD.shared_record_name [, keylist]);

Opening another page/component by exiting from current component/page

This one is also a general scenario where you want to navigate away from current component to a new component. Most of you might be already familiar with these functions because it is the traditional way of doing things. For these purpose you may use any one of the Transfer function based on your requirement.

If you want to transfer to another page in the same component, it is advised that you use the tailored function TransferPage(). If you are planning to navigate away to a page in another component then you have the functions Transfer(), TransferExact(), TransferNode() and TransferPortal(). If you are already sure of the keys for the new component, then you can directly use the TransferExact function to save some processing time. If you are not completely sure about the search keys for the new component, then it is advised that you use Transfer function. The later two functions are not that frequently used. If you have some weird scenarios like you want to transfer to a different node or portal, then you may use TransferNode and TransferPortal functions respectively.

The syntaxes of the functions mentioned here are as below.

TransferNode(new_instance, 

      NODE.nodename,

      MENUNAME.menuname, 

      MARKET.marketname,   

      COMPONENT.componentname, 

      PAGE.component_item_name, 

      action [, keylist]);

TransferPortal(new_instance, 

      PORTAL.portalname,

      NODE.nodename,

      MENUNAME.menuname, 

      MARKET.marketname,   

      COMPONENT.componentname, 

      PAGE.component_item_name, 

      action [, keylist]);

Transfer(new_instance,

        MENUNAME.menuname,

        BARNAME.barname,  

        ITEMNAME.menu_itemname,

        PAGE.component_item_name,      

        action [, keylist] [, AutoSearch]);

TransferExact(new_instance,

        MENUNAME.menuname,

        BARNAME.barname,  

        ITEMNAME.menu_itemname,

        PAGE.component_item_name,      

        action [, keylist] [, AutoSearch]);

TransferPage([PAGE.page_name_name]);

These are some of the commonly used navigational functions and their general use cases. You can still make wonders by mixing scenarios with the type of functions used. If you feel there is some better use case for any functions or vice verse, drop your comments in the comments section.