PeopleSoft World

Page Start to Display in ADD mode (or) component in ADD mode

2010-05-19T02:53:00.000-07:00

When we click on the component portal in menu then (the page with ADD button display) Then the following below PeopleCode Events will fire.

Search Processibg in Add Mode:
Record Field          FieldDefault
ComponentRecordField   FieldDefault
Record Field                    FieldFormula
Record Field          RowInit
Record Field          SearchInit
ComponentRecordlevel SearchInit
-->Enter the Employee and click on Add
Record Field            FeildEdit
ComponentRecordField FeildEdit
Record Field                      FieldChange
ComponentRecordField FieldChange
Record Field                      Field Formula
Record Field                      SaveEdit
Record Field                      SearchSave
ComponentRecordlevel      SearchSave

Page Dispaly (or)Component Build Process in ADD Mode:
Component Level            Prebuild
Record Field                         FieldFormula
Record Field         RowInit
ComponentRecord          RowInit
Component Level                  PostBuild
Page Activate fires and displays the page

Save Processing:
Record Field    SaveEdit
ComponentRecord           SaveEdit
Record Field          SavePrechange
ComponentRecord          SavePrechange
ComponentLevel             SavePrechange
Record Field                  Workflow
ComponentLevel            Workflow
Record Field            SavePostchange
ComponentRecordlevel SavePostchange
Component Level           SavePostchange

Search Process of a component in Update mode (UpdateDisplay/UpdateDisplayall) and Component Build Processing or Page build process in Update/Update Displayallmode

2010-05-19T02:45:00.000-07:00

When we click on the component portal in menu then the following below PeopleCode events will fire

Search Process
Record Field     SearchInit
ComponentRecord SearchInit

-->Click on Search
Record Field    SearchSave
ComponentRecord SearchSave

Component Build Process or Page Build processing in Updatedisplay/UpdateDisplayall mode
-->Select the Employee ID from the list then
Record Field                      RowSelect
ComponentRecord            RowSelect
Component Level    Prebuild
Record Field                     FieldFormula
Record Field                     RowInit
ComponentRecord            RowInit
Component level Postbuild
Page Activate fires and displays the page

When we edit the field (Employee ID) then the following below PeopleCode Events will fire
-->Edit the field (Employee ID) and hit on save then the following peoplecode events will fire
Record Field          FieldEdit
ComponentRecordField    FieldEdit
Record Field         FieldChange
ComponentRecordField      FieldChange
Record Field                           FieldFormula

Save Processing PeopleCode Events:
Record Field       SaveEdit
ComponentRecord                  SaveEdit
Record Field             SavePrechange
ComponentRecordField      SavePrechange
ComponentLevel                    SavePrechange
Record Field                          Workflow
ComponentLevel                    Workflow
Record Field        SavePostchange
ComponentRecordlevel          SavePostchange
Component Level                   SavePostchange

Inserting the data into 3 levels (0,1,2,3 levels) into database from flat file using Application Engine

2010-05-03T05:03:00.000-07:00

Inserting the data into 3 levels (0,1,2,3 levels) into database from flat file using Application Engine Below is the sample code (Ive used 2 fields in all the 3 levels)

Level0 -> Organisational ID, Organisational Name
Level1 -> College ID, College Name
Level2 -> Department ID, Department Name
Level3 -> Employee ID, Employee Name, Age

Local Record &R0, &R1, &R2, &R3;

&R0 = CreateRecord(Record.A_ORG_TBL);
&R1 = CreateRecord(Record.A_COL_TBL);
&R2 = CreateRecord(Record.A_DEPT_TBL);
&R3 = CreateRecord(Record.A_EMPL_TBL);

&myfile = GetFile("C:\temp\harsha.txt", "R", %FilePath_Absolute);
&myfile1 = GetFile("C:\temp\har.txt", "W", "A", %FilePath_Absolute);
&myfile1.writeline("empty array creation");
&myarray = CreateArrayRept("", 0);

If &myfile.Isopen Then
   While &myfile.Readline(&str);
      &myfile1.WRITELINE("FILE OPENED");
      &myfile1.WRITELINE("string value is " | &str);
      &myarray = Split(&str, ",");

      SQLExec("SELECT A_ORGID FROM PS_A_ORG_TBL WHERE A_ORGID = :1", &myarray [1], &ORGID);
      &myfile1.WRITELINE("array value of orgid is " | &ORGID);
      &myfile1.WRITELINE(" A_ORGID VALUE IS " | &ORGID);


      SQLExec("SELECT A_ORGID,A_COLID FROM PS_A_COL_TBL WHERE A_ORGID = :1", &myarray [1], &ORGID1, &COLID1);
      SQLExec("SELECT A_ORGID,A_COLID,A_DEPTID FROM PS_A_DEPT_TBL WHERE A_ORGID = :1", &myarray [1], &ORGID2, &COLID2, &DEPTID);
      SQLExec("SELECT A_ORGID,A_COLID,A_DEPTID FROM PS_A_DEPT_TBL WHERE A_ORGID = :1", &myarray [1], &ORGID2, &COLID2, &DEPTID);
      SQLExec("SELECT A_ORGID,A_COLID,A_DEPTID,A_EMPLID FROM PS_A_EMPL_TBL WHERE A_ORGID = :1", &myarray [1], &ORGID3, &COLID3, &DEPTID3, &EID3);

      If &myarray [1] = &ORGID Then
         &R1.A_ORGID.Value = &myarray [1];
         &R1.A_COLID.Value = &myarray [2];
         &R1.A_COLNAME.Value = &myarray [3];
         &R1.Insert();
         &myfile1.WRITELINE("values inserted at level 1 ");
         rem End-If;
         rem SQLExec("SELECT A_ORGID,A_COLID,A_DEPTID FROM PS_A_DEPT_TBL WHERE A_ORGID = :1", &myarray [1], &ORGID2, &COLID2, &DEPTID);
      Else
         &R0.A_ORGID.Value = &myarray [1];
         &R0.A_ORGNAME.Value = &myarray [2];
         &R0.Insert();
         &myfile1.WRITELINE("values inserted at level 0 ");
      End-If;

      If &myarray [1] = &ORGID1 And
            &myarray [2] = &COLID1 Then

         &R2.A_ORGID.Value = &myarray [1];
         &R2.A_COLID.Value = &myarray [2];
         &R2.A_DEPTID.Value = &myarray [3];
         &R2.A_DEPTNAME.Value = &myarray [4];
         &R2.Insert();
         &myfile1.WRITELINE("values inserted at level 2 ");


      End-If;
      If &myarray [1] = &ORGID2 And
            &myarray [2] = &COLID2 And
            &myarray [3] = &DEPTID Then
         &R3.A_ORGID.Value = &myarray [1];
         &R3.A_COLID.Value = &myarray [2];
         &R3.A_DEPTID.Value = &myarray [3];
         &R3.A_EMPLID.Value = &myarray [4];
         &R3.A_EMPNAME.Value = &myarray [5];
         &R3.A_EMPLAGE.Value = &myarray [6];
         &R3.Insert();
         &myfile1.WRITELINE("values inserted at level 3 ");

      End-If;


   End-While;

   &myfile.Close();
   &myfile1.Close();
End-If;

Steps for creating a GOTO ROW

2010-01-06T01:21:00.000-08:00

STEPS FOR CREATING GOTO ROW
===========================
1.Create a new Field (TIN1_GOTOROW)
2.Create a new record (TIN1_GOWTO_WRK) and select the radio button Derived/Work.
Write the following below people code in the Field change in the Field level in TIN1_GOWTO_WRK table.
==============================================================================
&tinku = DoModal(Page.TIN1_GTROW_PNL, "Secondary Page ", - 1, - 1);

If (&tinku = 1) Then
&rowentered = GetRecord(Record.TIN1_GOWTO_WRK).GetField(Field.TIN1_GOTOROW).Value;
rem WinMessage("hai", 64);

If All(&rowentered) Then
¤trow = GetRowset();

If (1 <= &rowentered And
&rowentered <= ¤trow.activerowcount) Then
REM IN THE BAOVE THE HIGHEST VALUE WILL BE PERSENT;
&reqrow = GetRowset()(&rowentered);
&field = &reqrow.getrecord(1).getfield(1);
&field.SetCursorPos(%Page);
Else
WinMessage("THE ROW IS WITH IN THE TABLE ", 64);

End-If;

End-If;
End-If;

=============================================================================
3.Create a new secondary page (TIN1_GTROW_PNL)
4.Drag and drop the WRK record in to the page.
5.Save it.

Application Data Security

2010-01-05T22:17:00.000-08:00

Defnition security is a form of data security.We use it to control access to particular rows of data (object definitions) in PeopleTools tables.

PeopleSoft also provides other methods to control the application data that a user is allowed to access in the PeopleSoft system. This task is also known as setting data permissions.With application data security, you can set data permissions at the following levels:

1.Table Level(Querry Security)
2.Row Level (SQL View)
3.Feild Level (People Code)

Table Level Security:
Table level security is implemented by using Querry security.
We can restrict the permissions to the users in accessing the tables while running and building the componenets

We can do this by creating querry acess group in PeopleSoft Tree manager and then assinning the users to those groups with PeopleSoft querry security.

PeopleSoft Query security is enforced only when usingPeopleSoft Query because it doesn’t control runtime page access to table data.

Row Level Security:
It can be implementd using SQL view's.
It controls the access to individual rows of data stored within application database tables
It enables the data of a particular user is permitted to access.

Field security:
Field level can be implemented using People Code.
Field security use people code to restrict access to particular fields or columns within application tables.

domodalcomponent steps

2010-01-05T21:44:00.000-08:00

Drag and drop the 2 fields STUID,NAME from the work record in all the pages

1.Create the required fields and place them in to the record PS_SRI_STUMOD_TBL(*place the Student id and name in to the record*)
2.Create a Derived/Work record with 2 fields (stuid,name from grid) and save it with _WRK.
3.Place a hyperlink/ push button in the grid.In the hyperlink /pushbutton properties select the Destination as PeoplecodeCommand,record name:SRI_STU1_TBL and field name:SRI_STUABC_INFO and select the check button set component changed.

