Excel - to CI - Compile error - User Defined Type not defined.

When using ExcelToCI on Windows 10, trying to load template or stage and submit data, it gave a 'Compile error' that reference to Visual Basic code reference to DOMDocument object. 

Users usually get a "Compile error: User-defined type not defined" error

This not an error in the Windows 7 OS, it is an issue with Windows 10. More corporate users are now moving the Windows 10 as the support earlier OS is coming to an end.

More users are facing the issue with the Excel-to-CI on there Windows 10 system. There are multiple solutions on different blogs to update the code (which works).

For easy access, I have attached the complete template for 8.54 and 8.55 (and Above) tools versions to the user the Excel to CI template for the Windows 10 OS.

This is provided by Oracle and it works fine in both old and new OS.


PSQuery Performance issues with Rowlevel Query Security Tables

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 or a Job name

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.

     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)
                       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.

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;


Sending EMAILS in PeopleSoft - Deep Dive !! - Different Options and Other details

Sending an email is very simple and easy concept, but there are so many different things that we need to keep in mind from best practices to new additional methods that can be useful in different purpose. I am trying to cover all the different aspects of sending emails, this will be Bible for sending emails in the PeopleSoft.

Best Practice
1)  Anything that works is not correct or best method to follow while writing the PeopleCode. First thing comes to the mind - SendMail function to send the email, it is an deprecated method and should not be used. Application Class MCFOutboundEmail needs to be used and it is the fully SMTP Protocol compliant to RFC 821/822. It uses Javamail functionality using this package.

Where to Configure
MCFOutBoundEmail() function uses the SMTP section of the Application Server's psappsrv.cfg file, and Process Scheduler's SMTP section of the psprcs.cfg file, just as the SendMail() function does. No special configuration is required for the MCFOutboundEmail() function to work.

Sample Code send Email
import PT_MCF_MAIL:*;
import PT_MCF_MAIL:MCFOutboundEmail;
import PT_MCF_MAIL:MCFEmail;
Local PT_MCF_MAIL:MCFOutboundEmail &email = create PT_MCF_MAIL:MCFOutboundEmail();
&email.Recipients = "FINQAGenUser10@ap6023fems.us.oracle.com";
&email.From = "";
&email.BCC = "";
&email.Subject = "MCF Outbound Email test";
&email.ReplyTo = "";
&email.Text = "Email Body - This is a Test of the MCF Outbound Email";
&res = &email.Send();

Best Performance for sending Bulk email 
 if you need to send several emails where content is specific to each email is different. Following code will help if you need to send email notification on the component peoplecode, as it make one SMTP connection to send all the emails and performance will be better.
import PT_MCF_MAIL:MCFOutboundEmail;
import PT_MCF_MAIL:MCFBodyPart;
import PT_MCF_MAIL:MCFMultipart;
import PT_MCF_MAIL:MCFMailUtil;
import PT_MCF_MAIL:SMTPSession;
Local integer &noOfMails = 20;
Local array of PT_MCF_MAIL:MCFOutboundEmail &mails;
Local PT_MCF_MAIL:MCFOutboundEmail &email;
Local integer &i;
Local PT_MCF_MAIL:SMTPSession &commonSession = create PT_MCF_MAIL:SMTPSession();
&commonSession.Server = "ap6023fems.us.oracle.com";
&commonSession.Port = 25;
&mails = CreateArray(&email);
For &i = 1 To &noOfMails
&email = &commonSession.CreateOutboundEmail();
&email.From = "sender@sender.com";
&email.Recipients = "receiver@receiver.com";
&email.BCC = "sender@sender.com";
&email.Subject = "Mail: " | NumberToString("2.0", &i) | " Hi there";
&email.Text = "Mail No: " | NumberToString("2.0", &i) | "One of multiple mails from from java";
&email.ContentType = "text/html";
&mails &i = &email;

Local PT_MCF_MAIL:MCFMailUtil &util = create PT_MCF_MAIL:MCFMailUtil();
Local array of integer &allRes = &commonSession.SendAll(&mails);

In case if mails are not working for you and you are not able to figure out the issue.
You should look into smtp logs for more details and it will tell you exactly what is happening.
Before using the SMTP logs , you need to enable it.
For enabling SMTP logs for App Server you need to edit the app server configuration file :psappsrv.cfg.
Open the psappsrv.cfg file  and find “SMTP”.Look for SMTPTrace variable.
It will be SMTPTrace=0 initially.

