DROP PROC IN_INVENTORYDESInsProc
GO

/*************************************************************************************************************************/
CREATE PROCEDURE IN_INVENTORYDESInsProc
(   @INVTIDSKU        UD_IDCORPORATIVO,
    @INVTIDSKUDES     UD_IDCORPORATIVO,
    @WTSTK2           tinyint)
AS
BEGIN
    DECLARE @ERROR Integer
    SELECT @ERROR=0   
    INSERT INTO IN_INVENTORYDES(INVTIDSKU,
                                INVTIDSKUDES,
                                WTSTK2)
    VALUES(@INVTIDSKU,
           @INVTIDSKUDES,
           @WTSTK2)


    SET @ERROR=@@error
    
    IF (@ERROR!=0)
    BEGIN       
        RETURN(1)    
    END

    RETURN(0)

END
GO

/*************************************************************************************************************************/

DROP PROC IN_INVENTORYDATDelProc
GO

CREATE PROCEDURE IN_INVENTORYDATDelProc
(
  @INVTIDSKU UD_IDCORPORATIVO)
AS
BEGIN
DECLARE @ERROR INTEGER    
    DELETE
        FROM IN_INVENTORYDAT
        WHERE INVTIDSKU = @INVTIDSKU

    SET @ERROR=@@error
    
    IF (@ERROR!=0)
    BEGIN       
        RETURN(1)    
    END

    RETURN(0)
END

/*************************************************************************************************************************/
GO

DROP PROC IN_INVENTORYDESDelProc
GO
 
CREATE PROCEDURE IN_INVENTORYDESDelProc
(
    @INVTIDSKU        UD_IDCORPORATIVO)
AS
BEGIN
DECLARE @ERROR INTEGER      
    DELETE
      FROM IN_INVENTORYDES
    WHERE INVTIDSKU = @INVTIDSKU

    SET @ERROR=@@error
    
    IF (@ERROR!=0)
    BEGIN       
        RETURN(1)    
    END

    RETURN(0)
END

/*************************************************************************************************************************/
GO

DROP PROC IN_INVENTORYDESUpdProc
GO

CREATE PROCEDURE IN_INVENTORYDESUpdProc
(   @INVTIDSKU        UD_IDCORPORATIVO,
    @INVTIDSKUDES     UD_IDCORPORATIVO,
    @WTSTK2           tinyint)
AS
BEGIN
    DECLARE @ERROR Integer
    SELECT @ERROR=0   

    UPDATE IN_INVENTORYDES SET INVTIDSKUDES=@INVTIDSKUDES,
                               WTSTK2=@WTSTK2
           WHERE INVTIDSKU=@INVTIDSKU
    SET @ERROR=@@error
    
    IF (@ERROR!=0)
    BEGIN       
        RETURN(1)    
    END

    RETURN(0)

END
/*************************************************************************************************************************/
go

drop proc IN_INVENTORYDESInsProc
go

CREATE PROCEDURE IN_INVENTORYDESInsProc
(   @INVTIDSKU        UD_IDCORPORATIVO,
    @INVTIDSKUDES     UD_IDCORPORATIVO,
    @WTSTK2           tinyint)
AS
BEGIN
    DECLARE @ERROR Integer
    SELECT @ERROR=0   
    INSERT INTO IN_INVENTORYDES(INVTIDSKU,
                                INVTIDSKUDES,
                                WTSTK2)
    VALUES(@INVTIDSKU,
           @INVTIDSKUDES,
           @WTSTK2)


    SET @ERROR=@@error
    
    IF (@ERROR!=0)
    BEGIN       
        RETURN(1)    
    END

    RETURN(0)

END
/*************************************************************************************************************************/
go

drop proc IN_INVENTORYDATDelProc
go

CREATE PROCEDURE IN_INVENTORYDATDelProc
(
  @INVTIDSKU UD_IDCORPORATIVO)
AS
BEGIN
DECLARE @ERROR INTEGER    
    DELETE
        FROM IN_INVENTORYDAT
        WHERE INVTIDSKU = @INVTIDSKU

    SET @ERROR=@@error
    
    IF (@ERROR!=0)
    BEGIN       
        RETURN(1)    
    END

    RETURN(0)
END

go

/*************************************/
DROP PROC IN_REPORTEFS210Proc
GO

