/* 
	Use this script to create tables in an existing database. The table 
	names can be altered but the field names cannot be modified. Additional
	fields may be added but they will not be read by Eclipse. Eclipse does
	not require a primary key. If one is needed, an auto-generated key can be added. 
*/

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ams_orderin]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[ams_orderin]
GO

CREATE TABLE [dbo].[ams_orderin] (
	[order_] [varchar] (20) NULL ,
	[bundle] [int] NULL ,
	[qty] [int] NULL ,
	[length] [decimal](8, 3) NULL ,
	[material] [varchar] (20) NULL ,
	[message] [varchar] (80) NULL ,
	[pcode] [varchar] (20) NULL ,
	[part_num] [varchar] (30) NULL ,
	[option_] [char] (1) NULL ,
	[bndl_msg] [varchar] (max) NULL ,
	[prin_msg] [varchar] (max) NULL ,
	[kit] [varchar] (24) NULL ,
	[item_id] [varchar] (40) NULL ,
	[action_] [char] (1) NULL ,
	[schedule] [datetime] NULL ,
	[machine] [int] NULL ,
	[user1] [varchar] (4000) NULL ,
	[user2] [varchar] (4000) NULL ,
	[offset] [decimal](8, 3) NULL ,
	[holecount] [int] NULL ,
	[stagger] [char] (1) NULL ,
	[partlabel] [varchar] (12) NULL ,
	[bndlelabel] [varchar] (12) NULL ,
	[user3] [varchar] (4000) NULL ,
	[user4] [varchar] (4000) NULL ,
	[user5] [varchar] (4000) NULL ,
	[sku] [varchar] (50) NULL ,
	[bndlecde] [varchar] (15) NULL ,
	[sqlseqnce] [int] NULL ,
	[sqlstatus] [char] (1) NULL ,
	[sqlplant] [varchar] (10) NULL ,
	[sch_time] [varchar] (8) NULL ,
	[piecemark] [varchar] (30) NULL ,
	[bundgrp] [varchar] (30) NULL		-- Used by Eclipse Pro to force items into separate bundles
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

/*	
	BundleIn is used for both importing bundle user fields and order 
	header data. To import order header, set the [bundle] to 0 (zero). 
*/

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ams_bundlein]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[ams_bundlein]
GO

CREATE TABLE [dbo].[ams_bundlein] (
	[order_] [varchar] (20) NULL ,
	[material] [varchar] (20) NULL ,
	[pcode] [varchar] (20) NULL ,
	[bundle] [int] NULL ,			-- When == 0: The row is the order header. When >= 1: the row is the bundle
	[user1] [varchar] (4000) NULL ,
	[user2] [varchar] (4000) NULL ,
	[user3] [varchar] (4000) NULL ,
	[user4] [varchar] (4000) NULL ,
	[user5] [varchar] (4000) NULL ,
	[bndl_msg] [varchar] (max) NULL ,
	[prin_msg] [varchar] (max) NULL ,
	[kit] [varchar] (30) NULL ,			-- Only for [bundle] = 0
	[bndlelabel] [varchar] (12) NULL ,
	[partlabel] [varchar] (12) NULL ,
	[custname] [varchar] (50) NULL ,		-- Only for [bundle] = 0
	[custaddr1] [varchar] (4000) NULL ,		-- Only for [bundle] = 0
	[custaddr2] [varchar] (4000) NULL ,		-- Only for [bundle] = 0
	[custcity] [varchar] (4000) NULL ,		-- Only for [bundle] = 0
	[custstate] [varchar] (2) NULL ,		-- Only for [bundle] = 0
	[custzip] [varchar] (10) NULL ,		-- Only for [bundle] = 0
	[custcntry] [varchar] (3) NULL ,		-- Only for [bundle] = 0
	[custinstr] [varchar] (4000) NULL ,		-- Only for [bundle] = 0
	[stagebay] [varchar] (10) NULL ,		-- Only for [bundle] = 0
	[loaddock] [varchar] (10) NULL ,		-- Only for [bundle] = 0
	[workorder] [varchar] (18) NULL ,		-- Only for [bundle] = 0
	[trucknbr] [varchar] (13) NULL ,		-- Only for [bundle] = 0
	[req_date] [datetime] NULL ,			-- Only for [bundle] = 0
	[pcodegrp] [varchar] (20) NULL ,		-- Only for [bundle] = 0
	[sqlstatus] [char] (1) NULL ,
	[sqlplant] [varchar] (10) NULL ,
	[req_time] [varchar] (8) NULL ,		-- Only for [bundle] = 0
	[custphone] [varchar] (30)NULL ,		-- Only for [bundle] = 0
	[custpo] [varchar] (30) NULL ,		-- Only for [bundle] = 0
	[salesorder] [varchar] (30) NULL ,		-- Only for [bundle] = 0
	[shipdate] [datetime] NULL ,			-- Only for [bundle] = 0
	[custnum] [varchar] (30) NULL ,		-- Only for [bundle] = 0
	[maxbundlbs] [int] NULL ,			-- Only for [bundle] = 0	-- Used by Eclipse Pro to auto bundle
	[maxbundqty] [int] NULL,			-- Only for [bundle] = 0	-- Used by Eclipse Pro to auto bundle
	[msg_text] [varchar] (40) NULL ,		-- Only for [bundle] = 0	-- Operator message that sent to controller above all line items
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ams_partin]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[ams_partin]
GO

