Hallo Denis,
denis robel hat geschrieben:
wie sehen denn bei Dir die Trigger BRRCP_BI2 und BRRCP_BU0 aus ?
So sehen sie aus:
Code: Alles auswählen
SET TERM ^ ;
CREATE OR ALTER TRIGGER BRRCP_BI2 FOR BRRCP
ACTIVE BEFORE INSERT POSITION 2
AS
DECLARE VARIABLE BWAER_ID INTEGER;
DECLARE VARIABLE KURS_FW NUMERIC(15,6);
DECLARE VARIABLE BMAND_ID INTEGER;
DECLARE VARIABLE SJA VARCHAR(1);
DECLARE VARIABLE SNEIN VARCHAR(1);
DECLARE VARIABLE MENGE_KLPE NUMERIC(15,4);
BEGIN
/* Letzter Stand 27.09.2007 16:35:14 AS */
/* Protokollieren, dass Aktion hier durchgelaufen ist */
IF (GEN_ID(GEN_ENTWICKLUNG,0) = 0) THEN
INSERT INTO A_WASMACHTIB (PROGRAMMTEIL) VALUES ('BRRCP_BI2');
/* Feld TST wird in VIEW-Trigger gesetzt und kann hier bei Bedarf abgefragt werden*/
/* TST: 1 = LS-Rückst.; 2 = LS-Pos.; 3 = RG-Rückst.; 4 = RG-Pos. (siehe Domänenbeschr. zum Feld) */
IF ((NEW.ID IS NULL) OR (NEW.ID = 0)) THEN NEW.ID = GEN_ID(GEN_BRRCP,1);
NEW.BENUTZER = USER;
NEW.GESPEICHERT = CURRENT_TIMESTAMP;
EXECUTE PROCEDURE P_BFIRMA_JANEIN RETURNING_VALUES (:SJA, :SNEIN);
IF (NEW.BAUFPO_ID_ALTAUFNRP = 0) THEN NEW.BAUFPO_ID_ALTAUFNRP = NULL;
IF (NEW.BRRC_ID_ALTRGNR = 0) THEN NEW.BRRC_ID_ALTRGNR = NULL;
IF (NEW.BRLS_ID_LSNR = 0) THEN NEW.BRLS_ID_LSNR = NULL;
IF (NEW.BRRC_ID_RGNR = 0) THEN NEW.BRRC_ID_RGNR = NULL;
IF (NEW.BRRCP_ID_BRRCP = 0) THEN NEW.BRRCP_ID_BRRCP = NULL;
IF (NEW.BVERPA_ID = 0) THEN NEW.BVERPA_ID = NULL;
IF (NEW.EPREIS IS NULL) THEN NEW.EPREIS = 0.0;
IF (NEW.GEBMENGE IS NULL) THEN NEW.GEBMENGE = 0.0;
IF (NEW.GPREIS IS NULL) THEN NEW.GPREIS = 0.0;
IF (NEW.GPNRAB IS NULL) THEN NEW.GPNRAB = NEW.GPREIS;
IF (NEW.MENGE IS NULL) THEN NEW.MENGE = 0.0;
IF (NEW.MWSTSATZ IS NULL) THEN NEW.MWSTSATZ = 0.0;
IF (NEW.PRBASIS IS NULL) THEN NEW.PRBASIS = 0;
IF (NEW.RABATT1 IS NULL) THEN NEW.RABATT1 = 0.0;
IF (NEW.RABATT2 IS NULL) THEN NEW.RABATT2 = 0.0;
IF (NEW.EPREIS_FW IS NULL) THEN NEW.EPREIS_FW = 0.0;
IF (NEW.GPREIS_FW IS NULL) THEN NEW.GPREIS_FW = 0.0;
IF (NEW.GPNRAB_FW IS NULL) THEN NEW.GPNRAB_FW = NEW.GPREIS_FW;
IF (NEW.EPREIS_FIX IS NULL) THEN NEW.EPREIS_FIX = sNEIN;
IF (NEW.GPREIS_FIX IS NULL) THEN NEW.GPREIS_FIX = sNEIN;
IF (NEW.MTZ_MTA IS NULL) THEN NEW.MTZ_MTA = 0.0;
IF (NEW.GPREIS_MTZ IS NULL) THEN NEW.GPREIS_MTZ = 0.0;
IF (NEW.BSA_EKPREIS IS NULL) THEN NEW.BSA_EKPREIS = 0.0;
IF (NEW.DECKBEITRAG IS NULL) THEN NEW.DECKBEITRAG = 0.0;
IF (NEW.UVP IS NULL) THEN
SELECT UVP
FROM BAUFPO
WHERE ID = NEW.BAUFPO_ID_AUFNRPOS
INTO NEW.UVP;
/* MENGE_LAG ist die Menge in der Mengeneinheit Verkauf, da in der MENGE
eine kundenspezifische Einheit reingerechnet werden kann.
Wird immer mitgepflegt. */
EXECUTE PROCEDURE P_CEINHEIT_BSAKUNDE(NEW.BSA_ID_ARTNR, NEW.BKUNDE_ID, NULL,
NEW.MENGE, 2, 1)
RETURNING_VALUES(NEW.MENGE_LAG);
/* Pos.Nr. für Lieferschein / Rechnung */
IF (NEW.LS_POSNR IS NULL) THEN NEW.LS_POSNR = ''; /* darf nicht NULL sein */
IF (NEW.RG_POSNR IS NULL) THEN NEW.RG_POSNR = ''; /* darf nicht NULL sein */
IF ((NEW.BRLS_ID_LSNR IS NOT NULL) AND ((NEW.LS_POSNR IS NULL) OR (F_LRTRIM(NEW.LS_POSNR)=''))) THEN
EXECUTE PROCEDURE P_MASKENKEY_LFDNR ('BRRCP_LS_POS', NEW.BRLS_ID_LSNR, 10, NEW.LS_POSNR)
RETURNING_VALUES (NEW.LS_POSNR);
ELSE
IF (NEW.BRLS_ID_LSNR IS NULL) THEN
NEW.LS_POSNR = '';
IF ((NEW.BRRC_ID_RGNR IS NOT NULL) AND ((NEW.RG_POSNR IS NULL) OR (F_LRTRIM(NEW.RG_POSNR)=''))) THEN
EXECUTE PROCEDURE P_MASKENKEY_LFDNR ('BRRCP_RG_POS', NEW.BRRC_ID_RGNR, 10, NEW.RG_POSNR)
RETURNING_VALUES (NEW.RG_POSNR);
ELSE
IF (NEW.BRRC_ID_RGNR IS NULL) THEN
NEW.RG_POSNR = '';
BWAER_ID = NULL;
IF (NEW.BRRC_ID_RGNR IS NOT NULL) THEN
BEGIN
SELECT BWAER_ID,
KURS_FW,
BMAND_ID
FROM BRRC
WHERE ID = NEW.BRRC_ID_RGNR
INTO :BWAER_ID,
:KURS_FW,
:BMAND_ID;
END
ELSE IF (NEW.BAUFPO_ID_AUFNRPOS IS NOT NULL) THEN
BEGIN
SELECT VB.BWAER_ID,
VB.KURS_FW,
VB.BMAND_ID
FROM BAUFPO VA
LEFT OUTER JOIN BAUF VB ON VA.BAUF_ID_LINKKEY = VB.ID
WHERE VA.ID = NEW.BAUFPO_ID_AUFNRPOS
INTO :BWAER_ID,
:KURS_FW,
:BMAND_ID;
END
IF (BWAER_ID IS NULL) THEN
BEGIN
EXECUTE PROCEDURE P_BMAND_CHECK
RETURNING_VALUES (:BMAND_ID);
SELECT BWAER_ID
FROM BMAND
WHERE ID = :BMAND_ID
INTO :BWAER_ID;
KURS_FW = 1.0;
END
IF (USER <> 'WORSTCASE') THEN
BEGIN
/* Kleinste Preiseinheit prüfen */
EXECUTE PROCEDURE P_BSA_KLPE(NEW.BSA_ID_ARTNR, NEW.MENGE)
RETURNING_VALUES(:MENGE_KLPE);
EXECUTE PROCEDURE P_BRRCP_PREIS (
:BWAER_ID,
:KURS_FW,
NEW.MENGE,
:MENGE_KLPE,
NEW.EPREIS,
NEW.EPREIS_FW,
NEW.PRBASIS,
NEW.RABATT1,
NEW.RABATT2,
NEW.GPREIS,
NEW.GPREIS_FW,
NEW.BSA_ID_ARTNR,
NEW.BMENG_ID_MEINHEIT,
NEW.BMENG_ID_PREISHEIT,
'MENGE',
NEW.EPREIS_FIX,
NEW.GPREIS_FIX,
:BMAND_ID)
RETURNING_VALUES(
NEW.MENGE,
NEW.EPREIS,
NEW.EPREIS_FW,
NEW.PRBASIS,
NEW.RABATT1,
NEW.RABATT2,
NEW.GPREIS,
NEW.GPREIS_FW);
NEW.EPREIS = F_ZAHLRUNDEN(NEW.EPREIS, 2);
NEW.EPREIS_FW = F_ZAHLRUNDEN(NEW.EPREIS_FW, 2);
NEW.GPREIS = F_ZAHLRUNDEN(NEW.GPREIS, 2);
NEW.GPREIS_FW = F_ZAHLRUNDEN(NEW.GPREIS_FW, 2);
NEW.GPREIS_MTZ = F_ZAHLRUNDEN(NEW.GPREIS + NEW.MTZ_MTA, 2);
END
END
^
SET TERM ; ^
/* Privileges of triggers */
GRANT INSERT ON A_WASMACHTIB TO TRIGGER BRRCP_BI2;
GRANT SELECT ON BAUF TO TRIGGER BRRCP_BI2;
GRANT SELECT ON BAUFPO TO TRIGGER BRRCP_BI2;
GRANT SELECT ON BFIRMA TO TRIGGER BRRCP_BI2;
GRANT SELECT ON BMAND TO TRIGGER BRRCP_BI2;
GRANT SELECT ON BRRC TO TRIGGER BRRCP_BI2;
GRANT UPDATE, REFERENCES ON BRRCP TO TRIGGER BRRCP_BI2;
GRANT EXECUTE ON PROCEDURE P_BARTLHBU01 TO TRIGGER BRRCP_BI2;
GRANT EXECUTE ON PROCEDURE P_BRRCP_PREIS TO TRIGGER BRRCP_BI2;
GRANT EXECUTE ON PROCEDURE P_MASKENKEY_LFDNR TO TRIGGER BRRCP_BI2;
Code: Alles auswählen
CREATE OR ALTER TRIGGER BRRCP_BU0 FOR BRRCP
ACTIVE BEFORE UPDATE POSITION 0
AS
DECLARE VARIABLE BWAER_ID INTEGER;
DECLARE VARIABLE KURS_FW NUMERIC(15,6);
DECLARE VARIABLE BMAND_ID INTEGER;
DECLARE VARIABLE SJA VARCHAR(1);
DECLARE VARIABLE SNEIN VARCHAR(1);
DECLARE VARIABLE MENGE_KLPE NUMERIC(15,4);
BEGIN
/* Letzter Stand 27.09.2007 16:35:46 AS */
/* Protokollieren, dass Aktion hier durchgelaufen ist */
IF (GEN_ID(GEN_ENTWICKLUNG,0) = 0) THEN
INSERT INTO A_WASMACHTIB (PROGRAMMTEIL) VALUES ('BRRCP_BU0');
/* Feld TST wird in VIEW-Trigger gesetzt und kann hier bei Bedarf abgefragt werden*/
/* TST: 1 = LS-Rückst.; 2 = LS-Pos.; 3 = RG-Rückst.; 4 = RG-Pos. (siehe Domänenbeschr. zum Feld) */
IF (NEW.GESPEICHERT <> OLD.GESPEICHERT) THEN EXCEPTION KEINE_AENDERUNG;
NEW.BENUTZER = USER;
NEW.GESPEICHERT = CURRENT_TIMESTAMP;
EXECUTE PROCEDURE P_BFIRMA_JANEIN RETURNING_VALUES (:SJA, :SNEIN);
IF (NEW.BAUFPO_ID_ALTAUFNRP = 0) THEN NEW.BAUFPO_ID_ALTAUFNRP = NULL;
IF (NEW.BRRC_ID_ALTRGNR = 0) THEN NEW.BRRC_ID_ALTRGNR = NULL;
IF (NEW.BRLS_ID_LSNR = 0) THEN NEW.BRLS_ID_LSNR = NULL;
IF (NEW.BRRC_ID_RGNR = 0) THEN NEW.BRRC_ID_RGNR = NULL;
IF (NEW.BRRCP_ID_BRRCP = 0) THEN NEW.BRRCP_ID_BRRCP = NULL;
IF (NEW.BVERPA_ID = 0) THEN NEW.BVERPA_ID = NULL;
IF (NEW.EPREIS IS NULL) THEN NEW.EPREIS = 0.0;
IF (NEW.GEBMENGE IS NULL) THEN NEW.GEBMENGE = 0.0;
IF (NEW.GPREIS IS NULL) THEN NEW.GPREIS = 0.0;
IF (NEW.GPNRAB IS NULL) THEN NEW.GPNRAB = NEW.GPREIS;
IF (NEW.MENGE IS NULL) THEN NEW.MENGE = 0.0;
IF (NEW.MWSTSATZ IS NULL) THEN NEW.MWSTSATZ = 0.0;
IF (NEW.PRBASIS IS NULL) THEN NEW.PRBASIS = 0;
IF (NEW.RABATT1 IS NULL) THEN NEW.RABATT1 = 0.0;
IF (NEW.RABATT2 IS NULL) THEN NEW.RABATT2 = 0.0;
IF (NEW.EPREIS_FW IS NULL) THEN NEW.EPREIS_FW = 0.0;
IF (NEW.GPREIS_FW IS NULL) THEN NEW.GPREIS_FW = 0.0;
IF (NEW.GPNRAB_FW IS NULL) THEN NEW.GPNRAB_FW = NEW.GPREIS_FW;
IF (NEW.EPREIS_FIX IS NULL) THEN NEW.EPREIS_FIX = sNEIN;
IF (NEW.GPREIS_FIX IS NULL) THEN NEW.GPREIS_FIX = sNEIN;
IF (NEW.MTZ_MTA IS NULL) THEN NEW.MTZ_MTA = 0.0;
IF (NEW.GPREIS_MTZ IS NULL) THEN NEW.GPREIS_MTZ = 0.0;
IF (NEW.BSA_EKPREIS IS NULL) THEN NEW.BSA_EKPREIS = 0.0;
IF (NEW.DECKBEITRAG IS NULL) THEN NEW.DECKBEITRAG = 0.0;
/* Pos.Nr. für Lieferschein / Rechnung */
IF (NEW.LS_POSNR IS NULL) THEN NEW.LS_POSNR = ''; /* darf nicht NULL sein */
IF (NEW.RG_POSNR IS NULL) THEN NEW.RG_POSNR = ''; /* darf nicht NULL sein */
/* MENGE_LAG ist die Menge in der Mengeneinheit Verkauf, da in der MENGE
eine kundenspezifische Einheit reingerechnet werden kann.
Wird immer mitgepflegt. */
EXECUTE PROCEDURE P_CEINHEIT_BSAKUNDE(NEW.BSA_ID_ARTNR, NEW.BKUNDE_ID, NULL,
NEW.MENGE, 2, 1)
RETURNING_VALUES(NEW.MENGE_LAG);
BWAER_ID = NULL;
IF (NEW.BRRC_ID_RGNR IS NOT NULL) THEN
BEGIN
SELECT BWAER_ID,
KURS_FW,
BMAND_ID
FROM BRRC
WHERE ID = NEW.BRRC_ID_RGNR
INTO :BWAER_ID,
:KURS_FW,
:BMAND_ID;
END
ELSE
IF (NEW.BAUFPO_ID_AUFNRPOS IS NOT NULL) THEN
BEGIN
SELECT VB.BWAER_ID,
VB.KURS_FW,
VB.BMAND_ID
FROM BAUFPO VA
LEFT OUTER JOIN BAUF VB ON VA.BAUF_ID_LINKKEY = VB.ID
WHERE VA.ID = NEW.BAUFPO_ID_AUFNRPOS
INTO :BWAER_ID,
:KURS_FW,
:BMAND_ID;
END
IF (BWAER_ID IS NULL) THEN
BEGIN
EXECUTE PROCEDURE P_BMAND_CHECK
RETURNING_VALUES (:BMAND_ID);
SELECT BWAER_ID
FROM BMAND
WHERE ID = :BMAND_ID
INTO :BWAER_ID;
KURS_FW = 1.0;
END
IF (USER <> 'WORSTCASE') THEN
BEGIN
/* Anfang Lieferschein.Position */
IF ((F_EQUALINTEGER(NEW.BRLS_ID_LSNR, OLD.BRLS_ID_LSNR)=0) OR
(F_EQUALSTRING(NEW.LS_POSNR, OLD.LS_POSNR)=0)) THEN
BEGIN
IF (F_EQUALINTEGER(NEW.BRLS_ID_LSNR, OLD.BRLS_ID_LSNR)=0) THEN
BEGIN
IF (NEW.BRLS_ID_LSNR IS NULL) THEN
NEW.LS_POSNR = '';
ELSE
BEGIN
NEW.LS_POSNR = '';
EXECUTE PROCEDURE P_MASKENKEY_LFDNR ('BRRCP_LS_POS', NEW.BRLS_ID_LSNR, 10, NEW.LS_POSNR)
RETURNING_VALUES (NEW.LS_POSNR);
END
END
ELSE
BEGIN
/* Pos.Nr. hat sich geändert */
EXECUTE PROCEDURE P_MASKENKEY_LFDNR ('BRRCP_LS_POS', NEW.BRLS_ID_LSNR, 10, NEW.LS_POSNR)
RETURNING_VALUES (NEW.LS_POSNR);
END
/* Leere Lagerbuchung machen */
/* Nur für Positionen, die von einem Auftrag kommen (Gutschriften)
und die noch keinen Lieferschein haben (gesplittete LS-Positionen) */
IF ((NEW.BAUFPO_ID_AUFNRPOS IS NOT NULL) AND
(NEW.BRLS_ID_LSNR IS NULL)) THEN
EXECUTE PROCEDURE P_BARTLHBU01 (NEW.BARTLH_ID_LAGERNR,NEW.ID,'LS',0);
END
/* Ende Lieferschein.Position */
/* Anfang Rechnung Position */
IF ((F_EQUALINTEGER(NEW.BRRC_ID_RGNR, OLD.BRRC_ID_RGNR)=0) OR
(F_EQUALSTRING(NEW.RG_POSNR, OLD.RG_POSNR)=0)) THEN
BEGIN
IF (F_EQUALINTEGER(NEW.BRRC_ID_RGNR, OLD.BRRC_ID_RGNR)=0) THEN
BEGIN
IF (NEW.BRRC_ID_RGNR IS NULL) THEN
NEW.RG_POSNR = '';
ELSE
BEGIN
NEW.RG_POSNR = '';
EXECUTE PROCEDURE P_MASKENKEY_LFDNR ('BRRCP_RG_POS', NEW.BRRC_ID_RGNR, 10, NEW.RG_POSNR)
RETURNING_VALUES (NEW.RG_POSNR);
END
END
ELSE
BEGIN
/* Pos.Nr. hat sich geändert */
EXECUTE PROCEDURE P_MASKENKEY_LFDNR ('BRRCP_RG_POS', NEW.BRRC_ID_RGNR, 10, NEW.RG_POSNR)
RETURNING_VALUES (NEW.RG_POSNR);
END
END
/* Ende Rechnung Position */
END
IF (USER <> 'WORSTCASE') THEN
BEGIN
/* Kleinste Preiseinheit prüfen */
EXECUTE PROCEDURE P_BSA_KLPE(NEW.BSA_ID_ARTNR, NEW.MENGE)
RETURNING_VALUES(:MENGE_KLPE);
EXECUTE PROCEDURE P_BRRCP_PREIS (
:BWAER_ID,
:KURS_FW,
NEW.MENGE,
:MENGE_KLPE,
NEW.EPREIS,
NEW.EPREIS_FW,
NEW.PRBASIS,
NEW.RABATT1,
NEW.RABATT2,
NEW.GPREIS,
NEW.GPREIS_FW,
NEW.BSA_ID_ARTNR,
NEW.BMENG_ID_MEINHEIT,
NEW.BMENG_ID_PREISHEIT,
'MENGE',
NEW.EPREIS_FIX,
NEW.GPREIS_FIX,
:BMAND_ID)
RETURNING_VALUES
NEW.MENGE,
NEW.EPREIS,
NEW.EPREIS_FW,
NEW.PRBASIS,
NEW.RABATT1,
NEW.RABATT2,
NEW.GPREIS,
NEW.GPREIS_FW;
NEW.EPREIS = F_ZAHLRUNDEN(NEW.EPREIS, 2);
NEW.EPREIS_FW = F_ZAHLRUNDEN(NEW.EPREIS_FW, 2);
NEW.GPREIS = F_ZAHLRUNDEN(NEW.GPREIS, 2);
NEW.GPREIS_FW = F_ZAHLRUNDEN(NEW.GPREIS_FW, 2);
NEW.GPREIS_MTZ = F_ZAHLRUNDEN(NEW.GPREIS + NEW.MTZ_MTA, 2);
/* EK-Preis aus Artikelstamm und Deckungsbeitrag eintragen,
wenn Position in Rechnung erfasst wird */
IF (NEW.BRRC_ID_RGNR IS NOT NULL AND OLD.BRRC_ID_RGNR IS NULL) THEN
BEGIN
SELECT F_ZAHLRUNDEN(GPREIS,2)
FROM BSA
WHERE ID = NEW.BSA_ID_ARTNR
INTO NEW.BSA_EKPREIS;
END
ELSE
IF (OLD.BRRC_ID_RGNR IS NOT NULL AND NEW.BRRC_ID_RGNR IS NULL) THEN
BEGIN
/* EK-Preis austragen aus Artikelstamm, wenn Position aus Rechnung gelöscht wird */
NEW.BSA_EKPREIS = 0.0;
NEW.DECKBEITRAG = 0.0;
END
/* Deckungsbeitrag ermitteln, wenn in Rechnung erfasst */
IF (NEW.BRRC_ID_RGNR IS NOT NULL AND
((NEW.GPREIS <> OLD.GPREIS) OR (NEW.MENGE <> OLD.MENGE) OR
(NEW.BSA_EKPREIS <> OLD.BSA_EKPREIS))) THEN
/* Berechnug über GPREIS, da hier Rabatt enthalten ist */
NEW.DECKBEITRAG = NEW.GPREIS - (NEW.BSA_EKPREIS * NEW.MENGE);
END
END
Wie man sieht, ist an diesen Triggern lange nichts geändert worden.
Was ist denn genau das Problem
).