Archive for March 2015

Home Archive for March 2015

How to Display a detailed SQL error (Online)

Sometimes when you navigate to a PeopleSoft page or while doing some work online you might come up with this error.

"A sql error occurred. Please consult log for Administrator."

As developers when we build and test it is handy if we can see detailed error so that we can rectify it quickly. Sometimes it is not that obvious where the error is coming from even though you build the records, views etc.. Otherwise, you have to get the Appserver logs to find out where the error is coming from.

There is a way you can enable SQL error details so that it will be displayed to the users (in this case developers)

If you want SQL error details to be visible to users, set this property as follows:

Suppress SQL Error=0

This property exists in the PSTOOLS section of the PSAPPSRV.CFG file.

"For security purposes, this option has a default value of 1 to prevent SQL error details from being displayed to users.
Any SQL errors that occur don't display details, but refer users to consult the system log.
The details that were in the SQL message are written to the log file. This helps to prevent SQL injection vulnerabilities."

So, if you want to see the detailed SQL error online, ask your PeopleSoft Sysadmin to change this option in App Server PSAPPSRV.CFG file so that next time you can see the detailed SQL error message.

How to Test Delivered PeopleSoft Web Service using SoapUI

Ayesha Wee 20:27 1 Comment Edit

Prerequisites

Download SoapUI from http://www.soapui.org/
SoapUI is an open-source web service testing application for service-oriented architectures (SOA) and representational state transfers (REST).
It's simplicity and easy to use interface allows developers and testers to quickly test PeopleSoft web services without spending too much time of learning about SoapUI.
In this example we are going to look at a delivered web service called List of Values (LOV) web service in PeopleSoft Campus Solutions (CS).

A quick overview of the List of Values Web Service

Suppose you have an online application that presents list of prompt fields to the users.
The list of values retrieved by the service is used to populate drop-down list boxes or prompt fields in an external UI.
The List of Values web service (service operation SCC_GET_LOV) allows an external UI to request a list of prompt or translate values for a field name configured inside the PeopleSoft Campus Solutions database.
Also, the external UI logic can use this retrieved list of values to validate a value that a user enters in a field of the external UI.
As an example assume that you pass valid translate values for possible Phone types for the external UI to display in a Phone Type drop down list so that a valid value can be picked by a user.

The LOV request message (SCC_LOV_REQ message) accepts the following input parameters:
Record Name (required).
Field Name (required).
LOV Context (optional – defined on the List of Values Setup component).

The Get List of Values service operation (SCC_GET_LOV) is called, the external UI nominates the field name and its record for which the service should return the list of values.

So, in this example we will try to get valid phone types from the translate values from PERSONAL_PHONE and the field name PHONE_TYPE (and LOV Context is left blank purposely).

Provide the web service

To provide the web service navigate to PeopleTools > Integration Broker > Web Services > Provide Web Service

In order to generate WSDL (web service description language) for your service, you need to use the Provide Web Service wizard to generate the web service in PeopleSoft.
The service for the List of Values web service is SCC_LOV and the service operation is SCC_GET_LOV.

Step 1

Search for the service SCC_LOV. Select your service and press Next.

Step 2

SCC_LOV service has SCC_GET_LOV service operation. Note that SCC_GET_LOV service operations will have request, response and fault messages associate with it.

Step 3

A WSDL file will be generate for SCC_LOV service. You can view it during this step.

Step 4

The final step is the wizard publishes the WSDL for your web service to the PeopleSoft WSDL reposit

Step 5

The last page will show the confirmation of your results of creating and publishing the WSDL for your web service.
Note down the Generated WSDL URL which will required when you want to create the SOAPUI project.

Create a new SOAP project

Assuming you have already downloaded SoapUI, then Open up your SoapUI instance.
In SoapUI, choose File > New SOAP Project (CTRL + N). Specify the project name, then enter the URL (from step 5) to the WSDL for your web service.

If the project is successfully created then in the left Navigator pane you can see your project and the SOAP Request.

Testing the Web Service

Double-click on the generated request (Request1) and that will open a new request window with a SOAP envelope.
In the generated XML replace the question mark (?) between the ser:FIELDNAME tag and ser:RECORDNAME tag with PHONE_TYPE field and PERSONAL_PHONE record.

