CREATE OR REPLACE FUNCTION CONSULTA_PRECO_ITEMPEDIDO(P_CDFILIAL      IN FILIAL.CDFILIAL%TYPE,
                                                     P_CDPRODUTO     IN PRODUTO.CDPRODUTO%TYPE,
                                                     P_CDCLIENTE     IN CLIENTE.CDCLIENTE%TYPE,
                                                     P_CDPRECOBASE   IN PRECOBASE.CDPRECOBASE%TYPE,
                                                     P_DTENTITPED    IN ITEMPEDIDO.DTENTITPED%TYPE,
                                                     P_CDMODPGTO     IN MODPGTO.CDMODPGTO%TYPE) RETURN PRECOITEMPED_TABLE PIPELINED
IS
   V_VRTABPRECO   ITEMPREBASE.VRITEPREBASE%TYPE;
   V_VRITEPREBASE ITEMPREBASE.VRITEPREBASE%TYPE;
   V_VRPEADPBASE  ITEMPREBASE.VRITEPREBASE%TYPE;
   V_VRPRECOBASE  ITEMPREBASE.VRITEPREBASE%TYPE;
   V_IDITPEDDIFER  ITEMPEDIDO.IDITPEDDIFER%TYPE;
   V_NRCASADECVRUNIT PARAMPEDIDO.NRCASADECVRUNIT%TYPE;
   V_RESULT       ITEMPREBASE.VRITEPREBASE%TYPE;
   OUT_RECORD PRECOITEMPED_TYPE := PRECOITEMPED_TYPE (NULL, NULL, NULL, NULL,
                                                      NULL, NULL, NULL, NULL);
