工程数据管理（物料清单子项明细）

--建表脚本--

create table T_ENG_BOMCHILD(

FENTRYID int not null  comment '主键'

,FID int not null  comment '物料清单id'

,FSEQ int default null  comment '序号'

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

,FMATERIALTYPE char(1) default null  comment '子项类型'

,FUNITID int default null  comment '单位'

,FDOSAGETYPE char(1) default null  comment '用量类型'

,FOPERID int default 10  comment '工序'

,FPROCESSID int default null  comment '作业编码'

,FNUMERATOR decimal(23,10) not null  comment '分子'

,FDENOMINATOR decimal(23,10) not null  comment '分母'

,FFIXSCRAPQTY decimal(23,10) not null  comment '固定损耗'

,FSCRAPRATE decimal(23,10) not null  comment '变动损耗率'

,FDISASSMBLERATE decimal(23,10) not null  comment '拆卸成本拆分比例'

,FEFFECTDATE datetime default null  comment '生效日期'

,FEXPIREDATE datetime default null  comment '失效日期'

,FISSUETYPE varchar(1) not null  comment '发料方式'

,FBACKFLUSHTYPE char(1) default null  comment '倒冲时机'

,FSUPPLYORG int default null  comment '发料组织'

,FSTOCKID int default null  comment '默认发料仓库'

,FSTOCKLOCID int default null  comment '默认发料仓位'

,FISGETSCRAP char(1) not null  comment '是否发损耗'

,FALLOWOVER char(1) not null  comment '允许超发'

,FOVERRATE decimal(23,10) not null  comment '超发比例'

,FOWNERTYPEID varchar(36) default null  comment '货主类型'

,FOWNERID int default null  comment '货主'

,FTIMEUNIT int default null  comment '时间单位'

,FOFFSETTIME decimal(23,10) not null  comment '偏置时间'

,FISKEYCOMPONENT char(1) not null  comment '是否为关键件'

,FREPLACEGROUP int not null  comment '项次'

,FAUXPROPID int default 0  comment '辅助属性'

,FPOSITIONNO nvarchar(2000) default null  comment '位置号'

,FBOMID int default null  comment '子项bom编号'

,FGUID varchar(36) not null  comment '子项唯一标示'

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

,FBASENUMERATOR decimal(23,10) not null  comment '基本单位分子'

,FBASEDENOMINATOR decimal(23,10) not null  comment '基本单位分母'

,FBASEFIXSCRAPQTY decimal(23,10) not null  comment '基本单位固定损耗'

,FENTRYROWID nvarchar(50) default null  comment '子项分录行标识'

,FQTY decimal(23,10) default null  comment '标准用量'

,FACTUALQTY decimal(23,10) default null  comment '实际用量'

,FOVERCONTROLMODE char(1) default ''2''  comment '控制类型'

,FChildSupplyOrgId int default 0  comment '供应组织'

,FOptQueue nvarchar(8) default '0'  comment '工序序列'

,FMasterId int not null  comment '分配内码'

,FISMinIssueQty char(1) not null  comment '领料考虑最小发料批量'

,FRECORDDATA nvarchar(500) default null  comment '记录字段'

,primary key (FENTRYID)

) comment = '物料清单子项明细'

--查询--

select FENTRYID as "fentryid",FID as "fid",FSEQ as "fseq",FMATERIALID as "fmaterialid",FMATERIALTYPE as "fmaterialtype",FUNITID as "funitid",FDOSAGETYPE as "fdosagetype",FOPERID as "foperid",FPROCESSID as "fprocessid",FNUMERATOR as "fnumerator",FDENOMINATOR as "fdenominator",FFIXSCRAPQTY as "ffixscrapqty",FSCRAPRATE as "fscraprate",FDISASSMBLERATE as "fdisassmblerate",FEFFECTDATE as "feffectdate",FEXPIREDATE as "fexpiredate",FISSUETYPE as "fissuetype",FBACKFLUSHTYPE as "fbackflushtype",FSUPPLYORG as "fsupplyorg",FSTOCKID as "fstockid",FSTOCKLOCID as "fstocklocid",FISGETSCRAP as "fisgetscrap",FALLOWOVER as "fallowover",FOVERRATE as "foverrate",FOWNERTYPEID as "fownertypeid",FOWNERID as "fownerid",FTIMEUNIT as "ftimeunit",FOFFSETTIME as "foffsettime",FISKEYCOMPONENT as "fiskeycomponent",FREPLACEGROUP as "freplacegroup",FAUXPROPID as "fauxpropid",FPOSITIONNO as "fpositionno",FBOMID as "fbomid",FGUID as "fguid",FBASEUNITID as "fbaseunitid",FBASENUMERATOR as "fbasenumerator",FBASEDENOMINATOR as "fbasedenominator",FBASEFIXSCRAPQTY as "fbasefixscrapqty",FENTRYROWID as "fentryrowid",FQTY as "fqty",FACTUALQTY as "factualqty",FOVERCONTROLMODE as "fovercontrolmode",FChildSupplyOrgId as "fchildsupplyorgid",FOptQueue as "foptqueue",FMasterId as "fmasterid",FISMinIssueQty as "fisminissueqty",FRECORDDATA as "frecorddata" from T_ENG_BOMCHILD

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

