FAKE SQL TO FORCE ABEND Error – PeopleSoft AE

Quick post!

An AE failed with the following error in PeopleSoft:

SQL error. Function:  SQLExec Error Position:  0 Return:  900 – ORA-00900: invalid SQL statement Statement:  FAKE SQL TO FORCE ABEND OF PROGRAM DUE TO INVALID COUNTRY CODE Original Statement:  Fake SQL to Force Abend of program due to Invalid Country Code

The run control record did not have any invalid values for Country though. The problem was with the Run Control ID that was used. It had far too many spaces and ultimately resulted in this error!

Until next time! 🙂

Advertisement
Posted in Uncategorized | Tagged , , , | Leave a comment

Re-submitting Failed Async Messages Automatically – PeopleSoft

Many a times Async messages have failed with errors – sometimes, it isn’t an “actual” error and goes away upon re-submitting. I agree that the best approach is to identify the root cause and get that resolved – however, it all depends on the scenario.

Here’s a quick code snippet to automate re-submission of failed messages. Looks like PeopleCode has had it all long, as part of the IntBroker class. 🙂

You can use the following SQL to fetch the required values and pass it to a State record as part of an Application Engine process. The values from the State record can then be passed onto a PeopleCode step as listed below.

Subscription Contracts:

SELECT A.IBTRANSACTIONID, A.QUEUENAME, B.SEGMENTNO
FROM PSAPMSGSUBCON A, PSAPMSGPUBDATA B
WHERE     A.STATUSSTRING = 'ERROR'
AND A.IBPUBTRANSACTID = B.IBTRANSACTIONID
AND B.SEGMENTNO = 0
AND B.SUBSEGMENTNO = 1;

Publication Contracts:


SELECT A.IBTRANSACTIONID, A.QUEUENAME, B.SEGMENTNO
FROM PSAPMSGPUBCON A, PSAPMSGPUBDATA B
WHERE A.STATUSSTRING = 'ERROR'
AND A.IBPUBTRANSACTID = B.IBTRANSACTIONID
AND B.SEGMENTNO = 0
AND B.SUBSEGMENTNO = 1;

And pass the values to a PeopleCode step to re-submit either the Sub or Pub contracts:

Local string &sTransactionID, &sQueueName;
Local number &nSegmentNum;

&sTransactionID = <Transaction ID from the Do Select Action> ;
&sQueueName = <Queue Name from the Do Select Action>;
&nSegmentNum =<Segment Number from the Do Select Action>;

&resubStatus = %IntBroker.ReSubmit(&sTransactionID, &sQueueName, %IntBroker_SUB, &nSegmentNum);

We’d have to replace %IntBroker_SUB with %IntBroker_PUB, for a Pub contract.

Please note : only messages with the following statuses can be re-submitted:

  • Cancelled
  • Edited
  • Error
  • Timeout

Hope this helps! 🙂

Posted in Oracle, PeopleSoft, SQL | Tagged , , , , , | Leave a comment

Setting Up Kibana on FSCM PI 35 – PeopleTools 8.58.02

Hello Everyone!

Next up is setting up Kibana on FSCM PI 35.

ElasticSearch is installed out of the box for this PI, however Kibana isn’t.

Before we start installing Kibana, follow the steps provided in this post to reset the password for the esadmin user. It is true that you can create a new user and provide the requisite permissions – but, this approach saves you from having to deal with hassles around ElasticSearch configurations.

Once you’ve reset the password, ensure you’ve re-started ElasticSearch(also provided in the earlier post).

Now, let’s install Kibana. Login to Putty(installation instructions are available here).

  • Execute the following command(s)

[root@fscm92 bin]$ sudo -s su esadm1

  • Navigate to the ElasticSearch DPK Setup directory(this is based on FSCM PI 35)

[esadm1@fscm92 bin]$ cd /opt/oracle/psft/dpk/ES/setup

  • Execute the following command:

[esadm1@fscm92 setup]$ ./psft-dpk-setup.sh –install –install_base_dir /opt/oracle/psft/pt/ES

  • You’ll be prompted to enter values – if you’ve already installed ElasticSearch or Logstash, skip them

You’ve chosen to do a fresh installation of Elasticsearch, Logstash and Kibana.

Do you want to install Elasticsearch: (y/n): n

Do you want to install Logstash (Needed for PeopleSoft Health Centre): (y/n): n

Do you want to install Kibana: (y/n): y

Enter the server port for Kibana [ 5601 ] :

  • Enter the hostname along with http://

