PC_WC_PROJ_VW

(SQL View)
 Index Back

Update Wizard Projects View


SELECT A.BUSINESS_UNIT ,A.PROJECT_ID ,A.DESCR ,A.EFF_STATUS ,A.EFF_STATUS ,A.PROJECT_TYPE ,B.PROJECT_MANAGER , B.PROJ_ROLE ,D.PROJECT_STATUS ,E.PC_STATUS_PATH_FLG , C.NAME ,A.START_DT , A.END_DT , A.GRANT_FLG , A.PROJECT_USER1 ,A.PROJECT_USER2 ,A.PROJECT_USER3 ,A.PROJECT_USER4 ,A.PROJECT_USER5 ,A.PROJECT_USER_DT1 ,A.PROJECT_USER_DT2 ,A.PC_USER_CURRENCY ,A.PROJECT_USERAMT1 ,A.PROJECT_USERAMT2 ,A.PROJECT_USERAMT3 , LOC.LOCATION , LOC.DESCR , A.SUMMARY_PRJ , A.PC_PRJ_DEF_CALC_MT , A.PC_TEMPLATE_STATUS FROM PS_PROJECT A LEFT OUTER JOIN PS_PROJECT_MGR B ON (A.BUSINESS_UNIT = B.BUSINESS_UNIT AND A.PROJECT_ID = B.PROJECT_ID) LEFT OUTER JOIN PS_PERSONAL_DATA C ON B.PROJECT_MANAGER = C.EMPLID LEFT OUTER JOIN PS_PROJECT_STATUS D ON (D.BUSINESS_UNIT = A.BUSINESS_UNIT AND D.PROJECT_ID = A.PROJECT_ID) LEFT OUTER JOIN PS_PROJ_TYPE_TBL E ON (A.PROJECT_TYPE = E.PROJECT_TYPE) LEFT OUTER JOIN PS_PC_PROJ_LOC_VW LOC ON (A.BUSINESS_UNIT = LOC.BUSINESS_UNIT AND A.PROJECT_ID = LOC.PROJECT_ID) WHERE (B.EFFDT IS NULL OR B.EFFDT = ( SELECT MAX(B1.EFFDT) FROM PS_PROJECT_MGR B1 WHERE B1.BUSINESS_UNIT = B.BUSINESS_UNIT AND B1.PROJECT_ID = B.PROJECT_ID AND B1.EFFDT <= %CurrentDateIn)) AND(D.EFFDT IS NULL OR D.EFFDT = ( SELECT MAX(D1.EFFDT) FROM PS_PROJECT_STATUS D1 WHERE D1.BUSINESS_UNIT = D.BUSINESS_UNIT AND D1.PROJECT_ID = D.PROJECT_ID AND D1.EFFDT <= %CurrentDateIn)) AND (D.EFFSEQ IS NULL OR D.EFFSEQ = ( SELECT MAX(D2.EFFSEQ) FROM PS_PROJECT_STATUS D2 WHERE D2.BUSINESS_UNIT = D.BUSINESS_UNIT AND D2.PROJECT_ID = D.PROJECT_ID AND D2.EFFDT = D.EFFDT)) AND (E.SETID IS NULL OR E.SETID=( SELECT SETID FROM PS_SET_CNTRL_REC WHERE RECNAME='PROJ_TYPE_TBL'AND SETCNTRLVALUE = A.BUSINESS_UNIT)) AND (E.EFFDT IS NULL OR E.EFFDT = ( SELECT MAX(E1.EFFDT) FROM PS_PROJ_TYPE_TBL E1 WHERE E1.SETID = E.SETID AND E1.PROJECT_TYPE = E.PROJECT_TYPE AND E1.EFFDT <= %CurrentDateIn)) AND (E.EFF_STATUS IS NULL OR E.EFF_STATUS ='A') AND A.EFF_STATUS <> 'I'

  • Related Language Record: PC_WC_PROJ_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 DESCR Character(30) VARCHAR2(30) NOT NULL Description
    4 EFF_STATUS Character(1) VARCHAR2(1) NOT NULL Effective Status
    A=Active
    I=Inactive

    Default Value: A

    5 PROCESSING_STATUS Character(1) VARCHAR2(1) NOT NULL Processing status
    A=Active
    I=Inactive
    P=Pending
    6 PROJECT_TYPE Character(5) VARCHAR2(5) NOT NULL Project Type
    A=Activity
    G=Grant
    H=Phase
    P=Project
    S=Segment
    7 PROJECT_MANAGER Character(11) VARCHAR2(11) NOT NULL Project Manager
    8 PROJ_ROLE Character(15) VARCHAR2(15) NOT NULL Project Role
    9 PROJECT_STATUS Character(1) VARCHAR2(1) NOT NULL Project Status
    B=Budgeted
    C=Closed
    H=Hold
    O=Open
    P=Proposed
    10 PC_STATUS_PATH_FLG Character(1) VARCHAR2(1) NOT NULL Use Status Path
    11 NAME Character(50) VARCHAR2(50) NOT NULL Name
    12 START_DT Date(10) DATE Start Date
    13 END_DT Date(10) DATE End Date
    14 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
    15 PROJECT_USER1 Character(10) VARCHAR2(10) NOT NULL Project User 1
    16 PROJECT_USER2 Character(10) VARCHAR2(10) NOT NULL Project User 2
    17 PROJECT_USER3 Character(10) VARCHAR2(10) NOT NULL Project User 3
    18 PROJECT_USER4 Character(10) VARCHAR2(10) NOT NULL Project User 4
    19 PROJECT_USER5 Character(10) VARCHAR2(10) NOT NULL Project User 5
    20 PROJECT_USER_DT1 Date(10) DATE Date 1
    21 PROJECT_USER_DT2 Date(10) DATE Date 2
    22 PC_USER_CURRENCY Character(3) VARCHAR2(3) NOT NULL User Currency

    Prompt Table: CURRENCY_CD_TBL

    23 PROJECT_USERAMT1 Signed Number(17,2) DECIMAL(15,2) NOT NULL Amount 1
    24 PROJECT_USERAMT2 Signed Number(17,2) DECIMAL(15,2) NOT NULL Amount 2
    25 PROJECT_USERAMT3 Signed Number(17,2) DECIMAL(15,2) NOT NULL Amount 3
    26 LOCATION Character(10) VARCHAR2(10) NOT NULL Location Code
    27 DESCR1 Character(30) VARCHAR2(30) NOT NULL Descr
    28 SUMMARY_PRJ Character(1) VARCHAR2(1) NOT NULL An indicator to determine whether project is summary or detail.
    N=Detail Project
    Y=Program
    29 PC_PRJ_DEF_CALC_MT Character(1) VARCHAR2(1) NOT NULL Calculate The
    D=Duration
    E=End Date
    S=Start Date
    30 PC_TEMPLATE_STATUS Character(1) VARCHAR2(1) NOT NULL Template Status
    A=Active
    I=Inactive