IF OBJECT_ID('P_EST_PEND_LIBERACAO_CONSULTA') IS NOT NULL
    BEGIN
        DROP PROCEDURE P_EST_PEND_LIBERACAO_CONSULTA;
END;
GO
CREATE PROCEDURE P_EST_PEND_LIBERACAO_CONSULTA
(@CD_FILIAIS VARCHAR(MAX), 
 @DT_INI     DATETIME, 
 @DT_FIM     DATETIME
)
AS
set nocount on;
     CREATE TABLE #FILIAIS
     (CD_EMP    INT, 
      CD_FILIAL INT
     );
     INSERT INTO #FILIAIS
            SELECT 1, 
                   ID
            FROM UFD_PRM_IN_STRING(@CD_FILIAIS);
     CREATE TABLE #V_EST_PROD_CPL_PENDENCIAS
     (cd_emp        INT, 
      CD_FILIAL     INT, 
      CD_PROD       INT, 
      DS_PROD       VARCHAR(600), 
      QT_PEND_SAI   MONEY, 
      QT_PEND_ENT   MONEY, 
      QT_PEND_DANFE MONEY, 
      QT_EST        MONEY, 
      QT_EST_FLUT   MONEY
     );
     CREATE TABLE #PEND_DANFE
     (CD_EMP    INT, 
      CD_FILIAL INT, 
      CD_PROD   INT, 
      QTDE_PROD MONEY
     );
     INSERT INTO #V_EST_PROD_CPL_PENDENCIAS
            SELECT pcpl.CD_EMP, 
                   pcpl.CD_FILIAL, 
                   pcpl.CD_PROD, 
                   p.DS_PROD, 
				   ISNULL(SUM(rs.QT_PEND_SAI), 0) AS QT_PEND_SAI,
				   ISNULL(SUM(rs.QT_PEND_ENT), 0) AS QT_PEND_ENT, 
                   0 AS QT_PEND_DANFE,
				   pcpl.QT_EST, 
                   pcpl.QT_EST_FLUT
            FROM dbo.EST_PROD AS p
                 INNER JOIN EST_PROD_EST_ARV_MERCADOLOGICA ON p.CD_EMP = EST_PROD_EST_ARV_MERCADOLOGICA.CD_EMP
                                                              AND p.CD_PROD = EST_PROD_EST_ARV_MERCADOLOGICA.CD_PROD
                 INNER JOIN dbo.EST_PROD_CPL AS pcpl ON p.CD_EMP = pcpl.CD_EMP
                                                        AND p.CD_PROD = pcpl.CD_PROD
			  INNER JOIN EST_PROD_IMP_LIBERACAO_ALCADA_PENDENTE PEND ON PEND.CD_EMP = pcpl.CD_EMP
											 AND  PEND.CD_FILIAL = pcpl.CD_FILIAL
                                                        AND PEND.CD_PROD = pcpl.CD_PROD
                 INNER JOIN #FILIAIS fil ON pcpl.CD_EMP = fil.CD_EMP
                                            AND pcpl.CD_FILIAL = fil.CD_FILIAL
                 LEFT OUTER JOIN EST_PROD_CPL_PENDENTE rs ON pcpl.CD_EMP = rs.CD_EMP
                                                             AND pcpl.CD_FILIAL = rs.CD_FILIAL
                                                             AND pcpl.CD_PROD = rs.CD_PROD
            WHERE pcpl.CD_EMP = 1
            GROUP BY pcpl.CD_EMP, 
                     pcpl.CD_FILIAL, 
                     pcpl.CD_PROD, 
                     p.DS_PROD, 
                     pcpl.QT_EST, 
                     pcpl.QT_EST_FLUT;
     INSERT INTO #PEND_DANFE
            SELECT d.CD_EMP, 
                   d.CD_FILIAL, 
                   i.CD_PROD, 
                   SUM(i.QTDE_PROD) AS QTDE_PROD
            FROM EST_NF_ENT_DANFE_PROVISORIO d
                 INNER JOIN EST_NF_ENT_DANFE_PROVISORIO_ITENS i ON d.CD_CTR = i.CD_CTR
                 INNER JOIN #V_EST_PROD_CPL_PENDENCIAS pcpl ON d.CD_EMP = pcpl.CD_EMP
                                                               AND d.CD_FILIAL = pcpl.CD_FILIAL
                                                               AND i.CD_PROD = pcpl.CD_PROD
                 INNER JOIN #filiais fil ON d.cd_emp = fil.cd_emp
                                            AND fil.cd_filial = d.cd_filial
            WHERE d.cd_emp = 1
            GROUP BY d.CD_EMP, 
                     d.CD_FILIAL, 
                     i.CD_PROD;
     UPDATE dpen
       SET 
           QT_PEND_DANFE = QTDE_PROD
     FROM #V_EST_PROD_CPL_PENDENCIAS dpen
          INNER JOIN #PEND_DANFE d ON d.CD_EMP = dpen.CD_EMP
                                      AND d.CD_FILIAL = dpen.CD_FILIAL
                                      AND d.CD_PROD = dpen.CD_PROD;
     SELECT L.CD_CTR, 
            L.CD_FILIAL, 
            L.CD_PROD, 
            P.DS_PROD, 
            L.QT_PROD, 
            L.VLR_CUSTO, 
            L.QT_PROD * L.VLR_CUSTO AS VLR_CUSTO_TOTAL,
            CASE
                WHEN L.TP_EST = 0
                THEN P.QT_EST
                ELSE P.QT_EST_FLUT
            END AS QT_EST, 
            P.QT_PEND_SAI, 
            P.QT_PEND_ENT, 
            P.QT_PEND_DANFE, 
            L.HIST, 
            L.TP_EST, 
            U.NM_USU, 
            L.DT_CAD, 
            L.TP_IMP, 
            L.TP_EST, 
            L.CD_USU, 
            ISNULL(L.TRANSF_COMERCIAL_FLUTUANTE, 0) AS TRANSF_COMERCIAL_FLUTUANTE, 
            ISNULL(L.ACRESCENTAR_FLUTUANTE, 0) AS ACRESCENTAR_FLUTUANTE, 
            ISNULL(L.RETIRAR_FLUTUANTE, 0) AS RETIRAR_FLUTUANTE
     FROM EST_PROD_IMP_LIBERACAO_ALCADA_PENDENTE L
          INNER JOIN #V_EST_PROD_CPL_PENDENCIAS P ON L.CD_EMP = P.CD_EMP
                                                     AND L.CD_FILIAL = P.CD_FILIAL
                                                     AND L.CD_PROD = P.CD_PROD
          INNER JOIN GLB_USU U ON L.CD_USU = U.CD_USU
          INNER JOIN #filiais fil ON L.CD_EMP = fil.CD_EMP
                                     AND L.CD_FILIAL = fil.CD_FILIAL
     WHERE L.CD_EMP = 1
           AND L.DT_CAD BETWEEN @DT_INI AND @DT_FIM;