## **Auxdata Release Notes**

## [[For your convenience, Auxdata's most recent enhancements now appear first, followed by progressively older changes back to and including the last major release.]{.smallcaps}]{.mark}

## **[Post-release QCRs:]{.underline}**

**\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\--**

**Release 2.3.0 - Includes the following SCRs:**

> **AUXDT00000461 (version 2.3.0; rolled 02/24/09)-** AUXDATA shall
> modify the Annual Member Letter report to fix the following:
>
> 1\. Correct the Annual Member Letter report so it will show all ICS
> courses a member has taken, not just 100, 200, 700 and 800.
>
> 2\. Correct the counting of ATON DISCREP and ATON VERIFIED. Also, ATON
> Missions are not calculating correctly. When multiple aids verified or
> discrepant are indicated within one activity log, the ATON Missions
> field doubles the missions.
>
> 3\. The Annual Member Letter report is incorrectly adding MDVs and
> VSCs performed as a Trainee, into the count of MDVs and VSCs shown on
> the report. Correct calculation of Program Visits and VSC Given and
> exclude trainee time.
>
> 4\. Check on why a \"99 Hours\" mission entered with a date of 31 Dec
> and for more than 24 hours, does not show up on the Letter.
>
> **AUXDT00000462 (version 2.3.0; rolled 02/24/09)-** AUXDATA shall no
> longer require aircraft inspectors to be validated. Users may enter a
> free-text name of whomever the aircraft inspector happens to be.
> He/she is not required to be an Auxiliary member. No LOV is available
> now. Any name can be stored in this field. A hint is given to the user
> for the format of the field - \"Format is LAST NAME, FIRST NAME,
> MIDDLE\".
>
> **AUXDT00000463 (version 2.3.0; rolled 02/24/09)-** AUXDATA shall
> enforce completion of all required tasks prior to competency
> certification. If member has not completed all required tasks, a
> message will display on the screen.

**\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\--**

**AUXDT00000465 (version 2.2.2; rolled 01/08/09) --** Auxdata shall
change the number of hours needed for the 12 SSA award from 22,500 to
25,000.

**AUXDT00000458 (version 2.2.1; rolled 12/31/08) --** Auxdata shall
modify LOVs to show all DIST members for Owners and Authorized Users
drop-downs.

**\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\--**

**Release 2.2.0 - Includes the following SCRs:**

> **AUXDT00000278 (version 2.2.0; rolled 12/22/08)-** AUXDATA shall add
> a new resource type \-- vehicle. Modify Resource Status to capture
> vehicle Offer for Use data.
>
> **AUXDT00000417 (version 2.2.0; rolled 12/22/08)-** Setup the report
> script to count all U/W hours for BOAT missions, all ABN hours for AIR
> missions, all UCGOPS minus MS missions for SUP and all UCGOPS MS
> missions for MS. No TRN or TRAINING hours will be counted.
> Additionally, 1/2 credit will be given for ADMINMS missions and 7
> points for each CFV exam as lead, 4 points for each TOW exam, and 4
> points for each UPV.
>
> **AUXDT00000418 (version 2.2.0; rolled 12/22/08) -** OPS Policy Manual
> requirement that the AUX-18 Task has to be completed 1 year after the
> initial date of certification. This new requirement should be added to
> the nightly CRON job that is run to check the 7015 task for members
> with Air competences. This one would check all three pilots and Air
> Crew to see if they have an AUX-18 completed one year plus 45 days
> since their initial certification. If not set them to REYR.
>
> **AUXDT00000419 (version 2.2.0; rolled 12/22/08) -** Add code to the
> application that will auto-generate and fill in the registration
> numbers for all new flotillas.
>
> **AUXDT00000421 (version 2.2.0; rolled 12/22/08)-** AUXDATA shall
> rename organizational positions due to Auxiliary realignment: VCO -
> District Vice Commodores are to be replaced by DCOS - District Chief
> of Staff. RCO - District Rear Commodore are to be replaced by DCAPT -
> District Captain. DVC - Division Captain are to be replaced by DCDR -
> Division Commander. VCP - Division Vice Captain are to be replaced by
> DVCDR - Division Vice Commander. In making these changes, the current
> holders of the offices being replaced will continue to be listed in
> the replacing offices. The above changes are to be made by 1 January
> 2009.
>
> Also, add new offices ASC - Auxiliary Sector Coordinator and SLO -
> State Liaison Officer to all Districts. ASC is an appointed office so
> it will not affect the Election reports.
>
> **AUXDT00000422 (version 2.2.0; rolled 12/22/08) -** All members who
> hold the Coxswain, Crew, the three Pilots, Air Crew or Trident
> competencies, must have completed the ICS 210 - OR - ICS 300 prior to
> 31 March 2009 in order to maintain currency in those competencies. Add
> ICS 210 and 300 for surface, aviation and MSO Qualifications with a
> Due Date of 31 March 2009. Will need to find out if this will be a
> static date that will then have to be updated once this time is
> reached or if it\'s something we can set to repeat every so often.
> Based on the attached emails, should also fix the problem with Annual
> 30 June tasks displaying 2009 as the due date.
>
> Further clarifying the requirements, a new task will need to be
> created and assigned to those competencies with a due date of
> 31-March-2009 and then a case statement will need to be used to see if
> the ICS 210 or 300 was done.
>
> All members who have the Coxswain, Aircraft Commander, First Pilot, Co
> Pilot or Trident competencies must have competed either ICS 210 or ICS
> 300 by 31 March 2009.
>
> **AUXDT00000423 (version 2.2.0; rolled 12/22/08)-** Currently the
> system identifies those who are authorized to perform a Radio facility
> inspection as members who hold the FSO-CM, SO-CM, ADSO-CM, DSO-CM or
> DVC-OT office - AND - have completed the AUXCOM course. That has been
> changed to the following: Those who are authorized to perform a Radio
> facility inspection are members who hold either FSO-CM, SO-CM,
> ADSO-CM, DSO-CM or DVC-OT office - AND - have completed the AUXCOM
> course (if prior to 1 August 2008) - OR - have been assigned the TCO
> competency.
>
> **AUXDT00000424 (version 2.2.0; rolled 12/22/08) -** For members with
> a QE competency, when they are credited with taking a Team
> Coordination Training Workshop (WK-15), have the system also credit
> them with the Biannual TCT Workshop.
>
> **AUXDT00000451(version 2.2.0; rolled 12/22/08) --** AUXDATA shall add
> delete functionality within the Past Officer tab page in Member
> Status. If a past officer was entered into the system in error, there
> was no way to remove it. It was falsifying the Officer Eligibility
> report for the next highest office.

End Release 2.2.0 Notes.

**\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\--**

**AUXDT00000447 (version 2.1.12; rolled 12/09/08) --** Auxdata will fix
the UPDATE statement for the AUXSCE instructor competency by adding the
person_id to the where clause.

**AUXDT00000442 (version 2.1.11; rolled 11/17/08) --** Auxdata changed
the usradmin package so that the drop_user function deletes accounts
based only off of the oracle_user_id instead of requiring the person_id.
Also, simplified account creation in the create_user function so that it
no longer checks the v\$\_tablespace for quota before designating 0 or
assigns a tablespace. It should just use the defaults. Updated the form
to call the drop_user function.

**AUXDT00000431 (version 2.1.10; rolled 11/3/08) --** AUXDATA changed
the COMO previously listed at the bottom of the annual member letter
report to COMO Nicholas Kerigan.

**AUXDT00000408 (version 2.1.09; rolled 9/8/08) --** AUXDATA modified
the Reports screen so that the Member ID can be entered instead of
selecting a unit. AUXDATA modified both reports to accept the Member ID.
We excluded AP status members from the reports. Made several to changes
to the function that selects which members are eligible. These changes
include adding the AUXMIN substitution for the APC competency for when
the member has served in a previous office; Correcting the term
calculations for the DCP office and the calculation for whether someone
has served as a DCO or not.

**AUXDT00000430 (version 2.1.08; rolled 9/3/08) --** AUXDATA changed the
CHDIRAUX name listed at the bottom of the Annual Member Letter Report to
CAPT Mark D. Rizzo.

**AUXDT00000405 (version 2.1.07; rolled 7/14/08) --** AUXDATA added the
Boat Crew Coxswain competency to all the checks that were previously
only Boat Crew Coxswain for everything regarding the new AUXSCE
Instructor competency.

**AUXDT00000401 (version 2.1.06; rolled 7/14/08) --** AUXDATA created a
new cron job that checks the TRAINEES table for members that are due to
retake the NavRule 95 exam within 45 days. AUXDATA then sends an email
notification that they must take the exam by their due date or they are
at risk of being decertified from either their BOAT CREW COXSWAIN or
PERSONAL WATERCRAFT OPERATOR competency.

**AUXDT00000386 (version 2.1.05; rolled 7/02/08) --** AUXDATA created a
new UMS/A/UADMS/MT/AUXSCE mission, AUXDATA created new AUXSCE
competency, assigned the SCETT task to it, and assigned it to all units.

We setup assignments so that the AUXSCE can\'t be assigned to a member
unless they complete the SCETT task and are Certified as Instructor plus
either Boat Crew Coxswain or one the of AIR competencies. Setup
Certifications to perform similar checks as the assignments screen.

Also added a check for decertify AUXSCE if the member is decertified in
any of the prerequisite comps.

Added a check in the Activity Logs Crew Assignments tab to only allow
members certified as an AUXSCE Instructor to be eligible as a Lead for
the UMS/A/UADMS/MT/AUXSCE mission.

**AUXDT00000391 (version 2.1.04; rolled 5/27/08) --** A notification
from CG-CIRT indicated a HIGH Threat Assessment against any version of
Adobe Acrobat Reader prior to version 8.1.2. Therefore, Auxdata Citrix
Servers must have Adobe Reader upgraded to a minimum version of 8.1.2.

**AUXDT00000378 (version 2.1.03; rolled 5/12/08) --** The reports piece
of the user_admin module was producing corrupted .pdf files not
recognized by adobe reader. Removed all report related code then copied
over the report packages from auxdata_reports and tailored them to work
inside the user_admin module. Changed the name of \"auxmis_user\" report
to \"auxmis_users\" so the \"r\" doesn\'t get trimmed off during the
call to the .rdf file.

**AUXDT00000385 (version 2.1.02; rolled 5/06/08) --** Hiding the
Election Eligibility report button until the report can be updated with
this year\'s requirements.

**AUXDT00000376 (version 2.1.01; rolled 4/28/08) --** Removed
requirement for AP status members to have BSC completed for Boat
missions. Added ability for AP status members to be assigned to Air
missions.

**Release 2.1.00 (Rolled 4/15/08 AUXDT00000358, AUXDT00000359) --**
AUXDATA shall upgrade the Oracle Forms and Reports running on the web
servers to Version 10.1.2.0.2. AUXDATA shall make necessary
modifications to Forms and/or Reports required for the application
server upgrade to Version 10.1.2.0.2.

**AUXDT00000357, AUXDT00000369 (version 2.0.87; rolled 2/12/08) --**
When the TCT Fresher task is \"W\" or \"Waived\" the previous year, the
Training Status Reports now displays it as a \"P\" status instead of an
\"N\" for the current year. Also When the TCT Fresher task is \"W\", the
Training status reports will show \"Waived\" instead of a due date.

The REYR script will set all competencies to REYR status if the member
did

not complete the necessary tasks in 2007.

**AUXDT00000352 (version 2.0.86; rolled 1/8/08) --** Changed the trigger
code to recognize the old UCG/A/UCGOPS/CVS/CFV missions as
UCG/A/UCGOPS/MS/CFV missions. This unblocks both the Exams Given and
Exams Passed boxes on the Activity Logs Details tab.

**AUXDT00000339 (version 2.0.85; rolled 1/2/08) --** Created scripts
that will do the following:

1\. Change the existing NAVRULE exam to NAVRULE(95) and make it 5 years
sliding

2\. Create a new NAVRULE(70) exam task that is permanent.

3\. Assign the new NAVRULE(70) to the coxswain compentency.

4\. Create a task_capture record for the new NAVRULE(70) task.

5\. Credit all existing coxswains with the new task.

Created SQL to run in a cron job starting Jan 14th 2008 that will set

coxswains to REYR if they haven\'t completed NAVRULE(95) with in the 5
years +

2 weeks.

**AUXDT00000226 (version 2.0.84; rolled 1/2/08) -** Created a script
that will modify the EMPLOYMENT_CATEGORIES and MISSIONS tables.

Several EMPLOYMENT_CATEGORIES are being set to inactive, some
descriptions are being changed. Auxdata_activity_logs was modified to
include more auto-fills for OPS/SAR and STBY/SAR missions.

**AUXDT00000335 (version 2.0.83; rolled 11/15/07) -** Added AP status
members with BSC checked to be available for certain tasks in task
capture.

**AUXDT00000338 (version 2.0.82; rolled 11/15/07) -** Changed visibility
of Past_offices tab and update_allowed for BSC check box. Instead of the
DIR_AUX security role, they are now based off of the admin role.

**AUXDT00000331 (version 2.0.81; rolled 11/08/07) -** Resource Status
form- Reworked the save button to only leave the screen and return to
the choose facility screen if facility is being set to archive or
uninspected.

**AUXDT00000319 and AUXDT00000327 (version 2.0.80; rolled 10/26/07)**
Errors when running Annual Member Letter for one member and the unit
grouping field were corrected. A new report, \"Election_Reason\" was
created to show why members do not qualify for certain offices.

**AUXDT00000328 and AUXDT00000306 (version 2.0.79; rolled 10/18/07)**
For SCR 328:

We created a new billing report that displays members that were AP
status during the quarter that the report is being run. We added the new
quarterly billing button to the reports choices screen. We need to keep
the old report for this quarter to allow a smooth transition.

For STR 306:

I added a check for the year when an RBS device report is run. If the
year is less than 2007, the application will use the year 2007.

**AUXDT00000297 and AUXDT00000326 (version 2.0.78; rolled 10/16/07) -**
For SCR 297:

We added a new tab that only users with DIRAUX can access. The new tab
displays the member\'s name, current offices and past offices. Users can
click on INSERT PAST OFFICE to enter any missing offices. Users can also
click on CHANGE DATES to change the dates of the current record
selected. When entering a past office, the office code, begin date, end
date and unit are required.

For SCR 326:

Changed the awards tab to show AP status members that have completed
BSC. The phones section has been rearranged to only show 1 phone of each
phone type so that it now matches the member roster report.

**AUXDT00000317 (version 2.0.77; rolled 9/27/07) -** The dates for the
report query have been changed from hard-coded dates to variables. The
variables will change on July 1 of each year.

**AUXDT00000324 (version 2.0.76; rolled 9/13/07) -** Fix the drop-down
list on the \'Eligibility\' Report so the correct \'Officer\' parameter
gets Passed to the report insuring the correct data returns.

**AUXDT00000321 (version 2.0.75; rolled 9/11/07) -** The save button is
setup to compare the old inspection date to the new one. The new date
must be greater than the old. If it isn\'t then an error message is
received.

**AUXDT00000305 (version 2.0.74; rolled 9/11/07) -** If the security
module is used to update a member\'s status, it\'s possible to enter a
status date that already exisits, which creates a duplicate status date.
AUXDATA shall correct the security module so that it perfoms a date
check similar to the member status module.

**AUXDT00000268 (version 2.0.73; rolled 9/04/07) --** A new report,
Election, was created. Access and the ability to run the report were
added to the Auxdata_reports.fmb. A Function, \'isEligible\' was created
to run the data for the report.

**AUXDT00000312 (version 2.0.72; rolled 8/27/07) --** Added BSC_COMPLETE
column to the PERSONS table and V_CHOOSE_MEMBER view. Added Check box to
the Member Status \> Member Information screen to give credit for the
Boating Safety Course being complete. Allowed AP status members with BSC
checked to be assigned competencies, but blocked Coxswain, Aircraft
Commander, First Pilot, Co Pilot, Air Crew, Air Observer and Personal
Watercraft Operator. Allowed AP status members with BSC checked to be
certified in assigned competencies. Allows Allowed AP status members
with BSC checked to be able to be assigned as lead or non lead depending
on certifications held. Added code to the Filter and other buttons in
Activity Logs so the AP members w/ BSC checked stay available.

**AUXDT00000300 (version 2.0.71; rolled 8/27/07) --** Resources by
Flotilla report - request field change from "NAME" to "FACILITY NUMBER"
to mirror Boat Resources so the call sign will show on the report.
(Either change the field to reflect the same as boats or create a new
field to capture this information).

**AUXDT00000302 (version 2.0.70; rolled 8/22/07) --** Activity Log
Module - request a field on second page to include "PATROL ORDER NUMBER"
and make it mandatory. For that field, allow Alpha/Numeric. Some numbers
will all numeric and some will be numeric with one or more alpha
characters. Make the field 10 characters in length. Put it in the
Mission Detail table.

**AUXDT00000307 (version 2.0.69; rolled 8/22/07) --** Resource Update,
Inspector - show all qualified VEs in LOV regardless of district.
Aircraft & Boat.

**AUXDT00000295 (version 2.0.68; rolled 8/22/07)** **--** Remove the
auto-fill of 0000s on all zip code fields.

**AUXDT00000242 (version 2.0.67; rolled 8/15/07) --** Training \>
Assignments \> Assign Tasks to Competencies \> Assignments Tab
Assignments Tab is currently displaying inactive tasks for both
\"Available\" and \"Assigned Tasks.\" AUXDATA shall correct the data
blocks

**AUXDT00000197 (version 2.0.66; rolled 8/15/07) --** Training \>
Assignments \> Assign Competencies to Units If user selects 2 or more
Units from the Units tab and then clicks on the Assignments tab, only 1
Unit is available on the Assignments tab. AUXDATA shall correct the form
so that all selected units will appear on the Assignments tab.

