Ir para conteúdo

Arquivado

Este tópico foi arquivado e está fechado para novas respostas.

ViniciusDias

Ajuda com cursor oracle

Recommended Posts

Bom dia.

 

Quando cadastro as parametrizações da filial (1)de uma empresa, o gatilho replica essa parametrização para todas as filiais dessa empresa. Como faço para que essa replicação aconteça para todas as filiais de todas as empresas?

 

Segue conteúdo do gatilho:

 

CREATE TRIGGER TGS_E095HFO_AIU
  AFTER INSERT ON E095HFO
  FOR EACH ROW
DECLARE
  PROGMA AUTONOMOUS_TRANSACTION;
  nCodEmp E070Emp.CodEmp%Type;
  nCodFil E070Fil.CodFil%Type;
  Cursor Cur_E070Emp is
    Select CodEmp From E070Emp Where CodEmp not in (99);
  Cursor Cur_E070Fil(nEmpCod E070Emp.CodEmp%TYPE) is
    Select CodEmp, CodFil
    From E070Fil
    Where CodEmp = nEmpCod 
    Order By CodEmp, CodFil;
BEGIN
  If (:New.CodEmp <> 1 And :New.CodFil = 1) And (:New.CodCpg <> 'AV') then
    For Reg_E070Fil in Cur_E070Fil(:New.CodEmp) Loop
        nCodFil := Reg_E070Fil.CodFil;
    BEGIN
            Insert into E095HFO
              (CODFOR,
               CODEMP,
               CODFIL,
               SALDUP,
               SALOUT,
               SALCRE,
               DATUPE,
               VLRUPE,
               DATUCP,
               VLRUCP,
               DATMCP,
               VLRMCP,
               DATUPG,
               VLRUPG,
               QTDPGT,
               DATATR,
               VLRATR,
               MAIATR,
               MEDATR,
               PRZENT,
               CPRCQL,
               CPRCPE,
               CPRCAT,
               CODTPR,
               CODCPG,
               CODFPG,
               QTDDCV,
               CRIEDV,
               CODTRA,
               CODPOR,
               CODCRT,
               CODBAN,
               CODAGE,
               CCBFOR,
               CODCRP,
               ULTDUP,
               PAGJMM,
               PAGTIR,
               PAGDTJ,
               PAGMUL,
               PAGDTM,
               PERDSC,
               TOLDSC,
               ANTDSC,
               PERDS1,
               PERDS2,
               PERDS3,
               PERDS4,
               PERDS5,
               PERFUN,
               PERINS,
               INDIND,
               CRIRAT,
               CTARED,
               CTARCR,
               CTAFDV,
               CTAFCR,
               CONEST,
               PERFRE,
               PERSEG,
               PEREMB,
               PERENC,
               PEROUT,
               PERISS,
               PERIRF,
               SEQORM,
               CIFFOB,
               CODFAV,
               PERINE,
               RVLCFR,
               RVLFRE,
               RVLSEG,
               RVLEMB,
               RVLENC,
               RVLOUT,
               RVLDAR,
               RVLFEI,
               RVLSEI,
               RVLOUI,
               CODDEP)
            values
              (:New.CODFOR,
               :New.CodEmp,
               nCodFil,
               :New.SALDUP,
               :New.SALOUT,
               :New.SALCRE,
               :New.DATUPE,
               :New.VLRUPE,
               :New.DATUCP,
               :New.VLRUCP,
               :New.DATMCP,
               :New.VLRMCP,
               :New.DATUPG,
               :New.VLRUPG,
               :New.QTDPGT,
               :New.DATATR,
               :New.VLRATR,
               :New.MAIATR,
               :New.MEDATR,
               :New.PRZENT,
               :New.CPRCQL,
               :New.CPRCPE,
               :New.CPRCAT,
               :New.CODTPR,
               :New.CODCPG,
               :New.CODFPG,
               :New.QTDDCV,
               :New.CRIEDV,
               :New.CODTRA,
               :New.CODPOR,
               :New.CODCRT,
               :New.CODBAN,
               :New.CODAGE,
               :New.CCBFOR,
               :New.CODCRP,
               :New.ULTDUP,
               :New.PAGJMM,
               :New.PAGTIR,
               :New.PAGDTJ,
               :New.PAGMUL,
               :New.PAGDTM,
               :New.PERDSC,
               :New.TOLDSC,
               :New.ANTDSC,
               :New.PERDS1,
               :New.PERDS2,
               :New.PERDS3,
               :New.PERDS4,
               :New.PERDS5,
               :New.PERFUN,
               :New.PERINS,
               :New.INDIND,
               :New.CRIRAT,
               :New.CTARED,
               :New.CTARCR,
               :New.CTAFDV,
               :New.CTAFCR,
               :New.CONEST,
               :New.PERFRE,
               :New.PERSEG,
               :New.PEREMB,
               :New.PERENC,
               :New.PEROUT,
               :New.PERISS,
               :New.PERIRF,
               :New.SEQORM,
               :New.CIFFOB,
               :New.CODFAV,
               :New.PERINE,
               :New.RVLCFR,
               :New.RVLFRE,
               :New.RVLSEG,
               :New.RVLEMB,
               :New.RVLENC,
               :New.RVLOUT,
               :New.RVLDAR,
               :New.RVLFEI,
               :New.RVLSEI,
               :New.RVLOUI,
               :New.CODDEP);
            COMMIT;
          EXCEPTION
            WHEN OTHERS THEN
              raise_application_error(-20000,
                                      TO_CHAR(SQLCODE) || '-' || SQLERRM);
              ROLLBACK;
          END;
    end if;
        COMMIT;
    End Loop;
  end if;
