PGM_ASSET_TEAM

(SQL View)
 Index Back

Asset View


SELECT AM.BUSINESS_UNIT , AM.ASSET_ID , AM.ASSET_TYPE , AM.ASSET_SUBTYPE , AM.TAG_NUMBER , AM.MFG_ID , AM.MODEL , AM.DESCR , LN.COST_RATE , LN.BILL_RATE , LN.CURRENCY_CD , LN.UNIT_OF_MEASURE , LN.EFFDT FROM PS_ASSET AM , PS_AM_SUBTYPE_LN LN WHERE AM.ASSET_STATUS NOT IN ('T', 'D') AND AM.GROUP_ASSET_FLAG <> 'G' AND LN.SETID = ( SELECT CNTRL.SETID FROM PS_SET_CNTRL_REC CNTRL WHERE CNTRL.SETCNTRLVALUE = AM.BUSINESS_UNIT AND CNTRL.REC_GROUP_ID = 'AM_15' AND CNTRL.RECNAME = 'AM_SUBTYPE_LN') AND LN.ASSET_TYPE = AM.ASSET_TYPE AND LN.ASSET_SUBTYPE = AM.ASSET_SUBTYPE AND LN.EFF_STATUS = 'A' AND LN.EFFDT = ( SELECT MAX (X.EFFDT) FROM PS_AM_SUBTYPE_LN X WHERE X.SETID = LN.SETID AND X.ASSET_TYPE = LN.ASSET_TYPE AND X.ASSET_SUBTYPE = LN.ASSET_SUBTYPE AND X.EFFDT <= %CurrentDateIn)

  • Related Language Record: PGM_AST_TEAM_LG

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

    Prompt Table: SP_BU_PC_NONVW

    2 ASSET_ID Character(12) VARCHAR2(12) NOT NULL Asset Identification
    3 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

    Prompt Table: ASSET_TYPE_SRCH

    4 ASSET_SUBTYPE Character(15) VARCHAR2(15) NOT NULL Asset Subtype

    Prompt Table: AM_SUBTYPE

    5 TAG_NUMBER Character(12) VARCHAR2(12) NOT NULL Tag Number
    6 MFG_ID Character(50) VARCHAR2(50) NOT NULL Manufacturer ID
    7 MODEL Character(30) VARCHAR2(30) NOT NULL Model

    Prompt Table: MFG_MODEL_VW

    8 DESCR Character(30) VARCHAR2(30) NOT NULL Description
    9 COST_RATE Number(27,3) DECIMAL(26,3) NOT NULL Cost Rate
    10 BILL_RATE Number(27,3) DECIMAL(26,3) NOT NULL Billing Rate
    11 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code
    12 UNIT_OF_MEASURE Character(3) VARCHAR2(3) NOT NULL Used on an approval rule set.
    MHR=Muti Hourly
    PER=Percentage
    SQF=Square Footage
    13 EFFDT Date(10) DATE Effective Date

    Default Value: %date