Relacion entre OITM y OBIN en SAP BO

SAP Business One
1

hola amigos, alguien que me ayuden es que tengo una solicitud en la empresa donde tengo que relacionar las tablas OITM y OBIN pero no he encontrado la manera de que me funcione

tengo esta consulta pero debo añadir un campo de la tabla OBIN
gracias.

select t0.ItemCode as Codigo,t0.FrgnName as Cod_Proveedor, t0.ItemName as Descripcion, t0.CreateDate as F_Creacion,

(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


from OITM t0 
where t0.ItemCode like '10-%'
2

buen día @JTRIANA6591 prueba con este código, espero te sea de utilidad.

SELECT 
	T1.ItemCode,
	T1.ItemName,
	T0.OnHandQty,
	T2.BinCode
FROM OIBQ T0
	INNER JOIN OITM T1 ON T0.ITEMCODE=T1.ItemCode
	INNER JOIN OBIN T2 ON T0.BinAbs=T2.AbsEntry
ORDER BY T1.ItemCode

Saludos.

1 me gusta
3

gracias por tu respuesta voy a realizar la prueba haber si me funciona

4

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