125 lines
4.1 KiB
SQL
125 lines
4.1 KiB
SQL
--
|
|
-- $Id$
|
|
--
|
|
-- Verwijder alle bestelaanvragen en bestelopdrachten voor Veldmans
|
|
|
|
DEFINE thisfile = 'HPJI#52756.SQL'
|
|
DEFINE dbuser = '^HPJI'
|
|
DEFINE custid = 'HPJI'
|
|
|
|
SET ECHO ON
|
|
SET DEFINE ON
|
|
COLUMN fcltlogfile NEW_VALUE fcltlogfile NOPRINT;
|
|
WHENEVER SQLERROR EXIT;
|
|
SELECT adm.scriptspoolfile('&dbuser', '&thisfile') AS fcltlogfile FROM DUAL;
|
|
WHENEVER SQLERROR CONTINUE;
|
|
SPOOL &fcltlogfile
|
|
SET DEFINE OFF
|
|
|
|
------ payload begin ------
|
|
|
|
--Bes_bestelling_item
|
|
|
|
DELETE FROM bes_bestelling_item
|
|
WHERE bes_bestelling_item_key IN
|
|
(SELECT bi.bes_bestelling_item_key
|
|
FROM bes_bestelling_item bi, bes_bestelling b
|
|
WHERE b.bes_bestelling_key = bi.bes_bestelling_key
|
|
AND b.bes_bestelling_key IN
|
|
(5331,
|
|
5332,
|
|
5335,
|
|
5369,
|
|
5374,
|
|
5544,
|
|
5593,
|
|
5884));
|
|
|
|
|
|
|
|
--Bes_bestelling
|
|
|
|
DELETE FROM bes_bestelling
|
|
WHERE bes_bestelling_key IN
|
|
(SELECT b.bes_bestelling_key
|
|
FROM bes_bestelling b
|
|
WHERE b.bes_bestelling_key IN
|
|
(5331,
|
|
5332,
|
|
5335,
|
|
5369,
|
|
5374,
|
|
5544,
|
|
5593,
|
|
5884));
|
|
|
|
--Bes_bestelopdracht_item
|
|
|
|
DELETE FROM bes_bestelopdr_item
|
|
WHERE bes_bestelopdr_item_key IN
|
|
(SELECT boi.bes_bestelopdr_item_key
|
|
FROM bes_bestelopdr_item boi, bes_bestelopdr bo
|
|
WHERE bo.bes_bestelopdr_key = boi.bes_bestelopdr_key
|
|
AND boi.bes_bestelopdr_key IN
|
|
(5269,
|
|
5810,
|
|
5272,
|
|
5820,
|
|
5479,
|
|
5268,
|
|
5309,
|
|
5528));
|
|
|
|
|
|
--Bes_bestelopdr
|
|
|
|
DELETE FROM bes_bestelopdr
|
|
WHERE bes_bestelopdr_key IN
|
|
(SELECT bo.bes_bestelopdr_key
|
|
FROM bes_bestelopdr bo
|
|
WHERE bo.bes_bestelopdr_key IN
|
|
(5269,
|
|
5810,
|
|
5272,
|
|
5820,
|
|
5479,
|
|
5268,
|
|
5309,
|
|
5528));
|
|
|
|
-- Ook de tracking van de bestellingen die er niet meer zijn.
|
|
DELETE FROM fac_tracking t
|
|
WHERE EXISTS
|
|
(SELECT 1
|
|
FROM fac_srtnotificatie
|
|
WHERE fac_srtnotificatie_xmlnode = 'bestelling'
|
|
AND fac_srtnotificatie_key = t.fac_srtnotificatie_key)
|
|
AND NOT EXISTS (SELECT 1
|
|
FROM bes_bestelling
|
|
WHERE bes_bestelling_key= t.fac_tracking_refkey);
|
|
|
|
|
|
-- Ook de tracking van de bestelopdrachten die er niet meer zijn.
|
|
DELETE FROM fac_tracking t
|
|
WHERE EXISTS
|
|
(SELECT 1
|
|
FROM fac_srtnotificatie
|
|
WHERE fac_srtnotificatie_xmlnode = 'bestelopdr'
|
|
AND fac_srtnotificatie_key = t.fac_srtnotificatie_key)
|
|
AND NOT EXISTS (SELECT 1
|
|
FROM bes_bestelopdr
|
|
WHERE bes_bestelopdr_key= t.fac_tracking_refkey);
|
|
|
|
------ payload end ------
|
|
|
|
SET DEFINE OFF
|
|
BEGIN adm.systrackscriptId ('$Id$', 1); END;
|
|
/
|
|
|
|
COMMIT;
|
|
SET ECHO OFF
|
|
SPOOL OFF
|
|
SET DEFINE ON
|
|
PROMPT Logfile of this upgrade is: &fcltlogfile
|
|
|
|
-- @@ &custid |