93 lines
3.2 KiB
SQL
93 lines
3.2 KiB
SQL
-- Afm#bestellingen.sql
|
|
--
|
|
--
|
|
SET ECHO ON
|
|
SPOOL xAfm#bestellingen.lst
|
|
PROMPT $Revision$
|
|
|
|
|
|
|
|
DECLARE
|
|
|
|
CURSOR c_cursor
|
|
IS
|
|
SELECT DISTINCT bo.bes_bestelopdr_key
|
|
FROM bes_bestelopdr bo, bes_bestelopdr_item boi, bes_bestelling_item bi, bes_bestelling b
|
|
WHERE bo.bes_bestelopdr_key = boi.bes_bestelopdr_key
|
|
AND boi.bes_bestelopdr_item_key = bi.bes_bestelopdr_item_key
|
|
AND bi.bes_bestelling_key = b.bes_bestelling_key
|
|
AND bes_bestelling_datum < sysdate - 40
|
|
AND bes_bestelling_status IN (3, 4, 5);
|
|
|
|
CURSOR c_cursor2 (pBestelopdr_key IN NUMBER)
|
|
IS
|
|
SELECT boi.bes_bestelopdr_item_key, bi.bes_bestelling_item_key
|
|
FROM ins_tab_discipline d,
|
|
ins_srtgroep g,
|
|
ins_srtdeel s,
|
|
prs_bedrijf bd,
|
|
bes_bestelopdr b,
|
|
bes_bestelling_item bi,
|
|
bes_bestelopdr_item boi,
|
|
bes_bestelling bes
|
|
WHERE d.ins_discipline_module = 'BES'
|
|
AND d.ins_discipline_verwijder IS NULL
|
|
AND d.ins_discipline_key = g.ins_discipline_key
|
|
AND g.ins_srtgroep_key = s.ins_srtgroep_key
|
|
AND s.ins_srtdeel_key = bi.ins_srtdeel_key
|
|
AND bi.bes_bestelling_key = bes.bes_bestelling_key
|
|
AND b.bes_bestelopdr_key = boi.bes_bestelopdr_key
|
|
AND bi.bes_bestelopdr_item_key = boi.bes_bestelopdr_item_key
|
|
AND s.prs_bedrijf_key = bd.prs_bedrijf_key
|
|
AND b.bes_bestelopdr_key = pBestelopdr_key;
|
|
|
|
next_record c_cursor%ROWTYPE;
|
|
next_record2 c_cursor%ROWTYPE;
|
|
|
|
oracle_err_num NUMBER;
|
|
oracle_err_mes VARCHAR2 (200);
|
|
v_errormsg VARCHAR2 (1024);
|
|
|
|
BEGIN
|
|
|
|
FOR next_record IN c_cursor LOOP
|
|
|
|
BEGIN
|
|
|
|
FOR next_record2 IN c_cursor2(next_record.bes_bestelopdr_key) LOOP
|
|
|
|
BEGIN
|
|
|
|
UPDATE bes_bestelopdr_item b
|
|
SET bes_bestelopdr_item_aantalontv = bes_bestelopdr_item_aantal
|
|
,bes_bestelopdr_item_ontvangen = SYSDATE
|
|
WHERE bes_bestelopdr_item_key = next_record2.bes_bestelopdr_item_key;
|
|
|
|
UPDATE bes_bestelling_item
|
|
SET BES_BESTELLING_ITEM_AANTALONTV = bes_bestelling_item_aantal
|
|
WHERE bes_bestelling_item_key = next_record2.bes_bestelling_item_key;
|
|
|
|
EXCEPTION
|
|
WHEN OTHERS
|
|
THEN
|
|
oracle_err_num := SQLCODE;
|
|
oracle_err_mes := SUBSTR (SQLERRM, 1, 100);
|
|
v_errormsg :=
|
|
'(ORACLE error ' || oracle_err_num || '/' || oracle_err_mes
|
|
|| ')';
|
|
END;
|
|
|
|
END LOOP;
|
|
|
|
END;
|
|
|
|
END LOOP;
|
|
|
|
commit;
|
|
|
|
END;
|
|
/
|
|
|
|
SPOOL OFF
|
|
|