|
SQL Inventory Columns Required
|   |
|
SQL Inventory Columns Required
| |
Note that you MUST define each DMS SQL column in your script; you cannot leave a column out even if you are not using the column. Each column must have a valid value of the type required. Null values are not permitted.
The SQL Server import columns and the Oracle importing columns are similar in layout, but the variable types are different so they have been separated into two sections:
SQL Server Inventory Columns Required
If you are not using a column, set that column value to 0 for integer or float, blank for VarChar, false for Boolean values.
Unless otherwise stated, the following are the range of values:
|
Big integer (INT64) |
An integer number. Internal IDs assigned by SQL can range from 1 to 9 quintillion (9,223,372,036,854,775,807). For quantities, number can be from 1 to 9,999,999,999. |
|
Boolean |
True or False |
|
Date |
A valid date. For blank dates use Convert(Date,'',0) to create the date of 01/01/1900 and the import will take any date less than the year 2000 and set the date to blank. You can also use Cast to cast a value to a date. |
|
Defaults |
Defaults for items are set within DMS using “Terms & Settings” under the menu category “Tables”. |
|
Float |
7 significant digits (i.e. 99999.99) |
|
Integer |
0 to 2 billion (2,147,483,647) |
|
Text |
A Varchar with a maximum length of 1 GB (about 1 billion characters). Practically speaking, a Text field can hold several paragraphs of text. For blank notes, use Convert(Text,TextToCast,0) as Notes You can also use Cast(TextToCast as Text) as Notes |
|
Varchar x |
Where x is the maximum number of alphanumeric characters in the Varchar |
During the import, if an Item No already exists in the inventory table, the Item No is updated with the new imported data. Duplicate InvAbbrev and duplicate ItemNo are allowed as long as the InvAbbrev plus the ItemNo together are unique.
|
|
Field |
Type |
Comment |
|
1. |
InvAbbrev |
Varchar (45) |
Required unique field. Inventory Abbrev of the inventory record to add or update. If not used, this field can be set to blank. |
|
2. |
ItemNo |
Varchar (45) |
Required unique field. Item no of the inventory record to add or update. |
|
3. |
AlternateItemNo |
Varchar (45) |
Optional. Alternate item no that may be used by a job. |
|
4. |
QtyOnHand |
Big integer |
0 to 999,999,999,999 |
|
5. |
Available |
Boolean |
True if the inventory item is available for use. False if the inventory item is not available. |
|
6. |
Notes |
Text |
Optional text to describe the inventory item and/or other notes. |
Oracle Inventory Columns Required
If you are not using a column, set that column value to 0 for integer or float, blank for VarChar2, or 0 (false) for Boolean values.
Unless otherwise stated, the following are the range of values:
|
Number(10) |
A large integer number; Actual format in Oracle is Number(10,0) |
|
Boolean |
Number(1); 0 for false or 1 for true; Actual format in Oracle is number(1,0) |
|
Date |
Valid date |
|
Float |
7 significant digits (i.e. 99999.99) |
|
Integer |
Integer values range from Number (2, 0) up to Number (5,0) |
|
VarChar2 (x) |
Where x is the maximum number of alphanumeric characters in the VarChar2 |
If you have information stored in a CLOB (Character Large OBect) in a column in the database, the following Oracle statement can be used to convert it into a VarChar2 for DMS:
dbms_lob.substr(NotesInCLOB, 2000, 1) as Notes
In the example above, NotesInCLOB is the column name in the Oracle database and Notes is the DMS required column that needs to be populated.
During the import, if an Item No already exists in the inventory table, the Item No is updated with the new imported data. Duplicate InvAbbrev and duplicate ItemNo are allowed as long as the InvAbbrev plus the ItemNo together are unique.
|
|
Field |
Type |
Comment |
|
1. |
InvAbbrev |
Varchar2(45) |
Required unique field. Inventory Abbrev of the inventory record to add or update. If not used, this field can be set to blank. |
|
2. |
ItemNo |
Varchar2(45) |
Required unique field. Item no of the inventory record to add or update. |
|
3. |
AlternateItemNo |
Varchar2(45) |
Optional. Alternate item no that may be used by a job. |
|
4. |
QtyOnHand |
Number (10) |
0 to 999,999,999,999 |
|
5. |
Available |
Boolean: Number(1) |
1 (true) if the inventory item is available for use. 0 (false) if the inventory item is not available. |
|
6. |
Notes |
Varchar2(2000) |
Optional text to describe the inventory item and/or other notes. |