END;

 

Att,

Vinicius Dias

 

Compartilhar este post


Link para o post
Compartilhar em outros sites

Tente

 

CREATE TRIGGER TGS_E095HFO_AIU
  AFTER INSERT ON E095HFO
  FOR EACH ROW
DECLARE
  PROGMA AUTONOMOUS_TRANSACTION;
  nCodEmp E070Emp.CodEmp%Type;
  nCodFil E070Fil.CodFil%Type;
  Cursor Cur_E070Fil(nEmpCod E070Emp.CodEmp%TYPE) is
    Select CodEmp, CodFil
    From E070Fil;
BEGIN
  If (:New.CodEmp <> 1 And :New.CodFil = 1) And (:New.CodCpg <> 'AV') then
    For Reg_E070Fil in Cur_E070Fil(:New.CodEmp) Loop
        nCodFil := Reg_E070Fil.CodFil;
    BEGIN
            Insert into E095HFO
              (CODFOR,
               CODEMP,
               CODFIL,
               SALDUP,
               SALOUT,
               SALCRE,
               DATUPE,
               VLRUPE,
               DATUCP,
               VLRUCP,
               DATMCP,
               VLRMCP,
               DATUPG,
               VLRUPG,
               QTDPGT,
               DATATR,
               VLRATR,
               MAIATR,
               MEDATR,
               PRZENT,
               CPRCQL,
               CPRCPE,
               CPRCAT,
               CODTPR,
               CODCPG,
               CODFPG,
               QTDDCV,
               CRIEDV,
               CODTRA,
               CODPOR,
               CODCRT,
               CODBAN,
               CODAGE,
               CCBFOR,
               CODCRP,
               ULTDUP,
               PAGJMM,
               PAGTIR,
               PAGDTJ,
               PAGMUL,
               PAGDTM,
               PERDSC,
               TOLDSC,
               ANTDSC,
               PERDS1,
               PERDS2,
               PERDS3,
               PERDS4,
               PERDS5,
               PERFUN,
               PERINS,
               INDIND,
               CRIRAT,
               CTARED,
               CTARCR,
               CTAFDV,
               CTAFCR,
               CONEST,
               PERFRE,
               PERSEG,
               PEREMB,
               PERENC,
               PEROUT,
               PERISS,
               PERIRF,
               SEQORM,
               CIFFOB,
               CODFAV,
               PERINE,
               RVLCFR,
               RVLFRE,
               RVLSEG,
               RVLEMB,
               RVLENC,
               RVLOUT,
               RVLDAR,
               RVLFEI,
               RVLSEI,
               RVLOUI,
               CODDEP)
            values
              (:New.CODFOR,
               :New.CodEmp,
               nCodFil,
               :New.SALDUP,
               :New.SALOUT,
               :New.SALCRE,
               :New.DATUPE,
               :New.VLRUPE,
               :New.DATUCP,
               :New.VLRUCP,
               :New.DATMCP,
               :New.VLRMCP,
               :New.DATUPG,
               :New.VLRUPG,
               :New.QTDPGT,
               :New.DATATR,
               :New.VLRATR,
               :New.MAIATR,
               :New.MEDATR,
               :New.PRZENT,
               :New.CPRCQL,
               :New.CPRCPE,
               :New.CPRCAT,
               :New.CODTPR,
               :New.CODCPG,
               :New.CODFPG,
               :New.QTDDCV,
               :New.CRIEDV,
               :New.CODTRA,
               :New.CODPOR,
               :New.CODCRT,
               :New.CODBAN,
               :New.CODAGE,
               :New.CCBFOR,
               :New.CODCRP,
               :New.ULTDUP,
               :New.PAGJMM,
               :New.PAGTIR,
               :New.PAGDTJ,
               :New.PAGMUL,
               :New.PAGDTM,
               :New.PERDSC,
               :New.TOLDSC,
               :New.ANTDSC,
               :New.PERDS1,
               :New.PERDS2,
               :New.PERDS3,
               :New.PERDS4,
               :New.PERDS5,
               :New.PERFUN,
               :New.PERINS,
               :New.INDIND,
               :New.CRIRAT,
               :New.CTARED,
               :New.CTARCR,
               :New.CTAFDV,
               :New.CTAFCR,
               :New.CONEST,
               :New.PERFRE,
               :New.PERSEG,
               :New.PEREMB,
               :New.PERENC,
               :New.PEROUT,
               :New.PERISS,
               :New.PERIRF,
               :New.SEQORM,
               :New.CIFFOB,
               :New.CODFAV,
               :New.PERINE,
               :New.RVLCFR,
               :New.RVLFRE,
               :New.RVLSEG,
               :New.RVLEMB,
               :New.RVLENC,
               :New.RVLOUT,
               :New.RVLDAR,
               :New.RVLFEI,
               :New.RVLSEI,
               :New.RVLOUI,
               :New.CODDEP);
            COMMIT;
          EXCEPTION
            WHEN OTHERS THEN
              raise_application_error(-20000,
                                      TO_CHAR(SQLCODE) || '-' || SQLERRM);
              ROLLBACK;
          END;
    end if;
        COMMIT;
    End Loop;
  end if;
