【数据分析】序列号主档组织状态显示异常

/*

序列号主档组织状态显示异常：组织在库状态和单据流转状态不一致

1.查询当前数据异常；

2.备份原始相关异常数据；

3.修正序列号适用组织在库状态；

*/

--1.查询当前数据异常：

select d.fnumber '组织' ,c.fnumber  '序列号',a.FSERIALID '序列号内码',a.FSTATE  '组织在库状态',b.FSTOCKSTATUS '实际在库状态' from T_BD_SERIALBILLTRACE  a

inner join T_BD_SERIALMASTERORG b on a.FSERIALID = b.FSERIALID and a.FDESTSTOCKORGID = b.FORGID

inner join T_BD_SERIALMASTER c on a.FSERIALID = c.fserialid inner join T_ORG_ORGANIZATIONS d on b.FORGID = d.FORGID

where fbilltraceid in ( SELECT MAX(FBILLTRACEID) fbilltraceid FROM   T_BD_SERIALBILLTRACE an GROUP BY FSERIALID,FDESTSTOCKORGID ) and a.FSTATE <> b.FSTOCKSTATUS

order by d.fnumber ,c.fnumber,a.FSERIALID,a.FSTATE,b.FSTOCKSTATUS

--2.备份原始相关异常数据:

select * into TM_BD_SERIALMASTERORG_20200413 from (

select b.* from T_BD_SERIALBILLTRACE  a

inner join T_BD_SERIALMASTERORG b on a.FSERIALID = b.FSERIALID and a.FDESTSTOCKORGID = b.FORGID 

where fbilltraceid in ( SELECT MAX(FBILLTRACEID) fbilltraceid FROM   T_BD_SERIALBILLTRACE an GROUP BY FSERIALID,FDESTSTOCKORGID ) and a.FSTATE <> b.FSTOCKSTATUS

) T;

--3.修正序列号适用组织在库状态:

merge into T_BD_SERIALMASTERORG T using

(

select b.fentryid,a.FSTATE from T_BD_SERIALBILLTRACE  a

inner join T_BD_SERIALMASTERORG b on a.FSERIALID = b.FSERIALID and a.FDESTSTOCKORGID = b.FORGID 

where fbilltraceid in ( SELECT MAX(FBILLTRACEID) fbilltraceid FROM   T_BD_SERIALBILLTRACE an GROUP BY FSERIALID,FDESTSTOCKORGID ) and a.FSTATE <> b.FSTOCKSTATUS

) TM on T.fentryid = TM.fentryid

when matched then update set T.FSTOCKSTATUS = TM.FSTATE;