V_PEOPLE_ROLES - Projects database

Projects database documentation

- Projects database

Documentation	Projects database
Name	V_PEOPLE_ROLES

Columns

	Name	Data type
1	PROJECT_ROLE_ID	NUMBER
2	PROJECT_ID	NUMBER
3	PERSON_ID	NUMBER
4	ROLE_TYPE_ID	NUMBER
5	ROLE_NAME	VARCHAR2(50 BYTE)
6	PERSON_NAME	VARCHAR2(201 BYTE)
7	DEPARTMENT_NAME	VARCHAR2(100 BYTE)
8	VALID_FROM	DATE
9	VALID_TO	DATE
10	CREATED_DATE	DATE
11	CREATED_BY	NUMBER
12	MODIFIED_DATE	DATE
13	MODIFIED_BY	NUMBER

Uses

Name
V_PEOPLE_ROLES
DEPARTMENTS
PEOPLE
PROJECT_ROLES

Script

SELECT r.PROJECT_ROLE_ID, r.PROJECT_ID, r.PERSON_ID, r.ROLE_TYPE_ID, r.NAME AS ROLE_NAME, p.first_name \|\| ' ' \|\| p.last_name as PERSON_NAME, d.name as department_name, r.VALID_FROM, r.VALID_TO, r.CREATED_DATE, r.CREATED_BY, r.MODIFIED_DATE, r.MODIFIED_BY FROM PROJECT_ROLES r INNER JOIN PEOPLE p ON r.PERSON_ID = p.PERSON_ID LEFT OUTER JOIN DEPARTMENTS d ON p.DEPARTMENT_ID = d.DEPARTMENT_ID

SELECT r.PROJECT_ROLE_ID,
  r.PROJECT_ID,
  r.PERSON_ID,
  r.ROLE_TYPE_ID,
  r.NAME AS ROLE_NAME,
  p.first_name || ' ' || p.last_name as PERSON_NAME,
  d.name as department_name,
  r.VALID_FROM,
  r.VALID_TO,
  r.CREATED_DATE,
  r.CREATED_BY,
  r.MODIFIED_DATE,
  r.MODIFIED_BY
FROM PROJECT_ROLES r 
  INNER JOIN PEOPLE p
    ON r.PERSON_ID = p.PERSON_ID
  LEFT OUTER JOIN DEPARTMENTS d
    ON p.DEPARTMENT_ID = d.DEPARTMENT_ID

Exported: 2019-02-04 22:52, Last imported: 2019-02-03 01:50