Costpoint Online Help

Input File/Table Hard Validations (GENL_UDEF_Table)

When importing asset records, Costpoint validates the imported information and stores it in different tables.

Four separate tables store Asset Master Data, as follows:

Table Name Table Description
ASSET This table holds data for the majority of Asset Master fields.
ASSET_OTHER_INFO This table holds “government” user-defined field information as well as other asset information (as an overflow from the ASSET table) for Asset Master records.
ASSET_OTH_BK_DEPR This table holds optional book information for Books 2 to 10, as applicable, for Asset Master records.
ASSET_BOOKS_DISP This table holds depreciation adjustment information for all books, as applicable, related to a disposal.

An additional Costpoint table (GENL_UDEF) stores specialized user-defined field data for an Asset Master record as well as for a number of other screens in other modules throughout Costpoint. Labels are established for these fields in the UDEF_LBL table. In addition, if you have added validated text for use with this functionality, the system stores this data in the UDEF_VALID_VALUES table.

The system tracks which user-defined data belongs with the Fixed Assets (FA) module by assigning the table ID (S_TABLE_ID) as “FA” in the GENL_UDEF and UDEF_LBL tables and assigning a unique table key (UDEF_LBL_KEY) for linkage purposes. The system links FA validated data with the appropriate FA user-defined fields and their labels (from the UDEF_LBL table) by using the same table ID (S_TABLE_ID) and table key (UDEF_LBL_KEY).

The fields below belong to the GENL_UDEF table, which holds specialized user-defined data for Asset Master records.

Note: You must also populate the UDEF_LBL table appropriately if you have user-defined data in the provider database. You will also need to populate the UDEF_VALID VALUES table if you have validated text associated with your user-defined fields.

You cannot add data to this table without first establishing the Asset Master record and thereby automatically creating data in the ASSET table. You must also establish the user-defined labels and any applicable validated text data in the destination database in the Manage Asset Master User-Defined Labels screen prior to running this preprocessor. Setup includes the label type, the label name, the sequence number that controls the order of the label display, a short description for the label, and whether data entry for the label should be required. If none of the labels has been designated as required in the provider database, user-defined data entry in the “Receiver” on this screen will be optional. You can also designate during setup whether the label should accommodate validated text or access the designated field from the selected Costpoint validation table. If you have selected the validated text option for the label, you must also establish the data to be validated against.

Three types of optional user-defined information must be user-specified in these user-defined fields:

  • D: Date
  • N: Numeric
  • T: Text

The system automatically stores this information in the Data Type field (S_DATA_TYPE) in the UDEF_LBL table.

You can use this special-purpose user-defined data functionality to record additional information that may be specific to your company for which there are no system-defined standard fields with the desired terminology. The primary advantage in using validated text data lies in the consistency of the entry format and the subsequent ability to print reports that use this consistent data format. When setting up validated text, you can define your own valid entries or you can select a field from an existing Costpoint table as the source for data validation.

Note: “Government” (or for any other purpose you choose) user-defined fields are also available for use with an Asset Master record. These are optional, non-validated text fields, and the system stores this data, as applicable, in the ASSET_OTHER_INFO table. You can use any combination of user-defined functionality in an Asset Master record.

Contents

Field Description
Input Row

This field (INPUT_ROW) is not a field in the GENL UDEF table, but must be included in the data to be uploaded into the GENL_UDEF table.

This field is required, it must be numeric, and it can hold up to 10 integers.

Its purpose is to be an identifier on the error report for records that fail validations.

Possible Error Message

  • ERROR MESSAGE: Input Row is not numeric.
Import Status

The status field is only used for Table data. This field does not exist in the input file format.

This field (STATUS) is not a field in the GENL UDEF table, but must be included in the data to be uploaded into the GENL UDEF table.

