CREATE OR REPLACE PROCEDURE RE_ESTOQUE(
   P_EXPORTAR     IN VARCHAR2,
   P_CDEMPRESA    IN EMPRESA.CDEMPRESA%TYPE,
   P_CDINSCESTA   IN EMPRESA.CDINSCESTA%TYPE,
   P_DTINICIAL    IN NOTAFISCAL.DTENTRSAID%TYPE,
   P_DTFINAL      IN NOTAFISCAL.DTENTRSAID%TYPE,
   P_FINALIDADE   IN VARCHAR2,
   P_NRSEQARQMAG  IN ARQMAGNETICO.NRSEQARQMAG%TYPE,
   P_CDLIVROMAG   IN NOTAFISCAL.NRLANCTONF%TYPE,
   P_DATA         IN NOTAFISCAL.DTENTRSAID%TYPE)
AS
   V_NRINSJURIEST   VARCHAR(20);
   V_CDINSCESTA     VARCHAR(16);
   V_NMRAZSOCEMPR   VARCHAR(80);
   V_QTDEREG02      NUMBER;
   V_QTDEREG03      NUMBER;
   V_QTDEREG04      NUMBER;
BEGIN
   SELECT RPAD(SUBSTR(REPLACE(REPLACE(REPLACE(REPLACE(IE.NRINSJURIEST,'.',''),',',''),'-',''),'/',''),1,14),14,' '),
          RPAD(REPLACE(REPLACE(REPLACE(REPLACE(DECODE(SUBSTR(UPPER(IE.CDINSCESTA),1,5),'ISENT','ISENTO',IE.CDINSCESTA),'.',''),',',''),'-',''),'/',''),14,' '),
          EP.NMRAZSOCEMPR
   INTO V_NRINSJURIEST, V_CDINSCESTA, V_NMRAZSOCEMPR
   FROM EMPRESA EP, INSCRESTAD IE
   WHERE EP.CDEMPRESA   = P_CDEMPRESA    AND
         IE.CDEMPRESA   = EP.CDEMPRESA   AND
         IE.CDINSCESTA  = P_CDINSCESTA;

   IF P_EXPORTAR = 'M' THEN    /*ESTOQUE MENSAL*/

      V_QTDEREG02 := 0;
      FOR C_REGISTRO_02M IN (SELECT SUBSTR(
                              '02'                      ||'|'||
                              LPAD(CDTPITEMPROD,13,'0') ||'|'||
                              REPLACE(LTRIM(TO_CHAR(ROUND(ABS(QTESTINVFISC),2),'999999999990.000')),'.',',')||'|'||
                              REPLACE(LTRIM(TO_CHAR(ROUND(ABS(VRTOTINVFISC),2),'999999999990.00')),'.',',')||'|'||
                              '|'||
                              REPLACE(LTRIM(TO_CHAR(ROUND(ABS(VRUNIINVFISC),2),'999999999990.00')),'.',','),1,126) AS DSARQMAG
                             FROM INVENTFISC I, RELACPRODITGEN R
                            WHERE I.CDEMPRESA     = P_CDEMPRESA
                              AND I.CDINSCESTA    = P_CDINSCESTA
                              AND I.DTINVEFISCAL BETWEEN P_DTINICIAL AND P_DTFINAL
                              AND R.CDPRODUTO(+) = I.CDPRODUTO
                              AND R.CDLIVROMAG(+) = P_CDLIVROMAG
                            ORDER BY R.CDTPITEMPROD) LOOP


         INSERT INTO ARQMAGNETICO
           (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
         VALUES
           ('20'||LPAD(RTRIM(V_QTDEREG02), 8, '0'), C_REGISTRO_02M.DSARQMAG, P_NRSEQARQMAG);

         V_QTDEREG02 := NVL(V_QTDEREG02,0)+1;
      END LOOP;


      V_QTDEREG03 := 0;
      FOR C_REGISTRO_03M IN (SELECT SUBSTR('03'         ||'|'||
                                          NRINSJURNF    ||'|'||
                                          SGESTADO      ||'|'||
                                          CDMODDOCFISC  ||'|'||
                                          CDSERIEENTR   ||'|'||
                                          NRNOTAFISC    ||'|'||
                                          CDFISCOPERNF  ||'|'||
                                          DTENTRSAID    ||'|'||
                                          CDTPITEMPROD  ||'|'||
                                          VRTOTAITEM    ||'|'||
                                          VRBASECALCST  ||'|'||
                                          VRIMPOSTOST   ||'|'||
                                          VRALIQITEM    ||'|'||
                                          VRIMPOSTOICMS,1 , 126) AS DSARQMAG, DTENTRSAID
                                  FROM(SELECT '03',
                                              RPAD(NVL(REPLACE(REPLACE(REPLACE(REPLACE(NF.NRINSJURNF,'.',''),',',''),'-',''),'/',''),'0'),14,' ') AS NRINSJURNF,
                                              RPAD(NVL(EF.SGESTADO,' '),2,' ') AS SGESTADO,
                                              RPAD(NVL(NF.CDMODDOCFISC,' '), 2, ' ') AS CDMODDOCFISC,
                                              RPAD(REPLACE(NVL(NF.CDSERIEENTR,' '),'-',' '),3,' ') AS CDSERIEENTR,
                                              SUBSTR(LPAD(NF.NRNOTAFISC,10,'0'),5,6) AS NRNOTAFISC,
                                              RPAD(NVL(NF.CDFISCOPERIT,'0'),4,' ') AS CDFISCOPERNF,
                                              TO_CHAR(NF.DTENTRSAID,'ddmmyyyy') AS DTENTRSAID,
                                              LPAD(NF.CDTPITEMPROD,13,'0') AS CDTPITEMPROD,
                                              REPLACE(LTRIM(TO_CHAR(ROUND(NF.VRTOTAITEM,2),'999999999990.00')),'.',',') AS VRTOTAITEM,
                                              REPLACE(LTRIM(TO_CHAR(ROUND(NF.VRBASECALCNF,2),'999999999990.00')),'.',',') AS VRBASECALCST,
                                              REPLACE(LTRIM(TO_CHAR(ROUND(NF.VRIMPOSTOST,2),'999999999990.00')),'.',',') AS VRIMPOSTOST,
                                              REPLACE(LTRIM(TO_CHAR(ROUND(NF.VRALIQITEM,2),'999999999990.00')),'.',',') AS VRALIQITEM,
                                              REPLACE(LTRIM(TO_CHAR(ROUND(NF.VRIMPOSTOICMS,2),'999999999990.00')),'.',',') AS VRIMPOSTOICMS
                                       FROM FORNECEDOR FO, ENDEFORN EF,
                                          (SELECT N.CDFILIAL, N.NRLANCTONF, N.NRINSJURNF, N.CDFORNECED, N.DTENTRSAID,
                                                  DECODE(N.CDSERIEENTR, '0', ' ', N.CDSERIEENTR) AS CDSERIEENTR,
                                                  N.NRNOTAFISC, I.CDFISCOPERIT, N.CDMODDOCFISC, R.CDTPITEMPROD, IT.VRTOTAITEM,
                                                  I.VRALIQITEM,
                                                  SUM(DECODE(I.CDFISCALIT,'1',I.VRBASECALCIT,0)) AS VRBASECALCNF,
                                                  SUM(DECODE(I.CDFISCALIT,'1',I.VRIMPOSTOIT,0))  AS VRIMPOSTOST,
                                                  VALOR_IMPOSTO_ITEM_NFE(I.CDFILIAL, I.NRLANCTONF, I.NRSEQITPROD, '00', 'I','1') AS VRIMPOSTOICMS
                                            FROM NOTAFISCAL N, ITEMPRODUTO IT, ITEMPROIMPOS I, MODDOCFISC MD, IMPOSTO IM, RELACPRODITGEN R
                                            WHERE (N.CDEMPRESA       = P_CDEMPRESA)     AND
                                                  (N.CDINSCESTA      = P_CDINSCESTA)    AND
                                                  (N.DTENTRSAID BETWEEN P_DTINICIAL  AND P_DTFINAL) AND
                                                  (IM.CDIMPOSTONFE   = '80')     AND
                                                  (IM.CDIMPOSTO      = I.CDIMPOSTO)     AND
                                                  (N.CDFILIAL   = IT.CDFILIAL)          AND
                                                  (N.NRLANCTONF = IT.NRLANCTONF)        AND
                                                  (N.IDENTRSAIDOP = 'E' )               AND
                                                  (IT.CDFILIAL = I.CDFILIAL)            AND
                                                  (IT.NRLANCTONF = I.NRLANCTONF)        AND
                                                  (IT.NRSEQITPROD = I.NRSEQITPROD)      AND
                                                  (I.NRITPROIMPOS = '1')                AND
                                                  (N.IDSTATUS IN ('A', 'E'))            AND
                                                  (N.CDMODDOCFISC    = MD.CDMODDOCFISC) AND
                                                  (R.CDPRODUTO(+) = IT.CDPRODUTO)       AND
                                                  (R.CDLIVROMAG(+) = P_CDLIVROMAG)      AND
                                                  (SUBSTR(I.CDSITUTRIBIT,2,2) NOT IN ('10','70'))  /*SUBSTITUIDO*/
                                            GROUP BY N.CDFILIAL, N.NRLANCTONF, N.NRINSJURNF, N.CDFORNECED, N.DTENTRSAID,
                                                     DECODE(N.CDSERIEENTR, '0', ' ', N.CDSERIEENTR),
                                                     N.NRNOTAFISC, I.CDFISCOPERIT, N.CDMODDOCFISC, R.CDTPITEMPROD, IT.VRTOTAITEM, VRALIQITEM,
                                                     VALOR_IMPOSTO_ITEM_NFE(I.CDFILIAL, I.NRLANCTONF, I.NRSEQITPROD, '00', 'I','1')) NF
                                       WHERE (FO.CDFORNECED      = NF.CDFORNECED  )              AND
                                             (EF.CDFORNECED(+)   = FO.CDFORNECED  )              AND
                                             (EF.IDTPENDEFORN(+) = NVL(FO.IDENDEMISFOR,'P')))NF
                                  ORDER BY DTENTRSAID) LOOP

         INSERT INTO ARQMAGNETICO
           (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
         VALUES
           ('30'||LPAD(RTRIM(V_QTDEREG03), 8, '0'), C_REGISTRO_03M.DSARQMAG, P_NRSEQARQMAG);

         V_QTDEREG03 := NVL(V_QTDEREG03,0)+1;
      END LOOP;


      V_QTDEREG04 := 0;
      FOR C_REGISTRO_04M IN (SELECT SUBSTR(
                                            '04'          ||'|'||
                                            REPLACE(DTINVEFISCAL,'/','')||'|'||
                                            REPLACE(DTINVEFISCAL,'/','')  ||'|'||
                                            V_NRINSJURIEST||'|'||
                                            LPAD(R.CDTPITEMPROD,13,'0') ||'|'||
                                            P.CDARVPROD   ||'|'||
                                            P.VRFATOCONV,1,126) AS DSARQMAG
                             FROM INVENTFISC I, RELACPRODITGEN R, PRODUTO P
                            WHERE I.CDEMPRESA     = P_CDEMPRESA
                              AND I.CDINSCESTA    = P_CDINSCESTA
                              AND I.DTINVEFISCAL BETWEEN P_DTINICIAL AND P_DTFINAL
                              AND I.CDPRODUTO = P.CDPRODUTO
                              AND R.CDPRODUTO(+) = I.CDPRODUTO
                              AND R.CDLIVROMAG(+) = P_CDLIVROMAG
                            ORDER BY DTINVEFISCAL) LOOP

         INSERT INTO ARQMAGNETICO
           (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
         VALUES
           ('40'||LPAD(RTRIM(V_QTDEREG04), 8, '0'), C_REGISTRO_04M.DSARQMAG, P_NRSEQARQMAG);

         V_QTDEREG04 := NVL(V_QTDEREG04,0)+1;
      END LOOP;

      FOR C_REGISTRO_01M IN (SELECT SUBSTR(
                              '01'                                  ||'|'||
                              V_NRINSJURIEST                        ||'|'||
                              RTRIM(V_CDINSCESTA)                   ||'|'||
                              V_NMRAZSOCEMPR                        ||'|'||
                              TO_CHAR(TO_DATE(P_DTINICIAL),'mmyyyy')||'|'||
                              V_QTDEREG02                           ||'|'||
                              V_QTDEREG03                           ||'|'||
                              V_QTDEREG04                           ||'|'||
                              P_FINALIDADE,1,126) AS DSARQMAG
                           FROM DUAL ) LOOP

         INSERT INTO ARQMAGNETICO
           (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
         VALUES
           ('10'||LPAD(RTRIM('00'), 8, '0'), C_REGISTRO_01M.DSARQMAG, P_NRSEQARQMAG);
      END LOOP;

   ELSE
      IF P_EXPORTAR = 'D' THEN /*ESTOQUE DIARIO*/
         FOR C_REGISTRO_01D IN (SELECT SUBSTR(
                                 '01'||
                                 V_NRINSJURIEST||
                                 V_CDINSCESTA||
                                 V_NMRAZSOCEMPR||
                                 TO_CHAR(TO_DATE(P_DTINICIAL),'mmyyyy')||
                                 V_QTDEREG02||
                                 P_FINALIDADE,1,126) AS DSARQMAG
                              FROM DUAL ) LOOP

            INSERT INTO ARQMAGNETICO
              (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
            VALUES
              ('10'||LPAD(RTRIM('00'), 8, '0'), C_REGISTRO_01D.DSARQMAG, P_NRSEQARQMAG);
         END LOOP;
      END IF;
   END IF;
END;
/

-- VERSAO --
BEGIN ATUVEROBJ('RE_ESTOQUE','5.67.000'); END;
/