create dba procedure "demoadm".wt_c_psksskini(
pcMandant CHAR(2),
psiWasTun SMALLINT DEFAULT NULL, -- LetDefault 0 verodert:
-- 1= psk clustern, 2= ssk clustern
psiDebugFlag SMALLINT DEFAULT 0,
pcDebugFile CHAR(40) DEFAULT NULL
) RETURNING CHAR(56) ;
DEFINE rcErg CHAR(56);
DEFINE iAnzRoh, iOldPsk, iOldSsk, iPsk, iSsk, iBtk INTEGER;
DEFINE iDBSqlError, iDBIsamError INTEGER;
DEFINE cDBErrMsg CHAR(72);
--
ON EXCEPTION SET iDBSqlError, iDBIsamError, cDBErrMsg
RAISE EXCEPTION iDBSqlError, iDBIsamError, cDBErrMsg;
END EXCEPTION
IF psiDebugFlag > 0 THEN
IF pcDebugFile = '' OR pcDebugFile IS NULL THEN
LET pcDebugFile = '/var/tmp/wt_c_psksskini.trc';
END IF
IF MOD(psiDebugFlag,2) = 1 THEN
SET DEBUG FILE TO pcDebugFile WITH APPEND ;
ELSE SET DEBUG FILE TO pcDebugFile ;
END IF
TRACE ON;
END IF
IF psiWasTun IS NULL THEN LET psiWasTun = 0 ; END IF
IF pcMandant IS NULL OR pcMandant = '' THEN
CALL "informix".n_v_error( 'wt_c_psksskini: Mandant erforderlich!' );
END IF
CALL "informix".nt_v_beginwork();
BEGIN
ON EXCEPTION IN ( -310 ) delete from t_psk ; END EXCEPTION WITH RESUME
create temp table t_psk (
kto integer,
gkto integer,
jahr smallint,
periode smallint,
soll decimal(12,2),
haben decimal(12,2),
anzbu integer
) with no log ;
END
BEGIN
ON EXCEPTION IN ( -310 ) delete from t_haben ; END EXCEPTION WITH RESUME
create temp table t_haben (
kto integer,
gkto integer,
jahr smallint,
periode smallint,
haben decimal(12,2),
anzbu integer
) with no log ;
END
BEGIN
ON EXCEPTION IN ( -310 ) delete from t_sach ; END EXCEPTION WITH RESUME
create temp table t_sach (
kto integer,
gkto integer,
jahr smallint,
periode smallint,
soll decimal(12,2),
haben decimal(12,2),
anzbu integer
) with no log ;
END
IF pcMandant = '01' THEN
LET iAnzRoh = "demoadm".r_ti_aggrjourn01();
ELIF pcMandant = '02' THEN
LET iAnzRoh = "demoadm".r_ti_aggrjourn02();
ELSE
CALL "informix".n_v_error('wt_c_psksskini: Mandant 01/02 erforderlich!');
END IF
CALL "informix".nt_v_commitbegin();
set pdqpriority 20;
-- Intermediaer auf TempTabs
BEGIN
ON EXCEPTION IN ( -316, -350 ) END EXCEPTION WITH RESUME
create unique index i_haben on t_haben ( kto, gkto, jahr, periode );
END
update statistics low for table t_haben;
BEGIN
ON EXCEPTION IN ( -316, -350 ) END EXCEPTION WITH RESUME
create unique index i_psk on t_psk ( kto, gkto, jahr, periode );
END
update statistics low for table t_psk;
-- Haben zu Soll im gleichen Satz ergaenzen
update t_psk set ( haben, anzbu ) = ( ( select W.haben, t_psk.anzbu + W.anzbu
from t_haben W
where t_psk.kto = W.kto and t_psk.gkto = W.gkto
and t_psk.jahr = W.jahr and t_psk.periode = W.periode ) )
where 0 < ( select count(*) from t_haben X
where t_psk.kto = X.kto and t_psk.gkto = X.gkto
and t_psk.jahr = X.jahr and t_psk.periode = X.periode ) ;
delete from t_haben
where 0 < ( select count(*) from t_psk
where t_psk.kto = t_haben.kto and t_psk.gkto = t_haben.gkto
and t_psk.jahr = t_haben.jahr and t_psk.periode = t_haben.periode );
insert into t_psk ( kto, gkto, jahr, periode, soll, haben, anzbu )
select kto, gkto, jahr, periode, 0, haben, anzbu from t_haben ;
drop table t_haben;
update statistics low for table t_psk;
-- Sachkto-Sachkto-Buchungen absondern
insert into t_sach ( kto, gkto, jahr, periode, soll, haben, anzbu )
select kto, gkto, jahr, periode, soll, haben, anzbu from t_psk
where kto <= 999999 and kto <= gkto ;
BEGIN
ON EXCEPTION IN ( -316, -350 ) END EXCEPTION WITH RESUME
create unique index i_sach on t_sach ( kto, gkto, jahr, periode );
END
update statistics low for table t_sach;
delete from t_psk where kto <= 999999 and kto <= gkto ;
-- die umgedrehten Kombinationen zurueckdrehen, zuerst sekundaere
update t_sach set ( soll, haben, anzbu ) = (
( select t_sach.soll + t_psk.haben, t_sach.haben + t_psk.soll,
t_sach.anzbu + t_psk.anzbu
from t_psk
where t_psk.kto = t_sach.gkto and t_psk.gkto = t_sach.kto
and t_psk.jahr = t_sach.jahr and t_psk.periode = t_sach.periode ) )
where 0 < ( select count(*) from t_psk
where t_psk.kto = t_sach.gkto and t_psk.gkto = t_sach.kto
and t_psk.jahr = t_sach.jahr and t_psk.periode = t_sach.periode ) ;
delete from t_psk
where 0 < ( select count(*) from t_sach
where t_sach.kto = t_psk.gkto and t_sach.gkto = t_psk.kto
-- or ( t_sach.kto = t_psk.kto and t_sach.gkto = t_psk.gkto ) -- 0
and t_sach.jahr = t_psk.jahr and t_sach.periode = t_psk.periode ) ;
-- dann primaere ungedrehte Kombinationen
insert into t_sach ( kto, gkto, jahr, periode, soll, haben, anzbu )
select gkto, kto, jahr, periode, haben, soll, anzbu from t_psk -- revers
where kto <= 999999 ;
delete from t_psk where kto <= 999999 ;
set pdqpriority 12;
CALL "informix".nt_v_commitbegin(); -- Jetzt permanente Zieltabellen
lock table "demoadm".perssachkto in exclusive mode;
lock table "demoadm".sachsachkto in exclusive mode;
-- der einfache Weg: alles zum Mandanten loeschen und neu
IF MOD(psiWasTun,2) = 1 THEN
BEGIN
ON EXCEPTION IN ( -319, -623 ) END EXCEPTION WITH RESUME
alter table "demoadm".perssachkto drop constraint "demoadm".psk_con_p;
drop index "demoadm".psk_idx_p;
END
END IF
delete from "demoadm".perssachkto where psk_mandant = pcMandant ;
LET iOldPsk = DBINFO('sqlca.sqlerrd2');
insert into "demoadm".perssachkto ( psk_pkto, psk_skto, psk_jahr,
psk_periode, psk_mandant, psk_soll, psk_haben, psk_anzbu )
select kto, gkto, jahr, periode, pcMandant, soll, haben, anzbu from t_psk;
LET iPsk = DBINFO('sqlca.sqlerrd2');
drop table t_psk;
IF MOD(psiWasTun,2) = 1 THEN
BEGIN
-- ON EXCEPTION IN ( -319, -623 ) END EXCEPTION WITH RESUME
create unique cluster index "demoadm".psk_idx_p on
"demoadm".perssachkto (psk_mandant, psk_jahr, psk_periode,
psk_skto,psk_pkto);
alter table "demoadm".perssachkto add constraint primary key
(psk_mandant, psk_jahr,psk_periode,psk_skto,psk_pkto)
constraint "demoadm".psk_con_p;
END
END IF
delete from "demoadm".sachsachkto where ssk_mandant = pcMandant ;
LET iOldSsk = DBINFO('sqlca.sqlerrd2');
insert into "demoadm".sachsachkto ( ssk_kto, ssk_skto, ssk_jahr, ssk_periode,
ssk_mandant, ssk_soll, ssk_haben, ssk_anzbu )
select kto, gkto, jahr, periode, pcMandant, soll, haben, anzbu
from t_sach ;
LET iSsk = DBINFO('sqlca.sqlerrd2');
set pdqpriority 0;
drop table t_sach;
BEGIN
ON EXCEPTION END EXCEPTION WITH RESUME -- wegen Rechten, sollte gehen
IF MOD(psiWasTun,4) >= 2 THEN
alter index "demoadm".ssk_idx_p to cluster;
END IF
update statistics medium for table "demoadm".sachsachkto
(ssk_soll,ssk_haben,ssk_anzbu) resolution 6 distributions only;
update statistics high for table "demoadm".sachsachkto
(ssk_mandant,ssk_jahr,ssk_kto,ssk_periode,ssk_skto) resolution 3;
update statistics low for table "demoadm".sachsachkto ;
update statistics medium for table "demoadm".perssachkto
(psk_soll,psk_haben,psk_anzbu) resolution 5 distributions only;
update statistics high for table "demoadm".perssachkto
(psk_mandant,psk_jahr,psk_periode,psk_pkto,psk_skto) resolution 2;
update statistics low for table "demoadm".perssachkto ;
END
CALL "informix".nt_v_commitwork();
IF pcMandant = '01' THEN LET iBtk = "demoadm".wt_i_btkini() ;
ELSE LET iBtk = 0 ;
END IF
LET rcErg = iAnzRoh || ' Btr:' || iBtk || ' Psk ' || iOldPsk || '->' || iPsk
|| ' Ssk ' || iOldSsk || '->' || iSsk ;
RETURN rcErg ;
end procedure
grant execute on "demoadm".wt_c_psksskini to "dbadmin" as "demoadm"; grant execute on "demoadm".wt_c_psksskini to "dvabt" as "demoadm";
HTML-Autor: Rohloff, Datenbank: demodb, Datenstand: 31.05.00 17:07