Versions Compared

Old Version 13

changes.mady.by.user Richard Hendricks

Saved on

compared with

New Version Current

changes.mady.by.user Richard Hendricks

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

Table of Contents

Overview

The process of adding and removing role members from departments in the Rice UI can be a time consuming process. To support the Time And Reporting System (TRS) Rice client application, there are often times when many role members must be added or removed from departments. Since it is not practical to make such mass updates via the Rice UI, these updates are currently managed though the Kuali Rice Data Set Migration Process. While this process does accomplish the task, it results in the proliferation of JIRA tickets and an increase in the workload for the DBA group who must execute the scripts. The scripts which are currently used to manage the department additions and removals consist of two procedures, one to add a role member to a department (add_dta_role_member), and one to remove a role member from a department (remove_dta_role_member). The script creates the procedures, adds or removes the role members, and then drops the procedures. This process is repeated with each Jira ticket which is created for the purpose of adding or removing role members. The procedures used each time are identical from one Jira ticket to the next, with the only different being the role members which are to be added or removed.

...

The kfs_kim_role_maint was created to manage the addition and removal of user roles from a department. The package definition along with the procedure definitions are shown below:

Code Block

CREATE OR REPLACE PACKAGE kfs_kim_role_maint AS  -- spec
PROCEDURE add_dta_role_member( PrincipalName IN VARCHAR2, HomeDepartmentCode IN VARCHAR2 );
PROCEDURE remove_dta_role_member( PrincipalName IN VARCHAR2, HomeDepartmentCode IN VARCHAR2, EndDate IN DATE DEFAULT SYSDATE );
END kfs_kim_role_maint;

The entire package definition is shown below:

Code Block