select FENTRYID as "主键",FID as "物料清单id",FSEQ as "序号",FMATERIALID as "子项物料编码",FMATERIALTYPE as "子项类型",FUNITID as "单位",FDOSAGETYPE as "用量类型",FOPERID as "工序",FPROCESSID as "作业编码",FNUMERATOR as "分子",FDENOMINATOR as "分母",FFIXSCRAPQTY as "固定损耗",FSCRAPRATE as "变动损耗率",FDISASSMBLERATE as "拆卸成本拆分比例",FEFFECTDATE as "生效日期",FEXPIREDATE as "失效日期",FISSUETYPE as "发料方式",FBACKFLUSHTYPE as "倒冲时机",FSUPPLYORG as "发料组织",FSTOCKID as "默认发料仓库",FSTOCKLOCID as "默认发料仓位",FISGETSCRAP as "是否发损耗",FALLOWOVER as "允许超发",FOVERRATE as "超发比例",FOWNERTYPEID as "货主类型",FOWNERID as "货主",FTIMEUNIT as "时间单位",FOFFSETTIME as "偏置时间",FISKEYCOMPONENT as "是否为关键件",FREPLACEGROUP as "项次",FAUXPROPID as "辅助属性",FPOSITIONNO as "位置号",FBOMID as "子项bom编号",FGUID as "子项唯一标示",FBASEUNITID as "基本单位",FBASENUMERATOR as "基本单位分子",FBASEDENOMINATOR as "基本单位分母",FBASEFIXSCRAPQTY as "基本单位固定损耗",FENTRYROWID as "子项分录行标识",FQTY as "标准用量",FACTUALQTY as "实际用量",FOVERCONTROLMODE as "控制类型",FChildSupplyOrgId as "供应组织",FOptQueue as "工序序列",FMasterId as "分配内码",FISMinIssueQty as "领料考虑最小发料批量",FRECORDDATA as "记录字段" from T_ENG_BOMCHILD

--INSERT脚本--

insert into T_ENG_BOMCHILD(FENTRYID,FID,FSEQ,FMATERIALID,FMATERIALTYPE,FUNITID,FDOSAGETYPE,FOPERID,FPROCESSID,FNUMERATOR,FDENOMINATOR,FFIXSCRAPQTY,FSCRAPRATE,FDISASSMBLERATE,FEFFECTDATE,FEXPIREDATE,FISSUETYPE,FBACKFLUSHTYPE,FSUPPLYORG,FSTOCKID,FSTOCKLOCID,FISGETSCRAP,FALLOWOVER,FOVERRATE,FOWNERTYPEID,FOWNERID,FTIMEUNIT,FOFFSETTIME,FISKEYCOMPONENT,FREPLACEGROUP,FAUXPROPID,FPOSITIONNO,FBOMID,FGUID,FBASEUNITID,FBASENUMERATOR,FBASEDENOMINATOR,FBASEFIXSCRAPQTY,FENTRYROWID,FQTY,FACTUALQTY,FOVERCONTROLMODE,FChildSupplyOrgId,FOptQueue,FMasterId,FISMinIssueQty,FRECORDDATA) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)

--UPDATE脚本--

update T_ENG_BOMCHILD set FENTRYID=?,FID=?,FSEQ=?,FMATERIALID=?,FMATERIALTYPE=?,FUNITID=?,FDOSAGETYPE=?,FOPERID=?,FPROCESSID=?,FNUMERATOR=?,FDENOMINATOR=?,FFIXSCRAPQTY=?,FSCRAPRATE=?,FDISASSMBLERATE=?,FEFFECTDATE=?,FEXPIREDATE=?,FISSUETYPE=?,FBACKFLUSHTYPE=?,FSUPPLYORG=?,FSTOCKID=?,FSTOCKLOCID=?,FISGETSCRAP=?,FALLOWOVER=?,FOVERRATE=?,FOWNERTYPEID=?,FOWNERID=?,FTIMEUNIT=?,FOFFSETTIME=?,FISKEYCOMPONENT=?,FREPLACEGROUP=?,FAUXPROPID=?,FPOSITIONNO=?,FBOMID=?,FGUID=?,FBASEUNITID=?,FBASENUMERATOR=?,FBASEDENOMINATOR=?,FBASEFIXSCRAPQTY=?,FENTRYROWID=?,FQTY=?,FACTUALQTY=?,FOVERCONTROLMODE=?,FChildSupplyOrgId=?,FOptQueue=?,FMasterId=?,FISMinIssueQty=?,FRECORDDATA=? where FENTRYID=?

--delete脚本--

delete from T_ENG_BOMCHILD where FENTRYID=?

--给字段加备注--

alter table T_ENG_BOMCHILD comment '物料清单子项明细';

alter table T_ENG_BOMCHILD modify column FENTRYID int not null  comment '主键';

