BI Publisher PDF Issue – ViewAttachment

Quick post!

We had a PDF generated using BI Publisher and the same file was stored as an attachment in a record PSFILE_ATTDET.

However, while trying to view the attachment using the ViewAttachment function, the PDF file prompted to be downloaded to local storage, instead of opening directly in a new browser tab. This was earlier working with PeopleTools 8.53, but not with upgraded PeopleTools 8.58.

Turns out, that starting PeopleTools 8.55, X-Content-Type-Options: nosniff response header is added to content retrieved using the ViewAttachment function.

This was the code I had:

Local number &retcode = ViewAttachment(URL.XXX, XXXX.ATTACHSYSFILENAME, "Attachments");

As this response header was not part of PeopleTools 8.53, though I did not specify the file extension in the aforementioned code, PeopleSoft was able to determine the appropriate application type and open the PDF file without any issues.

However, with 8.58, without the file extension, it appears that the response header blocked auto-detection and hence, the issue. This was fixed this by adding .pdf as below.

Local number &retcode = ViewAttachment(URL.XXX, XXXX.ATTACHSYSFILENAME, "Attachments.pdf");

Hope this helps! 😊

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! 🙂

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! 🙂

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)

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

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

Hope this helps! Have fun! 🙂

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 directory
• 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

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

• 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:

• 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! 🙂

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

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

• 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! 🙂

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

• You should be able to see search results as below

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

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

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

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.

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! 🙂

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

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

• 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

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

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

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! 🙂

 

 

 

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

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

• 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

• • 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 queries

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

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

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