Shyam's Blog | Oracle Cloud, Peoplesoft, Workday

Oracle HCM Spreadsheet loader shows as success but changes won't reflect in UI - User.dat business object

noreply@blogger.com (Shyam) — Mon, 4 Oct 2021 18:48:00 +0530

There is an issue in Oracle HCM that after running HCM Spreadsheet data loader(HSDL) for User.dat - Business Object, the load shows as success but the changes are not visible in the UI. The issue is the same with Create, Update, and Delete, where status shows as Success in spreadsheet loader, but changes are not reflected in the UI.

Similarly it happens also with HCM Data Loader (HDL) for User.dat file gets loaded successfully, the file might have MERGE/DELETE doesn't matter, but changes are not visible in the UI irrespective of whether user id was created, or roles were added/dropped to the user, or any other changes, or even if the user id was deleted. HDL will show status as Success but the changes are not visible in Oracle HCM Cloud application.

Resolution:

Whether it is HDL or HSDL, for User.dat business object, the changes will not be immediately visible in the UI. Run the process 'Send Pending LDAP Requests' and it should resolve the issue. To know more on Send Pending LDAP Requests - read it here from 21B version of Oracle documentation itself.

Issue 3:

Created new users, or modified roles(role provisioning), or updated user accounts through HDL with User.dat file or HSDL for User business object, the data loading status is success. Or you could have tried the same via a REST API call.
Ran 'Send Pending LDAP Requests' job which also went to success.

Solution:

In "Manage Enterprise HCM Information" task, under the section "User and Role Provisioning Information", check if User Account Creation is set as None. If it is set to None, change it to 'Party users' or 'Both person and party users' as required.
Depending on the need on whether you are doing user account maintenance or user account role provisioning, modify the values accordingly in the same section.
Then run the job - 'Send Pending LDAP Requests'

A key thing to note is 'Send Pending LDAP Requests' job doesn't run when User Account Maintenance is set to No.

How to add a missing Person ID to a User ID/Account in Oracle Fusion HCM Cloud

noreply@blogger.com (Shyam) — Sun, 3 Oct 2021 17:20:00 +0530

There are couple of issue around adding a person id to a user account in oracle HCM suit. Below are the couple of use cases/ issue which we will discuss deep in this post.

Issue 1: When creating a user account in Oracle HCM, Person ID was not associated and at a later stage unable to associate a person id to the user account. So, how to associate a Person ID with an existing user account in Oracle HCM?

Issue 2: On clicking My Client Groups > Learning > Any available option, triggers an error message in Oracle HCM that - "You cannot access this page because no person ID exists for your user name."

This happens because the user accessing the Learn pages does not have a person ID mapped with their user account. Either use an user account with person ID, and Learning Specialist role or follow the below steps to link a person ID to an user account.

Solution for Mentioned Oracle HCM issue:

My Client Groups > Person Management
Search for the required person, and click on Person Name. Please make sure to select a person id who is active, and does not have an user account already.
Click on Tasks > Personal and Employment > User Account
If the selected person does not have an user account(as specified in step 2), then it would present two options - i. Link an Existing User Account and ii. Create a User Account
Click on 'Link an Existing User Account'
Now in 'Link User Account', from the dropdown for 'User Name' field select the required user.
In case personal data needs to be copied to LDAP, from Actions click on 'Synchronize With Identity Store' then 'Synchronize' else skip this step.

Backdrop information - you might be interested to know

When a person is hired in Oracle HCM\Fusion Application(FA), or batch of users loaded, the user records will be created in Oracle Identity Manager(OIM) with the attributes - FA Person ID and FA User ID. Once we link a User Account to a person ID, application will automatically trigger update in the background to update FA Person ID and FA User ID attributes in OIM.

View option not available in Oracle Fusion Page Composer

noreply@blogger.com (Shyam) — Sat, 21 Nov 2020 15:45:00 +0530

Hi reader, If you have any of the below questions on oracle fusion , then you are in the right page.

Oracle Fusion users cannot see the source view when customizing pages?
Source view is not available when customizing pages. How to resolve it?
Why source view and design view not available in page composer - Oracle Fusion?

Yes , let us see how to resolve the above mentioned Oracle Fusion issue that "View option not available in page composer"

There are two Page Composer Views

Design view
Source view

To open a view option, the required view option(Design/Source) must be selected from the View menu at the top eft former of the page.

Both these view options share many common page modification features, some unique features are available in each view.

Root Cause of the issue:

There is a new profile option for customization that needs to be enabled. The new profile option is FND_PAGE_COMPOSER_SOURCE_VIEW which is used to render the Source View to support customization.

Solution to resolve the issue

Navigate to Setup and Maintenance
From the ‘Tasks’ list on the mid right page, select ‘Search’
Search for the task - Manage Administrator Profile Values
In the ‘Search : Profile Option’, search for the ‘Profile Option Code’ - FND_PAGE_COMPOSER_SOURCE_VIEW
At the end of the page, under Profile Values
For Profile Level – Site, change the profile Value from No to Yes
Click on Save/Save and Close

Log out and log in back again and go to sandbox
Navigate to the required page to make changes, ‘View’ menu should be available now with ‘Design’ as the default option. Design View is the default option for SUI.

PeopleTools Tables - Part 6 - Other PeopleTools important Tables

noreply@blogger.com (Shyam) — Thu, 13 Jun 2019 19:44:00 +0530

I have listed the PeopleTools tables in a series of posts earlier and this one is the last in this series.

You can check the previous 5 posts here:

PeopleTools tables related to PS-Queries
PeopleSoft Security definition tables
Definitions created in App designer
PeopleTools tables related to Application engine
PeopleTools tables related to the PeopleSoft Process Scheduler

Here are some more PeopleTools tables across various categories.

Other Peoplesoft Tables

Peoplesoft Portal Tables

PSPRSMDEFN - Content References and Folders
PSPRUHTABPGLT - Portal User HP Tab Pagelet
PSPRUHDEFN - Homepage definition
PSPRUHTAB - Homepage Tab
PSWEBPROFNVP - Web Profile Settings

Peoplesoft Change Control related Tables

PSCHGCTLHIST - shows history of locked definitions with project name, incident, and description
PSCHGCTLLOCK - shows definitions that are currently locked

Peoplesoft HTML Definitions

PSCONTDEFN - shows header record; last update time, etc.
PSCONTENT - stores the actual text in the HTML definition

Peoplesoft SQL Definitions tables

PSSQLDEFN - header record; last update time, etc.
PSSQLTEXTDEFN - stores the actual text in the SQL definition

File Layout Definitions

PSFLDDEFN - header record; last update time, etc.
PSFLDSEGDEFN - stores the segments for each layout
PSFLDFIELDDEFN - stores the fields for each layout

Peoplesoft Workflow related tables

APPR_RULE_DETL - Approval Rule Definition Details
APPR_RULE_FIELD - Approval Rule Definition Route Cntl
APPR_RULE_AMT - Approval Rule Amounts
RTE_CNTL_LN - Route Control Profile Line
RTE_CNTL_RUSER - RoleUser Route Control Profiles
RTE_CNTL_TYPE - Route Control Type
RTE_CNTL_HDR - Routing Control Type
PSWORKLIST - list of work items for each user
PS_WF_INSTSTATUS - description of the status