CREATE TABLE [dbo].[ams_partin] (
	[part_num] [varchar] (30) NULL ,
	[length] [decimal](8, 3) NULL ,
	[option_] [char] (1) NULL ,
	[tool] [int] NULL ,
	[reference] [int] NULL ,
	[offset] [decimal](8, 3) NULL ,
	[permanent_] [bit] NULL ,
	[macro] [varchar] (30) NULL ,
	[y_offset] [decimal](8, 3) NULL ,
	[y_ref] [int] NULL ,
	[id_type] [char] (1) NULL,		-- Options: T=Tool, X=Shape, M=Macro, S=ShapeId (for SGF model). Defaults to 'T'
	[shape] [varchar] (50) NULL,		-- Requires Eclipse Pro
	[sqlstatus] [char] (1) NULL ,
	[sqlplant] [varchar] (10) NULL 
) ON [PRIMARY]
GO

/*
	Use this table when importing coils. It is also possible to not 
	use the coil data directly and have Eclipse verify a coil on demand
	using the coilverify data below.
*/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ams_coilin]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[ams_coilin]
GO

CREATE TABLE [dbo].[ams_coilin] (
	[action_] [char] (1) NULL ,
	[invcoil] [varchar] (30) NOT NULL ,
	[desc] [varchar] (40) NULL ,
	[datein] [varchar] (10) NULL ,
	[dateout] [varchar] (10) NULL ,
	[l_start] [varchar] (8) NULL ,
	[l_used] [varchar] (8) NULL ,
	[status] [char] (1) NULL ,
	[vendor] [varchar] (30) NULL ,
	[material] [varchar] (20) NOT NULL ,
	[type] [varchar] (10) NULL ,
	[cost_lb] [varchar] (9) NULL ,
	[nex_scrap] [varchar] (9) NULL ,
	[ex_scrap] [varchar] (9) NULL ,
	[oth_adjst] [varchar] (9) NULL ,
	[weight] [varchar] (10) NULL ,
	[sqlstatus] [char] (1) NULL ,
	[sqlplant] [varchar] (10) NULL ,
	[externalid] [varchar] (30) NULL,
	[user1] [varchar] (30) NULL,
	[user2] [varchar] (30) NULL
) ON [PRIMARY]
GO

/*
	This is the table structure if using on demand coil verify. In Eclipse Pro, this is also used for coil synchronization.
	Generally, this is implemented using a SQL view and not a table.
*/
/*
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ams_coilverify]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[ams_coilverify]
GO

CREATE TABLE [dbo].[ams_coilverify] (
	[invcoil] [varchar] (30) NOT NULL ,
	[status] [char] (1) NOT NULL ,
	[material] [varchar] (20) NULL ,
	[remaining] [decimal] (10,3) NULL ,
	[startingft] [decimal] (10,3) NULL,
	[desc] [varchar] (40) NULL ,
	[datein] [varchar] (10) NULL ,
	[dateout] [varchar] (10) NULL ,
	[lb_ft] [decimal] (8,3) NULL ,
	[weight] [varchar] (10) NULL ,
	[sqlplant] [varchar] (10) NULL ,
	[externalid] [varchar] (30) NULL 
) ON [PRIMARY]
GO
*/