END;

 

Compartilhar este post


Link para o post
Compartilhar em outros sites

 

CREATE TRIGGER TGS_E095HFO_AIU
  AFTER INSERT ON E095HFO
  FOR EACH ROW
DECLARE
  PROGMA AUTONOMOUS_TRANSACTION;
  nCodEmp E070Emp.CodEmp%Type;
  nCodFil E070Fil.CodFil%Type;
  Cursor Cur_E070Fil is
    Select CodEmp, CodFil
    From E070Fil;
BEGIN
  If (:New.CodEmp <> 1 And :New.CodFil = 1) And (:New.CodCpg <> 'AV') then
    For Reg_E070Fil in Cur_E070Fil  Loop
        nCodFil := Reg_E070Fil.CodFil;
    BEGIN
            Insert into E095HFO
              (CODFOR,
               CODEMP,
               CODFIL,
               SALDUP,
               SALOUT,
               SALCRE,
               DATUPE,
               VLRUPE,
               DATUCP,
               VLRUCP,
               DATMCP,
               VLRMCP,
               DATUPG,
               VLRUPG,
               QTDPGT,
               DATATR,
               VLRATR,
               MAIATR,
               MEDATR,
               PRZENT,
               CPRCQL,
               CPRCPE,
               CPRCAT,
               CODTPR,
               CODCPG,
               CODFPG,
               QTDDCV,
               CRIEDV,
               CODTRA,
               CODPOR,
               CODCRT,
               CODBAN,
               CODAGE,
               CCBFOR,
               CODCRP,
               ULTDUP,
               PAGJMM,
               PAGTIR,
               PAGDTJ,
               PAGMUL,
               PAGDTM,
               PERDSC,
               TOLDSC,
               ANTDSC,
               PERDS1,
               PERDS2,
               PERDS3,
               PERDS4,
               PERDS5,
               PERFUN,
               PERINS,
               INDIND,
               CRIRAT,
               CTARED,
               CTARCR,
               CTAFDV,
               CTAFCR,
               CONEST,
               PERFRE,
               PERSEG,
               PEREMB,
               PERENC,
               PEROUT,
               PERISS,
               PERIRF,
               SEQORM,
               CIFFOB,
               CODFAV,
               PERINE,
               RVLCFR,
               RVLFRE,
               RVLSEG,
               RVLEMB,
               RVLENC,
               RVLOUT,
               RVLDAR,
               RVLFEI,
               RVLSEI,
               RVLOUI,
               CODDEP)
            values
              (:New.CODFOR,
               :New.CodEmp,
               nCodFil,
               :New.SALDUP,
               :New.SALOUT,
               :New.SALCRE,
               :New.DATUPE,
               :New.VLRUPE,
               :New.DATUCP,
               :New.VLRUCP,
               :New.DATMCP,
               :New.VLRMCP,
               :New.DATUPG,
               :New.VLRUPG,
               :New.QTDPGT,
               :New.DATATR,
               :New.VLRATR,
               :New.MAIATR,
               :New.MEDATR,
               :New.PRZENT,
               :New.CPRCQL,
               :New.CPRCPE,
               :New.CPRCAT,
               :New.CODTPR,
               :New.CODCPG,
               :New.CODFPG,
               :New.QTDDCV,
               :New.CRIEDV,
               :New.CODTRA,
               :New.CODPOR,
               :New.CODCRT,
               :New.CODBAN,
               :New.CODAGE,
               :New.CCBFOR,
               :New.CODCRP,
               :New.ULTDUP,
               :New.PAGJMM,
               :New.PAGTIR,
               :New.PAGDTJ,
               :New.PAGMUL,
               :New.PAGDTM,
               :New.PERDSC,
               :New.TOLDSC,
               :New.ANTDSC,
               :New.PERDS1,
               :New.PERDS2,
               :New.PERDS3,
               :New.PERDS4,
               :New.PERDS5,
               :New.PERFUN,
               :New.PERINS,
               :New.INDIND,
               :New.CRIRAT,
               :New.CTARED,
               :New.CTARCR,
               :New.CTAFDV,
               :New.CTAFCR,
               :New.CONEST,
               :New.PERFRE,
               :New.PERSEG,
               :New.PEREMB,
               :New.PERENC,
               :New.PEROUT,
               :New.PERISS,
               :New.PERIRF,
               :New.SEQORM,
               :New.CIFFOB,
               :New.CODFAV,
               :New.PERINE,
               :New.RVLCFR,
               :New.RVLFRE,
               :New.RVLSEG,
               :New.RVLEMB,
               :New.RVLENC,
               :New.RVLOUT,
               :New.RVLDAR,
               :New.RVLFEI,
               :New.RVLSEI,
               :New.RVLOUI,
               :New.CODDEP);
            COMMIT;
          EXCEPTION
            WHEN OTHERS THEN
              raise_application_error(-20000,
                                      TO_CHAR(SQLCODE) || '-' || SQLERRM);
              ROLLBACK;
          END;
    end if;
        COMMIT;
    End Loop;
  end if;
