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

Load Lookup Arrays in SQR

How to improve the performance of the SQR using Load Lookup Arrays?

For Simple database related validation, we will fire a SQL to check the sanity of data. If this data validation is to be fired for each and every row of data, then it is better to go for Load-Lookup Arrays. This is like Prompt table validation in the case of Online Pages.

In this way, we can reduce the database hits and thereby increasing the Performance of the SQR Program.

For Eg: If we want to validate the Earnings Codes present in the file, we can use these arrays to validate the Earning Code data.

Generally we will use the below SQL to validate the ERNCD data

SELECT PET.ERNCD, PET.EFFECT_ON_FLSA, PET.ADD_GROSS
FROM PS_EARNINGS_TBL PET
WHERE PET.EFFDT= (SELECT MAX (EFFDT) FROM PS_EARNINGS_TBL
WHERE PET.ERNCD = ERNCD
AND EFFDT <=(SYSDATE)

AND PET.EFF_STATUS =”A”

AND PET.ERNCD = $Erncd
In order to avoid these database hits for each and every row of data, we can the below code to load all the valid Earning Codes at once from the Database to Load Lookup Array Name EARN and then use the array to validate the input ERNCD data.

! Lookup array for EARNINGS CODES

Let $where = ‘PET.EFFDT = (SELECT MAX (EFFDT) ‘||

‘FROM PS_EARNINGS_TBL ‘||

‘WHERE PET.ERNCD = ERNCD ‘||

‘AND EFFDT <=SYSDATE) ‘||

‘AND EFF_STATUS =”A”’

LOAD-LOOKUP

NAME = EARN

TABLE = ‘PS_EARNINGS_TBL PET’

KEY = ERNCD

RETURN_VALUE = ERNCD||’-'||EFFECT_ON_FLSA||’-'||ADD_GROSS

WHERE = $where

QUIET

In this case, KEY (This is nothing but Input Data value) is ERNCD and RETURN_VALUE (Output data value) is Combination of ERNCD, EFFECT_ON_FLSA and ADD_GROSS database field’s value.

! Checking Load lookup array for data validation

Lookup EARN $input_data $output_data

Note: It is good to initialize these Load Lookup Arrays in the SETUP Section related procedures.

Read More About Load Lookup Arrays