public_notice_of_intent_submission_search_view

Columns

Column

Type

Size

Nulls

Auto

Default

Children

Parents

Comments

uuid

2147483647

√

null

applicant

varchar

2147483647

√

null

local_government_name

varchar

2147483647

√

null

configuration.name

Implied Constraint

R

file_number

varchar

2147483647

√

null

notice_of_intent_type_code

varchar

2147483647

√

null

date_submitted_to_alc

timestamptz

35,6

√

null

outcome

text

2147483647

√

null

last_update

timestamptz

35,6

√

null

status

jsonb

2147483647

√

null

Relationships

Close relationships within degrees of separation

View Definition

SELECT noi_sub.uuid,
    noi_sub.applicant,
    "localGovernment".name AS local_government_name,
    noi_sub.file_number,
    noi_sub.type_code AS notice_of_intent_type_code,
    noi.date_submitted_to_alc,
    decision_date.outcome,
    GREATEST(status_link.effective_date, decision_date.date) AS last_update,
    alcs.get_current_status_for_notice_of_intent_submission_by_uuid(noi_sub.uuid) AS status
   FROM ((((alcs.notice_of_intent_submission noi_sub
     JOIN alcs.notice_of_intent noi ON ((((noi.file_number)::text = (noi_sub.file_number)::text) AND (noi.hide_from_portal = false) AND (noi.audit_deleted_date_at IS NULL))))
     LEFT JOIN alcs.local_government "localGovernment" ON (((noi_sub.local_government_uuid = "localGovernment".uuid) AND ("localGovernment".audit_deleted_date_at IS NULL))))
     LEFT JOIN ( SELECT max(status_link_1.effective_date) AS effective_date,
            status_link_1.submission_uuid
           FROM alcs.notice_of_intent_submission_to_submission_status status_link_1
          WHERE (status_link_1.effective_date <= now())
          GROUP BY status_link_1.submission_uuid) status_link ON ((status_link.submission_uuid = noi_sub.uuid)))
     LEFT JOIN ( SELECT DISTINCT ON (decisions.notice_of_intentuuid) decisions.decisiondate AS date,
            decisions.outcome,
            decisions.dest_rank,
            decisions.notice_of_intentuuid AS notice_of_intent_uuid
           FROM ( SELECT decision.outcome_code AS outcome,
                    decision.date AS decisiondate,
                    decision.notice_of_intent_uuid AS notice_of_intentuuid,
                    rank() OVER (PARTITION BY decision.notice_of_intent_uuid ORDER BY decision.date DESC, decision.audit_created_at DESC) AS dest_rank
                   FROM alcs.notice_of_intent_decision decision
                  WHERE ((decision.is_draft = false) AND (decision.date <= now()) AND (decision.audit_deleted_date_at IS NULL))) decisions
          WHERE (decisions.dest_rank = 1)) decision_date ON ((decision_date.notice_of_intent_uuid = noi.uuid)))
  WHERE ((noi_sub.is_draft = false) AND ((noi.date_received_all_items IS NOT NULL) AND (noi.date_received_all_items <= now())) AND ((alcs.get_current_status_for_notice_of_intent_submission_by_uuid(noi_sub.uuid) ->> 'status_type_code'::text) <> 'CANC'::text) AND (noi_sub.audit_deleted_date_at IS NULL));

public_notice_of_intent_submission_search_view

Columns

Relationships

Close relationships within degrees of separation

View Definition

Possibly Referenced Tables/Views