Informe de Inventario por bodega que muestre número de serie o lote

[former_member325322]

Hola a todos

Necesito sacar un listado con los productos que tengo en stock, que muestre para el caso en que exista números de serie y números de lote.

Existe algún informe nativo de sap que me permita ver esta informació

[Former Member · Accepted solution]

Hola que tal,

Creo que lo que buscas esta en Inventario - Informes de inventario - Informe de operaciones num. de serie / Informe de operaciones numero de lote.

Saludos.

[former_member325322 · Accepted solution]

Gracias Miguel Angel por tu respuesta.

He intentado conocer el stock de un producto a traves de este informe pero no se cómo obtenerlo.

Al parecer este informe sólo me muestra la historia de cada serial (ingreso, movimientos, ventas etc.)

¿Cómo pueo saber que en stock tengo 2 unidades del producto A y sus seriales son S1 y S2?

Saludos

[felipe_loyolarodriguez · Accepted solution]

prueba este query

SELECT T0.ItemCode, T1.ItemName, T0.BatchNum, T0.WhsCode, T2.WhsName, ISNULL((SELECT SUM(Y.Quantity) FROM IBT1 Y WHERE Y.ItemCode = T0.ItemCode AND Y.WhsCode = T0.WhsCode AND Y.BatchNum = T0.BatchNum AND Y.Direction != '1' AND Y.DocDate <= '[%0]'),0)-ISNULL((SELECT SUM(Y.Quantity) FROM IBT1 Y WHERE Y.ItemCode = T0.ItemCode AND Y.WhsCode = T0.WhsCode AND Y.BatchNum = T0.BatchNum AND Y.Direction = '1' AND Y.DocDate <= '[%0]'),0) 'Stock'
FROM IBT1 T0
INNER JOIN OITM T1 ON T1.ItemCode = T0.ItemCode
INNER JOIN OWHS T2 ON T2.WhsCode = T0.WhsCode

Slds

[former_member325322 · Accepted solution]

Fracias Floyola

¿Qué significa la fecha que debo ingresar?, ¿es la fecha a la que quiero que se calcule el inventario?

Saludos

[former_member325322 · Accepted solution]

Tienes alguna rutina parecida para ver las seriales de los productos.

Saludos

[felipe_loyolarodriguez · Accepted solution]

Hola Patricio, realmente nunca habia hecho un query para # de serie, de todas maneras prueba este

SELECT DISTINCT T0.[ItemCode], T2.[ItemName], T3.[WhsCode], T3.[WhsName], T1.[IntrSerial],
ISNULL((SELECT SUM(A.Quantity) FROM ITL1 A INNER JOIN OITL B ON B.LogEntry=A.LogEntry WHERE A.ItemCode=T0.ItemCode AND A.SysNumber=T0.SysSerial AND B.DocDate<='[%0]'),0)'Stock'
FROM SRI1 T0  
INNER JOIN OSRI T1 ON T0.SysSerial = T1.SysSerial AND T0.ItemCode=T1.ItemCode
INNER JOIN OITM T2 ON T0.ItemCode = T2.ItemCode 
INNER JOIN OWHS T3 ON T0.WhsCode = T3.WhsCode
WHERE ISNULL((SELECT SUM(A.Quantity) FROM ITL1 A INNER JOIN OITL B ON B.LogEntry=A.LogEntry WHERE A.ItemCode=T0.ItemCode AND A.SysNumber=T0.SysSerial AND B.DocDate<='[%0]'),0)>0
ORDER BY 1,5

Saludos

[former_member325322 · Accepted solution]

Hola Floyola.

Muchas gracias por tu valioso aporte.

Tengo 2 dudas:

1) La fecha que me solicita el query de qué se trata?. ¿Es la fecha a la que se calcula el inventario?

2) Por alguna razón el stock que me muestra el query es mayor que el stock real

Saludos

[felipe_loyolarodriguez · Accepted solution]

Claro, la fecha que se ingresa es hasta donde se calcula en inventario

Cual de las 2 querys te arroja un inventario mayor?

[former_member325322 · Accepted solution]

Hasta ahora sólo revisé el de seriales.

