IF EXISTS(SELECT * FROM SYS.VIEWS
			WHERE  OBJECT_ID = OBJECT_ID(N'[V_NCC_CREDITO]'))
DROP VIEW [V_NCC_CREDITO];
GO

CREATE VIEW [V_NCC_CREDITO]
WITH ENCRYPTION
AS
	 SELECT
			NCC.CD_EMP
		  , NCC.CD_FILIAL
		  , NAO_FISCAL.CD_CX
		  , NAO_FISCAL.DT_CUPOM
		  , NAO_FISCAL.NR_COO
		  , NAO_FISCAL.VLR_TOT_CUPOM AS TOT_CUPOM
		  , DEB.CD_CLI
		  , DEB.NR_DP AS DP_TENTATIVA_BAIXA
		  , DEB.PARC
		  , DEB.ANO
		  , DEB.VLR_DP
		  , DEB.CD_FILIAL AS FILIAL_DP
		  , CASE
				WHEN DEB.STS_DP = 0 THEN 'EM ABERTO'
				WHEN DEB.STS_DP = 1 THEN 'FECHADA'
				WHEN DEB.STS_DP = 2 THEN 'QUITADA PARCIALMENTE'
				WHEN DEB.STS_DP = 3 THEN 'CANCELADA'
				WHEN DEB.STS_DP = 4 THEN 'RENEGOCIADA'
				WHEN DEB.STS_DP = 5 THEN 'AGD.RETORNO BANCO'
				ELSE 'DUPLICATA DE CRÉDITO ESTORNADO'
			END AS STATUS_DP
		  , NCC.VLR_NCC
		  , NCC.STS_NCC
		  , V_PDV_VD_DEV_NCC_STATUS.DS_STS_NCC AS SITUACAO
		  , NCC.DT_CAD AS DT_GERA_NCC
		  , NCC.CD_CTR_NCC
		  , DEB.DT_VENCTO
		  , 1 AS QTDE
		  , DT_CAD_REGISTRO
		  , RC_CLI.RZ_CLI
		  , NAO_FISCAL.HR_CUPOM
		  , NAO_FISCAL.CD_USU
		  , 0 AS TIPO_TENTATIVA_BAIXA
	
	 FROM
		 RC_PGTO_PDV PGTO
		 INNER JOIN RC_DEB_PDV_VD_DEV_NCC DEV_NCC ON PGTO.CD_CTR_PGTO = DEV_NCC.CD_CTR_PGTO
		 LEFT JOIN RC_CREDIARIO_PARC_PDV_VD_DEV_NCC PARC_NCC ON PARC_NCC.CD_CTR_NCC = DEV_NCC.CD_CTR_NCC
																AND PARC_NCC.CD_CTR_PGTO = DEV_NCC.CD_CTR_PGTO
		 INNER JOIN PDV_VD_DEV_NCC NCC ON NCC.CD_EMP = DEV_NCC.CD_EMP
										  AND NCC.CD_CTR_NCC = DEV_NCC.CD_CTR_NCC
		 INNER JOIN V_PDV_VD_DEV_NCC_STATUS ON NCC.STS_NCC = V_PDV_VD_DEV_NCC_STATUS.CD_STS_NCC
		 INNER JOIN RC_DEB DEB ON DEB.CD_EMP = DEV_NCC.CD_EMP
								  AND DEB.CD_FILIAL = DEV_NCC.CD_FILIAL
								  AND DEB.CD_RC_DEB = DEV_NCC.CD_RC_DEB
		 INNER JOIN PDV_CUPOM_NAO_FISCAL_RC_PGTO_PDV CUPOM_PGTO ON CUPOM_PGTO.CD_EMP = DEV_NCC.CD_EMP
																   AND CUPOM_PGTO.CD_CTR_PGTO = DEV_NCC.CD_CTR_PGTO
		 INNER JOIN PDV_CUPOM_NAO_FISCAL NAO_FISCAL ON NAO_FISCAL.CD_EMP = CUPOM_PGTO.CD_EMP
													   AND NAO_FISCAL.CD_FILIAL = CUPOM_PGTO.CD_FILIAL
													   AND NAO_FISCAL.CD_CTR = CUPOM_PGTO.CD_CTR
		 INNER JOIN RC_CLI ON DEB.CD_EMP = RC_CLI.CD_EMP
							  AND DEB.CD_CLI = RC_CLI.CD_CLI
	 WHERE   NCC.TIPO_NCC = 8
	 UNION ALL
	 SELECT DISTINCT
			NCC.CD_EMP
		  , NCC.CD_FILIAL
		  , NAO_FISCAL.CD_CX
		  , NAO_FISCAL.DT_CUPOM
		  , NAO_FISCAL.NR_COO
		  , NAO_FISCAL.VLR_TOT_CUPOM AS TOT_CUPOM
		  , NCC.CD_CLI
		  , PARC_CRED.CD_CTR AS DP_TENTATIVA_BAIXA
		  , PARC_CRED.PARC_CRED AS PARC
		  , YEAR(PARC_CRED.DT_LANC_CRED) AS ANO
		  , PARC_CRED.VLR_PARC_CRED AS VLR_DP
		  , PARC_CRED.CD_FILIAL AS FILIAL_DP
		  , ISNULL(V_RC_CREDIARIO_PARC_STATUS.STS_LANC_CRED_DS, 'NAO DEFINIDO') AS STATUS_DP
		  , NCC.VLR_NCC
		  , NCC.STS_NCC
		  , V_PDV_VD_DEV_NCC_STATUS.DS_STS_NCC AS SITUACAO
		  , NCC.DT_CAD AS DT_GERA_NCC
		  , NCC.CD_CTR_NCC
		  , PARC_CRED.DT_FECHA_CRED AS DT_VENCTO
		  , 1 AS QTDE
		  , DT_CAD_REGISTRO
		  , RC_CLI.RZ_CLI
		  , NAO_FISCAL.HR_CUPOM
		  , NAO_FISCAL.CD_USU
		  , 1 AS TIPO_TENTATIVA_BAIXA
	 FROM
		 RC_PGTO_PDV PGTO
		 INNER JOIN RC_CREDIARIO_PARC_PDV_VD_DEV_NCC PARC_NCC ON PARC_NCC.CD_CTR_PGTO = PARC_NCC.CD_CTR_PGTO
		 INNER JOIN PDV_VD_DEV_NCC NCC ON NCC.CD_CTR_NCC = PARC_NCC.CD_CTR_NCC
		 INNER JOIN V_PDV_VD_DEV_NCC_STATUS ON NCC.STS_NCC = V_PDV_VD_DEV_NCC_STATUS.CD_STS_NCC
		 INNER JOIN RC_CREDIARIO_PARC PARC_CRED ON PARC_CRED.CD_CTR = PARC_NCC.CD_CTR
		 INNER JOIN PDV_CUPOM_NAO_FISCAL_RC_PGTO_PDV CUPOM_PGTO ON CUPOM_PGTO.CD_CTR_PGTO = PARC_NCC.CD_CTR_PGTO
		 INNER JOIN PDV_CUPOM_NAO_FISCAL NAO_FISCAL ON NAO_FISCAL.CD_EMP = CUPOM_PGTO.CD_EMP
													   AND NAO_FISCAL.CD_FILIAL = CUPOM_PGTO.CD_FILIAL
													   AND NAO_FISCAL.CD_CTR = CUPOM_PGTO.CD_CTR
		 INNER JOIN RC_CLI ON PARC_CRED.CD_EMP = RC_CLI.CD_EMP
							  AND PARC_CRED.CD_CLI = RC_CLI.CD_CLI
		 LEFT OUTER JOIN V_RC_CREDIARIO_PARC_STATUS ON PARC_CRED.STS_LANC_CRED = V_RC_CREDIARIO_PARC_STATUS.STS_LANC_CRED
	 WHERE  NCC.TIPO_NCC = 8;
