Milestone Maker System

# Overview -- Phase 1

-   Replicate COATS as another system.

```{=html}
<!-- -->
```
-   (DONE, FML) Create ERD of existing COATS, reverse engineer and
    modify for Milestone Maker.

-   Prefix packages with MILES\_ (from SAFE\_ -\> MILES)

-   Prefix tables, seq tables, views with MILES\_

-   CA_COAT\_ -\> MILES_TX

-   CA\_ -\> MILES\_

```{=html}
<!-- -->
```
-   (DONE, FML) Create the following scripts

-   Create_table.sql -- create table script

-   Create_sequence.sql -- create sequence table script

-   Grants.sql -- grants of MILES\_ objects to webdev, webprod

-   Public_synonym.sql -- creation of public synonyms for MILES\_
    objects

-   Triggers.sql -- contains triggers for the MILES\_ tables.

```{=html}
<!-- -->
```
-   (DONE, FML) Run the scripts

-   All the above DDL scripts (except public_synonym.sql) under SAFETY
    schema.

-   The public_synonym.sql script under SHERIDAN schema.

```{=html}
<!-- -->
```
-   ( DONE, MMJ) Copy the following packages using the WEBPROD copy:

-   SAFE0001 MILES0001

-   SAFE0001A MILES0001A

-   SAFE0003 MILES0003

```{=html}
<!-- -->
```
-   (DONE, MMJ) Search and replace the above packages using the
    following order:

-   CA_COAT MILES_TX

-   CA\_ MILES\_

-   SAFE0001 MILES0001

-   SAFE0001A MILES0001A

-   SAFE0003 MILES0003

-   (DONE, MMJ) Copy lookup table data from COATS\_\* lookup tables to
    MILES\_\* lookup tables.

-   (DONE, MMJ) Adjust package calls through URL to not specify the
    PL/SQL agent (e.g. dcc0001/owa).

```{=html}
<!-- -->
```
-   (DONE, FML) Create WAM (web application menu) entries for the web
    packages.

-   (DONE, FML) Make sure all pages have a link back to the Main Menu.

-   (DONE, FML) Create OWS (web security) entries for the above packages
    and menu items.

**NOTE**: Users with COATS_SAFETY_USER and COATS_PRIV_USER are also
privileged users so no need to create and maintain a separate
MILES_SAFETY_USER and MILES_PRIV_USER roles.

-   (DONE, MMJ) Test the application AS-IS in WEBDEV. When ok, move copy
    to WEBPROD and test in WEBPROD (port 7001).

> **NOTE:** This is the 1^st^ milestone -- recreating the application
> AS-IS

# Overview -- Phase 2

-   Modify the MILES system

## Entry Page

-   (DONE, MMJ) Modify the Entry Page (and anything that resembles the
    Entry Page fields, i.e. query criteria and detail pages, etc.):

-   (DONE, MMJ) Main Title -- Change from "ANL/APS/ESH CORRECTIVE ACTION
    TRACKING" to "MILESTONE MAKER", change any other title with "COATS"
    to "Milestone/Inspection"

-   Tracking Number -- no change

-   Action Date Open -- no change

-   (DONE, MMJ) Action Type -- change the DATA to the following (default
    unknown as of this writing):

-   Documentation

-   Milestone

-   Inspection

-   Audit

-   Test

-   Other

-   Self (see Ref 1)

-   (DONE, MMJ) Responsible Division/Group -- remove "See UNK for
    Responsible Group if unknown, there is no UNKNOWN in this field for
    this application.

-   Facility -- no change

-   (DONE, MMJ) Classification -- hide field

-   Location -- no change

-   (DONE, MMJ) Description -- Rename label to "Description of Action
    Required"

-   (DONE, MMJ) Past Due After -- Rename label to "Due Date" (see Ref 2)

-   Status Log -- no change

-   (DONE, MMJ) Final Corrective Action -- change label to "Final
    Action"

-   Date Closed -- no change

-   (DONE, MMJ) Status -- change the DATA to the following (see Ref 3):

