PROJ_TEAM_VW2 (SQL View)	Index Back
Project Team Members View View of Project Team "labor" members.

PROJ_TEAM_VW2

(SQL View)

Project Team Members View

View of Project Team "labor" members.

SELECT DISTINCT PT.BUSINESS_UNIT , PT.PROJECT_ID , PT.TEAM_MEMBER_CLASS , PT.TEAM_MEMBER_IND , PT.TEAM_MEMBER , CASE WHEN EMAIL.EMAIL_ADDR IS NULL THEN OPRDEFN.EMAILID ELSE EMAIL.EMAIL_ADDR END , PT.PLAN_PROJROLE , PT.SOURCE_REQ , PT.TEAM_SCHED_CNT , PT.UNIT_OF_MEASURE , PT.PGM_RATE_TYPE , PT.COST_RATE , PT.CURRENCY_CD , PT.PC_BUDGET_ITEM , PT.PLAN_STARTDT , PT.PLAN_ENDDT , PT.BILL_RATE , PT.BILL_RATE_TYPE_1 , PT.BILL_CURRENCY_CD , PT.RESOURCE_STS , PT.BUSINESS_UNIT_AM , PT.ASSET_TYPE , PT.ASSET_SUBTYPE , PT.MODEL , PT.MFG_ID , PT.DEF_CRATE_COST , PT.DEF_CRATE_BILL , E.FIRST_NAME %Concat ' ' %Concat E.LAST_NAME , E.PHONE , CASE WHEN MGR.PROJECT_MANAGER <> ' ' THEN 'Y' ELSE 'N' END FROM PS_PROJECT_TEAM PT LEFT OUTER JOIN PS_PERSONAL_DATA E ON PT.TEAM_MEMBER = E.EMPLID 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 LEFT OUTER JOIN PSOPRDEFN OPRDEFN ON OPRDEFN.EMPLID = PT.TEAM_MEMBER AND OPRDEFN.ACCTLOCK = 0 WHERE PT.TEAM_MEMBER_CLASS = 'L' AND PT.RESOURCE_STS NOT IN ('RE', 'CA') 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 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
Default Value: PROJECT.BUSINESS_UNIT
Prompt Table: SP_BU_PC_NONVW

2 PROJECT_ID Character(15) VARCHAR2(15) NOT NULL Project Id ChartField
Prompt Table: PROJECT_ALL_VW

3 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
Default Value: L

4 TEAM_MEMBER_IND Character(1) VARCHAR2(1) NOT NULL Team Member Indicator
E=Employee
G=Generic Resource
N=Not Applicable
Default Value: E

5 TEAM_MEMBER Character(30) VARCHAR2(30) NOT NULL Team Member
Prompt Table: %EDITTABLE

6 EMAILID Character(70) VARCHAR2(70) NOT NULL A user's E-mail address

7 PLAN_PROJROLE Character(15) VARCHAR2(15) NOT NULL Requirement Project Role
Prompt Table: PROJ_ROLE

8 SOURCE_REQ Character(25) VARCHAR2(25) NOT NULL Source Requirement ID

9 TEAM_SCHED_CNT Number(4,0) SMALLINT NOT NULL Project Team Count
Default Value: 1

10 UNIT_OF_MEASURE Character(3) VARCHAR2(3) NOT NULL Used on an approval rule set.
MHR=Muti Hourly
PER=Percentage
SQF=Square Footage
Default Value: OPR_DEF_TBL_PC.UNIT_OF_MEASURE
Prompt Table: %EDIT_UOM

11 PGM_RATE_TYPE Character(1) VARCHAR2(1) NOT NULL 06/15/2004 FS - Changed eff. dates to 1/1/1900 from 1/1/2000
C=Override Rate
E=Employee Rate
J=Jobcode Rate
R=Project Role Rate
X=Custom Rate

12 COST_RATE Number(27,3) DECIMAL(26,3) NOT NULL Cost Rate
Default Value: 0

13 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code
Default Value: PROJECT.CURRENCY_CD
Prompt Table: CURRENCY_CD_TBL

14 PC_BUDGET_ITEM Character(15) VARCHAR2(15) NOT NULL Project Budgeting: Budget Item 11/15/2004- FS - Added label COST_BUDGET_ITEM
Prompt Table: %EDIT_BUD_CAT

15 PLAN_STARTDT Date(10) DATE Planned Start Date
Default Value: PROJECT.START_DT

16 PLAN_ENDDT Date(10) DATE Planned End Date
Default Value: PROJECT.END_DT

17 BILL_RATE Number(27,3) DECIMAL(26,3) NOT NULL Billing Rate
Default Value: 0

18 BILL_RATE_TYPE_1 Character(1) VARCHAR2(1) NOT NULL 06/03/2004 - PR_FS_B005 Estimanting Labor PRL/ARL Changes
C=Override Rate
E=Employee Rate
J=Jobcode Rate
R=Project Role Rate
X=Custom Rate

19 BILL_CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Bill Currency Code
Default Value: PROJECT.CURRENCY_CD
Prompt Table: CURRENCY_CD_TBL

20 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
Default Value: CO

21 BUSINESS_UNIT_AM Character(5) VARCHAR2(5) NOT NULL AM Business Unit

22 ASSET_TYPE Character(3) VARCHAR2(3) NOT NULL Asset Type
010=IT Hardware
020=IT Software
040=Equipment
050=Property
060=Fleet
070=Machinery
080=Furniture
090=Facility
100=Intangible
200=Asset Retirement Obligations
999=Other

23 ASSET_SUBTYPE Character(15) VARCHAR2(15) NOT NULL Asset Subtype
Prompt Table: AM_SUBTYPE

