Visão geral
Este comando listará as atividades de manutenção realizadas nos ativos e suas principais informações, independentemente da situação em que se encontram.
Tabelas relacionadas
TABELA
|
Descrição
|
Chave primária (PK)
|
Tabelas de ligação
|
GNACTIVITY
|
Cadastro de atividades
|
CDGENACTIVITY
|
ADALLUSERS
ADDEPARTMENT
ADMAILTASKREL
ADPOSITION
ADROLE
ADTEAM
ADUSEREXTERNALDATA
GNACTIVITYTIMECFG
GNAPPROV
GNASSOC
GNCALENDAR
GNCHECKLISTANSWER
GNCOSTCONFIG
GNEVALRESULTUSED
GNEVALREVISION
GNFAVORITE
GNOBJECTASSOCGROUP
GNTYPEROLE
SERICHTEXT
|
GNCOSTCONFIG
|
Armazena os custos da atividade
|
CDCOSTCONFIG
|
|
GNEVALRESULTUSED
|
Resultado das avaliações realizadas
|
CDEVALRESULTUSED
|
GNEVALRESULT
|
GNEVALRESULT
|
Resultados do método de avaliação
|
CDEVALRESULT
|
GNEVALREVISION
|
ASEXECACTIVITY
|
Cadastro de execução de atividades
|
CDEXECACTIVITY
|
ADDOCUMENT
ASACTIVITY
ASPLANACTIVITY
ASSERVICECENTER
ASSERVICECENTERTEC
DCDOCREVISION
GNACTIVITY
GNFAVORITE
GNRESOURCE
GNTOOLSANALISYS
MAMAINTTYPE
|
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
|
ASEXECACTIVASSET
|
Associação de ativos na atividade
|
CDEXECACTIVASSET
|
ASEXECACTIVITY
OBOBJECTGROUP
|
OBOBJECT
|
Cadastro de dados do item/ativo/insumo por revisão
|
CDOBJECT
CDREVISION
|
ADTEAM
GNASSOC
GNREVISION
OBOBJECTGROUP
OBSUBREASON
|
OBOBJECTGROUP
|
Cadastro de item/ativo/insumo
|
CDOBJECTGROUP
|
GNFAVORITE
|
OBOBJECTTYPE
|
Cadastro de tipo de item/ativo/insumo
|
CDOBJECTTYPE
|
ADMEASUNITY
ADTEAM
GNMASK
GNREVCONFIG
GNTYPEROLE
|
ASASSET
|
Cadastros de ativos
|
CDASSET
CDREVISION
|
ADCHECKLIST
ADCOMPANY
ADTEAM
ASCONTROLS
ASDEPRECIATION
ASGENCONFIG
ASSTATE
DCDOCUMENT
|
ASSERVICECENTER
|
Cadastro de centro de serviço
|
CDSERVICECENTER
|
GNCALENDAR
|
GNRESOURCE
|
Cadastro de recursos
|
CDRESOURCE
|
ADALLUSERS
ADCOMPANY
GNASSOC
GNCALENDAR
GNRESOURCETYPE
OBOBJECTGROUP
|
MAMAINTTYPE
|
Cadastro de tipo de manutenção
|
CDMAINTTYPE
|
|
ADTEAM
|
Cadastro de equipe
|
CDTEAM
|
ADAUTHLDAPCONFIG
GNMAPPING
GNPERMISSION
|
ADALLUSERS
|
Usuário
|
CDUSER
|
ADALLUSERS
ADUSEREXTERNALDATA
GNFAVORITE
|
GNASSOCATTACH
|
Anexo
|
CDASSOCATTACH
|
ADATTACHMENT
GNASSOC
|
GNCALENDARWORKDAY
|
Dias úteis
|
CDCALENDAR
DTDAY
|
|
ADPARAMS
|
Parâmetros gerais
|
CDISOSYSTEM
CDPARAM
|
|
Oracle
SELECT
GNACT.IDACTIVITY,
CUSTOM_GNACT.FGDEADLINE,
CUSTOM_GNACT.DTDEADLINE,
GNEVRESULT.NMEVALRESULT,
COALESCE(GNEVRESULTUSED.VLEVALRESULT, CAST( -999 AS NUMERIC(18,8) ) ) VLMINRESULT,
(SELECT IDUSER || ' - ' || NMUSER FROM ADUSER WHERE CDUSER = GNACT.CDUSER) AS NMEXECUTOR,
GNACT.FGSTATUS,
CASE
WHEN EXISTS (SELECT 1 FROM GNASSOCATTACH GNATTACH WHERE GNAT-TACH.CDASSOC = GNACT.CDASSOC ) THEN 1
ELSE 0
END AS FGATTACHMENT,
ASEXECACT.FGACTTYPE,
CASE
WHEN ASEXECACT.FGMULTIPLEASSET = 2 THEN OBOBJ.IDOBJECT || ' - ' || OBOBJ.NMOBJECT
WHEN ASEXECACT.FGMULTIPLEASSET = 1 THEN 'Múltiplos ativos'
ELSE NULL
END VIEW_EXECASSETS,
EXEC_ASPLANACT.IDPLANACTIVITY,
EXEC_ASPLANACT.NMPLANACTIVITY,
CASE
WHEN EXEC_ASPLANACT.CDTEAM IS NOT NULL THEN PLANTEAM.IDTEAM || ' - ' || PLANTEAM.NMTEAM
ELSE NULL
END AS VIEW_PLANTEAM,
ASACTMODEL.IDACTIVITY IDACTIVITYMODEL,
ASACTMODEL.NMACTIVITY NMACTIVITYMODEL,
ALIAS_MAMAINTTYPE.IDMAINTTYPE,
ALIAS_MAMAINTTYPE.NMMAINTTYPE,
CASE
WHEN ASSRVCNTR.CDSERVICECENTER IS NOT NULL THEN ASSRVC-NTR.IDCOMMERCIAL || ' - ' || ASSRVCNTR.NMCOMPANY
ELSE NULL
END NMSERVICECENTER,
CASE
WHEN GNRV.IDRESOURCE IS NOT NULL THEN GNRV.IDRESOURCE || ' - ' || GNRV.NMRESOURCE
ELSE ''
END AS NMRESOURCERESP,
GNACT.DTSTARTPLAN,
GNACT.DTSTART,
GNACT.DTFINISHPLAN,
GNACT.DTFINISH,
(SELECT
CASE
WHEN VLPARAM = 1 THEN
CASE
WHEN DTFINISH > DTFINISHPLAN THEN TO_DATE(DTFINISH, 'yyyy-mm-dd') - TO_DATE(DTFINISHPLAN, 'yyyy-mm-dd')
ELSE TO_DATE(DTFINISHPLAN, 'yyyy-mm-dd') - TO_DATE(DTFINISH, 'yyyy-mm-dd')
END
ELSE (
SELECT
COUNT( 1 ) - 1
FROM GNCALENDARWORKDAY
WHERE
CDCALENDAR = (
SELECT
CASE
WHEN VLPARAM = 1 THEN 0
WHEN VLPARAM = 2 THEN (SELECT MAX(CDCALENDAR) CDCALENDAR FROM GNCALENDAR WHERE FGDEFAULT = 1)
ELSE (SELECT MAX(CDCALENDAR) FROM GNRESOURCE WHERE CDASSET = OBOBJ.CDOBJECT)
END AS CDCALENDARIOATIVO
FROM ADPARAMS
WHERE
CDISOSYSTEM = 109
AND CDPARAM = 257
)
AND (
DTDAY <= DTFINISHPLAN AND DTDAY >= DTFINISH
OR DTDAY <= DTFINISH AND DTDAY >= DTFI-NISHPLAN
)
)
END QTDTFINISHDIFF
FROM ADPARAMS
WHERE
CDISOSYSTEM = 109
AND CDPARAM = 257
) QTDTFINISHDIFF, /*OBS 1*/
GNACT.VLPERCENTAGEM,
GNACT.QTMINUTESPLAN,
GNACT.QTMINUTESREAL,
CASE
WHEN GNACT.QTMINUTESPLAN IS NULL THEN NULL
WHEN GNACT.QTMINUTESREAL IS NULL THEN NULL
ELSE (GNACT.QTMINUTESREAL - GNACT.QTMINUTESPLAN)
END AS QTMINUTESDIFF,
GNCOSTCFG.MNCOSTPROGTEC,
GNCOSTCFG.MNCOSTREALTEC,
GNCOSTCFG.MNCOSTREALTEC - GNCOSTCFG.MNCOSTPROGTEC AS VIEW_MNCOSTDIFFTEC,
GNCOSTCFG.MNCOSTPROGMAT,
GNCOSTCFG.MNCOSTREALMAT,
GNCOSTCFG.MNCOSTREALMAT - GNCOSTCFG.MNCOSTPROGMAT AS VIEW_MNCOSTDIFFMAT,
GNCOSTCFG.MNCOSTPROGASSET,
GNCOSTCFG.MNCOSTREALASSET,
GNCOSTCFG.MNCOSTREALASSET - GNCOSTCFG.MNCOSTPROGASSET AS VIEW_MNCOSTDIFFASSET,
GNCOSTCFG.MNCOSTPROGTASK,
GNCOSTCFG.MNCOSTREALTASK,
GNCOSTCFG.MNCOSTREALTASK - GNCOSTCFG.MNCOSTPROGTASK AS VIEW_MNCOSTDIFFTASK,
GNCOSTCFG.MNCOSTPROGEXTRA,
GNCOSTCFG.MNCOSTREALEXTRA,
GNCOSTCFG.MNCOSTREALEXTRA - GNCOSTCFG.MNCOSTPROGEXTRA AS VIEW_MNCOSTDIFFEXTRA,
GNCOSTCFG.MNCOSTPROG,
GNCOSTCFG.MNCOSTREAL,
GNCOSTCFG.MNCOSTREAL - GNCOSTCFG.MNCOSTPROG AS VIEW_MNCOSTDIFF
FROM GNACTIVITY GNACT
INNER JOIN (
SELECT
TEMP_ACT.CDGENACTIVITY,
CASE
WHEN TEMP_ACT.FGSTATUS = 1 OR TEMP_ACT.FGSTATUS = 2 THEN
CASE
WHEN (TEMP_ACT.DTSTARTPLAN IS NOT NULL AND TEMP_ACT.DTSTARTPLAN < trunc(sysdate)) THEN 3
WHEN (TEMP_ACT.DTSTARTPLAN IS NULL OR TEMP_ACT.DTSTARTPLAN >= trunc(sysdate)) THEN 1
END
WHEN TEMP_ACT.FGSTATUS = 3 OR TEMP_ACT.FGSTATUS = 13 THEN
CASE
WHEN (TEMP_ACT.DTSTART IS NULL) THEN
CASE
WHEN (TEMP_ACT.DTSTARTPLAN < trunc(sysdate)) THEN 3
WHEN (TEMP_ACT.DTSTARTPLAN > trunc(sysdate)) THEN 1
ELSE 2
END
WHEN (TEMP_ACT.DTSTART IS NOT NULL AND TEMP_ACT.DTFINISH IS NULL) THEN
CASE
WHEN (TEMP_ACT.DTFINISHPLAN < trunc(sysdate)) THEN 3
WHEN (TEMP_ACT.DTFINISHPLAN > trunc(sysdate)) THEN 1
ELSE 2
END
WHEN (TEMP_ACT.DTSTART IS NOT NULL AND TEMP_ACT.DTFINISH IS NOT NULL) THEN
CASE
WHEN (TEMP_ACT.DTFINISHPLAN IS NOT NULL AND TEMP_ACT.DTFINISH <= TEMP_ACT.DTFINISHPLAN) THEN 1
WHEN (TEMP_ACT.DTFINISHPLAN IS NOT NULL AND TEMP_ACT.DTFINISH > TEMP_ACT.DTFINISHPLAN) THEN 3
ELSE 2
END
END
WHEN TEMP_ACT.FGSTATUS = 5 OR TEMP_ACT.FGSTATUS = 4 THEN
CASE
WHEN (TEMP_ACT.DTSTARTPLAN IS NULL OR TEMP_ACT.DTFINISH <= TEMP_ACT.DTFINISHPLAN) THEN 1
ELSE 3
END
END FGDEADLINE,
CASE
WHEN TEMP_ACT.FGSTATUS = 1 OR TEMP_ACT.FGSTATUS = 2 THEN
CASE
WHEN (TEMP_ACT.DTSTARTPLAN IS NOT NULL AND TEMP_ACT.DTSTARTPLAN < trunc(sysdate)) THEN TEMP_ACT.DTSTARTPLAN
WHEN (TEMP_ACT.DTSTARTPLAN IS NULL OR TEMP_ACT.DTSTARTPLAN >= trunc(sysdate)) THEN TEMP_ACT.DTSTARTPLAN
END
WHEN TEMP_ACT.FGSTATUS = 3 OR TEMP_ACT.FGSTATUS = 13 THEN
CASE
WHEN (TEMP_ACT.DTSTART IS NULL) THEN
CASE
WHEN (TEMP_ACT.DTSTARTPLAN < trunc(sysdate)) THEN TEMP_ACT.DTSTARTPLAN
WHEN (TEMP_ACT.DTSTARTPLAN > trunc(sysdate)) THEN TEMP_ACT.DTSTARTPLAN
ELSE TEMP_ACT.DTSTARTPLAN
END
WHEN (TEMP_ACT.DTSTART IS NOT NULL AND TEMP_ACT.DTFINISH IS NULL) THEN
CASE
WHEN (TEMP_ACT.DTFINISHPLAN < trunc(sysdate)) THEN TEMP_ACT.DTFINISHPLAN
WHEN (TEMP_ACT.DTFINISHPLAN > trunc(sysdate)) THEN TEMP_ACT.DTFINISHPLAN
ELSE TEMP_ACT.DTFINISHPLAN
END
WHEN (TEMP_ACT.DTSTART IS NOT NULL AND TEMP_ACT.DTFINISH IS NOT NULL) THEN
CASE
WHEN (TEMP_ACT.DTFINISHPLAN IS NOT NULL AND TEMP_ACT.DTFINISH <= TEMP_ACT.DTFINISHPLAN) THEN TEMP_ACT.DTFINISHPLAN
WHEN (TEMP_ACT.DTFINISHPLAN IS NOT NULL AND TEMP_ACT.DTFINISH > TEMP_ACT.DTFINISHPLAN) THEN TEMP_ACT.DTFINISHPLAN
ELSE NULL
END
END
WHEN TEMP_ACT.FGSTATUS = 5 OR TEMP_ACT.FGSTATUS = 4 THEN
CASE
WHEN (TEMP_ACT.DTSTARTPLAN IS NULL OR TEMP_ACT.DTFINISH <= TEMP_ACT.DTFINISHPLAN) THEN TEMP_ACT.DTSTARTPLAN
ELSE TEMP_ACT.DTSTARTPLAN
END
END DTDEADLINE
FROM GNACTIVITY TEMP_ACT
) CUSTOM_GNACT ON ( CUSTOM_GNACT.CDGENACTIVITY = GNACT.CDGENACTIVITY )
INNER JOIN GNCOSTCONFIG GNCOSTCFG ON ( GNCOSTCFG.CDCOSTCONFIG = GNACT.CDCOSTCONFIG )
LEFT JOIN GNEVALRESULTUSED GNEVRESULTUSED ON ( GNEVRESULTU-SED.CDEVALRESULTUSED = GNACT.CDEVALRSLTPRIORITY )
LEFT JOIN GNEVALRESULT GNEVRESULT ON ( GNEVRESULT.CDEVALRESULT = GNEVRE-SULTUSED.CDEVALRESULT )
INNER JOIN ASEXECACTIVITY ASEXECACT ON ( ASEXECACT.CDGENACTIVITY = GNACT.CDGENACTIVITY )
LEFT JOIN ASPLANACTIVITY EXEC_ASPLANACT ON ( EXEC_ASPLANACT.CDPLANACTIVITY = ASEXECACT.CDPLANNING )
INNER JOIN ASACTIVITY ASACTMODEL ON ( ASACTMODEL.CDACTIVITY = ASEXE-CACT.CDACTIVITY )
LEFT JOIN ASEXECACTIVASSET ASEXECACTAST ON ( ASEXECACT.FGMULTIPLEASSET = 2 AND ASEXECACT.CDEXECACTIVITY = ASEXECACTAST.CDEXECACTIVITY )
LEFT JOIN OBOBJECT OBOBJ ON ( ASEXECACT.FGMULTIPLEASSET = 2 AND ASEXE-CACTAST.CDASSET = OBOBJ.CDOBJECT AND OBOBJ.FGCURRENT = 1 )
LEFT JOIN OBOBJECTGROUP OBJGRP ON ( ASEXECACT.FGMULTIPLEASSET = 2 AND OBJGRP.CDOBJECTGROUP = OBOBJ.CDOBJECT )
LEFT JOIN OBOBJECTTYPE OBJTYPE ON ( ASEXECACT.FGMULTIPLEASSET = 2 AND OBJTYPE.CDOBJECTTYPE = OBJGRP.CDOBJECTTYPE )
LEFT JOIN ASASSET ASAST ON ( ASEXECACT.FGMULTIPLEASSET = 2 AND OBOBJ.CDREVISION = ASAST.CDREVISION AND ASEXECACTAST.CDASSET = ASAST.CDASSET )
LEFT JOIN ASSERVICECENTER ASSRVCNTR ON ( ASSRVCNTR.CDSERVICECENTER = ASEXECACT.CDSERVICECENTERE )
LEFT JOIN GNRESOURCE GNRV ON ( GNRV.CDRESOURCE = ASEXE-CACT.CDRESOURCERESP )
LEFT JOIN MAMAINTTYPE ALIAS_MAMAINTTYPE ON ( ALI-AS_MAMAINTTYPE.CDMAINTTYPE = ASEXECACT.CDACTIVCLASS )
LEFT JOIN ADTEAM PLANTEAM ON ( EXEC_ASPLANACT.CDTEAM = PLANTEAM.CDTEAM )
WHERE (
GNACT.CDISOSYSTEM IN (126) AND ASEXECACT.FGACTTYPE IN (2,6,7,8) /*OBS 2*/
) AND EXISTS (
SELECT 1
FROM ASEXECACTIVASSET CHKEXECACT
INNER JOIN ASASSET CHKAST ON (CHKAST.CDASSET = CHKEXE-CACT.CDASSET)
INNER JOIN OBOBJECT CHKOBJ ON (CHKOBJ.CDOBJECT = CHKAST.CDASSET AND CHKOBJ.CDREVISION = CHKAST.CDREVISION)
INNER JOIN OBOBJECTGROUP CHKOBJGRP ON (CHKO-BJGRP.CDOBJECTGROUP = CHKOBJ.CDOBJECT)
INNER JOIN OBOBJECTTYPE CHKOBJTYPE ON (CHKO-BJTYPE.CDOBJECTTYPE = CHKOBJGRP.CDOBJECTTYPE)
WHERE
CHKEXECACT.CDEXECACTIVITY = ASEXECACT.CDEXECACTIVITY
AND CHKOBJ.FGCURRENT = 1
AND CHKAST.FGASSTATUS <> 4
) AND GNACT.FGSTATUS IN (1,2,3,4,5,13) /*OBS 3*/
|
Postgres
SELECT
GNACT.IDACTIVITY,
CUSTOM_GNACT.FGDEADLINE,
CUSTOM_GNACT.DTDEADLINE,
GNEVRESULT.NMEVALRESULT,
COALESCE(GNEVRESULTUSED.VLEVALRESULT, CAST( -999 AS NUMERIC(18,8) ) ) VLMINRESULT,
(SELECT IDUSER || ' - ' || NMUSER FROM ADUSER WHERE CDUSER = GNACT.CDUSER) AS NMEXECUTOR,
GNACT.FGSTATUS,
CASE
WHEN EXISTS (SELECT 1 FROM GNASSOCATTACH GNATTACH WHERE GNATTACH.CDASSOC = GNACT.CDASSOC ) THEN 1
ELSE 0
END AS FGATTACHMENT,
ASEXECACT.FGACTTYPE,
CASE
WHEN ASEXECACT.FGMULTIPLEASSET = 2 THEN OBOBJ.IDOBJECT || ' - ' || OBOBJ.NMOBJECT
WHEN ASEXECACT.FGMULTIPLEASSET = 1 THEN 'Múltiplos ativos'
ELSE NULL
END VIEW_EXECASSETS,
EXEC_ASPLANACT.IDPLANACTIVITY,
EXEC_ASPLANACT.NMPLANACTIVITY,
CASE
WHEN EXEC_ASPLANACT.CDTEAM IS NOT NULL THEN PLANTEAM.IDTEAM || ' - ' || PLANTEAM.NMTEAM
ELSE NULL
END AS VIEW_PLANTEAM,
ASACTMODEL.IDACTIVITY IDACTIVITYMODEL,
ASACTMODEL.NMACTIVITY NMACTIVITYMODEL,
ALIAS_MAMAINTTYPE.IDMAINTTYPE,
ALIAS_MAMAINTTYPE.NMMAINTTYPE,
CASE
WHEN ASSRVCNTR.CDSERVICECENTER IS NOT NULL THEN ASSRVCNTR.IDCOMMERCIAL || ' - ' || ASSRVCNTR.NMCOMPANY
ELSE NULL
END NMSERVICECENTER,
CASE
WHEN GNRV.IDRESOURCE IS NOT NULL THEN GNRV.IDRESOURCE || ' - ' || GNRV.NMRESOURCE
ELSE ''
END AS NMRESOURCERESP,
GNACT.DTSTARTPLAN,
GNACT.DTSTART,
GNACT.DTFINISHPLAN,
GNACT.DTFINISH,
(SELECT
CASE
WHEN VLPARAM = 1 THEN
CASE
WHEN DTFINISH > DTFINISHPLAN THEN TO_DATE(DTFINISH, 'yyyy-mm-dd') - TO_DATE(DTFINISHPLAN, 'yyyy-mm-dd')
ELSE TO_DATE(DTFINISHPLAN, 'yyyy-mm-dd') - TO_DATE(DTFINISH, 'yyyy-mm-dd')
END
ELSE (
SELECT
COUNT( 1 ) - 1
FROM GNCALENDARWORKDAY
WHERE
CDCALENDAR = (
SELECT
CASE
WHEN VLPARAM = 1 THEN 0
WHEN VLPARAM = 2 THEN (SELECT MAX(CDCALENDAR) CDCALENDAR FROM GNCALENDAR WHERE FGDEFAULT = 1)
ELSE (SELECT MAX(CDCALENDAR) FROM GNRESOURCE WHERE CDASSET = OBOBJ.CDOBJECT)
END AS CDCALENDARIOATIVO
FROM ADPARAMS
WHERE
CDISOSYSTEM = 109
AND CDPARAM = 257
)
AND (
DTDAY <= DTFINISHPLAN AND DTDAY >= DTFINISH
OR DTDAY <= DTFINISH AND DTDAY >= DTFINISHPLAN
)
)
END QTDTFINISHDIFF
FROM ADPARAMS
WHERE
CDISOSYSTEM = 109
AND CDPARAM = 257
) QTDTFINISHDIFF, /*OBS 1*/
GNACT.VLPERCENTAGEM,
GNACT.QTMINUTESPLAN,
GNACT.QTMINUTESREAL,
CASE
WHEN GNACT.QTMINUTESPLAN IS NULL THEN NULL
WHEN GNACT.QTMINUTESREAL IS NULL THEN NULL
ELSE (GNACT.QTMINUTESREAL - GNACT.QTMINUTESPLAN)
END AS QTMINUTESDIFF,
GNCOSTCFG.MNCOSTPROGTEC,
GNCOSTCFG.MNCOSTREALTEC,
GNCOSTCFG.MNCOSTREALTEC - GNCOSTCFG.MNCOSTPROGTEC AS VIEW_MNCOSTDIFFTEC,
GNCOSTCFG.MNCOSTPROGMAT,
GNCOSTCFG.MNCOSTREALMAT,
GNCOSTCFG.MNCOSTREALMAT - GNCOSTCFG.MNCOSTPROGMAT AS VIEW_MNCOSTDIFFMAT,
GNCOSTCFG.MNCOSTPROGASSET,
GNCOSTCFG.MNCOSTREALASSET,
GNCOSTCFG.MNCOSTREALASSET - GNCOSTCFG.MNCOSTPROGASSET AS VIEW_MNCOSTDIFFASSET,
GNCOSTCFG.MNCOSTPROGTASK,
GNCOSTCFG.MNCOSTREALTASK,
GNCOSTCFG.MNCOSTREALTASK - GNCOSTCFG.MNCOSTPROGTASK AS VIEW_MNCOSTDIFFTASK,
GNCOSTCFG.MNCOSTPROGEXTRA,
GNCOSTCFG.MNCOSTREALEXTRA,
GNCOSTCFG.MNCOSTREALEXTRA - GNCOSTCFG.MNCOSTPROGEXTRA AS VIEW_MNCOSTDIFFEXTRA,
GNCOSTCFG.MNCOSTPROG,
GNCOSTCFG.MNCOSTREAL,
GNCOSTCFG.MNCOSTREAL - GNCOSTCFG.MNCOSTPROG AS VIEW_MNCOSTDIFF
FROM GNACTIVITY GNACT
INNER JOIN (
SELECT
TEMP_ACT.CDGENACTIVITY,
CASE
WHEN TEMP_ACT.FGSTATUS = 1 OR TEMP_ACT.FGSTATUS = 2 THEN
CASE
WHEN (TEMP_ACT.DTSTARTPLAN IS NOT NULL AND TEMP_ACT.DTSTARTPLAN < trunc(sysdate)) THEN 3
WHEN (TEMP_ACT.DTSTARTPLAN IS NULL OR TEMP_ACT.DTSTARTPLAN >= trunc(sysdate)) THEN 1
END
WHEN TEMP_ACT.FGSTATUS = 3 OR TEMP_ACT.FGSTATUS = 13 THEN
CASE
WHEN (TEMP_ACT.DTSTART IS NULL) THEN
CASE
WHEN (TEMP_ACT.DTSTARTPLAN < trunc(sysdate)) THEN 3
WHEN (TEMP_ACT.DTSTARTPLAN > trunc(sysdate)) THEN 1
ELSE 2
END
WHEN (TEMP_ACT.DTSTART IS NOT NULL AND TEMP_ACT.DTFINISH IS NULL) THEN
CASE
WHEN (TEMP_ACT.DTFINISHPLAN < trunc(sysdate)) THEN 3
WHEN (TEMP_ACT.DTFINISHPLAN > trunc(sysdate)) THEN 1
ELSE 2
END
WHEN (TEMP_ACT.DTSTART IS NOT NULL AND TEMP_ACT.DTFINISH IS NOT NULL) THEN
CASE
WHEN (TEMP_ACT.DTFINISHPLAN IS NOT NULL AND TEMP_ACT.DTFINISH <= TEMP_ACT.DTFINISHPLAN) THEN 1
WHEN (TEMP_ACT.DTFINISHPLAN IS NOT NULL AND TEMP_ACT.DTFINISH > TEMP_ACT.DTFINISHPLAN) THEN 3
ELSE 2
END
END
WHEN TEMP_ACT.FGSTATUS = 5 OR TEMP_ACT.FGSTATUS = 4 THEN
CASE
WHEN (TEMP_ACT.DTSTARTPLAN IS NULL OR TEMP_ACT.DTFINISH <= TEMP_ACT.DTFINISHPLAN) THEN 1
ELSE 3
END
END FGDEADLINE,
CASE
WHEN TEMP_ACT.FGSTATUS = 1 OR TEMP_ACT.FGSTATUS = 2 THEN
CASE
WHEN (TEMP_ACT.DTSTARTPLAN IS NOT NULL AND TEMP_ACT.DTSTARTPLAN < trunc(sysdate)) THEN TEMP_ACT.DTSTARTPLAN
WHEN (TEMP_ACT.DTSTARTPLAN IS NULL OR TEMP_ACT.DTSTARTPLAN >= trunc(sysdate)) THEN TEMP_ACT.DTSTARTPLAN
END
WHEN TEMP_ACT.FGSTATUS = 3 OR TEMP_ACT.FGSTATUS = 13 THEN
CASE
WHEN (TEMP_ACT.DTSTART IS NULL) THEN
CASE
WHEN (TEMP_ACT.DTSTARTPLAN < trunc(sysdate)) THEN TEMP_ACT.DTSTARTPLAN
WHEN (TEMP_ACT.DTSTARTPLAN > trunc(sysdate)) THEN TEMP_ACT.DTSTARTPLAN
ELSE TEMP_ACT.DTSTARTPLAN
END
WHEN (TEMP_ACT.DTSTART IS NOT NULL AND TEMP_ACT.DTFINISH IS NULL) THEN
CASE
WHEN (TEMP_ACT.DTFINISHPLAN < trunc(sysdate)) THEN TEMP_ACT.DTFINISHPLAN
WHEN (TEMP_ACT.DTFINISHPLAN > trunc(sysdate)) THEN TEMP_ACT.DTFINISHPLAN
ELSE TEMP_ACT.DTFINISHPLAN
END
WHEN (TEMP_ACT.DTSTART IS NOT NULL AND TEMP_ACT.DTFINISH IS NOT NULL) THEN
CASE
WHEN (TEMP_ACT.DTFINISHPLAN IS NOT NULL AND TEMP_ACT.DTFINISH <= TEMP_ACT.DTFINISHPLAN) THEN TEMP_ACT.DTFINISHPLAN
WHEN (TEMP_ACT.DTFINISHPLAN IS NOT NULL AND TEMP_ACT.DTFINISH > TEMP_ACT.DTFINISHPLAN) THEN TEMP_ACT.DTFINISHPLAN
ELSE NULL
END
END
WHEN TEMP_ACT.FGSTATUS = 5 OR TEMP_ACT.FGSTATUS = 4 THEN
CASE
WHEN (TEMP_ACT.DTSTARTPLAN IS NULL OR TEMP_ACT.DTFINISH <= TEMP_ACT.DTFINISHPLAN) THEN TEMP_ACT.DTSTARTPLAN
ELSE TEMP_ACT.DTSTARTPLAN
END
END DTDEADLINE
FROM GNACTIVITY TEMP_ACT
) CUSTOM_GNACT ON ( CUSTOM_GNACT.CDGENACTIVITY = GNACT.CDGENACTIVITY )
INNER JOIN GNCOSTCONFIG GNCOSTCFG ON ( GNCOSTCFG.CDCOSTCONFIG = GNACT.CDCOSTCONFIG )
LEFT JOIN GNEVALRESULTUSED GNEVRESULTUSED ON ( GNEVRESULTUSED.CDEVALRESULTUSED = GNACT.CDEVALRSLTPRIORITY )
LEFT JOIN GNEVALRESULT GNEVRESULT ON ( GNEVRESULT.CDEVALRESULT = GNEVRESULTUSED.CDEVALRESULT )
INNER JOIN ASEXECACTIVITY ASEXECACT ON ( ASEXECACT.CDGENACTIVITY = GNACT.CDGENACTIVITY )
LEFT JOIN ASPLANACTIVITY EXEC_ASPLANACT ON ( EXEC_ASPLANACT.CDPLANACTIVITY = ASEXECACT.CDPLANNING )
INNER JOIN ASACTIVITY ASACTMODEL ON ( ASACTMODEL.CDACTIVITY = ASEXECACT.CDACTIVITY )
LEFT JOIN ASEXECACTIVASSET ASEXECACTAST ON ( ASEXECACT.FGMULTIPLEASSET = 2 AND ASEXECACT.CDEXECACTIVITY = ASEXECACTAST.CDEXECACTIVITY )
LEFT JOIN OBOBJECT OBOBJ ON ( ASEXECACT.FGMULTIPLEASSET = 2 AND ASEXECACTAST.CDASSET = OBOBJ.CDOBJECT AND OBOBJ.FGCURRENT = 1 )
LEFT JOIN OBOBJECTGROUP OBJGRP ON ( ASEXECACT.FGMULTIPLEASSET = 2 AND OBJGRP.CDOBJECTGROUP = OBOBJ.CDOBJECT )
LEFT JOIN OBOBJECTTYPE OBJTYPE ON ( ASEXECACT.FGMULTIPLEASSET = 2 AND OBJTYPE.CDOBJECTTYPE = OBJGRP.CDOBJECTTYPE )
LEFT JOIN ASASSET ASAST ON ( ASEXECACT.FGMULTIPLEASSET = 2 AND OBOBJ.CDREVISION = ASAST.CDREVISION AND ASEXECACTAST.CDASSET = ASAST.CDASSET )
LEFT JOIN ASSERVICECENTER ASSRVCNTR ON ( ASSRVCNTR.CDSERVICECENTER = ASEXECACT.CDSERVICECENTERE )
LEFT JOIN GNRESOURCE GNRV ON ( GNRV.CDRESOURCE = ASEXECACT.CDRESOURCERESP )
LEFT JOIN MAMAINTTYPE ALIAS_MAMAINTTYPE ON ( ALIAS_MAMAINTTYPE.CDMAINTTYPE = ASEXECACT.CDACTIVCLASS )
LEFT JOIN ADTEAM PLANTEAM ON ( EXEC_ASPLANACT.CDTEAM = PLANTEAM.CDTEAM )
WHERE (
GNACT.CDISOSYSTEM IN (126) AND ASEXECACT.FGACTTYPE IN (2,6,7,8) /*OBS 2*/
) AND EXISTS (
SELECT 1
FROM ASEXECACTIVASSET CHKEXECACT
INNER JOIN ASASSET CHKAST ON (CHKAST.CDASSET = CHKEXECACT.CDASSET)
INNER JOIN OBOBJECT CHKOBJ ON (CHKOBJ.CDOBJECT = CHKAST.CDASSET AND CHKOBJ.CDREVISION = CHKAST.CDREVISION)
INNER JOIN OBOBJECTGROUP CHKOBJGRP ON (CHKOBJGRP.CDOBJECTGROUP = CHKOBJ.CDOBJECT)
INNER JOIN OBOBJECTTYPE CHKOBJTYPE ON (CHKOBJTYPE.CDOBJECTTYPE = CHKOBJGRP.CDOBJECTTYPE)
WHERE
CHKEXECACT.CDEXECACTIVITY = ASEXECACT.CDEXECACTIVITY
AND CHKOBJ.FGCURRENT = 1
AND CHKAST.FGASSTATUS <> 4
) AND GNACT.FGSTATUS IN (1,2,3,4,5,13) /*OBS 3*/
|
SQL Server
SELECT
GNACT.IDACTIVITY,
CUSTOM_GNACT.FGDEADLINE,
CUSTOM_GNACT.DTDEADLINE,
GNEVRESULT.NMEVALRESULT,
COALESCE(GNEVRESULTUSED.VLEVALRESULT, CAST( -999 AS NUMERIC(18,8) ) ) VLMINRESULT,
(SELECT IDUSER + ' - ' + NMUSER FROM ADUSER WHERE CDUSER = GNACT.CDUSER) AS NMEXECUTOR,
GNACT.FGSTATUS,
CASE
WHEN EXISTS (SELECT 1 FROM GNASSOCATTACH GNATTACH WHERE GNATTACH.CDASSOC = GNACT.CDASSOC ) THEN 1
ELSE 0
END AS FGATTACHMENT,
ASEXECACT.FGACTTYPE,
CASE
WHEN ASEXECACT.FGMULTIPLEASSET = 2 THEN OBOBJ.IDOBJECT + ' - ' + OBOBJ.NMOBJECT
WHEN ASEXECACT.FGMULTIPLEASSET = 1 THEN 'Múltiplos ativos'
ELSE NULL
END VIEW_EXECASSETS,
EXEC_ASPLANACT.IDPLANACTIVITY,
EXEC_ASPLANACT.NMPLANACTIVITY,
CASE
WHEN EXEC_ASPLANACT.CDTEAM IS NOT NULL THEN PLANTEAM.IDTEAM + ' - ' + PLANTEAM.NMTEAM
ELSE NULL
END AS VIEW_PLANTEAM,
ASACTMODEL.IDACTIVITY IDACTIVITYMODEL,
ASACTMODEL.NMACTIVITY NMACTIVITYMODEL,
ALIAS_MAMAINTTYPE.IDMAINTTYPE,
ALIAS_MAMAINTTYPE.NMMAINTTYPE,
CASE
WHEN ASSRVCNTR.CDSERVICECENTER IS NOT NULL THEN ASSRVCNTR.IDCOMMERCIAL + ' - ' + ASSRVCNTR.NMCOMPANY
ELSE NULL
END NMSERVICECENTER,
CASE
WHEN GNRV.IDRESOURCE IS NOT NULL THEN GNRV.IDRESOURCE +' - '+ GNRV.NMRESOURCE
ELSE ''
END AS NMRESOURCERESP,
GNACT.DTSTARTPLAN,
GNACT.DTSTART,
GNACT.DTFINISHPLAN,
GNACT.DTFINISH,
(SELECT
CASE
WHEN VLPARAM = 1 THEN
CASE
WHEN DTFINISH > DTFINISHPLAN THEN DATEDIFF(DAY, CAST(DTFINISHPLAN AS DATETIME), CAST(DTFINISH AS DATETIME))
ELSE DATEDIFF(DAY, CAST(DTFINISH AS DATETIME), CAST(DTFINISHPLAN AS DATETIME))
END
ELSE (
SELECT
COUNT( 1 ) - 1
FROM GNCALENDARWORKDAY
WHERE
CDCALENDAR = (
SELECT
CASE
WHEN VLPARAM = 1 THEN 0
WHEN VLPARAM = 2 THEN (SELECT MAX(CDCALENDAR) CDCALENDAR FROM GNCALENDAR WHERE FGDEFAULT = 1)
ELSE (SELECT MAX(CDCALENDAR) FROM GNRESOURCE WHERE CDASSET = OBOBJ.CDOBJECT)
END AS CDCALENDARIOATIVO
FROM ADPARAMS
WHERE
CDISOSYSTEM = 109
AND CDPARAM = 257
)
AND (
DTDAY <= DTFINISHPLAN AND DTDAY >= DTFINISH
OR DTDAY <= DTFINISH AND DTDAY >= DTFINISHPLAN
)
)
END QTDTFINISHDIFF
FROM ADPARAMS
WHERE
CDISOSYSTEM = 109
AND CDPARAM = 257
) QTDTFINISHDIFF, /*OBS 1*/
GNACT.VLPERCENTAGEM,
GNACT.QTMINUTESPLAN,
GNACT.QTMINUTESREAL,
CASE
WHEN GNACT.QTMINUTESPLAN IS NULL THEN NULL
WHEN GNACT.QTMINUTESREAL IS NULL THEN NULL
ELSE (GNACT.QTMINUTESREAL - GNACT.QTMINUTESPLAN)
END AS QTMINUTESDIFF,
GNCOSTCFG.MNCOSTPROGTEC,
GNCOSTCFG.MNCOSTREALTEC,
GNCOSTCFG.MNCOSTREALTEC - GNCOSTCFG.MNCOSTPROGTEC AS VIEW_MNCOSTDIFFTEC,
GNCOSTCFG.MNCOSTPROGMAT,
GNCOSTCFG.MNCOSTREALMAT,
GNCOSTCFG.MNCOSTREALMAT - GNCOSTCFG.MNCOSTPROGMAT AS VIEW_MNCOSTDIFFMAT,
GNCOSTCFG.MNCOSTPROGASSET,
GNCOSTCFG.MNCOSTREALASSET,
GNCOSTCFG.MNCOSTREALASSET - GNCOSTCFG.MNCOSTPROGASSET AS VIEW_MNCOSTDIFFASSET,
GNCOSTCFG.MNCOSTPROGTASK,
GNCOSTCFG.MNCOSTREALTASK,
GNCOSTCFG.MNCOSTREALTASK - GNCOSTCFG.MNCOSTPROGTASK AS VIEW_MNCOSTDIFFTASK,
GNCOSTCFG.MNCOSTPROGEXTRA,
GNCOSTCFG.MNCOSTREALEXTRA,
GNCOSTCFG.MNCOSTREALEXTRA - GNCOSTCFG.MNCOSTPROGEXTRA AS VIEW_MNCOSTDIFFEXTRA,
GNCOSTCFG.MNCOSTPROG,
GNCOSTCFG.MNCOSTREAL,
GNCOSTCFG.MNCOSTREAL - GNCOSTCFG.MNCOSTPROG AS VIEW_MNCOSTDIFF
FROM GNACTIVITY GNACT
INNER JOIN (
SELECT
TEMP_ACT.CDGENACTIVITY,
CASE
WHEN TEMP_ACT.FGSTATUS = 1 OR TEMP_ACT.FGSTATUS = 2 THEN
CASE
WHEN (TEMP_ACT.DTSTARTPLAN IS NOT NULL AND TEMP_ACT.DTSTARTPLAN < dateadd(dd, datediff(dd,0, getDate()), 0)) THEN 3
WHEN (TEMP_ACT.DTSTARTPLAN IS NULL OR TEMP_ACT.DTSTARTPLAN >= dateadd(dd, datediff(dd,0, getDate()), 0)) THEN 1
END
WHEN TEMP_ACT.FGSTATUS = 3 OR TEMP_ACT.FGSTATUS = 13 THEN
CASE
WHEN (TEMP_ACT.DTSTART IS NULL) THEN
CASE
WHEN (TEMP_ACT.DTSTARTPLAN < dateadd(dd, datediff(dd,0, getDate()), 0)) THEN 3
WHEN (TEMP_ACT.DTSTARTPLAN > dateadd(dd, datediff(dd,0, getDate()), 0)) THEN 1
ELSE 2
END
WHEN (TEMP_ACT.DTSTART IS NOT NULL AND TEMP_ACT.DTFINISH IS NULL) THEN
CASE
WHEN (TEMP_ACT.DTFINISHPLAN < dateadd(dd, datediff(dd,0, getDate()), 0)) THEN 3
WHEN (TEMP_ACT.DTFINISHPLAN > dateadd(dd, datediff(dd,0, getDate()), 0)) THEN 1
ELSE 2
END
WHEN (TEMP_ACT.DTSTART IS NOT NULL AND TEMP_ACT.DTFINISH IS NOT NULL) THEN
CASE
WHEN (TEMP_ACT.DTFINISHPLAN IS NOT NULL AND TEMP_ACT.DTFINISH <= TEMP_ACT.DTFINISHPLAN) THEN 1
WHEN (TEMP_ACT.DTFINISHPLAN IS NOT NULL AND TEMP_ACT.DTFINISH > TEMP_ACT.DTFINISHPLAN) THEN 3
ELSE 2
END
END
WHEN TEMP_ACT.FGSTATUS = 5 OR TEMP_ACT.FGSTATUS = 4 THEN
CASE
WHEN (TEMP_ACT.DTSTARTPLAN IS NULL OR TEMP_ACT.DTFINISH <= TEMP_ACT.DTFINISHPLAN) THEN 1
ELSE 3
END
END FGDEADLINE,
CASE
WHEN TEMP_ACT.FGSTATUS = 1 OR TEMP_ACT.FGSTATUS = 2 THEN
CASE
WHEN (TEMP_ACT.DTSTARTPLAN IS NOT NULL AND TEMP_ACT.DTSTARTPLAN < dateadd(dd, datediff(dd,0, getDate()), 0)) THEN TEMP_ACT.DTSTARTPLAN
WHEN (TEMP_ACT.DTSTARTPLAN IS NULL OR TEMP_ACT.DTSTARTPLAN >= dateadd(dd, datediff(dd,0, getDate()), 0)) THEN TEMP_ACT.DTSTARTPLAN
END
WHEN TEMP_ACT.FGSTATUS = 3 OR TEMP_ACT.FGSTATUS = 13 THEN
CASE
WHEN (TEMP_ACT.DTSTART IS NULL) THEN
CASE
WHEN (TEMP_ACT.DTSTARTPLAN < dateadd(dd, datediff(dd,0, getDate()), 0)) THEN TEMP_ACT.DTSTARTPLAN
WHEN (TEMP_ACT.DTSTARTPLAN > dateadd(dd, datediff(dd,0, getDate()), 0)) THEN TEMP_ACT.DTSTARTPLAN
ELSE TEMP_ACT.DTSTARTPLAN
END
WHEN (TEMP_ACT.DTSTART IS NOT NULL AND TEMP_ACT.DTFINISH IS NULL) THEN
CASE
WHEN (TEMP_ACT.DTFINISHPLAN < dateadd(dd, datediff(dd,0, getDate()), 0)) THEN TEMP_ACT.DTFINISHPLAN
WHEN (TEMP_ACT.DTFINISHPLAN > dateadd(dd, datediff(dd,0, getDate()), 0)) THEN TEMP_ACT.DTFINISHPLAN
ELSE TEMP_ACT.DTFINISHPLAN
END
WHEN (TEMP_ACT.DTSTART IS NOT NULL AND TEMP_ACT.DTFINISH IS NOT NULL) THEN
CASE
WHEN (TEMP_ACT.DTFINISHPLAN IS NOT NULL AND TEMP_ACT.DTFINISH <= TEMP_ACT.DTFINISHPLAN) THEN TEMP_ACT.DTFINISHPLAN
WHEN (TEMP_ACT.DTFINISHPLAN IS NOT NULL AND TEMP_ACT.DTFINISH > TEMP_ACT.DTFINISHPLAN) THEN TEMP_ACT.DTFINISHPLAN
ELSE NULL
END
END
WHEN TEMP_ACT.FGSTATUS = 5 OR TEMP_ACT.FGSTATUS = 4 THEN
CASE
WHEN (TEMP_ACT.DTSTARTPLAN IS NULL OR TEMP_ACT.DTFINISH <= TEMP_ACT.DTFINISHPLAN) THEN TEMP_ACT.DTSTARTPLAN
ELSE TEMP_ACT.DTSTARTPLAN
END
END DTDEADLINE
FROM GNACTIVITY TEMP_ACT
) CUSTOM_GNACT ON ( CUSTOM_GNACT.CDGENACTIVITY = GNACT.CDGENACTIVITY )
INNER JOIN GNCOSTCONFIG GNCOSTCFG ON ( GNCOSTCFG.CDCOSTCONFIG = GNACT.CDCOSTCONFIG )
LEFT JOIN GNEVALRESULTUSED GNEVRESULTUSED ON ( GNEVRESULTUSED.CDEVALRESULTUSED = GNACT.CDEVALRSLTPRIORITY )
LEFT JOIN GNEVALRESULT GNEVRESULT ON ( GNEVRESULT.CDEVALRESULT = GNEVRESULTUSED.CDEVALRESULT )
INNER JOIN ASEXECACTIVITY ASEXECACT ON ( ASEXECACT.CDGENACTIVITY = GNACT.CDGENACTIVITY )
LEFT JOIN ASPLANACTIVITY EXEC_ASPLANACT ON ( EXEC_ASPLANACT.CDPLANACTIVITY = ASEXECACT.CDPLANNING )
INNER JOIN ASACTIVITY ASACTMODEL ON ( ASACTMODEL.CDACTIVITY = ASEXECACT.CDACTIVITY )
LEFT JOIN ASEXECACTIVASSET ASEXECACTAST ON ( ASEXECACT.FGMULTIPLEASSET = 2 AND ASEXECACT.CDEXECACTIVITY = ASEXECACTAST.CDEXECACTIVITY )
LEFT JOIN OBOBJECT OBOBJ ON ( ASEXECACT.FGMULTIPLEASSET = 2 AND ASEXECACTAST.CDASSET = OBOBJ.CDOBJECT AND OBOBJ.FGCURRENT = 1 )
LEFT JOIN OBOBJECTGROUP OBJGRP ON ( ASEXECACT.FGMULTIPLEASSET = 2 AND OBJGRP.CDOBJECTGROUP = OBOBJ.CDOBJECT )
LEFT JOIN OBOBJECTTYPE OBJTYPE ON ( ASEXECACT.FGMULTIPLEASSET = 2 AND OBJTYPE.CDOBJECTTYPE = OBJGRP.CDOBJECTTYPE )
LEFT JOIN ASASSET ASAST ON ( ASEXECACT.FGMULTIPLEASSET = 2 AND OBOBJ.CDREVISION = ASAST.CDREVISION AND ASEXECACTAST.CDASSET = ASAST.CDASSET )
LEFT JOIN ASSERVICECENTER ASSRVCNTR ON ( ASSRVCNTR.CDSERVICECENTER = ASEXECACT.CDSERVICECENTERE )
LEFT JOIN GNRESOURCE GNRV ON ( GNRV.CDRESOURCE = ASEXECACT.CDRESOURCERESP )
LEFT JOIN MAMAINTTYPE ALIAS_MAMAINTTYPE ON ( ALIAS_MAMAINTTYPE.CDMAINTTYPE = ASEXECACT.CDACTIVCLASS )
LEFT JOIN ADTEAM PLANTEAM ON ( EXEC_ASPLANACT.CDTEAM = PLANTEAM.CDTEAM )
WHERE (
GNACT.CDISOSYSTEM IN (126) AND ASEXECACT.FGACTTYPE IN (2,6,7,8) /*OBS 2*/
) AND EXISTS (
SELECT 1
FROM ASEXECACTIVASSET CHKEXECACT
INNER JOIN ASASSET CHKAST ON (CHKAST.CDASSET = CHKEXECACT.CDASSET)
INNER JOIN OBOBJECT CHKOBJ ON (CHKOBJ.CDOBJECT = CHKAST.CDASSET AND CHKOBJ.CDREVISION = CHKAST.CDREVISION)
INNER JOIN OBOBJECTGROUP CHKOBJGRP ON (CHKOBJGRP.CDOBJECTGROUP = CHKOBJ.CDOBJECT)
INNER JOIN OBOBJECTTYPE CHKOBJTYPE ON (CHKOBJTYPE.CDOBJECTTYPE = CHKOBJGRP.CDOBJECTTYPE)
WHERE
CHKEXECACT.CDEXECACTIVITY = ASEXECACT.CDEXECACTIVITY
AND CHKOBJ.FGCURRENT = 1
AND CHKAST.FGASSTATUS <> 4
) AND GNACT.FGSTATUS IN (1,2,3,4,5,13) /*OBS 3*/
|
Observações
▪1: A diferença entra as datas de término é calculada de acordo com o parâmetro geral de fórmula de cálculo. Campo VLPARAM, definido desta maneira: 1 = Considera 24 por 7; 2 = Com base no calendário padrão do sistema; 3 = Com base no calendário associado ao ativo. ▪2: O campo FGACTTYPE da tabela ASEXECACTIVITY indica o tipo de atividade, definido desta maneira: 2 = Manutenção preventiva; 6 = Rota de manutenção; 7 = Manutenção programada; 8 = Manutenção corretiva. ▪3: O campo FGSTATUS da tabela GNACTIVITY indica a situação da atividade, definido desta maneira: 1 = Planejamento; 2 = Aprovação do planejamento; 3 = Execução; 4 = Aprovação da execução; 5 = Finalizado; 13 = A iniciar.
Campos utilizados na consulta
Nº
|
ID do campo
|
Tipo
|
Observação
|
1
|
IDACTIVITY
|
VARCHAR(50)
|
Identificador da atividade
|
2
|
FGDEADLINE
|
NUMERIC(2)
|
Prazo:
1- Em dia
2 - Em aviso
3 - Em atraso
|
3
|
NMEVALRESULT
|
VARCHAR(255)
|
Prioridade
|
4
|
NMEXECUTOR
|
VARCHAR(255)
|
Responsável
|
5
|
FGSTATUS
|
NUMERIC(2)
|
Situação:
1- Planejamento
2- Aprovação do planejamento
3- Execução
4- Aprovação da execução
5- Encerrada
6- Cancelada
8- Atividade reprovada
13- A iniciar
|
6
|
FGATTACHMENT
|
NUMERIC(2)
|
Contém anexo 1-Sim; 2-Não
|
7
|
FGACTTYPE
|
NUMERIC(2)
|
Tipo da atividade:
1- Verificação
2- Manutenção preventiva
3- Calibração
6- Rota de manutenção
7- Manutenção programada
8- Manutenção corretiva
|
8
|
VIEW_EXECASSET
|
VARCHAR(255)
|
Ativo
|
9
|
IDPLANACTIVITY
|
VARCHAR(50)
|
Identificador do plano
|
10
|
NMPLANACTIVITY
|
VARCHAR(255)
|
Nome do plano
|
11
|
VIEW_PLANTEAM
|
VARCHAR(255)
|
Equipe responsável
|
12
|
IDACTIVITYMODEL
|
VARCHAR(50)
|
Identificador da atividade modelo
|
13
|
NMACTIVITYMODEL
|
VARCHAR(255)
|
Nome da atividade modelo
|
14
|
IDMAINTTYPE
|
VARCHAR(50)
|
Identificador da classificação da atividade
|
15
|
NMMAINTTYPE
|
VARCHAR(255)
|
Nome da classificação da atividade
|
16
|
NMSERVICECENTER
|
VARCHAR(255)
|
Centro de serviço
|
17
|
NMRESOURCERESP
|
VARCHAR(255)
|
Técnico responsável
|
18
|
DTSTARTPLAN
|
DATETIME
|
Início planejado
|
19
|
DTSTART
|
DATETIME
|
Início real
|
20
|
DTFINISHPLAN
|
DATETIME
|
Término planejado
|
21
|
DTFINISH
|
DATETIME
|
Término real
|
22
|
QTDTFINISHDIFF
|
NUMERIC(10)
|
Diferença entre as datas de término
|
23
|
VLPERCENTAGEM
|
NUMERIC(28,12)
|
Porcentagem
|
24
|
QTMINUTESPLAN
|
NUMERIC(10)
|
Duração planejada em minutos
|
25
|
QTMINUTESREAL
|
NUMERIC(10)
|
Duração real em minutos
|
26
|
QTMINUTESDIFF
|
NUMERIC(10)
|
Diferença entre duração real e planejada
|
27
|
MNCOSTPROGTEC
|
NUMERIC(28,12)
|
Custo total planejado dos técnicos alocados
|
28
|
MNCOSTREALTEC
|
NUMERIC(28,12)
|
Custo total real dos técnicos alocados
|
29
|
VIEW_MNCOSTDIFFTEC
|
NUMERIC(28,12)
|
Diferença entre o custo total planejado e o custo total real dos técnicos
|
30
|
MNCOSTPROGMAT
|
NUMERIC(28,12)
|
Custo total planejado dos insumos
|
31
|
MNCOSTREALMAT
|
NUMERIC(28,12)
|
Custo total real dos insumos
|
32
|
VIEW_MNCOSTDIFFMAT
|
NUMERIC(28,12)
|
Diferença entre o custo total planejado dos insumos e o custo total real dos insumos
|
33
|
MNCOSTPROGASSET
|
NUMERIC(28,12)
|
Custo total planejado dos ativos
|
34
|
MNCOSTREALASSET
|
NUMERIC(28,12)
|
Custo total real dos ativos
|
35
|
VIEW_MNCOSTDIFFASSET
|
NUMERIC(28,12)
|
Diferença entre o custo total planejado dos ativos e o custo total real dos ativos
|
36
|
MNCOSTPROGTASK
|
NUMERIC(28,12)
|
Custo total planejado das tarefas
|
37
|
MNCOSTREALTASK
|
NUMERIC(28,12)
|
Custo total real das tarefas
|
38
|
VIEW_MNCOSTDIFFTASK
|
NUMERIC(28,12)
|
Diferença entre o custo planejado das tarefas e o custo real das tarefas
|
39
|
MNCOSTPROGEXTRA
|
NUMERIC(28,12)
|
Custo total planejado extra
|
40
|
MNCOSTREALEXTRA
|
NUMERIC(28,12)
|
Custo total real extra
|
41
|
VIEW_MNCOSTDIFFEXTRA
|
NUMERIC(28,12)
|
Diferença entre o custo planejado extra e o custo real extra
|
42
|
MNCOSTPROG
|
NUMERIC(28,12)
|
Custo total planejado
|
43
|
MNCOSTREAL
|
NUMERIC(28,12)
|
Custo total real
|
44
|
VIEW_MNCOSTDIFF
|
NUMERIC(28,12)
|
Diferença entre o custo planejado e real
|
|
