Subversion Repositories DevTools

CREATE TABLE CONTROL_OBJECTS
(
  OBJ_ID NUMBER NOT NULL,
  APP_ID NUMBER NOT NULL,
  OBJ_NAME VARCHAR2(1000 BYTE) NOT NULL,
  PARENT_OBJ_ID NUMBER,
  OBJ_DESCRIPTION VARCHAR2(1000 BYTE)
, CONSTRAINT PK_CONTROL_OBJECTS PRIMARY KEY
  (
    OBJ_ID
  )
  ENABLE
)
  TABLESPACE "USERS"
  LOGGING 
  PCTFREE 10
  PCTUSED 40
  INITRANS 1
  MAXTRANS 255
  STORAGE
  (
    INITIAL 128K
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    FREELISTS 1
    FREELIST GROUPS 1
    BUFFER_POOL DEFAULT
  )
;

CREATE TABLE PERMISSION_TYPES
(
  PERM_ID NUMBER NOT NULL,
  PERM_NAME VARCHAR2(255 BYTE) NOT NULL
, CONSTRAINT PK_PERMISSION_TYPES PRIMARY KEY
  (
    PERM_ID
  )
  ENABLE
)
  TABLESPACE "USERS"
  LOGGING 
  PCTFREE 10
  PCTUSED 40
  INITRANS 1
  MAXTRANS 255
  STORAGE
  (
    INITIAL 128K
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    FREELISTS 1
    FREELIST GROUPS 1
    BUFFER_POOL DEFAULT
  )
;

CREATE TABLE APPLICATIONS
(
  APP_ID NUMBER NOT NULL,
  APPLICATION_NAME VARCHAR2(1000 BYTE) NOT NULL,
  DB_SCHEMA VARCHAR2(1000 BYTE),
  TNS_NAME VARCHAR2(1000 BYTE),
  SCHEMA_USERNAME VARCHAR2(255 BYTE),
  SCHEMA_PASSWORD VARCHAR2(255 BYTE),
  ACRONYM VARCHAR2(3 BYTE) NOT NULL,
  IS_RUNNING CHAR(1 BYTE) NOT NULL
, CONSTRAINT PK_APPLICATIONS PRIMARY KEY
  (
    APP_ID
  )
  ENABLE
)
  TABLESPACE "USERS"
  LOGGING 
  PCTFREE 10
  PCTUSED 40
  INITRANS 1
  MAXTRANS 255
  STORAGE
  (
    INITIAL 128K
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    FREELISTS 1
    FREELIST GROUPS 1
    BUFFER_POOL DEFAULT
  )
;

CREATE TABLE USERS
(
  USER_ID NUMBER NOT NULL,
  FULL_NAME VARCHAR2(255 BYTE) NOT NULL,
  USER_NAME VARCHAR2(1000 BYTE) NOT NULL,
  USER_PASSWORD VARCHAR2(4000 BYTE),
  USER_EMAIL VARCHAR2(1000 BYTE),
  DOMAIN VARCHAR2(1000 BYTE),
  IS_DISABLED CHAR(1 BYTE),
  IS_ONLINE CHAR(1 BYTE),
  ONLINE_AT VARCHAR2(50 BYTE),
  LAST_VISIT DATE,
  LAST_REQUEST NUMBER
, CONSTRAINT PK_USERS PRIMARY KEY
  (
    USER_ID
  )
  ENABLE
)
  TABLESPACE "USERS"
  LOGGING 
  PCTFREE 10
  PCTUSED 40
  INITRANS 1
  MAXTRANS 255
  STORAGE
  (
    INITIAL 128K
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    FREELISTS 1
    FREELIST GROUPS 1
    BUFFER_POOL DEFAULT
  )
;

CREATE TABLE LOGIN_TRAIL
(
  ENUM_EVENT NUMBER NOT NULL,
  USER_NAME VARCHAR2(1000 BYTE) NOT NULL,
  CLIENT_IP VARCHAR2(255 BYTE),
  APP_ID NUMBER,
  STAMP DATE NOT NULL,
  COMMENTS VARCHAR2(4000 BYTE)
)
  TABLESPACE "USERS"
  LOGGING 
  PCTFREE 10
  PCTUSED 40
  INITRANS 1
  MAXTRANS 255
  STORAGE
  (
    INITIAL 128K
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    FREELISTS 1
    FREELIST GROUPS 1
    BUFFER_POOL DEFAULT
  )
