SQL Queries To Run A List of Bugs + Pre-Reqs Included in PUM Image

Below are queries that I wrote so I can get a list of bugs contained in PUM image and find out if there are any pre-req bugs associated with one particular bug.

Note: these queries are written in Oracle database, so they may need to be modified to make it work with others.

/* How to get all the bugs and if any pre-req exists included in certain PI */
Select H.ptiasprptno as Bug_ID, H.ptiaspspvers as PI_Release, H.ptiasprptsubject as Subject, P.ptiaspprdname as Product_Name, P.ptiaspprdgrpcd as Product_Group,
       Case NVL(PR.ptiasprptnopost, 0) when 0 then 'N' else 'Y' End as PreReq_Exists
From PS_PTIASPRPTHEAD H
   , PS_PTIASPPRD P
   , (Select distinct ptiasprptnopost
      From PS_PTIASPRPTRQPOVW
      ) PR
Where H.ptiaspspvers = '{Enter PI number e.g. 18}'
  --And H.ptiasprptno = '{Enter Bug Number}'
  And P.ptiaspprdid = H.ptiaspprdid
  And PR.ptiasprptnopost (+) = H.ptiasprptno;


Note: I included PreReq_Exists column on the first query, if 'Y' then plug the Bug_ID into the next query to get the pre-req(s) and what PI image


 /* How to get the pre-req(s) for certain bug */
Select PR.ptiasprptno as Bug_ID, H.ptiasprptsubject as Subject, P.ptiaspprdname as Product_Name, P.ptiaspprdgrpcd as Product_Group, H.ptiaspspvers as PI_Release
From PS_PTIASPRPTRQPOVW PR
   , PS_PTIASPPRD P
   , PS_PTIASPRPTHEAD H
Where PR.ptiasprptnopost = '{Enter Bug Number}'
  And H.ptiasprptno = PR.ptiasprptno
  And P.ptiaspprdid = H.ptiaspprdid;

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.

What the heck is going on with my (Approval Workflow Engine) AWE????

Yes, that is the question that has been haunting me for the past 24 hours.  AWE is not working!

The symptoms are:

1. I am getting errors below when initiating any AWE.

• "Optimistic lock exception at %1.  Please refresh the page try the same operation again. (18081,1009) EOAW_CORE.Utils.OnExecute  Name:ThrowOptimisticLockException  PCPC:19812  Statement:511The problem also occurs when the counter in the table EOAW_IDS is incorrect."
• at Approval process instance (Id = 'JobOpening', Definition ID = 'BYU_Hiring_Mgr_Recruiter', Effective date '1901-01-02', Thread id '48213') (18081,1056):10:1, Step nbr 1 (18081,1058) EOAW_CORE.ENGINE.DefStepInst.OnExecute  Name:Activate  PCPC:9660  Statement:143

2. Status monitor shows old workflow steps.

After poking around some codes and debugging them, I finally found that USERINST_ID and STEPINST_ID counter in the EOAW_IDS table are out of sync with the transactional tables: EOAW_USERINST and EOAW_STEPINST.

So, here is the solution:

Update ps_eoaw_ids set eoawcounter = (Select max(eoawstep_instance) + 1 from PS_EOAW_STEPINST) where eoawcountername = 'STEPINST_ID';

Update ps_eoaw_ids set eoawcounter = (Select max(eoawustep_inst_id) + 1 from PS_EOAW_USERINST) where eoawcountername = 'USERINST_ID';

Boring or Not So Boring Page, You Decide!

I decided in one of my recent developments to use jQuery in Peoplesoft FAQ page for a sleeker and smoother GUI interaction rather than a boring standard peoplesoft page (you know what I meant ;).  I mainly used the accordion for the FAQs and auto complete for the keyword search.  There are at least more than one way to reference the jQuery library from your peoplesoft page, but I found using the HTML definition is quite simple and easy.

This is what the end result looks like:

• Auto complete function will search all possible FAQs from the search box
• The FAQ's answer will be revealed when the FAQ is clicked and the box will smoothly expand/collapse

Here is what I did...

1. Go to jquery.com and download the jQuery v.1.9.1 and jQuery UI v1.10.3 (pick the version that applied to you)
2. Open a new HTML definition and copy/paste the jQuery code into it and I saved it as Y_JQUERY (name whatever you desire)
   
   
3. Open a new HTML definition and copy/paste the jQuery UI code into it and I saved it as Y_JQUERY_UI (name whatever you desire)
   
   
4. Open a new HTML definition and write a jQuery script (find a lot of code samples in jquery website) and I saved it as Y_ACCORDION_JQUERY (name whatever you desire).
   
   Several things to note are the bind variables that I use as input parameters:
   
   • %Bind(:1) = FAQ data
   • %Bind(:2) = url reference to jQuery library
   • %Bind(:3) = url reference to jQuery UI library
   • %Bind(:4) = array of keyword for auto complete search
   
   
   
    
