CREATE OR REPLACE
PROCEDURE APURAC_DADOSADIC(
   P_FILIAL       IN FILIAL.CDFILIAL%TYPE,
   P_FILIPROC     IN NOTAFISCAL.CDFILIAL%TYPE,
   P_LANCTONF     IN NOTAFISCAL.NRLANCTONF%TYPE,
   P_CDCIA        IN COMPANHIA.CDCIA%TYPE,
   P_NRVOO        IN VOO.NRVOO%TYPE,
   P_CDTRECHO     IN VOOTRECHO.CDTRECHO%TYPE,
   P_CDCLASSE     IN CLASSE.CDCLASSE%TYPE,
   P_NRDELIVERY   IN DELIVERY.NRDELIVERY%TYPE,
   P_CDEQUIP      IN EQUIPAMENTO.CDEQUIP%TYPE,
   P_CDLOCFATCIA  IN TRAB_APURAC.CDLOCFATCIAP%TYPE,
   P_CDAGRDEL     IN TRAB_APURAC.CDAGRDECOAP%TYPE)
AS
   V_DSDELIVNF       VARCHAR2(120);
   V_DSDELIVNFLOCAL  VARCHAR2(120);
   V_NRDELEMISS2      VARCHAR2(20);
   V_NRDELEMISS      NUMBER;
   V_NRDADOADIC      NUMBER;
   V_IDNACCIA        COMPANHIA.IDNACCIA%TYPE;
   V_CDTIPEMNF       CIAFILI.CDTIPEMNF%TYPE;
   V_SGEQUIP         EQUIPAMENTO.SGEQUIP%TYPE;
   V_IDUTSEQNUMRE    PARAMCOMIS.IDUTSEQNUMRE%TYPE;
BEGIN
   SELECT NVL(MAX(F.IDTIPEMNF),SUBSTR(MAX(C.CDTIPEMNF),3,4)), NVL(MAX(CO.IDNACCIA),'1')
   INTO V_CDTIPEMNF, V_IDNACCIA
   FROM CIAFILI C, COMPANHIA CO, FORMEMISNF F
   WHERE (C.CDFILIAL  = P_FILIAL) AND
         (C.CDCIA     = P_CDCIA)  AND
         (CO.CDCIA    = P_CDCIA)  AND
         (C.CDTIPEMNF = F.CDTIPEMNF (+));
   SELECT NVL(IDUTSEQNUMRE, 'N')
   INTO V_IDUTSEQNUMRE
   FROM PARAMCOMIS
   WHERE
      (CDFILIAL = P_FILIAL);

   DELETE
     FROM DADOSADIC
    WHERE (CDFILIAL   = P_FILIPROC) AND
          (NRLANCTONF = P_LANCTONF);
      
   FOR CURS_APNF IN (SELECT * FROM NOTAFISCAL
                     WHERE CDFILIAL   = P_FILIPROC AND
                           NRLANCTONF = P_LANCTONF) LOOP
