PC_PRQ_PROJ_VW

(SQL View)
 Index Back

Project Request View


SELECT HDR.PC_REQUEST_ID ,HDR.PC_REQUEST_TYPE ,HDR.PC_REQUEST_DESCR , A.BUSINESS_UNIT , A.PROJECT_ID , ( SELECT B.DESCR30 FROM PS_PC_REQUEST_TYPE B WHERE A.PC_REQUEST_TYPE = B.PC_REQUEST_TYPE AND %EffdtCheck(PC_REQUEST_TYPE C,B, %CURRENTDATEIN)) ,A.PROJECT_MANAGER,( SELECT NAME FROM PS_PERSONAL_DATA WHERE EMPLID = A.PROJECT_MANAGER ) ,HDR.PC_REQUESTOR,( SELECT OPRDEFNDESC FROM PSOPRDEFN WHERE OPRID = A.PC_REQUESTOR) ,A.BUDGETED_AMT ,A.CURRENCY_CD ,( SELECT VW.DESCR FROM PS_PC_PROJROLE_VW VW WHERE VW.PROJ_ROLE = A.PROJ_ROLE AND VW.SETID = ( SELECT SETID FROM PS_SET_CNTRL_REC WHERE SETCNTRLVALUE = A.BUSINESS_UNIT AND RECNAME = 'PC_PROJROLE_VW') ), ( SELECT VW.DESCR FROM PS_PC_WC_PRJTYP_VW VW WHERE VW.PROJECT_TYPE = A.PROJECT_TYPE AND VW.SETID = ( SELECT SETID FROM PS_SET_CNTRL_REC WHERE SETCNTRLVALUE = A.BUSINESS_UNIT AND RECNAME = 'PC_WC_PRJTYP_VW') ), A.DTTM_STAMP, HDR.PC_REQUEST_STATUS , START_DT, END_DT , ( SELECT A.PTAI_ANSWER FROM PS_PTAI_QUES_LIST A WHERE PTAI_QUESTION_ID ='PC_PRJ_REQ_Q0001_865959291' AND A.PTAI_LIST_ID = B.PTAI_LIST_ID),( SELECT A.PTAI_ANSWER FROM PS_PTAI_QUES_LIST A WHERE PTAI_QUESTION_ID ='PC_PRJ_REQ_Q0002_575347955' AND A.PTAI_LIST_ID = B.PTAI_LIST_ID), ( SELECT A.PTAI_ANSWER FROM PS_PTAI_QUES_LIST A WHERE PTAI_QUESTION_ID ='PC_PRJ_REQ_Q0003_50748687' AND A.PTAI_LIST_ID = B.PTAI_LIST_ID) FROM PS_PC_PRQ_HDR HDR, PS_PC_PRQ_PROJECT A , PS_PTAI_CONTEXT B WHERE %Join(COMMON_KEYS, PC_PRQ_HDR HDR, PC_PRQ_PROJECT A) AND EXISTS ( SELECT 'X' FROM PS_PTAI_CONTEXT B1 WHERE B1.PTAI_LIST_ID = B.PTAI_LIST_ID AND B1.FIELDNAME = 'PC_REQUEST_ID' AND B1.RECNAME = 'PC_PRQ_PROJ_VW' AND B1.PTAI_KEY_VALUE = HDR.PC_REQUEST_ID ) AND EXISTS ( SELECT 'X' FROM PS_PTAI_CONTEXT B1 WHERE B1.PTAI_LIST_ID = B.PTAI_LIST_ID AND B1.FIELDNAME = 'PC_REQUEST_TYPE' AND B1.RECNAME ='PC_PRQ_PROJ_VW' AND B1.PTAI_KEY_VALUE = HDR.PC_REQUEST_TYPE ) AND EXISTS ( SELECT 'X' FROM PS_PTAI_CONTEXT B1 WHERE B1.PTAI_LIST_ID = B.PTAI_LIST_ID AND B1.FIELDNAME = 'PC_REQUEST_DESCR' AND B1.RECNAME ='PC_PRQ_PROJ_VW' AND B1.PTAI_KEY_VALUE = HDR.PC_REQUEST_DESCR ) UNION SELECT HDR.PC_REQUEST_ID ,HDR.PC_REQUEST_TYPE ,HDR.PC_REQUEST_DESCR ,HDR.BUSINESS_UNIT ,HDR.PROJECT_ID , ( SELECT B.DESCR30 FROM PS_PC_REQUEST_TYPE B WHERE HDR.PC_REQUEST_TYPE = B.PC_REQUEST_TYPE AND %EffdtCheck(PC_REQUEST_TYPE C,B, %CURRENTDATEIN)) ,' ',' ',HDR.PC_REQUESTOR,( SELECT OPRDEFNDESC FROM PSOPRDEFN WHERE OPRID = HDR.PC_REQUESTOR) ,0 ,HDR.CURRENCY_CD, ' ',' ',HDR.DTTM_STAMP , HDR.PC_REQUEST_STATUS , CAST(NULL AS DATE), CAST(NULL AS DATE) , (SELECT A.PTAI_ANSWER FROM PS_PTAI_QUES_LIST A WHERE PTAI_QUESTION_ID ='PC_PRJ_REQ_Q0001_865959291' AND A.PTAI_LIST_ID = B.PTAI_LIST_ID),( SELECT A.PTAI_ANSWER FROM PS_PTAI_QUES_LIST A WHERE PTAI_QUESTION_ID ='PC_PRJ_REQ_Q0002_575347955' AND A.PTAI_LIST_ID = B.PTAI_LIST_ID), ( SELECT A.PTAI_ANSWER FROM PS_PTAI_QUES_LIST A WHERE PTAI_QUESTION_ID ='PC_PRJ_REQ_Q0003_50748687' AND A.PTAI_LIST_ID = B.PTAI_LIST_ID) FROM PS_PC_PRQ_HDR HDR , PS_PTAI_CONTEXT B WHERE EXISTS ( SELECT 'X' FROM PS_PTAI_CONTEXT B1 WHERE B1.PTAI_LIST_ID = B.PTAI_LIST_ID AND B1.FIELDNAME = 'PC_REQUEST_ID' AND B1.RECNAME = 'PC_PRQ_PROJ_VW' AND B1.PTAI_KEY_VALUE = HDR.PC_REQUEST_ID ) AND EXISTS ( SELECT 'X' FROM PS_PTAI_CONTEXT B1 WHERE B1.PTAI_LIST_ID = B.PTAI_LIST_ID AND B1.FIELDNAME = 'PC_REQUEST_TYPE' AND B1.RECNAME ='PC_PRQ_PROJ_VW' AND B1.PTAI_KEY_VALUE = HDR.PC_REQUEST_TYPE ) AND EXISTS ( SELECT 'X' FROM PS_PTAI_CONTEXT B1 WHERE B1.PTAI_LIST_ID = B.PTAI_LIST_ID AND B1.FIELDNAME = 'PC_REQUEST_DESCR' AND B1.RECNAME = 'PC_PRQ_PROJ_VW' AND B1.PTAI_KEY_VALUE = HDR.PC_REQUEST_DESCR ) AND NOT EXISTS( SELECT 'X' FROM PS_PC_PRQ_PROJECT A WHERE %Join(COMMON_KEYS, PC_PRQ_HDR HDR, PC_PRQ_PROJECT A))

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 PC_REQUEST_ID Character(15) VARCHAR2(15) NOT NULL Request Id

