Wednesday, July 22, 2015

Creating Reports with Peoplesoft BI publisher(XML Publisher) and Connected Query

PeopleSoft BI Publisher(formerly XML Publisher)
 
Oracle Business Intelligence Publisher (BI Publisher, formerly XML Publisher) is an enterprise reporting solution that streamlines report and form generation.
A primary feature of Oracle’s BI Publisher product is the separation of the data extraction process from the report layout. BI Publisher enables you to design and create report layout templates with the more common desktop applications of Microsoft Word and Adobe Acrobat, and renders XML data based on those templates.
With a single template, it can generate reports in many formats (PDF, RTF, Excel, HTML, and so on) in many languages. This approach to reporting can dramatically reduce report maintenance, enabling power business users to adjust report templates without involvement of IT resources.


For creating reports, there are basically three steps:


  1. Create Data source: BI PUBLISHER allows you to have 3 types of data source: PSQUERY, CONNECTED QUERY and XML file.
  2. Creation of Report: Create the report template as per requirement.
  3. Uploading report template into PeopleSoft and configuring other parameters.


Let’s begin :


Let’s create a simple report where we will print the basic profile information of an employee such as:
  • Personal Information: Name, DOB, Photo of the employee.
  • Current Job Information.
  • Past Job Information
  • Past Employments.
  • National Id information .
  • Profile data(Education, Certificates and Qualification).


We will make use of connected query in order to achieve this.
A Connected query is way to maintain parent-child relationship between queries.
You can create a parent query and its children and their children and so on to show on report.
This is really helpful as most of the reports are structured in Parent-Child manner.
We will create a parent query which will have Parent Data such as: Emplid, Name, Age, DOB, Photo of the employee i.e. is only one row per employee.
And rest of details will be child of this this query.


All the queries (Parent and Child are to be created as PSQUERY and then linked using Connected Query).


In Case you are not aware how to create PSQUERY , please refer the tutorial on creation of PSQUERY.


Creation of Parent PSQUERY:
  • Personal Information: Name, DOB, Photo of the employee.


The SQL of parent query looks something like below.


And the output looks like below.
You may create a query similar.


Creation of Child PSQUERIES:
  • Current Job Information.


SQL for Current job should look similar to this.


And output should look similar to below.


  • Job History


SQL should like something below.


Output should look something like below:


  • Prior Work Experiences .


SQL should like something below.


Output should look something like below:




  • National Id information .

SQL should like something below.



Output should look something like below:


  • Profile data(Degree).


SQL should like something below.


Output should look something like below:


SO, we have created all necessary PSQUERIES.
Now time to create Connected Query.


Add a new Connected Query:




Add the Parent-Children queries as shown below:


Map Parent with child:


Do this for each of the query.
Learn about how to create connected query for more details.


Each of the child query is mapped with parent.
After that change the status to Active and save the page.


One can test the Connected query by clicking on PreviewXML and check if the result is coming according to the expected result.




I have added additional criteria on our parent query to prompt for employee id for which the query has to be triggered. Otherwise the query will return a huge number of rows.




There might be some minor tweaking needed for all queries, you can modify it accordingly.


SO the first part of creation of PSQURIES has completed.


Now we have reached the second step in this to create the report.


For creating report, you may take copy of any existing report and can start editing.


Sample reports are also available at path where you would have installed BI publisher.
For my machine, the path looks something like below.


There you can see a number of samples.




Since we will be using RTF to create the report template we will go inside RTF template folder.


Inside RTF templates folder , there are many sample report, you may pick any and start working.




I have taken the copy of Balance Letter and rename it as Employee Profile.


Let’s clear out the existing content in the report.
To work with the report we need a sample XML data, so that we can test the report.


The same XML data can be fetched from the report definition.
We still haven’t created the report definition and will create now.


Registering a report starts with creation of Data Source.




Add a new value:




You can generate a sample file by clicking on Regenerate/Generate link , which can be used for the report.




Copy the content of XML and create and XML file with that content.




