DEBUG statement in SQRs

Consider a scenario where you write a code which needs to be executed only when you are trying to debug the program. For e.g. you may want to create a log file in SQR about the rows processed to check the validity of the Program. You may not want this log file to be generated in normal processing. When you want to debug the data you need to be executed.
In such cases SQR provides‘#DEBUG’ commands to perform the action. The Syntax for ‘#DEBUG’ command is:
#DEBUG[x...] SQR_Command

Where ‘x’ represents any letter or digit.

You can write different debug sets in a single SQR program. For E.g. you want only certain part of code to be executed in certain situation. You want some other set of codes need to be executed in other conditions. In such case group the related set of SQR commands with the debug command
Example
In a SQR, Consider the following set of commands. If my SQR errors out and I want to generate the list of Employee Id, Employee Records and Effective Date in log file, I will run the program again with the debug mode of ‘A’. In this case the SQR commands starting with ‘DEBUGA’ will get executed. If I want to trap all the Employee Id, Employee Records and Effective Date in Temp Log table, I will run the program in debug mode of ‘B’. In this case all the errors will be loaded in the temp file and log file will not be generated. If I want to run the SQR program to generate log file and load into Temp table, I can run the program in both mode at same time. #DEBUGA show ‘Emplid :’ $Emplid
#DEBUGA show ‘Empl Rcd :’ $Empl_Rcd
#DEBUGA show ‘Effdt :’ $Effdt



#DEBUGB Begin-Sql

#DEBUGB Insert into PS_Temp_log values (‘Error’,$Emplid,$Empl_Rcd,$Effdt);

#DEBUGB End-Sql



How to run the program I Debug Mode?

In the Processes navigation (‘Home > PeopleTools > Process Scheduler > Processes’) for the specified process, under ‘Override Options’ tab set the Parameter list value as ‘-DEBUG[x]’ with ‘Append’ mode.

Dynamic section calling in App engines

The execution of a PeopleSoft Application Engine starts with the Main section and flows down to other sections which are called from the main section.

For calling a section from within one, we use the call section action. To do this, in the call-section action, we specify the name of the App engine and the section we wish to call. If the section is in the same App engine, then, providing the name of the App engine is optional.

How to use Dynamic Call-Section in App engine?

Often, business logic requires us to call different sections based on occurrence of certain conditions in different scenarios and that too from the same call-section. To enable this kind of logic, we will have to make the call-section dynamic. This is how we can do it.

Dynamic Call-Section State Record

To enable dynamic call-section, we need to have a state record that can support it. The state record, in this case, should have two extra fields – AE_APPLID and AE_SECTION. If you intent to make a dynamic call to sections that are in the same App engine as the calling section, then the state record would be good to go with just AE_SECTION.

PeopleCode for Dynamic Call-Section

Once you have the state record in place, it’s time to set the values for the extra fields – AE_APPLID and AE_SECTION. This should be done before the dynamic call section. The simplest way to implement this is to use a if-else as shown below:
if condition then
AE_APPLID = "AE_ABC_TEST";
AE_SECTION = "SEC_STATE";
else
AE_APPLID = "AE_ABC_TEST";
AE_SECTION = "SEC_CITY";
end-if;

Call-Section Action

This is the final step. Insert a Call-section action into the App engine. Check the Dynamic check-box that states that the call-section is dynamic. On finding the dynamic check-box checked, the processor looks for the values of AE_APPLID and AE_SECTION in the state record and calls the section mentioned in AE_SECTION from the app engine that is mentioned in the AE_APPLID.

If the AE_APPLID is blank, the processor calls the section mentioned in AE_SECTION from the current application engine.

Multiple Reports in SQR

Generating multiple reports in SQR is common these days. Writing SQRs that produce multiple reports have many advantages over the other approach of having multiple SQRs do this job. Here are some of them.

Advantages

Multiple reports in one SQR approach reduces database trips thereby making the reports faster. This is especially true when all the reports are based on the same set of data. However, be cautious not to get totally different SQR reports into one – this can complicate things.
Easily possible to direct multiple reports to multiple printers. Since we are free to have different layouts and printers for different reports, we can easily direct some reports to one printer while some other reports to a different printer.
This results in fewer SQRs which in turn reduces maintenance costs. Well, this one needs no further explanation.
Having seen the advantages, you would be curious to see how we can generate multiple reports in SQRs. This can essentially be achieved in a three step approach.

