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 IEEM PERSON_CODE NOT NULL VARCHAR2(16) Used to link to the EMPLOYEE records on IEEM EMPLOYMENT_TYPE VARCHAR2(30) Used to link to the EMPLOYEE records on IEEM HIRE_DATE DATE Used to link to the EMPLOYEE records on IEEM 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 Personality 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 PAYHEADERS 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, PAYHEADERS 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 PAYHEADERS 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 PAYHEADER records require a valid PAYRUN entry to be generated in order for the Personality application to treat them correctly. Since a PAYRUN record can only contain PAYHEADER 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 PAYHEADER 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 PAYRUNS and PAYHEADERS can be converted into the Personality application. The PAYRUN records will have to be converted first so that the PAYHEADERS will be able to validate the PAYRUN number that was added to the record.
After the PAYRUNS and PAYHEADERS have been converted into Personality the PAYPCAMOUNTS can be converted. The majority of the columns in the PAYPCAMOUNTS are used to link it to the PAYHEADER 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 IEEM PERSON_CODE NOT NULL VARCHAR2(16) Used to link to the EMPLOYEE records on IEEM EMPLOYMENT_TYPE VARCHAR2(30) Used to link to the EMPLOYEE records on IEEM HIRE_DATE DATE Used to link to the EMPLOYEE records on IEEM PAY_NUMBER NOT NULL NUMBER(5) Used to link to the PAYHEADER 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