This field is required and it can only hold one of these values:

  • I: Records have been successfully uploaded into the Fixed Assets and GENL_UDEF tables
  • E: Record previously uploaded and failed validations
  • U: Records that have not yet been uploaded or that have been validated and passed validations such that the records may be imported but have not yet been imported
Record Type

Record Type is not a field in the GENL UDEF table, but must be included in the data to be uploaded into the different tables.

This field is required and it can only hold one of the following alphabetic characters:

  • H (Header table)
  • A (Asset table)
  • B (Other Books table)
  • O (Other Info/Books Disposal tables)  
  • U (User Defined table)

The purpose of the record type designation is to indicate whether the row is a header row or whether it should be linked with the ASSET, ASSET_OTH_BK_DEPR, or other tables that store Asset Master data (such as such as ASSET_OTHER_INFO or ASSET_BOOKS_DISP or GENL_UDEF).

Possible Error Message

  • ERROR MESSAGE: Row Type exceeds 1 character.
Asset ID

The data in this field must exactly match the Asset ID data entered for the same Asset Master record in the ASSET table.

This field (which is normally stored as the Asset ID in the ASSET, ASSET_OTHER_INFO, ASSET_OTH_BK_DEPR, and ASSET_BOOKS_DISP tables), is stored in the GENL_UDEF table in the GENL_ID field.

The Asset ID (GENL_ID), together with the Item No (GENL1_ID), supplies the unique two-part key identifier for each Asset Master record in the GENL_UDEF table.

Although this field in an Asset Master record will accept up to 10 alphanumeric characters, you must have previously designated the size of this field in the Length of Asset Number field on the Configure Fixed Assets Settings screen (FA_SETTINGS.ASSET_ID_LEN_NO). The designated length must be no less than 3 characters.

The length of a numeric Asset ID must equal the length designated on the Configure Fixed Assets Settings screen and cannot exceed that number. The length of an alpha-numeric Asset ID must equal or exceed 3 characters and be equal to or less than the length indicated on the Configure Fixed Assets Settings screen. The Asset ID/Item No combination must not already exist in the “Receiver” database, and it must be unique within the ASSET BOOKS DISP table.

Possible Error Messages

  • ERROR MESSAGE: This Numeric Asset ID length does not equal the Length of Asset Number in Fixed Assets Settings.
  • ERROR MESSAGE: This Asset ID length exceeds the Length of Asset Number in Fixed Assets Settings.
  • ERROR MESSAGE: This Asset ID/Item No combination is already being used. Please select another combination.
  • ERROR MESSAGE: This Asset ID/Item No combination is a duplicate of another record in this input file.
Item No

The data in this field must exactly match the Item No data entered for the same Asset Master record in the ASSET table.

This field (which is normally stored as the Item No in the ASSET, ASSET_OTHER_INFO, ASSET_OTH_BK_DEPR, and ASSET_BOOKS_DISP tables), is stored in the GENL_UDEF table in the GENL1_ID field.

The Asset ID (GENL_ID), together with the Item No (GENL1_ID), supplies the unique two-part key identifier for each Asset Master record in the GENL_UDEF table.

Each asset number must have a unique item number that is numeric, greater than zero, and does not exceed “99999.” The Asset ID/Item No combination must not already exist in the destination database when the record is imported. However, for the GENL UDEF table alone, there may be multiple records having the same asset/item number combination. Any asset record having multiple GENL_UDEF rows would have a different User Defined Field Label value for each of its GENL UDEF records.

Possible Error Messages

  • ERROR MESSAGE: Item No is null and auto-numbering is not activated.
  • ERROR MESSAGE: Item No must have a value when Asset ID has a value.
  • ERROR MESSAGE: This Asset ID/Item No combination is already being used. Please select another combination.
  • ERROR MESSAGE: This Asset ID/Item No combination is a duplicate of another record in this input file.
Date User-Defined Fields (UDEF_DT)

