Thursday, 9 February 2017

Creating Left Outer Joins in PSQuery

To create a left outer join on a new query:
  1. On the Query Manager search page (Reporting Tools, Query, Query Manager), click the Create New Query link.
  2. On the Records page, search for the first (left) record for the left outer join.
  3. Click the Add Record link on the same row as the record that you want to add.

    The Query page appears.

  4. Select the appropriate fields to add to the query.
  5. Navigate to the Records page.
  6. Find the joining record and then click the Join Record link on the same row as that record.
  7. Select Join to get additional fields only (Left outer join).
  8. Complete the join.
    • If the Enable Auto Join preference is selected on the Query Preference page, click the record name to join with.
    • If the Enable Auto Join preference is not selected on the Query Preference page, click the OK button.
  9. Define the join criteria.
    • If the Enable Auto Join preference is selected, the Auto Join Criteria page appears.
      You can clear the criteria from the query. You can also click the Add Criteria button to add or edit criteria or conditions. If no common keys are between the two join records, a message appears instead of the Add Criteria page.
    • If the Enable Auto Join preference is not selected, the Auto Join Criteria page does not appear.
      If you want, navigate to the Criteria page to add criteria to the ON clause of the outer join. The This Criteria Belongs To drop-down list box specifies where the criteria will appear. Select the alias that corresponds to this join record.
  10. Define the effective date criteria.
    • For 2–3 tier client: If the joined record is an effective-dated record, the Effective Date Criteria page appears.
      You can accept the defaults or change them as desired.
    • For 4–tier PeopleSoft Pure Internet Architecture: If the joined record is an effective dated record, a message appears stating that an effective date criteria has been automatically added.
      Optionally, you can navigate to the Criteria page to change the defaults for this criteria.
  11. To ensure that the left outer join finished successfully, navigate to the Query page.
    Text for the joined records confirms a successful left outer join.
  12. (Optional) If the query has multiple joins to the same security record, which can be resource intensive and time consuming when it runs, select the Security Join Optimizer option to improve the performance of this query.


Thursday, 8 December 2016

Difference between do-while, do-when, do-until and do-select in app engine.

I have sometimes found the Do actions in an Application Engine to be pretty confusing – especially the nature of iterations that happen with each of these actions. I thought it would be informative to share some insights into the working of these actions in an AE program.

Consider the following scenario:

If we have a select statement as below: 

Select emplid from PS_JOB where action_dt = sysdate order by emplid.

Suppose executing this query on your DB returns 10 rows. To understand the working of the Do actions, if I embed this query in an AE and print the emplid in a file, how many EMPLIDs will be printed in case of each Do actions?

The Do When in the above diagram can be replaced by Do While and Do Select, while checking for the number of iterations. The Do Actions will contain the SQL select statement as mentioned before and the Peoplecode will write the Emplid value to a File. In case of Do Until, Peoplecode will precede Do Until (as Do Until is the last action).

Coming back to our original question.

How many times will the EMPLID be written in the file in each of these cases??

Do When: Do When is a loop entry criteria. This will always be executed once and only once as long as the SQL statement fetches a row. Thus, with Do Until, one row will be printed. This is pretty straight forward.

Do While: All those of you who thought that the Do While loop will iterate 10 times will need to rethink the working of this loop. Do While will continue executing until at least one row is fetched. The background execution logic of a Do While loop is as follows: Every time, the AE executes the SQL select statement in the Do While Action, if it returns a row it will proceed to further actions, if not the step will be terminated. What is to be understood here is that, in the absence of a loop termination criteria (or in other words logic to discard already selected rows) the Select statement inside a Do While will always fetch the same row. This will mean that a query like the one we have written (select emplid from ps_job) will go into an infinite loop, as there is no termination criterion for this SQL. Thus, this will go into an infinite loop and the same EMPLID (the first Id returned by the query) will be printed in the file. Always keep in mind that a loop termination criterion is absolutely necessary in case of a Do While.

Do Until: Do Until is uncomplicated. It will execute until a row is returned by the query and this ensures that the step is executed atleast once. As the query returns a row, the loop gets terminated after the first iteration itself.

Do Select: Do Select is the most commonly used Do action. But I left Do Select for the last because even this is kind of tricky like Do While. I do not know how many of you have noticed the various types of a Do Select action:

What concerns us out here is the difference between Select/Fetch and Reselect. Will there be any difference if I run the Do Select in these two different modes?

