DROP PROCEDURE ReporteVentas_Vales2_Proc
GO
CREATE PROCEDURE ReporteVentas_Vales2_Proc
(
 @CORRNBR	INT,
 @FECHAINI	CHAR(15),
 @FECHAFIN	CHAR(15),
 @MONEDA	UD_CURYID,
 @TODOS_DOC	TINYINT=1,	-- (1) TODOS LOD DOC EN @MONEDA, (0) SOLO LOS DOC EN @MONEDA
 @ALMACENES	VARCHAR(255)='',-- CADENA DE ALMACENES
 @TODOS_ALM	TINYINT=1,--TODOS LOS ALMACENES
 @CRITERIOS	VARCHAR(255)='', --solo los elementos que cumplan la condicion
 @TODOS_CON	TINYINT=1, --TODOS LOS elementos del tipo de condicion
 @PAGO		TINYINT=0
)
AS
BEGIN
   DECLARE  @DATFECHAINI SMALLDATETIME
   DECLARE  @DATFECHAFIN SMALLDATETIME
      
   DECLARE @TIPOPAGO	CHAR(2)   
--  SET @FECHAINI='20021201'
--  SET @FECHAFIN='20021218'

	IF(@PAGO=0)
	BEGIN 
		SET @TIPOPAGO='06'
	END
	ELSE
	BEGIN
		SET @TIPOPAGO='14'
	END
       
        SET @DATFECHAINI=CAST(RTRIM(LTRIM(@FECHAINI)) AS SMALLDATETIME)
        SET @DATFECHAFIN=CAST(RTRIM(LTRIM(@FECHAFIN)) AS SMALLDATETIME)
         
        

	DECLARE @CADENA	VARCHAR(8000)
	DECLARE @MONEDABASE UD_CURYID	
	
	SET @MONEDABASE=(SELECT CURYORIG FROM SETUP)        --OBTENGO EL CODIGO DE LA MONEDA BASE
  
IF(@PAGO=0)
BEGIN
	IF (@MONEDA=@MONEDABASE) 
		BEGIN
			SET @CADENA="SELECT " + CAST(@CORRNBR AS CHAR(10)) + ",DOCTYPEID,MINDES,NBRDOCUMENT,SITEID,NAME,CUSTIDSS,CUSTNAME,RUC,NBRBONUS,PLACA,Codigo,DESCRINVENTORY,Cantidad,Precio,Total,DATEDOC,DATEPROCESALES " +
			    	    "FROM VW_REPORTE_VALES2_PEN " +
	        	            "WHERE DATEPROCESALES BETWEEN '" + RTRIM(LTRIM(CAST(@DATFECHAINI AS CHAR(100)))) + "' AND '" + RTRIM(LTRIM(CAST(@DATFECHAFIN AS CHAR(100)))) + "'"
		END
	ELSE
		BEGIN
			SET @CADENA="SELECT " + CAST(@CORRNBR AS CHAR(10)) + ",DOCTYPEID,MINDES,NBRDOCUMENT,SITEID,NAME,CUSTIDSS,CUSTNAME,RUC,NBRBONUS,PLACA,Codigo,DESCRINVENTORY,Cantidad,Precio,Total,DATEDOC,DATEPROCESALES " +
			    	    "FROM VW_REPORTE_VALES2_USD " +
	        	            "WHERE DATEPROCESALES BETWEEN '" + RTRIM(LTRIM(CAST(@DATFECHAINI AS CHAR(100)))) + "' AND '" + RTRIM(LTRIM(CAST(@DATFECHAFIN AS CHAR(100)))) + "'"
		END
