Need help for a pl sql code

Hello,

I have 5759 rows in my table ph2_item.

My phase2b1_vw, give me the number of site by customer:
select cus.customer_id, count(cus.nb) nb_site from (select customer_id, count(substr(OLD_SYS_REF,1,instr(OLD_SYS_REF,':')-1)) nb,
substr(OLD_SYS_REF,1,instr(OLD_SYS_REF,':')-1) site
from ph2_item group by customer_id, substr(OLD_SYS_REF,1,instr(OLD_SYS_REF,':')-1)
order by customer_id) cus
group by cus.customer_id;

CUSTOMER_ID    NB_SITE
----------- ----------
        100          1
        227          1
        235          1
        207          1
        222          2
        224          1
        108          1
        205          1
        229          1
        201          5


I need to update the order_id and item_id:
DECLARE
        CURSOR ph2_item_Cur IS
                SELECT  item.ORDER_ID,
                        item.item_id,
                        item.customer_id,
                        item.ship_date,
                        vw.NB_SITE
                from   ph2_item item,
                       phase2b1_vw vw
                where item.customer_id=vw.customer_id
                order by item.order_date
                for update of order_id, item_id nowait;
        v_item ph2_item.item_id%TYPE;
        v_order ph2_item.order_id%TYPE;
        v_customer ph2_item.customer_id%TYPE;
        v_ship_date ph2_item.ship_date%TYPE;
        v_maxi ph2_item.order_id%TYPE ;
        row ph2_item%ROWTYPE;
BEGIN
        SELECT max(order_id) into v_maxi from sales_order;

        FOR row in ph2_item_Cur
                LOOP
                        IF ((row.NB_SITE>1) and
                        (row.customer_id=v_customer)
                        and (row.ship_date=v_ship_date) and (row.order_id<>v_order)) or (row.order_id=v_order)
                        THEN
                                v_item := v_item + 1;
                        ELSE
                                v_maxi := v_maxi + 1;
                                v_item := 1;
                        END IF;

                        update ph2_item
                        set   order_id=v_maxi,
                              item_id=v_item
                        where CURRENT OF ph2_item_Cur;

                        v_order:=row.order_id;
                        v_customer:=row.customer_id;
                        v_ship_date:=row.ship_date;
                END LOOP;

        commit;
END;
/

This is my problem :
- I have no error message
- the max order_id in ph2_item is not correct.  
- number of distinct order_ids in ph2_item is not correct.  
- sum of item_ids is NOT as expected.  

I've made several test and each time I've got several different results :(

I don't understand where is my problem.

Start Free Trial