信息集同步元数据同步模板BUG处理(多语、是否显示、显示顺序等）

作者：周郑发布时间：2021-07-13 点击数：

现象描述：在NCC2005版本中，信息集同步元数据、同步模板后，会有BUG，大致表现为“之前在模板上设置的字段属性如：字段名称、字段显示顺序、是否可编辑、是否可见等”会被打乱。如下：

信息集同步元数据同步模板BUG处理(多语、是否显示、显示顺序等）(图1)

解决措施：如果字段属性仅仅是“同步元数据同步模板字段变成多语”则可以通过补丁处理。如果担心还有其他东西乱了，建议按照如下思路进行处理。处理步骤为：（1）增加元数据同步模板前先执行3备份信息集、模板；（2）增加元数据、同步模板；（3）执行6恢复字段的某些熟悉；（4）执行3备份信息集、模板。其他的点可供参考。

--1、信息集逻辑s

--根据信息集名称，查询单据的信息，找到本单据的Pk_billtemplet

ｓｅｌｅｃｔ * from hr_infoset where infoset_name like '人员基本信息'

--信息集字段

ｓｅｌｅｃｔ * from hr_infoset_item where pk_infoset = '1002Z7100000000046GP' and item_name in ('聘任专业技术职务类别')

--1、信息集逻辑e

--2、模板逻辑s

--根据模板名称，查询单据的信息，找到本单据的pk_page_templet 1001A1100000000003NI

ｓｅｌｅｃｔ pk_page_templet from pub_page_templet where name = '员工信息维护-wwq'

--找到各个模块的AREAID

ｓｅｌｅｃｔ areatype,areahide,METAID,METANAME,METASPACE,substr(METASPACE,6,20) as biao,NAME,PK_AREA from pub_area where pub_area.templetid = '1001A210000000076LUS'

and name in ('基本信息','任职记录','工作信息')--具体哪些模块

--根据AREAID查询模板具体信息

ｓｅｌｅｃｔ LABEL,METAPATH,pub_form_property.* from pub_form_property where AREAID = '1001A210000000076LUV'

--2、模板逻辑e

--3、备份信息集、模板s(第一次执行，因为ｄｒｏｐ删除对象还没有会报错，需要一行一行执行)。

--3.1、备份信息集HR_INFOSET表为HR_INFOSET_BACK

ｄｒｏｐ table HR_INFOSET_BACK;

create table HR_INFOSET_BACK as ｓｅｌｅｃｔ * from HR_INFOSET;

ｓｅｌｅｃｔ * from HR_INFOSET_BACK;

--3.2、备份信息集子表HR_INFOSET_ITEM表为HR_INFOSET_ITEM_BACK

ｄｒｏｐ table HR_INFOSET_ITEM_BACK;

create table HR_INFOSET_ITEM_BACK as ｓｅｌｅｃｔ * from HR_INFOSET_ITEM;

ｓｅｌｅｃｔ * from HR_INFOSET_ITEM_BACK;

--3.3、备份模板PUB_PAGE_TEMPLET表为PUB_PAGE_TEMPLET_BACK

ｄｒｏｐ table PUB_PAGE_TEMPLET_BACK;

create table PUB_PAGE_TEMPLET_BACK as ｓｅｌｅｃｔ * from PUB_PAGE_TEMPLET;

ｓｅｌｅｃｔ * from PUB_PAGE_TEMPLET_BACK;

--3.4、备份模板子集PUB_AREA表为PUB_AREA_BACK

ｄｒｏｐ table PUB_AREA_BACK;

create table PUB_AREA_BACK as ｓｅｌｅｃｔ * from PUB_AREA;

ｓｅｌｅｃｔ * from PUB_AREA_BACK;

--3.5、备份模板子集字段PUB_FORM_PROPERTY表为PUB_FORM_PROPERTY_BACK

ｄｒｏｐ table PUB_FORM_PROPERTY_BACK;

create table PUB_FORM_PROPERTY_BACK as ｓｅｌｅｃｔ * from PUB_FORM_PROPERTY;

ｓｅｌｅｃｔ * from PUB_FORM_PROPERTY_BACK;

--备份信息集、模板e

--4、查询备份的表与被备份的表不同的数据

--4.1HR_INFOSET表与HR_INFOSET_BACK表

ｓｅｌｅｃｔ test.* from(

ｓｅｌｅｃｔ * from HR_INFOSET

minus

ｓｅｌｅｃｔ * from HR_INFOSET_BACK) test

ｕｎｉｏｎ all

ｓｅｌｅｃｔ test1.* from(

ｓｅｌｅｃｔ * from HR_INFOSET_BACK

minus

ｓｅｌｅｃｔ * from HR_INFOSET) test1;

--4.2HR_INFOSET_ITEM表与HR_INFOSET_ITEM_BACK表

ｓｅｌｅｃｔ test.* from(

ｓｅｌｅｃｔ * from HR_INFOSET_ITEM

minus

ｓｅｌｅｃｔ * from HR_INFOSET_ITEM_BACK) test

ｕｎｉｏｎ all

ｓｅｌｅｃｔ test1.* from(

ｓｅｌｅｃｔ * from HR_INFOSET_ITEM_BACK

minus

ｓｅｌｅｃｔ * from HR_INFOSET_ITEM) test1;

--4.3PUB_PAGE_TEMPLET表与PUB_PAGE_TEMPLET_BACK表

ｓｅｌｅｃｔ test.* from(

ｓｅｌｅｃｔ * from PUB_PAGE_TEMPLET

minus

ｓｅｌｅｃｔ * from PUB_PAGE_TEMPLET_BACK) test

ｕｎｉｏｎ all

ｓｅｌｅｃｔ test1.* from(

ｓｅｌｅｃｔ * from PUB_PAGE_TEMPLET_BACK

minus

ｓｅｌｅｃｔ * from PUB_PAGE_TEMPLET) test1;

--4.4PUB_AREA表与PUB_AREA_BACK表

ｓｅｌｅｃｔ test.* from(

ｓｅｌｅｃｔ * from PUB_AREA

minus

ｓｅｌｅｃｔ * from PUB_AREA_BACK) test

ｕｎｉｏｎ all

ｓｅｌｅｃｔ test1.* from(

ｓｅｌｅｃｔ * from PUB_AREA_BACK

minus

ｓｅｌｅｃｔ * from PUB_AREA) test1;

--4.5PUB_FORM_PROPERTY表与PUB_FORM_PROPERTY_BACK表

ｓｅｌｅｃｔ test.* from(

ｓｅｌｅｃｔ * from PUB_FORM_PROPERTY

minus

ｓｅｌｅｃｔ * from PUB_FORM_PROPERTY_BACK) test

ｕｎｉｏｎ all

ｓｅｌｅｃｔ test1.* from(

ｓｅｌｅｃｔ * from PUB_FORM_PROPERTY_BACK

minus

ｓｅｌｅｃｔ * from PUB_FORM_PROPERTY) test1;

--5、查询字段的那些属性发了变化

ｓｅｌｅｃｔ

PUB_FORM_PROPERTY.PK_FORM_PROPERTY,--单据主键

PUB_FORM_PROPERTY.LABEL, --显示名称

PUB_FORM_PROPERTY.ISRESLABEL,--LABEL是否有值，若有则Y若无则N

PUB_FORM_PROPERTY.VISIBLE, --是否显示

PUB_FORM_PROPERTY.CODE, --字段编码（单据的键值）

PUB_FORM_PROPERTY.REQUIRED, --必输

PUB_FORM_PROPERTY.DISABLED, --不可修改

PUB_FORM_PROPERTY.ISREVISE, --可修订

PUB_FORM_PROPERTY.EDITFORMULA, --编辑公式

PUB_FORM_PROPERTY.SHOWFORMULA, --显示公式

PUB_FORM_PROPERTY.VALIDATEFORMULA, --验证公式

PUB_FORM_PROPERTY_BACK.LABEL as oldLABEL, --显示名称

PUB_FORM_PROPERTY_BACK.ISRESLABEL as oldISRESLABEL,--LABEL是否有值，若有则Y若无则N

PUB_FORM_PROPERTY_BACK.VISIBLE as oldVISIBLE, --是否显示

PUB_FORM_PROPERTY_BACK.CODE as oldCODE, --字段编码（单据的键值）

PUB_FORM_PROPERTY_BACK.REQUIRED as oldREQUIRED, --必输

PUB_FORM_PROPERTY_BACK.DISABLED as oldDISABLED, --不可修改

PUB_FORM_PROPERTY_BACK.ISREVISE as oldISREVISE, --可修订

PUB_FORM_PROPERTY_BACK.EDITFORMULA as oldEDITFORMULA, --编辑公式

PUB_FORM_PROPERTY_BACK.SHOWFORMULA as oldSHOWFORMULA, --显示公式

PUB_FORM_PROPERTY_BACK.VALIDATEFORMULA as oldVALIDATEFORMULA --验证公式

from PUB_FORM_PROPERTY

LEFT OUTER JOIN PUB_FORM_PROPERTY_BACK on PUB_FORM_PROPERTY_BACK.PK_FORM_PROPERTY = PUB_FORM_PROPERTY.PK_FORM_PROPERTY

where 1=1

and (

PUB_FORM_PROPERTY.LABEL!=PUB_FORM_PROPERTY_BACK.LABEL --显示名称

or PUB_FORM_PROPERTY.ISRESLABEL!=PUB_FORM_PROPERTY_BACK.ISRESLABEL --LABEL是否有值，若有则Y若无则N

or PUB_FORM_PROPERTY.VISIBLE!=PUB_FORM_PROPERTY_BACK.VISIBLE --是否显示

or PUB_FORM_PROPERTY.CODE!=PUB_FORM_PROPERTY_BACK.CODE --字段编码（单据的键值）

or PUB_FORM_PROPERTY.REQUIRED!=PUB_FORM_PROPERTY_BACK.REQUIRED--必输

or PUB_FORM_PROPERTY.DISABLED!=PUB_FORM_PROPERTY_BACK.DISABLED--不可修改

or PUB_FORM_PROPERTY.ISREVISE!=PUB_FORM_PROPERTY_BACK.ISREVISE--可修订

or PUB_FORM_PROPERTY.EDITFORMULA!=PUB_FORM_PROPERTY_BACK.EDITFORMULA --编辑公式

or PUB_FORM_PROPERTY.SHOWFORMULA!=PUB_FORM_PROPERTY_BACK.SHOWFORMULA --显示公式

or PUB_FORM_PROPERTY.VALIDATEFORMULA!=PUB_FORM_PROPERTY_BACK.VALIDATEFORMULA --验证公式

)

--6恢复字段的某些属性s

ｕｐｄａｔｅ PUB_FORM_PROPERTY

SET

--显示名称

PUB_FORM_PROPERTY.LABEL = (ｓｅｌｅｃｔ PUB_FORM_PROPERTY_BACK.LABEL from PUB_FORM_PROPERTY_BACK where PUB_FORM_PROPERTY_BACK.PK_FORM_PROPERTY = PUB_FORM_PROPERTY.PK_FORM_PROPERTY),

--LABEL是否有值，若有则Y若无则N

PUB_FORM_PROPERTY.ISRESLABEL = (ｓｅｌｅｃｔ PUB_FORM_PROPERTY_BACK.ISRESLABEL from PUB_FORM_PROPERTY_BACK where PUB_FORM_PROPERTY_BACK.PK_FORM_PROPERTY = PUB_FORM_PROPERTY.PK_FORM_PROPERTY),

--是否显示

PUB_FORM_PROPERTY.VISIBLE = (ｓｅｌｅｃｔ PUB_FORM_PROPERTY_BACK.VISIBLE from PUB_FORM_PROPERTY_BACK where PUB_FORM_PROPERTY_BACK.PK_FORM_PROPERTY = PUB_FORM_PROPERTY.PK_FORM_PROPERTY),

--字段编码（单据的键值）

PUB_FORM_PROPERTY.CODE = (ｓｅｌｅｃｔ PUB_FORM_PROPERTY_BACK.CODE from PUB_FORM_PROPERTY_BACK where PUB_FORM_PROPERTY_BACK.PK_FORM_PROPERTY = PUB_FORM_PROPERTY.PK_FORM_PROPERTY),

--必输

PUB_FORM_PROPERTY.REQUIRED = (ｓｅｌｅｃｔ PUB_FORM_PROPERTY_BACK.REQUIRED from PUB_FORM_PROPERTY_BACK where PUB_FORM_PROPERTY_BACK.PK_FORM_PROPERTY = PUB_FORM_PROPERTY.PK_FORM_PROPERTY),

--不可修改

PUB_FORM_PROPERTY.DISABLED = (ｓｅｌｅｃｔ PUB_FORM_PROPERTY_BACK.DISABLED from PUB_FORM_PROPERTY_BACK where PUB_FORM_PROPERTY_BACK.PK_FORM_PROPERTY = PUB_FORM_PROPERTY.PK_FORM_PROPERTY),

--可修订

PUB_FORM_PROPERTY.ISREVISE = (ｓｅｌｅｃｔ PUB_FORM_PROPERTY_BACK.ISREVISE from PUB_FORM_PROPERTY_BACK where PUB_FORM_PROPERTY_BACK.PK_FORM_PROPERTY = PUB_FORM_PROPERTY.PK_FORM_PROPERTY),

--编辑公式

PUB_FORM_PROPERTY.EDITFORMULA = (ｓｅｌｅｃｔ PUB_FORM_PROPERTY_BACK.EDITFORMULA from PUB_FORM_PROPERTY_BACK where PUB_FORM_PROPERTY_BACK.PK_FORM_PROPERTY = PUB_FORM_PROPERTY.PK_FORM_PROPERTY),

--显示公式

PUB_FORM_PROPERTY.SHOWFORMULA = (ｓｅｌｅｃｔ PUB_FORM_PROPERTY_BACK.SHOWFORMULA from PUB_FORM_PROPERTY_BACK where PUB_FORM_PROPERTY_BACK.PK_FORM_PROPERTY = PUB_FORM_PROPERTY.PK_FORM_PROPERTY),

--验证公式

PUB_FORM_PROPERTY.VALIDATEFORMULA = (ｓｅｌｅｃｔ PUB_FORM_PROPERTY_BACK.VALIDATEFORMULA from PUB_FORM_PROPERTY_BACK where PUB_FORM_PROPERTY_BACK.PK_FORM_PROPERTY = PUB_FORM_PROPERTY.PK_FORM_PROPERTY)

WHERE EXISTS(ｓｅｌｅｃｔ 1 FROM PUB_FORM_PROPERTY_BACK WHERE PUB_FORM_PROPERTY_BACK.PK_FORM_PROPERTY = PUB_FORM_PROPERTY.PK_FORM_PROPERTY)

and PUB_FORM_PROPERTY.PK_FORM_PROPERTY in(

ｓｅｌｅｃｔ

PUB_FORM_PROPERTY.PK_FORM_PROPERTY--单据主键