CREATE OR REPLACE PROCEDURE SALD_MENSAL_DIAAUX_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_SEQUNCIAL    NUMBER;
   V_LINHA        VARCHAR2(4000);
   V_SALCOME      NUMBER;
   V_VRDEBITO     NUMBER;
   V_VRCREDITO    NUMBER;
   V_TIPOCONTA    VARCHAR2(01);
   V_INIMES       NUMBER;
   V_TIPOCONTAINI VARCHAR2(01);
   V_FINMES       NUMBER;
   V_PRIMEIRO     VARCHAR2(01);
BEGIN
   FOR C_SALD_MENSAL_DIAAUX IN (SELECT DISTINCT CDSALDCOME AS CODCOME
                                FROM SALDCOME, CONTCTBL
                                WHERE CDEMPRESA = P_CDEMPRESA
                                  AND DTSALDCOME BETWEEN  P_DTINICIAL AND P_DTFINAL
                                  AND SALDCOME.CDCONTCTBL = CONTCTBL.CDCONTCTBL
                                  AND CONTCTBL.IDTIPOCONT = 'A') LOOP
      V_PRIMEIRO := 'S';
      V_FINMES := 0;

      FOR C_DATA IN (
      SELECT DISTINCT DTMES AS DTINICIAL
        FROM DATA
       WHERE DTDATA BETWEEN P_DTINICIAL AND P_DTFINAL
       ORDER BY 1) LOOP

         IF V_PRIMEIRO = 'S' THEN
            /* VALOR DO SALDO ANTERIOR */
            BEGIN
                 SELECT NVL(SUM(VRSALDCOME),0)
                 INTO V_INIMES
                 FROM SALDCOME
                 WHERE CDEMPRESA  = P_CDEMPRESA
                 AND   CDSALDCOME = C_SALD_MENSAL_DIAAUX.CODCOME
                 AND   DTSALDCOME = (SELECT MAX(DTSALDCOME) FROM SALDCOME
                                               WHERE CDSALDCOME = C_SALD_MENSAL_DIAAUX.CODCOME
                                    AND DTSALDCOME <  C_DATA.DTINICIAL
                                    AND CDEMPRESA = P_CDEMPRESA)
              GROUP BY CDSALDCOME;
            EXCEPTION
               WHEN NO_DATA_FOUND THEN
                  V_INIMES  := 0;
            END;
            V_PRIMEIRO := 'N';
            V_FINMES := V_INIMES;
         END IF;
         BEGIN
         SELECT NVL(SUM(ROUND(LANCCONT.VRLANCAMENT,2)),0) VRCREDITO
         INTO V_VRCREDITO
         FROM LANCCONT
         WHERE(CDEMPRESA = P_CDEMPRESA)                        AND
              (VRLANCAMENT > 0)                                AND
              (DTLANCCONT BETWEEN C_DATA.DTINICIAL AND LAST_DAY(C_DATA.DTINICIAL))   AND
              (CDCONTCOME = C_SALD_MENSAL_DIAAUX.CODCOME);
         EXCEPTION
            WHEN NO_DATA_FOUND THEN
              V_VRCREDITO := 0;
         END;
         BEGIN
         SELECT NVL(SUM(ROUND(LANCCONT.VRLANCAMENT,2)),0) VRDEBITO
         INTO V_VRDEBITO
         FROM LANCCONT
         WHERE(CDEMPRESA = P_CDEMPRESA)                        AND
              (VRLANCAMENT < 0)                                AND
              (DTLANCCONT BETWEEN C_DATA.DTINICIAL AND LAST_DAY(C_DATA.DTINICIAL))  AND
              (CDCONTCOME = C_SALD_MENSAL_DIAAUX.CODCOME);
         EXCEPTION
            WHEN NO_DATA_FOUND THEN
              V_VRDEBITO := 0;
         END;
         V_INIMES := V_FINMES;
         IF V_INIMES >= 0 THEN
            V_TIPOCONTAINI := 'C';
         END IF;
         IF V_INIMES < 0  THEN
            V_TIPOCONTAINI := 'D';
         END IF;
         V_FINMES := V_FINMES + ABS(V_VRCREDITO) - ABS(V_VRDEBITO);
         IF V_FINMES >= 0 THEN
            V_TIPOCONTA := 'C';
         END IF;
         IF V_FINMES < 0 THEN
            V_TIPOCONTA := 'D';
         END IF;
         DBMS_OUTPUT.PUT_LINE(TO_CHAR(V_INIMES) ||' + '||  TO_CHAR(ABS(V_VRCREDITO)) ||' - '|| TO_CHAR(ABS(V_VRDEBITO)) ||' + '|| TO_CHAR(V_FINMES));
         V_LINHA := TO_CHAR(LAST_DAY(C_DATA.DTINICIAL),'DDMMYYYY')||
                    RPAD(NVL(SUBSTR(REPLACE(REPLACE(REPLACE(C_SALD_MENSAL_DIAAUX.CODCOME,'/',''),'-',''),'.',''),2,28),' '),28)||
                    LTRIM(REPLACE(TO_CHAR(ABS(NVL(V_INIMES,0)),'000000000000000.00'),'.',''))||
                    V_TIPOCONTAINI||
                    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(V_FINMES ,0)),'000000000000000.00'),'.',''))||
                    V_TIPOCONTA;
         V_SEQUNCIAL := NVL(V_SEQUNCIAL,0)+1;
         INSERT INTO ARQMAGNETICO
           (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
         VALUES
           ('16'||LPAD(RTRIM(V_SEQUNCIAL), 8, '0'), V_LINHA,  P_NRSEQARQMAG);
      END LOOP;
   END LOOP;
END;
/
-- VERSAO --
BEGIN ATUVEROBJ('SALD_MENSAL_DIAAUX_412','5.33.xxx'); END;
/
