IF EXISTS (SELECT
						 *
					 FROM
						 SYS.OBJECTS
					 WHERE
						NAME = 'UFD_DETALHE_DEMISSAO')
	DROP FUNCTION [dbo].[UFD_DETALHE_DEMISSAO] 
GO 

--select * from UFD_DETALHE_DEMISSAO(1,532676,2207261)

CREATE FUNCTION [dbo].[UFD_DETALHE_DEMISSAO] (@CD_EMP INT, @CD_DEMISSAO INT, @CD_CLI INT)
RETURNS @UFD_RETURN TABLE(
       CD_EMP INT, 
       CD_CONV_DEMITE INT, 
       CD_CLI INT, 
       NR_CARTAO VARCHAR(30), 
       MATRICULA VARCHAR(30), 
       RZ_CLI VARCHAR(150), 
       VLR_CONV MONEY,
       DT_CAD DATETIME,
       DT_DISP DATETIME,
       QDE INT,
       SOLICITANTE VARCHAR(150),
       ORIGEM VARCHAR(50),
       DIA_FAT INT,
       DT_FECH_AGD DATETIME,
       DEMITE_MONTH INT,
       DEMITE_DAY INT
    
    )

	BEGIN

        DECLARE @DT_DISP DATETIME;
        DECLARE @DT_FECHA DATE;
        DECLARE @DT_FECH_AGD DATE;
        DECLARE @CD_CONV INT;
        DECLARE @DIA_FECH INT;
        DECLARE @ULTIMO_DIA_MES INT;

        SELECT @DT_DISP = DT_DISP, @CD_CONV = CD_CONV FROM WEB_CONV_DEMITE WHERE CD_CONV_DEMITE = @CD_DEMISSAO
        SELECT @DIA_FECH = DIA_FAT FROM RC_CLI_CONV WHERE CD_CONV = @CD_CONV

        

--      SET @ULTIMO_DIA_MES = DAY(EOMONTH(datefromparts(year(@DT_DISP), month(@DT_DISP), 1)));
	    SET @ULTIMO_DIA_MES = datepart(day, DATEADD( MONTH, 1 + DATEDIFF(MONTH,0,@DT_DISP ),-1) );

--      SET @DIA_FECH = IIF(@DIA_FECH > @ULTIMO_DIA_MES, @ULTIMO_DIA_MES, @DIA_FECH)
		IF @DIA_FECH > @ULTIMO_DIA_MES
		BEGIN 
			SET @DIA_FECH = @ULTIMO_DIA_MES 
		END 
		ELSE 
		BEGIN 
			SET @DIA_FECH = @DIA_FECH 
		END  

