How to create a XMLP Report with PS Query as data source and using PeopleCode?

What we are trying to achieve?

o   Create a XMLP report in pdf format of the content item information by clicking on a button on the page

o   Screenshot of the content item for the 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 mentioned XMLP Report

o   Create a PSQuery which will list the content item information as displayed on page.

o   Create a Data Source of type “PS Query” using the PSQuery created in the above step

o   Under “Generate File” column you will see two links as ‘Generate’, click on both the links to generate XML and XSD files as highlighted in yellow.

o   The moment you click on Generate link, automatically XML and XSD file will be generated with a link under “File Column”.

o   Click on the first .XML file and .XSD File link to download the xml and xsd to your local machine (Use IE browser)  

o   Download and Install ‘Designer Helper’ in case not installed

o   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 just downloaded on your local machine and ‘Load XSD Schema’ file from your local machine.

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

o   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

o   Template is ready with the fields as shown below:

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

o   Save the template as .RTF format

o   Create Report Definition, provide the name of your report definition

o   Select the data source id, which you have created earlier, from the prompt

o   Screenshot below for your reference

        

o   Upload the .rtf template you have created

o   Select output format type as PDF

                         o   Save the report definition.

o   Open application designer, 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.

o   The code consists of two parts.
      Part 1 provides all the prompt values which you have used in your PS Query as highlighted.
      Part 2 runs the XMLP Report using the class in PeopleSoft delivered Application Package

o   Provide the report definition and template id which you have created earlier as highlighted below:

===================PS Query code for XMLP Report=================

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

import PSXP_RPTDEFNMANAGER:*;     

/* Declare delivered functions for specific tasks like setting up directory path, file extension etc  */

Declare Function DeleteLocalFile PeopleCode PSXPFUNCLIB.FUNCLIB FieldFormula;

Declare Function GetDirSeparator PeopleCode PSXPFUNCLIB.FUNCLIB FieldFormula;

Declare Function GetFileNameFromPath PeopleCode PSXPFUNCLIB.FUNCLIB FieldFormula;

Declare Function GetDirectoryFromPath PeopleCode PSXPFUNCLIB.FUNCLIB FieldFormula;

Declare Function GetFileExtension PeopleCode PSXPFUNCLIB.FUNCLIB FieldFormula;

/* Declare variables */

Local PSXP_RPTDEFNMANAGER:ReportDefn &oRptDefn;

Local PSXP_RPTDEFNMANAGER:Utility &oUtil;

Local string &sDataDir;                       /* Data directory name*/

Local string &sDirSep;                         /* Directory separator */                  

Local string &sRptDefn;                      /* Contain report definition id */

Local string &sTemplateId;                 /* Contain template Id */

Local string &sOutputFile;                  /* Output file name  */

Local date &dAsOfDate;                     /* Date of running report */

Local string &sOutputFormat, &sFileExt;                         /*  Report output format and File extension */

Local Record &rcdQryPrompts;                        /* PS Query prompt record */

Local string &sOutputDir, &RptOutputDir;      /* Output directory and Report repository */

/* Try catch block starts */

Try

/* Provide report definition id*/

   &sRptDefn = "DK_CONTENT";

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

   &sTemplateId = " DK_CONTENT_1";

/* Current Date of execution*/

   &dAsOfDate = %Date;

   /* detect system directory separator */

   &sDirSep = GetDirSeparator();

   /* this method will create process directory in PSHOME server */

   CreateDirectory("XMLP", %FilePath_Relative);

   /* start logging for debugging purpose */

   WriteToLog(%ApplicationLogFence_Level1, "*** XML Publisher View Report Job Start: " | String(%Datetime) | "***");

   WriteToLog(%ApplicationLogFence_Level1, "Report Name = " | &sRptDefn);

   WriteToLog(%ApplicationLogFence_Level1, "Template id = " | &sTemplateId);

   WriteToLog(%ApplicationLogFence_Level1, "As of Date = " | &dAsOfDate);

   WriteToLog(%ApplicationLogFence_Level1, "Output Format = " | &sOutputFormat);

   WriteToLog(%ApplicationLogFence_Level1, "Language Code = " | %Language_User);

  

   /* instantiate report definition object and pass report definition id  */

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

   &oRptDefn.Get();

 

   /* output directory – after running the report the pdf output will be generated in the report repository */

   &RptOutputDir = GetEnv("PS_SERVDIR") | &sDirSep | "files" | &sDirSep | "XMLP" | &sDirSep | UuidGen();

   &sOutputDir = &RptOutputDir | &sDirSep | "RptInst";

   &sDataDir = &RptOutputDir | &sDirSep | "Data";

   CreateDirectory(&sOutputDir, %FilePath_Absolute);

   CreateDirectory(&sDataDir, %FilePath_Absolute);

 

   &oRptDefn.OutDestination = &RptOutputDir;

   /* select Output format in PDF specified as function parameter ‘2’ */

  /* 2 - PDF */

  /* 5 - HTM */

  /* 8 - XLS */

  /* 12 - RTF */

  

