/******************************************************************************/
/* AGENT2B                                                                    */
/*                                                                            */
/* Pedidos                                                                    */
/******************************************************************************/
/*                                                                            */
/* Nome: RETORNA_RENTABILIDADE_PED                                            */
/*                                                                            */
/* Criação:                                                                   */
/*    31/01/2011 - Welbert G. Moitinha                                        */
/*                                                                            */
/******************************************************************************/


IF EXISTS (SELECT * FROM SYSOBJECTS O, SYSUSERS U
           WHERE O.NAME = 'RETORNA_RENTABILIDADE_PED' AND
                 TYPE = 'FN' 		      AND
	              U.UID = O.UID           AND
	              U.NAME = USER)
DROP FUNCTION RETORNA_RENTABILIDADE_PED
GO

CREATE FUNCTION RETORNA_RENTABILIDADE_PED( @P_CDFILIAL   VARCHAR(4),
                                           @P_NRPEDIDO  VARCHAR(10) )RETURNS DOUBLE PRECISION
AS
BEGIN
   DECLARE @V_VRTOTALAUX   DOUBLE PRECISION;
   DECLARE @V_VRTOTALLIQAUX DOUBLE PRECISION;
   DECLARE @V_VRTOTCUSTPRODBASE  DOUBLE PRECISION;
   DECLARE @V_TOTLUCRO DOUBLE PRECISION;
   DECLARE @V_VRCORRENT DOUBLE PRECISION;
	DECLARE @V_VRITRENTABIL DOUBLE PRECISION;
   DECLARE @V_CDPRECOBASE VARCHAR(2);
   DECLARE @V_IDEXIBRENTPED CHAR(1);

   DECLARE @C_REG_CDPRODUTO CHAR(10);
   DECLARE @C_REG_QTITEMPED DOUBLE PRECISION;
   DECLARE @C_REG_VRPREUNITPED DOUBLE PRECISION;
   DECLARE @C_REG_VRDESITPED DOUBLE PRECISION;
   DECLARE @C_REG_VRPERCMARKUPIT DOUBLE PRECISION;
   DECLARE @C_REG_VRPRTABITPED DOUBLE PRECISION;
   DECLARE @C_REG_VRCUSTPRODBASE DOUBLE PRECISION;
   DECLARE @C_REG_VRPEFORMPREC DOUBLE PRECISION;

   SET @V_VRTOTALAUX = 0;
   SET @V_VRTOTALLIQAUX = 0;
   SET @V_VRTOTCUSTPRODBASE = 0;
   SET @V_TOTLUCRO = 0;
	SET @V_VRITRENTABIL = 0;
   SET @V_VRCORRENT = NULL;

   SELECT @V_IDEXIBRENTPED = MAX(IDEXIBRENTPED)
     FROM PARAMPEDIDO
    WHERE CDFILIAL = @P_CDFILIAL;

   IF (@V_IDEXIBRENTPED = 'S') BEGIN
      SELECT @V_CDPRECOBASE = MAX(CDPRECOBASE)
        FROM PEDIDO
       WHERE CDFILIAL = @P_CDFILIAL
         AND NRPEDIDO = @P_NRPEDIDO;

      DECLARE C_REG CURSOR FOR
                SELECT I.CDPRODUTO, ISNULL(QTITEMPED, 0) AS QTITEMPED, ISNULL(VRPREUNITPED, 0) AS VRPREUNITPED,
                       ISNULL(VRDESITPED, 0) AS VRDESITPED, ISNULL(VRPERCMARKUPIT, 0) AS VRPERCMARKUPIT,
                       ISNULL(VRPRTABITPED, 0) AS VRPRTABITPED, ISNULL(VRCUSTPRODBASE, 0) AS VRCUSTPRODBASE,
                       (SELECT MAX(VRPEFORMPREC)
                          FROM PARAMPEDIDO R, FORMPRECPROD FP
                         WHERE R.CDFILIAL = @P_CDFILIAL
                           AND FP.CDFILIAL = R.CDFILIAL
                           AND FP.CDPRODUTO = I.CDPRODUTO ) AS VRPEFORMPREC
                  FROM ITEMPEDIDO I, ITEMPREBASE H
                 WHERE I.CDFILIAL = @P_CDFILIAL
                   AND I.NRPEDIDO = @P_NRPEDIDO
                   AND I.CDPRODUTO = H.CDPRODUTO
                   AND H.CDPRECOBASE  = @V_CDPRECOBASE
                 ORDER BY I.NRITEMPED;

      OPEN C_REG;
      FETCH NEXT FROM C_REG INTO  @C_REG_CDPRODUTO, @C_REG_QTITEMPED, @C_REG_VRPREUNITPED, @C_REG_VRDESITPED,
                                  @C_REG_VRPERCMARKUPIT, @C_REG_VRPRTABITPED, @C_REG_VRCUSTPRODBASE, @C_REG_VRPEFORMPREC;

      WHILE (@@FETCH_STATUS = 0) BEGIN

         SET @V_VRTOTALAUX = (@C_REG_QTITEMPED * ROUND(@C_REG_VRPREUNITPED,2));
         SET @V_VRTOTALLIQAUX = (@C_REG_QTITEMPED * Round(@C_REG_VRPREUNITPED,2) - (FLOOR(@C_REG_VRDESITPED*100)/100));

         IF @C_REG_VRPERCMARKUPIT > 0 BEGIN
            SET @V_VRTOTALLIQAUX = @V_VRTOTALLIQAUX + ((@V_VRTOTALAUX *@C_REG_VRPERCMARKUPIT)/100);
         END;

         SET @V_VRTOTCUSTPRODBASE = @V_VRTOTCUSTPRODBASE +(@C_REG_VRCUSTPRODBASE* @C_REG_QTITEMPED);
         SET @V_TOTLUCRO = @V_TOTLUCRO + (((@C_REG_VRPEFORMPREC *@C_REG_VRCUSTPRODBASE)/100)* @C_REG_QTITEMPED)+ (@V_VRTOTALLIQAUX - @C_REG_VRPRTABITPED*@C_REG_QTITEMPED);

         FETCH NEXT FROM C_REG INTO  @C_REG_CDPRODUTO, @C_REG_QTITEMPED, @C_REG_VRPREUNITPED, @C_REG_VRDESITPED,
                                     @C_REG_VRPERCMARKUPIT, @C_REG_VRPRTABITPED, @C_REG_VRCUSTPRODBASE, @C_REG_VRPEFORMPREC;
      END;

      CLOSE C_REG;
      DEALLOCATE C_REG;

      IF @V_VRTOTCUSTPRODBASE <> 0 BEGIN
         SELECT @V_VRCORRENT = MAX(VRCORRENT)
           FROM RENTABILIDADE
          WHERE ((@V_TOTLUCRO*100)/@V_VRTOTCUSTPRODBASE)  BETWEEN VRPERCINIRENT AND VRPERCFIMRENT;
      END;
	END
	ELSE IF (@V_IDEXIBRENTPED = 'F') BEGIN
		SELECT @V_VRITRENTABIL = AVG(VRITRENTABIL)
        FROM ITEMPEDIDO
       WHERE CDFILIAL = @P_CDFILIAL
		   AND NRPEDIDO = @P_NRPEDIDO;
		
		IF (@V_VRITRENTABIL <> 0) THEN
			SELECT @V_VRCORRENT = MAX(VRCORRENT)
			  FROM RENTABILIDADE
			 WHERE (@V_VRITRENTABIL  BETWEEN VRPERCINIRENT AND VRPERCFIMRENT);
		END;
   END
   ELSE BEGIN
      SET @V_VRCORRENT = 0;
   END;
   RETURN(@V_VRCORRENT);
END;
GO

-- VERSAO --
EXEC ATUVEROBJ 'RETORNA_RENTABILIDADE_PED','6.19.000';
GO