BOS-基础服务与基础框架 / 编码规则及单据类型（1）

--建表脚本--

create table T_BAS_BILLCODERULE(

FRULEID varchar(36) not null  comment '规则内码'

,FBILLFORMID varchar(36) not null  comment '单据对象内码'

,FALLOWADJUST char(1) not null  comment '允许人工修改'

,FCREATEDATE datetime default getdate()  comment '创建日期'

,FCREATORID int not null  comment '创建人'

,FMODIFYDATE datetime default getdate()  comment '修改日期'

,FMODIFIERID int not null  comment '修改人'

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

,FAUDITORID int not null  comment '审核人'

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

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

,FFORBIDDERID int not null  comment '禁用人'

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

,FISSYSPRESET char(1) not null  comment '系统预设'

,FISDEFAULT char(1) not null  comment '是否默认'

,FNewSimulation char(1) default ''0''  comment '新增时模拟生成'

,FISREPAIRBILLNO char(1) not null  comment '手动补号'

,FISAUTOBILLNO char(1) not null  comment '自动补号'

,FVERSION char(1) default '' ''  comment '版本号'

,FSPECIFICKEY varchar(40) default '' ''  comment '指定其他编码字段'

,FISCONSTLEN char(1) not null  comment '是否是固定的流水号位数'

,primary key (FRULEID)

) comment = '编码规则'

--查询--

select FRULEID as "fruleid",FBILLFORMID as "fbillformid",FALLOWADJUST as "fallowadjust",FCREATEDATE as "fcreatedate",FCREATORID as "fcreatorid",FMODIFYDATE as "fmodifydate",FMODIFIERID as "fmodifierid",FDOCUMENTSTATUS as "fdocumentstatus",FAUDITORID as "fauditorid",FAUDITDATE as "fauditdate",FFORBIDSTATUS as "fforbidstatus",FFORBIDDERID as "fforbidderid",FFORBIDDATE as "fforbiddate",FISSYSPRESET as "fissyspreset",FISDEFAULT as "fisdefault",FNewSimulation as "fnewsimulation",FISREPAIRBILLNO as "fisrepairbillno",FISAUTOBILLNO as "fisautobillno",FVERSION as "fversion",FSPECIFICKEY as "fspecifickey",FISCONSTLEN as "fisconstlen" from T_BAS_BILLCODERULE

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

select FRULEID as "规则内码",FBILLFORMID as "单据对象内码",FALLOWADJUST as "允许人工修改",FCREATEDATE as "创建日期",FCREATORID as "创建人",FMODIFYDATE as "修改日期",FMODIFIERID as "修改人",FDOCUMENTSTATUS as "数据状态",FAUDITORID as "审核人",FAUDITDATE as "审核日期",FFORBIDSTATUS as "禁用状态",FFORBIDDERID as "禁用人",FFORBIDDATE as "禁用日期",FISSYSPRESET as "系统预设",FISDEFAULT as "是否默认",FNewSimulation as "新增时模拟生成",FISREPAIRBILLNO as "手动补号",FISAUTOBILLNO as "自动补号",FVERSION as "版本号",FSPECIFICKEY as "指定其他编码字段",FISCONSTLEN as "是否是固定的流水号位数" from T_BAS_BILLCODERULE

--INSERT脚本--

insert into T_BAS_BILLCODERULE(FRULEID,FBILLFORMID,FALLOWADJUST,FCREATEDATE,FCREATORID,FMODIFYDATE,FMODIFIERID,FDOCUMENTSTATUS,FAUDITORID,FAUDITDATE,FFORBIDSTATUS,FFORBIDDERID,FFORBIDDATE,FISSYSPRESET,FISDEFAULT,FNewSimulation,FISREPAIRBILLNO,FISAUTOBILLNO,FVERSION,FSPECIFICKEY,FISCONSTLEN) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)

--UPDATE脚本--