END;

Retirei o parametor do cursor

 

Compartilhar este post


Link para o post
Compartilhar em outros sites

Foi mal teria de ser a variável do cursor , algo como

Reg_E070Fil.codemp 

o :new. indica o novo valor da campo da tabela , no caso do INSERT o valor inserido

Compartilhar este post


Link para o post
Compartilhar em outros sites
Agora, Motta disse:

Foi mal teria de ser a variável do cursor , algo como

Reg_E070Fil.codemp 

o :new. indica o novo valor da campo da tabela , no caso do INSERT o valor inserido

Mesmo alterando ainda não deu certo

Compartilhar este post


Link para o post
Compartilhar em outros sites

O que dá de errado ?

 

Pq esta linha

If (:New.CodEmp <> 1 And :New.CodFil = 1) And (:New.CodCpg <> 'AV') then

?

 

Em tese ler a tabela de empresa/filiias e com base nela inserir bastaria.

Já fiz algo parecido.

Compartilhar este post


Link para o post
Compartilhar em outros sites

Bom dia.

 

Não dá erro, apenas cadastra na empresa 1 e filial 1.

 

Esta linha serve para que esse gatilho rode apenas se a inclusão acontecer nessa empresa e filial. Se for em outra, trata-se de uma parametrização especifica que não deve ser replicada.

 