;

CREATE TABLE DATA_TABLES
(
  DT_ID NUMBER NOT NULL,
  OBJ_ID NUMBER NOT NULL,
  TABLE_NAME VARCHAR2(1000 BYTE) NOT NULL,
  REF_COLUMN_NAME VARCHAR2(1000 BYTE) NOT NULL,
  DISPLAY_COLUMN_NAME VARCHAR2(1000 BYTE) NOT NULL
, CONSTRAINT PK_DATA_TABLES PRIMARY KEY
  (
    DT_ID
  )
  ENABLE
)
  TABLESPACE "USERS"
  LOGGING 
  PCTFREE 10
  PCTUSED 40
  INITRANS 1
  MAXTRANS 255
  STORAGE
  (
    INITIAL 128K
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    FREELISTS 1
    FREELIST GROUPS 1
    BUFFER_POOL DEFAULT
  )
;

CREATE TABLE ROLE_PRIVILEGES
(
  ROLE_ID NUMBER NOT NULL,
  OBJ_ID NUMBER NOT NULL,
  PERM_ID NUMBER NOT NULL,
  PERM_VALUE CHAR(1 BYTE) NOT NULL
)
  TABLESPACE "USERS"
  LOGGING 
  PCTFREE 10
  PCTUSED 40
  INITRANS 1
  MAXTRANS 255
  STORAGE
  (
    INITIAL 128K
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    FREELISTS 1
    FREELIST GROUPS 1
    BUFFER_POOL DEFAULT
  )
;

CREATE TABLE DATA_PERMISSIONS
(
  DT_ID NUMBER NOT NULL,
  ROLE_ID NUMBER NOT NULL,
  REF_COLUMN_VAL NUMBER NOT NULL,
  PERM_ID NUMBER NOT NULL,
  PERM_VALUE CHAR(1 BYTE) NOT NULL
)
  TABLESPACE "USERS"
  LOGGING 
  PCTFREE 10
  PCTUSED 40
  INITRANS 1
  MAXTRANS 255
  STORAGE
  (
    INITIAL 128K
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    FREELISTS 1
    FREELIST GROUPS 1
    BUFFER_POOL DEFAULT
  )
;

CREATE TABLE USER_ROLES
(
  USER_ID NUMBER NOT NULL,
  ROLE_ID NUMBER NOT NULL
)
  TABLESPACE "USERS"
  LOGGING 
  PCTFREE 10
  PCTUSED 40
  INITRANS 1
  MAXTRANS 255
  STORAGE
  (
    INITIAL 128K
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    FREELISTS 1
    FREELIST GROUPS 1
    BUFFER_POOL DEFAULT
  )
;

CREATE TABLE ROLES
(
  ROLE_ID NUMBER NOT NULL,
  APP_ID NUMBER NOT NULL,
  ROLE_NAME VARCHAR2(1000 BYTE) NOT NULL,
  IS_ROLE_VARIATION CHAR(1 BYTE),
  COMMENTS VARCHAR2(4000 BYTE)
, CONSTRAINT PK_ROLES PRIMARY KEY
  (
    ROLE_ID
  )
  ENABLE
)
  TABLESPACE "USERS"
  LOGGING 
  PCTFREE 10
  PCTUSED 40
  INITRANS 1
  MAXTRANS 255
  STORAGE
  (
    INITIAL 128K
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    FREELISTS 1
    FREELIST GROUPS 1
    BUFFER_POOL DEFAULT
  )
;

CREATE TABLE USER_APPLICATIONS
(
  USER_ID NUMBER NOT NULL,
  APP_ID NUMBER NOT NULL,
  LAST_VISIT_STAMP DATE
)
  TABLESPACE "USERS"
  LOGGING 
  PCTFREE 10
  PCTUSED 40
  INITRANS 1
  MAXTRANS 255
  STORAGE
  (
    INITIAL 128K
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    FREELISTS 1
    FREELIST GROUPS 1
    BUFFER_POOL DEFAULT
  )
;

CREATE TABLE PAGE_CONTROL_OBJECTS
(
  PAGE_ID NUMBER NOT NULL,
  OBJ_ID NUMBER NOT NULL
)
  TABLESPACE "USERS"
  LOGGING 
  PCTFREE 10
  PCTUSED 40
  INITRANS 1
  MAXTRANS 255
  STORAGE
  (
    INITIAL 128K
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    FREELISTS 1
    FREELIST GROUPS 1
    BUFFER_POOL DEFAULT
  )
