This page (revision-6) was last changed on 26-Nov-2021 10:22 by JEscott

This page was created on 26-Nov-2021 10:22 by JEscott

Only authorized users are allowed to rename pages.

Only authorized users are allowed to delete pages.

Page revision history

Version Date Modified Size Author Changes ... Change note
6 26-Nov-2021 10:22 8 KB JEscott to previous
5 26-Nov-2021 10:22 7 KB JEscott to previous | to last
4 26-Nov-2021 10:22 7 KB JEscott to previous | to last
3 26-Nov-2021 10:22 7 KB JEscott to previous | to last
2 26-Nov-2021 10:22 7 KB JEscott to previous | to last
1 26-Nov-2021 10:22 7 KB JEscott to last

Page References

Incoming links Outgoing links
CONVERTING PAY HISTORY
CONVERTING PAY HISTORY

Version management

Difference between version and

At line 8 changed 4 lines
|[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
|[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]
At line 13 changed 3 lines
|[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.
|[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.
At line 17 changed 8 lines
|[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.
|[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].
At line 26 changed 3 lines
|[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'
|[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'
At line 49 changed one line
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.
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.
At line 51 changed one line
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.
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.
At line 53 changed one line
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.
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.
At line 55 changed one line
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 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.
At line 57 changed one line
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.
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.
At line 59 changed one line
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 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.
At line 63 changed 9 lines
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
||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
This page (revision-6) was last changed on 26-Nov-2021 10:22 by JEscottTop