peoplesoft development overview application engine
TRANSCRIPT
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• XSLT—Only 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
• Example—Process sample to countSELECT ‘X’FROM PS_TEST_RECWHERE count < = %bind(totemp);– Other actions– Action that triggers the falseUPDATE PS_TEST_RECSET 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 yearFROM 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• ExampleUPDATE 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
statement—not 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, effdtFROM PS_JOBWHERE effdt < %CURRENTDATEIN
%DATEIN
• Date variable is used as an input variable• Formats Date to Database Date
Specifications• Example
SELECT emplid, effdtFROM PS_JOBWHERE 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_JOBWHERE 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, amountFROM PS_TEST_RUN_CONTROLWHERE oprid = %OPERATORID
%RUNCONTROL
• Returns RUNCONTROLID that is being used to run the Application Engine Program
• Example%SELECT(effdt, erncd, amount)SELECT %dateout(effdt), erncd, amountFROM PS_TEST_RUN_CONTROLWHERE oprid = %OPERATORIDAND 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, nameFROM PS_TEST_REC
%SELECTALL
• %SELECTALL will select all the fields from the record
• If the field is a date it automatically uses the %DATEOUT
• ExampleLocal 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– ExampleSENDMAIL(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