oats_issues

-1 rows


Description

An INQUIRY is a specific type of ISSUE, where someone poses a question to the Commission in regards to land use and the ALR. The Commission will reference this information if there is an ALR APPLICATION or COMPLAINT within the vicinity. A COMPLAINT is a specific type of ISSUE that warrants further investigation and may lead to an enforcement action. If further enforcement action is warranted, the typical course is: 1. Issue a Stop Work Order (SWO). The property owner may request this order be reviewed with the Chief Executive Officer (CEO). 2. The ALC staff will make recommendations to the CEO as to whether further enforcement action should be applied, in the form of fines and/or land remediation. 3. The CEO may then impose a fine and/or land remediation. An ISSUE is either an INQUIRY or COMPLAINT regarding land use within the jurisdiction of the ALR.

Columns

Column Type Size Nulls Auto Default Children Parents Comments
issue_id int8 19 null
oats_complaint_appeals.issue_id oats_cmap_issu_fk R
oats_documents.issue_id oats_docu_issu_fk R
oats_issue_notes.issue_id oats_isnt_issu_fk R
oats_stop_work_order_rcpnts.issue_id oats_swor_issu_fk R
oats_subject_properties.issue_id oats_sbjp_issu_fk R

System generated sequence number to uniquely identify an ISSUE
issu_type varchar 10 null
legacy_issue_nbr varchar 10 √ null

LEGACY ISSUE NBR is the characters used to identify an ISSUE in the legacy ATS application system, where NBR is an abbreviation for number. For example: 10011-1 or 20200
filed_by_pog_id int8 19 null
oats_person_organizations.person_organization_id oats_issu_pog_fk2 R

PERSON ORGANIZATION ID is the system generated unique identifier for PERSON ORGANIZATION.
local_gov_pog_id int8 19 null
oats_person_organizations.person_organization_id oats_issu_pog_fk R

PERSON ORGANIZATION ID is the system generated unique identifier for PERSON ORGANIZATION.
received_date timestamp 29,6 null

RECEIVED DATE is a time the ISSUE was received by the Commission.
description varchar 4000 null

DESCRIPTION isan attribute further extending the definition of the ISSUE.
inquiry_code varchar 10 √ null
oats_inquiry_codes.inquiry_code oats_issu_inqc_fk R

User supplied value to uniquely identify an ISSUE CODE
complaint_code varchar 10 √ null
oats_complaint_codes.complaint_code oats_issu_cmpc_fk R

User supplied value to uniquely identify an COMPLAINT CODE
swo_issued_by varchar 50 √ null

Description of who issued the stop work order
swo_issued_date timestamp 29,6 √ null

Date the stop work order was issued.
swo_terms varchar 4000 √ null

The provisions of the stop work order
swo_review_requested_date timestamp 29,6 √ null

Date the property owner requested a stop work order review with the Chief Executive Officer (CEO)
swo_ceo_review_date timestamp 29,6 √ null

Date the Chief Executive Officer (CEO) completed the review of the stop work order. This can only be supplied if a SWO REVIEW REQUESTED DATE exists.
swo_ceo_review_description varchar 4000 √ null

Description of the review conducted by the Chief Executive Officer (CEO). This can only be supplied if a SWO REVIEW REQUESTED DATE exists.
swo_ceo_review_result varchar 4000 √ null

Description of the Chief Executive Officer (CEO) results from the review. This can only be supplied if a SWO REVIEW REQUESTED DATE exists.
swo_complied_ind varchar 1 √ null

Indicates whether or not the property owner has complied with the stop work order or not.
recommend_fine_amt float8 17,17 √ null

The amount of a fine the ALC staff recommended to be imposed on the property owner.
recommend_land_remediation_ind varchar 1 √ null

Indicates whether or on the ALC staff recommends remediation to be completed on the land.
ceo_hearing_date timestamp 29,6 √ null

Date the Chief Executive Officer conducted a hearing to determine if further enforcement action (fines and/or land remediation) should be taken.
ceo_decision_date timestamp 29,6 √ null

Date the Chief Executive Officer rendered a decision if further enforcement action (fines and/or land remediation) will be taken.
fine_amt float8 17,17 √ null

The fine amount the property owner must pay.
fine_due_date timestamp 29,6 √ null

Date the fine is due by. This must be provided if a FINE AMT is imposed, or null if not.
fine_received_date timestamp 29,6 √ null

Date the fine was received by the ALC. This can only be provided if a FINE AMT is imposed.
remediation_required_ind varchar 1 √ null

Indicates whether or not the property owner must remediate the property or not
remediation_due_date timestamp 29,6 √ null

Date the remediation must be completed. Must be supplied if REMEDIATION REQUIRED IND is ‘Y’, otherwise must be null.
remediation_completion_date timestamp 29,6 √ null

Date the remediation is considered complete. Can only be supplied if REMEDIATION REQUIRED IND is ‘Y’, otherwise must always be null.
who_created varchar 32 null

WHO CREATED is the IDIR or Oracle userid of the person who created the record.
when_created timestamp 29,6 null

WHEN CREATED is the date/time that the record was created.
who_updated varchar 32 √ null

WHO UPDATED is the IDIR or Oracle userid of the person who last updated the record.
when_updated timestamp 29,6 √ null

WHEN UPDATED is the date/time that the record was last updated.
revision_count int8 19 0

REVISION COUNT is used by Hibernate to control optimistic locking

Indexes

Constraint Name Type Sort Column(s)
oats_issues_pkey Primary key Asc issue_id
oats_issu_cmpc_fk_i Performance Asc complaint_code
oats_issu_inqc_fk_i Performance Asc inquiry_code
oats_issu_pog_fk2_i Performance Asc filed_by_pog_id
oats_issu_pog_fk_i Performance Asc local_gov_pog_id

Check Constraints

Constraint Name Constraint
avcon_1238100890_remed_000 ((remediation_required_ind)::text = ANY ((ARRAY['Y'::character varying, 'N'::character varying])::text[]))
oats_issu_chk ((((issu_type)::text = 'CMP'::text) AND (inquiry_code IS NULL) AND (complaint_code IS NOT NULL)) OR (((issu_type)::text = 'INQ'::text) AND (inquiry_code IS NOT NULL) AND (complaint_code IS NULL) AND (swo_issued_by IS NULL) AND (swo_issued_by IS NULL) AND (swo_issued_date IS NULL) AND (swo_terms IS NULL) AND (swo_review_requested_date IS NULL) AND (swo_ceo_review_date IS NULL) AND (swo_ceo_review_description IS NULL) AND (swo_ceo_review_result IS NULL) AND (swo_complied_ind IS NULL) AND (recommend_fine_amt IS NULL) AND (recommend_land_remediation_ind IS NULL) AND (ceo_hearing_date IS NULL) AND (ceo_decision_date IS NULL) AND (fine_amt IS NULL) AND (fine_due_date IS NULL) AND (fine_received_date IS NULL) AND (remediation_required_ind IS NULL) AND (remediation_due_date IS NULL) AND (remediation_completion_date IS NULL)))
avcon_1238100890_issu__000 ((issu_type)::text = ANY ((ARRAY['INQ'::character varying, 'CMP'::character varying])::text[]))
avcon_1238100890_recom_000 ((recommend_land_remediation_ind)::text = ANY ((ARRAY['Y'::character varying, 'N'::character varying])::text[]))
avcon_1238100890_swo_c_000 ((swo_complied_ind)::text = ANY ((ARRAY['Y'::character varying, 'N'::character varying])::text[]))

Relationships

Close relationships within degrees of separation