Quellcode Prozedur b_r

Das DB-Objekt selbst

CREATE PROCEDURE "demoadm".b_r_pkprop()

  DEFINE dw_oper varchar(50,0);
  DEFINE err_sql int;
  DEFINE err_id  int;
  DEFINE err_isam int;
  DEFINE err_txt varchar(150,0);
  DEFINE err_msg varchar(250,0);
  DEFINE i  int;
  DEFINE sv_ktr_nr  char(15);
  DEFINE sv_kart_nr char(15);
  DEFINE sv_mandant     char(2);
  DEFINE sv_jahr decimal(4,0);
  DEFINE sv_periode decimal(2,0);
  DEFINE sv_betrag_plan decimal(13,2);
  DEFINE pv_action char(1);
  DEFINE pv_mandant char(2);
  DEFINE pv_ktr  char(15);       -- prop_kost.ktr
  DEFINE pv_kart  char(15);       -- prop_kost.kart
  DEFINE pv_gjahr  decimal(2,0);   -- prop_kost.gjahr
  DEFINE pv_periode  decimal(2,0); -- 1..12
  DEFINE pv_kosten  decimal(13,2);  -- prop_kost.kosten1..12

  LET dw_oper = ' Start';
  LET err_id = 0;
  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|| ' #'
  ||dw_oper;
    SYSTEM 'echo "B_r_pkprop: '||err_msg||'" >> /var/tmp/log.dwb';

    END EXCEPTION;

--%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%

    FOREACH dw_curs1 FOR SELECT
    l.er_id,
    l.lv_action,
    l.lv_mandant,
    l.lv_ktr,
    l.lv_kart,
    l.lv_gjahr,
    l.lv_periode,
    l.lv_kosten
    INTO
    err_id,
    pv_action,
    pv_mandant,
    pv_ktr,
    pv_kart,
    pv_gjahr,
    pv_periode,
    pv_kosten
    FROM DWE_r_pkprop l
    ON EXCEPTION
      UPDATE DWE_r_pkprop SET er_retry = er_retry + 1 WHERE er_id = err_id ;
    END EXCEPTION;

--%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%

    LET dw_oper = ' Computing variables';

    LET sv_ktr_nr = pv_ktr;
    LET sv_kart_nr = pv_kart;
    LET sv_mandant = pv_mandant;
    LET sv_jahr  = 1900 + pv_gjahr;
    LET sv_periode = pv_periode;

    LET sv_betrag_plan = pv_kosten;

    -- Negate Values --
    IF pv_action = 'D' THEN
    LET sv_betrag_plan = -sv_betrag_plan;
    END IF

    LET dw_oper = 'Updating sr_traegerart';
    UPDATE sr_traegerart SET
    betrag_plan = betrag_plan + sv_betrag_plan
    WHERE
    (ktr_nr        = sv_ktr_nr)  AND
    (kart_nr       = sv_kart_nr) AND
    (mandant       = sv_mandant) AND
    (jahr          = sv_jahr)    AND
    (periode       = sv_periode)
    ;
    IF DBINFO('sqlca.sqlerrd2') < 1 THEN -- No rows updated
    INSERT INTO sr_traegerart (
      ktr_nr,
      kart_nr,
      mandant,
      jahr,
      periode,
      betrag_plan
    )
    VALUES (
      sv_ktr_nr,
      sv_kart_nr,
      sv_mandant,
      sv_jahr,
      sv_periode,
      sv_betrag_plan
    );
    END IF

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

Das DB-Objekt selbst

Abhängige Objekte, Rechte
