Understanding the Database Schema

Overview

The following section defines in detail the schema of each database tables used by the Generic IVR Adapter. This section defines the parameters used by the Generic IVR Adapter's stored procedures.

Database Table Schema

TROUBLE_CALLS Table Schema

The TROUBLE_CALLS table stores the trouble calls that are submitted by the external application. The Generic IVR Adapter polls this table and submits new trouble call records to Oracle Utilities Network Management System, so Oracle Utilities Network Management System could apply the outage analysis algorithm to predict the outage device. The external application indirectly inserts records to the TROUBLE_CALLS table by invoking the pr_trouble_calls stored procedure. See “pr_trouble_calls” for more information.

Each field of the TROUBLE_CALLS table is matched with SRSinput field. The mapping is configurable. A column names are directly tied up to a specific field of the INCIDENTS table or the JOBS table of Oracle Utilities Network Management System.

In effect, each field in the TROUBLE_CALLS table is mapped (and the mapping is configurable) to a particular field of the INCIDENTS table or the JOBS table of Oracle Utilities Network Management System. For more information, see “Trouble Call Mapping Configuration”.

In the 'Description' column, take note that field names prefixed with 'INC.' would come from the INCIDENTS table. Field names prefixed by 'JOBS.' would come from the JOBS table. Field names prefixed by 'CC.' would come from the CES_CUSTOMERS table.

Field Name	Nullable	Data Type	Description (JMS Input String Reference)
ADDR_BUILDING	Y	VARCHAR2(10)	Customer building address. Refer to ADDR_BUILDING for more information. Map to INC.ADDR_BUILDING
ADDR_CITY	Y	VARCHAR2(45)	Customer City/State. Refer to ADDR_CITY for more information. Maps to INC.ADDR_CITY
ADDR_CROSS_STREET	Y	VARCHAR2(255)	Intersection cross street name. Maps to INC.ADDR_CROSS_STREET.
ADDR_STREET	Y	VARCHAR2(255)	Customer address. Refer to ADDR_STREET for more information. Maps to INC.ADDRESS and JOBS.ADDR_STREET
ALTERNATE_PHONE	Y	VARCHAR2(32)	Alternative contact number. Refer to ALTERNATE_PHONE for more information. Maps to INC.ALTERNATE_PHONE
APPT_RANGE	Y	NUMBER	Appointment Range. Refer to APPT_RANGE for more information. Maps to INC.APPT_RANGE.
APPT_TIME	Y	DATE	Time of appointment. Refer to APPT_TIME for more information. Maps to INC.APPT_TIME.
APPT_TYPE	Y	VARCHAR2(16)	Type of appointment. Refer to APPT_TYPE for more information. Maps to INC.APPT_TYPE.
CALL_COMMENT	Y	VARCHAR2(255)	Customer Comment. Refer to COMMENT Property Name for more information. Maps to INC.OP_COMMENT.
CALL_ID	Y	VARCHAR2(16)	Not used.
CALL_STATUS	Y	VARCHAR2(1)	Status of the trouble call in the TROUBLE_CALLS table. The Generic IVR Adapter uses this internally to identify the status of this trouble call. The possible values are as follows: 'N' - New trouble call 'I' - The Generic IVR Adapter is in the process of submitting this trouble call to Oracle Utilities Network Management System 'C' - Trouble call submission to Oracle Utilities Network Management System is completed. The Generic IVR Adapter uses this field as one of the criteria in purging the TROUBLE_CALLS table for 'old' records. Records with CALL_STATUS field = 'C' will be purged.
CALL_TIME	N	DATE	Input time of call. Refer to CALL_TIME for more information. Maps to INC.INPUT_TIME The Generic IVR Adapter uses this field as one of the criteria in purging the TROUBLE_CALLS table for 'old' records. The TROUBLE_CALL record is 'aged' based on the system date/time and the CALL_TIME field. Any record older than a predefined number of days will be removed. See keepdbinfo for more information.
CALL_TYPE	Y	VARCHAR2(8)	Type of call. Refer to CALL_TYPE for more information. Maps to INC.TYPE
CALLBACK_LATE	Y	VARCHAR2(1)	Callback late indicator. Refer to CALLBACK_LATE for more information. The possible values are as follows: 'Y' - It is OK to call back even when it is already late. 'N' - It is not OK to call back when it is already late. If no value was supplied, this field will default to 'N'. This information is only passed from the external application to Oracle Utilities Network Management System (using the Trouble Calls Data Flow), and back to the external application (using the Callback Requests Data Flow). No other action is taken.
CALLBACK_EMAIL	Y	VARCHAR2(254)	The callback email address.
CALLBACK_METHOD	N	number	The callback method requested (1‑phone, 2‑text, 3‑email).
CALLBACK_TEXT	Y	VARCHAR2(32)	The callback text number.
CALLBACK_REQUEST	Y	NUMBER	Callback request indicator. Refer to CALLBACK_REQUEST for more information. The possible values are as follows: '0' - callback not requested '1' - restoration callback requested '4' - ERT callback requested '5' - ERT and restoration callbacks requested Maps to INC.CALLBACK_REQUEST
CALLBACK_TIME	Y	DATE	Callback Before Time. Refer to CALLBACK_TIME for more information. Maps to INC.CALLBACK_TIME
CHECK_CUTOFF	Y	VARCHAR2(1)	Check cut-off customer indicator. Refer to CHECK_CUTOFF for more information. The possible values are as follows: 'Y' - check if the customer is disconnected 'N' - do not perform checking.
CLUE	Y	NUMBER	Indicates if call is clue if set to Y. Refer to “CLUE” for more information. Maps to INC.CLUE
COMBINE_PRI	Y	NUMBER	Total priority of call. Refer to COMBINE_PRI for more information.
CUST_CALL_CANCEL	Y	VARCHAR2(1)	Call cancel indicator. Refer to CUST_CALL_CANCEL for more information. Maps to INC.CALL_CANCEL
CUST_CRITICAL	Y	VARCHAR2(1)	Critical customer indicator. This is added to the critical C count of the outage. Refer to CUST_CRITICAL for more information. Maps to INC.CRITICAL_CUST
CUST_DEVICE_ALIAS	Y	VARCHAR2(32)	Customer Device Alias. Refer to CUST_DEVICE_ALIAS for more information. Maps to INC.OBJECT
CUST_DEVICE_CLS	Y	NUMBER	Corresponding CC.H_CLS field for the given CC.SERV_LOC_ID. This field does not have a corresponding input parameter in the pr_trouble_calls stored procedure. The stored procedure itself populates this field. Refer to CUST_DEVICE_CLS for more information. Maps to INC.H_CLS
CUST_DEVICE_IDX	Y	NUMBER	Corresponding CC.H_IDX field for the given CC.SERV_LOC_ID. This field does not have a corresponding input parameter in the pr_trouble_calls stored procedure. The stored procedure itself populates this field. Refer to CUST_DEVICE_IDX for more information. Maps to INC.H_IDX
CUST_DEVICE_NCG	Y	NUMBER	NCG of customer device. Refer to CUST_DEVICE_NCG for more information. Maps to INC.NCG
CUST_DEVICE_PARTITION	Y	NUMBER	Partition of customer device. Refer to CUST_DEVICE_ PARTITION for more information. Maps to INC.PARTITION
CUST_FIRST_NAME	Y	VARCHAR2(75)	Customer Name. Refer to CUST_FIRST_NAME for more information. Maps to INC.CUSTOMER_NAME and JOBS.CUSTOMER_NAME
CUST_ID	Y	VARCHAR2(64)	Unique customer record identifier. Maps to INC.CID.
CUST_INTERSECT_CLS	Y	NUMBER	Intersecting device class. Refer to CUST_INTERSECT_ CLS for more information.
CUST_INTERSECT_IDX	Y	NUMBER	Intersecting device index. Refer to CUST_INTERSECT_ IDX for more information.
CUST_INTERSECT_NCG	Y	NUMBER	Intersecting NCG. Refer to CUST_INTERSECT_ NCG for more information.
CUST_INTR_X	Y	NUMBER	Intersecting X coordinate. X coordinate used for intersection grouping.
CUST_INTR_Y	Y	NUMBER	Intersecting Y coordinate.Y coordinate used for intersection grouping.
CUST_KEY	Y	VARCHAR2(16)	Corresponding CC.ACCOUNT_NUMBER field for the given CC.SERV_LOC_ID. This field does not have a corresponding input parameter in the pr_trouble_calls stored procedure. The stored procedure itself populates this field. Refer to CUST_KEY for more information. Maps to INC.ACCOUNT_NUM
CUST_LAST_NAME	Y	VARCHAR2(75)	The last name of the customer. Refer to CUST_LAST_NAME for more information. Maps to INC.CUSTOMER_NAME and JOBS.CUSTOMER_NAME
CUST_LIFE_SUPPORT	Y	VARCHAR2(1)	Life support customer. Refer to CUST_LIFE_SUPPORT for more information. Maps to INC.LIFE_SUPPORT
CUST_ORDER_NUM	Y	VARCHAR2(16)	Customer order number. Refer to CUST_ORDER_NUM for more information. Maps to INC.ORDER_NUMBER
CUST_PHONE	Y	VARCHAR2(32)	Customer phone number. Refer to CUST_PHONE for more information. Maps to INC.CUSTOMER_PHONE and JOBS.CUSTOMER_PHONE
CUST_PHONE_AREA	Y	VARCHAR2(8)	Customer phone area code. Refer to CUST_PHONE_AREA for more information. Maps to INC.CUSTOMER_PHONE and JOBS.CUSTOMER_PHONE
CUST_PHONE_UPDATE	Y	VARCHAR2(1)	Whether to update customer phone. Refer to CUST_PHONE_ UPDATE for more information.
CUST_PRIORITY	Y	VARCHAR2(4)	Customer Priority. Refer to CUST_PRIORITY for more information. This is defined by customer and needs to be an integer string. Maps to INC.CUSTOMER_TYPE
CUST_STATUS	Y	NUMBER	Condition status of call.
CUST_TROUBLE_CODE	N	VARCHAR2(10)	Trouble code or customer complaint. Refer to CUST_TROUBLE_ CODE for more information. This is the trouble or complaint that the customer reports when making a call. The trouble code determines the priority of the incident. Trouble code mapping set up in Oracle Utilities Network Management System should be synchronized with the trouble code mapping set up on the external application. This is to ensure that the trouble code sent from the external application is interpreted similarly when the trouble code is received by Oracle Utilities Network Management System. Maps to INC.COMPLAINT
CUST_TROUBLE_QUEUE	Y	VARCHAR2(10)	Customer trouble queue. Refer to CUST_TROUBLE_ QUEUE for more information. This field contains the name of the work group queue that the event has been referred to. Maps to INC.TROUBLE_QUEUE and JOBS.TROUBLE_QUEUE
DRV_INST	Y	VARCHAR2(180)	Driving instructions. Maps to INC.DRV_INSTR1
EST_REST_TIME	Y	Date	The ERT.
EXTERNAL_ID	N	VARCHAR2(16)	External ID. Refer to EXTERNAL_ID for more information If it is used, its value should be unique. Maps to INC.EXTERNAL_ID and JOBS.EXTERNAL_ID
FUZZY_NCG_CLS	Y	NUMBER	Fuzzy control zone class.
FUZZY_NCG_IDX	Y	NUMBER	Fuzzy control zone index.
GENERAL_AREA	Y	VARCHAR2(32)	General Area. Not Used in the SPL OMS System. Maps to INC.GENERAL_AREA
GROUP_BY_NAME	Y	VARCHAR2(127)	Fuzzy control zone name.
GROUPABLE	Y	NUMBER	Indicates if call is groupable if set to 1. Maps to INC.GROUPABLE
MEET_TIME	Y	DATE	Time of customer meet. Refer to MEET_TIME for more information. Maps to INC.MEET_TIME
MEET_TYPE	Y	NUMBER	Customer meet type. Refer to MEET_TYPE for more information. Maps to INC.MEET_CODE
METER_ID	Y	VARCHAR2(32)	Customer meter number. Maps to INC.METER_ID
RELATED_EVT_APP	Y	NUMBER	Related event application.
RELATED_EVT_CLS	Y	NUMBER	Related event class. Maps to INC. RELATED_CLS
RELATED_EVT_IDX	Y	NUMBER	Related event index. Maps to INC. RELATED_IDX
REPORTED_ERT	Y	DATE	Estimated restoration time reported to caller. Maps to INC. REPORTED_EST_REST_TIME
SHORT_DESC	Y	VARCHAR2(128)	Trouble short description. Maps to INC.SHORT_DESC
TROUBLE_LOC	Y	VARCHAR2(255)	Incident's trouble location. Maps to INC.TROUBLE_LOC
UPDATE_EXISTING_INC	Y	NUMBER	Whether to update an existing incident. Refer to UPDATE_EXISTING_ INC for more information.
USER_NAME	Y	VARCHAR2(32)	Call-taker user name. Refer to USER_NAME for more information. Maps to INC.USER_NAME
X_REF	Y	NUMBER	Customer X coordinate. Refer to X_REF for more information. Maps to INC.X_COORD
Y_REF	Y	NUMBER	Customer Y coordinate. Refer to Y_REF for more information. Maps to INC.Y_COORD

