DROP PROC ConsultaParaReporteIngresoProc
GO

CREATE PROCEDURE ConsultaParaReporteIngresoProc
( @SITEID     UD_IDSITE,
  @BATNBR     CHAR(10),
  @VENDIDSS   UD_IDVENDOR,
  @CURYID     UD_CURYID
)

AS
BEGIN
      DECLARE @MONEDABASE CHAR(3)
      SET @MONEDABASE ='PEN' 
      
      SELECT SEQUENCE,IN_INTRAITEM.INVTIDSKU,IN_VENDORINVT.BUYCOD,IN_INVENTORY.CLASSID,
             IN_INVENTORY.CLASSUBID,IN_INVENTORY.DESCR,IN_INVENTORYUNITMED.DESCR AS UNIT_MED,
             QTY,IN_INTRAITEM.STDCOSTPEN,
             CASE @CURYID
			WHEN @MONEDABASE THEN IN_INTRAITEM.STDCOSTPEN
			ELSE IN_INTRAITEM.STDCOSTUSD
			END as Costo,
             CASE @CURYID
			WHEN @MONEDABASE THEN CAST(QTY*IN_INTRAITEM.STDCOSTPEN AS NUMERIC(10,2))
			ELSE CAST(QTY*IN_INTRAITEM.STDCOSTUSD AS NUMERIC(10,2))
			END as TOTAL

      FROM   IN_INTRAITEM,IN_INVENTORY,IN_VENDORINVT,IN_INVENTORYUNITMED
      WHERE  SITEID=@SITEID AND
             BATNBR=@BATNBR AND IN_INTRAITEM.INVTIDSKU=IN_INVENTORY.INVTIDSKU AND
             IN_INTRAITEM.INVTIDSKU=IN_VENDORINVT.INVTIDSKU AND VENDIDSS=@VENDIDSS AND
	     IN_INVENTORY.STKUNITID=IN_INVENTORYUNITMED.STKUNITID 
END

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

DROP PROC IN_AnularDocumentoProc
GO

CREATE PROCEDURE IN_AnularDocumentoProc
(@SITEID UD_IDSITE,
@BATNBR char(10)
)
AS
BEGIN
    DECLARE   @ERROR	integer
    
    BEGIN TRAN
   
   --ACTUALIZO EL ESTADO DEL DOCUMENTO EN IN_INTRAHEAD
  
    UPDATE IN_INTRAHEAD
    SET DOCSTATUSID='AN'
    WHERE SITEID=@SITEID AND BATNBR=@BATNBR
          
    SET @ERROR=@@error
     
          IF (@ERROR!=0 OR @@ROWCOUNT=0 )
          BEGIN       
            ROLLBACK
            RETURN(1)  
          END         

  COMMIT TRAN
  RETURN (0)

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_CalculaTotales_2Proc
GO
CREATE PROCEDURE IN_CalculaTotales_2Proc
( @CURYID 	UD_CURYID,
  @SITEID	UD_IDSITE,
  @BATNBR	char(10)
)
AS 
BEGIN
   DECLARE @MONEDABASE 	char(3)
   SET @MONEDABASE='PEN'

      SELECT 
      'VALVENTA'= CASE @CURYID
		        WHEN @MONEDABASE THEN SUM(STDCOSTPEN*QTY)
		        ELSE SUM(STDCOSTUSD*QTY)
			END 
      FROM IN_INTRAITEM
      WHERE SITEID=@SITEID AND BATNBR=@BATNBR

END 


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

DROP PROC IN_CalculaTotalesProc
GO
CREATE PROCEDURE IN_CalculaTotalesProc
( @CURYID 	UD_CURYID,
  @CORRNBR	INT)
AS 
BEGIN
   DECLARE @MONEDABASE 	char(3)
   SET @MONEDABASE='PEN'

   IF @CURYID=@MONEDABASE
      SELECT SUM(STDCOSTPEN*QTY) AS VALVENTA 
      FROM TMP_IN_INTRAITEM
      WHERE CORRNBR=@CORRNBR
   ELSE
      SELECT SUM(STDCOSTUSD*QTY) AS VALVENTA 
      FROM TMP_IN_INTRAITEM
      WHERE CORRNBR=@CORRNBR
END 


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

