165 lines
5.2 KiB
SQL
165 lines
5.2 KiB
SQL
-- Customer specific once-script WEHK9.
|
|
--
|
|
-- (c) 2009 SG|facilitor bv
|
|
-- $Revision$
|
|
-- $Id$
|
|
--
|
|
-- Support: +31 53 4800710
|
|
SET ECHO ON;
|
|
SPOOL xwehk9.lst;
|
|
SET DEFINE OFF;
|
|
|
|
/* Formatted on 16-12-2009 17:42:24 (QP5 v5.115.810.9015) */
|
|
BEGIN
|
|
FOR rec
|
|
IN (SELECT km.mld_melding_key,
|
|
fac.safe_to_number (
|
|
REPLACE (km.mld_kenmerkmelding_waarde, ',', '.')
|
|
)
|
|
waarde
|
|
FROM mld_kenmerkmelding km, mld_kenmerk k
|
|
WHERE km.mld_kenmerk_key = k.mld_kenmerk_key
|
|
AND k.mld_srtkenmerk_key = 563) -- OUD; waarde tbv finance
|
|
LOOP
|
|
BEGIN
|
|
INSERT INTO MLD_KENMERKOPDR (mld_opdr_key, mld_kenmerk_key, mld_kenmerkopdr_waarde)
|
|
SELECT mld_opdr_key, 701, -- NIEUW; waarde tbv finance
|
|
COALESCE (rec.waarde, 0)
|
|
FROM mld_opdr o
|
|
WHERE o.mld_typeopdr_key = 681
|
|
AND o.mld_melding_key = rec.mld_melding_key;
|
|
|
|
COMMIT;
|
|
EXCEPTION
|
|
WHEN OTHERS
|
|
THEN
|
|
fac.imp_writelog (
|
|
'ONCE',
|
|
-1,
|
|
'W',
|
|
'ORACLE error: ' || SUBSTR (SQLERRM, 1, 200),
|
|
'Waarde tbv finance kan niet worden toegevoegd ['
|
|
|| rec.mld_melding_key
|
|
|| '/'
|
|
|| rec.waarde
|
|
|| ']'
|
|
);
|
|
END;
|
|
END LOOP;
|
|
|
|
FOR rec
|
|
IN (SELECT km.mld_melding_key,
|
|
fac.safe_to_number (
|
|
REPLACE (km.mld_kenmerkmelding_waarde, ',', '.')
|
|
)
|
|
waarde
|
|
FROM mld_kenmerkmelding km, mld_kenmerk k
|
|
WHERE km.mld_kenmerk_key = k.mld_kenmerk_key
|
|
AND k.mld_srtkenmerk_key = 564) -- OUD; opkoperswaarde
|
|
LOOP
|
|
BEGIN
|
|
INSERT INTO MLD_KENMERKOPDR (mld_opdr_key, mld_kenmerk_key, mld_kenmerkopdr_waarde)
|
|
SELECT mld_opdr_key, 702, -- NIEUW; opkoperswaarde
|
|
COALESCE (rec.waarde, 0)
|
|
FROM mld_opdr o
|
|
WHERE o.mld_typeopdr_key = 681
|
|
AND o.mld_melding_key = rec.mld_melding_key;
|
|
|
|
COMMIT;
|
|
EXCEPTION
|
|
WHEN OTHERS
|
|
THEN
|
|
fac.imp_writelog (
|
|
'ONCE',
|
|
-1,
|
|
'W',
|
|
'ORACLE error: ' || SUBSTR (SQLERRM, 1, 200),
|
|
'Opkoperswaarde kan niet worden toegevoegd ['
|
|
|| rec.mld_melding_key
|
|
|| '/'
|
|
|| rec.waarde
|
|
|| ']'
|
|
);
|
|
END;
|
|
END LOOP;
|
|
|
|
FOR rec
|
|
IN (SELECT km.mld_melding_key,
|
|
fac.safe_to_number (
|
|
REPLACE (km.mld_kenmerkmelding_waarde, ',', '.')
|
|
)
|
|
waarde
|
|
FROM mld_kenmerkmelding km, mld_kenmerk k
|
|
WHERE km.mld_kenmerk_key = k.mld_kenmerk_key
|
|
AND k.mld_srtkenmerk_key = 568) -- OUD; VVP waarde
|
|
LOOP
|
|
BEGIN
|
|
INSERT INTO MLD_KENMERKMELDING (
|
|
mld_melding_key,
|
|
mld_kenmerk_key,
|
|
mld_kenmerkmelding_waarde
|
|
)
|
|
VALUES (rec.mld_melding_key, 703, -- NIEUW; VVP waarde
|
|
COALESCE (rec.waarde, 0));
|
|
|
|
COMMIT;
|
|
EXCEPTION
|
|
WHEN OTHERS
|
|
THEN
|
|
fac.imp_writelog (
|
|
'ONCE',
|
|
-1,
|
|
'W',
|
|
'ORACLE error: ' || SUBSTR (SQLERRM, 1, 200),
|
|
'VVP waarde kan niet worden toegevoegd ['
|
|
|| rec.mld_melding_key
|
|
|| '/'
|
|
|| rec.waarde
|
|
|| ']'
|
|
);
|
|
END;
|
|
END LOOP;
|
|
|
|
FOR rec
|
|
IN (SELECT km.mld_melding_key,
|
|
fac.safe_to_number (
|
|
REPLACE (km.mld_kenmerkmelding_waarde, ',', '.')
|
|
)
|
|
waarde
|
|
FROM mld_kenmerkmelding km, mld_kenmerk k
|
|
WHERE km.mld_kenmerk_key = k.mld_kenmerk_key
|
|
AND k.mld_srtkenmerk_key = 569) -- OUD; wehkamp waarde
|
|
LOOP
|
|
BEGIN
|
|
INSERT INTO MLD_KENMERKOPDR (mld_opdr_key, mld_kenmerk_key, mld_kenmerkopdr_waarde)
|
|
SELECT mld_opdr_key, 704, -- NIEUW; wehkamp waarde
|
|
COALESCE (rec.waarde, 0)
|
|
FROM mld_opdr o
|
|
WHERE o.mld_typeopdr_key = 681
|
|
AND o.mld_melding_key = rec.mld_melding_key;
|
|
|
|
COMMIT;
|
|
EXCEPTION
|
|
WHEN OTHERS
|
|
THEN
|
|
fac.imp_writelog (
|
|
'ONCE',
|
|
-1,
|
|
'W',
|
|
'ORACLE error: ' || SUBSTR (SQLERRM, 1, 200),
|
|
'Wehkamp waarde kan niet worden toegevoegd ['
|
|
|| rec.mld_melding_key
|
|
|| '/'
|
|
|| rec.waarde
|
|
|| ']'
|
|
);
|
|
END;
|
|
END LOOP;
|
|
END;
|
|
/
|
|
|
|
COMMIT;
|
|
|
|
SPOOL OFF
|
|
@wehk.sql
|