OI_ACTIV_VW

(SQL View)
 Index Back

Activity

Primary record for Activity

SELECT PA.BUSINESS_UNIT , PA.PROJECT_ID , PA.ACTIVITY_ID , PA.DESCR , PA.ACTIVITY_TYPE , PAT_ED.DESCR ACTIVITY_TYPE_DESCR , PA.WBS_ID , PW_ED.DESCR WBS_DESCR , XLI1.DESCR SYS_SRC_DESC , PA.ACT_TEMPLATE_ID , PA.ACT_TEMPLATE_SW , PA.WORK_ORDER_ID , PA.WORK_ORDER_DTL_ID , PA.AFUDC_ACT_SW , PA.TARGET_ACT_SW , PA.SALES_TAX_ACT_SW , PA.PERCENT_COMPLETE , PA.START_DT , PA.END_DT , PA.EARLY_START_DT , PA.LATE_START_DT , PA.ACTUAL_START_DT , PA.EARLY_FINISH_DT , PA.LATE_FINISH_DT , PA.ACTUAL_FINISH_DT , PA.BASELINE_FINISH_DT , PA.BASELINE_START_DT , PA.BCWP , PA.BCWS , PA.ACT_TYPE_SCH , SCH_ED.DESCR ACT_TYPE_SCH_DESCR , PA.INTEGRATION_ID , PA.INTEGRATION_TRG , PA.INTEGRATION_ANTYPE , PA.DTTM_STAMP , PA.DRAWING_NAME , PA.ACTIVITY_USER1 , PA.ACTIVITY_USER2 , PA.ACTIVITY_USER3 , PA.ACTIVITY_USER4 , PA.ACTIVITY_USER5 , PA.PC_ACT_USER_DT1 , PA.PC_ACT_USER_DT2 , PA.PC_USER_CURRENCY , PA.PC_ACT_USERAMT1 , PA.PC_ACT_USERAMT2 , PA.PC_ACT_USERAMT3 , PA.PC_MSP_UNIQUE_ID , PA.PC_MILESTONE_FLG , PA.PC_SCH_FIELD1 , PA.PC_SCH_FIELD2 , PA.PC_SCH_FIELD3 , PA.PC_SCH_FIELD4 , PA.PC_SCH_FIELD5 , PA.PC_SCH_FIELD6 , PA.PC_SCH_FIELD7 , XLI2.DESCR WAIVED_FA_CM_DESC , XLI3.DESCR ENFORCE_DESC , XLI4.DESCR SUMMARY_ACT_DESC , PA.PC_DURATION , PA.PC_CHC_ACT_SW , XLI5.DESCR PGM_SCHED_MD_DESC , PA.ASSGND_WORK , PA.ACTUAL_WORK , PA.REMAINING_WORK , PA.BASELINE_WORK , PA.COST , PA.DEFAULT_CURR_SW , PA.LEVEL_NUM , PA.ROW_NUMBER_5 , PA.DEADLINE_DT , PA.CONSTRAINT_DT , XLI6.DESCR CONSTRAINT_TY_DESC , PA.COSTING_ACT_SW , PA.PC_ACT_OWNER , PTP.NAME , PA.BILL_AMT , PA.CURRENCY_CD , XLI7.DESCR PC_ACT_DEF_CM_DESC , PA.PARTICIPATING , PA.IN_USE_FLAG FROM PS_PROJ_ACTIVITY PA LEFT OUTER JOIN PS_SET_CNTRL_REC SCR1 ON PA.BUSINESS_UNIT=SCR1.SETCNTRLVALUE AND SCR1.RECNAME = 'PROJ_ACT_TBL' LEFT OUTER JOIN ( SELECT PAT.SETID , PAT.DESCR , PAT.ACTIVITY_TYPE FROM PS_PROJ_ACT_TBL PAT WHERE PAT.EFFDT = ( SELECT MAX(ED1.EFFDT) FROM PS_PROJ_ACT_TBL ED1 WHERE PAT.SETID =ED1.SETID AND PAT.ACTIVITY_TYPE=ED1.ACTIVITY_TYPE ) ) PAT_ED ON SCR1.SETID =PAT_ED.SETID AND PA.ACTIVITY_TYPE=PAT_ED.ACTIVITY_TYPE LEFT OUTER JOIN PS_SET_CNTRL_REC SCR2 ON PA.BUSINESS_UNIT=SCR2.SETCNTRLVALUE AND SCR2.RECNAME = 'PC_WBS_TBL' LEFT OUTER JOIN ( SELECT PW.SETID ,PW.WBS_ID ,PW.DESCR FROM PS_PC_WBS_TBL PW WHERE PW.EFFDT = ( SELECT MAX(ED2.EFFDT) FROM PS_PC_WBS_TBL ED2 WHERE PW.SETID=ED2.SETID AND PW.WBS_ID=ED2.WBS_ID )) PW_ED ON SCR2.SETID=PW_ED.SETID AND PA.WBS_ID=PW_ED.WBS_ID LEFT OUTER JOIN PS_PC_TEAM_PROM_VW PTP ON PA.BUSINESS_UNIT=PTP.BUSINESS_UNIT AND PA.PROJECT_ID=PTP.PROJECT_ID AND PA.PC_ACT_OWNER = PTP.TEAM_MEMBER LEFT OUTER JOIN PS_SET_CNTRL_REC SCR3 ON PA.BUSINESS_UNIT=SCR3.SETCNTRLVALUE AND SCR3.RECNAME = 'PC_ACT_TYPE_SCH' LEFT OUTER JOIN ( SELECT SCH.SETID ,SCH.ACT_TYPE_SCH ,SCH.DESCR FROM PS_PC_ACT_TYPE_SCH SCH WHERE SCH.EFFDT = ( SELECT MAX(ED2.EFFDT) FROM PS_PC_ACT_TYPE_SCH ED2 WHERE SCH.SETID=ED2.SETID AND SCH.ACT_TYPE_SCH=ED2.ACT_TYPE_SCH )) SCH_ED ON SCR3.SETID=SCH_ED.SETID AND PA.ACT_TYPE_SCH=SCH_ED.ACT_TYPE_SCH LEFT OUTER JOIN PS_OI_COMMON_VW XLI1 ON XLI1.CODESETNAME = PA.SYSTEM_SOURCE AND XLI1.CODESETGROUPNAME='SYSTEM_SOURCE' LEFT OUTER JOIN PS_OI_COMMON_VW XLI2 ON XLI2.CODESETNAME = PA.WAIVED_FA_CLC_MTHD AND XLI2.CODESETGROUPNAME='WAIVED_FA_CLC_MTHD' LEFT OUTER JOIN PS_OI_COMMON_VW XLI3 ON XLI3.CODESETNAME = PA.ENFORCE AND XLI3.CODESETGROUPNAME='ENFORCE' LEFT OUTER JOIN PS_OI_COMMON_VW XLI4 ON XLI4.CODESETNAME = PA.SUMMARY_ACT AND XLI4.CODESETGROUPNAME='SUMMARY_ACT' LEFT OUTER JOIN PS_OI_COMMON_VW XLI5 ON XLI5.CODESETNAME = PA.PGM_SCHED_METHOD AND XLI5.CODESETGROUPNAME='PGM_SCHED_METHOD' LEFT OUTER JOIN PS_OI_COMMON_VW XLI6 ON XLI6.CODESETNAME = PA.CONSTRAINT_TYPE AND XLI6.CODESETGROUPNAME='CONSTRAINT_TYPE' LEFT OUTER JOIN PS_OI_COMMON_VW XLI7 ON XLI7.CODESETNAME = PA.PC_ACT_DEF_CALC_MT AND XLI7.CODESETGROUPNAME='PC_ACT_DEF_CALC_MT' UNION SELECT ' ' , ' ' , ' ' , 'Not Available' , '-' , '-' , '-' , '-' , '-' , '-' , '-' , '-' , '-' , '-' , '-' , '-' , 0 , %DateIn('1900-01-01') , %DateIn('1900-01-01') , %DateIn('1900-01-01') , %DateIn('1900-01-01') , %DateIn('1900-01-01') , %DateIn('1900-01-01') , %DateIn('1900-01-01') , %DateIn('1900-01-01') , %DateIn('1900-01-01') , %DateIn('1900-01-01') , 0 , 0 , '-' , '-' , '-' , '-' , '-' , %DateTimeIn('1900-01-01 00.00.00.000000') , '-' , '-' , '-' , '-' , '-' , '-' , %DateIn('1900-01-01') , %DateIn('1900-01-01') , '-' , 0 , 0 , 0 , '-' , '-' , '-' , '-' , '-' , '-' , '-' , '-' , '-' , '-' , '-' , '-' , 0 , '-' , '-' , 0 , 0 , 0 , 0 , 0 , '-' , 0 , 0 , %DateIn('1900-01-01') , %DateIn('1900-01-01') , '-' , '-' , '-' , '-' , 0 , '-' , '-' , '-' , '-' FROM PS_INSTALLATION

