!!!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|DSP_ID_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|DSP_ID_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|DTX_ID_HOME]| |VARCHAR2(16)| GEO Code from resident tax jurisdiction on IEPI |[GEO_CODE_WORK|DTX_ID_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