Yes, the working of the Do Select is completely different in these two cases. In case of a Select/Fetch the loop will iterate 10 times, while for Reselect an infinite loop is triggered (exactly similar to a Do While). The reason lies in the difference in working of these two Do Select types. Select/Fetch hits the Db just once, fetches all the values of the Select statement and puts it in a cursor and moves through the cursor during each iteration. This allows a sequential processing which is so integral to row by row processing in an Application Engine. While Reselect queries the DB for each iteration and picks up the first row returned by the select statement and puts it in a cursor. Thus, at any given point of time there will be only one row in the cursor of a Reselect type Do Select action. Moreover, Reselect will be slower than a Select/Fetch due to the DB trips involved in the former. Why does Reselect go into an infinite loop? This happens because for each iteration, the same SQL select statement is fired and the row returned first is buffered into the cursor (unlike a Select/Fetch where the SQL is executed against the DB once and then the AE moves successively through the cursor and terminates once it reaches the end of the cursor). Thus, in case of a Do Select of Reselect type, we will get a result similar to Do While – an infinite loop with the same EMPLID printed.

So what is the difference between a Do Select of Reselect type and a Do While? Remember that Reselect is used when the Restart function of an AE is turned on. A Select/Fetch Do Select does not execute commits for the entire action and thus will not be useful in case of a restart. This deficiency is overcome by the Reselect type, where commits are executed during the Step execution. At hindsight, I would always recommend to use the Restartable function (which is similar to Select/Fetch but with commits turned on) over the Reselect option. Coming to the difference between a Do Select (Reselect) and a Do While boils down the elementary question – why do we have two separate actions if they perform the same function? Do While and Do Select are fundamentally different, the first is similar to a FOR loop (executes for a definite number of times), while the latter is similar to a WHILE loop (executes till a condition is true). That is exactly the reason why a Do While comes before a Do Select.


Monday, 14 November 2016

Validating Email Address in Peoplecode

We have a number of pages in PeopleSoft, where user/admin is free to enter email addresses. How to validate if a value entered in a PeopleSoft field is a valid email address?
In general, how does an email id look like? It will have few characters followed by @ symbol followed by few characters and then a .(dot) symbol followed by few more characters. So, we have to validate the presence of these.
For example, if &email_addr is a variable which contains the string entered in email address field and needs to be validated then try the below code:
&AT = Find("@", &email_addr);
&DOT = Find(".", &email_addr);

If ALL(%AT,&DOT) Then
/* Validation Success */
/* Validation Fail*/
/* Issue Error message */
Additionally we could also perform checks to ensure that:
  • the position of @ is not the first character
  • .(dot) is not the last character.
  • @ is not repeated else where in the string
  • Dot is not repeated else where in the string
This can be done by using instr, substr and len functions.

Wednesday, 9 November 2016

Custom Run Control Page

Creating a Custom Run Control Page

This step is the same regardless of whether you will ultimately use the Run Control page with an SQR or App Engine program.  Do the following in Application Designer:

Creating the Run Control Record

  1. Open Record definition PRCSRUNCNTL and make a copy by using "Save As" and giving the Record a new name (for example, "MY_PRCS_RC").
    • When prompted to save a copy of the PeopleCode associated with PRCSRUNCNTL, choose "Yes".
  2. Update the following Record PeopleCode definitions on your new Run Control Record, replacing references to PRCSRUNCNTL with your custom Run Control Record name:
    • OPRID.RowInit
    • RUN_CNTL_ID.SaveEdit
    • LANGUAGE_CD.RowInit
    • LANGUAGE_OPTION.FieldChange
  3. Add any desired additional fields to your custom Run Control Record (for example, "STRM").
  4. Build the Record.
Creating the Run Control Page
  1. Create a new Page definition and save it with a new name (for example, "MY_PRCS_RC").
  2. Insert a Subpage onto your page, and choose "PRCSRUNCNTL_SBP".
    • On the Insert Subpage dialog, change the "Subpage Record Name Substitution" value to your custom Run Control Record (i.e., "MY_PRCS_RC").
  3. Drag your custom Run Control fields onto the Page (i.e., "STRM").
    • Note:  You do not need to add the default Run Control fields onto the page (OPRID, RUN_CNTL_ID, etc).  These values will be populated automatically through PeopleCode.
Creating the Run Control Component
  1. Create a new Component definition.
  2. Set the Component Search Record to your custom Run Control Record (i.e., "MY_PRCS_RC").
  3. Save the Component with a new name (for example, "My PRCS_RC").
  4. Register your Component using the Registration Wizard (Tools > Register Component).
Creating the Process Definition
In this step, we create a Process definition entry for our SQR or App Engine program, and associate the process definition with our custom Run Control component.
  1. In the PIA, navigate to:  PeopleTools, Process Scheduler, Processes
  2. Click the "Add a New Value" tab and enter the Process Type and Process Name.
    • Note:  The Process Name must exactly match the name of your App Engine program, or in the case of an SQR, must exactly match the file name of your SQR file (without the ".sqr" extension suffix).
  3. On the "Process Definition Options" tab:
    1. Add your custom Run Control Component under the "Component" grid area on the bottom left of the page.
    2. Add whichever Process Groups are appropriate to grant process security to the appropriate persons.