update T_BAS_BILLCODERULE set FRULEID=?,FBILLFORMID=?,FALLOWADJUST=?,FCREATEDATE=?,FCREATORID=?,FMODIFYDATE=?,FMODIFIERID=?,FDOCUMENTSTATUS=?,FAUDITORID=?,FAUDITDATE=?,FFORBIDSTATUS=?,FFORBIDDERID=?,FFORBIDDATE=?,FISSYSPRESET=?,FISDEFAULT=?,FNewSimulation=?,FISREPAIRBILLNO=?,FISAUTOBILLNO=?,FVERSION=?,FSPECIFICKEY=?,FISCONSTLEN=? where FRULEID=?

--delete脚本--

delete from T_BAS_BILLCODERULE where FRULEID=?

--给字段加备注--

alter table T_BAS_BILLCODERULE comment '编码规则';

alter table T_BAS_BILLCODERULE modify column FRULEID varchar(36) not null  comment '规则内码';

alter table T_BAS_BILLCODERULE modify column FBILLFORMID varchar(36) not null  comment '单据对象内码';

alter table T_BAS_BILLCODERULE modify column FALLOWADJUST char(1) not null  comment '允许人工修改';

alter table T_BAS_BILLCODERULE modify column FCREATEDATE datetime default getdate()  comment '创建日期';

alter table T_BAS_BILLCODERULE modify column FCREATORID int not null  comment '创建人';

alter table T_BAS_BILLCODERULE modify column FMODIFYDATE datetime default getdate()  comment '修改日期';

alter table T_BAS_BILLCODERULE modify column FMODIFIERID int not null  comment '修改人';

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

alter table T_BAS_BILLCODERULE modify column FAUDITORID int not null  comment '审核人';

alter table T_BAS_BILLCODERULE modify column FAUDITDATE datetime default null  comment '审核日期';

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

alter table T_BAS_BILLCODERULE modify column FFORBIDDERID int not null  comment '禁用人';

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

alter table T_BAS_BILLCODERULE modify column FISSYSPRESET char(1) not null  comment '系统预设';

alter table T_BAS_BILLCODERULE modify column FISDEFAULT char(1) not null  comment '是否默认';

alter table T_BAS_BILLCODERULE modify column FNewSimulation char(1) default ''0''  comment '新增时模拟生成';

alter table T_BAS_BILLCODERULE modify column FISREPAIRBILLNO char(1) not null  comment '手动补号';

alter table T_BAS_BILLCODERULE modify column FISAUTOBILLNO char(1) not null  comment '自动补号';

alter table T_BAS_BILLCODERULE modify column FVERSION char(1) default '' ''  comment '版本号';

alter table T_BAS_BILLCODERULE modify column FSPECIFICKEY varchar(40) default '' ''  comment '指定其他编码字段';

alter table T_BAS_BILLCODERULE modify column FISCONSTLEN char(1) not null  comment '是否是固定的流水号位数';

--建表脚本--

create table T_BAS_BILLCODERULEENTRY(

FENTRYID varchar(36) not null  comment '分录内码'

,FRULEID varchar(36) not null  comment '规则内码'

,FSEQ int not null  comment '序号'

,FPROJECTTYPE int not null  comment '元素类型'

,FPROJECTID varchar(30) default null  comment '元素来源'

,FLENGTH smallint not null  comment '长度'

,FCUTSTYLE char(1) not null  comment '右侧截断'

,FSEPERATOR nvarchar(1) default null  comment '分隔符'

,FFORMAT varchar(20) default null  comment '格式'

,FADDCHAR nvarchar(1) default null  comment '补位符'

,FADDSTYLE char(1) not null  comment '右侧补位'

,FRECHAR nvarchar(1) default null  comment '替代符'

,FPROJECTPROPERTY smallint not null  comment '来源属性'

,FPROJECTVALUE nvarchar(200) default null  comment '设置值'

,FSEED decimal(23,10) default null  comment '起始值'

,FINCREMENT decimal(23,10) default null  comment '步长'

,FCODEONLYBY char(1) default ''0''  comment '编码依据'

,FCODEELEMENT char(1) default ''1''  comment '编码元素'

,primary key (FENTRYID)

) comment = '编码规则分录'