&sOutputFormat = &oRptDefn.GetOutDestFormatString(Value("2"));

   /* fill PS Query prompt record with values */

   &rcdQryPrompts = &oRptDefn.GetPSQueryPromptRecord();

   If Not &rcdQryPrompts = Null Then

 &rcdQryPrompts.JPM_CAT_TYPE.Value = JPM_CAT_ITEMS.JPM_CAT_TYPE.Value;

&rcdQryPrompts.JPM_CAT_ITEM_ID.Value = JPM_CAT_ITEMS.JPM_CAT_ITEM_ID.Value;

                /* Set all the prompt values in the PS Query  */

 &oRptDefn.SetPSQueryPromptRecord(&rcdQryPrompts);     

   End-If;

       /* Call process report function with template id, language, current date and output format in PDF  as function parameters */

   &oRptDefn.ProcessReport(&sTemplateId, %Language_User, &dAsOfDate, &sOutputFormat);

 /* Set file extension like PDF, HTM, XLS or RTF */

  &sFileExt = GetFileExtension(&sOutputFormat);

   CommitWork();

   /* Display the output  method will fetch the report from report repository and open the report it in a new browser */

   &oRptDefn.DisplayOutput();

   /* cleanup cache */

   DeleteLocalFile(&sOutputFile, %FilePath_Absolute);

   WriteToLog(%ApplicationLogFence_Level1, "*** XML Publisher View Report Job End: " | String(%Datetime) | "***");

/* Catch block starts */

catch Exception &Err

   Local string &sSub1, &sSub2, &sSub3, &sSub4, &sSub5;

   Evaluate &Err.SubstitutionCount

   When > 4

      &sSub5 = &Err.GetSubstitution(5);

   When > 3

      &sSub4 = &Err.GetSubstitution(4);

   When > 2

      &sSub3 = &Err.GetSubstitution(3);

   When > 1

      &sSub2 = &Err.GetSubstitution(2);

   When > 0

      &sSub1 = &Err.GetSubstitution(1);

   End-Evaluate;

   Error MsgGet(&Err.MessageSetNumber, &Err.MessageNumber, &Err.ToString(), &sSub1, &sSub2, &sSub3, &sSub4, &sSub5);

end-try;

/* End of Code */

Limitations:

• This code will only work with the PS Query data source.
• This code will not work for the report which fetches data directly from the page buffer instead from physical database record.

Contributed by Rohit

PeopleSoft Test Framework Installation and Configuration - Part 2

In this follow up post, we will take a look at the configurations we need to do once the PTF client is installed.

Once the application is launched, the Local options and the Execution options need to be configured.

Configuring Local Options

Local Options and Execution options can be accessed by clicking the database name with a '@' symbol prefixed to it on the top left corner of the PTF client.

The local options are specific to the machine where the PTF client is installed. The process run options define the settings for timeout for each process status. With the configuration below, if a process invoked by PTF is in queued or posting status for 1 min, then the test will log a warning message.

Similarly, the output folder and the output format can be defined if PSQuery is run from PTF.

Configuring Execution Options

Unlike Local options which are specific to a machine where it is installed, execution options once defined – are stored in the application database, and are available for anyone who logins to that application database to create, modify test assets. Only the administrator (who has PTF Administrator role) can configure the execution options for a database.

An execution option is for a specific application – so if two applications are to be tested from the same PTF client, then we would require two execution options, one for each application.

           

