AM_HGRID

(SQL View)
 Index Back

SELECT G.BUSINESS_UNIT , G.ASSET_ID , G.ASSET_TYPE , G.ASSET_SUBTYPE , G.COMPONENT_OF_ID , G.COMPONENT_OF_ID , G.DESCR , G.ASSET_STATUS , G.TAG_NUMBER , 0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,C.CUSTODIAN ,C.EMPLID ,C.DEPTID ,L.LOCATION ,G.REPAIRABLE_FLG ,G.REPAIR_STATUS ,G.ASSET_CRITICALITY ,G.VIN ,G.SERIAL_ID ,L.AREA_ID , G.FINANCIAL_ASSET_SW , CASE WHEN (CASE WHEN %Substring(G.DESCR_LONG ,101 ,154) IS NULL THEN 'N' ELSE 'Y' END) ='Y' THEN %Substring(G.DESCR_LONG ,1,97)||'...' ELSE G.DESCR_LONG END FROM PS_ASSET G , PS_ASSET_CUSTODIAN C , PS_ASSET_LOCATION L WHERE G.BUSINESS_UNIT = C.BUSINESS_UNIT AND G.ASSET_ID = C.ASSET_ID AND G.BUSINESS_UNIT = L.BUSINESS_UNIT AND G.ASSET_ID = L.ASSET_ID AND L.EFFDT = ( SELECT MAX(L2.EFFDT) FROM PS_ASSET_LOCATION L2 WHERE L2.BUSINESS_UNIT = L.BUSINESS_UNIT AND L2.ASSET_ID = L.ASSET_ID) AND L.EFFSEQ = ( SELECT MAX(L2.EFFSEQ) FROM PS_ASSET_LOCATION L2 WHERE L2.BUSINESS_UNIT = L.BUSINESS_UNIT AND L2.ASSET_ID = L.ASSET_ID AND L2.EFFDT = L.EFFDT) AND C.EFFDT = ( SELECT MAX(C2.EFFDT) FROM PS_ASSET_CUSTODIAN C2 WHERE C2.BUSINESS_UNIT = C.BUSINESS_UNIT AND C2.ASSET_ID = C.ASSET_ID) AND C.EFFSEQ = ( SELECT MAX(C2.EFFSEQ) FROM PS_ASSET_CUSTODIAN C2 WHERE C2.BUSINESS_UNIT = C.BUSINESS_UNIT AND C2.ASSET_ID = C.ASSET_ID AND C2.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

Prompt Table: AM_COMPONENT

3 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
4 ASSET_SUBTYPE Character(15) VARCHAR2(15) NOT NULL Asset Subtype
5 COMPONENT_OF_ID Character(12) VARCHAR2(12) NOT NULL Component of Asset

Prompt Table: AM_COMPONENT

6 COMPONENT_PREV Character(12) VARCHAR2(12) NOT NULL Component of Asset
7 DESCR Character(30) VARCHAR2(30) NOT NULL Description
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
9 TAG_NUMBER Character(12) VARCHAR2(12) NOT NULL Tag Number
10 LEVEL_NUM Number(3,0) SMALLINT NOT NULL Level
11 LEVEL1 Number(5,0) INTEGER NOT NULL Level 1
12 LEVEL2 Number(5,0) INTEGER NOT NULL Level 2
13 LEVEL3 Number(5,0) INTEGER NOT NULL Level 3
14 LEVEL4 Number(5,0) INTEGER NOT NULL Level 4
15 LEVEL5 Number(5,0) INTEGER NOT NULL Level 5
16 LEVEL6 Number(5,0) INTEGER NOT NULL Level 6
17 LEVEL7 Number(5,0) INTEGER NOT NULL Level 7
18 LEVEL8 Number(5,0) INTEGER NOT NULL Level 8
19 LEVEL9 Number(5,0) INTEGER NOT NULL Level 9
20 CUSTODIAN Character(30) VARCHAR2(30) NOT NULL Custodian
21 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID

Prompt Table: PERSONAL_DATA

22 DEPTID Character(10) VARCHAR2(10) NOT NULL Department

Prompt Table: DEPT_TBL

23 LOCATION Character(10) VARCHAR2(10) NOT NULL Location Code

Prompt Table: LOCATION_TBL

24 REPAIRABLE_FLG Character(1) VARCHAR2(1) NOT NULL Repairable Flag
25 REPAIR_STATUS Character(1) VARCHAR2(1) NOT NULL Repair Status
0=None
1=Needs Repair
2=In Repair
26 ASSET_CRITICALITY Character(2) VARCHAR2(2) NOT NULL Asset Criticality
27 VIN Character(18) VARCHAR2(18) NOT NULL VIN
28 SERIAL_ID Character(20) VARCHAR2(20) NOT NULL Serial ID
29 AREA_ID Character(10) VARCHAR2(10) NOT NULL Area ID

Prompt Table: AM_AREA_TBL

30 FINANCIAL_ASSET_SW Character(1) VARCHAR2(1) NOT NULL Capitalized Asset
N=Non Cap
Y=Capitalize
31 DESCR_LONG Character(254) VARCHAR2(254) NOT NULL Long Description