PeopleSoft Development: Overview of Application Engine and the Query Tool
Presented by: Judi Doolittle (Judi Hotsinpller) and Barbara Sandoval
Agenda
Introductions Logistics Application Engine (Morning Session)
History Program Structure Properties Action Types Break 10 mins MetaSQL and MetaVariables Running an AE Program Printing Options Break 10 mins Sample Review of Take Home Exercise
Agenda Continued
Query Tool
Introductions
Instructor Introductions Student Introductions
Logistics
Class Format Restroom Location Breaks
Application Engine History
Released with Version 8.0 Developed by PeopleSoft Coder
Prior Tool COBOL Used for Batch Processing
Application Engine Vs. SQR Future
Fusion XML
Application Engine Program Structure
Application Designer
Application Engine Program Structure
Structure
Section
Steps
Actions
Application Engine Properties
General Tab
Track changes Document
State Tab
State Record Attributes
Temp Tables Tab
Define Set Assign For Batch Processing Build/Rebuild Meta SQL
Advanced Tab
General Tab
State Tab
Temp Tables Tab
Advanced Tab
Action Types
Do When Do While Do Select PeopleCode SQL Call Section Log Message Do Until XSLTOnly shows if it is specified as a Transformation Program
Do When
It is comparable to an IF statement in other Languages When true returns one or more rows and continue with actions When false exits step
Sample
Do While
Executing step while SQL is true To prevent an endless loop:
Requires code in another action The data must change to make the SQL false
ExampleProcess sample to count
SELECT X FROM PS_TEST_REC WHERE count < = %bind(totemp); Other actions Action that triggers the false UPDATE PS_TEST_REC SET count = count + 1;
Do While Example
Do Select
Loops through records one at a time Used to populate State Record Three Looping Rules
Select/Fetch (Default) ReSelect ReStartable
Example of Populating State Record
%SELECT(year) SELECT year FROM PS_RUN_CONTROL
Do Select Example
PeopleCode
Reasons why PeopleCode Used:
Manipulate and Manipulation Functions Application Packages Component Interface Reuse Developed Code
Refer to PeopleBooks for PeopleCode instruction
PeopleCode Continued
Unique to Application Engine
How to use State Record in PeopleCode Example
PS_TEST_AET.YEAR
Return Parameters
Exit(1) causes set parameter to executed (Execution can be ABORT, BREAK, or SKIP STEP) Exit(0) default will continue
PeopleCode Continued
SQL
SQL actions used for
INSERT UPDATE DELETE
Can be used with any of the Conditional Actions Or by themselves Example UPDATE PS_TEST_REC SET bonus = %bind(bonus) WHERE year = %bind(year)
SQL Continued
Call Section
Allows you to Call another Section Reason is for readability
Call Section Continued
Example 1
Call Section Continued
Example 2
Call Section Continued
Example 2 Continued
Log Message
Writes Messages to the Log File Messages stored in Message Catalog Parameters can be provided as part of the Message To create a custom message use Message Set > 20000
Do Until
Another Conditional Action Processes Until Condition is False Needs Another Action to make Condition False Difference between Do While and Do Until
Do While pre-processes and checks condition prior to other actions Do Until post-processes and checks condition after other actions
Do Until Continued
How to Run
Process Definition .Bat File
Process Definitions
Process Definitions Continued
.Bat File
\\ServerName\bin\client\winx86\PSAE.exe -CD database -CO USERID CP PASSWORD R RS_TEST_RUN_CONTROL AI PS_TEST_AE Note your PSAE.exe location is unique to your enviroment Description of parameter is immediately following See PeopleBooks for a comprehensive list of Parameter values
MetaSQL and MetaVariable
Quick Definition
SQL Functions Reserved Words Some are Unique to Application Engine Has to be in a SQL Statement Within PeopleCode it can be used in the SQLExec statementnot the most efficient Can use in CREATESQL in PeopleCode
%BIND
This is only used in AE Programs Retrieve fields from State Record Example
INSERT INTO PS_TEST_REC(emplid, bonus) VALUES (%bind(emplid), %bind(bonus))
%CURRENTDATEIN
Returns current system date Example
SELECT emplid, effdt FROM PS_JOB WHERE effdt < %CURRENTDATEIN
%DATEIN
Date variable is used as an input variable Formats Date to Database Date Specifications Example
SELECT emplid, effdt FROM PS_JOB WHERE effdt < %DATEIN(2007-12-01)
%DATEOUT
When selecting a date from the Database to ensure proper format Example
%SELECT(emplid, effdt) SELECT emplid, %dateout(effdt) FROM PS_JOB WHERE effdt < %CURRENTDATEIN
%OPERATORID
Returns USERID of person running the Application Engine Program Helps you get the correct row from the RUNCNTL Example
%SELECT(effdt, erncd, amount) SELECT %dateout(effdt), erncd, amount FROM PS_TEST_RUN_CONTROL WHERE oprid = %OPERATORID
%RUNCONTROL
Returns RUNCONTROLID that is being used to run the Application Engine Program Example
%SELECT(effdt, erncd, amount) SELECT %dateout(effdt), erncd, amount FROM PS_TEST_RUN_CONTROL WHERE oprid = %OPERATORID AND run_cntl_id = %RUNCONTROL
%SELECT
Used with a DoSelect Stores you data in the AET record Field Names must be valid Fields on the State Record Example
%SELECT(emplid, name) SELECT emplid, name FROM PS_TEST_REC
%SELECTALL
%SELECTALL will select all the fields from the record If the field is a date it automatically uses the %DATEOUT Example
Local Record &testrec; &testrec = CreateRecord(PS_TEST_REC) CreateSQL(%SELECTALL(:1), &testrec);
Printing
PeopleTools Delivers an Option to Print your AE Program Print in Flow Layout or Print in Definition Mode
Print Options
Sample Report
Advanced Topics
Using Email Input and Output Files
Using Email
Application Engine programs can send Emails
Used to notify when processes complete Or fail Also to send Notifications to Employees
Send Mail Function
Used within PeopleCode Example SENDMAIL(0, &EMAIL, , , &SUBJECT, &TEXT); For more Options refer to PeopleBooks
Input and Output Files
For interfacing with other Applications Excel, Access, and Text Files Process
Declare a Global Variable Path and Filename need to be Explicitly Defined
Delivered Functions
GetFile
This will open a file for read, write or update Example
GetFile(&path|&filename, W, %FilePath_Absolute)
FileName.Open
This will check to see if the file is open Example
If &InFile.Open Then
Delivered Functions Continued
Filename.Writeline
This will write to the file. Example
&Outfile.WriteLine(&string);
Filename.Readline
This will read from the file. Example
While &Infile.Readline . End-While
Sample Program
Walk through Handout
Discussion Time
Questions and Answers Workshop
Query Tool
