Database Schema
Oracle Utilities Network Management System MultiSpeak Adapter uses several databases tables to store meter status information received from the AMR system and pending meter ping requests.
AMR_REQUESTS
AMR_REQUESTS is populated by the outage prediction engine when a request for meter information is submitted. There is one row per request, and each request can involve multiple meters.
Field
DataType
Nullable
Comments
REQUEST_IDX
NUMBER
No
AMR request id.
PRIMARY KEY
EVENT_CLS
NUMBER
No
The class part of the handle of the event for which the AMR request was made.
EVENT_IDX
NUMBER
No
The index part of the handle of the event for which the AMR request was made.
REQUEST_TIME
DATE
No
The timestamp when the AMR request was created (in the UTC time zone).
WHO_REQUESTED
VARCHAR2(32)
Yes
User name of the operator who created the AMR request.
AMR_COMPLETE_TIME
DATE
Yes
The timestamp when the AMR request was completed or canceled (in the UTC time zone).
WHO_COMPLETED
VARCHAR2(32)
Yes
User name of the operator who completed or canceled the AMR request.
REQUEST_TYPE
NUMBER
Yes
Request type.
Possible values:
• 1 - PSO Verification
• 2 - PDO Verification
• 3 - Restoration Verification
• 4 - Manual
QUERY_TYPE
NUMBER
Yes
Query type.
Possible values:
• 0 - simple meter status query
• 1 - complex meter information query
• 100 - voltage query
• 101 - phase voltage query
Note: Query types with values below 100 are meter status (ping) queries. Query types with values 100 and above are meter read queries.
STATUS
NUMBER
Yes
Status of the AMR request.
Possible values:
1- active
2 - explicitly completed
3 -cancelled
DEVICE_CLS
NUMBER
Yes
The class part of the handle of the device for which the AMR request was made.
DEVICE_IDX
NUMBER
Yes
The index part of the handle of the device for which the AMR request was made.
NCG
NUMBER
Yes
NCG of the device for which the AMR request was made.
TTL
NUMBER(9)
Yes
The Time-To-Live of the request in seconds.
AMR_RESPONSES
The AMR_RESPONSES table is used to transfer meter status information between the outage prediction engine and the MultiSpeak adapter. The outage prediction engine inserts rows into this table when a request for meter information is submitted. Every request in AMR_RESPONSES is represented by one row for each meter requested. The MultiSpeak adapter updates this table as requested meter status information becomes available.
Field
DataType
Nullable
Comments
ID
Number
No
Unique record identifier. PRIMARY KEY
REQUEST_IDX
NUMBER
Yes
AMR request id.
REQUEST_TIME
DATE
Yes
Request timestamp (in the UTC time zone).
METER_NO
VARCHAR2(256)
Yes
Meter number as known to the AMR system
METER_ID
NUMBER
No
NMS meter identifier.
REQ_STATUS
VARCHAR2(1)
Yes
Request status.
Possible values:
• N - new request (not yet sent to AMR)
• P - pending request (waiting for AMR response)
• S - suppressed request
• R - AMR response received
• C - completed request
STATUS
VARCHAR2(256)
Yes
Meter status received from AMR.
Possible values:
• ON - meter has power
• OFF - meter does not have power
AMR_ERROR
VARCHAR2(256)
Yes
Error message received from the AMR system.
RECEIVED_TIME
DATE
Yes
Timestamp when the response was received from the AMR system (in the UTC time zone).
RESULT_TIME
DATE
Yes
Timestamp returned by the AMR system for the meter status (in the UTC time zone).
PROBABILITY
NUMBER
Yes
The probability of the meter having power (0 - no power; 100 - meter has power.)
ATTEMPT_COUNT
NUMBER(3)
Yes
Count of the attempts made to send a request to the AMR system.
VOLTAGE
VARCHAR2(16)
Yes
Meter voltage reading.
VOLTAGE_A
VARCHAR2(16)
Yes
Meter phase A voltage reading.
VOLTAGE_B
VARCHAR2(16)
Yes
Meter phase B voltage reading.
VOLTAGE_C
VARCHAR2(16)
Yes
Meter phase C voltage reading.
AMR_CU_METERS
Table AMR_CU_METERS contains information about all meters known to the Oracle Utilities Network Management System. This table is also used to cache the latest known meter status information to reduce the number of requests to the AMR system.
Field
DataType
Nullable
Comments
METER_ID
VARCHAR2(14)
No
Meter identifier in Oracle Utilities Network Management System.
METER_NO
VARCHAR2(20)
Yes
Meter identifier used by the AMR system.
RESULT_TIME
DATE
Yes
Timestamp of the latest meter status update (in the UTC time zone).
POWER_UP_TIME
DATE
Yes
Timestamp of the latest power-up message (in the UTC time zone).
LAST_GASP_TIME
DATE
Yes
Timestamp of the latest "last gasp" message (in the UTC time zone).
ALT_METER_NO
VARCHAR2(256)
Yes
Alternative meter number.
AMR_ENABLED
VARCHAR2(1)
Yes
Indicator that meter is AMR-enabled.
STATUS
VARCHAR2(256)
Yes
Latest known meter status.
Possible values:
• ON - meter has power
• OFF - meter does not have power
REQUEST_IDX
NUMBER
Yes
AMR request id for the latest received meter status.
PROBABILITY
NUMBER
Yes
Latest known probability of the meter having power (0 – no power; 100 – meter has power).
BELLWETHER
VARCHAR2(1)
Yes
Bellwether meter flag (Y/N)
METER_PHASES
VARCHAR2(1)
Yes
Meter phase/multiphase meter flag.
VOLTAGE_
THRESHOLD
NUMBER
Yes
Meter low voltage threshold.
AMR_CU_METERS_HISTORY
The AMR_CU_METERS_HISTORY table is used to store all meter status updates received from the AMR system. This table has the same columns as the AMR_CU_METERS table with the exception of the BELLWETHER column.
AMR_VOLTAGE_HISTORY
The AMR_VOLTAGE_HISTORY table is used to store meter voltage readings received from the AMR system.
Field
DataType
Nullable
Comments
METER_HIST_ID
NUMBER
No
Primary key (sequence generated).
METER_ID
NUMBER
Yes
NMS meter identifier.
METER_NO
VARCHAR2(256)
Yes
Meter number specific to the AMR system.
RESULT_TIME
DATE
Yes
Meter read time (in the UTC time zone).
VOLTAGE
NUMBER
Yes
Meter voltage.
VOLTAGE_A
NUMBER
Yes
Phase A meter voltage.
VOLTAGE_B
NUMBER
Yes
Phase B meter voltage.
VOLTAGE_C
NUMBER
Yes
Phase C meter voltage.