IF NOT OBJECT_ID('GET_EXTRATO_FIDELI') IS NULL
	BEGIN DROP PROCEDURE GET_EXTRATO_FIDELI;
	END;
GO

 /*
=======================================================================================
'Nível Classificação.: INFORMAÇÃO RESTRITA
'Versão..............: 202203040019
'Job.................:  
'Nome................: GET_EXTRATO_FIDELI
'Objetivo............: 
'Tabelas.............: 
'Dt Criação..........: Juan Mello
'Dt Ult Alteracão....: 04-03-2022
'Alterado por........: Marcos barros
'Criado por..........: 
'=======================================================================================
FARMAGOI-23565 - 202203040019 - Incluído validação para desconsiderar itens cancelados
 */

CREATE PROCEDURE  [GET_EXTRATO_FIDELI] 
  -- Add the parameters for the stored procedure here   
@CD_CLI INT      = 0
, @DT_INI DATETIME = NULL
, @DT_FIM DATETIME = NULL
WITH ENCRYPTION
AS
		 BEGIN
			 SET NOCOUNT ON;
			 BEGIN
				 DECLARE @FLAG_MESES_EXPIRA_PONTOS_SEM_COMPRA INT;
				 DECLARE @FLAG_MESES_EXPIRA_PONTOS_POR_TEMPO INT;
				 DECLARE @DT_EXPIRA_COMPRA DATETIME;
				 DECLARE @DT_ULT_CP DATETIME;
	  
      -- SET NOCOUNT ON added to prevent extra result sets from   
      -- interfering with SELECT statements.   
				 SET NOCOUNT ON;
				 IF @DT_INI IS NULL
					 BEGIN
						 SET @DT_INI = '1900-01-01';
					 END;
				 IF @DT_FIM IS NULL
					 BEGIN
						 SET @DT_FIM = CONVERT(VARCHAR(10), GETDATE(), 101);
					 END;
				 SET @DT_FIM = @DT_FIM+' 23:59:59';
				 SET @FLAG_MESES_EXPIRA_PONTOS_SEM_COMPRA =
(
	SELECT
		   ABS(DBO.UFD_BUSCA_PARAM_SISTEMA
		   (1, 'FLAG_MESES_EXPIRA_PONTOS_SEM_COMPRA', 0, 0
		   ))
);
				 SET @FLAG_MESES_EXPIRA_PONTOS_POR_TEMPO =
