create or replace FUNCTION BUSCA_IDITPEDDIFER_PED(
   P_CDFILIAL      IN FILIAL.CDFILIAL%TYPE,
   P_CDARVPROD     IN PRODUTO.CDARVPROD%TYPE,
   P_CDCLIENTE     IN CLIENTE.CDCLIENTE%TYPE,
   P_CDPRECOBASE   IN PRECOBASE.CDPRECOBASE%TYPE,
   P_DTENTPED      IN PEDIDO.DTENTPED%TYPE,
   P_CDMODPGTO     IN MODPGTO.CDMODPGTO%TYPE) RETURN VARCHAR2 IS

   V_VRITEPREBASE ITEMPREBASE.VRITEPREBASE%TYPE;
   V_VRPRECOBASE  ITEMPREBASE.VRITEPREBASE%TYPE;
   V_RESULT       ITEMPEDIDO.IDITPEDDIFER%TYPE;
   V_IDITPEDDIFER ITEMPEDIDO.IDITPEDDIFER%TYPE;
BEGIN
   --tabela de preco por cliente
   SELECT  NVL(MAX(T.VRPRECOPROD), 0) AS VRITEPREBASE
     INTO V_VRITEPREBASE
     FROM TABPRECLIE T,PRODUTO P
    WHERE (P.CDARVPROD = P_CDARVPROD)
      AND (P.CDPRODUTO = T.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.CDARVPROD   = P_CDARVPROD )
         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, PRODUTO P
          WHERE (I.CDPRECOBASE     = P_CDPRECOBASE)
            AND (P.CDARVPROD       = P_CDARVPROD  )
            AND (I.CDPRODUTO       = P.CDPRODUTO   )
            AND ((TO_CHAR(TO_DATE(P_DTENTPED),'DD/MM/YYYY') BETWEEN I.DTINIVALIPB AND I.DTFIMVALIPB) OR
                 (I.DTINIVALIPB <= TO_CHAR(TO_DATE(P_DTENTPED),'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, PRODUTO P,PROMOPRBASE PP
             WHERE (I.CDPRECOBASE     = P_CDPRECOBASE)
               AND (P.CDARVPROD       = P_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(P_DTENTPED),'DD/MM/YYYY') BETWEEN PP.DTINIPROMO AND PP.DTFINPROMO)
               AND ((PP.IDDIASPROMO =(TO_CHAR(TO_DATE(P_DTENTPED),'D')))OR(PP.IDDIASPROMO ='8'))
               AND (I.VRITEPREBASE > 0);

               IF NOT (V_VRITEPREBASE > 0) THEN
                  V_VRITEPREBASE := V_VRPRECOBASE;
                  V_IDITPEDDIFER := 'P';
               ELSE
                  V_IDITPEDDIFER := 'N';
               END IF;
         END IF;
      ELSE
         V_IDITPEDDIFER := 'G';
      END IF;
   ELSE
      V_IDITPEDDIFER := 'S';
   END IF;

   RETURN(V_IDITPEDDIFER);
END;
/