Saludos

[former_member325322 · Accepted solution]

Adjunto rutina de Floyola corregida para el inventario por seriales.

Saludos

SELECT DISTINCT T0.[ItemCode], T2.[ItemName], T4.[WhsCode], T3.[WhsName], T1.[IntrSerial],
ISNULL((SELECT SUM(A.Quantity) FROM ITL1 A INNER JOIN OITL B ON B.LogEntry=A.LogEntry WHERE A.ItemCode=T0.ItemCode AND A.SysNumber=T0.SysSerial AND B.DocDate<='[%0]'),0)'Stock'
FROM SRI1 T0  
INNER JOIN OSRI T1 ON T0.SysSerial = T1.SysSerial AND T0.ItemCode=T1.ItemCode
INNER JOIN OITM T2 ON T0.ItemCode = T2.ItemCode 
INNER JOIN OWHS T3 ON T0.WhsCode = T3.WhsCode
INNER JOIN OITW T4 ON T0.ItemCode = T4.ItemCode AND T0.WhsCode = T4.WhsCode AND T4.OnHand > 0 
WHERE ISNULL((SELECT SUM(A.Quantity) FROM ITL1 A INNER JOIN OITL B ON B.LogEntry=A.LogEntry WHERE A.ItemCode=T0.ItemCode AND A.SysNumber=T0.SysSerial AND B.DocDate<='[%0]'),0)>0
ORDER BY 1,5

[Former Member · Accepted solution]

Hola Felipe

Es muy bueno tu Query, Yo estoy tratando de hacer un reporte de ventas de los articulos que contengas los nuemos de series, pero actualmente me trae todas las facturas incluyendo las notas de credito, tu sabes como hacerle para que me unicamente las facturas.

Saludos.

[xhoracio]

a ver si te sirve mi stored procedure, trae ubicacion. lote y lo voy separando por nivel y rack, tambien muestra si no tiene ubicacion:

USE [reportes]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[plantillas]

AS

IF EXISTS (SELECT * FROM sysobjects WHERE type = 'U' AND name = 'plantillas1')

BEGIN

set nocount on

PRINT 'Existe'

drop table plantillas1

CREATE TABLE [dbo].[plantillas1](

[sl1code] [nvarchar](50) NULL,

[Pasillo] [nvarchar](36) NULL,

[Posicion] [nvarchar](36) NULL,

[Nivel] [nvarchar](25) NULL,

--[Plantilla] [nvarchar](228) NULL,

[Articulo] [nvarchar](50) NULL,

[Descrip_Articulo] [nvarchar](100) NULL,

[U.M.] [nvarchar](100) NULL,

[Almacen_id] [nvarchar](8) NULL,

--[Almacen nombre] [nvarchar](100) NULL,

[Ubicacion] [nvarchar](36) NULL,

[lote] [nvarchar](36) NULL,

[NuevoLote] [nvarchar](36) NULL,

[Conteo] [nvarchar](36) NULL,

[ADICIONAL] [varchar](63) NULL,

--[WhsCode] [nvarchar](8) NULL,

[Existencia] [nvarchar](36) NULL,

--, [DistNumber] [nvarchar](36) NULL

[costo] [nvarchar](36) NULL,

) ON [PRIMARY]

END

ELSE

BEGIN

PRINT 'no existe'

CREATE TABLE [dbo].[plantillas1](

[sl1code] [nvarchar](50) NULL,

[Pasillo] [nvarchar](36) NULL,

[Posicion] [nvarchar](36) NULL,

[Nivel] [nvarchar](25) NULL,

--[Plantilla] [nvarchar](228) NULL,

[Articulo] [nvarchar](50) NULL,

[Descrip_Articulo] [nvarchar](100) NULL,

[U.M.] [nvarchar](100) NULL,

[Almacen_id] [nvarchar](8) NULL,

--[Almacen nombre] [nvarchar](100) NULL,

[Ubicacion] [nvarchar](36) NULL,

[lote] [nvarchar](36) NULL,

[NuevoLote] [nvarchar](36) NULL,

[Conteo] [nvarchar](36) NULL,

[DIFERENCIA] [varchar](63) NULL,

--[WhsCode] [nvarchar](8) NULL,

[Existencia] [nvarchar](36) NULL,

--, [DistNumber] [nvarchar](36) NULL

[costo] [nvarchar](36) NULL,

) ON [PRIMARY]