--查询--

select FENTRYID as "fentryid",FRULEID as "fruleid",FSEQ as "fseq",FPROJECTTYPE as "fprojecttype",FPROJECTID as "fprojectid",FLENGTH as "flength",FCUTSTYLE as "fcutstyle",FSEPERATOR as "fseperator",FFORMAT as "fformat",FADDCHAR as "faddchar",FADDSTYLE as "faddstyle",FRECHAR as "frechar",FPROJECTPROPERTY as "fprojectproperty",FPROJECTVALUE as "fprojectvalue",FSEED as "fseed",FINCREMENT as "fincrement",FCODEONLYBY as "fcodeonlyby",FCODEELEMENT as "fcodeelement" from T_BAS_BILLCODERULEENTRY

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

select FENTRYID as "分录内码",FRULEID as "规则内码",FSEQ as "序号",FPROJECTTYPE as "元素类型",FPROJECTID as "元素来源",FLENGTH as "长度",FCUTSTYLE as "右侧截断",FSEPERATOR as "分隔符",FFORMAT as "格式",FADDCHAR as "补位符",FADDSTYLE as "右侧补位",FRECHAR as "替代符",FPROJECTPROPERTY as "来源属性",FPROJECTVALUE as "设置值",FSEED as "起始值",FINCREMENT as "步长",FCODEONLYBY as "编码依据",FCODEELEMENT as "编码元素" from T_BAS_BILLCODERULEENTRY

--INSERT脚本--

insert into T_BAS_BILLCODERULEENTRY(FENTRYID,FRULEID,FSEQ,FPROJECTTYPE,FPROJECTID,FLENGTH,FCUTSTYLE,FSEPERATOR,FFORMAT,FADDCHAR,FADDSTYLE,FRECHAR,FPROJECTPROPERTY,FPROJECTVALUE,FSEED,FINCREMENT,FCODEONLYBY,FCODEELEMENT) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)

--UPDATE脚本--

update T_BAS_BILLCODERULEENTRY set FENTRYID=?,FRULEID=?,FSEQ=?,FPROJECTTYPE=?,FPROJECTID=?,FLENGTH=?,FCUTSTYLE=?,FSEPERATOR=?,FFORMAT=?,FADDCHAR=?,FADDSTYLE=?,FRECHAR=?,FPROJECTPROPERTY=?,FPROJECTVALUE=?,FSEED=?,FINCREMENT=?,FCODEONLYBY=?,FCODEELEMENT=? where FENTRYID=?

--delete脚本--

delete from T_BAS_BILLCODERULEENTRY where FENTRYID=?

--给字段加备注--

alter table T_BAS_BILLCODERULEENTRY comment '编码规则分录';

alter table T_BAS_BILLCODERULEENTRY modify column FENTRYID varchar(36) not null  comment '分录内码';

alter table T_BAS_BILLCODERULEENTRY modify column FRULEID varchar(36) not null  comment '规则内码';

alter table T_BAS_BILLCODERULEENTRY modify column FSEQ int not null  comment '序号';

alter table T_BAS_BILLCODERULEENTRY modify column FPROJECTTYPE int not null  comment '元素类型';

alter table T_BAS_BILLCODERULEENTRY modify column FPROJECTID varchar(30) default null  comment '元素来源';

alter table T_BAS_BILLCODERULEENTRY modify column FLENGTH smallint not null  comment '长度';

alter table T_BAS_BILLCODERULEENTRY modify column FCUTSTYLE char(1) not null  comment '右侧截断';

alter table T_BAS_BILLCODERULEENTRY modify column FSEPERATOR nvarchar(1) default null  comment '分隔符';

