PI_SCAN_LIST_VW

(SQL View)
 Index Back

PI Scan Scope List

View of assets in PI IDs scan scope.

SELECT A.PI_ID , A.PI_LINE_NUM , A.BUSINESS_UNIT , A.ASSET_ID , A.MATCH_STATUS , A.DESCR , A.TAG_NUMBER , A.SERIAL_ID , A.MODEL , A.LOCATION , A.AREA_ID , A.HAND_HELD_OPERATOR , A.MANUFACTURER , A.IP_ADDRESS , A.IP_ALIAS , A.SCAN_TYPE , A.LAST_SCAN_DTTM , A.PI_OVERWRITE_LOC , 'Y' , TO_CHAR(A.COMMENTS) FROM %Table(PI_SCAN) A WHERE A.SCAN_TYPE IN ('REG', 'MAN', 'NEW') UNION SELECT B.PI_ID , 0 , C.BUSINESS_UNIT , C.ASSET_ID , ' ' , C.DESCR , C.TAG_NUMBER , C.SERIAL_ID , C.MODEL , C.LOCATION , C.AREA_ID , ' ' , C.MANUFACTURER , C.IP_ADDRESS , C.IP_ALIAS , ' ' , %DateTimeNull , ' ' , B.SCAN_FLAG , TO_CHAR(NULL) FROM %Table(PI_SCAN_SCOPE) B , %Table(ASSET_ALL_VW) C WHERE B.BUSINESS_UNIT = C.BUSINESS_UNIT AND B.ASSET_ID = C.ASSET_ID AND B.SCAN_FLAG <> 'Y'

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 PI_ID Character(10) VARCHAR2(10) NOT NULL Physical Inventory ID
2 PI_LINE_NUM Number(8,0) INTEGER NOT NULL PI Line Number
3 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
4 ASSET_ID Character(12) VARCHAR2(12) NOT NULL Asset Identification
5 MATCH_STATUS Character(1) VARCHAR2(1) NOT NULL PI Match Status
0=None
1=Special Depr
2=Accelerated Depr
3=Initial Depr
A=All (I,O,U)
D=Discard
I=Inventory
O=Over
R=Retire
U=Under
6 DESCR Character(30) VARCHAR2(30) NOT NULL Description
7 TAG_NUMBER Character(12) VARCHAR2(12) NOT NULL Tag Number
8 SERIAL_ID Character(20) VARCHAR2(20) NOT NULL Serial ID
9 MODEL Character(30) VARCHAR2(30) NOT NULL Model
10 LOCATION Character(10) VARCHAR2(10) NOT NULL Location Code
11 AREA_ID Character(10) VARCHAR2(10) NOT NULL Area ID
12 HAND_HELD_OPERATOR Character(30) VARCHAR2(30) NOT NULL Scanned By
13 MANUFACTURER Character(60) VARCHAR2(60) NOT NULL Manufacturer Name
14 IP_ADDRESS Character(39) VARCHAR2(39) NOT NULL Internet Protocol Address
15 IP_ALIAS Character(35) VARCHAR2(35) NOT NULL Internet Address Alias/UNC
16 SCAN_TYPE Character(3) VARCHAR2(3) NOT NULL Scan Type
LS=Location Scan
MAN=Manual
NEW=New
REG=Regular
17 LAST_SCAN_DTTM DateTime(26) TIMESTAMP NOT NULL Asset Management - Last datetime the asset was scanned (QR Codes).
18 PI_OVERWRITE_LOC Character(1) VARCHAR2(1) NOT NULL Force Asset Location Update
N=No
Y=Yes
19 SCAN_FLAG Character(1) VARCHAR2(1) NOT NULL Scanned Flag

Default Value: N

20 COMMENTS Long Character CLOB Comment