/****** Object:  View [dbo].[V_BC_LANC_CREDITOS_CONTABIL]    Script Date: 04/14/2014 17:08:57 ******/
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[V_BC_LANC_CREDITOS_CONTABIL]'))
DROP VIEW [dbo].[V_BC_LANC_CREDITOS_CONTABIL]
GO
GO
GO
--SELECT * FROM V_BC_LANC_CREDITOS_CONTABIL WHERE DT_LANCTO = '2018-07-10'
CREATE VIEW [dbo].[V_BC_LANC_CREDITOS_CONTABIL] AS
SELECT
	CD_EMP,
	CD_BC_LANC,
	DT_LANCTO,
	VLR_RECTA,    
	CD_CNT,    
	CD_DESP_REC,     
	DS_DESP_REC,     
	HIST_LANC,     
	DS_HIST,     
	NR_CNT_DESP,    
	CD_CNT_CTB_RED_DESP,    
	CD_CTB_CNT_CONTA,    
	CD_CTB_CNT_RED_CONTA,
	TIPO_TRANSACAO,
	CD_TIPO_TRANSACAO,
	CD_CONTROLE
FROM
(
Select 
	BC_LANC.CD_EMP,
	BC_LANC.CD_BC_LANC,
	BC_LANC.DT_LANCTO,
	BC_LANC.VLR_LANC AS VLR_RECTA,    
	BC_LANC.CD_CNT,    
	ISNULL(RC_RECTA_RAT.CD_DESP_REC,0) AS CD_DESP_REC,     
	ISNULL(DS_DESP_REC,'') AS DS_DESP_REC,     
	ISNULL(HIST_LANC,'') AS HIST_LANC,     
	ISNULL(DS_HIST,'') AS DS_HIST,     
	ISNULL(GLB_DESP_REC.NR_CNT,'') as NR_CNT_DESP,    
	ISNULL(GLB_DESP_REC.CD_CNT_CTB_RED,'') as CD_CNT_CTB_RED_DESP,    
	BC_CONTAS.CD_CTB_CNT as CD_CTB_CNT_CONTA,    
	BC_CONTAS.CD_CTB_CNT_RED as CD_CTB_CNT_RED_CONTA,
	'LANCTO DIRETO BANCARIO' AS TIPO_TRANSACAO,
	0 AS CD_TIPO_TRANSACAO,
	0 AS CD_CONTROLE
From 
	BC_LANC 
	left outer Join RC_RECTA_RAT_BC_LANC ON 
	BC_LANC.CD_EMP = RC_RECTA_RAT_BC_LANC.CD_EMP AND    
	BC_LANC.Cd_Bc_lanc = RC_RECTA_RAT_BC_LANC.Cd_Bc_lanc 
	left outer Join     RC_RECTA_RAT ON 
	RC_RECTA_RAT_BC_LANC.CD_EMP = RC_RECTA_RAT.CD_EMP AND    
	RC_RECTA_RAT_BC_LANC.CD_RAT_RECTA = RC_RECTA_RAT.CD_RAT_RECTA 
	left outer Join     GLB_DESP_REC ON 
	RC_RECTA_RAT.CD_EMP = GLB_DESP_REC.CD_EMP AND    
	RC_RECTA_RAT.CD_DESP_REC = GLB_DESP_REC.CD_DESP_REC 
	left outer Join     BC_CONTAS ON 
	BC_LANC.CD_EMP = BC_CONTAS.CD_EMP AND 
	BC_LANC.CD_CNT = BC_CONTAS.CD_CNT  
Where 
	BC_LANC.VLR_LANC > 0 
	AND BC_LANC.TP_LANC =0 
	AND BC_CONTAS.NAO_EXPORT_CONTABIL =0 
	AND NOT EXISTS(SELECT 
						CD_BC_LANC 
				    FROM
						BC_LANC_RC_ADM_CARTAO_MOV
					WHERE
						BC_LANC.CD_EMP = BC_LANC_RC_ADM_CARTAO_MOV.CD_EMP AND 
						BC_LANC.CD_BC_LANC = BC_LANC_RC_ADM_CARTAO_MOV.CD_BC_LANC)
	AND NOT EXISTS(SELECT 
						CD_BC_LANC 
				    FROM
						RC_CREDIARIO_PARC_CRED_BC_LANC
					WHERE
						BC_LANC.CD_EMP = RC_CREDIARIO_PARC_CRED_BC_LANC.CD_EMP AND 
						BC_LANC.CD_BC_LANC = RC_CREDIARIO_PARC_CRED_BC_LANC.CD_BC_LANC)

	AND NOT EXISTS(SELECT 
						CD_BC_LANC 
				    FROM
						RC_VD_CONV_RC_DEB_BC_LANC
					WHERE
						BC_LANC.CD_EMP = RC_VD_CONV_RC_DEB_BC_LANC.CD_EMP AND 
						BC_LANC.CD_BC_LANC = RC_VD_CONV_RC_DEB_BC_LANC.CD_BC_LANC)
UNION ALL
SELECT  	   
	BC_LANC.CD_EMP, 
	BC_LANC.CD_BC_LANC,
	BC_LANC.DT_LANCTO, 
	BC_LANC.VLR_LANC AS VLR_RECTA, 
	BC_LANC.CD_CNT, 
	0 AS CD_DESP_REC, 
	'' AS DS_DESP_REC, 
	ISNULL(BC_LANC.HIST_LANC, '') AS HIST_LANC, 
	'' AS DS_HIST, 
	ISNULL(RC_CLI_CNT_CTB.CD_CTB_CNT, '') AS NR_CNT_DESP, 
	ISNULL(RC_CLI_CNT_CTB.CD_CTB_CNT_RED, '') AS CD_CNT_CTB_RED_DESP, 
	BC_CONTAS.CD_CTB_CNT AS CD_CTB_CNT_CONTA, 
	BC_CONTAS.CD_CTB_CNT_RED AS CD_CTB_CNT_RED_CONTA,
	'LANCTO ADM CARTAO' AS TIPO_TRANSACAO,
	1 AS CD_TIPO_TRANSACAO,
	0 AS CD_CONTROLE
FROM         
	BC_LANC INNER JOIN BC_LANC_RC_ADM_CARTAO_MOV ON 
	BC_LANC.CD_EMP = BC_LANC_RC_ADM_CARTAO_MOV.CD_EMP AND 
	BC_LANC.CD_BC_LANC = BC_LANC_RC_ADM_CARTAO_MOV.CD_BC_LANC 
	INNER JOIN RC_ADM_CARTAO_MOV ON 
	BC_LANC_RC_ADM_CARTAO_MOV.CD_CTR = RC_ADM_CARTAO_MOV.CD_CTR 
	INNER JOIN RC_CLI_CNT_CTB ON 
	RC_ADM_CARTAO_MOV.CD_EMP = RC_CLI_CNT_CTB.CD_EMP AND 
    RC_ADM_CARTAO_MOV.CD_CLI = RC_CLI_CNT_CTB.CD_CLI 
	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.VLR_LANC > 0 
	AND BC_LANC.TP_LANC = 0
	AND RC_CLI_CNT_CTB.TP_CNT_CTB=0) RS
