CREATE OR REPLACE PROCEDURE ARQMESTRE_MERSERV_433(
   P_CDEMPRESA    IN EMPRESA.CDEMPRESA%TYPE,
   P_NRINSJURFILI IN FILIAL.NRINSJURFILI%TYPE,
   P_CDIMPOSTO    IN IMPOSTONF.CDIMPOSTO%TYPE,
   P_DTINICIAL    IN NOTAFISCAL.DTEMISSAO%TYPE,
   P_DTFINAL      IN NOTAFISCAL.DTEMISSAO%TYPE,
   P_NRSEQARQMAG  IN ARQMAGNETICO.NRSEQARQMAG%TYPE,
   P_CDLIVROMAG   IN LIVROMAGNETICO.CDLIVROMAG%TYPE)
AS
   V_DSARQUIVO VARCHAR2(400);
   V_DIAS      VARCHAR2(05);
   V_TOTDESC   NUMBER;
   V_TOTFRETE  NUMBER;
   V_TOTSEG    NUMBER;
   V_TOTOUTRO  NUMBER;
   V_IPI       NUMBER;
   V_NOTA      VARCHAR(50);
   V_SEQ_NOTA  NUMBER;
   V_PART      VARCHAR(50);
   V_SEQ_PART  NUMBER;

BEGIN

   V_SEQ_NOTA := 0;
   V_SEQ_PART := 0;

   SELECT NVL(MAX(CDCONTREGI),0)
     INTO V_SEQ_NOTA
     FROM TMP_REGDADOS_IN86
    WHERE CDREGISTRO = '433';

   SELECT NVL(MAX(CDCONTREGI),0)
     INTO V_SEQ_PART
     FROM TMP_REGDADOS_IN86
    WHERE CDREGISTRO = '491';

   FOR C_NOTAS_MAPA IN(SELECT I.CDFILIAL, I.NRLANCTONF, N.VRNOTAFISC, SUM(I.VRCONTABILNF), MIN(N.DTEMISSAO) AS DTATUALIZACAO
                         FROM IMPOSTONFE E, NOTAFISCAL N, IMPOSTONF I, IMPOSTO P, FILIAL F
                        WHERE E.CDIMPOSTONFE = P_CDIMPOSTO
                          AND N.CDEMPRESA    = P_CDEMPRESA
                          AND N.CDFILIRECE   = F.CDFILIAL
                          AND F.NRINSJURFILI = P_NRINSJURFILI
                          AND (((E.IDFORMAPURNFE = 'D') AND (N.DTENTRSAID BETWEEN P_DTINICIAL AND P_DTFINAL)) OR
                               ((E.IDFORMAPURNFE = 'E') AND (N.DTEMISSAO BETWEEN P_DTINICIAL AND P_DTFINAL)) OR
                               (N.DTENTRSAID BETWEEN P_DTINICIAL AND P_DTFINAL))
                          AND N.IDSTATUS <> 'P'
                          AND N.IDENTRSAIDOP = 'E'
                          AND N.CDMODDOCFISC <> '99'
                          AND I.CDFILIAL     = N.CDFILIAL
                          AND I.NRLANCTONF   = N.NRLANCTONF
                          AND P.CDIMPOSTONFE = E.CDIMPOSTONFE
                          AND I.CDIMPOSTO    = P.CDIMPOSTO
                          AND I.NRSEQUMAPAPU IS NOT NULL
                          AND ((I.IDIMPOSTONF <> '3') OR (I.IDIMPOSTONF IS NOT NULL))
                        GROUP BY I.CDFILIAL, I.NRLANCTONF, N.VRNOTAFISC) LOOP

      V_TOTDESC := 0;
      FOR C_ENCARGODESC IN(SELECT I.CDENCARGO , E.IDENCARGO
                             FROM ITEMENCARGO I, ENCARGO E
                            WHERE I.NRLANCTONF = C_NOTAS_MAPA.NRLANCTONF
                              AND I.CDFILIAL   = C_NOTAS_MAPA.CDFILIAL
                              AND I.CDENCARGO  = E.CDENCARGO
                              AND UPPER(E.NMENCARGO) LIKE '%DESCONTO%')LOOP
         V_TOTDESC:=VALOR_ENCARGO(C_NOTAS_MAPA.CDFILIAL, C_NOTAS_MAPA.NRLANCTONF, C_ENCARGODESC.IDENCARGO, C_ENCARGODESC.CDENCARGO)+V_TOTDESC;
      END LOOP;

      V_TOTFRETE := 0;
      FOR C_ENCARGOFRETE IN(SELECT I.CDENCARGO , E.IDENCARGO
                              FROM ITEMENCARGO I, ENCARGO E
                             WHERE I.NRLANCTONF = C_NOTAS_MAPA.NRLANCTONF
                               AND I.CDFILIAL   = C_NOTAS_MAPA.CDFILIAL
                               AND I.CDENCARGO  = E.CDENCARGO
                               AND UPPER(E.NMENCARGO) LIKE '%FRETE%') LOOP
         V_TOTFRETE:=VALOR_ENCARGO(C_NOTAS_MAPA.CDFILIAL, C_NOTAS_MAPA.NRLANCTONF, C_ENCARGOFRETE.IDENCARGO, C_ENCARGOFRETE.CDENCARGO)+V_TOTFRETE;
      END LOOP;

      V_TOTSEG := 0;
      FOR C_ENCARGOSEG IN  (SELECT I.CDENCARGO , E.IDENCARGO
                              FROM ITEMENCARGO I, ENCARGO E
                             WHERE I.NRLANCTONF = C_NOTAS_MAPA.NRLANCTONF
                               AND I.CDFILIAL   = C_NOTAS_MAPA.CDFILIAL
                               AND I.CDENCARGO  = E.CDENCARGO
                               AND UPPER(E.NMENCARGO) LIKE '%SEGURO%') LOOP
         V_TOTSEG:=VALOR_ENCARGO(C_NOTAS_MAPA.CDFILIAL, C_NOTAS_MAPA.NRLANCTONF, C_ENCARGOSEG.IDENCARGO, C_ENCARGOSEG.CDENCARGO)+V_TOTSEG;
      END LOOP;

      V_TOTOUTRO := 0;
      FOR C_ENCARGOOUTRO IN (SELECT I.CDENCARGO , E.IDENCARGO
                               FROM ITEMENCARGO I, ENCARGO E
                              WHERE I.NRLANCTONF = C_NOTAS_MAPA.NRLANCTONF
                                AND I.CDFILIAL   = C_NOTAS_MAPA.CDFILIAL
                                AND I.CDENCARGO  = E.CDENCARGO
                                AND UPPER(E.NMENCARGO) LIKE '%OUTROS%'
                                AND UPPER(E.NMENCARGO) NOT LIKE '%DESCONTO%'
                                AND UPPER(E.NMENCARGO) NOT LIKE '%FRETE%'
                                AND UPPER(E.NMENCARGO) NOT LIKE '%SEGURO%')LOOP
         V_TOTOUTRO:=VALOR_ENCARGO(C_NOTAS_MAPA.CDFILIAL, C_NOTAS_MAPA.NRLANCTONF, C_ENCARGOOUTRO.IDENCARGO, C_ENCARGOOUTRO.CDENCARGO)+V_TOTOUTRO;
      END LOOP;

      BEGIN
         SELECT SUM(I.VRIMPOSTONF) AS VRIMPOSTO
           INTO V_IPI
           FROM IMPOSTONF I, IMPOSTO IM
          WHERE IM.CDIMPOSTONFE = '10'
            AND IM.CDIMPOSTO    = I.CDIMPOSTO
            AND I.NRLANCTONF    = C_NOTAS_MAPA.NRLANCTONF
            AND I.CDFILIAL      = C_NOTAS_MAPA.CDFILIAL;
      EXCEPTION
      WHEN NO_DATA_FOUND THEN
         V_IPI := 0;
      END;

      FOR C_MESTRE_MERSERV IN (SELECT (RPAD(NVL(NF.CDMODDOCFISC, ' '),2, ' ')                              ||
                                       RPAD(NVL(NF.CDSERIEENTR, ' '), 5, ' ')                              ||
                                       SUBSTR(LPAD(NF.NRNOTAFISC,20,'0'),12,9)                             ||
                                       RPAD(TO_CHAR(NF.DTEMISSAO, 'DDMMYYYY'), 8, ' ')                     ||
                                       SUBSTR(LPAD(NVL(REPLACE(REPLACE(REPLACE(NF.COD_PART,'/',''),'.',''),'-',''),' '),21,' '),8,14) ||
                                       RPAD(TO_CHAR(NF.DTENTRSAID, 'DDMMYYYY'), 8, ' ')                    ||
                                       LPAD(NVL(SUBSTR(ROUND(NF.VRPRODUTOS,2) * 100, 1, 17), 0), 17, 0)    ||
                                       REPLACE(LTRIM(TO_CHAR(NVL(V_TOTDESC,0),'000000000000000.00')),'.')  ||
                                       REPLACE(LTRIM(TO_CHAR(NVL(V_TOTFRETE,0),'000000000000000.00')),'.') ||
                                       REPLACE(LTRIM(TO_CHAR(NVL(V_TOTSEG,0),'000000000000000.00')),'.')   ||
                                       REPLACE(LTRIM(TO_CHAR(NVL(V_TOTOUTRO,0),'000000000000000.00')),'.') ||
                                       REPLACE(LTRIM(TO_CHAR(NVL(V_IPI,0),'000000000000000.00')),'.')      ||
                                       LPAD(NVL(SUBSTR(ROUND(VALOR_ENCARGO(NF.CDFILIAL, NF.NRLANCTONF, 'A', '05'),2) * 100, 1, 17), 0), 17, 0) ||
                                       LPAD(NVL(SUBSTR(ROUND(NF.VRNOTAFISC,2) * 100, 1, 17), 0), 17, 0)    ||
                                       RPAD(' ', 14, ' '))  AS CORPONFENTR,
                                       NF.CDFILIAL, NF.NRLANCTONF,
                                       RPAD(NVL(NF.CDMODDOCFISC, ' '),2, ' ')||RPAD(NVL(NF.CDSERIEENTR, ' '), 5, ' ')||SUBSTR(LPAD(NF.NRNOTAFISC,20,'0'),12,9)||RPAD(TO_CHAR(NF.DTEMISSAO, 'DDMMYYYY'), 8, ' ')||SUBSTR(LPAD(NVL(REPLACE(REPLACE(REPLACE(NF.COD_PART,'/',''),'.',''),'-',''),' '),20,' '),7,14)  AS ID_REG,
                                       NF.COD_PART, RPAD(TO_CHAR(C_NOTAS_MAPA.DTATUALIZACAO, 'DDMMYYYY'), 8,'0') AS DTEMISSAO, NF.IDENTRSAIDOP, NF.IDORIGDESTNF
                                 FROM (SELECT NF.CDFILIAL, NF.NRLANCTONF, NF.CDMODDOCFISC, NF.CDSERIEENTR, NF.NRNOTAFISC, NF.VRNOTAFISC, NF.VRPRODUTOS,
                                              NF.DTEMISSAO, NF.NRINSJURNF, NF.DTENTRSAID, NF.IDSTATUS, NF.IDENTRSAIDOP, NF.IDORIGDESTNF,
                                              DECODE(NF.IDORIGDESTNF,'F',FO.CDFORNECED,DECODE(NF.IDORIGDESTNF,'C',CL.CDCLIENTE,FI.CDFILIAL)) AS COD_PART
                                         FROM NOTAFISCAL NF, FORNECEDOR FO, CLIENTE CL, FILIAL FI
                                        WHERE C_NOTAS_MAPA.CDFILIAL   = NF.CDFILIAL
                                          AND C_NOTAS_MAPA.NRLANCTONF = NF.NRLANCTONF
                                          AND NF.CDFORNECED = FO.CDFORNECED(+)
                                          AND NF.CDCLIENTE = CL.CDCLIENTE(+)
                                          AND NF.CDFILIEMIT = FI.CDFILIAL(+)) NF
                                ORDER BY NF.DTENTRSAID, NF.NRNOTAFISC ) LOOP

         SELECT DECODE(MAX(NVL(QTDIASVENPAG,0)), 0, '1', '2')
           INTO V_DIAS
           FROM CONTAPAGAR
          WHERE CDFILIAL   = C_MESTRE_MERSERV.CDFILIAL AND
                NRLANCTONF = C_MESTRE_MERSERV.NRLANCTONF;

         V_DSARQUIVO := C_MESTRE_MERSERV.CORPONFENTR||
                        V_DIAS||
                        RPAD(' ', 45, ' ')||
                        RPAD(' ', 50, ' ')||
                        RPAD(' ', 2, ' ')||
                        RPAD(' ', 5, ' ')||
                        RPAD('0', 9, '0')||
                        RPAD('0', 8, '0')||
                        RPAD(' ', 14, ' ');

         INSERT INTO ARQMAGNETICO
           (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
         VALUES
           ('02'||LPAD(RTRIM('0'), 8, '0'), V_DSARQUIVO,  P_NRSEQARQMAG);

         /*NOTAS - REGISTRO 433*/
         SELECT MAX(CDCHAVEREG)
           INTO V_NOTA
           FROM TMP_REGDADOS_IN86
          WHERE CDREGISTRO = '433'
            AND CDCHAVEREG = C_MESTRE_MERSERV.CDFILIAL||C_MESTRE_MERSERV.NRLANCTONF;
         IF (V_NOTA IS NULL) THEN
            V_SEQ_NOTA := V_SEQ_NOTA + 1;
            INSERT INTO TMP_REGDADOS_IN86
              (CDREGISTRO, CDCONTREGI, CDCHAVEREG, CDCAMPO1, CDCAMPO2, CDCAMPO3)
            VALUES
              ('433', V_SEQ_NOTA, C_MESTRE_MERSERV.CDFILIAL||C_MESTRE_MERSERV.NRLANCTONF, C_MESTRE_MERSERV.ID_REG, C_MESTRE_MERSERV.DTEMISSAO, C_MESTRE_MERSERV.IDENTRSAIDOP);
         END IF;
         V_NOTA := NULL;

         /*PARTICIPANTE - REGISTRO 491*/
         SELECT MAX(CDCHAVEREG)
           INTO V_PART
           FROM TMP_REGDADOS_IN86
          WHERE CDREGISTRO = '491'
            AND CDCHAVEREG = C_MESTRE_MERSERV.COD_PART;
         IF (V_PART IS NULL) AND (C_MESTRE_MERSERV.COD_PART IS NOT NULL) THEN
            V_SEQ_PART := V_SEQ_PART + 1;
            INSERT INTO TMP_REGDADOS_IN86
              (CDREGISTRO, CDCONTREGI, CDCHAVEREG, CDCAMPO1, CDCAMPO2)
            VALUES
              ('491', V_SEQ_PART, C_MESTRE_MERSERV.COD_PART, C_MESTRE_MERSERV.IDORIGDESTNF, C_MESTRE_MERSERV.DTEMISSAO);
         END IF;
         V_PART := NULL;
      END LOOP;
   END LOOP;

   COMMIT;
END;
/

-- VERSAO --
BEGIN ATUVEROBJ('ARQMESTRE_MERSERV_433','5.33.xxx'); END;
/

