IF  EXISTS (SELECT * FROM sys.views WHERE name ='V_RC_DEB_VLR_SLD')
DROP VIEW [dbo].[V_RC_DEB_VLR_SLD]
GO

  
/*SELECT * FROM V_RC_DEB_VLR_SLD*/    
CREATE VIEW [dbo].[V_RC_DEB_VLR_SLD]    
AS    
SELECT         
 dbo.RC_DEB.CD_EMP,     
 dbo.RC_DEB.CD_FILIAL,     
 dbo.RC_DEB.CD_RC_DEB,     
 dbo.RC_DEB.NR_DP,     
 dbo.RC_DEB.PARC,     
 dbo.RC_DEB.ANO,    
    dbo.RC_DEB.VLR_DP - ISNULL(V_RC_DEB_IMPOSTO.VLR_IMP,0) - ISNULL(rs.VLR_GLOSA,0) AS VLR_DP,     
 CASE WHEN  dbo.RC_DEB.VLR_DP <> ISNULL(V_RC_DEB_VLR_PGTO.VLR_PGTO,0) THEN    
  (dbo.RC_DEB.VLR_DP - ISNULL(V_RC_DEB_IMPOSTO.VLR_IMP,0) - ISNULL(rs.VLR_GLOSA,0) - ISNULL(RC_DEB.VLR_COMIS_CARTAO,0) - ISNULL(RC_DEB.VLR_DESP_OUTR, 0))  - (ISNULL(V_RC_DEB_VLR_PGTO.VLR_PGTO, 0)   + ISNULL(V_RC_DEB_VLR_PGTO.VLR_IMP, 0))     
 -- + ISNULL(V_RC_DEB_VLR_PGTO.VLR_GLOSA, 0) = RETIRADO DO CALCULO POIS O VALOR PAGO J? CONTEMPLA GLOSA.    
 ELSE    
  0    
 END AS VLR_SLD,     
 dbo.RC_DEB.CD_CLI,     
    dbo.RC_DEB.DT_VENCTO,     
    
 CASE WHEN  dbo.RC_DEB.VLR_DP <> ISNULL(V_RC_DEB_VLR_PGTO.VLR_PGTO,0) THEN    
  (ISNULL(V_RC_DEB_VLR_PGTO.VLR_PGTO, 0) - ISNULL(V_RC_DEB_VLR_PGTO.VL_DESC, 0) + ISNULL(V_RC_DEB_VLR_PGTO.VLR_IMP, 0))    
 --  + ISNULL(V_RC_DEB_VLR_PGTO.VLR_GLOSA, 0) VALOR TOTAL PAGAMENTO J? CONTEMPLA VALOR DA GLOSA    
 ELSE    
  ISNULL(V_RC_DEB_VLR_PGTO.VLR_PGTO, 0)    
 END AS  VLR_PGTO,    
    --(ISNULL(V_RC_DEB_IMPOSTO.VLR_IMP,0) - ISNULL(V_RC_CRED_HIST.VLR_IMPOSTO, 0)) AS VLR_IMP,    
    --(ISNULL(rs.VLR_GLOSA,0) - ISNULL(V_RC_CRED_HIST.VLR_GLOSA, 0)) AS VLR_GLOSA,  
        (ISNULL(V_RC_DEB_IMPOSTO.VLR_IMP,0) ) AS VLR_IMP,    
    (ISNULL(rs.VLR_GLOSA,0) ) AS VLR_GLOSA,   
 dbo.RC_DEB.DT_DP,     
 dbo.RC_DEB.STS_DP,     
 dbo.RC_DEB.IMP_BOL,     
 dbo.RC_DEB.COB,     
 dbo.RC_DEB.CD_HIST,     
 dbo.RC_DEB.ORIG_LANC,     
 dbo.RC_DEB.STS_NF,     
 dbo.RC_DEB.NR_DOC,     
 dbo.RC_DEB.CD_BC,     
 dbo.RC_DEB.VLR_COMIS_CARTAO,     
    CASE WHEN ISNULL(dbo.PRC_FILIAL_RC_CLI.CD_FILIAL, 0) > 0 THEN     
  1     
 ELSE     
  0     
 END AS GRP_ECONOMICO,     
 ISNULL(V_RC_DEB_VLR_PGTO.VL_JUR,0) AS VL_JUR,     
  --  CASE WHEN ISNULL(V_RC_DEB_VLR_PGTO.VL_DESC,0) = 0 THEN    
  --RC_DEB.VLR_DESC    
  --  ELSE    
  --  (ISNULL(V_RC_CRED_HIST.VL_DESC, 0) - ISNULL(V_RC_DEB_VLR_PGTO.VL_DESC, 0))   
  --  END AS VL_DESC, 
   V_RC_DEB_VLR_PGTO.VL_DESC AS VL_DESC,   
 CASE WHEN ISNULL(dbo.PRC_FILIAL_RC_CLI.CD_FILIAL, 0) > 0 THEN     
  CD_GRP_ECON     
 ELSE     
  0    
 END AS GRP_ECON_ORIG,    
 RC_DEB.NR_BOLETA,    
 RC_DEB.CD_BARRAS_BOLETA     
