|
SQL Server Job Import Columns Required
|   |
|
SQL Server Job Import 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. Unless specifically stated in the Comment section in the tables below, you cannot provide a Null value to a column. Each column must have a valid value of the type required. Some dates do allow a null value (such as Baseline Start Date) and they are defined below.
Unless otherwise stated for SQL Server, 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 column definitions are required for the SQL import:
|
|
Field |
Type |
Comment |
|
1. |
OrderNumber |
Varchar 25 |
|
|
2. |
PartNumber |
Varchar 45 |
|
|
3. |
JobIdentification |
Varchar 45 |
|
|
4. |
JobState |
Varchar 20 |
Blank to select the default or valid existing Job Sate required. (See Lookup Tables for Importing.) |
|
5. |
TemplateOrderNumber |
Varchar 25 |
Blank or the existing template order number to copy if adding a new order. |
|
6. |
Priority |
Integer |
Blank to select the default or 1 to 99 |
|
7. |
OrderDate |
Date |
|
|
8. |
ReleaseDate |
Date |
|
|
9. |
ReleaseShift |
Integer |
1, 2 or 3 |
|
10. |
DueDate |
Date |
|
|
11. |
DueShift |
Integer |
1, 2 or 3 |
|
12. |
PromiseDate |
Date |
|
|
13. |
QtyOrdered |
Big integer |
|
|
14. |
ScrapFactor |
Float |
From 0 to 100% |
|
15. |
SequenceType |
Varchar 26 |
Blank for default or a valid sequence type. (See Lookup Tables for Importing.) |
|
16. |
LotSize |
Big integer |
0 for default or a lot size as required by the sequence type. |
|
17. |
Overlap |
Float |
0 for default or a lot size as required by the sequence type. If the sequence type has optional queue time, this field is used to give the queue time between operations. |
|
18. |
ScheduleMethod |
Varchar 20 |
Blank for default or a valid schedule method. (See Lookup Tables for Importing.) |
|
19. |
MidPointOpSeq |
Integer |
0 or the mid point operation sequence number to use if required by schedule method |
|
20. |
TypeOfWork |
Varchar 20 |
Blank for default or a valid type of work. (See Lookup Tables for Importing.) |
|
21. |
JobType |
Varchar 1 |
Blank for default or P for production or F for fabrication |
|
22. |
BaselineStartDate |
Date |
Blank or a valid date. |
|
23. |
BaselineStartShift |
Integer |
1, 2, or 3 |
|
24. |
BaselineEndDate |
Date |
Blank or a valid date. |
|
25. |
BaselineEndShift |
Integer |
1, 2, or 3 |
|
26. |
PlannerEmplNo |
Varchar 20 |
Blank or a valid employee number must be given. |
|
27. |
Notes |
Memo |
Blank or text to describe the job and/or other job notes. |
Job SQL Script created by Build SQL Button
SELECT
as OrderNumber,
as PartNumber,
as JobIdentification,
as JobState,
as TemplateOrderNumber,
as Priority,
as OrderDate,
as ReleaseDate,
as ReleaseShift,
as DueDate,
as DueShift,
as PromiseDate,
as QtyOrdered,
as ScrapFactor,
as SequenceType,
as LotSize,
as Overlap,
as ScheduleMethod,
as MidPointOpSeq,
as TypeOfWork,
as JobType,
as BaselineStartDate,
as BaselineStartShift,
as BaselineEndDate,
as BaselineEndShift,
as PlannerEmplNo,
as Notes
FROM
WHERE 1=1
Order By OrderNumber
Copy a Template
The TemplateOrderNumber field can be used to copy an existing template order to a new order. The copy template feature copies the entire template order number; job user fields, operations, and operations user fields. It does not include Assembly information. To copy an existing job or template, give the order number of exiting job in the TemplateOrderNumber column. Otherwise, this column should be blank.
Job User Field Columns Required
Before data can be imported into a job user, the user field must first be defined in DMS. Depending on how the user field has been defined, the UF Text import would be one of the following text fields:
Note that the date format selection for importing user field dates can be changed. Valid selections are windows default, YYYY/MM/DD, DD/MM/YYYY, or MM/DD/YYYY. The date format is set on the Other SQL tab of Job Import SQL Scripts.
The MapNo is defined by the user in DMS to uniquely identify a user field. For example, a job user field may have been defined as “Product Color”. In order to import data into “Product Color” the user field must be assigned a unique map reference number which is an integer number. So if “Product Color” was given a map reference number of 7, then the import uses 7 to load the data into “Product Color”.
|
|
Field |
Type |
Comment |
|
1. |
OrderNumber |
Varchar 25 |
The job to which this user field belongs. |
|
2. |
MapNo |
Integer |
Defined in DMS to uniquely identify a user field. |
|
3. |
UFText |
Memo |
Text field where one of 10 formats as defined above can be used |
Job User Field SQL Script created by Build SQL Button
SELECT
as OrderNumber,
as MapNo,
as UFText
FROM
WHERE 1=1
Order By OrderNumber
Job material updates the material information on the job header.
|
|
Field |
Type |
Comment |
|
1. |
OrderNumber |
Varchar 25 |
The job to which this material information belongs. |
|
2. |
MaterialAvailable |
Boolean |
True if the material for the job is available, otherwise False |
|
3. |
DaysToDelay |
Integer |
Days to delay the job from the start date of the schedule run if material is not available. 0 to 999 days. |
|
4. |
ExpectedDelivery |
Date |
Valid date or blank |
|
5. |
DateReceived |
Date |
Valid date or blank |
|
6. |
MaterialPlannerEmplNo |
Varchar 20 |
Blank or a valid employee number must be given. |
Job Material SQL Script created by Build SQL Button
SELECT
as OrderNumber,
as MaterialAvailable,
as DaysToDelay,
as ExpectedDelivery,
as DateReceived,
as MaterialPlannerEmplNo
FROM
WHERE 1=1
Order By OrderNumber
The Job Multi Template SQL script only needs to be provided if you are adding multiple templates to a job in order to use the Job Multi Template scheduling feature. The columns required are as follows:
|
|
Field |
Type |
Comment |
|
1. |
Order Number |
VarChar 25 |
The job to which the job multi template information belongs. |
|
2. |
Template Order Number |
VarChar 25 |
The template order number for the job. The template must already exist in the DMS database. |
|
3. |
Sequence |
Integer |
The sequence order for the template. For example, the first template to try would be sequence 1, the second 2, etc. |
|
4. |
Allowable Days Late |
Integer |
The allowable days late for this template before another template will be tried by the scheduler. |
|
5. |
Selected |
Boolean |
If selected by the scheduler, this field is true. Otherwise, this field is false. |
|
6. |
Locked |
Boolean |
If locked by the user, this field is true. Otherwise, this field is false. |
Job Multi Template SQL Script created by Build SQL Button
SELECT
as OrderNumber,
as TemplateOrderNumber,
as Sequence,
as AllowableDaysLate,
as Selected,
as Locked
FROM
WHERE 1=1
Order By OrderNumber
It is recommended that you also order the imported records by the Sequence.
One or more operations can be defined for a job. During an import, the Original Resource is set to the Resource Name.
|
|
Field |
Type |
Comment |
|
1. |
OrderNumber |
Varchar 25 |
The parent job to which this operation belongs. |
|
2. |
OpSeq |
Integer |
The operation sequence number within the job. 1 to 400. |
|
3. |
OpIdent |
Varchar 15 |
Required for regular operations, but not required for schedule commands in which case the ident must be blank. Unique to the job. |
|
4. |
Description |
VarChar 40 |
Blank or text to describe the operation. |
|
5. |
RouteCommand |
Varchar 26 |
Blank or a valid command. (See Lookup Tables for Importing.) |
|
6. |
RouteParameter |
Varchar 20 |
Blank or a valid parameter to the route command (See Lookup Tables for Importing.) |
|
7. |
Pool |
Varchar 20 |
Blank or a valid existing pool name. |
|
8. |
ResourceName |
Varchar 60 |
Required for active operations. May be blank if a pool name has been given. |
|
9. |
SetupTime |
Float |
From 0 to 9999.99 |
|
10. |
Runtime |
Float |
From 0 to 9999.99. If given, it is either the time per piece, pieces per hour, or total hours of the operation depending on the runtime method. |
|
11. |
RuntimeMethod |
Varchar 1 |
Blank for default or T for time per piece, P for parts per hour, H for hours, or L for time per lot. |
|
12. |
TypeOfWork |
Varchar 20 |
Blank for default or a valid type of work. (See Lookup Tables for Importing.) |
|
13. |
QtyToBuild |
Big integer |
0 or must be given if “Quantity set manually” is true. |
|
14. |
QtyScrapped |
Big integer |
0 or the total amount scrapped by this operation. |
|
15. |
QtyCompleted |
Big integer |
0 or the total quantity completed by this operation. |
|
16. |
QtyMultiplier |
Float |
0 or 1 for a regular operation, or a value you want to multiple the qty by. |
|
17. |
SetQtyManually |
Boolean |
False if the quantity is the same as the job quantity. True if the quantity to build for this operation will be different than the job qty. |
|
18. |
MinResources |
Integer |
0 to 99. If greater than 0, minimum resources must be less than maximum resources. |
|
19. |
MaxResources |
Integer |
0 to 99. If greater than 0, minimum resources must be less than maximum resources. |
|
20. |
MinMaxSetupTime |
Float |
0 or if a min/max operation command, the setup time for each min/max operation. |
|
21. |
MinMaxRuntime |
Float |
0 or if a min/max operation command, the runtime for the min/max operation based on the runtime method. |
|
22. |
MinMaxActSetup |
Float |
0 or if a min/max operation command, the total actual setup time for the min/max operations. |
|
23. |
MinMaxActRuntime |
Float |
0 or if a min/max operation command, the total actual runtime for the min/max operations. |
|
24. |
MinMaxQtyToBuild |
Big integer |
0 or if a min/max operation command, the total quantity to build for the min/max operations. |
|
25. |
MinMaxQtyScrapped |
Big integer |
0 or if a min/max operation command, the total quantity scrapped for the min/max operations. |
|
26. |
MinMaxQtyComplete |
Big integer |
0 or if a min/max operation command, the total quantity completed for the min/max operations. |
|
27. |
AutoReporting |
Boolean |
False for a regular operation. True if the operation will be auto reporting. |
|
28. |
StartOpWhenPrevComplete |
Boolean |
False for a regular operation. True if the operation is to be set to in process when the previous operation is completed. |
|
29. |
VariableResource |
Boolean |
False for a regular operation. True if a AND command block and each AND operation can have variable start and end times. |
|
30. |
StartEndSameDay |
Boolean |
False for a regular operation. True if the operation must start and end on the same day. |
|
31. |
StartEndSameShift |
Boolean |
False for a regular operation. True if the operation must start and end on the same shift. |
|
32. |
NoCapacityInterruptions |
Boolean |
False for a regular operation. True if the operation cannot have any capacity interruptions. |
|
33. |
NoInterruptions |
Boolean |
False for a regular operation. True if the operation cannot have any interruptions (i.e. holidays, non working shifts, etc.) |
|
34. |
NoInterruptDays |
Integer |
Optional parameter for NoInterruptions to specify the permitted length of the interrupt. If not used, set to 0. |
|
35. |
LockOnDates |
Boolean |
False for a regular operation. True if the operation must be scheduled on the given start and end dates. |
|
36. |
SkipShifts |
Boolean |
False for a regular operation, True if the operation can skip a shift if capacity is not available on that shift |
|
37. |
SkipShiftsNo |
Varchar 3 |
If skip shifts is true, 1 to skip shift 1, 2 to skip shift 2, 3 to skip shift 3, 1&2 to skip shifts 1 and 2, 1&3 to skip shifts 1 and 3, 2&3 to skip shifts 2 and 3, or ALL to skip all shifts. If blank and skip shifts is true, the default is All. |
|
38. |
InfiniteOp |
Boolean |
False for a finite loaded operation. True if the operation's load will be infinite. |
|
39. |
Certificate |
Boolean |
False if the operation does not require a Security Certificate. True if the operation requires a resource with a security certificate. |
|
40. |
NonContigOp |
Boolean |
False for a regular operation. True if the operation can be non-contiguous. |
|
41. |
NonContigMinTime |
Float |
0 or if a non-contiguous operation, then the time given here is the minimum runtime required. |
|
42. |
PlannedDays |
Integer |
0 for regular operations. Number of elapsed or planned days for the operation. |
|
43. |
ActSetupTime |
Float |
0, or the actual setup for this operation. |
|
44. |
ActRunTime |
Float |
0, or the total actual runtime for this operation. |
|
45. |
ActStartDate |
Date |
Blank or the actual start date for the operation. |
|
46. |
ActStartShift |
Integer |
1, 2, or 3 |
|
47. |
ActEndDate |
Date |
Blank or the actual end date for the operation. |
|
48. |
ActEndShift |
Integer |
1, 2, or 3 |
|
49. |
OpStatus |
Varchar 5 |
Blank or a valid operation status (See Lookup Tables for Importing.) |
|
50. |
BaselineStartDate |
Date |
Blank or date for reporting purposes. |
|
51. |
BaselineStartShift |
Integer |
1, 2 or 3 |
|
52. |
BaselineEndDate |
Date |
Blank or date for reporting purposes. |
|
53. |
BaselineEndShift |
Integer |
1, 2 or 3 |
|
54. |
WaitTime |
Float |
Wait time to be added to the end of the operation runtime, or 0. |
|
55. |
MoveTime |
Float |
Move time to be added to the end of the operation runtime, or 0. |
|
56. |
WTUseCapacity |
Boolean |
Future use. Set to false. |
|
57. |
Notes |
Memo |
Blank or text to describe the operation tasks and/or other operation notes. |
Operations SQL Script created by Build SQL Button
SELECT
as OrderNumber,
as OpSeq,
as OpIdent,
as Description,
as RouteCommand,
as RouteParameter,
as Pool,
as ResourceName,
as SetupTime,
as Runtime,
as RuntimeMethod,
as TypeOfWork,
as QtyToBuild,
as QtyScrapped,
as QtyCompleted,
as QtyMultiplier,
as SetQtyManually,
as MinResources,
as MaxResources,
as MinMaxSetupTime,
as MinMaxRuntime,
as MinMaxActSetup,
as MinMaxActRuntime,
as MinMaxQtyToBuild,
as MinMaxQtyScrapped,
as MinMaxQtyCompleted,
as AutoReporting,
as StartOpWhenPrevComplete,
as VariableResource,
as StartEndSameDay,
as StartEndSameShift,
as NoCapacityInterruptions,
as NoInterruptions,
as NoInterruptDays,
as LockOnDates,
as SkipShifts,
as SkipShiftsNo,
as InfiniteOp,
as Certificate,
as NonContigOp,
as NonContigMinTime,
as PlannedDays,
as ActSetupTime,
as ActRuntime,
as ActStartDate,
as ActStartShift,
as ActEndDate,
as ActEndShift,
as OpStatus,
as BaselineStartDate,
as BaselineStartShift,
as BaselineEndDate,
as BaselineEndShift,
as WaitTime,
as MoveTime,
as WTUseCapacity,
as Notes
FROM
WHERE 1=1
Order By OrderNumber, OpSeq
Operation User Field Columns Required
Before data can be imported into an operation user field, the user field must first be defined in DMS. Depending on how the user field has been defined, the UF Text import would be one of the following text fields:
The MapNo is defined by the user in DMS to uniquely identify a user field. For example, an operation user field may have been defined as “Task Reference Code”. In order to import data into “Task Reference Code” the user field must be assigned a unique map reference number which is an integer number. So if “Task Reference Code” was given a map reference number of 3, then the import uses 3 to load the data into “Task Reference Code”.
|
|
Field |
Type |
Comment |
|
1. |
Order Number |
Varchar 25 |
The job to which this user field belongs. |
|
2. |
OpSeq |
Integer |
Required unique to job field. |
|
3. |
MapNo |
Integer |
Defined in DMS to uniquely identify a user field. |
|
4. |
UFText |
Text |
Text in one of 8 formats as defined above. |
Operation User Fields SQL Script created by Build SQL Button
SELECT
as OrderNumber,
as OpSeq,
as MapNo,
as UFText
FROM
WHERE 1=1
Order By OrderNumber, OpSeq
Operation Material Columns Required
Operation Material is used to set material parameters at the operation level.
|
|
Field |
Type |
Comment |
|
1. |
OrderNumber |
Varchar 25 |
The job to which this material information belongs. |
|
2. |
OpSeq |
Integer |
Required. Operation sequence number starting at 1. |
|
3. |
MaterialAvailable |
Boolean |
True if the material for the operation is available, otherwise False |
|
4. |
DaystoDelay |
Integer |
Days to delay the operation from the start date of the schedule run if material is not available. 0 to 999 days. |
|
5. |
ExpectedDelivery |
Date |
Blank or a valid date |
|
6. |
DateReceived |
Date |
Blank or a valid date |
Operation Material SQL Script created by Build SQL Button
SELECT
as OrderNumber,
as OpSeq,
as MaterialAvailable,
as DaysToDelay,
as ExpectedDelivery,
as DateReceived
FROM
WHERE 1=1
Order By OrderNumber, OpSeq
Material Details Columns Required
The Material Details is used to set detailed material requirement for a job.
|
|
Field |
Type |
Comment |
|
1. |
OrderNumber |
Varchar 25 |
The job to which this material information belongs. |
|
2. |
Sequence |
Integer |
0 or 1 to 500. If 0, the material item will be added if the job’s material already exists. If a sequence is given, the new record will replace the existing sequence material record if it exists. |
|
3. |
OpIdent |
Varchar 15 |
Blank or required if the material requirements are for a given operation. |
|
4. |
InvAbbrev |
Varchar 45 |
The inventory abbrev. The inv abbrev is an optional entry and must be either set to blank or a valid inv abbrev. |
|
5. |
ItemNo |
Varchar 45 |
The inventory Item No. The Item No must exist in the DMS Inventory table unless the Material Options Material Details Level "Do NOT use DMS Inventory table; use free format" is selected. |
|
6. |
JobAlternateItemNo |
Varchar 45 |
Either blank or a valid inventory Item No. The Item No must exist in the DMS Inventory table unless the Material Options Material Details Level "Do NOT use DMS Inventory table; use free format" is selected. If an Item No is given, the job can optionally use this Item No (if the required Item in field 4 above is not available). |
|
7. |
PerPiece |
Boolean |
True if the material requirements qty is to be multiple by the job qty. False if the material requirements qty is the amount for the entire job (regardless of job qty). |
|
8. |
MaterialAvailable |
Boolean |
True if the material is available (received), otherwise False |
|
9. |
QtyRequired |
Large Integer |
The item qty required. Must be greater than or equal to 0. |
|
10. |
QtyAvailable |
Large Integer |
The quantity available. Must be greater than or equal to 0. |
|
11. |
MaterialScrap |
Large Integer |
|
|
12. |
CanUseAltInInventory |
Boolean |
True if the job can use the alternate inventory item saved in the DMS inventory table if one exists. False if it cannot be used. This setting is in addition to the Job Alternate Item No. |
|
13. |
FloorStock |
Boolean |
True if the Item is floor stock and inventory will not be allocated with pegging. False if the requirements are to reduce the qty on hand/on order. |
|
14. |
Ordered Date |
Date |
Date material was ordered. |
|
15. |
ExpectedDelivery |
Date |
Material expected delivery date. |
|
16. |
DateReceived |
Date |
Date material was received. |
|
17. |
LeadTime |
Integer |
The lead time in days to get the material. If a valid ordered date and lead time are given and the expected date is blank, the expected date will be calculated by DMS. |
|
18. |
FirmDate |
Boolean |
True if the expected date is a firm date, otherwise false. |
|
19. |
PONumber |
Varchar 45 |
Optional PO number |
|
20. |
TextField1 |
Varchar 45 |
Optional material text user field |
|
21. |
TextField2 |
Varchar 45 |
Optional material text user field |
|
22. |
TextField3 |
Varchar 45 |
Optional material text user field |
|
23. |
TextField4 |
Varchar 45 |
Optional material text user field |
|
24. |
TextField5 |
Varchar 45 |
Optional material text user field |
|
25. |
NumField1 |
Money |
Optional material numeric user field |
|
26. |
NumField2 |
Money |
Optional material numeric user field |
|
27. |
NumField3 |
Money |
Optional material numeric user field |
|
28. |
NumField4 |
Money |
Optional material numeric user field |
|
29. |
NumField5 |
Money |
Optional material numeric user field |
|
30. |
Notes |
Memo |
Notes/comments |
Material Details SQL Script created by Build SQL Button
SELECT
as OrderNumber,
as Sequence,
as OpIdent,
as InvAbbrev,
as ItemNo,
as JobAlternateItemNo,
as PerPiece,
as MaterialAvailable,
as QtyRequired,
as QtyAvailable,
as MaterialScrap,
as CanUseAltInInventory,
as FloorStock,
as OrderedDate,
as LeadTime,
as FirmDate,
as ExpectedDelivery,
as DateReceived,
as PONumber,
as TextField1,
as TextField2,
as TextField3,
as TextField4,
as TextField5,
as NumField1,
as NumField2,
as NumField3,
as NumField4,
as NumField5,
as Notes
FROM
WHERE 1=1
Assembly Record Columns Required
If a job is part of an assembly, then the assembly structure must be defined.
|
|
Field |
Type |
Comment |
|
1. |
AssemblyName |
Varchar 25 |
|
|
2. |
FinalOrderNumber |
Varchar 25 |
The final order number for the entire assembly. Must be given for each component job and will be the same for all components in the assembly. |
|
3. |
OrderNumber |
Varchar 25 |
Component order number. |
|
4. |
OpIdent |
Varchar 15 |
Blank or a valid operation ident to feed into the parent. |
|
5. |
FeedsOrderNumber |
Varchar 25 |
The order number that is fed by the component order number being defined. This field can be left blank for the final assembly component. |
|
6. |
FeedsOpIdent |
Varchar 15 |
Blank or a valid operation ident to receive parts from the component order number. |
|
7. |
QtyToComplete |
Big integer |
0 or quantity to complete before parent can start. |
|
8. |
HoursToComplete |
Float |
0 or hours to complete before parent can start. |
|
9. |
SecondaryPriority |
Integer |
1 to 99. |
Assembly SQL Script created by Build SQL Button
SELECT
as AssemblyName,
as FinalOrderNumber,
as OrderNumber,
as OpIdent,
as FeedsOrderNumber,
as FeedsOpIdent,
as QtyToComplete,
as HoursToComplete,
as SecondaryPriority
FROM
WHERE 1=1
Order By AssemblyName
If a job is to be linked to another job, then the BOM job link structure must be defined.
|
|
Field |
Type |
Comment |
|
1. |
OrderNumber |
Varchar 25 |
Required. Order number of the job that will feed another job. |
|
2. |
OpIdent |
Varchar 15 |
Blank or the operation ident within the job that will feed another job. |
|
3. |
FeedsOrderNumber |
Varchar 25 |
Order number of the job that will be fed. |
|
4. |
FeedsOpIdent |
Varchar 15 |
Blank or the operation ident within the job that will be fed. |
|
5. |
QtyToComplete |
Big integer |
0 or the quantity to complete before fed order number can start. |
|
6. |
HoursToComplete |
Big integer |
0 or hours to complete before fed order number can start. |
BOM Job Link SQL Script created by Build SQL Button
SELECT
as OrderNumber,
as OpIdent,
as FeedsOrderNumber,
as FeedsOpIdent,
as QtyToComplete,
as HoursToComplete
FROM
WHERE 1=1
Order By OrderNumber