【数据分析】查询即时库存单位不一致问题

--查询同一物料在不同组织下基本单位不一致的数据

select t2.fbaseunitid,t1.* from T_BD_MATERIAL t1 inner join t_bd_materialbase t2 on t1.fmaterialid = t2.fmaterialid where 

t1.fmasterid in (select t3.fmasterid from (

select distinct t1.fmasterid,t2.fbaseunitid from t_bd_material t1 inner join t_bd_materialbase t2

on t1.FMATERIALID = t2.FMATERIALID) t3 group by t3.FMASTERID having count(*) > 1)

--查询同一物料在不同组织下库存单位不一致的数据

select t2.FSTOREUNITID,t1.* from T_BD_MATERIAL t1 inner join T_BD_MATERIALSTOCK t2 on t1.fmaterialid = t2.fmaterialid where 

t1.fmasterid in (select t3.fmasterid from (

select distinct t1.fmasterid,t2.FSTOREUNITID from t_bd_material t1 inner join T_BD_MATERIALSTOCK t2

on t1.FMATERIALID = t2.FMATERIALID) t3 group by t3.FMASTERID having count(*) > 1)

--查询同一物料在不同组织下辅助单位不一致的数据

select t2.FAUXUNITID,t1.* from T_BD_MATERIAL t1 inner join T_BD_MATERIALSTOCK t2 on t1.fmaterialid = t2.fmaterialid where 

t1.fmasterid in (select t3.fmasterid from (

select distinct t1.fmasterid,t2.FAUXUNITID from t_bd_material t1 inner join T_BD_MATERIALSTOCK t2

on t1.FMATERIALID = t2.FMATERIALID) t3 group by t3.FMASTERID having count(*) > 1)

--查询即时库存表基本单位和物料不一致的数据

select * from T_STK_INVENTORY t1 

inner join T_BD_MATERIALBASE t3 on t1.FMATERIALID = t3.FMATERIALID 

where t1.FBASEUNITID <> t3.FBASEUNITID

--查询即时库存表库存单位和物料不一致的数据

select * from T_STK_INVENTORY t1 inner join T_BD_MATERIALSTOCK t2

on t1.FMATERIALID = t2.FMATERIALID 

where t1.FSTOCKUNITID <> t2.FSTOREUNITID

--查询即时库存表辅助单位和物料不一致的数据

select * from T_STK_INVENTORY t1 inner join T_BD_MATERIALSTOCK t2

on t1.FMATERIALID = t2.FMATERIALID 

where t1.FSECUNITID <> t2.FAUXUNITID

--查询库存余额基本单位和物料不一致的数据

select * from T_STK_INVbal t1 

inner join T_BD_MATERIALBASE t3 on t1.FMATERIALID = t3.FMATERIALID 

where t1.FBASEUNITID <> t3.FBASEUNITID 

--查询库存余额辅助单位和物料不一致的数据

select * from T_STK_INVbal t1 inner join T_BD_MATERIALSTOCK t2

on t1.FMATERIALID = t2.FMATERIALID 

where t1.FSECUNITID <> t2.FAUXUNITID

--结存临时明细基本和物料不一致的数据   需要做库存关账才能查询最新有效数据，可以据此结果查询都哪些业务单据基本单位数据有问题

 select t1.FFORMID 单据类型,t5.fname 单据类型名称,t1.fbillno 单据编号,t1.fbillid 单据内码,t1.FBASEQTY 基本单位数量,t1.FBASEUNITID 单据基本单位,

t2.FBASEUNITID 物料基本单位, t1.fmaterialid 物料内码,t4.fnumber 物料编码, t1.* 

from T_STK_STKBALOCCUR t1 inner join 

T_BD_MATERIALBASE t2 on t1.FMATERIALID = t2.FMATERIALID 

inner join T_BD_MATERIAL t4 on t1.FMATERIALID = t4.FMATERIALID 

 inner join t_meta_objecttype_L t5 on t1.fformid = t5.fid and t5.flocaleid = 2052

where t1.FBASEUNITID <> t2.FBASEUNITID

--结存临时明细辅助单位和物料不一致的数据   需要做库存关账才能查询最新有效数据，可以据此结果查询都哪些业务单据库存辅单位数据有问题

 select t1.FFORMID 单据类型,t5.fname 单据类型名称,t1.fbillno 单据编号,t1.fbillid 单据内码,t1.FSECUNITID 单据辅助单位,t3.FAUXUNITID 物料辅助单位, 

 t1.fmaterialid 物料内码,t4.fnumber 物料编码, t1.* 

from T_STK_STKBALOCCUR t1  

inner join T_BD_MATERIALSTOCK t3 on t1.FMATERIALID = t3.FMATERIALID 

inner join T_BD_MATERIAL t4 on t1.FMATERIALID = t4.FMATERIALID 

 inner join t_meta_objecttype_L t5 on t1.fformid = t5.fid and t5.flocaleid = 2052

where t1.FSECUNITID <> t3.FAUXUNITID 

--余额表维度重复数据

select count(*) ,FBALTYPE,FBALDATE,FCOMBINEID,FMTONO,FPROJECTNO,FPRODUCEDATE,FEXPIRYDATE from T_STK_INVBAL

group by  FBALTYPE,FBALDATE,FCOMBINEID,FMTONO,FPROJECTNO,FPRODUCEDATE,FEXPIRYDATE

having count(*) > 1

order by FBALDATE desc

--或者：

select count(*) ,FBALTYPE,FBALDATE,FSTOCKORGID,FOWNERTYPEID,FOWNERID,FKEEPERTYPEID,FKEEPERID,FSTOCKID,FSTOCKLOCID,FMATERIALID,FBOMID,FAUXPROPID,

FMTONO,FPROJECTNO,FSTOCKSTATUSID,FLOT,FPRODUCEDATE,FEXPIRYDATE from T_STK_INVBAL

group by  FBALTYPE,FBALDATE,FSTOCKORGID,FOWNERTYPEID,FOWNERID,FKEEPERTYPEID,FKEEPERID,FSTOCKID,FSTOCKLOCID,FMATERIALID,FBOMID,FAUXPROPID,

FMTONO,FPROJECTNO,FSTOCKSTATUSID,FLOT,FPRODUCEDATE,FEXPIRYDATE

having count(*) > 1

--即时库存重复维度数据

select count(*) ,FCOMBINEID,FMTONO,FPROJECTNO,FPRODUCEDATE,FEXPIRYDATE from T_STK_INVENTORY

group by  FCOMBINEID,FMTONO,FPROJECTNO,FPRODUCEDATE,FEXPIRYDATE

having count(*) > 1

--前面无异常的话后面需要更新日志单据来获取单据的不一致数据