;

CREATE TABLE DEF_ACTION_BUTTONS
(
  ABTN_ID NUMBER NOT NULL,
  ABTN_NAME VARCHAR2(1000 BYTE) NOT NULL,
  TEXT VARCHAR2(50 BYTE),
  ACTION_LINK VARCHAR2(4000 BYTE),
  EVENT_HANDLER VARCHAR2(4000 BYTE),
  IMG_ENABLED VARCHAR2(1000 BYTE),
  IMG_DISABLED VARCHAR2(1000 BYTE),
  HINT VARCHAR2(50 BYTE),
  VISIBLE CHAR(1 BYTE) NOT NULL,
  ACTIVE CHAR(1 BYTE) NOT NULL,
  IS_READONLY_ACTION CHAR(1 BYTE) NOT NULL
, CONSTRAINT PK_DEF_ACTION_BUTTONS PRIMARY KEY
  (
    ABTN_ID
  )
  ENABLE
)
  TABLESPACE "USERS"
  LOGGING 
  PCTFREE 10
  PCTUSED 40
  INITRANS 1
  MAXTRANS 255
  STORAGE
  (
    INITIAL 128K
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    FREELISTS 1
    FREELIST GROUPS 1
    BUFFER_POOL DEFAULT
  )
;

CREATE TABLE APPLICATION_PAGES
(
  PAGE_ID NUMBER NOT NULL,
  APP_ID NUMBER NOT NULL,
  PAGE_NAME VARCHAR2(1000 BYTE) NOT NULL
, CONSTRAINT PK_APPLICATION_PAGES PRIMARY KEY
  (
    PAGE_ID
  )
  ENABLE
)
  TABLESPACE "USERS"
  LOGGING 
  PCTFREE 10
  PCTUSED 40
  INITRANS 1
  MAXTRANS 255
  STORAGE
  (
    INITIAL 128K
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    FREELISTS 1
    FREELIST GROUPS 1
    BUFFER_POOL DEFAULT
  )
;

CREATE TABLE VALIDATION_RULES
(
  FIELD_NAME VARCHAR2(1000 BYTE) NOT NULL,
  IS_REQUIRED CHAR(1 BYTE) NOT NULL,
  IS_NUMERIC CHAR(1 BYTE),
  MIN_NUMERIC_VALUE NUMBER,
  MAX_NUMERIC_VALUE NUMBER,
  IS_DATE CHAR(1 BYTE),
  START_DATE DATE,
  END_DATE DATE,
  MIN_STRING_LENGTH NUMBER,
  MAX_STRING_LENGTH NUMBER,
  REGEXP VARCHAR2(4000 BYTE),
  REGEXP_DESCRIPTION VARCHAR2(50 BYTE)
)
  TABLESPACE "USERS"
  LOGGING 
  PCTFREE 10
  PCTUSED 40
  INITRANS 1
  MAXTRANS 255
  STORAGE
  (
    INITIAL 128K
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    FREELISTS 1
    FREELIST GROUPS 1
    BUFFER_POOL DEFAULT
  )
;

ALTER TABLE CONTROL_OBJECTS
ADD CONSTRAINT FK_CONTROL_OBJ_REF_APPLICS FOREIGN KEY
(
  APP_ID
)
REFERENCES APPLICATIONS
(
APP_ID
) ENABLE
;

ALTER TABLE DATA_TABLES
ADD CONSTRAINT FK_DATA_TABLE_REF_OBJ FOREIGN KEY
(
  OBJ_ID
)
REFERENCES CONTROL_OBJECTS
(
OBJ_ID
) ENABLE
;

ALTER TABLE ROLE_PRIVILEGES
ADD CONSTRAINT FK_ROLE_OBJ_REF_ROLES FOREIGN KEY
(
  ROLE_ID
)
REFERENCES ROLES
(
ROLE_ID
) ENABLE
;

ALTER TABLE ROLE_PRIVILEGES
ADD CONSTRAINT FK_ROLE_OBJ_REF_OBJS FOREIGN KEY
(
  OBJ_ID
)
REFERENCES CONTROL_OBJECTS
(
OBJ_ID
) ENABLE
;

