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 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,
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.
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:
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.
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 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,
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 jobs.last_update_time%TYPE,
udf1 udf_field,
udf2 udf_field,
udf3 udf_field,
udf4 udf_field,
udf5 udf_field
);
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:
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 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,
udf1 udf_field,
udf2 udf_field,
udf3 udf_field,
udf4 udf_field,
udf5 udf_field
);
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.
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 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,
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
);
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
);
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
);
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
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.
Parameters
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
Note: Refer to the Data Flow Steps of the Callback Requests Data Flow on how the TROUBLE_CALLBACKS table is populated.
Parameter
Cursor Definition
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
Note: Refer to the Data Flow Steps of the Callback Response Data Flow on how the TROUBLE_CALLBACKS table is populated.
Parameters
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.
Parameters
Below are details about each parameter of the pr_customer_event_details stored procedure.