/*BLOCO Q*/
create or replace PROCEDURE SPED_ECF_BLOCOQ_100(
   P_CDEMPRESA      IN EMPRESA.CDEMPRESA%TYPE,
   P_ANO            IN VARCHAR,
   P_NRSEQARQMAG    IN VARCHAR,
   P_NRORDREGARQ    IN NUMBER,
   P_NRORDENACAO   OUT NUMBER)
AS
   V_NRORDREGARQ   NUMBER;
   V_COUNT         NUMBER;
   V_CDCONTINI     VARCHAR2(30);
   V_CDCONTFIN     VARCHAR2(30);
   V_Q100          VARCHAR2(500);
   V_SALDOFIN      NUMBER;
   V_LINHA         VARCHAR2(500);
BEGIN
   V_NRORDREGARQ := P_NRORDREGARQ;
   V_SALDOFIN    := 0;
   BEGIN
      SELECT '|Q001'         ||'|'||   /* NOME DO REGISTRO */
             '0'             ||'|'     /* COM DADOS.*/
        INTO V_LINHA
        FROM DUAL;
   EXCEPTION
        WHEN NO_DATA_FOUND THEN
           V_LINHA := NULL;
   END;

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

   FOR C_Q100 IN (
      SELECT TO_CHAR(X.DTLANCCONT,'DDMMYYYY') AS DATA, X.NRLANCCONT AS NUM_DOC,
             RETIRA_CARACTER_ESPECIAL(X.DSHISTLANC, 'S') AS HIST,
             REPLACE(LTRIM(TO_CHAR(ABS(NVL(ROUND((CASE WHEN X.VRLANCAMENT < 0
		                                          THEN X.VRLANCAMENT
                                                  ELSE 0 END), 2), 0)), '9999999999999990.00')), '.', ',') AS VL_SAIDA,
             REPLACE(LTRIM(TO_CHAR(ABS(NVL(ROUND((CASE WHEN X.VRLANCAMENT >= 0
                                                  THEN X.VRLANCAMENT
                                                  ELSE 0 END), 2), 0)), '9999999999999990.00')), '.', ',') AS VL_ENTRADA,
    		     X.VRSALDEMPR,
    		     (CASE WHEN X.VRLANCAMENT < 0
              THEN X.VRLANCAMENT
       		    ELSE 0  END) VL_SAIDA1,
    		     (CASE WHEN X.VRLANCAMENT >= 0
              THEN X.VRLANCAMENT
      		    ELSE 0 END) VL_ENTRADA1
	      FROM (SELECT PRINC.*
              	FROM EMPRESA,
                     (SELECT SALDANT.VRSALDEMPR, SALDANT.DTSALDEMPR,
                      	     SALDANT.CDCONTCTBL, SALDANT.NMCONTCTBL,
                             LANCCONT.NRLANCCONT, LANCCONT.CDHISTPADR,
                       	     LANCCONT.DSHISTLANC AS DSHISTLANC,
                        		 LANCCONT.DTLANCCONT, LANCCONT.VRLANCAMENT
                  	    FROM (SELECT CONT.CDCONTCTBL, S.VRSALDEMPR,
                                     S.DTSALDEMPR, CONT.NMCONTCTBL
                       		      FROM SALDEMPR S, CONTCTBL CONT
                               WHERE S.CDEMPRESA(+)  = P_CDEMPRESA
                                 AND S.CDCONTCTBL(+) = CONT.CDCONTCTBL
                                 AND CONT.IDTIPOCONT = 'A'
                                 AND S.DTSALDEMPR(+) = TO_DATE('31/12/'||(P_ANO - 1))
                                 AND CONT.IDCONTCCUS <> 'R') SALDANT,
                             LANCCONT, CONTCTBL
                       WHERE LANCCONT.CDEMPRESA(+) = P_CDEMPRESA
                         AND LANCCONT.DTLANCCONT(+) BETWEEN TO_DATE('01/01/'||P_ANO) AND TO_DATE('31/12/'||P_ANO)
                         AND LANCCONT.CDCONTCTBL(+) = CONTCTBL.CDCONTCTBL
                         AND SALDANT.CDCONTCTBL = CONTCTBL.CDCONTCTBL
                         AND CONTCTBL.IDCONTCCUS <> 'R'
                         AND LANCCONT.IDLANCINTE(+) <> 'S'
                         AND CONTCTBL.CDCONTCTBL IN (SELECT DISTINCT C.CDCONTCTBL
                                                       FROM PARAMLIVROCX P, CONTCTBL C
                                                      WHERE P.CDEMPRESA = '01'
                                                        AND C.CDCONTCTBL LIKE P.CDCONTCTBL||'%'
                                                        AND C.IDTIPOCONT = 'A')) PRINC
               WHERE EMPRESA.CDEMPRESA = P_CDEMPRESA
                 AND (PRINC.VRLANCAMENT <> 0 OR PRINC.VRSALDEMPR <> 0)) X
       ORDER BY X.DTLANCCONT,X.CDCONTCTBL,X.NRLANCCONT) LOOP

      IF V_Q100 IS NULL THEN
         V_Q100 := '|Q100|0101'||P_ANO||'|'||'|SALDO ANTERIOR|'||'0,00|'||'0,00|'||REPLACE(LTRIM(TO_CHAR(NVL(ROUND(C_Q100.VRSALDEMPR, 2), 0), '9999999999999990.00')), '.', ',')||'|';
         V_NRORDREGARQ := V_NRORDREGARQ + 1;
         INSERT INTO ARQMAGNETICO
            (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
         VALUES
            (LPAD(TO_CHAR(V_NRORDREGARQ), 10, '0'), V_Q100, P_NRSEQARQMAG);
      END IF;

      IF V_SALDOFIN = 0 THEN
         V_SALDOFIN := C_Q100.VRSALDEMPR;
      END IF;

      V_SALDOFIN    := V_SALDOFIN + C_Q100.VL_ENTRADA1 + C_Q100.VL_SAIDA1;
      V_LINHA       := '|Q100|'||C_Q100.DATA||'|'||C_Q100.NUM_DOC||'|'||C_Q100.HIST||'|'||C_Q100.VL_ENTRADA||'|'||C_Q100.VL_SAIDA||'|'||REPLACE(LTRIM(TO_CHAR(NVL(ROUND(V_SALDOFIN, 2), 0), '9999999999999990.00')), '.', ',')||'|';
      V_NRORDREGARQ := V_NRORDREGARQ + 1;
      INSERT INTO ARQMAGNETICO
         (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
      VALUES
         (LPAD(TO_CHAR(V_NRORDREGARQ), 10, '0'), V_LINHA, P_NRSEQARQMAG);
   END LOOP;

   BEGIN
      SELECT '|Q990'               ||'|'||  /* NOME DO REGISTRO */
             TO_CHAR(COUNT(*) + 1) ||'|'    /* QUANTIDADE DE REGISTROS DO BLOCO J */
        INTO V_LINHA
        FROM ARQMAGNETICO
       WHERE NRSEQARQMAG = P_NRSEQARQMAG
         AND SUBSTR(DSREGARQMAG, 2, 1) = 'Q';
   EXCEPTION
        WHEN NO_DATA_FOUND THEN
           V_LINHA := NULL;
   END;

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

END;
/