DROP TABLE TMP_LVELETRONICO
/

CREATE GLOBAL TEMPORARY TABLE TMP_LVELETRONICO
(
   CDREGISTRO      VARCHAR2(04)   NOT NULL,
   CDCONTREGI      NUMBER         NOT NULL,
   CDCHAVEREG      VARCHAR2(50)   NOT NULL,
   CDCAMPO1        VARCHAR2(50)   NULL,
   CDCAMPO2        VARCHAR2(50)   NULL,
   CDCAMPO3        VARCHAR2(50)   NULL,
   CDCAMPO4        VARCHAR2(50)   NULL,
   CDCAMPO5        VARCHAR2(50)   NULL,
   CONSTRAINT PK_TMP_LVELETRONICO PRIMARY KEY(CDREGISTRO, CDCONTREGI, CDCHAVEREG)) ON COMMIT PRESERVE ROWS
/

create or replace FUNCTION ARQDIGITAL_BLCA( 
   P_CDEMPRESA    IN EMPRESA.CDEMPRESA%TYPE, 
   P_CDINSCMUNIC  IN VARCHAR2, 
   P_DTINICIAL    IN DATE, 
   P_DTFINAL      IN DATE,
   P_NRSEQARQMAG  IN ARQMAGNETICO.NRSEQARQMAG%TYPE) RETURN NUMBER 
AS 
   V_REGAAAAA        NUMBER; 
   V_MOVIMENTO       NUMBER; 
   V_SEQUENCIAL      VARCHAR2(100); 
   V_CDINSCMUNIC     VARCHAR2(100); 
   V_PRODUTO         VARCHAR2(100); 
   V_SEQUPROD        NUMBER; 
   V_COD_PAR         VARCHAR2(100); 
   V_FILIAL          VARCHAR2(04); 