Att,

Vinicius Dias

Compartilhar este post


Link para o post
Compartilhar em outros sites
CREATE TRIGGER TGS_E095HFO_AIU
  ...
  Cursor Cur_E070Fil is
    Select CodEmp, CodFil
    From E070Fil;
BEGIN
  If (:New.CodEmp <> 1 And :New.CodFil = 1) And (:New.CodCpg <> 'AV') then
    For Reg_E070Fil in Cur_E070Fil  Loop
        nCodFil := Reg_E070Fil.CodFil;
    BEGIN
            Insert into E095HFO
              (CODFOR,
               CODEMP,
               CODFIL,
               SALDUP,
               ...)
            values
              (:New.CODFOR,
               Reg_E070Fil.CodEmp,--verifique se é este campo
               Reg_E070Fil.CodFil,--verifique se é este campo
               :..);
            COMMIT;
          EXCEPTION
            WHEN OTHERS THEN
              raise_application_error(-20000,
                                      TO_CHAR(SQLCODE) || '-' || SQLERRM);
              ROLLBACK;
          END;
    end if;
        COMMIT;
    End Loop;
  end if;
END;

 

Compartilhar este post


Link para o post
Compartilhar em outros sites

Ainda não funcionou.

 

este era o código original, porem este também só inclui para as filiais da empresa 1. Não inclui para as demais.

 

