
DROP PROC CARGA_TMP_COBRANZAS_PROC
GO
CREATE PROC CARGA_TMP_COBRANZAS_PROC
( @CORRNBR	INT,
  @CUSTIDSS	UD_CODCLIENT
)
AS
BEGIN
	BEGIN TRAN
	
	INSERT  INTO TMP_AR_COBRANZAS(CORRNBR,DOCTYPEID,NBRDOCUMENT,DATEPROCESALES,DUEDATE,TOTALPEN,TOTALUSD,DOCVALPEN,DOCVALUSD,PAGOPEN,PAGOUSD,CUSTIDSS)
		SELECT  @CORRNBR,DOCTYPEID,NBRDOCUMENT,DATEPROCESALES,DUEDATE,TOTALPEN,TOTALUSD,DOCVALPEN,DOCVALUSD,0,0,@CUSTIDSS
		FROM AR_CCDOC 
		WHERE CUSTIDSS=@CUSTIDSS AND DOCTYPEID<>'07' AND DOCSTATUSID NOT IN ('AN','CA')

	IF(@@ERROR!=0 OR @@ROWCOUNT=0)
	BEGIN
		ROLLBACK	
		RAISERROR 20000 'Error insertando en TMP_AR_COBRANZAS'
		RETURN(1)	
	END
	COMMIT TRAN
	RETURN(0)
END

GO

