--------------------------------------------------------
--  File created - Thursday-August-01-2013   
--------------------------------------------------------
--------------------------------------------------------
--  DDL for Function IN_LIST_NUMBER
--------------------------------------------------------

  CREATE OR REPLACE FUNCTION "IN_LIST_NUMBER" ( sInList IN VARCHAR2 ) RETURN ACCMGR_NUMBER_TAB_t IS

/* ---------------------------------------------------------------------------
    Version: 1.0.0
   --------------------------------------------------------------------------- */

    sync_rtags		   ACCMGR_NUMBER_TAB_t := ACCMGR_NUMBER_TAB_t();
	pos				   NUMBER;
	in_list			   VARCHAR2(4000) := sInList || ',';

BEGIN

	IF NOT sInList IS NULL
	THEN
		LOOP
	        EXIT WHEN in_list IS NULL;
	        pos := INSTR ( in_list, ',' );
	        sync_rtags.extend;
	        sync_rtags(sync_rtags.count) := LTRIM ( RTRIM ( SUBSTR ( in_list, 1, pos-1 ) ) );
	        in_list := SUBSTR ( in_list, pos+1 );
		END LOOP;
	END IF;

	RETURN sync_rtags;
END IN_LIST_NUMBER;

/

/
--------------------------------------------------------
--  DDL for Function IS_SAME_STRING
--------------------------------------------------------

  CREATE OR REPLACE FUNCTION "IS_SAME_STRING" ( sStringOne IN VARCHAR2,
	   	  		  		   				  	sStringTwo IN VARCHAR2 ) RETURN BOOLEAN IS

/* ---------------------------------------------------------------------------
    Version: 1.0.0
   --------------------------------------------------------------------------- */
   
ReturnValue BOOLEAN DEFAULT FALSE;
   
BEGIN

	--- Compare ---
	IF ( NVL( sStringOne, '' ) || 'APPEND' = NVL( sStringTwo, '' ) || 'APPEND' ) THEN
	   -- Strings are the same
	   ReturnValue := TRUE;
	   
	END IF; 

	RETURN ReturnValue;
END IS_SAME_STRING;

/

/
--------------------------------------------------------
--  DDL for Package PK_AMUTILS
--------------------------------------------------------

  CREATE OR REPLACE PACKAGE "PK_AMUTILS" IS

	PROCEDURE Log_Access ( nEvent IN LOGIN_TRAIL.ENUM_EVENT%TYPE,
			  			   sUserName IN LOGIN_TRAIL.USER_NAME%TYPE,
						   sClientIp IN LOGIN_TRAIL.CLIENT_IP%TYPE,
						   nAppId IN LOGIN_TRAIL.APP_ID%TYPE,
						   sComments IN LOGIN_TRAIL.COMMENTS%TYPE,
						   nUserId IN NUMBER DEFAULT NULL );		
						   
	FUNCTION Get_Hash ( sText IN VARCHAR2 ) RETURN VARCHAR2;					   			  						  				  				 

END pk_AMUtils;

/

/
--------------------------------------------------------
--  DDL for Package PK_APPLICATION
--------------------------------------------------------

  CREATE OR REPLACE PACKAGE "PK_APPLICATION" IS

PROCEDURE Add_Application ( sAppName IN APPLICATIONS.APPLICATION_NAME%TYPE,
		  				  	sAppAcronym IN APPLICATIONS.ACRONYM%TYPE );

PROCEDURE Remove_Application ( nAppId IN APPLICATIONS.APP_ID%TYPE );							
					  						  				  				 

END pk_Application;

/

/
--------------------------------------------------------
--  DDL for Package PK_CONTROL
--------------------------------------------------------

  CREATE OR REPLACE PACKAGE "PK_CONTROL" IS


PROCEDURE Add_Control ( sObjName IN CONTROL_OBJECTS.OBJ_NAME%TYPE,
		  		   	  	sObjDescription IN CONTROL_OBJECTS.OBJ_DESCRIPTION%TYPE,
					 	nAppId IN CONTROL_OBJECTS.APP_ID%TYPE
		  		   	 	); 
						
PROCEDURE Remove_Control ( sObjIdList IN VARCHAR2 );	

								 
PROCEDURE Set_Row_Permissions ( nDtId IN DATA_PERMISSIONS.DT_ID%TYPE,
							  	nRoleId IN DATA_PERMISSIONS.ROLE_ID%TYPE,
							 	nRefCol IN DATA_PERMISSIONS.REF_COLUMN_VAL%TYPE,
						     	cIsVisible IN DATA_PERMISSIONS.PERM_VALUE%TYPE DEFAULT NULL,
						     	cIsActive IN DATA_PERMISSIONS.PERM_VALUE%TYPE  DEFAULT NULL );
								
								 
PROCEDURE Delete_Data_Permission ( nDtId IN DATA_PERMISSIONS.DT_ID%TYPE,
								   nRoleId IN DATA_PERMISSIONS.ROLE_ID%TYPE,
								   nRefCol IN DATA_PERMISSIONS.REF_COLUMN_VAL%TYPE );
								   
PROCEDURE Add_DataTable ( nObjId IN DATA_TABLES.OBJ_ID%TYPE,
						  sTableName IN DATA_TABLES.TABLE_NAME%TYPE,
						  sRefColumn IN DATA_TABLES.REF_COLUMN_NAME%TYPE,
						  sDisplayColumn IN DATA_TABLES.DISPLAY_COLUMN_NAME%TYPE
		  		   	 	);								   
								   
PROCEDURE Remove_DataTable ( DtId IN NUMBER );								   
								   								 	


END pk_Control;

/

/
--------------------------------------------------------
--  DDL for Package PK_ROLE
--------------------------------------------------------

  CREATE OR REPLACE PACKAGE "PK_ROLE" IS


PROCEDURE Add_Role ( sRoleName IN ROLES.ROLE_NAME%TYPE,
		  		   	 sRoleComments IN ROLES.COMMENTS%TYPE,
				   	 nAppId IN ROLES.APP_ID%TYPE  );  
					 
PROCEDURE Remove_Role ( sRoleIdList IN VARCHAR2 );					 

PROCEDURE Set_Role_Permissions ( nRoleId IN ROLE_PRIVILEGES.ROLE_ID%TYPE,
		  					   	 nObjId IN ROLE_PRIVILEGES.OBJ_ID%TYPE,
							   	 cIsVisible IN ROLE_PRIVILEGES.PERM_VALUE%TYPE DEFAULT NULL,
							   	 cIsActive IN ROLE_PRIVILEGES.PERM_VALUE%TYPE  DEFAULT NULL );
								 
PROCEDURE Set_Role_Variation_Permissions ( nUserId IN USER_ROLES.USER_ID%TYPE,
		  								   nAppId IN ROLES.APP_ID%TYPE,
										   nRoleId IN ROLES.ROLE_ID%TYPE,
		  								   nObjId IN ROLE_PRIVILEGES.OBJ_ID%TYPE,
									   	   cIsVisible IN ROLE_PRIVILEGES.PERM_VALUE%TYPE DEFAULT NULL,
									   	   cIsActive IN ROLE_PRIVILEGES.PERM_VALUE%TYPE  DEFAULT NULL );
										   
FUNCTION Is_Permissions_Changed ( nRoleId IN ROLE_PRIVILEGES.ROLE_ID%TYPE,
  								  nObjId IN ROLE_PRIVILEGES.OBJ_ID%TYPE,
							   	  cIsVisible IN ROLE_PRIVILEGES.PERM_VALUE%TYPE,
							   	  cIsActive IN ROLE_PRIVILEGES.PERM_VALUE%TYPE ) RETURN BOOLEAN;	
								  
FUNCTION Is_Role_Variation ( nRoleId IN ROLES.ROLE_ID%TYPE ) RETURN BOOLEAN;								  									   								 
							  					 
PROCEDURE Delete_Role_Permission ( nRoleId IN ROLE_PRIVILEGES.ROLE_ID%TYPE,
		  						  nObjId IN ROLE_PRIVILEGES.OBJ_ID%TYPE,
								  nPermId IN ROLE_PRIVILEGES.PERM_ID%TYPE );	
								  
PROCEDURE Grant_Role ( sRoleIdList IN VARCHAR2,
		  			   nUserId IN USER_ROLES.USER_ID%TYPE );
					   
PROCEDURE Revoke_Role ( sRoleIdList IN VARCHAR2,
		  			    nUserId IN USER_ROLES.USER_ID%TYPE );					   								  				  
					  						  				  				 

END pk_Role;

/

/
--------------------------------------------------------
--  DDL for Package PK_SECURITY
--------------------------------------------------------

  CREATE OR REPLACE PACKAGE "PK_SECURITY" IS      

