PC_ED_RAW_SVW (SQL View)	Index Back
Project Transactions Endeca View of raw project summary transactions prior to calculation of budget, actuals, and revenue. For use in Endeca analytics.

PC_ED_RAW_SVW

(SQL View)

Project Transactions Endeca

View of raw project summary transactions prior to calculation of budget, actuals, and revenue. For use in Endeca analytics.

SELECT A.BUSINESS_UNIT , A.PROJECT_ID , A.ACTIVITY_ID , A.ANALYSIS_TYPE , A.RESOURCE_TYPE , A.RESOURCE_CATEGORY , A.RESOURCE_SUB_CAT , A.TRANS_DATE , A.ACCOUNTING_DT , A.BI_DISTRIB_STATUS , A.GL_DISTRIB_STATUS , A.UNIT_OF_MEASURE , F.CURRENCY_CD , A.EMPLID , D.BUDGET_PERIOD , B.DESCR , C.DESCR , B.PROJECT_TYPE , B.EFF_STATUS , B.PC_HOURS_PER_DAY , B.PROJECT_USER1 , B.PROJECT_USER2 , B.PROJECT_USER3 , B.PROJECT_USER4 , B.PROJECT_USER5 , C.EFF_STATUS , C.PERCENT_COMPLETE , C.PC_MILESTONE_FLG , C.ACTIVITY_USER1 , C.ACTIVITY_USER2 , C.ACTIVITY_USER3 , C.ACTIVITY_USER4 , C.ACTIVITY_USER5 , B.SET_OVERRIDE , B.AN_GRP_ACTV_BUD , B.AN_GRP_TOT_COSTS , B.PC_REV_BUD_AN_GRP , B.AN_GRP_TOT_REV , A.RESOURCE_QUANTITY , A.RESOURCE_AMOUNT , CASE WHEN (%Coalesce(A.DTTM_STAMP, %DateTimeIn('1900-01-01-00.00.00.000000')) >= %Coalesce(B.FMS_LASTUPDDTTM, %DateTimeIn('1900-01-01-00.00.00.000000')) AND %Coalesce(A.DTTM_STAMP, %DateTimeIn('1900-01-01-00.00.00.000000')) >= %Coalesce(C.FMS_LASTUPDDTTM, %DateTimeIn('1900-01-01-00.00.00.000000'))) THEN A.DTTM_STAMP WHEN (%Coalesce(B.FMS_LASTUPDDTTM, %DateTimeIn('1900-01-01-00.00.00.000000')) >= %Coalesce(A.DTTM_STAMP, %DateTimeIn('1900-01-01-00.00.00.000000')) AND %Coalesce(B.FMS_LASTUPDDTTM, %DateTimeIn('1900-01-01-00.00.00.000000')) >= %Coalesce(C.FMS_LASTUPDDTTM, %DateTimeIn('1900-01-01-00.00.00.000000'))) THEN B.FMS_LASTUPDDTTM WHEN (%Coalesce(C.FMS_LASTUPDDTTM, %DateTimeIn('1900-01-01-00.00.00.000000')) > %Coalesce(A.DTTM_STAMP, %DateTimeIn('1900-01-01-00.00.00.000000')) AND %Coalesce(C.FMS_LASTUPDDTTM, %DateTimeIn('1900-01-01-00.00.00.000000')) > %Coalesce(B.FMS_LASTUPDDTTM, %DateTimeIn('1900-01-01-00.00.00.000000'))) THEN C.FMS_LASTUPDDTTM END FROM PS_PC_SUMMARY_TBL A , PS_PROJECT B , PS_PROJ_ACTIVITY C , PS_CAL_BP_TBL D , PS_INSTALLATION_PC E , PS_BUS_UNIT_TBL_PC F WHERE A.BUSINESS_UNIT = B.BUSINESS_UNIT AND A.PROJECT_ID = B.PROJECT_ID AND A.BUSINESS_UNIT = C.BUSINESS_UNIT AND A.PROJECT_ID = C.PROJECT_ID AND A.ACTIVITY_ID = C.ACTIVITY_ID AND A.BUSINESS_UNIT = F.BUSINESS_UNIT AND B.SET_OVERRIDE = D.SETID AND D.CALENDAR_ID = E.PC_SUM_CAL_ID AND A.ACCOUNTING_DT BETWEEN D.BEGIN_DT AND D.END_DT

Parent record: PROJ_ACTIVITY

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description

1 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit

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

3 ACTIVITY_ID Character(15) VARCHAR2(15) NOT NULL Activity ID

4 ANALYSIS_TYPE Character(3) VARCHAR2(3) NOT NULL Analysis Type

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

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

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

8 TRANS_DT Date(10) DATE Transaction Date
Default Value: %date

