DROP TYPE COMISSAO_ORE_TABLE
/

DROP TYPE COMISSAO_ORE_TYPE
/

CREATE TYPE COMISSAO_ORE_TYPE AS OBJECT( 
   CDFILIAL     VARCHAR2(4),
   NRORCAEVENTO VARCHAR2(10),
   NRSEQORCAEVE VARCHAR2(10),
   CDCLIENTE    VARCHAR2(21),
   CDVENDEDOR   VARCHAR2(4),
   CDTIPOEVENTO VARCHAR2(3),
   DTEVENTO     DATE,
   DTFECHAORCA  DATE,
   VRTOTALOREV  FLOAT(126),
   VRPERCCOMIS  FLOAT(126),
   VRCOMISSAO   FLOAT(126),
   IDTPRECEOREV VARCHAR2(1)
)
/

CREATE TYPE COMISSAO_ORE_TABLE AS TABLE OF COMISSAO_ORE_TYPE
/

create or replace FUNCTION COMISSAO_ORE(P_CDFILIAL           IN VARCHAR2, 
                                        P_CDVENDEDOR         IN VARCHAR2, 
                                        P_CDTIPOEVENTO       IN VARCHAR2, 
                                        P_IDDTBASECOMISSAO   IN VARCHAR2, 
                                        P_IDGERACOMISSAOVEND IN VARCHAR2, 
                                        P_DTINICIAL          IN DATE, 
                                        P_DTFINAL            IN DATE, 
                                        P_IDCOMISGERADA      IN VARCHAR2) RETURN COMISSAO_ORE_TABLE PIPELINED 
IS 
  OUT_RECORD COMISSAO_ORE_TYPE := COMISSAO_ORE_TYPE (NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL); 
   
  V_TCDVENDEDOR     VARCHAR2(1); 
  V_TCDTIPOEVENTO   VARCHAR2(1); 
  V_VRCOMISSAO      FLOAT(126); 
  V_VRPERCCOMIS     FLOAT(126); 
  V_IDTPRECEOREV    VARCHAR2(1); 
  V_CDFORNECED      VARCHAR2(21); 
  V_VRNOTAFISC      FLOAT(126); 