ALTER TABLE ROLE_PRIVILEGES
ADD CONSTRAINT FK_ROLE_OBJ_REF_PERMISS FOREIGN KEY
(
  PERM_ID
)
REFERENCES PERMISSION_TYPES
(
PERM_ID
) ENABLE
;

ALTER TABLE DATA_PERMISSIONS
ADD CONSTRAINT FK_DATA_PER_REF_ROLES FOREIGN KEY
(
  ROLE_ID
)
REFERENCES ROLES
(
ROLE_ID
) ENABLE
;

ALTER TABLE DATA_PERMISSIONS
ADD CONSTRAINT FK_DATA_PERM_REF_DATA_TABLE FOREIGN KEY
(
  DT_ID
)
REFERENCES DATA_TABLES
(
DT_ID
) ENABLE
;

ALTER TABLE DATA_PERMISSIONS
ADD CONSTRAINT FK_DATA_PERM_REF_PERM_TYPES FOREIGN KEY
(
  PERM_ID
)
REFERENCES PERMISSION_TYPES
(
PERM_ID
) ENABLE
;

ALTER TABLE USER_ROLES
ADD CONSTRAINT FK_USER_ROLES_REF_ROLES FOREIGN KEY
(
  ROLE_ID
)
REFERENCES ROLES
(
ROLE_ID
) ENABLE
;

ALTER TABLE USER_ROLES
ADD CONSTRAINT FK_USER_ROLES_REF_USERS FOREIGN KEY
(
  USER_ID
)
REFERENCES USERS
(
USER_ID
) ENABLE
;

ALTER TABLE ROLES
ADD CONSTRAINT FK_ROLES_REF_APPLICS FOREIGN KEY
(
  APP_ID
)
REFERENCES APPLICATIONS
(
APP_ID
) ENABLE
;

ALTER TABLE USER_APPLICATIONS
ADD CONSTRAINT FK_USER_APP_REF_USERS FOREIGN KEY
(
  USER_ID
)
REFERENCES USERS
(
USER_ID
) ENABLE
;

ALTER TABLE USER_APPLICATIONS
ADD CONSTRAINT FK_USER_APPS_REF_APPS FOREIGN KEY
(
  APP_ID
)
REFERENCES APPLICATIONS
(
APP_ID
) ENABLE
;

ALTER TABLE PAGE_CONTROL_OBJECTS
ADD CONSTRAINT FK_PAGE_CON_REF_APP_PAGES FOREIGN KEY
(
  PAGE_ID
)
REFERENCES APPLICATION_PAGES
(
PAGE_ID
) ENABLE
;

ALTER TABLE PAGE_CONTROL_OBJECTS
ADD CONSTRAINT FK_PAGE_CON_REF_CONTROL_OBJS FOREIGN KEY
(
  OBJ_ID
)
REFERENCES CONTROL_OBJECTS
(
OBJ_ID
) ENABLE
;

ALTER TABLE APPLICATION_PAGES
ADD CONSTRAINT FK_APP_PAGE_REF_APP FOREIGN KEY
(
  APP_ID
)
REFERENCES APPLICATIONS
(
APP_ID
) ENABLE
;

CREATE OR REPLACE VIEW CONTROLOBJECTS_VS_ROLES AS select distinct app.app_id, app.application_name, co.obj_id, co.obj_name, co.obj_description, ro.role_id, ro.role_name
from applications app, control_objects co, roles ro, role_privileges rp
where app.app_id=co.app_id
and app.app_id=ro.app_id
and ro.role_id=rp.role_id
and rp.obj_id=co.obj_id
order by co.obj_name;

CREATE UNIQUE INDEX UNQ_CONTROL_OBJ ON CONTROL_OBJECTS (APP_ID ASC, OBJ_NAME ASC)  TABLESPACE "USERS"
  LOGGING 
  PCTFREE 10
  INITRANS 2
  MAXTRANS 255
  STORAGE
  (
    INITIAL 128K
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    FREELISTS 1
    FREELIST GROUPS 1
    BUFFER_POOL DEFAULT
  )
;

CREATE UNIQUE INDEX UNQ_PERMISSION_TYPES ON PERMISSION_TYPES (PERM_NAME ASC)  TABLESPACE "USERS"
  LOGGING 
  PCTFREE 10
  INITRANS 2
  MAXTRANS 255
  STORAGE
  (
    INITIAL 128K
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    FREELISTS 1
    FREELIST GROUPS 1
    BUFFER_POOL DEFAULT
  )
;