Peoplesoft Timings related tables

BAT_TIMINGS_LOG
BAT_TIMINGS_DTL
BAT_TIMINGS_FN

COBOL\SQL Statements

PS_SQLSTMT_TBL - To know more about this table, check out my earlier post on All about Stored Statements

PeopleTools Tables - Part 5 - Process Scheduler Tables

noreply@blogger.com (Shyam) — Wed, 12 Jun 2019 18:41:00 +0530

In this post, I have listed down the PeopleTools metatables related to PeopleSoft Process Scheduler.

Please do check out my previous posts on PeopleTools tables related to PS-Queries, PeopleSoft Security definitions, definitions created in App designer, and PeopleTools tables related to Application engine.

You may also want to know the list of tables affected by PeopleSoft Process Scheduler purge process - PRSYSPURGE. You can find more about PeopleSoft purge process for Process Scheduler in one of my previous posts.

Process Scheduler Tables

List of PeopleSoft Process Scheduler tables

PS_PRCSDEFN - Process Definition Header
PS_PRCSDEFNGRP - Process Group
PS_PRCSDEFNPNL - Component
PS_PRCSJOBDEFN - Job Header
PSPRCSRQST - Process Request Instances
PS_PRCSJOBITEM - Job Processes
PSPRCSQUE - Process Request Queue
PSPRCSPARMS - Contains process request parameters used by Process Scheduler to run the process
PS_PRCSRQSTDIST - Content Dist Manager - User access. This table is used to authorize user access to the content
PS_MESSAGE_LOG - Message log table
PSPRCSRQSTTEXT - Process Request Text. Contains all long text fields associated with a process request
PS_MESSAGE_LOGPARM - Parameters used for Message log
PSPRCSRQSTXFER - Process Request - Transfer. This record contains the panel transfer keys for a process request that has a panel transfer in it's definition.
PS_CDM_LIST - Content Distribution Manager - Content List. This record is used by Content Distribution Manager to sore the content list that are sent to the content repository.
PRCSDEFNLANG - Related language for all process definitions
PRCSDEFNXFER - Process definition - Panel transfers. Used to setup information related to allow users to transfer to a desired page upon successful completion of the program from Process Monitor
PRCSDEFNNOTIFY - Process definition - notifications. The table contains information on all the users or roles to be notified based on the process status
PRCSDEFNCNTDIST - Process definition - Distribution. The table contains list of users or roles authorized to view all generated reports and/or logs for the process
PRCSDEFNMESSAGE - Process definition messages
PRCSDEFNMETA - used to sore any metastring for JCL in OS 390.
PS_PRCSDEFNURL
PS_PRCSDEFNURL_LNG
PS_PRCSDEFNURLKEY

PeopleTools Tables - Part 4 - Application Engine Related PeopleTools Tables

noreply@blogger.com (Shyam) — Mon, 10 Jun 2019 20:41:00 +0530

After posting on PeopleTools tables related to PS-Queries, PeopleSoft Security definitions and for definitions created in App designer, here is my next one in this series listing the PeopleTools tables related to Application engine.

App Engine - PeopleTools Tables

PeopleTools tables related to Application engine

PSAEAPPLDEFN - header record; 1 row per app engine
PSAEAPPLSTATE - state records associated with app engines
PSAEAPPLTEMPTBL - temporary tables assigned to app engines
PSAESECTDEFN - information on sections in an app engine
PSAESTEPDEFN - information on steps in an app engine
PSAESTEPMSGDEFN - App Engine message action definition
PSAEAPPLLANG
PSAESTMTDEFN - App Engine Statement definition. It has a field AE_STMT_TYPE field and the possible values are listed below. This is useful if you are looking for an Application Engine with a certain type of action.

H = Do When
N = Do Until
C = Call Section
W = Do While
X = XSLT
M = Log Message
S = SQL
P = PeopleCode
D = DoSelect

PSAESECTDTLDEFN - Detailed definition of AE Sections
PS_AEINSTANCENBR - Table contains temporary instance numbers. This table has all possible temporary instance numbers (currently 1-99). This table is necessary to find a number that is not being used in PSAETEMPTBLMGR. To know more about, usage of temporary table instances in App Engine, please check out my previous post on Temporary Table Instances - Overview
PS_AELOCKMGR - App Engine Lock. This record is used to ensure single threaded updates to the temp table usage.

Microsoft Azure and Oracle Cloud partner to directly link their clouds

noreply@blogger.com (Shyam) — Thu, 6 Jun 2019 23:40:00 +0530

Microsoft and Oracle have entered into an alliance to interconnect Microsoft and Oracle clouds aimed at accelerating Enterprise cloud adoption.

Microsoft Azure and Oracle Alliance

Background of Microsoft - Oracle alliance

Enterprises widely use Microsoft solutions to power their business needs, and Oracle products primarily for the database and reporting needs. When the enterprises migrate their applications and data to the cloud, Microsoft products may get migrated to Azure along with third-party offerings while Oracle offerings are typically moved to Oracle Cloud Infrastructure (OCI). There is also an option of moving to AWS or Google cloud, let's set aside the cost implications of this route. This movement to isolated cloud partners can often result in strained interoperability between these multiple cloud islands.

The announcement of an alliance between Microsoft Azure and Oracle Cloud seems an industry first large scale offering, which gives customers to use both these cloud products seamlessly with greater effectiveness. This can mean that customers can also shift products cloud to cloud easily, based on the merits and also deploy applications spanning multiple clouds.

What exactly is the alliance between Microsoft and Oracle?

Microsoft and Oracle on June 5th, 2019, announced a cloud interoperability partnership enabling customers to migrate and run enterprise workloads across Microsoft Azure and Oracle Cloud. Microsoft and Oracle will partner deeply across Azure and Oracle Cloud, directly linking their data centers with high-speed links. The alliance is not just about basic connectivity, but also includes interoperability between Azure and Oracle Cloud Infrastructure (OCI) and uniting against the other cloud computing giant - Amazon Web Services. The direct interconnect is available immediately starting from the day of this announcement between the two clouds in Azure US and Oracle's Ashburn data center. This could later expand to other data centers of Microsoft and Oracle though details are not available as of now.

What does this alliance mean to customers?

For customers using Azure services like Analytics and AI, can connect to Oracle cloud services like Oracle Autonomous Database running on the Exadata infrastructure in OCI.

For Oracle customers, this partnership means they can deploy and run services like Oracle E-Business Suite, PeopleSoft, JD Edwards, Hyperion, Oracle Retail and other custom applications on Azure leveraging its scalability and flexibility while still using the database in Oracle Cloud Infrastructure like RAC, Exadata and Oracle Autonomous Database thereby taking full advantage of Oracle's product capabilities.

Oracle database will continue to be certified to run in Azure on various operating systems, including Microsoft and Linux, allowing customers to extend their on-premise data centers to both clouds.

Applications in both Azure and Oracle clouds can connect seamlessly and work without any latency

Unified Identity and Access Management with single sign-on experience across Azure and Oracle Cloud. It is also interesting to note that, Oracle applications can now use Azure Active Directory for identity management.

