Sunshine Coast Development

Oracle SSHR Absence Delete Approval

Gavin Harris's photo
Gavin Harris
·

4 min read

Issue

Our customer wants to allow the HR Team to Approve, or be informed of deletions to approved employee absences. Out of the box, Oracle EBS will not trigger a Workflow for these events.

Solution

Solution to this issue and a note on how to fire a Workflow from a Business Event and get the data to be available in Workflow Attributes.

  1. Trap the Absence Delete and fire an Event This is done in the HR_PERSON_absence_BK3 package. I created a new PLSQL Procedure:
procedure raise_delete_event(P_ABSENCE_ATTENDANCE_ID number)
IS
  /*-----Custom Parameters-------*/
  l_parameter_list wf_parameter_list_t;

  l_absence_type varchar(30);
  l_absence_reason varchar(30);
  l_date_start date;
  l_date_end date;
  l_absence_days number;
  l_full_name varchar(255);
  l_employee_number varchar(30);
  l_xmldocument varchar(4000);
  l_eventdata clob;
  /*--------------*/
begin
/*
Custom Code to raise Absence Delete Event
*/
--Build Parameter List

  hr_utility.set_location('Entering: HR_PERSON_ABSENCE_BK3.DELETE_PERSON_ABSENCE_B.CUSTOM_CODE', 15);
  SELECT paat.NAME ABSENCE_TYPE
    , paar.NAME ABSENCE_REASON
    , paa.DATE_START
    , paa.DATE_END
    , paa.ABSENCE_DAYS
    , papf.full_name
    , papf.employee_number
  INTO
     l_absence_type
   , l_absence_reason
   , l_date_start
   , l_date_end
   , l_absence_days
   , l_full_name
   , l_employee_number
FROM per_absence_attendances paa
  , PER_ABSENCE_ATTENDANCE_TYPES paat
  , PER_ABS_ATTENDANCE_REASONS paar
  , PER_ALL_PEOPLE_F papf
WHERE paa.absence_attendance_type_id = paat.absence_attendance_type_id
AND paa.abs_attendance_reason_id = paar.abs_attendance_reason_id (+) --There is not always an associated reason.
AND paa.person_id = papf.person_id
AND trunc(SYSDATE) BETWEEN papf.effective_start_date
AND papf.effective_end_date
AND paa.ABSENCE_ATTENDANCE_ID = P_ABSENCE_ATTENDANCE_ID;

if WF_EVENT.TEST('IBO.ABSENCE.DELETE') <> 'NONE' then

/*-Build the WF_DATA XML Document-*/

  l_xmldocument :='<?xml version ="1.0" encoding ="ASCII"?>';
  l_xmldocument :=l_xmldocument || '<HR_LOA_DELETE>';
  l_xmldocument :=l_xmldocument || '<ABSENCE_TYPE>'||l_absence_type||'</ABSENCE_TYPE>';
  l_xmldocument :=l_xmldocument || '<ABSENCE_REASON>'||l_absence_reason||'</ABSENCE_REASON>';
  l_xmldocument :=l_xmldocument || '<START_DATE>'||l_date_start||'</START_DATE>';
  l_xmldocument :=l_xmldocument || '<END_DATE>'||l_date_end||'</END_DATE>';
  l_xmldocument :=l_xmldocument || '<ABSENCE_DAYS>'||l_absence_days||'</ABSENCE_DAYS>';
  l_xmldocument :=l_xmldocument || '<FULL_NAME>'||l_full_name||'</FULL_NAME>';
  l_xmldocument :=l_xmldocument || '<EMPLOYEE_NUMBER>'||l_employee_number||'</EMPLOYEE_NUMBER>';
  l_xmldocument :=l_xmldocument || '</HR_LOA_DELETE>';

/*-Done Building XML Document-*/
/*-Create Event Data into the CLOB-*/
  dbms_lob.createtemporary(l_eventdata, FALSE, DBMS_LOB.CALL);
  dbms_lob.write(l_eventdata, length(l_xmldocument), 1 , l_xmldocument);

/*-RAISE The Event-*/
  WF_EVENT.RAISE( p_event_name => 'XX.ABSENCE.DELETE',
                                     p_event_key => P_ABSENCE_ATTENDANCE_ID,
                                   p_event_data => l_eventdata,
                                  p_parameters => l_parameter_list);

  l_absence_type := l_absence_type ||', Event Fired!';
end if;

hr_utility.set_location('Leaving: HR_PERSON_ABSENCE_BK3.DELETE_PERSON_ABSENCE_B.CUSTOM_CODE', 15);

EXCEPTION
WHEN OTHERS THEN
hr_utility.set_location('Leaving: HR_PERSON_ABSENCE_BK3.DELETE_PERSON_ABSENCE_B.CUSTOM_CODE Errored', 99);

end raise_delete_event;

I then added into the procedure DELETE_PERSON_ABSENCE_A, a call to this Proc (note, it does fire from Forms as well as Framework, but for some reason this proc fails when called from Forms, if any one know why? Probably something to do with a Funky doing a Techy job!). Any way... Notice that I have used XML to pass in the Data that I want available in Workflow Attributes.

  1. Create a Custom Workflow (XX_LOA_DELETE) Create 3 Attributes: 1.XX_LOA_DEL_EVENT, Type: Text 2.XX_LOA_DEL_EVENT_KEY, Type: Text 3.XX_LOA_DEL_EVENT_PAYLOAD, Type: Event

