CREATE OR REPLACE PROCEDURE
"*****"

-- ================================================================
-- | THIS PROCEDURE RETRIEVES THE INFORMATION TO BE DISPLAYED IN THE DOCKET |
-- | BY JIM DUNNE, jim@dunnes.net                                                                      |
-- | COPYRIGHT © 2006-2007                                                                             |
-- ================================================================

(
 DATEFROM IN VARCHAR2,
 DATETO IN VARCHAR2,
 JUDGEID IN VARCHAR2,
 SECONDJUDGEID IN VARCHAR2,
 CASETYPE IN VARCHAR2,
 HEARINGID IN VARCHAR2,
 SECONDHEARINGID IN VARCHAR2,
 THIRDHEARINGID IN VARCHAR2,
 FOURTHHEARINGID IN VARCHAR2,
 FIFTHHEARINGID IN VARCHAR2, 
 TIMEFROM IN VARCHAR2,
 TIMETO IN VARCHAR2,
 PROSECUTORINPUT IN VARCHAR2,
 ADDRESSYN IN VARCHAR2,
 CUR_RETURNDATA OUT SYS_REFCURSOR
)

AS

Type_  VARCHAR2(20);
AttorneyId  NUMBER(10,0);
CaseScheduleId  NUMBER(10,0);
CaseNumber  VARCHAR2(255);
FromDate  DATE;
FromTime DATE;
Room  VARCHAR2(30);
JudgesLastName  VARCHAR2(20);
Caption  VARCHAR2(255);
Hearing  VARCHAR2(30);
Attorney  VARCHAR2(255);
AttorneyAll  VARCHAR2(255);
AttorneyLastName VARCHAR2(42);
AttorneyFirstName VARCHAR2(42);
AttorneyCount  NUMBER(10,0);
Notes VARCHAR2(50);
ProsecutorLastName VARCHAR2(20);
ProsecutorId NUMBER(5,0);
ViolationDescription VARCHAR2(255);
i VARCHAR2(255);
ViolationDescriptionAll VARCHAR2(255);
ViolationNumber VARCHAR2(1000);
ViolationNumberAll VARCHAR2(1000);
OfficerLastName VARCHAR2(20);
CivilCaseID NUMBER(10,0);
CivilDefendants VARCHAR2(1000);
CivilPlaintiff VARCHAR2(1000);
PlaintiffCount  NUMBER(10,0);
DefendantName VARCHAR2(255);
DefendantInfo VARCHAR2(1000);
DefendantCount  NUMBER(10,0);
DefendantAddress VARCHAR2(90);
ViolatorSSN CHAR(9);
SelectText VARCHAR2(4000);
CaseViolationID VARCHAR2(14);
ViolationCount NUMBER(10,0);
PublicInfoZip  VARCHAR2(10);
PublicInfoCity  VARCHAR2(30);
PublicInfoState  VARCHAR2(5);
PublicInfoStreet  VARCHAR2(80);
CheckString VARCHAR2(1000);
CheckStringCount NUMBER(10,0);
CheckStringTemp1 VARCHAR2(255);
CheckStringTemp2 VARCHAR2(255);

Temp1000Char VARCHAR2 (1000);
Temp255Char VARCHAR2 (255);
Temp255Char1 VARCHAR2 (255);
Temp255Char2 VARCHAR2 (255);

TYPE MYCURSOR IS REF CURSOR ;
CUR_RESULTS MYCURSOR;
CUR_TEMP MYCURSOR;
CUR_VIOLATIONS MYCURSOR;
CUR_CIVILDEFENDANTS MYCURSOR;
CUR_CIVILPLAINTIFFS MYCURSOR;

TYPE VDType IS TABLE OF NUMBER
  INDEX BY VARCHAR2(255);
array_ViolationDescription VDType;

BEGIN

--BEGIN DYNAMICALLY CONSTRUCTING THE SELECT QUERY BASED ON INPUT PARAMETERS
SelectText := 'SELECT CASE_SCHEDULES.CASE_SCHEDULE_ID,
                               CASE_SCHEDULES.DATE_FROM,
                               CASE_SCHEDULES.DATE_FROM,
                               CASE_SCHEDULES.CASE_ID,
                               HEARINGS.HEARING,
                               JUDGES.LAST_NAME,
                               COURT_ROOMS.ROOM,
                               CASE_SCHEDULES.PROSECUTOR_ID,
                               CASE_SCHEDULES.ATTORNEY_ID,
                               CASE_SCHEDULES.TYPE_,
                               INITCAP(OFFICERS.LAST_NAME)
                    FROM JUDGES
                    INNER JOIN CASE_SCHEDULES ON JUDGES.JUDGE_ID = CASE_SCHEDULES.JUDGE_ID
                    INNER JOIN HEARINGS ON CASE_SCHEDULES.HEARING_ID = HEARINGS.HEARING_ID
                    INNER JOIN COURT_ROOMS ON CASE_SCHEDULES.COURT_ROOM_ID = COURT_ROOMS.COURT_ROOM_ID
                    LEFT JOIN OFFICERS ON CASE_SCHEDULES.OFFICER_ID = OFFICERS.OFFICER_ID
                    WHERE (CASE_SCHEDULES.DATE_FROM > TO_DATE(''' || DATEFROM || ''', ''DD/MM/YYYY'') AND
                      (CASE_SCHEDULES.DATE_FROM < (TO_DATE(''' || DATETO || ''', ''DD/MM/YYYY'')) + 1)) AND
                      (CASE_SCHEDULES.TYPE_ LIKE ''' || CASETYPE || ''')';