# 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 DESCR Character(30) VARCHAR2(30) NOT NULL Description
5 ACTIVITY_TYPE Character(5) VARCHAR2(5) NOT NULL Activity Type
A=Activity Type
E=Entry Type
G=Group Type/Origin
O=Activity Type/Origin
R=Entry Type/Reason
6 ACT_TYP_DESCR Character(30) VARCHAR2(30) NOT NULL Activity type description
7 WBS_ID Character(100) VARCHAR2(100) NOT NULL WBS ID
8 WBS_DESCR Character(30) VARCHAR2(30) NOT NULL WBS description
9 SYS_SRC_DESC Character(30) VARCHAR2(30) NOT NULL System Source Description
10 ACT_TEMPLATE_ID Character(15) VARCHAR2(15) NOT NULL Activity Template
11 ACT_TEMPLATE_SW Character(1) VARCHAR2(1) NOT NULL Use Activity as Template
12 WORK_ORDER_ID Character(15) VARCHAR2(15) NOT NULL Work Order ID
13 WORK_ORDER_DTL_ID Character(15) VARCHAR2(15) NOT NULL Work Order Detail
14 AFUDC_ACT_SW Character(1) VARCHAR2(1) NOT NULL Allow AFUDC Calculation
15 TARGET_ACT_SW Character(1) VARCHAR2(1) NOT NULL Allow Node to be target
16 SALES_TAX_ACT_SW Character(1) VARCHAR2(1) NOT NULL Allow Sales Tax Calculation
17 PERCENT_COMPLETE Number(6,2) DECIMAL(5,2) NOT NULL % Complete
18 START_DT Date(10) DATE Start Date
19 END_DT Date(10) DATE End Date
20 EARLY_START_DT Date(10) DATE Early Start Date
21 LATE_START_DT Date(10) DATE Late Start Date
22 ACTUAL_START_DT Date(10) DATE Actual Start Date
23 EARLY_FINISH_DT Date(10) DATE Early Finish Date
24 LATE_FINISH_DT Date(10) DATE Late Finish Date
25 ACTUAL_FINISH_DT Date(10) DATE Actual Finish Date
26 BASELINE_FINISH_DT Date(10) DATE Baseline Finish Date
27 BASELINE_START_DT Date(10) DATE Baseline Start Date
28 BCWP Number(3,0) SMALLINT NOT NULL BCWP - Worked Performed
29 BCWS Number(3,0) SMALLINT NOT NULL BCWS Work Scheduled
30 ACT_TYPE_SCH Character(5) VARCHAR2(5) NOT NULL Schedule Activity Type
31 ACT_TYP_SCH_DESCR Character(30) VARCHAR2(30) NOT NULL Schedule activity type description
32 INTEGRATION_ID Character(40) VARCHAR2(40) NOT NULL 3rd Prty Integr Identifier
33 INTEGRATION_TRG Character(3) VARCHAR2(3) NOT NULL Integration Trigger
34 INTEGRATION_ANTYPE Character(5) VARCHAR2(5) NOT NULL Integration AnType Override
35 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.
36 DRAWING_NAME Character(8) VARCHAR2(8) NOT NULL Drawing Name
37 ACTIVITY_USER1 Character(10) VARCHAR2(10) NOT NULL User 1
38 ACTIVITY_USER2 Character(10) VARCHAR2(10) NOT NULL User 2
39 ACTIVITY_USER3 Character(10) VARCHAR2(10) NOT NULL User 3
40 ACTIVITY_USER4 Character(10) VARCHAR2(10) NOT NULL User 4
41 ACTIVITY_USER5 Character(10) VARCHAR2(10) NOT NULL User 5
42 PC_ACT_USER_DT1 Date(10) DATE Date 1
43 PC_ACT_USER_DT2 Date(10) DATE Date 2
44 PC_USER_CURRENCY Character(3) VARCHAR2(3) NOT NULL User Currency
45 PC_ACT_USERAMT1 Signed Number(17,2) DECIMAL(15,2) NOT NULL Amount 1
46 PC_ACT_USERAMT2 Signed Number(17,2) DECIMAL(15,2) NOT NULL Amount 2
47 PC_ACT_USERAMT3 Signed Number(17,2) DECIMAL(15,2) NOT NULL Amount 3
48 PC_MSP_UNIQUE_ID Character(22) VARCHAR2(22) NOT NULL Proj Act Unique ID
49 PC_MILESTONE_FLG Character(1) VARCHAR2(1) NOT NULL Milestone Activity
N=No
Y=Yes
50 PC_SCH_FIELD1 Character(40) VARCHAR2(40) NOT NULL Generic field used for Projects Third Party Schedule Integration.
51 PC_SCH_FIELD2 Character(40) VARCHAR2(40) NOT NULL Generic field used for Projects Third Party Schedule Integration.
52 PC_SCH_FIELD3 Character(40) VARCHAR2(40) NOT NULL Generic field used for Projects Third Party Schedule Integration.
53 PC_SCH_FIELD4 Character(40) VARCHAR2(40) NOT NULL Generic field used for Projects Third Party Schedule Integration.
54 PC_SCH_FIELD5 Character(40) VARCHAR2(40) NOT NULL Generic field used for Projects Third Party Schedule Integration.
55 PC_SCH_FIELD6 Character(40) VARCHAR2(40) NOT NULL Generic field used for Projects Third Party Schedule Integration.
56 PC_SCH_FIELD7 Character(40) VARCHAR2(40) NOT NULL Generic field used for Projects Third Party Schedule Integration.
57 WAIVED_FA_CM_DESC Character(30) VARCHAR2(30) NOT NULL Waived F&A Calc Method
58 ENFORCE_DESC Character(30) VARCHAR2(30) NOT NULL Enforce Project Team
59 SUMMARY_ACT_DESC Character(30) VARCHAR2(30) NOT NULL Summary Activity
60 PC_DURATION Number(11,2) DECIMAL(10,2) NOT NULL Duration
61 PC_CHC_ACT_SW Character(1) VARCHAR2(1) NOT NULL Enable Change Control
62 PGM_SCHED_MD_DESC Character(30) VARCHAR2(30) NOT NULL Schedule Method description
63 ASSGND_WORK Signed Number(16,2) DECIMAL(14,2) NOT NULL Assigned Work
64 ACTUAL_WORK Signed Number(16,2) DECIMAL(14,2) NOT NULL Actual Work
65 REMAINING_WORK Signed Number(16,2) DECIMAL(14,2) NOT NULL New Remaining work in hours
66 BASELINE_WORK Signed Number(16,2) DECIMAL(14,2) NOT NULL Baseline Work
67 COST Signed Number(28,3) DECIMAL(26,3) NOT NULL Total Cost
68 DEFAULT_CURR_SW Character(1) VARCHAR2(1) NOT NULL Default Currency
69 LEVEL_NUM Number(3,0) SMALLINT NOT NULL Level
70 ROW_NUMBER_5 Number(5,0) INTEGER NOT NULL Row Number
71 DEADLINE_DT Date(10) DATE Deadline Date
72 CONSTRAINT_DT Date(10) DATE Constraint Date
73 CONSTRAINT_TY_DESC Character(30) VARCHAR2(30) NOT NULL Constraint Type description
74 COSTING_ACT_SW Character(1) VARCHAR2(1) NOT NULL Activity Costing Switch - determines whether an activity is available for charging. 'Y' = charging is allowed; 'N' = charging is not allowed.
75 PC_ACT_OWNER Character(11) VARCHAR2(11) NOT NULL Activity Owner - The employee id of the individual who is responsible for the execution of an activity or a group of activities.
76 NAME Character(50) VARCHAR2(50) NOT NULL Name
77 BILL_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Bill Amount
78 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code
79 PC_ACT_DEF_CM_DESC Character(30) VARCHAR2(30) NOT NULL Activity Calculation Method description
80 PARTICIPATING Character(1) VARCHAR2(1) NOT NULL Participating
81 IN_USE_FLAG Character(1) VARCHAR2(1) NOT NULL Row in use flag