BEGIN 
   V_REGAAAAA := NVL(V_REGAAAAA,0) + 1;
   V_CDINSCMUNIC := P_CDINSCMUNIC;
   SELECT MAX(CDFILIAL)
     INTO V_FILIAL
     FROM FILIAL
    WHERE CDINSCESTA = P_CDINSCMUNIC;

   FOR A_020 IN (SELECT '|A020'                                                                                    || '|' ||
                        '0'                                                                                        || '|' ||
                        '1'                                                                                        || '|' ||
                        NF.CDFORNECED                                                                              || '|' ||
                        NF.CDMODDOCFISC                                                                            || '|' ||
                        NF.STATUS                                                                                  || '|' ||
                        NF.SERIE                                                                                   || '|' ||
                         ''                                                                                        || '|' ||
                        NF.NRNOTAFISC                                                                              || '|' ||
                        TO_CHAR(NF.DTEMISSAO, 'ddmmyyyy')                                                          || '|' ||
                        GERA_CFPS(NF.CDFISCOPERNF)                                                                 || '|' ||
                        DECODE(GERA_CFPS(NF.CDFISCOPERNF), '9104', '0', MU.CDMUNICIBGE )                           || '|' ||
                        NF.CDFISCOPERNF                                                                            || '|' ||
                        NVL(REPLACE(LTRIM(TO_CHAR(ROUND(NF.VRCONTABILNF,2), '999999999990.00')), '.', ','),'0,00') || '|' ||
                        '0'                                                                                        || '|' ||
                        '0,00'                                                                                     || '|' ||
                        '0,00'                                                                                     || '|' ||
                        NVL(REPLACE(LTRIM(TO_CHAR(ROUND(NF.VRCONTABILNF,2), '999999999990.00')), '.', ','),'0,00') || '|' ||
                        '0,00'                                                                                     || '|' ||
                        '0,00'                                                                                     || '|' ||
                        '0,00'                                                                                     || '|' ||
                        NVL(REPLACE(LTRIM(TO_CHAR(ROUND(NF.VRBASEISSQN, 2), '999999999990.00')), '.', ','),'0,00') || '|' ||
                        NVL(REPLACE(LTRIM(TO_CHAR(ROUND(NF.VRISSQN,     2), '999999999990.00')), '.', ','),'0,00') || '|' ||
                        NVL(REPLACE(LTRIM(TO_CHAR(ROUND(NF.VRBASERETENC,2), '999999999990.00')), '.', ','),'0,00') || '|' ||
                        NVL(REPLACE(LTRIM(TO_CHAR(ROUND(NF.VRISSQNRET,  2), '999999999990.00')), '.', ','),'0,00') || '|' ||
                        '' || '|' AS ARQ_A020,
                        NF.CDFILIAL, NF.NRLANCTONF, NF.CDIMPOSTO, NF.CDFORNECED AS CPF_CNPJ, IDORIGDESTNF
                   FROM FORNECEDOR CL, ENDEFORN EN,  MUNICIPIO MU,
                        (SELECT SUM(DECODE(IM.CDIMPOSTONFE,'30',DECODE(I.CDFISCALNF,'1',VRBASECALCNF,0),0)) AS VRBASEISSQN,
                                SUM(DECODE(IM.CDIMPOSTONFE,'35',DECODE(I.CDFISCALNF,'1',VRBASECALCNF,0),0)) AS VRBASERETENC,
                                SUM(DECODE(IM.CDIMPOSTONFE,'30',DECODE(I.CDFISCALNF,'1',VRIMPOSTONF,0),0)) AS VRISSQN,
                                SUM(DECODE(IM.CDIMPOSTONFE,'35',DECODE(I.CDFISCALNF,'1',VRIMPOSTONF,0),0)) AS VRISSQNRET,
                                SUM(DECODE(IM.CDIMPOSTONFE,'30',VRCONTABILNF,0)) AS VRCONTABILNF,
                                I.CDFILIAL, I.NRLANCTONF, N.CDFORNECED, N.NRNOTAFISC, N.DTEMISSAO, N.NRLANCTBDOC,
                                DECODE(N.IDENTRSAIDOP, 'E', N.CDSERIEENTR, N.CDSERIE) AS SERIE, I.CDFISCOPERNF,
                                N.CDMODDOCFISC, DECODE(N.IDSTATUS, 'C', '02', '00') AS STATUS,
                                I.CDIMPOSTO, IDORIGDESTNF
                           FROM MAPAAPURAC M,  NOTAFISCAL N, IMPOSTONF I, IMPOSTO IM
                          WHERE M.CDEMPRESA     = P_CDEMPRESA
                            AND M.CDINSCMUNI    = V_CDINSCMUNIC
                            AND M.DTINIPERIODO  = P_DTINICIAL
                            AND M.DTFIMPERIODO  = P_DTFINAL
                            AND M.CDIMPOSTO     IN ('30','35')
                            AND I.NRSEQUMAPAPU  = M.NRSEQUMAPAPU
                            AND I.CDIMPOSTO     = IM.CDIMPOSTO
                            AND IM.CDIMPOSTONFE IN ('30','35')
                            AND N.CDMODDOCFISC  IN ('01','03','55')
                            AND N.CDFILIAL      = I.CDFILIAL
                            AND N.NRLANCTONF    = I.NRLANCTONF
                            AND NVL(I.IDIMPOSTONF, '1') <> '3'
                            AND ((N.IDSTATUS = 'A') OR (N.IDSTATUS = 'E'))
                          GROUP BY I.CDFILIAL, I.NRLANCTONF, N.CDFORNECED, N.NRNOTAFISC, N.DTEMISSAO, N.NRLANCTBDOC,
                                   DECODE(N.IDENTRSAIDOP, 'E', N.CDSERIEENTR, N.CDSERIE), I.CDFISCOPERNF,
                                   N.CDMODDOCFISC, IDORIGDESTNF, DECODE(N.IDSTATUS, 'C', '02', '00'), I.CDIMPOSTO) NF
                  WHERE NF.CDFORNECED    = CL.CDFORNECED
                    AND EN.CDFORNECED    = CL.CDFORNECED
                    AND NVL(EN.IDTPENDEFORN, 'P') = 'P'
                    AND MU.CDPAIS       = EN.CDPAIS
                    AND MU.SGESTADO     = EN.SGESTADO
                    AND MU.CDMUNICIPIO  = EN.CDMUNICIPIO
                  UNION ALL
                 SELECT '|A020'                                                                                    || '|' ||
                        '1'                                                                                        || '|' ||
                        '0'                                                                                        || '|' ||
                        NF.CDCLIENTE                                                                               || '|' ||
                        NF.CDMODDOCFISC                                                                            || '|' ||
                        NF.STATUS                                                                                  || '|' ||
                        NF.SERIE                                                                                   || '|' ||
                        ''                                                                                         || '|' ||
                        NF.NRNOTAFISC                                                                              || '|' ||
                        TO_CHAR(NF.DTEMISSAO, 'ddmmyyyy')                                                          || '|' ||
                        GERA_CFPS(NF.CDFISCOPERNF)                                                                 || '|' ||
                        DECODE(GERA_CFPS(NF.CDFISCOPERNF), '9104', '0', MU.CDMUNICIBGE )                           || '|' ||
                        NF.CDFISCOPERNF                                                                            || '|' ||
                        NVL(REPLACE(LTRIM(TO_CHAR(ROUND(NF.VRCONTABILNF,2), '999999999990.00')), '.', ','),'0,00') || '|' ||
                        '0'                                                                                        || '|' ||
                        '0,00'                                                                                     || '|' ||
                        '0,00'                                                                                     || '|' ||
                        NVL(REPLACE(LTRIM(TO_CHAR(ROUND(NF.VRCONTABILNF,2), '999999999990.00')), '.', ','),'0,00') || '|' ||
                        '0,00'                                                                                     || '|' ||
                        '0,00'                                                                                     || '|' ||
                        '0,00'                                                                                     || '|' ||
                        NVL(REPLACE(LTRIM(TO_CHAR(ROUND(NF.VRBASEISSQN, 2), '999999999990.00')), '.', ','),'0,00') || '|' ||
                        NVL(REPLACE(LTRIM(TO_CHAR(ROUND(NF.VRISSQN,     2), '999999999990.00')), '.', ','),'0,00') || '|' ||
                        NVL(REPLACE(LTRIM(TO_CHAR(ROUND(NF.VRBASERETENC,2), '999999999990.00')), '.', ','),'0,00') || '|' ||
                        NVL(REPLACE(LTRIM(TO_CHAR(ROUND(NF.VRISSQNRET,  2), '999999999990.00')), '.', ','),'0,00') || '|' ||
                        '' || '|' AS ARQ_A020,
                        NF.CDFILIAL, NF.NRLANCTONF, NF.CDIMPOSTO, NF.CDCLIENTE AS CPF_CNPJ, IDORIGDESTNF
                   FROM CLIENTE CL, ENDECLIE EN,  MUNICIPIO MU,
                        (SELECT SUM(DECODE(IM.CDIMPOSTONFE,'31',DECODE(I.CDFISCALNF,'1',VRBASECALCNF,0),0)) AS VRBASEISSQN,
                                SUM(DECODE(IM.CDIMPOSTONFE,'36',DECODE(I.CDFISCALNF,'1',VRBASECALCNF,0),0)) AS VRBASERETENC,
                                SUM(DECODE(IM.CDIMPOSTONFE,'31',DECODE(I.CDFISCALNF,'1',VRIMPOSTONF,0),0)) AS VRISSQN,
                                SUM(DECODE(IM.CDIMPOSTONFE,'36',DECODE(I.CDFISCALNF,'1',VRIMPOSTONF,0),0)) AS VRISSQNRET,
                                SUM(DECODE(IM.CDIMPOSTONFE,'31',VRCONTABILNF,0)) AS VRCONTABILNF,
                                I.CDFILIAL, I.NRLANCTONF, N.CDCLIENTE, N.NRNOTAFISC, N.DTEMISSAO, N.NRLANCTBDOC,
                                DECODE(N.IDENTRSAIDOP, 'E', N.CDSERIEENTR, N.CDSERIE) AS SERIE, I.CDFISCOPERNF,
                                N.CDMODDOCFISC, DECODE(N.IDSTATUS, 'C', '02', '00') AS STATUS,
                                I.CDIMPOSTO, IDORIGDESTNF
                           FROM MAPAAPURAC M, NOTAFISCAL N, IMPOSTONF I, IMPOSTO IM
                          WHERE M.CDEMPRESA     = P_CDEMPRESA
                            AND M.CDINSCMUNI    = V_CDINSCMUNIC
                            AND M.DTINIPERIODO  = P_DTINICIAL
                            AND M.DTFIMPERIODO  = P_DTFINAL
                            AND M.CDIMPOSTO     IN ('31','36')
                            AND I.NRSEQUMAPAPU  = M.NRSEQUMAPAPU
                            AND I.CDIMPOSTO     = IM.CDIMPOSTO
                            AND IM.CDIMPOSTONFE IN ('31','36')
                            AND GERA_CFPS(I.CDFISCOPERNF) IN ('9101', '9103','9104')
                            AND N.CDMODDOCFISC  IN ('01','03','55')
                            AND N.CDFILIAL      = I.CDFILIAL
                            AND N.NRLANCTONF    = I.NRLANCTONF
                            AND NVL(I.IDIMPOSTONF, '1') <> '3'
                            AND ((N.IDSTATUS = 'A') OR (N.IDSTATUS = 'E'))
                         GROUP BY I.CDFILIAL, I.NRLANCTONF, N.CDCLIENTE, N.NRNOTAFISC, N.DTEMISSAO, N.NRLANCTBDOC,
                                  DECODE(N.IDENTRSAIDOP, 'E', N.CDSERIEENTR, N.CDSERIE), I.CDFISCOPERNF,
                                  N.CDMODDOCFISC, DECODE(N.IDSTATUS, 'C', '02', '00'), I.CDIMPOSTO, IDORIGDESTNF) NF
                  WHERE NF.CDCLIENTE    = CL.CDCLIENTE
                    AND EN.CDCLIENTE    = CL.CDCLIENTE
                    AND NVL(EN.IDTPENDECLIE, 'P') = 'P'
                    AND MU.CDPAIS       = EN.CDPAIS
                    AND MU.SGESTADO     = EN.SGESTADO
                    AND MU.CDMUNICIPIO  = EN.CDMUNICIPIO) LOOP

      INSERT INTO ARQMAGNETICO
        (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
      VALUES
        ('11'||LPAD(RTRIM(V_REGAAAAA), 8, '0'), A_020.ARQ_A020, P_NRSEQARQMAG);
      V_REGAAAAA := NVL(V_REGAAAAA,0) + 1;
      V_SEQUENCIAL := LPAD(RTRIM(TO_CHAR(TO_NUMBER('1'))),3,'0');

      SELECT MAX(CDCHAVEREG)
        INTO V_COD_PAR
        FROM TMP_LVELETRONICO
       WHERE CDCHAVEREG = A_020.CPF_CNPJ
         AND CDREGISTRO = '0150';

      IF (V_COD_PAR IS NULL) AND (A_020.CPF_CNPJ IS NOT NULL) THEN
         V_SEQUPROD := NVL(V_SEQUPROD,0) + 1;
         INSERT INTO TMP_LVELETRONICO
           (CDREGISTRO, CDCONTREGI, CDCHAVEREG, CDCAMPO1) 
         VALUES 
           ('0150', V_SEQUPROD, A_020.CPF_CNPJ, A_020.IDORIGDESTNF);
      END IF;
      
      V_COD_PAR := '';
      FOR A_200 IN (SELECT P.CDARVPROD              || '|' ||
                           NVL(REPLACE(LTRIM(TO_CHAR(ROUND(VRUNITITEM,2), '999999999990.000')), '.', ','),'0,000')      || '|' ||
                           NVL(REPLACE(LTRIM(TO_CHAR(ROUND(QTITEMPROD,2), '999999999990.000')), '.', ','),'0,000')      || '|' ||
                           SGUNIDADE                || '|' ||
                           NVL(REPLACE(LTRIM(TO_CHAR(SUM(ROUND(VRCONTITIMP,2)), '999999999990.00')), '.', ','),'0,00')  || '|' ||
                           '0,00'                   || '|' ||
                           SUBSTR(CDSITUTRIBIT,1,2) || '|' ||
                           NVL(REPLACE(LTRIM(TO_CHAR(SUM(ROUND(VRBASECALCIT,2)), '999999999990.00')), '.', ','),'0,00') || '|' ||
                           NVL(REPLACE(LTRIM(TO_CHAR(ROUND(VRALIQITEM,2), '999999999990.00')), '.', ','),'0,00')        || '|' ||
                           NVL(REPLACE(LTRIM(TO_CHAR(SUM(ROUND(VRIMPOSTOIT,2)), '999999999990.00')), '.', ','),'0,00')  || '|' AS A200,
                           P.CDPRODUTO, P.CDARVPROD
                      FROM ITEMPROIMPOS I, ITEMPRODUTO IT, PRODUTO P 
                     WHERE I.CDFILIAL    = A_020.CDFILIAL
                       AND I.NRLANCTONF  = A_020.NRLANCTONF
                       AND I.CDIMPOSTO   = A_020.CDIMPOSTO
                       AND I.NRSEQITPROD = IT.NRSEQITPROD
                       AND I.CDFILIAL    = IT.CDFILIAL
                       AND I.NRLANCTONF  = IT.NRLANCTONF
                       AND P.CDPRODUTO   = IT.CDPRODUTO
                     GROUP BY I.NRSEQITPROD, P.CDARVPROD, 
                           NVL(REPLACE(LTRIM(TO_CHAR(ROUND(QTITEMPROD,2), '999999999990.000')), '.', ','),'0,000'), P.CDPRODUTO, 
                           SGUNIDADE, NVL(REPLACE(LTRIM(TO_CHAR(ROUND(VRUNITITEM,2), '999999999990.000')), '.', ','),'0,000'), 
                           CDSITUTRIBIT, NVL(REPLACE(LTRIM(TO_CHAR(ROUND(VRALIQITEM,2), '999999999990.00')), '.', ','),'0,00') 
                     ORDER BY I.NRSEQITPROD) LOOP

         INSERT INTO ARQMAGNETICO 
           (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG) 
         VALUES 
           ('11'||LPAD(RTRIM(V_REGAAAAA), 8, '0'), '|A200' || '|' || V_SEQUENCIAL || '|' || A_200.A200, P_NRSEQARQMAG); 
         V_SEQUENCIAL := LPAD(RTRIM(TO_CHAR(TO_NUMBER(RTRIM(V_SEQUENCIAL))+1)), 3,'0'); 
         V_REGAAAAA := NVL(V_REGAAAAA,0) + 1; 
          
         SELECT MAX(CDCHAVEREG) 
           INTO V_PRODUTO 
           FROM TMP_LVELETRONICO 
          WHERE CDCHAVEREG = A_200.CDPRODUTO 
            AND CDREGISTRO = '0200';

         IF V_PRODUTO IS NULL THEN 
            V_SEQUPROD := NVL(V_SEQUPROD,0) + 1; 
            INSERT INTO TMP_LVELETRONICO 
              (CDREGISTRO, CDCONTREGI, CDCHAVEREG, CDCAMPO1) 
            VALUES 
              ('0200', V_SEQUPROD, A_200.CDPRODUTO, A_200.CDARVPROD); 
         END IF; 
         V_PRODUTO := '';
      END LOOP; 
   END LOOP;

   IF V_REGAAAAA > 2 THEN 
      V_MOVIMENTO := 0; 
   ELSE 
      V_MOVIMENTO := 1; 
   END IF; 
    
   FOR C_REGA001 IN (SELECT '|A001'        || '|' ||
                            V_MOVIMENTO    || '|' ||
                            M.CDMUNICIBGE  || '|' AS ARQ_A001
                       FROM EMPRESA E, MUNICIPIO M
                      WHERE E.CDPAIS      = M.CDPAIS
                        AND E.SGESTADO    = M.SGESTADO
                        AND E.CDMUNICIPIO = M.CDMUNICIPIO
                        AND E.CDEMPRESA   = P_CDEMPRESA) LOOP
      INSERT INTO ARQMAGNETICO
        (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
      VALUES
        ('11'||LPAD(RTRIM('0'), 8, '0'), C_REGA001.ARQ_A001, P_NRSEQARQMAG);
        V_REGAAAAA := NVL(V_REGAAAAA,0) + 1;
   END LOOP;
    
   INSERT INTO ARQMAGNETICO 
     (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG) 
   VALUES 
     ('12'||LPAD(RTRIM(V_REGAAAAA), 8, '0'), '|A990' || '|' ||V_REGAAAAA|| '|', P_NRSEQARQMAG); 
   RETURN(V_REGAAAAA); 
END; 
/

-- VERSAO --
BEGIN ATUVEROBJ('ARQDIGITAL_BLCA','5.30.000'); END;
/

