工程数据管理（T_ENG_BOM 物料清单）

--建表脚本--

create table T_ENG_BOM(

FID int not null  comment '主键'

,FMASTERID int default null  comment '组织控制字段'

,FNUMBER nvarchar(80) default null  comment 'bom编号'

,FBILLTYPE varchar(36) default null  comment '单据类型'

,FDOCUMENTSTATUS char(1) default null  comment '数据状态'

,FBOMUSE int not null  comment 'bom用途'

,FBOMCATEGORY int not null  comment 'bom大类'

,FMATERIALID int default null  comment '父项物料编码'

,FUNITID int default null  comment '单位'

,FYIELDRATE decimal(23,10) default null  comment '成品率'

,FISDEFAULT char(1) not null  comment '默认使用bom'

,FAUTHGROUPID int default null  comment '授权组'

,FFORBIDSTATUS char(1) default null  comment '禁用状态'

,FCREATEORGID int default null  comment '创建组织'

,FUSEORGID int default null  comment '使用组织'

,FCREATORID int default null  comment '创建人'

,FCREATEDATE datetime default null  comment '创建日期'

,FMODIFIERID int default null  comment '修改人'

,FMODIFYDATE datetime default null  comment '修改日期'

,FAPPROVERID int default null  comment '审核人'

,FAPPROVEDATE datetime default null  comment '审核日期'

,FFORBIDDERID int default null  comment '禁用人'

,FFORBIDDATE datetime default null  comment '禁用日期'

,FBASEUNITID int default null  comment '父项基本单位'

,FQTY decimal(23,10) not null  comment '单位用量'

,FBASEQTY decimal(23,10) not null  comment '基本单位用量'

,FCFGBOMID int default null  comment '配置bom内码'

,FGROUP int default null  comment 'bom组别'

,FPLMBOMID nvarchar(50) default null  comment 'plmbom内码'

,FBOMSRC char(1) not null  comment 'bom来源'

,FAUXPROPID int not null  comment '父项辅助属性'

,primary key (FID)

) comment = '物料清单'

--查询--

select FID as "fid",FMASTERID as "fmasterid",FNUMBER as "fnumber",FBILLTYPE as "fbilltype",FDOCUMENTSTATUS as "fdocumentstatus",FBOMUSE as "fbomuse",FBOMCATEGORY as "fbomcategory",FMATERIALID as "fmaterialid",FUNITID as "funitid",FYIELDRATE as "fyieldrate",FISDEFAULT as "fisdefault",FAUTHGROUPID as "fauthgroupid",FFORBIDSTATUS as "fforbidstatus",FCREATEORGID as "fcreateorgid",FUSEORGID as "fuseorgid",FCREATORID as "fcreatorid",FCREATEDATE as "fcreatedate",FMODIFIERID as "fmodifierid",FMODIFYDATE as "fmodifydate",FAPPROVERID as "fapproverid",FAPPROVEDATE as "fapprovedate",FFORBIDDERID as "fforbidderid",FFORBIDDATE as "fforbiddate",FBASEUNITID as "fbaseunitid",FQTY as "fqty",FBASEQTY as "fbaseqty",FCFGBOMID as "fcfgbomid",FGROUP as "fgroup",FPLMBOMID as "fplmbomid",FBOMSRC as "fbomsrc",FAUXPROPID as "fauxpropid" from T_ENG_BOM

--查询(中文字段)--

select FID as "主键",FMASTERID as "组织控制字段",FNUMBER as "bom编号",FBILLTYPE as "单据类型",FDOCUMENTSTATUS as "数据状态",FBOMUSE as "bom用途",FBOMCATEGORY as "bom大类",FMATERIALID as "父项物料编码",FUNITID as "单位",FYIELDRATE as "成品率",FISDEFAULT as "默认使用bom",FAUTHGROUPID as "授权组",FFORBIDSTATUS as "禁用状态",FCREATEORGID as "创建组织",FUSEORGID as "使用组织",FCREATORID as "创建人",FCREATEDATE as "创建日期",FMODIFIERID as "修改人",FMODIFYDATE as "修改日期",FAPPROVERID as "审核人",FAPPROVEDATE as "审核日期",FFORBIDDERID as "禁用人",FFORBIDDATE as "禁用日期",FBASEUNITID as "父项基本单位",FQTY as "单位用量",FBASEQTY as "基本单位用量",FCFGBOMID as "配置bom内码",FGROUP as "bom组别",FPLMBOMID as "plmbom内码",FBOMSRC as "bom来源",FAUXPROPID as "父项辅助属性" from T_ENG_BOM

--INSERT脚本--

