Files

136 lines
9.8 KiB
SQL
Executable File

/* 脚本导出时间:2026/08/18 14:45:46 */
/* 脚本导出人员:系统管理员 */
/* 脚本 说明:T_BD_BOMENTRY BOM子集物料*/
-- DROP TABLE T_BD_BOMENTRY
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'T_BD_BOMENTRY') AND type in (N'U'))
BEGIN
CREATE TABLE T_BD_BOMENTRY(
FREPLACEGROUP INT NOT NULL DEFAULT 0 , -- 项次
FMATERIALID VARCHAR(50) NOT NULL DEFAULT '' , -- 子项物料
FMATERIALTYPE VARCHAR(1) NOT NULL DEFAULT 1 , -- 子项类型
FUNITID VARCHAR(50) NOT NULL DEFAULT '' , -- 子项单位
FSTOCKID VARCHAR(50) NOT NULL DEFAULT '' , -- 仓库
FSTOCKLOCID VARCHAR(50) NOT NULL DEFAULT '' , -- 仓位
FNUMERATOR DECIMAL(24,6) NOT NULL DEFAULT 0 , -- 分子
FOPERID VARCHAR(50) NOT NULL DEFAULT '' , -- 工序号
FDENOMINATOR DECIMAL(24,6) NOT NULL DEFAULT 0 , -- 分母
FBEGINDAY DATE NULL , -- 生效日期
FENDDAY DATE NULL , -- 失效日期
FSCRAPRATE DECIMAL(24,6) NOT NULL DEFAULT 0 , -- 变动损耗率%
FFIXSCRAPQTY DECIMAL(24,6) NOT NULL DEFAULT 0 , -- 固定损损耗
FISSKIP BIT NOT NULL DEFAULT 0 , -- 跳层
FPOSITIONNO NVARCHAR(50) NOT NULL DEFAULT '' , -- 位置号
FMACHINEPOS VARCHAR(50) NOT NULL DEFAULT '' , -- 工位
FOFFSETDAY DECIMAL(24,6) NOT NULL DEFAULT 0 , -- 提前期偏置
FBACKFLUSH BIT NOT NULL DEFAULT 0 , -- 倒冲
FISKEYITEM BIT NOT NULL DEFAULT 0 , -- 关键件
FISFEED BIT NOT NULL DEFAULT 0 , -- 不投料
FNOTE VARCHAR(50) NOT NULL DEFAULT '' , -- 备注
FCHECKERID VARCHAR(50) NOT NULL DEFAULT '' , -- 审核人
FCHECKDATE DATETIME NULL , -- 审核日期
FDISABLEID VARCHAR(50) NOT NULL DEFAULT '' , -- 禁用人
FDISABLEDATE DATETIME NULL , -- 禁用日期
FAUXPROPID VARCHAR(50) NOT NULL DEFAULT '' , -- 辅助属性
FFEEDSN BIT NOT NULL DEFAULT 0 , -- 启用序列号
FOTHERSN BIT NOT NULL DEFAULT 0 , -- 启用第三方序列号
FINTERID VARCHAR(50) NOT NULL DEFAULT '', -- 表内码
FENTRYID INT NOT NULL DEFAULT 0 , -- 行号
FDETAILID VARCHAR(50) NOT NULL DEFAULT '' , --表体内码
FSTATUS INT NOT NULL DEFAULT 0 , -- 状态
FDELETED BIT NOT NULL DEFAULT 0 , --禁用状态 0 = 正常 1 = 禁用
FCOMPANYID VARCHAR(50) NOT NULL DEFAULT '', -- 组织代码
C_YMD DATETIME NULL, -- 资料创建日期
C_USER VARCHAR(20) NOT NULL DEFAULT '', -- 资料创建帐号
M_YMD DATETIME NULL, -- 最后异动日期
M_USER VARCHAR(20) NOT NULL DEFAULT '', -- 最后异动帐号
FBOMID VARCHAR(50) NOT NULL DEFAULT '' , -- 子项BOM版本
UID VARCHAR(50) NOT NULL CONSTRAINT [PK_T_BD_BOMENTRY] PRIMARY KEY NONCLUSTERED
)
END
ELSE
BEGIN
if(not exists(select name from SysColumns where name ='FREPLACEGROUP' and id= OBJECT_ID('T_BD_BOMENTRY')))
Alter Table T_BD_BOMENTRY add FREPLACEGROUP INT NOT NULL DEFAULT 0 -- 项次
if(not exists(select name from SysColumns where name ='FMATERIALID' and id= OBJECT_ID('T_BD_BOMENTRY')))
Alter Table T_BD_BOMENTRY add FMATERIALID VARCHAR(50) NOT NULL DEFAULT '' -- 子项物料
if(not exists(select name from SysColumns where name ='FMATERIALTYPE' and id= OBJECT_ID('T_BD_BOMENTRY')))
Alter Table T_BD_BOMENTRY add FMATERIALTYPE VARCHAR(1) NOT NULL DEFAULT 1 -- 子项类型
if(not exists(select name from SysColumns where name ='FUNITID' and id= OBJECT_ID('T_BD_BOMENTRY')))
Alter Table T_BD_BOMENTRY add FUNITID VARCHAR(50) NOT NULL DEFAULT '' -- 子项单位
if(not exists(select name from SysColumns where name ='FSTOCKID' and id= OBJECT_ID('T_BD_BOMENTRY')))
Alter Table T_BD_BOMENTRY add FSTOCKID VARCHAR(50) NOT NULL DEFAULT '' -- 仓库
if(not exists(select name from SysColumns where name ='FSTOCKLOCID' and id= OBJECT_ID('T_BD_BOMENTRY')))
Alter Table T_BD_BOMENTRY add FSTOCKLOCID VARCHAR(50) NOT NULL DEFAULT '' -- 仓位
if(not exists(select name from SysColumns where name ='FNUMERATOR' and id= OBJECT_ID('T_BD_BOMENTRY')))
Alter Table T_BD_BOMENTRY add FNUMERATOR DECIMAL(24,6) NOT NULL DEFAULT 0 -- 分子
if(not exists(select name from SysColumns where name ='FOPERID' and id= OBJECT_ID('T_BD_BOMENTRY')))
Alter Table T_BD_BOMENTRY add FOPERID VARCHAR(50) NOT NULL DEFAULT '' -- 工序号
if(not exists(select name from SysColumns where name ='FDENOMINATOR' and id= OBJECT_ID('T_BD_BOMENTRY')))
Alter Table T_BD_BOMENTRY add FDENOMINATOR DECIMAL(24,6) NOT NULL DEFAULT 0 -- 分母
if(not exists(select name from SysColumns where name ='FBEGINDAY' and id= OBJECT_ID('T_BD_BOMENTRY')))
Alter Table T_BD_BOMENTRY add FBEGINDAY DATE NULL -- 生效日期
if(not exists(select name from SysColumns where name ='FENDDAY' and id= OBJECT_ID('T_BD_BOMENTRY')))
Alter Table T_BD_BOMENTRY add FENDDAY DATE NULL -- 失效日期
if(not exists(select name from SysColumns where name ='FSCRAPRATE' and id= OBJECT_ID('T_BD_BOMENTRY')))
Alter Table T_BD_BOMENTRY add FSCRAPRATE DECIMAL(24,6) NOT NULL DEFAULT 0 -- 变动损耗率%
if(not exists(select name from SysColumns where name ='FFIXSCRAPQTY' and id= OBJECT_ID('T_BD_BOMENTRY')))
Alter Table T_BD_BOMENTRY add FFIXSCRAPQTY DECIMAL(24,6) NOT NULL DEFAULT 0 -- 固定损损耗
if(not exists(select name from SysColumns where name ='FISSKIP' and id= OBJECT_ID('T_BD_BOMENTRY')))
Alter Table T_BD_BOMENTRY add FISSKIP BIT NOT NULL DEFAULT 0 -- 跳层
if(not exists(select name from SysColumns where name ='FPOSITIONNO' and id= OBJECT_ID('T_BD_BOMENTRY')))
Alter Table T_BD_BOMENTRY add FPOSITIONNO NVARCHAR(50) NOT NULL DEFAULT '' -- 位置号
if(not exists(select name from SysColumns where name ='FMACHINEPOS' and id= OBJECT_ID('T_BD_BOMENTRY')))
Alter Table T_BD_BOMENTRY add FMACHINEPOS VARCHAR(50) NOT NULL DEFAULT '' -- 工位
if(not exists(select name from SysColumns where name ='FOFFSETDAY' and id= OBJECT_ID('T_BD_BOMENTRY')))
Alter Table T_BD_BOMENTRY add FOFFSETDAY DECIMAL(24,6) NOT NULL DEFAULT 0 -- 提前期偏置
if(not exists(select name from SysColumns where name ='FBACKFLUSH' and id= OBJECT_ID('T_BD_BOMENTRY')))
Alter Table T_BD_BOMENTRY add FBACKFLUSH BIT NOT NULL DEFAULT 0 -- 倒冲
if(not exists(select name from SysColumns where name ='FISKEYITEM' and id= OBJECT_ID('T_BD_BOMENTRY')))
Alter Table T_BD_BOMENTRY add FISKEYITEM BIT NOT NULL DEFAULT 0 -- 关键件
if(not exists(select name from SysColumns where name ='FISFEED' and id= OBJECT_ID('T_BD_BOMENTRY')))
Alter Table T_BD_BOMENTRY add FISFEED BIT NOT NULL DEFAULT 0 -- 不投料
if(not exists(select name from SysColumns where name ='FNOTE' and id= OBJECT_ID('T_BD_BOMENTRY')))
Alter Table T_BD_BOMENTRY add FNOTE VARCHAR(50) NOT NULL DEFAULT '' -- 备注
if(not exists(select name from SysColumns where name ='FCHECKERID' and id= OBJECT_ID('T_BD_BOMENTRY')))
Alter Table T_BD_BOMENTRY add FCHECKERID VARCHAR(50) NOT NULL DEFAULT '' -- 审核人
if(not exists(select name from SysColumns where name ='FCHECKDATE' and id= OBJECT_ID('T_BD_BOMENTRY')))
Alter Table T_BD_BOMENTRY add FCHECKDATE DATETIME NULL -- 审核日期
if(not exists(select name from SysColumns where name ='FDISABLEID' and id= OBJECT_ID('T_BD_BOMENTRY')))
Alter Table T_BD_BOMENTRY add FDISABLEID VARCHAR(50) NOT NULL DEFAULT '' -- 禁用人
if(not exists(select name from SysColumns where name ='FDISABLEDATE' and id= OBJECT_ID('T_BD_BOMENTRY')))
Alter Table T_BD_BOMENTRY add FDISABLEDATE DATETIME NULL -- 禁用日期
if(not exists(select name from SysColumns where name ='FAUXPROPID' and id= OBJECT_ID('T_BD_BOMENTRY')))
Alter Table T_BD_BOMENTRY add FAUXPROPID VARCHAR(50) NOT NULL DEFAULT '' -- 辅助属性
if(not exists(select name from SysColumns where name ='FFEEDSN' and id= OBJECT_ID('T_BD_BOMENTRY')))
Alter Table T_BD_BOMENTRY add FFEEDSN BIT NOT NULL DEFAULT 0 -- 启用序列号
if(not exists(select name from SysColumns where name ='FOTHERSN' and id= OBJECT_ID('T_BD_BOMENTRY')))
Alter Table T_BD_BOMENTRY add FOTHERSN BIT NOT NULL DEFAULT 0 -- 启用第三方序列号
if(not exists(select name from SysColumns where name ='FCOMPANYID' and id= OBJECT_ID('T_BD_BOMENTRY')))
Alter Table T_BD_BOMENTRY add FCOMPANYID VARCHAR(50) NOT NULL DEFAULT '' -- 组织代码
if(not exists(select name from SysColumns where name ='FINTERID' and id= OBJECT_ID('T_BD_BOMENTRY')))
Alter Table T_BD_BOMENTRY add FINTERID VARCHAR(50) NOT NULL DEFAULT '' -- 表内码
if(not exists(select name from SysColumns where name ='FDETAILID' and id= OBJECT_ID('T_BD_BOMENTRY')))
Alter Table T_BD_BOMENTRY add FDETAILID VARCHAR(50) NOT NULL DEFAULT '' -- 表体内码
if(not exists(select name from SysColumns where name ='FENTRYID' and id= OBJECT_ID('T_BD_BOMENTRY')))
Alter Table T_BD_BOMENTRY add FENTRYID INT NOT NULL DEFAULT 0 -- 行号
END
GO
--
IF EXISTS (SELECT * FROM sysindexes WHERE id = OBJECT_ID(N'T_BD_BOMENTRY') AND name = N'T_BD_BOMENTRY_1' )
Drop Index T_BD_BOMENTRY.T_BD_BOMENTRY_1
GO
Create unique index T_BD_BOMENTRY_1 on T_BD_BOMENTRY(FDETAILID)
GO
--
IF EXISTS (SELECT * FROM sysindexes WHERE id = OBJECT_ID(N'T_BD_BOMENTRY') AND name = N'T_BD_BOMENTRY_2' )
Drop Index T_BD_BOMENTRY.T_BD_BOMENTRY_2
GO
Create index T_BD_BOMENTRY_2 on T_BD_BOMENTRY(FINTERID)
GO
GO