GM_PT_PROJCT_VW

(SQL View)
 Index Back

Project summary


SELECT DISTINCT C.OPRID , A.BUSINESS_UNIT_PC , A.PROJECT_ID , A.CONTRACT_NUM , A.BUSINESS_UNIT , A.PROJDESCR , A.PROJECT_STATUS , A.STATUS_DESCR , A.PROJECT_TYPE , A.START_DT , A.END_DT , A.EMPLID , A.NAME , A.DEPTID , A.DEPTNAME , A.DEPT_CONTACT_ID , E.TITLE56 , E.CUST_ID , E.CUST_NAME , A.FUNDED_AMT , A.BUDGETED_AMT , A.EXPENDED_AMT , A.ENCUMBERED_AMOUNT , A.PRE_ENCUMBERED_AMT , A.REMAINING_AMOUNT , A.BURN_RATE , A.BURN_RATE_INDC , A.AVG_MON_EXP_AMT , A.REMAINING_MONTHS , A.TOTAL_FORECAST_AMT , A.UNCOMMITED_AMT , A.UNCOM_PERCENTAGE , A.CURRENCY_CD , A.PROCESS_DTTM FROM PS_GM_PT_PROJECT A , PS_GM_PT_ACC_LVL3 B , PSOPRDEFN C , PS_GM_PT_AWARD E WHERE B.PROJECT_FLG = 'Y' AND (B.END_DT >= %CurrentDateIn OR B.END_DT IS NULL) AND B.BUSINESS_UNIT_PC = A.BUSINESS_UNIT_PC AND B.PROJECT_ID = A.PROJECT_ID AND B.BUSINESS_UNIT = A.BUSINESS_UNIT AND B.CONTRACT_NUM = A.CONTRACT_NUM AND C.EMPLID = B.EMPLID AND E.BUSINESS_UNIT = A.BUSINESS_UNIT AND E.CONTRACT_NUM = A.CONTRACT_NUM UNION SELECT DISTINCT C.OPRID , A.BUSINESS_UNIT_PC , A.PROJECT_ID , A.CONTRACT_NUM , A.BUSINESS_UNIT , A.PROJDESCR , A.PROJECT_STATUS , A.STATUS_DESCR , A.PROJECT_TYPE , A.START_DT , A.END_DT , A.EMPLID , A.NAME , A.DEPTID , A.DEPTNAME , A.DEPT_CONTACT_ID , ' ' , ' ' , ' ' , A.FUNDED_AMT , A.BUDGETED_AMT , A.EXPENDED_AMT , A.ENCUMBERED_AMOUNT , A.PRE_ENCUMBERED_AMT , A.REMAINING_AMOUNT , A.BURN_RATE , A.BURN_RATE_INDC , A.AVG_MON_EXP_AMT , A.REMAINING_MONTHS , A.TOTAL_FORECAST_AMT , A.UNCOMMITED_AMT , A.UNCOM_PERCENTAGE , A.CURRENCY_CD , A.PROCESS_DTTM FROM PS_GM_PT_PROJECT A , PS_GM_PT_ACC_LVL3 B , PSOPRDEFN C WHERE B.PROJECT_FLG = 'Y' AND (B.END_DT >= %CurrentDateIn OR B.END_DT IS NULL) AND B.BUSINESS_UNIT_PC = A.BUSINESS_UNIT_PC AND B.PROJECT_ID = A.PROJECT_ID AND C.EMPLID = B.EMPLID AND B.CONTRACT_NUM = ' '

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 OPRID Character(30) VARCHAR2(30) NOT NULL A user's ID (see PSOPRDEFN).
2 BUSINESS_UNIT_PC Character(5) VARCHAR2(5) NOT NULL PC Business Unit
3 PROJECT_ID Character(15) VARCHAR2(15) NOT NULL Project Id ChartField
4 CONTRACT_NUM Character(25) VARCHAR2(25) NOT NULL Contract
5 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
6 PROJDESCR Character(30) VARCHAR2(30) NOT NULL Project Name
7 PROJECT_STATUS Character(1) VARCHAR2(1) NOT NULL Project Status
B=Budgeted
C=Closed
H=Hold
O=Open
P=Proposed
8 STATUS_DESCR Character(30) VARCHAR2(30) NOT NULL Status Description
9 PROJECT_TYPE Character(5) VARCHAR2(5) NOT NULL Project Type
A=Activity
G=Grant
H=Phase
P=Project
S=Segment
10 BEGIN_DT Date(10) DATE Begin Date
11 END_DT Date(10) DATE End Date
12 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
13 NAME Character(50) VARCHAR2(50) NOT NULL Name
14 DEPTID Character(10) VARCHAR2(10) NOT NULL Department
15 DEPTNAME Character(30) VARCHAR2(30) NOT NULL Department

Prompt Table: GM_PT_DEPTNM_VW

16 GM_ADMIN_CNTCT Character(11) VARCHAR2(11) NOT NULL Grant Administrator
17 TITLE56 Character(56) VARCHAR2(56) NOT NULL TITLE56
18 CUST_ID Character(15) VARCHAR2(15) NOT NULL Uniquely identifies an organizational entity that purchases goods or services from the enterprise. Represents the sold-to customer which is the customer organization that places orders. The CUST_ID may or may not be the same as the identifiers for the bill-to and ship-to locations.
19 CUST_NAME Character(40) VARCHAR2(40) NOT NULL Name
20 FUNDED_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Funded Amount
21 BUDGETED_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Budgeted Assets
22 EXPENDED_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Expended Amount
23 ENCUMBERED_AMOUNT Signed Number(28,3) DECIMAL(26,3) NOT NULL Encumbered Amount
24 PRE_ENCUMBERED_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Pre Encumbered Amount
25 REMAINING_AMOUNT Signed Number(28,3) DECIMAL(26,3) NOT NULL Remaining Amount
26 BURN_RATE Signed Number(7,2) DECIMAL(5,2) NOT NULL Percentage
27 BURN_RATE_INDC Character(1) VARCHAR2(1) NOT NULL Burn Rate Indicator
H=High
L=Low
M=Medium
28 AVG_MON_EXP_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Average Monthly Burn Rate
29 REMAINING_MONTHS Signed Number(3,0) DECIMAL(2) NOT NULL Remaining Months
30 TOTAL_FORECAST_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Total Forecast
31 UNCOMMITED_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Uncommited Balance
32 UNCOM_PERCENTAGE Signed Number(12,2) DECIMAL(10,2) NOT NULL Uncommited Percentage
33 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code
34 PROCESS_DTTM DateTime(26) TIMESTAMP Process Date Time