强烈推荐篇：HR组织架构管理 / 组织架构管理（3）

--建表脚本--

create table T_ORG_POSTROLE(

FPOSTROLEID int not null  comment '岗位角色关系内码'

,FSEQ int default null  comment '序号'

,FPOSTID int not null  comment '岗位信息内码'

,FROLEID int default null  comment '角色内码'

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

,FISVALID char(1) not null  comment '是否可用'

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

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

,FCREATORID int default null  comment '创建人'

,FMODIFIERID int default null  comment '修改人'

,FCREATEDATE datetime default null  comment '创建时间'

,FMODIFYDATE datetime default null  comment '修改时间'

,FISHRCREATE char(1) not null  comment '是否hr创建'

,primary key (FPOSTROLEID)

) comment = '岗位角色关系表'

--查询--

select FPOSTROLEID as "fpostroleid",FSEQ as "fseq",FPOSTID as "fpostid",FROLEID as "froleid",FISDEFAULT as "fisdefault",FISVALID as "fisvalid",FEFFECTDATE as "feffectdate",FLAPSEDATE as "flapsedate",FCREATORID as "fcreatorid",FMODIFIERID as "fmodifierid",FCREATEDATE as "fcreatedate",FMODIFYDATE as "fmodifydate",FISHRCREATE as "fishrcreate" from T_ORG_POSTROLE

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

select FPOSTROLEID as "岗位角色关系内码",FSEQ as "序号",FPOSTID as "岗位信息内码",FROLEID as "角色内码",FISDEFAULT as "是否默认",FISVALID as "是否可用",FEFFECTDATE as "生效日期",FLAPSEDATE as "失效日期",FCREATORID as "创建人",FMODIFIERID as "修改人",FCREATEDATE as "创建时间",FMODIFYDATE as "修改时间",FISHRCREATE as "是否hr创建" from T_ORG_POSTROLE

--INSERT脚本--

insert into T_ORG_POSTROLE(FPOSTROLEID,FSEQ,FPOSTID,FROLEID,FISDEFAULT,FISVALID,FEFFECTDATE,FLAPSEDATE,FCREATORID,FMODIFIERID,FCREATEDATE,FMODIFYDATE,FISHRCREATE) values (?,?,?,?,?,?,?,?,?,?,?,?,?)

--UPDATE脚本--

update T_ORG_POSTROLE set FPOSTROLEID=?,FSEQ=?,FPOSTID=?,FROLEID=?,FISDEFAULT=?,FISVALID=?,FEFFECTDATE=?,FLAPSEDATE=?,FCREATORID=?,FMODIFIERID=?,FCREATEDATE=?,FMODIFYDATE=?,FISHRCREATE=? where FPOSTROLEID=?

--delete脚本--

delete from T_ORG_POSTROLE where FPOSTROLEID=?

--给字段加备注--

alter table T_ORG_POSTROLE comment '岗位角色关系表';

alter table T_ORG_POSTROLE modify column FPOSTROLEID int not null  comment '岗位角色关系内码';

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

alter table T_ORG_POSTROLE modify column FPOSTID int not null  comment '岗位信息内码';

alter table T_ORG_POSTROLE modify column FROLEID int default null  comment '角色内码';

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

alter table T_ORG_POSTROLE modify column FISVALID char(1) not null  comment '是否可用';

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

alter table T_ORG_POSTROLE modify column FLAPSEDATE datetime default null  comment '失效日期';

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

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

alter table T_ORG_POSTROLE modify column FCREATEDATE datetime default null  comment '创建时间';

alter table T_ORG_POSTROLE modify column FMODIFYDATE datetime default null  comment '修改时间';

alter table T_ORG_POSTROLE modify column FISHRCREATE char(1) not null  comment '是否hr创建';

--建表脚本--

create table T_ORG_POSTROLE_L(

FPKID int not null  comment '多语言内码'

,FPOSTROLEID int not null  comment '岗位角色关系内码'

,FLOCALEID int default null  comment '语种'

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

,primary key (FPKID)

) comment = '岗位角色关系多语言表'

--查询--

select FPKID as "fpkid",FPOSTROLEID as "fpostroleid",FLOCALEID as "flocaleid",FDESCRIPTION as "fdescription" from T_ORG_POSTROLE_L

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

select FPKID as "多语言内码",FPOSTROLEID as "岗位角色关系内码",FLOCALEID as "语种",FDESCRIPTION as "描述" from T_ORG_POSTROLE_L

