Req Key Values | Meaning | Comment |
N | Not required | Not needed for standard ces_customers table. |
C | Configured in standard ces_customers table. | Not all columns referenced in the ces_customers table are required for a given implementation - inclusion of some columns can be project-specific. |
Y | Required | Used in standard ces_customers table – still may not be 100% required. Actual requirements are generally project specific. |
Req | Column Name | Data Type | Description |
---|---|---|---|
Y,C | cust_id | NUMBER NOT NULL, | Primary key – may be generated. |
N | cust_account_number | VARCHAR2(30) NOT NULL | Customer account number. |
N | cust_billing_account | VARCHAR2(13) NULL | Customer billing account number. |
Y,C | cust_name | VARCHAR2(90) NULL | Name of the customer; concatenation of last, first and middle names, or business name. |
N | cust_last_name | VARCHAR2(30) NULL | Last name. |
N | cust_first_name | VARCHAR2(30) NULL | First name. Typically, this is only populated for residential customers. |
N | cust_middle_name | VARCHAR2(30) NULL | Middle name or initial. |
Y,C | cust_home_ac | NUMBER(3) NULL | Phone area code for the home phone. |
Y,C | cust_home_phone | NUMBER(7) NULL | Phone number for the home phone. |
N | cust_day_ac | NUMBER(3) NULL | Phone area code for the work phone. |
N | cust_day_phone | NUMBER(7) NULL | Phone number for the work phone. |
N | cust_day_phone_ex | NUMBER(7) NULL | Typically, day phone numbers are related to customers’ work phone numbers, which generally include extensions. |
N | cust_bill_addr_1 | VARCHAR2(50) NULL | Street address of the billing address. Note that billing address fields are usually populated only if different from the address held in the cu_service_point table. |
N | cust_bill_addr_2 | VARCHAR2(50) NULL | Second line, if necessary, of street address of the billing address. |
N | cust_bill_addr_3 | VARCHAR2(50) NULL | Third line, if necessary, of street address of the billing address. |
N | cust_bill_addr_4 | VARCHAR2(50) NULL | Fourth line, if necessary, of street address of the billing address. |
N | cust_bill_city | VARCHAR2(30) NULL, | City of the billing address. |
N | cust_bill_state | VARCHAR2(30) NULL | State of the billing address. |
N | cust_bill_postcode_1 | VARCHAR2(10) NULL | First 5 zip code numbers for US. |
N | cust_bill_postcode_2 | VARCHAR2(10) NULL | Second 4 zip code numbers for US. |
C | cust_name_initials | VARCHAR2(3) NULL | The customer initials. Possibly used for certain soundex type searching if a customer wants to enable it - not often. Not necessary. |
N | cust_comment | VARCHAR2(255)NULL | General field provided to support additional information about the customer, such as 30ft ladder, assult-case, crit-pmp-station, etc. |
N | cust_user_def_1 | VARCHAR2(255) NULL | These user-defined fields support the inclusion of other desired data not covered in the core fields. These fields can be extracted for project specific reporting. |
N | cust_user_def_2 | VARCHAR2(255) NULL | |
N | cust_user_def_3 | VARCHAR2(255) NULL | |
N | cust_user_def_4 | VARCHAR2(255) NULL | |
N | last_update_time | DATE | Time of last update for record. Generally, set internally when a record is updated - not via external CIS. |
Y | birth | DATE | Time the record was activated |
Y | death | DATE | Time the record was deactivated |
Y | to_die | VARCHAR(1) | (Internal use.) Used to flag whether the record is being given a death time. |
Req | Column Name | Data Type | Description |
---|---|---|---|
Y,C | serv_loc_id | NUMBER NOT NULL | Primary key – may be generated. |
N | serv_type | VARCHAR2(2) NULL | The type of service at this location. (electrical or gas). Only necessary for utilities that support multiple service types. |
N | serv_status | VARCHAR2(50) NULL | Electrical service status of the service location. For example: INA – Inactive ACT – Active PDI – Pending Disconnect Can be used to coordinate business processes around how to handle customer disconnects (for example, update the day before). Each project needs to discuss these. |
Y,C | serv_account_number | VARCHAR2(30) NOT NULL | The service account number which will be used for call entry purposes, and the account number used in createIncident XML. |
Y,C | serv_revenue_class | VARCHAR2(30) NULL | Revenue class for the service location. |
N | serv_load_mgmt | NUMBER NULL | Binary - whether or not there is load management at this Service Location |
Y,C | serv_concat_address | VARCHAR2(200) NULL | Concatenated address of the service address 1, 2, 3, and 4. |
N | serv_special_needs | VARCHAR2(1) NULL | Identifies any special needs of the customer. |
N | serv_priority | VARCHAR2(32) NULL | Mapped to ces_customers.priority. This defines the meaningful customer type value the utility uses internally. This value will be displayed on troubleInfo as well. |
N | serv_addr_1 | VARCHAR2(50) NULL | First line of street address of the service address. |
N | serv_addr_2 | VARCHAR2(50) NULL | Second line, if necessary, of street address of the service address. |
N | serv_addr_3 | VARCHAR2(50) NULL | Third line, if necessary, of street address of the service address. |
N | serv_addr_4 | VARCHAR2 (50) NULL | Third line, if necessary, of street address of the service address. |
N | serv_city | VARCHAR2(25) NULL | City of the service location. |
N | serv_state | VARCHAR2(25) NULL | State of the service location. |
Y,C | serv_city_state | VARCHAR2(50) NULL | This field contains the data that will appear in the ces_customers.CITY_STATE field. |
Y,C | serv_postcode_1 | VARCHAR2(10) NULL | First 5 Zip Code numbers for US. |
N | serv_postcode_2 | VARCHAR2(10) NULL | Second 4 Zip Code numbers for US. |
N | serv_user_geog_1 | VARCHAR2(25) NULL | User geo codes typically used for political areas, such as counties, tax districts, etc. |
N | serv_user_geog_2 | VARCHAR2(25) NULL | |
Y,C | serv_town | VARCHAR2(3) NULL | The town or county for the customer. |
Y,C | serv_str_block | VARCHAR2(20) NULL | Block number - used in searches. |
N | serv_str_pfix | VARCHAR2(10) NULL | The 'R' in R 321 Rolling Rd (R rear, F front, A adjacent, etc.) |
Y,C | serv_str_struc | VARCHAR2(20) NULL | Structure relates to apartments, units, piers, docks, warehouse, slip, etc. |
N | serv_str_name | VARCHAR2(30) NULL | Name of the street (Main Street). |
N | serv_str_cdl_dir | VARCHAR2(10) NULL | Cardinal direction (N, S, E, W). |
N | serv_str_sfix | VARCHAR2(10) NULL | ST, PKY, PLC, DR, RD, AVE, etc. |
Y,C | serv_lot | VARCHAR2(10) NULL | Lot number – used in searches. |
Y,C | serv_apt | VARCHAR2(8) NULL | Apartment number. |
N | serv_elec_addr | VARCHAR2(50) NULL | Elec address used in searches. |
N | serv_sic | VARCHAR2(8) NULL | Standard Industrial Code. |
N | serv_comment | VARCHAR2(255) NULL | General comment about the service location. |
Y,C | serv_cumulative_priority | NUMBER NULL | Summation of priority codes for this location. |
Y,C | serv_life_support | NUMBER NULL | Indicates if this is a life-support customer. |
Y,C | serv_a_priority | NUMBER NULL | A customer defined flag, 0 or 1 |
Y,C | serv_b_priority | NUMBER NULL | B customer defined flag, 0 or 1 |
Y,C | serv_c_priority | NUMBER NULL | C customer defined flag, 0 or 1 – often emergency customers. |
Y,C | serv_d_priority | NUMBER NULL | D customer defined flag, 0 or 1 – often medical customers. |
Y,C | serv_e_priority | NUMBER NULL | E customer defined flag, 0 or 1 – often entertainment customers. |
Y,C | serv_f_priority | NUMBER NULL | F customer defined flag, 0 or 1 – often fire customers. |
Y,C | serv_g_priority | NUMBER NULL | G customer defined flag, 0 or 1 – often government customers. |
Y,C | serv_h_priority | NUMBER NULL | H customer defined flag, 0 or 1 – often hospital customers. |
Y,C | serv_i_priority | NUMBER NULL | I customer defined flag, 0 or 1 |
Y,C | serv_j_priority | NUMBER NULL | J customer defined flag, 0 or 1 |
Y,C | serv_k_priority | NUMBER NULL | K customer defined flag, 0 or 1 – often keycustomers. |
Y,C | serv_l_priority | NUMBER NULL | L customer defined flag, 0 or 1 – often life support customers. |
Y,C | serv_m_priority | NUMBER NULL | M customer defined flag, 0 or 1 – often manufacturing customers. |
Y,C | serv_n_priority | NUMBER NULL | N customer defined flag, 0 or 1 – often nursing home customers. |
Y,C | serv_o_priority | NUMBER NULL | O customer defined flag, 0 or 1 |
Y,C | serv_p_priority | NUMBER NULL | P customer defined flag, 0 or 1 – often police customers. |
Y,C | serv_q_priority | NUMBER NULL | Q customer defined flag, 0 or 1 |
Y,C | serv_r_priority | NUMBER NULL | R customer defined flag, 0 or 1 |
Y,C | serv_s_priority | NUMBER NULL | S customer defined flag, 0 or 1 – often sensitive customers. |
Y,C | serv_t_priority | NUMBER NULL | T customer defined flag, 0 or 1 – often transportation customers. |
Y,C | serv_u_priority | NUMBER NULL | U customer defined flag, 0 or 1 |
Y,C | serv_v_priority | NUMBER NULL | V customer defined flag, 0 or 1 |
Y,C | serv_w_priority | NUMBER NULL | W customer defined flag, 0 or 1 |
Y,C | serv_x_priority | NUMBER NULL | X customer defined flag, 0 or 1 |
Y,C | serv_y_priority | NUMBER NULL | Y customer defined flag, 0 or 1 |
Y,C | serv_z_priority | NUMBER NULL | Z customer defined flag, 0 or 1 – often 911 customers. |
Y,C | serv_map_loc_x | NUMBER NULL | GPS lat/long or other mapping coordinates. |
Y,C | serv_map_loc_y | NUMBER NULL | |
N | serv_user_def_1 | VARCHAR2(255) NULL | These user-defined fields support other desired data not covered in the core fields. These fields can be extracted for project-specific reporting purposes. |
N | serv_user_def_2 | VARCHAR2(255) NULL | |
N | serv_user_def_3 | VARCHAR2(255) NULL | |
N | serv_user_def_4 | VARCHAR2(255) NULL | |
N | last_update_time | DATE | Time of last update for record. |
Y | birth | DATE | Time the record was activated |
Y | death | DATE | Time the record was deactivated |
Y | to_die | VARCHAR(1) | (Internal use.) Used to flag whether the record is being given a death time. |
Req | Column Name | Data Type | Description |
Y,C | meter_id | NUMBER NOT NULL | Primary key – may be generated. |
Y,C | meter_no | VARCHAR2(20) NOT NULL | Meter number. |
N | meter_serial_number | VARCHAR2(20) NULL | Serial number on the meter. |
N | meter_type | VARCHAR2(20) NULL | Type of meter (gas, electric, water, etc.). |
N | meter_manufacturer | VARCHAR2(20) NULL | Manufacturer of the meter. |
N | meter_phases | VARCHAR2(1) NULL | Phase(s) connected to the meter (IE 1, 2, or 3). |
N | meter_rate_code | VARCHAR2(65) NULL | Rate code for the meter. |
N | meter_user_def_1 | VARCHAR2(255) NULL | These user-defined fields support other desired data not covered in the core fields.These fields can be extracted for project-specific reporting purposes. |
N | meter_user_def_2 | VARCHAR2(255) NULL | |
N | meter_user_def_3 | VARCHAR2(255) NULL | |
N | meter_user_def_4 | VARCHAR2(255) NULL | |
N | last_update_time | DATE | Time of last update for record. |
Y | birth | DATE | Time the record was activated |
Y | death | DATE | Time the record was deactivated |
Y | to_die | VARCHAR(1) | (Internal use.) Used to flag whether the record is being given a death time. |
cu_account_type | ||
Column Name | Data Type | Description |
acctyp_account_type | VARCHAR2(10) NOT NULL | Electric, Gas, Propane, Appliance Repair, etc. |
acctyp_user_def_1 | VARCHAR2(255) NULL | These user-defined fields support other desired data not covered in the core fields. These fields can be extracted for project-specific reporting purposes. |
acctyp_user_def_2 | VARCHAR2(255) NULL | |
acctyp_user_def_3 | VARCHAR2(255) NULL | |
acctyp_user_def_4 | VARCHAR2(255) NULL |
Req | Column Name | Data Type | Description |
---|---|---|---|
Y,C | serv_point_id | VARCHAR2(64) NOT NULL | Primary key. If the CIS cannot provide a unique value, use a generated key (for example, by combining cust_id, serv_loc_id and meter_id columns). This is used for CIS-to-NMS integration. For Customer Care & Billing (CC&B) integration in Oracle Utilities Network Management System 1.10, this is the CC&B Service Point Id. (See below for related info on ces_customers). |
Y,C | cust_id | NUMBER NOT NULL | Foreign key ref to the cu_customers table. |
Y,C | serv_loc_id | NUMBER NOT NULL | Foreign key ref to the cu_service_locations table. |
Y,C | meter_id | NUMBER NOT NULL | Foreign key ref to the cu_meters table. |
Y,C | device_id | VARCHAR2(25) NOT NULL | Foreign key ref to the supply_nodes table. This field is critical and necessary, as it ties Oracle Utilities Network Management System to the CIS. |
N | feeder_id | VARCHAR2(10) NULL | Foreign key ref to the supply nodes table. Note this field is non-critical and generally not necessary. |
Y,C | active_fl | VARCHAR2(1) NOT NULL | Identifies currently active records. Generally, this is always ‘Y’ as there is little provision or need for inactive records in the system. Inactive records are generally removed from this table. |
N | create_dttm | DATE NOT NULL, | Timestamp for the record's creation. |
Y,C | account_type | VARCHAR2(10) NOT NULL | Foreign key to the cu_account_type table. |
N | last_update_time | DATE | Time of last update. |
Y | birth | DATE | Time the record was activated |
Y | death | DATE | Time the record was deactivated |
Y | to_die | VARCHAR(1) | (Internal use.) Used to flag whether the record is being given a death time. |
Req | Column Name | Data Type | Description |
Y,C | der_id | NUMBER NOT NULL | Primary key – may be generated. |
Y,C | der_name | VARCHAR2(80) NOT NULL | Name of the DER. |
Y,C | meter_id | NUMBER NOT NULL | The corresponding ID of the connected meter. |
N | der_type | VARCHAR2(30) NULL | Technology type for the DER (PV, wind, etc.). |
N | der_phases | VARCHAR2(1) NULL | DER phasing. |
N | rated_size | VARCHAR2(30) NULL | The size of the DER in kVA. |
N | nominal_voltage | NUMBER NULL | The nominal voltage value of the DER. |
N | der_user_def_1 | VARCHAR2(255) NULL | These user-defined fields support other desired data not covered in the core fields.These fields can be extracted for project-specific reporting purposes. |
N | der_user_def_2 | VARCHAR2(255) NULL | |
N | der_user_def_3 | VARCHAR2(255) NULL | |
N | der_user_def_4 | VARCHAR2(255) NULL | |
N | last_update_time | DATE | Time of last update for record. |
Y | birth | DATE | Time the record was activated |
Y | death | DATE | Time the record was deactivated |
Y | to_die | VARCHAR(1) | (Internal use.) Used to flag whether the record is being given a death time. |