Create procedure IN_REPORTEFS210Proc
@FechaDia CHAR(2),
@FechaMes CHAR(2),
@Fechaanio CHAR(4),
@FechaDiaIni char(2),   
@FechaMesIni char(2),  
@FechaAnioIni char(4),   
@FechaMesHistorica char(2),
@FechaDiaHasta char(2),
@FechaMesHasta char(2),
@FechaAnioHasta char(4),
@CadenaAlmacenes  varchar(8000),
@CadenaCategorias varchar(800),
@CadenaSubCategorias varchar(800),
@CATEGOSUBCATEG char(3)
as  
  begin
  DECLARE @cade varchar(8000)
  DECLARE @FechaDesde smalldatetime
  DECLARE @FechaInicial smalldatetime
  DECLARE @FechaHasta smalldatetime
  DECLARE @TIPO UD_TYPEMOVEID  
  
  set @FechaInicial=Cast(@FechaMesIni + '-' + @FechaDiaIni + '-' + @FechaAnioIni as smalldatetime)
  set @FechaDesde= Cast(@FechaMes + '-' +@FechaDia + '-' + @Fechaanio as smalldatetime)
  set @FechaHasta = Cast(@FechaMesHasta + '-' + @FechaDiaHasta + '-' + @FechaanioHasta as smalldatetime)
   
  if @CATEGOSUBCATEG='CAT' 
    begin
      select @cade=" SELECT in_kdxhist.siteid,IN_INVENTORY.CLASSID,SUM(QTYEND" + @FechaMesHistorica +
               ") AS [Inicial],0 as [QTYRESULT]" +
               " from (in_kdxhist inner join IN_INVENTORY on IN_KDXHIST.INVTIDSKU=IN_INVENTORY.INVTIDSKU)" +
               " inner join in_category on IN_INVENTORY.CLASSID=IN_CATEGORY.CLASSID " +
               " WHERE SITEID in (" + RTRIM(LTRIM(@CadenaAlmacenes)) + ") and " + 
               " IN_INVENTORY.CLASSID in (" + RTRIM(LTRIM(@CadenaCategorias)) + ") and " +
               " yearhist="  
               + RTRIM(LTRIM(STR(year(@FechaDesde)))) + " GROUP BY IN_INVENTORY.CLASSID,in_kdxhist.siteid " +  
               " UNION  ALL " +     
               " SELECT INTRAMOV.SITEID,IN_INVENTORY.CLASSID,0 as [Inicial],SUM(CASE SWTINOUT WHEN 1 THEN QTY WHEN 0 THEN " +
               " (QTY * - 1) END) AS QTYRESULT " +
               " from (INTRAMOV inner join IN_INVENTORY on INTRAMOV.INVTIDSKU=IN_INVENTORY.INVTIDSKU) " +  
               " inner join in_category on IN_INVENTORY.CLASSID=IN_CATEGORY.CLASSID " +
               " where SITEID in (" + RTRIM(LTRIM(@CadenaAlmacenes)) + ") and " +
               " IN_INVENTORY.CLASSID in (" + RTRIM(LTRIM(@CadenaCategorias)) + ") and " +
               " intrdateproce >='" + rtrim(ltrim(Cast(@FechaInicial as char(100)))) + "' and  " +
	       " intrdateproce <'" + rtrim(ltrim(Cast(@FechaDesde as char(100)))) + "' " +    
               " GROUP BY IN_INVENTORY.CLASSID,INTRAMOV.SITEID"   

       /*Se crea Tabla Temporal para Almacenar datos Obtenidos */

      Create Table #TMP_KARDEX
        (SITEID  char(5),
         CLASSID CHAR(5),
         INICIAL numeric(7,3),
         QTYRESULT numeric(7,3))  
      insert into #TMP_KARDEX exec(@cade)

      /*Se crea un Resumen de los Datos Creados en #TMP_KARDEXRESUMEN*/ 
  
      select #TMP_KARDEX.SITEID,SITE.NAME,#TMP_KARDEX.CLASSID,IN_CATEGORY.DESCR as [Descripcion],
             SUM(INICIAL)+SUM (QTYRESULT) AS [SALDO INICIAL] into #TMP_KARDEXRESUMEN 
             from (#TMP_KARDEX INNER JOIN SITE ON (#TMP_KARDEX.SITEID=SITE.SITEID))
                   INNER JOIN IN_CATEGORY ON #TMP_KARDEX.CLASSID=IN_CATEGORY.CLASSID              
             GROUP BY #TMP_KARDEX.SITEID,SITE.NAME,#TMP_KARDEX.CLASSID,IN_CATEGORY.DESCR

      /*Seleccionar el Typo de Movimientos de Inventarios (Por ventas)y excluirlo del Listado */ 
  
      Select @TIPO=INTYPESALES from in_setup 
      select siteid,classid,SUM (CASE TYPEDOCID
                          WHEN @TIPO THEN                          
                             QTYRESULT * -1 
                          ELSE
                             0                    
                          END)AS [QTYRESULT] INTO #VW_KARDEXFS210VENTAS 
                       from VW_KARDEXFS210  where
                       INTRDATEPROCE BETWEEN @FechaDesde AND @FechaHasta
                       GROUP BY SITEID,CLASSID      

      /*Guardar en la Tabla #TMPVENTAS el Total de Ventas Expresadas en Cantidad */  

      select VW_SITECATEGORY.*,ISNULL(QTYRESULT,0) AS [QTYRESULT]into #TMPVENTAS 
                            from VW_SITECATEGORY LEFT JOIN #VW_KARDEXFS210VENTAS ON 
                                 VW_SITECATEGORY.SITEID = #VW_KARDEXFS210VENTAS.SITEID AND
                                 VW_SITECATEGORY.CLASSID = #VW_KARDEXFS210VENTAS.CLASSID

      /* Realiza el Union para Juntar la Vista  VW_KARDEXFS210 con el Total de Ventas  */ 
 
       SELECT #TMP_KARDEXRESUMEN.*,VW_KARDEXFS210.* FROM VW_KARDEXFS210 INNER JOIN #TMP_KARDEXRESUMEN ON
                                                   (VW_KARDEXFS210.SITEID=#TMP_KARDEXRESUMEN.SITEID AND 
   	       		                            VW_KARDEXFS210.CLASSID=#TMP_KARDEXRESUMEN.CLASSID) AND 
                       	  		            INTRDATEPROCE BETWEEN @FechaDesde AND @FechaHasta AND
                                                    TYPEDOCID <> @TIPO 
       UNION ALL  
       SELECT #TMP_KARDEXRESUMEN.*,#TMPVENTAS.* FROM #TMP_KARDEXRESUMEN INNER JOIN #TMPVENTAS ON 
                                                     #TMP_KARDEXRESUMEN.SITEID=#TMPVENTAS.SITEID AND
                                                     #TMP_KARDEXRESUMEN.CLASSID=#TMPVENTAS.CLASSID    
     end  
   else     
     begin
        /*Si el Reporte es por SubCategoria */
          select @cade=" SELECT in_kdxhist.siteid,IN_INVENTORY.CLASSUBID,SUM(QTYEND" + @FechaMesHistorica +
                 ") AS [Inicial],0 as [QTYRESULT]" +
                 " from (in_kdxhist inner join IN_INVENTORY on IN_KDXHIST.INVTIDSKU=IN_INVENTORY.INVTIDSKU)" +
                 " inner join IN_SUBCATEGORY on IN_INVENTORY.CLASSUBID=IN_SUBCATEGORY.CLASSUBID " +
                 " WHERE SITEID in (" + RTRIM(LTRIM(@CadenaAlmacenes)) + ") and " + 
                 " IN_INVENTORY.CLASSUBID in (" + RTRIM(LTRIM(@CadenaSubCategorias)) + ") and " +
                 " yearhist="  
                 + RTRIM(LTRIM(STR(year(@FechaDesde)))) + " GROUP BY IN_INVENTORY.CLASSUBID,in_kdxhist.siteid " +  
                 " UNION  ALL " +     
                 " SELECT INTRAMOV.SITEID,IN_INVENTORY.CLASSUBID,0 as [Inicial],SUM(CASE SWTINOUT WHEN 1 THEN QTY WHEN 0 THEN " +
                 " (QTY * - 1) END) AS QTYRESULT " +
                 " from (INTRAMOV inner join IN_INVENTORY on INTRAMOV.INVTIDSKU=IN_INVENTORY.INVTIDSKU) " +  
                 " inner join IN_SUBCATEGORY on IN_INVENTORY.CLASSUBID=IN_SUBCATEGORY.CLASSUBID " +
                 " where SITEID in (" + RTRIM(LTRIM(@CadenaAlmacenes)) + ") and " +
                 " IN_INVENTORY.CLASSUBID in (" + RTRIM(LTRIM(@CadenaSubCategorias)) + ") and " +
                 " intrdateproce >='" + rtrim(ltrim(Cast(@FechaInicial as char(100)))) + "' and  " +
	         " intrdateproce <'" + rtrim(ltrim(Cast(@FechaDesde as char(100)))) + "' " +    
                 " GROUP BY IN_INVENTORY.CLASSUBID,INTRAMOV.SITEID"   

       /*Se crea Tabla Temporal para Almacenar datos Obtenidos */

      Create Table #TMP_KARDEXSUBCAT
        (SITEID  char(5),
         CLASSUBID CHAR(5),
         INICIAL numeric(7,3),
         QTYRESULT numeric(7,3))  
   
      insert into #TMP_KARDEXSUBCAT exec(@cade)
      /*Se crea un Resumen de los Datos Creados en #TMP_KARDEXRESUMENSUBCAT */ 
      
      select #TMP_KARDEXSUBCAT.SITEID,SITE.NAME,#TMP_KARDEXSUBCAT.CLASSUBID,IN_SUBCATEGORY.DESCR as [Descripcion],
             SUM(INICIAL)+SUM (QTYRESULT) AS [SALDO INICIAL] into #TMP_KARDEXRESUMENSUBCAT 
             from #TMP_KARDEXSUBCAT INNER JOIN SITE ON #TMP_KARDEXSUBCAT.SITEID=SITE.SITEID
                   INNER JOIN IN_SUBCATEGORY ON #TMP_KARDEXSUBCAT.CLASSUBID=IN_SUBCATEGORY.CLASSUBID                   
             GROUP BY #TMP_KARDEXSUBCAT.SITEID,SITE.NAME,
                      #TMP_KARDEXSUBCAT.CLASSUBID,IN_SUBCATEGORY.DESCR
     
      /*Seleccionar el Typo de Movimientos de Inventarios (Por ventas)y excluirlo del Listado */ 
  
      Select @TIPO=INTYPESALES from in_setup 
      select siteid,CLASSUBID,SUM (CASE TYPEDOCID
                          WHEN @TIPO THEN                          
                             QTYRESULT
                          ELSE
                             0                    
                          END)AS [QTYRESULT] INTO #VW_KARDEXFS210VENTASSUBCAT 
                       from VW_KARDEXFS210SUBCATEG  where
                       INTRDATEPROCE BETWEEN @FechaDesde AND @FechaHasta
                       GROUP BY SITEID,CLASSUBID      

      /*Guardar en la Tabla #TMPVENTASSUBCAT el Total de Ventas Expresadas en Cantidad */  

      select VW_SITESUBCATEGORY.*,ISNULL(QTYRESULT,0) AS [QTYRESULT]into #TMPVENTASSUBCAT 
                               from VW_SITESUBCATEGORY LEFT JOIN #VW_KARDEXFS210VENTASSUBCAT ON 
                                    VW_SITESUBCATEGORY.SITEID =  #VW_KARDEXFS210VENTASSUBCAT.SITEID AND
                                    VW_SITESUBCATEGORY.CLASSUBID = #VW_KARDEXFS210VENTASSUBCAT.CLASSUBID
        
 
      /* Realiza el Union para Juntar la Vista  VW_KARDEXFS210SUBCATEG con el Total de Ventas  */ 
 
       SELECT #TMP_KARDEXRESUMENSUBCAT.*,VW_KARDEXFS210SUBCATEG.* 
					FROM VW_KARDEXFS210SUBCATEG INNER JOIN #TMP_KARDEXRESUMENSUBCAT ON
                                            (VW_KARDEXFS210SUBCATEG.SITEID=#TMP_KARDEXRESUMENSUBCAT.SITEID AND 
   	       		                     VW_KARDEXFS210SUBCATEG.CLASSUBID=#TMP_KARDEXRESUMENSUBCAT.CLASSUBID) AND 
                       	  		     INTRDATEPROCE BETWEEN @FechaDesde AND @FechaHasta AND
                                             TYPEDOCID <> @TIPO 
       UNION ALL  
       SELECT #TMP_KARDEXRESUMENSUBCAT.*,#TMPVENTASSUBCAT.* 
                                            FROM #TMP_KARDEXRESUMENSUBCAT INNER JOIN #TMPVENTASSUBCAT ON 
                                            #TMP_KARDEXRESUMENSUBCAT.SITEID=#TMPVENTASSUBCAT.SITEID AND
                                            #TMP_KARDEXRESUMENSUBCAT.CLASSUBID=#TMPVENTASSUBCAT.CLASSUBID    
        
     end 
end
GO

/*************************************************************************************************************************/
DROP PROC IN_BucaArticuloProc
GO

CREATE PROCEDURE IN_BucaArticuloProc
(   @INVTIDSKU UD_IDCORPORATIVO,
    @CORRNBR INT)

AS
BEGIN
      SELECT INVTIDSKU
      FROM TMP_IN_INTRAITEM
      WHERE INVTIDSKU=@INVTIDSKU AND
	    CORRNBR=@CORRNBR

END
/*************************************************************************************************************************/
GO
DROP PROC IN_CargaInventoryAlterDataTempProc
GO

Create Procedure IN_CargaInventoryAlterDataTempProc
(@CORRNBR            int,     
 @INVTIDSKU          UD_IDCORPORATIVO)  
as
 BEGIN TRAN

 INSERT INTO TMP_IN_INVENTORYALTER SELECT @CORRNBR,IN_INVENTORYALTER.* FROM IN_INVENTORYALTER WHERE 
             INVTIDSKU=@INVTIDSKU 
 IF (@@error!=0)
    BEGIN
       ROLLBACK TRAN
       RETURN(1)    
    END

 COMMIT TRAN
/*************************************************************************************************************************/

GO

DROP PROC IN_CargaInventoryDataTempProc 
GO
Create Procedure IN_CargaInventoryDataTempProc 
(@CORRNBR            int,     
 @INVTIDSKU          UD_IDCORPORATIVO)  
as
 BEGIN TRAN

 INSERT INTO TMP_IN_INVENTORYDAT SELECT @CORRNBR,IN_INVENTORYDAT.* FROM IN_INVENTORYDAT WHERE 
             INVTIDSKU=@INVTIDSKU 
 IF (@@error!=0)
    BEGIN
       ROLLBACK TRAN
       RETURN(1)    
    END

 COMMIT TRAN
/*************************************************************************************************************************/
GO

DROP PROC IN_CargaInventoryVendorInvTempProc 
GO
Create Procedure IN_CargaInventoryVendorInvTempProc 
(@CORRNBR            int,     
 @INVTIDSKU          UD_IDCORPORATIVO)  
as
 BEGIN TRAN

 INSERT INTO TMP_IN_VENDORINVT SELECT @CORRNBR,IN_VENDORINVT.* FROM IN_VENDORINVT WHERE 
             INVTIDSKU=@INVTIDSKU 
 IF (@@error!=0)
    BEGIN
       ROLLBACK TRAN
       RETURN(1)    
    END

 COMMIT TRAN
/*************************************************************************************************************************/
GO

DROP PROC IN_CargaTempDataIN_VENDORINVTProc  
GO
Create Procedure IN_CargaTempDataIN_VENDORINVTProc  
(@CORRNBR int)
as
DECLARE   @VENDIDSS           UD_IDVENDOR
DECLARE   @INVTIDSKU          UD_IDCORPORATIVO
DECLARE   @BUYCOD             varchar(20)
DECLARE   @DESCR              UD_DESCRIPCION 
DECLARE   @CURYID             UD_CURYID
DECLARE   @STDCOST            UD_COSTO
DECLARE   @BUYDATE            smalldatetime
DECLARE   @USER1              UD_USER1
DECLARE   @USER2              UD_USER2
DECLARE   @USER3              UD_USER6
DECLARE   @USER4              UD_USER7
DECLARE   @ERROR              INTEGER
    
DECLARE InventoryVendor cursor 
   FOR SELECT VENDIDSS, INVTIDSKU, BUYCOD, DESCR, CURYID, 
              STDCOST, BUYDATE, USER1, USER2, USER3, USER4
        FROM TMP_IN_VENDORINVT where CORRNBR=@CORRNBR

   OPEN InventoryVendor       
       FETCH NEXT FROM InventoryVendor INTO  @VENDIDSS,@INVTIDSKU,@BUYCOD,@DESCR,
                       @CURYID,@STDCOST,@BUYDATE,@USER1,@USER2,@USER3,@USER4
                                                   
 WHILE @@FETCH_STATUS = 0
    BEGIN    
     INSERT INTO IN_VENDORINVT (VENDIDSS, INVTIDSKU, BUYCOD, DESCR, CURYID,
                                STDCOST, BUYDATE, USER1, USER2, USER3, USER4) values 
			       (@VENDIDSS,@INVTIDSKU,@BUYCOD,@DESCR,
                                @CURYID,@STDCOST,@BUYDATE,@USER1,@USER2,@USER3,@USER4)
	
     SET @ERROR=@@error
     
       IF (@ERROR!=0)
       BEGIN       
          CLOSE InventoryVendor 
          DEALLOCATE InventoryVendor
          RETURN(1)  
       END 

       FETCH NEXT FROM InventoryVendor INTO  @VENDIDSS,@INVTIDSKU,@BUYCOD,@DESCR,
                       @CURYID,@STDCOST,@BUYDATE,@USER1,@USER2,@USER3,@USER4
       
    END

  CLOSE InventoryVendor 
  DEALLOCATE InventoryVendor
  RETURN (0)
/*************************************************************************************************************************/
GO

DROP PROC IN_CargaTempDataInventoryAlterProc  
GO
Create Procedure IN_CargaTempDataInventoryAlterProc  
(@CORRNBR int)
as
DECLARE   @INVTIDSKU          UD_IDCORPORATIVO
DECLARE   @INVTIDBARR         UD_CODIGOBARRA
DECLARE   @USER1              UD_USER1
DECLARE   @USER2              UD_USER2
DECLARE   @USER3              UD_USER6
DECLARE   @USER4              UD_USER7
DECLARE   @ERROR              INTEGER
    
DECLARE InventoryAlter cursor 
    FOR SELECT INVTIDBARR,INVTIDSKU,USER1, USER2, USER3, USER4 
         FROM TMP_IN_INVENTORYALTER where CORRNBR=@CORRNBR

 OPEN InventoryAlter         
       FETCH NEXT FROM InventoryAlter INTO  @INVTIDBARR,@INVTIDSKU,@USER1,@USER2,
                      @USER3,@USER4
                                                   
 WHILE @@FETCH_STATUS = 0
    BEGIN    
     INSERT INTO IN_INVENTORYALTER (INVTIDBARR,INVTIDSKU,USER1, USER2, USER3, USER4) values 
                                   (@INVTIDBARR,@INVTIDSKU,@USER1,@USER2,
                                   @USER3,@USER4)
     SET @ERROR=@@error
     
       IF (@ERROR!=0)
       BEGIN       
          CLOSE InventoryAlter 
          DEALLOCATE InventoryAlter     
          RETURN(1)  
       END 

       FETCH NEXT FROM InventoryAlter INTO  @INVTIDBARR,@INVTIDSKU,@USER1,@USER2,
                       @USER3,@USER4
       
    END

  CLOSE InventoryAlter
  DEALLOCATE InventoryAlter    
  RETURN (0)

/*************************************************************************************************************************/
GO

DROP PROC IN_CargaTempDataInventoryProc  
GO

Create Procedure IN_CargaTempDataInventoryProc  
(@CORRNBR int)
as
DECLARE   @INVTIDSKU          UD_IDCORPORATIVO
DECLARE   @SITEID             UD_IDSITE
DECLARE   @PRECLVID           UD_PRECLVID
DECLARE   @STOCKMIN           UD_STOCK
DECLARE   @STOCKMAX           UD_STOCK
DECLARE   @STOCKSTAR          UD_STOCK
DECLARE   @QTYAVAIL           UD_STOCK
DECLARE   @STARTDATEINV       smalldatetime
DECLARE   @ENDDATEINV         smalldatetime
DECLARE   @LASTINVFIS         UD_STOCK
DECLARE   @AVGCOSTPEN         UD_COSTO
DECLARE   @AVGCOSTUSD         UD_COSTO
DECLARE   @STDCOSTPEN         UD_COSTO
DECLARE   @STDCOSTUSD         UD_COSTO
DECLARE   @LASTSTDCOSTPEN     UD_COSTO
DECLARE   @LASTSTDCOSTUSD     UD_COSTO
DECLARE   @SLS_PRICE          UD_COSTO
DECLARE   @USER1              UD_USER1
DECLARE   @USER2              UD_USER2
DECLARE   @USER3              UD_USER3
DECLARE   @USER4              UD_USER5
DECLARE   @USER5              UD_USER5
DECLARE   @USER6              UD_USER6
DECLARE   @USER7              UD_USER7
DECLARE   @USER8              UD_USER8
DECLARE   @USER9              UD_USER9 
DECLARE   @ERROR              INTEGER
    
DECLARE InventoryData cursor 
    FOR SELECT INVTIDSKU, SITEID,PRECLVID, STOCKMIN, STOCKMAX, 
             STOCKSTAR, QTYAVAIL, STARTDATEINV, ENDDATEINV, 
             LASTINVFIS, AVGCOSTPEN, AVGCOSTUSD, STDCOSTPEN, 
             STDCOSTUSD, LASTSTDCOSTPEN, LASTSTDCOSTUSD, 
             SLS_PRICE, USER1, USER2, USER3, USER4, USER5, USER6, 
             USER7, USER8, USER9
         FROM TMP_IN_INVENTORYDAT where CORRNBR=@CORRNBR

 OPEN InventoryData         
       FETCH NEXT FROM InventoryData INTO  @INVTIDSKU,@SITEID,@PRECLVID,@STOCKMIN,
                      @STOCKMAX,@STOCKSTAR,@QTYAVAIL,@STARTDATEINV,@ENDDATEINV,
                      @LASTINVFIS,@AVGCOSTPEN,@AVGCOSTUSD,@STDCOSTPEN,@STDCOSTUSD,
		      @LASTSTDCOSTPEN,@LASTSTDCOSTUSD,@SLS_PRICE,@USER1,@USER2,
                      @USER3,@USER4,@USER5,@USER6,@USER7,@USER8,@USER9
                                                   
 WHILE @@FETCH_STATUS = 0
    BEGIN    
     INSERT INTO IN_INVENTORYDAT (INVTIDSKU, SITEID, STOCKMIN, STOCKMAX, 
				   STOCKSTAR, QTYAVAIL, STARTDATEINV, ENDDATEINV, 
				   LASTINVFIS, AVGCOSTPEN, AVGCOSTUSD, STDCOSTPEN, 
				   STDCOSTUSD, LASTSTDCOSTPEN, LASTSTDCOSTUSD, 
			           SLS_PRICE,PRECLVID,USER1, USER2, USER3, USER4, USER5, USER6, 
				   USER7, USER8, USER9) values 
                                   (@INVTIDSKU,@SITEID,
                                   @STOCKMIN,@STOCKMAX,@STOCKSTAR,@QTYAVAIL,@STARTDATEINV,@ENDDATEINV,
                                   @LASTINVFIS,@AVGCOSTPEN,@AVGCOSTUSD,@STDCOSTPEN,@STDCOSTUSD,
		                   @LASTSTDCOSTPEN,@LASTSTDCOSTUSD,@SLS_PRICE,@PRECLVID,@USER1,@USER2,
                                   @USER3,@USER4,@USER5,@USER6,@USER7,@USER8,@USER9)
     SET @ERROR=@@error
     
       IF (@ERROR!=0)
       BEGIN       
          CLOSE InventoryData
          DEALLOCATE InventoryData    
          RETURN(1)  
       END 

      FETCH NEXT FROM InventoryData INTO  @INVTIDSKU,@SITEID,@PRECLVID,@STOCKMIN,
                      @STOCKMAX,@STOCKSTAR,@QTYAVAIL,@STARTDATEINV,@ENDDATEINV,
                      @LASTINVFIS,@AVGCOSTPEN,@AVGCOSTUSD,@STDCOSTPEN,@STDCOSTUSD,
		      @LASTSTDCOSTPEN,@LASTSTDCOSTUSD,@SLS_PRICE,@USER1,@USER2,
                      @USER3,@USER4,@USER5,@USER6,@USER7,@USER8,@USER9
       
    END

  CLOSE InventoryData
  DEALLOCATE InventoryData     
  RETURN (0)

/*************************************************************************************************************************/
GO

DROP PROC IN_INVENTORYALTERDelProc
GO

CREATE PROCEDURE IN_INVENTORYALTERDelProc
(
  @INVTIDSKU UD_IDCORPORATIVO)
AS
BEGIN
DECLARE @ERROR INTEGER    
    DELETE
        FROM IN_INVENTORYALTER
        WHERE INVTIDSKU = @INVTIDSKU

    SET @ERROR=@@error
    
    IF (@ERROR!=0)
    BEGIN       
        RETURN(1)    
    END

    RETURN(0)
END
/*************************************************************************************************************************/
GO

DROP PROC IN_INVENTORYALTERInsProc
GO

CREATE PROCEDURE IN_INVENTORYALTERInsProc
(
    @INVTIDBARR     UD_CODIGOBARRA,
    @INVTIDSKU      UD_IDCORPORATIVO            = NULL,
    @USER1          UD_USER1                    = NULL,
    @USER2          UD_USER2                    = NULL,
    @USER3          UD_USER6                    = NULL,
    @USER4          UD_USER7                    = NULL)
AS
BEGIN
    BEGIN TRAN

    INSERT INTO IN_INVENTORYALTER(INVTIDBARR,
                                  INVTIDSKU,
                                  USER1,
                                  USER2,
                                  USER3,
                                  USER4 
                                  )
    VALUES(@INVTIDBARR,
           @INVTIDSKU,
           @USER1,
           @USER2,
           @USER3,
           @USER4 
           )

    IF (@@error!=0)
    BEGIN
        RAISERROR  20000 'IN_INVENTORYALTERInsProc: Cannot insert because primary key value not found in IN_INVENTORYALTER '
        ROLLBACK TRAN
        RETURN(1)
    
    END

    COMMIT TRAN

END
/*************************************************************************************************************************/
GO

DROP PROC IN_INVENTORYALTERSelProc
GO

CREATE PROCEDURE IN_INVENTORYALTERSelProc
(
    @INVTIDBARR     UD_CODIGOBARRA)
AS
BEGIN
    SELECT INVTIDBARR,
           INVTIDSKU,
           USER1,
           USER2,
           USER3,
           USER4,
           TSTAMP
      FROM IN_INVENTORYALTER
     WHERE INVTIDBARR = @INVTIDBARR

    RETURN(0)
END
/*************************************************************************************************************************/
GO

DROP PROC IN_INVENTORYALTERUpdProc
GO

CREATE PROCEDURE IN_INVENTORYALTERUpdProc
(
    @INVTIDBARR     UD_CODIGOBARRA,
    @INVTIDSKU      UD_IDCORPORATIVO            = NULL,
    @USER1          UD_USER1                    = NULL,
    @USER2          UD_USER2                    = NULL,
    @USER3          UD_USER6                    = NULL,
    @USER4          UD_USER7                    = NULL,
    @TSTAMP         timestamp)
AS
BEGIN
    BEGIN TRAN

    UPDATE IN_INVENTORYALTER
       SET INVTIDSKU       = @INVTIDSKU,
           USER1           = @USER1,
           USER2           = @USER2,
           USER3           = @USER3,
           USER4           = @USER4
     WHERE INVTIDBARR = @INVTIDBARR

    IF (@@error!=0)
    BEGIN
        RAISERROR  20001 'IN_INVENTORYALTERUpdProc: Cannot update  in IN_INVENTORYALTER '
        ROLLBACK TRAN
        RETURN(1)
    
    END

    COMMIT TRAN

    RETURN(0)
END
/*****************************************************************************************************************************/
GO

DROP PROC IN_INVENTORYInsProc
GO

CREATE PROCEDURE IN_INVENTORYInsProc
/*procedireicfhsfjkhfkjhdsfdsfkjhsfkjdshfkjfhsdfjkhdsfjkh*/
(
    @INVTIDSKU       UD_IDCORPORATIVO, 
    @DESCR           varchar(40),
    @WTSTK1          numeric(7, 3),
    @STKFUELS        bit,
    @STKSTORE        bit,
    @STKOTHER        bit, 
    @STKITEM         bit,   
    @KIT             bit,
    @STKUNITID       UD_STKUNITID,
    @CLASSID         char(5),        
    @CLASSUBID       char(5), 
    @INVTYPEID       char(5),    
    @TAXIGV          UD_TAXID,
    @TAXISC          UD_TAXID                    = NULL,
    @DISCDSCTO1      UD_PORCENTAJE,
    @DISCDSCTO2      UD_PORCENTAJE,
    @DISCMTO         numeric(12, 2),
    @STKRECEPT       bit,
    @STKSALES        bit, 
    @STATUS          bit,
    @CONSIGNMENT     bit,
    @DISASSEMBLE     bit,
    @CORRNBR         INT,
    @INVTIDSKUDES     UD_IDCORPORATIVO,
    @WTSTK2           tinyint,
    @INVTIDSTK       char(20),
    @INVTIDNOSTK     char(20)                    = NULL)
AS
BEGIN
   DECLARE @ERROR Integer
   SELECT @ERROR=0   
   
   BEGIN TRAN

   INSERT INTO IN_INVENTORY(INVTIDSKU,DESCR,WTSTK1,STKFUELS,STKSTORE,STKOTHER,STKITEM,KIT,STKUNITID,
               CLASSID,CLASSUBID,INVTYPEID,TAXIGV,TAXISC,DISCDSCTO1,DISCDSCTO2,DISCMTO,STKRECEPT,STKSALES, 
               STATUS,CONSIGNMENT,DISASSEMBLE)

   VALUES  (@INVTIDSKU,@DESCR,@WTSTK1,@STKFUELS,@STKSTORE,@STKOTHER,@STKITEM,@KIT,@STKUNITID,
             @CLASSID,@CLASSUBID,@INVTYPEID,@TAXIGV,@TAXISC,@DISCDSCTO1,@DISCDSCTO2,@DISCMTO,@STKRECEPT,@STKSALES, 
             @STATUS,@CONSIGNMENT,@DISASSEMBLE)

    IF (@@error!=0)
    BEGIN
        RAISERROR  20000 'IN_INVENTORYInsProc: Cannot insert because primary key value not found in IN_INVENTORY '
        ROLLBACK TRAN
        RETURN(1)    
    END


    /*Borrar los datos de la tabla InventoryAlter */

    set exec @Error=IN_INVENTORYALTERDelProc @INVTIDSKU 
   
    IF (@ERROR!=0)
    BEGIN
       ROLLBACK TRAN
       RETURN(1)               
    END
   
 
    /*Recorrer el Temporal de IventoryAlter y pasarlo a la Tabla Inventory Alter */  
   
    set exec @Error=IN_CargaTempDataInventoryAlterProc @CORRNBR
    
    IF (@ERROR!=0)
    BEGIN
       ROLLBACK TRAN
       RETURN(1)               
    END

    
    /*Grabar en la Tabla Inventory_Des si el Flag de Desembsamble =1 */
    if @DISASSEMBLE=1 
       begin  
         set exec @Error=IN_INVENTORYDESInsProc @INVTIDSKU,@INVTIDSKUDES,@WTSTK2     
         IF (@ERROR!=0)
            BEGIN
              ROLLBACK TRAN
              RETURN(1)               
            END
        end

    /*Grabar en la Tabla In_SolInventory */

    set exec @Error=IN_SOLINVENTORYInsProc  @INVTIDSKU,@INVTIDSTK,@INVTIDNOSTK           
    IF (@ERROR!=0)
    BEGIN
       ROLLBACK TRAN
       RETURN(1)               
    END
 
    /*Borrar la Tabla Inventorydat (Recibe Código de Artículo + Código de Almacen)*/    
                          
    set EXEC @ERROR=IN_INVENTORYDATDelProc @INVTIDSKU

    IF (@ERROR!=0)
    BEGIN
       ROLLBACK TRAN
       RETURN(1)               
    END               

    /*Recorrer el Temporal pasando el identificador/correlativo para Grabar en InventoryDat*/
           
    set EXEC @ERROR=IN_CargaTempDataInventoryProc @CORRNBR                                                  
         
    IF (@ERROR!=0)
    BEGIN
       ROLLBACK TRAN
       RETURN(1)               
    END 

    /*Borrar los datos de la tabla IN_VENDORINVT */
    set exec @Error=IN_VENDORINVTDelProc @INVTIDSKU
    
    IF (@ERROR!=0)
    BEGIN
       ROLLBACK TRAN
       RETURN(1)               
    END     
    
    /*Recorrer el Temporal de IN_VENDORINVT y pasarlo a la Tabla IN_VENDORINVT*/

    set exec @Error=IN_CargaTempDataIN_VENDORINVTProc @CORRNBR          
     
    IF (@ERROR!=0)
    BEGIN
       ROLLBACK TRAN
       RETURN(1)               
    END    
                           
    COMMIT TRAN

END

/*************************************************************************************************************************/
GO

DROP PROC IN_INVENTORYUpdProc
GO

CREATE PROCEDURE IN_INVENTORYUpdProc
(
    @INVTIDSKU       UD_IDCORPORATIVO,
    @DESCR           varchar(40),
    @WTSTK1          numeric(7, 3),
    @STKFUELS        bit,
    @STKSTORE        bit,
    @STKOTHER        bit, 
    @STKITEM         bit,   
    @KIT             bit,
    @STKUNITID       UD_STKUNITID,
    @CLASSID         char(5),        
    @CLASSUBID       char(5), 
    @INVTYPEID       char(5),    
    @TAXIGV          UD_TAXID,
    @TAXISC          UD_TAXID                    = NULL,
    @DISCDSCTO1      UD_PORCENTAJE,
    @DISCDSCTO2      UD_PORCENTAJE,
    @DISCMTO         numeric(12, 2),
    @STKRECEPT       bit,
    @STKSALES        bit, 
    @STATUS          bit,
    @CONSIGNMENT     bit,
    @DISASSEMBLE     bit,
    @CORRNBR         INT,
    @INVTIDSKUDES    UD_IDCORPORATIVO,
    @WTSTK2          tinyint,
    @INVTIDSTK       char(20),
    @INVTIDNOSTK     char(20)			 = NULL)
AS
BEGIN
   DECLARE @ERROR Integer
   SELECT @ERROR=0   
   BEGIN TRAN

   UPDATE IN_INVENTORY
       SET DESCR=@DESCR,WTSTK1=@WTSTK1,STKFUELS=@STKFUELS,STKSTORE=@STKSTORE,STKOTHER=@STKOTHER, 
           STKITEM=@STKITEM,KIT=@KIT,STKUNITID=@STKUNITID,CLASSID=@CLASSID,CLASSUBID=@CLASSUBID,
           INVTYPEID=@INVTYPEID,TAXIGV=@TAXIGV,TAXISC=@TAXISC,DISCDSCTO1=@DISCDSCTO1,
           DISCDSCTO2=@DISCDSCTO2,DISCMTO=@DISCMTO,STKRECEPT=@STKRECEPT,STKSALES=@STKSALES,
           STATUS=@STATUS,CONSIGNMENT=@CONSIGNMENT,DISASSEMBLE=@DISASSEMBLE
   WHERE INVTIDSKU = @INVTIDSKU

   IF (@@error!=0)
   BEGIN
        RAISERROR  20001 'IN_INVENTORYUpdProc: Cannot update  in IN_INVENTORY '
        ROLLBACK TRAN
        RETURN(1)
    
   END
   

   /*Borrar los datos de la tabla InventoryAlter */

    set exec @Error=IN_INVENTORYALTERDelProc @INVTIDSKU 
   
    IF (@ERROR!=0)
    BEGIN
       ROLLBACK TRAN
       RETURN(1)               
    END
   
 
   /*Recorrer el Temporal de IventoryAlter y pasarlo a la Tabla Inventory Alter */  
   
    set exec @Error=IN_CargaTempDataInventoryAlterProc @CORRNBR
    
    IF (@ERROR!=0)
    BEGIN
       ROLLBACK TRAN
       RETURN(1)               
    END

   /*Tratamiento de la Tabla InventoryDes */   
    
    if @DISASSEMBLE=0   
      begin  
        set exec @Error=IN_INVENTORYDESDelProc @INVTIDSKU   
        IF (@ERROR!=0)
          BEGIN
           ROLLBACK TRAN
           RETURN(1)               
          END      
      end 
    else        
      begin 
        /*Si existe Actualizo*/
        if exists (select * from in_inventorydes where INVTIDSKU=@INVTIDSKU)        
         begin
           set exec @Error=IN_INVENTORYDESUpdProc @INVTIDSKU,@INVTIDSKUDES,@WTSTK2     
           IF (@ERROR!=0)
              BEGIN
              ROLLBACK TRAN
              RETURN(1)               
           END          
         end
        else
         begin
           set exec @Error=IN_INVENTORYDESInsProc @INVTIDSKU,@INVTIDSKUDES,@WTSTK2    
           IF (@ERROR!=0)
               BEGIN
               ROLLBACK TRAN
               RETURN(1)               
           END
         end 
      end
   /*Tratamiento de la Tabla In_SolInventory */ 
      if exists (select * from IN_SOLINVENTORY where INVTIDSKU=@INVTIDSKU) 
        begin 
           set exec @Error=IN_SOLINVENTORYUpdProc  @INVTIDSKU,@INVTIDSTK,@INVTIDNOSTK           
           IF (@ERROR!=0)
               BEGIN
               ROLLBACK TRAN
               RETURN(1)               
           END
        end
      else
        begin 
           set exec @Error=IN_SOLINVENTORYInsProc  @INVTIDSKU,@INVTIDSTK,@INVTIDNOSTK           
           IF (@ERROR!=0)
               BEGIN
               ROLLBACK TRAN
               RETURN(1)               
           END        
        end 
   /*Borrar los datos de la Tabla InventoryDat */ 
      
   set EXEC @ERROR=IN_INVENTORYDATDelProc @INVTIDSKU

   IF (@ERROR!=0)
   BEGIN
      ROLLBACK TRAN
      RETURN(1)               
   END   
         
   /*Recorrer el Temporal de IventoryDat y pasarlo a la Tabla InventoryDat */       
   set EXEC @ERROR=IN_CargaTempDataInventoryProc @CORRNBR                                                  
         
   IF (@ERROR!=0)
   BEGIN
      ROLLBACK TRAN
      RETURN(1)               
   END
   
   /*Borrar los datos de la tabla IN_VENDORINVT */
   set exec @Error=IN_VENDORINVTDelProc @INVTIDSKU
    
   IF (@ERROR!=0)
   BEGIN
       ROLLBACK TRAN
       RETURN(1)               
   END     
    
   /*Recorrer el Temporal de IN_VENDORINVT y pasarlo a la Tabla IN_VENDORINVT*/

    set exec @Error=IN_CargaTempDataIN_VENDORINVTProc @CORRNBR          
     
    IF (@ERROR!=0)
    BEGIN
       ROLLBACK TRAN
       RETURN(1)               
    END    


   COMMIT TRAN
   RETURN(0)
END
/*************************************************************************************************************************/
GO

DROP PROC IN_MostrarDetalleArticulosPorProveedorProc
GO

CREATE Procedure IN_MostrarDetalleArticulosPorProveedorProc
(@VENDIDSS     UD_IDVENDOR)
AS
Begin   
    Select  in_inventory.INVTIDSKU,in_inventory.descr,BUYCOD as [Cd.Compra], STDCOST as Costo  
           from ((in_vendorinvt inner join in_vendor on in_vendorinvt.VENDIDSS=in_vendor.VENDIDSS) inner join 
           currency on in_vendorinvt.curyid=currency.curyid) inner join in_inventory on in_vendorinvt.INVTIDSKU=
           in_inventory.INVTIDSKU  where in_vendorinvt.VENDIDSS =@VENDIDSS
    return(0)
end

/*************************************************************************************************************************/
GO

DROP PROC IN_MostrarDetalleIN_INVENTORYALTERProc
GO

Create Procedure IN_MostrarDetalleIN_INVENTORYALTERProc
(@INVTIDSKU       UD_IDCORPORATIVO)
AS
BEGIN
   Select INVTIDBARR as [Código Alternativo] from IN_INVENTORYALTER where INVTIDSKU =@INVTIDSKU
   RETURN(0)
END
/*************************************************************************************************************************/
GO

DROP PROC IN_MostrarDetalleIN_INVENTORYDES
GO

Create Procedure IN_MostrarDetalleIN_INVENTORYDES
(@INVTIDSKU       UD_IDCORPORATIVO)
AS
BEGIN
   select INVTIDSKUDES,in_inventorydes.WTSTK2 from in_inventorydes inner join in_inventory on 
          in_inventorydes.INVTIDSKU = in_inventory.INVTIDSKU 
          where in_inventory.INVTIDSKU=@INVTIDSKU
   RETURN(0) 
END
/*************************************************************************************************************************/
GO

DROP PROC IN_MostrarDetalleIN_INVENTORYDESProc
GO

Create Procedure IN_MostrarDetalleIN_INVENTORYDESProc
(@INVTIDSKU       UD_IDCORPORATIVO)
AS
BEGIN
   select INVTIDSKUDES,in_inventorydes.WTSTK2 from in_inventorydes inner join in_inventory on 
          in_inventorydes.INVTIDSKU = in_inventory.INVTIDSKU 
          where in_inventory.INVTIDSKU=@INVTIDSKU
   RETURN(0) 
END
/*************************************************************************************************************************/
GO

DROP PROC IN_MostrarDetalleIN_INVENTORYVENDORINVTProc
GO

Create Procedure IN_MostrarDetalleIN_INVENTORYVENDORINVTProc
(@INVTIDSKU       UD_IDCORPORATIVO)
AS
Begin   
    Select in_vendorinvt.VENDIDSS as [Cd],in_vendor.DESCR as [Proveedor],BUYCOD as [Cd.Compra],in_vendorinvt.descr as [Descripción],
           STDCOST as Costo,currency.curyid,currency.descr as Moneda,BUYDATE as [Ult.Compra] 
           from (in_vendorinvt inner join in_vendor on in_vendorinvt.VENDIDSS=in_vendor.VENDIDSS) inner join 
           currency on in_vendorinvt.curyid=currency.curyid where INVTIDSKU =@INVTIDSKU
    return(0)
end
/*************************************************************************************************************************/
GO

DROP PROC IN_MostrarDetalleIN_SOLINVENTORYProc
GO

Create Procedure IN_MostrarDetalleIN_SOLINVENTORYProc
(@INVTIDSKU       UD_IDCORPORATIVO)
AS
BEGIN
   select INVTIDSTK,INVTIDNOSTK from 
          IN_SOLINVENTORY where INVTIDSKU=@INVTIDSKU  
   RETURN(0) 
END
/*************************************************************************************************************************/
GO

DROP PROC IN_MostrarDetalleInventoryDatProc
GO

Create Procedure IN_MostrarDetalleInventoryDatProc
(@INVTIDSKU       UD_IDCORPORATIVO)
AS
BEGIN
     Select in_inventorydat.siteid as [Cd],Name as [Descripción],
            StockMax as [Stk.Max],StockMin as [Stk.Min],sls_price as [P.Venta],
            in_inventorydat.PRECLVID,IN_PRICELEVELTYPE.DESCR as [Tipo_Precio],CURRENCY.DESCR as [Moneda],
            lastdayin as [Ult.Ingreso],lastdayou As [Ult.Salida],avgcostpen,avgcostusd,stdcostpen,stdcostusd,stockstar,STARTDATEINV,qtyavail,
            ENDDATEINV,lastinvfis 
            from ((in_inventorydat inner join site on in_inventorydat.siteid=site.siteid) inner join 
            IN_PRICELEVELTYPE on in_inventorydat.PRECLVID=IN_PRICELEVELTYPE.PRECLVID) inner join currency
            on  IN_PRICELEVELTYPE.curyid=currency.curyid
            where INVTIDSKU =@INVTIDSKU
    RETURN(0)
END 
/*************************************************************************************************************************/
GO

DROP PROC IN_MostrarDetalleTempInventoryAlterProc
GO

Create Procedure IN_MostrarDetalleTempInventoryAlterProc
(@INVTIDSKU       UD_IDCORPORATIVO,
 @CORRNBR         integer)
as
begin 
      Select INVTIDBARR as [Código Alternativo] from TMP_IN_INVENTORYALTER where 
             INVTIDSKU =@INVTIDSKU
              and corrnbr=@CORRNBR

      RETURN(0)
end
/*************************************************************************************************************************/
GO

DROP PROC IN_MostrarDetalleTempInventoryDatProc
GO

Create Procedure IN_MostrarDetalleTempInventoryDatProc
(@INVTIDSKU       UD_IDCORPORATIVO,
 @CORRNBR         integer)
as 
begin
    Select tmp_in_inventorydat.siteid as [Cd],Name as [Descripción],
           StockMax as [Stk.Max],StockMin as [Stk.Min],sls_price as [P.Venta],
           tmp_in_inventorydat.PRECLVID,IN_PRICELEVELTYPE.DESCR as [Tipo_Precio],CURRENCY.DESCR as [Moneda],
           lastdayin as [Ult.Ingreso],lastdayou As [Ult.Salida],avgcostpen,avgcostusd,stdcostpen,
           stdcostusd,stockstar,STARTDATEINV,qtyavail,ENDDATEINV,lastinvfis 
           from ((tmp_in_inventorydat inner join site on tmp_in_inventorydat.siteid=site.siteid) left join 
                IN_PRICELEVELTYPE on tmp_in_inventorydat.PRECLVID=IN_PRICELEVELTYPE.PRECLVID) left join currency
                on  IN_PRICELEVELTYPE.curyid=currency.curyid
                where INVTIDSKU =@INVTIDSKU and corrnbr=@CORRNBR
     RETURN(0)
end
/*************************************************************************************************************************/
GO

DROP PROC IN_MostrarDetalleTEMPINVENTORYVENDORINVTProc
GO

Create Procedure IN_MostrarDetalleTEMPINVENTORYVENDORINVTProc
(@INVTIDSKU       UD_IDCORPORATIVO,
 @CORRNBR         integer)
AS
Begin
     Select tmp_in_vendorinvt.VENDIDSS as [Cd],in_vendor.DESCR as [Proveedor],BUYCOD as [Cd.Compra],
            TMP_in_vendorinvt.descr as [Descripción],STDCOST as Costo,currency.curyid,currency.descr as Moneda,
            BUYDATE as [Ult.Compra] 
     from (TMP_in_vendorinvt inner join in_vendor on TMP_in_vendorinvt.VENDIDSS=in_vendor.VENDIDSS) inner join 
            currency on TMP_in_vendorinvt.curyid=currency.curyid where INVTIDSKU =@INVTIDSKU AND 
            CORRNBR=@CORRNBR
     return(0)
end
/*************************************************************************************************************************/
GO

DROP PROC IN_NoPertenecealosAlmacenesProc
GO

Create Procedure IN_NoPertenecealosAlmacenesProc
(@INVTIDSKU       UD_IDCORPORATIVO)
as
  Select siteid,[name] from site where status=1 and siteid Not in( Select siteid from in_inventorydat 
                       where INVTIDSKU =@INVTIDSKU)

/*************************************************************************************************************************/
GO

DROP PROC IN_ObtenerDescripcionaInventoryProc
GO

Create Procedure IN_ObtenerDescripcionaInventoryProc
(@INVTIDSKU       UD_IDCORPORATIVO)
AS
BEGIN
   select descr from in_inventory where in_inventory.INVTIDSKU=@INVTIDSKU
   RETURN(0)
END
/*************************************************************************************************************************/
GO

DROP PROC IN_PertenecealosAlmacenesProc
GO

Create Procedure IN_PertenecealosAlmacenesProc
(@INVTIDSKU       UD_IDCORPORATIVO)
as
  Select siteid,[name] from site where siteid in( Select siteid from in_inventorydat 
                       where INVTIDSKU =@INVTIDSKU)

/*************************************************************************************************************************/
GO

DROP PROC IN_SUBCATEGORYSelProc
GO

CREATE PROCEDURE IN_SUBCATEGORYSelProc
(
    @CLASSID       char(5))
AS
BEGIN
    SELECT CLASSUBID,DESCR,
           CLASSID           
     FROM IN_SUBCATEGORY
     WHERE CLASSID   = @CLASSID

    RETURN(0)
END
/*************************************************************************************************************************/
GO

DROP PROC IN_SOLINVENTORYUpdProc
GO

CREATE PROCEDURE IN_SOLINVENTORYUpdProc
(
    @INVTIDSKU       UD_IDCORPORATIVO,
    @INVTIDSTK       char(20),
    @INVTIDNOSTK     char(20)                    = NULL)
AS
BEGIN
    DECLARE @ERROR INTEGER    

    UPDATE IN_SOLINVENTORY
       SET INVTIDSTK        = @INVTIDSTK,
           INVTIDNOSTK      = @INVTIDNOSTK
     WHERE INVTIDSKU = @INVTIDSKU

    SET @ERROR=@@error
    
    IF (@ERROR!=0)
    BEGIN       
        RETURN(1)    
    END

    RETURN(0)

END
/*************************************************************************************************************************/
GO

DROP PROC IN_SOLINVENTORYInsProc
GO

CREATE PROCEDURE IN_SOLINVENTORYInsProc
(
    @INVTIDSKU       UD_IDCORPORATIVO,
    @INVTIDSTK       char(20),
    @INVTIDNOSTK     char(20)                    = NULL)
AS
BEGIN
    DECLARE @ERROR INTEGER

    INSERT INTO IN_SOLINVENTORY(INVTIDSKU,INVTIDSTK,INVTIDNOSTK)
    VALUES(@INVTIDSKU,
           @INVTIDSTK,
           @INVTIDNOSTK)

    SET @ERROR=@@error
    
    IF (@ERROR!=0)
    BEGIN       
        RETURN(1)    
    END

    RETURN(0)

END
/*************************************************************************************************************************/
GO

DROP PROC IN_PRICELEVELTYPESelProc
GO

CREATE PROCEDURE IN_PRICELEVELTYPESelProc
(
    @PRECLVID     UD_PRECLVID)
AS
BEGIN
       SELECT PRECLVID,In_PriceLevelType.DESCR,currency.curyid as [codcurr], currency.descr as [desccurr]
       FROM In_PriceLevelType inner join currency
        on In_PriceLevelType.curyid=currency.curyid
       WHERE PRECLVID = @PRECLVID

    RETURN(0)
END

/*************************************************************************************************************************/
GO

DROP PROC IN_VENDORINVTDelProc
GO

CREATE PROCEDURE IN_VENDORINVTDelProc
(
  @INVTIDSKU UD_IDCORPORATIVO)
AS
BEGIN
DECLARE @ERROR INTEGER    
     DELETE
     FROM IN_VENDORINVT
          WHERE INVTIDSKU = @INVTIDSKU

    SET @ERROR=@@error
    
    IF (@ERROR!=0)
    BEGIN       
        RETURN(1)    
    END

    RETURN(0)
END
/*************************************************************************************************************************/
GO

DROP PROC TEMP_IN_INVENTORYALTERInsProc
GO

CREATE PROCEDURE TEMP_IN_INVENTORYALTERInsProc
(
    @CORRNBR        INT,
    @INVTIDBARR     UD_CODIGOBARRA,
    @INVTIDSKU      UD_IDCORPORATIVO)

AS
BEGIN
    BEGIN TRAN

    INSERT INTO TMP_IN_INVENTORYALTER(CORRNBR,INVTIDBARR,INVTIDSKU)
    VALUES(@CORRNBR,
           @INVTIDBARR,
           @INVTIDSKU)

    IF (@@error!=0)
    BEGIN
        RAISERROR  20000 'TEMP_IN_INVENTORYALTERInsProc: Cannot insert because primary key value not found in TMP_IN_INVENTORYALTER'
        ROLLBACK TRAN
        RETURN(1)
    
    END

    COMMIT TRAN

END
/*************************************************************************************************************************/
GO

DROP PROC TEMP_IN_INVENTORYDATReplicaProc
GO

CREATE PROCEDURE TEMP_IN_INVENTORYDATReplicaProc
(
    @CORRNBR            int,     
    @INVTIDSKU          UD_IDCORPORATIVO,
    @SITEID             UD_IDSITE,
    @STOCKMIN           UD_STOCK,
    @STOCKMAX           UD_STOCK)

AS
BEGIN
    BEGIN TRAN

    if exists(select * from TMP_IN_INVENTORYDAT where CORRNBR=@CORRNBR and 
              INVTIDSKU=@INVTIDSKU and SITEID=@SITEID) 
       UPDATE TMP_IN_INVENTORYDAT SET  STOCKMIN=@STOCKMIN,
                                       STOCKMAX=@STOCKMAX
       WHERE CORRNBR=@CORRNBR and 
             INVTIDSKU=@INVTIDSKU and SITEID=@SITEID       

    else      
       /*Si el registro no está en el Temporal entonces se Inserta*/
       INSERT INTO TMP_IN_INVENTORYDAT(CORRNBR,INVTIDSKU,SITEID,STOCKMIN,STOCKMAX)
                   
       VALUES(@CORRNBR,
              @INVTIDSKU,
              @SITEID,
              @STOCKMIN,
              @STOCKMAX)

    IF (@@error!=0)
    BEGIN
        RAISERROR  20000 'TEMP_IN_INVENTORYDATReplicaProc: Cannot insert because primary key value not found in IN_INVENTORYDAT '
        ROLLBACK TRAN
        RETURN(1)
    
    END
    COMMIT TRAN

END
/*************************************************************************************************************************/
GO

DROP PROC TEMP_IN_INVENTORYDATUpdProc
GO

CREATE PROCEDURE TEMP_IN_INVENTORYDATUpdProc
(
    @CORRNBR            int,     
    @INVTIDSKU          UD_IDCORPORATIVO,
    @SITEID             UD_IDSITE,
    @STOCKMIN           UD_STOCK,
    @STOCKMAX           UD_STOCK,
    @PRECLVID		UD_PRECLVID,
    @SLS_PRICE          UD_COSTO)
    
AS
BEGIN
    BEGIN TRAN

       UPDATE TMP_IN_INVENTORYDAT SET  STOCKMIN=@STOCKMIN,
                                       STOCKMAX=@STOCKMAX,
			               PRECLVID=@PRECLVID,
  				       SLS_PRICE=@SLS_PRICE
	
       WHERE CORRNBR=@CORRNBR and 
             INVTIDSKU=@INVTIDSKU and SITEID=@SITEID       

    IF (@@error!=0)
    BEGIN
        RAISERROR  20000 'TEMP_IN_INVENTORYDATUpdProc: Cannot insert because primary key value not found in IN_INVENTORYDAT '
        ROLLBACK TRAN
        RETURN(1)
    
    END
    COMMIT TRAN
END

/*************************************************************************************************************************/
GO

DROP PROC TEMP_IN_VENDORINVTnsProc
GO

CREATE PROCEDURE TEMP_IN_VENDORINVTnsProc
(
    @CORRNBR        INT,
    @VENDIDSS       UD_IDVENDOR,
    @INVTIDSKU      UD_IDCORPORATIVO,
    @BUYCOD         VARCHAR(20),
    @DESCR          UD_DESCRIPCION,
    @CURYID         UD_CURYID,
    @STDCOST        UD_COSTO )

AS
BEGIN
    BEGIN TRAN

    INSERT INTO TMP_IN_VENDORINVT(CORRNBR,VENDIDSS,INVTIDSKU,BUYCOD,DESCR,CURYID,STDCOST)
           VALUES(@CORRNBR,@VENDIDSS,@INVTIDSKU,@BUYCOD,@DESCR,@CURYID,@STDCOST)

    IF (@@error!=0)
    BEGIN
        RAISERROR  20000 'TEMP_IN_VENDORINVTnsProc: Cannot insert because primary key value not found in TMP_IN_VENDORINVT'
        ROLLBACK TRAN
        RETURN(1)
    
    END

    COMMIT TRAN

END

/***************************************************************************************************************************/
GO

DROP PROC TEMP_IN_VENDORINVTUpdProc
GO

CREATE PROCEDURE TEMP_IN_VENDORINVTUpdProc
(
    @CORRNBR        INT,
    @VENDIDSS       UD_IDVENDOR,
    @INVTIDSKU      UD_IDCORPORATIVO,
    @BUYCOD         VARCHAR(20),
    @DESCR          UD_DESCRIPCION,
    @CURYID         UD_CURYID,
    @STDCOST        UD_COSTO )

AS
BEGIN
    BEGIN TRAN

    UPDATE TMP_IN_VENDORINVT SET BUYCOD=@BUYCOD,DESCR=@DESCR,CURYID=@CURYID,STDCOST=@STDCOST
           WHERE CORRNBR=@CORRNBR AND VENDIDSS=@VENDIDSS AND INVTIDSKU=@INVTIDSKU

    IF (@@error!=0)
    BEGIN
        RAISERROR  20000 'TEMP_IN_VENDORINVTUpdProc: Cannot update in TMP_IN_VENDORINVT '
        ROLLBACK TRAN
        RETURN(1)
    
    END

    COMMIT TRAN

END
/*************************************************************************************************************************/