Basic Options

Under the basic options tab, the URL for the application needs to be given along with the default user ID and password. This URL is used to launch the application when a recorded test is played back.

The process server and the date format can also be defined in this section.

Skip PageSave will skip the step in the test case which attempts to save the component.

       

      

Advanced Options       

Advanced Options tab provides the option to enable persistent variables in the environment. Persistent variables store information that can be accessed whenever the application database is used in PTF client. They are similar to registry variables.

           

PeopleTools Options

It is in PeopleTools Options tab, we provide the database user ID and password. This is very important – as this information is used to retrieve test assets already stored in the database.

Log Export

In this tab, we define where the log files generated by the test cases are to be posted. A test case log can also have snapshots of the PeopleSoft screens, and 'Export Images' option in this tab will export the screenshots as images in the log export file.       

Note: The installation and the screenshots in this post are from PeopleTools version 8.53. This might be different from the earlier version of tools. 

Contributed by Karthik and Swetanshu

PeopleSoft Test Framework Installation and Configuration - Part 1

Introduction

PeopleSoft Test Framework (PTF) is a functional testing tool delivered by Oracle starting from PeopleTools version 8.51. This tool employs record and playback approach, and is meant to simulate one user working on a system at any point of time. This is not a load testing tool and the test assets created using the PTF tool are PeopleSoft managed objects - similar to records, pages and components.

The development instance of PTF should have:

1.   PTF client instance

2.   Connection to PeopleSoft application where we conduct tests

3.   Microsoft Internet Explorer browser (preferably IE8 and above)

4.   Connection to PeopleSoft application database where the test assets are stored 

The scope of this post is to explain the steps involved in the installation of the PTF client, and the configuration of the same.

Prerequisites

Verify Integration Broker Setup 

·         Go to Main Menu > PeopleTools > Integration Broker > Configuration > Gateways

·         Ping Gateway and see if it is active

   

·         Click the Gateway Setup Properties link on the same page. Sign on to integration gateway properties file, and verify if the default app server URL is specified.

Configuring the Web Profile

·         Go to Main Menu > Peopletools > Web Profile > Web Profile Configuration

·         Select the profile name of the environment and click on Debugging tab.

·      As shown in the above screenshot, check the 'Show Connection & Sys Info' and 'Generate HTML for testing' checkboxes. If the 'Show Connection & Sys Info' checkbox is not checked, PTF will not record the component, page and menu names in the test assets. Also, if the 'Generate HTML for testing' is not checked, PTF will not record HTML objects correctly.

 Defining Configuration Options

·         Go to Main Menu > Peopletools > Lifecycle Tools > Test Framework > Define Configuration Options

·         Specify whether untrusted SSL certificates can be allowed, and also specify record options.

Internet Explorer Options

·         Add the PeopleSoft application URL and related domain names to the safe list (Internet Options > Security tab > Local Intranet > Sites button), so that there will not be any issues in launching the application when the PTF test scripts are played back.

Installation

PTF client is a software installed in user's machines, where they can create, modify and execute test assets. The assets that are created in PTF client are stored directly in the application database. This client is similar to Application Designer.

The executable file for PTF is usually available from the path <PS_HOME>\setup\PsTestFramework. Before executing this file, make sure Microsoft .NET framework v3.5 and above is installed in your system.

Step1: Double click the executable file 'setup.exe' available in the path mentioned above. That launches the installation wizard.

Step 2: After providing the path where the application needs to be installed, click Next till the installation completes.

Step 3: Once the installation is complete, go to Start Menu and launch the PTF application

Step 4:  When the application starts, create a new instance (environment where the test assets are to be stored) by clicking the 'New' button.

Step 5:  Provide the database name (where the test assets are to be stored), application server URL and port, the node ID of the application.

Also, the user ID and password need to be provided during this setup – and it should be ensured that the user ID provided should have any of these 3 delivered roles provided by PeopleSoft for PTF.

·         PTF User

·         PTF Editor

·         PTF Administrator

After providing the details, click OK.

Step 6: This will open the PTF client as below

Note: The installation and the screenshots in this post are from PeopleTools version 8.53. This might be different from the earlier version of tools. 

Please take a look at the Part 2 for details on the configuration.

Contributed by Karthik and Swetanshu.