For this exercise I'm going to remove Optional sections from the XML as they need to configure for different purposes.
Now my XML request looks like this.

Once you submit this request your response will look like this. Note that all the active Phone Types (Translate values) will be displayed as a CODE/VALUE pair in the response.

Conclusion

In this post we looked at how to quickly test a delivered PeopleSoft web service, List of Values (SCC_LOV) using SoapUI.
List of Values web service is related to PeopleSoft Campus Solutions and this comes handy if someone new to PeopleSoft web service to get a basic idea of how to test a web service.

feel free to send any comments or suggestion!

How to Access PeopleSoft Browser-Based Shortcuts and App Designer Shortcuts

Ayesha Wee 18:09 Add Comment Edit

PeopleSoft applications offer set of keyboard shortcuts to interact with your PeopleSoft application more quickly and easily. These shortcuts are classified into two categories: hot keys and access keys.

To get these shortcuts, Navigate to any PeopleSoft online page and press Shift+Ctrl+K key combo.
You will get a printable list of these shortcuts while online.

Application Designer Keyboard Shortcuts

Some of the most useful PeopleSoft Application Designer Keyboard Shortcuts are mentioned at http://www.peoplesoftwiki.com/application-designer-keyboard-shortcuts

PeopleCode Editor Shortcuts

http://docs.oracle.com/cd/E57990_01/pt853pbh2/eng/pt/tpcd/task_ShortCutKeysinthePeopleCodeEditor-0749b6.html#topofpage

How to dynamically populate data in a grid using SQL views

Ayesha Wee 18:19 Add Comment Edit

PeopleSoft Grids are powerful page controls in PeopleSoft. It can display information in a spreadsheet format and you can embed other page controls such as buttons, drop-down lists, html areas, etc.. to the grid. You use grids depending your table structure and how your page will display to the users based on user requirements. Grids are used in occur Level 1, 2 or 3 and it depends on whether you have more than one underlying record definition on a page.

Sometimes you want to pull information from multiple records and display in a grid on a page.

Since, a grid can associate with only one primary record, you have to create a view to get data from one or more records. Then you can associate the view record as the primary record in the grid.

However, imagine that you want to populate the grid dynamically passing parameters to the view.

Since, views cannot accept parameters dynamically (unlike SQL definitions or dynamic views) how do you handle this kind of situation. You can achieve that with the help of Select method in Rowset class. Select, reads data from the database tables or views into either a row or rowset object.

A good example from PeopleBooks
The following example selects into the child scroll of the level one rowset. Each row fetched is placed under the appropriate row in &LEVEL1.
Note that instead of hard-coding the WHERE clause, the SQL repository is used to access a SQL definition named SELECT_WHERE.

&LEVEL1 = &LEVEL0()(1).GetRowset(SCROLL.EMPL_CHECKLIST);
&LEVEL1.Select(SCROLL.EMPL_CHKLST_ITM, RECORD.EMPL_CHKLST_ITM, SQL.SELECT_WHERE);

Another way you can achieve this, by creating a function to accept dynamic parameters (to WHERE clause) and then calling that function in appropriate event to populate the grid.
Note that here we use rowset Flush method to remove all rows from the rowset and free its associated buffer before selecting to the grid.
Flush method is often used to clear a work scroll before using the Select method.

rem Function to Refresh the Grid;
Function RefreshMyGrid(&Param1, &Param2,..,&ParamN)
   &rs = GetLevel0()(1).GetRowset(Scroll.MY_GRID_VW);
   &rs.Flush();
   &rs.Select(Record.MY_GRID_VW, "WHERE MYFIELD1 = :1 AND MYFIELD2=:2, MYFIELDN=:N", &Param1,&Param2,&ParamN);
End-Function;

How to Generate and Assign Unique Sequence Number in PeopleCode

Ayesha Wee 19:18 3 Comments Edit

Assigning a unique number to the Sequence Number field (or simply a number field) is something we have to do in some point as a PeopleSoft developer. The typical scenario is, you have a record which store multiple set of details and you need to identify each row uniquely. So adding a Sequence Number to the record is pretty common approach and each time you add a row to the record then the Sequence Number will get incremented and assign the next available number to the newly added row. Throughout PeopleSoft Applications (any PeopleSoft application) you can find numerous examples. Most of the time we want to auto assign a sequence number for records which are in Scroll Areas or Grids.
To achieve this you can write a function in a FieldFormula event that implement the algorithm to generate the unique sequence number for a given record. Then, you can basically use that function in RowInset (for example) or any other event as you want.

