WM_ASSET_DET_VW(SQL View) |
Index Back |
|---|---|
Asset Detail ViewAsset Detail view for web services |
| SELECT A.BUSINESS_UNIT , A.ASSET_ID , A.ASSET_TYPE , A.ASSET_SUBTYPE , A.SERIAL_ID , A.TAG_NUMBER , A.VIN , A.DESCR , A.DESCR_LONG , A.MFG_ID , A.MODEL , A.COMPONENT_OF_ID , A.ASSET_CRITICALITY , B.LOCATION , L.LATITUDE , L.LONGITUDE , A.ASSET_RESOURCE_SW , CASE WHEN B.SYNCDTTM > A.SYNCDTTM THEN B.SYNCDTTM ELSE A.SYNCDTTM END FROM PS_WM_ASSETBASE_VW A , PS_ASSET_LOCATION B , PS_AM_LOCATION_TBL L WHERE B.BUSINESS_UNIT = A.BUSINESS_UNIT AND B.ASSET_ID = A.ASSET_ID AND B.EFFDT = ( SELECT MAX(C.EFFDT) FROM PS_ASSET_LOCATION C WHERE C.BUSINESS_UNIT = B.BUSINESS_UNIT AND C.ASSET_ID = B.ASSET_ID AND C.EFFDT <= %CurrentDateIn) AND B.EFFSEQ = ( SELECT MAX(D.EFFSEQ) FROM PS_ASSET_LOCATION D WHERE D.BUSINESS_UNIT = B.BUSINESS_UNIT AND D.ASSET_ID = B.ASSET_ID AND D.EFFDT = B.EFFDT) AND L.SETID = ( SELECT S.SETID FROM PS_SET_CNTRL_REC S WHERE S.RECNAME = 'AM_LOCATION_TBL' AND S.SETCNTRLVALUE = B.BUSINESS_UNIT) AND L.LOCATION = B.LOCATION AND %EffdtCheck(AM_LOCATION_TBL L2, L, %CurrentDateIn) AND L.EFF_STATUS = 'A' |
| # | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
|---|---|---|---|---|
| 1 |  BUSINESS_UNIT |
Character(5) | VARCHAR2(5) NOT NULL |
Business Unit
Prompt Table: SP_BU_AM_NONVW |
| 2 | ASSET_ID |
Character(12) | VARCHAR2(12) NOT NULL | Asset Identification |
| 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
Prompt Table: AM_SUBTYPE |
| 5 | SERIAL_ID | Character(20) | VARCHAR2(20) NOT NULL | Serial ID |
| 6 | TAG_NUMBER | Character(12) | VARCHAR2(12) NOT NULL | Tag Number |
| 7 | VIN | Character(18) | VARCHAR2(18) NOT NULL | VIN |
| 8 | DESCR | Character(30) | VARCHAR2(30) NOT NULL | Description |
| 9 | DESCR_LONG | Character(254) | VARCHAR2(254) NOT NULL | Long Description |
| 10 | MFG_ID | Character(50) | VARCHAR2(50) NOT NULL |
Manufacturer ID
Prompt Table: MANUFACTURER |
| 11 | MODEL | Character(30) | VARCHAR2(30) NOT NULL |
Model
Prompt Table: MFG_MODEL |
| 12 | COMPONENT_OF_ID | Character(12) | VARCHAR2(12) NOT NULL |
Component of Asset
Prompt Table: AM_COMPONENT |
| 13 | ASSET_CRITICALITY | Character(2) | VARCHAR2(2) NOT NULL |
Asset Criticality
Prompt Table: AM_CRIT_LVL |
| 14 | LOCATION | Character(10) | VARCHAR2(10) NOT NULL |
Location Code
Prompt Table: LOCATION_TBL |
| 15 | LATITUDE | Signed Number(12,7) | DECIMAL(10,7) NOT NULL | Latitude |
| 16 | LONGITUDE | Signed Number(12,7) | DECIMAL(10,7) NOT NULL | Longitude |
| 17 | ASSET_RESOURCE_SW | Character(1) | VARCHAR2(1) NOT NULL | Field to indicate that an Asset is used as a tool or resource |
| 18 | SYNCDTTM | DateTime(26) | TIMESTAMP | Synchronization Last Update Date Time |