IF EXISTS (SELECT 1 FROM SYS.OBJECTS WHERE	NAME = 'UFD_DETALHE_DEMISSAO_ITEM')
	DROP FUNCTION [DBO].[UFD_DETALHE_DEMISSAO_ITEM] 
GO 
/*
=======================================================================================
'Nível Classificação.: INFORMAÇÃO RESTRITA
'Versão..............: 231006
'Job.................:  
'Nome................: [UFD_DETALHE_DEMISSAO_ITEM]
'Objetivo............: 
'Tabelas.............: 
'Dt Criação..........: 06/10/2023
'Dt Ult Alteracão....: --
'Criado por..........: --
'Alterado por........: Marcos Barros
'=======================================================================================
*/


CREATE FUNCTION [DBO].[UFD_DETALHE_DEMISSAO_ITEM] (@CD_EMP INT, @CD_DEMISSAO INT, @CD_CLI INT)
RETURNS @UFD_RETURN TABLE(
       	  CD_CTR                   INT
        , CD_CLI                   INT
        , RZ_CLI                   VARCHAR(250)
        , DT_VD                    DATETIME
        , CD_FILIAL                INT
        , RZ_FILIAL                VARCHAR(150)
        , CD_CX                    INT
        , NR_ECF                   INT
        , VLR_CONV                 MONEY
        , ST_FECHVD_CONV           TINYINT
        , HR_VD                    DATETIME
        , MATRICULA                VARCHAR(50)
        , FLAG_CONVENIADO_DEMISSAO INT
        , PARC                     INT
        , CD_PROD                  INT
        , QT_IT                    MONEY
        , VLR_IT                   MONEY
        , TOT_IT                   MONEY
        , VLR_DESC                 MONEY
        , DS_USUAL                 VARCHAR(250)
        , DS_ARV_MERC_LINHA        VARCHAR(150)
        , DS_ARV_MERC_CATEG        VARCHAR(150)
        , DS_ARV_MERC_DEPTO        VARCHAR(150)
        , NM_FABRIC                VARCHAR(150)
        , VLR_CONV_IT              MONEY
        , CD_MEDICO_REC            INT
        , CD_IT                    INT
        , CD_VD                    INT
        , CD_PLANO_VENDA           INT
        , DS_PLANO_VENDA           VARCHAR(30)
        , DIA_FAT                  INT
        , DIA_VENCTO               INT
        , ST_IT                    SMALLINT

    )

	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_VD_CONV.CD_CTR
            , RC_CLI.CD_CLI
            , RZ_CLI
            , RC_VD_CONV.DT_VD
            , RC_VD_CONV.CD_FILIAL
            , A.RZ_FILIAL
            , RC_VD_CONV.CD_CX
            , RC_VD_CONV.NR_ECF
            , RC_VD_CONV.VLR_CONV - ISNULL(G.VLR_CRED_CONV, 0) VLR_CONV
            , RC_VD_CONV.ST_FECHVD_CONV
            , F.HR_VD
            , ISNULL(C.MATRICULA, 0) AS MATRICULA
            , ISNULL(C.FLAG_CONVENIADO_DEMISSAO, 1) AS FLAG_CONVENIADO_DEMISSAO
            , RC_VD_CONV.PARC
            , IT.CD_PROD
            , IT.QT_IT
            , IT.VLR_IT
            , IT.TOT_IT
            , ROUND((((IT.TX_DESC * IT.VLR_IT) / 100) * IT.QT_IT), 2) AS VLR_DESC
            , PROD.DS_USUAL
            , LINHA.DS_ARV_MERC_LINHA
            , CATEG.DS_ARV_MERC_CATEG
            , DEPTO.DS_ARV_MERC_DEPTO
            , FABRIC.NM_FABRIC 
            , (CASE 
                    WHEN IT.ST_IT = 2 OR IT.LIBERA_VD_CONV = 0 THEN 0
                    ELSE  IT.TOT_IT / CAST(F.VLR_LIQ_VD AS DECIMAL(12,9)) * F.VLR_CONV END) AS VLR_CONV_IT
            , ISNULL(IR.CD_MEDICO_REC, 0) CD_MEDICO_REC
            , IT.CD_IT
            , F.CD_VD
            , ISNULL(PVENDA.CD_PLANO_VENDA, 0) CD_PLANO_VENDA
            , ISNULL(PVENDA.DS_PLANO_VENDA, 'NAO DEFINIDO') DS_PLANO_VENDA
            , RC_CLI_CONV.DIA_FAT
            , RC_CLI_CONV.DIA_VENCTO
            , IT.ST_IT

        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
            INNER 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 
            INNER JOIN PRC_FILIAL AS A 
                    ON RC_VD_CONV.CD_EMP = A.CD_EMP
                        AND RC_VD_CONV.CD_FILIAL = A.CD_FILIAL
            INNER JOIN PDV_VD_RC_VD_CONV AS E 
                    ON RC_VD_CONV.CD_EMP = E.CD_EMP
                        AND RC_VD_CONV.CD_CTR = E.CD_CTR
            INNER JOIN PDV_VD AS F 
                    ON F.CD_EMP = E.CD_EMP
                        AND F.CD_FILIAL = E.CD_FILIAL
                        AND F.CD_VD = E.CD_VD
                        AND F.ST_VD = 0
            INNER JOIN PDV_VD_IT IT 
                    ON F.CD_VD = IT.CD_VD
                        AND F.CD_EMP = IT.CD_EMP
                        AND F.CD_FILIAL = IT.CD_FILIAL

            INNER JOIN EST_PROD PROD 
                    ON IT.CD_PROD = PROD.CD_PROD
                    
            INNER JOIN EST_PROD_EST_ARV_MERCADOLOGICA ARVMERC 
                    ON PROD.CD_EMP = ARVMERC.CD_EMP
                        AND PROD.CD_PROD = ARVMERC.CD_PROD
            INNER JOIN EST_ARV_MERC_LINHA LINHA 
                    ON ARVMERC.CD_EMP = LINHA.CD_EMP
                        AND ARVMERC.CD_ARV_MERC_LINHA = LINHA.CD_ARV_MERC_LINHA
            INNER JOIN EST_ARV_MERC_CATEGORIA CATEG 
                    ON ARVMERC.CD_EMP = CATEG.CD_EMP
                        AND ARVMERC.CD_ARV_MERC_CATEG = CATEG.CD_ARV_MERC_CATEG
            INNER JOIN EST_ARV_MERC_DEPTO DEPTO 
                    ON ARVMERC.CD_EMP = DEPTO.CD_EMP
                        AND CATEG.CD_ARV_MERC_DEPTO = DEPTO.CD_ARV_MERC_DEPTO
            INNER JOIN EST_PROD_FABRIC FABRIC 
                    ON PROD.CD_FABRIC = FABRIC.CD_FABRIC
            

            LEFT OUTER JOIN PDV_VD_IT_REC AS IR 
                    ON IT.CD_EMP = IR.CD_EMP
                        AND IT.CD_FILIAL = IR.CD_FILIAL
                        AND IT.CD_VD = IR.CD_VD
                        AND IT.CD_IT = IR.CD_IT

            LEFT JOIN (SELECT CD_EMP , CD_CTR , SUM(ISNULL(VLR_CRED, 0)) VLR_CRED_CONV FROM RC_VD_CONV_CRED GROUP BY CD_EMP , CD_CTR ) G 
                    ON RC_VD_CONV.CD_EMP = G.CD_EMP
                        AND RC_VD_CONV.CD_CTR = G.CD_CTR

            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_CLI_CONV_RC_CLI C ON C.CD_EMP   = RC_CLI.CD_EMP 
										  AND C.CD_CLI   = RC_CLI.CD_CLI 
										  AND C.CD_CONV	 = RC_VD_CONV.CD_CONV 

		    INNER JOIN GLB_PLANO_VENDA AS PVENDA 
                    ON C.CD_PLANO_VENDA = PVENDA.CD_PLANO_VENDA

            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 CAST(RC_VD_CONV.DT_VD AS DATE) <= CAST(@DT_DISP AS DATE)
            AND @CD_CLI = CASE WHEN @CD_CLI > 0 THEN RC_CLI.CD_CLI ELSE 0 END

