Overview
This command will list the maintenance activities performed in the assets and their main information, regardless of their current status.
Related tables
TABLE
|
Description
|
Primary key (PK)
|
Connection tables
|
GNACTIVITY
|
Activity record
|
CDGENACTIVITY
|
ADALLUSERS
ADDEPARTMENT
ADMAILTASKREL
ADPOSITION
ADROLE
ADTEAM
ADUSEREXTERNALDATA
GNACTIVITYTIMECFG
GNAPPROV
GNASSOC
GNCALENDAR
GNCHECKLISTANSWER
GNCOSTCONFIG
GNEVALRESULTUSED
GNEVALREVISION
GNFAVORITE
GNOBJECTASSOCGROUP
GNTYPEROLE
SERICHTEXT
|
GNCOSTCONFIG
|
Stores the activity costs
|
CDCOSTCONFIG
|
|
GNEVALRESULTUSED
|
Result of evaluations performed
|
CDEVALRESULTUSED
|
GNEVALRESULT
|
GNEVALRESULT
|
Evaluation method results
|
CDEVALRESULT
|
GNEVALREVISION
|
ASEXECACTIVITY
|
Activity execution record
|
CDEXECACTIVITY
|
ADDOCUMENT
ASACTIVITY
ASPLANACTIVITY
ASSERVICECENTER
ASSERVICECENTERTEC
DCDOCREVISION
GNACTIVITY
GNFAVORITE
GNRESOURCE
GNTOOLSANALISYS
MAMAINTTYPE
|
ASPLANACTIVITY
|
Activity plan record
|
CDPLANACTIVITY
|
ADMAILTASKREL
ADTEAM
ASACTIVITY
GNCALENDAR
GNEVALRESULTUSED
GNEVALREVISION
MAROUTE
|
ASACTIVITY
|
Standard activity record
|
CDACTIVITY
|
ADAPPROVALROUTE
ADCHECKLIST
ADDOCUMENT
ADTEAM
ASACTIVMODELTYPE
ASSERVICECENTER
ASSERVICECENTERTEC
CACONFIGURATION
GNACTIVITYTIMECFG
GNCOSTCONFIG
GNEVALRESULTUSED
GNEVALREVISION
GNMASK
GNRESOURCE
MAMAINTTYPE
SERICHTEXT
|
ASEXECACTIVASSET
|
Association of assets with the activity
|
CDEXECACTIVASSET
|
ASEXECACTIVITY
OBOBJECTGROUP
|
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
|
ASASSET
|
Assets
|
CDASSET
CDREVISION
|
ADCHECKLIST
ADCOMPANY
ADTEAM
ASCONTROLS
ASDEPRECIATION
ASGENCONFIG
ASSTATE
DCDOCUMENT
|
ASSERVICECENTER
|
Service center record
|
CDSERVICECENTER
|
GNCALENDAR
|
GNRESOURCE
|
Resources
|
CDRESOURCE
|
ADALLUSERS
ADCOMPANY
GNASSOC
GNCALENDAR
GNRESOURCETYPE
OBOBJECTGROUP
|
MAMAINTTYPE
|
Maintenance type record
|
CDMAINTTYPE
|
|
ADTEAM
|
Teams
|
CDTEAM
|
ADAUTHLDAPCONFIG
GNMAPPING
GNPERMISSION
|
ADALLUSERS
|
User
|
CDUSER
|
ADALLUSERS
ADUSEREXTERNALDATA
GNFAVORITE
|
GNASSOCATTACH
|
Attachment
|
CDASSOCATTACH
|
ADATTACHMENT
GNASSOC
|
GNCALENDARWORKDAY
|
Working days
|
CDCALENDAR
DTDAY
|
|
ADPARAMS
|
General parameters
|
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 'Multiple assets'
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, /*NOTE 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) /*NOTE 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) /*NOTE 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 'Multiple assets'
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, /*NOTE 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) /*NOTE 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) /*NOTE 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 'Multiple assets'
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, /*NOTE 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) /*NOTE 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) /*NOTE 3*/
|
Notes
▪1: The difference between end dates is calculated according to the calculation formula general parameter. The VLPARAM field is defined as: 1 = Considers 24/7; 2 = Based on the default system calendar; 3 = Based on the calendar associated with the asset. ▪2: The FGACTTYPE field in the ASEXECACTIVITY table indicates the activity type, defined as: 2 = Preventive maintenance; 6 = Maintenance route; 7 = Programmed maintenance; 8 = Corrective maintenance. ▪3: The FGSTATUS field in the GNACTIVITY table indicates the activity status, defined as: 1 = Planning; 2 = Planning approval; 3 = Execution; 4 = Execution approval; 5 = Finished; 13 = To be started.
Fields used on the view screen
#
|
Field ID
|
Type
|
Description
|
1
|
IDACTIVITY
|
VARCHAR(50)
|
Activity ID #
|
2
|
FGDEADLINE
|
NUMERIC(2)
|
Deadline:
1- On time
2- Alert
3- Past due
|
3
|
NMEVALRESULT
|
VARCHAR(255)
|
Priority
|
4
|
NMEXECUTOR
|
VARCHAR(255)
|
Responsibility
|
5
|
FGSTATUS
|
NUMERIC(2)
|
Status:
1- Planning
2- Planning approval
3- Execution
4- Execution approval
5- Closed
6- Cancelled
8- Activity rejected
13- To be started
|
6
|
FGATTACHMENT
|
NUMERIC(2)
|
Contains attachment 1-Yes; 2-No
|
7
|
FGACTTYPE
|
NUMERIC(2)
|
Activity type:
1- Verification
2- Preventive maintenance
3- Calibration
6- Maintenance route
7- Programmed maintenance
8- Corrective maintenance
|
8
|
VIEW_EXECASSET
|
VARCHAR(255)
|
Asset
|
9
|
IDPLANACTIVITY
|
VARCHAR(50)
|
Plan ID #
|
10
|
NMPLANACTIVITY
|
VARCHAR(255)
|
Plan name
|
11
|
VIEW_PLANTEAM
|
VARCHAR(255)
|
Responsible team
|
12
|
IDACTIVITYMODEL
|
VARCHAR(50)
|
Standard activity ID #
|
13
|
NMACTIVITYMODEL
|
VARCHAR(255)
|
Standard activity name
|
14
|
IDMAINTTYPE
|
VARCHAR(50)
|
Activity classification ID #
|
15
|
NMMAINTTYPE
|
VARCHAR(255)
|
Activity classification name
|
16
|
NMSERVICECENTER
|
VARCHAR(255)
|
Service center
|
17
|
NMRESOURCERESP
|
VARCHAR(255)
|
Responsible technician
|
18
|
DTSTARTPLAN
|
DATETIME
|
Planned start date
|
19
|
DTSTART
|
DATETIME
|
Actual start date
|
20
|
DTFINISHPLAN
|
DATETIME
|
Planned end date
|
21
|
DTFINISH
|
DATETIME
|
Actual end date
|
22
|
QTDTFINISHDIFF
|
NUMERIC(10)
|
Difference between end dates
|
23
|
VLPERCENTAGEM
|
NUMERIC(28,12)
|
Percentage
|
24
|
QTMINUTESPLAN
|
NUMERIC(10)
|
Estimated duration in minutes
|
25
|
QTMINUTESREAL
|
NUMERIC(10)
|
Actual duration in minutes
|
26
|
QTMINUTESDIFF
|
NUMERIC(10)
|
Difference between actual and estimated duration
|
27
|
MNCOSTPROGTEC
|
NUMERIC(28,12)
|
Planned total cost of allocated technicians
|
28
|
MNCOSTREALTEC
|
NUMERIC(28,12)
|
Actual total cost of allocated technicians
|
29
|
VIEW_MNCOSTDIFFTEC
|
NUMERIC(28,12)
|
Difference between the planned total cost and the actual total cost of technicians
|
30
|
MNCOSTPROGMAT
|
NUMERIC(28,12)
|
Planned total cost of supplies
|
31
|
MNCOSTREALMAT
|
NUMERIC(28,12)
|
Actual total cost of supplies
|
32
|
VIEW_MNCOSTDIFFMAT
|
NUMERIC(28,12)
|
Difference between the planned total cost and the actual total cost of supplies
|
33
|
MNCOSTPROGASSET
|
NUMERIC(28,12)
|
Planned total cost of assets
|
34
|
MNCOSTREALASSET
|
NUMERIC(28,12)
|
Actual total cost of assets
|
35
|
VIEW_MNCOSTDIFFASSET
|
NUMERIC(28,12)
|
Difference between the planned total cost and the actual total cost of assets
|
36
|
MNCOSTPROGTASK
|
NUMERIC(28,12)
|
Planned total cost of tasks
|
37
|
MNCOSTREALTASK
|
NUMERIC(28,12)
|
Actual total cost of tasks
|
38
|
VIEW_MNCOSTDIFFTASK
|
NUMERIC(28,12)
|
Difference between the planned cost and the actual cost of tasks
|
39
|
MNCOSTPROGEXTRA
|
NUMERIC(28,12)
|
Planned total extra cost
|
40
|
MNCOSTREALEXTRA
|
NUMERIC(28,12)
|
Actual total extra cost
|
41
|
VIEW_MNCOSTDIFFEXTRA
|
NUMERIC(28,12)
|
Difference between the planned extra cost and the actual extra cost
|
42
|
MNCOSTPROG
|
NUMERIC(28,12)
|
Planned total cost
|
43
|
MNCOSTREAL
|
NUMERIC(28,12)
|
Actual total cost
|
44
|
VIEW_MNCOSTDIFF
|
NUMERIC(28,12)
|
Difference between the planned and the actual cost
|
|
