CREATE OR REPLACE PROCEDURE APURAC_GESROP(
   P_NRLISTA        IN VARCHAR,
   P_CDOPERLOGADO   IN VARCHAR DEFAULT NULL)
IS
  V_CDFILIAL        VARCHAR2(04);
  V_DTINICIAL       DATE;
  V_DTFINAL         DATE;

  V_CDGRUPPROD      VARCHAR2(10);
  V_FILIALPAR       VARCHAR2(4);
  V_IDUTPADRROPFIL  VARCHAR2(1);
  V_IDUTPADRROP     VARCHAR2(1);
  V_CDFILISUBGR     VARCHAR2(04);
  V_CDFILCENTPME    VARCHAR2(4);
  V_IDFILIALAGRROP  CHAR;

  V_NRCOUNT         FLOAT;
  V_NRCONS          FLOAT;
  V_NRMESCONSROP    NUMBER;
  V_IDPERCONSROP    CHAR;
  V_DTINICIO        DATE;
  V_DTFIM           DATE;
  V_IDFECHDIAROP    CHAR;

  V_DTINIC          DATE;
  V_FILIAPURA       VARCHAR2(4);
  V_NRDIAS          FLOAT;
  V_DTPROCESSA      DATE;
  V_IDLANCDTMENFIL  VARCHAR2(2);
  V_DTFILIALROP     DATE;

  V_NRFILIALROP     NUMBER;
  V_NUMEXEC         NUMBER;
  V_PER_DTINICIAL    DATE;
  V_PER_DTFINAL      DATE;
  V_PER_CDFILCENTPME VARCHAR2(4);
  V_PER_IDTPAPURACAO VARCHAR2(1);
  V_ORDENACAO        VARCHAR2(10);

  V_NMOPERADOR       VARCHAR2(100);
  V_NRLISTA          VARCHAR2(20);
  V_DATAAPURACAOROP  NUMBER;
  V_AUXSTR           VARCHAR2(01);

  PROCEDURE REMOVE_ROP(
    P_CDFILIAL    FILIAL.CDFILIAL%TYPE,
    P_DTINICIAL   IN DATE,
    P_DTFINAL     IN DATE)
  AS
  BEGIN
     /* Remove dados  */
     FOR C_DELDESP IN (SELECT DISTINCT F.NRFILIALROP
                         FROM FILIALROP F, DESPESASROP D
                        WHERE F.CDFILIAL = P_CDFILIAL
                          AND D.NRFILIALROP = F.NRFILIALROP
                          AND D.DTDESPESA >= P_DTINICIAL) LOOP
        DELETE DESPESASROP
         WHERE NRFILIALROP = C_DELDESP.NRFILIALROP;
     END LOOP;

     FOR C_ROPFILI IN (SELECT NRFILIALROP
                         FROM FILIALROP
                        WHERE CDFILIAL = P_CDFILIAL
                          AND DTFILIALROP >= P_DTINICIAL) LOOP
        DELETE FILIALROP
         WHERE CDFILIAL    = P_CDFILIAL
           AND NRFILIALROP = C_ROPFILI.NRFILIALROP;

        DELETE FATURAMENTOROP
         WHERE NRFILIALROP = C_ROPFILI.NRFILIALROP
           AND IDTPLANCROP NOT IN ('FAT_EFETIVO');
        DELETE CONSUMOROP
         WHERE NRFILIALROP = C_ROPFILI.NRFILIALROP;

        DELETE DEDUCAOROP
         WHERE NRFILIALROP = C_ROPFILI.NRFILIALROP;

        DELETE INVENTARIOROP
         WHERE NRFILIALROP = C_ROPFILI.NRFILIALROP;

        DELETE INVESTIMENTOROP
         WHERE NRFILIALROP = C_ROPFILI.NRFILIALROP;

        DELETE RETIRADADRO
         WHERE NRFILIALROP = C_ROPFILI.NRFILIALROP;
     END LOOP;

     FOR C_INVENTARIO IN (SELECT NRFILIALROP
                            FROM INVENTARIOROP
                           WHERE CDFILIAL = P_CDFILIAL
                             AND IDTPLANCROP IN ('AJUSTE', 'IMPLANTACAO')
                             AND DTINVENTARIO >= P_DTINICIAL) LOOP

        DELETE INVENTARIOROP
         WHERE NRFILIALROP = C_INVENTARIO.NRFILIALROP
           AND IDTPLANCROP IN ('AJUSTE', 'IMPLANTACAO');
     END LOOP;

     FOR C_FATURAMENTO IN (SELECT F.NRFATURAMENTOROP
                             FROM FATURAMENTOROP F, PARFILIALROP P
                            WHERE F.CDFILIAL = P_CDFILIAL
                              AND F.IDTPLANCROP IN ('FAT_EFETIVO')
                              AND (((P.IDDTAPURFAT IN ('S','E')) AND (F.DTENTRSAID >= P_DTINICIAL)) OR
                                   (('R' = P.IDDTAPURFAT) AND (F.DTREFERENC >= P_DTINICIAL)))
                              AND P.CDFILIAL = TIPOPARAM_DRO(F.CDFILIAL)) LOOP

        DELETE FATURAMENTOROP
         WHERE NRFATURAMENTOROP = C_FATURAMENTO.NRFATURAMENTOROP;
     END LOOP;

     DELETE FROM DEDUCAOROP D
           WHERE D.IDTPLANCROP = 'FAT_EFETIVO'
             AND D.CDFILIAL = P_CDFILIAL
             AND D.DTREFERENC >= P_DTINICIAL
             AND NOT EXISTS (SELECT 1
                               FROM FATURAMENTOROP F
                              WHERE F.IDTPLANCROP = D.IDTPLANCROP
                                AND F.CDFILIAL = D.CDFILIAL
                                AND F.DTREFERENC = D.DTREFERENC
                                AND F.NRFILIALROP = D.NRFILIALROP);
  END;