What do business leaders of Microsoft and Oracle say of this alliance?

Oracle is a natural choice for Microsoft enabling accelerated migration of enterprise applications and database to the public cloud - Scott Guthrie, Executive Vice President of Microsoft’s Cloud and AI division

Oracle and Microsoft have been long serving the enterprise needs and this partnership will enable customers to move their entire set of applications to cloud without a necessity to re-architect anything - Don Johnson, Executive Vice President, Oracle Cloud Infrastructure

Current ERP market scenario in the public cloud

Though, this partnership between these two major players raises eyebrows it is not a huge surprise considering that Oracle wants to be seen as a major player in its offerings and may have realized that it is unlikely to get to the size of Azure or AWS or Google cloud anytime soon.

It is worth remembering that, Microsoft has earlier entered into a similar pact - the Open Data Initiative with SAP and Adobe to enable their services to work better.

This announcement comes approximately six months after Workday announced that its customers can run Workday on Amazon Web Services (AWS). Workday continues to run its own data centers while this partnership with AWS gives organizations the flexibility to have their data in their own country\regions meeting their Data Sovereignty requirements. Workday came up with AWS option way back in 2016, however, only for Canadian customers and a select few customers in the US.

Workday - Workload Automation - How to Automate Workday Jobs

noreply@blogger.com (Unknown) — Sun, 12 May 2019 03:23:00 +0530

In this guest post, Venkatesh Manian discusses the challenges and problems faced in handling Workday integration jobs with the use of Workload automation tools and explores possible solutions.

As technology continuously evolves, it provides greater and newer capabilities in solving the existing problems or reduces the mundane and manual jobs. Cloud computing was one such IT wave and ERP products were not far behind to ride the cloud wave. Workday – cloud ERP is a pathbreaker in many ways in the ERP market and leads in terms of technology innovations rather than just keeping pace with the technological advancements. Workday being a cloud product, integration with various in-house internal and external systems is inevitable and Workday provides simpler and intuitive integration capabilities through Workday Integrations. Workday Integrations make it easier to connect with other systems like ADP, Fidelity, BSS, etc.

Workday - Workload Automation

However, with the larger population of the vendor applications trying to stay integrated with Workday and employee's it still difficult to manage the integrations. With Workday’s customer base increasing continuously the workload automation system within Workday will gain prominence as the customers move on to the next stage post implementation of Workday. This post is an introductory and research article in its baby steps, on the various options around workload automation. First up, here are some basics for beginners on workload automation and Workday integration ecosystems:

Workday Web Services

Workday implements WWS (Workday Web Services) with industrial standards to provide a public API for our On-demand Business Management Services and keep on enhance using version control.

WWS is a service that uses an XML messaging system to provide the various operations of Workday. In all the operational domains, Workday provides a number of web services to do the GET and PUT operations.

Workday also provides web service in Integration Domain as well. As on the publication of this post, Workday provides Web Service: Integrations (v32.0) and it provides 25 operations related to all integrations within the Workday system primarily based upon Get and Put services.

Workload Automation Tools

Workload automation tools are used to manage and automate background processes. A typical Workload automation tool will process various background processes across multiple business systems as needed. This reduces the turnaround time for workflow across multiple platforms and products and subsequently reduces delays from end-to-end business processes. These automation tools also provide a centralized view of all the jobs across platforms and the best thing is they can be dependent on the other platform jobs as well.

In traditional on-premise products, workload automation software such as AutoSys, Control-M, JAMS, vCloud, Automate, and several other similar products are used to schedule, initiate, run and manage the batch jobs. Apart from the automation of job processing, these tools provide us great capabilities of monitoring and alerting features that help in ensuring timeliness between various data exchange partners.

Altogether we have below advantages of using a workload automation tool

Workload automation tools have event-driven scheduling providing options to ensure successful completion of jobs within time and in the desired manner.
Facilitates synchronizing between cross domain jobs.
Custom notifications via e-mail, SMS, calls, on job events.
Simplified file transfers for Inbound and Outbound jobs.
Enables well-structured and unstructured calendar driven job scheduling
Logging of data and output responses for auditing

In a tool like Control-M, it is possible to have a file watcher job, which will trigger a PeopleSoft(or similar ERP) job which needs to process the inbound file and after this job completion, we can have a downstream job which can run in a third-party system outside the ERP. In Workday too, integrations can pull the file directly from the FTP server and start processing. However, Workday integrations do not support file watcher or daemon processes that continue to wait for a particular input condition and trigger the required dependent jobs. Due to this, an inbound file delay by even a single second can cause the integration to fail. This situation can be handled better using workload automation tools by having integration jobs followed by a file watcher.

Workday Job scheduling ideology

Workday being a cloud product, jobs will be executed in the cloud. We need to use web services to synchronize job events. We can divide this synchronization into two steps as given below:

Job triggering or initialization
Execution and status retrieval

Below are the Workday web service operations from integration WWS that can help us in the above phases.

WebService Operations	Description
Launch_EIB	This operation will Launch an EIB Integration. The request has the ability to accept override Runtime Parameters that should be used within the integration processing. If no override is provided within the request, the System Default, which has been defined on the EIB Definition, will be used. The result of this operation will be the creation of an Integration Event along with the processing of the integration itself.
Launch_Integration	This operation will Launch an Integration. The request has the ability to accept Runtime Parameters and the Workday Account that should be used within the integration processing. The result of this operation will be the creation of an Integration Event along with the processing of the integration itself.
Get_Integration_Events	This operation will retrieve data related to the execution of an Integration Event. Data included will be Integration System, Launch datetime, Triggering User, Status and Runtime Parameters.

Job triggering or initialization

We can trigger integrations using Launch_EIB/Launch_Integration operations by passing an integration system ID and required parameters.

Let’s see below a simple request using Launch EIB with default parameter

<?xml version="1.0" encoding="UTF-8"?>
<env:Envelope
   xmlns:env="http://schemas.xmlsoap.org/soap/envelope/"
   xmlns:xsd="http://www.w3.org/2001/XMLSchema">
   <env:Body>
    <wd:Launch_EIB_Request xmlns:wd="urn:com.workday/bsvc" wd:version="v31.2">
       <wd:Integration_System_Reference>
          <wd:ID wd:type="Integration_System_ID">{Integration System ID}</wd:ID>
       </wd:Integration_System_Reference>
     </wd:Launch_EIB_Request>
    </env:Body>
</env:Envelope>

replace the {Integration System ID} with the required Integration System Id.

Execution and status retrieval

For the above operation, Workday in response returns the Background_Process_Instance_ID along with other relevant information after triggering the integration. Using this Background_Process_Instance_ID as an input to the web service operation Get_Integration_Events, we can get information on the triggered jobs. The response returned by Get_Integration_Events operation includes the below information using which we can use to build the logic for workload automation jobs to set the status and write to the logs.

Below is a sample request for Get Integration Events.

