Friday, October 21, 2016

Automated Data Archive

Setting up a process to archive data in PeopleSoft requires two steps.  The first copies the data to your defined history table.  Oracle has forced you to run a second step where you choose the batch ID of your first step to delete the data from the source table.  It is well documented that you "Must have a secondary step to delete archived data from online tables".  The problem I was having is that our requirement was to have this to all happen on a schedule without manual intervention.  I used this solution to setup the entire process in a scheduled Jobset.

PIA Setup

Data Archive Manager

  PeopleTools  \ Data Archive Manager \  Homepage
  1. Create a Archive Record following Oracle Documentation and your Application Engine.
  2. Define an Archive Object identifying the Archiving table and the history record from step 1
  3. Create a Public Query to identify the rows you want archived
  4. Define your Archive Template using the Archive Object from step 2
    1. Add your Query for the Selective Archiving Query
    2. Add Archive Selection AE Process Post AE Program MCM_ARCH_BAT
  5. Create Run Controls
    1. My Archive example I name it MCM_ARCHIVE
      • Using your new Archive Template, Selection process type
      • Set the selective query to your query added in the template
    2. Create your delete run control with the same name adding "_DELETE" in my example it us named MCM_ARCHIVE_DELETE
      • Using your new Archive Template, Delete process type
      • Note:  You need to run the full MCM_ARCHIVE process and delete once manually in order to create a delete run control because Batch Number is a required field.

Jobs Setup

  PeopleTools \  Process Scheduler \  Jobs

Create a job and add 2 application process instances of PSARCHIVE.


 PeopleTools \ Process Scheduler \ Schedule JobSet Definitions

You can now add the Job you created to a existing Jobset or a new Jobset.

  1. Set the first instance to your ARCHIVE run control
  2. Set the second instance to the _DELETE run control



Running the Job

Now when this Jobset is run the job will first execute the PSARCHIVE step copying the selection criteria data to the history table.  Because of the Archive Template post AE task an App Engine will run updated your batch ID in the second Run Control to the same batch ID run in the first process.  The job then executes the second process to delete the batch from the archiving table.

Record and Application Engine Setup

  1. Create a Archive Record following Oracle Documentation 
  2. Create your Application Engine as defined below.  In my example it was named MCM_ARCH_BAT
Your Application Engine needs 2 actions. The first one is a standard SQL Init step that loads your state record. I used the delivered record ARCH_RPT_AET as a state record because it already contained the fields I needed.

%Select(ARCH_RPT_AET.OPRID,ARCH_RPT_AET.RUN_CNTL_ID,ARCH_RPT_AET.PSARCH_ID) 
 SELECT OPRID 
 , RUN_CNTL_ID 
 , PSARCH_ID 
  FROM PSARCHRUNCNTL 
 WHERE OPRID = %OperatorId 
   AND RUN_CNTL_ID = %RunControl

The Second is a PeopleCode Action to update the Delete version of the run Control.
/* Description:  The AppEngine will be used as in a Archive Template 
              to update a specific Delete Run Control
*/

Local number &batchNumber;
Local string &DeleteRunControl;
Local Record &Rec;

/* What process is currently running */
MessageBox(0, "", 0, 0, "AET:%1, %2, %3", 
           ARCH_RPT_AET.PROCESS_INSTANCE, ARCH_RPT_AET.RUN_CNTL_ID, ARCH_RPT_AET.PSARCH_ID);

/* What Batch ID was created for this Archive Process */
Local String &qry = "SELECT PSARCH_BATCHNUM FROM PSARCHBATCH WHERE PROCESS_INSTANCE = :1";
SQLExec(&qry, ARCH_RPT_AET.PROCESS_INSTANCE, &batchNumber);
MessageBox(0, "", 0, 0, "This Batch ID: %1", &batchNumber);

/* Check that the matching Delete Run Control exists */
&DeleteRunControl = ARCH_RPT_AET.RUN_CNTL_ID | "_DELETE";

&Rec = CreateRecord(Record.PSARCHRUNCNTL);
&Rec.OPRID.Value = ARCH_RPT_AET.OPRID;
&Rec.RUN_CNTL_ID.Value = &DeleteRunControl;
&Rec.PSARCH_ID.Value = ARCH_RPT_AET.PSARCH_ID;
If &Rec.SelectByKey() Then
   /* Delete Run Control Found */
   If &Rec.PSARCH_PROCESS.Value = "D" Then
      /* Set batch value */
      &Rec.PSARCH_BATCHNUM.Value = &batchNumber;
      If Not &Rec.Update() Then
         MessageBox(0, "", 0, 0, "Error updating %1", &DeleteRunControl);
      End-If;
   Else
      MessageBox(0, "", 0, 0, "Your matching run control is not setup as Delete");
   End-If;