-   New (Default) (NOTE: the status field is NOT NULL so there needs to
    be something in the beginning)

-   Compliant

-   Pending

-   Overdue

-   (DONE, MMJ) Reference -- NEW FIELD (see Ref 4)

-   (DONE, MMJ) Title -- NEW FIELD (see Ref 5)

-   (DONE, MMJ) Frequency -- NEW FIELD (see Ref 6)

-   (DONE, MMJ) Float -- NEW FIELD (see Ref 7)

-   (DONE, FML) Include due date, due date change date and close date in
    the miles_status_code_detail history table. Modify the
    milex_tx_header, status_code column update trigger.

```{=html}
<!-- -->
```
-   Ref 1 -- User selects 'Self' then all email notifications should go
    to originator or self.

-   Ref 2 -- This requires something similar to the recurring system in
    place in the Calendar system.

-   Ref 3 -- The following workflow should be followed:

```{=html}
<!-- -->
```
-   If Date Closed field is filled in, the Status automatically becomes
    COMPLIANT (interactive).

-   If Due Date passes by the current system date, Status automatically
    becomes OVERDUE (daemon process).

```{=html}
<!-- -->
```
-   If during initial entry and non of the above applies and the initial
    email has been sent, the Status becomes 'PENDING' (driven by the
    email event being sent).

-   Fallback status is the default status.

-   Ref 4 -- This field will allow a user to enter a number of reference
    line items composed of a label and a URL for this tracking number.

```{=html}
<!-- -->
```
-   (DONE, FML) Create the following object:

-   MILES_REFERENCE_LIST

-   Table

-   Unique index on tracking_no and label

-   Audit fields (required)

-   Public synonym

-   Grants

```{=html}
<!-- -->
```
-   Entry and Edit security is same as the facility field.

```{=html}
<!-- -->
```
-   Ref 5 -- This field will allow a user to enter a milestone
    inspection title.

-   (DONE, FML) Add the following column to the MILES_TX_HEADER table.

-   TITLE -- varchar2(255)

```{=html}
<!-- -->
```
-   Ref 6 -- This field will indicate how often the Inspection will be
    carried out. It will also be the basis for the notification
    mechanism.

-   (DONE, FML) Create a new table that includes frequency lookup values
    -- MILES_FREQUENCY_TABLE. The following are the fields:

-   Frequency_ID number not null,

-   Label varchar2(30) not null

-   Description varchar2(255) null

-   (DONE, FML) Add the following column to the MILES_TX_HEADER table.

-   Frequency_id (foreign key to MILES_FREQUENCY_TABLE.Frequency_ID).

```{=html}
<!-- -->
```
-   Ref 7 -- This field will allow a user to specify if the next due
    date gets reset to the closed date.

-   (DONE, FML) Add the following column to the MILES_TX_HEADER table.

-   FLOAT_YN -- varchar2(1), valid values (Y.N), default N

-   (DONE, MMJ) The web application will need to be adjusted to include
    this new column

-   The nightly batch job will have to be modified to include the
    processing of this column. This will be based on the flow described
    in the flowchart.

**Query Listing**

-   (DONE, MMJ) Modify the query result page:

-   Change title from "COATS Items Found" to "Milestones/Inspections"

-   Make the following changes:

FROM

+--------+------+------+-----+------+----+-------+--------+-------+
| Tr     | St   | Date | T   | D    | Fa | Clas  | Lo     | D     |
| acking | atus | Open | ype | iv/G | ci | sific | cation | escri |
|        |      |      |     | roup | li | ation |        | ption |
| \#     |      |      |     |      | ty |       |        |       |
+--------+------+------+-----+------+----+-------+--------+-------+
| Track  | Sta  | Acti | A   | R    | F  | Clas  | Lo     | defic |
| ing_no | tus_ | on_d | cti | espo | ac | sific | cation | iency |
|        | code | ate_ | on_ | nsib | il | ation |        |       |
|        |      | open | typ | le_d | it | _code |        |       |
|        |      |      | e_c | ivis | y( |       |        |       |
|        |      |      | ode | ion/ | Fa |       |        |       |
|        |      |      |     |      | ci |       |        |       |
|        |      |      |     | R    | li |       |        |       |
|        |      |      |     | espo | ty |       |        |       |
|        |      |      |     | nsib | _c |       |        |       |
|        |      |      |     | le_g | od |       |        |       |
|        |      |      |     | roup | e) |       |        |       |
+--------+------+------+-----+------+----+-------+--------+-------+

