PI_SCAN_LST2_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.MANUFACTURER , A.IP_ADDRESS , A.IP_ALIAS , A.SCAN_TYPE , A.HAND_HELD_OPERATOR , P1.NAME , %DatePart(A.LAST_SCAN_DTTM) , A.PI_OVERWRITE_LOC , 'Y' , TO_CHAR(A.COMMENTS) FROM %Table(PI_SCAN) A , %Table(PERSONAL_DATA) P1 , %Table(PSOPRDEFN) P2 WHERE A.SCAN_TYPE IN ('REG', 'MAN', 'NEW') AND A.HAND_HELD_OPERATOR = P2.OPRID AND P1.EMPLID = P2.EMPLID 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 MANUFACTURER Character(60) VARCHAR2(60) NOT NULL Manufacturer Name
13 IP_ADDRESS Character(39) VARCHAR2(39) NOT NULL Internet Protocol Address
14 IP_ALIAS Character(35) VARCHAR2(35) NOT NULL Internet Address Alias/UNC
15 SCAN_TYPE Character(3) VARCHAR2(3) NOT NULL Scan Type
LS=Location Scan
MAN=Manual
NEW=New
REG=Regular
16 HAND_HELD_OPERATOR Character(30) VARCHAR2(30) NOT NULL Scanned By
17 NAME Character(50) VARCHAR2(50) NOT NULL Name
18 AM_PI_DATE Date(10) DATE Asset Date
19 PI_OVERWRITE_LOC Character(1) VARCHAR2(1) NOT NULL Force Asset Location Update
N=No
Y=Yes
20 SCAN_FLAG Character(1) VARCHAR2(1) NOT NULL Scanned Flag

Default Value: N

21 COMMENTS Long Character CLOB Comment