TROUBLE_CALLBACKS Table Schema

The TROUBLE_CALLBACKS table contains callback request information that has to be reported to the external application. The table also stores the corresponding callback response received from the external application. The Generic IVR Adapter directly inserts new callback requests to the said table. It also directly picks up processed callbacks from the same table. The external application is provided two stored procedures for indirectly reading and updating callback information from the table.

From the table below, on the 'Description' column, take note that field names prefixed with 'INC.' would come from the INCIDENTS table.

Column Name	Nullable	Data Type	Description
ALTERNATE_PHONE	Y	VARCHAR2(38)	Populated by the Callback Requests Data Flow from INC.ALTERNATE_PHONE.
CALL_TAKER_ID	Y	VARCHAR2(32)	Populated by the Callback Requests Data Flow from INC.USER_NAME.
CALLBACK_DONE	Y	VARCHAR2(1)	Initially populated by the Callback Requests Data Flow as 'N', signifying that the callback is not yet done. As soon as the external application successfully returns the callback response to the Generic IVR Adapter (using pr_trouble_callback_responses stored procedure), the field is updated to 'Y', signifying that the callback has been done. Below is a list of valid values for this field. 'N' - Callback Has Not Been Done 'Y' - Callback Has Been Done The Generic IVR Adapter uses this field as one of the criteria in purging the TROUBLE_CALLBACKS table for 'old' records. Records with CALLBACK_DONE field = 'Y' will be purged.
CALLBACK_EMAIL	Y	VARCHAR2(254)	The callback email
CALLBACK_LATE	Y	VARCHAR2(1)	Populated by the Callback Requests Data Flow from INC.CALLBACK_LATE The possible values are as follows: 'Y' - It is OK to call back even when it is already late. 'N' - It is not OK to call back when it is already late. This information is only passed from the external application to Oracle Utilities Network Management System (using the Trouble Calls Data Flow), and back to the external application (using the Callback Requests Data Flow). No other action is taken.
CALLBACK_LATE_TIME	Y	DATE	Populated by the Callback Requests Data Flow from INC.CALLBACK_TIME. This information is only passed from the external application to Oracle Utilities Network Management System (using the Trouble Calls Data Flow), and back to the external application (using the Callback Requests Data Flow). No other action is taken.
CALLBACK_METHOD	N	number	The callback method requested (1‑phone, 2‑text, 3‑email)
CALLBACK_REASON	Y	VARCHAR2(100)	This is used by the Generic IVR Adapter to indicate the source of the callback request. This will default to 'OMS'.
CALLBACK_STATUS	Y	VARCHAR2(10)	Initially populated by the Callback Requests Data Flow as NULL; The field is repopulated by the external application (using pr_trouble_callback_responses stored procedure). The valid values are as follows: 'F' - Not Restored Callback 'R' - Restored Callback 'N' - Cancel Callback, unable to get a response The Callback Response Data Flow is responsible for sending the updated value to Oracle Utilities Network Management System. A remapped value is placed in INC.CALLBACK_STATUS.
CALLBACK_TEXT	Y	VARCHAR2(32)	The Callback text number
CALLBACK_TIME	Y	DATE	Initially populated by the Callback Requests Data Flow as NULL; The field could be repopulated by the external application (using pr_trouble_callback_responses stored procedure). The stored procedure defaults this field to the system date if no information was supplied by the external application. The Callback Response Data Flow is responsible for sending the updated value to Oracle Utilities Network Management System. The value is placed in INC.CB_CALL_TIME.
CAUSE_CODE	Y	VARCHAR2(32)	This is used to relay back to customers the cause of an outage when a callback is performed. Populated by the Callback Requests Data Flow from JOBS.CAUSE when the useExternalCause rule is set to 'yes' in the SRS_RULES.
CB_DETAIL_1	Y	VARCHAR2(80)	Populated by the Callback Requests Data Flow from a column in the PICKLIST_INFO_UPD_TR database table. Column name is configured in the IVR_ADAPTER_CONFIG database table.
CB_DETAIL_2	Y	VARCHAR2(80)	Populated by the Callback Requests Data Flow from a column in the PICKLIST_INFO_UPD_TR database table. Column name is configured in the IVR_ADAPTER_CONFIG database table.
CB_DETAIL_3	Y	VARCHAR2(80)	Populated by the Callback Requests Data Flow from a column in the PICKLIST_INFO_UPD_TR database table. Column name is configured in the IVR_ADAPTER_CONFIG database table.
CB_DETAIL_4	Y	VARCHAR2(80)	Populated by the Callback Requests Data Flow from a column in the PICKLIST_INFO_UPD_TR database table. Column name is configured in the IVR_ADAPTER_CONFIG database table.
CUSTOMER_ADDRESS	Y	VARCHAR2(255)	Populated by the Callback Requests Data Flow by concatenating INC.ADDR_BUILDING, INC.ADDRESS and INC.ADDR_CITY
CUSTOMER_COMMENT	Y	VARCHAR2(255)	Populated by the Callback Requests Data Flow from INC.OP_COMMENT.
CUSTOMER_COUNT	Y	NUMBER	Populated by the Callback Requests Data Flow from INC.USER_NAME.
CUSTOMER_NAME	Y	VARCHAR2(75)	Populated by the Callback Requests Data Flow from INC.CUSTOMER_NAME.
CUSTOMER_PHONE	Y	VARCHAR2(38)	Populated by the Callback Requests Data Flow from INC.CUSTOMER_PHONE.
EST_REST_TIME	Y	Date	The ERT
EVENT_CLS	Y	NUMBER(38)	Populated by the Callback Requests Data Flow from INC.EVENT_CLS.
EVENT_IDX	Y	NUMBER(38)	Populated by the Callback Requests Data Flow from INC.EVENT_IDX.
EXTERNAL_ID	Y	VARCHAR2(16)	Populated by the Callback Requests Data Flow from INC.EXTERNAL_ID.
INCIDENT_NUMB	N	NUMBER(38)	Populated by the Callback Requests Data Flow from INC.NUMB.
INCIDENT_TIME	Y	DATE	Populated by the Callback Requests Data Flow from INC.INPUT_TIME. The Generic IVR Adapter uses this field as one of the criteria in purging the TROUBLE_CALLBACKS table for 'old' records. The TROUBLE_CALLBACKS table record is 'aged' based on the system date/time and the INCIDENT_TIME field. Any record older than a predefined number of days will be removed. See “keepdbinfo” for more information.
OUTAGE_DURATION	Y	NUMBER	Outage duration in seconds. Populated by the Callback Requests Data with the difference between JOBS.RESTORE_TIME and JOBS.BEGIN_TIME.
PREMISE_ID	N	VARCHAR2(64)	Populated by the Callback Requests Data Flow from INC.CID.
PROCESS_STATUS	Y	VARCHAR2(1)	Initially populated by the Callback Requests Data Flow as 'N', signifying that the record is a new callback Once the record was fetched by the external application (using pr_trouble_callback_requests stored procedure), the field is automatically updated by the stored procedure to 'I' signifying that the external system is currently processing the callback response. As soon as the external application successfully returns the callback response to the Generic IVR Adapter (using pr_trouble_callback_responses stored procedure), the field is updated to 'C', signifying that the external application has completed the processing of the callback response. This field is internally maintained by the Generic IVR Adapter. Below is a list of valid values for this field. 'N' - New Callback 'I' - In Processing Of Callback Response 'C' - Completed The Processing Of Callback Response The Generic IVR Adapter uses this field as one of the criteria in purging the TROUBLE_CALLBACKS table for 'old' records. Records with PROCESS_STATUS field = 'C' will be purged.
SHORT_DESCRIPTION	Y	VARCHAR2(128)	Populated by the Callback Requests Data Flow from INC.SHORT_DESC This is the clue (e.g., 'Out') of the incident rather than the trouble code (e.g., '10000000'). 'Out' is short for 'All Power Out'.
TROUBLE_CODE	Y	VARCHAR2(32)	Populated by the Callback Requests Data Flow from INC.COMPLAINT. This is the trouble code (e.g., '10000000') of the incident rather than the clue (e.g., 'Out'). 'Out' is short for 'All Power Out'.

