CREATE OR REPLACE PROCEDURE CALCULA_RATCOMISSAO(
   P_CDFILIAL   IN SOLISERV.CDFILIAL%TYPE,
   P_NRSOLISERV IN SOLISERV.NRSOLISERV%TYPE)

IS
   V_VRRTEQCOMSOL NUMBER;
   V_VRRATEIOTOTA NUMBER;

BEGIN
   V_VRRATEIOTOTA := 0;

   FOR C_RATEIO IN (SELECT P.CDEQUIPETRAB, P.VRRTEQCOMSOL, COUNT(*) AS NRCOUNTFUNC
                    FROM PARRATCOMSOL P, SOLISERV S, COMISSAOFUNC C
                    WHERE P.CDTPRATCOMSL = S.CDTPRATCOMSL
                      AND S.CDFILIAL     = P_CDFILIAL
                      AND S.NRSOLISERV   = P_NRSOLISERV
                      AND C.CDFILIAL     = P_CDFILIAL
                      AND C.NRSOLISERV   = P_NRSOLISERV
                      AND C.CDEQUIPETRAB = P.CDEQUIPETRAB
                    GROUP BY P.CDEQUIPETRAB, P.VRRTEQCOMSOL) LOOP

      UPDATE COMISSAOFUNC C
      SET VRPECOMISFUN = C_RATEIO.VRRTEQCOMSOL/C_RATEIO.NRCOUNTFUNC
      WHERE C.CDFILIAL     = P_CDFILIAL
        AND C.NRSOLISERV   = P_NRSOLISERV
        AND C.CDEQUIPETRAB = C_RATEIO.CDEQUIPETRAB;

      V_VRRATEIOTOTA := V_VRRATEIOTOTA + C_RATEIO.VRRTEQCOMSOL;
   END LOOP;

   SELECT NVL(SUM(P.VRRTEQCOMSOL),0) AS VRRTEQCOMSOL
   INTO V_VRRTEQCOMSOL
   FROM PARRATCOMSOL P, SOLISERV S
   WHERE P.CDTPRATCOMSL = S.CDTPRATCOMSL
     AND S.CDFILIAL     = P_CDFILIAL
     AND S.NRSOLISERV   = P_NRSOLISERV
     AND NOT EXISTS(SELECT CDEQUIPETRAB
                    FROM COMISSAOFUNC C
                    WHERE C.CDFILIAL     = P_CDFILIAL
                      AND C.NRSOLISERV   = P_NRSOLISERV
                      AND C.CDEQUIPETRAB = P.CDEQUIPETRAB);

   IF V_VRRTEQCOMSOL <> 0 THEN
      UPDATE COMISSAOFUNC C
      SET VRPECOMISFUN = VRPECOMISFUN + ((V_VRRTEQCOMSOL/V_VRRATEIOTOTA) * VRPECOMISFUN)
      WHERE C.CDFILIAL   = P_CDFILIAL
        AND C.NRSOLISERV = P_NRSOLISERV;
   END IF;
END;
/