--      SET @DT_FECHA = datefromparts(year(@DT_DISP), month(@DT_DISP), @DIA_FECH);
		SET @DT_FECHA = DATEADD (DAY,@DIA_FECH-1,DATEADD(MONTH, MONTH(@DT_DISP)-1,DATEADD (YEAR,YEAR(@DT_DISP)-1900,'1900-01-01') ));


        IF DAY(@DT_DISP) <= @DIA_FECH
            SET @DT_FECHA = DATEADD(MONTH, -1, @DT_FECHA)

        IF MONTH(@DT_FECHA) = MONTH(@DT_DISP) AND DAY(@DT_DISP) <= @DIA_FECH
        BEGIN
            SET @DT_FECHA = DATEADD(DAY, -(DAY(@DT_FECHA)), @DT_FECHA)
            SET @DIA_FECH = DAY(@DT_FECHA)
        END

        SELECT @DT_FECH_AGD = DT_FECH_AGD
        FROM RC_CLI_CONV_AGD_FECH
        WHERE CD_CONV = @CD_CONV AND CONVERT(CHAR(6),DT_FECH_CONV, 112) = CONVERT(CHAR(6), @DT_FECHA, 112)

        IF NOT @DT_FECH_AGD IS NULL AND DAY(@DT_FECH_AGD) < DAY(@DT_FECHA)
            SET @DT_FECHA = @DT_FECH_AGD


        SET @DT_FECHA = DATEADD(DAY, 1, @DT_FECHA)

        INSERT INTO @UFD_RETURN 
        SELECT 
            RC_CLI.CD_EMP, 
            V.CD_CONV_DEMITE, 
            RC_CLI.CD_CLI, 
            RC_CLI_FIDELI_CARTAO.NR_CARTAO, 
            C.MATRICULA, 
            RC_CLI.RZ_CLI, 
            ISNULL(RC_VD_CONV.VLR_CONV, 0) AS VLR_CONV,
            V.DT_CAD,
            V.DT_DISP,
            V.QDE,
            V.SOLICITANTE,
            V.ORIGEM,
            RC_CLI_CONV.DIA_FAT,
            AGD.DT_FECH_AGD,
            MONTH	(V.DT_CAD)  DEMITE_MONTH,
            DAY	(V.DT_CAD)  DEMITE_DAY
        FROM   V_WEB_LIST_DEMISSOES V 
            INNER JOIN WEB_CONV_DEMITE_RC_CLI 
                    ON V.CD_EMP = WEB_CONV_DEMITE_RC_CLI.CD_EMP 
                        AND V.CD_CONV_DEMITE = WEB_CONV_DEMITE_RC_CLI.CD_CONV_DEMITE 
            INNER JOIN RC_CLI 
                    ON WEB_CONV_DEMITE_RC_CLI.CD_EMP = RC_CLI.CD_EMP 
                        AND WEB_CONV_DEMITE_RC_CLI.CD_CLI = RC_CLI.CD_CLI 
            INNER JOIN RC_CLI_CONV 
                    ON V.CD_EMP = RC_CLI_CONV.CD_EMP AND  V.CD_CONV = RC_CLI_CONV.CD_CONV
            LEFT JOIN RC_CLI_FIDELI_CARTAO 
                    ON RC_CLI.CD_EMP = RC_CLI_FIDELI_CARTAO.CD_EMP 
                        AND RC_CLI.CD_CLI = RC_CLI_FIDELI_CARTAO.CD_CLI 
            LEFT JOIN RC_VD_CONV 
                    ON RC_VD_CONV.CD_EMP = RC_CLI.CD_EMP 
                        AND RC_VD_CONV.CD_CLI = RC_CLI.CD_CLI 
                        AND V.CD_CONV = RC_VD_CONV.CD_CONV 
                        AND RC_CLI.CD_EMP = @CD_EMP 
                        AND RC_VD_CONV.VLR_CONV > 0 
                        AND RC_VD_CONV.ST_FECHVD_CONV IN ( 0, 1, 5 ) 
                        AND RC_VD_CONV.DT_FECHA >= @DT_FECHA 
            LEFT JOIN RC_CLI_CONV_RC_CLI C 
                    ON C.CD_EMP = RC_CLI.CD_EMP 
                        AND C.CD_CLI = RC_CLI.CD_CLI 
            LEFT JOIN RC_CLI_CONV_AGD_FECH AS AGD 
                    ON V.CD_CONV = AGD.CD_CONV 
                        AND YEAR(AGD.DT_FECH_CONV) = YEAR(V.DT_CAD) 
                        AND MONTH(AGD.DT_FECH_CONV) = MONTH(V.DT_CAD) 
        WHERE  ( V.CD_CONV_DEMITE = @CD_DEMISSAO ) 
            AND ( CASE 
                    WHEN RC_VD_CONV.DT_VD = CAST(@DT_DISP AS DATE) THEN CAST( 
                    RC_VD_CONV.HR_VD AS TIME) 
                    ELSE CAST(@DT_DISP AS TIME) 
                    END ) <= CAST(@DT_DISP AS TIME) 
            AND @CD_CLI = CASE WHEN @CD_CLI > 0 THEN RC_CLI.CD_CLI ELSE 0 END

			RETURN
	END 