Tuesday, June 21, 2011

Script to Add #WFM_CC Attribute to the orders

-- Cursor for already booked orders
DECLARE
CURSOR cur_err_orders
IS
SELECT wias.item_key
FROM wf_item_activity_statuses wias, wf_process_activities wpa
WHERE 1 = 1
AND wias.item_type = 'OEOH'
AND wias.activity_status = 'ERROR'
AND instance_label = 'XXDP_ORD_BOOK_ACK_SALES_REP_FU'
--'XXDP_ORD_BOOK_ACK_FUNC'
AND wias.process_activity = wpa.instance_id;

--AND wias.item_key = '1094113';

-- Cursor for Entered Orders
CURSOR cur_entered_ord
IS
SELECT DISTINCT h.header_id
FROM oe_order_headers_all h, oe_workflow_assignments w
WHERE TRUNC (h.creation_date) < '05-AUG-2010'
AND flow_status_code = 'ENTERED'
--and trunc(booked_date)>='05-AUG-2010'
AND h.order_type_id = w.order_type_id
AND w.end_date_active IS NULL
AND process_name IN (
SELECT DISTINCT p.process_name
FROM wf_process_activities p,
wf_process_activities a
WHERE p.instance_label = a.process_name
AND a.instance_label =
'XXDP_ORD_BOOK_ACK_SALES_REP_FU');
--AND h.header_id = 1129493;
BEGIN
FOR rec_err_orders IN cur_err_orders
LOOP
wf_engine.additemattr ('OEOH',
rec_err_orders.item_key,
'#WFM_CC',
NULL,
NULL,
NULL
);
COMMIT;
END LOOP;

FOR rec_entered_ord IN cur_entered_ord
LOOP
wf_engine.additemattr ('OEOH',
rec_entered_ord.header_id,
'#WFM_CC',
NULL,
NULL,
NULL
);
COMMIT;
END LOOP;
END;
/

No comments:

Post a Comment

Best Blogger TipsGet Flower Effect