CREATE OR REPLACE FUNCTION APLICA_PAR_PRECO(
   P_CDFILIAL     IN ITEMPEDIDO.CDFILIAL%TYPE,
   P_NRPEDIDO     IN ITEMPEDIDO.NRPEDIDO%TYPE,
   P_NRITEMPED    IN ITEMPEDIDO.NRITEMPED%TYPE,
   P_VRPRTABITPED IN ITEMPEDIDO.VRPRTABITPED%TYPE) RETURN NUMBER IS

   V_VRTABPRECO   NUMBER;
   V_CDMODPGTO    VARCHAR2(5);
   V_CDPRECOBASE  VARCHAR2(2);
   V_CDCLIENTE    VARCHAR2(21);
   V_DTENTPED     DATE;
   V_CDARVPROD    VARCHAR2(20);
   V_VRITEPREBASE NUMBER;
   V_CDPRODUTO    VARCHAR2(10);
   V_DTINIVALIPB  DATE;
   V_DTFIMVALIPB  DATE;
   V_IDPRECOPROM  VARCHAR2(02);
   V_IDITPEDDIFER VARCHAR2(01);
   V_VRPEADPBASE NUMBER;

   V_RESULT      NUMBER;


BEGIN
   V_VRTABPRECO := P_VRPRTABITPED;
   SELECT MAX(CDMODPGTO), MAX(CDPRECOBASE), MAX(CDCLIENTE), MAX(DTENTPED)
     INTO V_CDMODPGTO, V_CDPRECOBASE, V_CDCLIENTE, V_DTENTPED
     FROM PEDIDO
    WHERE CDFILIAL = P_CDFILIAL
      AND NRPEDIDO = P_NRPEDIDO;

   IF ((P_VRPRTABITPED = 0) OR (P_VRPRTABITPED IS NULL)) THEN
      SELECT MAX(P.CDARVPROD)
        INTO V_CDARVPROD
        FROM ITEMPEDIDO I, PRODUTO P
       WHERE I.CDFILIAL = P_CDFILIAL
         AND I.NRPEDIDO = P_NRPEDIDO
         AND I.NRITEMPED = P_NRITEMPED
         AND I.CDPRODUTO = P.CDPRODUTO;

      SELECT DECODE(MAX(IDSTATUSPROMO),'AT',MAX(VRTOTALPROMO),MAX(VRITEPREBASE)) AS VRITEPREBASE,
             MAX(CDPRODUTO), MAX(DTINIVALIPB),MAX(DTFIMVALIPB),
             DECODE(MAX(IDSTATUSPROMO),'AT','S','N') AS IDPRECOPROM,
             MAX(IDITPEDDIFER)
        INTO V_VRITEPREBASE, V_CDPRODUTO,  V_DTINIVALIPB, V_DTFIMVALIPB,
             V_IDPRECOPROM, V_IDITPEDDIFER
        FROM (--tabela de preco por cliente
             SELECT 1 AS ORDEM, MAX(T.VRPRECOPROD) AS VRITEPREBASE, MAX(P.CDARVPROD) CDARVPROD, MAX(P.CDPRODUTO) CDPRODUTO,
                    '' AS CDPRECOBASE,NULL AS DTINIVALIPB,NULL AS DTFIMVALIPB,
                    '' AS IDTIPOPROMO,NULL AS DTINIPROMO,NULL AS DTFINPROMO,
                    '' IDDIASPROMO, 0 AS VRTOTALPROMO,'' AS IDSTATUSPROMO,
                    'S' IDITPEDDIFER
               FROM TABPRECLIE T,PRODUTO P
              WHERE (P.CDARVPROD = V_CDARVPROD)
                AND (P.CDPRODUTO = T.CDPRODUTO)
                AND (T.CDCLIENTE = V_CDCLIENTE)
                AND (
                     ( (SYSDATE BETWEEN NVL(T.DTVALINI,SYSDATE) AND NVL(T.DTVALFIN,SYSDATE))OR
                       ((SYSDATE >= NVL(T.DTVALINI,SYSDATE))AND(T.DTVALFIN IS NULL))
                     )
                    )
             UNION
             --tabela promocional por grupo de clientes e produto
             SELECT 2 AS ORDEM, MAX(VRPROMOPRODCLIE) AS VRITEPREBASE, MAX(P.CDARVPROD) CDARVPROD, MAX(P.CDPRODUTO) CDPRODUTO,
                    '' AS CDPRECOBASE, NULL AS DTINIVALIPB,NULL AS DTFIMVALIPB,
                    '' AS IDTIPOPROMO, MAX(DTPROMOINICIAL) AS DTINIVALIPB, MAX(DTPROMOFINAL) AS DTFINPROMO,
                    '' IDDIASPROMO, MAX(VRPROMOPRODCLIE) AS VRTOTALPROMO, DECODE(MAX(IDPROMOATIVO), 'C', 'AT', 'IN') AS IDSTATUSPROMO,
                    'G' IDITPEDDIFER
               FROM PROMOPRODCLIE PC, PRODUTO P, CLIENTE C
              WHERE (P.CDARVPROD   = V_CDARVPROD )
                AND (INSTR(P.CDARVPROD, TRIM(REPLACE(PC.CDARVGRUPPROD, '.', ''))) = 1)
                AND (C.CDCLIENTE = V_CDCLIENTE)
                AND (INSTR(C.NRINSJURCLIE, TRIM(REPLACE(PC.NRINSGRUPCLIE, '.',''))) = 1)
                AND (
                     ( (SYSDATE BETWEEN NVL(PC.DTPROMOINICIAL,SYSDATE) AND NVL(PC.DTPROMOFINAL,SYSDATE))OR
                       ((SYSDATE >= NVL(PC.DTPROMOINICIAL,SYSDATE))AND(PC.DTPROMOFINAL IS NULL))
                      )
                     )
             UNION
             --promocao vingente na tabela de preco
             SELECT 3 AS ORDEM,MAX(I.VRITEPREBASE) VRITEPREBASE, MAX(P.CDARVPROD) CDARVPROD, MAX(P.CDPRODUTO) CDPRODUTO,
                    MAX(I.CDPRECOBASE) CDPRECOBASE, MAX(I.DTINIVALIPB) DTINIVALIPB, MAX(I.DTFIMVALIPB) DTFIMVALIPB,
                    MAX(PP.IDTIPOPROMO) IDTIPOPROMO,MAX(PP.DTINIPROMO) DTINIPROMO, MAX(PP.DTFINPROMO) DTFINPROMO,
                    MAX(PP.IDDIASPROMO) IDDIASPROMO, MAX(PP.VRTOTALPROMO) VRTOTALPROMO, MAX(PP.IDSTATUSPROMO) IDSTATUSPROMO,
                    'P' IDITPEDDIFER
               FROM ITEMPREBASE I, PRODUTO P,PROMOPRBASE PP
              WHERE (I.CDPRECOBASE     = V_CDPRECOBASE)
                AND (P.CDARVPROD       = V_CDARVPROD  )
                AND (I.CDPRODUTO       = P.CDPRODUTO   )
                AND (PP.CDPRECOBASE    = I.CDPRECOBASE )
                AND (PP.CDPRODUTO      = I.CDPRODUTO   )
                AND (PP.DTINIVALIPB    = I.DTINIVALIPB )
                AND (TO_CHAR(TO_DATE(V_DTENTPED),'DD/MM/YYYY') BETWEEN PP.DTINIPROMO AND PP.DTFINPROMO)
                AND ((PP.IDDIASPROMO =(TO_CHAR(TO_DATE(V_DTENTPED),'D')))OR(PP.IDDIASPROMO ='8'))
             UNION
             --tabela de preco
             SELECT 4 AS ORDEM,MAX(I.VRITEPREBASE) VRITEPREBASE, MAX(P.CDARVPROD) CDARVPROD, MAX(P.CDPRODUTO) CDPRODUTO,
                    MAX(I.CDPRECOBASE) CDPRECOBASE, MAX(I.DTINIVALIPB) DTINIVALIPB, MAX(I.DTFIMVALIPB) DTFIMVALIPB,
                    '' AS IDTIPOPROMO,NULL AS DTINIPROMO,NULL AS DTFINPROMO,
                    '' IDDIASPROMO, 0 AS VRTOTALPROMO,'' AS IDSTATUSPROMO,
                    'N' IDITPEDDIFER
               FROM ITEMPREBASE I, PRODUTO P
              WHERE (I.CDPRECOBASE     = V_CDPRECOBASE)
                AND (P.CDARVPROD       = V_CDARVPROD  )
                AND (I.CDPRODUTO       = P.CDPRODUTO   )
                AND ((TO_CHAR(TO_DATE(V_DTENTPED),'DD/MM/YYYY') BETWEEN I.DTINIVALIPB AND I.DTFIMVALIPB) OR
                     (I.DTINIVALIPB <= TO_CHAR(TO_DATE(V_DTENTPED),'DD/MM/YYYY') AND I.DTFIMVALIPB IS NULL))
             ORDER BY ORDEM
            );

      V_VRTABPRECO := V_VRITEPREBASE;
   END IF;

   SELECT MAX(NVL(VRPEADPBASE,0))
     INTO V_VRPEADPBASE
     FROM PRECOUTI
    WHERE ((CDFILIAL   = P_CDFILIAL   ) OR (CDFILIAL IS NULL))
      AND (CDMODPGTO   = V_CDMODPGTO  )
      AND (CDPRECOBASE = V_CDPRECOBASE);

   V_RESULT := ROUND((V_VRTABPRECO + ((V_VRPEADPBASE) * (V_VRTABPRECO)) / 100),2);

   RETURN(V_RESULT);
END;