CREATE OR REPLACE PROCEDURE DEFINE_DELIVERY(
  P_CDFILIAL     IN FILIAL.CDFILIAL%TYPE,
  P_CDCIA        IN COMPANHIA.CDCIA%TYPE,
  P_NRVOO        IN VOO.NRVOO%TYPE,
  P_CDTRECHO     IN VOOTRECHO.CDTRECHO%TYPE,
  P_DTHRPLAVOOPV IN PLANOVOO.DTHRPLAVOOPV%TYPE,
  P_CDAGRDECO    IN SERVPLANO.CDAGRDECO%TYPE,
  P_VRPETXAERO   IN DELIVERY.VRPETXAERO%TYPE,
  P_VRBACATX     IN DELIVERY.VRBACATX%TYPE,
  P_DTEMISDEL    IN DELIVERY.DTEMISDEL%TYPE,
  P_VRCOTDOLAR   IN DELIVERY.VRCOTDOLAR%TYPE,
  P_VRTXAEROR    IN DELIVERY.VRTXAEROR%TYPE,
  P_VRTXAEROD    IN DELIVERY.VRTXAEROD%TYPE,
  P_VRDESCDEL    IN DELIVERY.VRDESCDEL%TYPE,
  P_TXDESCDEL    IN DELIVERY.TXDESCDEL%TYPE,
  P_VRDESCDESV   IN DELIVERY.VRDESCDESV%TYPE,
  P_VRDESCCOND   IN DELIVERY.VRDESCCOND%TYPE,
  P_VRDESCFIXO   IN DELIVERY.VRDESCFIXO%TYPE,
  P_IDTIPNACDEL  IN DELIVERY.IDTIPNACDEL%TYPE,
  P_HRPREVDECPV  IN PLANOVOO.HRPREVDECPV%TYPE,
  P_CDLOCAFATCIA IN LOCALFATUCIA.CDLOCAFATCIA%TYPE,
  P_NMRESPDEL    IN DELIVERY.NMRESPDEL %TYPE,
  P_CDLOCALDELIV IN DELIVERY.CDLOCALDELIV%TYPE,
  P_NRSEQDELIVER IN  SERVPLANO.NRSEQDELIVER%TYPE,  
  P_PXNRDELIVERY IN DELIVERY.NRDELIVERY%TYPE DEFAULT NULL,
  P_NRDELIVERY  OUT DELIVERY.NRDELIVERY%TYPE)
