IF EXISTS
(
    SELECT
		 *
    FROM SYS.VIEWS
    WHERE OBJECT_ID = OBJECT_ID(N'[V_WEB_CONV_USU]')
)
DROP VIEW
	[V_WEB_CONV_USU];
GO
CREATE VIEW [dbo].[V_WEB_CONV_USU]
AS
	SELECT
		  B.CD_USU AS CODE
		, B.ATIVO
		, B.PR_ACESSO
		, A.NM_USU
		, A.SENHA
		, 0 AS TP_USU
		, B.DT_CAD
		, B.CD_USU_CONV
		, '0' AS NR_CARTAO
		, 0 AS CD_CONV
		, 0 AS NR_CONTROLE_CARTAO
	FROM
		dbo.GLB_USU AS A
		INNER JOIN dbo.WEB_CONV_USU_GLB_USU AS B
		ON A.CD_USU = B.CD_USU
	UNION
	SELECT
		  c.CD_CLI  AS CODE
		, A.ATIVO
		, A.PR_ACESSO
		, C.RZ_CLI AS NM_USU
		, C.SENHA
		, A.TP_USU
		, A.DT_CAD
		, A.CD_USU_CONV
		, D.NR_CARTAO  AS NR_CARTAO
		, CV.CD_CONV
		, ISNULL(D.NR_CONTROLE_CARTAO, 0) AS NR_CONTROLE_CARTAO
	FROM
		dbo.WEB_CONV_USU_RC_CLI_CONV_RC_CLI AS A

		  LEFT JOIN dbo.RC_CLI_FIDELI_CARTAO AS D
		ON a.CD_EMP = D.CD_EMP
		   AND a.CD_CLI = D.CD_CLI

		INNER JOIN dbo.RC_CLI AS C
		ON A.CD_EMP = C.CD_EMP
		   AND A.CD_CLI = C.CD_CLI
		INNER JOIN dbo.RC_CLI_CONV_RC_CLI AS CV
		ON C.CD_EMP = CV.CD_EMP
		   AND C.CD_CLI = CV.CD_CLI
		   AND CV.STS_ATIVO = 0
	
	WHERE C.cd_emp = 1  

