SERIAL_IDIT_SN_CUST_VW(SQL View) |
Index Back |
|---|---|
ITAM - SerialNbr CustodianITAM - View of IT Hardware assets' Serial IDs and their current effective-dated Custodian. |
| SELECT C.SERIAL_ID , C.BUSINESS_UNIT , C.ASSET_ID , C.TAG_NUMBER , C.NBR_OF_CPU , A.EMPLID FROM PS_ASSET_CUSTODIAN A , PS_ASSET C WHERE A.BUSINESS_UNIT = C.BUSINESS_UNIT AND A.ASSET_ID = C.ASSET_ID AND C.IN_PHY_USE = 'Y' AND A.EFFDT = ( SELECT MAX(B.EFFDT) FROM PS_ASSET_CUSTODIAN B WHERE A.BUSINESS_UNIT = B.BUSINESS_UNIT AND A.ASSET_ID = B.ASSET_ID AND B.EFFDT <= %CurrentDateIn) AND A.EFFSEQ = ( SELECT MAX(Y.EFFSEQ) FROM PS_ASSET_CUSTODIAN Y WHERE Y.BUSINESS_UNIT = A.BUSINESS_UNIT AND Y.ASSET_ID = A.ASSET_ID AND Y.EFFDT = A.EFFDT) AND EXISTS ( SELECT 'X' FROM PS_IT_SUBTYPE D WHERE D.AUTHORIZE_SFTWR = 'Y' AND C.ASSET_SUBTYPE = D.IT_SUBTYPE) |
| # | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
|---|---|---|---|---|
| 1 | SERIAL_ID |
Character(20) | VARCHAR2(20) NOT NULL | Serial ID |
| 2 | BUSINESS_UNIT | Character(5) | VARCHAR2(5) NOT NULL | Business Unit |
| 3 | ASSET_ID | Character(12) | VARCHAR2(12) NOT NULL | Asset Identification |
| 4 | TAG_NUMBER | Character(12) | VARCHAR2(12) NOT NULL | Tag Number |
| 5 | 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. |
| 6 | EMPLID | Character(11) | VARCHAR2(11) NOT NULL | Employee ID |