DROP PROC IN_EmitirDocumentoProc
GO
CREATE PROCEDURE IN_EmitirDocumentoProc
(@SITEID1 UD_IDSITE,
@BATNBR1 char(10)
)
AS
BEGIN
    
    DECLARE   @SITEID            UD_IDSITE
    DECLARE   @BATNBR            char(10)
    DECLARE   @TYPEDOCID         UD_TYPEMOVEID
    DECLARE   @INVTIDSKU         UD_IDCORPORATIVO
    DECLARE   @QTY               UD_STOCK
    DECLARE   @STDCOSTPEN        UD_COSTO
    DECLARE   @STDCOSTUSD        UD_COSTO
    DECLARE   @INTRDATE          smalldatetime
    DECLARE   @INTRDATEPROCE     smalldatetime
    DECLARE   @CURYRATE		 numeric(6,4)
    DECLARE   @PERPOST           char(6) 
    DECLARE   @SWTINOUT		 bit    
    DECLARE   @COMPONENT         bit
    DECLARE   @SLSPRICE          numeric(9, 2)  
    DECLARE   @REFNBR		 char(10)
    DECLARE   @ERROR             integer
    DECLARE   @DOCTYPEID	 UD_DOCTYPEID
    

    SET @BATNBR=@BATNBR1   
    SET @SITEID=@SITEID1
    SET @SLSPRICE=0
    SET @COMPONENT=0	
    
    BEGIN TRAN
   
    SELECT @CURYRATE=CURYRATE,@PERPOST=PERPOST,@REFNBR=REFNBR,@DOCTYPEID=DOCTYPEID
    FROM IN_INTRAHEAD 
    WHERE SITEID=@SITEID AND BATNBR=@BATNBR 

    SELECT @SWTINOUT=IN_TYPEDOC.INOUT
    FROM IN_TYPEDOC,IN_INTRAHEAD
    WHERE IN_TYPEDOC.TYPEDOCID=IN_INTRAHEAD.TYPEDOCID AND  SITEID=@SITEID AND BATNBR=@BATNBR

   --ACTUALIZO EL ESTADO DEL DOCUMENTO EN IN_INTRAHEAD Y IN_INTRAITEM
  
    UPDATE IN_INTRAHEAD
    SET DOCSTATUSID='OK',
	RLSED=1
    WHERE SITEID=@SITEID AND BATNBR=@BATNBR
          
    SET @ERROR=@@error
     
          IF (@ERROR!=0 OR @@ROWCOUNT=0 )
          BEGIN       
            ROLLBACK
            RETURN(1)  
          END         


    UPDATE IN_INTRAITEM
    SET RLSED=1
    WHERE SITEID=@SITEID AND BATNBR=@BATNBR
          
    SET @ERROR=@@error
     
          IF (@ERROR!=0 OR @@ROWCOUNT=0 )
          BEGIN       
            ROLLBACK
            RETURN(1)  
          END         

     /*----------------------------------------------------------------------------------------- */

    -- SE DECLARA UN CURSOR PARA IN_INTRAITEM Y REALIZAR LA ACUALIZACION DE INTRAMOV
    DECLARE INTRAITEM cursor 


    FOR SELECT TYPEDOCID, INVTIDSKU, 
	       QTY, STDCOSTPEN, STDCOSTUSD, 
  	       INTRDATE, INTRDATEPROCE
        FROM   IN_INTRAITEM WHERE SITEID=@SITEID AND BATNBR=@BATNBR 

    OPEN INTRAITEM
    FETCH NEXT FROM INTRAITEM INTO  @TYPEDOCID, @INVTIDSKU,
                      		    @QTY, @STDCOSTPEN, @STDCOSTUSD,
                      		    @INTRDATE, @INTRDATEPROCE

      WHILE @@FETCH_STATUS=0
        BEGIN
	  --ACTUALIZANDO LA TABLA IN_INVENTORYDAT CON LAS NUEVAS CANTIDADES INGRESADAS EN EL DOCUMENTO
	IF (@STDCOSTPEN<>0)  -- SI EL COSTO ES CERO INDICA QUE NO ES VALORIZADO
	  BEGIN
		UPDATE  IN_INVENTORYDAT
	  	SET QTYAVAIL=QTYAVAIL + @QTY, STDCOSTPEN=@STDCOSTPEN, STDCOSTUSD=@STDCOSTUSD,LASTSTDCOSTPEN=@STDCOSTPEN, LASTSTDCOSTUSD=@STDCOSTUSD
	  	WHERE SITEID=@SITEID AND INVTIDSKU=@INVTIDSKU   
	  END
	ELSE
	  BEGIN
		UPDATE  IN_INVENTORYDAT
	  	SET QTYAVAIL=QTYAVAIL + @QTY
	  	WHERE SITEID=@SITEID AND INVTIDSKU=@INVTIDSKU   
	  END

              SET @ERROR=@@error
     
              IF (@ERROR!=0 OR @@ROWCOUNT=0 )
               BEGIN       
              	CLOSE INTRAITEM
               	DEALLOCATE INTRAITEM
	  	ROLLBACK
               	RETURN(1)  
               END         
	  
  	  --INSERTANDO EN LA TABLA INTRAMOV
          INSERT INTO INTRAMOV (SITEID,INVTIDSKU,BATNBR,INTRDATE,INTRDATEPROCE,
                                TYPEDOCID,CURYRATE,CURYDATE,PERPOST,SWTINOUT,
				QTY,STDCOSTPEN,STDCOSTUSD,COMPONENT,SLSPRICE,
				REFNBR,DOCTYPEID
                               )
          VALUES(@SITEID,@INVTIDSKU,@BATNBR,@INTRDATE,@INTRDATEPROCE,
                 @TYPEDOCID,@CURYRATE,@INTRDATE,@PERPOST,@SWTINOUT,
		 @QTY,@STDCOSTPEN,@STDCOSTUSD,@COMPONENT,@SLSPRICE,
		 @REFNBR,@DOCTYPEID 
                 )
 	
	  SET @ERROR=@@error
     
          IF (@ERROR!=0)
          BEGIN       
            CLOSE INTRAITEM
            DEALLOCATE INTRAITEM
	    ROLLBACK
            RETURN(1)  
          END         
	-------------------------------------------------------         
    	  FETCH NEXT FROM INTRAITEM INTO  @TYPEDOCID, @INVTIDSKU,
                      		    @QTY, @STDCOSTPEN, @STDCOSTUSD,
                      		    @INTRDATE, @INTRDATEPROCE
        
      END /* Termina while */




  CLOSE INTRAITEM
  DEALLOCATE INTRAITEM   
  COMMIT TRAN
  RETURN (0)

END   


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

DROP PROC IN_GrabaDetalleMovimientoProc
GO

