IF EXISTS (SELECT * FROM SYS.VIEWS
					 WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[V_PG_DESP_RAT_CENTRO_CUSTO]'))
	DROP VIEW [DBO].[V_PG_DESP_RAT_CENTRO_CUSTO]

GO

CREATE VIEW [dbo].[V_PG_DESP_RAT_CENTRO_CUSTO]
AS
     SELECT
            PG_DESP_RAT.CD_EMP
          , PG_DESP_RAT.CD_RAT_DESP
          , PG_DESP_RAT.CD_DESP_REC
          , GLB_DESP_REC.NR_CNT
          , GLB_DESP_REC.DS_DESP_REC
          , PG_CRED.DT_EMIS
          , ISNULL(SUM(CASE
                           WHEN ISNULL(C.VLR_RAT_DESP, 0) > 0 THEN CASE
                                                                       WHEN DUP.VLR_DP > 0 THEN((PG_DESP_RAT.PERC_DESP / 100) * DUP.VLR_DP)
                                                                       ELSE 0
                                                                   END
                           ELSE CASE
                                    WHEN PG_DESP_RAT.VLR_DESP > 0 THEN PG_DESP_RAT.VLR_DESP
                                    ELSE 0
                                END
                       END), 0) AS VLR_DESP
          , SUM(DISTINCT PG_DESP_RAT.PERC_DESP) AS PERC_DESP
          , SUM(DISTINCT(CASE
                             WHEN PG_CRED.VLR_DP_PAGO > 0 THEN((PG_DESP_RAT.PERC_DESP / 100) * PG_CRED.VLR_DP_PAGO)
                             ELSE 0
                         END)) AS VLR_DESP_PAGO
          , PG_DESP_RAT.MES_ANO_REF
          , 'PAGAR' AS TIPO_LANCTO
          , PG_DESP_RAT_PG_CRED.CD_FILIAL
          , CONVERT(VARCHAR(10), PG_DESP_RAT_PG_CRED.NR_DP)+'-'+CONVERT(VARCHAR(10), PG_DESP_RAT_PG_CRED.ANO) AS DOC_ORIGEM
          , C.CD_USU
          , GLB_DESP_REC.RATEIO_CENTRO_CUSTO
          , C.CD_CENTRO_CTB
          , PG_CRED.DT_VENCTO
          , c.VLR_RAT_DESP
     FROM
(
    SELECT
           CD_EMP
         , CD_FILIAL
         , NR_DP
         , ANO
         , DT_EMIS
         , MAX(DT_VENCTO) AS DT_VENCTO
         , SUM(VLR_DP) AS VLR_DP
    FROM
         PG_CRED
    WHERE PG_CRED.STS_DP <> 3
    GROUP BY
             CD_EMP
           , CD_FILIAL
           , NR_DP
           , ANO
           , DT_EMIS
--           , VLR_DP Retirado o agrupamento de valor pois nem sempre os valores das parcelas da duplicata são os mesmos
) AS DUP
LEFT OUTER JOIN
(
    SELECT
           CD_EMP
         , CD_FILIAL
         , NR_DP
         , ANO
         , MIN(DT_EMIS) DT_EMIS
         , MAX(DT_VENCTO) AS DT_VENCTO
         , SUM((VLR_BAIXA + VLR_JUROS_DEB) - (VLR_DESC_DEB + VLR_IMP_DEB)) AS VLR_DP_PAGO
    FROM
         V_PG_CRED
    WHERE V_PG_CRED.STS_DP <> 3
    GROUP BY
             CD_EMP
           , CD_FILIAL
           , NR_DP
           , ANO 
) AS PG_CRED ON DUP.CD_EMP = PG_CRED.CD_EMP
                AND DUP.CD_FILIAL = PG_CRED.CD_FILIAL
                AND DUP.NR_DP = PG_CRED.NR_DP
                AND DUP.ANO = PG_CRED.ANO
INNER JOIN PG_DESP_RAT_PG_CRED ON PG_CRED.CD_EMP = PG_DESP_RAT_PG_CRED.CD_EMP
                                  AND PG_CRED.CD_FILIAL = PG_DESP_RAT_PG_CRED.CD_FILIAL
                                  AND PG_CRED.NR_DP = PG_DESP_RAT_PG_CRED.NR_DP
                                  AND PG_CRED.ANO = PG_DESP_RAT_PG_CRED.ANO
INNER JOIN PG_DESP_RAT ON PG_DESP_RAT_PG_CRED.CD_EMP = PG_DESP_RAT.CD_EMP
                          AND PG_DESP_RAT_PG_CRED.CD_RAT_DESP = PG_DESP_RAT.CD_RAT_DESP
LEFT JOIN PG_DESP_RAT_CTB_CENTRO_CUSTO C ON PG_DESP_RAT.CD_EMP = C.CD_EMP
                                            AND PG_DESP_RAT.CD_RAT_DESP = C.CD_RAT_DESP
INNER JOIN GLB_DESP_REC ON PG_DESP_RAT.CD_EMP = GLB_DESP_REC.CD_EMP
                           AND PG_DESP_RAT.CD_DESP_REC = GLB_DESP_REC.CD_DESP_REC
     GROUP BY
              PG_DESP_RAT.CD_EMP
            , PG_DESP_RAT.CD_RAT_DESP
            , PG_DESP_RAT.CD_DESP_REC
            , GLB_DESP_REC.NR_CNT
            , GLB_DESP_REC.DS_DESP_REC
            , PG_CRED.DT_EMIS
            , PG_DESP_RAT.MES_ANO_REF
            , PG_DESP_RAT_PG_CRED.CD_FILIAL
            , PG_DESP_RAT_PG_CRED.NR_DP
            , C.CD_USU
            , GLB_DESP_REC.RATEIO_CENTRO_CUSTO
            , C.CD_CENTRO_CTB
            , PG_CRED.DT_VENCTO
            , PG_DESP_RAT_PG_CRED.ANO
            , c.VLR_RAT_DESP
     UNION ALL
     SELECT
            PG_DESP_RAT.CD_EMP
          , PG_DESP_RAT.CD_RAT_DESP
          , PG_DESP_RAT.CD_DESP_REC
          , GLB_DESP_REC.NR_CNT
          , GLB_DESP_REC.DS_DESP_REC
          , BC_LANC.DT_LANCTO
          , PG_DESP_RAT.VLR_DESP
          , PG_DESP_RAT.PERC_DESP
          , BC_LANC.VLR_LANC VLR_DESP_PAGO
          , PG_DESP_RAT.MES_ANO_REF
          , 'BANCARIO' AS TIPO_LANCTO
          , PG_DESP_RAT.CD_FILIAL
          , CONVERT(VARCHAR(10), PG_DESP_RAT_BC_LANC.CD_BC_LANC) AS DOC_ORIGEM
          , BC_LANC.CD_USU
          , GLB_DESP_REC.RATEIO_CENTRO_CUSTO
          , C.CD_CENTRO_CTB
          , PG_DESP_RAT.MES_ANO_REF
          , c.VLR_RAT_DESP
     FROM
          PG_DESP_RAT
          INNER JOIN GLB_DESP_REC ON PG_DESP_RAT.CD_EMP = GLB_DESP_REC.CD_EMP
                                     AND PG_DESP_RAT.CD_DESP_REC = GLB_DESP_REC.CD_DESP_REC
          INNER JOIN PG_DESP_RAT_BC_LANC ON PG_DESP_RAT.CD_EMP = PG_DESP_RAT_BC_LANC.CD_EMP
                                            AND PG_DESP_RAT.CD_RAT_DESP = PG_DESP_RAT_BC_LANC.CD_RAT_DESP
          LEFT JOIN PG_DESP_RAT_CTB_CENTRO_CUSTO C ON PG_DESP_RAT.CD_EMP = C.CD_EMP
                                                      AND PG_DESP_RAT.CD_RAT_DESP = C.CD_RAT_DESP
          INNER JOIN BC_LANC ON PG_DESP_RAT_BC_LANC.CD_EMP = BC_LANC.CD_EMP
                                AND PG_DESP_RAT_BC_LANC.CD_BC_LANC = BC_LANC.CD_BC_LANC
     UNION ALL
     SELECT
            PG_DESP_RAT.CD_EMP
          , PG_DESP_RAT.CD_RAT_DESP
          , PG_DESP_RAT.CD_DESP_REC
          , GLB_DESP_REC.NR_CNT
          , GLB_DESP_REC.DS_DESP_REC
          , RC_CLI_CONV_GLOSA_PG_DESP_RAT.DT_LANC
          , PG_DESP_RAT.VLR_DESP
          , PG_DESP_RAT.PERC_DESP
          , PG_DESP_RAT.VLR_DESP VLR_DESP_PAGO
          , PG_DESP_RAT.MES_ANO_REF
          , 'GLOSA CONVENIO' AS TIPO_LANCTO
          , PG_DESP_RAT.CD_FILIAL
          , '0' AS DOC_ORIGEM
          , RC_CLI_CONV_GLOSA_PG_DESP_RAT.CD_USU
          , GLB_DESP_REC.RATEIO_CENTRO_CUSTO
          , C.CD_CENTRO_CTB
          , PG_DESP_RAT.MES_ANO_REF
          , c.VLR_RAT_DESP
     FROM
          RC_CLI_CONV_GLOSA_PG_DESP_RAT
          INNER JOIN PG_DESP_RAT ON RC_CLI_CONV_GLOSA_PG_DESP_RAT.CD_EMP = PG_DESP_RAT.CD_EMP
                                    AND RC_CLI_CONV_GLOSA_PG_DESP_RAT.CD_RAT_DESP = PG_DESP_RAT.CD_RAT_DESP
          LEFT JOIN PG_DESP_RAT_CTB_CENTRO_CUSTO C ON PG_DESP_RAT.CD_EMP = C.CD_EMP
                                                      AND PG_DESP_RAT.CD_RAT_DESP = C.CD_RAT_DESP
          INNER JOIN GLB_DESP_REC ON PG_DESP_RAT.CD_EMP = GLB_DESP_REC.CD_EMP
                                     AND PG_DESP_RAT.CD_DESP_REC = GLB_DESP_REC.CD_DESP_REC
     UNION ALL
     SELECT
            PG_DESP_RAT.CD_EMP
          , PG_DESP_RAT.CD_RAT_DESP
          , PG_DESP_RAT.CD_DESP_REC
          , GLB_DESP_REC.NR_CNT
          , GLB_DESP_REC.DS_DESP_REC
          , PG_DESP_RAT_PDV_VD_FECHA_MOV.DT_MOV
          , PG_DESP_RAT.VLR_DESP
          , PG_DESP_RAT.PERC_DESP
          , PG_DESP_RAT.VLR_DESP VLR_DESP_PAGO
          , PG_DESP_RAT.MES_ANO_REF
          , 'FECHAMENTO DE CAIXA' AS TIPO_LANCTO
          , PG_DESP_RAT_PDV_VD_FECHA_MOV.CD_FILIAL
          , '0' AS DOC_ORIGEM
          , 0 AS CD_USU
          , GLB_DESP_REC.RATEIO_CENTRO_CUSTO
          , C.CD_CENTRO_CTB
          , PG_DESP_RAT.MES_ANO_REF
          , c.VLR_RAT_DESP
     FROM
          PG_DESP_RAT_PDV_VD_FECHA_MOV
          INNER JOIN PG_DESP_RAT ON PG_DESP_RAT_PDV_VD_FECHA_MOV.CD_EMP = PG_DESP_RAT.CD_EMP
                                    AND PG_DESP_RAT_PDV_VD_FECHA_MOV.CD_RAT_DESP = PG_DESP_RAT.CD_RAT_DESP
          LEFT JOIN PG_DESP_RAT_CTB_CENTRO_CUSTO C ON PG_DESP_RAT.CD_EMP = C.CD_EMP
                                                      AND PG_DESP_RAT.CD_RAT_DESP = C.CD_RAT_DESP
          INNER JOIN GLB_DESP_REC ON PG_DESP_RAT.CD_EMP = GLB_DESP_REC.CD_EMP
                                     AND PG_DESP_RAT.CD_DESP_REC = GLB_DESP_REC.CD_DESP_REC;