Rem drv: <create type="types" params="EM_REPOS_USER" pos="events/events_types.sql+"/>
Rem
Rem $Header: emcore/source/oracle/sysman/emdrep/sql/core/latest/notification/notification_types.sql rbali_oneoff_updated/1 2025/03/14 06:41:14 rbali Exp $
Rem
Rem notification_types.sql
Rem
Rem Copyright (c) 2002, 2025, Oracle and/or its affiliates. 
Rem All rights reserved.

Rem BEGIN EMRU_FILE_METADATA
Rem EMRU_SOURCE_FILE:emcore/source/oracle/sysman/emdrep/sql/core/latest/notification/notification_types.sql
Rem EMRU_UNIX_DELIVERABLES:
Rem emcore/source/oracle/sysman/emdrep/sql/core/latest/notification/notification_types.sql

Rem EMRU_FILE_POSTINSTALL:<!!! enter the view based location of the executable file. If there is NONE leave as-is !!!>
Rem EMRU_GENERIC_FLAG:NONE
Rem END EMRU_FILE_METADATA

Rem
Rem    NAME
Rem      notification_types.sql - <one-line expansion of the name>
Rem
Rem    DESCRIPTION
Rem      <short description of component this file declares/defines>
Rem
Rem    NOTES
Rem      <other useful comments, qualifications, etc.>
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    gbafna      02/03/25 - 
Rem    gbafna      01/07/25 - make synonyms noneditionable
Rem    rnadidar    02/11/15 - cut over to EBR
Rem    jianpeng    08/06/12 - delete MGMT_NOTIFY_EMAIL_CUST_OBJ
Rem    bnam        12/24/09 - add MGMT_NOTIF_SUBSCRIBER_ARRAY/MGMT_NOTIF_
Rem                           ADV_DEVICE_ARRAY  
Rem    bnam        12/15/09 - dropped abbreviated and timestamp_format from
Rem                           MGMT_NOTIFY_PARAMETER_FORMATS
Rem    sktedla     09/19/09 - Bug#8886999 - Increased the 'value' in 
Rem                           'MGMT_NOTIFY_COLUMN' from 256 to 4000
Rem    bnam        11/20/08 - changed CLOB type in email cust. to NCLOB for unicode
Rem    xuli        11/11/08 - add last updated by and date
Rem    xuli        08/30/08 - 
Rem    zeding      08/25/08 - 
Rem    mgoswami    08/12/08 - 
Rem    xuli        08/27/08 - 
Rem    bnam        08/07/08 - email customization
Rem    rsidlagh    08/04/08 - Added support for UDTP, ER# 4416955
Rem    rsidlagh    07/17/08 - Added support for repeat_notif for non-email
Rem                           devices
Rem    bnam        06/10/08 - add MGMT_NOTIFY_NVPAIR type
Rem    minfan      05/13/08 - bug 6801999
Rem    neearora    05/03/07 - added repeat_count in mgmt_notify_notificaion
Rem    dgiaimo     04/30/07 - Changing 10.2.4.0 to 10.2.0.4
Rem    smalathe    04/23/07 - Bug 5881705: Change MGMT_NOTIFY_JOB_SC.JOB_TYPE
Rem                           and MGMT_NOTIFY_JOB.JOB_TYPE type
Rem    neearora    03/27/07 - call notification_types of 10.2.4.0
Rem    dcawley     08/05/05 - Add labels 
Rem    dcawley     08/04/05 - Add occurrence timestamp for jobs 
Rem    gsbhatia    06/26/05 - New repmgr header impl 
Rem    gsbhatia    02/13/05 - updating repmgr header 
Rem    gsbhatia    02/09/05 - updating repmgr header 
Rem    gsbhatia    02/07/05 - updating repmgr header 
Rem    dcawley     02/02/05 - Increase sizes for ca 
Rem    ktlaw       01/11/05 - add repmgr header 
Rem    dcawley     12/02/04 - Keep keys the same 
Rem    dcawley     11/23/04 - Add type for composite keys 
Rem    rzazueta    11/01/04 - Add EM_REPOS_USER parameter 
Rem    dcawley     09/28/04 - Fix step id 
Rem    dcawley     09/20/04 - Remove output from job object 
Rem    dcawley     09/02/04 - Types for Jobs, CAs 
Rem    dcawley     07/15/04 - Add corrective actions 
Rem    dcawley     07/09/04 - Add object for job state changes 
Rem    dcawley     07/07/04 - Increase user name size 
Rem    dcawley     06/14/04 - Type changes for 10gR2 
Rem    dcawley     09/25/03 - Add key value name 
Rem    dcawley     09/18/03 - Add key value guid 
Rem    dcawley     09/08/03 - use timezone_region 
Rem    dcawley     07/14/03 - Change size of notification object
Rem    dcawley     05/21/03 - Change MGMT_NOTIFY_NOTIFICATION
Rem    dcawley     02/19/03 - Add timezone to MGMT_NOTIFY_SEVERITY
Rem    skini       07/12/02 - Change in target_name column size
Rem    dcawley     06/10/02 - Add types for parameter formating.
Rem    dcawley     05/24/02 - Add rule_name and rule_owner to message queue object.
Rem    dcawley     05/24/02 - Increase size of SMP_EMD_MAIL to handle 4000 byte severity messages.
Rem    dcawley     05/20/02 - Add snmp_community to MGMT_NOTIFY_NOTIFICATION.
Rem    dcawley     05/20/02 - Add SMP_EMD_GATEWAYS_DATA.
Rem    rpinnama    05/15/02 - rpinnama_reorg_rep_scripts
Rem    rpinnama    05/15/02 - Created
Rem

