CONTRACT_NUMBUSINESS_UNIT_PCPROJECT_IDA=Asset
L=Labor
M=Material
O=Other
E=Employee
G=Generic Resource
N=Not Applicable
TEAM_MEMBERAL=Allocated
CA=Cancelled
CM=Committed
CO=Considered
CP=Completed
RE=Rejected
RQ=Requested
GM_AWD_TEAM_VW(SQL View) |
Index Back |
|---|---|
Award Team ViewGrants Award Team view |
| SELECT AWD.CONTRACT_NUM , 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 , B.DESCR , PT.PLAN_STARTDT , PT.PLAN_ENDDT , PT.RESOURCE_STS , EMAIL.EMAIL_ADDR , PHONE.PHONE , JOB.DEPTID , PHOTO.EMPLOYEE_PHOTO , PHOTO.PSIMAGEVER FROM PS_GM_AWARD AWD , PS_GM_AWD_PROJT_VW VW , PS_PROJECT PROJ , PS_PROJ_ROLE B , PS_PROJECT_TEAM PT LEFT OUTER JOIN PS_PERSONAL_DATA E ON E.EMPLID = PT.TEAM_MEMBER LEFT OUTER JOIN PS_GM_JOB_DEPT_VW JOB ON JOB.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_PERSONAL_PHONE PHONE ON PHONE.EMPLID = E.EMPLID AND PHONE.PHONE_TYPE ='BUSN' LEFT OUTER JOIN PS_EMPL_PHOTO PHOTO ON PHOTO.EMPLID = PT.TEAM_MEMBER AND PHOTO.PHOTO_SIZENAME='PAGE' WHERE AWD.CONTRACT_NUM = VW.CONTRACT_NUM AND VW.PROJECT_ID = PT.PROJECT_ID AND PT.TEAM_MEMBER_CLASS = 'L' AND PT.TEAM_MEMBER_IND IN ('E', 'G') AND PROJ.PROJECT_ID = VW.PROJECT_ID AND PROJ.BUSINESS_UNIT = VW.BUSINESS_UNIT_PC AND B.PROJ_ROLE = PT.PLAN_PROJROLE AND PT.RESOURCE_STS NOT IN ('RE', 'CA') AND B.SETID = ( SELECT C.SETID FROM PS_SET_CNTRL_REC C WHERE C.RECNAME = 'PROJ_ROLE' AND C.SETCNTRLVALUE = PT.BUSINESS_UNIT) |
| # | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
|---|---|---|---|---|
| 1 | CONTRACT_NUM |
Character(25) | VARCHAR2(25) NOT NULL | Contract |
| 2 | BUSINESS_UNIT_PC |
Character(5) | VARCHAR2(5) NOT NULL | PC Business Unit |
| 3 | PROJECT_ID |
Character(15) | VARCHAR2(15) NOT NULL | Project Id ChartField |
| 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 |
| 7 | PERSON_NAME | Character(100) | VARCHAR2(100) NOT NULL | Name |
| 8 | PLAN_PROJROLE | Character(15) | VARCHAR2(15) NOT NULL | Requirement Project Role |
| 9 | PROJ_ROLE_DESCR | Character(30) | VARCHAR2(30) NOT NULL | Project Role |
| 10 | PLAN_STARTDT | Date(10) | DATE | Planned Start Date |
| 11 | PLAN_ENDDT | Date(10) | DATE | Planned End Date |
| 12 | 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 |
| 13 | EMAIL_ADDR | Character(70) | VARCHAR2(70) NOT NULL | Email Address |
| 14 | PHONE | Character(24) | VARCHAR2(24) NOT NULL | Telephone |
| 15 | DEPTID | Character(10) | VARCHAR2(10) NOT NULL | Department |
| 16 | EMPLOYEE_PHOTO | Image / Attachment(256) | VARCHAR2(256) | Employee Photograph |
| 17 | PSIMAGEVER | Number(10,0) | DECIMAL(10) NOT NULL | Image Version |