PeopleSoft is widely used in local government, healthcare, and education. As the name implies, PeopleSoft’s origins rise from managing groups of people.
Different types of variables in the Application Engine people code
- Local variable – these are available for the duration of the program in which they are declared.
- Global/Component – These variables are available while the Application Engine program is running. They are saved at commits and checkpoints, So they can be used for restarts. Component variables are same as Global incase of the AE.
Different ways pass data between the steps
- State records – One row can be passed and can have many state records.
- Component/Global – People code variables.
- Temporary records – Multiple rows of data can be passed.
Prerequisite for the State record
1) Should be either Derived work record Or SQL Table.
2) Name of the record must end in AET; this is how the system identifies the record as a state record.
3) PROCESS_INSTANCE must be a key.
4) Any data types except character or numeric must require not be fields.
Read the runctrl parameters in AE
%Select (EMPLID) Select EMPLID from PS_AERUNCONTROL where PROCESS_INSTANCE = %PROCESS_INSTANCE and ORPID = %oprid
2) People code:
&SQL = “Select EMPLID from PS_AERUNCONTROL Where PROCESS_INSTACNE =” | PS_TEST_AET.PROCESS_INSTANCE | “and OPRID =” | %oprid.
%PROCESS_INSTANCE or %Bind (PROCESS_INSTANCE) can be used.
%PROCESS_INSTANCE is more efficient and faster
Execute Application Engine through Push Button
1) CreateProcessRequest () , Schedule ()
CallAppEngine (applid [, state record]);
3 trace parameters you can pass to your psae.exe?
Best Trace option
– TRACE 384 – mostly used trace value.
Difference between a Trace value and Trace parameters
A Trace parameter determines which type of trace is turned on.
A Trace value determines what type of data is recorded in your trace files (s).
Where does the trace file is written to?
The trace file is written to the server directory when you’ve specified the trace on the client. If the client has %Temp% set to a drive or directory that does not exist on the server, PeopleSoft Application Engine does not generate a trace file. For an Application Engine program running on a server, PeopleTools writes the generic PeopleTools trace for SQL and PeopleCode trace files to the same directories as the AET traces. The prefix of the trace file name is also the same, and the suffix is trace. On the Windows workstation, the trace is written to the “People Tools Trace File” specified in the Trace folder of PeopleSoft Configuration Manager.
3 common ways to pass a trace parameter and value to your program psae.exe
Configuration manager, Process Definition, Command prompt.
Different types of Application Engine?
- Standard: Which is a Standard or normal entry-point program.
- Upgrade Only: Used by PeopleSoft Upgrade utilities only.
- Import Only: Used by PeopleSoft Import utilities only.
- Daemon Only: Use for daemon type programs.
- Transform Only: Support for Extensible Stylesheet Language Transformations (XSLT).
One PSDAEMON process can run for each row in the PS_SERVERDEFN table. The PS_SERVERDEFN.DAEMONENABLED field must be set to 1.
System automatically modifies all static references in other programs to the renamed program
The system automatically modifies all static references in other programs to the renamed program. For instance, if you call the renamed program from another Application Engine program, the Call Section action in the calling program is modified to reflect the new program name. All sections and steps are saved under the new name. Only one occurrence of a program name can exist for a given database.
PeopleSoft Application Engine recompiles a reused statement
%Clear Cursor function
Restart AE program
- State Record: One of the state record needs to SQL Table, Since All Derived work record will be re-initializing on commit.
- Program Properties: On the Advanced tab in the program properties dialog box, make sure that disable restart is not checked.
- Configuration manager: In the configuration manager, sure that Disable restart is not selected on the process scheduler tab.
- Section type: The option for section type are prepare only and critical updates. If the section is preparing data i,e select data, Populating temporary tables, or updating temporary tables then the section should be prepare only. If the section is updating the permanent application tables in the database, you should select critical update.
- Add an order by clause:
%Select Field Select Field1 from PS_SOME_RECORD Where FIELD1 > %Bind (FIELD1) Order by FIELD1.
- Add a switch to the selected table
- Delete processed rows.
The only restriction for batch runs occurs when you have restart enabled, and you are inside a Do Select that is of the Select/Fetch type (instead of “Re-select” or “Restartable”). with select/Fetch, all commits inside the loop are ignored, including the commit frequency if it’s set.
Why Sections are reordered alphabetically by name?
Sections are always reordered alphabetically by name at save time to make it easier to locate a given section. However, order of execution is dependent on internal call section references, and is therefore independent of the order that sections are inserted and displayed.
What does On Error property do?
The On Error property does not apply to compile errors (for example, specifying erroneous SQL statements). It checks only for execution-type errors. If your program has a syntax error, the program terminates.
What is to be intend to include multiple SQL statements within a single action?
Use the meta-SQL construct %EXECUTE. The previous sample SQL statement sample contains bind variables from a previous Application Engine action. Entries in the AEREQUESTTBL table do not have any effect on Application Engine programs called from PeopleCode using the CallAppEngine function.
Tables Used in the Process Request Page
The Application Engine process request page inserts values into the following tables:
- AEREQUESTTBL: Contains all of the values that appear on the page except those in the Parameters group.
- AEREQUESTPARM: Includes only initial state record values specified in the Parameters group, if needed.
Invoke an Application Engine program through the command line
- Restarting: When a program abends, a system administrator might restart the program using the command line. If needed, you can locate all of the specific program and process information from Process Monitor on the Process Request Detail dialog box. Normally, users (or system administrators) perform a restart from Process Monitor.
- Development or Testing: Many developers include the command line in a batch file to launch a program they are developing or testing. This way, they can quickly execute the batch file as needed. This also enables separation of development of the application program from its associated pages.
- Debugging: To debug a program running on the server, you can sign into the server (using telnet, for example) and invoke the program from the command line.
Bulk Insert is also ignored when all three of the following conditions are true: the database platform is Oracle, the record contains and EFFDT field (effective date), and the record contains a mobile trigger. This is required because an Oracle database does not allow the reading of mutating tables in a row trigger.
Populate PS_MESSAGE_LOG instead of Log Message action
You can also use MessageBox PeopleCode to populate PS_MESSAGE_LOG instead of using the Log Message action. This enables you to easily record errors encountered within Application Engine PeopleCode programs.
Application Engine program is not set up for restart ability
When an Application Engine program is not set up for restart ability, then commits are not controlled, monitored, or restricted by PeopleSoft Application Engine. When Restart is disabled, commits are controlled by the program. PeopleSoft Application Engine does not commit a step containing a Do Select action with the Select/Fetch option enabled until the entire step completes successfully, regardless of the other options you have selected.
Set Based Processing
Set Processing uses SQL to process groups, or sets, of rows at one time rather than processing each row individually. With row-by-row processing you following a repetitive loop that selects a row, determines if it meets a given criteria, if so, apply rule x to row Update row, commit. With set processing, you only select those rows that meet the filtering criteria and then run the rule once again all the affected rows.
Advantages of Set Processing
- Improved Performance: Our internal testing has revealed that, in an overwhelming majority of cases, set processing performs significantly better than it is -by-row counterpart for “reasonable” batch processing volumes. Minimized SQL Overhead: It tends to use fewer Application Engine SQL statements that each processed more data than the statements executed in row-by-row processing.
- Easy Maintenance: – If need to make a fix or add an enhancement to SQL, it’s just a matter of modifying the SQL or inserting the new “Chunk”.
- Leveraging the RDBMS: With Set – based processing, you take advantage of the SQL processing engine on the database rather than placing the processing burden and overhead on the application executable.
Testing and debugging application engine
Application Engine Trace file-You can track the step execution of your application execution.
Application Engine Interactive Debugger – First click on the trace tab and turn off the statement timings. Select Profile -> Edit Profile-> Process Scheduler Tab -> Application section Select Debug check box.
(You can dynamically modify the state record run application engine step by step. People Code Debugger for the Application Engine.)
AE Trace files: – AE__.AET
Without the process instance: – AE__.AET
Order and flow of action types
1) Do When
2) Do While
3) Do Select
6) Call Section
7) Message Log
9) Do until
How running AE program as Batch differs from running it online
Application Engine programs are designed for two types of execution and each has its own pool of Temporary Tables
- Invoked through the Process Scheduler.
- Run for longer amounts of time, asynchronously, and at scheduled times.
- Can be designed for parallel execution for performance.
- Uses the Batch/Dedicated Temporary table.
- Restart able, If not Re-startable on Program completion.
- It allocates instance number based on the availability on a record by record basis and psae.exe begins with the lowest instance numbers. If the properties are set continue – Base table is used with Process instance as key.
- If Re-starable – Locked across Restarts until completes successfully.
Access property in the SectionWhich actions are mutually exclusive and why they are mutually exclusive?Error handling or Exception handling in Application Engine
Step propertiesOn Error
- Abort – Write message to message log and terminate.
- Ignore – Write message to message log and continue.
- Suppress – No message will be written, but program will continue.
People code Action
On return options can used to handle run time errors.
- Abort: Exits immediately – Not recommended.
- Break: Exits the current step and section and control returns to the calling step.
- Skip Step: The program exits the current step, and continues processing at the next step in the section. If this is the last step in the section, the calling step resumes control of the processing.
SQL Action properties
- No Rows: When the Sql doesn’t return any rows, you can tell what application engine program should do.
- Abort: Program terminates.
- Section Break: Application Engine exits the current section immediately, and control returns to the calling step.
- Continue: The program continues processing.
- Skip Step: Application Engine exits the current step immediately and moves on to the next step. When using skip step keep the following in mind:
- Application Engine ignores the commit for the current step at runtime
- If the current step contains only one Action, only use skip step at by-pass the commit.
What are different types Do Select
- Select/Fetch: Opens the cursor only at the first time and retrieve rows one at loop. Commits inside the step (commits in the Called Section) are ignored if AE is Restart enabled.
- Reselect: It opens the cursor and closes the cursor on each iteration of the loop. It will reselect the same row of data. Logic in Actions of the step should be such that it will be changing the status of the rows in the table the do select is selecting. Commits are not ignored and will be committed in a reselect loop when the restart is enabled.
- Restartable: similar to select/Fetch but it WILL COMMIT inside the loop thus allowing the checkpoint to the PS_AERUNCONTROL table.
Difference between exit (0), exit (1) when we are using this functions in AE
Exit (1) causes immediate termination of a PeopleCode program. Use this parameter to rollback database changes.
Exit (0) caused immediate termination of a Peoplecode Program but don’t make rollback in the database.
What is the Mandatory property of App.engine?
Is the Mandatory property of App.engine:
- Main is the required section in Application Engine.
- There can be multiple steps in single application engine but at least one step should be part of AE.
- Similarly you can have multiple actions in AE but you should have minimum 1 action part of step.
- Save as AE
They are used to store the immediate values during the processing of the AE. They help in batch processing by creating multiple instances and there by facilitating parallel processing. If used properly they can greatly improve the performance.
They are used to pass on values from one section/step/prog to the other. There can be a max of 200 state records that can be used in a single AE but only one of them can be default. state record name must end with _AET.
Differences between State and temporary records
1. State record at any point of time has only single line information as it’s driven by the Process_Instance as key field. While temp table can have multiple rows.
2. State record had only single instance while the temp table can have maximum of 99 instance.
3. State record is used for restart logic.
4. State record can be used for dynamically calling AE section by adding AE_SECTION AE_APPLID as fields. Temp table cannot be used.
Why temporary records are needed?
Defines the record definition as a temporary table. Temporary images of the table can be created specified by the PeopleTools Options Page. Temporary tables are used for running Application Engine batch processes. Temporary tables can store specific data to update without risking your main application table.
Prerequisite for the Temporary record
1) Should have process Instance as key
2) Name should end with _TAO
Different types of Temporary tables
Potential performance improvements by splitting the data to be processed into groups and simultaneously running multiple instance of your program to deal with different groups of data.
If you have a one program that uses a temporary table and is invoked multiple times, that single temporary table could be used concurrently in multiple executions of the code. This could create unpredictable results since the different instances of the code would be issuing delete, Inserts and/or updates unsynchronized with each other.
You could solve the problem by creating multiple temporary tables as a pool of tables. Each invocation of your program would have to allocate an unused temporary table,mark it as ‘in use’ , use it and release it back to the pool when you through with it (for each Application Engine program you write).
What are the important steps for implementing the parallel processing
1. Define you Temporary Tables.
2. Set the Temporary Tables Online pool.
3. Assign Temporary Tables to your Application Engine program in it program.
4. Set Temporary Table Batch Pool – Instance count in the AE.
5. Build / Rebuild your Temporary Table record.
6. Code %Table Meta – SQL as reference to Temporary Tables in your Application Engine program, so that Application Engine can table references to the assigned Temporary Table instance dynamically at runtime.
What happens when all the instance of the temporary table are in use?
It behavior can control by AE developer. If the runtime options are set to continue “People Tools will insert rows into the base table using the PROCESS_INSTANCE as a key”. If temp table doesn’t contain PROCESS_INSTANCE as a key field in a Temporary table, you should change the Temp table runtime options to “Abort” in the appropriate Application Engine programs.
Divide the data to be processed by different instance of the program to perform parallel program
Run control parameter passed to each instance of the AE program enable it to identify which input rows “belong” to it, and each program instance inserts the rows from the source table into its assigned temporary table instance using %Table.
Difference between Application Engine Do-while, Do-When and Do-select
In DoSelect we don’t need to increase the counter. Pointer to the next row in the selected data will be moved automatically. In DoWhen we need to write the select query to skip the processed row.
Do-while keeps running till the condition runs true.. And do-select runs for each row and moves to the next row.
like if you have statement: ‘SELECT EMPLID from PS_EMPLOYEMENT’ and PS_ EMPLOYEMENT has 2000 rows then do-while keeps running forever and selecting the same row every time but do-select will run for 2000 times fetching the next EMPLID each time.
Difference in using SQL directly and calling SQL from PeopleCode in Application Engine
If you use SQL directly then it will go to database directly to fetch rows of data. But if u write SQL’S in PeopleCode then the trip will be from application server to database because PeopleCode resides in app server.
One should avoid writing SQL’S in App engine PeopleCode as it would degrade the performance.
Run an Application Engine program in different ways
1. Running from Application Designer.
2. By calling People Code function.
3. Running from DOS Environment (Debugging).
4. Running from Application Engine People Tool.
5. Running from PeopleSoft Application.
Why can’t the SQL and call Section Actions be present simultaneously in a single step?
Call section and SQL actions are mutually exclusive so they cannot be called at a time. There is chance to get into an infinite loop if they are appeared in the same step.
Test an application engine program
One can test Application in two ways:
1. Running Application engine from 2-Tier
2. Running Application engine from 3-Tier.
Running Application Engine from 2-Tier
Open Application Designer Then open desired Application engine. Then on top middle tool bar you can see ‘Run Program button’ click on to that button give vales to parameter like Process Instance Run Cntrl id etc and click. This will run application engine and generate a trace file to your local system. After completion of Application engine you can see if you got desired results or not.
Running Application engine from 3-Tier
Open 3-Tier link Go to peopleTools-> Process Schedular -> Process Add you Application engine process here in ‘Process Defination Options’ give ‘PRCSMULTI’ in COMPONENT and ‘TLSALL’ in Process Group and then Save the page.
After defining Application engine Go to peopleTools-> Process Schedular -> System Process Request then add a RUN CONTROL ID After adding run control id click on the RUN button on the page this will lead you to page where all the process will be listed Select you process name (Application engine Name) and clik on ok. This will run you Application engine.
Application Engine event in PeopleCode
Application engine PeopleCode event is On Execute.
Debug your Application Engine
1. Go to configuration manager of 2 tier (App Designer). Then navigate thru Profile > Edit > Process Scheduler > Application Engine check the Debug option.
2. Open the App Engine program needs to debug and click the traffic light run program button.
3. Enter the run control id & debug the AE.
Difference between AE and SQR
1. AE is very systematic as it is structured as Section step and Action. While SQR is not systematic.
2. AE flow is easy to read and understand while SQR is not that easy.
3. AE is easy to debug as you can do line by line debugging. With SQR it’s not easily possible.
4. By using SQR you can create and maintain different reports which are not possible in AE’s.
Difference between sql statements and metasql statements
The SQL statements are used to fetch/retrieve update insert delete data from the database. In PeopleSoft we use the sql stmts using sqlexec or createsql. Differnet sql statements are: Select Insert Update Delete.
Different RDBMS have difference date and time formats. Though the component processor takes care of the automatic conversion but at the time of using the sqlexec the automatic conversion does not take place. So in order to understand this the meta sql concept has come. Which we can use in diff sql stmts. for example datetimein date time currentdatetime, datetimeout etc.
dateIn: This is used in the where clause of Select and update.
dateout: This is used while selecting the data.
Inbound and outbound Process
by using a file layout requires the following sequence of activities:
1. Provide the import data in a properly formatted flat file.
Each record in the file must correspond to a record with the same name in the PeopleSoft database, and its fields must have the same names and data formats as the database record’s fields. Each record in the file must end with a new line character.
2. Create a file layout definition to match the record and field structure of the data.
Insert the appropriate record definitions into the file layout, then reposition the file records and file fields to match the record and field positions in the file.
3. Preview and troubleshoot the input data format and content.
4. Generate the data import Application Engine program and PeopleCode.
5. Run the Application Engine program to import the data.
To export data to a flat file, you create a file layout definition, and then write PeopleCode to transfer the data to a file. The PeopleCode can be initiated from Application Engine or from any event. It should populate text strings, rowset objects, or record objects, and it should apply the WriteRecord or WriteRowset method from the File class to transfer the data to the file by using the file layout definition to position the records and fields as required by the target application.