4.IN component level record field level assign the values to the Derived/Work record.
Local Rowset &RS0, &RS1, &RS2, &RS3;
&RS0 = GetLevel0();
&RS1 = &RS0(1).GetRowset(Scroll.SRI_ORG1_TBL);
For &I = 1 To &RS1.ActiveRowCount
&RS2 = &RS1(&I).GetRowset(Scroll.SRI_DEPT1_TBL);
For &J = 1 To &RS2.ActiveRowCount
&RS3 = &RS2(&J).GetRowset(Scroll.SRI_STU1_TBL);
For &K = 1 To &RS3.ActiveRowCount;
&SID = GetRecord(Record.SRI_STU1_TBL).GetField(Field.SRI_STU1_ID).Value;
&SNAME = GetRecord(Record.SRI_STU1_TBL).GetField(Field.SRI_STU1_NAME).Value;
SRI_STUAB_WRK.SRI_STU1_ID.Value = &SID;
SRI_STUAB_WRK.SRI_STU1_NAME.Value = &SNAME;
End-For;
End-For;
End-For;
DoModalComponent(MenuName.SRI_UNV_MNU, BarName.SRI_UNV_MNU, ItemName.SRI_STUABS_CMP, Page.SRI_STUABS_PNL, "A", Record.SRI_STUAB_WRK);
5.Call the other page /component using the DoModalComponent function.
DoModalComponent(MenuName.SRI_UNV_MNU, BarName.SRI_UNV_MNU, ItemName.SRI_STUABS_CMP, Page.SRI_STUABS_PNL, "A", Record.SRI_STUAB_WRK);
6.Create a View for the STUDENTID and NAME (SRI_STUAB_VW)
7.For STUDENTID in the table PS_SRI_STUMOD_TBL give the view (this makes the scroll change when we select different student id's)

Set ID and Business Unit

2010-01-05T21:30:00.000-08:00

SetID
An identification code that represents a set of control table information or table sets. Set Ids enable the sharing of a set of control table information across two or more Business Units.

Table sets enable you to share control table information and processing options among business units. The goal is to minimize redundant data and system maintenance tasks. When you assign a setID to a record group in a business unit, you indicate that all of the tables in the record group are shared between that business unit and any other business unit that also assigns that setID to
that record group. For example, you can define a group of common job codes that are shared between several business units. Each business unit that shares the job codes is assigned the same setID for that record group."

Business Unit
An identification code that represents a high-level organization of business information. You can use a business unit to define regional or departmental units within a larger organization.

Business Unit organizes your company or your organization, SetIDs help you organize your data within the system. The HRMS system uses tables (Control Tables or Prompt Tables) that use a high-level key that enables you to identify and retrieve data from the system. A secondary high-level key, referred to as a SetID, has also been added on various tables. SetIDs are simply the labels used to identify a TableSet. Business Unit and SetID functionality in PeopleSoft HRMS also provides you with a higher business level for reporting purposes and other business data roll-up.

Difference between Transcation Tables and Control Tables

2010-01-05T21:09:00.000-08:00

Transaction tables store data about day-to-day activities. Because of this, these tables are updated frequently and can be quite large. The type of transaction varies, such as invoices, paychecks, employee names and addresses, job history, benefits data.

Information in transaction tables is organized and stored by Business Unit.

Control tables store information that define the accounting structure and processing rules that are used when transactions are entered into your PeopleSoft applications. Control tables include master lists such as customers, vendors, products, country and location tables. It is important to note that Control tables are static, meaning they only change when you perform specific maintenance on them.

Control table information is organized and stored by a set identifier, commonly called a SetID.

Stand alone RowSet

2010-01-05T21:03:00.000-08:00

STAND ALONE ROWSET
In PeopleCode a standalone rowset is an independent rowset object not associated with the component buffer. They allow you to work with data outside of the buffer by getting whatever additional data you need form the database. In this sense they replace the functionality of derived records which were once used as place holders to store data not directly associated with the component. Because a standalone rowset is standalone, there is no automatic action by the component processor on it. This means that if a standalone rowset is used to manipulate data (inserts/updates), code will need to be added to manually save the changes.

Code to create a standalone rowset object
Local Rowset &rsExample;
&rsExample = CreateRowset(Record.REC1);

Filling a standalone rowset
The Fill method in the Rowset class is used to populate the rowset. This parameters to the fill method are a Where clause and bind values. &rExample.Fill("where FIELD1 = :1", REC2.FIELD2);

The following example writes a file using a file layout that contains parent-child records:
Local File &MYFILE;
Local Rowset &rsBusExp, &rsBusExpPer, &rsBusExpDtl;
Local Record &rBusExp, &rBusExpPer, &rBusExpDtl;
Local SQL &SQL1, &SQL2, &SQL3;
&rBusExp = CreateRecord(Record.PERSONAL_DATA);
&rBusExpPer = CreateRecord(Record.BUS_EXPENSE_PER);
&rBusExpDtl = CreateRecord(Record.BUS_EXPENSE_DTL);

&rsBusExp = CreateRowset(Record.PERSONAL_DATA,
CreateRowset(Record.BUS_EXPENSE_PER,
CreateRowset(Record.BUS_EXPENSE_DTL)));
&rsBusExpPer = &rsBusExp.GetRow(1).GetRowset(1);

&MYFILE = GetFile("c:\temp\BUS_EXP.out", "W", %FilePath_Absolute);
&MYFILE.SetFileLayout(FileLayout.BUS_EXP_OUT);
&EMPLID = "8001";

&SQL1 = CreateSQL("%selectall(:1) where EMPLID = :2", &rBusExp, &EMPLID);
&SQL2 = CreateSQL("%selectall(:1) where EMPLID = :2", &rBusExpPer, &EMPLID);

While &SQL1.Fetch(&rBusExp)
&rBusExp.CopyFieldsTo(&rsBusExp.GetRow(1).PERSONAL_DATA);
&I = 1;
While &SQL2.Fetch(&rBusExpPer)
&rBusExpPer.CopyFieldsTo(&rsBusExpPer(&I).BUS_EXPENSE_PER);
&J = 1;
&SQL3 = CreateSQL("%selectall(:1) where EMPLID = :2
and EXPENSE_PERIOD_DT = :3", &rBusExpDtl, &EMPLID,
&rsBusExpPer(&I).BUS_EXPENSE_PER.EXPENSE_PERIOD_DT.Value);
&rsBusExpDtl = &rsBusExpPer.GetRow(&I).GetRowset(1);
While &SQL3.Fetch(&rBusExpDtl)
&rBusExpDtl.CopyFieldsTo(&rsBusExpDtl(&J).BUS_EXPENSE_DTL);
&rsBusExpDtl.InsertRow(&J);
&J = &J + 1;
End-While;

&rsBusExpPer.InsertRow(&I);
&I = &I + 1;
End-While;
&MYFILE.WriteRowset(&rsBusExp);
End-While;
&MYFILE.Close();

Out Put for the above program
CC8001 03/01/199802/15/1998011200 USDConference 00001
CC8001 03/01/199802/16/19980220000 JPYConference 00001

The following code shows an example of reading in a file and inserting the rows into the database:
Local File &MYFILE;
Local Rowset &rsBusExp, &rsBusExpPer, &rsBusExpDtl;
Local Record &rBusExp, &rBusExpPer, &rBusExpDtl;
Local SQL &SQL1;

&rBusExp = CreateRecord(Record.PERSONAL_DATA);
&rBusExpPer = CreateRecord(Record.BUS_EXPENSE_PER);
&rBusExpDtl = CreateRecord(Record.BUS_EXPENSE_DTL);

&rsBusExp = CreateRowset(Record.PERSONAL_DATA,
CreateRowset(Record.BUS_EXPENSE_PER,
CreateRowset(Record.BUS_EXPENSE_DTL)));

&MYFILE = GetFile("c:\temp\BUS_EXP.out", "R", %FilePath_Absolute);
&MYFILE.SetFileLayout(FileLayout.BUS_EXP_OUT);

&SQL1 = CreateSQL("%Insert(:1)");

&rsBusExp = &MYFILE.ReadRowset();
While &rsBusExp <> Null;
&rsBusExp.GetRow(1).PERSONAL_DATA.CopyFieldsTo(&rBusExp);
&rsBusExpPer = &rsBusExp.GetRow(1).GetRowset(1);
For &I = 1 To &rsBusExpPer.ActiveRowCount
&rsBusExpPer(&I).BUS_EXPENSE_PER.CopyFieldsTo(&rBusExpPer);
&rBusExpPer.ExecuteEdits(%Edit_Required);
If &rBusExpPer.IsEditError Then
For &K = 1 To &rBusExpPer.FieldCount
&MYFIELD = &rBusExpPer.GetField(&K);
If &MYFIELD.EditError Then
&MSGNUM = &MYFIELD.MessageNumber;
&MSGSET = &MYFIELD.MessageSetNumber;
End-If;
End-For;
Else
&SQL1.Execute(&rBusExpPer);
&rsBusExpDtl = &rsBusExpPer.GetRow(&I).GetRowset(1);
For &J = 1 To &rsBusExpDtl.ActiveRowCount
&rsBusExpDtl(&J).BUS_EXPENSE_DTL.CopyFieldsTo(&rBusExpDtl);
&rBusExpDtl.ExecuteEdits(%Edit_Required);
If &rBusExpDtl.IsEditError Then
For &K = 1 To &rBusExpDtl.FieldCount
&MYFIELD = &rBusExpDtl.GetField(&K);
If &MYFIELD.EditError Then
&MSGNUM = &MYFIELD.MessageNumber;
&MSGSET = &MYFIELD.MessageSetNumber;
End-If;
End-For;
Else
&SQL1.Execute(&rBusExpDtl);
End-If;
End-For;
End-If;
End-For;
&rsBusExp = &MYFILE.ReadRowset();
End-While;
&MYFILE.Close();

Basic HRMS Setup's and adding the Benefits to the Employee Setup

2010-01-05T20:55:00.000-08:00

Create SETID
Navigation:
Main Menu ->PeopleTools ->Utilities ->Administration ->TablesetID's

Create Business Unit
Navigation:
Main Menu ->Setup HRMS ->Foundation Tables ->Organisation ->Business Unit

Setup Location:
Navigation:
Main Menu ->Setup HRMS ->Foundation Tables ->Organisation ->Location

Add a company
Navigation:
Main Menu ->Setup HRMS ->Foundation Tables ->Organisation ->Company

Create an Establishment ID
Navigation:
Main Menu ->Setup HRMS ->Foundation Tables ->Organisation ->Establishment

Create Departments
Navigation:
Main Menu ->Setup HRMS ->Foundation Tables ->Organisation ->Departments

Setting the Business Unit options default
Navigation:
Main Menu ->Setup HRMS ->Foundation Tables ->Organisation ->Business Unit options default (Enter the company and Country details)

Create a pay Group Table:
Navigation:
Main Menu ->Setup HRMS ->Product Related ->Pay Roll ->Pay Group Table

Create a Job Code
Navigation:
Main Menu ->Setup HRMS ->Foundation Tables ->Job Attributes ->Job Code Table

Setting Org Defaults permission lists
Navigation:
Main Menu ->Setup HRMS ->Foundation Tables ->Organisation ->Org Defaults by permission Lists (Enter the Organisation related
data here)

Tree Manager:
Navigation:
Main Menu ->Tree Manager->Tree Utilites ->Tree Manager
->Create a new tree with the name DEPT_SECURITY
->Select the structure ID as DEPARTMENT
->Select the category as HR
->Click on ADD LEVEL and add the following below levels
1.Corporate
2.Business
3.Region
4.Company
5.Department
6.Division
7.Group
8.Unit
->Select the root node
->save it.
Note: In Business Options Default enter the details.

Security:
Navigation:
Main Menu ->Security ->Core Row Level Security ->Security by Permission Lists
->Select HCDPALL.Add ur business uint it and select the 011 as Security acess type.

Security by Dept Tree:
Navigation:
Setup HRMS ->Security ->Core Row level Security ->Security by Dept Tree
->Select HCDPALL.Add your SETID and Department ID in it.

Running the Process:
Navigation:
Setup HRMS ->Security ->Core Row Level Security->Refresh SJT Class ALL / Refresh Trans SJT Tables / Refresh SJT OPR class

Run all the above process

Buzz the Application
Navigation:
Setup HRMS ->Install -> Installation Table
->Change the SETID and Campany Name.
->Restart the services.

Adding a Person:
Navigation:
Main Menu ->Workforce Administration -> Personal Information ->Add a Person.

Adding the Benfits to the existing Employee:
===================================
Base Benfits:
Adding a Health Plan to an Existing Employee:

Provider / Vendor Tables:
Navigation:
Main Menu ->Setup HRMS ->Product Related ->Base Benfits ->Plans and Providers ->Provider/ Vendor Tables ->Vendor ID

Benfit Plan Type:
Navigation:
Main Menu ->Setup HRMS ->Product Related ->Base Benfits ->Plans and Providers ->Provider/ Vendor Tables ->Benfit Plan Type

Health Plan Table:
Navigation:
Main Menu ->Setup HRMS ->Product Related ->Plan Attributes ->Health Plan Table

Health Coverage Codes:
Navigation:
Main Menu ->Setup HRMS ->Product Related ->Plan Attributes ->Health Coverage Codes

Rates and Rules:
Navigation:
Main Menu ->Setup HRMS ->Product Related ->Base Benfits ->Rates and Rules ->Benfit Rates.

Deduction Code:
Navigation:
Main Menu ->Setup HRMS ->Product Related ->Payroll Interface ->Deductions ->Deduction Tables.

Cloning the Benfit Program Utility:
Navigation:
Main Menu ->Setup HRMS ->Product Related ->Base Benfits ->Program Structure ->Benfit Program clone Utility.

->Select required Basic benfits and clone it.

Benfit Program Table:
Navigation:
Main Menu ->Setup HRMS ->Product Related ->Base Benfits ->Program Structure ->Benfit Program Table.

Adding the Benfit to the Employee:
Navigation:
Main Menu ->Workforce Administration ->Job Data ->Select the EMPLID ->Click on the Benfit program participation link ->Add the benfit program.

Check it in Benfits
Navigation:
Main Menu ->Benfits ->Enroll in Benfits ->Assign to Benefit Program ->Select the EMPLID and check for Benfits.

PeopleSoft Finance Tables with Detail Description XLS sheet

2009-12-23T01:21:00.000-08:00

Hi Please find the PeopleSoft Finance Tables with Detail Description XLS sheet in the below link

http://www.ziddu.com/download/7856877/PT_501.xls.html

how tofind out which tables contain a certain SETID value.

2009-10-07T03:45:00.000-07:00

This post is to share another alternative on how to find out which tables contain a certain SETID value.
Navigate to PeopleTools > Archive Data > Find Data
On the "Field Name" field enter "SETID", on the "Value to Match" field enter the value you are looking for.
The system will give a list of each and every table that has the requested value on the requested field.
this can be used for any other table/value, not only SETID.

Another way to find in Oracle database is to run the below 2 SQL statements

SELECT TABLE_NAME
FROM DBA_TAB_COLUMNS
WHERE COLUMN_NAME='SETID'

SELECT TABLE_NAME
FROM DBA_TAB_COLUMNS
WHERE COLUMN_NAME='BUSINESS_UNIT'

To find all the records in a given component. Below is the query which takes component name as input and the list of all records in that component as

2009-10-01T05:14:00.000-07:00

To find all the records in a given component. Below is the query which takes component name as input and the list of all records in that component as output.

SELECT recname
FROM pspnlgroup a, pspnlfield b
WHERE (a.pnlname = b.pnlname OR a.pnlname = b.subpnlname)
AND a.pnlgrpname = :component_name
GROUP BY recname

PeopleSoft HR Tables

2009-09-29T04:11:00.000-07:00

PeopleSoft HR Tables

PS_EMPLOYEES
PS_EMPLOYEES_LNG
PS_EMPLOYMENT
PS_EMPLOYMENT_LNG
PS_EMPLOYMENT_LNG1
PS_EMPLOYMENT_LNG2
PS_EMPLOYMENT_LNG3
PS_HS_EMPLOYMENT
PS_HS_EMPL_REL_CAN
PS_HS_EMPL_REL_LNG
PS_HS_EMPL_SUB_CAN
PS_HS_EMPL_SUB_LNG
PS_INJ_CLMADDR_CAN
PS_JOB
PS_PERSONAL_DATA
PS_PERSONL_DTA_LNG
PS_PERS_DATA_AET
PS_PERS_DATA_EFFDT
PS_PERS_DTAEFF_LNG
PS_PERS_NID
PS_JOB_EARNS_DIST
PS_JOB_APPROVALS
PS_BEN_PROG_PARTIC

Complete PeopleSoft Tables

2009-09-29T04:10:00.000-07:00

Complete PeopleSoft Tables
PS_EMPLOYEES
PS_EMPLOYEES_LNG
PS_EMPLOYMENT
PS_EMPLOYMENT_LNG
PS_EMPLOYMENT_LNG1
PS_EMPLOYMENT_LNG2
PS_EMPLOYMENT_LNG3
PS_HS_EMPLOYMENT
PS_HS_EMPL_REL_CAN
PS_HS_EMPL_REL_LNG
PS_HS_EMPL_SUB_CAN
PS_HS_EMPL_SUB_LNG
PS_INJ_CLMADDR_CAN
PS_JOB, PS_PERSONAL_DATA
PS_PERSONL_DTA_LNG
PS_PERS_DATA_AET
PS_PERS_DATA_EFFDT
PS_PERS_DTAEFF_LNG
PS_PERS_NID
PS_JOB_EARNS_DIST
PS_JOB_APPROVALS
PS_STATE_TAX_DATA
PS_GENL_DEDUCTION
PS_BAL_ADJ_UI_CAN
PS_CAN_CHECK_YTD
PS_CAN_DED_BALANCE
PS_CAN_ERN_BALANCE
PS_CAN_TAX_BALANCE
PS_CAN_TAX_DATA
PS_CHECK_YTD
PS_DEDUCTION_BAL
PS_EARNINGS_BAL
PS_FED_TAX_DATA
PS_TAX_BALANCE
PS_LOCAL_TAX_DATA
PS_BAL_ADJ_ARR
PS_BAL_ADJ_CHK
PS_BAL_ADJ_CN_CHK
PS_BAL_ADJ_CN_DED
PS_BAL_ADJ_CN_ERN
PS_BAL_ADJ_CN_TAX
PS_BAL_ADJ_DED
PS_BAL_ADJ_ERN
PS_BAL_ADJ_GRN
PS_BAL_ADJ_TAX
PS_DED_ARREARS
PS_GARN_RULE
PS_GARN_SCHED
PS_GARN_SPEC
PS_GENL_DED_CD
PS_BAS_PARTIC
PS_BAS_PARTIC_COST
PS_BAS_PARTIC_DPND
PS_BAS_PARTIC_INVT
PS_BAS_PARTIC_OPTN
PS_BAS_PARTIC_PLAN
PS_BENEF_COMMENT
PS_BEN_PROG_PARTIC
PS_DEPENDENT_BENEF
PS_DISABILITY_BEN
PS_FSA_BENEFIT
PS_FSA_PAYMENT
PS_HEALTH_BENEFIT
PS_HEALTH_DEPENDNT
PS_LEAVE_ACCRUAL
PS_LEAVE_PLAN
PS_LIFE_ADD_BEN
PS_LIFE_ADD_BENEFC
PS_PENSION_BENEFC
PS_PENSION_PLAN
PS_RTRMNT_PLAN
PS_SAVINGS_BENEFIC
PS_SAVINGS_INVEST
PS_SAVINGS_PLAN
PS_VACATION_BEN

PeopleSoft Base Benefits Tables

2009-09-29T04:01:00.000-07:00

Base Benefits Tables
====================

PS_BAS_PARTIC
PS_BAS_PARTIC_COST
PS_BAS_PARTIC_DPND
PS_BAS_PARTIC_INVT
PS_BAS_PARTIC_OPTN
PS_BAS_PARTIC_PLAN
PS_BENEF_COMMENT
PS_BEN_PROG_PARTIC
PS_DEPENDENT_BENEF
PS_DISABILITY_BEN
PS_FSA_BENEFIT
PS_FSA_PAYMENT
PS_HEALTH_BENEFIT
PS_HEALTH_DEPENDNT
PS_LEAVE_ACCRUAL
PS_LEAVE_PLAN
PS_LIFE_ADD_BEN
PS_LIFE_ADD_BENEFC
PS_PENSION_BENEFC
PS_PENSION_PLAN
PS_RTRMNT_PLAN
PS_SAVINGS_BENEFIC
PS_SAVINGS_INVEST
PS_SAVINGS_PLAN
PS_VACATION_BEN

People Payroll Tables

2009-09-29T03:51:00.000-07:00

Payroll Tables
==============
PS_STATE_TAX_DATA
PS_GENL_DEDUCTION
PS_BAL_ADJ_UI_CAN
PS_CAN_CHECK_YTDPS_CAN_DED_BALANCE
PS_CAN_ERN_BALANCE
PS_CAN_TAX_BALANCE
PS_CAN_TAX_DATA
PS_CHECK_YTDPS_DEDUCTION_BAL
PS_EARNINGS_BAL
PS_FED_TAX_DATA
PS_TAX_BALANCE
PS_LOCAL_TAX_DATAPS_BAL_ADJ_ARR
PS_BAL_ADJ_CHK
PS_BAL_ADJ_CN_CHK
PS_BAL_ADJ_CN_DED
PS_BAL_ADJ_CN_ERN PS_BAL_ADJ_CN_TAX
PS_BAL_ADJ_DED
PS_BAL_ADJ_ERN
PS_BAL_ADJ_GRN
PS_BAL_ADJ_TAXPS_DED_ARREARS
PS_GARN_RULE
PS_GARN_SCHED
PS_GARN_SPEC
PS_GENL_DED_CD

To get the process output location in SQR

2009-09-08T01:46:00.001-07:00

To get the process output location in SQR

select PRCSOUTPUTDIR
from PSPRCSPARMS
where PRCSINSTANCE = '123456789'

To know the Run Status when running through the Process Scheduler

2009-09-08T01:33:00.000-07:00

To know the Run Status while running through the Process Scheduler

select FIELDVALUE, XLATLONGNAME
from PSXLATITEM
where FIELDNAME = 'RUNSTATUS'

The summary of the run status translates (from PeopleTools 8.49). Note that not all of these are active.

Value Status
===== =======
1 Cancel
2 Delete
3 Error
4 Hold
5 Queued
6 Initiated
7 Processing
8 Cancelled
9 Success
10 Not Successful
11 Posted
12 Unable to Post
13 resend
14 Posting
15 Content Generated
16 Pending
17 Success with Warning
18 Blocked
19 Restart

The following below query will give us the summary of the process run statuses in your process request table

select
RUNSTATUS,
(
select XLATSHORTNAME
from PSXLATITEM
where FIELDNAME = 'RUNSTATUS'
and FIELDVALUE = RUNSTATUS
) as RUNSTATUS_DESCR,
count(PRCSINSTANCE)
from
PSPRCSRQST
group by
RUNSTATUS
order by
RUNSTATUS;

For getting a particular row number from a table

2009-09-08T00:19:00.000-07:00

SELECT * FROM ( SELECT *, ROW_NUMBER() OVER (ORDER BY name) as row FROM sys.databases ) a WHERE row > 5 and row <= 10

The query above returns rows 6 through 10 from sys.databases as ordered by the "name" column. ROW_NUMBER() is the key function we're using here. It's one of a set of ranking functions introduced in 2005. Note that it's always accompanied by an OVER clause that specifies the ordering that the row_number should be based on.

U can find the above query in the blow link
http://blogs.msdn.com/sqlserver/archive/2006/10/25/limit-in-sql-server.aspx

Peoplesoft Hot Keys

2009-09-07T03:02:00.001-07:00

We all probably know that “CTRL J” hot key gives us the system information. But did you know that there are a lot more available. But you do not have to trouble your brain to remember them, simply press navigate to a search or transaction page and invoke hot key “CTRL K” to get all the information. Below are the results from PeopleSoft “CTRL K”.
�
Accessing your application using the keyboard
Keyboard navigation is controlled by Hot keys and Access keys .
�
List of Hot Keys
Alt 1 — Executes different buttons depending on the page type
> Save button on the Toolbar in a page
> OK button on a secondary page
> Search or Add button on a Search or Lookup page
Alt 2 — Return to Search
Alt 3 — Next in List
Alt 4 — Previous in List
Alt 5 — Valid Lookup Values
Alt 6 — Related Links
Alt 7 — Insert Row in grid or scroll area
Alt 8 — Delete Row in grid or scroll area
Alt 0 — Refreshes the page by invoking the Refresh button on the Toolbar
Alt . — Next set of rows in grid or scroll area [e.g., Alt period]
Alt , — Previous set of rows in grid or scroll area [e.g., Alt comma]
Alt / — Find in grid or scroll area [e.g., Alt forward slash]
Alt ‘ — View All in grid or scroll area [e.g., Alt prime]
Alt \ — Toggle between Add and Update on the Search page [e.g., Alt backslash]
Ctrl J — System Information
Ctrl K — Keyboard Information
Ctrl Y — Toggle menu between collapse and expand.
Ctrl Tab — Toggles focus through the frame set
Enter — Invokes the following buttons where present: OK, Search, Lookup
Esc — Cancel
�
List of Access Keys
Alt 9 — Takes you to the Help line
Alt \ — Takes you to the Toolbar [e.g., Alt backslash Enter]
Ctrl Z — Takes you to the Search box of the Menu
�
Menu Access Keys
The Ctrl Z combination will focus your cursor onto the menuing system. From there, you can use your tab key (or shift-tab to reverse direction) to navigate through the menu hierarchy.
�
About Access keys and Hot keys
> An Access Key is an Alt key combination that moves focus to a specified field on the current page.
For example, Alt \ moves focus to first button on the Toolbar. Then pressing the Enter key would invoke that action. Or, you may use the Tab key to move you to the next Toolbar button.
> A Hot Key performs an immediate action. For example, when focus is in a field that has lookup processing, Alt 5 invokes the Lookup page without having to press the Enter key.

How to read the runcontrol parameters in AE

2009-08-30T22:51:00.000-07:00

We can read the Runcontrol Parameters in Application Engine by the following below 2 ways:

SQL
%select(EMPLID)selct EMPLID from PS_AERUNCONTROL where PROCESS_INSTANCE=%PROCESS_INSTANCE AND OPRID=%oprid

PeopleCode
&SQL="Select EMPLID from PS_AERUNCONTROL where PROCESS_INSTANCE="|PS_TEST_AET.PROCESS_INSTANCE|"and OPRID="|%oprid

%PROCESS_INSTANCE or %Bind(PROCESS_INSTANCE) can be used.
%PROCESS_INSTANCE is more efficent and faster

Sample People Code for getting the DEPT ID based on the ORG ID

2009-08-19T23:03:00.000-07:00

Below is the 3 level rowset peoplecode for getting the Department ID prompt values when we select the Organization ID from prompt table.(i'e when we select the Organization ID from the prompt table then the departments under the particular Organization should only display)
Local Rowset &RS0, &RS1, &RS2;
&RS0 = GetLevel0();
&RS1 = &RS0(1).GetRowset(Scroll.SRI_ORG1_TBL);
For &I = 1 To &RS1.ActiveRowCount
&ID = &RS1(&I).GetRecord(Record.SRI_ORG1_TBL).GetField(Field.SRI_ORG_ID).Value;
&SQL = CreateSQL("SELECT DESCR FROM PS_EXT_ORG_TBL WHERE EXT_ORG_ID=:1", &ID);
While &SQL.FETCH(&DESCR)
&RS1(&I).GetRecord(Record.SRI_ORG1_TBL).GetField(Field.SRI_ORG1_NAME).Value = &DESCR;
REM WinMessage(&DESCR, 0);

&RS2 = &RS1(&I).GetRowset(Scroll.SRI_DEPT1_TBL);

For &J = 1 To &RS2.ActiveRowCount;
&DI = &RS2(&J).GetRecord(Record.SRI_DEPT1_TBL).GetField(Field.SRI_ORG_ID).Value;
&SQL1 = CreateSQL("SELECT ORG_DEPARTMENT FROM PS_ORG_DEPT WHERE EXT_ORG_ID=:1", &DI);
While &SQL1.FETCH(&ORG_DEPARTMENT)
REM SQLEXEC("SELECT COUNT(EMPLID) FROM ");
If &ORG_DEPARTMENT = "" Then
&RS2(&J).GetRecord(Record.SRI_DEPT1_TBL).GetField(Field.SRI_DEPT_ID).Value = "";
WinMessage("There are no Departments under this Organisation ID", 0);
Else
&RS2(&J).GetRecord(Record.SRI_DEPT1_TBL).GetField(Field.SRI_DEPT_ID).Value = "";

rem WinMessage(&ORG_DEPARTMENT, 0);

End-If;
End-While;
End-For;
End-While;
End-For;

People Code for calucating the student marks,average and grade

2009-08-19T22:52:00.000-07:00

&A = STUD_TBL.STUD_MI.Value;
&B = STUD_TBL.STUD_M2.Value;
&C = STUD_TBL.STUD_M3.Value;
STUD_TBL.STUD_TOT = &A + &B + &C;
STUD_TBL.STUD_AVG = (&A + &B + &C) / 3;
If (&A = "") Or
(&B = "") Or
(&C = "") Then
STUD_TBL.STUD_GRADE = "";
Else
If (&A < 35) Or
(&B < 35) Or
(&C < 35) Then
STUD_TBL.STUD_GRADE = "FAIL";
Else
If (STUD_TBL.STUD_AVG > 35) And
(STUD_TBL.STUD_AVG < 50) Or
(STUD_TBL.STUD_AVG > 35) And
(STUD_TBL.STUD_AVG < 50) Or
(STUD_TBL.STUD_AVG > 35) And
(STUD_TBL.STUD_AVG < 50) Then
STUD_TBL.STUD_GRADE = "C";
Else
If (STUD_TBL.STUD_AVG > 50) And
(STUD_TBL.STUD_AVG < 70) Or
(STUD_TBL.STUD_AVG > 50) And
(STUD_TBL.STUD_AVG < 70) Or
(STUD_TBL.STUD_AVG > 50) And
(STUD_TBL.STUD_AVG < 70) Then
STUD_TBL.STUD_GRADE = "B";
Else
If (STUD_TBL.STUD_AVG > 70) And
(STUD_TBL.STUD_AVG <= 100) Or
(STUD_TBL.STUD_AVG > 70) And
(STUD_TBL.STUD_AVG <= 100) Or
(STUD_TBL.STUD_AVG > 70) And
(STUD_TBL.STUD_AVG <= 100) Then
STUD_TBL.STUD_GRADE = "A"
End-If
End-If
End-If
End-If
End-If

Sample 2 level rowset code for Language selection

2009-08-19T22:47:00.000-07:00

When we select the the language from the drop down list then the field names in two level's should change automatically.Below is the sample rowset (RS0,RS1) code for 2 levels.

Local Rowset &rs0, &rs1;
&field = GetField(SRI_ORGSAMP_TBL.SRI_ORGSAMP_ID);
&field1 = GetField(SRI_ORGSAMP_TBL.SRI_ORGSAMP_NAME);
&LABELID = &field.Name;
&LABELID1 = &field1.Name;

/*CHINEESE LANGUAGE*/
If SRI_ORGSAMP_TBL.SRI_LANGUAGE.Value = "CHIN" Or
SRI_ORGSAMP_TBL.SRI_LANGUAGE.Value = "" Then
&field.Label = "GRO DI";
&field1.Label = "GRO EMAN";
&rs0 = GetLevel0();
&rs1 = &rs0(1).GetRowset(Scroll.SRI_EMPSAMP_TBL);
For &i = 1 To &rs1.ActiveRowCount
/*Variable*/
&empid = &rs1(&i).GetRecord(Record.SRI_EMPSAMP_TBL).GetField(Field.SRI_EMPSAMP_ID);
&empid.Label = "EEOLPME DI";
&NAME = &rs1(&i).GetRecord(Record.SRI_EMPSAMP_TBL).GetField(Field.SRI_EMPSAMP_NAME);
&NAME.Label = "EMAN";
&GENDER = &rs1(&i).GetRecord(Record.SRI_EMPSAMP_TBL).GetField(Field.SRI_EMPSAMP_GENDER);
&GENDER.Label = "REDNEG";
&COUN = &rs1(&i).GetRecord(Record.SRI_EMPSAMP_TBL).GetField(Field.SRI_EMPSAMP_COUN);
&COUN.Label = "GNIKROW NOITACOL";
&DOB = &rs1(&i).GetRecord(Record.SRI_EMPSAMP_TBL).GetField(Field.SRI_EMPSAMP_DOB);
&DOB.Label = "BOD";
&DOJ = &rs1(&i).GetRecord(Record.SRI_EMPSAMP_TBL).GetField(Field.SRI_EMPSAMP_DOJ);
&DOJ.Label = "JOD";
End-For;

End-If;
/*END OFCHINEESE LANGUAGE*/
/*FRANCE LANGUAGE*/
If SRI_ORGSAMP_TBL.SRI_LANGUAGE.Value = "FRAN" Or
SRI_ORGSAMP_TBL.SRI_LANGUAGE.Value = "" Then
&field.Label = "ORGANI ID";
&field1.Label = "NAMEE";
&rs0 = GetLevel0();
&rs1 = &rs0(1).GetRowset(Scroll.SRI_EMPSAMP_TBL);
For &i = 1 To &rs1.ActiveRowCount
/*Variable*/
&empid = &rs1(&i).GetRecord(Record.SRI_EMPSAMP_TBL).GetField(Field.SRI_EMPSAMP_ID);
&empid.Label = "EMPOLOD ID";
&NAME = &rs1(&i).GetRecord(Record.SRI_EMPSAMP_TBL).GetField(Field.SRI_EMPSAMP_NAME);
&NAME.Label = "NAMEE";
&GENDER = &rs1(&i).GetRecord(Record.SRI_EMPSAMP_TBL).GetField(Field.SRI_EMPSAMP_GENDER);
&GENDER.Label = "DREE";
&COUN = &rs1(&i).GetRecord(Record.SRI_EMPSAMP_TBL).GetField(Field.SRI_EMPSAMP_COUN);
&COUN.Label = "Workingo Locationio";
&DOB = &rs1(&i).GetRecord(Record.SRI_EMPSAMP_TBL).GetField(Field.SRI_EMPSAMP_DOB);
&DOB.Label = "birtho";
&DOJ = &rs1(&i).GetRecord(Record.SRI_EMPSAMP_TBL).GetField(Field.SRI_EMPSAMP_DOJ);
&DOJ.Label = "joino";
End-For;

End-If;
/*END OF FRANCE LANGUAGE*/

/*ENGLISH LANGUAGE*/
If SRI_ORGSAMP_TBL.SRI_LANGUAGE.Value = "ENG" Or
SRI_ORGSAMP_TBL.SRI_LANGUAGE.Value = "" Then
&field.Label = "ORGANIZATION ID";
&field1.Label = "ORG NAME";
&rs0 = GetLevel0();
&rs1 = &rs0(1).GetRowset(Scroll.SRI_EMPSAMP_TBL);
For &i = 1 To &rs1.ActiveRowCount
/*Variable*/
&empid = &rs1(&i).GetRecord(Record.SRI_EMPSAMP_TBL).GetField(Field.SRI_EMPSAMP_ID);
&empid.Label = "EMPLOYEE ID";
&NAME = &rs1(&i).GetRecord(Record.SRI_EMPSAMP_TBL).GetField(Field.SRI_EMPSAMP_NAME);
&NAMELabel = "NAME";
&GENDER = &rs1(&i).GetRecord(Record.SRI_EMPSAMP_TBL).GetField(Field.SRI_EMPSAMP_GENDER);
&GENDER.Label = "GENDER";
&COUN = &rs1(&i).GetRecord(Record.SRI_EMPSAMP_TBL).GetField(Field.SRI_EMPSAMP_COUN);
&COUN.Label = "WORKING LOCATION ";
&DOB = &rs1(&i).GetRecord(Record.SRI_EMPSAMP_TBL).GetField(Field.SRI_EMPSAMP_DOB);
&DOB.Label = "DATE OF BIRTH";
&DOJ = &rs1(&i).GetRecord(Record.SRI_EMPSAMP_TBL).GetField(Field.SRI_EMPSAMP_DOJ);
&DOJ.Label = "DATE OF JOIN";
End-For;

End-If;
/*END OF ENGLISH LANGUAGE*/

To get the OprID for the Current Session

2009-08-13T04:28:00.000-07:00

For getting the OprID for the current session then write the following below line of code in Record Level Save PreChange Event

SRI_STUORG4_TBL.SRI_STUORG_OPRID = %UserId;

select any check box in the Grid then the remaning check boxes in the same column should hide automatically

2009-08-13T04:13:00.000-07:00

Level 0 = Org ID,Name
Level 1 = Org details
Level 2 = 3 Grids (Active locations,Primary Contact,Active Departments)

When we select any check box then the remaning check boxes in the same column should hide automatically.And the corresponding Name (Active locations,Primary Contact,Active Departments)which is in grid should be displayed in the Corresponding fields.

Write the following below code in the Component Record Level Field change event.

Local Rowset &RS0, &RS1, &RS2, &rs4;
&RS0 = GetLevel0();
&RS1 = &RS0(1).GetRowset(Scroll.SRI_STUORG1_TBL);
&CNT = 0;
For &I = 1 To &RS1.ActiveRowCount
&RS2 = &RS1(&I).GetRowset(Scroll.SRI_STUORG3_TBL);
For &J = 1 To &RS2.ActiveRowCount
&chk = &RS2.GetRow(&J).GetRecord(Record.SRI_STUORG3_TBL).GetField(Field.SRI_ORG_PRIMARY).Value;
&cr = CurrentRowNumber(2);

If &RS2.GetRow(&J).GetRecord(Record.SRI_STUORG3_TBL).GetField(Field.SRI_ORG_PRIMARY).Value = "Y" Then

&id = &RS2.GetRow(&J).GetRecord(Record.SRI_STUORG3_TBL).GetField(Field.SRI_ORG_CONTACTNO).Value;
&RS2.GetRow(&J).GetRecord(Record.SRI_STUORG3_TBL).GetField(Field.SRI_ORG_PRIMARYCOU).Value = &id;

&CNT = &CNT + 1;

For &p = 1 To &RS2.ActiveRowCount
REM WinMessage("in for ", 0);
&RS2.GetRow(&p).GetRecord(Record.SRI_STUORG3_TBL).GetField(Field.SRI_ORG_PRIMARY).Visible = False;
End-For;
&RS2.GetRow(&cr).GetRecord(Record.SRI_STUORG3_TBL).GetField(Field.SRI_ORG_PRIMARY).Visible = True;
REM WinMessage("count " | &CNT, 0);
End-If;

If &RS2.GetRow(&J).GetRecord(Record.SRI_STUORG3_TBL).GetField(Field.SRI_ORG_PRIMARY).Value = "N" And
&CNT = 0 Then

&RS2.GetRow(&J).GetRecord(Record.SRI_STUORG3_TBL).GetField(Field.SRI_ORG_PRIMARYCOU).Value = &id;
REM WinMessage("in if1", 0);
&RS2.GetRow(&J).GetRecord(Record.SRI_STUORG3_TBL).GetField(Field.SRI_ORG_PRIMARY).Visible = True;

End-If;

If &RS2.GetRow(&J).GetRecord(Record.SRI_STUORG3_TBL).GetField(Field.SRI_ORG_PRIMARY).Value = "N" And
&CNT > 0 Then

&RS2.GetRow(&J).GetRecord(Record.SRI_STUORG3_TBL).GetField(Field.SRI_ORG_PRIMARYCOU).Value = &id;
If &J <> &cr And
&RS2.GetRow(&cr).GetRecord(Record.SRI_STUORG3_TBL).GetField(Field.SRI_ORG_PRIMARY).Value = "Y" Then
REM WinMessage("in if2", 0);
&RS2.GetRow(&J).GetRecord(Record.SRI_STUORG3_TBL).GetField(Field.SRI_ORG_PRIMARY).Visible = False;
End-If;

If &RS2.GetRow(&cr).GetRecord(Record.SRI_STUORG3_TBL).GetField(Field.SRI_ORG_PRIMARY).Value = "N" Then
&RS2.GetRow(&J).GetRecord(Record.SRI_STUORG3_TBL).GetField(Field.SRI_ORG_PRIMARYCOU).Value = &id;
For &u = 1 To &RS2.ActiveRowCount
REM WinMessage("in if2", 0);
&RS2.GetRow(&u).GetRecord(Record.SRI_STUORG3_TBL).GetField(Field.SRI_ORG_PRIMARY).Visible = True;
End-For;
End-If;
End-If;

End-For;
End-For;

Fill color in grid Cell

2009-08-11T21:30:00.000-07:00

FOR &Z = 1 TO &RS.ACTIVEROWCOUNT
&RS(&Z).RSRECNAME.RECFIELDNAME.Style = "STYLEVALUE";
END-FOR

Date display format on Page

2009-08-11T05:16:00.000-07:00

In Order to change the date format through Personalization, please follow the steps given below:

1. Navigate, Root > People Tools > Personalization > Personalization Options > Search

Here, you will get the option for the different application for which you want to change the Date Format or you can also choose PPTL (People Tools) which change the Date Format in the Tools itself. Select, PPTL.

2. Now, you can see there is a Definition---> DFRMT, for the Date Format.

3. On the Top of this page and beside the Definition Tab, you will find the Format Tab, just click on this Tab.

4. Now, click on the "Set Option Default Value".

5. Change the Option Default Value as per your requirement MMDDYY and click OK.

6. Save the changes.

========================================
========================================

If you want to change the Date Format for a Specific User on specific page then you can also do this through People Coding.

You can also play with the below code with the help of %oprid, like if %oprid then the below code or w.r.t roles through "IsUserInRole" function.

There is one built-in function "DateTimeToLocalizedString". You can use this if you want to change the date format through people code for your PIA pages.

How to use:

Example 1:
&String = DateTimeToLocalizedString(&Date, "M/d/y");
/* if you are picking the date from any record field and using &Date for that field */

Example 2:
&String = DateTimeToLocalizedString(%Date, "M/d/y");
/* if you want to pick the system date and want to use it on your page with different formats. */

-- Assign this String variable (&String) to your desired field.

Note: Here 'd' for date and 'y' for year both must be in smaller letters only. And, 'M' for months should be in Caps.

For getting the current DAteTime when ever we change or edit any field i

2009-08-11T04:39:00.000-07:00

Write the following below code in the Record Level in Save Prechange

Local Rowset &RS0, &RS1, &RS2;

&RS0 = GetLevel0();
&RS1 = &RS0(1).GetRowset(Scroll.SRI_STUORG1_TBL);
For &I = 1 To &RS1.ActiveRowCount
&RS2 = &RS1(&I).GetRowset(Scroll.SRI_STUORG4_TBL);
For &J = 1 To &RS2.ActiveRowCount

If &RS2.GetRow(&J).IsChanged Then

&RS2.GetRow(&I).GetRecord(Record.SRI_STUORG4_TBL).GetField(Field.SRI_STUORG_DTTIME).Value = %Datetime;
End-If;

End-For;
End-For;

Inserting a New Scroll Row from within the Same Scroll

2009-08-09T22:56:00.000-07:00

In PeopleCode, when inserting or deleting a row on a scroll, it is required that you perform the action on a parent row of the rowset being inserted/deleted to. PeopleTools doesn’t allow PeopleCode (using the built-in functions/methods InsertRow and DeleteRow) to insert or delete a row on the same scroll within which it is currently running. If you attempt to do this, PeopleTools will give an error complaining about changing the current program context.

However, there are some cases where inserting within the same scroll might be desirable. For example, based on the data entered on a row of a scroll, a new matching row must be inserted within the same scroll.

Background

The Rowset class has a SelectNew method, which is quite similar to the Select method. The main difference is that rows populated through SelectNew are marked as new in the component buffer. Both Select and SelectNew allows the record source of the data being loaded to the rowset to be different from the primary record of the rowset. There is a requirement, however, that the source record must contain at least one of the key fields in the primary record. Though, none of the fields in the source record have to be keys as well.
The Technique

Step 1. Create a new Dynamic View record containing at least one of the keys of the primary record of the scroll. For this tutorial, assume that the name of the new record is SCROLL_INS_ROW. This is important: none of the fields in this record must be set to a key.

Step 2. Set the SQL of the view to the following:

SELECT NULL
FROM PS_INSTALLATION

Of course, if you’ve included more fields, then SELECT the appropriate number of NULL’s on the SQL.

Step 3. The PeopleCode for inserting a row within the same scroll would be the following:

Local Rowset &this_rowset = GetRowset();
&this_rowset.SelectNew(Record.SCROLL_INS_ROW);

Month difference PeopleCode

2009-08-09T22:41:00.002-07:00

Function diff_months(&DATE1 As date, &DATE2 As date) Returns number;
Local integer &YEAR1 = Year(&DATE1);
Local integer &MONTH1 = Month(&DATE1);
Local integer &DAY1 = Day(&DATE1);
Local integer &YEAR2 = Year(&DATE2);
Local integer &MONTH2 = Month(&DATE2);
Local integer &DAY2 = Day(&DATE2);
If &DATE1 <= &DATE2 Then
If &DAY1 > &DAY2 Then
If &MONTH1 = 12 Then
&MONTH1 = 1;
&YEAR1 = &YEAR1 + 1;
Else
&MONTH1 = &MONTH1 + 1;
End-If;
End-If;
Else
If &DAY1 < &DAY2 Then
If &MONTH2 = 12 Then
&MONTH2 = 1;
&YEAR2 = &YEAR2 + 1;
Else
&MONTH2 = &MONTH2 + 1;
End-If;
End-If;
End-If;
Local integer &DIFF_MONTHS = 12 * (&YEAR2 - &YEAR1) + &MONTH2 - &MONTH1;
Return &DIFF_MONTHS;

End-Function;

File operations using DOS commands in SQR

2009-08-09T22:41:00.001-07:00

We can use the following as an sqc, for performing file operations in SQR.

!*********************************************************************
!Program Name : FileOprs.sqc
!Description : File operations using DOS commands.
!*********************************************************************

!*********************************************************************
!Procedure Name : Make_Dir
!Description : Creates a new path based on the input. Path must be
! a complete directory tree listing.
!Usage : Do Make_Dir($Directory_NewPath)
!*********************************************************************
begin-procedure Make_Dir($NewPath)
#debugy show ‘In Procedure : Make_Dir’

Let $Commmand_line = ‘cmd /c mkdir ‘ || $NewPath
Do Exec_Cmd($Commmand_line)

end-procedure ! Make_Dir

!*********************************************************************
!Procedure Name : Exec_Cmd
!Description : Executes $Commmand_line on the system and reports
! errors for debugging.
!Usage : Do Exec_Cmd($Commmand_line)
!*********************************************************************
begin-procedure Exec_Cmd($Commmand_line)
#debugy show ‘In Procedure : Exec_Cmd’

call system using $Commmand_line #status nowait
If #status <> 0
#debugy show ‘Command ‘ $Commmand_line ‘ failed to execute.’
End-If

end-procedure ! Exec_Cmd

!*********************************************************************
!Procedure Name : Copy_Files
!Description : Copy files from old to new path. Old information should be of
! form \pathname\filename, while new information can just be a path.
!Usage : Do Copy_Files($old_file_name, $new_file_name)
!*********************************************************************
begin-procedure Copy_Files($old, $new)
#debugy show ‘In Procedure : Copy_Files’

Let $Commmand_line = ‘cmd /c copy ‘ || $old || ‘ ‘ || $new
Do Exec_Cmd($Commmand_line)

end-procedure ! Copy_Files

!*********************************************************************
!Procedure Name : Move_Files
!Description : Move files from old to new path. Old information should be of
! form \pathname\filename, while new information can just be a path.
!Usage : Do Move_Files($old_file_name, $new_file_name)
!*********************************************************************
begin-procedure Move_Files($old, $new)
#debugy show ‘In Procedure : Move_Files’

Let $Commmand_line = ‘cmd /c move ‘ || $old || ‘ ‘ || $new
Do Exec_Cmd($Commmand_line)

end-procedure ! Move_Files

!*********************************************************************
!Procedure Name : Delete_Files
!Description : Deletes the files passed in variable $files
!Usage : Do Delete_Files($files)
!*********************************************************************
begin-procedure Delete_Files($files)
#debugy show ‘In Procedure : Delete_Files’

Let $Commmand_line = ‘cmd /c del ‘ || $files
Do Exec_Cmd($Commmand_line)

end-procedure ! Delete_Files

Running an SQR from within your PeopleCode program

2009-08-09T22:17:00.000-07:00

The best and safest way to launch an SQR program is to use the PeopleCode functions CreateProcessRequest() and Schedule().

The CreateProcessRequest function allows you to create a ProcessRequest object.
The CreateProccessRequest function takes 2 arguments. The Process Type and the Process Name.

REM Declare your Variables;
Local ProcessRequest &MYRQST;
Local String &MySQR;
&MySQR = "DDDAUDIT"
REM Create My Process Request Object;
&MYRQST = CreateProcessRequest("SQR Process", &MySQR);

REM Set Properties of My Process Request Object;
&MYRQST.RunControlID = "MYRUNCNTRL_ID"

REM Set Properties of My Process Request Object;
&MYRQST.SetOutputOption("Web", "PDF", "", &MySQR);

The above example creates a ProcessRequest object for the DDDAUDIT SQR named &MYRQST. Also specified Run Control ID and the output options. I can now take this Object and use the Schedule() method agains it to Schedule the SQR. Here is an example.

&MYRQST.Schedule();
If &MYRQST.Status = 0 then
/* Schedule succeeded. */
Else
/* Process (job) not scheduled, do error processing */
End-If;

Date related calucations

2009-08-09T22:08:00.000-07:00

Local date &From_Date, &To_Dt;

&Duration = 0;
&Tot_Holi = 0;
&Sat = 0;
&Sun = 0;

&From_Dt = SRI_STUMOD_TBL.SRI_STUAB_STD.Value;
&To_Dt = SRI_STUMOD_TBL.SRI_STUAB_END.Value;

While (&From_Dt <= &To_Dt);
&From_Dt = AddToDate(&From_Dt, 0, 0, 1);
If (Weekday(&From_Dt) = 7) Then
&Sat = &Sat + 1;
End-If;
If (Weekday(&From_Dt) = 1) Then
&Sun = &Sun + 1;
End-If;
If Weekday(&From_Dt) <> 7 And
Weekday(&From_Dt) <> 1 And
&flag <> "x" Then
&Duration = &Duration + 1;
End-If;

SQLExec("select 'x' from PS_HOLIDAY_DATE WHERE HOLIDAY=:1", &From_Dt, &flag);
If &flag = "x" Then
&Tot_Holi = &Tot_Holi + 1;
End-If;

End-While;
SRI_STUMOD_TBL.SRI_STUAB_SAT.Value = &Sat;
&Sat = 0;
SRI_STUMOD_TBL.SRI_STUAB_SUN.Value = &Sun;
&Sun =0;
SRI_STUMOD_TBL.SRI_STUAB_DURATION.Value = &Duration;

Date related calucations using rowsets

2009-08-09T22:06:00.000-07:00

Local Rowset &RS0, &RS1;
Local date &From_Date, &To_Dt;
&Duration = 0;
&Tot_Holi = 0;
&Sat = 0;
&Sun = 0;
&RS0 = GetLevel0();
&RS1 = &RS0(1).GetRowset(Scroll.SRI_STUMOD_TBL);
For &I = 1 To &RS1.ActiveRowCount
&From_Date=&RS1(I).GetRecord(Record.SRI_STUMOD_TBL).GetField(Field.SRI_STUAB_STD).Value;
&To_Dt =&RS1(&I).GetRecord(Record.SRI_STUMOD_TBL).GetField(Field.SRI_STUAB_END).Value;
While (&From_Date <= &To_Dt);

If (Weekday(&From_Date) = 7) Then
&Sat = &Sat + 1;
End-If;

If (Weekday(&From_Date) = 1) Then
&Sun = &Sun + 1;
End-If;

If Weekday(&From_Date) <> 7 And
Weekday(&From_Date) <> 1 And
&flag <> "x" Then
&Duration = &Duration + 1;

End-If;
&From_Date = AddToDate(&From_Date, 0, 0, 1);

End-While;

&RS1(&I).GetRecord(Record.SRI_STUMOD_TBL).GetField(Field.SRI_STUAB_DURATION).Value = &Duration;
If &Duration > 20 Then
Error MsgGet(11100, 180, "Only 20 leaves per annum please select the correct dates");
End-If;

&RS1(&I).GetRecord(Record.SRI_STUMOD_TBL).GetField(Field.SRI_STUAB_SAT).Value = &Sat;
WinMessage(&Sat, 0);
&Sat = 0;

&RS1(&I).GetRecord(Record.SRI_STUMOD_TBL).GetField(Field.SRI_STUAB_SUN).Value = &Sun;
&Sun = 0;

End-For;

Derived/ Work record

2009-07-30T02:10:00.000-07:00

When we want to call another component (The fields(Student ID and Name) in level 3 should be displayed in the other page fields)when we click on the hyperlink or Push button in Grid then

1.Create the Derived/ Work record with appropriate fields(Make sure that they are key fields).
2.Put the fields in the level 0 in both the pages.
3.Assign the values(Student ID and Name)
4.Use the Domaodalcomponent function
SYNTAX:DoModalComponent(MENUNAME.menuname, BARNAME.barname, ITEMNAME.menuitem_name, PAGE.component_item_name, action, RECORD.shared_record_name [, keylist])

To know the Menu,Bar,Item name's to use in Transfer,Transfer Exact,Domodal component

2009-07-30T00:04:00.000-07:00

To know the Menu,Bar,Item name's to use in Transfer,Transfer Exact,Domodal component(when we click on the hyper link or push button in grid to transfer to another component) use the following below SQL

select *
from PSAUTHITEM
where MENUNAME = 'your menu name'
and PNLITEMNAME = 'your page name'

Structured Querry Report

2009-07-22T07:04:00.000-07:00

Structured Query Report (SQR)
Structured Query Report Writer is a programming language that combines the power of SQL queries, the sophistication of procedural logic, and the freedom of the multiple-platform development. It works equally well on client and server, crosses almost seamlessly between different platforms, and covers nearly all-relational databases.

With SQR we can extract data from and load data into the database, process complex file structures, print sophisticated reports with dynamic breaks at multiple levels, create interfaces between different systems, generate form letters with business charts, graphs and images.

SQR components include SQR Server, SQR viewer, SQR Print, SQR Execute, and SQR Workbench for windows.

SQR products are available for use with all major databases including oracle, Sysbase, MS SQL Server, DB2, SQLBase, Informix, Ingres, Red Brick, Rdb and AllBase.

With SQR we can do the following
1. Reports
2. Background sql process
3. Interfaces (file handling)

SQR will be stored on File Server not on the DB

SQR server runs on a wide variety of both desktop and enterprise operation systems

SQR programs are distributed at source level or as pre-compiled pseudo-code modules.

All SQR commands and functions are platform-transparent and require no changes when SQR programs are moved across platforms.

SQR SECTIONS:

In SQR there 5 Sections available

1. Setup Section – Begin-Setup/End-Setup
2. Heading Section - Begin-Heading /End-Heading
3. Footing Section - Begin-Footing/End-Footing
4. Program Section - Begin-Program/End-Program
5. Procedure Section - Begin-Procedure/End-Procedure

In SQR there are 3 Paragraphs available

1. Select Paragraph - Begin-Select/End-Select
2. SQL Paragraph - Begin-SQL/End-Sql
3. Document Paragraph - Begin-Document/End-Document

An SQR program consists of Sections. Each section starts with the Begin statement, follows with SQR commands that make up the section body, and ends with the End Statement.
Syntax
Begin-

End-

SQR program may include a number of different sections, but the only required one is the Program Section.

Begin-Program

End-Program

SQR Procedures:

An SQR program may include 1 or more SQR procedures. Procedures allow us to split our program into several logical pieces, which makes the program easy to understand and maintain.

Syntax:

Begin-Procedure

End-Procedure

Note: in earlier SQR versions the Program Section was called the Report Section.

No
Sections / Uses
1
Begin-Setup
End-Setup

This section contains commands that determine the overall characteristics of the program. This section is optional but, if included, must be placed at the beginning of the program. The Setup section is processed during the program compilation stage before actual program execution.

The following commands can be used in setup section: Ask, Begin-Sql, Create-Array, Declare-Variable, Load-Lookup, Use.

2
Begin-Heading
End-Heading

Commands in this section are executed every time SQR generates a page. When an SQR program generates multiple reports, we can have more than 1 heading section, however, there can be only 1 heading for each report.

The #lines parameter in the begin-heading statement defines the number of lines allocated for the heading.

3
Begin-Footing
End-Footing

The general structure of footing section is similar to the heading section.

The #lines parameter in the begin-footing statement defines the number of lines allocated for the footing.

4
Begin-Program
End-Program

This section is the required section in SQR program.
5
Begin-Procedure
End-Procedure

This section is optional, it can used to break our program into manageable and easy-to-maintain pieces. Multiple procedures can exist in SQR program, but must have unique names and be invoked with the help of the Do Command.

Procedures can global or local. By default, all procedures are global, all variables and selected columns from a global procedure can be referenced in other procedures.

To make procedure local, we have to explicitly declare it local using the LOCAL keyword. All procedures with arguments are considered as local.

All variables and selected columns created within a local procedure can be referenced only in this procedure and will not be recognized outside the procedure.

To reference a global variables in local procedure, we have to add an underscore to the variable name after its special character $,#,&. Ex: #_Count, $_Street, &_Emplid.

All SQR reserved variables are global. When referencing SQR-reserved variables in a local procedure, we have to add an underscore to their names ex:$_sql-error, #_sql-staus.

No
Paragraph / Uses
1
Begin-Select
End-Select

It is very similar to SQL Select statement. All selected column names are placed 1 per program line at the beginning of each line with no comma.

Note: all selected columns must be listed in the select paragraph; you cannot use an asterisk in the select paragraph.

Begin-Select Loops=10 determines the loop should repeat for only 10 rows.

When a row is not returned from a query, none of the SQR commands in the Select paragraph are executed.
2
Begin-SQL
End-SQL

SQR Select Command is similar to SQL Select statement, but not exactly the same. We have to use strict SQR syntax to select data and to process each selected row, and cannot use native SQL Select statement in SQR. Native SQL statements other than SQL Select are handled in the SQL paragraph. For ex, we may use this paragraph to load data into the DB or to update certain records in the DB. The SQL paragraph can be used in one of the following sections: Procedure section, Program Section, Setup Section.

The Sql paragraph starts with Begin-sql and ends with End-sql commands. If we have more than 1 sql statement in the same sql paragraph, the sql statements must be separated by a semicolon-‘;’.

On-Error clause can be used in SQl paragraph. This clause specifies the name of the procedure to be executed if an error occurs during the Sql exection.

Begin-Sql On-Error=Db_Error
Update Personal_data
Set Postal = $Old_zip
Where Postal = $New_zip
End-Sql

On-Error=Warn => is used when we want a warning message to be displayed when an error occurs.

On-Error=Stop =>is used when we want to halt the program if an error occurs. Note that the rest of the program will be scanned for errors but will not be processed.

On-Error=Skip => is used when we want to ignore all errors and to continue to run the program.
3
Begin-Document
End-Document

SQR Document paragraph allows to put information onto a report pages same as word processor. This is useful when creating form letters. We can design business forms or letters, and automatically populate them with data from the DB.

The Document paragraph allows combining the body of our standard letter with the values of variables or database columns. The Document paragraph can be used in an SQR Procedure section or in the Program Section. It is advisable to place it in the Procedure section. We can have multiple Document paragraphs in a single procedure.

Begin-Procedure Print_Document
Begin-Document (1,1)
Dear Friend:
.b ! use .b to print blank lines
This letter is written to let you know how simple is.
.b
Sincerely yours,
.b
.b
End-Document
End-Procedure

· Use spaces (not tabs) to indent text or field.
· Use .b to indicate an entire blank line.
· Use fixed or relative position to indicate the start of the paragraph on the page
· SQR commands are not allowed within the Document Paragraph.
· A Document paragraph must fit within a single report page.
· In order to place a variable or column into document, simply specify the variable or column name at the desired location.

Paragraph accessibility in Sections

Begin-Select
Begin-Sql
Begin-Document
Begin-Setup
No
Yes
No
Begin-Heading
No
No
No
Begin-Footing
No
No
No
Begin-Program
Yes
Yes
Yes
Begin-Procedure
Yes
Yes
Yes

SQR Dialog Box:

The SQR Dialog Box provides the perfect means of running SQR programs under windows. It also connects to the DB. The SQR dialog box is very simple to use.

When an SQR program runs under windows, you can see the results of all Display and Show statements executions on the screen. SQR will also display all compile errors on the same screen. All this screen information is written to the log file.

By default SQR creates an output file with name of your program and the extension ‘.lis’. This .lis file is basically designed for printing.

Unless otherwise specified SQR places the output file in the same directory with SQR program.

Note: if you do not specify different log file name for each SQR program run, the
next program execution will overlay the previously created log file.

Key Points

1. We can use ASCII text editor to type our SQR program
2. The SQR Dialog Box can be used to submit our program for execution under windows.
3. The following are commonly used extensions for different SQR files
.sqr - for SQR Programs
.lis - for Report files
log - for SQR log files

4. All print commands direct their output to the report file. All Show & Display commands direct their output to the log file.
5. An SQR program may include a number of different sections, but the only required one is the Program Section. Program Section may call one or more procedures using the DO command. Procedures may in tern call other procedures.
6. SQR uses an exclamation mark to indicate the start of a comment line in the program.

SQR Data Elements:

SQR has 3 main categories of data elements:
1. SQR columns
2. SQR Variables
3. SQR Literals

1. SQR Columns are used to reference columns defined in the database. In order to refer a table column, SQR uses the symbol ‘&’ as a prefix to the table column name. For ex: &Emplid, &Hire_date. We don’t have to declare column variable in SQR program.

2. SQR Variables can be
String variable, Numeric variable, Date Variable.

SQR uses ‘$’ symbol for String, Date variables ex:$Name,$MyDate
‘#’ Symbol for Numeric variables ex:#Count
‘%’ Symbol for list variables ex:%MyList

3. SQR Literals are String, Numeric or Date Constatns.

String Literals are placed in single quotes – ex: ‘this is string literal’
Numeric literals may include digits with optional decimal point and leading sign. Ex:2043.44,-434.55

SQR variables do not have to be declared explicitly in the program. When a variable appear for the first time, SQR assigns this variable its type (based on the first character of the variable’s name) and initial value. All string variables are initialized to the NULL valued. All numeric variables are initialized to zeros. All string variables have variable length.

The Declare-Variable command, which allows to explicitly declaring variables.

When date variables are declared, they are initialized to NULL values similarly to string variables.

Variable names are not case-sensitive: for ex- $Name is same as $NAME or $name. Variable names can be of any length.

SQR variables can be global or local. Global variables can be referenced throughout the entire program, whereas Local variables are effective only within a local procedure.

Predefined SQR Variables:

Predefined variables are not write-protected, so we can change their values just as like other variables.

Variable
Use
#Current-line
Denotes the current line number within the current program.
#Current-column
The current column number on the page.
#End-File
If the controls is at the end of file then #end-file = 1
Otherwise, #end-file = 0.
#Page-Count
The current page number
$Current-Date
The current date and time on the local machine when program started running.
#Return-Status
This value will be returned by SQR to the calling program.
#Sql-Count
The number of rows affected by any Sql DML statement (Insert, Update, Delete)
Note:#Sql-Count cannot be used to check the number of selected rows. It is used only to verify updates.
$Sql-Error
This string variable contains an error explanation message from the DB
#Sql-Status
A status value returned from the DB after each SQL statement is executed.
$Sqr-Database
SQR installation is set up to work with some DB and this variable can be checked in an SQR program to determine what DB we are working with.
#Sqr-Pid
The process Id of the current SQR run. This value is unique for each run of an SQR program and can be used to create unique composite file names.
$Sqr-Platform
This variable tells our SQR program which Operating System the program is run under. Valid values are DOS/Windows/Windows-NT/Unix/Vm/VMS/MVS.
$Sqr-locale
The name of the current locale. The SQR locale name is defined in the SQR.ini file.
$Sqr-dbcs
Specifies whether SQR recognized double-character strings. Valid values are ‘Yes’ or ‘No’
$Sqr-Program
The name of the SQR program
$Sqr-Report
The name of the report output file. This is actual name specified in the –F flag parameter or in the New-Report command.
$Sqr-ver
A string with SQR version ID.
$Username
The user name specified on the command line.
$Sqr-hostname
The name of the computer on which your SQR program is executed
#Sqr-max-lines
The max number of lines for the current report
#sqr-max-columns
The max number of columns for the current report.

Working with Dates

Date variables must be explicitly declared with the help of the Declare-Variable command. Date variable names are prefixed with a dollar sign-$.

In SQR dates can be stored as character strings in string format variables or as date in special date format variables.

Ex:
Begin-Setup
Declare-Variable
Date $MyDate
End-Declare
End-Setup

$Current-Date reserved variable can be used to get current date & time from the system.

List Variables

List variables contain ordered collections of SQR variables. These variables are not nested: you cannot include one list variable within another. List variables cannot be passed as parameters to local functions.

List variables are denoted with a special character %. Unlike other variables these variables cannot be declared via the Declare-Variable command. They are created and manipulated with the LET command. We can use list variables to hold one set of variables or multiple rows of information of similar structure.

Arithmetic commands:

There are 4 arithmetic commands are available
1. Add - Add #Employee_Salary To #Total_By_Department
2. Subtract - Subtract 1 From #Employee_Count
3. Multiply - Multiply &Rate_Increase Times #Total Round 2
4. Divide - Divide #Number-of_Employees Into #Avg_Sal Round 2

String Manipulation Commands

SQR has the following commands to perform string manipulations

1. Find - Find ‘John’ In $Full_Name 0 #Position
2. Concat - Concat $Full_zip with $Address
3. Extract - Extract $First_Name from $FullName #StartLocation #Lenth
4. Encode -
5. String - String $Emplid $Empl_Name By ‘,’ Into $Empl_Record
6. UnString - Unstring $Empl_Nam By ‘-‘ Into $FirstName $Initial $LastNam
7. Uppercase -
8. Lowercase -

Move Command

The Move command is used to moving data from one field to another, it can also perform data conversions and data editing using special edit masks.

The Move command can handle data of any format. The source and target fields for the Move command can have different formats. For ex: the source can be a numeric variable, and the target can be a string variable. In this case, the command not only moved data, but also performs the numeric-to-string conversion.

Move ’15,995.00’ To #Total !Results in incorrect move (error is - , comma)

Move ‘15.99500’ To #Total !Results in correct Move

Some Examples of Move command

Move &Phone To $Disp_Phone (xxx)bxxxx-xxx
Move #Salary To $Disp_Salary $9,999,999.99
Move ‘Month DD, YYYY’ To $Date_Mask
Move &Counter to #Number_of_Employees Number
Move &Annual_Rate To #Annual_salary Money
Move $Hire_Date to $Start_date Date

LET Command

The Let command is much more complex and versatile than other date element manipulation commands. A single Let command may be capable of replacing a number of date manipulation and logic commands.

Syntax: Let target_variable = expression

Expressions in the let command can be combinations of operands, operators and functions.
Let #Number_of_days = $Julian_day
Let #ytd_expense_avg = #ytd_expnse_total / #Number_of_days

The Let command can also be used to create and manipulate List Variables. List variables are used in conjunction with the Begin-Execute command that used list variables as parameters to pass them to external sources of information. SQR arrays cannot be used for this purpose.

Here is how we can create a list variable using the Let command

Let %Customer = List(#Cust_Num, $Cust_Nam, $Cust_Birth_Dt)

The command creates a single-row list variable. To create a multiple-row list variable, use slightly different format of the same command.

Let %Customer[100]=List(NUMBER’.Cust_Num’,TEXT’.Cust_Nm’,DATE’.Cust_Birth_dt’)

The number between the square brackets indicated the number of rows in the list.

To retrieve values stored in components of list variables, use the following Let command.

Let $My_Cust_Name = %Customer[20].Cust_Nm

SQR Functions

SQR version 6 offers more than 50 different built-in functions. In addition, we can write our own functions in C using the supplied source file ‘ufunc.c’

The following are the different types functions available in SQR.

1. Numeric Functions
2. File-related Functions
3. Date Functions
4. String Functions
5. Miscellaneous Functions

1. Numeric Functions:
Abs():this function returns a value of the same type as its argument either integer, float or decimal.

Let #Percent_difference = (abs(#Avg_salary - &Salary) / #Avg_salary) * 100

2. File-related Functions:
These functions are helpful in performing file maintenance, and can sometimes replace calls to the OS.

There are 3 functions available. All these function returns Zero if the operation was successful; otherwise they return the system error code (platform-specific)

a. Delete() : Let #status = exists($New_file_Name)
If #status=0
Let DelStatus = Delete($New_file_name)
End-if

b. Exists() : Move ‘C:\temp\testb.lis’ To $New_File_Name
Let #status = Exists($New_file_Name)

c. Rename(): Let #Status = Exists($New_file_name)
If #Status =0
Let RenFile = Rename($File_Name, $New_file)
End-if

3. Date Functions:

a. StrToDate() : This function converts String to Date
Date $MyDate1
Date $MyDate2
Let $MyText1 = ‘19980315’
Let $MyText2 = ‘98/03/15’
Let $MyDate1 = StrToDate($MyText1)
Let $MyDate2 = StrToDate($MyText2, ‘yy/mm/dd’)

b. DateToStr() : it converts date to a string
Let $Date_String = DateToStr($MyDate, ‘MonbDD,byy’)

c. DateAdd() : this function adds a specified number of time units to a date
Let $Fifth_Anniv_dt = DateAdd(&Hire_Dte, ‘YEAR’, 5)

d. DateDiff() : used to calculate the difference between 2 dates
Let #Diff_Months = DateDiff(&Effdt, $Calc_date, ‘Month’)

e. DateNow() : this function returns the Current date and time
Let $Date_time= datenow()

Note: the difference between the SQR predefined variable #Current_date & DateNow() functions is
#Current_date variable: contains the date and time when our program started
DateNow(): function returns the current date and time.

4. String Functions:

There are 16 string functions is SQR version 6

a. Instr() :
b. IsBlank() :
c. IsNull() :
d. Length() : tells the number of characters in a string or date
e. Lower() : converts the characters to lower
f. Upper() : converts the lower characters to Upper characters
g. Ltrim() : eliminates the left side spaces
h. Rtrim() : eliminates the right side spaces
i. Lpad() :
j. Rpad() :
k. Replace() :
l. SubStr() :
m. To_Char() : converts the source numeric literal, column, variable or expression to the character format and preserves the precision of the source
n. To_Number() : converts the source string or expression to the numeric format.
o. Translate() :
p. Edit() : this function is similar to move command. Formatting the
source field according to the specified edit mask.
Ex: Let SSN_No = Edit(#SSN, ‘xxxx-xxx-xxxx’)
Let Dformat = Edit(Datenow(), ‘DD/MM/YY’)

5. Miscellaneous functions:

1. Array()
2. Ascii()
3. Asciic()
4. Chr()
5. Cond()
6. Getenv()
7. Nvl()
8. Range()

Key Points

1. The 3 main SQR data elements are Columns, Variables and literals
2. SQR columns are fields defined in the DB. SQR uses the symbol & as the first character of a table column name
3. SQR variables can be either string variables or numeric variables. SQR uses the special character $ for string variables and the special character # for numeric variable.
4. The numeric variables can be integer, floats, or decimals.
5. SQR list variables are ordered collections of SQR variables, literals or column variables.
6. SQR literals are string, date or numeric constants.
7. SQR variables do not have to explicitly declare in the program; when a variable appears for the first time, SQR assigns this variable its initial value.
8. SQR variables can be global or local. Global variables can be referenced throughout the entire program while local variables are effective only within a local procedure.
9. SQR provides you with a number of date element manipulation commands, including arithmetic commands and string manipulation commands.
10. The MOVE command helps you to move data elements from one field to another with optional editing using special edit format masks.
11. The LET command has the most power allowing you the use of sophisticated expressions. As a result, one Let command can replace a number of elementary arithmetic or string commands.

SQR Page

SQR does not print into its output file every time we use the print command. Instead, SQR accumulates the entire page in the memory. The heading and footing sections are generated after the body of the pages has been filled. Once the entire page is complete, it is written to the output file and is erased from the memory just in time to be replaced with the next page.

How SQR process the source program:

SQR goes over the source program in to 2 stages.

1. Compile Stage

· All external source files are inserted into the program source file.
· All compiler directives are evaluated. Compiler directives start with # and are processed during the compile stage.
· All substitution variables are resolved.
· SQR executes all ASK Commands that prompt the user for the values of some substitution variables not code in the program.
· Program memory and work buffers are allocated. All memory arrays are created.
· SQR checks the syntax of the source program.
· SQR determines how to optimize the SQL data access.

2. Execution Stage

· Actual program execution takes place only if SQR finds no compile errors in the source program.
· Starts processing at Begin-Program and stops at End-Program.
· Calculates the size of the report working page.
· Processes the report body
· Processes the report heading
· Processes the report footing
· Writes the entire page to the output ifle and gets ready for the next page.

Usually wont separate these 2 stages, when running the source program, SQR runs through the compile stage and, if no errors are found, immediately switches into the execution stage.

In some cases, we may want to proceed directly to the execution stage, SQR does allow to perform the compile step once, the save the pre-compiled version of the program, to return your report at a later date. If we use pre-compiled version of a program, SQR will not re-execute the required compile steps, including the ASK command. SQR will use all external source files inserted during the compilation stage as well as all substitution variable values assigned.

Key Points

1. SQR page consists of a heading, a body and a footing.
2. SQR accumulates all the report pages lines in the memory and writes the entire page into the output file.
3. SQR processes programs in 2 steps: 1.Compile Step, 2.Execution step.
4. An SQR program may include the Program section, Setup section, Heading section, Footing section and Procedure section. The only required section if the Program Section.
5. The Setup Section determines the overall characteristics of the program. The setup section is processed during the compilation stage.
6. Commands in the Heading and Footing sections are executed every time SQR generates a page.
7. The procedure section is used to break program into manageable and easy to maintain pieces. We can have multiple procedure sections in our program. Each procedure is invoked with the help of the Do command.
8. Procedures can be global or local; by default all procedures are global. To make a procedure local we have to explicitly declare it with the LOCAL keyword.
9. All procedures that have arguments are local
10. To reference a global variable within a local procedure, we have to add an underscore to the variable name after its type character $, # ,&.
11. All SQR reserved variables are global, when referencing SQR reserved variables in a local procedure; we have to add an underscore to their names.

Column Variable

When a column value is selected from a DB SQR automatically creates a column variable for this column. The name of the column variable is the column name prefixed with an ampersand-& symbol. We can use this name to reference the column variable in our program. Ex: &Emplid, &Name.

Begin-Select
Count (*) &EE_count
Print ‘Employee count=’ (1,1)
Print &EE_count
From employment_data
Where hire_dtAnd Empl‑rcd = 0
End-select

Explicit and Implicit printing

Explicit Printing:

We use Print command to print the selected column values. This is called explicit printing.
Ex: Print ‘ Company Name ’ (1,10)

Implicit Printing:

By placing a position parameter immediately after the column name. The printing will perform at the moment of selection.
Ex: Begin-Select
Emplid (+1,1) !implicit printing
Name(,12)
From personal_data
Where state=’NY’
End-Select

The order of both implicit and explicit print commands output is determined by the order in which these commands are coded in the select paragraph. You can also include print edit parameters in Select paragraph. These parameters define the output edit masks for each column. Ex:
Begin-Select
A.Zip (,+2) Edit 99999-999
A.Phone (,+2) Edit xxx/xxx-xxxx
From personal_data
End-Select

Load-Lookup and Lookup

Load-Lookup and Lookup is very useful technique of retrieving information from multiple tables. This method significantly improves the performance, but also simplify table joins.

Load-Lookup: this command creates an internal memory array and populates this array with keys and the corresponding column values from a specified table. This command can be used in either the Setup section or any procedure, but it is important to remember that, if the Load-Lookup command is used in the Setup section, it is processed only once, when used in a procedure, the command is processed every time the procedure gets executed.

Load-Lookup retrieved 2 fields from the DB: 1. Key field 2. Return_value field. The key field may be of the string or numeric format, and it must refer to a column with unique values in the table. No NULL values are allowed in the field. The Return_value field may refer to just one table column or a combination of several table columns. In case of a column combination, the columns must be concatenated. We can have the Load-Lookup command populate its array with only certain records based on some specified selection criteria.

Ex: Begin-Setup
Load-Lookup Name = Company_Name
Rows = 200
Table = Company_tbl
Key = company
Return_value = Descr
Where = country = ‘USA’
End-Setup

In this ex, the Load-Lookup command creates an array named Company_Name and populates the array with the company names from the Descr column of the Company_tbl. When populating array, Load-Lookup uses the SQL where clause to select only table rows with the Country column value equal to ‘USA’. The Rows parameter specifies the initial size of Lookup array as 200 rows. This parameter is optional and, if omitted, a default value of 100 will be used. When an array becomes full, another optional parameter, Extent, will be used to increase this array size. If no Extent is specified, 25% of the initial size will be used as a default value. Note that the only limit of the size of a Lookup table is the amount of memory available in your computer.

Lookup: this command is used to search through internal memory arrays created and populated by the Load-Lookup command. It returns the Return_value field value for each specified key field value. SQR uses the binary search algorithm to search through the array.

Begin-Select Distinct
Emplid (+1,1)
Company
Lookup Company_Name &Company $Comp_Name
!(the lookup command retrieves the $Comp_Name value based on the key stored in &Company)
From Job A
where A.effdt = (select max(effdt) from job
where emplid = A.emplid
and empl_rcd = A.empl_rcd)
and A.effseq = (select max(effseq) from job
where emplid = A.emplid
and empl_rcd = A.empl_rcd
and effdt = A.Effdt)
End-Select

*** we can also build the where clause in the Load-Lookup command dynamically by placing it into a string variable.

Begin-Program
Let $where = ‘A.Plan_type=’’00’’ and’
‘A.effdt = (select max(effdt) from deduction_tbl’
‘where Play_type = A.Play_type and Dedcd=A.Dedcd)’

Load-Lookup name=Ded_codes
Rows=5
Table=”Deduction_tbl a’
Key=Dedcd
Return_value=’DESCR’’,’’DED_PRIORITY’
Where = $Where

End-Program

Key Points

1. The select paragraph starts with Begin-Select and ends with End-Select.
2. SQR commands can be placed in the Select paragraph anywhere between the column names and the From clause.
3. SQR commands in the Select paragraph must be indented.
4. select * not allowed in SQR
5. SQR commands in the Select paragraph are executed for each selected row.
6. SQR column variables are read-only
7. You can use an implicit Print command in the Select paragraph by placing the position qualifiers next to the selected columns.
8. There are many ways to select data from multiple tables. You can use SQL table joins, nested or hierarchical queries, or the Load-Lookup and Lookup commands.
9. Using the Lookup technique for long reports may significantly improve performance.
10. The Load-Lookup command can be used in the Setup section or in any procedure. It is used in conjunction with one or more lookup commands.

DML vs. DDL Statements

Data Manipulation Language: Insert, Update and Delete.

Data Definition Language: Create, Drop, Alter, Grant and Revoke.

Both DDL, DML statements could be used in the SQL paragraph of the setup section. Recommended to keep the DML and DDL statements separate since only DML statements are preprocessed by SQR in the compile stage.

In SQR programs, DDL statements are often used with temporary tables. Temporary tables are handy to hold the intermediate results. If we want to create temporary table, we may code the SQL paragraph in the setup section as follows:

Begin-Setup
Begin-Sql On-Error=warn
Drop table Temp_test_tbl
Create table Temp_test_tbl (emplid char(11), Name char(30))
End-Sql
End-Setup

Key Points

1. We can use native SQL statements in SQR with exception of the SQL Select statement.
2. It is a good idea to use the On-Error clause when updating tables.
3. SQR verifies SQL DML statements at the compile stage, but leaves SQL DDL statements unchecked.
4. The best place to create temporary tables is the Setup Section.

Key Points

1. SQR logical expression can be a combination of 3 major elements: operands, operators and functions.
2. There are 2 types of logical operators: Relational operators and Arithmetic and String Operators.
3. Parentheses in logical expressions help to override operator precedence.
4. Parentheses in complex logical expressions make our expressions more redable and easier to debug.
5. We can use any number of built-in function in a logical expression.
6. The result of a logical expression evaluation in the IF statement is either FALSE (Zero) or TRUE(One)
7. The Evaluate statement is useful when we have a large decision tree, and the decisions depend on a value of the same variables.
8. the Break Command is used to exit from the While or Evaluate Loops.

Print Command

The Print command places data on the page, we must specify the position and length of each output field with the help of 3 parameters (x,y,z)

X - specifies the line position of the page
Y - the column number
Z - the number of positions allocated for the field (length)

Print $Name (+1, 1, 20) ! prints $Name on the next line, position 1, width=20

Print $Name (+2, +3) ! prints $Name 2 lines below the current line starting from current column +3 and use
width = actual length of $Name

Print $Name () ! Print in the current line, current column,
Print $Name(0, 0, 0) ! Print in the current line, current column.

We can also use substitution variables defined in the beginning of program to code all printing parameters.

Formatting output

Print $Name (+1, 1) Bold !Prints the output in Bold

Print $Header (2, 5) Underline !Printer the output in Underlined

Print ‘Test Report’ () Center !Printes the text in Center of the page

Print ‘*’ (1, 1, 25) Fill ! Fills line with 25 Asterisks

Print $Comments (1, 1) wrap 15, 3

! This command prints the value of string variable Comments in 15 Characters per line for a maximum of 3 lines.

Print $Empl_Status (0, 16) Match
A 0 25 ‘Active’
T 0 25 ‘Terminated’
L 0 25 ‘On Leave’

If match is found in Empl_status field then the corresponding substituted value (‘Active’, ‘Terminated’, ‘On Leave’) will be printed in position 25 of the current line. In no match is found, an orginal employee status code will printer in position 16 of the current line.

Edit Masks

There are 3 types of edits
1. Text Edit
· X – Place a character in the output
Move ‘123456789’ to $Zip
Print $Zip (1,1) edit xxxxx-xxxx !output will be 12345-6789

· b – insert a blank in the output
Move ‘3456789’ to $Phone
Print $Phone (1,1) edit (xx)bxxx-xx output->(34) 567-89

· ~ -(tilde) skips a character in the output
Print ‘ABCDEFGH’ (1,1) edit xxxx~xx~ output->ABCDFG

2. Numeric Edit

Let #Unit_Price=10.459
Let #qty=10
Let #total=104.59
Let #credit=-50.00

Print #Unit_Price (1,1) edit 9999.99 output->10.46
Print #qty (1,1) edit 9999.99 output->10.00
Print #total (1,1) edit 9999.99 output->104.59
Print #credit (1,1) edit 9999.99 output->-50.00

Let #Unit_Price=10.459
Let #qty=10
Let #total=104.59
Let #credit=-50.00

Print #Unit_Price (1,1) edit $$$$.99 output->$10.46
Print #qty (1,1) edit 9999 output->10
Print #total (1,1) edit $$$$.99 output->$104.59
Print #credit (1,1) edit $$$$.99 output->$-50.00

3. Date Edit

Position Command

Position command will allow us to set the current position:

Position (10,2) ! sets the current position to line 10, column 2
Print #Amount() ! print amount in the current position
Position (+3,1) ! set the current position 3 lines down
Print #Total () ! Print total in the current position

Position (+1) ! move the print position to the next line

Controlling Vertical Spacing

We can use Next-Listing command to create detail groups in report and to control the vertical spacing between detail groups on a page.

Ex:
Begin-Procedure Select_EE
Begin-Select
Name (1,1,40)
Address1 (2,1,40)
City (3,1,15)
Next-Listing Skiplines=1 Need=3
From personal_data
Where State=’CA’
End-Select
End-Select

Here columns Name, Address1, City make up a detail group that is printed on 3 lines every time a row is selected.

The Next-Listing command moves the current position to 1 line below the current line in the page body, skipping 1 line and starting the next detail group on the next line.

The Need parameter controls the end of the page printing, directing SQR to begin a new page if there are less than 3 lines on the current page left, thus eliminating a potential situation in which a detail group can be broken between 2 different pages.

We can also use the Next-Listing command when we need to create variable length detail groups by using the Wrap qualifier.

Controlling Horizontal spacing

The Columns, Next-Column and Use-Column commands are used to control the horizontal spacing in reports. They define and navigate logical columns on the page.

Columns 10 20 30 !Define 3 Columns in report

This command defined 3 logical columns and made column 10 Current. Now if we use a Print command, it will shift the current column position from the left edge of the page and add 10 to the position qualifier in any Print Command.

Columns 10 20 30
Print $Name (3, 2, 30)

$Name will be printed in the 2nd position of the first logical column. What will be the actual SQR page position? The answer is: 11

In order to advance from 1 column to another, we use the Next-Column command.

Columns 10 20 30
Print $Name (3, 2, 30)
Next-Column
Print #Amount ()

The Next-Column command will move the current column position to the second declared logical column that is the column located at absolute column position 20. variable #Amount will be printed in the first position of the second logical column.

The Use-Column command sets a specific logical column as current or turns off column printing. To stop printing within logical columns simply specify 0(Zero) as a Use-Column parameter

Columns 20 50
Use-Column 2 !the columns command must be issued prior to Use-Column
Print $Last_Name() ! prints Last name in the second column i.e 50
Use-Column 0 !stops printing within columns.

Changing Reposts Heading/Footing:

Alter-Report is used to change a report’s heading or footing sections while the report is running. We can change the size of heading or footing or switch from one heading or footing to another dynamically.

Generating PDF files

SQR can generate Adobe PDF files. We don’t have to change our source program, all we have to do is to use the –PRINTER:PD SQR command line flag. The SQR.INI file has new section called [PDF Fonts] which provides the mapping between SQR fonts and Adobe Fonts. The name of the PDF file will be same as your report name with the .Pdf extension.

Another SQR command line flag, -EH_PDF creates a PDF icon in the navigation bar when we generate HTML output.

Key Points

1. The print command controls the position and the length of each output field with the help of 3 parameters; the 2 coordinates on the page grid (line and column) and the number of positions allocated for the field.
2. Line positions are always specified relative to the current page section (header, body, or footer), not to the entire report page.
3. The position and length parameters in the Print command can be numeric literals or variables.
4. In the Select Paragraph, we can implicitly print a table column by specifying the print position coordinates (x,y) and the space allocation Z to the right of the column name.
5. We can use substitution variables to define all printing parameters at the beginning of report.
6. The Print command format options help to enhance the appearance of report
7. There is 3 types of edit format masks: Text Edit, Numeric Edit, and Date Edit. Each of these 3 edit types uses its own set of special characters for mask coding.
8. Edit masks can be built and changed dynamically by assigning a mask value to a text variable, and then referencing this variable by its name prefixed by a colon.
9. The Position command will allow you to set the current position in the report page.
10. The Next-Listing command is handy when your report has groups of detail records.
11. The Columns, Next-Column, and Use-Column commands are used to control the horizontal spacing.
12. The SQR reserved variable #Current-Line always holds the actual line number within the page body.
13. The Print Delay command allows you to go back and change the value of an output variable before printing.
14. We can create PDF output without changing our program.
15. We can change reports heading or footing dynamically with the help of the Alter-Report Command.

On-Break option of the Print Command

When a value of a column is changed, it is common to do some special processing, we may need to skip lines, print value only if changed, execute a special procedure before or after the break, print sub-totals etc. SQR break handling commands allow we to do all of the above.

The On-Break option causes the specified action to take place when the value of a certain output field is changed. It can be used for both explicit and implicit printing. We can specify a number of qualifiers of the On-Break Option. These qualifiers define specific actions to be taken when the break occurs. The most popular qualifier is Print (not be confused with Print command). The Print qualifier is the default qualifier of the On-Break option; we don’t have to specify this qualifier when coding the On-Break option.

When using the Print qualifier of the On-Break option, we can specify when the break field should be printed:

Print=

Always
The break field will be printed for each detail group.
Change
The break field will be printed only when its values is changed. This is the default option.
Change/Top-Page
The break field will be printed when its value is changed plus at the top of each new page.
Never
The break field will not be printed.
In some cases, we may choose not to print a break column value when a break occurs.

Begin-Select
A.Company (,1,7) On-Break Print=Change/Top-Page Skiplines=1
End-Select

Skiplines qualifier specifies how many lines to skip when the break occurs.

When dealing with nested On-Breaks, use the Level qualifier to ensure that the breaks are properly nested and to control the order in which break procedures are called (if any). The parameter in the Level qualifier specifies the level of the break for reports containing multiple breaks. When coding the value of this parameter, we should number breaks in the same order as that in the Order By clause.

Begin-Select
A.Company (,1,7) On-Break Level=1 Print=Change/Top-Page Skiplines=1
A.Paygroup (,+2,8) On-Break Level=2
End-Select

On-Break can only be used on String and Date Column and variables. We cannot explicitly use the On-Break option directly on a numeric column or variables. To fool the system, move the numeric column value to a string variable, then code the On-Break option in the Print command for this new string variable

Using procedures when Break occurs

When we use the On-Break option of the Print command, we can have SQR automatically call procedures before and after each break occurs. In order to accomplish this, use the Before and After qualifiers specify the names of the procedures to be called.

The Before qualifier will automatically call the specified procedure right before the column value is changed, including the processing of the first selected row.

The After qualifier will automatically call the specified procedure after the column value is changed including the last change, that is, when select is completed.

***If no rows are selected, neither procedure will be called.

Note: the Before and After qualifiers can be used only within Select Paragraph.

Begin-Select
A.Company (,1,7) On-Break Before=Company_Name After=Company_totals Skiplines=1
End-Select

Using the SAVE qualifier of the On-Break Option

Sometimes it is necessary to use or print the previous break value of a print variable in the After procedure. Since After procedures are executed after the values of the On-Break column have been changed, the columns will already hold their new values. The problem can be solved by using the SAVE qualifier of the On-break option. This method allows storing the previous column value in a specially designated variable when a break on this column value occurs.
Ex:
Begin-Select
A.Company (,1,7) On-Break Level=1 After=Company_totals Skiplines=1 Save=$Prev_comp
End-Select

Key Points

1. We can greatly enhance reports by using the On-Break option of the Print Command.
2. We can write special procedures to be invoked before and after column breaks. The Before and After qualifiers tell SQR when to call these procedures.
3. The Level qualifier of the On-Break option is used to arrange multiple breaks in hierarchy, and to specify the sequence of events.
4. Use the Skiplines qualifier to insert the necessary number of lines between detail groups.
5. When a break occurs, the previous column value is overridden with the new one. Use the Save qualifier to save a previously selected value to be used in the After procedure.
6. On-Break can only be used on String and Date Columns and variables.
7. In the beginning of a query, all Before procedures are process in ascending Level order when the first row of the query is fetched.
8. All After procedures are processed in descending Level order from the highest level to the current break level of the field where the break occurred.
9. All Before procedures are processed in ascending Level order from the current break level to the highest break level.
10. The Procedure qualifier can not be used in a combination with either Before or After qualifiers.
11. After the query execution (at End-Select) all After procedures are processed once more in descending Level order.
12. If a query returns no rows, neither Before nor After procedures are invoked.

SQR Run-Time & Compile-Time Variables:

There are 3 types of variables are used to make SQR reports flexible

1. SQR Bind Variables - Run-time variables
2. Substitution Variables - Compile-time variables
3. Dynamic Query Variables-

1. SQR Bind Variables/Run-time variables:

SQR Bind variables are used when a query includes parameters defined outside of the query. These parameters may come from user input, another query, or procedure.

SQR Bind variables are run-time variables. Their values are assigned and changed during the program execution stage. When we use a bind variable in an SQL statement, SQR ‘binds’ the variable before the SQL is executed.

Bind variables can also used to link 1 query to another. One typical situation occurs when we use nested queries.

2. Substitution Variables/Compile-time variables:

Substitution variables are used to specify SQR elements in several program locations at compile time. The values of these variables are set during the compilation stage and checked for syntax errors before the execution begins. They cannot be changed during the program execution.

The #Define statement and Ask Command present 2 ways to define substitution variables and to assign values to these variables.

Please note that we define these variables only once, but use them in more than 1 place in the program. When referenced in program, these variables must be enclosed in braces ‘{}’

SQR supports nested #Include statements; one include file may have a #Include statement referencing another include file, allowing up to 4 levels of nesting.

Another way to utilize substitution variables is Ask Command. The Ask command always coded in the Setup Section prompts the user for the value, which is placed into a substitution variable. With Input command, we can validate user input and re-prompt if it is not valid, something we cannot do with the Ask command.

The Ask command should not be confused with the Input Command, another user dialog command. The Input command is processed at execution time; whereas the Ask command is process at compile time.

The Ask command is used only in Setup Section. Ask commands have to be issued prior to any use of their substitution variables.

3. Dynamic Query variables/Dynamic SQL variables:

Dynamic query variables are used to build dynamic SQL Code. Sometimes called Dynamic SQL variables, or simply Dynamic variables, dynamic query variables are text variables whose values are used as parts of SQL statements. When referenced in SQL statements their names must be enclosed in square brackets ex:[$by]

We can use dynamic query variables to substitute columns and other parts of SQL statements or to dynamically change the Where clause or an entire SQL Statement. The important thing to remember is that when we use dynamic variables in our SQL Statement, SQR cannot check syntax at compile time as it usually does. Run-time errors will occur if a dynamic variable or the Where clause syntax are incorrect.

Key Points

1. The 3 special types of variables that increase flexibility of SQR programs are: Bind Variables, Substitution Variables, Dynamic Query variables.
2. Bind variables and Dynamic query variables are Run-Time variables.
3. Substitution variables are Compile-time variables: their values can’t be changed at runtime.
4. Bind variables are used in SQL statements in the SQL or Select Paragraphs.
5. Bind variables can be used in correlated Select statements to link one query to another.
6. Substitution variables can be used to alter any part of SQR programs.
7. Substitution variable values are set at compile time by either the #Define compile directive or the Ask Command.
8. The Ask command can be used only in the Setup section.
9. When referenced in a program, substitution variable names must be enclosed into braces ex: {Subst_var}
10. A change to just one substitution variable may cause multiple changes in the program.
11. Dynamic query variables are variables whose values are used as parts of SQL Statements.
12. When referencing dynamic query variables we should enclose their names in square brackets ex:[dynamic_var]
13. Dynamic query variables can be used to substitute any part of an SQL statement.
14. When dynamic query variables are used to generate SQL statements, SQR cannot check the SQL syntax at compile time.
15. When using dynamic query variables to generate SQL statements, it is very important to code error-handling routines.

SQR Arrays

An Array is a collection of similar groups of fields in the program memory, wherein its relative position number in the array can refer to each group.

An SQR Array is a memory structure that consists of rows and fields. When no arrays are used, in most cases, an SQR program processes its input records one by one. After a record is processed, it is overlaid with the next record, making the first record’s contents inaccessible. Because it is sometimes necessary to process all or some records at once, SQR Arrays can serve as buffers where the data can be temporarily stored. Often times, it is not only a convenient method of temporarily storing information, but also a good way of developing effective program.

Arrays are temporary structures created in the program memory. SQR has a specific set of commands used to create and populate arrays and to access information stored in arrays.

· Create-Array (to create arrays)
· Clear-Array (Initializing arrays)
· Get (retrieve data from arrays)
· Put (place data into arrays)
· Array-Add (
· Array-Subtract
· Array-Multiply
· Array-Divide

· Create-Array

In SQR arrays are created during the compile stage, before the program is executed. The Create-Array command is used to create array.

Create-Array Name=Dependents Size=25
Field=Dependent_Id:Char
Field=Dependent_Name:Char
Field=Dependent_Birthdate:Date
Field=Replationship:Char

In the example we have created an array named Dependents with a size of 25 Rows. The size in the array command specifies the max number of rows in the array. If any array subscript value exceeds the maximum array size, SQR aborts the program execution and displays the following message:

(SQR 1500) Array element out of range (25) for array ‘Dependents’ on line 28
SQRW: Program Aborting.

All SQR array are create before the program is executed. While the Create-Array command can be used in any section of the program, it is a good practice to place in a Setup-Section. The Create-Array command just allocates and initializes memory for an array.

When array is created its fields are automatically initiated to their default values based on the following rules

All Numeric fields are set to ZERO.
All String fields are set to NULL.
All Date fields are set to NULL.

Multiple-Occurrence fields:

Create-Array Name=Clients Szie=100
Field=Name:Char
Field=Phone:Char:3

In the above array each row includes the Name field and the 3-occurrence field Phone. Once the array is dynamically created, specifying the field name can reference any array field, the field row number starting from number Zero. In Clients array, the rows are numbered from 0-99, and the field Phone occurrences are numbered from 0-2.

· Clear-Array
When an array is created all its field values are automatically initialized. Sometimes we need to re-initialize an array after some or all of its fields have been populated. It is a good practice to always reinitialize arras after its use. The Clear-Array command resets each field of an array to its initial value. Field initial values are specified in the Create-Array command. If no initials value was specified for a field, the field is et t its default value.

Clear-Array Name=Dependents

· Get
The Get command is used to retrieve data from arrays. We can retrieve the entire array’s fields at once using the one Get command.

Get $Dep_Id from Dependents(10)
Get $Dep_Id $Dep_Name from Dependents(#i)
Get $Client_Name $Client_Phone from Clients(10) Name Phone(#j)

· Put
The Put command is used to placing data into arrays. There are 2 methods of placing data into an array. We can reference the array elements in the Let command or by using the Put command.

Let Dependents.Dependent_Id(10) = ‘01’
Let Dependents.Dependent_Id(#Row_Num) = &A.Dependent_Benef
Let Clients.Phone(#i,#j) = &A.Phone

Put ‘01’ into Dependents(5)
Put ‘01’ ‘Smith,Maria’ into Dependents(#i)
Put &Name $Phone into Clients(#Row_Num) Name Phone(#sub_Num)

We can perform addition, subtraction, division and multiplication operations on elements of an array by using the Let command or of the following array specific commands.

Let Employees.Salary(10) = Employees.Salary(10) + #Bonus

· Array-Add

Array-Add #count to Clients(#i) Count

· Array-Subtract

Array-Subtract 1 from Books(500) Books_received

· Array-Multiply

Array-Multiply #Incr1 #Incr2 times Empl(1) Rate(0) Rate(1)

· Array-Divide

Array-Divide 5 into Empl(#m)

Searching Data in Arrays

SQR does not have special commands to carry out array searches; we have to program this logic. There are many popular algorithms that can be used to search in arrays. Some of them are very simple to implement, while others may require some effort.

Here we will discuss 2 algorithms: Sequential search and Binary search.

Sequential Search Algorithm: when looking for an element with a particular value in a certain field, our program has to go over every single array element until it hits the right one. Not a very efficient method of searching, but very simple to program.

Binary Search Algorithm: it is more efficient than the sequential search, but it has very important limitations; it works only with pre-sorted arrays. The main idea of the binary search algorithm is very simple; it compares the target value to the value of the search field of the middle element of the arrays current search area. The search starts when the current search area I equal to the entire array. The search fields in the array are ordered in a predefined sequence, therefore, if the target value is less than the value in middle element, the search focuses on the lower half of the array, otherwise the search concentrates on the upper half. The remaining half of the array becomes the current search are. The next step is to compare the target value to the middle element of the current search area. This process of dividing the current search is in 2 continues until the element is found or the current search area is empty.

Key Points

1. The Create-Array command creates an array of fields in the memory.
2. The Create-Array command can be used in any section of SQR program.
3. We can specify an initial value for each field in an array.
4. Some fields in an array can be defined as multiple-occurrence fields.
5. Row number and field occurrence numbers in arrays start from Zero.
6. Row numbers and field occurrence numbers can be numeric literals or numeric variables.
7. When using variables to refer to row numbers or occurrence numbers, be sure to keep the variable values within the boundaries defined in the Create-Array command.
8. All SQR arrays are created during the program compile stage
9. All fields used in arithmetic operations must be declared as number, decimal, float or integer
10. The Get command is used to retrieve one or more fields from an array.
11. The Put command is used to move one or more fields into an array.
12. The Clear-Array command is used to initialize an array.
13. The following commands can be used when working with arrays:
Create-Array
Clear-Array
Get
Put
Array-Add
Array-Subtract
Array-Multiply
Array-Divide
Let

Defining Multiple Reports in SQR

In a multiple-report SQR program, each report must be defined with a separates Declare-Report command, this command is coded in the Setup Section.

In the Declare-Report command, specify the following attributes

· Name of the report
· Name of the Layout to be used for the report
· Type of the printer to be used for the report

Depending on the specific business requirements, each report can use its own Layout or printer definition, or some reports can share same Layout or Printer definitions. If no Printer-Type is specified, the default is LINEPRINTER for the report. If no Layout is specified in the Declare-Report command, the Default Layout is used.

Declare-Report My_Report1
Layout=My_Layout1
Printer-Type=PS
End-Declare

Valid values for the Printer-Type argument in the Declare-Report command are HTML(HT), HPLASERJET(HP), POSTSCRIPT(PS) and LINEPRINTER(LP).

The Declare-Layout command is also coded in the Setup Section. This command allows specifying the attributes of the layout for our output file and defining as many layouts as we need for our application.

If we don’t code the Declare-Layout in our SQR program, a default layout with the name Default will be created. If we need to override its attributes, we can define a layout called Default in our program and list only the attributes we need to change. If we are defining multiple layouts in our program, each layout name must be unique.

Begin-Setup

Declare-Layout Layout1
Left-margin=1
Right-margin=1
End-Declare

Declare-Layout Layout2
Left-margin=5
Right-margin=5
End-Declare

Declare-Report Rpt1
Layout=Layout1
Printer-Type=HP
End-Declare

Declare-Report Rpt2
Layout=Layout2
Printer-Type=PS
End-Declare

End-Setup

If we need to override some of the default printer attributes, use the Declare-Printer command. This command is also coded in setup section.

The Declare-Printer command allows us to create our own customized printer type and assign it a name. In order to make a Declare-Printer command applicable to a specific report, we have to use For-Reports parameter with a specific report name defined in a Declare-Report Command. If the For-Reports parameter is not coded, the printer type attributes specified in the Declare-Printer command will apply to all reports that use the printer type specified in the Type argument of this Declare-printer command.

Begin-Setup

Declare-Printer HP1
For-Reports = Report1
Type=HP
Font=4
End-Declare

Declare-Printer PS1
For-Reports = Report2
Type=PS
Font=5
End-Declare

End-Setup

We can specify the printer type in the SQR command line –Printer:xx flag. this will cause the SQR program to produce all our report output files for the specified printer type. For ex: -Pinter:HP to produce all output for HP LasetJet printer.

Handling Multiple-Reports in one Program

If our program generates multiple reports, the Print commands need to know which report to direct their output to. The Use-Report command will allow our program to switch between reports. When this command is used, all Print commands issued after the Use-Report command will write the output to the specified report file, until the next Use-Report command is issued. See the following ex

Use-Report Empl_detail
Print ‘Emplid’ (1,1)
Use-Report Empl_Summary
Print ‘Total Employees:’ (+1,1)
Print #Total_recs (,1)

The headers and footers are printed using a different approach. The same header or footer can be shared by multiple reports. In real business life, however, different reports usually have different information in their headers and, sometimes, footers. In these cases, we should code separate Heading and footing section for each report and use the For-Report parameter to assign each section to a specific report, for example

Begin-Heading 4 For-Reports=(Empl_Detail)
Print ‘ Employee Detail Report ‘ (1) Center
Print ‘….’
End-Heading

Begin-Heading 2 For-Reports=(Empl_Summary)
Print ‘ Employee Summary Report ‘ (1) Center
Print ‘….’
End-Heading

Begin-Footing 1 For-Reports=(Empl_Detail)
Print ‘ End of Employee Detail Report ‘ (1) Center
End-Footing

Output Files in SQR Program with Multiple-Reports

If we use 2 multiple reports in our SQR program, it will generate 2 report files; for example if the file name is Test1a.sqr then the output report files will be Test1a.lis and Test1a.l01. The Test1a.lis will be assigned to the first Declare-Report command, and the Test1a.l01 will be assigned to the next report declared by the Declare-Report Command.
By default SQR creates output report files with the same names as our program name, but with different extensions. For single report programs, the extension is usually .lis or .spf.

For multiple reports, the report file names are still the same as the program name, but the file extensions will be .lis, l01, l02 and so on. In case of .spf output files, the names will be same as the program name with the extensions: .spf, .s01, .s02 and so on.

If the Environment variable OUTPUT-FILE-MODE=LONG in the Default settings of the SQR.Ini file, the report file names will be like

Test1a.spf, Test1a_01.spf, Test1a_02.spf
Test1a.lis, Test1a_01.lis, Test1a_02.lis.

By default the OUTPUT-FILE-MODE=SHORT.

We can use the –F flag in the SQR command line to override the default file names with our own file output names. For example, if we want the Test1a.sqr program to generate the Empl.lis and Depd.lis report files, the following –F flags:

-FEmpl.lis –Fdepd.lis

Similarly, we can use the –F flag to create multiple .spf files. Keep in mind, however, that we must still specify the .lis extensions for these reports. SQR will automatically rename the files to Empl.spf and Depd.s01.

Key Points

1. In multiple report programs, each report must be defined with a separate Declare-Report command.
2. The Declare-Report and Declare-Layout commands must be coded in the Setup Section.
3. Each report can use its own layout or printer type definitions; some reports can share the same layout and type definitions. If no layout is specified, the Default layout is used.
4. If we define multiple layouts in our program, each layout name must be unique.
5. The Use-Report command allows our program to switch between reports.
6. SQR allows us to specify the Heading and Footing sections for each report using the For-Report argument in the Begin-Heading and Begin-Footing commands.
7. When creating multiple output files, the default names of the report files will be the program name with the extensions .lis, l01, l02 and so on. In case of the SPF output files, the file extension will be: .spf, .s01, .s02 and so on.

Creating SQR Portable Files: SPF Files

SQR allows creating SQR Portable File (SPF). The main idea of SPF file is that, with their help, our program can create printer-independent output files. These files can be viewed and printed by SQR Viewer, or converted back to LIS files with the help of SQR Printer. End users can use SPF files to view, e-mail, fax, or print them on any available printer device.

SPF output files can be produced by SQR on any platform. We don’t have to change our program to make if generate SPF output. All we need to do is to use one of the following flags in the SQR command line or the SQR Dialog Box:

· -NOLIS will create SPF file output instead of LIS output file.

· -KEEP will allow creating SPF output in addition to LIS output.

· -ZIV will invoke the SQR viewer after creating an SPF file. This flag automatically invokes the –KEEP flag to create an SPF file. If a program creates multiple output files, the SQR viewer will automatically display only the first report file. The other SPF files can be viewed or printed by using the SQR Viewers file selection option.

SQR generates SPF output even when it is not asked to do so. When SQR needs to accumulate its output before creating the final reports it uses SPF files as intermediate output file storage. Another example occurs when the Last-Page command is used in a program, this command causes SQR to accumulate the entire report without printing it until the last page is processed. In these and other cases, SQR automatically creates SPF files and deletes them upon the programs completion.

SPF files allows generating a report and storing the produced SPF output for subsequent electronic distribution or on-site printing. We do not have to know the type of printer belonging to each potential recipient.

SPF files also permit to view, search, or email the text portions of a graphic output by rendering a line-printer output. We can run a report on a UNIX server and then print the output on a PC using Windows.

Using SQR Viewer

The SQR Viewer allows viewing our report online before printing. We can also take advantage of the Viewer’s Print Preview, Print Setup, Find and Zoom options as well as the capability to email the report to users.

To view a report thought the SQR Viewer, the report must be in the SPF format. These files normally have .SPF or .SNN extensions.

We use the –NOLIS command line flag to create SPF output. This option will make the program generate: Test1a.spf and Test1a.s01
(If the OUTPUT-FILE-MODE environmental variable in the SQR.ini file is set to LONG, the SPF file names will be Test1a.spf and Test1a_01.spf)

Another convenient way of using the SQR Viewer is to run the program with –ZIV command line flag. When we use this flag, SQR creates SPF output, automatically invokes the SQR Viewer, and opens the first SPF file created in the program.

It is important to keep in mind that while SPF files can be created on any platform, the SQR Viewer can be used only on Windows. What if our program ran on a non-windows platform, but users need to view the SPF file generated by the program? The simplest way to solve the problem is to use a file transfer tool (such as FTP) to transfer the files to the windows environment.

Key Points

1. SQR portable files (SPF files) are printer-independent and offer a higher degree of flexibility for both program developers and end users.
2. We don’t have to change our program to make it generate SPF files; it can be achieved by using the proper SQR command line flags.
3. SPF files can be generated on any platform.
4. The SQR viewer allows to view, print and email SPF files. This product works in the Windows environment only.
5. The SQR Print converts SPF files to printer-specific files. This is a multiple-platform product.

Document Markers

Another method of placing the values of variables or columns into Document involves using document markers. A document marker is a special variable whose name begins with @. It marks a location in the document where we place data from areas external to the Document Paragraph parts of the program. Document markers defined in Document Paragraph can be referenced in the Position command outside the document paragraph to establish the next printing position. (SQR command cannot be used inside Document paragraph.)

Begin-Program
Do Print_Doc
Do Print_Bold
End-Program

Begin-Procedure Print_Doc
Begin-Document (1,1)
Dear Colleague:
.b
@Start_Bold
.b
@Start_Underline
.b
End-Document
End-Procedure

Begin-Procedure Print_Bold
Position () @Start_Bold
Print ‘ Hai this is Bold text…’ () Bold

Position () @Start_Underline
Print ‘ This is underline text…’ () Underline
End-Procedure

Key Points

1. Use the Document paragraph to create form Letters.
2. The Begin-Document and End-Document commands are used to define Document paragraph.
3. The Document Paragraph may be used inside any procedure. There may be multiple Document paragraphs within a program.
4. Do not use tabs inside a Document paragraph. Use spaces instead. For a full line spaces specify .b
5. SQR commands are not allowed within the Document paragraph.
6. A printed Document paragraph must fit into a single report page.
7. We can use document markers to designate special locations in the Document paragraph where we can place data from external to the paragraph parts of the program. The Position and Print commands are used to move data to the Document paragraph from outside the paragraph.
8. We must generate a document before placing information at the locations marked by the document markers.
9. We can separate our letter into variable length sections to achieve greater flexibility.

Working with Flat Files

Files in SQR: Regular sequential files are often called Flat Files. SQR supports all necessary input/output operation on flat files, including opening and closing files and reading from and writing to the files. Please note that SQR does not provide random access input/output commands; all records are processed sequentially.

We can use sequential/Flat Files to

· Import data from other systems into our DB tables.
· Unload data from our DB tables into Flat Files, and use the files as interfaces to external systems.
· Sort our data using the operating system commands or utilities.

Flat files created in SQR are frequently used as Interfaces between PeopleSoft applications and other Systems. They are also used for generating comma-delimited files compatible with Excel Spreadsheet format.

Opening a File in SQR

Before accessing a file in SQR, we have to open it. A file can be opened as an input file for reading from an existing file, or an output file for writing to a new file or appending data to an existing file.

The maximum number of open files at any time is 256. If our program includes more than 256, close the files we do not need at that moment.

When a file is opened for reading, the record length specified in the Open Command must be equal to or greater than the length of the longest record used in the file. The maximum record length is (32K –1) bytes (32767)

When a file is opened for writing, a new file is created unless the For-Append parameter is specified. If the same file already exists and no append mode is specified, the existing file will be overwritten.

Open ‘My_file1’ as 1 For-Reading Record=80:Vary

Open ‘c:\temp\My_file2’ as 2 For-Reading Record=80:Fixed

Open $File3 as 3 For-Reading

Open $File2 as #k For-Writing Record=100

Open $File3 as 4 For-Append Record=70

Open &A.File_Name as 5 For-Reading Record=120:Fixed_Nolf

Let $File4=’C:\temp\abc.dat’
Open $File4 For-Reading Record=120:Vary Status=#File-Status

The number for file handling is a positive integer from 1 to 64,000.

Vary : should not contain binary data, record length does not include the line terminator character. It is applied by default if no type is specified.

Fixed : all the records should have the fixed length.

Fixed_Nolf : indicates with no line terminator at the end of the record. This is useful when writing or reading binary data.

Status : if status is not Zero, it means that file was not found

Closing a File in SQR

SQR files stay opened until the Close Command is used or program successfully terminates.

Close 1
Close #k

The close command uses only one argument, the file number, which is the number assigned to the file in the corresponding Open Command.

Reading from a File

SQR will allow reading an input files only if the file was opened For-Reading. The Read command reads the next record from the file into the variable or variables specified in the command.

Read Into:

Read 1 Into $Input_Record:80

In this example, it reads the whole record into a single variable $Input_Rcord. This gives us flexibility and, with the help of string functions, we can easily extract the needed fields, or parse the record by a field separator. Here are some parsing examples:

Unstring $Input_Record By ‘,’ Into $Last_Name $First_Name $Address

Extract $First_Name from $Input_Record 0 20

Let $Last_Name=Substr($Input_Record,1,20)

Read 1 Into $Last_Name:20 $First_Name:10 $Address:50

Detecting End of File:

SQR internal variable #End-File is set to 1 when there are no more records to read. We should check this condition after each record is read.

Begin-Procedure Read-File

While 1
Read 1 Into $Record:80
If #End-File=1
Break
End-If
End-While

End-Procedure

Verifying Read Status:

SQR returns Zero if the read is successful, otherwise a system-dependent error number is returned.

Begin-Procedure Read-File

While Not #End-File
Read 1 Into $Record:80 Status=#Read_Stat
If #End-File
Break
End-If
End-While

End-Procedure

Reading Text Data

When reading text data we should specify the variable name and number of bytes we want the program to read. Keep in mind that the trailing blanks are omitted when the record is read.

The total length specified for all our read variables must be less than or equal to the length of the entire record read.

Reading Binary Data

In order to read binary data, the file must be opened as a file with record type equal to Fixed or Fixed_Nolf. Binary fields can be 1, 2 or 4 bytes in length. When reading binary numbers, they must be placed into numeric variables. Binary numbers hold only integers. If we need to maintain a decimal portion of a number, convert the number to a string variable.

The following is the example for reading binary numbers.

Read 1 Into #Amount:2 #Hours:1

Reading Date Fields

The date field was written to a file in the SQR date variable format, we could read this field to either a date or string variable. The date variable must be in one of the following formats:

The format specified by the SQR_DB_DATE_FORMAT environment variable.
DB specific format
DB independent format ‘SYYYYMMDD[HH24[SS[NNNNNN]]]]’.

Writing to a File:

SQR will allow writing a record to a file only if the file was opened For-Writing or For-Append. The Write Command writes a record to a file from the variable or variables specified in this command.

When using SQR Write command, we can write to a file from a single literal, variable, or column or from a list of literals, variables, or columns. The command operands are very similar to those of the Read Command. We have to specify the file number, the source fields, the length of the source fields, and a variable to hold the Write command status. Only the file number and source field names are required parameters. The source field and the status variable are optional. See the following example;

Write 1 From $Record:80 Status=#Write_Status

Write #H From $Record_2

Write 2 From $Lst_Name:20 ‘,’ $First_Name:10 ‘,’ $Address:50

Write 3 From #Amount:2 #Number:1

Write 4 From $Date:18

Different techniques for creating Flat File output

One of the most popular uses of flat files is downloading information from a database into a flat file. The file created can later be used by other applications outside the SQR environment. We can use one of the 2 major approaches to accomplish this task:

1. The Write Command
2. The Print and New-Page command

Using the Write command is a more traditional way of creating flat files. It is also a more flexible method. It involves the following steps

1. Open a file For-Output or For-Append
2. Select the required data from DB tables in the Begin-Select Paragraph
3. Perform necessary data conversions and manipulations.
4. Use the Write command to place data into the file.
5. Repeat steps 2, 3 and 4 until the end of selection.
6. Close the output file.

Using Print Command to Create a Flat File

Using the Write command to create an output file is traditional way of creating Flat File. Many programming languages use very similar approaches. SQR allows us to employ another technique of writing data to an output file: using the Print Command. Keep in mind, however, that this method is not as flexible as using the Write command, it has a number of limitations

To use Print command to output data to a file:

1. Specify Max-Lines=1 in the Declare-Layout Command.
2. Define the Width in the Declare-Layout command equal to record size (the size of our output record)
3. Specify Formfeed=No in the Declare-Layout command to make sure that SQR will not output form-feed characters.
4. Use explicit length in our Print Command, thus placing each field at a fixed location in the record.
5. Use the New-Page command after each record is processed.

Using Flat Files to import data into DataBase

Another frequent task involves uploading data from a flat file into a Database. We can develop a simple program that reads the file and inserts the information into our database. Some business needs require the designing of more complex programs that not only read and insert, but also verify the information read from the file, and populate multiple tables at once. Our SQR program can do many things, including the following

· Read records from the input file
· Edit input fields using SQR functions, table lookups and queries
· Build logic to insert data into different tables based on the specified criteria
· Check for duplicates
· Print a audit report

Example of interface:

!*************************************
Begin-Setup
!*************************************

Begin-SQL On-Error=Stop
Create Table Temp_Job_Tbl
(
Emplid Varchar2 (11) Not Null,
Empl_Rcd Number Not Null,
Effdt Date Not Null,
Effseq Number Not Null,
Deptid Varchar2 (6) Not Null,
Jobcode Varchar2 (6) Not Null,

Empl_Status Varchar2 (1) Not Null,
Annual_Rt Number (18,3) Not Null
Monthly_Rt Number (18,3) Not Null,
Hourly_Rt Number (18,6) Not Null
)
End-SQL

End-Setup

!*************************************
Begin-Program
!**************************************

Open ‘C:\temp\job.dat’ as 1 For-Reading Record=3000 Status=#File-Status
If #File-Status !=0
Show ‘Error opening input file’
Else
Do Read_Input_File
End-If

End-Program

!*************************************
Begin-Procedure Read_Input_File
!*************************************

Display ‘ inserting records from file’
Move 0 to #Inserts
Move 0 to #Tot-Recs

While Not #End-File
Read 1 Into $Input:1000
If #End-File
Break
End-If

Unstring $Input By ‘ ‘ Into
$Emplid –
$Empl_Rcd –
$Effdt –
$Effseq –
$Deptid –
$Deptid –
$Jobcode –
$Empl_Status –
$Annual_Rt –
$Monthly_Rt –
$Hourly_Rt

Do Insert_Temp_Job

End-While

Close 1
Display ‘ Total records inserted:’
Display #Tot-Recs 999,999,999
End-Procedure

!*************************************
Begin-Procedure Insert_Temp_Job
!*************************************
Begin-SQL On-Error=Insert_Error

Insert Into Temp_Job_Tbl
(
Emplid,
Empl_Rcd,
Effdt,
Effseq,
Deptid,
Deptid,
Jobcode,
Empl_Status,
Annual_Rt,
Monthly_Rt,
Hourly_Rt)
Values
(
$Emplid
$Empl_Rcd
$Effdt
$Effseq
$Deptid
$Deptid
$Jobcode
$Empl_Status
$Annual_Rt
$Monthly_Rt
$Hourly_Rt )
End-SQL

If #sql-count=1
Add 1 to #Inserts
If #Inserts >= 500
Commit
Move 0 to #Inserts
End-If
Add 1 to #Tot_Recs
End-If

End-Procedure

!*************************************
Begin-Procedure Insert_Error
!*************************************
If #sql-status = -9
Show ‘ insert error: duplicate row is not allowed for emplid=’ $Emplid
Else
Show ‘Insert Error:’ $sql-error
Show ‘Error No: ‘ #sql-status
Stop
End-If

End-Procedure

Key Points

1. SQR provides the following Input/Output commands: Open, Read, Write and Close.
2. Before accessing a file, it must be opened. If we are reading a file it should be opened For-Reading. If we are writing to a file, it should be opened For-Writing.
3. The open For-Append parameter is used when we need to append the records to an existing file. If a file does not exist, it will be created.
4. The record type may be defined as Fixed, Vary, or Fixed_Nolf(fixed with no line terminator). The Fixed_Nolf type is often used for accessing binary data.
5. When reading from or writing to a file, we refer to the file by the file number rather than by the file name. File names are specified only in the Open command.
6. SQR internal variables #End-File is set to 1 when there are no more records to read.
7. When reading text data, the trailing blanks are omitted.
8. In order to read binary data, the file must be opened as Fixed or Fixed_Nolf.
9. We cannot read or write NULL values from or to an ASCII file
10. The Print and New-Page commands can be used to create Flat Files with fixed length record.
11. We can use the operating system commands to sort files.
12. Files are closed either by Close command, or upon successful termination of our program.

SQR Program Execution

SQR program can be invoked in a number of ways.

We can run an SQR program from the

1. SQR Dialog Box in the windows environment.
2. Command Line
3. PeopleSoft Process-Scheduler

Executing SQR Program from the Command Line

When executing an SQR program from the operating system Command Line, we need to enter

· The name of the SQR engine (SQRW in Windows, SQR in Unix, etc)
· Our program name
· The database connectivity string
· SQR command line flags
· Application specific arguments

Since .sqr is the default extension for all SQR source programs, we don’t have to specify it, unless our program has another extension.

The database connectivity string contains important information needed to connect our program to the DB, including User Id and Password. The exact format of this string is database-specific.

Sqrw test01.sqr sqrbook/passwd –Fc:\temp\ -windows

Sqrw test01.sqr sqrbook/passwd –Fc:\temp\mysqr.lis -Windows

Sqr test01.sqr sqrbook/passwd –F/home/output -Unix

Sqrw test18a.sqr test/pswrd @arg1.dat !argument file is arg1.dat

Executing Pre-Compiled SQR Program/.SQT Program

In order to compile an SQR program without executing, use the –RS SQR command line flag which allows us to create a pre-compiled version of our program. Pre-compiled programs are usually assigned the extension of .SQT and are sometimes called SQT programs.

After an SQT program is created, we can execute it at any time with the help of either the –RT SQR command line flag or via the SQR Execute (SQRWT for windows, SQRT for Unix) program. The SQR Execute program performs only program execution, it allows us to specify most command line flags similar to the flags we specify when executing our program in 2 stages.

The following example shows how to pre-compile and then execute and SQR program.

Sqrw test01.sqr sqrbook/passwd –RS !compile test01.sqr
Sqrw test01.sqt sqrbook/passwd –RT –Fc:\temp\ !execute testo1.sqt

All the commands in Setup section are processed at compile time. This means that when a pre-compiled program is executed, all Ask commands will be ignored. We will have to use Input command, instead of Ask command to prompt for user input at run time.

Executing SQR Programs in Batch Mode

SQR programs can be executed in batch mode under MS-DOS, Windows, UNIX or MVS. For example an SQR command line can be included into a batch file or a Unix shell Script. PeopleSoft users are, probably, familiar with a special script name PRCS_SQR. This script wad developed by PeopleSoft to run PeopleSoft SQR programs in the Unix Environment. It takes all user parameters from PeopleSoft on-line panels with the help of the PeopleSoft Process Scheduler, and dynamically builds the regular SQR command line.

********* The Call System SQR command is the Operating system command.

Call System Using ‘cp /usr/tmp/file1.dat /usr/tmp/file2.dat’ #Status

Key Points

1. We can execute an SQR program from a Command Line, start this program from the SQRW Dialog Box in the windows environment, or call the program from other program.
2. SQR allows us to changes its default behavior by using its command line flags. SQR command line flags always start with a dash (-). Some flags may have additional arguments. In this case, the arguments must follow their respective flags immediately with no intervening spaces.
3. When flag arguments specify file directories, they must be ended with the operating system-specific directory character. For windows, it is a backslash ‘\’; for unix, a forward slash ‘/’.
4. SQR command line arguments are the values that your SQR program expects to receive from the command line via the Ask command at compile time or the Input command at run time.
5. We can use an argument file to pass arguments to our program on the command line. Each argument must be entered in the argument file on a separate line.
6. SQR processes the program in 2 stages. The compilation stage and the execution stage. We can use –RS command flag to create a pre-compiled version of program with .SQT extension. We can execute the .SQT file by specifying –RT command flag or using the SQR Execute.
7. We can issue any operating system command within SQR program. The Call System Using command is used to execute the operating system commands.
8. In addition to issuing the operating system commands, the Call System Using command can be used to invoke any executable program.
9. In order to call SQR program from other applications, we can use the SQRAPI
10. SQR programs can be scheduled fro execution from PeopleCode.

Key Points

1. We can generate program reports in an HTML compatible format and publish these reports on the Internet.
2. There are 3 main methods to generate HTML from an SQR program output.
3. We always have to use the –PRINTER:HT, -PRINTER:EH command line parameters to have our program generate HTML output.
4. All SQR HTML procedures are delivered in the Html.inc file.
5. Executing the Html_On procedure turns on all SQR HTML procedures, but causes SQR to ignore all print position qualifiers.
6. The SQR HTML table procedures can be used to control the report-element positioning.
7. The SQR HTML hypertext link procedure allows us to specify links to other parts of the current web page or to an external web page. Alternatively, we can use these procedures to define anchors within our report so that the same or other web pages can have links to these anchors.
8. Our program can use the regular SQR Input command to accept input parameters from another HTML Script.
9. If we run an SQR program without the -PRINTER:HT, -PRINTER:EH command flag, SQR will ignore all SQR HTML procedures in the program and will generate regular LIS files as the program’s output.

Debugging Techniques

Show and Display commands

When it comes to debugging an SQR program, there are no fancy step-through debuggers. The main weapons are 2 SQR commands: Show and Display. We can use these commands anywhere in our program except the Setup Section. Both the Show and Display commands display the specified texts and variables on the screen and also write the same information into the program log file.

If for example, we ran and completed a program successfully, but output is not what we expected, then we need to find out whether our program had some logic flaws, or whether the unexpected results have been caused by an incorrect selection in the programs query. The simplest way to find this out is to add some Show or Display commands to the program.

Show ‘Select started’
Begin-Select
A.Emplid
A.Name
Show ‘selected emplid=’ &a.emplid ‘Name=’ &A.Name
From Personal_data
Where name like ‘Abr%’
End-Select

In the above example, each selected record is displayed on the screen and written to the program log file.

The Display command is somewhat similar to the Show command, but it has less power; we cannot display multiple fields in one Display command.

Display ‘Selected Emplid = ‘ Noline
Display &A.Emplid

The #Debug Command

We can optionally use the #Debug conditional compiler directive by executing the Display, Show, Print or other commands in our program during testing, and then deactivating them when the program is released to production. This approach is called Debugging Encapsulation.

Any SQR command that immediately follows the #Debug statement will be compiled only when the corresponding –Debug flag is specified in the SQR command line. The #Debug command may be appended with one or more letters or numbers: ex #Debugxy, to provide additional flexibility in debugging. We may optionally specify up to 10 different suffix letters or numbers.

#Debug Show ‘Start proce1’
#Debugx Show ‘Start proc2’
#Debugy Display ‘start proc3’
#Debugz Display ‘Start proc4’

#Ifdef or #Ifndef / #End-If
#If / #Else / #End-If

Key Points

1. The Display and Show commands are the main instruments in debugging SQR programs.
2. The –DEBUG SQR command line flag in combination with the #Debug, #Ifdef, and #Ifndef statements, can help to encapsulate the debugging logic in your program.
3. The –S, -T, -C and –E SQR command line flags can provide additional help during the debugging process.

Local Procedures Vs. Global Procedures

All procedures in SQR are considered global by default. In order to make a procedure local, it must have parameters or must be explicitly declared local. Variables that appear in local procedures are not available outside of their respective procedures. This way, the same variable name can be used in different local procedures without creating an error situation.

Local procedures can communicate with other local and global procedures via parameters. Since global variables defined outside of a local procedure can be accessed only by using special naming convention there is no risk that some variable values can be accidentally overridden.

Key Points

1. To support global development:
· Avoid hard-coding date, currency and numeric edit format masks: use SQR special keywords Date, Money and Number instead.
· Use SQR locales.
2. To ensure our program platform independence:
· Do not use fully qualified hard-coded file names
· Use the $sqr-platform predefined variable to determine the current platform
· Avoid calling operating system commands from SQR programs; use similar SQR functions instead.
· Test your programs on both client and server
3. These rules will help to write database independent programs
· Do not use database-specific function, use similar SQR function instead.
· Avoid using built-in function in the where clause
· Use the ANSI standards in SQL
· Do not rely on database-specific default date formats
· Do not assume that all database return the same error codes and messages
· Do not use database specific command parameters and flags.

4. Using SQR procedures in the Begin-Select instead of sub-queries gives your move flexibility and improves readability of program, but may negatively impact performance.
5. Local procedures may help to avoid errors caused by incorrect usage of previously defined variables.
6. Coding error-handling routines allows you to alter users of any unusual situation, avoid the system crushes, and prevent a possible database corruption.
7. Avoid hard-coding in programs
8. Place encapsulated debugging statements in the program code
9. Do not forget about comments
10. Use meaningful variable name.

Running SQR in PeopleSoft Applications

We can run SQR programs from the SQR Dialog Box, or from the operating system Command Line. PeopleSoft-delivered reports are usually executed from an online page and run with the help of the PeopleSoft Process Scheduler.

Programs in PeopleSoft can be written in different languages: SQR, Crystal Reports, Cobol Application Engine, etc. all these programs can run under the PeopleSoft Process Scheduler. The Process Scheduler works with processes and job streams.

A process is any program that runs under PeopleSoft Process Scheduler. It can be a reporting program, a file generation program, a database update program, or a combination of all these. We can run processes on workstation or a server. The PeopleSoft Process Scheduler can help to schedule the execution of processes so that they can run automatically.

The PeopleSoft Process Scheduler allows bundling processes into job streams and scheduling them to run at a specific date and time.

Process
Scheduler
Process Monitor
SQR Program

When page information is filled in, PeopleSoft generates process request parameters. These parameters usually include User Id, Run Control Id, Run Location, Output Destination, File/Printer name, plus application-specific parameters, fro example, company Id, FromDate, ToDate and so on.

After the process request parameters have been read from a page, PeopleSoft passes them to the Process Scheduler. The Process Scheduler generates the SQR command line with flags and arguments that are required to run the requested SQR program, invokes SQR and passes the flags and arguments to SQR. When the input from the page is save, the system updates a number of tables that are used by SQR to communicate with the Process Scheduler and the Process Monitor via the PeopleSoft API.

The requested SQR program is executed. It may generate reports, update the database, create flat files, or print its reports directly on the specified printer. Users are kept inormed abou the program status with the help of the PeopleSoft Process Monitor. The Process Monitor receives the program feedback via the PeopleSoft API parameters and displays the program status on the Process Monitor page.

Any program run under the Process Scheduler is considered as a process.

Run Control

When we run a process via the Process Scheduler, we need to supply it with a number of parameters like; the run location, output destination, output format, file/printer name, etc. this information is stored in the PeopleTools Run Control record PSPRCSRUNCNTL.

Each process maintains its own Application Run Control Record to store the process-specific input run-time parameters for ex: as-of-date, companycode, or state. The Run Control Id along with the OperId are the key fields in both Application Run Control Record and PeopleTools Run Control Record.

Process Scheduler Request Dialog

The Process Scheduler Request Dialog is used to specify where we want to run our report, the destination type and format of our output and the time of the report execution.

In this page we tell the system how to handle our process request. We select the Server Name, Recurrence definition, Run Date & Run Time.

The Recurrence parameter allows defining a process as a recurring process that may be executed on a periodic basis.

The default output format for SQR with output type web is Adobe Acrobat-(.pdf)

After filling Process Scheduler Request page, click on the OK button. The PeopleTools Run Control Record (PSPRCSRUNCNTL) is updated. Also a record is inserted into the PSPRCSRQST table (Process Request Table) and other PeopleTools tables. This gives the Process Scheduler the necessary information to run and monitor the process request. The Process Scheduler Server Agent pools the PSPRCSRQST table for incoming process request. When the row is found, the Server Agent invokes special program, called PSSQR wrapper, which then calls SQRW.EXE or SQR.EXE depending on the platform the SQR report runs on.

Take a look at the line under the Run Button. It shows the Process Instance Number. As soon as report is submitted, the system automatically generates the Process Instance value.

Note: A Process Instance number displayed under the Run Button indicates that our Process has been submitted for execution.

The Process Monitor

The Process Monitor not only allows to check the status of our process, but also to delete completed report request from the queue or cancel process requests that re currently running or have been initiated.

If we click on the Process Monitor hyperlink the system will bring the Process Monitor Page. The Refresh Button is used to update this page. The process Monitor shows the Processes by User (Operator Id). We can modify this process list by narrowing the selection down to a specific Server, Type (SQR Report, SQR Process, Application Engine, Crystal, etc.) or Run Status (Success, Error, Initiated, Canceled).

Queued => Initiated => Processing => Posting => Success.

Key Points

1. PeopleSoft delivered reports are usually executed from online page and run with the help of the PeopleSoft Process Scheduler
2. The Process Scheduler works with processes and job streams.
3. PeopleSoft recommends executing processes and jobs via the PeopleSoft Internet Architecture (PIA). It allows more flexibility in distribution and viewing the report output in Report Manager
4. The Process Scheduler controls process execution with the help of the control records; PeopleTools Run Control and the Application Run Control. The Run Control Id along with the Operator Id are the key fields in these records.
5. Clicking on the Run Button in the Run Control Page does not submit a report. It brings u the Process Scheduler Request page.
6. The Process Scheduler Request Page is used to specify where you want to run your report, the destination and the type and format of your output, and the time of the report execution.
7. The Process Instance number displayed under the Run button on the Run Control Page indicates that your process has been submitted for execution.
8. SQR is executed the wrapper program PSSQR.exe. This is an executable module of a program written in C that is responsible for handling your process request.
9. The Trace File hyperlink in the Report/Log Viewer brings you a page with the log file. Any Display or Show statements used in our SQR program will be shown in this file. It will also show you any errors produced by the program.
10. The View Log/Trace link appears on the Process Monitor detail page if the following 2 conditions are true.
a. the process request output destination is set to web.
b. The report and log files were successfully posted to the Report Repository by the Distribution Agent. The process must have a run status of successful.
11. If the program produced a report with an output destination type of web, we would be able to click on the report link from the Report/Log viewer page and view your report online. For process types with output other than web, we can track their status through the Message Log.
12. The Status on the Report manager page differs from the Run Status on the Process Request page.

To access any report under PeopleSoft, we need to know the Menu to which this report belongs. After all application-specific parameters are entered and the Run Control page is saved, the system inserts this information into the Application Run Control record fro the report. In order to attach our report to a Run Control Page, a Process Definition has to be created for this report.

The following PeopleTools objects have be created or reused when we need to attach a custom report to the PeopleSoft Process Scheduler.

· Menu
· Run Control Page
· Component
· Application Run Control Record
· Process Definition

PeopleSoft delivered Application Run Control Record is PRCSRUNCNTL, which has the following fields

OperId Key
Run_Cntl_Id Key
Language_Cd
Language_Option

PeopleSoft delivered Run Control Page is PRCSRUNCNTL which has a Sub-Page named PRCSRUNCNTL_SBP.

Creating Process Definition

Every process run under the PeopleSoft Process Scheduler needs a Process Definition to specify the process attributes and link the process to the appropriate component.

To create a Process definition, we will be using the Process Scheduler manager. This component of PeopleTools, similar to the Maintain Security tool, is accessed via PIA.

Navigation: Home=>PeopleTools=>Process Scheduler Manager=>Use=>Process Definition.

If we select SQR process, the Process Scheduler will not pass the Operator ID and Run Control ID to our program and the program will not work correctly unless we specify Operator ID and Run Control Id as additional parameters.

The Process Name must be the same as our program name, the extension .sqr is not needed.

After we press OK and assigned the type to our process, the system displays the Process Scheduler Process definition component. This component consists of the following 6 pages

1. Process Definition
2. Process Definition Options
3. Override Options
4. Destination
5. Page Transfer
6. Notification
The Process Definition Page

An API aware process is a process that updates the Process Request Table (PSPRCSRQST) with the Process Run Status (Error, Success etc), completion code, message set and message number. This allows the system to perform Commit or Rollback, depending on the Run Status. Based on the process execution results, the system displays a standard or custom message on the Process Monitors Process Request Detail Page.

Not every program is API aware. Turning the API aware flag ON does not automatically make the process API aware.

The Process Definition Options Page

In this page the Run location can be Server, Client or both. The Server name is (optional) specified if you always have to run your process on a particular server. If we do not want to be so restrictive, eave it blank.

If a user leaves the server name blank on the process request page, the system automatically find the first available server that can process the request for this process class. The server name can be specified only if the run location is server.

Placing the SQR Program into the right Directory

SQR reports are located on the file server on your network. There are 4 environment variables that are used by PeopleSoft. The Process Scheduler uses these variables to search for your SQR program.

%PSSQR1% - your workstation user SQR directory
%PSSQR2% - your workstation PS delivered SQR Directory
%PSSQR3% - network file server user SQR directory
%PSSQR4% - network file server PS delivered SQR directory

Key Points

1. Any Run Control Record must have the Operator Id and the Run Control Id as its key fields.
2. A Run Control Record may contain additional fields that are not used in our program.
3. A Run Control Page should be specific to our application and should contain only the fields that we want user to enter. Many processes may use the same Run Control page as long as they share the same input parameters.
4. A Component acts as a link between a Run Control page and a menu. There may be multiple pages in a single component.
5. We can add our SQR program to an existing menu item or create a new one.
6. The appropriate security access must be granted to allow all users to see the new menu.
7. Each PeopleSoft user has an individual profile, called User Profile, which is linked to one or more roles.
8. Permission lists control access to menu, components, and pages for each role. Roles act as links between User Profiles and Permission lists.
9. An API aware process update the Process Request (PSPRCSRQST) tables with the process run status, completion code and message parameters.
10. In Order to make our SQR program run under the Process Scheduler, the following PeopleSoft object have to be created, modified or reused:
a. Application-specific Run Control Record
b. Application Run Control Page
c. Component
d. Menu Item
e. Security access to the new component
f. Process Definition

Making SQR Program API Aware

Making an SQR program API aware involves adding a program code to update the Process Request Table (PSPRCSRQST) with the program run status (Error, Success) completion code, error message set and error message number.

In order to make our SQR Program API aware, we have to use the #Include operators to add the STDAPI.sqc and SETENV.sqc files to our program. The STDAPI.sqc intern, uses the nested #Include operators that refer to other important API files.

#Include ‘setenv.sqc’

Begin-Program
Do Stdapi-Init
Do Main
Do Stdapi-Term
End-Program

#Include ‘datetime.sqc’
#Include ‘Stdapi.sqc’

At the program start, the Stdapi-Init procedure is invoked. This procedure is a part of the PeopleSoft-delivered SQC file STDAPI.sqc. Stdapi-Init invokes 2 more procedures in tern. The first one is Define-Prcs-Vars its job is to initialize all API variables, the second procedure is Get-Run-Control-Parms determine whether the program is called from the Process Scheduler and, if yes, promotes the run status from ‘Initiated’ to ‘Processing’

At the end of the main section, the program calls the Stdapi-Term procedure, which is a part of STDAPI.sqc. The purpose of this procedure is to update the PRCSRQST table with the Process Run Status, Message Parameters, and Return code.

The Run Status in the PSPRCSRQST table is updated based on the #prcs_run_status variable value. The variable determines the Run Status that we see on the Process Monitor page.
In a normal run PeopleSoft promotes the Process Run Status in the following order:

Queued -> Initiated -> Processing -> Success

Key Points

1. An API aware process is a process that updates the Process Request table (PSPRCSRQST) with the process run status (Error, success) completion code, message set, and message number.
2. PeopleSoft-delivered SQC file STDAPI.sqc helps us to make our program API aware.
3. The Define-Prcs-Vars procedure in PRCSDEF.sqc initializes tall the fields used in the API.
4. The Get_Run_Control_Params procedure in PRCSAPI.sqc retrieves input parameters: Database Name, Process Instance, Operator Id, and Run Control Id.
5. It is our SQR programs responsibility to communicate errors back to the Process Scheduler.

Key Points

1. To accept input parameters from PeopleSoft online pages, we can either use the existing PeopleSoft-delivered SQC files or develop our own, depending on the parameters our SQR program needs to accept.
2. Our program should support both types of input parameter retrieval logic: retrieving the parameters from the Process Scheduler, or accepting them from the SQR Dialog Box, or the Command Line.
3. Usually, there are 2 SQC files involved in accepting program input parameters from PeopleSoft Online pages. 1 file should contain a procedure to select all required fields from the proper Run Control Record. Another one should include procedures to edit the selected fields and place them into designated SQR variables.
4. Our SQR program must be changed to include the proper SQC files, and a code to call the input parameter retrieval procedures.
5. A Run Control page that contains all the input parameters should be developed, or an existing page should be used or customized.
6. The changed SQR program has to be thoroughly tested to make sure that the input parameters are passed and accepted correctly.

Key Points

1. We can schedule our programs that run on Server for execution on a recurring basis.
2. A job may include one or more processes.
3. In order to schedule a job for execution, a job definition has to be created. Like a Process Definition, a Job Definition needs to be associated with a Component.
4. We can include processes of different types (SQR program, Cobol, Application Engine,) into one job.
5. We need to create a page in the jobs Component for each run control record used by a process in the job.
6. If we want our processes to be executed sequentially, we should select the Serial mode on our Job definition page, otherwise, use the Parallel Mode.

Preventing an SQR from running outside the Process Scheduler

If the value of $Prcs_oprid is Null, SQR detects that it is not executed from the Process Scheduler.

Key Points

1. In order to prevent an SQR Program execution outside the PeopleSoft Process Scheduler, check the value of the $prcs_oprid variable.
2. Row-Level security is used to control access to specific rows of data in the database. PeopleSoft delivers applications with row-level security.
3. PeopleSoft uses security search view records to provide on-line row-level security.
4. Build your own security views when we cannot use the PeopleSoft-delivered ones.

Working with Effective-Dated Tables

In PeopleSoft, most applications are built around the concept of effective dates. This approach facilitates managing data changes over time and keeping track of all changes. It also allows for creating future-dated information and making these data effective only on the appropriate date.

In order to accomplish this task, some PeopleSoft tables contain a special field: EFFDT- effective date. A table that contains the effective date as part of the table key, is usually called an effective-dated table, and the data rows of such tables are called effective-dated rows.

PeopleSoft relates all effective-dated rows to one of the following 3 categories:

· Historical data rows
· The Current data rows
· Future data rows

The current data row is the most recently entered row, whose effective date comes closest to todsy’s date without exceeding, it. For a given search criteria, there can be only 1 current data record within a particular table.

The future data rows are rows with effective dates that are greater than today’s date. All other rows are considered historical rows. The historical data rows include rows with effective dates that are less than the current row’s effective date, as well as the rows with the effective date equal to the current row effective date if these rows were entered into the system prior to the current row.

Multiple Records with the same Effective Date

In many cases, we need to insert multiple rows with the same effective date into an effective-dated table. Lets consider an example of the effective-dated table PS_JOB that belongs to the PeopleSoft HRMS system. This table contains employee job history records.

An employee may have several events that can occur on the same date. Most of these events are registered in the employee’s job history. When 2 different events may occur on the same day, PeopleSoft provides a key field Effseq (Effective Sequence). When paired with Effdt, this key combination allows entering more than one unique row with the same effective date.

Begin-Select
A.Emplid
A.Company
A.Effdt
A.Action
From PS_JOB A
Where A.Company=’CCB’
And A.Effdt = (Select max(Effdt) from PS_JOB B
Where A.Emplid = B.Emplid
And A.Empl_Rcd = B.Empl_Rcd
And A.Effdt <= Sysdate)
And A.Effseq = (Select max(Effseq) from PS_JOB C
Where A.Emplid = C.Emplid
And A.Empl_Rcd = C.Empl_Rcd
And A.Effdt = C.Effdt)
End-Select

Selecting the top row from the Effective-dated table

Begin-Select
A.Emplid
A.Company
A.Effdt
A.Action
From PS_JOB A
Where A.Company=’CCB’
And A.Effdt = (Select max(Effdt) from PS_JOB B
Where A.Emplid = B.Emplid (the sub selection does not include additional
And A.Empl_Rcd = B.Empl_Rcd restrictions on the Effective dates.)

And A.Effseq = (Select max(Effseq) from PS_JOB C
Where A.Emplid = C.Emplid
And A.Empl_Rcd = C.Empl_Rcd
And A.Effdt = C.Effdt)
End-Select

Key Points

1. Placing effective dates in table records allows from managing and tracking data changes over time.
2. Tables that contain the Effective date columns are usually called Effective-dated tables.
3. PeopleSoft relates all effective-dated rows to one of the 3 categories: Historical rows, Current rows and Future rows.
4. There can be one 1 current record for a particular table key.
5. In PeopleSoft, if the effective date is present in a table record, it is always a part of the table key.
6. The effective sequence fields, when paired with the effective date fields, allow for entering more than 1 row with the same effective date.
7. The MAX SQL aggregate function can be used to select the current rows from the effective-dated table.
8. We can improve performance when selecting the current records by sorting table by the effective date and sequence and using Loops=1 to limit the output of the sub-queries to only 1 record.
9. We can select both the current and prior rows from an effective-dated table.
10. We can us the Exists SQL operator to select only specific prior rows.
11. Date intervals between events can be calculated using either SQR date functions or database-specific functions. Avoiding database-specific functions makes your programs database-independent.

Multiple Report Generation in SQR

2009-07-22T06:44:00.000-07:00

For generating multiple reports in sqr , Use different "Declare-Report" and use the different report name in various procedures Given an example code:- Here we get two reports

begin-setup
!#define MAX_LABEL_LINES 10
!#define LINES_BETWEEN_LABELS 3begin-setup
declare-layout labels
paper-size&H3D(10,11) left-margin&H3D0.33
end-declare
declare-layout form_letter
end-declare
declare-layout listing
end-declare
declare-report labels
layout&H3Dlabels
end-declare
declare-report form_letter
layout&H3Dform_letter
end-declare
declare-report listing
layout&H3Dlisting
end-declare
end-setup

begin-program
do aa
do bb
end-program

begin-procedure aa
begin-select
emplid (,1)
position (+1)
from psoprdefn
end-select
use-report form_letter --> First report
end-procedure

begin-procedure bb
print 'hello' (1,1)
use-report listing --> Second Report
end-procedure

You can use diferent layouts if you want as given above.

PEOPLESOFT HOT KEY'S

2009-07-21T03:28:00.001-07:00

We all probably know that “CTRL J” hot key gives us the system information. But did you know that there are a lot more available. But you do not have to trouble your brain to remember them, simply press navigate to a search or transaction page and invoke hot key “CTRL K” to get all the information. Below are the results from PeopleSoft “CTRL K“.
Accessing your application using the keyboard
Keyboard navigation is controlled by Hot keys and Access keys .

List of Hot Keys
Alt 1– Executes different buttons depending on the page type

> Save button on the Toolbar in a page
> OK button on a secondary page
> Search or Add button on a Search or Lookup page

Alt 2 — Return to Search
Alt 3 — Next in List
Alt 4 — Previous in List
Alt 5 — Valid Lookup Values
Alt 6 — Related Links
Alt 7 — Insert Row in grid or scroll area
Alt 8 — Delete Row in grid or scroll area
Alt 0 — Refreshes the page by invoking the Refresh button on the Toolbar
Alt . — Next set of rows in grid or scroll area [e.g., Alt period]
Alt , — Previous set of rows in grid or scroll area [e.g., Alt comma]
Alt /– Find in grid or scroll area [e.g., Alt forward slash]
Alt ‘ — View All in grid or scroll area [e.g., Alt prime]
Alt \– Toggle between Add and Update on the Search page [e.g., Alt backslash]
Ctrl J– System Information
Ctrl K> — Keyboard Information
Ctrl Y>– Toggle menu between collapse and expand.
Ctrl Tab> — Toggles focus through the frame set
Enter >– Invokes the following buttons where present: OK, Search, Lookup
Esc >– Cancel

List of Access Keys
Alt 9> — Takes you to the Help line
Alt \ >– Takes you to the Toolbar [e.g., Alt backslash Enter]
Ctrl Z> — Takes you to the Search box of the Menu

Menu Access Keys
The Ctrl Z combination will focus your cursor onto the menuing system. From there, you can use your tab key (or shift-tab to reverse direction) to navigate through the menu hierarchy.

About Access keys and Hot keys
> An Access Key is an Alt key combination that moves focus to a specified field on the current page.
For example, Alt \ moves focus to first button on the Toolbar. Then pressing the Enter key would invoke that action. Or, you may use the Tab key to move you to the next Toolbar button.
> A Hot Key performs an immediate action. For example, when focus is in a field that has lookup processing, Alt 5 invokes the Lookup page without having to press the Enter key.

Time Clock code in PEopleSoft

2009-07-19T22:31:00.001-07:00

Process Definition

2009-07-16T01:33:00.000-07:00

When a process definition has been created, there are three main tables where it makes an entry and store definition of the . Those are:

PS_PRCSDEFN -- The main page where you start creating "Process Definition".
PS_PRCSDEFNPNL -- "Process Definition Options" page where you define components, process group, recurrence etc...
PS_PRCSDEFNGRP -- "Process Definition Options" page where you define components, process group, recurrence etc...

Apart from that, there are some more tables which get affects depends on options you have choosen while creating a process definition. Below are the same with description...

PS_PRCSDEFNCNTDIST -- If you have specified "Output Destination Options" for some process, enteries goes to this table.
PS_PRCSDEFNXFER -- If you have specified "Page Transfer Information" for some process, enteries goes to this table.
PS_PRCSDEFNNOTIFY -- If you have specified " Notifications" for some process, enteries goes to this table.
PS_PRCSDEFNMETA -- If you have specified some value in "OS390 Option", enteries goes to this table.
PS_PRCSDEFNMESSAGE -- If you have specified some value for message from page "Schedule Message Information", enteries goes to this table.

So there are two query sets here to delete any process definition...

First case in which you just have created a wrong process definition:

DELETE FROM PS_PRCSDEFN where PRCSNAME = 'process name'
DELETE FROM PS_PRCSDEFNPNL where PRCSNAME = 'process name'
DELETE FROM PS_PRCSDEFNGRP where PRCSNAME = 'process name'

Second case in which you have created a wrong process definition with the options like Output destination, Page transfer etc...:

DELETE FROM PS_PRCSDEFN where PRCSNAME = 'process name'
DELETE FROM PS_PRCSDEFNPNL where PRCSNAME = 'process name'
DELETE FROM PS_PRCSDEFNGRP where PRCSNAME = 'process name'
DELETE FROM PS_PRCSDEFNCNTDIST where PRCSNAME = 'process name'
DELETE FROM PS_PRCSDEFNXFER where PRCSNAME = 'process name'
DELETE FROM PS_PRCSDEFNNOTIFY where PRCSNAME = 'process name'
DELETE FROM PS_PRCSDEFNMETA where PRCSNAME = 'process name'
DELETE FROM PS_PRCSDEFNMESSAGE where PRCSNAME = 'process name'

SQR output and Fonts

2009-07-16T01:31:00.000-07:00

Below is the extract from the same PS delivered file. You may try with any font which is not listed below and in case if that font is not supported, PS converts it to the default font, which is Courier.

List of defined fonts in PeopleSoft:

Number --------------- Name

---------------------------------

3 ----------------------- Courier
300 -------------------- Courier-Bold
4 ----------------------- Helvetica
400 -------------------- Helvetica-Bold
5 ----------------------- Times-Roman
500 -------------------- Times-Bold
6 ----------------------- AvantGarde-Book
8 ----------------------- Palatino-Roman
800 -------------------- Palatino-Bold
11 ---------------------- Symbol
12 ---------------------- ZapfDingbats
17 ---------------------- ZapfChancery-MediumItalic
18 ---------------------- Bookman-Light
23 ---------------------- NewCenturySchlbk-Roman
2300 ------------------- NewCenturySchlbk-Bold
30 ---------------------- Courier-Oblique
3000 ------------------- Courier-BoldOblique
31 ---------------------- Helvetica-Oblique
3100 ------------------- Helvetica-BoldOblique
32 ---------------------- Times-Italic
3200 ------------------- Times-BoldItalic
33 ---------------------- AvantGarde-Demi
34 ---------------------- AvantGarde-BookOblique
35 ---------------------- AvantGarde-DemiOblique
36 ---------------------- Palatino-Italic
3600 ------------------- Palatino-BoldItalic
37 ---------------------- NewCenturySchlbk-Italic
3700 ------------------- NewCenturySchlbk-BoldItalic
38 ---------------------- Helvetica-Narrow
3800 ------------------- Helvetica-Narrow-Bold
39 ---------------------- Helvetica-Narrow-Oblique
3900 ------------------- Helvetica-Narrow-BoldOblique
40 ---------------------- Bookman-Demi
41 ---------------------- Bookman-LightItalic
42 ---------------------- Bookman-DemiItalic

You can get this list of delivered SQR fonts in postscri.str file which you can access from the machine where scheduler is installed and configured.

Hope this helps... suggestions are always welcome in case I missed out something...

The Structure of PS_HOME

2009-07-14T23:34:00.000-07:00

The PS_HOME directory on a typical PeopleSoft file server are below:

* appserv - application server/process scheduler files for each domain
* bin - client, server and sqr binaries
* class - common Java classes
* class_ca - more Java classes ?
* crw - Crystal reports
* data - data used used to populate PeopleTools and application demo databases
* dict - language dictionaries ?
* doc - not sure - empty?
* etc - not sure ?
* excel - nVision, Excel to CI files
* fonts - truetype fonts
* jre - Java runtime environment
* log - installation log files
* nvision - nvision reporting
* projects - delivered application designer projects
* PSEMAgent - PeopleSoft environment management agent
* PSEMViewer - PeopleSoft environment management viewer
* psreports - report repository
* scripts - installation sql and datamover scripts
* sdk - software development kit and API information (e.g. Integration Broker and component interfaces)
* SecurityAddIns - Oracle single signon plugin?
* secvault - encryption keys (e.g for pscipher)
* sendmaster - SendMaster utility for testing PeopleSoft Integration Broker
* setup - Various installers (e.g. PIA, change assistant, database configuration wizard)
* sqr - SQRs and SQCs
* sqrfonts - SQR font loader
* src - COBOL and other miscellaneous source files
* template - nothing?
* tuxedo - tuxedo files
* utility - various utlities
* verity - verity search engine
* web - where PeopleSoft Java Object adapter .jar file is located. Not sure what else this is for.
* webserv - possibly where the web server files have been installed or most likely empty
* winword - Microsoft Word templates and macros

Files

* peopletools.properties - Basic PeopleTools install properties

Application Engine

2009-07-01T23:38:00.001-07:00

Application Engine
Application Engine is a PeopleTool designed to develop background SQL processing programs. AE offers an alternative to writing Cobol or SQR programs for background SQL processing. AE does not generate, parse or understand SQL. It merely executes SQL that we provide.

Using the Application Engine, we create application that performs background SQL processing against the Data. AE is used for batch processing where we have large amount of data that needs to be processed without user intervention.

We can use AE for straight, row-by-row processing, but the most AE programs are written to perform set-based processing.

Application Engines execution component is written in C++ and is part of and maintained by – PeopleTools. This executable program is called PSAE.exe.

Application Engine Advantages:

1. Graphical Developer Interface
2. Encapsulation
3. Data Dictionary Integration
4. Enhanced SQL/Meta-SQL Support
5. Effective-Dating
6. Platform Flexibility
7. Reuse Business Logic
8. Upgrade Support
9. Built-in Restart Logic

1. Graphical Developer Interface: with PeopleSoft 8, AE is fully integrated with People Tools. This means that we can use the Application Designer, an intuitive graphical interface to create Application Engine Programs.

2. Encapsulation: An AE program resides completely within the DB. With Application Engine, we do not have to compiler our programs, there are no statements to store, and there is no need to directly interact with the operating environment we use.

3. Data Dictionary Integration: one of the cornerstones of PeopleSoft functionality is the Application Designer. Because of the way it works, most field attributes, such as type, length and scale, only need to be specified once, and that change will be reflected globally. If the same field appears on more than one record, we know that the same attributes reside in each record definition.
Because AE works in harmony within the PeopleSoft system and references the Data Dictionary for object definitions, changes to meta-data in the database have no impact on AE programs.

4. Enhanced SQL/Meta-SQL support: we can write SQL within AE, or we can copy SQL statements into AE from any SQL utility with few, if any changes.

RDBMS platforms have many differing syntax rules-especially in regard to Date, time and other numeric calculations. For the most part, we can work around this problem using PeopleSoft meta-SQL, which AE supports. This language is designed to replace RDBMS-specific SQL syntax with a standard syntax.

For example, if we reference a date field called AE_DATE in a SELECT clause, we specify it as %DATEOUT(AE_DATE). This meta-string will be resolved to the appropriate value as required by the current RDBMS platform. Meta-SQL also allows to dynamically generating portions of SQL statements. This new meta-SQL is not supported in Cobol or SQR.

Fox example, if we wanted to join 2 tables on their common keys, we could use the following code:

%Join(COMMON_KEYS, PSAESECTDEFN ABC, PSAESTEPDEFN XYZ)

at run time, the function would be expanded into the following

ABC.AE_APPLID = XYZ.AE_APPLID
AND ABC.AE_SECTION = XYZ.AE_SECTION
AND ABC.DBTYPE = XYZ.DBTYPE
AND ABC.EFFDT = XYZ.EFFDT

5. Effective-Dating: Application Sections are effective-dated, meaning we can activate/deactivate a Section as of a particular date. This enables us to archive Sections as we modify them, instead of destroying them. In the future if we decide to revert to a previous incarnation of a section, we can simply reactive it.

6. Platform Flexibility: in AE we have the ability to call different versions of a Section for different platforms, as needed.

Default : This is the common denominator or global version.

Supported RDBMS platform: DB2, Oracle, Informix, DB2/Unix, Sybase, MS SQL Server.

Most Sections will only have the default version and even in cases where we refine a particular Section for a specific RDBMS, a default version of that Section will also exist. When AE executes a Section, it will use the version that’s specific to the current DB platform if a specific version exists. If there is no REBMS-specific version, AE uses the (default) version.

Within platform-specific Sections, we also have the ability to call base portions of SQL statements by using the %SQL object. This means we can write our generic SQL portions just once, and then can reference them from different platform versions.

7. Reuse Business Logic: AE programs can now invoke PeopleCode. This means that we can call common PeopleCode functions from AE. Conversely, PeopleCode can now invoke an AE program Synchronously. AE Libraries are used to store common routines, stored as callable sections. Multiple programs can then call these sections.

8. Upgrade Support: AE program definitions are defined in and stored as Application Designer object, then join the growing ranks of PeopleTools components that we can easily upgrade using the Application Designer. We can upgrade AE definitions by project, program, or section.

9. Built-in Restart Logic: within each AE program, we must define how frequently our program will issue a COMMIT. After doing so, each COMMIT becomes a checkpoint that AE uses to locate where within a program to restart after an abend.

The restarted AE program locates the last checkpoint, or the last successfully executed and committed step, and continues with the processing from that point. This type of built-in logic does not exist with COBOL or SQR.

Key Points

1. The AE executable is PSAE.exe and is written in C++
2. AE is generated into the Application Designer to give you a graphical developer tool
3. AE programs are stored in PeopleSoft DB.
4. Applications can dynamically adjust for changes in record and field definitions through integration into the PeopleSoft Data Dictionary and meta-SQL, a situation requiring changes and recompilation of COBOL.
5. Using meta-SQL our programs can be written to be DB platform independent.
6. Portions of our program are effective-dated so we can activate/deactivate a section as of a particular date.
7. Our programs can take advantage of existing business logic in the form of shared PeopleCode
8. You have Upgrade integration for migrating programs to other instances of our DB as well as future release upgrade support.
9. AE has built in restart capability.

Application Engine programs are created and modified in the Application Designer. The Application Designer offers AE developers the following benefits

· Ability to open multiple AE programs concurrently.
· Ability to work on entire AE program at once. Users of previous versions will know that this is a change from only being allowed to work on a Section at a time.
· Easy access to PeopleCode and SQL editiors.
· 2 Views of our program

1. The Definition view is where we create and modify our programs.
2. The Program Flow view shows a graphical representation of our program execution.

Application Engine Program Structure

An AE program is made up of several key components.

1. Program
An Application Engine program identifies the set of processes to execute a given task. A program must contain at least one section. The execution of the program always starts with the Section defined as ‘MAIN’

2. Sections
An AE Section is comprised of 1 or more Steps and is equivalent to a cobol paragraph or an SQR procedure. All AE programs must contain at least 1 Section entitled ‘MAIN’

Execution precedence: A section is unique based on the program, section names, RDBMS platforms, effective date and market. When we execute and AE program, it executes Sections based on the following order

· If a Section for the current Market exists, execute it.
· If a Section for the current Platform or RDBMS exists, execute it.
· If a Section for the current Effective Date exists, execute it.

If a ‘Unique’ section does not exist, AE just executes the ‘base’ Section. The default Market value is GBL for Global, which is similar to the Platform value of default.

3. Steps
A Step is the smallest unit of work that can be committed within a program. Although, we can use a Step to execute a PeopleCode command or log a message, typically, we will use a Step to execute a SQL statement or to call another Section. The SQL or PeopleCode that a step executes are the Actions with the step.

Each section contains 1 or more steps. The steps are executed in the order that they are displayed in the Application Designer. The steps contain the logical groupings or actions to be performed.

Control
Description
Step Name
Consecutively numbered based on the last step name
Step Description
Enter a descriptive name for the step
Commit After
Determines when commit will be executed:
· Default – uses value supplied for the section
· Later – don not commit now
· After step - commit when step completes

Frequency
Only available in loops
On Error
Determines the action to take when an error occurs
· Abort – program will write message to message log and terminate
· Ignore – program will write message to log and continue
· Suppress – no message will be written, but program will continue.
Comment
Add any additional comments to document your program

4. Actions
There are multiple types of Actions that we can specify to include within a step. A single step can have multiple actions.

Actions are the reason for our program. They have the particular executable action we wish to take. The primary type of action for the AE program is a SQL statement we whish to have executed against the DB. But we also have the ability to log messages, execute PeopleCode, and call other Sections. Moreover, we can execute other sections conditionally and iteratively. The following action types are available

Do Select
Do While
Do Until
Do When
PeopleCode
SQL
Call Section
Log Message

We can note that there are 7 actions out of the 8, because the SQL and Call Section Actions are mutually exclusive.

Note: we must save our program before we can change the name of a Section. The only Section we cannot rename is MAIN.

One of the advantages of AE is that we can code a program to execute differently based on the market code. Based on the market selected, the program will execute the appropriate Sections.

Key Points

1. Applications are structured in a hierarchy of Sections, Steps and Actions.
2. we must determine the data sources and structures for our AE program.
3. We must generate the SQL required for our program. We can use the PeopleSoft Query tool or relational DB SQL tool.
4. Application Designer is used to build individual AE programs.
5. FASTTEST can be used from the AE Request Page to test our AE programs.

State Records:

When we create AE programs, we will need to pass variables; a State Record is the method by which we allocate variables in our AE program, and it also is the method by which we pass the values to subsequent program steps. An AE program may have 0, 1 or many State Records, but only one State Record will be the default.

The State Record can be a Derived Work Record or an SQL Table. The only difference is that Derived State records cannot have their values saved at commit time, there fore the values would be lost during the restart.

The State Record is used as a worktable for:

· Initial values
· Values to be passed to program Steps from prior Sections, Steps and Actions.
· Results of SQL Selects
· Values for SQL Updates, Inserts, Deletes
· Computed values or counters
· Program range or scope values (Business Unit, Pay Groups, Departments)
· Program working values
· Programmable switches or flags

ü A State Record can be a Database Table of Derived Work Record
ü Name must end with _AET identifier
ü PROCESS_INSTANCE must be a key field
ü Date fields cannot be defined as required fields
ü %SELECT (meta-SQL) stores data into the State Record.
ü %BIND (meta-SQL) gets data out of the State Record.
ü Can have 1,nore or multiple State Records
ü In pre-release, it was called as ‘Cache’ Record.
ü No PeopleCode files on an AE State Record.

Values are inserted into the State Record, either by using the %Select statement or the Run Control.

Once a value is inserted into the State Record, it can be access ed using the %Bind(fieldname) to execute the SQL.

When the AE program completes successfully, the row of data in the State Record is deleted.

SQL Action Properties:

1. ReUse Statement: ReUse is an option we can enable to optimize the SQL
components of our batch program. ReUse converts any %BIND references to State Record Fields into real bind variables(:1, :2 and so on)

ReUse has the following options

a. Bulk Insert: This option turns on ReUse, and in addition, it holds all the data in a buffer and only performs an insert after a large volume of rows has gathered in the buffer.

b. No: This option disable the ReUse, with ReUse off, the AE runtime Process recompiles the SQL statement every time the loop executes. By default ReUse will be disabled.

c. Yes: Select this option to enable basic ReUse functionality.

2. No Rows: In the event that the SQL(Insert, Update, Delete) associated with the SQL Action does not return any rows, we need to specify what our AE program should do. The following list contains the options that we have when no rows are returned:

a. Abort – The Program terminates.
b. Section Break – AE exits the current section immediately, and control returns to the calling Step.
c. Continue – The program continues processing.
d. Skip Step – AE exits the current Step immediately and moves on to the next step. When using Skip Step the following in mind:
I. AE ignores the commit for the current step at runtime.
II. If the current Step contains only one Action, only use Skip Step to bypass the commit.

Note: In the Message Catalog, variables are entered using %1 and consecutively increasing the number for each variable.

Meta-SQL Alternative

When we use %SQL in a statement, AE replaces it with the specified SQL object. This allows commonly used SQL text to be shared among AE and PeopleCode programs alike. In AE the variables in the %SQL can be

· %BIND(fieldname)
· Literal

In PeopleCode SQL, we can use: Record.Field or :1

Update PS_CUSTOMER_PRD
Set CUSTOMER_ID = %BIND(TO_CUSTOMER_ID)
Where CUSTOMER_ID = %BIND(FROM_CUSTOMER_ID)

To rewrite this using %SQL it would be the following:

%sql(UPD_CUST_ID, PS_CUSTOMER_PRD, %BIND(TO_CUSTOMER_ID), %BIND(FROM_CUSTOMER_ID))

Key Points

1. A State Record is the method by which we allocate variables for AE program.
2. The developer creates the State Record
3. A State Record must be defined with the following criteria:
· The name of the record must end with AET
· The record contains only 1 key field that must be PROCESS_INSTANCE
· All other fields on the State Record are designed to meet the need of the program.
4. %SELECT is used to store data in the State Record.
5. %BIND is used to retrieve data from the State Record.
6. We can have 0,1 or many state records associated with an AE program.
7. The State Record can be either physical table or Derived Work Record.
8. State Records are associated with an AE program in the Object properties.

Testing & Debugging

Initially, the details of the failures are presented in the Process Monitor. To capture the details of the execution of our application, we have a variety of trace facilities including the Application Engine Step Trace and Application Engine SQL Trace.

To monitor the application and control its execution while it is running, we have Application Engine Debugger. If we have PeopleCode in our AE program, we can use the PeopleCode Debugger with or without the AE Debugger.

1. The Process Monitor:
The Process Monitor has a message log that will tell you if the program executed successfully or not. With a failure you will need to invoke other tools.

2. The Application Engine Trace File
The Application Engine at your request generates trace. You can specify Steps to see a sequence of messages tracking the Step execution of your application execution. We can also trace the more detailed execution of our programs SQL within the Steps. We can also have performance information logged. We can find and analyze the trace in

%temp%\ps\\AE__.AET

3. The Application Engine Interactive Debugger
The Application Engine Debugger is an online, interactive, execution-monitoring tool. We can run our application step by step and look at the results of the execution in the State Record. We can dynamically modify the State Record. We can set Break Points and Watch Fields to see if our application executes portions of the application. We can control the execution of the program and output is dynamically presented to you in a windows command window.

4. The PeopleCode Debugger
We can use the PeopleCode Debugger if errors are occurring the our Application Engine PeopleCode.

Application Engine Trace:

For processes running on a Windows workstation, we can set our trace options in the Configuration Manager. This procedure is only valid when we are running the Application Engine program from the development environment or through the command line on the client.

Application Engine Trace files use the naming convention:
_.AET

Application Engine Debugger:

The Application Engine’s internal debugger can be used while running on the client or when running directly on the Server- outside of the Process Scheduler. When we run our application, we will enter an interactive state in which we can set break points, issue COMMITs, look at the State record, etc.

Turning On the AE Debugger:

The AE debugger is turned on in the Configuration Manager. Open the Configuration Manager => First click on the Trace Tab and turn off the Statement Timings => Click on the Profile Tab => click on the Edit Push button => on the Process Scheduler page, we can find the Application Engine Options.

Debug –Turning this option ON will cause AE to run in debug mode every time we invoke AE in 2-tier.

Disable Restart -this Option will disable restart for all AE programs run in 2-Tier.

Process Monitor:

Programs run through the Process Scheduler, the Process Monitor keeps track of the status of the programs during and after execution. It is one of the first thing that should be inspected to determine if our program terminated correctly and if not, to see if any clues as to the problem recorded.

Key Points

1. To determine what the execution flow of our AE was, we have the trace facility.
2. to drill down during execution of our AE application we have a debug facility to run in step mode, view the State Record as it changes and set breakpoints to permit us to determine exactly what our application is doing.
3. The Restart ability allows us to both debug an application during development and restart a failing production run.
4. Knowing the execution flow of the AE can help you refine the cause of failures.

Do Select Action

The Do Select Action is a SELECT statement that executes subsequent Actions in the Step once for every row of data that the Do Select produces. In other words, the subsequent Actions within the Step are executed in a loop based on whether the SELECT statement in the Do Select Action has returned a row. This is equivalent to a FOR loop.

The Do Select will store the row in the State Record as specified in the Do Select SQL.

%select(Course, Effdt, Eff_status, Tools_Rel)
Select Course, Effdt, Eff_status, Tools_Rel
From PS_PSU_COURSE_TBL
Where Eff_Status=’I’

Each row of data in the database meeting the criteria is loaded into the state record and processed within a loop. When there are no more rows meeting the criteria, the program will move to the next step.

When the Do Select executes, it sets a cursor in the DB for all of the rows matching the criteria in the select statement. The first row is loaded into the State Record then the program proceeds to next action in the step.

Do Select Types:

The type of Do Select determines the specific looping rules. There are 3 Select Types:

a. Select/Fetch
b. Reselect
c. Restartable

a. Select/Fetch

The Select/Fetch type

· Opens the cursor to the database.
· Selects the first row that meets the SQL condition in the Do Select.
· Executes the actions in the step.

Once the subsequent actions, are completed the program returns to the open cursor form the Do Select, retrieves the next row and continues again with the rest of the actions.

Commits:

The Select and Fetch type will ignore all COMMITS placed anywhere in your program that are executed within the Do Select loop, unless we have disabled restart. Disabling restart is a simple way of telling the application that we are freeing it up of the responsibilities of knowing where to restart. That responsibility is now ours via a SWITCH field or an ORDER BY clause

Write ‘COMMIT IGNORED’ in the trace file for any commits within a Do Select loop.

b. Reselect

The Reselect type opens the cursor and closes the cursor on each iteration of the loop.

· Opens the cursor to the database
· Selects the first row that meets the SQL condition in the Do Select
· Closes the cursor
· Executes the following Actions in the step
· Once the subsequent Actions, are completed the program returns to the SQL in the Do Select and opens a cursor again.

Commits:

Commits are not ignored and will be committed in a Reselect Loop. Because the rows being selected are being changed by your code, provided you COMMIT those changes, the program will in fact have a way to know where in the loop it left off, making a restart possible.

We need to: Identify the rows to be processed, orders the data values, place commits in the appropriate sections or steps.

There is a column on the PS_AERUNCONTROL table called AE_STEP_DONE. This column will be populated with a value of ‘Y’ once the entire step, all loops complete. If the program abends in the middle of the loop, the column AE_STEP_DONE will be set to ‘N’.

c. Restartable

The Resatrtable type acts precisely like the Select and Fetch. It opens the cursor and leaves it open. This causes each iteration of our loop to select the next row that meets the condition without the need to change the status of that row as we do with the Reselect option.

Commits:

The major difference with Restartable is that it will COMMIT inside the loop thus allowing a checkpoint to the PS_AERUNCONTROL table.
If the program abends, the information in the AE_RUNCONTROL record and the State Record is used to restart the program from that point.

Steps to Application Engine Development

1. Analyze and Design the Application.

· Determine your data sources
· Develop and process logic pseudo-code and program source
· Does the program need to be restartable

2. Develop and Build your Application

2A. Create and test your SQL statements

· Write and test your SQL Statements

Ø SQL Tool / PS-Query

· Performance-tune your SQL statements

Ø An iterative process
Ø Optimizers

2B. Build Related Definitions

· State Records
· Messages
· Methods for use the user execute the program
Ø Process Definitions (Process Scheduler Manager)
Ø Push Buttons (Application Designer)
Ø Batch Files (PC Text Editor)

2C. Build the Application Engine Program

Create the program in the Application Designer

3. Test and validate the program

· Design your tests
· Initialize your data
· Test and Debug
· Validate success
· Test for restart capability, if applicable.

Key Points

1. The Do Select Action is a SELECT statement that executes subsequent Actions once for every row of data that the Do Select produces.
2. The type of Do Select determines the specific looping rules.
3. Select/Fetch – opens the cursor and retrieves the first row that meets the SQL criteria performs the subsequent actions, the fetches the next row meeting the criteria. This process continues until there are no more rows meeting the criteria, at which time the cursor is closed and the program continues to the next step.
4. Reselect – opens and closes the cursor on each iteration of the loop
5. Restartable – acts the same as the Select/Fetch, except that it will COMMIT inside the loop thus allowing a checkpoint.
6. We must code our program restartability.

Conditional Processing with Do Actions

When a section is called, its steps are executed sequentially. An AE program begins by executing for the first step of the section MAIN and ends after the last step. Any programming language needs to have the capability to perform conditional logic to control the looping.

The 4 Do Actions – Do Select, Do When, Do While, Do Until, act as SQL condition for the execution of the other 4 actions – PeopleCode, SQL, Call Section and Log Message.

Do When Action

The Do When Action is a SELECT statement that allows subsequent actions to be executed if any rows of data are returned. The Do When statement is first in the execution order within a step. The Do When statement will contain SQL that tests for a condition. As long as the condition is true and a row of data is returned, the subsequent actions within the step will be performed. If there is now row of data returned the entire Step would terminate. If there are no more steps to execute, the program will end.

This Do When option is similar to a COBOL ‘IF’ statement. A Do When statement runs before any other actions in the step. If the Do When statement returns any rows, the next Action will be executed. If the Do When conditions are not met, the remaining Actions within that Step are not executed. A Do When Action is executed just once when a step is executed.

***Executes the action once if the condition is TRUE.

DO WHEN counter=1000
Grant Award

Do While Action

The Do While Action is a SELECT statement that runs before subsequent Actions of the Step. If the Do While returns any rows of data, the subsequent actions in the step will be executed. You use a Do While Action to control the execution of the following actions. The Do While Action runs each time before the next Action is repeated. If the Do While returns any rows of data, all the Actions in the Step will be repeated. If no rows are returned, AE will move on to the next Step or Section.

The Do While is identical to the Cobol ‘While’ function. In other words, the subsequent Actions within the Step are executed in a loop as long as the SELECT statement returns at the least one row for the Do while Action.

***Loops as long as the condition is true. In short, if the Do While does not return any rows, the Step is complete.

DO WHILE counter<1000
Counte=counter+1

Do Until Action

A Do Until Action is a SELECT statement that runs after each Action, when a Step completes. If the SELECT returns any rows of data, the Step terminates.

Use a Do Until if you want the ‘processing actions’ to execute at least once, and to execute over and over until a certain condition is true, as in until a SELECT returns some rows.

We can also use a Do Until to stop a Do Select prematurely. For example of the SELECT for the Do Until does not return any rows, then the Actions in the Step are repeated (except if a Do When appears in the Step). Normally, a Do Select continues until no rows are returned. If any rows of data are returned, the Do Select stops and the actions in the Step are not repeated.

***Loops as long as the condition is FALSE.

DO UNTIL counter=1000
Counter=counter+1

Test Condition and the State Record:

All SQL SELECT statements in an AE Action must be preceded by a %SELECT(), causing the value to be written to the State Record. We generally use the TEMP_FLD to write the value to, which acts as a container for the ‘X’ retrieved from the DB if our condition is true. Once the ‘X’ is written to the field, there is no need for it. A subsequent ‘X’ statement in our AE program will overwrite it, and generally the character is not used in our processing.

%SELECT(TEMP_FLD)
SELECT ‘X’
FROM PS_INSTALLATION
WHERE %BIND(COUNTER) >10
Meta-SQL to write ‘X’ to State Record
If ‘X’ is returned this equals TRUE
Fills the requirement for a FROM table
Condition to be tested.

When we add an Action it automatically takes its place within the hierarchy. This way, while we are developing our program we can visualize the sequence in which our logic will execute.

The following shows an example of the Action Execution Hierarchy:

Do When
Do While
Do Select
PeopleCode
SQL / Call Section
Log Message
Do Until

Application Library

Most Application Engine programs are defined to be executable. An executable program has a main entry point in its definition: the Section MAIN. This defines the entry point so whatever method you choose to invoke the program, such as command line or Process Scheduler, can initiate the program. However, in some cases, we may want a program to only contain a collection, or “library” of common routines (in the form of “callable” Sections) that we do not want to run as standalone program. When sections are defined as “Public”, other programs can call the Sections, or routines, that exist in the “library” at runtime. Because this type of program is not designed to run as a stand-alone program, it does not require the MAIN section, or initial entry point. Setting the Application Library option renames or removes any existing MAIN section.

Application Libraries are the appropriate sport to store a collection of shared Application Engine program Sections. It is not intended to store a specific SQL Action within a Section. To share common SQL, use the SQL repository.

Key Points

1. The Application Engine has the ability toe make logical decisions to control the execution of Sections, Steps or Actions.
2. Do Flow Actions are:

· Do When Action allows subsequent Actions to be executed if any rows of data are returned in a SQL Select before the execution of the step.
· Do While Action allows subsequent Actions to be executed if any rows or data are returned in a SQL select
· Do Select will execute subsequent Actions once for every row of data returned in a SQL Select
· Do Until Action allows the Step to be return after the Step has run and before it loops again.

Do When => IF Condition logic
Do While => While Loop Logic
Do Select => FOR Loop Logic
Do Until => Repeat Until Logic

PeopleCode and Application Engine

In PeopleSoft 8, the ability to use PeopleCode in an Application Engine program was introduced. Keep in mind that AE is a development tool that allows to develop programs that execute SQL, in batch and online mode, against our data in a procedural and dynamic structure.

PeopleCode can be used for:

· Setting IF, THEN, ELSE logic constructs.
· Performing data ‘preparation’ tasks
· Building dynamic portions of SQL, while still relying on SQL to complete the bulk of the actual program processing.
· Reusing online logic that’s already developed
· Leveraging the new technologies – Business Interlinks, File Objects, Component Interfaces, Application Messaging.

PeopleCode in Application Engine Program

Inserting PeopleCode within AE programs provides an opportunity for us to reuse common function libraries and improve performance. It also can simplify development in some instances, as well. In many cases, a small PeopleCode program used instead of a Do When Action, for example, can replace a more complicated SQL statement.

There are few restrictions in terms of how we take advantage of PeopleCode in our AE program, however, as with most software development tools, there is an ‘intended’ use for a given tool. AE PeopleCode is an excellent way to build dynamic SQL, perform simple IF/THEN/ELSE edits, set defaults, and perform other tasks that don’t require a trip to the DB.

ü We do not need to concern yourself with formatting your code. When you save or validate the PeopleCode program, the system will automatically format the code for you. This holds true for anything except quoted literals, which are not formatted. Also PeopleCode is case-insensitive.

PeopleCode Basics:

There are 3 types of variables in PeopleCode, differentiated by the life span of the variable.

Variable Type
Life Span
Notes
Local PeopleCode Variables

PeopleCode Program
These variables are only available for the duration of the program in which they are declared.
Global PeopleCode Variables
AE Program
These variables are available while the AE program is running. They are saved at commits and checkpoints, so they can be used for restarts.
Component PeopleCode Variables
AE Program
Same as Global PeopleCode Variables for AE.

Variables are denoted with an ‘&’ prefix
Example: &MyRow, &Course

Local Variables:

Local variables do not need to be declared. They can take on the appropriate datatype when a value is assigned to the variable. However, Global and component variables must be declared.

LOCAL ;

Where LOCAL can be replace with GLOBAL or COMPONENT depending on the variable type. This declaration must appear before all other PeopleCode in our program.

can be the following:

Standard Types: Boolean, Date, DateTime, Time, Number, Object, String,
Any

Definition Types: Field, Record, Row, RowSet, AESection, Array, Component, File, BusinessInterlink, Message, Opertor, ProcessRequest, SQL.

Referencing Fields:

There are significant differences between Online and Batch modes. AE programs run in Batch mode so, our PeopleCode cannot access pages or controls as it can while running in Online mode. In short, any Peoplecode operations that manipulate pages will not run successfully. Even if you invoke your AE program “Online” from a Record or a Page using the CallAppEngine PeopleCode Function, the Application Engine PeopleCode still does not have direct access to the Page buffers.

Any RECORD.FIELD references that appear in a PeopleCode Action can only refer to fields that exist on an Application Engine State Records. Page buffers, controls, and so on are inaccessible even if you define the page records as State Records on the Program Properties dialog. An Application Engine program can only access State Records or other Definitions you create in PeopleCode.

There are multiple methods used to reference fields in PeopleCode. Of the various methods, there are only 2 that apply to Application Engine.

First, we can refer to the field using the Syntax .. For example, we would refer to an employee’s name using EMPLOYEE.NAME.

Second, we can use dot notation. For example, we can assign the employee’s name to a variable using the following syntax:

&Name=GetRecord(RECORD.Employee).GetField(FIELD.Name);

All PeopleCode statements should end with a semicolon – ‘;’. The PeopleCode editor will strip away any excess spaces or line breaks when the program is saved.

Date and Time operations

Operation
Result
Time + Number (no of seconds)
Time
Date + Number (no of days)
Date
Date – Date
Number (difference in days)
Time – Time
Number (difference in seconds)
Date + Time
DateTime

If then

else

end-if

*****************

Exit Statement

In the Application Engine, the IF-THEN statement is used to control flow of execution with the EXIT statement. The setting of the EXIT can cause the next Application Engine step to conditionally be skipped.

If &Test = 10 Then
Exit(0);
Else
Exit(1);
End-if

The parameter in the Exit Function controls the path the AE program will take

1 – indicates that the instruction to be done On Return will be done.

0 – tells the program to ignore the On Return Action of the PeopleCode Action.

The On Return options are:

Abort The program issues an error and exits immediately
Break The program exits the current Step and Section, and control returns to the calling Step
Skip Step The program exits that current step, and continues processing at the next Step in the Section. If this is the last Step in the Section, the calling Step resumes control of the processing.

Sequence Numbering

We can use the meta-SQL constructs %NEXT and %PREVIOUS when performing sequence numbering. Using these constructs may help performance in both PeopleCode and SQL calls.

Dynamic Calls

Rather than calling one specific Section, we can take advantage of the AE_APPLID and AE_SECTION fields in the State Record to execute different Sections depending on the conditions a program encounters during runtime. This is called Dynamic Call.

If we will only be using the Section within one AE program, then only the AE_SECTION field must be on the State Record. If we want to be able to call a section in other Application Engine Program, we will need to define both AE_APPLID and AE_SECTION on our State Record.

We enable a Dynamic Call by first having our program store different Section names in the AE_SECTION field, and different program names in AE_APPLID field. The values you insert into these fields needs to be based on various conditions met within our program. We then create a Call Section Action that call whatever Section name appears in the State Record field by selecting the Dynamic checkbox.

Check Points

1. The AE has the ability to use PeopleCode to make logical decisions to control the execution of Sections, Steps or Actions.
2. We can use the PeopleCode Action type with If-Then logic to control the flow of our AE program.
3. We can use PeopleCode in AE programs to perform computational functions.
4. PeopleCode can be used to control processing and to save time and overhead by not going to the database to make test.
5. This is mainly due to the fact that AE is most powerful when it executes SQL-based processing against your database.
6. PeopleCode is an interpreted language, so there is an inherent extra performance overhead when computed to compiled language.

Set Processing

Set Processing uses SQL to process groups, or sets, of rows at one time rather than processing each row individually. With Set Processing, you’d only select, or isolate, those rows that meet the filtering criteria and then run the rule once against all the effected rows.

In most cases, Set Processing is more efficient than row-by-row processing, especially when you enter the realm of high volume processing. Since Application Engine is designed specifically to process our high volume updates, Set Processing is vital to the development of the most efficient Application Engine Programs.

Set Processing allows applying our business rule directly on the data in the DB using an UPDATE or INSERT/SELECT statement. The bulk of the performance gain lies in the fact that the processing occurs in the DB as opposed to

· Pulling the data across the network, to our application program
· Processing it
· Transmitting results back across the network to be put into the DB tables.

Note: the only SELECT statements used in Set Processing appear in sub-queries of an INSERT or UPDATE statement.

Set Processing, Temporary Tables, and Application Engine program

Most Application Engine program require 1 or more Temporary Tables to contain transient data. This is especially true of programs that take advantage of Set Processing techniques. Although temporary tables are not necessarily required for Set Processing.

Creating temporary tables allow you to achieve one of the main objective involved with set based processing – the processing remains on the RDBMS Server. By storing transient data in temporary tables, you avoid the situation where the batch program fetches the data, row-by-row, and runs the business rule, processes the data and then passes the updated data back to the database. If the program were running on the client, you’d be taking performance hits due to the network roundtrip and the diminished processing speed of a client.

Temporary tables should be designed to accomplish the following:

· Hold transaction data for the current run or iteration of your program.
· Contain only those rows of data affected by the business rule
· Present key information in a denormalized or ‘flattened’ form, which provides the most efficient processing.
· Switch the keys for rows coming from the master tables if needed. A transaction may use a different key than what appears on the master tables.

Identify Temporary Tables

Most applications will require 1 or more temporary tables to contain transient data. This is especially true of applications that take advantage of Set Processing techniques. Normally, we would name temporary table something that ends with “_TMP”.

AE has Checkpoint/Restart capabilities, these tables should not really be considered temporary. To protect the overall integrity of the batabase, give these tables name that end with “_TAO”.

There is a special record type “Temporary Table” that is used with AE programs for parallel processing. If you only need one instance of a temporary table, and SQL table could be used. For this activity, we will use a record type of “SQL Table”.

Key Points

1. Application solutions designed to take advantage of Set Processing improve the performance of an AE application
2. Set Processing can yield:
· Improved performance
· Minimized (PeopleTools) SQL overhead
· Easier maintenance
· Leveraged power from the RDBMS

Temporary Tables

The ability for multiple instances of the same program to run simultaneously is called Parallel or Concurrent processing.

Since AE programs run in Batch, it is very likely that multiple instances of the same program can be running in parallel. When batch programs being running simultaneously, they introduce a significant risk of data connection and dedlocks on common tables and/or temporary tables.

For AE programs, PeopleTools provides a feature that enables you to drastically reduce the risk of table contention by dedicating specific instances of temporary tables for each program run. With this feature, we have a pool of temporary table instances, and within that pool some tables are dedicated to particular programs, and some instances are undedicated, meaning they are shared.

Parallel Processing – Why?

For batch process there can be potential performance improvements by spilitting the data to be processed into groups and simultaneously running multiple instances of our program to deal with different groups of data.

For example if we have an inventory of 1,000,000 items you need to process, consider the following time lines to run the following 3 cases

1. All 1,000,000 with one execution of our program

-------------t--à------------------------------------

2. 500,000 each in 2 executions of our program, serially

--------t-à------------------------t-à-------------

3. 500,000 each in 2 executions of our program parallel

--------t-à--------------
--------t-à--------------

If you have a program that uses a Temporary Table and is invoked multiple times, that single Temporary Table could be used concurrently in multiple executions of the code.

This could create unpredictable results since the different instances of the code would be issuing Deletes, Inserts, and/or Updates unsynchronized with each other.

We could solve the problem by creating multiple temporary tables as a pool of tables. Each invocation of our program would have to allocate an unused temporary table, mark it as ‘in use’ use it and release it back to the pool when you are through with it (for each AE program we write)

Implementing Parallel Processing

There is no simple switch or checkbox that enables you to turn parallel processing on and off. To implement parallel processing, you need to complete a set of tasks in the order that they appear in the following list

1. Define your Temporary Tables by defining and saving your Temporary Table records as type Temporary Table.
2. Build the Temporary Table Pool. This will build the basic Temporary Table Pool based on the PeopleTools options specifications.
3. Assign Temporary Tables to your AE Program in its Program Properties, setting the appropriate number of instance counts and Runtime options.
4. Build/Rebuild your Temporary Table record. This will build the necessary Batch temporary tables into that record’s Temporary Table pool for use at execution time.
5. Code %Table meta-SQL as references to Temporary Tables in your AE program, so that AE can resolve table references to the assigned Temporary Table instance dynamically at runtime.

The Temporary Table Instance (Total) specified on the PeopleTools Options page.
Plus the sum of all instance count values specified in all of the AE programs that use that Temporary table.

Application Designer only creates a maximum of 99 Temporary table instances.

There are 2 fields that are used in connection with the temporary table instances.

Temp Table Instances(Total)
Temp Table Instances(Online)

These 2 values should always be the same except for EPM

EPM Managed Temporary tables

The difference between the Temp Table Instances(Total) and Temp Table Instances(Online) will be the EPM Managed Temporary Tables

The naming convention for the Temporary Table instances is as follows:
nn
where nn is a number between 1 and 99.

PeopleTools Options:

Temp Table Instances (Total):5
Temp Table Instances (Online):3

Application Designer Build

OnlineTEMP_TA05
TotalTEMP_TA04
EPM ManagedTEMP_TA03
TEMP_TA02
Base TableTEMP_TA01
TEMP_TA0

Reference Temporary Table (%Table)

To reference a temporary table (Online or Batch), we need to use:

%Table(record)

We can reference any table with %Table, but only those records defined as Temporary Tables get replaced by Application Engine with an numbered instance of a Temporary Table from the Temporary Table pool.

Clear Temporary Table (%TruncateTable)

We do not need to delete data from a Temporary Table manually. The Temporary Tables are truncated automatically when they are assigned to our program. If the shared base table has been allocated because no batch/dedicated instances were available. Then AE performs a delete by process instance instead of performing a truncate. In such case, the PROCESS_INSTANCE is required as a high-level key.

We can perform additional deletes of Temporary Table results during the run, but you will need to include your own SQL Action that does a %TruncateTable.

Run Time Allocation

Depending on how we invoke a program the manner in which it allocates a Temporary Table is different. The distinction between invocation methods fall into 2 categories:

1. Online
2. Batch

1. Online Processes

Online processes have their own set of dedicated Temporary tables, defined globally on PeopleTools Options.

When you invoke a process Online, PeopleTools randomly allocates a single Temporary Table instance number to ProgramX for all its dedicated temp table names. While programX runs, no other program can use that instance number assigned to programX until programX has run to completion. Any other Online process that happens to get the same instance value as programX will wait for the look to be released when programX completes and the instance number is unlocked.

2. Batch Processes

For Batch processes, PeopleTools allocates the batch/dedicated table instance numbers on a record-by-record basis. PeopleTools begins with the lowest instance number available for each Temporary Table until all of the Temporary Table instances are in use. If there are not any temporary table available and you indicated Continue for the If non-shared Tables cannot be assigned, then the base table will be used with the process instance number as a key.

When a program ends normally or is cancelled by way of Process Monitor, the system automatically releases the assigned instances.

Application Engine Execution

We can invoke AE programs 3 modes:

1. Batch
2. Online
3. Manual

1. Batch

This is the most typical mode of execution. We invoke programs that run in this mode using Process Scheduler or the AE Process Request page. Batch mode is also referred to as asynchronous execution meaning that it runs independently in the background.

2. Online

AE programs that execute online, typically get executed from a page with the CallAppEngine PropleCode function. Such Online processes are synchronous meaning that subsequent processes wait on the results. For instance, a page may ‘frozen’ until the Online process returns the necessary results. When the program is called synchronously, no COMMITs are issued until the program completes.

3. Manual

To execute an AE program in manual mode, we would use the command line. Usually, we only use this technique during testing or if you need to manually restart the program.

1. Batch Mode 2.Online Mode 3.Manual Mode
Asynchronous Process Synchronous Process
Process Scheduler CallAppEngine Command Line
PeopleCode

Process Scheduler
Request Push Button Bat File
AE Process
Request Page

PSAE.exe

Key Points

1. We can execute AE programs in a number of different ways appropriate to the type of user that is initiating the program.
2. we can initiate AE program from pages with Push Button set for Synchronous or Asynchronous execution.
3. We can run AE program in or out of the Process Scheduler.

Application Engine Process Definition

We use Process Scheduler Manager to set and create Process Types, Process Definitions, and Job Definitions to integrate Process Scheduler in PeopleSoft applications from our browser. For each AE program that we want to process via the process scheduler, we need to create Process Definition and a component designed to control the runtime parameters. PeopleSoft provides standard records and pages from which to clone.

PeopleTools => Process Scheduler Manager => Use => Process Type Definitions

Command Line: points to the executable program PSAE.exe, a PeopleSoft API program to run AE programs.

Enclose local environment (Client side) strings within a single pair of percent signs:%TOOLBIN%.

Enclose all Server environment (Server side) strings with a double pair percent signs: %%TOOLBIN%%

Parameter List: contains the string command line variable passes to the executable. The parameter list follows the same definitions as we saw for the command line. The variable are denoted by the %% and are retrieved from the system at run time.

Parameter
Variables (meta-strings)
Description
-CT
%%DBTYPE%%
Database Type
-CD
%%DBNAME%%
Database Name
-CO
%%OPRID%%
User ID
-CP
%%OPRPSWD%%
User Password
-R
%%RUNCNTLID%%
Run Control ID
-I
%%INSTANCE%%
Process Instance
-AI
%%PRCSNAME%%
Process Definition

Process Scheduler uses a number of variables during the run time. These are in the form of meta-strings, in-line bind variables, or client/server variables.

% - Client side Variable
%% - Server side Variable

C:\PT812\CLIENT\WINX86\PSAE –CT MICROSOFT –CD -CO PTTRN –CP PTTRN –R PROCESS_ORD2 –AI PROCESS_ORD2

Steps to Add Appliction Engine Process

1. Design
2. Identify initial Bind Variables
3. Determine Run Control Record
4. Build the Record
5. Determine the Run Control Page
6. Place the Run Control Page on a Component
7. Place the Component on a Menu
8. Create a Process Definition
9. Grant Access to the Component
10. Test

Run Control Record

The most common way to request an AE program through the Process Scheduler is from a Run Control component. To build a component, we must start with a record.

PropleSoft provides a standard Run Control Record for processes with no user input: PRCSRUNCNTL. This record contains 4 fields.

1. OPRID Key Field
2. RUN_CNTL_ID Key Field
3. LANGUAGE_CD
4. LANGUAGE_OPTION

The Fields OPRID and RUN_CNTL_ID contain the following PeopleCode:

OPRID - RowInit - OPRID=%OperatorId
RUN_CNTL_ID- Save Edit - Ensures no blanks in value

Key Points

1. A process type definition is used to define the command line, parameter list, working directory, and other general parameters for a process that can be run through the Process Scheduler.
2. AE Generic Test Type can be used to run an AE program without creating the process definition (for testing).
3. A Process Definition defines the AE program to the Process Scheduler.
4. PeopleSoft supplies some standard run control records and pages that can be used or cloned.
5. The steps to add a new AE program to the Process Scheduler are:
· Design
· Identify initial bind variables – these will need to be included on the Run Control Page
· Determine the Run Control Record
· Build the Record
· Determine the Run Control Page
· Place page on a component
· Place component on a Menu
· Add a Process Definition
· Grant access to the Component
· Test the application

Appliction Enginer Meta-SQL

Meta-SQL
Description
%Bind

%Select

%SQL

%Table

%TruncateTable

%ExecuteEdits

%InsertSelect

%Join

%List

%SelectInit

%UpdateStatus

Application Engine Macros

Meta-SQL
Description
%ClearCursor

%Execute

%Next
%Previous

%RoundCurrency

Application Engine System (Meta) Variables

Meta-SQL
Description
%AeProgram

%AeSection

%AeStep

%JobInstance

%ProcessInstance

%ReturnCode

%RunControl

%AsOfDate

%SQLRows

%LeftParen

%LeftParen

%Space

%Comma

Meta-SQL Date & Time Functions

Meta-SQL
Description
%CurrentDateIn

%CurrentDateOut

%DateIn(dt)

%DateOut(dt)

%DateTime(dtt)

%DateTimeOut(datetime_col)

%TimeIn(tm)

%TimeOut(tm)

Difference between Writeline and WriteString in AppEngine PeopleCode

2009-07-01T02:07:00.000-07:00

Write String prints the data continuously in one line.

Write line prints the data line by line

To overwrite search record on component by using peoplecode

2009-06-29T01:01:00.000-07:00

Provide the search keys values in searchinit event.
Use Setsearchdialogbehaviour() function.
Make sure, in the component properties,"Force Search processing" is switched off.

Code to print a image in SQR

2009-06-29T01:00:00.001-07:00

let $imgpath = $DB_Server_Path || 'logo.jpeg'
print-image (1,1)
type=jpeg-file
image-size=(75,9)
ource= $imgpath

SQR Migration Strategies

2009-06-29T00:54:00.000-07:00

your expert comments and thoughts are invited to further refine this tool.

Type of SQR Migration Strategy

Inbound Interface – Data source: File File Layout + AE + CI if insertion is done to a component

Inbound Interface – Data source: Peoplesoft

App Messaging/IB

Mass Data Changes (Mass hires, Mass update of a table etc.)

AE + CI if updation to a component is done. Analyse whether the Peoplesoft Mass Change functionality can be utilized.

Outbound Interface (Flat files, Dataset)

AE + XML Publisher for report generation/ Web Services if 3rd party supports

SQCs

Custom SQCs can be replaced by Application Classes to be invoked from Peoplecode.

Utility programs (Clean up, File manipulation, Audits, Status Checks)

Audits, File Operations, OS related processes are better done by SQRs, retain them. Utilities that affect database can be replaced by AE.

Letters and Forms (online forms as well as letters that are mailed)

XML Publisher + Peoplecode notification classes for mails

Reports – Simple single data source (reports with one BEGIN-SELECT)

XML Publisher

Reports – Simple multiple data sources (reports with more than one BEGIN-SELECT and simple calculations)

Migration of these might require the use of AE to execute the calculations and XML/XSL modifications. Move to XML Publisher if the logic is not complicated, otherwise retain SQRs.

Reports – On-breaks and multiple reports

XML Publishers

Reports – Complex with multiple BEGIN-SELECTS and complex conditional logic

Do not migrate – the effort is not worth it.

Reports – Status of batch process

This category contains the production of error reports and status reports of processing done by an AE or other batch programs – move to XML Publisher.

SQR Optimisation Techniques

2009-06-29T00:51:00.001-07:00

1. Check your SQL queries: Most of the performance issue can be attributed to poorly written SQLs. Critically review each SQL statement of your SQR and ensure their correctness.

2. Usage of Temporary Tables: This is number two on my list. A strategy that I adopt when multiple tables have to be joined and the report involves complex processing, is to pull out the master data and insert the same into a temporary table that has been created in the begin-setup section of the SQR. Further processing can be done by referring the data in the temporary table and this reduces trips to transaction tables whereby giving us significant performance enhancements.

3. Usage of arrays: Arrays are excellent instruments to improve the maintainability of the program. I would always use arrays to hold values of totals and other variables that are to be re-initialised multiple times in the report (note that the values of an array can be reset by using the clear-array command). If variables were used instead, the number of variables that would have to be maintained, the initialisation routine etc. would add to the complexity to the program.

4. Dynamic SQLs: The usage of dynamic sqls greatly reduces the length of a program and helps in improving its performance. One requirement I had was to generate two reports with the same output fields and filter criteria, but one sorted by EMPLID and another by NAME. In this case, the only difference was the ORDER BY clause and instead of writing two different BEGIN-SELECTs, the requirement was achieved by maintaining a single BEGIN-SELECT and passing the order by clause as a dynamic SQL.

5. Break down your SQLs: This is something you will find in Peoplebooks also. Make sure that you are querying the database only when necessary. Let's say you have to produce a report which lists the EMPLID, NAME, BIRTHDATE, MANAGER'S NAME and JOBCODE DESCRIPTION. This will involve the join of 4 tables (including PS_NAMES aliased twice) and considering the key structure of these tables, will take considerable execution time. An alternate strategy would be to have a different procedure to get the JOBCODE descr where only the JOBCODE_TBL is queried for instead of making it a part of the join.

6. Minimal code and re-usable logic: The beauty of SQRs is that it is the most flexible tool in the Peoplesoft technical environment (especially considering the rigidity and ready made nature of an AE, CI or Peoplecode) and offers the developer multiple paths to one solution. Evolve a logic that re-uses your procedures and minimises database trips.

What about Load Lookups? Documentation says that Load Lookups give significant performance enhancements. Practically I have not been able to achieve this. The look up gets loaded at the start of the report and the time taken to load the lookup offsets any advantage that this concept delivers. Usage of temporary tables is a much better alternatives - in effect the concept of the two are inherently the same - reduce database trips and querying of large transaction tables.

Auto creation of user profiles

2009-06-29T00:47:00.000-07:00

There are multiple scenarios where its required to create new user profiles in bulk.
The best approach to this problem is to achieve the same by a CI built on the USER_SAVEAS component (Copy User profile component).
Was mulling over the various options to auto generate passwords for the user profiles and ran into the delivered function in DERIVED_HINT.EMAILPSWD.Fieldformula event.

Dynamic Views as Prompt Tables

2009-06-29T00:42:00.000-07:00

Dynamic Views are an excellent object type provided by Peoplesoft - a pseudo view that can take any shape! They are excellent candidates where the SQL condition of a view changes dynamically according and are not even a database objects.
There are two major limitations I have come across when you use a Dynamic View as a prompt:
1. If the SQL of the dynamic view contains joins and if the search fields are used in the join.
2. If the SQL of the dynamic view contains joins.

What causes the failure is that the component processor takes the search fields from the prompt and appends to the SQL statement like 'and = '. Now, unfortunately the processor does not do any aliasing which causes the common 'ambiguous column definition' SQL error.

To overcome this, either put the SQL in a view and query from the view or better, treat the SQL as an inline view and query from the inline view.
For example, if the initial SQL of the DV was something like:
SELECT EMPLID FROM PSOPRDEFN UNION SELECT EMPLID FROM PSOPRDEFN, for this to work in a dynamic view, it has to be called from an inline view like:
SELECT EMPLID FROM (SELECT EMPLID FROM PSOPRDEFN UNION SELECT EMPLID FROM PSOPRDEFN)

Gray out an entire page except one field using Peplecode

2009-06-29T00:39:00.000-07:00

Option 1 : Use GRAY() function in the page activate event to gray out individual fields.
Option2: Make page display only. and then in the peoplecode use enable property for the field that needs to be enabled.
OR
Loop through
&Rec= Your Record
for &i =1 to &rec.fieldcount
if &rec.field.name <> 'your req field' then
&rec.field.displayonly =true;
else
&rec.field.displayonly =true;
end-if;
end-fo

To display the long name from translate table and display it output in SQR

2009-06-29T00:05:00.000-07:00

To display the long name from translate table and display it output in SQR

BEGIN-SELECT
XLATLONGNAME (,132)
FROM
PSXLATITEM
WHERE
FIELDVALUE = &RELATION
AND FIELDNAME = 'RELATIONSHIP'
END-SELECT
(Note:Call it in a procedure)

String to Date conversion (30-dec-2009 to 12/30/2009) in PeopleCode

2009-06-29T00:00:00.000-07:00

Below is the code to convert the string to date (30-dec-2009 to 12/30/2009)
&str1 = &array [5];
If &array [5] = "NULL" Then
&oSStu2Tbl.date = "" | "NULL";
&fileLog.WriteLine("&oSStu2Tbl.date = " | &oSStu2Tbl.date);

End-If;
If &str1 <> "" And
Len(&str1) = 9 Then
&str2 = Substring(&str1, 1, 2);
&str3 = Substring(&str1, 4, 3);
Evaluate &str3
When = "Jan"
&mm = 1;
Break;
When = "Feb"
&mm = 2;
Break;
When = "Mar"
&mm = 3;
Break;
When = "Apr"
&mm = 4;
Break;
When = "May"
&mm = 5;
Break;
When = "June"
&mm = 6;
Break;
When = "July"
&mm = 7;
Break;
When = "Aug"
&mm = 8;
Break;
When = "Sep"
&mm = 9;
Break;
When = "Oct"
&mm = 10;
Break;
When = "Nov"
&mm = 11;
Break;
When = "Dec"
&mm = 12;
Break;

End-Evaluate;

rem &b = Month(&str3);

&str4 = Substring(&str1, 8, 2);
&dte = &str2 | "/" | &mm | "/" | &str4;

&oSStu2Tbl.S_STU_DATE = &dte;
&fileLog.WriteLine("&oSStu2Tbl.date = " | &str2 | "/" | &mm | "/"|&str4);
End-If;

If &str1 <> "" And
Len(&str1) = 8 Then
&str2 = Substring(&str1, 1, 1);
&str3 = Substring(&str1, 3, 3);
Evaluate &str3
When = "Jan"
&mm = 1;
Break;
When = "Feb"
&mm = 2;
Break;
When = "Mar"
&mm = 3;
Break;
When = "Apr"
&mm = 4;
Break;
When = "May"
&mm = 5;
Break;
When = "June"
&mm = 6;
Break;
When = "July"
&mm = 7;
Break;
When = "Aug"
&mm = 8;
Break;
When = "Sep"
&mm = 9;
Break;
When = "Oct"
&mm = 10;
Break;
When = "Nov"
&mm = 11;
Break;
When = "Dec"
&mm = 12;
Break;

End-Evaluate;

rem &b = Month(&str3);
&str4 = Substring(&str1, 7, 2);
&dte = &str2 | "/" | &mm | "/" | &str4;
&oSStu2Tbl.S_STU_DATE = &dte;
&fileLog.WriteLine("&oSStu2Tbl.date = " | &str2 | "/" | &mm | "/" | &str4);
End-If;

How to delete process definition

2009-06-26T03:25:00.000-07:00

The BEST way to delete any PeopleTools definition that you can insert as an object into application designer is by setting the action to delete for the relevant definitions, copying the project to file and reimporting the project

The exact steps are covered below:
http://peoplesoft.wikidot.com/deleting-definitions

PeopleTools Tables

2009-06-26T03:22:00.000-07:00

Projects

* PSPROJECTDEFN — Project header table
* PSPROJECTITEM — Definitions in the project

Fields

* PSDBFIELD — Fields in the system
* PSXLATITEM — Translate Values

Records

* PSRECDEFN — Record header table
* PSRECFIELD — Fields in the record (subrecords not expanded)
* PSRECFIELDALL — Fields in the record (subrecords expanded)
* PSKEYDEFN — Indexes
* PSTBLSPCCAT — Tablespaces
* PSRECTBLSPC — Records’ tablespace assignments

Pages

(Note: Pages still have the name panels in the PeopleTools table names)

* PSPNLDEFN — Page header table
* PSPNLFIELD — Page controls (field types/FIELDTYPE)
* PSPNLHTMLAREA — Static HTML Areas on Pages

Components

(Note: Components still have the name panel group in the PeopleTools table names)

* PSPNLGRPDEFN — Component header table
* PSPNLGROUP — Pages in the components

Component Interface

* PSBCDEFN — header record; one row for each component interface
* PSBCITEM — one row for each property

Menus

* PSMENUDEFN — Menu header table
* PSMENUITEM — Items (components) on the menu

Security

* PSCLASSDEFN — Permission List header table
* PSAUTHITEM — Menu items granted security by permission lists
* PSROLEDEFN — Role header table
* PSROLECLASS — Permission Lists in roles
* PSOPRDEFN — User ID header table
* PSROLEUSER — Roles granted to users

Process Scheduler

* PS_PRCSDEFN — Process Definition Header
* PS_PRCSDEFNGRP — Process Group
* PS_PRCSDEFNPNL — Component
* PS_PRCSJOBDEFN — Job Header
* PSPRCSRQST — Process Request Instances
* PS_PRCSJOBITEM — Job Processes

Portal

* PSPRSMDEFN — Content References and Folders
* PSPRUHTABPGLT — Portal User HP Tab Pagelet

Change Control

* PSCHGCTLHIST — shows history of locked definitions with project name, incident, and description
* PSCHGCTLLOCK — shows definitions that are currently locked

Application Engine

* PSAEAPPLDEFN — header record; 1 row per app engine
* PSAEAPPLSTATE — state records assigned to app engines
* PSAEAPPLTEMPTBL — temp tables assigned to app engines
* PSAESECTDEFN — sections
* PSAESTEPDEFN — steps
* PSAESTEPMSGDEFN
* PSAESTMTDEFN — actions (action types)

List Log Messages

HTML Definitions

* PSCONTDEFN — header record; last update time, etc.
* PSCONTENT — stores actual text in the HTML definition

SQL Definitions

* PSSQLDEFN — header record; last update time, etc.
* PSSQLTEXTDEFN — stores actual text in the SQL definition

File Layout Definitions

* PSFLDDEFN — header record; last update time, etc.
* PSFLDSEGDEFN — stores the segments for each layout
* PSFLDFIELDDEFN — stores the fields for each layout

Workflow

* APPR_RULE_DETL - Approval Rule Defn Details
* APPR_RULE_FIELD - Approval Rule Defn Route Cntl
* APPR_RULE_AMT - Approval Rule Amounts
* RTE_CNTL_LN - Route Control Profile Line
* RTE_CNTL_RUSER - RoleUser Route Cntl Profiles
* RTE_CNTL_TYPE - Route Control Type
* RTE_CNTL_HDR - Routing Control Type

To write Images along with HTML text into a file created in an App Engine in PeopleSoft

2009-06-26T02:12:00.001-07:00

It's possible to write images along with HTML text.
Sample Code:
1. Create a new HTML Object as follows:
!html <>
!body
!img src="cid:23abc@pc27" /
!/html
!/body
cid stands for the Content Id
2. Sample PeopleCode:
Local string &STRLURL = GetURL(URL.IMG_PATH);
&image.SetAttachmentContent(&STRLURL, %FilePath_Absolute, "sample.jpg", "This is a sample image!", "", "");

/* The Content ID in the code (Highlighted) must be same as Content ID in HTML */

&image.AddHeader("Content-ID", "23abc@pc27");
We have used this to display html image and text in email notifications. You could try it for documents and let us know if it works for you.

Reasons why PeopleSoft does not recomd SQLEXEC()

2009-06-26T02:11:00.002-07:00

PS does not recommend sqlexec(), to avoid direct database calls. As the main reason for 3 tier architecture was to execute the business logic on the application server so that the application can be made database independent. This means if you want to change the database of any application from one vendor to another, you should be able to do it easily.

If you use sqlexec() calls, this purpose is not met as you may use sql which is very specific to the database. However if you use Peoplesoft way of creating a record, for example

RC_CASE is record with key as CASE_ID

Local record &lrec = createrecord(record.rc_case);
&lrec.case_id.value = 123;

Now if you want to select from RC_CASE, you would use sqlexec() as sqlexec("select case_closed from ps_rc_case where case_id=:1",123,&caseclos);

Instead you can also use the record instance as
&lrec.selectbykey();

The above will populate the rc_case record with all the values and you can use it. Also in this case Peoplesoft forms the sql based on the database, so if the organization using this application decides to change the database from Oracle to MS-SQL Server, you will not have to change the code for above.

You are not authorized for this page error in PeopleSoft

2009-06-26T02:11:00.001-07:00

These are the steps you will have to follow further to avoid the error:
a) Go to Peopletools > Portal > Structure and Content. (Navigate to your component)
b) Go to security tab
c) Change the security to public(makes all permission list to go) and now again Permission List access (making all permission list to reappear).
d) Save the page
e) Check if the Permission list is shown after the save..
f) Now sign-out and delete the application server and browser cache.
g) Try login again and access the page.

XML navigations Creating the data Source through PS QUERY (Reporting Tools) in PeopleSoft

2009-06-26T02:10:00.002-07:00

Creating the data Source through PS QUERY (Reporting Tools)

Defining Data source:

1.Click on Reporting Tools.
2.Click on XML Publisher.
3.Click on Data Source.
4.Click on Add a New Value.
5.Select the Data Source type as PS Query.
6.In Data Source ID select ur Query (Ex:HARSHA_QRY)
7.Click on ADD.
8.Enter the Description Name (Ex:Harsha)
9.Selct the Object Owner ID as Human Resource.
10.Click on Save.

Step2: Defining Data Source

11.In the Related Files-->Sample Data File-->Click on Generate
12.In the Related Files-->Schema File-->Click on Generate.
13.Click on Save.
14.Open the XML file and Save As Harsha1_Qry on Desktop.

Step3: Building/Creating Template:

1.Open MS Word.
2.Click On Add-Ins.
3.Click on Template Builder-->Data-->Load XML data.
4.Click on Template Builder-->Insert-->Table/ Form
5.Drag and dorp the Rows in to Template Work sapce and click on All Nodes.
4.Click on Ok.
5.Save the MS Wrod sheet with same name given to the XML(HARSHA_QRY) with RTF

Step4: CLICK ON REPORT DEFINITION

1.Select the Existing saved Report(HARSHA_QRY)
2.In the Object owner Id make it empty.
3.Clickon template tab
4.Change the status to Active.
5.Click on Upload.
6.Selct the Existing Word doc
7.Click on Save.

CLICK ON QUERY REPORT VIEWER

1.In the Query Report Viewer select ur existing report.
2.Click on View Report.

Creating Component Interface in Application Designer and giving permissions to Component Interface in PeopleSoft

2009-06-26T02:10:00.001-07:00

Creating Component Interface in Application Designer:
1. Open new Component Interface.
2. Select the Component
3. Save the Component Interface with _CI.
4. Drag and drop the component Interface in to the Application Engine Programs
(Which generates the People code automatically. Edit it )

To test the component Interface:
1. Right click Component Interface
2. Click on Test Component Interface.

Steps to give permissions to CI:
1. People Tools-->Security-->Permissions & Roles-->Permission Lists
2. Add HCCPFGALLP (Federal all pages)
3. Click on Component Interface tab.
4. Add the Component interface.
5. Select thr Coponent Interface and click on Edit.
6. Click on Full Access and click on Ok.
7. Save it.

Steps to replace DropDownbox in place of Prompt table and Inserting a static Image into a page in PeopleSoft

2009-06-26T02:09:00.003-07:00

STEPS TO FRO DROP DOWN BOX IN PLACE OF PROMPT TABLE

1.Delete the filed prompt button from the page (counrty)
2.Click on Insert-->Drop Down List box
3.Double click on the drop down list box.
Select the record name:TIN1_EMP_TBL
Select the Field Name:TIN1_COUNTRY
Select the radio button :Prompt Table Field.
Select the Feild as TIN1_COUNTRY from the drop down list in Prompt Table Field
4.Click on OK.
5.Save the project.

STEPS FOR INSERTING THE STATIC IMAGE IN TO THE PAGE
1.Open a new Image (Ctrl+N-->Image)
Save it and click on F7
2.Click on Insert-->Static Image
3.Double click on it and browse the Image.
4.Click on OK.
5.Save it.

Steps for creating Secondary page with Push Button, Grid and Sate Record in PeopleSoft

2009-06-26T02:09:00.001-07:00

Creating Secondary Page With Push Button:
-->Create the requried Fields(Ex:Email,Alternative Adress, Phone No)
-->Drag and drop the field in the Record.
-->Open New Page-->Right Click-->Select Page Properties-->Select Secondary Page fro the drop down box
-->From the record Drag & Drop the Email to the Secondary Page
-->Save it
-->Open the Mian Page-->-->Click on Insert-->Push Button/Hyper Link-->Doubble click on the icon in the Main page
-->Select the destination as:secondary page
-->Record Name as:HARSHA_EMP_TBL\
-->Field Name as:HARSHA_EMP_EMAIL
-->Secondary Page as:HARSHA_EMAIL_PNL
-->Click xon the Label tab-->Select Standard Text
-->Select Static Text
-->Enter the Text as: Email
-->Save it

Creating Grid:
1.Open a new Page.
2.Click on Insert-->Grid
3.Now drag and drop the records in to the grid
4.Save it (Ex:-HARSHA_EMP_PNL)
5.Click on F7.

Creating State Record:
1.Open a New Record.
2.Drag and drop the necessary field in to it
3.Save it with _AET extension (Ex:-HARSHA_EMP_AET)
4.Click on F7.

Steps for creating Run Control ID in PeopleSoft

2009-06-26T02:08:00.001-07:00

Creating Run Control Page from the Existing Fields
Open New Page.
Click on insert -->Subpage
Select Subpage as PRCSRUNCNTL_SBP
Select Record Name as PRCSRUNCNTL
Click on ok
Save it as HARSHA_RNCTL_PNL
Click on F7.

Creating Own Run Control Page:
1.Click on Insert-->OPRID
2.Click on Insert-->RUNCNTLID.
3.Click On New-->Field(Employee ID)
4.Click on New-->Record
5.Drag and drop all the 3 records in to it.
6.Make both RUNCNTLID and EMPID as Keys
7.Select the Employee ID-->Double click onit.
8.Click on Edit Tab.
9.Select the Radio Button Table Edit.
10.Select Prompt Table as SRI_EMP1_VW.
11.Save It.

Steps for creating Field,Record,Page,Component and Menu in PeopleSoft

2009-06-26T02:07:00.000-07:00

Creating the Fields:
-->Open the Application Designer.
-->Click on File->New
-->In the New Definition Pop Up Window Select Field and double click on it.
-->Select the Field Type as Character.
-->Select the appropriate Field Length (Ex:-20)
-->In the Label ID give the appropriate name (Ex:-AST_HARSHA_EMP_ID)
-->In the Long Name give the appropriate name (Ex:-Employee Name)
-->In the Short Name give the appropriate name (Ex:-EName)
-->Save the Project with appropriate name (Ex:-AST_HARSHA_EMP_ID)
-->Click on Insert-->Click on Current Definition in to project or Hit F7.
-->REPEAT THE SAME ABOVE PROCESS FOR EMPLOYEE NAME.

Creating a Record: (TBL)
-->Click on File-->New
-->In the New Definition Pop Up Window Select Record and double click on it.
-->Drag and drop the Emp_Id and Emp_Name from the Fields.
-->Double click on the Record's and the Record Field Properties Pop Up Window Opens.
-->In Record Field Properties Pop Up Window Select the Key Checkbox and click on OK.(This is Option which is used to establish the relation)
-->Click on Record Type and select Sql Table radio button.
-->Save as AST_HARSHA_TBL
-->Click on Ok
-->Click on Insert->Click on Current Definition in to project or Hit F7.
-->From the Records select AST_HARSHA_TBL right click and click on Build-->In Build Options select the checkbox's Create Tables in Build Options and Execute SQL now in Build Execute Options
-->Click on Build.
-->After the process getting completed close the Build Progress window
-->Open SQL server
-->Execute the SQL statement by selecting the appropriate database (Ex:-HCMTRA90)
-->select * from PS_AST_HARSHA_TBL (Hit on F5).
Creating a Page: (PNL)
-->Click on File-->New
-->In the New Definition Pop Up Window Select Page and double click on it.
-->Resize the Page by Right click->Page Properties-->Use-->Select the required page size and click on Ok.
-->Drag and Drop the Records
-->Align the page
-->save the page as AST_HARSHA-PNL
-->Click on F7
Inserting the Scrolling : ( Which allows inserting Multiple Values at run time)
-->Click on Insert
-->Click On Scroll Area
-->Double click on the scrolled area.
-->Click on Label
-->Uncheck the Display footer check box.
-->Click on the Properties of Display Title.
-->In the Text enter the Name to be displayed.
-->Click on Ok.
Components:
-->Click on File-->New
-->In the New Definition Pop Up Window Select Component and double click on it.
-->Drag and drop the Page Component.
-->Re Name the Item Label (Ex:-Harsha Employee Id)
-->Save it as AST_HARSHA_CMP
-->Then in the Component Properties in the Search Record Field select the file (Ex:-AST_HARSHA_TBL)
-->Click on Ok.
-->Save it as AST_HARSHA_CMP
-->Hit on F7.
-->Select the component file(Ex:-AST_STUDENT_TBL).
-->Right Click and click on Build-->Click on Yes
-->Select the checkbox create tables in Build Options.
-->Select the Radio Button Execute SQL now in Buid Execute Options
-->Click On settings
--> Click On Logging tab
-->Select/enable the Log Settings check Box
-->Click on Ok.
-->Click on Scripts Tab
-->Select the Radio Button Output to single file and in script file names give the path to store the scripts as separate files
-->Click On ok
-->Click On Build.
Creating Menu:
-->Click on File-->New
-->In the New Definition Pop Up Window Select Menu and double click on it.
-->Select Standard from the New Menu and click on Ok.
-->Double click on the Dotted area.
-->In the Bar Item Properties pop Up window Name it as AST_HARSHA_PROCESS
-->Name the label as Harsha.
-->From the component darg and drop the AST_HARSHA_CMP (GBL) in the process sub menu
-->save it as AST_HARSHA_MENU
-->Hit F7
-->Click on File-->Click on save project-->HARSHA_EMP
-->Select the process sub Menu-->Right Click-->Click on register Menu Item
-->Click on next
-->Select Folder Name as AST_HARSHA.
-->OBJECT OWNER ID: HHR (Human Resource)
-->Select the check box Always use default node.
-->Click on Next
-->Click on Select and In Select Permission List -->In Name field give as HCCPFGALLP
-->Click on Next
-->Select the Permission List Check Box
-->Click on Finish.

Steps for creating a Sub Record, Sub Page and Secondary Page In PeopleSoft

2009-06-26T02:06:00.002-07:00

Steps for creating a sub record:
1. Create necessary fields.
2. Open a new record and drag and drop the necessary fields in to the record
3. Save it with _SBR and Build the Project.
4. Open the Existing record which we want to insert the sub record into it.
5. Click on Insert-->Subrecord
6. Select the sub record (ex:AST_STU_SBP) and click on insert.
7. Build the project.
8. Save it.

Steps for creating a sub page:
1. Click on New-->Page
2. Right click on the page and select Sub Page.
3. Drag and drop the necessary fields from the Sub record
4. Save it with _SBP
5. Click on Insert-->Subpage
6. Select the Sub page from the list and click on Ok
Steps for creating secondary page
1. Drag and drop all the necessary fields in to the main record
2. Open a new page-->Right click and select Secondary page
3. Drag and drop all the necessary fields in to it.
4. Save it with _SEC.
5. Click on Insert-->hyperlink/push button.
6. Select destination as Secondary PAge.
7. Record name tbl
8. select the secondary page.

Scheduling SQR report through the Process Scheduler

2009-06-26T02:06:00.001-07:00

Process Scheduler:
Scheduling a Process :
-->Click on People Tools
-->Process Scheduler->Processes
-->Click on Add a New Value
-->Select the process type as SQR Report.
-->Enter the process name (Ex:-Harsha1)
-->Click on ADD
-->Select the Process Definition tab
-->Select the Run control ID that u have created for the existing project (SRI_RUNCNTRL2_CMP)
-->Select the Process Groups as ALL PAGES
-->Save it
-->Now click on Astute Business Solutions5 Folder
-->Select your name and click on the project
Running a Report through the Process Scheduler
-->Open the http://astsrvr2:10000/psp/HCMTRA90/EMPLOYEE/HRMS/?cmd=logout (PS Sign on page)
-->Click on PeopleTools
-->Click on Process Scheduler
-->System Process Requests
-->Click on Add a New Value (Ex:1)
-->Click on Add
-->Click on Run
-->Select check box Cross reference window listing. (SQR Reports)
-->Click on Ok.
-->Click on Process Monitor (to check the Process status)
-->Click on Details on any of the running Process.
-->Click on View Log/Trace
-->Click on PDF Url (Ex:XRFWIN_2744.PDF)(It opens the Report in PDF from)

Creating New Permission Lists:
-->Open the PS SignOn page.
-->Click on People Tools.
-->Click On security.
-->Click o permissions & Roles.
-->Click on Permission Lists.
-->Click on Add a New Value.
-->Enter the description.
-->In Permission List Home Page Select Human Resources.
-->Click on Save.
-->Click on Page Tab.
-->Select the Menu Name (Your menu name)
-->Click on Edit components
-->Click on Edit pages
-->Click on select all
-->Click on Ok.
-->Click on Save.
-->Click on PeopleTools Tab.
-->In the PeopleTools Permissions select the necessary.
-->In Data Archival Select the necessary
-->Click on Save.
-->Click on Process Tab
-->Click on Process Group Permissions
-->Select the ALLPAGES, ALLPANLS, FEDALL.
-->In the Allow Process Request:
-->In View By select Owner and In Update By select Owner.
-->Click on Ok.
-->Click on save.
-->Click on Web Libraries Tab.
-->Select WEBLIB PORTAL.
-->Click On save
-->Now Click on roles
-->Click on Add New Role.
-->Select the Permission List (Ex:Harsha)
-->Click On save
-->Click on user profiles.
-->Click on Roles Tab.
-->Click On +
-->Select the Role Name and select it.
-->Click on Save.

PeopleSoft WorkFlow Navigations

2009-06-26T02:05:00.000-07:00

WorkFlow:-
TO CREATE THE WORKFLOW IN APPLICATION DESIGNER
INSERTING AN WORKLIST INTO THE EXISTING PROJECT:
1. Before that inserting the worklist
2. Copy the existing worklist from R_TT_WL
3. Save it as SRI_EMP_WL
4. Click on F7
CREATING THE BUSINESS PROCESS:
1. Click on New-->Business Process.
2. Drag and drop the Activity1 on to the work space.
3. Drag and drop the Activity2 on to the work space.
4. Link Both Activity1 and Activity2
5. Save the Business process as SRI_EMP_BP.
6. Click on F7.
Creating Activity
1. Open an Existing Project.
2. New--> Activity
Click on the Step (Blue color) and Click on the Work space.
-->Double click on the step and Name it as SHE_EMP_ACT1
-->Give the required Icon Description.
-->Click on the Attributes and Select the Menu, Bar, Item, Page, Action (as add) Names in the project.
-->Click on Ok.
Click on the Event (Yellow color) and Click on the Work space beside the step.
-->Double click on the Event
-->Name: SRI_EVENT and Icon Descr as SRI_EVENT.
-->Select the record name SRI_EMP_TBL.
-->Click on Edit Business Rules which opens a window. And write the following below code:
&I=TriggerBusinessEvent(BUSPROCESS.bus_proc_name,BUSACTIVITY.activity_name,BUSEVENT.bus_event_name)
-->Save it.
-->Click on Ok.
Click on the Worklist (Yellow color rectangle) and Click on the Work space beside the Event.
-->Double click on the Work List.
-->Name: Worklist and Icon Descr: Worklist.
-->Click on Attributes.
-->Select the worklist from the drop down list (SRI_EMP_WL).
-->Select the Business Process from the Drop downlist (SRI_EMP_Bp)
-->Select the Activity2 from the drop down list(SRI_EMP_ACT2)
-->Click on oK.
-->Click on Field Mapping.
-->Click on ADD
-->Select the Field name as OPRID and Map module as copy(by default)
-->Select the radio button Constant.
-->Give the name of the people who want to see the work report (Ex:SARAT)
-->Click on Ok.
-->Click on ADD.
-->Select the Field name as SRI_EMPAE_TBL and Map module as COPY
-->Select the radio button RecField. (sELECT THE FIELD NAME TO BE MAPPED)
-->Click on ok.
--->Click on the link(beside the ?) and point it on the Step towards the Event in the workspace and same to the Worklist.
2. Again create another activity with only one event and save it as SRI_EMP_ACT2
-->Select the Menu Name as ADMINISTER_WORKFORCE_(GBL)
-->Select Bar Name and Use
-->Item Name as JOB_DATA
-->Page Name as EMPLOYEMENT_DATA
-->Action as &UPDATE/DISPLAY
-->Click on Ok
NOTE:-IF WE GET SQL ERROR THEN CHECK OUT WITH THE BUSINESS PROCESS NAME

PeopleSoft Query Security Navigations

2009-06-26T02:04:00.000-07:00

Query Security:
-->Open http://Server/psp/XXXXX/EMPLOYEE/HRMS/h/?tab=DEFAULT (PS Sign On page)
-->Select PeopleTools
-->Select Security-->Query Security
-->Click on Query Access Manager.
-->Click on Search.
-->Select the Access Group which is already exists in the database (ex:-AD ACESS GROUP-Admissions Access Group)
-->Select any one of the Leaf Node.(Ex:-SCHOOL LOAD)
-->Click on insert child record.
-->Click on Search
-->Enter the table name (your own table name that u have created before)
-->Click on Look Up
-->Select the table (which u wants to add)
-->Click an Add.
-->Click on Save.
-->Click on Query Access List Cache under Menu.
-->Select the Enable Access List Cache
-->Click on Run.
-->Click on Ok.
-->Sign Out from PS Sign-in page.
-->Open the Application Designer.
-->Select Go-->Click on Query.
-->The list of records will appear in the Left hand side.
-->Search your record which is added before.
-->Send Tree Change Message will be selected by default.
-->Click on OK.

Creating the User Profiles and assigning the Roles and Permissions:

2009-06-26T02:03:00.000-07:00

Creating the Permission List (Collection of roles)
-->Open the PS SignOn page.
-->Click on People Tools.
-->Click On security.
-->Click on permissions & Roles.
-->Click on Permission Lists.
-->Click on Add a New Value.
-->Enter the description.
-->In Permission List Homepage Select Human Resources.
-->Click on Save.
-->Click on Page Tab.
-->Select the Menu Name (Your menu name)
-->Click on Edit components
-->Click on Edit pages
-->Click on select all
-->Click on Ok.
-->Click on Save.
-->Click on PeopleTools Tab.
-->In the PeopleTools Permissions select the necessary.
-->In Data Archival Select the necessary
-->Click on Save.
-->Click on Process Tab
-->Click on Process Group Permissions
-->Select the ALLPAGES, ALLPANLS, FEDALL.
-->In the Allow Process Request:
-->In View By select Owner and In Update By select Owner.
-->Click on Ok.
-->Click on save.
-->Click on Web Libraries Tab.
-->Select WEBLIB PORTAL.
-->Click On save.
Creating Roles (Role is a collection of user profiles)
-->Now Click on Roles
-->Click on Add New Role.
-->Select the Permission List (Ex:Harsha)
-->Click On save
Creating User Profiles:
-->Click on User Profiles.
-->Click on user profiles.
-->Click on Roles Tab.
-->Click On +
-->Select the Role Name and select it.
-->Click on Save.

Steps to Run the Appilaction Engine Program (Inbound/OutBound) Program through thr Process Scheduler

2009-06-26T02:01:00.000-07:00

In Application Designer:
1. Open a New page.
2. Save the page.
3. Click on Insert-->Subpage-->PRCSRUNCNTL_SBP to PRCSRUNCNTL
4. Save it.
5. Drag and drop the Page into component and save it.
6. Register the component.
7. Open the existing menu and drag and drop the component into it.
8. Save it.
Process Scheduler:
1. Open the PS signin page.
2. Click on people tools-->Process Scheduler-->Process
3. Click on Add a new value.
4. Select the process type as Application Engine
5. Enter the process name (ABC1_STU_AE)
6. Click on Process Definition tab and enter the description (Sample student)
7. Click on Process Definition Options and select the component (ABC1_STU_RUN_CMP).
8. Select the Process Groups as ALLPAGES
9. Save it.

Employee Change Request Application Enginer Code

2009-06-26T01:57:00.000-07:00

&MYFILE = GetFile("C:\Documents and Settings\Trainees.AST\Desktop\CR2.csv", "R", %FilePath_Absolute);
&MYFILE1 = GetFile("C:\Documents and Settings\Trainees.AST\Desktop\EMPChange Request AE.TXT", "W", %FilePath_Absolute);
&array = CreateArrayRept("", 0);
&array3 = CreateArrayRept("", 0);
&array4 = CreateArrayRept("", 0);
&intime = CreateArrayRept("", 0);
&outtime = CreateArrayRept("", 0);
&entrytime = 8 * 60;
&exittime = 16 * 60;
&num = 8;
rem Local string &str, &str1;
Local Record &R0;

&R0 = CreateRecord(Record.ABC_EMPCR_TBL);

If &MYFILE.IsOpen Then
While &MYFILE.Readline(&string);
&array = Split(&string, ",");
REM &MYFILE1.WRITELINE(&array.LEN);

&MYFILE1.WRITELINE(&array [1]);
&R0.EMPLID.Value = &array [1];
rem &MYFILE1.WRITELINE(&array [2]);
&R0.ABC_EMPCR_NAME.Value = &array [2];
rem &MYFILE1.WRITELINE(&array [4]);
If &array [4] = "" Then
&R0.ABC_EMP_DATE.Value = "";
Else
&R0.ABC_EMP_DATE.Value = DateValue(&array [4]);
End-If;

If &array [5] <> "" And
&array [6] <> "" Then
&str = TimeValue(&array [5]);
&intime = Split(&str, ".");
&str1 = TimeValue(&array [6]);
&outtime = Split(&str1, ".");

&str = &intime [1] * 60;

rem &MYFILE1.writeline("---Tot Late Min when =8-----" | &str);

If &str = &entrytime Then
&inhrs = 8;
&inmins = &intime [2];
&inmins1 = (&intime [1] * 60) + &intime [2];
&entrymins = 0;
&latentrymin = &inmins - &entrymins;
&MYFILE1.writeline("---Total Late Min when Employee logs at 8=" | &latentrymin);
End-If;

If &str < &entrytime Then
&inhrs = 8;
&inmin = 0;
&MYFILE1.writeline("---Total Late Min when Employee logs at <8-----" | &inmin);
&inmins1 = 480;
End-If;
If &str > &entrytime Then
&inmins = (&intime [1] * 60) + &intime [2];
&entrymins = 8 * 60;
&latentrymin = &inmins - &entrymins;
&MYFILE1.writeline("---Total Late Min when Employee logs at >8-----" | &latentrymin);
&inmins1 = (&intime [1] * 60) + &intime [2];
End-If;

If &outtime [1] > 16 Then
&outhrs = 16;
&outmin = 0;
&MYFILE1.writeline("---Total OUT Late Min when Employee logs OUT at >16-----" | &outmin);
&outmins1 = 16 * 60;
End-If;

If &outtime [1] < 16 Then
&outmins = (&outtime [1] * 60) + &outtime [2];
&exitmins = 16 * 60;
&latexitmin = &exitmins - &outmins;
&MYFILE1.writeline("---Total Late Min when Employee logs OUT at <16-----" | &latexitmin);
&outmins1 = (&outtime [1] * 60) + &outtime [2];
End-If;

If &outtime [1] = 16 Then
&outmins = &outtime [2];
&exitmins = 0;
&outmins1 = (&outtime [1] * 60) + &outtime [2];
rem &latexitmin = &outmins - &exitmins;
rem &MYFILE1.writeline("---Tot Late Min when =16-----" |&latentrymin);
End-If;
&totlatemin = &latentrymin + &latexitmin;
&MYFILE1.writeline("---Tot Late Min = &latentrymin + &latexitmin-----" | &totlatemin);

If Mod(&totlatemin, 60) = 0 Then
&latehrs1 = &totlatemin / 60;
&latehrs2 = Round(&latehrs1, 2);
&MYFILE1.writeline("---Total Late hrs when mod=0-----" | &latehrs2);
Else
&latehrs1 = &totlatemin / 60;
&hrs1 = Substring(&latehrs1, 1, 1);
rem &MYFILE1.writeline("---Tot Late hrs when mod != 0 -----" | &hrs1);
&mins1 = Mod(&totlatemin, 60);
&totlthrs = &hrs1 | ":" | &mins1;
rem late hrs1 and late min;
&MYFILE1.writeline("---Employee Total Late hrs when mod != 0 -----" | &totlthrs);
End-If;

&MYFILE1.writeline("---TOTAL OUT MINUTES---" | &outmins1);
&MYFILE1.writeline("---TOTAL IN MINUTES -----" | &inmins1);
&wrkhrs1 = &outmins1 - &inmins1;
&MYFILE1.writeline("---TOTAL WORKING MINUTES &outmins1 - &inmins1-----" | &wrkhrs1);

/*WORKING HOURS*/
If Mod(&wrkhrs1, 60) = 0 Then

&wrkhrs2 = &wrkhrs1 / 60;

&wrkhrs3 = Round(&wrkhrs2, 0);

&MYFILE1.writeline("---Total WORK hrs when mod=0-----" | &wrkhrs3);
Else

&wrkhrs2 = &wrkhrs1 / 60;
&wrkhrs3 = Substring(&wrkhrs2, 1, 1);
rem &MYFILE1.writeline("---Tot Late hrs when mod != 0 -----" | &hrs1);
&wrkmins1 = Mod(&wrkhrs1, 60);
&totwrkhrs = &wrkhrs3 | ":" | &wrkmins1;
rem Tot Working wrkhrs3 and Tot Working wrkmins1;

&MYFILE1.writeline("---EMPLOYEE TOTAL WORKING HOURS -----" | &totwrkhrs);
End-If;

Else
&wrkhrs3 = 0;
&wrkmins1 = 0;
&hrs1 = 0;
&mins1 = 0;
End-If;

&R0.ABC_EMP_WRKTIME.Value = &wrkhrs3;
&R0.ABC_EMP_TOTMIN.Value = &wrkmins1;
&R0.ABC_EMP_LATEHOUR.Value = &hrs1;
&R0.ABC_EMP_LATEMIN.Value = &mins1;

&R0.Insert();

End-While;
End-If;

rem &R0.Update();
&MYFILE.close();