RS_ASSETS_VW

(SQL View)
 Index Back

Asset View

Asset View to be used for ALM Asset search.

SELECT A.BUSINESS_UNIT , A.ASSET_ID , A.SERIAL_ID , A.TAG_NUMBER , A.DESCR , A.ASSET_STATUS , C.LOCATION , A.ASSET_STATUS , A.SCHEDULABLE_SW ,A.MODEL ,A.MFG_ID ,A.ASSET_TYPE , A.ASSET_SUBTYPE FROM %Table(ASSET) A , %Table(ASSET_LOCATION) C WHERE A.BUSINESS_UNIT = C.BUSINESS_UNIT AND A.ASSET_ID = C.ASSET_ID AND C.EFFDT = ( SELECT MAX(X.EFFDT) FROM %Table(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 %Table(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_AM Character(5) VARCHAR2(5) NOT NULL AM Business Unit

Prompt Table: SP_BU_AM_NONVW

2 ASSET_ID Character(12) VARCHAR2(12) NOT NULL Asset Identification
3 SERIAL_ID Character(20) VARCHAR2(20) NOT NULL Serial ID
4 TAG_NUMBER Character(12) VARCHAR2(12) NOT NULL Tag Number
5 DESCR Character(30) VARCHAR2(30) NOT NULL Description
6 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
7 LOCATION Character(10) VARCHAR2(10) NOT NULL Location Code
8 CATEGORY Character(5) VARCHAR2(5) NOT NULL Asset Category
9 SCHEDULABLE_SW Character(1) VARCHAR2(1) NOT NULL Schedulable Switch

Y/N Table Edit

10 MODEL Character(30) VARCHAR2(30) NOT NULL Model

Prompt Table: MFG_MODEL

11 MFG_ID Character(50) VARCHAR2(50) NOT NULL Manufacturer ID

Prompt Table: MANUFACTURER

12 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
13 ASSET_SUBTYPE Character(15) VARCHAR2(15) NOT NULL Asset Subtype

Prompt Table: AM_SUBTYPE