create or replace PROCEDURE SALD_MENSAL_DIAGERAL_412(
   P_CDEMPRESA    IN NOTAFISCAL.CDEMPRESA%TYPE,
   P_DTINICIAL    IN NOTAFISCAL.DTEMISSAO%TYPE,
   P_DTFINAL      IN NOTAFISCAL.DTEMISSAO%TYPE,
   P_NRSEQARQMAG  IN ARQMAGNETICO.NRSEQARQMAG%TYPE)
AS
   V_SEQUENCIAL   NUMBER;
   V_SALDEMPR     NUMBER;
   V_VRDEBITO     NUMBER;
   V_VRCREDITO    NUMBER;
   V_DSREGISTRO   VARCHAR2(500);
   V_TIPOCONTA    VARCHAR2(01);
BEGIN
   FOR C_SALD_MENSAL_DIAGERAL IN (SELECT DTSALDEMPR AS DTSALDEMPR,
                                         TO_CHAR(DTSALDEMPR,'MM/YYYY') AS MESANOSALDEMPR,
                                         RPAD(NVL(SALDEMPR.CDCONTCTBL,' '),28) AS CONTA,
                                         VRSALDEMPR AS VRSALDEMPR,
                                         DECODE(ABS(VRSALDEMPR),VRSALDEMPR,'C','D') AS TIPOSALD,
                                         SALDEMPR.CDCONTCTBL
                                  FROM SALDEMPR, CONTCTBL
                                  WHERE CDEMPRESA = P_CDEMPRESA                      AND
                                        DTSALDEMPR BETWEEN P_DTINICIAL AND P_DTFINAL AND
                                        SALDEMPR.CDCONTCTBL = CONTCTBL.CDCONTCTBL    AND
                                        CONTCTBL.IDTIPOCONT = 'A'
                                  ORDER BY DTSALDEMPR, SALDEMPR.CDCONTCTBL) LOOP
      /* valor do saldo anterior */
      BEGIN
         SELECT VRSALDEMPR, DECODE(ABS(VRSALDEMPR),VRSALDEMPR,'C','D')
         INTO V_SALDEMPR, V_TIPOCONTA
         FROM SALDEMPR
         WHERE CDEMPRESA  = P_CDEMPRESA AND
               DTSALDEMPR = LAST_DAY(ADD_MONTHS(C_SALD_MENSAL_DIAGERAL.DTSALDEMPR, -1)) AND
               CDCONTCTBL = C_SALD_MENSAL_DIAGERAL.CDCONTCTBL;
      EXCEPTION
         WHEN NO_DATA_FOUND THEN
            V_SALDEMPR  := NULL;
            V_TIPOCONTA := NULL;
      END;
      SELECT SUM(ROUND(LANCCONT.VRLANCAMENT,2)) VRCREDITO
      INTO V_VRCREDITO
      FROM LANCCONT
      WHERE(CDEMPRESA = P_CDEMPRESA)                        AND
           (VRLANCAMENT > 0)                                AND
           (DTLANCCONT BETWEEN to_date(('01/'||C_SALD_MENSAL_DIAGERAL.MESANOSALDEMPR)) AND C_SALD_MENSAL_DIAGERAL.DTSALDEMPR)   AND
           (CDCONTCTBL = C_SALD_MENSAL_DIAGERAL.CDCONTCTBL);
      SELECT SUM(ROUND(LANCCONT.VRLANCAMENT,2)) VRDEBITO
      INTO V_VRDEBITO
      FROM LANCCONT
      WHERE(CDEMPRESA = P_CDEMPRESA)                        AND
           (VRLANCAMENT < 0)                                AND
           (DTLANCCONT BETWEEN to_date(('01/'||C_SALD_MENSAL_DIAGERAL.MESANOSALDEMPR)) AND C_SALD_MENSAL_DIAGERAL.DTSALDEMPR)   AND
           (CDCONTCTBL = C_SALD_MENSAL_DIAGERAL.CDCONTCTBL);
      V_DSREGISTRO := TO_CHAR(C_SALD_MENSAL_DIAGERAL.DTSALDEMPR,'DDMMYYYY')||
                      RPAD(NVL(C_SALD_MENSAL_DIAGERAL.CONTA,' '),28)||
                      LTRIM(REPLACE(TO_CHAR(ABS(NVL(V_SALDEMPR,0)),'000000000000000.00'),'.',''))||
                      RPAD(NVL(V_TIPOCONTA,' '),1)||
                      LTRIM(REPLACE(TO_CHAR(ABS(NVL(V_VRDEBITO,0)),'000000000000000.00'),'.',''))||
                      LTRIM(REPLACE(TO_CHAR(ABS(NVL(V_VRCREDITO,0)),'000000000000000.00'),'.',''))||
                      LTRIM(REPLACE(TO_CHAR(ABS(NVL(C_SALD_MENSAL_DIAGERAL.VRSALDEMPR,0)),'000000000000000.00'),'.',''))||
                      C_SALD_MENSAL_DIAGERAL.TIPOSALD;
      V_SEQUENCIAL := NVL(V_SEQUENCIAL,0)+1;
      INSERT INTO ARQMAGNETICO
        (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
      VALUES
        ('15'||LPAD(RTRIM(V_SEQUENCIAL), 8, '0'), V_DSREGISTRO,  P_NRSEQARQMAG);
   END LOOP;
END;
/
-- VERSAO --
BEGIN ATUVEROBJ('SALD_MENSAL_DIAGERAL_412','5.33.xxx'); END;
/
