/****************************************************************************/
/* FILENAME:      INSTALL_DIRECT_2.sql                                      */
/* VERSION:       1.6                                                       */
/* DATABASE:      Oracle 8i,9i 	                                        */
/* SUMMARY:       Creates database objects for direct synchronization.      */
/* INSTRUCTIONS:  Execute as user 'idm' after INSTALL_DIRECT_1.sql.      */
/* DISPLAY:       monospaced                                                */
/****************************************************************************/


SET TERMOUT ON;
SET ECHO OFF;

/****************************************************************************/
/* Create views for customer tables.                                                  */
/****************************************************************************/



CREATE OR REPLACE VIEW idm.view_ORCLUSERS (pk_USERNAME, USER_ID, ACCOUNT_STATUS, LOCK_DATE, EXPIRY_DATE, DEFAULT_TABLESPACE, TEMPORARY_TABLESPACE, CREATED, PROFILE, INITIAL_RSRC_CONSUMER_GROUP, EXTERNAL_NAME)
AS
/* VIEW 'DBA_USERS' would need to be schema prefixed if it were located outside */
/* of the driver's schema. */
SELECT USERNAME, USER_ID, ACCOUNT_STATUS, LOCK_DATE, EXPIRY_DATE, DEFAULT_TABLESPACE, TEMPORARY_TABLESPACE, CREATED, PROFILE, INITIAL_RSRC_CONSUMER_GROUP, EXTERNAL_NAME FROM SYS.DBA_USERS;

CREATE OR REPLACE VIEW idm.view_ORCLROLES (pk_ROLE, PASSWORD_REQUIRED)
AS
/* VIEW 'DBA_ROLES' would need to be schema prefixed if it were located outside */
/* of the driver's schema. */
SELECT ROLE, PASSWORD_REQUIRED FROM SYS.DBA_ROLES;

CREATE OR REPLACE VIEW idm.view_ORCLPRIVS (pk_GRANTEE, pk_GRANTED_ROLE, ADMIN_OPTION, DEFAULT_ROLE)
AS
/* VIEW 'DBA_ROLE_PRIVS' would need to be schema prefixed if it were located outside */
/* of the driver's schema. */
SELECT GRANTEE, GRANTED_ROLE, ADMIN_OPTION, DEFAULT_ROLE FROM SYS.DBA_ROLE_PRIVS;


/****************************************************************************/
/* Create event log table.                                                  */
/****************************************************************************/

CREATE TABLE IDM.eventlog
(
	record_id		NUMBER(32) 		NOT NULL
							UNIQUE,
	table_key		VARCHAR2(96)	NOT NULL,		
	status		CHAR(1)		DEFAULT 'N',
	event_type		NUMBER(1) 		NOT NULL, 
	event_time		DATE			DEFAULT SYSDATE 
							NOT NULL, 			
	perpetrator		VARCHAR2(32)	DEFAULT USER,
	table_name		VARCHAR2(32) 	NOT NULL,
	column_name		VARCHAR2(32),
	old_value		VARCHAR2(64),
	new_value		VARCHAR2(64),
	CONSTRAINT chk_eventlog_event_type CHECK(event_type IN (1, 2, 3, 4, 5, 6, 7, 8))
);	
CREATE UNIQUE INDEX i_eventlog_1 ON idm.eventlog(table_key, record_id, status);
CREATE UNIQUE INDEX i_eventlog_2 ON idm.eventlog(record_id, table_key);


CREATE SEQUENCE idm.seq_recid
	START WITH 1
	INCREMENT BY 1
	NOMINVALUE
	NOMAXVALUE
	CACHE 100
	ORDER;




COMMIT;