IF EXISTS(SELECT NAME
          FROM SYSOBJECTS
          WHERE NAME = 'ATUALIZA_ITEMORCA' AND TYPE = 'P')
  DROP PROCEDURE ATUALIZA_ITEMORCA
  GO

CREATE PROCEDURE ATUALIZA_ITEMORCA
   	@P_CDEMPRESA  VARCHAR(2),
      @P_CDORCAMENT VARCHAR(4),
      @P_CDFILIAL   VARCHAR(4),
      @P_CDCENTCUST VARCHAR(4),
      @P_CDCONTCTBL VARCHAR(20)
AS

DECLARE @V_PRIMNIVEL         VARCHAR(20);
DECLARE @ATUALIZA_CDCONTCTBL VARCHAR(20);
DECLARE @ATUALIZA_DTITEMORCA DATETIME;
DECLARE @ATUALIZA_VRITEMORCA NUMERIC(16, 2);

BEGIN
   SELECT @V_PRIMNIVEL = CDCONTCTBL
   FROM CONTCTBL
   WHERE (CHARINDEX(RTRIM(CONTCTBL.CDCONTCTBL), @P_CDCONTCTBL) = 1) AND
         (CONTCTBL.CDNVCONTCTBL = '1');

   UPDATE ITEMORCA
   SET VRITEMORCA = 0
   WHERE (ITEMORCA.CDEMPRESA  = @P_CDEMPRESA)                      AND
         (ITEMORCA.CDORCAMENT = @P_CDORCAMENT)                     AND
         (ITEMORCA.CDFILIAL   = @P_CDFILIAL)                       AND
         (ITEMORCA.CDCENTCUST = @P_CDCENTCUST)                     AND
         (CHARINDEX(RTRIM(@V_PRIMNIVEL), ITEMORCA.CDCONTCTBL) = 1) AND
         (ITEMORCA.CDCONTCTBL IN (SELECT CDCONTCTBL
                                  FROM CONTCTBL
                                  WHERE IDTIPOCONT = 'S'));

   DECLARE ATUALIZA CURSOR FOR
	   SELECT CDCONTCTBL, DTITEMORCA, VRITEMORCA
      FROM ITEMORCA
		WHERE (CDEMPRESA  = @P_CDEMPRESA)                               AND
            (CDORCAMENT = @P_CDORCAMENT)                              AND
            (CDFILIAL   = @P_CDFILIAL)                                AND
            (CDCENTCUST = @P_CDCENTCUST)                              AND
            (CHARINDEX(RTRIM(@V_PRIMNIVEL), ITEMORCA.CDCONTCTBL) = 1) AND
            (ITEMORCA.CDCONTCTBL IN (SELECT CDCONTCTBL
                                     FROM CONTCTBL
                                     WHERE IDTIPOCONT = 'A'))
      ORDER BY CDCONTCTBL, DTITEMORCA, VRITEMORCA;

   OPEN ATUALIZA;
   FETCH NEXT FROM ATUALIZA INTO @ATUALIZA_CDCONTCTBL, @ATUALIZA_DTITEMORCA, @ATUALIZA_VRITEMORCA;

   WHILE(@@FETCH_STATUS = 0) BEGIN
      INSERT INTO ITEMORCA
         (CDEMPRESA, CDORCAMENT, CDFILIAL, CDCENTCUST, CDCONTCTBL, DTITEMORCA, VRITEMORCA)
	   (SELECT @P_CDEMPRESA, @P_CDORCAMENT, @P_CDFILIAL, @P_CDCENTCUST, CDCONTCTBL, @ATUALIZA_DTITEMORCA, 0
       FROM CONTCTBL
       WHERE (CDCONTCTBL NOT IN (SELECT DISTINCT CDCONTCTBL
                                 FROM ITEMORCA
                                 WHERE (ITEMORCA.CDEMPRESA  = @P_CDEMPRESA)  AND
                                       (ITEMORCA.CDORCAMENT = @P_CDORCAMENT) AND
                                       (ITEMORCA.CDFILIAL   = @P_CDFILIAL)   AND
                                       (ITEMORCA.CDCENTCUST = @P_CDCENTCUST) AND
                                       (ITEMORCA.DTITEMORCA = @ATUALIZA_DTITEMORCA))) AND
             (CHARINDEX(RTRIM(CONTCTBL.CDCONTCTBL), @ATUALIZA_CDCONTCTBL) = 1));

      UPDATE ITEMORCA
      SET VRITEMORCA = VRITEMORCA + @ATUALIZA_VRITEMORCA
      WHERE  (ITEMORCA.CDEMPRESA  = @P_CDEMPRESA)                              AND
             (ITEMORCA.CDORCAMENT = @P_CDORCAMENT)                             AND
             (ITEMORCA.CDFILIAL   = @P_CDFILIAL)                               AND
             (ITEMORCA.CDCENTCUST = @P_CDCENTCUST)                             AND
             (ITEMORCA.DTITEMORCA = @ATUALIZA_DTITEMORCA)                      AND
             (CHARINDEX(RTRIM(ITEMORCA.CDCONTCTBL), @ATUALIZA_CDCONTCTBL) = 1) AND
             (ITEMORCA.CDCONTCTBL IN (SELECT CDCONTCTBL
                                      FROM CONTCTBL
                                      WHERE IDTIPOCONT = 'S'));

      FETCH NEXT FROM ATUALIZA INTO @ATUALIZA_CDCONTCTBL, @ATUALIZA_DTITEMORCA, @ATUALIZA_VRITEMORCA;
	END;
CLOSE ATUALIZA;
DEALLOCATE ATUALIZA;
END;
GO

