complaint

-1 rows


Description

Initial information provided on a potential incident.

Columns

Column Type Size Nulls Auto Default Children Parents Comments
complaint_identifier varchar 20 null
action_taken.complaint_identifier Implied Constraint R
allegation_complaint.complaint_identifier FK_algtncmplt_complaint R
complaint_update.complaint_identifier complaint_update_fk R
gir_complaint.complaint_identifier FK_gircmplnt_complaint R
hwcr_complaint.complaint_identifier FK_hwcrcmplnt_complaint R
linked_complaint_xref.complaint_identifier FK_lnkcmplxref_complaint R
linked_complaint_xref.linked_complaint_identifier FK_lnkcmplxref_linked_complaint R
person_complaint_xref.complaint_identifier FK_person_complaint_xref__complaint_identifier R
staging_complaint.complaint_identifier Implied Constraint R
detail_text text 2147483647 √ null

Verbatim details of the complaint as recorded by the call centre or through the web form.
caller_name varchar 120 √ null

The name provided by the caller to the call centre or entered onto the web form.
caller_address varchar 120 √ null

The address provided by the caller to the call centre or entered onto the web form.
caller_email varchar 120 √ null

The email address provided by the caller to the call centre or entered onto the web form.
caller_phone_1 varchar 15 √ null

The primary phone number provided by the caller to the call centre or entered onto the web form.
caller_phone_2 varchar 15 √ null

An alternate phone number provided by the caller to the call centre or entered onto the web form.
caller_phone_3 varchar 15 √ null

An alternate phone number provided by the caller to the call centre or entered onto the web form.
location_summary_text varchar 120 √ null

A brief summary of the location of the complaint.
location_detailed_text varchar 4000 √ null

A more detailed description of the location of the complaint.
incident_utc_datetime timestamp 29,6 √ null

The date and time at which the complaint occurred.
incident_reported_utc_timestmp timestamp 29,6 √ null

The date and time at which the complaint was reported.
reported_by_other_text varchar 120 √ null

Provides a more detailed description when the referred by Agency is of type “OTHER”
create_user_id varchar 32 null

The id of the user that created the complaint.
create_utc_timestamp timestamp 29,6 null

The timestamp when the complaint was created. The timestamp is stored in UTC with no Offset.
update_user_id varchar 32 null

The id of the user that updated the complaint.
update_utc_timestamp timestamp 29,6 null

The timestamp when the complaint was updated. The timestamp is stored in UTC with no Offset.
reported_by_code varchar 10 √ null
reported_by_code.reported_by_code FK_complaint_reported_by_code R

A human readable code used to identify an agency. The agency that originally referred the complaint.
owned_by_agency_code varchar 10 √ null
agency_code.agency_code FK_complaint_owned_by_agencycode R

A human readable code used to identify an agency. The agency that currently owns the complaint.
complaint_status_code varchar 10 √ null
complaint_status_code.complaint_status_code FK_complaint_compntstscd R

A human readable code used to identify a complaint status.
geo_organization_unit_code varchar 10 √ null
geo_organization_unit_code.geo_organization_unit_code FK_complaint_geoorgutnd R

A human readable code used to identify a geographical organization unit. The finest known grain geographical organization unit where the complaint occurred.
location_geometry_point geometry 2147483647 √ null

The closest approximation to where the incident occurred. Stored as a geometric point using the EPSG:3005 Projected Coordinate System (BC Albers)
webeoc_identifier varchar 20 √ null

Unique Identifier from the webEOC source system to identify a complaint update. This is required as the natural key is not available in all webEOC APIs
comp_mthd_recv_cd_agcy_cd_xref_guid uuid 2147483647 √ null
comp_mthd_recv_cd_agcy_cd_xref.comp_mthd_recv_cd_agcy_cd_xref_guid complaint_comp_mthd_recv_cd_agcy_cd_xref_fk R

Methods in which the complaint was received. Examples include: DGIR forward, Direct email or phone call,Minister’s office,RAPP ,Referral
is_privacy_requested bpchar 1 √ 'U'::bpchar

flag to represent that the caller has asked for special care when handling their personal information
reference_number varchar 20 √ null

Allows users to link complaints to files in external systems. Currently labeled in the system as COORS reference number and initially only used for COORS linkages.

Indexes

Constraint Name Type Sort Column(s)
PK_complaint Primary key Asc complaint_identifier
IDX_adbfa452bdecec83d2daf17d18 Performance Asc location_geometry_point

Check Constraints

Constraint Name Constraint
complaint_is_privacy_requested ((is_privacy_requested = ANY (ARRAY['Y'::bpchar, 'N'::bpchar, 'U'::bpchar])))

Relationships

Close relationships within degrees of separation