DECLARE
l_result VARCHAR2 (30);
p_line_rec OE_Order_PUB.Line_Rec_Type;
l_line_process_name VARCHAR2 (30);
l_item_type VARCHAR2 (30);
l_aname wf_engine.nametabtyp;
l_aname2 wf_engine.nametabtyp;
l_avalue wf_engine.numtabtyp;
l_avaluetext wf_engine.texttabtyp;
l_process_activity NUMBER;
line VARCHAR2 (240);
l_org_id NUMBER := -99;
CURSOR items
IS
SELECT l.org_id,
h.order_number,
TO_CHAR (l.line_id) item_key,
l.line_id,
l.flow_status_code,
l.open_flag,
l.booked_flag,
l.creation_date,
l.line_type_id,
h.order_type_id,
l.item_type_code,
l.shipped_quantity shq,
l.line_category_code cat,
l.fulfilled_flag,
l.invoice_interface_status_code,
l.cancelled_flag,
l.ato_line_id
FROM oe_order_lines_all l, oe_order_headers_all h, wf_items hdr_wf
WHERE l.header_id = h.header_id
AND TO_CHAR (l.header_id) = hdr_wf.item_key
AND hdr_wf.item_type = OE_GLOBALS.G_WFI_HDR
AND l.line_id IN (7446995, 7447003, 7447004) -- Input sales order line_id
AND (l.open_flag = 'Y' AND NVL (l.cancelled_flag, 'N') = 'N')
AND NOT EXISTS
(SELECT 1
FROM wf_items itm
WHERE itm.item_type = OE_GLOBALS.G_WFI_LIN
AND itm.item_key = TO_CHAR (l.line_id))
ORDER BY l.org_id, h.order_number, l.line_id;
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);
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;
--
l_debug_level CONSTANT NUMBER := 5; --oe_debug_pub.g_debug_level;
--
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;
/*
IF l_process_name IS NULL THEN
RAISE NO_DATA_FOUND;
END IF;
*/
IF l_debug_level > 0
THEN
oe_debug_pub.add ('PROCESS NAME IS ' || L_PROCESS_NAME);
END IF;
IF l_debug_level > 0
THEN
oe_debug_pub.add ('EXITING GET_PROCESSNAME');
END IF;
RETURN l_process_name;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
oe_debug_pub.add ('Could not find root flow');
RAISE;
WHEN OTHERS
THEN
RAISE;
END Get_ProcessName;
BEGIN
DBMS_OUTPUT.put_line (
'Org id: order number: line id: status: booked flag: open flag: created: WF process');
DBMS_OUTPUT.put_line ('----------------------------------');
DELETE FROM wf_items
WHERE item_key IN
(7446995, 7447003, 7447004) -- Input sales order line_id
AND item_type = 'OEOL';
COMMIT;
DELETE FROM WF_ITEM_ATTRIBUTE_VALUES ATV
WHERE ATV.ITEM_TYPE = 'OEOL'
AND ATV.ITEM_KEY IN
(7446995, 7447003, 7447004); -- Input sales order line_id
COMMIT;
FOR c IN items
LOOP
BEGIN
SAVEPOINT loop_start;
IF NVL (l_org_id, -99) <> NVL (c.org_id, -99)
THEN
l_org_id := c.org_id;
fnd_client_info.set_org_context (c.org_id);
END IF;
--FND_PROFILE.PUT( 'OE_DEBUG_LOG_DIRECTORY', '/sqlcom/out/omptmast' );
oe_debug_pub.setdebuglevel (5);
p_line_rec := OE_LINE_UTIL.query_row (c.line_id);
l_item_type := OE_Order_Wf_Util.get_wf_item_type (p_line_rec);
l_line_process_name := NULL;
l_line_process_name :=
Get_ProcessName (OE_GLOBALS.G_WFI_LIN,
p_Line_rec.line_id,
l_item_type);
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)
|| ': '
|| l_line_process_name
|| ':');
IF l_line_process_name IS NOT NULL
AND l_line_process_name NOT LIKE 'UPG%'
THEN
OE_Order_Wf_Util.Set_Line_User_Key (p_line_rec);
line := SUBSTRB (fnd_message.get, 1, 240);
-- Create Line Work item
WF_ENGINE.CreateProcess (OE_Globals.G_WFI_LIN,
TO_CHAR (p_Line_rec.line_id),
l_line_process_name,
line);
oe_debug_pub.add ('After WF_ENGINE.CreateProcess');
oe_debug_pub.add ('G_ORG_ID : ' || OE_GLOBALS.G_ORG_ID);
oe_debug_pub.add ('l_org_id : ' || c.org_id);
-- Set various Line Attributes
l_aname (1) := 'USER_ID';
l_avalue (1) :=
wf_engine.GetItemAttrNumber (OE_GLOBALS.G_WFI_HDR,
TO_CHAR (p_line_rec.header_id),
'USER_ID');
l_aname (2) := 'APPLICATION_ID';
l_avalue (2) :=
wf_engine.GetItemAttrNumber (OE_GLOBALS.G_WFI_HDR,
TO_CHAR (p_line_rec.header_id),
'APPLICATION_ID');
l_aname (3) := 'RESPONSIBILITY_ID';
l_avalue (3) :=
wf_engine.GetItemAttrNumber (OE_GLOBALS.G_WFI_HDR,
TO_CHAR (p_line_rec.header_id),
'RESPONSIBILITY_ID');
fnd_global.apps_initialize (l_avalue (1),
l_avalue (3),
l_avalue (2));
l_aname (4) := 'ORG_ID';
l_avalue (4) := c.org_id;
oe_debug_pub.add ('Setting Item Attr Number');
wf_engine.SetItemAttrNumberArray (OE_GLOBALS.G_WFI_LIN,
p_line_rec.line_id,
l_aname,
l_avalue);
l_aname2 (1) := 'LINE_CATEGORY';
l_avaluetext (1) := p_line_rec.line_category_code;
l_aname2 (2) := 'NOTIFICATION_APPROVER';
l_avaluetext (2) :=
FND_PROFILE.VALUE_SPECIFIC ('OE_NOTIFICATION_APPROVER',
l_avalue (1),
l_avalue (3),
l_avalue (2));
oe_debug_pub.add ('user id : ' || l_avalue (1));
oe_debug_pub.add ('appl id : ' || l_avalue (2));
oe_debug_pub.add ('resp id : ' || l_avalue (3));
oe_debug_pub.add ('catgr code : ' || l_avaluetext (1));
oe_debug_pub.add ('notif approver : ' || l_avaluetext (2));
oe_debug_pub.add ('Setting Item Attr Text');
wf_engine.SetItemAttrTextArray (OE_GLOBALS.G_WFI_LIN,
p_line_rec.line_id,
l_aname2,
l_avaluetext);
oe_debug_pub.add ('Setting Parrent');
WF_ITEM.Set_Item_Parent (OE_Globals.G_WFI_LIN,
TO_CHAR (p_Line_rec.line_id),
OE_GLOBALS.G_WFI_HDR,
TO_CHAR (p_Line_rec.header_id),
'');
oe_debug_pub.add ('Starting a process');
IF c.flow_status_code = 'AWAITING_SHIPPING'
THEN
wf_engine.handleerror ('OEOL',
c.item_key,
'SHIP_LINE',
'RETRY',
NULL);
BEGIN
SELECT st.process_activity
INTO l_process_activity
FROM wf_item_activity_statuses st,
wf_process_activities wpa
WHERE wpa.instance_id = st.process_activity
AND st.item_type = 'OEOL'
AND wpa.activity_name = 'SHIP_LINE'
AND st.activity_status = 'ERROR'
AND st.item_key = c.item_key
AND EXISTS
(SELECT 1
FROM wsh_delivery_details
WHERE source_line_id =
TO_NUMBER (item_key)
AND source_code = 'OE'
-- and released_status = 'C'
AND oe_interfaced_flag = 'N');
WF_ITEM_ACTIVITY_STATUS.Create_Status (
itemtype => 'OEOL',
itemkey => c.item_key,
actid => l_process_activity,
status => wf_engine.eng_notified,
result => wf_engine.eng_null,
beginning => SYSDATE,
ending => NULL);
EXCEPTION
WHEN NO_DATA_FOUND
THEN
NULL;
END;
ELSIF c.flow_status_code = 'SHIPPED' AND NVL (c.shq, 0) > 0
THEN
wf_engine.handleerror ('OEOL',
c.item_key,
'SHIP_LINE',
'SKIP',
'SHIP_CONFIRM');
ELSIF c.flow_status_code IN
('AWAITING_FULFILLMENT', 'SHIPPED')
AND c.cat = 'ORDER'
AND NVL (c.shq, 0) > 0
THEN
wf_engine.handleerror ('OEOL',
c.item_key,
'SHIP_LINE',
'SKIP',
'SHIP_CONFIRM');
ELSIF c.flow_status_code = 'AWAITING_FULFILLMENT'
AND c.cat = 'ORDER'
AND NVL (c.shq, 0) = 0
THEN
wf_engine.handleerror ('OEOL',
c.item_key,
'SHIP_LINE',
'SKIP',
'NON_SHIPPABLE');
ELSIF c.flow_status_code = 'AWAITING_FULFILLMENT'
AND c.cat = 'RETURN'
AND c.shq IS NOT NULL
THEN
wf_engine.handleerror ('OEOL',
c.item_key,
'RMA_WAIT_FOR_RECEIVING',
'SKIP',
'COMPLETE');
wf_engine.handleerror ('OEOL',
c.item_key,
'RMA_WAIT_FOR_INSPECTION',
'SKIP',
'COMPLETE');
ELSIF c.flow_status_code = 'AWAITING_FULFILLMENT'
AND c.cat = 'RETURN'
AND c.shq IS NULL
THEN
wf_engine.handleerror ('OEOL',
c.item_key,
'RMA_WAIT_FOR_RECEIVING',
'SKIP',
'NOT_ELIGIBLE');
ELSIF c.flow_status_code = 'FULFILLED' AND c.fulfilled_flag = 'Y'
THEN
wf_engine.handleerror ('OEOL',
c.item_key,
'FULFILL_LINE',
'SKIP',
NULL);
ELSIF c.flow_status_code = 'INVOICED'
AND c.invoice_interface_status_code = 'YES'
THEN
wf_engine.handleerror ('OEOL',
c.item_key,
'INVOICE_INTERFACE',
'SKIP',
'COMPLETE');
ELSIF c.flow_status_code = 'CLOSED' AND c.open_flag = 'N'
THEN
wf_engine.handleerror ('OEOL',
c.item_key,
'CLOSE_CONT_H',
'RETRY',
NULL);
ELSIF c.flow_status_code = 'CANCELLED'
AND c.open_flag = 'N'
AND c.cancelled_flag = 'Y'
THEN
wf_engine.handleerror ('OEOL',
c.item_key,
'CLOSE_CONT_H',
'RETRY',
NULL);
ELSIF c.open_flag = 'Y'
AND NVL (c.cancelled_flag, 'N') = 'N'
AND ( c.flow_status_code = 'ENTERED'
OR ( c.flow_status_code = 'BOOKED'
AND c.booked_flag = 'Y'
AND ( c.ato_line_id IS NULL
OR c.ato_line_id <> c.line_id)))
THEN
wf_engine.startprocess ('OEOL', c.item_key);
END IF;
ELSE
DBMS_OUTPUT.put_line ('No eligible workflow assignment found.');
END IF; -- WF process is not null
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (SQLERRM);
ROLLBACK TO loop_start;
END;
END LOOP;
DBMS_OUTPUT.put_line (
'File name ' || OE_DEBUG_PUB.G_DIR || '/' || OE_DEBUG_PUB.G_FILE);
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
oe_debug_pub.add ('Exception raised');
RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
END;
very interesting article I feel very enthusiastic while reading and the information
ReplyDeleteprovided in this article is so useful for me. Content in this article guides in clarifying some of my doubts.
oracle fusion financials training
oracle fusion financials online training
oracle fusion Procurement training training
oracle fusion procurement online training