<?xml version="1.0" encoding="UTF-8"?>
<env:Envelope
  xmlns:env="http://schemas.xmlsoap.org/soap/envelope/"
  xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <env:Body>
    <wd:Get_Integration_Events_Request
      xmlns:wd="urn:com.workday/bsvc"
      wd:version="v31.2">
      <wd:Request_References>
        <wd:Integration_Event_Reference>
        <wd:ID wd:type="Background_Process_Instance_ID">{Backgrnd ID}</wd:ID>
        </wd:Integration_Event_Reference>
      </wd:Request_References>
      <wd:Response_Filter>
         <wd:Page>1</wd:Page>
         <wd:Count>100</wd:Count>
      </wd:Response_Filter>
    </wd:Get_Integration_Events_Request>
   </env:Body>
</env:Envelope>

In the above code snippet, replace {Backgrnd ID} with the appropriate Background_Process_Instance_ID.

Note: Optionally, we can use Get_Event_Documents operation if the output documents are required for detailed logging purposes.

Scheduling the Jobs in workload automation tool

Yes, another big part would be scheduling the jobs in workload automation tools. Most of the traditional automation tools are well designed to handle synchronous jobs. However, Workday jobs would be processed asynchronously independent of the previous job’s status. For example, using a workload automation tool if two jobs are to be processed in sequence i.e. if we need to ensure the second job gets triggered only after the successful completion of the first job, it is not straight forward to do it in the Workday server. This is because the workload automation tools lack the penetration to handle such scenarios for Workday and SOAP-based jobs.

Let’s examine Control-M for the experimental purpose. Control-M has various job types set up for Informatica, SAP, PeopleSoft, Amazon Server, OS jobs and so on.

It also has a Web service job type as shown in the image.

We can schedule two web service jobs one to trigger the actual integration and the other to just monitor it.

However, both these jobs will be moving to success status irrespective of the job status as soon as they receive the responses. To handle this, we can have a script based job where the script is programmed to trigger the integration job, read the job initiation request and read the job status upon completion. The script should able to read the response and mark the job status in the workload automation tool accordingly as success or failure.

In addition to scripting, Control-M provides additional functionality, where we can have a custom job template defined as per our requirements. Then given is the design of the Control-M custom job functionality.

Currently, I am exploring the designing aspects of custom jobs and will share the details once available. For further information or suggestions, please comment or drop a mail to me.

Limitations

As per Workday, we have below limitations:

It is not possible to launch an integration via web service and provide a determine at runtime value. For example, you can't select Last Successful Integration Event as a value for Last Successful Runtime Date. The web services expect explicit values.

It is not possible to provide values for cascading prompts. For example, if you're trying to launch an EIB that calls a custom report where the prompt values are dependent on each other for content you will get an error returned. A more specific example is if there is a Pay Group prompt that precedes a Pay Group Period prompt.

However, I am exploring the possibilities and workarounds for this.

Disclaimer

This post is based on the author’s exposure and ideas and there may be limitations in its implementations. As stated in the post, the pros and cons of Workday job scheduling is being explored by the author and the findings will be eventually updated.

This is a guest article by Venkatesh Manian.

Cloud ERP vs On-premise ERP

noreply@blogger.com (Shyam) — Mon, 3 Dec 2018 23:59:00 +0530

Cloud ERP

2010's IT Trends
“Cloud” was the buzz word of the IT industry at the start of this decade along with mobile devices - the mini-supercomputers in our palms. While cloud transformation and mobility reaching its pinnacle now (if not already reached), the last few years have been about Digital adoption, AI and ML. Thanks to Google and Sundar Pichai’s keynote addresses for making even a novice like me get an idea of AI and ML. Any consultant working in an on-premise ERP would know that the growth and opportunities provided in these newer IT trends have had a more pronounced impact in ERP space.

What is meant by Cloud ERP or ERP on Cloud?
Cloud ERP systems are ERP systems that are hosted in the cloud (public\private) instead of installing the ERP software on servers hosted in-house (on-premise). Cloud ERP systems, in general, adapt pay per use model - SaaS.

Okay, so what's next? Should we jump start the movement to Cloud ERP? Though the trend is to have IT solutions on Cloud, we cannot simply move on-premise ERP to cloud or buy a cloud ERP product. Let's explore Cloud vs On-Premise ERP further.

On-Premise ERP and Cloud ERP Comparison:

There are many web resources already available on differences between a Cloud ERP vs On-Premise ERP. Among several such information, I liked Oracle Netsuite's article on HOW CLOUD ERP COMPARES TO ON-PREMISE ERP? In this post, I share my views as a summary of the cloud ERP system and how it differs from the on-premise ERP system.

Cloud ERP Vs On-Premise ERP System

On-Premise ERP:

You purchase an ERP product, buy the required hardware, host them in your data centers, buy and install the supporting platform, including OS, middleware products and database and then install, configure and customize the ERP product.

The onus of data backup, securing the data center and the servers hosting the ERP system lies with the implementing organization. To manage and maintain the on-premise systems, even the hardware maintenance would need specialized people, along with the need to have high knowledge workers perform the administrative actions, development and maintenance support for the ERP system.

On-Premise ERP systems are delivered with rich functionality that is based on standard policies, procedures and practices generally accepted at a high level by enterprises across the globe. However, the one size fits all approach may work for smaller firms where there are no unique processes. The good part and major USP of On-Premise ERP is, the product can be customized to the needs of the implementing organization. The product also allows adding new functionality in addition to the ones delivered by the On-Premise ERP provider.

An implementing organization should also take responsibility for taking care of applying patches released for all related software and implementing regulatory changes like PPACA (Obamacare) in the US or GST implementation in India, though the On-Premise ERP provider will deliver the necessary changes. Let alone understanding which issue is fixed in a patch, even to find out if you need to apply the patch requires skilled workers who know their trade.

Pros of On-Premise ERP System:

Allows CUSTOMIZATION i.e. Product can be tailored to the organization needs
Allows adding new functionality
Increased control over the implementation
Timing of upgrade can be decided by the customer depending on their current version’s support end date

Cons of On-Premise ERP System:

Reliance on knowledge workers, vendor resources to implement, upgrade and even to maintain
With increased customization complexity increases
High initial cost around hardware, platform and licensing
Upgrade\Implementation could go for several months to a few years

Cloud ERP:

Similar to On-Premise ERP, Cloud ERPs are also delivered with rich functionality that is based on standard policies, procedures and practices generally accepted at a high level by enterprises across the globe. Cloud ERP systems are generally provided as Software-as-a-Service (SaaS) where you pay a fixed fee per employee for a specific period. Sounds as simple as paying for your telecom service provider!! There is even more. These products are provided by companies over their highly secure cloud space, enabling continuous availability and a mobile-friendly UI. Customers can only focus on using the product sans the overhead around hardware, platform, software, upgrades (to a major extent) and regulatory changes. Imagine you buy a product, and you could roll it out to employees faster and allow them to simply use the product without any need for you to even think about the underlying hardware and software and other maintenance requirements. Sounds cool, isn't it? Think of the additional cost saved by the removal of these overhead and the intangible benefits of not having to rely on individuals, teams, and vendors. That's exactly what a Cloud ERP could do. However, it comes with major disadvantages of (i) no flexibility to change the product to suit your organization needs and (ii) no ability to add new functionalities that the product does not provide. These two disadvantages are directly proportional to the benefits of a Cloud ERP. If at all the product would allow customized and custom functionality, then many of these benefits would no longer exist. In simple words, Organization processes need to tailor to the standard processes supported by the product.