create or replace 
PACKAGE BODY kfs_kim_role_maint AS

  PROCEDURE add_dta_role_member( PrincipalName IN VARCHAR2, HomeDepartmentCode IN VARCHAR2 ) IS
      PrincipalId VARCHAR2(40);
      RoleMemberId VARCHAR2(40) := 'KFS_TRS_2_'||PrincipalName||'_'||HomeDepartmentCode;
      Temp NUMBER;
  BEGIN
      SELECT prncpl_id INTO PrincipalId
          FROM krim_prncpl_t
          WHERE prncpl_nm = PrincipalName;

     -- look for an active assignment for the same department
      SELECT COUNT(*)
          INTO Temp
          FROM krim_role_mbr_t rm
              JOIN krim_role_mbr_attr_data_t rma ON rma.role_mbr_id = rm.role_mbr_id
          WHERE rm.MBR_TYP_CD = 'P'
            AND rm.MBR_ID = PrincipalId
            AND rm.ROLE_ID = 'KFS_TRS_2'
           AND rma.KIM_ATTR_DEFN_ID = 'KFS_TRS_HD'
            AND rma.ATTR_VAL = HomeDepartmentCode
            AND (ACTV_FRM_DT IS NULL OR ACTV_FRM_DT <= TRUNC(SYSDATE))
            AND (ACTV_TO_DT IS NULL OR ACTV_TO_DT > TRUNC(SYSDATE));

      IF Temp = 0 THEN
          INSERT INTO krim_role_mbr_t ( role_id, role_mbr_id, mbr_id, mbr_typ_cd, obj_id, ver_nbr, last_updt_dt, actv_frm_dt )
              VALUES ( 'KFS_TRS_2', RoleMemberId, PrincipalId, 'P', SYS_GUID(), 1, SYSDATE, TRUNC(SYSDATE) );
          INSERT INTO krim_role_mbr_attr_data_t ( attr_data_id, role_mbr_id, kim_typ_id, kim_attr_defn_id, attr_val, obj_id, ver_nbr )
              VALUES ( RoleMemberId||'_DEPT', RoleMemberId, 'KFS_TRS_1', 'KFS_TRS_HD', HomeDepartmentCode, SYS_GUID(), 1 );
          INSERT INTO krim_role_mbr_attr_data_t ( attr_data_id, role_mbr_id, kim_typ_id, kim_attr_defn_id, attr_val, obj_id, ver_nbr )
              VALUES ( RoleMemberId||'_CAMPUS', RoleMemberId, 'KFS_TRS_1', '12', 'DV', SYS_GUID(), 1 );

      ELSE
          DBMS_OUTPUT.PUT_LINE(PrincipalName||' - '||HomeDepartmentCode||' FOUND IN KRIM FOR TRS, OPERATION SKIPPED FOR THIS RECORD. '||Temp);
      END IF;
    EXCEPTION
    	WHEN NO_DATA_FOUND THEN
    		DBMS_OUTPUT.PUT_LINE(PrincipalName||' NOT FOUND IN KRIM_PRINCPL_T. ');
    	WHEN OTHERS THEN
 
  		DBMS_OUTPUT.PUT_LINE('EXCEPTION '||SQLERRM(SQLCODE)||' OCCURRED ADDING '||PrincipalName||' - '||HomeDepartmentCode);

    COMMIT;
  END add_dta_role_member;


  PROCEDURE remove_dta_role_member( PrincipalName IN VARCHAR2, HomeDepartmentCode IN VARCHAR2, EndDate IN DATE DEFAULT SYSDATE ) IS
      PrincipalId VARCHAR2(40);
      RoleMemberId VARCHAR2(40);
  BEGIN
      SELECT prncpl_id INTO PrincipalId
          FROM krim_prncpl_t
          WHERE prncpl_nm = PrincipalName;
     SELECT rm.role_mbr_id
          INTO RoleMemberId
          FROM krim_role_mbr_t rm
              JOIN krim_role_mbr_attr_data_t rma ON rma.role_mbr_id = rm.role_mbr_id
          WHERE rm.MBR_TYP_CD = 'P'
            AND rm.MBR_ID = PrincipalId
            AND rm.ROLE_ID = 'KFS_TRS_2'
            AND rma.KIM_ATTR_DEFN_ID = 'KFS_TRS_HD'
            AND rma.ATTR_VAL = HomeDepartmentCode
            AND (ACTV_FRM_DT IS NULL OR ACTV_FRM_DT <= TRUNC(SYSDATE))
            AND (ACTV_TO_DT IS NULL OR ACTV_TO_DT > TRUNC(EndDate));
      UPDATE krim_role_mbr_t
          SET actv_to_dt = TRUNC( EndDate ), last_updt_dt = SYSDATE
          WHERE role_mbr_id = RoleMemberId;

      IF SQL%ROWCOUNT = 0 THEN
    	DBMS_OUTPUT.PUT_LINE(PrincipalName||' - '||HomeDepartmentCode||' NO REMOVAL OCCURRED.');
      END IF;
      EXCEPTION
    	WHEN NO_DATA_FOUND THEN
    		IF PrincipalId IS NULL THEN
    		DBMS_OUTPUT.PUT_LINE(PrincipalName||' NOT FOUND IN KRIM_PRINCPL_T.');
    		ELSE
    		DBMS_OUTPUT.PUT_LINE(PrincipalName||' NOT FOUND IN TRS.');
   
		END IF;
    	WHEN OTHERS THEN
    		DBMS_OUTPUT.PUT_LINE('EXCEPTION '||SQLERRM(SQLCODE)||' OCCURRED REMOVING '||PrincipalName||' - '||HomeDepartmentCode);

     COMMIT;
  END remove_dta_role_member;
END kfs_kim_role_maint;

Example Utilization

An example script making use of the kfs_kim_role_maint package is shown below:

Code Block

---------------Opening Section-------------------
COL DT NOPRINT NEW_VALUE db_date;
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD-HH24MISS') DT FROM DUAL;
COL db NOPRINT NEW_VALUE db_name;
SELECT SYS_CONTEXT ('RICE', 'MOTHRA') DB FROM DUAL;
----- Name the spool file -----
SPOOL ./KFS_ROLE_MAINT--&db_date-&db_name.log;
SET ECHO ON;
SET TERMOUT ON;
SET VERIFY OFF;
SET TRIMSPOOL ON;
SET SERVEROUTPUT ON SIZE 1000000;
WHENEVER SQLERROR EXIT SQL.SQLCODE ROLLBACK;
WHENEVER OSERROR EXIT SQL.SQLCODE ROLLBACK;

SET ECHO ON;

SHOW USER;
BEGIN
    kfs_kim_role_maint.remove_dta_role_member( 'rvmartin', '061300' );
    kfs_kim_role_maint.remove_dta_role_member( 'soconner', '061095' );
    kfs_kim_role_maint.add_dta_role_member( 'licruz', '062007' );
    kfs_kim_role_maint.add_dta_role_member( 'licruz', '062015' );

END;
/
SPOOL OFF;