/*
------------------------------
||  Last Modified:  S.Vukovic
||  Modified Date:  28/Apr/2005  
||  Spec Version:   1.0
------------------------------
*/

	TYPE typeCur IS REF CURSOR;  
    
    /*================================================================================================*/
    --FUNCTION GET_USER_BY_ID ( ID IN NUMBER, records OUT typeCur ) RETURN NUMBER;
    --FUNCTION GET_USER_BY_USERNAME ( UserName IN VARCHAR2, records OUT typeCur ) RETURN NUMBER;   
    --FUNCTION GET_USER_PERMISSIONS ( UserSK IN NUMBER, ActionNameFilter IN VARCHAR2, records OUT typeCur ) RETURN NUMBER;   
    --FUNCTION AUTHENTICATE ( UserName IN VARCHAR2,
    --                        UserPassword IN VARCHAR2,
    --                       records OUT typeCur ) RETURN NUMBER;
    /*================================================================================================*/
	PROCEDURE GET_USER_STATIC_PERMISSIONS ( UsedId IN NUMBER, AppId IN NUMBER, RecordSet OUT typeCur);
	PROCEDURE GET_USER_DATA_PERMISSIONS ( UsedId IN NUMBER, AppId IN NUMBER, RecordSet OUT typeCur);
	FUNCTION IS_LOCAL_PASSWORD_CORRECT ( UserName IN VARCHAR2, UserPassword IN VARCHAR2 ) RETURN NUMBER;
	FUNCTION Set_Password ( sUserName IN VARCHAR2, sPasswordA IN VARCHAR2, sPasswordB IN VARCHAR2 ) RETURN NUMBER;
	/*================================================================================================*/

END PK_SECURITY;

/

/
--------------------------------------------------------
--  DDL for Package PK_USER
--------------------------------------------------------

  CREATE OR REPLACE PACKAGE "PK_USER" IS
/*
------------------------------
||  Last Modified:  J.Tweddle
||  Modified Date:  21/Jan/2008
||  Spec Version:   2.1
------------------------------
*/

PROCEDURE Add_User_Account ( sFullName IN USERS.FULL_NAME%TYPE,
		  		   	 	   	 sUserName IN USERS.USER_NAME%TYPE,
							 sUserEmail IN USERS.USER_EMAIL%TYPE,
							 sDomain IN USERS.DOMAIN%TYPE   );
                                                         
PROCEDURE Update_User_Account ( sUserId IN VARCHAR2, sFullName IN USERS.FULL_NAME%TYPE,
		  		   	 	   	 sUserName IN USERS.USER_NAME%TYPE,
							 sUserEmail IN USERS.USER_EMAIL%TYPE,
							 sDomain IN USERS.DOMAIN%TYPE   );

PROCEDURE Add_Application_User ( sUserIdList IN VARCHAR2,
		  					   	 nAppId IN USER_APPLICATIONS.APP_ID%TYPE,
								 cIncludeEveryone IN CHAR );
								 
PROCEDURE Remove_Application_User ( sUserIdList IN VARCHAR2,
		  					   	  	nAppId IN USER_APPLICATIONS.APP_ID%TYPE,
									cIncludeEveryone IN CHAR DEFAULT NULL );	

PROCEDURE Add_Role_Member ( sUserIdList IN VARCHAR2,
		  					nRoleId IN USER_ROLES.ROLE_ID%TYPE,
							cIncludeEveryone IN CHAR );
																
PROCEDURE Remove_Role_Member ( sUserIdList IN VARCHAR2,
		  					   nRoleId IN USER_ROLES.ROLE_ID%TYPE,
							   cIncludeEveryone IN CHAR DEFAULT NULL);									
									
PROCEDURE Disable_Users ( sUserIdList IN VARCHAR2,
 					   	  nAppId IN USER_APPLICATIONS.APP_ID%TYPE );	
						  
PROCEDURE Remove_User_Account ( sUserIdList IN VARCHAR2 );
						  
END pk_user;

/

/
--------------------------------------------------------
--  DDL for Package Body PK_AMUTILS
--------------------------------------------------------

  CREATE OR REPLACE PACKAGE BODY "PK_AMUTILS" 
IS
/* ---------------------------------------------------------------------------
    Version: 1.0.0
   --------------------------------------------------------------------------- */

/*--------------------------------------------------------------------------------------------------*/
PROCEDURE Log_Access ( nEvent IN LOGIN_TRAIL.ENUM_EVENT%TYPE,
		  			   sUserName IN LOGIN_TRAIL.USER_NAME%TYPE,
					   sClientIp IN LOGIN_TRAIL.CLIENT_IP%TYPE,
					   nAppId IN LOGIN_TRAIL.APP_ID%TYPE,
					   sComments IN LOGIN_TRAIL.COMMENTS%TYPE,
					   nUserId IN NUMBER DEFAULT NULL ) IS

UserName USERS.USER_NAME%TYPE; 						 
				  							 
BEGIN
	/*--------------- Business Rules Here -------------------*/
	IF nUserId IS NOT NULL THEN
	   SELECT USER_NAME  INTO  UserName  FROM USERS  WHERE USER_ID = nUserId;
	ELSE
	   UserName := sUserName;
	END IF;
	/*-------------------------------------------------------*/
	
		
	-- Insert Login Trail
	INSERT INTO LOGIN_TRAIL ( ENUM_EVENT, USER_NAME, CLIENT_IP, APP_ID, STAMP, COMMENTS ) 
	VALUES ( nEvent,
		   	 UserName,
			 sClientIp, 
			 nAppId,
			 TO_DATE( TO_CHAR( SYSDATE,'DD-MON-YYYY HH24:MI:SS' ),'DD-MON-YYYY HH24:MI:SS' ),
			 sComments
	   	    );
	
END	Log_Access;
/*--------------------------------------------------------------------------------------------------*/
FUNCTION Get_Hash ( sText IN VARCHAR2 ) RETURN VARCHAR2 IS

				  							 
BEGIN
	/*--------------- Business Rules Here -------------------*/
	/*-------------------------------------------------------*/
	
	RETURN DBMS_OBFUSCATION_TOOLKIT.MD5( input_string => sText );
 	
END	Get_Hash;
/*--------------------------------------------------------------------------------------------------*/


END pk_AMUtils;

/

/
--------------------------------------------------------
--  DDL for Package Body PK_APPLICATION
--------------------------------------------------------

  CREATE OR REPLACE PACKAGE BODY "PK_APPLICATION" 
IS
/* ---------------------------------------------------------------------------
    Version: 1.0.0
   --------------------------------------------------------------------------- */

/*--------------------------------------------------------------------------------------------------*/
PROCEDURE Add_Application ( sAppName IN APPLICATIONS.APPLICATION_NAME%TYPE,
		  				  	sAppAcronym IN APPLICATIONS.ACRONYM%TYPE ) IS
						 
AppId NUMBER;

CURSOR curAppAcronym IS 
		SELECT app.ACRONYM
		  FROM APPLICATIONS app
		 WHERE app.ACRONYM = sAppAcronym;
recAppAcronym curAppAcronym%ROWTYPE;

				  							 
BEGIN
	/*--------------- Business Rules Here -------------------*/
	
	-- Check for duplicate acronyms
	OPEN curAppAcronym;
	FETCH curAppAcronym INTO recAppAcronym;
	
	IF curAppAcronym%FOUND
	THEN
		RAISE_APPLICATION_ERROR (-20000, 'Application Acronym <b>'|| sAppAcronym ||'</b> is already used.' );
		
	END IF;
	
	CLOSE curAppAcronym;
	
	/*-------------------------------------------------------*/
	
	/*+++++ INSERT APPLICATION ++++++++*/
	BEGIN
		-- Get app_id
		SELECT SEQ_APP_ID.NEXTVAL INTO AppId FROM DUAL;
		
		-- Insert new Application
		INSERT INTO APPLICATIONS ( APP_ID, APPLICATION_NAME, ACRONYM ) 
		VALUES ( AppId, sAppName, sAppAcronym );
	
	
	EXCEPTION
    WHEN DUP_VAL_ON_INDEX
	THEN		
		RAISE_APPLICATION_ERROR (-20000, 'Application Name <b>'|| sAppName ||'</b> is already used.');
	END;
	/*+++++ END INSERT APPLICATION +++++*/
	
	
END	Add_Application;
/*--------------------------------------------------------------------------------------------------*/
PROCEDURE Remove_Application ( nAppId IN APPLICATIONS.APP_ID%TYPE ) IS
						 
rowCount NUMBER DEFAULT 0;
			  							 
