SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- ====================================================================
-- Author:		Francis Bolaños Arias
-- Create date: 03/10/2012 05:06:22
-- Description:	Procedimiento para obtener el 
--              estado de Inventory (Registros sin aplicar)
-- ====================================================================

IF EXISTS (SELECT * FROM sys.objects 
   WHERE object_id = OBJECT_ID(N'DSS_INV_GetEstado') )
DROP Procedure DSS_INV_GetEstado
GO


CREATE PROCEDURE DSS_INV_GetEstado
				(@IdDatos       INT,
				 @Ejercicio     INT)
AS
BEGIN

	DECLARE @vBDAccounting VARCHAR(50)
	
	SELECT TOP 1 @vBDAccounting = base_datos_dest
	FROM   IHSecurity..VST_Enlaces
	WHERE  id_datos_origen = @IdDatos 
	  AND  id_modulo_dest  = 2

	DECLARE @vSql VARCHAR(4000)
	SELECT  @vSql = 'SELECT COUNT(op.id_oper)   AS Total,
						    MAX(op.id_tmov)     AS IdMovimiento,
						    MAX(tm.descripcion) AS Movimiento,
						    MAX(pe.orden) AS id_peri,
						    pe.titulo AS Periodo,
						    ej.ejercicio AS Ejercicio,
						    0 AS Orden
					 FROM        STOKOPER op
					 INNER JOIN  STOKTMOV tm ON op.id_tmov = tm.id_tmov
					 INNER JOIN  {Accounting}..CONTPERI pe ON op.fec_oper BETWEEN pe.desde AND pe.hasta
					 INNER JOIN  {Accounting}..CONTEJER ej ON pe.id_ejer   = ej.id_ejer
					 WHERE    op.estado = 1
					 GROUP BY op.id_tmov, pe.titulo, ej.ejercicio'
	                 
	SET @vSql = REPLACE(@vSql, '{Accounting}', @vBDAccounting)
	
	CREATE TABLE #TempInvEst
					(Total        INT,
					 IdMovimiento INT,
					 Movimiento   VARCHAR(50),
					 id_peri      INT,
					 Periodo      VARCHAR(20),
					 Ejercicio    SMALLINT,
					 Orden        INT)
	
	INSERT INTO #TempInvEst
	EXEC(@vSql)
	
	SELECT  @vSql = 'SELECT pe.orden AS id_peri,
	                        pe.titulo AS Periodo,
							ej.ejercicio AS Ejercicio
	                 FROM       {Accounting}..CONTPERI pe 
					 INNER JOIN {Accounting}..CONTEJER ej ON pe.id_ejer   = ej.id_ejer
					 WHERE  pe.orden <> 0 AND pe.orden <> 99'
	                 
	SET @vSql = REPLACE(@vSql, '{Accounting}', @vBDAccounting)
	
	CREATE TABLE #TempPeriodos
	(
		id_peri    INT,
	    Periodo    VARCHAR(20),
	    Ejercicio  SMALLINT
	)
	
	INSERT INTO #TempPeriodos
	EXEC(@vSql)
	
	SELECT IDENTITY(INT, 1, 1) AS Id, *
	INTO #TempPeriodos2
	FROM #TempPeriodos
	
	SELECT IDENTITY(INT, 1, 1) AS Id, *
	INTO   #TempInvEst2
	FROM   #TempInvEst
	WHERE  Ejercicio = @Ejercicio
	
	DROP TABLE #TempPeriodos
	DROP TABLE #TempInvEst
	
	DECLARE @Inicio    INT = (SELECT MIN(Id) FROM #TempInvEst2)
	DECLARE @Final     INT = (SELECT MAX(Id) FROM #TempInvEst2)
	DECLARE @Cuenta    INT = @Inicio
	DECLARE @IdPeriodo INT = 0
	DECLARE @Periodo   VARCHAR(20) = ''
	DECLARE @IdMovimiento  INT = 0
	DECLARE @Movimiento    VARCHAR(50) = ''
	
	WHILE @Cuenta <= @Final
	BEGIN
		
		DECLARE @PInicio  INT = (SELECT MIN(Id) FROM #TempPeriodos2)
		DECLARE @PFinal   INT = (SELECT MAX(Id) FROM #TempPeriodos2)
		DECLARE @PCuenta  INT = @Inicio
		DECLARE @Existe   INT = 0
		
		SET @IdMovimiento = (SELECT IdMovimiento FROM #TempInvEst2 WHERE Id = @Cuenta)
		SET @Movimiento =   (SELECT Movimiento   FROM #TempInvEst2 WHERE Id = @Cuenta)
		
		WHILE @PCuenta <= @PFinal
		BEGIN
			SET @Periodo   = (SELECT Periodo  FROM #TempPeriodos2 WHERE Id = @PCuenta)
			SET @IdPeriodo = (SELECT id_peri  FROM #TempPeriodos2 WHERE Id = @PCuenta)
			
			SET @Existe  = (SELECT COUNT(*) 
			                   FROM #TempInvEst2   
			                   WHERE Movimiento = @Movimiento 
			                     AND Periodo = @Periodo)
			
			IF @Existe = 0
			BEGIN
				INSERT INTO #TempInvEst2
						(Total,
						 IdMovimiento,
						 Movimiento,
						 id_peri,
						 Periodo,
						 Ejercicio,
						 Orden)
				 VALUES(0,
					    @IdMovimiento,
					    @Movimiento,
					    @IdPeriodo,
					    @Periodo,
					    @Ejercicio,
					    0)
			END
			
			SET @PCuenta = @PCuenta + 1
		END
		
		SET @Cuenta = @Cuenta + 1
		
	END
	
	SELECT * 
	   FROM #TempInvEst2 es
	ORDER BY Orden, Ejercicio, 
	         Movimiento, id_peri ASC
		
END