END

set nocount on

insert into plantillas1

SELECT

--*'Pasillo'=left(right(T4.BinCode,4),1),

--*'Posicion'=LEFT(RIGHT(T4.BinCode,3),2),

--*'Nivel'=RIGHT(T4.BinCode,1),

'sl1code'=OBIN.SL1Code,

'Pasillo'=LEFT(OBIN.SL1Code,1),

'Posicion'=LEFT(RIGHT(OBIN.SL1Code,3),2),

'Nivel'=RIGHT(OBIN.SL1Code,1),

--'Plantilla'= OBIN.BinCode,

'Articulo'=OBTN.ItemCode,

'Descrip_Articulo'=OITM.ItemName,

'U.M.'=OITM.[SalUnitMsr],

'Almacen_id'=OBIN.WhsCode,

--'Almacen nombre'=OWHS.WhsName,

'Ubicacion'= OBIN.BinCode,

'lote'=OBTN.DistNumber,

'NuevoLote'=null,

'Conteo'=0,

'ADICIONAL'=0,

--OBIN.WhsCode ,

case when OBBQ.OnHandQty > 0 then OBBQ.OnHandQty

else 0

end,

--,OBTN.DistNumber

'Costo'=OITM.AvgPrice

FROM cucu2.dbo.OBIN OBIN

LEFT JOIN cucu2.dbo.OBBQ OBBQ ON OBBQ.BinAbs=OBIN.AbsEntry AND OBBQ.WhsCode=OBIN.WhsCode AND OBBQ.OnHandQty<>0

LEFT JOIN cucu2.dbo.OBTN OBTN ON OBTN.ItemCode = OBBQ.ItemCode and OBTN.AbsEntry = OBBQ.SnBMDAbs

LEFT JOIN cucu2.dbo.OITM OITM ON OITM.ItemCODE=OBBQ.ItemCode

LEFT JOIN cucu2.dbo.OWHS OWHS ON OWHS.WhsCode=OBIN.WhsCode

WHERE OBIN.DISABLED='N'

ORDER BY OBIN.WhsCode,

LEFT(OBIN.SL1Code,1)+RIGHT(OBIN.SL1Code,1),

LEFT(RIGHT(OBIN.SL1Code,3),2),

RIGHT(OBIN.SL1Code,1)

set nocount on

insert into plantillas1

SELECT

'sl1code'=OBIN.SL1Code,

'Pasillo'=LEFT(OBIN.SL1Code,1),

'Posicion'=LEFT(RIGHT(OBIN.SL1Code,3),2),

'Nivel'=RIGHT(OBIN.SL1Code,1),

--'Plantilla'= OBIN.BinCode,

'Articulo'=OITM.ITEMCODE,

'Descrip_Articulo'=OITM.ItemName,

'U.M.'=OITM.[SalUnitMsr],

'Almacen_id'=OBIN.WhsCode,

--'Almacen nombre'=OWHS.WhsName,

'Ubicacion'=isnull(OBIN.BinCode,0),

'lote'=OBTN.DistNumber,

'NuevoLote'=null,

'Conteo'=0,

'ADICIONAL'=0,

--'whscode'=OBIN.WhsCode ,

case when oitm.OnHand > 0 then oitm.OnHand

else 0

end,

--,OBTN.DistNumber

'Costo'=OITM.AvgPrice

FROM cucu2.dbo.OITM OITM

LEFT JOIN cucu2.dbo.OBTN OBTN ON OBTN.ItemCode=OITM.ItemCODE

LEFT JOIN cucu2.dbo.OBTQ OBTQ ON OBTQ.ItemCode=OBTN.ItemCode AND OBTN.SysNumber=OBTQ.SysNumber

LEFT join cucu2.dbo.OBBQ OBBQ on OBTN.ItemCode = OBBQ.ItemCode and OBTN.AbsEntry = OBBQ.SnBMDAbs AND OBBQ.WhsCode=OBTQ.WhsCode

