PS Query Statistics

All,

Here’s a quick code snippet that you can run to get details on how many times a PS Query has been run, the most recent date time it was run and so on –  the table to use is PSQRYSTATS.

SELECT DISTINCT a.qryname AS "Query",
B.DESCR AS "Query Description",
SUM(a.execcount) AS "# of Runs",
MAX(a.lastexecdttm) AS "Last Run Date"
FROM PSQRYSTATS A,
PSQRYDEFN B
WHERE A.QRYNAME = B.QRYNAME
AND a.qryname LIKE 'X_%'
GROUP BY a.qryname,
b.descr
ORDER BY 3 DESC;

Hope this helps! 🙂

 

Advertisements
Posted in Code, Oracle, PeopleSoft, Uncategorized | Tagged , , | Leave a comment

PSTREE2PDF runs to No Success in PeopleSoft

All,

Here’s an interesting issue:

  1. In any PeopleSoft instance, Navigate to Main Menu > Tree Manager > Tree Manager
  2. Open any tree and click on the Export to PDF hyperlink
  3. In the run control page that appears, click on the Run button and submit the PSTREE2PDF process
  4. If your process runs to No Success, see if the following error message appears:

First operand of . is NULL, so cannot access member name. (180,236) PSTREE2PDF.XMLFile.GBL.default.1 900-01-01.Step01.OnExecute PCPC:27842 Statement:473

Also, verify if you are able to open the record PSTREEMGRVAR (for example). If you get the error message as below, then the issue is related to the Object definitions that the permission list assigned to your user profile can access.
0

To resolve this issue, follow the steps provided below:

Step 1: Navigate to Main Menu > PeopleTools > Security > user Profile and note down your Primary Permission list – say, ALLPAGES

Step 2: Open up an instance of Application Designer

Step 3: Navigate to Go > Definition Security

1

Step 4: Once the PS Definition Security window opens, navigate to File > Open > Permission List

Step 5: Search for your primary permission list and click on OK

Step 6: You would have two panes – Group ID and Excluded Group ID

2

Step 7: Select all applicable items from the right pane and click on the Double arrow button

3

Step 8 : Select File > Save

Step 9 : See if you’re able to access record PSTREEMGRVAR

Now, try re-running the TREE2PDF process and it should run to success now.

Have fun! 🙂

Posted in Code, PeopleSoft, Uncategorized | Tagged , , | Leave a comment

No default message version defined for message error in PeopleSoft

Hello all,

Here’s an error message that I spent a considerable time debugging. I  was trying  to trigger a Full Sync from an App engine, but got the error message listed below:

No default message version defined for message RS_ASSGN_ST_TBL. (2,899) XX_SYNC.MAIN.GBL.default. 1900-01-01.Step01.OnExecute  PCPC:151  Statement:3

It turns out that the Message name defined needs to have the same name as the Service/Service Operation. Doing that, resolved this error. I’m not sure if that is a criteria for setting up a message, but hope this helps! 🙂

 

Posted in PeopleSoft | Tagged , , | Leave a comment

Excel to CI utility not working in Excel 2016

The Excel to CI utility that comes with PeopleSoft is very useful when it comes to loading multiple rows of data from an Excel template onto PeopleSoft. This seems to have been working fine with Excel 2007/2010, but failed with the following error in Microsoft Excel 2016.

Compile error : User-defined type not defined

Compile_Error

Here are the steps that I followed to get the data loaded to PeopleSoft. The primary root cause of this issue is the XML Parser version that defaults in Office 2016 and the DOMDocument code that is used in the VB Script – both have to match, else resulting in an error during compile time. Without further ado, here are the steps!

Here are the instance details for your reference:

Host OS: Windows 10 Home
PeopleTools 8.55.01
PeopleSoft HCM 9.2
Microsoft Excel 2016 32-bit version

Step 1 : Open an instance of Excel 2016 and ensure that the settings are the same as in the screenshot provided below:

You can access the same using File > Options > Trust Center and Click on Trust Center settings

Trust_Center_SettingsTrust_Center_AddInsSettingsTrust_Center_ActiveXSettings

Step 2 : Open the ExcelToCI2007.xlsm file from the PS_HOME directory.

