PD Data Conversion

The PD Conversion (HRUTZZ04 and HRUTZZ05) process was created so that new clients can convert their Professional Development Data from their legacy system to BusinessPlus. It works based on a spreadsheet concept with column headings that tell the process what data is being converted. Information for each table is contained in a different file and is processed one at a time for maximum control.

The process will handle Fully-Qualified-Accounts. If you want to be able to obtain the GL and JL key and object codes using an FQA, do the following:

Put the fully qualified account number in the GL KEY column.
Have the GL Object column in the spreadsheet but have blank.
If using a Job Ledger, place the GR, KEY and Object columns in the spreadsheet, but have blank.

The process will handle all create-who, update-who, create-when and update-when fields in the tables. On insertion, create-who and update-who will be "ROLL" and create-when and update-when will be the date the process was activated. On update, update-who will be "UPDATE" and update-when will be the date the process was activated. Create-who and create-when will not be changed.

Spreadsheets

Spreadsheets allow the maximum flexibility to the generic process. The key to the spreadsheet is row 1 in which the name of the information contained in that field column exists. These names match the name of the column in the database table as well as the online help field descriptions on each screen.

The columns do not have to be in any specific order. The process will handle special features based on the column heading of that field (row 1).
The name of the spreadsheet is the name of the table the information is being inserted into (e.g., PDCLASSS.xls).
The columns are color coded as to the importance of the data. Red columns must be in the data file whether inserting or updating. Green columns must be there if inserting data. Gold columns are information needed / recommended by our consultants. White are just other optional information. All colors other than red are not necessarily needed in the file in order to process it. Ensure that each column appears in the upload file only once. If there are duplicate columns, the Insert will not happen, regardless if the tail sheet says it did.
There are two tabs per spreadsheet file. The first tab is for the data. The second tab has the descriptions of the field columns. The screen and tab to find this data is also contained in the field description tab as well as what type of data, length of text fields, how many digits follow the decimal point and any comments, defaults and recommendations.
The process will decipher the column heading row. It will handle dashes as well as lower and upper-case letters to adjust the field name to what it needs to be. If a field name specified is not one within the table, the process will display the error, but will still update/insert the data information for all other columns unless that one wrong column is a required one (red).
Dates – Eight different date formats are available for use. The only requirement is that all dates within a file are the same format. You will specify that format and the program will modify the date to fit the correct format that the database table will accept.
Numbers / Amounts – The program will handle all decimal options. It will calculate what type of number the data is, validate that it is a number, then format it to the correct form to be accepted by the database table. This is on a field by field basis. Only requirement: no dollar signs ($) are permitted.
The data file can be delimited three different ways: TAB, COMMA and PIPE. This is on a file by file basis. Note: If a data file has many number fields with commas in the numbers, TAB delimited works better than COMMA.
When updating information, if a blank data field is found, the table will clear that field data as well.
Some data fields have been kept off of the spreadsheet columns to minimize data clutter. These fields are usually standard data that is the same for all records. Based on this, if the process does not find that column field name, it will take the preset default. These fields are defined in the spreadsheet field description tab and are dependent on the database table. This default option can be over turned by manually inserting the column into the spreadsheet.
Sorting of a spreadsheet is usually based on the red columns and is specified on the field description tab. This is essential at times to find duplication of data or to be sure data is grouped properly for processing.

Tables

Tables are in update and insertion mode only. No purging of data will be done unless specified.

PDCOURSE

This routine will update the PD-COURSE-MSTR table based on the COURSE-ID.
This routine will allow Fully Qualified Accounts to be used.
GL and JL Key and Object Codes will be verified.
Instructor's ID (INST-PE-ID) will be verified in the PE Subsystem.
Defaults that can be handled by the process:
INST-PEDB-CD = "P"
COURSE-GR = User GL Code
COURSE-JLGR = User JL Code

PDCLASS

This routine will insert only into both the PD-CLASS-DTL and PD-ATTEND-DTL tables.
PD-CLASS-DTL: 1 record per COURSE-ID and CLASS-DTTM.
PD-ATTEND-DTL: 1 record based on the CLASS-UID which is handled by the process.
Participant's ID (PART-PE-ID) will be verified in the HR Subsystem
Defaults that can be handled by the process:
PART-PEDB-CD = "E"

PDREG

