FA Data Conversion

Overview

The FA Data Conversion process was created so that new clients can convert their Fixed Assets 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.

Fixed Asset ID is usually a system-generated number that is assigned in sequential order. To avoid previously owned assets to possibly conflict with this process, all Asset IDs will be the Tag Number (if possible) with an alpha character in the first column. This tag number can be no longer than 12 characters, counting the attached alpha character. This needs to be consistent with all tables and spreadsheets containing the field name FAID.

For example, in the spreadsheet for the FAID value, you would enter 100, which is the tag number. When you run the process, the additional character might be R for Rolled Assets. The resulting FAID or Asset ID value would be R100.

There are FQA columns in this subsystem. They are used similar to the FQA Account field in the HR Subsystem and the FQA Flags in the PO and AP Subsystems. If FQA ACCT column is present, it is used to obtain the GL/JL Key and Object Codes. The GL/JL Key and Object Code columns must exist in the spreadsheet.

Spreadsheets

Spreadsheets allow the maximum flexibility to the generic process. Row 1 contains column headers which name the information contained in that field column. The column headers match the name of the column in the database table as well as the online help field descriptions on each page.

Here are some features to the process, on a file-by-file basis:

The columns do not need to be in any specific order. The process handles 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 (for example, FAAPPO.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 or recommended by our consultants. White columns contain 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 does not process, even if the tail sheet says it did.
There are two tabs in each spreadsheet file. The first tab is for the data. The second tab has the descriptions of the field columns. The page 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 deciphers the column heading row. It handles 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 displays the error, but still updates or inserts the data information for all other columns unless the wrong column is required (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 must specify that format, and the program modifies the dates to fit the correct format that the database table accepts.
Numbers and Amounts: The program handles all decimal options. It calculates what type of number the data is, validates that it is a number, then formats it to the correct form to be accepted by the database table. This is on a field by field basis. No dollar signs ($) are permitted.
The data file can be delimited three different ways: tab, comma, or pipe. This is on a file by file basis. If a data file has many number fields with commas in the numbers, tab-delimited works better than comma-delimited.
When updating information, if a blank data field is found, the table clears 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 takes 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 overridden by manually inserting information in the spreadsheet column.
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 is done unless specified.

For all tables:

References to any ledgers or account information are verified by the process. If the values fail, that information is not inserted or updated. These types of errors do not stop the record from being processed.
PEID is verified in the PE subsystem. If the value fails, it is not inserted or updated. This error does not stop the record from being processed.
CREATE_WHO = ROLL
UPDATE_WHO = ROLL or UPDATE
CREATE_WHEN and UPDATE_WHEN = current date

01 – Coded Values (FAUPCD)

FA_CODES – Master Table

1 record per code based on FA_LEDGER, CODEID, and CODEVAL.

02 – Fixed Asset Info (FAUPAS)

FA_IDNT – Master Table

1 record per Asset based on FA_LEDGER and FAID.
FAID: refer to Overview
If the field TAG is left off the spreadsheet, the process puts the original FAID value into this field only at INSERT.
The Primary, Secondary, and Tertiary Class columns are validated in the FA-PCDEF table.
Codes ACQCD, COND, METHOD, STAT and TITLE are validated in the FA-CODES table.
Defaults:
TAGGED = Y

03 – Location (FAUPAS)

FA_SITE

1 record per Asset based on FA_LEDGER and FAID.
FAID: refer to Overview
If GLFQA-ACCT column is present, then it is used to obtain the GL Key and Object Codes. The KEY, OBJ, JLKEY and JLOBJ columns must be present in the spreadsheet.
Codes BLDG, DEPT, LCTN and RPID are validated in the FA-CODES table.

04 – Potential Assets (FAUPPA)

FA_APPO

Multiple records per Asset
Input file must be sorted by STATUS_CD, FAID.
This is an INSERT table only. If the FAID has a valid value, all table records are purged before the FAID is processed.
Assets can be existing ones or future purchases. STATUS_CD must be filled in since this ascertains what type of asset this is.
If the Asset exists, then the FA_LEDGER and FAID are required.
FAID: refer to Overview
If the Asset is a future purchase, then the FAID can be left blank.
If KEY column has the FQA Account Number in it and the OJB column in blank, then it is used to obtain the GL Key and Object Codes. The KEY, OBJ, JLKEY and JLOBJ columns must be present in the spreadsheet.
Codes STAT, DTYPE, METHOD, FREQ, and TYPE are validated in the FA-CODES table.
The system inserts new component records only when the FAID, Invoice Number, Invoice Amount, Item Amount, and Item Description combination do not match an existing component record. If all of these match an existing record, then the system updates the existing record.

05 – Depreciation (FAUPAS)

FA_DEPR: INSERT ONLY, Data is purged and resubmitted each time.

1 or more records per Asset based on FA_LEDGER and FAID.
FAID: refer to Overview
PCT is calculated and must add up to 100%. If there is only 1 record for an Asset, the PCT column can be left off the spreadsheet or be left blank and the program fills in 100%.
Should the PCT test fail, all records for the Asset fail and the old records are not changed.
If an Asset passes all conditions, all entries in this table are deleted and then re-inserted accordingly.

06 – Disposal (FAUPAS)

FA_DISP

1 record per Asset based on FA_LEDGER and FAID.
FAID: refer to Overview
Codes DTYPE and METHOD are validated in the FA-CODES table.

07 – FS Definition (FAUPFS)

FA_FSDEF – Master Table

1 record per fund source based on FA_LEDGER and FS.
If KEY column has the FQA Account Number in it and the OJB column is blank, then it is used to obtain the GL Key and Object Codes. The KEY, OBJ, JLKEY and JLOBJ columns must be present in the spreadsheet.
Code FS is validated in the FA-CODES table.

08 – Insurance (FAUPAS)

FA_INSR

1 record per Asset based on FA_LEDGER and FAID.
FAID: refer to Overview
Code CARRIER is validated in the FA-CODES table.
PEID is validated in the PE subsystem.
ADDRCD is validated in the Common Codes.
Vendor Address Information can be obtained from the PE subsystem by the process based on the PEID and ADDRCD. The ADDR1, ADDR2, CITY, STATE, ZIP, VENDOR, PHONE, and CONTACT fields can either be blank or left off the spreadsheet.

09 – Maintenance (FAUPAS)

FA_MI

1 record per Asset based on FA_LEDGER and FAID.
FAID: refer to Overview
If FAMI-ACCT column is present, then it is used to obtain the GL Key and Object Codes. The KEY, OBJ, JLKEY and JLOBJ columns must be present in the spreadsheet.

10 – Warranty (FAUPAS)

FA_WARR

1 record per asset based on FA_LEDGER, FAID, and WARRNO.
FAID: refer to Overview

11 – History Information

FA_HIST

1 record per Asset based on FA_LEDGER and FAID.
FAID: refer to Overview

12 – Funding (FAUPAS)

FA_FNDSRC: INSERT ONLY, Data is purged and resubmitted each time.

1 or more records per Asset based on FA_LEDGER and FAID.
FAID: refer to Overview
PCT is calculated and cannot be greater than 100%. If there is only 1 record for an Asset, the PCT column can be left off the spreadsheet or be left blank and the program fills in 100%.
Should the PCT test fail, all records for the Asset fail and the old records are not changed.
If an Asset passes all conditions, all entries in this table are deleted and then re-inserted accordingly.

13 – Lease (FAUPAS)

FA_LEASE

1 record per Asset based on FA_LEDGER and FAID.
FAID: refer to Overview

14 – Use Fee (FAUPAS)

FA_USEFEE

1 or more records per Asset based on FA_LEDGER and FAID.
FAID: refer to Overview
If the GR column is present, the GL ledger is validated, otherwise the default GL ledger is used.
If the JLGR column is present, the JL ledger is validated, otherwise the default JL ledger is used.
Columns GR, KEY, OJB, JLGR, JLKEY, and JLOBJ are used to validate the account number.

15 – Transaction Information

FA_TRANS

1 record per Asset based on FA_LEDGER, FAID, T-DATE and T-TYPE.
FAID: refer to Overview
If the GR column is present, the GL ledger is validated, otherwise the default GL ledger is used.
If the JLGR column is present, the JL ledger is validated, otherwise the default JL ledger is used.
If FATR-ACCT column is present, then it is used to obtain the GL / JL Key and Object Codes. The KEY, OBJ, JLKEY and JLOBJ columns must be present in the spreadsheet.
Columns GR, KEY, OJB, JLGR, JLKEY, and JLOBJ are used to validate the account number.
Columns GR, NEWKEY, NEWOBJ, JLGR, NEWJLKEY and NEWJLOBJ are used to validate the account number.

16 – Primary Class Defs (FAUPPC)

FA_PCDEF – Master Table

1 record per class based on FA_LEDGER, PC, SC and TC.
SC and TC can be left off of spreadsheet. If so, then they are set to blanks.
If KEY column has the FQA Account Number in it and the OJB column is blank, then it is used to obtain the GL Key and Object Codes. The KEY, OBJ, JLKEY and JLOBJ columns must be present in the spreadsheet.
Codes FREQ, TYPE, PC, SC and TC are validated in the FA-CODES table.

Common Code Options

The common code to run the process without the questions and with more options is FARL/ROLLOVER. This code is required by the FAUTZZ utility that imports data into FA tables from flat files.

How to Run the Process

Before you run the process, the tail sheet needs to be set up to be archived so that it can be reviewed later for statistics, totals and error reports.

FAUTZZ

There are two options to running FA Conversion. The options are listed in the menu FAUTZZ.

FAUTZZ01: Generic Common Code Launch – This option uses the Common Code to set up the process. It 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 FARL/ROLLOVERcommon code.
- Locate the file you want to use for the conversion. After the file is selected, the question field displays the path to the file and is greyed out.
- 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, and inserted. If the input file is too large, either split the file 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 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 looks for that file on the App server when the utility is run.
- For Trial (Debug) Mode: YES runs the process to handle all the error checking, but does not actually insert or update the tables. NO inserts and updates the tables.
- Submit the file first in Trial Mode to evaluate for potential errors that need to be resolved. Inspect the tail sheet for the count of records read and any records that may have failed. If the trial tail sheet looks correct, clear the Trial Mode box (change to N) and then submit again. After the job completes, open the targeted mask to review the inserted records.
FAUTZZ02: Custom Conversion – This option is used if there was a custom program written and is set by the programmer.