--SELECT * FROM V_PG_DEB
--SELECT TOP 1 * FROM [V_PG_DEB]
/****** Object:  View [dbo].[V_PG_DEB]    Script Date: 06/08/2020 ******/
IF EXISTS
(    SELECT * FROM   sys.views  WHERE object_id = OBJECT_ID(N'[dbo].[V_PG_DEB]')
)
    DROP VIEW
         [dbo].[V_PG_DEB];
GO

CREATE VIEW [dbo].[V_PG_DEB]
WITH ENCRYPTION
AS
     SELECT dbo.PG_CRED.CD_EMP
          , dbo.PG_CRED.CD_FILIAL
          , dbo.PRC_FILIAL.NM_FANT AS NM_FANT_FIL
          , dbo.PG_CRED.NR_DP
          , dbo.PG_CRED.ANO
          , dbo.PG_CRED.PARC
          , dbo.PG_CRED.CD_FORN
          , dbo.PG_FORN.NM_FANT
          , dbo.PG_FORN.RZ_FORN
          , ISNULL(dbo.V_PG_CRED_VLR_PGTO.VLR_SLD, PG_CRED.VLR_DP + dbo.PG_CRED.VLR_DESP_OUTR) AS VLR_SLD
          , ISNULL(ISNULL(dbo.V_PG_CRED_IMPOSTO.VLR_IMP, V_PG_CRED_VLR_PGTO.VLR_IMPOSTO), 0) AS VLR_IMP
          , dbo.PG_CRED.DT_VENCTO
          , dbo.PG_CRED.NR_TIT
          , PG_CRED.VLR_DP AS VLR_DP
          , ISNULL(dbo.V_PG_CRED_VLR_PGTO.VLR_PGTO, 0) AS VLR_BAIXA
          , dbo.PG_CRED.STS_DP
          , dbo.V_PG_CRED_DT_EMIS.DT_EMI_NF
          , dbo.PG_CRED.DT_EMIS
          , ISNULL(dbo.PG_CRED.VLR_JUROS, 0) AS VLR_JUROS
          , CASE  WHEN STS_DP in (0,5,6) 
            THEN ISNULL(dbo.PG_CRED.VLR_DESC, 0)
            ELSE 0
 
           END AS VLR_DESC
          , dbo.CTB_HIST.DS_HIST
          , dbo.PG_CRED.CD_PG_CRED
          , dbo.PG_CRED.NR_BOLETA
          , CASE
                WHEN dbo.V_PG_CRED_VLR_PGTO.VLR_SLD > 0
                THEN 0
                ELSE dbo.PG_CRED.VLR_DESP_OUTR
            END AS VLR_DESP_OUTR
          , dbo.PG_CRED.COB
          , dbo.V_PG_CRED_VLR_PGTO.DT_PAG
          , ISNULL(dbo.V_PG_CRED_VLR_PGTO.VLR_DESC, 0) AS VLR_DESC_DEB
          , ISNULL(dbo.V_PG_CRED_VLR_PGTO.VLR_JUROS, 0) AS VLR_JUROS_DEB
          , ISNULL(dbo.V_PG_CRED_VLR_PGTO.VLR_IMPOSTO, 0) AS VLR_IMPOSTO_DEB
          , ISNULL(dbo.V_PG_CRED_VLR_PGTO.VLR_SLD, 0) AS VLR_SLD_DEB
          , ISNULL(dbo.V_PG_CRED_VLR_PGTO.VLR_PGTO, 0) AS VLR_PGTO_DEB
          , dbo.PG_CRED.CD_BC

          , dbo.PG_CRED.DT_CAD
          , dbo.PG_CRED.CD_USU
          , dbo.PG_FORN.CD_TP_FORN
          , CASE
                WHEN ISNULL(PRC_FILIAL_PG_FORN.CD_FILIAL, 0) > 0
                THEN 1
                ELSE 0
            END AS GRP_ECONOMICO
          , CASE
                WHEN ISNULL(dbo.PRC_FILIAL_PG_FORN.CD_FILIAL, 0) > 0
                THEN CD_GRP_ECON
                ELSE 0
            END AS GRP_ECON_ORIG
          , dbo.V_PG_CRED_DT_EMIS.NR_NF
          , ISNULL(PG_CRED.VLR_DEVOLUCAO, 0) VLR_DEVOLUCAO
          , ISNULL(V_PG_CRED_VLR_PGTO.VLR_DEVOLUCAO, 0) VLR_DEVOLUCAO_DEB
          , ISNULL(dbo.PG_CRED_CD_BARRAS.CD_BARRAS_BOLETA,'') AS CD_BARRAS_BOLETA
          , V_PG_CRED_VLR_PGTO.CD_DP_LANC
          , PG_CRED.TP_DP
          , PG_CRED_CONTROLE.CD_PG_CRED_CONTROLE AS CD_CTR
          , ISNULL(FLAG_NAO_EXP_PROVISIONAMENTO_CONTABILIDADE, 0) AS FLAG_NAO_EXP_PROVISIONAMENTO_CONTABILIDADE
          , 0 AS TIPO_LANCTO
		  ,PG_CRED.ORIG_LANC
     FROM
          dbo.PG_CRED
          INNER JOIN dbo.PG_FORN
          ON dbo.PG_CRED.CD_EMP = dbo.PG_FORN.CD_EMP
             AND dbo.PG_CRED.CD_FORN = dbo.PG_FORN.CD_FORN
          INNER JOIN dbo.PRC_FILIAL
          ON dbo.PG_CRED.CD_EMP = dbo.PRC_FILIAL.CD_EMP
             AND dbo.PG_CRED.CD_FILIAL = dbo.PRC_FILIAL.CD_FILIAL
          LEFT OUTER JOIN dbo.PRC_FILIAL_PG_FORN
          ON dbo.PG_FORN.CD_EMP = dbo.PRC_FILIAL_PG_FORN.CD_EMP
             AND dbo.PG_FORN.CD_FORN = dbo.PRC_FILIAL_PG_FORN.CD_FORN
          LEFT OUTER JOIN PRC_GRP_ECON_PRC_FILIAL
     
     ON PRC_FILIAL_PG_FORN.CD_EMP = PRC_GRP_ECON_PRC_FILIAL.CD_EMP
             AND PRC_FILIAL_PG_FORN.CD_FILIAL = PRC_GRP_ECON_PRC_FILIAL.CD_FILIAL
          LEFT OUTER JOIN
     (
         SELECT CD_EMP
              , CD_FILIAL
         FROM PRC_FILIAL

         WHERE FLAG_OPERADOR_LOG = 0
     ) FILIAL
          ON PRC_FILIAL_PG_FORN.CD_EMP = FILIAL.CD_EMP
             AND PRC_FILIAL_PG_FORN.CD_FILIAL = FILIAL.CD_FILIAL
          LEFT OUTER JOIN dbo.V_PG_CRED_IMPOSTO
          ON dbo.PG_CRED.CD_EMP = dbo.V_PG_CRED_IMPOSTO.CD_EMP
             AND dbo.PG_CRED.CD_FILIAL = dbo.V_PG_CRED_IMPOSTO.CD_FILIAL
             AND dbo.PG_CRED.CD_PG_CRED = dbo.V_PG_CRED_IMPOSTO.CD_PG_CRED
          LEFT OUTER JOIN dbo.V_PG_CRED_DT_EMIS
          ON dbo.PG_CRED.CD_EMP = dbo.V_PG_CRED_DT_EMIS.CD_EMP
             AND dbo.PG_CRED.CD_FILIAL = dbo.V_PG_CRED_DT_EMIS.CD_FILIAL
             AND dbo.PG_CRED.CD_PG_CRED = dbo.V_PG_CRED_DT_EMIS.CD_PG_CRED
          LEFT OUTER JOIN dbo.V_PG_CRED_VLR_PGTO
          ON dbo.PG_CRED.CD_EMP = dbo.V_PG_CRED_VLR_PGTO.CD_EMP
             AND dbo.PG_CRED.CD_FILIAL = dbo.V_PG_CRED_VLR_PGTO.CD_FILIAL
             AND dbo.PG_CRED.CD_PG_CRED = dbo.V_PG_CRED_VLR_PGTO.CD_PG_CRED
          LEFT OUTER JOIN dbo.CTB_HIST
          ON dbo.PG_CRED.CD_EMP = dbo.CTB_HIST.CD_EMP
             AND dbo.PG_CRED.CD_HIST = dbo.CTB_HIST.CD_HIST
		  INNER JOIN PG_CRED_CONTROLE ON
          PG_CRED.CD_EMP = dbo.PG_CRED_CONTROLE.CD_EMP
             AND PG_CRED.CD_FILIAL = dbo.PG_CRED_CONTROLE.CD_FILIAL
           
  AND PG_CRED.CD_PG_CRED = dbo.PG_CRED_CONTROLE.CD_PG_CRED
	LEFT  JOIN dbo.PG_CRED_CD_BARRAS ON 
	dbo.PG_CRED.CD_EMP = dbo.PG_CRED_CD_BARRAS.CD_EMP AND 
    dbo.PG_CRED.CD_FILIAL = dbo.PG_CRED_CD_BARRAS.CD_FILIAL AND 
    dbo.PG_CRED.CD_PG_CRED = dbo.PG_CRED_CD_BARRAS.CD_PG_CRED 		    


