Create 2 new attributes:
Create new custom function add this 2 attributes and apply in the workflow
Package code at the end of the document.
Update message to show our attributes:
CREATE OR REPLACE package APPS.XXFUJ_WF_PR as
procedure set_selected_approvalPath(itemtype in varchar2
,itemkey in varchar2
,actid in number
,funmode in varchar2
,result out nocopy varchar2) ;
end;
CREATE OR REPLACE package body APPS.XXFUJ_WF_PR as
procedure set_selected_approvalPath(itemtype in varchar2
,itemkey in varchar2
,actid in number
,funmode in varchar2
,result out nocopy varchar2)
is
l_APPROVAL_PATH_ID number;
l_APPROVAL_PATH_DESC VARCHAR2(2000);
l_selected_person number;
l_sel_person_username varchar2(200);
l_selected_person_id number;
l_sel_function_name varchar2(200);
l_sel_absence_name varchar2(200);
l_sel_request_id varchar2(200);
l_sel_employee_num varchar2(200);
l_sel_leave_reason varchar2(200);
L_DOCUMENT_ID NUMBER;
L_ATTRIBUTE1 varchar2(200);L_ATTRIBUTE2 varchar2(200);
L_ATTRIBUTE1_ID VARCHAR2(200);
L_ATTRIBUTE2_ID VARCHAR2(200);
begin
--get the transaction_id to use in getting the selected_person_id
l_APPROVAL_PATH_ID := wf_engine.GetItemAttrNumber(ITEMTYPE => ITEMTYPE,
ITEMKEY => ITEMKEY,
ANAME => 'APPROVAL_PATH_ID'
);
L_DOCUMENT_ID := wf_engine.GetItemAttrNumber(ITEMTYPE => ITEMTYPE,
ITEMKEY => ITEMKEY,
ANAME => 'DOCUMENT_ID'
);
-- get the selected_person_id
BEGIN
SELECT NAME
INTO l_APPROVAL_PATH_DESC
FROM PER_POSITION_STRUCTURES_V
WHERE POSITION_STRUCTURE_ID = l_APPROVAL_PATH_ID ;
EXCEPTION
WHEN NO_DATA_FOUND THEN l_APPROVAL_PATH_DESC := '' ;
WHEN OTHERS THEN l_APPROVAL_PATH_DESC := '' ;
END;
BEGIN
SELECT ATTRIBUTE1, ATTRIBUTE2
INTO L_ATTRIBUTE1_ID,L_ATTRIBUTE2_ID
FROM PO_REQUISITION_HEADERS_ALL POH
WHERE REQUISITION_HEADER_ID = L_DOCUMENT_ID;
EXCEPTION WHEN OTHERS THEN
L_ATTRIBUTE1 := ' ';
L_ATTRIBUTE2 := ' ';
END;
BEGIN
SELECT L_ATTRIBUTE2_ID||'-'||DESCRIPTION
INTO L_ATTRIBUTE2 FROM FND_FLEX_VALUES_VL
WHERE
FLEX_VALUE = L_ATTRIBUTE2_ID--POH.ATTRIBUTE1
AND FLEX_VALUE_SET_ID = 1019523 --'FIA_COST CENTER'
AND ROWNUM =1;
EXCEPTION WHEN OTHERS THEN
L_ATTRIBUTE2 := ' ';
-- L_ATTRIBUTE2 := ' ';
END;
BEGIN
SELECT DESCRIPTION
INTO L_ATTRIBUTE1 FROM FND_FLEX_VALUES_VL
WHERE
FLEX_VALUE = L_ATTRIBUTE1_ID--POH.ATTRIBUTE1
AND FLEX_VALUE_SET_ID = 1019810
AND ROWNUM =1;
EXCEPTION WHEN OTHERS THEN
L_ATTRIBUTE1 := 'Routine';
-- L_ATTRIBUTE2 := ' ';
END;
-- l_APPROVAL_PATH_DESC := 'Testing Desc';
wf_engine.SetItemAttrText(itemtype => itemtype,
itemkey => itemkey,
aname => 'APPROVAL_PATH_ID_DESC',
avalue => l_APPROVAL_PATH_DESC );
wf_engine.SetItemAttrText(itemtype => itemtype,
itemkey => itemkey,
aname => 'XXATTRIBUTE1',
avalue => NVL(L_ATTRIBUTE1,'Routine') );
wf_engine.SetItemAttrText(itemtype => itemtype,
itemkey => itemkey,
aname => 'XXATTRIBUTE2',
avalue => NVL(L_ATTRIBUTE2,'') );
wf_engine.SetItemAttrText(itemtype => itemtype,
itemkey => itemkey,
aname => 'XXJUSTIFICATION',
avalue => NVL('Hello Justification','') );
wf_engine.SetItemAttrText(itemtype => itemtype,
itemkey => itemkey,
aname => 'XXACCOUNT',
avalue => NVL('Hello XXACCOUNT','') );
exception
when others then
wf_engine.SetItemAttrText(itemtype => itemtype,
itemkey => itemkey,
aname => 'APPROVAL_PATH_ID_DESC',
avalue => '' );
wf_engine.SetItemAttrText(itemtype => itemtype,
itemkey => itemkey,
aname => 'XXATTRIBUTE1',
avalue => '' );
wf_engine.SetItemAttrText(itemtype => itemtype,
itemkey => itemkey,
aname => 'XXATTRIBUTE2',
avalue => '' );
wf_core.context('XXFUJ_WF_PR','set_selected_approvalPath',SQLCODE || SQLERRM);
raise;
end set_selected_approvalPath;
end;
