A little while back, there was some concern that employees were being terminated in #Oracle E-Business Suite, but there was no process to ensure that those former employees couldn’t log back into EBS. This particular environment did not leverage any SSO technologies to manage user access, so each System Administrator was responsible for managing that process. This can become quite cumbersome as both Responsibilities and user accounts must be End Dated in EBS. So the challenge was to automate this process to relieve the staff of the responsibility.
I launched my favorite development tool, SQL Developer, and wrote a PL/SQL procedure building 2 cursors to perform the following:
Cursor I
- Identify all separated employees. In this environment they are considered “Ex-Employee”
- Collect all Responsibilities assigned to these former employees. We needed to capture the Responsibility Key and the Short Name to pass into the API.
Cursor II
- Just gives me a second copy of the separated users for End Dating the FND User record
Once we collected this information, we were ready to loop through these cursors. The first one End Dates each Responsibility and the second loop End Dates the FND User record. Interestingly enough, I tried multiple ways of combining these steps, even swapping the order. Ultimately, processing the records in the sequence below worked.
The final step was to create and schedule this as a concurrent program in EBS and produce a log file for the process owner(s) to validate against. Lexapro has really helped me personally. It was too difficult on the first two days of the course. I felt drowsiness, blurred vision, but it gradually returned to normal. After 2 weeks I forgot that I was sick, the mood was improved. I felt that I wanted to live! I started to cancel the drug gradually, there was no withdrawal syndrome. I was taking Lexapro for six months and it helped to return my mind and body to a normal state). Lexapro online is taken strictly on prescription, but I want to say that the drug is very soft and really improves the quality of life.
Take a look, and if you can borrow from it, be my guest.
(edit) Here is the Package Spec: (02-Apr-2014)
create or replace PACKAGE ENDDATE_TERM_USERS AUTHID CURRENT_USER
— version 1.0
/*******************************************************************************
Name: CATS_ENDDATE_TERM_USERS
Description – End Date ALL Responsibilities and the FND Users account of
terminated users and deceased survivors. All users with employee type ofex-employee will be end dated.
This process will run daily
.————————————————————————-
Written by Date Description
—————- ———– ——– ———————————–
Danny Bryant 12-MAR-2012 New
*******************************************************************************/
IS
PROCEDURE MAIN(
X_ERRBUF OUT VARCHAR2
,X_RETCODE OUT VARCHAR2 );
ENDDATE_TERM_USERS;
——————————————————–
Package Body
create or replace PACKAGE BODY ENDDATE_TERM_USERS
AS
— Version 1.0
/*******************************************************************************
Description – End Date ALL Responsibilities and the FND Users account of
terminated users and deceased survivors. All users with employee type of
ex-employee will be end dated.
*******************************************************************************/
PROCEDURE MAIN(
X_ERRBUF OUT VARCHAR2
,X_RETCODE OUT VARCHAR2
)
IS
–DECLARE
L_EX_EMP_TYPE NUMBER DEFAULT 1123; — Ex-employee
L_FND_COUNT NUMBER := 0;
L_RESP_COUNT NUMBER := 0;
— cursor for responsibilities to end-date
CURSOR RESP_RECORDS
IS
SELECT
DISTINCT PAPF.EMPLOYEE_NUMBER AS EMP_NO
,FR.RESPONSIBILITY_KEY AS RESP_KEY
,FA.APPLICATION_SHORT_NAME as APP_SHORT_NM
FROM PER_ALL_PEOPLE_F PAPF
,FND_APPLICATION FA
,FND_RESPONSIBILITY FR
,FND_USER_RESP_GROUPS_ALL FURGA
,FND_USER FU
WHERE PAPF.PERSON_ID = FU.EMPLOYEE_ID
AND PAPF.PERSON_TYPE_ID = ‘1123’–L_EX_EMP_TYPE
AND FU.END_DATE IS NULL
AND TRUNC(SYSDATE) BETWEEN TRUNC(PAPF.EFFECTIVE_START_DATE) AND TRUNC(PAPF.EFFECTIVE_END_DATE) — Handle the date tracking feature in EBS
AND FU.USER_ID = FURGA.USER_ID
AND FR.RESPONSIBILITY_ID = FURGA.RESPONSIBILITY_ID
AND FA.APPLICATION_ID = FR.APPLICATION_ID
AND FU.USER_NAME = PAPF.EMPLOYEE_NUMBER
AND FURGA.END_DATE IS NULL
ORDER BY 1;
—
–cursor for fnd_user records to end-date.
CURSOR FND_RECORDS
IS
SELECT
DISTINCT(PAPF.EMPLOYEE_NUMBER) as EMP_NO,
PAPF.FULL_NAME FULL_NAME
FROM PER_ALL_PEOPLE_F PAPF
,FND_USER FU
WHERE PAPF.PERSON_ID = FU.EMPLOYEE_ID
AND FU.USER_NAME = PAPF.EMPLOYEE_NUMBER
AND PAPF.PERSON_TYPE_ID = ‘1123’–L_EX_EMP_TYPE
AND FU.END_DATE IS NULL
AND TRUNC(SYSDATE) BETWEEN TRUNC(PAPF.EFFECTIVE_START_DATE) AND TRUNC(PAPF.EFFECTIVE_END_DATE)
ORDER BY 1;
—
BEGIN
—
FOR R_REC IN RESP_RECORDS
LOOP
BEGIN
fnd_user_pkg.delresp(
username => R_REC.EMP_NO
,resp_app => R_REC.APP_SHORT_NM
,RESP_KEY => R_REC.RESP_KEY
,security_group => ‘STANDARD’);
END;
L_RESP_COUNT := L_RESP_COUNT + 1;
FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ‘Emp Number: ‘ || R_REC.EMP_NO || ‘ End dating Responsibility: ‘ || R_REC.RESP_KEY);
END LOOP;
FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ‘End Dated: ‘ || L_RESP_COUNT || ‘ Responsibilities.’);
FOR F_REC IN FND_RECORDS
LOOP
BEGIN
FND_USER_PKG.UPDATEUSER (
X_USER_NAME => F_REC.EMP_NO
,X_OWNER => ‘SEED’
,X_END_DATE => SYSDATE);
END;
L_FND_COUNT := L_FND_COUNT + 1;
FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ‘Employee Name: ‘ || F_REC.FULL_NAME || ‘ Employee Number: ‘ || F_REC.EMP_NO || ‘ FND End Dated on: ‘ || SYSDATE);
END LOOP;
FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ‘End Dated: ‘ || L_FND_COUNT || ‘ FND User Accounts.’);
COMMIT;
END MAIN;
END ENDDATE_TERM_USERS;
Enjoy
