PC_PASTPROJ_VW(SQL View) |
Index Back |
|---|
| SELECT P.BUSINESS_UNIT , P.PROJECT_ID , MP.OPRID , P.DESCR , P.START_DT , P.END_DT , P.PROJECT_TYPE , PT.DESCR , PM.PROJECT_MANAGER , P.PC_TEMPLATE_STATUS , PS.EFFDT , PS.EFFSEQ , P.EFF_STATUS , PS.PROJECT_STATUS , PST.DESCR , P.PC_CALCULATE_SW , P.PC_PRJ_DEF_CALC_MT , %DateDiff(P.END_DT, %CurrentDateIn) , ' ' FROM PS_PROJECT P LEFT OUTER JOIN PS_PROJECT_STATUS PS ON (P.PROJECT_ID = PS.PROJECT_ID AND P.BUSINESS_UNIT = PS.BUSINESS_UNIT) LEFT OUTER JOIN PS_PROJ_STATUS_TBL PST ON (PST.PROJECT_STATUS = PS.PROJECT_STATUS) LEFT OUTER JOIN PS_PROJ_TYPE_TBL PT ON (P.PROJECT_TYPE = PT.PROJECT_TYPE AND PT.EFF_STATUS = 'A') LEFT OUTER JOIN PS_PROJECT_MGR PM ON (P.PROJECT_ID = PM.PROJECT_ID AND P.BUSINESS_UNIT = PM.BUSINESS_UNIT) , PS_PC_OPRID_PRJACT MP WHERE MP.BUSINESS_UNIT = P.BUSINESS_UNIT AND MP.PROJECT_ID = P.PROJECT_ID AND P.EFF_STATUS IN ('A','P') AND P.END_DT <= %CurrentDateIn AND PS.EFFDT = ( SELECT MAX(PS_ED.EFFDT) FROM PS_PROJECT_STATUS PS_ED WHERE PS.PROJECT_ID = PS_ED.PROJECT_ID AND PS.BUSINESS_UNIT = PS_ED.BUSINESS_UNIT AND PS.EFFDT <= %CurrentDateIn) AND PS.EFFSEQ = ( SELECT MAX(PS_ES.EFFSEQ) FROM PS_PROJECT_STATUS PS_ES WHERE PS.PROJECT_ID = PS_ES.PROJECT_ID AND PS.BUSINESS_UNIT = PS_ES.BUSINESS_UNIT AND PS_ES.EFFDT = PS.EFFDT) AND (PM.EFFDT IS NULL OR PM.EFFDT=( SELECT MAX(EFFDT) FROM PS_PROJECT_MGR PM_ED WHERE PM_ED.BUSINESS_UNIT = PM.BUSINESS_UNIT AND PM_ED.PROJECT_ID = PM.PROJECT_ID AND PM_ED.EFFDT <= %CurrentDateIn)) |
| # | 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 | OPRID | Character(30) | VARCHAR2(30) NOT NULL | A user's ID (see PSOPRDEFN). |
| 4 | DESCR | Character(30) | VARCHAR2(30) NOT NULL | Description |
| 5 | START_DT | Date(10) | DATE | Start Date |
| 6 | END_DT | Date(10) | DATE | End Date |
| 7 | 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 |
| 8 | DESCR1 | Character(30) | VARCHAR2(30) NOT NULL | Descr |
| 9 | PROJECT_MANAGER | Character(11) | VARCHAR2(11) NOT NULL | Project Manager |
| 10 | PC_TEMPLATE_STATUS | Character(1) | VARCHAR2(1) NOT NULL |
Template Status
A=Active I=Inactive |
| 11 |  EFFDT |
Date(10) | DATE |
Effective Date
Default Value: %date |
| 12 | EFFSEQ | Number(3,0) | SMALLINT NOT NULL | Effective Sequence |
| 13 | EFF_STATUS | Character(1) | VARCHAR2(1) NOT NULL |
Effective Status
A=Active I=Inactive |
| 14 | 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 |
| 15 | DESCR3 | Character(30) | VARCHAR2(30) NOT NULL | Description |
| 16 | PC_CALCULATE_SW | Character(1) | VARCHAR2(1) NOT NULL |
Project-level switch to allow auto-calculation to be turned off if enabled at the BU level.
D=Delay Calculations Until Save M=Manual R=Realtime Calculations |
| 17 | PC_PRJ_DEF_CALC_MT | Character(1) | VARCHAR2(1) NOT NULL |
Calculate The
D=Duration E=End Date S=Start Date |
| 18 | DAYS | Number(6,0) | INTEGER NOT NULL | Days |
| 19 | ERROR_MSG_TXT | Long Character(1000) | VARCHAR2(1000) | Error Message |