How to design a file based inbound interface or data conversion process in PeopleSoft?

In our professional life as PeopleSoft consultant, many a time we come across requirement for a data conversion process or an inbound interface where we need to read an input file and then load the data into one or more PeopleSoft components. 

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

The requirement based on which this design is illustrated is as follows

• Develop a run control page with file attachment functionality, so that end user can attach the input CSV file before running the Inbound Interface
• Develop an Application Engine Program to insert or update data to the department component using component interface.
• The input file can consists of the following data
• New department information
• New effective dated row for an already existing department
• Changed values for non-key fields for an existing department and effective date combination

Please note in real world for Department component we may not even write an upload process and can make use of Excel to CI.

1.	Create a custom run control page with file attachment functionality (custom run control record, page, component & menu), so that user can attach a file and system physically stores the file to a pre-defined location.
2	Develop an application engine program that performs the following steps.
	·         Delete data from custom staging (STG) table. The structure of this SQL Table will be similar to the input file with additional fields like PROCESS, ERROR_FLG and ERROR_NOS ·         Select the attached file from custom run control record and store that to state record ·         Load the data from CSV file to STG table, and update the PROCESS field of the STG table to “N” and ERROR_FLG to "N" ·         Perform SQL Data validation (mostly referential integrity validations) in the Staging table based on the requirement. Mark the rows which have errors by updating the ERROR_FLG to "Y". ·         Read the rows of data from the staging table where ERROR_FLG="N", and execute the CI and insert data to PeopleSoft component. Mark the PROCESS field value for the successfully processed row to "Y". In case of CI errors, mark the ERROR_FLG to "Y" for those rows in the STG table. ·         Generate Reports by reading the data stored in the Staging table where ERROR_FLG = "Y"

Contributed by Milan