CREATE PROCEDURE IN_GrabaDetalleMovimientoProc
(@CORRNBR int,
@BATNBR1 char(10),
@VENDIDSS UD_IDVENDOR,
@CURYID char(3)
)
AS
BEGIN    
    DECLARE   @SITEID            UD_IDSITE
    DECLARE   @BATNBR            char(10)
    DECLARE   @TYPEDOCID         UD_TYPEMOVEID
    DECLARE   @SEQUENCE          smallint
    DECLARE   @INVTIDSKU         UD_IDCORPORATIVO
    DECLARE   @INVTIDBARR        UD_CODIGOBARRA
    DECLARE   @QTY               UD_STOCK
    DECLARE   @TAXIGV            numeric(4, 2)
    DECLARE   @STDCOSTPEN        UD_COSTO
    DECLARE   @STDCOSTUSD        UD_COSTO
    DECLARE   @STKFISI           bit
    DECLARE   @INTRDATE          smalldatetime
    DECLARE   @INTRDATEPROCE     smalldatetime
    DECLARE   @RLSED             bit
    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

   --VARIABLE PARA IN_VENDORINVT
    DECLARE   @MONEDABASE		char(3)    -- almacena la moneda base
    DECLARE   @CAMPO  		char(10)  --para distinguir el campo de costo a tomar 

    SET @BATNBR=@BATNBR1   
    SET @MONEDABASE='PEN' 

    
   

    DECLARE INTRAITEM cursor 


    FOR SELECT SITEID, @BATNBR,TYPEDOCID, SEQUENCE, INVTIDSKU, 
               INVTIDBARR, QTY, TAXIGV, STDCOSTPEN, STDCOSTUSD, 
               STKFISI, INTRDATE, INTRDATEPROCE, RLSED,  
               USER1, USER2, USER3, USER4, USER5, USER6, 
               USER7, USER8, USER9
        FROM TMP_IN_INTRAITEM WHERE CORRNBR=@CORRNBR

    OPEN INTRAITEM
    FETCH NEXT FROM INTRAITEM INTO  @SITEID, @BATNBR, @TYPEDOCID, @SEQUENCE, @INVTIDSKU,
                      		    @INVTIDBARR, @QTY, @TAXIGV, @STDCOSTPEN, @STDCOSTUSD,
                      		    @STKFISI, @INTRDATE, @INTRDATEPROCE, @RLSED, @USER1,
				    @USER2, @USER3, @USER4, @USER5, @USER6,
		      		    @USER7, @USER8, @USER9  


      WHILE @@FETCH_STATUS=0
        BEGIN    

          INSERT INTO IN_INTRAITEM(SITEID,BATNBR,TYPEDOCID,SEQUENCE,INVTIDSKU,INVTIDBARR,QTY,
                                   TAXIGV,STDCOSTPEN,STDCOSTUSD,STKFISI,INTRDATE,INTRDATEPROCE,
                                   RLSED,USER1,USER2,USER3,USER4,USER5,USER6,USER7,USER8,USER9 
                                   )
          VALUES(@SITEID,@BATNBR,@TYPEDOCID,@SEQUENCE,@INVTIDSKU,@INVTIDBARR,@QTY,@TAXIGV,@STDCOSTPEN,
                 @STDCOSTUSD,@STKFISI,@INTRDATE,@INTRDATEPROCE,@RLSED,@USER1,@USER2,@USER3,@USER4,
                 @USER5,@USER6,@USER7,@USER8,@USER9 
                 )


          SET @ERROR=@@error
     
          IF (@ERROR!=0)
          BEGIN       
            CLOSE INTRAITEM
            DEALLOCATE INTRAITEM
            RETURN(1)  
          END         
          
          ---- ACTUALIZO LA TABLA IN_VENDORINVT
        IF (LTRIM(RTRIM(@CURYID))<>'')
        BEGIN
	IF (@CURYID=@MONEDABASE)
	 	BEGIN
                        UPDATE IN_VENDORINVT
	           	SET CURYID=@CURYID,STDCOST=@STDCOSTPEN
        	        WHERE VENDIDSS=@VENDIDSS AND INVTIDSKU= @INVTIDSKU
	 	END
	ELSE
	 	BEGIN
                        UPDATE IN_VENDORINVT
	           	SET CURYID=@CURYID,STDCOST=@STDCOSTUSD
        	        WHERE VENDIDSS=@VENDIDSS AND INVTIDSKU= @INVTIDSKU
	 	END
	 
	IF (@@ERROR!=0 OR @@ROWCOUNT=0)
               	BEGIN       
                 	CLOSE INTRAITEM
                 	DEALLOCATE INTRAITEM
                 	RETURN(1)  
              	END         

       END

   

            ----------------------------------------------------------------------

    	  FETCH NEXT FROM INTRAITEM INTO  @SITEID, @BATNBR, @TYPEDOCID, @SEQUENCE, @INVTIDSKU,
                      		    @INVTIDBARR, @QTY, @TAXIGV, @STDCOSTPEN, @STDCOSTUSD,
                      		    @STKFISI, @INTRDATE, @INTRDATEPROCE, @RLSED, @USER1,
				    @USER2, @USER3, @USER4, @USER5, @USER6,
		      		    @USER7, @USER8, @USER9

        
      END /* Termina while */



  CLOSE INTRAITEM
  DEALLOCATE INTRAITEM
  RETURN (0)

END   


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

DROP PROC IN_GrabarMovimientoProc
GO

CREATE PROCEDURE IN_GrabarMovimientoProc
(
    @SITEID            UD_IDSITE,

    @TYPEDOCID         UD_TYPEMOVEID,
    @VENDIDSS          UD_IDVENDOR,
    @DOCTYPEID         UD_DOCTYPEID,
    @DOCSTATUSID       char(2),
    @REFNBR            char(10),
    @CURYRATE          numeric(6, 4),
    @TOCURYID          char(3),
    @FROMCURYID        char(3),
    @SITEPROCES        UD_IDSITE,
    @NBRORDER          char(10)                 = NULL,
    @DATENBRORDER      smalldatetime            = NULL,
    @USERIDORDER       UD_USERID                = NULL,
    @INTRDATE          smalldatetime,
    @INTRDATEPROCE     smalldatetime,
    @SITEDEST          UD_IDSITE,
    @INTRTOTPEN        numeric(9, 2),
    @INTRTAXPEN        numeric(9, 2),
    @INTRTOTUSD        numeric(9, 2),
    @INTRTAXUSD        numeric(9, 2),
    @PERPOST           char(6),
    @RLSED             bit,
    @USER1             UD_USER1                 = NULL,
    @USER2             UD_USER2                 = NULL,
    @USER3             UD_USER3                 = NULL,
    @USER4             UD_USER5                 = NULL,
    @USER5             UD_USER5                 = NULL,
    @USER6             UD_USER6                 = NULL,
    @USER7             UD_USER7                 = NULL,
    @USER8             UD_USER8                 = NULL,
    @USER9             UD_USER9                 = NULL,
    @CORRNBR           int)