Declare-Report
When generating multiple reports, SQR mandates us to declare all the reports that we wish to generate. This is done within the Setup section of the SQR. We can use different printers / layouts for different reports. If you are happy with the default printer and layout, just ignore these in your report definition.

In the sample code below, we have declared two reports, both of which use the default layout and printer for simplicity sake.

Begin-Setup

declare-report TEST1
end-declare

declare-report TEST2
end-declare

End-Setup

For-Reports
Standard SQRs that generate just one report would have only one Heading / Footing. However, we need to have a mechanism to print different headings / footings on different reports. This can be achieved using the For-Reports parameter. This is how we use it.

Begin-Heading 1 for-reports=(TEST1)
print ’Test Report One’ (1) center
End-Heading

Begin-Footing 1 for-reports=(TEST1)
page-number (1,1) ’Page ’
last-page () ’ of ’
End-Footing

Begin-Heading 1 for-reports=(TEST2)
print ’Test Report Two’ (1) center
End-Heading

Begin-Footing 1 for-reports=(TEST2)
page-number (1,1) ’Page ’
last-page () ’ of ’
End-Footing

Use-Report
We have reached the final stage – printing the actual report. Before we can print anything, we would need to inform the processor about the report to which we are printing. We use the Use-Report command to set the printing context. This is how we do it.

Begin-Program

use-report TEST1
print 'This text goes into report TEST1' (,1)

use-report TEST2
print 'This text goes into report TEST2' (,1)

End-Program

Load Look up In SQR

It’s common to join tables within SQRs to retrieve data from normalized tables. As SQL statements consume significant computing resources, such joins may be a hindrance to performance of the SQR. Further, as the number of tables that are used in the join increases, the performance decreases.

This rational makes us look for ways to reduce the number of tables used in the join as a means to tune the SQR. This is when Load-Lookup in SQR comes into picture. Using Load-Lookup is a two step process – here’s how to make use of it in your SQR programs.

Load-Lookup
You start by loading the Load-Lookup. This can either be done within the setup section or within a procedure. While done within the setup section, it is only executed once. When within procedures, the execution happens each time the code is encountered.

The code snippet shows how this is used within the setup section. On execution of the below Load-Lookup, SQR creates an array containing a set of return values against keys.

Begin-Setup
Load-Lookup
Name = Product_Names
Table = PRODUCTS
Key = PRODUCT_CODE
Return_value = DESCRIPTION
End-Setup

Lookup
Once we have the first step in place, it’s time to utilize the lookup. The below code will essentially look up for a key (PRODUCT_CODE) in the array and return the return value (DESCRIPTION).

Begin-Select
ORDER_NUM (+1,1)
PRODUCT_CODE
Lookup Product_Names &PRODUCT_CODE $DESC
print $DESC (,15)
from ORDERLINES
End-Select

Multiple Keys / Return_values
Although Load-Lookup doesn’t support multiple keys or return_values, we can do this by concatenating the values using database specific concatenation operators. So if you are on Oracle DB, this would be how you can do it. The return values can later be separated using the unstring command.

Load-Lookup
Name = Product_Names
Table = PRODUCTS1
Key = 'PRODUCT_CODE||','||KEY2'
Return_value = 'DESCRIPTION||','||COLUMN2'

Using where clause in Load-Lookup
To limit the values that are populated in the Load-Lookup array, we can use a where clause as shown below.

Load-Lookup
Name = Product_Names
Table = PRODUCTS
Key = PRODUCT_CODE
Return_value = DESCRIPTION
Where = PRODUCT_CODE > 1000

calling unix scripts from Unix

It often requires us to invoke OS commands from within an SQR. Today we will see how to use the call system command from within the SQR to invoke a UNIX script.

Call System Command
SQR provides the Call System command to issue commands to the underlying OS. The OS then returns a status indicating if the execution of the command that was issued was successful or not. This is how the Call System command is used.

Call System using $cmd_string #status

In the above statement, $cmd_string is contains the command that would be issued to the OS. It’s up to you to decide what needs to be written in the script, the choices are unlimited! The status that the OS returns will be received in #status. On successful execution, the status returned would be 0. This can be used to confirm if the command issued was successful or not.

Another way of doing this would be to hard code the value of the command directly within the Call System command. This would be less flexible though.

Call System using 'rm abcd.lis' #status

Standalone rowsets

