SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

--SP_REP_FAC_CRED '20080501','20080602','PEN',1,'',1,'20119071641',0,0,'CA',0
ALTER  PROC SP_REP_FAC_CRED
(
 @FECHAINI	CHAR(15),
 @FECHAFIN	CHAR(15),
 @MONEDA	UD_CURYID,
 @TODOS_DOC	INT=1,	 -- (1) TODOS LOD DOC EN @MONEDA, (0) SOLO LOS DOC EN @MONEDA
 @ALMACENES	VARCHAR(255)='', -- CADENA DE ALMACENES
 @TODOS_ALM	INT=1,       --TODOS LOS ALMACENES
 @CRITERIOS	VARCHAR(255)='', --solo los elementos que cumplan la condicion
 @TODOS_CON	INT=1,       --TODOS LOS elementos del tipo de condicion
 @PAGO		INT=0,
 @STATUS        CHAR(2),
 @TODOSESTADO   INT    -- Verifica si son Todos los Estados (1) o es solo alguno (0)
)
AS

   DECLARE  @DATFECHAINI SMALLDATETIME
   DECLARE  @DATFECHAFIN SMALLDATETIME
       
        SET @DATFECHAINI=CAST(RTRIM(LTRIM(@FECHAINI)) AS SMALLDATETIME)
        SET @DATFECHAFIN=CAST(RTRIM(LTRIM(@FECHAFIN)) AS SMALLDATETIME)
         
	DECLARE @CADENA	VARCHAR(8000)
--MODIFICADO PARA Q EL RANGO DE FECHAS AFECTE A LA FECHA DE PAGO... Y NO A LA FECHA DL DOCUMENTO      
IF @MONEDA = 'PEN'
BEGIN
--CD.PAIDMENTPEN
--ANTES A.PAIDMENTPEN
----ANTES DOCDATE AHORA DATEPROCEPAIDMENT
	SET @CADENA="SELECT D.MINDES,A.NBRDOCUMENT,C.CUSTNAME,CD.DATEPROCEPAIDMENT AS DOCDATE,A.DUEDATE," +
		"A.TOTALPEN AS TOTAL,CD.PAIDMENTPEN AS PAGADO,A.DOCVALPEN AS SALDO,CASE A.DOCSTATUSID WHEN 'CA' THEN '  CA' ELSE '  PE' END AS DOCSTATUSID " +
		"FROM AR_CCDOC A  " +
		"LEFT JOIN AR_CCDOCDET CD ON (A.SITEID = CD.SITEID AND A.DOCTYPEID = CD.DOCTYPEID AND A.NBRDOCUMENT = CD.NBRDOCUMENT) " +
		"INNER JOIN DOCTYPE D ON (A.DOCTYPEID=D.DOCTYPEID) " +
		"INNER JOIN CUSTOMER C ON (C.CUSTIDSS = A.CUSTIDSS) " +
		"WHERE A.DOCSTATUSID NOT IN ('AN') " +
		"AND A.DOCTYPEID = '01' AND LEFT(A.NBRDOCUMENT,2) <> '17' "

        IF @TODOSESTADO=1 -- SON TODOS
           BEGIN		--DOCDATE
		SET @CADENA=@CADENA + "AND A.DOCDATE BETWEEN  '" + 
		RTRIM(LTRIM(CAST(@DATFECHAINI AS CHAR(100)))) + "' AND '" + RTRIM(LTRIM(CAST(@DATFECHAFIN AS CHAR(100)))) + "'"
           END
        ELSE
           BEGIN
		IF @STATUS IN ('TD','PE')
		BEGIN
			SET @CADENA=@CADENA + "AND CD.DATEPROCEPAIDMENT BETWEEN  '" + 
			RTRIM(LTRIM(CAST(@DATFECHAINI AS CHAR(100)))) + "' AND '" + RTRIM(LTRIM(CAST(@DATFECHAFIN AS CHAR(100)))) + "'" + 
			" AND A.DOCSTATUSID ='" + @STATUS + "'"
		END
		ELSE
		BEGIN
			SET @CADENA=@CADENA + "AND CD.DATEPROCEPAIDMENT BETWEEN  '" + 
			RTRIM(LTRIM(CAST(@DATFECHAINI AS CHAR(100)))) + "' AND '" + RTRIM(LTRIM(CAST(@DATFECHAFIN AS CHAR(100)))) + "'" --+ 
			--" AND A.DOCSTATUSID ='" + @STATUS + "'"
		END
           END   


	IF (@TODOS_ALM=0)
                 BEGIN
              	SET @CADENA=@CADENA + " AND A.SITEID IN (" + @ALMACENES + ")" 
                  END
          
	IF (@TODOS_CON=0)
                BEGIN
		SET @CADENA=@CADENA + " AND C.CUSTIDSS IN (" + @CRITERIOS + ")"
                END
	SET @CADENA=@CADENA + " GROUP BY D.MINDES,A.NBRDOCUMENT,C.CUSTNAME,CD.DATEPROCEPAIDMENT,A.DUEDATE," +
		"A.TOTALPEN,CD.PAIDMENTPEN,A.DOCVALPEN,A.DOCSTATUSID ORDER BY A.NBRDOCUMENT"