Rem 
Rem This script requires the following arguments
Rem  1. Name of the repository user
Rem 
DEFINE EM_REPOS_USER           = "&1"

ALTER SESSION SET CURRENT_SCHEMA=SYSMAN_TYPES;

-- Create the message type for the Advanced Queue
CREATE OR REPLACE TYPE MGMT_NOTIFY_NOTIFICATION IS OBJECT
(
  notification_type NUMBER(2),-- NORMAL, RETRY, REPEAT, ESCALATED  
  is_ping NUMBER(1), -- flag to say if this is this checking the device status
  source_type NUMBER(2), -- SEVERITY, JOB, POLICY
  source_guid RAW(16), -- TARGET_GUID, JOB_GUID, POLICY_GUID
  device_name VARCHAR2(132), -- the notification method
  device_owner VARCHAR2(256), -- the notification method owner
  device_type NUMBER(2), -- the type of the device
  rule_name VARCHAR2(64), -- the rule causing this notification
  rule_owner VARCHAR2(256), -- the owner of the rule
  qname VARCHAR2(30), -- the name of the queue this was placed in
  repeat_count NUMBER -- in casse of reate notificaion, the number of repeat
);
/

grant execute on MGMT_NOTIFY_NOTIFICATION to sysman;

BEGIN
    SYSMAN.CUTOVER_SYNONYM('MGMT_NOTIFY_NOTIFICATION');
END;
/

ALTER SESSION SET CURRENT_SCHEMA=SYSMAN;


BEGIN
  DBMS_AQADM.CREATE_QUEUE_TABLE (queue_table => '&EM_REPOS_USER'||'.MGMT_NOTIFY_QTABLE',
                              queue_payload_type => '&EM_REPOS_USER'||'_TYPES.MGMT_NOTIFY_NOTIFICATION',
                              multiple_consumers => TRUE);
  DBMS_AQADM.CREATE_QUEUE (queue_name => '&EM_REPOS_USER'||'.MGMT_NOTIFY_Q',
                           queue_table => '&EM_REPOS_USER'||'.MGMT_NOTIFY_QTABLE');
  DBMS_AQADM.START_QUEUE (queue_name => '&EM_REPOS_USER'||'.MGMT_NOTIFY_Q');
END;
/

-- Bug#8886999 - Increased the 'value' FROM 256 TO 4000

CREATE OR REPLACE TYPE MGMT_NOTIFY_COLUMN AS OBJECT
(
   column_label VARCHAR2(64),
   column_label_nlsid VARCHAR2(64),
   value VARCHAR2(4000)
);
/
CREATE OR REPLACE TYPE MGMT_NOTIFY_COLUMNS IS TABLE OF MGMT_NOTIFY_COLUMN;
/

CREATE OR REPLACE TYPE MGMT_NOTIFY_TGT_PROP IS OBJECT
(
  name  VARCHAR2(64),   -- Interbal name of the target property
  value VARCHAR2(4000), -- target property value
  label VARCHAR2(128),  -- Default target property label
  nls_id VARCHAR2(128)  -- NLS ID of the target property name
);
/

CREATE OR REPLACE TYPE MGMT_NOTIFY_TGT_PROP_ARRAY AS TABLE OF MGMT_NOTIFY_TGT_PROP;
/

-- These are the types used to pass information to PLSQL notification methods

-- This is the base object
CREATE OR REPLACE TYPE MGMT_NOTIFY_OBJECT IS OBJECT
(rule_name             VARCHAR2(132),
 rule_owner            VARCHAR2(64)
) NOT FINAL;
/