BEGIN
	/*--------------- Business Rules Here -------------------*/
	
	-- Check if any Users are using this Application
	SELECT Count(*) INTO rowCount
	  FROM USER_APPLICATIONS ua
	 WHERE ua.APP_ID = nAppId;
	 
	IF rowCount > 0 THEN
	   RAISE_APPLICATION_ERROR (-20000, 'Some Users are still assigned to this Application ( Counted '|| rowCount ||' ).' );
	END IF; 
	
	-- Check if any Roles are using this Application
	SELECT Count(*) INTO rowCount
  	  FROM ROLES ro 
	 WHERE ro.APP_ID = nAppId;
	 
	IF rowCount > 0 THEN
	   RAISE_APPLICATION_ERROR (-20000, 'Some Roles are still assigned to this Application ( Counted '|| rowCount ||' ).' );
	END IF;
	
	/*-------------------------------------------------------*/
	
	
	-- Remove Application --
	DELETE 
	  FROM APPLICATIONS
	 WHERE APP_ID = nAppId;
	
	
END	Remove_Application;
/*--------------------------------------------------------------------------------------------------*/


END pk_Application;

/

/
--------------------------------------------------------
--  DDL for Package Body PK_CONTROL
--------------------------------------------------------

  CREATE OR REPLACE PACKAGE BODY "PK_CONTROL" 
IS
/* ---------------------------------------------------------------------------
    Version: 1.0.0
   --------------------------------------------------------------------------- */

/*--------------------------------------------------------------------------------------------------*/
PROCEDURE Add_Control ( sObjName IN CONTROL_OBJECTS.OBJ_NAME%TYPE,
		  		   	  	sObjDescription IN CONTROL_OBJECTS.OBJ_DESCRIPTION%TYPE,
					 	nAppId IN CONTROL_OBJECTS.APP_ID%TYPE
		  		   	 	) IS
						 
ObjID NUMBER;

CURSOR curPermissionTypes IS 
		SELECT pt.PERM_ID
  		  FROM PERMISSION_TYPES pt;
recPermissionTypes curPermissionTypes%ROWTYPE;

				  							 
BEGIN
	/*--------------- Business Rules Here -------------------*/
	/*-------------------------------------------------------*/
	
	-- Get obj_id
	SELECT SEQ_OBJ_ID.NEXTVAL INTO ObjID FROM DUAL;
	
	-- Insert new Control Object
	INSERT INTO CONTROL_OBJECTS ( OBJ_ID, APP_ID, OBJ_NAME, PARENT_OBJ_ID, OBJ_DESCRIPTION ) 
	VALUES ( ObjID, nAppId, sObjName, NULL, sObjDescription );
	
	
	
	
	/* Set default permissions to all roles */
	
	/*
	OPEN curPermissionTypes;
	FETCH curPermissionTypes INTO recPermissionTypes;
	
	WHILE curPermissionTypes%FOUND
	LOOP
		INSERT INTO ROLE_PRIVILEGES ( ROLE_ID, OBJ_ID, PERM_ID, PERM_VALUE )
		SELECT ROLE_ID, 
			   ObjID AS OBJ_ID, 
			   recPermissionTypes.PERM_ID AS PERM_ID, 
			   'Y' AS PERM_VALUE
		  FROM ROLES
		 WHERE IS_ROLE_VARIATION != 'Y';
		
		
		FETCH curPermissionTypes INTO recPermissionTypes;
	END LOOP;
	CLOSE curPermissionTypes;
	*/
	
	EXCEPTION
    WHEN DUP_VAL_ON_INDEX
	THEN		
		RAISE_APPLICATION_ERROR (-20000, 'Control Name '|| sObjName ||' is already used in this Application.');

	
END	Add_Control;
/*--------------------------------------------------------------------------------------------------*/
PROCEDURE Remove_Control ( sObjIdList IN VARCHAR2 ) IS
						 
rowCount NUMBER DEFAULT 0;
			  							 
BEGIN
	/*--------------- Business Rules Here -------------------*/
	
	-- Check if any Pages use this control
	SELECT Count(*) INTO rowCount
	  FROM PAGE_CONTROL_OBJECTS pco
	 WHERE pco.OBJ_ID IN  (
						   SELECT *
					   	     FROM THE ( SELECT CAST( IN_LIST_NUMBER( sObjIdList ) AS ACCMGR_NUMBER_TAB_t ) FROM dual ) 
	   	   			   	   );
	 
	IF rowCount > 0 THEN
	   RAISE_APPLICATION_ERROR (-20000, 'Some Pages are still using this Control ( Counted '|| rowCount ||' ).' );
	END IF; 
	
	
	-- Check if any Roles use this control
	SELECT Count(*) INTO rowCount
  	  FROM ROLE_PRIVILEGES rp 
	 WHERE rp.OBJ_ID IN (
						   SELECT *
					   	     FROM THE ( SELECT CAST( IN_LIST_NUMBER( sObjIdList ) AS ACCMGR_NUMBER_TAB_t ) FROM dual ) 
	   	   			   	   );
	 
	IF rowCount > 0 THEN
	   RAISE_APPLICATION_ERROR (-20000, 'Some Roles are still using this Control ( Counted '|| rowCount ||' ).' );
	END IF;
	
	
	-- Check if any Data tables use this control
	SELECT Count(*) INTO rowCount
	  FROM DATA_TABLES dt,
	  	   DATA_PERMISSIONS dp 
	 WHERE dt.OBJ_ID IN   (
						   SELECT *
					   	     FROM THE ( SELECT CAST( IN_LIST_NUMBER( sObjIdList ) AS ACCMGR_NUMBER_TAB_t ) FROM dual ) 
	   	   			   	   )
	   AND dt.DT_ID = dp.DT_ID;
	 
	IF rowCount > 0 THEN
	   RAISE_APPLICATION_ERROR (-20000, 'Some Data Filters are still in use by Roles ( Counted '|| rowCount ||' ).' );
	END IF;
	
	/*-------------------------------------------------------*/
	
	
	-- Remove Data Filter --
	DELETE 
	  FROM DATA_TABLES dt
	 WHERE dt.OBJ_ID IN   (
						   SELECT *
					   	     FROM THE ( SELECT CAST( IN_LIST_NUMBER( sObjIdList ) AS ACCMGR_NUMBER_TAB_t ) FROM dual ) 
	   	   			   	   );
	
	-- Remove Control -- 
	DELETE 
	  FROM CONTROL_OBJECTS co
	 WHERE co.OBJ_ID IN   (
						   SELECT *
					   	     FROM THE ( SELECT CAST( IN_LIST_NUMBER( sObjIdList ) AS ACCMGR_NUMBER_TAB_t ) FROM dual ) 
	   	   			   	   );
	
	
END	Remove_Control;
/*--------------------------------------------------------------------------------------------------*/
PROCEDURE Add_DataTable ( nObjId IN DATA_TABLES.OBJ_ID%TYPE,
						  sTableName IN DATA_TABLES.TABLE_NAME%TYPE,
						  sRefColumn IN DATA_TABLES.REF_COLUMN_NAME%TYPE,
						  sDisplayColumn IN DATA_TABLES.DISPLAY_COLUMN_NAME%TYPE
		  		   	 	) IS
						 
DtID NUMBER;

				  							 
BEGIN
	/*--------------- Business Rules Here -------------------*/
	/*-------------------------------------------------------*/
	
	-- Get dt_id
	SELECT SEQ_DT_ID.NEXTVAL INTO DtID FROM DUAL;
	
	-- Insert new Control Object
	INSERT INTO DATA_TABLES ( DT_ID, OBJ_ID, TABLE_NAME, REF_COLUMN_NAME, DISPLAY_COLUMN_NAME )
	VALUES ( DtID, nObjId, sTableName, sRefColumn, sDisplayColumn );
	
	
	EXCEPTION
    WHEN DUP_VAL_ON_INDEX
	THEN		
		RAISE_APPLICATION_ERROR (-20000, 'This Data Table Reference is already used in this Action object.');

	
END	Add_DataTable;
/*--------------------------------------------------------------------------------------------------*/
PROCEDURE Remove_DataTable ( DtId IN NUMBER ) IS
						 
			  							 
BEGIN
	/*--------------- Business Rules Here -------------------*/
	
	/*-------------------------------------------------------*/
	
	
	-- Remove Data Permissions --
	DELETE 
	  FROM DATA_PERMISSIONS dp
	 WHERE dp.DT_ID = DtId;
	
	-- Remove Data Table -- 
	DELETE 
	  FROM DATA_TABLES dt
	 WHERE dt.DT_ID = DtId;
	
	