Now open the template and go to BI Publisher tab, and click on Sample XML and load the .xml doc you saved just before.
















Do the same for all the Child Query and you will be template something like below:




Rename the Column Headers, remove the unnecessary columns. Add header for each of the section .The template might look like something below:




You can Preview the template, by clicking on the PDF/HTML/EXCEL etc link at the top on the BI PUBLISHER ribbon.




Tempate design is over, Third and Last step is to create Report Definition.
Navigate as below and click on add a new value.
Give a name to your report.


Select the data source type as Connected Query and select the data source id which we created earlier.




Enter the details as below. Make sure “Report Status” is set to Active once you are done with the data entry.




Upload the template you created here.




Change the Status to Active once you are done.


Leave the other Output tab as it is unless you want to change the default setting.


Keep this tab as it is.


Add the Security for the Report.


Keep the Bursting Tab as it is, we need not use it now.
It can be used for bursting option.




Before saving the component make sure Status are active as shown below.




Save the page.


Trigger the report by scheduling it.
Navigate as below and add a runcontrol id.




Provide the details as below for the report definition which was created earlier.




Click on run.
Select Format as PDF or any other preferred format.




Click on OK to run the report.




Go to report Manager to view the report.




Click on the link to view the report.


And our report is generated.






Report template can be modified to adjust look and feel.
The PSqueries and connected query can be modified to adjust the data available to XML report.


There are other useful features also which comes with BI PUBLISHER.
  1. Sorting
  2. Grouping
  3. Conditional Formatting
  4. Condition Region
  5. Additional Coding using XSLT.




Apart from Connected Query and PSQUERY, BI PUBLISHER can also use XML file as input. For that we create the XML file using the Peoplecode or any other utility and pass it to the Report.

In that manner the data can be manipulated with much more flexibility.

Wednesday, May 27, 2015

PeopleSoft Tracing & Logging

Tracing online People Code and SQL

