CREATE OR REPLACE PROCEDURE SPED_CONTABIL_SALDO (
   P_NRSEQARQMAG  IN ARQMAGNETICO.NRSEQARQMAG%TYPE,
   P_CDEMPRESA    IN EMPRESA.CDEMPRESA%TYPE,        
   P_DTINICIAL    IN DATE,
   P_DTFINAL      IN DATE,
   P_NRINIORDENA  IN INTEGER,
   P_IDSPED       IN VARCHAR2, /* PARAMETRO QUE DEFINE SE SERA GERADO SPED OU FCONT */
   P_PERIODO      IN VARCHAR2, /* Identificador de encerramento Anual ou Trimestral. */
   P_MUDPLANCTB   IN VARCHAR2,
   P_NRSEQPROJETO IN INTEGER,
   P_NRORDREGARQ  OUT INTEGER)
IS
   V_NRORDREGARQ INTEGER;
   V_AUXILIAR    VARCHAR2(200);
   V_LANCPERIODO VARCHAR2(01);
   V_VRINICIAL   FLOAT;
   V_VRDEBITO    FLOAT;
   V_VRCREDITO   FLOAT;
   V_VRFINAL     FLOAT;
   V_I155        VARCHAR2(1000);
   V_I157        VARCHAR2(1000);
   V_DATAINICIAL DATE;
   V_DATAFINAL DATE;
   V_TRIMESTRE INT;
   V_QTTRIMESTE INT;
   V_ANO NUMBER;
   V_CONTAREFE VARCHAR2(20);
   V_IDFORMAPUR VARCHAR2(03);
   V_CDCONTCTBL CONTCTBL.CDCONTCTBL%TYPE;
   V_MESUM     VARCHAR2(01);