CREATE UNIQUE INDEX UNQ_APPLICATIONS ON APPLICATIONS (APPLICATION_NAME ASC)  TABLESPACE "USERS"
  LOGGING 
  PCTFREE 10
  INITRANS 2
  MAXTRANS 255
  STORAGE
  (
    INITIAL 128K
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    FREELISTS 1
    FREELIST GROUPS 1
    BUFFER_POOL DEFAULT
  )
;

CREATE UNIQUE INDEX UNQ_USERS ON USERS (USER_NAME ASC)  TABLESPACE "USERS"
  LOGGING 
  PCTFREE 10
  INITRANS 2
  MAXTRANS 255
  STORAGE
  (
    INITIAL 128K
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    FREELISTS 1
    FREELIST GROUPS 1
    BUFFER_POOL DEFAULT
  )
;

CREATE INDEX INX_CLIENT_IP ON LOGIN_TRAIL (CLIENT_IP ASC)  TABLESPACE "USERS"
  LOGGING 
  PCTFREE 10
  INITRANS 2
  MAXTRANS 255
  STORAGE
  (
    INITIAL 128K
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    FREELISTS 1
    FREELIST GROUPS 1
    BUFFER_POOL DEFAULT
  )
;

CREATE INDEX INX_ENUM_EVENT ON LOGIN_TRAIL (ENUM_EVENT ASC)  TABLESPACE "USERS"
  LOGGING 
  PCTFREE 10
  INITRANS 2
  MAXTRANS 255
  STORAGE
  (
    INITIAL 128K
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    FREELISTS 1
    FREELIST GROUPS 1
    BUFFER_POOL DEFAULT
  )
;

CREATE INDEX INX_USER_NAME ON LOGIN_TRAIL (USER_NAME ASC)  TABLESPACE "USERS"
  LOGGING 
  PCTFREE 10
  INITRANS 2
  MAXTRANS 255
  STORAGE
  (
    INITIAL 128K
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    FREELISTS 1
    FREELIST GROUPS 1
    BUFFER_POOL DEFAULT
  )
;

CREATE UNIQUE INDEX UNQ_DATA_TABLE ON DATA_TABLES (TABLE_NAME ASC, OBJ_ID ASC, REF_COLUMN_NAME ASC)  TABLESPACE "USERS"
  LOGGING 
  PCTFREE 10
  INITRANS 2
  MAXTRANS 255
  STORAGE
  (
    INITIAL 128K
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    FREELISTS 1
    FREELIST GROUPS 1
    BUFFER_POOL DEFAULT
  )
;

CREATE UNIQUE INDEX UNQ_ROLE_OBJ_PERM ON ROLE_PRIVILEGES (ROLE_ID ASC, OBJ_ID ASC, PERM_ID ASC)  TABLESPACE "USERS"
  LOGGING 
  PCTFREE 10
  INITRANS 2
  MAXTRANS 255
  STORAGE
  (
    INITIAL 128K
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    FREELISTS 1
    FREELIST GROUPS 1
    BUFFER_POOL DEFAULT
  )
;

CREATE UNIQUE INDEX UNQ_DATA_PERM ON DATA_PERMISSIONS (DT_ID ASC, ROLE_ID ASC, REF_COLUMN_VAL ASC, PERM_ID ASC)  TABLESPACE "USERS"
  LOGGING 
  PCTFREE 10
  INITRANS 2
  MAXTRANS 255
  STORAGE
  (
    INITIAL 128K
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    FREELISTS 1
    FREELIST GROUPS 1
    BUFFER_POOL DEFAULT
  )
;

CREATE UNIQUE INDEX UNQ_USER_ROLES ON USER_ROLES (USER_ID ASC, ROLE_ID ASC)  TABLESPACE "USERS"
  LOGGING 
  PCTFREE 10
  INITRANS 2
  MAXTRANS 255
  STORAGE
  (
    INITIAL 128K
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    FREELISTS 1
    FREELIST GROUPS 1
    BUFFER_POOL DEFAULT
  )
;

CREATE UNIQUE INDEX UNQ_ROLES ON ROLES (APP_ID ASC, ROLE_NAME ASC)  TABLESPACE "USERS"
  LOGGING 
  PCTFREE 10
  INITRANS 2
  MAXTRANS 255
  STORAGE
  (
    INITIAL 128K
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    FREELISTS 1
    FREELIST GROUPS 1
    BUFFER_POOL DEFAULT
  )
