CREATE OR REPLACE PROCEDURE USR_IMPORT_HIERARQ (P_NRSEQIMPORT   IN VARCHAR2,
                                                P_CDOPERADOR    IN VARCHAR2)
IS 
  
  TYPE CURTYPE       IS REF CURSOR;
  C_IMPORT           CURTYPE;
  
  V_NRSEQIMPORT      VARCHAR2(10);
  V_CDFILIMP         VARCHAR2(4);
  V_CDCENTCUSTIMP    VARCHAR2(15);
  V_NRLINHAARQ       NUMBER;
  V_DSEMAILCONTROL   VARCHAR2(200);
  V_DSEMAILAPROVN1   VARCHAR2(200);
  V_RESP_SEGMENTO    VARCHAR2(20);
  V_RESP_DIRETORIA   VARCHAR2(20);
  V_RESP_REGIONAL    VARCHAR2(20);
  V_RESP_GOP         VARCHAR2(20);
  
  V_ERROR            VARCHAR2(1);
  V_DSERRO           VARCHAR2(2000);
  
  V_CDFILIAL         VARCHAR2(4);
  
  V_CDOPERCONTROL    VARCHAR2(12);
  V_NMOPERCONTROL    VARCHAR2(100);
  
  V_CDOPERSEGMENTO   VARCHAR2(12);
  
  V_CDOPERDIRETOR    VARCHAR2(12);
  V_NMOPERDIRETOR     VARCHAR2(100);
  
  V_CDOPERREGIONAL   VARCHAR2(12);
  
  V_CDOPERGEROPERAC  VARCHAR2(12);
  V_NMOPERGEROPERAC  VARCHAR2(100);
  
  V_OLD_CDGOPERAC    VARCHAR2(12);
  V_OLD_NMGOPERAC    VARCHAR2(100);
  V_OLD_CDSUPERVISOR VARCHAR2(12);
  V_OLD_NMSUPERVISOR VARCHAR2(100);
  V_OLD_CDDIRETORIA  VARCHAR2(12);
  V_OLD_NMDIRETORIA  VARCHAR2(100);

  V_NRNOTIFICSUPPLY VARCHAR2(20);
  
  V_DSTEXTNOTIFIC   VARCHAR2(1000);

  V_IDSISDOS        VARCHAR2(1);
  
