CREATE OR REPLACE PROCEDURE ATUALIZA_DELIVERY_SERVPLANOM(
   P_CDCIA       IN COMPANHIA.CDCIA%TYPE,
   P_CDFILIAL    IN FILIAL.CDFILIAL%TYPE,
   P_NRAPURACFATM IN NOTAFISCAL.NRAPURACFATM%TYPE,
   P_CDPERADOR IN VARCHAR2,
   P_NRORG IN NUMBER DEFAULT 1)

AS

   V_NRDELIVERY  DELIVERYM.NRDELIVERY%TYPE;

   V_QUANTIDADE      ITEMPRODUTO.QTITEMPROD%TYPE;
   V_VRPESOBRUTO     ITEMPRODUTO.QTITEMPROD%TYPE;
   V_VRLIQUIDO       ITEMPRODUTO.QTITEMPROD%TYPE;
   V_NRPEDIDOFAC VARCHAR2(20);


   CURSOR C_TRAB_APURAC IS SELECT TA.CDCIAAP, TA.NRDELIVERYAP, TA.NRVOOAP,
                                  TA.CDTRECHOAP, TA.DTHRPLAVOOPVAP, TA.NRSERVICOCIAFILIAP,
                                  TA.CDFILIALAP, TA.NRLANCTONFAP, TA.CDFILISERVAP,
                                  TA.CDCLASSEAP, TA.CDCICLOAP, TA.CDPRODUTOAP
                           FROM TRAB_APURACM TA
                           WHERE TA.CDCIAAP       = P_CDCIA       AND
                                 TA.CDFILIALAP    = P_CDFILIAL    AND
                                 TA.NRAPURACFATAP = P_NRAPURACFATM AND
                                 TA.CDFILISERVAP IS NOT NULL
                           ORDER BY NRDELIVERYAP;

BEGIN

-- =============================================================================
-- ATUALIZACAO DA TABELA DE SERVPLANO E DELIVERY
-- =============================================================================

   V_NRDELIVERY := 0;

   FOR CURS_TRAB IN C_TRAB_APURAC LOOP

      IF V_NRDELIVERY <> CURS_TRAB.NRDELIVERYAP THEN
         UPDATE DELIVERYM
         SET    IDSITDEL = '2',
                NRORGULTATU = P_NRORG,
                DTULTATU = SYSDATE,
                CDOPERULTATU = P_CDPERADOR
         WHERE
            NRDELIVERY = CURS_TRAB.NRDELIVERYAP AND
            CDFILIAL   = CURS_TRAB.CDFILISERVAP;
      END IF;

      UPDATE SERVPLANOM
      SET NRLANCTONF = CURS_TRAB.NRLANCTONFAP,
          CDFILIALNF = CURS_TRAB.CDFILIALAP
      WHERE CDFILIAL     = CURS_TRAB.CDFILISERVAP   AND
            CDCIA        = CURS_TRAB.CDCIAAP        AND
            NRDELIVERY   = CURS_TRAB.NRDELIVERYAP   AND
            NRVOO        = CURS_TRAB.NRVOOAP        AND
            CDTRECHO     = CURS_TRAB.CDTRECHOAP     AND
            DTHRPLAVOOPV = CURS_TRAB.DTHRPLAVOOPVAP AND
            NRSERVICOCIAFILI   = CURS_TRAB.NRSERVICOCIAFILIAP AND
            CDCLASSE = CURS_TRAB.CDCLASSEAP AND
            CDCICLO = CURS_TRAB.CDCICLOAP AND
            NRLANCTONF IS NULL;

      SELECT SUM(P.QTITEMPROD) AS QTITEMPROD,
             SUM(P.QTITEMPROD * NVL(PR.VRPESOUNID, 0)) AS VRPESOBRUTO
      INTO V_QUANTIDADE, V_VRPESOBRUTO
      FROM ITEMPRODUTO P,  PRODUTO PR
      WHERE P.CDFILIAL      = CURS_TRAB.CDFILIALAP     AND
            P.NRLANCTONF    = CURS_TRAB.NRLANCTONFAP   AND
            P.CDPRODUTO     = PR.CDARVPROD             AND
            NOT EXISTS (SELECT NRSEQITPROD
                        FROM ITEMPROIMPOS I
                        WHERE I.CDFILIAL    =  CURS_TRAB.CDFILIALAP   AND
                              I.NRLANCTONF  =  CURS_TRAB.NRLANCTONFAP AND
                              I.CDIMPOSTO   = '31'                    AND
                              I.NRSEQITPROD = P.NRSEQITPROD);

      V_VRLIQUIDO := V_VRPESOBRUTO * 97/100;

      UPDATE NOTAFISCAL
      SET IDPGTOFRETE  = '1',
          DSQTDEVOLU   = SUBSTR(TO_CHAR(ROUND(V_QUANTIDADE,0)),1,6),
          DSESPECVOLU  = 'VOLUME',
          VRPBRUTOVOLU = TO_CHAR(ROUND(V_VRPESOBRUTO,2)),
          VRPLIQUVOLU  = V_VRLIQUIDO
      WHERE CDFILIAL   = CURS_TRAB.CDFILIALAP AND
            NRLANCTONF = CURS_TRAB.NRLANCTONFAP;


      V_NRDELIVERY := CURS_TRAB.NRDELIVERYAP;
   END LOOP;

   DELETE FROM TRAB_APURACM
   WHERE CDCIAAP       = P_CDCIA    AND
         CDFILIALAP    = P_CDFILIAL AND
         NRAPURACFATAP = P_NRAPURACFATM;
END;
/