9 ACCOUNTING_DT Date(10) DATE The accounting entry construction date for a given transaction (a generic field that crosses multiple
Default Value: %date

10 BI_DISTRIB_STATUS Character(1) VARCHAR2(1) NOT NULL Billing Distribution Status
D=Distributed
I=Ignore
N=Not Distributed
P=Priced
U=Unbillable/Non-billable
W=Billing Worksheet
Default Value: N

11 GL_DISTRIB_STATUS Character(1) VARCHAR2(1) NOT NULL Distribution Status
C=Available to Contracts
D=Distributed
G=Generated
H=Hold
I=Ignored
J=Creating Journals
M=Modified
N=None
P=To be processed
X=Waiting for Reversal
Default Value: I

12 UNIT_OF_MEASURE Character(3) VARCHAR2(3) NOT NULL Used on an approval rule set.
MHR=Muti Hourly
PER=Percentage
SQF=Square Footage

13 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code
Default Value: BUS_UNIT_TBL_PC.CURRENCY_CD

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

15 BUDGET_PERIOD Character(8) VARCHAR2(8) NOT NULL Budget Period

16 PROJ_DESCR Character(30) VARCHAR2(30) NOT NULL Project ID Description

17 ACTIVITY_DESCR Character(30) VARCHAR2(30) NOT NULL Activity Description

18 PROJECT_TYPE Character(5) VARCHAR2(5) NOT NULL Project Type
A=Activity
G=Grant
H=Phase
P=Project
S=Segment

19 EFF_STATUS Character(1) VARCHAR2(1) NOT NULL Effective Status
A=Active
I=Inactive

20 PC_HOURS_PER_DAY Number(2,0) SMALLINT NOT NULL Hours per Day

21 PROJECT_USER1 Character(10) VARCHAR2(10) NOT NULL Project User 1

22 PROJECT_USER2 Character(10) VARCHAR2(10) NOT NULL Project User 2

23 PROJECT_USER3 Character(10) VARCHAR2(10) NOT NULL Project User 3

24 PROJECT_USER4 Character(10) VARCHAR2(10) NOT NULL Project User 4

25 PROJECT_USER5 Character(10) VARCHAR2(10) NOT NULL Project User 5

26 ACTIVITY_STATUS Character(1) VARCHAR2(1) NOT NULL Activity Status

27 PERCENT_COMPLETE Number(6,2) DECIMAL(5,2) NOT NULL % Complete

28 PC_MILESTONE_FLG Character(1) VARCHAR2(1) NOT NULL Milestone Activity
N=No
Y=Yes

29 ACTIVITY_USER1 Character(10) VARCHAR2(10) NOT NULL User 1

30 ACTIVITY_USER2 Character(10) VARCHAR2(10) NOT NULL User 2

31 ACTIVITY_USER3 Character(10) VARCHAR2(10) NOT NULL User 3

32 ACTIVITY_USER4 Character(10) VARCHAR2(10) NOT NULL User 4

33 ACTIVITY_USER5 Character(10) VARCHAR2(10) NOT NULL User 5

34 SET_OVERRIDE Character(5) VARCHAR2(5) NOT NULL FS_08 SetID

35 AN_GRP_ACTV_BUD Character(5) VARCHAR2(5) NOT NULL Budget Analysis Group

36 AN_GRP_TOT_COSTS Character(5) VARCHAR2(5) NOT NULL Cost Analysis Group

37 PC_REV_BUD_AN_GRP Character(5) VARCHAR2(5) NOT NULL Revenue budget analysis group

38 AN_GRP_TOT_REV Character(5) VARCHAR2(5) NOT NULL Cost Analysis Group

39 RESOURCE_QUANTITY Signed Number(16,2) DECIMAL(14,2) NOT NULL Quantity

40 RESOURCE_AMOUNT Signed Number(28,3) DECIMAL(26,3) NOT NULL Transaction Amount

41 DTTM_STAMP DateTime(26) TIMESTAMP Specifies the date and time of the last update to an entry. This field is maintained by PeopleSoft and is used in a variety of contexts.

#	PeopleSoft Field Name	PeopleSoft Field Type	Database Column Type	Description
1	BUSINESS_UNIT	Character(5)	VARCHAR2(5) NOT NULL	Business Unit
2	PROJECT_ID	Character(15)	VARCHAR2(15) NOT NULL	Project Id ChartField
3	ACTIVITY_ID	Character(15)	VARCHAR2(15) NOT NULL	Activity ID
4	ANALYSIS_TYPE	Character(3)	VARCHAR2(3) NOT NULL	Analysis Type
5	RESOURCE_TYPE	Character(5)	VARCHAR2(5) NOT NULL	Source Type
6	RESOURCE_CATEGORY	Character(5)	VARCHAR2(5) NOT NULL	Category
7	RESOURCE_SUB_CAT	Character(5)	VARCHAR2(5) NOT NULL	Subcategory
8	TRANS_DT	Date(10)	DATE	Transaction Date Default Value: %date
9	ACCOUNTING_DT	Date(10)	DATE	The accounting entry construction date for a given transaction (a generic field that crosses multiple Default Value: %date
10	BI_DISTRIB_STATUS	Character(1)	VARCHAR2(1) NOT NULL	Billing Distribution Status D=Distributed I=Ignore N=Not Distributed P=Priced U=Unbillable/Non-billable W=Billing Worksheet Default Value: N
11	GL_DISTRIB_STATUS	Character(1)	VARCHAR2(1) NOT NULL	Distribution Status C=Available to Contracts D=Distributed G=Generated H=Hold I=Ignored J=Creating Journals M=Modified N=None P=To be processed X=Waiting for Reversal Default Value: I
12	UNIT_OF_MEASURE	Character(3)	VARCHAR2(3) NOT NULL	Used on an approval rule set. MHR=Muti Hourly PER=Percentage SQF=Square Footage
13	CURRENCY_CD	Character(3)	VARCHAR2(3) NOT NULL	Currency Code Default Value: BUS_UNIT_TBL_PC.CURRENCY_CD
14	EMPLID	Character(11)	VARCHAR2(11) NOT NULL	Employee ID
15	BUDGET_PERIOD	Character(8)	VARCHAR2(8) NOT NULL	Budget Period
16	PROJ_DESCR	Character(30)	VARCHAR2(30) NOT NULL	Project ID Description
17	ACTIVITY_DESCR	Character(30)	VARCHAR2(30) NOT NULL	Activity Description
18	PROJECT_TYPE	Character(5)	VARCHAR2(5) NOT NULL	Project Type A=Activity G=Grant H=Phase P=Project S=Segment
19	EFF_STATUS	Character(1)	VARCHAR2(1) NOT NULL	Effective Status A=Active I=Inactive
20	PC_HOURS_PER_DAY	Number(2,0)	SMALLINT NOT NULL	Hours per Day
21	PROJECT_USER1	Character(10)	VARCHAR2(10) NOT NULL	Project User 1
22	PROJECT_USER2	Character(10)	VARCHAR2(10) NOT NULL	Project User 2
23	PROJECT_USER3	Character(10)	VARCHAR2(10) NOT NULL	Project User 3
24	PROJECT_USER4	Character(10)	VARCHAR2(10) NOT NULL	Project User 4
25	PROJECT_USER5	Character(10)	VARCHAR2(10) NOT NULL	Project User 5
26	ACTIVITY_STATUS	Character(1)	VARCHAR2(1) NOT NULL	Activity Status
27	PERCENT_COMPLETE	Number(6,2)	DECIMAL(5,2) NOT NULL	% Complete
28	PC_MILESTONE_FLG	Character(1)	VARCHAR2(1) NOT NULL	Milestone Activity N=No Y=Yes
29	ACTIVITY_USER1	Character(10)	VARCHAR2(10) NOT NULL	User 1
30	ACTIVITY_USER2	Character(10)	VARCHAR2(10) NOT NULL	User 2
31	ACTIVITY_USER3	Character(10)	VARCHAR2(10) NOT NULL	User 3
32	ACTIVITY_USER4	Character(10)	VARCHAR2(10) NOT NULL	User 4
33	ACTIVITY_USER5	Character(10)	VARCHAR2(10) NOT NULL	User 5
34	SET_OVERRIDE	Character(5)	VARCHAR2(5) NOT NULL	FS_08 SetID
35	AN_GRP_ACTV_BUD	Character(5)	VARCHAR2(5) NOT NULL	Budget Analysis Group
36	AN_GRP_TOT_COSTS	Character(5)	VARCHAR2(5) NOT NULL	Cost Analysis Group
37	PC_REV_BUD_AN_GRP	Character(5)	VARCHAR2(5) NOT NULL	Revenue budget analysis group
38	AN_GRP_TOT_REV	Character(5)	VARCHAR2(5) NOT NULL	Cost Analysis Group
39	RESOURCE_QUANTITY	Signed Number(16,2)	DECIMAL(14,2) NOT NULL	Quantity
40	RESOURCE_AMOUNT	Signed Number(28,3)	DECIMAL(26,3) NOT NULL	Transaction Amount
41	DTTM_STAMP	DateTime(26)	TIMESTAMP	Specifies the date and time of the last update to an entry. This field is maintained by PeopleSoft and is used in a variety of contexts.