Saturday, November 29, 2014

Setting up Approval Process in PeopleSoft Approval Workflow Engine (AWE)

Being part of an organization which houses its HRIS system in PeopleSoft, we as an employee do various  Self Service transactions like submit a planned leave request for a week or so which then goes to manager for their approval and upon final approval the database changes are done eventually. Let's talk about a typical transfer process which involves certain steps. Assume a manager raises a transfer request for one of their direct reports. The request is routed through a series of approval involving following stakeholders:
  1. N+2 (Requester's Manager)
  2. N+3 (Manager's Manger)
  3. HR Head
The transfer request goes through following steps:
  1. Manager raises the request for one of it's direct reports using Transfer Employee component (MSS > Job and Personal Information > Transfer Employee).
  2. The component Transfer Employee checks whether approval is required for this transaction from the page Workflow Configuration (Setup HRMS > Common Definition > Self Service > Workflow Configuration) then checks what approval process is attached with this transaction from the page Workflow Transactions (Setup HRMS > Common Definitions > Approvals > Workflow Transactions) If the approval is required then it submits the transaction to AWE.
  3. AWE identifies the approval process definition passed by transaction component, reviews the process setup from Approval Process Setup page (Setup HRMS > Common Definitions > Approvals > Approval Process Setup) to determine the next approver in the approval chain.
  4. AWE then checks whether email notifications have been configured for this approval process from the page Transaction Configuration (Setup HRMS > Common Definitions > Approvals > Transaction Configuration)  and then determines what should be the mode of communication (Email or Worklist or Both) to the next approver. It then notifies next approver accordingly and updates the Header Record (HR_TRANSFER_DAT) and Cross Reference Table (HR_TRANSFER_XREF) through the 'Approval Event Handler Class' all mentioned in the Transaction Registry Page (Setup HRMS > Common Definitions > Approvals > Transaction Registry).
  5. Approver approves the transfer request which came from previous steps, through the page Approve Transfers (MSS > Job and Personal Information > Approve Transfer) upon which AWE again updates all the records mentioned in the step 4.
  6. Step 4 and Step 5 are repeated until request has been approved by all the approvers.
  7. Once the request is approved by all the approvers , HR Admin opens the page Employee Transfer Requests (Workforce Administration > Self Service Transactions > Employee Transfer Requests), select appropriate option and click on 'Save' which ultimately updates the database i.e adds a transfer row in Job Data component.

Things are quite clear so far but one question arises here, What is this Approval Workflow Engine (AWE) that I just mentioned about in the above transfer business process ?
As it's name suggests, Its an engine which drives an approval process of different kinds. It provides capabilities creating, running and managing approval processes.

Well, as I always say the best way to learn something in PeopleSoft is to do a hands on. So lets get into some real business and see how exactly an approval process is setup with AWE in PeopleSOft. The requirement is to create an approval process which we saw in the beginning in AWE. This process will include following roles in the approval chain:
  1. N+2 (Originator's Manager where originator itself is a manager)
  2. N+3 (Manager's Manager)
  3. HR Head
Most of us who are not much familiar with AWE might think that doing the setup and configuration in AWE is all it takes to implement an entire approval process in PeopleSoft but let me tell you there are whole lot of other things that has to be done before we could even touch the AWE setup part. Having said that, following are the three major parts in this entire implementation:

The elements highlighted in red in above diagrams are the ones we actually need to create however, their names have been given from the perspective of a transfer process that we are going to setup. But you can give your own name which suits your needs best.

In the following articles I will explain each of these steps to setup a transfer approval process.

Wednesday, November 26, 2014

How to use PeopleSoft ExceltoCI Utility

Before reading this article you must understand the basics of component interface Understanding Componenet Interface in PeopleSoft

When a business is run through PeopleSoft or in any other enterprise application for that matter and if you are providing PeopleSoft support to that business, you get endless requirements of different kind as part of your work. I would like to bring up an example of the two requirements to upload the data in PeopleSoft system.
  1. The first requirement is to upload the fringe benefit tax information sent by third party system into PeopleSoft on daily basis.
  2. The second requirement is to upload 200 promotions in the system through Job Data component.
How do you think these requirements can be catered to. Well, whenever we hear upload word in PeopleSoft the first think which comes in mind is Component Interface (CI) but can both of the above requirements be catered through CI ?

The answer would be yes because after all its about upload and with the CI being so powerful tool there is no deny to it but if you observe closely, there is a basic difference between these two requirements and that is - the first one is a daily upload activity but the second one is a one time upload activity. Hence, its quite obvious that for first requirement we will be creating the CI of the target component where Fringe Benefit Tax information is gonna be stored and call this CI from a new Application Engine program which can be scheduled as a process on daily basis.
But what about second one ? should we really create an entire Application Engine which would work in conjunction with CI when its a one time exercise ?
I would simply say No ..! we should rather be using Excel to CI (ExceltoCI) utility to do the same however, in this case also we need to create CI for target component.

PeopleSoft Excel to CI or ExceltoCI is a Microsoft excel based application which is used to upload the data from Microsoft Excel workbook to PeopleSoft system. So basically, Excel to CI tool will take the data as input in excel format and pass it to CI which will ultimately load the data into database through underlying component. 

There are four simple steps to be followed in order to configure the ExceltoCI (Excel to CI) tool and then load the data through it.

Before we begin, first we need to get this utility either from someone who already have it or from the PeopleSoft directory <PS_HOME>/excel. Once you have it with you, just open it. You will see 5 tabs in this excel as shown below and it also represents 4 step process in ExceltoCI utility:

As mentioned earlier, its a four step process:

  1. Connection
  2. Template
  3. Data Input
  4. Staging and Submission
Lets follow above mentioned steps and see how exactly this utility works.


First we need to establish connection between ExceltoCI utility and the desired component interface (CI) and to begin with, first open the target PeopleSoft instance into which you wish to upload the data. Let's assume that instance name is HCMDEV so open it in the web browser and copy different element of the URL displayed in the web browser. We will be using these elements to establish connection between ExceltoCI and  the CI. Go to the 'Connection Information' tab at the bottom of the ExceltoCI utility and paste copied elements as shown below:

Its quite clear with the highlighted colors that which element from URL needs to be copied where in the Connection Information page.  There are couple of things I would like to point out here:
If the protocol is https then the HTTP Port would be 443 but if the protocol is http then HTTP Port will be 80. The field Action dictates whether a new row will be created through the component or an existing one will be updated similar to what we do in correct history mode.
Leave rest of the fields as shown above.


This is the step where we select the CI through which the data is going to be uploaded. Click on the tab Template as below:

Enter the User ID, Password  and the CI name (For example 'CI_JOB_DATA') as shown above and then click on OK. It will open the CI definition:

Our goal here is to select all the required fields from the component interface which are mandatory in order for the transaction to be saved. For example, if we are intending to add promotion rows for employees then we will be requiring EMPLID, EMPL_RCD, EFFDT, EFFSEQ, ACTION, ACTION_REASON, POSITION_NBR etc...  The field selection here will be based on what are all the fields need to be filled in when promotion row is added through Job Data component in PIA. Highlight the field you want to select and then click on the option 'Select Input Cell' at the top. Do the same for all the fields and finally click on the option 'New Data Input' available on the top which will open the Data Input page.

Data Input

All the selected fields have been displayed in this page and since they might be at different levels in the component hence they are shown in different colors as shown above. 
Till now, we established connection, created template for the CI and selected the input fields. Now we are all set to fill in these selected fields with input data that we are planning to upload into the database. 
Add all the input data rows in excel form and click on the option 'Stage Data for Submission' available on the top which will open the next page.

Staging and Submission

In this page you will also see errors and warnings generated by the underlying component. All the errors needs to be fixed before the data can be submitted further but the warnings can be ignored. Once all the errors are fixed then click on the option 'Submit' displayed on the top which will finally load the data into database.

You can gain in-depth knowledge on CI with a live example by just paying below amount

I have a complete session in two parts which explains:

  1. How CI works - Overview, all the elements of CI etc..
  2. Creating an Inbound interface, that loads data on a multi level component using CI and App Engine
Below are the links to videos in YouTube.

CI Part 1
CI Part 2

Click here to know how it works

However, if you want to save money by purchasing whole module instead of in parts then visit this page to get more details PeopleSoft Functional and technical online training

Saturday, November 8, 2014

Understanding PeopleSoft Group IDs

How would you create Performance Document for the employees directly reporting to CEO ? How would you process a special bonus plan for those employees who have been rated as 2 during the annual performance evaluation process ?
Well, one way is to create an SQL query which will give list of such employees and then process each employee one by one. It's certainly gonna be  a long time taking process if such employees are higher in number. So what should we do then ?

If somehow, this SQL can be run dynamically on its own and pass in all the employee IDs which it returns, to the performance document creation process to create the performance document or to variable compensation application to process the special bonus for these employees then our problem will be solved and that's where Groups come in picture.

We create a group and define the population that belongs to this group. The population for a group is defined with the set of criteria with the respective records and their fields. We basically create a dynamic SQL but in functional way. So let's create a group which will contain the employees directly reporting to CEO.

Creating a Group in PeopleSoft involves following steps:
  1. Select Records, Fields and Values that will define the population for the group
  2. Define e Group
  3. Setup Group Security

There are various setup pages which we use to define Groups and we do the required setup and configuration through each of these pages in sequence:

Group Build Records and Fields

This page is available here: Main > Setup HRMS > Common Definitions > Group Build > Group Build Records and Fields Page.
As I mentioned earlier defining a group is nothing more than creating a dynamic SQL which requires records and fields to be used in WHERE clause of the SQL. Hence, we first need to identify the records and their fields that will be needed in the SQL to fetch the employees directly reporting to CEO and then we need to add them through this page.  Only the records and fields added here will be available to be used as criteria in Group Definition.
So in our case the only record we will be requiring is JOB and the fields will be - HR_STATUS, REG_REGION, REPORTS_TO, EFFDT, EFF_SEQ, PER_ORG

Group Definition - Group Profile

Main > Setup HRMS > Common Definition > Group Build > Group Build, Group Definition
This is the page where we actually define the group. We just add set of criteria in the group definition and these criteria fetch the population at run time.  One important thing to note here is that instead of adding criteria, a PS Query can also be added into the Group definition in which case the setup which we just did in the page 'Group Build Records and Fields' will not be required.
Following will be the criteria under WHERE clause that we need to add:

EFFDT = (Max EFFDT Criteria)
REG_REGION = <Region>
REPORTS_TO = <CEO Position Number>

I would show you how these criteria should be added with group definition through below screenshot:

Just match the criteria added through this page not all though, as shown in the above screenshot against the criteria written in the SQL earlier in the article. The items highlighted in Red are the logical operators we use in WHERE clause, the items highlighted in Green are the fields used in the WHERE clause, the items highlighted in blue are the field values for which employees are fetched from database.

Setting Up Group Security

Because you can group people together in any manner that suits your needs, including across companies or departments, groups have their own security structure that is separate from, and overrides, data permission security. For example, a user who does not normally have access to department 10100, but who has access to a group that includes people in department 10100, can see all group members, even those who belong to that department. This makes security factors an important consideration when you set up groups.

Following are the pages used for setting up Group Security:

Group Security Default:  Set Up HCM>Common Definitions>Group Build>Group Security Default
Specify which components in your system can use or refer to groups that are created in the Group Build component

Security By Group:  Set Up HCM>Common Definitions>Group Build>Security By Group
Specify the users who have security access to the selected group ID and the components that the user can access for the group

Security By Operator:  Set Up HCM>Common Definitions>Group Build>Security By Operator
Set up security by user. Specify the groups that the selected user can access and the components that the user can access for the group.  

Monday, November 3, 2014

Removing Auto Generated Row Level Security Criteria from PS Query

We have already seen how a PS Query is created through PIA in PeopleSoft in the following article - Creating Query Report with PS Query in PeopleSoft.
When we create a PS query which involves those records that has employee ID field then we see System automatically adds a row level security criteria in the generated SQL which prevents unauthorized access to the employee data. Ideally speaking, this extra security measure is indeed very significant because otherwise, everybody can access everything through PS Query. However, this additional security measure can sometimes prove to be hurdle in the way and apparently I have dealt with such an hurdle once upon a time.

There was a requirement where we had to create a ePerformance report that would generate list of employees who have gone in an assignment to UK from India and the performance document has been created for them. Well, so far there wasn't any issue though until we realized that the report should be accessible to India HR only.We got down to work and created such a report using PS Query but when we ran it as HR it didn't fetch any employee.

We were surprised to see that and were very curious to know what went wrong. We then opened the PS Query definition through Query Manager and checked the generated SQL:
The highlighted area above is the real culprit which is preventing the HR to see those employee's data who went to UK for assignments. Let me put it in another way -
The HR is of the region IND and the employee they are trying to fetch the details are now of the region GBR because they have gone to UK for assignment and since HR is not authorized to see the cross country information hence system has automatically joined row level security search records PS_EMPLMT_SRCH_QRY and PS_PERALL_SEC_QRY in the query appropriately to prevent the potential unauthorized access and it cannot be deleted from the PS Query.

So what are we gonna do now because we want that the India HR should be able to get the report on UK employees, here is the solution.
  1. Instead of creating a PS Query, first create a view (For Example - EPERF_DATA_VW) from the same records and the same SQL you have used in the PS Query that will fetch the desired employees.
  2. then create the PS Query (EPERF_REPORT) from this View EPERF_DATA_VW.
  3. Finally create the report from the PS Query EPERF_REPORT.
Check if the issue has been resolved.