END	Remove_DataTable;
/*--------------------------------------------------------------------------------------------------*/
PROCEDURE Set_Row_Permissions ( nDtId IN DATA_PERMISSIONS.DT_ID%TYPE,
							  	nRoleId IN DATA_PERMISSIONS.ROLE_ID%TYPE,
							 	nRefCol IN DATA_PERMISSIONS.REF_COLUMN_VAL%TYPE,
						     	cIsVisible IN DATA_PERMISSIONS.PERM_VALUE%TYPE DEFAULT NULL,
						     	cIsActive IN DATA_PERMISSIONS.PERM_VALUE%TYPE  DEFAULT NULL ) IS
						 
nPermId PERMISSION_TYPES.PERM_ID%TYPE DEFAULT 0;
			  							 
BEGIN
	/*--------------- Business Rules Here -------------------*/
	/*-------------------------------------------------------*/
	
	-- Delete Existing Permission --
	Delete_Data_Permission ( nDtId, nRoleId, nRefCol );
	
	
	----- Set Data Permission -----
	
	-- Get PermId for "Visible"
	SELECT pt.PERM_ID INTO nPermId FROM PERMISSION_TYPES pt WHERE pt.PERM_NAME = 'enumVISIBLE';
	
	
	
	IF (cIsVisible IS NOT NULL) THEN
	   
	   INSERT INTO DATA_PERMISSIONS ( DT_ID, ROLE_ID, REF_COLUMN_VAL, PERM_ID, PERM_VALUE )
	   VALUES ( nDtId, nRoleId, nRefCol, nPermId, cIsVisible );
	   
	END IF;
	
	
	
	-- Get PermId for "Active"
	SELECT pt.PERM_ID INTO nPermId FROM PERMISSION_TYPES pt WHERE pt.PERM_NAME = 'enumACTIVE';
	
	
	
	IF (cIsActive IS NOT NULL) THEN
	   
	   INSERT INTO DATA_PERMISSIONS ( DT_ID, ROLE_ID, REF_COLUMN_VAL, PERM_ID, PERM_VALUE )
	   VALUES ( nDtId, nRoleId, nRefCol, nPermId, cIsActive );
	   
	END IF;
	
	
	
END	Set_Row_Permissions;
/*--------------------------------------------------------------------------------------------------*/
PROCEDURE Delete_Data_Permission ( nDtId IN DATA_PERMISSIONS.DT_ID%TYPE,
								   nRoleId IN DATA_PERMISSIONS.ROLE_ID%TYPE,
								   nRefCol IN DATA_PERMISSIONS.REF_COLUMN_VAL%TYPE ) IS
						 
				  							 
BEGIN
	/*--------------- Business Rules Here -------------------*/
	IF (nDtId IS NULL) OR (nRoleId IS NULL) OR (nRefCol IS NULL)
	THEN
		RAISE_APPLICATION_ERROR (-20000, 'Missing some parameters! nDtId= '|| nDtId ||', nRoleId='|| nRoleId ||', nRefCol='|| nRefCol );
		
	END IF;
	/*-------------------------------------------------------*/

	DELETE
	  FROM DATA_PERMISSIONS dp
	 WHERE dp.ROLE_ID = nRoleId
	   AND dp.DT_ID = nDtId
	   AND dp.REF_COLUMN_VAL = nRefCol;
	
	
END	Delete_Data_Permission;
/*--------------------------------------------------------------------------------------------------*/


END pk_Control;

/