Pros of Cloud ERP System:

No huge initial costs towards hardware, platform and licensing
Zero dependencies on in-house\vendor resources for system availability and data security
No headache of implementing regulatory changes
Always on the current version (or one less)
Increased and readily available functionality for mobile usage
With almost zero customization, lesser no of resources will be required and thereby reduced costs and resourcing overhead

Cons of Cloud ERP System:

The product cannot be tailored to the organization needs
Newer functionality cannot be added
Maintenance decisions are made by the vendor

Cloud ERP or On-premise ERP - Which is Cost Effective?

I know as you read, many of you might be pondering over the cost aspect. Depending on the number of users and years a customer uses the product, cost can work either in favor of On-Premise ERP or Cloud ERP; though cloud ERPs are generally considered as cost effective due to reduced initial cost. But the TCO may not always be in favor of Cloud ERPs. In my view, on-premise ERP might work out cheaper for larger corporations, considering the subscription charges for Cloud ERP does not reduce with increased no. of users contrary to various popular pay for usage model services.

Which one is better - Cloud ERP or On-Premise ERP?

Well, the answer to this question is contextual. Neither Cloud ERP nor On-Premise ERP may solve all the ERP challenges of your organization. Both lines of products bring their own advantages and disadvantages. The advantages of a Cloud ERP are the disadvantages of an On-Premise ERP while the disadvantages of a Cloud ERP are the advantages of an On-Premise ERP. That's a Catch 22 Situation.

Set aside the cost aspect and various other factors, selection can be influenced depending on whether your organization is willing to adapt to the ERP product or want to tailor the ERP product to facilitate the several unique processes of your organization. The former may work for mid-sized organizations while the latter may be the need for large global enterprises. Cloud ERP may look cost effective, but if you end up having multiple custom built applications or third-party products integrating with the Cloud ERP (to support the missing functionalities), any cost advantage of a Cloud ERP might go for a toss.

While the integration capabilities of a Cloud ERP are excellent, the other important factor to consider is why an organization is using ERP in the first place? Is it to save dollars or to improve operational efficiency? If your organization's objective of ERP is to have reduced costs, TCO calculation will give an instant answer, if the objective is to improve operational efficiency and the cloud product does not support your operations, then On-Premise might be the answer.

Choosing between a Cloud ERP and an On-Premise ERP is not a simple task and is influenced by several factors referenced above. In the end, the decision might also be based on who among your CTO, CIO and CFO have their say.

The decision need not be always an Either/OR between On-Premise ERP and Cloud ERP, here are some heterogeneous solutions:

Many organizations adopt a hybrid model focusing on popular Cloud ERP for self-service and frequently used transactions and specific solutions for various other needs.
If the unique functionalities you have in your on-premise ERP cannot be get ridden off and yet the benefits of having your ERP in the cloud like availability and near-zero hardware\platform maintenance matters to you, then consider moving your On-Premise ERP to Amazon or Azure cloud.
If your On-Premise product is PeopleSoft, PeopleSoft can also be hosted on Oracle Cloud. However, these two solutions are not SaaS solutions.

Popular On-Premise and Cloud ERPs:
Some popular On-premise products include PeopleSoft, SAP and Oracle EBS while some of the leading cloud ERP products are Oracle Cloud, Workday and SAP among several others.

In my next series of posts, I will be writing on Oracle HCM Cloud modules with a focus on self-learning along with basic tutorials and troubleshooting.

Workflow errors on New Hire and Add employment instance

noreply@blogger.com (Shyam) — Wed, 19 Apr 2017 09:14:00 +0530

User tries to hire a new person or tries to add a new organization relationship encounters a series of error messages related to workflow as shown below. Even in vanilla installation, ‘PS’ user id would experience the same issue.

Error Message 1:

Role Benefits Administrator has no members. (52,7)

This role doesn't have any members; if it's a query role, the query returned no results.
The system couldn't use the ROLEUSER specified as the System Default User in the Workflow Administrator, either because no System Default User has been identified or because the specified ROLEUSER was invalid.
There is a problem in calling TriggerBusinessEvent for the indicated Business Process/Event.
Review the program for errors. If necessary, run a PeopleCode trace to determine the program that failed.
The PeopleCode TriggerBusinessEvent built-in function was used by the specified PeopleCode program and failed to execute correctly on the client workstation.

Error Message 2:

Error when attempting to trigger Business Process / Event: Administer Workforce / Hire Employee (67,2)

Error Message 3:

TriggerBusinessEvent failed for Administer Workforce.Hire Workforce-Hire Employee. (2,302) JOB.EMPLID.Workflow PCPC:439 Statement:3

At first look, issue seems to be because the role – 'Benefits Administrator' is missing for the user id accessing the system.

If the role is missing to the user id, make sure to add it.

Carefully reading the second portion of the first error message, will reveal another aspect to see if there is no system default user or even if there is one, is it a valid user with 'Benefits Administrator' role.

Navigate to PeopleTools > Workflow > Set Workflow Defaults and verify there is a system default userid. If there is one already, verify the user is active and has 'Benefits Administrator' role.

If none of these help, let's pay attention to the second and third error messages. It is clear that there is an issue with 'Hire Employee' event, 'Hire Workforce' Business activity and 'Administer Workforce' Business process, Opening the workflow objects in app designer will give further inputs.

Open 'Hire Workforce' Business activity, there will be an email as 'Benefits Administrator'. This indicates, that there will be an email triggered to users with role 'Benefits Administrator.

· Now find out at least one user or system default workflow user with this role

· For the identified user visit user profile page in PeopleTools > Security > User profile > General tab

· Ensure an email address is added

· Then go to Workflow tab and ensure 'Email User' option is selected.

Doing these changes should resolve the issue. The error message can also occur on doing a termination transaction. It can also happen with the role name changing to Payroll Administrator or Training Administrator or Facilities Administrator or MIS administrator or VC administrator. Similar steps should resolve the issue.

noreply@blogger.com (Shyam) — Mon, 3 Nov 2014 08:35:00 +0530

SQL query to find out list of users who have access to a Page and information on which Permission list enables the user to access the page:
SELECT DISTINCT oprid,
                oprclass
FROM   psoprcls
WHERE  oprclass IN (SELECT DISTINCT classid
                    FROM   psauthitem
                    WHERE  pnlitemname = :1)

This query can be modified as below to see if a user has access to a particular component and if yes through which permission list.

SELECT DISTINCT oprid,
                oprclass
FROM            psoprcls
WHERE           oprclass IN
                             (
                             SELECT DISTINCT classid
                             FROM            psauthitem
                             WHERE           pnlitemname = :1--required component name)
AND             oprid = :2      --required userid

How to Query Which Components Are Accessed by Which Roles?
For example, for component "JOB_DATA", to find out which role should be given to a user which gives them access to that component or page use the following SQL.

SELECT *
FROM   psauthitem A,
       psroleclass B,
       psroleuser C
WHERE  A.baritemname IN ( :1 )
       AND A.classid = B.classid
       AND C.rolename = B.rolename
       AND C.roleuser = 'PS'

