PGM_ED_BDACT_VW

(SQL View)
 Index Back

Project Discovery Budg/Actuals

Budget and actuals info by project activities used for Project Discovery. Includes only active projects.

SELECT A.BUSINESS_UNIT , A.PROJECT_ID , B.ACTIVITY_ID , SUM((CASE WHEN A.AN_GRP_TOT_COSTS = L.ANALYSIS_GROUP AND B.ANALYSIS_TYPE = L.ANALYSIS_TYPE THEN B.RESOURCE_AMOUNT ELSE 0 END)) AS ACTUAL_AMOUNT , SUM((CASE WHEN A.AN_GRP_ACTV_BUD = L.ANALYSIS_GROUP AND B.ANALYSIS_TYPE = L.ANALYSIS_TYPE THEN B.RESOURCE_AMOUNT ELSE 0 END)) AS BUDGET_AMOUNT , SUM((CASE WHEN A.AN_GRP_ACTV_BUD = L.ANALYSIS_GROUP AND B.ANALYSIS_TYPE = L.ANALYSIS_TYPE AND B.ACCOUNTING_DT <= %CurrentDateIn THEN B.RESOURCE_AMOUNT ELSE 0 END)) AS BUD_TO_DATE , SUM((CASE WHEN A.AN_GRP_TOT_COSTS = L.ANALYSIS_GROUP AND B.ANALYSIS_TYPE = L.ANALYSIS_TYPE AND B.UNIT_OF_MEASURE = 'MHR' THEN B.RESOURCE_QUANTITY WHEN A.AN_GRP_TOT_COSTS = L.ANALYSIS_GROUP AND B.ANALYSIS_TYPE = L.ANALYSIS_TYPE AND B.UNIT_OF_MEASURE = 'MDY' THEN B.RESOURCE_QUANTITY * A.PC_HOURS_PER_DAY ELSE 0 END )) AS ACTUAL_HOURS , SUM((CASE WHEN A.AN_GRP_ACTV_BUD = L.ANALYSIS_GROUP AND B.ANALYSIS_TYPE = L.ANALYSIS_TYPE AND B.UNIT_OF_MEASURE = 'MHR' THEN B.RESOURCE_QUANTITY WHEN A.AN_GRP_ACTV_BUD = L.ANALYSIS_GROUP AND B.ANALYSIS_TYPE = L.ANALYSIS_TYPE AND B.UNIT_OF_MEASURE = 'MDY' THEN B.RESOURCE_QUANTITY * A.PC_HOURS_PER_DAY ELSE 0 END )) AS BUDGET_HOURS FROM PS_PROJECT A , PS_PC_SUMMARY_TBL B , PS_PROJ_AN_GRP_MAP L WHERE A.BUSINESS_UNIT = B.BUSINESS_UNIT AND A.PROJECT_ID = B.PROJECT_ID AND A.SET_OVERRIDE = L.SETID AND (A.AN_GRP_TOT_COSTS = L.ANALYSIS_GROUP OR A.AN_GRP_ACTV_BUD = L.ANALYSIS_GROUP) AND B.ANALYSIS_TYPE = L.ANALYSIS_TYPE AND A.EFF_STATUS = 'A' GROUP BY A.BUSINESS_UNIT , A.PROJECT_ID, B.ACTIVITY_ID

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
2 PROJECT_ID Character(15) VARCHAR2(15) NOT NULL Project Id ChartField
3 ACTIVITY_ID Character(15) VARCHAR2(15) NOT NULL Activity ID
4 ACTUAL_AMOUNT Signed Number(28,3) DECIMAL(26,3) NOT NULL Actual Invoice Amount
5 BUDGET_AMOUNT Signed Number(28,3) DECIMAL(26,3) NOT NULL Projects Budget Amount
6 BUDGETED_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Budgeted Assets
7 PC_ACTUAL_HRS Signed Number(16,2) DECIMAL(14,2) NOT NULL Actual Hours
8 PC_BUDGET_HRS Signed Number(16,2) DECIMAL(14,2) NOT NULL Budget Hours