DARMIS Final Database Schema (A017) by g4509244

VIEWS: 7 PAGES: 47

									Proposal for




Department of Defense
Defense Acquisition Regulations
Management Information System (DARMIS)
Modernization




                                        November 14, 2008


                         DARMIS Final Database
                       Schema Document (A017)

                                                       Submitted to:


                           Acting Deputy Director for
                     Defense Acquisition Regulations
                                             System
                          DARS
                       Management
                       Information
                         System
                        (DARMIS)


                               5180 Parkstone Drive, Suite 100
                               Chantilly, VA 20151 703.667.9420
                                                            Final Database Schema (FDS) (A017)




                    Modernization
               Database Schema (A017)



                              Contract # FA7012-05-C-A002
                               Date: November 14, 2008
                                     Version: 3.0
                                   Revision: R02C00




Contract # FA7012-05-C-A002                ii                              November 14, 2008
                                                             Final Database Schema (FDS) (A017)



                                   Revision History

Revision                Date          Description
Number

R00C00                  Feb 2008      DARMIS Database Schema (A002) Draft

R01C00                  Apr 2008      Reviewed and Updated

R02C00                  Nov 2008      Updated




Contract # FA7012-05-C-A002                 iii                              November 14, 2008
                                                                                                               Final Database Schema (FDS) (A017)



                                                     Table of Contents

INTRODUCTION ......................................................................................................................................... 6
PRESENT FOXPRO DATABASE TABLE NAMES AND FIELD NAMES ......................................... 6
DATABASE ER DIAGRAM ......................................................................................................................14
TABLE AND DATA DESCRIPTIONS .....................................................................................................15
   AGENCY ..................................................................................................................................................15
   AGENDA ..................................................................................................................................................15
   AGENDA_CODE ....................................................................................................................................15
   CASE_ORIGIN .......................................................................................................................................15
   CASE_SPONSOR..................................................................................................................................16
   CASE_STAGE ........................................................................................................................................16
   CASE_STATE.........................................................................................................................................16
   CASE_STATE_TEMPLATE .................................................................................................................17
   CASE_STATE_TEMPLATE_ENTRY ..................................................................................................17
   CASES .....................................................................................................................................................18
   COMMITTEE...........................................................................................................................................19
   COMMITTEE_HOLDINGFILES ...........................................................................................................20
   COUNCILAGENDA_RPT ......................................................................................................................20
   DFARSCLOSEDCASES .......................................................................................................................21
   FARCLOSEDCASES .............................................................................................................................21
   ORGANIZATION ....................................................................................................................................21
   ORGANIZATION_PERSONNEL..........................................................................................................22
   PAPERWORK_BURDEN_HISTORY ..................................................................................................22
   PART ........................................................................................................................................................22
   PERSONNEL ..........................................................................................................................................23
   REGULATION_TYPE ............................................................................................................................23
   REPORTS_LINKS..................................................................................................................................23
   USERS .....................................................................................................................................................24
GAP ANALYSIS .........................................................................................................................................24
LEGACY TABLES .....................................................................................................................................24
   AGENDA_RAW ......................................................................................................................................25
   ARCH_AGENDA ....................................................................................................................................26
   ARCH_CASE ..........................................................................................................................................26
   ARCHCASE_OLD_RAW.......................................................................................................................28
   ARCH_DEV .............................................................................................................................................30
   ARCH_FOIA ............................................................................................................................................30
   ARCH_HOLD ..........................................................................................................................................31
   ARCH_PART ..........................................................................................................................................33
   ARCH_CASE_RAW ...............................................................................................................................33
   ARCHDEVIATE_RAW ...........................................................................................................................35
   CFOIA_RAW ...........................................................................................................................................35
   DEVIATE_RAW ......................................................................................................................................36
   FOIA_RAW ..............................................................................................................................................37
   HARCHCASE_RAW ..............................................................................................................................38
   HOPENCASE_RAW ..............................................................................................................................39
   OPENCASE_RAW .................................................................................................................................42
   OPENCASE_RAW_DUMP ...................................................................................................................44
Contract # FA7012-05-C-A002                                                     iv                                                     November 14, 2008
                                                                                                         Final Database Schema (FDS) (A017)


   OPENCASE_RAW_MAJEVENT_SPLIT ............................................................................................45
   PART_NO_RAW ....................................................................................................................................47
CONCLUSION ............................................................................................................................................47




Contract # FA7012-05-C-A002                                                 v                                                  November 14, 2008
                                                              Final Database Schema (FDS) (A017)




Introduction
This Final Database Schema (FDS) document is a baseline for the database
specifications of how the core functionality of the DARMIS application has been
developed. This document is a living document that will be kept current throughout the
course of the project. This document details the technical approach for database use to
ensure the success of the DARMIS Modernization effort. The document and its
descriptions explain table relationships and their data entities.


Present FoxPro database table names and field names
(Table names are in bold, field names are in italics, and columns are in order from left to
right.)


AGENCODE:                       CASEMGR                          CN_INTERDT
CODE                            DARCCM                           CN_FINALDT
                                LASTDATE                         CN_COMMENT
AGEOFOC:                        COMM_NAME
CYEAR                           CASEOPEN                         ARCHDEV:
NUM_FAR                         UPDATED                          CONTROL
NUM_DFAR                        TOTALDAYS                        DARTRACK
                                ONHOLD                           TITLE
ARCHCASE:                       ONHOLDDATE                       REQUESTOR
CONTROL                         CUMONHOLD                        REQDATE
DAR_DAFARNO                     NETDAY                           DDPACT
FAR_NO                          ARCHIVE                          DDPDATE
CAA_NO                          PRA_APPLYS                       EXPDATE
RINNO                           PRA_RQSTDT                       DEVFLAG
TITLE                           PRA_CLERDT                       STATUS
MAJOREVENT                      PRA_EXPRDT                       MAJEVENTS
SYNOP                           RFA_APPLYS
STATUS                          FRA_INITIA                       CALYEAR:
PRI                             RFA_INITDT                       YEAR
MDL                             RFA_FINAL                        OPEN_NUMDF
REQUESTOR                       RFA_FINLDT                       OPEN_NUMFA
REG                             RFA_CERTDT                       OPEN_TOTAL
STATCODE                                                         CLOSED_NUM
                                FEDREG
PDATE                                                            CLOSED_NU2
                                RULE_TYPE

Contract # FA7012-05-C-A002                  6 of 47                         November 14, 2008
                                                   Final Database Schema (FDS) (A017)


CLOSED_TOT                    FOIANO                  PRA_CTRLNO
AVG_NUMDFA                    MANAGER
AVG_NUMFAR                    REQUESTOR               COMMITTEE:
AVG_TOTAL                     COMPANY                 COMM_CODE

                              LOCATION                COMM_NAME
CASE20MB:                     PHONE                   COMM_AGENC
CASENUM                       REQ_DT
OMBNUM                        REC_DT                  CONTROL:
LINKTYPE                                              NUMBER
                              COMPL_DT
                                                      SCRAP
                              TOT_DAYS
CASE_LIST:                                            NAME
                              INF_REQ
CASE_TYPE
                              STATUS
DESC                                                  CONVTAB:
                              REMARKS
                                                      DATABASE
                              JDRSTART
CASE_REF:                                             TAG
                              JDRFINIS
CONTROL                                               EXPRESSION
                              JDRCUMDAYS
CASEREF                                               UNIQUE
                              DATETO02
CASETYPE
                              REFERDATE
                                                      DARCCM:
                              REFERCOMP               DARC_AGNCY
CASE2OMB:
CASENUM                                               AGNCY_NAME
                              CMTEDATE:               DOD
OMBNUM
                              CONTROL
LINKTYPE
                              CMTE_RPT                DATENAME:
                              COMM_NAME               DATENAME
CASEAGND:
CONTROL
                              COLL_TBL:               DEADLINE:
AGNDCODE
                              CONTROL
AGNDDATE                                              CONTROL
                              PRA_CITN
AGNDACTION                                            REQRMENT
                              PRA_HRS
                                                      DEADLINE
                              PRA_CTRLNO
CASEINTR:                                             DATENAME
CONTROL
                              COLLATS:
INTEREST                                              DEV_AGND:
                              CONTROL
                                                      CONTROL
                              PRA_CITN
CFOIA:                                                AGNDCODE
                              PRA_HRS
CONTROL                                               AGNDDATE


Contract # FA7012-05-C-A002              7 of 47                 November 14, 2008
                                                    Final Database Schema (FDS) (A017)


AGNDACTION                    REQDATE                  FOIANO
STATCODE                      DDPACT                   MANAGER
_NULLFLAGS                    DDPDATE                  REQUESTOR
                              EXPDATE                  COMPANY
DEV_INQ:                      DEVFLAG                  LOCATION
CONTROL                       STATUS                   PHONE
INQUIRER
                              MAJEVENTS                REQ_DT
INQDATE
                                                       REC_DT
TOAGNDT
                              EXTERNAL:                COMPL_DT
RECDDT
                              INTEREST                 TOT_DAYS
TOINQDT
                              INT_NAME                 INF_REQ
                                                       STATUS
DEV_PART:
                              FAPPEAL:                 REMARKS
CONTROL
                              CONTROL                  JDRSTART
PART_NO
                              APPEALNO                 JDRFINIS
                              COMP_DT
                                                       JDRCUMDAYS
DEV_REGS:
                              APPL_DT
CONTROL                                                DATETO02
                              REVW_DT
REG_NO                                                 REFERDATE
                              REVW_DT_FR
                                                       REFERCOMP
                              APREVIEW
DEV_REL:                      APACTION
CONTROL                                                FOXUSER:
CASEREF                                                TYPE
                              FEDREG:
CASETYPE                                               ID
                              CONTROL
                              RULE_DATE                NAME
DEV_REVW:                     RULE_CITE                READONLY
CONTROL                       FED_TYPE                 CKVAL
REVIEWER                      PUB_COM                  DATA
TOREVDT                       FACDACTYPE               UPDATED
FMREVDT                       FACDAC
                              FACDACEFCT               FPART_NO:
DEVIATE:                      DPT_LTR                  CONTROL
CONTROL                       DLDATE                   PART_NO
DARTRACK                                               REG_TYPE
TITLE                         FOIA:
REQUESTOR                     CONTROL                  FREF_IN:


Contract # FA7012-05-C-A002               8 of 47                 November 14, 2008
                                                    Final Database Schema (FDS) (A017)


CONTROL                       CONTROL                  CASEOPEN
REF_FROM                      REV_DT_TO                UPDATED
RECD_DT                       REV_DT_FRO               TOTALDAYS
REPLY_DT                      REVIEWER                 ONHOLD
RFACTION                      RVACTION
                                                       ONHOLDDATE
                                                       CUMONHOLD
FREF_OUT:                     GETFILE:
                                                       NETDAY
CONTROL                       FID
                                                       ARCHIVE
REF_TO                        FILE_PATH
                                                       PRA_APPLYS
SENT_DT
                                                       PRA_RQSTDT
REP_RECD                      GOTOSCR:
                                                       PRA_CLERDT
RESULTS                       SYSTEM
                                                       PRA_EXPRDT
                              SYSNAME
                                                       RFA_APPLYS
FRELCITE:                     SCREENSPR
                                                       RFA_INITIA
CONTROL2                      DESCRIPT
CITE_TYPE                                              RFA_INITDT

CITE_NO                                                RFA_FINAL
                              HARCHCAS:
                                                       RFA_FINLDT
                              CONTROL
FRELEASE:                                              RFA_CERTDT
                              HFILE_NUM
CONTROL                                                FEDREG
                              CAA_NO
CASE_NO                                                RULE_TYPE
                              RINNO
REG_SECT                                               CN_INTERDT
                              TITLE
CAS_TYPE                                               CN_FINALDT
                              MAJOREVENT
CA_SCOPE                                               CN_COMMENT
                              SYNOP
REG_TYPE
                              STATUS
OTHERINF                                               HCASE_RE:
CITE_TYPE                     PRI
                                                       CONTROL
CITE_NO                       MDL
                                                       CASEREF
CONTROL2                      REQUESTOR
                                                       CASETYPE
                              REG
