PI_FIND_RS_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.REPLACE_SW , A.REPLACE_ASSET_ID , A.CONVERSION_ID , A.NEW_ASSET_SW , A.AVAIL_SW , A.AVAIL_CONTACT , A.AVAIL_PHONE , A.ACQUISITION_CD , A.ACQUISITION_DT , A.FINANCING_CD , A.REPLACEMENT_COST , A.REPLACE_COST_DT , A.LEASE_ASSET_ID , A.PROFILE_ID , A.ASSET_CLASS , A.CAP_NUM , A.CAP_SEQUENCE , A.MANUFACTURER , A.MODEL , A.PROD_VERSION , A.SERIAL_ID , A.PRODUCTION_DT , A.PLANT , A.MANUFACTUR_CONTACT , A.BUSINESS_UNIT_JV , A.INDEX_NAME , A.INDEX_DETAIL_NAME , A.LAST_INDEX_VALUE , A.COMPOSITE_SW , A.COMPOSITE_ID , A.FERC_CD , A.GROUP_ASSET_FLAG , A.CURRENCY_CD , %subrec(RD_SBR, A) , A.IP_ADDRESS , A.IP_ALIAS , A.COLLATERAL_ASSET , A.CLUSTERED_ASSET , A.LIFE , B.CUSTODIAN , B.EMPLID , %subrec(CF12_AN_SBR, B) , B.PROJECT_ID , B.DEPTID , B.OFFSITE_SW , B.AUTH_STATUS , B.AUTHORIZATION_NAME , B.AUTHORIZATION_DT , C.LOCATION , C.AUTH_STATUS , C.AUTHORIZATION_NAME , C.AUTHORIZATION_DT , C.DOCUMENT_ID , A.HAZARDOUS_SW , A.DESCR_LONG , C.AREA_ID , A.LINEAR_ASSET_SW , A.NBR_OF_CPU , A.ASSET_TYPE , A.ASSET_SUBTYPE , A.ARO_SW , A.ARO_ID , A.CAP_THRSHLD_ID , A.SES_LAST_DTTM , B.DEPTID , ' ' , ' ' , %DateTimeNull , ' ' 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 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) AND A.ASSET_ID NOT IN ( SELECT D.ASSET_ID FROM PS_PI_SCAN_HIST D WHERE D.BUSINESS_UNIT = A.BUSINESS_UNIT) UNION 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.REPLACE_SW , A.REPLACE_ASSET_ID , A.CONVERSION_ID , A.NEW_ASSET_SW , A.AVAIL_SW , A.AVAIL_CONTACT , A.AVAIL_PHONE , A.ACQUISITION_CD , A.ACQUISITION_DT , A.FINANCING_CD , A.REPLACEMENT_COST , A.REPLACE_COST_DT , A.LEASE_ASSET_ID , A.PROFILE_ID , A.ASSET_CLASS , A.CAP_NUM , A.CAP_SEQUENCE , A.MANUFACTURER , A.MODEL , A.PROD_VERSION , A.SERIAL_ID , A.PRODUCTION_DT , A.PLANT , A.MANUFACTUR_CONTACT , A.BUSINESS_UNIT_JV , A.INDEX_NAME , A.INDEX_DETAIL_NAME , A.LAST_INDEX_VALUE , A.COMPOSITE_SW , A.COMPOSITE_ID , A.FERC_CD , A.GROUP_ASSET_FLAG , A.CURRENCY_CD , %subrec(RD_SBR, A) , A.IP_ADDRESS , A.IP_ALIAS , A.COLLATERAL_ASSET , A.CLUSTERED_ASSET , A.LIFE , B.CUSTODIAN , B.EMPLID , %subrec(CF12_AN_SBR, B) , B.PROJECT_ID , B.DEPTID , B.OFFSITE_SW , B.AUTH_STATUS , B.AUTHORIZATION_NAME , B.AUTHORIZATION_DT , C.LOCATION , C.AUTH_STATUS , C.AUTHORIZATION_NAME , C.AUTHORIZATION_DT , C.DOCUMENT_ID , A.HAZARDOUS_SW , A.DESCR_LONG , C.AREA_ID , A.LINEAR_ASSET_SW , A.NBR_OF_CPU , A.ASSET_TYPE , A.ASSET_SUBTYPE , A.ARO_SW , A.ARO_ID , A.CAP_THRSHLD_ID , A.SES_LAST_DTTM , B.DEPTID , D.HAND_HELD_OPERATOR , D.SCAN_TYPE , D.LAST_SCAN_DTTM , D.PI_ID FROM PS_ASSET A , PS_ASSET_CUSTODIAN B , PS_ASSET_LOCATION C , PS_PI_SCAN_HIST D WHERE A.BUSINESS_UNIT = B.BUSINESS_UNIT AND A.BUSINESS_UNIT = C.BUSINESS_UNIT AND C.BUSINESS_UNIT = D.BUSINESS_UNIT AND A.ASSET_ID = B.ASSET_ID AND A.ASSET_ID = C.ASSET_ID AND C.ASSET_ID = D.ASSET_ID 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) AND D.LAST_SCAN_DTTM = ( SELECT MAX(H.LAST_SCAN_DTTM) FROM PS_PI_SCAN_HIST H WHERE H.BUSINESS_UNIT = A.BUSINESS_UNIT AND H.ASSET_ID = A.ASSET_ID)

  • 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 REPLACE_SW Character(1) VARCHAR2(1) NOT NULL Replacement Asset

    Y/N Table Edit

    Default Value: N

    11 REPLACE_ASSET_ID Character(12) VARCHAR2(12) NOT NULL Replaced Asset ID

    Prompt Table: ASSET

    12 CONVERSION_ID Character(15) VARCHAR2(15) NOT NULL Old Asset ID
    13 NEW_ASSET_SW Character(1) VARCHAR2(1) NOT NULL New Asset
    N=Used Asset
    Y=New Asset

    Default Value: Y

    14 AVAIL_SW Character(1) VARCHAR2(1) NOT NULL Asset is Available

    Y/N Table Edit

    Default Value: N

    15 AVAIL_CONTACT Character(30) VARCHAR2(30) NOT NULL Availability Contact
    16 AVAIL_PHONE Character(12) VARCHAR2(12) NOT NULL Availability Phone
    17 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

    18 ACQUISITION_DT Date(10) DATE Acquisition Date
    19 FINANCING_CD Character(5) VARCHAR2(5) NOT NULL Financing Code

    Prompt Table: FINANCE_CD_TBL

    20 REPLACEMENT_COST Signed Number(28,3) DECIMAL(26,3) NOT NULL Replacement Cost
    21 REPLACE_COST_DT Date(10) DATE Last Update
    22 LEASE_ASSET_ID Character(12) VARCHAR2(12) NOT NULL Lease Asset ID

    Prompt Table: LEASE_SRCH2

    23 PROFILE_ID Character(10) VARCHAR2(10) NOT NULL Asset Profile ID

    Prompt Table: PROFILE_TBL

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

    Prompt Table: ASSET_CLASS_TBL

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

    Prompt Table: CAP

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

    Prompt Table: CAP_DET_OPEN_VW

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

    Prompt Table: MANUF_FS_VW

    28 MODEL Character(30) VARCHAR2(30) NOT NULL Model
    29 PROD_VERSION Character(10) VARCHAR2(10) NOT NULL Product Version
    30 SERIAL_ID Character(20) VARCHAR2(20) NOT NULL Serial ID
    31 PRODUCTION_DT Date(10) DATE Production Date
    32 PLANT Character(30) VARCHAR2(30) NOT NULL Manufacturing Plant
    33 MANUFACTUR_CONTACT Character(30) VARCHAR2(30) NOT NULL Manufacturing Contact
    34 BUSINESS_UNIT_JV Character(5) VARCHAR2(5) NOT NULL Joint Venture BUS Unit
    35 INDEX_NAME Character(20) VARCHAR2(20) NOT NULL Index Name

    Prompt Table: AM_INDEX_TBL

    36 INDEX_DETAIL_NAME Character(35) VARCHAR2(35) NOT NULL SubIndex Name

    Prompt Table: AM_INDEX_DETAIL

    37 LAST_INDEX_VALUE Signed Number(28,3) DECIMAL(26,3) NOT NULL Last Index Value
    38 COMPOSITE_SW Character(1) VARCHAR2(1) NOT NULL Composite Asset

    Y/N Table Edit

    Default Value: N

    39 COMPOSITE_ID Character(12) VARCHAR2(12) NOT NULL Composite Asset ID

    Prompt Table: ASSET_COMPOS_VW

    40 FERC_CD Character(10) VARCHAR2(10) NOT NULL FERC Code

    Prompt Table: FERC_CD_TBL

    41 GROUP_ASSET_FLAG Character(1) VARCHAR2(1) NOT NULL Group Asset Flag
    G=Group Asset
    M=Group Member
    N=None

    Default Value: N

    42 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code

    Default Value: CURR_CD_AM_VW.CURRENCY_CD

    43 RD_PLANT_ASSET Character(1) VARCHAR2(1) NOT NULL R and D Plant Asset

    Y/N Table Edit

    44 RD_START_DT Date(10) DATE R and D Start Date
    45 RD_USE_NBV Character(1) VARCHAR2(1) NOT NULL Use NBV for R and D

    Y/N Table Edit

    46 IP_ADDRESS Character(39) VARCHAR2(39) NOT NULL Internet Protocol Address
    47 IP_ALIAS Character(35) VARCHAR2(35) NOT NULL Internet Address Alias/UNC
    48 COLLATERAL_ASSET Character(2) VARCHAR2(2) NOT NULL Collateral Asset
    01=Mortgage
    02=Fixed Mortgage
    03=Factory Foundation Mortgage
    04=Others
    49 CLUSTERED_ASSET Character(1) VARCHAR2(1) NOT NULL Clustered Asset

    Y/N Table Edit

    50 LIFE Number(4,0) SMALLINT NOT NULL Useful Life
    51 CUSTODIAN Character(30) VARCHAR2(30) NOT NULL Custodian
    52 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
    53 OPERATING_UNIT Character(8) VARCHAR2(8) NOT NULL Operating Unit ChartField
    54 PRODUCT Character(6) VARCHAR2(6) NOT NULL Product ChartField
    55 FUND_CODE Character(5) VARCHAR2(5) NOT NULL Fund Code
    56 CLASS_FLD Character(5) VARCHAR2(5) NOT NULL Class Field
    57 PROGRAM_CODE Character(5) VARCHAR2(5) NOT NULL Program Code ChartField
    58 BUDGET_REF Character(8) VARCHAR2(8) NOT NULL Budget Reference
    59 AFFILIATE Character(5) VARCHAR2(5) NOT NULL Affiliate
    60 AFFILIATE_INTRA1 Character(10) VARCHAR2(10) NOT NULL IntraUnit Affiliate1
    61 AFFILIATE_INTRA2 Character(10) VARCHAR2(10) NOT NULL Operating Unit Affiliate
    62 CHARTFIELD1 Character(10) VARCHAR2(10) NOT NULL Expansion chartfield 1
    63 CHARTFIELD2 Character(10) VARCHAR2(10) NOT NULL Expansion Chartfield 2
    64 CHARTFIELD3 Character(10) VARCHAR2(10) NOT NULL Expansion Chartfield 3
    65 PROJECT_ID Character(15) VARCHAR2(15) NOT NULL Project Id ChartField
    66 DEPTID Character(10) VARCHAR2(10) NOT NULL Department
    67 OFFSITE_SW Character(1) VARCHAR2(1) NOT NULL This Asset is Offsite

    Y/N Table Edit

    68 CUST_AUTH_STATUS Character(1) VARCHAR2(1) NOT NULL Custodian Authorization Status
    69 CUST_AUTH_NAME Character(30) VARCHAR2(30) NOT NULL Custodian Authorization Name
    70 CUST_AUTH_DT Date(10) DATE Custodian Authorization Date
    71 LOCATION Character(10) VARCHAR2(10) NOT NULL Location Code
    72 LOC_AUTH_STATUS Character(1) VARCHAR2(1) NOT NULL Location Authorization Status
    73 LOC_AUTH_NAME Character(30) VARCHAR2(30) NOT NULL Location Authorization Name
    74 LOC_AUTH_DT Date(10) DATE Location Authorization Date
    75 DOCUMENT_ID Character(12) VARCHAR2(12) NOT NULL Document
    76 HAZARDOUS_SW Character(1) VARCHAR2(1) NOT NULL Hazardous Asset
    77 DESCR_LONG Character(254) VARCHAR2(254) NOT NULL Long Description
    78 AREA_ID Character(10) VARCHAR2(10) NOT NULL Area ID

    Prompt Table: AM_AREA_TBL

    79 LINEAR_ASSET_SW Character(1) VARCHAR2(1) NOT NULL Linear Asset

    Y/N Table Edit

    80 NBR_OF_CPU Number(3,0) SMALLINT NOT NULL Used by ITAM. Servers and Desktops can have multiple CPUs. A separate Software License is needed for each CPU.
    81 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
    82 ASSET_SUBTYPE Character(15) VARCHAR2(15) NOT NULL Asset Subtype
    83 ARO_SW Character(1) VARCHAR2(1) NOT NULL Asset Retirement Obligations
    N=No
    Y=Yes
    84 ARO_ID Character(12) VARCHAR2(12) NOT NULL ARO Identification
    85 CAP_THRSHLD_ID Character(10) VARCHAR2(10) NOT NULL Threshold ID
    86 SES_LAST_DTTM DateTime(26) TIMESTAMP PTSF Last Modified DateTimestamp
    87 CUSTODIAN_DEPTID Character(10) VARCHAR2(10) NOT NULL Asset Custodian Deptid
    88 HAND_HELD_OPERATOR Character(30) VARCHAR2(30) NOT NULL Scanned By
    89 SCAN_TYPE Character(3) VARCHAR2(3) NOT NULL Scan Type
    LS=Location Scan
    MAN=Manual
    NEW=New
    REG=Regular
    90 LAST_SCAN_DTTM DateTime(26) TIMESTAMP Asset Management - Last datetime the asset was scanned (QR Codes).
    91 PI_ID Character(10) VARCHAR2(10) NOT NULL Physical Inventory ID