PGM_ED_SCHED_VW

(SQL View)
 Index Back

Project Schedule Endeca View

Project and activity schedule information.

SELECT A.BUSINESS_UNIT , A.PROJECT_ID , B.ACTIVITY_ID , A.EFF_STATUS , CASE WHEN A.PROJECT_TYPE <> ' ' THEN A.PROJECT_TYPE ELSE 'N/A' END , CASE WHEN M.PROJECT_MANAGER <> ' ' THEN M.PROJECT_MANAGER ELSE 'N/A' END , CASE WHEN M.PROJECT_MANAGER <> ' ' THEN M.MANAGER_NAME ELSE 'N/A' END , A.DESCR , B.DESCR , A.START_DT , A.END_DT , A.BASELINE_START_DT , A.BASELINE_FINISH_DT , A.ACTUAL_START_DT , A.ACTUAL_FINISH_DT , B.START_DT , B.END_DT , B.BASELINE_START_DT , B.BASELINE_FINISH_DT , B.ACTUAL_START_DT , B.ACTUAL_FINISH_DT , CASE WHEN A.PERCENT_COMPLETE = 0 THEN 'N' WHEN A.PERCENT_COMPLETE = 100 THEN 'C' ELSE 'S' END AS PROGRESS_PRJ , CASE WHEN B.PERCENT_COMPLETE = 0 THEN 'N' WHEN B.PERCENT_COMPLETE = 100 THEN 'C' ELSE 'S' END AS PROGRESS_ACT , %DateDiff(A.START_DT, %CurrentDateIn) AS DELAY_PRJ , %DateDiff(B.START_DT, %CurrentDateIn) AS DELAY_ACT , %DateDiff(A.END_DT, %CurrentDateIn) AS LATE_PRJ , %DateDiff(B.END_DT, %CurrentDateIn) AS LATE_ACT , CASE WHEN %DateDiff(A.START_DT, %CurrentDateIn) BETWEEN 1 AND 15 THEN '1-15 Days' WHEN %DateDiff(A.START_DT, %CurrentDateIn) BETWEEN 16 AND 30 THEN '16-30 Days' WHEN %DateDiff(A.START_DT, %CurrentDateIn) BETWEEN 31 AND 90 THEN '31-90 Days' WHEN %DateDiff(A.START_DT, %CurrentDateIn) > 90 THEN '91+ Days' ELSE 'No Delay' END AS DELAY_RANGE_PRJ , CASE WHEN %DateDiff(B.START_DT, %CurrentDateIn) BETWEEN 1 AND 21 THEN '1-21 Days' WHEN %DateDiff(B.START_DT, %CurrentDateIn) > 21 THEN '21+ Days' ELSE 'No Delay' END AS DELAY_RANGE_ACT , CASE WHEN %DateDiff(A.END_DT, %CurrentDateIn) BETWEEN 1 AND 21 THEN '1-21 Days' WHEN %DateDiff(A.END_DT, %CurrentDateIn) > 21 THEN '21+ Days' ELSE 'No Delay' END AS LATE_RANGE_PRJ , CASE WHEN %DateDiff(B.END_DT, %CurrentDateIn) BETWEEN 1 AND 21 THEN '1-21 Days' WHEN %DateDiff(B.END_DT, %CurrentDateIn) > 21 THEN '21+ Days' ELSE 'No Delay' END AS LATE_RANGE_ACT , CASE WHEN A.PROJECT_USER1 <> ' ' THEN A.PROJECT_USER1 ELSE 'N/A' END , CASE WHEN A.PROJECT_USER2 <> ' ' THEN A.PROJECT_USER2 ELSE 'N/A' END , CASE WHEN A.PROJECT_USER3 <> ' ' THEN A.PROJECT_USER3 ELSE 'N/A' END , CASE WHEN A.PROJECT_USER4 <> ' ' THEN A.PROJECT_USER4 ELSE 'N/A' END , CASE WHEN A.PROJECT_USER5 <> ' ' THEN A.PROJECT_USER5 ELSE 'N/A' END , CASE WHEN B.ACTIVITY_USER1 <> ' ' THEN B.ACTIVITY_USER1 ELSE 'N/A' END , CASE WHEN B.ACTIVITY_USER2 <> ' ' THEN B.ACTIVITY_USER2 ELSE 'N/A' END , CASE WHEN B.ACTIVITY_USER3 <> ' ' THEN B.ACTIVITY_USER3 ELSE 'N/A' END , CASE WHEN B.ACTIVITY_USER4 <> ' ' THEN B.ACTIVITY_USER4 ELSE 'N/A' END , CASE WHEN B.ACTIVITY_USER5 <> ' ' THEN B.ACTIVITY_USER5 ELSE 'N/A' END , A.PERCENT_COMPLETE , B.PERCENT_COMPLETE , CASE WHEN B.PC_MILESTONE_FLG <> ' ' THEN B.PC_MILESTONE_FLG ELSE 'N' END , C.CURRENCY_CD , (CASE WHEN %Coalesce(A.FMS_LASTUPDDTTM, %DateTimeIn('1900-01-01-00.00.00.000000')) >= %Coalesce(B.FMS_LASTUPDDTTM, %DateTimeIn('1900-01-01-00.00.00.000000')) THEN A.FMS_LASTUPDDTTM ELSE B.FMS_LASTUPDDTTM END) , B.ACTIVITY_TYPE FROM PS_PROJECT A LEFT OUTER JOIN ( SELECT F.BUSINESS_UNIT , F.PROJECT_ID , F.PROJECT_MANAGER , %Substring((%TrimSubstr(PERS.FIRST_NAME, 1, 30) %Concat ' ' %Concat %TrimSubstr(PERS.LAST_NAME, 1, 30)), 1, 50) AS MANAGER_NAME FROM PS_PROJECT_MGR F , PS_PERSONAL_DATA PERS WHERE PERS.EMPLID = F.PROJECT_MANAGER AND F.EFFDT = ( SELECT MAX(G.EFFDT) FROM PS_PROJECT_MGR G WHERE F.BUSINESS_UNIT = G.BUSINESS_UNIT AND F.PROJECT_ID = G.PROJECT_ID AND G.EFFDT <= %CurrentDateIn)) M ON A.BUSINESS_UNIT = M.BUSINESS_UNIT AND A.PROJECT_ID = M.PROJECT_ID , PS_PROJ_ACTIVITY B, PS_BUS_UNIT_TBL_PC C, PS_EID_ETL_LASTRUN X WHERE A.BUSINESS_UNIT = B.BUSINESS_UNIT AND A.PROJECT_ID = B.PROJECT_ID AND A.BUSINESS_UNIT = C.BUSINESS_UNIT AND A.EFF_STATUS IN ('A', 'P') AND B.EFF_STATUS = 'A' AND X.EID_DS_ID = 'PGMProjectPlans' AND (A.FMS_LASTUPDDTTM >= X.LAST_RUN_DTM OR B.FMS_LASTUPDDTTM >= X.LAST_RUN_DTM OR ( SELECT MAX(X1.DTTM_STAMP) FROM PS_PROJECT_DESCR X1 WHERE X1.BUSINESS_UNIT = A.BUSINESS_UNIT AND X1.PROJECT_ID = A.PROJECT_ID) >= X.LAST_RUN_DTM OR ( SELECT MAX(X2.DTTM_STAMP) FROM PS_PROJ_ACT_DESCR X2 WHERE X2.BUSINESS_UNIT = A.BUSINESS_UNIT AND X2.PROJECT_ID = A.PROJECT_ID AND X2.ACTIVITY_ID = B.ACTIVITY_ID) >= X.LAST_RUN_DTM OR ( SELECT MAX(X3.DTTM_STAMP) FROM PS_PC_SUMMARY_TBL X3 WHERE X3.BUSINESS_UNIT = A.BUSINESS_UNIT AND X3.PROJECT_ID = A.PROJECT_ID AND X3.ACTIVITY_ID = B.ACTIVITY_ID) >= X.LAST_RUN_DTM OR ( SELECT MAX(X4.FMS_LASTUPDDTTM) FROM PS_PC_BUD_DETAIL X4 WHERE X4.BUSINESS_UNIT = A.BUSINESS_UNIT AND X4.PROJECT_ID = A.PROJECT_ID AND X4.ACTIVITY_ID = B.ACTIVITY_ID) >= X.LAST_RUN_DTM OR ( SELECT MAX(X5.FMS_LASTUPDDTTM) FROM PS_PROJECT_TEAM X5 WHERE X5.BUSINESS_UNIT = A.BUSINESS_UNIT AND X5.PROJECT_ID = A.PROJECT_ID) >= X.LAST_RUN_DTM OR ( SELECT MAX(X6.FMS_LASTUPDDTTM) FROM PS_PROJ_ACT_TEAM X6 WHERE X6.BUSINESS_UNIT = A.BUSINESS_UNIT AND X6.PROJECT_ID = A.PROJECT_ID AND X6.ACTIVITY_ID = B.ACTIVITY_ID) >= X.LAST_RUN_DTM)