Stored Procedure Parameters

pk_ccb.submit_call

Data structures and parameters of the PK_CCB.SUBMIT_CALL stored procedure:

SUBTYPE udf_field IS VARCHAR2(256);

TYPE input_call_rec IS RECORD (

call_source_id VARCHAR2(3),

service_point_id trouble_calls.cust_id%TYPE,

external_id trouble_calls.external_id%TYPE,

account_number trouble_calls.cust_key%TYPE,

trouble_code trouble_calls.cust_trouble_code%TYPE,

first_name trouble_calls.cust_first_name%TYPE,

last_name trouble_calls.cust_last_name%TYPE,

phone trouble_calls.cust_phone%TYPE,

phone_area trouble_calls.cust_phone_area%TYPE,

alt_phone trouble_calls.alternate_phone%TYPE,

priority trouble_calls.cust_priority%TYPE,

critical_flag trouble_calls.cust_critical%TYPE,

life_support_flag trouble_calls.cust_life_support%TYPE,

call_id trouble_calls.general_area%TYPE,

call_time TIMESTAMP WITH TIME ZONE,

call_comment trouble_calls.call_comment%TYPE,

call_taker trouble_calls.usename%TYPE,

call_type trouble_calls.call_type%TYPE,

addr_building trouble_calls.addr_building%TYPE,

addr_street trouble_calls.addr_street%TYPE,

addr_cross_street trouble_calls.addr_street%TYPE,

addr_city_state trouble_calls.addr_city%TYPE,

drive_instr trouble_calls.drv_inst%TYPE,

meet_time TIMESTAMP WITH TIME ZONE,

meet_type trouble_calls.meet_type%TYPE,

group_by_name trouble_calls.group_by_name%TYPE,

device_id trouble_calls.cust_device_alias%TYPE,

meter_id trouble_calls.meter_id%TYPE,

trouble_queue trouble_calls.cust_trouble_queue%TYPE,

trouble_location trouble_calls.trouble_loc%TYPE,

x_coord trouble_calls.x_ref%TYPE,

y_coord trouble_calls.y_ref%TYPE,

appt_type trouble_calls.appt_type%TYPE,

appt_time TIMESTAMP WITH TIME ZONE,

appt_range trouble_calls.appt_range%TYPE,

callback_flag trouble_calls.callback_request%TYPE,

callback_before_time TIMESTAMP WITH TIME ZONE,

callback_late_flag trouble_calls.callback_late%TYPE,

intersection_cls trouble_calls.cust_device_cls%TYPE,

intersection_idx trouble_calls.cust_device_idx%TYPE,

cancel_flag trouble_calls.cust_call_cancel%TYPE,

update_flag trouble_calls.update_existing_inc%TYPE,

udf1 udf_field,

udf2 udf_field,

udf3 udf_field,

udf4 udf_field,

udf5 udf_field

);