Else
   MessageBox(0, "", 0, 0, "You do not have a matching run control for the DELETE step");
   MessageBox(0, "", 0, 0, "Missing Run Control %1, ARCHIVE ID %2 for user %3"
              , &DeleteRunControl, ARCH_RPT_AET.PSARCH_ID, ARCH_RPT_AET.OPRID);
End-If;

Monday, September 12, 2016

DELETE SQL performance

Delete Performance and ROWID 

We discovered slow performance on a DELETE statement that was using an EXISTS joining a related table and temporary table.  The problem is the entire subselect is executed returning a lot of  data to be evaluated only to delete a few rows from the source table.

To speed this up I added the original source table to the join and the driving table that gave me the smallest set of data.  In this case it was to join SAD_EXT_CRS_COM to EXT_COURSE followed by joining to the temporary table.  Now the the optimizer also help the subselect run extremely fast.  At this point we just need to convert the EXISTS to an IN statement and use the oracle pseudocolumn ROWID which is the fastest way to access a single row. The delete query now executes in no time.

Slow version using WHERE EXISTS

 DELETE FROM %Table(SAD_EXT_CRS_COM) c 
WHERE EXISTS ( SELECT NULL FROM %Table(EXT_COURSE) p 
                     , %Table(M_ET_PST_TMP) t 
               WHERE t.process_instance = %Bind(process_instance) 
                 AND t.oprid = %OperatorId 
                 AND p.emplid = t.emplid 
                 AND p.ext_org_id = t.ext_org_id 
                 AND p.ls_data_source = 'OET' 
                 AND p.ext_data_nbr = t.ext_data_nbr 
                 AND c.emplid = p.emplid 
                 AND c.ext_org_id = p.ext_org_id 
                 AND c.EXT_COURSE_NBR = p.EXT_COURSE_NBR ) 

Faster version using ROWID IN

DELETE FROM %Table(SAD_EXT_CRS_COM) c 
WHERE ROWID IN ( SELECT C.ROWID FROM %Table(SAD_EXT_CRS_COM) C 
                   JOIN %Table(EXT_COURSE) P ON C.EMPLID = P.EMPLID 
                    AND C.EXT_ORG_ID = P.EXT_ORG_ID 
                    AND C.EXT_COURSE_NBR = P.EXT_COURSE_NBR 
                   JOIN %Table(M_ET_PST_TMP) T ON P.EMPLID = T.EMPLID 
                    AND P.EXT_ORG_ID = T.EXT_ORG_ID 
                    AND P.EXT_DATA_NBR = T.EXT_DATA_NBR 
                   WHERE P.LS_DATA_SOURCE = 'OET' 
                     AND T.PROCESS_INSTANCE = %Bind(process_instance) 
                     AND T.OPRID = %OperatorId ) 

Friday, May 27, 2016

Peoplesoft Menu Listing or Search

SQL for Menu Searching

Here is a very handy way to either export a full listing of the PeopleSoft Menu or search for a specific menu item.
----  Menu Look-up
SELECT SRCH.PORTAL_LABEL LABEL
   , NVL(SRCH.PORTAL_URI_SEG2, ' ') COMPNOENT
   , SRCH.PORTAL_OBJNAME
  --L1.PORTAL_LABEL,  L2.PORTAL_LABEL,  L3.PORTAL_LABEL,  
  --L4.PORTAL_LABEL,  L5.PORTAL_LABEL,  L6.PORTAL_LABEL
  ,'Main Menu > ' || L1.PORTAL_LABEL || decode(L1.PORTAL_REFTYPE, 'F', ' > ')
  || L2.PORTAL_LABEL  || DECODE(L2.PORTAL_REFTYPE, 'F', ' > ')
  || L3.PORTAL_LABEL  || DECODE(L3.PORTAL_REFTYPE, 'F', ' > ')
  || L4.PORTAL_LABEL  || DECODE(L4.PORTAL_REFTYPE, 'F', ' > ')
  || L5.PORTAL_LABEL  || DECODE(L5.PORTAL_REFTYPE, 'F', ' > ')
  || L6.PORTAL_LABEL NAV_PATH,
  decode(SRCH.PORTAL_REFTYPE, 'F', ' ', 'C', SRCH.DESCR254) DESCR
