PeopleSoft Learning

PSQuery Performance issues with Query Security Tables

We see PSQuery having bad performance due to many Query Security Join tables that the PSQuery tool adds to the PSQuery SQL for security.


We have few options to work around to easily improve the performance without much involving tunning from the DBA perspective which can time consuming.

 If security is not required for this query 

 Change the Query type to 'Process'. Process queries override the automatic row-level query security logic that is applied to all other types of queries.
   
    It will be useful for the PSQuery in Pagelet specific to the logged-in users where security is not important.

   If you are using Campus Solution and you don't know Campus solution specific query tables, you don't need Security to join tables.

 If Security is required

  Select the Security optimization checkbox, it will eliminate duplicate security.
  if still, more than one Security join tables are added and need improvement in performance, 
  one security join should be fine usually and for other records view of the table without security join table.

Identify navigation in PeopleSoft by a Process name and/or Job name

There are many SQL statements in different blogs to identify the navigation for a process.
But I have modified that to get the Navigation for a Job.
Single SQL works for both Process and Job.


SELECT DISTINCT RTRIM( REVERSE ( SYS_CONNECT_BY_PATH(REVERSE (portal_label), '>-')) ,' >- ') PATH
      FROM PSPRSMDEFN
     WHERE portal_name = 'EMPLOYEE'
       AND portal_prntobjname = 'PORTAL_ROOT_OBJECT'
START WITH portal_uri_seg2 IN (
                       SELECT DISTINCT pnlgrpname
                       FROM ps_prcsdefnpnl
                       WHERE prcsname = UPPER(:1)
                       UNION
                       Select Distinct PNLGRPNAME
                       FROM PS_PRCSJOBPNL
                       Where PRCSJOBNAME = UPPER(:1) )
CONNECT BY PRIOR portal_prntobjname = portal_objname;


Hope this will be useful.

SQL to Identify detailed Security access for a Page or Component.

When you want details about a single page in the component.

select a.menuname,a.barname,a.baritemname,rc.rolename, rd.descr , a.classid, c.classdefndesc,a.Displayonly,ac.pageaccessdescr
from psauthitem a, psclassdefn c, psroleclass rc, psroledefn rd, PSPGEACCESSDESC ac
where a.pnlitemname = :1
and a.classid = c.classid
and a.classid = rc.classid
and rc.rolename = rd.rolename
and a.Authorizedactions = ac.Authorizedactions
Order by rc.rolename;


When you want details about a component.

select a.menuname,a.barname,a.baritemname,rc.rolename, rd.descr , a.classid, c.classdefndesc,a.Displayonly,ac.pageaccessdescr
from psauthitem a, psclassdefn c, psroleclass rc, psroledefn rd, PSPGEACCESSDESC ac
where a.BARITEMNAME = :1
and a.classid = c.classid
and a.classid = rc.classid
and rc.rolename = rd.rolename
and a.Authorizedactions = ac.Authorizedactions
Order by rc.rolename;

HCM - RowLevel security directly driven by the - "RowLevelSecurity" Permission list.
User Profile needs to have Rowlevel Security Permission assigned to get the security.

We can have Department based - RowLevelSecurity as well as based on the custom Security Set Properties.(It can be based on COMPANY, BUSINESS UNIT etc.).

1) Security by Department 2) Security by Permission List.


Rowlevel security is enforced in the components by user Security Views as Search Records, where OPRID is a non-search key field. PeopleSoft

Rowlevel Security in the PSQuery is achieved by Search - Query Security Record - which is an property of the Record. Query Security Record (which a security view) is added to query automatically.

Since the Campus has different security model, using HCM Personal data records in PSQuery for Campus Solutions will be a problem.

Campus solution Module doesn't use the "RowLevelSecurity" Permission list in the User Profile.

Campus solution has 2 options -

a) SCAR security based on the data element linked to student is accessed. Like INSTITUTION, CAREER, PROGRAM, PLAN.
Each User ID needs to be configured in the SCAR security for each of the data elements like CAREER, PROGRAM, PLAN etc.

b) Person Mask security, even if user have access to different data elements in the SACR for the configuration purpose, we can restrict the access to student data by using this configuration.
PERSON_SRCH - Delivered search view is used to control the security and part of many search records of the components in Campus solution.

In this case, User "Primary Permission" list needs to be configured in the Person Mask component and Person Mask process needs to be executed so that User can get the access to view Student information in the campus solution.