/*
	Customer data is generally never used. Uncomment if needed.
*/
/*
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ams_customerin]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[ams_customerin]
GO

CREATE TABLE [dbo].[ams_customerin] (
	[action_] [char] (1) NULL ,
	[custcode] [varchar] (11) NULL ,
	[name] [varchar] (30) NULL ,
	[address1] [varchar] (30) NULL ,
	[address2] [varchar] (30) NULL ,
	[city] [varchar] (30) NULL ,
	[state] [varchar] (2) NULL ,
	[zip] [varchar] (10) NULL ,
	[country] [varchar] (3) NULL ,
	[shipstru] [varchar] (30) NULL ,
	[sqlstatus] [char] (1) NULL ,
	[sqlplant] [varchar] (10) NULL 
) ON [PRIMARY]
GO
*/

/*
	Material is best described as Coil Type. In addition to general material properties (color, gauge, etc.) it also includes width. 
*/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ams_materialin]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[ams_materialin]
GO

CREATE TABLE [dbo].[ams_materialin] (
	[action_] [char] (1) NULL ,
	[material] [varchar] (20) NULL ,
	[gauge] [int] NULL ,
	[thickness] [decimal](6, 4) NULL ,
	[width] [decimal](6, 3) NULL ,
	[color] [varchar] (20) NULL ,
	[type] [varchar] (10) NULL ,
	[cover] [decimal](7, 3) NULL ,
	[descript] [varchar] (40) NULL ,
	[lb_ft] [decimal](8, 4) NULL ,
	[cost_lb] [decimal](9, 4) NULL ,
	[normlngth] [decimal](9, 3) NULL ,
	[reorder] [int] NULL ,
	[sqlstatus] [char] (1) NULL ,
	[sqlplant] [varchar] (10) NULL ,
	[externalid] [varchar] (30) NULL
) ON [PRIMARY]
GO


/*
	PCode is generally the tooling required to make a part. Material changes that do not require machine adjustment (like color, etc.) generally should not force a different value.  
*/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ams_pcodein]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[ams_pcodein]
GO

CREATE TABLE [dbo].[ams_pcodein] (
	[action_] [char] (1) NULL ,
	[machine] [int] NULL ,
	[pcode] [varchar] (20) NULL ,
	[desc_] [varchar] (100) NULL ,
	[finwidth] [decimal](8, 3) NULL ,
	[stagebay] [varchar] (10) NULL ,
	[loaddock] [varchar] (10) NULL ,
	[holespace] [decimal](8, 3) NULL ,
	[calclength] [bit] NULL ,
	[holecount] [bit] NULL ,
	[ft_minutes] [int] NULL ,
	[coilchgmin] [decimal](6, 3) NULL ,
	[toolchgmin] [decimal](6, 3) NULL ,
	[toollib] [varchar] (25) NULL ,
	[setuplib] [varchar] (25) NULL ,
	[customlist] [varchar] (40) NULL ,
	[axislib] [varchar] (25) NULL ,
	[prefferred] [bit] NULL ,
	[setupinstr] [varchar] (4000) NULL ,
	[profile] [varchar] (100) NULL ,
	[legheight] [decimal](8, 3) NULL ,
	[pcodegrp] [varchar] (20) NULL ,
	[sqlstatus] [char] (1) NULL ,
	[sqlplant] [varchar] (10) NULL 
) ON [PRIMARY] 
GO

/*
	Prodout is the feedback table from Eclipse. It is an event log of activities from Eclipse and the controllers. See other documentation 
	for a description of the record types. Eclipse will only insert into this table. It is up to the upstream system to removed processed records as needed.
*/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ams_prodout]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[ams_prodout]
GO
/*	As noted above, Eclipse does not require a primary key. It also cannot
	set a primary key. If one is needed, use a server assigned key (auto) */
