Trigger Postgres

Trigger Postgres

-- **************************************************************************
-- gcommovh_upd
-- DEISTER WebStudio XSQL-TRIGGER Mon Oct 10 18:58:30 CEST 2011 Engine: postgres
-- **************************************************************************
CREATE OR REPLACE FUNCTION func_gcommovh_upd() RETURNS TRIGGER AS $$

DECLARE
    N65620 integer;
    N65696 integer;
    N65705 integer;
    N65736 integer;
    N65745 integer;
    N66048 integer;
    N66090 integer;
    N66220 integer;
    N66253 integer;
    N66333 integer;
    N66399 integer;
    m_void	CHAR(1);
BEGIN


   -- Include [before] for trigger [gcommovh_upd] must be resolved at runtime

    IF (OLD.tipdoc != NEW.tipdoc AND
                NEW.movhis != -1
           ) THEN
     m_void := sdm_raise_msg(0, 'No se puede modificar ciertos datos claves del movimiento');

    END IF;

    IF (
               (OLD.almori != NEW.almori                       OR
                OLD.almdes != NEW.almdes                       OR
               (OLD.almdes IS NULL AND NEW.almdes IS NOT NULL) OR
               (OLD.almdes IS NOT NULL AND NEW.almdes IS NULL) OR
                COALESCE(OLD.docori, '') != COALESCE(NEW.docori, '')) AND
                NEW.movhis != -1
            ) THEN

-- Variable ID: N65620
     
                    SELECT COUNT(*)
                     INTO N65620
                      FROM gcommovl
                     WHERE cabid = NEW.cabid;
    IF (N65620 > 0
            ) THEN
     m_void := sdm_raise_msg(0, 'No se puede modificar ciertos datos si hay líneas');

    END IF;

    END IF;

    IF (LENGTH(NEW.docori) > 0 AND
               (OLD.delega != NEW.delega OR OLD.tercer != NEW.tercer) AND
                NEW.movhis != -1
           ) THEN
     m_void := sdm_raise_msg(0, 'Mov. con documento origen, ciertos datos no se pueden modificar');

    END IF;

    IF (
               (OLD.delega != NEW.delega  OR
                OLD.tercer != NEW.tercer  OR
                OLD.terenv != NEW.terenv  OR
                OLD.divisa != NEW.divisa) AND
                NEW.movhis != -1
           ) THEN

-- Variable ID: N65696
     
                  SELECT COUNT(*)
                   INTO N65696
                    FROM gcommovd
                   WHERE codigo = NEW.tipdoc
                     AND tabdes = 'FC'
               ;
     
                   SELECT COUNT(*)
                    INTO N65705
                     FROM gcommovl
                    WHERE cabid = NEW.cabid
                      AND estlin NOT IN ('E', 'V')
               ;
    IF (N65696 = 0 AND
               N65705 > 0
           ) THEN
     m_void := sdm_raise_msg(0, 'Mov. con lineas procesadas, ciertos datos no se pueden modificar');

    END IF;

    END IF;