--SELECT FOR A CERTAIN JUDGE
SelectText := SelectText ||   ' AND ((JUDGES.JUDGE_ID LIKE ''' || JUDGEID || ''')';
--IF A SECOND JUDGE IS SPECIFIED, SELECT FOR THAT JUDGE, TOO
IF SECONDJUDGEID IS NOT NULL THEN
 SelectText := SelectText || ' OR (JUDGES.JUDGE_ID LIKE ''' || SECONDJUDGEID || '''))';
--ELSE, END THIS PART OF THE QUERY
ELSE
 SelectText := SelectText ||')';
END IF;

--SELECT FOR A CERTAIN HEARING TYPE
SelectText := SelectText || ' AND ((HEARINGS.HEARING_ID LIKE ''' || HEARINGID || ''')';

--IF A SECOND HEARING TYPE IS SPECIFIED, SELECT FOR THAT HEARING TYPE, TOO
IF SECONDHEARINGID IS NOT NULL THEN
  SelectText := SelectText || ' OR (HEARINGS.HEARING_ID LIKE ''' || SECONDHEARINGID || ''')';
--ELSE, END THIS PART OF THE QUERY (SPEEDS THINGS UP BY AVOIDING THE NEXT THREE IF/THENS)
ELSE GOTO Hearing_Finish;
END IF;
--IF A THIRD HEARING TYPE IS SPECIFIED, SELECT FOR THAT HEARING TYPE, TOO
IF THIRDHEARINGID IS NOT NULL THEN
  SelectText := SelectText || ' OR (HEARINGS.HEARING_ID LIKE ''' || THIRDHEARINGID || ''')';
--ELSE, END THIS PART OF THE QUERY
ELSE GOTO Hearing_Finish;
END IF;
--IF A FOURTH HEARING TYPE IS SPECIFIED, SELECT FOR THAT HEARING TYPE, TOO
IF FOURTHHEARINGID IS NOT NULL THEN
  SelectText := SelectText || ' OR (HEARINGS.HEARING_ID LIKE ''' || FOURTHHEARINGID || ''')';
--ELSE, END THIS PART OF THE QUERY
ELSE GOTO Hearing_Finish;
END IF;
--IF A FIFTH HEARING TYPE IS SPECIFIED, SELECT FOR THAT HEARING TYPE, TOO
IF FIFTHHEARINGID IS NOT NULL THEN
  SelectText := SelectText || ' OR (HEARINGS.HEARING_ID LIKE ''' || FIFTHHEARINGID || ''')';
END IF;

<<Hearing_Finish>>
--END THE HEARING TYPE SELECT STATEMENT
SelectText := SelectText || ')';

--ADD THE PROSECUTOR OPTION TO THE SELECT QUERY. PROSECUTORS SOMETIMES SHOW UP WITH MORE
--THAN ONE ID NUMBER IN THE DATABASE, SO HAVE TO SPECIFY ID'S MANUALLY.
IF PROSECUTORINPUT  = 'Fleming' THEN
  SelectText := SelectText ||  ' AND ((CASE_SCHEDULES.PROSECUTOR_ID = ''1''))';
ELSIF PROSECUTORINPUT  = 'Coughlin' THEN
  SelectText := SelectText ||  ' AND ((CASE_SCHEDULES.PROSECUTOR_ID = ''3''))';
ELSIF PROSECUTORINPUT  = 'McGinnis' THEN
  SelectText := SelectText ||  ' AND ((CASE_SCHEDULES.PROSECUTOR_ID = ''2''))';
ELSIF PROSECUTORINPUT  = 'Spears' THEN
  SelectText := SelectText ||  ' AND ((CASE_SCHEDULES.PROSECUTOR_ID = ''10'')
                                           OR (CASE_SCHEDULES.PROSECUTOR_ID = ''87''))';
ELSIF PROSECUTORINPUT  = 'Dundes' THEN
  SelectText := SelectText ||  ' AND ((CASE_SCHEDULES.PROSECUTOR_ID = ''67''))';
ELSIF PROSECUTORINPUT  = 'Brunk' THEN
  SelectText := SelectText ||  ' AND ((CASE_SCHEDULES.PROSECUTOR_ID = ''68''))';
END IF;

--IF THE USER SPECIFIED A START TIME, ADD IT TO THE SELECT QUERY
IF TIMEFROM IS NOT NULL THEN
  SelectText := SelectText ||
                       ' AND (TO_CHAR(CASE_SCHEDULES.DATE_FROM,''HH24:MI'') >= ''' || TIMEFROM || ''') AND
                        (TO_CHAR(CASE_SCHEDULES.DATE_FROM,''HH24:MI'') <= ''' || TIMETO || ''')';
END IF;

--CLEAR THE GLOBAL TEMPORARY TABLE
EXCUTE IMMEDIATE 'TRUNCATE TABLE SA.GTT_RETURN';

--OPEN CUR_RESULTS USING THE SELECT QUERY JUST GENERATED
OPEN CUR_RESULTS FOR SelectText;
LOOP 
    --CLEAR VARIABLES
    CaseScheduleId := '';
    FromDate := '';
    FromTime := '';
    CaseNumber := '';
    Caption := '';
    Hearing := '';
    JudgesLastName := '';
    Room := '';
    ProsecutorLastName := '';
    AttorneyId := '';
    Type_ := '';
    OfficerLastName := '';
    DefendantInfo := '';
    ViolationDescription := '';
    ViolationNumber := '';
    CaseViolationID := '';
    ViolationDescriptionAll := '';
    ViolationNumberAll := '';

    --PUT THE CURSOR CONTENTS INTO VARIABLES
    FETCH CUR_RESULTS
    INTO CaseScheduleId,
            FromDate,
            FromTime,
            CaseNumber,
            Hearing,
            JudgesLastName,
            Room,
            ProsecutorId,
            AttorneyId,
            Type_,
            OfficerLastName;
    EXIT WHEN CUR_RESULTS%NOTFOUND;

   --IF THIS IS A TRAFFIC/CRIMINAL CASE
   IF SUBSTR(Type_,1,1) = 'T' THEN
    --IF A PROSECUTOR ID EXISTS
    IF ProsecutorId IS NOT NULL THEN
     --GET THE PROSECUTOR'S LAST NAME
     SELECT INITCAP(LAST_NAME)
     INTO ProsecutorLastName
     FROM PROSECUTORS
     WHERE PROSECUTOR_ID = ProsecutorId;
    END IF;
            
    --GET THE VIOLATOR'S SSN
    SELECT (TO_CHAR(TRAFFIC_CRIMINAL_CASES.VIOLATOR_SSN))
    INTO ViolatorSSN
    FROM  TRAFFIC_CRIMINAL_CASES
    --GET JUST THE CASE NUMBER PART OF THE CASE VIOLATION ID
     WHERE TRAFFIC_CRIMINAL_CASES.CASE_NUMBER = SUBSTR(CaseNumber, LENGTH(CaseNumber)-11,12);

    --CLEAR THE ADDRESS VARIABLES
    PublicInfoStreet := '';
    PublicInfoCity := '';
    PublicInfoState := '';
    PublicInfoZip := '';

    --GET THE VIOLATOR'S PERSONAL INFORMATION
    SELECT ('<b>' || PUBLIC_INFO.LAST_NAME || ', ' ||
               PUBLIC_INFO.FIRST_NAME || ' ' || PUBLIC_INFO.MIDDLE_INITIAL || '</b>'),
               PUBLIC_INFO.STREET,
               PUBLIC_INFO.CITY,
               PUBLIC_INFO.STATE,
               PUBLIC_INFO.ZIP
    INTO DefendantName,
            PublicInfoStreet,
            PublicInfoCity,
            PublicInfoState,
            PublicInfoZip
    FROM PUBLIC_INFO
    WHERE ViolatorSSN = PUBLIC_INFO.SSN;

    --CONSTRUCT THE ADDRESS STRING
     IF PublicInfoStreet IS NOT NULL THEN
     --ADD STREET TO THE ADDRESS
     DefendantAddress := PublicInfoStreet;
    END IF;
    --IF CITY EXISTS, ADD CITY, STATE, ZIP TO ADDRESS STRING
     IF PublicInfoCity IS NOT NULL THEN
     DefendantAddress := DefendantAddress || '<br>' || PublicInfoCity || ', ' || PublicInfoState || ' ' || PublicInfoZip;
    END IF;

    --IF THE USER WANTS THE DEFENDANT'S ADDRESS TO SHOW ON THE DOCKET
     IF ADDRESSYN = 'True' THEN
     --ADD AN HTML BREAK AND THEN THE ADDRESS TO THE DEFENDANT'S INFORMATION STRING
      DefendantInfo := UPPER(DefendantName || '<br>' || DefendantAddress);
    --ELSE, JUST USE DEFENDANT'S NAME
    ELSE
     DefendantInfo := UPPER(DefendantName);
    END IF;

    --GET THE VIOLATION INFORMATION FOR EACH VIOLATION ON THIS CASE
     OPEN CUR_VIOLATIONS FOR
     SELECT VIOLATION_CODES.VIOLATION_DESCRIPTION,
                VIOLATION_CODES.SECTION_NUMBER,
                CASE_VIOLATION_ID
     FROM VIOLATION_CODES
     INNER JOIN CASE_VIOLATIONS ON CASE_VIOLATIONS.VIOLATION_ID = VIOLATION_CODES.VIOLATION_ID
     WHERE CASE_VIOLATIONS.CASE_NUMBER = SUBSTR(CaseNumber, LENGTH(CaseNumber)-11,12)
     ORDER BY CASE_VIOLATION_ID;

    --SET THE VIOLATION COUNT TO 0
     ViolationCount := 0;

    --FETCH THE CURSOR INFORMATION INTO AN ARRAY
     LOOP
     FETCH CUR_VIOLATIONS
     INTO ViolationDescription,
             ViolationNumber,
             CaseViolationID;
    --STOP LOOPING WHEN NO MORE ROWS ARE IN THE CURSOR
     EXIT WHEN CUR_VIOLATIONS%NOTFOUND;
     --MEANWHILE, IF THIS IS THE SECOND OR GREATER VIOLATION
      IF ViolationCount > 0 THEN
       --IF THIS PARTICULAR VIOLATION DESCRIPTION ALREADY EXISTS IN THE ARRAY
       IF array_ViolationDescription.EXISTS(ViolationDescription)
        --INCREMENT THE NUMBER IN THE ARRAY BY 1
       THEN
       array_ViolationDescription(ViolationDescription) := array_ViolationDescription(ViolationDescription) + 1;
        --ELSE, ADD THIS DESCRIPTION TO THE ARRAY, WITH AN INITIAL COUNT OF 1
       ELSE
       array_ViolationDescription(ViolationDescription) := 1;
     --ADD AN HTML LINE BREAK AND THE VIOLATION SECTION NUMBER TO THE AGGREGATE STRING ViolationNumberAll
     ViolationNumberAll := UPPER(ViolationNumberAll  || '<br>' || ViolationNumber);
    END IF;
  --ELSE, THERE IS ONLY 1 VIOLATION IN THE CURSOR
  ELSE
    --ADD VIOLATION DESCRIPTION TO THE ARRAY WITH CORRESPONDING NUMBER 1
    array_ViolationDescription(ViolationDescription) := 1;
    --ADD THE UPPER CASE VIOLATION DESCRIPTION TO THE STRING ViolationDescriptionAll
    ViolationDescriptionAll := UPPER(ViolationDescription);
    --ADD THE UPPER CASE VIOLATION SECTION NUMBER TO THE STRING ViolationNumberAll
   ViolationNumberAll := UPPER(ViolationNumber);
  END IF;
   --INCREMENT THE VIOLATION COUNTER BY 1
   ViolationCount := ViolationCount + 1;

   --RESET THE VARIABLES TO NULL
   CaseViolationID := '';
  ViolationDescription := '';
  ViolationNumber := '';

   END LOOP;

   --SET THE ARRAY POSITION TO THE FIRST ITEM IN THE ARRAY
  i := array_ViolationDescription.FIRST;

   --BEGIN LOOP TO PARSE ARRAY
   LOOP
  --EXIT THE LOOP WHEN THERE ARE NO MORE ITEMS IN THE ARRAY
   EXIT WHEN NOT array_ViolationDescription.EXISTS(i);
    --IF THIS IS THE FIRST ITEM IN THE ARRAY
     IF i = array_ViolationDescription.FIRST THEN
     --IF THERE ARE MULTIPLES OF THE SAME VIOLATION ON THIS CASE
      IF array_ViolationDescription(i) > 1 THEN
      --ADD THE VIOLATION DESCRIPTION TO THE AGGREGATE STRING AS "DESCRIPTION (NUMBER OF OCCURRENCES)"
       ViolationDescriptionAll := UPPER(i || ' (' || TO_CHAR(array_ViolationDescription(i)) || ')');
     ELSE --THERE IS ONLY ONE VIOLATION DESCRIPTION LIKE THIS ON THIS CASE
       --ADD THE VIOLATION DESCRIPTION TO THE AGGREGATE STRING
       ViolationDescriptionAll := UPPER(i);
     END IF;
    ELSE--THIS IS NOT THE FIRST ITEM IN THE ARRAY
      --IF THERE ARE MULTIPLES OF THE SAME VIOLATION ON THIS CASE
      IF array_ViolationDescription(i) > 1 THEN
      --ADD AN HTML LINE BREAK AND THE VIOLATION DESCRIPTION TO THE AGGREGATE STRING
       --AS "DESCRIPTION (NUMBER OF OCCURRENCES)"
       ViolationDescriptionAll := UPPER(ViolationDescriptionAll  || '<br>' || i || ' (' ||TO_CHAR(array_ViolationDescription(i)) || ')');
     ELSE--THERE IS ONLY ONE VIOLATION DESCRIPTION LIKE THIS ON THIS CASE
      --ADD AN HTML LINE BREAK AND THE VIOLATION DESCRIPTION TO THE AGGREGATE STRING
      ViolationDescriptionAll := UPPER(ViolationDescriptionAll  || '<br>' || i);
     END IF;
    END IF;
   --INCREMENT THE ARRAY POSITION TO LOOK AT THE NEXT ITEM IN THE ARRAY
    i := array_ViolationDescription.NEXT(i);
   --END LOOPING THROUGH THE ARRAY
    END LOOP;
   --DELETE THE ARRAY
   array_ViolationDescription.DELETE;

  --END OF TRAFFIC/CRIMINAL IF/THEN
   END IF;

   --IF THIS IS A CIVIL CASE
   IF SUBSTR(Type_,1,1) = 'C' THEN

   --CLEAR VARIABLES
   CivilPlaintiff := '';
  CivilDefendants := '';
  PlaintiffCount := 0;
  DefendantCount := 0;

   --PUT PLAINTIFF INFO INTO CUR_CIVILPLAINTIFFS
   OPEN CUR_CIVILPLAINTIFFS FOR
    SELECT LAST_NAME,
               FIRST_NAME,
               MIDDLE_INITIAL
    FROM PUBLIC_INFO
    INNER JOIN PLAINTIFFS ON PLAINTIFFS.PLAINTIFF_SSN = PUBLIC_INFO.SSN
    INNER JOIN CIVIL_CASES ON CIVIL_CASES.CIVIL_CASE_ID = PLAINTIFFS.CIVIL_CASE_ID
    WHERE CaseNumber = CIVIL_CASES.CASE_NUMBER;

     --BEGIN LOOP TO FORMAT PLAINTIFF STRING
     LOOP
      --FETCH THE INFO IN CUR_CIVILPLAINTIFFS INTO THE TEMPORARY VARIABLES
      FETCH CUR_CIVILPLAINTIFFS
     INTO Temp255Char,
            Temp255Char1,
            Temp255Char2;

    --STOP LOOPING WHEN THERE ARE NO MORE ROWS IN THE CURSOR
     EXIT WHEN CUR_CIVILPLAINTIFFS%NOTFOUND;

     --PUT THE PLAINTIFF INFO INTO Temp1000Char
      Temp1000Char := UPPER(Temp255Char1 || ' ' || Temp255Char2 || ' ' || Temp255Char);

      --IF Temp1000Char > 35 CHARACTERS LONG
      IF LENGTH(Temp1000Char) > 35 THEN
       --SET THE STRING PLACE COUNTER
       CheckStringCount := 35;
       --WHILE THE STRING PLACE COUNTER IS LESS THAN THE LENGTH OF THE PLAINTIFF'S NAME
       WHILE CheckStringCount < LENGTH(Temp1000Char)
       LOOP
          --IF THERE IS A SPACE AT THE PLACE COUNTER POSITION
          IF SUBSTR(Temp1000Char, CheckStringCount, 1) = ' ' THEN
           --CREATE A SUBSTRING FROM THE BEGINNING TO RIGHT BEFORE THE SPACE, WITH AN HTML LINE BREAK
           CheckStringTemp1 := SUBSTR(Temp1000Char, 1, CheckStringCount - 1) || '<br>';
           --CREATE A SUBSTRING FROM RIGHT AFTER THE SPACE TO THE END OF THE LINE
           CheckStringTemp2 := SUBSTR(Temp1000Char, CheckStringCount + 1,  LENGTH(Temp1000Char) - CheckStringCount);
         --IF THE SECOND SUBSTRING IS LONGER THAN 35 CHARACTERS, WANT TO BREAK THE LINE
         --AT THE NEXT SPACE AFTER 35 CHARACTERS
         IF LENGTH(CheckStringTemp2) > 35 THEN
          --SET STRING LENGTH COUNTER
          CheckStringCount2 := 35;
          --WHILE THE STRING LENGTH COUNTER IS LESS THAN THE STRING LENGTH
          WHILE CheckStringCount2 < LENGTH(CheckStringTemp2)
           LOOP
            --IF THERE IS A SPACE AT THE PLACE COUNTER POSITION
            IF SUBSTR(CheckStringTemp2, CheckStringCount2, 1) = ' ' THEN
             --CREATE A SUBSTRING FROM THE BEGINNING TO RIGHT BEFORE THE SPACE, WITH AN HTML LINE BREAK
             CheckStringTemp3 := SUBSTR(CheckStringTemp2, 1, CheckStringCount2 - 1) || '<BR> ';
             --CREATE A SUBSTRING FROM RIGHT AFTER THE SPACE TO THE END OF THE LINE
             CheckStringTemp4 := SUBSTR(CheckStringTemp2, CheckStringCount2 + 1, LENGTH(CheckStringTemp2) - CheckStringCount2);
             --THE SECOND SUBSTRING IS THE COMBINATION OF SUBSTRINGS 3 AND 4
             CheckStringTemp2 := CheckStringTemp3 || CheckStringTemp4;
             --ALL DONE, EXIT THE LOOP
             EXIT;
            END IF;
            --INCREMENT THE STRING PLACE COUNTER BY 1
            CheckStringCount2 := CheckStringCount2 + 1;
           END LOOP;
         --END THE CheckStringTemp2 IF/THEN LENGTH CHECKER
         END IF;

           --THE TOTAL PLAINTIFF NAME IS THESE TWO SUBSTRINGS COMBINED
           Temp1000Char := CheckStringTemp1 || CheckStringTemp2;
           --EXIT THE LOOP
           EXIT;
        END IF;

          --INCREMENT THE STRING PLACE COUNTER BY 1
          CheckStringCount := CheckStringCount + 1;
         --END THE PLAINTIFF STRING FORMAT LOOP
         END LOOP;
        --END THE PLAINTIFF STRING FORMAT IF/THEN
        END IF;

        --IF THIS IS THE FIRST PLAINTIFF
        IF PlaintiffCount = 0 THEN
         --SET THE CIVIL PLAINTIFF AGGREGATE STRING EQUAL TO Temp1000Char
         CivilPlaintiff := Temp1000Char;
      ELSIF PlaintiffCount > 0 THEN --THIS IS THE SECOND OR GREATER PLAINTIFF
         --ADD AN UNBOLDED, ITALICIZED "and" PLUS AN HTML LINE BREAK PLUS THE PLAINTIFF NAME TO THE AGGREGATE STRING
         CivilPlaintiff := UPPER(CivilPlaintiff) || ' </B><I>and</I><B><BR>' || Temp1000Char;
      END IF;

        --INCREMENT THE PLAINTIFF COUNTER BY 1
      PlaintiffCount := PlaintiffCount + 1;
        --END LOOP TO FORMAT PLAINTIFF STRING
        END LOOP;

        --SET THE DEFENDANT COUNT TO 0
        DefendantCount := 0;

        --PUT DEFENDANT INFO INTO CUR_CIVILDEFENDANTS
        OPEN CUR_CIVILDEFENDANTS FOR
          SELECT LAST_NAME,
                    FIRST_NAME,
                    MIDDLE_INITIAL
          FROM PUBLIC_INFO
          INNER JOIN DEFENDANTS
        ON DEFENDANTS.DEFENDANT_SSN = PUBLIC_INFO.SSN
        INNER JOIN CIVIL_CASES
        ON CIVIL_CASES.CIVIL_CASE_ID = DEFENDANTS.CIVIL_CASE_ID
          WHERE CaseNumber = CIVIL_CASES.CASE_NUMBER;

         --BEGIN LOOP TO FORMAT DEFENDANT STRING
         LOOP
          --FETCH THE INFO IN CUR_CIVILDEFENDANTS INTO THE TEMPORARY VARIABLES
          FETCH CUR_CIVILDEFENDANTS
          INTO Temp255Char,
                Temp255Char1,
                Temp255Char2;
         --STOP LOOPING WHEN THERE ARE NO MORE ROWS IN THE CURSOR
         EXIT WHEN CUR_CIVILDEFENDANTS%NOTFOUND;
          --PUT THE DEFENDANT INFO INTO Temp1000CharT
        Temp1000Char := UPPER(Temp255Char1 || ' ' || Temp255Char2 || ' ' || Temp255Char);
          --IF Temp1000Char > 35 CHARACTERS LONG
          IF LENGTH(Temp1000Char) > 35 THEN
           --SET THE STRING PLACE COUNTER
           CheckStringCount := 35;
           --WHILE THE STRING PLACE COUNTER IS LESS THAN THE LENGTH OF THE DEFENDANT'S NAME
           WHILE CheckStringCount < LENGTH(Temp1000Char)
            LOOP
             --IF THERE IS A SPACE AT THE PLACE COUNTER POSITION
             IF SUBSTR(Temp1000Char, CheckStringCount, 1) = ' ' THEN
              --CREATE A SUBSTRING FROM THE BEGINNING TO RIGHT BEFORE THE SPACE, WITH AN HTML LINE BREAK
              CheckStringTemp1 := SUBSTR(Temp1000Char, 1, CheckStringCount - 1) || '<br>';
              --CREATE A SUBSTRING FROM RIGHT AFTER THE SPACE TO THE END OF THE LINE
              CheckStringTemp2 := SUBSTR(Temp1000Char, CheckStringCount + 1,  LENGTH(Temp1000Char) - CheckStringCount);
            --IF THE SECOND SUBSTRING IS LONGER THAN 35 CHARACTERS, WANT TO BREAK THE LINE
            --AT THE NEXT SPACE AFTER 35 CHARACTERS
            IF LENGTH(CheckStringTemp2) > 35 THEN
             --SET STRING LENGTH COUNTER
             CheckStringCount2 := 35;
             --WHILE THE STRING LENGTH COUNTER IS LESS THAN THE STRING LENGTH
             WHILE CheckStringCount2 < LENGTH(CheckStringTemp2)
              LOOP
               --IF THERE IS A SPACE AT THE PLACE COUNTER POSITION
               IF SUBSTR(CheckStringTemp2, CheckStringCount2, 1) = ' ' THEN
                --CREATE A SUBSTRING FROM THE BEGINNING TO RIGHT BEFORE THE SPACE, WITH AN HTML LINE BREAK
                CheckStringTemp3 := SUBSTR(CheckStringTemp2, 1, CheckStringCount2 - 1) || '<BR> ';
                --CREATE A SUBSTRING FROM RIGHT AFTER THE SPACE TO THE END OF THE LINE
                CheckStringTemp4 := SUBSTR(CheckStringTemp2, CheckStringCount2 + 1, LENGTH(CheckStringTemp2) - CheckStringCount2);
                --THE SECOND SUBSTRING IS THE COMBINATION OF SUBSTRINGS 3 AND 4
                CheckStringTemp2 := CheckStringTemp3 || CheckStringTemp4;
                --ALL DONE, EXIT THE LOOP
                EXIT;
               END IF;
               --INCREMENT THE STRING PLACE COUNTER BY 1
               CheckStringCount2 := CheckStringCount2 + 1;
              END LOOP;
            --END THE CheckStringTemp2 IF/THEN LENGTH CHECKER
            END IF;
              --THE COMPLETE DEFENDANT NAME IS THESE TWO SUBSTRINGS COMBINED
              Temp1000Char := CheckStringTemp1 || CheckStringTemp2;
              --ALL DONE, EXIT THE LOOP
              EXIT;
             END IF;
            --INCREMENT THE STRING PLACE COUNTER BY 1
            CheckStringCount := CheckStringCount + 1;

           --END THE DEFENDANT STRING FORMAT LOOP
           END LOOP;

          --END THE DEFENDANT STRING FORMAT IF/THEN
          END IF;

          --IF THIS IS THE FIRST DEFENDANT
          IF DefendantCount = 0 THEN
           --SET THE CIVIL DEFENDANT AGGREGATE STRING EQUAL TO Temp1000Char
           CivilDefendants := Temp1000Char;
          ELSIF DefendantCount > 0 THEN --THIS IS THE SECOND OR GREATER DEFENDANT
           --ADD AN UNDERLINED "and" PLUS AN HTML LINE BREAK PLUS THE DEFENDANT NAME TO THE AGGREGATE STRING
           CivilDefendants := CivilDefendants || ' </B><I>and</I><B><BR>' || Temp1000Char;
          END IF;

          --INCREMENT THE DEFENDANT COUNTER BY 1
          DefendantCount := DefendantCount + 1;
         --END LOOP TO FORMAT DEFENDANT STRING
       END LOOP;

         --THE DEFENDANT INFO IS THE CIVIL PLAINTIFF STRING BOLDED PLUS AN HTML LINE BREAK PLUS AN UNBOLDED,
         --ITALICIZED "vs." PLUS AN HTML LINE BREAK, PLUS THE CIVIL DEFENDANT STRING BOLDED
         DefendantInfo :=  '<B>' || CivilPlaintiff || '</B><BR><I>vs.</I><BR><B>' || CivilDefendants || '</B>';
         --REPLACE ANY DOUBLE "ET AL"'S IN THE LINE
         DefendantInfo := REPLACE(DefendantInfo, 'ET AL ET AL', 'ET AL');

         --END THE CIVIL CASE IF/THEN
         END IF;

         --IF THIS IS A TRAFFIC/CRIMINAL CASE AND THE ATTORNEY ID IS NOT NULL
         --(THERE IS ONLY ONE ATTORNEY)
         IF  Type_ = 'TRCR' AND AttorneyId IS NOT NULL THEN
          --PUT THE ATTORNEY'S LAST NAME INTO AttorneyAll
          SELECT INITCAP(LAST_NAME)
          INTO AttorneyAll
          FROM ATTORNEYS
          WHERE ATTORNEY_ID = AttorneyId;
         ELSE--THERE MAY BE MULTIPLE ATTORNEYS
          --SELECT ATTORNEYS' INFO INTO CUR_TEMP
          OPEN CUR_TEMP FOR
            SELECT INITCAP(ATTORNEYS.LAST_NAME),
                     INITCAP(ATTORNEYS.FIRST_NAME),
                     SCHEDULED_ATTORNEYS.NOTE
          FROM CASE_SCHEDULES
            INNER JOIN SCHEDULED_ATTORNEYS ON CASE_SCHEDULES.CASE_SCHEDULE_ID = SCHEDULED_ATTORNEYS.CASE_SCHEDULE_ID
            INNER JOIN ATTORNEYS ON SCHEDULED_ATTORNEYS.ATTORNEY_ID = ATTORNEYS.ATTORNEY_ID
            WHERE (CASE_SCHEDULES.CASE_SCHEDULE_ID = CaseScheduleId);

            --INITIALIZE VARIABLES
            AttorneyCount := 0;
          AttorneyAll := '';
          Attorney := '';

            --LOOP TO GET ATTORNEYS' NAMES
            LOOP
            --STOP LOOPING WHEN THERE ARE NO MORE ROWS IN THE CURSOR
            EXIT WHEN CUR_TEMP%NOTFOUND;
             --FETCH ATTORNEY INFO INTO VARIABLES
             FETCH CUR_TEMP
              INTO AttorneyLastName,
                   AttorneyFirstName,
                   Notes;

          --IF THERE IS AN ATTORNEY LAST NAME
             IF AttorneyLastName IS NOT NULL THEN
              --IF THERE IS AN ATTORNEY FIRST NAME AND THERE'S ONLY 1 ATTORNEY
              IF AttorneyFirstName IS NOT NULL AND AttorneyCount < 2 THEN
               --ATTORNEY STRING IS FIRST NAME PLUS SPACE PLUS LAST NAME
               Attorney := AttorneyFirstName || ' ' || AttorneyLastName;
              --ELSE IF THERE IS NO ATTORNEY FIRST NAME AND THERE'S ONLY 1 ATTORNEY
              ELSIF AttorneyCount < 2 THEN
               --ATTORNEY STRING IS LAST NAME
               Attorney := AttorneyLastName;
              END IF;
              --IF THERE IS A NOTE AND THERE'S ONLY 1 ATTORNEY
              IF Notes IS NOT NULL AND AttorneyCount < 2 THEN
               --ATTORNEY AGGREGATE STRING IS ATTORNEY STRING PLUS " for " PLUS NOTES PLUS AN HTML LINE BREAK
            --PLUS ATTORNEY AGGREGATE STRING
            AttorneyAll := Attorney || ' for ' || Notes || '<br>' || AttorneyAll;
              --ELSE THERE'S NO NOTE AND THERE'S ONLY 1 ATTORNEY
              ELSIF AttorneyCount < 2 THEN
               --ATTORNEY AGGREGATE STRING IS ATTORNEY STRING PLUS AN HTML LINE BREAK PLUS ATTORNEY AGGREGATE STRING
               AttorneyAll := Attorney || '<br>' || AttorneyAll;
              END IF;

              --INCREMENT ATTORNEY COUNTER BY 1
              AttorneyCount := AttorneyCount + 1;

              --RESET VARIABLES TO NULL
              Attorney := '';
           AttorneyFirstName := '';
           AttorneyLastName := '';
           Notes := '';
          --END OF ATTORNEY LAST NAME IF/THEN
            END IF;

         --END OF LOOP TO GET ATTORNEYS' NAMES
           END LOOP;

          --END OF TRAFFIC/CRIMINAL CASE AND THE ATTORNEY ID IS NOT NULL IF/THEN
        END IF;

       --CHANGE CaseNumber from CASE VIOLATION ID TO CASE NUMBER
         CaseNumber := SUBSTR(CaseNumber, LENGTH(CaseNumber) - 11, 12);

         --REFORMAT NAME SUFFIXES
         AttorneyAll := REPLACE(AttorneyAll, 'Iii', 'III');
       AttorneyAll := REPLACE(AttorneyAll, 'Ii', 'II');

       --CONVERT APOSTROPHES TO SPECIAL HTML CHARACTER, OR ASP.NET WILL ASSERT ON APOSTROPHE IN STRING
         DefendantInfo := REPLACE(DefendantInfo, '''', '&#39');

