1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68
| DECLARE
--variable to update
vNewOrderId ph2_item.order_id%TYPE;
vNewItemId ph2_item.item_id%TYPE := 0;
--Values of the current row of cursor to be compqred
vCrOrderid ph2_item.order_id%TYPE;
vCrCustomerid ph2_item.customer_id%TYPE;
vCrShipdate ph2_item.ship_date%TYPE;
--Values of precedent row to be compared
vCkOrderid ph2_item.order_id%TYPE;
vCkCustomerid ph2_item.customer_id%TYPE;
vCkShipdate ph2_item.ship_date%TYPE;
CURSOR cr_consolid
IS
SELECT
order_id,
customer_id,
ship_date
FROM
ph2_item
GROUP BY
order_id,
customer_id,
order_date,
ship_date
ORDER BY
order_date;
BEGIN
SELECT (MAX(order_id)+1) INTO vNewOrderid FROM sales_order;
vCkOrderid := 1;
vCkCustomerid := 1;
vCkShipdate := '01-JAN-11';
OPEN cr_consolid;
LOOP
FETCH cr_consolid INTO vCrOrderid, vCrCustomerid, vCrShipdate;
EXIT WHEN cr_consolid%NOTFOUND;
IF(vCkCustomerid = vCrCustomerid) AND (vCkShipdate = vCrShipdate) THEN
UPDATE ph2_item
SET order_id = vNewOrderId, item_id = vNewItemId-- ici je dois incrementer l'id pour
WHERE customer_id = vCrCustomerid --chaque ligne qui sera 'updated'
AND ship_date = vCrShipdate;
vNewItemId := vNewItemId +1;
vCkOrderid := vCrOrderid;
vCkCustomerid := vCrCustomerid;
vCkShipdate := vCrShipdate;
ELSE
UPDATE ph2_item
SET order_id = vNewOrderId,
WHERE customer_id = vCrCustomerid
AND ship_date = vCrShipdate;
vNewOrderId := vNewOrderId +1;
vNewItemId :=1;
vCkOrderid := vCrOrderid;
vCkCustomerid := vCrCustomerid;
vCkShipdate := vCrShipdate;
END IF;
END LOOP;
CLOSE cr_consolid;
END;
/ |
Partager