/
--------------------------------------------------------
--  DDL for Package Body PK_ROLE
--------------------------------------------------------

  CREATE OR REPLACE PACKAGE BODY "PK_ROLE" 
         IS

         /*--------------------------------------------------------------------------------------------------*/
         PROCEDURE Add_Role ( sRoleName IN ROLES.ROLE_NAME%TYPE,
                              sRoleComments IN ROLES.COMMENTS%TYPE,
                              nAppId IN ROLES.APP_ID%TYPE  ) IS

         RoleID NUMBER;

         BEGIN
            /*--------------- Business Rules Here -------------------*/
            /*-------------------------------------------------------*/


            -- Get role_id
            SELECT SEQ_ROLE_ID.NEXTVAL INTO RoleID FROM DUAL;

            -- Insert new Role
            INSERT INTO ROLES ( ROLE_ID, APP_ID, ROLE_NAME, IS_ROLE_VARIATION, COMMENTS )
            VALUES ( RoleID, nAppId, sRoleName, NULL, sRoleComments );


            EXCEPTION
            WHEN DUP_VAL_ON_INDEX
            THEN
               RAISE_APPLICATION_ERROR (-20000, 'Role Name '|| sRoleName ||' is already used in this Application.');

         END   Add_Role;
         /*--------------------------------------------------------------------------------------------------*/
         PROCEDURE Remove_Role ( sRoleIdList IN VARCHAR2 ) IS

         rowCount NUMBER DEFAULT 0;

         BEGIN
            /*--------------- Business Rules Here -------------------*/

            -- Check if any Users user this role
            SELECT Count(*) INTO rowCount
            FROM USER_ROLES ur
            WHERE ur.ROLE_ID IN  (SELECT * FROM THE ( SELECT CAST( IN_LIST_NUMBER( sRoleIdList ) AS ACCMGR_NUMBER_TAB_t ) FROM dual ) );

            IF rowCount > 0 THEN
               RAISE_APPLICATION_ERROR (-20000, 'Some Users are still using this Role ( Counted '|| rowCount ||' ).' );
            END IF;

            /*-------------------------------------------------------*/

            -- Remove Role Privileges --
            DELETE
            FROM ROLE_PRIVILEGES rp
            WHERE rp.ROLE_ID IN  ( SELECT * FROM THE ( SELECT CAST( IN_LIST_NUMBER( sRoleIdList ) AS ACCMGR_NUMBER_TAB_t ) FROM dual ) );

            -- Remove Role Data Permissions --
            DELETE
            FROM DATA_PERMISSIONS dp
            WHERE dp.ROLE_ID IN  ( SELECT * FROM THE ( SELECT CAST( IN_LIST_NUMBER( sRoleIdList ) AS ACCMGR_NUMBER_TAB_t ) FROM dual ) );

            -- Remove Role --
            DELETE
            FROM ROLES ro
            WHERE ro.ROLE_ID IN  ( SELECT * FROM THE ( SELECT CAST( IN_LIST_NUMBER( sRoleIdList ) AS ACCMGR_NUMBER_TAB_t ) FROM dual ) );


         END   Remove_Role;
         /*--------------------------------------------------------------------------------------------------*/
         PROCEDURE Set_Role_Permissions ( nRoleId IN ROLE_PRIVILEGES.ROLE_ID%TYPE,
                                          nObjId IN ROLE_PRIVILEGES.OBJ_ID%TYPE,
                                          cIsVisible IN ROLE_PRIVILEGES.PERM_VALUE%TYPE DEFAULT NULL,
                                          cIsActive IN ROLE_PRIVILEGES.PERM_VALUE%TYPE  DEFAULT NULL ) IS

         nPermId PERMISSION_TYPES.PERM_ID%TYPE DEFAULT 0;

         BEGIN
            /*--------------- Business Rules Here -------------------*/
            IF (nRoleId IS NULL) OR (nObjId IS NULL)
            THEN
               RAISE_APPLICATION_ERROR (-20000, 'Missing some parameters! nRoleId= '|| nRoleId ||', nObjId='|| nObjId );

            END IF;
            /*-------------------------------------------------------*/


            --- Set "Visible" state ----------------------------------

            -- Get PermId for "Visible"
            SELECT pt.PERM_ID INTO nPermId FROM PERMISSION_TYPES pt WHERE pt.PERM_NAME = 'enumVISIBLE';

            Delete_Role_Permission ( nRoleId, nObjId, nPermId );

            IF cIsVisible IS NOT NULL THEN

               INSERT INTO ROLE_PRIVILEGES ( ROLE_ID, OBJ_ID, PERM_ID, PERM_VALUE )
               VALUES ( nRoleId, nObjId, nPermId, cIsVisible );

            ELSE
               IF cIsActive IS NOT NULL THEN
                  -- If "Active" is Set then "Visible" must be "SHOW=Y"

                  INSERT INTO ROLE_PRIVILEGES ( ROLE_ID, OBJ_ID, PERM_ID, PERM_VALUE )
                  VALUES ( nRoleId, nObjId, nPermId, 'Y' );

               END IF;

            END IF;

            --- Set "Active" state ----------------------------------

            -- Get PermId for "Visible"
            SELECT pt.PERM_ID INTO nPermId FROM PERMISSION_TYPES pt WHERE pt.PERM_NAME = 'enumACTIVE';

            Delete_Role_Permission ( nRoleId, nObjId, nPermId );

            IF ( cIsActive IS NOT NULL ) AND ( cIsVisible <> 'N') THEN

               INSERT INTO ROLE_PRIVILEGES ( ROLE_ID, OBJ_ID, PERM_ID, PERM_VALUE )
               VALUES ( nRoleId, nObjId, nPermId, cIsActive );

            END IF;


         END   Set_Role_Permissions;
         /*--------------------------------------------------------------------------------------------------*/
         PROCEDURE Set_Role_Variation_Permissions ( nUserId IN USER_ROLES.USER_ID%TYPE,
                                                   nAppId IN ROLES.APP_ID%TYPE,
                                                   nRoleId IN ROLES.ROLE_ID%TYPE,
                                                   nObjId IN ROLE_PRIVILEGES.OBJ_ID%TYPE,
                                                   cIsVisible IN ROLE_PRIVILEGES.PERM_VALUE%TYPE DEFAULT NULL,
                                                   cIsActive IN ROLE_PRIVILEGES.PERM_VALUE%TYPE  DEFAULT NULL ) IS

         nPermId             PERMISSION_TYPES.PERM_ID%TYPE DEFAULT 0;
         nRoleVariationId    ROLES.ROLE_ID%TYPE DEFAULT NULL;
         sUserName           USERS.USER_NAME%TYPE;
         nDataPermCount      NUMBER;
         nRolePermCount      NUMBER;

         --- Get Role Variation Id ---
         CURSOR curRoleVariation IS
               SELECT ro.ROLE_ID
               FROM USER_ROLES ur,
                     ROLES ro
               WHERE ur.ROLE_ID = ro.ROLE_ID
                  AND ro.IS_ROLE_VARIATION = 'Y'
                  AND ur.USER_ID = nUserId;

         recRoleVariation curRoleVariation%ROWTYPE;

         BEGIN
            /*--------------- Business Rules Here -------------------*/
            IF (nUserId IS NULL) OR (nAppId IS NULL) OR (nObjId IS NULL)
            THEN
               RAISE_APPLICATION_ERROR (-20000, 'Missing some parameters! nUserId='|| nUserId ||', nAppId='|| nAppId ||', nObjId='|| nObjId);

            END IF;
            /*-------------------------------------------------------*/

            IF Is_Permissions_Changed( nRoleId, nObjId, cIsVisible, cIsActive ) THEN

               IF Is_Role_Variation ( nRoleId ) THEN
                  --- Set this user role permissions ---
                  Set_Role_Permissions ( nRoleId, nObjId, cIsVisible, cIsActive );


                  --- Remove this user role for no permission settings ---
                  -- Get Role Permissions Count
                  SELECT Count(*) INTO nRolePermCount  FROM ROLE_PRIVILEGES rp WHERE rp.ROLE_ID = nRoleVariationId;

                  -- Get Role Permissions Count
                  SELECT Count(*) INTO nDataPermCount  FROM DATA_PERMISSIONS dp WHERE dp.ROLE_ID = nRoleVariationId;


                  IF (nRolePermCount = 0) AND (nDataPermCount = 0) THEN
                     -- There are no permission settings, hence proceed to remove this user role
                     DELETE
                     FROM ROLES ro
                     WHERE ro.ROLE_ID = nRoleVariationId;

                  END IF;

               ELSE
                     --- Get Role Variation Id ---
                  OPEN curRoleVariation;
                  FETCH curRoleVariation INTO recRoleVariation;

                  IF curRoleVariation%FOUND THEN
                     nRoleVariationId := recRoleVariation.ROLE_ID;
                  END IF;

                  CLOSE curRoleVariation;

                  --- Create Role Variation if does not exist ---
                  IF nRoleVariationId IS NULL THEN

                     -- Get role_id
                     SELECT SEQ_ROLE_ID.NEXTVAL INTO nRoleVariationId FROM DUAL;

                     -- Get user_name
                     SELECT usr.USER_NAME INTO sUserName FROM USERS usr WHERE usr.USER_ID = nUserId;

                     -- Create Role Variation
                     INSERT INTO ROLES ( ROLE_ID, APP_ID, ROLE_NAME, IS_ROLE_VARIATION, COMMENTS )
                     VALUES ( nRoleVariationId, nAppId, UPPER( sUserName ) || '_SPECIFIC', 'Y', 'Auto-created role to define user specific permissions.');

                     -- Link this role to user
                     INSERT INTO USER_ROLES ( USER_ID, ROLE_ID )
                     VALUES ( nUserId, nRoleVariationId );

                  END IF;

                  --- Set this user role permissions ---
                  Set_Role_Permissions ( nRoleVariationId, nObjId, cIsVisible, cIsActive );

               END IF;

            END IF;


         END   Set_Role_Variation_Permissions;
         /*--------------------------------------------------------------------------------------------------*/
         FUNCTION Is_Permissions_Changed ( nRoleId IN ROLE_PRIVILEGES.ROLE_ID%TYPE,
                                          nObjId IN ROLE_PRIVILEGES.OBJ_ID%TYPE,
                                          cIsVisible IN ROLE_PRIVILEGES.PERM_VALUE%TYPE,
                                          cIsActive IN ROLE_PRIVILEGES.PERM_VALUE%TYPE ) RETURN BOOLEAN IS

         ReturnValue       BOOLEAN DEFAULT FALSE;
         cCurrentIsVisible ROLE_PRIVILEGES.PERM_VALUE%TYPE;
         cCurrentIsActive  ROLE_PRIVILEGES.PERM_VALUE%TYPE;

         CURSOR curCurrentIsVisible IS
               SELECT rp.PERM_VALUE
               FROM ROLE_PRIVILEGES rp
               WHERE rp.ROLE_ID = nRoleId
                  AND rp.OBJ_ID = nObjId
                  AND rp.PERM_ID = 1;
         recCurrentIsVisible curCurrentIsVisible%ROWTYPE;

         CURSOR curCurrentIsActive IS
               SELECT rp.PERM_VALUE
               FROM ROLE_PRIVILEGES rp
               WHERE rp.ROLE_ID = nRoleId
                  AND rp.OBJ_ID = nObjId
                  AND rp.PERM_ID = 2;
         recCurrentIsActive curCurrentIsActive%ROWTYPE;

         BEGIN
            /*--------------- Business Rules Here -------------------*/
            /*-------------------------------------------------------*/


            -- Get "Visible" Permission
            OPEN curCurrentIsVisible;
            FETCH curCurrentIsVisible INTO recCurrentIsVisible;

            IF curCurrentIsVisible%FOUND THEN
               cCurrentIsVisible := recCurrentIsVisible.PERM_VALUE;
            END IF;

            CLOSE curCurrentIsVisible;

            -- Get "Active" Permission
            OPEN curCurrentIsActive;
            FETCH curCurrentIsActive INTO recCurrentIsActive;

            IF curCurrentIsActive%FOUND THEN
               cCurrentIsActive := recCurrentIsActive.PERM_VALUE;
            END IF;

            CLOSE curCurrentIsActive;

            --- Compare ---
            IF NOT Is_Same_String( cCurrentIsVisible, cIsVisible ) OR NOT Is_Same_String( cCurrentIsActive, cIsActive )  THEN
               ReturnValue := TRUE;

            END IF;

            RETURN ReturnValue;
         END   Is_Permissions_Changed;
         /*--------------------------------------------------------------------------------------------------*/
         FUNCTION Is_Role_Variation ( nRoleId IN ROLES.ROLE_ID%TYPE ) RETURN BOOLEAN IS

         ReturnValue     BOOLEAN DEFAULT FALSE;
         cIsRoleVariation CHAR;

         BEGIN
            /*--------------- Business Rules Here -------------------*/
            /*-------------------------------------------------------*/

            -- Get is_role_variation
            SELECT ro.IS_ROLE_VARIATION INTO cIsRoleVariation
            FROM ROLES ro
            WHERE ro.ROLE_ID = nRoleId;

            IF cIsRoleVariation IS NOT NULL THEN
               ReturnValue := TRUE;
            END IF;

            RETURN ReturnValue;
         END   Is_Role_Variation;
         /*--------------------------------------------------------------------------------------------------*/
         PROCEDURE Delete_Role_Permission ( nRoleId IN ROLE_PRIVILEGES.ROLE_ID%TYPE,
                                          nObjId IN ROLE_PRIVILEGES.OBJ_ID%TYPE,
                                          nPermId IN ROLE_PRIVILEGES.PERM_ID%TYPE ) IS


         BEGIN
            /*--------------- Business Rules Here -------------------*/
            IF (nRoleId IS NULL) OR (nObjId IS NULL) OR (nPermId IS NULL)
            THEN
               RAISE_APPLICATION_ERROR (-20000, 'Missing some parameters! nRoleId= '|| nRoleId ||', nObjId='|| nObjId ||', nPermId='|| nPermId );

            END IF;
            /*-------------------------------------------------------*/

            DELETE
            FROM ROLE_PRIVILEGES rp
            WHERE rp.ROLE_ID = nRoleId
               AND rp.OBJ_ID = nObjId
               AND rp.PERM_ID = nPermId;


         END   Delete_Role_Permission;
         /*--------------------------------------------------------------------------------------------------*/
         PROCEDURE Grant_Role ( sRoleIdList IN VARCHAR2,
                              nUserId IN USER_ROLES.USER_ID%TYPE ) IS


         BEGIN
            /*--------------- Business Rules Here -------------------*/
            IF sRoleIdList IS NULL THEN
               RAISE_APPLICATION_ERROR (-20000, 'Please select at least one role.' );
            END IF;
            /*-------------------------------------------------------*/

            --- Grant Role(s) ---
            INSERT INTO USER_ROLES ( USER_ID, ROLE_ID )
            SELECT nUserId, qry.ROLE_ID
            FROM (
                  SELECT ro.ROLE_ID
                     FROM ROLES ro
                     WHERE ro.ROLE_ID IN ( SELECT * FROM THE ( SELECT CAST( IN_LIST_NUMBER( sRoleIdList ) AS ACCMGR_NUMBER_TAB_t ) FROM dual ) )
                  MINUS
                  SELECT ur.ROLE_ID
                     FROM USER_ROLES ur
                     WHERE ur.USER_ID = nUserId
                  ) qry;


         END   Grant_Role;
         /*--------------------------------------------------------------------------------------------------*/
         PROCEDURE Revoke_Role ( sRoleIdList IN VARCHAR2,
                                 nUserId IN USER_ROLES.USER_ID%TYPE ) IS

         nidcollector  ACCMGR_NUMBER_TAB_t := ACCMGR_NUMBER_TAB_t();
         roleId        NUMBER;
         roleName      ROLES.ROLE_NAME%TYPE;
         sUserSpec     USERS.USER_NAME%TYPE;

         BEGIN
            /*--------------- Business Rules Here -------------------*/
            IF sRoleIdList IS NULL THEN
               RAISE_APPLICATION_ERROR (-20000, 'Please select at least one role.' );
            END IF;
            /*-------------------------------------------------------*/

            --- Revoke Role(s) ---
            DELETE FROM USER_ROLES ur
            WHERE ur.USER_ID = nUserId
               AND ur.ROLE_ID IN (SELECT * FROM THE ( SELECT CAST( IN_LIST_NUMBER( sRoleIdList ) AS ACCMGR_NUMBER_TAB_t ) FROM dual ) );

            -- Get user_name
            SELECT usr.USER_NAME INTO sUserSpec FROM USERS usr WHERE usr.USER_ID = nUserId;
            sUserSpec := UPPER( sUserSpec ) || '_SPECIFIC';

            nidcollector := IN_LIST_NUMBER (sRoleIdList);

            FOR i IN 1 .. nidcollector.COUNT
            LOOP
               roleId := nidcollector (i);

               -- Get the role name
               select role_name into roleName from roles where role_id = roleId;

               -- See if the role name contains the users name
               IF (IS_SAME_STRING(roleName, sUserSpec) = TRUE) THEN
                  -- This is this users specific role and so we should remove it to ensure
                  -- database is kept in a good clean state for the next time a specific
                  -- role for this user is required

                  DELETE FROM ROLE_PRIVILEGES rp WHERE rp.ROLE_ID = roleId;

                  DELETE FROM DATA_PERMISSIONS dp WHERE dp.ROLE_ID = roleId;

                  DELETE FROM ROLES ro WHERE ro.ROLE_ID = roleId;
                  EXIT WHEN TRUE;
               END IF;

            END LOOP;
         END   Revoke_Role;
         /*--------------------------------------------------------------------------------------------------*/
         END pk_Role;

