Code :- Run AE thru CreateProcessRequest in  PeopleCode

Local ProcessRequest &RQST;

&RQST = CreateProcessRequest();
&RQST.ProcessType = "Application Engine";
&RQST.ProcessName = "POLOADFILE";
&RQST.RunControlID = "POLOAD";
&RQST.Schedule();

If &RQST.Status = 0 Then
   WinMessage("AE Successfully Scheduled", 0);
Else
   WinMessage("There is a problem while Scheduling AE", 0);

End-If;

ArrayS in Peoplecode

Local File &FILE1;

Local string &STRING, &ITEM_ID, &ship_id;

Local array of string &Array;

&FILE1 = GetFile(xyz.txt, "R", %FilePath_Absolute);

&Array = CreateArrayRept("", 0);

If &FILE1.IsOpen Then

   While &FILE1.ReadLine(&STRING);

      &Array = Split(&STRING, Char(9));/*** Define array and make sure the Fields separated by tab space  in xyz.txt file***/

      &ITEM_ID = LTrim(RTrim(&Array [1], " "), " ");

      &ship_id = LTrim(RTrim(&Array [2], " "), " ");

                MessageBox(0, "", 0, 0, "The Processed  item id's are  # " | &ITEM_ID );

   End-While;

End-If;

&FILE1.Close();
/***The string &item_id ( includes 1234,4567 values) which can be used for validations or printing ***/

Dynamic SQL in SQR with example:

Assume a scenario; SQR generates employee details report based on either Company or pay Group.

 So SQR code would be.

If $Run_Option = ‘P’

Begin-Select

A.EMPLID (+1, 1)

A.NAME (, +5)

 FROM XYZ A

WHERE A.PAYGROUP IN (‘PG’)

End-Select

Else

Begin-Select

B.EMPLID (+1, 1)

B.NAME (, +5)

 FROM XYZ B

WHERE B.COMPANY IN (‘SAP’)

End-Select

End-If

Above code can be tuned by Create dynamic SQL.

If $Run_Option = ‘P’

   LET $WHERE = 'WHERE A.PAYGROUP IN (' || '''' || 'PG' || '''' || ')'

END-IF

IF $Run_Option = ‘C’

   LET $WHERE = 'WHERE A.COMPANY IN (' || '''' || 'SAP' || '''' || ')'

End-If

Begin-Select

EMPLID (+1, 1)

NAME (, +5)

 FROM XYZ A

[$where]

End-Select

If run option is not P and C then there won’t be any where condition. SQR will not give any error. It runs without the Where condition, so all the rows from XYZ table will be Selected.

EDIT MASK -Show - Display SQR Commands

SQR Commands – Show and Display and EDIT MASK

·   Display command useful to display only one value at a time

Display &STUDID Display &NAME Display &SEM Output: 100 Satish 4

Display &STUDID NO LINE Display &NAME NO LINE Display &SEM Output: 100 Satish 4

·   Show command useful to show more than one value at a time.

                                          

Show &STUDID &NAME &SEM Output: 100 Satish 4

·   We can also use the EDIT Mask with Show and Display.

Display '123456789' xxx-xx-xxxx Output: 123-45-6789

Display 1234567.89 999,999,999.99 Output: 1,234,567.89

Pseudo Code -File Operations - Reading from one file and writing into another file .

Reading from one file and writing into another file Pseudo Code.

Local array of string &FNAMES;

Local string &FILEPATH,&Lin;

Local File &File1,&File2;

&FILENAME2 = "c:\abc.txt";

&FILENAME1 = "d:\xyz.txt";/***Give the right path with file name***/

/***Opening File2 in Append mode and writing data into File2***/

&File2 = GetFile(&FILENAME2, "A", %FilePath_Absolute);

If &File2.IsOpen Then

   &TextPRINT = "Program  ABCEF";

   &File2.WriteLine(&TextPRINT);

End-If;

&File2.Close();

/***Opening File2 in Read mode ***/

&File2 = GetFile(&FILENAME2, "R", %FilePath_Absolute);

/***Check whether File1 Exists***/

If FileExists(&FILENAME1, %FilePath_Absolute) Then        

   &File1 = GetFile(&FILENAME1, "A", %FilePath_Absolute);

   /***read the data from File2 and  writing into File1***/

   If &File1.IsOpen Then

      While &File2.ReadLine(&Lin) 

         &File1.WriteLine(&Lin);

      End-While;

   End-If;

   &File1.Close();

   &File2.Close();

End-If;

*******************

Expected output

File1 d:\xyz will have the Data of File2 (c:\abc.txt)

On Break in SQR with Example

On Break in SQR

A break is a change in the value of a field.

Uses

1. Avoids printing redundant data.

2. Skip lines when the value of the Column is changed.

3. Print a value only if changed.

4. To add spaces in the report.

5. Perform conditional processing on variables that change.

6. Execute a special procedure before or after the break.

7. Print Subtotals.

Sample Programs – Break Logic?

Assume a table ABC is having following data.

1.      SKIPLINES

BEGIN-SELECT

COMPANY (,1) ON-BREAK SKIPLINES = 1

COUNTRY (,+5)

PAY_END_DT (,+5)

EMPLID (,+5)

NAME (,+5)

SALARY (,+5)

 POSITION (+1)

 FROM ABC

END-SELECT

Company will be printed only once and is not repeated.

Skiplines argument provides a line break between each break.

2.      SKIPLINES & LEVEL

BEGIN-SELECT