AS
BEGIN
   
   DECLARE @BATNBR char(10)
   DECLARE @BATNBR_NEXT char(10)
   DECLARE @NUM_BATNBR NUMERIC(10,0)
   DECLARE @ERROR Integer
   DECLARE @TSTAMP timestamp
   DECLARE @ROWCOUNT Integer
   SELECT @ERROR=0   


    BEGIN TRAN
   
   --set TRANSACTION  isolation LEVEL read committed 

   SELECT @BATNBR=BATNBRTYPEDOC from in_setup
-- SELECT @TSTAMP=TIMESTAMP from in_setup
   SELECT @NUM_BATNBR=cast(@BATNBR AS NUMERIC(10,0))+1
   SELECT @BATNBR_NEXT =replicate('0',10-len(@NUM_BATNBR))+cast(@NUM_BATNBR as char(10))
    

    INSERT INTO IN_INTRAHEAD(SITEID,BATNBR,TYPEDOCID,VENDIDSS,DOCTYPEID,DOCSTATUSID,
                             REFNBR,CURYRATE,TOCURYID,FROMCURYID,SITEPROCES,NBRORDER,
                             DATENBRORDER,USERIDORDER,INTRDATE,INTRDATEPROCE,SITEDEST,
                             INTRTOTPEN,INTRTAXPEN,INTRTOTUSD,INTRTAXUSD,PERPOST,
                             RLSED,USER1,USER2,USER3,USER4,USER5,USER6,USER7,USER8,USER9 
                             )
    VALUES(@SITEID,@BATNBR,@TYPEDOCID,@VENDIDSS,@DOCTYPEID,@DOCSTATUSID,@REFNBR,@CURYRATE,
           @TOCURYID,@FROMCURYID,@SITEPROCES,@NBRORDER,@DATENBRORDER,@USERIDORDER,@INTRDATE,
           @INTRDATEPROCE,@SITEDEST,@INTRTOTPEN,@INTRTAXPEN,@INTRTOTUSD,@INTRTAXUSD,@PERPOST,
	   @RLSED,@USER1,@USER2,@USER3,@USER4,@USER5,@USER6,@USER7,@USER8,@USER9 
           )
 
    IF (@@error!=0)
    BEGIN
        RAISERROR  20000 'IN_INTRAHEADInsProc: Cannot insert because primary key value not found in IN_INTRAHEAD '
        ROLLBACK TRAN
        RETURN(1)
    
    END

    /*** Llamada al store de grabado de detalle ******/
    SET EXEC @ERROR=IN_GrabaDetalleMovimientoProc @CORRNBR,@BATNBR,@VENDIDSS,@FROMCURYID

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

   /*** ACTUALIZACION DEL CORRELATIVO DE TIPO DE MOVIMIENTO ***/
    	UPDATE IN_SETUP
    	SET    BATNBRTYPEDOC=@BATNBR_NEXT    
	WHERE  BATNBRTYPEDOC=@BATNBR

     IF (@@error!=0 OR @@ROWCOUNT=0 )
    BEGIN
        ROLLBACK TRAN
        RETURN(1)
    
    END
   
   SELECT @BATNBR as NUMEROMOVIMIENTO
    COMMIT TRAN
    RETURN(0)
END 


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

DROP PROC IN_INTRAHEADDelProc
GO
CREATE PROCEDURE IN_INTRAHEADDelProc
(
    @SITEID            UD_IDSITE,
    @BATNBR            char(10),
    @TYPEDOCID         UD_TYPEMOVEID)
AS
BEGIN
    BEGIN TRAN

    DELETE
      FROM IN_INTRAHEAD
     WHERE SITEID    = @SITEID
       AND BATNBR    = @BATNBR
       AND TYPEDOCID = @TYPEDOCID

    IF (@@error!=0)
    BEGIN
        RAISERROR  20002 'IN_INTRAHEADDelProc: Cannot delete because foreign keys still exist in IN_INTRAHEAD '
        ROLLBACK TRAN
        RETURN(1)
    
    END

    COMMIT TRAN

    RETURN(0)
END

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

DROP PROC IN_INTRAHEADInsProc
GO
CREATE PROCEDURE IN_INTRAHEADInsProc
(
    @SITEID            UD_IDSITE,
    @BATNBR            char(10),
    @TYPEDOCID         UD_TYPEMOVEID,
    @VENDIDSS          UD_IDVENDOR,
    @DOCTYPEID         UD_DOCTYPEID,
    @DOCSTATUSID       char(2),
    @REFNBR            char(10),
    @CURYRATE          numeric(6, 4),
    @TOCURYID          char(3),
    @FROMCURYID        char(3),
    @SITEPROCES        UD_IDSITE,
    @NBRORDER          char(10)                 = NULL,
    @DATENBRORDER      smalldatetime            = NULL,
    @USERIDORDER       UD_USERID                = NULL,
    @INTRDATE          smalldatetime,
    @INTRDATEPROCE     smalldatetime,
    @SITEDEST          UD_IDSITE,
    @INTRTOTPEN        numeric(9, 2),
    @INTRTAXPEN        numeric(9, 2),
    @INTRTOTUSD        numeric(9, 2),
    @INTRTAXUSD        numeric(9, 2),
    @PERPOST           char(6),
    @RLSED             bit,
    @USER1             UD_USER1                 = NULL,
    @USER2             UD_USER2                 = NULL,
    @USER3             UD_USER3                 = NULL,
    @USER4             UD_USER5                 = NULL,
    @USER5             UD_USER5                 = NULL,
    @USER6             UD_USER6                 = NULL,
    @USER7             UD_USER7                 = NULL,
    @USER8             UD_USER8                 = NULL,
    @USER9             UD_USER9                 = NULL)