insert into T_ENG_BOM(FID,FMASTERID,FNUMBER,FBILLTYPE,FDOCUMENTSTATUS,FBOMUSE,FBOMCATEGORY,FMATERIALID,FUNITID,FYIELDRATE,FISDEFAULT,FAUTHGROUPID,FFORBIDSTATUS,FCREATEORGID,FUSEORGID,FCREATORID,FCREATEDATE,FMODIFIERID,FMODIFYDATE,FAPPROVERID,FAPPROVEDATE,FFORBIDDERID,FFORBIDDATE,FBASEUNITID,FQTY,FBASEQTY,FCFGBOMID,FGROUP,FPLMBOMID,FBOMSRC,FAUXPROPID) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)

--UPDATE脚本--

update T_ENG_BOM set FID=?,FMASTERID=?,FNUMBER=?,FBILLTYPE=?,FDOCUMENTSTATUS=?,FBOMUSE=?,FBOMCATEGORY=?,FMATERIALID=?,FUNITID=?,FYIELDRATE=?,FISDEFAULT=?,FAUTHGROUPID=?,FFORBIDSTATUS=?,FCREATEORGID=?,FUSEORGID=?,FCREATORID=?,FCREATEDATE=?,FMODIFIERID=?,FMODIFYDATE=?,FAPPROVERID=?,FAPPROVEDATE=?,FFORBIDDERID=?,FFORBIDDATE=?,FBASEUNITID=?,FQTY=?,FBASEQTY=?,FCFGBOMID=?,FGROUP=?,FPLMBOMID=?,FBOMSRC=?,FAUXPROPID=? where FID=?

--delete脚本--

delete from T_ENG_BOM where FID=?

--给字段加备注--

alter table T_ENG_BOM comment '物料清单';

alter table T_ENG_BOM modify column FID int not null  comment '主键';

alter table T_ENG_BOM modify column FMASTERID int default null  comment '组织控制字段';

alter table T_ENG_BOM modify column FNUMBER nvarchar(80) default null  comment 'bom编号';

alter table T_ENG_BOM modify column FBILLTYPE varchar(36) default null  comment '单据类型';

alter table T_ENG_BOM modify column FDOCUMENTSTATUS char(1) default null  comment '数据状态';

alter table T_ENG_BOM modify column FBOMUSE int not null  comment 'bom用途';

alter table T_ENG_BOM modify column FBOMCATEGORY int not null  comment 'bom大类';

alter table T_ENG_BOM modify column FMATERIALID int default null  comment '父项物料编码';

alter table T_ENG_BOM modify column FUNITID int default null  comment '单位';

alter table T_ENG_BOM modify column FYIELDRATE decimal(23,10) default null  comment '成品率';

alter table T_ENG_BOM modify column FISDEFAULT char(1) not null  comment '默认使用bom';

alter table T_ENG_BOM modify column FAUTHGROUPID int default null  comment '授权组';

alter table T_ENG_BOM modify column FFORBIDSTATUS char(1) default null  comment '禁用状态';

alter table T_ENG_BOM modify column FCREATEORGID int default null  comment '创建组织';

alter table T_ENG_BOM modify column FUSEORGID int default null  comment '使用组织';

alter table T_ENG_BOM modify column FCREATORID int default null  comment '创建人';

alter table T_ENG_BOM modify column FCREATEDATE datetime default null  comment '创建日期';

alter table T_ENG_BOM modify column FMODIFIERID int default null  comment '修改人';

alter table T_ENG_BOM modify column FMODIFYDATE datetime default null  comment '修改日期';

alter table T_ENG_BOM modify column FAPPROVERID int default null  comment '审核人';

alter table T_ENG_BOM modify column FAPPROVEDATE datetime default null  comment '审核日期';

alter table T_ENG_BOM modify column FFORBIDDERID int default null  comment '禁用人';

alter table T_ENG_BOM modify column FFORBIDDATE datetime default null  comment '禁用日期';

alter table T_ENG_BOM modify column FBASEUNITID int default null  comment '父项基本单位';

alter table T_ENG_BOM modify column FQTY decimal(23,10) not null  comment '单位用量';

alter table T_ENG_BOM modify column FBASEQTY decimal(23,10) not null  comment '基本单位用量';

alter table T_ENG_BOM modify column FCFGBOMID int default null  comment '配置bom内码';

alter table T_ENG_BOM modify column FGROUP int default null  comment 'bom组别';

alter table T_ENG_BOM modify column FPLMBOMID nvarchar(50) default null  comment 'plmbom内码';

alter table T_ENG_BOM modify column FBOMSRC char(1) not null  comment 'bom来源';

alter table T_ENG_BOM modify column FAUXPROPID int not null  comment '父项辅助属性';