**AUXDT00000244 (version 2.0.65; rolled 8/14/07) --** Disabled
navigation from the screen if the user starts to archive and enters an
end date. Disabled navigation from the screen if the user starts to
activate or restore a resource (needs inspection date and inspector to
save) After restoring a resource, set it up to require a user to enter a
new inspection date and inspector. Disabled \"Save\" button for
read-only users.

**AUXDT00000286 (version 2.0.64; rolled 7/11/07) --** Removed duplicate
assignment of TCT Refresher Task at the DIST level. Created a new
ind_training_status_csv.rdf to handle a bug causing duplicate lines to
appear in the report. Added U/W hours for Marine Dealer Visitor, Vessel
Examiner, Boat Crew Coxswain, Boat Crew Crewmember, Personal Watercraft
Operator, and Paddle Craft Operator competencies in the csv version of
the Unit Training Status report.

**AUXDT00000269 (version 2.0.63; rolled 7/10/07) --** Update the Member
Roster report. Remove the columns to the left. Provide the ability to
filter by competency and office. Keep the following fields: Name,
EMPLID, ADD1, ADD2, CITY, STATE, ZIP, EMAIL, EMAIL2, PHONE1, PHONE2,
PHONE3, BASE ENROLLMENT, STATUS. When printing Mailing Labels, the
format still does not match 5160/8160. This needs to be corrected. When
logged in at Division Level and selecting Division, the Officer
selection shows Division Officers - Good When selecting a Flotilla, the
Officer selection shows Flotilla Officers -- Good When selecting Unit
and All Subunits, clicking on Officers brings up nothing. It should
bring up all division and Flotilla Offices. When logged in at the
District Level and electing Unit and All Subunits, all District,
Division and Flotilla Offices should be available. When logged on at the
National Level and selecting Unit and All Subunits, all National,
Division and Flotilla Offices should be available. Also limit phone
numbers to 6.

**AUXDT00000274 (version 2.0.62; rolled 6/11/07) --** AUXDATA shall:

1\. For Radio inspectors, you must look for workshops completed (AUXCOM,
task_id 2522) in the TRAINEES table. Workshops are usually permanent
frequency, and are not stored in the PERSONS_COMPS table.

2\. Put the Warning back and prevent the user from entering a
non-qualified inspector at the time they enter the person in the field.
Don\'t allow it to be saved and then let them leave the screen, if the
inspector is not qualified. In the wording of the warning, modify so it
is clear a non-qualified inspector won\'t be allowed to be entered. It
has previously just been a warning, so re-word if necessary.

3\. 5SR is District 5 Southern Region - 054. The fix for that is to have
all qualified examiners, regardless of their district, appear in the
LOV.

**AUXDT00000235 (version 2.0.61; rolled 6/11/07) --** Crew Assignments -
Lead and NonLead Warning: For Lead Air and Non Lead Air crew
assignments, please update the hard-coded comp_id\'s. For example, 1055
no longer exists in the COMPETENCY table, but it is hard-coded in the
Crew Assignments code. Comp_id 2175 is co-pilot, and it does not exist
in the code but should.

The COMPETENCY table must have been updated since the Crew Assignments
code was written, and the competencies don\'t match up now.

**AUXDT00000270 (version 2.0.60; rolled 6/11/07) --** AUXDATA shall make
modifications so the detached flotillas will appear on the security
form.

**AUXDT00000284 (version 2.0.59; rolled 6/04/07) --** AUXDATA shall set
all competencies inactive that have been REYR for 5 or more years. And
AUXDATA shall add a date of \"05-FEB-2007\" where they were previously
NULL to all of the competencies that were set to REYR when the script
was run this year.

**AUXDT00000285 (version 2.0.58; rolled 6/04/07) --** This SCR makes
inactive statuses (RET, MREQ, FDUE, DADM) available choices even with no
security investigation complete.

**AUXDT00000239 (version 2.0.57; rolled 5/30/07) --** This SCR removes
the ability to set a status of DPSI from any dropdown list. Set the
status options to be based on security OS or DO status being completed,
FAVORABLE, and current, instead of checking for previously held
statuses. Added DPSI to Member_History_Data.sql

**AUXDT00000272 (version 2.0.56; rolled 5/3/07) --** AUXDATA shall
Modified the code in MR_COMPS function to prevent inactive comps from
displaying when running the weekly \"member roster\" script.

**AUXDT00000257 (version 2.0.55; rolled 5/2/07) --** AUXDATA shall Added
new RBS Device Award to picklists table. Added check from the Awards tab
in Member Status so user\'s can\'t give a member the RBS award more than
once. Added new button to Admin Reports to call rbs_awards report.
Created new rbs_awards.rdf file.

**AUXDT00000169 (version 2.0.54; rolled 4/30/07) --** AUXDATA shall
Added a Parameter screen will display ALL, UN-INSPECTED, and
OPERATIONAL; instead if ALL, INACTIVE, and ACTIVE

**AUXDT00000208 (version 2.0.53; rolled 4/30/07) --** AUXDATA shall add
a Requirement changed after previous SCR AUXDT00000083 rolled to
production. Adding Pilot/Flight Examiner competency. For Aircraft
facilities, the inspector must be a current Aircraft Commander or
Instructor Pilot/Flight Examiner (IP-FE). Please add any member who is
an ADSO-CM AND who has passed AUXCOM to the list of those who are
authorized to perform Radio facility inspections.

**AUXDT00000266 (version 2.0.52; rolled 4/26/07) --** AUXDATA shall
Added a function to make sure the members status were changed from a
\"bad status\" (ie. AP, FDUE) to a \"good status\" (ie. IQ, BQ) during
the quarter.

**AUXDT00000253 (version 2.0.51; rolled 4/17/07) --** AUXDATA shall add
a new Emergency Contact report.

**AUXDT00000254 (version 2.0.50; rolled 4/2/07) --** AUXDATA shall add
Charter Date to Auxiliary Unit Listing report. Remove Zip Code and put
Charter Date instead if not enough room to add new column.

**AUXDT00000261 (version 2.0.49; rolled 3/26/07) --** AUXDATA shall Set
the 12th SSA award to require 22,500 instead of 25,000.

**AUXDT00000251 & AUXDT00000252 (version 2.0.48; rolled 3/21/07) --**
Created the 1 HR TCT Refresher Task. The task is due annually, but not
when either of the 8 HR TCT workshops are due. Setup Boat Coxswain hours
to credit Lead, Non-Lead hours and CREWAUG hours. Set requirement to 12
hours for Boat Crew Coxswain, Boat Crew Crewmember, and Personal
Watercraft Operator.

**AUXDT00000256 (version 2.0.47; rolled 3/20/07) --** AUXDATA shall
remove all CFVE requirements from the VE/MDV Admin Report. There are
only three ways to earn the award.

1\. Perform at least 60 VSCs, or

2\. Perform at least 60 MDVs. or

3\. Perform at least 60 of a combination of VSCs + MDVs.

**AUXDT00000247 (version 2.0.46; rolled 1/31/07) --** The Annual Billing
report is currently displaying archived members. Annual Billing and
Quarterly billing both display duplicate names. AUXDATA shall remove
archived members from the annual report and the duplicates from both
reports.

**AUXDT00000234 (version 2.0.45; rolled 1/22/07) --** When running the
Patriot Readiness report, it only returns information if the unit number
entered is FLOT level. The report also needs to work for DIV and DIST
levels.

**AUXDT00000241 (version 2.0.44; rolled 1/18/07) --** Auxiliary is
requesting that when either the Individual Training Status or Unit
Training Status report is run with an end_date of 2006 that the reports
are to follow the rules that were in place for 2006. see SCR277.

**AUXDT00000243 (version 2.0.43; rolled 1/9/07) --** The owner\'s
information in CSV format is being divided between 2 columns. AUXDATA
shall correct and have the information appear in one column.

**AUXDT00000188 (version 2.0.42; rolled 1/8/07) --** The Annual Member
Letter is a new report to be run at the flotilla level only. The menu
button for the report will be visible to flotilla and division accounts
only. For Flotillas with many members, the report can only be run for 20
people at a time in order to handle the sheer volume of data. A 'drop
down' is provided that divides the members into sets of up to 20
depending on how many members are active in each flotilla. Also note,
when closing the report windows, they should be closed at a fairly slow
pace (1 to 2 seconds) in between so the member's computer can keep up
with the memory use.

**AUXDT00000228 (version 2.0.41; rolled 1/3/07) --** AUXDATA shall
disenroll all members who have not completed a PSI package for a
security background investigation on 30 Dec 2006 via a script and cron
job. Approximately 2600 members will be disenrolled, some of which will
be set to RET (retired) if they have 15 years of service. The Member
Status module will allow DPSI status members to be re-enrolled as needed
(based on same rules as MREQ and FDUE).

**AUXDT00000227 (version 2.0.40; rolled 12/29/06) --** AUXDATA shall
modify the Individual Training Status and Unit Training Status reports,
and change currency requirements for boat crew, coxswain, and PWO. The
U/W hours shall change from 8 to 12, and QE Approval shall change to 3
instead of 5 years. Also created new function F_TASK_MONTHS and modified
all affected reports to handle scaling due dates based on the completion
date of the QE approval tasks.

**AUXDT00000065 (version 2.0.39; rolled-back 09/15/06) --** AUXDATA
shall rollback the OP_AWARDS.rdf, PE_AWARDS.rdf, and VEMDV_AWARDS.rdf
from AUXPROD. Replace with the previous versions from PVCS. Due to a
requirement change or miscommunication, the changes made to these
reports are causing a different problem. Reports need to be modified to
accommodate both requirements.

**AUXDT00000065 (version 2.0.39; rolled 09/05/06) \--** For Admin
Reports: Operations Awards, VE/MDV Awards, and PE Performance Awards
reports, AUXDATA shall exclude members from the report whom have already
received the award for the year. Only members who are still due to
receive the award shall display on the reports.

**AUXDT00000207 (version 2.0.38; rolled 08/16/06) \--** Popup displaying
a new users password does not show when creating a user \"Not in
Auxdata\". Also, error message is received, \"FRM-40735:
WHEN-BUTTON-PRESSED trigger raised unhandled exception ORA-01403\"

**AUXDT0000083 (version 2.0.37; rolled 08/08/06) \--** AUXDATA shall
modify the facility inspector module in order to insure that only
authorized inspectors are entered into the associated text field.

**AUXDT00000186 (version 2.0.36; rolled 08/07/06) \--** AUXDATA shall
modify its security to generate (and distribute) unique passwords (not a
default value) and prohibit the reuse of passwords based on the profile
(initially prohibited to greater than 8 times since last use). AUXDATA
shall change the order in which a random password is generated for new
user accounts. A new password shall be generated AFTER the new user\'s
roles have been selected and the SAVE button is clicked. Also, AUXDATA
shall increase the size of font used to display the passowrd and remove
the highlight. A role will be added so some users can have the option to
email passwords while others will not.

**AUXDT00000192 (version 2.0.35; rolled 07/3/06) \--** AUXDATA shall
Copy code from the **[Clear all Parameters]{.underline}** button to the
**[Return to Menu]{.underline}** button.

**AUXDT00000158 (version 2.0.34; rolled 06/19/06) \--** AUXDATA shall
modify the Member Roster report to allow multiple competencies, multiple
offices, or multiple (any other paramter), a status, and status date to
be selected in the parameter form.

**AUXDT00000191 (version 2.0.33; rolled 06/19/06) \--** AUXDATA shall,
in Member Status, on the Awards tab page, be modified so that AWARDEES
from other districts do not display in the AWARDEES page when they are
not in your chain of command.

**AUXDT00000157 (version 2.0.32; rolled 06/12/06) \--** AUXDATA shall
set to vacant all offices held by a member that transfers out of the
flotilla, division, or district. If the member transfers within the same
division or district, those offices will not be affected. Also, when a
unit is disestablished, the same rules apply as an individual member
transfer. Exception, no vacant record created for FLOT offices because
the FLOT no longer exists. DIV and DIST offices that are ended, shall
have a vacant record inserted.

**AUXDT00000180 (version 2.0.31; rolled 05/30/06) \--** AUXDATA shall
correct the problem with the unit training management report where task
assignments from other units are showing incorrectly as requirements,
where tasks are not assigned to competencies by the assigned to units.

