How to use DirectReportsUI in your Manager Self Service Component - Part 1

Overview

As PeopleSoft consultant, many a times we design and build Manager Self Service (MSS) components. MSS is that part of the PeopleSoft HCM application, where a manager (someone who has direct reports) can go and perform transactions for their Direct Reports. So in all of our custom MSS components we need to have a solution in place to display a list of direct reports of the manager. Upon selection of one/multiple Direct Reports the manager is usually taken to the custom transaction component where they can perform the tasks for the selected Direct Report(s).

For the first part of the requirement which is common across all MSS components, PeopleSoft delivers the DirectReportsUI application class that encapsulates the Direct Reports "Select Employees" User Interface pages into an easy to use class that can be plugged into any new custom MSS component. Unfortunately instead of using the DirectReportsUI application class many a times we end up reinventing the wheel and design our own solution for displaying the list of direct reports. Ease of use, standard look and feel, comprehensive configuration options and integration with text catalog are some of the compelling reasons to use the DirectReportsUI application class.

In part 2 of this blog we will understand the steps involved in using the DirectReportsUI application class for our custom MSS components. For this illustration we have kept the requirement of the transaction component, where the manager will be transferred on selecting a Direct Reports as very simple. In this case the transaction component consists of only one page which displays current job data of the Direct Report selected by the manager. The search record of the component is PS_INSTALLATION as the component buffer will be populated using PeopleCode written in the Component PostBuild event. We want to highlight how simple it is to use the DirectReportsUI application class.

Contributed by Debasish

How to implement PeopleSoft Simple Person Search in our application?

Overview:

Most of the times while designing and building our custom pages, where we need to provide the users with a prompt to select person (active/inactive, employee/contingent worker/POI etc.) we end up either using some delivered or defining our own prompt table and using it. A more elegant alternative is to use PeopleSoft “Simple Person Search”. 

 Quite a few of the PeopleSoft delivered components use this functionality.  So it will be a good idea to incorporate it in our custom components to give the end users similar user experience which is the key to any of the bolt-on application that we design.

This small configuration component available at (Set Up HRMS, Common Definitions, Person Search Match, Configure Simple Person Search, Configure Simple Person Search) enables users of an application to search for and select a person to process.

Some of the advantages of using this option are

• An application can easily render a UI that prompts the user for partial names and displays a list of person from which the user can select
• Configurable multiple person select option
• Configurable HR Status (active/inactive) option
• Configurable Per Org(Employees/Contingent workers/Person of Interest) option
• Optional drill-down to additional non-sensitive data
• The configuration is keyed by “Object Owner Id” which further provides the flexibility to use different configuration for different components

Example:

• In this example there is a hyperlink in a page as shown below

• On clicking this hyperlink the user sees the below Person Search Page. The search options available are configurable from the setup page.

• The user can use the above search options to search for person. The selection of person from the search result can then be tied back to the page from which Person Search page is launched.

Steps to do it:

• We add our own Object Owner Id value.  To do that we need to add a translate value to the field OBJECTOWNERID from the following navigation:  Main Menu > PeopleTools > Utilities > Administration > Translate Values. In the screenshot below we have created an Object Owner Id value “SOA”

• Next we have to Configure Person Search page for the Object Owner Id value created above by navigating to Main Menu > Setup HCM > Common Definitions > Person Search Match > Configure Simple Person Search. The details of the configuration options can be found in the PeopleBook - Person Search 

• Once we are done with the configuration we can test the Person Search configuration for our Object Owner Id by navigating to Main Menu > Setup HCM > Common Definitions > Person Search Match > Invoke Person Search API

• Technical Design

1. We add HR_PSS_DUMMY_FIELD of DERIVED_PSS record in level 0 of the page. This is a requirement for using the Simple Person Search functionality
2. We need to add another field at suitable level in the page which will act as the hyperlink which the user clicks to launch the Simple Person Search page
3. We need to use” PersonSearchSimpleUI” class, which belongs to Application Package HR_PERSON_SEARCH_SIMPLE
4. Within the field change event we instantiate the above mentioned class
5. We call SelectPeople () method to make this Search Page work. This method returns an array String (Employee IDs)
6. We need to then traverse through the array to get the employees and do the rest of the processing
7. We can also do some more manipulations by using several properties of the class. For example: to restrict selecting multiple employees from the search page we have to make MultiSelect property of the object as False.
8. Find below the Field Change PeopleCode for reference. Please note that the below code is for selecting only one person from the result set and assigning it to the field SOA_RECOG_ID.EMPLID

