物料审核报错：“不能在具有唯一索引'IDX_HS_INIVSTOCKDIMENSION'的对象中插入重复键的行。

栏目：云星空知识作者：金蝶来源：金蝶云社区发布：2024-09-16浏览：1

物料审核报错：“不能在具有唯一索引 'IDX_HS_INIVSTOCKDIMENSION' 的对象 'dbo.T_HS_INIVSTOCKDIMENSION' 中插入重复键的行。语句已终止。”

---修量修复反审核重新审核报错库存维度重复

--查询物料组织，执行以下修复脚本后这里查询出来的核算组织要重新核算

select d.fnumber 核算组织编码, c.fnumber 物料编码,a.FMATERIALID,a.FCARGOOWNERTYPE,a.FLOT,a.FCARGOOWNERID,

a.FSTOCKID,a.FSTOCKSTATUSID,a.FAUXPROPID,a.FSTOCKID,a.FSTOCKLOCID,a.FBOMID,

a.FMTONO,b.FACCTGRANGEID,count(1) from T_HS_INIVSTOCKDIMENSION a

inner join T_HS_STOCKDIMENSION b on a.FDIMEENTRYID=b.FENTRYID

join T_BD_MATERIAL c on a.fmaterialid=c.fmaterialid

join T_ORG_ORGANIZATIONS d on FCARGOOWNERID=d.forgid

group by d.fnumber , c.fnumber, a.FMATERIALID,a.FCARGOOWNERTYPE,a.FCARGOOWNERID,a.FSTOCKID,a.FSTOCKSTATUSID,a.FAUXPROPID,a.FSTOCKID,a.FSTOCKLOCID,a.FBOMID,a.FMTONO,b.FACCTGRANGEID,a.FLOT

having count (1)>1

---1、备份

select * into T_HS_INIVSTOCKDIMENSION_sqlbak_20230831 from T_HS_INIVSTOCKDIMENSION

select * into T_HS_STOCKDIMENSION_sql_bak_20230831 from T_HS_STOCKDIMENSION

---2、创建中间表

select Max(a.FENTRYID) as FENTRYID ,Max(a.fdimeentryid) fdimeentryid into tmpdim

from T_HS_INIVSTOCKDIMENSION a

inner join T_HS_STOCKDIMENSION b on a.FDIMEENTRYID=b.FENTRYID

join T_BD_MATERIAL c on a.fmaterialid=c.fmaterialid

group by c.fnumber, a.FMATERIALID,a.FCARGOOWNERTYPE,a.FCARGOOWNERID,a.FSTOCKID,a.FSTOCKSTATUSID,a.FAUXPROPID,a.FSTOCKID,a.FSTOCKLOCID,a.FBOMID,a.FMTONO,b.FACCTGRANGEID,a.FLOT

having count (1)>1

--3、删除重复数据

delete from T_HS_INIVSTOCKDIMENSION where FENTRYID not in (

select FDIMEENTRYID from T_HS_INIVBALANCE_H

union all

select FDIMEENTRYID from T_HS_INIVBALANCE where FENDINITKEY='0'

union all

select FDIMEENTRYID from T_HS_OUTINSTOCKSEQ_H )

and FENTRYID in (select FENTRYID from tmpdim )

delete from T_HS_STOCKDIMENSION where FENTRYID not in (

select FACCTGDIMEENTRYID from T_HS_INIVBALANCE_H

union all