PC_WC_ACT_VW

(SQL View)
 Index Back

Projects Activity Select View

Projects Activity Select View for Update Wizard

SELECT PRJ.BUSINESS_UNIT ,PRJ.PROJECT_ID ,PRJA.ACTIVITY_ID ,PRJ.DESCR ,PRJA.DESCR ,PRJA.EFF_STATUS ,PRJ.PROJECT_TYPE ,PRJA.ACTIVITY_TYPE ,PRJ.PROJECT_MANAGER , PRJ.SUMMARY_PRJ ,PRJ.PROJECT_STATUS ,E.ACTIVITY_STATUS , PRJ.PC_STATUS_PATH_FLG , STATA.PC_STATUS_PATH_FLG ,PRJ.START_DT ,PRJ.END_DT ,PRJA.START_DT ,PRJA.END_DT ,PRJA.ACTIVITY_USER1 ,PRJA.ACTIVITY_USER2 ,PRJA.ACTIVITY_USER3 ,PRJA.ACTIVITY_USER4 ,PRJA.ACTIVITY_USER5 ,PRJA.PC_ACT_USER_DT1 ,PRJA.PC_ACT_USER_DT2 ,PRJA.PC_USER_CURRENCY ,PRJA.PC_ACT_USERAMT1 ,PRJA.PC_ACT_USERAMT2 ,PRJA.PC_ACT_USERAMT3 , PRJ.GRANT_FLG FROM PS_PC_WC_PROJ_VW PRJ , PS_PROJ_ACTIVITY PRJA LEFT OUTER JOIN ( SELECT * FROM PS_PROJ_ACT_STATUS WHERE (EFFDT IS NULL OR EFFDT = ( SELECT MAX(E1.EFFDT) FROM PS_PROJ_ACT_STATUS E1 WHERE E1.BUSINESS_UNIT = PS_PROJ_ACT_STATUS.BUSINESS_UNIT AND E1.PROJECT_ID = PS_PROJ_ACT_STATUS.PROJECT_ID AND E1.ACTIVITY_ID = PS_PROJ_ACT_STATUS.ACTIVITY_ID AND E1.EFFDT <= %CurrentDateIn)) AND (EFFSEQ IS NULL OR EFFSEQ = ( SELECT MAX(E2.EFFSEQ) FROM PS_PROJ_ACT_STATUS E2 WHERE E2.BUSINESS_UNIT = PS_PROJ_ACT_STATUS.BUSINESS_UNIT AND E2.PROJECT_ID = PS_PROJ_ACT_STATUS.PROJECT_ID AND E2.ACTIVITY_ID = PS_PROJ_ACT_STATUS.ACTIVITY_ID AND E2.EFFDT <= PS_PROJ_ACT_STATUS.EFFDT))) E ON (PRJA.BUSINESS_UNIT = E.BUSINESS_UNIT AND PRJA.PROJECT_ID = E.PROJECT_ID AND PRJA.ACTIVITY_ID = E.ACTIVITY_ID) LEFT OUTER JOIN PS_PROJ_ACT_TBL STATA ON (PRJA.ACTIVITY_TYPE = STATA.ACTIVITY_TYPE) WHERE PRJ.BUSINESS_UNIT = PRJA.BUSINESS_UNIT AND PRJ.PROJECT_ID = PRJA.PROJECT_ID AND (STATA.SETID IS NULL OR STATA.SETID=( SELECT SETID FROM PS_SET_CNTRL_REC WHERE RECNAME='PROJ_ACT_TBL' AND SETCNTRLVALUE = PRJA.BUSINESS_UNIT)) AND (STATA.EFFDT IS NULL OR STATA.EFFDT = ( SELECT MAX(STATA1.EFFDT) FROM PS_PROJ_ACT_TBL STATA1 WHERE STATA1.SETID = STATA.SETID AND STATA1.ACTIVITY_TYPE = STATA.ACTIVITY_TYPE AND STATA1.EFFDT <= %CurrentDateIn)) AND (STATA.EFF_STATUS IS NULL OR STATA.EFF_STATUS ='A')

  • Related Language Record: PC_WC_ACT_LGVW

    • # 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 DESCR Character(30) VARCHAR2(30) NOT NULL Description
    5 DESCR1 Character(30) VARCHAR2(30) NOT NULL Descr
    6 EFF_STATUS Character(1) VARCHAR2(1) NOT NULL Effective Status
    A=Active
    I=Inactive
    7 PROJECT_TYPE Character(5) VARCHAR2(5) NOT NULL Project Type
    A=Activity
    G=Grant
    H=Phase
    P=Project
    S=Segment
    8 ACTIVITY_TYPE Character(5) VARCHAR2(5) NOT NULL Activity Type
    A=Activity Type
    E=Entry Type
    G=Group Type/Origin
    O=Activity Type/Origin
    R=Entry Type/Reason
    9 PROJECT_MANAGER Character(11) VARCHAR2(11) NOT NULL Project Manager
    10 SUMMARY_PRJ Character(1) VARCHAR2(1) NOT NULL An indicator to determine whether project is summary or detail.
    N=Detail Project
    Y=Program
    11 PROJECT_STATUS Character(1) VARCHAR2(1) NOT NULL Project Status
    B=Budgeted
    C=Closed
    H=Hold
    O=Open
    P=Proposed
    12 ACTIVITY_STATUS Character(1) VARCHAR2(1) NOT NULL Activity Status
    13 PC_STATUS_PATH_FLG Character(1) VARCHAR2(1) NOT NULL Use Status Path
    14 AC_STATUS_PATH_FLG Character(1) VARCHAR2(1) NOT NULL Use Status Path
    15 START_DT Date(10) DATE NOT NULL Start Date
    16 END_DT Date(10) DATE NOT NULL End Date
    17 START_DT_2 Date(10) DATE Start Date
    18 END_DT_2 Date(10) DATE End Date
    19 ACTIVITY_USER1 Character(10) VARCHAR2(10) NOT NULL User 1
    20 ACTIVITY_USER2 Character(10) VARCHAR2(10) NOT NULL User 2
    21 ACTIVITY_USER3 Character(10) VARCHAR2(10) NOT NULL User 3
    22 ACTIVITY_USER4 Character(10) VARCHAR2(10) NOT NULL User 4
    23 ACTIVITY_USER5 Character(10) VARCHAR2(10) NOT NULL User 5
    24 PC_ACT_USER_DT1 Date(10) DATE Date 1
    25 PC_ACT_USER_DT2 Date(10) DATE Date 2
    26 PC_USER_CURRENCY Character(3) VARCHAR2(3) NOT NULL User Currency

    Prompt Table: CURRENCY_CD_TBL

    27 PC_ACT_USERAMT1 Signed Number(17,2) DECIMAL(15,2) NOT NULL Amount 1
    28 PC_ACT_USERAMT2 Signed Number(17,2) DECIMAL(15,2) NOT NULL Amount 2
    29 PC_ACT_USERAMT3 Signed Number(17,2) DECIMAL(15,2) NOT NULL Amount 3
    30 GRANT_FLG Character(1) VARCHAR2(1) NOT NULL Grants Management Used for designating whether or not an object is for Grants or for Projects.
    N=Projects Object
    Y=Grants Object