SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO

-- execute CreaVista_cuentas

ALTER Procedure CreaVista_Cuentas 
/*
   Date: Mar 16,2016

 

*/

as
/* ---------------------------------------------------
   Enlace 0- Sin contabilidad 1- Otra contabilidad
   ---------------------------------------------------
*/
 	If exists(Select contabil 
	             From STOKTABL 
				 Where contabil = 2 ) 
	         Begin
	         
/* ----------------------------------------------------
   Region 00
    Enlace con Contabilidad
   ----------------------------------------------------
*/

 /* ----------------------------------------------------
    Se busca la base de datos de contabilidad 
    con que esta enlazada si el enlace es con ACC.
	----------------------------------------------------
 */
		Declare @baseCont varchar(50),
         		@idSTOK int

		Select @idSTOK=id_datos 
		   From datos 
		   Where rtrim(ltrim(base_datos))= db_name() 



		Select @basecont = dbo.AllTrim(base_datos) 
		   From datos
		   Where id_datos 
		     in(Select id_datos_destino 
			       From enlace_datos
			       Where id_datos_origen=@idSTOK) 
				     and id_modulo=2

--set @basecont = 'innacc'

/* ----------------------------------------------------
   Region 01
    Cuenta y Centro de Costo
   ----------------------------------------------------
*/
	-- Fran: Repetida
	
		--If exists(Select * From sysobjects Where name='STOKCTACTO')
		--Execute('	Alter  VIEW dbo.STOKCTACTO
		--		AS
		--		Select enco.id_cuenta,enco.tit_cta, ctas.id_cta,ctas.cuenta,ctos.id_cto, 
		--				ctos.cto_gto,ctos.tit_ctgt
		--			From dbo.STOKENCO enco 
		--					INNER JOIN dbo.STOKCTAS ctas ON enco.id_cta = ctas.id_cta
		--					INNER JOIN dbo.STOKCTOS ctos ON enco.id_cto = ctos.id_cto'                                                )
		--Else
		--Execute(' Create VIEW dbo.STOKCTACTO
		--		AS
		--		Select enco.id_cuenta,enco.tit_cta, ctas.id_cta,ctas.cuenta,ctos.id_cto, 
		--				ctos.cto_gto,ctos.tit_ctgt
		--			From dbo.STOKENCO enco 
		--				INNER JOIN dbo.STOKCTAS ctas ON enco.id_cta = ctas.id_cta
		--				INNER JOIN dbo.STOKCTOS ctos ON enco.id_cto = ctos.id_cto'    
		
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------               
		If Exists(Select * From sysobjects Where name = 'STOKCTACTO')	 
        			Execute('	Alter VIEW dbo.STOKCTACTO
					AS  
                    Select enco.id_cuenta,enco.tit_cta, ctas.id_cta, enco.cuenta,
					       ctos.id_ctgt as id_cto,ctos.cto_gto,ctos.tit_ctgt
					From '+ @basecont+'..contplan enco 
					       left join '+@basecont+'..contctgt ctos 
               	     		ON enco.id_ctgt = ctos.id_ctgt 
							inner join dbo.VSTOKCTAS ctas
					ON  RTRIM(LTRIM(enco.cuenta)) = RTRIM(LTRIM(ctas.cuenta))
					    Inner Join STOKEMPR  T ON T.id_empr=enco.id_empr 
					Where T.empresa 
					  in(Select empresa 
					        From STOKtabl) and
 					enco.activa=1 ')
		Else
			Execute('	Create VIEW dbo.STOKCTACTO
					AS  
                    Select enco.id_cuenta,enco.tit_cta, ctas.id_cta, enco.cuenta,
					       ctos.id_ctgt as id_cto,ctos.cto_gto,ctos.tit_ctgt
					From '+ @basecont+'..contplan enco 
					       left join '+@basecont+'..contctgt ctos 
               	     		ON enco.id_ctgt = ctos.id_ctgt 
							inner join dbo.VSTOKCTAS ctas
					ON  RTRIM(LTRIM(enco.cuenta)) = RTRIM(LTRIM(ctas.cuenta))
					    Inner Join STOKEMPR  T ON T.id_empr=enco.id_empr 
					Where T.empresa 
					  in(Select empresa 
					        From STOKtabl) and
 					enco.activa=1 ')  

/* ----------------------------------------------------
   Region 02
    Centro de Costo
   ----------------------------------------------------
*/
	-- Fran: Repetida
	
		--If exists(Select * From sysobjects Where name='VSTOKCTOS')	
		--	Execute('Alter  VIEW dbo.VSTOKCTOS
		--			AS
		--			Select id_ctgt = id_cto,cto_gto,tit_ctgt
		--				From dbo.STOKCTOS')
		--Else
		--	Execute(' Create  VIEW dbo.VSTOKCTOS
		--			  AS
		--			   Select id_ctgt = id_cto,cto_gto,tit_ctgt
		--				  From dbo.STOKCTOS')
		
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
		If Exists(Select * From sysobjects Where name = 'VSTOKCTOS')	              	
			Execute('	Alter VIEW dbo.VSTOKCTOS
					AS
					Select id_ctgt,cto_gto,tit_ctgt
					From '+@basecont+'..contctgt'    )
		Else
			-- Fran: !! No corresponde con la de arriba ni BD Instalación, usaba alter.
			--Execute('	Alter VIEW dbo.VSTOKCTOS
			--		AS
			--		Select id_ctgt,cto_gto,tit_ctgt
			--		From '+@basecont+'..contctgt c 
			--		inner join stokempr t on t.id_empr=c.id_empr 
			--		Where t.empresa in (Select empresa From stoktabl)'    )
			Execute('	Create VIEW dbo.VSTOKCTOS
					AS
					Select id_ctgt,cto_gto,tit_ctgt
					From '+@basecont+'..contctgt'    )

/* ----------------------------------------------------
   Region 03
    Cuentas
   ----------------------------------------------------
*/

	-- Fran: Repetida
	
		--If exists(Select * From sysobjects Where name='VSTOKCTAS')	
		--	Execute('Alter  VIEW dbo.VSTOKCTAS
		--			 AS
		--			  Select * From dbo.STOKCTAS'  )
		--Else
		--	Execute('Create  VIEW dbo.VSTOKCTAS
		--			 AS
		--			  Select * From dbo.STOKCTAS'  )
		
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
		If Exists(Select * From sysobjects Where name = 'VSTOKCTAS')		
			Execute ('ALTER   VIEW dbo.VSTOKCTAS
					AS
					Select MIN(p.id_cuenta) AS id_cta, p.cuenta, 
								MIN(p.tit_cta) as tit_cta
								From '+@basecont+'.dbo.CONTPLAN p 
									INNER JOIN dbo.STOKEMPR t ON t.id_empr = p.id_empr
									inner join stoktabl l on l.empresa=t.empresa
								Where (p.activa = 1) 
								GROUP BY p.cuenta'
								)
		Else
			Execute ('CREATE   VIEW dbo.VSTOKCTAS
					AS
					Select MIN(p.id_cuenta) AS id_cta, p.cuenta, 
								MIN(p.tit_cta) as tit_cta
								From '+@basecont+'.dbo.CONTPLAN p 
									INNER JOIN dbo.STOKEMPR t ON t.id_empr = p.id_empr
									inner join stoktabl l on l.empresa=t.empresa
								Where (p.activa = 1) 
								GROUP BY p.cuenta'
								)

/* ----------------------------------------------------
   Region 04
    Tipos de movimientos u Operaciones
   ----------------------------------------------------
*/
	-- Fran: Repetida

		--If exists(Select * From sysobjects Where name='VSTOKASTO')	
 	--		Execute ('Alter  VIEW dbo.VSTOKASTO
		--			  AS
		--			  Select * From STOKASTO' )	
		--Else
		--	Execute ('Create  VIEW dbo.VSTOKASTO
		--				AS
		--				Select * From STOKASTO')
		
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
		If Exists(Select * From sysobjects Where name = 'VSTOKASTO')	
			 Execute ('	Alter  VIEW dbo.VSTOKASTO
					AS
					Select     *
					From '+@basecont+'..CONTASTO Where  asiento > 400 and asiento <450 ') 
		Else                    
  			Execute ('	Create  VIEW dbo.VSTOKASTO
					AS
					Select     *
					From '+@basecont+'..CONTASTO Where  asiento > 400 and asiento <450 ')
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

/* ----------------------------------------------------
   Region 05
    Departamentos
   ----------------------------------------------------
*/

	-- Fran: Repetida
	
		--If exists(Select * From sysobjects Where name='VSTOKAPTE')	
		--	Execute ('Alter  VIEW dbo.VSTOKAPTE
		--			  AS
		--			  Select * From STOKAPTE'  )	
		--Else	
		--	Execute ('Create  VIEW dbo.VSTOKAPTE
		--			  AS
		--			  Select * From STOKAPTE'  )
		
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
	If Exists(Select * From sysobjects Where name = 'VSTOKAPTE')	
		Execute (	'Alter VIEW dbo.VSTOKAPTE
				AS
				Select     *
				From '+@basecont+'..CONTAPTE Where  asiento > 400 and asiento <450 ')
	Else
        Execute (	'Create  VIEW dbo.VSTOKAPTE
				AS
				Select     *
				From '+@basecont+'..CONTAPTE Where  asiento > 400 and asiento <450 ')

 End

/* --------------------------------------------------------------------
     Enlace de Empresas y Otros Datos
   --------------------------------------------------------------------
*/
	  IF EXISTS (SELECT * 
                    FROM sys.objects 
                    WHERE object_id = OBJECT_ID(N'STOKEMPR') )
      DROP View STOKEMPR

			Execute ('Create VIEW STOKEMPR
			AS
			Select     *
			From '+@basecont+'..CONTEMPR ') 

	  IF EXISTS (SELECT * 
                FROM sys.objects 
                WHERE object_id = OBJECT_ID(N'STOKPERI') )
      DROP View STOKPERI

			Execute ('Create VIEW STOKPERI
			AS
			Select     *
			From '+@basecont+'..CONTPERI ')
			
		-- Fran: !! No corresponde con la de BD Instalación

		 -- IF EXISTS (SELECT * 
			--		FROM sys.objects 
			--		WHERE object_id = OBJECT_ID(N'STOKEJER') )
		 -- DROP View STOKEJER

			--Execute ('	Create  VIEW dbo.STOKEJER
			--AS
			--Select   T.id_ejer,E.ejercicio
			--From ' + @basecont + '..CONTTABL T Inner Join  '
			--       + @basecont + '..CONTEJER E On T.id_ejer=E.id_ejer' )
			
			IF EXISTS (SELECT * 
					FROM sys.objects 
					WHERE object_id = OBJECT_ID(N'STOKEJER') )
		    DROP View STOKEJER

			Execute ('	Create  VIEW dbo.STOKEJER
			AS
			Select   E.id_ejer,E.ejercicio
			From ' + @basecont + '..CONTTABL T Inner Join  '
			       + @basecont + '..CONTEJER EA On T.id_ejer=EA.id_ejer Inner Join '
			       + @basecont + '..CONTEJER E On EA.ejercicio <= E.ejercicio' )

/* --------------------------------------------------------------------
     Vistas Nuevas
   --------------------------------------------------------------------
*/

			IF EXISTS (SELECT * 
					FROM sys.objects 
					WHERE object_id = OBJECT_ID(N'VContDiar') )
			DROP View VContDiar

			Execute ('Create  VIEW VContDiar
			AS
			Select * From ' + @basecont + '..VContDiar' )

			IF EXISTS (SELECT * 
				FROM sys.objects 
				WHERE object_id = OBJECT_ID(N'VContAsie') )
			DROP View VContAsie

			Execute ('	Create  VIEW VContAsie
			AS
			Select * From ' + @basecont + '..VContAsie' )

			IF EXISTS (SELECT * 
					FROM sys.objects 
					WHERE object_id = OBJECT_ID(N'Vst_Ejercicio') )
			DROP View Vst_Ejercicio

			Execute ('	Create  VIEW Vst_Ejercicio
			AS
			Select * From ' + @basecont + '..Vst_Ejercicio' )


 Return


