GM_PT_PER_S_VW

(SQL View)
 Index Back

Personnel summary view


SELECT DISTINCT E.OPRID , A.BUSINESS_UNIT_PC , A.PROJECT_ID , A.ACTIVITY_ID , A.EMPLID , A.BUSINESS_UNIT , A.CONTRACT_NUM , A.BUDGET_PERIOD_GM , A.TRANSACTION_DT , A.ACCOUNTING_DT , A.AWARD_STATUS , A.AWARD_TYPE , A.TITLE56 , B.PROJECT_STATUS , B.PROJECT_TYPE , B.PROJDESCR , F.PLAN_PROJROLE , A.DEPTID , %subrec(CF12_AN_SBR, A) , A.GM_ADMIN_CNTCT , A.CUST_NAME , A.CUSTOMER_TYPE , A.JOBCODE , A.ACCOUNT , A.RESOURCE_TYPE , A.RESOURCE_CATEGORY , A.RESOURCE_SUB_CAT , SUM(A.PAID_AMT) , SUM(A.COMMITTED_AMT) , A.CURRENCY_CD , SUM(A.EFFORT) FROM PS_GM_PT_EFFORT A , PS_GM_PT_PROJECT B , 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 A.SALARY_TYPE = 'SPN' 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 GROUP BY E.OPRID , A.BUSINESS_UNIT_PC , A.PROJECT_ID , A.ACTIVITY_ID , A.EMPLID , A.BUSINESS_UNIT, A.CONTRACT_NUM , A.BUDGET_PERIOD_GM , A.TRANSACTION_DT , A.ACCOUNTING_DT , A.AWARD_STATUS , A.AWARD_TYPE , A.TITLE56 , B.PROJECT_STATUS , B.PROJECT_TYPE , B.PROJDESCR , F.PLAN_PROJROLE , A.DEPTID , A.GM_ADMIN_CNTCT , A.CUST_NAME , A.CUSTOMER_TYPE , A.JOBCODE , A.ACCOUNT , %subrec(CF12_AN_SBR, A), A.RESOURCE_TYPE , A.RESOURCE_CATEGORY , A.RESOURCE_SUB_CAT , A.CURRENCY_CD

# 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 ACTIVITY_ID Character(15) VARCHAR2(15) NOT NULL Activity ID
5 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID

Prompt Table: GM_PERSONAL1_VW

6 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
7 CONTRACT_NUM Character(25) VARCHAR2(25) NOT NULL Contract
8 BUDGET_PERIOD_GM Number(3,0) SMALLINT NOT NULL BUDGET_PERIOD_GM
9 TRANSACTION_DT Date(10) DATE Transaction date (often used as trade date)
10 ACCOUNTING_DT Date(10) DATE The accounting entry construction date for a given transaction (a generic field that crosses multiple
11 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
12 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
13 TITLE56 Character(56) VARCHAR2(56) NOT NULL TITLE56
14 PROJECT_STATUS Character(1) VARCHAR2(1) NOT NULL Project Status
B=Budgeted
C=Closed
H=Hold
O=Open
P=Proposed
15 PROJECT_TYPE Character(5) VARCHAR2(5) NOT NULL Project Type
A=Activity
G=Grant
H=Phase
P=Project
S=Segment
16 PROJDESCR Character(30) VARCHAR2(30) NOT NULL Project Name
17 PROJ_ROLE Character(15) VARCHAR2(15) NOT NULL Project Role
18 DEPTID Character(10) VARCHAR2(10) NOT NULL Department

Prompt Table: DEPTID_BUGL2_VW

19 OPERATING_UNIT Character(8) VARCHAR2(8) NOT NULL Operating Unit ChartField
20 PRODUCT Character(6) VARCHAR2(6) NOT NULL Product ChartField
21 FUND_CODE Character(5) VARCHAR2(5) NOT NULL Fund Code
22 CLASS_FLD Character(5) VARCHAR2(5) NOT NULL Class Field
23 PROGRAM_CODE Character(5) VARCHAR2(5) NOT NULL Program Code ChartField
24 BUDGET_REF Character(8) VARCHAR2(8) NOT NULL Budget Reference
25 AFFILIATE Character(5) VARCHAR2(5) NOT NULL Affiliate
26 AFFILIATE_INTRA1 Character(10) VARCHAR2(10) NOT NULL IntraUnit Affiliate1
27 AFFILIATE_INTRA2 Character(10) VARCHAR2(10) NOT NULL Operating Unit Affiliate
28 CHARTFIELD1 Character(10) VARCHAR2(10) NOT NULL Expansion chartfield 1
29 CHARTFIELD2 Character(10) VARCHAR2(10) NOT NULL Expansion Chartfield 2
30 CHARTFIELD3 Character(10) VARCHAR2(10) NOT NULL Expansion Chartfield 3
31 GM_ADMIN_CNTCT Character(11) VARCHAR2(11) NOT NULL Grant Administrator
32 CUST_NAME Character(40) VARCHAR2(40) NOT NULL Name
33 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
34 JOBCODE Character(6) VARCHAR2(6) NOT NULL Job Code

Prompt Table: JOBCODE_TBL
Set Control Field: BUSINESS_UNIT_PC

35 ACCOUNT Character(10) VARCHAR2(10) NOT NULL Account

Prompt Table: GL_ACCT_CTLB_VW

36 RESOURCE_TYPE Character(5) VARCHAR2(5) NOT NULL Source Type

Prompt Table: PROJ_RES_TYPE

37 RESOURCE_CATEGORY Character(5) VARCHAR2(5) NOT NULL Category

Prompt Table: PROJ_CATG_TBL
Set Control Field: BUSINESS_UNIT_PC

38 RESOURCE_SUB_CAT Character(5) VARCHAR2(5) NOT NULL Subcategory

Prompt Table: PROJ_SUBCAT_TBL
Set Control Field: BUSINESS_UNIT_PC

39 PAID_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Paid Amount
40 COMMITTED_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Committed Assets
41 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code

Prompt Table: CURRENCY_CD_TBL

42 EFFORT Number(13,2) DECIMAL(12,2) NOT NULL Effort