Ayuda consulta rotación inventario

SAP Business One
1

hola amigos de SAP necesito de ayuda para arreglar un consulta de movimiento de inventario los campos B_central y repuesto me esta duplicando el valor siendo un ALMACEN diferente es el mismo articulo pero diferente almacén.

image
image830×56 4.15 KB

existe la posibilidad que si el almacén es diferente el campo B_central y repuesto sea vacia como muestro en la siguiente IMG

image
image827×58 4.27 KB

envio mi consulta para ver si alguien me ayuda

select  t0.ItemCode as Codigo,t0.FrgnName as Cod_Proveedor ,t0.ItemName as Descripcion,T2.BinCode AS "Primer_Ubicacion", t0.CreateDate as F_Creacion,sum (t1.OnHandQty) as Inventario,

--(select sum(tt0.OnHandQty) from OIBQ tt0 where tt0.ItemCode = t0.ItemCode ) as Inventario,

(select sum(tt0.OnHandQty) from OIBQ tt0 where tt0.ItemCode = t0.ItemCode and tt0.WhsCode = 'CEN') as B_Central,
(select sum(tt0.OnHandQty) from OIBQ tt0 where tt0.ItemCode = t0.ItemCode and tt0.WhsCode = 'DEM') as Demos,
(select sum(tt0.OnHandQty) from OIBQ tt0 where tt0.ItemCode = t0.ItemCode and tt0.WhsCode = 'PPR') as P_PRoceso,
(select sum(tt0.OnHandQty) from OIBQ tt0 where tt0.ItemCode = t0.ItemCode and tt0.WhsCode = 'REP') as Repuesto,(select sum(tt0.OnHandQty) from OIBQ tt0 where tt0.ItemCode = t0.ItemCode and tt0.WhsCode = 'TEL') as Telmex,



DATEDIFF(DAY,(select top 1  tt1.DocDate from OINM tt1 where tt1.ItemCode = t0.ItemCode and tt1.TransType <> 67
order by tt1.DocDate desc),GETDATE()) as Dias_Sin_Rotar,
-----------------------------------ROTACION DE INVENTARIO----------------------------------------