alter table T_ENG_BOMCHILD modify column FID int not null  comment '物料清单id';

alter table T_ENG_BOMCHILD modify column FSEQ int default null  comment '序号';

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

alter table T_ENG_BOMCHILD modify column FMATERIALTYPE char(1) default null  comment '子项类型';

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

alter table T_ENG_BOMCHILD modify column FDOSAGETYPE char(1) default null  comment '用量类型';

alter table T_ENG_BOMCHILD modify column FOPERID int default 10  comment '工序';

alter table T_ENG_BOMCHILD modify column FPROCESSID int default null  comment '作业编码';

alter table T_ENG_BOMCHILD modify column FNUMERATOR decimal(23,10) not null  comment '分子';

alter table T_ENG_BOMCHILD modify column FDENOMINATOR decimal(23,10) not null  comment '分母';

alter table T_ENG_BOMCHILD modify column FFIXSCRAPQTY decimal(23,10) not null  comment '固定损耗';

alter table T_ENG_BOMCHILD modify column FSCRAPRATE decimal(23,10) not null  comment '变动损耗率';

alter table T_ENG_BOMCHILD modify column FDISASSMBLERATE decimal(23,10) not null  comment '拆卸成本拆分比例';

alter table T_ENG_BOMCHILD modify column FEFFECTDATE datetime default null  comment '生效日期';

alter table T_ENG_BOMCHILD modify column FEXPIREDATE datetime default null  comment '失效日期';

alter table T_ENG_BOMCHILD modify column FISSUETYPE varchar(1) not null  comment '发料方式';

alter table T_ENG_BOMCHILD modify column FBACKFLUSHTYPE char(1) default null  comment '倒冲时机';

alter table T_ENG_BOMCHILD modify column FSUPPLYORG int default null  comment '发料组织';

alter table T_ENG_BOMCHILD modify column FSTOCKID int default null  comment '默认发料仓库';

alter table T_ENG_BOMCHILD modify column FSTOCKLOCID int default null  comment '默认发料仓位';

alter table T_ENG_BOMCHILD modify column FISGETSCRAP char(1) not null  comment '是否发损耗';

alter table T_ENG_BOMCHILD modify column FALLOWOVER char(1) not null  comment '允许超发';

alter table T_ENG_BOMCHILD modify column FOVERRATE decimal(23,10) not null  comment '超发比例';

alter table T_ENG_BOMCHILD modify column FOWNERTYPEID varchar(36) default null  comment '货主类型';

alter table T_ENG_BOMCHILD modify column FOWNERID int default null  comment '货主';

alter table T_ENG_BOMCHILD modify column FTIMEUNIT int default null  comment '时间单位';

alter table T_ENG_BOMCHILD modify column FOFFSETTIME decimal(23,10) not null  comment '偏置时间';

alter table T_ENG_BOMCHILD modify column FISKEYCOMPONENT char(1) not null  comment '是否为关键件';

alter table T_ENG_BOMCHILD modify column FREPLACEGROUP int not null  comment '项次';

alter table T_ENG_BOMCHILD modify column FAUXPROPID int default 0  comment '辅助属性';

alter table T_ENG_BOMCHILD modify column FPOSITIONNO nvarchar(2000) default null  comment '位置号';

alter table T_ENG_BOMCHILD modify column FBOMID int default null  comment '子项bom编号';

alter table T_ENG_BOMCHILD modify column FGUID varchar(36) not null  comment '子项唯一标示';

alter table T_ENG_BOMCHILD modify column FBASEUNITID int default null  comment '基本单位';

alter table T_ENG_BOMCHILD modify column FBASENUMERATOR decimal(23,10) not null  comment '基本单位分子';

alter table T_ENG_BOMCHILD modify column FBASEDENOMINATOR decimal(23,10) not null  comment '基本单位分母';

alter table T_ENG_BOMCHILD modify column FBASEFIXSCRAPQTY decimal(23,10) not null  comment '基本单位固定损耗';

alter table T_ENG_BOMCHILD modify column FENTRYROWID nvarchar(50) default null  comment '子项分录行标识';

alter table T_ENG_BOMCHILD modify column FQTY decimal(23,10) default null  comment '标准用量';

alter table T_ENG_BOMCHILD modify column FACTUALQTY decimal(23,10) default null  comment '实际用量';

alter table T_ENG_BOMCHILD modify column FOVERCONTROLMODE char(1) default ''2''  comment '控制类型';

alter table T_ENG_BOMCHILD modify column FChildSupplyOrgId int default 0  comment '供应组织';

alter table T_ENG_BOMCHILD modify column FOptQueue nvarchar(8) default '0'  comment '工序序列';

alter table T_ENG_BOMCHILD modify column FMasterId int not null  comment '分配内码';

alter table T_ENG_BOMCHILD modify column FISMinIssueQty char(1) not null  comment '领料考虑最小发料批量';

alter table T_ENG_BOMCHILD modify column FRECORDDATA nvarchar(500) default null  comment '记录字段';