CREATE OR REPLACE PROCEDURE SPED_CONTABIL_BALANCETE (
   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_PERIODO      IN VARCHAR2,
   P_NRORDREGARQ  OUT INTEGER)
IS
   V_NRORDREGARQ INTEGER;
   V_AUXILIAR    VARCHAR2(200);
   V_DATAFINAL DATE;
   V_TRIMESTRE INT;
   V_QTTRIMESTE INT;
   V_PRITRIMESTRE DATE;
   V_SEGTRIMESTRE DATE;
   V_TERTRIMESTRE DATE;
   V_ULTTRIMESTRE DATE;

BEGIN
   V_NRORDREGARQ := P_NRINIORDENA;
   BEGIN
   SELECT CEIL(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 P_PERIODO = 'A' THEN
      V_AUXILIAR    := '|I350|' || TO_CHAR(LAST_DAY(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 I355 IN
         (SELECT '|I355|' || S.CDCONTCTBL || '|' || '' || '|' ||
                  REPLACE(LTRIM(TO_CHAR(ABS(NVL(NVL(ST.VRSALDOTRANSF, S.VRSALDEMPR), 0)),'9999999999990.00')), '.', ',')|| '|' ||
                  DECODE(NVL(NVL(ST.VRSALDOTRANSF, S.VRSALDEMPR), 0), ABS(NVL(NVL(ST.VRSALDOTRANSF, S.VRSALDEMPR), 0)), 'C', 'D') || '|' AS DSLINHA
            FROM SALDOTRANSF ST, SALDEMPR S, CONTCTBL C
           WHERE S.CDEMPRESA     = P_CDEMPRESA
             AND S.DTSALDEMPR = P_DTFINAL
             AND S.CDCONTCTBL  = C.CDCONTCTBL
             AND C.IDTIPOCONT  = 'A'
             AND C.IDNATUCONT  = '04'
             AND C.IDCONTCCUS  <> 'R'
             AND S.DTSALDEMPR = ST.DTSALDOTRANSF(+)
             AND S.CDEMPRESA  = ST.CDEMPRESA(+)
             AND S.CDCONTCTBL = ST.CDCONTCTBL(+)
           ORDER BY S.CDCONTCTBL ASC, S.DTSALDEMPR, ST.CDCONTCTBL ASC, ST.DTSALDOTRANSF)LOOP

         V_NRORDREGARQ := V_NRORDREGARQ + 1;
         INSERT INTO ARQMAGNETICO
            (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
         VALUES
            (LPAD(TO_CHAR(V_NRORDREGARQ),10,'0'), I355.DSLINHA, P_NRSEQARQMAG);
      END LOOP;
   ELSE
     /* ENCERRAMENTO TRIMESTRAL */
	    V_PRITRIMESTRE := '31/03'||TO_CHAR(P_DTINICIAL,'YYYY');
        V_SEGTRIMESTRE := '30/06'||TO_CHAR(P_DTINICIAL,'YYYY');
        V_TERTRIMESTRE := '30/09'||TO_CHAR(P_DTINICIAL,'YYYY');
        V_ULTTRIMESTRE := '31/12'||TO_CHAR(P_DTINICIAL,'YYYY');
        V_TRIMESTRE := 0;
        V_DATAFINAL := P_DTINICIAL;
		IF V_DATAFINAL <= V_PRITRIMESTRE THEN 
               V_DATAFINAL := V_PRITRIMESTRE;
            ELSIF V_DATAFINAL <= V_SEGTRIMESTRE THEN
               V_DATAFINAL := V_SEGTRIMESTRE;
            ELSIF V_DATAFINAL <= V_TERTRIMESTRE THEN
               V_DATAFINAL := V_TERTRIMESTRE;
            ELSE 
               V_DATAFINAL := V_ULTTRIMESTRE;
            END IF;
        --V_DATAFINAL := LAST_DAY(ADD_MONTHS(V_DATAFINAL, 2));
	  
    --  RAISE_APPLICATION_ERROR(-20501,V_QTTRIMESTE,TRUE);
      WHILE V_TRIMESTRE < V_QTTRIMESTE LOOP
         V_AUXILIAR    := '|I350|' || 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 I355 IN
            (SELECT '|I355|' || S.CDCONTCTBL || '|' || '' || '|' ||
                     REPLACE(LTRIM(TO_CHAR(ABS(NVL(NVL(ST.VRSALDOTRANSF, S.VRSALDEMPR), 0)),'9999999999990.00')), '.', ',')|| '|' ||
                     DECODE(NVL(NVL(ST.VRSALDOTRANSF, S.VRSALDEMPR), 0), ABS(NVL(NVL(ST.VRSALDOTRANSF, S.VRSALDEMPR), 0)), 'C', 'D') || '|' AS DSLINHA
               FROM SALDOTRANSF ST, SALDEMPR S, CONTCTBL C
              WHERE S.CDEMPRESA     = P_CDEMPRESA
                AND S.DTSALDEMPR = V_DATAFINAL
                AND S.CDCONTCTBL  = C.CDCONTCTBL
                AND C.IDTIPOCONT  = 'A'
                AND C.IDNATUCONT  = '04'
	             AND C.IDCONTCCUS  <> 'R'
                AND S.DTSALDEMPR = ST.DTSALDOTRANSF(+)
                AND S.CDEMPRESA  = ST.CDEMPRESA(+)
                AND S.CDCONTCTBL = ST.CDCONTCTBL(+)
              ORDER BY S.CDCONTCTBL ASC, ST.DTSALDOTRANSF, S.DTSALDEMPR)LOOP

            V_NRORDREGARQ := V_NRORDREGARQ + 1;
            INSERT INTO ARQMAGNETICO
               (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
            VALUES
               (LPAD(TO_CHAR(V_NRORDREGARQ),10,'0'), I355.DSLINHA, P_NRSEQARQMAG);
         END LOOP;
         V_DATAFINAL := LAST_DAY(ADD_MONTHS(V_DATAFINAL, 3));
         V_TRIMESTRE := V_TRIMESTRE + 1;
      END LOOP;
   END IF;
   P_NRORDREGARQ := V_NRORDREGARQ;
END;
/