SQL> SQL> /****************************************************************************** DOC> * FOR USE ON THE SERVER STCM DATABASE * DOC> *----------------------------------------------------------------------------* DOC> * PURPOSE: * DOC> * Create stored procedure that will refresh the unresolved violations result * DOC> * tables. * DOC> * * DOC> * EXECUTE THIS AS STCM * DOC> * * DOC> * Author: Rajitha Kantamneni * DOC> * * DOC> * Date: July 05, 2006 * DOC> * * DOC> ******************************************************************************/ SQL> SELECT systimestamp FROM dual; 07-JUL-06 05.39.43.466132 PM -04:00 1 row selected. SQL> SQL> CREATE OR REPLACE PROCEDURE STCM.refresh_result_tables 2 IS 3 /******************************************************************************* 4 * Name: REFRESH_RESULT_TABLES * 5 * * 6 * Purpose: Refresh a set of result tables to support a portal reports. * 7 * * 8 * Author: Rajitha Kantamneni * 9 * * 10 * Date: 05 July, 2006 * 11 * * 12 *******************************************************************************/ 13 14 BEGIN 15 16 /******************************************************************************* 17 * Truncate all of the tables that support unresolved violations 18 *******************************************************************************/ 19 EXECUTE IMMEDIATE 'TRUNCATE TABLE stcm.result_violations REUSE STORAGE'; 20 EXECUTE IMMEDIATE 'DROP INDEX stcm.rslt_v_rd_i'; 21 EXECUTE IMMEDIATE 'DROP INDEX stcm.rslt_v_ci_i'; 22 EXECUTE IMMEDIATE 'DROP INDEX stcm.rslt_v_di_i'; 23 EXECUTE IMMEDIATE 'DROP INDEX stcm.rslt_v_ftoi_i'; 24 25 26 27 EXECUTE IMMEDIATE 'TRUNCATE TABLE stcm.result_facility_activities REUSE STORAGE'; 28 EXECUTE IMMEDIATE 'DROP INDEX stcm.rslt_fa_aui_i'; 29 EXECUTE IMMEDIATE 'DROP INDEX stcm.rslt_fa_adi_i'; 30 EXECUTE IMMEDIATE 'DROP INDEX stcm.rslt_fa_di_i'; 31 EXECUTE IMMEDIATE 'DROP INDEX stcm.rslt_fa_ci_i'; 32 EXECUTE IMMEDIATE 'DROP INDEX stcm.rslt_fa_acd_i'; 33 EXECUTE IMMEDIATE 'DROP INDEX stcm.rslt_fa_fpi_i'; 34 35 EXECUTE IMMEDIATE 'TRUNCATE TABLE stcm.result_dist_ofc_county_facs REUSE STORAGE'; 36 EXECUTE IMMEDIATE 'DROP INDEX stcm.rslt_docf_fi_uk'; 37 38 EXECUTE IMMEDIATE 'TRUNCATE TABLE stcm.result_facility_processes REUSE STORAGE'; 39 EXECUTE IMMEDIATE 'DROP INDEX stcm.rslt_fp_pi_uk'; 40 EXECUTE IMMEDIATE 'DROP INDEX stcm.rslt_fp_fi_i'; 41 42 /******************************************************************************* 43 * result_facility_processes 44 *******************************************************************************/ 45 EXECUTE IMMEDIATE 'INSERT INTO stcm.result_facility_processes 46 (facility_id, 47 process_id, 48 prcsdf_process_def_id, 49 prstcd_process_status_id, 50 description, 51 status_date, 52 initiated_date, 53 closed_date, 54 created_username, 55 created_timestamp, 56 username, 57 time_stamp, 58 NAME, 59 due_date, 60 prtpcd_process_type_id, 61 rstcd_result_id, 62 ordinal, 63 opened_date, 64 initiated_user_id, 65 opened_user_id, 66 closed_user_id 67 ) 68 SELECT STCM.TO_NUMBER_IF_NUMERIC(pa.VALUE) AS facility_id, 69 p.process_id, 70 p.prcsdf_process_def_id, 71 p.prstcd_process_status_id, 72 p.description, 73 p.status_date, 74 p.initiated_date, 75 p.closed_date, 76 p.created_username, 77 p.created_timestamp, 78 p.username, 79 p.time_stamp, 80 p.NAME, 81 p.due_date, 82 p.prtpcd_process_type_id, 83 p.rstcd_result_id, 84 p.ordinal, 85 p.opened_date, 86 p.initiated_user_id, 87 p.opened_user_id, 88 p.closed_user_id 89 FROM ewoc.processes p, 90 ewoc.process_attributes pa 91 WHERE pa.prcs_process_id = p.process_id 92 AND pa.pradf_process_attribute_def_id = 10'; 93 94 EXECUTE IMMEDIATE 'CREATE UNIQUE INDEX stcm.rslt_fp_pi_uk ON stcm.result_facility_processes 95 (process_id) 96 LOGGING 97 TABLESPACE STCM_INDEX 98 PCTFREE 10 99 INITRANS 2 100 MAXTRANS 255 101 STORAGE ( 102 INITIAL 12000K 103 NEXT 1200K 104 MINEXTENTS 1 105 MAXEXTENTS 505 106 PCTINCREASE 0 107 FREELISTS 1 108 FREELIST GROUPS 1 109 BUFFER_POOL DEFAULT 110 ) 111 NOPARALLEL'; 112 113 EXECUTE IMMEDIATE 'CREATE INDEX stcm.rslt_fp_fi_i ON stcm.result_facility_processes 114 (facility_id) 115 LOGGING 116 TABLESPACE STCM_INDEX 117 PCTFREE 10 118 INITRANS 2 119 MAXTRANS 255 120 STORAGE ( 121 INITIAL 12000K 122 NEXT 1200K 123 MINEXTENTS 1 124 MAXEXTENTS 505 125 PCTINCREASE 0 126 FREELISTS 1 127 FREELIST GROUPS 1 128 BUFFER_POOL DEFAULT 129 ) 130 NOPARALLEL'; 131 /******************************************************************************* 132 * stcm.result_dist_ofc_county_facs 133 *******************************************************************************/ 134 EXECUTE IMMEDIATE 'INSERT INTO stcm.result_dist_ofc_county_facs 135 (district_id, 136 tank_office_id, 137 office_type, 138 tank_office_name, 139 county_id, 140 county_name, 141 facility_id, 142 facility_name, 143 district_name) 144 SELECT tocc.dis_district_id as district_id, 145 tocc.toc_tank_office_id as tank_office_id, 146 toc.office_type, 147 toc.name tank_office_name, 148 f.cc_county_id county_id, 149 cc.name county_name, 150 f.facility_id facility_id, 151 f.name facility_name, 152 dc.name district_name 153 FROM stcm.facilities f, 154 stcm.tank_office_county_codes tocc, 155 stcm.tank_office_codes toc, 156 bis_lib.county_codes cc, 157 bis_lib.district_codes dc 158 WHERE f.dep_contractor_owned = ''N'' 159 AND f.cc_county_id = tocc.cc_county_id 160 AND cc.county_id = f.cc_county_id 161 AND dc.abbreviation = tocc.dis_district_id 162 AND tocc.end_date IS NULL 163 AND tocc.toc_tank_office_id = toc.tank_office_id 164 AND toc.office_type = ''LP'' 165 UNION 166 SELECT tocc.dis_district_id as district_id, 167 tocc.toc_tank_office_id as tank_office_id, 168 toc.office_type, 169 toc.name tank_office_name, 170 f.cc_county_id county_id, 171 cc.name county_name, 172 f.facility_id facility_id, 173 f.name facility_name, 174 dc.name district_name 175 FROM stcm.facilities f, 176 stcm.tank_office_county_codes tocc, 177 stcm.tank_office_codes toc, 178 bis_lib.county_codes cc, 179 bis_lib.district_codes dc 180 WHERE f.dep_contractor_owned = ''N'' 181 AND f.cc_county_id = tocc.cc_county_id 182 AND cc.county_id = f.cc_county_id 183 AND dc.abbreviation = tocc.dis_district_id 184 AND tocc.end_date IS NULL 185 AND tocc.toc_tank_office_id = toc.tank_office_id 186 AND tocc.cc_county_id NOT IN ( 187 SELECT tocc.cc_county_id 188 FROM tank_office_county_codes tocc, tank_office_codes toc 189 WHERE toc_tank_office_id = toc.tank_office_id 190 AND tocc.end_date IS NULL 191 AND toc.office_type = ''LP'') 192 AND toc.office_type = ''DST'' 193 UNION 194 SELECT tocc.dis_district_id as district_id, 195 tocc.toc_tank_office_id as tank_office_id, 196 toc.office_type, 197 toc.name tank_office_name, 198 f.cc_county_id county_id, 199 cc.name county_name, 200 f.facility_id facility_id, 201 f.name facility_name, 202 dc.name district_name 203 FROM stcm.facilities f, 204 stcm.tank_office_county_codes tocc, 205 stcm.tank_office_codes toc, 206 bis_lib.county_codes cc, 207 bis_lib.district_codes dc 208 WHERE f.dep_contractor_owned = ''Y'' 209 AND f.cc_county_id = tocc.cc_county_id 210 AND cc.county_id = f.cc_county_id 211 AND dc.abbreviation = tocc.dis_district_id 212 AND tocc.end_date IS NULL 213 AND tocc.toc_tank_office_id = toc.tank_office_id 214 AND toc.office_type = ''DST'' '; 215 EXECUTE IMMEDIATE 'CREATE UNIQUE INDEX stcm.rslt_docf_fi_uk ON stcm.result_dist_ofc_county_facs 216 (facility_id) 217 LOGGING 218 TABLESPACE STCM_INDEX 219 PCTFREE 10 220 INITRANS 2 221 MAXTRANS 255 222 STORAGE ( 223 INITIAL 1120K 224 NEXT 112K 225 MINEXTENTS 1 226 MAXEXTENTS 505 227 PCTINCREASE 0 228 FREELISTS 1 229 FREELIST GROUPS 1 230 BUFFER_POOL DEFAULT 231 ) 232 NOPARALLEL'; 233 /******************************************************************************* 234 * result_facility_activities 235 *******************************************************************************/ 236 EXECUTE IMMEDIATE 'INSERT INTO stcm.result_facility_activities 237 (activity_process_id, 238 activity_name, 239 activity_description, 240 facility_id, 241 facility_name, 242 project_process_id, 243 project_name, 244 activity_result_id, 245 result_name, 246 activity_user_id, 247 activity_user_name, 248 user_tank_office_id, 249 activity_def_id, 250 activity_status_id, 251 activity_initiated_user_id, 252 activity_initiated_date, 253 activity_closed_user_id, 254 activity_closed_date, 255 activity_opened_user_id, 256 activity_opened_date, 257 activity_assigned_date, 258 activity_assigned_user_id, 259 usts_inspected, 260 asts_inspected, 261 ncl_send_date, 262 wl_sent_date, 263 county_id, 264 county_name, 265 district_id, 266 district_name, 267 faciltiy_tank_office_id, 268 facility_tank_office_name, 269 facility_process_id, 270 primary_value) 271 SELECT distinct(p.process_id) as activity_process_id, 272 p.name AS activity_name, 273 p.description AS activity_description, 274 docf.facility_id AS facility_id, 275 docf.facility_name AS facility_name, 276 p2.process_id AS project_process_id, 277 p2.name AS project_name, 278 p.rstcd_result_id AS activity_result_id, 279 rc.name as result_name, 280 ppp.activity_user_id, 281 cu.last_name || '', '' || cu.first_name || '' '' || substr(cu.middle_name, 1, 1) AS activity_user_name, 282 cu.toc_tank_office_id AS user_tank_office_id, 283 p.prcsdf_process_def_id AS activity_def_id, 284 p.prstcd_process_status_id AS activity_status_id, 285 p.initiated_user_id AS activity_initiated_user_id, 286 p.initiated_date AS activity_initiated_date, 287 p.closed_user_id AS activity_closed_user_id, 288 p.closed_date AS activity_closed_date, 289 p.opened_user_id AS activity_opened_user_id, 290 p.opened_date AS activity_opened_date, 291 pa2.assigned_date AS activity_assigned_date, 292 pa2.assigned_user_id AS activity_assigned_user_id, 293 TO_NUMBER 294 (NVL ((SELECT pa.VALUE 295 FROM ewoc.process_attributes pa 296 WHERE pa.prcs_process_id = p.process_id 297 AND pa.pradf_process_attribute_def_id = 3), 0) 298 ) AS usts_inspected, 299 TO_NUMBER 300 (NVL ((SELECT pa.VALUE 301 FROM ewoc.process_attributes pa 302 WHERE pa.prcs_process_id = p.process_id 303 AND pa.pradf_process_attribute_def_id = 4), 0) 304 ) AS asts_inspected, 305 (SELECT CASE 306 WHEN VALUE IS NOT NULL AND VALUE <> ''CURRENT'' 307 THEN TO_DATE (VALUE, ''YYYYMMDD HH:MI:SS'') 308 ELSE NULL 309 END send_date 310 FROM ewoc.process_attributes pa 311 WHERE pa.prcs_process_id = p.process_id 312 AND pa.pradf_process_attribute_def_id = 38 313 and p.prcsdf_process_def_id = 11) AS ncl_send_date, 314 (SELECT CASE 315 WHEN VALUE IS NOT NULL AND VALUE <> ''CURRENT'' 316 THEN TO_DATE (VALUE, ''YYYYMMDD HH:MI:SS'') 317 ELSE NULL 318 END sent_date 319 FROM ewoc.process_attributes pa 320 WHERE pa.prcs_process_id = p.process_id 321 AND pa.pradf_process_attribute_def_id = 42 322 and p.prcsdf_process_def_id = 31) AS wl_sent_date, 323 docf.county_id as county_id, 324 docf.county_name as county_name, 325 docf.district_id as district_id, 326 docf.district_name as district_name, 327 docf.tank_office_id as faciltiy_tank_office_id, 328 docf.tank_office_name as facility_tank_office_name, 329 fp.prcs_process_id as facility_process_id, 330 pap.value as primary_value 331 FROM ewoc.processes p 332 join ewoc.process_attributes pa on p.process_id = pa.prcs_process_id 333 AND pa.value IS NOT NULL 334 AND pa.pradf_process_attribute_def_id = 10 335 join stcm.result_dist_ofc_county_facs docf on pa.value = TO_CHAR(docf.facility_id) 336 join ewoc.process_associations paa on p.process_id = paa.prcs_process_id_association 337 AND paa.astpcd_association_type_id = 2 338 join ewoc.processes p2 on p2.process_id = paa.prcs_process_id 339 join ewoc.process_assignments pa2 on p.process_id = pa2.prcs_process_id 340 join ( select pp.process_id, 341 (CASE WHEN pp.closed_user_id IS NOT NULL 342 THEN pp.closed_user_id 343 WHEN pa.assigned_user_id IS NOT NULL 344 THEN pa.assigned_user_id 345 WHEN pp.opened_user_id IS NOT NULL 346 THEN pp.opened_user_id 347 ELSE pp.initiated_user_id 348 END ) AS activity_user_id 349 from ewoc.processes pp , ewoc.process_assignments pa 350 where pp.process_id = pa.prcs_process_id) ppp on ppp.process_id = p.process_id 351 join stcm.client_users cu on cu.user_id = ppp.activity_user_id 352 left outer join ewoc.result_codes rc on p.rstcd_result_id = rc.result_id 353 join stcm.facility_processes fp on to_char(fp.fac_facility_id) = pa.value 354 left outer join ewoc.process_attributes pap on pap.prcs_process_id = p.process_id 355 and pap.pradf_process_attribute_def_id = 71 356 WHERE p.prtpcd_process_type_id = 3'; 357 EXECUTE IMMEDIATE 'CREATE INDEX stcm.rslt_fa_fpi_i ON stcm.result_facility_activities 358 (facility_process_id) 359 LOGGING 360 TABLESPACE STCM_INDEX 361 PCTFREE 10 362 INITRANS 2 363 MAXTRANS 255 364 STORAGE ( 365 INITIAL 160K 366 NEXT 80K 367 MINEXTENTS 1 368 MAXEXTENTS 505 369 PCTINCREASE 0 370 FREELISTS 1 371 FREELIST GROUPS 1 372 BUFFER_POOL DEFAULT 373 ) 374 NOPARALLEL'; 375 EXECUTE IMMEDIATE 'CREATE INDEX stcm.rslt_fa_acd_i ON stcm.result_facility_activities 376 (activity_closed_date) 377 LOGGING 378 TABLESPACE STCM_INDEX 379 PCTFREE 10 380 INITRANS 2 381 MAXTRANS 255 382 STORAGE ( 383 INITIAL 160K 384 NEXT 32K 385 MINEXTENTS 1 386 MAXEXTENTS 505 387 PCTINCREASE 0 388 FREELISTS 1 389 FREELIST GROUPS 1 390 BUFFER_POOL DEFAULT 391 ) 392 NOPARALLEL'; 393 EXECUTE IMMEDIATE 'CREATE INDEX stcm.rslt_fa_ci_i ON stcm.result_facility_activities 394 (county_id) 395 LOGGING 396 TABLESPACE STCM_INDEX 397 PCTFREE 10 398 INITRANS 2 399 MAXTRANS 255 400 STORAGE ( 401 INITIAL 160K 402 NEXT 32K 403 MINEXTENTS 1 404 MAXEXTENTS 505 405 PCTINCREASE 0 406 FREELISTS 1 407 FREELIST GROUPS 1 408 BUFFER_POOL DEFAULT 409 ) 410 NOPARALLEL'; 411 EXECUTE IMMEDIATE 'CREATE INDEX stcm.rslt_fa_di_i ON stcm.result_facility_activities 412 (district_id) 413 LOGGING 414 TABLESPACE STCM_INDEX 415 PCTFREE 10 416 INITRANS 2 417 MAXTRANS 255 418 STORAGE ( 419 INITIAL 160K 420 NEXT 32K 421 MINEXTENTS 1 422 MAXEXTENTS 505 423 PCTINCREASE 0 424 FREELISTS 1 425 FREELIST GROUPS 1 426 BUFFER_POOL DEFAULT 427 ) 428 NOPARALLEL'; 429 EXECUTE IMMEDIATE 'CREATE INDEX stcm.rslt_fa_adi_i ON stcm.result_facility_activities 430 (activity_def_id) 431 LOGGING 432 TABLESPACE STCM_INDEX 433 PCTFREE 10 434 INITRANS 2 435 MAXTRANS 255 436 STORAGE ( 437 INITIAL 160K 438 NEXT 32K 439 MINEXTENTS 1 440 MAXEXTENTS 505 441 PCTINCREASE 0 442 FREELISTS 1 443 FREELIST GROUPS 1 444 BUFFER_POOL DEFAULT 445 ) 446 NOPARALLEL'; 447 EXECUTE IMMEDIATE 'CREATE INDEX stcm.rslt_fa_aui_i ON stcm.result_facility_activities 448 (activity_user_id) 449 LOGGING 450 TABLESPACE STCM_INDEX 451 PCTFREE 10 452 INITRANS 2 453 MAXTRANS 255 454 STORAGE ( 455 INITIAL 240K 456 NEXT 32K 457 MINEXTENTS 1 458 MAXEXTENTS 505 459 PCTINCREASE 0 460 FREELISTS 1 461 FREELIST GROUPS 1 462 BUFFER_POOL DEFAULT 463 ) 464 NOPARALLEL'; 465 /******************************************************************************* 466 * stcm.result_violations 467 *******************************************************************************/ 468 EXECUTE IMMEDIATE 'INSERT INTO stcm.result_violations 469 (violation_process_id, 470 facility_process_id, 471 district_id, 472 district_name, 473 facility_tank_office_id, 474 facility_tank_office_name, 475 county_id, 476 county_name, 477 facility_id, 478 facility_name, 479 initiated_process_id, 480 initiated_date, 481 viocd_violation_id, 482 activity_result, 483 violation_significance, 484 violation_status, 485 initiated_activity_name, 486 initiated_activity_closed_date, 487 resolved_date, 488 resolved_process_id, 489 resolving_activity_name, 490 explanation, 491 corrective_action, 492 initiated_user_id, 493 resolved_user_id, 494 max_ncl_sent_date, 495 max_wl_sent_date, 496 reinspection_date) 497 SELECT p2.process_id AS violation_process_id, 498 fp.process_id AS facility_process_id, 499 docf.district_id, docf.district_name, docf.tank_office_id AS facility_tank_office_id, 500 docf.tank_office_name AS facility_tank_office_name, docf.county_id, docf.county_name, 501 fp.facility_id AS facility_id, docf.facility_name, 502 pai.prcs_process_id AS initiated_process_id, 503 p2.initiated_date AS initiated_date, v.value AS viocd_violation_id, 504 rc.name AS activity_result, sc.name AS violation_significance, 505 psc.name AS violation_status, 506 p3.name AS initiated_activity_name, 507 p3.closed_date AS initiated_activity_closed_date, 508 p2.closed_date AS resolved_date, 509 par.prcs_process_id AS resolved_process_id, 510 p4.name as resolving_activity_name, 511 (SELECT pa.value 512 FROM ewoc.process_attributes pa 513 WHERE pa.prcs_process_id = p2.process_id 514 AND pa.pradf_process_attribute_def_id = 8) AS explanation, 515 (SELECT pa.value 516 FROM ewoc.process_attributes pa 517 WHERE pa.prcs_process_id = p2.process_id 518 AND pa.pradf_process_attribute_def_id = 9) AS corrective_action, 519 p2.initiated_user_id AS initiated_user_id, 520 p2.closed_user_id AS resolved_user_id, 521 (SELECT MAX (npa.ncl_send_date) 522 FROM stcm.result_facility_activities npa 523 WHERE npa.facility_process_id = fp.process_id 524 AND npa.ncl_send_date BETWEEN p2.initiated_date AND NVL (p2.closed_date, SYSDATE)) max_ncl_sent_date, 525 (SELECT MAX (wpa.wl_sent_date) 526 FROM stcm.result_facility_activities wpa 527 WHERE wpa.facility_process_id = fp.process_id 528 AND wpa.wl_sent_date BETWEEN p2.initiated_date AND NVL (p2.closed_date, SYSDATE)) max_wl_sent_date, 529 (SELECT MAX (rfa.activity_closed_date) 530 FROM stcm.result_facility_activities rfa,ewoc.process_attributes pa 531 WHERE rfa.facility_process_id = fp.process_id 532 and rfa.activity_process_id = pa.prcs_process_id 533 and pa.pradf_process_attribute_def_id in (34,61) 534 and pa.value like ''%Primary%'' 535 AND rfa.activity_closed_date BETWEEN p2.initiated_date AND NVL (p2.closed_date, SYSDATE)) reinspection_date 536 FROM stcm.result_facility_processes fp 537 JOIN ewoc.processes p1 ON p1.process_id = fp.process_id AND p1.prtpcd_process_type_id = 1 538 JOIN ewoc.process_associations pa12 ON p1.process_id = pa12.prcs_process_id 539 AND pa12.astpcd_association_type_id = 4 540 JOIN ewoc.processes p2 ON pa12.prcs_process_id_association = p2.process_id 541 AND p2.prtpcd_process_type_id = 5 542 JOIN 543 (SELECT pa.VALUE, pa.prcs_process_id 544 FROM ewoc.process_attributes pa 545 WHERE pa.pradf_process_attribute_def_id = 7) v 546 ON v.prcs_process_id = p2.process_id 547 LEFT OUTER JOIN 548 (SELECT pa.prcs_process_id_association, pa.prcs_process_id 549 FROM ewoc.process_associations pa 550 WHERE pa.astpcd_association_type_id = 6) pai 551 ON p2.process_id = pai.prcs_process_id_association 552 LEFT OUTER JOIN ewoc.processes p3 ON p3.process_id = pai.prcs_process_id 553 LEFT OUTER JOIN 554 (SELECT pa.prcs_process_id_association, pa.prcs_process_id 555 FROM ewoc.process_associations pa 556 WHERE pa.astpcd_association_type_id = 5) par ON p2.process_id = par.prcs_process_id_association 557 LEFT OUTER JOIN ewoc.processes p4 on p4.process_id = par.prcs_process_id 558 JOIN stcm.violation_codes vc ON v.value = vc.violation_id 559 JOIN stcm.significance_codes sc ON vc.sigcd_significance_id = sc.significance_id 560 LEFT OUTER JOIN ewoc.result_codes rc ON rc.result_id = p3.rstcd_result_id 561 JOIN ewoc.process_status_codes psc ON p2.prstcd_process_status_id = psc.process_status_id 562 JOIN stcm.result_dist_ofc_county_facs docf ON fp.facility_id = docf.facility_id'; 563 564 EXECUTE IMMEDIATE 'CREATE INDEX stcm.rslt_v_rd_i ON stcm.result_violations 565 (resolved_date) 566 LOGGING 567 TABLESPACE STCM_INDEX 568 PCTFREE 10 569 INITRANS 2 570 MAXTRANS 255 571 STORAGE ( 572 INITIAL 120K 573 NEXT 32K 574 MINEXTENTS 1 575 MAXEXTENTS 505 576 PCTINCREASE 0 577 FREELISTS 1 578 FREELIST GROUPS 1 579 BUFFER_POOL DEFAULT 580 ) 581 NOPARALLEL'; 582 EXECUTE IMMEDIATE 'CREATE INDEX stcm.rslt_v_ci_i ON stcm.result_violations 583 (county_id) 584 LOGGING 585 TABLESPACE STCM_INDEX 586 PCTFREE 10 587 INITRANS 2 588 MAXTRANS 255 589 STORAGE ( 590 INITIAL 360K 591 NEXT 64K 592 MINEXTENTS 1 593 MAXEXTENTS 505 594 PCTINCREASE 0 595 FREELISTS 1 596 FREELIST GROUPS 1 597 BUFFER_POOL DEFAULT 598 ) 599 NOPARALLEL'; 600 EXECUTE IMMEDIATE 'CREATE INDEX stcm.rslt_v_di_i ON stcm.result_violations 601 (district_id) 602 LOGGING 603 TABLESPACE STCM_INDEX 604 PCTFREE 10 605 INITRANS 2 606 MAXTRANS 255 607 STORAGE ( 608 INITIAL 360K 609 NEXT 64K 610 MINEXTENTS 1 611 MAXEXTENTS 505 612 PCTINCREASE 0 613 FREELISTS 1 614 FREELIST GROUPS 1 615 BUFFER_POOL DEFAULT 616 ) 617 NOPARALLEL'; 618 EXECUTE IMMEDIATE 'CREATE INDEX stcm.rslt_v_ftoi_i ON stcm.result_violations 619 (facility_tank_office_id) 620 LOGGING 621 TABLESPACE STCM_INDEX 622 PCTFREE 10 623 INITRANS 2 624 MAXTRANS 255 625 STORAGE ( 626 INITIAL 520K 627 NEXT 64K 628 MINEXTENTS 1 629 MAXEXTENTS 505 630 PCTINCREASE 0 631 FREELISTS 1 632 FREELIST GROUPS 1 633 BUFFER_POOL DEFAULT 634 ) 635 NOPARALLEL'; 636 EXCEPTION 637 WHEN OTHERS THEN 638 raise_application_error (-20012, SQLERRM); 639 640 END refresh_result_tables; 641 / Procedure created. SQL> SQL> GRANT EXECUTE ON STCM.refresh_result_tables TO FIRST_ADMIN; Grant succeeded. SQL> SQL> GRANT EXECUTE ON STCM.refresh_result_tables TO FIRST_QUERY_ROLE; Grant succeeded. SQL> SQL> SQL> SELECT systimestamp FROM dual; 07-JUL-06 05.39.44.507124 PM -04:00 1 row selected. SQL> -------------------- SQL> spool off