AS
  V_NRDELIVERY    DELIVERY.NRDELIVERY%TYPE;
  V_CONTDELIV     NUMBER;
  V_CONT          NUMBER;
  V_TXOBSDELTMP   OBSDELIVTMP.TXOBSDELTMP%TYPE;
  V_IDSTKDTABAPV  PLANOVOO.IDSTKDTABAPV%TYPE;
  V_VRPEDESTXAER  LOCALFATUCIA.VRPEDESTXAER%TYPE;
  V_VRPERSBYUT    CIAFILI.VRPERSBYUT%TYPE;
  V_VRPERSBYNUT   CIAFILI.VRPERSBYNUT%TYPE;
  V_IDTRIBTXPORT  CIAFILI.IDTRIBTXPORT%TYPE;
  V_IDUTSEQNUMRE  PARAMCOMIS.IDUTSEQNUMRE%TYPE;
  V_NRDELEMISS    DELIVERY.NRDELEMISS%TYPE;
  V_IDUTLOCAFATU  CIAFILI.IDUTLOCAFATU%TYPE;
  V_NUMERACAONOVA VARCHAR2(20);
  V_VRDESCONTOPROP FLOAT;

  PROCEDURE DEFINE_PURCHASEORDER(P_NRDELIVERY_PROCESSO IN VARCHAR2)
   AS
   V_CDPURCHASEORDER VARCHAR2(20);
   V_NRTABPRECO VARCHAR2(20);
   BEGIN
      SELECT MAX(CDPURCHASEORDER)
        INTO V_CDPURCHASEORDER
        FROM PURCHASEORDER
       WHERE CDFILIAL = P_CDFILIAL
         AND CDCIA = P_CDCIA
         AND P_DTHRPLAVOOPV BETWEEN DTINIVIGENCIA AND DTFIMVIGENCIA;

      IF V_CDPURCHASEORDER IS NOT NULL THEN
         UPDATE DELIVERY
            SET CDPURCHASEORDER = V_CDPURCHASEORDER
          WHERE CDFILIAL = P_CDFILIAL
            AND NRDELIVERY = P_NRDELIVERY_PROCESSO;

         SELECT MAX(NRTABPRECO)
           INTO V_NRTABPRECO
           FROM TABPRECOSE
          WHERE CDFILIAL = P_CDFILIAL
            AND CDCIA = P_CDCIA            
            AND (P_DTHRPLAVOOPV BETWEEN DTINIVIGTAB AND DTFIMVIGTAB);

         IF V_NRTABPRECO IS NULL THEN 
            RETURN;
         END IF;

         FOR CUR_SERV IN
         (
         SELECT S.CDSERVICO, P.CDSEQPURCHASEORDER
           FROM SERVPLANO S
                INNER JOIN PRECOSECIA P ON P.CDFILIAL = S.CDFILIAL AND
                                           P.CDCIA = S.CDCIA AND
                                           P.CDSERVICO = S.CDSERVICO
          WHERE S.CDFILIAL = P_CDFILIAL
            AND S.CDCIA = P_CDCIA
            AND S.NRVOO = P_NRVOO
            AND S.CDTRECHO = P_CDTRECHO
            AND S.DTHRPLAVOOPV = P_DTHRPLAVOOPV
            AND P.CDFILIAL = P_CDFILIAL
            AND P.CDCIA = P_CDCIA
            AND S.NRDELIVERY = P_NRDELIVERY_PROCESSO
            AND P.NRTABPRECO = V_NRTABPRECO
            AND ((P.DTINIVIGSE <= P_DTHRPLAVOOPV) OR
                   ((P.DTINIVIGSE IS NULL) AND
                    (NOT EXISTS (SELECT NRSEQPRECO
                                 FROM PRECOSECIA
                                 WHERE (CDFILIAL    = P_CDFILIAL    ) AND
                                       (CDCIA       = P_CDCIA       ) AND
                                       (NRTABPRECO  = V_NRTABPRECO  ) AND
                                       (CDSERVICO   = P.CDSERVICO   ) AND
                                       (DTINIVIGSE <= P_DTHRPLAVOOPV)))))
            AND ((P.DTFIMVIGSE >= P_DTHRPLAVOOPV) OR
                   ((P.DTFIMVIGSE IS NULL) AND
                    (NOT EXISTS (SELECT NRSEQPRECO
                                 FROM PRECOSECIA
                                 WHERE (CDFILIAL    = P_CDFILIAL    ) AND
                                       (CDCIA       = P_CDCIA       ) AND
                                       (NRTABPRECO  = V_NRTABPRECO  ) AND
                                       (CDSERVICO   = P.CDSERVICO   ) AND
                                       (DTFIMVIGSE >= P_DTHRPLAVOOPV )))))
         
         ) LOOP
           UPDATE SERVPLANO
              SET CDPURCHASEORDER = V_CDPURCHASEORDER,
                  CDSEQPURCHASEORDER = CUR_SERV.CDSEQPURCHASEORDER
            WHERE CDFILIAL = P_CDFILIAL
              AND CDCIA = P_CDCIA
              AND NRVOO = P_NRVOO
              AND CDTRECHO = P_CDTRECHO
              AND DTHRPLAVOOPV = P_DTHRPLAVOOPV
              AND CDSERVICO = CUR_SERV.CDSERVICO;                                      
         END LOOP;

      END IF;

   END;

