recreation_spatial_submission

-1 rows


Description

Tracks spatial submission requests sent to SNCSC and their adjudication outcomes.

Columns

Column Type Size Nulls Auto Default Children Parents Comments
application_guid uuid 2147483647 gen_random_uuid()
recreation_spatial_submission_history.application_guid Implied Constraint R

Primary GUID for the submission record in RST.
submission_id varchar 50 √ (nextval('rst.recreation_spatial_submission_submission_id_seq'::regclass))::character varying(50)
recreation_exhibit_a_doc.submission_id fk_recreation_exhibit_a_doc_submission_id R

Client-facing submission identifier (auto-generated incremental value when omitted).
tenure_id varchar 20 null
recreation_resource.rec_resource_id recreation_spatial_submission_tenure_id_fkey R

Tenure identifier (FK to rst.recreation_resource.rec_resource_id).
status_request_guid varchar 50 √ null

SNCSC internal record key returned after submission creation.
status_request_type_code varchar 3 null

Request geometry type: RL (Linear) or RPO (Polygonal).
status varchar 3 'PND'::character varying

Submission lifecycle status: PND, CLR, APP, REJ, or FAI.
purpose_code varchar 1 null

Submission purpose: N (New), A (Amend), or R (Retire).
recreation_resource_type varchar 3 √ null
recreation_resource_type_code.rec_resource_type_code recreation_spatial_submission_recreation_resource_type_fkey R

Recreation resource type code associated to the submission.
submission_date date 13 null

Date the spatial request was sent to SNCSC.
org_unit_code varchar 6 null

Natural resource organization unit code associated to the submission context.
adjudication_ind bool 1 false

Indicates whether a final adjudication decision has been made (true only for APP or REJ).
adjudicated_by varchar 30 √ null

IDIR of the adjudicator.
adjudication_date timestamp 29,6 √ null

Date and time of adjudication.
adjudication_comment text 2147483647 √ null

Adjudication notes, including reject reason where applicable.
adjudication_report_document_uri varchar 2048 √ null

Document URI for the adjudication report/exhibit output.
decision varchar 7 √ null

Adjudication decision value: APPROVE or REJECT.
updated_at timestamp 29,6 √ now()
updated_by text 2147483647 √ null
created_at timestamp 29,6 √ now()
created_by text 2147483647 √ null
sys_period tstzrange 2147483647 tstzrange(CURRENT_TIMESTAMP, NULL::timestamp with time zone)

Indexes

Constraint Name Type Sort Column(s)
recreation_spatial_submission_pkey Primary key Asc application_guid
idx_recreation_spatial_submission_org_unit_code Performance Asc org_unit_code
idx_recreation_spatial_submission_status Performance Asc status
idx_recreation_spatial_submission_submission_date Performance Asc submission_date
idx_recreation_spatial_submission_tenure_id Performance Asc tenure_id
recreation_spatial_submission_status_request_guid_key Must be unique Asc status_request_guid
recreation_spatial_submission_submission_id_key Must be unique Asc submission_id

Check Constraints

Constraint Name Constraint
chk_spatial_submission_status_request_type ((status_request_type_code)::text = ANY ((ARRAY['RL'::character varying, 'RPO'::character varying])::text[]))
chk_spatial_submission_adjudication (((adjudication_ind = true) AND ((status)::text = ANY ((ARRAY['APP'::character varying, 'REJ'::character varying])::text[])) AND (adjudicated_by IS NOT NULL) AND (adjudication_date IS NOT NULL)) OR ((adjudication_ind = false) AND ((status)::text = ANY ((ARRAY['PND'::character varying, 'CLR'::character varying, 'FAI'::character varying])::text[])) AND (adjudicated_by IS NULL) AND (adjudication_date IS NULL)))
chk_spatial_submission_decision (((decision)::text = ANY ((ARRAY['APPROVE'::character varying, 'REJECT'::character varying])::text[])) OR (decision IS NULL))
chk_spatial_submission_status_business_rules ((((status)::text = 'CLR'::text) AND (decision IS NULL) AND (adjudication_comment IS NULL) AND (adjudication_report_document_uri IS NULL)) OR (((status)::text = 'APP'::text) AND ((decision)::text = 'APPROVE'::text) AND (adjudication_report_document_uri IS NOT NULL)) OR (((status)::text = 'REJ'::text) AND ((decision)::text = 'REJECT'::text) AND (adjudication_comment IS NOT NULL)) OR ((status)::text = ANY ((ARRAY['PND'::character varying, 'FAI'::character varying])::text[])))
chk_spatial_submission_purpose ((purpose_code)::text = ANY ((ARRAY['N'::character varying, 'A'::character varying, 'R'::character varying])::text[]))
chk_spatial_submission_status ((status)::text = ANY ((ARRAY['CLR'::character varying, 'APP'::character varying, 'REJ'::character varying, 'PND'::character varying, 'FAI'::character varying])::text[]))

Relationships

Close relationships within degrees of separation