TO

<table>
<colgroup>
<col style="width: 8%" />
<col style="width: 6%" />
<col style="width: 8%" />
<col style="width: 8%" />
<col style="width: 10%" />
<col style="width: 8%" />
<col style="width: 12%" />
<col style="width: 12%" />
<col style="width: 9%" />
<col style="width: 15%" />
</colgroup>
<tbody>
<tr class="odd">
<td><p>Tracking</p>
<p>#</p></td>
<td>Title</td>
<td><p>Frequ-</p>
<p>Ency</p></td>
<td>Due By</td>
<td><p>Status/</p>
<p>Date Completed</p></td>
<td>Type</td>
<td><p>Responsible</p>
<p>Organization</p></td>
<td><p>Facility/</p>
<p>Location</p></td>
<td>Comments</td>
<td><p>Source</p>
<p>Reference</p></td>
</tr>
<tr class="even">
<td>Tracking_no</td>
<td>Title</td>
<td>Label(Frequency_id)</td>
<td>Estimated_closure_date</td>
<td><p>Status_code/</p>
<p>Date_closed</p></td>
<td>Action_type_code</td>
<td>Responsible_division/responsible_group</td>
<td><p>Facility(Facility_code)/</p>
<p>Location</p></td>
<td>Deficiency</td>
<td>Label(tracking_no,seq_no), URL(tracking_no,seq_no)</td>
</tr>
</tbody>
</table>

## Email Notification Systems

-   Pending Notification -- emails are sent when the following occurs on
    the front-end:

-   (DONE, MMJ) When a user first enters a milestone inspection record
    the status is set to NEW.

-   The user then proceeds to a confirmation page and hits the submit
    button. The status gets updated to PENDING. A notification email
    gets sent to recipients based on the action type and responsible
    group.

-   Every night a scheduled job does the following:

```{=html}
<!-- -->
```
-   Rescheduling -- Looks at the miles_tx_header record and updates the
    status back to PENDING. The float_YN determines if and when the due
    date needs to be updated.

-   (DONE, FML) Create flow chart indicating this process.

-   Create code that performs this process

-   Overdue checking -- Looks at the miles_tx_header and checks if items
    are overdue and marks them as such.

-   (DONE, FML) Create flow chart indicating this process.

-   Create code that performs this process.

```{=html}
<!-- -->
```
-   Warning notification - Looks at the miles_tx_header record and
    create warning notification email files and script based on current
    and due dates.

-   (DONE, FML) Create flow chart indicating this process.

-   Create code that performs this process.

-   Overdue notification - Looks at the miles_tx_header record and
    create notification email files and script for overdue items.

-   (DONE, FML) Create flow chart indicating this process.

-   Create code that performs this process.

> NOTE: Notification hierarchy is similar to COATS. If Action_Type is
> SELF then all email notifications goes to the STATUS_CODE_BY_BADGE_NO.

## Events and Updates

The following updates should occur based on an event:

-   Closing Event -- This is when the date_closed field is entered or
    updated. When this occurs, the following is done in the program:

-   Status_code is set to COMPLIANT.

> **NOTE:** The Estimated_closure_date is NOT moved forward. The
> Recycling process does this.

**Error Messages**

-   (TO DO, TBA) Error messages will need to be adjusted that is
    specific to the Milestone Maker system.

## Administration Form

-   (DONE FML) Create a Forms 4.5 application that will administer the
    lookup tables. Form is \$Mis_forms/miles_admin.fmx.