PROJECTS
| Documentation | Projects database |
| Name | PROJECTS |
Table holds all projects carried out by a company. This includes:
- Contracts,
- Product development,
- R&D and,
- other internal projects.
Sources
New projects are created in following ways:
- Projects form in Projects Management Application. Source = 'PMA'
- Projects interface which is used to insert projects from:
- CRM for new contracts. Source = 'CRM'
- ERP/Maintenance module for facility maintenance projects. Source = 'MNT'
- Migrated from old system prior to 2012 implementation of PMA. Source = 'OLD'
Lifecycle
Projects are regarder as open as long as they are not closed, i.e. in "CLOSED" status.
Calculations
Project start:
Whichever is not null in this order: ACTUAL_BEGIN, EXPECTED_BEGIN, CREATED_DATE
Stadard criteria
Open projects
STATUS_ID -> DICTIONARY d
where d.LABEL not like 'Closed%'
Known issues
- Projects before 2008 have most of descriptive fields blank or incorrect (e.g. manager, customer, market)
- Project expected begin and end are rarely updated and expected begin can be in the past for long even if project still didn't start
Columns
| Key | Name | Data type | Null | Attributes | References | Description | ||
|---|---|---|---|---|---|---|---|---|
| 1 | PROJECT_ID | NUMBER | ||||||
| 2 | PROJECT_NUMBER | VARCHAR2(25 CHAR) | ||||||
| 3 | PROGRAM_ID | NUMBER | PROGRAMS | |||||
| References: PROGRAMS | ||||||||
| 4 | NAME | VARCHAR2(500 CHAR) | ||||||
| 5 | DESCRIPTION | VARCHAR2(4000 CHAR) | ||||||
| 6 | OBJECTIVES | VARCHAR2(4000 CHAR) | ||||||
| 7 | SCOPE | VARCHAR2(4000 CHAR) | ||||||
| 8 | TYPE_ID | NUMBER | DICTIONARY | |||||
| References: DICTIONARY | ||||||||
| 9 | STATUS_ID | NUMBER | STATUSES | |||||
| References: STATUSES | ||||||||
| 10 | ORG_ID | NUMBER | DEPARTMENTS | |||||
| References: DEPARTMENTS | ||||||||
| 11 | MNGR_ID | NUMBER | PEOPLE | |||||
| References: PEOPLE | ||||||||
| 12 | EXPECTED_BEGIN | DATE | ||||||
| 13 | EXPECTED_END | DATE | ||||||
| 14 | ACTUAL_BEGIN | DATE | ||||||
| 15 | ACTUAL_END | DATE | ||||||
| 16 | CUST_ID | NUMBER | COMPANIES | |||||
| References: COMPANIES | ||||||||
| 17 | CUST_MNGR_ID | NUMBER | PEOPLE | |||||
| References: PEOPLE | ||||||||
| 18 | CREATED_DATE | DATE | Default: SYSDATE | |||||
|
Default:
SYSDATE |
||||||||
| 19 | CREATED_BY | NUMBER | ||||||
| 20 | MODIFIED_DATE | DATE | Default: SYSDATE | |||||
|
Default:
SYSDATE |
||||||||
| 21 | MODIFIED_BY | NUMBER | ||||||
| 22 | GEOGRAPHY_ID | NUMBER | ||||||
| 23 | SOURCE | VARCHAR2(10 BYTE) | ||||||
Relations
| Foreign table | Primary table | Join | Title / Name / Description | |
|---|---|---|---|---|
| PROJECTS | COMPANIES | PROJECTS.CUST_ID = COMPANIES.COMPANY_ID | FK_PROJECTS_CUSTOMER | |
|
PROJECTS.CUST_ID = COMPANIES.COMPANY_ID Name: FK_PROJECTS_CUSTOMER |
||||
| PROJECTS | DEPARTMENTS | PROJECTS.ORG_ID = DEPARTMENTS.DEPARTMENT_ID | FK_PROJECTS_ORG | |
|
PROJECTS.ORG_ID = DEPARTMENTS.DEPARTMENT_ID Name: FK_PROJECTS_ORG |
||||
| PROJECTS | DICTIONARY | PROJECTS.TYPE_ID = DICTIONARY.DICT_VAL_ID | FK_PROJECTS_TYPE | |
|
PROJECTS.TYPE_ID = DICTIONARY.DICT_VAL_ID Name: FK_PROJECTS_TYPE |
||||
| PROJECTS | PEOPLE | PROJECTS.CUST_MNGR_ID = PEOPLE.PERSON_ID | FK_PROJECTS_CUST_MGR | |
|
PROJECTS.CUST_MNGR_ID = PEOPLE.PERSON_ID Name: FK_PROJECTS_CUST_MGR |
||||
| PROJECTS | PEOPLE | PROJECTS.MNGR_ID = PEOPLE.PERSON_ID | FK_PROJECTS_MANAGER | |
|
PROJECTS.MNGR_ID = PEOPLE.PERSON_ID Name: FK_PROJECTS_MANAGER |
||||
| PROJECTS | PROGRAMS | PROJECTS.PROGRAM_ID = PROGRAMS.PROGRAM_ID | FK_PROJECTS_PROGRAM | |
|
PROJECTS.PROGRAM_ID = PROGRAMS.PROGRAM_ID Name: FK_PROJECTS_PROGRAM |
||||
| PROJECTS | STATUSES | PROJECTS.STATUS_ID = STATUSES.STATUS_ID | FK_PROJECTS_STATUS | |
|
PROJECTS.STATUS_ID = STATUSES.STATUS_ID Name: FK_PROJECTS_STATUS |
||||
| ACTIVITIES | PROJECTS | ACTIVITIES.PROJECT_ID = PROJECTS.PROJECT_ID | FK_ACTIVITIES_PROJECT | |
|
ACTIVITIES.PROJECT_ID = PROJECTS.PROJECT_ID Name: FK_ACTIVITIES_PROJECT |
||||
| PROJECT_ROLES | PROJECTS | PROJECT_ROLES.PROJECT_ID = PROJECTS.PROJECT_ID | FK_PROJECT_ROLES_PROJECT | |
|
PROJECT_ROLES.PROJECT_ID = PROJECTS.PROJECT_ID Name: FK_PROJECT_ROLES_PROJECT |
||||
| WBS | PROJECTS | WBS.PROJECT_ID = PROJECTS.PROJECT_ID | FK_WBS_PROJECT | |
|
WBS.PROJECT_ID = PROJECTS.PROJECT_ID Name: FK_WBS_PROJECT |
||||
Unique keys
| Key name | Columns | Description | |
|---|---|---|---|
| PK_PROJECTS | PROJECT_ID | ||
| UK_PROJECTS_NAME | NAME, ORG_ID |
Uses
| Name |
|---|
|
PROJECTS
|
Used by
| Name |
|---|
|
PROJECTS
|
Exported: 2019-02-04 22:52, Last imported: 2019-02-03 01:50