AS
BEGIN
    BEGIN TRAN

    INSERT INTO IN_INTRAHEAD(SITEID,
                             BATNBR,
                             TYPEDOCID,
                             VENDIDSS,
                             DOCTYPEID,
                             DOCSTATUSID,
                             REFNBR,
                             CURYRATE,
                             TOCURYID,
                             FROMCURYID,
                             SITEPROCES,
                             NBRORDER,
                             DATENBRORDER,
                             USERIDORDER,
                             INTRDATE,
                             INTRDATEPROCE,
                             SITEDEST,
                             INTRTOTPEN,
                             INTRTAXPEN,
                             INTRTOTUSD,
                             INTRTAXUSD,
                             PERPOST,
                             RLSED,
                             USER1,
                             USER2,
                             USER3,
                             USER4,
                             USER5,
                             USER6,
                             USER7,
                             USER8,
                             USER9 
                             )
    VALUES(@SITEID,
           @BATNBR,
           @TYPEDOCID,
           @VENDIDSS,
           @DOCTYPEID,
           @DOCSTATUSID,
           @REFNBR,
           @CURYRATE,
           @TOCURYID,
           @FROMCURYID,
           @SITEPROCES,
           @NBRORDER,
           @DATENBRORDER,
           @USERIDORDER,
           @INTRDATE,
           @INTRDATEPROCE,
           @SITEDEST,
           @INTRTOTPEN,
           @INTRTAXPEN,
           @INTRTOTUSD,
           @INTRTAXUSD,
           @PERPOST,
           @RLSED,
           @USER1,
           @USER2,
           @USER3,
           @USER4,
           @USER5,
           @USER6,
           @USER7,
           @USER8,
           @USER9 
           )

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

    COMMIT TRAN

END

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

DROP PROC IN_INTRAHEADSelProc
GO

CREATE PROCEDURE IN_INTRAHEADSelProc
(
    @SITEID            UD_IDSITE,
    @BATNBR            char(10),
    @TYPEDOCID         UD_TYPEMOVEID)
AS
BEGIN
    SELECT SITEID,
           BATNBR,
           TYPEDOCID,
           VENDIDSS,
           DOCTYPEID,
           DOCSTATUSID,
           REFNBR,
           CURYRATE,
           TOCURYID,
           FROMCURYID,
           SITEPROCES,
           NBRORDER,
           DATENBRORDER,
           USERIDORDER,
           INTRDATE,
           INTRDATEPROCE,
           SITEDEST,
           INTRTOTPEN,
           INTRTAXPEN,
           INTRTOTUSD,
           INTRTAXUSD,
           PERPOST,
           RLSED,
           USER1,
           USER2,
           USER3,
           USER4,
           USER5,
           USER6,
           USER7,
           USER8,
           USER9,
           TSTAMP
      FROM IN_INTRAHEAD
     WHERE SITEID    = @SITEID
       AND BATNBR    = @BATNBR
       AND TYPEDOCID = @TYPEDOCID

    RETURN(0)
END

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

DROP PROC IN_INTRAHEADUpdProc
GO

CREATE PROCEDURE IN_INTRAHEADUpdProc
(
    @SITEID            UD_IDSITE,
    @BATNBR            char(10),
    @TYPEDOCID         UD_TYPEMOVEID,
    @VENDIDSS          UD_IDVENDOR,
    @DOCTYPEID         UD_DOCTYPEID,
    @DOCSTATUSID       char(2),
    @REFNBR            char(10),
    @CURYRATE          numeric(6, 4),
    @TOCURYID          char(3),
    @FROMCURYID        char(3),
    @SITEPROCES        UD_IDSITE,
    @NBRORDER          char(10)                 = NULL,
    @DATENBRORDER      smalldatetime            = NULL,
    @USERIDORDER       UD_USERID                = NULL,
    @INTRDATE          smalldatetime,
    @INTRDATEPROCE     smalldatetime,
    @SITEDEST          UD_IDSITE,
    @INTRTOTPEN        numeric(9, 2),
    @INTRTAXPEN        numeric(9, 2),
    @INTRTOTUSD        numeric(9, 2),
    @INTRTAXUSD        numeric(9, 2),
    @PERPOST           char(6),
    @RLSED             bit,
    @USER1             UD_USER1                 = NULL,
    @USER2             UD_USER2                 = NULL,
    @USER3             UD_USER3                 = NULL,
    @USER4             UD_USER5                 = NULL,
    @USER5             UD_USER5                 = NULL,
    @USER6             UD_USER6                 = NULL,
    @USER7             UD_USER7                 = NULL,
    @USER8             UD_USER8                 = NULL,
    @USER9             UD_USER9                 = NULL,
    @TSTAMP            timestamp)
AS
BEGIN
    BEGIN TRAN

    UPDATE IN_INTRAHEAD
       SET VENDIDSS           = @VENDIDSS,
           DOCTYPEID          = @DOCTYPEID,
           DOCSTATUSID        = @DOCSTATUSID,
           REFNBR             = @REFNBR,
           CURYRATE           = @CURYRATE,
           TOCURYID           = @TOCURYID,
           FROMCURYID         = @FROMCURYID,
           SITEPROCES         = @SITEPROCES,
           NBRORDER           = @NBRORDER,
           DATENBRORDER       = @DATENBRORDER,
           USERIDORDER        = @USERIDORDER,
           INTRDATE           = @INTRDATE,
           INTRDATEPROCE      = @INTRDATEPROCE,
           SITEDEST           = @SITEDEST,
           INTRTOTPEN         = @INTRTOTPEN,
           INTRTAXPEN         = @INTRTAXPEN,
           INTRTOTUSD         = @INTRTOTUSD,
           INTRTAXUSD         = @INTRTAXUSD,
           PERPOST            = @PERPOST,
           RLSED              = @RLSED,
           USER1              = @USER1,
           USER2              = @USER2,
           USER3              = @USER3,
           USER4              = @USER4,
           USER5              = @USER5,
           USER6              = @USER6,
           USER7              = @USER7,
           USER8              = @USER8,
           USER9              = @USER9
     WHERE SITEID    = @SITEID
       AND BATNBR    = @BATNBR
       AND TYPEDOCID = @TYPEDOCID

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

    COMMIT TRAN

    RETURN(0)