END
ELSE
BEGIN
	SET @CADENA="SELECT D.MINDES,A.NBRDOCUMENT,C.CUSTNAME,CD.DATEPROCEPAIDMENT AS DOCDATE,A.DUEDATE," +
		"A.TOTALUSD AS TOTAL,CD.PAIDMENTUSD AS PAGADO,A.DOCVALUSD AS SALDO,CASE A.DOCSTATUSID WHEN 'CA' THEN 'CANCEL' ELSE 'PENDIENT' END AS DOCSTATUSID " +
		"FROM AR_CCDOC A  " +
		"LEFT JOIN AR_CCDOCDET CD ON (A.SITEID = CD.SITEID AND A.DOCTYPEID = CD.DOCTYPEID AND A.NBRDOCUMENT = CD.NBRDOCUMENT) " +
		"INNER JOIN DOCTYPE D ON (A.DOCTYPEID=D.DOCTYPEID) " +
		"INNER JOIN CUSTOMER C ON (C.CUSTIDSS = A.CUSTIDSS) " +
		"WHERE DOCSTATUSID NOT IN ('AN') " +
		"AND A.DOCTYPEID = '01' AND LEFT(A.NBRDOCUMENT,2) <> '17' "

        IF @TODOSESTADO=1 -- SON TODOS
           BEGIN		--DOCDATE
		SET @CADENA=@CADENA + "AND A.DOCDATE BETWEEN  '" + 
		RTRIM(LTRIM(CAST(@DATFECHAINI AS CHAR(100)))) + "' AND '" + RTRIM(LTRIM(CAST(@DATFECHAFIN AS CHAR(100)))) + "'"
           END
        ELSE
           BEGIN
		IF @STATUS IN ('TD','PE')
		BEGIN
			SET @CADENA=@CADENA + "AND CD.DATEPROCEPAIDMENT BETWEEN  '" + 
			RTRIM(LTRIM(CAST(@DATFECHAINI AS CHAR(100)))) + "' AND '" + RTRIM(LTRIM(CAST(@DATFECHAFIN AS CHAR(100)))) + "'" + 
			" AND A.DOCSTATUSID ='" + @STATUS + "'"
		END
		ELSE
		BEGIN
			SET @CADENA=@CADENA + "AND CD.DATEPROCEPAIDMENT BETWEEN  '" + 
			RTRIM(LTRIM(CAST(@DATFECHAINI AS CHAR(100)))) + "' AND '" + RTRIM(LTRIM(CAST(@DATFECHAFIN AS CHAR(100)))) + "'" + 
			" AND A.DOCSTATUSID ='" + @STATUS + "'"
		END
           END   


	IF (@TODOS_ALM=0)
                 BEGIN
              	SET @CADENA=@CADENA + " AND SITEID IN (" + @ALMACENES + ")" 
                  END
          
	IF (@TODOS_CON=0)
                BEGIN
		SET @CADENA=@CADENA + " AND C.CUSTIDSS IN (" + @CRITERIOS + ")"
                END
	SET @CADENA=@CADENA + "  GROUP BY D.MINDES,A.NBRDOCUMENT,C.CUSTNAME,CD.DATEPROCEPAIDMENT,A.DUEDATE," +
		"A.TOTALUSD,CD.PAIDMENTUSD,A.DOCVALUSD,A.DOCSTATUSID ORDER BY A.NBRDOCUMENT"

END
--AND A.NBRDOCUMENT IN ('1020030569','1020030648','1020030654')
--MINDES,NBRDOCUMENT,CUSTNAME,DOCDATE,DUEDATE,TOTALPEN,TOTALUSD,PAIDMENTPEN,PAIDMENTUSD,DOCVALPEN,DOCVALUSD,DOCSTATUSID--,*
--SELECT * FROM CUSTOMER
	EXEC (@CADENA)	

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