-- new/updated trouble call

PROCEDURE submit_call (

p_call IN input_call_rec,

p_err_no OUT NUMBER,

p_err_msg OUT VARCHAR2

);

Description of the parameters of the PK_CCB.SUBMIT_CALL stored procedure.

Parameter Name	Parameter Type	Description
p_call.call_source_id	VARCHAR2(2)	Id unique to the call capture mechanism (always set to 2 for CCB, 3 for IVR - for example), Value will be prefixed to p_call.external_id field in this stored procedure. Used to allow NMS to maintain unique call ids (incidents.external_id) across multiple call taking systems submitting independent (overlapping) sets of external_ids. Generally an integer (to better support Interactive Voice Response systems) - but can be project specific.
p_call.service_point_id	VARCHAR2(64)	Service point id.
p_call.external_id	VARCHAR2(16)	Call external id unique ID from call capture system (CCB). To ensure uniqueness a given NMS implementation needs to agree on a fixed length field (12 characters for example).
p_call. account_number	VARCHAR2(30)	Customer account number.
p_call.trouble_code	VARCHAR2(32)	Call trouble code. Integer passed as a string - each character (0-9) indicates a specific selection (or 0 for non-selection) from each of 1 to 32 different trouble call categories. Project configurable.
p_call.first_name	VARCHAR2(75)	Customer first name or full name (if customer name is passed in a single field).
p_call.last_name	VARCHAR2(75)	Customer last name.
p_call.phone	VARCHAR2(32)	Customer phone number.
p_call.phone_area	VARCHAR2(8)	Customer phone area code.
p_call.alt_phone	VARCHAR2(32)	Alternative/callback phone number.
p_call.priority	VARCHAR2(4)	The same value as ces_customers.priority should be passed in. This is used to determine critical customer type.
p_call.critical_flag	VARCHAR2(1)	Critical customer (Y/N)
p_call.life_support_flag	VARCHAR2(1)	Life support flag (Y/N).
p_call.call_id	VARCHAR2(32)	Call identifier (for example, 911 call id) - mapped to general_area.
p_call.call_time	TIMESTAMP WITH TIME ZONE	Call capture time from external call capture system
p_call.call_comment	VARCHAR2(255)	Comments
p_call.call_taker	VARCHAR2(32)	Call taker id.
p_call.call_type	VARCHAR2(8)	Call type.
CC&B should leave this field empty.
p_call.addr_building	VARCHAR2(10)	Building/block number.
p_call.addr_street	VARCHAR2(255)	Street address or name of the first intersection street.
p_call.addr_cross_street	VARCHAR2(255)	Name of the second intersection (cross) street.
p_call.addr_city_state	VARCHAR2(45)	City and (optionally) state.
p_call.drive_instr	VARCHAR2(180)	Driving instructions.
p_call.meet_time	TIMESTAMP WITH TIME ZONE	Meet time.
p_call.meet_type	NUMBER	Meet action code. Possible values: • 0 - for non-meet calls • 1 - create new meet • 2 - reschedule existing meet • 3- cancel existing meet
p_call.group_by_name	VARCHAR2(127)	Optional control zone name for fuzzy calls.
p_call.device_id	VARCHAR2(32)	Device alias.
p_call.meter_id	VARCHAR2(32)	Meter number.
p_call.trouble_queue	VARCHAR2(10)	Trouble queue (Tree Trimming, Underground, etc)
p_call.trouble_location	VARCHAR2(255)	Trouble location.
p_call.x_coord	NUMBER	X coordinate in the NMS electrical network model coordinate system (generally NOT lat/long). If not provided JMService will default to the coordinates for the supply_node from the point_coordinates table.
p_call.y_coord	NUMBER	Y coordinate - match for X coordinate above.
p_call.appt_type	NUMBER	Appointment type.
p_call.appt_time	TIMESTAMP WITH TIME ZONE	Appointment time.
p_call.appt_range	NUMBER	Appointment time window in minutes.
p_call.callback_flag	NUMBER	Callback request flag. • 0 - callback has not been requested • 1 - restoration callback has been requested. • 4 - ERT callback has been requested • 5 - ERT and restoration callbacks have been requested
p_call.callback_before_time	TIMESTAMP WITH TIME ZONE	Callback requested before this time.
p_call.callback_late_flag	VARCHAR2(1)	Callback late ok flag (Y/N)
p_call.intersection_cls	NUMBER	If p_call.service_point_id is NOT null this field is ignored. If not null and p_call.service_point_d is null interpreted as att_street_intersection.h_cls. Used to help identify an intersection (when paired with p_call.intersection_idx)
p_call.intersection_idx	NUMBER	If p_call.service_point_id is NOT null this field is ignored. If not null and p_call.service_point_id is null interpreted as att_street_intersection.h_idx - to help identify an intersection (when paired with p_call.intersection_cls).
p_call.cancel_flag	VARCHAR2(1)	Call cancel flag (Y/N).
p_call.update_flag	NUMBER	If 0 then this is a new call, otherwise this is an update to an existing call.
p_call.udf1	VARCHAR2(255)	User-defined call field 1.
p_call.udf2	VARCHAR2(255)	User-defined call field 1.
p_call.udf3	VARCHAR2(255)	User-defined call field 1.
p_call.udf4	VARCHAR2(255)	User-defined call field 1.
p_call.udf5	VARCHAR2(255)	User-defined call field 1.
p_err_no	NUMBER	Error code. In case of successful execution 0 is returned.
p_err_msg	VARCHAR2(200)	Internal error message.

pk_ccb.job_history

Stored procedure PK_CCB.JOB_HISTORY allows caller to retrieve list of jobs matching passed in search condition.

The following types of search conditions are supported:

• Search for specific customer by service point id, premise id or account number.

• Location-based search. Search for jobs at or nearby specified location. Location can be street intersection or street segment (block).

• Fuzzy outage search. Search for fuzzy jobs by external id, call identifier, caller name or caller phone.

• Custom search. To use custom search the stored procedure has to be modified by the project. Additional search parameters are passed in the 'p_custom' field.

CREATE OR REPLACE TYPE customer_search_obj AS OBJECT (

serv_point_id VARCHAR2(64),

premise_id NUMBER,

account_number VARCHAR2(30)

)

CREATE OR REPLACE TYPE location_search_obj AS OBJECT (

city VARCHAR2(200),

state VARCHAR2(30),

street1 VARCHAR2(200),

street2 VARCHAR2(200),

block_number NUMBER

)

