Trigger Db2i

Trigger Db2i

-- **************************************************************************
-- gcommovh_upd
-- DEISTER WebStudio XSQL-TRIGGER Mon Oct 10 18:58:30 CEST 2011 Engine: db2i
-- **************************************************************************
CREATE TRIGGER gcommovh_upd
BEFORE UPDATE ON gcommovh
REFERENCING NEW AS nxt OLD AS prv
FOR EACH ROW MODE DB2ROW
BEGIN ATOMIC

       DECLARE m_var_N65832 varchar(20);
       DECLARE m_nxt_cabid integer;
       DECLARE m_nxt_tipdoc varchar(4);
       DECLARE m_nxt_docser varchar(20);

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

       IF prv.tipdoc != nxt.tipdoc AND
                nxt.movhis != -1
            THEN
 THEN
     CALL sdm_raise_msg(0, 'No se puede modificar ciertos datos claves del movimiento');


    END IF;

       IF 
               (prv.almori != nxt.almori                       OR
                prv.almdes != nxt.almdes                       OR
               (prv.almdes IS NULL AND nxt.almdes IS NOT NULL) OR
               (prv.almdes IS NOT NULL AND nxt.almdes IS NULL) OR
                COALESCE(prv.docori, '') != COALESCE(nxt.docori, '')) AND
                nxt.movhis != -1
             THEN
    IF (
                    SELECT COUNT(*)
                    
                      FROM gcommovl
                     WHERE cabid = nxt.cabid) > 0
             THEN
 THEN
     CALL sdm_raise_msg(0, 'No se puede modificar ciertos datos si hay líneas');


    END IF;

    END IF;

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


    END IF;

       IF 
               (prv.delega != nxt.delega  OR
                prv.tercer != nxt.tercer  OR
                prv.terenv != nxt.terenv  OR
                prv.divisa != nxt.divisa) AND
                nxt.movhis != -1
            THEN
    IF (
                  SELECT COUNT(*)
                  
                    FROM gcommovd
                   WHERE codigo = nxt.tipdoc
                     AND tabdes = 'FC'
               ) = 0 AND
               (
                   SELECT COUNT(*)
                   
                     FROM gcommovl
                    WHERE cabid = nxt.cabid
                      AND estlin NOT IN ('E', 'V')
               ) > 0
            THEN
 THEN
     CALL sdm_raise_msg(0, 'Mov. con lineas procesadas, ciertos datos no se pueden modificar');


    END IF;

    END IF;

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


    END IF;

       IF nxt.impnxt = 1 AND
                (COALESCE(prv.conten,'') != COALESCE(nxt.conten,'') OR
                 COALESCE(prv.dosier,'') != COALESCE(nxt.dosier,'') OR
                 COALESCE(prv.destra,'') != COALESCE(nxt.destra,'') OR
                 COALESCE(prv.desadu,'') != COALESCE(nxt.desadu,''))
             THEN
 THEN
       SET m_var_N65832 = 'Movimiento [' || RTRIM(nxt.docser) || '] evolucionado. No se puede cambiar datos de importaciones';
     CALL sdm_raise_msg(0, m_var_N65832);


    END IF;

       IF prv.movest  = 1                 AND
                prv.movest  = nxt.movest AND
                prv.movhis  = nxt.movhis AND
                nxt.movhis != -1
             THEN
 THEN
     SET nxt.movest = sdm_set_value('0');


    END IF;

       IF prv.movhis = 1                 AND
                prv.movhis = nxt.movhis AND
               (prv.movest = nxt.movest OR prv.movest = 1 AND nxt.movest = 0)
             THEN
 THEN
     SET nxt.movhis = sdm_set_value('0');


    END IF;

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



    END IF;

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



    END IF;

       IF prv.movest != nxt.movest AND
                nxt.movest  = 1 AND
                nxt.movhis != -1
             THEN
 THEN
     DELETE FROM gdoc_traspaso WHERE 
                        tabname = 'gcommovh'       AND
                        colname = 'cabid'          AND
                        colval  = nxt.cabid AND
                        tippro  = 0
                     ;



    END IF;

       IF prv.movhis != nxt.movhis AND
                nxt.movhis  = 1 AND
                prv.movhis != -1
             THEN
 THEN
     DELETE FROM gdoc_traspaso WHERE 
                        tabname = 'gcommovh'        AND
                        colname = 'cabid'           AND
                        colval  = nxt.cabid  AND
                        tippro  = 1
                     ;



    END IF;

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



    END IF;

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



    END IF;

       IF nxt.fecmov != prv.fecmov AND
                nxt.movhis != -1
             THEN
 THEN
     UPDATE gcommovl
        SET cosmed=0
      WHERE 
                        cabid   = nxt.cabid AND
                        cosmed != 0
                    ;



    END IF;

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

     DELETE FROM gcommovl_dtlh WHERE cabid = nxt.cabid;

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



    END IF;

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



    END IF;

       IF prv.estcab  = 'V' AND
                nxt.estcab  = 'E' AND
                nxt.movhis != -1  AND
                (
                    SELECT COUNT(*)
                    
                      FROM gcommovd
                     WHERE codigo = prv.tipdoc
                       AND cominv = 'S'
                ) > 0
             THEN
 THEN
     DELETE FROM cpar_premovi WHERE 
                        tabori = 'gcomalbh' AND
                        docser = prv.docser;



    END IF;

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


    END IF;

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



    END IF;

       IF 
                (prv.dtogen != nxt.dtogen OR
                 prv.fecmov != nxt.fecmov OR
                 prv.dtopp  != nxt.dtopp) AND
                 nxt.movhis != -1 AND
                 (
                    SELECT COUNT(*)
                    
                      FROM gcommovd
                     WHERE codigo = nxt.tipdoc
                       AND tabdes = 'FC'
                 ) > 0
             THEN
 THEN
     UPDATE gcomfach
        SET dtogen=nxt.dtogen,
            dtopp=nxt.dtopp,
            fecha=nxt.fecmov,
            valor=nxt.fecmov,
            user_updated=USER,
            date_updated=CURRENT TIMESTAMP
      WHERE 
                        cabori = nxt.cabid;



    END IF;

       IF prv.tipdoc != nxt.tipdoc AND
                nxt.movhis != -1
             THEN
 THEN
        SET m_nxt_cabid = nxt.cabid;
     CALL cerrauth_anular('gcommovh', 'tipdoc', m_nxt_cabid, 0);


    END IF;

       IF prv.delega != nxt.delega AND
                nxt.movhis != -1
             THEN
 THEN
        SET m_nxt_cabid = nxt.cabid;
     CALL cerrauth_anular('gcommovh', 'delega', m_nxt_cabid, 0);


    END IF;

       IF prv.depart != nxt.depart AND
                nxt.movhis != -1
             THEN
 THEN
        SET m_nxt_cabid = nxt.cabid;
     CALL cerrauth_anular('gcommovh', 'depart', m_nxt_cabid, 0);


    END IF;

       IF prv.almori != nxt.almori AND
                nxt.movhis != -1
             THEN
 THEN
        SET m_nxt_cabid = nxt.cabid;
     CALL cerrauth_anular('gcommovh', 'almori', m_nxt_cabid, 0);


    END IF;

       IF prv.almdes != nxt.almdes AND
                nxt.movhis != -1
             THEN
 THEN
        SET m_nxt_cabid = nxt.cabid;
     CALL cerrauth_anular('gcommovh', 'almdes', m_nxt_cabid, 0);


    END IF;

       IF prv.refter != nxt.refter AND
                nxt.movhis != -1
             THEN
 THEN
        SET m_nxt_cabid = nxt.cabid;
     CALL cerrauth_anular('gcommovh', 'refter', m_nxt_cabid, 0);


    END IF;

       IF prv.tipefe != nxt.tipefe AND
                nxt.movhis != -1
             THEN
 THEN
        SET m_nxt_cabid = nxt.cabid;
     CALL cerrauth_anular('gcommovh', 'tipefe', m_nxt_cabid, 0);


    END IF;

       IF prv.frmpag != nxt.frmpag AND
                nxt.movhis != -1
             THEN
 THEN
        SET m_nxt_cabid = nxt.cabid;
     CALL cerrauth_anular('gcommovh', 'frmpag', m_nxt_cabid, 0);


    END IF;

       IF prv.imptot != nxt.imptot AND
                nxt.movhis != -1
             THEN
 THEN
        SET m_nxt_cabid = nxt.cabid;
     CALL cerrauth_anular('gcommovh', 'imptot', m_nxt_cabid, 0);


    END IF;

       IF prv.tercer != nxt.tercer AND
                nxt.movhis != -1
             THEN
 THEN
        SET m_nxt_cabid = nxt.cabid;
     CALL cerrauth_anular('gcommovh', 'tercer', m_nxt_cabid, 0);


    END IF;

       IF prv.tipdir != nxt.tipdir AND
                nxt.movhis != -1
             THEN
 THEN
        SET m_nxt_cabid = nxt.cabid;
     CALL cerrauth_anular('gcommovh', 'tipdir', m_nxt_cabid, 0);


    END IF;

       IF prv.terfac != nxt.terfac AND
                nxt.movhis != -1
             THEN
 THEN
        SET m_nxt_cabid = nxt.cabid;
     CALL cerrauth_anular('gcommovh', 'terfac', m_nxt_cabid, 0);


    END IF;

       IF prv.dirfac != nxt.dirfac AND
                nxt.movhis != -1
             THEN
 THEN
        SET m_nxt_cabid = nxt.cabid;
     CALL cerrauth_anular('gcommovh', 'dirfac', m_nxt_cabid, 0);


    END IF;

       IF prv.divisa != nxt.divisa AND
                nxt.movhis != -1
             THEN
 THEN
        SET m_nxt_cabid = nxt.cabid;
     CALL cerrauth_anular('gcommovh', 'divisa', m_nxt_cabid, 0);


    END IF;

       IF prv.dtogen != nxt.dtogen AND
                nxt.movhis != -1
             THEN
 THEN
        SET m_nxt_cabid = nxt.cabid;
     CALL cerrauth_anular('gcommovh', 'dtogen', m_nxt_cabid, 0);


    END IF;

       IF prv.dtopp  != nxt.dtopp AND
                nxt.movhis != -1
             THEN
 THEN
        SET m_nxt_cabid = nxt.cabid;
     CALL cerrauth_anular('gcommovh', 'dtopp', m_nxt_cabid, 0);


    END IF;

       IF COALESCE(prv.conten, '') != COALESCE(nxt.conten, '') AND
                nxt.movhis != -1
             THEN
 THEN
        SET m_nxt_cabid = nxt.cabid;
     CALL cerrauth_anular('gcommovh', 'conten', m_nxt_cabid, 0);


    END IF;

       IF prv.impres != nxt.impres AND
                nxt.movhis != -1
             THEN
 THEN
        SET m_nxt_cabid = nxt.cabid;
     CALL cerrauth_anular('gcommovh', 'impres', m_nxt_cabid, 0);


    END IF;

       IF nxt.movhis != -1 THEN
 THEN
        SET m_nxt_tipdoc = nxt.tipdoc;
        SET m_nxt_docser = nxt.docser;
     CALL csyn_document_inserta('gcommovh', m_nxt_tipdoc, m_nxt_docser);


    END IF;

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



    END IF;

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

END;