CREATE OR REPLACE PROCEDURE GERA_DADOS_LALUR(
  P_CDEMPRESA  IN EMPRESA.CDEMPRESA%TYPE,
  P_CDTIPODEMO IN TIPODEMO.CDTIPODEMO%TYPE,
  P_DTINILALUR IN DATE,
  P_DTFINLALUR IN DATE,
  P_NRACESSCON IN VARCHAR2)
IS
  V_NRACESSCON     VARCHAR2(20);
  V_NRSEQCHPRIREG  VARCHAR2(20);
  V_VALORCALC      FLOAT;
  V_VRADI          FLOAT;
  V_VREXC          FLOAT;
  V_VRCOMPCRED     FLOAT;
  V_VRCOMPDEBI     FLOAT;
  V_TESTE          VARCHAR2(5);
BEGIN
  FOR C_TIPODEMO IN (
     SELECT ITEMDEMOF.CDTIPODEMO, ITEMDEMOF.CDITEMSTRU, ITEMDEMOF.NMITEMSTRU,
            ITEMDEMOF.IDNATUITEM, ITEMDEMOF.IDORIGVALOR, ITEMDEMOF.IDTIPOVALOR,
            ITEMDEMOF.IDOPERACAO, ITEMDEMOF.VRVALORCALC, ITEMDEMOF.CDITEMSTRUPERC
       FROM ITEMDEMOF, TIPODEMO
      WHERE (P_CDTIPODEMO = ITEMDEMOF.CDTIPODEMO)
        AND (P_CDTIPODEMO = TIPODEMO.CDTIPODEMO)
        AND (TIPODEMO.IDTIPODEMO = 'L')
      ORDER BY ITEMDEMOF.CDITEMSTRU) LOOP
      NOVO_CODIGO('NRSEQCHPRIREG' ,V_NRSEQCHPRIREG,1);
      V_NRSEQCHPRIREG := SUBSTR(V_NRSEQCHPRIREG,16,5);

      IF C_TIPODEMO.IDORIGVALOR = 'T' THEN
         INSERT INTO TMPRELLALUR
           (NRSEQACECONC, NRSEQCHPRIREG, CDEMPRESA, DSNATAJUSTE, VRAJUSTELALUR, VRTOTALLALUR, IDORIGVRLALUR)
         VALUES
           (P_NRACESSCON, V_NRSEQCHPRIREG, P_CDEMPRESA, C_TIPODEMO.NMITEMSTRU, 0, 0, C_TIPODEMO.IDORIGVALOR);

      ELSIF C_TIPODEMO.IDORIGVALOR = 'D' THEN
         V_VRADI := 0;

         INSERT INTO TMPRELLALUR
           (NRSEQACECONC, NRSEQCHPRIREG, CDEMPRESA, DSNATAJUSTE, VRAJUSTELALUR, VRTOTALLALUR, IDORIGVRLALUR)
         VALUES
           (P_NRACESSCON, V_NRSEQCHPRIREG, P_CDEMPRESA, C_TIPODEMO.NMITEMSTRU, 0, 0,C_TIPODEMO.IDORIGVALOR);

         BEGIN
            SELECT SUM(VRADICREGAJPA)
              INTO V_VRADI
              FROM REGAJUPERBASEPA
             WHERE CDEMPRESA = P_CDEMPRESA
               AND DTREGAJPA BETWEEN P_DTINILALUR AND P_DTFINLALUR
               AND VRADICREGAJPA IS NOT NULL;
         EXCEPTION
            WHEN NO_DATA_FOUND THEN
              V_VRADI := 0;
         END;

         UPDATE ITEMDEMOF SET VRVALORCALC = V_VRADI
          WHERE CDTIPODEMO = C_TIPODEMO.CDTIPODEMO
            AND CDITEMSTRU = C_TIPODEMO.CDITEMSTRU;

         FOR C_REGAJUSA IN(
             SELECT CDEMPRESA, NVL(DSTOTAREGAJPA,DSHISTREGAJPA) AS HISTORICO, SUM(VRADICREGAJPA) AS VRADICREGAJPA
               FROM REGAJUPERBASEPA
              WHERE CDEMPRESA = P_CDEMPRESA
                AND DTREGAJPA BETWEEN P_DTINILALUR AND P_DTFINLALUR
                AND VRADICREGAJPA IS NOT NULL
              GROUP BY CDEMPRESA, NVL(DSTOTAREGAJPA,DSHISTREGAJPA))LOOP

                NOVO_CODIGO('NRSEQCHPRIREG' ,V_NRSEQCHPRIREG,1);
                V_NRSEQCHPRIREG := SUBSTR(V_NRSEQCHPRIREG,16,5);

                INSERT INTO TMPRELLALUR
                   (NRSEQACECONC, NRSEQCHPRIREG, CDEMPRESA, DSNATAJUSTE, VRAJUSTELALUR, VRTOTALLALUR, IDORIGVRLALUR)
                VALUES
                   (P_NRACESSCON, V_NRSEQCHPRIREG, P_CDEMPRESA, C_REGAJUSA.HISTORICO, C_REGAJUSA.VRADICREGAJPA, 0, C_TIPODEMO.IDORIGVALOR);

         END LOOP;

      ELSIF C_TIPODEMO.IDORIGVALOR = 'E' THEN
         V_VREXC := 0;

         INSERT INTO TMPRELLALUR
           (NRSEQACECONC, NRSEQCHPRIREG, CDEMPRESA, DSNATAJUSTE, VRAJUSTELALUR, VRTOTALLALUR, IDORIGVRLALUR)
         VALUES
           (P_NRACESSCON, V_NRSEQCHPRIREG, P_CDEMPRESA, C_TIPODEMO.NMITEMSTRU, 0, 0,C_TIPODEMO.IDORIGVALOR);

         BEGIN
            SELECT SUM(-VREXCLREGAJPA)
              INTO V_VREXC
              FROM REGAJUPERBASEPA
             WHERE CDEMPRESA = P_CDEMPRESA
               AND DTREGAJPA BETWEEN P_DTINILALUR AND P_DTFINLALUR
               AND VREXCLREGAJPA IS NOT NULL;
         EXCEPTION
            WHEN NO_DATA_FOUND THEN
              V_VREXC:= 0;
         END;

         UPDATE ITEMDEMOF SET VRVALORCALC = V_VREXC
          WHERE CDTIPODEMO = C_TIPODEMO.CDTIPODEMO
            AND CDITEMSTRU = C_TIPODEMO.CDITEMSTRU;

         FOR C_REGAJUSA IN(
             SELECT CDEMPRESA, NVL(DSTOTAREGAJPA,DSHISTREGAJPA) AS HISTORICO, SUM(-VREXCLREGAJPA) AS VREXCLREGAJPA
               FROM REGAJUPERBASEPA
              WHERE CDEMPRESA = P_CDEMPRESA
                AND DTREGAJPA BETWEEN P_DTINILALUR AND P_DTFINLALUR
                AND VREXCLREGAJPA IS NOT NULL
              GROUP BY CDEMPRESA, NVL(DSTOTAREGAJPA,DSHISTREGAJPA))LOOP

                NOVO_CODIGO('NRSEQCHPRIREG' ,V_NRSEQCHPRIREG,1);
                V_NRSEQCHPRIREG := SUBSTR(V_NRSEQCHPRIREG,16,5);

                INSERT INTO TMPRELLALUR
                  (NRSEQACECONC, NRSEQCHPRIREG, CDEMPRESA, DSNATAJUSTE, VRAJUSTELALUR, VRTOTALLALUR, IDORIGVRLALUR)
                VALUES
                  (P_NRACESSCON, V_NRSEQCHPRIREG, P_CDEMPRESA, C_REGAJUSA.HISTORICO, C_REGAJUSA.VREXCLREGAJPA, 0,C_TIPODEMO.IDORIGVALOR);
         END LOOP;

      ELSIF C_TIPODEMO.IDORIGVALOR = 'A' THEN
         V_VALORCALC := 0;

         NOVO_CODIGO('NRSEQCHPRIREG' ,V_NRSEQCHPRIREG,1);
         V_NRSEQCHPRIREG := SUBSTR(V_NRSEQCHPRIREG,16,5);

         FOR C_APURACAO IN (
            SELECT ITEMAPUCTB.CDTIPODEMO, ITEMAPUCTB.CDITEMSTRU,
                   ITEMAPUCTB.CDORIGDEMO, ITEMAPUCTB.IDTIPOORIG,
                   ITEMAPUCTB.CDSEGUAPUR
              FROM TIPODEMO, ITEMAPUCTB
             WHERE (C_TIPODEMO.CDTIPODEMO = ITEMAPUCTB.CDTIPODEMO)
               AND (C_TIPODEMO.CDITEMSTRU = ITEMAPUCTB.CDITEMSTRU)
               AND (TIPODEMO.CDTIPODEMO   = C_TIPODEMO.CDTIPODEMO )
               AND (TIPODEMO.IDTIPODEMO   = 'L')
             ORDER BY ITEMAPUCTB.IDTIPOORIG) LOOP

             IF C_APURACAO.IDTIPOORIG = 'S' THEN
                FOR C_SALDO IN(
                   SELECT SALDEMPR.CDEMPRESA,
                          SALDEMPR.CDCONTCTBL,
                          SALDEMPR.DTSALDEMPR,
                          SALDEMPR.VRSALDEMPR
                   FROM SALDEMPR
                   WHERE (SALDEMPR.CDEMPRESA  = P_CDEMPRESA)  AND
                         (SALDEMPR.CDCONTCTBL = C_APURACAO.CDORIGDEMO) AND
                         (SALDEMPR.DTSALDEMPR = LAST_DAY(P_DTFINLALUR)))LOOP

                   IF C_TIPODEMO.IDOPERACAO IS NOT NULL THEN
                      CASE C_TIPODEMO.IDOPERACAO
                         WHEN 'N' THEN V_VALORCALC := C_SALDO.VRSALDEMPR;
                         WHEN 'S' THEN V_VALORCALC := (C_SALDO.VRSALDEMPR) + V_VALORCALC;
                         WHEN 'U' THEN V_VALORCALC := (V_VALORCALC - (C_SALDO.VRSALDEMPR));
                         WHEN 'M' THEN V_VALORCALC := (C_SALDO.VRSALDEMPR) * V_VALORCALC;
                         WHEN 'D' THEN V_VALORCALC := (V_VALORCALC / (C_SALDO.VRSALDEMPR));
                      END CASE;
                   ELSE
                      V_VALORCALC := C_SALDO.VRSALDEMPR;
                   END IF;
                END LOOP;
             ELSIF C_APURACAO.IDTIPOORIG = 'V' THEN
                FOR C_CTIPODEMOAPU IN(
                   SELECT ITEMDEMOF.CDTIPODEMO, ITEMDEMOF.VRVALORCALC
                     FROM ITEMDEMOF
                    WHERE ITEMDEMOF.CDTIPODEMO = C_TIPODEMO.CDTIPODEMO
                      AND ITEMDEMOF.CDITEMSTRU = SUBSTR(C_APURACAO.CDORIGDEMO,3,4)
                    ORDER BY ITEMDEMOF.CDITEMSTRU ) LOOP

                    V_VALORCALC := V_VALORCALC + C_CTIPODEMOAPU.VRVALORCALC;
                END LOOP;
             END IF;
             UPDATE ITEMDEMOF SET VRVALORCALC = V_VALORCALC
              WHERE CDTIPODEMO = C_TIPODEMO.CDTIPODEMO
                AND CDITEMSTRU = C_TIPODEMO.CDITEMSTRU;
         END LOOP;
         INSERT INTO TMPRELLALUR
           (NRSEQACECONC, NRSEQCHPRIREG, CDEMPRESA, DSNATAJUSTE, VRAJUSTELALUR, VRTOTALLALUR, IDORIGVRLALUR)
         VALUES
           (P_NRACESSCON, V_NRSEQCHPRIREG, P_CDEMPRESA, C_TIPODEMO.NMITEMSTRU, 0, V_VALORCALC,C_TIPODEMO.IDORIGVALOR);

       ELSIF C_TIPODEMO.IDORIGVALOR = 'C' THEN
          V_VRCOMPCRED := 0;
          V_VRCOMPDEBI := 0;

          INSERT INTO TMPRELLALUR
            (NRSEQACECONC, NRSEQCHPRIREG, CDEMPRESA, DSNATAJUSTE, VRAJUSTELALUR, VRTOTALLALUR,IDORIGVRLALUR)
          VALUES
            (P_NRACESSCON, V_NRSEQCHPRIREG, P_CDEMPRESA, C_TIPODEMO.NMITEMSTRU, 0, 0,C_TIPODEMO.IDORIGVALOR);

         BEGIN
            SELECT SUM(VRCREDREGAJPB),SUM(- VRDEBIREGAJPB)
              INTO V_VRCOMPCRED, V_VRCOMPDEBI
              FROM REGAJUPERBASEPB
              WHERE CDEMPRESA = P_CDEMPRESA
                AND DTREGAJPB BETWEEN P_DTINILALUR AND P_DTFINLALUR;
         EXCEPTION
            WHEN NO_DATA_FOUND THEN
              V_VRCOMPCRED := 0;
              V_VRCOMPDEBI := 0;
         END;

         UPDATE ITEMDEMOF SET VRVALORCALC = V_VRCOMPCRED + V_VRCOMPDEBI
          WHERE CDTIPODEMO = C_TIPODEMO.CDTIPODEMO
            AND CDITEMSTRU = C_TIPODEMO.CDITEMSTRU;

          FOR C_REGAJUSB IN(
             SELECT CDEMPRESA, DTREGAJPB, CDCONTCTBL, DSHISTREGAJPB, DSTOTAREGAJPB,
                    VRCREDREGAJPB , VRDEBIREGAJPB
               FROM REGAJUPERBASEPB
              WHERE CDEMPRESA = P_CDEMPRESA
                AND DTREGAJPB BETWEEN P_DTINILALUR AND P_DTFINLALUR)LOOP

                NOVO_CODIGO('NRSEQCHPRIREG' ,V_NRSEQCHPRIREG,1);
                V_NRSEQCHPRIREG := SUBSTR(V_NRSEQCHPRIREG,16,5);

                INSERT INTO TMPRELLALUR
                   (NRSEQACECONC, NRSEQCHPRIREG, CDEMPRESA, DSNATAJUSTE, VRAJUSTELALUR, VRTOTALLALUR,IDORIGVRLALUR)
                VALUES
                   (P_NRACESSCON, V_NRSEQCHPRIREG, P_CDEMPRESA, C_REGAJUSB.DSHISTREGAJPB, C_REGAJUSB.VRCREDREGAJPB +(- C_REGAJUSB.VRDEBIREGAJPB) , 0,C_TIPODEMO.IDORIGVALOR);

          END LOOP;
       END IF;
   END LOOP;
END;
/