Default Value: NEXT

2 PC_REQUEST_TYPE Character(5) VARCHAR2(5) NOT NULL Request Type

Prompt Table: PC_RQST_TYP_VW

3 PC_REQUEST_DESCR Character(254) VARCHAR2(254) NOT NULL Request Description
4 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit

Prompt Table: SP_BU_PC_NONVW

5 PROJECT_ID Character(15) VARCHAR2(15) NOT NULL Project Id ChartField
6 DESCR30 Character(30) VARCHAR2(30) NOT NULL Request Type Description
7 PROJECT_MANAGER Character(11) VARCHAR2(11) NOT NULL Project Manager
8 NAME Character(50) VARCHAR2(50) NOT NULL Name
9 OPRID Character(30) VARCHAR2(30) NOT NULL A user's ID (see PSOPRDEFN).
10 OPRDEFNDESC Character(30) VARCHAR2(30) NOT NULL Description field for a user
11 BUDGETED_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Budgeted Amount
12 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code
13 DESCR3 Character(30) VARCHAR2(30) NOT NULL Project Role
14 DESCR1 Character(30) VARCHAR2(30) NOT NULL Project Type
15 DTTM_STAMP 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.
16 PC_REQUEST_STATUS Character(1) VARCHAR2(1) NOT NULL Request Status
A=Approved
B=Pushback
D=Denied
I=In Progress
P=Pending
17 START_DT Date(10) DATE Start Date
18 END_DT Date(10) DATE End Date
19 QUES_PROJECT Character(1) VARCHAR2(1) NOT NULL Project Request
N=No
Y=Yes
20 QUES_ACTIVITY Character(1) VARCHAR2(1) NOT NULL Activity Request
N=No
Y=Yes
21 QUES_TEAM Character(1) VARCHAR2(1) NOT NULL Team Request
N=No
Y=Yes