PeopleTools Tables - Part 3 - PeopleTools records for basic definitions in PeopleSoft Application Designer

noreply@blogger.com (Shyam) — Sun, 2 Nov 2014 16:19:00 +0530

After PeopleTools Tables - Part 1 - PeopleTools table for PS-Queries, and PeopleTools Tables - Part 2 - PeopleTools records for Security Definition the next set of PeopleTools records is here related to basic PeopleSoft object definitions related to Application development.

Record for Projects:

PSPROJDEFNLANG - Project Definition Lang Table
PSPROJECTDEFN - Project Definition Table
PSPROJECTDEP - Incident Dependency Table
PSPROJECTINC - Project Incidents Table
PSPROJECTITEM - Project Item Table Here are couple of my posts on PSPROJECTITEM Part 1 and Part 2 which will help in understanding PSPROJECTITEM record. Here is another post on PSPROJECTITEM record which explains querying PSPROJECTITEM data and extracting it to spreadsheet
PSPROJECTMSG - Project Messages
PSPROJECTSEC

Record for Fields:

PSDBFIELD - A table with all Database Fields
PSDBFIELDLANG - Alternate language for PSDBFIELD table
PSDBFLDLABL - DBField Label Table where FIELDNAME represents DBField name while LABEL_ID is a unique ID of Field Label.
PSDBFLDLABLLANG - DBField Label Language Table
PSXLATITEM - Translate Value Items

Record for Records:

PSRECDEFN - Record header table
PSRECFIELD - Fields in the record (subrecords not expanded)
PSRECFIELDALL - Fields in the record (subrecords expanded)
PSKEYDEFN - Indexes
PSTBLSPCCAT - Tablespaces
PSRECTBLSPC - Records’ tablespace assignments
PSRECDEFNLANG

Record for Indexes:

PSINDEXDEFN

Record for Pages:

PSPNLDEFN - Page header table
PSPNLFIELD - Page controls
PSPNLHTMLAREA - Static HTML Areas on Pages

Record for Components:

PSPNLGRPDEFN - Component header table
PSPNLGROUP - Pages in the components
PSPNLGDEFNLANG
PSPNLGROUPLANG

Record for Component Interfaces:

PSBCDEFN - header record; one row for each Component Interface
PSBCITEM - one row for each property
PSAUTHBUSCOMP - Component Interfaces access table. Links Permission Lists, Component Interfaces, Methods and Authorized Actions

Record for Menus:

PSMENUDEFN - Menu header table
PSMENUITEM - Items (components) on the menu

PeopleTools Tables - Part 2 - PeopleTools records for Security Definition

noreply@blogger.com (Shyam) — Sun, 2 Nov 2014 13:22:00 +0530

After PeopleTools Tables - Part 1 - PeopleTools table for PS-Queries, the next set of PeopleTools records is here related to PeopleSoft Security.

Below is the complete list of PeopleTools meta tables that stores various information on PeopleSoft Security. These tables are specific to Tools version 8.50 and could vary slightly between versions.

PSCLASSDEFN - Permission Lists definition. Master list of permission lists
PSAUTHITEM - Authorized menu item. It contains list of Menu items granted security by permission lists. To find out permissions lists giving access to a component, search this record
PSROLEDEFN - Role definition. Master table of Roles
PSROLECLASS - Role Classes. Links Roles and associated Permission Lists
PSOPRDEFN - Operator Definition. This is a core table for User Profile Manager which defines PeopleSoft users.
PSROLEUSER - Roles granted to users. Links roles and users having access to.
PSAUTHBUSCOMP - Component Interfaces access table. Links Permission Lists, Component Interfaces, Methods and Authorized actions
PSUSEREMAIL - Email addresses for user ids.
PSOPRCLS - Userids and their associated Permissions Lists
PSAUTHPRCS - Link between permission lists and process groups
PSAUTHSIGNON - Link between permission lists and signon times
PSAUTHWEBLIBVW - A view linking permission lists and access to web libraries (menus in PSAUTHITEM that begin with WEBLIB_).
PSAUTHWS - Link between permission lists and web services (service operations)
PS_SCRTY_ACC_GRP - Link between permission lists, trees and query access groups

PSSIGNONPPC - Signon Peoplecode
PSAUTHCHNLMON - Message monitor Channel Security
PSAUTHCUBE - Link between Permission lists and Cube Instance Ids
PSAUTHOPTN - Link between Permission lists with User options and category levels
PSAUTHQUEUEMON - Link between Permission lists and Queues
PSAUTHWS - Authorized Web Services. Link between Permission lists and Service operation
PSPRCSPRFL - Process Profile. Link between Permission lists with Server/client file/print options, Output destination details, Request status, Server status and Recurrence update details.
PS_MC_OPR_SECURITY - Mass change operator security. Link between userid(oprid) and Mass Change Template
PS_MC_OPRID - Mass change operator security. Link between operatorid(OPRID) and Online execution details
PS_SCRTY_QUERY - PS Query profile. Links permission lists and various access levels in PS Query.

Records for USERS:

1. PSOPRALIAS

2. PSOPRDEFN

3. PSROLEUSER

4. PSUSERATTR

5. PSUSEREMAIL

6. PSUSERPRSNLOPTN

7. PS_ROLEXLATOPR

8. PS_ROLEXLATOPR_LNG

9. PS_RTE_CNTL_RUSER

Records for ROLES:

1. PSROLEDEFN

2. PSROLEDEFNLANG

3. PSROLECANGRANT

4. PSROLECLASS

Records for PERMISSION LISTS

1. PSCLASSDEFN

2. PSAUTHBUSCOMP

3. PSAUTHCHNLMON

4. PSAUTHCUBE

5. PSAUTHITEM

6. PSAUTHOPTN

7. PSAUTHQUEUEMON

8. PSAUTHPRCS

9. PSAUTHSIGNON

10. PSAUTHWS

11. PSPRCSPRFL

12. PS_MC_OPR_SECURITY

13. PS_MC_OPRID

14. PS_SCRTY_ACC_GRP

15. PS_SCRTY_QUERY

Updating Table Stats in Oracle

noreply@blogger.com (Shyam) — Sat, 1 Feb 2014 12:45:00 +0530

To gather statistics in Oracle for a specific table use the following sql. One would need sysadm access to run this sql.

Exec dbms_Stats.gather_table_Stats(‘SYSADM’,'TABLENAME');

To know more about gathering statistics check out this Oracle link

Prompt Edit on Create Keys does not work on Component Interfaces

noreply@blogger.com (Shyam) — Fri, 16 Aug 2013 00:20:00 +0530

In a Component Interface while calling the create method, when an invalid value is entered in a record field(create key) having Prompt Table with Edit, the data gets saved. In other words, Save() method executes successfully though there is an invalid value entered in the create key with a Prompt Table Edit.

This happens because while assigning value to the create key, the CI is still uninstantiated and the prompt table edit does not get executed then. The invalid value is accepted by the system as the validation does not happen against the prompt table.

As a solution to this issue, we can access the search record\Add search record of the component and select "SearchEdit" in the record field properties of the Create Key field. This will ensure the validation is triggered on the Create key.

PeopleCode Metatables