DROP PROC GRABAR_COBRANZAS_PROC
GO
CREATE PROC GRABAR_COBRANZAS_PROC
( @CORRNBR	INT,
  @SITEID	UD_IDSITE,
  @DOCTYPEIDPAID	UD_DOCTYPEID,
  @NBRDOCUMENTPAID	UD_CORRELATIVOS,
  @DEPOSITPASSWORD	CHAR(20),
  @CURYTYPEID		UD_CURYTYPEID,
  @DATEPROCEPAIDMENT	UD_FECHA,
  @CURYID		UD_CURYID,
  @TOTAL		UD_MONTO,
  @CURYRATE		UD_CURYRATE,
  @CUSTIDSS		UD_CODCLIENT,
  @TAXREGNBR		UD_CODCLIENT,
  @USERID		UD_USERID
)
AS
BEGIN
	BEGIN TRAN

	DECLARE @BATNBRAR_CCDOCDET	UD_CORRELATIVOS

   	SELECT @BATNBRAR_CCDOCDET=NBRCORELATIVE FROM OP_CORELATIVE
				WHERE TYPECORELATIVE='BATNBRAR_CCDOCDET'

 	 /*** ACTUALIZACION DEL CORRELATIVO DE TIPO DE MOVIMIENTO ***/
    	UPDATE OP_CORELATIVE
    	SET    NBRCORELATIVE=NBRCORELATIVE+1   
	WHERE  TYPECORELATIVE='BATNBRAR_CCDOCDET' AND NBRCORELATIVE=@BATNBRAR_CCDOCDET

	IF (@@error!=0 OR @@ROWCOUNT=0 )
    	BEGIN
		RAISERROR  20000 'Error Actualizando OP_CORELATIVE'
        	ROLLBACK TRAN
        	RETURN(1)
    	END


	DECLARE @DEC_MONTO	INT
	DECLARE @CODMONEDABASE	UD_CURYID
	SELECT  @CODMONEDABASE=CURYORIG,@DEC_MONTO=FLOATOUTPUT FROM SETUP

	DECLARE @DOCTYPEID	UD_DOCTYPEID
	DECLARE @NBRDOCUMENT	UD_CORRELATIVOS
	DECLARE @PAGOPEN	UD_MONTO
	DECLARE @PAGOUSD	UD_MONTO

	DECLARE CUR_COBRANZA CURSOR
	FOR 	SELECT DOCTYPEID,NBRDOCUMENT,PAGOPEN,PAGOUSD FROM TMP_AR_COBRANZAS
		WHERE CORRNBR=@CORRNBR AND PAGOPEN>0
	
	OPEN CUR_COBRANZA
    	FETCH NEXT	FROM 	CUR_COBRANZA
		 	INTO  	@DOCTYPEID, @NBRDOCUMENT, @PAGOPEN, @PAGOUSD


      	WHILE @@FETCH_STATUS=0
        BEGIN
		IF(@CURYID=@CODMONEDABASE)
			BEGIN
				SET @PAGOPEN=@PAGOPEN
				SET @PAGOUSD=ROUND(@PAGOPEN/@CURYRATE,@DEC_MONTO)

			END
		ELSE
			BEGIN
				SET @PAGOPEN=ROUND(@PAGOPEN*@CURYRATE,@DEC_MONTO)
				SET @PAGOUSD=@PAGOPEN
			END 	

		UPDATE 	AR_CCDOC
		SET 	PAIDMENTPEN=PAIDMENTPEN + @PAGOPEN,
			PAIDMENTUSD=PAIDMENTUSD + @PAGOUSD,
			DOCVALPEN=DOCVALPEN-@PAGOPEN,
			DOCVALUSD=DOCVALUSD-@PAGOUSD
		WHERE   DOCTYPEID=@DOCTYPEID AND NBRDOCUMENT=@NBRDOCUMENT AND CUSTIDSS=@CUSTIDSS

		IF (@@ERROR!=0)
          	BEGIN       
            		CLOSE CUR_COBRANZA
            		DEALLOCATE CUR_COBRANZA
	    		ROLLBACK
            		RETURN(1)  
          	END



		--INSERTAMOS EN AR_CCDOCDET
		INSERT INTO AR_CCDOCDET(BATNBRAR_CCDOCDET,SITEID,DOCTYPEID,NBRDOCUMENT,DOCTYPEIDPAID,
					NBRDOCUMENTPAID,DEPOSITPASSWORD,CURYTYPEID,DATEPROCEPAIDMENT,
					PAIDMENTPEN,PAIDMENTUSD,CURYID,CURYRATE,CUSTIDSS,TAXREGNBR,
					DOCSUMMARYTTYPEID,REFNBR,USERID,DOCSTATUSID)
    		VALUES(@BATNBRAR_CCDOCDET,@SITEID,@DOCTYPEID,@NBRDOCUMENT,@DOCTYPEIDPAID,
					@NBRDOCUMENTPAID,@DEPOSITPASSWORD,@CURYTYPEID,@DATEPROCEPAIDMENT,
					@PAGOPEN,@PAGOUSD,@CURYID,@CURYRATE,@CUSTIDSS,@TAXREGNBR,
					NULL,NULL,@USERID,'OK')

		IF (@@ERROR!=0)
          	BEGIN       
            		CLOSE CUR_COBRANZA
            		DEALLOCATE CUR_COBRANZA
	    		ROLLBACK
            		RETURN(1)  
          	END

    		FETCH NEXT	FROM 	CUR_COBRANZA
		 		INTO  	@DOCTYPEID, @NBRDOCUMENT, @PAGOPEN, @PAGOUSD

	END

	-- SE ACTUALIZA EL ESTADO DEL DOCUMENTO SI EL PAGO IGUALA AL TOTAL
		IF(@CURYID=@CODMONEDABASE)
			BEGIN
				UPDATE AR_CCDOC
				SET  DOCSTATUSID='CA'
				WHERE CUSTIDSS=@CUSTIDSS AND TOTALPEN=PAIDMENTPEN
			END
		ELSE
			BEGIN
				UPDATE AR_CCDOC
				SET  DOCSTATUSID='CA'
				WHERE CUSTIDSS=@CUSTIDSS AND TOTALUSD=PAIDMENTUSD
			END

		IF (@@ERROR!=0)
          	BEGIN       
            		CLOSE CUR_COBRANZA
            		DEALLOCATE CUR_COBRANZA
	    		ROLLBACK
            		RETURN(1)  
          	END 	


	UPDATE OP_CUST_LIMIT	
	SET CURRBAL=CURRBAL-@TOTAL
	WHERE CUSTIDSS=@CUSTIDSS AND SITEID=@SITEID

	IF (@@ERROR!=0)
        BEGIN       
            	CLOSE CUR_COBRANZA
            	DEALLOCATE CUR_COBRANZA
	    	ROLLBACK
            	RETURN(1)  
        END
	
	SELECT @BATNBRAR_CCDOCDET AS CORRELATIVO

  	CLOSE CUR_COBRANZA
  	DEALLOCATE CUR_COBRANZA  
	COMMIT TRAN
	RETURN(0)
	