Give your functional testers a break

PeopleSoft has lot of tools to work on – some of the very popular tools include Application Designer, Application Engine, Component Interface, etc.., When PeopleSoft releases a new PeopleTools version, generally it comes up with some new tools, and also provides updates/upgrades to their existing tools. Tools like Integration Broker, Upgrade Manager, XML publisher, etc.., are very handy, but somehow they are not so common to entry level technical consultants. We recently explored a new tool for functional testing which has been delivered by PeopleSoft from the tools version 8.51. This tool proves to be so easy to access, and even entry level consultants found it easy to work on it.

This relatively new tool – PeopleSoft Test Framework seems to be slowly catching up in the functional testing space of PeopleSoft applications. It employs record and playback approach. When we record the test, it translates each of our activity into small steps, and these steps together make up a test. When we playback, the steps are executed sequentially. PTF has a test editor where we can modify the existing tests and clone them into new ones. The editor also provides options to delete or inactivate steps.

The idea behind the launch of this tool is primarily to reduce the effort spent on functional testing. The tests created in this tool are PeopleSoft managed objects, so it is easy to migrate these test assets to different PeopleSoft environments. In those instances, we need to just play it back. This is an interesting concept. As a functional tester, one can be relieved to get rid of mundane tasks to be done in each of the instances – thanks to PTF tool. No need to maintain bulky excel sheets with test cases. PTF generates the test logs and they can be viewed from PTF client along with the screenshots. We also can go ahead and create dashboard reports out of these test case results, since all the data we require for the report are already stored in the database.

This tool can also be used for regression testing. This means, we can create a test today, and use the same script after couple of years when an application upgrade is done – to see if your business processes are still intact. Organizations that have implemented PeopleSoft can very well utilize this tool for their testing needs. In the long run, customers can very well realize that the testing costs have drastically reduced.

If your organization has implemented PeopleSoft, it’s high time you try PTF.


Leave your comments below on what do you think about this tool from PeopleSoft. If you are already using this, please comment on what kind of value addition this tool has brought to you and your team.

Contributed by Karthik

PeopleSoft HCM Data Permission Security @work

Background

Recently one for our customer, for whom we had implemented PeopleSoft HCM 9.1 few years back, asked us to revisit and re-design their Data Permission Security as the business need had changed since implementation. 

The customer has employees in USA and Canada and has separate HR Administrator for both the regulatory region. During implementation, Data permission security was implemented for the components and reports having sensitive employee information using the PeopleSoft delivered Security Type -005 i.e. Job Reg-Region.

Business Issue

Due to increase in the workforce, the customer wanted to reorganize their HR Administrator’s span of control.  They wanted the HR Administrator to be responsible for employees in a location or groups of location.

So, when the HR Administrator accesses a component containing employee data, he/she should have access to the employees of those locations only, for which the HR Administrator has the data security permission.

If this was the only requirement from the customer, it would have been a simple case of using the PeopleSoft delivered Security Type -002 i.e Job Location.

But the customer’s business need was not limited to that. They wanted the HR Administrator not to have access to the employees of the “HR” department in those locations, for which otherwise they have access through data security permission.

In PeopleSoft, when we grant a permission list access to data in a security set , using more than one security access type , the security access creates a union, not a join or an intersect, with the two types.

For example, if we enable the Job Location and Job Deptid Non Tree security access types for the PPLJOB security set and grant a permission list access to employees in location A and employees in department B, HR Administrator with the permission list can access all employees in location A or all employees in department B; their access is not restricted to employees in both location A and department B.

So , for the above business need , we cannot simply enable PeopleSoft delivered Security Type -002 i.e. Job Location and Security Type -025 i.e. Job - Deptid - non Tree.

SOAIS Solution

We created a custom Security Type within the Security Set PPLJOB using Business Unit, Location and Department. This helped us to meet the customer requirement of intersect of location and department to determine the span of control for the HR Administrator. The HR Administrators were given access to the combination of locations and departments based on their span of control except for the HR department.

Please find below the snapshot of the configuration

Snapshot of the Configuration

• Custom Security Access Type 102 within Security Set PPLJOB.

• Screenshot of Security by Permission List using the custom Security Access Type 102.

Contributed by Debasish