/

/
--------------------------------------------------------
--  DDL for Package Body PK_SECURITY
--------------------------------------------------------

  CREATE OR REPLACE PACKAGE BODY "PK_SECURITY" IS     

/*
------------------------------
||  Last Modified:  S.Vukovic
||  Modified Date:  28/Apr/2005  
||  Body Version:   1.0
------------------------------
*/

/*-------------------------------------------------------------------------------------------------------*/
FUNCTION IS_LOCAL_PASSWORD_CORRECT ( UserName IN VARCHAR2, UserPassword IN VARCHAR2 ) RETURN NUMBER IS
	encryptedUserPassword VARCHAR2(4000);
BEGIN
	SELECT usr.USER_PASSWORD INTO encryptedUserPassword
	  FROM USERS usr
	 WHERE usr.USER_NAME = UserName;
	 
	IF encryptedUserPassword = PK_AMUTILS.GET_HASH( UserPassword ) THEN
		-- Password Correct
		RETURN 1;
	ELSE
		-- Password Incorrect
		RETURN -1; 
	END IF;	 
END;
/*--------------------------------------------------------------------------------------------------*/
FUNCTION Set_Password ( sUserName IN VARCHAR2, sPasswordA IN VARCHAR2, sPasswordB IN VARCHAR2 ) RETURN NUMBER IS
						 
				  							 
BEGIN
	-- Used to set password for the first time. 
	-- It can only be set if previous password in null
	
	/*--------------- Business Rules Here -------------------*/
	/*-------------------------------------------------------*/
	
	IF sPasswordA = sPasswordB THEN
		-- Update passwords
		UPDATE USERS usr SET
		  usr.USER_PASSWORD = PK_AMUTILS.Get_Hash ( sPasswordA )
		WHERE usr.USER_NAME = sUserName
		  AND usr.USER_PASSWORD IS NULL;

		-- Successfull update
		RETURN 1;	  
		
	ELSE
		-- Password mistmatch
		RETURN -1;	
	END IF;
	
	
END;
/*-------------------------------------------------------------------------------------------------------*/
PROCEDURE GET_USER_STATIC_PERMISSIONS ( UsedId IN NUMBER, AppId IN NUMBER, RecordSet OUT typeCur) IS

BEGIN

	OPEN RecordSet FOR
	SELECT co.obj_name, perm.perm_id, perm.perm_value
	  FROM (
	  
	  	    (
	  	    /* All 'Y' User Permissions */	  
			
			/* Get all 'Y' permissions */
			SELECT DISTINCT rp.OBJ_ID, rp.PERM_ID, rp.PERM_VALUE
			  FROM USER_ROLES ur,
			  	   ROLE_PRIVILEGES rp,
				   ROLES ro
	         WHERE ur.ROLE_ID = ro.ROLE_ID
			   AND rp.ROLE_ID = ro.ROLE_ID
			   AND ur.USER_ID = UsedId
			   AND rp.PERM_VALUE = 'Y'	
			   --AND ro.APP_ID = AppId   
			MINUS
			/* Revoke permissions if they set to 'N'*/
			SELECT DISTINCT rp.OBJ_ID, rp.PERM_ID, 'Y' AS PERM_VALUE
			  FROM USER_ROLES ur,
			  	   ROLE_PRIVILEGES rp,
				   ROLES ro
	         WHERE ur.ROLE_ID = ro.ROLE_ID
			   AND rp.ROLE_ID = ro.ROLE_ID
			   AND ur.USER_ID = UsedId
			   AND rp.PERM_VALUE = 'N'	
			  -- AND ro.APP_ID = AppId
			   
			)
			
			MINUS    
			
			/* Role Variant Revokes */   
			SELECT DISTINCT rp.OBJ_ID, rp.perm_id, 'Y' AS perm_value
			  FROM role_privileges rp,
			       ROLES ro,
			       user_roles ur
			 WHERE ro.role_id = rp.role_id
			   AND ro.role_id = ur.role_id
			   AND rp.PERM_VALUE = 'N'
			   AND ro.IS_ROLE_VARIATION = 'Y'
			   AND ur.user_id = UsedId
			  -- AND ro.APP_ID = AppId 
			   
	  	   ) perm,
	       control_objects co
	 WHERE perm.obj_id = co.obj_id;
	  -- AND co.APP_ID = AppId;


END;
/*-------------------------------------------------------------------------------------------------------*/
PROCEDURE GET_USER_DATA_PERMISSIONS ( UsedId IN NUMBER, AppId IN NUMBER, RecordSet OUT typeCur) IS

