Oracle Apps R12 SSHR Custom Notification Requirement to add Empno and Leave Name in the Notification Subject sent to the approver:

Requirement to add Empno and Leave Name in the Notification Subject sent to the approver:

1. Create package in database XXZAMEL_WF_SS.

Code of package is at the end of the post.

2. 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

3. 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

4. Open the workflow builder, and then open the process name mentioned in step3.

5. Create 2 attributes under Attributes.

1. SELECT_ABSENCE_NAME
2. SELECT_EMPLOYEE_NUMBER

6. 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

7. Copy/drag the new 2 attributes created in step 5, paste/drag under the new message created in step6.

8. 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.

9. 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

10. 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.

11. Drag the custom function we created between new notification and OR function as below shown.

12. Save and test the solution.

dfd

Package 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;
/