--INSERT ALL THE INFORMATION INTO THE GLOBAL TEMPORARY TABLE
INSERT INTO GTT_RETURN VALUES
 (
   FromDate,
   FromTime,
   CaseNumber,
   DefendantInfo,
   Caption,
   ViolationNumberAll,
   ViolationDescriptionAll,
   Hearing,
   JudgesLastName,
   Room,
   ProsecutorLastName,
   AttorneyAll,
   OfficerLastName
 );
                       
--END THE MAIN LOOP
END LOOP;

--SELECT ALL FROM THE GLOBAL TEMPORARY TABLE INTO THE CURSOR TO RETURN TO ASP.NET
OPEN CUR_RETURNDATA
 FOR 'SELECT *
        FROM GTT_RETURN
        ORDER BY FROMDATE,
        FROMTIME,
        DEFENDANTNAME,
        CASENUMBER DESC,
        CAPTION';

--THE FOLLOWING IS THE SCRIPT USED TO CREATE THE GLOBAL TEMPORARY TABLE GTT_RETURN
/*
EXECUTE IMMEDIATE 'CREATE GLOBAL TEMPORARY TABLE GTT_RETURN
 (
   FromDate DATE,
   FromTime DATE,
   CaseNumber VARCHAR2(1000),
   DefendantName VARCHAR2(350),
   Caption  VARCHAR2(255),
   ViolationNumber VARCHAR2(1000),
   ViolationDescription VARCHAR2(1000),
   Hearing  VARCHAR2(30),
   JudgesName VARCHAR2(20),
   Room  VARCHAR2(30),
   ProsecutorLastName VARCHAR2(20),
   Attorney VARCHAR2(255),
   OfficerLastName VARCHAR2(20)
 )
ON COMMIT PRESERVE ROWS';
*/
END;