1. Select Trace at the login screen or add “&trace=Y” to the login URL OR - To set trace flags, click here (use this link.
2. Choose the options in the tracing screen and login.
3. Perform the actions you wish to record.
4. Once complete logout and retrieve the file from the /appserv//LOGS directory. 
The trace file name will include your login name.


Tracing online PeopleCode and SQL (Alternative)

1. Login to PeopleSoft and navigate to PeopleTools > Debug.
2. Choose the options and save.
3. Perform the actions you wish to record.
4. Once complete logout and retrieve the file from the
/appserv//LOGS directory. The trace
file name will include your login name.


Application Engine Tracing
1. Open the process definition
2. Select the overrides tab.
3. Select Append on the parameters field.
4. Enter -TRACE 131 -TOOLSTRACEPC 3596 -TOOLSTRACESQL
143
5. Save the record.


SQR Tracing
1. Open the process definition
2. Select the overrides tab.
3. Select Append on the parameters field.
4. Enter –DEBUG[ABC]… (replace ABC with the debug letters
provided in the sqr, sqc source).
5. Save the record.


Cobol Tracing
1. Open the Cobol SQL process type for the platform you are running on.
2. Enter 255 in the command line field between two forward slashes “//”.
3. Save the record.
%%PRCSNAME%% %%DBTYPE%%/%%DBNAME%%/%%OPRID%%/%%OPRPSWD%%/%%RUNCNTLID%%/%%INSTANCE%%/255/%%DBFLAG%%
4. Run the process and retrieve the COBOL sqltrace from the process monitor.


Tracing Settings
1. Both the process scheduler (psprcs.cfg) and application server (psappsrv.cfg) config files allow for permanent tracing settings as well as setting up trace Masks. psprcs.cfg - /appserv/prcs// psappsrv.cfg - /appserv//

2. Trace Masks are setup to limit the amount of tracing allowed when a user wants to trace through the front end package (see all methods
above).

3. LogFence allows for lower and higher detail tracing with regards to application server standard logging. This is ideal for identifying problems on the application server.

4. To enable the trace link in the Sign on page.
    People-tools > Web-profile.
  
   Web profile > Debugging Tab -> Check the Option - Show Trace Link at Signon

Thursday, February 19, 2015

Peoplesoft Row Level Security Search Records

PeopleSoft Row Level Security Search Records

If an organization we are working with is using PeopleSoft as it's ERP solution then depending upon what role we play we do online transactions through various components in PeopleSoft. For example, HR users usually work in Production environment hence use components under Workforce Administration, Organizational Development, Workforce Development, Benefits etc… in order to carry out their daily business.
A Consultant (Technical or Functional) however will mostly works in Non-Production environment hence the components under the menus – Workforce Administration, Set Up HRMS, PeopleTools etc… are the ones where they visits most often to do transactions as required. A consultant at times needs to play various roles in order to expedite certain tasks part of theirassignment. For example: 
  • Resolving a production issue by simulating it into Non Prod environment
  • Validating a test scenario which is part of implementation
  • Fig-Gap analysis
If we just forget about the role that one plays and list out few most common components a user deals with to carry out various transactions then the list will contain:
Transactions on Job data

·       JOB_DATA (Navigation: Main > Workforce Administration > Job Information > Job Data)

·         JOB_DATA_CURRENT (Navigation: Main > Workforce Administration > Job Information > Current Job)

·         ADD_PER_ORG_ASGN (Navigation: Main > Workforce Administration > Job Information > Add Additional Assignment)

·         ADD_HOST_ASSIGN (Navigation: Main > Workforce Administration > Global Assignments > Track Assignment > Add a Host Assignment)

Etc…


Transactions on Personal data

·         PERSONAL_DATA (Navigation: Main > Workforce Administration >Personal Information > Modify a Person)

·         DEPEND_BENEF (Navigation: Main > Workforce Administration >Personal Information > Personal Relationships >Dependent Information)

·         EMERGENCY_CONTACT (Navigation: Main > Workforce Administration >Personal Information > Personal Relationships > Emergency Contact)

·         DISABILITY (Navigation: Main > Workforce Administration >Personal Information > Disabilities)

Etc…


Hold on.. Why am I explaining all this when our topic here is “Row Level Security Search Records in PeopleSoft”?
Well, because all the components mentioned above, use security views as their search record and that is to ensure that a particular user who is doing transactions through these components, can see only those employees that they are allowed to see.I would like to list down those security views along with the components where they are used as search record:

   Components for Job data transactions

·         Job Data (JOB_DATA)  - EMPLMT_SRCH_GBL

·         Current Job (JOB_DATA_CURRENT ) - EMPLMT_SRCH_COR

·         Add Additional Assignment (ADD_PER_ORG_ASGN ) - PERS_SRCH_GBL

·         Add a Host Assignment (ADD_HOST_ASSIGN ) - PERS_SRCH_GBL

            Etc…

   Components for Personal data transactions

·         Modify a Person (PERSONAL_DATA)  - PERS_SRCH_ALL 

·         Dependent Information (DEPEND_BENEF) - PERS_SRCH_GBL

·         Emergency Contact (EMERGENCY_CONTACT) - PERS_SRCH_ALL

·         Disabilities(DISABILITY) - PERS_SRCH_ALL

            Etc…

As you can see, there are various security views created and are used in these components. Based on the component requirement, some of these views search for the employee data rows by EMPLID and EMPL_RCD and others just by the EMPLID.
For example, The view EMPLMT_SRCH_GBL used in Job data component considers EMPL_RCD field while searching the data row when a user hits on the “Search” button in the component search page whereas the view PERS_SRCH_ALL used in Personal Data component doesn’t consider EMPL_RCD filed while searching the data rows.
One easy way to find out the search record for a component, Just open the menu definition under which the component exists, in the Application Designer and locate your component, then right click on it and open the “Menu Item Properties”. Check what the search record for the component is and whether it has been overridden.

So, when the so called “Row Level Security Search Records” come in picture and how they secure the data from a user (HR Admin, Consultant etc…). This is also called Row Level Security in PeopleSoft.

Securing Data rows using Security Search Records (Row Level Security in PeopleSoft)



Let’s take any one of the component mentioned earlier. A new employee “ST01ST20” has been hired in the organization but when HR tries to open their job data through JOB_DATA component:

 

Oops..!  looks like employee doesn’t exist.

Is that what it looks like?  No, instead the HR user is not able to see the newly hired employee because they don't have access to them and this restricted access has been enforced with the use of the row level security view EMPLMT_SRCH_GBLas search record in this component. 

So let’s find out what exactly happens behind the scene and how the security search view works to ensure the unintended employee data shouldn’t be displayed to HR user.

   In the search page, when we enter the EMPLID ‘ST01ST20’ and hit enter:

     


   Hope everything is clear so far and if it is then let’s give data access to the HR user so that they can see the data rows of employee ST01ST20. There are various different ways such access can be given and for now we will just add business unit “AUS01” to the Permission list TEST_PERMSN (Navigation: Main > Set Up HRMS > Security > Core Row Level Security > Security By Permission list).  

  Note that, this access can also be given through “Security by Dept Tree” component    which exists under the same menu. In this method, the HR users are given access to the intended department defined in a Department tree where a particular employee belongs.
After the access is given to HR user let’s see what happens: 


Tuesday, February 17, 2015

Tracing PeopleSoft Application Engine

Tracing Peoplesoft App Engine
Application Engine are PeopleSoft’s way to tie SQL and PeopleCode into a powerful processing engine which can run synchronously as well as in batch mode. App Engines are well structured and easy to debug. In this post will see how TRACE an App Engine program. We will see how to debug an App Engine separately.
As tracing brings significant overhead, by default, tracing is turned off. You will have to enable it using one of the methods below depending on the specific scenario. Simultaneously using more than one of these may produce misleading results.

Process Scheduler

One of the most common way of tracing an App Engine is by setting the TRACE at the Process Scheduler. To do this, we need to open the process definition for the App engine in question and navigate to the Override Options tab. Select ‘Append’ in the Parameter List dropdown and fill in the Parameters field with the below command.
-TRACE 135 -TOOLSTRACEPC 1984 -TOOLSTRACESQL 31
This tool can help you analyze your trace files: PeopleSoft Trace Magic
Based on your requirement, you can use the below values for tracing. To specify a combination of traces, use the sum of the corresponding trace values. Say, to enable the first four options all at once, you would use the sum of 1, 2, 4 and 128 ie; 135
ValueDescription
0Disables tracing.
1Initiates the Application Engine step trace.
2Initiates the Application Engine SQL trace.
4Initiates the trace for dedicated temporary table allocation to an Application Engine trace (AET) file. You can trace how the system allocates, locks, and releases temporary tables during program runs.
128Initiates the statement timings trace to a file, which is similar to the COBOL timings trace to a file.
256Initiates the PeopleCode detail to the file for the timings trace.
1024Initiates the statement timings trace, but stores the results in the following tables: PS_BAT_TIMINGS_LOG and PS_BAT_TIMINGS_DTL.
2048Requests a database optimizer trace file.
4096Requests a database optimizer to be inserted in the Explain Plan table of the current database.
8192Sets a trace for PeopleSoft Integration Broker transform programs.

Configuration Manager

To enable tracing for an App Engine running on a Windows PC ie; two-tier mode, we use the Configuration Manager. Launch the Configuration Manager and select the needed level of tracing from the Trace tab.

Command Line

To enable Tracing for an App Engine executed from the command line, use the -TRACE option silimar to the one used in the process definition. An example as shown in PeopleBooks is
n:\pt840\bin\client\winx86\psae.exe -CT MICROSFT -CD PT800GES -CO PTDMO? -CP PTDMO -R PT8GES -AI AETESTPROG -I 45 -TRACE 2

Server Configuration Files

For programs invoked by PeopleCode and run on the application server, set the TraceAE parameter in the Trace section of the Application Server configuration file (PSAPPSRV.CFG). You can use PSADMIN to set this parameter.