[Resolvido] Erro Funcao PGSQL

Rodrigovf · Janeiro 20, 2009

Bom pessoal sou novo aki no forum e novo tbm no pgsql...

Mas no meu trabalho me passaram para fazer uma trigger...

Como estou testando, nao estou testando como trigger...

Segue o script abaixo:

CREATE OR REPLACE FUNCTION SINTETIZA_SQL()

-- RETURNS TRIGGER AS

RETURNS VOID AS

$$

DECLARE

D02 RECORD;

A17 RECORD;

G01 RECORD;

D44 RECORD;

B23 RECORD;

B05 RECORD;

B10 RECORD;

B01 RECORD;

nMULT NUMERIC;

nMULTDEV NUMERIC;

nCUSTOST NUMERIC;

nMARGEM NUMERIC;

nMARGEMC NUMERIC;

nCOMISSAO NUMERIC;

nDESPESA NUMERIC;

nDESP_LJ NUMERIC;

nYXPERDES NUMERIC;

nREVERTE NUMERIC;

nTMP_COD NUMERIC;

nALIQUOTA NUMERIC;

cCOMPRADOR VARCHAR;

cSETOR VARCHAR;

cANOMES VARCHAR;

cCOMP_SET VARCHAR;

NEW RECORD;

OLD RECORD;

TG_OP VARCHAR;

BEGIN

SELECT INTO NEW * FROM FTB02 WHERE FTB02.cdemp = '51' AND

FTB02.nrped = '0172970' AND

FTB02.cdexp = '10.1010.00010' AND

sr_deleted = '';

SELECT INTO OLD * FROM FTB02 WHERE FTB02.cdemp = '51' AND

FTB02.nrped = '0172970' AND

FTB02.cdexp = '10.1010.00010' AND

sr_deleted = '';

TG_OP := 'UPDATE';

IF TG_OP = 'DELETE' THEN

IF OLD.cdope NOT IN ('001','002','500') THEN

-- RETURN OLD;

ELSE

IF NEW.cdope NOT IN ('001','002','500') THEN

-- RETURN NEW;

END IF;

IF OLD.controle IN ('41','42','49') AND

NEW.controle NOT IN ('41','42','49') THEN

nREVERTE := -1;

ELSE

nREVERTE := 1;

END IF;

IF ( ( OLD.controle IN ('41','42','49') AND NEW.controle IN ('41','42','49') ) OR

OLD.controle = NEW.controle ) AND

nREVERTE = 1 AND

OLD.cdexp = NEW.cdexp AND

OLD.dtmov = NEW.dtmov AND

OLD.cdemp = NEW.cdemp AND

OLD.qtmov = NEW.qtmov AND

OLD.vltot = NEW.vltot AND

OLD.vltot_jr = NEW.vltot_jr AND

OLD.cod_trib = NEW.cod_trib AND

OLD.vlpro = NEW.vlpro AND

OLD.vlpro_jr = NEW.vlpro_jr AND

OLD.alicm_nf = NEW.alicm_nf AND

OLD.prvis = NEW.prvis AND

OLD.vlnot = NEW.vlnot AND

OLD.comis = NEW.comis AND

OLD.vlcus = NEW.vlcus AND

OLD.cstmt = NEW.cstmt THEN

IF TG_OP = 'DELETE' THEN

-- RETURN OLD;

ELSE

-- RETURN NEW;

END IF;

SELECT INTO D02 * FROM CAD02 WHERE CAD02.cdexp = NEW.cdexp AND

sr_deleted = '';

SELECT INTO A17 * FROM FTA17 WHERE FTA17.cdemp = NEW.cdemp AND

sr_deleted = '';

SELECT INTO G01 * FROM CGA01 WHERE CGA01.cdemp = NEW.cdemp AND

sr_deleted = '';

SELECT INTO D44 * FROM CAD44 WHERE CAD44.familia = SUBSTR( NEW.cdexp, 1, 2 ) AND

CAD44.grupo = SUBSTR( NEW.cdexp, 4, 4 ) AND

sr_deleted = '';

SELECT INTO B23 * FROM FTB23 WHERE FTB23.cdemp = NEW.cdemp AND

FTB23.nrped = NEW.nrped AND

FTB23.cdexp = NEW.cdexp AND

sr_deleted = '';

SELECT INTO B01 * FROM FTB01 WHERE FTB01.cdemp = NEW.cdemp AND

FTB01.nrped = NEW.nrped AND

sr_deleted = '';

SELECT INTO B05 * FROM FTB05 WHERE FTB05.cdfil = B01.cdfil AND

FTB05.cdcli = B01.cdcli AND

sr_deleted = '';

SELECT INTO B10 * FROM TAB10 WHERE TAB10.cdope = NEW.cdope AND

sr_deleted = '';

-- Calculo do CustoST

IF NEW.cstmt = 0 OR NEW.cstmt IS NULL THEN

IF NEW.vlcus = 0 OR NEW.vlcus IS NULL THEN

nCUSTOST := NEW.prvis * 0.55 * NEW.qtmov;

ELSE

nCUSTOST := NEW.vlcus * NEW.qtmov;

END IF;

ELSE

nCUSTOST := NEW.cstmt * NEW.qtmov;

END IF;

-- Calculo da Aliquota

IF NEW.cod_trib = 0 OR NEW.cod_trib IS NULL THEN

nTMP_COD := D02.cod_trib;

ELSE

nTMP_COD := NEW.cod_trib;

END IF;

nALIQUOTA := ALIQUOTA_SQL( nTMP_COD, G01.uf, B05.estad, B05.tppes, B10.tipo );

-- Calculo da Comissao

nYXPERDES := ( 100 - ( NEW.vlpro / NEW.prvis * 100 ) );

nCOMISSAO := RET_COM_SQL( D02.situa, SUBSTR( NEW.rapid, 1, 1 ), 'N', D02.comis, NEW.comis, NEW.dtmov,

NEW.cdemp, NEW.cdexp, nYXPERDES );

-- Calculo das Despesas

nDESPESA := DESP_VAR_SQL( NEW.cdemp, NEW.cdexp );

nDESP_LJ := DESP_VAR_SQL( G01.cdemp, '' );

-- Calculo das Margens

nMARGEM := ( B23.mcvl * NEW.qtmov ) - ((( NEW.vlpro * NEW.qtmov ) / 100 ) * NEW.comis );

nMARGEMC := MARGEM_SQL( nDESPESA, nDESP_LJ, 'S', NEW.dtmov, NEW.cdexp, NEW.vltot, NEW.vltot_jr,

NEW.vlcus, NEW.calc_mc, NEW.prvis, NEW.qtmov, D02.ctrep, D02.custo2,

nYXPERDES, nCOMISSAO, nALIQUOTA );

-- Comprador e Setor

cCOMP_SET := COMP_SET_SQL( NEW.cdexp );

cCOMPRADOR := SPLIT_PART( cCOMP_SET, '|', 2 );

cSETOR := SPLIT_PART( cCOMP_SET, '|', 1 );

-- 1ª ESTATISTICA

SINTE_EST01_SQL( OLD.cdexp, OLD.cdemp, '', TG_OP, nCUSTOST, nCOMISSAO, nDESPESA, nMARGEM,