SQL Resource 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. 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 Resource Columns Required
Oracle Resource Columns Required

SQL Server Resource 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 resource updates:

Resource Header

	Field	Type	Comment
1.	ResourceName	Varchar (60)	Required unique field. Resource's name.
2.	Available	Boolean	True if the resource is available for use. False if the resource is not available.
3.	Department	Varchar (20)	Department name for the resource.
4.	TypeOfResource	Varchar (25)	The type of resource. Resource types are either Break, Labor, Outsource, Inventory, Prepare, Resource, Move or Wait.
5.	HoursPerShift	Float	The standard Hours/Shift for this resource. The hours per shift are used as the standard to populate the resource profile's hours per shift.
6.	HoursPerOperation	Float	The standard Hours/Operation for this resource. The hours per operation are used as the standard to populate the resource profile's hours per operation.
7.	EfficiencyFactor	Float	The Efficiency Factor for the resource. The efficiency factor is applied to both the hours per shift and the hours per operation to calculate the amount of scheduled capacity available
8.	StandardCostPerHour	Currency	Enter the standard cost per hour for the resource. The standard cost/hour is used by the Overtime Analysis to calculate standard costs versus overtime costs for a resource.
9.	OvertimeCostPerHour	Currency	Enter the overtime cost per hour for the resource. The overtime cost/hour is used by the Overtime Analysis to calculate standard costs versus overtime costs for a resource.
10.	Notes	Text	Blank or text to describe the resource.

Resource Profiles

	Field	Type	Comment
1.	ResourceName	Varchar (60)	Required unique field. The resource's name to add or edit the profile to.
2.	TypeOfWork	Varchar (20)	The type of work for the new profile. The type of work must already exist in DMS.
3.	Available	Boolean	True if the resource profile is available for use. False if the resource profile is not available.
4.	DefaultProfile	Boolean	True if this profile is the Standard Default Profile otherwise false.
5.	Constrained	Boolean	If the profile will be finite (i.e. constrained by the hours per shift) set this vale to true. If the profile for the resource will allow infinite loading, set this value to false.
6.	NonContiguousHours	Float	If the resource will allow noncontiguous scheduling, enter the minimum hours that must be available before the resource will be scheduled with a non contiguous load. If not, enter 0.0.
7.	StandardHours	Boolean	If the profile will be the standard hours used to calculate overtime, set this value to true. Otherwise, set this value to false.
8.	AutoReporting	Boolean	If you want this profile to be auto reporting, set this value to true. Otherwise, set this value to false.
9.	StartWhenPreviousCompleted	Boolean	If you want operations using this resource profile to automatically start when the previous operation has been completed, set this value to true. Otherwise, set this value to false.
10.	StartOperationOnNewDay	Boolean	If you want operations using this resource profile to start on a new day (next available day) after the end of the previous operation, set this value to true. Otherwise, set this value to false.
11.	StartNextOperationOnNewDay	Boolean	If you want operations that follow operations using this resource profile to start on a new day (next available day), set this value to true. Otherwise, set this value to false.
12.	CompleteWhenNextOperationStarted	Boolean	If you want operations that are using this resource to automatically be completed when the next operation starts (i.e. Break commands), set this value to true. Otherwise, set this value to false.
13.	MonHoursShift1 MonHoursShift2 MonHoursShift3 MonHoursOp1 MonHoursOp2 MonHoursOp3 TueHoursShift1 TueHoursShift2 TueHoursShift3 TueHoursOp1 TueHoursOp2 TueHoursOp3 WedHoursShift1 WedHoursShift2 WedHoursShift3 WedHoursOp1 WedHoursOp2 WedHoursOp3 ThrHoursShift1 ThrHoursShift2 ThrHoursShift3 ThrHoursOp1 ThrHoursOp2 ThrHoursOp3 FriHoursShift1 FriHoursShift2 FriHoursShift3 FriHoursOp1 FriHoursOp2 FriHoursOp3 SatHoursShift1 SatHoursShift2 SatHoursShift3 SatHoursOp1 SatHoursOp2 SatHoursOp3 SunHoursShift1 SunHoursShift2 SunHoursShift3 SunHoursOp1 SunHoursOp2 SunHoursOp3	Float	The hours/shift for each shift for each day of the week and the hours/operation for each shift for each day of the week must be given in a float format. There are 42 values in all; 6 values for each day of the week.

If the resource profile already exists (matched by resource name and type of work), the resource profile is replaced with the new profile.

Pools

	Field	Type	Comment
1.	PoolName	Varchar (20)	Required unique field. The pool's name to add or edit the pools to.
2.	LevelLoad	Varchar (20)	True if the pool is to be level loaded. Otherwise, set to false.
3.	Available	Boolean	True if the pool is available for use. False if the pool is not available.
4.	TypeOfPool	Varchar (25)	The type of pool. Pools are assigned a resource type and are either Break, Labor, Outsource, Inventory, Prepare, Resource, Move or Wait.
5.	Notes	Text	Blank or text to describe the pool.

Resource/Pool Relationships

	Field	Type	Comment
1.	ResourceName	Varchar (60)	Required unique field. The resource's name to add or edit the pools to.
2.	PoolName	Varchar (20)	The resource pool name for the new resource/pool relationship. The pool must already exist in DMS.
3.	DeletePool	Boolean	Set to false if you are adding a new pool or editing an existing pool. Set to true if you want to delete an existing resource/pool relationship.

