Visão geral
Este comando listará os dados de um plano de manutenção.
Tabelas relacionadas
TABELA
|
Descrição
|
Chave primária (PK)
|
Tabelas de ligação
|
ASPLANACTIVITY
|
Cadastro de plano de atividades
|
CDPLANACTIVITY
|
ADMAILTASKREL
ADTEAM
ASACTIVITY
GNCALENDAR
GNEVALRESULTUSED
GNEVALREVISION
MAROUTE
|
ASACTIVITY
|
Cadastro de atividades modelo
|
CDACTIVITY
|
ADAPPROVALROUTE
ADCHECKLIST
ADDOCUMENT
ADTEAM
ASACTIVMODELTYPE
ASSERVICECENTER
ASSERVICECENTERTEC
CACONFIGURATION
GNACTIVITYTIMECFG
GNCOSTCONFIG
GNEVALRESULTUSED
GNEVALREVISION
GNMASK
GNRESOURCE
MAMAINTTYPE
SERICHTEXT
|
ASPLANACTIVASSET
|
Associação de ativos ao plano
|
CDPLANACTIVASSET
|
ASPLANACTIVITY
OBOBJECTGROUP
|
OBOBJECT
|
Cadastro de dados do item/ativo/insumo por revisão
|
CDOBJECT
CDREVISION
|
ADTEAM
GNASSOC
GNREVISION
OBOBJECTGROUP
OBSUBREASON
|
ASASSET
|
Cadastros de ativos
|
CDASSET
CDREVISION
|
ADCHECKLIST
ADCOMPANY
ADTEAM
ASCONTROLS
ASDEPRECIATION
ASGENCONFIG
ASSTATE
DCDOCUMENT
|
MAEQPT
|
Cadastro de equipamentos
|
CDEQPT
CDREVISION
|
ADALLUSERS
ADMAILTASKREL
ASASSET
MAEQPTMETERREAD
MAEQPTMETERRESTART
MAEQPTUNITY
|
MAEQPTMETERREAD
|
Cadastro das leituras de medidor dos ativos
|
CDEQPT
CDREVISION
NRMETERREAD
|
ASEXECACTIVITY
MAEQPT
|
ADTEAM
|
Cadastro de equipe
|
CDTEAM
|
ADAUTHLDAPCONFIG
GNMAPPING
GNPERMISSION
|
GNCALENDAR
|
Cadastro de calendário
|
CDCALENDAR
|
|
GNEVAL
|
Cadastro de métodos de avaliação
|
CDEVAL
|
|
GNEVALRESULTUSED
|
Resultado das avaliações realizadas
|
CDEVALRESULTUSED
|
GNEVALRESULT
|
GNEVALRESULT
|
Resultados do método de avaliação
|
CDEVALRESULT
|
GNEVALREVISION
|
Oracle
SELECT
ASPLANACT.IDPLANACTIVITY,
ASPLANACT.NMPLANACTIVITY,
ASACT.IDACTIVITY || ' - ' || ASACT.NMACTIVITY NMACTIVITYMODEL,
ASPLANACT.FGPLANTYPE,
CASE
WHEN ASPLANACT.FGMULTIPLEASSET = 2 THEN OBOBJ.IDOBJECT || ' - ' || OBOBJ.NMOBJECT
WHEN ASPLANACT.FGMULTIPLEASSET = 1 THEN 'Múltiplos ativos'
ELSE NULL
END VIEW_PLANASSETS,
PLANRESPTEAM.IDTEAM || ' - ' || PLANRESPTEAM.NMTEAM NMPLANRESPTEAM,
PLANTEAM.IDTEAM || ' - ' || PLANTEAM.NMTEAM AS NMPLANTEAM,
EXECTEAM.IDTEAM || ' - ' || EXECTEAM.NMTEAM AS NMEXECTEAM,
ASPLANACT.FGFREQTYPE,
ASPLANACT.QTFREQUENCE,
ASPLANACT.FGFREQUNIT,
ASPLANACT.FGFORMATDATE,
ASPLANACT.DTEXP,
GNCAL.IDCALENDAR || ' - ' || GNCAL.NMCALENDAR NMCALENDAR,
ASPLANACT.VLMETERREADFREQ,
ASPLANACT.VLANTMETERREADFREQ,
ASPLANACT.VLEXPMETERREADING,
TBLLASTMETERREAD.VLREADVALUE,
TBLLASTMETERREAD.DTREAD,
ASPLANACT.FGAUTOCREATE,
ASPLANACT.QTCREATEBEFORE,
ASPLANACT.FGKEEPCREATING,
ASPLANACT.FGCREATESTEP,
GNEVAL.IDEVAL || ' - ' || GNEVAL.NMEVAL NMEVALMETHOD,
GNRESLT.NMEVALRESULT
FROM ASPLANACTIVITY ASPLANACT
INNER JOIN ASACTIVITY ASACT ON ( ASPLANACT.CDACTIVITY = ASACT.CDACTIVITY )
LEFT JOIN ASPLANACTIVASSET PLANASSET ON ( ASPLANACT.FGMULTIPLEASSET = 2 AND PLANASSET.CDPLANACTIVITY = ASPLANACT.CDPLANACTIVITY )
LEFT JOIN OBOBJECT OBOBJ ON ( ASPLANACT.FGMULTIPLEASSET = 2 AND PLANASSET.CDASSET = OBOBJ.CDOBJECT AND OBOBJ.FGCURRENT = 1 )
LEFT JOIN ASASSET ASAST ON ( ASPLANACT.FGMULTIPLEASSET = 2 AND OBOBJ.CDREVISION = ASAST.CDREVISION AND PLANASSET.CDASSET = ASAST.CDASSET )
LEFT JOIN (
SELECT
MAEQPT.CDEQPT,
MAEQPT.CDREVISION,
COALESCE( MAREAD.DTREAD, MARESTART.DTRESTART ) DTREAD,
COALESCE( MAREAD.QTHRREAD, MARESTART.QTHRRESTART ) QTHRREAD,
COALESCE( MAREAD.VLREADVALUE, 0 ) VLREADVALUE,
MAREAD.NRMETERREAD
FROM MAEQPT
LEFT JOIN MAEQPTMETERREAD MAREAD ON (
MAEQPT.CDEQPT = MAREAD.CDEQPT
AND MAEQPT.CDREVISION = MAREAD.CDREVISION
AND MAEQPT.NRLASTREAD = MAREAD.NRMETERREAD
)
LEFT JOIN MAEQPTMETERRESTART MARESTART ON (
MAEQPT.CDEQPT = MARESTART.CDEQPT
AND MAEQPT.CDREVISION = MARESTART.CDREVISION
AND MAEQPT.NRLASTREAD = MARESTART.NRMETERREAD
)
WHERE MAREAD.NRMETERREAD IS NOT NULL OR MARESTART.NRMETERRESTART IS NOT NULL
) TBLLASTMETERREAD ON ( TBLLASTMETERREAD.CDEQPT = ASAST.CDASSET AND TBLLASTMETERREAD.CDREVISION = ASAST.CDREVISION )
LEFT JOIN ADTEAM PLANTEAM ON ( PLANTEAM.CDTEAM = ASACT.CDPLANTEAM )
LEFT JOIN ADTEAM EXECTEAM ON ( EXECTEAM.CDTEAM = ASACT.CDTEAM )
LEFT JOIN ADTEAM PLANRESPTEAM ON ( PLANRESPTEAM.CDTEAM = ASPLANACT.CDTEAM )
LEFT JOIN GNCALENDAR GNCAL ON ( GNCAL.CDCALENDAR = ASPLANACT.CDCALENDAR )
LEFT JOIN GNEVAL GNEVAL ON ( ASPLANACT.CDEVALMETHOD = GNEVAL.CDEVAL )
LEFT JOIN GNEVALRESULTUSED GNRESLTUSD ON ( GNRESLTUSD.CDEVALRESULTUSED = ASPLANACT.CDEVALRESULT )
LEFT JOIN GNEVALRESULT GNRESLT ON ( GNRESLT.CDEVALRESULT = GNRESLTUSD.CDEVALRESULT )
WHERE (
ASPLANACT.IDPLANACTIVITY = 'IDENTIFICADOR_DO_PLANO' /*OBS 1*/
)
|
Postgres
SELECT
ASPLANACT.IDPLANACTIVITY,
ASPLANACT.NMPLANACTIVITY,
ASACT.IDACTIVITY || ' - ' || ASACT.NMACTIVITY NMACTIVITYMODEL,
ASPLANACT.FGPLANTYPE,
CASE
WHEN ASPLANACT.FGMULTIPLEASSET = 2 THEN OBOBJ.IDOBJECT || ' - ' || OBOBJ.NMOBJECT
WHEN ASPLANACT.FGMULTIPLEASSET = 1 THEN 'Múltiplos ativos'
ELSE NULL
END VIEW_PLANASSETS,
PLANRESPTEAM.IDTEAM || ' - ' || PLANRESPTEAM.NMTEAM NMPLANRESPTEAM,
PLANTEAM.IDTEAM || ' - ' || PLANTEAM.NMTEAM AS NMPLANTEAM,
EXECTEAM.IDTEAM || ' - ' || EXECTEAM.NMTEAM AS NMEXECTEAM,
ASPLANACT.FGFREQTYPE,
ASPLANACT.QTFREQUENCE,
ASPLANACT.FGFREQUNIT,
ASPLANACT.FGFORMATDATE,
ASPLANACT.DTEXP,
GNCAL.IDCALENDAR || ' - ' || GNCAL.NMCALENDAR NMCALENDAR,
ASPLANACT.VLMETERREADFREQ,
ASPLANACT.VLANTMETERREADFREQ,
ASPLANACT.VLEXPMETERREADING,
TBLLASTMETERREAD.VLREADVALUE,
TBLLASTMETERREAD.DTREAD,
ASPLANACT.FGAUTOCREATE,
ASPLANACT.QTCREATEBEFORE,
ASPLANACT.FGKEEPCREATING,
ASPLANACT.FGCREATESTEP,
GNEVAL.IDEVAL || ' - ' || GNEVAL.NMEVAL NMEVALMETHOD,
GNRESLT.NMEVALRESULT
FROM ASPLANACTIVITY ASPLANACT
INNER JOIN ASACTIVITY ASACT ON ( ASPLANACT.CDACTIVITY = ASACT.CDACTIVITY )
LEFT JOIN ASPLANACTIVASSET PLANASSET ON ( ASPLANACT.FGMULTIPLEASSET = 2 AND PLANASSET.CDPLANACTIVITY = ASPLANACT.CDPLANACTIVITY )
LEFT JOIN OBOBJECT OBOBJ ON ( ASPLANACT.FGMULTIPLEASSET = 2 AND PLANASSET.CDASSET = OBOBJ.CDOBJECT AND OBOBJ.FGCURRENT = 1 )
LEFT JOIN ASASSET ASAST ON ( ASPLANACT.FGMULTIPLEASSET = 2 AND OBOBJ.CDREVISION = ASAST.CDREVISION AND PLANASSET.CDASSET = ASAST.CDASSET )
LEFT JOIN (
SELECT
MAEQPT.CDEQPT,
MAEQPT.CDREVISION,
COALESCE( MAREAD.DTREAD, MARESTART.DTRESTART ) DTREAD,
COALESCE( MAREAD.QTHRREAD, MARESTART.QTHRRESTART ) QTHRREAD,
COALESCE( MAREAD.VLREADVALUE, 0 ) VLREADVALUE,
MAREAD.NRMETERREAD
FROM MAEQPT
LEFT JOIN MAEQPTMETERREAD MAREAD ON (
MAEQPT.CDEQPT = MAREAD.CDEQPT
AND MAEQPT.CDREVISION = MAREAD.CDREVISION
AND MAEQPT.NRLASTREAD = MAREAD.NRMETERREAD
)
LEFT JOIN MAEQPTMETERRESTART MARESTART ON (
MAEQPT.CDEQPT = MARESTART.CDEQPT
AND MAEQPT.CDREVISION = MARESTART.CDREVISION
AND MAEQPT.NRLASTREAD = MARESTART.NRMETERREAD
)
WHERE MAREAD.NRMETERREAD IS NOT NULL OR MARESTART.NRMETERRESTART IS NOT NULL
) TBLLASTMETERREAD ON ( TBLLASTMETERREAD.CDEQPT = ASAST.CDASSET AND TBLLASTMETERREAD.CDREVISION = ASAST.CDREVISION )
LEFT JOIN ADTEAM PLANTEAM ON ( PLANTEAM.CDTEAM = ASACT.CDPLANTEAM )
LEFT JOIN ADTEAM EXECTEAM ON ( EXECTEAM.CDTEAM = ASACT.CDTEAM )
LEFT JOIN ADTEAM PLANRESPTEAM ON ( PLANRESPTEAM.CDTEAM = ASPLANACT.CDTEAM )
LEFT JOIN GNCALENDAR GNCAL ON ( GNCAL.CDCALENDAR = ASPLANACT.CDCALENDAR )
LEFT JOIN GNEVAL GNEVAL ON ( ASPLANACT.CDEVALMETHOD = GNEVAL.CDEVAL )
LEFT JOIN GNEVALRESULTUSED GNRESLTUSD ON ( GNRESLTUSD.CDEVALRESULTUSED = ASPLANACT.CDEVALRESULT )
LEFT JOIN GNEVALRESULT GNRESLT ON ( GNRESLT.CDEVALRESULT = GNRESLTUSD.CDEVALRESULT )
WHERE (
ASPLANACT.IDPLANACTIVITY = 'IDENTIFICADOR_DO_PLANO' /*OBS 1*/
)
|
SQL Server
SELECT
ASPLANACT.IDPLANACTIVITY + ' - ' + ASPLANACT.NMPLANACTIVITY NMPLANACTIVITY,
ASACT.IDACTIVITY + ' - ' + ASACT.NMACTIVITY NMACTIVITYMODEL,
ASPLANACT.FGPLANTYPE,
CASE
WHEN ASPLANACT.FGMULTIPLEASSET = 2 THEN OBOBJ.IDOBJECT + ' - ' + OBOBJ.NMOBJECT
WHEN ASPLANACT.FGMULTIPLEASSET = 1 THEN 'Múltiplos ativos'
ELSE NULL
END VIEW_PLANASSETS,
PLANRESPTEAM.IDTEAM + ' - ' + PLANRESPTEAM.NMTEAM NMPLANRESPTEAM,
PLANTEAM.IDTEAM + ' - ' + PLANTEAM.NMTEAM AS NMPLANTEAM,
EXECTEAM.IDTEAM + ' - ' + EXECTEAM.NMTEAM AS NMEXECTEAM,
ASPLANACT.FGFREQTYPE,
ASPLANACT.QTFREQUENCE,
ASPLANACT.FGFREQUNIT,
ASPLANACT.FGFORMATDATE,
ASPLANACT.DTEXP,
GNCAL.IDCALENDAR + ' - ' + GNCAL.NMCALENDAR NMCALENDAR,
ASPLANACT.VLMETERREADFREQ,
ASPLANACT.VLANTMETERREADFREQ,
ASPLANACT.VLEXPMETERREADING,
TBLLASTMETERREAD.VLREADVALUE,
TBLLASTMETERREAD.DTREAD,
ASPLANACT.FGAUTOCREATE,
ASPLANACT.QTCREATEBEFORE,
ASPLANACT.FGKEEPCREATING,
ASPLANACT.FGCREATESTEP,
GNEVAL.IDEVAL + ' - ' + GNEVAL.NMEVAL NMEVALMETHOD,
GNRESLT.NMEVALRESULT
FROM ASPLANACTIVITY ASPLANACT
INNER JOIN ASACTIVITY ASACT ON ( ASPLANACT.CDACTIVITY = ASACT.CDACTIVITY )
LEFT JOIN ASPLANACTIVASSET PLANASSET ON ( ASPLANACT.FGMULTIPLEASSET = 2 AND PLANASSET.CDPLANACTIVITY = ASPLANACT.CDPLANACTIVITY )
LEFT JOIN OBOBJECT OBOBJ ON ( ASPLANACT.FGMULTIPLEASSET = 2 AND PLANASSET.CDASSET = OBOBJ.CDOBJECT AND OBOBJ.FGCURRENT = 1 )
LEFT JOIN ASASSET ASAST ON ( ASPLANACT.FGMULTIPLEASSET = 2 AND OBOBJ.CDREVISION = ASAST.CDREVISION AND PLANASSET.CDASSET = ASAST.CDASSET )
LEFT JOIN (
SELECT
MAEQPT.CDEQPT,
MAEQPT.CDREVISION,
COALESCE( MAREAD.DTREAD, MARESTART.DTRESTART ) DTREAD,
COALESCE( MAREAD.QTHRREAD, MARESTART.QTHRRESTART ) QTHRREAD,
COALESCE( MAREAD.VLREADVALUE, 0 ) VLREADVALUE,
MAREAD.NRMETERREAD
FROM MAEQPT
LEFT JOIN MAEQPTMETERREAD MAREAD ON (
MAEQPT.CDEQPT = MAREAD.CDEQPT
AND MAEQPT.CDREVISION = MAREAD.CDREVISION
AND MAEQPT.NRLASTREAD = MAREAD.NRMETERREAD
)
LEFT JOIN MAEQPTMETERRESTART MARESTART ON (
MAEQPT.CDEQPT = MARESTART.CDEQPT
AND MAEQPT.CDREVISION = MARESTART.CDREVISION
AND MAEQPT.NRLASTREAD = MARESTART.NRMETERREAD
)
WHERE MAREAD.NRMETERREAD IS NOT NULL OR MARESTART.NRMETERRESTART IS NOT NULL
) TBLLASTMETERREAD ON ( TBLLASTMETERREAD.CDEQPT = ASAST.CDASSET AND TBLLASTMETERREAD.CDREVISION = ASAST.CDREVISION )
LEFT JOIN ADTEAM PLANTEAM ON ( PLANTEAM.CDTEAM = ASACT.CDPLANTEAM )
LEFT JOIN ADTEAM EXECTEAM ON ( EXECTEAM.CDTEAM = ASACT.CDTEAM )
LEFT JOIN ADTEAM PLANRESPTEAM ON ( PLANRESPTEAM.CDTEAM = ASPLANACT.CDTEAM )
LEFT JOIN GNCALENDAR GNCAL ON ( GNCAL.CDCALENDAR = ASPLANACT.CDCALENDAR )
LEFT JOIN GNEVAL GNEVAL ON ( ASPLANACT.CDEVALMETHOD = GNEVAL.CDEVAL )
LEFT JOIN GNEVALRESULTUSED GNRESLTUSD ON ( GNRESLTUSD.CDEVALRESULTUSED = ASPLANACT.CDEVALRESULT )
LEFT JOIN GNEVALRESULT GNRESLT ON ( GNRESLT.CDEVALRESULT = GNRESLTUSD.CDEVALRESULT )
WHERE (
ASPLANACT.IDPLANACTIVITY = 'IDENTIFICADOR_DO_PLANO' /*OBS 1*/
)
|
Observações
▪1: Identificador do plano a ser filtrado.
Campos utilizados na consulta
Nº
|
ID do campo
|
Tipo
|
Observação
|
1
|
IDPLANACTIVITY
|
VARCHAR(50)
|
Identificador do plano
|
2
|
NMPLANACTIVITY
|
VARCHAR(255)
|
Nome do plano
|
3
|
FGPLANTYPE
|
NUMERIC(2)
|
Tipo do plano:
1- Verificação
2- Manutenção preventiva
3- Calibração
6- Rota de manutenção;
|
4
|
VIEW_PLANASSETS
|
VARCHAR(255)
|
Ativo do plano
|
5
|
NMPLANRESPTEAM
|
VARCHAR(255)
|
Equipe responsável pelo plano
|
6
|
NMPLANTEAM
|
VARCHAR(255)
|
Equipe responsável pelo planejamento
|
7
|
NMEXECTEAM
|
VARCHAR(255)
|
Equipe responsável pela execução
|
8
|
FGFREQTYPE
|
NUMERIC(2)
|
Tipo de frequência:
1- Data
2- Primeiro uso
3- Tempo de uso
|
9
|
QTFREQUENCE
|
NUMERIC(10)
|
Frequência
|
10
|
FGFREQUNIT
|
NUMERIC(2)
|
Unidade da frequência:
1- Diário
2- Semanal
3- Mensal
4- Anual
5- Dias úteis
|
11
|
FGFORMATDATE
|
NUMERIC(2)
|
Formato da data:
1- dia/mês/ano
2- mês/ano
|
12
|
DTEXP
|
DATETIME
|
Data da próxima execução
|
13
|
NMCALENDAR
|
VARCHAR(255)
|
Calendário
|
14
|
VLMETERREADFREQ
|
NUMERIC(28,12)
|
Frequência
|
15
|
VLANTMETERREADFREQ
|
NUMERIC(28,12)
|
Antecipação da leitura de medidor
|
16
|
VLEXPMETERREADING
|
NUMERIC(28,12)
|
Próxima execução
|
17
|
VLREADVALUE
|
NUMERIC(28,12)
|
Leitura atual do medidor
|
18
|
DTREAD
|
DATETIME
|
Data da última leitura do medidor
|
19
|
FGAUTOCREATE
|
NUMERIC(2)
|
Criar atividade automaticamente:
1- Sim
2- Não
|
20
|
QTCREATEBEFORE
|
NUMERIC(10)
|
Dias de antecipação da criação automática de atividades
|
21
|
FGKEEPCREATING
|
NUMERIC(2)
|
Indica se deve continuar criando atividades automaticamente mesmo que haja atividades pendentes:
1- Sim
2- Não
|
22
|
FGCREATESTEP
|
NUMERIC(2)
|
Etapa que as atividades serão criadas:
1- Planejamento
2- A iniciar
|
23
|
NMEVALMETHOD
|
VARCHAR(255)
|
Método de avaliação
|
24
|
NMEVALRESULT
|
VARCHAR(255)
|
Resultado da prioridade
|
|
