Help with report with historic stock in a specific date

[Former Member]

Dear Experts,

I want to build a report with my historic stock in a specific date, including warehouses, bin locations and serial numbers.

I already have a query that shows the stock, but in some cases it shows a negative quantity.

select distinct XXX."ItemCode",
		XXX."ItemName",
		XXX."LocCode",
		XXX."SL1Code",
		XXX."Serie",
		SUM(XXX."Disponible") as "Disponible",
		SUM(XXX."Tránsito") as "Tránsito"
from 
(
select  distinct T0."ItemCode", 
				T3."ItemName", 
				T0."LocCode", 
				T2."SL1Code",
				T0."TransType",
				T0."DocEntry",
				T4."Serie",
				CASE 
					WHEN T2."SL1Code" = 'FIN' THEN (CASE WHEN T0."ActionType" IN (2,20) 
													THEN T1."Quantity" * -1 
													ELSE T1."Quantity" END) 
					ELSE 0 
				END AS "Disponible",
				CASE 
					WHEN T2."SL1Code" = 'TRA' THEN (CASE WHEN T0."ActionType" IN (2,20) 
													THEN T1."Quantity" * -1 
													ELSE T1."Quantity" END) 
					ELSE 0 
				END AS "Tránsito"
		from OILM T0
			left join OBTL T1 ON T1."MessageID" = T0."MessageID"
			left join OBIN T2 ON T2."AbsEntry"  = T1."BinAbs"
			left join OITM T3 ON T3."ItemCode" = T0."ItemCode"
			inner join "NumeroSerieTransacciones2" T4 on T4."Documento"=T0."TransType" and T4."NroDocto"=T0."DocEntry" 
				and T4."Linea"=T0."DocLineNum" and T4."Cod_Item"=T0."ItemCode" and T4."Almacen"=T0."LocCode"
		where T0."DocDate" <= '20170630' and ifnull(T2."SL1Code",'')<>''
		
) XXX
group by XXX."ItemCode",
		XXX."ItemName",
		XXX."LocCode",
		XXX."SL1Code",
		XXX."Serie"
having sum(XXX."Disponible") + sum(XXX."Tránsito") <> 0

I'm using SAP B1 9.2 PL04.

1.png

2.png

Thanks for your help!

Richard Borja