11/26/2021

State Record In Application Engine Peoplesoft

Application Engine

State records are used in Application Engine programs to keep track of useful information through the life of a program. PeopleSoft uses the concept of a record (table) to store this information. The scope of data in a state record is global to an application engine program (and other programs that share the state record). The naming convention. State Record: 1. The record that ends with 'AET' is identified as State Record. Used in Application Engine program. As name itself says 'state' meaning we get to know the state of data (snapshot) that is passed through the program.


It isused to develop batch programs or online programs that perform the back endprocessing against the data.
ApplicationEngines are neatly structured into blocks and offer an alternative to writingCOBOL or SQR programs for background SQL processing.
1.Designer->consists ofthe batch program (A program is defined in application designerwhich performsa business process).
2.Run time environment->To runand monitor the programs.
Itdoes not generate SQL or PeopleCode, butexecutes SQL or PeopleCode as apart of program.
It isdesigned for batch processing where data must be processed without userinteraction.

APPLICATION ENGINE PROGRAM ELEMENTS:
1.PS AE consists a set of processes to execute a given task and made up of several elements.They are
State Record In Application Engine Peoplesofti) SectionsState Record In Application Engine Peoplesoft
It is a set of ordered steps that gets executed as a part of the program.The execution of the program starts with the section defined as MAIN.
ii) Steps
Steps are the smallest unit of the program that can be commited within a program.It is used to execute the a peopleCode
command or log a message with the step.When the section gets called, its steps execute sequentiately.
iii) Actions

State Record In Application Engine Peoplesoft Query