WHERE
	NOT EXISTS (SELECT  
					 CD_BC_LANC 
				 FROM  
					 BC_LANC_CHQ_DEVO 
				 WHERE 
					 BC_LANC_CHQ_DEVO.CD_EMP = RS.CD_EMP AND 
					 BC_LANC_CHQ_DEVO.CD_BC_LANC = RS.CD_BC_LANC) 

	AND NOT EXISTS (SELECT  
					 CD_BC_LANC  
					 FROM  
						 PG_DEB_LOTE_BC_LANC 
					 WHERE 
						 PG_DEB_LOTE_BC_LANC.CD_EMP = RS.CD_EMP AND 
						 PG_DEB_LOTE_BC_LANC.CD_BC_LANC = RS.CD_BC_LANC) 

	AND NOT EXISTS (SELECT  
						CD_BC_LANC  
					 FROM  
						 PG_DESP_RAT_BC_LANC 
					 WHERE 
						 PG_DESP_RAT_BC_LANC.CD_EMP = RS.CD_EMP AND 
						 PG_DESP_RAT_BC_LANC.CD_BC_LANC = RS.CD_BC_LANC) 

	AND NOT EXISTS (SELECT  
						 CD_BC_LANC  
					 FROM  
						 PG_DEB_BC_LANC 
					 WHERE 
						 PG_DEB_BC_LANC.CD_EMP = RS.CD_EMP AND 
						 PG_DEB_BC_LANC.CD_BC_LANC = RS.CD_BC_LANC) 

	AND NOT EXISTS (SELECT  
						 CD_BC_LANC
					 FROM  
						 BC_LANC_BC_CHQ_LANC 
					 WHERE 
						 BC_LANC_BC_CHQ_LANC.CD_EMP = RS.CD_EMP AND 
						 BC_LANC_BC_CHQ_LANC.CD_BC_LANC = RS.CD_BC_LANC) 

	AND NOT EXISTS (SELECT  
						 CD_BC_LANC
					 FROM  
						 BC_LANC_CHQ_REDEPOS 
					 WHERE 
						 BC_LANC_CHQ_REDEPOS.CD_EMP = RS.CD_EMP AND 
						 BC_LANC_CHQ_REDEPOS.CD_BC_LANC = RS.CD_BC_LANC) 

	AND NOT EXISTS (SELECT  
						 CD_BC_LANC 
					 FROM  
						 BC_LANC_TRANSF 
					 WHERE 
						 BC_LANC_TRANSF.CD_EMP = RS.CD_EMP AND 
						 BC_LANC_TRANSF.CD_BC_LANC_ORIG = RS.CD_BC_LANC) 

	AND NOT EXISTS (SELECT  
						CD_BC_LANC 
					 FROM  
						 BC_LANC_TRANSF 
					 WHERE 
						 BC_LANC_TRANSF.CD_EMP = RS.CD_EMP AND 
						 BC_LANC_TRANSF.CD_BC_LANC_DEST = RS.CD_BC_LANC) 

	AND NOT EXISTS (SELECT  
						 CD_BC_LANC  
					 FROM  
						 RC_CRED_BC_LANC 
					 WHERE 
						 RC_CRED_BC_LANC.CD_EMP = RS.CD_EMP AND 
						 RC_CRED_BC_LANC.CD_BC_LANC = RS.CD_BC_LANC) 

	AND NOT EXISTS (SELECT  
						CD_BC_LANC
					 FROM  
						 RC_CRED_LOTE_BC_LANC 
					 WHERE 
						 RC_CRED_LOTE_BC_LANC.CD_EMP = RS.CD_EMP AND 
						 RC_CRED_LOTE_BC_LANC.CD_BC_LANC = RS.CD_BC_LANC) 

	AND NOT EXISTS (SELECT  
						CD_BC_LANC  
					 FROM  
						 BC_LANC_CHQ_LANC_CPL 
					 WHERE 
						 BC_LANC_CHQ_LANC_CPL.CD_EMP = RS.CD_EMP AND 
						 BC_LANC_CHQ_LANC_CPL.CD_BC_LANC = RS.CD_BC_LANC) 

	AND NOT EXISTS (SELECT  
					 CD_BC_LANC  
				 FROM  
					 BC_LANC_CHQ_DEPOS 
				 WHERE 
					 BC_LANC_CHQ_DEPOS.CD_EMP = RS.CD_EMP AND 
					 BC_LANC_CHQ_DEPOS.CD_BC_LANC = RS.CD_BC_LANC) 

	AND NOT EXISTS (SELECT  
					 CD_BC_LANC  
				 FROM  
					 BC_LANC_CHQ_LANC_CUSTODIA 
				 WHERE 
					 BC_LANC_CHQ_LANC_CUSTODIA.CD_EMP = RS.CD_EMP AND 
					 BC_LANC_CHQ_LANC_CUSTODIA.CD_BC_LANC = RS.CD_BC_LANC) 

	AND NOT EXISTS (SELECT CD_BC_LANC FROM  
					 BC_LANC_CHQ_DEST_ENV 
				 WHERE 
					 RS.CD_EMP = BC_LANC_CHQ_DEST_ENV.CD_EMP AND 
					 RS.CD_BC_LANC = BC_LANC_CHQ_DEST_ENV.CD_BC_LANC) 

	AND NOT EXISTS (SELECT CD_BC_LANC FROM  
					 BC_LANC_CHQ_DEST_ENV_CPL 
				 WHERE 
					 RS.CD_EMP = BC_LANC_CHQ_DEST_ENV_CPL.CD_EMP AND 
					 RS.CD_BC_LANC = BC_LANC_CHQ_DEST_ENV_CPL.CD_BC_LANC)

	AND NOT EXISTS (SELECT BC_LANC_CHQ_LANC.CD_BC_LANC
				FROM BC_LANC_CHQ_LANC
					INNER JOIN CHQ_LANC_RC_CRED	ON 
						CHQ_LANC_RC_CRED.CD_EMP = BC_LANC_CHQ_LANC.CD_EMP AND
						CHQ_LANC_RC_CRED.CD_CHQ_LANC = BC_LANC_CHQ_LANC.CD_CHQ_LANC	
				WHERE
					BC_LANC_CHQ_LANC.CD_EMP 			= RS.CD_EMP 
					AND BC_LANC_CHQ_LANC.CD_BC_LANC		= RS.CD_BC_LANC)
					 