Set up SACR > Security > Secure Student Administration > Permission List > Demographic Data Access
Set up SACR > Security > Secure Student Administration > Process > Demographic Data Access

Since the HR Person records has Row level security attached for the PSQuery and Campus doesn't use the Rowlevel security model similar to HR. We cannot use the base Personal records in Campus to create the PSquery reports (unless security is replicated).

Core Person Model -- Record PS Query View for CS
ACCOMPLISHMENTS -- SCC_ACCOMP_QVW
ACCOM_DIAGNOSIS -- SCC_ACCOM_D_QVW
ACCOM_JOB_TASK -- SCC_ACCOM_T_QVW
ACCOM_OPTION -- SCC_ACCOM_O_QVW
ACCOM_REQUEST -- SCC_ACCOM_R_QVW
ADDRESSES -- ADDRESS_NPC_VW
AUDIOMETRIC_TST -- SCC_AUDIO_T_QVW
CITIZENSHIP -- SCC_CITIZEN_QVW
CITIZEN_PSSPRT -- SCC_CITZN_P_QVW
DISABILITY -- SCC_DISABLE_QVW
DIVERSITY -- SCC_DIVERS_QVW
DIVERS_ETHNIC -- SCC_DIV_ETH_QVW
DRIVERS_LIC -- SCC_DRIVERS_QVW
EMAIL_ADDRESSES -- SCC_EMAIL_QVW
EMERGENCY_CNTCT -- SCC_EMERG_C_QVW
EMERGENCY_PHONE -- SCC_EMERG_P_QVW
EYE_EXAM -- SCC_EYE_EXA_QVW
NAMES -- SCC_NAMES_QVW
PERSON -- PERSON_NPC_VW
PERS_DATA_EFFDT -- SCC_PER_EFF_QVW
PERS_DATA_CAN -- SCC_PDE_CAN_QVW
PERS_DATA_USA -- SCC_PDE_USA_QVW
PERS_NID -- SCC_PERS_NI_QVW
PERSON_PHONE -- SCC_PERS_PH_QVW
PHYSICAL_EXAM -- SCC_PHYS_EX_QVW
PUBLICATIONS -- SCC_PUBLICA_QVW
RESPIRATORY_EXM -- SCC_RESP_EX_QVW
VISA_PMT_DATA -- SCC_VISA_P_QVW
VISA_PMT_SUPPRT -- SCC_VISA_S_QVW


Query Views to be used in place of core Person Model Tables with PeopleSoft
Query for Campus Solutions reporting needs.

PeopleSoft Trace provides a lot useful information to find the bugs, issues and understand the application state. The main problem with this is it provides a so much of information that it hardly gets used completely.

PeopleSoft Trace is not very reader friendly, with a lot of information overload. How can we debug the code provide the details of the critical variable values. Many Application Framework come with support for the Custom Logging capability.

Application Logging is very much different for what we use "WinMessage" to debug and get the values of the variables. The main problem with this is every time you have to remove the code for winmessage and add the code back when you want to debug the same PeopleCode. What if you had switch to control the "Winmessage" like debug statements work only when it is required and not always. ??  Welcome to "Application Logging".
Application Logging is the answer for this and there is 2 options that we can use withe PeopleSoft (Atleast for my knowledge).

How can you achieve this in the PeopleSoft?
1) PeopleSoft do have one which can use for the Application Log facility. Application logging enables you to do error logging using an independent application log fence mechanism. It also enables you to write to the PeopleTools log using the WriteToLog built-in function
In PeopleTools, a log fence is a type of barrier. Application error messages are only written to the PeopleTools log if the log fence setting that the messages are written to the log with (using WriteToLog) is less than or equal to the current application log fence setting (AppLogFence) in the application server configuration file (PSAPPSRV.CFG ).
Following document will explain in more detail about this.
http://docs.oracle.com/cd/E38689_01/pt853pbr0/eng/pt/tpcd/task_UsingApplicationLogging-0749d1.html
2) We do have custom method that we can use for the logging the application. Custom method - well not really custom method, we basically using the JavaAPI - Log4J - is an standard Logging method for the Java based applications. Since PeopleSoft supports the Java we can use this mechanism to get the application log.
Since it's framework and works with Java - Any version of the PeopleSoft can adapt to this method.
You can configure the log level, targets, and layouts from a configuration file without touching your production code. You can change the Log Level using the Peoplecode Dynamically.

You can get more information about this in the below link, where Jim has explained about the Log4J method.

http://jjmpsj.blogspot.in/2006/09/logging-peoplecode_15.html