(
  (
	(
		SELECT 
		stock= case
		when (SUM(Y.InQty)-SUM(Y.OutQty)) <= 0 then 0
		when (SUM(Y.InQty)-SUM(Y.OutQty)) > 0 then  (SUM(Y.InQty)-SUM(Y.OutQty))
		else NULL
		end
		FROM OINM Y WHERE Y.DocDate <= DATEADD(MONTH,-12,GETDATE()) and y.itemcode = t0.itemcode
		group by y.ItemCode

	)
	+	
	(
		SELECT 
		stock= case
		when (SUM(Y.InQty)-SUM(Y.OutQty)) <= 0 then 0
		when (SUM(Y.InQty)-SUM(Y.OutQty)) > 0 then  (SUM(Y.InQty)-SUM(Y.OutQty))
		else NULL
		end
		FROM OINM Y WHERE Y.DocDate <= DATEADD(MONTH,-11,GETDATE()) and y.itemcode = t0.itemcode
		group by y.ItemCode

	)
	+	
	(
		SELECT 
		stock= case
		when (SUM(Y.InQty)-SUM(Y.OutQty)) <= 0 then 0
		when (SUM(Y.InQty)-SUM(Y.OutQty)) > 0 then  (SUM(Y.InQty)-SUM(Y.OutQty))
		else NULL
		end
		FROM OINM Y WHERE Y.DocDate <= DATEADD(MONTH,-10,GETDATE()) and y.itemcode = t0.itemcode
		group by y.ItemCode

	)
	+
	(
		SELECT 
		stock= case
		when (SUM(Y.InQty)-SUM(Y.OutQty)) <= 0 then 0
		when (SUM(Y.InQty)-SUM(Y.OutQty)) > 0 then  (SUM(Y.InQty)-SUM(Y.OutQty))
		else NULL
		end
		FROM OINM Y WHERE Y.DocDate <= DATEADD(MONTH,-9,GETDATE()) and y.itemcode = t0.itemcode
		group by y.ItemCode

	)
	+
	(
		SELECT 
		stock= case
		when (SUM(Y.InQty)-SUM(Y.OutQty)) <= 0 then 0
		when (SUM(Y.InQty)-SUM(Y.OutQty)) > 0 then  (SUM(Y.InQty)-SUM(Y.OutQty))
		else NULL
		end
		FROM OINM Y WHERE Y.DocDate <= DATEADD(MONTH,-8,GETDATE()) and y.itemcode = t0.itemcode
		group by y.ItemCode

	)
	+
	(
		SELECT 
		stock= case
		when (SUM(Y.InQty)-SUM(Y.OutQty)) <= 0 then 0
		when (SUM(Y.InQty)-SUM(Y.OutQty)) > 0 then  (SUM(Y.InQty)-SUM(Y.OutQty))
		else NULL
		end
		FROM OINM Y WHERE Y.DocDate <= DATEADD(MONTH,-7,GETDATE()) and y.itemcode = t0.itemcode
		group by y.ItemCode

	)
	+
	(
		SELECT 
		stock= case
		when (SUM(Y.InQty)-SUM(Y.OutQty)) <= 0 then 0
		when (SUM(Y.InQty)-SUM(Y.OutQty)) > 0 then  (SUM(Y.InQty)-SUM(Y.OutQty))
		else NULL
		end
		FROM OINM Y WHERE Y.DocDate <= DATEADD(MONTH,-6,GETDATE()) and y.itemcode = t0.itemcode
		group by y.ItemCode

	)
	+
	(
		SELECT 
		stock= case
		when (SUM(Y.InQty)-SUM(Y.OutQty)) <= 0 then 0
		when (SUM(Y.InQty)-SUM(Y.OutQty)) > 0 then  (SUM(Y.InQty)-SUM(Y.OutQty))
		else NULL
		end
		FROM OINM Y WHERE Y.DocDate <= DATEADD(MONTH,-5,GETDATE()) and y.itemcode = t0.itemcode
		group by y.ItemCode

	)
	+
	(
		SELECT 
		stock= case
		when (SUM(Y.InQty)-SUM(Y.OutQty)) <= 0 then 0
		when (SUM(Y.InQty)-SUM(Y.OutQty)) > 0 then  (SUM(Y.InQty)-SUM(Y.OutQty))
		else NULL
		end
		FROM OINM Y WHERE Y.DocDate <= DATEADD(MONTH,-4,GETDATE()) and y.itemcode = t0.itemcode
		group by y.ItemCode

	)
	+
	(
		SELECT 
		stock= case
		when (SUM(Y.InQty)-SUM(Y.OutQty)) <= 0 then 0
		when (SUM(Y.InQty)-SUM(Y.OutQty)) > 0 then  (SUM(Y.InQty)-SUM(Y.OutQty))
		else NULL
		end
		FROM OINM Y WHERE Y.DocDate <= DATEADD(MONTH,-3,GETDATE()) and y.itemcode = t0.itemcode
		group by y.ItemCode

	)
	+
	(
		SELECT 
		stock= case
		when (SUM(Y.InQty)-SUM(Y.OutQty)) <= 0 then 0
		when (SUM(Y.InQty)-SUM(Y.OutQty)) > 0 then  (SUM(Y.InQty)-SUM(Y.OutQty))
		else NULL
		end
		FROM OINM Y WHERE Y.DocDate <= DATEADD(MONTH,-2,GETDATE()) and y.itemcode = t0.itemcode
		group by y.ItemCode

	)
	+
	(
		SELECT 
		stock= case
		when (SUM(Y.InQty)-SUM(Y.OutQty)) <= 0 then 0
		when (SUM(Y.InQty)-SUM(Y.OutQty)) > 0 then  (SUM(Y.InQty)-SUM(Y.OutQty))
		else NULL
		end
		FROM OINM Y WHERE Y.DocDate <= DATEADD(MONTH,-1,GETDATE()) and y.itemcode = t0.itemcode
		group by y.ItemCode

	)
	+
	(
		SELECT 
		stock= case
		when (SUM(Y.InQty)-SUM(Y.OutQty)) <= 0 then 0
		when (SUM(Y.InQty)-SUM(Y.OutQty)) > 0 then  (SUM(Y.InQty)-SUM(Y.OutQty))
		else NULL
		end
		FROM OINM Y WHERE Y.DocDate <= GETDATE() and y.itemcode = t0.itemcode
		group by y.ItemCode

	)			
 )/13

) as INV_PRO,


-------------------------------------------------------------------------------------------------


CRITERIOS=CASE

