Quellcode Prozedur b

Das DB-Objekt selbst

CREATE PROCEDURE "demoadm".b_perskto()
  DEFINE err_id  int;
  DEFINE err_sql int;
  DEFINE err_isam int;
  DEFINE err_txt varchar(150,0);
  DEFINE err_msg varchar(250,0);
  DEFINE i  int;
  DEFINE pv_action char(1);
  DEFINE pv_mandant char(2);
  DEFINE pv_pkto  char(15);
  DEFINE pv_jahr decimal(4,0);
  DEFINE pv_periode decimal(2,0);
  DEFINE pv_soll decimal(13,2);
  DEFINE pv_haben decimal(13,2);

  BEGIN WORK;
  SET LOCK MODE TO WAIT 10;
  BEGIN
    ON EXCEPTION
    SET err_sql, err_isam, err_txt
    LET err_msg = 'ERR['||err_sql||','||err_isam||','||err_id||']:'||err_txt;
    SYSTEM 'echo "B_perskto: '||err_msg||'" >> /var/tmp/log.dwb';
    END EXCEPTION;
    FOREACH dw_curs1 FOR SELECT
    er_id,
    lv_action,
    lv_mandant,
    lv_pkto,
    lv_jahr,
    lv_periode,
    lv_soll,
    lv_haben
    INTO
    err_id,
    pv_action,
    pv_mandant,
    pv_pkto,
    pv_jahr,
    pv_periode,
    pv_soll,
    pv_haben
    FROM DWE_perskto l
    ON EXCEPTION
      UPDATE DWE_perskto SET er_retry = er_retry + 1 WHERE er_id = err_id ;
    END EXCEPTION;

    IF (pv_action = 'I') THEN

      INSERT INTO sr_sh_pkto (
      mandant,
      pkto_nr,
      jahr,
      periode,
      soll,
    haben
      )
      VALUES (
      pv_mandant,
      pv_pkto,
      pv_jahr,
      pv_periode,
      pv_soll,
      pv_haben
      );

    ELIF (pv_action = 'D') AND (pv_periode = 99) THEN

      DELETE FROM sr_sh_pkto
      WHERE (pkto_nr = pv_pkto)
      AND (mandant = pv_mandant)
      AND ((jahr = pv_jahr) OR (jahr = pv_jahr-1))
      ;

    ELIF ((pv_action = 'D') AND (pv_periode < 20))
      OR ((pv_soll = 0) AND (pv_haben = 0) AND (pv_periode <> 0))
    THEN

      DELETE FROM sr_sh_pkto
      WHERE (pkto_nr = pv_pkto)
      AND (mandant = pv_mandant)
      AND (jahr    = pv_jahr)
      AND (periode = pv_periode)
      ;

    ELSE -- it must be update

      UPDATE sr_sh_pkto SET
      soll = pv_soll,
      haben = pv_haben
      WHERE (pkto_nr = pv_pkto)
      AND (mandant = pv_mandant)
      AND (jahr = pv_jahr)
      AND (periode = pv_periode)
      ;
      IF DBINFO('sqlca.sqlerrd2') < 1 THEN -- No rows updated
      INSERT INTO sr_sh_pkto (
        mandant,
        pkto_nr,
        jahr,
        periode,
        soll,
    haben
      )
      VALUES (
        pv_mandant,
        pv_pkto,
        pv_jahr,
        pv_periode,
        pv_soll,
        pv_haben
      );
      END IF

    END IF

    DELETE FROM DWE_perskto WHERE CURRENT OF dw_curs1;
    END FOREACH
  END
  SET LOCK MODE TO NOT WAIT;
  COMMIT WORK;
END PROCEDURE
Quellcode Prozedur b_perskto

Das DB-Objekt selbst

Abhängige Objekte, Rechte
