|
SQL Job Updates Columns Required
|   |
|
SQL Job Updates 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 Job Updates 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 |
The following columns are required for the SQL Server job updates:
|
|
Field |
Type |
Comment |
|
1. |
OrderNumber |
Varchar 25 |
Required unique field. Order number of the job to update. |
|
2. |
OpIdent |
Varchar 20 |
Blank or required if Operation Sequence is not given. The operation ident of the operation to update. |
|
3. |
OpSeq |
Integer |
0 or required if OpIdent is not given. The operation sequence number of the operation to update. |
|
4. |
UpdateDate |
Date |
The date to apply to the actual start and completion dates of the operation. |
|
5. |
UpdateShift |
Integer |
Shift 1, 2, or 3 to apply to the actual start and completion dates of the operation. |
|
6. |
Resource |
Varchar 20 |
Actual resource name that performed the operation. |
|
7. |
EmployeeNumber |
Varchar 20 |
Blank or a valid employee number must be given. |
|
8. |
SetupTime |
Float |
0 or the setup time to add to the actual setup time. |
|
9. |
Runtime |
Float |
0 or the runtime to add to the actual runtime. |
|
10. |
ReportedQty |
Big Integer |
The quantity completed to add to the total quantity completed for the operation. |
|
11. |
ReportedScrap |
Big Integer |
The scrap to add to the total scrap for the operation. |
|
12. |
OperationStatus |
Varchar 5 |
Blank or a valid operation status. (See Lookup Tables for Importing.) |
|
13. |
TypeOfWork |
Varchar 20 |
Blank for default or a valid type of work. (See Lookup Tables for Importing.) |
|
14. |
PercentComplete |
Integer |
0 for a regular operation, or 1 to 100 if reporting on percent complete. |
|
15. |
RecalculateRuntime |
Boolean |
False or if reporting percent complete True if the runtime is to be recalculated based on the percent complete reported. |
|
18. |
StatusReason |
Varchar 20 |
Blank or a valid status reason. (See Lookup Tables for Importing.) |
|
17. |
Backflush |
Boolean |
False or True if the system is to “back flush” and update previous operations. |
|
18. |
BackflushActToPlan |
Boolean |
If back flush is True, set this value to True if you want back flushed operations to have their actual times and quantities set to their planned time and quantities. |
|
19. |
BackflushStatus |
Varchar 5 |
Blank or a valid operation status to set back flushed operation to. (See Lookup Tables for Importing.) |
|
20. |
Notes |
Memo |
Blank or text to describe the update and/or other update notes. |
Oracle Job Updates 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, 1000, 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.
The following columns are required for the Oracle job updates:
|
|
Field |
Type |
Comment |
|
|
OrderNumber |
VarChar2(25) |
Required unique field. Order number of the job to update. |
|
|
OpIdent |
VarChar2(20) |
Blank or required if Operation Sequence is not given. The operation ident of the operation to update. |
|
|
OpSeq |
Number(5) |
0 or required if OpIdent is not given. The operation sequence number of the operation to update. |
|
|
UpdateDate |
Date |
The date to apply to the actual start and completion dates of the operation. |
|
|
UpdateShift |
Number(2) |
Shift 1, 2, or 3 to apply to the actual start and completion dates of the operation. |
|
|
ResourceName |
VarChar2(20) |
Actual resource name that performed the operation. |
|
|
EmployeeNumber |
VarChar2(20) |
Blank or a valid employee number must be given. |
|
|
SetupTime |
Float |
0 or the setup time to add to the actual setup time. |
|
|
Runtime |
Float |
0 or the runtime to add to the actual runtime. |
|
|
ReportedQty |
Number(10) |
The quantity completed to add to the total quantity completed for the operation. |
|
|
ReportedScrap |
Number(10) |
The scrap to add to the total scrap for the operation. |
|
|
OperationStatus |
VarChar2(5) |
Blank or a valid operation status. |
|
|
TypeOfWork |
VarChar2(20) |
Blank for default or a valid type of work. |
|
|
PercentComplete |
Number(5) |
0 for a regular operation, or 1 to 100 if reporting on percent complete. |
|
|
RecalculateRuntime |
Number(1) |
0 for false, or if reporting percent complete 1 for true if the runtime is to be recalculated based on the percent complete reported. |
|
|
StatusReason |
VarChar2(20) |
Blank or a valid status reason. |
|
|
Backflush |
Number(1) |
0 for false or 1 for true if the system is to “back flush” and update previous operations. |
|
|
BackflushActToPlan |
Number(1) |
If back flush is 1 for true, set this value to 1 for true if you want back flushed operations to have their actual times and quantities set to their planned time and quantities. Otherwise set this value to 0 for false. |
|
|
BackflushStatus |
VarChar2(5) |
Blank or a valid operation status to set back flushed operation to. |
|
|
Notes |
VarChar2 (2000) |
Blank or text to describe the update and/or other update notes. |