END

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

DROP PROC IN_INTRAITEMDelProc
GO

CREATE PROCEDURE IN_INTRAITEMDelProc
(
    @SITEID            UD_IDSITE,
    @BATNBR            char(10),
    @TYPEDOCID         UD_TYPEMOVEID,
    @SEQUENCE          smallint)
AS
BEGIN
    BEGIN TRAN

    DELETE
      FROM IN_INTRAITEM
     WHERE SITEID    = @SITEID
       AND BATNBR    = @BATNBR
       AND TYPEDOCID = @TYPEDOCID
       AND SEQUENCE  = @SEQUENCE

    IF (@@error!=0)
    BEGIN
        RAISERROR  20002 'IN_INTRAITEMDelProc: Cannot delete because foreign keys still exist in IN_INTRAITEM '
        ROLLBACK TRAN
        RETURN(1)
    
    END

    COMMIT TRAN

    RETURN(0)
END

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

DROP PROC IN_INTRAITEMInsProc
GO

CREATE PROCEDURE IN_INTRAITEMInsProc
(
    @SITEID            UD_IDSITE,
    @BATNBR            char(10),
    @TYPEDOCID         UD_TYPEMOVEID,
    @SEQUENCE          smallint,
    @INVTIDSKU         UD_IDCORPORATIVO,
    @INVTIDBARR        UD_CODIGOBARRA,
    @QTY               UD_STOCK,
    @TAXIGV            numeric(4, 2)               = NULL,
    @STDCOSTPEN        UD_COSTO                    = NULL,
    @STDCOSTUSD        UD_COSTO                    = NULL,
    @STKFISI           bit,
    @INTRDATE          smalldatetime,
    @INTRDATEPROCE     smalldatetime,
    @RLSED             bit,
    @USER1             UD_USER1                    = NULL,
    @USER2             UD_USER2                    = NULL,
    @USER3             UD_USER3                    = NULL,
    @USER4             UD_USER5                    = NULL,
    @USER5             UD_USER5                    = NULL,
    @USER6             UD_USER6                    = NULL,
    @USER7             UD_USER7                    = NULL,
    @USER8             UD_USER8                    = NULL,
    @USER9             UD_USER9                    = NULL)
AS
BEGIN
    BEGIN TRAN

    INSERT INTO IN_INTRAITEM(SITEID,
                             BATNBR,
                             TYPEDOCID,
                             SEQUENCE,
                             INVTIDSKU,
                             INVTIDBARR,
                             QTY,
                             TAXIGV,
                             STDCOSTPEN,
                             STDCOSTUSD,
                             STKFISI,
                             INTRDATE,
                             INTRDATEPROCE,
                             RLSED,
                             USER1,
                             USER2,
                             USER3,
                             USER4,
                             USER5,
                             USER6,
                             USER7,
                             USER8,
                             USER9 
                             )
    VALUES(@SITEID,
           @BATNBR,
           @TYPEDOCID,
           @SEQUENCE,
           @INVTIDSKU,
           @INVTIDBARR,
           @QTY,
           @TAXIGV,
           @STDCOSTPEN,
           @STDCOSTUSD,
           @STKFISI,
           @INTRDATE,
           @INTRDATEPROCE,
           @RLSED,
           @USER1,
           @USER2,
           @USER3,
           @USER4,
           @USER5,
           @USER6,
           @USER7,
           @USER8,
           @USER9 
           )

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

    COMMIT TRAN

END

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

DROP PROC IN_INTRAITEMSelProc
GO

CREATE PROCEDURE IN_INTRAITEMSelProc
(   @SITEID     UD_IDSITE,
    @BATNBR     CHAR(10), 
    @VENDIDSS   UD_IDVENDOR,
    @CURYID     UD_CURYID)
AS
BEGIN
      DECLARE @MONEDABASE CHAR(3)
      SET @MONEDABASE ='PEN'

      SELECT [SEQUENCE] AS Registro,
             IN_INTRAITEM.INVTIDSKU AS Codigo,
	     IN_VENDORINVT.BUYCOD AS Cod_Compra, 
             IN_INVENTORY.DESCR AS Descripcion,
             QTY as Cantidad,
             CASE @CURYID
			WHEN @MONEDABASE THEN STDCOSTPEN
			ELSE STDCOSTUSD
			END as Costo,
             CASE @CURYID
			WHEN @MONEDABASE THEN CAST(QTY*STDCOSTPEN AS NUMERIC(10,2))
			ELSE CAST(QTY*STDCOSTUSD AS NUMERIC(10,2))
			END as Total
	
      FROM IN_INTRAITEM, IN_INVENTORY, IN_VENDORINVT 
      WHERE IN_INTRAITEM.INVTIDSKU = IN_INVENTORY.INVTIDSKU AND 
      	    SITEID=@SITEID AND IN_INTRAITEM.INVTIDSKU=IN_VENDORINVT.INVTIDSKU AND 
            VENDIDSS=@VENDIDSS AND BATNBR=@BATNBR 

END

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

DROP PROC IN_INTRAITEMUpdProc
GO

