If you are using the Responsibility Ownership function, you should write a PL/SQL code to subscribe to the following business event:
oracle.apps.per.selfservice.respowner.revoke_access
Attention: The code should call the following Application Programmatic Interface (API) which end-dates the responsibility assignment:
fnd_user_resp_groups_api.update_assignment
Sample code is provided below. You can use this as a basis for your own code.
Creating a Sequence (CUST_RESP_OWNER_ARCHIVE_SEQ)
CREATE SEQUENCE CUST_RESP_OWNER_ARCHIVE_SEQ
MINVALUE 1
MAXVALUE 999999999999999999999999999
START WITH 1
INCREMENT BY 1
CACHE 20;
Creating a Table (CUST_RESP_OWNER_ARCHIVE)
create table CUST_RESP_OWNER_ARCHIVE (REVOKE_ID varchar2(25), USER_ID NUMBER(10), RESPONSIBILITY_ID NUMBER(10), REVOKE_JUSTIFICATION varchar2(250), REVOKE_DATE Date, REVOKED_BY NUMBER(10), STATUS varchar2(250));
Creating a Package Header
CREATE OR REPLACE PACKAGE cust_resp_owner_bevent AUTHID CURRENT_USER AS FUNCTION revoke_access_wfevent_subscrb ( p_subscription_guid in raw, p_event in out NOCOPY wf_event_t) RETURN VARCHAR2; END cust_resp_owner_bevent; /
Creating Package Body
CREATE OR REPLACE PACKAGE BODY cust_resp_owner_bevent AS
PROCEDURE archive_data
(p_user_id IN NUMBER,
p_user_name IN VARCHAR2,
p_resp_id IN NUMBER,
p_resp_appl_id IN NUMBER,
p_security_group_id IN NUMBER,
p_justification IN VARCHAR2,
p_status IN VARCHAR2) IS
l_login_person_id number;
BEGIN
l_login_person_id := fnd_global.user_id;
INSERT INTO CUST_RESP_OWNER_ARCHIVE
(REVOKE_ID,
USER_ID,
RESPONSIBILITY_ID,
REVOKE_JUSTIFICATION,
REVOKE_DATE,
REVOKED_BY,
STATUS)
VALUES
(CUST_RESP_OWNER_ARCHIVE_SEQ.nextval,
p_user_id,
p_resp_id,
p_justification,
sysdate,
l_login_person_id,
p_status);
EXCEPTION
WHEN OTHERS THEN
NULL;
END archive_data;
PROCEDURE remove_resp
(p_user_id IN NUMBER,
p_resp_id IN NUMBER,
p_resp_appl_id IN NUMBER,
p_security_group_id IN NUMBER,
p_error OUT VARCHAR2,
p_justification IN VARCHAR2) IS
cursor csr_user_name(v_user_id in number) is
select user_name
from fnd_user
where user_id = v_user_id;
cursor csr_user_resp_groups is
select description
from fnd_user_resp_groups
where user_id = p_user_id
and responsibility_id = p_resp_id;
l_user_name fnd_user.user_name%type;
l_owner_name fnd_user.user_name%type;
l_description varchar(2000);
l_login_person_id number;
BEGIN
l_login_person_id := fnd_global.user_id;
OPEN csr_user_name(p_user_id);
FETCH csr_user_name into l_user_name;
CLOSE csr_user_name;
OPEN csr_user_name(l_login_person_id);
FETCH csr_user_name into l_owner_name;
CLOSE csr_user_name;
OPEN csr_user_resp_groups;
FETCH csr_user_resp_groups into l_description;
CLOSE csr_user_resp_groups;
BEGIN
Fnd_User_Resp_Groups_Api.update_assignment(
USER_ID => p_user_id,
RESPONSIBILITY_ID => p_resp_id,
RESPONSIBILITY_APPLICATION_ID => p_resp_appl_id,
SECURITY_GROUP_ID => p_security_group_id,
START_DATE => SYSDATE - 2,
END_DATE => SYSDATE - 1,
DESCRIPTION =>
substr('RO:'||l_owner_name||':'||l_description,1,240)
);
p_error := 'Status SUCCESSFUL!';
EXCEPTION
WHEN OTHERS THEN
p_error := SQLERRM;
END;
--Helpdesk.remove_resp
-- (p_username => l_user_name,
-- resp_code => to_char(p_resp_id),
-- p_error => p_error,
-- p_description =>
--substr('RO:'||l_owner_name||':'||l_description,1,240));
archive_data(
P_USER_ID => p_user_id,
P_USER_NAME => l_user_name,
P_RESP_ID => p_resp_id,
P_RESP_APPL_ID => p_resp_appl_id,
P_SECURITY_GROUP_ID => p_security_group_id,
p_justification => p_justification,
p_status => p_error
);
EXCEPTION
WHEN OTHERS THEN
NULL;
END remove_resp;
FUNCTION revoke_access_wfevent_subscrb
( p_subscription_guid in raw,
p_event in out NOCOPY wf_event_t)
RETURN VARCHAR2
IS
I number := 0;
usrIdCnt number := 0;
l_error varchar2(2000) := null;
BEGIN
usrIdCnt := to_number(p_event.GetValueForParameter('USER_COUNT'));
FOR I IN 1 .. usrIdCnt LOOP
remove_resp
(p_user_id =>
to_number(p_event.GetValueForParameter('USER_ID'||to_char(I)))
,p_resp_id => to_number(p_event.GetValueForParameter('RESP_ID'))
,p_resp_appl_id =>
to_number(p_event.GetValueForParameter('RESP_APPL_ID'))
,p_security_group_id => to_number(p_event.GetValueForParameter
('SECURITY_GROUP_ID'))
,p_error => l_error
,p_justification => p_event.GetValueForParameter('MESSAGE'));
END LOOP;
COMMIT;
RETURN 'SUCCESS';
EXCEPTION
WHEN OTHERS THEN
WF_CORE.CONTEXT('PER_RESPOWNER_UTIL_SS',
'revoke_access_wfevent_subscrb', p_event.getEventName(),
p_subscription_guid);
WF_EVENT.setErrorInfo(p_event, l_error);
RETURN 'ERROR';
END revoke_access_wfevent_subscrb;
END cust_resp_owner_bevent;
/
For more information on using PL/SQL, see: Overview of Using PL/SQL in Applications
Use the Workflow Administrator Web Applications responsibility and Events page to enable the business event: oracle.apps.per.selfservice.respowner.revoke_access. The revoke access feature of the Responsibility Ownership functionality works only if you subscribe to the business event
Follow these steps to subscribe to the business event:
Enabling the Business Event
Log on to the Workflow Administrator Web Applications responsibility.
Click Business Events. The Events page appears.
In the Events page, search for the business event: oracle.apps.per.selfservice.respowner.revoke_access.
Click Update.
In the Update Event page, select the Enabled status and click Apply.
Creating Subscription for the Business Event
In the Events page, search for the business event: oracle.apps.per.selfservice.respowner.revoke_access.
Click Subscription. The Subscriptions: Event: oracle.apps.per.selfservice.respowner.revoke_access page appears.
Click Create Subscription. The Create Event Subscription page appears.
Select the instance name.
Select the event filter: oracle.apps.per.selfservice.respowner.revoke_access
Enter 10 in the Phase field.
Select Enabled as the Status.
Select Message as the Rule Data.
Select custom as the Action Type.
Click Next.
In the PL/SQL Rule Function, enter the PL/SQL rule function in the following format: <package_name>.<function_name>
for example, cust_resp_owner_bevent.revoke_access_wfevent_subscrb
Enter the Owner Name: SSHR.
Enter the Owner Tag: PER.
Click Apply.