Below function is implemented in FieldFormula Event

/*This function will Increment and assign unique number to the SEQNBR field;
Input Parameter - Record Object (passed as reference)
This example assume that your record has a field called SEQNBR - (PeopleSoft delivered number field)
You can modify this function to pass your custom Number Field as well, 
so that you don't need to assume SEQNBR is a field in the passing record*/
Function GenerateSeqNbr(&recName As Record)
   
   Local integer &intMaxSeqNbr = 0;
   Local integer &I, &intSeqNbr;
   Local Rowset &RS = GetLevel0()(1).GetRowset(@("Scroll." | &recName.Name));
   
   For &I = 1 To &RS.ActiveRowCount;
      &intSeqNbr = &RS(&I).GetRecord(@("Record." | &recName.Name)).SEQNBR.Value;
      If &intSeqNbr > &intMaxSeqNbr Then
         &intMaxSeqNbr = &intSeqNbr;
      End-If;
   End-For;
   
   &intSeqNbr = &intMaxSeqNbr + 1;
   &recName.SEQNBR.Value = &intSeqNbr;
   
End-Function;

/* To call the function*/
Declare Function GenerateSeqNbr PeopleCode MY_DRV_RECORD.SEQNBR FieldFormula;

/*call the GenerateSeqNbr function for the current record from calling event*/
GenerateSeqNbr(GetRecord());

How to use .Bat File to clear PeopleSoft App Designer Cache in Local Machine

Ayesha Wee 21:44 Add Comment Edit

Have you ever noticed a folder in your local system which created by PeopleSoft App Designer to Cache your PeopleSoft database object information?
To give bit of a background, in users local machine PeopleSoft Configuration Manager automatically store database objects in cache files the first time you open a PeopleSoft database object.
They are also downloaded automatically if the master copy of the object on the database server has changed. For each PeopleSoft database to which you connect, the system creates a single child cache files directory to store the cache files for that database.

So where is this cache directory configured?
Open up your PeopleSoft App Designer and on the Menu Click "Go > Configuration"

This will open up the configuration manager, Look at Cache File Directory field and you can see the Directory where your PeopleSoft cache files are stored.

Sometimes you may want to delete these cache files and you can do by clicking Purge Cache Directories.
But, here we will use a windows batch file so that you can use that whenever you want to purge the local directory cache files.
One of the advantages of this methods is you don't want to login to App Designer.
To do that Copy below commands to Notepad and save the file as .bat extension. Then whenever you want to run this file simply double click to run it.

@echo ON
@echo Deleting files from PS CACHE directory...
set DataDir=C:\PS\CACHE 
del /q /s /f %DataDir%
rd /s /q %DataDir%

PS: In this example we assume that your cache directory is C:\PS\CACHE
running this .bat file will delete CACHE directory as well.If a cache file directory is missing (after you delete it), the system automatically rebuilds it the next time that cache files are downloaded.

Can we Add Attachments That user to files with names longer than 64 characters in PeopleSoft?

Ayesha Wee 16:15 Add Comment Edit

I have seen that some forums developers ask above question often. As delivered by PeopleSoft there are no way (not that I know of) we can add attachments with file name longer than 64 characters. This is a limitation which is coming from AddAttachement peoplesoft built-in function. If you try to attach file with a name longer than 64 characters then you will get a this particular error;
File transfer failed because name of selected file name is too long. Maximum is 64 characters.

Further, in PeopleBooks that has mentioned as;

Here, UserFileName is the 4th parameter to the AddAttachment function.

How to use MsgGet and MsgGetExplainText in a MessageBox via PeopleCode

Ayesha Wee 20:35 Add Comment Edit

Most often when we want to show a message we use MsgGet function in PeopleCode and use Message Catalog to substitutes the values of the parameters into the text message.

MsgGet(message_set, message_num, default_msg_txt [, paramlist])

where paramlist is an arbitrary-length list of parameters of undetermined (Any) data type to be substituted in the resulting text string, in the form:
param1 [, param2]. . .