;

CREATE UNIQUE INDEX UNQ_USER_APPS ON USER_APPLICATIONS (USER_ID ASC, APP_ID ASC)  TABLESPACE "USERS"
  LOGGING 
  PCTFREE 10
  INITRANS 2
  MAXTRANS 255
  STORAGE
  (
    INITIAL 128K
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    FREELISTS 1
    FREELIST GROUPS 1
    BUFFER_POOL DEFAULT
  )
;

CREATE UNIQUE INDEX UNQ_PAGE_COBJ ON PAGE_CONTROL_OBJECTS (PAGE_ID ASC, OBJ_ID ASC)  TABLESPACE "USERS"
  LOGGING 
  PCTFREE 10
  INITRANS 2
  MAXTRANS 255
  STORAGE
  (
    INITIAL 128K
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    FREELISTS 1
    FREELIST GROUPS 1
    BUFFER_POOL DEFAULT
  )
;

CREATE UNIQUE INDEX UNQ_ABTN_DEF ON DEF_ACTION_BUTTONS (ABTN_NAME ASC)  TABLESPACE "USERS"
  LOGGING 
  PCTFREE 10
  INITRANS 2
  MAXTRANS 255
  STORAGE
  (
    INITIAL 128K
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    FREELISTS 1
    FREELIST GROUPS 1
    BUFFER_POOL DEFAULT
  )
;

CREATE UNIQUE INDEX UNQ_APP_PAGES ON APPLICATION_PAGES (APP_ID ASC, PAGE_NAME ASC)  TABLESPACE "USERS"
  LOGGING 
  PCTFREE 10
  INITRANS 2
  MAXTRANS 255
  STORAGE
  (
    INITIAL 128K
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    FREELISTS 1
    FREELIST GROUPS 1
    BUFFER_POOL DEFAULT
  )
;

CREATE UNIQUE INDEX UNQ_VALIDATION_RULES ON VALIDATION_RULES (FIELD_NAME ASC)  TABLESPACE "USERS"
  LOGGING 
  PCTFREE 10
  INITRANS 2
  MAXTRANS 255
  STORAGE
  (
    INITIAL 128K
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    FREELISTS 1
    FREELIST GROUPS 1
    BUFFER_POOL DEFAULT
  )
;

CREATE SEQUENCE SEQ_APP_ID INCREMENT BY 1 MAXVALUE 999999999999999999999999999 MINVALUE 1 CACHE 20 ORDER ;

CREATE SEQUENCE SEQ_ROLE_ID INCREMENT BY 1 MAXVALUE 999999999999999999999999999 MINVALUE 1 CACHE 20 ORDER ;

CREATE SEQUENCE SEQ_USER_ID INCREMENT BY 1 MAXVALUE 999999999999999999999999999 MINVALUE 1 CACHE 20 ORDER ;

CREATE SEQUENCE SEQ_DT_ID INCREMENT BY 1 MAXVALUE 999999999999999999999999999 MINVALUE 1 CACHE 20 ORDER ;

CREATE SEQUENCE SEQ_OBJ_ID INCREMENT BY 1 MAXVALUE 999999999999999999999999999 MINVALUE 1 CACHE 20 ORDER ;

CREATE OR REPLACE SYNONYM RM_PROJECTS FOR RELEASE_MANAGER.PROJECTS;

CREATE OR REPLACE SYNONYM DM_DEF_MENU_ITEMS FOR DEF_MENU_ITEMS;

CREATE OR REPLACE SYNONYM DM_DM_PROJECTS FOR DM_PROJECTS;

CREATE OR REPLACE SYNONYM PM_DM_PROJECTS FOR DEPLOYMENT_MANAGER.DM_PROJECTS;

CREATE OR REPLACE SYNONYM PROJECTS FOR RELEASE_MANAGER.PROJECTS;

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;
/

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;
/

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;
/

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;
/

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;
/

CREATE OR REPLACE TYPE "ACCMGR_NUMBER_TAB_T"                                                                          as TABLE of NUMBER
/

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;
/

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;
/

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;
/

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;
/

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;
/

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;
/

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

/*--------------------------------------------------------------------------------------------------*/
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
                                                 
                                                                                         
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 ) 
                                                  );
          
        
END     Revoke_Role;
/*--------------------------------------------------------------------------------------------------*/


END pk_Role;
/

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;
/

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;
/