CREATE OR REPLACE TYPE fuzzy_search_obj AS OBJECT (

external_id VARCHAR2(200),

call_id VARCHAR2(200),

caller_name VARCHAR2(200),

caller_phone VARCHAR2(200)

)

CREATE OR REPLACE TYPE custom_search_obj AS OBJECT (

field1 VARCHAR2(200),

field2 VARCHAR2(200),

field3 VARCHAR2(200),

field4 VARCHAR2(200),

field5 VARCHAR2(200)

)

TYPE nms_cursor IS REF CURSOR;

-- Get job history.

PROCEDURE job_history (

p_cust IN customer_search_obj,

p_loc IN location_search_obj,

p_fuzzy IN fuzzy_search_obj,

p_custom IN custom_search_obj,

p_num_days IN NUMBER,

p_jobs OUT nms_cursor,

p_err_no OUT NUMBER,

p_err_msg OUT VARCHAR2

);

Description of the parameters of the PK_CCB.JOB_HISTORY stored procedure.

Parameter Name	Parameter Type	Description
p_cust.serv_point_id	VARCHAR2(64)	Service point id.
p_cust.premise_id	NUMBER	Service location (premise id).
p_cust.account_number	VARCHAR2(30)	Customer account number.
p_loc.city	VARCHAR2(200)	City.
p_loc.state	VARCHAR2(30)	State.
p_loc.street1	VARCHAR2(200)	Street name. This field is used in both street intersection search and street segment search.
p_loc.street2	VARCHAR2(200)	Second street name for street intersection search.
p_loc.block_number	NUMBER	Block number for street segment search.
p_fuzzy.external_id	VARCHAR2(200)	Call external id.
p_fuzzy.call_id	VARCHAR2(200)	Call identifier (for example, id for 911 calls).
p_fuzzy.caller_name	VARCHAR2(200)	Caller name.
p_fuzzy.caller_phone	VARCHAR2(200)	Caller phone number.
p_custom.xxx		Implementation-defined search parameters.
p_cmp_days	NUMBER	If greater than 0 then switching plans completed within specified number of days in the past will be returned in addition to current and future switching plans.
p_jobs	nms_cursor	Returned jobs information.
p_err_no	NUMBER	Error code. In case of successful execution 0 is returned.
p_err_msg	VERCHAR2(200)	Internal error message.

For each returned job the following information is included.

TYPE job_rec IS RECORD (

serv_point_id ces_customers.id%TYPE,

serv_point_addr ces_customers.address%TYPE,

event_idx jobs.event_idx%TYPE,

begin_time TIMESTAMP WITH TIME ZONE,

est_rest_time TIMESTAMP WITH TIME ZONE,

est_rest_time_source jobs.est_source%TYPE,

restore_time TIMESTAMP WITH TIME ZONE,

cust_out jobs.num_cust_out%TYPE,

comments jobs.operator_comment%TYPE,

alarm_state jobs.alarm_state%TYPE,

alarm_state_desc te_valid_states.description%TYPE,

trouble_location jobs.display_name%TYPE,

status jobs.status%TYPE,

device_class jobs.devcls_name%TYPE,

trouble_code jobs.trouble_code%TYPE,

feeder_name jobs.feeder_name%TYPE,

cause jobs.cause%TYPE,

description jobs.description%TYPE,

referral_group jobs.referral_group%TYPE,

last_update_time TIMESTAMP WITH TIME ZONE,

udf1 udf_field,

udf2 udf_field,

udf3 udf_field,

udf4 udf_field,

udf5 udf_field

);

Field Name	Field Type	Description
serv_point_id	VARCHAR2(64)	Service point id.
serv_point_addr	VARCHAR2(200)	Service point address
event_idx	NUMBER	Event index.
begin_time	TIMESTAMP WITH TIME ZONE	Outage begin time.
est_rest_time	TIMESTAMP WITH TIME ZONE	Estimated restoration time (ERT).
est_rest_time_source	VARCHAR2(1)	ERT source. Possible values: • N - no ERT. • I - Initial ERT. • C - crew‑entered via mobile interface. • S - ERT calculated by Storm Management. • O - ERT calculated by Storm Management (crew on-site). • P - Non-publisher ERT. • G - ERT override is in effect. • D - ERT delay is in effect. • U - User‑entered via applications or tools (for example, Work Agenda, Event Details, or Crew tools) other than Storm Management. • m - Minimum ERT is not reached. The ERT is below the set threshold for minimum ERT. • M - Maximum ERT exceeded. The Storm Management calculated ERT is beyond a set threshold for maximum ERT. • W - System-wide default ERT. • A - Auto-extended ERT. • E - ERT generated by Storm Management while a crew is en route to the event.
restore_time	TIMESTAMP WITH TIME ZONE	Outage restoration time.
cust_out	NUMBER	Number of customers affected by the outage.
comments	VARCHAR2(255)	Operator's comment. Note some customers increase this to max allowed (4k).
alarm_state	VARCHAR2(32)	Outage state.
alarm_state_desc	VARCHAR2(80)	Description of the outage state.
trouble_location	VARCHAR2(255)
status	NUMBER	Job type. Possible values: • 0 - Fuzzy outage. • 1 - Probable/predicted service outage. • 2 - Probable/predicted device outage. • 3 - Real service outage. • 4 - Real device outage. • 7 - Non-outage. • 8 - Critical meet. • 9 - Future meet. • 10 - Confirmed service outage. • 11 - Confirmed secondary outage. • 13 - Probable/predicted momentary outage. • 14 - Real momentary outage. • 15 - Planned outage. • 16 - Non-electric event. • 17 - Master switching job. • 18 - Fault current event. • 19 - Optimization event. • 20 - Active Network Management job. • 21 - Protection Scheme job.
device_class	VARCHAR2(32)	Outage device class name (e.g., fuse)
trouble_code	VARCHAR2(128)	Trouble code.
feeder_name	VARCHAR2(32)	Feeder name.
cause	VARCHAR2(32)	Outage cause.
description	VARCHAR2(128)	Job description.
referral_group	VARCHAR2(32)	Referral group.
last_update_time	TIMESTAMP WITH TIME ZONE	Timestamp of the latest update to the outage record.
udf1	VARCHAR2(255)	Job user-defined field 1.
udf2	VARCHAR2(255)	Job user-defined field 2.
udf3	VARCHAR2(255)	Job user-defined field 3.
udf4	VARCHAR2(255)	Job user-defined field 4.
udf5	VARCHAR2(255)	Job user-defined field 5.

pk_ccb.call_history

Stored procedure PK_CCB.CALL_HISTORY will allow caller to retrieve list of calls matching search condition.

Following types of search conditions will be supported:

• Search for calls for a specific customer by service point id, premise id or account number.

• Location-based search. Search for calls at or nearby specified location. Location can be street intersection or street segment (block).

• Fuzzy outage search. Search for fuzzy calls by external id, call identifier, caller name or caller phone.

Custom search. To use custom search the stored procedure has to be modified by the project. Additional search parameters are passed in the 'p_custom' field.

PROCEDURE call_history (

p_cust IN customer_search_obj,

p_loc IN location_search_obj,

p_fuzzy IN fuzzy_search_obj,

p_custom IN custom_search_obj,

p_num_days IN NUMBER,

p_calls OUT nms_cursor,

p_err_no OUT NUMBER,

p_err_msg OUT VARCHAR2

);

For each returned call the following information is included.

-- call history record