BEGIN 
  V_TCDVENDEDOR   := ''; 
  V_TCDTIPOEVENTO := ''; 
  IF (P_CDVENDEDOR IS NULL) THEN 
    V_TCDVENDEDOR := 'T'; 
  END IF; 
  IF (P_CDTIPOEVENTO IS NULL) THEN 
    V_TCDTIPOEVENTO := 'T'; 
  END IF; 
 
   
  FOR C_TOTALORCAEVENTO in  
    ( SELECT OE. CDFILIAL, OE.CDVENDEDOR, SUM(OE.VRTOTALOREV) VRTOTALOREV
        FROM (  SELECT CDFILIAL,    CDVENDEDOR,  
                       SUM(VRTOTALOREV) VRTOTALOREV 
                  FROM ORCAEVENTO OE 
                 WHERE CDFILIAL = P_CDFILIAL  
                   AND ((INSTR(P_CDVENDEDOR,   CDVENDEDOR  ) > 0) OR (V_TCDVENDEDOR = 'T')) 
                   AND ((INSTR(P_CDTIPOEVENTO, CDTIPOEVENTO) > 0) OR (V_TCDTIPOEVENTO  = 'T')) 
                   AND ((CASE P_IDDTBASECOMISSAO  
                         WHEN 'EVENTO'  
                         THEN TO_DATE(DTEVENTO)  
                         ELSE TO_DATE(DTFECHAORCA) END) BETWEEN TO_DATE(P_DTINICIAL) AND TO_DATE(P_DTFINAL)) 
                   AND IDSITUORCEVE   IN (3,4,5,6,7,0) 
                   AND ((P_IDCOMISGERADA = 'N' AND ((P_IDGERACOMISSAOVEND = 'S' AND NOT EXISTS (SELECT 1 
                                                                                                   FROM NFRELACORC, VENDEDOR, NOTAFISCAL 
                                                                                                  WHERE NFRELACORC.CDFILIAL     = OE.CDFILIAL 
                                                                                                    AND NFRELACORC.NRORCAEVENTO = OE.NRORCAEVENTO 
                                                                                                    AND NFRELACORC.NRSEQORCAEVE = OE.NRSEQORCAEVE  
                                                                                                    AND VENDEDOR.CDVENDEDOR     = OE.CDVENDEDOR    
                                                                                                    AND NFRELACORC.IDORIGNFORCA = 'V' 
                                                                                                    AND NFRELACORC.CDFILIAL     = NOTAFISCAL.CDFILIAL 
                                                                                                    AND NFRELACORC.NRLANCTONF   = NOTAFISCAL.NRLANCTONF 
                                                                                                    AND (((OE.VRTOTALOREV > 0) AND (NOTAFISCAL.CDFORNECED = VENDEDOR.CDFORNECED)) 
                                                                                                     OR  ((OE.VRTOTALOREV < 0) AND (NOTAFISCAL.CDCLIENTE  = VENDEDOR.CDFORNECED))))) 
                                                     OR 
                                                     (P_IDGERACOMISSAOVEND = 'N' AND NOT EXISTS( SELECT 1 
                                                                                                   FROM PERCENTCOMISGERADA 
                                                                                                  WHERE CDFILIAL     = OE.CDFILIAL 
                                                                                                    AND NRORCAEVENTO = OE.NRORCAEVENTO 
                                                                                                    AND NRSEQORCAEVE = OE.NRSEQORCAEVE  
                                                                                                    AND CDVENDEDOR   = OE.CDVENDEDOR ) )) 
 
                          ) 
                      OR (P_IDCOMISGERADA = 'S' AND ((P_IDGERACOMISSAOVEND = 'S' AND EXISTS (SELECT 1 
                                                                                               FROM NFRELACORC, VENDEDOR, NOTAFISCAL 
                                                                                              WHERE NFRELACORC.CDFILIAL     = OE.CDFILIAL 
                                                                                                AND NFRELACORC.NRORCAEVENTO = OE.NRORCAEVENTO 
                                                                                                AND NFRELACORC.NRSEQORCAEVE = OE.NRSEQORCAEVE  
                                                                                                AND VENDEDOR.CDVENDEDOR     = OE.CDVENDEDOR    
                                                                                                AND NFRELACORC.IDORIGNFORCA = 'V' 
                                                                                                AND NFRELACORC.CDFILIAL     = NOTAFISCAL.CDFILIAL 
                                                                                                AND NFRELACORC.NRLANCTONF   = NOTAFISCAL.NRLANCTONF 
                                                                                                AND (((OE.VRTOTALOREV > 0) AND (NOTAFISCAL.CDFORNECED = VENDEDOR.CDFORNECED)) 
                                                                                                 OR  ((OE.VRTOTALOREV < 0) AND (NOTAFISCAL.CDCLIENTE  = VENDEDOR.CDFORNECED))))) 
                                                    OR 
                                                    (P_IDGERACOMISSAOVEND = 'N' AND EXISTS(SELECT 1 
                                                                                             FROM PERCENTCOMISGERADA 
                                                                                            WHERE CDFILIAL     = OE.CDFILIAL 
                                                                                              AND NRORCAEVENTO = OE.NRORCAEVENTO 
                                                                                              AND NRSEQORCAEVE = OE.NRSEQORCAEVE  
                                                                                              AND CDVENDEDOR     = OE.CDVENDEDOR ))) 
                         )) 
                 GROUP BY CDFILIAL,    CDVENDEDOR 
 
                 UNION 
                 
                SELECT CDFILIAL,    CDSUPVENDEDOR CDVENDEDOR,  
                       SUM(VRTOTALOREV) VRTOTALOREV 
                  FROM ORCAEVENTO OE 
                 WHERE CDFILIAL = P_CDFILIAL  
                   AND ((INSTR(P_CDVENDEDOR,   CDSUPVENDEDOR) > 0) OR (V_TCDVENDEDOR = 'T')) 
                   AND ((INSTR(P_CDTIPOEVENTO, CDTIPOEVENTO ) > 0) OR (V_TCDTIPOEVENTO  = 'T')) 
                   AND ((CASE P_IDDTBASECOMISSAO  
                         WHEN 'EVENTO'  
                         THEN TO_DATE(DTEVENTO)  
                         ELSE TO_DATE(DTFECHAORCA) END) BETWEEN TO_DATE(P_DTINICIAL) AND TO_DATE(P_DTFINAL)) 
                   AND IDSITUORCEVE   IN (3,4,5,6,7,0) 
                   AND ((P_IDCOMISGERADA = 'N' AND ((P_IDGERACOMISSAOVEND = 'S' AND NOT EXISTS (SELECT 1 
                                                                                                   FROM NFRELACORC, VENDEDOR, NOTAFISCAL 
                                                                                                  WHERE NFRELACORC.CDFILIAL     = OE.CDFILIAL 
                                                                                                    AND NFRELACORC.NRORCAEVENTO = OE.NRORCAEVENTO 
                                                                                                    AND NFRELACORC.NRSEQORCAEVE = OE.NRSEQORCAEVE  
                                                                                                    AND VENDEDOR.CDVENDEDOR     = OE.CDSUPVENDEDOR    
                                                                                                    AND NFRELACORC.IDORIGNFORCA = 'V' 
                                                                                                    AND NFRELACORC.CDFILIAL     = NOTAFISCAL.CDFILIAL 
                                                                                                    AND NFRELACORC.NRLANCTONF   = NOTAFISCAL.NRLANCTONF 
                                                                                                    AND (((OE.VRTOTALOREV > 0) AND (NOTAFISCAL.CDFORNECED = VENDEDOR.CDFORNECED)) 
                                                                                                     OR  ((OE.VRTOTALOREV < 0) AND (NOTAFISCAL.CDCLIENTE  = VENDEDOR.CDFORNECED))))) 
                                                     OR 
                                                     (P_IDGERACOMISSAOVEND = 'N' AND NOT EXISTS( SELECT 1 
                                                                                                   FROM PERCENTCOMISGERADA 
                                                                                                  WHERE CDFILIAL     = OE.CDFILIAL 
                                                                                                    AND NRORCAEVENTO = OE.NRORCAEVENTO 
                                                                                                    AND NRSEQORCAEVE = OE.NRSEQORCAEVE  
                                                                                                    AND CDVENDEDOR   = OE.CDSUPVENDEDOR ) )) 
 
                          ) 
                      OR (P_IDCOMISGERADA = 'S' AND ((P_IDGERACOMISSAOVEND = 'S' AND EXISTS (SELECT 1 
                                                                                               FROM NFRELACORC, VENDEDOR, NOTAFISCAL 
                                                                                              WHERE NFRELACORC.CDFILIAL     = OE.CDFILIAL 
                                                                                                AND NFRELACORC.NRORCAEVENTO = OE.NRORCAEVENTO 
                                                                                                AND NFRELACORC.NRSEQORCAEVE = OE.NRSEQORCAEVE  
                                                                                                AND VENDEDOR.CDVENDEDOR     = OE.CDSUPVENDEDOR    
                                                                                                AND NFRELACORC.IDORIGNFORCA = 'V' 
                                                                                                AND NFRELACORC.CDFILIAL     = NOTAFISCAL.CDFILIAL 
                                                                                                AND NFRELACORC.NRLANCTONF   = NOTAFISCAL.NRLANCTONF 
                                                                                                AND (((OE.VRTOTALOREV > 0) AND (NOTAFISCAL.CDFORNECED = VENDEDOR.CDFORNECED)) 
                                                                                                 OR  ((OE.VRTOTALOREV < 0) AND (NOTAFISCAL.CDCLIENTE  = VENDEDOR.CDFORNECED))))) 
                                                    OR 
                                                    (P_IDGERACOMISSAOVEND = 'N' AND EXISTS(SELECT 1 
                                                                                             FROM PERCENTCOMISGERADA 
                                                                                            WHERE CDFILIAL     = OE.CDFILIAL 
                                                                                              AND NRORCAEVENTO = OE.NRORCAEVENTO 
                                                                                              AND NRSEQORCAEVE = OE.NRSEQORCAEVE  
                                                                                              AND CDVENDEDOR   = OE.CDSUPVENDEDOR ))) 
                         )) 
                 GROUP BY CDFILIAL,    CDSUPVENDEDOR 
 
                 UNION 
                 
                SELECT CDFILIAL,    CDGERVENDEDOR CDVENDEDOR,  
                       SUM(VRTOTALOREV) VRTOTALOREV 
                  FROM ORCAEVENTO OE 
                 WHERE CDFILIAL = P_CDFILIAL  
                   AND ((INSTR(P_CDVENDEDOR,   CDGERVENDEDOR) > 0) OR (V_TCDVENDEDOR = 'T')) 
                   AND ((INSTR(P_CDTIPOEVENTO, CDTIPOEVENTO ) > 0) OR (V_TCDTIPOEVENTO  = 'T')) 
                   AND ((CASE P_IDDTBASECOMISSAO  
                         WHEN 'EVENTO'  
                         THEN TO_DATE(DTEVENTO)  
                         ELSE TO_DATE(DTFECHAORCA) END) BETWEEN TO_DATE(P_DTINICIAL) AND TO_DATE(P_DTFINAL)) 
                   AND IDSITUORCEVE   IN (3,4,5,6,7,0) 
                   AND ((P_IDCOMISGERADA = 'N' AND ((P_IDGERACOMISSAOVEND = 'S' AND NOT EXISTS (SELECT 1 
                                                                                                   FROM NFRELACORC, VENDEDOR, NOTAFISCAL 
                                                                                                  WHERE NFRELACORC.CDFILIAL     = OE.CDFILIAL 
                                                                                                    AND NFRELACORC.NRORCAEVENTO = OE.NRORCAEVENTO 
                                                                                                    AND NFRELACORC.NRSEQORCAEVE = OE.NRSEQORCAEVE  
                                                                                                    AND VENDEDOR.CDVENDEDOR     = OE.CDGERVENDEDOR
                                                                                                    AND NFRELACORC.IDORIGNFORCA = 'V' 
                                                                                                    AND NFRELACORC.CDFILIAL     = NOTAFISCAL.CDFILIAL 
                                                                                                    AND NFRELACORC.NRLANCTONF   = NOTAFISCAL.NRLANCTONF 
                                                                                                    AND (((OE.VRTOTALOREV > 0) AND (NOTAFISCAL.CDFORNECED = VENDEDOR.CDFORNECED)) 
                                                                                                     OR  ((OE.VRTOTALOREV < 0) AND (NOTAFISCAL.CDCLIENTE  = VENDEDOR.CDFORNECED))))) 
                                                     OR 
                                                     (P_IDGERACOMISSAOVEND = 'N' AND NOT EXISTS( SELECT 1 
                                                                                                   FROM PERCENTCOMISGERADA 
                                                                                                  WHERE CDFILIAL     = OE.CDFILIAL 
                                                                                                    AND NRORCAEVENTO = OE.NRORCAEVENTO 
                                                                                                    AND NRSEQORCAEVE = OE.NRSEQORCAEVE  
                                                                                                    AND CDVENDEDOR   = OE.CDGERVENDEDOR ) )) 
 
                          ) 
                      OR (P_IDCOMISGERADA = 'S' AND ((P_IDGERACOMISSAOVEND = 'S' AND EXISTS (SELECT 1 
                                                                                               FROM NFRELACORC, VENDEDOR, NOTAFISCAL 
                                                                                              WHERE NFRELACORC.CDFILIAL     = OE.CDFILIAL 
                                                                                                AND NFRELACORC.NRORCAEVENTO = OE.NRORCAEVENTO 
                                                                                                AND NFRELACORC.NRSEQORCAEVE = OE.NRSEQORCAEVE  
                                                                                                AND VENDEDOR.CDVENDEDOR     = OE.CDGERVENDEDOR    
                                                                                                AND NFRELACORC.IDORIGNFORCA = 'V' 
                                                                                                AND NFRELACORC.CDFILIAL     = NOTAFISCAL.CDFILIAL 
                                                                                                AND NFRELACORC.NRLANCTONF   = NOTAFISCAL.NRLANCTONF 
                                                                                                AND (((OE.VRTOTALOREV > 0) AND (NOTAFISCAL.CDFORNECED = VENDEDOR.CDFORNECED)) 
                                                                                                 OR  ((OE.VRTOTALOREV < 0) AND (NOTAFISCAL.CDCLIENTE  = VENDEDOR.CDFORNECED))))) 
                                                    OR 
                                                    (P_IDGERACOMISSAOVEND = 'N' AND EXISTS(SELECT 1 
                                                                                             FROM PERCENTCOMISGERADA 
                                                                                            WHERE CDFILIAL     = OE.CDFILIAL 
                                                                                              AND NRORCAEVENTO = OE.NRORCAEVENTO 
                                                                                              AND NRSEQORCAEVE = OE.NRSEQORCAEVE  
                                                                                              AND CDVENDEDOR   = OE.CDGERVENDEDOR ))) 
                         )) 
                 GROUP BY CDFILIAL,    CDGERVENDEDOR 
            ) OE
      WHERE OE.CDVENDEDOR IS NOT NULL
      GROUP BY OE. CDFILIAL, OE.CDVENDEDOR
    )  
    LOOP  
       
      FOR C_ORCAMENTOS IN 
        ( SELECT OE.CDFILIAL,    OE.NRORCAEVENTO, OE.NRSEQORCAEVE,  
                 OE.VRTOTALOREV, OE.DTFECHAORCA,  OE.DTEVENTO,  
                 OE.CDVENDEDOR,  VE.NMFANVEN,     VE.IDCARGOVEND, 
                 OE.CDCLIENTE,   OE.CDTIPOEVENTO, OE.CDTPRECEOREV 
            FROM (  SELECT CDFILIAL,    NRORCAEVENTO, NRSEQORCAEVE, CDVENDEDOR,  
                           VRTOTALOREV, DTFECHAORCA,  DTEVENTO,     CDTIPOEVENTO, 
                           CDCLIENTE,   CDTPRECEOREV 
                      FROM ORCAEVENTO 
                     WHERE CDFILIAL = C_TOTALORCAEVENTO.CDFILIAL  
                       AND CDVENDEDOR = C_TOTALORCAEVENTO.CDVENDEDOR 
                       AND ((INSTR(P_CDTIPOEVENTO, CDTIPOEVENTO) > 0) OR (V_TCDTIPOEVENTO  = 'T')) 
                       AND ((CASE P_IDDTBASECOMISSAO  
                             WHEN 'EVENTO'  
                             THEN TO_DATE(DTEVENTO)  
                             ELSE TO_DATE(DTFECHAORCA) END) BETWEEN TO_DATE(P_DTINICIAL) AND TO_DATE(P_DTFINAL)) 
                       AND IDSITUORCEVE   IN (3,4,5,6,7,0) 
                     UNION 
                    SELECT CDFILIAL,    NRORCAEVENTO, NRSEQORCAEVE, CDSUPVENDEDOR,  
                           VRTOTALOREV, DTFECHAORCA,  DTEVENTO,     CDTIPOEVENTO, 
                           CDCLIENTE,   CDTPRECEOREV 
                      FROM ORCAEVENTO 
                     WHERE CDFILIAL = C_TOTALORCAEVENTO.CDFILIAL  
                       AND CDSUPVENDEDOR = C_TOTALORCAEVENTO.CDVENDEDOR 
                       AND ((INSTR(P_CDTIPOEVENTO, CDTIPOEVENTO ) > 0) OR (V_TCDTIPOEVENTO  = 'T')) 
                       AND ((CASE P_IDDTBASECOMISSAO  
                             WHEN 'EVENTO'  
                             THEN TO_DATE(DTEVENTO)  
                             ELSE TO_DATE(DTFECHAORCA) END) BETWEEN TO_DATE(P_DTINICIAL) AND TO_DATE(P_DTFINAL)) 
                       AND IDSITUORCEVE   IN (3,4,5,6,7,0) 
                     UNION 
                    SELECT CDFILIAL,    NRORCAEVENTO, NRSEQORCAEVE, CDGERVENDEDOR,  
                           VRTOTALOREV, DTFECHAORCA,  DTEVENTO,     CDTIPOEVENTO, 
                           CDCLIENTE,   CDTPRECEOREV 
                      FROM ORCAEVENTO 
                     WHERE CDFILIAL = C_TOTALORCAEVENTO.CDFILIAL  
                       AND CDGERVENDEDOR = C_TOTALORCAEVENTO.CDVENDEDOR 
                       AND ((INSTR(P_CDTIPOEVENTO, CDTIPOEVENTO ) > 0) OR (V_TCDTIPOEVENTO  = 'T')) 
                       AND ((CASE P_IDDTBASECOMISSAO  
                             WHEN 'EVENTO'  
                             THEN TO_DATE(DTEVENTO)  
                             ELSE TO_DATE(DTFECHAORCA) END) BETWEEN TO_DATE(P_DTINICIAL) AND TO_DATE(P_DTFINAL)) 
                       AND IDSITUORCEVE   IN (3,4,5,6,7,0)) 
                 OE, VENDEDOR VE 
           WHERE OE.CDVENDEDOR = VE.CDVENDEDOR 
             AND OE.VRTOTALOREV <> 0 
             AND VE.CDFORNECED IS NOT NULL  
             AND ((P_IDCOMISGERADA = 'N' AND ((P_IDGERACOMISSAOVEND = 'S' AND NOT EXISTS (SELECT 1 
                                                                                           FROM NFRELACORC, VENDEDOR, NOTAFISCAL 
                                                                                          WHERE NFRELACORC.CDFILIAL     = OE.CDFILIAL 
                                                                                            AND NFRELACORC.NRORCAEVENTO = OE.NRORCAEVENTO 
                                                                                            AND NFRELACORC.NRSEQORCAEVE = OE.NRSEQORCAEVE  
                                                                                            AND VENDEDOR.CDVENDEDOR     = OE.CDVENDEDOR    
                                                                                            AND NFRELACORC.IDORIGNFORCA = 'V' 
                                                                                            AND NFRELACORC.CDFILIAL     = NOTAFISCAL.CDFILIAL 
                                                                                            AND NFRELACORC.NRLANCTONF   = NOTAFISCAL.NRLANCTONF 
                                                                                            AND (((OE.VRTOTALOREV > 0) AND (NOTAFISCAL.CDFORNECED = VENDEDOR.CDFORNECED)) 
                                                                                             OR  ((OE.VRTOTALOREV < 0) AND (NOTAFISCAL.CDCLIENTE  = VENDEDOR.CDFORNECED))))) 
                                             OR 
                                             (P_IDGERACOMISSAOVEND = 'N' AND NOT EXISTS( SELECT 1 
                                                                                           FROM PERCENTCOMISGERADA 
                                                                                          WHERE CDFILIAL     = OE.CDFILIAL 
                                                                                            AND NRORCAEVENTO = OE.NRORCAEVENTO 
                                                                                            AND NRSEQORCAEVE = OE.NRSEQORCAEVE  
                                                                                            AND CDVENDEDOR   = OE.CDVENDEDOR ) )) 
 
                  ) 
              OR (P_IDCOMISGERADA = 'S' AND ((P_IDGERACOMISSAOVEND = 'S' AND EXISTS (SELECT 1 
                                                                                       FROM NFRELACORC, VENDEDOR, NOTAFISCAL 
                                                                                      WHERE NFRELACORC.CDFILIAL     = OE.CDFILIAL 
                                                                                        AND NFRELACORC.NRORCAEVENTO = OE.NRORCAEVENTO 
                                                                                        AND NFRELACORC.NRSEQORCAEVE = OE.NRSEQORCAEVE  
                                                                                        AND VENDEDOR.CDVENDEDOR     = OE.CDVENDEDOR    
                                                                                        AND NFRELACORC.IDORIGNFORCA = 'V' 
                                                                                        AND NFRELACORC.CDFILIAL     = NOTAFISCAL.CDFILIAL 
                                                                                        AND NFRELACORC.NRLANCTONF   = NOTAFISCAL.NRLANCTONF 
                                                                                        AND (((OE.VRTOTALOREV > 0) AND (NOTAFISCAL.CDFORNECED = VENDEDOR.CDFORNECED)) 
                                                                                         OR  ((OE.VRTOTALOREV < 0) AND (NOTAFISCAL.CDCLIENTE  = VENDEDOR.CDFORNECED))))) 
                                            OR 
                                            (P_IDGERACOMISSAOVEND = 'N' AND EXISTS(SELECT 1 
                                                                                     FROM PERCENTCOMISGERADA 
                                                                                    WHERE CDFILIAL     = OE.CDFILIAL 
                                                                                      AND NRORCAEVENTO = OE.NRORCAEVENTO 
                                                                                      AND NRSEQORCAEVE = OE.NRSEQORCAEVE  
                                                                                      AND CDVENDEDOR     = OE.CDVENDEDOR ))) 
                 )) 
        ) 
        LOOP 
           
          IF (P_IDCOMISGERADA = 'N') THEN 
             
            BEGIN 
              SELECT CS.VRCOMISSAO 
                INTO V_VRPERCCOMIS 
                FROM (  SELECT PO.VRCOMISSAO 
                          FROM (  SELECT FA.VRCOMISSAO, CP.NRCOMISSAOPROMOREV SUBCOMISSAO 
                                    FROM COMISSAOOREV CO, COMISSAOPROMOREV CP, FAIXACOMISSAOOREV FA 
                                   WHERE CO.CDFILIAL            = C_ORCAMENTOS.CDFILIAL 
                                     AND CO.DTVALIDADEINI      <= SYSDATE 
                                     AND CO.DTVALIDADEFIN      >= SYSDATE 
                                     AND CO.NRCOMISSAOOREV      = CP.NRCOMISSAOOREV 
                                     AND CP.DTVALINICOMISPROMO <= C_ORCAMENTOS.DTFECHAORCA 
                                     AND CP.DTVALFINCOMISPROMO >= C_ORCAMENTOS.DTFECHAORCA 
                                     AND CP.IDCARGOVEND         = C_ORCAMENTOS.IDCARGOVEND 
                                     AND CP.CDTIPOEVENTO        = C_ORCAMENTOS.CDTIPOEVENTO 
                                     AND CO.NRCOMISSAOOREV      = FA.NRCOMISSAOOREV 
                                     AND FA.IDTIPOCOMISSAO      = 'PROMOCAO' 
                                     AND FA.NRCOMISSAOPROMOREV  = CP.NRCOMISSAOPROMOREV 
                                     AND CP.IDDTBASECOMISPROMO  = 'DATAFECHAMENTO' 
                                     AND FA.VRINICIAL          <= C_ORCAMENTOS.VRTOTALOREV 
                                     AND FA.VRFINAL            >= C_ORCAMENTOS.VRTOTALOREV 
                                  UNION 
                                  SELECT FA.VRCOMISSAO, CP.NRCOMISSAOPROMOREV SUBCOMISSAO 
                                    FROM COMISSAOOREV CO, COMISSAOPROMOREV CP, FAIXACOMISSAOOREV FA 
                                   WHERE CO.CDFILIAL            = C_ORCAMENTOS.CDFILIAL 
                                     AND CO.DTVALIDADEINI      <= SYSDATE 
                                     AND CO.DTVALIDADEFIN      >= SYSDATE 
                                     AND CO.NRCOMISSAOOREV      = CP.NRCOMISSAOOREV 
                                     AND CP.DTVALINICOMISPROMO <= C_ORCAMENTOS.DTEVENTO 
                                     AND CP.DTVALFINCOMISPROMO >= C_ORCAMENTOS.DTEVENTO 
                                     AND CP.IDCARGOVEND         = C_ORCAMENTOS.IDCARGOVEND 
                                     AND CP.CDTIPOEVENTO        = C_ORCAMENTOS.CDTIPOEVENTO 
                                     AND CO.NRCOMISSAOOREV      = FA.NRCOMISSAOOREV 
                                     AND FA.IDTIPOCOMISSAO      = 'PROMOCAO' 
                                     AND FA.NRCOMISSAOPROMOREV  = CP.NRCOMISSAOPROMOREV 
                                     AND CP.IDDTBASECOMISPROMO  = 'DATAEVENTO' 
                                     AND FA.VRINICIAL          <= C_ORCAMENTOS.VRTOTALOREV 
                                     AND FA.VRFINAL            >= C_ORCAMENTOS.VRTOTALOREV 
                                  ORDER BY SUBCOMISSAO DESC 
                               ) PO 
                          WHERE ROWNUM = 1 
                        UNION ALL 
                        SELECT FA.VRCOMISSAO 
                          FROM COMISSAOOREV CO, COMISSAOTPEVENTOREV CT, FAIXACOMISSAOOREV FA 
                         WHERE CO.CDFILIAL              = C_ORCAMENTOS.CDFILIAL 
                           AND CT.CDTIPOEVENTO          = C_ORCAMENTOS.CDTIPOEVENTO 
                           AND CO.DTVALIDADEINI        <= SYSDATE 
                           AND CO.DTVALIDADEFIN        >= SYSDATE 
                           AND CO.NRCOMISSAOOREV        = CT.NRCOMISSAOOREV 
                           AND CT.IDCARGOVEND           = C_ORCAMENTOS.IDCARGOVEND 
                           AND CO.NRCOMISSAOOREV        = FA.NRCOMISSAOOREV 
                           AND FA.IDTIPOCOMISSAO        = 'TIPOEVENTO' 
                           AND FA.NRCOMISSAOTPEVENTOREV = CT.NRCOMISSAOTPEVENTOREV 
                           AND FA.VRINICIAL            <= C_TOTALORCAEVENTO.VRTOTALOREV 
                           AND FA.VRFINAL              >= C_TOTALORCAEVENTO.VRTOTALOREV 
                        UNION ALL 
                        SELECT FA.VRCOMISSAO 
                          FROM COMISSAOOREV CO, COMISSAOCARGOOREV CA, FAIXACOMISSAOOREV FA 
                         WHERE CO.CDFILIAL            = C_ORCAMENTOS.CDFILIAL 
                           AND CO.NRCOMISSAOOREV      = CA.NRCOMISSAOOREV 
                           AND CO.DTVALIDADEINI      <= SYSDATE 
                           AND CO.DTVALIDADEFIN      >= SYSDATE 
                           AND CA.IDCARGOVEND         = C_ORCAMENTOS.IDCARGOVEND 
                           AND FA.NRCOMISSAOCARGOOREV = CA.NRCOMISSAOCARGOOREV 
                           AND FA.IDTIPOCOMISSAO      = 'CARGO' 
                           AND FA.VRINICIAL          <= C_TOTALORCAEVENTO.VRTOTALOREV 
                           AND FA.VRFINAL            >= C_TOTALORCAEVENTO.VRTOTALOREV 
                     ) CS 
               WHERE ROWNUM = 1; 
            EXCEPTION 
              WHEN NO_DATA_FOUND THEN 
                V_VRPERCCOMIS := 0;  
            END; 
             
            V_VRCOMISSAO := C_ORCAMENTOS.VRTOTALOREV * (NVL(V_VRPERCCOMIS, 0) / 100); 
           
             
          ELSIF (P_IDCOMISGERADA = 'S') THEN 
            IF P_IDGERACOMISSAOVEND = 'S' THEN 
              BEGIN 
                SELECT VRPERCCOMISSAO 
                  INTO V_VRPERCCOMIS 
                  FROM PERCENTCOMISGERADA 
                 WHERE CDFILIAL     = C_ORCAMENTOS.CDFILIAL 
                   AND NRORCAEVENTO = C_ORCAMENTOS.NRORCAEVENTO 
                   AND NRSEQORCAEVE = C_ORCAMENTOS.NRSEQORCAEVE 
                   AND CDVENDEDOR   = C_ORCAMENTOS.CDVENDEDOR;  
              EXCEPTION 
                WHEN NO_DATA_FOUND THEN 
                  V_VRPERCCOMIS := 0; 
              END; 
 
              BEGIN 
                SELECT CDFORNECED 
                  INTO V_CDFORNECED 
                  FROM VENDEDOR 
                 WHERE CDVENDEDOR = C_ORCAMENTOS.CDVENDEDOR; 
                IF (C_ORCAMENTOS.VRTOTALOREV > 0) THEN  
                  SELECT NOTAFISCAL.VRNOTAFISC 
                    INTO V_VRCOMISSAO 
                    FROM NFRELACORC, NOTAFISCAL 
                   WHERE NFRELACORC.CDFILIAL     = C_ORCAMENTOS.CDFILIAL 
                     AND NFRELACORC.NRORCAEVENTO = C_ORCAMENTOS.NRORCAEVENTO 
                     AND NFRELACORC.NRSEQORCAEVE = C_ORCAMENTOS.NRSEQORCAEVE  
                     AND NFRELACORC.IDORIGNFORCA = 'V' 
                     AND NFRELACORC.CDFILIAL     = NOTAFISCAL.CDFILIAL 
                     AND NFRELACORC.NRLANCTONF   = NOTAFISCAL.NRLANCTONF 
                     AND NOTAFISCAL.CDFORNECED   = V_CDFORNECED; 
                ELSIF (C_ORCAMENTOS.VRTOTALOREV < 0) THEN 
                  SELECT NOTAFISCAL.VRNOTAFISC 
                    INTO V_VRCOMISSAO 
                    FROM NFRELACORC, NOTAFISCAL, CLIENTE 
                   WHERE NFRELACORC.CDFILIAL     = C_ORCAMENTOS.CDFILIAL 
                     AND NFRELACORC.NRORCAEVENTO = C_ORCAMENTOS.NRORCAEVENTO 
                     AND NFRELACORC.NRSEQORCAEVE = C_ORCAMENTOS.NRSEQORCAEVE  
                     AND NFRELACORC.IDORIGNFORCA = 'V' 
                     AND NFRELACORC.CDFILIAL     = NOTAFISCAL.CDFILIAL 
                     AND NFRELACORC.NRLANCTONF   = NOTAFISCAL.NRLANCTONF 
                     AND CLIENTE.CDCLIENTE       = V_CDFORNECED 
                     AND NOTAFISCAL.CDCLIENTE    = CLIENTE.CDCLIENTE; 
                END IF; 
              EXCEPTION 
                WHEN NO_DATA_FOUND THEN 
                  V_VRCOMISSAO := 0;  
              END; 
 
              IF (C_ORCAMENTOS.VRTOTALOREV < 0) THEN 
                V_VRCOMISSAO := (V_VRCOMISSAO * -1); 
              END IF;           
            ELSIF P_IDGERACOMISSAOVEND = 'N' THEN 
              BEGIN 
                SELECT PERCENTCOMISGERADA.VRCOMISGERADA, PERCENTCOMISGERADA.VRPERCCOMISSAO 
                  INTO V_VRCOMISSAO, V_VRPERCCOMIS 
                  FROM PERCENTCOMISGERADA 
                 WHERE PERCENTCOMISGERADA.CDFILIAL     = C_ORCAMENTOS.CDFILIAL 
                   AND PERCENTCOMISGERADA.NRORCAEVENTO = C_ORCAMENTOS.NRORCAEVENTO 
                   AND PERCENTCOMISGERADA.NRSEQORCAEVE = C_ORCAMENTOS.NRSEQORCAEVE 
                   AND PERCENTCOMISGERADA.CDVENDEDOR   = C_ORCAMENTOS.CDVENDEDOR; 
              EXCEPTION 
                WHEN NO_DATA_FOUND THEN 
                  V_VRCOMISSAO := 0;            
              END;          
            END IF; 
          END IF;   
 
          OUT_RECORD.CDFILIAL     := C_ORCAMENTOS.CDFILIAL; 
          OUT_RECORD.NRORCAEVENTO := C_ORCAMENTOS.NRORCAEVENTO; 
          OUT_RECORD.NRSEQORCAEVE := C_ORCAMENTOS.NRSEQORCAEVE; 
          OUT_RECORD.CDCLIENTE    := C_ORCAMENTOS.CDCLIENTE; 
          OUT_RECORD.CDVENDEDOR   := C_ORCAMENTOS.CDVENDEDOR; 
          OUT_RECORD.CDTIPOEVENTO := C_ORCAMENTOS.CDTIPOEVENTO; 
          OUT_RECORD.DTEVENTO     := C_ORCAMENTOS.DTEVENTO; 
          OUT_RECORD.DTFECHAORCA  := C_ORCAMENTOS.DTFECHAORCA; 
          OUT_RECORD.VRTOTALOREV  := C_ORCAMENTOS.VRTOTALOREV; 
          OUT_RECORD.VRPERCCOMIS  := NVL(V_VRPERCCOMIS, 0); 
          OUT_RECORD.VRCOMISSAO  := V_VRCOMISSAO; 
                       
          BEGIN 
            SELECT IDTPRECEOREV 
              INTO V_IDTPRECEOREV 
              FROM TPRECEORCEVE 
             WHERE CDTPRECEOREV = C_ORCAMENTOS.CDTPRECEOREV; 
            OUT_RECORD.IDTPRECEOREV  := V_IDTPRECEOREV;      
          EXCEPTION 
            WHEN NO_DATA_FOUND THEN 
                OUT_RECORD.IDTPRECEOREV := 'C'; 
          END;         
 
          PIPE ROW (OUT_RECORD); 
        END LOOP; 
    END LOOP;   
END;
/

-- VERSAO --
BEGIN ATUVEROBJ('COMISSAO_ORE','0.00.000'); END;
/