Page Comparison

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;