END

GO 

DROP PROC ANULAR_COBRANZA_PROC
GO
CREATE PROC ANULAR_COBRANZA_PROC
( @BATNBRAR_CCDOCDET	UD_CORRELATIVOS,
  @CURYID	UD_CURYID,
  @SITEID	UD_IDSITE,
  @CUSTIDSS	UD_CODCLIENT,
  @TOTAL	UD_MONTO,
  @USERID	UD_USERID
)
AS
BEGIN
	BEGIN TRAN

	DECLARE @DEC_MONTO	INT
	DECLARE @CODMONEDABASE	UD_CURYID
	SELECT  @CODMONEDABASE=CURYORIG,@DEC_MONTO=FLOATOUTPUT FROM SETUP


	DECLARE @DOCTYPEID	UD_DOCTYPEID
	DECLARE @NBRDOCUMENT	UD_CORRELATIVOS
	DECLARE @PAGOPEN	UD_MONTO
	DECLARE @PAGOUSD	UD_MONTO	

	DECLARE CUR_COBRANZA	CURSOR
	FOR SELECT DOCTYPEID,NBRDOCUMENT,PAIDMENTPEN,PAIDMENTUSD FROM AR_CCDOCDET WHERE BATNBRAR_CCDOCDET=@BATNBRAR_CCDOCDET AND SITEID=@SITEID
	OPEN CUR_COBRANZA

	FETCH NEXT FROM CUR_COBRANZA
		   INTO @DOCTYPEID, @NBRDOCUMENT, @PAGOPEN, @PAGOUSD

	WHILE @@FETCH_STATUS=0
	BEGIN

		UPDATE 	AR_CCDOC
		SET 	PAIDMENTPEN=PAIDMENTPEN - @PAGOPEN,
			PAIDMENTUSD=PAIDMENTUSD - @PAGOUSD,
			DOCVALPEN=DOCVALPEN + @PAGOPEN,
			DOCVALUSD=DOCVALUSD + @PAGOUSD
		WHERE   DOCTYPEID=@DOCTYPEID AND NBRDOCUMENT=@NBRDOCUMENT AND CUSTIDSS=@CUSTIDSS


		IF (@@ERROR!=0)
          	BEGIN       
            		CLOSE CUR_COBRANZA
            		DEALLOCATE CUR_COBRANZA
	    		ROLLBACK
			RAISERROR 20000 'Error Actualizando en AR_CCDOC' 
            		RETURN(1)  
          	END

		FETCH NEXT FROM CUR_COBRANZA
			   INTO @DOCTYPEID, @NBRDOCUMENT, @PAGOPEN, @PAGOUSD

	END

	-- SE ACTUALIZA EL ESTADO DEL DOCUMENTO SI EL PAGO IGUALA AL TOTAL
	IF(@CURYID=@CODMONEDABASE)
		BEGIN
			UPDATE AR_CCDOC
			SET  DOCSTATUSID='PE'
			WHERE CUSTIDSS=@CUSTIDSS AND TOTALPEN<>PAIDMENTPEN
		END
	ELSE
		BEGIN
			UPDATE AR_CCDOC
			SET  DOCSTATUSID='PE'
			WHERE CUSTIDSS=@CUSTIDSS AND TOTALUSD<>PAIDMENTUSD
		END

	IF (@@ERROR!=0)
        	BEGIN       
            		CLOSE CUR_COBRANZA
            		DEALLOCATE CUR_COBRANZA
	    		ROLLBACK
			RAISERROR 20000 'Error Actualizando estado de docmento en AR_CCDOC'
            		RETURN(1)  
          	END 	

	-- SE ACTUALIZA LA DEUDA DEL CLIENTE 
	UPDATE OP_CUST_LIMIT	
	SET CURRBAL=CURRBAL + @TOTAL
	WHERE CUSTIDSS=@CUSTIDSS AND SITEID=@SITEID

	IF (@@ERROR!=0)
        BEGIN       
            	CLOSE CUR_COBRANZA
            	DEALLOCATE CUR_COBRANZA
	    	ROLLBACK
		RAISERROR 20000 'Error Actualizando en CUST_LIMIT'
            	RETURN(1)  
        END

	UPDATE AR_CCDOCDET
	SET DOCSTATUSID='AN'
	WHERE BATNBRAR_CCDOCDET=@BATNBRAR_CCDOCDET AND SITEID=@SITEID

	IF (@@ERROR!=0)
        BEGIN       
            	CLOSE CUR_COBRANZA
            	DEALLOCATE CUR_COBRANZA
	    	ROLLBACK
		RAISERROR 20000 'Error Actualizando en AR_CCDOCDET'
            	RETURN(1)  
        END

	CLOSE CUR_COBRANZA
	DEALLOCATE CUR_COBRANZA	
	COMMIT TRAN
	RETURN(0)