COMPANY (,1) ON-BREAK SKIPLINES = 2

COUNTRY (,+5) ON-BREAK LEVEL = 2 SKIPLINES = 1

PAY_END_DT (,+5)

EMPLID (,+5)

NAME (,+5)

SALARY (,+5)

 POSITION (+1)

 FROM ABC

END-SELECT

 

Best Practice to take back up Before deleting any data in Table

Before deleting any data in table it always Best Practice to take back up

So here is the Sample SQL 

Creating a Temp Table

CREATE TABLE SYSADM.PS_PO_HDR_TMP AS SELECT * FROM SYSADM.PS_PO_HDR WHERE BUSINESS_UNIT = 'MN300';

Make sure Table is created with Expected rows of data

SELECT * FROM SYSADM.PS_PO_HDR_TMP;

Delete rows from main table to remove data of backup data

DELETE FROM SYSADM.PS_PO_HDR

WHERE (BUSINESS_UNIT,PO_ID) IN  (SELECT BUSINESS_UNIT,PO_ID FROM SYSADM.PS_PO_HDR_TMP);

SQR App Engine Tracing Parameters with snaps and navigation

     SQR Tracing Parameters

Go to Process Scheduler – Process – Select any Process – Go to Override Tab – Select Parameter List type as APPEND 

Ways to Trace Application Engine?

a) Command line

By using –TRACE.

For Example:

PSAE.EXE —CT <DB_TYPE> -CD <DB_NAME> -CO <OPRID> -CP <PASSWORD> -R <RUN_CONTROL> -AI <PROGRAM_NAME> -I <PROCESS_INSTANCE> -TRACE <Value>

C:\PT8.45\bin\server\WINX86\psae.EXE -CT MICROSFT -CS PSNT -CD HC -CO PS -CP PS -R 123 -AI AETSTPROG -I 111 -TRACE 384

 b)Configuration Manager.

Set the Tracing option in Configuration Manager > Trace tab > A.E Trace.

c) Process Definition.

 Go to Process Scheduler – Process – Select any Process – Go to Override Tab – Select Parameter List type as APPEND and Specify the Trace option like -TRACE 7 -TOOLSTRACEPC 3980 –TOOLSTRACESQL 135.

d)Server configuration files.

We can enable the trace in the configuration files of Application Server and the Process Scheduler Server

Row-by-row processing VS Set Processing with Example

 Row By Row Processing

· A Single row will be fetched and rest of actions will be performed and then next row will be fetched and rest of actions will be performed and next and so on..

· Program will fetch the data Row by Row from database and performs the Subsequent Actions.

    Sample Program

· Assume a table ABC is having EMPLID, DEPTID, and SALARY.

· If we wanted to give 10% hike to the employees who are belongs to ‘BBB’ Department.

EMPLID	DEPTID	SALARY
11111	AAA	10,000
22222	BBB	10,000
33333	BBB	25,000
44444	CCC	12,000
55555	BBB	20,000

· With Row By Row Processing, AE Program Structure would be

MAIN    Step1       DoSelect %Select(EMPLID) Select EMPLID From ABC WHERE DEPITD = ’BBB’       SQL UPDATE ABC SET SALARY = (0.10*SALARY) + SALARY WHERE EMPLID = %Bind(EMPLID);

· DoSelect will fetch 22222, 33333, and 55555 employees.

· In First Loop - Inserts 22222 into State Record.

· Executes the SQL Action which updates the Salary with 10% hike for 22222 Employee.

· In Second Loop - Inserts 33333 into State Record and then updates the Salary.

· In Third Loop – Inserts 55555 into State Record and then Updates the Salary.

· Then comes out from the DoSelect.

· As the program in Row by Row Processing, it goes to the database 3 times and updates the Salary.

     Set Processing

Set processing is nothing but processing set of rows at one time rather than processing each row individually.

     Sample Program 

· With Set Processing, AE Program Structure would be

MAIN    Step1       SQL UPDATE ABC SET SALARY = (0.10*SALARY) + SALARY WHERE DEPTID = ‘BBB’;

· SQL Action will be executed which updates the Salary for all the 3 employees at a time.

Dynamic Call Section with Sample Program

 Procedure to Call Sections Dynamically

·    Create and assign a State Record with AE_APPLID and AE_SECTION fields.

·    Include DoSelect and insert AE_APPLID and AE_SECTIONID

(OR)

·    Include PeopleCode Action and based on the Conditions assign Program Name and Section Name.

Then

·    Make the Section as Dynamic by selecting Dynamic Check Box.

.      Sample Program 

MAIN   Step1     DoSelect %Select(AE_APPLID, AE_SECTIONID) Select AE_APPLID, AE_SECTIONID FROM PS_ABC OR     PeopleCode MessageBox(0, "", 0, 0, "Main Section - Step1 - PeopleCode"); If %OperatorId = "PS" Then    ABC_AET.AE_APPLID.Value = "ABC_APPENG";    ABC_AET.AE_SECTION.Value = "Insert"; Else    ABC_AET.AE_APPLID.Value = "ABC_APPENG";    ABC_AET.AE_SECTION.Value = "Delete"; End-If;     Call Section       Select Dynamic Check Box Delete Section   Step1     PeopleCode MessageBox(0, "", 0, 0, "Delete Section - Step1 - PeopleCode"); Insert Section   Step1     PeopleCode MessageBox(0, "", 0, 0, "Insert Section - Step1 - PeopleCode");