CREATE PROCEDURE IN_INTRAITEMUpdProc
(
    @SITEID            UD_IDSITE,
    @BATNBR            char(10),
    @TYPEDOCID         UD_TYPEMOVEID,
    @SEQUENCE          smallint,
    @INVTIDSKU         UD_IDCORPORATIVO,
    @INVTIDBARR        UD_CODIGOBARRA,
    @QTY               UD_STOCK,
    @TAXIGV            numeric(4, 2)               = NULL,
    @STDCOSTPEN        UD_COSTO                    = NULL,
    @STDCOSTUSD        UD_COSTO                    = NULL,
    @STKFISI           bit,
    @INTRDATE          smalldatetime,
    @INTRDATEPROCE     smalldatetime,
    @RLSED             bit,
    @USER1             UD_USER1                    = NULL,
    @USER2             UD_USER2                    = NULL,
    @USER3             UD_USER3                    = NULL,
    @USER4             UD_USER5                    = NULL,
    @USER5             UD_USER5                    = NULL,
    @USER6             UD_USER6                    = NULL,
    @USER7             UD_USER7                    = NULL,
    @USER8             UD_USER8                    = NULL,
    @USER9             UD_USER9                    = NULL,
    @TSTAMP            timestamp)
AS
BEGIN
    BEGIN TRAN

    UPDATE IN_INTRAITEM
       SET INVTIDSKU          = @INVTIDSKU,
           INVTIDBARR         = @INVTIDBARR,
           QTY                = @QTY,
           TAXIGV             = @TAXIGV,
           STDCOSTPEN         = @STDCOSTPEN,
           STDCOSTUSD         = @STDCOSTUSD,
           STKFISI            = @STKFISI,
           INTRDATE           = @INTRDATE,
           INTRDATEPROCE      = @INTRDATEPROCE,
           RLSED              = @RLSED,
           USER1              = @USER1,
           USER2              = @USER2,
           USER3              = @USER3,
           USER4              = @USER4,
           USER5              = @USER5,
           USER6              = @USER6,
           USER7              = @USER7,
           USER8              = @USER8,
           USER9              = @USER9
     WHERE SITEID    = @SITEID
       AND BATNBR    = @BATNBR
       AND TYPEDOCID = @TYPEDOCID
       AND SEQUENCE  = @SEQUENCE

    IF (@@error!=0)
    BEGIN
        RAISERROR  20001 'IN_INTRAITEMUpdProc: Cannot update  in IN_INTRAITEM '
        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_ObtieneSecuenciaProc
GO

CREATE PROCEDURE IN_ObtieneSecuenciaProc
( @CORRNBR  int)
AS
BEGIN

    BEGIN TRAN
    DECLARE @VALOR INTEGER	
    SET  SELECT @VALOR = COUNT(*)+1 FROM TMP_IN_INTRAITEM WHERE CORRNBR=@CORRNBR
   

    IF (@@error!=0)
    BEGIN
        RAISERROR  20000 'ERROR AL CONSULTAR TMP_IN_INTRAITEM'
        ROLLBACK TRAN
        RETURN(-1)
    END

    COMMIT TRAN
    RETURN(@VALOR)

END 

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

DROP PROC IN_PROVEEDOR_ARTICULO_PROC
GO

-- PROCEDIMIENTO QUE VALIDA LA EXISTENCIA DE UN CODIGO DE ARTICULO INGRESADOÇ
-- PUEDE SER CODIGO SKU,CODIGO DE BARRA O CODIGO DE PROVEEDOR
CREATE PROCEDURE IN_PROVEEDOR_ARTICULO_PROC
( @VENDIDSS	UD_IDVENDOR,
  @CODIGO	UD_IDCORPORATIVO
)
AS

	SELECT  IN_INVENTORY.INVTIDSKU,
		IN_INVENTORY.DESCR,
		IN_INVENTORYALTER.INVTIDBARR,
		IN_VENDORINVT.BUYCOD,
		IN_VENDORINVT.VENDIDSS,
		IN_VENDORINVT.CURYID,
		IN_VENDORINVT.STDCOST
	FROM 	IN_INVENTORY 
		LEFT JOIN IN_INVENTORYALTER
		ON IN_INVENTORY.INVTIDSKU=IN_INVENTORYALTER.INVTIDSKU 
		LEFT JOIN IN_VENDORINVT ON IN_INVENTORY.INVTIDSKU=IN_VENDORINVT.INVTIDSKU
        WHERE	IN_VENDORINVT.VENDIDSS=@VENDIDSS AND 
		(IN_INVENTORY.INVTIDSKU=@CODIGO OR IN_INVENTORYALTER.INVTIDBARR=@CODIGO OR IN_VENDORINVT.BUYCOD=@CODIGO)


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

DROP PROC ObtenerCorrelativoProcUtil
GO

Create Procedure ObtenerCorrelativoProcUtil
(@USERID UD_USERID)
AS     
  BEGIN
   BEGIN TRAN
    insert into TMP_CORRNBRHEAD(userid) values (@USERID)

    IF (@@error!=0)
    BEGIN
        RAISERROR  20000 'ObtenerCorrelativoProcUtil: Error al Insertar'
        ROLLBACK TRAN
        RETURN(1)
    
    END
     
    COMMIT TRAN
    SELECT @@IDENTITY as Correlativo     
  END

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

DROP PROC Tmp_In_IntraItemDelProc
GO

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

AS
BEGIN
    BEGIN TRAN

    DELETE
      FROM  TMP_IN_INTRAITEM
      WHERE INVTIDSKU=@INVTIDSKU AND
	    CORRNBR=@CORRNBR


    IF (@@error!=0)
    BEGIN
        RAISERROR  20002 'Error al Eliminar'
        ROLLBACK TRAN
        RETURN(1)
    
    END

    COMMIT TRAN

    RETURN(0)
END

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

DROP PROC Tmp_In_IntraItemDelTodoProc
GO


