Overview
This command will list the data of a maintenance plan.
Related tables
TABLE
|
Description
|
Primary key (PK)
|
Connection tables
|
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
|
ASPLANACTIVASSET
|
Association of assets with the plan
|
CDPLANACTIVASSET
|
ASPLANACTIVITY
OBOBJECTGROUP
|
OBOBJECT
|
Item/Asset/Supply data based on revision
|
CDOBJECT
CDREVISION
|
ADTEAM
GNASSOC
GNREVISION
OBOBJECTGROUP
OBSUBREASON
|
ASASSET
|
Assets
|
CDASSET
CDREVISION
|
ADCHECKLIST
ADCOMPANY
ADTEAM
ASCONTROLS
ASDEPRECIATION
ASGENCONFIG
ASSTATE
DCDOCUMENT
|
MAEQPT
|
Equipment
|
CDEQPT
CDREVISION
|
ADALLUSERS
ADMAILTASKREL
ASASSET
MAEQPTMETERREAD
MAEQPTMETERRESTART
MAEQPTUNITY
|
MAEQPTMETERREAD
|
Asset meter reading record
|
CDEQPT
CDREVISION
NRMETERREAD
|
ASEXECACTIVITY
MAEQPT
|
ADTEAM
|
Teams
|
CDTEAM
|
ADAUTHLDAPCONFIG
GNMAPPING
GNPERMISSION
|
GNCALENDAR
|
Calendar record
|
CDCALENDAR
|
|
GNEVAL
|
Evaluation method record
|
CDEVAL
|
|
GNEVALRESULTUSED
|
Result of evaluations performed
|
CDEVALRESULTUSED
|
GNEVALRESULT
|
GNEVALRESULT
|
Evaluation method results
|
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 'Multiple assets'
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 = 'PLAN_ID' /*NOTE 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 'Multiple assets'
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 = 'PLAN_ID' /*NOTE 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 'Multiple assets'
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 = 'PLAN_ID' /*NOTE 1*/
)
|
Notes
▪1: ID # of the plan to be filtered.
Fields used on the view screen
#
|
Field ID
|
Type
|
Description
|
1
|
IDPLANACTIVITY
|
VARCHAR(50)
|
Plan ID #
|
2
|
NMPLANACTIVITY
|
VARCHAR(255)
|
Plan name
|
3
|
FGPLANTYPE
|
NUMERIC(2)
|
Plan type:
1- Verification
2- Preventive maintenance
3- Calibration
6- Maintenance route;
|
4
|
VIEW_PLANASSETS
|
VARCHAR(255)
|
Plan asset
|
5
|
NMPLANRESPTEAM
|
VARCHAR(255)
|
Responsible team for plan
|
6
|
NMPLANTEAM
|
VARCHAR(255)
|
Responsible team for planning
|
7
|
NMEXECTEAM
|
VARCHAR(255)
|
Responsible team for execution
|
8
|
FGFREQTYPE
|
NUMERIC(2)
|
Frequency type:
1- Date
2- First use
3- Usage time
|
9
|
QTFREQUENCE
|
NUMERIC(10)
|
Frequency
|
10
|
FGFREQUNIT
|
NUMERIC(2)
|
Frequency unit:
1- Daily
2- Weekly
3- Monthly
4- Yearly
5- Working days
|
11
|
FGFORMATDATE
|
NUMERIC(2)
|
Date format:
1- day/month/year
2- month/year
|
12
|
DTEXP
|
DATETIME
|
Next execution date
|
13
|
NMCALENDAR
|
VARCHAR(255)
|
Calendar
|
14
|
VLMETERREADFREQ
|
NUMERIC(28,12)
|
Frequency
|
15
|
VLANTMETERREADFREQ
|
NUMERIC(28,12)
|
Meter reading anticipation
|
16
|
VLEXPMETERREADING
|
NUMERIC(28,12)
|
Next execution
|
17
|
VLREADVALUE
|
NUMERIC(28,12)
|
Current meter reading
|
18
|
DTREAD
|
DATETIME
|
Date of the last meter reading
|
19
|
FGAUTOCREATE
|
NUMERIC(2)
|
Create activity automatically:
1- Yes
2- No
|
20
|
QTCREATEBEFORE
|
NUMERIC(10)
|
Automatic activity creation anticipation days
|
21
|
FGKEEPCREATING
|
NUMERIC(2)
|
Indicates whether to continue to create activities automatically even if there are already pending activities:
1- Yes
2- No
|
22
|
FGCREATESTEP
|
NUMERIC(2)
|
Step in which the activities will be created:
1- Planning
2- To be started
|
23
|
NMEVALMETHOD
|
VARCHAR(255)
|
Evaluation method
|
24
|
NMEVALRESULT
|
VARCHAR(255)
|
Priority result
|
|
