SQL> SQL> /****************************************************************************** DOC> * FOR USE ON THE SERVER STCM DATABASE * DOC> *----------------------------------------------------------------------------* DOC> * PURPOSE: * DOC> * Create result tables to be used by portal reports. * DOC> * * DOC> * EXECUTE THIS AS STCM * DOC> * * DOC> * Author: Rajitha Kantamneni DOC> * * DOC> * Date: July 5, 2006 * DOC> * * DOC> * Note: Took 4 minutes, 35 seconds to execute this script on FRSTPNT. * DOC> * * DOC> ******************************************************************************/ SQL> SELECT systimestamp FROM dual; 07-JUL-06 05.34.30.847348 PM -04:00 1 row selected. SQL> SQL> /******************************************************************************* DOC> * drop all of the result tables DOC> *******************************************************************************/ SQL> -- not in production SQL> DROP TABLE stcm.result_dist_ofc_county_facs; Table dropped. SQL> -- not in production SQL> DROP TABLE stcm.result_facility_activities; Table dropped. SQL> SQL> DROP TABLE stcm.result_facility_processes; Table dropped. SQL> SQL> --DROP TABLE stcm.result_fac_proc_violations; SQL> SQL> --DROP TABLE stcm.result_ncl_proc_activities; SQL> SQL> --DROP TABLE stcm.result_unresolved_violations; SQL> SQL> --DROP TABLE stcm.result_wl_proc_activities; SQL> SQL> DROP TABLE stcm.result_violations; Table dropped. SQL> SQL> SQL> SQL> /******************************************************************************* DOC> * result_facility_processes DOC> *******************************************************************************/ SQL> -- took about 90 seconds on TEST9206 SQL> CREATE TABLE stcm.result_facility_processes 2 TABLESPACE STCM_DATA 3 STORAGE 4 ( 5 INITIAL 120000K 6 NEXT 12000K 7 PCTINCREASE 0 8 MINEXTENTS 1 9 MAXEXTENTS 505 10 ) 11 AS 12 SELECT STCM.TO_NUMBER_IF_NUMERIC(pa.VALUE) AS facility_id, 13 p.process_id, 14 p.prcsdf_process_def_id, 15 p.prstcd_process_status_id, 16 p.description, 17 p.status_date, 18 p.initiated_date, 19 p.closed_date, 20 p.created_username, 21 p.created_timestamp, 22 p.username, 23 p.time_stamp, 24 p.NAME, 25 p.due_date, 26 p.prtpcd_process_type_id, 27 p.rstcd_result_id, 28 p.ordinal, 29 p.opened_date, 30 p.initiated_user_id, 31 p.opened_user_id, 32 p.closed_user_id 33 FROM ewoc.processes p, 34 ewoc.process_attributes pa 35 WHERE pa.prcs_process_id = p.process_id 36 AND pa.pradf_process_attribute_def_id = 10; Table created. SQL> SQL> /******************************************************************************** DOC> * need indexes on the following columns for the result_facility_processes table: DOC> * process_id DOC> * facility_id DOC> ********************************************************************************/ SQL> -- took 8 seconds on TEST9206 SQL> CREATE UNIQUE INDEX stcm.rslt_fp_pi_uk ON stcm.result_facility_processes 2 (process_id) 3 LOGGING 4 TABLESPACE STCM_INDEX 5 PCTFREE 10 6 INITRANS 2 7 MAXTRANS 255 8 STORAGE ( 9 INITIAL 12000K 10 NEXT 1200K 11 MINEXTENTS 1 12 MAXEXTENTS 505 13 PCTINCREASE 0 14 FREELISTS 1 15 FREELIST GROUPS 1 16 BUFFER_POOL DEFAULT 17 ) 18 NOPARALLEL; Index created. SQL> SQL> -- took 10 seconds on TEST9206 SQL> CREATE INDEX stcm.rslt_fp_fi_i ON stcm.result_facility_processes 2 (facility_id) 3 LOGGING 4 TABLESPACE STCM_INDEX 5 PCTFREE 10 6 INITRANS 2 7 MAXTRANS 255 8 STORAGE ( 9 INITIAL 12000K 10 NEXT 1200K 11 MINEXTENTS 1 12 MAXEXTENTS 505 13 PCTINCREASE 0 14 FREELISTS 1 15 FREELIST GROUPS 1 16 BUFFER_POOL DEFAULT 17 ) 18 NOPARALLEL; Index created. SQL> SQL> COMMENT ON TABLE STCM.result_facility_processes IS 'CREATED BY FIRST APPLICATION. List all the associated activity, project, initiative and violation processes for each facility.'; Comment created. SQL> SQL> COMMENT ON COLUMN STCM.result_facility_processes.FACILITY_ID IS 'The facility ID to which this process belongs to. This data comes from ewoc.process_attributes table. The attribute ID is 10.'; Comment created. SQL> SQL> COMMENT ON COLUMN STCM.result_facility_processes.PROCESS_ID IS 'The process ID from ewoc.processes table.'; Comment created. SQL> SQL> COMMENT ON COLUMN STCM.result_facility_processes.PRCSDF_PROCESS_DEF_ID IS 'Process definition ID from ewoc.process_definitions table. The process is classified by one of these definitions.'; Comment created. SQL> SQL> COMMENT ON COLUMN STCM.result_facility_processes.PRSTCD_PROCESS_STATUS_ID IS 'Process status, is it closed/open/pending etc.. This data comes from ewoc.process_status_codes table.'; Comment created. SQL> SQL> COMMENT ON COLUMN STCM.result_facility_processes.DESCRIPTION IS 'Description of the process.'; Comment created. SQL> SQL> COMMENT ON COLUMN STCM.result_facility_processes.STATUS_DATE IS 'Date the current process status became effective.'; Comment created. SQL> SQL> COMMENT ON COLUMN STCM.result_facility_processes.INITIATED_DATE IS 'Date the process was instantiated.'; Comment created. SQL> SQL> COMMENT ON COLUMN STCM.result_facility_processes.CLOSED_DATE IS 'Date the process was completed.'; Comment created. SQL> SQL> COMMENT ON COLUMN STCM.result_facility_processes.CREATED_USERNAME IS 'Identifies the user that created the initial record for auditing purposes.'; Comment created. SQL> SQL> COMMENT ON COLUMN STCM.result_facility_processes.CREATED_TIMESTAMP IS 'Identifies the date and time that the initial record was created for auditing purposes.'; Comment created. SQL> SQL> COMMENT ON COLUMN STCM.result_facility_processes.USERNAME IS 'Identifies the user that last modified the record for auditing purposes.'; Comment created. SQL> SQL> COMMENT ON COLUMN STCM.result_facility_processes.TIME_STAMP IS 'Identifies the date and time that the record was last modified for auditing purposes.'; Comment created. SQL> SQL> COMMENT ON COLUMN STCM.result_facility_processes.NAME IS 'Short name for the process displayed to the user for selection purposes.'; Comment created. SQL> SQL> COMMENT ON COLUMN STCM.result_facility_processes.DUE_DATE IS 'Date the process is due for a completed status.'; Comment created. SQL> SQL> COMMENT ON COLUMN STCM.result_facility_processes.PRTPCD_PROCESS_TYPE_ID IS 'Type of process. Is it a initiative/project/activity/violation etc.. This data comes from ewoc.process_type_codes table.'; Comment created. SQL> SQL> COMMENT ON COLUMN STCM.result_facility_processes.RSTCD_RESULT_ID IS 'The result of this process. This data comes from ewoc.result_codes table.'; Comment created. SQL> SQL> COMMENT ON COLUMN STCM.result_facility_processes.ORDINAL IS 'The order that related processes are identified with.'; Comment created. SQL> SQL> COMMENT ON COLUMN STCM.result_facility_processes.OPENED_DATE IS 'Date the process status was changed to OPEN.'; Comment created. SQL> SQL> COMMENT ON COLUMN STCM.result_facility_processes.INITIATED_USER_ID IS 'Oracle ID of the user who created this process.'; Comment created. SQL> SQL> COMMENT ON COLUMN STCM.result_facility_processes.OPENED_USER_ID IS 'Oracle ID of the user who changed the status of this process to OPEN.'; Comment created. SQL> SQL> COMMENT ON COLUMN STCM.result_facility_processes.CLOSED_USER_ID IS 'Oracle ID of the user who changed the status of this process to CLOSED.'; Comment created. SQL> SQL> SQL> GRANT SELECT ON STCM.result_facility_processes TO FIRST_ADMIN; Grant succeeded. SQL> SQL> GRANT SELECT ON STCM.result_facility_processes TO FIRST_QUERY_ROLE; Grant succeeded. SQL> /******************************************************************************* DOC> * result_dist_ofc_county_facs DOC> *******************************************************************************/ SQL> SQL> CREATE TABLE stcm.result_dist_ofc_county_facs 2 TABLESPACE STCM_DATA 3 STORAGE 4 ( 5 INITIAL 7440K 6 NEXT 744K 7 PCTINCREASE 0 8 MINEXTENTS 1 9 MAXEXTENTS 505 10 ) 11 AS 12 SELECT tocc.dis_district_id as district_id, 13 tocc.toc_tank_office_id as tank_office_id, 14 toc.office_type, 15 toc.name tank_office_name, 16 f.cc_county_id county_id, 17 cc.name county_name, 18 f.facility_id facility_id, 19 f.name facility_name, 20 dc.name district_name 21 FROM stcm.facilities f, 22 stcm.tank_office_county_codes tocc, 23 stcm.tank_office_codes toc, 24 bis_lib.county_codes cc, 25 bis_lib.district_codes dc 26 WHERE f.dep_contractor_owned = 'N' 27 AND f.cc_county_id = tocc.cc_county_id 28 AND cc.county_id = f.cc_county_id 29 AND dc.abbreviation = tocc.dis_district_id 30 AND tocc.end_date IS NULL 31 AND tocc.toc_tank_office_id = toc.tank_office_id 32 AND toc.office_type = 'LP' 33 UNION 34 SELECT tocc.dis_district_id as district_id, 35 tocc.toc_tank_office_id as tank_office_id, 36 toc.office_type, 37 toc.name tank_office_name, 38 f.cc_county_id county_id, 39 cc.name county_name, 40 f.facility_id facility_id, 41 f.name facility_name, 42 dc.name district_name 43 FROM stcm.facilities f, 44 stcm.tank_office_county_codes tocc, 45 stcm.tank_office_codes toc, 46 bis_lib.county_codes cc, 47 bis_lib.district_codes dc 48 WHERE f.dep_contractor_owned = 'N' 49 AND f.cc_county_id = tocc.cc_county_id 50 AND cc.county_id = f.cc_county_id 51 AND dc.abbreviation = tocc.dis_district_id 52 AND tocc.end_date IS NULL 53 AND tocc.toc_tank_office_id = toc.tank_office_id 54 AND tocc.cc_county_id NOT IN ( 55 SELECT tocc.cc_county_id 56 FROM tank_office_county_codes tocc, tank_office_codes toc 57 WHERE toc_tank_office_id = toc.tank_office_id 58 AND tocc.end_date IS NULL 59 AND toc.office_type = 'LP') 60 AND toc.office_type = 'DST' 61 UNION 62 SELECT tocc.dis_district_id as district_id, 63 tocc.toc_tank_office_id as tank_office_id, 64 toc.office_type, 65 toc.name tank_office_name, 66 f.cc_county_id county_id, 67 cc.name county_name, 68 f.facility_id facility_id, 69 f.name facility_name, 70 dc.name district_name 71 FROM stcm.facilities f, 72 stcm.tank_office_county_codes tocc, 73 stcm.tank_office_codes toc, 74 bis_lib.county_codes cc, 75 bis_lib.district_codes dc 76 WHERE f.dep_contractor_owned = 'Y' 77 AND f.cc_county_id = tocc.cc_county_id 78 AND cc.county_id = f.cc_county_id 79 AND dc.abbreviation = tocc.dis_district_id 80 AND tocc.end_date IS NULL 81 AND tocc.toc_tank_office_id = toc.tank_office_id 82 AND toc.office_type = 'DST'; Table created. SQL> /******************************************************************************** DOC> * need index on the following column for the stcm.result_dist_ofc_county_facs table: DOC> * facility_id DOC> ********************************************************************************/ SQL> CREATE UNIQUE INDEX stcm.rslt_docf_fi_uk ON stcm.result_dist_ofc_county_facs 2 (facility_id) 3 LOGGING 4 TABLESPACE STCM_INDEX 5 PCTFREE 10 6 INITRANS 2 7 MAXTRANS 255 8 STORAGE ( 9 INITIAL 1120K 10 NEXT 112K 11 MINEXTENTS 1 12 MAXEXTENTS 505 13 PCTINCREASE 0 14 FREELISTS 1 15 FREELIST GROUPS 1 16 BUFFER_POOL DEFAULT 17 ) 18 NOPARALLEL; Index created. SQL> SQL> COMMENT ON TABLE STCM.result_dist_ofc_county_facs IS 'CREATED BY FIRST APPLICATION. 2 This table relates facility with county , district and tankoffice (waste first web application) 3 Author: Rajitha Kantamneni 4 DATE: 5 July 2006'; Comment created. SQL> SQL> COMMENT ON COLUMN STCM.result_dist_ofc_county_facs.district_id IS 'The abbreviation from bis_lib.district_codes table'; Comment created. SQL> SQL> COMMENT ON COLUMN STCM.result_dist_ofc_county_facs.tank_office_id IS 'The tank_office_id from stcm.tank_office_county_codes table.'; Comment created. SQL> SQL> COMMENT ON COLUMN STCM.result_dist_ofc_county_facs.office_type IS 'The office_type from stcm.facilities table'; Comment created. SQL> SQL> COMMENT ON COLUMN STCM.result_dist_ofc_county_facs.tank_office_name IS 'The name from stcm.tank_office_codes table'; Comment created. SQL> SQL> COMMENT ON COLUMN STCM.result_dist_ofc_county_facs.county_id IS 'county_id from bis_lib.county_codes table.'; Comment created. SQL> SQL> COMMENT ON COLUMN STCM.result_dist_ofc_county_facs.county_name IS 'name from bis_lib.county_codes table.'; Comment created. SQL> SQL> COMMENT ON COLUMN STCM.result_dist_ofc_county_facs.facility_id IS 'facility_id from stcm.facilities table.'; Comment created. SQL> SQL> COMMENT ON COLUMN STCM.result_dist_ofc_county_facs.facility_name IS 'name from stcm.facilities table.'; Comment created. SQL> SQL> COMMENT ON COLUMN STCM.result_dist_ofc_county_facs.district_name IS 'The name from bis_lib.district_codes table .'; Comment created. SQL> SQL> SQL> SQL> GRANT SELECT ON STCM.result_dist_ofc_county_facs TO FIRST_ADMIN; Grant succeeded. SQL> SQL> GRANT SELECT ON STCM.result_dist_ofc_county_facs TO FIRST_QUERY_ROLE; Grant succeeded. SQL> /******************************************************************************* DOC> * result_facility_activities -- took 40 secs to create on test9206 DOC> *******************************************************************************/ SQL> SQL> CREATE TABLE stcm.result_facility_activities 2 TABLESPACE STCM_DATA 3 STORAGE 4 ( 5 INITIAL 2592K 6 NEXT 264K 7 PCTINCREASE 0 8 MINEXTENTS 1 9 MAXEXTENTS 505 10 ) 11 AS 12 SELECT distinct(p.process_id) as activity_process_id, 13 p.name AS activity_name, 14 p.description AS activity_description, 15 docf.facility_id AS facility_id, 16 docf.facility_name AS facility_name, 17 p2.process_id AS project_process_id, 18 p2.name AS project_name, 19 p.rstcd_result_id AS activity_result_id, 20 rc.name as result_name, 21 ppp.activity_user_id, 22 cu.last_name || ', ' || cu.first_name || ' ' || substr(cu.middle_name, 1, 1) AS activity_user_name, 23 cu.toc_tank_office_id AS user_tank_office_id, 24 p.prcsdf_process_def_id AS activity_def_id, 25 p.prstcd_process_status_id AS activity_status_id, 26 p.initiated_user_id AS activity_initiated_user_id, 27 p.initiated_date AS activity_initiated_date, 28 p.closed_user_id AS activity_closed_user_id, 29 p.closed_date AS activity_closed_date, 30 p.opened_user_id AS activity_opened_user_id, 31 p.opened_date AS activity_opened_date, 32 pa2.assigned_date AS activity_assigned_date, 33 pa2.assigned_user_id AS activity_assigned_user_id, 34 TO_NUMBER 35 (NVL ((SELECT pa.VALUE 36 FROM ewoc.process_attributes pa 37 WHERE pa.prcs_process_id = p.process_id 38 AND pa.pradf_process_attribute_def_id = 3), 0) 39 ) AS usts_inspected, 40 TO_NUMBER 41 (NVL ((SELECT pa.VALUE 42 FROM ewoc.process_attributes pa 43 WHERE pa.prcs_process_id = p.process_id 44 AND pa.pradf_process_attribute_def_id = 4), 0) 45 ) AS asts_inspected, 46 (SELECT CASE 47 WHEN VALUE IS NOT NULL AND VALUE <> 'CURRENT' 48 THEN TO_DATE (VALUE, 'YYYYMMDD HH:MI:SS') 49 ELSE NULL 50 END send_date 51 FROM ewoc.process_attributes pa 52 WHERE pa.prcs_process_id = p.process_id 53 AND pa.pradf_process_attribute_def_id = 38 54 and p.prcsdf_process_def_id = 11) AS ncl_send_date, 55 (SELECT CASE 56 WHEN VALUE IS NOT NULL AND VALUE <> 'CURRENT' 57 THEN TO_DATE (VALUE, 'YYYYMMDD HH:MI:SS') 58 ELSE NULL 59 END sent_date 60 FROM ewoc.process_attributes pa 61 WHERE pa.prcs_process_id = p.process_id 62 AND pa.pradf_process_attribute_def_id = 42 63 and p.prcsdf_process_def_id = 31) AS wl_sent_date, 64 docf.county_id as county_id, 65 docf.county_name as county_name, 66 docf.district_id as district_id, 67 docf.district_name as district_name, 68 docf.tank_office_id as faciltiy_tank_office_id, 69 docf.tank_office_name as facility_tank_office_name, 70 fp.prcs_process_id as facility_process_id, 71 pap.value as primary_value 72 FROM ewoc.processes p 73 join ewoc.process_attributes pa on p.process_id = pa.prcs_process_id 74 AND pa.value IS NOT NULL 75 AND pa.pradf_process_attribute_def_id = 10 76 join stcm.result_dist_ofc_county_facs docf on pa.value = TO_CHAR(docf.facility_id) 77 join ewoc.process_associations paa on p.process_id = paa.prcs_process_id_association 78 AND paa.astpcd_association_type_id = 2 79 join ewoc.processes p2 on p2.process_id = paa.prcs_process_id 80 join ewoc.process_assignments pa2 on p.process_id = pa2.prcs_process_id 81 join ( select pp.process_id, 82 (CASE WHEN pp.closed_user_id IS NOT NULL 83 THEN pp.closed_user_id 84 WHEN pa.assigned_user_id IS NOT NULL 85 THEN pa.assigned_user_id 86 WHEN pp.opened_user_id IS NOT NULL 87 THEN pp.opened_user_id 88 ELSE pp.initiated_user_id 89 END ) AS activity_user_id 90 from ewoc.processes pp , ewoc.process_assignments pa 91 where pp.process_id = pa.prcs_process_id) ppp on ppp.process_id = p.process_id 92 join stcm.client_users cu on cu.user_id = ppp.activity_user_id 93 left outer join ewoc.result_codes rc on p.rstcd_result_id = rc.result_id 94 join stcm.facility_processes fp on to_char(fp.fac_facility_id) = pa.value 95 left outer join ewoc.process_attributes pap on pap.prcs_process_id = p.process_id 96 and pap.pradf_process_attribute_def_id = 71 97 WHERE p.prtpcd_process_type_id = 3; Table created. SQL> SQL> /******************************************************************************** DOC> * need indexes on the following columns for the result_facility_activities table: DOC> * facility_process_id DOC> * activity_closed_date DOC> * county_id DOC> * district_id DOC> * activity_user_id DOC> ********************************************************************************/ SQL> /** DOC> used for other result tables (result_violations) DOC>*/ SQL> CREATE INDEX stcm.rslt_fa_fpi_i ON stcm.result_facility_activities 2 (facility_process_id) 3 LOGGING 4 TABLESPACE STCM_INDEX 5 PCTFREE 10 6 INITRANS 2 7 MAXTRANS 255 8 STORAGE ( 9 INITIAL 160K 10 NEXT 80K 11 MINEXTENTS 1 12 MAXEXTENTS 505 13 PCTINCREASE 0 14 FREELISTS 1 15 FREELIST GROUPS 1 16 BUFFER_POOL DEFAULT 17 ) 18 NOPARALLEL; Index created. SQL> SQL> /** DOC> used in Inspections by county and date range report DOC>*/ SQL> SQL> CREATE INDEX stcm.rslt_fa_acd_i ON stcm.result_facility_activities 2 (activity_closed_date) 3 LOGGING 4 TABLESPACE STCM_INDEX 5 PCTFREE 10 6 INITRANS 2 7 MAXTRANS 255 8 STORAGE ( 9 INITIAL 160K 10 NEXT 32K 11 MINEXTENTS 1 12 MAXEXTENTS 505 13 PCTINCREASE 0 14 FREELISTS 1 15 FREELIST GROUPS 1 16 BUFFER_POOL DEFAULT 17 ) 18 NOPARALLEL; Index created. SQL> SQL> /** DOC>used in Inspections by county and date range report DOC>*/ SQL> SQL> CREATE INDEX stcm.rslt_fa_ci_i ON stcm.result_facility_activities 2 (county_id) 3 LOGGING 4 TABLESPACE STCM_INDEX 5 PCTFREE 10 6 INITRANS 2 7 MAXTRANS 255 8 STORAGE ( 9 INITIAL 160K 10 NEXT 32K 11 MINEXTENTS 1 12 MAXEXTENTS 505 13 PCTINCREASE 0 14 FREELISTS 1 15 FREELIST GROUPS 1 16 BUFFER_POOL DEFAULT 17 ) 18 NOPARALLEL; Index created. SQL> SQL> /** DOC>used in Inspections by county and date range report DOC>*/ SQL> SQL> CREATE INDEX stcm.rslt_fa_di_i ON stcm.result_facility_activities 2 (district_id) 3 LOGGING 4 TABLESPACE STCM_INDEX 5 PCTFREE 10 6 INITRANS 2 7 MAXTRANS 255 8 STORAGE ( 9 INITIAL 160K 10 NEXT 32K 11 MINEXTENTS 1 12 MAXEXTENTS 505 13 PCTINCREASE 0 14 FREELISTS 1 15 FREELIST GROUPS 1 16 BUFFER_POOL DEFAULT 17 ) 18 NOPARALLEL; Index created. SQL> SQL> /** DOC>used in Inspections by county and date range report DOC>*/ SQL> SQL> CREATE INDEX stcm.rslt_fa_adi_i ON stcm.result_facility_activities 2 (activity_def_id) 3 LOGGING 4 TABLESPACE STCM_INDEX 5 PCTFREE 10 6 INITRANS 2 7 MAXTRANS 255 8 STORAGE ( 9 INITIAL 160K 10 NEXT 32K 11 MINEXTENTS 1 12 MAXEXTENTS 505 13 PCTINCREASE 0 14 FREELISTS 1 15 FREELIST GROUPS 1 16 BUFFER_POOL DEFAULT 17 ) 18 NOPARALLEL; Index created. SQL> SQL> /** DOC>used in Inspections by county and date range report DOC>*/ SQL> SQL> CREATE INDEX stcm.rslt_fa_aui_i ON stcm.result_facility_activities 2 (activity_user_id) 3 LOGGING 4 TABLESPACE STCM_INDEX 5 PCTFREE 10 6 INITRANS 2 7 MAXTRANS 255 8 STORAGE ( 9 INITIAL 240K 10 NEXT 32K 11 MINEXTENTS 1 12 MAXEXTENTS 505 13 PCTINCREASE 0 14 FREELISTS 1 15 FREELIST GROUPS 1 16 BUFFER_POOL DEFAULT 17 ) 18 NOPARALLEL; Index created. SQL> SQL> COMMENT ON TABLE STCM.result_facility_activities IS 'CREATED BY FIRST APPLICATION. 2 This result table relates all activities for that facility. 3 Author: Rajitha Kantamneni 4 DATE: 05 July 2006'; Comment created. SQL> SQL> COMMENT ON COLUMN STCM.result_facility_activities.activity_process_id IS 'The process_id from EWOC.PROCESSES table'; Comment created. SQL> SQL> COMMENT ON COLUMN STCM.result_facility_activities.activity_name IS 'The name from EWOC.PROCESSES table.'; Comment created. SQL> SQL> COMMENT ON COLUMN STCM.result_facility_activities.activity_description IS 'The description for this activity'; Comment created. SQL> SQL> COMMENT ON COLUMN STCM.result_facility_activities.facility_id IS 'The facility_id of the activity'; Comment created. SQL> SQL> COMMENT ON COLUMN STCM.result_facility_activities.facility_name IS 'The facility_name of the activity.'; Comment created. SQL> SQL> COMMENT ON COLUMN STCM.result_facility_activities.project_process_id IS 'the process_id of this project the activity is initiated'; Comment created. SQL> SQL> COMMENT ON COLUMN STCM.result_facility_activities.project_name IS 'The name of the project'; Comment created. SQL> SQL> COMMENT ON COLUMN STCM.result_facility_activities.activity_result_id IS 'result_id of the activity.'; Comment created. SQL> SQL> COMMENT ON COLUMN STCM.result_facility_activities.result_name IS 'name of the result .'; Comment created. SQL> SQL> COMMENT ON COLUMN STCM.result_facility_activities.activity_user_id IS 'The user_id of the activity.'; Comment created. SQL> SQL> COMMENT ON COLUMN STCM.result_facility_activities.activity_user_name IS 'user full name of the activity.'; Comment created. SQL> SQL> COMMENT ON COLUMN STCM.result_facility_activities.user_tank_office_id IS 'The user tank_office_id of the activity.'; Comment created. SQL> SQL> COMMENT ON COLUMN STCM.result_facility_activities.activity_def_id IS 'The def_id of the activity.'; Comment created. SQL> SQL> COMMENT ON COLUMN STCM.result_facility_activities.activity_status_id IS 'The status of the activity'; Comment created. SQL> SQL> COMMENT ON COLUMN STCM.result_facility_activities.activity_initiated_user_id IS 'The user who initiated this activity.'; Comment created. SQL> SQL> COMMENT ON COLUMN STCM.result_facility_activities.activity_initiated_date IS 'The initiated date of the activity.'; Comment created. SQL> SQL> COMMENT ON COLUMN STCM.result_facility_activities.activity_closed_user_id IS 'The user who closed this activity.'; Comment created. SQL> SQL> COMMENT ON COLUMN STCM.result_facility_activities.activity_closed_date IS 'Date the activity was closed.'; Comment created. SQL> SQL> COMMENT ON COLUMN STCM.result_facility_activities.activity_opened_user_id IS 'The user who opened this activity.'; Comment created. SQL> SQL> COMMENT ON COLUMN STCM.result_facility_activities.activity_opened_date IS 'Date the activity was opened'; Comment created. SQL> SQL> COMMENT ON COLUMN STCM.result_facility_activities.activity_assigned_date IS 'Date the activity was assigned.'; Comment created. SQL> SQL> COMMENT ON COLUMN STCM.result_facility_activities.activity_assigned_user_id IS 'The user who assigned for this activity'; Comment created. SQL> SQL> COMMENT ON COLUMN STCM.result_facility_activities.usts_inspected IS 'The number of usts inspected for this activity'; Comment created. SQL> SQL> COMMENT ON COLUMN STCM.result_facility_activities.asts_inspected IS 'The number of asts inspected for this activity'; Comment created. SQL> SQL> COMMENT ON COLUMN STCM.result_facility_activities.ncl_send_date IS 'The sent date of the non compliance letter activity'; Comment created. SQL> SQL> COMMENT ON COLUMN STCM.result_facility_activities.wl_sent_date IS 'The sent date of the warning letter activity'; Comment created. SQL> SQL> COMMENT ON COLUMN STCM.result_facility_activities.county_id IS 'The county id of this facility'; Comment created. SQL> SQL> COMMENT ON COLUMN STCM.result_facility_activities.county_name IS 'The county name of this facility'; Comment created. SQL> SQL> COMMENT ON COLUMN STCM.result_facility_activities.district_id IS 'The district id of this facility'; Comment created. SQL> SQL> COMMENT ON COLUMN STCM.result_facility_activities.district_name IS 'The district name of this facility'; Comment created. SQL> SQL> COMMENT ON COLUMN STCM.result_facility_activities.faciltiy_tank_office_id IS 'The tank office id of this facility'; Comment created. SQL> SQL> COMMENT ON COLUMN STCM.result_facility_activities.facility_tank_office_name IS 'The tank office name of this facility'; Comment created. SQL> SQL> COMMENT ON COLUMN STCM.result_facility_activities.facility_process_id IS 'The facility process id of this activity.'; Comment created. SQL> SQL> COMMENT ON COLUMN STCM.result_facility_activities.primary_value IS 'The inspection primary value of this activity.'; Comment created. SQL> SQL> GRANT SELECT ON STCM.result_facility_activities TO FIRST_ADMIN; Grant succeeded. SQL> SQL> GRANT SELECT ON STCM.result_facility_activities TO FIRST_QUERY_ROLE; Grant succeeded. SQL> /******************************************************************************* DOC> * result_violations - took 1.30 min on test9206 DOC> *******************************************************************************/ SQL> SQL> CREATE TABLE stcm.result_violations 2 TABLESPACE STCM_DATA 3 STORAGE 4 ( 5 INITIAL 5760K 6 NEXT 576K 7 PCTINCREASE 0 8 MINEXTENTS 1 9 MAXEXTENTS 505 10 ) 11 AS 12 SELECT p2.process_id AS violation_process_id, 13 fp.process_id AS facility_process_id, 14 docf.district_id, docf.district_name, docf.tank_office_id AS facility_tank_office_id, 15 docf.tank_office_name AS facility_tank_office_name, docf.county_id, docf.county_name, 16 fp.facility_id AS facility_id, docf.facility_name, 17 pai.prcs_process_id AS initiated_process_id, 18 p2.initiated_date AS initiated_date, v.value AS viocd_violation_id, 19 rc.name AS activity_result, sc.name AS violation_significance, 20 psc.name AS violation_status, 21 p3.name AS initiated_activity_name, 22 p3.closed_date AS initiated_activity_closed_date, 23 p2.closed_date AS resolved_date, 24 par.prcs_process_id AS resolved_process_id, 25 p4.name as resolving_activity_name, 26 (SELECT pa.value 27 FROM ewoc.process_attributes pa 28 WHERE pa.prcs_process_id = p2.process_id 29 AND pa.pradf_process_attribute_def_id = 8) AS explanation, 30 (SELECT pa.value 31 FROM ewoc.process_attributes pa 32 WHERE pa.prcs_process_id = p2.process_id 33 AND pa.pradf_process_attribute_def_id = 9) AS corrective_action, 34 p2.initiated_user_id AS initiated_user_id, 35 p2.closed_user_id AS resolved_user_id, 36 (SELECT MAX (npa.ncl_send_date) 37 FROM stcm.result_facility_activities npa 38 WHERE npa.facility_process_id = fp.process_id 39 AND npa.ncl_send_date BETWEEN p2.initiated_date AND NVL (p2.closed_date, SYSDATE)) max_ncl_sent_date, 40 (SELECT MAX (wpa.wl_sent_date) 41 FROM stcm.result_facility_activities wpa 42 WHERE wpa.facility_process_id = fp.process_id 43 AND wpa.wl_sent_date BETWEEN p2.initiated_date AND NVL (p2.closed_date, SYSDATE)) max_wl_sent_date, 44 (SELECT MAX (rfa.activity_closed_date) 45 FROM stcm.result_facility_activities rfa,ewoc.process_attributes pa 46 WHERE rfa.facility_process_id = fp.process_id 47 and rfa.activity_process_id = pa.prcs_process_id 48 and pa.pradf_process_attribute_def_id in (34,61) 49 and pa.value like '%Primary%' 50 AND rfa.activity_closed_date BETWEEN p2.initiated_date AND NVL (p2.closed_date, SYSDATE)) reinspection_date 51 FROM stcm.result_facility_processes fp 52 JOIN ewoc.processes p1 ON p1.process_id = fp.process_id AND p1.prtpcd_process_type_id = 1 53 JOIN ewoc.process_associations pa12 ON p1.process_id = pa12.prcs_process_id 54 AND pa12.astpcd_association_type_id = 4 55 JOIN ewoc.processes p2 ON pa12.prcs_process_id_association = p2.process_id 56 AND p2.prtpcd_process_type_id = 5 57 JOIN 58 (SELECT pa.VALUE, pa.prcs_process_id 59 FROM ewoc.process_attributes pa 60 WHERE pa.pradf_process_attribute_def_id = 7) v 61 ON v.prcs_process_id = p2.process_id 62 LEFT OUTER JOIN 63 (SELECT pa.prcs_process_id_association, pa.prcs_process_id 64 FROM ewoc.process_associations pa 65 WHERE pa.astpcd_association_type_id = 6) pai 66 ON p2.process_id = pai.prcs_process_id_association 67 LEFT OUTER JOIN ewoc.processes p3 ON p3.process_id = pai.prcs_process_id 68 LEFT OUTER JOIN 69 (SELECT pa.prcs_process_id_association, pa.prcs_process_id 70 FROM ewoc.process_associations pa 71 WHERE pa.astpcd_association_type_id = 5) par ON p2.process_id = par.prcs_process_id_association 72 LEFT OUTER JOIN ewoc.processes p4 on p4.process_id = par.prcs_process_id 73 JOIN stcm.violation_codes vc ON v.value = vc.violation_id 74 JOIN stcm.significance_codes sc ON vc.sigcd_significance_id = sc.significance_id 75 LEFT OUTER JOIN ewoc.result_codes rc ON rc.result_id = p3.rstcd_result_id 76 JOIN ewoc.process_status_codes psc ON p2.prstcd_process_status_id = psc.process_status_id 77 JOIN stcm.result_dist_ofc_county_facs docf ON fp.facility_id = docf.facility_id; Table created. SQL> SQL> /******************************************************************************** DOC> * need indexes on the following columns for the result_violations table: DOC> * facility_process_id DOC> * activity_closed_date DOC> * county_id DOC> * district_id DOC> * activity_user_id DOC> ********************************************************************************/ SQL> /** DOC> used in Inspections by county and date range report DOC>*/ SQL> CREATE INDEX stcm.rslt_v_rd_i ON stcm.result_violations 2 (resolved_date) 3 LOGGING 4 TABLESPACE STCM_INDEX 5 PCTFREE 10 6 INITRANS 2 7 MAXTRANS 255 8 STORAGE ( 9 INITIAL 120K 10 NEXT 32K 11 MINEXTENTS 1 12 MAXEXTENTS 505 13 PCTINCREASE 0 14 FREELISTS 1 15 FREELIST GROUPS 1 16 BUFFER_POOL DEFAULT 17 ) 18 NOPARALLEL; Index created. SQL> /** DOC>used in charts DOC>*/ SQL> CREATE INDEX stcm.rslt_v_ci_i ON stcm.result_violations 2 (county_id) 3 LOGGING 4 TABLESPACE STCM_INDEX 5 PCTFREE 10 6 INITRANS 2 7 MAXTRANS 255 8 STORAGE ( 9 INITIAL 360K 10 NEXT 64K 11 MINEXTENTS 1 12 MAXEXTENTS 505 13 PCTINCREASE 0 14 FREELISTS 1 15 FREELIST GROUPS 1 16 BUFFER_POOL DEFAULT 17 ) 18 NOPARALLEL; Index created. SQL> SQL> /** DOC>used in charts DOC>*/ SQL> CREATE INDEX stcm.rslt_v_di_i ON stcm.result_violations 2 (district_id) 3 LOGGING 4 TABLESPACE STCM_INDEX 5 PCTFREE 10 6 INITRANS 2 7 MAXTRANS 255 8 STORAGE ( 9 INITIAL 360K 10 NEXT 64K 11 MINEXTENTS 1 12 MAXEXTENTS 505 13 PCTINCREASE 0 14 FREELISTS 1 15 FREELIST GROUPS 1 16 BUFFER_POOL DEFAULT 17 ) 18 NOPARALLEL; Index created. SQL> SQL> /** DOC>used in charts DOC>*/ SQL> CREATE INDEX stcm.rslt_v_ftoi_i ON stcm.result_violations 2 (facility_tank_office_id) 3 LOGGING 4 TABLESPACE STCM_INDEX 5 PCTFREE 10 6 INITRANS 2 7 MAXTRANS 255 8 STORAGE ( 9 INITIAL 520K 10 NEXT 64K 11 MINEXTENTS 1 12 MAXEXTENTS 505 13 PCTINCREASE 0 14 FREELISTS 1 15 FREELIST GROUPS 1 16 BUFFER_POOL DEFAULT 17 ) 18 NOPARALLEL; Index created. SQL> COMMENT ON TABLE STCM.result_violations IS 'CREATED BY FIRST APPLICATION. 2 List all the violation processes that are in EWOC.'; Comment created. SQL> SQL> COMMENT ON COLUMN STCM.result_violations.violation_process_id IS 'Process ID of "violation" process. This data comes from ewoc.processes table.'; Comment created. SQL> SQL> COMMENT ON COLUMN STCM.result_violations.facility_process_id IS 'Process ID of facility process. '; Comment created. SQL> SQL> COMMENT ON COLUMN STCM.result_violations.district_id IS 'The district id of the facility which this violation belongs.'; Comment created. SQL> SQL> COMMENT ON COLUMN STCM.result_violations.district_name IS 'The name of that district'; Comment created. SQL> SQL> COMMENT ON COLUMN STCM.result_violations.facility_tank_office_id IS 'The tank office id of that facility.'; Comment created. SQL> SQL> COMMENT ON COLUMN STCM.result_violations.facility_tank_office_name IS 'The name of that tank office id.'; Comment created. SQL> SQL> COMMENT ON COLUMN STCM.result_violations.county_id IS 'The county id of that facility.'; Comment created. SQL> SQL> COMMENT ON COLUMN STCM.result_violations.county_name IS 'The name of the county.'; Comment created. SQL> SQL> COMMENT ON COLUMN STCM.result_violations.facility_id IS 'Facility ID where the violation occurred. This data comes from stcm.facility_processes table.'; Comment created. SQL> SQL> COMMENT ON COLUMN STCM.result_violations.facility_name IS 'Facility name where the violation occurred. This data comes from stcm.facility_processes table.'; Comment created. SQL> SQL> COMMENT ON COLUMN STCM.result_violations.initiated_process_id IS 'The process that initiated this violation. The relationship is defined in ewoc.process_associations table.'; Comment created. SQL> SQL> COMMENT ON COLUMN STCM.result_violations.initiated_date IS 'The date this violation was initiated. This data comes from ewoc.processes table.'; Comment created. SQL> SQL> COMMENT ON COLUMN STCM.result_violations.viocd_violation_id IS 'Violation code of the Violation process. This data comes from ewoc.process_attributes table. The attribute ID is 7.'; Comment created. SQL> SQL> COMMENT ON COLUMN STCM.result_violations.activity_result IS 'the result of the activity.'; Comment created. SQL> SQL> COMMENT ON COLUMN STCM.result_violations.violation_significance IS 'The significance of the violation.'; Comment created. SQL> SQL> COMMENT ON COLUMN STCM.result_violations.violation_status IS 'The status of the violation.'; Comment created. SQL> SQL> COMMENT ON COLUMN STCM.result_violations.initiated_activity_name IS 'Initiating process name.'; Comment created. SQL> SQL> COMMENT ON COLUMN STCM.result_violations.initiated_activity_closed_date IS 'Initiating process closing date.'; Comment created. SQL> SQL> COMMENT ON COLUMN STCM.result_violations.resolved_date IS 'The date this violation was closed. This data comes from ewoc.processes table.'; Comment created. SQL> SQL> COMMENT ON COLUMN STCM.result_violations.resolved_process_id IS 'The process that resolved this violation. The relationship is defined in ewoc.process_associations table.'; Comment created. SQL> SQL> COMMENT ON COLUMN STCM.result_violations.resolving_activity_name IS 'Resolved process name.'; Comment created. SQL> SQL> COMMENT ON COLUMN STCM.result_violations.explanation IS 'The explanation given for citing the violation. This data comes from ewoc.process_attributes table. The attribute ID is 8.'; Comment created. SQL> SQL> COMMENT ON COLUMN STCM.result_violations.corrective_action IS 'Corrective action needed to resolve the violation. This data comes from ewoc.process_attributes table. The attribute ID is 9.'; Comment created. SQL> SQL> COMMENT ON COLUMN STCM.result_violations.initiated_user_id IS 'Oracle ID of the user who created this violation(process).'; Comment created. SQL> SQL> COMMENT ON COLUMN STCM.result_violations.resolved_user_id IS 'Oracle ID of the user who changed the status of this violation(process) to CLOSED.'; Comment created. SQL> SQL> COMMENT ON COLUMN STCM.result_violations.max_ncl_sent_date IS 'Maximum of the sent date when the NCL send.'; Comment created. SQL> SQL> COMMENT ON COLUMN STCM.result_violations.max_wl_sent_date IS 'Maximum of the sent date when the warning letter send.'; Comment created. SQL> SQL> COMMENT ON COLUMN STCM.result_violations.reinspection_date IS 'Maximum of the re inspection date.'; Comment created. SQL> SQL> GRANT SELECT ON STCM.result_violations TO FIRST_ADMIN; Grant succeeded. SQL> SQL> GRANT SELECT ON STCM.result_violations TO FIRST_QUERY_ROLE; Grant succeeded. SQL> SQL> SELECT systimestamp FROM dual; 07-JUL-06 05.39.43.411476 PM -04:00 1 row selected. SQL> -------------------- SQL> spool off