/********************************************************************************/

/* We have used custom Text Catalog (OwnerID: SOA )for the page formatting & all*/

/*Import the  Application Class*/

import HR_PERSON_SEARCH_SIMPLE:PersonSearchSimpleUI;

/*Declare the Object of the class*/

Global  HR_PERSON_SEARCH_SIMPLE:PersonSearchSimpleUI &PersonSearchSimpleUI;

/*Object instantiation*/

If &PersonSearchSimpleUI <> Null Then

   If &PersonSearchSimpleUI.propObjectOwnerID <> "SOA" Or

         &PersonSearchSimpleUI.propSubID <> " " Then

      &PersonSearchSimpleUI = create HR_PERSON_SEARCH_SIMPLE:PersonSearchSimpleUI("SOA", " ");

   End-If;

Else

   &PersonSearchSimpleUI = create HR_PERSON_SEARCH_SIMPLE:PersonSearchSimpleUI("SOA", " ");

End-If;

/* Accessing Methods declared in the class. This method helps in Selecting the employee from the search page*/

&arrEmplids = &PersonSearchSimpleUI.SelectPeople();

/*Traverse through the loop and read the employee id in the EMPLID field*/

For &j = 1 To &arrEmplids.Len

   If &arrEmplids [&j] = %EmployeeId Then

      Error ("Self Recognition is not permissible");

   Else

     SOA_RECOG_ID.EMPLID.Value = &arrEmplids [&j];

End-If;

End-For;

/*********************************************************************/

Contributed by Sourav Pal

How to design a file based inbound interface or data conversion process in PeopleSoft?

In our professional life as PeopleSoft consultant, many a time we come across requirement for a data conversion process or an inbound interface where we need to read an input file and then load the data into one or more PeopleSoft components. 

In this blog we list the steps of a very simple but working design approach. 

The requirement based on which this design is illustrated is as follows

• Develop a run control page with file attachment functionality, so that end user can attach the input CSV file before running the Inbound Interface
• Develop an Application Engine Program to insert or update data to the department component using component interface.
• The input file can consists of the following data
• New department information
• New effective dated row for an already existing department
• Changed values for non-key fields for an existing department and effective date combination

Please note in real world for Department component we may not even write an upload process and can make use of Excel to CI.

1.	Create a custom run control page with file attachment functionality (custom run control record, page, component & menu), so that user can attach a file and system physically stores the file to a pre-defined location.
2	Develop an application engine program that performs the following steps.
	·         Delete data from custom staging (STG) table. The structure of this SQL Table will be similar to the input file with additional fields like PROCESS, ERROR_FLG and ERROR_NOS ·         Select the attached file from custom run control record and store that to state record ·         Load the data from CSV file to STG table, and update the PROCESS field of the STG table to “N” and ERROR_FLG to "N" ·         Perform SQL Data validation (mostly referential integrity validations) in the Staging table based on the requirement. Mark the rows which have errors by updating the ERROR_FLG to "Y". ·         Read the rows of data from the staging table where ERROR_FLG="N", and execute the CI and insert data to PeopleSoft component. Mark the PROCESS field value for the successfully processed row to "Y". In case of CI errors, mark the ERROR_FLG to "Y" for those rows in the STG table. ·         Generate Reports by reading the data stored in the Staging table where ERROR_FLG = "Y"

Contributed by Milan

Best Practices - Folder Structure in Worksoft Certify

As we can only share / re-use processes within any given Project in Certify, the best option for end to end testing is to have a single Certify Project.

Below is the proposed folder structure for a typical Project:

