USE [vmfg] GO /****** Object: Table [dbo].[CUSTOMER] Script Date: 06/17/2015 17:36:06 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[CUSTOMER]( [ROWID] [int] IDENTITY(1,1) NOT NULL, [ID] [varchar](15) NOT NULL, [NAME] [varchar](50) NULL, [ADDR_1] [varchar](50) NULL, [ADDR_2] [varchar](50) NULL, [ADDR_3] [varchar](50) NULL, [CITY] [varchar](30) NULL, [STATE] [varchar](10) NULL, [ZIPCODE] [varchar](10) NULL, [COUNTRY] [varchar](50) NULL, [CONTACT_FIRST_NAME] [varchar](30) NULL, [CONTACT_LAST_NAME] [varchar](30) NULL, [CONTACT_INITIAL] [varchar](2) NULL, [CONTACT_POSITION] [varchar](20) NULL, [CONTACT_HONORIFIC] [varchar](15) NULL, [CONTACT_SALUTATION] [varchar](60) NULL, [CONTACT_PHONE] [varchar](20) NULL, [CONTACT_FAX] [varchar](20) NULL, [BILL_TO_NAME] [varchar](50) NULL, [BILL_TO_ADDR_1] [varchar](50) NULL, [BILL_TO_ADDR_2] [varchar](50) NULL, [BILL_TO_ADDR_3] [varchar](50) NULL, [BILL_TO_CITY] [varchar](30) NULL, [BILL_TO_STATE] [varchar](10) NULL, [BILL_TO_ZIPCODE] [varchar](10) NULL, [BILL_TO_COUNTRY] [varchar](50) NULL, [DISCOUNT_CODE] [varchar](15) NULL, [FREE_ON_BOARD] [varchar](25) NULL, [SHIP_VIA] [varchar](40) NULL, [SALESREP_ID] [varchar](15) NULL, [TERRITORY] [varchar](15) NULL, [CURRENCY_ID] [varchar](15) NOT NULL, [DEF_SLS_TAX_GRP_ID] [varchar](15) NULL, [ENTITY_ID] [varchar](5) NOT NULL, [SIC_CODE] [varchar](15) NULL, [IND_CODE] [varchar](15) NULL, [CREDIT_STATUS] [char](1) NOT NULL, [CREDIT_LIMIT_CTL] [char](1) NOT NULL, [CREDIT_LIMIT] [decimal](15, 2) NULL, [RECV_AGE_LIMIT] [smallint] NULL, [FINANCE_CHARGE] [decimal](6, 3) NULL, [TAX_EXEMPT] [char](1) NOT NULL, [TAX_ID_NUMBER] [varchar](25) NULL, [BACKORDER_FLAG] [char](1) NOT NULL, [TERMS_NET_TYPE] [char](1) NOT NULL, [TERMS_NET_DAYS] [smallint] NULL, [TERMS_NET_DATE] [datetime] NULL, [TERMS_DISC_TYPE] [char](1) NOT NULL, [TERMS_DISC_DAYS] [smallint] NULL, [TERMS_DISC_DATE] [datetime] NULL, [TERMS_DISC_PERCENT] [decimal](5, 3) NULL, [TERMS_DESCRIPTION] [varchar](80) NULL, [FREIGHT_TERMS] [char](1) NOT NULL, [DUNNING_LETTERS] [char](1) NOT NULL, [LAST_ORDER_DATE] [datetime] NULL, [OPEN_DATE] [datetime] NULL, [MODIFY_DATE] [datetime] NULL, [TOTAL_OPEN_ORDERS] [decimal](15, 2) NOT NULL, [TOTAL_OPEN_RECV] [decimal](15, 2) NOT NULL, [OPEN_ORDER_COUNT] [int] NOT NULL, [OPEN_RECV_COUNT] [int] NOT NULL, [USER_1] [varchar](80) NULL, [USER_2] [varchar](80) NULL, [USER_3] [varchar](80) NULL, [USER_4] [varchar](80) NULL, [USER_5] [varchar](80) NULL, [USER_6] [varchar](80) NULL, [USER_7] [varchar](80) NULL, [USER_8] [varchar](80) NULL, [USER_9] [varchar](80) NULL, [USER_10] [varchar](80) NULL, [SUPPLIER_ID] [varchar](15) NULL, [CARRIER_ID] [varchar](15) NULL, [TAX_ON_WHOLESALE] [char](1) NULL, [DEF_RECV_ACCT_ID] [varchar](30) NULL, [UDF_LAYOUT_ID] [varchar](15) NULL, [ARRIVAL_CODE] [varchar](25) NULL, [TRANS_CODE] [varchar](25) NULL, [COUNTRY_ID] [varchar](15) NULL, [NATURE_OF_TRANS] [int] NULL, [MODE_OF_TRANSPORT] [int] NULL, [SIRET_NUMBER] [decimal](14, 0) NULL, [VAT_REGISTRATION] [varchar](25) NULL, [VAT_BOOK_CODE_I] [varchar](5) NULL, [VAT_BOOK_CODE_M] [varchar](5) NULL, [VAT_EXEMPT] [char](1) NULL, [SHIPTO_ID] [varchar](20) NULL, [RMA_REQUIRED] [char](1) NULL, [PRIORITY_CODE] [varchar](15) NULL, [FILL_RATE_TYPE] [char](1) NULL, [WAREHOUSE_ID] [varchar](15) NULL, [COMPLIANCE_LABEL] [char](1) NULL, [CONSOLIDATE_ORDERS] [char](1) NULL, [ORDER_FILL_RATE] [decimal](5, 2) NOT NULL, [CONTACT_MOBILE] [varchar](20) NULL, [CONTACT_EMAIL] [varchar](50) NULL, [DEF_TRANS_CURRENCY] [varchar](15) NULL, [DEF_LBL_FORMAT_ID] [varchar](30) NULL, [CUSTOMER_TYPE] [varchar](20) NULL, [ACCEPT_EARLY] [char](1) NULL, [DAYS_EARLY] [smallint] NULL, [VAT_DISCOUNTED] [char](1) NOT NULL, [WEB_USER_ID] [varchar](20) NULL, [WEB_PASSWORD] [varchar](20) NULL, [REALLOCATE] [char](1) NOT NULL, [ALLOCATION_FENCE] [int] NULL, [PRIORITY] [smallint] NULL, [language_id] [varchar](2) NULL, [bill_language_id] [varchar](2) NULL, [def_ack_id] [varchar](15) NULL, [PRIMARILY_EDI] [char](1) NULL, [vat_always_disc] [varchar](1) NOT NULL, [vat_code] [varchar](15) NULL, [vat_override_seq] [varchar](1) NOT NULL, [return_trans] [int] NULL, [AUTO_ALLOCATE] [char](1) NULL, [CO_ALLOC_LEVEL] [char](1) NULL, [CASH_PERCENT_VAR] [char](1) NULL, [CASH_MIN_VAR] [decimal](15, 2) NULL, [CASH_MAX_VAR] [decimal](15, 2) NULL, [TAKE_DISC_DAYS] [decimal](15, 2) NULL, [MARKET_ID] [varchar](15) NULL, [CREDIT_CARD_ID] [varchar](250) NULL, [WEB_URL] [varchar](128) NULL, [UPS_ACCOUNT_ID] [varchar](15) NULL, [GENERATE_ASN] [varchar](1) NULL, [HOLD_TRANSFER_ASN] [varchar](1) NULL, [CUSTOMS_DOC_PRINT] [varchar](1) NULL, [SUPPRESS_AR_PRINT] [varchar](1) NULL, [ACCEPT_830] [varchar](1) NULL, [ACCEPT_862] [varchar](1) NULL, [POOL_CODE] [varchar](20) NULL, [INTER_CONSIGNEE] [varchar](20) NULL, [CONSOL_SHIP_LINE] [char](1) NULL, [PALLET_DETAILS_REQ] [char](1) NULL, [MATCH_INV_TO_PACK] [char](1) NULL, [ALLOW_OVER_PAYMNT] [char](1) NULL, [ALLOW_ADJ_INVOICE] [char](1) NULL, [TOTAL_OPEN_SHIPPED] [decimal](15, 2) NOT NULL, [DEF_GL_ACCT_ID] [varchar](30) NULL, [LIQ_GL_ACCT_ID] [varchar](30) NULL, [PBC_GL_ACCT_ID] [varchar](30) NULL, [GENERATE_WSA] [char](1) NULL, [HOLD_TRANSFER_WSA] [char](1) NULL, [ACTIVE_FLAG] [char](1) NULL, [SUPPRESS_PRINT_INV_NON_EDI] [char](1) NULL, [OPEN_OLDEST_RECV] [datetime] NULL, [STATUS_EFF_DATE] [datetime] NOT NULL, [PRICE_GROUP] [varchar](15) NULL, [CT_DAYS] [int] NULL, [CT_BEST_LOWER_PCT] [int] NULL, [CT_GOOD_LOWER_PCT] [int] NULL, [PT_DAYS] [int] NULL, [PT_BEST_LOWER_PCT] [int] NULL, [PT_GOOD_LOWER_PCT] [int] NULL, [SUPPRESS_PRINT_CUST_STMT] [char](1) NULL, [DEF_TERMS_ID] [varchar](15) NULL, [OLDEST_INV_TYPE] [char](1) NULL, CONSTRAINT [PK_CUSTOMER] PRIMARY KEY CLUSTERED ( [ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY] ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO ALTER TABLE [dbo].[CUSTOMER] WITH NOCHECK ADD CONSTRAINT [FK_CUST_CNTRY] FOREIGN KEY([COUNTRY_ID]) REFERENCES [dbo].[COUNTRY] ([ID]) GO ALTER TABLE [dbo].[CUSTOMER] CHECK CONSTRAINT [FK_CUST_CNTRY] GO ALTER TABLE [dbo].[CUSTOMER] WITH NOCHECK ADD CONSTRAINT [FKEY0001] FOREIGN KEY([ENTITY_ID]) REFERENCES [dbo].[ENTITY] ([ID]) GO ALTER TABLE [dbo].[CUSTOMER] CHECK CONSTRAINT [FKEY0001] GO ALTER TABLE [dbo].[CUSTOMER] WITH NOCHECK ADD CONSTRAINT [FKEY0042] FOREIGN KEY([DEF_SLS_TAX_GRP_ID]) REFERENCES [dbo].[SALES_TAX_GROUP] ([ID]) GO ALTER TABLE [dbo].[CUSTOMER] CHECK CONSTRAINT [FKEY0042] GO ALTER TABLE [dbo].[CUSTOMER] WITH NOCHECK ADD CONSTRAINT [FKEY0057] FOREIGN KEY([CURRENCY_ID]) REFERENCES [dbo].[CURRENCY] ([ID]) GO ALTER TABLE [dbo].[CUSTOMER] CHECK CONSTRAINT [FKEY0057] GO ALTER TABLE [dbo].[CUSTOMER] WITH NOCHECK ADD CONSTRAINT [FKEY0069] FOREIGN KEY([DISCOUNT_CODE]) REFERENCES [dbo].[CUSTOMER_DISCOUNT] ([DISCOUNT_CODE]) GO ALTER TABLE [dbo].[CUSTOMER] CHECK CONSTRAINT [FKEY0069] GO ALTER TABLE [dbo].[CUSTOMER] WITH NOCHECK ADD CONSTRAINT [FKEY9304] FOREIGN KEY([ARRIVAL_CODE]) REFERENCES [dbo].[PORT_OF_ARRIVAL] ([CODE]) GO ALTER TABLE [dbo].[CUSTOMER] CHECK CONSTRAINT [FKEY9304] GO ALTER TABLE [dbo].[CUSTOMER] WITH NOCHECK ADD CONSTRAINT [FKEY9305] FOREIGN KEY([TRANS_CODE]) REFERENCES [dbo].[PORT_OF_TRANS] ([CODE]) GO ALTER TABLE [dbo].[CUSTOMER] CHECK CONSTRAINT [FKEY9305] GO ALTER TABLE [dbo].[CUSTOMER] WITH NOCHECK ADD CONSTRAINT [FKEY9307] FOREIGN KEY([NATURE_OF_TRANS]) REFERENCES [dbo].[NATURE_OF_TRANS] ([CODE]) GO ALTER TABLE [dbo].[CUSTOMER] CHECK CONSTRAINT [FKEY9307] GO ALTER TABLE [dbo].[CUSTOMER] WITH NOCHECK ADD CONSTRAINT [FKEY9308] FOREIGN KEY([MODE_OF_TRANSPORT]) REFERENCES [dbo].[MODE_OF_TRANSPORT] ([CODE]) GO ALTER TABLE [dbo].[CUSTOMER] CHECK CONSTRAINT [FKEY9308] GO ALTER TABLE [dbo].[CUSTOMER] WITH NOCHECK ADD CONSTRAINT [FKEY9461] FOREIGN KEY([DEF_LBL_FORMAT_ID]) REFERENCES [dbo].[LABEL_FORMAT] ([ID]) GO ALTER TABLE [dbo].[CUSTOMER] CHECK CONSTRAINT [FKEY9461] GO ALTER TABLE [dbo].[CUSTOMER] WITH NOCHECK ADD CONSTRAINT [FKEY9470] FOREIGN KEY([DEF_TRANS_CURRENCY]) REFERENCES [dbo].[CURRENCY] ([ID]) GO ALTER TABLE [dbo].[CUSTOMER] CHECK CONSTRAINT [FKEY9470] GO ALTER TABLE [dbo].[CUSTOMER] WITH NOCHECK ADD CONSTRAINT [FKEY9499] FOREIGN KEY([def_ack_id]) REFERENCES [dbo].[VMDI_ACK_CODES] ([ACK_ID]) GO ALTER TABLE [dbo].[CUSTOMER] CHECK CONSTRAINT [FKEY9499] GO ALTER TABLE [dbo].[CUSTOMER] WITH NOCHECK ADD CONSTRAINT [FKEY9577] FOREIGN KEY([return_trans]) REFERENCES [dbo].[NATURE_OF_TRANS] ([CODE]) GO ALTER TABLE [dbo].[CUSTOMER] CHECK CONSTRAINT [FKEY9577] GO ALTER TABLE [dbo].[CUSTOMER] WITH NOCHECK ADD CONSTRAINT [FKEY9623] FOREIGN KEY([DEF_TERMS_ID]) REFERENCES [dbo].[TERMS] ([ID]) GO ALTER TABLE [dbo].[CUSTOMER] CHECK CONSTRAINT [FKEY9623] GO ALTER TABLE [dbo].[CUSTOMER] WITH CHECK ADD CONSTRAINT [CHK_CUSTOMER] CHECK ((([CREDIT_STATUS] = 'A' or [CREDIT_STATUS] = 'O' or [CREDIT_STATUS] = 'S' or [CREDIT_STATUS] = 'H') and ([CREDIT_LIMIT_CTL] = 'O' or [CREDIT_LIMIT_CTL] = 'N') and ([TERMS_NET_TYPE] = 'A' or [TERMS_NET_TYPE] = 'M' or [TERMS_NET_TYPE] = 'D' or [TERMS_NET_TYPE] = 'N' or [TERMS_NET_TYPE] = 'E' or [TERMS_NET_TYPE] = 'I') and ([TERMS_DISC_TYPE] = 'A' or [TERMS_DISC_TYPE] = 'M' or [TERMS_DISC_TYPE] = 'D' or [TERMS_DISC_TYPE] = 'N' or [TERMS_DISC_TYPE] = 'E') and ([DUNNING_LETTERS] = 'Y' or [DUNNING_LETTERS] = 'N') and ([TAX_EXEMPT] = 'Y' or [TAX_EXEMPT] = 'N') and ([RMA_REQUIRED] = 'Y' or [RMA_REQUIRED] = 'N') and ([VAT_DISCOUNTED] = 'Y' or [VAT_DISCOUNTED] = 'N') and ([VAT_ALWAYS_DISC] = 'Y' or [VAT_ALWAYS_DISC] = 'N') and ([VAT_OVERRIDE_SEQ] = 'C' or [VAT_OVERRIDE_SEQ] = 'P'))) GO ALTER TABLE [dbo].[CUSTOMER] CHECK CONSTRAINT [CHK_CUSTOMER] GO ALTER TABLE [dbo].[CUSTOMER] ADD CONSTRAINT [DF__CUSTOMER__CURREN__4A23E96A] DEFAULT (' ') FOR [CURRENCY_ID] GO ALTER TABLE [dbo].[CUSTOMER] ADD CONSTRAINT [DF__CUSTOMER__CREDIT__4B180DA3] DEFAULT (' ') FOR [CREDIT_STATUS] GO ALTER TABLE [dbo].[CUSTOMER] ADD CONSTRAINT [DF__CUSTOMER__CREDIT__4C0C31DC] DEFAULT (' ') FOR [CREDIT_LIMIT_CTL] GO ALTER TABLE [dbo].[CUSTOMER] ADD CONSTRAINT [DF__CUSTOMER__TAX_EX__4D005615] DEFAULT (' ') FOR [TAX_EXEMPT] GO ALTER TABLE [dbo].[CUSTOMER] ADD CONSTRAINT [DF__CUSTOMER__BACKOR__4DF47A4E] DEFAULT (' ') FOR [BACKORDER_FLAG] GO ALTER TABLE [dbo].[CUSTOMER] ADD CONSTRAINT [DF__CUSTOMER__TERMS___4EE89E87] DEFAULT (' ') FOR [TERMS_NET_TYPE] GO ALTER TABLE [dbo].[CUSTOMER] ADD CONSTRAINT [DF__CUSTOMER__TERMS___4FDCC2C0] DEFAULT (' ') FOR [TERMS_DISC_TYPE] GO ALTER TABLE [dbo].[CUSTOMER] ADD CONSTRAINT [DF__CUSTOMER__FREIGH__50D0E6F9] DEFAULT (' ') FOR [FREIGHT_TERMS] GO ALTER TABLE [dbo].[CUSTOMER] ADD CONSTRAINT [DF__CUSTOMER__DUNNIN__51C50B32] DEFAULT (' ') FOR [DUNNING_LETTERS] GO ALTER TABLE [dbo].[CUSTOMER] ADD CONSTRAINT [DF__CUSTOMER__TOTAL___52B92F6B] DEFAULT (0) FOR [TOTAL_OPEN_ORDERS] GO ALTER TABLE [dbo].[CUSTOMER] ADD CONSTRAINT [DF__CUSTOMER__TOTAL___53AD53A4] DEFAULT (0) FOR [TOTAL_OPEN_RECV] GO ALTER TABLE [dbo].[CUSTOMER] ADD CONSTRAINT [DF__CUSTOMER__OPEN_O__54A177DD] DEFAULT (0) FOR [OPEN_ORDER_COUNT] GO ALTER TABLE [dbo].[CUSTOMER] ADD CONSTRAINT [DF__CUSTOMER__OPEN_R__55959C16] DEFAULT (0) FOR [OPEN_RECV_COUNT] GO ALTER TABLE [dbo].[CUSTOMER] ADD CONSTRAINT [DF__CUSTOMER__ORDER___5689C04F] DEFAULT (0) FOR [ORDER_FILL_RATE] GO ALTER TABLE [dbo].[CUSTOMER] ADD CONSTRAINT [DF__CUSTOMER__VAT_DI__577DE488] DEFAULT ('Y') FOR [VAT_DISCOUNTED] GO ALTER TABLE [dbo].[CUSTOMER] ADD CONSTRAINT [DF__CUSTOMER__vat_al__65599662] DEFAULT (' ') FOR [vat_always_disc] GO ALTER TABLE [dbo].[CUSTOMER] ADD CONSTRAINT [DF__CUSTOMER__vat_ov__664DBA9B] DEFAULT (' ') FOR [vat_override_seq] GO ALTER TABLE [dbo].[CUSTOMER] ADD DEFAULT (0) FOR [TOTAL_OPEN_SHIPPED] GO ALTER TABLE [dbo].[CUSTOMER] ADD DEFAULT (getdate()) FOR [STATUS_EFF_DATE] GO