|
Oracle Job Import Columns Required
|   |
|
Oracle 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.
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 for Oracle, 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.
If you do not use a DMS column, you can always hard code the SQL script to set the column to have a default value such as:
'' as PlannerEmplNo,
0 as LotSize,
…
These example statements will always set the column PlannerEmplNo to blank and LotSize to 0 for all jobs being imported.
The script example below shows a DMS script for an Oracle database. In this example, the data being imported from the database has column names the same as the DMS required columns with a 1 appended to the end of the name. The database view name is OrderImport and only those jobs that have been marked for release in the Oracle database will be imported (i.e. the example statement OrderImport.JobReleased=1):
SELECT
OrderNumber1 as OrderNumber,
PartNumber1 as PartNumber,
JobIdentification1 as JobIdentification,
JobState1 as JobState,
Priority1 as Priority,
ReleaseShift1 as ReleaseShift,
DueShift1 as DueShift,
QtyOrdered1 as QtyOrdered,
LotSize1 as LotSize,
SequenceType1 as SequenceType,
ScrapFactor1 as ScrapFactor,
Overlap1 as Overlap,
ScheduleMethod1 as ScheduleMethod,
MidPointOpSeq1 as MidPointOpSeq,
TypeOfWork1 as TypeOfWork,
JobType1 as JobType,
BaselineStartShift1 as BaselineStartShift,
BaselineEndShift1 as BaselineEndShift,
'' as TemplateOrderNumber,
'' as PlannerEmplNo,
OrderDate1 as OrderDate,
ReleaseDate1 as ReleaseDate,
DueDate1 as duedate,
PromiseDate1 as PromiseDate,
BaselineStartDate1 as BaselineStartDate,
BaselineEndDate1 as BaselineEndDate,
dbms_lob.substr(Notes1, 1000, 1) as notes
FROM OrderImport
WHERE OrderImport.JobReleased=1
The following column definitions are required for the SQL import:
|
|
Field |
Type |
Comment |
|
|
OrderNumber |
VarChar2 (25) |
|
|
|
PartNumber |
VarChar2 (45) |
|
|
|
JobIdentification |
VarChar2 (45) |
|
|
|
JobState |
VarChar2 (20) |
A valid existing Job State is required. |
|
|
TemplateOrderNumber |
VarChar2 (25) |
Blank or the existing template order number to copy if adding a new order. |
|
|
Priority |
Number(5) |
1 to 99 |
|
|
OrderDate |
Date |
Null or a valid date |
|
|
ReleaseDate |
Date |
|
|
|
ReleaseShift |
Number(2) |
1, 2 or 3 |
|
|
DueDate |
Date |
|
|
|
DueShift |
Number(2) |
1, 2 or 3 |
|
|
PromiseDate |
Date |
Null or a valid date |
|
|
QtyOrdered |
Number(10) |
|
|
|
ScrapFactor |
Float |
From 0 to 100% |
|
|
SequenceType |
VarChar2 (26 |
A valid sequence type. |
|
|
LotSize |
Number(10) |
0 or a lot size as required by the sequence type. |
|
|
Overlap |
Float |
0 or a lot size as required by the sequence type, or optional queue time if the sequence type requires a queue time parameter. |
|
|
ScheduleMethod |
VarChar2 (20) |
A valid schedule method. |
|
|
MidPointOpSeq |
Number(5) |
0 or the mid point operation sequence number to use if required by schedule method |
|
|
TypeOfWork |
VarChar2 (20) |
Blank for default or a valid type of work. |
|
|
JobType |
VarChar2 (1) |
P for production or F for fabrication |
|
|
BaselineStartDate |
Date |
Null or a valid date |
|
|
BaselineStartShift |
Number(2) |
1, 2, or 3 |
|
|
BaselineEndDate |
Date |
Null or a valid date |
|
|
BaselineEndShift |
Number(2) |
1, 2, or 3 |
|
|
PlannerEmplNo |
VarChar2 (20) |
Blank or a valid employee number must be given. |
|
|
Notes |
VarChar2 (2000) |
Blank or text to describe the job and/or other job notes. |
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 |
VarChar2 (25) |
The job to which this user field belongs. |
|
2. |
MapNo |
Number(5) |
Defined in DMS to uniquely identify a user field. |
|
3. |
UFText |
VarChar2(2000) |
Text field where one of 10 formats as defined above can be used |
Job material updates the material information on the job header.
|
|
Field |
Type |
Comment |
|
1. |
OrderNumber |
VarChar2 (25) |
The job to which this material information belongs. |
|
2. |
MaterialAvailable |
Boolean: Number(1) |
1 if the material for the job is available, otherwise 0 |
|
3. |
DaysToDelay |
Number(5) |
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 null |
|
5. |
DateReceived |
Date |
Valid date or null |
|
6. |
MaterialPlannerEmplNo |
VarChar2 (20) |
Blank or a valid employee number must be given. |
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 |
VarChar2 (25) |
The job to which the job multi template information belongs. |
|
2. |
Template Order Number |
VarChar2 (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: Number(1) |
If selected by the scheduler, this field is 1. Otherwise, this field is 0. |
|
6. |
Locked |
Boolean: Number(1) |
If locked by the user, this field is 1. Otherwise, this field is 0. |
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 |
VarChar2 (25) |
The parent job to which this operation belongs. |
|
2. |
OpSeq |
Number(5) |
The operation sequence number within the job. 1 to 400. |
|
3. |
OpIdent |
VarChar2 (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 |
VarChar2 (40) |
Blank or a short description of the operations |
|
5. |
RouteCommand |
VarChar2 (26) |
Blank or a valid command. |
|
6. |
RouteParameter |
VarChar2 (20) |
Blank or a valid parameter to the route command |
|
7. |
Pool |
VarChar2 (20) |
Blank or a valid existing pool name. |
|
8. |
ResourceName |
VarChar2 (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 |
VarChar2 (1) |
T for time per piece, P for parts per hour, H for hours, or L for time per lot. |
|
12. |
TypeOfWork |
VarChar2 (20) |
Blank for default or a valid type of work. |
|
13. |
QtyToBuild |
Number(10) |
0 or must be given if “Quantity set manually” is 1. |
|
14. |
QtyScrapped |
Number(10) |
0 or the total amount scrapped by this operation. |
|
15. |
QtyCompleted |
Number(10) |
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: Number(1) |
0 if the quantity is the same as the job quantity. 1 if the quantity to build for this operation will be different than the job qty. |
|
18. |
MinResources |
Number(5) |
0 to 99. If greater than 0, minimum resources must be less than maximum resources. |
|
19. |
MaxResources |
Number(5) |
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 |
Number(10) |
0 or if a min/max operation command, the total quantity to build for the min/max operations. |
|
25. |
MinMaxQtyScrapped |
Number(10) |
0 or if a min/max operation command, the total quantity scrapped for the min/max operations. |
|
26. |
MinMaxQtyComplete |
Number(10) |
0 or if a min/max operation command, the total quantity completed for the min/max operations. |
|
27. |
AutoReporting |
Boolean: Number(1) |
0 for a regular operation. 1 if the operation will be auto reporting. |
|
28. |
StartOpWhenPrevComplete |
Boolean: Number(1) |
0 for a regular operation. 1 if the operation is to be set to in process when the previous operation is completed. |
|
29. |
VariableResource |
Boolean: Number(1) |
0 for a regular operation. 1 if a AND command block and each AND operation can have variable start and end times. |
|
30. |
StartEndSameDay |
Boolean: Number(1) |
0 for a regular operation. 1 if the operation must start and end on the same day. |
|
31. |
StartEndSameShift |
Boolean: Number(1) |
0 for a regular operation. 1 if the operation must start and end on the same shift. |
|
32. |
NoCapacityInterruptions |
Boolean: Number(1) |
0 for a regular operation. 1 if the operation cannot have any capacity interruptions. |
|
33. |
NoInterruptions |
Boolean: Number(1) |
0 for a regular operation. 1 if the operation cannot have any interruptions (i.e. holidays, non working shifts, etc.) |
|
34. |
NoInterruptDays |
Number(5) |
Optional parameter for NoInterruptions to specify the permitted length of the interrupt. If not used, set to 0. |
|
35. |
LockOnDates |
Boolean: Number(1) |
0 for a regular operation. 1 if the operation must be scheduled on the given start and end dates. |
|
36. |
SkipShifts |
Boolean: Number(1) |
0 for a regular operation, 1 if the operation can skip a shift if capacity is not available on that shift |
|
37. |
SkipShiftsNo |
VarChar2 (3) |
If skip shifts is 1, 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 1, the default is All. |
|
38. |
NonContigOp |
Boolean: Number(1) |
0 for a regular operation. 1 if the operation can be non-contiguous. |
|
39. |
NonContigMinTime |
Float |
0 or if a non-contiguous operation, then the time given here is the minimum runtime required. |
|
40 |
PlannedDays |
Number(5) |
0 for regular operations. Number of elapsed or planned days for the operation. |
|
41. |
ActSetupTime |
Float |
0, or the actual setup for this operation. |
|
42. |
ActRunTime |
Float |
0, or the total actual runtime for this operation. |
|
43. |
ActStartDate |
Date |
Null or the actual start date for the operation. |
|
44. |
ActStartShift |
Number(2) |
1, 2, or 3 |
|
45. |
ActEndDate |
Date |
Null or the actual end date for the operation. |
|
46. |
ActEndShift |
Number(2) |
1, 2, or 3 |
|
47. |
OpStatus |
VarChar2 (5) |
Blank or a valid operation status |
|
48. |
BaselineStartDate |
Date |
Null or date for reporting purposes. |
|
49. |
BaselineStartShift |
Number(2) |
1, 2 or 3 |
|
50. |
BaselineEndDate |
Date |
Null or date for reporting purposes. |
|
51. |
BaselineEndShift |
Number(2) |
1, 2 or 3 |
|
52. |
MoveTime |
Float |
Optional move time to add after the operation runtime, or 0. |
|
53. |
WaitTime |
Float |
Optional wait time to add after the operation runtime, or 0. |
|
54. |
WTUseCapacity |
Boolean: Number(1) |
Future use. Set to false. |
|
55. |
Notes |
VarChar2(2000) |
Blank or text to describe the operation tasks and/or other operation notes. |
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 user field 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 |
VarChar2 (25) |
The job to which this user field belongs. |
|
2. |
OpSeq |
Number(5) |
Required unique to job field. |
|
3. |
MapNo |
Number(5) |
Defined in DMS to uniquely identify a user field. |
|
4. |
UFText |
VarChar2(2000) |
Text in one of 8 formats as defined above. |
Operation Material Columns Required
Operation Material is used to set material parameters at the operation level.
|
|
Field |
Type |
Comment |
|
1. |
OrderNumber |
VarChar2 (25) |
The job to which this material information belongs. |
|
2. |
OpSeq |
Number(5) |
Required. Operation sequence number starting at 1. |
|
3. |
MaterialAvailable |
Boolean: Number(1) |
1 if the material for the operation is available, otherwise 0 |
|
4. |
DaystoDelay |
Number(5) |
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 |
Null or a valid date |
|
6. |
DateReceived |
Date |
Null or a valid date |
Material Details Columns Required
The Material Details is used to set detailed material requirement for a job.
|
|
Field |
Type |
Comment |
|
1. |
OrderNumber |
Varchar2(25) |
The job to which this material information belongs. |
|
2. |
Sequence |
Number(5) |
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 |
Varchar2(15) |
Blank or required if the material requirements are for a given operation. |
|
4. |
InvAbbrev |
Varchar2(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 |
Varchar2(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 |
Varchar2(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: Number(1) |
1 if the material requirements qty is to be multiple by the job qty. 0 if the material requirements qty is the amount for the entire job (regardless of job qty). |
|
8. |
MaterialAvailable |
Boolean: Number(1) |
1 if the material is available (received), otherwise 0 |
|
9. |
QtyRequired |
Number(10) |
The item qty required. Must be greater than or equal to 0. |
|
10. |
QtyAvailable |
Number(10) |
The quantity available. Must be greater than or equal to 0. |
|
11. |
MaterialScrap |
Number(10) |
|
|
12. |
CanUseAltInInventory |
Boolean: Number(1) |
1 if the job can use the alternate inventory item saved in the DMS inventory table if one exists. 0 if it cannot be used. This setting is in addition to the Job Alternate Item No. |
|
13. |
FloorStock |
Boolean: Number(1) |
1 if the Item is floor stock and inventory will not be allocated with pegging. 0 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 |
Number(5) |
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: Number(1) |
1 if the expected date is a firm date, otherwise 0. |
|
19. |
PONumber |
VarChar2(45) |
Optional PO number |
|
20. |
TextField1 |
VarChar2(45) |
Optional material text user field |
|
21. |
TextField2 |
VarChar2(45) |
Optional material text user field |
|
22. |
TextField3 |
VarChar2(45) |
Optional material text user field |
|
23. |
TextField4 |
VarChar2(45) |
Optional material text user field |
|
24. |
TextField5 |
VarChar2(45( |
Optional material text user field |
|
25. |
NumField1 |
Number(10,4) |
Optional material numeric user field |
|
26. |
NumField2 |
Number(10,4) |
Optional material numeric user field |
|
27. |
NumField3 |
Number(10,4) |
Optional material numeric user field |
|
28. |
NumField4 |
Number(10,4) |
Optional material numeric user field |
|
29. |
NumField5 |
Number(10,4) |
Optional material numeric user field |
|
30. |
Notes |
VarChar2(2000) |
Notes/comments |
|
|
Field |
Type |
Comment |
|
1. |
OrderNumber |
VarChar2 (25) |
The job to which this material information belongs. |
|
2. |
Sequence |
Number(5) |
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 |
VarChar2 (15) |
Blank or an existing operation ident if the material requirements are for a given operation. |
|
4. |
InvAbbrev |
VarChar2 (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 |
VarChar2 (45) |
The inventory Item No. The Item No must exist in the DMS Inventory table. |
|
6. |
JobAlternateItemNo |
VarChar2 (45) |
Either blank or a valid inventory Item No. The Item No must exist in the DMS Inventory table. 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: Number(1) |
1 if the material requirements qty is to be multiple by the job qty. 0 if the material requirements qty is the amount for the entire job (regardless of job qty). |
|
8. |
MaterialAvailable |
Boolean: Number(1) |
1 if the material is available (received), otherwise 0 |
|
9. |
QtyRequired |
Number(10) |
The item qty required for the job. Must be greater than or equal to 0. |
|
10. |
QtyAvailable |
Number(10) |
The quantity available for the job. Must be greater than or equal to 0. |
|
11. |
CanUseAltInInventory |
Boolean: Number(1) |
1 if the job can use the alternate inventory item saved in the DMS inventory table if one exists. 0 if it cannot be used. This setting is in addition to the Job Alternate Item No. |
|
12. |
FloorStock |
Boolean: Number(1) |
1 if the Item is floor stock and inventory will not be allocated with pegging. 0 if the requirements are to reduce the qty on hand/on order. |
|
13. |
OrderedDate |
Date |
Valid date or null. If a valid ordered date and lead time are given and the expected date is blank, the expected date will be calculated by DMS. |
|
14. |
LeadTime |
Number(5) |
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. |
|
15. |
FirmDate |
Boolean: Number(1) |
1 if the expected date is a firm date, otherwise 0. |
|
16. |
ExpectedDelivery |
Date |
Date or null. |
|
17. |
DateReceived |
Date |
Date or null. |
|
18. |
Notes |
VarChar2(2000) |
Notes/comments |
Assembly Record Columns Required
If a job is part of an assembly, then the assembly structure must be defined.
|
|
Field |
Type |
Comment |
|
1. |
AssemblyName |
VarChar2 (25) |
The name of the assembly. The name must be the same for all components of the same assembly. |
|
2. |
FinalOrderNumber |
VarChar2 (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 |
VarChar2 (25) |
Component order number. |
|
4. |
OpIdent |
VarChar2 (15) |
Blank or a valid operation ident to feed into the parent. |
|
5. |
FeedsOrderNumber |
VarChar2 (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 |
VarChar2 (15) |
Blank or a valid operation ident to receive parts from the component order number. |
|
7. |
QtyToComplete |
Number(10) |
0 or quantity to complete before parent can start. |
|
8. |
HoursToComplete |
Float |
0 or hours to complete before parent can start. |
|
9. |
SecondaryPriority |
Number(5) |
1 to 99. |
If a job is to be linked to another job, then the BOM job link structure must be defined.
|
|
Field |
Type |
Comment |
|
1. |
OrderNumber |
VarChar2 (25) |
Required. Order number of the job that will feed another job. |
|
2. |
OpIdent |
VarChar2 (15) |
Blank or the operation ident within the job that will feed another job. |
|
3. |
FeedsOrderNumber |
VarChar2 (25) |
Order number of the job that will be fed. |
|
4. |
FeedsOpIdent |
VarChar2 (15) |
Blank or the operation ident within the job that will be fed. |
|
5. |
QtyToComplete |
Number(10) |
0 or the quantity to complete before fed order number can start. |
|
6. |
HoursToComplete |
Float |
0 or hours to complete before fed order number can start. |