Folder	Description
1.1 – Integrated Tests	This folder will contain final / approved versions of test scenarios/business flows. In Worksoft, Integrated Tests act as driver scripts that call one or more transactional tests in sequence and pass in global data elements. The folder will be secured so that only Admins can make changes and promote content from Staging. Sub-folders for each functional team will be present to keep the content organized.
1.2 – Transactional Tests	This folder will contain final / approved versions of transactional tests. In Worksoft, transactional tests are the reusable components that make up test scenarios (Integrated Tests). Transactional Tests may also have associated local data inputs. The folder will be secured so that only Admins can make changes and promote content from Staging. Sub-folders for each functional team will be present to keep the content organized.
1.3 – Utilities	Utilities are elements of logic that can be re-used within Integrated Tests or Transactional Tests. The folder will be secured so that only Admins can make changes and promote content from staging.
1.4 – Master Content	As part of the license agreement with Worksoft, we’ll have access to an existing library of SAP test cases in Certify that will be stored in this folder. Content that is useful from this library can be copied into staging in order to build transactional tests / integrated tests. The folder will be secured with the expectation that there will not be a need to make changes to this content.
2.1 – Sandbox	The sandbox area is where most Certify users will be working, as this is the preferred location for integrated tests and transactional tests development. Each user will have their own sub-folder under the Sandbox. In order to promote to the 1.x level, content must first be executed successfully in a user’s sandbox folder. Users will have full access to their sandbox folder and read access to other sandbox folders.

To know more about Worksoft Certify best practices reach us at info@soais.com

Contributed by Gautam

How to design an incremental/delta outbound interface?

In our professional life as PeopleSoft consultant, many a time we come across requirement for an outbound interface where we need to write/push data that had changed between last run and current run to a third party application. 

In this blog we list the steps of a very simple but working design approach. 

The example below assumes a file based integration but same design approach will also work for other integration. 

For this example, the requirement is to pull changed  active employees personal data (EmplId, Empl_Rcd, Name, Address1, Email ID and Phone) and write to a csv file.

In this approach we will use the following PeopleSoft object definitions:

• SQL Table - PS_SOA_PERSDT_NEW - stores the information of current run.
• SQL Table - PS_SOA_PERSDT_OLD - stores the information of previous run
• SQL Table - PS_SOA_PERSDT_INTF - stores the rows of those employees whose data has changed and thus has to be written to the output file.
• SQL Table - PS_SOA_PERSDT_HIST - maintain the history data
• An application Engine Program with SQL and PeopleCode actions
• A File layout

Below is the working flow of the outbound process:

• Truncate PS_SOA_PERSDT_OLD record
• Populate PS_SOA_PERSDT_OLD record from PS_SOA_PERSDT_NEW
• Truncate PS_SOA_PERSDT_NEW record
• Populate PS_SOA_PERSDT_NEW with employee id from PS_JOB where HR_STATUS=A and using the effective dated logic
• Update the required information for employees in PS_SOA_PERSDT_NEW using Correlated update and taking care of null values
• Update Name 
• Update Address 
• Update Email ID
• Update Phone Number
•  Populate the PS_SOA_PERSDT_HIST table with the previous run data present in PS_SOA_PERSDT_INTF table
• Truncate PS_SOA_PERSDT_INTF record
• Find the employee whose information had changed PS_SOA_PERSDT_NEW minus PS_SOA_PERSDT_OLD) and insert it to PS_SOA_PERSDT_INTF
• Using File Layout based on PS_SOA_PERSDT_INTF record, write to a file using standard PeopleCode built-in functions.
• Write Log Report based on the data in the PS_SOA_PERSDT_INTF

Contributed by Milan

How to use ODBC Data Object in Certify?

