PC_PROJ_TEAM_VW(SQL View) |
Index Back |
|---|---|
Project Team Members ViewView of Project Team members except resource status in (cancelled, rejected). |
| SELECT B.OPRID , PT.BUSINESS_UNIT , PT.PROJECT_ID , PT.TEAM_MEMBER_CLASS , PT.TEAM_MEMBER_IND , PT.TEAM_MEMBER , E.FIRST_NAME %Concat ' ' %Concat E.LAST_NAME , PT.PLAN_PROJROLE , PT.PLAN_STARTDT , PT.PLAN_ENDDT , PT.RESOURCE_STS , EMAIL.EMAIL_ADDR , E.PHONE , CASE WHEN MGR.PROJECT_MANAGER <> ' ' THEN 'Y' ELSE 'N' END , CASE WHEN PT.TEAM_MEMBER_IND = 'E' THEN 1 ELSE 0 END , CASE WHEN PT.TEAM_MEMBER_IND = 'G' THEN 1 ELSE 0 END , 1 FROM PS_PROJECT_TEAM PT LEFT OUTER JOIN PS_PERSONAL_DATA E ON E.EMPLID = PT.TEAM_MEMBER LEFT OUTER JOIN PS_EMAIL_ADDRESSES EMAIL ON E.EMPLID = EMAIL.EMPLID AND EMAIL.E_ADDR_TYPE = 'BUSN' LEFT OUTER JOIN PS_PROJECT_MGR MGR ON MGR.BUSINESS_UNIT = PT.BUSINESS_UNIT AND MGR.PROJECT_ID = PT.PROJECT_ID AND MGR.PROJECT_MANAGER = PT.TEAM_MEMBER , PS_PC_OPRID_PRJACT B , PS_OPR_DEF_TBL_PC OPR WHERE PT.BUSINESS_UNIT = B.BUSINESS_UNIT AND PT.PROJECT_ID = B.PROJECT_ID AND PT.TEAM_MEMBER_CLASS = 'L' AND PT.TEAM_MEMBER_IND IN ('E', 'G') AND PT.RESOURCE_STS <> (CASE WHEN OPR.ACTIVE_RES_ONLY = 'Y' THEN 'CP' ELSE ' ' END) AND B.OPRID = OPR.OPRID AND (MGR.EFFDT IS NULL OR MGR.EFFDT=( SELECT MAX(EFFDT) FROM PS_PROJECT_MGR A_ED WHERE A_ED.BUSINESS_UNIT = MGR.BUSINESS_UNIT AND A_ED.PROJECT_ID = MGR.PROJECT_ID AND A_ED.EFFDT <= %CurrentDateIn)) |
| # | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
|---|---|---|---|---|
| 1 |  OPRID |
Character(30) | VARCHAR2(30) NOT NULL | A user's ID (see PSOPRDEFN). |
| 2 | BUSINESS_UNIT |
Character(5) | VARCHAR2(5) NOT NULL |
Business Unit
Prompt Table: SP_BU_PC_NONVW |
| 3 | PROJECT_ID |
Character(15) | VARCHAR2(15) NOT NULL |
Project Id ChartField
Prompt Table: PROJECT_VW |
| 4 | TEAM_MEMBER_CLASS |
Character(1) | VARCHAR2(1) NOT NULL |
05/26/2004 - FS
Translate Value "O" added.
A=Asset L=Labor M=Material O=Other |
| 5 | TEAM_MEMBER_IND |
Character(1) | VARCHAR2(1) NOT NULL |
Team Member Indicator
E=Employee G=Generic Resource N=Not Applicable |
| 6 | TEAM_MEMBER |
Character(30) | VARCHAR2(30) NOT NULL |
Team Member
Prompt Table: PROJECT_TEAM_VW |
| 7 | PERSON_NAME | Character(100) | VARCHAR2(100) NOT NULL | Name |
| 8 | PROJ_ROLE | Character(15) | VARCHAR2(15) NOT NULL |
Project Role
Prompt Table: PROJ_ROLE |
| 9 | START_DT | Date(10) | DATE | Start Date |
| 10 | END_DT | Date(10) | DATE | End Date |
| 11 | RESOURCE_STS | Character(2) | VARCHAR2(2) NOT NULL |
11/15/2004 - New field added.
AL=Allocated CA=Cancelled CM=Committed CO=Considered CP=Completed RE=Rejected RQ=Requested |
| 12 | EMAILID | Character(70) | VARCHAR2(70) NOT NULL | A user's E-mail address |
| 13 | PHONE | Character(24) | VARCHAR2(24) NOT NULL | Telephone |
| 14 | PROJ_MGR_FLAG | Character(1) | VARCHAR2(1) NOT NULL | Project Manager Review |
| 15 | PC_COUNT1 | Number(10,0) | DECIMAL(10) NOT NULL | PC_COUNT1 |
| 16 | PC_COUNT2 | Number(10,0) | DECIMAL(10) NOT NULL | PC_COUNT2 |
| 17 | PC_COUNT3 | Number(10,0) | DECIMAL(10) NOT NULL | PC_COUNT3 |