--INSERT脚本--

insert into T_ORG_POSTROLE_L(FPKID,FPOSTROLEID,FLOCALEID,FDESCRIPTION) values (?,?,?,?)

--UPDATE脚本--

update T_ORG_POSTROLE_L set FPKID=?,FPOSTROLEID=?,FLOCALEID=?,FDESCRIPTION=? where FPKID=?

--delete脚本--

delete from T_ORG_POSTROLE_L where FPKID=?

--给字段加备注--

alter table T_ORG_POSTROLE_L comment '岗位角色关系多语言表';

alter table T_ORG_POSTROLE_L modify column FPKID int not null  comment '多语言内码';

alter table T_ORG_POSTROLE_L modify column FPOSTROLEID int not null  comment '岗位角色关系内码';

alter table T_ORG_POSTROLE_L modify column FLOCALEID int default null  comment '语种';

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

--建表脚本--

create table T_ORG_POST_L(

FPKID int not null  comment '多语言内码'

,FPOSTID int not null  comment '岗位信息内码'

,FLOCALEID int default null  comment '语种'

,FNAME nvarchar(255) default null  comment '名称'

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

,FPOSTFULLNAME nvarchar(255) default null  comment '全称'

,primary key (FPKID)

) comment = '岗位信息多语言表'

--查询--

select FPKID as "fpkid",FPOSTID as "fpostid",FLOCALEID as "flocaleid",FNAME as "fname",FDESCRIPTION as "fdescription",FPOSTFULLNAME as "fpostfullname" from T_ORG_POST_L

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

select FPKID as "多语言内码",FPOSTID as "岗位信息内码",FLOCALEID as "语种",FNAME as "名称",FDESCRIPTION as "描述",FPOSTFULLNAME as "全称" from T_ORG_POST_L

--INSERT脚本--

insert into T_ORG_POST_L(FPKID,FPOSTID,FLOCALEID,FNAME,FDESCRIPTION,FPOSTFULLNAME) values (?,?,?,?,?,?)

--UPDATE脚本--

update T_ORG_POST_L set FPKID=?,FPOSTID=?,FLOCALEID=?,FNAME=?,FDESCRIPTION=?,FPOSTFULLNAME=? where FPKID=?

--delete脚本--

delete from T_ORG_POST_L where FPKID=?

--给字段加备注--

alter table T_ORG_POST_L comment '岗位信息多语言表';

alter table T_ORG_POST_L modify column FPKID int not null  comment '多语言内码';

alter table T_ORG_POST_L modify column FPOSTID int not null  comment '岗位信息内码';

alter table T_ORG_POST_L modify column FLOCALEID int default null  comment '语种';

alter table T_ORG_POST_L modify column FNAME nvarchar(255) default null  comment '名称';

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

alter table T_ORG_POST_L modify column FPOSTFULLNAME nvarchar(255) default null  comment '全称';

--建表脚本--

create table T_SEC_HRDATARULE(

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

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

,FNUMBER nvarchar(30) default null  comment '编码'

,FOBJECTTYPEID varchar(36) default null  comment '业务对象类型'

,FXML xml default null  comment '规则元数据'

,primary key (FITEMID)

) comment = 'hr数据规则主表'

--查询--

select FITEMID as "fitemid",FFORBIDSTATUS as "fforbidstatus",FNUMBER as "fnumber",FOBJECTTYPEID as "fobjecttypeid",FXML as "fxml" from T_SEC_HRDATARULE

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

select FITEMID as "内码",FFORBIDSTATUS as "禁用状态",FNUMBER as "编码",FOBJECTTYPEID as "业务对象类型",FXML as "规则元数据" from T_SEC_HRDATARULE

--INSERT脚本--

insert into T_SEC_HRDATARULE(FITEMID,FFORBIDSTATUS,FNUMBER,FOBJECTTYPEID,FXML) values (?,?,?,?,?)

--UPDATE脚本--

update T_SEC_HRDATARULE set FITEMID=?,FFORBIDSTATUS=?,FNUMBER=?,FOBJECTTYPEID=?,FXML=? where FITEMID=?

--delete脚本--

delete from T_SEC_HRDATARULE where FITEMID=?

--给字段加备注--

alter table T_SEC_HRDATARULE comment 'hr数据规则主表';

alter table T_SEC_HRDATARULE modify column FITEMID varchar(36) not null  comment '内码';

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

