IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[V_CHQ_LANC_CPL_SITUACAO_STATUS]'))
DROP VIEW [dbo].[V_CHQ_LANC_CPL_SITUACAO_STATUS]
GO
--SELECT * FROM V_CHQ_LANC_CPL_SITUACAO_STATUS
CREATE VIEW V_CHQ_LANC_CPL_SITUACAO_STATUS AS
SELECT  
	DISTINCT  
	CHQ_LANC_CPL.CD_EMP,
	CHQ_LANC_CPL.CD_CHQ,
	ISNULL(CHQ_DEVO.CD_CHQ,0) DEVOLVIDO,  
	ISNULL(CHQ_DEPOS_CPL.CD_CHQ,0) DEPO,  
	ISNULL(CHQ_DEST_ENV_CPL.CD_CHQ,0)COBRANCA, 
	ISNULL(CHQ_LANC_RC_CRED.CD_CHQ_LANC,0) AS VINC_DUP, 
	ISNULL(CHQ_LANC_CPL.FLAG_RECBTO,0) AS FLAG_RECBTO,  
	CASE WHEN CHQ_REDEPOS.CD_REDEPOS > 0 THEN 
		1 
	ELSE 
		0 
	END AS REDEP,  
	CASE WHEN ISNULL(CHQ_DEST_ENV_CPL.VLR_RECB,0) >= CHQ_LANC_CPL.VLR_CHQ THEN 
		1  
	WHEN ISNULL(CHQ_DEST_ENV_CPL.VLR_RECB,0) < CHQ_LANC_CPL.VLR_CHQ AND CHQ_DEST_ENV_CPL.VLR_RECB  > 0 THEN 
		2 
	ELSE 
		0 
	END AS RECUPERADO, 
	ISNULL(V_CHQ_DEV_QT.QT_DEV,0)QT_DEV, 
	ISNULL(CHQ_LANC_CPL.COMP,0)COMP,
	CHQ_LANC.CD_FILIAL, 
	ISNULL(PDV_VD_CHQS_CHQ_LANC.CD_CHQ,0) AS CHQ_PDV,
	STS_CHQ,

	CASE
    WHEN STS_CHQ = 0 THEN
    'ABERTO'
    WHEN STS_CHQ=1 THEN
    'COBRANCA'
    WHEN STS_CHQ=2 THEN
    'CUSTODIA'
    WHEN STS_CHQ=3 THEN
	'DEVOLVIDO'
    WHEN STS_CHQ=5 THEN
    'COMPENSADO'
   WHEN STS_CHQ=6 THEN
    'DEPOSITADO'
    WHEN STS_CHQ=7 THEN
    'RECUPERADO(PARCIAL OU TOTAL)'
    WHEN STS_CHQ=9 THEN
    'REDEPOSITADO'
    WHEN STS_CHQ=10 THEN
    'DESCONTADO'
    END AS ULTIMA_SITUACAO