UNION ALL
Select 
	BC_LANC.CD_EMP,
	BC_LANC.CD_BC_LANC,
	DT_LANCTO AS DT_LANCTO ,
	RC_CREDIARIO_PARC_CRED.vlr_cred AS VLR_RECTA,    
	BC_LANC.CD_CNT,    
	ISNULL(0,0) AS CD_DESP_REC,     
	ISNULL('','') AS DS_DESP_REC,     
	ISNULL(HIST_LANC,'') AS HIST_LANC,     
	ISNULL('REF: ANTECIPACAO COMPRAS CREDIARIO CLIENTE ' + RZ_CLI ,'') AS DS_HIST,     
	ISNULL(rs.CD_CTB_CNT,'') as NR_CNT_DESP,    
	ISNULL(rs.CD_CTB_CNT_RED,'') as CD_CNT_CTB_RED_DESP,    
	BC_CONTAS.CD_CTB_CNT as CD_CTB_CNT_CONTA,    
	BC_CONTAS.CD_CTB_CNT_RED as CD_CTB_CNT_RED_CONTA,
	'LANCTO ANTECIPACAO CREDIARIO' AS TIPO_TRANSACAO,
	2 AS CD_TIPO_TRANSACAO,
	0 AS CD_CONTROLE
From 
	BC_LANC left outer Join  BC_CONTAS ON 
	BC_LANC.CD_EMP = BC_CONTAS.CD_EMP AND 
	BC_LANC.CD_CNT = BC_CONTAS.CD_CNT  
	INNER JOIN RC_CREDIARIO_PARC_CRED_BC_LANC ON
	BC_LANC.CD_EMP = RC_CREDIARIO_PARC_CRED_BC_LANC.CD_EMP AND
	BC_LANC.CD_BC_LANC = RC_CREDIARIO_PARC_CRED_BC_LANC.CD_BC_LANC
	INNER JOIN RC_CREDIARIO_PARC_CRED ON
	RC_CREDIARIO_PARC_CRED_BC_LANC.CD_CTR =RC_CREDIARIO_PARC_CRED.CD_CTR AND 
	RC_CREDIARIO_PARC_CRED_BC_LANC.CD_CTR_CRED=RC_CREDIARIO_PARC_CRED.CD_CTR_CRED
	INNER JOIN RC_CREDIARIO_PARC ON
	RC_CREDIARIO_PARC_CRED.CD_CTR  = RC_CREDIARIO_PARC.CD_CTR 

	LEFT OUTER JOIN (SELECT 			    
				RC_CLI.CD_EMP,
				RC_CLI.CD_CLI,
			    ISNULL(RC_CLI_CNT_CTB.CD_CTB_CNT,'') AS CD_CTB_CNT, 
				ISNULL(RC_CLI_CNT_CTB.CD_CTB_CNT_RED,0) AS CD_CTB_CNT_RED, 
			    RC_CLI.RZ_CLI    
		    FROM 
				RC_CLI 
				LEFT OUTER JOIN RC_CLI_CNT_CTB ON 
				    RC_CLI.CD_EMP = RC_CLI_CNT_CTB.CD_EMP AND 
				    RC_CLI.CD_CLI = RC_CLI_CNT_CTB.CD_CLI) rs ON 
	RC_CREDIARIO_PARC.CD_EMP = rs.CD_EMP AND
	RC_CREDIARIO_PARC.CD_CLI = rs.CD_CLI 
