Converting Data into P2k Tables (PL / SQL Routines)#

The conversion of the data from the interface tables, into the application tables, is actually performed by stored procedures within the database. There are over 60 of these procedures stored in the P2K schema of the database. Each procedure will read the contents of one of the interface tables and insert the records into the corresponding P2K tables. The records being inserted in the database may actually insert into several tables, from the one interface table. For example, the data from the INF_CV_PERSONALS table is actually inserted into the P2K_HR_IDENTITIES, P2K_HR_PERSONALS, P2K_HR_ALIASES and P2K_AM_USER_FIELD_VALUES tables. Certain columns within this interface table will store their data in one of these columns of these tables. At the end of each report that the conversion process generates the number of records inserted into each table will be identified.

The Conversion drop down menu in the Conversion Application can be used to start these procedures. All the stored procedures are grouped within the menu by the module they are a member of. There is a set of procedures for Human Resource tables and for Payroll tables. As well as several other modules that can have data loaded in, using this process. When these procedures run they will create an output report that will be displayed in a report preview screen and can be printed if needed. However, there reports are extremely long. They will contain a list of both the records that succeeded in being converted, as well as the failures. If the interface table contains thousands of records, the report could be hundreds or thousands of pages long.

Each stored procedure has a maximum error limit defined within the code. If the number of bad records processed exceeds this limit, the conversion procedure will abort. This error condition, as well as the number of records processed, will be shown at the end of the report. When the report is displayed in the report preview screen, go to the second last page to see the number of records inserted into Wiki tables. The last page of the report is simply a banner page and does not contain any useful information.

As the records are read and processed by the conversion procedures they are deleted from the INF table if they are successfully inserted into the application tables. When the conversion procedure completes, if any records generated errors they will not be moved to the application tables and will not be delete from the INF table. These records can be corrected within the conversion application screens and then converted again to be moved to the application tables.

When the number of bad records exceeds the maximum error count allowed in the conversion procedure and abort error will be displayed at the end of the conversion report. The records that remain in the INF table of the database will contain both error records and records that have not been processed yet. The unprocessed records could be additional error records or valid records that would be moved to the P2K tables if there were processed. Until the error records are corrected (or deleted) these additional records will not get processed by the conversion routines.

Since the report can be extremely large after the first run through the records in the interface table, the initial report can be ignored and the conversion process be run a second time. During the second processing of the records it will abort after processing the first 50 errors. All the good records will have been deleted from the interface table, leaving the bad records and unprocessed records after them. This version of the report will contain errors only and can be printed without wasting a large amount of paper with irrelevant information.