noreply@blogger.com (Shyam) — Thu, 15 Aug 2013 18:47:00 +0530

Peoplecode Audit:
At times, we have the necessity to find audit details of a particular peoplecode on a particular object. When we check the LASTUPDDTTM of a record or a page or a component, it does not give us the required information since the definition itself hasn't got changed by writing peoplecode. However, there is an answer to this. Most of the PeopleSoft technical consultants know that PSPCMPROG table stores the peoplecode. PROGTXT field in PSPCMPROG record stores the peoplecode however it cannot be retrieved in a readable format since the code is saved in binary format in the database.
PSPCMPROG record provides us two fields - LASTUPDDTTM and LASTUPDOPRID which gives us information on when the peoplecode was last modified and by whom.

References to objects in peoplecode:
I found something more interesting while verifying this sometime back. PSPCMNAME table contains two fields RECNAME and REFNAME which caught my attention. For every reference to a PeopleTools object referenced from peoplecode, an entry is written into this table. For example, if a Record.Field.Fieldchange event references five different PeopleSoft objects then there will be five entries in this table. I believe this is how PeopleSoft's cross reference utility\Definition references works. Surely give this a shot, when you have to do a definition reference next time.

How to read a SQL Trace in PeopleSoft

noreply@blogger.com (Shyam) — Sun, 17 Mar 2013 23:41:00 +0530

As always with so many things, I struggled initially to understand the trace file. More often, I would generate a trace file and did not understand every aspect of the trace file. However with experience, I have figured what is written to the trace file and sharing my findings below.

When a trace is run for SQL statements, the resulting trace statement will have various parts. Here is the list along with the description.

First Part: n-xxxxx.

This is a sequential line counter for the process (exe). n is a integer starting from 1 to n. xxxxx is reserved for each line written to the trace file. If there is a second process, then it would be 2-xxxxx, for the third one it would be 3-xxxxx and so on.

Second Part: hh.mi.ss

It indicates the timestamp at which the trace line is written. This timestamp is retrieved from the machine in which PeopleTools is running.

Third Part: A time value

This is the time elapsed since the previous trace line was written. The time elapsed between n-xxxxx and n-(xxxxx+1) is written.

Fourth Part: Cur#n

This indicates the cursor number for the statement

Fifth Part: PSFT_DB

Indicates the PeopleSoft database in which this API call is executing.

Sixth Part: RC=0

This is the return code for the associated API call.

Seventh Part: Dur=Another time value.

This is the time to execute the assoicated API call.

Eight Part: COM Stmt=<SQL Statement>

This is the database API call and provides information on the SQL executed.

Next post is on reading a Peoplecode trace.

Printing Multiple Reports in a SQR Report

noreply@blogger.com (Shyam) — Sun, 3 Feb 2013 14:23:00 +0530

One of my team had a requirement to create 2 reports from one SQR and faced couple of minor issues. Sharing the issues and the solution we used in this post.

Mutliple reports from one SQR:
To generate multiple report from SQR two things should be done:

Declaring the reports using DECLARE-REPORT
Updating the Parameters in Process Definition

Step 1: In the BEGIN-SETUP section declare report layouts as below:

BEGIN-SETUP

DECLARE-REPORT REPORT1

END-DECLARE

DECLARE-REPORT REPORT2

END-DECLARE

END-SETUP

Step 2: In the BEGIN-PROGRAM section or from the required procedure use the reports declared in Step 1.

BEGIN-PROGRAM
USE-REPORT REPORT1
PRINT 'Text to print in REPORT1' (,1)
!Once REPORT1 is completed switch to REPORT2 as below:
USE-REPORT REPORT2
PRINT 'Text to print in REPORT2' (,1)
END-PROGRAM

Step 3: After performing the above two steps and running the SQR, one will find only one report(usually the first one) is displayed in Process Monitor though the other one also got generated. This is because the Process Scheduler is unaware that we are printing two reports in a single report. To make the process scheduler aware of multiple reports in the Parameter section of Process Scheduler specify "-MR n" without quotes. Here, MR indicates Multiple Reports and 'n' is a number between 2 to 99 indicating to the Process Scheduler the number of reports generated by the SQR report.

Step 4: Providing custom file names for multiple reports generated by SQR
While the above three steps will generate multiple reports and display in Process Scheduler, the file names would be a combination of process name, process instance and the file number. To provide custom file names, modify the contents of Step 2 as below:
BEGIN-PROGRAM
Let $Filename_of_Report1 = Requiredpath/1.pdf
Let $Filename_of_Report2 = Requiredpath/2.pdf
USE-REPORT REPORT1
NEW-REPORT $Filename_of_Report1
PRINT 'Text to print in REPORT1' (,1)
!Once REPORT1 is completed switch to REPORT2 as below:
USE-REPORT REPORT2
NEW-REPORT $Filename_of_Report2
PRINT 'Text to print in REPORT2' (,1)
END-PROGRAM

Using GetFile() on AE commits the App Engine

noreply@blogger.com (Shyam) — Sat, 19 Jan 2013 23:19:00 +0530

The title of this post sounds strange isn't it? When I encountered an issue with one of my App Engine committing though the program would error, it sounded strange to me. The AE had GetFile() peoplecode function and was triggered from a pushbutton. In the fieldchange event associated with the pushbutton, there were few data changes and had also GetNextProcessInstance() function to achieve a desired functionality, followed by a call to AE using CallAppEngine() function. Even after getting rid of GetNextProcessInstance() function(which would issue a commit) did not resolve the issue. Since the program started to commit data, though the AE would fail had a hard time identifying the issue. I had no idea that the GetFile() function would perform implicit commit until I came across a documentation in Oracle Support which listed situations where implicit commit is issued by PeopleTools. The list below:

GetFile()
GetNextProcessInstance()
MessageBox()
When CI is called, and any CI Error/Warning is occured

I have listed the details of impacted versions and workaround below:

GetFile():

Impacted Version: All versions except PT8.49.21 the ONLY version for which Oracle has provided a fix

Workaround: Call the function at the top of the program from a place where the commit would have no impact on the transaction data.

GetNextProcessInstance():

Impacted Version: All versions

Workaround: Call the function at the top of the program from a place where the commit would have no impact on the transaction data

MessageBox():

Impacted Version: PT 8.48.17 to 8.48.19, and 8.49.09 to 8.49.18

Workaround: No workaround and PT upgrade is the only option

When a CI is called from an AE and any CI Error/Warning is encountered:

Impacted Version: PT 8.48.17 to 8.48.19, and 8.49.09 to 8.49.18

Workaround: No workaround and PT upgrade is the only option

TypeError: saveWaitObj is null

noreply@blogger.com (Shyam) — Sun, 13 Jan 2013 00:11:00 +0530

I was meddling with the various options in PeopleSoft WebProfile configuration and at one point lost track of the changes made. After a while, hitting on certain Save button or pushbuttons would cause this error message:

TypeError: saveWaitObj is null

Perplexed with the message, I switched the web profile and did not see that error message again. Switching back to the initial web profile the message reappeared. The reason for the message is that I had unchecked “Enable Processing Message” under PeopleTools > Web Profile > Web Profile Configurations.

