Stored Procedure Parameters
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 trouble_calls.call_time%TYPE,
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 trouble_calls.meet_time%TYPE,
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 trouble_calls.appt_time%TYPE,
appt_range trouble_calls.appt_range%TYPE,
callback_flag trouble_calls.callback_request%TYPE,
callback_before_time trouble_calls.callback_time%TYPE,
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,
-- new/updated trouble call
p_call IN input_call_rec,
Description of the parameters of the PK_CCB.SUBMIT_CALL stored procedure.
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),
account_number VARCHAR2(30)
CREATE OR REPLACE TYPE location_search_obj AS OBJECT (
CREATE OR REPLACE TYPE fuzzy_search_obj AS OBJECT (
external_id VARCHAR2(200),
caller_name VARCHAR2(200),
caller_phone VARCHAR2(200)
CREATE OR REPLACE TYPE custom_search_obj AS OBJECT (
TYPE nms_cursor IS REF CURSOR;
p_cust IN customer_search_obj,
p_loc IN location_search_obj,
p_fuzzy IN fuzzy_search_obj,
p_custom IN custom_search_obj,
Description of the parameters of the PK_CCB.JOB_HISTORY stored procedure.
For each returned job the following information is included.
serv_point_id ces_customers.id%TYPE,
serv_point_addr ces_customers.address%TYPE,
event_idx jobs.event_idx%TYPE,
begin_time jobs.begin_time%TYPE,
est_rest_time jobs.est_rest_time%TYPE,
est_rest_time_source jobs.est_source%TYPE,
restore_time jobs.restore_time%TYPE,
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,
device_class jobs.devcls_name%TYPE,
trouble_code jobs.trouble_code%TYPE,
feeder_name jobs.feeder_name%TYPE,
description jobs.description%TYPE,
referral_group jobs.referral_group%TYPE,
last_update_time jobs.last_update_time%TYPE,
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.
p_cust IN customer_search_obj,
p_loc IN location_search_obj,
p_fuzzy IN fuzzy_search_obj,
p_custom IN custom_search_obj,
For each returned call the following information is included.
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 incidents.input_time%TYPE,
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,
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_sw_plans OUT nms_cursor,
Description of the parameters of the PK_CCB.SWITCHING_HISTORY stored procedure.
For each returned switching plan following information is included.
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 swman_sheet.start_date%TYPE,
end_date swman_sheet.finish_date%TYPE,
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,
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,
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
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 (i.e., the first 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.
|
|
|
|
|
|
|
|
|
The value is inserted as is.
|
|
|
|
|
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.
|
|
|
|
|
The possible values are as follows:
‘0’ - callback not requested
Defaults to ‘1’ if no value is supplied.
‘Y’ is translated to ‘1’.
‘N’ is translated to ‘0’.
|
|
|
|
|
Defaults to the database system date if no value is supplied
|
|
|
|
|
The value is inserted as is.
|
|
|
|
|
The value is inserted as is.
|
|
|
|
|
The value is inserted as is.
|
|
|
|
|
The value is inserted as is.
|
|
|
|
|
The value is inserted as is.
|
|
|
|
|
The value is inserted as is.
|
|
|
|
|
The value is inserted as is.
|
|
|
|
|
The value is inserted as is.
|
|
|
|
|
The value is inserted as is.
|
|
|
|
|
The value is inserted as is.
|
|
|
|
|
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
|
|
|
|
|
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
|
|
|
|
|
The value is inserted as is.
|
|
|
|
|
The value is inserted as is.
|
|
|
|
|
The value is inserted as is.
|
|
|
|
|
The value is inserted as is.
|
|
|
|
|
The value is inserted as is.
|
|
|
|
|
The value is inserted as is.
|
|
|
|
|
The value is inserted as is.
|
|
|
|
|
The value is inserted as is.
|
|
|
|
|
The value is inserted as is.
|
|
|
|
|
The value is inserted as is.
|
|
|
|
|
The value is inserted as is.
|
|
|
|
|
The value is inserted as is.
|
|
|
|
|
The value is inserted as is.
|
|
|
|
|
The value is inserted as is.
|
|
|
|
|
The value is inserted as is.
|
|
|
|
|
The value is inserted as is.
|
|
|
|
|
The value is inserted as is.
|
|
|
|
|
The value is inserted as is.
|
|
|
|
|
The value is inserted as is.
|
|
|
|
|
The value is inserted as is.
|
|
|
|
|
The value is inserted as is.
|
|
|
|
|
The value is inserted as is.
|
|
|
|
|
The value is inserted as is.
|
|
|
|
|
The value is inserted as is.
|
|
|
|
|
The value is inserted as is.
|
|
|
|
|
The value is inserted as is.
|
|
|
|
|
The value is inserted as is.
|
|
|
|
|
The value is inserted as is.
|
|
|
|
|
The value is inserted as is.
|
|
|
|
|
The value is inserted as is.
|
|
|
|
|
The value is inserted as is.
|
|
|
|
|
The value is inserted as is.
|
|
|
|
|
The value is inserted as is.
|
|
|
|
|
The value is inserted as is.
|
|
|
|
|
The value is inserted as is.
|
|
|
|
|
The value is inserted as is.
|
|
|
|
|
The value is inserted as is.
|
|
|
|
|
The value is inserted as is.
|
|
|
|
|
The value is inserted as is.
|
|
|
|
|
The value is inserted as is.
|
|
|
|
|
The value is inserted as is.
|
|
|
|
|
The value is inserted as is.
|
|
|
|
|
The erroneous premise ID input parameter
|
|
|
|
|
|
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.
Below are the fields of the CALLBACK_CURSOR. Take note that the CALLBACK_CURSOR is defined as a weakly typed cursor.
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.
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).
|
Below are details about each parameter of the pr_customer_event_details stored procedure.