In PeopleSoft ERP Projects, PeopleSoft Admins have to create several instances, refresh production to Development and Test Instances. These are not just database copy there are so many tasks that needs to be completed after the PeopleSoft database is copied. Starting from Integration Broker configuration, PTF configuration, SES configuration and several other tasks that needs to be executed.

In 8.53 new feature Automated Configuration management was released in trail basis, in 8.54 this feature is full implemented and provided to be use easily.
What it does basically is to create to automate the configuration of the PeopleSoft environments with single click of a button. We need to save the configuration information in a Template, which saved as Template file. Template just is a collections of the Plug-ins which configure the PeopleSoft Environment.

Plug-ins take input parameter values and on execute take care of the configuration and manual configuration is not required. For example, the value for your Integration Gateway URL, which you would normally add manually on the Gateways page, can be entered in your template or template file once, and then retrieved by the automated configuration management framework and inserted into the database each time you refresh that environment.

 You can create the Template for all the related configuration Plug-ins and execute them as required. It can be executed in the PIA - Application Engine or it can be executed from the command line.
Now, you may be wondering what is an Plug-ins, these are just Application Class. For example, to configure IB Gateway nodes, the application class(plug-in) PTIBConfigureGatewayNodes in the application package PTEM_CONFIG is used.

There are 18 devlivered plug-ins that are available, these configurations are mainly available for
1) Integration Broker
2) Search Framework Configuration
3) PTF Configuration
 4) Process Scheduler
5) Performance Monitor configuration.

After setting up the basic infrastructure of a PeopleSoft environment, including database, application server, Process Scheduler server, and PIA domain, you run the configuration program (either using PIA or command line). This configuration program reads your environment properties from the template or template file, and inserts the stored values into the database, saving you from updating the settings manually each and every time you create or refresh an environment.
Sensitive data, like passwords, can be encrypted so that these parameters are not exposed if sharing the property file among multiple environments and multiple development or testing teams. With simple option of the encrypt_password = "true" and decrypt_password = 'true'.

There is an option to Verify the configuration using this framework - Automated Environment Configuration framework verifies the settings. The parameters provided in the property file are validated against the corresponding values inserted into the database to ensure they are identical.
You can monitor the execution of the Template, View the results in the PIA page, You can review results after execution  with icon displaying against each plugin like Success, Error, Manual Verification.

You can check for the dependency between the Plug-ins.
You assign a value to a template variable which is in-turn used to assign values for properties in cases where values are recurring in multiple places in a template or template file.

 Extending Plugins and Creating Plugins - Plugins are Applications Classes and hence it is easy to Extend the features and create new plugins based on the project requirement.
After you create a template, you must add the template to a permission list. PeopleTools provides the PTPT4800 specifically for the purpose of automated configuration.
Use the ACM Templates page to add a template to the PTPT4800 permission list.If a template is not added to the PTPT4800 permission list, you cannot use the template for a configuration run.
Use the PTEM_CONFIG application package to create a plug-in. If you need to use a custom package, ensure that the custom package starts with PTEM_CONFIG. Write the plug-in class. The new plug-in class must extend from the base class — PTEM_CONFIG:EMConfigurationPlugin.
Override and implement the four base class methods
1) getProperties
2) dependant_plugins
3) getPluginHelpMessage
4) isInternalPlugin

Register the Custom Plugin in the following location.

PeopleTools, then selectAutomated Config Manager, then selectACM Utilities, then select Register Plugin

PeopleSoft ERP delivered Business Process might not be application for all customers. Customer prefer to change the PeopleSoft rather than change their business process to suite their requirements.
Customizing PeopleSoft all time is not good idea, can we avoid it ? No, we cannot avoid customization to PeopleSoft Application as it might not suite customer requirements.
Lets see what are the options provided by PeopleSoft to avoid Customization to delivered objects so that it will be easy to handle during the customization and retro-fit efforts are reduced.
1) Most common customization is to add additional fields on the delivered components to Capture additional information.
    a) Related Content Framework - it can be used to display additional custom page in the delivered component where additional parameters can be captured. It is very powerful solution where you can display the page from PeopleSoft, Query Results, External Application, Web applications.
      Related Action Framework - Will help to related different related transaction.
   b) Common Attribute Framework - It is an feature in the Campus Solution - where many of the delivered component related to Student/Prospect Personal information loaded from PDL process and processed from admission application. Using the simple configuration we can store additional values in different format like Scroll and Attributes. This can be applicable for other pages with minimal configuration. This feature should be available for HCM and FSCM as well.