FROM PSPRSMDEFN L1
  LEFT JOIN PSPRSMDEFN L2
  ON L2.PORTAL_NAME         =L1.PORTAL_NAME
  AND L2.PORTAL_PRNTOBJNAME = L1.PORTAL_OBJNAME
  LEFT JOIN PSPRSMDEFN L3
  ON L3.PORTAL_NAME         =L2.PORTAL_NAME
  AND L3.PORTAL_PRNTOBJNAME = L2.PORTAL_OBJNAME
  LEFT JOIN PSPRSMDEFN L4
  ON L4.PORTAL_NAME         =L3.PORTAL_NAME
  AND L4.PORTAL_PRNTOBJNAME = L3.PORTAL_OBJNAME
  LEFT JOIN PSPRSMDEFN L5
  ON L5.PORTAL_NAME         =L4.PORTAL_NAME
  AND L5.PORTAL_PRNTOBJNAME = L4.PORTAL_OBJNAME
  LEFT JOIN PSPRSMDEFN L6
  ON L6.PORTAL_NAME         =L5.PORTAL_NAME
  AND L6.PORTAL_PRNTOBJNAME = L5.PORTAL_OBJNAME
  LEFT JOIN PSPRSMDEFN SRCH
  ON SRCH.PORTAL_NAME       = 'EMPLOYEE'
  AND SRCH.PORTAL_OBJNAME   
    = NVL(L6.PORTAL_OBJNAME,NVL(L5.PORTAL_OBJNAME,NVL(L4.PORTAL_OBJNAME, 
           NVL(L3.PORTAL_OBJNAME,(NVL(L2.PORTAL_OBJNAME,NVL(L1.PORTAL_OBJNAME,' ')))))))
WHERE L1.PORTAL_NAME ='EMPLOYEE' AND L1.PORTAL_PRNTOBJNAME = 'PORTAL_ROOT_OBJECT'
--*************************************************************************************--
--  Search for Items
--*************************************************************************************--
  --AND SRCH.PORTAL_URI_SEG2    = 'MCM_ADV_SUMMARY'     -- Component
  --AND SRCH.PORTAL_LABEL       LIKE 'PeopleTools%'     -- Label
  --AND L1.PORTAL_OBJNAME       = 'PT_PEOPLETOOLS'      -- Folder/Ref Object Name
ORDER BY L1.PORTAL_REFTYPE DESC,  L1.PORTAL_LABEL,
  L2.PORTAL_REFTYPE DESC,  L2.PORTAL_LABEL,  L3.PORTAL_REFTYPE DESC,  L3.PORTAL_LABEL,
  L4.PORTAL_REFTYPE DESC,  L4.PORTAL_LABEL,  L5.PORTAL_REFTYPE DESC,  L5.PORTAL_LABEL,
  L6.PORTAL_REFTYPE DESC,  L6.PORTAL_LABEL ;

Tuesday, December 8, 2015

Component Verification SQL

Component Verification

This query was built as part of a script to review projects.  The objective was a quick way to confirm all the components in your project are following the company standards. Peoplesoft will often use a Binary bit mapping to turn several flags into a single decimal value.  In this case the column SHOWTBAR is a numeric SUM of 6 binary values 111111 = 1+2+4+8+16+32 = 63.  If you experiment with these you will notice some odd behavior like the Disable Toolbar when checked doesn't add 1 to the decimal but Disable Pagebar when checked adds the value 2.

Columns
  1. What is the default Search setting for the component
  2. What flags are set for "Multi Page Navigation"
  3. Disable Toolbar Flag
  4. Disable Pagebar Flag
  5. Disable Help URL
  6. Disable Copy URL
  7. Disable New Window
  8. Disable Customize Page
Binary Mapping for SHOWTBAR
  • +1 Disable Toolbar is Unchecked
  • +2 Disable Pagebar is Checked
  • +4 Help Link is Uncheked
  • +8 Copy URL Link is Unchecked
  • +16 New Window Link is Unchecked
  • +32 Customize Page Link is Unchecked



select PNLGRPNAME as Component,
CASE DFLTSRCHTYPE WHEN 0 THEN 'BASIC SEARCH *ERROR*' 
                  WHEN 1 THEN 'ADVANCED SEARCH' 
                  ELSE to_char(DFLTSRCHTYPE) END as SEARCH_TYPE,
CASE PNLNAVFLAGS  WHEN 0 THEN 'MULTI-PAGE NAV OFF' 
                  WHEN 1 THEN 'FOLDERS (TOP)' 
                  WHEN 2 THEN 'HYPERLINKS (BOTTOM)' 
                  WHEN 3 THEN 'FOLDER + LINKS NAV ON' 
                  ELSE TO_CHAR(PNLNAVFLAGS) END AS NAVIGATION_TYPE
