CREATE OR REPLACE FORCE VIEW "V_DT430_DT472" ("DATUM_KAL", "PR", "VG", "RESS", "KOA", "OE_RESS", "DATE_CREATION", "DATE_MODIFY", "RWDU", "USER_CREATION", "USER_MODIFY", "OWNER_LICENSE", "OSC", "OWNER", "group", "any", "BEL_IST", "POS", "ABR_DATUM") AS
SELECT DATUM_KAL,
PR,
VG,
RESS,
KoA,
OE_RESS,
date_creation,
date_modify,
rwdu,
user_creation,
user_modify,
owner_license,
osc,
owner,
"group",
"any",
bel_ist_472 AS BEL_Ist,
pos_472 AS Pos,
abr_datum_472 AS ABR_DATUM
FROM
(SELECT DI000997 AS DATUM_KAL,
DI001390 AS PR,
DI001391 AS VG,
DI001392 AS RESS,
DI001393 AS KoA,
DI009063 AS OE_RESS,
DI001401 AS date_creation,
DI001402 AS date_modify,
DI001403 AS rwdu,
DI001404 AS user_creation,
DI001405 AS user_modify,
DI001406 AS owner_license,
DI000596 AS osc,
DI001407 AS owner,
DI001408 AS "group",
DI001409 AS "any"
FROM DT430,
DT466
WHERE DI000475 IN
(SELECT DI003107 FROM dt452 WHERE di003109=150
)
)
LEFT JOIN
(SELECT DI001519 DATUM_472,
DI001395 PR_472,
DI001516 VG_472,
DI001517 RESS_472,
SUM(DI001510) bel_ist_472,
MAX(NVL(DI001400,0)) pos_472,
MAX(DI002673) abr_datum_472
FROM DT472
GROUP BY DI001519,
DI001395,
DI001516,
DI001517
)
ON DATUM_472 =DATUM_KAL AND PR_472=PR AND VG_472=VG AND RESS_472=RESS;
CREATE OR REPLACE TRIGGER "CREATE_DT472" INSTEAD OF
UPDATE ON V_DT430_DT472 FOR EACH ROW DECLARE Pos NUMBER(10);
vh NUMBER(10);
BEGIN
-- check whether the record already exists of not
SELECT COUNT(di001395)
INTO vh
FROM dt472
WHERE DI001395=:NEW.PR
AND DI001516 =:NEW.VG
AND DI001517 =:NEW.RESS
AND DI001518 =:NEW.KoA
AND DI001400 =:NEW.Pos
AND DI001519 =:NEW.DATUM_KAL;
IF vh > 0 THEN
UPDATE DT472
SET DI001510 =:NEW.BEL_Ist,
DI001500 =:NEW.date_creation,
DI001501 =:NEW.date_modify,
DI001502 =:NEW.rwdu,
DI001503 =:NEW.user_creation,
DI001504 =:NEW.user_modify,
DI001505 =:NEW.owner_license,
DI000602 =:NEW.osc,
DI001506 =:NEW.owner,
DI001507 =:NEW."group",
DI001508 =:NEW."any"
WHERE DI001395=:NEW.PR
AND DI001516 =:NEW.VG
AND DI001517 =:NEW.RESS
AND DI001518 =:NEW.KoA
AND DI001400 =:NEW.Pos
AND DI001519 =:NEW.DATUM_KAL;
ELSE
Pos:=get_new_auto_id('820',NULL,NULL);
-- PR,VG,RESS,KoA,Pos,Datum,Bel-Ist,Kommentar,OE der Ress, zu belastende OE
-- object protection fields
INSERT
INTO DT472
(
DI001395,
DI001516,
DI001517,
DI001518,
DI001400,
DI001519,
DI001510,
DI001438,
DI001445,
DI007783,
DI001500,
DI001501,
DI001502,
DI001503,
DI001504,
DI001505,
DI000602,
DI001506,
DI001507,
DI001508
)
VALUES
(
:NEW.PR,
:NEW.VG,
:NEW.RESS,
:NEW.KoA,
Pos,
:NEW.DATUM_KAL,
:NEW.BEL_Ist,
'',
:NEW.OE_Ress,
:NEW.OE_Ress,
:NEW.date_creation,
:NEW.date_modify,
:NEW.rwdu,
:NEW.user_creation,
:NEW.user_modify,
:NEW.owner_license,
:NEW.osc,
:NEW.owner,
:NEW."group",
:NEW."any"
);
END IF;
END;
/
ALTER TRIGGER "CREATE_DT472" ENABLE;