The Start item for this workflow will be an Event with the following setup:

From Event Tab. Event Action = Receive Event Filter = XX.ABSENCE.DELETE

From Event Details Tab. Event Name = XX_LOA_DEL_EVENT Event Key = XX_LOA_DEL_EVENT_KEY Event Message = XX_LOA_DEL_EVENT_PAYLOAD

Create more Attributes that will accept the data that is passed into Workflow from the XML. In my case I will need 7 Attributes. E.G. XX_ABSENCE_TYPE_PAYLOAD, Type: Text

  1. Create a PLSQL Procedure to populate Attributes I created the following (from examples!). The procedure reads in the XX_LOA_DEL_EVENT_PAYLOAD attribute (this will contain the XML) and uses some fancy coding bits to get the data out (again nicked from elsewhere!):
PROCEDURE pop_loa_delete_wf_attributes
(
itemtype IN VARCHAR2
,itemkey IN VARCHAR2
,activityid IN NUMBER
,funcmode IN VARCHAR2
,result OUT VARCHAR2
)
IS
bank_event_document CLOB;
event wf_event_t;
xmldoc xmldom.domdocument;
parser xmlparser.parser;
v_data VARCHAR2(500);
BEGIN
IF (funcmode = 'RUN')
THEN
--
event := wf_engine.getitemattrevent(itemtype => itemtype
,itemkey => itemkey
,NAME => 'XX_LOA_DEL_EVENT_PAYLOAD');
bank_event_document := event.geteventdata();
/* LOA Delete XML Structure...
<HR_LOA_DELETE>
<ABSENCE_TYPE></ABSENCE_TYPE>
<ABSENCE_REASON></ABSENCE_REASON>
<START_DATE></START_DATE>
<END_DATE></END_DATE>
<ABSENCE_DAYS></ABSENCE_DAYS>
<FULL_NAME></FULL_NAME>
<EMPLOYEE_NUMBER></EMPLOYEE_NUMBER>
</HR_LOA_DELETE>*/
v_data := irc_xml_util.valueof(bank_event_document,'/HR_LOA_DELETE/ABSENCE_TYPE');
wf_engine.setitemattrtext(itemtype => itemtype
,itemkey => itemkey
,aname => 'XX_ABSENCE_TYPE_PAYLOAD'
,avalue => v_data);

v_data := irc_xml_util.valueof(bank_event_document,'/HR_LOA_DELETE/ABSENCE_REASON');
wf_engine.setitemattrtext(itemtype => itemtype
,itemkey => itemkey
,aname => 'XX_ABSENCE_REASON_PAYLOAD'
,avalue => v_data);

v_data := irc_xml_util.valueof(bank_event_document,'/HR_LOA_DELETE/START_DATE');
wf_engine.setitemattrtext(itemtype => itemtype
,itemkey => itemkey
,aname => 'XX_START_DATE_PAYLOAD'
,avalue => v_data);

v_data := irc_xml_util.valueof(bank_event_document,'/HR_LOA_DELETE/END_DATE');
wf_engine.setitemattrtext(itemtype => itemtype
,itemkey => itemkey
,aname => 'XX_END_DATE_PAYLOAD'
,avalue => v_data);

v_data := irc_xml_util.valueof(bank_event_document,'/HR_LOA_DELETE/EMPLOYEE_NUMBER');
wf_engine.setitemattrtext(itemtype => itemtype
,itemkey => itemkey
,aname => 'XX_EMPLOYEE_NUMBER_PAYLOAD'
,avalue => v_data);

v_data := irc_xml_util.valueof(bank_event_document,'/HR_LOA_DELETE/FULL_NAME');
wf_engine.setitemattrtext(itemtype => itemtype
,itemkey => itemkey
,aname => 'XX_FULL_NAME_PAYLOAD'
,avalue => v_data);
END IF;
END pop_loa_delete_wf_attributes;
  1. Create a New Function for XX_LOA_DELETE This function will call the above PLSQL procedure. Set the following Parameters:

Function Name: PKG.pop_loa_delete_wf_attributes Function Type: PL/SQL

Once this has been run, the Attributes created to hold the data (in point 2 above) will be populated. Now you can use this data to send off Notifications etc.

  1. Create the Business Event and Subscription Navigate to Workflow Administrator, Business Events. Click Create Event. Enter the event Name (XX.ABSENCE.DELETE) Enter a Display Name... Status: Enabled Owner tag and name: SYSADMIN Click Apply.

Query your New Event, and click on the Subscription Button. Click Create Subscription. Enter the System Name Source Type: Local Event Filter: XX.ABSENCE.DELETE Phase: 10 (use this to control which Subscription fires first) Status: Enabled Rule Date: Message Action Type: Launch Workflow On Error: not sure how this works?? Click Next...

Enter Workflow type (HRSSA) Workflow Process: XX_LOA_DELETE (you should have saved the above workflow back the the DB before you do this). Owner Name / Tag: SYSADMIN Click Apply.

Test...

Hope this helps. Gavin

Oracleworkflow