BEGIN

	OPEN RecordSet FOR
	SELECT qry.TABLE_NAME,
	 	   qry.REF_COLUMN_VAL,
	 	   qry.PERM_ID,
	 	   qry.PERM_VALUE
	  FROM CONTROL_OBJECTS co,
		   DATA_TABLES dt,
			(
				
			/* Access Control Data Permissions */	
			 SELECT bl.DT_ID,
			 		bl.TABLE_NAME,
			 		bl.REF_COLUMN_VAL,
			 		bl.PERM_ID,
			 		DECODE ( yc.PERM_VALUE,
			 	 		   	 NULL, bl.PERM_VALUE,
			 			 	 yc.PERM_VALUE ) AS PERM_VALUE
			  FROM (
			       	/* Get base list for Data Permissions */   
				 	SELECT rol.ROLE_ID, dt.DT_ID, dt.TABLE_NAME, 0 AS REF_COLUMN_VAL, pt.PERM_ID, 'Y' AS PERM_VALUE 
				 	  FROM CONTROL_OBJECTS co,
				 	  	   DATA_TABLES dt,
				 		   PERMISSION_TYPES pt,
				 		   (
				 		    /* Get User Roles for this Application */
				 		    SELECT ro.*
				 			  FROM ROLES ro,
				 			  	   USER_ROLES ur
				 			 WHERE ur.ROLE_ID = ro.ROLE_ID
				 			   --AND ro.APP_ID = AppId
				 			   AND ur.USER_ID = UsedId
				 		   ) rol
				 	 WHERE dt.OBJ_ID = co.OBJ_ID
				 	   --AND co.APP_ID = AppId
			  	   ) bl,
			       (
			 		 /* Find only 'Y' Permissions, which will change 'All' permission to 'N' */
			 		 SELECT dt.DT_ID, 0 AS REF_COLUMN_VAL, dp.PERM_ID, 'N' AS PERM_VALUE, COUNT(*) AS YES_COUNT 
			 		  FROM DATA_PERMISSIONS dp,
			 		  	   DATA_TABLES dt,
			 			   ROLES ro,
			 			   USER_ROLES ur
			 		 WHERE ur.ROLE_ID = ro.ROLE_ID
			 		   AND dp.ROLE_ID = ro.ROLE_ID
			 		   AND dp.DT_ID = dt.DT_ID	 
			 		  -- AND ro.APP_ID = AppId 
			 		   AND ur.USER_ID = UsedId 
			 		   AND ro.IS_ROLE_VARIATION IS NULL
			 		 GROUP BY dt.DT_ID, dp.PERM_ID
			 		) yc 
			  WHERE yc.DT_ID (+) = bl.DT_ID
			    AND yc.PERM_ID (+) = bl.PERM_ID
			UNION
			(
			/* Get Filter Settings for All Permission Types */ 
			SELECT dt.DT_ID, dt.TABLE_NAME, dp.REF_COLUMN_VAL, dp.PERM_ID, dp.PERM_VALUE 
			  FROM DATA_PERMISSIONS dp,
			  	   DATA_TABLES dt,
				   ROLES ro,
				   USER_ROLES ur
			 WHERE ur.ROLE_ID = ro.ROLE_ID
			   AND dp.ROLE_ID = ro.ROLE_ID
			   AND dp.DT_ID = dt.DT_ID	 
			  -- AND ro.APP_ID = AppId 
			   AND ur.USER_ID = UsedId
			MINUS  
			/* Overwrite Data Permissions using User Specific Role */
			SELECT DISTINCT
				   dt.DT_ID, 
				   dt.TABLE_NAME,
				   dp.REF_COLUMN_VAL,
				   dp.PERM_ID,
				   DECODE ( dp.PERM_VALUE,
				   		  	'Y', 'N', 'Y' ) AS PERM_VALUE
			  FROM DATA_PERMISSIONS dp,
			  	   DATA_TABLES dt,
				   ROLES ro,
				   USER_ROLES ur
			 WHERE ur.ROLE_ID = ro.ROLE_ID
			   AND dp.ROLE_ID = ro.ROLE_ID
			   AND dp.DT_ID = dt.DT_ID	 
			 --  AND ro.APP_ID = AppId 
			   AND ur.USER_ID = UsedId 
			   AND ro.IS_ROLE_VARIATION = 'Y'   
			)
			
			) qry
	  WHERE dt.OBJ_ID = co.OBJ_ID
		AND dt.DT_ID = qry.DT_ID;
		--AND co.APP_ID = AppId;


END;
/*-------------------------------------------------------------------------------------------------------*/
END PK_SECURITY;

/

/
--------------------------------------------------------
--  DDL for Package Body PK_USER
--------------------------------------------------------

  CREATE OR REPLACE PACKAGE BODY "PK_USER" IS
/*
------------------------------
||  Last Modified:  J.Tweddle
||  Modified Date:  21/Jan/2008  
||  Body Version:   2.1
------------------------------
*/

/*--------------------------------------------------------------------------------------------------*/
PROCEDURE Add_User_Account ( sFullName IN USERS.FULL_NAME%TYPE,
		  		   	 	   	 sUserName IN USERS.USER_NAME%TYPE,
							 sUserEmail IN USERS.USER_EMAIL%TYPE,
							 sDomain IN USERS.DOMAIN%TYPE   ) IS
						 
UserId NUMBER;

				  							 
BEGIN
	/*--------------- Business Rules Here -------------------*/
	/*-------------------------------------------------------*/
	
	
	-- Get user_id
	SELECT SEQ_USER_ID.NEXTVAL INTO UserId FROM DUAL;
	
	-- Insert new User Account
	INSERT INTO USERS (USER_ID, FULL_NAME, USER_NAME, USER_EMAIL, DOMAIN )
	VALUES( UserId, sFullName, sUserName, sUserEmail, sDomain );
	
	
	EXCEPTION
        WHEN DUP_VAL_ON_INDEX
	THEN		
		RAISE_APPLICATION_ERROR (-20000, 'User Name '|| sUserName ||' already exists.');
	
END     Add_User_Account;

/*--------------------------------------------------------------------------------------------------*/
PROCEDURE Update_User_Account ( sUserId IN VARCHAR2, sFullName IN USERS.FULL_NAME%TYPE,
		  		   	 	   	 sUserName IN USERS.USER_NAME%TYPE,
							 sUserEmail IN USERS.USER_EMAIL%TYPE,
							 sDomain IN USERS.DOMAIN%TYPE   ) IS
                                                         
BEGIN
	/*--------------- Business Rules Here -------------------*/
	IF (sUserId IS NULL) THEN
	   RAISE_APPLICATION_ERROR (-20000, 'Please select a User Account.' );
	END IF;
	/*-------------------------------------------------------*/
	
        -- Update User Account
	UPDATE USERS usr SET
	usr.FULL_NAME = sFullName, usr.USER_NAME = sUserName, usr.USER_EMAIL = sUserEmail, usr.DOMAIN = sDomain
	WHERE usr.USER_ID = sUserId;
        
END     Update_User_Account;

/*--------------------------------------------------------------------------------------------------*/
PROCEDURE Add_Application_User ( sUserIdList IN VARCHAR2,
		  					   	 nAppId IN USER_APPLICATIONS.APP_ID%TYPE,
								 cIncludeEveryone IN CHAR ) IS
						 
				  							 
BEGIN
	/*--------------- Business Rules Here -------------------*/
	IF (sUserIdList IS NULL) AND (cIncludeEveryone IS NULL) THEN
	   RAISE_APPLICATION_ERROR (-20000, 'Please select at least one User.' );
	END IF;
	/*-------------------------------------------------------*/
	
	-- Insert Application User --
	
	IF cIncludeEveryone = 'Y' THEN
	    -- Insert All Users
		INSERT INTO USER_APPLICATIONS ( USER_ID, APP_ID )
		SELECT qry.USER_ID, nAppId
		  FROM (
				SELECT usr.USER_ID
				  FROM USERS usr 
				MINUS
				SELECT ua.USER_ID
				  FROM USER_APPLICATIONS ua
				 WHERE ua.APP_ID = nAppId
		  	   ) qry;
			   
	ELSE
		-- Insert specific user list
		INSERT INTO USER_APPLICATIONS ( USER_ID, APP_ID )
		SELECT qry.USER_ID, nAppId
		  FROM (
		  	   	SELECT usr.USER_ID
				  FROM USERS usr
				 WHERE usr.USER_ID IN (
				 	   			  	  SELECT *
								   	    FROM THE ( SELECT CAST( IN_LIST_NUMBER( sUserIdList ) AS ACCMGR_NUMBER_TAB_t ) FROM dual ) 
				   	   			   	  )
		  	    MINUS
				SELECT ua.USER_ID
				  FROM USER_APPLICATIONS ua
				 WHERE ua.APP_ID = nAppId
				   AND ua.USER_ID IN (
				 	   			  	  SELECT *
								   	    FROM THE ( SELECT CAST( IN_LIST_NUMBER( sUserIdList ) AS ACCMGR_NUMBER_TAB_t ) FROM dual ) 
				   	   			   	  )
		  	   ) qry;
	
	END IF;
	
	
	--- Make sure Build in User is not included ---
	Remove_Application_User ( '0', nAppId );
	
	