What is ODBC? (Source: http://support.microsoft.com/kb/110093)

Open Database Connectivity (ODBC) is Microsoft's strategic interface for accessing data in a heterogeneous environment of relational and non - relational database management systems. Based on the Call Level Interface specification of the SQL Access Group, ODBC provides an open, vendor- neutral way of accessing data stored in a variety of proprietary personal computer, minicomputer, and mainframe databases.

ODBC alleviates the need for independent software vendors and corporate developers to learn multiple application programming interfaces. ODBC now provides a universal data access interface. With ODBC, application developers can allow an application to concurrently access, view, and modify data from multiple, diverse databases. ODBC is a core component of Microsoft Windows Open Services Architecture.

To use ODBC you need a data source better known as DSN (data source name)

Data Source Name (Source: http://en.wikipedia.org/wiki/Data_source_name)

This is an ODBC term that refers to the name of the database to which you're connecting. A data source name or DSN is a string that has an associated data structure used to describe a connection to a data source. DSN attributes may include the following, but are not limited to:

1. Name of the data source
2. Location of the data source
3. Name of a driver which can access the data source
4. User ID for data access (if required)
5. User password for data access (if required)

Types of Data Source

There are three type of DSN

1. User DSN
2. System DSN or Machine DSN
3. File DSN

1. User DSN - The most common type of DSN. Stores information about how to connect to a specific data source. It can be used only by the current user on the current machine.
2. System DSN - Like the User DSN, the System DSN stores information about how to connect to a specific data source, but is available to all users on a particular machine. It is local to a computer, rather than dedicated to a user. The system, or any user having privileges, can use a data source set up with a system DSN. This is the preferred DSN type for Data Security.
3. File DSN - A data source stored in a file that can be shared among all users who have the same ODBC drivers installed. These data sources need not be dedicated to a specific user or local or in network to a computer.

How to create a Data Source Name (DSN) in Windows

You can create a DSN for your data source as follows:

Step 1: 
Go to Control Panel. On Windows NT systems, double-click the ODBC Data Sources icon. On Windows 7/XP systems, open the Administrative Tools folder and then double-click the Data Sources (ODBC) icon. On some systems, the icon may appear labeled as ‘32bit ODBC’.

Once you double click on the highlighted icon from the above screen shot, a new window ‘ODBC Data Source Administrator’ will appear.

Step 2: 
Select the User DSN tab and click Add. (You must be logged in as the Administrative User or else you need the required privileges to create a DSN). Refer the screen shot below.

Step 3: 
After pressing on ‘Add’ button a new window ‘Create New Data Source’ will appear. Select your database from Name column on this window and press ‘Finish’ button. Refer the screen shot below.

Step 4: 
Once you select your database and press the ‘Finish’ button a window for creating a DSN will appear. Here you need to enter the below.

1)   Name – your data source name (DSN). It can be any name but better to give some meaningful name. Remember you cannot create a duplicate DSN

2)   Description – some information about your data source (optional).

3)   Server – IP address of your database.

After entering all this information press the ‘Next’ button. Refer the below screen shot.

Step 5: 
Then in the next window select the ‘with SQL Server authentication’ radio button and enter your database credentials. Refer the below screen shot.

Step 6: 
If your credential is correct then pressing ‘Next’ from the above screen shot will take you to the next screen. Make sure that ‘Change the default database’ option is unchecked and Press the ‘Next’ button on this screen and after that press the ‘Finish’ button to complete the setup. Refer the below screen shot.

Step 7: 
After completing the setup i.e. pressing the ‘Finish’ button from the above step, a new screen will appear from where you can test your DSN whether correctly configured or not. If you want to test the DSN you created press the ‘Test Data Source’ button. If everything is fine then you will get a screen with a Test Result which will give you a success message. Once you get this success message press to ‘OK’ button to complete the setup. Refer the below screen shots.

Step 8: 
Now you can see the DSN which you created from Step 4 is showing in the respective area.

ODBC Data Object in Certify

The ODBC Data Object allows you to execute a valid SQL query during a process execution to store a data value from the underlying database and/or validate the value between Certify and database. You can query a single value per process step. For example, you can query the value of a user name in one step and then query the password for that user name in the next step. Your query can be as complex as necessary to get the value you need.

There are two actions available for this object in Certify.

1.      Database Store Action

2.      Database Verify Action

These actions do not write to the database, but reads from the database and then writes the value to a variable in Certify.

Important – before writing your query makes sure that your query should return a single row and a single column as a result. Certify will throw an error if your query returns more than a single row/column as a part of query result.

Database Store Action - This action allows you to execute a valid SQL query during a process execution and stores the output value in a Certify variable.

For example - If you want to know the total number of customers you have in a certain region or the total number of users you have in the system, use the Database Store action.

