Converting Pay History#
The conversion of Payroll History data is actually done in two steps. The conversion process starts with the loading and converting of PAY HEADERS, followed by the PAY PC AMOUNTS. The PAY LINES can optionally be converted for historical reasons, but they will not have any impact on future pays. The PAY HEADERS and PAY PC AMOUNTS will only be used for pay reversals or for the generation of the To Date amounts (IE year to date, quarter to date, etc.).
PAY HEADERS are loaded into the INF_CV_PAYHEADERS table. The following chart defines the layout of this tables and the typical values for the fields found within it:
| Name | Null? | Type | Notes |
|---|---|---|---|
| ENTITY_CODE | NOT NULL | VARCHAR2(16) | Used to link to the EMPLOYEE records on IEEI |
| PERSON_CODE | NOT NULL | VARCHAR2(16) | Used to link to the EMPLOYEE records on IEEI |
| EMPLOYMENT_TYPE | VARCHAR2(30) | Used to link to the EMPLOYEE records on IEEI | |
| HIRE_DATE | DATE | Used to link to the EMPLOYEE records on IEEI | |
| PAY_NUMBER | NOT NULL | NUMBER(5) | A unique number to identify the pay for each employee. IE each employee would have Pay Number 1 and count up for each additional pay. |
| USER_NAME | VARCHAR2(30) | The USERID who created the record. Typically set to P2K. | |
| UNIT_CODE | NOT NULL | VARCHAR2(16) | Unit code from the employees prime assignment record, as of the Pay Issue date. |
| GROUP_CODE | NOT NULL | VARCHAR2(16) | Group code from the employees prime assignment record, as of the Pay Issue date. |
| COUNTRY_CODE | NOT NULL | VARCHAR2(16) | |
| STATE_PROVINCE_CODE_HOME | NOT NULL | VARCHAR2(16) | Must be a valid entry from the IDSP screen. Typically set to the resident State code as of the Pay Issue date. |
| STATE_PROVINCE_CODE_WORK | NOT NULL | VARCHAR2(16) | Must be a valid entry from the IDSP screen. Typically set to the work State code as of the Pay Issue date. |
| PAY_CATEGORY_CODE | NOT NULL | VARCHAR2(16) | Must be a valid entry from the IPPGC or IPPGU, for Canadian or USA pays |
| PAY_CALENDAR_CODE | NOT NULL | VARCHAR2(16) | Must be a valid entry from the IPCL screen. |
| PAY_PERIOD | NOT NULL | NUMBER(10) | Must be a valid period for the Pay Calendar from the IPCL screen. |
| PAYROLL_CODE | NOT NULL | VARCHAR2(16) | Must be a valid entry from the IPPR screen. |
| BATCH_NUMBER | NUMBER(10) | Must be a valid entry for the PAYROLL from the IPBE screen. | |
| PAY_RUN_NUMBER | NUMBER(10) | Must be within a valid range defined on IMCS. | |
| SCHOOL_DISTRICT_CODE | VARCHAR2(16) | Used for school district tax calculation, if applicable. | |
| GEO_CODE_HOME | VARCHAR2(16) | GEO Code from resident tax jurisdiction on IEPI | |
| GEO_CODE_WORK | VARCHAR2(16) | GEO Code from work tax jurisdiction on IEAS | |
| AUDITED | VARCHAR2(4) | Check mark field set during UPAUDT> Typically set to '1' | |
| CHECK_NUMBER | NUMBER(10) | ||
| FINAL_PAY_METHOD | VARCHAR2(30) | Set to '01' if this records represents the last pay the employee received | |
| FIRST_PAY_IN_MONTH | VARCHAR2(4) | optional check mark field, set to '1' if fist pay of the calendar month | |
| FIRST_PAY_IN_PERIOD | VARCHAR2(4) | optional check mark field, set to '1' if fist pay of the pay period | |
| HAND_CHECK | VARCHAR2(4) | optional check mark field | |
| PAY_BY_CHECK | VARCHAR2(4) | optional check mark field | |
| PAY_HEADER_SOURCE | VARCHAR2(30) | Must be a valid lexicon entry. Typically set to '05' or 'External Source' | |
| PAY_HEADER_STAGE | VARCHAR2(30) | Must be a valid lexicon entry. Typically set to '70' or 'Closed' | |
| PAY_RUN_SEQUENCE | NUMBER(5) | ||
| POSTED_BENEFITS | VARCHAR2(4) | optional check mark field | |
| POSTED_ATTENDANCE | VARCHAR2(4) | optional check mark field | |
| QUICK_PAY | VARCHAR2(4) | ||
| TAX_WEEKS | NUMBER(18,6) | ||
| TIMESHEET_RETURNED | VARCHAR2(4) | ||
| CHECK_AMOUNT | NUMBER(14,2) | ||
| PAY_ISSUE_DATE | DATE | Date of the check or deposit. Determines the Year to add to. | |
| SUNDRY_ATTACHED | VARCHAR2(4) | optional check mark field | |
| JOURNALIZED | VARCHAR2(4) | ||
| REVERSAL_PAY_NUMBER | NUMBER(5) | Must point to a previously loaded pay header, or blank |
The data within the PAY HEADERS is relatively simple and doesn’t need a lengthy explanation. The only reason the data in this table can become complicated to convert if the sheer volume of records. Since each employee will have one, or more, PAY HEADERS per pay period per year, the number of records in this table can get quite large. With the addition of reversals, bonuses, YTD adjustments and other types adding to this number.
Since the majority of the records in the PAY HEADERS are for historical information only, the only records that need to be loaded and converted are for the current year. The records from previous years can be loaded in as well, but are not required for the application to process a live payroll. It is also suggested that the records be loaded in a phases to limit the number of records that will need to be processed by the load and convert functions. Simply splitting the data into batches by calendar year is usually enough to limit the number records each batch needs to convert.
The PAY HEADER records require a valid PAY RUN entry to be generated in order for the application to treat them correctly. Since a PAY RUN record can only contain PAY HEADER records for a single payroll and pay calendar code, this information can be created. A custom SQL script called INSERT_PAYRUNS.SQL can be run after the PAY HEADER records have been loaded into the interface table and before attempting to convert them. This script will insert records into the INF_CV_PAYRUNS table and update the data in INF_CV_PAYHEADERS to point to these new records.
After running the script the PAY RUNS and PAY HEADERS can be converted into the application. The PAY RUN records will have to be converted first so that the PAY HEADERS will be able to validate the PAY RUN number that was added to the record.
After the PAY RUNS and PAY HEADERS have been converted into the PAY PC AMOUNTS can be converted. The majority of the columns in the PAY PC AMOUNTS are used to link it to the PAY HEADER data. Since there are only seven columns in the interface table, this only leave two columns for the actual data. These are the PC Code and the amount. The PC Code must be a valid entry from the IPPC screen, while the amount is simply a number.
The simple table layout is very easy to load data into, however the number of records in this table will quite easily be the highest in the entire database. Since each pay header for each employee can have a significant number of PC Amount values, the table can have several million rows after all the data has been loaded.
The file layout of the INF_CV_PAYPCAMOUNTS is shown below:
| Name | Null? | Type | Notes |
|---|---|---|---|
| ENTITY_CODE | NOT NULL | VARCHAR2(16) | Used to link to the EMPLOYEE records on IEEI |
| PERSON_CODE | NOT NULL | VARCHAR2(16) | Used to link to the EMPLOYEE records on IEEI |
| EMPLOYMENT_TYPE | VARCHAR2(30) | Used to link to the EMPLOYEE records on IEEI | |
| HIRE_DATE | DATE | Used to link to the EMPLOYEE records on IEEI | |
| PAY_NUMBER | NOT NULL | NUMBER(5) | Used to link to the PAY HEADER records on IPPH |
| PC_CODE | NOT NULL | NUMBER(4) | Must be a valid PC Code on IPPC |
| AMOUNT | NOT NULL | NUMBER(18,6) | Value for PC listed above |
Notes #
Click to create a new notes pageScreen captures are meant to be indicative of the concept being presented and may not reflect the current screen design.
If you have any comments or questions please email the Wiki Editor
All content © High Line Corporation