TYPE call_rec IS RECORD (

external_id incidents.external_id%TYPE,

call_id incidents.general_area%TYPE,

serv_point_id incidents.cid%TYPE,

call_time TIMESTAMP WITH TIME ZONE,

address incidents.address%TYPE,

short_desc incidents.short_desc%TYPE,

comments incidents.op_comment%TYPE,

call_taker incidents.user_name%TYPE,

cust_name incidents.customer_name%TYPE,

status incidents.active%TYPE,

udf1 udf_field,

udf2 udf_field,

udf3 udf_field,

udf4 udf_field,

udf5 udf_field

);

Field Name	Field Type	Description
external_id	VARCHAR2(16)	Call external id.
call_id	VARCHAR2(32)	Call identifier (for example, id for 911 calls).
serv_point_id	VARCHAR2(64)	Service point id.
call_time	TIMESTAMP WITH TIME ZONE	Call time.
Address	VARCHAR2(255)	Address for the call.
short_desc	VARCHAR2(256)	Trouble code.
Comments	VARCHAR2(255)	Comments.
call_taker	VARCHAR2(32)	Call taker id.
cust_name	VARCHAR2(75)	Customer/caller name.
Status	VARCHAR2(1)	Call status. Possible values (other values can exist in NMS but they would not be returned by this procedure):"Y - active call "N - inactive/restored call "C - canceled call "E - call belongs to canceled job
udf1	VARCHAR2(255)	Call user-defined field 1.
udf2	VARCHAR2(255)	Call user-defined field 2.
udf3	VARCHAR2(255)	Call user-defined field 3.
udf4	VARCHAR2(255)	Call user-defined field 4.
udf5	VARCHAR2(255)	Call user-defined field 5.

pk_ccb.switching_history

The stored procedure PK_CCB.SWITCHING_HISTORY allows a caller to retrieve a list of current, future, and (optionally) past switching plans affecting a given customer.

PROCEDURE switching_history (

p_cust IN customer_search_obj,

p_custom IN custom_search_obj,

p_num_days IN NUMBER,

p_sw_plans OUT nms_cursor,

p_err_no OUT NUMBER,

p_err_msg OUT VARCHAR2

);

Description of the parameters of the PK_CCB.SWITCHING_HISTORY stored procedure.

Parameter Name	Parameter Type	Description
p_cust.serv_point_id	VARCHAR2(64)	Service point id.
p_cust.premise_id	NUMBER	Service location (premise id).
p_cust.account_number	VARCHAR2(30)	Customer account number.
p_custom		Implementation-defined search parameters.
p_sw_plans	nms_cursor	Returned switching plan information.
p_err_no	NUMBER	Error code In case of successful execution 0 is returned.
p_err_msg	VARCHAR2(200)	Error message.

For each returned switching plan following information is included.

-- switching plan record

TYPE switching_plan_rec IS RECORD (

plan_class swman_sheet_cls.switch_sheet_type%TYPE,

plan_number swman_sheet.switch_sheet_idx%TYPE,

start_date TIMESTAMP WITH TIME ZONE,

end_date TIMESTAMP WITH TIME ZONE,

device_alias swman_sheet.device_alias%TYPE,

state te_valid_states.state_name%TYPE,

work_district swman_sheet_extn.string_value%TYPE,

work_location swman_sheet_extn.string_value%TYPE,

work_description swman_sheet_extn.string_value%TYPE,

serv_point_id ces_customers.id%TYPE,

serv_point_addr ces_customers.address%TYPE,

udf1 udf_field,

udf2 udf_field,

udf3 udf_field,

udf4 udf_field,

udf5 udf_field,

udf6 udf_field,

udf7 udf_field,

udf8 udf_field,

udf9 udf_field,

udf10 udf_field

);

Field Name	Field Type	Description
plan_class	VARCHAR2(32)	Switching plan type (planned, emergency, ….).
plan_number	NUMBER	Switching plan number.
start_date	TIMESTAMP WITH TIME ZONE	Switching plan start date.
end_date	TIMESTAMP WITH TIME ZONE	Switching plan end date.
State	VARCHAR2(32)	Switching plan state.
work_district	VARCHAR2(500)
work_location	VARCHAR2(500)
work_description	VARCHAR2(500)
serv_point_id	VARCHAR2(64)	Service point id.
serv_point_addr	VARCHAR2(200)	Service point address

pk_ccb.trouble_code_config

Stored procedure PK_CCB.TROUBLE_CODE_CONFIG allows caller to retrieve list of trouble codes configured in the Oracle Utilities Network Management System.

PROCEDURE trouble_code_config (

p_trouble_codes OUT nms_cursor,

p_err_no OUT NUMBER,

p_err_msg OUT VARCHAR2

);

Field Name	Field Type	Description
p_trouble_codes	nms_cursor	Returned trouble code information.
p_err_no	NUMBER	Error code In case of successful execution 0 is returned.
p_err_msg	VARCHAR2(200)	Error message

For each returned trouble code following information is included:

-- trouble code configuration record

TYPE trouble_code_rec IS RECORD (

group_name srs_trouble_codes.group_name%TYPE,

group_order srs_trouble_codes.group_order%TYPE,

code_name srs_trouble_codes.code_name%TYPE,

code_num srs_trouble_codes.code_num%TYPE,

short_desc srs_trouble_codes.short_desc%TYPE,

description srs_trouble_codes.description%TYPE

);

Field Name	Field Type	Description
group_name	VARCHAR2(20)	Trouble code group name
group_order	NUMBER	Trouble code group order
code_name	VARCHAR2(40)	Trouble code name
code_num	NUMBER	Trouble code number within its group
short_desc	VARCHAR2(25)	Short description of the trouble code
description	VARCHAR2(70)	Long description of the trouble code

pr_trouble_calls

The Generic IVR Adapter provides the pr_trouble_calls procedure to be used by the external application to insert trouble calls in the TROUBLE_CALLS table. Refer to “Trouble Calls” for Data Flow details.

Below is a high level description of what is done inside the stored procedure

• Upon invoking the stored procedure, the p_premise_id parameter is used to query the CES_CUSTOMERS table (via the SERV_LOC_ID field) to retrieve the ACCOUNT_NUMBER, H_CLS and H_IDX fields of the said table. The value of these fields is placed in the corresponding columns of the TROUBLE_CALLS table.

• Other parameter values are inserted to corresponding fields on the TROUBLE_CALL table.

• Several TROUBLE_CALLS columns will have default value when no parameter value is supplied.

• Should there be an error in the record insert, an Oracle error is returned.

Note: If the given premise id has multiple accounts associated with it, only one account is used.

Below are details about each parameter of the pr_trouble_calls stored procedure. Note that the field name column indicates the corresponding column that is populated in the TROUBLE_CALLS table.

Parameters

