CA_WC_OPNPRJ_VW(SQL View) |
Index Back |
---|---|
Open ProjectsTo be used in Workcenter |
SELECT HDR.CONTRACT_NUM , CA_STATUS , CONTRACT_TYPE ,DTL.CONTRACT_LINE_NUM ,PROJ.PROJECT_ID ,HDR.BUSINESS_UNIT ,PROJ.BUSINESS_UNIT_PC ,HDR.DESCR ,DTL.DESCR ,PROJ1.DESCR ,PROJECT_STATUS , PROJECT_TYPE , GRANT_FLG ,HDR.SOLD_TO_CUST_ID , CUST.NAME1 , CONTRACT_ADMIN FROM PS_CA_CONTR_HDR HDR , PS_CUSTOMER CUST , PS_SET_CNTRL_GROUP GRP , PS_CA_DETAIL DTL , PS_CA_DETAIL_PROJ PROJ , PS_PROJECT PROJ1 , PS_PROJECT_STATUS STA WHERE HDR.SOLD_TO_CUST_ID = CUST.CUST_ID AND HDR.BUSINESS_UNIT = GRP.SETCNTRLVALUE AND GRP.REC_GROUP_ID = 'FS_23' AND GRP.SETID = CUST.SETID AND HDR.CA_RQST_SRC <> 'OND' AND HDR.CONTRACT_NUM = DTL.CONTRACT_NUM AND DTL.CONTRACT_NUM = PROJ.CONTRACT_NUM AND DTL.CONTRACT_LINE_NUM = PROJ.CONTRACT_LINE_NUM AND PROJ.PROJECT_ID = PROJ1.PROJECT_ID AND PROJ.PROJECT_ID = STA.PROJECT_ID AND PROJ1.BUSINESS_UNIT = STA.BUSINESS_UNIT AND PROJ1.PROJECT_ID = STA.PROJECT_ID AND CA_PROC_STATUS = 'C' AND EFF_STATUS = 'A' AND STA.EFFDT = ( SELECT MAX(EFFDT) FROM PS_PROJECT_STATUS STA1 WHERE STA1.BUSINESS_UNIT = STA.BUSINESS_UNIT AND STA1.PROJECT_ID =STA.PROJECT_ID AND STA1.EFFDT <= %CurrentDateIn) AND STA.EFFSEQ = ( SELECT MAX(EFFSEQ) FROM PS_PROJECT_STATUS STA2 WHERE STA2.BUSINESS_UNIT = STA.BUSINESS_UNIT AND STA2.PROJECT_ID =STA.PROJECT_ID AND STA2.EFFDT = STA.EFFDT) |
# | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
---|---|---|---|---|
1 | CONTRACT_NUM | Character(25) | VARCHAR2(25) NOT NULL | Contract |
2 | CA_STATUS | Character(24) | VARCHAR2(24) NOT NULL |
Contract Status
Prompt Table: %EDITTABLE |
3 | CONTRACT_TYPE | Character(15) | VARCHAR2(15) NOT NULL |
Contract Type
Prompt Table: CA_TYPE_TBL |
4 | CONTRACT_LINE_NUM | Number(3,0) | SMALLINT NOT NULL | Contract Line Num |
5 | PROJECT_ID | Character(15) | VARCHAR2(15) NOT NULL | Project Id ChartField |
6 | BUSINESS_UNIT | Character(5) | VARCHAR2(5) NOT NULL |
Business Unit
Prompt Table: SP_BU_CA_NONVW |
7 | BUSINESS_UNIT_PC | Character(5) | VARCHAR2(5) NOT NULL |
PC Business Unit
Prompt Table: SP_BU_PC_NONVW |
8 | DESCR | Character(30) | VARCHAR2(30) NOT NULL | Description |
9 | DESCR1 | Character(30) | VARCHAR2(30) NOT NULL | Descr |
10 | DESCR2 | Character(30) | VARCHAR2(30) NOT NULL | Descr2 |
11 | PROJECT_STATUS | Character(1) | VARCHAR2(1) NOT NULL |
Project Status
B=Budgeted C=Closed H=Hold O=Open P=Proposed Prompt Table: PROJ_STATUS_TBL |
12 | PROJECT_TYPE | Character(5) | VARCHAR2(5) NOT NULL |
Project Type
A=Activity G=Grant H=Phase P=Project S=Segment Prompt Table: PROJ_TYPE_TBL |
13 | 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 |
14 | SOLD_TO_CUST_ID | Character(15) | VARCHAR2(15) NOT NULL |
Sold To Customer
Prompt Table: CUST_SLD_VW |
15 | NAME1 | Character(40) | VARCHAR2(40) NOT NULL | Name 1 |
16 | CONTRACT_ADMIN | Character(40) | VARCHAR2(40) NOT NULL |
Contract Administrator defined on CABU.
Prompt Table: MEMBER_VW |