P6_WBS_ACTTX_VW

(SQL View)
 Index Back

P6 WBS-Actv Actl Cost Txn View

PSFT Project Activity total Actual Cost, aggregated per PC Transaction ID (level 2 view)

SELECT A.BUSINESS_UNIT , A.PROJECT_ID , A.ACTIVITY_ID , B.PC_TRANS_ID , SUM(A.RESOURCE_AMOUNT) FROM PS_P6_WBS_ACTCF_VW A , PS_PC_TRANS_ID_DTL B WHERE B.SETID = ( SELECT I.SETID FROM PS_SET_CNTRL_REC I WHERE I.SETCNTRLVALUE = A.BUSINESS_UNIT AND I.REC_GROUP_ID = 'PC_16' AND I.RECNAME = 'PC_TRANS_ID_DTL') AND B.DETAIL_NO = ( SELECT MIN(J.DETAIL_NO) FROM PS_PC_TRANS_ID_DTL J WHERE J.SETID = B.SETID AND J.PC_TRANS_ID = B.PC_TRANS_ID AND (((J.RESOURCE_TYPE <> '%' AND J.RESOURCE_TYPE = A.RESOURCE_TYPE) AND (J.RESOURCE_CATEGORY <> '%' AND J.RESOURCE_CATEGORY = A.RESOURCE_CATEGORY) AND (J.RESOURCE_SUB_CAT <> '%'AND J.RESOURCE_SUB_CAT = A.RESOURCE_SUB_CAT)) OR ((J.RESOURCE_TYPE <> '%' AND J.RESOURCE_TYPE = A.RESOURCE_TYPE) AND (J.RESOURCE_CATEGORY <> '%' AND J.RESOURCE_CATEGORY = A.RESOURCE_CATEGORY) AND J.RESOURCE_SUB_CAT = '%') OR ((J.RESOURCE_TYPE <> '%' AND J.RESOURCE_TYPE = A.RESOURCE_TYPE) AND J.RESOURCE_CATEGORY = '%' AND (J.RESOURCE_SUB_CAT <> '%'AND J.RESOURCE_SUB_CAT = A.RESOURCE_SUB_CAT)) OR ((J.RESOURCE_TYPE <> '%' AND J.RESOURCE_TYPE = A.RESOURCE_TYPE) AND J.RESOURCE_CATEGORY = '%' AND J.RESOURCE_SUB_CAT = '%') OR (J.RESOURCE_TYPE = '%' AND (J.RESOURCE_CATEGORY <> '%' AND J.RESOURCE_CATEGORY = A.RESOURCE_CATEGORY) AND (J.RESOURCE_SUB_CAT <> '%'AND J.RESOURCE_SUB_CAT = A.RESOURCE_SUB_CAT)) OR (J.RESOURCE_TYPE = '%' AND (J.RESOURCE_CATEGORY <> '%' AND J.RESOURCE_CATEGORY = A.RESOURCE_CATEGORY) AND J.RESOURCE_SUB_CAT = '%') OR (J.RESOURCE_TYPE = '%' AND J.RESOURCE_CATEGORY = '%' AND (J.RESOURCE_SUB_CAT <> '%'AND J.RESOURCE_SUB_CAT = A.RESOURCE_SUB_CAT)) OR (J.RESOURCE_TYPE = '%' AND J.RESOURCE_CATEGORY = '%' AND J.RESOURCE_SUB_CAT = '%'))) GROUP BY A.BUSINESS_UNIT, A.PROJECT_ID, A.ACTIVITY_ID, B.PC_TRANS_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 PC_TRANS_ID Character(10) VARCHAR2(10) NOT NULL Transaction Identifier
5 RESOURCE_AMOUNT Signed Number(28,3) DECIMAL(26,3) NOT NULL Transaction Amount