/* ERP - REPLICAR DEFINICÕES DE FORNECEDORES ENTRE EMPRESAS/FILIAIS */
CREATE OR REPLACE TRIGGER TGS_E095HFO_AIU
AFTER INSERT ON E095HFO
FOR EACH ROW
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
nCodEmp E070Emp.CodEmp%Type;
nCodFil E070Fil.CodFil%Type;
Cursor Cur_E070Emp is
Select CodEmp From E070Emp Where CodEmp not in (99);
Cursor Cur_E070Fil(nEmpCod E070Emp.CodEmp%TYPE) is
Select CodEmp, CodFil
From E070Fil
Where CodEmp = nEmpCod
And CodFil <> 1
Order By CodEmp, CodFil;
BEGIN
If (:New.CodEmp in (1, 2, 3, 4, 5, 6, 7, 8, 9) And :New.CodFil = 1) And (:New.CodCpg <> 'AV') then
For Reg_E070Fil in Cur_E070Fil(:New.CodEmp) Loop
nCodFil := Reg_E070Fil.CodFil;
If Updating or Inserting Then
BEGIN
Update E095HFO
Set PRZENT = :New.PRZENT,
CPRCQL = :New.CPRCQL,
CPRCPE = :New.CPRCPE,
CPRCAT = :New.CPRCAT,
CODTPR = :New.CODTPR,
CODCPG = :New.CODCPG,
CODFPG = :New.CODFPG,
QTDDCV = :New.QTDDCV,
CRIEDV = :New.CRIEDV,
CODTRA = :New.CODTRA,
CODPOR = :New.CODPOR,
CODCRT = :New.CODCRT,
CODBAN = :New.CODBAN,
CODAGE = :New.CODAGE,
CCBFOR = :New.CCBFOR,
CODCRP = :New.CODCRP,
PAGJMM = :New.PAGJMM,
PAGTIR = :New.PAGTIR,
PAGDTJ = :New.PAGDTJ,
PAGMUL = :New.PAGMUL,
PAGDTM = :New.PAGDTM,
PERDSC = :New.PERDSC,
TOLDSC = :New.TOLDSC,
ANTDSC = :New.ANTDSC,
PERDS1 = :New.PERDS1,
PERDS2 = :New.PERDS2,
PERDS3 = :New.PERDS3,
PERDS4 = :New.PERDS4,
PERDS5 = :New.PERDS5,
PERFUN = :New.PERFUN,
PERINS = :New.PERINS,
INDIND = :New.INDIND,
CRIRAT = :New.CRIRAT,
CTARED = :New.CTARED,
CTARCR = :New.CTARCR,
CTAFDV = :New.CTAFDV,
CTAFCR = :New.CTAFCR,
CONEST = :New.CONEST,
PERFRE = :New.PERFRE,
PERSEG = :New.PERSEG,
PEREMB = :New.PEREMB,
PERENC = :New.PERENC,
PEROUT = :New.PEROUT,
PERISS = :New.PERISS,
PERIRF = :New.PERIRF,
SEQORM = :New.SEQORM,
CIFFOB = :New.CIFFOB,
CODFAV = :New.CODFAV,
PERINE = :New.PERINE,
RVLCFR = :New.RVLCFR,
RVLFRE = :New.RVLFRE,
RVLSEG = :New.RVLSEG,
RVLEMB = :New.RVLEMB,
RVLENC = :New.RVLENC,
RVLOUT = :New.RVLOUT,
RVLDAR = :New.RVLDAR,
RVLFEI = :New.RVLFEI,
RVLSEI = :New.RVLSEI,
RVLOUI = :New.RVLOUI,
CODDEP = :New.CODDEP
Where CODFOR = :New.CODFOR
And CODEMP = :New.CodEmp
And CODFIL = nCodFil;
EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20000,
TO_CHAR(SQLCODE) || '-' || SQLERRM);
ROLLBACK;
END;
If sql%notfound Then
BEGIN
Insert into E095HFO
(CODFOR,
CODEMP,
CODFIL,
SALDUP,
SALOUT,
SALCRE,
DATUPE,
VLRUPE,
DATUCP,
VLRUCP,
DATMCP,
VLRMCP,
DATUPG,
VLRUPG,
QTDPGT,
DATATR,
VLRATR,
MAIATR,
MEDATR,
PRZENT,
CPRCQL,
CPRCPE,
CPRCAT,
CODTPR,
CODCPG,
CODFPG,
QTDDCV,
CRIEDV,
CODTRA,
CODPOR,
CODCRT,
CODBAN,
CODAGE,
CCBFOR,
CODCRP,
ULTDUP,
PAGJMM,
PAGTIR,
PAGDTJ,
PAGMUL,
PAGDTM,
PERDSC,
TOLDSC,
ANTDSC,
PERDS1,
PERDS2,
PERDS3,
PERDS4,
PERDS5,
PERFUN,
PERINS,
INDIND,
CRIRAT,
CTARED,
CTARCR,
CTAFDV,
CTAFCR,
CONEST,
PERFRE,
PERSEG,
PEREMB,
PERENC,
PEROUT,
PERISS,
PERIRF,
SEQORM,
CIFFOB,
CODFAV,
PERINE,
RVLCFR,
RVLFRE,
RVLSEG,
RVLEMB,
RVLENC,
RVLOUT,
RVLDAR,
RVLFEI,
RVLSEI,
RVLOUI,
CODDEP)
values
(:New.CODFOR,
:New.CodEmp,
nCodFil,
:New.SALDUP,
:New.SALOUT,
:New.SALCRE,
:New.DATUPE,
:New.VLRUPE,
:New.DATUCP,
:New.VLRUCP,
:New.DATMCP,
:New.VLRMCP,
:New.DATUPG,
:New.VLRUPG,
:New.QTDPGT,
:New.DATATR,
:New.VLRATR,
:New.MAIATR,
:New.MEDATR,
:New.PRZENT,
:New.CPRCQL,
:New.CPRCPE,
:New.CPRCAT,
:New.CODTPR,
:New.CODCPG,
:New.CODFPG,
:New.QTDDCV,
:New.CRIEDV,
:New.CODTRA,
:New.CODPOR,
:New.CODCRT,
:New.CODBAN,
:New.CODAGE,
:New.CCBFOR,
:New.CODCRP,
:New.ULTDUP,
:New.PAGJMM,
:New.PAGTIR,
:New.PAGDTJ,
:New.PAGMUL,
:New.PAGDTM,
:New.PERDSC,
:New.TOLDSC,
:New.ANTDSC,
:New.PERDS1,
:New.PERDS2,
:New.PERDS3,
:New.PERDS4,
:New.PERDS5,
:New.PERFUN,
:New.PERINS,
:New.INDIND,
:New.CRIRAT,
:New.CTARED,
:New.CTARCR,
:New.CTAFDV,
:New.CTAFCR,
:New.CONEST,
:New.PERFRE,
:New.PERSEG,
:New.PEREMB,
:New.PERENC,
:New.PEROUT,
:New.PERISS,
:New.PERIRF,
:New.SEQORM,
:New.CIFFOB,
:New.CODFAV,
:New.PERINE,
:New.RVLCFR,
:New.RVLFRE,
:New.RVLSEG,
:New.RVLEMB,
:New.RVLENC,
:New.RVLOUT,
:New.RVLDAR,
:New.RVLFEI,
:New.RVLSEI,
:New.RVLOUI,
:New.CODDEP);
COMMIT;
EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20000,
TO_CHAR(SQLCODE) || '-' || SQLERRM);
ROLLBACK;
END;
end if;
COMMIT;
end if;
End Loop;
end if;
END;

