on 04-29-2014 10:57 PM
Buenas tardes,
Amigos necesito elaborar un reporte que me muestre lo siguiente:
y la verdad que no se por donde comenzar, quizás ustedes me puedan ayudar o dar luces con este reporte?
Agradezco su colaboración de antemano.
Saludos
Hola pablo,
Te puedo facilitar un query que utilice para hacer un reporte de control de inventario que funciona parecido al de Informe de auditoria de stocks de SBO.
Básicamente si lo generas te muestra el recorrido del articulo en entradas y salidas de mercancia y saldo , ahí puedes visualizar cuando quedo en cero y cuando fue su ultima entrada de mercancia, tiene varios campos de usuarios que no utilizaras así como filtros como la sucursal, espero que te ayude como guia.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/******
******/
CREATE Procedure [dbo].[Sp_ControlInventario]
@Sucursal Varchar(15),
@Fini dateTIME,
@Ffin dateTIME,
@Grupo int
as
begin
SET NOCOUNT ON
DECLARE @ItemCode nvarchar(20), @ItemName nvarchar(100), @U_FechReal datetime,
@U_Veh_Port nvarchar(100),@Arancelario nvarchar(100), @U_PCIF Numeric(19,6), @Tasa Numeric(19,6),
@DocDate datetime, @ENTRADA Numeric(19,6), @SALIDA Numeric(19,6), @SALDO Numeric(19,6),
@OBSERVACION nvarchar(100), @ID INT, @SALDO_TOTAL Numeric(19,6)
CREATE TABLE #MyTempTable (
ItemCode nvarchar(20), ItemName nvarchar(100), U_FechReal datetime,
U_Veh_Port nvarchar(100),Arancelario nvarchar(100), U_PCIF Numeric(19,6), Tasa Numeric(19,6),
DocDate datetime, ENTRADA Numeric(19,6), SALIDA Numeric(19,6), SALDO Numeric(19,6),
OBSERVACION nvarchar(100), ID INT );
DECLARE ControlInventario_cursor CURSOR FOR
--ENTRADAS
--Query que busca todas las transferencias de stock
SELECT T1.ItemCode, T1.ItemName, (SELECT OPDN.U_FechReal FROM OPDN WHERE OPDN.DocNum = T3.BaseRef ) AS FECHAREAL,
(SELECT OPDN.U_Veh_Port FROM OPDN WHERE OPDN.DocNum = T3.BaseRef ) AS VEHICULO,
T1.U_CodArancel , T3.U_PCIF, (T3.U_PCIF * 0.01) as TASA , T2.DocDate, T3.Quantity AS ENTRADA, 0 AS SALIDA, 0 AS SALDO,
'Transferencia Stock ' + Cast((T2.DocNum) AS varchar(15)) AS OBSERVACION , 1 AS ID
FROM OITM T1, OWTR T2 , WTR1 T3
WHERE
T3.ItemCode = T1.ItemCode AND
T2.DocEntry = T3.DocEntry AND
T2.Filler IN('01' , '02') AND
(T3.Project=@Sucursal or 'Todas' = @Sucursal) AND
T2.DocDate Between @Fini and @Ffin AND
T1.ItmsGrpCod =@Grupo
Union All
--- Query que busca las Entradas al Inventario
--- La fecha real y vehiculo porteador para este query no es necesario
SELECT T1.ItemCode, T1.ItemName, '' AS FECHAREAL,
'' AS VEHICULO,
T1.U_CodArancel, T3.U_PCIF AS CIF, (T3.U_PCIF * 0.01) AS CCIF , T2.DocDate, T3.Quantity, 0 AS SALIDA, 0 AS SALDO,
isnull('Entrada de Inventario ' + T2.Comments, 'Entrada de Inventario') AS OBSERVACION , 1 AS ID
FROM OITM T1 , OIGN T2, IGN1 T3
WHERE
T3.ItemCode = T1.ItemCode AND
T2.DocEntry = T3.DocEntry AND
(T3.Project=@Sucursal or 'Todas' = @Sucursal) AND
T2.DocDate Between @Fini and @Ffin AND
T1.ItmsGrpCod =@Grupo
Union All
--- Query que busca las notas de creditos
--- Las Notas de Creditos deben esta agrepadas (Consolidadas)
--- La fecha real y vehiculo porteador para este query no es necesario
SELECT T1.ItemCode, T1.ItemName, '' AS FECHAREAL,
'' AS VEHICULO, T1.U_CodArancel,
sum(T3.U_PCIF) AS CIF, (sum(T3.U_PCIF) * 0.01) AS TASA ,
T2.DocDate, Sum(T3.Quantity), 0 AS SALIDA, 0 AS SALDO,
isnull( 'Nota de Credito ' + Cast(max(T2.NumAtCard) AS varchar(15)), 'Nota de Credito' ) AS OBSERVACION , 1 AS ID
FROM OITM T1, ORIN T2, RIN1 T3
WHERE
T3.ItemCode = T1.ItemCode AND
T2.DocEntry = T3.DocEntry AND
(T3.Project=@Sucursal or 'Todas' = @Sucursal) AND
T2.DocDate Between @Fini and @Ffin AND
T1.ItmsGrpCod =@Grupo
GRoup By T1.ItemCode, T1.ItemName, T2.DocDate, T1.U_CodArancel
----SALIDAS
Union All
---Query que busca las facturas de cliente con articulos
---Estas facturas deben estar consolidadas
SELECT T1.ItemCode, T1.ItemName, '' AS FECHAREAL,
'' AS VEHICULO, T1.U_CodArancel,
sum(T3.U_PCIF) AS CIF, (sum(T3.U_PCIF) * 0.01) AS TASA ,
T2.DocDate, 0 AS ENTRADA, Sum(T3.Quantity) AS SALIDA, 0 AS SALDO,
'Factura de clientes ' + Cast(max(T2.NumAtCard ) AS varchar(15)) AS OBSERVACION , 2 AS ID
FROM OITM T1, OPCH T2, PCH1 T3
WHERE
T3.ItemCode = T1.ItemCode AND
T2.DocEntry = T3.DocEntry AND
(T3.Project=@Sucursal or 'Todas' = @Sucursal) AND
T2.DocDate Between @Fini and @Ffin AND
T1.ItmsGrpCod =@Grupo
GRoup By T1.ItemCode, T1.ItemName, T2.DocDate, T1.U_CodArancel
--- Query salida de inventario
Union All
SELECT T1.ItemCode, T1.ItemName, '' AS FECHAREAL,
'' AS VEHICULO, T1.U_CodArancel,
T3.U_PCIF AS CIF, (T3.U_PCIF * 0.01) AS CCIF , T2.DocDate, 0 AS ENTRADA, T3.Quantity AS SALIDA, 0 AS SALDO,
Isnull('Salida de Inventario ' + T2.Comments, 'Salida de Inventario') AS OBSERVACION , 2 AS ID
FROM OITM T1 , OIGE T2, IGE1 T3
WHERE
T3.ItemCode = T1.ItemCode AND
T2.DocEntry = T3.DocEntry AND
(T3.Project=@Sucursal or 'Todas' =@Sucursal )AND
T2.DocDate Between @Fini and @Ffin AND
T1.ItmsGrpCod =@Grupo
Order By T1.ItemCode, ID
OPEN ControlInventario_cursor
FETCH NEXT FROM ControlInventario_cursor
INTO @ItemCode , @ItemName , @U_FechReal , @U_Veh_Port , @Arancelario, @U_PCIF , @Tasa ,
@DocDate , @ENTRADA , @SALIDA , @SALDO ,
@OBSERVACION , @ID
WHILE @@FETCH_STATUS = 0
BEGIN
--Se llena la tabla temporal.
IF @ENTRADA > 0
BEGIN
SELECT @SALDO_TOTAL = (@ENTRADA + (SELECT ISNULL( SUM(ENTRADA), 0) FROM #MyTempTable WHERE ItemCode =@ItemCode AND ID= 1 )) -(SELECT ISNULL( SUM(SALIDA), 0) FROM #MyTempTable WHERE ItemCode =@ItemCode AND ID =2 )
END
IF @SALIDA > 0
BEGIN
SELECT @SALDO_TOTAL =(SELECT ISNULL( SUM(ENTRADA), 0) FROM #MyTempTable WHERE ItemCode =@ItemCode AND ID =1 ) - (@SALIDA + (SELECT ISNULL( SUM(SALIDA), 0) FROM #MyTempTable WHERE ItemCode =@ItemCode AND ID= 2 ))
END
INSERT INTO #MyTempTable VALUES (
@ItemCode , @ItemName , @U_FechReal ,
@U_Veh_Port ,@Arancelario, @U_PCIF , @Tasa ,
@DocDate , @ENTRADA , @SALIDA ,
@SALDO_TOTAL,
@OBSERVACION , @ID
);
FETCH NEXT FROM ControlInventario_cursor
INTO @ItemCode , @ItemName , @U_FechReal , @U_Veh_Port ,@Arancelario, @U_PCIF , @Tasa ,
@DocDate , @ENTRADA , @SALIDA , @SALDO ,
@OBSERVACION , @ID
END
CLOSE ControlInventario_cursor
DEALLOCATE ControlInventario_cursor
SELECT
ItemCode AS [CODIGO] , ItemName AS [DESCRIPCION], U_FechReal AS [FECHA DE LLEGADA],
U_Veh_Port AS [VEHICULO PORTEADOR], Arancelario AS [CÓDIGO ARANCELARIO] , U_PCIF AS [VALOR CIF], Tasa AS [TASA POR SERVICIO DE ADUANAS],
DocDate AS [FECHA DE TRANSACCIÓN], ENTRADA AS [ENTRADA], SALIDA AS [SALIDA], SALDO AS [SALDO],
OBSERVACION AS [OBSERVACIONES]
FROM #MyTempTable
end
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Muchas gracias por la respuesta amigo Wuilmer Venegas me será de mucha ayuda para lograr lo que necesito.
Saludos
User | Count |
---|---|
103 | |
12 | |
11 | |
6 | |
5 | |
4 | |
3 | |
3 | |
3 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.