FRELREG:                      STATCODE
                                                       HCASEAGN:
CONTROL2                      PDATE
                                                       CONTROL
REG_TYPE                      CASEMGR                  AGNDCODE
REG_SECT                      DARCCM                   AGNDDATE
                              LASTDATE                 AGNDACTION
FREVIEW:                      COMM_NAME


Contract # FA7012-05-C-A002               9 of 47                 November 14, 2008
                                                     Final Database Schema (FDS) (A017)



HCASEINT:                     DLDATE                    SYNOP
CONTROL                                                 STATUS
INTEREST                      HHIST:                    PRI
                              CONTROL                   MDL
HCMTEDAT:                     DAR_DFARNO                REQUESTOR
CONTROL                       FAR_NO                    REG
CMTE_RPT                      CAA_NO                    STATCODE
                              HISTORY                   PDATE
HCOLL_TB:                                               CASEMGR
CONTROL                       HINTERES:                 DARCCM
PRA_CITN                      CONTROL                   LASTDATE
PRA_HRS                       PARTY                     COMM_NAME
PRA_CTRLNO
                                                        CASEOPEN
                              HISTABLE:                 UPDATED
HCOLLATS:                     CONTROL                   TOTALDAYS
CONTROL                       STATCODE
                                                        ONHOLD
PRA_CITN                      NEWCODE
                                                        ONHOLDDATE
PRA_HRS                       PDATE
PRA_CTRLNO                                              CUMONHOLD
                                                        NETDAY
                              HISTLOAD:
HEADLIN:                                                ARCHIVE
                              HDATE
CONTROL                                                 PRA_APPLYS
                              TOTAL
REQRMENT                                                PRA_RQSTD
                              CAAC
DEADLINE                                                PRA_CLERDT
                              DARC
DATENAME                      DFAR                      PRA_EXPDT

                              FAC                       RFA_APPLYS

HFEDREG:                      DAC                       RFA_INITIA

CONTROL                                                 RFA_INIDT
RULE_CITE                     HOPENCAS:                 RFA_FINAL
FED_TYPE                      CONTROL                   RFA_FINLDT
RULE_DATE                     HFILE_NUM                 RFA_CERTDT
PUB_COM                       CAA_NO                    FEDREG
FACDACTYPE                    RINNO                     RULE_TYPE
FACDAC                                                  CN_INTERDT
                              TITLE
FACDACEFCT                                              CN_FINALDT
                              MAJOREVENT
DPT_LTR
                                                        CN_COMMENT

Contract # FA7012-05-C-A002               10 of 47                  November 14, 2008
                                                     Final Database Schema (FDS) (A017)


                                                        OMB_NUMBER
HPART_NO:                     OMB_CASE:                 REGTYPE
CONTROL                       OMBCONTROL                DFARS_PART
PART_NO                       REG_TYPE                  APRVDUPDT
                              DARCASE                   OMB_CLOSE

HSTATUTE:
CONTROL                       OMB_FEDREG:               OMBPART:
PUBLICLAW                     OMBCONTROL                CONTROL
ENACTDATE                     RULE_DATE                 OMB_NUMBER
EFFECTDATE                    RULE_CITE                 REGTYPE
STITLE                        FED_TYPE                  DFARS_PART
SECTION                       PUB_COM                   OMBHRSBAL
                                                        APRVDUPDT

INTEREST:                     OMBCITE:                  OMB_CLOSE

CONTROL                       CONTROL
PARTY                         OMBCONTROL                OMBRPTS:

                              DATETOOMB                 OMBCONTROL
INTERNAL:                     OLDBALANCE                ORDERID
INTEREST                      HRS_RQSTD                 REQ_REPORT
INT_NAME                      DATE_APRVD                SUBPARTINOS

                              HRS_APRVD                 RESPONDCNT
LOCATION:                     CHANGETYPE                RESPSEACH
STATE                                                   ANNUALRESPS
                              NET_CHANGE
STATE_INIT                                              HRSPERRESP
                              PRIOR_COST
                              COST_PUB                  HOTRESPHRS
NUMDAY:                                                 DESC
                              NETCHG_COS
MONTH
                              EXPIREDATE
NUMDAY                                                  OPENCASE:
                              STARTPREP
                              REMARKS                   CONTROL
OHIST:                                                  DAR_DFARNO
                              CASEMGR
CONTROL                                                 FAR_NO
                              _NULLFLAGS
DAR_DFARNO                                              CAA_NO
FAR_NO                                                  RINNO
                              OMBHOLD:
CAA_NO                                                  TITLE
                              CONTROL
HISTORY                                                 MAJOREVENT
                              PB_CONTROL
CASEOPEN

Contract # FA7012-05-C-A002               11 of 47                  November 14, 2008
                                                     Final Database Schema (FDS) (A017)



SYNOP
STATUS                        PART_NO:                  SODFARS:
PRI                           CONTROL                   RPTDATE
MDL                           PART_NO                   ATCOMM

REQUESTOR                     PART_INDEX                ATCOUNCIL

REG                                                     ATSTAFF
                              PBFEDREG:                 REQUEST
STATCODE
                              CONTROL                   RECEIVED
PDATE
                              RULE_CITE                 ATOIRA
CASEMGR
                              FED_TYPE                  OCLEAR
DARCCM
                              RULE_DATE                 PUBCOM
LASTDATE
                              PUB_COM                   ONHOLD
COMM_NAME
                                                        DTOTAL
CASEOPEN                                                TIME_STAMP
                              PROGCONV:
UPDATED
                              PROG
TOTALDAYS                                               SOFAR:
                              ALT_PROG
ONHOLD                                                  RPTDATE
                              STATCODE
ONHOLDDATE                    RULE_TYPE                 OFARDOD
CUMONHOLD                                               OFARCAAC
NETDAY                        REQ_CODE:                 RESOLV
ARCHIVE                       REQ_CODE                  AGREE
PRA_APPLYS                    REQUESTOR                 GSA

PRA_RQSTDT                                              GSA_ISSUES

PRA_CLERDT                                              GSA_CONCUR
                              REVIEWER_LIST:
                                                        OFPP_ISSUE
PRA_EXPRDT                    REVIEWER
                                                        OFPP_RESOL
RFA_APPLYS
                                                        OIRA
RFA_INITIA                    SCREENS:
                                                        OIRA_ISSUE
RFA_INITDT                    SYSTEM
                                                        OIRA_CLEAR
RFA_FINAL                     SYSNAME
                                                        AWAIT_DOD
RFA_FINLDT                    SCREENSPR
                                                        FAR_SEC
RFA_CERTDT                    DESCRIPT
                                                        PUBCOM
FEDREG                                                  ONHOLD
RULE_TYPE                     SERIAL:
                                                        FTOTAL
CN_INTERDT                    NUMBER
                                                        TIME_STAMP
                              SCRAP
CN_FINALDT
                              NAME
CN_COMMENT

Contract # FA7012-05-C-A002               12 of 47                  November 14, 2008
                                                     Final Database Schema (FDS) (A017)



STAFF:                        STATUTES:                 TAGUNIQUE
DARSTAFF                      CONTROL                   TAGORDER
STAFFNAME                     PUBLICLAW
EMAIL                         ENACTDATE                 TABLES:
                              EFFECTDATE                DATABASE
STATCODE:                     STITLE                    TAG
STATOLDDFA                    SECTION                   EXPRESSION
STATOLDFAR                                              UNIQUE
OLDCODE                       STORAGE:
STAT_DESC                     LOCATION                  USER:
STATCODE                      CONFIG                    USERNAME
FAR_AT                                                  LAST
DFARS_AT                      SUBMCASE:                 FIRST
RULE_TYPE                     YEAR                      MIDDLE
CAAC                          CATEGORY                  OFFICE
DARC                          AGNCY_NAME                ACTIVE
                              FAR                       PASSWORD
STATHIST:                     DFARS                     CASE_EDIT
CONTROL                                                 FOIA
STATCODE                      SUBTABS:                  DEVIATION
STATDATE                                                PAPERWORK
                              SUBSYSTEM
RULE_TYPE                                               SYS_ADMIN
                              DATABASE
ENTERDATE
                              TAG
                              TAGEXPR




Contract # FA7012-05-C-A002               13 of 47                  November 14, 2008
                                          Final Database Schema (FDS) (A017)




Database ER Diagram




Figure 1 Database ER Diagram




Contract # FA7012-05-C-A002    14 of 47                November 14, 2008
                                                              Final Database Schema (FDS) (A017)




Table and Data Descriptions
AGENCY
The Agency table is a lookup table which stores the Agency information.
                 PK_AGENCY_ID (NUMBER) IS ‘Generated PK’
                 AGENCY_NAME [VARCHAR2 (4000)] IS ‘Agency type ,i.e. Army, Air
                 Force, etc’
                 CODE[VARCHAR2(4000)] IS ‘It is a respective code’
This table is related to CASES table through FK_AGENCY_ID.



AGENDA
The Agenda table is used to store the details of the agenda code.
                 PK_AGENDA_ID (NUMBER) IS ‘Generated PK’
                 ACTION [VARCHAR2 (4000)] IS ‘ Action for Agenda’
                 NOTE [VARCHAR2 (4000)] IS ‘ Notes taken from Agenda’
                 AGENDA_DATE (DATE) IS 'The date on which the agenda will take
                 place.'
                  FK_AGENDA_CODE_ID (NUMBER) IS 'The associated agenda code,
                 i.e. Reclama, Discuss, etc.'
                 FK_CASE_ID(NUMBER) IS ‘The associated Cases has FK_CASE_ID
This table is related (one to many) to CASES through FK_CASE_ID.


AGENDA_CODE
The Agenda Code table is a lookup table which stores all the agenda types, i.e. DISCUSS,
RECLAMA, TIMECTN, REPORT DUE, and TIMELINE.
                 PK_AGENDA_CODE_ID (NUMBER) IS ‘Generated PK’
                 AGENDA_NAME [VARCHAR2 (20)] IS 'Agenda type name, i.e. Discuss,
                 Reclama, etc.'
                 SORT_ORDER (NUMBER) IS 'The order in which the report will display
                 the agenda types’
This table is related (one to many) to AGENDA table through FK_AGENDA_ID.



CASE_ORIGIN
The Case_origin table is a lookup table which stores all the origin name and code.



Contract # FA7012-05-C-A002                  15 of 47                      November 14, 2008
                                                                 Final Database Schema (FDS) (A017)



                 PK_CASE_ORIGIN_ID (NUMBER) IS 'Generated PK.'
                 ORIGIN_NAME [VARCHAR2 (20)] IS 'Case origin name, i.e. Air Force,
                 Army, etc.'
                 ORIGIN_CODE[VARCHAR2(40)] IS ‘Case origin code, i.e.F,A,etc.’
This table is related to CASES table through FK_CASE_ORIGIN_ID.



CASE_SPONSOR
This Case_sponsor is a look up table which stores all the information for Sponsor name
and code.
                 PK_CASE_SPONSOR_ID (NUMBER) IS 'Generated PK.'
                 SPONSOR_NAME [VARCHAR2 (20)] IS 'Case sponsor name, i.e. Air
                 Force, Army, etc.
                 SPONSOR_CODE [VARCHAR2 (40)] IS ‘Case Sponsor Code, i.e. F, A,
                 etc.
                 DoD[CHAR(1)] IS ‘True or False with DOD’
This table is related to CASES table through FK_CASE_SPONSOR_ID.



CASE_STAGE
The Case_Stage table is a lookup table with all rule types a case can be within, i.e.,
proposed, interim and final.
                 PK_CASE_STAGE_ID (NUMBER) IS ‘Generated PK.’
                 RULE_NAME [VARCHAR2 (20)] IS ‘ Rule Name , i.e. Entry, Final,etc’
                 SORT_ORDER(NUMBER) IS ‘The Sort order for Customization’
                 RECORD_STATUS [CHAR(1)] IS ‘Record Status as I,A’




CASE_STATE
The Case_State table is used to store the DARS status codes. i.e. 111, 113 etc.
                 PK_CASE_STATE_ID (NUMBER) IS 'Generated PK.'
                 CODE [VARCHAR2(3)] IS 'The legacy state code, e.g. 011, 111, etc. '
                 STATE_NAME [VARCHAR2 (50)] IS 'The name/description that will
                 show on the screen which indicates the state, e.g. (P) OIRA Cleared, (Q)
                 At DOD, etc.'




