CREATE OR REPLACE PROCEDURE SPED_CONTABIL_CABECALHO (
   P_NRSEQARQMAG  IN ARQMAGNETICO.NRSEQARQMAG%TYPE,
   P_CDEMPRESA    IN EMPRESA.CDEMPRESA%TYPE,
   P_DTINICIAL    IN DATE,
   P_DTFINAL      IN DATE,
   P_IDSPED       IN VARCHAR2, 
   P_PORTE IN VARCHAR2,
   P_FINALIDADE IN VARCHAR2,
   P_MUDPLANCTB IN VARCHAR2,
   P_CODPLANCTB IN VARCHAR2,
   P_NRORDREGARQ  OUT INTEGER)

IS
   V_NRORDREGARQ INTEGER;
   V_AUXILIAR    VARCHAR2(200);
   V_ANO NUMBER;

BEGIN
   IF (P_IDSPED = 'S') THEN
      BEGIN
         V_NRORDREGARQ := 0;
         V_ANO := TO_NUMBER(TO_CHAR(P_DTFINAL, 'YYYY'));
         
         IF V_ANO = 2013 THEN
            SELECT '|0000|' || 
                    'LECD|' || 
                   TO_CHAR(P_DTINICIAL,'DDMMYYYY')||              '|'|| 
                   TO_CHAR(P_DTFINAL,'DDMMYYYY')||                '|'|| 
                   E.NMRAZSOCEMPR||                               '|'|| 
                   E.NRINSJUREMPR||                               '|'|| 
                   E.SGESTADO||                                   '|'|| 
                   NVL(E.NRINSCESTA,'')||                         '|'|| 
                   LPAD(NVL(M.CDMUNICIBGE,'0'),7,'0')||           '|'|| 
                   NVL(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(NVL(E.CDINSCMUNI,''), ' ', ''),'.',''),',',''),'-',''),'/',''),'')|| '|' || 
                   '|'  || 

                   
                   '0|' || 
                   '1|' || 

                   P_FINALIDADE|| '|' || 
                   '|'  || 
                   '|'  || 
                   P_PORTE ||'|'    

                    AS DSLINHA
            INTO V_AUXILIAR
            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;
         ELSIF V_ANO = 2014 THEN 
            SELECT '|0000|' || 
                    'LECD|' || 
                   TO_CHAR(P_DTINICIAL,'DDMMYYYY')||              '|'|| 
                   TO_CHAR(P_DTFINAL,'DDMMYYYY')||                '|'|| 
                   E.NMRAZSOCEMPR||                               '|'|| 
                   E.NRINSJUREMPR||                               '|'|| 
                   E.SGESTADO||                                   '|'|| 
                   NVL(E.NRINSCESTA,'')||                         '|'|| 
                   LPAD(NVL(M.CDMUNICIBGE,'0'),7,'0')||           '|'|| 
                   NVL(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(NVL(E.CDINSCMUNI,''), ' ', ''),'.',''),',',''),'-',''),'/',''),'')|| '|' || 
                   '|'  || 

                   
                   '0|' || 
                   '1|' || 

                   P_FINALIDADE|| '|' || 
                   '|'  || 
                   '|'  || 
                   P_PORTE||'|'||  
                   '0|' ||   
                   '|'    

                    AS DSLINHA
            INTO V_AUXILIAR
            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;
         ELSIF V_ANO = 2015 THEN 
            SELECT '|0000|' || 
                    'LECD|' || 
                   TO_CHAR(P_DTINICIAL,'DDMMYYYY')||              '|'|| 
                   TO_CHAR(P_DTFINAL,'DDMMYYYY')||                '|'|| 
                   E.NMRAZSOCEMPR||                               '|'|| 
                   E.NRINSJUREMPR||                               '|'|| 
                   E.SGESTADO||                                   '|'|| 
                   NVL(E.NRINSCESTA,'')||                         '|'|| 
                   LPAD(NVL(M.CDMUNICIBGE,'0'),7,'0')||           '|'|| 
                   NVL(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(NVL(E.CDINSCMUNI,''), ' ', ''),'.',''),',',''),'-',''),'/',''),'')|| '|' || 
                   '|'  || 

                   
                   '0|' || 
                   '1|' || 

                   P_FINALIDADE|| '|' || 
                   '|'  || 
                   '|'  || 
                   P_PORTE||'|'||  
                   '0|' ||   
                   '|'  ||  
                   'N|'     

                    AS DSLINHA
            INTO V_AUXILIAR
            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;
         ELSIF V_ANO IN (2016,2017,2018) THEN 
            SELECT '|0000|' || 
                    'LECD|' || 
                   TO_CHAR(P_DTINICIAL,'DDMMYYYY')||              '|'|| 
                   TO_CHAR(P_DTFINAL,'DDMMYYYY')||                '|'|| 
                   E.NMRAZSOCEMPR||                               '|'|| 
                   E.NRINSJUREMPR||                               '|'|| 
                   E.SGESTADO||                                   '|'|| 
                   NVL(E.NRINSCESTA,'')||                         '|'|| 
                   LPAD(NVL(M.CDMUNICIBGE,'0'),7,'0')||           '|'|| 
                   NVL(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(NVL(E.CDINSCMUNI,''), ' ', ''),'.',''),',',''),'-',''),'/',''),'')|| '|' || 
                   '|'  || 

                   
                   '0|' || 
                   '1|' || 

                   P_FINALIDADE|| '|' || 
                   '|'  || 

                   P_PORTE||'|'||  
                   '0|' ||   
                   '|'  ||  
                   'N|' ||   
                   'N|'     

                    AS DSLINHA
            INTO V_AUXILIAR
            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;

         ELSIF V_ANO >= 2019 THEN 
            SELECT '|0000|' || 
                    'LECD|' || 
                   TO_CHAR(P_DTINICIAL,'DDMMYYYY')||              '|'|| 
                   TO_CHAR(P_DTFINAL,'DDMMYYYY')||                '|'|| 
                   E.NMRAZSOCEMPR||                               '|'|| 
                   E.NRINSJUREMPR||                               '|'|| 
                   E.SGESTADO||                                   '|'|| 
                   NVL(E.NRINSCESTA,'')||                         '|'|| 
                   LPAD(NVL(M.CDMUNICIBGE,'0'),7,'0')||           '|'|| 
                   NVL(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(NVL(E.CDINSCMUNI,''), ' ', ''),'.',''),',',''),'-',''),'/',''),'')|| '|' || 
                   '|'  || 

                   
                   '0|' || 
                   '1|' || 

                   P_FINALIDADE|| '|' || 
                   '|'  || 

                   P_PORTE||'|'||  
                   '0|' ||   
                   '|'  ||  
                   'N|' ||   
                   'N|' ||    
                   '0|'||
                    P_MUDPLANCTB||'|'|| 
                    P_CODPLANCTB||'|'    

                    AS DSLINHA
            INTO V_AUXILIAR
            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;
         END IF;

         V_NRORDREGARQ := V_NRORDREGARQ + 1;
         INSERT INTO ARQMAGNETICO
           (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
         VALUES
           (LPAD(TO_CHAR(V_NRORDREGARQ),10,'0'), V_AUXILIAR, P_NRSEQARQMAG);

         V_NRORDREGARQ := V_NRORDREGARQ + 1;
         INSERT INTO ARQMAGNETICO
           (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
         VALUES
           (LPAD(TO_CHAR(V_NRORDREGARQ),10,'0'), '|0001|0|', P_NRSEQARQMAG);

         FOR C_0007 IN (
         SELECT '|0007|'||
                I.SGESTADO||'|'||
                I.CDINSCESTA||'|' AS DSLINHA
         FROM INSCRESTAD I
         WHERE I.CDEMPRESA = P_CDEMPRESA
         ORDER BY I.SGESTADO, I.CDINSCESTA) LOOP

            V_NRORDREGARQ := V_NRORDREGARQ + 1;
            INSERT INTO ARQMAGNETICO
              (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
            VALUES
              (LPAD(TO_CHAR(V_NRORDREGARQ),10,'0'), C_0007.DSLINHA, P_NRSEQARQMAG);
         END LOOP;
		 
		 FOR C_0035 IN (
         SELECT '|0035|'||
                P.NRINSJURPROJ||'|'||
                P.NMPROJETO||'|' AS DSLINHA
         FROM PROJETOSCP P , FILIAL F
         WHERE P.CDEMPRESA = P_CDEMPRESA
		   AND P.NRSEQPROJETO = F.NRSEQPROJETO
         ORDER BY P.NRINSJURPROJ, P.NMPROJETO
                                              ) LOOP

            V_NRORDREGARQ := V_NRORDREGARQ + 1;
            INSERT INTO ARQMAGNETICO
              (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
            VALUES
              (LPAD(TO_CHAR(V_NRORDREGARQ),10,'0'), C_0035.DSLINHA, P_NRSEQARQMAG);
         END LOOP;


		 FOR C_REG0150_REG0180 IN (
         SELECT '|0150'                                                                         ||'|'||                          
                'F' || A.CDFORNECED                                                             ||'|'||                          
                TRIM(F.NMRAZSOCFORN)                                                            ||'|'||                          
                LPAD(P.CDPAISBACEN, 5, '0')                                                     ||'|'||                          
                CASE WHEN IDTPIJURFORN = 'J' THEN
                  REPLACE(REPLACE(REPLACE(REPLACE(F.NRINSJURFORN, '.'), '/'), '-'), '') || '|'                                   
                ELSE
                  '|' || REPLACE(REPLACE(REPLACE(REPLACE(F.NRINSJURFORN, '.'), '/'), '-'), '')                                   
                END                                                                             ||'|'||
                ''                                                                              ||'|'||                          
                E.SGESTADO                                                                      ||'|'||                          
                REPLACE(REPLACE(REPLACE(REPLACE(F.NRINSESTFORN, '.'), '/'), '-'), '')           ||'|'||                          
                ''                                                                              ||'|'||                          
                M.CDMUNICIBGE                                                                   ||'|'||                          
                REPLACE(REPLACE(REPLACE(REPLACE(F.NRINSMUNFORN, '.'), '/'), '-'), '')           ||'|'||                          
                REPLACE(REPLACE(REPLACE(REPLACE(F.NRINSCSUFRAMAFO, '.'), '/'), '-'), '')        ||'|' AS DSLINHA_0150,           
                '|0180'                                                                         ||'|'||                          
                A.IDCODRELAC                                                                    ||'|'||                          
                REPLACE(A.DTINICIAL, '/', '')                                                   ||'|'||                          
                REPLACE(A.DTFINAL, '/', '')                                                     ||'|' AS DSLINHA_0180            
           FROM PARAMABEECD A
           JOIN FORNECEDOR  F ON (F.CDFORNECED = A.CDFORNECED AND A.IDTPPARTECD = 'F')
           JOIN ENDEFORN    E ON (E.CDFORNECED = F.CDFORNECED AND E.IDTPENDEFORN = F.IDENDEMISFOR)
           JOIN ESTADO      T ON (T.CDPAIS = E.CDPAIS AND  T.SGESTADO = E.SGESTADO)
           JOIN MUNICIPIO   M ON (M.CDMUNICIPIO = E.CDMUNICIPIO AND M.SGESTADO = E.SGESTADO)
           JOIN PAIS        P ON (P.CDPAIS = E.CDPAIS)
          UNION ALL
         SELECT '|0150'                                                                         ||'|'||                          
                'C' || A.CDCLIENTE                                                              ||'|'||                          
                TRIM(C.NMRAZSOCCLIE)                                                            ||'|'||                          
                LPAD(P.CDPAISBACEN, 5, '0')                                                     ||'|'||                          
                CASE WHEN IDTPIJURCLIE = 'J' THEN
                  REPLACE(REPLACE(REPLACE(REPLACE(C.NRINSJURCLIE, '.'), '/'), '-'), '') || '|'                                   
                ELSE
                  '|' || REPLACE(REPLACE(REPLACE(REPLACE(C.NRINSJURCLIE, '.'), '/'), '-'), '')                                   
                END                                                                             ||'|'||
                ''                                                                              ||'|'||                          
                E.SGESTADO                                                                      ||'|'||                          
                REPLACE(REPLACE(REPLACE(REPLACE(C.NRINSESTCLIE, '.'), '/'), '-'), '')           ||'|'||                          
                ''                                                                              ||'|'||                          
                M.CDMUNICIBGE                                                                   ||'|'||                          
                REPLACE(REPLACE(REPLACE(REPLACE(C.NRINSMUNICLIE, '.'), '/'), '-'), '')          ||'|'||                          
                REPLACE(REPLACE(REPLACE(REPLACE(C.NRINSCSUFRAMACL, '.'), '/'), '-'), '')        ||'|' AS DSLINHA_0150,           
                '|0180'                                                                         ||'|'||                          
                A.IDCODRELAC                                                                    ||'|'||                          
                REPLACE(A.DTINICIAL, '/', '')                                                   ||'|'||                          
                REPLACE(A.DTFINAL, '/', '')                                                     ||'|' AS DSLINHA_0180            
           FROM PARAMABEECD A
           JOIN CLIENTE     C ON (C.CDCLIENTE = A.CDCLIENTE AND A.IDTPPARTECD = 'C')
           JOIN ENDECLIE    E ON (E.CDCLIENTE = C.CDCLIENTE AND E.IDTPENDECLIE = C.IDENDEMISCLI)
           JOIN ESTADO      T ON (T.CDPAIS = E.CDPAIS AND  T.SGESTADO = E.SGESTADO)
           JOIN MUNICIPIO   M ON (M.CDMUNICIPIO = E.CDMUNICIPIO AND M.SGESTADO = E.SGESTADO)
           JOIN PAIS        P ON (P.CDPAIS = E.CDPAIS)
          UNION ALL
         SELECT '|0150'                                                                         ||'|'||                          
                'L' || A.CDFILIAL                                                               ||'|'||                          
                TRIM(F.NMRAZSOCFILI)                                                            ||'|'||                          
                LPAD(P.CDPAISBACEN, 5, '0')                                                     ||'|'||                          
                CASE WHEN IDTPIJURFILI = 'J' THEN
                  REPLACE(REPLACE(REPLACE(REPLACE(F.NRINSJURFILI, '.'), '/'), '-'), '') || '|'                                   
                ELSE
                  '|' || REPLACE(REPLACE(REPLACE(REPLACE(F.NRINSJURFILI, '.'), '/'), '-'), '')                                   
                END                                                                             ||'|'||
                ''                                                                              ||'|'||                          
                E.SGESTADO                                                                      ||'|'||                          
                REPLACE(REPLACE(REPLACE(REPLACE(F.CDINSCESTA, '.'), '/'), '-'), '')             ||'|'||                          
                ''                                                                              ||'|'||                          
                M.CDMUNICIBGE                                                                   ||'|'||                          
                REPLACE(REPLACE(REPLACE(REPLACE(F.CDINSCMUNI, '.'), '/'), '-'), '')             ||'|'||                          
                REPLACE(REPLACE(REPLACE(REPLACE(F.NRINSCSUFRAMAFI, '.'), '/'), '-'), '')        ||'|' AS DSLINHA_0150,           
                '|0180'                                                                         ||'|'||                          
                A.IDCODRELAC                                                                    ||'|'||                          
                REPLACE(A.DTINICIAL, '/', '')                                                   ||'|'||                          
                REPLACE(A.DTFINAL, '/', '')                                                     ||'|' AS DSLINHA_0180            
           FROM PARAMABEECD A
           JOIN FILIAL      F ON (F.CDFILIAL = A.CDFILIAL AND A.IDTPPARTECD = 'L')
           JOIN ENDEFILI    E ON (E.CDFILIAL = F.CDFILIAL AND E.IDTPENDEFILI = F.IDENDEMISFIL)
           JOIN ESTADO      T ON (T.CDPAIS = E.CDPAIS AND  T.SGESTADO = E.SGESTADO)
           JOIN MUNICIPIO   M ON (M.CDMUNICIPIO = E.CDMUNICIPIO AND M.SGESTADO = E.SGESTADO)
           JOIN PAIS        P ON (P.CDPAIS = E.CDPAIS)) LOOP
         V_NRORDREGARQ := V_NRORDREGARQ + 1;
         INSERT INTO ARQMAGNETICO
           (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
         VALUES
           (LPAD(TO_CHAR(V_NRORDREGARQ),10,'0'), C_REG0150_REG0180.DSLINHA_0150, P_NRSEQARQMAG);

         V_NRORDREGARQ := V_NRORDREGARQ + 1;
         INSERT INTO ARQMAGNETICO
           (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
         VALUES
           (LPAD(TO_CHAR(V_NRORDREGARQ),10,'0'), C_REG0150_REG0180.DSLINHA_0180, P_NRSEQARQMAG);
         END LOOP;

         V_NRORDREGARQ := V_NRORDREGARQ + 1;
         SELECT '|0990|'||
                TO_CHAR(COUNT(NRSEQARQMAG) + 1)||'|'  AS DSLINHA
         INTO V_AUXILIAR
         FROM ARQMAGNETICO
         WHERE NRSEQARQMAG = P_NRSEQARQMAG;

         INSERT INTO ARQMAGNETICO
           (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
         VALUES
           (LPAD(TO_CHAR(V_NRORDREGARQ),10,'0'), V_AUXILIAR, P_NRSEQARQMAG);
         P_NRORDREGARQ := V_NRORDREGARQ;
      END;
   ELSIF (P_IDSPED = 'F') THEN
      BEGIN
         V_NRORDREGARQ := 0;

         SELECT '|0000|'||
                'LALU|'||
                TO_CHAR(P_DTINICIAL,'DDMMYYYY')||'|'|| 
                TO_CHAR(P_DTFINAL,'DDMMYYYY')||'|'|| 
                E.NMRAZSOCEMPR||'|'||
                E.NRINSJUREMPR||'|'||
                E.SGESTADO||'|'||
                NVL(E.NRINSCESTA,'')||'|'||
                LPAD(NVL(M.CDMUNICIBGE,'0'),7,'0')||'|'||
                NVL(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(NVL(E.CDINSCMUNI,''), ' ', ''),'.',''),',',''),'-',''),'/',''),'')||'||0|' AS DSLINHA
         INTO V_AUXILIAR
         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;

         V_NRORDREGARQ := V_NRORDREGARQ + 1;
         INSERT INTO ARQMAGNETICO
           (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
         VALUES
           (LPAD(TO_CHAR(V_NRORDREGARQ),10,'0'), V_AUXILIAR, P_NRSEQARQMAG);

         V_NRORDREGARQ := V_NRORDREGARQ + 1;
         INSERT INTO ARQMAGNETICO
           (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
         VALUES
           (LPAD(TO_CHAR(V_NRORDREGARQ),10,'0'), '|0001|0|', P_NRSEQARQMAG);

         FOR C_0007 IN (
         SELECT '|0007|'||
                I.SGESTADO||'|'||
                I.CDINSCESTA||'|' AS DSLINHA
         FROM INSCRESTAD I
         WHERE I.CDEMPRESA = P_CDEMPRESA
         ORDER BY I.SGESTADO, I.CDINSCESTA) LOOP

            V_NRORDREGARQ := V_NRORDREGARQ + 1;
            INSERT INTO ARQMAGNETICO
              (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
            VALUES
              (LPAD(TO_CHAR(V_NRORDREGARQ),10,'0'), C_0007.DSLINHA, P_NRSEQARQMAG);
         END LOOP;
		 
		 FOR C_0035 IN (
         SELECT '|0035|'||
                P.NRINSJURPROJ||'|'||
                P.NMPROJETO||'|' AS DSLINHA
         FROM PROJETOSCP P , FILIAL F
         WHERE P.CDEMPRESA = P_CDEMPRESA
		   AND P.NRSEQPROJETO = F.NRSEQPROJETO
         ORDER BY P.NRINSJURPROJ, P.NMPROJETO
                                              ) LOOP

            V_NRORDREGARQ := V_NRORDREGARQ + 1;
            INSERT INTO ARQMAGNETICO
              (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
            VALUES
              (LPAD(TO_CHAR(V_NRORDREGARQ),10,'0'), C_0035.DSLINHA, P_NRSEQARQMAG);
         END LOOP;

         V_NRORDREGARQ := V_NRORDREGARQ + 1;
         SELECT '|0990|'||
                TO_CHAR(COUNT(NRSEQARQMAG) + 1)||'|'  AS DSLINHA
         INTO V_AUXILIAR
         FROM ARQMAGNETICO
         WHERE NRSEQARQMAG = P_NRSEQARQMAG;

         INSERT INTO ARQMAGNETICO
           (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
         VALUES
           (LPAD(TO_CHAR(V_NRORDREGARQ),10,'0'), V_AUXILIAR, P_NRSEQARQMAG);

         V_NRORDREGARQ := V_NRORDREGARQ + 1;
         INSERT INTO ARQMAGNETICO
           (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
         VALUES
           (LPAD(TO_CHAR(V_NRORDREGARQ),10,'0'), '|I001|0|', P_NRSEQARQMAG);

         
         V_NRORDREGARQ := V_NRORDREGARQ + 1;
         INSERT INTO ARQMAGNETICO
           (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
         VALUES
           (LPAD(TO_CHAR(V_NRORDREGARQ),10,'0'), '|I010|G|1.00|', P_NRSEQARQMAG);

         P_NRORDREGARQ := V_NRORDREGARQ;
      END;
   END IF;
END;
/
