ALTER PROCEDURE P_EST_ARV_MERC_MIX_ANALISE_ESTOQUE
AS 
--EXEC P_EST_ARV_MERC_MIX_ANALISE_ESTOQUE
     DECLARE @MES INT;
     DECLARE @ANO INT;
     DECLARE @DT_ATUAL DATETIME;
     SET @DT_ATUAL = GETDATE();
     SET @MES = MONTH(@DT_ATUAL);
     SET @ANO = YEAR(@DT_ATUAL);
--LIMPANDO REGISTROS

delete from  EST_ARV_MERC_MIX_ANALISE_ESTOQUE		 where ano=@ano and mes = @mes;
delete from  EST_ARV_MERC_MIX_ANALISE_ESTOQUE_FILIAL where ano=@ano and mes = @mes;


--INSERINDO REGISTROS
     INSERT INTO EST_ARV_MERC_MIX_ANALISE_ESTOQUE
(
            CD_EMP
          , CD_PROD
          , ANO
          , MES
          , QTDE_ESTOQUE_REDE
          , QT_PEND_ENT_REDE
          , QT_PEND_SAIDA_REDE
          , QTDE_MEDIAF
          , QTDE_MEDIAP
          , CD_SIGLA
          , DT_CAD
          , VLR_TABELA
)
            SELECT
                   CD_EMP
                 , CD_PROD
                 , ANO
                 , MES
                 , SUM(QTDE_ESTOQUE_REDE)
                 , SUM(QT_PEND_ENT_REDE)
                 , SUM(QT_PEND_SAIDA_REDE)
                 , SUM(QTDE_MEDIAF_REDE)
                 , SUM(QTDE_MEDIAP_REDE)
                 , SUM(CD_SIGLA)
                 , DT_ATUAL
                 , SUM(VLR_TABELA)
            FROM
(
    SELECT
           EST.CD_EMP
         , EST.CD_PROD
         , @ANO ANO
         , @MES MES
         , QT_EST QTDE_ESTOQUE_REDE
         , QT_PEND_ENT QT_PEND_ENT_REDE
         , QT_PEND_SAI QT_PEND_SAIDA_REDE
         , 0 AS QTDE_MEDIAF_REDE
         , 0 AS QTDE_MEDIAP_REDE
         , 0 AS CD_SIGLA
         , @DT_ATUAL AS DT_ATUAL
         , 0 AS VLR_TABELA
    FROM
         V_EST_PROD_CPL_ESTOQUE EST
         INNER JOIN PRC_FILIAL FIL ON EST.CD_EMP = FIL.CD_EMP
                                      AND EST.CD_FILIAL = FIL.CD_FILIAL
         INNER JOIN EST_PROD P ON EST.CD_EMP = P.CD_EMP
                                  AND EST.CD_PROD = P.CD_PROD
    WHERE FIL.FLAG_FILIAL_NAO_PARTICP_MIX = 0
          AND FIL.CD_EMP = 1
    UNION ALL
    SELECT
           f.CD_EMP
         , f.CD_PROD
         , @ANO
         , @MES
         , 0 AS QTDE_ESTOQUE_REDE
         , 0 AS QT_PEND_ENT_REDE
         , 0 AS QT_PEND_SAIDA_REDE
         , QTDE_MEDIAF AS QTDE_MEDIAF_REDE
         , 0 AS QTDE_MEDIAP_REDE
         , 0 AS CD_SIGLA
         , @DT_ATUAL
         , 0 AS VLR_TABELA
    FROM
         EST_PROD_CPL_MEDIAF F
         INNER JOIN PRC_FILIAL FIL ON F.CD_EMP = FIL.CD_EMP
                                      AND F.CD_FILIAL = FIL.CD_FILIAL
         INNER JOIN EST_PROD P ON F.CD_EMP = P.CD_EMP
                                  AND F.CD_PROD = P.CD_PROD
         LEFT OUTER JOIN PRC_FILIAL_DEPOSITO d ON F.CD_EMP = d.CD_EMP
                                                  AND F.CD_FILIAL = d.CD_FILIAL
    WHERE FIL.FLAG_FILIAL_NAO_PARTICP_MIX = 0
          AND FIL.CD_EMP = 1
          AND d.CD_FILIAL IS NULL
    UNION ALL
    SELECT
           P.CD_EMP
         , P.CD_PROD
         , @ANO
         , @MES
         , 0 AS QTDE_ESTOQUE_REDE
         , 0 AS QT_PEND_ENT_REDE
         , 0 AS QT_PEND_SAIDA_REDE
         , 0 AS QTDE_MEDIAF_REDE
         , QTDE_MEDIAP AS QTDE_MEDIAP_REDE
         , 0 AS CD_SIGLA
         , @DT_ATUAL
         , 0 AS VLR_TABELA
    FROM
         EST_PROD_CPL_MEDIAP P
         INNER JOIN PRC_FILIAL FIL ON P.CD_EMP = FIL.CD_EMP
                                      AND P.CD_FILIAL = FIL.CD_FILIAL
         LEFT OUTER JOIN PRC_FILIAL_DEPOSITO d ON P.CD_EMP = d.CD_EMP
                                                  AND P.CD_FILIAL = d.CD_FILIAL
         INNER JOIN EST_PROD PR ON P.CD_EMP = PR.CD_EMP
                                   AND P.CD_PROD = PR.CD_PROD
    WHERE FIL.FLAG_FILIAL_NAO_PARTICP_MIX = 0
          AND FIL.CD_EMP = 1
          AND d.CD_FILIAL IS NULL
    UNION ALL
    SELECT
           C.CD_EMP
         , C.CD_PROD
         , @ANO
         , @MES
         , 0 AS QTDE_ESTOQUE_REDE
         , 0 AS QT_PEND_ENT_REDE
         , 0 AS QT_PEND_SAIDA_REDE
         , 0 AS QTDE_MEDIAF_REDE
         , 0 AS QTDE_MEDIAP_REDE
         , CD_SIGLA
         , @DT_ATUAL
         , VLR_TABELA
    FROM
         EST_PROD_CPL C
         INNER JOIN PRC_FILIAL FIL ON C.CD_EMP = FIL.CD_EMP
                                      AND C.CD_FILIAL = FIL.CD_FILIAL
         INNER JOIN PRC_FILIAL_DEPOSITO d ON C.CD_EMP = d.CD_EMP
                                             AND C.CD_FILIAL = d.CD_FILIAL
         INNER JOIN EST_PROD_PRECO ON C.CD_EMP = EST_PROD_PRECO.CD_EMP
                                      AND C.CD_FILIAL = EST_PROD_PRECO.CD_FILIAL
                                      AND C.CD_PROD = EST_PROD_PRECO.CD_PROD
         INNER JOIN EST_PROD P ON C.CD_EMP = P.CD_EMP
                                  AND C.CD_PROD = P.CD_PROD
    WHERE FIL.FLAG_FILIAL_NAO_PARTICP_MIX = 0
          AND FIL.CD_EMP = 1
) RS
            GROUP BY
                     CD_EMP
                   , CD_PROD
                   , ANO
                   , MES
                   , DT_ATUAL;
     INSERT INTO EST_ARV_MERC_MIX_ANALISE_ESTOQUE_FILIAL
