
/****** Object:  StoredProcedure [dbo].[Create_View_ALMAARTI]    Script Date: 02/01/2013 15:07:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO

DROP      PROCEDURE [dbo].[Create_View_ALMAAREX]
GO
-- execute Create_View_ALMAARTI 'InnInv','ORLANDO'
CREATE      PROCEDURE [dbo].[Create_View_ALMAAREX] @base_datos varchar(50),
	 			          @server     varchar(40)
AS
BEGIN
  SET NOCOUNT ON

  declare @path  varchar(255),
	  @c     varchar (8000),
          @path1 varchar(255)

  Select @base_datos = LTrim(RTrim(@base_datos)),
	 @server     = LTrim(RTrim(@server))

  if @@SERVERNAME = @server
    Select @path = @base_datos + '..'
  else 
    Select @path = @server + '.' + @base_datos + '.dbo.'

  Select @path1 = @base_datos + '.dbo.'
 
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ALMAAREX]') and OBJECTPROPERTY(id, N'IsView') = 1)
   drop view [dbo].[ALMAAREX]

  SET @c = 'Create  VIEW dbo.ALMAAREX AS
     SELECT id_aral    = A.ID_ARTI,
  	    cod_aral   = Convert(char(20), LTRIM(RTRIM(G.CODIGO))+LTRIM(RTRIM(F.CODIGO))+ LTRIM(RTRIM(A.CODIGO))),
 	    desc_aral  = A.DESCRIPCION,  
	    id_alma    = AL.id_alma , 
	    id_unme    = A.id_unme_se, 
            p_costo    = CASE WHEN AE.id_alex is null or AE.existencia = 0
				     THEN CASE WHEN COALESCE(A.ult_precio_cmp,0) = 0
					       THEN Convert(money, ROUND(' + @path1 + 'GetCostoLineaSubMayor(' + @path1 + 'GetUltimaOperacionArtiAlmacen(A.id_arti)) / 
													        A.cant_unme_se, 2) )
                                               ELSE Convert(money, ROUND( A.ult_precio_cmp / COALESCE(A.ult_tasa,1) / A.cant_unme_al / A.cant_unme_se, 2) )
				     	  END
				     ELSE Convert(money, ROUND(AE.importe / AE.existencia / A.cant_unme_se,2) )
				END,
	    id_unme_co   = A.id_unme_co,
	    cant_unme_al = A.cant_unme_al,
	    id_unme_al   = A.id_unme_al, 				
	    cant_unme_se = A.cant_unme_se,
	    existencia   = (COALESCE(' + REPLACE(@path1, '..', '.dbo.') + 'ExistenciaEnAlmacen(A.id_arti, NULL), 0) + 
						COALESCE(' + REPLACE(@path1, '..', '.dbo.') + 'ExistenciaEnSeccion(A.id_arti, NULL), 0)) / A.cant_unme_se		
   FROM ' + @path + 'STOKARTI A
     INNER JOIN ' + @path + 'STOKFAMI F    ON A.ID_FAMI  = F.ID_FAMI
     INNER JOIN ' + @path + 'STOKGRUP G    ON G.ID_GRUP  = F.ID_GRUP 
     INNER JOIN ' + @path + 'STOKALFA AL   ON F.ID_FAMI  = AL.ID_FAMI  
     LEFT JOIN  ' + @path + 'STOKALEX AE   ON A.ID_ARTI  = AE.ID_ARTI  and 
				              AL.ID_ALMA = AE.ID_ALMA'
				              
 print @c

  Execute(@c)
end