ExcelToCI_HomePage

If you have a Demo version installed on a Windows Host, you can find out the home directory using Configuration Manager.

Open an instance of Configuration Manager

Click on Profile tab and then on the Edit button

Profile_Tab

Click on the Process Scheduler tab and copy the path available under the PeopleSoft Home Directory path

Process_Scheduler_Tab

Step 3 : Next, add the Developer tab to the ribbon

To enable the Developer tab, follow the steps provided below:

Select File > Options

In the Excel options window that opens, click on the Customize Ribbon option. On the right pane, check the Developer option and click OK.

Enable_Developer_Tab

Ensure that the Developer tab appears now and click on it

Step 4 : Click on the Visual Basic icon

Visual_Basic_Button

Step 5 : In the Visual Basic editor that opens, open the StagingAndSubmission code

StagingAndSubmission

Step 6 : Select Debug > Compile ExcelToCI and you would get the error message now

Compile_ExcelToCI

To resolve the error, please follow the steps provided below:

Step 7 : First off, ensure you have the latest MS XML Parser enabled. To verify:

Select Tools > References from within the VB Editor

XML6_References

Ensure Microsoft XML, v6.0 is checked. Else, scroll through the list and select the same. Also, ensure that any other MS XML version like v3.0 is unchecked.

Step 8 : Clicking on OK would set the cursor to the following line in the VB Editor. After changing each occurrence, compile the code using Debug > Compile ExcelToCI to ensure you haven’t missed any occurrence

DOMDocument_VB

Replace every occurrence of DOMDocument with DOMDocument60

Replace every occurrence of ServerXMLHTTP with ServerXMLHTTP60

Replace every occurrence of xDoc As DOMDocument with xDoc As DOMDocument60

Replace every occurrence of xHTTP As ServerXMLHTTP with xHTTP As ServerXMLHTTP60

Replace every occurrence of New DOMDocument with New DOMDocument60

Long story short, add 60 to each DOMDocument name to make it compatible with MS XML 6.0

Code_Modified

Step 9 : Re-compile the code once more and ensure there are no more errors. Close the VB Editor and proceed with loading data using ExcelToCI template. You’re all set!!

Please post your questions in the comment section, if you have any.

Hope this helps! 🙂

 

 

Posted in Code, Operating System, Oracle, PeopleSoft, Software | Tagged , , , , , , , | 16 Comments

Issues with Oracle BI Publisher with Microsoft Word 2016

For PeopleSoft XML reports, we usually add the BI Publisher plug-in to MS Word, load the XML files, create RTF templates and the list goes on.

When you download and install the BI Publisher from PeopleSoft (Design Helper), at first load of Microsoft Word 2016, the BI Publisher tab should appear in the ribbon. If it doesn’t, it means either the add-in is restricted, disabled or does not work. Office 2016 isn’t certified yet to be used with Oracle BI Publisher, but there’s a workaround. Provided below are multiple steps that you can follow to get this issue resolved and have the BI Publisher tab appear permanently in Microsoft Word 2016.

For reference purposes, here are the details of my environment:

Windows 10 Home 64-bit

Microsoft Office 2016 32-bit

PeopleSoft HCM 9.2

PeopleTools 8.55.01

Step 1 : To find out which version of Office 2016 you have installed, open up an instance of Word 2016, File > Account > About Word

Find_Version_Edit

Step 2 : Download and install BI Publisher from Design Helper

Navigation : Main Menu > Reporting Tools > BI Publisher > Setup > Design Helper

Choose the setup file appropriate to your version of Microsoft Word 2016 (either 32/64-bit) and download the same

Design_Helper

Step 3 : Before starting with the installation, ensure all instances of Microsoft Word have been closed. If you’re on a remote desktop, ensure that all instances of Word have been closed by all users who have logged on to the server.

Step 4 : Once the installation is complete,  search for Enable or Disable Template Builder from the installed programs list in Windows

Enable_Or_Disable_Template_Builder

Step 5 : Click on Enable or Disable Template Builder

Enable_Disable_1Enable_Disable_2

Step 6 : Click OK

Click on Button 1

Enable_Disable_3

And you’re done!

