!!!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|UNIT_CODE] from the employees prime assignment record, as of the Pay Issue date.
|[GROUP_CODE]      |  	NOT NULL| 	VARCHAR2(16)|	[Group code|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 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|PAYROLL_CODE] and [pay calendar code|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|PC_CODE] and the [amount|AMOUNT]. The [PC Code|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|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|PC_CODE] on [IPPC]
|[AMOUNT]          |	NOT NULL |	NUMBER(18,6)|	Value for PC listed above