5. Open a new HTML definition and write a javascript to build the url with appropriate query strings, such as Y_FAQ_ID to determine what FAQ to display from the FAQ setup (not included in this tutorial), RETURN_LNK to determine to return link (not included in this tutorial), and TAGS (the field name from the edit box#2 to get the keyword for the search)
   
   
    
6. Create a page definition.  Add HTML #1 (FAQs page w/ jQuery Accordion Style), edit box field (search box), and HTML#3 (search button for auto complete)
   
   
   
   
   
   • HTML area #1
     
   •  Name the page field "TAGS" to the edit box #2
     
   • HTML area #3
     
7. Finally, write the page peoplecode to put pieces together and let the magic happens here!
   
   
   
   That should be it!  Not too bad, huh!

How to use SQLExec with a criteria using IN or NOT IN

SQLExec is one of the most powerful peoplecode command to execute your SQL statement. Of course, there are several other ways to accomplish the same thing, such as CreateSQL, GetSQL, etc...If you ask me what I prefer, it really comes down to whether I need to loop through multiple rows vs single row from table. If multiple rows, CreateSQL and GETSQL will do a fantastic job, otherwise SQLExec is very efficient in fetching single row data.

Now and then, you will use IN or NOT IN SQL criteria to fetch a row from table in peoplecode. This example will use SQLExec technique.

Let say you have a string of values like this: 'A','B','C'. You want to use insert this string as a parameter in the SQLExec.

Bad Example:

Local string &in_values = "'A','B','C'";
Local string &out_val;

SQLExec("Select min(field1) from ps_table_1 Where field1 NOT IN (:1)", &in_values, &out_val);

Result: Bad


Good Example:

Local string &in_values = "'A','B','C'";
Local string &sql_cmd;
Local string &out_val;
&sql_cmd = ("Select min(field1) from ps_table_1 Where field1 NOT IN (" &in_values ")";

SQLExec(&sql_cmd, &out_val);

Result: Good


Happy Coding... :)

Component Error "You Are Not Authorized..."

This morning I was helping my colleague troubleshooting this common peoplesoft component error "You are not authorized to access this component. (40,20)"

It's not fun, HUH!...

Have you checked?
1. Component in the menu
2. Access to component in the permission list
3. permission list in the role
4. the role assigned to the user profile
5. clear browser cache, close the browser, reopen browser
6. clear and reboot app server cache
Ya..Ya..all checked, but why it still errors out??? I know your frustration, and don't bang your head against the table yet.

This is what I found today that I want to add it into the list:

7. non-existing permission list assigned to the role.

What!! How could it be possible?

The only way that non-existing permission list in the role is by doing a project migration from database to database. You may think the permission list in the project, but apparently not.

If you were like me, I do not add roles, permission lists, menu in my project. Unless you are the sole developer for the system, I would encourage to stay away from it.

Good Luck!

Code Tip:
Here is a SQL to check if there is non-existed permission list which is still attached to role:

Select R.rolename
, RC.classidFrom psroleuser R
      , psroleclass RCWhere RC.Rolename = R.Rolename
    And not exists
          (Select 'X' from psclassdefn C
           Where C.classid = RC.classid)
    And R.roleuser = '[--OPRID--]'

Note: Replace [--OPRID--] to the actual user oprid.

Cool Trick! Dynamic Prompt Table Using PeopleCode

Prompt table is used frequently in peoplesoft pages to eliminate human input error to the database. It is simple and easy to use for end users. Most of the prompt tables are displaying same results to all users from the underline table or view. However, there is time when you need to display different result based on a page/field condition, a previous field selection, or perhaps a different group of users. Can we do this?

YES! Have you heard of using dynamic view and sqltext in peoplecode? If you have not, then this maybe useful for you.

The cool thing about using a sqltext in peoplecode is to override the sql object in your view. First, you need to create a dynamic view that you will use as a prompt table. You may want to write a generic SQL in the view with all the fields you need to display in the search result.

Once you finished with creating dynamic view, then you can assign it as a prompt table edit. When the user click on the prompt table lookup from the page, it will display results from your generic SQL.

Now, in your peoplecode you can modify the generic SQL in the dynamic view by writing your own SQL with additional criteria using sqltext.

/* Update SQLText of the dynamic view */
RECORD_NAME.RECORD_FIELD.SqlText = "SELECT VAL1, VAL2 FROM PS_TABLE Where field_criteria = '" | &criteria_variable | "'";

Note:
1. Replace RECORD_NAME, RECORD_FIELD, VAL1, VAL2, PS_TABLE, field_criteria with your own
2. Assign criteria value to &criteria_variable

That's it, easy, huh!