IF  EXISTS (SELECT 1 FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[V_RC_CRED_HIST]'))
DROP VIEW [dbo].[V_RC_CRED_HIST]
GO

/*
=======================================================================================
'Nível Classificação.: INFORMAÇÃO RESTRITA
'Versão..............: 20230516
'Job.................:  
'Nome................: [V_RC_CRED_HIST]
'Objetivo............: 
'Tabelas.............: 
'Dt Criação..........: 
'Dt Ult Alteracão....: 2023-05-16
'Alterado por........: Marcos Barros
'Versionado por......: Marcos Barros
'Criado por..........: 
'=======================================================================================
 */

CREATE VIEW [dbo].[V_RC_CRED_HIST] AS     
Select     
 RC_CRED.CD_EMP,     
 RC_CRED.CD_FILIAL,     
 RC_CRED.CD_RC_DEB,    
 RC_CRED.CD_DP_LANC,     
 RC_CRED.DT_PAG,     
 RC_CRED.VL_JUR,     
 RC_CRED.VLR_IMPOSTO,     
 RC_CRED.VL_DESC,     
 CASE WHEN  DBO.RC_DEB.VLR_DP <> ISNULL(RC_CRED.VL_PGTO,0) THEN    
  (ISNULL(RC_CRED.VL_PGTO, 0) + ISNULL(RC_CRED.VLR_IMPOSTO, 0))    
 ELSE    
    ISNULL(RC_CRED.VL_PGTO, 0)    
 END AS VL_PGTO,    
 RC_CRED.VLR_COMIS,    
 RC_CRED.VLR_GLOSA,     
 ISNULL(RC_CRED.VLR_TAXA, 0) AS VLR_TAXA,     
 RC_CRED.HIST,    
 RC_DEB.DT_DP,    
 RC_DEB.PARC,    
 RC_DEB.DT_VENCTO,    
 RC_DEB.CD_CLI,    
 RC_DEB.NR_FATURA,    
 ISNULL(BC_CONTAS.NR_CNT,'') AS NR_CNT,     
    ISNULL(BC_CONTAS.CD_BC,'') AS CD_BC,     
 RC_DEB.COB,
 0 AS TP_BAIXA,    
 RC_DEB.ID_BOLETO_STONE, 
 RC_CRED.CD_USU    
From     
 RC_CRED LEFT OUTER JOIN RC_CRED_BC_LANC ON    
 RC_CRED.CD_EMP = RC_CRED_BC_LANC.CD_EMP AND    
 RC_CRED.CD_FILIAL = RC_CRED_BC_LANC.CD_FILIAL AND    
 RC_CRED.CD_RC_DEB = RC_CRED_BC_LANC.CD_RC_DEB AND    
 RC_CRED.CD_DP_LANC = RC_CRED_BC_LANC.CD_DP_LANC    
 LEFT OUTER JOIN BC_LANC ON    
 RC_CRED_BC_LANC.CD_EMP = BC_LANC.CD_EMP AND    
 RC_CRED_BC_LANC.CD_BC_LANC = BC_LANC.CD_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_DEB ON    
 RC_CRED.CD_EMP = RC_DEB.CD_EMP AND    
 RC_CRED.CD_FILIAL = RC_DEB.CD_FILIAL AND    
 RC_CRED.CD_RC_DEB = RC_DEB.CD_RC_DEB    
 LEFT OUTER JOIN PDV_VD_DEV_NCC_LANC_RC_CRED ON     
 RC_CRED.CD_EMP = PDV_VD_DEV_NCC_LANC_RC_CRED.CD_EMP AND     
 RC_CRED.CD_FILIAL = PDV_VD_DEV_NCC_LANC_RC_CRED.CD_FILIAL AND     
 RC_CRED.CD_RC_DEB = PDV_VD_DEV_NCC_LANC_RC_CRED.CD_RC_DEB AND     
    RC_CRED.CD_DP_LANC = PDV_VD_DEV_NCC_LANC_RC_CRED.CD_DP_LANC     
 LEFT OUTER JOIN  RC_CRED_LOTE_RC_CRED  ON    
 RC_CRED.CD_EMP = RC_CRED_LOTE_RC_CRED.CD_EMP AND      
 RC_CRED.CD_FILIAL = RC_CRED_LOTE_RC_CRED.CD_FILIAL AND     
 RC_CRED.CD_RC_DEB = RC_CRED_LOTE_RC_CRED.CD_RC_DEB AND    
 RC_CRED.CD_DP_LANC = RC_CRED_LOTE_RC_CRED.CD_DP_LANC    
 LEFT OUTER JOIN  CHQ_LANC_RC_CRED  ON    
 RC_CRED.CD_EMP = CHQ_LANC_RC_CRED.CD_EMP AND      
 RC_CRED.CD_FILIAL = CHQ_LANC_RC_CRED.CD_FILIAL AND     
 RC_CRED.CD_RC_DEB = CHQ_LANC_RC_CRED.CD_RC_DEB AND    
 RC_CRED.CD_DP_LANC = CHQ_LANC_RC_CRED.CD_DP_LANC     
WHERE    
    PDV_VD_DEV_NCC_LANC_RC_CRED.CD_DP_LANC IS NULL     
    AND RC_CRED_LOTE_RC_CRED.CD_DP_LANC IS NULL    
    AND CHQ_LANC_RC_CRED.CD_DP_LANC IS NULL    
UNION ALL    
Select     
 RC_CRED.CD_EMP,     
 RC_CRED.CD_FILIAL,     
 RC_CRED.CD_RC_DEB,     
 RC_CRED.CD_DP_LANC,     
 RC_CRED.DT_PAG,     
 RC_CRED.VL_JUR,     
 RC_CRED.VLR_IMPOSTO,     
 RC_CRED.VL_DESC,     
 CASE WHEN  DBO.RC_DEB.VLR_DP <> ISNULL(RC_CRED.VL_PGTO,0) THEN    
  (ISNULL(RC_CRED.VL_PGTO, 0) + ISNULL(RC_CRED.VLR_GLOSA, 0) + ISNULL(RC_CRED.VLR_IMPOSTO, 0))    
 ELSE    
    ISNULL(RC_CRED.VL_PGTO, 0)    
 END AS VL_PGTO,    
 RC_CRED.VLR_COMIS,    
 RC_CRED.VLR_GLOSA,   
 ISNULL(RC_CRED.VLR_TAXA, 0) AS VLR_TAXA,       
 RC_CRED.HIST,    
 RC_DEB.DT_DP,    
 RC_DEB.PARC,    
 RC_DEB.DT_VENCTO,    
 RC_DEB.CD_CLI,    
 RC_DEB.NR_FATURA,    
 BC_CONTAS.NR_CNT,     
    BC_CONTAS.CD_BC,     
 RC_DEB.COB,
 1 AS TP_BAIXA,   
 RC_DEB.ID_BOLETO_STONE,  
 RC_CRED.CD_USU    
From     
 RC_CRED INNER JOIN  RC_CRED_LOTE_RC_CRED  ON    
 RC_CRED.CD_EMP = RC_CRED_LOTE_RC_CRED.CD_EMP AND      
 RC_CRED.CD_FILIAL = RC_CRED_LOTE_RC_CRED.CD_FILIAL AND     
 RC_CRED.CD_RC_DEB = RC_CRED_LOTE_RC_CRED.CD_RC_DEB AND    
 RC_CRED.CD_DP_LANC = RC_CRED_LOTE_RC_CRED.CD_DP_LANC     
 INNER JOIN  RC_CRED_LOTE_BC_LANC  ON    
 RC_CRED_LOTE_RC_CRED.CD_EMP = RC_CRED_LOTE_BC_LANC.CD_EMP AND      
 RC_CRED_LOTE_RC_CRED.CD_RC_CRED_LOTE = RC_CRED_LOTE_BC_LANC.CD_RC_CRED_LOTE      
 INNER JOIN BC_LANC ON    
 RC_CRED_LOTE_BC_LANC.CD_EMP = BC_LANC.CD_EMP AND    
 RC_CRED_LOTE_BC_LANC.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    
 INNER JOIN RC_DEB ON    
 RC_CRED.CD_EMP = RC_DEB.CD_EMP AND    
 RC_CRED.CD_FILIAL = RC_DEB.CD_FILIAL AND    
 RC_CRED.CD_RC_DEB = RC_DEB.CD_RC_DEB    
 LEFT OUTER JOIN PDV_VD_DEV_NCC_LANC_RC_CRED ON     
 RC_CRED.CD_EMP = PDV_VD_DEV_NCC_LANC_RC_CRED.CD_EMP AND     
 RC_CRED.CD_FILIAL = PDV_VD_DEV_NCC_LANC_RC_CRED.CD_FILIAL AND     
 RC_CRED.CD_RC_DEB = PDV_VD_DEV_NCC_LANC_RC_CRED.CD_RC_DEB AND     
    RC_CRED.CD_DP_LANC = PDV_VD_DEV_NCC_LANC_RC_CRED.CD_DP_LANC     
WHERE    
    PDV_VD_DEV_NCC_LANC_RC_CRED.CD_DP_LANC IS NULL    
UNION ALL    
Select     
 RC_CRED.CD_EMP,     
 RC_CRED.CD_FILIAL,     
 RC_CRED.CD_RC_DEB,     
 RC_CRED.CD_DP_LANC,     
 RC_CRED.DT_PAG,     
 RC_CRED.VL_JUR,     
 RC_CRED.VLR_IMPOSTO,     
 RC_CRED.VL_DESC,     
 CASE WHEN  DBO.RC_DEB.VLR_DP <> ISNULL(RC_CRED.VL_PGTO,0) THEN    
  (ISNULL(RC_CRED.VL_PGTO, 0) + ISNULL(RC_CRED.VLR_GLOSA, 0) + ISNULL(RC_CRED.VLR_IMPOSTO, 0))    
 ELSE    
    ISNULL(RC_CRED.VL_PGTO, 0)    
 END AS VL_PGTO,    
 RC_CRED.VLR_COMIS,    
 RC_CRED.VLR_GLOSA,  
 ISNULL(RC_CRED.VLR_TAXA, 0) AS VLR_TAXA,        
 RC_CRED.HIST,    
 RC_DEB.DT_DP,    
 RC_DEB.PARC,    
 RC_DEB.DT_VENCTO,    
 RC_DEB.CD_CLI,     
 RC_DEB.NR_FATURA,    
 BC_CONTAS.NR_CNT,     
    BC_CONTAS.CD_BC,   
 RC_DEB.COB,  
 2 AS TP_BAIXA,   
 RC_DEB.ID_BOLETO_STONE,  
 RC_CRED.CD_USU     
From     
 RC_CRED INNER JOIN  CHQ_LANC_RC_CRED  ON    
 RC_CRED.CD_EMP = CHQ_LANC_RC_CRED.CD_EMP AND      
 RC_CRED.CD_FILIAL = CHQ_LANC_RC_CRED.CD_FILIAL AND     
 RC_CRED.CD_RC_DEB = CHQ_LANC_RC_CRED.CD_RC_DEB AND    
 RC_CRED.CD_DP_LANC = CHQ_LANC_RC_CRED.CD_DP_LANC     
 INNER JOIN  BC_LANC_CHQ_LANC  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 AND      
 CHQ_LANC_RC_CRED.ANO = BC_LANC_CHQ_LANC.ANO     
 INNER JOIN BC_LANC ON    
 BC_LANC_CHQ_LANC.CD_EMP = BC_LANC.CD_EMP AND    
 BC_LANC_CHQ_LANC.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    
 INNER JOIN RC_DEB ON    
 RC_CRED.CD_EMP = RC_DEB.CD_EMP AND    
 RC_CRED.CD_FILIAL = RC_DEB.CD_FILIAL AND    
 RC_CRED.CD_RC_DEB = RC_DEB.CD_RC_DEB     
 LEFT OUTER JOIN PDV_VD_DEV_NCC_LANC_RC_CRED ON     
 RC_CRED.CD_EMP = PDV_VD_DEV_NCC_LANC_RC_CRED.CD_EMP AND     
 RC_CRED.CD_FILIAL = PDV_VD_DEV_NCC_LANC_RC_CRED.CD_FILIAL AND     
 RC_CRED.CD_RC_DEB = PDV_VD_DEV_NCC_LANC_RC_CRED.CD_RC_DEB AND     
    RC_CRED.CD_DP_LANC = PDV_VD_DEV_NCC_LANC_RC_CRED.CD_DP_LANC     
WHERE    
    PDV_VD_DEV_NCC_LANC_RC_CRED.CD_DP_LANC IS NULL    
UNION ALL    
SELECT         
 RC_CRED.CD_EMP,     
 RC_CRED.CD_FILIAL,     
 RC_CRED.CD_RC_DEB,     
 RC_CRED.CD_DP_LANC,     
 RC_CRED.DT_PAG,     
 RC_CRED.VL_JUR,     
    RC_CRED.VLR_IMPOSTO,     
    RC_CRED.VL_DESC,     
    RC_CRED.VL_PGTO,     
    RC_CRED.VLR_COMIS,     
    RC_CRED.VLR_GLOSA,  
	ISNULL(RC_CRED.VLR_TAXA, 0) AS VLR_TAXA,        
    RC_CRED.HIST,     
    RC_DEB.DT_DP,    
    RC_DEB.PARC,    
    RC_DEB.DT_VENCTO,    
 RC_DEB.CD_CLI,        
    RC_DEB.NR_FATURA,    
    '' AS NR_CNT,    
    0 AS CD_BC,     
	RC_DEB.COB,
    3 AS TP_BAIXA,   
	RC_DEB.ID_BOLETO_STONE, 
    RC_CRED.CD_USU    
FROM             
 RC_CRED INNER JOIN PDV_VD_DEV_NCC_LANC_RC_CRED ON     
 RC_CRED.CD_EMP = PDV_VD_DEV_NCC_LANC_RC_CRED.CD_EMP AND     
 RC_CRED.CD_FILIAL = PDV_VD_DEV_NCC_LANC_RC_CRED.CD_FILIAL AND     
 RC_CRED.CD_RC_DEB = PDV_VD_DEV_NCC_LANC_RC_CRED.CD_RC_DEB AND     
    RC_CRED.CD_DP_LANC = PDV_VD_DEV_NCC_LANC_RC_CRED.CD_DP_LANC    
 INNER JOIN RC_DEB ON    
 RC_CRED.CD_EMP = RC_DEB.CD_EMP AND    
 RC_CRED.CD_FILIAL = RC_DEB.CD_FILIAL AND    
 RC_CRED.CD_RC_DEB = RC_DEB.CD_RC_DEB



