PC_PIV_FNSM_VW1

(SQL View)
 Index Back

View created for sybase

View created for sybase DB to use in PC_PIV_FNSUM_VW view.

SELECT E.OPRID AS OPRID , A.BUSINESS_UNIT AS BUSINESS_UNIT , A.PROJECT_ID AS PROJECT_ID , A.ACTIVITY_ID AS ACTIVITY_ID , A.CURRENCY_CD , B.PERCENT_COMPLETE , CASE WHEN B.PROJECT_TYPE <> ' ' THEN B.PROJECT_TYPE ELSE 'NONE' END AS PROJECT_TYPE , CASE WHEN ( SELECT 'X' FROM PS_PROJ_AN_GRP_MAP C , PS_SET_CNTRL_REC D WHERE C.SETID = D.SETID AND C.ANALYSIS_GROUP = B.AN_GRP_ACTV_BUD AND C.ANALYSIS_TYPE = A.ANALYSIS_TYPE AND D.SETCNTRLVALUE = A.BUSINESS_UNIT AND D.RECNAME = 'PROJ_AN_GRP_MAP' ) = 'X' THEN A.RESOURCE_AMOUNT ELSE 0 END AS PC_AMOUNT_1 , CASE WHEN ( SELECT 'X' FROM PS_PROJ_AN_GRP_MAP C , PS_SET_CNTRL_REC D WHERE C.SETID = D.SETID AND C.ANALYSIS_GROUP = B.ANLTC_ACT_COST AND C.ANALYSIS_TYPE = A.ANALYSIS_TYPE AND D.SETCNTRLVALUE = A.BUSINESS_UNIT AND D.RECNAME = 'PROJ_AN_GRP_MAP' ) = 'X' THEN A.RESOURCE_AMOUNT ELSE 0 END AS PC_AMOUNT_2 , CASE WHEN ( SELECT 'X' FROM PS_PROJ_AN_GRP_MAP C , PS_SET_CNTRL_REC D WHERE C.SETID = D.SETID AND C.ANALYSIS_GROUP = B.PC_REV_BUD_AN_GRP AND C.ANALYSIS_TYPE = A.ANALYSIS_TYPE AND D.SETCNTRLVALUE = A.BUSINESS_UNIT AND D.RECNAME = 'PROJ_AN_GRP_MAP' ) = 'X' THEN A.RESOURCE_AMOUNT ELSE 0 END AS PC_AMOUNT_3 , CASE WHEN ( SELECT 'X' FROM PS_PROJ_AN_GRP_MAP C , PS_SET_CNTRL_REC D WHERE C.SETID = D.SETID AND C.ANALYSIS_GROUP = B.AN_GRP_TOT_REV AND C.ANALYSIS_TYPE = A.ANALYSIS_TYPE AND D.SETCNTRLVALUE = A.BUSINESS_UNIT AND D.RECNAME = 'PROJ_AN_GRP_MAP' ) = 'X' THEN A.RESOURCE_AMOUNT ELSE 0 END AS PC_AMOUNT_4 FROM PS_PC_ACTIVITY_SUM A , PS_PROJECT B, PS_PC_OPRID_PRJACT E WHERE A.BUSINESS_UNIT = B.BUSINESS_UNIT AND A.PROJECT_ID = B.PROJECT_ID AND A.RESOURCE_AMOUNT <> 0 AND E.BUSINESS_UNIT = A.BUSINESS_UNIT AND E.PROJECT_ID = A.PROJECT_ID

# 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 Character(5) VARCHAR2(5) NOT NULL Business Unit
3 PROJECT_ID Character(15) VARCHAR2(15) NOT NULL Project Id ChartField
4 ACTIVITY_ID Character(15) VARCHAR2(15) NOT NULL Activity ID
5 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code
6 PERCENT_COMPLETE Number(6,2) DECIMAL(5,2) NOT NULL % Complete
7 PROJECT_TYPE Character(5) VARCHAR2(5) NOT NULL Project Type
A=Activity
G=Grant
H=Phase
P=Project
S=Segment
8 PC_AMOUNT_1 Signed Number(28,3) DECIMAL(26,3) NOT NULL Project Costing Amount field
9 PC_AMOUNT_2 Signed Number(28,3) DECIMAL(26,3) NOT NULL Project Costing Amount Field
10 PC_AMOUNT_3 Signed Number(28,3) DECIMAL(26,3) NOT NULL Project Costing Amount Field
11 PC_AMOUNT_4 Signed Number(28,3) DECIMAL(26,3) NOT NULL Project Costing Amount Field