1、信用常见问题点:信用档案列表打开时提示:表V_CRE_ITEMCLASSDOC中读取出的数据,出现重复的主键(FItemID)数据:xxxxxxx,请检查此表是否设置了主键或主键是否是FItemID;
2、分析:这个视图为信用档案中对象类型多类别基础资料,其中包括基础资料--客户、销售员、销售组、销售部门、销售组织、集团客户。V_CRE_ITEMCLASSDOC的主键(FItemID)来源于以上所有基础资料,一般来说,所有的基础资料的主键来源于同一个种子数表,但是不排查异常发生,比如说二开改造了主键的数据来源,使得不同的基础资料主键存在重复,导致报错。
还有一个特殊情况,就是在扩展信用档案二开的时候去掉了对象类型的不重建视图的勾选项,如下图所示:
上图这种情况也会导致报错。
3、解决办法:针对重复的数据,如果对象是客户;
其他重复的基础资料,如果没有引用过,删除基础资料然后重新创建即可,如果已经引用过,重建视图,把重复的那个基础资料在视图中删除即可,如下图所示:
注意:V_CRE_ITEMCLASSDOC、V_CRE_ITEMCLASSDOC_L俩个视图都需要重建。
针对上面第2点中的特殊情况,需要重建视图;脚本如下:
DROP VIEW V_CRE_ITEMCLASSDOC;
CREATE VIEW V_CRE_ITEMCLASSDOC
AS SELECT FCUSTID fitemid, 'BD_Customer' fformid, FNUMBER fnumber, FMASTERID fmasterid, FCREATEORGID fcreateorgid, FUSEORGID fuseorgid, FDOCUMENTSTATUS fdocumentstatus, FFORBIDSTATUS fforbidstatus, FCREATORID fcreatorid, FMODIFIERID fmodifierid, FCREATEDATE fcreatedate, FMODIFYDATE fmodifydate FROM T_BD_CUSTOMER
UNION ALL SELECT FORGID fitemid, 'ORG_Organizations' fformid, FNUMBER fnumber, FORGID fmasterid, 0 fcreateorgid, 0 fuseorgid, FDOCUMENTSTATUS fdocumentstatus, FFORBIDSTATUS fforbidstatus, FCREATORID fcreatorid, FMODIFIERID fmodifierid, FCREATEDATE fcreatedate, FMODIFYDATE fmodifydate FROM T_ORG_ORGANIZATIONS
UNION ALL SELECT FDEPTID fitemid, 'BD_Department' fformid, FNUMBER fnumber, FMASTERID fmasterid, FCREATEORGID
fcreateorgid, FUSEORGID fuseorgid, FDOCUMENTSTATUS fdocumentstatus, FFORBIDSTATUS fforbidstatus, FCREATORID fcreatorid, FMODIFIERID fmodifierid, FCREATEDATE fcreatedate, FMODIFYDATE fmodifydate FROM T_BD_DEPARTMENT
UNION ALL SELECT B.FENTRYID fitemid, 'BD_SaleGroup' fformid, B.FNUMBER fnumber, B.FENTRYID fmasterid, B.FBIZORGID fcreateorgid, B.FBIZORGID fuseorgid, 'C' fdocumentstatus, 'A' fforbidstatus, A.FCREATORID fcreatorid, A.FMODIFIERID fmodifierid, A.FCREATEDATE fcreatedate, A.FMODIFYDATE fmodifydate
FROM T_BD_OPERATORGROUP A INNER JOIN T_BD_OPERATORGROUPENTRY B ON A.FOPERATORGROUPID = B.FOPERATORGROUPID WHERE ((B.FISUSE = '1' AND B.FENTRYID NOT IN (SELECT FCUSTID FROM T_BD_CUSTOMER)) AND B.FENTRYID NOT IN (SELECT FDEPTID FROM T_BD_DEPARTMENT))
UNION ALL SELECT B.FENTRYID fitemid, 'BD_Saler' fformid, B.FNUMBER fnumber, B.FENTRYID fmasterid, B.FBIZORGID fcreateorgid, B.FBIZORGID fuseorgid, C.FDOCUMENTSTATUS fdocumentstatus, C.FFORBIDSTATUS fforbidstatus, A.FCREATORID fcreatorid, A.FMODIFIERID fmodifierid, A.FCREATEDATE fcreatedate, A.FMODIFYDATE fmodifydate FROM T_BD_OPERATOR A INNER JOIN T_BD_OPERATORENTRY B ON A.FOPERATORID = B.FOPERATORID INNER JOIN T_BD_STAFF C ON B.FSTAFFID = C.FSTAFFID WHERE (((B.FOPERATORTYPE = 'XSY' AND B.FISUSE = '1') AND B.FENTRYID NOT IN (SELECT FCUSTID FROM T_BD_CUSTOMER)) AND B.FENTRYID NOT IN (SELECT FDEPTID FROM T_BD_DEPARTMENT));
DROP VIEW V_CRE_ITEMCLASSDOC_L;
CREATE VIEW V_CRE_ITEMCLASSDOC_L AS
SELECT FPKID, FCUSTID fitemid, 'BD_Customer' fformid, FLOCALEID, FNAME FROM T_BD_CUSTOMER_L
UNION ALL SELECT FPKID, FORGID fitemid, 'ORG_Organizations' fformid, FLOCALEID, FNAME FROM T_ORG_ORGANIZATIONS_L
UNION ALL SELECT FPKID, FDEPTID fitemid, 'BD_Department' fformid, FLOCALEID, FNAME FROM T_BD_DEPARTMENT_L
UNION ALL SELECT FPKID, FENTRYID fitemid, 'BD_SaleGroup' fformid, FLOCALEID, FNAME FROM T_BD_OPERATORGROUPENTRY_L
UNION ALL SELECT B.FPKID fpkid, A.FENTRYID fitemid, 'BD_Saler' fformid, B.FLOCALEID flocaleid, B.FNAME fname FROM T_BD_OPERATORENTRY A INNER JOIN T_BD_STAFF_L B ON A.FSTAFFID = B.FSTAFFID WHERE (A.FOPERATORTYPE = 'XSY' AND A.FISUSE = '1');
推荐阅读