Set the value of this variable SMTPTrace=1 and save the file.
This change does not require a Application Server boot and changes will be reflected immediately.
If you are using mail classes from Process Scheduler, you may need to set their trace also.
The psprcs.cfg file needs to be modified for this.
Open the file and look for SMTP and change the settings as done in the App server configuration file.
It will generate Trace file SMTP.LOG in LOGS Folder under
$PS_HOMEappservdomain_namelogsSMTP.LOG  for Appserver and $PS_HOMEappservprcsdomain_namelogsSMTP.LOG for Batchserver.

Creating an Email from Rich Text Editor Output
Using the ParseRichTextHTML - you can send the Rich Text with messages. Follwoing link has the complete details of the sample code.

Validating Email Address
ValidateAddress(addresslist) - Use the ValidateAddress method to validate a comma- or semicolon-separated list of email addresses. This method checks the syntax of the email address. It does not verify the domain name or the validity of the user name.
import PT_MCF_MAIL:*;
&emailutil = create PT_MCF_MAIL:MCFMailUtil();
Local boolean &result = &emailutil.ValidateAddress(&email.Recipients);
Local array of string &bAddresses = &emailutil.badaddresses;
If (&result = False) Then
If (&bAddresses <> Null) Then
&i = 0;
While &bAddresses.Next(&i)
Warning ("Bad email address in input = " | &bAddresses [&i]);

Creating an HTML Email with Images
Sending the HTML content and sending the Client Image may be requried. It looks good to send them in the email, Rather than the boring static emails which doesn't have much of details in it.
Following link has the details of the code to send the same.

2)  Notification Framework Another option is not explored very well by developers to send emails.  It can be easy way sending the email for many scenarios, where code can be simple and  provide configurable options.

The Notifications Framework uses the generic templates of the PeopleTools Workflow Technology, and the reader is urged to review the information in PeopleTools: Workflow Technology, “Using Notification Templates,” for a more comprehensive understanding.

 Entity Registry to generalize all notifications into a single structure. The architecture is modeled on a pluggable channel-based approach. Each notification type is supported by a dedicated channel that supports the idiosyncrasies of the particular notification type.

After creating Templates

PeopleTools, then select  Workflow, then selectNotifications, then selectGeneric Templates 

Notification Setup page (select Set Up SACR, then select System Administration, then select Utilities, then select Notifications, then select Notification Setup)

Notification Consumer Setup page (select Set Up SACR, then select System Administration, then select Utilities, then select Notifications, then select Notification Consumer Setup).

Write an AppClass that extends AbstractNotification class. The Consumer ID must be passed to the Super Class constructor
Implement the abstract method createNtfContext().
Implement the abstract method populateNtfContext(). This method populates the context create above with the necessary values 
Invoke the send(), remind(), or resend() methods.
Override the OnSuccess() and OnError() for custom behavior

Consultants can refer to the Delegated Access Application Class SCC_DA:NOTIFY as a reference for how to trigger the Notifications Framework as a consumer.


How HCM and CS is different in providing Row Level Security to an User ID ?

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

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


Write Custom Messages to PeopleSoft Trace File to Help Debugging ?

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.
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.



Automated Configuration Management - Automate Configuration

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


Methods to Reduce Customization/Modification in PeopleSoft

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.
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

Oracle Learning Library

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



Script to update the Tablespace for all the records in the Project.

Script to update the Tablespace for all the records in the Project. when we import Project in the client instance, we can use this SQL to change the tablespace for all the records at time, rather changing it one by one.



Test third Party webservices integration with PeopleSoft

Test third Party intergration w/ sample Peoplecode

Consume third Party web services.

Download the document for all the steps.


Creating PeopleSoft Mockup Pages Made Easy

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: Utility for analyzing *.tracesql trace files

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.

TraceMagic is designed primarily to address the difficulty in working with tracesql files when attempting to
determine which SQL statement(s), and or PeopleCode programs are causing performance problems. As such, it tends to assume that certain trace flags were set during the creation of the trace file.


Trace Magic requires Version 4 of the Microsoft dot.net Framework. The setup.exe program used to install TraceMagic on an end users machine will
 inspect the target system for the presence of the prerequisite software, and request permission from the installer to download and install the
 required software if needed. TraceMagic has been tested on the following platforms:
  •     Windows XP (32-bit)
  •     Windows 7 (32-bit)
  •     Windows 7 (64-bit)

Other platforms may or may not function correctly with TraceMagic. Platforms that will not run the Microsoft dot.net framework will not
likely run TraceMagic.

Download link - Login Required.


PeopleTools 8.52 popup prompts

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.