FROM             
 dbo.RC_DEB LEFT OUTER JOIN dbo.PRC_FILIAL_RC_CLI ON     
 dbo.RC_DEB.CD_CLI = dbo.PRC_FILIAL_RC_CLI.CD_CLI AND     
    dbo.RC_DEB.CD_EMP = dbo.PRC_FILIAL_RC_CLI.CD_EMP    
     
 LEFT OUTER JOIN PRC_GRP_ECON_PRC_FILIAL ON    
 PRC_FILIAL_RC_CLI.CD_EMP = PRC_GRP_ECON_PRC_FILIAL.CD_EMP AND    
 PRC_FILIAL_RC_CLI.CD_FILIAL = PRC_GRP_ECON_PRC_FILIAL.CD_FILIAL     
     
 LEFT OUTER JOIN dbo.V_RC_DEB_VLR_PGTO ON     
 dbo.RC_DEB.CD_EMP = dbo.V_RC_DEB_VLR_PGTO.CD_EMP AND     
    dbo.RC_DEB.CD_FILIAL = dbo.V_RC_DEB_VLR_PGTO.CD_FILIAL AND     
 dbo.RC_DEB.CD_RC_DEB = dbo.V_RC_DEB_VLR_PGTO.CD_RC_DEB  
   
 --LEFT OUTER JOIN V_RC_CRED_HIST ON     
 --dbo.RC_DEB.CD_EMP = dbo.V_RC_CRED_HIST.CD_EMP AND     
 --dbo.RC_DEB.CD_FILIAL = dbo.V_RC_CRED_HIST.CD_FILIAL AND     
 --dbo.RC_DEB.CD_RC_DEB = dbo.V_RC_CRED_HIST.CD_RC_DEB  
     
 LEFT OUTER JOIN V_RC_DEB_IMPOSTO ON     
 RC_DEB.CD_EMP = V_RC_DEB_IMPOSTO.CD_EMP AND     
    RC_DEB.CD_FILIAL = V_RC_DEB_IMPOSTO.CD_FILIAL AND     
    RC_DEB.CD_RC_DEB = V_RC_DEB_IMPOSTO.CD_RC_DEB    
        
       
    LEFT OUTER JOIN (SELECT         
        RC_VD_CONV_RC_DEB_GLOSA.CD_EMP,     
        RC_VD_CONV_RC_DEB_GLOSA.CD_FILIAL,    
        RC_VD_CONV_RC_DEB_GLOSA.CD_RC_DEB,    
        SUM(RC_VD_CONV.VLR_CONV) AS VLR_GLOSA    
     FROM             
        RC_VD_CONV_RC_DEB_GLOSA INNER JOIN RC_VD_CONV ON     
        RC_VD_CONV_RC_DEB_GLOSA.CD_EMP = RC_VD_CONV.CD_EMP AND    
        RC_VD_CONV_RC_DEB_GLOSA.CD_CTR = RC_VD_CONV.CD_CTR     
        INNER JOIN RC_DEB ON     
        RC_VD_CONV_RC_DEB_GLOSA.CD_EMP = RC_DEB.CD_EMP AND     
        RC_VD_CONV_RC_DEB_GLOSA.CD_FILIAL = RC_DEB.CD_FILIAL AND    
        RC_VD_CONV_RC_DEB_GLOSA.CD_RC_DEB = RC_DEB.CD_RC_DEB    
 WHERE    
        --RC_DEB.STS_DP = 0    
         RC_DEB.STS_DP in (0,5)   
      
    
    GROUP BY     
RC_VD_CONV_RC_DEB_GLOSA.CD_EMP,     
     RC_VD_CONV_RC_DEB_GLOSA.CD_FILIAL,    
  RC_VD_CONV_RC_DEB_GLOSA.CD_RC_DEB ) Rs ON    
     
 RC_DEB.CD_EMP = Rs.CD_EMP AND    
    RC_DEB.CD_FILIAL = Rs.CD_FILIAL AND    
    RC_DEB.CD_RC_DEB = Rs.CD_RC_DEB    
    
    
    