How to design an incremental/delta outbound interface?

In our professional life as PeopleSoft consultant, many a time we come across requirement for an outbound interface where we need to write/push data that had changed between last run and current run to a third party application. 

In this blog we list the steps of a very simple but working design approach. 

The example below assumes a file based integration but same design approach will also work for other integration. 

For this example, the requirement is to pull changed  active employees personal data (EmplId, Empl_Rcd, Name, Address1, Email ID and Phone) and write to a csv file.

In this approach we will use the following PeopleSoft object definitions:

• SQL Table - PS_SOA_PERSDT_NEW - stores the information of current run.
• SQL Table - PS_SOA_PERSDT_OLD - stores the information of previous run
• SQL Table - PS_SOA_PERSDT_INTF - stores the rows of those employees whose data has changed and thus has to be written to the output file.
• SQL Table - PS_SOA_PERSDT_HIST - maintain the history data
• An application Engine Program with SQL and PeopleCode actions
• A File layout

Below is the working flow of the outbound process:

• Truncate PS_SOA_PERSDT_OLD record
• Populate PS_SOA_PERSDT_OLD record from PS_SOA_PERSDT_NEW
• Truncate PS_SOA_PERSDT_NEW record
• Populate PS_SOA_PERSDT_NEW with employee id from PS_JOB where HR_STATUS=A and using the effective dated logic
• Update the required information for employees in PS_SOA_PERSDT_NEW using Correlated update and taking care of null values
• Update Name 
• Update Address 
• Update Email ID
• Update Phone Number
•  Populate the PS_SOA_PERSDT_HIST table with the previous run data present in PS_SOA_PERSDT_INTF table
• Truncate PS_SOA_PERSDT_INTF record
• Find the employee whose information had changed PS_SOA_PERSDT_NEW minus PS_SOA_PERSDT_OLD) and insert it to PS_SOA_PERSDT_INTF
• Using File Layout based on PS_SOA_PERSDT_INTF record, write to a file using standard PeopleCode built-in functions.
• Write Log Report based on the data in the PS_SOA_PERSDT_INTF

Contributed by Milan