52 lines
1.6 KiB
SQL
52 lines
1.6 KiB
SQL
-- Customer specific once-script NYBU#31900.
|
|
|
|
SET ECHO ON
|
|
SPOOL xNYBU#31900.lst
|
|
SET DEFINE OFF
|
|
|
|
-- NYBU#31900 vastgelopen workflowmeldingen afmelden
|
|
-- meldt lopende hoofdworkflow meldingen (status 5) af die geen lopende workflowstappen meer hebben.
|
|
-- status 1, 5, 6 = afgewezen, afgemeld, verwerkt
|
|
DECLARE
|
|
CURSOR c
|
|
IS
|
|
SELECT mld_melding_key
|
|
FROM mld_melding m
|
|
WHERE mld_melding_status NOT IN (1, 5, 6)
|
|
AND m.mld_melding_key = mld_melding_start_key
|
|
AND m.mld_melding_key NOT IN
|
|
(SELECT m.mld_melding_start_key
|
|
FROM mld_melding m
|
|
WHERE mld_melding_status NOT IN (1, 5, 6)
|
|
AND m.mld_melding_key !=
|
|
mld_melding_start_key);
|
|
BEGIN
|
|
FOR rec IN c
|
|
LOOP
|
|
BEGIN
|
|
UPDATE mld_melding
|
|
SET mld_melding_status = 5
|
|
WHERE mld_melding_key = rec.mld_melding_key;
|
|
|
|
|
|
INSERT INTO fac_tracking (
|
|
fac_tracking_refkey,
|
|
fac_srtnotificatie_key,
|
|
fac_tracking_oms
|
|
)
|
|
VALUES (
|
|
rec.mld_melding_key,
|
|
42, -- mldupd
|
|
'Workflow melding op verzoek van Nyenrode afgemeld'
|
|
);
|
|
END;
|
|
END LOOP;
|
|
|
|
COMMIT;
|
|
END;
|
|
/
|
|
|
|
BEGIN adm.systrackscriptId('$Id$'); END;
|
|
/
|
|
SPOOL OFF
|