END
ELSE
BEGIN

	IF (@MONEDA=@MONEDABASE) 
		BEGIN
			SET @CADENA="SELECT " + CAST(@CORRNBR AS CHAR(10)) + ",DOCTYPEID,MINDES,NBRDOCUMENT,SITEID,NAME,CUSTIDSS,CUSTNAME,RUC,NBRBONUS,PLACA,Codigo,DESCRINVENTORY,Cantidad,Precio,Total,DATEDOC,DATEPROCESALES " +
			    	    "FROM VW_REPORTE_VALES2_ADELANTO_PEN " +
	        	            "WHERE DATEPROCESALES BETWEEN '" + RTRIM(LTRIM(CAST(@DATFECHAINI AS CHAR(100)))) + "' AND '" + RTRIM(LTRIM(CAST(@DATFECHAFIN AS CHAR(100)))) + "'"
		END
	ELSE
		BEGIN
			SET @CADENA="SELECT " + CAST(@CORRNBR AS CHAR(10)) + ",DOCTYPEID,MINDES,NBRDOCUMENT,SITEID,NAME,CUSTIDSS,CUSTNAME,RUC,NBRBONUS,PLACA,Codigo,DESCRINVENTORY,Cantidad,Precio,Total,DATEDOC,DATEPROCESALES " +
			    	    "FROM VW_REPORTE_VALES2_ADELANTO_USD " +
	        	            "WHERE DATEPROCESALES BETWEEN '" + RTRIM(LTRIM(CAST(@DATFECHAINI AS CHAR(100)))) + "' AND '" + RTRIM(LTRIM(CAST(@DATFECHAFIN AS CHAR(100)))) + "'"
		END

END

	IF (@TODOS_ALM=0)
              	SET @CADENA=@CADENA + " AND SITEID IN (" + @ALMACENES + ")" 


	IF (@TODOS_CON=0)
		SET @CADENA=@CADENA + " AND CUSTIDSS IN (" + @CRITERIOS + ")"

	--SELECT 	@CADENA		
	
	INSERT INTO TMP_VENTAS_VALES EXEC (@CADENA)

	DECLARE @DOCTYPEID	UD_DOCTYPEID
	DECLARE @NBRDOCUMENT	UD_CORRELATIVOS
	DECLARE @SITEID		UD_IDSITE
	DECLARE @NBRBONUS	NUMERIC(10,0)

	DECLARE TMPVALES CURSOR
	FOR 	SELECT S.DOCTYPEID,S.NBRDOCUMENT,S.SITEID,B.NBRBONUS FROM OP_SALES AS S JOIN OP_BONUS AS B
		ON S.DOCTYPEID=B.DOCTYPEID AND S.NBRDOCUMENT=B.NBRDOCUMENT AND S.SITEID=B.SITEID AND S.CUSTIDSS=B.CUSTIDSS
		WHERE S.TYPEPAYMENTID=@TIPOPAGO AND S.DOCSTATUSID<>'AN' AND S.DATEPROCESALES >= CAST(@DATFECHAINI AS SMALLDATETIME) AND S.DATEPROCESALES <= CAST(@DATFECHAFIN AS SMALLDATETIME)
		ORDER BY B.NBRBONUS

	OPEN TMPVALES
	FETCH NEXT FROM TMPVALES INTO @DOCTYPEID,@NBRDOCUMENT,@SITEID,@NBRBONUS
	
	WHILE @@FETCH_STATUS=0
        BEGIN   

		UPDATE TMP_VENTAS_VALES SET NBRBONUS=LTRIM(RTRIM(ISNULL(NBRBONUS,''))) + "-" + LTRIM(RTRIM(CAST(@NBRBONUS AS CHAR(10))))
		WHERE DOCTYPEID=@DOCTYPEID AND NBRDOCUMENT=@NBRDOCUMENT AND SITEID=@SITEID AND CORRNBR=@CORRNBR


		FETCH NEXT FROM TMPVALES INTO @DOCTYPEID,@NBRDOCUMENT,@SITEID,@NBRBONUS
	END

	UPDATE TMP_VENTAS_VALES SET NBRBONUS=SUBSTRING(NBRBONUS,2,LEN(NBRBONUS)-1)
	WHERE CORRNBR=@CORRNBR

	CLOSE TMPVALES
	DEALLOCATE TMPVALES

	SELECT * FROM TMP_VENTAS_VALES WHERE CORRNBR=@CORRNBR

END 
GO




DELETE FROM TMP_VENTAS_VALES
GO
ReporteVentas_Vales2_Proc 10,'20030101','20030131','PEN'
GO
SELECT * FROM TMP_VENTAS_VALES
