create database Barcodeuse Barcode /*用戶從條碼讀取的領料單頭記錄 EL_PickListTable :表名 RecId :自動增長類型的主鍵 UploadStatus:上載狀態 RefId :單號 PickListNum:領料預算表號碼 BarcodeUniqueId:barcode號碼 ToWarehouse :至倉庫 ToLocation :至位置 TransId 上載時的transId（交易號） CreatedBy 建立者 CreateDate 創建日--期 */create table EL_PickListTable( RecId bigint identity(1,1) primary key, UploadStatus int , UploadDate Datetime, RefId nvarchar(20), PickListNum nvarchar(20), BarcodeUniqueId nvarchar(50), ToWarehouse nvarchar(10), ToLocation nvarchar(10), TransId nvarchar(20), CreateBy nvarchar(20), CreateDate Datetime ) /*用戶從條碼中讀取的領料單行記錄EL_PickListLine :表名 RecId :主鍵自動增長類型 ItemId :物料編號 InputUnit:單位 PickListNum:領料預算表號碼 PickListTableRecId:領料單頭Recid FrmBatchId :由批號 ToBatchId :至批號 RefId :單號 ExFactoryNo :出廠單號 FrmProjId : 由項目 FrmSKU : 由SKU ToProjId :至項目 ToSKUrt : 至ＳＫＵ BarcodeUniqueId:barcode號碼 FrmWarehouse:由倉庫 FrmLocation :由位置 ToWarehouse :至倉庫 InputWeight :總重量 LevelQty :對淮數 Remarks :備註 ContainerType:箱類型 ContainerQty 箱數量 MouldId :洋行模號 MouldVersion:模版本 DomainId :用戶現時登陸的Domain DataareaId :以用戶登陸時的Domain取當前的Dataareaid*/create table EL_PickListLine( RecId bigint identity(1,1) primary key, ItemId nvarchar(15), InputQty decimal(28,12), InputUnit decimal(28,12), PickListNum nvarchar(20), PickListTableRecId bigint, FrmBatchId nvarchar(20), ToBatchId nvarchar(20), RefId nvarchar(20), ExFactoryNo nvarchar(30), FrmProjId nvarchar(15), FrmSKU nvarchar(20), ToProjId nvarchar(15), ToSKU nvarchar(20), BarcodeUniqueId nvarchar(50), FrmWarehouse nvarchar(10), FrmLocation nvarchar(10), ToWarehouse nvarchar(10), ToLocation nvarchar(10), InputWeight decimal(28,12), LevelQty decimal(28,12), Remarks nvarchar(1000), ContainerType nvarchar(50), ContainerQty int , MouldId nvarchar(4), MouldVersion nvarchar(4), DomainId nvarchar(10), DataareaId nvarchar(4))/*EL_PickListUserSetting:表名UserId:ELBase用戶號碼FrmWarehouse:由倉庫FrmLocation:由位置*/create table EL_PickListUserSetting( UserId nvarchar(50) primary key, FrmWarehouse nvarchar(10), FrmLocation nvarchar(10))--一個裱中可以有多個字段組成的主鍵 create table EL_TransIdTable( TableName nvarchar(50) , LastTransId nvarchar(15), Prefix nchar(5), DomainId nvarchar(10) primary key(TableName,DomainId))--初始化裱中的數據insert into EL_TransIdTable values(' ppcPickList','20000','GPLIT','SG')insert into EL_TransIdTable values(' ppcPickList','20000','PLIT','PW')/*ppcPickList :表名（上載到AX領料資料記錄） RefId :領料單頭單號PickListNum :領料單行領料預算表號碼 ItemId :無料編號FrmLocation :由倉庫FrmProjId :由位置 FrmSKU :由SKUFrmBatchId :由批號ToWarehouse :至倉庫ToLocation :至位置 ToProjId :至項目 ToSKU :至SKUToBatchId :至批號InputQty :總數量CreatedBy :領料單頭創建者CreatedDate :交易創建日期 TransId :交易號碼 DeviceID:用戶機名 LineRefId:領料單行單號LinePickListNum:領料單頭領料預算表號碼ExFactoryNo :出廠單號LevelQty :對淮數InputWeight :總重量 Remarks :備註ContainerType :箱類型ContainerQty :箱數量MouldId :洋行模號MouldRefNum :模廠模號DataAreaId :領料單行DataareaIdRecId :領料單行RecId*/create table ppcPickList( RefId nvarchar(20), PickListNum nvarchar(20), ItemId nvarchar(15), FrmWarehouse nvarchar(10), FrmLocation nvarchar(10), FrmProjId nvarchar(15), FrmSKU nvarchar(20), FrmBatchId nvarchar(20), ToWarehouse nvarchar(10), ToLocation nvarchar(10), ToProjId nvarchar(15), ToSKU nvarchar(20), ToBatchId nvarchar(20), InputQty numeric(28,12), InputUnit nvarchar(10), CreatedBy nvarchar(70), CreatedDate datetime, TransId nvarchar(20),--交易號碼 select Prefix+(LastTransId+1) from EL_TransIdTable where tablename=“ppcPickList” and DomainId="現時Domain" DeviceID nvarchar(50), LineRefId nvarchar(20), LinePickListNum nvarchar(50), ExFactoryNo nvarchar(30), LevelQty numeric(28,12), InputWeight numeric(28,12), Remarks nvarchar(1000), ContainerType nvarchar(50), ContainerQty numeric(28,12), MouldId nvarchar(20), MouldRefNum nvarchar(20), MouldVersion int, DataAreaId nvarchar(4), RecId bigint)/*(需要上載到AX的領料單 上載完成后，記錄會在此裱中刪除)ppcPickListLogin :表名 TransId :交易號碼 Username : ELBase用戶號碼 Password ErrorMessage*/create table ppcPickListLogin( TransId nvarchar(20) , UserName nvarchar(50), Password nvarchar(50), ErrorMessage nvarchar(100))/*EL_ContainerType : 表名(箱類型主檔) Seq :箱類型序號 ContainerType :箱類型 Description :描述*/create table EL_ContainerType( Seq int primary key, ContainerType nvarchar(50), Description nvarchar(50))--測試表 一表中存在多個字段組成的主鍵drop table testcreate table test( id int , name nvarchar(30), --age int primary key(id,age) , age int, address nvarchar(30),)-- alter table test add constraint pk_testid primary key(id,age)