Parameter	Direction	Data Type	Field Name	Comment
p_premise_id	In	VARCHAR2	PREMISE_ID	The value is inserted as is.
p_trouble_code	In	VARCHAR2	TROUBLE_CODE	Defaults to '1' followed by a certain number of '0'. If no value was supplied. The total length of the string is the total number of distinct groups in the SRS_TROUBLE_CODES table.
p_callback_ind	In	VARCHAR2	CALLBACK_INDICATOR	The possible values are as follows: '0' - callback not requested '1' - callback requested Defaults to '1' if no value is supplied. 'Y' is translated to '1'. 'N' is translated to '0'.
p_call_time	In	DATE	CALL_TIME	Defaults to the database system date if no value is supplied
p_call_taker_id	In	VARCHAR2	CALL_TAKER_ID	The value is inserted as is.
p_alternate_phone	In	VARCHAR2	ALTERNATE_PHONE	The value is inserted as is.
p_customer_comment	In	VARCHAR2	CUSTOMER_COMMENT	The value is inserted as is.
p_customer_phone	In	VARCHAR2	CUSTOMER_PHONE	The value is inserted as is.
p_customer_name	In	VARCHAR2	CUSTOMER_NAME	The value is inserted as is.
p_customer_address	In	VARCHAR2	CUSTOMER_ADDRESS	The value is inserted as is.
p_customer_city_state	In	VARCHAR2	CUSTOMER_CITY_STATE	The value is inserted as is.
p_customer_priority	In	VARCHAR2	CUSTOMER_PRIORITY	The value is inserted as is.
p_external_id	In	VARCHAR2	EXTERNAL_ID	The value is inserted as is.
p_device_alias	In	VARCHAR2	DEVICE_ALIAS	The value is inserted as is.
p_check_cutoff_ind	In	VARCHAR2	CHECK_CUTOFF_IND	The possible values are as follows: 'Y' - check if the customer is disconnected 'N' - do not perform checking. Defaults to 'N' if no value is supplied
p_callback_late_ind	In	VARCHAR2	CALLBACK_LATE_IND	The possible values are as follows: 'Y' - It is OK to call back even when it is already late. 'N' - It is not OK to call back when it is already late. Defaults to 'N' if no value is supplied
p_callback_before_time	In	DATE	CALLBACK_BEFORE_TIME	The value is inserted as is.
p_trouble_queue	In	VARCHAR2	TROUBLE_QUEUE	The value is inserted as is.
p_meter_id	In	VARCHAR2	METER_ID	The value is inserted as is.
p_supply_id	In	NUMBER	SUPPLY_ID	The value is inserted as is.
p_cust_phone_area	In	VARCHAR2	CUST_PHONE_AREA	The value is inserted as is.
p_cust_last_name	In	VARCHAR2	CUST_LAST_NAME	The value is inserted as is.
p_general_area	In	VARCHAR2	GENERAL_AREA	The value is inserted as is.
p_cust_order_num	In	VARCHAR2	CUST_ORDER_NUM	The value is inserted as is.
p_drv_inst	In	VARCHAR2	DRV_INST	The value is inserted as is.
p_cust_life_support	In	VARCHAR2	CUST_LIFE_SUPPORT	The value is inserted as is.
p_cust_call_cancel	In	VARCHAR2	CUST_CALL_CANCEL	The value is inserted as is.
p_short_desc	In	VARCHAR2	SHORT_DESC	The value is inserted as is.
p_addr_building	In	VARCHAR2	ADDR_BUILDING	The value is inserted as is.
p_meet_time	In	DATE	MEET_TIME	The value is inserted as is.
p_meet_type	In	NUMBER	MEET_TYPE	The value is inserted as is.
p_groupable	In	NUMBER	GROUPABLE	The value is inserted as is.
p_clue	In	NUMBER	CLUE	The value is inserted as is.
p_combine_pri	In	NUMBER	COMBINE_PRI	The value is inserted as is.
p_cust_status	In	NUMBER	CUST_STATUS	The value is inserted as is.
p_cust_intr_x	In	NUMBER	CUST_INTR_X	The value is inserted as is.
p_cust_intr_y	In	NUMBER	CUST_INTR_Y	The value is inserted as is.
p_cust_intersect_cls	In	NUMBER	CUST_INTERSECT_CLS	The value is inserted as is.
p_cust_intersect_idx	In	NUMBER	CUST_INTERSECT_IDX	The value is inserted as is.
p_cust_intersect_ncg	In	NUMBER	CUST_INTERSECT_NCG	The value is inserted as is.
p_update_existing_inc	In	NUMBER	UPDATE_EXISTING_INC	The value is inserted as is.
p_fuzzy_ncg_cls	In	NUMBER	FUZZY_NCG_CLS	The value is inserted as is.
p_fuzzy_ncg_idx	In	NUMBER	FUZZY_NCG_IDX	The value is inserted as is.
p_group_by_name	In	VARCHAR2	GROUP_BY_NAME	The value is inserted as is.
p_cust_critical	In	VARCHAR2	CUST_CRITICAL	The value is inserted as is.
p_related_evt_cls	In	NUMBER	RELATED_EVT_CLS	The value is inserted as is.
p_related_evt_idx	In	NUMBER	RELATED_EVT_IDX	The value is inserted as is.
p_related_evt_app	In	NUMBER	RELATED_EVT_APP	The value is inserted as is.
p_x_ref	In	NUMBER	X_REF	The value is inserted as is.
p_y_ref	In	NUMBER	Y_REF	The value is inserted as is.
p_call_type	In	VARCHAR2	CALL_TYPE	The value is inserted as is.
p_cust_phone_update	In	VARCHAR2	CUST_PHONE_UPDATE	The value is inserted as is.
p_trouble_loc	In	VARCHAR2	TROUBLE_LOC	The value is inserted as is.
p_appt_type	In	VARCHAR2	APPT_TYPE	The value is inserted as is.
p_appt_time	In	DATE	APPT_TIME	The value is inserted as is.
p_appt_range	In	NUMBER	APPT_RANGE	The value is inserted as is.
p_cust_device_ncg	In	NUMBER	CUST_DEVICE_NCG	The value is inserted as is.
p_cust_device_partition	In	NUMBER	CUST_DEVICE_PARTITION	The value is inserted as is.
p_err_premise_id	Out	VARCHAR2	VARCHAR2(80)	The erroneous premise ID input parameter
p_err_oracle_error	Out	VARCHAR2	VARCHAR2(80)	Oracle's error message.

Note: The pr_trouble_calls stored procedure does not require a call status parameter from the user to insert in the TROUBLE_CALLS stored procedure. Each time the stored procedure inserts trouble calls in the TROUBLE_CALLS table, the CALL_STATUS field is always 'N', signifying that it is a new trouble call.

pr_trouble_callback_requests

Below is a high level description of what is done inside the stored procedure

• From the TROUBLE_CALLBACKS table, a list of new callback requests is created. These are the TROUBLE_CALLBACKS records whose PROCESS_STATUS field is 'N' (New) and CALLBACK_DONE field is 'N' (No).

• The list is captured within the stored procedure as a database cursor and returned to the calling application.

• The PROCESS_STATUS field of the records in the list is updated from 'N' (New) to 'I' (In Progress).

Note: Refer to the Data Flow Steps of the Callback Requests Data Flow on how the TROUBLE_CALLBACKS table is populated.

Parameter

Parameter	Direction	Cursor
p_callback_requests	In/Out	CALLBACK_CURSOR

Cursor Definition

Below are the fields of the CALLBACK_CURSOR. Take note that the CALLBACK_CURSOR is defined as a weakly typed cursor.