For the above example query will be like this:

Select Count(*) from <<Table>>;

Now refer the below screen shot how to perform this query in Certify.

Database Verify Action - The action allows you to execute a valid SQL query during a process execution to verify the given value with the database value. This action reads from the database and then compares the value to the specified criteria. You can query a single value per process step. Your query can be as complex as necessary to get the value you need.

For example - You can verify the value of Last Name for the given user and let’s assume that you are getting the last name value for that user from somewhere (application).

Contributed by Gautam

How to create XML Publisher report with XML File as data source and using PeopleCode?

What we are trying to achieve?

o   Create a XMLP report in PDF format with the click of a button on the page. The report will print the Content Item information.

o   Screenshot of a content item of content type ‘COMPETENCY’, with a print button (highlighted in yellow) to the trigger the PeopleCode which generates XMLP report in PDF format.

o   Screenshot of the report output of the content item page after clicking the print button

·       Step-by-Step Guide to develop the above XMLP Report

o   Using Application Designer, create a derived/work record. Include all the fields into the work record which you want to show in the report.

o   Create a file layout, drag and drop the derived/work record you have just created into the file layout structure, change the file layout format to ‘XML’ in the file layout properties

o   This file layout will be used to create, both the sample file (to be used for creating the template) and the XML file which will be used to generate the report using PeopleCode.

o   Write the following PeopleCode in the FieldChange Event of the Print Button. Please note in our page design we have level 0 and level 1

a) Part 1 of the code create XML file using work record and file layout as shown below

b) Part 2 of the code runs the XMLP Report using the class in PeopleSoft delivered Application Package

========== XML File PeopleCode for XMLP report =============

/* Include Application Package – Used for setting output format and process report */

/* Include Application Package – Used for while generating XML file */

import PSXP_RPTDEFNMANAGER:ReportDefn;            

import PSXP_XMLGEN:RowSetDS;

/* Declare variables */

Local PSXP_RPTDEFNMANAGER:ReportDefn &oRptDefn;

Local string &FileNameXML, &FileNameXSD, &sOutDestFormat;

<*

Since the data source is "XML File", we need to first generate a XML file from code before we define Report Definition Id and Template Id. So for the time being comment out this block of code to avoid errors (Report Id may not be created at this point of time) and later on once the XML file is generated and Report Definition is created online, we can un-comment the code.  

*>

/* Assign report definition id */

Rem Local string &ReportId = "DK_CONTENT";

/*Assign template id defined in the report definition */

Rem Local string &TemplateId = "DK_CONTENT_1";

Local Rowset &content_RS;

/* Get PS HOME server directory path */

&sOutputDir = GetEnv("PS_SERVDIR");

  /* set system directory separator */

&sDirSep = "/";

If Substring(&sOutputDir, 1, 1) <> "/" Then

   &sDirSep = "\"

End-If;

  /* provide XML file name  */

&FileName = "SOA_CONTENT_ITEMS.xml";

 /* XML  file directory path with directory separator and xml file name  */

&sOutputFile = &sOutputDir | &sDirSep | &FileName;

/* Get file in write mode */

Local File &FILE = GetFile(&FileName, "W", %FilePath_Absolute);

&FILE.Open(&FileName, "A", "UTF8", %FilePath_Absolute);