CREATE TABLE [dbo].[ams_prodout] (
	[type] [char] (1) NULL ,
	[reason] [varchar] (3) NULL ,
	[date_] [smalldatetime] NULL ,
	[time_] [char] (8) NULL ,
	[minutes] [decimal](11, 2) NULL ,
	[proddate] [smalldatetime] NULL ,
	[shift] [char] (1) NULL ,
	[machine] [int] NULL ,
	[machdesc] [varchar] (30) NULL ,
	[wrkgroup] [char] (2) NULL ,
	[order_] [varchar] (20) NULL ,
	[material] [varchar] (20) NULL ,
	[pcode] [varchar] (20) NULL ,
	[custname] [varchar] (30) NULL ,
	[workorder] [varchar] (18) NULL ,
	[ordertype] [char] (1) NULL ,
	[bundle] [int] NULL ,
	[qty] [int] NULL ,
	[length] [decimal](8, 3) NULL ,		-- programmed part length in inches
	[option_] [char] (1) NULL ,
	[part_num] [varchar] (30) NULL ,
	[pattern] [char](3) NULL ,
	[totlength] [decimal](11, 3) NULL ,		-- produced length in inches (qty x length)
	[footage] [decimal](11, 3) NULL ,		-- length consumed from the coil in inches
	[offset] [decimal](8, 3) NULL ,
	[holecount] [int] NULL ,
	[invcoil] [varchar] (30) NULL ,			-- coil serial number
	[coilmatl] [varchar] (20) NULL ,		-- material code of coil
	[lb_ft] [decimal](6, 3) NULL ,
	[cost_lb] [decimal](5, 2) NULL ,
	[code_type] [char] (1) NULL ,
	[code_val] [int] NULL ,
	[code_desc] [varchar] (30) NULL ,
	[code_exmpt] [char] (1) NULL ,
	[machstatus] [char] (1) NULL ,
	[duration] [decimal](8, 2) NULL ,
	[runtime] [decimal](8, 2) NULL ,		-- the following "time" values are in minutes
	[downtime] [decimal](8, 2) NULL ,
	[exemptime] [decimal](8, 2) NULL ,
	[good] [decimal](11, 3) NULL ,		-- good material used in inches
	[scrap] [decimal](11, 3) NULL ,		-- scrapped material in inches (non-exempt)
	[exmptscrap] [decimal](11, 3) NULL ,		-- scrapped material in inches (exempt)
	[reclaimed] [decimal](11, 3) NULL ,		-- previously scrapped material that was converted to good (decrement qty function)
	[actspeed] [decimal](8, 3) NULL ,		-- feet per minute
	[targspeed] [decimal](11, 3) NULL ,
	[employ_id] [int] NULL ,
	[name] [varchar] (30) NULL ,
	[toolchg] [int] NULL ,			-- 1 if pcode changed
	[matlchg] [int] NULL ,			-- 1 if material changed
	[coilchg] [int] NULL ,			-- 1 if coil changed
	[matldev] [int] NULL ,			-- 1 if the coil material does not match the order material
	[item_id] [varchar] (40) NULL ,
	[listid] [int] NULL ,
	[listtext] [varchar] (40) NULL ,
	[plantname] [varchar] (30) NULL ,
	[profile] [varchar] (100) NULL ,
	[sku] [varchar] (50) NULL ,
	[sqlstatus] [char] (1) NULL ,			-- Value will always be 'N'
	[sqlplant] [varchar] (10) NULL,
	[histdate] [datetime] NULL,			-- Date the record was read by Eclipse
	[listtrigr] [int] NULL,
	[listvalid] [varchar](100) NULL,
	[code_resp] [int] NULL,
	[bndlecde] [varchar](15) NULL,		-- Bundle id provided in order import. 
	[scrapqty] [int] NULL,			-- Number of full pieces that were scrapped.
	[exptmatlen] [decimal](14,6) NULL,		-- Expected inches consumed by 1 good piece. Especially important when producing spanish style roofing panels where the more material is required than the part length.
	[shearkerf] [decimal](14,6) NULL,
	[bundleid] [varchar](15) NULL,		-- Bundle id generated by the controller
	[sequence] [int] NULL,
	[itmid] [bigint] NULL,			-- Eclipse Internal Id of the order item
	[ordid] [bigint] NULL,			-- Eclipse Internal Id of the order header. Can be used to reconstruct the job sourced bundle identifier
) ON [PRIMARY]
GO