CREATE PROCEDURE "demoadm".p_journal(
pv_action char(1),
pv_mandant char(2),
pv_bu_journr int, -- journal.bu_journr
pv_bu_status_6 char(1), -- journal.bu_status_6
pv_bu_status_7 char(1), -- journal.bu_status_7
pv_bu_status_8 char(1), -- journal.bu_status_8
pv_bu_skto int, -- journal.bu_skto
pv_bu_fvkto int, -- journal.bu_fvkto
pv_bu_fvgkto int, -- journal.bu_fvgkto
pv_bu_kto char(7), -- journal.bu_kto
pv_bu_gkto char(7), -- journal.bu_gkto
pv_bu_nettobetrag decimal(13,2), -- journal.bu_nettobetrag
pv_bu_umstbetrag decimal(13,2), -- journal.bu_umstbetrag
pv_bu_bruttobetr decimal(13,2), -- journal.bu_bruttobetr
pv_bu_periode int, -- journal.bu_periode
pv_bu_soha int, -- journal.bu_soha
pv_bu_umstcode int -- journal.bu_umstcode
)
DEFINE dw_oper varchar(50,0);
DEFINE err_sql int;
DEFINE err_isam int;
DEFINE err_txt varchar(150,0);
DEFINE err_msg varchar(250,0);
DEFINE i int;
DEFINE j int;
DEFINE k int;
DEFINE sv_skto_nr char(15);
DEFINE sv_mandant char(2);
DEFINE sv_jahr decimal(4,0);
DEFINE sv_periode decimal(2,0);
DEFINE sv_soll decimal(13,2);
DEFINE sv_haben decimal(13,2);
LET dw_oper = ' Start';
SET LOCK MODE TO WAIT 10;
LET dw_oper = ' Computing variables';
LET sv_mandant = pv_mandant;
LET sv_jahr = 1900 + TRUNC(pv_bu_journr/10000000);
IF pv_bu_status_8 = 1 THEN
LET sv_periode = 0;
ELSE
LET sv_periode = pv_bu_periode;
END IF
IF LENGTH(TRIM(pv_bu_kto)) = 6 THEN -- bu_kto is 6 char long
LET j = 0;
BEGIN
ON EXCEPTION
SET err_sql, err_isam, err_txt
LET err_msg = 'ISAM['||err_isam||']:'||err_txt||' #'||dw_oper;
INSERT INTO DWE_journal VALUES (0,err_sql,err_msg,0,'sr_sh_skto:1',
pv_action,
sv_mandant,
sv_skto_nr,
sv_jahr,
sv_periode,
sv_soll,
sv_haben
);
END EXCEPTION;
LET sv_skto_nr = pv_bu_kto;
IF pv_bu_soha = 104 THEN -- bu_soha = h
IF (pv_bu_status_7 = 0) OR (pv_bu_umstcode = 99) THEN
LET sv_haben = pv_bu_bruttobetr;
ELSE
LET sv_haben = pv_bu_nettobetrag;
END IF
LET sv_soll = 0;
ELSE -- bu_soha = s
IF (pv_bu_status_7 = 0) OR (pv_bu_umstcode = 99) THEN
LET sv_soll = pv_bu_bruttobetr;
ELSE
LET sv_soll = pv_bu_nettobetrag;
END IF
LET sv_haben = 0;
END IF
IF pv_action = 'D' THEN
LET sv_soll = -sv_soll;
LET sv_haben = -sv_haben;
END IF
UPDATE sr_sh_skto SET
soll = soll + sv_soll,
haben = haben + sv_haben
WHERE
(skto_nr = sv_skto_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_sh_skto (
skto_nr,
mandant,
jahr,
periode,
soll,
haben
)
VALUES (
sv_skto_nr,
sv_mandant,
sv_jahr,
sv_periode,
sv_soll,
sv_haben
);
END IF
END
ELSE
LET j = 1;
END IF
IF LENGTH(TRIM(pv_bu_gkto)) = 6 THEN -- bu_gkto is 6 char long
LET k = 0;
BEGIN
ON EXCEPTION
SET err_sql, err_isam, err_txt
LET err_msg = 'ISAM['||err_isam||']:'||err_txt||' #'||dw_oper;
INSERT INTO DWE_journal VALUES (0,err_sql,err_msg,0,'sr_sh_skto:2',
pv_action,
sv_mandant,
sv_skto_nr,
sv_jahr,
sv_periode,
sv_soll,
sv_haben
);
END EXCEPTION;
LET sv_skto_nr = pv_bu_gkto;
IF pv_bu_soha = 104 THEN -- bu_soha = h
IF ((j = 1) OR (pv_bu_status_6 = 1)) AND (pv_bu_umstcode <> 99) THEN
LET sv_soll = pv_bu_nettobetrag;
ELSE
LET sv_soll = pv_bu_bruttobetr;
END IF
LET sv_haben = 0;
ELSE -- bu_soha = s
IF ((j = 1) OR (pv_bu_status_6 = 1)) AND (pv_bu_umstcode <> 99) THEN
LET sv_haben = pv_bu_nettobetrag;
ELSE
LET sv_haben = pv_bu_bruttobetr;
END IF
LET sv_soll = 0;
END IF
IF pv_action = 'D' THEN
LET sv_soll = -sv_soll;
LET sv_haben = -sv_haben;
END IF
UPDATE sr_sh_skto SET
soll = soll + sv_soll,
haben = haben + sv_haben
WHERE
(skto_nr = sv_skto_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_sh_skto (
skto_nr,
mandant,
jahr,
periode,
soll,
haben
)
VALUES (
sv_skto_nr,
sv_mandant,
sv_jahr,
sv_periode,
sv_soll,
sv_haben
);
END IF
END
ELSE
LET k = 1;
END IF
IF pv_bu_skto <> 0 THEN -- bu_skto is 6
BEGIN
ON EXCEPTION
SET err_sql, err_isam, err_txt
LET err_msg = 'ISAM['||err_isam||']:'||err_txt||' #'||dw_oper;
INSERT INTO DWE_journal VALUES (0,err_sql,err_msg,0,'sr_sh_skto:3',
pv_action,
sv_mandant,
sv_skto_nr,
sv_jahr,
sv_periode,
sv_soll,
sv_haben
);
END EXCEPTION;
LET sv_skto_nr = pv_bu_skto;
IF pv_bu_status_7 = 0 THEN
IF pv_bu_soha = 115 THEN -- s
LET sv_haben = pv_bu_umstbetrag;
LET sv_soll = 0;
ELSE -- h
LET sv_haben = 0;
LET sv_soll = pv_bu_umstbetrag;
END IF
ELSE
IF pv_bu_soha = 115 THEN -- s
LET sv_haben = 0;
LET sv_soll = pv_bu_umstbetrag;
ELSE -- h
LET sv_haben = pv_bu_umstbetrag;
LET sv_soll = 0;
END IF
END IF
IF pv_action = 'D' THEN
LET sv_soll = -sv_soll;
LET sv_haben = -sv_haben;
END IF
UPDATE sr_sh_skto SET
soll = soll + sv_soll,
haben = haben + sv_haben
WHERE
(skto_nr = sv_skto_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_sh_skto (
skto_nr,
mandant,
jahr,
periode,
soll,
haben
)
VALUES (
sv_skto_nr,
sv_mandant,
sv_jahr,
sv_periode,
sv_soll,
sv_haben
);
END IF
END
END IF
IF (pv_bu_fvkto <> 0) AND (j = 1) THEN
BEGIN
ON EXCEPTION
SET err_sql, err_isam, err_txt
LET err_msg = 'ISAM['||err_isam||']:'||err_txt||' #'||dw_oper;
INSERT INTO DWE_journal VALUES (0,err_sql,err_msg,0,'sr_sh_skto:4',
pv_action,
sv_mandant,
sv_skto_nr,
sv_jahr,
sv_periode,
sv_soll,
sv_haben
);
END EXCEPTION;
LET sv_skto_nr = pv_bu_fvkto;
IF pv_bu_soha = 104 THEN -- bu_soha = h
LET sv_haben = pv_bu_bruttobetr;
LET sv_soll = 0;
ELSE -- bu_soha = s
LET sv_soll = pv_bu_bruttobetr;
LET sv_haben = 0;
END IF
IF pv_action = 'D' THEN
LET sv_soll = -sv_soll;
LET sv_haben = -sv_haben;
END IF
UPDATE sr_sh_skto SET
soll = soll + sv_soll,
haben = haben + sv_haben
WHERE
(skto_nr = sv_skto_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_sh_skto (
skto_nr,
mandant,
jahr,
periode,
soll,
haben
)
VALUES (
sv_skto_nr,
sv_mandant,
sv_jahr,
sv_periode,
sv_soll,
sv_haben
);
END IF
END
END IF
IF (pv_bu_fvgkto <> 0) AND (k = 1) THEN
BEGIN
ON EXCEPTION
SET err_sql, err_isam, err_txt
LET err_msg = 'ISAM['||err_isam||']:'||err_txt||' #'||dw_oper;
INSERT INTO DWE_journal VALUES (0,err_sql,err_msg,0,'sr_sh_skto:5',
pv_action,
sv_mandant,
sv_skto_nr,
sv_jahr,
sv_periode,
sv_soll,
sv_haben
);
END EXCEPTION;
LET sv_skto_nr = pv_bu_fvgkto;
IF pv_bu_soha = 104 THEN -- bu_soha = h
LET sv_soll = pv_bu_bruttobetr;
LET sv_haben = 0;
ELSE -- bu_soha = s
LET sv_haben = pv_bu_bruttobetr;
LET sv_soll = 0;
END IF
IF pv_action = 'D' THEN
LET sv_soll = -sv_soll;
LET sv_haben = -sv_haben;
END IF
UPDATE sr_sh_skto SET
soll = soll + sv_soll,
haben = haben + sv_haben
WHERE
(skto_nr = sv_skto_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_sh_skto (
skto_nr,
mandant,
jahr,
periode,
soll,
haben
)
VALUES (
sv_skto_nr,
sv_mandant,
sv_jahr,
sv_periode,
sv_soll,
sv_haben
);
END IF
END
END IF
SET LOCK MODE TO NOT WAIT;
END PROCEDURE
HTML-Autor: Rohloff, Datenbank: demodb, Datenstand: 31.05.00 17:07