Enter the elasticsearch host[http(s)://hostname] : http://fscm92.ps.com

Enter the elasticsearch port [ 9200 ] :

Enter the password for esadmin : <enter your esadmin password here>
Re-enter the password for esadmin : <second time lucky>

  • You’ll see the following output from the installation script

Checking if Elasticsearch service is running…….

Elasticsearch service is running. Proceeding with the Kibana installation.

Extracting the new Kibana Binary …….
[OK]
Configuring Kibana ……
Kibana Keystore updated.
[OK]

Starting Kibana…..

Kibana installation is completed.
[esadm1@fscm92 setup]$

If you’ve reached this far, congratulations! 🙂

Login to PeopleSoft using VP1 and update hostname and credentials in the following pages:

    • Navigate to PeopleTools > Search Framework > Administration > Search Instance and update the password for the esadmin user (the following screenshot is based on HCM PI 34)

SS-01

    • Navigate to PeopleTools > Search Framework > Administration > Monitoring Server > Configure Server tab and update the password for the esadmin username
      • You can either update it manually or run the script provided in this post (the following screenshot is based on HCM PI 34)

SS-02

See if you’re able to access the Kibana home page from http://fscm92.ps.com:5601/

Hope this helps! Have fun! 🙂

Posted in Oracle, PeopleSoft, SQL | Tagged , , , , , | Leave a comment

Resolving Issues with esadmin Password – PeopleTools 8.58

Hello Everyone!

I had been trying to get the password for esadmin reset on the HCM PI 34, but without much luck. This post from psadmin.io and lot of help from the Oracle Support forums came in very handy – I was able to reset the passwords using the PSCipher utility, however, I couldn’t get the change to take effect.

This all started when I tried accessing port 9200 on my HCM image – http://hcm92.ps.com:9200/

I got this error when attempting some random passwords(esadmin being the most likely) 🙂

{“error”:{“root_cause”:[{“type”:”orcl_auth_plugin_exception”,”reason”:”Invalid user/password”}],”type”:”orcl_auth_plugin_exception”,”reason”:”Invalid user/password”},”status”:401}

Couple of Oracle Support notes provided steps to add a new user(say, esadmin1) and provide full access access to be the same as esadmin. I wanted to see if there was a workaround that we could use, to get this resolved. Here are the steps:

  • Login to the App Server using Putty
  • Execute the following command

[root@hcm92 piabin]$ sudo -s su psadm2

  • Navigate to the piabin directory on your Web Server (if you use PI 34 like me, it would be /home/psadm2/psft/pt/8.58/webserv/peoplesoft/piabin)

[psadm2@hcm92 piabin]$ cd /home/psadm2/psft/pt/8.58/webserv/peoplesoft/piabin

  • Next, execute the following command using the PSCipher Utility

[psadm2@hcm92 piabin]$ ./PSCipher.sh esadmin2web

  • where, esadmin2web is the new password that you intend to use for esadmin
  • Upon execution, your result would be something like this:

[psadm2@hcm92 piabin]$ ./PSCipher.sh esadmin2web
Encrypted text: {V2.1}lR4fYCnZEXWiK9x8dlGXKNLOPPTUtWPo

  • Note the content in bold, provided against Encrypted text
  • Navigate to ES_HOME/pt/elasticsearch7.0.0/plugins/orcl-security-plugin/config/properties on the App Server – it would be easier to access and edit the file via WinSCP or a text editor
    • The full path in my case was /opt/oracle/psft/pt/ES/pt/elasticsearch7.0.0/plugins/orcl-security-plugin/config/properties
  • Open the esusers.yml file available in this directorySS-01
  • Replace the password against esadmin in this file with the encrypted text from the steps listed above(generated using the PSCipher utility)
  • Restart ElasticSearch
    • If a version of ElasticSearch is already running, execute the following command

[root@hcm92 bin]$ sudo -s su esadm1

[esadm1@hcm92 bin]$ ps -ef | grep elas

    • The first value available in the results would be the process ID(pid)
    • Terminate it using the following command:

[esadm1@hcm92 bin]$ kill pid

  • Navigate to the ElasticSearch bin directory and issue the following command on HCM PI 34:

[esadm1@hcm92 bin]$ cd /opt/oracle/psft/pt/ES/pt/elasticsearch7.0.0/bin

[esadm1@hcm92 bin]$ nohup ./elasticsearch &

  • The last command(upon pressing Enter) will continue to run in the background and you can press the Enter key again to return to the prompt
  • If you’ve been successful until now, great job! 🙂 – there are two more steps remaining
    • Navigate to PeopleTools > Search Framework > Administration > Search Instance and update the password for the esadmin user

SS-01

    • Navigate to PeopleTools > Search Framework > Administration > Monitoring Server > Configure Server tab and update the password for the esadmin username
      • You can either update it manually or run the script provided in this post

SS-02

  • Access the ElasticSearch page on your browser (http://hcm92.ps.com:9200/)
    • Provide username as esadmin and the new password(that you have reset)
    • You should be able to see something like this:

SS-03

  • Try doing a Global Search and it should work fine – in my case, it did not return any search results. I usually search for Kibana from the global search results – I presume that the search index file wasn’t created or was broken. This was resolved by running the PTSF_GENFEED process for run control PTPORTALREGISTRY_FULL.

Hope this helps!

Happy Reading! 🙂

Posted in Oracle, PeopleSoft, Software, Virtualization | Tagged , , , , , , , | Leave a comment

Resolving “PTCDPK-50008: odcdeploy:Oracle client cloning failed” Error : PeopleTools 8.58

If you’ve had a PeopleTools client installed on your PC with an earlier VirtualBox installation, just deleting the PT8.xx.xx_Client_ORA and c:\oracle folders won’t suffice, as references to the earlier Oracle installations are set elsewhere. If you run the SetupPTClient.bat file, it’ll fail with the following error:

ODCDeploy.py ERROR : PTCDPK-50008: odcdeploy:Oracle client cloning failed

If you’ve setup VirtualBox installations like me, you can clean them up with the following steps:

Using the Registry Editor:

  • Ensure you have admin access to the PC
  • Use the run prompt(Windows Key + R) and type regedit
  • Once the Registry Editor window appears, navigate to the following key : Computer > HKEY_LOCAL_MACHINE > SOFTWARE > Oracle > KEY_OraClient12Home2

SS-01

  • Right click on KEY_OraClient12Home2 and select Delete
  • When the following dialog appears, click Yes

SS-02

  • Exit Registry Editor

Removing the Oracle Inventory File:

  • Assuming your earlier PeopleTools client installation was in the C drive, open this folder : C:\Program Files\Oracle\Inventory\ContentsXML
  • Here, you’d have three files listed:
    • comps.xml
    • inventory.xml
    • libs.xml
  • If you open up the inventory.xml file, you’ll notice that it contains references to previous Oracle Client installations
  • Delete the inventory.xml file from C:\Program Files\Oracle\Inventory\ContentsXML and start executing the SetupPTClient.bat again – this time, the installation will continue without any issues

Hope this helps!

Have fun! 🙂

Posted in Oracle, PeopleSoft, Personal, SQL | Tagged , , , , , | Leave a comment

PeopleSoft 9.2 – PeopleTools 8.58 VirtualBox Installation – ElasticSearch

Link to the main post.

We’re almost there!

HCM PI 34 bundles ElasticSearch and Kibana. On Linux, both these directories are available under /opt/oracle/psft/pt/ES/pt.

Sasank’s posts provide a lot of detail about setting up ElasticSearch and Kibana, if they haven’t been started.

  • You can verify if ElasticSearch is up and running by logging into PeopleSoft and performing a global search

SS-32

  • You should be able to see search results as below

SS-33

  • You can also use the Diagnostics Utility to check if ElasticSearch is up and running – from PeopleTools > Search Framework > Utilities > Diagnostics

SS-34

  • Alternatively, you can follow instructions provided in Sasank’s post as well
  • If neither of these work, navigate to the ElasticSearch bin folder and start up and instance of elasticsearch

[root@hcm92 bin]# cd /opt/oracle/psft/pt/ES/pt/elasticsearch7.0.0/bin

  • Against the prompt, switch to the esadm1 user, type nohup ./elasticsearch & and press Enter

[root@hcm92 bin]# sudo -s su esadm1

[esadm1@hcm92 bin]# nohup ./elasticsearch &

  • After few seconds, press the Enter key to return to the command prompt
  • Execute the following commands to verify ElasticSearch is up and running
    • If there are errors, we’d have to troubleshoot – but if the file size does not grow any longer, check for a message that states that “colour was changed from red to yellow” to indicate the service’s status

[root@hcm92 bin]# cat nohup.out | tail -10

    • Execute the following command – you should be able to see a large output that indicates ElasticSearch is up and running

[root@hcm92 bin]# ps -ef | grep elas

If it is all good, return to global search and ensure you can see results.

Posted in Oracle, PeopleSoft, Software | Tagged , , , , | Leave a comment

PeopleSoft 9.2 – PeopleTools 8.58 : Error while Deploying Kibana Visualizations

I expected Kibana to work out of the box for HCM PI 34 – for some reason, I wasn’t able to get the dashboards and visualizations up and running.

If you’re receiving a blank page while accessing the dashboards in Kibana, look no further 🙂

The error message that I received while trying to Deploy Visualizations was like this:

Integration Gateway – External System Contact Error

PTSF_ES_STATWRK.PTSF_ES_STAT_SAVE.FieldFormula Name:DeployVisualization PCPC:9793 Statement:109

Called from:PTSF_ES_STATWRK.PTSF_KIBANA_DEPLOY.FieldChange Statement:1

Integration Gateway was not able to contact the external system. The network location specified may be incorrect, or the site is permanently or temporarily down

SS-37

The first step is to check details provided in the Configure Server tab (from PeopleTools > Search Framework > Administration > Monitoring Server) are correct – in my case, none of the fields from the screenshot below were populated.

SS-40

You can copy the values from PeopleTools > Search Framework > Administration > Search Instance > PTSF_DEFAULT. No problems if you don’t know the password for esadmin (I believe it is setup as part of the bootstrap script) – you can provide some password temporarily and then, execute the following SQL script via SQL developer or Data Mover to sync the actual passwords(as a workaround).

 

UPDATE PS_PTSF_ES_STATDTL SET PTSF_ADMIN_PASSWD = (SELECT PTSF_ADMIN_PASSWD FROM PS_PTSF_SRCH_NODES);

You can now proceed with deploying the Visualizations and it’ll work like a charm!

Hope this helps! 🙂

Posted in Code, Oracle, PeopleSoft, SQL | Tagged , , , , , | Leave a comment

PeopleSoft 9.2 – PeopleTools 8.58 VirtualBox Installation – Kibana

Link to the main post

I’ve seen a lot of posts where people have had Kibana up and running, out of the (virtual)box from HCM PI 34. I had issues getting the dashboards and visualizations to work. Many thanks to folks from Oracle who helped me resolve this issue.

  • The Kibana homepage can be accessed from here : http://hcm92.ps.com:5601/
  • You’d have to replace hcm92.ps.com with your hostname, whereas 5601 is the default port for Kibana
  • When you login to this URL for the first time, it would appear as below
    • Enter the username as PS
    • Enter the appropriate password
    • Enter the Oracle database name – in this case HR92U034

SS-35

  • The dashboards are usually empty until you deploy them
  • For me, it looked like this – even with a completely fresh install

SS-35

  • Kibana was up and running, but of no avail. In case you get an error while accessing the Kibana URL, you can execute the following commands.

[root@hcm92 bin]#cd /opt/oracle/psft/pt/ES/pt/Kibana7.0.0/bin

[root@hcm92 bin]# nohup ./kibana &

  • The next steps were to deploy Kibana dashboards and visualizations
  • To do that, navigate to PeopleTools > Search Framework > Administration > Monitoring Server > Monitoring Visualizations tab
    • Select all dashboards and click on Deploy Visualizations
    • If there were no errors deploying these visualizations, you’d get a message stating so
    • If you received errors, check this post

SS-36

  • Next, deploy dashboards from PeopleTools > Search Framework > Administration > Deploy Kibana Dashboards

SS-37

  • If all goes well, navigate to the Recruiting component and ensure you’re able to see visualizations as below

SS-38SS-39

That brings us to the end of these series of posts on setting up PeopleSoft 9.2 with PeopleTools 8.58.03. Feel free to add your questions and I’ll try to answer them as much as I can! 🙂

Have fun! 🙂

 

 

 

Posted in Oracle, PeopleSoft, Software | Tagged , , , , , , , , , , , | Leave a comment

PeopleSoft 9.2 – PeopleTools 8.58 VirtualBox Installation – SQL Developer & Putty

Link to the main post

If you’ve installed the Oracle Client successfully, you can get SQL Developer up and running in no time.

  • Click on SQL Developer from the Start menu

SS-26

  • Once you get the following window, click on the New Connection button

SS-27

  • In the window that opens, provide the following details:
    • Connection Name of your choice – say, HR92U034 (does not have to be the same name as your database)
    • Username as SYSADM
    • Password to be the same as the one you had provided during the initial installation
    • Select the Save Password checkbox
    • Change Connection Type to TNS
    • Select your database name from the Network Alias dropdown
      • If this doesn’t appear, ensure you’ve copied the tnsnames.ora file to the required directory

SS-28

    • Click on Save
    • Click on Test – ensure there are no errors
    • Click on Connect
    • The SQL editor would appear as below – run a SQL against a table to verify you’re able to run queriesSS-29

To access the Linux command prompt via Putty, download and install the application from here.

  • Click on Putty from the Start Menu
  • In the window that appears, provide your virtual machine’s IP address

SS-30

  • Click on Open
  • When the prompt appears, provide the username as root and the appropriate password

SS-31

That brings us to the end of setting up our environment – almost! The next post would deal with setting up ElasticSearch and Kibana.

Posted in Oracle, PeopleSoft, SQL | Tagged , , , , | Leave a comment

PeopleSoft 9.2 – PeopleTools 8.58 VirtualBox Installation – PeopleTools Installation

Link to the main post

So, if you’ve come this far, you should be able to access the PeopleSoft home page without any issues 🙂

In this post, let us look at installing PeopleTools. First, you’d have to access the files required for the PeopleTools installation.

These are available on a Samba Network Share. You can access them by providing your virtual machine’s IP address. In this case, the IP address is 192.168.1.116.

If you’re on an earlier version of Windows 10, you should be able to access the Samba share without any issues by accessing it via the run prompt:

SS-12

But with newer versions of Windows 10, you’re more likely to get the following prompt to enter your computer’s user name and password:

SS-11

Regardless of what you enter against the username and password fields, it is likely that you get the message “the user name or password is incorrect“. There are various options out there from “Turn On Network Discovery” to “Turning Off Password Authentication for Network Shares” and “Editing the Registry”. Unfortunately, none of them seem to work. If you know of a way to get it working, please leave a comment and I’ll include it in this post.

So, what can you do now to access the shares? There’s an alternative. You can access the files via WinSCP by connecting to the Linux server.

Download and install WinSCP from here. Once installed, access the application and follow the steps provided below:

  • Click on the New Session button

SS-13

  • In the window that opens, click on New Site, enter the Linux Host name, username as root and password for the root user : click on Login once done

SS-14

  • You’d get the following message – click on Yes

SS-15

  • The right pane of your WinSCP window will now look like this

SS-16

  • Press Ctrl + O and paste this path /opt/oracle/psft/pt/tools_client
  • The directory and its contents would look like this

SS-17

  • You can copy these folders to your PC which is on the left pane of WinSCP, by dragging and dropping them into a folder of your choice(on your PC)
  • Open a new cmd session in Administrator mode and navigate to the tools_client folder

C:\Users\MANIKANDAN>cd tools_client

Your prompt should now look like this:

C:\Users\MANIKANDAN\tools_client>

  • Type SetupPTClient.bat and press enter
  • Once the batch script starts executing, you’ll start getting the following messages along with the Oracle Client installation – type N for the first prompt, if you’re setting it up on your PC. The rest is all automatic:

SS-19

If the installation goes through without any issues, your prompt would look like this

SS-20

So far so good! Now lets get the Application Designer and other 2-tier tools installed.

  • If installation completed without any issues, your PT client will be available against C:\PT8.58.03_Client_ORA
  • Open the PS Configuration Manager [pscfg] from C:\PT8.58.03_Client_ORA\bin\client\winx86
  • It looks like this:SS-21
  • Double-click on the pscfg file
  • Provide the database name as configured, User ID as “PS” or as configured, connect ID as people and the password that you had provided during the initial setup for peopleSS-22
  • Click on the Client Setup tab and select all options as displayed below, click on Apply and then OK

SS-23

  • From your tools_client folder, now copy the tnsnames.ora file and paste it under the network folder in your Oracle Home directory
    • In this case, from C:\Users\MANIKANDAN\tools_client to C:\oracle\product\12.1.0\client_1\network\admin or C:\oracle\product\12.1.0\client_2\network\admin
  • Access Application Designer from the Start Menu

SS-24

  • In the window that appears, database and username should be pre-populated – enter the password

SS-25

  • If this is the first time that you’re accessing Application Designer, it would take a while – it is worth the wait! 😉
  • The App Designer window looks like this:

SS-26

That brings us to the end of this post. Let’s look at querying the database(using SQL developer) and setting up Putty in the next post.

 

 

Posted in Oracle, PeopleSoft, PeopleSoft HCM, Software | Tagged , , , , | 2 Comments