END 


GO


DROP PROC OBTIENE_TOTAL_PAGO
GO
CREATE PROC OBTIENE_TOTAL_PAGO
( @BATNBRAR_CCDOCDET	UD_CORRELATIVOS,
  @SITEID		UD_IDSITE,
  @CURYID		UD_CURYID
)
AS
BEGIN 

	
	DECLARE @DEC_MONTO	INT
	DECLARE @CODMONEDABASE	UD_CURYID
	SELECT  @CODMONEDABASE=CURYORIG,@DEC_MONTO=FLOATOUTPUT FROM SETUP	

	IF(@CURYID=@CODMONEDABASE)
		BEGIN
			SELECT  SUM(PAIDMENTPEN) AS TOTAL FROM  AR_CCDOCDET
			WHERE  	BATNBRAR_CCDOCDET=@BATNBRAR_CCDOCDET AND SITEID=@SITEID
		END
	ELSE
		BEGIN
			SELECT  SUM(PAIDMENTUSD) AS TOTAL FROM  AR_CCDOCDET
			WHERE  	BATNBRAR_CCDOCDET=@BATNBRAR_CCDOCDET AND SITEID=@SITEID
		END



END


GO

DROP PROC TMP_AR_COBRANZASUpd_Proc
GO
CREATE PROC TMP_AR_COBRANZASUpd_Proc
( @CORRNBR	INT,
  @DOCTYPEID	UD_DOCTYPEID,
  @NBRDOCUMENT	UD_CORRELATIVOS,
  @MONTOPAGO	UD_MONTO
)
AS
BEGIN
	BEGIN TRAN 

	UPDATE TMP_AR_COBRANZAS
	SET PAGOPEN=@MONTOPAGO
	WHERE CORRNBR=@CORRNBR AND DOCTYPEID=@DOCTYPEID AND NBRDOCUMENT=@NBRDOCUMENT


	IF(@@ERROR!=0 AND @@ROWCOUNT=0)
	BEGIN
		ROLLBACK	
		RAISERROR 20000 'Error Actualizando Monto a Pagar'
		RETURN(1)
	END
 
	COMMIT TRAN
	RETURN(0)
END

DELETE FROM IN_INVENTPHISICAL