(
	SELECT
		   ABS(DBO.UFD_BUSCA_PARAM_SISTEMA
		   (1, 'FLAG_MESES_EXPIRA_PONTOS_POR_TEMPO', 0, 0
		   ))
);
				 IF @FLAG_MESES_EXPIRA_PONTOS_SEM_COMPRA > 0
					 BEGIN
				--Verificando o tipo de expiracao prevista para os pontos. Expiracao por tempo sem compra
						 SET @DT_ULT_CP = ISNULL(
(
	SELECT
		   MAX(CRED.DT_CAD)
	FROM
		RC_CLI_FIDELI_PONTOS_CREDITO CRED
		INNER JOIN RC_MOTIVO_PONTO ON CRED.CD_MT_PONTO = RC_MOTIVO_PONTO.CD_MT_PONTO
	WHERE  CRED.CD_EMP = 1
		   AND CRED.CD_CLI = @CD_CLI
		   AND RC_MOTIVO_PONTO.CD_ACAO_MT_PONTO = 0
), DATEADD(MONTH, @FLAG_MESES_EXPIRA_PONTOS_SEM_COMPRA * -1, GETDATE()));
					 END;
				 SET @DT_EXPIRA_COMPRA = DATEADD(MONTH, @FLAG_MESES_EXPIRA_PONTOS_SEM_COMPRA, @DT_ULT_CP);							
			
      -- Insert statements for procedure here   
				 SELECT
						ISNULL(G.NR_ECF, 0) AS NR_ECF
					  , '' AS NR_AUTORIZADOR
					  , ISNULL(G.CD_CX, 0) AS CD_CX
					  , ISNULL(C.NM_FANT, 'COORDENACAO FIDELIDADE') AS NM_FANT
					  , A.DT_CAD
					  , ISNULL(G.VLR_LIQ_VD, 0) AS VRL_COMPRA
					  , ISNULL(SUM(E.TOT_IT), 0) AS TOT_IT
					  , ROUND(ISNULL(A.QTDE_PONTOS, 0), 2) AS QTDE_PONTOS
					  , UPPER(D.DS_MT_PONTO) AS DS_MT_PONTO
					  , 'C' AS TIPO
					  , ISNULL(A.CD_EMP, 0) AS CD_EMP
					  , ISNULL(A.CD_CLI, 0) AS CD_CLI
					  , ISNULL(A.CD_USU, 0) AS CD_USU
					  , ISNULL(A.CD_MT_PONTO, 0) AS CD_MT_PONTO
					  , ISNULL(D.CD_ACAO_MT_PONTO, 0) AS CD_ACAO_MT_PONTO
					  , ISNULL(B.CD_FILIAL, 0) AS CD_FILIAL
					  , ISNULL(F.DS_CID, '') AS DS_CID
					  , ISNULL(F.CD_CID, 0) AS CD_CID
					  , ISNULL(G.CD_VD, 0) AS CD_VD
					  , A.CD_CTR_PONTOS
					  , GU.NM_USU
					  , (CASE
							 WHEN DATEADD(MONTH, @FLAG_MESES_EXPIRA_PONTOS_POR_TEMPO, A.DT_CAD) < @DT_EXPIRA_COMPRA THEN DATEADD(MONTH, @FLAG_MESES_EXPIRA_PONTOS_POR_TEMPO, A.DT_CAD)
							 ELSE @DT_EXPIRA_COMPRA
						 END) AS DT_PREV_EXPIRA
				 FROM
					 RC_CLI_FIDELI_PONTOS_CREDITO AS A
					 LEFT JOIN RC_CLI_FIDELI_PONTOS_CREDITO_PDV_VD_IT_FIDELI_CARTAO AS B ON A.CD_EMP = B.CD_EMP
																							AND A.CD_CTR_PONTOS = B.CD_CTR_PONTOS
					 LEFT JOIN PRC_FILIAL AS C ON C.CD_FILIAL = B.CD_FILIAL
												  AND C.CD_EMP = B.CD_EMP
					 INNER JOIN RC_MOTIVO_PONTO AS D ON D.CD_MT_PONTO = A.CD_MT_PONTO
					 LEFT JOIN PDV_VD_IT AS E ON E.CD_EMP = B.CD_EMP
												 AND E.CD_FILIAL = B.CD_FILIAL
												 AND E.CD_VD = B.CD_VD
												 AND E.CD_IT = B.CD_IT
					 LEFT JOIN PDV_VD AS G ON E.CD_EMP = G.CD_EMP
											  AND E.CD_FILIAL = G.CD_FILIAL
											  AND E.CD_VD = G.CD_VD
											  --AND 0 = G.ST_VD
					 LEFT JOIN GLB_CID AS F ON F.CD_CID = C.CD_CID
					 INNER JOIN GLB_USU AS GU ON A.CD_USU = GU.CD_USU
				 WHERE   A.CD_CLI = @CD_CLI
						 AND A.DT_CAD BETWEEN @DT_INI AND @DT_FIM
						 AND E.ST_IT <> 2
				 GROUP BY
						  A.CD_EMP
						, A.CD_CLI
						, A.CD_CTR_PONTOS
						, A.DT_CAD
						, A.CD_USU
						, A.QTDE_PONTOS
						, A.CD_MT_PONTO
						, D.DS_MT_PONTO
						, D.CD_ACAO_MT_PONTO
						, B.CD_FILIAL
						, C.NM_FANT
						, F.DS_CID
						, F.CD_CID
						, G.CD_CX
						, G.CD_VD
						, G.NR_ECF
						, G.VLR_LIQ_VD
						, GU.NM_USU
				 UNION
				 SELECT
						ISNULL(G.NR_ECF, 0) AS NR_ECF
					  , '' AS NR_AUTORIZADOR
					  , ISNULL(G.CD_CX, 0) AS CD_CX
					  , ISNULL(C.NM_FANT, 'COORDENACAO FIDELIDADE') AS NM_FANT
					  , A.DT_CAD
					  , ISNULL(G.VLR_LIQ_VD, 0) AS VRL_COMPRA
					  , ISNULL(SUM(E.TOT_IT), 0) AS TOT_IT
					  , ROUND(ISNULL(A.QTDE_PONTOS, 0), 2) AS QTDE_PONTOS
					  , UPPER(D.DS_MT_PONTO) AS DS_MT_PONTO
					  , 'D' AS TIPO
					  , ISNULL(A.CD_EMP, 0) AS CD_EMP
					  , ISNULL(A.CD_CLI, 0) AS CD_CLI
					  , ISNULL(A.CD_USU, 0) AS CD_USU
					  , ISNULL(A.CD_MT_PONTO, 0) AS CD_MT_PONTO
					  , ISNULL(D.CD_ACAO_MT_PONTO, 0) AS CD_ACAO_MT_PONTO
					  , ISNULL(B.CD_FILIAL, 0) AS CD_FILIAL
					  , ISNULL(F.DS_CID, '') AS DS_CID
					  , ISNULL(F.CD_CID, 0) AS CD_CID
					  , ISNULL(G.CD_VD, 0) AS CD_VD
					  , A.CD_CTR_PONTOS
					  , GU.NM_USU AS NM_USU
					  , NULL AS DT_PREV_EXPIRA
				 FROM
					 RC_CLI_FIDELI_PONTOS_DEBITO AS A
					 INNER JOIN RC_CLI_FIDELI_PONTOS_DEBITO_PDV_VD_IT_FIDELI_CARTAO AS B ON A.CD_EMP = B.CD_EMP
																							AND A.CD_CTR_PONTOS = B.CD_CTR_PONTOS
					 INNER JOIN RC_MOTIVO_PONTO AS D ON A.CD_MT_PONTO = D.CD_MT_PONTO
					 INNER JOIN GLB_USU AS GU ON A.CD_USU = GU.CD_USU
					 LEFT JOIN PRC_FILIAL AS C ON C.CD_FILIAL = B.CD_FILIAL
												  AND C.CD_EMP = B.CD_EMP
					 LEFT JOIN PDV_VD_IT AS E ON E.CD_EMP = B.CD_EMP
												 AND E.CD_FILIAL = B.CD_FILIAL
												 AND E.CD_VD = B.CD_VD
												 AND E.CD_IT = B.CD_IT
					LEFT JOIN PDV_VD AS G ON E.CD_EMP = G.CD_EMP
												  AND E.CD_FILIAL = G.CD_FILIAL
												  AND E.CD_VD = G.CD_VD
												  --AND 0 = g.st_vd
					 LEFT JOIN GLB_CID AS F ON F.CD_CID = C.CD_CID
				 WHERE   A.CD_CLI = @CD_CLI
						 AND A.DT_CAD BETWEEN @DT_INI AND @DT_FIM
						 AND E.ST_IT <> 2
				 GROUP BY
						  A.CD_EMP
						, A.CD_CLI
						, A.CD_CTR_PONTOS
						, A.DT_CAD
						, A.QTDE_PONTOS
						, A.CD_USU
						, A.CD_MT_PONTO
						, D.DS_MT_PONTO
						, D.CD_ACAO_MT_PONTO
						, B.CD_FILIAL
						, C.NM_FANT
						, F.DS_CID
						, F.CD_CID
						, G.CD_CX
						, G.CD_VD
						, G.NR_ECF
						, G.VLR_LIQ_VD
						, GU.NM_USU
				 UNION
				 SELECT
						0 AS NR_ECF
					  , ISNULL(NR_AUTORIZADOR, '') COLLATE SQL_Latin1_General_CP1_CI_AS AS NR_AUTORIZADOR
					  , 0 AS CD_CX
					  , ISNULL(C.NM_FANT, 'COORDENACAO FIDELIDADE') AS NM_FANT
					  , A.DT_CAD
					  , ISNULL(I.VLR_VALE_CP, 0) AS VRL_COMPRA
					  , ISNULL(I.VLR_VALE_CP, 0) AS TOT_IT
					  , ROUND(ISNULL(A.QTDE_PONTOS, 0), 2) AS QTDE_PONTOS
					  , UPPER(D.DS_MT_PONTO) AS DS_MT_PONTO
					  , 'D' AS TIPO
					  , ISNULL(A.CD_EMP, 0) AS CD_EMP
					  , ISNULL(A.CD_CLI, 0) AS CD_CLI
					  , ISNULL(A.CD_USU, 0) AS CD_USU
					  , ISNULL(A.CD_MT_PONTO, 0) AS CD_MT_PONTO
					  , ISNULL(D.CD_ACAO_MT_PONTO, 0) AS CD_ACAO_MT_PONTO
					  , ISNULL(I.CD_FILIAL, 0) AS CD_FILIAL
					  , ISNULL(F.DS_CID, '') AS DS_CID
					  , ISNULL(F.CD_CID, 0) AS CD_CID
					  , 0 AS CD_VD
					  , A.CD_CTR_PONTOS
					  , GU.NM_USU AS NM_USU
					  , NULL AS DT_PREV_EXPIRA
				 FROM
					 RC_CLI_FIDELI_PONTOS_DEBITO AS A
					 INNER JOIN RC_CLI_FIDELI_PONTOS_DEBITO_RC_CLI_FIDELI_VALE_COMPRA AS H ON A.CD_CTR_PONTOS = H.CD_CTR_PONTOS
					 INNER JOIN RC_MOTIVO_PONTO AS D ON A.CD_MT_PONTO = D.CD_MT_PONTO
					 INNER JOIN GLB_USU AS GU ON A.CD_USU = GU.CD_USU
					 LEFT JOIN RC_CLI_FIDELI_VALE_COMPRA AS I ON I.CD_CTR_VALE_CP = H.CD_CTR_VALE_CP
					 LEFT JOIN PRC_FILIAL AS C ON C.CD_FILIAL = I.CD_FILIAL
												  AND C.CD_EMP = I.CD_EMP
					 LEFT JOIN GLB_CID AS F ON F.CD_CID = C.CD_CID
				 WHERE   A.CD_CLI = @CD_CLI
						 AND A.DT_CAD BETWEEN @DT_INI AND @DT_FIM
				 GROUP BY
						  A.CD_EMP
						, A.CD_CLI
						, A.CD_CTR_PONTOS
						, A.DT_CAD
						, A.QTDE_PONTOS
						, A.CD_USU
						, A.CD_MT_PONTO
						, D.DS_MT_PONTO
						, D.CD_ACAO_MT_PONTO
						, I.CD_FILIAL
						, C.NM_FANT
						, F.DS_CID
						, F.CD_CID
						, NR_AUTORIZADOR
						, GU.NM_USU
						, I.VLR_VALE_CP
				 UNION
				 SELECT
						0 AS NR_ECF
					  , ISNULL(I.CD_TRANS_MULT, '') COLLATE SQL_Latin1_General_CP1_CI_AS AS NR_AUTORIZADOR
					  , 0 AS CD_CX
					  , 'COORDENACAO FIDELIDADE' AS NM_FANT
					  , A.DT_CAD
					  , 0 AS VRL_COMPRA
					  , 0 AS TOT_IT
					  , ROUND(ISNULL(A.QTDE_PONTOS, 0), 2) AS QTDE_PONTOS
					  , UPPER(D.DS_MT_PONTO) AS DS_MT_PONTO
					  , 'D' AS TIPO
					  , ISNULL(A.CD_EMP, 0) AS CD_EMP
					  , ISNULL(A.CD_CLI, 0) AS CD_CLI
					  , ISNULL(A.CD_USU, 0) AS CD_USU
					  , ISNULL(A.CD_MT_PONTO, 0) AS CD_MT_PONTO
					  , ISNULL(D.CD_ACAO_MT_PONTO, 0) AS CD_ACAO_MT_PONTO
					  , 0 AS CD_FILIAL
					  , '' AS DS_CID
					  , 0 AS CD_CID
					  , 0 AS CD_VD
					  , A.CD_CTR_PONTOS
					  , GU.NM_USU AS NM_USU
					  , NULL AS DT_PREV_EXPIRA
				 FROM
					 RC_CLI_FIDELI_PONTOS_DEBITO AS A
					 INNER JOIN RC_CLI_FIDELI_PONTOS_DEBITO_RC_CLI_FIDELI_CARTAO_OPERADORA_TRANS AS H ON A.CD_CTR_PONTOS = H.CD_CTR_PONTOS
					 INNER JOIN RC_MOTIVO_PONTO AS D ON A.CD_MT_PONTO = D.CD_MT_PONTO
					 INNER JOIN GLB_USU AS GU ON A.CD_USU = GU.CD_USU
					 INNER JOIN RC_CLI_FIDELI_CARTAO_MULTIPLUS_TRANS AS I ON H.CD_TRANS = I.CD_TRANS
				 WHERE   A.CD_CLI = @CD_CLI
						 AND A.DT_CAD BETWEEN @DT_INI AND @DT_FIM
				 GROUP BY
						  A.CD_EMP
						, A.CD_CLI
						, A.CD_CTR_PONTOS
						, A.DT_CAD
						, A.QTDE_PONTOS
						, A.CD_USU
						, A.CD_MT_PONTO
						, D.DS_MT_PONTO
						, D.CD_ACAO_MT_PONTO
						, GU.NM_USU
						, I.CD_TRANS_MULT
				 UNION
				 SELECT
						0 AS NR_ECF
					  , '' AS NR_AUTORIZADOR
					  , 0 AS CD_CX
					  , 'COORDENACAO FIDELIDADE' AS NM_FANT
					  , A.DT_CAD
					  , 0 AS VRL_COMPRA
					  , 0 AS TOT_IT
					  , ROUND(ISNULL(A.QTDE_PONTOS, 0), 2) AS QTDE_PONTOS
					  , UPPER(D.DS_MT_PONTO) AS DS_MT_PONTO
					  , 'D' AS TIPO
					  , ISNULL(A.CD_EMP, 0) AS CD_EMP
					  , ISNULL(A.CD_CLI, 0) AS CD_CLI
					  , ISNULL(A.CD_USU, 0) AS CD_USU
					  , ISNULL(A.CD_MT_PONTO, 0) AS CD_MT_PONTO
					  , ISNULL(D.CD_ACAO_MT_PONTO, 0) AS CD_ACAO_MT_PONTO
					  , 0 AS CD_FILIAL
					  , '' AS DS_CID
					  , 0 AS CD_CID
					  , 0 AS CD_VD
					  , A.CD_CTR_PONTOS
					  , GU.NM_USU AS NM_USU
					  , NULL AS DT_PREV_EXPIRA
				 FROM
					 RC_CLI_FIDELI_PONTOS_DEBITO AS A
					 LEFT JOIN RC_CLI_FIDELI_PONTOS_DEBITO_PDV_VD_IT_FIDELI_CARTAO AS B ON A.CD_EMP = B.CD_EMP
																						   AND A.CD_CTR_PONTOS = B.CD_CTR_PONTOS
					 LEFT JOIN RC_CLI_FIDELI_PONTOS_DEBITO_RC_CLI_FIDELI_CARTAO_OPERADORA_TRANS AS H ON A.CD_CTR_PONTOS = H.CD_CTR_PONTOS
					 LEFT JOIN RC_CLI_FIDELI_PONTOS_DEBITO_RC_CLI_FIDELI_VALE_COMPRA AS H1 ON A.CD_CTR_PONTOS = H1.CD_CTR_PONTOS
					 INNER JOIN RC_MOTIVO_PONTO AS D ON A.CD_MT_PONTO = D.CD_MT_PONTO
					 INNER JOIN GLB_USU AS GU ON A.CD_USU = GU.CD_USU
					 LEFT OUTER JOIN RC_CLI_FIDELI_PONTOS_DEBITO_PDV_VD_IT AS CANCEL ON 
					 A.CD_EMP = CANCEL.CD_EMP AND 
					 A.CD_CTR_PONTOS = CANCEL.CD_CTR_PONTOS
				 WHERE  A.CD_CLI = @CD_CLI
						AND A.DT_CAD BETWEEN @DT_INI AND @DT_FIM
						AND B.CD_CTR_PONTOS IS NULL
						AND H.CD_CTR_PONTOS IS NULL
						AND H1.CD_CTR_PONTOS IS NULL
						AND CANCEL.CD_VD IS NULL
				 GROUP BY
						  A.CD_EMP
						, A.CD_CLI
						, A.CD_CTR_PONTOS
						, A.DT_CAD
						, A.QTDE_PONTOS
						, A.CD_USU
						, A.CD_MT_PONTO
						, D.DS_MT_PONTO
						, D.CD_ACAO_MT_PONTO
						, GU.NM_USU
				UNION 
				 SELECT
					 PDV_VD.NR_ECF
					  , '' AS NR_AUTORIZADOR
					  , PDV_VD.CD_CX
					  , C.NM_FANT
					  , A.DT_CAD
					  , 0 AS VRL_COMPRA
					  , 0 AS TOT_IT
					  , ROUND(ISNULL(A.QTDE_PONTOS, 0), 2) AS QTDE_PONTOS
					  , UPPER(D.DS_MT_PONTO) AS DS_MT_PONTO
					  , 'D' AS TIPO
					  , ISNULL(A.CD_EMP, 0) AS CD_EMP
					  , ISNULL(A.CD_CLI, 0) AS CD_CLI
					  , ISNULL(A.CD_USU, 0) AS CD_USU
					  , ISNULL(A.CD_MT_PONTO, 0) AS CD_MT_PONTO
					  , ISNULL(D.CD_ACAO_MT_PONTO, 0) AS CD_ACAO_MT_PONTO
					  , PDV_VD.CD_FILIAL
					  , F.DS_CID
					  , C.CD_CID
					  , PDV_VD.CD_VD
					  , A.CD_CTR_PONTOS
					  , GU.NM_USU AS NM_USU
					  , NULL AS DT_PREV_EXPIRA
				 FROM
					 RC_CLI_FIDELI_PONTOS_DEBITO AS A INNER JOIN RC_CLI_FIDELI_PONTOS_DEBITO_PDV_VD_IT AS B ON 
					 A.CD_EMP = B.CD_EMP AND 
					 A.CD_CTR_PONTOS = B.CD_CTR_PONTOS
					 INNER JOIN PDV_VD ON
					 B.CD_EMP = PDV_VD.CD_EMP AND
					 B.CD_FILIAL = PDV_VD.CD_FILIAL AND  
					 B.CD_VD = PDV_VD.CD_VD 
					 INNER JOIN RC_MOTIVO_PONTO AS D ON 
					 D.CD_MT_PONTO = A.CD_MT_PONTO
					 INNER JOIN PRC_FILIAL AS C ON 
					 C.CD_FILIAL = B.CD_FILIAL AND
					 C.CD_EMP = B.CD_EMP
					 INNER JOIN GLB_CID AS F ON F.CD_CID = C.CD_CID
					  INNER JOIN GLB_USU AS GU ON A.CD_USU = GU.CD_USU
				 WHERE  A.CD_CLI = @CD_CLI
						AND A.DT_CAD BETWEEN @DT_INI AND @DT_FIM
				 GROUP BY
						  A.CD_EMP
						, A.CD_CLI
						, A.CD_CTR_PONTOS
						, A.DT_CAD
						, A.QTDE_PONTOS
						, A.CD_USU
						, A.CD_MT_PONTO
						, D.DS_MT_PONTO
						, D.CD_ACAO_MT_PONTO
						, GU.NM_USU
						,PDV_VD.NR_ECF
						,PDV_VD.CD_CX
						,C.NM_FANT
						,PDV_VD.CD_FILIAL
						,F.DS_CID
						,C.CD_CID
						,PDV_VD.CD_VD
				 ORDER BY
						  DT_CAD ASC,
						  TIPO;
			 END;
		 END;