One of the requirements I have lately is to compare table data between two different environments (Development and Test).
The Oracle SQL below compares table1 that has 2 key fields and 3 regular fields.

Note: For the SQL below to work, your password needs to be the same in both environments. If not, then
a connection using the below will not be possible.

Here is the SQL to compare the differences in the data in different tables with same structure. Will be helpful for comparing the data between databases. This works in MS SQL Server if both databases are on same server or if there is a linked server configured for the other database.

Here is a quick example to help you understand how oracle Start With analytical function works. The start with .. connect by clause can be used to select data that has a hierarchical relationship. Usually, it is some sort of parent child relationship like supervisor and an employee.

Let’s assume we have a table called employees. I would like to know employees that directly report to supervisor Id 1122456 –simple, right?

A grid label is the blue (normally) colored grid header that you see on PeopleSoft pages. The grid text label can be easily edited using the Grid Properties in app designer. Once you have the grid properties box open, click the Label tab > Properties and there you can see the Label Text area. You can choose between a Static Text type or Message Catalog. I would almost always go with Message Catalog type as this could be easily updated online.

Lets start with the basics of application development in PeopleSoft. Well, I hope you all know that Application Designer is the tool which we used to develop PeopleSoft application and we usually call it as App Designer in short. Basically there are 8 steps in PeopleSoft Application Development.

Here are the 8 steps of PeopleSoft Application Development:

1. Design the application
2. Define new fields
3. Create record definitions
4. Build the records
5. Create page definition
6. Define the component
7. Registering the component
8. Test the application

PSOPRDEFN - UserIds created.
PSCLASSDEFN - Permission List definition.
PSROLEDEFN - Role Definition table
PSAUTHITEM - Menus, Components and Pages attached to a permission list.
PSROLEUSER - Contains user and role mapping.
PSROLECLASS - Role Permission List mapping.

PSAUTHBUSCOMP -- Component Interfaces Permissions
PSAUTHCHNLMON - Message Channels tagged to any particular PLs
PSAUTHMP - Mobile Pages tagged to a particular Permission List

PSUSERATTR
PSUSEREMAIL
PSOPRALIAS
PSOPRALIASTYPE
PSOPRALIASFIELD
PSROLECANGRANT
PSAUTHCUBE
PSAUTHOPTN
PSAUTHPRCS
PSAUTHSIGNON

User Id

The User Id is the ID associated with the individual user logging onto the PeopleSoft application, via PeopleTools.Validation of this ID is performed by the PeopleTools themselves at log-in time, by virtue of verifying that a row has been defined in the PSOPRDEFN table for this ID. The row in the PSOPERDEFN table contains information pertinant to PeopleSoft internal security administration, such as the access profile defining what areas of the application this ID will have access to, and a link to the ACCESS ID (defined later) associated with the UserId.

Here is the SQL that you can use to fetch the number of rows in the PS tables in MS SQL Server database.

We can bypass the component search page in different ways.
1. One way is to set the key values using peoplecode and use the peoplecode function SetSearchDialogBehavior. You can write the code in the SearchInit PeopleCode. Suppose Employee Id is the only key, then the code will look like,

EMPLID = %EmployeeId;
SetSearchDialogBehavior(0);

2. There is another method of using a record with no keys as the component search record. This will also bypass the search.

Based on the requirement you can use any of the above methods.

Here is the piece of code you can use to send email with attachments in SQR.

BEGIN-PROCEDURE sendmail
let $subject = 'Subject of the Email Here'
let $to_list = 'email@email.com'
let $ReportID = 'G:\PeopleSoft\Reports\Report_Name.pdf'
let $alias ='Attachment File Name Alias including the file extension'
let $enter = chr(10)chr(13)