
IF   EXISTS
(
    SELECT 1
    FROM SYS.PROCEDURES
    WHERE NAME = 'P_CONSULTA_PEDIDOS_DEMANDA'
)
    BEGIN
        DROP PROCEDURE P_CONSULTA_PEDIDOS_DEMANDA;
    END;
GO
CREATE PROCEDURE P_CONSULTA_PEDIDOS_DEMANDA
(@CD_EMP     INT,
 @CD_GRP_PED INT
)
AS 
--P_CONSULTA_PEDIDOS_DEMANDA 1,33966
--DECLARE @Cd_Emp INT= 1, @cd_GRP_PED INT= 33966;

--DROP TABLE #TmpDemanda
     CREATE TABLE #TmpDemanda
(CD_EMP            INT,
 CD_FILIAL         INT,
 NM_FANT           VARCHAR(100),
 CD_PED_SOL        INT,
 DT_PED            DATE,
 DT_DEMANDA        DATE,
 CD_FORN           INT,
 RZ_FORN           VARCHAR(50),
 ORD_ENV           INT,
 QTDE_DEMANDA_FORN INT,
 QTDE_NAO_ATENDIDO INT,
 VLR_DEMANDA       MONEY,
 ST_ENVIADO        VARCHAR(3),
 SIT_ATENDIDO      VARCHAR(20),
 QTDE_ATENDIDO     INT,
 VLR_ATENDIDO      MONEY,
 CD_PED_FORN       INT,
 CD_PED            INT,
 CD_MSG_FORN       VARCHAR(6),
 DS_MSG_FORN       VARCHAR(150),
 CD_CTR_ENV        INT,
 ORDMELHORCOND     INT
);
     INSERT INTO #TmpDemanda
            SELECT @cd_Emp CD_EMP,
                   PRC_FILIAL.CD_FILIAL,
                   PRC_FILIAL.NM_FANT,
                   EST_PED_ELTR.CD_PED_SOL,
                   EST_PED_ELTR.DT_PED,
                   EST_PED_ELTR.HORA_DEMANDA AS DT_DEMANDA,
                   ISNULL(PG_FORN.CD_FORN, 0) AS CD_FORN,
                   ISNULL(PG_FORN.RZ_FORN, 'NÃO ENVIADO A FORNECEDOR') AS RZ_FORN,
                   ISNULL(EST_PED_ELTR_FORN.ORD_ENV, 0) AS ORD_ENV,
                   0 AS QTDE_DEMANDA_FORN, --ISNULL(EST_PED_ELTR_FORN_IT.QT_IT_ENV, 0) AS QTDE_DEMANDA_FORN, 
                   0 AS QTDE_NAO_ATENDIDO, --ISNULL(EST_PED_ELTR_FORN_IT.QT_IT_ENV, 0) - SUM(ISNULL(EST_PED_CP_IT.QT_IT, 0)) AS QTDE_NAO_ATENDIDO, 
                   0 AS VLR_DEMANDA, --(ISNULL(EST_PED_ELTR_FORN_IT.QT_IT_ENV, 0) * EST_PED_ELTR_IT.VLR_PROD) AS VLR_DEMANDA, 
                   'NAO' AS ST_ENVIADO, --(CASE WHEN ISNULL(EST_PED_ELTR_FORN_IT.CD_EMP, 0) = 0 THEN 'NÃO' ELSE 'SIM' END) AS ST_ENVIADO, 
                   'NAO ATENDIDO' AS SIT_ATENDIDO,		
	 --(CASE
	 --     WHEN SUM(ISNULL(EST_PED_CP_IT.QT_IT, 0)) = 0
	 --     THEN 'NAO ATENDIDO'
	 --     WHEN ISNULL(EST_PED_ELTR_FORN_IT.QT_IT_ENV, 0) - SUM(ISNULL(EST_PED_CP_IT.QT_IT, 0)) > 0
	 --     THEN 'ATENDIDO PARCIAL'
	 --     ELSE 'ATENDIDO TOTAL'
	 -- END) AS SIT_ATENDIDO, 
                   0 AS QTDE_ATENDIDO, --    SUM(ISNULL(EST_PED_CP_IT.QT_IT, 0)) AS QTDE_ATENDIDO, 
                   0 AS VLR_ATENDIDO, -- SUM(ISNULL(EST_PED_CP_IT.QT_IT, 0) * ISNULL(EST_PED_CP_IT.VLR_IT, 0)) AS VLR_ATENDIDO, 
                   0 AS CD_PED_FORN, -- ISNULL(EST_PED_CP.CD_PED_FORN, 0) AS CD_PED_FORN, 
                   0 AS CD_PED, --ISNULL(EST_PED_ELTR_FORN_PED_CP.CD_PED, 0) AS CD_PED,  
                   '0000' AS CD_MSG_FORN, --ISNULL(R.CD_MSG_FORN, '0000') AS CD_MSG_FORN, 
                   'NAO INFORMADO' AS DS_MSG_FORN, --ISNULL(R.DS_MSG_FORN, 'NAO INFORMADO') AS DS_MSG_FORN, 
                   ISNULL(E.CD_CTR_ENV, 0) AS CD_CTR_ENV,
                   ISNULL(E.ORDMELHORCOND, 0) AS ORDMELHORCOND
            FROM EST_PED_ELTR
                 INNER JOIN EST_PED_ELTR_FORN ON EST_PED_ELTR_FORN.CD_EMP = EST_PED_ELTR.CD_EMP
                                                 AND EST_PED_ELTR_FORN.CD_PED_SOL = EST_PED_ELTR.CD_PED_SOL
                 INNER JOIN PG_FORN ON PG_FORN.CD_EMP = EST_PED_ELTR_FORN.CD_EMP
                                       AND PG_FORN.CD_FORN = EST_PED_ELTR_FORN.CD_FORN
                 INNER JOIN EST_PED_ELTR_FORN_CTR_ENV E ON EST_PED_ELTR_FORN.CD_EMP = E.CD_EMP
                                                           AND EST_PED_ELTR_FORN.CD_PED_SOL = E.CD_PED_SOL
                                                           AND EST_PED_ELTR_FORN.CD_FORN = E.CD_FORN
                                                           AND EST_PED_ELTR_FORN.ORD_ENV = E.ORD_ENV
                 INNER JOIN PRC_FILIAL ON PRC_FILIAL.CD_EMP = EST_PED_ELTR.CD_EMP
                                          AND PRC_FILIAL.CD_FILIAL = EST_PED_ELTR.CD_FIL_ENTR
                 INNER JOIN EST_PED_ELTR_GRP_PED_EST_PED_ELTR GRP_PED ON EST_PED_ELTR.CD_EMP = GRP_PED.CD_EMP
                                                                         AND EST_PED_ELTR.CD_PED_SOL = GRP_PED.CD_PED_SOL
            WHERE GRP_PED.CD_EMP = @cd_Emp
                  AND GRP_PED.CD_GRP_PED = @cd_GRP_PED;
     WITH CTE_UPDATE
          AS (SELECT D.CD_EMP,
                     D.CD_FILIAL,
                     D.CD_PED_SOL,
                     EST_PED_ELTR_FORN_IT.QT_IT_ENV AS QTDE_DEMANDA_FORN,
                     (EST_PED_ELTR_FORN_IT.QT_IT_ENV * EST_PED_ELTR_IT.VLR_PROD) AS VLR_DEMANDA,
                     'SIM' AS ST_ENVIADO,
                     CASE
                         WHEN EST_PED_ELTR_FORN_IT.QT_IT_ENV - SUM(EST_PED_CP_IT.QT_IT) > 0
                         THEN 'ATENDIDO PARCIAL'
                         ELSE 'ATENDIDO TOTAL'
                     END AS SIT_ATENDIDO,
                     SUM(EST_PED_CP_IT.QT_IT) AS QTDE_ATENDIDO,
                     SUM(EST_PED_CP_IT.QT_IT * EST_PED_CP_IT.VLR_IT) AS VLR_ATENDIDO,
                     EST_PED_CP.CD_PED_FORN AS CD_PED_FORN,
                     EST_PED_ELTR_FORN_PED_CP.CD_PED AS CD_PED,
                     ISNULL(R.CD_MSG_FORN, '0000') AS CD_MSG_FORN,
                     ISNULL(R.DS_MSG_FORN, 'NAO INFORMADO') AS DS_MSG_FORN
              FROM #TmpDemanda D
                   INNER JOIN EST_PED_ELTR_IT ON D.CD_EMP = EST_PED_ELTR_IT.CD_EMP
                                                 AND D.CD_PED_SOL = EST_PED_ELTR_IT.CD_PED_SOL
                   INNER JOIN EST_PED_ELTR_FORN_IT ON EST_PED_ELTR_FORN_IT.CD_EMP = D.CD_EMP
                                                      AND EST_PED_ELTR_FORN_IT.CD_PED_SOL = D.CD_PED_SOL
                                                      AND EST_PED_ELTR_FORN_IT.CD_FORN = D.CD_FORN
                                                      AND EST_PED_ELTR_FORN_IT.ORD_ENV = D.ORD_ENV
                                                      AND EST_PED_ELTR_FORN_IT.CD_PROD = EST_PED_ELTR_IT.CD_PROD
                   INNER JOIN EST_PED_ELTR_FORN_PED_CP ON EST_PED_ELTR_FORN_PED_CP.CD_EMP = D.CD_EMP
                                                          AND EST_PED_ELTR_FORN_PED_CP.CD_PED_SOL = D.CD_PED_SOL
                                                          AND EST_PED_ELTR_FORN_PED_CP.CD_FORN = D.CD_FORN
                                                          AND EST_PED_ELTR_FORN_PED_CP.ORD_ENV = D.ORD_ENV
                   INNER JOIN EST_PED_CP_IT ON EST_PED_CP_IT.CD_EMP = EST_PED_ELTR_FORN_IT.CD_EMP
                                               AND EST_PED_CP_IT.CD_PED = EST_PED_ELTR_FORN_PED_CP.CD_PED
                                               AND EST_PED_CP_IT.CD_FILIAL = EST_PED_ELTR_FORN_PED_CP.CD_FIL_ENTR
                                               AND EST_PED_CP_IT.CD_PROD = EST_PED_ELTR_FORN_IT.CD_PROD
                   INNER JOIN EST_PED_CP ON EST_PED_CP.CD_EMP = EST_PED_CP_IT.CD_EMP
                                            AND EST_PED_CP.CD_PED = EST_PED_CP_IT.CD_PED
                                            AND EST_PED_CP.CD_FILIAL = EST_PED_CP_IT.CD_FILIAL
                   LEFT JOIN EST_PED_ELTR_FORN_FALTAS F ON EST_PED_ELTR_FORN_IT.CD_EMP = F.CD_EMP
                                                           AND EST_PED_ELTR_FORN_IT.CD_FORN = F.CD_FORN
                                                           AND EST_PED_ELTR_FORN_IT.CD_PED_SOL = F.CD_PED_SOL
                                                           AND EST_PED_ELTR_FORN_IT.CD_PROD = F.CD_PROD
                                                           AND EST_PED_ELTR_FORN_IT.ORD_ENV = F.ORD_ENV
                   LEFT JOIN PG_FORN_MSG_RET R ON F.CD_EMP = R.CD_EMP
                                                  AND F.CD_FORN = R.CD_FORN
                                                  AND F.CD_MSG_FORN = R.CD_MSG_FORN
              GROUP BY D.CD_EMP,
                       D.CD_FILIAL,
                       D.CD_PED_SOL,
                       EST_PED_ELTR_FORN_IT.QT_IT_ENV,
                       EST_PED_ELTR_IT.VLR_PROD,
                       EST_PED_CP_IT.QT_IT,
                       EST_PED_CP_IT.VLR_IT,
                       EST_PED_CP.CD_PED_FORN,
                       EST_PED_ELTR_FORN_PED_CP.CD_PED,
                       R.CD_MSG_FORN,
                       R.DS_MSG_FORN)
          UPDATE D
            SET
                QTDE_DEMANDA_FORN = CTE.QTDE_DEMANDA_FORN,
                VLR_DEMANDA = CTE.VLR_DEMANDA,
                ST_ENVIADO = CTE.ST_ENVIADO,
                D.SIT_ATENDIDO = CTE.SIT_ATENDIDO,
                QTDE_ATENDIDO = CTE.QTDE_ATENDIDO,
                VLR_ATENDIDO = CTE.VLR_ATENDIDO,
                CD_PED_FORN = CTE.CD_PED_FORN,
                CD_PED = CTE.CD_PED,
                CD_MSG_FORN = CTE.CD_MSG_FORN,
                DS_MSG_FORN = CTE.DS_MSG_FORN
          FROM #TmpDemanda D
               INNER JOIN CTE_UPDATE CTE ON D.CD_EMP = CTE.CD_EMP
                                            AND D.CD_FILIAL = CTE.CD_FILIAL
                                            AND D.CD_PED_SOL = CTE.CD_PED_SOL;
     SELECT D.CD_FILIAL,
            D.NM_FANT,
            D.CD_PED_SOL,
            D.DT_PED,
            D.DT_DEMANDA,
            EST_ARV_MERC_LINHA.CD_ARV_MERC_LINHA AS LINHA,
            EST_PROD.CD_PROD,
            D.CD_FORN,
            D.RZ_FORN,
            D.ORD_ENV,
            EST_PED_ELTR_IT.QT_IT AS QTDE_DEMANDA,
            D.QTDE_DEMANDA_FORN, --ISNULL(EST_PED_ELTR_FORN_IT.QT_IT_ENV, 0) AS QTDE_DEMANDA_FORN,
            0 AS QTDE_DEMANDA_FORN,
            D.QTDE_ATENDIDO, --    SUM(ISNULL(EST_PED_CP_IT.QT_IT, 0)) AS QTDE_ATENDIDO,
            D.QTDE_NAO_ATENDIDO, --ISNULL(EST_PED_ELTR_FORN_IT.QT_IT_ENV, 0) - SUM(ISNULL(EST_PED_CP_IT.QT_IT, 0)) AS QTDE_NAO_ATENDIDO,
            0 AS QTDE_NAO_ATENDIDO,
            D.VLR_ATENDIDO, -- SUM(ISNULL(EST_PED_CP_IT.QT_IT, 0) * ISNULL(EST_PED_CP_IT.VLR_IT, 0)) AS VLR_ATENDIDO,
            D.VLR_DEMANDA, --(ISNULL(EST_PED_ELTR_FORN_IT.QT_IT_ENV, 0) * EST_PED_ELTR_IT.VLR_PROD) AS VLR_DEMANDA,
            EST_PED_ELTR_IT.VLR_PROD AS VLR_DEMANDA,
            EST_PROD_FABRIC.CD_FABRIC,
            EST_PROD_FABRIC.NM_FABRIC,
            EST_PROD.DS_PROD,
            CD_PED, --ISNULL(EST_PED_ELTR_FORN_PED_CP.CD_PED, 0) AS CD_PED,
            CASE
                WHEN COMPRA_POR_SUPRIMENTO = 0
                THEN 'RESSUPRIMENTO'
                WHEN COMPRA_POR_SUPRIMENTO = 1
                THEN 'SUPRIMENTO'
                WHEN COMPRA_POR_SUPRIMENTO = 2
                THEN 'SUP-RESSUP'
            END AS COMPRA_POR_SUPRIMENTO,
            D.CD_PED_FORN AS CD_PED_FORN, -- ISNULL(EST_PED_CP.CD_PED_FORN, 0) AS CD_PED_FORN,
            COUNT(DISTINCT D.CD_PED_SOL) AS QTDE_REG_DEM,
            EST_PROD_FORN.CD_PROD_FORN,
            (CASE
                 WHEN EST_PROD_FORN.COMPRA_INATIVA = 1
                 THEN 'COMPRA INATIVA'
                 ELSE 'COMPRA LIBERADA'
             END) AS ST_COMPRA_FORN,
            (CAST(EST_PROD_FORN.APRES_EMB_CP AS VARCHAR)+' X '+CAST(ROUND(EST_PROD_FORN.APRES_QT_CP, 0) AS VARCHAR)) AS EMB_ENT_FORN,

       --(CASE
       --     WHEN SUM(ISNULL(EST_PED_CP_IT.QT_IT, 0)) = 0
       --     THEN 'NAO ATENDIDO'
       --     WHEN ISNULL(EST_PED_ELTR_FORN_IT.QT_IT_ENV, 0) - SUM(ISNULL(EST_PED_CP_IT.QT_IT, 0)) > 0
       --     THEN 'ATENDIDO PARCIAL'
       --     ELSE 'ATENDIDO TOTAL'
       -- END) AS SIT_ATENDIDO,
            D.SIT_ATENDIDO,
            D.CD_MSG_FORN AS CD_MSG_FORN,
            D.DS_MSG_FORN AS DS_MSG_FORN,
            D.CD_CTR_ENV,
            D.ORDMELHORCOND,
       --(CASE WHEN ISNULL(EST_PED_ELTR_FORN_IT.CD_EMP, 0) = 0 THEN 'NÃO' ELSE 'SIM' END) AS ST_ENVIADO,
            D.ST_ENVIADO,
            CASE
                WHEN FLAG_COMPRA_PHARMALINK = 0
                THEN '0 - SEM PROCESSO DE VAN DE COMPRAS'
                WHEN FLAG_COMPRA_PHARMALINK = 1
                THEN '1 - PHARMALINK'
                WHEN FLAG_COMPRA_PHARMALINK = 2
                THEN '2 - ENTIRE'
                WHEN FLAG_COMPRA_PHARMALINK = 3
                THEN '3 - PHARMALINK E ENTIRE'
                WHEN FLAG_COMPRA_PHARMALINK = 4
                THEN '4 - OL MIX'
                WHEN FLAG_COMPRA_PHARMALINK = 5
                THEN '5 - FIDELIZE'
                WHEN FLAG_COMPRA_PHARMALINK = 6
                THEN '6 - WEBOL'
                ELSE 'NAO DEFINIDO'
            END AS FLAG_COMPRA_PHARMALINK
     FROM #TmpDemanda D
          INNER JOIN EST_PED_ELTR_IT ON D.CD_EMP = EST_PED_ELTR_IT.CD_EMP
                                        AND D.CD_PED_SOL = EST_PED_ELTR_IT.CD_PED_SOL
          INNER JOIN EST_PROD ON EST_PROD.CD_EMP = EST_PED_ELTR_IT.CD_EMP
                                 AND EST_PROD.CD_PROD = EST_PED_ELTR_IT.CD_PROD
          INNER JOIN EST_PROD_EST_ARV_MERCADOLOGICA ON EST_PROD.CD_EMP = EST_PROD_EST_ARV_MERCADOLOGICA.CD_EMP
                                                       AND EST_PROD.CD_PROD = EST_PROD_EST_ARV_MERCADOLOGICA.CD_PROD
          INNER JOIN EST_ARV_MERC_LINHA ON EST_ARV_MERC_LINHA.CD_EMP = EST_PROD_EST_ARV_MERCADOLOGICA.CD_EMP
                                           AND EST_ARV_MERC_LINHA.CD_ARV_MERC_LINHA = EST_PROD_EST_ARV_MERCADOLOGICA.CD_ARV_MERC_LINHA
          INNER JOIN EST_PROD_FABRIC ON EST_PROD.CD_FABRIC = EST_PROD_FABRIC.CD_FABRIC
          INNER JOIN EST_PED_ELTR_GRP_PED_EST_PED_ELTR GRP_PED ON EST_PED_ELTR_IT.CD_EMP = GRP_PED.CD_EMP
                                                                  AND EST_PED_ELTR_IT.CD_PED_SOL = GRP_PED.CD_PED_SOL
          LEFT JOIN EST_PROD_FORN ON EST_PROD.CD_EMP = EST_PROD_FORN.CD_EMP
                                     AND EST_PROD.CD_PROD = EST_PROD_FORN.CD_PROD
                                     AND D.CD_FORN = EST_PROD_FORN.CD_FORN
     GROUP BY D.CD_FILIAL,
              D.NM_FANT,
              D.CD_PED_SOL,
              D.DT_PED,
              D.DT_DEMANDA,
              EST_ARV_MERC_LINHA.CD_ARV_MERC_LINHA,
              EST_PROD.CD_PROD,
              D.CD_FORN,
              D.RZ_FORN,
              D.ORD_ENV,
              D.QTDE_DEMANDA_FORN,
              EST_PROD_FABRIC.CD_FABRIC,
              EST_PROD_FABRIC.NM_FABRIC,
              EST_PROD.DS_PROD,
              D.CD_PED,
              COMPRA_POR_SUPRIMENTO,
              D.CD_PED_FORN,
              D.QTDE_ATENDIDO,
              QTDE_NAO_ATENDIDO,
              EST_PED_ELTR_IT.QT_IT,
              EST_PED_ELTR_IT.QT_IT,
              EST_PED_ELTR_IT.VLR_PROD,
              D.VLR_ATENDIDO,
              D.SIT_ATENDIDO,
              D.ST_ENVIADO,
              D.VLR_DEMANDA,
              EST_PROD_FORN.CD_PROD_FORN,
              (CASE
                   WHEN EST_PROD_FORN.COMPRA_INATIVA = 1
                   THEN 'COMPRA INATIVA'
                   ELSE 'COMPRA LIBERADA'
               END),
              (CAST(EST_PROD_FORN.APRES_EMB_CP AS VARCHAR)+' X '+CAST(ROUND(EST_PROD_FORN.APRES_QT_CP, 0) AS VARCHAR)),
              D.CD_MSG_FORN,
              D.DS_MSG_FORN,
              D.CD_CTR_ENV,
              D.ORDMELHORCOND,
              D.CD_EMP,
              FLAG_COMPRA_PHARMALINK;