alter table T_BAS_BILLCODERULEENTRY modify column FFORMAT varchar(20) default null  comment '格式';

alter table T_BAS_BILLCODERULEENTRY modify column FADDCHAR nvarchar(1) default null  comment '补位符';

alter table T_BAS_BILLCODERULEENTRY modify column FADDSTYLE char(1) not null  comment '右侧补位';

alter table T_BAS_BILLCODERULEENTRY modify column FRECHAR nvarchar(1) default null  comment '替代符';

alter table T_BAS_BILLCODERULEENTRY modify column FPROJECTPROPERTY smallint not null  comment '来源属性';

alter table T_BAS_BILLCODERULEENTRY modify column FPROJECTVALUE nvarchar(200) default null  comment '设置值';

alter table T_BAS_BILLCODERULEENTRY modify column FSEED decimal(23,10) default null  comment '起始值';

alter table T_BAS_BILLCODERULEENTRY modify column FINCREMENT decimal(23,10) default null  comment '步长';

alter table T_BAS_BILLCODERULEENTRY modify column FCODEONLYBY char(1) default ''0''  comment '编码依据';

alter table T_BAS_BILLCODERULEENTRY modify column FCODEELEMENT char(1) default ''1''  comment '编码元素';

--建表脚本--

create table T_BAS_BILLCODERULE_L(

FPKID varchar(36) not null  comment '内码'

,FRULEID varchar(36) not null  comment '规则内码'

,FLOCALEID int not null  comment '语言类型'

,FNAME nvarchar(80) default ''  comment '名称'

,FDESCRIPTION nvarchar(255) default ''  comment '描述'

,primary key (FPKID)

) comment = '编码规则多语言'

--查询--

select FPKID as "fpkid",FRULEID as "fruleid",FLOCALEID as "flocaleid",FNAME as "fname",FDESCRIPTION as "fdescription" from T_BAS_BILLCODERULE_L

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

select FPKID as "内码",FRULEID as "规则内码",FLOCALEID as "语言类型",FNAME as "名称",FDESCRIPTION as "描述" from T_BAS_BILLCODERULE_L

--INSERT脚本--

insert into T_BAS_BILLCODERULE_L(FPKID,FRULEID,FLOCALEID,FNAME,FDESCRIPTION) values (?,?,?,?,?)

--UPDATE脚本--

update T_BAS_BILLCODERULE_L set FPKID=?,FRULEID=?,FLOCALEID=?,FNAME=?,FDESCRIPTION=? where FPKID=?

--delete脚本--

delete from T_BAS_BILLCODERULE_L where FPKID=?

--给字段加备注--

alter table T_BAS_BILLCODERULE_L comment '编码规则多语言';

alter table T_BAS_BILLCODERULE_L modify column FPKID varchar(36) not null  comment '内码';

alter table T_BAS_BILLCODERULE_L modify column FRULEID varchar(36) not null  comment '规则内码';

alter table T_BAS_BILLCODERULE_L modify column FLOCALEID int not null  comment '语言类型';

alter table T_BAS_BILLCODERULE_L modify column FNAME nvarchar(80) default ''  comment '名称';

alter table T_BAS_BILLCODERULE_L modify column FDESCRIPTION nvarchar(255) default ''  comment '描述';

--建表脚本--

create table T_BAS_BILLCODES(

FCODEID int not null  comment '内码'

,FRULEID varchar(36) not null  comment '规则内码'

,FBYVALUE nvarchar(255) not null  comment '流水依据值'

,FNUMMAX decimal(23,10) not null  comment '最大流水号'

,FBILLNOTEMPLATE varchar(600) default '' ''  comment '补号模板'

,FCODEBYDATA varchar(3000) default '' ''  comment '编码依据数据'

,primary key (FCODEID)

) comment = '编码规则最大编码表'

--查询--