-- This contains details of a metric severity or a policy violation
CREATE OR REPLACE TYPE MGMT_NOTIFY_SEVERITY UNDER MGMT_NOTIFY_OBJECT
(target_name           VARCHAR2(256),
 target_type           VARCHAR2(64),
 timezone              VARCHAR2(64),
 host_name             VARCHAR2(128),
 metric_name           VARCHAR2(64),
 metric_description    VARCHAR2(128),
 metric_column         VARCHAR2(64),
 metric_value          VARCHAR2(1024),
 key_value_name        VARCHAR2(512),
 key_value             VARCHAR2(1290),
 key_value_guid        VARCHAR2(256),
 collection_timestamp  DATE,
 severity_code         NUMBER,
 message               VARCHAR2(4000),
 severity_guid         RAW(16),
 target_guid           RAW(16),
 metric_guid           RAW(16),
 ctxt_list             MGMT_NOTIFY_COLUMNS,
 cycle_guid            RAW(16),
 repeat_count          NUMBER,
 tgt_usrdef_props      MGMT_NOTIFY_TGT_PROP_ARRAY
);
/

-- This contains details of a job state change
-- Bug 5881705: Set job_type type to the type of
-- mgmt_job_type_info.job_type_default, varchar2(256)
CREATE OR REPLACE TYPE MGMT_NOTIFY_JOB UNDER MGMT_NOTIFY_OBJECT
(job_name              VARCHAR2(128),
 job_owner             VARCHAR2(256),
 job_type              VARCHAR2(256),
 job_status            NUMBER,
 occurred              DATE,
 state_change_guid     RAW(16),
 job_guid              RAW(16),
 execution_id          RAW(16),
 targets               SMP_EMD_NVPAIR_ARRAY
) NOT FINAL;
/

-- This contains details of a corrective action state change for both
-- metric severities and policy violations. For policy violations, the 
-- metric_name stores the policy with metric_column not being used
CREATE OR REPLACE TYPE MGMT_NOTIFY_CORRECTIVE_ACTION UNDER MGMT_NOTIFY_JOB
(metric_name           VARCHAR2(256),
 metric_column         VARCHAR2(64),
 metric_value          VARCHAR2(1024),
 severity_code         NUMBER,
 key_value_name        VARCHAR2(512),
 key_value             VARCHAR2(1290),
 key_value_guid        VARCHAR2(256),
 ctxt_list             MGMT_NOTIFY_COLUMNS
);
/

CREATE OR REPLACE TYPE MGMT_NOTIFY_PARAMETER_FORMAT IS OBJECT
(name VARCHAR2(256)
);
/
CREATE OR REPLACE TYPE MGMT_NOTIFY_PARAMETER_FORMATS IS TABLE OF MGMT_NOTIFY_PARAMETER_FORMAT;
/

-- This contains details of a job state changes for either a regular job 
-- or a corrective action
-- Bug 5881705: Set job_type type to the type of
-- mgmt_job_type_info.job_type_default, varchar2(256)
CREATE OR REPLACE TYPE MGMT_NOTIFY_JOB_SC AS OBJECT
(
   job_name VARCHAR2(64),
   job_type VARCHAR2(256),
   job_type_nlsid VARCHAR2(64),
   job_owner VARCHAR2(256),
   target_names MGMT_MEDIUM_STRING_ARRAY,
   target_types MGMT_MEDIUM_STRING_ARRAY,
   target_timezone VARCHAR2(64),
   logged DATE,
   occurred DATE,
   status NUMBER(2),
   job_id RAW(16),
   execution_id RAW(16),
   step_id NUMBER(38),
   label VARCHAR2(256),
   label_nlsid VARCHAR2(256),
   metric_name VARCHAR2(256),
   metric_column VARCHAR2(64),
   metric_value VARCHAR2(1024),
   violation_level NUMBER,
   keys MGMT_NOTIFY_COLUMNS,
   ctxt_list MGMT_NOTIFY_COLUMNS
);
/

-- This type is for 20 week notification schedule
--
create or replace type MGMT_NOTIFY_NVPAIR as OBJECT (
    name VARCHAR2(256),
    value  VARCHAR2(4000)
);
/

create or replace type MGMT_NOTIFY_NVPAIR_ARRAY as TABLE of MGMT_NOTIFY_NVPAIR;
/

create or replace type mgmt_notification_rule_details is object (
 rule_name varchar2(256),
 rule_owner varchar(256),
 target_name varchar2(256),
 target_type varchar2(256),
 target_guid raw(16),
 metric_name varchar2(256),
 metric_column varchar2(256),
 column_label varchar2(256),
 key_values varchar2(2000),
 severities varchar2(256),
 critical_cas varchar2(256),
 warning_cas varchar2(256),
 notification_devices varchar2(2000),
 email_user varchar2(2000)
);
/

create or replace type mgmt_notif_rules_detail_array as table of mgmt_notification_rule_details;
/

@@&&EM_SQL_ROOT/core/10.2.0.4/notification/notification_types.sql

