/****** Object:  View [dbo].[V_RC_DEB_POSICAO_FINANCEIRO]    Script Date: 08/31/2013 17:26:31 ******/
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[V_RC_DEB_POSICAO_FINANCEIRO]'))
DROP VIEW [dbo].[V_RC_DEB_POSICAO_FINANCEIRO]
GO
GO
GO
--SELECT * FROM V_RC_DEB_POSICAO_FINANCEIRO WHERE DT_PROC='2017-02-24'
CREATE VIEW V_RC_DEB_POSICAO_FINANCEIRO AS
 SELECT  
	RC_DEB_HISTORICO.DT_PROC,
	RC_DEB_HISTORICO_CPL.CD_EMP, 
	RC_DEB_HISTORICO_CPL.CD_FILIAL,  
	RC_DEB_HISTORICO_CPL.NR_DP,  
	RC_DEB_HISTORICO_CPL.PARC,  
	RC_DEB_HISTORICO_CPL.CD_CLI,  
	RC_CLI.NM_FANT,  
	RC_CLI.RZ_CLI,  
	RC_DEB_HISTORICO_CPL.VLR_DP,  
	RC_DEB_HISTORICO_CPL.SLD_DP,  
	0 AS VL_JUR, 
	0 AS VL_DESC, 
	0 AS VLR_GLOSA ,  
	RC_DEB_HISTORICO_CPL.VLR_IMPOSTO,  
	RC_DEB_HISTORICO_CPL.DT_DP,  
	RC_DEB_HISTORICO_CPL.DT_VENCTO,   
	CASE WHEN RC_DEB_HISTORICO_CPL.STS_DP = 0 THEN      
		CASE WHEN RC_DEB_HISTORICO_CPL.STS_NF = 0 THEN          
			'EM ABERTO'     
		ELSE          
			'NOTA FISCAL CANCELADA'     
		END 
	WHEN RC_DEB_HISTORICO_CPL.STS_DP = 1 THEN      
		'BAIXADO' 
	WHEN RC_DEB_HISTORICO_CPL.STS_DP = 2 THEN      
		'BAIXADO PARCIALMENTE' 
	WHEN RC_DEB_HISTORICO_CPL.STS_DP = 3 THEN      
		'CANCELADA' 
	WHEN RC_DEB_HISTORICO_CPL.STS_DP = 4 THEN      
		'RENEGOCIADA'
	WHEN RC_DEB_HISTORICO_CPL.STS_DP = 5 THEN      
		'AGUARDANDO RETORNO BANCO(CNAB)'
	WHEN RC_DEB_HISTORICO_CPL.STS_DP = 6 THEN      
		'DUPLICATA ESTORNADA'
	ELSE      
		'NAO DEFINIDO'  
	END STS_DP, 
	RC_CLI.TEL ,  
	DATEDIFF(d,DT_VENCTO, RC_DEB_HISTORICO.DT_PROC) AS ATRASO,  
	DATEDIFF(d,DT_DP, DT_VENCTO) AS PRZ_PGTO,  
	ISNULL(RC_DEB_HISTORICO_CPL.CD_BC,'') AS CD_BC, 
	CASE WHEN RC_DEB_HISTORICO_CPL.FLAG_CONVENIO = 0 THEN      
		CASE WHEN RC_DEB_HISTORICO_CPL.FLAG_ADM_CARTAO=0 THEN          
			0     
		ELSE          
			5     
		END 
	ELSE      
		6  
	END AS TIPO_DUPLICATA, 
	CASE WHEN RC_DEB_HISTORICO_CPL.FLAG_CONVENIO = 0 THEN      
		CASE WHEN RC_DEB_HISTORICO_CPL.FLAG_ADM_CARTAO=0 THEN          
			'DUPLICATA'     
		ELSE          
			'OPERADORA DE CARTAO'     
		END 
	ELSE      
		'CONVENIO FECHADO'  
	END DS_TIPO_DUPLICATA, 
	'PROVISAO' AS TRANSACAO, 
	CASE WHEN RC_DEB_HISTORICO_CPL.FLAG_CONTRATO_VERBA=0 THEN   
		'NÃO' 
	ELSE   
		'SIM' 
	END AS VERBA_COMERCIAL, 
	GLB_REG.DS_REG, 
	1 AS QTDE 