Field Name from the Cursor	Data Type	Field Name from TROUBLE_CALLBACKS	Comments
EVENT_CLS	NUMBER(38)	TCB.EVENT_CLS	Event class
EVENT_IDX	NUMBER(38)	TCB.EVENT_IDX	Event index
INCIDENT_NUMB	NUMBER(38)	TCB.INCIDENT_NUMB	Incident number
PREMISE_ID	VARCHAR2(50)	TCB.PREMISE_ID	Premise id
CUSTOMER_NAME	VARCHAR2(75)	TCB.CUSTOMER_NAME	Customer name
CUSTOMER_PHONE	VARCHAR2(38)	TCB.CUSTOMER_PHONE	Customer phone
CUSTOMER_ADDRESS	VARCHAR2(255)	TCB.CUSTOMER_ADDRESS	Customer address
ALTERNATE_PHONE	VARCHAR2(38)	TCB.ALTERNATE_PHONE	Customer alternate phone number
TROUBLE_CODE	VARCHAR2(32)	TCB.TROUBLE_CODE	This is the trouble code (e.g., '10000000') of the incident rather than the clue (e.g., 'Out'). 'Out' is short for 'All Power Out'.
SHORT_DESCRIPTION	VARCHAR2(128)	TCB.SHORT_DESCRIPTION	This is the clue (e.g., 'Out') of the incident rather than the trouble code (e.g., '10000000'). 'Out' is short for 'All Power Out'.
CUSTOMER_COMMENT	VARCHAR2(255)	TCB.CUSTOMER_COMMENT	Call-taker Comments. Comments provided by the customer or call-taker about the incident.
INCIDENT_TIME	DATE	TCB.INCIDENT_TIME	Input time of call. The input time of the incident.
EXTERNAL_ID	VARCHAR2(16)	TCB.EXTERNAL_ID	Unique call identifier. The unique identifier for the incident.
CALL_TAKER_ID	VARCHAR2(32)	TCB.CALL_TAKER_ID	Call-taker user name. The name of the call-taker or interface that created the call.
CALLBACK_LATE	VARCHAR2(1)	TCB.CALLBACK_LATE	The possible values are as follows: 'Y' - It is OK to call back even when it is already late. 'N' - It is not OK to call back when it is already late.
CALLBACK_LATE_TIME	DATE	TCB.CALLBACK_LATE_TIME
CALLBACK_REASON	VARCHAR2(100)	TCB.CALLBACK_REASON	This will default to 'OMS'.
CAUSE_CODE	VARCHAR2(32)	TCB.CAUSE_CODE	Cause code of the event related to the callback.

pr_trouble_callback_responses

Below is a high level description of what is done inside the stored procedure

• Upon receiving the input parameter values, the stored procedure verifies if either the p_incident_numb input parameter or the p_external_id input parameter was supplied. If both were supplied, the p_incident_numb parameter takes precedence.

• The stored procedure validates if the p_callback_status input parameter has a valid value. The valid values are 'F' (not restored), 'R' (restored) and 'N' (cancel callback).

• The stored procedure verifies that there is a unique combination of p_incident_numb and p_premise_id OR a unique combination of p_external_id and p_premise_id on the TROUBLE_CALLBACKS table, whichever among p_incident_numb or p_external_id was supplied.

• The TROUBLE_CALLBACKS table is updated for the p_incident_numb and p_premise_id combination OR the p_external_id and p_premise_id combination. The following fields are updated:

• The callback's CALLBACK_DONE field to 'Y' signifying that the callback was already done.

• The callback's CALLBACK_TIME field with provided p_callback_time stored procedure parameter. CALLBACK_TIME field defaults to the system date if no value was provided.

• The callback's CALLBACK_STATUS field with the appropriate callback response code.

• Should any of these steps fail, the stored procedure exits and returns the appropriate error.

Note: Refer to the Data Flow Steps of the Callback Response Data Flow on how the TROUBLE_CALLBACKS table is populated.

Parameters

Parameter	Direction	Data Type	Field Name	Comments
p_incident_numb	In	NUMBER	INCIDENT_NUMB	Incident Number. Either this or the p_external_id parameter has to be supplied
p_external_id	In	VARCHAR2	EXTERNAL_ID	External Id. Either this or the p_incident_numb parameter has to be supplied
p_premise_id	In	VARCHAR2	PREMISE_ID	Premise Id.
p_callback_status	In	VARCHAR2	CALLBACK_STATUS	The valid values are as follows: 'F' - Not Restored Callback 'R' - Restored Callback 'N' - Cancel Callback, unable to get a response
p_callback_time	In	DATE	CALLBACK_TIME	Defaulted to the system date if no value was supplied
p_err_incident_numb	Out	NUMBER		The erroneous incident number input parameter
p_err_external_id	Out	VARCHAR2		The erroneous external ID input parameter
p_err_premise_id	Out	VARCHAR2		The erroneous premise ID input parameter
p_err_oracle_error	Out	VARCHAR2		Oracle's error message

pr_customer_event_details

The Generic IVR Gateway provides the pr_customer_event_details stored procedure that gives the event details of an outage given the customer premise. Refer to the data flow detail for “Callback Requests”.

Below is a high level description of what is done inside the stored procedure.

• The stored procedure tries to get the latest event for the given premise ID (p_in_premise_id input parameter).

Parameters

Below are details about each parameter of the pr_customer_event_details stored procedure.

Parameter	Direction	Data Type	Comments
p_in_premise_id	In	VARCHAR2	Premise ID input parameter with a corresponding entry in CES_CUSTOMERS.SERV_LOC_ID
p_out_event_class	Out	NUMBER	Event class output parameter
p_out_event_index	Out	NUMBER	Event index output parameter
p_out_outage_status	Out	VARCHAR2	This is an abbreviation of the current state of the event, for instance, 'NEW', 'ASN', 'CMP', etc.
p_ out_outage_start_time	Out	DATE	The time of the lead call of the job.
p_ out_first_dispatch_time	Out	DATE	The time the first crew was dispatched
p_ out_est_restore_time	Out	DATE	The last estimate of restoration time.
p_ out_est_restore_time_src	Out	VARCHAR2	The source of the ERT of the event. Possible values are as follows: 'N' - none (no ERT) 'S' - Storm Management 'P' - Storm Management "non-published global ERT" 'O' - Storm Management "onsite ERT" 'G' - Storm Management "published global ERT" D' - Storm Management "published global ERT delay" 'C' - User-entered (assumed to have been provided by the crew) 'I' - Initial default ERT 'M' - Storm Management ERT is further in the future then allowed
p_ out_crew_arrival_time	Out	DATE	The time when the crew arrived on location
p_ out_completion_time	Out	DATE	The time the event has been completed. This implies power restoration, the crew(s) are gone, and the event is completed in the Event Details window.
p_ out_restoration_time	Out	DATE	The time that power has been restored.
p_ out_case_note	Out	VARCHAR2	Comment
p_ out_status	Out	NUMBER	Condition status
p_ out_active	Out	VARCHAR2	Possible values are as follows: 'Y' - Outage Is Active 'N' - Outage Is Not Active
p_out_alias	Out	VARCHAR2	The device alias.
P_out_event_type	Out	VARCHAR2	Possible values are as follows: OUT NON MEET PLAN SWP
P_out_feeder_name	Out	VARCHAR2	The name of the feeder.
P_out_cause	Out	VARCHAR2	The cause of the outage if the SRS Rule useExternalCause is on.
P_out_num_calls	Out	NUMBER	The number of calls.
P_out_num_cust_out	Out	NUMBER	The number of customers out.
P_err_premise_id	Out	VARCHAR2
P_err_oracle_error	Out	VARCHAR2

SRSInput Testing Utility Command Line Options

SRSInput adds raw file incidents into JMService.

Usage

SRSinput [-ami][-ami_up] [-max number] [-ivr] [-time] [-blanksok] [-package number]

[-interval seconds] [-divide number] [-tilde] [-debug]

-input filename

Options/Arguments:

Option	Description
-ami	Submit AMI Last Gasp (Power Down) inputs for the meters specified by the -input parameter.
-ami_up	Submit AMI Power Up (Restore) inputs for the meters specified by the -input parameter.
-max number	Maximum number of calls to enter.
-ivr	Write calls to trouble_calls table for IVR adapter.
-time	Add input_time with current time.
-blanksok	Blank lines in call record are acceptable.
-package number	Number of calls to send to JMS at a time. Default: 10.
-interval seconds	Seconds to delay between calls.Defaults to 5. Also, it can take floating point values such as 2.5 to sleep 2 and a half seconds between call batches.
-divide number	Number to divide <WAIT> times by.
-tilde	Use the tilde in column 0 as a call separator.
-debug	Enable runtime debugging.
-input filename	Filename containing trouble call data. Required.

Note: If the -ivr option is not used, SRSinput sends calls to JMService via the C++ API.