Contract # FA7012-05-C-A002                    16 of 47                       November 14, 2008
                                                                 Final Database Schema (FDS) (A017)



                 FK_CASE_STAGE_ID (NUMBER) IS 'This FK references the case’s rule
                 type, e.g. PROPSED, INTERIM and FINAL, associated with this case
                 state.'
                 FK_REGULATION_TYPE_ID (NUMBER) IS 'This FK references the
                 regulation type, e.g. FAR and DFARS, associated with the case state.'
                 FK_CASE_STAGE_ID (NUMBER) IS ‘This FK references to
                 CASE_STAGE.
                 RECORD STATUS [VARCHAR2(1)] IS ‘This is from CASE_STAGE
                 table’
                 CREATED_BY[VARCHAR2(8)] IS ‘This says who created/how created
                 entry in this table’
                 CREATED_ON(DATE) IS ‘This created date’
                 UPDATED_BY[VARCHAR2(8)] IS ‘Who updated ‘
                 UPDATED_ON(DATE) IS ‘Updated Date’
This table is related (one to many) to CASE_STATE_TEMPLATE table through
FK_CASE_STATE_ID.


CASE_STATE_TEMPLATE
The Case_State_Template table is a lookup table that stores that default business rules
are associated with the status codes.
                 PK_CASE_STATE_TEMPLATE_ID (NUMBER)
                 TEXT [VARCHAR2 (4000)] IS 'the state/rule template.'
                 FK_CASE_STATE_ID (NUMBER) IS 'The associated case state.'
                 FK_ORGANIZATION_ID_COUNCIL (NUMBER) IS 'The associated
                 council, e.g. DARC or CAAC.'
This table is related (one to many) to CASE_STATE_TEMPLATE_ENTRY table through
FK_CASE_STATE_TEMPLATE_ID.


CASE_STATE_TEMPLATE_ENTRY
The Case_State_Template_Entry is used to store the business rules and any
modifications made by the users to the rules.
                 PK_CASE_STATE_TEMPLATE_ENTRY_ID(NUMBER)
                 CREATE_DATE (DATE) IS 'The date on which the case is created.'
                 MODIFIED_TEXT [VARCHAR2 (4000)] IS 'The case state text filled in
                 with specific data, i.e. date, by the user.'
                 FK_PERSONNEL_ID_CREATE (NUMBER) IS 'The user/personnel who
                 creates this entry.'



Contract # FA7012-05-C-A002                    17 of 47                       November 14, 2008
                                                                  Final Database Schema (FDS) (A017)



                 FK_CASE_STATE_TEMPLATE_ID (NUMBER) IS 'The case state
                 template from which the statement is built.'
                 FK_CASE_ID (NUMBER) IS 'The case for which the state is captured
                 for.'
                 ENTRY_POSITION(NUMBER) IS ‘The number of status belong to that
                 case number’
                  MAJOREVENT[VARCHAR2(4000)] IS ‘This field will be populated with
                 legacy data’
                 STATUS_CODE_DATE IS ‘This date associated with Status code
                 change’
This is a connector table between CASE, CASE_STATE_TEMPLATE and
PERSONNEL table.


CASES
The Cases table is used to store all the case records with their activities.
                 PK_CASE_ID (NUMBER) will be 'Generated PK.'
                 CASE_NO [VARCHAR2 (20)] IS 'Case Number assigned to the case.'
                 RIN_NO [VARCHAR2 (4000)] IS ‘Generated from legacy data’
                 SYNOPSIS [VARCHAR2 (4000)] IS ‘The synopsis of the case’
                 TITLE [VARCHAR2 (4000)] IS ‘The title of the case’
                 CREATE_DATE (DATE) IS 'The date on which the case is created.'
                 FK_PERSONNEL_ID_CREATE IS 'The user/personnel who creates the
                 case. ' (NUMBER)
                 FK_PERSONNEL_ID_CASE_MGR IS 'Case manager who is assigned
                 to the case.' (NUMBER)
                 FK_COMMITTEE_ID (NUMBER) IS 'The committee assigned to this
                 case.'
                 FK_REGULATION_TYPE_ID (NUMBER) IS 'The regulation, i.e. FAR or
                 DFARS, for which the case is created for.
                 FK_CASE_ORIGIN_ID (NUMBER) IS ‘The origin for which case is
                 created for’.
                 FK_CASE_SPONSOR_ID(NUMBER) IS ‘ The sponsor for which case is
                 created for’
                 FK_PERSONNEL_ID_FAR_ANALYST(NUMBER) IS ‘ The FAR analyst
                 for the case’
                 CLOSED_FLAG [VARCHAR2(1)] IS ‘To know whether case is closed or
                 opened’
                 STATUS [VARCHAR2(4000)] IS ‘ To know the status of the case’


Contract # FA7012-05-C-A002                   18 of 47                         November 14, 2008
                                                              Final Database Schema (FDS) (A017)



                 DUE_DATE (DATE) IS ‘For FOIA case’
                 COMPLETION_DATE (DATE) IS ‘For FOIA case’
                 REQUESTOR [VARCHAR2(4000)] IS ‘For FOIA case’
                 REQ_PHONE_NO [VARCHAR2(4000)] IS ‘For FOIA case’
                 COMPANY_NAME [VARCHAR2(4000)] IS ‘For FOIA case’
                 COMPANY_ADDRESS[VARCHAR2(4000)] IS ‘For FOIA case’
                 OFOISR_POC_NAME[VARCHAR2(4000)] IS ‘For FOIA case’
                 OFOISR_POC_PHONE_NO[VARCHAR2(4000)] IS ‘For FOIA case’
                 APPROVAL_DATE (DATE) IS ‘For Deviation case’
                 EXPIRATION_DATE (DATE) IS ‘For Deviation case’
                 FK_AGENCY_ID (NUMBER) IS ‘The Agency that belongs to that case’
                 PARTNO(NUMBER) IS ‘For PWB case’
                 START_DATE (DATE) IS ‘For PWB case’
                 DATE_DUE_TO_EDITOR(DATE) IS ‘For PWB case’
                 EDITOR_RECEIVED_DATE(DATE) IS ‘For PWB case’
                 FIRST_FR(DATE) IS ‘For PWB case’
                 FIRST_COMMENT_DATE(DATE) IS ‘For PWB case’
                 TO_WHS(DATE) IS ‘For PWB case’
                 SECOND_FR(DATE) IS ‘For PWB case’
                 SECOND_COMMENT_DATE(DATE) IS ‘For PWB case’
                 CASE_TYPE[VARCHAR2(20)] IS ‘To know which type of the case’
                 FK_COMMITTEE_ID_HOLDINGFILES(NUMBER) ‘This belongs to
                 committee for Holding files’
                 FK_RULE_TYPE_ID (NUMBER) ‘This belongs to Rule Type Id’
                 LEGACY_FLAG ‘This belongs to know the difference between the legacy
                 data and normal data’
This table is related (one to many) to CASE_STATE_TEMPLATE_ENTRY,
PAPERWORK_BURDEN_HISTORY, and PART through FK_CASE_ID.


COMMITTEE
The Committee table is a lookup table which stores all the committees.
                 PK_COMMITTEE_ID (NUMBER) IS ‘Generated PK’
                 COMMITTEE_NAME [VARCHAR2 (4000)] IS ‘The Committee name i.e.
                 Contract Finance, Environmental, etc.



Contract # FA7012-05-C-A002                  19 of 47                      November 14, 2008
                                                                 Final Database Schema (FDS) (A017)



                 FK_REGULATION_TYPE_ID (NUMBER) IS 'The Committee the case is
                 assigned to a regulation type’.
                 FAR_TEAM_FLAG(NUMBER) IS ’This flag set to 1 for FAR cases’
                 COMM_CODE IS ‘The Committee code i.e. CA,ENV,etc’
                 COMM_AGNC IS ‘The Agency that belongs to the committee i.e. OSD,
                 DLA, etc.
                 COMM_HOLD_FLAG IS ‘This flag set to 1 for Holding Files’
This table is related (one to many) to CASE table through FK_COMMITTEE_ID.



