Dynamic Position Title from Jobcode vs Position Data

On a several different occasion, I was asked how we dynamically derive a position title from the position data table when the position number exists in the job data instead of from jobcode table.

So, I wrote this SQL to return emplID, empl record, and dynamic position title (derived from jobcode vs position data).

You can also make this SQL as record view and you can call it using peoplecode to return a dynamic position title you desire.

For this example, I am querying emplID: KU0010.

Note: replace {less than sign} with <
For some reasons, it keeps translating and displaying it to < (If anyone has suggestion to fix the dispay, please let me know.  I just don't have time to research it right now)

Additionally, I am running this in Oracle database, so sysdate will work just fine.  If you are using MSSQL database, you know what to do.

SELECT job.emplid 
 , job.empl_rcd 
 , CASE pos.descr WHEN '' THEN jobcd.descr ELSE pos.descr END 
  FROM ps_job job LEFT JOIN ( 
 SELECT J1.setid 
 , J1.jobcode 
 , J1.Descr 
  FROM ps_jobcode_tbl J1 
 WHERE J1.effdt = ( 
 SELECT MAX(effdt) 
  FROM ps_jobcode_tbl 
 WHERE setid = J1.setid 
   AND jobcode = J1.jobcode 
   AND effdt {less than sign}= SYSDATE)) jobcd ON jobcd.setid = job.setid_jobcode 
   AND jobcd.jobcode = job.jobcode LEFT JOIN ( 
 SELECT P1.position_nbr 
 , P1.descr 
  FROM ps_position_data P1 
 WHERE P1.effdt = ( 
 SELECT MAX(effdt) 
  FROM ps_position_data 
 WHERE position_nbr = P1.position_nbr 
   AND effdt {less than sign}= SYSDATE)) pos ON pos.position_nbr = job.position_nbr 
 WHERE job.effdt = ( 
 SELECT MAX(effdt) 
  FROM ps_job 
 WHERE emplid = job.emplid 
   AND empl_rcd = job.empl_rcd 
   AND effdt {less than sign}= SYSDATE) 
   AND job.effseq = ( 
 SELECT MAX(effseq) 
  FROM ps_job 
 WHERE emplid = job.emplid 
   AND empl_rcd = job.empl_rcd 
   AND effdt = job.effdt)
 AND job.emplid like 'KU0010';

How to add ROWNUM in a Record View

Let's say that I have a table called Y_DISCUSSION with 6 existing columns.  Now, I want to add the 7th column that contains row number.  How do I do that?

It's pretty easy actually...

• 1 - Create a Record View with 7 columns, the last column will be used as a row number.
  
  
  
  
  
  
  
  
  
  
  
  
• 2 - Write the SQL Definition in this format below
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
• 3 - Save and build the view
• 4 - Run the view and get the result below
  
  

Now I have the rownum in the table, I can use the rownum in my selection criteria, such as rownum between 5 and 10.

Ah! That's where the SQL Developer pathname for java.exe is!

I keep forgetting where the java.exe located in my Oracle client, so I left myself a note here ;)

When opening a SQL Developer for the first time after the new client installed, you will get a prompt like this below:

The path is pretty simple, but if you have to install the Oracle client every once a while, finding the specific file can be frustrating.

Here it is:

C:\Oracle\client\product\12.1.0\client_1\jdk\bin\java.exe

Note: your Oracle home path may be different from mine above.

Fix: Getting Error When Undeploying Secure Enterprise Search (SES) Search Definition

If you are getting the error below trying to undeploy search definition, then you may have the similar issue that I recently had after the database refresh.

Service Exception: ns2:CreatableAdminObjectFault : EQA-11000: The object with key "[name=PTPORTALREGISTRY_HRPRD]" and type "schedule" was not found. (262,1018) PT_SEARCH.SESIMPL.MESSAGE.AdminResponse.OnExecute  Name:AdminResponse  PCPC:1452  Statement:20
Called from:PT_SEARCH.SESIMPL.AdminService.OnExecute  Name:doService  Statement:848
Called from:PT_SEARCH.SESIMPL.AdminService.OnExecute  Name:delete  Statement:802
Called from:PT_SEARCH.SESIMPL.AdminService.OnExecute  Name:RemovePSFTSource  Statement:248
Called from:PTSF_DP_SBO_WRK.PTSF_UNDEPLOY_BTN.FieldChange  Statement:111 

Service Exception

There is a useful tutorial on how to resolve this sync issue.  If that solved your problem, great!

if not, continue reading, this may help you further.

After further checking, I found that the reason why I was getting an error while trying to undeploy the search definition is because the search definition deployed name in my PeopleSoft database did not exist in the SES database. 

Why? Because after the database refresh, the deployed name in my PeopleSoft Test database is now replaced with the one from the Production database.

Pay attention to the error message again.  Notice that the name has _HRPRD which is my Prod Database name.
 
When logged in to the SES Admin console, the name did not exist and the correct name should be PTPORTALREGISTRY_HRSTG.

Service Exception: ns2:CreatableAdminObjectFault : EQA-11000: The object with key "[name=PTPORTALREGISTRY_HRPRD]" and type "schedule" was not found. (262,1018) 

Okay, here is to fix it.

We need to change the deployed name inside the PTSF_DEPLOY_OBJ table.

First , I did a quick select all the search definition deployed names that end with _HRPRD

Note: HRPRD is the database name.  Yours will be different.

Select * from PS_PTSF_DEPLOY_OBJ where ptsf_deployed_name like '%HRPRD'

I got one result:



Next, I updated all the deployed name and replace _HRPRD with _HRSTG

Update PS_PTSF_DEPLOY_OBJ set ptsf_deployed_name = substr(ptsf_deployed_name, 1, length(ptsf_deployed_name) - 5) || 'HRSTG' where ptsf_deployed_name like '%HRPRD'

After I committed and I ran the select for the new name, I got the following result



Lastly, I went back to Main Menu > PeopleTools > Search Framework > Administration > Deploy/Delete Object, selected PTPORTALREGISTRY search definition and clicked "Undeploy" button.

Result: HOORAY!! No more error message.

Action Plan: Talk to the DBA to restore PS_PTSF_DEPLOY_OBJ after the DB refresh so you won't have to do this again for every refresh.

Hope this helps.