Where 
	BC_LANC.VLR_LANC > 0 
	AND BC_LANC.TP_LANC =0 
	AND BC_CONTAS.NAO_EXPORT_CONTABIL =0 
	AND NOT EXISTS(SELECT 
						CD_BC_LANC 
				    FROM
						BC_LANC_RC_ADM_CARTAO_MOV
					WHERE
						BC_LANC.CD_EMP = BC_LANC_RC_ADM_CARTAO_MOV.CD_EMP AND 
						BC_LANC.CD_BC_LANC = BC_LANC_RC_ADM_CARTAO_MOV.CD_BC_LANC)
UNION ALL
Select 
	BC_LANC.CD_EMP,
	BC_LANC.CD_BC_LANC,
	BC_LANC.DT_LANCTO,
	BC_LANC.VLR_LANC AS VLR_RECTA,    
	BC_LANC.CD_CNT,    
	ISNULL(RC_RECTA_RAT.CD_DESP_REC,0) AS CD_DESP_REC,     
	ISNULL(DS_DESP_REC,'') AS DS_DESP_REC,     
	ISNULL(HIST_LANC,'') AS HIST_LANC,     
	ISNULL(DS_HIST,'') AS DS_HIST,     
	ISNULL(GLB_DESP_REC.NR_CNT,'') as NR_CNT_DESP,    
	ISNULL(GLB_DESP_REC.CD_CNT_CTB_RED,'') as CD_CNT_CTB_RED_DESP,    
	BC_CONTAS.CD_CTB_CNT as CD_CTB_CNT_CONTA,    
	BC_CONTAS.CD_CTB_CNT_RED as CD_CTB_CNT_RED_CONTA,
	'COMPRA TITULO CONVENIO FECHAMENTO' AS TIPO_TRANSACAO,
	3 AS CD_TIPO_TRANSACAO,
	RC_VD_CONV_RC_DEB_BC_LANC.CD_CTR AS CD_CONTROLE
