IF EXISTS (SELECT * FROM SYSOBJECTS O, SYSUSERS U
                    WHERE
	        O.NAME = 'CURSOR_SEMIACABADO' AND
	        TYPE = 'P'                    AND
	        U.UID = O.UID                 AND
	        U.NAME = USER)

   DROP PROCEDURE CURSOR_SEMIACABADO
   GO

CREATE PROCEDURE CURSOR_SEMIACABADO
   @P_CDFILIAL        VARCHAR(4),
   @P_CDPRODMRP       VARCHAR(10),
   @P_DTMRP           datetime,
   @P_QTAFABRICPCP    DOUBLE PRECISION,
   @P_IDESTOQUE       VARCHAR(1),
   @P_NRORDEMPROD     DOUBLE PRECISION,
   @P_CURSOR_SACAB    CURSOR VARYING OUTPUT
AS

   SET @P_CURSOR_SACAB = CURSOR FORWARD_ONLY STATIC FOR
   SELECT
     INS.CDPRODINSUMO,
     INS.IDSUBOPINSU,
     CL.NRORDENAOP,
     CL.NRORDENASUBOP,
     PROD2.IDPRODPROD,
     ISNULL(PRF.QTESTOSEGU, 0) AS QTESTOQMIN,
     ISNULL(SUM(PR.QTPRODLOTE), 0) AS QTPRODUCAO,
     (CASE ISNULL(@P_IDESTOQUE,'N') WHEN 'S' THEN ISNULL(P.QTESTDIACON, 0) ELSE 0 END) AS QTDEESTO,
     ISNULL(PF2.QTPADPROCFAB, 0) AS QTPADPROCFAB,
     ISNULL(PF2.QTLOTEFABDIA, 0) AS QTLOTEFABDIA,
     DTCONCPROMRP,
     QTSEMITOTAL,
     INS.IDSEMIACETAP,
     ISNULL(SUM(TEKNISA.QTPREVI_MATPRIMA(@P_CDFILIAL, @P_CDPRODMRP, INS.CDPROCFABRIC, INS.CDETAPAPROD, INS.CDPRODINSUMO, CL.QTAFABRICMRP, 'S')), 0) AS QUANTIDADE
   FROM
      PRODUTO PROD,
      PRODUTO PROD2,
      PROCFABRICA PF,
      PROCFABRICA PF2,
      CALEPRODMRP CL,
      INSUMETAPA INS LEFT JOIN
                              (SELECT R.CDPRODUTO, R.IDGERAOPCONF, SUM(R.QTPRODLOTE) AS QTPRODLOTE, SUM(QTSEMITOTAL) AS QTSEMITOTAL
                               FROM (SELECT
                                        O.CDPRODUTO,
                                        P.IDGERAOPCONF,
                                        ISNULL(SUM(QTAPRODPREOP),0) AS QTPRODLOTE,
                                        ISNULL(SUM(QTAPRODPREOP),0) AS QTSEMITOTAL
                                     FROM
                                        ORDPRODUCAO O, PARAMPRODMRP P
                                     WHERE
                                       (O.CDFILIAL     = @P_CDFILIAL)  AND
                                       (O.IDSITORDPROD = '1')         AND
                                       (O.IDCONFIRMAOP = 'S')         AND
                                       (P.CDFILIAL  = O.CDFILIAL ) AND
                                       (P.CDPRODUTO = O.CDPRODUTO) AND
                                       (P.IDGERAOPCONF = 'S')
                                     GROUP BY
                                        O.CDPRODUTO, P.IDGERAOPCONF
                                     UNION ALL
                                     SELECT
                                        O.CDPRODUTO,
                                        P.IDGERAOPCONF,
                                        0 AS QTPRODLOTE,
                                        ISNULL(SUM(QTAPRODPREOP),0) AS QTSEMITOTAL
                                     FROM
                                        ORDPRODUCAO O, PARAMPRODMRP P
                                     WHERE
                                        (O.CDFILIAL     = @P_CDFILIAL)  AND
                                        (O.IDSITORDPROD = '2')         AND
                                        (O.IDCONFIRMAOP = 'S')         AND
                                        (P.CDFILIAL  = O.CDFILIAL ) AND
                                        (P.CDPRODUTO = O.CDPRODUTO) AND
                                        (P.IDGERAOPCONF = 'S'     )

                                     GROUP BY
                                        O.CDPRODUTO, P.IDGERAOPCONF
                                    ) R
                               GROUP BY R.CDPRODUTO, R.IDGERAOPCONF) PR ON (INS.CDPRODINSUMO   = PR.CDPRODUTO)
                     LEFT JOIN POSESTCONS P ON (P.CDFILIAL  = @P_CDFILIAL)      AND
                                               (P.CDPRODUTO = INS.CDPRODINSUMO)
                     LEFT JOIN ESTOSEGU PRF ON (PRF.CDFILIAL       = INS.CDFILIAL)      AND
                                               (PRF.CDPRODESTO     = INS.CDPRODINSUMO)
   WHERE
      (PROD.CDPRODUTO     = @P_CDPRODMRP)      AND
      (PF.CDPRODUTO       = PROD.CDPRODUTO)    AND
      (PF.IDPROFABPADR    = 'S')               AND
      (CL.CDFILIAL        = @P_CDFILIAL)       AND
      (CL.CDPRODUTO       = @P_CDPRODMRP)      AND
      (CONVERT(VARCHAR, CL.DTPROGPRODCP, 103))    = CONVERT(VARCHAR, @P_DTMRP, 103)          AND
      (INS.CDPRODUTO      = PF.CDPRODUTO)      AND
      (INS.CDPROCFABRIC   = PF.CDPROCFABRIC)   AND
      (INS.IDSEMIACETAP   = 'S')               AND
      ((P.DTPOSESTCON     = (SELECT MAX(PO.DTPOSESTCON)
                             FROM POSESTCONS PO
                             WHERE PO.CDFILIAL    = @P_CDFILIAL       AND
                                   PO.CDPRODUTO   = INS.CDPRODINSUMO AND
                                   PO.DTPOSESTCON <= (GETDATE() + 1))) OR
       P.DTPOSESTCON IS NULL)                  AND
       (PROD2.CDPRODUTO   = INS.CDPRODINSUMO)  AND
       (PF2.CDPRODUTO     = INS.CDPRODINSUMO)  AND
       (PF2.IDPROFABPADR  = 'S')               AND
       (CL. NRORDEMPROD    = @P_NRORDEMPROD )
   GROUP BY
      INS.CDPRODINSUMO,
      INS.IDSUBOPINSU,
      CL.NRORDENAOP,
      CL.NRORDENASUBOP,
      PROD2.IDPRODPROD,
      ISNULL(PRF.QTESTOSEGU, 0),
      (CASE ISNULL(@P_IDESTOQUE ,'N') WHEN 'S' THEN ISNULL(P.QTESTDIACON, 0) ELSE 0 END),
      ISNULL(PF2.QTPADPROCFAB, 0),
      ISNULL(PF2.QTLOTEFABDIA, 0),
      DTCONCPROMRP,
      QTSEMITOTAL,
      INS.IDSEMIACETAP
   ORDER BY
      DTCONCPROMRP DESC;

   OPEN @P_CURSOR_SACAB
GO

-- VERSAO --
EXEC ATUVEROBJ 'CURSOR_SEMIACABADO','4.11.179';
GO

