ASSET_SCHDLB_VW

(SQL View)
 Index Back

View of schedulable assets


SELECT A.BUSINESS_UNIT , A.ASSET_ID , A.TAG_NUMBER , A.SERIAL_ID , A.DESCR , A.DESCRSHORT , A.ASSET_TYPE , C.LOCATION FROM PS_ASSET A , PS_ASSET_LOCATION C WHERE A.ASSET_STATUS = 'I' AND A.SCHEDULABLE_SW = 'Y' AND A.BUSINESS_UNIT = C.BUSINESS_UNIT AND A.ASSET_ID = C.ASSET_ID 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)

# 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 TAG_NUMBER Character(12) VARCHAR2(12) NOT NULL Tag Number
4 SERIAL_ID Character(20) VARCHAR2(20) NOT NULL Serial ID
5 DESCR Character(30) VARCHAR2(30) NOT NULL Description
6 DESCRSHORT Character(10) VARCHAR2(10) NOT NULL Short Description
7 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
8 LOCATION Character(10) VARCHAR2(10) NOT NULL Location Code