PC_WC_MGR_FL_VW

(SQL View)
 Index Back

Missing & Inactive Managers

This view has been created to fetch missing and inactive project managers for fluid Projects WorkCenter exception.

SELECT P.BUSINESS_UNIT , P.PROJECT_ID , P.DESCR , P.EFF_STATUS , P.START_DT , P.END_DT , P.PROJECT_TYPE , PT.DESCR , P.SUMMARY_PRJ , MP.OPRID , PM.PROJECT_MANAGER , PD.NAME , JB.EMPL_STATUS , PM.PROJ_ROLE , PR.DESCR , PM.START_DT , PM.END_DT , PM.SCHED_NUM , PS.PROJECT_STATUS , PST.DESCR FROM PS_PC_OPRID_PRJACT MP , PS_PROJECT P LEFT OUTER JOIN PS_PROJECT_STATUS PS ON (PS.BUSINESS_UNIT = P.BUSINESS_UNIT AND PS.PROJECT_ID = P.PROJECT_ID) LEFT OUTER JOIN PS_PROJ_STATUS_TBL PST ON (PST.PROJECT_STATUS = PS.PROJECT_STATUS) LEFT OUTER JOIN PS_PROJ_TYPE_TBL PT ON (P.PROJECT_TYPE = PT.PROJECT_TYPE AND PT.EFF_STATUS = 'A') LEFT OUTER JOIN PS_PROJECT_MGR PM ON (P.BUSINESS_UNIT = PM.BUSINESS_UNIT AND P.PROJECT_ID = PM.PROJECT_ID ) LEFT OUTER JOIN PS_PROJ_ROLE PR ON (PM.PROJ_ROLE = PR.PROJ_ROLE) LEFT OUTER JOIN PS_PERSONAL_DATA PD ON (PM.PROJECT_MANAGER = PD.EMPLID) LEFT OUTER JOIN PS_JOB JB ON (PM.PROJECT_MANAGER = JB.EMPLID) WHERE MP.BUSINESS_UNIT = P.BUSINESS_UNIT AND MP.PROJECT_ID = P.PROJECT_ID AND (P.EFF_STATUS IN ('A','P') OR (P.EFF_STATUS = 'T' AND P.PC_TEMPLATE_STATUS <> 'I')) AND P.END_DT >= %CurrentDateIn AND(PS.EFFDT = ( SELECT MAX(ST_ED.EFFDT) FROM PS_PROJECT_STATUS ST_ED WHERE PS.BUSINESS_UNIT = ST_ED.BUSINESS_UNIT AND PS.PROJECT_ID = ST_ED.PROJECT_ID AND ST_ED.EFFDT <= %CurrentDateIn) OR PS.EFFDT IS NULL) AND (PS.EFFSEQ = ( SELECT MAX(ST_ES.EFFSEQ) FROM PS_PROJECT_STATUS ST_ES WHERE PS.BUSINESS_UNIT = ST_ES.BUSINESS_UNIT AND PS.PROJECT_ID = ST_ES.PROJECT_ID AND PS.EFFDT = ST_ES.EFFDT) OR PS.EFFSEQ IS NULL) AND ((PST.SETID = ( SELECT SETID FROM PS_SET_CNTRL_REC PST_ED WHERE PST_ED.SETCNTRLVALUE = P.BUSINESS_UNIT AND PST_ED.RECNAME = 'PROJ_STATUS_TBL') OR PST.SETID IS NULL) AND (PST.EFFDT = ( SELECT MAX(D_ED.EFFDT) FROM PS_PROJ_STATUS_TBL D_ED WHERE PST.SETID = D_ED.SETID AND PST.PROJECT_STATUS = D_ED.PROJECT_STATUS AND D_ED.EFFDT <= %CurrentDateIn) OR PST.EFFDT IS NULL)) AND ((PT.SETID = ( SELECT SETID FROM PS_SET_CNTRL_REC C WHERE C.SETCNTRLVALUE = P.BUSINESS_UNIT AND C.RECNAME = 'PROJ_TYPE_TBL') OR PT.SETID IS NULL) AND (PT.EFFDT = ( SELECT MAX(A_ED.EFFDT) FROM PS_PROJ_TYPE_TBL A_ED WHERE PT.SETID = A_ED.SETID AND PT.PROJECT_TYPE = A_ED.PROJECT_TYPE AND A_ED.EFFDT <= %CurrentDateIn) OR PT.EFFDT IS NULL)) AND ((PM.EFFDT = ( SELECT MAX(C_EDT.EFFDT) FROM PS_PROJECT_MGR C_EDT WHERE PM.BUSINESS_UNIT = C_EDT.BUSINESS_UNIT AND PM.PROJECT_ID = C_EDT.PROJECT_ID AND C_EDT.EFFDT >= P.START_DT) AND PM.END_DT < P.END_DT ) OR PM.EFFDT IS NULL ) AND (PR.SETID = ( SELECT SETID FROM PS_SET_CNTRL_REC PR_ED WHERE PR_ED.SETCNTRLVALUE = P.BUSINESS_UNIT AND PR_ED.RECNAME = 'PROJ_ROLE') OR PR.SETID IS NULL) AND ((JB.EFFDT = ( SELECT MAX(B2.EFFDT) FROM PS_JOB B2 WHERE JB.EMPLID = B2.EMPLID AND JB.EMPL_RCD = B2.EMPL_RCD AND B2.EFFDT <= %CurrentDateIn ) OR JB.EFFDT IS NULL) AND (JB.EFFSEQ = ( SELECT MAX(B3.EFFSEQ) FROM PS_JOB B3 WHERE JB.EMPLID = B3.EMPLID AND JB.EMPL_RCD = B3.EMPL_RCD AND JB.EFFDT = B3.EFFDT) OR JB.EFFSEQ IS NULL) AND (JB.EMPL_RCD = ( SELECT MIN(B0.EMPL_RCD) FROM PS_JOB B0 WHERE JB.EMPLID = B0.EMPLID AND (B0.EFFDT = ( SELECT MAX(B1.EFFDT) FROM PS_JOB B1 WHERE B1.EMPLID = B0.EMPLID AND B1.EMPL_RCD = B0.EMPL_RCD AND B1.EFFDT <= %CurrentDateIn ) OR B0.EFFDT IS NULL) AND (B0.EFFSEQ = ( SELECT MAX(B4.EFFSEQ) FROM PS_JOB B4 WHERE B4.EMPLID = B0.EMPLID AND B4.EMPL_RCD = B0.EMPL_RCD AND B4.EFFDT = B0.EFFDT) OR B0.EFFSEQ IS NULL)) OR JB.EMPL_RCD IS NULL)) UNION SELECT P.BUSINESS_UNIT , P.PROJECT_ID , P.DESCR , P.EFF_STATUS , P.START_DT , P.END_DT , P.PROJECT_TYPE , PT.DESCR , P.SUMMARY_PRJ , MP.OPRID , PM.PROJECT_MANAGER , PD.NAME , JB.EMPL_STATUS , PM.PROJ_ROLE , PR.DESCR , PM.START_DT , PM.END_DT , PM.SCHED_NUM , PS.PROJECT_STATUS , PST.DESCR FROM PS_PC_OPRID_PRJACT MP , PS_PROJECT P LEFT OUTER JOIN PS_PROJECT_STATUS PS ON (PS.BUSINESS_UNIT = P.BUSINESS_UNIT AND PS.PROJECT_ID = P.PROJECT_ID) LEFT OUTER JOIN PS_PROJ_STATUS_TBL PST ON (PST.PROJECT_STATUS = PS.PROJECT_STATUS) LEFT OUTER JOIN PS_PROJ_TYPE_TBL PT ON (P.PROJECT_TYPE = PT.PROJECT_TYPE AND PT.EFF_STATUS = 'A') LEFT OUTER JOIN PS_PROJECT_MGR PM ON (PM.BUSINESS_UNIT = P.BUSINESS_UNIT AND PM.PROJECT_ID = P.PROJECT_ID ) LEFT OUTER JOIN PS_PROJ_ROLE PR ON (PM.PROJ_ROLE = PR.PROJ_ROLE) LEFT OUTER JOIN PS_PERSONAL_DATA PD ON (PM.PROJECT_MANAGER = PD.EMPLID) LEFT OUTER JOIN PS_JOB JB ON (PM.PROJECT_MANAGER = JB.EMPLID AND JB.EMPL_STATUS NOT IN ('A' , 'P' , 'L' , 'W')) WHERE MP.BUSINESS_UNIT = P.BUSINESS_UNIT AND MP.PROJECT_ID = P.PROJECT_ID AND (P.EFF_STATUS IN ('A','P') OR (P.EFF_STATUS = 'T' AND P.PC_TEMPLATE_STATUS <> 'I')) AND P.END_DT >= %CurrentDateIn AND (PS.EFFDT = ( SELECT MAX(ST_ED.EFFDT) FROM PS_PROJECT_STATUS ST_ED WHERE PS.BUSINESS_UNIT = ST_ED.BUSINESS_UNIT AND PS.PROJECT_ID = ST_ED.PROJECT_ID AND ST_ED.EFFDT <= %CurrentDateIn) OR PS.EFFDT IS NULL) AND (PS.EFFSEQ = ( SELECT MAX(ST_ES.EFFSEQ) FROM PS_PROJECT_STATUS ST_ES WHERE PS.BUSINESS_UNIT = ST_ES.BUSINESS_UNIT AND PS.PROJECT_ID = ST_ES.PROJECT_ID AND PS.EFFDT = ST_ES.EFFDT) OR PS.EFFSEQ IS NULL) AND ((PST.SETID = ( SELECT SETID FROM PS_SET_CNTRL_REC PST_ED WHERE PST_ED.SETCNTRLVALUE = P.BUSINESS_UNIT AND PST_ED.RECNAME = 'PROJ_STATUS_TBL') OR PST.SETID IS NULL) AND (PST.EFFDT = ( SELECT MAX(D_ED.EFFDT) FROM PS_PROJ_STATUS_TBL D_ED WHERE PST.SETID = D_ED.SETID AND PST.PROJECT_STATUS = D_ED.PROJECT_STATUS AND D_ED.EFFDT <= %CurrentDateIn) OR PST.EFFDT IS NULL)) AND ((PT.SETID = ( SELECT SETID FROM PS_SET_CNTRL_REC C WHERE C.SETCNTRLVALUE = P.BUSINESS_UNIT AND C.RECNAME = 'PROJ_TYPE_TBL') OR PT.SETID IS NULL) AND (PT.EFFDT = ( SELECT MAX(A_ED.EFFDT) FROM PS_PROJ_TYPE_TBL A_ED WHERE PT.SETID = A_ED.SETID AND PT.PROJECT_TYPE = A_ED.PROJECT_TYPE AND A_ED.EFFDT <= %CurrentDateIn) OR PT.EFFDT IS NULL)) AND (PM.EFFDT = ( SELECT MAX(B_ED.EFFDT) FROM PS_PROJECT_MGR B_ED WHERE PM.BUSINESS_UNIT = B_ED.BUSINESS_UNIT AND PM.PROJECT_ID = B_ED.PROJECT_ID AND B_ED.EFFDT >= P.START_DT) AND PM.END_DT >= %CurrentDateIn OR PM.EFFDT IS NULL) AND (PR.SETID = ( SELECT SETID FROM PS_SET_CNTRL_REC PR_ED WHERE PR_ED.SETCNTRLVALUE = P.BUSINESS_UNIT AND PR_ED.RECNAME = 'PROJ_ROLE') OR PR.SETID IS NULL) AND ((JB.EFFDT = ( SELECT MAX(B2.EFFDT) FROM PS_JOB B2 WHERE JB.EMPLID = B2.EMPLID AND JB.EMPL_RCD = B2.EMPL_RCD AND B2.EFFDT <= %CurrentDateIn) OR JB.EFFDT IS NULL) AND (JB.EFFSEQ = ( SELECT MAX(B3.EFFSEQ) FROM PS_JOB B3 WHERE JB.EMPLID = B3.EMPLID AND JB.EMPL_RCD = B3.EMPL_RCD AND JB.EFFDT = B3.EFFDT) OR JB.EFFSEQ IS NULL) AND (JB.EMPL_RCD = ( SELECT MIN(B0.EMPL_RCD) FROM PS_JOB B0 WHERE JB.EMPLID = B0.EMPLID AND B0.EMPL_STATUS NOT IN ('A', 'P', 'L', 'W') AND (B0.EFFDT = ( SELECT MAX(B1.EFFDT) FROM PS_JOB B1 WHERE B1.EMPLID = B0.EMPLID AND B1.EMPL_RCD = B0.EMPL_RCD AND B1.EFFDT <= %CurrentDateIn) OR B0.EFFDT IS NULL) AND (B0.EFFSEQ = ( SELECT MAX(B4.EFFSEQ) FROM PS_JOB B4 WHERE B4.EMPLID = B0.EMPLID AND B4.EMPL_RCD = B0.EMPL_RCD AND B4.EFFDT = B0.EFFDT) OR B0.EFFSEQ IS NULL)) OR JB.EMPL_RCD IS NULL))

  • Parent record: PROJECT

    • # 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 PROCESSING_STATUS Character(1) VARCHAR2(1) NOT NULL Processing status
    A=Active
    I=Inactive
    P=Pending
    5 START_DT Date(10) DATE NOT NULL Start Date
    6 END_DT Date(10) DATE End Date
    7 PROJECT_TYPE Character(5) VARCHAR2(5) NOT NULL Project Type
    A=Activity
    G=Grant
    H=Phase
    P=Project
    S=Segment
    8 DESCR1 Character(30) VARCHAR2(30) NOT NULL Descr
    9 SUMMARY_PRJ Character(1) VARCHAR2(1) NOT NULL An indicator to determine whether project is summary or detail.
    N=Detail Project
    Y=Program
    10 OPRID Character(30) VARCHAR2(30) NOT NULL A user's ID (see PSOPRDEFN).
    11 PROJECT_MANAGER Character(11) VARCHAR2(11) NOT NULL Project Manager
    12 NAME Character(50) VARCHAR2(50) NOT NULL Name
    13 EMPL_STATUS Character(1) VARCHAR2(1) NOT NULL Employee Status
    A=Active
    D=Deceased
    I=Inactive
    L=Leave of Absence
    P=Leave With Pay
    Q=Retired With Pay
    R=Retired
    S=Suspended
    T=Terminated
    U=Terminated With Pay
    V=Terminated Pension Pay Out
    W=Short Work Break
    X=Retired-Pension Administration
    14 PROJ_ROLE Character(15) VARCHAR2(15) NOT NULL Project Role
    15 DESCR2 Character(30) VARCHAR2(30) NOT NULL Descr2
    16 PLAN_STARTDT Date(10) DATE Planned Start Date
    17 PLAN_ENDDT Date(10) DATE Planned End Date
    18 SCHED_NUM Number(5,0) INTEGER NOT NULL Team sequence number
    19 PROJECT_STATUS Character(1) VARCHAR2(1) NOT NULL Project Status
    B=Budgeted
    C=Closed
    H=Hold
    O=Open
    P=Proposed

    Prompt Table: PC_WC_PRJSTS_VW

    20 DESCR3 Character(30) VARCHAR2(30) NOT NULL Description