GM_PT_PERSON_VW

(SQL View)
 Index Back

Personnel summary view


SELECT DISTINCT E.OPRID , A.BUSINESS_UNIT_PC , A.PROJECT_ID , A.EMPLID , A.BUSINESS_UNIT , A.CONTRACT_NUM , A.AWARD_STATUS , A.AWARD_TYPE , A.TITLE56 , B.PROJECT_STATUS , B.PROJECT_TYPE , B.PROJDESCR , F.PLAN_PROJROLE , A.GM_ADMIN_CNTCT , A.CUST_ID , A.CUST_NAME , A.CUSTOMER_TYPE , A.JOBCODE , A.DEPTID , C.DESCR , SUM(A.PAID_AMT) , SUM(A.COMMITTED_AMT) , F.PERCENTAGE , SUM(A.EFFORT) , A.CURRENCY_CD , A.PROCESS_DTTM FROM PS_GM_PT_EFFORT A , PS_GM_PT_PROJECT B , PS_DEPT_TBL C , PS_GM_PT_ACC_LVL3 D , PSOPRDEFN E , PS_PROJECT_TEAM F WHERE D.PERSONNEL_FLG = 'Y' AND (D.END_DT >= %CurrentDateIn OR D.END_DT IS NULL) AND E.EMPLID = D.EMPLID AND A.BUSINESS_UNIT_PC = D.BUSINESS_UNIT_PC AND A.PROJECT_ID = D.PROJECT_ID AND B.BUSINESS_UNIT_PC = A.BUSINESS_UNIT_PC AND B.PROJECT_ID = A.PROJECT_ID AND F.BUSINESS_UNIT = A.BUSINESS_UNIT_PC AND F.PROJECT_ID = A.PROJECT_ID AND F.TEAM_MEMBER_CLASS = 'L' AND F.TEAM_MEMBER_IND = 'E' AND F.TEAM_MEMBER = A.EMPLID AND C.DEPTID = A.DEPTID AND C.SETID = ( SELECT CTL.SETID FROM PS_SET_CNTRL_REC CTL WHERE CTL.SETCNTRLVALUE = A.BUSINESS_UNIT_PC AND CTL.RECNAME = 'DEPT_TBL') AND C.EFFDT = ( SELECT MAX(C1.EFFDT) FROM PS_DEPT_TBL C1 WHERE C1.SETID = C.SETID AND C1.DEPTID = C.DEPTID AND C1.EFFDT <= %CurrentDateIn AND C1.EFF_STATUS = 'A') GROUP BY E.OPRID , A.BUSINESS_UNIT_PC , A.PROJECT_ID , A.EMPLID , A.BUSINESS_UNIT , A.CONTRACT_NUM , A.AWARD_STATUS , A.AWARD_TYPE , A.TITLE56 , B.PROJECT_STATUS , B.PROJECT_TYPE , B.PROJDESCR , F.PLAN_PROJROLE , A.GM_ADMIN_CNTCT , A.CUST_ID, A.CUST_NAME , A.CUSTOMER_TYPE , A.JOBCODE , A.DEPTID , C.DESCR, F.PERCENTAGE, A.CURRENCY_CD, A.PROCESS_DTTM

# 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_PC Character(5) VARCHAR2(5) NOT NULL PC Business Unit

Prompt Table: SP_BU_PC_NONVW

3 PROJECT_ID Character(15) VARCHAR2(15) NOT NULL Project Id ChartField

Prompt Table: SP_PROJ_NONVW

4 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID

Prompt Table: GM_PERSONAL1_VW

5 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
6 CONTRACT_NUM Character(25) VARCHAR2(25) NOT NULL Contract
7 AWARD_STATUS Character(3) VARCHAR2(3) NOT NULL Award Status
ACP=Accepted
CLS=Closed
HOL=Holding
NEG=Under Negotiation
PEN=Pending
PRE=Pre-Award
REF=Refused
TER=Terminated
TRA=Transfer
WTH=Withdrawn
8 AWARD_TYPE Character(3) VARCHAR2(3) NOT NULL Award Type
BOA=Basic Ordering Agreement
CLI=Clinical Trial
CON=Contract
CP=Cooperative Agreement
FDP=FDP
FIX=Fixed
GR=Grant
IPA=IPA
LIC=License
LN=Loan
MOU=MOU
MTA=MTA
NDA=NDA
OTH=Other
PA=Patent Agreement
PO=Purchase Order
SB=SubContract
TO=Task Order
9 TITLE56 Character(56) VARCHAR2(56) NOT NULL TITLE56
10 PROJECT_STATUS Character(1) VARCHAR2(1) NOT NULL Project Status
B=Budgeted
C=Closed
H=Hold
O=Open
P=Proposed
11 PROJECT_TYPE Character(5) VARCHAR2(5) NOT NULL Project Type
A=Activity
G=Grant
H=Phase
P=Project
S=Segment
12 PROJDESCR Character(30) VARCHAR2(30) NOT NULL Project Name
13 PROJ_ROLE Character(15) VARCHAR2(15) NOT NULL Project Role
14 GM_ADMIN_CNTCT Character(11) VARCHAR2(11) NOT NULL Grant Administrator
15 CUST_ID Character(15) VARCHAR2(15) NOT NULL Uniquely identifies an organizational entity that purchases goods or services from the enterprise. Represents the sold-to customer which is the customer organization that places orders. The CUST_ID may or may not be the same as the identifiers for the bill-to and ship-to locations.
16 CUST_NAME Character(40) VARCHAR2(40) NOT NULL Name
17 CUSTOMER_TYPE Character(1) VARCHAR2(1) NOT NULL Customer Type
1=User 1
2=User 2
3=User 3
4=User 4
F=Foreign
M=Commercial
N=Consumer
S=State and Local
X=Excluded Foreign Customer
18 JOBCODE Character(6) VARCHAR2(6) NOT NULL Job Code

Prompt Table: JOBCODE_TBL
Set Control Field: BUSINESS_UNIT_PC

19 DEPTID Character(10) VARCHAR2(10) NOT NULL Department
20 DEPTNAME Character(30) VARCHAR2(30) NOT NULL Department
21 PAID_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Paid Amount
22 COMMITTED_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Committed Assets
23 EFFORT Number(13,2) DECIMAL(12,2) NOT NULL Effort
24 EFFORT1 Number(13,2) DECIMAL(12,2) NOT NULL Effort
25 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code
26 PROCESS_DTTM DateTime(26) TIMESTAMP Process Date Time