Oracle Resource 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 resource updates:

Resource Header

	Field	Type	Comment
1.	ResourceName	Varchar2 (60)	Required unique field. Resource's name.
2.	Available	Boolean	True if the resource is available for use. False if the resource is not available.
3.	Department	Varchar2 (20)	Department name for the resource.
4.	TypeOfResource	Varchar2 (25)	The type of resource. Resource types are either Break, Labor, Outsource, Inventory, Prepare, Resource, Move or Wait.
5.	HoursPerShift	Float	The standard Hours/Shift for this resource. The hours per shift are used as the standard to populate the resource profile's hours per shift.
6.	HoursPerOperation	Float	The standard Hours/Operation for this resource. The hours per operation are used as the standard to populate the resource profile's hours per operation.
7.	EfficiencyFactor	Float	The Efficiency Factor for the resource. The efficiency factor is applied to both the hours per shift and the hours per operation to calculate the amount of scheduled capacity available
8.	StandardCostPerHour,	Currency	Enter the standard cost per hour for the resource. The standard cost/hour is used by the Overtime Analysis to calculate standard costs versus overtime costs for a resource.
9.	OvertimeCostPerHour	Currency	Enter the overtime cost per hour for the resource. The overtime cost/hour is used by the Overtime Analysis to calculate standard costs versus overtime costs for a resource.
10.	Notes	Varchar2 (2000)	Blank or text to describe the resource.

Resource Profiles

	Field	Type	Comment
1.	ResourceName	Varchar2 (20)	Required unique field. The resource's name to add or edit the profile to.
2.	TypeOfWork	Varchar2 (20)	The type of work for the new profile. The type of work must already exist in DMS.
3.	Available	Boolean	True if the resource profile is available for use. False if the resource profile is not available.
4.	DefaultProfile	Boolean	True if this profile is the Standard Default Profile otherwise false.
5.	Constrained	Boolean	If the profile will be finite (i.e. constrained by the hours per shift) set this vale to true. If the profile for the resource will allow infinite loading, set this value to false.
6.	NonContiguousHours	Float	If the resource will allow noncontiguous scheduling, enter the minimum hours that must be available before the resource will be scheduled with a non contiguous load. If not, enter 0.0.
7.	StandardHours	Boolean	If the profile will be the standard hours used to calculate overtime, set this value to true. Otherwise, set this value to false.
8.	AutoReporting	Boolean	If you want this profile to be auto reporting, set this value to true. Otherwise, set this value to false.
9.	StartWhenPreviousCompleted	Boolean	If you want operations using this resource profile to automatically start when the previous operation has been completed, set this value to true. Otherwise, set this value to false.
10.	StartOperationOnNewDay	Boolean	If you want operations using this resource profile to start on a new day (next available day) after the end of the previous operation, set this value to true. Otherwise, set this value to false.
11.	StartNextOperationOnNewDay	Boolean	If you want operations that follow operations using this resource profile to start on a new day (next available day), set this value to true. Otherwise, set this value to false.
12.	CompleteWhenNextOperationStarted	Boolean	If you want operations that are using this resource to automatically be completed when the next operation starts (i.e. Break commands), set this value to true. Otherwise, set this value to false.
13.	MonHoursShift1 MonHoursShift2 MonHoursShift3 MonHoursOp1 MonHoursOp2 MonHoursOp3 TueHoursShift1 TueHoursShift2 TueHoursShift3 TueHoursOp1 TueHoursOp2 TueHoursOp3 WedHoursShift1 WedHoursShift2 WedHoursShift3 WedHoursOp1 WedHoursOp2 WedHoursOp3 ThrHoursShift1 ThrHoursShift2 ThrHoursShift3 ThrHoursOp1 ThrHoursOp2 ThrHoursOp3 FriHoursShift1 FriHoursShift2 FriHoursShift3 FriHoursOp1 FriHoursOp2 FriHoursOp3 SatHoursShift1 SatHoursShift2 SatHoursShift3 SatHoursOp1 SatHoursOp2 SatHoursOp3 SunHoursShift1 SunHoursShift2 SunHoursShift3 SunHoursOp1 SunHoursOp2 SunHoursOp3	Float	The hours/shift for each shift for each day of the week and the hours/operation for each shift for each day of the week must be given in a float format. There are 42 values in all; 6 values for each day of the week.

If the resource profile already exists (matched by resource name and type of work), the resource profile is replaced with the new profile.

Pools

	Field	Type	Comment
1.	PoolName	Varchar2 (20)	Required unique field. The pool's name to add or edit the pools to.
2.	LevelLoad	Varchar2 (20)	True if the pool is to be level loaded. Otherwise false.
3.	Available	Boolean	True if the pool is available for use. False if the pool is not available.
4.	TypeOfPool	Varchar2 (25)	The type of pool. Pools are assigned a resource type and are either Break, Labor, Outsource, Inventory, Prepare, Resource, Move or Wait.
5.	Notes	Varchar2(2000)	Blank or text to describe the pool.

Resource/Pool Relationships

	Field	Type	Comment
1.	ResourceName	Varchar2 (60)	Required unique field. The resource's name to add or edit the pools to.
2.	PoolName	Varchar2 (20)	The resource pool name for the new resource/pool relationship. The pool must already exist in DMS.
3.	DeletePool	Boolean	Set to false if you are adding a new pool or editing an existing pool. Set to true if you want to delete an existing resource/pool relationship.