,DECODE(BITAND(PSPNLGRPDEFN.SHOWTBAR,1),1,'N','Y') AS DISABLE_TOOLBAR
,DECODE(BITAND(PSPNLGRPDEFN.SHOWTBAR,2),2,'Y','N') AS DISABLE_PAGEBAR
,DECODE(BITAND(PSPNLGRPDEFN.SHOWTBAR,4),4,'N','Y') AS SHOW_HELP_URL
,DECODE(BITAND(PSPNLGRPDEFN.SHOWTBAR,8),8,'N','Y') AS SHOW_COPY_URL
,DECODE(BITAND(PSPNLGRPDEFN.SHOWTBAR,16),16,'N','Y') AS SHOW_NEW_WIN
,DECODE(BITAND(PSPNLGRPDEFN.SHOWTBAR,32),32,'N','Y') AS SHOW_CUSTOMIZE_PAGE
FROM PSPNLGRPDEFN WHERE PNLGRPNAME IN (SELECT PI.OBJECTVALUE1 FROM PSPROJECTITEM PI WHERE PI.PROJECTNAME = 'MY_PROD_NAME' AND PI.OBJECTTYPE=7);

Wednesday, August 26, 2015

String to Boolean


I needed a way to convert a string to a boolean. Specifically, I wanted to use a message in the message catalog to store a variable to allow us to easily change the flow in a specific page. This turned out to be much easier than I expected.

Using the following short hand returns a true if the string matches or false if it does not.

(&theString = "Y")

Here is how I used it with the message catalog text:
Local array of number &openMonths = CreateArray(1, 5, 9);
Local boolean &openSeason = (MsgGetText(21027, 8, "false") = "true");
...
If (&openMonths.Find(Month(&today)) > 0 And
      Day(&today) < 16) Or
      &openSeason Then
       ...
end-if;
     

Tuesday, April 28, 2015

Peoplesoft Styles Demo

View Peoplecode Styles

SQL


Run this SQL against your database and copy the output.
SELECT '<div class="' || STYLECLASSNAME || '">' || STYLECLASSNAME || '</div>'
FROM PSSTYLECLASS WHERE STYLESHEETNAME = 'PTSTYLEDEF'

Page

Temporary add an HTML area to any page and paste your SQL output into the value constant of your HTML area. View your page and you'll see a demo of every style for that Style sheet name in the query you ran.

Extra

If you want to create a more permanent page in your development environment that dynamically loads the HTML area with any style sheet you choose from the system you can do the following:

 Record

Create a new derived record (MY_RECORD) and add the fields
  • STYLESHEETNAME  (prompt table edit : EOPP_STSHEET_VW)
  • HTMLAREA

Page

Create a new page and add your both your derived record fields to it.  

Component

Create a component add your new page to it and add the following Peoplecode to the STYLESHEETNAME FieldChange event.

Local string &qry, &qoutput, &html;
Local SQL &sql;
Local array &AAny = CreateArrayAny();

&qry = "SELECT '<div class=' || STYLECLASSNAME || '>' || STYLECLASSNAME || '</div>'";
&qry = &qry | " FROM PSSTYLECLASS WHERE STYLESHEETNAME = :1 order by STYLECLASSNAME ";

&html = "";

&sql = CreateSQL(&qry, MY_RECORD.STYLESHEETNAME);
While &sql.Fetch(&AAny)
   &html = &html | &AAny [1];
End-While;

MY_RECORD.HTMLAREA.Value = &html;

Register your component to the menu and load your new page.

Thursday, April 23, 2015

PeopleCode Reference Links

Reference Links

Google is one of a programmers best friends and after a while you build up a small collection of great resources.  Here are a couple of my favorite places to find People code solutions and examples:

PeopleCode Language Reference 8.53

  • Built-in Functions
  • Meta-SQL
  • System Variables
  • Meta-HTML

PeopleCode API Reference 8.53

Every class in a great Tree view that includes a quick link to the class details such as:
  • Understanding
  • Using
  • Declaring
  • Built-in Functions
  • Methods
  • Properties
  • etc....

PeopleCode Developers Guild 8.43


  • Operators
  • Data Types
  • Expressions
  • Variables
  • Editors
  • Events
  • Debugging
  • Short Cuts

PeopleCode Built-in Functions 8.50

Listing of every built in function in one place sorted by category.  
With Frames version.

Toolbox.com 

Message board with many users who post solutions.and is most often referenced when doing a Peoplecode google search.


I have a few other links in my links and references page.