E-Business Suite, EBS, HCM, Hints and Tip, HRMS, Oracle, PLSQL, SQL

Auto End Date Separated Employees

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

  1. Identify all separated employees.  In this environment they are considered  “Ex-Employee”
  2. 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

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

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