BEGIN
   V_ANO := TO_NUMBER(TO_CHAR(P_DTFINAL, 'YYYY'));
   BEGIN
      SELECT FORMATRIB
        INTO V_IDFORMAPUR
        FROM REG0010_ECF
       WHERE CDEMPRESA = P_CDEMPRESA;
   EXCEPTION
      WHEN NO_DATA_FOUND THEN
         V_IDFORMAPUR := NULL;
   END;
   BEGIN
   SELECT MONTHS_BETWEEN(TO_DATE(P_DTFINAL), TO_DATE(P_DTINICIAL)-1)/3
     INTO V_QTTRIMESTE
     FROM DUAL;
   EXCEPTION
        WHEN NO_DATA_FOUND THEN
        V_QTTRIMESTE := 0;
   END;
   IF V_IDFORMAPUR IS NULL THEN
      BEGIN
         SELECT IDFORMAPUR
           INTO V_IDFORMAPUR
           FROM EMPRESA
          WHERE CDEMPRESA = P_CDEMPRESA;
      EXCEPTION
         WHEN NO_DATA_FOUND THEN
            V_IDFORMAPUR := NULL;
      END;
   END IF;
   IF V_IDFORMAPUR IN ('1','2') THEN
      V_IDFORMAPUR := 'R';
   ELSIF V_IDFORMAPUR IN ('3', '4', '5', '7') THEN
      V_IDFORMAPUR := 'P';
   ELSIF V_IDFORMAPUR IN ('8','9') THEN
      V_IDFORMAPUR := 'I';
   END IF;
   IF V_IDFORMAPUR IN ('LPM', 'LPT') THEN
      V_IDFORMAPUR := 'P';
   ELSIF V_IDFORMAPUR IN ('LRM', 'LRT') THEN
      V_IDFORMAPUR := 'R';
   END IF;

   IF (P_IDSPED = 'S') THEN
      BEGIN
         V_NRORDREGARQ := P_NRINIORDENA;
         V_MESUM := 'S';
         FOR C_DATA IN (
            SELECT DISTINCT SUBSTR(DTDATA,4,8) DTDATA
              FROM DATA
             WHERE DTMES BETWEEN P_DTINICIAL AND P_DTFINAL
          ORDER BY 1) LOOP
            V_LANCPERIODO := 'S';

            FOR C_CONTA IN (SELECT C.CDCONTCTBL, C.NMCONTCTBL, R.CDCTBLINTE, R.VRSALDANT
                              FROM CONTCTBL C, RELACCONTCTBL R
                             WHERE C.CDNVCONTCTBL = (SELECT MAX(CDNVCONTCTBL)
                                                       FROM CONTCTBL)
                               AND C.CDCONTCTBL = R.CDCONTCTBL(+)
                               AND R.CDEMPRESA(+) = P_CDEMPRESA
                               AND C.IDCONTCCUS <> 'R'
                             ORDER BY C.CDCONTCTBL ) LOOP

               BEGIN
                  SELECT S.VRSALDEMPR AS VRSALDINI
                    INTO V_VRINICIAL
                    FROM SALDEMPR S
                   WHERE S.CDEMPRESA  = P_CDEMPRESA
                     AND S.DTSALDEMPR = TO_DATE('01/'||C_DATA.DTDATA) -1
                     AND S.CDCONTCTBL = C_CONTA.CDCONTCTBL;
               EXCEPTION
                  WHEN NO_DATA_FOUND THEN
                     V_VRINICIAL := 0;
               END;

               BEGIN
                  SELECT SUM(ROUND(VRLANCAMENT,2)) AS DEBITO
                    INTO V_VRDEBITO
                    FROM LANCCONT
                   WHERE CDEMPRESA  = P_CDEMPRESA
                     AND CDCONTCTBL = C_CONTA.CDCONTCTBL
                     AND DTLANCCONT BETWEEN '01/'||C_DATA.DTDATA and LAST_DAY('01/'||C_DATA.DTDATA)
                     AND VRLANCAMENT < 0;
               EXCEPTION
                  WHEN NO_DATA_FOUND THEN
                     V_VRDEBITO := 0;
               END;

               BEGIN
                  SELECT SUM(ROUND(VRLANCAMENT,2)) AS CREDITO
                    INTO V_VRCREDITO
                    FROM LANCCONT
                   WHERE CDEMPRESA = P_CDEMPRESA
                     AND CDCONTCTBL = C_CONTA.CDCONTCTBL
                     AND DTLANCCONT BETWEEN '01/'||C_DATA.DTDATA and LAST_DAY('01/'||C_DATA.DTDATA)
                     AND VRLANCAMENT > 0;
               EXCEPTION
                  WHEN NO_DATA_FOUND THEN
                     V_VRCREDITO := 0;
               END;

               BEGIN
                  SELECT S.VRSALDEMPR AS VRSALDFIN
                    INTO V_VRFINAL
                    FROM SALDEMPR S
                   WHERE S.CDEMPRESA  = P_CDEMPRESA
                     AND S.CDCONTCTBL = C_CONTA.CDCONTCTBL
                     AND S.DTSALDEMPR = LAST_DAY('01/'||C_DATA.DTDATA);
               EXCEPTION
                  WHEN NO_DATA_FOUND THEN
                     V_VRFINAL := 0;
               END;


               IF (ABS(NVL(V_VRINICIAL,0)) + ABS(NVL(V_VRDEBITO,0)) + ABS(NVL(V_VRCREDITO,0))) <> 0 THEN
                  IF V_LANCPERIODO = 'S' THEN
                     V_AUXILIAR := '|I150|'||'01'||SUBSTR(C_DATA.DTDATA, 1, 2)||SUBSTR(C_DATA.DTDATA, 4, 4)||'|'||TO_CHAR(LAST_DAY('01/'||C_DATA.DTDATA),'DDMMYYYY')||'|';
                     V_NRORDREGARQ := V_NRORDREGARQ + 1;

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

                     V_LANCPERIODO := 'N';
                  END IF;
                  IF V_ANO <= 2014 THEN /* LEIANTE 3.0 PARA BAIXO */
             /*1*/   SELECT '|I155|'||
             /*2*/          C_CONTA.CDCONTCTBL||'|'||
             /*3*/               '|'||
             /*4-5*/        REPLACE(LTRIM(TO_CHAR(ABS(NVL(V_VRINICIAL,0)),'9999999999990.00')||'|'||DECODE(V_VRINICIAL, ABS(V_VRINICIAL), 'C', 'D')),'.',',') ||'|'||
             /*6*/          REPLACE(LTRIM(TO_CHAR(ABS(NVL(V_VRDEBITO,0)),'9999999999990.00')),'.',',') ||'|'||
             /*7*/          REPLACE(LTRIM(TO_CHAR(NVL(V_VRCREDITO,   0), '9999999999990.00')),'.',',') ||'|'||
             /*8-9*/        REPLACE(LTRIM(TO_CHAR(ABS(NVL(V_VRFINAL, 0)),'9999999999990.00')||'|'||DECODE(V_VRFINAL, ABS(V_VRFINAL), 'C', 'D')),'.',',') ||'|'
                    INTO V_I155
                    FROM DUAL;
                  ELSIF V_ANO >= 2015 THEN /* LEIAUTE 4.0 */
             /*1*/   SELECT '|I155|'||
             /*2*/          C_CONTA.CDCONTCTBL||'|'||
             /*3*/               '|'||
             /*4-5*/        REPLACE(LTRIM(TO_CHAR(ABS(NVL(V_VRINICIAL,0)),'9999999999990.00')||'|'||
                            DECODE(V_VRINICIAL, ABS(V_VRINICIAL), 'C', 'D')),'.',',') ||'|'||
             /*6*/          REPLACE(LTRIM(TO_CHAR(ABS(NVL(V_VRDEBITO,0)),'9999999999990.00')),'.',',') ||'|'||
             /*7*/          REPLACE(LTRIM(TO_CHAR(NVL(V_VRCREDITO,   0), '9999999999990.00')),'.',',') ||'|'||
             /*8-9*/        REPLACE(LTRIM(TO_CHAR(ABS(NVL(V_VRFINAL, 0)),'9999999999990.00')||'|'||
                            DECODE(V_VRFINAL, ABS(V_VRFINAL), 'C', 'D')),'.',',') ||'|'
                       INTO V_I155
                       FROM DUAL;
                       IF P_MUDPLANCTB = '1' AND C_CONTA.CDCTBLINTE IS NOT NULL AND V_MESUM = 'S' THEN

                            SELECT '|I157|'||
                                   RETIRA_CARACTER_ESPECIAL(C_CONTA.CDCTBLINTE,'S')||'|'||
                                   '|'||
             /*4-5*/               REPLACE(LTRIM(TO_CHAR(ABS(NVL(NVL(C_CONTA.VRSALDANT,V_VRINICIAL),0)),'9999999999990.00')||'|'||
                                   DECODE(NVL(C_CONTA.VRSALDANT,V_VRINICIAL), ABS(NVL(C_CONTA.VRSALDANT,V_VRINICIAL)), 'C', 'D')),'.',',') ||'|'
                              INTO V_I157
                              FROM DUAL;
                       END IF;
                       V_CDCONTCTBL := C_CONTA.CDCONTCTBL;

                  END IF;

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

                  IF V_I157 IS NOT NULL THEN
                     V_NRORDREGARQ := V_NRORDREGARQ + 1;
                     INSERT INTO ARQMAGNETICO
                       (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
                     VALUES
                       (LPAD(TO_CHAR(V_NRORDREGARQ),10,'0'), V_I157, P_NRSEQARQMAG);
                  END IF;
               END IF;
               V_I157  := NULL;
            END LOOP;
            V_MESUM := 'N';
         END LOOP;
         P_NRORDREGARQ := V_NRORDREGARQ;
      END;
   ELSIF (P_IDSPED = 'F') THEN
      BEGIN
         V_NRORDREGARQ := P_NRINIORDENA;

         IF P_PERIODO = 'A' THEN /* ENCERRAMENTO ANUAL */
            V_AUXILIAR := '|I150|'||TO_CHAR(P_DTINICIAL,'DDMMYYYY')||'|'||TO_CHAR(P_DTFINAL,'DDMMYYYY')||'|';
            V_NRORDREGARQ := V_NRORDREGARQ + 1;
            INSERT INTO ARQMAGNETICO
              (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
            VALUES
              (LPAD(TO_CHAR(V_NRORDREGARQ),10,'0'), V_AUXILIAR, P_NRSEQARQMAG);
            FOR C_I155_AUX IN (
               SELECT CDCONTCTBL, DECODE(V_IDFORMAPUR, 'R', CDCONTPLANREFE, 'P',CDCONTREFEPRE, CDCONTREFEIMUISEN) CDCONTPLANREFE, CDCONTREFEAUX
                 FROM CONTCTBL
                WHERE (((CDCONTPLANREFE IS NOT NULL)    AND (V_IDFORMAPUR = 'R')) OR
                       ((CDCONTREFEPRE IS NOT NULL)     AND (V_IDFORMAPUR = 'P')) OR
                       ((CDCONTREFEIMUISEN IS NOT NULL) AND (V_IDFORMAPUR = 'I')))
                  AND IDNATUCONT IN ('01', '02', '03')
                  AND IDCONTCCUS <> 'R'
                  AND IDTIPOCONT = 'A'
                ORDER BY 1, 2) LOOP

               FOR C_I155 IN(
                  SELECT '|I155|'||
                         SALDFIN.CDCONTCTBL||
                         '||'||
                         REPLACE(LTRIM(TO_CHAR(ABS(NVL(SALDINI.VRSALDINI,0)),'9999999999990.00')||'|'||DECODE(SALDINI.VRSALDINI, ABS(SALDINI.VRSALDINI), 'C', 'D')),'.',',') ||'|'||
                         REPLACE(LTRIM(TO_CHAR(ABS(NVL(DEBITO.DEBITO,0)),'9999999999990.00')),'.',',') ||'|'||
                         REPLACE(LTRIM(TO_CHAR(NVL(CREDITO.CREDITO,0), '9999999999990.00')),'.',',') ||'|'||
                         REPLACE(LTRIM(TO_CHAR(ABS(NVL(SALDFIN.VRSALDFIN,0)),'9999999999990.00')||'|'||DECODE(SALDFIN.VRSALDFIN, ABS(SALDFIN.VRSALDFIN), 'C', 'D')),'.',',') ||'|' AS DSLINHA,
                         CREDITO, DEBITO
                  FROM (SELECT L.CDCONTCTBL, SUM(ROUND(L.VRLANCAMENT,2)) AS DEBITO
                        FROM LANCCONT L , FILIAL F
                        WHERE L.VRLANCAMENT < 0
                          AND L.CDEMPRESA = P_CDEMPRESA
						  AND L.CDFILLANC = F.CDFILIAL						  
						  AND F.NRSEQPROJETO = P_NRSEQPROJETO
                          AND L.DTLANCCONT BETWEEN P_DTINICIAL AND P_DTFINAL
                          AND L.CDCONTCTBL = C_I155_AUX.CDCONTCTBL
						  GROUP BY L.CDCONTCTBL
						  UNION
						  SELECT L.CDCONTCTBL, SUM(ROUND(L.VRLANCAMENT,2)) AS DEBITO
                        FROM LANCCONT L
                        WHERE L.VRLANCAMENT < 0
                          AND L.CDEMPRESA = P_CDEMPRESA
						  AND P_NRSEQPROJETO IS NULL
                          AND L.DTLANCCONT BETWEEN P_DTINICIAL AND P_DTFINAL
                          AND L.CDCONTCTBL = C_I155_AUX.CDCONTCTBL
                        GROUP BY L.CDCONTCTBL) DEBITO,
                       (SELECT L.CDCONTCTBL, SUM(ROUND(L.VRLANCAMENT,2)) AS CREDITO
                        FROM LANCCONT L , FILIAL F
                        WHERE L.VRLANCAMENT > 0
                          AND L.CDEMPRESA = P_CDEMPRESA
						  AND L.CDFILLANC = F.CDFILIAL						  
						  AND F.NRSEQPROJETO = P_NRSEQPROJETO
                          AND L.DTLANCCONT BETWEEN P_DTINICIAL AND P_DTFINAL
                          AND L.CDCONTCTBL = C_I155_AUX.CDCONTCTBL
                        GROUP BY L.CDCONTCTBL
						UNION
						SELECT L.CDCONTCTBL, SUM(ROUND(L.VRLANCAMENT,2)) AS CREDITO
                        FROM LANCCONT L
                        WHERE L.VRLANCAMENT > 0
                          AND L.CDEMPRESA = P_CDEMPRESA
						  AND P_NRSEQPROJETO IS NULL
                          AND L.DTLANCCONT BETWEEN P_DTINICIAL AND P_DTFINAL
                          AND L.CDCONTCTBL = C_I155_AUX.CDCONTCTBL
                        GROUP BY L.CDCONTCTBL) CREDITO,
                       (SELECT S.CDCONTCTBL, NVL(S.VRSALDEMPR, 0) AS VRSALDINI
                        FROM SALDEMPR S, FILIAL F 
                        WHERE S.CDEMPRESA = P_CDEMPRESA
						  AND S.CDFILIAL = F.CDFILIAL 
						  AND F.NRSEQPROJETO = P_NRSEQPROJETO
                          AND S.DTSALDEMPR = TO_DATE(P_DTINICIAL) - 1
                          AND S.CDCONTCTBL = C_I155_AUX.CDCONTCTBL
                          AND S.VRSALDEMPR <> 0
						  UNION
						  SELECT S.CDCONTCTBL, NVL(S.VRSALDEMPR, 0) AS VRSALDINI
                        FROM SALDEMPR S 
                        WHERE S.CDEMPRESA = P_CDEMPRESA
						  AND P_NRSEQPROJETO IS NULL
                          AND S.DTSALDEMPR = TO_DATE(P_DTINICIAL) - 1
                          AND S.CDCONTCTBL = C_I155_AUX.CDCONTCTBL
                          AND S.VRSALDEMPR <> 0) SALDINI,
                       (SELECT S.CDCONTCTBL, NVL(S.VRSALDEMPR, 0) AS VRSALDFIN
                         FROM SALDEMPR S , FILIAL F 
                        WHERE S.CDEMPRESA  = P_CDEMPRESA
						  AND S.CDFILIAL = F.CDFILIAL 
						  AND F.NRSEQPROJETO = P_NRSEQPROJETO
                          AND S.DTSALDEMPR = P_DTFINAL
                          AND S.CDCONTCTBL = C_I155_AUX.CDCONTCTBL
						  UNION 
						  SELECT S.CDCONTCTBL, NVL(S.VRSALDEMPR, 0) AS VRSALDFIN
                         FROM SALDEMPR S 
                        WHERE S.CDEMPRESA  = P_CDEMPRESA
						  AND P_NRSEQPROJETO IS NULL
                          AND S.DTSALDEMPR = P_DTFINAL
                          AND S.CDCONTCTBL = C_I155_AUX.CDCONTCTBL) SALDFIN
                  WHERE SALDFIN.CDCONTCTBL = DEBITO.CDCONTCTBL(+)
                    AND SALDFIN.CDCONTCTBL = CREDITO.CDCONTCTBL(+)
                    AND SALDFIN.CDCONTCTBL = SALDINI.CDCONTCTBL(+)
                    AND (ABS(NVL(SALDINI.VRSALDINI,0)) + ABS(NVL(DEBITO.DEBITO,0)) + ABS(NVL(CREDITO.CREDITO,0))) <> 0
                  ORDER BY SALDFIN.CDCONTCTBL) LOOP
                     V_NRORDREGARQ := V_NRORDREGARQ + 1;

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

                    V_NRORDREGARQ := V_NRORDREGARQ + 1;
                    IF V_ANO <= 2014 THEN
                       V_CONTAREFE := C_I155_AUX.CDCONTREFEAUX;
                    ELSE
                       V_CONTAREFE := C_I155_AUX.CDCONTPLANREFE;
                    END IF;
                    V_AUXILIAR    := '|I156|' || V_CONTAREFE || '|' ||
                                     REPLACE(LTRIM(TO_CHAR(ABS(NVL(C_I155.DEBITO, 0)),'9999999999990.00')),'.',',')  || '|' ||
                                     REPLACE(LTRIM(TO_CHAR(ABS(NVL(C_I155.CREDITO,0)),'9999999999990.00')),'.',',')  || '|';
                    INSERT INTO ARQMAGNETICO
                       (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
                    VALUES
                       (LPAD(TO_CHAR(V_NRORDREGARQ),10,'0'), V_AUXILIAR, P_NRSEQARQMAG);
               END LOOP;
            END LOOP;
         ELSE
            /* ENCERRAMENTO TRIMESTRAL */
            V_TRIMESTRE := 0;
            V_DATAINICIAL := TO_CHAR(P_DTINICIAL, 'DD/MM/YYYY');
            V_DATAFINAL := LAST_DAY(ADD_MONTHS(V_DATAINICIAL, 2));
            WHILE V_TRIMESTRE < V_QTTRIMESTE LOOP
               V_AUXILIAR := '|I150|'|| TO_CHAR(V_DATAINICIAL, 'DDMMYYYY') ||'|'||TO_CHAR(V_DATAFINAL, 'DDMMYYYY')||'|';
               V_NRORDREGARQ := V_NRORDREGARQ + 1;
               INSERT INTO ARQMAGNETICO
                 (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
               VALUES
                 (LPAD(TO_CHAR(V_NRORDREGARQ),10,'0'), V_AUXILIAR, P_NRSEQARQMAG);
               FOR C_I155_AUX IN (
                  SELECT CDCONTCTBL, DECODE(V_IDFORMAPUR, 'R', CDCONTPLANREFE, 'P',CDCONTREFEPRE, CDCONTREFEIMUISEN) CDCONTPLANREFE, CDCONTREFEAUX
                    FROM CONTCTBL
                   WHERE (((CDCONTPLANREFE IS NOT NULL)    AND (V_IDFORMAPUR = 'R')) OR
                          ((CDCONTREFEPRE IS NOT NULL)     AND (V_IDFORMAPUR = 'P')) OR
                          ((CDCONTREFEIMUISEN IS NOT NULL) AND (V_IDFORMAPUR = 'I')))
                     AND IDNATUCONT IN ('01', '02', '03')
                     AND IDCONTCCUS <> 'R'
                     AND IDTIPOCONT = 'A'
                   ORDER BY 1, 2) LOOP

                  FOR C_I155 IN(
                     SELECT '|I155|'||
                            SALDFIN.CDCONTCTBL||
                            '||'||
                            REPLACE(LTRIM(TO_CHAR(ABS(NVL(SALDINI.VRSALDINI,0)),'9999999999990.00')||'|'||DECODE(SALDINI.VRSALDINI, ABS(SALDINI.VRSALDINI), 'C', 'D')),'.',',') ||'|'||
                            REPLACE(LTRIM(TO_CHAR(ABS(NVL(DEBITO.DEBITO,0)),'9999999999990.00')),'.',',') ||'|'||
                            REPLACE(LTRIM(TO_CHAR(NVL(CREDITO.CREDITO,0), '9999999999990.00')),'.',',') ||'|'||
                            REPLACE(LTRIM(TO_CHAR(ABS(NVL(SALDFIN.VRSALDFIN,0)),'9999999999990.00')||'|'||DECODE(SALDFIN.VRSALDFIN, ABS(SALDFIN.VRSALDFIN), 'C', 'D')),'.',',') ||'|' AS DSLINHA,
                            CREDITO, DEBITO
                     FROM (SELECT S.CDCONTCTBL, NVL(S.VRSALDEMPR, 0) AS VRSALDINI
                            FROM SALDEMPR S , FILIAL F
                           WHERE S.CDEMPRESA = P_CDEMPRESA
						     AND S.CDFILIAL = F.CDFILIAL 
							 AND F.NRSEQPROJETO = P_NRSEQPROJETO
                             AND S.DTSALDEMPR = TRUNC(V_DATAINICIAL - 1)
                             AND S.CDCONTCTBL = C_I155_AUX.CDCONTCTBL
                             AND S.VRSALDEMPR <> 0
							 UNION
							 SELECT S.CDCONTCTBL, NVL(S.VRSALDEMPR, 0) AS VRSALDINI
                            FROM SALDEMPR S 
                           WHERE S.CDEMPRESA = P_CDEMPRESA
						     AND P_NRSEQPROJETO IS NULL
                             AND S.DTSALDEMPR = TRUNC(V_DATAINICIAL - 1)
                             AND S.CDCONTCTBL = C_I155_AUX.CDCONTCTBL
                             AND S.VRSALDEMPR <> 0) SALDINI,

                         (SELECT L.CDCONTCTBL, SUM(ROUND(L.VRLANCAMENT,2)) AS DEBITO
                            FROM LANCCONT L , FILIAL F 
                           WHERE L.VRLANCAMENT < 0
						     AND L.CDFILLANC = F.CDFILIAL 
							 AND F.NRSEQPROJETO = P_NRSEQPROJETO
                             AND L.CDEMPRESA = P_CDEMPRESA
                             AND L.DTLANCCONT BETWEEN V_DATAINICIAL AND V_DATAFINAL
                             AND L.CDCONTCTBL = C_I155_AUX.CDCONTCTBL
                           GROUP BY L.CDCONTCTBL
						   UNION 
						   SELECT L.CDCONTCTBL, SUM(ROUND(L.VRLANCAMENT,2)) AS DEBITO
                            FROM LANCCONT L  
                           WHERE L.VRLANCAMENT < 0
						     AND P_NRSEQPROJETO IS NULL
                             AND L.CDEMPRESA = P_CDEMPRESA
                             AND L.DTLANCCONT BETWEEN V_DATAINICIAL AND V_DATAFINAL
                             AND L.CDCONTCTBL = C_I155_AUX.CDCONTCTBL
                           GROUP BY L.CDCONTCTBL) DEBITO,

                          (SELECT L.CDCONTCTBL, SUM(ROUND(L.VRLANCAMENT,2)) AS CREDITO
                             FROM LANCCONT L , FILIAL F 
                            WHERE L.VRLANCAMENT > 0
							  AND L.CDFILLANC = F.CDFILIAL
							  AND F.NRSEQPROJETO = P_NRSEQPROJETO
                              AND L.CDEMPRESA = P_CDEMPRESA
                              AND L.DTLANCCONT BETWEEN V_DATAINICIAL AND V_DATAFINAL
                              AND L.CDCONTCTBL = C_I155_AUX.CDCONTCTBL
                            GROUP BY L.CDCONTCTBL
							UNION
							SELECT L.CDCONTCTBL, SUM(ROUND(L.VRLANCAMENT,2)) AS CREDITO
                             FROM LANCCONT L  
                            WHERE L.VRLANCAMENT > 0
							  AND P_NRSEQPROJETO IS NULL
                              AND L.CDEMPRESA = P_CDEMPRESA
                              AND L.DTLANCCONT BETWEEN V_DATAINICIAL AND V_DATAFINAL
                              AND L.CDCONTCTBL = C_I155_AUX.CDCONTCTBL
                            GROUP BY L.CDCONTCTBL) CREDITO,

                          (SELECT S.CDCONTCTBL, NVL(S.VRSALDEMPR, 0) AS VRSALDFIN
                            FROM SALDEMPR S , FILIAL F
                           WHERE S.CDEMPRESA  = P_CDEMPRESA
						     AND S.CDFILIAL = F.CDFILIAL 
							 AND F.NRSEQPROJETO = P_NRSEQPROJETO
                             AND S.DTSALDEMPR = V_DATAFINAL
                             AND S.CDCONTCTBL = C_I155_AUX.CDCONTCTBL
							 UNION 
							 SELECT S.CDCONTCTBL, NVL(S.VRSALDEMPR, 0) AS VRSALDFIN
                            FROM SALDEMPR S 
                           WHERE S.CDEMPRESA  = P_CDEMPRESA
						     AND P_NRSEQPROJETO IS NULL
                             AND S.DTSALDEMPR = V_DATAFINAL
                             AND S.CDCONTCTBL = C_I155_AUX.CDCONTCTBL) SALDFIN

                     WHERE SALDFIN.CDCONTCTBL = DEBITO.CDCONTCTBL(+)
                       AND SALDFIN.CDCONTCTBL = CREDITO.CDCONTCTBL(+)
                       AND SALDFIN.CDCONTCTBL = SALDINI.CDCONTCTBL(+)
                       AND (ABS(NVL(SALDINI.VRSALDINI,0)) + ABS(NVL(DEBITO.DEBITO,0)) + ABS(NVL(CREDITO.CREDITO,0))) <> 0
                     ORDER BY SALDFIN.CDCONTCTBL) LOOP
                        V_NRORDREGARQ := V_NRORDREGARQ + 1;

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

                       V_NRORDREGARQ := V_NRORDREGARQ + 1;
                       IF V_ANO <= 2014 THEN
                          V_CONTAREFE := C_I155_AUX.CDCONTREFEAUX;
                       ELSE
                          V_CONTAREFE := C_I155_AUX.CDCONTPLANREFE;
                       END IF;

                       V_AUXILIAR    := '|I156|' || V_CONTAREFE || '|' ||
                                        REPLACE(LTRIM(TO_CHAR(ABS(NVL(C_I155.DEBITO, 0)),'9999999999990.00')),'.',',')  || '|' ||
                                        REPLACE(LTRIM(TO_CHAR(ABS(NVL(C_I155.CREDITO,0)),'9999999999990.00')),'.',',')  || '|';
                       INSERT INTO ARQMAGNETICO
                          (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
                       VALUES
                          (LPAD(TO_CHAR(V_NRORDREGARQ),10,'0'), V_AUXILIAR, P_NRSEQARQMAG);
                  END LOOP;
               END LOOP;
               V_DATAINICIAL := ADD_MONTHS(V_DATAINICIAL, 3);
               V_DATAFINAL := LAST_DAY(ADD_MONTHS(V_DATAFINAL, 3));
               V_TRIMESTRE := V_TRIMESTRE + 1;
            END LOOP;
         END IF;
         P_NRORDREGARQ := V_NRORDREGARQ;
      END;
   END IF;
END;
/