END	Add_Application_User;

/*--------------------------------------------------------------------------------------------------*/
PROCEDURE Remove_Application_User ( sUserIdList IN VARCHAR2,
		  					   	  	nAppId IN USER_APPLICATIONS.APP_ID%TYPE,
									cIncludeEveryone IN CHAR DEFAULT NULL) IS
						 
				  							 
BEGIN
	/*--------------- Business Rules Here -------------------*/
	IF (sUserIdList IS NULL) AND (cIncludeEveryone IS NULL) THEN
	   RAISE_APPLICATION_ERROR (-20000, 'Please select at least one User.' );
	END IF;
	/*-------------------------------------------------------*/
	
	IF cIncludeEveryone = 'Y' THEN
	   	-- Remove All Users --
		DELETE
		  FROM user_applications ua
		 WHERE ua.app_id = nAppId;
		 
	ELSE
		-- Remove Application Users --
		DELETE
		  FROM user_applications ua
		 WHERE ua.app_id = nAppId
		   AND ua.user_id IN (
                       SELECT *
                         FROM THE ( SELECT CAST( IN_LIST_NUMBER( sUserIdList ) AS ACCMGR_NUMBER_TAB_t ) FROM dual )
                       );
                -- Remove User(s) Application Roles --
                DELETE
                  FROM user_roles ur
                 WHERE ur.user_id IN (
                       SELECT *
                         FROM THE ( SELECT CAST( IN_LIST_NUMBER( sUserIdList ) AS ACCMGR_NUMBER_TAB_t ) FROM dual )
                       )
                   AND ur.role_id IN (
                       SELECT ro.role_id
                         FROM roles ro
                        WHERE ro.app_id = nAppId
                       );
	END IF;
	
	
END	Remove_Application_User;

/*--------------------------------------------------------------------------------------------------*/
PROCEDURE Add_Role_Member ( sUserIdList IN VARCHAR2,
		  					nRoleId IN USER_ROLES.ROLE_ID%TYPE,
							cIncludeEveryone IN CHAR ) IS
						 
				  							 
BEGIN
	/*--------------- Business Rules Here -------------------*/
	IF (sUserIdList IS NULL) AND (cIncludeEveryone IS NULL) THEN
	   RAISE_APPLICATION_ERROR (-20000, 'Please select at least one User.' );
	END IF;
	/*-------------------------------------------------------*/
	
	-- Insert Role Member --
	
	IF cIncludeEveryone = 'Y' THEN
	    -- Insert All Users
		INSERT INTO USER_ROLES ( USER_ID, ROLE_ID )
		SELECT qry.USER_ID, nRoleId
		  FROM (
				SELECT usr.USER_ID
				  FROM USERS usr 
				MINUS
				SELECT ur.USER_ID
				  FROM USER_ROLES ur
				 WHERE ur.ROLE_ID = nRoleId
		  	   ) qry;
			   
	ELSE
		-- Insert specific user list
		INSERT INTO USER_ROLES ( USER_ID, ROLE_ID )
		SELECT qry.USER_ID, nRoleId
		  FROM (
		  	   	SELECT usr.USER_ID
				  FROM USERS usr
				 WHERE usr.USER_ID IN (
				 	   			  	  SELECT *
								   	    FROM THE ( SELECT CAST( IN_LIST_NUMBER( sUserIdList ) AS ACCMGR_NUMBER_TAB_t ) FROM dual ) 
				   	   			   	  )
		  	    MINUS
				SELECT ur.USER_ID
				  FROM USER_ROLES ur
				 WHERE ur.ROLE_ID = nRoleId
				   AND ur.USER_ID IN (
				 	   			  	  SELECT *
								   	    FROM THE ( SELECT CAST( IN_LIST_NUMBER( sUserIdList ) AS ACCMGR_NUMBER_TAB_t ) FROM dual ) 
				   	   			   	  )
		  	   ) qry;
	
	END IF;
	
	
	--- Make sure Build in User is not included ---
	Remove_Role_Member ( '0', nRoleId );
	
	
END	Add_Role_Member;

/*--------------------------------------------------------------------------------------------------*/
PROCEDURE Remove_Role_Member ( sUserIdList IN VARCHAR2,
		  					   nRoleId IN USER_ROLES.ROLE_ID%TYPE,
							   cIncludeEveryone IN CHAR DEFAULT NULL) IS
						 
				  							 
BEGIN
	/*--------------- Business Rules Here -------------------*/
	IF (nRoleId IS NULL) THEN
		RAISE_APPLICATION_ERROR (-20000, 'RoleId is missing.' );
	END IF;
	
	IF (sUserIdList IS NULL) AND (cIncludeEveryone IS NULL) THEN
	   RAISE_APPLICATION_ERROR (-20000, 'Please select at least one User.' );
	END IF;
	/*-------------------------------------------------------*/
	
	IF cIncludeEveryone = 'Y' THEN
	   	-- Remove All Users --
		DELETE
		  FROM USER_ROLES ur
		 WHERE ur.ROLE_ID = nRoleId;
		 
	ELSE
		-- Remove Application Users --
		DELETE
		  FROM USER_ROLES ur
		 WHERE ur.ROLE_ID = nRoleId
		   AND ur.USER_ID IN (
		 	   			  	  SELECT *
						   	    FROM THE ( SELECT CAST( IN_LIST_NUMBER( sUserIdList ) AS ACCMGR_NUMBER_TAB_t ) FROM dual ) 
		   	   			   	  );		
	END IF;
	
	
END	Remove_Role_Member;

/*--------------------------------------------------------------------------------------------------*/
PROCEDURE Disable_Users ( sUserIdList IN VARCHAR2,
 					   	  nAppId IN USER_APPLICATIONS.APP_ID%TYPE ) IS
						 
				  							 
BEGIN
	/*--------------- Business Rules Here -------------------*/
	IF (sUserIdList IS NULL) THEN
	   RAISE_APPLICATION_ERROR (-20000, 'Please select at least one User.' );
	END IF;
	/*-------------------------------------------------------*/
	
	
	-- Disable User Accounts
	UPDATE USERS usr SET
	usr.IS_DISABLED = 'Y'
	WHERE usr.USER_ID IN (
		 	   			   SELECT *
						     FROM THE ( SELECT CAST( IN_LIST_NUMBER( sUserIdList ) AS ACCMGR_NUMBER_TAB_t ) FROM dual ) 
		   	   			  );	
	
END	Disable_Users;

/*--------------------------------------------------------------------------------------------------*/
PROCEDURE Remove_User_Account ( sUserIdList IN VARCHAR2 ) IS
						 
rowCount NUMBER DEFAULT 0;
			  							 
BEGIN
	/*--------------- Business Rules Here -------------------*/
	
	IF (sUserIdList IS NULL) THEN
	   RAISE_APPLICATION_ERROR (-20000, 'Please select at least one User Account.' );
	END IF;
	
	
	-- Check if any Users have any roles
	SELECT Count(*) INTO rowCount
	  FROM USER_ROLES ur
	 WHERE ur.USER_ID IN  (
						   SELECT *
					   	     FROM THE ( SELECT CAST( IN_LIST_NUMBER( sUserIdList ) AS ACCMGR_NUMBER_TAB_t ) FROM dual ) 
	   	   			   	   );
	 
	IF rowCount > 0 THEN
	   RAISE_APPLICATION_ERROR (-20000, 'Some Users still have Roles assigned. ( Counted '|| rowCount ||' ).' );
	END IF; 
	
	/*-------------------------------------------------------*/
	
	-- Remove User Applications --
	DELETE 
	  FROM USER_APPLICATIONS ua
	 WHERE ua.USER_ID IN  (
						   SELECT *
					   	     FROM THE ( SELECT CAST( IN_LIST_NUMBER( sUserIdList ) AS ACCMGR_NUMBER_TAB_t ) FROM dual ) 
	   	   			   	   );
	
	-- Remove User -- 
	DELETE 
	  FROM USERS us
	 WHERE us.USER_ID IN  (
						   SELECT *
					   	     FROM THE ( SELECT CAST( IN_LIST_NUMBER( sUserIdList ) AS ACCMGR_NUMBER_TAB_t ) FROM dual ) 
	   	   			   	   );
	
	
END	Remove_User_Account;
/*--------------------------------------------------------------------------------------------------*/
END pk_user;

/

/
