Overview
This command will list the storage places of assets in the system.
Related tables
Table
|
Description
|
Primary key (PK)
|
Connection tables
|
ASHISTASSETSITE
|
Assets location history
|
CDHISTASSETSITE
|
ADALLUSERS
ASASSET
ASSITE
|
ASASSET
|
Assets
|
CDASSET
CDREVISION
|
ADCHECKLIST
ADCOMPANY
ADTEAM
ASCONTROLS
ASDEPRECIATION
ASGENCONFIG
ASSTATE
DCDOCUMENT
|
OBOBJECT
|
Item/Asset/Supply data based on revision
|
CDOBJECT
CDREVISION
|
ADTEAM
GNASSOC
GNREVISION
OBOBJECTGROUP
OBSUBREASON
|
OBOBJECTGROUP
|
Item/Asset/Supply
|
CDOBJECTGROUP
|
GNFAVORITE
|
OBOBJECTTYPE
|
Item/Asset/Supply type
|
CDOBJECTTYPE
|
ADMEASUNITY
ADTEAM
GNMASK
GNREVCONFIG
GNTYPEROLE
|
GNREVISION
|
Revision
|
CDREVISION
|
ADALLUSERS
GNACTIONASSOC
GNASSOC
GNREASON
GNREVCONFIG
GNREVISIONSTATUS
|
ASSITE
|
Location record
|
CDSITE
|
ADDEPARTMENT
|
ADALLUSERS
|
User
|
CDUSER
|
ADALLUSERS
ADUSEREXTERNALDATA
GNFAVORITE
|
ADDEPARTMENT
|
Department
|
CDDEPARTMENT
|
ADALLUSERS
ADDEPARTMENT
ADTEAM
COWORKSPACE
GNASSOC
GNFAVORITE
GNMAPPING
|
Oracle
SELECT
OBG.FGSTATUS,
AST.FGASSTATUS,
OBTYPE.IDOBJECTTYPE,
OBJ.IDOBJECT,
OBJ.NMOBJECT,
GNREV.IDREVISION,
ADU.NMUSER,
ASSI.IDSITE || ' - ' || ASSI.NMSITE NMSITE,
ASHI.NMCOORD,
ASHI.DTSITE,
ASHI.TMSITE
FROM ASHISTASSETSITE ASHI
INNER JOIN ASASSET AST ON (AST.CDASSET = ASHI.CDASSET)
INNER JOIN OBOBJECT OBJ ON (AST.CDASSET = OBJ.CDOBJECT AND AST.CDREVISION = OBJ.CDREVISION)
INNER JOIN OBOBJECTGROUP OBG ON (OBG.CDOBJECTGROUP = OBJ.CDOBJECT)
INNER JOIN OBOBJECTTYPE OBTYPE ON (OBTYPE.CDOBJECTTYPE = OBG.CDOBJECTTYPE)
INNER JOIN GNREVISION GNREV ON (GNREV.CDREVISION = AST.CDREVISION)
LEFT OUTER JOIN ASSITE ASSI ON (ASSI.CDSITE = ASHI.CDSITE)
LEFT OUTER JOIN ADUSER ADU ON (ADU.CDUSER = ASHI.CDUSERSITE)
LEFT JOIN ADDEPARTMENT ADPT ON (ADPT.CDDEPARTMENT = ASSI.CDCOMPANY)
WHERE
OBJ.FGTEMPLATE <> 1 /*NOTE 1*/
AND OBJ.FGCURRENT = 1 /*NOTE 2*/
AND (AST.FGASSTATUS IS NULL OR AST.FGASSTATUS <> 4) /*NOTE 3*/
AND ASHI.FGLASTSITE = 1 /*NOTE 4*/
|
Postgres
SELECT
OBG.FGSTATUS,
AST.FGASSTATUS,
OBTYPE.IDOBJECTTYPE,
OBJ.IDOBJECT,
OBJ.NMOBJECT,
GNREV.IDREVISION,
ADU.NMUSER,
ASSI.IDSITE || ' - ' || ASSI.NMSITE NMSITE,
ASHI.NMCOORD,
ASHI.DTSITE,
ASHI.TMSITE
FROM ASHISTASSETSITE ASHI
INNER JOIN ASASSET AST ON (AST.CDASSET = ASHI.CDASSET)
INNER JOIN OBOBJECT OBJ ON (AST.CDASSET = OBJ.CDOBJECT AND AST.CDREVISION = OBJ.CDREVISION)
INNER JOIN OBOBJECTGROUP OBG ON (OBG.CDOBJECTGROUP = OBJ.CDOBJECT)
INNER JOIN OBOBJECTTYPE OBTYPE ON (OBTYPE.CDOBJECTTYPE = OBG.CDOBJECTTYPE)
INNER JOIN GNREVISION GNREV ON (GNREV.CDREVISION = AST.CDREVISION)
LEFT OUTER JOIN ASSITE ASSI ON (ASSI.CDSITE = ASHI.CDSITE)
LEFT OUTER JOIN ADUSER ADU ON (ADU.CDUSER = ASHI.CDUSERSITE)
LEFT JOIN ADDEPARTMENT ADPT ON (ADPT.CDDEPARTMENT = ASSI.CDCOMPANY)
WHERE
OBJ.FGTEMPLATE <> 1 /*NOTE 1*/
AND OBJ.FGCURRENT = 1 /*NOTE 2*/
AND (AST.FGASSTATUS IS NULL OR AST.FGASSTATUS <> 4) /*NOTE 3*/
AND ASHI.FGLASTSITE = 1 /*NOTE 4*/
|
SQL Server
SELECT
OBG.FGSTATUS,
AST.FGASSTATUS,
OBTYPE.IDOBJECTTYPE,
OBJ.IDOBJECT,
OBJ.NMOBJECT,
GNREV.IDREVISION,
ADU.NMUSER,
ASSI.IDSITE + ' - ' + ASSI.NMSITE NMSITE,
ASHI.NMCOORD,
ASHI.DTSITE,
ASHI.TMSITE
FROM ASHISTASSETSITE ASHI
INNER JOIN ASASSET AST ON (AST.CDASSET = ASHI.CDASSET)
INNER JOIN OBOBJECT OBJ ON (AST.CDASSET = OBJ.CDOBJECT AND AST.CDREVISION = OBJ.CDREVISION)
INNER JOIN OBOBJECTGROUP OBG ON (OBG.CDOBJECTGROUP = OBJ.CDOBJECT)
INNER JOIN OBOBJECTTYPE OBTYPE ON (OBTYPE.CDOBJECTTYPE = OBG.CDOBJECTTYPE)
INNER JOIN GNREVISION GNREV ON (GNREV.CDREVISION = AST.CDREVISION)
LEFT OUTER JOIN ASSITE ASSI ON (ASSI.CDSITE = ASHI.CDSITE)
LEFT OUTER JOIN ADUSER ADU ON (ADU.CDUSER = ASHI.CDUSERSITE)
LEFT JOIN ADDEPARTMENT ADPT ON (ADPT.CDDEPARTMENT = ASSI.CDCOMPANY)
WHERE
OBJ.FGTEMPLATE <> 1 /*OBS 1*/
AND OBJ.FGCURRENT = 1 /*OBS 2*/
AND (AST.FGASSTATUS IS NULL OR AST.FGASSTATUS <> 4) /*OBS 3*/
AND ASHI.FGLASTSITE = 1 /*OBS 4*/
|
Notes
▪1: The FGTEMPLATE field of the OBOBJECT table indicates whether the asset is a template, 1 – Yes; 2 – No; ▪2: FGCURRENT value 1 selects the last asset revision. ▪3: The FGSTATUS field of the ASASSET indicates the asset status, the value 4 is disabled. ▪4: The FGLASTSITE field of the ASASSET indicates the latest location of the asset.
Fields used on the view screen
#
|
Field ID
|
Type
|
Description
|
1
|
FGSTATUS
|
NUMERIC(2)
|
Object status:
1 – Issue
2 – Released
3 – Revision
4 – Cancelled
|
2
|
FGASSTATUS
|
NUMERIC(2)
|
Asset status:
1– Verification
2– Usage event
3– Maintenance event
4– Disabled
5– Available
6– Calibration event
7– Calibration in progress
8– Calibration approval
9– Nonconformity analysis
10– Maintenance
|
3
|
IDOBJECTTYPE
|
VARCHAR(50)
|
Asset type
|
4
|
IDOBJECT
|
VARCHAR(50)
|
Asset ID #
|
5
|
NMOBJECT
|
VARCHAR(255)
|
Asset name
|
6
|
IDREVISION
|
VARCHAR(50)
|
Revision
|
7
|
NMUSER
|
VARCHAR(255)
|
User
|
8
|
NMSITE
|
VARCHAR(255)
|
Locations
|
9
|
NMCOORD
|
VARCHAR(255)
|
Coordinates
|
10
|
DTSITE
|
DATETIME
|
Date
|
11
|
TMSITE
|
VARCHAR(8)
|
Time
|
|