BEGIN
   OPEN C_IMPORT FOR SELECT NRSEQIMPORT, CDCENTROCUS, NRLINHAARQ,
                            SUBSTR(CDCENTROCUS,LENGTH(CDCENTROCUS)-3 , 4) CDFILIAL,
                            DSEMAILAPROVN1, DSEMAILAPROVN2,
                            REPLACE(REGEXP_SUBSTR(DSSEGMENTO,'#[^,]+'), '#', '') ,
                            REPLACE(REGEXP_SUBSTR(DSDIRETORIA,'#[^,]+'), '#', '') ,
                            REPLACE(REGEXP_SUBSTR(DSREGIONAL,'#[^,]+'), '#', '') ,
                            REPLACE(REGEXP_SUBSTR(DSGOP,'#[^,]+'), '#', '') 
                       FROM INTG_HIERARQ
                      WHERE NRSEQIMPORT = P_NRSEQIMPORT
                      ORDER BY NRLINHAARQ;
   LOOP
   FETCH C_IMPORT INTO V_NRSEQIMPORT, V_CDCENTCUSTIMP, V_NRLINHAARQ, V_CDFILIMP, V_DSEMAILAPROVN1, V_DSEMAILCONTROL, V_RESP_SEGMENTO, V_RESP_DIRETORIA, V_RESP_REGIONAL, V_RESP_GOP; 
   EXIT WHEN C_IMPORT%NOTFOUND;
   
      V_ERROR := 'N';
      /* VALIDA SE AUNIDADE EXISTE*/
      BEGIN
         SELECT CDFILIAL
           INTO V_CDFILIAL
           FROM FILIAL
          WHERE CDFILIAL = V_CDFILIMP;
      EXCEPTION
         WHEN NO_DATA_FOUND THEN
            V_CDFILIAL := NULL;
      END; 
      IF V_CDFILIAL IS NULL THEN
         V_ERROR := 'S';
          
         V_DSERRO := 'LINHA '||V_NRLINHAARQ||' Unidade: '||V_CDFILIMP||' vinculada ao Centro de Custo: '||V_CDCENTCUSTIMP||' da coluna: "ns1:CodigoCentroCoste" nao existe no TecFood';
          
         /*GRAVA NA TABELA DE LOG*/
         INSERT INTO LOG_INTG_HIERARQ (NRSEQIMPORT, DSERRO, CDOPERADOR, DTIMPORTACAO, NRLINHAARQ)
         VALUES (V_NRSEQIMPORT, V_DSERRO, P_CDOPERADOR,SYSDATE, V_NRLINHAARQ);
      END IF;
    
      /* VALIDA SE O CONTROLLER EXISTE*/
      BEGIN
         SELECT CDOPERADOR, NMOPERADOR
           INTO V_CDOPERCONTROL, V_NMOPERCONTROL
           FROM OPERADOR
          WHERE DSEMAILOPER = V_DSEMAILCONTROL;
      EXCEPTION
         WHEN NO_DATA_FOUND THEN
            V_CDOPERCONTROL := NULL;
      END; 
      IF V_CDOPERCONTROL IS NULL THEN
         V_ERROR := 'S';
          
         V_DSERRO :=  'LINHA '||V_NRLINHAARQ||' E-mail: '||V_DSEMAILCONTROL||' da coluna: "ns1:EmailAutorizador2" nao esta vinculado a nenhum usuario do TecFood';
          
         /*GRAVA NA TABELA DE LOG*/
         INSERT INTO LOG_INTG_HIERARQ (NRSEQIMPORT, DSERRO, CDOPERADOR, DTIMPORTACAO, NRLINHAARQ)
         VALUES (V_NRSEQIMPORT, V_DSERRO, P_CDOPERADOR,SYSDATE, V_NRLINHAARQ);
      END IF;
       
      /* VALIDA SE O OPERADOR DO SEGMENTO EXISTE*/
      BEGIN
         SELECT CDOPERADOR
           INTO V_CDOPERSEGMENTO
           FROM OPERADOR
          WHERE CDOPERADOR LIKE '%'||V_RESP_SEGMENTO;
      EXCEPTION
         WHEN NO_DATA_FOUND THEN
            V_CDOPERSEGMENTO := NULL;
      END; 
      IF V_CDOPERSEGMENTO IS NULL THEN
         V_ERROR := 'S';
          
         V_DSERRO :=  'LINHA '||V_NRLINHAARQ||' Usuario: '||V_RESP_SEGMENTO||' da coluna: "ns1:Nivel2" nao existe no TecFood';
          
         /*GRAVA NA TABELA DE LOG*/
         INSERT INTO LOG_INTG_HIERARQ (NRSEQIMPORT, DSERRO, CDOPERADOR, DTIMPORTACAO, NRLINHAARQ)
         VALUES (V_NRSEQIMPORT, V_DSERRO, P_CDOPERADOR,SYSDATE, V_NRLINHAARQ);
      END IF;
    
      /* VALIDA SE O OPERADOR DA DIRETORIA EXISTE*/
      BEGIN
         SELECT CDOPERADOR, NMOPERADOR
           INTO V_CDOPERDIRETOR, V_NMOPERDIRETOR
           FROM OPERADOR
          WHERE CDOPERADOR LIKE '%'||V_RESP_DIRETORIA;
      EXCEPTION
         WHEN NO_DATA_FOUND THEN
            V_CDOPERDIRETOR := NULL;
      END; 
      IF V_CDOPERDIRETOR IS NULL THEN
         V_ERROR := 'S';
         
         V_DSERRO :=  'LINHA '||V_NRLINHAARQ||' Usuario: '||V_RESP_DIRETORIA||' da coluna: "ns1:Nivel4" nao existe no TecFood';
         
         /*GRAVA NA TABELA DE LOG*/
         INSERT INTO LOG_INTG_HIERARQ (NRSEQIMPORT, DSERRO, CDOPERADOR, DTIMPORTACAO, NRLINHAARQ)
         VALUES (V_NRSEQIMPORT, V_DSERRO, P_CDOPERADOR,SYSDATE, V_NRLINHAARQ);
      END IF;
    
      /* VALIDA SE O OPERADOR DA REGIONAL EXISTE*/
      BEGIN
         SELECT CDOPERADOR
           INTO V_CDOPERREGIONAL
           FROM OPERADOR
          WHERE CDOPERADOR LIKE '%'||V_RESP_REGIONAL;
      EXCEPTION
         WHEN NO_DATA_FOUND THEN
            V_CDOPERREGIONAL := NULL;
      END; 
      IF V_CDOPERREGIONAL IS NULL THEN
         V_ERROR := 'S';
          
         V_DSERRO :=  'LINHA '||V_NRLINHAARQ||' Usuario: '||V_RESP_REGIONAL||' da coluna: "ns1:Nivel5" nao existe no TecFood';
          
         /*GRAVA NA TABELA DE LOG*/
         INSERT INTO LOG_INTG_HIERARQ (NRSEQIMPORT, DSERRO, CDOPERADOR, DTIMPORTACAO, NRLINHAARQ)
         VALUES (V_NRSEQIMPORT, V_DSERRO, P_CDOPERADOR,SYSDATE, V_NRLINHAARQ);
      END IF;
    
      /* VALIDA SE O OPERADOR DA GERENTE OPERACIONAL EXISTE*/
      BEGIN
         SELECT CDOPERADOR, NMOPERADOR
           INTO V_CDOPERGEROPERAC, V_NMOPERGEROPERAC
           FROM OPERADOR
          WHERE CDOPERADOR LIKE '%'||V_RESP_GOP;
      EXCEPTION
         WHEN NO_DATA_FOUND THEN
            V_CDOPERGEROPERAC := NULL;
            V_NMOPERGEROPERAC := NULL;
      END; 
      IF V_CDOPERGEROPERAC IS NULL THEN
         V_ERROR := 'S';
          
         V_DSERRO := 'LINHA '||V_NRLINHAARQ||' Usuario: '||V_RESP_GOP||' da coluna: "ns1:Nivel6" nao existe no TecFood';
         
         /*GRAVA NA TABELA DE LOG*/
         INSERT INTO LOG_INTG_HIERARQ (NRSEQIMPORT, DSERRO, CDOPERADOR, DTIMPORTACAO, NRLINHAARQ)
         VALUES (V_NRSEQIMPORT, V_DSERRO, P_CDOPERADOR,SYSDATE, V_NRLINHAARQ);
      END IF;
       
      --######################################### ATUALIZACAO DOS REGISTROS NA FILIAL, NA ALCADA E NAS APROVACOES ##################################
       
      IF V_ERROR = 'N' THEN
         /*DEFINI O IDSISDOS DO SISTEMA */
         SELECT IDSISDOS
           INTO V_IDSISDOS
           FROM PARAMGERAL;

         /*GUARDA OS APROVADORES ATUAIS EM SEUS RESPECTIVOS CARGOS*/
         SELECT CDGOPERAC, GO.NMOPERADOR AS NMGOPERAC, CDSUPERVISOR, CONT.NMOPERADOR AS NMSUPERVISOR, CDDIRETORIA, DIRET.NMOPERADOR AS NMDIRETORIA
           INTO V_OLD_CDGOPERAC, V_OLD_NMGOPERAC, V_OLD_CDSUPERVISOR, V_OLD_NMSUPERVISOR, V_OLD_CDDIRETORIA,  V_OLD_NMDIRETORIA
           FROM FILIAL LEFT JOIN OPERADOR GO   ON CDGOPERAC = GO.CDOPERADOR 
                       LEFT JOIN OPERADOR CONT ON CDSUPERVISOR = CONT.CDOPERADOR
                       LEFT JOIN OPERADOR DIRET ON CDDIRETORIA= DIRET.CDOPERADOR
          WHERE CDFILIAL = V_CDFILIAL;
          
          /*ATUALIZA OS REGISTROS NA TABELA DE FILIAL*/
         UPDATE FILIAL
            SET CDDIRETORIA  = V_CDOPERDIRETOR,  -- DIRETORIA
                CDSUPERVISOR = V_CDOPERCONTROL,  -- CONTROLLER
                CDGOPERAC    = V_CDOPERGEROPERAC -- GO
          WHERE CDFILIAL = V_CDFILIAL;

         /*************************** INICIO ATUALIZACAO DA GO **********************/
         IF (V_OLD_CDGOPERAC <> V_CDOPERGEROPERAC) THEN 
            /*DELETA OS OPERADORES RELACIONADOS AO GO ANTERIOR*/  
            DELETE FROM ALCADAGEROPER
             WHERE CDFILIAL   = V_CDFILIAL
               AND CDOPERADOR = V_OLD_CDGOPERAC;
                 
            /*UPDATA O CAMPO DE GO NAS ALCADAS*/ 
            UPDATE ALCADAGERFILI
               SET CDOPERADOR = V_CDOPERGEROPERAC
             WHERE CDFILIAL   = V_CDFILIAL
               AND CDOPERADOR = V_OLD_CDGOPERAC;
                 
            /*ATUALIZA AS APROVACOES PENDENTES PARA A  NOVA GO*/
            UPDATE APROVALCADA SET CDOPERADOR = V_CDOPERGEROPERAC
             WHERE CDFILDEST = V_CDFILIAL
               AND CDOPERADOR = V_OLD_CDGOPERAC
               AND IDAPROVADO = 'PENDENTE';
                 
            /*RETIRA O RELACIONAMENTO DA GO PARA A FILIAL NO SUPPLYTEK*/
            DELETE FROM ACESSOSUPPLY
             WHERE CDFILIAL = V_CDFILIAL
               AND CDOPERADOR = V_OLD_CDGOPERAC;
              
            /* SALVA NOTIFICACAO NO SUPPLYTEK SOBRE A TROCA DE GO*/
            FOR C_NOTIFIC IN (SELECT CDFILIAL, CDOPERADOR, NRORG
                                FROM ACESSOSUPPLY
                               WHERE CDFILIAL = V_CDFILIAL
                                 AND IDPERFILACES = 'GU')
            LOOP
               --GERA O SEQUENCIAL DA TABELA NOTIFICSUPPLY
               NOVO_CODIGO_SC('NOTIFICSUPPLY', V_NRNOTIFICSUPPLY, 1); 
               V_NRNOTIFICSUPPLY := SUBSTR(V_NRNOTIFICSUPPLY, 11, 10);

               V_DSTEXTNOTIFIC := 'A partir de hoje o(a) senhor(a) '|| V_NMOPERGEROPERAC||' sera responsavel pelas aprovacoes dos pedidos (pendentes e futuros) desta unidade no lugar do(a) senhor(a) '||V_OLD_NMGOPERAC||'.';
    
               INSERT INTO NOTIFICSUPPLY ( NRNOTIFICSUPPLY, CDFILIAL, CDOPERADOR,  NMNOTIFICACAO, DSTEXTNOTIFIC,  IDNOTIFICLIDO, NRORG, IDATIVO, DTINCLUSAO, CDOPERINCLUSAO) 
                    VALUES
                    (V_NRNOTIFICSUPPLY, C_NOTIFIC.CDFILIAL, C_NOTIFIC.CDOPERADOR, 'Alteracao de Aprovador', V_DSTEXTNOTIFIC, 'N', C_NOTIFIC.NRORG, 'S', SYSDATE, 
                     P_CDOPERADOR); 
            END LOOP;
         END IF;
          /*************************** FIM ATUALIZACAO DA GO **********************/
         
         /*************************** INICIO ATUALIZACAO DO CONTROLLER **********************/
         IF (V_OLD_CDSUPERVISOR <> V_CDOPERCONTROL) THEN 
            /*DELETA OS OPERADORES RELACIONADOS AO CONTROLLER ANTERIOR*/  
            DELETE FROM ALCADAGEROPER
             WHERE CDFILIAL   = V_CDFILIAL
               AND CDOPERADOR = V_OLD_CDSUPERVISOR;
               
            /*UPDATA O CAMPO DE CONTROLLER NAS ALCADAS*/ 
            UPDATE ALCADAGERFILI
               SET CDOPERADOR = V_CDOPERCONTROL
             WHERE CDFILIAL   = V_CDFILIAL
               AND CDOPERADOR = V_OLD_CDSUPERVISOR;
               
            /*ATUALIZA AS APROVACOES PENDENTES PARA A  NOVA CONTROLLER*/
            UPDATE APROVALCADA 
               SET CDOPERADOR = V_CDOPERCONTROL
             WHERE CDFILDEST = V_CDFILIAL
               AND CDOPERADOR = V_OLD_CDSUPERVISOR
               AND IDAPROVADO = 'PENDENTE';
               
            /*RETIRA O RELACIONAMENTO DO CONTROLLER PARA A FILIAL NO SUPPLYTEK*/
            DELETE FROM ACESSOSUPPLY
             WHERE CDFILIAL = V_CDFILIAL
               AND CDOPERADOR = V_OLD_CDSUPERVISOR;
            
            /* SALVA NOTIFICACAO NO SUPPLYTEK SOBRE A TROCA DE CONTROLLER*/
            FOR C_NOTIFIC IN (SELECT CDFILIAL, CDOPERADOR, NRORG
                                FROM ACESSOSUPPLY
                               WHERE CDFILIAL = V_CDFILIAL
                                 AND IDPERFILACES = 'GU')
            LOOP
               --GERA O SEQUENCIAL DA TABELA NOTIFICSUPPLY
               NOVO_CODIGO_SC('NOTIFICSUPPLY', V_NRNOTIFICSUPPLY, 1); 
               V_NRNOTIFICSUPPLY := SUBSTR(V_NRNOTIFICSUPPLY, 11, 10); 
               V_DSTEXTNOTIFIC := 'A partir de hoje o(a) senhor(a) '|| V_NMOPERCONTROL||' sera responsavel pelas aprovacoes dos pedidos (pendentes e futuros) desta unidade no lugar do(a) senhor(a) '||V_OLD_NMSUPERVISOR||'.';

               INSERT INTO NOTIFICSUPPLY ( NRNOTIFICSUPPLY, CDFILIAL, CDOPERADOR,  NMNOTIFICACAO, DSTEXTNOTIFIC,  IDNOTIFICLIDO, NRORG, IDATIVO, DTINCLUSAO, CDOPERINCLUSAO) 
                    VALUES (V_NRNOTIFICSUPPLY, C_NOTIFIC.CDFILIAL, C_NOTIFIC.CDOPERADOR, 'Alteracao de Aprovador', V_DSTEXTNOTIFIC, 'N', C_NOTIFIC.NRORG, 'S', SYSDATE, 
                    P_CDOPERADOR); 
            END LOOP;
         END IF;
         /*************************** FIM ATUALIZACAO DO CONTROLLER **********************/
          
         /*************************** INICIO ATUALIZACAO DA DIRETORIA **********************/
         IF V_IDSISDOS <> '3' THEN
            IF (V_OLD_CDDIRETORIA <> V_CDOPERDIRETOR) THEN 
               /*DELETA OS OPERADORES RELACIONADOS AO DIRETOR ANTERIOR*/  
               DELETE FROM ALCADAGEROPER
                WHERE CDFILIAL   = V_CDFILIAL
                  AND CDOPERADOR = V_OLD_CDDIRETORIA;
                   
               /*UPDATA O CAMPO DE DIRETOR NAS ALCADAS*/ 
               UPDATE ALCADAGERFILI
                  SET CDOPERADOR = V_CDOPERDIRETOR
                WHERE CDFILIAL   = V_CDFILIAL
                  AND CDOPERADOR = V_OLD_CDDIRETORIA;
                   
               /*ATUALIZA AS APROVACOES PENDENTES PARA A  NOVA DIRETORIA*/
               UPDATE APROVALCADA 
                  SET CDOPERADOR = V_CDOPERDIRETOR
                WHERE CDFILDEST = V_CDFILIAL
                  AND CDOPERADOR = V_OLD_CDDIRETORIA
                  AND IDAPROVADO = 'PENDENTE';
                   
               /*RETIRA O RELACIONAMENTO DO DIRETOR PARA A FILIAL NO SUPPLYTEK*/
               DELETE FROM ACESSOSUPPLY
                WHERE CDFILIAL = V_CDFILIAL
                  AND CDOPERADOR = V_OLD_CDDIRETORIA;
                
               /* SALVA NOTIFICACAO NO SUPPLYTEK SOBRE A TROCA DE DIRETOR*/
               FOR C_NOTIFIC IN (SELECT CDFILIAL, CDOPERADOR, NRORG
                                   FROM ACESSOSUPPLY
                                  WHERE CDFILIAL = V_CDFILIAL
                                    AND IDPERFILACES = 'GU')
               LOOP
                  --GERA O SEQUENCIAL DA TABELA NOTIFICSUPPLY
                  NOVO_CODIGO_SC('NOTIFICSUPPLY', V_NRNOTIFICSUPPLY, 1); 
                  V_NRNOTIFICSUPPLY := SUBSTR(V_NRNOTIFICSUPPLY, 11, 10);
      
                  V_DSTEXTNOTIFIC := 'A partir de hoje o(a) senhor(a) '||V_NMOPERDIRETOR||' sera responsavel pelas aprovacoes dos pedidos (pendentes e futuros) desta unidade no lugar do(a) senhor(a) '||V_OLD_NMDIRETORIA||'.';
      
                  INSERT INTO NOTIFICSUPPLY ( NRNOTIFICSUPPLY, CDFILIAL, CDOPERADOR,  NMNOTIFICACAO, DSTEXTNOTIFIC,  IDNOTIFICLIDO, NRORG, IDATIVO, DTINCLUSAO, CDOPERINCLUSAO) 
                       VALUES(V_NRNOTIFICSUPPLY, C_NOTIFIC.CDFILIAL, C_NOTIFIC.CDOPERADOR, 'Alteracao de Aprovador', V_DSTEXTNOTIFIC, 'N', C_NOTIFIC.NRORG, 'S', SYSDATE, 
                       P_CDOPERADOR); 
               END LOOP;
            END IF;
         END IF;
         /*************************** FIM ATUALIZACAO DA DIRETORIA **********************/
         /*************************** ATUALIZA STATUS DA IMPORTACAO **********************/
         UPDATE INTG_HIERARQ
            SET IDIMPORTADO = 'S'
          WHERE NRSEQIMPORT = P_NRSEQIMPORT;
      END IF;
   END LOOP;
   CLOSE C_IMPORT;
END;
/