Multiple actions can be within a step.There are 5 multiple actions they are
DO ACTIONS:
Do actions contain a SQL statements taht is designed to return the results on which subsequent actions depend.
It is equvalent to a COBOL perfrom statement.There are 4 types of Do actios they are:
1)Do While 2)DO When 3)Do Select 4)Do Until.
SQL AND CALL SECTION ARE MUTUALLY EXCLUSIVELY ACTION WHICH CANNOT BE PERFORMED ONCE AT ATIME COZ IT CAUSES DEADLOCKS
SQL:
It contain a single SQL statements.There are 4 types of SQL statements.They are
1)Update 2)Delete 3)Insert 4)Select
PeopleCode:
It provides an excellent way to build dynamic SQL, perform simple if/else edits,set defaults and other operations that don't require a trip to the database
Log Message:
Call Section:
It is used to call and insert an action in the progaram or outside program.
iv) State Records
It is a PeopleSoft Record that must be created and maintained by the Application Engine developer.
This record defines the fields a program uses to pass values from one action to another.
It can be either a Physical record or a work record, any no of sate records can be assosiated with a program.
Physical state records must be keyed by process instance.
APPLICATION ENGINE PROGRAM TYPES:
There are 5 types of Application Engine Programs.They are.
1.Standard->Normal entery point program.
2.Upgrade Only->used in PS Upgrade utilites.
3.Import Only->used by PS import utilites.
4.Daemon Only->A type of program which is used a daemon Process.
5.Transform Only->used to support Extensible stylesheet language Transformations(XSLT).
DAEMON PROGRAM TYPE:
1.PS AE provides a daemon process called PSDAEMON, that runs continously when PS Process Scheduler is running and it is intended for running jobs.
2.It consists of a predefined set of conditions as events.When PSDAEMON schedules a process to handle an event when the conditions are true.
3.It allows only PS AE tracing at the step and SQL levels
META-SQL
1.Meta-SQL is a set of predefined terms designed to replace RDBMS syntax.
2.PS meta-SQL enables us to dynamically generate portions of SQL code.(EX;--2 join 2 tables based on their common keys)
3.%select->It is ued to insert values of varibles in to sate record.
4.%bind->It is used to get values.
SQLEXEC();
By using the SQLEXEC() function we can do the data manipulation (Insert,Update and Delete).
Example:
SQLEXEC('select * from PS_JOB where X.EMPID.vlaue=:1',abc,&emplid1')
The main draw back using this SQLEXEC() function is 'It will return only one value at a time'.
Note:-for more in detail draw backs about SQLEXEC,please go through the other post Reasons why PeopleSoft does not recomd SQLEXEC()'

Reasons why PeopleSoft does not recomd SQLEXEC()

PS does not recommend sqlexec(), to avoid direct database calls. As the main reason for 3 tier architecture was to execute the business logic on the application server so that the application can be made database independent. This means if you want to change the database of any application from one vendor to another, you should be able to do it easily.
If you use sqlexec() calls, this purpose is not met as you may use sql which is very specific to the database. However if you use Peoplesoft way of creating a record, for example
RC_CASE is record with key as CASE_ID
Local record &lrec = createrecord(record.rc_case);
&lrec.case_id.value = 123;
Now if you want to select from RC_CASE, you would use sqlexec() as sqlexec('select case_closed from ps_rc_case where case_id=:1',123,&caseclos);
Instead you can also use the record instance as
&lrec.selectbykey();
The above will populate the rc_case record with all the values and you can use it. Also in this case Peoplesoft forms the sql based on the database, so if the organization using this application decides to change the database from Oracle to MS-SQL Server, you will not have to change the code for above.
Sate Records:
It is a working storage.
It is a physical record or derived work record
It passes values from section to section ,Action to Action and step to step.
We can use 'N' no of state records in a program.
It consists of Runcontrol ID and Process Instance ID.
It it is a physical record then process Instance ID must be keyed.
It maintains temporary data in AE programs for online processing.
State Record Properties
General
It is used to select any sate record from the list
Temp Tables
1.Select the temp tables tab.
2.It is used to get a temporary record.
3.When we enter any of the wild card character or the table full name and Click on get list.Then it displays all the records containing temporary tables.
4.Select the necessary temporary table and click o add in to the list.
5.We can remove a particular temporary table by selecting it and click on remove.
6.Instance count is used to enter the no.of physical tables to be created for each dedicated table for this program during the SQL Build procedure in PeopleSoft Application Designer.If we want to any program to run for 3 times then we should specify in the instance count.
7.If we select base version then it uses the temporary tables.
8.If we select Abort then the program exists with an error message.
Advanced Tab
1.Disable Restart
2.Application Library
3.Batch only
4.Messsage Set
5.Program Type (STD,Upgrade,Import,Daemon,Transform)

Restating an AE program

The Key feature of AE is Restart.
Sate record should be a physical record.When the system is processing a huge amount of data (batch processing) and if there occurs any failure through the environment then the restartable logic comes in to picture.The cursor directly comes in to the checkpoint where the commit is set upand restarts the program from that point.
We can restart the program from the process request page.
AERUNCONTROL record saves all the information that is required for a program to restart.
File Layout's
1.It is used for file processing using INBOUND and OUTBOUND process
2.A File layout is acollection of records.
3.Works with hierarchical and non-hierarchical data
Peoplesoft4.stored with _FLT

Peoplesoft Application Engine Tables


5.It enables us to do the following
-->Export and Import the data.
-->It uses Batch Process to perform large amount of data import and export.
-->There are 3 types of file formats.They are CSV,XML,FIXED.
6.It also facilitates to provide the transaction between PS systems and 3rd party systems

Inbound Program

Reading from a FALT file and writing in to database
-->Open the flat file in READ mode
-->Read each and every line in a flat file and put them in a string
-->Split the data with separators and put it in an array
-->Insert the values in to the record by matching the field values in to record.
Example:
Local record &rec
&myfile=getfile('c:/documents/abcd.txt','R',%filepath_absolute);
&arr=createarrayrept(',0);
&rec=createrecord(record.EMP_TBL);
If &file.open() then
while &file.readline(&str);
&arr = split(&str,',');
&rec.EMP_ID.value = &arr[1];Record
&rec.EMP_NAME.value = &arr[2];
&rec.EMP_AGE.value = &arr[3];
&rec.insert();
End-while;
end-if;
&myfile.close
Outbound Program
Writing from a Database to flatfile
-->Open the flat file in WRITE mode.
-->Use the %SELECT to select all the record field values.
-->Put the field values in a dummy record.
-->By using the dummy record field value write the files to FLAT file usng WRITELINE.
Example:
&myfile=getfile('c:/documents/abcd.txt','W',%filepath_absolute);
&myfile.open('c:/documents/abcd.txt','W');
For i=1 to5;
&myfile.writeline('HI welcome to PS world');
end-for;
&myfile.close
A sample code to read a file using file layouts and insert data read into a record Local File &MYFILE;
Local Record &REC;
Local array of string &ARRAY;
Local string &FILE_DIRECTORY, &FileName;
&FileName = 'MY_FILE_NAME.txt';
&FILE_DIRTORY = '/MYDIRECTORY/'
/*open file for reading*/
&MYFILE = GetFile(&FILE_DIRECTORY &FileName, 'R', %FilePath_Absolute);
/*create record object*/
&REC = CreateRecord(Record.MY_RECORD);
&ARRAY = CreateArrayRept(', 0);
/*check if file is open*/
If &MYFILE.IsOpen Then
/*The SetFileLayout method is a file layout method. It associates a specific file layout definition with the file object executing this method, providing easy access to rowset data.(PeopleBooks)*/
If &MYFILE.SetFileLayout(FileLayout.FILE_LAYOUT_NAME) Then
/*read line into &STRING*/
While &MYFILE.ReadLine(&STRING);
&ARRAY = Split(&STRING, ',');
For &I = 1 To &REC.FieldCount
&REC.GetField(&I).Value = RTrim(LTrim(&ARRAY [&I]));
End-For;
/* do additional processing here for converting values */
&REC.Insert();
/*count rows inserted into record*/
&COUNT = &COUNT + 1;
End-While;
Else
/* do error processing - filelayout not correct */
End-If;
Else
/* do error processing - file not open */
End-If;
We can run the AE in 3 way's
1.Application Designer -->RUN
2.Command Line -->PSAE.EXE
3.Process Scheduler.
Order of events in Application Engine
1.Do When
2.Do While
3.Do Select
4.People Code
5.SQL
6.Call Section
7.Log Message

State Record In Application Engine Peoplesoft Access


8.Do Until

Introducing Application Engine Designer

Peoplesoft App Engine

  • Describing Student Workstation Resources
  • Using Application Engine Designer
  • Describing Application Engine Program Structure
  • Building Application Engine Programs
  • Adding Markets and Filters to Application Engine Program Sections
  • Setting Application Engine Program Properties
  • Testing Application Engine Programs

Using State Records

  • Describing the Function of State Records
  • Executing an Application Engine Program Using the Developer’s Shortcut
  • Modifying Application Engine Programs to use State Records
  • Adding Sections and Steps to Existing Application Engine Programs
  • Using Meta-SQL in Application Engine Programs

Testing and Debugging

  • Testing Application Engine Programs
  • Using the Process Monitor
  • Using Application Engine Traces
  • Using the Application Engine Debugger
  • Testing With Application Engine Restart

Using the Do Select Action

  • Describing Do Select
  • Using Select and Fetch
  • Using Reselect
  • Using Restartable
  • Developing an Application Engine Program Using Do Select

Incorporating Conditional Processing

  • Defining Do Actions
  • Explaining Conditional Statements in SQL
  • Explaining Do Action Program Flow
  • Designing a Program Using Conditional Processing
  • Using Application Engine Libraries

Using PeopleCode with Application Engine

  • Identifying How to use PeopleCode in Application Engine Programs
  • Inserting PeopleCode into an Application Engine Program
  • Enabling Dynamic Calls

Using Set Processing

  • Describing Set Processing
  • Modifying a Program to use Set Processing

Using Temporary Tables

  • Describing Parallel Processing
  • Implementing Parallel Processing
  • Using Meta-SQL with Parallel Processing
  • Describing Run-Time Allocation

Executing Application Engine Programs

  • Executing an Application Engine Program Using a Push Button
  • Executing an Application Program with the Process Scheduler
  • Executing an Application Engine Program with a Command Script

Oracle Peoplesoft Applications

Creating Process Definitions for Application Engine

  • Identify the Process Types used with Application Engine
  • Creating Application Engine Process Definitions
  • Implementing an Application Engine Program with No User Inputs
  • Implementing an Application Engine Program With User Inputs

Peoplesoft App Engine Example

Measuring Application Engine Performance

  • Describing Traces
  • Passing Trace Values to PSAE.EXE
  • Interpreting Application Engine Trace Data
  • Interpreting the PeopleTools Trace
  • Describing Advanced Trace Options

Tuning Application Engine Programs

Peoplesoft Application Engine Trace Options

  • Describing the Process of Performance Tuning
  • Identifying Application Level Tuning Opportunities
  • Explaining the Performance Impact of PeopleCode
  • Describing How Do Flow Control Affects Program Performance
  • Explaining Database-Level Tuning Considerations