Correcting Data in the Interface Tables#

Since the size of the modification tokens in the SQL Loader .CTL files are limit to 256 characters and can only work with one record at a time, there are times when the data must be loaded into the INF table before it can be modified and then converted. This also simplifies the extract and load process for the legacy data, as all the data manipulation to fit the environment is done with .SQL scripts after the data has been loaded into the INF tables.

This step of the conversion process is optional, and may not be needed for all files being loaded in. Each table will need to be evaluated to see if modification scripts will be needed to complete the conversion of the data. Typically, modification scripts are used on the data if it is easier to access the needed information from other tables in the application. An example of this would be the loading of SUNDRY LINES. The IPSN screen, the P2K_PR_SUNDRY_LINES and P2K_PR_SUNDRY_LINE_DETAILS tables require specific data for the pay components to be loaded into the records. This information is stored on the pay component definition tables and can be seen in the IPPC screen. It is very unlikely that this information will be available to the routines that will be used to extract the data, or during the loading of it into the INF table.

Other modification scripts can be as simple as removing leading or trailing characters from specific columns before conversion. While these types of modification can be done during the extracting of the data or during the loading of it with the SQL Loader CTL file tokens, it may be more convenient to do all the modifications after the data is loaded.

One of the most common types of modifications that can be included at this point in time is to delete records that are known to cause an error during the conversion. The PILOT database is being copied to the CONV/PROD database at the start of the conversion process, but is continuing to be added to and changed during the implementation process. Some of the data that is need by the converted data for validation checks in this database will not be available until additional modules have been trained and implemented. For example, after the Foundation module has been trained, some of the departments, jobs and positions will have been keyed in, but not all of them. Until all these records have been created in the database, employees in these departments, job and positions will generate errors in the conversion routines. To avoid these errors, or to ensure that routine doesn’t abort due to an excessive number of errors, you should delete the records from the INF table until these records have been created and moved from PILOT to CONV.

The following sample script will delete all the records from the INF_CV_ASSIGNMENTS for employees that the POSITION record does not exist for:

DELETE FROM INF_CV_ASSIGNMENTS
WHERE POSITION_CODE NOT IN
(SELECT POSITION_CODE FROM P2K_CM_POSITIONS);