BEGIN
   V_PER_CDFILCENTPME := '';
   V_ORDENACAO := 'BINARY';

   EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_SORT = '||V_ORDENACAO;

   IF P_CDOPERLOGADO IS NOT NULL THEN
      BEGIN
         SELECT NMOPERADOR
           INTO V_NMOPERADOR
           FROM OPERADOR
          WHERE CDOPERADOR = P_CDOPERLOGADO;
      EXCEPTION
         WHEN NO_DATA_FOUND THEN
            V_NMOPERADOR := ' ';
      END;
   END IF;

   IF P_NRLISTA IS NULL THEN
      NOVO_CODIGO('LISTA', V_NRLISTA);

      V_NRLISTA := SUBSTR(V_NRLISTA,10,10);

      INSERT INTO LISTA (NRLISTA, CDLISTA)
           SELECT V_NRLISTA, CDFILIAL
             FROM PARFILIALROP
            WHERE CDFILIAL <> 'P'
            GROUP BY CDFILIAL
            UNION
           SELECT V_NRLISTA, CDFILIAL
             FROM FILIAL
            WHERE EXISTS (SELECT 1
                            FROM PARAMGERAL P
                           WHERE P.IDUTPADRROP = 'S');

      FOR C_LISTAFIL IN (SELECT L.CDLISTA, D.CDFILIAL IDALTERAR, D.DTFINAPURACROP, TRUNC(DECODE(P.IDUTILDTPROC, 'S', NVL(PF.DTPROCESSA, SYSDATE), SYSDATE)) DTPROC
                           FROM LISTA L INNER JOIN PARAMFILIAL PF ON PF.CDFILIAL = L.CDLISTA
                                        INNER JOIN FILIAL F ON F.CDFILIAL = L.CDLISTA
                                        CROSS JOIN PARAMGERAL P
                                        LEFT  JOIN DATAAPURACAOROP D ON D.CDFILIAL = L.CDLISTA
                          WHERE L.NRLISTA = V_NRLISTA
                            AND NVL(F.IDCNTRRESC,'N') = 'N') LOOP

         IF C_LISTAFIL.IDALTERAR IS NULL THEN
            NOVO_CODIGO('DATAAPURACAOROP', V_DATAAPURACAOROP);

            INSERT INTO DATAAPURACAOROP (NRDATAAPURACAOROP, CDFILIAL, DTINIAPURACROP, DTFINAPURACROP)
                 SELECT V_DATAAPURACAOROP, C_LISTAFIL.CDLISTA, C_LISTAFIL.DTPROC - 31, C_LISTAFIL.DTPROC - 1
                   FROM DUAL;
         ELSE
            UPDATE DATAAPURACAOROP
               SET DTINIAPURACROP = NVL(C_LISTAFIL.DTFINAPURACROP, FIRST_DAY(C_LISTAFIL.DTPROC))-46,
                   DTFINAPURACROP = C_LISTAFIL.DTPROC - 1
             WHERE CDFILIAL = C_LISTAFIL.CDLISTA;
         END IF;
      END LOOP;
      COMMIT;
   ELSE
      V_NRLISTA := P_NRLISTA;
   END IF;

   FOR C_FILIAL IN (SELECT DISTINCT CDFILIAL, CDLISTA, IDFILIALAGRROP, CDFILCENTPME, NVL(IDTPAPURACAO, '3') AS IDTPAPURACAO,
                           NVL(IDUTPADRROPFIL, 'N') AS IDUTPADRROPFIL, NMFILIAL, NRORG
                     FROM (SELECT F.CDFILIAL, CDLISTA, IDFILIALAGRROP, CDFILCENTPME, IDTPAPURACAO, IDUTPADRROPFIL, F.NMFILIAL, F.NRORG
                             FROM LISTA L, FILIAL F, PARFILIALROP P, PARAMGERAL G
                            WHERE NRLISTA = V_NRLISTA
                              AND CDLISTA = P.CDFILIAL
                              AND NVL(IDUTPADRROP,'N') <> 'S'
                              AND ((CDLISTA = F.CDFILIAL)
                                OR ((CDFILCENTPME = CDLISTA)
                               AND (IDFILIALAGRROP = 'S')))

                            UNION ALL

                           SELECT F.CDFILIAL, CDLISTA, IDFILIALAGRROP, CDFILCENTPME, IDTPAPURACAO, IDUTPADRROPFIL, F.NMFILIAL, F.NRORG
                             FROM LISTA L, FILIAL F, PARFILIALROP P, PARAMGERAL G
                            WHERE NRLISTA = V_NRLISTA
                              AND P.CDFILIAL = 'P'
                              AND NVL(IDUTPADRROP,'N') = 'S'
                              AND ((CDLISTA = F.CDFILIAL)
                                 OR ((CDFILCENTPME = CDLISTA)
                                AND (IDFILIALAGRROP = 'S'))))
                            ORDER BY CDFILCENTPME,CDLISTA, CDFILIAL) LOOP

      V_PER_IDTPAPURACAO := C_FILIAL.IDTPAPURACAO;
      V_CDFILIAL := C_FILIAL.CDFILIAL;

      IF (C_FILIAL.CDFILIAL <> 'P') THEN
         IF (C_FILIAL.IDUTPADRROPFIL = 'S') THEN
		      BEGIN
			      SELECT NVL(IDTPAPURACAO, '3')
			        INTO V_PER_IDTPAPURACAO
      			  FROM PARFILIALROP P
    				 WHERE P.CDFILIAL = 'P';
     			END;
         END IF;
      END IF;

      IF (C_FILIAL.CDFILCENTPME IS NOT NULL)
        AND (C_FILIAL.IDFILIALAGRROP = 'S')
        AND (V_PER_CDFILCENTPME <> C_FILIAL.CDFILCENTPME)  THEN

         V_PER_CDFILCENTPME := C_FILIAL.CDFILCENTPME;
         BEGIN
            SELECT TRUNC(NVL(DTINIAPURACROP, DECODE(IDUTILDTPROC, 'S',DTPROCESSA, SYSDATE))),
                   TRUNC(NVL(DTFINAPURACROP, DECODE(IDUTILDTPROC, 'S',DTPROCESSA, SYSDATE)))
              INTO V_PER_DTINICIAL, V_PER_DTFINAL
              FROM DATAAPURACAOROP A, PARAMFILIAL P, PARAMGERAL
             WHERE P.CDFILIAL  = C_FILIAL.CDFILCENTPME
               AND P.CDFILIAL  = A.CDFILIAL(+);
         EXCEPTION
            WHEN NO_DATA_FOUND THEN BEGIN
               V_PER_DTINICIAL := TRUNC(SYSDATE);
               V_PER_DTFINAL   := TRUNC(SYSDATE);
            END;
         END;
      ELSE
         BEGIN
            SELECT TRUNC(NVL(DTINIAPURACROP, DECODE(IDUTILDTPROC, 'S',DTPROCESSA, SYSDATE))),
                   TRUNC(NVL(DTFINAPURACROP, DECODE(IDUTILDTPROC, 'S',DTPROCESSA, SYSDATE)))
              INTO V_PER_DTINICIAL, V_PER_DTFINAL
              FROM DATAAPURACAOROP A, PARAMFILIAL P, PARAMGERAL
             WHERE P.CDFILIAL  = C_FILIAL.CDLISTA
               AND P.CDFILIAL  = A.CDFILIAL(+);
         EXCEPTION
            WHEN NO_DATA_FOUND THEN BEGIN
               V_PER_DTINICIAL := TRUNC(SYSDATE);
               V_PER_DTFINAL   := TRUNC(SYSDATE);
            END;
         END;
      END IF;

      V_DTINICIAL := V_PER_DTINICIAL;
      V_DTFINAL := V_PER_DTFINAL;

      UPDATE DATAAPURACAOROP
         SET DTINIAPURACROP = V_PER_DTFINAL  + 1,
             DTFINAPURACROP = V_PER_DTFINAL  + 1
       WHERE CDFILIAL = C_FILIAL.CDFILIAL;

      IF (NVL(V_PER_IDTPAPURACAO,'0') <> '0') THEN
         V_NRDIAS := 0;

         BEGIN
            SELECT G.IDUTPADRROP
              INTO V_IDUTPADRROP
              FROM PARAMGERAL G;
         EXCEPTION
           WHEN NO_DATA_FOUND THEN BEGIN
             V_IDUTPADRROP := NULL;
           END;
         END;
         BEGIN
            SELECT P.IDUTPADRROPFIL, F.CDFILCENTPME
              INTO V_IDUTPADRROPFIL, V_CDFILCENTPME
              FROM FILIAL F LEFT JOIN PARFILIALROP P ON F.CDFILIAL = P.CDFILIAL
             WHERE F.CDFILIAL = V_CDFILIAL;
         EXCEPTION
           WHEN NO_DATA_FOUND THEN BEGIN
             V_IDUTPADRROPFIL := NULL;
             V_CDFILCENTPME   := NULL;
           END;
         END;
         IF (V_IDUTPADRROP = 'S') OR (V_IDUTPADRROPFIL = 'S') THEN
            V_FILIALPAR := 'P';
         ELSE
            IF V_CDFILCENTPME IS NULL THEN
               V_FILIALPAR := V_CDFILIAL;
            ELSE
               BEGIN
                  SELECT IDFILIALAGRROP
                    INTO V_IDFILIALAGRROP
                    FROM PARFILIALROP
                   WHERE CDFILIAL = V_CDFILCENTPME;
               EXCEPTION
                 WHEN NO_DATA_FOUND THEN
                   V_IDFILIALAGRROP := 'N';
               END;
               IF V_IDFILIALAGRROP = 'S' THEN
                  V_FILIALPAR := V_CDFILCENTPME;
               ELSE
                  V_FILIALPAR := V_CDFILIAL;
               END IF;
            END IF;
         END IF;

         BEGIN
            SELECT P.CDGRUPPROD, NVL(P.NRMESCONSROP,1), NVL(P.IDPERCONSROP, 'N'), P.IDFECHDIAROP
              INTO V_CDGRUPPROD, V_NRMESCONSROP, V_IDPERCONSROP, V_IDFECHDIAROP
              FROM PARFILIALROP P
             WHERE P.CDFILIAL = V_FILIALPAR;
         EXCEPTION
           WHEN NO_DATA_FOUND THEN BEGIN
             V_CDGRUPPROD      := NULL;
             V_NRMESCONSROP    := 1;
             V_IDPERCONSROP    := 'N';
             V_IDFECHDIAROP    := 'N';
           END;
         END;

         BEGIN
            SELECT CASE WHEN P.IDUTILDTPROC = 'S'
                        THEN NVL(TRUNC(PF.DTPROCESSA), TRUNC(SYSDATE))
                        ELSE TRUNC(SYSDATE)
                   END
              INTO V_DTPROCESSA
              FROM PARAMFILIAL PF, PARAMGERAL P
             WHERE PF.CDFILIAL = V_CDFILIAL;
         EXCEPTION
           WHEN NO_DATA_FOUND THEN
             V_DTPROCESSA    := TRUNC(SYSDATE);
         END;
         SELECT NVL(MAX(X.CDFILIAL), MAX(X.CDFILIPADRAO)) CDFILISUBGR
           INTO V_CDFILISUBGR
           FROM (SELECT DISTINCT CDFILISUBGR AS CDFILIAL, '' AS CDFILIPADRAO
                   FROM PRSUBGRPROD
                  WHERE CDGRUPPROD  = V_CDGRUPPROD
                    AND CDFILISUBGR = V_CDFILIAL
                  UNION ALL
                 SELECT DISTINCT '' AS CDFILIAL, CDFILISUBGR AS CDFILIPADRAO
                   FROM PRSUBGRPROD
                  WHERE CDGRUPPROD  = V_CDGRUPPROD
                    AND CDFILISUBGR = 'P') X;

         V_FILIAPURA := V_FILIALPAR;
         IF V_FILIAPURA = 'P' THEN
            V_FILIAPURA := V_CDFILIAL;
         END IF; 

         BEGIN 
            SELECT DTINIAPURACROP
              INTO V_DTINIC
              FROM DATAAPURACAOROP
             WHERE CDFILIAL = V_FILIAPURA;
         EXCEPTION
           WHEN NO_DATA_FOUND THEN
             V_DTINIC := V_DTINICIAL;
         END;
         IF NOT(V_DTINIC < V_DTINICIAL AND V_DTINIC IS NOT NULL) THEN
            V_DTINIC := V_DTINICIAL;
         END IF;
         BEGIN
            SELECT NVL(IDLANCDTMENFIL,'N')
              INTO V_IDLANCDTMENFIL
              FROM PARAMFILIAL
             WHERE CDFILIAL = V_CDFILIAL;
         EXCEPTION
           WHEN NO_DATA_FOUND THEN
             V_IDLANCDTMENFIL := 'N';
         END;
         IF NVL(V_IDLANCDTMENFIL,'N') = 'S' THEN
            BEGIN
               SELECT MAX(A.NRDIAS)
                 INTO V_NRDIAS
                 FROM (SELECT NRDIASENTRFIL NRDIAS
                         FROM PARAMFILIAL
                        WHERE CDFILIAL = V_CDFILIAL
                          AND NVL(IDLANCDTMENFIL,'N') = 'S'
                        UNION ALL
                       SELECT NRDIASSAIDFIL NRDIAS
                         FROM PARAMFILIAL
                        WHERE CDFILIAL = V_CDFILIAL
                          AND NVL(IDLANCDTMENFIL,'N') = 'S'
                        UNION ALL
                       SELECT NRDIASAJUSFIL NRDIAS
                         FROM PARAMFILIAL
                        WHERE CDFILIAL = V_CDFILIAL
                          AND NVL(IDLANCDTMENFIL,'N') = 'S') A;
            EXCEPTION
              WHEN NO_DATA_FOUND THEN
                V_NRDIAS := 0;
            END;
         ELSE
            BEGIN
               SELECT MAX(A.NRDIAS)
                 INTO V_NRDIAS
                 FROM (SELECT NRDIASENTR NRDIAS
                         FROM PARAMESTOQUE
                        WHERE NVL(IDLANCDTMEN,'N') = 'S'
                        UNION ALL
                       SELECT NRDIASSAID NRDIAS
                         FROM PARAMESTOQUE
                        WHERE NVL(IDLANCDTMEN,'N') = 'S'
                        UNION ALL
                       SELECT NRDIASAJUS NRDIAS
                         FROM PARAMESTOQUE
                        WHERE NVL(IDLANCDTMEN,'N') = 'S') A;
            EXCEPTION
              WHEN NO_DATA_FOUND THEN
                V_NRDIAS := 0;
            END;
         END IF;

         IF V_DTINIC >= (V_DTPROCESSA - V_NRDIAS) THEN
            V_DTINIC := V_DTPROCESSA - V_NRDIAS;
         END IF;

         REMOVE_ROP(V_CDFILIAL, V_DTINIC, V_DTFINAL);

         V_DTFILIALROP := V_DTINIC;
         V_NUMEXEC := 1;
         WHILE (V_DTFILIALROP <= V_DTFINAL) LOOP
            V_NRFILIALROP := SEQ_FILIALROP.NEXTVAL;

            IF V_DTFILIALROP = V_DTINIC THEN
               V_AUXSTR := 'S';
            ELSE
               V_AUXSTR := 'N';
            END IF;

            INSERT INTO FILIALROP (NRFILIALROP, CDFILIAL, CDOPERADOR, DTINICROP, DTFINALROP, DTGERAROP, DTFILIALROP)
                 VALUES (V_NRFILIALROP, V_CDFILIAL, NULL, V_DTINICIAL, V_DTFINAL, SYSDATE, V_DTFILIALROP);

            APURAC_INVENTARIOROP(V_CDFILIAL, V_FILIALPAR, V_DTFILIALROP, V_DTFINAL, V_NRFILIALROP, NULL, V_AUXSTR);

            APURAC_FATURAMENTOROP(V_CDFILIAL, V_FILIALPAR, V_DTFILIALROP, V_DTFINAL, NULL, V_NRFILIALROP, V_AUXSTR);

            APURAC_CONSUMOROP(V_CDFILIAL, V_FILIALPAR, V_DTFILIALROP, V_DTFILIALROP, NULL, V_NRFILIALROP);

            APURAC_RETIRADAROP(V_CDFILIAL, V_FILIALPAR, V_DTFILIALROP, V_DTFILIALROP, V_CDFILISUBGR, V_NRFILIALROP);

            APURAC_DESPESAROP(V_CDFILIAL, V_FILIALPAR, V_DTFILIALROP, V_DTFILIALROP, V_NRFILIALROP, V_NUMEXEC);

            APURAC_INVESTROP(V_CDFILIAL, V_FILIALPAR, V_DTFILIALROP, V_DTFILIALROP, V_NRFILIALROP);

            V_DTFILIALROP := TO_DATE(V_DTFILIALROP+1);
            V_NUMEXEC := V_NUMEXEC + 1;
            
            COMMIT;
         END LOOP;

         IF V_IDFECHDIAROP = 'S'  THEN
            UPDATE DATAAPURACAOROP
               SET DTINIAPURACROP  = V_DTFINAL + 1,
                   DTFINAPURACROP  = V_DTFINAL + 1
             WHERE CDFILIAL = V_CDFILIAL;
         END IF;

         APURAC_DM_RESULT_OPERACIONAL(V_CDFILIAL, V_DTINIC, V_DTFINAL, C_FILIAL.NRORG);
      END IF;

      INSERT INTO LOGOPERAGES
          (NMTABELA,     DTOPERACAO,   NMRESPOPER,  IDTIPOOPER,   DSOPERACAO,  CDPESQITEM,
           DSRDMUSUOPER, DSENDUSUOPER, DSNMUSUOPER, DSMETUSUOPER, CDORESPOPER, CDFILLOGOPER)
       VALUES
          ('FILIALROP', SYSDATE, NVL(V_NMOPERADOR,' '), 1, 'Unidade '||C_FILIAL.CDFILIAL||' - '||C_FILIAL.NMFILIAL||' foi apurada. ', V_NRFILIALROP,
          ' ', ' ', ' ', ' ', ' ', NVL(P_CDOPERLOGADO,' '));

      COMMIT;
   END LOOP;

   DELETE FROM LISTA WHERE NRLISTA = V_NRLISTA;

   COMMIT;
END;
/