-- ::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
-- TRATAR VENDA DE CONVENIO COM + DE 1 ITEM. 
-- ::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
DECLARE @TAB TABLE ( CD_FILIAL INT, 
					 NR_ECF    INT, 
					 CD_CX     INT,
					 PARC	   INT,
					 CD_PROD   INT,
					 NEW INT )


 INSERT INTO @TAB
	SELECT   A.*  
      FROM @UFD_RETURN B
           JOIN (
				SELECT  CD_FILIAL, 
						NR_ECF,
						CD_CX , 
						PARC,
						CD_PROD,
						ROW_NUMBER() OVER(PARTITION BY PARC,  NR_ECF,CD_FILIAL ORDER BY PARC ASC)  NEW  
				  FROM @UFD_RETURN 
				 GROUP BY  CD_FILIAL,
						   NR_ECF,
						   CD_CX,
						   PARC,
						   CD_PROD 
						   ) A ON B.PARC    = A.PARC 
							  AND B.CD_PROD = A.CD_PROD 
							  AND B.NR_ECF  = A.NR_ECF 
 ORDER BY PARC ASC


 														  
UPDATE UFD_RETURN
  SET VLR_CONV = 0 
 FROM @UFD_RETURN UFD_RETURN
      JOIN @TAB TAB ON UFD_RETURN.CD_FILIAL	= TAB.CD_FILIAL     
	               AND UFD_RETURN.NR_ECF    = TAB.NR_ECF
				   AND UFD_RETURN.CD_CX     = TAB.CD_CX
				   AND UFD_RETURN.PARC      = TAB.PARC 
			       AND UFD_RETURN.CD_PROD 	= TAB.CD_PROD
				  
 WHERE NEW > 1 

-- ::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
-- ::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::


			RETURN
	END 