**AUXDT00000181 (version 2.0.30; rolled 05/25/06) \--** AUXDATA shall
fix the TMT_BASE.fmb so that it will compile. TMT_BASE.fmb won\'t
compile because of an error within the form: INSERT INTO TRNG_TYPE
VALUES (NULL,:TRNG_TYPE.DESCRIPTION,\'Y\'); The TRNG_TYPE table has 7
columns, 4 of which are the audit columns that are not being inserted
when these triggers fire. CATEGORY, CERTIFICATION and REFERENCE_MANUALS
are also a problem when the form is compiled. Same reason \-- missing
audit columns in the INSERT.

**AUXDT00000087 & AUXDT00000161** **(version 2.0.29; rolled 04/11/06)
\--** AUXDATA shall correct the column widths of Date Archived and
Archived By on the Restore from Archive screen. The text is being cut
off. Also spelled Archived out on both buttons since there was more
room.

AUXDATA shall correct/update the Navigation Equipment LOV values that
are greater than 4 characters. The field has a max of 4 characters, and
there were previously values in the List of Values that were greater
than 4 characters. All data in the NAVAIDS table was updated to the new
values to fix any previously entered data.

**AUXDT00000038 & AUXDT00000174 (version 2.0.28; rolled 04/11/06) \--**
AUXDATA shall update the Member Status module so that a resource
assignment is changed only when the primary owner moves to a new unit.
AUXDATA shall correct a problem with member transfers and archived or
un-inspected resources: When a member transfers and an owned resource is
already archived or un-inspected, a new resource_assignments record gets
created and causes the resource to show up in Activity Logs. This has
been corrected. AUXDATA shall not allow duplicate status dates. If a
user attempts to perform multiple member transfers on the same day, the
user will receive an alert that it is not allowed. They may then contact
the AUXDATA Help Desk for assistance. Also, if a member status change
occurs, and then a member transfer is attempted on the same day, the
application will allow it, but the transfer will be dated one day
forward. AUXDATA shall increase the width of the Member ID field on the
Member Status Change screen and the Member Transfer screen. The Empl IDs
were getting cut off previously.

**ECR AUXDT00000156** **(version 2.0.27; rolled 03/29/06) \--** AUXDATA
shall attempt to correct the RESOURCE_LOGS table locking problem by
setting the PK in the Activity Logs data block. AUXDATA shall provide a
more understandable error message when records are locked.

**STR AUXDT00000162 and ECR AUXDT00000168** **(version 2.0.26; rolled
03/28/06) \--** AUXDATA shall correct the Member Status module to update
resource_end_date when a member\'s status is updated to inactive. It was
previously not updated resource_end_date, only assignment_end_date.
AUXDATA shall correct the Member Status module so that when a member is
transferred, the status date is populated with sysdate. It was
previously leaving status date null.

**AUXDT00000140 (version 2.0.25; rolled 03/14/06) \--** AUXDATA shall
modify the Member Status module and prevent duplicate status dates from
being entered. AUXDATA shall also modify the QTR_BILL table population
scripts to handle duplicate status dates should they occur.

**AUXDT00000124 (version 2.0.24; rolled 03/06/06) \--** Code was altered
to hide the Unit-drop down when logged in as a single unit or flotillia.

**AUXDT00000132 (version 2.0.23; rolled 02/27/06) \--** AUXDATA reports
were updated to improve system performance.

**AUXDT00000141 (version 2.0.22; rolled 02/16/06) \--** AUXDATA shall
change the wording of the Key for Underlined items to say as of the date
the report is run instead of end date. Put zeros where null values
occur. Put a n/a in the Attendance HRS column since hours do not apply
to Attendance, only #s.

**SCR AUXDT00000094, STR AUXDT00000138 (version 2.0.21; rolled 02/16/06)
\--** All forms listed above were modified to use bind variables
wherever applicable. Code for the task capture tab filter button on the
auxdata_activity_logs.fmb was fixed so that the Oracle Error: Unable to
Perform Query was fixed. Also, in all forms listed above, the code in
the filter package was fixed so that Error FRM-40112 no longer appears.

**AUXDT00000128 (version 2.0.20; rolled 02/6/06) \--** AUXDATA shall
restore the missing divider lines in the Officer Directory report.

**AUXDT00000126 (version 2.0.19; rolled 02/2/06) \--** The
TRAINING_MANAGEMENT query was modified, and the Completed Format Trigger
was updated so that when a sliding frequency is queried, the correct
colors will show depending on the completed date and the required date.
Also, the Annual 30 June code for the Completed Format Trigger was
updated so that when it has two months before its expiration date, it
will change to yellow.

**AUXDT00000101, AUXDT00000102, AUXDT00000130 (version 2.0.18; rolled
01/31/06) \--** AUXDATA shall modify the Individual Training Status
report so that the CSV format output does not have any extra columns
caused by commas. AUXDATA shall modify the Individual Training Record
report so that the CSV format output does not have any extra columns
caused by commas. AUXDATA shall modify the Unit Summary Data report and
correct the problem where UNIT-type activity hours were not being
included if the END_DTG_DC was null/empty. The hours should be included.

**AUXDT00000129 (version 2.0.17; rolled 01/30/06) \--** Added to_char
and format mask were added to the Data_model so the correct hours will
display when the report is run in .csv format.

**STR AUXDT00000114 & AUXDT00000127 (version 2.0.16; rolled 01/25/06)
\--** \* Some reports drop down lists were not in the correct order. An
Order_by unit_number was added to the record_groups that needed it. This
fixes the problem. Removed TO_CHAR(AWARD_DATE) function which was giving
the OP_AWARDS and VEMDV_AWARDS reports invalid dates.

**STR AUXDT00000121 (version 2.0.15; rolled 01/23/06) \--** AUXDATA
shall modify the Mission Details report. The Unit Number displayed
should be for the crew member, not the resource for the mission. Also,
any Position entries of \"T RAINING\" or \"NONLEAD\" have been corrected
and will always display as \"TRAINING\" or \"NON LEAD\".

**STR AUXDT00000122 (version 2.0.14; rolled 01/23/06) \--** PE_AWARDS
report was previously returning incorrect data. This has been corrected.
A date format mask in the Data Model was removed.

**SCR AUXDT00000099, SCR AUXDT00000100, STR AUXDT00000120 (version
2.0.13; rolled 01/23/06) \--** The Member Anniversary Status report
query was modified, and commas were replaced with colons to avoid extra
columns in the CSV output. The Email Directory report query was
modified, and commas were replaced with colons to avoid extra columns in
the CSV output. The problem with Mission Details report where a
read-only FLOT-level user tries to run the report and it fails with
\"FRM-41214 Unable to run report\". It also fails when a user selects
Units and all SubUnits.

**SCR AUXDT00000091, SCR AUXDT00000111, ECR AUXDT00000118, SCR
AUXDT00000119**

**(version 2.0.12; rolled 01/18/06) \--** AUXDATA shall provide
Quarterly Billing and Annual Billings reports in CSV format also.
AUXDATA shall provide Mission Detail in CSV format also. Quarterly
Billing and Annual Billing reports shall pull current Unit from QTR_BILL
table instead of PERSONS to eliminate problem with XFERs and other
inactive status changes after 31 Dec. AUXDATA shall improve the Member
Roster report. A new Report Parameter canvas was created for the Member
Roster report. It includes an option to print the shortened version of
the Member Roster report or the newly created Print Mailing Labels
report. Three fields: Competency, Office, and State were added to use as
additional search criteria.

**AUXDT00000113 (version 2.0.11; rolled 01/10/06) \--** AUXDATA shall
modify Unit Summary Data report queries to improve performance and data
accuracy.

**AUXDT00000110 (version 2.0.10; rolled 01/09/06) \--** AUXDATA shall
modify the Annual Billing report to improve performance and fix data
inaccuracies.

**AUXDT00000109 (version 2.0.9; rolled 01/09/06) \--** AUXDATA shall
modify Operations Performance Awards, Public Education Awards, and
Vessel Examination/Marine Dealer Visits reports to improve performance.

**STR AUXDT00000107 (version 2.0.8; rolled 01/04/06) \--** AUXDATA shall
modify Quarterly Billing report to improve performance and fix data
inaccuracies.

**AUXDT00000081**/**AUXDT00000108 (version 2.0.7; rolled 12/27/05) \--**
AUXDATA shall add to the Activity Log Details screen (when entering a
UMS / A / UADMS / VSC / PB activity-Flotilla Marine Safety Vessel Safety
Check) two new windows for collecting data for \"Number of High Risk
Vessels,\" and one for \"Number of First Time VSCs.\"

**AUXDT00000104 (version 2.0.6; rolled 12/12/05) \--** AUXDATA replaced
hard coded URL and Report Server number to variables populated from the
database to avoid having to maintain multiple copies of the same .fmb

**AUXDT00000098 (version 2.0.5; rolled 12/07/05) \--** AUXDATA modified
the SQL in the Mission Details report so that the report runs much
faster.

**AUXDT00000093 (version 2.0.4; rolled 12/05/05) \--**

Member Roster CSV \-- Order of columns improved. Unit Number and Empl ID
put first before Last Name. Base Enrollment Date and OCC moved to match
PDF order of columns.

Unit Training Status \-- EmpId sometimes in Col A and sometimes in Col J
with the data moved around. Also records that have Instructor Hours
shifts data 2 columns. CSV format no longer has extra columns due to
commas in the data. Emp ID is always in the first column now.

Unit training Record \-- Some people enter the Instructor in Task with a
comma. This causes a data shift. CSV format no longer has extra columns
due to commas in the data.

Resources by Flotilla \-- Some records have the Name dropped which
causes a 1 or 2 column data shift. It appears that it is where there is
a Co-Owner. The PDF version also has a blank for these same names. CSV
format no longer has extra columns due to commas in the data. Now
instead of a blank when multiple owners exist, report displays
\"MULTIPLE OWNERS ASSIGNED\" or \"NO OWNERS ASSIGNED\".

Calendar Buttons throughout application via CITRIX are fixed.

In Activity Logs, improved informational message at the bottom of the
screen when last record is hi-lited. \"The last record may not be
deleted. You may add a EOM record, and then delete desired record.\"

**AUXDT00000092 (version 2.0.3; rolled 11/14/05) \--** AUXDATA shall
correct the problems encountered with the following reports when rolling
to 10G: Member Roster; Auxiliary Unit Listing; Member Anniversary
Status; Patriot Readiness: \* AUXDATA_REPORTS.fmb was modified to
include missing URL production entries that were causing Patriot
Readiness report to not run at all. \* PATRIOTREPORT.rdf was modified to
improve CSV format. Report query items were moved around / re-ordered to
fix the order of columns. \* CHAIN_OF_COMMAND.rdf (Auxiliary Unit
Listing report) CSV format was displaying extra columns, etc.) Data
within the UNITS table was updated to remove commas within the UNIT_NM
column. The commas were causing extra columns in the CSV format. No
report changes were made, only data updates.

\* Member Roster CSV format problems were handled by creating a separate
report called MEMBER_ROSTER_CSV.rdf. See ECR AUXDT00000089 for details.
No additional changes to Member Roster under this ECR. \* The Member
Anniversary Status report was fixed (was not displaying correct data in
CSV format). A conditional formatting trigger was used in the past to
display only the member\'s with 5 year anniversaries, and this
\"conditional formatting\" was not working with CSV for some reason. I
removed the trigger, and put the necessary code within the report query
instead. \* Member Anniversary Status report "sometimes" throws an
error. I am unable to re-create this error on production, so I will have
to monitor and see if it happens again. There are some comments in the
code within the AUXDATA_REPORTS form where strange P_1 errors have
occurred in the past ... but no name or date, or explanation. The P_1
field has to do with the parameters ACTIVE, INACTIVE or ALL as a choice
for some reports. This report, however, does not have that option. \*
Member Status \> Member History Update: the Update History button was
enabled for all users, and should be visible to only those with the
MBR_HIST_UPD role. Code was added to the AUXDATA_MEMBER_STATUS form to
fix this problem. \* The User Admin button on the Main Menu is also
appearing for users that should not see it. The AUXDATA_ADMIN role
should be granted to only a few select users. FSO role had the
AUXDATA_ADMIN role granted to it \-- might explain why everyone was
seeing it. \* Admin Reports -- all four admin reports were modified for
CSV format so that the extra header info would not show at the bottom of
each report display. The headers display correctly in 10g without the
extra query (select "text" from dual), so they have been removed. Report
property palette commented the same for all 4 admin reports. \* Security
Tracking CSV not working on production. SECURITY_REPORT.fmb was modified
and changed a lower case csv to upper CSV to match the value passed from
the parameter form. Also, modified and commented URL and repserv90 in
the production version of SECURITY.fmb.

**AUXDT00000073 (version 2.0.2; rolled 10/31/05) \--** AUXDATA shall
upgrade to AIS 10g.

**AUXDT00000068 (version 2.0.1; rolled 10/31/05) \--** AUXDATA shall
upgrade to Oracle 10g.

**AUXDT00000070 (version 2.0.0; rolled 10/31/05) \--** AUXDATA shall
upgrade to Solaris 9.

**AUXDT00000080 (version 1.4.64; rolled 09/16/05) \--** AUXDATA shall
update the Unit Summary Data report facility display and show
descriptions in bold where it displays the total number of facilities
owned by a flotilla between the parameter dates.

**AUXDT00000078 (version 1.4.63; rolled 09/13/05) \--** AUXDATA shall
modify code so that when a PDLCFT resource U/W activity is entered, and
Crew Assignments are assigned, if member is certified in PCO, he/she
should not receive the alert "Member not certified for this position."

**AUXDT00000075 (version 1.4.62; rolled 08/30/05) \--** AUXDATA shall
when an activity UCG / A / UCGOPS / ATON / PRIVATE, FEDERAL or BRIDGE is
entered, on the Activity Logs Details screen, in the ATON DETAILS
section, under Type, have the drop down menu only display Discrepancy
and Verification. Do not display Repair / Maintenance.

**AUXDT00000066 (version 1.4.61; rolled 08/18/05) \--** AUXDATA shall
improve the Training Management report with the following enhancements:
Handle REYR and REWK. Query handles getting CERT_ID 3 or 4 (REYR or
REWK) from PERSONS_COMPS. Then, color coding is handled by Conditional
Formatting in Report Builder. Member name has blue background if REYR,
and a pink background if REWK. Name Color Coding key added also. Remove
the \"TEACH COURSE LESSON\" task column under Instructor competency, and
any other inactive tasks from the report. Remove CSV option from report
parameters. PDV is only option now. Set today\'s date as default, and
set 1 as default Task. A better label for #2 would be \"Currency
Maintenance Tasks.\" Increased font size throughout report. Improve
performance of report. Query rewritten to speed up report.

**AUXDT00000063 (version 1.4.60; rolled 08/02/05) \--** AUXDATA shall
update the training status report to include an annual (calendar year)
frequency ending on June 30th. Also, new report called Training
Management is available under Training, Reports, Unit reports. This
report also handles the Annual 30 June frequency for Operations
Workshop, Instructor Workshop, and the VE/MDV Workshop.

**AUXDT00000053 (version 1.4.59; rolled 07/18/05) \--** When an office
holder retires, transfers, or is disenrolled, have the system set any
offices held to vacant. AUXDATA is already handling resources and
offices when a member's status is changed to DTH, DADM, MREQ, or FDUE.
When a member is RET, AUXDATA will update the end-date for the member's
officers record, and also insert a vacant office record. When an office
holder transfers, AUXDATA will update the end-date for the member's
OFFICER records, and also insert a vacant office record. When a unit is
disestablished, update the end-date for the member's OFFICER records. No
vacant office records needed in this case. When a member who has access
to AUXDATA retires or is disenrolled, revoke AUXDATA access. Created a
nightly CRON job to lock accounts.

**AUXDT00000061 (version 1.4.58; rolled 07/11/05) \--** New Mission and
Employment Categories added to AUXDATA:

There are 12 new Missions that need to be added to AUXDATA They all will
be Unit Missions. The Activity, Status and Sub-Activity

for all will be UCG A UCGOPS.

The new Mission is HS (Health Services).

The Sub Missions are:

93A - Direct Health Care - Medical

93B - Direct Health Care - Dental

93C - Direct Health Care - Allied Health

93D - Clinical Support - Medical

93E - Clinical Support - Dental

93F - Contingency Response Deployment

93G - Health Care Training - EMR

93H - Health Care Training - BLS

93J - Health Care Training - General

93K - Safety & Environmental Health Program Support

93L - Operational Support 93M - Administrative Support

**AUXDT00000062 (version 1.4.57; rolled 07/12/05) \--** Corrected the
resource_logs filter problem that caused the DB to lock. Added code to
WHEN-NEW-RECORD-INSTANCE trigger in the Activity Logs form that disables
the Activity Logs and Approvals tabs when no resources are available to
choose from.

**AUXDT00000043 (version 1.4.56; rolled 06/27/05) \--** OFFICE UPDATE
ENHANCEMENT. A new OFFICE role has been created which will allow only
users granted this role to see the new Update Offices, Copy and Delete
buttons. In AUXMIS.fmb (main menu form), the global variable OFFICE was
created and initialized. If user has been granted the OFFICE role, and
is a NATL user, he/she will be able to see the new Update Offices, Copy
and Delete buttons. DIST users will see Copy and Delete buttons only.
NATL level users may see all NATL codes. DIST users may not currently
see the office codes screen. In the future, we may allow access. If so,
they will be able to see ADSO codes only. The staff listing was updated
on the Table Management screen. Only vacant officer records may be
deleted. If user attempts to delete a record other than vacant, he/she
will receive an alert. An officer record may be copied and will be
automatically assigned vacant. For DIST users, only RCO, ADSO, and VPPCA
records may be copied. For NATL users, any record may be copied. User is
notified that the record was copied and that it was set to vacant.
Attempts to Copy non-RCO, non-ADSO codes or non-VPPCA codes as a DIST
user results in an alert. NATL users with the OFFICE role have the
ability to add new office codes on the new Update Offices screen. The
New button allows a NATL user to create a new office code. When a new
office code is inserted, a vacant officer record is automatically
created. As a NATL user, Activate and Inactivate toggle on the same
button based on which record is highlighted. A check is performed behind
the scenes if a user attempts to inactivate an office code. If officer
records exist, the inactivation is not allowed until all officer records
have been set to vacant and deleted. When a user activates an office
code, a starter officer record is created and set to vacant. This
functionality is the same as when a new office code is created. User may
also update the Rank and Description of existing codes. All other fields
are read-only. When the Rank is updated on the Update Offices page, code
behind the scenes automatically updates all records in the Officers
table with the new Rank. User may sort by column headings and also use
the Filter option. Audit columns are displayed read-only for
informational purposes.

**AUXDT00000057 (version 1.4.55; rolled 06/21/05) \--** The
TRAINING_STATUS package was modified to correct the return of hours for
U/W and CREWAUG hours toward the Boat Crewmember competency. Previously,
if not CREWAUG hours existed, no hours were returned.

**AUXDT00000051 (version 1.4.54; rolled 06/13/05) \--** The filter
button on the Assign Competencies to People \> Assigned Competencies tab
page has been fixed.

**AUXDT00000049 (version 1.4.53; rolled 06/13/05) \--** CREWAUG lead and
non-lead hours are now included toward the Boat Crew Crewmember
competency on the Training Status reports.

**AUXDT00000047 (version 1.4.52; rolled 05/31/05) \--**

-   Code was added to update the LAST_LOGIN_DATE with SYSDATE in the
    SU_USER table when an accounts trigger is changed from the locked to
    unlocked state.

-   Code was also added to update the LAST_LOGIN_DATE with SYSDATE in
    the SU_USER table when the accounts password is reset.

-   A trigger called Pre-Form was added to block the \"Transaction
    complete: 1 records applied and saved\" message that appeared when
    the password was reset.

-   Code and trigger were added to both the Citrix and Non-Citrix
    versions.

**AUXDT00000042 (version 1.4.51; rolled 05/06/05) \--** The DIRAUX
Action button was not displaying members per user account District
correctly. Make sure members listed are only within the district that
the user\'s account is set to.

**AUXDT00000041 (version 1.4.50; rolled 05/03/05) \--** AUXDATA shall
update the "Missing PSQs" button now says "Member Option?" instead.
Updated tool tip that is displayed when mouse hovers also. The button
query was copied and carried over to WHEN-NEW-FORM-INSTANCE and
WHEN-BUTTON-PRESSED triggers so it is consistent. We also updated the
query in the DO Unfavorable report so that members who have no
competencies at all would show up also (outer join added to query).
Sugarek is listed on the report now, and he has no PERSONS_COMPS
records, but has a DO Unfavorable. Automatically set DIRAUX Required =
DO and Member Option = DO if a member is assigned a competency that
requires a DO investigation. Ran UPDATE script to sync all members who
currently have DO-required competencies \-- set DIRAUX Required = DO and
Member Option = DO.

**AUXDT00000009 (version 1.4.49; rolled 05/02/05) \--** AUXDATA shall
modify the Security \> DIRAUX Action button so that when an OS
Favorable, DO Unfavorable combination is returned, those members are no
longer included in the member list. New DO Unfav/OS Fav button created
on the Security screen. User will be alerted to update the DIRAUX
Required and Member Option fields to OS. Then a member list will appear.
Member may be selected, and user can proceed to Member Status screen if
desired. AUXDATA shall automatically inactivate any competency that
requires a DO Favorable if the member receives a DO Unfavorable
investigation. A CRON job has been created to run this procedure
nightly. It will automatically inactivate competencies (that require DO
FAVORABLE) when a member receives a DO UNFAVORABLE. After the CRON job
runs, an email notification will be sent to the DIRAUX Help Desk with
information regarding inactivation of competencies. This email
notification is sent only when inactivation of competencies occurs. In
other words, an email is not sent daily - only as needed. No news is
good news. The attachment contains the member info and competencies that
were inactivated. A new CERTIFICATION_ID has been created to track the
future inactivation of competencies. It is 6 - NOT CERTIFIED SECURITY.
In the Training module, in Assignments \-- we will now enforce not
allowing the assignment of a competency to a member if that member
already has a DO UNFAVORABLE. The warning alert will remain
informational-only for members who have not received investigation
results at the time of assignment. Members who already have a DO
favorable will be allowed the assignment. Members with an unfavorable DO
will not be allowed the assignment. When restoring from archive, those
members disenrolled after 1 January 2004, do not permit a change to AP
status. If disenrolled prior to 1 January 2004 only allow AP status. The
AP status choice was removed from the drop-down choices. If an attempt
is made to restore a member from archive and the member was disenrolled
after 1-JAN-2004, has no record of BQ, IQ, or AX after 1-JAN-2004 and
has no security investigation results ... this member will automatically
be set to AP status. AP is no longer a choice in the drop-down. Same
functionality was incorporated into the Change Status screen for
activating RET members. AP will be set automatically when the member was
RET after 1-JAN-2004, has no record of BQ, IQ, or AX after 1-JAN-2004
and has no security investigation results. AP is no longer a choice in
the drop-down. A new report has been created. The "DO Unfavorable"
report provides a list of members who have received a DO UNFAVORABLE
investigation result and a list of DO-required competencies, per member,
that were inactivated because of the DO Unfavorable. Only the
competencies that require a DO are listed for each member even though
all members with a DO Unfavorable are listed in the report. Training
Status report vs. Competencies Assigned to People discrepancies
...Checkbox and Assigned Comps tab page not in sync. Set the tab page
disabled until the member actually selects a member to work with and
keep checkbox in sync with whether the Assigned Competencies tab page is
disabled or enabled.

**AUXDT00000040 (version 1.4.48; rolled 04/29/05) \--** AUXDATA Unit
Summary Data report now displays correct hours for Operations, Safety.
Modified query of Operations, Safety Hours and \# of missions to not
include date_modified in the where clause. Also, changed START_DTG to
START_DTG_LOCAL in several other queries throughout the report.

**AUXDT00000037 (version 1.4.47; rolled 04/19/05) \--** The Default
where clause, in Task_Capture, was being set at the when-new-form level.
It has been changed to be set in the \'initialize\' program unit where
the training Type is set.

**AUXDT00000036 (version 1.4.46; rolled 04/19/05) \--** AUXDATA shall
correct the Air Competencies by removing all references to Airborne
Hours. AUXDATA will not be used to track Flight Hours by Pilots and
Aircrew. Remove the task \*(AC) Completed Requirements of OPS POL MAN\"
where ever it appears. Add the following new task for all Pilots and
Aircrew. \"Complete form ANSC 7015 submitted.\" It is to be an annual
requirement, on a sliding scale. Set this task to Red 12 months after
the last entry. Create a CRON job that will set the Pilot/Aircrew
competencies to REYR 12 months plus 45 days. After the last entry of the
\"Completed form ANSC 7015 submitted\" task that remains red.

**AUXDT00000002 (version 1.4.45; rolled 03/30/05) \--** AUXDATA shall
permit limited personnel to insert, update, and delete member history
records.

1\. Update History button has been added to the Member History tab.
Select users that have been granted the new MBR_HIST_UPD role will see
this button.

2\. The new MBR_HIST_UPD database role grants the ability to INSERT,
UPDATE and DELETE on the PERSONS and STATUS_CHANGE_HISTORY tables.

3\. Update History button takes user to a brand new canvas/screen.

4\. Header info at the top of the screen is read-only.

5\. All dates, time in and time out are calculated as records/dates are
changed and will refresh after Save.

6\. Back button is disabled when changes are made until Save.

7\. Last modified by \_\_\_\_\_ on \_\_\_ added to help track/audit
changes. New columns were added to the STATUS_CHANGE_HISTORY table and
are displayed on the new screen read-only.

8\. Record Created By and Date Created are auto-populated by a database
trigger if user does not fill in. They are updateable since we are
dealing with past records in some cases and user making the change may
not have been the creator of the original record.

9\. Prior Unit Number column is linked to List of Values and is
validated for accuracy. The Status column is linked to a List of Values
also, but cannot be validated because of the need to enter XFER records.

10\. History records are displayed/ordered by Status Date and will
refresh after changes and Save.

11\. User may use Create Record button, TAB down to next empty line, or
mouse-click on next empty line to initiate a new insert.

12\. Delete/Cancel Record removes an existing high-lighted record, or
cancels an insert that hasn't been Saved yet.

13\. There must always be at least one history record for a member. User
will not be allowed to delete the last history record and will be
alerted to this fact.

14\. STATUS_CHANGE_HISTORY and PERSONS must always be in-sync. The
current status and status date in PERSONS must match the latest history
record in STATUS_CHANGE_HISTORY. This is enforced and handled behind the
scenes when the user selects Save. USE CAUTION WHEN ENTERING HISTORY
RECORDS BECAUSE YOU WILL ACTUALLY UPDATE THE MEMBER'S CURRENT STATUS
DEPENDING ON WHAT YOU ENTER/DELETE. If the latest record entered is an
inactive status, and the user selects Save, the member will be archived.
User will be alerted and will have the choice to continue or cancel. The
best practice is to enter all history records desired and then save
once.

15\. When entering XFER records, burden is on user to know what Prior
Unit Number should be, and it will be required.

**AUXDT00000007 (version 1.4.44; rolled 03/17/05) \--** Auxdata shall
update the Training status report to no longer display air hours.

**Q2005-006-AD (version 1.4.43; rolled 03/1/05) \--** Auxdata shall
update the The Training status report to return a task as pending at the
correct time. A tool tip was also added and the text was made bold. The
instructor hours are added back to the report.

**Q2005-007-AD (version 1.4.42; rolled 02/23/05) \--** Auxdata shall
update the Security module and allow AP personnel to be set to DADM,
DTH, or MREQ status prior to security center determination (favorable or
unfavorable). This will allow personnel to be disenrolled that get tired
of waiting or have other complications.

**Q2005-005-AD (version 1.4.41; rolled 02/23/05) \--**

1\. AUXDATA shall update the Resource Status module and display inactive
owners (in red) and allow them to be deleted (not added).

2\. AUXDATA shall put the cursor on the first record (task) in TMT, TASK
CAPTURE, TRAINEES when multiple tasks are being updated on the TRAINEES
tab. This so that the copy participants button works better.

3\. Do not allow member status to be changed to MREQ, DADM, DTH or FDUE
if solely-owned resource has unapproved activity logs.

User will receive an alert and will not be allowed to change the
member's status until activity logs are approved.

Also, added text to warn users that resource updates should occur prior
to member status updates.

**Q2004-053-AD (version 1.4.40; rolled 02/23/05) \--** AUXDATA shall
enable ownership update after restoring a resource from archive. AUXDATA
maintains the old primary ownership link, and won\'t allow user to enter
a new owner. This may be related to previous owner having been updated
with an inactive status, such as DTH. Investigate and fix code. See
attached documentation when attempting to update owner.

**Q2005-004-AD (version 1.4.39; rolled 02/10/05) \--** AUXDATA shall
correct the error occurring where function was not returning correct
value (or not returning a value at all) if a member did not meet the
current year requirements but was current the previous year. The
TRAINING_STATUS package, specifically the F_IT function, was modified to
correct this error. The function was also completely reformatted to
allow for easier maintenance and readability.

**Q2005-002-AD (version 1.4.38; rolled 02/02/05) \--** AUXDATA shall
update Member Status as described (A member having a history, or not, of
BQ, IQ or AX has no date limitation. When activating a RET status
member, we are checking to see if the member was set to RET after
1-JAN-2004. If so, we allow the activation to a BQ, IQ or AX. If the
member was set to RET before 1-JAN-2004, and the member does not have a
FAVORABLE security investigation, we force the member to be set to AP.
The same rule applies when restoring MREQ or FDUE members from archive.
If the member was set to MREQ or FDUE before 1-JAN-2004 and has no
FAVORABLE security investigation, we force AP status. Lastly, we do not
allow DADM or DSEC members to be restored from archive.

Bottom line, the date rule only applies to whether the member was set to
RET, MREQ or FDUE before or after 1-JAN-2004. Having a history of BQ, IQ
or AX at some point is checked without a date rule. Further, the member
either has a FAVORABLE security investigation or not, no date checked
here either.

I fixed the latest bug where Change Status for a member from a good
status to another good status (BQ to IQ, etc.) was not working. This is
on AUXDEV right now for testing; and with John's approval, I will get it
on AUXPROD today.)

**Q2005-001-AD (version 1.4.37; rolled 02/01/05) \--** AUXDATA shall:

1\. Unit Name field on Persons in Unit screen was increased from 30 to
50 characters, and increased width of display.

> **NOTE:** The same modifications were made in the CITRIX version of
> the form.

2\. Increased the width of the display for Unit Name on the
Update/Delete screen. Width of field was already set to 50, cosmetic
change only.

> **NOTE:** The same modifications were made in the CITRIX version of
> the form.

3\. Report \-- AUXDATA Resources at Unit not displaying Unit Name
correctly. Increased width of Unit Name in header. Header of report now
displays the Unit Name and Unit Number correctly.

**Q2004-031-AD (version 1.4.36; rolled 02/01/05) \--** AUXDATA shall:

1\. The hours reported under activity UCG / A / UCGOPS / MS / TRAINING
should be counted exactly the same as hours reported under UMS / A /
UADMS / AUXADMN / OTHER. This applies to PE Awards report, Operational
Performance Awards report and VE/MDV Awards report. The hours should NOT
be counted as underway hours or as IT (instructor) hours for currency
maintenance requirements. In the Training Status Reports (individual and
unit), exclude TRAINING hours for AUXMP/TRN and CGOPS/TRAINING. Include
hours for MT/ Lead only.

2\. The hours reported under activity UCG / A / UCGOPS / MS / TRAINING
or UMS / A / UADMS / AUXADMN / OTHER should NOT be counted for
Performance Awards for IT, COX, CREW, PWO u/w hours. **NO CHANGES
REQUIRED \-- CONFIRMED HOURS ARE NOT INCLUDED**

3\. The hours reported under activity UMS / A / UADMS / AUXADMN / OTHER
should be included in Sustained Service hours. The hours reported for
sub-missions 22A, 22B and 70U should be included in Sustained Service
hours. **NO CHANGES REQUIRED \-- CONFIRMED HOURS ARE INCLUDED**

4\. In the Operational Performance Awards report, the hours reported
under Air or Boat /MS/SAFESEC or /MS/HARPAT activities should be
included.

5\. Training Status reports - Instructor competency tasks are displaying
red when they should be displaying in black to indicate previous year
currency. (TRAINING_STATUS package)

6\. Activity Logs à Crew Assignments - Select Flotilla filter should
remain in effect when users toggle between Activity Logs and Crew
Assignments, and still enforce including or excluding AP status members
as required. (AUXDATA_ACTIVITY_LOGS.fmb)

7\. Activity Logs à Crew Assignments - Filter button filter will only
remain in effect while working on the Crew Assignments page, one
activity at a time. This is necessary because AP status members are
included only for Unit type activities, and are to be excluded when the
activity is Air, Boat or Radio. The block property for the member list
on the Crew Assignments screen cannot handle both conditions at the same
time. See #10 below for similar functionality.

8\. Activity Logs report - Start Date and End Date range parameter is
not working. Report is displaying ALL activity logs for the Unit
selected regardless of date range entered.

9\. Training \> Task Capture \> Trainees - When Select Flotilla filter
is used, it shall remain in effect until user chooses another filter
option, including leaving the Trainees screen. Also, when multiple tasks
are listed at the top of Trainees screen, Select Flotilla filter shall
remain in effect as user picks a different task to work with.

10\. Training \> Task Capture \> Trainees - When the Filter button is
used, it shall remain in effect until user selects Unfilter, leaves the
Trainees page, or selects a new task (from multiples listed at the top
of the screen) to work with. The Filter button filter is valid for
working with one task at a time only. This is necessary because AP
status members are included only for Unit or General category tasks, and
are to be excluded when the task is any category other than Unit or
General (Air, Boat, etc.). The block property for the member list cannot
handle both conditions at the same time. Therefore, if a different task
is selected to work with, the member list must be refreshed to include
or exclude AP status members, based on the task chosen, and the previous
Filter used gets overridden.

**Q2004-087-AD (version 1.4.35; rolled 01/07/05) \--** AUXDATA shall
update the Quarterly_billing changed to not pick up persons who already
have a valid status. Annual_Billing changed to pick up persons who have
a status of XFER.

**Q2004-027-AD (version 1.4.34; rolled 01/03/05) \--** AUXDATA shall
update the retrieve from archive canvas, displaying the reason for
disenrollment and adding a FILTER button. Added Status column and Filter
button on Restore from Archive screen.

**Q2004-038-AD (version 1.4.33; rolled 01/03/05) \--** SECURITY
ENHANCEMENTS \-- PHASE IV,

**[PART I]{.underline}**

1\. AUXDATA shall improve the security and
tasks/competencies/certification relationship:

> When a member is assigned a certification that requires a DO
> investigation, AUXDATA shall check to see if member has a DO favorable
> status, and if not, provide an alert "Assigning this competency will
> now require this member to have a favorable DO security investigation.
> Once the 3-year grace period is over, you won\'t be permitted to
> assign the competency.\" Update the DIR_REQD and MBR_RQST fields in
> the SECURITY table with value "DO" when competency is assigned. After
> the 3-year grace period, we won't allow the competency to be assigned
> if the member doesn't already have a DO favorable.
>
> AUXDATA shall add a "DO security investigation required" checkbox to
> the Competency Maintenance "add new" and "update/delete" screens. The
> checkbox will allow users to maintain the DO requirement among
> competencies. The checkbox is linked to a new column in the COMPETENCY
> table that stores a Y or N value. See COMPS_REQ_DO.XLS for a list of
> competencies that require a DO investigation.
>
> Below will be handled with the year-end REYR script:
>
> *When a member is placed in Archive because MREQ, FDUE or DTH (need
> that because there are instances where this was done by mistake) all
> competencies are to remain in place. When a member is restored from
> Archive, the competencies requiring currency maintenance shall be
> treated as the rules require. That is:*
>
> *1) If the competency was current when archive and member is restored
> in the same year, competency remains current.*
>
> *2) If the member is restored the following year and the competency
> was current and all tasks completed when archived, competency remains
> current. If the competency would have gone REYR at the end of the
> year, set it to REYR when restored.*
>
> *3) If the member is restored from 2 to 4 years after being Archive
> set the competency to REYR.*
>
> *4) If the member is restored 5 years after being Archived, remove
> that competency.*

2\. AUXDATA shall improve the security and member status relationship:

>  If member is Archived or change status to DTH, RET, MREQ, set
> SECURITY.ACTIVE_IND = 'N'. If restored from archive or change status,
> activated from RET status, set to 'Y' . PERSONS and SECURITY active
> indicators must match at all times.
>
>  If member restored from archive, check previous security history.
> Don\'t allow restore if member had a previous unfavorable or appealed
> status.
>
>  If member change status, activated from RET status, check previous
> security history. Don't allow restore if member had a previous
> unfavorable or appealed status.
>
>  If member restored from archive, check that DO status favorable is
> not over 10 years old. OS status favorable is good forever. If
> favorable, restore member immediately.
>
>  If member restored from archive or activated from RET, and no
> previous security history exists, automatically set member to AP
> status and Approval Pending unit.
>
>  DSEC and DADM members may NOT be restored from archive.
>
>  On Restore from Archive screen, display \"Status\" column and be
> able to sort by.
>
>  Remove HON and HCOM status codes from the database. Update to
> Inactive - Marvin Schwall, Edward Weiss and Sage Linsenbaum.
>
>  Add 15-year retirement eligibility check to Member Status, Change
> Status (so it matches functionality in Security, Member Status, Update
> Status).
>
>  Create a database trigger to enforce integrity between
> active_person_ind and active_sec_ind. Run script to update mis-matched
> records in SECURITY.
>
>  Run update script to modify all old style EMP_Ids with the new
> sequence-generated IDs.

3\. AUXDATA shall force a 10-digit, ZIP+4 format zip code entry
throughout the application - add a dash (-) and/or trailing zeros to all
zip codes when the end user only enters a 5-digit zip. Zip code must be
a minimum of 5 characters. If user does not know the +4 extension,
application will insert zeros so all zip codes are formatted the same.

> Zip code data entry exists in three places:
>
> Member Status, Insert a new Member
>
> Member Status, Emergency Contact
>
> Resource Status, Insert a new Unit
>
> Further, all existing data in the PERSONS, EMERGENCY_CONTACT and UNITS
> tables shall be updated to 00000-0000 format. Run ZIPCODE_CLEANUP.SQL.

4\. AUXDATA shall make the Security menu button RED if action is
required within the Security module. Action required is dependent on the
DIR_AUX Action, Missing PSQs or SECCEN Action buttons, and is simply an
alert to users to enter Security to do something. If the button is
GREEN, no action is necessary, but the user may still enter the module.

5\. AUXDATA shall modify the condition(s) that allow Update Unit and
Update Status to be enabled when a user enters the Member Status tab of
Security. If user updates status and forgets to update unit, Update Unit
is not enabled when the user returns. Right now, it\'s a one-shot deal.
Modify code to include \"IF status = AP OR IF Unit = Approval Pending%
\....

6\. Fix Update Status alert, text is cut off.

7\. When inserting a new member, auto-pop the Unit (000-99-99), Unit
Name (Approval Pending Unit) and Status = Applicant.

8\. In Security, Reports tab \-- remove the two extra Security report
buttons.

9\. Security tab, Member Status field \-- a \"Pending\" member\'s status
should update to \"Existing\" immediately after his/her status is
changed (after Member Status tab, Update Status is performed).

10\. Increase the width of ZipCode column on the Patriot Readiness
report to accommodate 10 characters 00000-0000. All other reports
display the 10-character zip code accurately.

**[PART II]{.underline}**

### [ACTIVITY LOGS]{.underline}

> 1\. AP status members shall be listed in the Crew Assignments member
> list only for Unit activities. AP status members are not allowed to
> participate in any Boat, Air or Radio type activities.
>
> 2\. In Crew Assignments \-- For Unit activities only, if AP status
> member is assigned, auto-pop TRAINEE in the position field. Provide
> alert, "AP status members may be set to TRAINEE only." if user
> attempts to change to LEAD or NON LEAD.
>
> 3\. In Activity Logs, Task Capture \-- AP members shall be included in
> the member list for Unit or General tasks only. Exclude AP status
> members when category is AIR, BOAT or RADIO. Create two new
> categories: MS-Marine Safety and VALID-Validator to distinguish the
> individual tasks that should not be available to AP members, that were
> previously classified under Unit or General.
>
> 4\. On Crew Assignments page, increased width of Select Flotilla
> button, unit name display field from 30 to 40 characters to
> accommodate Portsmouth Harbor Light Flotilla. Previously was receiving
> a "data too large for field" warning.

### [TRAINING]{.underline}

> 1\. In Training, Task Capture \-- AP status members shall be listed
> only for unit or general type tasks. Exclude AP status members when
> category is AIR, BOAT or RADIO. Create two new categories: MS-Marine
> Safety and VALID-Validator to distinguish the individual tasks that
> should not be available to AP members, that were previously classified
> under Unit or General.
>
> 2\. In Training, Assignments \-- do NOT show AP status members.
>
> 3\. In Training, Certifications \-- do NOT show AP status members.
>
> 4\. In Training, Task Capture - increase the width of Select Flotilla
> button, unit name display field from 30 to 40 characters to
> accommodate Portsmouth Harbor Light Flotilla. Previously was receiving
> a "data too large for field" warning.
>
> 5\. Verify Lt. Hall's current (6 Dec 2004) list of competencies that
> require Favorable DO or Favorable OS investigations. Add FIXED/MOBILE
> RADIO FACILITY OPERATOR ?

### [MEMBER STATUS]{.underline}

> 1\. When inserting a new member, applicant shall be assigned to their
> \"applying\" flotilla - NOT to an approval pending flotilla. Remove
> the previously added auto-pop of approval pending unit.
>
> 2\. Districts shall transfer members currently in approval pending
> units to valid units as soon as possible. Eventually, the approval
> pending units will be de-activated, and only AP member status will
> exist.
>
> 3\. Do NOT allow the insert of a new member if that member was
> previously DSEC or DADM. Users previously could confirm and bypass the
> duplicate warning and just insert the member as new, even if the
> member had an unfavorable past disenrollment.

### [SECURITY]{.underline}

> 1\. Add a new column to SECURITY table, SECCEN_FWD_OPM (SECCEN
> forwarded to OPM date). Add new date field (Date SECCEN forwarded to
> OPM) to data entry screen in Security module.
>
> 2\. Remove the Update Unit button and functionality from Security,
> Member Status tab page.

### [SECURITY REPORTS]{.underline}

> 1\. Add new date field filter (Date SECCEN forwarded to OPM) to
> Security Tracking report parameters.
>
> 2\. Add new date field (Date SECCEN forwarded to OPM) to display on
> Security Tracking report.

**Q2004-086-AD (version 1.4.32; rolled 12/21/04) \--** AUXDATA shall
update the Quarterly and Annual Billing reports so that they do not
include members in \"AP\" status.

**Q2004-023-AD (version 1.4.31; rolled 12/21/04) \--** Auxdata shall on
the Resources by Facility report, add fields to show the Latitude and
Longitude for each facility. This can be in addition to, or in place or,
the current "Location" field. Data for these new fields is found in the
Aircraft Information, Boat Information and Radio Information screens.

On the Resources by Facility report, add fields to show the Latitude and
Longitude for each facility. This can be, in addition to, or in place
of, the current \"Location\" field. Data for these new fields is found
in the Aircraft Information, Boat Information and Radio Information
screens. Also add a Field to show the Facility Number for each boat
facility found in the Boat Information screen. When run in CSV, place
the ZIP and Last Name in separate fields. Add the facility number to the
report, replacing the type column. Separate EMPLID and Last Name,
separate fields in CSV.

**Q2004-084-AD (version 1.4.30; rolled 12/21/04) \--** A new button,
\'Search by Name,EMP ID\' has been added to the \'Param_form\' canvas. A
new screen, Persons has been added to the auxdata_reports.fmb that
searches for a person by name or emp_id. Two(2) new parameters, P_ID and
P_where2 were added to the address_flotilla_data.rdf. The \'Select\'
statement in the data_model was altered to use the P_where2 clause. The
P_where2 clause was added to the address_flotilla_data.rdf before report
trigger.

**Q2004-061-AD (version 1.4.29; rolled 12/08/04) \--** When a member is
re-certified, only the data in the \"Last Update\" column on the
Certification Report should be changed with the entry of the Sysdate.
The date in the \"Date Qualified\" column should NOT be changed but
remain the date initially certified.

> 1\. If certification is brand new to the member, AUXDATA shall update
> existing \"not certified\" record in persons_comps to \"certified.\"
>
> 2\. If certification is a re-cert within 5 years, AUXDATA shall update
> existing record, but not update the original Certified Date.
>
> 3\. If certification is a re-cert, but over 5 years old, and an
> inactive record exists with the same comp_id, AUXDATA shall insert a
> new record.

**Q2003-056-AD (version 1.4.28; rolled 12/08/04) \--** AUXDATA shall on
the Radio Resource main screen, remove the labels and windows for Hull
Value, :Mach Value, Elec Value, Other Value and Total Value. In their
place, add the label \"Station Location\" and a window labeled
\"Latitude\" and a window labeled \"Longitude\".

**Q2004-079-AD (version 1.4.27; rolled 11/02/04) \--** Auxdata shall
make the following changes to the Individual Training Status and Unit
Training Status reports:

-   Altered task: **Airborne Hours** to **Airborne Hours XX Lead XX
    Total**.

-   Changed Air Tasks Frequencies to S(C) Sliding Calendar.

-   Changed Instructor Pilot, Flight Examiner's Number Required to 3.

-   Updated the RESOURCE_CLASS_CD column in the CURRENCY_REQUIREMENTS
    table to 'UNI' for Vessel Examiner.

-   U/W HOURS 8 A(C) added to the Currency Maintenance Requirements for
    PCO.

-   District PCO Qualification Program task added.

-   REYR and REWK are now displayed in red.

-   If Status is REYR or REWK the date the status turned REYR is
    displayed.

-   The blue section of the US flag flown without the stripes is called
    The Union Jack.

-   Due Date field has been made larger.

-   Paddle Craft Operator added.

**Q2004-082-AD (version 1.4.26; rolled 10/21/04) \--** Auxdata shall
display the system name on the User Management Forms.

**Q2004-081-AD (version 1.4.25; rolled 10/21/04) \--** AUXDATA cleaned
up the flow of window names throughout the application to better
facilitate Robot testing.

**Q2004-083-AD (version 1.4.24; rolled 10/13/04) \--** Report was not
accurately reflecting quarterly dues for new members due to the gap in
between base enrollment date and when the member receives a favorable
security investigation and is XFERd to a valid flotilla. Dues are due
when the member gets XFERd to a valid flotilla from the temporary
\"Approval Pending Flotilla.\" Report was changed to reflect this.

**Q2004-046-AD (version 1.4.23; rolled 10/05/04) \--** AUXDATA shall
modify the security procedures to require password change on first
logon, password expiration, and account locking after no use.
Specifically,

1\. Passwords must be at least 8 characters in length, have one
alphabetical character, have one number but can\'t begin with a number,
have one of the following special characters (#, \_ or \$) but can\'t
begin with the special character, must not be the same as the user name,
be too simple or be the same as the last password used.

2\. When a new user is created, the user will be given a temporary
password of AUXUSERNAME_1. The user will have one grace logon and will
be prompted to change his/her password.

3\. When a user\'s password is reset, the temporary password will be
AUXUSERNAME_1. The user will have one grace logon and will be prompted
to change his/her password.

4\. An account will be locked after three failed logon attempts. The
user may contact the AUXDATA Help Desk to have his/her account unlocked,
or wait one hour for the account to automatically unlock.

5\. User passwords will expire every 90 days. The user will be alerted
at logon 15 days out from the 90-day mark and will be given an
opportunity to change his/her password. If the user has not changed
his/her password by the 90th day, the account will be locked.

6\. After 90 days of inactivity, the user account will be locked. If
there is no user activity for a total of 180 days, the user account will
be dropped.

7\. If a user\'s account is locked or expired, the user will be alerted
at logon with a valid ORA- alert message. The user must contact the
AUXDATA Help Desk for assistance if this occurs.

8\. Add the following date displays to the User Admin form update
screens for information-only:

\* This user last logged into AUXDATA on 99-99-9999

\* This user\'s password will expire/expired on 99-99-9999

**Q2004-080-AD (version 1.4.22; rolled 10/05/04) \--** Unit Summary Data
report \-- \"Error populating group\" when selecting All units and
sub-units run individually radio button. ORA-01722 occurs when selecting
UNITS AND ALL SUB UNITS from Unit drop-down. The record group necessary
to populate the Unit drop down was missing from the auxdata_reports
parameters form. The record group was copied in from the old AUXMIS.fmb
single form. The ORA-01722 was subsequently fixed when the missing
record group was put in place, as well as the correct Units being
available for selection.

**Q2004-078-AD (version 1.4.21; rolled 09/28/04) \--** For the
Auxdata_activit_logs.fmb: Fields where added to the TMT Task Capture
screen and add date filters similar to those on the
AOPSHQ_VALIDATION.fmb form. A text field will appear when the manual
date filter buttons are in use. Sort buttons now available on the AOPS
version of the form Have been added.

**Q2004-077-AD (version 1.4.20; rolled 09/14/04) \--**

\* Member Roster report was previously only printing 1 page of data.
This has been corrected. Privacy Act info displays on first page of
report only. CSV format displays header and Privacy Act info correctly.

\* Address & Flotilla Data report displays Privacy Act info on first
page of report only.

*\* Wording of Privacy Act was changed on the Member Roster, Address &
Flotilla Data and Patriot Readiness reports. The word \"continued\" was
changed to \"contained.\"*

**Q2004-076-AD (version 1.4.19; rolled 09/14/04) \--**

For the Auxdata_activit_logs.fmb:

-   Allow person with correct competency to be assigned as \'Lead\'
    without a warning message appearing.

-   Prevent display of Pending flotilla in LOV of approving flotillas.

-   Prevent a person whose status is \'AP\' from being displayed in the
    list of persons.

For the TMT Unit_Training_Record and Ind_Training_Record reports:

-   Corrected bad joins and removed unused columns in the select
    statement in the individual and unit training record reports.

**Q2004-071-AD (version 1.4.18; rolled 08/24/04) \--** Freedom of
Information Confidentiality Notice, as shown below, needs to be included
on the first page of the Member Roster, the Address and Flotilla Data,
and OPRIII reports, when viewed and printed in both PDF and
CSV..CONFIDENTIALITY NOTICE - PRIVACY ACT OF 1974: The disclosure or
this material is subject to the provisions contained in the Privacy Act
of 1974, This Act, with certain exceptions, prohibits the disclosure,
distribution, dissemination, or copying of any material containing the
home address, home phone number, spouse\'s names, and social security
numbers, except for official business. Violations may result in
disciplinary action by the Coast Guard and/or civilian criminal
sanctions.\" Missing in Member Roster in CSV, OPR in CSV and PDF.

**Q2004-067-AD (version 1.4.17; rolled 08/31/04) \--**

-   Security Tracking report now offers the ability to filter by
    Previous Date, Previous Agency or Previous Level.

-   The three \"Previous\" data fields also display on the report.

-   CSV format column headers now appear.

**Q2004-070-AD (version 1.4.16; rolled 08/16/04) \--** AUXDATA shall fix
the following problem. When checking Command Approval and other
parameters and then reset password and exit, the Command Approval check
box becomes unchecked. Question: Is Command Approval enabled even after
the checkbox becomes unchecked?

**Q2004-069-AD (version 1.4.15; rolled 08/16/04) \--** AUXDATA shall fix
the bug where member-owned resource(s) is(are) not transferred
automatically when the member is transferred.

**Q2004-039-AD (version 1.4.14; rolled 07/26/04) \--** AUXDATA shall
modify the Auxdata application and User Management form to handle
read-only privileges for a new role called BASIC_MEMBER. BASIC_MEMBER
will be allowed update on their own records in AUXMIS.PERSONS AND
AUXMIS.PERSON_PHONE tables. The User Admin form will allow
administrators to grant and revoke the read-only role. A member with
read-only access can view Activity Logs, Member Status, Resource Status,
Reports and Training in AUXDATA. Access is read-only, except for Member
Status, where the member can update his/her own member record only.
Unit, Enrollment Date, Status and Gender cannot be changed however.

**Q2004-066-AD (version 1.4.13; rolled 07/26/04) \--** AUXDATA shall,

1\. Activity Logs \-- when toggling between Air, Radio, Boat and Unit,
if no resources exist for the type, Activity Logs tab is disabled.

2\. ZULU OFFSET is now required and validated against the LOV when
entering activity logs. This will prevent a blank/null or invalid value
from being saved with the log.

**Q2004-058-AD (version 1.4.12; rolled 07/26/04) \--** AUXDATA shall
display units in the report parameter form at the user\'s organizational
level and below (e.g. a division officer see their division and all
flotillas in that division) for the Patriot Readiness report.

**Q2004-054-AD (version 1.4.11; rolled 07/16/04) \--** AUXDATA shall
update all Virus Scan s/w on all servers.

**Q2004-044-AD (version 1.4.10; rolled 07/19/04) \--** AUXDATA shall
update the table management form to reflect current table schema and
team.

**Q2004-060-AD (version 1.4.9; rolled 07/14/04) \--** An internal
application package (RL_TAPI_PKG) was updated to use implicit cursors
rather then explicit cursors. This allows the application to find just
the specific records that it is looking for instead of cycling through
all of the records to determine which records are valid.

**Q2004-052-AD (version 1.4.8; rolled 07/19/04) \--** AUXDATA shall
update the V_TASK_FAVS2 view to check that the active indicator equals Y
in the UNITS_COMPS and TASK_ASSIGNMENTS tables

**Q2004-055-AD (version 1.4.7; rolled 07/12/04) \--** AUXDATA shall
correct the Transfer count in the Unit Summary and the duration problem
in the Mission Details reports.

**Q2004-056-AD (version 1.4.6; rolled 07/02/04) \--** AUXDATA shall
correct the global variable problem (FRM-40815).

**Q2004-049-AD (version 1.4.5; rolled 06/30/04) \--**

In the Mission Activity Report, in the Report Module, the number of
\"Exams Given\" are not shown on the report when entering the following
activities:

UCG / A / UCGOPS / CVS / CFV

UCG / A / UCGOPS / MS / UPV

The number entered in Exams Given field in the Activity Log Details
screen should be reported in the Exams Given in the Mission Activity
Report.

This problem was corrected. During the modification process, it was
noted that the report did not include the transactions occurring on the
end date. With Capt. Platt's permission, this was also corrected.

**Q2004-020-AD (version 1.4.4; rolled 06/30/04) \--**

-New SKILLS table contains all information on skills a person may
possess. SKILLS are transferred from existing occupation information in
the PICKLISTS table.

-New PERSONS_SKILLS table contains an entry for every skill a person
possess. One person may have an unlimited amount of skills. Links from a
person to a particular skill in the SKILLS table.

-Patriot Readiness report no longer shows security information. Report
now shows all skills that a person possesses. Report allows filtering
based on whether a person has a certain skill.

-Patriot Readiness report parameter page no longer allows filtering
based on security info.

Report now allows the ability to choose a skill and run a report finding
only those who have that skill. Also fixed preexisting bug with generic
competency searches.

-Patriot Readiness data entry screen no longer allows data entry of
security info. Existing skills for a person may now be
viewed/edited/deleted. Additional skills may also be added for a person.

-Member Information screen no longer has the ability to enter an
Occupation for a person, as that column is now obsolete, and has been
replaced with the more generic PERSONS_SKILLS table.

**Q2004-051-AD (version 1.4.3; rolled 06/23/04) \--** The End Date of
the Activity by State Report has been modified so that it will display
properly at the top of the page.

**Q2004-048-AD (version 1.4.2; rolled 06/16/04) \--** Removed
requirement to enter City, State, Country and Mother\'s Maiden when
DIRAUX Submitted date filled in. Also, Missing PSQs will now show all
members where the Member Option field is null (empty).

**Q2004-047-AD (version 1.4.1; rolled 06/16/04) \--** Retired or
otherwise inactive members no longer show up in report results. If
DIS/RET is entered under member requested sec clearence, it is changed
behind the scenes to be DR, which is the way it is stored in the
database.

## **[AUXDATA Version 1.4 (released 06/01/2004)]{.underline}**

On June 1, 2004, Auxdata Version 1.4 rolled to production. The core of
this release involved the Database Cleanup effort. This release
includes:

CR2003-001-AD, Foreign Keys

CR2003-002-AD, Trigger Renewal

CR2003-003-AD, DB Cleanup

E2004-001-AD, Billing Report Date Change

E2004-002-AD, Member Roster Report Zipcode

E2004-003-AD, DB Triggers; Resource Changes

**Q2004-029-AD (version 1.3.105; rolled 06/03/04) \--** The \"Security
Tracking Report\" button on the reports tab of the security form now
links to the security_report form.

Security Tracking Report created:

-   Can be created in Excel / PDF

-   Only district level or national level units are available for
    report. District level users will only see their district, National
    level users will see all districts as well as the national unit.

-   Additionally, results may be tailored to the situation through the
    use of filters, explained below:

    -   First, you must select the field you wish to look at under the
        \'value to be checked\' row, for example First Name, PSI Result
        DO or Member Status

    -   Then, after selecting what you will be filtering based on,
        select the type of filter, for example Is Empty will only return
        persons for whom that particular field is not filled in, or
        Equals will only return rows for which that particular field is
        equal to a value you specify

    -   Finally, if you are using a filter type such as Equals or Date
        Before, you must specify what the value must equal or what date
        will be compared to in the last row.

-   These filters can be further customized by using the AND/OR drop
    down menus between the filters

-   Initially, all three filters must be satisified for a result to be
    displayed, however, placing an OR between two filters will allow
    results that satisfy either of those two conditions to be displayed.
    Putting OR for both of the AND/OR options will display results for
    which any of the three filters are satisified.

> As examples,
>
> if you have:
>
> Member Status AND PSI Results DO OR PSI Results OS
>
> Existing Is Empty Is Empty
>
> This will display all existing members in that unit(s) who have at
> least one of their security results columns not filled in.

If you have:

> Seccen Recvd Date AND PSI Results DO AND PSI Results OS
>
> Date Before Is Empty Is Empty
>
> 05-MAR-04
>
> This will display any people for which the Seccen recieved a request
> before 3/5/04, and no results at all have been returned for that
> person.

After selecting a unit, and optionally creating any filters, hitting
\'Submit\' creates the report about the unit, which contains these
details about each person who meets the condtions.

\- Unit Number

\- Employee ID

\- First Name, Last Name, Middle Name

\- Diraux Required Clearance (OS/DO)

\- Member Requested Clearance (OS/DO)

\- Date Diraux Submitted / Date Seccen Recieved

\- Date PSI Completed OS / Date PSI Completed DO

\- PSI Results OS / PSI Results DO (Favorable/Unfavorable)

**Q2004-025-AD (version 1.3.104; rolled 06/03/04) \--** The Task Capture
portion of TMT has been modified so that the user can select many tasks
at once, and have all of them display when switching to Task Capture
tab. (Previously not all the tasks would display). Modifications have
also been made so that the user can delete multiple Task Capture records
at once (he will receive a warning prior to each delete). Finally, on
the Trainees tab, the \"Select Flotilla\" and \"Filter\" button have
been improved so that they work well together, and the list of available
persons refreshes properly as the user switches between different tasks.

**Q2004-006-AD (version 1.3.103; rolled 06/01/04) \--** Broke apart the
AUXMIS.fmb form into several pieces to make the application load faster
and make it easier for developers to work on. Broke apart the Activity
Logs, Member Status, Resource Status, and Reports buttons from the main
screen into their own forms. Also broke apart the Reports in the
Training form.

**Q2004-042-AD (version 1.3.102; rolled 05/26/04) \--** The Public
Education Awards report has been modified so that all people who are due
for awards are indeed displayed in the report. A correction was made to
the calculations behind the \"Credits\" column. Previously, many people
were not being displayed in the report who should have been displayed.
Now the change has been made so that persons with 30 or more credits
will display in the report.

**Q2004-041-AD (version 1.3.101; rolled 05/24/04) \--** SECCEN users may
now insert/update data on the DIR_AUX data entry side of the Security
page.

**Q2004-036-AD (version 1.3.100; rolled 05/04/04) \--** Security Phase
3:

1\. Add "DIRAUX Action", "Missing PSQs" and "SECCEN Action" buttons to
Security tab. Each button displays a list of members that require
action. Selection of a specific member to take action on returns user to
Security page with member's info auto-populated. User may continue data
entry as required. DIRAUX Action and Missing PSQs are only enabled for
DIRAUX users, and SECCEN Action is only enabled for SECCEN users.

> DIRAUX Action and Missing PSQs buttons are RED when action is needed,
> and green and disabled when no action is needed. SECCEN Action button
> is also RED when action is needed, and GREEN and disabled when no
> action is needed. When DIRAUX user logged in, SECCEN Action button is
> not visible. When SECCEN user logged in, DIRAUX Action and Missing
> PSQs buttons are not visible.
>
> DIRAUX Action returns members who are AP status and DO Status or OS
> Status is null, or member status is not DSEC, MREQ or RET and DO
> Status or OS Status is Unfavorable.
>
> Missing PSQs returns members who have not completed the required
> security paperwork/submitted a personnel security questionnaire.
>
> SECCEN Action returns members where DIRAUX Submitted date is not null
> and DO Status or OS Status is null.

Remove report alerts, since this will provide the prompt for action to
required parties?

2\. Disable States and Countries LOV buttons when SECCEN users are
logged in.

3\. Update Status - limit the Status choices depending upon Favorable or
Unfavorable, DO or OS Status updates. When DIRAUX user goes to Member
Status and selects Update Status, the status choices will be limited
based on different scenarios.

>  If member has FAVORABLE DO Status and/or OS Status, choices are IQ,
> BQ or AX
>
>  If member has UNFAVORABLE DO or OS Status, DSEC (disenroll for
> security reasons) is the only choice.
>
>  If member has UNFAVORABLE DO Status, but has FAVORABLE OS Status,
> choices are IQ, BQ, AX, DSEC, RET or MREQ.

4\. Required/Dependant fields - do we want to require any fields on
Security data entry? Any dependencies, if one field updated, require
other field(s)?

> If DIRAUX Submitted is not null, then require City, State, Country,
> Mother's Maiden, DIRAUX Requested.
>
> If SECCEN DO Status not null, then require DO Status Date. If OS
> Status not null, then require OS Status date.

5\. Validate State and Country entries against LOV.

6\. Add Unit Number to LOV display for SECCEN Emp ID and Member Name
searches.

7\. Add format dash 999-99-99 to all Unit Number LOV displays.

8\. Add phone numbers to Member Status view-only page.

9\. For Country field, store the 2-digit code in the database. LOV
displays code and description.

**Q2004-033-AD (version 1.3.99; rolled 06/01/04) \--** AUXDATA shall
remove the NOTES and TASK_NOTES tables from the dB and remove the
canvasses from the TMB Base form which allows insert, update, and delete
in those tables.

**Q2004-026-AD (version 1.3.98; rolled 04/28/04)** \-- AUXDATA shall add
to the anniversary status cron job, the SQL which will set te mail flag
to \'N\' for inactive personnel (DTH, DADM, MREQ, DSEC, FDUE) and
archive their persons table record, setting the unit_id to 99999999.

**Q2004-035-AD (version 1.3.97; rolled 04/22/04)** \-- The
member_unit_id and facility_unit_id LOVs previously shared the same
record group. The programer updated the query for the member_unit_id LOV
to handle insertion of new members while security investigations are
pending, and did not realize this. Now when users try to enter
facilities, they only receive the \"Approval Pending\" flotilla as a
choice. This does not apply to entry of facilities/resources, and so
created a new record group for facility_unit_id and put the old query
back in for that one.

**Q2004-034-AD (version 1.3.96; rolled 04/20/04)** \-- All updates
manually setting END_DTG_DC were commented out. This included code
within the EOM (End-of-Mission) Save button, the EOC End-of-Course Save
button, and the Transfer of a Resource Save button. The END_DTG_DC
should never be set manually. The RL_TAPI_PKG handles the processing of
this date field within the application.

**Q2004-028-AD (version 1.3.95; rolled 04/20/04)** \-- Security \--
Phase II

1\. Ability to update Status and Unit within the Security, Member Status
page. The Update Status and Update Unit buttons are enabled on the
Member Status tab, only for DIRAUX users, when one of the following:
Member Option, DO Status or OS Status fields have been updated. The
functionality works the same as changing a member\'s status within the
Member Status module; however, only a limited choice of status codes are
available.

2\. State and Country LOVs were added to the Security data entry tab.

3\. A 15-year retirement eligilibility check is performed when the
following fields are entered:

-   Member Option, DIS/RET (informational only, still alllows Save)

-   Update Status, Status (if member does not have 15 years of service,
    MEMBER REQUESTED DISENROLLMENT must be used.)

-   Update Status, Save (if member does not have 15 years of service,
    MEMBER REQUESTED DISENROLLMENT must be used.)

4\. Update Status - when a member\'s status is changed to DSEC
(disenrollment for security reasons) or MREQ (member requested
disenrollment), the member will no longer receive Auxiliary mailings.

5\. Reports tab updated to reflect correct name of Security Tracking
report. Other reports to be specified at a later date. Reports are still
under construction.

6\. Security \-- if DIRAUX user selects the checkbox for DO Requested by
CG Authority?, then the DIRAUX Required field will automatically be
updated with DO.

7\. The Security button on the main screen is the only option for SECCEN
users. All other menu options are disabled, except for Exit of course.

8\. Within Member Status, AUXDATA \-- Unit LOVs was updated to allow
APPROVAL units to display when inserting a new member.

**Q2004-032-AD (version 1.3.94; rolled 04/19/04)** \-- AUXDATA shall
modify the Unit Summary Report to allow the Multi L Course query to
include course completions in the count.

**Q2004-022-AD (version 1.3.93; rolled 04/19/04)** \-- AUXDATA shall
modify the Unit Summary Report and not count Trailer time on the Unit
Summary Report under Operations -- Safety.

**Q2004-019-AD (version 1.3.92; rolled 04/19/04)** \-- AUXDATA shall
update the Member Roster, the Address and Flotilla Data, and OPRIII
reports adding the Freedom of Information confidentiality Notice.

**Q2004-012-AD (version 1.3.91; rolled 04/07/04)** \-- AUXDATA Security
Enhancement

**[Purpose:]{.underline}** AUXDATA shall allow data entry, maintenance
and reporting of security investigation/clearance data for U.S. Coast
Guard Auxiliary members.

**[Basis:]{.underline}** All U.S. Coast Guard Auxiliary members shall be
subject to new security requirements. Existing members are required to
complete a SECCEN Personnel Security Questionnaire. New member
applicants and existing members must submit necessary security
paperwork/package. Member applicants will retain an "Applicant" status
until all security requirements are met. All members will be designated
either Direct Operations or Operational Support. Specific security
requirements for Direct Operations and Operational Support explained
further in another document.

**[Scope:]{.underline}** AUXDATA will store security data for each
Auxiliary member, and will incorporate the security data relationship
with existing member and task data. The inclusion of security
information is mandated and will be an enhancement to existing
application functionality. There will be new application access levels,
GUI screens and reports. Further, existing menu selections, member
status GUIs, database structures, etc. will be modified to accommodate
the security information.

> The requirement and scope of above has been broken into three phases:
> This install is Phase 1. Phase 2 will include Member Status update
> functionality and additional functionality changes. Phase 3 will
> include reports and data change alerts/notifications.
>
> From the AUXMIS main menu, limited users will see a Security button.
> Upon entry into the Security form, DIR_AUX users will be able to enter
> data related to security investigations for members of the Auxiliary.
> Security Central (SECCEN) users will also be able to enter data
> related to security investigations. Each user level has a distinct
> data entry area, and can only read the other. Both levels of users
> will be able to view a read-only screen of member status information.
>
> At a later date, after Phase 2 install, DIR_AUX users will be able to
> update Status and Unit for applicant members within the Member Status
> tab. Reports will follow after Phase 3 install.
>
> Specific functionality:
>
> 1\. Emp ID and Name Search buttons limited to DIST of user. DIR_AUX
> users see Existing and Pending Members. SECCEN users only see Pending.
>
> 2\. All date fields have a Calendar button. A single mouse-click will
> populate the field with the current date. Date format throughout is
> MM/DD/YYYY.
>
> 3\. Member certifications can be viewed on the Member Status tab.
>
> 4\. All current Auxiliary members have been updated with a DO or OS
> status (auto-populated in the DIRAUX Required field).
>
> 5\. When a new member is created via Member Status, Insert, a starter
> record is automatically entered in the SECURITY table. Later, user may
> search applicant/pending member within the Security module and update
> as needed.
>
> 6\. Reports tab is \"Under Construction.\"
>
> 7\. Cancel and Save are disabled on the Member Status tab. After Phase
> 2 install, they will be enabled when Unit and Status fields may be
> updated by DIR_AUX users only.
>
> 8\. Security tab, Member Option field is only for Existing members.
> Existing members have the choice of Disenrolling or Retiring. A check
> has been added when user selects DIS/RET for an existing member. If
> the member does not have 15 years of service, an alert will notify
> user that this member is not eligible to retire.
>
> 9\. State and Country drop-downs will be added with Phase 2 install.

**Q2004-014-AD (version 1.3.90; rolled 04/07/04)** \-- The AUXDATA user
admin form now has checkboxes to grant/revoke the two new Security
enhancement roles: DIR_AUX and SECCEN. There is also a new field called
Associates, where user may be classified as CONTRACTOR, ACTIVE DUTY
MILITARY, CG CIVILIAN, AUXILIARY MEMBER or UNKNOWN. This new
classification will assist with our link to personnel data within the
EDW at a later date. The new checkboxes and Associates field are
available on both the \"Users not in AUXDATA\" and the \"User in
AUXDATA\" screens. Only AUXDATA user administrators with the admin
option will be able to access the checkboxes for the Security roles.

**Q2004-018-AD (version 1.3.89; rolled 03/22/04)** \-- Post-install
corrections for Q2003-058-AD and Q2003-054-AD regarding read-only user
access. Within Resource Status, read-only users shall only have the
option to View. Insert, Update, Activate, and Restore from Archive is
not an option for read-only users.

**Q2003-54-AD (version 1.3.88; rolled 03/22/04)** \-- For members with
read only access:

In the MEMBER STATUS module remove access to the Restore from Archive
button.

In the RESOURCE STATUS module remove access to the Restore from Archive
button.

Remove access to the Update button on the Resources screens.

Provide members with SO-IS access the ability to VIEW the Facilities
screen the same as those with read only access.

**Q2004-013-AD (version 1.3.87; rolled 03/11/04)** \-- AUXDATA shall
update the key on the Unit Summary report showing the differences in
format based on the report medium. I altered the Key Code on the report
to include a message viewing the document using Adobe.

**Q2004-016-AD (version 1.3.86; rolled 03/16/04)** \-- The \"Individual
Training Status\" and \"Unit Training Status\" reports (AUXDATA
Training/TMT Reports screen) have been modified so that the underway
hours for the Personal Watercraft Operator competency display in the
same block as the rest of the requirements for the Personal Watercraft
Operator competency.

**Q2004-015-AD (version 1.3.85; rolled 03/11/04)** \-- AUXDATA shall
update the UNIT SUMMARY report and pass necessary parameters to count
the field values. The code was altered reflect passing three parameters
from the function.

**Q2004-011-AD (version 1.3.84; rolled 03/16/04)** \-- Database
functionality has changed so that, in currency reports, instructor hours
that a person logs under the mission code of \"CGOPS\" \-\-- training
the active duty Coast Guard \-\-- will be counted as hours toward the
instructor competency.

**Q2003-058-AD (version 1.3.83; rolled 03/02/04)** \--

1\. AUXDATA shall add a new RESOURCE_HISTORY table. This new table shall
track all resource history. Specifically, new records will be inserted
into this table when a resource is:

a\) transferred,

b\) transferred along with a member,

c\) un-inspected by entering an end date,

d\) activated after being un-inspected,

e\) inserted as a new Unit,

f\) inserted as a new Radio, Aircraft or Boat,

g\) archived,

h\) restored from archive,

i\) dis-established (unit)

Records will continue to be inserted into or updated within
RESOURCE_ASSIGNMENTS when a resource:

a\) owner transfers to a new unit/flotilla,

b\) transfers,

c\) a unit is dis-established,

d\) is restored,

e\) a new unit is entered,

f\) a new boat, aircraft or radio is entered.

Also, create new RESOURCE_STATUS table to store lookup status codes.
These codes can be selected from a poplist or LOVs, in the future,
throughout the application. They are used transparently when the above
resource history actions occur. Available status codes are:

XOPFAC Transfer resource

MBRXFR Transfer member with resource(s)

UNINSP Resource un-inspected

ACTIVATE Resource activated after being un-inspected

NEW New resource created

ARCHIVE Resource archived

RESTORE Resource restored from archive

DISESTB Resource / Unit dis-established

OWNER Resource owner changed (to be used in the future)

RETIRE Resource retired after 45 days past due on inspection

Update server-side script, RESOURCERETIREMENT.SQL . Resources are
retired after 45 days of being past due on inspection. Script will now
insert a record into RESOURCE_HISTORY with a status of RETIRE when
applicable.

2\. AUXDATA shall allow ownership of resources at the Division and
District level also. When a user [transfers a resource]{.underline} from
the Approvals screen, the Unit LOVs will include districts and divisions
now. Upon entry to the Resources screen, the [list of resources will
order by Unit Number]{.underline} (i.e., 013, then 013-01, then
013-01-01 \...).

When [inserting a new resource]{.underline}, user will also be able to
select District or Division from the LOV. Data displayed within the
Resource Status, Facilities tab is ordered by Unit Number as well.
Finally, [when user Restores a resource]{.underline}, Districts and
Divisions will be available to choose from.

3\. Relocate \"Activate\" functionality to facility information screen.
Activate will now occur after user updates an un-inspected resource and
clicks Save button. When Operational selected, Insert and Update
allowed. When Un-Inspected, only Activate is allowed.

4\. Fix error when transferring a member with a resource. Unique
constraint on the RESOURCES table was preventing users from transferring
a member\'s resources if the RESOURCE_HTN was not renamed. Unique
constraint(s) disabled on RESOURCES table until all business rules have
been established (especially with new, future resource history tracking
enhancements). Coordination with DBA regarding foreign keys also needed.

The screen that allowed changing the RESOURCE_HTN now only confirms the
resource(s) that will transfer with the member. Users can no longer
update RESOURCE_HTN from this screen. New functionality will be added at
a later date to allow users to change a resource's RESOURCE_HTN or UNIT
anytime within the Resource Status, Update screen.

5\. Member History tab, Member Information screen: modified ORDER BY
clause to be STATUS_DATE, DATE_CREATED Fix included in this QCR and
performed by D.Caglarcan 12/24/2003.

6\. Add an alert within Activity Logs if a user attempts to enter an
activity on a newly activated resource and the date of the activity
entered falls within the range when the resource was out of service. An
activity should not be entered between the date range of
assignment_end_date and the status_date of activation within
RESOURCE_HISTORY table.

7\. Crew Assignments: Add PWO (Personal Watercraft Operator) to the lead
and non-lead certification alert. When a user is performing crew
assignments after entering a Boat activity, an alert will display if the
user is not certified accordingly for LEAD or NON-LEAD positions.

8\. Correct tab/enter navigation flow on Unit_Info screen.

9\. Added Filter button to Restore from Archive screen.

**Q2004-007-AD (version 1.3.82; rolled 03/02/04)** \-- For the
UNIT_SUMMARY_REPORT, each query was opened and examined. The major
concern was to make sure that the status_change_history table was
involved in many of the queries. The status_change_history table was
incorporated into the get_status function. The layout of the report was
also slightly changed: field names were underlined whose query was based
on the end_date, field names were bolded whose calculation was based on
the start_date and end_date. A key code was added as a text box to
explain these changes.

**Q2004-008-AD (version 1.3.81; rolled 03/02/04)** \-- The methodology
for maintaining the \"Base Enrollment Date\" in AUXDATA has been
modified. First, the nightly process for updating this date has been
modified so that all non-archived members have their Base Enrollment
Date updated each night. This ensures that the data always reflects the
\"latest and greatest\" status for each member. Also, the logic of the
process has been modified slightly to make the date calculation more
accurate. Finally, the AUXDATA application has been modified so that
whenever a new member is inserted, has a status change, or is restored
from archive, the Base Enrollment Date is updated in the database. If,
for some reason, a person has a NULL Base Enrollment Date (for example,
an insert or update was performed at the database level, not through the
application), the AUXDATA application will calculate the Base Enrollment
Date \"on the fly\" so that the user will see it immediately. (Of
course, if any member has a null Base Enrollment Date in the database,
that situation will be remedied by the next nightly job). Together,
these changes will significantly improve the timeliness and accuracy of
the Base Enrollment Date information in AUXDATA.

**Q2003-082-AD (version 1.3.80; rolled 03/02/04)** \-- AUXDATA shall add
a zipcode field to the end of the UNITS table which will hold the values
for an individual units zipcode area. (Schema change). The DBA SOP will
be updated to reflect this new change. Also, modify the AUXMIS.fmb
form - provide an area on the form to allow users the ability to enter
in the zipcode area for their particular unit. Report updates were also
made to include the zipcode field on several reports.

**Q2003-080-AD (version 1.3.79; rolled 03/02/04)** \-- The Activity By
State report has been modified significantly so that it will run very
quickly (less than 1 minute). Although the report appears the same as
the previous version, it is very fast because the large-scale number
crunching has been moved to a behind-the-scenes process that runs
monthly. The report has a \"current as of\" indicator on the bottom of
each page, indicating at what time the numbers were crunched. The report
is run monthly for \"year to date\" totals, and the available reports
are displayed in a drop-down list to the user, so he can select which
report he wishes to view. The report is run during off hours on the 15th
of each month. Also, the calculations used for certain fields in the
report have been modified to ensure total accuracy; specifically the
\"Auxiliarists In State\" field, and the \"1-Lesson Enrollees\",
\"Multi-Lesson Enrollees\", and \"State Course Enrollees\" fields under
the \"Public Education Enrollees\" heading.

**Q2004-005-AD (version 1.3.78; rolled 02/09/04)** \-- AUXDATA shall add
50 more Citrix MetaFrame XPe 1.0 connection licenses.

**Q2004-002-AD (version 1.3.77; rolled 02/09/04)** \-- System script
that runs once each week to rotate the web service log files. These
files can grow very large if not managed.

**Q2003-085-AD (version 1.3.76; rolled 02/09/04)** \-- This install will
create database side triggers that will capture the CREATED_BY,
DATE_CREATED, MODIFIED_BY, and DATE_MODIFIED information for all tables
where applicable. The emails address will also be converted to lower
case characters for any email address fields within the database.

**E2004-001-AD (affected baseline 1.3.74, rolled 01/21/04)** -- AUXDATA
shall update the Billing Report and correct the problem where
STATUS_CHANGE_HISTORY records from 31 December are not properly counted
in the report. The report should be passed a date of \< 1 Janary of the
following year.

**Q2004-002-AD** **(version 1.3.75; rolled 01/14/04)** \-- AUXDATA shall
update the POMS CRON so that it confirms successful transmission/ftp of
the AUXDATA file to FINCEN.

**Q2003-086-AD** **(version 1.3.74; rolled 12/23/03)** \-- AUXDATA shall
modify the VE/MDV performance award report to accept the pwhere
parameter and limit the number of records returned based on the user\'s
logon.

**Q2003-073-AD (version 1.3.73; rolled 12/23/03)** \-- AUXDATA shall
maintain the Latitude and longitude of all AUXDATA resources (flotillas,
radios, boats, aircraft). This location will be the meeting location,
base, marina, or airfield where the resource normally resides (home of
record).

**Q2003-084-AD** **(version 1.3.72; rolled 12/11/03) --** AUXDATA shall:

1\. Correct the save button in the Activity Log Details screen. AUXDATA
shall force the form to save. Previously, users could make changes
within the Details screen, and then select another tab and exit the
screen without saving.

2\. Update the Activity Log report, showing duration only \'A\' u/w time
for boats, radios and aircraft.

3\. Fix Save button on Crew Assignments to allow for Position updates.
Save button will be enabled only when crewmember and position are not
null. Code modification necessary to allow for changes/updates to any of
the existing crewmember/position entries, no matter what order user
changes/updates.

4\. Removed Sub-Mission requirement when an EOM entered manually.
Sub-Mission is a required field for all other activities.

5\. Sub-Mission LOV not working for a Boat IP / B0 / SAR or Air OD / B0
/ SAR activity entry. This was a result of Q2003-050-AD changes. The LOV
code was modified to allow B\* type status so sub-mission LOV works now.

6\. On Activity Log report, change title Resource Hull Tail Number to
just Resource. If the Resource is a Unit activity, display the Unit
number. If activity is Air, Boat or Radio, display the actual hull tail
number.

**Q2003-068-AD** **(version 1.3.71; rolled 12/15/03) --** AUXDATA shall
change all references to "Inspected" resources back to the former term
of "Operational."

**Q2003-050-AD** **(version 1.3.70; rolled 12/08/03) --** AUXDATA shall:

1\. Require EOM (end of mission) for Air OD / B\* / STBY / SAR / STANDBY
and Boat IP / B\* / STBY / SAR / STANDBY activities.

2\. For UPE / A / EOC activities, require mission and sub-mission. For
UPE and EOC, auto-pop mission with UPE and alert user when sub-mission
not entered.

3\. XOPFAC entries will default to status C.

4\. For all A and B status activities (except EOC sub-activities),
require Crew Assignments when a new activity log is entered. When a user
updates an existing log, details or crew assignments, do not require
crew assignments again if they already exist, and disable/enable other
tabs and Save button accordingly. At EOM screen, added Crew Assignments
to the message \"Remember to enter Activity Log Details and Crew
Assignments for the Activity you just entered. From Activity Log Details
tabs, user will be automatically taken to Crew Assignments tab. Save
button and other tabs are disabled until user enters at least one
crewmember and position. Position is required now. User may select
Position by just typing L, N or T to auto-fill field. User may use
tab/enter to navigate down Position fields after selecting crewmembers.

5\. Crew Assignment: Positions entered are verified for certifications.
User will receive a warning \"Member not certified.\" under the
following conditions:

a\. Boat activity, member selected as Lead is not Coxswain certified.

b\. Boat activity, member selected as Non-Lead is not Coxswain or Crew
certified.

c\. Air activity, member selected as Lead is not certified as one of the
pilot certifications: AIRAC, AIRCP, AIRFP.

d\. Air activity, member selected as Non-Lead is not certified as one of
the air competencies: AIRAC, AIRCP, AIRFP, AIRCREW or AIROBS.

e\. For UPE or MT activities, member selected as Lead is not certified
as an IT (instructor).

f\. For a VSC activity, member selected as Lead is not certified as a VE
(vessel examiner).

g\. For a UMDV activity, member selected as Lead is not certified as a
MDV (marine dealer visitor).

6\. For all Unit activities, on the Activity Logs Details tab, Mission
Details section, disable the Assist, Patrol Status and Water fields.

7\. Eliminate the alert when going to Crew Assignment tab for an
approved activity.

8\. Activity Log data entry cleanup: provide automatic population of
fields based on previous entry, single-character auto-fill, automatic
cursor movement to next field, etc. For example, Boat U/W activities are
always status A and OPS sub-activity, so auto-pop the status and
sub-activity fields and move cursor to mission field, user can enter U
and U/W will auto-fill the rest. Other conditions include, but are not
limited to:

a\. UPE activity is always UPE mission.

b\. UMDV activity is always UMDV mission.

c\. Radio, TRANS activity is always status A and RADOP sub-activity.

d\. AIR, ABN activity is always status A and OPS sub-activity.

e\. UMS activity is always status A and UADMS sub-activity.

f\. UPA activity is always UPA sub-activity and UPA mission.

9\. When an invalid date is entered on Activity Logs tab, return cursor
to field so user can change, don\'t delete what was initially entered.

10\. Approvals: Change \"Up\" and \"Down\" text to /\\ and V arrows. Add
\"Approve all\" double-arrow up /\\ /\\ button. Duration is checked, and
last activity is not available for approval.

**Q2003-081-AD** **(version 1.3.69; rolled 12/01/03) --** AUXDATA shall
update the POMS feed and provide the cell phone number before the home
number.

**Q2003-083-AD** **(version 1.3.68; rolled 11/17/03) --** Corrects
problems as a result of rolling QCR Q2003-055-AD. Previously, CREWAUG
hours were included in all Underway total hour calculations for
Coxswain, Boat Crew Crewmember and Personal Watercraft Operator.
Requirement was clarified after roll to Prod that CREWAUG should only be
included in total hours for Boat Crew Crewmember competency. Also,
previous roll did not include addition of Currency of Competency for
some reason.

**Q2003-064-AD** **(version 1.3.67; rolled 11/17/03) --** The Officer
Directory Report is a new report than users can run to view all the
officer and staff information at their own unit and down the chain of
command. This information includes a mailing address, various phone
numbers, an email address, and the regular officer meeting location,
date and time.

**Q2003-079-AD** **(version 1.3.66; rolled 11/12/03) --** The
permissions have been modified for the \"Member Info\" portion of the
application. The ordinary AUXDATA user (non-SOIS, non-DIRAUX) will now
have the ability to update the majority of his/her personal information
in the application. In addition, the SOIS-level and DIRAUX-level users
will now be able to update the Birthdate field for records under their
purview.

**Q2003-052-AD** **(version 1.3.65; rolled 11/12/03) --** Mission Detail
report shall show total \"Exams Given\" and/or \"Visits\" (as displayed
on Activity Logs Detail screen. These totals shall only be displayed for
employment categories/sub-missions PB, FACI and UMDV.

**Q2003-055-AD** **(version 1.3.64; rolled 11/12/03) --** On the
Individual Training Status, Unit Training Status, Crew Underway Time,
and Underway Crew reports, total hours reported for U/W Hours now
includes unit/activity UCG/A/UCGOPS/CGOPS/CREWAUG hours. Additionally,
the Individual Training Status and Unit Training Status reports now show
currency of the competency.

**Q2003-049-AD** **(version 1.3.63; rolled 11/12/03) --** This QCR was
to modify the Activity Log Details screen as follows: \"Currently on the
Activity Log Details screen, the \"Number in Attendance\" window under
PA Activities is open for all 11 UPA Activities. This window should only
be opened for the following three UPA Activities:

UPA / A / UPA / UPA / LECTURES

UPA / A / UPA / UPA / BOATINGSFTY

UPA / A / UPA / UPA / PUBLIC

The \"Number in Attendance\" should be closed for all other UPA
activities.\"

AUXDATA shall Change the label on the Activity Log Details screen from
"Number of

Visits / Inspections" to "Number of Visits."

QCR was amended by Bob Platt on Nov. 3, 2003:

The 'Comments' window should be opened for all UPA Activities. 

Also, all windows in the \"General Unit Activity Details\" section
should be closed except for the

\"Length of Time\" window .

**Q2003-057-AD** **(version 1.3.62; rolled 11/12/03) --** This QCR added
a Menu button to the Awards Assignments Screen.

**Q2003-078-AD** **(version 1.3.61; rolled 10/20/03) --** This QCR was a
result of earlier changes to the Member Roster report (Q2003-069-AD).
Previously, the comma was removed to accomodate a cleaner output to the
CSV format and the \"generate a delimited file from CITRIX\" for
MailMerge, etc. Users want the comma within the PDF format, so it was
added back. Additionally, AUXDATA users should be aware there will be an
extra column between last name and first name in the CSV and generate to
delimited file formats. This should not interfere with the Mail Merge
utility.

**Q2003-074-AD** **(version 1.3.60; rolled; rolled 10/29/03) --** Prior
to this QCR, there were 56 records in the CLAIMANTS table. The table was
populated with new data from the UNITS table in AOPS, so it now contains
approx. 300 national, district and flotilla level claimant records. The
naming convention of the claimant_id/OPFACs is 00-00000 now. In
addition, on the Activity Logs tab, the OPFAC column LOV (list of
values) is now limited based on your user logon level.

**Q2003-063-AD** **(version 1.3.59; rolled 10/20/03) --** Duration of
the activity was added to the Activity Logs Report.

**Q2003-077-AD** **(version 1.3.58; rolled 10/20/03) --** The Member
History tab screen has been modified so that it no longer includes
Transfer and Save buttons. (To Transfer and Save, users must use the
buttons on the Member General Info tab screen).

**Q2003-076-AD** **(version 1.3.57; rolled 10/07/03) --** Bug fix for
Q2003-069-AD.

**Q2003-071-AD** **(version 1.3.56; rolled 10/06/03 -** Updates the
Patriot Readiness section of the form to allow the users to create
records that don\'t have any days of availability selected. Updates the
Patriot Readiness section in the reports to allow the user to put a wild
card (%) in both the State/Unit and Competency fields so that all
Patriot Readiness records can be easily viewed. Also modifies the
reports section so a PDF file can be run as well as a CSV (Excel
document).

**Q2003-069-AD** **(version 1.3.55; rolled 10/06/03) -** The Member
Status section of the application has been modified. When you select a
Member in order to view his/her information on screen, you now have a
Member General Information tab and a Member History tab. The Member
History tab includes information not previously displayed in the
application, including the member\'s Current Enrollment Date, Base
Enrollment Date, Flotilla Date, and a history of the member\'s status
within the Coast Guard Auxiliary. In addition, the Member Roster report
now displays a member's base enrollment date.

**Q2003-051-AD** **(version 1.3.54; rolled 10/06/03) -** Under Admin
Reports, the Operations Awards, Public Education Awards and Vessel
Examinations/Marine Dealer Visits Awards reports no longer count trainee
time in performance awards. Additionally, the VEMDV report counts only
lead time. All reports, including the Sustained Service Awards report,
may now be displayed in CSV format with header info.

**Q2003-048-AD** **(version 1.3.53; rolled 9/10/03)** -- The Patriot
Readiness Quick Change Request:

> • Enables Coast Guard Auxiliary members to indicate their availability
> to volunteer by day of the week and time of day
>
> • Asks their willingness to travel
>
> • Asks their physical fitness
>
> • Asks status of security clearances
>
> • Asks if they have a valid Auxiliary ID card
>
> • And asks if they are willing to do administrative work

The Patriot Readiness report, available to District and National users,
allows the DIRAUX and National staff to search on the fields above,
state, flotilla number, and competency/skill. The report, available in
EXCEL spreadsheet format, lists contact information for the Auxiliary
members meeting the specified criteria and all of their
competencies/skills.

In addition, other competencies can easily be added. For example,
several DIRAUX staff asked about adding CPR and FIRST AID to the list of
competencies and skills stored in the dB. Adding those competencies
could be done quickly and easily and then tracked nationally.

**Q2003-042-AD** **(version 1.3.52; rolled 8/06/03)** -- System script
that runs nightly to retire facilities (AIR, BOAT, RADIO) that are 45
days past the inspection due date.

**Q2003-047-AD** **(version 1.3.51; rolled 7/16/03)** -- Delete records
from owners, patriot, person_phone, and su_user tables when a person has
a status change of death.

**Q2003-041-AD (version 1.3.50; rolled 7/16/03)** -- No longer count
Trainee time in computing time for the Operations Performance Awards
report or the VE/MDV Performance Awards report. In addition, for the
VE/MDV award, only count Lead time.

**Q2003-045-AD (version 1.3.49; rolled 7/16/03)** -- Throughout AUXDATA,
changed the phrase Non-Operational to read Un-inspected. Also changed
Operational to read Inspected throughout. On the activate facility
screen, changed New Start Date to read New Resource Start Date. Also
made sure that all lists of values were properly ordered throughout
AUXDATA.

**Q2003-044-AD (version 1.3.48; rolled 7/16/03)** -- Corrected the bug
which did not allow the creation of new facilities.

**Q2003-046-AD (version 1.3.47; rolled 6/23/03)** -- Implements
operating-system level scripts to ensure that users whose sessions have
timed out (i.e., exceeded the recently instituted 30-minutes idle time
limit) will be able to readily establish a new connection to the
application.

**Pick List Changes (rolled 6/23/03) -** Open the AUXMP
Safety/Chart/Regatta mission options for Radio facilities; inactivate
the AUXMP Radio mission option; and, add "20A" to the description field
of the AUXMP Safety mission

**Q2003-040-AD (version 1.3.46; rolled 6/23/03)** -- Includes three
enhancements: 1) disallows activation of a non-operational facility if
the facility\'s inspection date is out of compliance; 2) disallows entry
of a resource end date less than the assignment start date, resource
start date, or highest resource log start date for that facility; and 3)
displays the filter button only where needed.

**Q2003-043-AD(version 1.3.45; rolled 6/16/03)** -- Fixes a bug in both
the individual and unit Training Status reports. Some users were getting
error messages when running the reports for Auxiliary members with the
instructor competency. This release corrects a function that returned
information to the report, and in some cases caused an error message,
regarding instructor teaching activity.

**Q2003-039-AD(version 1.3.44; rolled 6/11/03)** -- Limits users to two
concurrent sessions and terminates any session after 30 minutes of idle
time. These limits minimize lock-induced waits, thereby enhancing
application availability. Logging on to the application counts as one
session; generating a report therein or logging on again counts as
another. Users who attempt to create yet another session will receive an
"invalid username/password." Please do [not]{.underline} contact Auxdata
user administration to have the logon reset, because it is, in fact,
still valid. This is simply the internally-generated error message
provided when someone attempts to violate this resource limit.

**Q2003-038-AD(version 1.3.43; rolled 6/02/03)** -- When modifying a
resource that has a resource end date, you are no longer asked if you
want to make the resource inoperable or archived. That choice only
appears if you have just entered the resource end date.

**Q2003-037-AD(version 1.3.42; rolled 6/02/03)** -- Enables generation
of the Resources by Flotilla report at the flotilla level.

**Q2003-034-AD(version 1.3.41; rolled 6/02/03)** -- Enables the display
of various owner types. However, when multiple owners exist, only one
owner is still listed, along with his/her owner type, according to the
following order of precedence: Primary (P), Co-Owner (C), Corporate (G),
and Unit (U).

**Q2003-036-AD(version 1.3.40; rolled 5/22/03)** -- Do not allow
duplicate Registration Numbers to be entered for resources.

**Q2003-029-AD(version 1.3.39; rolled 5/13/03)** -- Apply database
security patches to AUXDATA, Oracle 8.1.7.4 patchset.

**Q2003-031-AD(version 1.3.38; rolled 5/20/03)** -- Added filter
capability in many places throughout te entire AUXDATA application..

**Q2003-021-AD(version 1.3.37; rolled 5/05/03)** -- "Auto Notification
-- Inspections", create a program that will automatically notify CG
Auxillary members, via e-mail, when their facilities (boats, aircraft,
and radios) are due for inspection. Also send e-mail copy to Flotilla
Commander and SO_OP.

**Q2003-033-AD(version 1.3.36; rolled 4/30/03)** -- Only allow 1 Primary
owner for each facility, and do not allow a person to be entered as any
kind of owner more than once for each facility.

**Q2003-032-AD(version 1.3.35; rolled 4/30/03)** -- Display a more user
friendly message when a person changes the Zulu Offset on the Activity
Logs screen that makes the Start Date a duplicate of another Activity
Log.

**Q2003-030-AD(version 1.3.34; rolled 4/30/03)** -- Made the Resources
By Flotilla report more useful by adding a filter that allows the
selection of Operational, Non-Operational, or All facilities. Only
display a facility 1 time on the Resources By Flotilla report. Added
additional columns to the Resources By Flotilla report to display the
primary owner\'s name and member ID, the location of the facility, and
if the resource is a boat, whether or not it is trailered.

**Q2003-028-AD (version 1.3.33; rolled 4/22/03)** -- Changed the copy
trainees button to read "copy participants," added the copy participants
button on the activity log screen task capture tab, and added mouse-over
hint text to that button in both places.

**Q2003-027-AD (version 1.3.32; rolled 4/14/03)** -- Does not allow
update of a person's information on the Certifications screen or on the
Assign Competencies to People screen.

**Q2003-026-AD (version 1.3.30; rolled 4/14/03)** -- When inserting new
persons, Unit picklist now only shows active units.

**Q2003-025-AD (version 1.3.31; rolled 4/14/03)** -- Correctly displays
the length of a boat in feet and inches on the Resources by Flotilla
report.

**Q2003-022-AD (version 1.3.27; rolled 4/14/03)** -- Made instructor and
duration optional. Provided the capability to copy trainees from the
task block on the trainees tab to lower tasks. Improved the cursor
positioning when using the populate buttons to reduce user key strokes.

**Q2003-024-AD (version 1.3.29; rolled 4/9/03)** -- Update the two
training status reports. Inactive people will not show on these reports.
Likewise, unassigned or inactive competencies will not display. When a
certification is removed (the competency is set to REYR, REWK, or
ADMIN), then the competency and required tasks will continue to show. To
completely remove the competency, please go to the competency assignment
area, and unassign the competency.

**Q2003-020-AD (version 1.3.26; rolled 3/24/03)** -- Allows select on
active, inactive or all members for the Address and Flotilla Data and
Member Roster reports.

**Q2003-019-AD (version 1.3.25; rolled 3/18/03)** -- Add REYR_DATE
(DATE) to the PERSONS_COMPS Table.

**Q2003-018-AD (version 1.3.24; rolled 3/10/03)** -- Correction to the
Mission Details report.

**Q2003-016-AD (version 1.3.23; rolled 3/10/03)** -- Writes a record to
the Status Change History table when someone is restored from archive
and updates the Persons table status field accordingly. Provides users
with the ability to enter the restoration date or enters a default value
equal to the database server system date. Also, repairs a problem in
Activity Logs Details (i.e., where the Public Affairs section is open
for UMDV sub-activity code).

### Q2003-014-AD (version 1.3.21; rolled 3/3/03) -- 

1\) In the Activity Log Details screen, for the following
Activity/Status/Sub Activity/Mission, in the General Unit Activity
Details section, grayed out all but the indicated windows:

Activity/Status/Sub Activity/Mission Open Windows

UCG / A / UCGADM / (all) Length of Time

UCG / A / UCGOPS / (all ) Length of Time

UMS / A / UADMS / UMDV Number Visits/Inspect & Length of Time

UMS / A UADMS / MT Length of Time

UMS / A / UADMS / AUXADMN Length of Time

UMS / A / UADMS / VSC Nr Given, Nr Passed & Length of Time

2\) In the Activity Log Details screen, the ATON Section windows opens
only with a Unit - UCG / A / UCGOPS / ATON activity.

**Q2003-017-AD (version 1.3.22; rolled 2/26/03) --** Bug Fix: Unit
Summary - Prior Unit -- PWhere. Capture transfer of personnel in the
Status Change History table, insure that Unit Summary Report reflects
transfers.

**Q2003-015-AD (version 1.3.20; rolled 2/24/03) --** Unit Summary Report
Update.

**Q2003-013-AD (version 1.3.19; rolled 2/24/03) --** Change the pick
lists in the Activity Logs Module for Unit Resource, so that for the
Activity/Status/Sub Activity listed below, only the indicated Missions
are listed.

Activity/Status/Sub Activity Missions

UMS / A / UADMS UMDV, MT, AUXADMN, VSC

UCG / A / UCGOPS CGOPS, ELT, ATON, MEP, CVS, MS

UCG / A / UCGADMS CGADM, UREC, GOVSUP, LO, IA, OR

**Q2003-010-AD (version 1.3.18; rolled 2/24/03) --** Insert a record to
the status change history table upon the transfer of a member from one
flotilla to another. Add a column to the status_change_history table for
PRIOR_UNIT NUMBER(38).

**Q2003-011-AD (version 1.3.17; rolled 2/24/03) --** Report is not
showing any member that has not previously been awarded the SSA award.
Show all members that should receive the award.

**Q2003-009-AD (version 1.3.16; rolled 2/24/03) --** Combining the
Sailing List Detail and Underway Activity and Crew Reports.

**Q2003-008-AD (version 1.3.15; rolled 1/28/03) --** Bug Fix.

**Q2003-005-AD (version 1.3.14; rolled 1/28/03) --** Displays system
name and version number at the top of the application main menu.

**Q2003-007-AD (version 1.3.13; rolled 1/27/03) --** Updated the
Training Status reports. They now display the number of hours or
activities conducted during the period passed as the "end date" for the
report. (i.e. If December 31, 2002 is the end date and the requirement
is annual (e.g. stern tow for boat coxswain) then the report will show
the number of stern tows completed during 2002, whether or not the
member is current. Previously, the report showed the number completed
during 2001, if the member was not current in 2002, but current in 2001.

**Q2003-004-AD (version 1.3.12; rolled 1/24/03) -** For Auxdata reports,
set January 1 of the current year as the default begin date and sysdate
as the default end date.

**Q2003-003-AD (version 1.3.11; rolled 1/24/03) -** Per the Auxiliary
Manual, modify the script producing the Sustained Service report so that
an individual appears once for every 750 hours served up to 7,500 hours
total, then again only when he/she has served 15,000 hours, and finally,
upon 25,000 hours service. Apart from these 12 instances, an
auxliliarist should not appear on this report, regardless of hours
served.

**Q2003-006-AD (version 1.3.10; rolled 1/22/03) --** This change
provided further color highlighting of tasks displayed in the individual
and Unit Training Status Report.

**Q2003-001-AD (version 1.3.9; rolled 1/16/03)** -- Different Auxiliary
tasks have different currency requirements. While some are due one, two
or five years from the **date** last completed (i.e., "sliding-scale"
currency), others only become due as of December 31^st^. at one, two or
five years from the **year** last completed (i.e., "calendar-year"
currency). This QCR implements these two bases for determining currency
based on each task's specific requirements.

**Q2003-002-AD (version 1.3.8, rolled 1/10/03) --** Eliminates the
possibility of orphan records in the Trainees table when a Task Capture
record is deleted.

**Q2002-021-AD** **(version 1.3.7, rolled 1/6/03)** -- Provided further
enhancements to the Quarterly and Annual Billing reports.

**Q2002-22-AD (version 1.3.6, rolled 1/6/03) --**

1\) Developed the Operations Performance Awards Report. (COMDTNOTE 16790
codifies new and revised award criteria to better align the Auxiliary
with the Coast Guard.)

2\) Modified the PE and VE/MDV Performance Awards Reports so that each
orders entries by last name and then first name.

3\) Modified the PE Performance Award Report to include member training
(MT).

**Q2002-019-AD** **(version 1.3.5, 12/17/02)** -- Enabled remote
generation of the Quarterly and Annual Billing reports.

**Q2002-020-AD** **(version 1.3.4, rolled 12/4/02)** --

1\) Modified the Training Status package to include hours served while
on a CGOPS mission, QE_UW_CH sub-mission in the lead position. Hours
served in this capacity will be summed with coxswain lead hours to
provide the hours underway value in the Training Status Report.

2\) Modified the Training Status package to calculate currency on a
literal calendar-year basis for boat crew coxswain, boat crew crewmember
and personal watercraft operator. For example, if it\'s an annual
requirement, and the requirement was satisfied on 6/1/2002, he/she would
remain current until 12/31/03, not 6/1/03.

**Q2002-018-AD** **(version 1.3.3, rolled 12/3/02)** -- Modified the
Public Education and Vessel Exam/Marine Dealer Visit Performance Award
reports to enable district-level report generation in addition to
national-level report generation.

3\) Modify Individual & Unit Tranining Status reports so that the cycle
value is changed from \"A\" for annual to \"CY\" for current year for
boat crew coxswain, boat crew member and personal watercraft operator.

**Q2002-017-AD** **(version 1.3.2, rolled 11/26/02)** - Corrected the
function that returns how many lead and non-lead assignments an
instructor has (F_IT).

**Q2002-016-AD (version 1.3.1, rolled 11/26/02)** -- created the Vessel
Examinations/Marine Dealer Visitations Service Award report. This report
lists all Auxiliarists who have earned 60 credits as described in
COMDTINST 16790 dated July 11, 2000.

**Q2002-015-AD (version 1.2.4, rolled 11/21/02)** - fixed the problem
with deleting an updated activity log and added filtering capability in:

Activity Log -Crew Assignment

Training - Assignments - Assign Competencies to Individuals

Training - Certifications - Assignments

Training - Task Capture -- Trainees

## 

## **[AUXDATA Version 1.3 (released 11/4/2002)]{.underline}**

On November 2, 2002, Auxdata Version 1.2 rolled to production. This
release includes many user-requested enhancements. We hope you find
these new capabilities and modifications to existing features both
user-friendly and timesaving. New features available in Auxdata Version
1.2 include:

# Entire Application

-   Where text was cut off and not entirely visible, screens have been
    modified to allow scrolling or the field is made visible on another
    part of the form. This was especially noticeable where task and
    competency descriptions were displayed.

-   Capability to sort on most column headings. Column headings have
    button appearance. When a button is clicked, records are sorted by
    that column. The button text remains in bold to indicate the sort
    order.

-   Date entry: No births or deaths in the future allowed

```{=html}
<!-- -->
```
-   Date entry: Warning for all dates in the future

# Address and Flotilla Report

-   All phone numbers captured in Auxdata now appear along with legend
    elements indicating phone type

-   Capability to order by name or flotilla and name

# User Account Management Form

-   Added unit number and search capability on this field

# Activity Logs Module

-   Added help text for End of Course record entry

-   Option to enter an End of Mission entry is given after insertion of
    boat, aircraft or radio activity log

-   Ability to recall up to six months of approved activity logs

-   Can query on name in Crew Assignments

-   Users no longer jumped to the Resources tab after deleting the only
    visible activity log

-   For unit activities, only workshops appear; other unit activities
    are captured in the TMT module

-   To minimize data entry, added field populate capability to task
    capture areas of both TMT and Activity Logs

-   When selecting training tasks completed during an activity, users
    may click on the task line or the include box. Either method will
    produce the desired results (i.e., entering a check in the include
    box).

-   Made the following activity log picklist adjustments concurrent with
    changes to the Unit Summary report (outlined below in the Reports
    section)

```{=html}
<!-- -->
```
-   To the Sub-mission pick list for the UPE Mission, changed:

> B6L -- Boating Skills and Seamanship to read (6L Core)
>
> B8L -- Boating Skills and Seamanship to read (8L Core).
>
> B7L -- Boating Skills and Seamanship (7 to 9-L Core) to read
>
> B9L -- Boating Skills and Seamanship (9 Lessons)

# Member Status

-   Provided ability to archive members no longer serving. Automatic
    archiving of persons occurs on change of status to death, member
    requested, or administrative. Any solely-owned resources are
    archived and officer assignments removed.

-   Provided ability to transfer resources when transferring a member.

-   Provided ability to restore from archive.

-   When creating new members, system checks for duplicates using last
    name and birth date fields.

# 

# Resource Status

-   Resources can be archived (e.g., when sold or permanently removed
    from service)

-   Resources can be restored from archive.

-   Can capture HP for boats in the same manner as aircraft.

-   When creating new resources, system checks for duplicates
    (registration number for boats, radios, and aircraft)

# Training

-   Changed text colors to match those in the Activity Log module of the
    application, (i.e., black text on white background).

# Training Certifications

-   Changed TRN certification description to ADMIN.

# Reports

Reminder: WHEN RUNNING REPORTS VIA CITRIX (THE INTERNET LINK FROM
[HTTP://AUXDATA.USCG.GOV](http://AUXDATA.USCG.GOV/)) REMEMBER THAT THE
REPORT MUST BE RUN IN PDF FORMAT. AFTER THE REPORT DISPLAYS, SELECT
GENERATE TO FILE AND SELECT "," FROM THE DROP DOWN LIST TO CREATE A
COMMA-SEPERATED-VALUE (CSV) FILE.

Unit Summary

-   Changed label "Courtesy Examiners" to "Vessel Examiners\'."

-   Value for Vessel Examiners corresponds to number of members
    certified in VE competency.

-   Value for Marine Dealer Visitors corresponds to number of members
    certified in MDV competency.

-   Value for Fishing Vessel Examiner corresponds to number of members
    certified in CFV competency.

-   Value for VSCs given limited to Exams Given field when a VSC Private
    Boat activity log is entered

-   Value for VSCs passed limited to Exams Passed field when a VSC
    Private Boat activity log entered.

-   Value for MD Visits corresponds to Number of Visits/Inspections
    field when UMDV mission, UMDV sub-mission activity log entered.

-   Value for Fishing Vessel Exams limited to Number of
    Visits/Inspections field when VSC mission, CFV sub-mission activity
    log entered.

-   \[Note: OCX acknowledges not all VSC categories will be displayed on
    report with the restrictions outlined above\]

```{=html}
<!-- -->
```
-   In the Public Education area, changed course titles as follows:

> Change \"BS&S (6L)\" to read \"BS&S (8L)\"
>
> Change \"BS&S (7 to 9L)\" to read \"BS&S (9L)\"

Training Status

-   The Unit Training Status report now displays the same information as
    the Individual Training Status report. It shows hours and training
    tasks required to maintain currency.

Member Roster

-   Created CSV report that includes offices, addresses, phone numbers,
    and competencies.

Address and Flotilla

-   Users can sort on last name or flotilla, then name.

# SO-IS Role

-   Changed all current Users with Division and D17 Flotilla level &
    Admin access to new SO-IS role.

-   SO-IS role capabilities:

> Activity Log Module --
>
> full access and functionality (insert activity logs, activity log
> details, crew assignment, task, task capture, and approval)
>
> Member Status Module -
>
> Update member records
>
> No access to transfer member function
>
> No access to change status function
>
> No access/visibility of Awards tab
>
> Resource Status Module -
>
> Units Tab -
>
> No access to probation date and disestablish date
>
> No access/visibility of Facilities tab
>
> Update Officer
>
> Reports
>
> Full report access
>
> TMT

Report button access/visibility