AM_MYASSET_FLVW

(SQL View)
 Index Back

Asset, custodian, location vw


SELECT A.BUSINESS_UNIT , A.ASSET_ID , A.DESCR ,A.FINANCIAL_ASSET_SW , A.TAG_NUMBER , A.SERIAL_ID , A.ASSET_STATUS , A.MODEL , A.MANUFACTURER , B.CUSTODIAN , B.EMPLID , B.DEPTID , %subrec(CF12_AN_SBR, B) , C.LOCATION FROM PS_ASSET A , PS_ASSET_CUSTODIAN B , PS_ASSET_LOCATION C WHERE A.BUSINESS_UNIT = B.BUSINESS_UNIT AND A.ASSET_ID = B.ASSET_ID AND C.BUSINESS_UNIT = A.BUSINESS_UNIT AND C.ASSET_ID = A.ASSET_ID AND A.ASSET_STATUS NOT IN ('D', 'T') AND B.effdt = ( SELECT MAX(d.effdt) FROM PS_ASSET_custodian d WHERE b.business_unit = d.business_unit AND b.asset_id = d.asset_id) AND B.EFFSEQ = ( SELECT MAX(G.EFFSEQ) FROM PS_ASSET_CUSTODIAN G WHERE B.BUSINESS_UNIT = G.BUSINESS_UNIT AND B.ASSET_ID = G.ASSET_ID AND B.EFFDT = G.EFFDT) AND C.effdt = ( SELECT MAX(e.effdt) FROM PS_ASSET_location e WHERE c.business_unit = e.business_unit AND c.asset_id = e.asset_id) AND C.EFFSEQ = ( SELECT MAX(H.EFFSEQ) FROM PS_ASSET_LOCATION H WHERE C.BUSINESS_UNIT = H.BUSINESS_UNIT AND C.ASSET_ID = H.ASSET_ID AND C.EFFDT = H.EFFDT)

  • Related Language Record: AM_MYASST_LN_VW

    • # PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
    1 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
    2 ASSET_ID Character(12) VARCHAR2(12) NOT NULL Asset Identification
    3 DESCR Character(30) VARCHAR2(30) NOT NULL Description
    4 FINANCIAL_ASSET_SW Character(1) VARCHAR2(1) NOT NULL Capitalized Asset
    N=Non Cap
    Y=Capitalize
    5 TAG_NUMBER Character(12) VARCHAR2(12) NOT NULL Tag Number
    6 SERIAL_ID Character(20) VARCHAR2(20) NOT NULL Serial ID
    7 ASSET_STATUS Character(1) VARCHAR2(1) NOT NULL Asset Status
    A=Received (Not in Service)
    B=Budgeted
    C=Commitment
    D=Disposed
    I=In Service
    M=Suspended
    R=Requisitioned
    T=Transferred
    W=Work In Progress
    8 MODEL Character(30) VARCHAR2(30) NOT NULL Model
    9 MANUFACTURER Character(60) VARCHAR2(60) NOT NULL Manufacturer Name
    10 CUSTODIAN Character(30) VARCHAR2(30) NOT NULL Custodian
    11 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
    12 DEPTID Character(10) VARCHAR2(10) NOT NULL Department
    13 OPERATING_UNIT Character(8) VARCHAR2(8) NOT NULL Operating Unit ChartField
    14 PRODUCT Character(6) VARCHAR2(6) NOT NULL Product ChartField
    15 FUND_CODE Character(5) VARCHAR2(5) NOT NULL Fund Code
    16 CLASS_FLD Character(5) VARCHAR2(5) NOT NULL Class Field
    17 PROGRAM_CODE Character(5) VARCHAR2(5) NOT NULL Program Code ChartField
    18 BUDGET_REF Character(8) VARCHAR2(8) NOT NULL Budget Reference
    19 AFFILIATE Character(5) VARCHAR2(5) NOT NULL Affiliate
    20 AFFILIATE_INTRA1 Character(10) VARCHAR2(10) NOT NULL IntraUnit Affiliate1
    21 AFFILIATE_INTRA2 Character(10) VARCHAR2(10) NOT NULL Operating Unit Affiliate
    22 CHARTFIELD1 Character(10) VARCHAR2(10) NOT NULL Expansion chartfield 1
    23 CHARTFIELD2 Character(10) VARCHAR2(10) NOT NULL Expansion Chartfield 2
    24 CHARTFIELD3 Character(10) VARCHAR2(10) NOT NULL Expansion Chartfield 3
    25 LOCATION Character(10) VARCHAR2(10) NOT NULL Location Code