Requirement to add Empno and Leave Name in the Notification Subject sent to the approver:
- Create package in database XXZAMEL_WF_SS.
Code of package is at the end of the post.
- Identify the item_Key , message_name from the below query.
select item_key,message_name from wf_notifications
WHERE NOTIFICATION_ID = 9589048
Notification Message Name: HR_EMBD_NTFY_APPROVAL_FWD_MSG
Approval Message Name: HR_EMBED_RN_NTF_APPR_MSG
- Get the process name from the below query.
select process_name, TRANSACTION_ID from hr_api_transactions where item_key = :item_key_step2
HR_GENERIC_APPROVAL_PRC
- Open the workflow builder, and then open the process name mentioned in step3.
- Create 2 attributes under Attributes.
- SELECT_ABSENCE_NAME
- SELECT_EMPLOYEE_NUMBER
- Copy(right click copy) Under Messages ‘HR_EMBED_RN_NTF_APPR_MSG’
and paste (right click paste) with new name as ‘XX_HR_EMBED_RN_NTF_APPR_MSG’
Open the properties of Message and in Body Subject add our new attributes as below.
&HR_APPROVER_MSG_SUBJECT_ATTR -> &SELECT_EMPLOYEE_NUMBER -> &SELECT_ABSENCE_NAME
- Copy/drag the new 2 attributes created in step 5, paste/drag under the new message created in step6.
- Copy the notification ‘HR_APPROVER_NTF’ paste it with the new name ‘XX_HR_APPROVER_NTF’.
In the properties of Notification change the message to our new custom message created in step7.
- Under Functions, create new function internal name as ‘XX_HR_APPR_SET_VALUES’, drag/paste the 2 new attributes under the new function as shown below.
Function Name: give the package.procedure which we created in step1.
XXZAMEL_WF_SS.set_selected_person
- Open the process ‘HR_APPROVAL_NTF_PRC’
Take the screenshot and save it, as we will delete the HR_APPROVER_NTF message selected in the above image.
Once deleted, drag the XX_ HR_APPROVER_NTF notification and connect the same as before.
The blue lines shows the connectivity is done twice, Example: Resubmit and then again Approve.
Don’t worry about the function, we are going to add in the next step.
- Drag the custom function we created between new notification and OR function as below shown.
Open the properties of our Notification and go to the Node Tab.
Performer Type: ItemAttribtue
Value: Forward to Username
- Save and test the solution.
dfdPackage code:
/*--------------------------------------- (XXZAMEL_WF_SS) Package ------------------------------------------------*/
CREATE OR REPLACE package APPS.XXZAMEL_WF_SS as
procedure set_selected_person(itemtype in varchar2
,itemkey in varchar2
,actid in number
,funmode in varchar2
,result out nocopy varchar2) ;
end;
/
/*----------------------------------------- Package Body -------------------------------------*/
/*--------------------------------------------------------------------------------------------*/
CREATE OR REPLACE package body APPS.XXZAMEL_WF_SS as
---- preocedue set_selected_function gets the selected person in HRSS as per transaction_id from hr_api_transactions table
-- created by HNasr 0ctober-2015
procedure set_selected_person(itemtype in varchar2
,itemkey in varchar2
,actid in number
,funmode in varchar2
,result out nocopy varchar2)
is
l_transaction_id number;
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);
begin
--get the transaction_id to use in getting the selected_person_id
l_transaction_id := wf_engine.GetItemAttrNumber(ITEMTYPE => ITEMTYPE,
ITEMKEY => ITEMKEY,
ANAME => 'TRANSACTION_ID'
);
-- get the selected_person_id
BEGIN
SELECT selected_person_id
into l_selected_person
FROM HR_API_TRANSACTIONS
WHERE TRANSACTION_ID = l_transaction_id
and selected_person_id <> creator_person_id;
EXCEPTION
WHEN NO_DATA_FOUND THEN l_selected_person_id := 0 ;
WHEN OTHERS THEN l_selected_person_id := 0 ;
END;
-- get the username for the selected_perosn to send him the notification
begin
select user_name
into l_sel_person_username
from fnd_user
where employee_id = l_selected_person
and rownum = 1
and (trunc(SYSDATE) BETWEEN trunc(start_date) AND trunc(end_date) or end_date is null )
ORDER BY LAST_UPDATE_DATE DESC;
exception
when no_data_found then l_sel_person_username := 'SYSADMIN';
WHEN OTHERS THEN l_sel_person_username := 'SYSADMIN';
end;
begin
select distinct fu.USER_FUNCTION_NAME
into l_sel_function_name
from hr_api_transactions ap,
FND_FORM_FUNCTIONS_VL fu
where fu.function_id = ap.function_id
and ap.TRANSACTION_ID= l_transaction_id;
exception
when no_data_found then null;
WHEN OTHERS THEN null;
end;
begin
select abs.name
into l_sel_absence_name
from hr_api_transaction_steps stp,
hr_api_transactions tra,
--per_absence_attendance_types abs
per_abs_attendance_types_tl abs
where stp.TRANSACTION_ID = tra.TRANSACTION_ID
and abs.ABSENCE_ATTENDANCE_TYPE_ID = stp.INFORMATION5
and tra.TRANSACTION_ID= l_transaction_id
and abs.language = 'AR';--userenv('LANG'); as its not working getting english in both instances
exception
when no_data_found then null;
WHEN OTHERS THEN null;
end;
begin
select val.VARCHAR2_VALUE
into l_sel_request_id
from hr_api_transaction_steps stp,
hr_api_transaction_values val
where stp.TRANSACTION_STEP_ID = val.TRANSACTION_STEP_ID
and val.NAME = 'P_INFORMATION1_1'
and stp.TRANSACTION_STEP_ID= l_transaction_id;
exception
when no_data_found then null;
WHEN OTHERS THEN null;
end;
begin
select DISTINCT per.EMPLOYEE_NUMBER
into l_sel_employee_num
from per_all_people_f per,
hr_api_transactions tran
where per.PERSON_ID = tran.SELECTED_PERSON_ID
and tran.TRANSACTION_ID= l_transaction_id
AND ROWNUM = 1;
exception
when no_data_found then null;
WHEN OTHERS THEN null;
end;
begin
select per.ABS_INFORMATION1
into l_sel_leave_reason
from per_absence_attendances per,
hr_api_transactions tran
where per.PERSON_ID = tran.SELECTED_PERSON_ID
and tran.TRANSACTION_ID= l_transaction_id;
exception
when no_data_found then null;
WHEN OTHERS THEN null;
end;
/*
wf_engine.SetItemAttrText(itemtype => itemtype,
itemkey => itemkey,
aname => 'SELECTED_FUNCTION_NAME',
avalue => l_sel_function_name );
-- Set the selected_person_id to the attribute selected_person_id
wf_engine.SetItemAttrNumber(itemtype => itemtype,
itemkey => itemkey,
aname => 'SELECTED_PERSON_ID',
avalue => l_selected_person );
wf_engine.SetItemAttrText(itemtype => itemtype,
itemkey => itemkey,
aname => 'SELECT_PERSON_USERNAME',
avalue => l_sel_person_username );
wf_engine.SetItemAttrText(itemtype => itemtype,
itemkey => itemkey,
aname => 'SELECT_REQUEST_ID',
avalue => l_sel_request_id );
wf_engine.SetItemAttrText(itemtype => itemtype,
itemkey => itemkey,
aname => 'SELECT_LEAVE_REASON',
avalue => l_sel_leave_reason );
*/
wf_engine.SetItemAttrText(itemtype => itemtype,
itemkey => itemkey,
aname => 'SELECT_ABSENCE_NAME',
avalue => l_sel_absence_name );
wf_engine.SetItemAttrText(itemtype => itemtype,
itemkey => itemkey,
aname => 'SELECT_EMPLOYEE_NUMBER',
avalue => l_sel_employee_num );
exception
when others then
wf_core.context('XXSWCC_WF_SS','set_selected_person',SQLCODE || SQLERRM);
raise;
end set_selected_person;
end;
/