For the message text we can pass parameters and The parameters are referenced in the message text using the % character followed by an integer corresponding to the position of the parameter in the paramlist.
But sometimes, message text is not long enough to present the required message you want, and the message text (MESSAGE_TEXT field) is limited to 100 characters.
In this case we want to use more detailed "explain text" in the Message Catalog. This can be achieved using Long Description (DESCRLONG fied in PSMSGCATDEFN record) in Message Catalog.
You can pass parameters to the long description as well. This can be useful if you want to dynamically build your message catalog in PeopleCode. Then you can use MsgGetExplainText function to retrieve the Explain text of a message from the PeopleCode Message Catalog and substitutes the values of the parameters in paramlist into the explain text. It returns the resulting message explain text as a String data type.
Now lets see how to combine these two in a message box;

MessageBox(0, "", 0, 0, MsgGet(12345, 1, "Message not found", &param1) | Char(10) | Char(13) | MsgGetExplainText(12345, 1, "Message not found", &dynamicString), 0);

In the above line of code Char(10) | Char(13) is used for line feed and carriage return. so that the message will show as follows,

You can use MsgGetText instead of MsgGet for constructing the full message.

Message cannot be changed. Message referenced in runtime tables

Ayesha Wee 21:45 Add Comment Edit

FYI, instead of writing whole post of how to fix the problem, I will redirect you to the correct location so that you know next time where to go;

http://peoplesoft.wikidot.com/message-referenced-in-runtime-tables
http://www.compshack.com/peoplesoft/integration-broker/message-cannot-be-changed-message-referenced-runtime-tables-doc-id-660

Another common integration broker error message we commonly encounter is;

Integration Broker: Message Already Used in WSDL

how to fix it is shown in here http://psst0101.digitaleagle.net/2011/10/15/integration-broker-message-already-used-in-wsdl/

How to show a list of restricted file types to the user (based on File Extension List)

Ayesha Wee 22:34 Add Comment Edit

In my previous two posts I mentioned that we can use File Extension List to created (or store) a list of file extensions. This File Extension List can be used to reject or accept list of file types for users when uploading files to the PeopleSoft system. To refresh, please read following Posts if you are not sure what the File Extension List is?
How to restrict File Types (extensions) from URL Object Properties When Adding Attachments Part I
How to restrict FileTypes (extensions) from URL Object Properties When Adding Attachments Part II
Sometimes you want to show a message to users to explicitly say that our PeopleSoft system only accept certain list of file types when you doing file attachments. One of the easiest ways to generate this message is to get the list of file types from the File Extension List records. In this post, I assume that you have already created a File Extension List (PeopleTools > Utilities > Administration > File Extension List) and configure it as a URL properties for the file attachment record.

If all the configurations are done then you can access this file extensions by querying the underlying record PTFX_EXTLST.
You can create a PeopleCode function in which you can pass list Name (e.g. MY_TEST_FILE_EXTENSION) to the function dynamically and build the file list as shown in the following example.

/*this function will build the common file extension list for file upload*/
Function MyFileExtensionList(&ExtListName As string) Returns string
   
   Local string &FileExt;
   Local string &FileList = "";;
   Local SQL &FileList;
   rem get the file extension list from PTFX_EXTLST record (File List);
   &FileList = CreateSQL("SELECT DISTINCT A.PTFX_EXT FROM PS_PTFX_EXTLST A WHERE A.PTFX_EXTLST_NAME = :1 ORDER BY 1", &ExtListName);
   
   /*build the file extension list*/
   While &FileList.Fetch(&FileExt)
      &FileList = &FileList | ", " | &FileExt;
   End-While;
   
   &FileList = LTrim(&FileList, ", ");
   
   Return &FileList;   
End-Function;

This way you can show a message to user if they fail to attach a file which are not in this list

/*check the Return code for file extension validation*/
/*Retrun code 21 - "File transfer failed because the file extension is not allowed."*/
   If &numRetCode = 21 Then
      MessageBox(0, "", 0, 0, MyFileExtensionList("MY_TEST_FILE_EXTENSION"), 0);
   Else
/*all good*/
 End-If;

How to sort Grid Columns using PeopleCode

Ayesha Wee 20:19 1 Comment Edit