# 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 EFF_STATUS Character(1) VARCHAR2(1) NOT NULL Effective Status
A=Active
I=Inactive
5 PROJECT_TYPE Character(5) VARCHAR2(5) NOT NULL Project Type
A=Activity
G=Grant
H=Phase
P=Project
S=Segment
6 PROJECT_MANAGER Character(11) VARCHAR2(11) NOT NULL Project Manager
7 PROJ_MGR_NAME Character(50) VARCHAR2(50) NOT NULL Project Manager Name
8 PROJ_DESCR Character(30) VARCHAR2(30) NOT NULL Project ID Description
9 ACTIVITY_DESCR Character(30) VARCHAR2(30) NOT NULL Activity Description
10 START_DT Date(10) DATE Start Date
11 END_DT Date(10) DATE End Date
12 BASELINE_START_DT Date(10) DATE Baseline Start Date
13 BASELINE_FINISH_DT Date(10) DATE Baseline Finish Date
14 ACTUAL_START_DT Date(10) DATE Actual Start Date
15 ACTUAL_FINISH_DT Date(10) DATE Actual Finish Date
16 ACT_START_DT Date(10) DATE Activity Start Date
17 ACT_END_DT Date(10) DATE Activity End Date
18 BASELINE_BEGIN_DT Date(10) DATE Baseline Start Date
19 BASELINE_END_DT Date(10) DATE Baseline Finish Date
20 ACTL_START_DT Date(10) DATE Actual Start Date
21 ACTL_END_DT Date(10) DATE Actual End Date
22 PROGRESS_PRJ Character(1) VARCHAR2(1) NOT NULL Indicator of project's progress based on percent complete.
23 PROGRESS_ACT Character(1) VARCHAR2(1) NOT NULL Indicator of activity's progress based on percent complete.
24 DELAY_PRJ Signed Number(7,0) DECIMAL(6) NOT NULL Difference in days between a project's scheduled start date and current date.
25 DELAY_ACT Signed Number(7,0) DECIMAL(6) NOT NULL Difference in days between activity's scheduled start date and current date.
26 LATE_PRJ Signed Number(7,0) DECIMAL(6) NOT NULL Difference in days between project's scheduled end date and current date.
27 LATE_ACT Signed Number(7,0) DECIMAL(6) NOT NULL Difference in days between activity's scheduled end date and current date.
28 DELAY_RANGE_PRJ Character(30) VARCHAR2(30) NOT NULL Project delay range
29 DELAY_RANGE_ACT Character(30) VARCHAR2(30) NOT NULL Activity delay range
30 LATE_RANGE_PRJ Character(30) VARCHAR2(30) NOT NULL Project late range
31 LATE_RANGE_ACT Character(30) VARCHAR2(30) NOT NULL Activity late range
32 PROJECT_USER1 Character(10) VARCHAR2(10) NOT NULL Project User 1
33 PROJECT_USER2 Character(10) VARCHAR2(10) NOT NULL Project User 2
34 PROJECT_USER3 Character(10) VARCHAR2(10) NOT NULL Project User 3
35 PROJECT_USER4 Character(10) VARCHAR2(10) NOT NULL Project User 4
36 PROJECT_USER5 Character(10) VARCHAR2(10) NOT NULL Project User 5
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 PERCENT_COMPLETE Number(6,2) DECIMAL(5,2) NOT NULL % Complete
43 PERCENT_COMP Number(6,2) DECIMAL(5,2) NOT NULL Scrap Prdn Pct
44 PC_MILESTONE_FLG Character(1) VARCHAR2(1) NOT NULL Milestone Activity
N=No
Y=Yes
45 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code
46 LAST_UPDATE_DTTM 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.
47 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