DECLARE
l_header_id NUMBER := 1860455; --Input sales order header_id
l_count NUMBER := 0;
l_user NUMBER := 110816;
l_resp NUMBER := 64421;
l_appl NUMBER := 660;
wf_process VARCHAR2 (100);
l_org_id NUMBER := -99;
l_item_type VARCHAR2 (30);
p_line_rec OE_Order_PUB.Line_Rec_Type;
l_aname wf_engine.nametabtyp;
l_aname2 wf_engine.nametabtyp;
l_avalue wf_engine.numtabtyp;
l_avaluetext wf_engine.texttabtyp;
l_user_name VARCHAR2 (100);
CURSOR items
IS
SELECT h.org_id,
h.order_number,
h.header_id,
h.flow_status_code,
h.open_flag,
h.booked_flag,
h.creation_date,
h.order_type_id,
h.order_category_code,
TO_CHAR (h.header_id) item_key
FROM oe_order_headers_all h
WHERE h.open_flag = 'Y'
AND NVL (h.cancelled_flag, 'N') = 'N'
AND h.header_id = l_header_id
AND NOT EXISTS
(SELECT 1
FROM wf_items itm
WHERE itm.item_type = 'OEOH'
AND itm.item_key = TO_CHAR (h.header_id))
ORDER BY h.org_id, h.order_number;
FUNCTION Get_ProcessName (
p_itemtype IN VARCHAR2,
p_itemkey IN VARCHAR2,
p_wfasgn_item_type IN VARCHAR2 := FND_API.G_MISS_CHAR)
RETURN VARCHAR2
IS
l_process_name VARCHAR2 (30) := NULL;
CURSOR find_HdrProcessname (
itemkey VARCHAR2)
IS
SELECT wf_assign.process_name
FROM oe_workflow_assignments wf_assign, oe_order_headers header
WHERE header.header_id = TO_NUMBER (itemkey)
AND header.order_type_id = wf_assign.order_type_id
AND SYSDATE >= wf_assign.start_date_active
AND SYSDATE <= NVL (wf_assign.end_date_active, SYSDATE)
AND wf_assign.line_type_id IS NULL;
CURSOR find_LineProcessname (
itemkey VARCHAR2)
IS
SELECT wf_assign.process_name
FROM oe_workflow_assignments wf_assign,
oe_order_headers header,
oe_order_lines line
WHERE line.line_id = TO_NUMBER (itemkey)
AND NVL (p_wfasgn_item_type, '-99') =
NVL (wf_assign.item_type_code,
NVL (p_wfasgn_item_type, '-99'))
AND header.header_id = line.header_id
AND header.order_type_id = wf_assign.order_type_id
AND line.line_type_id = wf_assign.line_type_id
AND wf_assign.line_type_id IS NOT NULL
AND SYSDATE >= wf_assign.start_date_active
AND SYSDATE <= NVL (wf_assign.end_date_active, SYSDATE)
ORDER BY wf_assign.item_type_code;
--
--
BEGIN
IF (p_itemtype = OE_GLOBALS.G_WFI_HDR)
THEN
OPEN find_HdrProcessname (p_itemkey);
FETCH find_HdrProcessname INTO l_process_name;
CLOSE find_HdrProcessname;
ELSE
OPEN find_LineProcessname (p_itemkey);
FETCH find_LineProcessname INTO l_process_name;
CLOSE find_LineProcessname;
END IF;
RETURN l_process_name;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
RAISE;
WHEN OTHERS
THEN
RAISE;
END Get_ProcessName;
BEGIN
DBMS_OUTPUT.put_line (
'Org id: order number: header id: status: booked flag: open flag: created: WF process');
DBMS_OUTPUT.put_line ('----------------------------------');
DELETE wf_items
WHERE item_key = '1860455' AND item_type = 'OEOH'; --Input sales order header_id
COMMIT;
DELETE WF_ITEM_ATTRIBUTE_VALUES ATV
WHERE ATV.ITEM_TYPE = 'OEOH' AND ATV.ITEM_KEY = '1860455'; --Input sales order header_id
COMMIT;
FOR c IN items
LOOP
BEGIN
SAVEPOINT loop_start;
IF l_org_id <> c.org_id
THEN
l_org_id := c.org_id;
fnd_client_info.set_org_context (l_org_id);
END IF;
wf_process := NULL;
wf_process := Get_ProcessName (OE_GLOBALS.G_WFI_HDR, c.item_key);
DBMS_OUTPUT.put_line (
TO_CHAR (c.org_id)
|| ': '
|| TO_CHAR (c.order_number)
|| ': '
|| c.item_key
|| ': '
|| c.flow_status_code
|| ': '
|| c.booked_flag
|| ': '
|| c.open_flag
|| ': '
|| TO_CHAR (c.creation_date)
|| ': '
|| wf_process);
SELECT COUNT (*)
INTO l_count
FROM DUAL
WHERE EXISTS
(SELECT 1
FROM fnd_responsibility
WHERE application_id = l_appl
AND responsibility_id = l_resp)
AND EXISTS
(SELECT 1
FROM fnd_user
WHERE user_id = l_user);
IF wf_process IS NULL OR wf_process LIKE 'UPG%'
THEN
DBMS_OUTPUT.put_line ('No eligible workflow assignment found.');
ELSIF l_user IS NOT NULL
AND l_resp IS NOT NULL
AND l_appl IS NOT NULL
AND l_count > 0
AND l_org_id = fnd_profile.value_specific ('ORG_ID',
l_user,
l_resp,
l_appl)
THEN
fnd_global.apps_initialize (l_user, l_resp, l_appl);
WF_ENGINE.CreateProcess (OE_Globals.G_WFI_HDR,
c.item_key,
wf_process);
-- Set various Header Attributes
l_aname (1) := 'USER_ID';
l_avalue (1) := l_user;
l_aname (2) := 'APPLICATION_ID';
l_avalue (2) := l_appl;
l_aname (3) := 'RESPONSIBILITY_ID';
l_avalue (3) := l_resp;
l_aname (4) := 'ORG_ID';
l_avalue (4) := l_org_id;
l_aname (5) := 'ORDER_NUMBER';
l_avalue (5) := c.order_number;
wf_engine.SetItemAttrNumberArray (OE_GLOBALS.G_WFI_HDR,
c.item_key,
l_aname,
l_avalue);
/* new logic to get FROM_ROLE */
BEGIN
SELECT user_name
INTO l_user_name
FROM fnd_user
WHERE user_id = l_user;
EXCEPTION
WHEN OTHERS
THEN
l_user_name := NULL; -- do not set FROM_ROLE then
END;
l_aname2 (1) := 'ORDER_CATEGORY';
l_avaluetext (1) := c.order_category_code;
l_aname2 (2) := 'NOTIFICATION_APPROVER';
l_avaluetext (2) :=
FND_PROFILE.VALUE_specific ('OE_NOTIFICATION_APPROVER',
l_user,
l_resp,
l_appl);
l_aname2 (3) := 'NOTIFICATION_FROM_ROLE';
l_avaluetext (3) := l_user_name;
wf_engine.SetItemAttrTextArray (OE_GLOBALS.G_WFI_HDR,
c.item_key,
l_aname2,
l_avaluetext);
IF c.booked_flag = 'Y' AND c.flow_status_code = 'BOOKED'
THEN
wf_engine.handleerror (OE_GLOBALS.G_WFI_HDR,
c.item_key,
'BOOK_ORDER',
'SKIP',
'COMPLETE');
ELSIF c.flow_status_code = 'ENTERED'
THEN
wf_engine.startprocess (OE_GLOBALS.G_WFI_HDR, c.item_key);
END IF;
ELSE
DBMS_OUTPUT.put_line (
'User, responsibility and appl id do not match org.');
END IF;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (SQLERRM);
ROLLBACK TO loop_start;
END;
END LOOP;
COMMIT;
END;
