DECLARE
TYPE MultiSite IS TABLE OF ph2b_item%ROWTYPE;
mname MultiSite;
NewOrderId ph2b_item.order_id%TYPE;
NewItemId ph2b_item.item_id%TYPE := 0;

CURSOR cr_consolid
	IS
		SELECT
		  oid.order_id||oid.customer_id||oid.ship_date cr_id
		FROM
		(SELECT 
		  order_id,
		  customer_id,
		  ship_date, 
		  order_date
		FROM
		  ph2b_item
		GROUP BY
		  order_id,
		  customer_id,
		  ship_date, 
		  order_date) oid
		ORDER BY
		  oid.order_date;
		--FOR UPDATE OF ph2b_item;		
BEGIN
	SELECT (MAX(order_id)+1) INTO NewOrderId FROM ph2b_item;
	SELECT (i1.order_id||i1.customer_id||i1.ship_date) BULK COLLECT INTO mname 
	FROM  ph2b_item i1  JOIN  ph2b_item i2
	ON	i1.ship_date = i2.ship_date  AND  i1.customer_id = i2.customer_id AND   i1.order_id <> i2.order_id   
	WHERE i1.customer_id IN 
		(SELECT  ns.customer_id
		 FROM	 ph2_numsites_vw ns
		 WHERE   ns.num_sites>1)
	GROUP BY   (i1.order_id||i1.customer_id||i1.ship_date);
	
FOR to_consolid IN cr_consolid 
	LOOP
		FOR i IN mname.first..mname.last 
		LOOP
			IF ((to_consolid.cr_id) = mname)			
			THEN 
				UPDATE ph2b_item i
				SET i.order_id = NewOrderId, i.item_id = NewItemId
				WHERE CURRENT OF to_consolid;
				NewItemId := NewItemId+1;
			ELSE
				UPDATE ph2b_item i
				SET i.order_id = NewOrderId
				WHERE CURRENT OF to_consolid;
			END IF;
		END LOOP;
	NewOrderId := NewOrderId + 1;
	END LOOP; 
END;
/










 */