24 MODEL Character(30) VARCHAR2(30) NOT NULL Model
Prompt Table: MFG_MODEL

25 MFG_ID Character(50) VARCHAR2(50) NOT NULL Manufacturer ID
Prompt Table: MANUFACTURER

26 DEF_CRATE_COST Character(15) VARCHAR2(15) NOT NULL The default Custom Rate Name for costing rate
Prompt Table: PC_CR_PRL_VW

27 DEF_CRATE_BILL Character(15) VARCHAR2(15) NOT NULL The default Custom Rate Name for billing rate
Prompt Table: PC_CR_PRL_VW

28 PERSON_NAME Character(100) VARCHAR2(100) NOT NULL Name

29 PHONE Character(24) VARCHAR2(24) NOT NULL Telephone

30 PROJ_MGR_FLAG Character(1) VARCHAR2(1) NOT NULL Project Manager Review

#	PeopleSoft Field Name	PeopleSoft Field Type	Database Column Type	Description
1	BUSINESS_UNIT	Character(5)	VARCHAR2(5) NOT NULL	Business Unit Default Value: PROJECT.BUSINESS_UNIT Prompt Table: SP_BU_PC_NONVW
2	PROJECT_ID	Character(15)	VARCHAR2(15) NOT NULL	Project Id ChartField Prompt Table: PROJECT_ALL_VW
3	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 Default Value: L
4	TEAM_MEMBER_IND	Character(1)	VARCHAR2(1) NOT NULL	Team Member Indicator E=Employee G=Generic Resource N=Not Applicable Default Value: E
5	TEAM_MEMBER	Character(30)	VARCHAR2(30) NOT NULL	Team Member Prompt Table: %EDITTABLE
6	EMAILID	Character(70)	VARCHAR2(70) NOT NULL	A user's E-mail address
7	PLAN_PROJROLE	Character(15)	VARCHAR2(15) NOT NULL	Requirement Project Role Prompt Table: PROJ_ROLE
8	SOURCE_REQ	Character(25)	VARCHAR2(25) NOT NULL	Source Requirement ID
9	TEAM_SCHED_CNT	Number(4,0)	SMALLINT NOT NULL	Project Team Count Default Value: 1
10	UNIT_OF_MEASURE	Character(3)	VARCHAR2(3) NOT NULL	Used on an approval rule set. MHR=Muti Hourly PER=Percentage SQF=Square Footage Default Value: OPR_DEF_TBL_PC.UNIT_OF_MEASURE Prompt Table: %EDIT_UOM
11	PGM_RATE_TYPE	Character(1)	VARCHAR2(1) NOT NULL	06/15/2004 FS - Changed eff. dates to 1/1/1900 from 1/1/2000 C=Override Rate E=Employee Rate J=Jobcode Rate R=Project Role Rate X=Custom Rate
12	COST_RATE	Number(27,3)	DECIMAL(26,3) NOT NULL	Cost Rate Default Value: 0
13	CURRENCY_CD	Character(3)	VARCHAR2(3) NOT NULL	Currency Code Default Value: PROJECT.CURRENCY_CD Prompt Table: CURRENCY_CD_TBL
14	PC_BUDGET_ITEM	Character(15)	VARCHAR2(15) NOT NULL	Project Budgeting: Budget Item 11/15/2004- FS - Added label COST_BUDGET_ITEM Prompt Table: %EDIT_BUD_CAT
15	PLAN_STARTDT	Date(10)	DATE	Planned Start Date Default Value: PROJECT.START_DT
16	PLAN_ENDDT	Date(10)	DATE	Planned End Date Default Value: PROJECT.END_DT
17	BILL_RATE	Number(27,3)	DECIMAL(26,3) NOT NULL	Billing Rate Default Value: 0
18	BILL_RATE_TYPE_1	Character(1)	VARCHAR2(1) NOT NULL	06/03/2004 - PR_FS_B005 Estimanting Labor PRL/ARL Changes C=Override Rate E=Employee Rate J=Jobcode Rate R=Project Role Rate X=Custom Rate
19	BILL_CURRENCY_CD	Character(3)	VARCHAR2(3) NOT NULL	Bill Currency Code Default Value: PROJECT.CURRENCY_CD Prompt Table: CURRENCY_CD_TBL
20	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 Default Value: CO
21	BUSINESS_UNIT_AM	Character(5)	VARCHAR2(5) NOT NULL	AM Business Unit
22	ASSET_TYPE	Character(3)	VARCHAR2(3) NOT NULL	Asset Type 010=IT Hardware 020=IT Software 040=Equipment 050=Property 060=Fleet 070=Machinery 080=Furniture 090=Facility 100=Intangible 200=Asset Retirement Obligations 999=Other
23	ASSET_SUBTYPE	Character(15)	VARCHAR2(15) NOT NULL	Asset Subtype Prompt Table: AM_SUBTYPE
24	MODEL	Character(30)	VARCHAR2(30) NOT NULL	Model Prompt Table: MFG_MODEL
25	MFG_ID	Character(50)	VARCHAR2(50) NOT NULL	Manufacturer ID Prompt Table: MANUFACTURER
26	DEF_CRATE_COST	Character(15)	VARCHAR2(15) NOT NULL	The default Custom Rate Name for costing rate Prompt Table: PC_CR_PRL_VW
27	DEF_CRATE_BILL	Character(15)	VARCHAR2(15) NOT NULL	The default Custom Rate Name for billing rate Prompt Table: PC_CR_PRL_VW
28	PERSON_NAME	Character(100)	VARCHAR2(100) NOT NULL	Name
29	PHONE	Character(24)	VARCHAR2(24) NOT NULL	Telephone
30	PROJ_MGR_FLAG	Character(1)	VARCHAR2(1) NOT NULL	Project Manager Review