select FCODEID as "fcodeid",FRULEID as "fruleid",FBYVALUE as "fbyvalue",FNUMMAX as "fnummax",FBILLNOTEMPLATE as "fbillnotemplate",FCODEBYDATA as "fcodebydata" from T_BAS_BILLCODES

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

select FCODEID as "内码",FRULEID as "规则内码",FBYVALUE as "流水依据值",FNUMMAX as "最大流水号",FBILLNOTEMPLATE as "补号模板",FCODEBYDATA as "编码依据数据" from T_BAS_BILLCODES

--INSERT脚本--

insert into T_BAS_BILLCODES(FCODEID,FRULEID,FBYVALUE,FNUMMAX,FBILLNOTEMPLATE,FCODEBYDATA) values (?,?,?,?,?,?)

--UPDATE脚本--

update T_BAS_BILLCODES set FCODEID=?,FRULEID=?,FBYVALUE=?,FNUMMAX=?,FBILLNOTEMPLATE=?,FCODEBYDATA=? where FCODEID=?

--delete脚本--

delete from T_BAS_BILLCODES where FCODEID=?

--给字段加备注--

alter table T_BAS_BILLCODES comment '编码规则最大编码表';

alter table T_BAS_BILLCODES modify column FCODEID int not null  comment '内码';

alter table T_BAS_BILLCODES modify column FRULEID varchar(36) not null  comment '规则内码';

alter table T_BAS_BILLCODES modify column FBYVALUE nvarchar(255) not null  comment '流水依据值';

alter table T_BAS_BILLCODES modify column FNUMMAX decimal(23,10) not null  comment '最大流水号';

alter table T_BAS_BILLCODES modify column FBILLNOTEMPLATE varchar(600) default '' ''  comment '补号模板';

alter table T_BAS_BILLCODES modify column FCODEBYDATA varchar(3000) default '' ''  comment '编码依据数据';

--建表脚本--

create table T_BAS_BILLNOREPAIRRECORDS(

FID int not null  comment '内码'

,FRULEID varchar(36) not null  comment '编码规则标识'

,FBILLNO nvarchar(80) not null  comment '单据编号'

,FFORMID varchar(36) not null  comment 'FFORMID'

,FSTATUS char(1) not null  comment '状态'

,primary key (FID)

) comment = '补号记录表'

--查询--

select FID as "fid",FRULEID as "fruleid",FBILLNO as "fbillno",FFORMID as "fformid",FSTATUS as "fstatus" from T_BAS_BILLNOREPAIRRECORDS

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

select FID as "内码",FRULEID as "编码规则标识",FBILLNO as "单据编号",FFORMID as "FFORMID",FSTATUS as "状态" from T_BAS_BILLNOREPAIRRECORDS

--INSERT脚本--

insert into T_BAS_BILLNOREPAIRRECORDS(FID,FRULEID,FBILLNO,FFORMID,FSTATUS) values (?,?,?,?,?)

--UPDATE脚本--

update T_BAS_BILLNOREPAIRRECORDS set FID=?,FRULEID=?,FBILLNO=?,FFORMID=?,FSTATUS=? where FID=?

--delete脚本--

delete from T_BAS_BILLNOREPAIRRECORDS where FID=?

--给字段加备注--

alter table T_BAS_BILLNOREPAIRRECORDS comment '补号记录表';

alter table T_BAS_BILLNOREPAIRRECORDS modify column FID int not null  comment '内码';

alter table T_BAS_BILLNOREPAIRRECORDS modify column FRULEID varchar(36) not null  comment '编码规则标识';

alter table T_BAS_BILLNOREPAIRRECORDS modify column FBILLNO nvarchar(80) not null  comment '单据编号';

alter table T_BAS_BILLNOREPAIRRECORDS modify column FFORMID varchar(36) not null  comment 'FFORMID';

alter table T_BAS_BILLNOREPAIRRECORDS modify column FSTATUS char(1) not null  comment '状态';