GM_MD_PRJACT_VW

(SQL View)
 Index Back

Associated Projects by Award

This view retrireves all the projects, activities for an award.

SELECT DTL.CONTRACT_NUM , DTL.BUSINESS_UNIT_PC , DTL.PROJECT_ID , PRJACT.ACTIVITY_ID , PRJ.START_DT , PRJ.END_DT , PRJACT.START_DT , PRJACT.END_DT FROM PS_CA_DETAIL_PROJ DTL , PS_PROJECT PRJ , PS_PROJECT_STATUS ST , PS_PROJ_ACTIVITY PRJACT WHERE DTL.BUSINESS_UNIT_PC = PRJ.BUSINESS_UNIT AND DTL.PROJECT_ID = PRJ.PROJECT_ID AND ST.BUSINESS_UNIT = PRJ.BUSINESS_UNIT AND ST.PROJECT_ID = PRJ.PROJECT_ID AND PRJACT.BUSINESS_UNIT = DTL.BUSINESS_UNIT_PC AND PRJACT.PROJECT_ID = DTL.PROJECT_ID AND PRJACT.ACTIVITY_ID = DTL.ACTIVITY_ID AND ST.EFFDT = ( SELECT MAX(ST1.EFFDT) FROM PS_PROJECT_STATUS ST1 WHERE ST1.BUSINESS_UNIT = ST.BUSINESS_UNIT AND ST1.PROJECT_ID = ST.PROJECT_ID AND ST1.EFFDT <=%CurrentDateIn) AND ST.EFFSEQ = ( SELECT MAX(ST2.EFFSEQ) FROM PS_PROJECT_STATUS ST2 WHERE ST2.BUSINESS_UNIT = ST.BUSINESS_UNIT AND ST2.PROJECT_ID = ST.PROJECT_ID AND ST2.EFFDT = ST.EFFDT)

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 CONTRACT_NUM Character(25) VARCHAR2(25) NOT NULL Contract
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 START_DT Date(10) DATE NOT NULL Start Date
6 END_DT Date(10) DATE NOT NULL End Date
7 START_DT_2 Date(10) DATE NOT NULL Activity Start Date
8 END_DT_2 Date(10) DATE NOT NULL Activity End Date