PC_PRJUSRFLD_VW

(SQL View)
 Index Back

Missing Project User Fields


WITH PRJUSRFLDS AS ( SELECT PRJ.BUSINESS_UNIT , PRJ.PROJECT_ID , PRJ.DESCR , PRJ.PROJECT_TYPE , PRST.PROJECT_STATUS , PST.DESCR AS "DESCR3" , MGR.PROJECT_MANAGER , PRJ.START_DT , PRJ.END_DT , PRJ.GRANT_FLG , PRJ.PROJECT_USER1 , PRJ.PROJECT_USER2 , PRJ.PROJECT_USER3 , PRJ.PROJECT_USER4 , PRJ.PROJECT_USER5 , PRJ.PC_USER_CURRENCY , PRJ.PROJECT_USERAMT1 , PRJ.PROJECT_USERAMT2 , PRJ.PROJECT_USERAMT3 , PRJ.PROJECT_USER_DT1 , PRJ.PROJECT_USER_DT2 , MPRJ.OPRID FROM PS_PROJECT PRJ LEFT OUTER JOIN ( SELECT * FROM PS_PROJECT_STATUS PRS WHERE PRS.EFFDT = ( SELECT MAX(PRS1.EFFDT) FROM PS_PROJECT_STATUS PRS1 WHERE PRS1.BUSINESS_UNIT = PRS.BUSINESS_UNIT AND PRS1.PROJECT_ID = PRS.PROJECT_ID AND PRS1.EFFDT <= %CurrentDateIn) AND PRS.EFFSEQ = ( SELECT MAX(PRS2.EFFSEQ) FROM PS_PROJECT_STATUS PRS2 WHERE PRS2.BUSINESS_UNIT = PRS.BUSINESS_UNIT AND PRS2.PROJECT_ID = PRS.PROJECT_ID AND PRS2.EFFDT = PRS.EFFDT) ) PRST ON (PRJ.BUSINESS_UNIT = PRST.BUSINESS_UNIT AND PRJ.PROJECT_ID = PRST.PROJECT_ID) LEFT OUTER JOIN PS_PROJ_STATUS_TBL PST ON (PST.PROJECT_STATUS = PRST.PROJECT_STATUS)LEFT OUTER JOIN ( SELECT * FROM PS_PROJECT_MGR MGR WHERE MGR.EFFDT = ( SELECT MAX(MGR1.EFFDT) FROM PS_PROJECT_MGR MGR1 WHERE MGR1.BUSINESS_UNIT = MGR.BUSINESS_UNIT AND MGR1.PROJECT_ID = MGR.PROJECT_ID AND MGR1.EFFDT <= %CurrentDateIn) ) MGR ON (MGR.BUSINESS_UNIT = PRJ.BUSINESS_UNIT AND MGR.PROJECT_ID = PRJ.PROJECT_ID), PS_PC_OPRID_PRJACT MPRJ WHERE MPRJ.BUSINESS_UNIT = PRJ.BUSINESS_UNIT AND MPRJ.PROJECT_ID = PRJ.PROJECT_ID AND (PRJ.EFF_STATUS IN ('A','P') OR (PRJ.EFF_STATUS = 'T' AND PRJ.PC_TEMPLATE_STATUS <> 'I'))) SELECT PRJUSRFLDS.BUSINESS_UNIT , PRJUSRFLDS.PROJECT_ID , PRJUSRFLDS.DESCR , PRJUSRFLDS.PROJECT_TYPE , PRJUSRFLDS.PROJECT_STATUS , PRJUSRFLDS.DESCR AS "DESCR3" , PRJUSRFLDS.PROJECT_MANAGER , PRJUSRFLDS.START_DT , PRJUSRFLDS.END_DT , PRJUSRFLDS.GRANT_FLG , PRJUSRFLDS.OPRID , CASE PRJUSRFLDS.PROJECT_USER1 WHEN ' ' THEN 'U1' ELSE 'OK' END FROM PRJUSRFLDS UNION SELECT PRJUSRFLDS.BUSINESS_UNIT , PRJUSRFLDS.PROJECT_ID , PRJUSRFLDS.DESCR , PRJUSRFLDS.PROJECT_TYPE , PRJUSRFLDS.PROJECT_STATUS , PRJUSRFLDS.DESCR AS "DESCR3" , PRJUSRFLDS.PROJECT_MANAGER , PRJUSRFLDS.START_DT , PRJUSRFLDS.END_DT , PRJUSRFLDS.GRANT_FLG , PRJUSRFLDS.OPRID , CASE PRJUSRFLDS.PROJECT_USER2 WHEN ' ' THEN 'U2' ELSE 'OK' END FROM PRJUSRFLDS UNION SELECT PRJUSRFLDS.BUSINESS_UNIT , PRJUSRFLDS.PROJECT_ID , PRJUSRFLDS.DESCR , PRJUSRFLDS.PROJECT_TYPE , PRJUSRFLDS.PROJECT_STATUS , PRJUSRFLDS.DESCR AS "DESCR3" , PRJUSRFLDS.PROJECT_MANAGER , PRJUSRFLDS.START_DT , PRJUSRFLDS.END_DT , PRJUSRFLDS.GRANT_FLG , PRJUSRFLDS.OPRID , CASE PRJUSRFLDS.PROJECT_USER3 WHEN ' ' THEN 'U3' ELSE 'OK' END FROM PRJUSRFLDS UNION SELECT PRJUSRFLDS.BUSINESS_UNIT , PRJUSRFLDS.PROJECT_ID , PRJUSRFLDS.DESCR , PRJUSRFLDS.PROJECT_TYPE , PRJUSRFLDS.PROJECT_STATUS , PRJUSRFLDS.DESCR AS "DESCR3" , PRJUSRFLDS.PROJECT_MANAGER , PRJUSRFLDS.START_DT , PRJUSRFLDS.END_DT , PRJUSRFLDS.GRANT_FLG , PRJUSRFLDS.OPRID , CASE PRJUSRFLDS.PROJECT_USER4 WHEN ' ' THEN 'U4' ELSE 'OK' END FROM PRJUSRFLDS UNION SELECT PRJUSRFLDS.BUSINESS_UNIT , PRJUSRFLDS.PROJECT_ID , PRJUSRFLDS.DESCR , PRJUSRFLDS.PROJECT_TYPE , PRJUSRFLDS.PROJECT_STATUS , PRJUSRFLDS.DESCR AS "DESCR3" , PRJUSRFLDS.PROJECT_MANAGER , PRJUSRFLDS.START_DT , PRJUSRFLDS.END_DT , PRJUSRFLDS.GRANT_FLG , PRJUSRFLDS.OPRID , CASE PRJUSRFLDS.PROJECT_USER5 WHEN ' ' THEN 'U5' ELSE 'OK' END FROM PRJUSRFLDS UNION SELECT PRJUSRFLDS.BUSINESS_UNIT , PRJUSRFLDS.PROJECT_ID , PRJUSRFLDS.DESCR , PRJUSRFLDS.PROJECT_TYPE , PRJUSRFLDS.PROJECT_STATUS , PRJUSRFLDS.DESCR AS "DESCR3" , PRJUSRFLDS.PROJECT_MANAGER , PRJUSRFLDS.START_DT , PRJUSRFLDS.END_DT , PRJUSRFLDS.GRANT_FLG , PRJUSRFLDS.OPRID , CASE PRJUSRFLDS.PC_USER_CURRENCY WHEN ' ' THEN 'U6' ELSE 'OK' END FROM PRJUSRFLDS UNION SELECT PRJUSRFLDS.BUSINESS_UNIT , PRJUSRFLDS.PROJECT_ID , PRJUSRFLDS.DESCR , PRJUSRFLDS.PROJECT_TYPE , PRJUSRFLDS.PROJECT_STATUS , PRJUSRFLDS.DESCR AS "DESCR3" , PRJUSRFLDS.PROJECT_MANAGER , PRJUSRFLDS.START_DT , PRJUSRFLDS.END_DT , PRJUSRFLDS.GRANT_FLG , PRJUSRFLDS.OPRID , CASE PRJUSRFLDS.PROJECT_USERAMT1 WHEN 0 THEN 'U7' ELSE 'OK' END FROM PRJUSRFLDS UNION SELECT PRJUSRFLDS.BUSINESS_UNIT , PRJUSRFLDS.PROJECT_ID , PRJUSRFLDS.DESCR , PRJUSRFLDS.PROJECT_TYPE , PRJUSRFLDS.PROJECT_STATUS , PRJUSRFLDS.DESCR AS "DESCR3" , PRJUSRFLDS.PROJECT_MANAGER , PRJUSRFLDS.START_DT , PRJUSRFLDS.END_DT , PRJUSRFLDS.GRANT_FLG , PRJUSRFLDS.OPRID , CASE PRJUSRFLDS.PROJECT_USERAMT2 WHEN 0 THEN 'U8' ELSE 'OK' END FROM PRJUSRFLDS UNION SELECT PRJUSRFLDS.BUSINESS_UNIT , PRJUSRFLDS.PROJECT_ID , PRJUSRFLDS.DESCR , PRJUSRFLDS.PROJECT_TYPE , PRJUSRFLDS.PROJECT_STATUS , PRJUSRFLDS.DESCR AS "DESCR3" , PRJUSRFLDS.PROJECT_MANAGER , PRJUSRFLDS.START_DT , PRJUSRFLDS.END_DT , PRJUSRFLDS.GRANT_FLG , PRJUSRFLDS.OPRID , CASE PRJUSRFLDS.PROJECT_USERAMT3 WHEN 0 THEN 'U9' ELSE 'OK' END FROM PRJUSRFLDS UNION SELECT PRJUSRFLDS.BUSINESS_UNIT , PRJUSRFLDS.PROJECT_ID , PRJUSRFLDS.DESCR , PRJUSRFLDS.PROJECT_TYPE , PRJUSRFLDS.PROJECT_STATUS , PRJUSRFLDS.DESCR AS "DESCR3" , PRJUSRFLDS.PROJECT_MANAGER , PRJUSRFLDS.START_DT , PRJUSRFLDS.END_DT , PRJUSRFLDS.GRANT_FLG , PRJUSRFLDS.OPRID , CASE WHEN PRJUSRFLDS.PROJECT_USER_DT1 IS NULL THEN 'U91' ELSE 'OK' END FROM PRJUSRFLDS UNION SELECT PRJUSRFLDS.BUSINESS_UNIT , PRJUSRFLDS.PROJECT_ID , PRJUSRFLDS.DESCR , PRJUSRFLDS.PROJECT_TYPE , PRJUSRFLDS.PROJECT_STATUS , PRJUSRFLDS.DESCR AS "DESCR3" , PRJUSRFLDS.PROJECT_MANAGER , PRJUSRFLDS.START_DT , PRJUSRFLDS.END_DT , PRJUSRFLDS.GRANT_FLG , PRJUSRFLDS.OPRID , CASE WHEN PRJUSRFLDS.PROJECT_USER_DT2 IS NULL THEN 'U92' ELSE 'OK' END FROM PRJUSRFLDS

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit

Prompt Table: SP_BU_PC_NONVW

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

Prompt Table: PC_WC_MYPRJ_VW

3 DESCR Character(30) VARCHAR2(30) NOT NULL Description
4 PROJECT_TYPE Character(5) VARCHAR2(5) NOT NULL Project Type
A=Activity
G=Grant
H=Phase
P=Project
S=Segment

Prompt Table: PC_WC_PRJTYP_VW
Set Control Field: BUSINESS_UNIT

5 PROJECT_STATUS Character(1) VARCHAR2(1) NOT NULL Project Status
B=Budgeted
C=Closed
H=Hold
O=Open
P=Proposed

Prompt Table: PC_WC_PRJSTS_VW
Set Control Field: BUSINESS_UNIT

6 DESCR3 Character(30) VARCHAR2(30) NOT NULL Description
7 PROJECT_MANAGER Character(11) VARCHAR2(11) NOT NULL Project Manager
8 START_DT Date(10) DATE Start Date
9 END_DT Date(10) DATE End Date
10 GRANT_FLG Character(1) VARCHAR2(1) NOT NULL Grants Management Used for designating whether or not an object is for Grants or for Projects.
N=Projects Object
Y=Grants Object
11 OPRID Character(30) VARCHAR2(30) NOT NULL A user's ID (see PSOPRDEFN).
12 PRJ_USER_FIELD Character(3) VARCHAR2(3) NOT NULL Missing User Fields
U1=User Field 1
U2=User Field 2
U3=User Field 3
U4=User Field 4
U5=User Field 5
U6=User Currency
U7=User Amount 1
U8=User Amount 2
U9=User Amount 3
U91=User Date 1
U92=User Date 2

Prompt Table: PC_USER_FLD_LBL