DROP TRIGGER ATUALIZA_OPERADOR_FUNCIONARIO
/

CREATE OR REPLACE
TRIGGER ATUALIZA_OPERADOR_FUNCIONARIO BEFORE INSERT OR UPDATE
ON OPERADOR
FOR EACH ROW
BEGIN
   IF :NEW.CDCLIENTE IS NULL AND :NEW.CDFUNC IS NULL THEN
      INSERT INTO FUNCIONARIO
         (CDEMPRESA,
          CDFUNC,
          CDFILIAL,
          CDEQUIPETRAB,
          CDDEPTO,
          CDCARGO,
          CDTPADMISSAO,
          CDESTACIVIL,
          CDESCOLARIDADE,
          CDNACIONALIDADE,
          CDCONVMEDI,
          CDCONTSINDI,
          CDSINDICATO,
          NMFUNC,
          IDCONTFUNC,
          IDSEXOFUNC,
          DTNASCFUNC,
          NMLOCANASC,
          DSENDEFUNC,
          NMBAIRFUNC,
          NRCEPFUNC,
          NRFONEFUNC,
          DTEFETFUNC,
          IDVTRAFUNC,
          IDCBASFUNC,
          IDCONVMEDI,
          IDCONVMEGE,
          IDSEXEFUNC,
          CDPAIS,
          SGESTADO,
          CDMUNICIPIO,
          NMLOGINFUNC,
          DSEMAILFUNC, 
          CDEMPRESAFOLHA)
         (SELECT
             '01',
             SUBSTR(:NEW.CDOPERADOR,3,10),
             '9999',
             '0000000999',
             '999',
             '9999',
             'X',
             'O',
             'O',
             '99',
             '99',
             'O',
             '9999',
             :NEW.NMOPERADOR,
             '1',
             'M',
             :NEW.DTNASCIMENTO,
             'Belo Horizonte',
             :NEW.DSENDEOPER,
             :NEW.NMBAIROPER,
             :NEW.NRCEPOPER,
             :NEW.NRTELEOPER,
             TO_DATE(TO_CHAR(SYSDATE,'DDMMYYYY'),'DDMMYYYY'),
             'N',
             'N',
             'N',
             'N',
             'N',
             :NEW.CDPAIS,
             :NEW.SGESTADO,
             :NEW.CDMUNICIPIO,
             :NEW.NMACESSO,
             :NEW.DSEMAILOPER,
             '01'
          FROM
             PARAMGERAL
          WHERE
             NOT EXISTS (SELECT
                            B.CDFUNC
                         FROM
                            FUNCIONARIO B
                         WHERE
                            (B.CDEMPRESA = '01') AND
                            (B.CDFUNC    = SUBSTR(:NEW.CDOPERADOR,3,10))));
      :NEW.CDEMPRESA := '01';
      :NEW.CDFUNC    := SUBSTR(:NEW.CDOPERADOR,3,10);
   END IF;
   IF :NEW.CDFUNC IS NOT NULL THEN
      :NEW.CDEMPRESA := '01';
   END IF;
   IF UPDATING AND :NEW.CDFUNC IS NOT NULL THEN
      SELECT CDSETOR
        INTO :NEW.CDSETOR
        FROM FUNCIONARIO
       WHERE CDEMPRESA = :NEW.CDEMPRESA
         AND CDFUNC = :NEW.CDFUNC;
   END IF;
END;
/