LEFT JOIN cucu2.dbo.OBIN OBIN ON OBIN.AbsEntry= OBBQ.BinAbs

left join cucu2.dbo.OWHS OWHS ON OWHS.WhsCode=OBIN.WhsCode

WHERE OBTN.DistNumber IS NULL

AND OITM.OnHand > 0

IF EXISTS (SELECT * FROM sysobjects WHERE type = 'U' AND name = 'plantillas2')

BEGIN

set nocount on

PRINT 'Existe'

DROP TABLE plantillas2

end

CREATE TABLE [dbo].[plantillas2](

ID int IDENTITY(1,1) PRIMARY KEY,

[sl1code] [nvarchar](50) NULL,

[Pasillo] [nvarchar](36) NULL,

[Posicion] [nvarchar](36) NULL,

[Nivel] [nvarchar](25) NULL,

--[Plantilla] [nvarchar](228) NULL,

[Articulo] [nvarchar](50) NULL,

[Descrip_Articulo] [nvarchar](100) NULL,

[U.M.] [nvarchar](100) NULL,

[Almacen_id] [nvarchar](8) NULL,

--[Almacen nombre] [nvarchar](100) NULL,

[Ubicacion] [nvarchar](36) NULL,

[lote] [nvarchar](36) NULL,

[NuevoLote] [nvarchar](36) NULL,

[Conteo] [nvarchar](36) NULL,

[ADICIONAL] [varchar](63) NULL,

--[WhsCode] [nvarchar](8) NULL,

[Existencia] [nvarchar](36) NULL,

--, [DistNumber] [nvarchar](36) NULL

[costo] [nvarchar](36) NULL,

)

insert into plantillas2 select * FROM PLANTILLAS1 WHERE UBICACION=CAST(0 AS VARCHAR)

insert into plantillas2 select * from plantillas1 where pasillo+nivel='A'+'A' ORDER BY pasillo+nivel, posicion

insert into plantillas2 select * from plantillas1 where pasillo+nivel='A'+'B' ORDER BY pasillo+nivel, posicion

insert into plantillas2 select * from plantillas1 where pasillo+nivel='A'+'C' ORDER BY pasillo+nivel, posicion

insert into plantillas2 select * from plantillas1 where pasillo+nivel='A'+'D' ORDER BY pasillo+nivel, posicion

insert into plantillas2 select * from plantillas1 where pasillo+nivel='A'+'E' ORDER BY pasillo+nivel, posicion

insert into plantillas2 select * from plantillas1 where pasillo+nivel='B'+'A' ORDER BY pasillo+nivel, posicion

insert into plantillas2 select * from plantillas1 where pasillo+nivel='B'+'B' ORDER BY pasillo+nivel, posicion

insert into plantillas2 select * from plantillas1 where pasillo+nivel='B'+'C' ORDER BY pasillo+nivel, posicion

insert into plantillas2 select * from plantillas1 where pasillo+nivel='B'+'D' ORDER BY pasillo+nivel, posicion

insert into plantillas2 select * from plantillas1 where pasillo+nivel='B'+'E' ORDER BY pasillo+nivel, posicion

insert into plantillas2 select * from plantillas1 where pasillo+nivel='C'+'A' ORDER BY pasillo+nivel, posicion

insert into plantillas2 select * from plantillas1 where pasillo+nivel='C'+'B' ORDER BY pasillo+nivel, posicion

insert into plantillas2 select * from plantillas1 where pasillo+nivel='C'+'C' ORDER BY pasillo+nivel, posicion

insert into plantillas2 select * from plantillas1 where pasillo+nivel='C'+'D' ORDER BY pasillo+nivel, posicion

insert into plantillas2 select * from plantillas1 where pasillo+nivel='C'+'E' ORDER BY pasillo+nivel, posicion

insert into plantillas2 select * from plantillas1 where pasillo+nivel='D'+'A' ORDER BY pasillo+nivel, posicion

insert into plantillas2 select * from plantillas1 where pasillo+nivel='D'+'B' ORDER BY pasillo+nivel, posicion