As per Peoplebooks, this option is checked by default and it enables processing notification while the system processes a request. Selecting this option and bouncing the PIA resolved the issue.

SQL Query to fetch list of all processes\jobs in a recurrence

noreply@blogger.com (Shyam) — Sat, 12 Jan 2013 20:07:00 +0530

One of my colleague asked me for a sql query to retrieve the list of all processes and jobs for a particular recurrence. I wrote the below SQL which gives a list of PeopleSoft processes running in Process Scheduler for a recurrence. This SQL also gives the individual processes associated with a PS Job scheduled in the same recurrence.
\

SELECT process_job_name,
       description,
       recurname
FROM   ps_prcsrecur a,
       (SELECT p.prcsname  AS Process_Job_name,
               p.descr     AS Description,
               p.recurname AS recurname
        FROM   ps_prcsdefn p
        UNION
        SELECT jp.prcsjobname AS Process_Job_name,
               (SELECT j.descr
                FROM   ps_prcsdefn j
                WHERE  j.prcsname = jp.prcsname) AS Description,
               jp.recurname AS recurname
        FROM   ps_prcsjobdefn j,
               ps_prcsjobitem jp
        WHERE  j.prcsjobname = jp.prcsjobname) b
WHERE b.recurname = a.recurname

AND a.recurname = :1

ORDER BY 1

How to combine PeopleSoft Audit and Database Trigger?

noreply@blogger.com (Shyam) — Tue, 1 Jan 2013 12:55:00 +0530

PeopleSoft's delivered Audit feature works only for changes made from online. We depend on RDBMS's trigger feature to track changes made through non-PeopleSoft methods. PeopleSoft has come up with a Database Level auditing feature using which we can combine both the PS delivered Audit and Database's Trigger feature.

Part A - Update Database Level Auditing:

Navigation: PeopleTools > Utilities > Audit > Update Database Level Auditing

1) Search for the PeopleSoft record name to audit. The system by default will create audit trigger record with the base record name suffixed by _TR

2) Select the Audit record name. Please refer to my earlier post on PS Audit for more details

3) Audit Options available are Add, Change or Delete

4) Click on "Generate Code". This will create the SQL statement to use for the Trigger creation

5) "Create Trigger Statement" section should have the SQL statement generated by above action

Note:

1) All the information entered above, gets captured in the PeopleTools trigger definition table – PSTRIGGERDEFN.

2) EnableDBMonitoring domain parameter must be set in PSADMIN for this feature to work

Part B - Perform Database Level Audit:

Navigation: PeopleTools > Utilities > Audit > Update Database Level Auditing

1) Select or create a Run Control ID

2) Select either "Create All Triggers" or "Create Trigger(s) On" depending on the requirement

3) Click Run which will trigger the Audit Application Engine - TRGRAUDPROG(Auditing Triggers). This process will fetch every row in PSTRIGGERDEFN and writes Create Trigger statements to the file - TRGCODEX.SQL, where Z represents a digit determined by the number of files that already exist in the output directory.

4) This step only creates the SQL Statement, and it should be run using SQL tool to create trigger(s)

To Delete Audit Trigger:

(Excerpt from Peoplebook)

1) Select PeopleTools, Utilities, Audit, Update Database Level Auditing

2) Open the trigger that you want to delete

3) Clear all the Audit options (Add, Change, and Delete)

4) Click Generate Code

5) Click Save

6) Drop the trigger name from the database

Coverage Formula for PS-Benefits from HCM9.x and insight on how it is created by the data conversion process

noreply@blogger.com (Shyam) — Wed, 26 Dec 2012 00:09:00 +0530

After upgrade to PeopleSoft 9.1\9.0 from prior 8.x releases noticed that in PS_BN_FORMULA table the description is also populated with the same value as formula id-BN_FORMULA_ID.

First up, PS8.9 supported coverage calculation based on the Benefit Plan attribute table and Calculation Rules table. Life Add table(LIFE_ADD_TBL) had Factor X Salary, Flat Amount while Disability Plan table( DISBLTY_PLN_TBL) had the Maximum monthly benefit. In addition, calculation rules table had data about Coverage, Premium As-Of data, Benefit Base to use, Rounding rules, Max/Min Coverage and multiple job combination rules.

PS9.x versions have got rid of this dependency from Life Add table and Disability Plan tables along with Calculation Rules. Instead now coverage is loaded by the conversion App Engine processes directly into Benefit Formula table(PS_BN_FORMULA). This table contains the Coverage Formula which is populated by the data conversion AE process. This Coverage Formula is populated with the data contained in Life Add table, Disability Benefit Plans table and Calculation Rules Ids.

Note:

The upgrade data conversion process populates both the formula id and description (DESCR field) in PS_BN_FORMULA table with the same value - the formula ID. This seems to be because the DESCR field is required and to write a conversion program that will give a description based on the formula would be quite complex. May be we are talking about AI to do this. Hence Oracle should have kept it simple by populating the DESCR field with the same value as BN_FORMULA_ID.

Setting that controls the display time of "Saved" message

noreply@blogger.com (Shyam) — Tue, 25 Dec 2012 00:48:00 +0530

After every user initiated save, PeopleSoft delivered "Saved" image(yes it is not a text and the image name is SAVE_CONFIRM) that appears in the top right of every component. This message gets displayed for 3seconds by delivered standards. However, how\where to modify the time that after user initiated save?

Save Confirmation Display Time:
The setting is available under Web Profile Configuration.
Navigation: PeopleTools > Web Profile > Web Profile Configuration
Under General tab, the value entered in "Save Confirmation Display Time" controls this. The default value is 3000milliseconds(3 seconds). The maximum value that can be entered is 99,999,999 milliseconds which is equivalent to 1.16days!

Inactivity Warning and Inactivity Logout Timing:
The same can also be entered for Inactivity Warning and Inactivity Logout. These values are available in the same navigation under PeopleTools > Web Profile > Web Profile Configuration > Security. The default value for "Inactivity Warning" is 1080seconds(18min) and "Inactivity Logout" is 1200secods(20min). The maximum value that can be provide is 99,999,999 seconds which is equivalent to a whopping 3.17years!!!

SQL Query to identify the Level 0 record of a component

noreply@blogger.com (Shyam) — Sat, 24 Nov 2012 01:42:00 +0530

The below SQL will give all the records of a component along with its corresponding occurs levels:

SELECT DISTINCT D.recname     TableName,
                D.occurslevel ScrollName
FROM   pspnlgrpdefn A,
       pspnlgroup B,
       pspnldefn C,
       pspnlfield D,
       psrecdefn E
WHERE  A.pnlgrpname = B.pnlgrpname
       AND A.market = B.market
       AND B.pnlname = C.pnlname
       AND C.pnlname = D.pnlname
       AND A.pnlgrpname = 'POSITION_DATA' --Component Name
       AND E.recname = D.recname
       /* Restricts to only tables and eliminates views and derived records */
       AND E.rectype IN ( 0 )
       /*This condition eliminates records in component as Related Fields*/
       AND To_char(Bitand(D.fielduse, 16)) <> '16'
ORDER  BY 2,1

Note: Adding condition as d.occurslevel = 0 will return only the level 0 record