Visão geral
Este comando listará os dados de um ativo.
Tabelas relacionadas
Tabela
|
Descrição
|
Chave primária (PK)
|
Tabelas de ligação
|
OBOBJECT
|
Cadastro dos dados de item/ativo/insumo por revisão
|
CDOBJECT
CDREVISION
|
ADTEAM
GNASSOC
GNREVISION
OBOBJECTGROUP
OBSUBREASON
|
GNREVISION
|
Revisão
|
CDREVISION
|
ADALLUSERS
GNACTIONASSOC
GNASSOC
GNREASON
GNREVCONFIG
GNREVISIONSTATUS
|
OBOBJECTGROUP
|
Cadastro de item/ativo/insumo
|
CDOBJECTGROUP
|
GNFAVORITE
|
ADTEAM
|
Cadastro de equipe
|
CDTEAM
|
ADAUTHLDAPCONFIG
GNMAPPING
GNPERMISSION
|
OBOBJECTTYPE
|
Cadastro de tipo de item/ativo/insumo
|
CDOBJECTTYPE
|
ADMEASUNITY
ADTEAM
GNMASK
GNREVCONFIG
GNTYPEROLE
|
ASASSET
|
Cadastro de ativos
|
CDASSET
CDREVISION
|
ADCHECKLIST
ADCOMPANY
ADTEA
ASCONTROLS
ASDEPRECIATION
ASGENCONFIG
ASSTATE
DCDOCUMENT
|
ADCOMPANY
|
Empresa
|
CDCOMPANY
|
ADCOMPANYTYPE
|
DCDOCREVISION
|
Documento
|
CDDOCUMENT
CDREVISION
|
DCCATEGORY
DCDOCUMENT
EFREVISIONFORM
GNASSOC
GNCOMPFILECONTCOPY
GNREVISION
GNTRANSLATION
|
MAEQPT
|
Cadastro de equipamentos
|
CDEQPT
CDREVISION
|
ADALLUSERS
ADMAILTASKREL
ASASSET
MAEQPTMETERREAD
MAEQPTMETERRESTART
MAEQPTUNITY
|
GNRESOURCE
|
Cadastro de recursos
|
CDRESOURCE
|
ADALLUSERS
ADCOMPANY
GNASSOC
GNCALENDAR
GNRESOURCETYPE
OBOBJECTGROUP
|
GNCALENDAR
|
Calendário
|
CDCALENDAR
|
|
ASHISTASSETSITE
|
Cadastro do histórico das localizações dos ativos
|
CDHISTASSETSITE
|
ADALLUSERS
ASASSET
ASSITE
|
ASHISTASSETSTATE
|
Cadastro do histórico das condições do ativo
|
CDHISTASSETSTATE
|
ADALLUSERS
ASASSET
ASSTATE
|
GNTRANSLATIONLANGUAGE
|
Tradução
|
GNTRANSLATION
|
GNTRANSLATION
|
ADALLUSERS
|
Usuário
|
ADUSEREXTERNALDATA
GNFAVORITE
|
ADUSEREXTERNALDATA
GNFAVORITE
|
ADDEPARTMENT
|
Área
|
ADALLUSERS
ADDEPARTMENT
ADTEAM
COWORKSPACE
GNASSOC
GNFAVORITE
GNMAPPING
|
ADALLUSERS
ADDEPARTMENT
ADTEAM
COWORKSPACE
GNASSOC
GNFAVORITE
GNMAPPING
|
Oracle
SELECT
OBJ.IDOBJECT,
OBJ.NMOBJECT,
GNREV.IDREVISION,
GNREV.DTREVISION,
OBJTYPE.IDOBJECTTYPE || ' - ' || OBJTYPE.NMOBJECTTYPE NMOBJECTTYPE,
TEAM.IDTEAM || ' - ' || TEAM.NMTEAM NMTEAMRESPONSABLE,
TBL_LASTSTATE.IDSTATE || ' - ' || TBL_LASTSTATE.NMSTATE NMLASTSTATE,
OBJ.FGAPPLICATION,
ASAST.DTSTARTOPER,
ASAST.QTQUANTITY,
ASAST.QTAVAILABLE,
AST_CALENDAR.IDCALENDAR || ' - ' || AST_CALENDAR.NMCALENDAR NMCALENDAR,
ASAST.FGASSTATUS,
TBL_LASTSITE.IDSITE || ' - ' || TBL_LASTSITE.NMSITE LASTSITE_NMSITE,
TBL_LASTSITE.IDUSER || ' - ' || TBL_LASTSITE.NMUSER LASTSITE_NMUSER,
TBL_LASTSITE.NMCOORD,
ASAST.FGVIEWACCESS,
TEAMACCESS.IDTEAM || ' - ' || TEAMACCESS.NMTEAM NMTEAMVIEWACCESS,
ASAST.FGOWNER,
ADCMP.IDCOMMERCIAL || ' - ' || ADCMP.NMCOMPANY NMCOMPANYOWNER,
ADMNFT.IDCOMMERCIAL || ' - ' || ADMNFT.NMCOMPANY NMMANUFACTURER,
ADMSUPP.IDCOMMERCIAL || ' - ' || ADMSUPP.NMCOMPANY NMSUPPLIER,
ASAST.IDSERIALNUMBER,
ASAST.IDMODEL,
ASAST.FGRESOURCE,
TBL_CHKWARRANTYDOC.IDDOCUMENT || ' - ' || TBL_CHKWARRANTYDOC.NMTITLE NMWARRANTYDOC,
TBL_CHKWARRANTYDOC.DTREVISION DTDOCREV,
CASE TBL_CHKWARRANTYDOC.FGVALIDITY
WHEN 1 THEN (TBL_CHKWARRANTYDOC.DTREVISION + TBL_CHKWARRANTYDOC.QTVALIDITY)
WHEN 2 THEN (TBL_CHKWARRANTYDOC.DTREVISION + (TBL_CHKWARRANTYDOC.QTVALIDITY*30))
WHEN 3 THEN (TBL_CHKWARRANTYDOC.DTREVISION + (TBL_CHKWARRANTYDOC.QTVALIDITY*365))
ELSE NULL
END VIEW_DTDOCREVVALIDITY,
ASAST.DSDESCRIPTION,
MAEQUIPMENT.DSEQPT,
OBJ.DSOBSERVATION,
ASAST.FGAUTOACQUISITION,
ASAST.IDIPADDRESS,
ASAST.IDPORT,
ASAST.QTTIMEOUT
FROM OBOBJECT OBJ
INNER JOIN GNREVISION GNREV ON (OBJ.CDREVISION = GNREV.CDREVISION)
INNER JOIN OBOBJECTGROUP OBJGRP ON (OBJ.CDOBJECT = OBJGRP.CDOBJECTGROUP)
LEFT JOIN ADTEAM TEAM ON (OBJ.CDTEAMRESPONSABLE = TEAM.CDTEAM)
INNER JOIN OBOBJECTTYPE OBJTYPE ON (OBJTYPE.CDOBJECTTYPE = OBJGRP.CDOBJECTTYPE)
INNER JOIN ASASSET ASAST ON (ASAST.CDASSET = OBJ.CDOBJECT AND ASAST.CDREVISION = OBJ.CDREVISION)
LEFT JOIN ADTEAM TEAMACCESS ON (ASAST.CDTEAMVIEWACCESS = TEAMACCESS.CDTEAM)
LEFT JOIN ADCOMPANY ADCMP ON (ADCMP.CDCOMPANY = ASAST.CDCOMPANYOWNER)
LEFT JOIN ADCOMPANY ADMNFT ON (ADMNFT.CDCOMPANY = ASAST.CDMANUFACTURER)
LEFT JOIN ADCOMPANY ADMSUPP ON (ADMSUPP.CDCOMPANY = ASAST.CDSUPPLIER)
LEFT JOIN (
SELECT CHKDCREV.CDDOCUMENT,
CHKDCREV.CDREVISION,
CHKDCREV.IDDOCUMENT,
COALESCE(TRLG.NMTRANSLATION, CHKDCREV.NMTITLE) NMTITLE,
CHKGNREV.DTREVISION,
CHKGNREV.QTVALIDITY,
CHKGNREV.FGVALIDITY
FROM DCDOCREVISION CHKDCREV
LEFT JOIN GNREVISION CHKGNREV ON (CHKDCREV.CDREVISION = CHKGNREV.CDREVISION)
LEFT JOIN DCCATEGORY CHKDCCAT ON (CHKDCCAT.CDCATEGORY = CHKDCREV.CDCATEGORY)
LEFT JOIN GNTRANSLATIONLANGUAGE TRLG ON (CHKDCREV.CDTRANSLATION = TRLG.CDTRANSLATION AND TRLG.FGLANGUAGE = 2)
WHERE CHKDCCAT.FGCATEGORYTYPE = 4
AND CHKDCREV.FGCURRENT = 1
) TBL_CHKWARRANTYDOC ON (TBL_CHKWARRANTYDOC.CDDOCUMENT = ASAST.CDWARRANTYDOC)
LEFT JOIN MAEQPT MAEQUIPMENT ON (MAEQUIPMENT.CDEQPT = ASAST.CDASSET AND MAEQUIPMENT.CDREVISION = ASAST.CDREVISION)
LEFT JOIN GNRESOURCE AST_RESOURCE ON (AST_RESOURCE.CDASSET = ASAST.CDASSET)
LEFT JOIN GNCALENDAR AST_CALENDAR ON (AST_CALENDAR.CDCALENDAR = AST_RESOURCE.CDCALENDAR)
LEFT JOIN (
SELECT ALIAS_ASHISTSITE.CDASSET,
ALIAS_ASHISTSITE.CDREVISION,
ALIAS_ASHISTSITE.FGSITETYPE,
ALIAS_ASHISTSITE.NMCOORD,
ALIAS_ASSITE.CDSITE,
ALIAS_ASSITE.IDSITE,
ALIAS_ASSITE.NMSITE,
ALIAS_ASSITE.NMCOUNTRY,
ALIAS_ASSITE.NMSTATE,
ALIAS_ASSITE.NMCITY,
ALIAS_ASSITE.NMPOSTALCODE,
ALIAS_ASSITE.NMCOORD AS NMCOORD_SITE,
ALIAS_ASSITE.DSADDRESS,
ALIAS_ADDEP.IDDEPARTMENT,
ALIAS_ADDEP.NMDEPARTMENT,
ALIAS_ADUSER.CDUSER,
ALIAS_ADUSER.IDUSER,
ALIAS_ADUSER.NMUSER
FROM ASHISTASSETSITE ALIAS_ASHISTSITE
LEFT JOIN ASSITE ALIAS_ASSITE ON (ALIAS_ASSITE.CDSITE = ALIAS_ASHISTSITE.CDSITE)
LEFT JOIN ADUSER ALIAS_ADUSER ON (ALIAS_ADUSER.CDUSER = ALIAS_ASHISTSITE.CDUSERSITE)
LEFT JOIN ADDEPARTMENT ALIAS_ADDEP ON (ALIAS_ASSITE.CDCOMPANY = ALIAS_ADDEP.CDDEPARTMENT)
WHERE ALIAS_ASHISTSITE.FGLASTSITE = 1
) TBL_LASTSITE ON (TBL_LASTSITE.CDASSET = ASAST.CDASSET)
LEFT JOIN (
SELECT ALIAS_ASHISTSSTATE.CDASSET,
ALIAS_ASHISTSSTATE.CDREVISION,
ALIAS_ASSTATE.CDSTATE,
ALIAS_ASSTATE.IDSTATE,
ALIAS_ASSTATE.NMSTATE
FROM ASHISTASSETSTATE ALIAS_ASHISTSSTATE
LEFT JOIN ASSTATE ALIAS_ASSTATE ON (ALIAS_ASSTATE.CDSTATE = ALIAS_ASHISTSSTATE.CDSTATE)
WHERE ALIAS_ASHISTSSTATE.FGLASTSTATE = 1
) TBL_LASTSTATE ON (TBL_LASTSTATE.CDASSET = ASAST.CDASSET)
WHERE
OBJ.FGCURRENT = 1 /*OBS 1*/ AND
OBJ.IDOBJECT = 'IDENTIFICADOR_DO_ATIVO' /*OBS 2*/
|
Postgres
SELECT
OBJ.IDOBJECT,
OBJ.NMOBJECT,
GNREV.IDREVISION,
GNREV.DTREVISION,
OBJTYPE.IDOBJECTTYPE || ' - ' || OBJTYPE.NMOBJECTTYPE NMOBJECTTYPE,
TEAM.IDTEAM || ' - ' || TEAM.NMTEAM NMTEAMRESPONSABLE,
TBL_LASTSTATE.IDSTATE || ' - ' || TBL_LASTSTATE.NMSTATE NMLASTSTATE,
OBJ.FGAPPLICATION,
ASAST.DTSTARTOPER,
ASAST.QTQUANTITY,
ASAST.QTAVAILABLE,
AST_CALENDAR.IDCALENDAR || ' - ' || AST_CALENDAR.NMCALENDAR NMCALENDAR,
ASAST.FGASSTATUS,
TBL_LASTSITE.IDSITE || ' - ' || TBL_LASTSITE.NMSITE LASTSITE_NMSITE,
TBL_LASTSITE.IDUSER || ' - ' || TBL_LASTSITE.NMUSER LASTSITE_NMUSER,
TBL_LASTSITE.NMCOORD,
ASAST.FGVIEWACCESS,
TEAMACCESS.IDTEAM || ' - ' || TEAMACCESS.NMTEAM NMTEAMVIEWACCESS,
ASAST.FGOWNER,
ADCMP.IDCOMMERCIAL || ' - ' || ADCMP.NMCOMPANY NMCOMPANYOWNER,
ADMNFT.IDCOMMERCIAL || ' - ' || ADMNFT.NMCOMPANY NMMANUFACTURER,
ADMSUPP.IDCOMMERCIAL || ' - ' || ADMSUPP.NMCOMPANY NMSUPPLIER,
ASAST.IDSERIALNUMBER,
ASAST.IDMODEL,
ASAST.FGRESOURCE,
TBL_CHKWARRANTYDOC.IDDOCUMENT || ' - ' || TBL_CHKWARRANTYDOC.NMTITLE NMWARRANTYDOC,
TBL_CHKWARRANTYDOC.DTREVISION DTDOCREV,
CASE TBL_CHKWARRANTYDOC.FGVALIDITY
WHEN 1 THEN (TBL_CHKWARRANTYDOC.DTREVISION + TBL_CHKWARRANTYDOC.QTVALIDITY)
WHEN 2 THEN (TBL_CHKWARRANTYDOC.DTREVISION + (TBL_CHKWARRANTYDOC.QTVALIDITY*30))
WHEN 3 THEN (TBL_CHKWARRANTYDOC.DTREVISION + (TBL_CHKWARRANTYDOC.QTVALIDITY*365))
ELSE NULL
END VIEW_DTDOCREVVALIDITY,
ASAST.DSDESCRIPTION,
MAEQUIPMENT.DSEQPT,
OBJ.DSOBSERVATION,
ASAST.FGAUTOACQUISITION,
ASAST.IDIPADDRESS,
ASAST.IDPORT,
ASAST.QTTIMEOUT
FROM OBOBJECT OBJ
INNER JOIN GNREVISION GNREV ON (OBJ.CDREVISION = GNREV.CDREVISION)
INNER JOIN OBOBJECTGROUP OBJGRP ON (OBJ.CDOBJECT = OBJGRP.CDOBJECTGROUP)
LEFT JOIN ADTEAM TEAM ON (OBJ.CDTEAMRESPONSABLE = TEAM.CDTEAM)
INNER JOIN OBOBJECTTYPE OBJTYPE ON (OBJTYPE.CDOBJECTTYPE = OBJGRP.CDOBJECTTYPE)
INNER JOIN ASASSET ASAST ON (ASAST.CDASSET = OBJ.CDOBJECT AND ASAST.CDREVISION = OBJ.CDREVISION)
LEFT JOIN ADTEAM TEAMACCESS ON (ASAST.CDTEAMVIEWACCESS = TEAMACCESS.CDTEAM)
LEFT JOIN ADCOMPANY ADCMP ON (ADCMP.CDCOMPANY = ASAST.CDCOMPANYOWNER)
LEFT JOIN ADCOMPANY ADMNFT ON (ADMNFT.CDCOMPANY = ASAST.CDMANUFACTURER)
LEFT JOIN ADCOMPANY ADMSUPP ON (ADMSUPP.CDCOMPANY = ASAST.CDSUPPLIER)
LEFT JOIN (
SELECT CHKDCREV.CDDOCUMENT,
CHKDCREV.CDREVISION,
CHKDCREV.IDDOCUMENT,
COALESCE(TRLG.NMTRANSLATION, CHKDCREV.NMTITLE) NMTITLE,
CHKGNREV.DTREVISION,
CHKGNREV.QTVALIDITY,
CHKGNREV.FGVALIDITY
FROM DCDOCREVISION CHKDCREV
LEFT JOIN GNREVISION CHKGNREV ON (CHKDCREV.CDREVISION = CHKGNREV.CDREVISION)
LEFT JOIN DCCATEGORY CHKDCCAT ON (CHKDCCAT.CDCATEGORY = CHKDCREV.CDCATEGORY)
LEFT JOIN GNTRANSLATIONLANGUAGE TRLG ON (CHKDCREV.CDTRANSLATION = TRLG.CDTRANSLATION AND TRLG.FGLANGUAGE = 2)
WHERE CHKDCCAT.FGCATEGORYTYPE = 4
AND CHKDCREV.FGCURRENT = 1
) TBL_CHKWARRANTYDOC ON (TBL_CHKWARRANTYDOC.CDDOCUMENT = ASAST.CDWARRANTYDOC)
LEFT JOIN MAEQPT MAEQUIPMENT ON (MAEQUIPMENT.CDEQPT = ASAST.CDASSET AND MAEQUIPMENT.CDREVISION = ASAST.CDREVISION)
LEFT JOIN GNRESOURCE AST_RESOURCE ON (AST_RESOURCE.CDASSET = ASAST.CDASSET)
LEFT JOIN GNCALENDAR AST_CALENDAR ON (AST_CALENDAR.CDCALENDAR = AST_RESOURCE.CDCALENDAR)
LEFT JOIN (
SELECT ALIAS_ASHISTSITE.CDASSET,
ALIAS_ASHISTSITE.CDREVISION,
ALIAS_ASHISTSITE.FGSITETYPE,
ALIAS_ASHISTSITE.NMCOORD,
ALIAS_ASSITE.CDSITE,
ALIAS_ASSITE.IDSITE,
ALIAS_ASSITE.NMSITE,
ALIAS_ASSITE.NMCOUNTRY,
ALIAS_ASSITE.NMSTATE,
ALIAS_ASSITE.NMCITY,
ALIAS_ASSITE.NMPOSTALCODE,
ALIAS_ASSITE.NMCOORD AS NMCOORD_SITE,
ALIAS_ASSITE.DSADDRESS,
ALIAS_ADDEP.IDDEPARTMENT,
ALIAS_ADDEP.NMDEPARTMENT,
ALIAS_ADUSER.CDUSER,
ALIAS_ADUSER.IDUSER,
ALIAS_ADUSER.NMUSER
FROM ASHISTASSETSITE ALIAS_ASHISTSITE
LEFT JOIN ASSITE ALIAS_ASSITE ON (ALIAS_ASSITE.CDSITE = ALIAS_ASHISTSITE.CDSITE)
LEFT JOIN ADUSER ALIAS_ADUSER ON (ALIAS_ADUSER.CDUSER = ALIAS_ASHISTSITE.CDUSERSITE)
LEFT JOIN ADDEPARTMENT ALIAS_ADDEP ON (ALIAS_ASSITE.CDCOMPANY = ALIAS_ADDEP.CDDEPARTMENT)
WHERE ALIAS_ASHISTSITE.FGLASTSITE = 1
) TBL_LASTSITE ON (TBL_LASTSITE.CDASSET = ASAST.CDASSET)
LEFT JOIN (
SELECT ALIAS_ASHISTSSTATE.CDASSET,
ALIAS_ASHISTSSTATE.CDREVISION,
ALIAS_ASSTATE.CDSTATE,
ALIAS_ASSTATE.IDSTATE,
ALIAS_ASSTATE.NMSTATE
FROM ASHISTASSETSTATE ALIAS_ASHISTSSTATE
LEFT JOIN ASSTATE ALIAS_ASSTATE ON (ALIAS_ASSTATE.CDSTATE = ALIAS_ASHISTSSTATE.CDSTATE)
WHERE ALIAS_ASHISTSSTATE.FGLASTSTATE = 1
) TBL_LASTSTATE ON (TBL_LASTSTATE.CDASSET = ASAST.CDASSET)
WHERE
OBJ.FGCURRENT = 1 /*OBS 1*/ AND
OBJ.IDOBJECT = 'IDENTIFICADOR_DO_ATIVO' /*OBS 2*/
|
SQL Server
SELECT
OBJ.IDOBJECT,
OBJ.NMOBJECT,
GNREV.IDREVISION,
GNREV.DTREVISION,
OBJTYPE.IDOBJECTTYPE + ' - ' + OBJTYPE.NMOBJECTTYPE NMOBJECTTYPE,
TEAM.IDTEAM + ' - ' + TEAM.NMTEAM NMTEAMRESPONSABLE,
TBL_LASTSTATE.IDSTATE + ' - ' + TBL_LASTSTATE.NMSTATE NMLASTSTATE,
OBJ.FGAPPLICATION,
ASAST.DTSTARTOPER,
ASAST.QTQUANTITY,
ASAST.QTAVAILABLE,
AST_CALENDAR.IDCALENDAR + ' - ' + AST_CALENDAR.NMCALENDAR NMCALENDAR,
ASAST.FGASSTATUS,
TBL_LASTSITE.IDSITE + ' - ' + TBL_LASTSITE.NMSITE LASTSITE_NMSITE,
TBL_LASTSITE.IDUSER + ' - ' + TBL_LASTSITE.NMUSER LASTSITE_NMUSER,
TBL_LASTSITE.NMCOORD,
ASAST.FGVIEWACCESS,
TEAMACCESS.IDTEAM + ' - ' + TEAMACCESS.NMTEAM NMTEAMVIEWACCESS,
ASAST.FGOWNER,
ADCMP.IDCOMMERCIAL + ' - ' + ADCMP.NMCOMPANY NMCOMPANYOWNER,
ADMNFT.IDCOMMERCIAL + ' - ' + ADMNFT.NMCOMPANY NMMANUFACTURER,
ADMSUPP.IDCOMMERCIAL + ' - ' + ADMSUPP.NMCOMPANY NMSUPPLIER,
ASAST.IDSERIALNUMBER,
ASAST.IDMODEL,
ASAST.FGRESOURCE,
TBL_CHKWARRANTYDOC.IDDOCUMENT + ' - ' + TBL_CHKWARRANTYDOC.NMTITLE NMWARRANTYDOC,
TBL_CHKWARRANTYDOC.DTREVISION DTDOCREV,
CASE TBL_CHKWARRANTYDOC.FGVALIDITY
WHEN 1 THEN (TBL_CHKWARRANTYDOC.DTREVISION + TBL_CHKWARRANTYDOC.QTVALIDITY)
WHEN 2 THEN (TBL_CHKWARRANTYDOC.DTREVISION + (TBL_CHKWARRANTYDOC.QTVALIDITY*30))
WHEN 3 THEN (TBL_CHKWARRANTYDOC.DTREVISION + (TBL_CHKWARRANTYDOC.QTVALIDITY*365))
ELSE NULL
END VIEW_DTDOCREVVALIDITY,
ASAST.DSDESCRIPTION,
MAEQUIPMENT.DSEQPT,
OBJ.DSOBSERVATION,
ASAST.FGAUTOACQUISITION,
ASAST.IDIPADDRESS,
ASAST.IDPORT,
ASAST.QTTIMEOUT
FROM OBOBJECT OBJ
INNER JOIN GNREVISION GNREV ON (OBJ.CDREVISION = GNREV.CDREVISION)
INNER JOIN OBOBJECTGROUP OBJGRP ON (OBJ.CDOBJECT = OBJGRP.CDOBJECTGROUP)
LEFT JOIN ADTEAM TEAM ON (OBJ.CDTEAMRESPONSABLE = TEAM.CDTEAM)
INNER JOIN OBOBJECTTYPE OBJTYPE ON (OBJTYPE.CDOBJECTTYPE = OBJGRP.CDOBJECTTYPE)
INNER JOIN ASASSET ASAST ON (ASAST.CDASSET = OBJ.CDOBJECT AND ASAST.CDREVISION = OBJ.CDREVISION)
LEFT JOIN ADTEAM TEAMACCESS ON (ASAST.CDTEAMVIEWACCESS = TEAMACCESS.CDTEAM)
LEFT JOIN ADCOMPANY ADCMP ON (ADCMP.CDCOMPANY = ASAST.CDCOMPANYOWNER)
LEFT JOIN ADCOMPANY ADMNFT ON (ADMNFT.CDCOMPANY = ASAST.CDMANUFACTURER)
LEFT JOIN ADCOMPANY ADMSUPP ON (ADMSUPP.CDCOMPANY = ASAST.CDSUPPLIER)
LEFT JOIN (
SELECT CHKDCREV.CDDOCUMENT,
CHKDCREV.CDREVISION,
CHKDCREV.IDDOCUMENT,
COALESCE(TRLG.NMTRANSLATION, CHKDCREV.NMTITLE) NMTITLE,
CHKGNREV.DTREVISION,
CHKGNREV.QTVALIDITY,
CHKGNREV.FGVALIDITY
FROM DCDOCREVISION CHKDCREV
LEFT JOIN GNREVISION CHKGNREV ON (CHKDCREV.CDREVISION = CHKGNREV.CDREVISION)
LEFT JOIN DCCATEGORY CHKDCCAT ON (CHKDCCAT.CDCATEGORY = CHKDCREV.CDCATEGORY)
LEFT JOIN GNTRANSLATIONLANGUAGE TRLG ON (CHKDCREV.CDTRANSLATION = TRLG.CDTRANSLATION AND TRLG.FGLANGUAGE = 2)
WHERE CHKDCCAT.FGCATEGORYTYPE = 4
AND CHKDCREV.FGCURRENT = 1
) TBL_CHKWARRANTYDOC ON (TBL_CHKWARRANTYDOC.CDDOCUMENT = ASAST.CDWARRANTYDOC)
LEFT JOIN MAEQPT MAEQUIPMENT ON (MAEQUIPMENT.CDEQPT = ASAST.CDASSET AND MAEQUIPMENT.CDREVISION = ASAST.CDREVISION)
LEFT JOIN GNRESOURCE AST_RESOURCE ON (AST_RESOURCE.CDASSET = ASAST.CDASSET)
LEFT JOIN GNCALENDAR AST_CALENDAR ON (AST_CALENDAR.CDCALENDAR = AST_RESOURCE.CDCALENDAR)
LEFT JOIN (
SELECT ALIAS_ASHISTSITE.CDASSET,
ALIAS_ASHISTSITE.CDREVISION,
ALIAS_ASHISTSITE.FGSITETYPE,
ALIAS_ASHISTSITE.NMCOORD,
ALIAS_ASSITE.CDSITE,
ALIAS_ASSITE.IDSITE,
ALIAS_ASSITE.NMSITE,
ALIAS_ASSITE.NMCOUNTRY,
ALIAS_ASSITE.NMSTATE,
ALIAS_ASSITE.NMCITY,
ALIAS_ASSITE.NMPOSTALCODE,
ALIAS_ASSITE.NMCOORD AS NMCOORD_SITE,
ALIAS_ASSITE.DSADDRESS,
ALIAS_ADDEP.IDDEPARTMENT,
ALIAS_ADDEP.NMDEPARTMENT,
ALIAS_ADUSER.CDUSER,
ALIAS_ADUSER.IDUSER,
ALIAS_ADUSER.NMUSER
FROM ASHISTASSETSITE ALIAS_ASHISTSITE
LEFT JOIN ASSITE ALIAS_ASSITE ON (ALIAS_ASSITE.CDSITE = ALIAS_ASHISTSITE.CDSITE)
LEFT JOIN ADUSER ALIAS_ADUSER ON (ALIAS_ADUSER.CDUSER = ALIAS_ASHISTSITE.CDUSERSITE)
LEFT JOIN ADDEPARTMENT ALIAS_ADDEP ON (ALIAS_ASSITE.CDCOMPANY = ALIAS_ADDEP.CDDEPARTMENT)
WHERE ALIAS_ASHISTSITE.FGLASTSITE = 1
) TBL_LASTSITE ON (TBL_LASTSITE.CDASSET = ASAST.CDASSET)
LEFT JOIN (
SELECT ALIAS_ASHISTSSTATE.CDASSET,
ALIAS_ASHISTSSTATE.CDREVISION,
ALIAS_ASSTATE.CDSTATE,
ALIAS_ASSTATE.IDSTATE,
ALIAS_ASSTATE.NMSTATE
FROM ASHISTASSETSTATE ALIAS_ASHISTSSTATE
LEFT JOIN ASSTATE ALIAS_ASSTATE ON (ALIAS_ASSTATE.CDSTATE = ALIAS_ASHISTSSTATE.CDSTATE)
WHERE ALIAS_ASHISTSSTATE.FGLASTSTATE = 1
) TBL_LASTSTATE ON (TBL_LASTSTATE.CDASSET = ASAST.CDASSET)
WHERE
OBJ.FGCURRENT = 1 /*OBS 1*/ AND
OBJ.IDOBJECT = 'IDENTIFICADOR_DO_ATIVO' /*OBS 2*/
|
Observações
▪1: FGCURRENT com o valor 1 seleciona a última revisão do ativo. ▪2: Identificador do ativo a ser filtrado.
Campos utilizados na consulta
Nº
|
ID do campo
|
Tipo
|
Observação
|
1
|
IDOBJECT
|
VARCHAR(50)
|
Identificador do ativo
|
2
|
NMOBJECT
|
VARCHAR(255)
|
Nome do ativo
|
3
|
IDREVISION
|
VARCHAR(50)
|
Identificador da revisão
|
4
|
DTREVISION
|
DATETIME
|
Data de revisão
|
5
|
NMOBJECTTYPE
|
VARCHAR(255)
|
Tipo de ativo
|
6
|
NMTEAMRESPONSABLE
|
VARCHAR(255)
|
Equipe responsável
|
7
|
NMLASTSTATE
|
VARCHAR(255)
|
Condição
|
8
|
FGAPPLICATION
|
NUMERIC(2)
|
Especificação do ativo:
1– Instrumento
2– Equipamento
3– Item
4– Outros
5– Insumos
6- Ferramental
8– Modelo de ativo
9– Resíduo
10– Família de item
11– Computador;
|
9
|
DTSTARTOPER
|
DATETIME
|
Data de início da operação
|
10
|
QTQUANTITY
|
NUMERIC(10)
|
Quantidade
|
11
|
QTAVAILABLE
|
NUMERIC(10)
|
Quantidade disponível
|
12
|
NMCALENDAR
|
VARCHAR(255)
|
Nome do calendário
|
13
|
FGASSTATUS
|
NUMERIC(2)
|
Situação do ativo:
1– Verificação
2– Movimentação para utilização
3– Movimentação para manutenção
4– Desativado
5– Disponível
6– Movimentação para calibração
7– Calibração em execução
8– Aprovação de calibração
9– Análise de não conformidade
10– Manutenção
|
14
|
LASTSITE_NMSITE
|
VARCHAR(255)
|
Localização do ativo
|
15
|
LASTSITE_NMUSER
|
VARCHAR(255)
|
Usuário
|
16
|
NMCOORD
|
VARCHAR(255)
|
Coordenadas geográficas
|
17
|
FGVIEWACCESS
|
NUMERIC(2)
|
Acesso restrito nas telas de consulta:
1– Sim
2– Não
|
18
|
NMTEAMVIEWACCESS
|
VARCHAR(255)
|
Equipe de visualização
|
19
|
FGOWNER
|
NUMERIC(2)
|
Proprietário:
1– Interno
2– Externo
|
20
|
NMCOMPANYOWNER
|
VARCHAR(255)
|
Empresa proprietária
|
21
|
NMMANUFACTURER
|
VARCHAR(255)
|
Fabricante
|
22
|
NMSUPPLIER
|
VARCHAR(255)
|
Fornecedor
|
23
|
IDSERIALNUMBER
|
VARCHAR(50)
|
Número serial
|
24
|
IDMODEL
|
VARCHAR(50)
|
Modelo
|
25
|
FGRESOURCE
|
NUMERIC(2)
|
Utilizar ativo como recurso:
1– Sim
2– Não
|
26
|
NMWARRANTYDOC
|
VARCHAR(255)
|
Contrato de garantia
|
27
|
DTDOCREV
|
DATETIME
|
Data de compra
|
28
|
VIEW_DTDOCREVVALIDITY
|
DATETIME
|
Data de validade
|
29
|
DSDESCRIPTION
|
TEXT
|
Descrição
|
30
|
DSEQPT
|
TEXT
|
Características
|
31
|
DSOBSERVATION
|
TEXT
|
Observações
|
32
|
FGAUTOACQUISITION
|
NUMERIC(2)
|
Aquisição automática:
1– Sim
2– Não
|
33
|
IDIPADDRESS
|
VARCHAR(50)
|
Endereço IP
|
34
|
IDPORT
|
VARCHAR(50)
|
Porta
|
35
|
QTTIMEOUT
|
NUMERIC(10)
|
Timeout
|
|