COMMITTEE_HOLDINGFILES
The Committee table is a lookup table which stores all the Holding Files committees.
                 PK_COMMITTEE_ID (NUMBER) IS ‘Generated PK’
                 COMM_CODE VARCHAR2(15 BYTE IS ‘The Committee code i.e.
                 CA,ENV,etc’
                  COMMITTEE_NAME VARCHAR2 (100 BYTE) IS ‘The Committee name
                 i.e. Contract Finance, Environmental, etc’.
                 COMM_AGENC VARCHAR2 (15 BYTE) IS ‘The Agency that belongs to
                 the committee i.e. OSD, DLA, etc’.
This table is related (one to many) to CASE table through
FK_COMMITTEE_ID_HOLDINGFILES.



COUNCILAGENDA_RPT
This table is created for weekly report form. This table is a stand alone table.
                 PK_AGENDA_ID NUMBER IS ‘Generated PK’
                 ACTION VARCHAR2(4000 BYTE) IS ‘Agenda Action taken’
                 NOTE VARCHAR2(4000 BYTE) IS ‘Agenda Remarks’
                 AGENDA_DATE DATE IS’ Agenda data
                 COUNCILACTION VARCHAR2(4000 BYTE) IS ‘Agenda Action taken’
                 CASENUMBER VARCHAR2(1000 BYTE) IS ‘Case number represents
                 for which case the agenda is taken’
                 TITLE VARCHAR2(4000 BYTE) IS ‘Title of the case’
                 REG VARCHAR2(100 BYTE) IS ‘Regulation Type of the case’
                 SPONSOR VARCHAR2(4000 BYTE) IS ‘ Sponsor of the case’
                 CASEMANAGER VARCHAR2(1000 BYTE) IS ‘ Case manager of the
                 case’


Contract # FA7012-05-C-A002                   20 of 47                        November 14, 2008
                                                                Final Database Schema (FDS) (A017)



                 FK_CASE_ID NUMBER IS ‘Case Id of the case’



DFARSCLOSEDCASES
This table is created for Reports for DFARS closed cases. This table is a stand alone
table.
       CASENUMBER VARCHAR2(4000 BYTE) IS ‘Case Numbers for all DFARS
        closed cases’
       RULETYPE VARCHAR2(4000 BYTE) IS ‘Rule type for the Cases’
       TITLE VARCHAR2(4000 BYTE) IS ‘Title of the case’
       CASEMANAGER VARCHAR2(4000 BYTE) IS ‘Case manager of the case’
       DATECLOSED VARCHAR2(1000 BYTE) IS ‘Date Closed of the case’
       LASTUPDATE VARCHAR2(4000 BYTE) IS ‘Latest status date and description’
       SUBTITLE VARCHAR2(4000 BYTE) IS ‘Sub title for the case’




FARCLOSEDCASES
This table is created for Reports for FAR closed cases. This table is a stand alone table.
                 CASENUMBER VARCHAR2(4000 BYTE) IS ‘Case Numbers for all FAR
                 closed cases’
                 RULETYPE VARCHAR2(4000 BYTE) IS ‘Rule type for the Cases’
                 TITLE VARCHAR2(4000 BYTE) IS ‘Title of the case’
                 CASEMANAGER VARCHAR2(4000 BYTE) IS ‘Case manager of the
                 case’
                 DATECLOSED VARCHAR2(1000 BYTE) IS ‘Date Closed of the case’
                 LASTUPDATE VARCHAR2(4000 BYTE) IS ‘Latest status date and
                 description’
                 SUBTITLE VARCHAR2(4000 BYTE) IS ‘Sub title for the case’



ORGANIZATION
The Organization table is used to store all organizations involved as a requestor or
sponsor.
                 PK_ORGANIZATION_ID (NUMBER) IS ‘Generated PK’




Contract # FA7012-05-C-A002                  21 of 47                        November 14, 2008
                                                             Final Database Schema (FDS) (A017)



                 ORGANIZATION_NAME [VARCHAR2 (4000)] IS ‘The organization
                 name i.e. CAAC,DARC,etc’
This table is related (one to many) to ORGANIZATION_PERSONNEL, CASE and
PRIORITY tables through FK_ORGANIZATION_ID.



ORGANIZATION_PERSONNEL
The Organization_Personnel is the association table between personnel and organization.
                 FK_PERSONNEL_ID (NUMBER) IS ‘Foreign key from Personnel table’.
                 FK_ORGANIZATION_ID (NUMBER) IS ‘Foreign key from Organization
                 table’.
This table is a connector table between ORGANIZATION and PERSONNEL table.
ORGANIZATION and PERSONNEL table has a many to many relationship.


PAPERWORK_BURDEN_HISTORY
The paperwork_burden_history is the association table between cases and
paperwork_burden_history.
                 FK_HISTORY_ID (NUMBER) IS ‘Generated PK’
                 FK_CASE_NO [VARCHAR2 (4000)] IS ‘Foreign key from Cases table’.
                 APPROVAL_DATE (DATE) IS ‘Approval date for PWB history’.
                 DELTA (NUMBER) IS ‘Difference between hours’.
                 HOURS (NUMBER) IS ‘How much hours for PWB history’.
This table is a connector table between CASES and PAPERWORK_BURDEN_HISTORY
table. These tables have 1 to many relationships.


PART
The Part table is used to store the parts, subparts and subsections of the DFARS and FAR
regulations relating to a case. The user will be able to input the Part Numbers for their
cases which can later be used as a reporting mechanism.
   PK_PART_ID (NUMBER) IS ‘Generated PK’.
   NO [VARCHAR2 (4000)] IS 'part, subpart or subsection number.'
   FK_PART_ID_PARENT (NUMBER) IS 'This FK references the parent part record.'
   FK_REGULATION_TYPE_ID (NUMBER) IS 'This FK references the regulation type,
    e.g. FAR and DFARS, associated with the part.'
   FK_CASE_ID(NUMBER) IS ‘This FK references the CASES.
This table is related (one to many) to PART_CASE table through FK_PART_ID.
This table is related (one to many) to CASES table through FK_CASE_ID.


Contract # FA7012-05-C-A002                 22 of 47                      November 14, 2008
                                                                 Final Database Schema (FDS) (A017)




PERSONNEL
The Personnel table is used to store all users within the DARMIS application.
                 PK_PERSONNEL_ID (NUMBER) IS ‘Generated PK’.
                 USER_NAME[VARCHAR2(32)] IS ‘User Name i.e. Petersmp,Sainb,etc’
                 FIRST_NAME [VARCHAR2 (32)] IS ‘First name for the personnel’.
                 LAST_NAME [VARCHAR2 (32)] IS ‘Last name for the personnel’.
                 MIDDLE_NAME [VARCHAR2 (32)] IS ‘Middle name for the personnel’.
                 CASE_MGR_FLAG (NUMBER) IS 'Boolean which indicates whether the
                 person can act as a case manager.'
                 FK_TITLE_ID (NUMBER) IS 'Associated title.'
                 FAR_ANALYST_FLAG(NUMBER) IS ‘Associated with
                 FAR_ANALYST_FLAG’.
                 CREATED_ON (DATE) IS ‘Created date for the personnel’.
                 UPDATED_ON (DATE) IS ‘Updated date for the personnel’.
                 USER_MAIL[VARCHAR2(40)] IS ‘To store the email address for the
                 personnel’.
This table is related (one to many) to CASE_STATE_TEMPLATE_ENTRY,
ORGANIZATION_PERSONNEL and CASE table through FK_PERSONNEL_ID.


REGULATION_TYPE
The Regulation_Type table is a lookup table which stores the regulations. This table will
allow for growth of new case types.
                 PK_REGULATION_TYPE_ID (NUMBER) IS ‘Generated PK’
                 REGULATION_NAME [VARCHAR2 (20)] IS ‘Regulation Name i.e.
                 DFARS, FAR, PGI.
This table is related (one to many) to COMMITTEE, PART, CASE_STATE_TEMPLATE and
CASE_STATE, CASES tables through FK_REGULATION_TYPE_ID.



REPORTS_LINKS
The Reports_links table is used to store the list of Reports that are used.
                 REPORT_ID (NUMBER) IS ‘Generated PK’
                 REPORTNAME [VARCHAR2 (1000)] IS ‘Report name i.e. Open Cases
                 By FAR Team, etc.
                 REPORTLINK [VARCHAR2 (1000)] IS ‘Report link i.e.
                 ~/Reports/Reports/wf_OpenPGICasesbyCaseNumberOrder.aspx’.


Contract # FA7012-05-C-A002                  23 of 47                         November 14, 2008
                                                                 Final Database Schema (FDS) (A017)



                 ACTIVEFLAG (NUMBER) IS ‘This flag will be set to 1 for Active reports’.
                 ADHOCFLAG (NUMBER) IS ‘This flag will be set to 1 for Adhoc type
                 reports.



USERS
The Users table is a reference table which stores the system user information and their
access rights for the application.
                 PK_USERS_ID (NUMBER) IS ‘Generated PK’
                 FIRST_NAME [VARCHAR2 (1000)] IS ‘First name of the system user’.
                 LAST_NAME [VARCHAR2 (1000)] IS ‘Last name of the system user’.
                 MIDDLE_NAME [VARCHAR2 (1000)] IS ‘Middle name of the system
                 user’
                 OPENCASE_FLAG (NUMBER) IS ‘This flag will be set to 1 for the
                 system to access the Open case’.
                 FOIA_NONFOIA_FLAG (NUMBER) IS ‘This flag will be set to 1 for the
                 system to access the FOIA, Non-FOIA case.’
                 DEV_FLAG (NUMBER) IS ‘This flag will be set to 1 for the system to
                 access the DEV case’.
                 PWB_FLAG (NUMBER) IS ‘This flag will be set to 1 for the system to
                 access the PWB case’.
                 ADMIN_FLAG (NUMBER) IS ‘This flag will be set to 1 for the system to
                 access all type of case’.
                 USER_ID [VARCHAR2 (100)] IS ‘This is the user id for the system user’.
GAP ANALYSIS
There are a lot of tables in the FoxPro database that the new DARMIS application does
not need and so they are not in the Oracle database except as part of the raw data. The
reason for this is that redundancy of the legacy data occurs in multiple tables as well as
obsolete information is not used in the new application.


LEGACY TABLES
The following tables are available with the non-processed legacy data in the Oracle
database.
       AGENDA_RAW
       ARCH_AGENDA
       ARCH_CASE
       ARCHCASE_OLD_RAW
       ARCH_DEV


Contract # FA7012-05-C-A002                   24 of 47                        November 14, 2008
                                                              Final Database Schema (FDS) (A017)



       ARCH_FOIA
       ARCH_PART
       ARCHCASE_RAW
       ARCHDEVIATE_RAW
       CFOIA_RAW
       DEVIATE_RAW
       FAR_ANALYST_OPENCASE_RAW
       FOIA_RAW
       HARCHCASE_RAW
       HOPENCASE_RAW
       OMBCITE_RAW
       OMBHOLD_RAW
       OMBPART_RAW
       OPENCASE_RAW
       OPENCASE_RAW_DUMP
       OPENCASE_RAW_MAJEVENT_SPLIT
       PART_NO_RAW

AGENDA_RAW
This table has the agenda information that is stored raw data from legacy system.


                 CONTROL         VARCHAR2 (4000 BYTE) IS ‘Not used in the new
                 system’.
                 AGNDCODE VARCHAR2(4000 BYTE) IS ‘Agenda code like
                 Discuss,etc’
                AGNDDATE DATE IS ‘Date is for Agenda action’
                AGNDACTION         VARCHAR2(4000 BYTE)IS ‘Action for Agenda’
                 ACTIONTAKEN       VARCHAR2(4000 BYTE)IS ‘Action taken for
                 Agenda’
                ACTIONNOTES        VARCHAR2(4000 BYTE)IS ‘Remarks for Agenda’
                 CASE_NO     VARCHAR2(100 BYTE)IS ‘Case number associated for
                 Agenda’
                CASE_ID      NUMBER IS ‘Case Id associated for Agenda’




Contract # FA7012-05-C-A002                 25 of 47                       November 14, 2008
                                                                  Final Database Schema (FDS) (A017)



ARCH_AGENDA
This table has the agenda information that has migrated from AGENDA_RAW.
                PK_AGENDA_ID            NUMBER IS ‘Generated PK’
                ACTION          VARCHAR2(4000 BYTE) IS ‘Action for Agenda’
                NOTE VARCHAR2(4000 BYTE) IS ‘Remarks for Agenda’
                AGENDA_DATE             DATE IS ‘Date for Agenda’
                 FK_AGENDA_CODE_ID             NUMBER IS ‘it is associated with Agenda
                 code’
                FK_CASE_ID NUMBER IS ‘Case Id associated for Agenda’



ARCH_CASE
This table is used to store the archive information for cases.
                CONTROL         VARCHAR2 (100 BYTE) IS ‘Not used in the new system’.
                  DAR_DFARNO VARCHAR2(100 BYTE) IS ‘DAR_DFARNO number’
                  FAR_NO        VARCHAR2 (100 BYTE) IS ‘FAR number for the archive
                 case’’.
                  CAA_NO        VARCHAR2 (100 BYTE) IS ‘CAAC number for the archive
                 case’’.
                  RINNO        VARCHAR2 (100 BYTE) IS ‘RIN number for the archive
                 case’’.
                  TITLE       VARCHAR2 (4000 BYTE) IS ‘Title for the archive case’.
                  MAJOREVENT VARCHAR2 (4000 BYTE) IS ‘Majorevent for the
                 archive case’.
                  SYNOP        VARCHAR2 (4000 BYTE) IS ‘Synopsis for the archive
                 case’.
                  STATUS        VARCHAR2 (4000 BYTE) IS ‘Status for the archive case’.
                  PRI        VARCHAR2 (100 BYTE) IS ‘priority for the archive case’.
                  MDL        DATE IS ‘MDL date for the archive case’.
                  REQUESTOR VARCHAR2(100 BYTE) IS ‘Requestor for archive case’
                  REG         VARCHAR2(100 BYTE) IS ‘Regulation type for archive case’
                  STATCODE        VARCHAR2(100 BYTE) IS ‘Status code for archive case’
                  PDATE        DATE IS ‘Recent Status date for archive case’
                  CASEMGR        VARCHAR2(100 BYTE) IS ‘Case Manger for archive
                 case’
                  DARCCM         VARCHAR2(100 BYTE) IS ‘Sponsor for the archive case’



Contract # FA7012-05-C-A002                     26 of 47                       November 14, 2008
                                                              Final Database Schema (FDS) (A017)



                  LASTDATE    DATE IS ‘Not used in the new system’
                  COMM_NAME VARCHAR2(100 BYTE) IS ‘Committee for the archive
                 case’
                  CASEOPEN     DATE IS ‘Open Date for the archive case’
                  UPDATED1     DATE IS ‘Not used in the new system’
                  TOTALDAYS NUMBER IS ‘Not used in the new system’
                  ONHOLD      NUMBER IS ‘Not used in the new system’
                  ONHOLDDATE DATE IS ‘Not used in the new system’
                  CUMONHOLD NUMBER IS ‘Not used in the new system’
                  NETDAY     NUMBER IS ‘Not used in the new system’
                  ARCHIVE1    VARCHAR2(10 BYTE) IS ‘Not used in the new system’
                  PRA_APPLYS VARCHAR2(10 BYTE) IS ‘Not used in the new system’
                  PRA_RQSTDT DATE IS ‘Not used in the new system’
                  PRA_CLERDT DATE IS ‘Not used in the new system’
                  PRA_EXPRDT DATE IS ‘Not used in the new system’
                  RFA_APPLYS VARCHAR2(10 BYTE) IS ‘Not used in the new system’
                  RFA_INITIA VARCHAR2(10 BYTE) IS ‘Not used in the new system’
                  RFA_INITDT DATE IS ‘Not used in the new system’
                  RFA_FINAL VARCHAR2(10 BYTE) IS ‘Not used in the new system’
                  RFA_FINLDT DATE IS ‘Not used in the new system’
                  RFA_CERTDT DATE IS ‘Not used in the new system’
                  FEDREG     NUMBER IS ‘Not used in the new system’
                  RULE_TYPE VARCHAR2(100 BYTE) IS ‘Rule type for the archive
                 system’
                  CN_INTERDT DATE IS ‘Not used in the new system’
                  CN_FINALDT DATE IS ‘Not used in the new system’
                  CN_COMMENT VARCHAR2(4000 BYTE) IS ‘Not used in the new
                 system’
                  COMMENT1 VARCHAR2(4000 BYTE)
                 LEGACY_FLAG        NUMBER(1,0)
                 CASE_ID      NUMBER(10,0)
                 CASE_NO      VARCHAR2(15 BYTE)
                 FK_PERSONNEL_ID_MGR NUMBER(38,0)
                 FK_COMMITTEE_ID NUMBER(10,0)



Contract # FA7012-05-C-A002                 27 of 47                       November 14, 2008
                                                                  Final Database Schema (FDS) (A017)




ARCHCASE_OLD_RAW
This table is used to store the archive information for cases.
                CONTROL         VARCHAR2 (100 BYTE) IS ‘Not used in the new system’.
                  DAR_DFARNO VARCHAR2(100 BYTE) IS ‘DAR_DFARNO number’
                  FAR_NO        VARCHAR2 (100 BYTE) IS ‘FAR number for the archive
                 case’’.
                  CAA_NO        VARCHAR2 (100 BYTE) IS ‘CAAC number for the archive
                 case’’.
                  RINNO        VARCHAR2 (100 BYTE) IS ‘RIN number for the archive
                 case’’.
                  TITLE       VARCHAR2 (4000 BYTE) IS ‘Title for the archive case’.
                  MAJOREVENT CLOB IS ‘Majorevent for the archive case’.
                  SYNOP        VARCHAR2 (4000 BYTE) IS ‘Synopsis for the archive
                 case’.
                  STATUS        VARCHAR2 (4000 BYTE) IS ‘Status for the archive case’.
                  PRI        VARCHAR2 (100 BYTE) IS ‘priority for the archive case’.
                  MDL        DATE IS ‘MDL date for the archive case’.
                  REQUESTOR VARCHAR2(100 BYTE) IS ‘Requestor for archive case’
                  REG         VARCHAR2(100 BYTE) IS ‘Regulation type for archive case’
                  STATCODE        VARCHAR2(100 BYTE) IS ‘Status code for archive case’
                  PDATE        DATE IS ‘Recent Status date for archive case’
                  CASEMGR        VARCHAR2(100 BYTE) IS ‘Case Manger for archive
                 case’
                  DARCCM         VARCHAR2(100 BYTE) IS ‘Sponsor for the archive case’
                  LASTDATE       DATE IS ‘Not used in the new system’
                  COMM_NAME VARCHAR2(100 BYTE) IS ‘Committee for the archive
                 case’
                  CASEOPEN        DATE IS ‘Open Date for the archive case’
                  UPDATED1       DATE IS ‘Not used in the new system’
                  TOTALDAYS NUMBER IS ‘Not used in the new system’
                  ONHOLD        NUMBER IS ‘Not used in the new system’
                  ONHOLDDATE DATE IS ‘Not used in the new system’
                  CUMONHOLD NUMBER IS ‘Not used in the new system’
                  NETDAY        NUMBER IS ‘Not used in the new system’


Contract # FA7012-05-C-A002                     28 of 47                       November 14, 2008
                                                             Final Database Schema (FDS) (A017)



                  ARCHIVE1    VARCHAR2(10 BYTE) IS ‘Not used in the new system’
                  PRA_APPLYS VARCHAR2(10 BYTE) IS ‘Not used in the new system’
                  PRA_RQSTDT DATE IS ‘Not used in the new system’
                  PRA_CLERDT DATE IS ‘Not used in the new system’
                  PRA_EXPRDT DATE IS ‘Not used in the new system’
                  RFA_APPLYS VARCHAR2(10 BYTE) IS ‘Not used in the new system’
                  RFA_INITIA VARCHAR2(10 BYTE) IS ‘Not used in the new system’
                  RFA_INITDT DATE IS ‘Not used in the new system’
                  RFA_FINAL VARCHAR2(10 BYTE) IS ‘Not used in the new system’
                  RFA_FINLDT DATE IS ‘Not used in the new system’
                  RFA_CERTDT DATE IS ‘Not used in the new system’
                  FEDREG     NUMBER IS ‘Not used in the new system’
                  RULE_TYPE VARCHAR2(100 BYTE) IS ‘Rule type for the archive
                 system’
                  CN_INTERDT DATE IS ‘Not used in the new system’
                  CN_FINALDT DATE IS ‘Not used in the new system’
                  CN_COMMENT VARCHAR2(4000 BYTE) IS ‘Not used in the new
                 system’
                 COMMENT1 VARCHAR2(4000 BYTE) IS ‘Not used in the new system’
                 LEGACY_FLAG        NUMBER(1,0) IS ‘set to 1’
                 CASE_ID      NUMBER(10,0) IS ‘Auto generated’
                 CASE_NO      VARCHAR2(15 BYTE) IS ‘Case number of the archive
                 case’
                 FK_PERSONNEL_ID_MGR NUMBER(38,0) IS ‘Case manager Id
                 associated with case’
                 FK_COMMITTEE_ID NUMBER (10, 0) IS ‘Committee Id associated with
                 case’.




Contract # FA7012-05-C-A002                 29 of 47                      November 14, 2008
                                                                 Final Database Schema (FDS) (A017)



ARCH_DEV
This table is used to store the Archive information for deviation cases.
                 CONTROL       VARCHAR2(1000 BYTE) IS ‘Not used in the new system’
                  DARTRACK VARCHAR2(1000 BYTE) IS ‘Case number for deviation
                 case’
                  TITLE      VARCHAR2(1000 BYTE) IS ‘Title of the deviation case’
                  REQUESTOR VARCHAR2(1000 BYTE) IS ‘Not used in the new
                 system’
                  REQDATE       VARCHAR2(1000 BYTE) IS ‘Not used in the new system’
                  DDPACT       VARCHAR2(1000 BYTE) IS ‘Not used in the new system’
                  DDPDATE VARCHAR2(1000 BYTE) IS ‘Approval date for the
                 deviation case’
                  EXPDATE VARCHAR2(1000 BYTE) IS ‘Expiration date for the
                 deviation case’
                  DEVFLAG      VARCHAR2(1000 BYTE) IS ‘Not used in the new system’
                  STATUS       VARCHAR2(4000 BYTE) IS ‘Status for the deviation case’
                  MAJEVENTS VARCHAR2(4000 BYTE) IS ‘Majorevent for the deviation
                 case’
                 CASE_ID        NUMBER(10,0) IS ‘Auto generated ‘
                 LEGACY_FLAG           NUMBER(1,0) IS ‘Set to 1’


ARCH_FOIA
This table is used to store the archive information for foia cases.
                 CONTROL       VARCHAR2(1000 BYTE) IS ‘Not used in the new system’
                  FOIANO       VARCHAR2(1000 BYTE) IS ‘Case number for FOIA case’
                  MANAGER        VARCHAR2(4000 BYTE) IS ‘Case manager for FOIA
                 case’
                  REQUESTOR VARCHAR2(4000 BYTE) IS ‘Requestor for FOIA case’
                  COMPANY        VARCHAR2(4000 BYTE) IS ‘Company for FOIA case’
                  LOCATION      VARCHAR2(4000 BYTE) IS ‘Location for FOIA case’
                  PHONE        VARCHAR2(4000 BYTE) IS ‘Phone for the requestor’
                  REQ_DT       DATE IS ‘Not used in the new system’
                  REC_DT       DATE IS ‘Create date for the FOIA case’
                  COMPL_DT       DATE IS ‘Completion date for the FOIA case’
                  TOT_DAYS       VARCHAR2(4000 BYTE) IS ‘Not used in the new system’


Contract # FA7012-05-C-A002                    30 of 47                       November 14, 2008
                                                                     Final Database Schema (FDS) (A017)



                  INF_REQ         VARCHAR2(4000 BYTE) IS ‘Not used in the new system’
                  STATUS          VARCHAR2(4000 BYTE) IS ‘Not used in the new system’
                  REMARKS          VARCHAR2(4000 BYTE) IS ‘Not used in the new system’
                  JDRSTART         DATE IS ‘Not used in the new system’
                  JDRFINIS        DATE IS ‘Not used in the new system’
                  JDRCUMDAYS VARCHAR2(4000 BYTE) IS ‘Not used in the new
                 system’
                  DATETO02         DATE IS ‘Not used in the new system’
                  REFERDATE DATE IS ‘Not used in the new system’
                  REFERCOMP VARCHAR2(4000 BYTE) IS ‘Not used in the new
                 system’
                 CASE_ID           NUMBER(10,0) IS ‘Auto generated’
                 LEGACY_FLAG              NUMBER(1,0) IS ‘Set to 1’
                 FK_PERSONNEL_ID_MGR NUMBER (10, 0) ‘Case manager
                 associated with case.



ARCH_HOLD
This table is used to store the archive information for holding file.
                 CONTROL          VARCHAR2(100 BYTE) IS ‘Not used in the new system’
                   HFILE_NUM VARCHAR2(100 BYTE) IS ‘Case number for Holding
                 File’
                    CAA_NO        VARCHAR2 (100 BYTE) IS ‘CAAC number for the holding
                 file case’’.
                  RINNO          VARCHAR2 (100 BYTE) IS ‘RIN number for the holding file
                 case’’.
                  TITLE         VARCHAR2 (4000 BYTE) IS ‘Title for the holding file case’.
                  MAJOREVENT VARCHAR2 (4000 BYTE) IS ‘Majorevent for the
                 holding file case’.
                  SYNOP          VARCHAR2 (4000 BYTE) IS ‘Synopsis for the holding file
                 case’.
                  STATUS          VARCHAR2 (4000 BYTE) IS ‘Status for the holding file
                 case’.
                  PRI          VARCHAR2 (100 BYTE) IS ‘priority for the holding file case’.
                  MDL           DATE IS ‘MDL date for the holding file case’.
                  REQUESTOR VARCHAR2(100 BYTE) IS ‘Requestor for holding file
                 case’



Contract # FA7012-05-C-A002                       31 of 47                        November 14, 2008
                                                                  Final Database Schema (FDS) (A017)



                  REG        VARCHAR2(100 BYTE) IS ‘Regulation type for holding file
                 case’
                  STATCODE      VARCHAR2(100 BYTE) IS ‘Status code for holding file
                 case’
                  PDATE       DATE IS ‘Recent Status date for holding file case’
                  CASEMGR       VARCHAR2(100 BYTE) IS ‘Case Manger for holding file
                 case’
                  DARCCM       VARCHAR2(100 BYTE) IS ‘Sponsor for the holding file
                 case’
                  LASTDATE      DATE IS ‘Not used in the new system’
                    COMM_NAME VARCHAR2(100 BYTE) IS ‘Committee for the holding
                 file case’
                  CASEOPEN       DATE IS ‘Open Date for the holding file case’
                  UPDATED1      DATE IS ‘Not used in the new system’
                  TOTALDAYS NUMBER IS ‘Not used in the new system’
                  ONHOLD       NUMBER IS ‘Not used in the new system’
                  ONHOLDDATE DATE IS ‘Not used in the new system’
                  CUMONHOLD NUMBER IS ‘Not used in the new system’
                  NETDAY       NUMBER IS ‘Not used in the new system’
                  ARCHIVE1      VARCHAR2(10 BYTE) IS ‘Not used in the new system’
                  PRA_APPLYS VARCHAR2(10 BYTE) IS ‘Not used in the new system’
                  PRA_RQSTDT DATE IS ‘Not used in the new system’
                  PRA_CLERDT DATE IS ‘Not used in the new system’
                  PRA_EXPRDT DATE IS ‘Not used in the new system’
                  RFA_APPLYS VARCHAR2(10 BYTE) IS ‘Not used in the new system’
                  RFA_INITIA VARCHAR2(10 BYTE) IS ‘Not used in the new system’
                  RFA_INITDT DATE IS ‘Not used in the new system’
                  RFA_FINAL VARCHAR2(10 BYTE) IS ‘Not used in the new system’
                  RFA_FINLDT DATE IS ‘Not used in the new system’
                  RFA_CERTDT DATE IS ‘Not used in the new system’
                  FEDREG       NUMBER IS ‘Not used in the new system’
                  RULE_TYPE VARCHAR2(100 BYTE) IS ‘Rule type for the holding file
                 system’
                  CN_INTERDT DATE IS ‘Not used in the new system’
                  CN_FINALDT DATE IS ‘Not used in the new system’



Contract # FA7012-05-C-A002                    32 of 47                        November 14, 2008
                                                                  Final Database Schema (FDS) (A017)



                  CN_COMMENT VARCHAR2(4000 BYTE) IS ‘Not used in the new
                 system’
                 CASE_ID        NUMBER(10,0) IS ‘Auto generated’
                 LEGACY_FLAG            NUMBER(1,0) IS ‘Set to 1’
                 FK_PERSONNEL_ID_MGR NUMBER(10,0) IS ‘Case manager id
                 associated with case’
                 FK_COMMITTEE_ID NUMBER(10,0) IS ‘Committee id associated with
                 case’


ARCH_PART
This table stores the part number information for the archive cases.
                 PK_PART_ID NUMBER IS ‘Generated PK’
                 NO      VARCHAR2(4000 BYTE) IS ‘ Part number for the case’
                 FK_REGULATION_TYPE_ID NUMBER IS ‘Regulation type associated
                 with part number’
                 FK_PART_ID_PARENT NUMBER IS ‘Not used in the system’
                 FK_CASE_ID NUMBER IS ‘Case id associated with case’


ARCH_CASE_RAW
This table is used to store the archive information for cases.
                 CONTROL        VARCHAR2 (100 BYTE) IS ‘Not used in the new system’.
                  DAR_DFARNO VARCHAR2(100 BYTE) IS ‘DAR_DFARNO number’
                  FAR_NO        VARCHAR2 (100 BYTE) IS ‘FAR number for the archive
                 case’’.
                  CAA_NO        VARCHAR2 (100 BYTE) IS ‘CAAC number for the archive
                 case’’.
                  RINNO        VARCHAR2 (100 BYTE) IS ‘RIN number for the archive
                 case’’.
                  TITLE       VARCHAR2 (4000 BYTE) IS ‘Title for the archive case’.
                  MAJOREVENT VARCHAR2 (4000 BYTE) IS ‘Majorevent for the
                 archive case’.
                  SYNOP        VARCHAR2 (4000 BYTE) IS ‘Synopsis for the archive
                 case’.
                  STATUS        VARCHAR2 (4000 BYTE) IS ‘Status for the archive case’.
                  PRI        VARCHAR2 (100 BYTE) IS ‘priority for the archive case’.
                  MDL        DATE IS ‘MDL date for the archive case’.


Contract # FA7012-05-C-A002                     33 of 47                       November 14, 2008
                                                                Final Database Schema (FDS) (A017)



                  REQUESTOR VARCHAR2(100 BYTE) IS ‘Requestor for archive case’
                  REG        VARCHAR2(100 BYTE) IS ‘Regulation type for archive case’
                  STATCODE      VARCHAR2(100 BYTE) IS ‘Status code for archive case’
                  PDATE       DATE IS ‘Recent Status date for archive case’
                  CASEMGR       VARCHAR2(100 BYTE) IS ‘Case Manger for archive
                 case’
                  DARCCM       VARCHAR2(100 BYTE) IS ‘Sponsor for the archive case’
                  LASTDATE      DATE IS ‘Not used in the new system’
                  COMM_NAME VARCHAR2(100 BYTE) IS ‘Committee for the archive
                 case’
                  CASEOPEN       DATE IS ‘Open Date for the archive case’
                  UPDATED1      DATE IS ‘Not used in the new system’
                  TOTALDAYS NUMBER IS ‘Not used in the new system’
                  ONHOLD       NUMBER IS ‘Not used in the new system’
                  ONHOLDDATE DATE IS ‘Not used in the new system’
                  CUMONHOLD NUMBER IS ‘Not used in the new system’
                  NETDAY       NUMBER IS ‘Not used in the new system’
                  ARCHIVE1     VARCHAR2(10 BYTE) IS ‘Not used in the new system’
                  PRA_APPLYS VARCHAR2(10 BYTE) IS ‘Not used in the new system’
                  PRA_RQSTDT DATE IS ‘Not used in the new system’
                  PRA_CLERDT DATE IS ‘Not used in the new system’
                  PRA_EXPRDT DATE IS ‘Not used in the new system’
                  RFA_APPLYS VARCHAR2(10 BYTE) IS ‘Not used in the new system’
                  RFA_INITIA VARCHAR2(10 BYTE) IS ‘Not used in the new system’
                  RFA_INITDT DATE IS ‘Not used in the new system’
                  RFA_FINAL VARCHAR2(10 BYTE) IS ‘Not used in the new system’
                  RFA_FINLDT DATE IS ‘Not used in the new system’
                  RFA_CERTDT DATE IS ‘Not used in the new system’
                  FEDREG       NUMBER IS ‘Not used in the new system’
                  RULE_TYPE VARCHAR2(100 BYTE) IS ‘Rule type for the archive
                 system’
                  CN_INTERDT DATE IS ‘Not used in the new system’
                  CN_FINALDT DATE IS ‘Not used in the new system’
                  CN_COMMENT VARCHAR2(4000 BYTE) IS ‘Not used in the new
                 system’

Contract # FA7012-05-C-A002                   34 of 47                       November 14, 2008
                                                                 Final Database Schema (FDS) (A017)



                  COMMENT1 VARCHAR2(4000 BYTE)
                 LEGACY_FLAG           NUMBER(1,0)
                 CASE_ID        NUMBER(10,0)
                 CASE_NO        VARCHAR2(15 BYTE)
                 FK_PERSONNEL_ID_MGR NUMBER(38,0)
                 FK_COMMITTEE_ID NUMBER(10,0)


ARCHDEVIATE_RAW
The archdeviate_raw table is used to store the Archive information for deviation cases.
                 CONTROL       VARCHAR2(1000 BYTE) IS ‘Not used in the new system’
                  DARTRACK VARCHAR2(1000 BYTE) IS ‘Case number for deviation
                 case’
                  TITLE      VARCHAR2(1000 BYTE) IS ‘Title of the deviation case’
                  REQUESTOR VARCHAR2(1000 BYTE) IS ‘Not used in the new
                 system’
                  REQDATE       VARCHAR2(1000 BYTE) IS ‘Not used in the new system’
                  DDPACT       VARCHAR2(1000 BYTE) IS ‘Not used in the new system’
                  DDPDATE VARCHAR2(1000 BYTE) IS ‘Approval date for the
                 deviation case’
                  EXPDATE VARCHAR2(1000 BYTE) IS ‘Expiration date for the
                 deviation case’
                  DEVFLAG      VARCHAR2(1000 BYTE) IS ‘Not used in the new system’
                  STATUS       VARCHAR2(4000 BYTE) IS ‘Status for the deviation case’
                  MAJEVENTS VARCHAR2(4000 BYTE) IS ‘Majorevent for the deviation
                 case’


CFOIA_RAW
This cfoia_raw table is used to store the archive information for foia cases.
                 CONTROL       VARCHAR2(1000 BYTE) IS ‘Not used in the new system’
                  FOIANO       VARCHAR2(1000 BYTE) IS ‘Case number for FOIA case’
                  MANAGER        VARCHAR2(4000 BYTE) IS ‘Case manager for FOIA
                 case’
                  REQUESTOR VARCHAR2(4000 BYTE) IS ‘Requestor for FOIA case’
                  COMPANY        VARCHAR2(4000 BYTE) IS ‘Company for FOIA case’
                  LOCATION      VARCHAR2(4000 BYTE) IS ‘Location for FOIA case’


Contract # FA7012-05-C-A002                    35 of 47                       November 14, 2008
                                                                Final Database Schema (FDS) (A017)



                  PHONE        VARCHAR2(4000 BYTE) IS ‘Phone for the requestor’
                  REQ_DT       DATE IS ‘Not used in the new system’
                  REC_DT       DATE IS ‘Create date for the FOIA case’
                  COMPL_DT       DATE IS ‘Completion date for the FOIA case’
                  TOT_DAYS       VARCHAR2(4000 BYTE) IS ‘Not used in the new system’
                  INF_REQ      VARCHAR2(4000 BYTE) IS ‘Not used in the new system’
                  STATUS       VARCHAR2(4000 BYTE) IS ‘Not used in the new system’
                  REMARKS        VARCHAR2(4000 BYTE) IS ‘Not used in the new system’
                  JDRSTART       DATE IS ‘Not used in the new system’
                  JDRFINIS     DATE IS ‘Not used in the new system’
                  JDRCUMDAYS VARCHAR2(4000 BYTE) IS ‘Not used in the new
                 system’
                  DATETO02       DATE IS ‘Not used in the new system’
                  REFERDATE DATE IS ‘Not used in the new system’
                  REFERCOMP VARCHAR2(4000 BYTE) IS ‘Not used in the new
                 system’


DEVIATE_RAW
This deviate_raw table is used to store the non-processed information for deviation cases.
                 CONTROL       VARCHAR2(1000 BYTE) IS ‘Not used in the new system’
                  DARTRACK VARCHAR2(1000 BYTE) IS ‘Case number for deviation
                 case’
                  TITLE      VARCHAR2(1000 BYTE) IS ‘Title of the deviation case’
                  REQUESTOR VARCHAR2(1000 BYTE) IS ‘Not used in the new
                 system’
                  REQDATE       VARCHAR2(1000 BYTE) IS ‘Not used in the new system’
                  DDPACT       VARCHAR2(1000 BYTE) IS ‘Not used in the new system’
                  DDPDATE VARCHAR2(1000 BYTE) IS ‘Approval date for the
                 deviation case’
                  EXPDATE VARCHAR2(1000 BYTE) IS ‘Expiration date for the
                 deviation case’
                  DEVFLAG      VARCHAR2(1000 BYTE) IS ‘Not used in the new system’
                  STATUS       VARCHAR2(4000 BYTE) IS ‘Status for the deviation case’
                  MAJEVENTS VARCHAR2(4000 BYTE) IS ‘Majorevent for the deviation
                 case’



Contract # FA7012-05-C-A002                    36 of 47                      November 14, 2008
                                                               Final Database Schema (FDS) (A017)




FAR_ANALYST_OPENCASE_RAW
This table is used to store the missing the FAR analyst information from legacy system.


                 CASE_NO       VARCHAR2(100 BYTE) IS ‘Case number is associated
                 with FAR analyst’
                 FAR_ANALYST        VARCHAR2(4000 BYTE) IS ‘FAR analyst for the
                 case’


FOIA_RAW
This foia_raw table is used to store the non-processed information for foia cases.
                 CONTROL     VARCHAR2(1000 BYTE) IS ‘Not used in the new system’
                  FOIANO     VARCHAR2(1000 BYTE) IS ‘Case number for FOIA case’
                  MANAGER      VARCHAR2(4000 BYTE) IS ‘Case manager for FOIA
                 case’
                  REQUESTOR VARCHAR2(4000 BYTE) IS ‘Requestor for FOIA case’
                  COMPANY      VARCHAR2(4000 BYTE) IS ‘Company for FOIA case’
                  LOCATION    VARCHAR2(4000 BYTE) IS ‘Location for FOIA case’
                  PHONE      VARCHAR2(4000 BYTE) IS ‘Phone for the requestor’
                  REQ_DT     DATE IS ‘Not used in the new system’
                  REC_DT     DATE IS ‘Create date for the FOIA case’
                  COMPL_DT     DATE IS ‘Completion date for the FOIA case’
                  TOT_DAYS    VARCHAR2(4000 BYTE) IS ‘Not used in the new system’
                  INF_REQ    VARCHAR2(4000 BYTE) IS ‘Not used in the new system’
                  STATUS     VARCHAR2(4000 BYTE) IS ‘Not used in the new system’
                  REMARKS     VARCHAR2(4000 BYTE) IS ‘Not used in the new system’
                  JDRSTART    DATE IS ‘Not used in the new system’
                  JDRFINIS   DATE IS ‘Not used in the new system’
                  JDRCUMDAYS VARCHAR2(4000 BYTE) IS ‘Not used in the new
                 system’
                  DATETO02    DATE IS ‘Not used in the new system’
                  REFERDATE DATE IS ‘Not used in the new system’
                  REFERCOMP VARCHAR2(4000 BYTE) IS ‘Not used in the new
                 system’




Contract # FA7012-05-C-A002                 37 of 47                        November 14, 2008
                                                                     Final Database Schema (FDS) (A017)




HARCHCASE_RAW
This harchcase_raw table is used to store the archive information for holding file.
                 CONTROL          VARCHAR2(100 BYTE) IS ‘Not used in the new system’
                   HFILE_NUM VARCHAR2(100 BYTE) IS ‘Case number for Holding
                 File’
                    CAA_NO        VARCHAR2 (100 BYTE) IS ‘CAAC number for the holding
                 file case’’.
                  RINNO          VARCHAR2 (100 BYTE) IS ‘RIN number for the holding file
                 case’’.
                  TITLE         VARCHAR2 (4000 BYTE) IS ‘Title for the holding file case’.
                  MAJOREVENT VARCHAR2 (4000 BYTE) IS ‘Majorevent for the
                 holding file case’.
                  SYNOP          VARCHAR2 (4000 BYTE) IS ‘Synopsis for the holding file
                 case’.
                  STATUS          VARCHAR2 (4000 BYTE) IS ‘Status for the holding file
                 case’.
                  PRI          VARCHAR2 (100 BYTE) IS ‘priority for the holding file case’.
                  MDL           DATE IS ‘MDL date for the holding file case’.
                  REQUESTOR VARCHAR2(100 BYTE) IS ‘Requestor for holding file
                 case’
                  REG           VARCHAR2(100 BYTE) IS ‘Regulation type for holding file
                 case’
                  STATCODE          VARCHAR2(100 BYTE) IS ‘Status code for holding file
                 case’
                  PDATE          DATE IS ‘Recent Status date for holding file case’
                  CASEMGR           VARCHAR2(100 BYTE) IS ‘Case Manger for holding file
                 case’
                  DARCCM           VARCHAR2(100 BYTE) IS ‘Sponsor for the holding file
                 case’
                  LASTDATE         DATE IS ‘Not used in the new system’
                    COMM_NAME VARCHAR2(100 BYTE) IS ‘Committee for the holding
                 file case’
                  CASEOPEN          DATE IS ‘Open Date for the holding file case’
                  UPDATED1          DATE IS ‘Not used in the new system’
                  TOTALDAYS NUMBER IS ‘Not used in the new system’
                  ONHOLD           NUMBER IS ‘Not used in the new system’



Contract # FA7012-05-C-A002                       38 of 47                        November 14, 2008
                                                                   Final Database Schema (FDS) (A017)



                  ONHOLDDATE DATE IS ‘Not used in the new system’
                  CUMONHOLD NUMBER IS ‘Not used in the new system’
                  NETDAY         NUMBER IS ‘Not used in the new system’
                  ARCHIVE1        VARCHAR2(10 BYTE) IS ‘Not used in the new system’
                  PRA_APPLYS VARCHAR2(10 BYTE) IS ‘Not used in the new system’
                  PRA_RQSTDT DATE IS ‘Not used in the new system’
                  PRA_CLERDT DATE IS ‘Not used in the new system’
                  PRA_EXPRDT DATE IS ‘Not used in the new system’
                  RFA_APPLYS VARCHAR2(10 BYTE) IS ‘Not used in the new system’
                  RFA_INITIA VARCHAR2(10 BYTE) IS ‘Not used in the new system’
                  RFA_INITDT DATE IS ‘Not used in the new system’
                  RFA_FINAL VARCHAR2(10 BYTE) IS ‘Not used in the new system’
                  RFA_FINLDT DATE IS ‘Not used in the new system’
                  RFA_CERTDT DATE IS ‘Not used in the new system’
                  FEDREG         NUMBER IS ‘Not used in the new system’
                  RULE_TYPE VARCHAR2(100 BYTE) IS ‘Rule type for the holding file
                 system’
                  CN_INTERDT DATE IS ‘Not used in the new system’
                  CN_FINALDT DATE IS ‘Not used in the new system’
                  CN_COMMENT VARCHAR2(4000 BYTE) IS ‘Not used in the new
                 system’


HOPENCASE_RAW
This hopencase_raw is used to store the non-processed information for holding file.
                 CONTROL         VARCHAR2(100 BYTE) IS ‘Not used in the new system’
                   HFILE_NUM VARCHAR2(100 BYTE) IS ‘Case number for Holding
                 File’
                    CAA_NO       VARCHAR2 (100 BYTE) IS ‘CAAC number for the holding
                 file case’’.
                  RINNO         VARCHAR2 (100 BYTE) IS ‘RIN number for the holding file
                 case’’.
                  TITLE        VARCHAR2 (4000 BYTE) IS ‘Title for the holding file case’.
                  MAJOREVENT VARCHAR2 (4000 BYTE) IS ‘Majorevent for the
                 holding file case’.




Contract # FA7012-05-C-A002                      39 of 47                       November 14, 2008
                                                                   Final Database Schema (FDS) (A017)



                  SYNOP        VARCHAR2 (4000 BYTE) IS ‘Synopsis for the holding file
                 case’.
                  STATUS        VARCHAR2 (4000 BYTE) IS ‘Status for the holding file
                 case’.
                  PRI        VARCHAR2 (100 BYTE) IS ‘priority for the holding file case’.
                  MDL         DATE IS ‘MDL date for the holding file case’.
                  REQUESTOR VARCHAR2(100 BYTE) IS ‘Requestor for holding file
                 case’
                  REG         VARCHAR2(100 BYTE) IS ‘Regulation type for holding file
                 case’
                  STATCODE        VARCHAR2(100 BYTE) IS ‘Status code for holding file
                 case’
                  PDATE        DATE IS ‘Recent Status date for holding file case’
                  CASEMGR         VARCHAR2(100 BYTE) IS ‘Case Manger for holding file
                 case’
                  DARCCM         VARCHAR2(100 BYTE) IS ‘Sponsor for the holding file
                 case’
                  LASTDATE       DATE IS ‘Not used in the new system’
                    COMM_NAME VARCHAR2(100 BYTE) IS ‘Committee for the holding
                 file case’
                  CASEOPEN        DATE IS ‘Open Date for the holding file case’
                  UPDATED1        DATE IS ‘Not used in the new system’
                  TOTALDAYS NUMBER IS ‘Not used in the new system’
                  ONHOLD         NUMBER IS ‘Not used in the new system’
                  ONHOLDDATE DATE IS ‘Not used in the new system’
                  CUMONHOLD NUMBER IS ‘Not used in the new system’
                  NETDAY        NUMBER IS ‘Not used in the new system’
                  ARCHIVE1       VARCHAR2(10 BYTE) IS ‘Not used in the new system’
                  PRA_APPLYS VARCHAR2(10 BYTE) IS ‘Not used in the new system’
                  PRA_RQSTDT DATE IS ‘Not used in the new system’
                  PRA_CLERDT DATE IS ‘Not used in the new system’
                  PRA_EXPRDT DATE IS ‘Not used in the new system’
                  RFA_APPLYS VARCHAR2(10 BYTE) IS ‘Not used in the new system’
                  RFA_INITIA VARCHAR2(10 BYTE) IS ‘Not used in the new system’
                  RFA_INITDT DATE IS ‘Not used in the new system’
                  RFA_FINAL VARCHAR2(10 BYTE) IS ‘Not used in the new system’


Contract # FA7012-05-C-A002                     40 of 47                        November 14, 2008
                                                               Final Database Schema (FDS) (A017)



                  RFA_FINLDT DATE IS ‘Not used in the new system’
                  RFA_CERTDT DATE IS ‘Not used in the new system’
                  FEDREG     NUMBER IS ‘Not used in the new system’
                  RULE_TYPE VARCHAR2(100 BYTE) IS ‘Rule type for the holding file
                 system’
                  CN_INTERDT DATE IS ‘Not used in the new system’
                  CN_FINALDT DATE IS ‘Not used in the new system’
                  CN_COMMENT VARCHAR2(4000 BYTE) IS ‘Not used in the new
                 system’


OMBCITE_RAW
This table is used to store the paperwork burden and history information.
                 CONTROL      VARCHAR2(100 BYTE) IS’ Not used in the new system’
                  OMBCONTROL        VARCHAR2(100 BYTE) IS ‘Paperwork burden
                 information’
                 DATETOOMB DATE IS ‘Date of the PWB case’
                 OLDBALANCE         NUMBER IS ‘Not used in the new system’
                 HRS_RQSTD NUMBER IS ‘Not used in the new system’
                 DATE_APRVD         DATE IS ‘Used in the PWB history table’
                 HRS_APRVD NUMBER IS ’Used in the PWB history table’
                 CHANGETYPE         VARCHAR2(100 BYTE) IS ‘Not used in the new
                 system’
                 NET_CHANGE         NUMBER IS ‘Used in the PWB history table’
                 PRIOR_COST NUMBER IS ‘Not used in the new system’
                 COST_PUB     NUMBER IS ‘Not used in the new system’
                 NETCHG_COS         NUMBER IS ‘Not used in the new system’
                 EXPIREDATE DATE IS ‘Used in PWB case expiration date’
                 STARTPREP DATE IS ‘Used in PWB case start date’
                 REMARKS      VARCHAR2(4000 BYTE) IS ‘Not used in the new system’
                 CASEMGR      VARCHAR2(4000 BYTE) IS ‘Case manager associated
                 with’


OMBHOLD_RAW
This table is used to compare the paperwork burden data is open or closed.
                 CONTROL      VARCHAR2(100 BYTE) IS ‘Not used in the system’

Contract # FA7012-05-C-A002                 41 of 47                        November 14, 2008
                                                               Final Database Schema (FDS) (A017)



                 PB_CONTROL           VARCHAR2(100 BYTE) IS ‘Not used in the new
                 system’
                 OMB_NUMBER           VARCHAR2(100 BYTE) IS ‘PWB Case number’
                 REGTYPE       VARCHAR2(100 BYTE)IS ‘Regulation type associated
                 with PWB case’
                 DFARS_PART          VARCHAR2(4000 BYTE) IS ‘Part number
                 associated with PWB case’
                 APRVDUPDT DATE IS ‘Not used in the new system’
                  OMB_CLOSE VARCHAR2(100 BYTE) IS ‘Used to identify whether case
                 is closes or opened’


OMBPART_RAW
This table is used to get the part number information PWB cases.
                 CONTROL       VARCHAR2(100 BYTE) IS ‘Not used in the new system’
                 OMB_NUMBER           VARCHAR2 (100 BYTE) IS ‘This PWB number.
                 REGTYPE       VARCHAR2(100 BYTE) IS ‘Regulation type associated
                 with PWB case’
                 DFARS_PART          VARCHAR2(100 BYTE) IS ‘ Part number
                 associated with PWB case’
                 OMBHRSBAL VARCHAR2(4000 BYTE) IS ‘ Not used in the new system’
                 APRVDUPDT DATE IS ‘Not used in the new system’
                  OMB_CLOSE VARCHAR2(100 BYTE) IS ‘Used to identify whether case
                 is closed or opened’


OPENCASE_RAW
This opencase_raw is used to store the non-processed information for open cases.
                 CONTROL       VARCHAR2 (100 BYTE) IS ‘Not used in the new system’.
                  DAR_DFARNO VARCHAR2(100 BYTE) IS ‘DAR_DFARNO number’
                  FAR_NO       VARCHAR2 (100 BYTE) IS ‘FAR number for the open
                 case’’.
                  CAA_NO       VARCHAR2 (100 BYTE) IS ‘CAAC number for the open
                 case’’.
                  RINNO       VARCHAR2 (100 BYTE) IS ‘RIN number for the open
                 case’’.
                  TITLE      VARCHAR2 (4000 BYTE) IS ‘Title for the open case’.
                  MAJOREVENT VARCHAR2 (4000 BYTE) IS ‘Majorevent for the open
                 case’.


Contract # FA7012-05-C-A002                   42 of 47                      November 14, 2008
                                                                 Final Database Schema (FDS) (A017)



                  SYNOP        VARCHAR2 (4000 BYTE) IS ‘Synopsis for the open case’.
                  STATUS        VARCHAR2 (4000 BYTE) IS ‘Status for the open case’.
                  PRI        VARCHAR2 (100 BYTE) IS ‘priority for the open case’.
                  MDL        DATE IS ‘MDL date for the open case’.
                  REQUESTOR VARCHAR2(100 BYTE) IS ‘Requestor for open case’
                  REG         VARCHAR2(100 BYTE) IS ‘Regulation type for open case’
                  STATCODE        VARCHAR2(100 BYTE) IS ‘Status code for open case’
                  PDATE        DATE IS ‘Recent Status date for open case’
                  CASEMGR        VARCHAR2(100 BYTE) IS ‘Case Manger for open case’
                  DARCCM         VARCHAR2(100 BYTE) IS ‘Sponsor for the open case’
                  LASTDATE       DATE IS ‘Not used in the new system’
                  COMM_NAME VARCHAR2(100 BYTE) IS ‘Committee for the open
                 case’
                  CASEOPEN        DATE IS ‘Open Date for the open case’
                  UPDATED1       DATE IS ‘Not used in the new system’
                  TOTALDAYS NUMBER IS ‘Not used in the new system’
                  ONHOLD        NUMBER IS ‘Not used in the new system’
                  ONHOLDDATE DATE IS ‘Not used in the new system’
                  CUMONHOLD NUMBER IS ‘Not used in the new system’
                  NETDAY        NUMBER IS ‘Not used in the new system’
                  ARCHIVE1       VARCHAR2(10 BYTE) IS ‘Not used in the new system’
                  PRA_APPLYS VARCHAR2(10 BYTE) IS ‘Not used in the new system’
                  PRA_RQSTDT DATE IS ‘Not used in the new system’
                  PRA_CLERDT DATE IS ‘Not used in the new system’
                  PRA_EXPRDT DATE IS ‘Not used in the new system’
                  RFA_APPLYS VARCHAR2(10 BYTE) IS ‘Not used in the new system’
                  RFA_INITIA VARCHAR2(10 BYTE) IS ‘Not used in the new system’
                  RFA_INITDT DATE IS ‘Not used in the new system’
                  RFA_FINAL VARCHAR2(10 BYTE) IS ‘Not used in the new system’
                  RFA_FINLDT DATE IS ‘Not used in the new system’
                  RFA_CERTDT DATE IS ‘Not used in the new system’
                  FEDREG        NUMBER IS ‘Not used in the new system’
                  RULE_TYPE VARCHAR2(100 BYTE) IS ‘Rule type for the open case
                 system’


Contract # FA7012-05-C-A002                    43 of 47                       November 14, 2008
                                                                 Final Database Schema (FDS) (A017)



                  CN_INTERDT DATE IS ‘Not used in the new system’
                  CN_FINALDT DATE IS ‘Not used in the new system’
                  CN_COMMENT VARCHAR2(4000 BYTE) IS ‘Not used in the new
                 system’




OPENCASE_RAW_DUMP
This table is used to store the non-processed information for open cases.
                CONTROL         VARCHAR2 (100 BYTE) IS ‘Not used in the new system’.
                  DAR_DFARNO VARCHAR2(100 BYTE) IS ‘DAR_DFARNO number’
                  FAR_NO        VARCHAR2 (100 BYTE) IS ‘FAR number for the open
                 case’’.
                  CAA_NO        VARCHAR2 (100 BYTE) IS ‘CAAC number for the open
                 case’’.
                  RINNO        VARCHAR2 (100 BYTE) IS ‘RIN number for the open
                 case’’.
                  TITLE       VARCHAR2 (4000 BYTE) IS ‘Title for the open case’.
                  MAJOREVENT VARCHAR2 (4000 BYTE) IS ‘Majorevent for the open
                 case’.
                  SYNOP        VARCHAR2 (4000 BYTE) IS ‘Synopsis for the open case’.
                  STATUS        VARCHAR2 (4000 BYTE) IS ‘Status for the open case’.
                  PRI        VARCHAR2 (100 BYTE) IS ‘priority for the open case’.
                  MDL        DATE IS ‘MDL date for the open case’.
                  REQUESTOR VARCHAR2(100 BYTE) IS ‘Requestor for open case’
                  REG         VARCHAR2(100 BYTE) IS ‘Regulation type for open case’
                  STATCODE        VARCHAR2(100 BYTE) IS ‘Status code for open case’
                  PDATE        DATE IS ‘Recent Status date for open case’
                  CASEMGR        VARCHAR2(100 BYTE) IS ‘Case Manger for open case’
                  DARCCM         VARCHAR2(100 BYTE) IS ‘Sponsor for the open case’
                  LASTDATE       DATE IS ‘Not used in the new system’
                  COMM_NAME VARCHAR2(100 BYTE) IS ‘Committee for the open
                 case’
                  CASEOPEN        DATE IS ‘Open Date for the open case’
                  UPDATED1       DATE IS ‘Not used in the new system’
                  TOTALDAYS NUMBER IS ‘Not used in the new system’



Contract # FA7012-05-C-A002                    44 of 47                       November 14, 2008
                                                                Final Database Schema (FDS) (A017)



                  ONHOLD       NUMBER IS ‘Not used in the new system’
                  ONHOLDDATE DATE IS ‘Not used in the new system’
                  CUMONHOLD NUMBER IS ‘Not used in the new system’
                  NETDAY       NUMBER IS ‘Not used in the new system’
                  ARCHIVE1      VARCHAR2(10 BYTE) IS ‘Not used in the new system’
                  PRA_APPLYS VARCHAR2(10 BYTE) IS ‘Not used in the new system’
                  PRA_RQSTDT DATE IS ‘Not used in the new system’
                  PRA_CLERDT DATE IS ‘Not used in the new system’
                  PRA_EXPRDT DATE IS ‘Not used in the new system’
                  RFA_APPLYS VARCHAR2(10 BYTE) IS ‘Not used in the new system’
                  RFA_INITIA VARCHAR2(10 BYTE) IS ‘Not used in the new system’
                  RFA_INITDT DATE IS ‘Not used in the new system’
                  RFA_FINAL VARCHAR2(10 BYTE) IS ‘Not used in the new system’
                  RFA_FINLDT DATE IS ‘Not used in the new system’
                  RFA_CERTDT DATE IS ‘Not used in the new system’
                  FEDREG       NUMBER IS ‘Not used in the new system’
                  RULE_TYPE VARCHAR2(100 BYTE) IS ‘Rule type for the open case
                 system’
                  CN_INTERDT DATE IS ‘Not used in the new system’
                  CN_FINALDT DATE IS ‘Not used in the new system’
                  CN_COMMENT VARCHAR2(4000 BYTE) IS ‘Not used in the new
                 system’


OPENCASE_RAW_MAJEVENT_SPLIT
This table is used to store the information about Major event split for Open cases.
                 CONTROL       VARCHAR2 (100 BYTE) IS ‘Not used in the new system’.
                  DAR_DFARNO VARCHAR2(100 BYTE) IS ‘DAR_DFARNO number’
                  FAR_NO       VARCHAR2 (100 BYTE) IS ‘FAR number for the open
                 case’’.
                  CAA_NO       VARCHAR2 (100 BYTE) IS ‘CAAC number for the open
                 case’’.
                  RINNO       VARCHAR2 (100 BYTE) IS ‘RIN number for the open
                 case’’.
                  TITLE      VARCHAR2 (4000 BYTE) IS ‘Title for the open case’.




Contract # FA7012-05-C-A002                   45 of 47                       November 14, 2008
                                                                 Final Database Schema (FDS) (A017)



                  MAJOREVENT VARCHAR2 (4000 BYTE) IS ‘Majorevent for the open
                 case’.
                  SYNOP        VARCHAR2 (4000 BYTE) IS ‘Synopsis for the open case’.
                  STATUS        VARCHAR2 (4000 BYTE) IS ‘Status for the open case’.
                  PRI        VARCHAR2 (100 BYTE) IS ‘priority for the open case’.
                  MDL        DATE IS ‘MDL date for the open case’.
                  REQUESTOR VARCHAR2(100 BYTE) IS ‘Requestor for open case’
                  REG         VARCHAR2(100 BYTE) IS ‘Regulation type for open case’
                  STATCODE        VARCHAR2(100 BYTE) IS ‘Status code for open case’
                  PDATE        DATE IS ‘Recent Status date for open case’
                  CASEMGR        VARCHAR2(100 BYTE) IS ‘Case Manger for open case’
                  DARCCM         VARCHAR2(100 BYTE) IS ‘Sponsor for the open case’
                  LASTDATE       DATE IS ‘Not used in the new system’
                  COMM_NAME VARCHAR2(100 BYTE) IS ‘Committee for the open
                 case’
                  CASEOPEN        DATE IS ‘Open Date for the open case’
                  UPDATED1       DATE IS ‘Not used in the new system’
                  TOTALDAYS NUMBER IS ‘Not used in the new system’
                  ONHOLD        NUMBER IS ‘Not used in the new system’
                  ONHOLDDATE DATE IS ‘Not used in the new system’
                  CUMONHOLD NUMBER IS ‘Not used in the new system’
                  NETDAY        NUMBER IS ‘Not used in the new system’
                  ARCHIVE1       VARCHAR2(10 BYTE) IS ‘Not used in the new system’
                  PRA_APPLYS VARCHAR2(10 BYTE) IS ‘Not used in the new system’
                  PRA_RQSTDT DATE IS ‘Not used in the new system’
                  PRA_CLERDT DATE IS ‘Not used in the new system’
                  PRA_EXPRDT DATE IS ‘Not used in the new system’
                  RFA_APPLYS VARCHAR2(10 BYTE) IS ‘Not used in the new system’
                  RFA_INITIA VARCHAR2(10 BYTE) IS ‘Not used in the new system’
                  RFA_INITDT DATE IS ‘Not used in the new system’
                  RFA_FINAL VARCHAR2(10 BYTE) IS ‘Not used in the new system’
                  RFA_FINLDT DATE IS ‘Not used in the new system’
                  RFA_CERTDT DATE IS ‘Not used in the new system’
                  FEDREG        NUMBER IS ‘Not used in the new system’


Contract # FA7012-05-C-A002                    46 of 47                       November 14, 2008
                                                              Final Database Schema (FDS) (A017)



                  RULE_TYPE VARCHAR2(100 BYTE) IS ‘Rule type for the open case
                 system’
                  CN_INTERDT DATE IS ‘Not used in the new system’
                  CN_FINALDT DATE IS ‘Not used in the new system’
                  CN_COMMENT VARCHAR2(4000 BYTE) IS ‘Not used in the new
                 system’
                 CASE_NO     VARCHAR2(100 BYTE) IS ‘case number for the case’
                 CASE_TYPE VARCHAR2(1000 BYTE) IS ‘Case type for the case’
                 CLOSED_FLAG        NUMBER IS ‘To determine whether the case is
                 closed or open’
                 CASE_ID     NUMBER IS ‘Auto generated number’


PART_NO_RAW
This table is used to store the part number data from the legacy system.


                 CONTROL     VARCHAR2(1000 BYTE) IS ‘Not used in the new system’
                 PART_NO     VARCHAR2(1000 BYTE) IS ‘Part number for the cases’
                 PART_INDEX VARCHAR2(1000 BYTE) IS ‘Not used in the new system’




CONCLUSION
This document reflects the legacy schema and how it relates to the new Oracle database
schema and is being used to ensure the success of the DARMIS application. Citizant’s
team had recurring meetings with the DARS Directorate and AT&L to make certain that
the client’s needs are being incorporated into the DARMIS application.




Contract # FA7012-05-C-A002                47 of 47                        November 14, 2008

								
To top