when DATEDIFF(DAY,(select top 1  tt1.DocDate from OINM tt1 where tt1.ItemCode = t0.ItemCode and tt1.TransType <> 67
order by tt1.DocDate desc),GETDATE()) <= 364 then 'menos de un 1 Año'

when DATEDIFF(DAY,(select top 1  tt1.DocDate from OINM tt1 where tt1.ItemCode = t0.ItemCode and tt1.TransType <> 67
order by tt1.DocDate desc),GETDATE()) between '365' and  '730' then 'Entre 1 y 2 Años'

when DATEDIFF(DAY,(select top 1  tt1.DocDate from OINM tt1 where tt1.ItemCode = t0.ItemCode and tt1.TransType <> 67
order by tt1.DocDate desc),GETDATE()) between '731' and '1095' then 'Entre 2 y 3 Años'

when DATEDIFF(DAY,(select top 1  tt1.DocDate from OINM tt1 where tt1.ItemCode = t0.ItemCode and tt1.TransType <> 67
order by tt1.DocDate desc),GETDATE()) between '1096' and '1460' then 'Entre 3 y 4 Años'

when DATEDIFF(DAY,(select top 1  tt1.DocDate from OINM tt1 where tt1.ItemCode = t0.ItemCode and tt1.TransType <> 67
order by tt1.DocDate desc),GETDATE()) between '1461' and '1825' then 'Entre 4 y 5 Años'

when DATEDIFF(DAY,(select top 1  tt1.DocDate from OINM tt1 where tt1.ItemCode = t0.ItemCode and tt1.TransType <> 67
order by tt1.DocDate desc),GETDATE()) >= 1826 then 'Mas de 5 Años'


else NULL

end 

,

t0.LstEvlPric as Precio_Unitario,
((select sum(tt0.OnHandQty) from OIBQ tt0 where tt0.ItemCode = t0.ItemCode )*t0.LstEvlPric) as Precio_Total

--((select sum(tt0.OnHandQty) from OIBQ tt0 where tt0.ItemCode = t0.ItemCode )*(select tt3.LstEvlPric from OITM tt3 where tt3.itemcode=t0.ItemCode)) as Precio_Total



from OITM t0 
	left JOIN OIBQ T1 ON T1.ITEMCODE=T0.ItemCode
	left JOIN OBIN T2 ON T1.BinAbs=T2.AbsEntry
where t0.ItemCode like '10-%'  and T1.OnHandQty > 0  and t0.ItemCode = '10-MGBI-TE'

group by t0.ItemCode, t0.FrgnName  ,t0.ItemName ,T2.BinCode , t0.CreateDate,t0.LstEvlPric,t1.WhsCode

 --select *  from OIBQ
2

Estimado debe poner la consulta en la seccion SapBussinesOne

Saludos

1 me gusta
3

Hola @jtriana6591, me parece que tu query estas obeteniendo mal las tablas, donde dice Inventario, estas tomando de T1 que es OIBQ (que es el stock por ubicacion, y como el haces un Left Join y solo cruzas el “ItemCode”, entonces te toma uno cualquiera de cualquier almacén.
Según veo lo que necesitas es que saque una linea por Item-Almacen, y luego pasarlo a columna, eso lo puedes hacer con un CASE. Algo asi:

Select T0.ItemCode, T0.ItemName, T1.WhsCode, T1.OnHand,
CASE T0.WhsCode = 'CEN' THEN T1.OnHand ELSE 0 END as 'B_Central',
CASE T0.WhsCode = 'REP' THEN T1.OnHand ELSE 0 END as 'Repuesto',
.....
FROM OITM T0 inner join OITW T1 on T0.ItemCode = T1.ItemCode

Ojo si vas a sacar Stock por almacén, mejor es buscar la OITW.

4

gracias por tu ayuda voy a probar y te cuento

5

:warning: Chicos, por favor, este tema debió ser reportado para que se mueva al sector de #sap-business-one, todos los que respondieron debieron reportar primero usando el :black_flag: del mensaje que tiene abajo cada mensaje, y eligen la opción “Avisar al STAFF”, o “es inapropiado” … así lo analizamos quienes podemos mover temas.

Es 10 segundos, o menos lo que demoran. :pray:

2 Me gusta
6

Este tema se cerró automáticamente 30 días después de la última publicación. No se permiten nuevas respuestas.