-- =============================================================================
-- DADOS ADICIONAIS - DELIVERYS DAS NOTAS FISCAIS
-- =============================================================================

      SELECT MAX(NRDADOADIC) + 1 INTO V_NRDADOADIC
      FROM DADOSADIC
      WHERE (CDFILIAL   = CURS_APNF.CDFILIAL) AND
            (NRLANCTONF = CURS_APNF.NRLANCTONF);
      IF (V_NRDADOADIC IS NULL) THEN
         V_NRDADOADIC := 1;
      END IF;
      V_DSDELIVNF := 'DELIV.: ';
      FOR CURS_DELIVNF IN
            (SELECT DISTINCT NRDELIVERYAP, CDLOCALDELIVAP, LPAD(NRDELEMISSAP, 7, '0') NRDELEMISSAP
             FROM TRAB_APURAC
             WHERE (CDCIAAP      = P_CDCIA)    AND
                   (CDFILIALAP   = P_FILIPROC) AND
                   (NRVOOAP      = P_NRVOO OR 'T' = P_NRVOO) AND
                   (CDTRECHOAP   = P_CDTRECHO OR 'T' = P_CDTRECHO) AND
                   (CDCLASSEAP   = P_CDCLASSE OR 'T'= P_CDCLASSE) AND
                   (NRDELIVERYAP = P_NRDELIVERY OR P_NRDELIVERY IS NULL) AND
                   (CDEQUIPAP    = P_CDEQUIP OR 'T' = P_CDEQUIP) AND
                   (CDLOCFATCIAP = P_CDLOCFATCIA OR 'T' = P_CDLOCFATCIA) AND
                   (CDAGRDECOAP  = P_CDAGRDEL OR 'T' = P_CDAGRDEL)) LOOP
         IF LENGTH(V_DSDELIVNF) > 111 THEN
            /* 111 representa 120 (tamanho mximo do campo) - 7 (tamanho do
               proximo numero de delivery) - 2 (', ') */
            INSERT INTO DADOSADIC (CDFILIAL, NRDADOADIC, CDMENSAGEM,
                                   DSDADOADIC, NRLANCTONF)
            VALUES (CURS_APNF.CDFILIAL, LPAD(TO_CHAR(V_NRDADOADIC),3,'0'), NULL,
                    V_DSDELIVNF, CURS_APNF.NRLANCTONF);
            V_DSDELIVNF := 'DELIV.: ';
            V_NRDADOADIC := V_NRDADOADIC + 1;
         END IF;
         IF V_IDUTSEQNUMRE = 'S' THEN
            V_DSDELIVNFLOCAL  :=  V_DSDELIVNF || CURS_DELIVNF.CDLOCALDELIVAP;
            V_NRDELEMISS  := CURS_DELIVNF.NRDELEMISSAP;
            V_NRDELEMISS2 :=  TO_CHAR(V_NRDELEMISS );
            V_DSDELIVNF := V_DSDELIVNFLOCAL||' - '||V_NRDELEMISS2 || ', ';
         ELSE
            V_DSDELIVNF := V_DSDELIVNF || TO_CHAR(CURS_DELIVNF.NRDELIVERYAP) || ', ';
         END IF;
      END LOOP;
      INSERT INTO DADOSADIC (CDFILIAL, NRDADOADIC, CDMENSAGEM,
                             DSDADOADIC, NRLANCTONF)
      VALUES (CURS_APNF.CDFILIAL, LPAD(TO_CHAR(V_NRDADOADIC),3,'0'), NULL,
              V_DSDELIVNF, CURS_APNF.NRLANCTONF);
      V_NRDADOADIC := V_NRDADOADIC + 1;
      V_DSDELIVNF := 'DATA DELIV.: ';
      FOR CURS_DELIVNF IN
            (SELECT DISTINCT DTEMISDELAP FROM TRAB_APURAC
             WHERE (CDCIAAP      = P_CDCIA)    AND
                   (CDFILIALAP   = P_FILIPROC) AND
                   (NRVOOAP      = P_NRVOO OR 'T' = P_NRVOO) AND
                   (CDTRECHOAP   = P_CDTRECHO OR 'T' = P_CDTRECHO) AND
                   (CDCLASSEAP   = P_CDCLASSE OR 'T'= P_CDCLASSE) AND
                   (NRDELIVERYAP = P_NRDELIVERY OR P_NRDELIVERY IS NULL) AND
                   (CDEQUIPAP    = P_CDEQUIP OR 'T' = P_CDEQUIP) AND
                   (CDLOCFATCIAP = P_CDLOCFATCIA OR 'T' = P_CDLOCFATCIA) AND
                   (CDAGRDECOAP  = P_CDAGRDEL OR 'T' = P_CDAGRDEL) AND
                   (DTEMISDELAP IS NOT NULL)) LOOP
         IF LENGTH(V_DSDELIVNF) > 108 THEN
            /* 108 representa 120 (tamanho mximo do campo) - 10 (tamanho do
               proximo numero de data) - 2 (', ') */
            INSERT INTO DADOSADIC (CDFILIAL, NRDADOADIC, CDMENSAGEM,
                                   DSDADOADIC, NRLANCTONF)
            VALUES (CURS_APNF.CDFILIAL, LPAD(TO_CHAR(V_NRDADOADIC),3,'0'), NULL,
                    V_DSDELIVNF, CURS_APNF.NRLANCTONF);
            V_DSDELIVNF := 'DATA DELIV.: ';
            V_NRDADOADIC := V_NRDADOADIC + 1;
         END IF;
         V_DSDELIVNF := V_DSDELIVNF || TO_CHAR(CURS_DELIVNF.DTEMISDELAP,'DD/MM/YYYY') || ', ';
      END LOOP;
      INSERT INTO DADOSADIC (CDFILIAL, NRDADOADIC, CDMENSAGEM,
                             DSDADOADIC, NRLANCTONF)
      VALUES (CURS_APNF.CDFILIAL, LPAD(TO_CHAR(V_NRDADOADIC),3,'0'), NULL,
              V_DSDELIVNF, CURS_APNF.NRLANCTONF);
      V_NRDADOADIC := V_NRDADOADIC + 1;
      V_DSDELIVNF := 'VOO:';
      FOR CURS_DELIVNF IN
            (SELECT DISTINCT NRVOOAP FROM TRAB_APURAC
             WHERE (CDCIAAP      = P_CDCIA)    AND
                   (CDFILIALAP   = P_FILIPROC)AND
                   (NRVOOAP      = P_NRVOO OR 'T' = P_NRVOO) AND
                   (CDTRECHOAP   = P_CDTRECHO OR 'T' = P_CDTRECHO) AND
                   (CDCLASSEAP   = P_CDCLASSE OR 'T'= P_CDCLASSE) AND
                   (NRDELIVERYAP = P_NRDELIVERY OR P_NRDELIVERY IS NULL) AND
                   (CDEQUIPAP    = P_CDEQUIP OR 'T' = P_CDEQUIP) AND
                   (CDLOCFATCIAP = P_CDLOCFATCIA OR 'T' = P_CDLOCFATCIA) AND
                   (CDAGRDECOAP  = P_CDAGRDEL OR 'T' = P_CDAGRDEL)) LOOP

         IF LENGTH(V_DSDELIVNF) > 113 THEN
            /* 111 representa 120 (tamanho mximo do campo) - 5 (tamanho do
               proximo numero de voo) - 2 (', ') */
            INSERT INTO DADOSADIC (CDFILIAL, NRDADOADIC, CDMENSAGEM,
                                   DSDADOADIC, NRLANCTONF)
            VALUES (CURS_APNF.CDFILIAL, LPAD(TO_CHAR(V_NRDADOADIC),3,'0'), NULL,
                    V_DSDELIVNF, CURS_APNF.NRLANCTONF);
            V_DSDELIVNF := 'VOO:';
            V_NRDADOADIC := V_NRDADOADIC + 1;
         END IF;
         V_DSDELIVNF := V_DSDELIVNF || CURS_DELIVNF.NRVOOAP || ', ';
      END LOOP;
      INSERT INTO DADOSADIC (CDFILIAL, NRDADOADIC, CDMENSAGEM,
                             DSDADOADIC, NRLANCTONF)
      VALUES (CURS_APNF.CDFILIAL, LPAD(TO_CHAR(V_NRDADOADIC),3,'0'), NULL,
              V_DSDELIVNF, CURS_APNF.NRLANCTONF);
      IF (V_CDTIPEMNF = '07') OR (V_CDTIPEMNF = '08') OR (V_CDTIPEMNF = '09') THEN
         V_NRDADOADIC := V_NRDADOADIC + 1;
         V_DSDELIVNF := 'EQUIP.: ';
         FOR CURS_DELIVNF IN
               (SELECT DISTINCT CDEQUIPAP
                FROM TRAB_APURAC
                WHERE(CDCIAAP      = P_CDCIA)    AND
                (CDFILIALAP   = P_FILIPROC) AND
                (NRVOOAP      = P_NRVOO OR 'T' = P_NRVOO) AND
                (CDTRECHOAP   = P_CDTRECHO OR 'T' = P_CDTRECHO) AND
                (CDCLASSEAP   = P_CDCLASSE OR 'T'= P_CDCLASSE) AND
                (NRDELIVERYAP = P_NRDELIVERY OR P_NRDELIVERY IS NULL) AND
                (CDEQUIPAP    = P_CDEQUIP OR 'T' = P_CDEQUIP) AND
                (CDLOCFATCIAP = P_CDLOCFATCIA OR 'T' = P_CDLOCFATCIA) AND
                (CDAGRDECOAP  = P_CDAGRDEL OR 'T' = P_CDAGRDEL)) LOOP

            IF LENGTH(V_DSDELIVNF) > 108 THEN
               /* 108 representa 120 (tamanho mximo do campo) - 10 (tamanho do
                  proximo numero de delivery) - 2 (', ') */
               INSERT INTO DADOSADIC (CDFILIAL, NRDADOADIC, CDMENSAGEM,
                                      DSDADOADIC, NRLANCTONF)
               VALUES (CURS_APNF.CDFILIAL, LPAD(TO_CHAR(V_NRDADOADIC),3,'0'), NULL,
                       V_DSDELIVNF, CURS_APNF.NRLANCTONF);
               V_DSDELIVNF := 'EQUIP.: ';
               V_NRDADOADIC := V_NRDADOADIC + 1;
            END IF;
            SELECT MAX(SGEQUIP)
            INTO V_SGEQUIP
            FROM EQUIPAMENTO
            WHERE CDEQUIP = CURS_DELIVNF.CDEQUIPAP;
            V_DSDELIVNF := V_DSDELIVNF || V_SGEQUIP || ', ';
         END LOOP;
         INSERT INTO DADOSADIC (CDFILIAL, NRDADOADIC, CDMENSAGEM,
                                DSDADOADIC, NRLANCTONF)
         VALUES (CURS_APNF.CDFILIAL, LPAD(TO_CHAR(V_NRDADOADIC),3,'0'), NULL,
                 V_DSDELIVNF, CURS_APNF.NRLANCTONF);
      END IF;
   END LOOP;
END;
/

-- VERSAO --
BEGIN ATUVEROBJ('APURAC_DADOSADIC','5.45.xxx'); END;
/