(
            CD_EMP
          , CD_FILIAL
          , CD_PROD
          , ANO
          , MES
          , QTDE_ESTOQUE_REDE
          , QT_PEND_ENT_REDE
          , QT_PEND_SAIDA_REDE
          , QTDE_MEDIAF
          , QTDE_MEDIAP
          , CD_SIGLA
          , DT_CAD
          , VLR_TABELA
)
            SELECT
                   CD_EMP
                 , CD_FILIAL
                 , CD_PROD
                 , ANO
                 , MES
                 , SUM(QTDE_ESTOQUE_REDE)
                 , SUM(QT_PEND_ENT_REDE)
                 , SUM(QT_PEND_SAIDA_REDE)
                 , SUM(QTDE_MEDIAF_REDE)
                 , SUM(QTDE_MEDIAP_REDE)
                 , SUM(CD_SIGLA)
                 , DT_ATUAL
                 , SUM(VLR_TABELA)
            FROM
(
    SELECT
           EST.CD_EMP
         , EST.CD_FILIAL
         , EST.CD_PROD
         , @ANO ANO
         , @MES MES
         , QT_EST QTDE_ESTOQUE_REDE
         , QT_PEND_ENT QT_PEND_ENT_REDE
         , QT_PEND_SAI QT_PEND_SAIDA_REDE
         , 0 AS QTDE_MEDIAF_REDE
         , 0 AS QTDE_MEDIAP_REDE
         , 0 AS CD_SIGLA
         , @DT_ATUAL AS DT_ATUAL
         , 0 AS VLR_TABELA
    FROM
         V_EST_PROD_CPL_ESTOQUE EST
         INNER JOIN PRC_FILIAL FIL ON EST.CD_EMP = FIL.CD_EMP
                                      AND EST.CD_FILIAL = FIL.CD_FILIAL
         INNER JOIN EST_PROD P ON EST.CD_EMP = P.CD_EMP
                                  AND EST.CD_PROD = P.CD_PROD
    WHERE FIL.FLAG_FILIAL_NAO_PARTICP_MIX = 0
          AND FIL.CD_EMP = 1
    UNION ALL
    SELECT
           f.CD_EMP
         , F.CD_FILIAL
         , f.CD_PROD
         , @ANO
         , @MES
         , 0 AS QTDE_ESTOQUE_REDE
         , 0 AS QT_PEND_ENT_REDE
         , 0 AS QT_PEND_SAIDA_REDE
         , QTDE_MEDIAF AS QTDE_MEDIAF_REDE
         , 0 AS QTDE_MEDIAP_REDE
         , 0 AS CD_SIGLA
         , @DT_ATUAL
         , 0 AS VLR_TABELA
    FROM
         EST_PROD_CPL_MEDIAF F
         INNER JOIN PRC_FILIAL FIL ON F.CD_EMP = FIL.CD_EMP
                                      AND F.CD_FILIAL = FIL.CD_FILIAL
         INNER JOIN EST_PROD P ON F.CD_EMP = P.CD_EMP
                                  AND F.CD_PROD = P.CD_PROD
         LEFT OUTER JOIN PRC_FILIAL_DEPOSITO d ON F.CD_EMP = d.CD_EMP
                                                  AND F.CD_FILIAL = d.CD_FILIAL
    WHERE FIL.FLAG_FILIAL_NAO_PARTICP_MIX = 0
          AND FIL.CD_EMP = 1
          AND d.CD_FILIAL IS NULL
    UNION ALL
    SELECT
           P.CD_EMP
         , P.CD_FILIAL
         , P.CD_PROD
         , @ANO
         , @MES
         , 0 AS QTDE_ESTOQUE_REDE
         , 0 AS QT_PEND_ENT_REDE
         , 0 AS QT_PEND_SAIDA_REDE
         , 0 AS QTDE_MEDIAF_REDE
         , QTDE_MEDIAP AS QTDE_MEDIAP_REDE
         , 0 AS CD_SIGLA
         , @DT_ATUAL
         , 0 AS VLR_TABELA
    FROM
         EST_PROD_CPL_MEDIAP P
         INNER JOIN PRC_FILIAL FIL ON P.CD_EMP = FIL.CD_EMP
                                      AND P.CD_FILIAL = FIL.CD_FILIAL
         LEFT OUTER JOIN PRC_FILIAL_DEPOSITO d ON P.CD_EMP = d.CD_EMP
                                                  AND P.CD_FILIAL = d.CD_FILIAL
         INNER JOIN EST_PROD PR ON P.CD_EMP = PR.CD_EMP
                                   AND P.CD_PROD = PR.CD_PROD
    WHERE FIL.FLAG_FILIAL_NAO_PARTICP_MIX = 0
          AND FIL.CD_EMP = 1
          AND d.CD_FILIAL IS NULL
    UNION ALL
    SELECT
           C.CD_EMP
         , C.CD_FILIAL
         , C.CD_PROD
         , @ANO
         , @MES
         , 0 AS QTDE_ESTOQUE_REDE
         , 0 AS QT_PEND_ENT_REDE
         , 0 AS QT_PEND_SAIDA_REDE
         , 0 AS QTDE_MEDIAF_REDE
         , 0 AS QTDE_MEDIAP_REDE
         , CD_SIGLA
         , @DT_ATUAL
         , VLR_TABELA
    FROM
         EST_PROD_CPL C
         INNER JOIN PRC_FILIAL FIL ON C.CD_EMP = FIL.CD_EMP
                                      AND C.CD_FILIAL = FIL.CD_FILIAL
         INNER JOIN PRC_FILIAL_DEPOSITO d ON C.CD_EMP = d.CD_EMP
                                             AND C.CD_FILIAL = d.CD_FILIAL
         INNER JOIN EST_PROD_PRECO ON C.CD_EMP = EST_PROD_PRECO.CD_EMP
                                      AND C.CD_FILIAL = EST_PROD_PRECO.CD_FILIAL
                                      AND C.CD_PROD = EST_PROD_PRECO.CD_PROD
         INNER JOIN EST_PROD P ON C.CD_EMP = P.CD_EMP
                                  AND C.CD_PROD = P.CD_PROD
    WHERE FIL.FLAG_FILIAL_NAO_PARTICP_MIX = 0
          AND FIL.CD_EMP = 1
) RS
            GROUP BY
                     CD_EMP
                   , CD_FILIAL
                   , CD_PROD
                   , ANO
                   , MES
                   , DT_ATUAL;