Compartilhar este post


Link para o post
Compartilhar em outros sites

Vamó lá , plsql no notepad é bravo

A ideia e usar o cursor para ler os pares empresa/filial e aí inserir.

 

CREATE OR REPLACE TRIGGER TGS_E095HFO_AIU
    AFTER INSERT ON E095HFO
    FOR EACH ROW
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
    nCodEmp E070Emp.CodEmp%Type;
    nCodFil E070Fil.CodFil%Type;
    
    Cursor Cur_E070Fil(nEmpCod E070Emp.CodEmp%TYPE) is --cursor sem parametro
    Select CodEmp, CodFil
    From E070Fil
    Where CodFil <> 1
    Order By CodEmp, CodFil;
BEGIN
    If (:New.CodEmp in (1, 2, 3, 4, 5, 6, 7, 8, 9) And :New.CodFil = 1) And (:New.CodCpg <> 'AV') then
        For Reg_E070Fil in Cur_E070Fil  Loop
        nCodEmp  := Reg_E070Fil.Codemp; --atualiza cod emp
        nCodFil := Reg_E070Fil.CodFil;
        If Updating or Inserting Then
            BEGIN
            Update E095HFO
            Set PRZENT = :New.PRZENT,
            CPRCQL = :New.CPRCQL,
            ...
            Where CODFOR = :New.CODFOR
            And CODEMP = :New.CodEmp
            And CODFIL = nCodFil;
            EXCEPTION
            WHEN OTHERS THEN
            raise_application_error(-20000,
            TO_CHAR(SQLCODE) || '-' || SQLERRM);
            ROLLBACK;
            END;
            If sql%notfound Then
                BEGIN
                Insert into E095HFO
                (CODFOR,
                CODEMP,
                CODFIL,
                ...)
                values
                (:New.CODFOR,
                nCodEmp, --do cursor
                nCodFil, --do cursor
                ...);
                COMMIT;
                EXCEPTION
                WHEN OTHERS THEN
                raise_application_error(-20000,
                TO_CHAR(SQLCODE) || '-' || SQLERRM);
                ROLLBACK;
                END;
            end if;
            COMMIT;
        end if;
        End Loop;
    end if;
END; 

 

Compartilhar este post


Link para o post
Compartilhar em outros sites

×

Informação importante

Ao usar o fórum, você concorda com nossos Termos e condições.