AM_SS_FIND_VW

(SQL View)
 Index Back

Physical Inventory Find Asset

Used for Fluid Find Asset Results grid in Physical Inventory and Scan By Location tiles.

SELECT A.BUSINESS_UNIT , A.ASSET_ID , A.PARENT_ID , A.TAGGABLE_SW , A.TAG_NUMBER , A.DESCR , A.DESCRSHORT , A.ASSET_STATUS , A.FINANCIAL_ASSET_SW , A.ACQUISITION_CD , A.ACQUISITION_DT , A.PROFILE_ID , A.ASSET_CLASS , A.CAP_NUM , A.CAP_SEQUENCE , A.MANUFACTURER , A.MODEL , A.SERIAL_ID , A.LIFE , B.CUSTODIAN , B.EMPLID , %subrec(CF12_AN_SBR, B) , B.PROJECT_ID , B.DEPTID , C.LOCATION , A.DESCR_LONG , C.AREA_ID , A.ASSET_TYPE , A.ASSET_SUBTYPE , A.CAP_THRSHLD_ID ,A.PROD_VERSION ,A.PRODUCTION_DT FROM PS_ASSET A , PS_ASSET_CUSTODIAN B , PS_ASSET_LOCATION C WHERE A.BUSINESS_UNIT = B.BUSINESS_UNIT AND A.BUSINESS_UNIT = C.BUSINESS_UNIT AND A.ASSET_ID = B.ASSET_ID AND A.ASSET_ID = C.ASSET_ID AND A.ACQUISITION_CD <> 'L' AND B.EFFDT = ( SELECT MAX(Z.EFFDT) FROM PS_ASSET_CUSTODIAN Z WHERE Z.BUSINESS_UNIT = A.BUSINESS_UNIT AND Z.ASSET_ID = A.ASSET_ID AND Z.EFFDT <= %CurrentDateIn ) AND B.EFFSEQ = ( SELECT MAX(G.EFFSEQ) FROM PS_ASSET_CUSTODIAN G WHERE G.BUSINESS_UNIT = A.BUSINESS_UNIT AND G.ASSET_ID = A.ASSET_ID AND G.EFFDT = B.EFFDT ) AND C.EFFDT = ( SELECT MAX(X.EFFDT) FROM PS_ASSET_LOCATION X WHERE X.BUSINESS_UNIT = A.BUSINESS_UNIT AND X.ASSET_ID = A.ASSET_ID AND X.EFFDT <= %CurrentDateIn ) AND C.EFFSEQ = ( SELECT MAX(Y.EFFSEQ) FROM PS_ASSET_LOCATION Y WHERE Y.BUSINESS_UNIT = A.BUSINESS_UNIT AND Y.ASSET_ID = A.ASSET_ID AND Y.EFFDT = C.EFFDT )

  • Related Language Record: ASSET_LANG_VW

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

    Default Value: OPR_DEF_TBL_FS.BUSINESS_UNIT

    Prompt Table: SP_BU_AM_NONVW

    2 ASSET_ID Character(12) VARCHAR2(12) NOT NULL Asset Identification

    Default Value: NEXT

    3 PARENT_ID Character(12) VARCHAR2(12) NOT NULL Parent ID

    Prompt Table: PARENT_ASSET

    4 TAGGABLE_SW Character(1) VARCHAR2(1) NOT NULL Taggable Asset
    N=Non Taggable Asset
    Y=Taggable Asset
    5 TAG_NUMBER Character(12) VARCHAR2(12) NOT NULL Tag Number
    6 DESCR Character(30) VARCHAR2(30) NOT NULL Description
    7 DESCRSHORT Character(10) VARCHAR2(10) NOT NULL Short Description

    Default Value: ASSET.DESCR

    8 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

    Default Value: I

    9 FINANCIAL_ASSET_SW Character(1) VARCHAR2(1) NOT NULL Capitalized Asset
    N=Non Cap
    Y=Capitalize

    Y/N Table Edit

    Default Value: N

    10 ACQUISITION_CD Character(1) VARCHAR2(1) NOT NULL Acquisition Code
    C=Constructed
    D=Donated
    I=Trade In
    K=Like Exchange
    L=Leased
    P=Purchased
    T=Transferred

    Default Value: P

    11 ACQUISITION_DT Date(10) DATE Acquisition Date
    12 PROFILE_ID Character(10) VARCHAR2(10) NOT NULL Asset Profile ID

    Prompt Table: PROFILE_TBL

    13 ASSET_CLASS Character(10) VARCHAR2(10) NOT NULL Asset Class

    Prompt Table: ASSET_CLASS_TBL

    14 CAP_NUM Character(15) VARCHAR2(15) NOT NULL Capital Acquisition Plan #

    Prompt Table: CAP

    15 CAP_SEQUENCE Number(3,0) SMALLINT NOT NULL CAP Sequence

    Prompt Table: CAP_DET_OPEN_VW

    16 MANUFACTURER Character(60) VARCHAR2(60) NOT NULL Manufacturer Name

    Prompt Table: MANUF_FS_VW

    17 MODEL Character(30) VARCHAR2(30) NOT NULL Model
    18 SERIAL_ID Character(20) VARCHAR2(20) NOT NULL Serial ID
    19 LIFE Number(4,0) SMALLINT NOT NULL Useful Life
    20 CUSTODIAN Character(30) VARCHAR2(30) NOT NULL Custodian
    21 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
    22 OPERATING_UNIT Character(8) VARCHAR2(8) NOT NULL Operating Unit ChartField
    23 PRODUCT Character(6) VARCHAR2(6) NOT NULL Product ChartField
    24 FUND_CODE Character(5) VARCHAR2(5) NOT NULL Fund Code
    25 CLASS_FLD Character(5) VARCHAR2(5) NOT NULL Class Field
    26 PROGRAM_CODE Character(5) VARCHAR2(5) NOT NULL Program Code ChartField
    27 BUDGET_REF Character(8) VARCHAR2(8) NOT NULL Budget Reference
    28 AFFILIATE Character(5) VARCHAR2(5) NOT NULL Affiliate
    29 AFFILIATE_INTRA1 Character(10) VARCHAR2(10) NOT NULL IntraUnit Affiliate1
    30 AFFILIATE_INTRA2 Character(10) VARCHAR2(10) NOT NULL Operating Unit Affiliate
    31 CHARTFIELD1 Character(10) VARCHAR2(10) NOT NULL Expansion chartfield 1
    32 CHARTFIELD2 Character(10) VARCHAR2(10) NOT NULL Expansion Chartfield 2
    33 CHARTFIELD3 Character(10) VARCHAR2(10) NOT NULL Expansion Chartfield 3
    34 PROJECT_ID Character(15) VARCHAR2(15) NOT NULL Project Id ChartField
    35 DEPTID Character(10) VARCHAR2(10) NOT NULL Department
    36 LOCATION Character(10) VARCHAR2(10) NOT NULL Location Code
    37 DESCR_LONG Character(254) VARCHAR2(254) NOT NULL Long Description
    38 AREA_ID Character(10) VARCHAR2(10) NOT NULL Area ID

    Prompt Table: AM_AREA_TBL

    39 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
    40 ASSET_SUBTYPE Character(15) VARCHAR2(15) NOT NULL Asset Subtype
    41 CAP_THRSHLD_ID Character(10) VARCHAR2(10) NOT NULL Threshold ID
    42 PROD_VERSION Character(10) VARCHAR2(10) NOT NULL Product Version
    43 PRODUCTION_DT Date(10) DATE Production Date