If the label has been set up as type D (Date) on the Manage Asset Master User-Defined Labels screen, enter the user-defined data in date format (YYYYMMDD) in the UDEF_TXT field.

If you have specified on the Manage Asset Master User-Defined Labels screen that data for this field is required for this user-defined field, you must enter a date before you can save the Asset Master record. The Validated Text field will automatically display N for a D type label in the Asset Master user-defined field entry screen.

Numeric User-Defined Fields (UDEF_AMT)

If the label has been set up as type N (Numeric) on the Manage Asset Master User-Defined Labels screen, enter the user-defined data in numeric format. You can enter up to 12 numeric characters and two decimal characters (999,999,999,999.99) in the UDEF_AMT field.

If you have specified on the Manage Asset Master User-Defined Labels screen that data for this field is required for this user-defined field, you must enter numeric data before you can save the Asset Master record.

(Although only a single numeric character entered in this field fulfills the system requirement for data entry, you will find it more beneficial to supply meaningful data because your entry will display in the Asset Master record as well as in reports that use this field.) The Validated Text field will automatically display N for an N type label in the Asset Master user-defined field entry screen.

Text User-Defined Fields (UDEF_TXT)

If the label has been set up as type T (Text) on the Manage Asset Master User-Defined Labels screen and the label does not require validated data, enter up to 30 alphanumeric characters of data in text format for a new asset record.

If the Validated Text field for this label displays N and the Costpoint Validation Field is empty AND the Required column displays N for this label, you can enter text data of your choice that is not system-validated.

If the Required field for this label displays Y, you must enter text for this field before you can save the Asset Master record.

(Although only a single character entered in this field fulfills the system requirement for data entry, you will find it more beneficial to supply meaningful data because your entry will display in the Asset Master record and in reports that use this field.)

Validated Text User-Defined Fields (UDEF_ID)

The user-defined text label may have been set up to accept validated data only. In this circumstance, one of two sources for validated text was already designated during setup on the Manage Asset Master User-Defined Labels screen of the “Giver” database, and should be manually set up to exactly match on the Manage Asset Master User-Defined Labels screen of the “Receiver” database.

  • If the Validated Text field for this label displays Y on the Manage Asset Master User Defined Labels screen, you must add the same validated data in the UDEF_VALID_VALUES table (via the Manage Asset Master User Defined Labels screen) in the “Receiver” database to match this data that already exists in the “Giver” database. This field is stored in the table as Y (Yes) or N (No) in the VALIDATE_FL field of the UDEF_LBL table.

    You will need to supply both a validated text name of up to 20 alphanumeric characters (UDEF_ID) and a description of up to 30 alphanumeric characters (UDEF_DESC) in the UDEF_VALID_VALUES table to satisfy system requirements for validated text.

  • If a Costpoint field name displays in the Costpoint Validation Field, you must add the same Costpoint table data field (via the Manage Asset Master User-Defined Labels screen) in the “Receiver” database to match this data that already exists in the “Giver” database.

    The Costpoint validation field that you select is stored in the UDEF_LBL table in the Costpoint Validation Field (S_CASE_LBL field, which can hold up to 15 system-designated alphanumeric characters.  You can must select from system-provided data only.

Label (UDEF_LBL)

If there is user-defined data in the provider database to be processed to the destination database, you must first set up labels on the Manage Asset Master User-Defined Labels screen of the “Giver” database to exactly match the labels on the Manage Asset Master User-Defined Labels screen of the “Receiver” database.

Template Numbers (along with any applicable revision numbers) are permitted in the input file/table. If the Template No/Rev No is populated on an input file/table record, the asset record will be populated with the values associated with the Template No/Rev No as they exist in the Receiving database, as follows:

  • If a field is blank in the input file/table for which there is a corresponding template data field, the system will use the Template No (if it exists) to determine the data for that field.
  • In the circumstance that data exists in the input file/table, even if there is a corresponding template data field, the system will bypass any reference to the Template No.