How to build an efficient interface using AE or SQR

While building an Inbound/Outbound interface, most of the time we may be in dilemma whether to use Application Engine (AE) or SQR. Here are the tips to build an efficient interface using either AE or SQR.

If we use Application Engine, below points are to be taken into account

• If the interface is INBOUND, its better to go for Application Engine
• If the program fails, we can make use of Restart Control to restart the program from the position where it abends
• PeopleSoft – Business Process related Validation will be taken care automatically when we go for AE/CI.
• Parallel Processing can be easily achieved using AE. This can be done by using Temporary Tables and State Records.
• Reusability of SQLs is very much easy when we go for AE.
• Bulk Insertion of data and Set Processing of data can be easily done in an AE
• COMMIT levels can be maintained either at SECTION or STEP levels
• Generation of Reports using File Layout, Workflow related batch interface can be easily developed using an AE
• XML related processing is quiet easy when we go for AE
• When we go for Upgrade of the Current PS System, Retrofitting of AE is done by easily identifying the impacted objects.

If we are going to use SQR, below points can be taken into account to build an interface

• Formatting of Reports is very much easy in SQR using Begin-Header, Begin-Footer and Begin-Setup commands.
• If we are going to consider the Performance of the Interface Program, its better to go for SQR.
• Better Performance can be achieved by using Load-Lookup Arrays and Dynamic SQL
• Reusability of SQLs can be achieved by creating either generic Procedures or SQCs

PeopleCode to Create XML and XSD file

The below code is useful to create XML and XSD file.  Create Application engine program with below code and run the AE program in App designer to get a file in local. You can get the same in server without specifying any path and give it as relative path instead of file absolute.

PSXP_XMLGEN application package used to create xml file. You can multiple parent and child records. This file is used for data import or export programs.

Import PSXP_XMLGEN:*;

/*Create Rowsets*/

&RS_ADR = CreateRowset(Record.EMPLOYEES);
&RS_PER = CreateRowset(Record.PERSON, &RS_ADR);

/*Fill Parent*/
&RS_PER.fILL(“WHERE EMPLID = ‘11902′”);

/*Fill Child*/
For &i = 1 To &RS_PER.Activerowcount

&row = &RS_PER.Getrow(&i);
&rs = &row.Getrowset(Scroll.EMPLOYEES);
&rs.fill(“Where emplid = :1″, &row.PERSON.emplid.value);
End-For;

/* Create XSD */

&rds = create PSXP_XMLGEN:RowSetDS();
&myschema = &rds.Getxsdschema(&RS_PER);
&f1 = GetFile(“C:\temp\rpt01.xsd”, “w”, %FilePath_Absolute);
&f1.writeline(&myschema);
&f1.close();

/*Create Sample XML File */

&myXMLfile = &rds.GetXMLData(&RS_PER, “C:\temp\rpt01.xsd”);
&f2 = GetFile(“C:\temp\rpt01.xml”, “w”, %FilePath_Absolute);
&f2.writeline(&myXMLfile);
&f2.close();