insert into plantillas2 select * from plantillas1 where pasillo+nivel='D'+'C' ORDER BY pasillo+nivel, posicion

insert into plantillas2 select * from plantillas1 where pasillo+nivel='D'+'D' ORDER BY pasillo+nivel, posicion

insert into plantillas2 select * from plantillas1 where pasillo+nivel='D'+'E' ORDER BY pasillo+nivel, posicion

insert into plantillas2 select * from plantillas1 where pasillo+nivel='E'+'A' ORDER BY pasillo+nivel, posicion

insert into plantillas2 select * from plantillas1 where pasillo+nivel='E'+'B' ORDER BY pasillo+nivel, posicion

insert into plantillas2 select * from plantillas1 where pasillo+nivel='E'+'C' ORDER BY pasillo+nivel, posicion

insert into plantillas2 select * from plantillas1 where pasillo+nivel='E'+'D' ORDER BY pasillo+nivel, posicion

insert into plantillas2 select * from plantillas1 where pasillo+nivel='E'+'E' ORDER BY pasillo+nivel, posicion

insert into plantillas2 select * from plantillas1 where pasillo+nivel='F'+'A' ORDER BY pasillo+nivel, posicion

insert into plantillas2 select * from plantillas1 where pasillo+nivel='F'+'B' ORDER BY pasillo+nivel, posicion

insert into plantillas2 select * from plantillas1 where pasillo+nivel='F'+'C' ORDER BY pasillo+nivel, posicion

insert into plantillas2 select * from plantillas1 where pasillo+nivel='F'+'D' ORDER BY pasillo+nivel, posicion

insert into plantillas2 select * from plantillas1 where pasillo+nivel='F'+'E' ORDER BY pasillo+nivel, posicion

insert into plantillas2 select * from plantillas1 where pasillo+nivel='G'+'A' ORDER BY pasillo+nivel, posicion

insert into plantillas2 select * from plantillas1 where pasillo+nivel='G'+'B' ORDER BY pasillo+nivel, posicion

insert into plantillas2 select * from plantillas1 where pasillo+nivel='G'+'C' ORDER BY pasillo+nivel, posicion

insert into plantillas2 select * from plantillas1 where pasillo+nivel='G'+'D' ORDER BY pasillo+nivel, posicion

insert into plantillas2 select * from plantillas1 where pasillo+nivel='G'+'E' ORDER BY pasillo+nivel, posicion

insert into plantillas2 select * from plantillas1 where pasillo+nivel='H'+'A' ORDER BY pasillo+nivel, posicion

insert into plantillas2 select * from plantillas1 where pasillo+nivel='H'+'B' ORDER BY pasillo+nivel, posicion

insert into plantillas2 select * from plantillas1 where pasillo+nivel='H'+'C' ORDER BY pasillo+nivel, posicion

insert into plantillas2 select * from plantillas1 where pasillo+nivel='H'+'D' ORDER BY pasillo+nivel, posicion

insert into plantillas2 select * from plantillas1 where pasillo+nivel='H'+'E' ORDER BY pasillo+nivel, posicion

insert into plantillas2 select * from plantillas1 where pasillo+nivel='I'+'A' ORDER BY pasillo+nivel, posicion

insert into plantillas2 select * from plantillas1 where pasillo+nivel='I'+'B' ORDER BY pasillo+nivel, posicion

insert into plantillas2 select * from plantillas1 where pasillo+nivel='I'+'C' ORDER BY pasillo+nivel, posicion

insert into plantillas2 select * from plantillas1 where pasillo+nivel='I'+'D' ORDER BY pasillo+nivel, posicion

insert into plantillas2 select * from plantillas1 where pasillo+nivel='I'+'E' ORDER BY pasillo+nivel, posicion

insert into plantillas2 select * from plantillas1 where pasillo+nivel='J'+'A' ORDER BY pasillo+nivel, posicion

insert into plantillas2 select * from plantillas1 where pasillo+nivel='J'+'B' ORDER BY pasillo+nivel, posicion

insert into plantillas2 select * from plantillas1 where pasillo+nivel='J'+'C' ORDER BY pasillo+nivel, posicion