BEGIN
   SELECT NVL(MAX(NRCASADECVRUNIT),2)
     INTO V_NRCASADECVRUNIT
     FROM PARAMPEDIDO
    WHERE CDFILIAL = P_CDFILIAL;
   SELECT NVL(MAX(VRPEADPBASE),0)
     INTO V_VRPEADPBASE
     FROM PRECOUTI
    WHERE ((CDFILIAL   = P_CDFILIAL   ) OR (CDFILIAL IS NULL))
      AND (CDMODPGTO   = P_CDMODPGTO  )
      AND (CDPRECOBASE = P_CDPRECOBASE);
   --tabela de preco por cliente
   SELECT  NVL(MAX(T.VRPRECOPROD), 0) AS VRITEPREBASE
     INTO V_VRITEPREBASE
     FROM TABPRECLIE T
    WHERE (T.CDPRODUTO = P_CDPRODUTO)
      AND (T.CDCLIENTE = P_CDCLIENTE)
      AND (
           ( (SYSDATE BETWEEN NVL(T.DTVALINI,SYSDATE) AND NVL(T.DTVALFIN,SYSDATE))OR
             ((SYSDATE >= NVL(T.DTVALINI,SYSDATE))AND(T.DTVALFIN IS NULL))
           )
          )
      AND T.VRPRECOPROD > 0;
   IF NOT(V_VRITEPREBASE > 0) THEN
      --tabela promocional por grupo de clientes e produto
      SELECT NVL(MAX(VRPROMOPRODCLIE), 0) AS VRITEPREBASE
        INTO V_VRITEPREBASE
        FROM PROMOPRODCLIE PC, PRODUTO P, CLIENTE C
       WHERE (P.CDPRODUTO   = P_CDPRODUTO )
         AND (INSTR(P.CDARVPROD, TRIM(REPLACE(PC.CDARVGRUPPROD, '.', ''))) = 1)
         AND (C.CDCLIENTE = P_CDCLIENTE)
         AND (IDPROMOATIVO  = 'S')
         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))
               )
              )
         AND VRPROMOPRODCLIE > 0;
      IF NOT(V_VRITEPREBASE > 0) THEN
          --tabela de preco
         SELECT NVL(MAX(I.VRITEPREBASE), 0) VRITEPREBASE
           INTO V_VRPRECOBASE
           FROM ITEMPREBASE I
          WHERE (I.CDPRECOBASE     = P_CDPRECOBASE)
            AND (I.CDPRODUTO       = P_CDPRODUTO  )
            AND ((TO_CHAR(TO_DATE(P_DTENTITPED),'DD/MM/YYYY') BETWEEN I.DTINIVALIPB AND I.DTFIMVALIPB) OR
                 (I.DTINIVALIPB <= TO_CHAR(TO_DATE(P_DTENTITPED),'DD/MM/YYYY') AND I.DTFIMVALIPB IS NULL))
            AND (I.VRITEPREBASE > 0);
         IF (V_VRPRECOBASE > 0) THEN
            --promocao vingente na tabela de preco
            SELECT DECODE(MAX(IDSTATUSPROMO),'AT',NVL(MAX(VRTOTALPROMO),0),NVL(MAX(VRITEPREBASE),0)) AS VRITEPREBASE
              INTO V_VRITEPREBASE
              FROM ITEMPREBASE I, PROMOPRBASE PP
             WHERE (I.CDPRECOBASE     = P_CDPRECOBASE)
               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(P_DTENTITPED),'DD/MM/YYYY') BETWEEN PP.DTINIPROMO AND PP.DTFINPROMO)
               AND ((PP.IDDIASPROMO =(TO_CHAR(TO_DATE(P_DTENTITPED),'D')))OR(PP.IDDIASPROMO ='8'))
               AND (I.VRITEPREBASE > 0);
            IF NOT (V_VRITEPREBASE > 0) THEN
               V_VRTABPRECO := V_VRPRECOBASE;
               V_RESULT := ROUND((V_VRTABPRECO + ((V_VRPEADPBASE) * (V_VRTABPRECO)) / 100),V_NRCASADECVRUNIT);
               OUT_RECORD.CDFILIAL     := P_CDFILIAL;
               OUT_RECORD.CDPRODUTO     := P_CDPRODUTO;
               OUT_RECORD.CDCLIENTE    := P_CDCLIENTE;
               OUT_RECORD.CDPRECOBASE  := P_CDPRECOBASE;
               OUT_RECORD.DTENTITPED   := P_DTENTITPED;
               OUT_RECORD.CDMODPGTO    := P_CDMODPGTO;
               OUT_RECORD.VRPRECOBASE  := V_RESULT;
               OUT_RECORD.IDITPEDDIFER := 'P';
               PIPE ROW (OUT_RECORD);
            ELSE
               V_VRTABPRECO := V_VRITEPREBASE;
               V_RESULT := ROUND((V_VRTABPRECO + ((V_VRPEADPBASE) * (V_VRTABPRECO)) / 100),V_NRCASADECVRUNIT);
               OUT_RECORD.CDFILIAL     := P_CDFILIAL;
               OUT_RECORD.CDPRODUTO     := P_CDPRODUTO;
               OUT_RECORD.CDCLIENTE    := P_CDCLIENTE;
               OUT_RECORD.CDPRECOBASE  := P_CDPRECOBASE;
               OUT_RECORD.DTENTITPED   := P_DTENTITPED;
               OUT_RECORD.CDMODPGTO    := P_CDMODPGTO;
               OUT_RECORD.VRPRECOBASE  := V_RESULT;
               OUT_RECORD.IDITPEDDIFER := 'N';
               PIPE ROW (OUT_RECORD);
            END IF;
         ELSE
            OUT_RECORD.CDFILIAL     := P_CDFILIAL;
            OUT_RECORD.CDPRODUTO    := P_CDPRODUTO;
            OUT_RECORD.CDCLIENTE    := P_CDCLIENTE;
            OUT_RECORD.CDPRECOBASE  := P_CDPRECOBASE;
            OUT_RECORD.DTENTITPED   := P_DTENTITPED;
            OUT_RECORD.CDMODPGTO    := P_CDMODPGTO;
            OUT_RECORD.VRPRECOBASE  := 0;
            OUT_RECORD.IDITPEDDIFER := '';
            PIPE ROW (OUT_RECORD);
         END IF;
      ELSE
         V_VRTABPRECO := V_VRITEPREBASE;
         V_RESULT := ROUND((V_VRTABPRECO + ((V_VRPEADPBASE) * (V_VRTABPRECO)) / 100),V_NRCASADECVRUNIT);
         OUT_RECORD.CDFILIAL     := P_CDFILIAL;
         OUT_RECORD.CDPRODUTO     := P_CDPRODUTO;
         OUT_RECORD.CDCLIENTE    := P_CDCLIENTE;
         OUT_RECORD.CDPRECOBASE  := P_CDPRECOBASE;
         OUT_RECORD.DTENTITPED   := P_DTENTITPED;
         OUT_RECORD.CDMODPGTO    := P_CDMODPGTO;
         OUT_RECORD.VRPRECOBASE  := V_RESULT;
         OUT_RECORD.IDITPEDDIFER := 'G';
         PIPE ROW (OUT_RECORD);
      END IF;
   ELSE
      V_VRTABPRECO := V_VRITEPREBASE;
      V_RESULT := ROUND((V_VRTABPRECO + ((V_VRPEADPBASE) * (V_VRTABPRECO)) / 100),V_NRCASADECVRUNIT);
      OUT_RECORD.CDFILIAL     := P_CDFILIAL;
      OUT_RECORD.CDPRODUTO    := P_CDPRODUTO;
      OUT_RECORD.CDCLIENTE    := P_CDCLIENTE;
      OUT_RECORD.CDPRECOBASE  := P_CDPRECOBASE;
      OUT_RECORD.DTENTITPED   := P_DTENTITPED;
      OUT_RECORD.CDMODPGTO    := P_CDMODPGTO;
      OUT_RECORD.VRPRECOBASE  := V_RESULT;
      OUT_RECORD.IDITPEDDIFER := 'S';
      PIPE ROW (OUT_RECORD);
   END IF;
END;