-- Variable ID: N65736
     
                    SELECT COUNT(*)
                     INTO N65736
                      FROM gcommovd, galmctas a, galmctas b
                     WHERE gcommovd.codigo = NEW.tipdoc
                       AND gcommovd.ctaori = a.codigo
                       AND gcommovd.ctades = b.codigo
                       AND (a.indsal != 'N' OR b.indsal != 'N')
                ;
     
                    SELECT COUNT(*)
                     INTO N65745
                      FROM galmacen
                     WHERE (codigo = NEW.almori OR  codigo = NEW.almdes)
                       AND fecval IS NOT NULL
                       AND (fecval >= NEW.fecmov
                        OR  fecval >= OLD.fecmov)
                ;
    IF (NEW.fecmov != OLD.fecmov AND
                NEW.movhis != -1 AND
                N65736 > 0 AND
                N65745 > 0
            ) THEN
     m_void := sdm_raise_msg(0, 'Fecha movimiento anterior a la del ultimo cierre del almacen orig/dest');

    END IF;

    IF (NEW.impnxt = 1 AND
                (COALESCE(OLD.conten,'') != COALESCE(NEW.conten,'') OR
                 COALESCE(OLD.dosier,'') != COALESCE(NEW.dosier,'') OR
                 COALESCE(OLD.destra,'') != COALESCE(NEW.destra,'') OR
                 COALESCE(OLD.desadu,'') != COALESCE(NEW.desadu,''))
            ) THEN
     m_void := sdm_raise_msg(0, 'Movimiento [' || TRIM(NEW.docser) || '] evolucionado. No se puede cambiar datos de importaciones');

    END IF;

    IF (OLD.movest  = 1                 AND
                OLD.movest  = NEW.movest AND
                OLD.movhis  = NEW.movhis AND
                NEW.movhis != -1
            ) THEN
     SELECT * FROM sdm_set_value('0') INTO  NEW.movest;

    END IF;

    IF (OLD.movhis = 1                 AND
                OLD.movhis = NEW.movhis AND
               (OLD.movest = NEW.movest OR OLD.movest = 1 AND NEW.movest = 0)
            ) THEN
     SELECT * FROM sdm_set_value('0') INTO  NEW.movhis;

    END IF;

    IF (OLD.movest != NEW.movest AND
                NEW.movest  = 0 AND
                NEW.movhis != -1
            ) THEN
     INSERT INTO gdoc_traspaso (tabname, colname, colval, tipope, tippro)
       VALUES ('gcommovh', 'cabid', NEW.cabid, 1, 0);


    END IF;

    IF (OLD.movhis != NEW.movhis AND
                NEW.movhis  = 0 AND
                OLD.movhis != -1
            ) THEN
     INSERT INTO gdoc_traspaso (tabname, colname, colval, tipope, tippro)
       VALUES ('gcommovh', 'cabid', NEW.cabid, 1, 1);


    END IF;

    IF (OLD.movest != NEW.movest AND
                NEW.movest  = 1 AND
                NEW.movhis != -1
            ) THEN
     DELETE FROM gdoc_traspaso WHERE 
                        tabname = 'gcommovh'       AND
                        colname = 'cabid'          AND
                        colval  = NEW.cabid AND
                        tippro  = 0
                     ;


    END IF;

    IF (OLD.movhis != NEW.movhis AND
                NEW.movhis  = 1 AND
                OLD.movhis != -1
            ) THEN
     DELETE FROM gdoc_traspaso WHERE 
                        tabname = 'gcommovh'        AND
                        colname = 'cabid'           AND
                        colval  = NEW.cabid  AND
                        tippro  = 1
                     ;


    END IF;

-- Variable ID: N66048
     
                    SELECT COUNT(*)
                     INTO N66048
                      FROM gcommovd
                     WHERE codigo = OLD.tipdoc
                       AND stkpla IN (1, 2, 3, 4)
                 ;
    IF (
               ((OLD.estcab  = 'E' AND NEW.estcab != 'E') OR
                (OLD.estcab != 'E' AND NEW.estcab  = 'E') OR
                 OLD.fecrec != NEW.fecrec) AND
                 NEW.movhis != -1 AND
                 N66048 > 0
            ) THEN
     UPDATE gpro_stkplanm
        SET estado=CASE WHEN NEW.estcab = 'E' THEN 'E' ELSE 'V' END,
            fecmov=NEW.fecrec
      WHERE 
                        tabmov = 'gcommovh' AND
                        docser = OLD.docser;


    END IF;

-- Variable ID: N66090
     
                    SELECT COUNT(*)
                     INTO N66090
                      FROM gcommovd
                     WHERE codigo = OLD.tipdoc
                       AND stkpla IN (1, 2, 3, 4)
                 ;
    IF (OLD.fecrec < NEW.fecrec AND
                 NEW.movhis != -1 AND
                 N66090 > 0
            ) THEN
     UPDATE greserva
        SET estlin='A'
      WHERE 
                        tabres    = 'gvenpedh'        AND
                        tabapr    = 'gcommovh'        AND
                        docapr    = NEW.docser AND
                        fecent < NEW.fecrec AND
                        estlin   != 'A'               AND
                        canpro   != 0
                    ;


    END IF;

    IF (NEW.fecmov != OLD.fecmov AND
                NEW.movhis != -1
            ) THEN
     UPDATE gcommovl
        SET cosmed=0
      WHERE 
                        cabid   = NEW.cabid AND
                        cosmed != 0
                    ;


    END IF;

    IF (
               (OLD.tercer != NEW.tercer OR
                OLD.delega != NEW.delega OR
                OLD.terenv != NEW.terenv OR
                OLD.divisa != NEW.divisa) AND
                NEW.movhis != -1
            ) THEN
     DELETE FROM gcommovh_dtcl WHERE cabid = NEW.cabid;

     DELETE FROM gcommovl_dtlh WHERE cabid = NEW.cabid;

     UPDATE gcommovl
        SET precio=0,
            dtolin=0,
            pretar=NULL,
            dtotar=NULL
      WHERE cabid  = NEW.cabid;


    END IF;