FROM 
	RC_DEB_HISTORICO_CPL  INNER JOIN RC_DEB_HISTORICO ON  
	RC_DEB_HISTORICO_CPL.CD_CTR = RC_DEB_HISTORICO.CD_CTR 
	INNER JOIN RC_CLI ON  
	RC_DEB_HISTORICO_CPL.CD_EMP = RC_CLI.CD_EMP AND 
	RC_DEB_HISTORICO_CPL.CD_CLI = RC_CLI.CD_CLI  
	LEFT JOIN PRC_FILIAL_RC_CLI FC ON  
	RC_DEB_HISTORICO_CPL.CD_EMP = FC.CD_EMP AND  
	RC_DEB_HISTORICO_CPL.CD_CLI = FC.CD_CLI    
	LEFT JOIN PRC_GRP_ECON_PRC_FILIAL GF ON  
	FC.CD_EMP = GF.CD_EMP AND  
	FC.CD_FILIAL = GF.CD_FILIAL  
	LEFT JOIN PRC_GRP_ECON G ON   
	GF.CD_GRP_ECON = G.CD_GRP_ECON 
	LEFT OUTER JOIN BC_BANCO ON  
	BC_BANCO.CD_EMP = RC_DEB_HISTORICO_CPL.CD_EMP AND 
	BC_BANCO.CD_BC = RC_DEB_HISTORICO_CPL.CD_BC 
	INNER JOIN PRC_GRP_ECON_PRC_FILIAL  ON  
	RC_DEB_HISTORICO_CPL.CD_EMP = PRC_GRP_ECON_PRC_FILIAL.CD_EMP AND 
	RC_DEB_HISTORICO_CPL.CD_FILIAL = PRC_GRP_ECON_PRC_FILIAL.CD_FILIAL  
	INNER JOIN PRC_GRP_ECON ON  
	PRC_GRP_ECON_PRC_FILIAL.CD_GRP_ECON = PRC_GRP_ECON.CD_GRP_ECON   
	INNER JOIN GLB_REG ON    
	GLB_REG.CD_EMP = RC_CLI.CD_EMP AND    
	GLB_REG.CD_REG = RC_CLI.CD_REGI 
UNION ALL
SELECT      
	RC_DEB_HISTORICO.DT_PROC,  
	CHQ_LANC.CD_EMP,
	CHQ_LANC.CD_FILIAL, 
	CHQ_LANC_CPL.NR_CHQ AS NR_DP, 
	1 AS PARC, 
	CHQ_EMI.CD_EMI, 
	CHQ_EMI.NM_EMI AS NM_FANT, 
	CHQ_EMI.NM_EMI AS RZ_CLI , 
	CHQ_LANC_CPL.VLR_CHQ AS VLR_DP,
	RC_DEB_HISTORICO_CPL_CHQ_LANC_CPL.VLR_CHQ_SLD AS SLD_DP,
	0 AS VL_JUR,
	0 AS VL_DESC,
	0 AS VLR_GLOSA,
	0 AS VLR_IMPOSTO,
	CHQ_LANC_CPL.DT_EMI, 
	CHQ_LANC_CPL.DT_VENCTO, 
	'EM ABERTO' AS STS_DP,
	 '' AS TEL,
	 DATEDIFF(d,CHQ_LANC_CPL.DT_VENCTO, RC_DEB_HISTORICO.DT_PROC) AS ATRASO,
	DATEDIFF(d,CHQ_LANC_CPL.DT_EMI, CHQ_LANC_CPL.DT_VENCTO) AS PRZ_PGTO, 
	CHQ_LANC_CPL.CD_BC_CHQ AS CD_BC, 
	1 AS TIPO_DUPLICATA,
	'CHEQUE ABERTO' AS DS_TIPO_DUPLICATA, 
	'PROVISAO' AS TRANSACAO, 
	'NAO' AS VERBA_COMERCIAL,
	'NAO DEFINIDO' AS DS_REG,
	1 AS QTDE
FROM       
	RC_DEB_HISTORICO INNER JOIN  RC_DEB_HISTORICO_CPL_CHQ_LANC_CPL ON
	RC_DEB_HISTORICO.CD_CTR = RC_DEB_HISTORICO_CPL_CHQ_LANC_CPL.CD_CTR 
	INNER JOIN CHQ_LANC_CPL ON 
	RC_DEB_HISTORICO_CPL_CHQ_LANC_CPL.CD_EMP = CHQ_LANC_CPL.CD_EMP AND 
	RC_DEB_HISTORICO_CPL_CHQ_LANC_CPL.CD_CHQ = CHQ_LANC_CPL.CD_CHQ 
	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 CHQ_EMI ON 
	CHQ_LANC_CPL.CD_EMP = CHQ_EMI.CD_EMP AND 
	CHQ_LANC_CPL.CD_EMI = CHQ_EMI.CD_EMI