BEGIN
   SELECT NVL(IDUTSEQNUMRE, 'N')
   INTO V_IDUTSEQNUMRE
   FROM
      PARAMCOMIS
   WHERE
      CDFILIAL = P_CDFILIAL;

   V_NRDELEMISS := '';

   IF V_IDUTSEQNUMRE = 'S' THEN
      SELECT NVL(MAX(NRULTDELIVERY),0)+1
      INTO V_NRDELIVERY
      FROM PARAMCOMIS
      WHERE CDFILIAL = P_CDFILIAL;
      IF P_PXNRDELIVERY IS NOT NULL THEN
         V_NRDELEMISS := P_PXNRDELIVERY;
      END IF;
   ELSE
      IF P_PXNRDELIVERY IS NULL THEN
         SELECT NVL(MAX(NRULTDELIVERY),0)+1
         INTO V_NRDELIVERY
         FROM PARAMCOMIS
         WHERE CDFILIAL = P_CDFILIAL;
      ELSE
         V_NRDELIVERY := P_PXNRDELIVERY;
      END IF;
   END IF;
   V_VRPEDESTXAER := P_VRPETXAERO;
   SELECT IDTRIBTXPORT, IDUTLOCAFATU
   INTO V_IDTRIBTXPORT, V_IDUTLOCAFATU
   FROM CIAFILI
   WHERE
      (CDFILIAL = P_CDFILIAL) AND
      (CDCIA    = P_CDCIA   );
   IF V_IDTRIBTXPORT <> '1' THEN
      IF V_IDUTLOCAFATU = 'S' THEN
         SELECT NVL(MAX(VRPEDESTXAER), 0)
         INTO V_VRPEDESTXAER
         FROM LOCALFATUCIA
         WHERE
            (CDFILIAL     = P_CDFILIAL    ) AND
            (CDCIA        = P_CDCIA       ) AND
            (CDLOCAFATCIA = P_CDLOCAFATCIA);
      END IF;
   END IF;
   INSERT INTO DELIVERY
   (CDFILIAL, NRDELIVERY, CDCIA, NRVOO, CDTRECHO, DTHRPLAVOOPV, VRBACATX,
    VRPETXAERO, HREMISDEL, IDSITDEL, DTEMISDEL, VRCOTDOLAR, VRTXAEROR, VRTXAEROD,
    VRDESCDEL, TXDESCDEL,  VRDESCDESV, VRDESCCOND, VRDESCFIXO, IDTIPNACDEL,
    CDLOCAFATCIA, NMRESPDEL, NRDELEMISS, CDLOCALDELIV)
   VALUES
   (P_CDFILIAL, V_NRDELIVERY, P_CDCIA, P_NRVOO, P_CDTRECHO, P_DTHRPLAVOOPV, P_VRBACATX,
    V_VRPEDESTXAER, TO_CHAR(SYSDATE,'HH24MM'), '1', P_DTEMISDEL, P_VRCOTDOLAR, P_VRTXAEROR,
    P_VRTXAEROD, P_VRDESCDEL, P_TXDESCDEL, P_VRDESCDESV, P_VRDESCCOND, P_VRDESCFIXO,
    P_IDTIPNACDEL, P_CDLOCAFATCIA, P_NMRESPDEL, V_NRDELEMISS, P_CDLOCALDELIV);
   SELECT COUNT(*)
   INTO V_CONT
   FROM OBSDELIVTMP
   WHERE (CDFILIAL     = P_CDFILIAL    ) AND
         (CDCIATMP     = P_CDCIA       ) AND
         (NRVOOTMP     = P_NRVOO       ) AND
         (CDTRECHOTMP  = P_CDTRECHO    ) AND
         (DTHRPLAVOTMP = P_DTHRPLAVOOPV) AND
         (CDAGRDECOTMP = P_CDAGRDECO   );

   IF V_CONT > 0 THEN

      SELECT TXOBSDELTMP, VRDESCONTOPROP
      INTO V_TXOBSDELTMP, V_VRDESCONTOPROP
      FROM OBSDELIVTMP
      WHERE (CDFILIAL     = P_CDFILIAL    ) AND
            (CDCIATMP     = P_CDCIA       ) AND
            (NRVOOTMP     = P_NRVOO       ) AND
            (CDTRECHOTMP  = P_CDTRECHO    ) AND
            (DTHRPLAVOTMP = P_DTHRPLAVOOPV) AND
            (CDAGRDECOTMP = P_CDAGRDECO   );

      INSERT INTO OBSDELIVERY
      (CDFILIAL, NRDELIVERY, TXOBSDEL, VRDESCONTOPROP)
      VALUES
      (P_CDFILIAL, V_NRDELIVERY, V_TXOBSDELTMP, V_VRDESCONTOPROP);

   END IF;

   DELETE FROM OBSDELIVTMP
   WHERE (CDFILIAL     = P_CDFILIAL    ) AND
         (CDCIATMP     = P_CDCIA       ) AND
         (NRVOOTMP     = P_NRVOO       ) AND
         (CDTRECHOTMP  = P_CDTRECHO    ) AND
         (DTHRPLAVOTMP = P_DTHRPLAVOOPV) AND
         (CDAGRDECOTMP = P_CDAGRDECO   );
   UPDATE PARAMCOMIS
   SET NRULTDELIVERY = V_NRDELIVERY
   WHERE CDFILIAL = P_CDFILIAL;
   IF V_IDUTSEQNUMRE = 'S' THEN
      V_NUMERACAONOVA := LPAD(TO_CHAR(V_NRDELEMISS),20,'0');
      UPDATE NOVOCODIGO
      SET NRSEQUENCIAL = V_NUMERACAONOVA
      WHERE CDCONTADOR = 'DELIVERY'||P_CDFILIAL||P_CDLOCALDELIV;
   END IF;
   SELECT MAX(VRPERSBYUT),MAX(VRPERSBYNUT)
   INTO V_VRPERSBYUT,V_VRPERSBYNUT
   FROM CIAFILI
   WHERE (CDFILIAL = P_CDFILIAL) AND
         (CDCIA    = P_CDCIA   );
   UPDATE SERVPLANO
   SET NRDELIVERY   = V_NRDELIVERY,
       IDSITSERSP   = '2',
       /* Desconto de Delivery */
       VRDESCRESESP = DECODE(P_VRBACATX,0,0,
                      (/*Valor do desconto*/
                       P_VRDESCDEL*
                       /*Valor total em reais do servio*/
                       (NVL(QTSERFATSP*VRPRURESESP,0) +
                        NVL(VRPRUSBYSP*(V_VRPERSBYUT/100)*QTSESBYUTSP,0)+
                        NVL(VRPRUSBYSP*(V_VRPERSBYNUT/100)*(QTSESBYPRSP-QTSESBYUTSP),0))/
                       /*Valor total da delivery*/
                       P_VRBACATX)),
       VRDESCDOSESP = DECODE(P_VRBACATX,0,0,
                      (/*Valor do desconto*/
                       P_VRDESCDEL*
                       /*Valor total em dlares do servio*/
                       (NVL(QTSERFATSP*VRPRUDOLSESP,0) +
                        NVL(VRPRUSBYSP*(V_VRPERSBYUT/100)*QTSESBYUTSP,0)+
                        NVL(VRPRUSBYSP*(V_VRPERSBYNUT/100)*(QTSESBYPRSP-QTSESBYUTSP),0))/
                       /*Valor total da delivery*/
                       P_VRBACATX)),
       /* Desconto Contratual Condicional */
       VRDESCONDRSP = DECODE(P_VRBACATX,0,0,
                      (/*Valor do desconto*/
                       P_VRDESCCOND*
                       /*Valor total em reais do servio*/
                       (NVL(QTSERFATSP*VRPRURESESP,0) +
                        NVL(VRPRUSBYSP*(V_VRPERSBYUT/100)*QTSESBYUTSP,0)+
                        NVL(VRPRUSBYSP*(V_VRPERSBYNUT/100)*(QTSESBYPRSP-QTSESBYUTSP),0))/
                       /*Valor total da delivery*/
                       P_VRBACATX)),
       VRDESCONDDSP = DECODE(P_VRBACATX,0,0,
                      (/*Valor do desconto*/
                       P_VRDESCCOND*
                       /*Valor total em dlares do servio*/
                       (NVL(QTSERFATSP*VRPRUDOLSESP,0) +
                        NVL(VRPRUSBYSP*(V_VRPERSBYUT/100)*QTSESBYUTSP,0)+
                        NVL(VRPRUSBYSP*(V_VRPERSBYNUT/100)*(QTSESBYPRSP-QTSESBYUTSP),0))/
                       /*Valor total da delivery*/
                       P_VRBACATX)),
       /* Desconto Contratual Fixo */
       VRDESFIXORSP = DECODE(P_VRBACATX,0,0,
                      (/*Valor do desconto*/
                       P_VRDESCFIXO*
                       /*Valor total em reais do servio*/
                       (NVL(QTSERFATSP*VRPRURESESP,0) +
                        NVL(VRPRUSBYSP*(V_VRPERSBYUT/100)*QTSESBYUTSP,0)+
                        NVL(VRPRUSBYSP*(V_VRPERSBYNUT/100)*(QTSESBYPRSP-QTSESBYUTSP),0))/
                       /*Valor total da delivery*/
                       P_VRBACATX)),
       VRDESFIXODSP = DECODE(P_VRBACATX,0,0,
                      (/*Valor do desconto*/
                       P_VRDESCFIXO*
                       /*Valor total em dlares do servio*/
                       (NVL(QTSERFATSP*VRPRUDOLSESP,0) +
                        NVL(VRPRUSBYSP*(V_VRPERSBYUT/100)*QTSESBYUTSP,0)+
                        NVL(VRPRUSBYSP*(V_VRPERSBYNUT/100)*(QTSESBYPRSP-QTSESBYUTSP),0))/
                       /*Valor total da delivery*/
                       P_VRBACATX)),
       /* Desconto de Desvalorizao */
       VRDESDESVRSP = DECODE(P_VRBACATX,0,0,
                      (/*Valor do desconto*/
                       P_VRDESCDESV*
                       /*Valor total em reais do servio*/
                       (NVL(QTSERFATSP*VRPRURESESP,0) +
                        NVL(VRPRUSBYSP*(V_VRPERSBYUT/100)*QTSESBYUTSP,0)+
                        NVL(VRPRUSBYSP*(V_VRPERSBYNUT/100)*(QTSESBYPRSP-QTSESBYUTSP),0))/
                       /*Valor total da delivery*/
                       P_VRBACATX)),
       VRDESDESVDSP = DECODE(P_VRBACATX,0,0,
                      (/*Valor do desconto*/
                       P_VRDESCDESV*
                       /*Valor total em dlares do servio*/
                       (NVL(QTSERFATSP*VRPRUDOLSESP,0) +
                        NVL(VRPRUSBYSP*(V_VRPERSBYUT/100)*QTSESBYUTSP,0)+
                        NVL(VRPRUSBYSP*(V_VRPERSBYNUT/100)*(QTSESBYPRSP-QTSESBYUTSP),0))/
                       /*Valor total da delivery*/
                       P_VRBACATX))
   WHERE (CDFILIAL     = P_CDFILIAL    ) AND
         (CDCIA        = P_CDCIA       ) AND
         (NRVOO        = P_NRVOO       ) AND
         (CDTRECHO     = P_CDTRECHO    ) AND
         (DTHRPLAVOOPV = P_DTHRPLAVOOPV) AND
         (CDAGRDECO    = P_CDAGRDECO   ) AND
         (NRDELIVERY   IS NULL         ) AND
         (IDSITSERSP   = '1'           ) AND
         (NVL(NRSEQDELIVER, 0) = P_NRSEQDELIVER) AND
         EXISTS (SELECT 1 FROM PRODUTO
                  WHERE PRODUTO.CDPRODUTO = SERVPLANO.CDSERVICO
                    AND (NVL(PRODUTO.IDFATVENDADEVOL, 'NFVENDA') = 'NFVENDA'));
   SELECT COUNT(*)
   INTO V_CONTDELIV
   FROM SERVPLANO
   WHERE (CDFILIAL      = P_CDFILIAL    ) AND
         (CDCIA         = P_CDCIA       ) AND
         (NRVOO         = P_NRVOO       ) AND
         (CDTRECHO      = P_CDTRECHO    ) AND
         (DTHRPLAVOOPV  = P_DTHRPLAVOOPV) AND
         (NRDELIVERY      IS NULL       ) AND
         (IDSITSERSP   <> 3             ) AND
         EXISTS (SELECT 1 FROM PRODUTO
                  WHERE PRODUTO.CDPRODUTO = SERVPLANO.CDSERVICO
                    AND (NVL(PRODUTO.IDFATVENDADEVOL, 'NFVENDA') = 'NFVENDA'));
   IF V_CONTDELIV = 0 THEN
   	UPDATE PLANOVOO
      SET    IDSITABASPV = '2'
      WHERE (CDFILIAL     = P_CDFILIAL    ) AND
            (CDCIA        = P_CDCIA       ) AND
            (NRVOO        = P_NRVOO       ) AND
            (CDTRECHO     = P_CDTRECHO    ) AND
            (DTHRPLAVOOPV = P_DTHRPLAVOOPV) AND
            (IDSITABASPV  = '1'           );
   END IF;
   /* UPDATADA A SITUACAO DO KDT */
   IF P_DTHRPLAVOOPV < SYSDATE THEN
     V_IDSTKDTABAPV := '2';   /* ATRASADO */
   ELSIF P_DTHRPLAVOOPV > SYSDATE THEN
     V_IDSTKDTABAPV := '1';   /* NO HORARIO */
   ELSIF P_DTHRPLAVOOPV = SYSDATE THEN
     IF P_HRPREVDECPV > TO_CHAR(SYSDATE,'HH24MM') THEN
       V_IDSTKDTABAPV := '1'; /* NO HORARIO */
     ELSE
       V_IDSTKDTABAPV := '2'; /* ATRASADO */
     END IF;
   ELSE
     V_IDSTKDTABAPV := NULL;
   END IF;
   UPDATE PLANOVOO
   SET IDSTKDTABAPV = V_IDSTKDTABAPV
   WHERE (CDFILIAL     = P_CDFILIAL    ) AND
         (CDCIA        = P_CDCIA       ) AND
         (NRVOO        = P_NRVOO       ) AND
         (CDTRECHO     = P_CDTRECHO    ) AND
         (DTHRPLAVOOPV = P_DTHRPLAVOOPV);
   /* UPDATA O VALOR DESCONTADO DO DESCONTO CONDICIONAL */
   IF P_VRDESCCOND > 0 THEN
     UPDATE DESPERCIA
     SET    VRUTIDESC = VRUTIDESC + P_VRDESCCOND
     WHERE  (CDFILIAL = P_CDFILIAL) AND
            (CDCIA    = P_CDCIA   ) AND
            (P_DTHRPLAVOOPV BETWEEN DTINIDESC AND DTFIMDESC);
   END IF;
   P_NRDELIVERY := V_NRDELIVERY;

   DEFINE_PURCHASEORDER(V_NRDELIVERY);
END;
/


-- VERSAO --
BEGIN ATUVEROBJ('DEFINE_DELIVERY','4.07.xxx'); END;
/