-- Variable ID: N66220
     
                   SELECT COUNT(*)
                    INTO N66220
                     FROM gcommovd
                    WHERE codigo  = OLD.tipdoc
                      AND cominv != 'N'
                      AND aplcon != 0
               ;
    IF (OLD.fconta IS NULL     AND
               NEW.fconta IS NOT NULL AND
               NEW.movhis != -1 AND
               N66220 > 0
           ) THEN
     DELETE FROM cpar_premovi WHERE 
                      tabori = 'gcomalbh'        AND
                      docser = OLD.docser AND
                      estado = 'C'
                  ;


    END IF;

-- Variable ID: N66253
     
                    SELECT COUNT(*)
                     INTO N66253
                      FROM gcommovd
                     WHERE codigo = OLD.tipdoc
                       AND cominv = 'S'
                ;
    IF (OLD.estcab  = 'V' AND
                NEW.estcab  = 'E' AND
                NEW.movhis != -1  AND
                N66253 > 0
            ) THEN
     DELETE FROM cpar_premovi WHERE 
                        tabori = 'gcomalbh' AND
                        docser = OLD.docser;


    END IF;

    IF (OLD.estcab != NEW.estcab                 AND
               (OLD.estcab  = 'P' AND NEW.estcab != 'V') AND
               (OLD.estcab != 'E' AND OLD.estcab != 'V') AND
               (NEW.estcab  = 'E'  OR NEW.estcab  = 'V') AND
                NEW.movhis != -1
            ) THEN
     m_void := sdm_raise_msg(0, 'Cabecera de transaccion cursada. No se puede alterar');

    END IF;

-- Variable ID: N66333
     
                    SELECT COUNT(*)
                     INTO N66333
                      FROM gcommovd
                     WHERE codigo = NEW.tipdoc
                       AND tabori = 'MC'
                       AND actori = 'S'
                ;
    IF (OLD.dtogen != NEW.dtogen AND
                NEW.docori IS NOT NULL          AND
                NEW.movhis != -1 AND
                N66333 > 0
            ) THEN
     UPDATE gcomacuh
        SET imptot= imptot +
                         (SELECT COALESCE(SUM(impnet), 0) * (NEW.dtogen - OLD.dtogen) / 100
                            FROM gcommovl
                           WHERE cabid = NEW.cabid),
            user_updated=USER,
            date_updated=CURRENT_TIMESTAMP
      WHERE 
                        docser = NEW.docori;


    END IF;