From 
	BC_LANC 
	left outer Join RC_RECTA_RAT_BC_LANC ON 
	BC_LANC.CD_EMP = RC_RECTA_RAT_BC_LANC.CD_EMP AND    
	BC_LANC.Cd_Bc_lanc = RC_RECTA_RAT_BC_LANC.Cd_Bc_lanc 
	left outer Join     RC_RECTA_RAT ON 
	RC_RECTA_RAT_BC_LANC.CD_EMP = RC_RECTA_RAT.CD_EMP AND    
	RC_RECTA_RAT_BC_LANC.CD_RAT_RECTA = RC_RECTA_RAT.CD_RAT_RECTA 
	left outer Join     GLB_DESP_REC ON 
	RC_RECTA_RAT.CD_EMP = GLB_DESP_REC.CD_EMP AND    
	RC_RECTA_RAT.CD_DESP_REC = GLB_DESP_REC.CD_DESP_REC 
	left outer Join     BC_CONTAS ON 
	BC_LANC.CD_EMP = BC_CONTAS.CD_EMP AND 
	BC_LANC.CD_CNT = BC_CONTAS.CD_CNT  
	INNER JOIN RC_VD_CONV_RC_DEB_BC_LANC ON 
	BC_LANC.CD_EMP = RC_VD_CONV_RC_DEB_BC_LANC.CD_EMP AND 
	BC_LANC.CD_BC_LANC = RC_VD_CONV_RC_DEB_BC_LANC.CD_BC_LANC
Where 
	BC_LANC.VLR_LANC > 0 
	AND BC_LANC.TP_LANC =0 
	AND BC_CONTAS.NAO_EXPORT_CONTABIL =0 
	AND NOT EXISTS(SELECT 
						CD_BC_LANC 
				    FROM
						BC_LANC_RC_ADM_CARTAO_MOV
					WHERE
						BC_LANC.CD_EMP = BC_LANC_RC_ADM_CARTAO_MOV.CD_EMP AND 
						BC_LANC.CD_BC_LANC = BC_LANC_RC_ADM_CARTAO_MOV.CD_BC_LANC)
	AND NOT EXISTS(SELECT 
						CD_BC_LANC 
				    FROM
						RC_CREDIARIO_PARC_CRED_BC_LANC
					WHERE
						BC_LANC.CD_EMP = RC_CREDIARIO_PARC_CRED_BC_LANC.CD_EMP AND 
						BC_LANC.CD_BC_LANC = RC_CREDIARIO_PARC_CRED_BC_LANC.CD_BC_LANC)