This code gives idea about API's available and usage to assign new responsibility or revoke existing responsibility. Also one can find how to end date responsibility itself.
/* Formatted on 2012/08/28 10:59 (Formatter Plus v4.8.8) */
CREATE OR REPLACE PACKAGE BODY apps.xxrh_decommission_11i_pkg
AS
/* $Header: /home/appprdas/new/xxrh/fnd/install/XXRH_DECOMMISSION_11I_PKG.sql,v 1.9 2005/01/27 18:38:58 appprdas Exp $ */
-- Modification History
-- Date Author Changes
-- 08/21/2012 ypatil initial draft
-- Declaration of Constants
--
PROCEDURE modify_access (
errbuf OUT VARCHAR2,
retcode OUT NUMBER,
p_tower IN VARCHAR2,
p_app_code IN VARCHAR2,
p_user_type IN VARCHAR2,
p_processing_mode IN VARCHAR2
)
AS
CURSOR cur_revoke_resp
IS
SELECT usr.user_name, fr.responsibility_key,
frt.responsibility_name, fa.application_short_name app,
fnds.security_group_key, frt.description, usr.user_id,
fr.responsibility_id,
fr.application_id responsibility_application_id,
furg.start_date,
DECODE (p_user_type,
'ASSOCIATE', xd.inactive_date_assoc,
'MANAGER', xd.inactive_date_manager,
'SUPER USER', xd.inactive_date_super_user
) user_type_inactive_date,
fa_c.application_short_name inq_app_name,
fr_c.responsibility_key inq_resp_key,
fnds.security_group_key inq_sec_grp,
frt_c.description inq_resp_desc, fr.data_group_id,
fr.data_group_application_id, fr.menu_id,
fr.start_date resp_start_date, fr.group_application_id,
fr.request_group_id, fr.VERSION, fr.web_host_name,
fr.web_agent_name, xd.*
FROM fnd_user usr,
fnd_responsibility_tl frt,
fnd_application fa,
fnd_responsibility fr,
fnd_security_groups fnds,
fnd_user_resp_groups_all furg,
bolinf.xxrh_decommission_11i xd,
bolinf.xxrh_decommission_user_type xua,
fnd_responsibility_tl frt_c,
fnd_application fa_c,
fnd_responsibility fr_c
WHERE usr.user_id = furg.user_id
AND furg.responsibility_id = fr.responsibility_id
AND fr.responsibility_id = frt.responsibility_id
AND fr.application_id = fa.application_id
AND furg.security_group_id = fnds.security_group_id
AND usr.user_name = xua.user_name
AND NVL (furg.end_date, SYSDATE + 1) > SYSDATE
AND xd.resp_name = frt.responsibility_name
AND xua.user_type =
DECODE (p_user_type,
'ALL', xua.user_type,
p_user_type
)
AND xd.tower = p_tower
AND fa.application_short_name = p_app_code
AND fr_c.responsibility_id = frt_c.responsibility_id
AND fr_c.application_id = fa_c.application_id
AND DECODE (xd.corr_inq_resp,
'Not Required', xd.resp_name,
xd.corr_inq_resp
) = frt_c.responsibility_name
ORDER BY frt.responsibility_name ;
l_inactive_date DATE;
l_user_type_inactive_date DATE;
l_resp_name VARCHAR2 (500);
l_resp_name_all VARCHAR2 (500);
l_raise_excp EXCEPTION;
BEGIN
fnd_file.put_line
(fnd_file.LOG,
'~~~~~~~~~~~~~~~~~~~~~PARAMETERS~~~~~~~~~~~~~~~~~~~~~~~~~~~~~'
);
fnd_file.put_line (fnd_file.LOG, ' Tower ' || p_tower);
fnd_file.put_line (fnd_file.LOG, ' Application ' || p_app_code);
fnd_file.put_line (fnd_file.LOG, ' User Type ' || p_user_type);
fnd_file.put_line (fnd_file.LOG,
' Processing Mode ' || p_processing_mode
);
fnd_file.put_line
(fnd_file.LOG,
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~'
);
fnd_file.new_line(fnd_file.LOG,2);
FOR rec_revoke_resp IN cur_revoke_resp
LOOP
IF p_user_type = 'ALL'
THEN
BEGIN
l_resp_name_all := rec_revoke_resp.responsibility_name;
SELECT DISTINCT inactive_date
INTO l_user_type_inactive_date
FROM (SELECT inactive_date_assoc inactive_date
FROM bolinf.xxrh_decommission_11i xda
WHERE xda.resp_name = l_resp_name_all
UNION
SELECT inactive_date_manager inactive_date
FROM bolinf.xxrh_decommission_11i xdm
WHERE xdm.resp_name = l_resp_name_all
UNION
SELECT inactive_date_super_user inactive_date
FROM bolinf.xxrh_decommission_11i xds
WHERE xds.resp_name = l_resp_name_all) a;
EXCEPTION
WHEN TOO_MANY_ROWS
THEN
fnd_file.put_line
(fnd_file.LOG,
' Different inactive dates for User type ALL resp name '
|| rec_revoke_resp.responsibility_name
);
retcode := 2;
errbuf :=
'Different inactive dates for User type ALL for '
|| rec_revoke_resp.responsibility_name;
RAISE l_raise_excp;
WHEN OTHERS
THEN
fnd_file.put_line
(fnd_file.LOG,
' Different inactive dates for User type ALL resp name '
|| rec_revoke_resp.responsibility_name
);
retcode := 2;
errbuf :=
'Different inactive dates for User type ALL for '
|| rec_revoke_resp.responsibility_name;
RAISE l_raise_excp;
END;
ELSE
l_user_type_inactive_date :=
rec_revoke_resp.user_type_inactive_date;
END IF; -- user type check
IF p_processing_mode IN ('DISABLE', 'BOTH')
THEN
l_inactive_date := SYSDATE;
IF l_inactive_date >= rec_revoke_resp.user_type_inactive_date
THEN
fnd_user_resp_groups_api.update_assignment
(user_id => rec_revoke_resp.user_id,
responsibility_id => rec_revoke_resp.responsibility_id,
responsibility_application_id => rec_revoke_resp.responsibility_application_id,
start_date => rec_revoke_resp.start_date,
end_date => l_inactive_date,
description => NULL
);
fnd_file.put_line (fnd_file.LOG,
'Inactivated Resp '
|| rec_revoke_resp.responsibility_name
|| ' for '
|| rec_revoke_resp.user_name
|| ' with end date '
|| rec_revoke_resp.user_type_inactive_date
);
-- disable responsibility only if user access is disabled first
IF NVL (l_resp_name, 'N/A') <>
rec_revoke_resp.responsibility_name
THEN
-- API call to end date responsibility
fnd_responsibility_pkg.update_row
(x_responsibility_id => rec_revoke_resp.responsibility_id,
x_application_id => rec_revoke_resp.responsibility_application_id,
x_web_host_name => rec_revoke_resp.web_host_name,
x_web_agent_name => rec_revoke_resp.web_agent_name,
x_data_group_application_id => rec_revoke_resp.data_group_application_id,
x_data_group_id => rec_revoke_resp.data_group_id,
x_menu_id => rec_revoke_resp.menu_id,
x_start_date => rec_revoke_resp.resp_start_date,
x_end_date => SYSDATE,
x_group_application_id => rec_revoke_resp.group_application_id,
x_request_group_id => rec_revoke_resp.request_group_id,
x_version => rec_revoke_resp.VERSION,
x_responsibility_key => rec_revoke_resp.responsibility_key,
x_responsibility_name => rec_revoke_resp.responsibility_name,
x_description => rec_revoke_resp.description,
x_last_update_date => SYSDATE,
x_last_updated_by => fnd_global.user_id,
x_last_update_login => fnd_global.login_id
);
fnd_file.new_line (fnd_file.LOG,3) ;
fnd_file.put_line (fnd_file.LOG,
' Inactivated Responsiibility - '
|| rec_revoke_resp.responsibility_name
);
fnd_file.new_line (fnd_file.LOG,3) ;
l_resp_name := rec_revoke_resp.responsibility_name;
END IF; -- disable responsibility
ELSE
-- Inactive date is not yet arrived , pre-mature run of process
fnd_file.put_line
(fnd_file.LOG,
' Pre-mature run of process for resp '
|| rec_revoke_resp.responsibility_name
|| ' - Proposed inactive date '
|| rec_revoke_resp.user_type_inactive_date
|| ' Current system date for the timezone is '
|| l_inactive_date
);
END IF; -- date comparison
END IF; -- processing mode 1
IF p_processing_mode IN ('ENABLE', 'BOTH') AND rec_revoke_resp.corr_inq_resp <>'Not Required'
THEN
-- Add responsibility
fnd_user_pkg.addresp
(username => UPPER
(rec_revoke_resp.user_name
),
resp_app => rec_revoke_resp.inq_app_name,
resp_key => rec_revoke_resp.inq_resp_key,
security_group => rec_revoke_resp.inq_sec_grp,
description => rec_revoke_resp.inq_resp_desc,
start_date => SYSDATE,
end_date => NULL
);
fnd_file.put_line (fnd_file.LOG,
' Activated Resp '
|| rec_revoke_resp.inq_resp_desc
|| ' for '
|| rec_revoke_resp.user_name
);
-- end add resp
END IF; -- processing mode 2
END LOOP;
COMMIT;
EXCEPTION
WHEN l_raise_excp
THEN
fnd_file.put_line (fnd_file.LOG, ' l_raise_excp Raised ');
retcode := 2;
WHEN OTHERS
THEN
ROLLBACK;
errbuf := 'Other Error ' || SQLERRM;
fnd_file.put_line (fnd_file.LOG, ' Error Occured ' || errbuf);
END modify_access;
-------------------------------------------
END xxrh_decommission_11i_pkg;
/
/* Formatted on 2012/08/28 10:59 (Formatter Plus v4.8.8) */
CREATE OR REPLACE PACKAGE BODY apps.xxrh_decommission_11i_pkg
AS
/* $Header: /home/appprdas/new/xxrh/fnd/install/XXRH_DECOMMISSION_11I_PKG.sql,v 1.9 2005/01/27 18:38:58 appprdas Exp $ */
-- Modification History
-- Date Author Changes
-- 08/21/2012 ypatil initial draft
-- Declaration of Constants
--
PROCEDURE modify_access (
errbuf OUT VARCHAR2,
retcode OUT NUMBER,
p_tower IN VARCHAR2,
p_app_code IN VARCHAR2,
p_user_type IN VARCHAR2,
p_processing_mode IN VARCHAR2
)
AS
CURSOR cur_revoke_resp
IS
SELECT usr.user_name, fr.responsibility_key,
frt.responsibility_name, fa.application_short_name app,
fnds.security_group_key, frt.description, usr.user_id,
fr.responsibility_id,
fr.application_id responsibility_application_id,
furg.start_date,
DECODE (p_user_type,
'ASSOCIATE', xd.inactive_date_assoc,
'MANAGER', xd.inactive_date_manager,
'SUPER USER', xd.inactive_date_super_user
) user_type_inactive_date,
fa_c.application_short_name inq_app_name,
fr_c.responsibility_key inq_resp_key,
fnds.security_group_key inq_sec_grp,
frt_c.description inq_resp_desc, fr.data_group_id,
fr.data_group_application_id, fr.menu_id,
fr.start_date resp_start_date, fr.group_application_id,
fr.request_group_id, fr.VERSION, fr.web_host_name,
fr.web_agent_name, xd.*
FROM fnd_user usr,
fnd_responsibility_tl frt,
fnd_application fa,
fnd_responsibility fr,
fnd_security_groups fnds,
fnd_user_resp_groups_all furg,
bolinf.xxrh_decommission_11i xd,
bolinf.xxrh_decommission_user_type xua,
fnd_responsibility_tl frt_c,
fnd_application fa_c,
fnd_responsibility fr_c
WHERE usr.user_id = furg.user_id
AND furg.responsibility_id = fr.responsibility_id
AND fr.responsibility_id = frt.responsibility_id
AND fr.application_id = fa.application_id
AND furg.security_group_id = fnds.security_group_id
AND usr.user_name = xua.user_name
AND NVL (furg.end_date, SYSDATE + 1) > SYSDATE
AND xd.resp_name = frt.responsibility_name
AND xua.user_type =
DECODE (p_user_type,
'ALL', xua.user_type,
p_user_type
)
AND xd.tower = p_tower
AND fa.application_short_name = p_app_code
AND fr_c.responsibility_id = frt_c.responsibility_id
AND fr_c.application_id = fa_c.application_id
AND DECODE (xd.corr_inq_resp,
'Not Required', xd.resp_name,
xd.corr_inq_resp
) = frt_c.responsibility_name
ORDER BY frt.responsibility_name ;
l_inactive_date DATE;
l_user_type_inactive_date DATE;
l_resp_name VARCHAR2 (500);
l_resp_name_all VARCHAR2 (500);
l_raise_excp EXCEPTION;
BEGIN
fnd_file.put_line
(fnd_file.LOG,
'~~~~~~~~~~~~~~~~~~~~~PARAMETERS~~~~~~~~~~~~~~~~~~~~~~~~~~~~~'
);
fnd_file.put_line (fnd_file.LOG, ' Tower ' || p_tower);
fnd_file.put_line (fnd_file.LOG, ' Application ' || p_app_code);
fnd_file.put_line (fnd_file.LOG, ' User Type ' || p_user_type);
fnd_file.put_line (fnd_file.LOG,
' Processing Mode ' || p_processing_mode
);
fnd_file.put_line
(fnd_file.LOG,
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~'
);
fnd_file.new_line(fnd_file.LOG,2);
FOR rec_revoke_resp IN cur_revoke_resp
LOOP
IF p_user_type = 'ALL'
THEN
BEGIN
l_resp_name_all := rec_revoke_resp.responsibility_name;
SELECT DISTINCT inactive_date
INTO l_user_type_inactive_date
FROM (SELECT inactive_date_assoc inactive_date
FROM bolinf.xxrh_decommission_11i xda
WHERE xda.resp_name = l_resp_name_all
UNION
SELECT inactive_date_manager inactive_date
FROM bolinf.xxrh_decommission_11i xdm
WHERE xdm.resp_name = l_resp_name_all
UNION
SELECT inactive_date_super_user inactive_date
FROM bolinf.xxrh_decommission_11i xds
WHERE xds.resp_name = l_resp_name_all) a;
EXCEPTION
WHEN TOO_MANY_ROWS
THEN
fnd_file.put_line
(fnd_file.LOG,
' Different inactive dates for User type ALL resp name '
|| rec_revoke_resp.responsibility_name
);
retcode := 2;
errbuf :=
'Different inactive dates for User type ALL for '
|| rec_revoke_resp.responsibility_name;
RAISE l_raise_excp;
WHEN OTHERS
THEN
fnd_file.put_line
(fnd_file.LOG,
' Different inactive dates for User type ALL resp name '
|| rec_revoke_resp.responsibility_name
);
retcode := 2;
errbuf :=
'Different inactive dates for User type ALL for '
|| rec_revoke_resp.responsibility_name;
RAISE l_raise_excp;
END;
ELSE
l_user_type_inactive_date :=
rec_revoke_resp.user_type_inactive_date;
END IF; -- user type check
IF p_processing_mode IN ('DISABLE', 'BOTH')
THEN
l_inactive_date := SYSDATE;
IF l_inactive_date >= rec_revoke_resp.user_type_inactive_date
THEN
fnd_user_resp_groups_api.update_assignment
(user_id => rec_revoke_resp.user_id,
responsibility_id => rec_revoke_resp.responsibility_id,
responsibility_application_id => rec_revoke_resp.responsibility_application_id,
start_date => rec_revoke_resp.start_date,
end_date => l_inactive_date,
description => NULL
);
fnd_file.put_line (fnd_file.LOG,
'Inactivated Resp '
|| rec_revoke_resp.responsibility_name
|| ' for '
|| rec_revoke_resp.user_name
|| ' with end date '
|| rec_revoke_resp.user_type_inactive_date
);
-- disable responsibility only if user access is disabled first
IF NVL (l_resp_name, 'N/A') <>
rec_revoke_resp.responsibility_name
THEN
-- API call to end date responsibility
fnd_responsibility_pkg.update_row
(x_responsibility_id => rec_revoke_resp.responsibility_id,
x_application_id => rec_revoke_resp.responsibility_application_id,
x_web_host_name => rec_revoke_resp.web_host_name,
x_web_agent_name => rec_revoke_resp.web_agent_name,
x_data_group_application_id => rec_revoke_resp.data_group_application_id,
x_data_group_id => rec_revoke_resp.data_group_id,
x_menu_id => rec_revoke_resp.menu_id,
x_start_date => rec_revoke_resp.resp_start_date,
x_end_date => SYSDATE,
x_group_application_id => rec_revoke_resp.group_application_id,
x_request_group_id => rec_revoke_resp.request_group_id,
x_version => rec_revoke_resp.VERSION,
x_responsibility_key => rec_revoke_resp.responsibility_key,
x_responsibility_name => rec_revoke_resp.responsibility_name,
x_description => rec_revoke_resp.description,
x_last_update_date => SYSDATE,
x_last_updated_by => fnd_global.user_id,
x_last_update_login => fnd_global.login_id
);
fnd_file.new_line (fnd_file.LOG,3) ;
fnd_file.put_line (fnd_file.LOG,
' Inactivated Responsiibility - '
|| rec_revoke_resp.responsibility_name
);
fnd_file.new_line (fnd_file.LOG,3) ;
l_resp_name := rec_revoke_resp.responsibility_name;
END IF; -- disable responsibility
ELSE
-- Inactive date is not yet arrived , pre-mature run of process
fnd_file.put_line
(fnd_file.LOG,
' Pre-mature run of process for resp '
|| rec_revoke_resp.responsibility_name
|| ' - Proposed inactive date '
|| rec_revoke_resp.user_type_inactive_date
|| ' Current system date for the timezone is '
|| l_inactive_date
);
END IF; -- date comparison
END IF; -- processing mode 1
IF p_processing_mode IN ('ENABLE', 'BOTH') AND rec_revoke_resp.corr_inq_resp <>'Not Required'
THEN
-- Add responsibility
fnd_user_pkg.addresp
(username => UPPER
(rec_revoke_resp.user_name
),
resp_app => rec_revoke_resp.inq_app_name,
resp_key => rec_revoke_resp.inq_resp_key,
security_group => rec_revoke_resp.inq_sec_grp,
description => rec_revoke_resp.inq_resp_desc,
start_date => SYSDATE,
end_date => NULL
);
fnd_file.put_line (fnd_file.LOG,
' Activated Resp '
|| rec_revoke_resp.inq_resp_desc
|| ' for '
|| rec_revoke_resp.user_name
);
-- end add resp
END IF; -- processing mode 2
END LOOP;
COMMIT;
EXCEPTION
WHEN l_raise_excp
THEN
fnd_file.put_line (fnd_file.LOG, ' l_raise_excp Raised ');
retcode := 2;
WHEN OTHERS
THEN
ROLLBACK;
errbuf := 'Other Error ' || SQLERRM;
fnd_file.put_line (fnd_file.LOG, ' Error Occured ' || errbuf);
END modify_access;
-------------------------------------------
END xxrh_decommission_11i_pkg;
/
No comments:
Post a Comment