alter table T_SEC_HRDATARULE modify column FNUMBER nvarchar(30) default null  comment '编码';

alter table T_SEC_HRDATARULE modify column FOBJECTTYPEID varchar(36) default null  comment '业务对象类型';

alter table T_SEC_HRDATARULE modify column FXML xml default null  comment '规则元数据';

--建表脚本--

create table T_SEC_HRDATARULEENTRY(

FRULEENTRYID varchar(36) not null  comment 'hr权限明细内码'

,FITEMID varchar(36) not null  comment 'hr权限'

,FSCHEMEID int default null  comment '组织架构方案id'

,FDEPTID int default null  comment '组织单元id'

,FISSUBPERMIT char(1) not null  comment '是否具有下级组织权限'

,FFORBID char(1) not null  comment '是否禁用'

,primary key (FRULEENTRYID)

) comment = 'hr权限明细表'

--查询--

select FRULEENTRYID as "fruleentryid",FITEMID as "fitemid",FSCHEMEID as "fschemeid",FDEPTID as "fdeptid",FISSUBPERMIT as "fissubpermit",FFORBID as "fforbid" from T_SEC_HRDATARULEENTRY

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

select FRULEENTRYID as "hr权限明细内码",FITEMID as "hr权限",FSCHEMEID as "组织架构方案id",FDEPTID as "组织单元id",FISSUBPERMIT as "是否具有下级组织权限",FFORBID as "是否禁用" from T_SEC_HRDATARULEENTRY

--INSERT脚本--

insert into T_SEC_HRDATARULEENTRY(FRULEENTRYID,FITEMID,FSCHEMEID,FDEPTID,FISSUBPERMIT,FFORBID) values (?,?,?,?,?,?)

--UPDATE脚本--

update T_SEC_HRDATARULEENTRY set FRULEENTRYID=?,FITEMID=?,FSCHEMEID=?,FDEPTID=?,FISSUBPERMIT=?,FFORBID=? where FRULEENTRYID=?

--delete脚本--

delete from T_SEC_HRDATARULEENTRY where FRULEENTRYID=?

--给字段加备注--

alter table T_SEC_HRDATARULEENTRY comment 'hr权限明细表';

alter table T_SEC_HRDATARULEENTRY modify column FRULEENTRYID varchar(36) not null  comment 'hr权限明细内码';

alter table T_SEC_HRDATARULEENTRY modify column FITEMID varchar(36) not null  comment 'hr权限';

alter table T_SEC_HRDATARULEENTRY modify column FSCHEMEID int default null  comment '组织架构方案id';

alter table T_SEC_HRDATARULEENTRY modify column FDEPTID int default null  comment '组织单元id';

alter table T_SEC_HRDATARULEENTRY modify column FISSUBPERMIT char(1) not null  comment '是否具有下级组织权限';

alter table T_SEC_HRDATARULEENTRY modify column FFORBID char(1) not null  comment '是否禁用';

--建表脚本--

create table T_SEC_HRDATARULE_L(

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

,FITEMID varchar(36) default null  comment '权限项内码'

,FLOCALEID int default null  comment '多语言内码'

,FNAME nvarchar(255) default null  comment '名称'

,primary key (FPKID)

) comment = '数据范围多语言'

--查询--

select FPKID as "fpkid",FITEMID as "fitemid",FLOCALEID as "flocaleid",FNAME as "fname" from T_SEC_HRDATARULE_L

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

select FPKID as "内码",FITEMID as "权限项内码",FLOCALEID as "多语言内码",FNAME as "名称" from T_SEC_HRDATARULE_L

--INSERT脚本--

insert into T_SEC_HRDATARULE_L(FPKID,FITEMID,FLOCALEID,FNAME) values (?,?,?,?)

--UPDATE脚本--

update T_SEC_HRDATARULE_L set FPKID=?,FITEMID=?,FLOCALEID=?,FNAME=? where FPKID=?

--delete脚本--

delete from T_SEC_HRDATARULE_L where FPKID=?

--给字段加备注--

alter table T_SEC_HRDATARULE_L comment '数据范围多语言';

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

alter table T_SEC_HRDATARULE_L modify column FITEMID varchar(36) default null  comment '权限项内码';

alter table T_SEC_HRDATARULE_L modify column FLOCALEID int default null  comment '多语言内码';

alter table T_SEC_HRDATARULE_L modify column FNAME nvarchar(255) default null  comment '名称';