FROM  
	CHQ_LANC  INNER JOIN CHQ_LANC_CHQ_LANC_CPL ON  
	CHQ_LANC.CD_EMP = CHQ_LANC_CHQ_LANC_CPL.CD_EMP AND   
	CHQ_LANC.ANO = CHQ_LANC_CHQ_LANC_CPL.ANO AND    
	CHQ_LANC.CD_CHQ_LANC = CHQ_LANC_CHQ_LANC_CPL.CD_CHQ_LANC  
	INNER JOIN CHQ_LANC_CPL ON   
	CHQ_LANC_CHQ_LANC_CPL.CD_EMP = CHQ_LANC_CPL.CD_EMP AND   
	CHQ_LANC_CHQ_LANC_CPL.CD_CHQ = CHQ_LANC_CPL.CD_CHQ 
	LEFT JOIN CHQ_DEVO ON   
	CHQ_LANC_CHQ_LANC_CPL.CD_EMP = CHQ_DEVO.CD_EMP AND   
	CHQ_LANC_CHQ_LANC_CPL.CD_CHQ = CHQ_DEVO.CD_CHQ 
	LEFT JOIN BC_LANC_CHQ_DEST_ENV_CPL ON    
	CHQ_LANC_CHQ_LANC_CPL.CD_EMP = BC_LANC_CHQ_DEST_ENV_CPL.CD_EMP AND   
	CHQ_LANC_CHQ_LANC_CPL.CD_CHQ = BC_LANC_CHQ_DEST_ENV_CPL.CD_CHQ 
	LEFT JOIN BC_LANC ON    
	BC_LANC_CHQ_DEST_ENV_CPL.CD_EMP = BC_LANC.CD_EMP AND    
	BC_LANC_CHQ_DEST_ENV_CPL.CD_BC_LANC = BC_LANC.CD_BC_LANC 
	LEFT JOIN CHQ_REDEPOS ON    
	CHQ_LANC_CHQ_LANC_CPL.CD_EMP = CHQ_REDEPOS.CD_EMP AND   
	CHQ_LANC_CHQ_LANC_CPL.CD_CHQ = CHQ_REDEPOS.CD_CHQ 
	LEFT JOIN BC_LANC_CHQ_REDEPOS ON    
	CHQ_REDEPOS.CD_EMP = BC_LANC_CHQ_REDEPOS.CD_EMP AND    
	CHQ_REDEPOS.CD_REDEPOS = BC_LANC_CHQ_REDEPOS.CD_REDEPOS 
	LEFT JOIN BC_LANC BC_LANC_REDEPOS ON    
	BC_LANC_CHQ_REDEPOS.CD_EMP = BC_LANC_REDEPOS.CD_EMP AND    
	BC_LANC_CHQ_REDEPOS.CD_BC_LANC = BC_LANC_REDEPOS.CD_BC_LANC 
	LEFT JOIN CHQ_DEPOS_CPL ON   
	CHQ_LANC_CPL.CD_EMP = CHQ_DEPOS_CPL.CD_EMP AND     
	CHQ_LANC_CPL.CD_CHQ = CHQ_DEPOS_CPL.CD_CHQ  
	LEFT JOIN CHQ_DEST_ENV_CPL ON   
	CHQ_LANC_CPL.CD_EMP = CHQ_DEST_ENV_CPL.CD_EMP AND     
	CHQ_LANC_CPL.CD_CHQ = CHQ_DEST_ENV_CPL.CD_CHQ 
	LEFT JOIN V_CHQ_DEV_QT ON   
	CHQ_LANC_CPL.CD_EMP = V_CHQ_DEV_QT.CD_EMP AND     
	CHQ_LANC_CPL.CD_CHQ = V_CHQ_DEV_QT.CD_CHQ 
	LEFT JOIN PDV_VD_CHQS_CHQ_LANC ON    
	CHQ_LANC_CPL.CD_EMP = PDV_VD_CHQS_CHQ_LANC.CD_EMP AND   
	CHQ_LANC_CPL.CD_CHQ = PDV_VD_CHQS_CHQ_LANC.CD_CHQ  
	LEFT JOIN PDV_VD ON    
	PDV_VD_CHQS_CHQ_LANC.CD_EMP = PDV_VD.CD_EMP AND    
	PDV_VD_CHQS_CHQ_LANC.CD_FILIAL = PDV_VD.CD_FILIAL AND   
	PDV_VD_CHQS_CHQ_LANC.CD_VD = PDV_VD.CD_VD 
	LEFT JOIN CHQ_LANC_RC_CRED ON    
	CHQ_LANC.CD_EMP = CHQ_LANC_RC_CRED.CD_EMP AND   
	CHQ_LANC.ANO = CHQ_LANC_RC_CRED.ANO AND    
	CHQ_LANC.CD_CHQ_LANC = CHQ_LANC_RC_CRED.CD_CHQ_LANC  
 WHERE  
	CHQ_LANC.CD_EMP =1 
