r Experts,
While Upgrading from SAP B1 2007B to 8.8 the error came like
Serial number and bathecs are inconsistent comparing to stock
if (select version from cinf) >= 880000 begin
select t0.itemcode ItemCode, t1.itemname ItemName, t0.whscode Warehouse, t0.onhand OnHand,
(select coalesce(sum (quantity),0) from OSRQ where itemcode = t0.itemcode and WhsCode = T0.WhsCode) OnSerial,
((select coalesce(SUM(ABS(t2.StockQty)),0)-coalesce(SUM(ABS(t2.DefinedQty)),0) from OITL t2
where t2.DocQty > 0 and t2.StockEff <> 2 and t2.ItemCode = t0.itemcode and t2.LocCode = t0.whscode)
+ ABS((select coalesce(SUM(ABS(t2.StockQty)),0)-coalesce(SUM(ABS(t2.DefinedQty)),0) from OITL t2
where t2.DocQty > 0 and t2.StockEff <> 2 and t2.ItemCode = t0.itemcode and t2.LocCode = t0.whscode))) / 2 ForComplete,
t0.onhand - (select coalesce(sum (quantity),0) from OSRQ where itemcode = t0.itemcode and WhsCode = T0.WhsCode) -
(((select coalesce(SUM(ABS(t2.StockQty)),0)-coalesce(SUM(ABS(t2.DefinedQty)),0) from OITL t2
where t2.DocQty > 0 and t2.StockEff <> 2 and t2.ItemCode = t0.itemcode and t2.LocCode = t0.whscode)
+ ABS((select coalesce(SUM(ABS(t2.StockQty)),0)-coalesce(SUM(ABS(t2.DefinedQty)),0) from OITL t2
where t2.DocQty > 0 and t2.StockEff <> 2 and t2.ItemCode = t0.itemcode and t2.LocCode = t0.whscode))) / 2) Difference
,case (select count (1) from AITM T5 where T5.ItemCode = T0.ItemCode and T5.ManSerNum <> 'Y') when 0 then 'N' else 'Y' end 'StatusChanged', T1.MngMethod
from oitw t0 inner join oitm t1 on T0.ItemCode = T1.ItemCode where t1.ManSerNum = 'Y' and
t0.onhand - (select coalesce(sum (quantity),0) from OSRQ where itemcode = t0.itemcode and WhsCode = T0.WhsCode) -
(((select coalesce(SUM(ABS(t2.StockQty)),0)-coalesce(SUM(ABS(t2.DefinedQty)),0) from OITL t2
where t2.DocQty > 0 and t2.StockEff <> 2 and t2.ItemCode = t0.itemcode and t2.LocCode = t0.whscode)
+ ABS((select coalesce(SUM(ABS(t2.StockQty)),0)-coalesce(SUM(ABS(t2.DefinedQty)),0) from OITL t2
where t2.DocQty > 0 and t2.StockEff <> 2 and t2.ItemCode = t0.itemcode and t2.LocCode = t0.whscode))) / 2) <> 0
order by t0.itemcode, t0.whscode
end
else
select t0.itemcode ItemCode, t1.itemname ItemName, t0.whscode Warehouse, t0.onhand OnHand,
(select count(status) from osri where itemcode = t0.itemcode and WhsCode = T0.WhsCode and status = 0) OnSerial,
(select coalesce(sum(docopenqty),0) from osrd where itemcode = t0.itemcode and WhsCode = T0.WhsCode and direction = '0' and docopenqty > 0) ForComplete,
t0.onhand - (select count(status) from osri where itemcode = t0.itemcode and WhsCode = T0.WhsCode and status = 0) -
(select coalesce(sum(docopenqty),0) from osrd where itemcode = t0.itemcode and WhsCode = T0.WhsCode and direction = '0' and docopenqty > 0) Difference
,case (select count (1) from AITM T5 where T5.ItemCode = T0.ItemCode and T5.ManSerNum <> 'Y')
when 0 then 'N'
else 'Y'
end 'StatusChanged'
,T1.MngMethod
from oitw t0 inner join oitm t1 on T0.ItemCode = T1.ItemCode where t1.mansernum = 'Y'
and t0.onhand - (select count(status) from osri where itemcode = t0.itemcode and WhsCode = T0.WhsCode and status = 0) -
(select coalesce(sum(docopenqty),0) from osrd where itemcode = t0.itemcode and WhsCode = T0.WhsCode and direction = '0' and docopenqty > 0) <> 0
After running this query (got from SAP Note),
result is like
Item Code : FEBL9018699
Item Name : RV160/270
Warehouse : FG
OnHand : 103.000000
OnSerial : 103
ForComplete : 1.000000
Difference : -1.000000
StatusChanged : Y
MngMethod : A
Is the problem is with 103 (OnSerial) + 1 (For Complete) is not equal to OnHand (103) ?