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

  DROP PROCEDURE GERA_SOLICMP
  GO

CREATE PROCEDURE GERA_SOLICMP  @P_CDFILSOLI     VARCHAR (4),
                               @P_CDFILLAN      VARCHAR (4),
                               @P_NRSOLICMP     VARCHAR(20),
                               @P_DTENTSOLICMP  DATETIME,
                               @P_DTSOLCMP      DATETIME,
                               @P_NRLISTA       VARCHAR(10),
                               @P_NRSUGCOMPRA   VARCHAR(20),
                               @P_IDTPRELSUG    VARCHAR (1),
                               @P_IDMENSSOLI    VARCHAR (1) OUTPUT
AS
   DECLARE @V_GEROU_SOLI       DOUBLE PRECISION;
   DECLARE @V_CTITEMSOLI       DOUBLE PRECISION;
   DECLARE @C_PR_CDPRODUTO      VARCHAR(10);
   DECLARE @C_PR_CDARVPROD      VARCHAR(10);
   DECLARE @C_PR_SGUNIDADE      VARCHAR(2);
   DECLARE @C_PR_NMPRODUTO      VARCHAR(50);
   DECLARE @C_SUG_CDPROSUGCOMP  VARCHAR(10);
   DECLARE @C_SUG_QTCOMPRA      DOUBLE PRECISION;
   DECLARE @C_SUG_QTESTOQSGCMP  DOUBLE PRECISION;
   DECLARE @C_SUG_QTSAIDAATUAL  DOUBLE PRECISION;
