物料审核报错:“不能在具有唯一索引'IDX_HS_INIVSTOCKDIMENSION'的对象 中插入重复键的行。原创
金蝶云社区-羊羊羊羊羊羊
羊羊羊羊羊羊
82人赞赏了该文章 2,211次浏览 未经作者许可,禁止转载编辑于2024年11月01日 17:31:00


注意:已启用成本拆分表不可以用以下方法处理



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

image.png

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

--查询物料组织,执行以下修复脚本后这里查询出来的核算组织要重新核算
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
select FACCTGDIMEENTRYID from T_HS_INIVBALANCE where  FENDINITKEY='0'
union all
select FACCTGDIMEENTRYID from T_HS_OUTINSTOCKSEQ_H
union all
select FDIMEENTRYID from T_HS_BALANCE_H
union all
select FDIMEENTRYID from T_HS_BALANCE where   FENDINITKEY='0'
union all
select FDIMEENTRYID from T_HS_INIVSTOCKDIMENSION

) and   FENTRYID  in    (select fdimeentryid  from tmpdim )


---4、删除临时表
drop  table  tmpdim


图标赞 82
82人点赞
还没有人点赞,快来当第一个点赞的人吧!
图标打赏
0人打赏
还没有人打赏,快来当第一个打赏的人吧!