IF EXISTS (SELECT 1 FROM SYS.VIEWS WHERE OBJECT_NAME(OBJECT_ID) = 'V_SITUACAO_CHEQUES') 
DROP VIEW V_SITUACAO_CHEQUES
GO
CREATE VIEW [DBO].[V_SITUACAO_CHEQUES] AS           
      
----- BUSCANDO CHEQUES SEM TRATAMENTO          
SELECT           
 A.CD_EMP,           
 A.CD_CHQ,          
 CASE WHEN A.DT_EMI > CHQ_DEPOS.DT_CAD THEN          
  DT_DEPOS           
 ELSE          
  A.DT_EMI           
 END AS DT_CHQ,      
 A.DT_EMI AS DT_CAD,    
 0 AS TP,
 'SEM TRATAMENTO' AS DS_TP          
FROM CHQ_LANC_CPL A LEFT JOIN CHQ_DEPOS_CPL B ON          
 A.CD_EMP=B.CD_EMP AND          
 A.ANO_CHQ =B.ANO AND           
 A.CD_CHQ = B.CD_CHQ           
        
 LEFT JOIN CHQ_DEPOS ON           
 B.CD_EMP = CHQ_DEPOS.CD_EMP AND           
 B.ANO = CHQ_DEPOS.ANO AND          
 B.CD_DEPOS = CHQ_DEPOS.CD_DEPOS           
       
       
UNION ALL             
---------BUSCANDO ENVIO PARA COBRAN?A          
SELECT           
 CHQ_DEST_ENV_CPL.CD_EMP,          
 CHQ_DEST_ENV_CPL.CD_CHQ,           
 CHQ_DEST_ENV.DT_ENV AS DT_CHQ,         
 CHQ_DEST_ENV.DT_CAD,     
 1 AS TP,
 'ENVIADO PARA COBRANCA' AS DS_TP                    
FROM CHQ_DEST_ENV_CPL, CHQ_DEST_ENV, CHQ_DEST           
WHERE CHQ_DEST_ENV_CPL.CD_EMP = CHQ_DEST_ENV.CD_EMP AND          
 CHQ_DEST_ENV_CPL.ANO_ENV = CHQ_DEST_ENV.ANO_ENV AND          
 CHQ_DEST_ENV_CPL.CD_ENV = CHQ_DEST_ENV.CD_ENV AND          
 CHQ_DEST_ENV.CD_EMP = CHQ_DEST.CD_EMP AND           
 CHQ_DEST_ENV.CD_DEST = CHQ_DEST.CD_DEST           
      
UNION ALL          
-------BUSCANDO LAN?AMENTO DE CUST?DIA          
SELECT           
 CHQ_LANC_CUSTODIA.CD_EMP,           
 CHQ_LANC_CUSTODIA_CPL.CD_CHQ,          
 CHQ_LANC_CUSTODIA.DT_CUSTODIA AS DT_CHQ,          
 CHQ_LANC_CUSTODIA.DT_CAD,     
 2 AS TP,        
 'EM CUSTODIA' AS DS_TP          
FROM CHQ_LANC_CUSTODIA_CPL INNER JOIN CHQ_LANC_CUSTODIA ON           
 CHQ_LANC_CUSTODIA_CPL.CD_EMP = CHQ_LANC_CUSTODIA.CD_EMP AND          
 CHQ_LANC_CUSTODIA_CPL.ANO = CHQ_LANC_CUSTODIA.ANO AND          
 CHQ_LANC_CUSTODIA_CPL.CD_CUSTODIA = CHQ_LANC_CUSTODIA.CD_CUSTODIA           
      
 INNER JOIN BC_LANC_CHQ_LANC_CUSTODIA ON           
 CHQ_LANC_CUSTODIA.CD_EMP = BC_LANC_CHQ_LANC_CUSTODIA.CD_EMP AND          
 CHQ_LANC_CUSTODIA.ANO = BC_LANC_CHQ_LANC_CUSTODIA.ANO AND          
 CHQ_LANC_CUSTODIA.CD_CUSTODIA = BC_LANC_CHQ_LANC_CUSTODIA.CD_CUSTODIA           
      
 INNER JOIN BC_LANC ON          
 BC_LANC_CHQ_LANC_CUSTODIA.CD_EMP = BC_LANC.CD_EMP AND          
 BC_LANC_CHQ_LANC_CUSTODIA.CD_BC_LANC = BC_LANC.CD_BC_LANC           
      
 INNER JOIN BC_CONTAS ON          
 BC_LANC.CD_EMP = BC_CONTAS.CD_EMP AND BC_LANC.CD_CNT = BC_CONTAS.CD_CNT          
              