BEGIN
   SET @V_GEROU_SOLI = 0;
   SET @P_IDMENSSOLI = '1';


   DECLARE  C_PRF CURSOR FOR
      SELECT PR.CDPRODUTO, PR.CDARVPROD, PR.SGUNIDADE, PR.NMPRODUTO
      FROM PRODUTO PR
      WHERE (PR.CDPRODESTO IS NOT NULL)
        AND EXISTS
              (SELECT PF.CDPRODUTO
               FROM PRODUTO P, PRODFORN PF, LISTA LI
               WHERE (LI.NRLISTA    = @P_NRLISTA)
                 AND (PF.CDFORNECED = LI.CDLISTA)
                 AND (PF.CDPRODUTO  = P.CDPRODUTO)
                 AND (PR.CDARVPROD LIKE P.CDARVPROD + '%'));
   OPEN C_PRF;
   FETCH NEXT FROM C_PRF INTO @C_PR_CDPRODUTO, @C_PR_CDARVPROD, @C_PR_SGUNIDADE, @C_PR_NMPRODUTO

   WHILE (@@FETCH_STATUS = 0) BEGIN

      SELECT @V_CTITEMSOLI = COUNT(CDFILSOLI)
      FROM ITEMSOLI
      WHERE (CDFILSOLI  = @P_CDFILSOLI)
        AND (CDFILLAN   = @P_CDFILLAN)
        AND (CDPRODUTO  = @C_PR_CDPRODUTO)
        AND (DTPROGENTR = @P_DTENTSOLICMP);

      IF @V_CTITEMSOLI = 0 BEGIN
         IF @V_GEROU_SOLI = 0  BEGIN
            INSERT INTO SOLICITA
               (CDFILSOLI,  CDFILLANCA, NRSOLICMP,
                DTSOLCMP,   IDTIPLAN,  CDFILCENT)
            VALUES
               (@P_CDFILSOLI, @P_CDFILLAN, @P_NRSOLICMP,
                @P_DTSOLCMP,  'A',         @P_CDFILLAN);

            SET @V_GEROU_SOLI = 1;
         END;

         INSERT INTO ITEMSOLI
            (CDFILSOLI, CDFILLAN, NRSOLICMP,
             CDPRODUTO, DTPROGENTR, QTSOLIPROD)
         VALUES
            (@P_CDFILSOLI, @P_CDFILLAN, @P_NRSOLICMP,
             @C_PR_CDPRODUTO, @P_DTENTSOLICMP, 0);

      END
      ELSE BEGIN
         SET @P_IDMENSSOLI = '2';
      END;
      FETCH NEXT FROM C_PRF INTO @C_PR_CDPRODUTO, @C_PR_CDARVPROD, @C_PR_SGUNIDADE, @C_PR_NMPRODUTO;
   END;
   CLOSE C_PRF;
   DEALLOCATE C_PRF;

   IF @V_GEROU_SOLI = 1  BEGIN
      IF @P_IDTPRELSUG = '1'  BEGIN
         DECLARE C_SUG CURSOR FOR
            SELECT
               CDPROSUGCOMP, QTCOMPRA
            FROM
               TRAB_SUGCOMPRA
            WHERE
               CDFILSUGCOMP    = @P_CDFILSOLI
              AND NRSUGCOMPRA  = @P_NRSUGCOMPRA
              AND QTCOMPRA     > 0
            ORDER BY
               CDPROSUGCOMP;
            OPEN C_SUG;
            FETCH NEXT FROM C_SUG INTO  @C_SUG_CDPROSUGCOMP, @C_SUG_QTCOMPRA;

         WHILE (@@FETCH_STATUS = 0) BEGIN
            UPDATE ITEMSOLI
            SET QTSOLIPROD = @C_SUG_QTCOMPRA
            WHERE
               CDFILSOLI      = @P_CDFILSOLI
               AND CDFILLAN   = @P_CDFILLAN
               AND NRSOLICMP  = @P_NRSOLICMP
               AND CDPRODUTO  = @C_SUG_CDPROSUGCOMP
               AND DTPROGENTR = @P_DTENTSOLICMP;
            FETCH NEXT FROM C_SUG INTO  @C_SUG_CDPROSUGCOMP, @C_SUG_QTCOMPRA;
         END;
         CLOSE C_SUG;
         DEALLOCATE C_SUG;
      END
      ELSE BEGIN
         DECLARE C_SUG CURSOR FOR
            SELECT
               CDPROSUGCOMP, QTESTOQSGCMP, SUM(QTSAIDAATUAL) QTSAIDAATUAL
            FROM TRAB_SUGCOMPRA
            WHERE
               CDFILSUGCOMP    = @P_CDFILSOLI
              AND NRSUGCOMPRA  = @P_NRSUGCOMPRA
              AND QTSAIDAATUAL > 0
            GROUP BY
               CDPROSUGCOMP, QTESTOQSGCMP;
         OPEN C_SUG;
         FETCH NEXT FROM C_SUG INTO  @C_SUG_CDPROSUGCOMP, @C_SUG_QTESTOQSGCMP, @C_SUG_QTSAIDAATUAL;

         WHILE (@@FETCH_STATUS = 0) BEGIN
            UPDATE ITEMSOLI
            SET QTSOLIPROD = ABS(ISNULL( @C_SUG_QTESTOQSGCMP, 0) - ISNULL( @C_SUG_QTSAIDAATUAL, 0))
            WHERE CDFILSOLI  = @P_CDFILSOLI
              AND CDFILLAN   = @P_CDFILLAN
              AND NRSOLICMP  = @P_NRSOLICMP
              AND CDPRODUTO  = @C_SUG_CDPROSUGCOMP
              AND DTPROGENTR = @P_DTENTSOLICMP;
            FETCH NEXT FROM C_SUG INTO  @C_SUG_CDPROSUGCOMP, @C_SUG_QTESTOQSGCMP, @C_SUG_QTSAIDAATUAL;
         END;
         CLOSE C_SUG;
         DEALLOCATE C_SUG;
      END;
   END
   ELSE BEGIN
      SET @P_IDMENSSOLI = '3';
   END
END;
GO


-- VERSAO --
EXEC ATUVEROBJ 'GERA_SOLICMP','5.01.014';
GO

