PC_PIV_FNSUM_VW

(SQL View)
 Index Back

Project Fin Summary Pivot View

Project Fin Summary Pivot View

SELECT OPRID , BUSINESS_UNIT , PROJECT_ID , ACTIVITY_ID , PROJECT_TYPE , (%Coalesce (( SELECT NAME FROM PS_PC_PROJ_MGR_VW WHERE BUSINESS_UNIT = G.BUSINESS_UNIT AND PROJECT_ID = G.PROJECT_ID), 'NO MANAGER')) AS MANAGER_NAME , (%Coalesce (( SELECT DESCR FROM PS_PC_PROJ_LOC_VW WHERE BUSINESS_UNIT = G.BUSINESS_UNIT AND PROJECT_ID = G.PROJECT_ID), 'NO LOCATION')) AS LOCATION , (%Coalesce((SELECT NAME1 FROM PS_PC_CON_CUST_VW WHERE BUSINESS_UNIT = G.BUSINESS_UNIT AND PROJECT_ID = G.PROJECT_ID AND ACTIVITY_ID = G.ACTIVITY_ID) , 'NO CUSTOMER')) AS CUSTOMER_NAME , (%Coalesce((SELECT CONTRACT_NUM FROM PS_PC_CON_CUST_VW WHERE BUSINESS_UNIT = G.BUSINESS_UNIT AND PROJECT_ID = G.PROJECT_ID AND ACTIVITY_ID = G.ACTIVITY_ID), 'NO CONTRACT')) AS CONTRACT_NUM_NAME , G.CURRENCY_CD , SUM(ACTUALBUD) , (CASE WHEN ACTUALCOST <>0 THEN ACTUALCOST ELSE 0 END) , (CASE WHEN ACTUALCOST <>0 THEN ACTUALCOST ELSE 0 END) - SUM(ACTUALBUD) , %Round(CASE WHEN SUM(ACTUALBUD) <> 0 THEN (((CASE WHEN ACTUALCOST <>0 THEN ACTUALCOST ELSE 0 END) - SUM(ACTUALBUD) - SUM(ACTUALBUD))/ SUM(ACTUALBUD)) * 100 ELSE 0 END, 3) , SUM(REVENUEBUD) , SUM(ACTUALREVENUE) , (SUM(ACTUALREVENUE) - (CASE WHEN ACTUALCOST <>0 THEN ACTUALCOST ELSE 0 END) ) , G.PERCENT_COMPLETE FROM ( 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 ACTUALBUD ,( SELECT SUM((A1.RESOURCE_AMOUNT) * ( C1.SIGNED_VALUE)) FROM PS_PROJ_RESOURCE A1 , PS_PROJECT B1 , PS_PROJ_AN_GRP_MAP C1 , PS_SET_CNTRL_REC D1 WHERE A1.BUSINESS_UNIT = B1.BUSINESS_UNIT AND A1.PROJECT_ID = B1.PROJECT_ID AND D1.RECNAME = 'PROJ_AN_GRP_MAP' AND D1.SETCNTRLVALUE = A1.BUSINESS_UNIT AND C1.SETID = D1.SETID AND A1.ANALYSIS_TYPE = C1.ANALYSIS_TYPE AND C1.ANALYSIS_GROUP = B1.ANLTC_ACT_COST AND A1.BUSINESS_UNIT=A.BUSINESS_UNIT AND A1.PROJECT_ID=A.PROJECT_ID AND A1.ACTIVITY_ID = A.ACTIVITY_ID )AS ACTUALCOST , 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 REVENUEBUD , 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 ACTUALREVENUE 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 ) G GROUP BY G.OPRID, G.BUSINESS_UNIT, G.PROJECT_ID, G.PROJECT_TYPE, G.PERCENT_COMPLETE, G.ACTIVITY_ID, G.CURRENCY_CD,G.ACTUALCOST

  • Related Language Record: PC_PIVOT_LNG_VW

    • # 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 PROJECT_TYPE Character(5) VARCHAR2(5) NOT NULL Project Type
    A=Activity
    G=Grant
    H=Phase
    P=Project
    S=Segment
    6 PROJ_MGR_NAME Character(50) VARCHAR2(50) NOT NULL Project Manager Name
    7 PC_LOCATION_DESCR Character(30) VARCHAR2(30) NOT NULL Project Location Description
    8 PC_CUST_NAME Character(40) VARCHAR2(40) NOT NULL Project Customer Name
    9 CONTRACT_NUM Character(25) VARCHAR2(25) NOT NULL Contract
    10 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code
    11 PC_AMOUNT_1 Signed Number(28,3) DECIMAL(26,3) NOT NULL Project Costing Amount field
    12 PC_AMOUNT_2 Signed Number(28,3) DECIMAL(26,3) NOT NULL Project Costing Amount Field
    13 PC_AMOUNT_3 Signed Number(28,3) DECIMAL(26,3) NOT NULL Project Costing Amount Field
    14 PC_AMOUNT_4 Signed Number(28,3) DECIMAL(26,3) NOT NULL Project Costing Amount Field
    15 PC_AMOUNT_5 Signed Number(28,3) DECIMAL(26,3) NOT NULL Project Costing Amount Field
    16 PC_AMOUNT_6 Signed Number(28,3) DECIMAL(26,3) NOT NULL Project Costing Amount Field
    17 PC_AMOUNT_7 Signed Number(28,3) DECIMAL(26,3) NOT NULL Project Costing Amount Field
    18 PERCENT_COMPLETE Number(6,2) DECIMAL(5,2) NOT NULL % Complete