PeopleSoft Finance Tables with Detail Description XLS sheet

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.

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

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

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

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

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

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

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

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

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

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

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

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

&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

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

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

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

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

Date display format on Page

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

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

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

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

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

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

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

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

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

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

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.