-- Variable ID: N66399
     
                    SELECT COUNT(*)
                     INTO N66399
                      FROM gcommovd
                     WHERE codigo = NEW.tipdoc
                       AND tabdes = 'FC'
                 ;
    IF (
                (OLD.dtogen != NEW.dtogen OR
                 OLD.fecmov != NEW.fecmov OR
                 OLD.dtopp  != NEW.dtopp) AND
                 NEW.movhis != -1 AND
                 N66399 > 0
            ) THEN
     UPDATE gcomfach
        SET dtogen=NEW.dtogen,
            dtopp=NEW.dtopp,
            fecha=NEW.fecmov,
            valor=NEW.fecmov,
            user_updated=USER,
            date_updated=CURRENT_TIMESTAMP
      WHERE 
                        cabori = NEW.cabid;


    END IF;

    IF (OLD.tipdoc != NEW.tipdoc AND
                NEW.movhis != -1
            ) THEN
     m_void := cerrauth_anular('gcommovh', 'tipdoc', NEW.cabid, 0);

    END IF;

    IF (OLD.delega != NEW.delega AND
                NEW.movhis != -1
            ) THEN
     m_void := cerrauth_anular('gcommovh', 'delega', NEW.cabid, 0);

    END IF;

    IF (OLD.depart != NEW.depart AND
                NEW.movhis != -1
            ) THEN
     m_void := cerrauth_anular('gcommovh', 'depart', NEW.cabid, 0);

    END IF;

    IF (OLD.almori != NEW.almori AND
                NEW.movhis != -1
            ) THEN
     m_void := cerrauth_anular('gcommovh', 'almori', NEW.cabid, 0);

    END IF;

    IF (OLD.almdes != NEW.almdes AND
                NEW.movhis != -1
            ) THEN
     m_void := cerrauth_anular('gcommovh', 'almdes', NEW.cabid, 0);

    END IF;

    IF (OLD.refter != NEW.refter AND
                NEW.movhis != -1
            ) THEN
     m_void := cerrauth_anular('gcommovh', 'refter', NEW.cabid, 0);

    END IF;

    IF (OLD.tipefe != NEW.tipefe AND
                NEW.movhis != -1
            ) THEN
     m_void := cerrauth_anular('gcommovh', 'tipefe', NEW.cabid, 0);

    END IF;

    IF (OLD.frmpag != NEW.frmpag AND
                NEW.movhis != -1
            ) THEN
     m_void := cerrauth_anular('gcommovh', 'frmpag', NEW.cabid, 0);

    END IF;

    IF (OLD.imptot != NEW.imptot AND
                NEW.movhis != -1
            ) THEN
     m_void := cerrauth_anular('gcommovh', 'imptot', NEW.cabid, 0);

    END IF;

    IF (OLD.tercer != NEW.tercer AND
                NEW.movhis != -1
            ) THEN
     m_void := cerrauth_anular('gcommovh', 'tercer', NEW.cabid, 0);

    END IF;

    IF (OLD.tipdir != NEW.tipdir AND
                NEW.movhis != -1
            ) THEN
     m_void := cerrauth_anular('gcommovh', 'tipdir', NEW.cabid, 0);

    END IF;

    IF (OLD.terfac != NEW.terfac AND
                NEW.movhis != -1
            ) THEN
     m_void := cerrauth_anular('gcommovh', 'terfac', NEW.cabid, 0);

    END IF;

    IF (OLD.dirfac != NEW.dirfac AND
                NEW.movhis != -1
            ) THEN
     m_void := cerrauth_anular('gcommovh', 'dirfac', NEW.cabid, 0);

    END IF;

    IF (OLD.divisa != NEW.divisa AND
                NEW.movhis != -1
            ) THEN
     m_void := cerrauth_anular('gcommovh', 'divisa', NEW.cabid, 0);

    END IF;

    IF (OLD.dtogen != NEW.dtogen AND
                NEW.movhis != -1
            ) THEN
     m_void := cerrauth_anular('gcommovh', 'dtogen', NEW.cabid, 0);

    END IF;

    IF (OLD.dtopp  != NEW.dtopp AND
                NEW.movhis != -1
            ) THEN
     m_void := cerrauth_anular('gcommovh', 'dtopp', NEW.cabid, 0);

    END IF;

    IF (COALESCE(OLD.conten, '') != COALESCE(NEW.conten, '') AND
                NEW.movhis != -1
            ) THEN
     m_void := cerrauth_anular('gcommovh', 'conten', NEW.cabid, 0);

    END IF;

    IF (OLD.impres != NEW.impres AND
                NEW.movhis != -1
            ) THEN
     m_void := cerrauth_anular('gcommovh', 'impres', NEW.cabid, 0);

    END IF;

    IF (NEW.movhis != -1) THEN
     m_void := csyn_document_inserta('gcommovh', NEW.tipdoc, NEW.docser);

    END IF;

    IF (OLD.impres != NEW.impres AND
                NEW.impres  = 'S' AND
                NEW.movhis != -1
            ) THEN
     INSERT INTO csyn_document (tabid, docser, accion, progname, proces) 
           SELECT 
                            tabid, NEW.docser, 2, progname, 0
                        
        FROM csyn_tablas
       WHERE 
                            tabid = 'gcommovh' AND
                            COALESCE(tipdoc, NEW.tipdoc) = NEW.tipdoc AND
                            indnot = 2
                        
;


    END IF;

   -- Include [after] for trigger [gcommovh_upd] must be resolved at runtime

 RETURN NEW;

END;
$$ LANGUAGE 'plpgsql';


CREATE TRIGGER gcommovh_upd
AFTER UPDATE  ON gcommovh
FOR EACH ROW
EXECUTE PROCEDURE func_gcommovh_upd();