PeopleSoft Grids are most common form of data display control and they display fields in spreadsheet like format. Often, we need to provide custom sort for grid columns in various situations.
This can be achieved easily by using Sort Method in Rowset PeopleCode class.
However, there are few things to consider and we will look at it later in this post. First, how do we provide custom Sort in a grid.

Please place the following code in appropriate event to provide custom sorting of a grid;

/* Assuming you grid is in level 1*/
Local Rowset &rsLevel1 = GetLevel0()(1).GetRowset(Scroll.MY_GRID_REC);
&rsLevel1.Sort("MY_GRID_REC.COL1", "A", "MY_GRID_REC.COL2", "A", "MY_GRID_REC.COL3", "D");

Note that MY_GRID_REC.COL1, MY_GRID_REC.COL2, MY_GRID_REC.COL3 are your grid columns that you want to sort and MY_GRID_REC is the primary record of the grid.
"A" specifies ascending order; "D" specifies descending order.

If you are not auto selecting rows to populate in a grid you can achieve the same thing as below;

/*The first example repopulates a rowset in a page programmatically by first flushing its contents, 
selecting new contents using Select, then sorting the rows in ascending order by EXPORT_OBJECT_NAME*/
Function populate_rowset

   &RS1 = GetLevel0()(1).GetRowset(SCROLL.EXPORT_OBJECT);
   &RS1.Flush();
   &RS1.Select(RECORD.EXPORT_OBJECT, "where export_type =:EXPORT_TYPE_VW.EXPORT_TYPE");
   &RS1.Sort(EXPORT_OBJECT_NAME, "A");

End-Function;

Couple of Points to remember

PeopleSoft Application Designer enables the user to personalize a grid at runtime. By default, grid personalization is enabled. If you provide custom sort using PeopleCode and if user personalize the grid at runtime (i.e online) then custom sort will not work afterwards;
You can get back the custom sort you provided, by executing the same PeopleCode. To do that have to place a button on the page and allow user to click it to get back the custom sort on the grid.

How to know Scrum in 10 minutes

Ayesha Wee 00:46 Add Comment Edit

If you are a wondering about knowing of bits and pieces of Agile based development methodologies you may have already heard these buzz words such as Agile, Scrum, Kanban, Burndown charts, Sprints etc.. Then you will be amazed about this little video I used to watch and quickly learn about Scrum concepts a while ago. This video well under 10 min so it won't waste your time at all. If you are keen to know about one of the most popular agile methods known as Scrum under 10 min this video is for you :-)

Software Testing: A Culture of Quality

Ayesha Wee 23:45 Add Comment Edit

One of the leading tenets of Agile software development is to always maintain a shippable product. Doing so allows a steady flow of code from the development team out to the user base. Regular releases out to your customer base gives users the ability to provide feedback on the software to drive future development. Development and test teams that quickly respond to customer feedback are more successful.

Read the full article at: https://www.atlassian.com/software-testing/?tab=strategic-planning

How to find PeopleSoft Project Item (PSPROJECTITEM) Object type list

Ayesha Wee 01:14 Add Comment Edit

When I was searching to find out what are all the distinct OBJECTTYPE values for the PSPROJECTITEM table, I found out there are heaps of excellent posts written in many places. one particular post from https://garrettdieckmann.wordpress.com/2013/12/11/object-types/ has mentioned a table called PS_WHEREUSEDOBJTBL. This table stores the object type in the WHEREUSEDOBJTYPE field. In addition you can find PeopleSoft object types (definition types) in PeopleTools 8.54: Application Designer Developer's Guide - Appendix B.
Altogether there are 120 object types are listed in PeopleTools 8.54: Application Designer Developer's Guide (this is same as PeopleTools 8.53: Application Designer Developer's Guide).