If &FILE.IsOpen Then

  

      /* Attach file layout with the file object */

      If &FILE.SetFileLayout(FileLayout.SOA_CONTENT_ITEMS_FL) Then

     

     /* Get level 0 field value  */

     &cat_type = JPM_CAT_ITEMS.JPM_CAT_TYPE.Value;

      &item = JPM_CAT_ITEMS.JPM_CAT_ITEM_ID.Value;

     /* Get reference to level  1 from level 0 to fetch other information  */

      &content_RS = GetLevel0().GetRow(1).GetRowset(Scroll.JPM_CAT_ITEMS);

      /* Get level 1 fields  */

      &effdt = &content_RS.GetRow(1).GetRecord(Record.JPM_CAT_ITEMS).GetField(Field.EFFDT).Value;

      &descr90 = &content_RS.GetRow(1).GetRecord(Record.JPM_CAT_ITEMS).GetField(Field.JPM_DESCR90).Value;

      &rating = &content_RS.GetRow(1).GetRecord(Record.JPM_CAT_ITEMS).GetField(Field.RATING_MODEL).Value;

      &shortdecs = &content_RS.GetRow(1).GetRecord(Record.JPM_CAT_ITEMS).GetField(Field.DESCRSHORT).Value;

      &category = &content_RS.GetRow(1).GetRecord(Record.JPM_CAT_ITEMS).GetField(Field.CM_CATEGORY).Value;

     

     /* create standalone record to capture content item information */

      &content_dtl = CreateRecord(Record.SOA_CON_DTL_WRK);

      &content_dtl.JPM_CAT_TYPE.Value = &cat_type;

      &content_dtl.JPM_CAT_ITEM_ID.Value = &item;

      &content_dtl.EFFDT.Value = &effdt;

      &content_dtl.JPM_DESCR90.Value = &descr90;

      &content_dtl.RATING_MODEL.Value = &rating;

      &content_dtl.DESCRSHORT.Value = &shortdecs;

      &content_dtl.CM_CATEGORY.Value = &category;

      &FILE.WriteRecord(&content_dtl);

      &FILE.WriteLine("</SOA_CON_DTL_WRK>");

  

      &FILE.Close();

   Else

      Error "File layout not set."

   End-If;

Else

   /* error condition:  could not open file - add handling; */

End-If;

<*

Since the data source is "XML File", we need to first generate XML file from code before we define Report Definition Id and pass it to the ProcessReport() function. So for the time being comment out this block of code to avoid errors (Report Id may not be passed at this point of time) and later on once the XML file is generated and report definition is created, we can un-comment the code.  

*>

<*

/* Pass report definition id to XML Publisher engine and get report definition object  */

&oRptDefn = create PSXP_RPTDEFNMANAGER:ReportDefn(&ReportId);

&oRptDefn.Get();

/* Pass xml file name and generate XML File with data tags */

&oRptDefn.SetRuntimeDataXMLFile(&FileName);

/* Set default output format of the report as defined in the report definition  */

&sOutDestFormat = &oRptDefn.GetDefaultOutputFormat();

 /* Call process report function with current date and output destination format in PDF  as function parameters */

&oRptDefn.ProcessReport("", "", %Date, &sOutDestFormat);

CommitWork();

/* Display the output  method will fetch the report from report repository and open the report it in a new browser */ &oRptDefn.DisplayOutput();

*>

/* Code Ends */

  

o   Access the field values from the component buffer and assigned it to the work record fields.

o   Code will generate XML file using file layout

o   First time after clicking the button, the above code, with the commented lines of code, generates XML file in PSHOME directory

o   Since you have just generated XML file by PeopleCode it is a good time for you to create a new Data Source definition. The Data Source Type will be "XML File"

o   Upload the XML file by clicking the upload link as shown below:

o   Create report template

a)  Download and Install ‘Designer Helper’ in case not installed

b)  Open Microsoft office word, click on the add-ins tab, click on the ‘Data’ and select ‘Load XML Data’, upload the XML file you have created using the PeopleCode.

Note: No need to bother for XSD, just leave it blank.

c)  After loading data, you will get a confirm message ‘Data Load Successfully’.

d)  Click on the ‘Insert’ tab, select Table wizard, select table then select columns which you want to show in your report and click next and finish as shown below

e)  Template is ready with the fields as shown below:

f)  Instead of using table wizard, you can directly drag and drop fields (use free form) in the template according to your business requirements.

g)  Save the template as .RTF format

o   Create Report Definition

a)  Provide the name of your new report definition

b)  Select the data source id, which you have created earlier, from the prompt.

c)  Screenshot below for your reference

d)  Upload the .rtf template you have created

e)  Select output format type as PDF

f)  Save the report definition.

o   Provide report definition and template name you have just created in the code also as highlighted above in the code itself as ‘&ReportId’ and ‘&TemplateId’

o   Click button to run the report.

Note:

Since we have created report definition, we will un-comment the code which we have commented earlier (highlighted in block) so that report definition id can be passed to process the report finally.

Contributed by Rohit