PSC_WORKFLOWSTATUS_DURATION_V

Details

• Schema: FUSION

• Object owner: PSC_PER

• Object type: VIEW

Columns

Name
RECORD_KEY OPA_PROCESS_ID IPC_SUB PTI_IPC PTI_PLR PTI_INS

Query

SQL_Statement

WITH JOIN_VO AS ( SELECT a.record_key, COALESCE(a.opa_process_id, x.opa_process_id) AS opa_process_id, a.oic_process_id, a.lnp_rec_status, a.resource_name, a.creation_date, r.RECORD_TYPE_ID, s.system_status FROM psc_ts_wf_activities a LEFT JOIN psc_ts_process_gen_xref x ON a.oic_process_id = x.psc_xref_key LEFT JOIN psc_lnp_record_type_b r ON a.resource_name = r.RECORD_TYPE_ID INNER JOIN psc_lnp_record_status_b s ON a.lnp_rec_status = s.record_status AND s.classification = 'PMT' AND s.system_status IN ('SUB', 'IPC', 'PTI', 'PLR', 'INS') ), Filtered AS ( SELECT j.record_key, j.opa_process_id, j.system_status, j.creation_date FROM JOIN_VO j WHERE j.resource_name = 'processes' OR j.resource_name='publicSectorRecordQueries' OR j.resource_name = j.RECORD_TYPE_ID ), Pivoted AS ( SELECT record_key, opa_process_id, MIN(CASE WHEN system_status = 'SUB' THEN creation_date END) AS submitted_time, MIN(CASE WHEN system_status = 'IPC' THEN creation_date END) AS in_process_time, MIN(CASE WHEN system_status = 'PTI' THEN creation_date END) AS permit_issued_time, MIN(CASE WHEN system_status = 'PLR' THEN creation_date END) AS plan_review_time, MIN(CASE WHEN system_status = 'INS' THEN creation_date END) AS inspection_time FROM Filtered GROUP BY record_key, opa_process_id ), Durations as (SELECT record_key, opa_process_id, CASE WHEN submitted_time IS NOT NULL AND in_process_time IS NOT NULL THEN (EXTRACT(DAY FROM (in_process_time - submitted_time)) * 86400) + (EXTRACT(HOUR FROM (in_process_time - submitted_time)) * 3600) + (EXTRACT(MINUTE FROM (in_process_time - submitted_time)) * 60) + EXTRACT(SECOND FROM (in_process_time - submitted_time)) ELSE NULL END AS IPC_SUB, CASE WHEN permit_issued_time IS NOT NULL AND in_process_time IS NOT NULL THEN (EXTRACT(DAY FROM (permit_issued_time - in_process_time)) * 86400) + (EXTRACT(HOUR FROM (permit_issued_time - in_process_time)) * 3600) + (EXTRACT(MINUTE FROM (permit_issued_time - in_process_time)) * 60) + EXTRACT(SECOND FROM (permit_issued_time - in_process_time)) ELSE NULL END AS PTI_IPC, CASE WHEN permit_issued_time IS NOT NULL AND plan_review_time IS NOT NULL THEN (EXTRACT(DAY FROM (permit_issued_time - plan_review_time)) * 86400) + (EXTRACT(HOUR FROM (permit_issued_time - plan_review_time)) * 3600) + (EXTRACT(MINUTE FROM (permit_issued_time - plan_review_time)) * 60) + EXTRACT(SECOND FROM (permit_issued_time - plan_review_time)) ELSE NULL END AS PTI_PLR, CASE WHEN permit_issued_time IS NOT NULL AND inspection_time IS NOT NULL THEN (EXTRACT(DAY FROM ( inspection_time - permit_issued_time )) * 86400) + (EXTRACT(HOUR FROM (inspection_time - permit_issued_time)) * 3600) + (EXTRACT(MINUTE FROM (inspection_time - permit_issued_time)) * 60) + EXTRACT(SECOND FROM (inspection_time - permit_issued_time)) ELSE NULL END AS PTI_INS FROM Pivoted) SELECT c.record_key, c.opa_process_id, c.IPC_SUB, c.PTI_IPC, c.PTI_PLR, c.PTI_INS FROM Durations c ORDER BY c.record_key, c.opa_process_id