UNION ALL
SELECT        
	RC_DEB_HISTORICO.DT_PROC, 
	RC_CREDIARIO_PARC.CD_EMP, 
	RC_CREDIARIO_PARC_PDV_VD.CD_FILIAL, 
	RC_CREDIARIO_PARC.CD_CTR, 
	RC_CREDIARIO_PARC.PARC_CRED, 
    RC_CREDIARIO_PARC.CD_CLI, 
	RC_CLI.NM_FANT, 
	RC_CLI.RZ_CLI, 
	RC_CREDIARIO_PARC.VLR_PARC_CRED, 
	RC_DEB_HISTORICO_CPL_RC_CREDIARIO_PARC.VLR_PARC_CRED_SLD, 
	0 AS VL_JUR, 
    0 AS VL_DESC, 
	0 AS VLR_GLOSA, 
	0 AS VLR_IMPOSTO, 
	RC_CREDIARIO_PARC.DT_LANC_CRED, 
	RC_CREDIARIO_PARC.DT_VENCTO_CRED, 
	'EM ABERTO' AS STS_DP, 
	RC_CLI.TEL, 
	DATEDIFF(d,RC_CREDIARIO_PARC.DT_VENCTO_CRED, RC_DEB_HISTORICO.DT_PROC) AS ATRASO, 
	DATEDIFF(d, RC_CREDIARIO_PARC.DT_LANC_CRED, RC_CREDIARIO_PARC.DT_VENCTO_CRED) AS PRZ_PGTO, 
	0 AS CD_BC, 
	2 AS TIPO_DUPLICATA,
    'CREADIARIO PARCELAS' AS DS_TIPO_DUPLICATA, 
	'PROVISAO' AS TRANSACAO, 
	'NAO' AS VERBA_COMERCIAL, 
	GLB_REG.DS_REG, 
	1 AS QTDE
FROM            
	RC_DEB_HISTORICO_CPL_RC_CREDIARIO_PARC INNER JOIN RC_DEB_HISTORICO ON 
	RC_DEB_HISTORICO_CPL_RC_CREDIARIO_PARC.CD_CTR = RC_DEB_HISTORICO.CD_CTR 
	INNER JOIN RC_CREDIARIO_PARC ON 
	RC_DEB_HISTORICO_CPL_RC_CREDIARIO_PARC.CD_CTR_PARC = RC_CREDIARIO_PARC.CD_CTR 
	INNER JOIN RC_CREDIARIO_PARC_PDV_VD ON 
	RC_CREDIARIO_PARC.CD_CTR = RC_CREDIARIO_PARC_PDV_VD.CD_CTR 
	INNER JOIN RC_CLI ON 
	RC_CREDIARIO_PARC.CD_EMP = RC_CLI.CD_EMP AND 
	RC_CREDIARIO_PARC.CD_CLI = RC_CLI.CD_CLI
	INNER JOIN GLB_REG ON    
	GLB_REG.CD_EMP = RC_CLI.CD_EMP AND    
	GLB_REG.CD_REG = RC_CLI.CD_REGI 
UNION ALL
SELECT        
	RC_DEB_HISTORICO.DT_PROC, 
	RC_VD_CONV.CD_EMP, 
	RC_VD_CONV.CD_FILIAL, 
	RC_VD_CONV.CD_CTR, 
	RC_VD_CONV.PARC, 
    RC_VD_CONV.CD_CONV, 
	RC_CLI_CONV.DS_CONV, 
	RC_CLI_CONV.DS_CONV, 
	RC_VD_CONV.VLR_CONV, 
	RC_DEB_HISTORICO_CPL_RC_VD_CONV.VLR_CONV_SLD, 
	0 AS VL_JUR, 
    0 AS VL_DESC, 
	0 AS VLR_GLOSA, 
	0 AS VLR_IMPOSTO, 
	RC_VD_CONV.DT_VD, 
	RC_VD_CONV.DT_FECHA, 
	'EM ABERTO' AS STS_DP, 
	'' AS TEL, 
	DATEDIFF(d,RC_VD_CONV.DT_FECHA, RC_DEB_HISTORICO.DT_PROC) AS ATRASO, 
	DATEDIFF(d, RC_VD_CONV.DT_VD, RC_VD_CONV.DT_FECHA) AS PRZ_PGTO, 
	0 AS CD_BC, 
	4 AS TIPO_DUPLICATA,
    'VENDAS CONVENIO SEM FECHAMENTO' AS DS_TIPO_DUPLICATA, 
	'PROVISAO' AS TRANSACAO, 
	'NAO' AS VERBA_COMERCIAL, 
	'NAO DEFINIDO' AS DS_REG, 
	1 AS QTDE
FROM            
	RC_DEB_HISTORICO_CPL_RC_VD_CONV INNER JOIN RC_DEB_HISTORICO ON 
	RC_DEB_HISTORICO_CPL_RC_VD_CONV.CD_CTR = RC_DEB_HISTORICO.CD_CTR 	
	INNER JOIN RC_VD_CONV ON 
	RC_DEB_HISTORICO_CPL_RC_VD_CONV.CD_EMP = RC_VD_CONV.CD_EMP AND 
	RC_DEB_HISTORICO_CPL_RC_VD_CONV.CD_CTR_CONV = RC_VD_CONV.CD_CTR
	INNER JOIN RC_CLI_CONV ON
	RC_VD_CONV.CD_EMP =RC_CLI_CONV.CD_EMP AND
	RC_VD_CONV.CD_CONV = RC_CLI_CONV.CD_CONV