You should now be able to navigate to your Run Control page in the PIA, fill out the Run Control parameters, and schedule your process to run on the Process Scheduler.
Retrieving Run Control Parameters
Now that we have a way to provide Run Control parameters via a Run Control page, we need to be able retrieve and use those parameters from within a program.  This part of the process is different for App Engine and SQR programs.
Run Control Parameters in App Engine Programs
The standard way to store Run Control parameters in an App Engine program is to use a State Record.  To set up the State Record:
  1. A requirement in naming State Records is that they must have "_AET" as their suffix.  Make a copy of your Run Control record, giving it a new name (for example, "MY_PRCS_AET").
    1. When prompted to save a copy of the PeopleCode associated with the original Record, choose "No".
  2. Change the Record Type from "SQL Table" to "Derived/Work".  A Derived/Work Record doesn't persist any data to the database.  Instead, it acts as an in-memory data structure while the program is running on the Process Scheduler.
  3. Open your App Engine program, and navigate to: File, Definition Properties
  4. On the "State Records" tab search for your State Record definition, and click the "Add" button to move the definition from the left-hand panel to the right-hand panel.
Now that the State Record is created and associated with the App Engine program, we need to populate it.
  1. Add an SQL step as the first step in the App Engine program.  To populate the State Record with values from the Run Control Record, we use the Meta-SQL %Select function.  For example:

    WHERE OPRID = %OperatorId
    AND RUN_CNTL_ID = %RunControl
Now that the Run Control parameters have been stored into the State Record, they can be referenced from PeopleCode via the State Record.  For example, the following would write the STRM Run Control parameter value to the message log:
MessageBox(0, "", 0 , 0, "STRM=" | MY_PRCS_AET.STRM);


Tuesday, 8 November 2016

SQLExec function

Use the SQLExec function to execute a SQL command from within a PeopleCode program by passing a SQL command string. The SQL command bypasses the Component Processor and interacts with the database server directly. If you want to delete, insert, or update a single record, use the corresponding PeopleCode record object method.
If you want to delete, insert, or update a series of records, all of the same type, use the CreateSQL or GetSQL functions, then the Execute SQL class method.

Limitation of SQLExec SELECT Statement:
SQLExec can only Select a single row of data. If your SQL statement (or your SQL.sqlname statement) retrieves more than one row of data, SQLExec sends only the first row to its output variables. Any subsequent rows are discarded. This means if you want to fetch only a single row, SQLExec can perform better than the other SQL functions, because only a single row is fetched. If you need to SELECT multiple rows of data, use the CreateSQL or GetSQL functions and the Fetch SQL class method. You can also use ScrollSelect or one of the Select methods on a rowset object to read rows into a (usually hidden) work scroll.

SQLExec statements that result in a database update (specifically, UPDATE, INSERT, and DELETE) can only be issued in the following events:
  • SavePreChange
  • WorkFlow
  • SavePostChange
  • FieldChange 

    SQLExec("Select sum(posted_total_amt) from PS_LEDGER 
    where deptid between :1 and :2", DEPTID_FROM, DEPTID_TO, &SUM);
    :1 is replaced by the value contained in the record field DEPTID_FROM;
    :2 is replaced by the value contained in the record field DEPTID_TO. 
    &SUM will be assigned value of sum(posted_total_amt) selected.

Monday, 31 October 2016

Access level 1 field from level 0

/*Declaring Variables*/
Local Rowset &rowset;
Local Record &rec;
Local Field &field;

&rowset0=getlevel0();  /*Get level 0 rowset*/
&row=&rowset0.getrow(1)/*Get row 0 of level 0 rowset*/
&rowset1=&row0.getrowset(scroll.RECORD_NAME1);  /*Get rowset from row 0 using the scroll name(can also be the name of the primary record in the scroll)*/
&row1=&rowset1.getrow(1);  /*get row1 of level 1 rowset*/
&rec1=&row1.getrecord(record.RECORD_NAME2);  /*get record in the row1*/
&field1=&rec1.FIELD_NAME;  /*finallay access the field in the record*/ 


&rowset1 = getlevel0(1).getrowset(scroll.RECORD_NAME1);
&field = &rowset.getrecord(record.RECORD_NAME).FIELD_NAME;


Thursday, 13 October 2016

Trigger Peoplecode on selection of radio button.

When we are working with radio buttons, there is a problem say associated peoplecode does not trigger when we select radio button. Lets learn how associated peoplecode can be triggered on selecting radio button below.

Create a simple Peoplesoft page having radio buttons.

Now go to page properties and uncheck allow Defferred Processing.

Now you will see that associated peoplecode will trigger when you select a radio button.