CREATE PROCEDURE Tmp_In_IntraItemDelTodoProc
(
  @CORRNBR  INT)
AS
BEGIN
    BEGIN TRAN

    DELETE
      FROM Tmp_In_IntraItem
     WHERE CORRNBR=@CORRNBR

    IF (@@error!=0)
    BEGIN
        RAISERROR  20002 'TMP_IN_INTRAITEMTodoDelProc: Cannot delete because foreign keys still exist in TMP_IN_INTRAITEM '
        ROLLBACK TRAN
        RETURN(1)
    
    END

    COMMIT TRAN

    RETURN(0)
END

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

DROP PROC TMP_IN_INTRAITEMInsProc
GO

CREATE PROCEDURE TMP_IN_INTRAITEMInsProc
(
    @CORRNBR           int,
    @SITEID            UD_IDSITE,

    @TYPEDOCID         UD_TYPEMOVEID,
    @SEQUENCE          smallint,
    @INVTIDSKU         UD_IDCORPORATIVO,
    @INVTIDBARR        UD_CODIGOBARRA,
    @QTY               UD_STOCK,
    @TAXIGV            numeric(4, 2)               = NULL,
    @STDCOSTPEN        UD_COSTO                    = NULL,
    @STDCOSTUSD        UD_COSTO                    = NULL,
    @STKFISI           bit,
    @INTRDATE          smalldatetime,
    @INTRDATEPROCE    smalldatetime,
    @RLSED             bit,
    @USER1             UD_USER1                    = NULL,
    @USER2             UD_USER2                    = NULL,
    @USER3             UD_USER3                    = NULL,
    @USER4             UD_USER5                    = NULL,
    @USER5             UD_USER5                    = NULL,
    @USER6             UD_USER6                    = NULL,
    @USER7             UD_USER7                    = NULL,
    @USER8             UD_USER8                    = NULL,
    @USER9             UD_USER9                    = NULL)
AS
BEGIN
    BEGIN TRAN

          INSERT INTO TMP_IN_INTRAITEM(CORRNBR,SITEID,TYPEDOCID,SEQUENCE,INVTIDSKU,INVTIDBARR,QTY,
                                   TAXIGV,STDCOSTPEN,STDCOSTUSD,STKFISI,INTRDATE,INTRDATEPROCE,
                                   RLSED,USER1,USER2,USER3,USER4,USER5,USER6,USER7,USER8,USER9 
                                   )
          VALUES(@CORRNBR,@SITEID,@TYPEDOCID,@SEQUENCE,@INVTIDSKU,@INVTIDBARR,@QTY,@TAXIGV,@STDCOSTPEN,
                 @STDCOSTUSD,@STKFISI,@INTRDATE,@INTRDATEPROCE,@RLSED,@USER1,@USER2,@USER3,@USER4,
                 @USER5,@USER6,@USER7,@USER8,@USER9 
                 )

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

    COMMIT TRAN

END


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

DROP PROC TMP_IN_INTRAITEMSelProc
GO

CREATE PROCEDURE TMP_IN_INTRAITEMSelProc
(   @VENDIDSS UD_IDVENDOR,
    @CORRNBR  INT,
    @CURYID     UD_CURYID)

AS
BEGIN
      DECLARE @MONEDABASE CHAR(3)
      SET @MONEDABASE ='PEN'

      SELECT [SEQUENCE] AS Registro,
           	       TMP_IN_INTRAITEM.INVTIDSKU AS Codigo,
	       IN_VENDORINVT.BUYCOD AS Cod_Compra, 
                    IN_INVENTORY.DESCR AS Descripcion,
                    QTY as Cantidad,
                    CASE @CURYID
			WHEN @MONEDABASE THEN STDCOSTPEN
			ELSE STDCOSTUSD
			END as Costo,
                    CASE @CURYID
			WHEN @MONEDABASE THEN CAST(QTY*STDCOSTPEN AS NUMERIC(10,2))
			ELSE CAST(QTY*STDCOSTUSD AS NUMERIC(10,2))
			END as Total
      FROM TMP_IN_INTRAITEM,IN_INVENTORY,IN_VENDORINVT
      WHERE TMP_IN_INTRAITEM.INVTIDSKU = IN_INVENTORY.INVTIDSKU AND
	    CORRNBR=@CORRNBR AND TMP_IN_INTRAITEM.INVTIDSKU=IN_VENDORINVT.INVTIDSKU
	    AND VENDIDSS=@VENDIDSS

END

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

DROP PROC TMP_IN_INTRAITEMUpdProc
GO

CREATE PROCEDURE TMP_IN_INTRAITEMUpdProc
(
    @INVTIDSKU         	UD_IDCORPORATIVO,
    @SEQUENCE		SMALLINT,
    @QTY              	UD_STOCK,
    @COSTO        	UD_COSTO                    = NULL,
    @CORRNBR           	int,
    @CURYID		UD_CURYID)
AS
BEGIN
    BEGIN TRAN
     
      DECLARE @MONEDABASE CHAR(3)
      SET @MONEDABASE ='PEN'


    UPDATE TMP_IN_INTRAITEM
    SET 	SEQUENCE = @SEQUENCE,
           	QTY  = @QTY,
		STDCOSTPEN= CASE @CURYID
				WHEN @MONEDABASE THEN @COSTO
				ELSE STDCOSTPEN
				END,
		STDCOSTUSD= CASE @CURYID
				WHEN @MONEDABASE THEN STDCOSTUSD
				ELSE @COSTO
				END
	
    WHERE INVTIDSKU=@INVTIDSKU AND CORRNBR=@CORRNBR

    IF (@@error!=0)
    BEGIN
        RAISERROR  20001 'No se piede actualizar la tabla TMP_IN_INTRAITEM '
        ROLLBACK TRAN
        RETURN(1)
    
    END

    COMMIT TRAN

    RETURN(0)
END

GO