insert into plantillas2 select * from plantillas1 where pasillo+nivel='J'+'D' ORDER BY pasillo+nivel, posicion

insert into plantillas2 select * from plantillas1 where pasillo+nivel='J'+'E' ORDER BY pasillo+nivel, posicion

insert into plantillas2 select * from plantillas1 where pasillo+nivel='K'+'A' ORDER BY pasillo+nivel, posicion

insert into plantillas2 select * from plantillas1 where pasillo+nivel='K'+'B' ORDER BY pasillo+nivel, posicion

insert into plantillas2 select * from plantillas1 where pasillo+nivel='K'+'C' ORDER BY pasillo+nivel, posicion

insert into plantillas2 select * from plantillas1 where pasillo+nivel='K'+'D' ORDER BY pasillo+nivel, posicion

insert into plantillas2 select * from plantillas1 where pasillo+nivel='K'+'E' ORDER BY pasillo+nivel, posicion

insert into plantillas2 select * from plantillas1 where pasillo+nivel='L'+'A' ORDER BY pasillo+nivel, posicion

insert into plantillas2 select * from plantillas1 where pasillo+nivel='L'+'B' ORDER BY pasillo+nivel, posicion

insert into plantillas2 select * from plantillas1 where pasillo+nivel='L'+'C' ORDER BY pasillo+nivel, posicion

insert into plantillas2 select * from plantillas1 where pasillo+nivel='L'+'D' ORDER BY pasillo+nivel, posicion

insert into plantillas2 select * from plantillas1 where pasillo+nivel='L'+'E' ORDER BY pasillo+nivel, posicion

insert into plantillas2 select * from plantillas1 where pasillo+nivel='C'+'1' ORDER BY pasillo+nivel, posicion

insert into plantillas2 select * from plantillas1 where pasillo+nivel='C'+'S' ORDER BY pasillo+nivel, posicion

insert into plantillas2 select * from plantillas1 where pasillo='Q' ORDER BY pasillo+nivel, posicion

insert into plantillas2 select * from plantillas1 where pasillo='P' ORDER BY pasillo+nivel, posicion

insert into plantillas2 select * from plantillas1 where pasillo='T' ORDER BY pasillo+nivel, posicion

insert into plantillas2 select * from plantillas1 where pasillo+nivel='U'+'S' ORDER BY pasillo+nivel, posicion

insert into plantillas2 select * from plantillas1 where pasillo+nivel NOT IN ( 'A'+'A','A'+'B','A'+'C','A'+'D','A'+'E', 'B'+'A','B'+'B','B'+'C','B'+'D','B'+'E', 'C'+'A','C'+'B','C'+'C','C'+'D','C'+'E',

'D'+'A','D'+'B','D'+'C','D'+'D','D'+'E', 'E'+'A','E'+'B','E'+'C','E'+'D','E'+'E', 'F'+'A','F'+'B','F'+'C','F'+'D','F'+'E',

'G'+'A','G'+'B','G'+'C','G'+'D','G'+'E', 'H'+'A','H'+'B','H'+'C','H'+'D','H'+'E', 'I'+'A','I'+'B','I'+'C','I'+'D','I'+'E',

'J'+'A','J'+'B','J'+'C','J'+'D','J'+'E', 'K'+'A','K'+'B','K'+'C','K'+'D','K'+'E', 'L'+'A','L'+'B','L'+'C','L'+'D','L'+'E',

'C'+'1','C'+'S','U'+'S') AND pasillo not in ('Q','P','T')

ORDER BY pasillo+nivel, posicion

select * from plantillas2

[former_member1009941]

patricio.godoy

Muy buen aporte.
Sin embargo en mi caso me muestra los últimos dos o tres movimientos de la serie en las bodegas relacionadas, ya verifique a detalle sin embargo no existe realcion por fechas. es decir no siempre es el primero ni el ultimo

Me podría ayudar con eso Gracias.
Adjunto imagen.

Si es mucho pedir Tambien se podra agregar la Fecha de Transaccio o de Sistema del Ultimo movimiento
Gracias.