52 lines
1.7 KiB
SQL
52 lines
1.7 KiB
SQL
SET SERVEROUTPUT ON
|
|
|
|
DECLARE
|
|
afmelddatum DATE;
|
|
count_mld NUMBER;
|
|
count_opdr NUMBER;
|
|
|
|
BEGIN
|
|
afmelddatum := SYSDATE;
|
|
|
|
UPDATE mld_opdr
|
|
SET mld_statusopdr_key = 6,
|
|
mld_opdr_datumuitgevoerd = afmelddatum,
|
|
mld_opdr_afgemeld_user = 1042
|
|
WHERE mld_opdr_key IN (
|
|
SELECT mld_opdr_key
|
|
FROM mld_opdr o, mld_melding m
|
|
WHERE mld_statusopdr_key = 5
|
|
AND o.mld_melding_key = m.mld_melding_key
|
|
AND ( o.mld_opdr_aanmaak < SYSDATE - 60
|
|
OR o.mld_opdr_aanmaak IS NULL
|
|
)
|
|
AND m.mld_ins_discipline_key = 165);
|
|
|
|
UPDATE mld_melding m
|
|
SET mld_melding_afgemeld_user = 1042,
|
|
mld_melding_afgemeld = afmelddatum
|
|
WHERE mld_melding_key IN (SELECT DISTINCT mld_melding_key
|
|
FROM mld_opdr o
|
|
WHERE mld_opdr_datumuitgevoerd =
|
|
afmelddatum)
|
|
AND NOT EXISTS (SELECT m.mld_melding_key
|
|
FROM mld_opdr o
|
|
WHERE mld_statusopdr_key = 5
|
|
AND m.mld_melding_key = o.mld_melding_key);
|
|
|
|
SELECT count(mld_melding_key)
|
|
INTO count_mld
|
|
FROM mld_melding
|
|
WHERE mld_melding_afgemeld = afmelddatum;
|
|
|
|
SELECT count(mld_opdr_key)
|
|
INTO count_opdr
|
|
FROM mld_opdr
|
|
WHERE mld_opdr_datumuitgevoerd = afmelddatum;
|
|
|
|
DBMS_OUTPUT.PUT_LINE('Aantal meldingen afgemeld: ' || count_mld );
|
|
DBMS_OUTPUT.PUT_LINE('Aantal opdrachten afgemeld: ' || count_opdr );
|
|
END;
|
|
/
|
|
|
|
COMMIT ; |