Open up an instance of Word 2016 and see if the BI Publisher tab appears in the ribbon. Ensure the BI Publisher tab does not disappear by opening Word 2016 couple of times.

BI_Publisher_Tab_Office_2016

If it doesn’t appear at all or disappears if you open Word 2016 after a while, try the following:

Step 7 : Go to File > Options > Trust Center

Step 8 : Click on Trust Center settings

Trust_Center

Step 9 : Ensure that the settings are the same as in the screenshot provided below:

Trust_Center_Settings

Step 10 : Click on ActiveX settings and ensure that the settings are the same as in the screenshot provided below:

Trust_Center_ActiveXSettings

Step 1 1: Click on Add-ins and ensure that the settings are the same as in the screenshot provided below:

Trust_Center_AddInsSettings

Step 12: Click on OK and then select Add-ins from the Word Options dialog

Step 13 : Check if BI Publisher Template Builder for Word is listed under Inactive Application Add-ins

Step 14 : Select it and at the bottom of the dialog, select COM Add-ins and click on the Go button

AddIns_Inactive

Step 15 : In the COM Add-ins dialog that appears, select the BI Publisher Template Builder for Word checkbox and ensure that the Load Behavior is Load at Startup and not Unloaded.

COM_AddIns_LoadBehavior

Click on OK to close the dialog. Close the Word 2016 instance and open a new instance. The BI Publisher tab should appear in the ribbon.

If it still doesn’t appear in the ribbon, repeat Step 15 again. Ensure that the Load Behavior is not Unloaded.  If the Load Behavior has been reset or if the checkbox has been cleared, please follow the steps provided below.

Step 16 : Ensure that you have Administrator permissions to your system.

Step 17 : Open the run prompt – Windows + R and type regedit

Step 18 : Look at the following two registry entries:

HKEY_CURRENT_USER\SOFTWARE\Microsoft\Office\Word\Addins\TBAddIn

HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\Microsoft\Office\Word\Addins\TBAddIn

Step 19 : Look for the DWORD value named LoadBehavior

Registry_LoadBehavior

Step 20 : Double click on LoadBehavior and set its value to 3 for both Registry paths provided above

LoadBehavior_Value_Changed

Step 21 : Click on OK and exit the Registry Editor. Open the Registry again and ensure that the value has not been overwritten.

Go to step 15 again and ensure that the COM add-in is checked. Close and restart the Word 2016 instance again and see if the BI Publisher tab appears in the ribbon. The BI Publisher add-in will now appear in the Active add-ins list.

AddIns_Active

Hopefully all these steps should resolve this issue.

Please add any questions that you may have in the comments section.

Hope this helps! 🙂

Posted in Operating System, PeopleSoft, Software | 47 Comments

Using Colon in an Expression result in PeopleSoft PS Query

Here’s a quick PS Query snippet : If you’ve ever wanted to use a colon in an expression – say something like displaying a colon between HH(hours) and MM(minutes), PeopleSoft PS Query would throw an error – because the Query parser thinks that the colon represents a bind variable.

Instead, you can create an expression that takes in the ASCII value of colon and use it in your PS Query.

So, your expression should be something like this:

HH | char(58) | MM

and the result would be

HH:MM

Here’s the ASCII table for reference : ASCII

Hope this helps! 🙂

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

Resolving Planned Exit Date cannot be before Job Effective Date. (1000,875) Error in PeopleSoft HCM 9.1

Here’s an error that I spent some time debugging when trying to insert a new row in the JOB component with the Expected End Date field (available in the JOB component, on the first tab, at the bottom left) populated. If the new effective date being inserted is later than the Expected End Date, then the following error message appears – if the data is being saved manually or via a CI.
Planned Exit Date cannot be before Job Effective Date. (1000,875)
The HCM version in question is PeopleSoft HCM 9.1.
The validation is written in JOB_DATES_SBR.EXPECTED_END_DATE.SaveEdit and triggers the error message if all three of the following conditions are met:
Job Expected End date is populated
Job Expected End date is less than the current effective date and
Job Action is not ASC
This error can be resolved by either of the following methods:
  1. Clear out the date from the Job Expected End date field (or)
  2. Set the Job Expected End date to be later than the new effective date

Hope this helps! 🙂

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