Following SQL Query will show you all the object types in project item table.
Please note this Query is copied from (https://gist.github.com/garrettdieckmann/1978543ed214e05588c8)

select projectname
  , case when objecttype='0' then 'Records'
  when objecttype='1' then 'Indexes'
  when objecttype='2' then 'Fields'
  when objecttype='3' then 'Format definitions'
  when objecttype='4' then 'Translate values'
  when objecttype='5' then 'Pages'
  when objecttype='6' then 'Menus'
  when objecttype='7' then 'Components'
  when objecttype='8' then 'Record PeopleCode'
  when objecttype='9' then 'Menu PeopleCode'
  when objecttype='10' then 'Queries'
  when objecttype='11' then 'Tree structures'
  when objecttype='12' then 'Trees*'
  when objecttype='13' then 'Access groups*'
  when objecttype='14' then 'Colors'
  when objecttype='15' then 'Styles'
  when objecttype='16' then 'Not used'
  when objecttype='17' then 'Business processes'
  when objecttype='18' then 'Activities'
  when objecttype='19' then 'Roles*'
  when objecttype='20' then 'Process definitions'
  when objecttype='21' then 'Process servers'
  when objecttype='22' then 'Process types'
  when objecttype='23' then 'Process jobs'
  when objecttype='24' then 'Process recurrences'
  when objecttype='25' then 'Message catalog entries*'
  when objecttype='26' then 'Dimensions*'
  when objecttype='27' then 'Cube definitions*'
  when objecttype='28' then 'Cube instance definitions*'
  when objecttype='29' then 'Business interlinks'
  when objecttype='30' then 'SQL definitions'
  when objecttype='31' then 'File layout definitions'
  when objecttype='32' then 'Component interfaces'
  when objecttype='33' then 'Application Engine programs'
  when objecttype='34' then 'Application Engine sections'
  when objecttype='35' then 'Message nodes'
  when objecttype='36' then 'Message channels'
  when objecttype='37' then 'Message definitions'
  when objecttype='38' then 'Approval rule sets'
  when objecttype='39' then 'Message PeopleCode'
  when objecttype='40' then 'Subscription PeopleCode'
  when objecttype='41' then 'Not used'
  when objecttype='42' then 'Component interface PeopleCode'
  when objecttype='43' then 'Application engine PeopleCode'
  when objecttype='44' then 'Page PeopleCode'
  when objecttype='45' then 'Page field PeopleCode'
  when objecttype='46' then 'Component PeopleCode'
  when objecttype='47' then 'Component record PeopleCode'
  when objecttype='48' then 'Component record field PeopleCode'
  when objecttype='49' then 'Images'
  when objecttype='50' then 'Style sheets'
  when objecttype='51' then 'HTML'
  when objecttype='52' then 'Not used'
  when objecttype='53' then 'Permission lists'
  when objecttype='54' then 'Portal registry definitions'
  when objecttype='55' then 'Portal registry structures'
  when objecttype='56' then 'URL definitions'
  when objecttype='57' then 'Application Packages'
  when objecttype='58' then 'Application Package PeopleCode'
  when objecttype='59' then 'Portal Registry User homepages'
  when objecttype='60' then 'Problem type definitions'
  when objecttype='61' then 'Archive templates'
  when objecttype='62' then 'XSLT'
  when objecttype='63' then 'Portal Registry User Favorites'
  when objecttype='64' then 'Mobile pages'
  when objecttype='65' then 'Relationships'
  when objecttype='66' then 'Component Interface Property PeopleCode**'
  when objecttype='67' then 'Optimization Models***'
  when objecttype='68' then 'File References***'
  when objecttype='69' then 'File Reference Type Code***'
  when objecttype='70' then 'Archive object definitions***'
  when objecttype='71' then 'Archive Templates (Type 2)***'
  when objecttype='72' then 'Diagnostic Plug-Ins***'
  when objecttype='73' then 'Analytic Models'
  when objecttype='74' then 'Not Used'
  when objecttype='75' then 'Java Portlet User Preferences'
  when objecttype='76' then 'WSRP Remote Producers'
  when objecttype='77' then 'WSRP Remote Portlets'
  when objecttype='78' then 'WSRP Cloned Portlet Handles'
  when objecttype='79' then 'Services'
  when objecttype='80' then 'Service Operations'
  when objecttype='81' then 'Service Operation Handlers'
  when objecttype='82' then 'Service Operation Version'
  when objecttype='83' then 'Service Operation Routings'
  when objecttype='84' then 'IB Queues'
  when objecttype='85' then 'BIP Template Definitions'
  when objecttype='86' then 'BIP Report Definitions'
  when objecttype='87' then 'BIP File Definitions'
  when objecttype='88' then 'BIP Data Source Definitions'
  when objecttype='89' then 'WSDL'
  when objecttype='90' then 'Message Schemas'
  when objecttype='91' then 'Connected Query Definition'
  when objecttype='92' then 'Logical Schemas'
  when objecttype='93' then 'XML Schemas'
  when objecttype='94' then 'Relational Schemas'
  when objecttype='95' then 'Dependency Documents'
  when objecttype='96' then 'Document Schema'
  when objecttype='97' then 'Essbase Cube Dimensions'
  when objecttype='98' then 'Essbase Cube Outlines'
  when objecttype='99' then 'Essbase Cube Connections'
  when objecttype='100' then 'Essbase Cube Templates'
  when objecttype='101' then 'Delimited Schemas'
  when objecttype='102' then 'Positional Schemas'
  when objecttype='103' then 'Application Data Set Definitions'
  when objecttype='104' then 'Test Definitions'
  when objecttype='105' then 'Test Framework Test Cases'
  when objecttype='106' then 'Application Data Set Bindings'
  when objecttype='107' then 'Feed Definitions'
  when objecttype='108' then 'Feed Categories'
  when objecttype='109' then 'Feed Data Type'
  when objecttype='110' then 'JSON Documents'
  when objecttype='111' then 'Related Content Definition'
  when objecttype='112' then 'Related Content Services'
  when objecttype='113' then 'Related Content Configurations'
  when objecttype='114' then 'Related Content Layouts'
  when objecttype='115' then 'Search Attributes'
  when objecttype='116' then 'Search Definitions'
  when objecttype='117' then 'Search Categories'
  when objecttype='118' then 'Search Contexts'
  when objecttype='119' then 'Integration Groups'
 else 'missing type' end object_type
  , objecttype
  , objectvalue1
  , objectvalue2
  , objectvalue3
  , objectvalue4
  from psprojectitem 
  where projectname = '&project_name';

How to use Message Catalog pop-up in PeopleSoft Pages

Ayesha Wee 02:59 2 Comments Edit

Popups are common in web pages and they are handy to display additional information about a field or information for a subset of users. For example, you can create a popup to display information about a types of files which are accepting for a file upload process. Another example would be to show a list of restricted characters for a particular field (a password field). In PeopleSoft we can create a special type of page called a pop-up page which are bit different from standard PeopleSoft pages. There are certain restriction when creating pop-up pages. For instance, Pop-up pages support only a subset of page controls (HTML areas, for example cannot add to a Pop-up page).

However, for this post we will look at how to get popup working from message catalog. In this case you do not need to create a Pop-up page.

Enabling Message Catalog Pop-ups

To enable message catalog pop-ups:

1. Insert a Push button/Hyperlink to the page you want (you can associate pop-up with any other page control which support pop-ups).

2. Double click and access a Push button/Hyperlink properties

3. In Type tab Associate your Push button/Hyperlink to a Destination (e.g PeopleCode, Page Anchor), Record and a field.

4. In label Tab configure properties as you want, example is shown below from PeopleSoft delivered User Profile Page.

5. In use tab, Mouse Over Popup section select Message catalog Popup and set Message Set and Number.

6. When you follow above basic setup, then you can see the message catalog popup on the page when you Click or Hover the link (or image).

(Note: The text in the Message Text field appears as the pop-up page header. The text in the

Explanation field appears in the pop-up page body.)

How to Know Your PeopleSoft PeopleTools Version (for Beginners)

Ayesha Wee 02:37 Add Comment Edit

There are few ways you can check PeopleTools version of your PeopleSoft Application. In this post we will look at most common ways to check the PeopleTools version.

Method 1: Via App Designer

You can check PeopleTools version using app designer, by Clicking Help > About PeopleTools...

Method 2: Pressing (Ctrl + J) or (Ctrl + Shift + J) Key combination from brower

The famous (Ctrl + J) or (Ctrl + Shift + J) Key combination will work for most browsers such as Chrome, FireFox and Internet Explorer

Method 3: Installing Browser Plugging (PSChrome) for Chrome and FireFox.

PSChrome is a Chrome and FireFox Plugin (or Extension) which

offers some basic features for use by developers and users enabling a simplified user experience in PeopleSoft environments. It is really handy for Analysts and Beginners for PeopleSoft where you can find information about Components, Pages, Field values etc.. Once installed, it shows you a Toolbar at the top of the page which you can drill down and find many useful information.

PS: When you upgrade Firefox to version 36.0.1 PSChrome for Firefox will not work.

Method 4: Using PSPCMPROG record

Last but not least, you can query the delivered record PSPCMPROG record which store all PeopleCode programs. Simply issue this Query;

SELECT PTTOOLSREL FROM PSPCMPROG;

How to Compile your PeopleCode Program

Ayesha Wee 03:45 Add Comment Edit

In PeopleTools App Designer there is an option to compile all PeopleCode programs which are stored in the database or in a project to check for errors. When you do that it compile every PeopleCode program. Apart from finding errors in the program, one advantage of compiling the program through App Designer is after an upgrade to verify that all the programs were upgraded correctly.

To access this option in App Designer, go to Tools menu and select Compile All PeopleCode option.

To compile all PeopleCode programs

Open Application Designer while accessing the database that contains the PeopleCode that you want to check, then Select Tools, Compile All PeopleCode. Select which PeopleCode to compile. Click Compile in the Compile All PeopleCode dialog box. If there are any errors they will appear in the PeopleCode log display window.

Ayesha Wee 19:57 Add Comment Edit

Some of the PeopleSoft PeopleTools Queries that someone might find useful;

How to find database tables (i.e SQL records) attached to a page?

SELECT A.LBLTEXT, A.RECNAME, A.FIELDNAME, A.PNLNAME
FROM PSPNLFIELD A
WHERE A,PNLNAME = :1 --Page Name
AND A.RECNAME NOT LIKE '%XLAT%'
AND A.RECNAME NOT LIKE '%WRK%'
AND A.RECNAME NOT LIKE '%DERIVED%'
AND A.FIELDNAME <> ' '
ORDER BY 2;

How to find objectownerid of a record definition?

SELECT A.RECNAME, A.RECDESCR, A.OBJECTOWNERID, A.DESCRLONG
FROM PSRECDEFN A
WHERE A.RECNAME = :1 --Record Name

How to find list of record definitions (tables) the belong to a particular object owner ?

SELECT A.RECNAME, A.RECDESCR, A.OBJECTOWNERID, A.DESCRLONG
FROM PSRECDEFN A
WHERE A.OBJECTOWNERID= :1 --Object owner id

How to find PS Queries that select data from specific table

SELECT A.OPRID, A.QRYNAME, A.RECNAME, A.FIELDNAME
FROM PSQRYFIELD A
WHERE A.RECNAME LIKE :1 -- Record Name

How to find PS Queries that use specific field

SELECT A.OPRID, A.QRYNAME, A.RECNAME, A.FIELDNAME
FROM PSQRYFIELD A
WHERE A.FIELDNAME LIKE :1 -- Field Name

How to find Queries that were not delivered by PeopleSoft

SELECT A.OPRID, A.QRYNAME, A.DESCR
FROM PSQRYDEFN A
WHERE A.LASTUPDOPRID <> 'PPLSOFT'

How to restrict FileTypes (extensions) from URL Object Properties When Adding Attachments Part II

Ayesha Wee 20:01 Add Comment Edit

So, as I mentioned in How to restrict File Types (extensions) from URL Object Properties When Adding Attachments Part I post, I have tested this particular configuration when adding attachments.
The good news is it worked :-)
So, how did it exactly work?
Well, assuming that you have configured a File Extension list and added a list of accepted or rejected file types to that list as per my previous post. Once done you are ready to go and check whether it is working.
As per AddAttachment PeopleCode function, once the file is uploaded you can check the return code which is either an integer or a constant value. Then you can check the return code "21" which is to check the File Extension from the File Extension list that you have configured.

So basically you can try out a code similar to,

/*check the Return code for file extension validation*/
/*Retrun code 21 - "File transfer failed because the file extension is not allowed*/
&RETCODE = AddAttachment("URL.MYTESTRECORD", &ATTACHSYSFILENAME, "", &ATTACHUSERFILE, 0);
   If &RETCODE = 21 Then
      Error MsgGet(12345, 5, "File transfer failed because the file extension is not allowed");
Else
/*Do something*/
End-if;

Subscribe to: Posts ( Atom )