PROJ_ACT_TEAM_V

(SQL View)
 Index Back

Project Activity Team View

PROJ_ACT_TEAM view that selects Work Order eligible resources.

SELECT TEAM.BUSINESS_UNIT , TEAM.PROJECT_ID , TEAM.ACTIVITY_ID , TEAM.TEAM_MEMBER_CLASS , TEAM.TEAM_MEMBER_IND , TEAM.TEAM_MEMBER , TEAM.PROJ_ROLE , TEAM.SCHED_NUM , TEAM.SOURCE_FLAG , TEAM.ACT_UID , TEAM.DESCRLONG , TEAM.LASTUPDSOURCE , TEAM.USER_FIELD_1 , TEAM.UNIT_OF_MEASURE , TEAM.PGM_RATE_TYPE , TEAM.COST_RATE , TEAM.CURRENCY_CD , TEAM.COST , TEAM.PC_BUDGET_ITEM , TEAM.AVAIL_UNITS , TEAM.QTY , TEAM.ASSGND_WORK , TEAM.ACTUAL_WORK , TEAM.REMAINING_WORK , TEAM.BASELINE_WORK , TEAM.RESOURCE_NOTIFIED , TEAM.FMS_DTTM_STAMP , TEAM.FMS_OPRID , TEAM.FMS_LASTUPDDTTM , TEAM.FMS_LASTUPDOPRID , TEAM.BILL_RATE , TEAM.BILL_RATE_TYPE_1 , TEAM.BILL_AMT , TEAM.BILL_CURRENCY_CD , TEAM.ACT_ASSGN_DAYS , TEAM.ASSIGNED_TO_WO , 'N' , ' ' , PROJ_TEAM.BUSINESS_UNIT_AM , TEAM.ASSET_TYPE , TEAM.ASSET_SUBTYPE , TEAM.MFG_ID , TEAM.MODEL , ' ' FROM PS_PROJ_ACT_TEAM TEAM , PS_PROJECT_TEAM PROJ_TEAM WHERE TEAM.BUSINESS_UNIT = PROJ_TEAM.BUSINESS_UNIT AND TEAM.PROJECT_ID = PROJ_TEAM.PROJECT_ID AND TEAM.TEAM_MEMBER = PROJ_TEAM.TEAM_MEMBER AND TEAM.TEAM_MEMBER_CLASS = PROJ_TEAM.TEAM_MEMBER_CLASS AND TEAM.TEAM_MEMBER_IND = PROJ_TEAM.TEAM_MEMBER_IND AND ( (TEAM.TEAM_MEMBER_CLASS = 'L' AND TEAM.TEAM_MEMBER_IND = 'E' AND EXISTS ( SELECT 'X' FROM PS_RS_WRKR_EFFDT RS WHERE TEAM.TEAM_MEMBER = RS.EMPLID AND RS.SYSTEM_SOURCE = 'WM' AND EFFDT = ( SELECT MAX(EFFDT) FROM PS_RS_WRKR_EFFDT RS1 WHERE RS1.EMPLID = RS.EMPLID AND RS.SYSTEM_SOURCE = 'WM'))) OR (TEAM.TEAM_MEMBER_CLASS = 'A' AND TEAM.TEAM_MEMBER_IND = 'E' AND EXISTS ( SELECT 1 FROM PS_ASSET WHERE PS_ASSET.ASSET_RESOURCE_SW = 'Y' AND PS_ASSET.ASSET_ID = TEAM.TEAM_MEMBER AND PS_ASSET.BUSINESS_UNIT = PROJ_TEAM.BUSINESS_UNIT_AM)) OR (TEAM.TEAM_MEMBER_CLASS = 'M' AND TEAM.TEAM_MEMBER_IND ='E') OR (TEAM.TEAM_MEMBER_CLASS = 'L' AND TEAM.TEAM_MEMBER_IND = 'G' ) OR (TEAM.TEAM_MEMBER_CLASS = 'A' AND TEAM.TEAM_MEMBER_IND = 'N' ) OR (TEAM.TEAM_MEMBER_CLASS = 'M' AND TEAM.TEAM_MEMBER_IND = 'N') OR TEAM.TEAM_MEMBER_CLASS = 'O' )

  • Parent record: PROJ_ACTIVITY

    • # PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
    1 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit

    Default Value: PROJECT.BUSINESS_UNIT

    Prompt Table: SP_BU_PC_NONVW

    2 PROJECT_ID Character(15) VARCHAR2(15) NOT NULL Project Id ChartField

    Prompt Table: PROJECT_ALL_VW

    3 ACTIVITY_ID Character(15) VARCHAR2(15) NOT NULL Activity ID

    Prompt Table: PROJ_ACTIVITY

    4 TEAM_MEMBER_CLASS Character(1) VARCHAR2(1) NOT NULL 05/26/2004 - FS Translate Value "O" added.
    A=Asset
    L=Labor
    M=Material
    O=Other

    Default Value: L

    5 TEAM_MEMBER_IND Character(1) VARCHAR2(1) NOT NULL Team Member Indicator
    E=Employee
    G=Generic Resource
    N=Not Applicable

    Default Value: G

    6 TEAM_MEMBER Character(30) VARCHAR2(30) NOT NULL Team Member
    7 PROJ_ROLE Character(15) VARCHAR2(15) NOT NULL Project Role

    Prompt Table: PROJ_ROLE

    8 SCHED_NUM Number(5,0) INTEGER NOT NULL Team sequence number

    Default Value: 1

    Prompt Table: PROJ_TEAM_SCHED

    9 SOURCE_FLAG Character(1) VARCHAR2(1) NOT NULL Team member created from
    6=Primavera P6
    G=Proposal Management
    M=MSP
    P=Projects online
    R=Resource Management
    10 ACT_UID Character(15) VARCHAR2(15) NOT NULL Task ID
    11 DESCRLONG Long Character CLOB Long Description
    12 LASTUPDSOURCE Character(1) VARCHAR2(1) NOT NULL Team member source
    13 USER_FIELD_1 Character(10) VARCHAR2(10) NOT NULL User Field 1
    14 UNIT_OF_MEASURE Character(3) VARCHAR2(3) NOT NULL Used on an approval rule set.
    MHR=Muti Hourly
    PER=Percentage
    SQF=Square Footage

    Prompt Table: %EDIT_UOM

    15 PGM_RATE_TYPE Character(1) VARCHAR2(1) NOT NULL 06/15/2004 FS - Changed eff. dates to 1/1/1900 from 1/1/2000
    C=Override Rate
    E=Employee Rate
    J=Jobcode Rate
    R=Project Role Rate
    X=Custom Rate
    16 COST_RATE Number(27,3) DECIMAL(26,3) NOT NULL Cost Rate
    17 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code

    Default Value: PROJECT.CURRENCY_CD

    Prompt Table: CURRENCY_CD_TBL

    18 COST Signed Number(28,3) DECIMAL(26,3) NOT NULL Total Cost
    19 PC_BUDGET_ITEM Character(15) VARCHAR2(15) NOT NULL Project Budgeting: Budget Item 11/15/2004- FS - Added label COST_BUDGET_ITEM

    Prompt Table: %EDIT_BUD_CAT

    20 AVAIL_UNITS Number(6,0) INTEGER NOT NULL Availability Units

    Default Value: 100

    21 QTY Signed Number(17,4) DECIMAL(15,4) NOT NULL Quantity
    22 ASSGND_WORK Signed Number(16,2) DECIMAL(14,2) NOT NULL Assigned Work

    Default Value: 0

    23 ACTUAL_WORK Signed Number(16,2) DECIMAL(14,2) NOT NULL Actual Work
    24 REMAINING_WORK Signed Number(16,2) DECIMAL(14,2) NOT NULL New Remaining work in hours
    25 BASELINE_WORK Signed Number(16,2) DECIMAL(14,2) NOT NULL Baseline Work
    26 RESOURCE_NOTIFIED Character(1) VARCHAR2(1) NOT NULL Flag to indicate whether an activity resource has been notified of his/her assignment to the activity.
    N=Not Notified
    Y=Notified

    Y/N Table Edit

    Default Value: N

    27 FMS_DTTM_STAMP DateTime(26) TIMESTAMP Specifies the date and time of the original entry.
    28 FMS_OPRID Character(30) VARCHAR2(30) NOT NULL Specifies the User ID which made the original entry.
    29 FMS_LASTUPDDTTM DateTime(26) TIMESTAMP Specifies the date and time of the last update to an entry.
    30 FMS_LASTUPDOPRID Character(30) VARCHAR2(30) NOT NULL Specifies the User ID which made the last update to an entry.
    31 BILL_RATE Number(27,3) DECIMAL(26,3) NOT NULL Billing Rate
    32 BILL_RATE_TYPE_1 Character(1) VARCHAR2(1) NOT NULL 06/03/2004 - PR_FS_B005 Estimanting Labor PRL/ARL Changes
    C=Override Rate
    E=Employee Rate
    J=Jobcode Rate
    R=Project Role Rate
    X=Custom Rate
    33 BILL_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Bill Amount
    34 BILL_CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Bill Currency Code

    Default Value: PROJECT.CURRENCY_CD

    Prompt Table: CURRENCY_CD_TBL

    35 ACT_ASSGN_DAYS Number(8,2) DECIMAL(7,2) NOT NULL Actual Assignment Days
    36 ASSIGNED_TO_WO Character(1) VARCHAR2(1) NOT NULL An indicator that if a project activitity resource is copied over to a work order.
    N=No
    Y=Yes
    37 SELECT_RESOURCE Character(1) VARCHAR2(1) NOT NULL Resource selection.
    N=No
    Y=Yes
    38 GEN_RESOURCE Character(30) VARCHAR2(30) NOT NULL Generic Resource Name
    39 BUSINESS_UNIT_AM Character(5) VARCHAR2(5) NOT NULL AM Business Unit
    40 ASSET_TYPE Character(3) VARCHAR2(3) NOT NULL Asset Type
    010=IT Hardware
    020=IT Software
    040=Equipment
    050=Property
    060=Fleet
    070=Machinery
    080=Furniture
    090=Facility
    100=Intangible
    200=Asset Retirement Obligations
    999=Other
    41 ASSET_SUBTYPE Character(15) VARCHAR2(15) NOT NULL Asset Subtype

    Prompt Table: AM_SUBTYPE

    42 MFG_ID Character(50) VARCHAR2(50) NOT NULL Manufacturer ID
    43 MODEL Character(30) VARCHAR2(30) NOT NULL Model
    44 INVENTORY_ITEM Character(1) VARCHAR2(1) NOT NULL Inventory Item
    N=No
    Y=Yes

    Y/N Table Edit