PC_WC_MSPRJ_VW(SQL View) |
Index Back |
|---|---|
Project Fields Exception viewMissing Project Fields Exception base view |
| WITH PRJFLDS AS ( SELECT PRJ.BUSINESS_UNIT , PRJ.PROJECT_ID , PRJ.DESCR , PRJ.PROJECT_TYPE , PRST.PROJECT_STATUS , PST.DESCR AS "DESCR3" , PRJ.START_DT , PRJ.END_DT , MGR.PROJECT_MANAGER , PRJ.GRANT_FLG , 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 PRJFLDS.BUSINESS_UNIT , PRJFLDS.PROJECT_ID , PRJFLDS.DESCR , PRJFLDS.PROJECT_TYPE , PRJFLDS.PROJECT_STATUS , PRJFLDS.DESCR3 , PRJFLDS.START_DT , PRJFLDS.END_DT , PRJFLDS.PROJECT_MANAGER , PRJFLDS.GRANT_FLG , PRJFLDS.OPRID , ' ' , ' ' , '01' FROM PRJFLDS WHERE PRJFLDS.PROJECT_TYPE = ' ' UNION SELECT PRJFLDS.BUSINESS_UNIT , PRJFLDS.PROJECT_ID , PRJFLDS.DESCR , PRJFLDS.PROJECT_TYPE , PRJFLDS.PROJECT_STATUS , PRJFLDS.DESCR3 , PRJFLDS.START_DT , PRJFLDS.END_DT , PRJFLDS.PROJECT_MANAGER , PRJFLDS.GRANT_FLG , PRJFLDS.OPRID , ' ' , ' ' , '02' FROM PRJFLDS WHERE NOT EXISTS( SELECT 'X' FROM PS_PROJ_LOCATION LOC WHERE LOC.BUSINESS_UNIT = PRJFLDS.BUSINESS_UNIT AND LOC.PROJECT_ID = PRJFLDS.PROJECT_ID) |
| # | 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
Prompt Table: SP_PROJPT_NONVW |
| 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 |
| 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 |
| 6 | DESCR3 | Character(30) | VARCHAR2(30) NOT NULL |
Description
Prompt Table: PROJ_STATUS_TBL |
| 7 | START_DT | Date(10) | DATE | Start Date |
| 8 | END_DT | Date(10) | DATE | End Date |
| 9 | PROJECT_MANAGER | Character(11) | VARCHAR2(11) NOT NULL | Project Manager |
| 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 | ERROR_MSG_TXT | Long Character(1000) | VARCHAR2(1000) | Error Message |
| 13 | UPD_STATUS_FL | Character(1) | VARCHAR2(1) NOT NULL | Update Status |
| 14 | PC_ERROR_PRJ | Character(2) | VARCHAR2(2) NOT NULL |
PC Project Errors
01=Project Type 02=Project Location |