PC_PIV_BDACT_VW

(SQL View)
 Index Back

Budget Vs Actual Pivot View

Budget Vs Actual 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 , G.BUDGET_PERIOD , G.BEGIN_DT , G.END_DT , SUM(ACTUALBUD) , SUM(ACTUALCOST) , (SUM(ACTUALCOST) - SUM(ACTUALBUD)) , CASE WHEN SUM(ACTUALBUD) <> 0 THEN ((SUM(ACTUALCOST) - SUM(ACTUALBUD))/ SUM(ACTUALBUD)) * 100 ELSE 0 END , SUM(REVENUEBUD) , SUM(ACTUALREVENUE) , (SUM(ACTUALREVENUE) - SUM(ACTUALCOST)) FROM ( SELECT H.OPRID AS OPRID , A.BUSINESS_UNIT AS BUSINESS_UNIT , A.PROJECT_ID AS PROJECT_ID , A.ACTIVITY_ID AS ACTIVITY_ID , A.CURRENCY_CD , F.BUDGET_PERIOD , F.BEGIN_DT , F.END_DT , 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 , 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 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_SUMMARY_TBL A , PS_PROJECT B, PS_INSTALLATION_PC E, PS_CAL_BP_TBL F, PS_SET_CNTRL_REC G, PS_PC_OPRID_PRJACT H WHERE A.BUSINESS_UNIT = B.BUSINESS_UNIT AND A.PROJECT_ID = B.PROJECT_ID AND A.RESOURCE_AMOUNT <> 0 AND F.SETID = G.SETID AND G.RECNAME = 'CAL_BP_TBL' AND G.SETCNTRLVALUE = A.BUSINESS_UNIT AND F.CALENDAR_ID = E.PC_SUM_CAL_ID AND A.ACCOUNTING_DT = F.BEGIN_DT AND H.BUSINESS_UNIT = A.BUSINESS_UNIT AND H.PROJECT_ID = A.PROJECT_ID) G GROUP BY G.OPRID, G.BUSINESS_UNIT, G.PROJECT_ID, G.PROJECT_TYPE, G.ACTIVITY_ID, G.CURRENCY_CD, G.BUDGET_PERIOD, G.BEGIN_DT, G.END_DT

  • 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 BUDGET_PERIOD Character(8) VARCHAR2(8) NOT NULL Budget Period
    12 BEGIN_DT Date(10) DATE Begin Date
    13 END_DT Date(10) DATE End Date
    14 PC_AMOUNT_1 Signed Number(28,3) DECIMAL(26,3) NOT NULL Project Costing Amount field
    15 PC_AMOUNT_2 Signed Number(28,3) DECIMAL(26,3) NOT NULL Project Costing Amount Field
    16 PC_AMOUNT_3 Signed Number(28,3) DECIMAL(26,3) NOT NULL Project Costing Amount Field
    17 PC_AMOUNT_4 Signed Number(28,3) DECIMAL(26,3) NOT NULL Project Costing Amount Field
    18 PC_AMOUNT_5 Signed Number(28,3) DECIMAL(26,3) NOT NULL Project Costing Amount Field
    19 PC_AMOUNT_6 Signed Number(28,3) DECIMAL(26,3) NOT NULL Project Costing Amount Field
    20 PC_AMOUNT_7 Signed Number(28,3) DECIMAL(26,3) NOT NULL Project Costing Amount Field