The views expressed on this blog are my own and do not necessarily reflect the views of Oracle.

Saturday, 23 March 2013

Database as storage for attachments

For those who want a secure and fast method to store attachment without creating an ftp server, db attachments will be a good option.

PeopleSoft allows you to store your attachments in database in a much efficient and faster manner. For those who are not exposed to db attachments, I can tell you that this is the easiest method you can try out for storing attachments. The advantage includes, the attachment will reside inside your database so when you are making a production copy of database for your support/testing team, you can retain the attachment without providing access to the ftp server or appserver file storage. The main disadvantage obliviously includes larger database size.

Let me explain how to create a db as file storage. First you need to create a record to store the attachments. When you create the record make sure that you include the sub record FILE_ATTDET_SBR. Make the record as db table type. You should never ever modify the FLIE_ATTDET_SBR. Now I can say you are almost done with the db storage creation. Now you only need to use the record as storage.


You can use it by providing the URL as record://RECORD_NAME where RECORD_NAME is the name of the record you have created earlier. With this you can do all the attachment functions, to store/retrieve/delete/copy attachments to/from database. PeopleSoft will internally take care of doing the attachments.

/* The new record created is MY_ATTACHMENTS */
&retcode = AddAttachment(“record://MY_ATTACHMENTS”, ATTACHSYSFILENAME, "", ATTACHUSERFILE, 0);


/* The URL MYFTP is configured with the URL record://MY_ATTACHMENTS */

To re-iterate, the only two steps you need to do is creating a record with the delivered sub record and specifying the URL as mentioned above, where ever you 

Bulk Insert in PeopleSoft

When you have applications or process that inserts large number of rows into database tables, you might have noticed a lag in the performance of the application\process. Apart from the normal db insert time, the major time consuming part is the presenting of data to the database (database trips).

PeoplesSoft offers some mechanism to further fine tune the performance in case of bulk inserts. For this PeopleSoft has introduced a method called Bulk Mode/Bulk Insert.

If you are using Peoplecode to insert the data, then you can use the BulkMode property of the SQL class. To use this you need to create a sql statement and set the BulkMode property as true. Now each time you use the execute statement, PeopleSoft is not going to present the data to the database. Instead, it will cache the data in the buffer until the sql is closed or the buffer is full. Now peoplesoft will present the entire data to the database in single stretch, thus reducing the data base trips and thereby increasing the performance of the application.

But not all databases supports bulk inserts, major db’s like Oracle, DB2 & MS SQL Server will support bulk inserts. Incase if your db will not support bulk insert, then PeopleSoft will ignore this property and processing will happen as normal (with each trip to db with each execute statement).

Below is one of the example code piece from PeopleBooks demonstrating the usage of BulkMode.
Local SQL &SQL;
Local array of Record &RECS;

/* Set up the array of records.  */
   .  .  .
   /* Create the SQL object open on an insert */
   /* statement, and unbound.*/
   &SQL = CreateSQL("%Insert(:1)");
   /* Try for bulk mode.  */
   &SQL.BulkMode = True;
   /* While the array has something in it… */
   While &RECS.Len
      /* Insert the first record of the array, */
      /* and remove it from the array.  */
      If not &SQL.Execute(&RECS.Shift) then
         /* A duplicate record found, possibly */
         /* in bulk mode.  There is no way to  */
         /* tell which record had the problem. */
         /* One approach to recovery is to fail*/
         /* the transaction and retry it with a*/
         /* process that does only one record  */
         /* at a time, that is, doesn’t use    */
         /* bulk mode.*/
         .  .  .;
/* If buffer is not filled yet, db insert will happen at the below step. */

Now we have seen how to handle bulk inserts in applications using peoplecode. Now if you are running an application engine process to insert data, how can you handle bulk inserts?

PeopleSoft has provided a solution for the same as well. There is a property in the application engine SQL action called “Reuse Statement”. You should select the property to “Bulk Insert” in case your sql action is going to insert huge number of rows. This will start buffering data and presenting it as a single set as we have seen in the BulkMode property of the SQL object.

Monday, 18 March 2013

PeopleSoft 9.2 General Availability

Oracle has announced the release of much awaited major version of PeopleSoft 9.2. PeopleSoft 9.2 will be generally available from Friday, March 22 2013.

The new release includes more than 1000 features, functions and enhancements. The released 9.2 is accompanied by new features with PeopleTools 8.53.

To have a detailed look on the release proposition make sure to visit the below link.

Saturday, 16 March 2013

Changing Styles

Are you bored of using with the same set of styles and colors every day? You can do a lot in peoplesoft to completely revamp the appearance of the application. You can change the style of the entire application, create new styles and change the styles of the page elements dynamically through peoplecode.

I will try to throw some light on it so that you will get a point to start with.

It will be worth of writing two sentences about style sheets. Style sheets are basically a set which stores different style classes. Each style class will have its own property such as font, color, size, back ground color and so on. Once you create a style sheet, you can attach the styles to any number of objects so that the style is applied to the object you attached with.

By default Peoplesoft will deliver a few styles along with the application. To change the entire theme or styles of the application navigate to the link: People Tools > Utilities > Administration > People Tools Options
Here you can change the styles by changing the value in the field “Style Sheet Name”. Once you log out and log in back, you can see the change. Sometimes you may need to clear the web server and browser cache to see the changes work properly.

To create your own styles, open the application designer and click on File > New; and select Style Sheet. Now to simplify your life, select standard style sheet. If you know css then you can select the other options also.
From the free form object double click the parent node and double click again on the object to see the final list of object like edit box hyper link etc… Now to define your style, double click on the object and make the changes similar to what you do in old MS Word. Save it and you are done with your style.

Now I will talk about one more section to let you know how to dynamically change the style of an object by people code. The PeopleSoft Field objects have an associated property with it called style. You can use this property to set the style of the object.

&fField = GetRectrd(Record.MY_REC).GetField(Field.MY_FLD);
/* Seeting the style to editbox error */
&fField.Style = “PSERROR”;

The style you mention must be defined inside the style sheet you selected for the page. The default styles for any object can be found in the object properties box (Alt+Enter).

I will use the most frequently quoted example to set the style dynamically.

&fField = GetRectrd(Record.MY_REC).GetField(Field.MY_FLD);

/* Check if value is there */
If None(&fField.Value) Then

/* If no value then highlight the field in red and throw error. */
&fField.Style = “PSERROR”;
Error “Value is required for the field …”;

/* Change back the field to normal editbox style */
&fField.Style = “PSEDITBOX”;

Saturday, 9 March 2013

Build Sequence

This post is for the developers who will be engaged in created brand new applications or features. You will be creating many new records as part of the new application. The records will obviously contain many views. There is a hidden possibility of error in such situations which will be unlocked only when you migrate the project from development to testing or production environments.

When you migrate and do the build project option, often the build will error out stating table not defined in the database. The major reason for this issue is multiple level of views created. Say you have created  5 tables and 5 views as below.

Tables : TBL1, TBL2, TBL3, TBL4, TBL5


VW1 = select … from TBL1,TBL2 where …

VW2 = select … from VW1,TBL3 where …

VW3 = select … from VW2,TBL4 where …

VW4 = select … from VW5,VW3 where …

VW5 = select … from VW3,TBL5 where …

When you build the project, the views might be built in random order. So if your VW4 is built before VW5, then the system will throw error as there is no VW5 in the database at that time. There is nothing wrong with your coding, sql or project. But just because of the building order of the view the build process will get error out.

To overcome this situation PeopleSoft has provided an additional option in the record properties. If you open record properties and visit “Record Type” tab then you can see an option called “Build Sequence No:”. This property is to decide when to build the view if you use build project option.

This is how it works. When you do a build project action, the application designer will search for all the views with build sequence 1 and then build them. After this is completed, all the views with build sequence 2 is built and so on.

So when you are developing applications which have multiple level of views, make use of the build sequence property and give the correct sequence number so that you won’t face any issue while project migration.

Working with Files and Attachments in PeopleSoft

When customizing PeopleSoft applications to meet the business requirements, you will frequently encounter situations where you need to handle files. Since peoplesoft is a database driven application and peoplecode is limited to application level coding, you might not find enough objects to handle files.

However peoplesoft has delivered some built in functions to handle files and attachments. I will give a glimpse on the usage of these functions. For detailed explanations you can visit the peoplebooks hosted by oracle.

Uploading File from end user machine to server

This is a frequent requirement when you are working with recruitment of staffing systems. You need to upload the resume files into the system.

PeopleSoft has delivered a built in function called AddAttachment() to handle this scenario.

Syntax: AddAttachment(URLDestination, DirAndFilePrefix, FileType, UserFileName[, MaxSize [, PreserveCase[, UploadPageTitle[, AllowLargeChunks]]]])


Th URL Destination will be the parameter which specifies the path in the server where you want to store the file. It can be a DB record, FTP URL, or app server URL. For detailed explanation you can refer peoplebooks.

Copying File from Storage to App Server

Sometimes you might need to copy a file from its storage location to app server to do the temporary processing. For example, if the file is stored in DB server, you cannot send the file as attachment using SendMail function. For that first you need to copy file from DB server to App Server and then send the mail. To deal with such situations, you can use the GetAttachment() function.

GetAttachment(URLSource, DirAndSysFileName, DirAndLocalFileName[, LocalDirEnvVar[, PreserveCase]])

&retcode = GetAttachment("",
"NewHire/11042000resume.txt", "c:\NewHires\resume.txt");

The first two parameters combined make the full path of the source file. The third parameter can be the full path or relative path where the file should be saved. Optionally you can provide the fourth parameter where the specific folder is there in the environment variables. Ex: TMP environment variable can be used.

Copying file from App Server to Storage

This is the reverse of the other. If you want to copy a file from the application server file location to file storage such as ftp or db server, then you can use the function PutAttachment().

PutAttachment(URLDestination, DirAndSysFileName, DirAndLocalFileName[, LocalDirEnvVar[, PreserveCase[, AllowLargeChunks]]])


&retcode = PutAttachment(&FTPINFO, &TARGETFILENAME, "resume.doc");

Copying full files from one location to another

If you have some specific requirement such as copying entire files from one location to another such as after processing a process all the output files needs to be copied to another storage location, then peoplesoft has delivered a function called CopyAttachments().


CopyAttachments(URLSource, URLDestination [, FileRefRecords [, PreserveCase[, AllowLargeChunks]]])

&retcode = CopyAttachments(URL.UrlID, ftp://user:passwd@ftpaddress/");

The first parameter should pass the path for the source location and the second parameter should be path to where all the files should be copied.

Opening a file and viewing it from the browser

You might have attached the files to an application component and sometimes encounter a situation where this file should be opened and displayed from the browser. For example you might have uploaded the resume using AddAttachment function. Now when the recruiter open the profile he will see the attachment link and want to open the resume file. For such scenarios we can use ViewAttachment() function.

ViewAttachment(URLSource, DirAndSysFileName, UserFileName [, NewWindow[, PreserveCase]])

Checking if a file path and filename is correct

Before opening or acting on a file it is necessary to find out that the file name and path are correct. Otherwise the system will throw error during run time if the path or filename is incorrect. To check this PeopleSoft has delivered a function called FileExists().

FileExists(filename [, pathtype])

If FileExists("c:\work\item.txt", %FilePath_Absolute) Then
   &MYFILE = GetFile("c:\work\item.txt", "A");
   /* Process the file */

First parameter will be filename with full path or with relative path. Second parameter will specify if you have specified full path or relative path in the first parameter.

Deleting a file from the storage location

This is also a frequent scenario where you might want to delete a file after temporary processing. Sometimes you might want to delete existing attachment and add a new one. For all these purposes peoplesoft has delivered a built in function called DeleteAttachment(). Before using this function, you might want to check if the file exists using the function mentioned above.

DeleteAttachment(URLSource, DirAndSysFileName[, PreserveCase])

&retcode = DeleteAttachment(URL.BKFTP, ATTACHSYSFILENAME);

There are even other functions to deal with files and attachments. What I have provided above is the basic scenarios which you come across frequently. For more functions, you may refer people books.

For getting a real example of how to use these functions, you can take a look at the peoplecode delivered by peoplesoft under the work record field events in the record FILE_ATTACH_WRK.

For details on creating db record as attachment storage destination, check out the post .

Friday, 8 March 2013

Creating excel file from PeopleSoft

Recently I have seen a piece of code over internet which is intended to create excel file from peoplesoft in a much easier way. I thought it is worth sharing it over here.

Saturday, 2 March 2013

Maintaining Trees

Often there are chances that your tree goes out of sync with the physical objects as the tree ages. This can happen due to deletion of a record from the application designer but not updating the same in the query tree. Also bad sql’s on Meta tables can also the tree in a bad shape.

If you are to find and repair these errors in the tree manually, then it is going to be a nightmare for the maintenance team.

Although not used by many, Peoplesoft has delivered a page solely for the purpose of repairing the trees. This page has variety options to repair various kinds of damages to the tree. You need to select the option and run the process so that the tree is repaired in the background.

The navigation to this page is : Tree Manager > Tree Utilities > Repair Tree

The various options to repair the tree includes

1.       Tree Audits – Run audit on selected tree.

2.       Correct Level Numbers – Corrects the nodes level number if it is wrong.

3.       Correct Parent node numbers – Corrects the node numbers of parent nodes.

4.       Delete Orphan Tree Objects – Deletes orphan objects from the tree.

5.       Remove Tree Branches – To remove all branches from a tree.

6.       Remove tree reservations – Removes reservations on a tree so that other actions can be performed on it.

7.       Reset Tree node gap – Evenly re-distributes tree node and their node numbers.

               8. Update Tree Table Statistics – Whenever a large dml changes are happened on the tree, run this utility to impro

Friday, 1 March 2013

Searching for Component Navigation

Most of the times when you do impact analysis or finding definition references, you will be ending up at a point where you have the component name but does not know where in the PIA it appears.

To tackle this situation, traditionally people are following a method – executing a sql statement to figure out the path. The sql will be similar to the below one.

SELECT a.portal_name || ' >> ' ||
       e.portal_label || ' >> ' ||
       d.portal_label || ' >> ' ||
       c.portal_label || ' >> ' ||
       b.portal_label|| ' >> ' ||
       a.portal_label AS Navigation
FROM   psprsmdefn a
       left join psprsmdefn b
         ON b.portal_name = a.portal_name
            AND b.portal_objname = a.portal_prntobjname
       left join psprsmdefn c
         ON c.portal_name = b.portal_name
            AND c.portal_objname = b.portal_prntobjname
       left join psprsmdefn d
         ON d.portal_name = c.portal_name
            AND d.portal_objname = c.portal_prntobjname
       left join psprsmdefn e
         ON e.portal_name = d.portal_name
            AND e.portal_objname = d.portal_prntobjname
WHERE  a.portal_reftype = 'C'
       -- Parameter to set: Provide the component name
       AND a.portal_uri_seg2 = '&Component'

Now peopletools has delivered a mechanism whereby you could find the navigation online itself. To use it navigate to : Enterprise Components > Find Object Navigation

All you need to do is enter the component name and click on search. It contains more option as link to directly open the component, search for pages and content references. For those who don’t like the sql method, this will be a greater relief.