WHERE BC_LANC_CHQ_LANC_CUSTODIA.TP_LANC = 0          
 AND BC_LANC.TP_LANC = 0           
      
UNION ALL            
      
-------------BUSCANDO DEVOLU??O SE HOUVER          
SELECT          
 CD_EMP,            
 CD_CHQ,           
 DT_DEVO AS DT_CHQ,      
 DT_CAD,     
 3 AS TP,         
 'DEVOLVIDO' AS DS_TP          
FROM 
	V_CHQ_DEPOS_CHQ_REDEPOS          
      
UNION ALL          
      
-----BUSCANDO DEP?SITO          
SELECT           
 CHQ_DEPOS_CPL.CD_EMP,          
 CHQ_DEPOS_CPL.CD_CHQ,          
 CHQ_DEPOS.DT_DEPOS AS DT_CHQ,          
 CHQ_DEPOS.DT_CAD,    
 6 AS TP,
  'DEPOSITADO' AS DS_TP          

FROM CHQ_DEPOS_CPL INNER JOIN CHQ_DEPOS ON        
 CHQ_DEPOS_CPL.CD_EMP = CHQ_DEPOS.CD_EMP AND        
 CHQ_DEPOS_CPL.ANO = CHQ_DEPOS.ANO AND       
 CHQ_DEPOS_CPL.CD_DEPOS = CHQ_DEPOS.CD_DEPOS        
 INNER JOIN BC_CONTAS ON        
 CHQ_DEPOS.CD_EMP = BC_CONTAS.CD_EMP AND        
 CHQ_DEPOS.CD_CNT = BC_CONTAS.CD_CNT       
      
UNION ALL          
       
----- RECUPERADO          
SELECT           
 BC_LANC.CD_EMP,          
 BC_LANC_CHQ_DEST_ENV_CPL.CD_CHQ,          
 BC_LANC.DT_LANCTO AS DT_CHQ,      
 BC_LANC.DT_CAD,         
 7 AS TP,
 'RECUPERADO' AS DS_TP          
FROM  BC_LANC, BC_LANC_CHQ_DEST_ENV_CPL           
 WHERE BC_LANC.CD_EMP = BC_LANC_CHQ_DEST_ENV_CPL.CD_EMP AND           
 BC_LANC.CD_BC_LANC = BC_LANC_CHQ_DEST_ENV_CPL.CD_BC_LANC          
      
UNION ALL          
      
----- BUSCANCO CHEQUES REDEPOSITADOS          
SELECT           
 CD_EMP,           
 CD_CHQ,          
 DT_REDEPOS AS DT_CHQ,          
 CHQ_REDEPOS.DT_CAD,    
 9 AS TP,
'REDEPOSITADO' AS DS_TP          
FROM CHQ_REDEPOS           
      
UNION ALL        
      
----BUSCANDO CHEQUES DESCONTADOS          
SELECT            
 CHQ_DESC_CPL.CD_EMP,          
 CHQ_DESC_CPL.CD_CHQ,           
 CHQ_DESC.DT_CAD  AS DT_CHQ,     
 CHQ_DESC.DT_CAD,          
 10 AS TP,          
 'DESCONTADO' AS DS_TP                
FROM  
	CHQ_DEST, 
	CHQ_DESC_CPL,
	CHQ_DESC                
 WHERE 
	CHQ_DEST.CD_EMP = CHQ_DESC.CD_EMP        
	AND CHQ_DEST.CD_DEST = CHQ_DESC.CD_DEST           
	AND CHQ_DESC.CD_EMP = CHQ_DESC_CPL.CD_EMP           
	AND CHQ_DESC.CD_DESC = CHQ_DESC_CPL.CD_DESC       