專案

一般

配置概況

下載 (27 KB)

新增需求 #2108 » HQ_DOC_script.sql

德宥 劉, 2024-10-24 05:12

 
??USE [HQ_W22310_WMS]
GO
ALTER TABLE [dbo].[his_doc_inbound_detail] DROP CONSTRAINT [FK_HISqnojq0h1a87rgfpl7e0ae09r]
GO
ALTER TABLE [dbo].[his_doc_outbound_detail] DROP CONSTRAINT [DF__his_doc_outbo__weigh__0697FACD]
GO
ALTER TABLE [dbo].[his_doc_outbound_detail] DROP CONSTRAINT [DF__his_doc_outbo__diffe__05A3D694]
GO
ALTER TABLE [dbo].[his_doc_outbound] DROP CONSTRAINT [DF__his_doc_outbo__total__04AFB25B]
GO
ALTER TABLE [dbo].[his_doc_outbound] DROP CONSTRAINT [DF__his_doc_outbo__total__03BB8E22]
GO
ALTER TABLE [dbo].[his_doc_outbound] DROP CONSTRAINT [DF__his_doc_outbo__total__02C769E9]
GO
ALTER TABLE [dbo].[his_doc_inbound_detail] DROP CONSTRAINT [DF__his_doc_inbou__weigh__1C873BEC]
GO
ALTER TABLE [dbo].[his_doc_inbound_detail] DROP CONSTRAINT [DF__his_doc_inbou__diffe__1B9317B3]
GO
ALTER TABLE [dbo].[his_doc_inbound] DROP CONSTRAINT [DF__his_doc_inbou__total__7AF13DF7]
GO
ALTER TABLE [dbo].[his_doc_inbound] DROP CONSTRAINT [DF__his_doc_inbou__total__79FD19BE]
GO
ALTER TABLE [dbo].[his_doc_inbound] DROP CONSTRAINT [DF__his_doc_inbou__total__18B6AB08]
GO
/****** Object: Table [dbo].[his_doc_outbound_detail_desc] Script Date: 2024/10/23 NHS 03:58:36 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[his_doc_outbound_detail_desc]') AND type in (N'U'))
DROP TABLE [dbo].[his_doc_outbound_detail_desc]
GO
/****** Object: Table [dbo].[his_doc_outbound_detail] Script Date: 2024/10/23 NHS 03:58:36 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[his_doc_outbound_detail]') AND type in (N'U'))
DROP TABLE [dbo].[his_doc_outbound_detail]
GO
/****** Object: Table [dbo].[his_doc_outbound] Script Date: 2024/10/23 NHS 03:58:36 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[his_doc_outbound]') AND type in (N'U'))
DROP TABLE [dbo].[his_doc_outbound]
GO
/****** Object: Table [dbo].[his_doc_inbound_detail_desc] Script Date: 2024/10/23 NHS 03:58:36 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[his_doc_inbound_detail_desc]') AND type in (N'U'))
DROP TABLE [dbo].[his_doc_inbound_detail_desc]
GO
/****** Object: Table [dbo].[his_doc_inbound_detail] Script Date: 2024/10/23 NHS 03:58:36 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[his_doc_inbound_detail]') AND type in (N'U'))
DROP TABLE [dbo].[his_doc_inbound_detail]
GO
/****** Object: Table [dbo].[his_doc_inbound] Script Date: 2024/10/23 NHS 03:58:36 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[his_doc_inbound]') AND type in (N'U'))
DROP TABLE [dbo].[his_doc_inbound]
GO
/****** Object: Table [dbo].[his_doc_inbound] Script Date: 2024/10/23 NHS 03:58:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[his_doc_inbound](
[id] [bigint] IDENTITY(1,1) NOT NULL,
[create_time] [datetime2](7) NULL,
[creator] [varchar](255) NULL,
[update_time] [datetime2](7) NULL,
[updater] [varchar](255) NULL,
[allocate_date] [varchar](10) NULL,
[assign_workStation_id] [varchar](255) NULL,
[status] [int] NOT NULL,
[end_date_time] [datetime2](7) NULL,
[inbound_date] [varchar](10) NULL,
[memo] [nvarchar](255) NULL,
[op_user_ids] [nvarchar](max) NULL,
[prod_code] [varchar](255) NULL,
[receive_source] [nvarchar](50) NULL,
[receive_date_time] [datetime2](7) NULL,
[ref_no] [varchar](255) NULL,
[start_date_time] [datetime2](7) NULL,
[supplier] [nvarchar](255) NULL,
[sys_order_no] [varchar](255) NOT NULL,
[total_difference_qty] [decimal](38, 10) NULL,
[total_plan_qty] [decimal](38, 10) NULL,
[total_prod_qty] [decimal](38, 10) NULL,
[user_id] [bigint] NULL,
[trans_no] [nvarchar](30) NULL,
[trans_date_time] [datetime2](7) NULL,
[trans_type] [nvarchar](400) NULL,
[carrier_id] [varchar](255) NULL,
[sub_carrier_id] [nvarchar](24) NULL,
[weight] [int] NULL,
CONSTRAINT [PK__his_doc_inbo__3213E83FD33FE224] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
CONSTRAINT [his_doc_inbound_sys_order_no] UNIQUE NONCLUSTERED
(
[sys_order_no] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object: Table [dbo].[his_doc_inbound_detail] Script Date: 2024/10/23 NHS 03:58:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[his_doc_inbound_detail](
[id] [bigint] IDENTITY(1,1) NOT NULL,
[create_time] [datetime2](7) NULL,
[creator] [varchar](255) NULL,
[update_time] [datetime2](7) NULL,
[updater] [varchar](255) NULL,
[carrier_id] [varchar](255) NULL,
[difference_qty] [decimal](38, 10) NULL,
[end_date_time] [datetime2](7) NULL,
[is_finished] [bit] NOT NULL,
[location_code] [varchar](255) NULL,
[op_user_ids] [nvarchar](max) NULL,
[parent_id] [bigint] NOT NULL,
[plan_qty] [decimal](38, 10) NULL,
[prod_code] [varchar](255) NULL,
[prod_inbound_date] [varchar](20) NULL,
[prod_qty] [decimal](38, 10) NULL,
[ref_no] [varchar](255) NULL,
[room_code] [varchar](255) NULL,
[start_date_time] [datetime2](7) NULL,
[status] [varchar](255) NULL,
[supplier] [nvarchar](255) NULL,
[supplier_prod_code] [varchar](255) NULL,
[sys_order_no] [varchar](255) NOT NULL,
[user_id] [bigint] NULL,
[weight] [decimal](38, 10) NULL,
[weight_plan_qty] [decimal](38, 10) NULL,
[trans_no] [nvarchar](30) NULL,
[master_carrier_id] [nvarchar](24) NULL,
[receive_date_time] [datetime2](7) NULL,
[docInbound] [varbinary](255) NULL,
[sub_carrier_id] [nvarchar](24) NULL,
[doc_status] [varchar](255) NULL,
[cmd_source] [varchar](255) NULL,
[prod_ident_code] [varchar](255) NULL,
[prod_good_date] [varchar](10) NULL,
[row_seq] [int] NULL,
CONSTRAINT [PK__his_doc_inbo__3213E83F9D86D3FC] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object: Table [dbo].[his_doc_inbound_detail_desc] Script Date: 2024/10/23 NHS 03:58:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[his_doc_inbound_detail_desc](
[id] [bigint] IDENTITY(1,1) NOT NULL,
[create_time] [datetime2](7) NULL,
[creator] [varchar](255) NULL,
[update_time] [datetime2](7) NULL,
[updater] [varchar](255) NULL,
[EXP01] [nvarchar](max) NULL,
[EXP02] [nvarchar](max) NULL,
[EXP03] [nvarchar](max) NULL,
[EXP04] [nvarchar](max) NULL,
[EXP05] [nvarchar](max) NULL,
[EXP06] [nvarchar](max) NULL,
[EXP07] [nvarchar](max) NULL,
[EXP08] [nvarchar](max) NULL,
[EXP09] [nvarchar](max) NULL,
[EXP10] [nvarchar](max) NULL,
[EXP11] [nvarchar](max) NULL,
[EXP12] [nvarchar](max) NULL,
[EXP13] [nvarchar](max) NULL,
[EXP14] [nvarchar](max) NULL,
[EXP15] [nvarchar](max) NULL,
[EXP16] [nvarchar](max) NULL,
[EXP17] [nvarchar](max) NULL,
[EXP18] [nvarchar](max) NULL,
[EXP19] [nvarchar](max) NULL,
[EXP20] [nvarchar](max) NULL,
[detail_id] [nvarchar](1024) NULL,
[parent_id] [bigint] NOT NULL,
PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object: Table [dbo].[his_doc_outbound] Script Date: 2024/10/23 NHS 03:58:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[his_doc_outbound](
[id] [bigint] IDENTITY(1,1) NOT NULL,
[create_time] [datetime2](7) NULL,
[creator] [varchar](255) NULL,
[update_time] [datetime2](7) NULL,
[updater] [varchar](255) NULL,
[allocate_date] [varchar](10) NULL,
[assign_workStation_id] [varchar](255) NULL,
[status] [int] NOT NULL,
[his_] [nvarchar](10) NULL,
[end_date_time] [datetime2](7) NULL,
[memo] [nvarchar](255) NULL,
[op_user_ids] [nvarchar](max) NULL,
[outbound_date] [varchar](10) NULL,
[prod_code] [varchar](255) NULL,
[receive_date_time] [datetime2](7) NULL,
[ref_no] [varchar](255) NULL,
[start_date_time] [datetime2](7) NULL,
[supplier] [nvarchar](255) NULL,
[sys_order_no] [varchar](255) NOT NULL,
[total_difference_qty] [decimal](38, 10) NULL,
[total_plan_qty] [decimal](38, 10) NOT NULL,
[total_prod_qty] [decimal](38, 10) NOT NULL,
[user_id] [bigint] NULL,
[trans_no] [nvarchar](30) NULL,
[arr_carrier_count] [int] NULL,
[trans_date_time] [datetime2](7) NULL,
[trans_type] [nvarchar](400) NULL,
[receive_source] [nvarchar](50) NULL,
[carrier_id] [varchar](255) NULL,
[cmd_source] [varchar](255) NULL,
[sub_carrier_id] [nvarchar](24) NULL,
PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
CONSTRAINT [his_doc_outbound_sys_order_no] UNIQUE NONCLUSTERED
(
[sys_order_no] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
CONSTRAINT [UK_qyyd7p38q8hpi6sdk9xhfcydx] UNIQUE NONCLUSTERED
(
[sys_order_no] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object: Table [dbo].[his_doc_outbound_detail] Script Date: 2024/10/23 NHS 03:58:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[his_doc_outbound_detail](
[id] [bigint] IDENTITY(1,1) NOT NULL,
[create_time] [datetime2](7) NULL,
[creator] [varchar](255) NULL,
[update_time] [datetime2](7) NULL,
[updater] [varchar](255) NULL,
[carrier_id] [varchar](255) NULL,
[difference_qty] [decimal](38, 10) NULL,
[is_finished] [bit] NOT NULL,
[is_replenish] [bit] NULL,
[location_code] [varchar](255) NULL,
[op_user_ids] [nvarchar](max) NULL,
[parent_id] [bigint] NOT NULL,
[plan_qty] [decimal](38, 10) NULL,
[prod_code] [varchar](255) NOT NULL,
[prod_outbound_date] [varchar](255) NULL,
[prod_qty] [decimal](38, 10) NULL,
[room_code] [varchar](255) NULL,
[status] [varchar](255) NULL,
[stock_qty] [decimal](38, 10) NULL,
[sys_order_no] [varchar](255) NOT NULL,
[user_id] [bigint] NULL,
[weight] [decimal](38, 10) NULL,
[end_date_time] [datetime2](7) NULL,
[start_date_time] [datetime2](7) NULL,
[ref_no] [varchar](255) NULL,
[trans_no] [nvarchar](255) NULL,
[memo] [nvarchar](255) NULL,
[cmd_source] [varchar](255) NULL,
[doc_status] [int] NULL,
[sub_carrier_id] [nvarchar](24) NULL,
[prod_ident_code] [varchar](255) NULL,
PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object: Table [dbo].[his_doc_outbound_detail_desc] Script Date: 2024/10/23 NHS 03:58:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[his_doc_outbound_detail_desc](
[id] [bigint] IDENTITY(1,1) NOT NULL,
[create_time] [datetime2](7) NULL,
[creator] [varchar](255) NULL,
[update_time] [datetime2](7) NULL,
[updater] [varchar](255) NULL,
[detail_id] [nvarchar](1024) NULL,
[EXP01] [nvarchar](max) NULL,
[EXP02] [nvarchar](max) NULL,
[EXP03] [nvarchar](max) NULL,
[EXP04] [nvarchar](max) NULL,
[EXP05] [nvarchar](max) NULL,
[EXP06] [nvarchar](max) NULL,
[EXP07] [nvarchar](max) NULL,
[EXP08] [nvarchar](max) NULL,
[EXP09] [nvarchar](max) NULL,
[EXP10] [nvarchar](max) NULL,
[EXP11] [nvarchar](max) NULL,
[EXP12] [nvarchar](max) NULL,
[EXP13] [nvarchar](max) NULL,
[EXP14] [nvarchar](max) NULL,
[EXP15] [nvarchar](max) NULL,
[EXP16] [nvarchar](max) NULL,
[EXP17] [nvarchar](max) NULL,
[EXP18] [nvarchar](max) NULL,
[EXP19] [nvarchar](max) NULL,
[EXP20] [nvarchar](max) NULL,
[parent_id] [bigint] NOT NULL,
PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[his_doc_inbound] ADD CONSTRAINT [DF__his_doc_inbou__total__18B6AB08] DEFAULT ('0') FOR [total_difference_qty]
GO
ALTER TABLE [dbo].[his_doc_inbound] ADD CONSTRAINT [DF__his_doc_inbou__total__79FD19BE] DEFAULT ('0') FOR [total_plan_qty]
GO
ALTER TABLE [dbo].[his_doc_inbound] ADD CONSTRAINT [DF__his_doc_inbou__total__7AF13DF7] DEFAULT ('0') FOR [total_prod_qty]
GO
ALTER TABLE [dbo].[his_doc_inbound_detail] ADD CONSTRAINT [DF__his_doc_inbou__diffe__1B9317B3] DEFAULT ('0') FOR [difference_qty]
GO
ALTER TABLE [dbo].[his_doc_inbound_detail] ADD CONSTRAINT [DF__his_doc_inbou__weigh__1C873BEC] DEFAULT ('0') FOR [weight]
GO
ALTER TABLE [dbo].[his_doc_outbound] ADD DEFAULT ('0') FOR [total_difference_qty]
GO
ALTER TABLE [dbo].[his_doc_outbound] ADD DEFAULT ('0') FOR [total_plan_qty]
GO
ALTER TABLE [dbo].[his_doc_outbound] ADD DEFAULT ('0') FOR [total_prod_qty]
GO
ALTER TABLE [dbo].[his_doc_outbound_detail] ADD DEFAULT ('0') FOR [difference_qty]
GO
ALTER TABLE [dbo].[his_doc_outbound_detail] ADD DEFAULT ('0') FOR [weight]
GO
ALTER TABLE [dbo].[his_doc_inbound_detail] WITH CHECK ADD CONSTRAINT [FK_HISqnojq0h1a87rgfpl7e0ae09r] FOREIGN KEY([parent_id])
REFERENCES [dbo].[his_doc_inbound] ([id])
GO
ALTER TABLE [dbo].[his_doc_inbound_detail] CHECK CONSTRAINT [FK_HISqnojq0h1a87rgfpl7e0ae09r]
GO
    (1-1/1)