RF_AST_RECON_VW

(SQL View)
 Index Back

Asset Reconciliation View

This view contains matches from asset entries received from the Financial system along with the corresponding installed asset entries that they must be reconciled with.

SELECT B.BUSINESS_UNIT , B.ASSET_ID , A.SETID , A.INST_PROD_ID , B.TAG_NUMBER , A.ASSETTAG , A.SERIAL_ID , C.INST_PROD_STATUS , B.ASSET_SUBTYPE , B.COMPONENT_OF_ID , B.EMPLID , A.DEPTID , A.INV_ITEM_ID , B.AM_ASSET_TYPE , B.MFG_ID , B.MODEL , A.LOCATION , A.PRODUCT_ID , A.PERSON_ID FROM PS_RF_INST_PROD A , PS_RF_ASSET_RECON B , PS_RF_INST_PROD_ST C WHERE A.INST_PROD_TYPE = '001' AND A.SETID = B.SETID AND A.SETID = C.SETID AND A.INST_PROD_ID = C.INST_PROD_ID AND A.SERIAL_ID = ' ' AND B.STATUS = 'A' AND A.PRODUCT_ID = B.PRODUCT_ID AND ((A.BO_ID_CONTACT = B.BO_ID_CONTACT AND B.BO_ID_CONTACT <> 0) OR (A.DEPTID = B.DEPTID AND B.DEPTID <> ' ') OR (A.LOCATION = B.LOCATION AND B.LOCATION <> ' ')) AND A.INST_PROD_ID NOT IN ( SELECT INST_PROD_ID FROM PS_RF_IPRD_AM_MAP C WHERE A.SETID = C.SETID AND A.INST_PROD_ID = C.INST_PROD_ID)

# 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_RF_NONVW

2 ASSET_ID Character(12) VARCHAR2(12) NOT NULL Asset Identifier used in Contracts Integration
3 SETID Character(5) VARCHAR2(5) NOT NULL This field is used to store the value of SetID on various setup tables.
4 INST_PROD_ID Character(20) VARCHAR2(20) NOT NULL This field is used to represent the installed product ID (key field)
5 TAG_NUMBER Character(12) VARCHAR2(12) NOT NULL Tag Number
6 ASSETTAG Character(40) VARCHAR2(40) NOT NULL Asset Tag
7 SERIAL_ID Character(20) VARCHAR2(20) NOT NULL This field is used to represent the Serial ID of an item or installed product
8 INST_PROD_STATUS Character(4) VARCHAR2(4) NOT NULL Status of an installed product.
9 ASSET_SUBTYPE Character(15) VARCHAR2(15) NOT NULL This field is used in the problem tree in work management.
10 COMPONENT_OF_ID Character(12) VARCHAR2(12) NOT NULL Indicates the component to which the current asset belongs.
11 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
12 DEPTID Character(15) VARCHAR2(15) NOT NULL Department
13 INV_ITEM_ID Character(18) VARCHAR2(18) NOT NULL Item ID
14 AM_ASSET_TYPE Character(3) VARCHAR2(3) NOT NULL This field defines the asset types that are available
010=IT Hardware
020=IT Software
030=Plant
040=Equipment
050=Property
060=Fleet
070=Machinery
080=Furniture
090=Facility
100=Intangible
999=Other
15 MFG_ID Character(50) VARCHAR2(50) NOT NULL Key field to represent Manufacturer Identification number
16 MODEL Character(30) VARCHAR2(30) NOT NULL Model
17 LOCATION Character(15) VARCHAR2(15) NOT NULL This field stores the Location Code.
18 PRODUCT_ID Character(18) VARCHAR2(18) NOT NULL Product ID
19 PERSON_ID Character(15) VARCHAR2(15) NOT NULL Person ID