/****** Object:  View [dbo].[V_CHQ_CUSTODIA_RET]    Script Date: 02/06/2014 15:00:00 ******/
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[V_CHQ_CUSTODIA_RET]'))
DROP VIEW [dbo].[V_CHQ_CUSTODIA_RET]
GO

/****** Object:  View [dbo].[V_CHQ_CUSTODIA_RET]    Script Date: 02/06/2014 15:00:03 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[V_CHQ_CUSTODIA_RET]
AS

Select  CHQ_LANC_CPL.CD_CHQ, 
	CHQ_LANC_CPL.COMP,  
	CHQ_LANC_CPL.NR_CHQ, 
	CHQ_LANC_CPL.SR_CHQ,  
	CHQ_LANC_CPL.ANO_CHQ, 
	CHQ_LANC_CPL.Cd_Bc_Chq ,  
	CHQ_LANC_CPL.AG_CHQ , 
	CHQ_LANC_CPL.VLR_CHQ,  
	CHQ_EMI.NM_EMI,
	DT_VENCTO,
	DT_EMI,
	CHQ_LANC.CD_FILIAL,
    ISNULL(CHQ_LANC_CUSTODIA_REM.CD_CTR,0)CD_CTR,
	CHQ_LANC_CUSTODIA.ANO ANO_CUST, 
	CHQ_LANC_CUSTODIA.CD_CUSTODIA,
    CHQ_LANC_CUSTODIA.CD_EMP, 
    ISNULL(CD_DEVO,0)CD_DEVO,
    ISNULL(CD_DEPOS,0)CD_DEPOS, 
    CHQ_LANC_CUSTODIA.CD_CNT_ORIG, 
    BC_LANC.TP_LANC ,
    CHQ_LANC_CUSTODIA.CD_CNT_CUST CD_CNT, 
    CMC7,
    ISNULL(CHQ_DEST_ENV_CPL.CD_ENV,0)AS CD_ENV,
    0 AS COBRANCA 
   
From   BC_LANC_CHQ_LANC_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 CHQ_LANC_CUSTODIA  
INNER JOIN CHQ_LANC_CUSTODIA_CPL ON  
	CHQ_LANC_CUSTODIA.CD_EMP = CHQ_LANC_CUSTODIA_CPL.CD_EMP   
	AND CHQ_LANC_CUSTODIA.ANO = CHQ_LANC_CUSTODIA_CPL.ANO 
	AND   CHQ_LANC_CUSTODIA.CD_CUSTODIA = CHQ_LANC_CUSTODIA_CPL.CD_CUSTODIA  
INNER JOIN  CHQ_LANC_CPL ON    
	CHQ_LANC_CUSTODIA_CPL.CD_EMP = CHQ_LANC_CPL.CD_EMP    
	AND CHQ_LANC_CUSTODIA_CPL.CD_CHQ = CHQ_LANC_CPL.CD_CHQ  
INNER JOIN CHQ_EMI ON    
	CHQ_LANC_CPL.CD_EMP = CHQ_EMI.CD_EMP    
	AND CHQ_LANC_CPL.CD_EMI = CHQ_EMI.CD_EMI  
INNER JOIN CHQ_LANC_CHQ_LANC_CPL ON    
	CHQ_LANC_CPL.CD_EMP = CHQ_LANC_CHQ_LANC_CPL.CD_EMP   
	AND CHQ_LANC_CPL.CD_CHQ = CHQ_LANC_CHQ_LANC_CPL.CD_CHQ  
INNER JOIN CHQ_LANC ON    
	CHQ_LANC_CHQ_LANC_CPL.CD_EMP = CHQ_LANC.CD_EMP    
	AND CHQ_LANC_CHQ_LANC_CPL.ANO = CHQ_LANC.ANO   
	AND CHQ_LANC_CHQ_LANC_CPL.CD_CHQ_LANC = CHQ_LANC.CD_CHQ_LANC ON   
	BC_LANC_CHQ_LANC_CUSTODIA.CD_EMP = CHQ_LANC_CUSTODIA.CD_EMP    
	AND BC_LANC_CHQ_LANC_CUSTODIA.ANO = CHQ_LANC_CUSTODIA.ANO    
	AND BC_LANC_CHQ_LANC_CUSTODIA.CD_CUSTODIA = CHQ_LANC_CUSTODIA.CD_CUSTODIA 
LEFT OUTER JOIN  CHQ_LANC_CUSTODIA_REM ON   
	CHQ_LANC_CUSTODIA.CD_EMP = CHQ_LANC_CUSTODIA_REM.CD_EMP   
	AND CHQ_LANC_CUSTODIA.ANO = CHQ_LANC_CUSTODIA_REM.ANO    
	AND CHQ_LANC_CUSTODIA.CD_CUSTODIA = CHQ_LANC_CUSTODIA_REM.CD_CUSTODIA  
LEFT OUTER JOIN CHQ_DEVO ON    
	CHQ_LANC_CPL.CD_EMP = CHQ_DEVO.CD_EMP    
	AND CHQ_LANC_CPL.CD_CHQ = CHQ_DEVO.CD_CHQ    
LEFT OUTER 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

UNION ALL

Select  CHQ_LANC_CPL.CD_CHQ, 
	CHQ_LANC_CPL.COMP,  
	CHQ_LANC_CPL.NR_CHQ, 
	CHQ_LANC_CPL.SR_CHQ,  
	CHQ_LANC_CPL.ANO_CHQ, 
	CHQ_LANC_CPL.Cd_Bc_Chq ,  
	CHQ_LANC_CPL.AG_CHQ , 
	CHQ_LANC_CPL.VLR_CHQ,  
	CHQ_EMI.NM_EMI,
	DT_VENCTO,
	DT_EMI,
	CHQ_LANC.CD_FILIAL,
    ISNULL(CHQ_LANC_CUSTODIA_REM.CD_CTR,0)CD_CTR,
	CHQ_LANC_CUSTODIA.ANO ANO_CUST, 
	CHQ_LANC_CUSTODIA.CD_CUSTODIA,
    CHQ_LANC_CUSTODIA.CD_EMP, 
    ISNULL(CD_DEVO,0)CD_DEVO,
    ISNULL(CD_DEPOS,0)CD_DEPOS, 
    CHQ_LANC_CUSTODIA.CD_CNT_ORIG, 
    BC_LANC.TP_LANC ,
    CHQ_LANC_CUSTODIA.CD_CNT_CUST CD_CNT, 
    CMC7,
    ISNULL(CHQ_DEST_ENV_CPL.CD_ENV,0)AS CD_ENV,
    1 AS COBRANCA 
    
FROM         
	CHQ_DEST_ENV_CPL RIGHT OUTER JOIN
	CHQ_DEVO RIGHT OUTER JOIN
	CHQ_EMI INNER JOIN
	CHQ_LANC_CPL ON 
		CHQ_EMI.CD_EMP = CHQ_LANC_CPL.CD_EMP AND CHQ_EMI.CD_EMI = CHQ_LANC_CPL.CD_EMI 
	INNER JOIN 	CHQ_LANC_CHQ_LANC_CPL ON 
		CHQ_LANC_CPL.CD_EMP = CHQ_LANC_CHQ_LANC_CPL.CD_EMP 
		AND CHQ_LANC_CPL.CD_CHQ = CHQ_LANC_CHQ_LANC_CPL.CD_CHQ 
	INNER JOIN	CHQ_LANC ON 
		CHQ_LANC_CHQ_LANC_CPL.CD_EMP = CHQ_LANC.CD_EMP 
		AND CHQ_LANC_CHQ_LANC_CPL.ANO = CHQ_LANC.ANO 
		AND CHQ_LANC_CHQ_LANC_CPL.CD_CHQ_LANC = CHQ_LANC.CD_CHQ_LANC 
	INNER JOIN	BC_LANC_CHQ_LANC_CUSTODIA_CPL_ESTORNO 
	INNER JOIN	BC_LANC_CHQ_LANC_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	CHQ_LANC_CUSTODIA ON 
		BC_LANC_CHQ_LANC_CUSTODIA.CD_EMP = CHQ_LANC_CUSTODIA.CD_EMP 
		AND BC_LANC_CHQ_LANC_CUSTODIA.ANO = CHQ_LANC_CUSTODIA.ANO 
		AND BC_LANC_CHQ_LANC_CUSTODIA.CD_CUSTODIA = CHQ_LANC_CUSTODIA.CD_CUSTODIA ON 
		BC_LANC_CHQ_LANC_CUSTODIA_CPL_ESTORNO.CD_EMP = CHQ_LANC_CUSTODIA.CD_EMP 
		AND BC_LANC_CHQ_LANC_CUSTODIA_CPL_ESTORNO.ANO = CHQ_LANC_CUSTODIA.ANO 
		AND BC_LANC_CHQ_LANC_CUSTODIA_CPL_ESTORNO.CD_CUSTODIA = CHQ_LANC_CUSTODIA.CD_CUSTODIA ON 
		CHQ_LANC_CPL.CD_EMP = BC_LANC_CHQ_LANC_CUSTODIA_CPL_ESTORNO.CD_EMP 
		AND CHQ_LANC_CPL.CD_CHQ = BC_LANC_CHQ_LANC_CUSTODIA_CPL_ESTORNO.CD_CHQ 
	LEFT OUTER JOIN	CHQ_LANC_CUSTODIA_REM ON 
		CHQ_LANC_CUSTODIA.CD_EMP = CHQ_LANC_CUSTODIA_REM.CD_EMP 
		AND CHQ_LANC_CUSTODIA.ANO = CHQ_LANC_CUSTODIA_REM.ANO 
		AND CHQ_LANC_CUSTODIA.CD_CUSTODIA = CHQ_LANC_CUSTODIA_REM.CD_CUSTODIA ON 
		CHQ_DEVO.CD_EMP = CHQ_LANC_CPL.CD_EMP 
		AND CHQ_DEVO.CD_CHQ = CHQ_LANC_CPL.CD_CHQ 
	LEFT OUTER 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 ON 
		CHQ_DEST_ENV_CPL.CD_EMP = CHQ_LANC_CPL.CD_EMP 
		AND CHQ_DEST_ENV_CPL.CD_CHQ = CHQ_LANC_CPL.CD_CHQ




GO