1 record per COURSE ID and PART-PE-ID.
Participant's ID (PART-PE-ID) will be verified in the HR Subsystem
Defaults that can be handled by the process:
PART-PEDB-CD = "E"

PDHIST

This routine will update the PD-HISTORY-DTL table based on the PART-PE-ID and COURSE-ID.
This routine will allow Fully Qualified Accounts to be used.
GL and JL Key and Object Codes will be verified.
Instructor's ID (INST-PE-ID) and PART ID (PART-PE-ID) will try to be verified.
Defaults that can be handled by the process:
INST-PEDB-CD = "P"
PART-PEDB-CD = "E"
COURSE-GR = User GL Code
COURSE-JLGR = User JL Code

PDAREA

This routine will update both the PD-AREA-MSTR and PD-AREA-DTL tables.
PD-AREA-MSTR: 1 record per AREA-CD.
PD-AREA-DTL: 1 record based on the AREA-CD and COURSE-ID.

PDREQ

This routine will update both the PD-REQ-MSTR and PD-REQ-DTL tables.
PD-REQ-MSTR: 1 record per REQ-CD.
PD-REQ-DTL: 1 record based on the REQ-CD and COURSE-ID.

PDMISC

This routine will update both the PD-MISC-MSTR and PD-COURSEMISC-DTL tables.
PD-MISC-MSTR: 1 record per MISC-TYPE and MISC-CODE.
PD-COURSEMISC-DTL: 1 record based on the MISC-TYPE, MISC-CODE and COURSE-ID.

PDCREDIT

This routine will update both the PD-CREDITTYPE-MSTR and PD-CREDIT-DTL tables.
PD-CREDITTYPE-MSTR: 1 record per COURSE-ID and CREDIT-TYPE.
PD-CREDIT-DTL: Multiple records based on COURSE-ID and CREDIT-TYPE. Data will be purged from the table and then reinserted.
Column PART-PE-ID will need to be present in order to insert into the PD-CREDIT-DTL table.
Participant's ID (PART-PE-ID) will be verified in the HR Subsystem.

PDCOST

This routine will update the PD-COST-MSTR and PD-COST-DTL tables.
PD-COST-MSTR; 1 record per COST-CD.
PD-COST-DTL: 1 record per COURSE-ID and COST-CD.
This routine will allow Fully Qualified Accounts to be used.
GL and JL Key and Object Codes will be verified.
Defaults that can be handled by the process:
COURSE-GR = User GL Code
COURSE-JLGR = User JL Code

PDEVAL

This routine will update the PD-EVAL-MSTR and PD-EVAL-DTL tables.
PD-EVAL-MSTR: 1 record per EVAL-ID.
PD-EVAL-DTL: multiple records based on EVAL-ID and COURSE-ID.

PDRESP

This routine will update the PD-RESP-DTL table.
1 record per EVAL-ID, COURSE-ID and PART-PE-ID.
Column PART-PE-ID will be validated in the HR Subsystem.
Defaults that can be handled by the process:
PART-PEDB-CD = "E"

PDPREREQ

1 or more records per Course ID.
Table will be purged of data and reinserted based on the Course ID.

PDCATG

1 record per REQ-CATG.

Common Code Options

The Common Code to run the process without the questions and with more options is PDRL/ROLLOVER. This code is required by the HRUTZZ04 utility that imports data into PD tables from flat files.

How to Run the Process

Before running the process, the tail sheet will need to be set up to be archived so that it can be looked at later for statistics, totals and error reports.

HRUTZZ04

Generic Common Code Launch - This option uses the Common Code to set up the process. HRUTZZ04 asks three basic questions: which table information to convert, what input file to use, and whether or not to run the conversion in trial mode (debug). All other information is taken from the PDRL/ROLLOVER common code. Locate the file you want to use for the conversion. The remote upload may not be able to handle extremely large input files. Check the tail sheet to verify how many records were read/updated/inserted. If the input file is too large, either split the file up into smaller files, or place the file on the App server in the /admin/data directory, which can handle larger file sizes. When placed on the App server, the file name can be no longer than eight characters, all letters in uppercase and have no file extension.

The default location of the input file question field is the /data/admin directory on the App server. If a file name is entered here instead of using the upload/browse feature, the system will look for that file on the App server when the utility is run.

For Trial (Debug) Mode: "YES" will have the process handle all the error checking, but not actually insert or update the table. "NO" inserts/updates the table.