PeopleSoft : App Engine: XML Export File using File Layout

Creating a File Layout.

1. Create or Select a Record for your data.  I used a view, to avoid having to deal with joins in PeopleCode.
2. Create a File Layout Definition.
3. Create an Application Engine Program, with a tiny bit of code in it.
4. Stuff all these things into a project so you can maintain your sanity at a later time.

After you have selected the record with the data you want to export, create a new File Layout Definition (File->New Definition->File Layout).

On the definition tab, right click “NEW FILE” and select Insert Record.  Search for the record you are using for your data, and double click it.  The Record Definition, and its columns will show up under “NEW FILE” now.  Save your File Layout Definition.

Click the Property Icon , then click the “Use” tab, and select XML in the “File Layout Format” drop down. Click OK.

Create a new Application Engine Program (File->New->Application Engine Program).  Right click “Step01″ and select “Insert Action”.  Click where it says “SQL”, and select “PeopleCode” from the drop down.

Double click the new action to edit the PeopleCode.  Copy and paste this PeopleCode into the window:
Local Record &Rec;
Local File &File;
Local SQL &SQL;

&File = GetFile("c:\temp\export_sample.xml", "W", %FilePath_Absolute);
If &File.IsOpen Then
If &File.SetFileLayout(FileLayout.YOUR_FILE_LAYOUT_DEFINITION) Then
&Rec = CreateRecord(Record.YOUR_RECORD_DEFINITION);
&SQL = CreateSQL("%Selectall(:1)", &Rec);
While &SQL.Fetch(&Rec)
&File.WriteRecord(&Rec);
&File.WriteLine("</YOUR_RECORD_DEFINITION>");
End-While;
Else
Error ("File Layout Not Correct");
End-If;
Else
Error ("file not open");
End-If;
&File.Close();

PeopleSoft : Differences between App Engine and SQR

App Engine	SQR
Advantages	Disadvantages
Maintained with in PeopleSoft, so Impact analysis is much easier. Changes to objects need not be dealt with every A.E every time.	Lies outside PeopleSoft, so separate Impact analysis is required. Changes need to be done manually in every file.
A.E. is a PeopleTool object designed as an alternative for SQR, COBOL, etc for background processing using SQL and PeopleCode.
Can be – Run from Application Designer. – Called from People Code function. – Running from DOS Environment (Debugging). – Running from Application Engine People Tool. – Running from People soft Application.	Can be – Process Scheduler –Command Line (Can take user input) –Winddoes
Reusability Can use Component Interface Integration Broker Message Catalogs
Ease of coding We can use Meta-SQL in PeoplCode/SQL. These expand to SQL in runtime thus decreasing the effort of coding For e.g. %Join àJoins the given tables using common fields or keys, can exclude list of fields.  Avoids rework even when record structure changes. %EffdtCheck à Creates the sub query for effective date check %Coalesce à Avoids database dependent coding for NVL
State table/AET table (SQL Table or Derived/Work Record) used to store/share Program level variables. Temp Tables to break complicated logic/sql and perform faster.  Since all is done in database the processing is faster. Output doesn't reach permanent tables until program is successful. Avoids deadlocks etc. in case it abends it can be fixed and rerun again.	Have to use variables or arrays to pass values.
Restartability à In case the program abends due to some issue it can be set to restart from that point instead of running from start all over again. AET table must be SQL Table for this. Repeatability à SQLs can be set for reusability thus saving recompile time and running faster.
Different sections can be created for different Markets, or different Platforms (databases)
Debugging- Has built in debugger Trace can be set for various levels of debugging	Must use SHOW or DISPLAY for debugging. No debugger.
Can call other A.E. using Call Section or use Functions in FuncLib or App Packages.	Can call functions in SQCs.
Disadvantages
Programming technique is very restrictive. Cant use SQL and PeopleCode together.  Has its own advantages.
Restricted View Can see one SQL/peopleCode at a time.	Can see full program. Understanding is easy.  Even editor can be of our choice.
Control Structure is fixed	Flow is not restricted.