2) Creating New Custom Approval Workflow Process.
   Workflow in an Important feature required of the Business Process, most of the configuration for AWE can be done online, but what if the customer need New Approval Process ?
PeopleSoft Forms and Approval Builder enables business analysts to create electronic, routable forms without having to use PeopleSoft Application Designer. A typical form can be designed and in use within 30 minutes and requires no writing of code. The intuitive wizard guides you step by step through establishing a form. Options include adding instructions to the form, similar to what you would find on the back of a paper form; adding attachments to the form for users to read, submit, or both; and being able to create your own fields on the form. On Final Approval - Component Update from CI is possible using the configuration.
This is available from PeopleSoft 9.1 on-wards and hence available for Peopleoft HCM, FSCM, CRM only and yet to available for PeopleSoft Campus Solution.
3) Creating an Webservice/Interface for the External System to get the real-time data from PeopleSoft.
   a) CI Based Web service - Which doesn't need any code development where component can be operated easily and no coding and customization is required.
    b) If the data is required to be fetched from other than the component - CI will not be applicable.
QAS - Query as Web service - Creating Simple Query can pull the data from multiple records and apply data security and other business logic. It is easy to create this webservice and third party system can make use of it. It can be simple interface for the Down stream system.
4) Reading Inbound interface file or loading the data from file - It is one of the common requirement.
    File Parser  - is a Campus Solution feature it not available for HCM, FSCM and CRM Application. it is very useful to load any data from File, without any coding required.
5)  Sending Emails/Work list/Notifications for different users on Events in the PeopleSoft Application -
  Following method doesn't avoid customization but reduce the amount customization and retro-fit effort. It will be standard method to send Worklist, Emails - Which can be easily tracked. It should be used as standard method and Best practice as it has a lot features that can used with less customization.
Events and Notifications Framework
The PeopleSoft Events and Notifications framework provides three features that can be configured and used to monitor business processes and create messages when unusual situations or errors occur within a PeopleSoft process or table. These messages can be routed to different users (PeopleSoft or non-PeopleSoft) to prompt the user to resolve the issue. Links can take the user directly to the page for correction or resolution.
The three features are: • Events • Notifications • Alerts

Following is the simple code Raise the event, Event handler can handle the rest.
import EOEN_MVC:EOEN_MODEL:EOENInterface;
Local EOEN_MVC:EOEN_MODEL:EOENInterface &myEvent;
Local Record &recContext;
&myEvent = create EOEN_MVC:EOEN_MODEL:EOENInterface("", 0);
&recContext = &rsContextRec(1).GetRecord(Record.CONTEXT_REC);

&myEvent.AddContextRecord(&recContext); &myEvent.RaiseEvent("EventName");

Oracle Learning Library
__________________

This has a lot of trainings related to the new features released in 8.53 and 8.52 version.


http://apex.oracle.com/pls/apex/f?p=44785:2:0:::2:P2_GROUP_ID:1022

Creating a PeopleSoft Mockup page while creating the Functional Design document and Requirement collection process is very important in the PeopleSoft Consulting Projects.

Usually Paint and screen captures are used to create the mock-ups.

Here find the the link to excel document, which has the collection of all the possible images/icons that you need to create easy mock-up pages quickly.

PeopleSoft  Mockup Template

Download the file to view it in the Microsoft Excel.

TraceMagic is a utility that gives PeopleSoft system administrators, programmers and support engineers the ability to quickly isolate performance bottlenecks in SQL Statements and/or PeopleCode functions. It accomplishes this by turning the text-based, time-ordered tracesql file into a sortable-grid display, allowing the user to quickly locate system performance issues.

Requirements

•     Windows XP (32-bit)
•     Windows 7 (32-bit)
•     Windows 7 (64-bit)

Download link - Login Required.
https://support.oracle.com/epmos/downloadattachmentprocessor?attachid=1470578.1%3ATM_VERSION1.2&docType=DIAGNOSTIC%20TOOLS&action=download

A new feature in PeopleTools 8.52 are popup modal prompts. These are automatically enabled wherever the PeopleCode PROMPT function is used. You most often see this in Query Manager prompts.


Prompt("Enter EMAIL ID" &TLMlbx1Str, "",TL_MAILBOX_WRK1.EMAIL, "Enter Enter Emailid ",&TLEMAILID);

This prompt similar to PS Query for the Bind parameter but, here it can be used on the regular PeopleSoft Components.