When any page in a component is opened, the system retrieves all of the data records for the entire component and stores them in one set of record buffers called the component buffer which is organized by scroll level and then by page level. However, if we need to access data in records that are outside of the component buffer, we need to use Standalone Rowsets. This post will take you through the steps involved in creating and manipulating data using standalone rowsets.

So what are Standalone Rowsets?
It’s a rowset that is outside of the component buffer and not related to the component presently being processed. Since it lies outside the data buffer, we will have to write PeopleCode to perform data manipulations like insert / update / delete.

Creating Standalone Rowset
We use the below PeopleCode to create a standalone rowset. With this step, the rowset is just created with similar structure to that of the record SAMPLE_RECORD. However, the rowset would be empty at this point..

Local Rowset &rsSAlone;
&rsSAlone = CreateRowset(Record.SAMPLE_RECORD);

Populating a Standalone Rowset
Now that we have created a standalone rowset, we need to populate it with date that we need to work on. We can use the below methods to populate data into a standalone rowset.

Fill Method
The simplest way to populate data into a standalone rowset is to use the Fill method. What the below code essentially does is, populate the standalone rowset with all rows from the SAMPLE_RECORD where the TRAINING_ID = ’12345'.

&TRG_ID = '12345';
&rsSAlone.Fill("where TRAINING_ID = :1", &TRG_ID);

CopyTo Method
Another way to populate a standalone rowset it by using the CopyTo method. This method copies like-named fields from a source rowset to a destination rowset. To perform the copy, it uses like-named records for matching, unless specified. The below code copies the content of the above rowset &rsSAlone into &rsSAlone2.

Local Rowset &rsSAlone2;
&rsSAlone2 = CreateRowset(Record.SAMPLE_RECORD);
&rsSAlone.CopyTo(&rsSAlone2);

In case if we had NOT created both the above rowsets from the same record, we would have mentioned the complete record names in the fill method as shown below. The below code would copy the contents of similar fields in &rsSAlone into those in &rsSAlone2.

&rsSAlone.CopyTo(rsSAlone2, RECORD.SAMPLE_RECORD, RECORD.SAMPLE_RECORD_2);

Child Rowsets
We have seen a standalone rowset being created usign a single record. However, we can also create one using another rowset. This would be handy to setup parent-child relations. This is how this can be achieved.

Local Rowset &Lvl1, &Lvl2, &Lvl3;
&Lvl3 = CreateRowset(Record.SAMPLE_LVL3_REC);
&Lvl2 = CreateRowset(Record.SAMPLE_LVL2_REC, &Lvl3);
&Lvl1 = CreateRowset(Record.SAMPLE_LVL1_REC, &Lvl2);

The above can also be written as shown below.

Local Rowset &Lvl1;
&Lvl1 = CreateRowset(Record.SAMPLE_LVL1_REC,
CreateRowset(Record.SAMPLE_LVL2_REC,
CreateRowset(Record.SAMPLE_LVL3_REC)));

Related Language Records

As we all know, PeopleSoft is capable of maintaining application data in multiple languages within the same database. This feature is driven by special records called Related Language Records that store language sensitive information in all required languages other than the base language of the system.

Structure of a Related Language Record
Data in Related Language Record has a one-to-one relation with the data in the base record to which it is tagged. So it’s natural that it will have all the key fields that the base record have. Further, it will also have LANGUAGE_CD as a key field.

Creating a Related Language Record
The easiest way to setup a Related Language Record for your base record would be to follow the below steps.

Clone the base record and and save it as <base_record_name>_LANG
Include LANGUAGE_CD as a key
Remove all non-key, non-language-sensitive fields
Associate it with the base record
Associating Related Language Record to Base Record
To associate the Related Language Record to your base record, open the base record in Application Designer and open the record properties. On the use tab, within the Related Language Record field, enter the name of the Related Language Record that you have just created. Save the record!

How Related Language Records Work
Say you have a search record that has a Related Language Record associated with it. When you login to the system in the base language and try searching, the text is retrieved from the base record. However, when you are logged in to the system in a language other than the base language, the system checks for a translation in the related language record. If a translation is found, it is displayed; otherwise, it displays the text from the base record. This logic enables us to selectively translate portions of data in the system while keeping the system functional at all times even if all rows are not translated.

By default, the base language for all PeopleSoft systems is English. However, an admin can change the base language to the desired one via SWAP_BASE_LANGUAGE Data Mover script.