This is an archive of the Maximo Yahoo Community. The content of this pages may be a sometimes obsolete so please check post dates.
Thanks to the community owner Christopher Wanko for providing the content.
IBM highly discourages the use of stored procedures in the Maximo database.
Michael
From: MAXIMO@yahoogroups.com [mailto:MAXIMO@yahoogroups.com] On Behalf Of in2data
Sent: Friday, December 09, 2011 9:59 AM
To: MAXIMO@yahoogroups.com
Subject: Re: [MAXIMO List] KPI Roundup
Hi,
We have one to measure the On-Time PM rate. PMs are on-time only if they are completed within the allowed window. For instance 90 day or less PMs have =/- 7 days of the scheduled start date to be completed. The system copies the target start date from the PM work order to the scheduled start date just after the work order is generated. The stored procedure also does this if the scheduled start date is still null. The site maintenance planner can then move the scheduled start date to align with available downtime.
The way it's calculated is a stored procedure is run on the database each night that updates a custom table OSC_KPI_PM. You can and should create the custom table in Maximo but the stored procedure has to be run from the database. The code is below and it's for SQL Server. Your DB admins can convert to Oracle.
I also included the code to create the table in the DB. You can use this for the table attributes in Maximo.
Then you create a report using the query at the end. again that's for SQL and will need some editing for Oracle. I've added some conditional colors in the report to show where on-time rates are out of the KPI metric limit.
Why did I use a stored procedure? It takes a long time to calculate the KPIs and having a table already populated with the results makes the report run much faster. The users like to run this on demand.
In the code where you run across poolyear and other values like that those are internal calendar periods we use. OSC is an agricultural co-operative and we measure everything by the pool year calendar. Those periods do not align with the normal Jan-Dec calendar. Our pool year starts the end of Feb and has varying pool period lengths from 25 to 32 days. Our financial periods in Maximo are set to those pool periods.
OK. That's a lot of fire hose time. I'll turn it off now.
Enjoy the briar patch!
Dave Bone
Ocean Spray Cranberries
/*Stored Prodecure Code Begins*/
USE [maxprod]
GO
/****** Object: StoredProcedure [dbo].[OSC_KPI_PM_Update] Script Date: 12/09/2011 08:35:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--Drop PROCEDURE OSC_KPI_PM_Update
-- exec OSC_KPI_PM_Update
-- select * from OSC_KPI_PM
CREATE PROCEDURE [dbo].[OSC_KPI_PM_Update]
AS
update workorder set schedfinish = targcompdate
where ((schedfinish is null) and (targcompdate is not null)
and (worktype = 'PM' or worktype = 'PMYR' or worktype = 'REG'))
Delete from OSC_KPI_PM
Declare
@90daykpi as varchar(8),
@over90daykpi as varchar(8),
@fiscalyr as varchar(8)
set @90daykpi = '7'
set @over90daykpi = '15'
set @fiscalyr = (Select LEFT(f.financialperiod, 4) FROM financialperiods f Where orgid = 'OSC' and (f.periodstart <= getdate()) AND (f.periodend > getdate()))
Insert into OSC_KPI_PM (poolperiod,periodstart,periodend,siteid,orgid,comp,can,[open],open_late,comp_ot,comp_early_late,noactfinish,total)
SELECT financialperiod as poolperiod, periodstart, periodend, w.siteid, w.orgid,
Case When (count(CASE WHEN (w.status = 'CLOSE' or w.status = 'COMP' or w.status = 'COMPCLOSE') THEN wonum ELSE null END)) = 0 THEN 1 ELSE (count(CASE WHEN (w.status = 'CLOSE' or w.status = 'COMP' or w.status = 'COMPCLOSE') THEN wonum ELSE null END)) END AS COMP,
--this is the old comp the one above keeps from having a divide by 0 error when no pm is comp,can or open_late count(CASE WHEN (w.status = 'CLOSE' or w.status = 'COMP') THEN wonum ELSE null END) AS COMP,
count(CASE WHEN (w.status = 'CAN' or w.status = 'CANHOL' or w.status = 'CANOPS') THEN wonum ELSE null END) AS CAN,
count(CASE WHEN (w.status <> 'CLOSE' and w.status <> 'COMP' and w.status <> 'COMPCLOSE' and w.status <> 'CAN' and w.status <> 'CANHOL' and w.status <> 'CANOPS')
THEN wonum ELSE null END) AS 'OPEN',
count(CASE WHEN (w.status <> 'CLOSE' and w.status <> 'COMP' and w.status <> 'COMPCLOSE' and w.status <> 'CAN' and w.status <> 'CANHOL' and w.status <> 'CANOPS')
and
(((w.schedfinish + 7 <= getdate())
and ((Case p.frequnit
when 'DAYS' then Isnull(p.frequency,0)
when 'WEEKS' then (Isnull(p.frequency,0) * 7)
when 'MONTHS' then (Isnull(p.frequency,0) * 30)
when 'YEARS' then (Isnull(p.frequency,0) * 365)
else 0
end)*Isnull(s.interval,1) <= '90')
and ((Case p.frequnit
when 'DAYS' then Isnull(p.frequency,0)
when 'WEEKS' then (Isnull(p.frequency,0) * 7)
when 'MONTHS' then (Isnull(p.frequency,0) * 30)
when 'YEARS' then (Isnull(p.frequency,0) * 365)
else 0
end)*Isnull(s.interval,1) >= '28')
and (abs(datediff(dd,w.schedfinish,getdate())) > @90daykpi))
or
((w.schedfinish + 15 <= getdate())
and ((Case p.frequnit
when 'DAYS' then Isnull(p.frequency,0)
when 'WEEKS' then (Isnull(p.frequency,0) * 7)
when 'MONTHS' then (Isnull(p.frequency,0) * 30)
when 'YEARS' then (Isnull(p.frequency,0) * 365)
else 0
end)*Isnull(s.interval,1) > '90')
and (abs(datediff(dd,w.schedfinish,getdate())) > @over90daykpi)))
THEN wonum ELSE null END) AS 'OPEN_LATE',
count(CASE WHEN (w.status = 'CLOSE' or w.status = 'COMP' or w.status = 'COMPCLOSE')
and
((((Case p.frequnit
when 'DAYS' then Isnull(p.frequency,0)
when 'WEEKS' then (Isnull(p.frequency,0) * 7)
when 'MONTHS' then (Isnull(p.frequency,0) * 30)
when 'YEARS' then (Isnull(p.frequency,0) * 365)
else 0
end)*Isnull(s.interval,1) <= '90')
and ((Case p.frequnit
when 'DAYS' then Isnull(p.frequency,0)
when 'WEEKS' then (Isnull(p.frequency,0) * 7)
when 'MONTHS' then (Isnull(p.frequency,0) * 30)
when 'YEARS' then (Isnull(p.frequency,0) * 365)
else 0
end)*Isnull(s.interval,1) >= '28')
and (abs(datediff(dd,w.schedfinish,w.actfinish)) <= @90daykpi))
or
(((Case p.frequnit
when 'DAYS' then Isnull(p.frequency,0)
when 'WEEKS' then (Isnull(p.frequency,0) * 7)
when 'MONTHS' then (Isnull(p.frequency,0) * 30)
when 'YEARS' then (Isnull(p.frequency,0) * 365)
else 0
end)*Isnull(s.interval,1) > '90')
and (abs(datediff(dd,w.schedfinish,w.actfinish)) <= @over90daykpi)))
THEN wonum ELSE null END) AS COMP_OT,
count(CASE WHEN (w.status = 'CLOSE' or w.status = 'COMP' or w.status = 'COMPCLOSE')
and
((((Case p.frequnit
when 'DAYS' then Isnull(p.frequency,0)
when 'WEEKS' then (Isnull(p.frequency,0) * 7)
when 'MONTHS' then (Isnull(p.frequency,0) * 30)
when 'YEARS' then (Isnull(p.frequency,0) * 365)
else 0
end)*Isnull(s.interval,1) <= '90')
and ((Case p.frequnit
when 'DAYS' then Isnull(p.frequency,0)
when 'WEEKS' then (Isnull(p.frequency,0) * 7)
when 'MONTHS' then (Isnull(p.frequency,0) * 30)
when 'YEARS' then (Isnull(p.frequency,0) * 365)
else 0
end)*Isnull(s.interval,1) >= '28')
and (abs(datediff(dd,w.schedfinish,w.actfinish)) > @90daykpi))
or
(((Case p.frequnit
when 'DAYS' then Isnull(p.frequency,0)
when 'WEEKS' then (Isnull(p.frequency,0) * 7)
when 'MONTHS' then (Isnull(p.frequency,0) * 30)
when 'YEARS' then (Isnull(p.frequency,0) * 365)
else 0
end)*Isnull(s.interval,1) > '90')
and (abs(datediff(dd,w.schedfinish,w.actfinish)) > @over90daykpi)))
THEN wonum ELSE null END) AS COMP_EARLY_LATE,
count(CASE WHEN (w.status = 'CLOSE' or w.status = 'COMP' or w.status = 'COMPCLOSE')
and actfinish is null
THEN wonum ELSE null END) AS NOACTFINISH,
count(wonum) AS TOTAL
FROM workorder w
left outer join pm p on (p.pmnum = w.pmnum and p.orgid = w.orgid and p.siteid = w.siteid
and (Case p.frequnit
when 'DAYS' then Isnull(p.frequency,0)
when 'WEEKS' then (Isnull(p.frequency,0) * 7)
when 'MONTHS' then (Isnull(p.frequency,0) * 30)
when 'YEARS' then (Isnull(p.frequency,0) * 365)
else 0
end) >= '28')
join financialperiods f on (w.schedfinish >= f.periodstart and w.schedfinish < f.periodend and w.orgid = f.orgid)
left outer join pmsequence s on (s.jpnum = w.jpnum and s.pmnum = w.pmnum and s.orgid = f.orgid and s.siteid = w.siteid)
WHERE (istask = 0 and w.pmnum is not null) and (woclass = 'WORKORDER' or woclass = 'ACTIVITY')
and (w.worktype = 'PM' or w.worktype = 'PMYR' or w.worktype = 'REG')
and ((Case p.frequnit
when 'DAYS' then Isnull(p.frequency,0)
when 'WEEKS' then (Isnull(p.frequency,0) * 7)
when 'MONTHS' then (Isnull(p.frequency,0) * 30)
when 'YEARS' then (Isnull(p.frequency,0) * 365)
else 0
end)*Isnull(s.interval,1) >= '28')
and (left(financialperiod,4) >= (@fiscalyr - 5))
and w.wonum not in (select wonum from wostatus where status = 'DELSCHED')
GROUP BY financialperiod, periodstart, periodend, w.siteid, w.orgid
order by financialperiod
/*Stored Prodecure Code End*/
/*Table Create Code Begins*/
USE [maxprod]
GO
/****** Object: Table [dbo].[OSC_KPI_MSP] Script Date: 12/09/2011 08:46:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[OSC_KPI_MSP](
[poolyear] AS (left([poolperiod],(4))) PERSISTED,
[poolperiod] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[periodstart] [smalldatetime] NULL,
[periodend] [smalldatetime] NULL,
[siteid] [nchar](24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[orgid] [nchar](24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[comp] [decimal](5, 0) NULL,
[can] [decimal](5, 0) NULL,
[open] [decimal](5, 0) NULL,
[open_late] [decimal](5, 0) NULL,
[comp_ot] [decimal](5, 0) NULL,
[comp_early_late] [decimal](5, 0) NULL,
[noactfinish] [decimal](5, 0) NULL,
[total] [decimal](5, 0) NULL,
[comp_ot_cal] AS ([comp_ot]/(([comp]+[open_late])+[can])) PERSISTED
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
/*Table Create Code Ends*/
/*Report Query Code Begins*/
maximoDataSet = MXReportDataSetProvider.create(this.getDataSource().getName(), this.getName());
maximoDataSet.open();
var temppoolyearString = new String();
var tempsiteidString = new String();
if(params["poolyear"].value)
{
temppoolyearString = reportContext.getParameterValue("poolyear").toUpperCase();
}
else
{
temppoolyearString = "%";
}
if(params["siteid"].value)
{
tempsiteidString = reportContext.getParameterValue("siteid").toUpperCase();
}
else
{
tempsiteidString = "%";
}
var sqlText = new String();
// Add query to sqlText variable.
sqlText = "select poolyear, poolperiod, periodstart, periodend, siteid, orgid, comp, can, [open], open_late, "
+ " comp_ot, comp_early_late, noactfinish, total, comp_ot_cal, getdate() as 'currentdate' "
+ " from osc_kpi_pm "
+ " where " + params["where"]
+ " and siteid like '%" + tempsiteidString + "%' "
+ " and poolyear like '" + temppoolyearString + "' "
+ " order by siteid, poolperiod"
;
maximoDataSet.setQuery(sqlText);
/*Report Query Code Ends*/
--- In MAXIMO@yahoogroups.com<mailto:MAXIMO%40yahoogroups.com>, Chad Stringer <cstringer@...<mailto:cstringer@...>> wrote:
>
> You might try getting a hold of an SMRP (Society of Maintenance and Reliability Professionals) handbook. They have a lot of great metrics that are industry standards to look into. That is a great place to start, and where I pull most of my ideas for KPIs.
> The SQL code is a whole different matter. Many companies use custom fields or different parameters based on how they measure things. I'm sure there are some really good base queries available to use though. Best of luck, and keep us all in the loop on your progress.
>
> CJ Stringer
>
> From: MAXIMO@yahoogroups.com<mailto:MAXIMO%40yahoogroups.com> [mailto:MAXIMO@yahoogroups.com<mailto:MAXIMO%40yahoogroups.com>] On Behalf Of john_gould14
> Sent: Friday, December 09, 2011 8:28 AM
> To: MAXIMO@yahoogroups.com<mailto:MAXIMO%40yahoogroups.com>
> Subject: [MAXIMO List] KPI Roundup
>
>
>
> Hello All - in attempting to further develop our start center KPI's to get better visualization of our orgnaizations performance, I'm always wondering what other KPI's people are using. I have the standard, KPI's like percetnage of work type, average age of work orders, # of work orders reported versus completed, inprogress, open emergencies, PM's overdue - things like that.
>
> If you have some more 'interesting' ones to share I'd love to hear about them. I'm a SQL novice so if you have the SQL code - even better. I'm running version 7.5.0 with Oracle DB.
>
>
>
>
>
________________________________
----Notice Regarding Confidentiality----
This email, including any and all attachments, (this "Email") is intended only for the party to whom it is addressed and may contain information that is confidential or privileged. Sierra Systems Group Inc. and its affiliates accept no responsibility for any loss or damage suffered by any person resulting from any unauthorized use of or reliance upon this Email. If you are not the intended recipient, you are hereby notified that any dissemination, copying or other use of this Email is prohibited. Please notify us of the error in communication by return email and destroy all copies of this Email. Thank you.
We (at least those of us who have been around a while) are all aware of
that, and I have to say that IBM has been making the need for direct
database coding less and less necessary. But there are still times when a
Stored Proc does the job better than anything that can be done using the GUI
tools, especially since the Stored Procs are pre-compiled on the database
side for increased speed.
Even though I'm a dyed-in-the-wool "use the GUI to do everything" kind of
person, I actually considered using a trigger for something the other day .
it had to do with a problem with special characters in the Long Description.
Users were copying the bodies of e-mails (containing vendor quotes) into the
Long Description on PR Lines, and then saving the record. That part worked
fine - but when the record needed to be processed further down the line
(i.e. the Invoice stage), it resulted in a Java error and the inability to
save the invoice until the offending characters were removed. For a while,
the IT tech on the scene was editing the Long Descriptions using a text
tool, which needless to say, was not time-effective. So eventually they
stopped doing the copying altogether, which is a shame. I got the idea of
using a trigger to intercept the text before it went into the
LONGDESCRIPTION table and replace the problem characters to more useful
ones.
So on rare occasions, I still think database-side code is useful - but only
at the discretion of someone who has a lot of experience and knowledge of
the product, and can make a VERY informed judgement.
I do agree with you, though, that people who have knowledge of Java and/or
Database-side code (TRANSACT-SQL or PL/SQL), but are new to Maximo, are
frequently too ready to use code to solve a problem - only to inadvertently
"gum things up" and cause more problems down the line.
Shannon
P.S. If anyone has a solution to the Long Description problem, using the
GUI tools or even Java, speak up - I'd like to hear it. (Although for all I
know, it may be fixed in 7.5 . I'll check it out). I haven't actually
implemented my trigger idea, and I'd rather not, if possible.
From: MAXIMO@yahoogroups.com [mailto:MAXIMO@yahoogroups.com] On Behalf Of
Hall, Michael
Sent: December-09-11 10:50 AM
To: MAXIMO@yahoogroups.com
Subject: RE: [MAXIMO List] KPI Roundup
IBM highly discourages the use of stored procedures in the Maximo database.
Michael
From: MAXIMO@yahoogroups.com <mailto:MAXIMO%40yahoogroups.com>
[mailto:MAXIMO@yahoogroups.com <mailto:MAXIMO%40yahoogroups.com> ] On Behalf
Of in2data
Sent: Friday, December 09, 2011 9:59 AM
To: MAXIMO@yahoogroups.com <mailto:MAXIMO%40yahoogroups.com>
Subject: Re: [MAXIMO List] KPI Roundup
Hi,
We have one to measure the On-Time PM rate. PMs are on-time only if they are
completed within the allowed window. For instance 90 day or less PMs have
=/- 7 days of the scheduled start date to be completed. The system copies
the target start date from the PM work order to the scheduled start date
just after the work order is generated. The stored procedure also does this
if the scheduled start date is still null. The site maintenance planner can
then move the scheduled start date to align with available downtime.
The way it's calculated is a stored procedure is run on the database each
night that updates a custom table OSC_KPI_PM. You can and should create the
custom table in Maximo but the stored procedure has to be run from the
database. The code is below and it's for SQL Server. Your DB admins can
convert to Oracle.
I also included the code to create the table in the DB. You can use this for
the table attributes in Maximo.
Then you create a report using the query at the end. again that's for SQL
and will need some editing for Oracle. I've added some conditional colors in
the report to show where on-time rates are out of the KPI metric limit.
Why did I use a stored procedure? It takes a long time to calculate the KPIs
and having a table already populated with the results makes the report run
much faster. The users like to run this on demand.
In the code where you run across poolyear and other values like that those
are internal calendar periods we use. OSC is an agricultural co-operative
and we measure everything by the pool year calendar. Those periods do not
align with the normal Jan-Dec calendar. Our pool year starts the end of Feb
and has varying pool period lengths from 25 to 32 days. Our financial
periods in Maximo are set to those pool periods.
OK. That's a lot of fire hose time. I'll turn it off now.
Enjoy the briar patch!
Dave Bone
Ocean Spray Cranberries
/*Stored Prodecure Code Begins*/
USE [maxprod]
GO
/****** Object: StoredProcedure [dbo].[OSC_KPI_PM_Update] Script Date:
12/09/2011 08:35:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--Drop PROCEDURE OSC_KPI_PM_Update
-- exec OSC_KPI_PM_Update
-- select * from OSC_KPI_PM
CREATE PROCEDURE [dbo].[OSC_KPI_PM_Update]
AS
update workorder set schedfinish = targcompdate
where ((schedfinish is null) and (targcompdate is not null)
and (worktype = 'PM' or worktype = 'PMYR' or worktype = 'REG'))
Delete from OSC_KPI_PM
Declare
@90daykpi as varchar(8),
@over90daykpi as varchar(8),
@fiscalyr as varchar(8)
set @90daykpi = '7'
set @over90daykpi = '15'
set @fiscalyr = (Select LEFT(f.financialperiod, 4) FROM financialperiods f
Where orgid = 'OSC' and (f.periodstart <= getdate()) AND (f.periodend >
getdate()))
Insert into OSC_KPI_PM
(poolperiod,periodstart,periodend,siteid,orgid,comp,can,[open],open_late,com
p_ot,comp_early_late,noactfinish,total)
SELECT financialperiod as poolperiod, periodstart, periodend, w.siteid,
w.orgid,
Case When (count(CASE WHEN (w.status = 'CLOSE' or w.status = 'COMP' or
w.status = 'COMPCLOSE') THEN wonum ELSE null END)) = 0 THEN 1 ELSE
(count(CASE WHEN (w.status = 'CLOSE' or w.status = 'COMP' or w.status =
'COMPCLOSE') THEN wonum ELSE null END)) END AS COMP,
--this is the old comp the one above keeps from having a divide by 0 error
when no pm is comp,can or open_late count(CASE WHEN (w.status = 'CLOSE' or
w.status = 'COMP') THEN wonum ELSE null END) AS COMP,
count(CASE WHEN (w.status = 'CAN' or w.status = 'CANHOL' or w.status =
'CANOPS') THEN wonum ELSE null END) AS CAN,
count(CASE WHEN (w.status <> 'CLOSE' and w.status <> 'COMP' and w.status <>
'COMPCLOSE' and w.status <> 'CAN' and w.status <> 'CANHOL' and w.status <>
'CANOPS')
THEN wonum ELSE null END) AS 'OPEN',
count(CASE WHEN (w.status <> 'CLOSE' and w.status <> 'COMP' and w.status <>
'COMPCLOSE' and w.status <> 'CAN' and w.status <> 'CANHOL' and w.status <>
'CANOPS')
and
(((w.schedfinish + 7 <= getdate())
and ((Case p.frequnit
when 'DAYS' then Isnull(p.frequency,0)
when 'WEEKS' then (Isnull(p.frequency,0) * 7)
when 'MONTHS' then (Isnull(p.frequency,0) * 30)
when 'YEARS' then (Isnull(p.frequency,0) * 365)
else 0
end)*Isnull(s.interval,1) <= '90')
and ((Case p.frequnit
when 'DAYS' then Isnull(p.frequency,0)
when 'WEEKS' then (Isnull(p.frequency,0) * 7)
when 'MONTHS' then (Isnull(p.frequency,0) * 30)
when 'YEARS' then (Isnull(p.frequency,0) * 365)
else 0
end)*Isnull(s.interval,1) >= '28')
and (abs(datediff(dd,w.schedfinish,getdate())) > @90daykpi))
or
((w.schedfinish + 15 <= getdate())
and ((Case p.frequnit
when 'DAYS' then Isnull(p.frequency,0)
when 'WEEKS' then (Isnull(p.frequency,0) * 7)
when 'MONTHS' then (Isnull(p.frequency,0) * 30)
when 'YEARS' then (Isnull(p.frequency,0) * 365)
else 0
end)*Isnull(s.interval,1) > '90')
and (abs(datediff(dd,w.schedfinish,getdate())) > @over90daykpi)))
THEN wonum ELSE null END) AS 'OPEN_LATE',
count(CASE WHEN (w.status = 'CLOSE' or w.status = 'COMP' or w.status =
'COMPCLOSE')
and
((((Case p.frequnit
when 'DAYS' then Isnull(p.frequency,0)
when 'WEEKS' then (Isnull(p.frequency,0) * 7)
when 'MONTHS' then (Isnull(p.frequency,0) * 30)
when 'YEARS' then (Isnull(p.frequency,0) * 365)
else 0
end)*Isnull(s.interval,1) <= '90')
and ((Case p.frequnit
when 'DAYS' then Isnull(p.frequency,0)
when 'WEEKS' then (Isnull(p.frequency,0) * 7)
when 'MONTHS' then (Isnull(p.frequency,0) * 30)
when 'YEARS' then (Isnull(p.frequency,0) * 365)
else 0
end)*Isnull(s.interval,1) >= '28')
and (abs(datediff(dd,w.schedfinish,w.actfinish)) <= @90daykpi))
or
(((Case p.frequnit
when 'DAYS' then Isnull(p.frequency,0)
when 'WEEKS' then (Isnull(p.frequency,0) * 7)
when 'MONTHS' then (Isnull(p.frequency,0) * 30)
when 'YEARS' then (Isnull(p.frequency,0) * 365)
else 0
end)*Isnull(s.interval,1) > '90')
and (abs(datediff(dd,w.schedfinish,w.actfinish)) <= @over90daykpi)))
THEN wonum ELSE null END) AS COMP_OT,
count(CASE WHEN (w.status = 'CLOSE' or w.status = 'COMP' or w.status =
'COMPCLOSE')
and
((((Case p.frequnit
when 'DAYS' then Isnull(p.frequency,0)
when 'WEEKS' then (Isnull(p.frequency,0) * 7)
when 'MONTHS' then (Isnull(p.frequency,0) * 30)
when 'YEARS' then (Isnull(p.frequency,0) * 365)
else 0
end)*Isnull(s.interval,1) <= '90')
and ((Case p.frequnit
when 'DAYS' then Isnull(p.frequency,0)
when 'WEEKS' then (Isnull(p.frequency,0) * 7)
when 'MONTHS' then (Isnull(p.frequency,0) * 30)
when 'YEARS' then (Isnull(p.frequency,0) * 365)
else 0
end)*Isnull(s.interval,1) >= '28')
and (abs(datediff(dd,w.schedfinish,w.actfinish)) > @90daykpi))
or
(((Case p.frequnit
when 'DAYS' then Isnull(p.frequency,0)
when 'WEEKS' then (Isnull(p.frequency,0) * 7)
when 'MONTHS' then (Isnull(p.frequency,0) * 30)
when 'YEARS' then (Isnull(p.frequency,0) * 365)
else 0
end)*Isnull(s.interval,1) > '90')
and (abs(datediff(dd,w.schedfinish,w.actfinish)) > @over90daykpi)))
THEN wonum ELSE null END) AS COMP_EARLY_LATE,
count(CASE WHEN (w.status = 'CLOSE' or w.status = 'COMP' or w.status =
'COMPCLOSE')
and actfinish is null
THEN wonum ELSE null END) AS NOACTFINISH,
count(wonum) AS TOTAL
FROM workorder w
left outer join pm p on (p.pmnum = w.pmnum and p.orgid = w.orgid and
p.siteid = w.siteid
and (Case p.frequnit
when 'DAYS' then Isnull(p.frequency,0)
when 'WEEKS' then (Isnull(p.frequency,0) * 7)
when 'MONTHS' then (Isnull(p.frequency,0) * 30)
when 'YEARS' then (Isnull(p.frequency,0) * 365)
else 0
end) >= '28')
join financialperiods f on (w.schedfinish >= f.periodstart and w.schedfinish
< f.periodend and w.orgid = f.orgid)
left outer join pmsequence s on (s.jpnum = w.jpnum and s.pmnum = w.pmnum and
s.orgid = f.orgid and s.siteid = w.siteid)
WHERE (istask = 0 and w.pmnum is not null) and (woclass = 'WORKORDER' or
woclass = 'ACTIVITY')
and (w.worktype = 'PM' or w.worktype = 'PMYR' or w.worktype = 'REG')
and ((Case p.frequnit
when 'DAYS' then Isnull(p.frequency,0)
when 'WEEKS' then (Isnull(p.frequency,0) * 7)
when 'MONTHS' then (Isnull(p.frequency,0) * 30)
when 'YEARS' then (Isnull(p.frequency,0) * 365)
else 0
end)*Isnull(s.interval,1) >= '28')
and (left(financialperiod,4) >= (@fiscalyr - 5))
and w.wonum not in (select wonum from wostatus where status = 'DELSCHED')
GROUP BY financialperiod, periodstart, periodend, w.siteid, w.orgid
order by financialperiod
/*Stored Prodecure Code End*/
/*Table Create Code Begins*/
USE [maxprod]
GO
/****** Object: Table [dbo].[OSC_KPI_MSP] Script Date: 12/09/2011 08:46:34
******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[OSC_KPI_MSP](
[poolyear] AS (left([poolperiod],(4))) PERSISTED,
[poolperiod] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[periodstart] [smalldatetime] NULL,
[periodend] [smalldatetime] NULL,
[siteid] [nchar](24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[orgid] [nchar](24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[comp] [decimal](5, 0) NULL,
[can] [decimal](5, 0) NULL,
[open] [decimal](5, 0) NULL,
[open_late] [decimal](5, 0) NULL,
[comp_ot] [decimal](5, 0) NULL,
[comp_early_late] [decimal](5, 0) NULL,
[noactfinish] [decimal](5, 0) NULL,
[total] [decimal](5, 0) NULL,
[comp_ot_cal] AS ([comp_ot]/(([comp]+[open_late])+[can])) PERSISTED
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
/*Table Create Code Ends*/
/*Report Query Code Begins*/
maximoDataSet =
MXReportDataSetProvider.create(this.getDataSource().getName(),
this.getName());
maximoDataSet.open();
var temppoolyearString = new String();
var tempsiteidString = new String();
if(params["poolyear"].value)
{
temppoolyearString =
reportContext.getParameterValue("poolyear").toUpperCase();
}
else
{
temppoolyearString = "%";
}
if(params["siteid"].value)
{
tempsiteidString = reportContext.getParameterValue("siteid").toUpperCase();
}
else
{
tempsiteidString = "%";
}
var sqlText = new String();
// Add query to sqlText variable.
sqlText = "select poolyear, poolperiod, periodstart, periodend, siteid,
orgid, comp, can, [open], open_late, "
+ " comp_ot, comp_early_late, noactfinish, total, comp_ot_cal, getdate() as
'currentdate' "
+ " from osc_kpi_pm "
+ " where " + params["where"]
+ " and siteid like '%" + tempsiteidString + "%' "
+ " and poolyear like '" + temppoolyearString + "' "
+ " order by siteid, poolperiod"
;
maximoDataSet.setQuery(sqlText);
/*Report Query Code Ends*/
--- In MAXIMO@yahoogroups.com <mailto:MAXIMO%40yahoogroups.com>
<mailto:MAXIMO%40yahoogroups.com>, Chad Stringer <cstringer@...
<mailto:cstringer@...%3cmailto:cstringer@...> <mailto:cstringer@...>> wrote:
>
> You might try getting a hold of an SMRP (Society of Maintenance and
Reliability Professionals) handbook. They have a lot of great metrics that
are industry standards to look into. That is a great place to start, and
where I pull most of my ideas for KPIs.
> The SQL code is a whole different matter. Many companies use custom fields
or different parameters based on how they measure things. I'm sure there are
some really good base queries available to use though. Best of luck, and
keep us all in the loop on your progress.
>
> CJ Stringer
>
> From: MAXIMO@yahoogroups.com <mailto:MAXIMO%40yahoogroups.com>
<mailto:MAXIMO%40yahoogroups.com> [mailto:MAXIMO@yahoogroups.com
<mailto:MAXIMO%40yahoogroups.com> <mailto:MAXIMO%40yahoogroups.com>] On
Behalf Of john_gould14
> Sent: Friday, December 09, 2011 8:28 AM
> To: MAXIMO@yahoogroups.com <mailto:MAXIMO%40yahoogroups.com>
<mailto:MAXIMO%40yahoogroups.com>
> Subject: [MAXIMO List] KPI Roundup
>
>
>
> Hello All - in attempting to further develop our start center KPI's to get
better visualization of our orgnaizations performance, I'm always wondering
what other KPI's people are using. I have the standard, KPI's like
percetnage of work type, average age of work orders, # of work orders
reported versus completed, inprogress, open emergencies, PM's overdue -
things like that.
>
> If you have some more 'interesting' ones to share I'd love to hear about
them. I'm a SQL novice so if you have the SQL code - even better. I'm
running version 7.5.0 with Oracle DB.
>
>
>
>
>
________________________________
----Notice Regarding Confidentiality----
This email, including any and all attachments, (this "Email") is intended
only for the party to whom it is addressed and may contain information that
is confidential or privileged. Sierra Systems Group Inc. and its affiliates
accept no responsibility for any loss or damage suffered by any person
resulting from any unauthorized use of or reliance upon this Email. If you
are not the intended recipient, you are hereby notified that any
dissemination, copying or other use of this Email is prohibited. Please
notify us of the error in communication by return email and destroy all
copies of this Email. Thank you.
Hi,
What Shannon said times 2! I've been using Maximo since 1994 and we've always had issues that can be fixed in no other way. It's always the last thing I try. As always you test the heck out of anything before you put it in production and test again for any upgrades. So you want to keep this to a minimum.
I doubt that IBM will ever eliminate the use of stored procedures and other direct database update tools. In this case the stored procedure does not update any out of the box Maximo tables. It updates a table that is only used for reporting.
I have another stored procedure that updates a table to forecast PM work orders for the next three years. Of course a PM forecast is totally missing in Maximo unless you want to pay IBM or someone else to do this.
Shannon's issue is just one more case in point.
IBM should code the application to trap characters that cannot be handled.
Failing that the Maximo escalations should provide a way to run SQL code to update issues that cannot be otherwise accomplished. At least then you would have everything in one application.
For instance in 7.1.1.6 you cannot return a service. So how does IBM expect us to return a service other than updating the tables directly? Maybe they should hire a business analyst that actually knows something about maintenance so they don't do something stupid like this?
Dave Bone
Ocean Spray Cranberries
--- In MAXIMO@yahoogroups.com, "Shannon Rotz" <shannonrotz@...> wrote:
>
> We (at least those of us who have been around a while) are all aware of
> that, and I have to say that IBM has been making the need for direct
> database coding less and less necessary. But there are still times when a
> Stored Proc does the job better than anything that can be done using the GUI
> tools, especially since the Stored Procs are pre-compiled on the database
> side for increased speed.
>
>
>
> Even though I'm a dyed-in-the-wool "use the GUI to do everything" kind of
> person, I actually considered using a trigger for something the other day .
> it had to do with a problem with special characters in the Long Description.
> Users were copying the bodies of e-mails (containing vendor quotes) into the
> Long Description on PR Lines, and then saving the record. That part worked
> fine - but when the record needed to be processed further down the line
> (i.e. the Invoice stage), it resulted in a Java error and the inability to
> save the invoice until the offending characters were removed. For a while,
> the IT tech on the scene was editing the Long Descriptions using a text
> tool, which needless to say, was not time-effective. So eventually they
> stopped doing the copying altogether, which is a shame. I got the idea of
> using a trigger to intercept the text before it went into the
> LONGDESCRIPTION table and replace the problem characters to more useful
> ones.
>
>
>
> So on rare occasions, I still think database-side code is useful - but only
> at the discretion of someone who has a lot of experience and knowledge of
> the product, and can make a VERY informed judgement.
>
>
>
> I do agree with you, though, that people who have knowledge of Java and/or
> Database-side code (TRANSACT-SQL or PL/SQL), but are new to Maximo, are
> frequently too ready to use code to solve a problem - only to inadvertently
> "gum things up" and cause more problems down the line.
>
>
>
>
>
> Shannon
>
>
>
> P.S. If anyone has a solution to the Long Description problem, using the
> GUI tools or even Java, speak up - I'd like to hear it. (Although for all I
> know, it may be fixed in 7.5 . I'll check it out). I haven't actually
> implemented my trigger idea, and I'd rather not, if possible.
>
>
>
> From: MAXIMO@yahoogroups.com [mailto:MAXIMO@yahoogroups.com] On Behalf Of
> Hall, Michael
> Sent: December-09-11 10:50 AM
> To: MAXIMO@yahoogroups.com
> Subject: RE: [MAXIMO List] KPI Roundup
>
>
>
>
>
> IBM highly discourages the use of stored procedures in the Maximo database.
>
> Michael
>
> From: MAXIMO@yahoogroups.com <mailto:MAXIMO%40yahoogroups.com>
> [mailto:MAXIMO@yahoogroups.com <mailto:MAXIMO%40yahoogroups.com> ] On Behalf
> Of in2data
> Sent: Friday, December 09, 2011 9:59 AM
> To: MAXIMO@yahoogroups.com <mailto:MAXIMO%40yahoogroups.com>
> Subject: Re: [MAXIMO List] KPI Roundup
>
> Hi,
>
> We have one to measure the On-Time PM rate. PMs are on-time only if they are
> completed within the allowed window. For instance 90 day or less PMs have
> =/- 7 days of the scheduled start date to be completed. The system copies
> the target start date from the PM work order to the scheduled start date
> just after the work order is generated. The stored procedure also does this
> if the scheduled start date is still null. The site maintenance planner can
> then move the scheduled start date to align with available downtime.
>
> The way it's calculated is a stored procedure is run on the database each
> night that updates a custom table OSC_KPI_PM. You can and should create the
> custom table in Maximo but the stored procedure has to be run from the
> database. The code is below and it's for SQL Server. Your DB admins can
> convert to Oracle.
>
> I also included the code to create the table in the DB. You can use this for
> the table attributes in Maximo.
>
> Then you create a report using the query at the end. again that's for SQL
> and will need some editing for Oracle. I've added some conditional colors in
> the report to show where on-time rates are out of the KPI metric limit.
>
> Why did I use a stored procedure? It takes a long time to calculate the KPIs
> and having a table already populated with the results makes the report run
> much faster. The users like to run this on demand.
>
> In the code where you run across poolyear and other values like that those
> are internal calendar periods we use. OSC is an agricultural co-operative
> and we measure everything by the pool year calendar. Those periods do not
> align with the normal Jan-Dec calendar. Our pool year starts the end of Feb
> and has varying pool period lengths from 25 to 32 days. Our financial
> periods in Maximo are set to those pool periods.
>
> OK. That's a lot of fire hose time. I'll turn it off now.
>
> Enjoy the briar patch!
>
> Dave Bone
> Ocean Spray Cranberries
>
> /*Stored Prodecure Code Begins*/
>
> USE [maxprod]
> GO
> /****** Object: StoredProcedure [dbo].[OSC_KPI_PM_Update] Script Date:
> 12/09/2011 08:35:43 ******/
> SET ANSI_NULLS ON
> GO
> SET QUOTED_IDENTIFIER ON
> GO
>
> --Drop PROCEDURE OSC_KPI_PM_Update
> -- exec OSC_KPI_PM_Update
> -- select * from OSC_KPI_PM
> CREATE PROCEDURE [dbo].[OSC_KPI_PM_Update]
> AS
> update workorder set schedfinish = targcompdate
> where ((schedfinish is null) and (targcompdate is not null)
> and (worktype = 'PM' or worktype = 'PMYR' or worktype = 'REG'))
> Delete from OSC_KPI_PM
> Declare
> @90daykpi as varchar(8),
> @over90daykpi as varchar(8),
> @fiscalyr as varchar(8)
> set @90daykpi = '7'
> set @over90daykpi = '15'
> set @fiscalyr = (Select LEFT(f.financialperiod, 4) FROM financialperiods f
> Where orgid = 'OSC' and (f.periodstart <= getdate()) AND (f.periodend >
> getdate()))
> Insert into OSC_KPI_PM
> (poolperiod,periodstart,periodend,siteid,orgid,comp,can,[open],open_late,com
> p_ot,comp_early_late,noactfinish,total)
> SELECT financialperiod as poolperiod, periodstart, periodend, w.siteid,
> w.orgid,
> Case When (count(CASE WHEN (w.status = 'CLOSE' or w.status = 'COMP' or
> w.status = 'COMPCLOSE') THEN wonum ELSE null END)) = 0 THEN 1 ELSE
> (count(CASE WHEN (w.status = 'CLOSE' or w.status = 'COMP' or w.status =
> 'COMPCLOSE') THEN wonum ELSE null END)) END AS COMP,
> --this is the old comp the one above keeps from having a divide by 0 error
> when no pm is comp,can or open_late count(CASE WHEN (w.status = 'CLOSE' or
> w.status = 'COMP') THEN wonum ELSE null END) AS COMP,
> count(CASE WHEN (w.status = 'CAN' or w.status = 'CANHOL' or w.status =
> 'CANOPS') THEN wonum ELSE null END) AS CAN,
> count(CASE WHEN (w.status <> 'CLOSE' and w.status <> 'COMP' and w.status <>
> 'COMPCLOSE' and w.status <> 'CAN' and w.status <> 'CANHOL' and w.status <>
> 'CANOPS')
> THEN wonum ELSE null END) AS 'OPEN',
>
> count(CASE WHEN (w.status <> 'CLOSE' and w.status <> 'COMP' and w.status <>
> 'COMPCLOSE' and w.status <> 'CAN' and w.status <> 'CANHOL' and w.status <>
> 'CANOPS')
> and
> (((w.schedfinish + 7 <= getdate())
> and ((Case p.frequnit
> when 'DAYS' then Isnull(p.frequency,0)
> when 'WEEKS' then (Isnull(p.frequency,0) * 7)
> when 'MONTHS' then (Isnull(p.frequency,0) * 30)
> when 'YEARS' then (Isnull(p.frequency,0) * 365)
> else 0
> end)*Isnull(s.interval,1) <= '90')
> and ((Case p.frequnit
> when 'DAYS' then Isnull(p.frequency,0)
> when 'WEEKS' then (Isnull(p.frequency,0) * 7)
> when 'MONTHS' then (Isnull(p.frequency,0) * 30)
> when 'YEARS' then (Isnull(p.frequency,0) * 365)
> else 0
> end)*Isnull(s.interval,1) >= '28')
> and (abs(datediff(dd,w.schedfinish,getdate())) > @90daykpi))
> or
> ((w.schedfinish + 15 <= getdate())
> and ((Case p.frequnit
> when 'DAYS' then Isnull(p.frequency,0)
> when 'WEEKS' then (Isnull(p.frequency,0) * 7)
> when 'MONTHS' then (Isnull(p.frequency,0) * 30)
> when 'YEARS' then (Isnull(p.frequency,0) * 365)
> else 0
> end)*Isnull(s.interval,1) > '90')
> and (abs(datediff(dd,w.schedfinish,getdate())) > @over90daykpi)))
> THEN wonum ELSE null END) AS 'OPEN_LATE',
>
> count(CASE WHEN (w.status = 'CLOSE' or w.status = 'COMP' or w.status =
> 'COMPCLOSE')
> and
> ((((Case p.frequnit
> when 'DAYS' then Isnull(p.frequency,0)
> when 'WEEKS' then (Isnull(p.frequency,0) * 7)
> when 'MONTHS' then (Isnull(p.frequency,0) * 30)
> when 'YEARS' then (Isnull(p.frequency,0) * 365)
> else 0
> end)*Isnull(s.interval,1) <= '90')
> and ((Case p.frequnit
> when 'DAYS' then Isnull(p.frequency,0)
> when 'WEEKS' then (Isnull(p.frequency,0) * 7)
> when 'MONTHS' then (Isnull(p.frequency,0) * 30)
> when 'YEARS' then (Isnull(p.frequency,0) * 365)
> else 0
> end)*Isnull(s.interval,1) >= '28')
> and (abs(datediff(dd,w.schedfinish,w.actfinish)) <= @90daykpi))
> or
> (((Case p.frequnit
> when 'DAYS' then Isnull(p.frequency,0)
> when 'WEEKS' then (Isnull(p.frequency,0) * 7)
> when 'MONTHS' then (Isnull(p.frequency,0) * 30)
> when 'YEARS' then (Isnull(p.frequency,0) * 365)
> else 0
> end)*Isnull(s.interval,1) > '90')
> and (abs(datediff(dd,w.schedfinish,w.actfinish)) <= @over90daykpi)))
> THEN wonum ELSE null END) AS COMP_OT,
>
> count(CASE WHEN (w.status = 'CLOSE' or w.status = 'COMP' or w.status =
> 'COMPCLOSE')
> and
> ((((Case p.frequnit
> when 'DAYS' then Isnull(p.frequency,0)
> when 'WEEKS' then (Isnull(p.frequency,0) * 7)
> when 'MONTHS' then (Isnull(p.frequency,0) * 30)
> when 'YEARS' then (Isnull(p.frequency,0) * 365)
> else 0
> end)*Isnull(s.interval,1) <= '90')
> and ((Case p.frequnit
> when 'DAYS' then Isnull(p.frequency,0)
> when 'WEEKS' then (Isnull(p.frequency,0) * 7)
> when 'MONTHS' then (Isnull(p.frequency,0) * 30)
> when 'YEARS' then (Isnull(p.frequency,0) * 365)
> else 0
> end)*Isnull(s.interval,1) >= '28')
> and (abs(datediff(dd,w.schedfinish,w.actfinish)) > @90daykpi))
> or
> (((Case p.frequnit
> when 'DAYS' then Isnull(p.frequency,0)
> when 'WEEKS' then (Isnull(p.frequency,0) * 7)
> when 'MONTHS' then (Isnull(p.frequency,0) * 30)
> when 'YEARS' then (Isnull(p.frequency,0) * 365)
> else 0
> end)*Isnull(s.interval,1) > '90')
> and (abs(datediff(dd,w.schedfinish,w.actfinish)) > @over90daykpi)))
> THEN wonum ELSE null END) AS COMP_EARLY_LATE,
>
> count(CASE WHEN (w.status = 'CLOSE' or w.status = 'COMP' or w.status =
> 'COMPCLOSE')
> and actfinish is null
> THEN wonum ELSE null END) AS NOACTFINISH,
>
> count(wonum) AS TOTAL
>
> FROM workorder w
> left outer join pm p on (p.pmnum = w.pmnum and p.orgid = w.orgid and
> p.siteid = w.siteid
> and (Case p.frequnit
> when 'DAYS' then Isnull(p.frequency,0)
> when 'WEEKS' then (Isnull(p.frequency,0) * 7)
> when 'MONTHS' then (Isnull(p.frequency,0) * 30)
> when 'YEARS' then (Isnull(p.frequency,0) * 365)
> else 0
> end) >= '28')
> join financialperiods f on (w.schedfinish >= f.periodstart and w.schedfinish
> < f.periodend and w.orgid = f.orgid)
> left outer join pmsequence s on (s.jpnum = w.jpnum and s.pmnum = w.pmnum and
> s.orgid = f.orgid and s.siteid = w.siteid)
> WHERE (istask = 0 and w.pmnum is not null) and (woclass = 'WORKORDER' or
> woclass = 'ACTIVITY')
> and (w.worktype = 'PM' or w.worktype = 'PMYR' or w.worktype = 'REG')
> and ((Case p.frequnit
> when 'DAYS' then Isnull(p.frequency,0)
> when 'WEEKS' then (Isnull(p.frequency,0) * 7)
> when 'MONTHS' then (Isnull(p.frequency,0) * 30)
> when 'YEARS' then (Isnull(p.frequency,0) * 365)
> else 0
> end)*Isnull(s.interval,1) >= '28')
> and (left(financialperiod,4) >= (@fiscalyr - 5))
> and w.wonum not in (select wonum from wostatus where status = 'DELSCHED')
> GROUP BY financialperiod, periodstart, periodend, w.siteid, w.orgid
> order by financialperiod
>
> /*Stored Prodecure Code End*/
>
> /*Table Create Code Begins*/
>
> USE [maxprod]
> GO
> /****** Object: Table [dbo].[OSC_KPI_MSP] Script Date: 12/09/2011 08:46:34
> ******/
> SET ANSI_NULLS ON
> GO
> SET QUOTED_IDENTIFIER ON
> GO
> SET ANSI_PADDING ON
> GO
> CREATE TABLE [dbo].[OSC_KPI_MSP](
> [poolyear] AS (left([poolperiod],(4))) PERSISTED,
> [poolperiod] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> [periodstart] [smalldatetime] NULL,
> [periodend] [smalldatetime] NULL,
> [siteid] [nchar](24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> [orgid] [nchar](24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> [comp] [decimal](5, 0) NULL,
> [can] [decimal](5, 0) NULL,
> [open] [decimal](5, 0) NULL,
> [open_late] [decimal](5, 0) NULL,
> [comp_ot] [decimal](5, 0) NULL,
> [comp_early_late] [decimal](5, 0) NULL,
> [noactfinish] [decimal](5, 0) NULL,
> [total] [decimal](5, 0) NULL,
> [comp_ot_cal] AS ([comp_ot]/(([comp]+[open_late])+[can])) PERSISTED
> ) ON [PRIMARY]
>
> GO
> SET ANSI_PADDING OFF
>
> /*Table Create Code Ends*/
>
> /*Report Query Code Begins*/
>
> maximoDataSet =
> MXReportDataSetProvider.create(this.getDataSource().getName(),
> this.getName());
> maximoDataSet.open();
>
> var temppoolyearString = new String();
> var tempsiteidString = new String();
>
> if(params["poolyear"].value)
> {
> temppoolyearString =
> reportContext.getParameterValue("poolyear").toUpperCase();
> }
> else
> {
> temppoolyearString = "%";
> }
>
> if(params["siteid"].value)
> {
> tempsiteidString = reportContext.getParameterValue("siteid").toUpperCase();
> }
> else
> {
> tempsiteidString = "%";
> }
>
> var sqlText = new String();
>
> // Add query to sqlText variable.
> sqlText = "select poolyear, poolperiod, periodstart, periodend, siteid,
> orgid, comp, can, [open], open_late, "
> + " comp_ot, comp_early_late, noactfinish, total, comp_ot_cal, getdate() as
> 'currentdate' "
> + " from osc_kpi_pm "
> + " where " + params["where"]
> + " and siteid like '%" + tempsiteidString + "%' "
> + " and poolyear like '" + temppoolyearString + "' "
> + " order by siteid, poolperiod"
> ;
>
> maximoDataSet.setQuery(sqlText);
>
> /*Report Query Code Ends*/
>
> --- In MAXIMO@yahoogroups.com <mailto:MAXIMO%40yahoogroups.com>
> <mailto:MAXIMO%40yahoogroups.com>, Chad Stringer <cstringer@
> <mailto:cstringer@%3cmailto:cstringer@> <mailto:cstringer@>> wrote:
> >
> > You might try getting a hold of an SMRP (Society of Maintenance and
> Reliability Professionals) handbook. They have a lot of great metrics that
> are industry standards to look into. That is a great place to start, and
> where I pull most of my ideas for KPIs.
> > The SQL code is a whole different matter. Many companies use custom fields
> or different parameters based on how they measure things. I'm sure there are
> some really good base queries available to use though. Best of luck, and
> keep us all in the loop on your progress.
> >
> > CJ Stringer
> >
> > From: MAXIMO@yahoogroups.com <mailto:MAXIMO%40yahoogroups.com>
> <mailto:MAXIMO%40yahoogroups.com> [mailto:MAXIMO@yahoogroups.com
> <mailto:MAXIMO%40yahoogroups.com> <mailto:MAXIMO%40yahoogroups.com>] On
> Behalf Of john_gould14
> > Sent: Friday, December 09, 2011 8:28 AM
> > To: MAXIMO@yahoogroups.com <mailto:MAXIMO%40yahoogroups.com>
> <mailto:MAXIMO%40yahoogroups.com>
> > Subject: [MAXIMO List] KPI Roundup
> >
> >
> >
> > Hello All - in attempting to further develop our start center KPI's to get
> better visualization of our orgnaizations performance, I'm always wondering
> what other KPI's people are using. I have the standard, KPI's like
> percetnage of work type, average age of work orders, # of work orders
> reported versus completed, inprogress, open emergencies, PM's overdue -
> things like that.
> >
> > If you have some more 'interesting' ones to share I'd love to hear about
> them. I'm a SQL novice so if you have the SQL code - even better. I'm
> running version 7.5.0 with Oracle DB.
> >
> >
> >
> >
> >
>
> ________________________________
> ----Notice Regarding Confidentiality----
>
> This email, including any and all attachments, (this "Email") is intended
> only for the party to whom it is addressed and may contain information that
> is confidential or privileged. Sierra Systems Group Inc. and its affiliates
> accept no responsibility for any loss or damage suffered by any person
> resulting from any unauthorized use of or reliance upon this Email. If you
> are not the intended recipient, you are hereby notified that any
> dissemination, copying or other use of this Email is prohibited. Please
> notify us of the error in communication by return email and destroy all
> copies of this Email. Thank you.
>
>
>
>
>
>
>
>
>
Hi Dave: regarding the services - does clicking on "New Row" and entering a
negative quantity not work? Also, FYI: V7.5 has a PM forecasting ability
(finally)!
Shannon
From: MAXIMO@yahoogroups.com [mailto:MAXIMO@yahoogroups.com] On Behalf Of
in2data
Sent: December-12-11 8:48 AM
To: MAXIMO@yahoogroups.com
Subject: Re: [MAXIMO List] KPI Roundup
Hi,
What Shannon said times 2! I've been using Maximo since 1994 and we've
always had issues that can be fixed in no other way. It's always the last
thing I try. As always you test the heck out of anything before you put it
in production and test again for any upgrades. So you want to keep this to a
minimum.
I doubt that IBM will ever eliminate the use of stored procedures and other
direct database update tools. In this case the stored procedure does not
update any out of the box Maximo tables. It updates a table that is only
used for reporting.
I have another stored procedure that updates a table to forecast PM work
orders for the next three years. Of course a PM forecast is totally missing
in Maximo unless you want to pay IBM or someone else to do this.
Shannon's issue is just one more case in point.
IBM should code the application to trap characters that cannot be handled.
Failing that the Maximo escalations should provide a way to run SQL code to
update issues that cannot be otherwise accomplished. At least then you would
have everything in one application.
For instance in 7.1.1.6 you cannot return a service. So how does IBM expect
us to return a service other than updating the tables directly? Maybe they
should hire a business analyst that actually knows something about
maintenance so they don't do something stupid like this?
Dave Bone
Ocean Spray Cranberries
--- In MAXIMO@yahoogroups.com <mailto:MAXIMO%40yahoogroups.com> , "Shannon
Rotz" <shannonrotz@...> wrote:
>
> We (at least those of us who have been around a while) are all aware of
> that, and I have to say that IBM has been making the need for direct
> database coding less and less necessary. But there are still times when a
> Stored Proc does the job better than anything that can be done using the
GUI
> tools, especially since the Stored Procs are pre-compiled on the database
> side for increased speed.
>
>
>
> Even though I'm a dyed-in-the-wool "use the GUI to do everything" kind of
> person, I actually considered using a trigger for something the other day
.
> it had to do with a problem with special characters in the Long
Description.
> Users were copying the bodies of e-mails (containing vendor quotes) into
the
> Long Description on PR Lines, and then saving the record. That part worked
> fine - but when the record needed to be processed further down the line
> (i.e. the Invoice stage), it resulted in a Java error and the inability to
> save the invoice until the offending characters were removed. For a while,
> the IT tech on the scene was editing the Long Descriptions using a text
> tool, which needless to say, was not time-effective. So eventually they
> stopped doing the copying altogether, which is a shame. I got the idea of
> using a trigger to intercept the text before it went into the
> LONGDESCRIPTION table and replace the problem characters to more useful
> ones.
>
>
>
> So on rare occasions, I still think database-side code is useful - but
only
> at the discretion of someone who has a lot of experience and knowledge of
> the product, and can make a VERY informed judgement.
>
>
>
> I do agree with you, though, that people who have knowledge of Java and/or
> Database-side code (TRANSACT-SQL or PL/SQL), but are new to Maximo, are
> frequently too ready to use code to solve a problem - only to
inadvertently
> "gum things up" and cause more problems down the line.
>
>
>
>
>
> Shannon
>
>
>
> P.S. If anyone has a solution to the Long Description problem, using the
> GUI tools or even Java, speak up - I'd like to hear it. (Although for all
I
> know, it may be fixed in 7.5 . I'll check it out). I haven't actually
> implemented my trigger idea, and I'd rather not, if possible.
>
>
>
> From: MAXIMO@yahoogroups.com <mailto:MAXIMO%40yahoogroups.com>
[mailto:MAXIMO@yahoogroups.com <mailto:MAXIMO%40yahoogroups.com> ] On Behalf
Of
> Hall, Michael
> Sent: December-09-11 10:50 AM
> To: MAXIMO@yahoogroups.com <mailto:MAXIMO%40yahoogroups.com>
> Subject: RE: [MAXIMO List] KPI Roundup
>
>
>
>
>
> IBM highly discourages the use of stored procedures in the Maximo
database.
>
> Michael
>
> From: MAXIMO@yahoogroups.com <mailto:MAXIMO%40yahoogroups.com>
<mailto:MAXIMO%40yahoogroups.com>
> [mailto:MAXIMO@yahoogroups.com <mailto:MAXIMO%40yahoogroups.com>
<mailto:MAXIMO%40yahoogroups.com> ] On Behalf
> Of in2data
> Sent: Friday, December 09, 2011 9:59 AM
> To: MAXIMO@yahoogroups.com <mailto:MAXIMO%40yahoogroups.com>
<mailto:MAXIMO%40yahoogroups.com>
> Subject: Re: [MAXIMO List] KPI Roundup
>
> Hi,
>
> We have one to measure the On-Time PM rate. PMs are on-time only if they
are
> completed within the allowed window. For instance 90 day or less PMs have
> =/- 7 days of the scheduled start date to be completed. The system copies
> the target start date from the PM work order to the scheduled start date
> just after the work order is generated. The stored procedure also does
this
> if the scheduled start date is still null. The site maintenance planner
can
> then move the scheduled start date to align with available downtime.
>
> The way it's calculated is a stored procedure is run on the database each
> night that updates a custom table OSC_KPI_PM. You can and should create
the
> custom table in Maximo but the stored procedure has to be run from the
> database. The code is below and it's for SQL Server. Your DB admins can
> convert to Oracle.
>
> I also included the code to create the table in the DB. You can use this
for
> the table attributes in Maximo.
>
> Then you create a report using the query at the end. again that's for SQL
> and will need some editing for Oracle. I've added some conditional colors
in
> the report to show where on-time rates are out of the KPI metric limit.
>
> Why did I use a stored procedure? It takes a long time to calculate the
KPIs
> and having a table already populated with the results makes the report run
> much faster. The users like to run this on demand.
>
> In the code where you run across poolyear and other values like that those
> are internal calendar periods we use. OSC is an agricultural co-operative
> and we measure everything by the pool year calendar. Those periods do not
> align with the normal Jan-Dec calendar. Our pool year starts the end of
Feb
> and has varying pool period lengths from 25 to 32 days. Our financial
> periods in Maximo are set to those pool periods.
>
> OK. That's a lot of fire hose time. I'll turn it off now.
>
> Enjoy the briar patch!
>
> Dave Bone
> Ocean Spray Cranberries
>
> /*Stored Prodecure Code Begins*/
>
> USE [maxprod]
> GO
> /****** Object: StoredProcedure [dbo].[OSC_KPI_PM_Update] Script Date:
> 12/09/2011 08:35:43 ******/
> SET ANSI_NULLS ON
> GO
> SET QUOTED_IDENTIFIER ON
> GO
>
> --Drop PROCEDURE OSC_KPI_PM_Update
> -- exec OSC_KPI_PM_Update
> -- select * from OSC_KPI_PM
> CREATE PROCEDURE [dbo].[OSC_KPI_PM_Update]
> AS
> update workorder set schedfinish = targcompdate
> where ((schedfinish is null) and (targcompdate is not null)
> and (worktype = 'PM' or worktype = 'PMYR' or worktype = 'REG'))
> Delete from OSC_KPI_PM
> Declare
> @90daykpi as varchar(8),
> @over90daykpi as varchar(8),
> @fiscalyr as varchar(8)
> set @90daykpi = '7'
> set @over90daykpi = '15'
> set @fiscalyr = (Select LEFT(f.financialperiod, 4) FROM financialperiods f
> Where orgid = 'OSC' and (f.periodstart <= getdate()) AND (f.periodend >
> getdate()))
> Insert into OSC_KPI_PM
>
(poolperiod,periodstart,periodend,siteid,orgid,comp,can,[open],open_late,com
> p_ot,comp_early_late,noactfinish,total)
> SELECT financialperiod as poolperiod, periodstart, periodend, w.siteid,
> w.orgid,
> Case When (count(CASE WHEN (w.status = 'CLOSE' or w.status = 'COMP' or
> w.status = 'COMPCLOSE') THEN wonum ELSE null END)) = 0 THEN 1 ELSE
> (count(CASE WHEN (w.status = 'CLOSE' or w.status = 'COMP' or w.status =
> 'COMPCLOSE') THEN wonum ELSE null END)) END AS COMP,
> --this is the old comp the one above keeps from having a divide by 0 error
> when no pm is comp,can or open_late count(CASE WHEN (w.status = 'CLOSE' or
> w.status = 'COMP') THEN wonum ELSE null END) AS COMP,
> count(CASE WHEN (w.status = 'CAN' or w.status = 'CANHOL' or w.status =
> 'CANOPS') THEN wonum ELSE null END) AS CAN,
> count(CASE WHEN (w.status <> 'CLOSE' and w.status <> 'COMP' and w.status
<>
> 'COMPCLOSE' and w.status <> 'CAN' and w.status <> 'CANHOL' and w.status <>
> 'CANOPS')
> THEN wonum ELSE null END) AS 'OPEN',
>
> count(CASE WHEN (w.status <> 'CLOSE' and w.status <> 'COMP' and w.status
<>
> 'COMPCLOSE' and w.status <> 'CAN' and w.status <> 'CANHOL' and w.status <>
> 'CANOPS')
> and
> (((w.schedfinish + 7 <= getdate())
> and ((Case p.frequnit
> when 'DAYS' then Isnull(p.frequency,0)
> when 'WEEKS' then (Isnull(p.frequency,0) * 7)
> when 'MONTHS' then (Isnull(p.frequency,0) * 30)
> when 'YEARS' then (Isnull(p.frequency,0) * 365)
> else 0
> end)*Isnull(s.interval,1) <= '90')
> and ((Case p.frequnit
> when 'DAYS' then Isnull(p.frequency,0)
> when 'WEEKS' then (Isnull(p.frequency,0) * 7)
> when 'MONTHS' then (Isnull(p.frequency,0) * 30)
> when 'YEARS' then (Isnull(p.frequency,0) * 365)
> else 0
> end)*Isnull(s.interval,1) >= '28')
> and (abs(datediff(dd,w.schedfinish,getdate())) > @90daykpi))
> or
> ((w.schedfinish + 15 <= getdate())
> and ((Case p.frequnit
> when 'DAYS' then Isnull(p.frequency,0)
> when 'WEEKS' then (Isnull(p.frequency,0) * 7)
> when 'MONTHS' then (Isnull(p.frequency,0) * 30)
> when 'YEARS' then (Isnull(p.frequency,0) * 365)
> else 0
> end)*Isnull(s.interval,1) > '90')
> and (abs(datediff(dd,w.schedfinish,getdate())) > @over90daykpi)))
> THEN wonum ELSE null END) AS 'OPEN_LATE',
>
> count(CASE WHEN (w.status = 'CLOSE' or w.status = 'COMP' or w.status =
> 'COMPCLOSE')
> and
> ((((Case p.frequnit
> when 'DAYS' then Isnull(p.frequency,0)
> when 'WEEKS' then (Isnull(p.frequency,0) * 7)
> when 'MONTHS' then (Isnull(p.frequency,0) * 30)
> when 'YEARS' then (Isnull(p.frequency,0) * 365)
> else 0
> end)*Isnull(s.interval,1) <= '90')
> and ((Case p.frequnit
> when 'DAYS' then Isnull(p.frequency,0)
> when 'WEEKS' then (Isnull(p.frequency,0) * 7)
> when 'MONTHS' then (Isnull(p.frequency,0) * 30)
> when 'YEARS' then (Isnull(p.frequency,0) * 365)
> else 0
> end)*Isnull(s.interval,1) >= '28')
> and (abs(datediff(dd,w.schedfinish,w.actfinish)) <= @90daykpi))
> or
> (((Case p.frequnit
> when 'DAYS' then Isnull(p.frequency,0)
> when 'WEEKS' then (Isnull(p.frequency,0) * 7)
> when 'MONTHS' then (Isnull(p.frequency,0) * 30)
> when 'YEARS' then (Isnull(p.frequency,0) * 365)
> else 0
> end)*Isnull(s.interval,1) > '90')
> and (abs(datediff(dd,w.schedfinish,w.actfinish)) <= @over90daykpi)))
> THEN wonum ELSE null END) AS COMP_OT,
>
> count(CASE WHEN (w.status = 'CLOSE' or w.status = 'COMP' or w.status =
> 'COMPCLOSE')
> and
> ((((Case p.frequnit
> when 'DAYS' then Isnull(p.frequency,0)
> when 'WEEKS' then (Isnull(p.frequency,0) * 7)
> when 'MONTHS' then (Isnull(p.frequency,0) * 30)
> when 'YEARS' then (Isnull(p.frequency,0) * 365)
> else 0
> end)*Isnull(s.interval,1) <= '90')
> and ((Case p.frequnit
> when 'DAYS' then Isnull(p.frequency,0)
> when 'WEEKS' then (Isnull(p.frequency,0) * 7)
> when 'MONTHS' then (Isnull(p.frequency,0) * 30)
> when 'YEARS' then (Isnull(p.frequency,0) * 365)
> else 0
> end)*Isnull(s.interval,1) >= '28')
> and (abs(datediff(dd,w.schedfinish,w.actfinish)) > @90daykpi))
> or
> (((Case p.frequnit
> when 'DAYS' then Isnull(p.frequency,0)
> when 'WEEKS' then (Isnull(p.frequency,0) * 7)
> when 'MONTHS' then (Isnull(p.frequency,0) * 30)
> when 'YEARS' then (Isnull(p.frequency,0) * 365)
> else 0
> end)*Isnull(s.interval,1) > '90')
> and (abs(datediff(dd,w.schedfinish,w.actfinish)) > @over90daykpi)))
> THEN wonum ELSE null END) AS COMP_EARLY_LATE,
>
> count(CASE WHEN (w.status = 'CLOSE' or w.status = 'COMP' or w.status =
> 'COMPCLOSE')
> and actfinish is null
> THEN wonum ELSE null END) AS NOACTFINISH,
>
> count(wonum) AS TOTAL
>
> FROM workorder w
> left outer join pm p on (p.pmnum = w.pmnum and p.orgid = w.orgid and
> p.siteid = w.siteid
> and (Case p.frequnit
> when 'DAYS' then Isnull(p.frequency,0)
> when 'WEEKS' then (Isnull(p.frequency,0) * 7)
> when 'MONTHS' then (Isnull(p.frequency,0) * 30)
> when 'YEARS' then (Isnull(p.frequency,0) * 365)
> else 0
> end) >= '28')
> join financialperiods f on (w.schedfinish >= f.periodstart and
w.schedfinish
> < f.periodend and w.orgid = f.orgid)
> left outer join pmsequence s on (s.jpnum = w.jpnum and s.pmnum = w.pmnum
and
> s.orgid = f.orgid and s.siteid = w.siteid)
> WHERE (istask = 0 and w.pmnum is not null) and (woclass = 'WORKORDER' or
> woclass = 'ACTIVITY')
> and (w.worktype = 'PM' or w.worktype = 'PMYR' or w.worktype = 'REG')
> and ((Case p.frequnit
> when 'DAYS' then Isnull(p.frequency,0)
> when 'WEEKS' then (Isnull(p.frequency,0) * 7)
> when 'MONTHS' then (Isnull(p.frequency,0) * 30)
> when 'YEARS' then (Isnull(p.frequency,0) * 365)
> else 0
> end)*Isnull(s.interval,1) >= '28')
> and (left(financialperiod,4) >= (@fiscalyr - 5))
> and w.wonum not in (select wonum from wostatus where status = 'DELSCHED')
> GROUP BY financialperiod, periodstart, periodend, w.siteid, w.orgid
> order by financialperiod
>
> /*Stored Prodecure Code End*/
>
> /*Table Create Code Begins*/
>
> USE [maxprod]
> GO
> /****** Object: Table [dbo].[OSC_KPI_MSP] Script Date: 12/09/2011 08:46:34
> ******/
> SET ANSI_NULLS ON
> GO
> SET QUOTED_IDENTIFIER ON
> GO
> SET ANSI_PADDING ON
> GO
> CREATE TABLE [dbo].[OSC_KPI_MSP](
> [poolyear] AS (left([poolperiod],(4))) PERSISTED,
> [poolperiod] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> [periodstart] [smalldatetime] NULL,
> [periodend] [smalldatetime] NULL,
> [siteid] [nchar](24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> [orgid] [nchar](24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> [comp] [decimal](5, 0) NULL,
> [can] [decimal](5, 0) NULL,
> [open] [decimal](5, 0) NULL,
> [open_late] [decimal](5, 0) NULL,
> [comp_ot] [decimal](5, 0) NULL,
> [comp_early_late] [decimal](5, 0) NULL,
> [noactfinish] [decimal](5, 0) NULL,
> [total] [decimal](5, 0) NULL,
> [comp_ot_cal] AS ([comp_ot]/(([comp]+[open_late])+[can])) PERSISTED
> ) ON [PRIMARY]
>
> GO
> SET ANSI_PADDING OFF
>
> /*Table Create Code Ends*/
>
> /*Report Query Code Begins*/
>
> maximoDataSet =
> MXReportDataSetProvider.create(this.getDataSource().getName(),
> this.getName());
> maximoDataSet.open();
>
> var temppoolyearString = new String();
> var tempsiteidString = new String();
>
> if(params["poolyear"].value)
> {
> temppoolyearString =
> reportContext.getParameterValue("poolyear").toUpperCase();
> }
> else
> {
> temppoolyearString = "%";
> }
>
> if(params["siteid"].value)
> {
> tempsiteidString =
reportContext.getParameterValue("siteid").toUpperCase();
> }
> else
> {
> tempsiteidString = "%";
> }
>
> var sqlText = new String();
>
> // Add query to sqlText variable.
> sqlText = "select poolyear, poolperiod, periodstart, periodend, siteid,
> orgid, comp, can, [open], open_late, "
> + " comp_ot, comp_early_late, noactfinish, total, comp_ot_cal, getdate()
as
> 'currentdate' "
> + " from osc_kpi_pm "
> + " where " + params["where"]
> + " and siteid like '%" + tempsiteidString + "%' "
> + " and poolyear like '" + temppoolyearString + "' "
> + " order by siteid, poolperiod"
> ;
>
> maximoDataSet.setQuery(sqlText);
>
> /*Report Query Code Ends*/
>
> --- In MAXIMO@yahoogroups.com <mailto:MAXIMO%40yahoogroups.com>
<mailto:MAXIMO%40yahoogroups.com>
> <mailto:MAXIMO%40yahoogroups.com>, Chad Stringer <cstringer@
> <mailto:cstringer@%3cmailto:cstringer@> <mailto:cstringer@>> wrote:
> >
> > You might try getting a hold of an SMRP (Society of Maintenance and
> Reliability Professionals) handbook. They have a lot of great metrics that
> are industry standards to look into. That is a great place to start, and
> where I pull most of my ideas for KPIs.
> > The SQL code is a whole different matter. Many companies use custom
fields
> or different parameters based on how they measure things. I'm sure there
are
> some really good base queries available to use though. Best of luck, and
> keep us all in the loop on your progress.
> >
> > CJ Stringer
> >
> > From: MAXIMO@yahoogroups.com <mailto:MAXIMO%40yahoogroups.com>
<mailto:MAXIMO%40yahoogroups.com>
> <mailto:MAXIMO%40yahoogroups.com> [mailto:MAXIMO@yahoogroups.com
<mailto:MAXIMO%40yahoogroups.com>
> <mailto:MAXIMO%40yahoogroups.com> <mailto:MAXIMO%40yahoogroups.com>] On
> Behalf Of john_gould14
> > Sent: Friday, December 09, 2011 8:28 AM
> > To: MAXIMO@yahoogroups.com <mailto:MAXIMO%40yahoogroups.com>
<mailto:MAXIMO%40yahoogroups.com>
> <mailto:MAXIMO%40yahoogroups.com>
> > Subject: [MAXIMO List] KPI Roundup
> >
> >
> >
> > Hello All - in attempting to further develop our start center KPI's to
get
> better visualization of our orgnaizations performance, I'm always
wondering
> what other KPI's people are using. I have the standard, KPI's like
> percetnage of work type, average age of work orders, # of work orders
> reported versus completed, inprogress, open emergencies, PM's overdue -
> things like that.
> >
> > If you have some more 'interesting' ones to share I'd love to hear about
> them. I'm a SQL novice so if you have the SQL code - even better. I'm
> running version 7.5.0 with Oracle DB.
> >
> >
> >
> >
> >
>
> ________________________________
> ----Notice Regarding Confidentiality----
>
> This email, including any and all attachments, (this "Email") is intended
> only for the party to whom it is addressed and may contain information
that
> is confidential or privileged. Sierra Systems Group Inc. and its
affiliates
> accept no responsibility for any loss or damage suffered by any person
> resulting from any unauthorized use of or reliance upon this Email. If you
> are not the intended recipient, you are hereby notified that any
> dissemination, copying or other use of this Email is prohibited. Please
> notify us of the error in communication by return email and destroy all
> copies of this Email. Thank you.
>
>
>
>
>
>
>
>
>
Hi,
That creates another receipt transaction type record not a return type record.
I have audit tables on all purchasing related tables. I have a field we added that we can add comments at anytime so users can update where the PO is with info like UPS tacking numbers etc... I make a comment in there that I'm deleting the services and that is put into the audit table. Then I delte the table records.
Not the best thing to do but why MRO/IBM thought you would never return a service is beyond me. Users are always receiving the wrong PO lines and sometimes they are services.
dave Bone
--- In MAXIMO@yahoogroups.com, "Shannon Rotz" <shannonrotz@...> wrote:
>
> Hi Dave: regarding the services - does clicking on "New Row" and entering a
> negative quantity not work? Also, FYI: V7.5 has a PM forecasting ability
> (finally)!
>
>
>
>
>
> Shannon
>
>
>
> From: MAXIMO@yahoogroups.com [mailto:MAXIMO@yahoogroups.com] On Behalf Of
> in2data
> Sent: December-12-11 8:48 AM
> To: MAXIMO@yahoogroups.com
> Subject: Re: [MAXIMO List] KPI Roundup
>
>
>
>
>
> Hi,
>
> What Shannon said times 2! I've been using Maximo since 1994 and we've
> always had issues that can be fixed in no other way. It's always the last
> thing I try. As always you test the heck out of anything before you put it
> in production and test again for any upgrades. So you want to keep this to a
> minimum.
>
> I doubt that IBM will ever eliminate the use of stored procedures and other
> direct database update tools. In this case the stored procedure does not
> update any out of the box Maximo tables. It updates a table that is only
> used for reporting.
>
> I have another stored procedure that updates a table to forecast PM work
> orders for the next three years. Of course a PM forecast is totally missing
> in Maximo unless you want to pay IBM or someone else to do this.
>
> Shannon's issue is just one more case in point.
>
> IBM should code the application to trap characters that cannot be handled.
>
> Failing that the Maximo escalations should provide a way to run SQL code to
> update issues that cannot be otherwise accomplished. At least then you would
> have everything in one application.
>
> For instance in 7.1.1.6 you cannot return a service. So how does IBM expect
> us to return a service other than updating the tables directly? Maybe they
> should hire a business analyst that actually knows something about
> maintenance so they don't do something stupid like this?
>
> Dave Bone
> Ocean Spray Cranberries
>
> --- In MAXIMO@yahoogroups.com <mailto:MAXIMO%40yahoogroups.com> , "Shannon
> Rotz" <shannonrotz@> wrote:
> >
> > We (at least those of us who have been around a while) are all aware of
> > that, and I have to say that IBM has been making the need for direct
> > database coding less and less necessary. But there are still times when a
> > Stored Proc does the job better than anything that can be done using the
> GUI
> > tools, especially since the Stored Procs are pre-compiled on the database
> > side for increased speed.
> >
> >
> >
> > Even though I'm a dyed-in-the-wool "use the GUI to do everything" kind of
> > person, I actually considered using a trigger for something the other day
> .
> > it had to do with a problem with special characters in the Long
> Description.
> > Users were copying the bodies of e-mails (containing vendor quotes) into
> the
> > Long Description on PR Lines, and then saving the record. That part worked
> > fine - but when the record needed to be processed further down the line
> > (i.e. the Invoice stage), it resulted in a Java error and the inability to
> > save the invoice until the offending characters were removed. For a while,
> > the IT tech on the scene was editing the Long Descriptions using a text
> > tool, which needless to say, was not time-effective. So eventually they
> > stopped doing the copying altogether, which is a shame. I got the idea of
> > using a trigger to intercept the text before it went into the
> > LONGDESCRIPTION table and replace the problem characters to more useful
> > ones.
> >
> >
> >
> > So on rare occasions, I still think database-side code is useful - but
> only
> > at the discretion of someone who has a lot of experience and knowledge of
> > the product, and can make a VERY informed judgement.
> >
> >
> >
> > I do agree with you, though, that people who have knowledge of Java and/or
> > Database-side code (TRANSACT-SQL or PL/SQL), but are new to Maximo, are
> > frequently too ready to use code to solve a problem - only to
> inadvertently
> > "gum things up" and cause more problems down the line.
> >
> >
> >
> >
> >
> > Shannon
> >
> >
> >
> > P.S. If anyone has a solution to the Long Description problem, using the
> > GUI tools or even Java, speak up - I'd like to hear it. (Although for all
> I
> > know, it may be fixed in 7.5 . I'll check it out). I haven't actually
> > implemented my trigger idea, and I'd rather not, if possible.
> >
> >
> >
> > From: MAXIMO@yahoogroups.com <mailto:MAXIMO%40yahoogroups.com>
> [mailto:MAXIMO@yahoogroups.com <mailto:MAXIMO%40yahoogroups.com> ] On Behalf
> Of
> > Hall, Michael
> > Sent: December-09-11 10:50 AM
> > To: MAXIMO@yahoogroups.com <mailto:MAXIMO%40yahoogroups.com>
> > Subject: RE: [MAXIMO List] KPI Roundup
> >
> >
> >
> >
> >
> > IBM highly discourages the use of stored procedures in the Maximo
> database.
> >
> > Michael
> >
> > From: MAXIMO@yahoogroups.com <mailto:MAXIMO%40yahoogroups.com>
> <mailto:MAXIMO%40yahoogroups.com>
> > [mailto:MAXIMO@yahoogroups.com <mailto:MAXIMO%40yahoogroups.com>
> <mailto:MAXIMO%40yahoogroups.com> ] On Behalf
> > Of in2data
> > Sent: Friday, December 09, 2011 9:59 AM
> > To: MAXIMO@yahoogroups.com <mailto:MAXIMO%40yahoogroups.com>
> <mailto:MAXIMO%40yahoogroups.com>
> > Subject: Re: [MAXIMO List] KPI Roundup
> >
> > Hi,
> >
> > We have one to measure the On-Time PM rate. PMs are on-time only if they
> are
> > completed within the allowed window. For instance 90 day or less PMs have
> > =/- 7 days of the scheduled start date to be completed. The system copies
> > the target start date from the PM work order to the scheduled start date
> > just after the work order is generated. The stored procedure also does
> this
> > if the scheduled start date is still null. The site maintenance planner
> can
> > then move the scheduled start date to align with available downtime.
> >
> > The way it's calculated is a stored procedure is run on the database each
> > night that updates a custom table OSC_KPI_PM. You can and should create
> the
> > custom table in Maximo but the stored procedure has to be run from the
> > database. The code is below and it's for SQL Server. Your DB admins can
> > convert to Oracle.
> >
> > I also included the code to create the table in the DB. You can use this
> for
> > the table attributes in Maximo.
> >
> > Then you create a report using the query at the end. again that's for SQL
> > and will need some editing for Oracle. I've added some conditional colors
> in
> > the report to show where on-time rates are out of the KPI metric limit.
> >
> > Why did I use a stored procedure? It takes a long time to calculate the
> KPIs
> > and having a table already populated with the results makes the report run
> > much faster. The users like to run this on demand.
> >
> > In the code where you run across poolyear and other values like that those
> > are internal calendar periods we use. OSC is an agricultural co-operative
> > and we measure everything by the pool year calendar. Those periods do not
> > align with the normal Jan-Dec calendar. Our pool year starts the end of
> Feb
> > and has varying pool period lengths from 25 to 32 days. Our financial
> > periods in Maximo are set to those pool periods.
> >
> > OK. That's a lot of fire hose time. I'll turn it off now.
> >
> > Enjoy the briar patch!
> >
> > Dave Bone
> > Ocean Spray Cranberries
> >
> > /*Stored Prodecure Code Begins*/
> >
> > USE [maxprod]
> > GO
> > /****** Object: StoredProcedure [dbo].[OSC_KPI_PM_Update] Script Date:
> > 12/09/2011 08:35:43 ******/
> > SET ANSI_NULLS ON
> > GO
> > SET QUOTED_IDENTIFIER ON
> > GO
> >
> > --Drop PROCEDURE OSC_KPI_PM_Update
> > -- exec OSC_KPI_PM_Update
> > -- select * from OSC_KPI_PM
> > CREATE PROCEDURE [dbo].[OSC_KPI_PM_Update]
> > AS
> > update workorder set schedfinish = targcompdate
> > where ((schedfinish is null) and (targcompdate is not null)
> > and (worktype = 'PM' or worktype = 'PMYR' or worktype = 'REG'))
> > Delete from OSC_KPI_PM
> > Declare
> > @90daykpi as varchar(8),
> > @over90daykpi as varchar(8),
> > @fiscalyr as varchar(8)
> > set @90daykpi = '7'
> > set @over90daykpi = '15'
> > set @fiscalyr = (Select LEFT(f.financialperiod, 4) FROM financialperiods f
> > Where orgid = 'OSC' and (f.periodstart <= getdate()) AND (f.periodend >
> > getdate()))
> > Insert into OSC_KPI_PM
> >
> (poolperiod,periodstart,periodend,siteid,orgid,comp,can,[open],open_late,com
> > p_ot,comp_early_late,noactfinish,total)
> > SELECT financialperiod as poolperiod, periodstart, periodend, w.siteid,
> > w.orgid,
> > Case When (count(CASE WHEN (w.status = 'CLOSE' or w.status = 'COMP' or
> > w.status = 'COMPCLOSE') THEN wonum ELSE null END)) = 0 THEN 1 ELSE
> > (count(CASE WHEN (w.status = 'CLOSE' or w.status = 'COMP' or w.status =
> > 'COMPCLOSE') THEN wonum ELSE null END)) END AS COMP,
> > --this is the old comp the one above keeps from having a divide by 0 error
> > when no pm is comp,can or open_late count(CASE WHEN (w.status = 'CLOSE' or
> > w.status = 'COMP') THEN wonum ELSE null END) AS COMP,
> > count(CASE WHEN (w.status = 'CAN' or w.status = 'CANHOL' or w.status =
> > 'CANOPS') THEN wonum ELSE null END) AS CAN,
> > count(CASE WHEN (w.status <> 'CLOSE' and w.status <> 'COMP' and w.status
> <>
> > 'COMPCLOSE' and w.status <> 'CAN' and w.status <> 'CANHOL' and w.status <>
> > 'CANOPS')
> > THEN wonum ELSE null END) AS 'OPEN',
> >
> > count(CASE WHEN (w.status <> 'CLOSE' and w.status <> 'COMP' and w.status
> <>
> > 'COMPCLOSE' and w.status <> 'CAN' and w.status <> 'CANHOL' and w.status <>
> > 'CANOPS')
> > and
> > (((w.schedfinish + 7 <= getdate())
> > and ((Case p.frequnit
> > when 'DAYS' then Isnull(p.frequency,0)
> > when 'WEEKS' then (Isnull(p.frequency,0) * 7)
> > when 'MONTHS' then (Isnull(p.frequency,0) * 30)
> > when 'YEARS' then (Isnull(p.frequency,0) * 365)
> > else 0
> > end)*Isnull(s.interval,1) <= '90')
> > and ((Case p.frequnit
> > when 'DAYS' then Isnull(p.frequency,0)
> > when 'WEEKS' then (Isnull(p.frequency,0) * 7)
> > when 'MONTHS' then (Isnull(p.frequency,0) * 30)
> > when 'YEARS' then (Isnull(p.frequency,0) * 365)
> > else 0
> > end)*Isnull(s.interval,1) >= '28')
> > and (abs(datediff(dd,w.schedfinish,getdate())) > @90daykpi))
> > or
> > ((w.schedfinish + 15 <= getdate())
> > and ((Case p.frequnit
> > when 'DAYS' then Isnull(p.frequency,0)
> > when 'WEEKS' then (Isnull(p.frequency,0) * 7)
> > when 'MONTHS' then (Isnull(p.frequency,0) * 30)
> > when 'YEARS' then (Isnull(p.frequency,0) * 365)
> > else 0
> > end)*Isnull(s.interval,1) > '90')
> > and (abs(datediff(dd,w.schedfinish,getdate())) > @over90daykpi)))
> > THEN wonum ELSE null END) AS 'OPEN_LATE',
> >
> > count(CASE WHEN (w.status = 'CLOSE' or w.status = 'COMP' or w.status =
> > 'COMPCLOSE')
> > and
> > ((((Case p.frequnit
> > when 'DAYS' then Isnull(p.frequency,0)
> > when 'WEEKS' then (Isnull(p.frequency,0) * 7)
> > when 'MONTHS' then (Isnull(p.frequency,0) * 30)
> > when 'YEARS' then (Isnull(p.frequency,0) * 365)
> > else 0
> > end)*Isnull(s.interval,1) <= '90')
> > and ((Case p.frequnit
> > when 'DAYS' then Isnull(p.frequency,0)
> > when 'WEEKS' then (Isnull(p.frequency,0) * 7)
> > when 'MONTHS' then (Isnull(p.frequency,0) * 30)
> > when 'YEARS' then (Isnull(p.frequency,0) * 365)
> > else 0
> > end)*Isnull(s.interval,1) >= '28')
> > and (abs(datediff(dd,w.schedfinish,w.actfinish)) <= @90daykpi))
> > or
> > (((Case p.frequnit
> > when 'DAYS' then Isnull(p.frequency,0)
> > when 'WEEKS' then (Isnull(p.frequency,0) * 7)
> > when 'MONTHS' then (Isnull(p.frequency,0) * 30)
> > when 'YEARS' then (Isnull(p.frequency,0) * 365)
> > else 0
> > end)*Isnull(s.interval,1) > '90')
> > and (abs(datediff(dd,w.schedfinish,w.actfinish)) <= @over90daykpi)))
> > THEN wonum ELSE null END) AS COMP_OT,
> >
> > count(CASE WHEN (w.status = 'CLOSE' or w.status = 'COMP' or w.status =
> > 'COMPCLOSE')
> > and
> > ((((Case p.frequnit
> > when 'DAYS' then Isnull(p.frequency,0)
> > when 'WEEKS' then (Isnull(p.frequency,0) * 7)
> > when 'MONTHS' then (Isnull(p.frequency,0) * 30)
> > when 'YEARS' then (Isnull(p.frequency,0) * 365)
> > else 0
> > end)*Isnull(s.interval,1) <= '90')
> > and ((Case p.frequnit
> > when 'DAYS' then Isnull(p.frequency,0)
> > when 'WEEKS' then (Isnull(p.frequency,0) * 7)
> > when 'MONTHS' then (Isnull(p.frequency,0) * 30)
> > when 'YEARS' then (Isnull(p.frequency,0) * 365)
> > else 0
> > end)*Isnull(s.interval,1) >= '28')
> > and (abs(datediff(dd,w.schedfinish,w.actfinish)) > @90daykpi))
> > or
> > (((Case p.frequnit
> > when 'DAYS' then Isnull(p.frequency,0)
> > when 'WEEKS' then (Isnull(p.frequency,0) * 7)
> > when 'MONTHS' then (Isnull(p.frequency,0) * 30)
> > when 'YEARS' then (Isnull(p.frequency,0) * 365)
> > else 0
> > end)*Isnull(s.interval,1) > '90')
> > and (abs(datediff(dd,w.schedfinish,w.actfinish)) > @over90daykpi)))
> > THEN wonum ELSE null END) AS COMP_EARLY_LATE,
> >
> > count(CASE WHEN (w.status = 'CLOSE' or w.status = 'COMP' or w.status =
> > 'COMPCLOSE')
> > and actfinish is null
> > THEN wonum ELSE null END) AS NOACTFINISH,
> >
> > count(wonum) AS TOTAL
> >
> > FROM workorder w
> > left outer join pm p on (p.pmnum = w.pmnum and p.orgid = w.orgid and
> > p.siteid = w.siteid
> > and (Case p.frequnit
> > when 'DAYS' then Isnull(p.frequency,0)
> > when 'WEEKS' then (Isnull(p.frequency,0) * 7)
> > when 'MONTHS' then (Isnull(p.frequency,0) * 30)
> > when 'YEARS' then (Isnull(p.frequency,0) * 365)
> > else 0
> > end) >= '28')
> > join financialperiods f on (w.schedfinish >= f.periodstart and
> w.schedfinish
> > < f.periodend and w.orgid = f.orgid)
> > left outer join pmsequence s on (s.jpnum = w.jpnum and s.pmnum = w.pmnum
> and
> > s.orgid = f.orgid and s.siteid = w.siteid)
> > WHERE (istask = 0 and w.pmnum is not null) and (woclass = 'WORKORDER' or
> > woclass = 'ACTIVITY')
> > and (w.worktype = 'PM' or w.worktype = 'PMYR' or w.worktype = 'REG')
> > and ((Case p.frequnit
> > when 'DAYS' then Isnull(p.frequency,0)
> > when 'WEEKS' then (Isnull(p.frequency,0) * 7)
> > when 'MONTHS' then (Isnull(p.frequency,0) * 30)
> > when 'YEARS' then (Isnull(p.frequency,0) * 365)
> > else 0
> > end)*Isnull(s.interval,1) >= '28')
> > and (left(financialperiod,4) >= (@fiscalyr - 5))
> > and w.wonum not in (select wonum from wostatus where status = 'DELSCHED')
> > GROUP BY financialperiod, periodstart, periodend, w.siteid, w.orgid
> > order by financialperiod
> >
> > /*Stored Prodecure Code End*/
> >
> > /*Table Create Code Begins*/
> >
> > USE [maxprod]
> > GO
> > /****** Object: Table [dbo].[OSC_KPI_MSP] Script Date: 12/09/2011 08:46:34
> > ******/
> > SET ANSI_NULLS ON
> > GO
> > SET QUOTED_IDENTIFIER ON
> > GO
> > SET ANSI_PADDING ON
> > GO
> > CREATE TABLE [dbo].[OSC_KPI_MSP](
> > [poolyear] AS (left([poolperiod],(4))) PERSISTED,
> > [poolperiod] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> > [periodstart] [smalldatetime] NULL,
> > [periodend] [smalldatetime] NULL,
> > [siteid] [nchar](24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> > [orgid] [nchar](24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> > [comp] [decimal](5, 0) NULL,
> > [can] [decimal](5, 0) NULL,
> > [open] [decimal](5, 0) NULL,
> > [open_late] [decimal](5, 0) NULL,
> > [comp_ot] [decimal](5, 0) NULL,
> > [comp_early_late] [decimal](5, 0) NULL,
> > [noactfinish] [decimal](5, 0) NULL,
> > [total] [decimal](5, 0) NULL,
> > [comp_ot_cal] AS ([comp_ot]/(([comp]+[open_late])+[can])) PERSISTED
> > ) ON [PRIMARY]
> >
> > GO
> > SET ANSI_PADDING OFF
> >
> > /*Table Create Code Ends*/
> >
> > /*Report Query Code Begins*/
> >
> > maximoDataSet =
> > MXReportDataSetProvider.create(this.getDataSource().getName(),
> > this.getName());
> > maximoDataSet.open();
> >
> > var temppoolyearString = new String();
> > var tempsiteidString = new String();
> >
> > if(params["poolyear"].value)
> > {
> > temppoolyearString =
> > reportContext.getParameterValue("poolyear").toUpperCase();
> > }
> > else
> > {
> > temppoolyearString = "%";
> > }
> >
> > if(params["siteid"].value)
> > {
> > tempsiteidString =
> reportContext.getParameterValue("siteid").toUpperCase();
> > }
> > else
> > {
> > tempsiteidString = "%";
> > }
> >
> > var sqlText = new String();
> >
> > // Add query to sqlText variable.
> > sqlText = "select poolyear, poolperiod, periodstart, periodend, siteid,
> > orgid, comp, can, [open], open_late, "
> > + " comp_ot, comp_early_late, noactfinish, total, comp_ot_cal, getdate()
> as
> > 'currentdate' "
> > + " from osc_kpi_pm "
> > + " where " + params["where"]
> > + " and siteid like '%" + tempsiteidString + "%' "
> > + " and poolyear like '" + temppoolyearString + "' "
> > + " order by siteid, poolperiod"
> > ;
> >
> > maximoDataSet.setQuery(sqlText);
> >
> > /*Report Query Code Ends*/
> >
> > --- In MAXIMO@yahoogroups.com <mailto:MAXIMO%40yahoogroups.com>
> <mailto:MAXIMO%40yahoogroups.com>
> > <mailto:MAXIMO%40yahoogroups.com>, Chad Stringer <cstringer@
> > <mailto:cstringer@%3cmailto:cstringer@> <mailto:cstringer@>> wrote:
> > >
> > > You might try getting a hold of an SMRP (Society of Maintenance and
> > Reliability Professionals) handbook. They have a lot of great metrics that
> > are industry standards to look into. That is a great place to start, and
> > where I pull most of my ideas for KPIs.
> > > The SQL code is a whole different matter. Many companies use custom
> fields
> > or different parameters based on how they measure things. I'm sure there
> are
> > some really good base queries available to use though. Best of luck, and
> > keep us all in the loop on your progress.
> > >
> > > CJ Stringer
> > >
> > > From: MAXIMO@yahoogroups.com <mailto:MAXIMO%40yahoogroups.com>
> <mailto:MAXIMO%40yahoogroups.com>
> > <mailto:MAXIMO%40yahoogroups.com> [mailto:MAXIMO@yahoogroups.com
> <mailto:MAXIMO%40yahoogroups.com>
> > <mailto:MAXIMO%40yahoogroups.com> <mailto:MAXIMO%40yahoogroups.com>] On
> > Behalf Of john_gould14
> > > Sent: Friday, December 09, 2011 8:28 AM
> > > To: MAXIMO@yahoogroups.com <mailto:MAXIMO%40yahoogroups.com>
> <mailto:MAXIMO%40yahoogroups.com>
> > <mailto:MAXIMO%40yahoogroups.com>
> > > Subject: [MAXIMO List] KPI Roundup
> > >
> > >
> > >
> > > Hello All - in attempting to further develop our start center KPI's to
> get
> > better visualization of our orgnaizations performance, I'm always
> wondering
> > what other KPI's people are using. I have the standard, KPI's like
> > percetnage of work type, average age of work orders, # of work orders
> > reported versus completed, inprogress, open emergencies, PM's overdue -
> > things like that.
> > >
> > > If you have some more 'interesting' ones to share I'd love to hear about
> > them. I'm a SQL novice so if you have the SQL code - even better. I'm
> > running version 7.5.0 with Oracle DB.
> > >
> > >
> > >
> > >
> > >
> >
> > ________________________________
> > ----Notice Regarding Confidentiality----
> >
> > This email, including any and all attachments, (this "Email") is intended
> > only for the party to whom it is addressed and may contain information
> that
> > is confidential or privileged. Sierra Systems Group Inc. and its
> affiliates
> > accept no responsibility for any loss or damage suffered by any person
> > resulting from any unauthorized use of or reliance upon this Email. If you
> > are not the intended recipient, you are hereby notified that any
> > dissemination, copying or other use of this Email is prohibited. Please
> > notify us of the error in communication by return email and destroy all
> > copies of this Email. Thank you.
> >
> >
> >
> >
> >
> >
> >
> >
> >
>
>
>
>
>
>
>
Right, but it's a negative quantity and a negative line cost - the
accountants are usually happy with that .
Shannon
From: MAXIMO@yahoogroups.com [mailto:MAXIMO@yahoogroups.com] On Behalf Of
in2data
Sent: December-13-11 8:49 AM
To: MAXIMO@yahoogroups.com
Subject: Re: [MAXIMO List] KPI Roundup
Hi,
That creates another receipt transaction type record not a return type
record.
I have audit tables on all purchasing related tables. I have a field we
added that we can add comments at anytime so users can update where the PO
is with info like UPS tacking numbers etc... I make a comment in there that
I'm deleting the services and that is put into the audit table. Then I delte
the table records.
Not the best thing to do but why MRO/IBM thought you would never return a
service is beyond me. Users are always receiving the wrong PO lines and
sometimes they are services.
dave Bone
--- In MAXIMO@yahoogroups.com <mailto:MAXIMO%40yahoogroups.com> , "Shannon
Rotz" <shannonrotz@...> wrote:
>
> Hi Dave: regarding the services - does clicking on "New Row" and entering
a
> negative quantity not work? Also, FYI: V7.5 has a PM forecasting ability
> (finally)!
>
>
>
>
>
> Shannon
>
>
>
> From: MAXIMO@yahoogroups.com <mailto:MAXIMO%40yahoogroups.com>
[mailto:MAXIMO@yahoogroups.com <mailto:MAXIMO%40yahoogroups.com> ] On Behalf
Of
> in2data
> Sent: December-12-11 8:48 AM
> To: MAXIMO@yahoogroups.com <mailto:MAXIMO%40yahoogroups.com>
> Subject: Re: [MAXIMO List] KPI Roundup
>
>
>
>
>
> Hi,
>
> What Shannon said times 2! I've been using Maximo since 1994 and we've
> always had issues that can be fixed in no other way. It's always the last
> thing I try. As always you test the heck out of anything before you put it
> in production and test again for any upgrades. So you want to keep this to
a
> minimum.
>
> I doubt that IBM will ever eliminate the use of stored procedures and
other
> direct database update tools. In this case the stored procedure does not
> update any out of the box Maximo tables. It updates a table that is only
> used for reporting.
>
> I have another stored procedure that updates a table to forecast PM work
> orders for the next three years. Of course a PM forecast is totally
missing
> in Maximo unless you want to pay IBM or someone else to do this.
>
> Shannon's issue is just one more case in point.
>
> IBM should code the application to trap characters that cannot be handled.
>
> Failing that the Maximo escalations should provide a way to run SQL code
to
> update issues that cannot be otherwise accomplished. At least then you
would
> have everything in one application.
>
> For instance in 7.1.1.6 you cannot return a service. So how does IBM
expect
> us to return a service other than updating the tables directly? Maybe they
> should hire a business analyst that actually knows something about
> maintenance so they don't do something stupid like this?
>
> Dave Bone
> Ocean Spray Cranberries
>
> --- In MAXIMO@yahoogroups.com <mailto:MAXIMO%40yahoogroups.com>
<mailto:MAXIMO%40yahoogroups.com> , "Shannon
> Rotz" <shannonrotz@> wrote:
> >
> > We (at least those of us who have been around a while) are all aware of
> > that, and I have to say that IBM has been making the need for direct
> > database coding less and less necessary. But there are still times when
a
> > Stored Proc does the job better than anything that can be done using the
> GUI
> > tools, especially since the Stored Procs are pre-compiled on the
database
> > side for increased speed.
> >
> >
> >
> > Even though I'm a dyed-in-the-wool "use the GUI to do everything" kind
of
> > person, I actually considered using a trigger for something the other
day
> .
> > it had to do with a problem with special characters in the Long
> Description.
> > Users were copying the bodies of e-mails (containing vendor quotes) into
> the
> > Long Description on PR Lines, and then saving the record. That part
worked
> > fine - but when the record needed to be processed further down the line
> > (i.e. the Invoice stage), it resulted in a Java error and the inability
to
> > save the invoice until the offending characters were removed. For a
while,
> > the IT tech on the scene was editing the Long Descriptions using a text
> > tool, which needless to say, was not time-effective. So eventually they
> > stopped doing the copying altogether, which is a shame. I got the idea
of
> > using a trigger to intercept the text before it went into the
> > LONGDESCRIPTION table and replace the problem characters to more useful
> > ones.
> >
> >
> >
> > So on rare occasions, I still think database-side code is useful - but
> only
> > at the discretion of someone who has a lot of experience and knowledge
of
> > the product, and can make a VERY informed judgement.
> >
> >
> >
> > I do agree with you, though, that people who have knowledge of Java
and/or
> > Database-side code (TRANSACT-SQL or PL/SQL), but are new to Maximo, are
> > frequently too ready to use code to solve a problem - only to
> inadvertently
> > "gum things up" and cause more problems down the line.
> >
> >
> >
> >
> >
> > Shannon
> >
> >
> >
> > P.S. If anyone has a solution to the Long Description problem, using the
> > GUI tools or even Java, speak up - I'd like to hear it. (Although for
all
> I
> > know, it may be fixed in 7.5 . I'll check it out). I haven't actually
> > implemented my trigger idea, and I'd rather not, if possible.
> >
> >
> >
> > From: MAXIMO@yahoogroups.com <mailto:MAXIMO%40yahoogroups.com>
<mailto:MAXIMO%40yahoogroups.com>
> [mailto:MAXIMO@yahoogroups.com <mailto:MAXIMO%40yahoogroups.com>
<mailto:MAXIMO%40yahoogroups.com> ] On Behalf
> Of
> > Hall, Michael
> > Sent: December-09-11 10:50 AM
> > To: MAXIMO@yahoogroups.com <mailto:MAXIMO%40yahoogroups.com>
<mailto:MAXIMO%40yahoogroups.com>
> > Subject: RE: [MAXIMO List] KPI Roundup
> >
> >
> >
> >
> >
> > IBM highly discourages the use of stored procedures in the Maximo
> database.
> >
> > Michael
> >
> > From: MAXIMO@yahoogroups.com <mailto:MAXIMO%40yahoogroups.com>
<mailto:MAXIMO%40yahoogroups.com>
> <mailto:MAXIMO%40yahoogroups.com>
> > [mailto:MAXIMO@yahoogroups.com <mailto:MAXIMO%40yahoogroups.com>
<mailto:MAXIMO%40yahoogroups.com>
> <mailto:MAXIMO%40yahoogroups.com> ] On Behalf
> > Of in2data
> > Sent: Friday, December 09, 2011 9:59 AM
> > To: MAXIMO@yahoogroups.com <mailto:MAXIMO%40yahoogroups.com>
<mailto:MAXIMO%40yahoogroups.com>
> <mailto:MAXIMO%40yahoogroups.com>
> > Subject: Re: [MAXIMO List] KPI Roundup
> >
> > Hi,
> >
> > We have one to measure the On-Time PM rate. PMs are on-time only if they
> are
> > completed within the allowed window. For instance 90 day or less PMs
have
> > =/- 7 days of the scheduled start date to be completed. The system
copies
> > the target start date from the PM work order to the scheduled start date
> > just after the work order is generated. The stored procedure also does
> this
> > if the scheduled start date is still null. The site maintenance planner
> can
> > then move the scheduled start date to align with available downtime.
> >
> > The way it's calculated is a stored procedure is run on the database
each
> > night that updates a custom table OSC_KPI_PM. You can and should create
> the
> > custom table in Maximo but the stored procedure has to be run from the
> > database. The code is below and it's for SQL Server. Your DB admins can
> > convert to Oracle.
> >
> > I also included the code to create the table in the DB. You can use this
> for
> > the table attributes in Maximo.
> >
> > Then you create a report using the query at the end. again that's for
SQL
> > and will need some editing for Oracle. I've added some conditional
colors
> in
> > the report to show where on-time rates are out of the KPI metric limit.
> >
> > Why did I use a stored procedure? It takes a long time to calculate the
> KPIs
> > and having a table already populated with the results makes the report
run
> > much faster. The users like to run this on demand.
> >
> > In the code where you run across poolyear and other values like that
those
> > are internal calendar periods we use. OSC is an agricultural
co-operative
> > and we measure everything by the pool year calendar. Those periods do
not
> > align with the normal Jan-Dec calendar. Our pool year starts the end of
> Feb
> > and has varying pool period lengths from 25 to 32 days. Our financial
> > periods in Maximo are set to those pool periods.
> >
> > OK. That's a lot of fire hose time. I'll turn it off now.
> >
> > Enjoy the briar patch!
> >
> > Dave Bone
> > Ocean Spray Cranberries
> >
> > /*Stored Prodecure Code Begins*/
> >
> > USE [maxprod]
> > GO
> > /****** Object: StoredProcedure [dbo].[OSC_KPI_PM_Update] Script Date:
> > 12/09/2011 08:35:43 ******/
> > SET ANSI_NULLS ON
> > GO
> > SET QUOTED_IDENTIFIER ON
> > GO
> >
> > --Drop PROCEDURE OSC_KPI_PM_Update
> > -- exec OSC_KPI_PM_Update
> > -- select * from OSC_KPI_PM
> > CREATE PROCEDURE [dbo].[OSC_KPI_PM_Update]
> > AS
> > update workorder set schedfinish = targcompdate
> > where ((schedfinish is null) and (targcompdate is not null)
> > and (worktype = 'PM' or worktype = 'PMYR' or worktype = 'REG'))
> > Delete from OSC_KPI_PM
> > Declare
> > @90daykpi as varchar(8),
> > @over90daykpi as varchar(8),
> > @fiscalyr as varchar(8)
> > set @90daykpi = '7'
> > set @over90daykpi = '15'
> > set @fiscalyr = (Select LEFT(f.financialperiod, 4) FROM financialperiods
f
> > Where orgid = 'OSC' and (f.periodstart <= getdate()) AND (f.periodend >
> > getdate()))
> > Insert into OSC_KPI_PM
> >
>
(poolperiod,periodstart,periodend,siteid,orgid,comp,can,[open],open_late,com
> > p_ot,comp_early_late,noactfinish,total)
> > SELECT financialperiod as poolperiod, periodstart, periodend, w.siteid,
> > w.orgid,
> > Case When (count(CASE WHEN (w.status = 'CLOSE' or w.status = 'COMP' or
> > w.status = 'COMPCLOSE') THEN wonum ELSE null END)) = 0 THEN 1 ELSE
> > (count(CASE WHEN (w.status = 'CLOSE' or w.status = 'COMP' or w.status =
> > 'COMPCLOSE') THEN wonum ELSE null END)) END AS COMP,
> > --this is the old comp the one above keeps from having a divide by 0
error
> > when no pm is comp,can or open_late count(CASE WHEN (w.status = 'CLOSE'
or
> > w.status = 'COMP') THEN wonum ELSE null END) AS COMP,
> > count(CASE WHEN (w.status = 'CAN' or w.status = 'CANHOL' or w.status =
> > 'CANOPS') THEN wonum ELSE null END) AS CAN,
> > count(CASE WHEN (w.status <> 'CLOSE' and w.status <> 'COMP' and w.status
> <>
> > 'COMPCLOSE' and w.status <> 'CAN' and w.status <> 'CANHOL' and w.status
<>
> > 'CANOPS')
> > THEN wonum ELSE null END) AS 'OPEN',
> >
> > count(CASE WHEN (w.status <> 'CLOSE' and w.status <> 'COMP' and w.status
> <>
> > 'COMPCLOSE' and w.status <> 'CAN' and w.status <> 'CANHOL' and w.status
<>
> > 'CANOPS')
> > and
> > (((w.schedfinish + 7 <= getdate())
> > and ((Case p.frequnit
> > when 'DAYS' then Isnull(p.frequency,0)
> > when 'WEEKS' then (Isnull(p.frequency,0) * 7)
> > when 'MONTHS' then (Isnull(p.frequency,0) * 30)
> > when 'YEARS' then (Isnull(p.frequency,0) * 365)
> > else 0
> > end)*Isnull(s.interval,1) <= '90')
> > and ((Case p.frequnit
> > when 'DAYS' then Isnull(p.frequency,0)
> > when 'WEEKS' then (Isnull(p.frequency,0) * 7)
> > when 'MONTHS' then (Isnull(p.frequency,0) * 30)
> > when 'YEARS' then (Isnull(p.frequency,0) * 365)
> > else 0
> > end)*Isnull(s.interval,1) >= '28')
> > and (abs(datediff(dd,w.schedfinish,getdate())) > @90daykpi))
> > or
> > ((w.schedfinish + 15 <= getdate())
> > and ((Case p.frequnit
> > when 'DAYS' then Isnull(p.frequency,0)
> > when 'WEEKS' then (Isnull(p.frequency,0) * 7)
> > when 'MONTHS' then (Isnull(p.frequency,0) * 30)
> > when 'YEARS' then (Isnull(p.frequency,0) * 365)
> > else 0
> > end)*Isnull(s.interval,1) > '90')
> > and (abs(datediff(dd,w.schedfinish,getdate())) > @over90daykpi)))
> > THEN wonum ELSE null END) AS 'OPEN_LATE',
> >
> > count(CASE WHEN (w.status = 'CLOSE' or w.status = 'COMP' or w.status =
> > 'COMPCLOSE')
> > and
> > ((((Case p.frequnit
> > when 'DAYS' then Isnull(p.frequency,0)
> > when 'WEEKS' then (Isnull(p.frequency,0) * 7)
> > when 'MONTHS' then (Isnull(p.frequency,0) * 30)
> > when 'YEARS' then (Isnull(p.frequency,0) * 365)
> > else 0
> > end)*Isnull(s.interval,1) <= '90')
> > and ((Case p.frequnit
> > when 'DAYS' then Isnull(p.frequency,0)
> > when 'WEEKS' then (Isnull(p.frequency,0) * 7)
> > when 'MONTHS' then (Isnull(p.frequency,0) * 30)
> > when 'YEARS' then (Isnull(p.frequency,0) * 365)
> > else 0
> > end)*Isnull(s.interval,1) >= '28')
> > and (abs(datediff(dd,w.schedfinish,w.actfinish)) <= @90daykpi))
> > or
> > (((Case p.frequnit
> > when 'DAYS' then Isnull(p.frequency,0)
> > when 'WEEKS' then (Isnull(p.frequency,0) * 7)
> > when 'MONTHS' then (Isnull(p.frequency,0) * 30)
> > when 'YEARS' then (Isnull(p.frequency,0) * 365)
> > else 0
> > end)*Isnull(s.interval,1) > '90')
> > and (abs(datediff(dd,w.schedfinish,w.actfinish)) <= @over90daykpi)))
> > THEN wonum ELSE null END) AS COMP_OT,
> >
> > count(CASE WHEN (w.status = 'CLOSE' or w.status = 'COMP' or w.status =
> > 'COMPCLOSE')
> > and
> > ((((Case p.frequnit
> > when 'DAYS' then Isnull(p.frequency,0)
> > when 'WEEKS' then (Isnull(p.frequency,0) * 7)
> > when 'MONTHS' then (Isnull(p.frequency,0) * 30)
> > when 'YEARS' then (Isnull(p.frequency,0) * 365)
> > else 0
> > end)*Isnull(s.interval,1) <= '90')
> > and ((Case p.frequnit
> > when 'DAYS' then Isnull(p.frequency,0)
> > when 'WEEKS' then (Isnull(p.frequency,0) * 7)
> > when 'MONTHS' then (Isnull(p.frequency,0) * 30)
> > when 'YEARS' then (Isnull(p.frequency,0) * 365)
> > else 0
> > end)*Isnull(s.interval,1) >= '28')
> > and (abs(datediff(dd,w.schedfinish,w.actfinish)) > @90daykpi))
> > or
> > (((Case p.frequnit
> > when 'DAYS' then Isnull(p.frequency,0)
> > when 'WEEKS' then (Isnull(p.frequency,0) * 7)
> > when 'MONTHS' then (Isnull(p.frequency,0) * 30)
> > when 'YEARS' then (Isnull(p.frequency,0) * 365)
> > else 0
> > end)*Isnull(s.interval,1) > '90')
> > and (abs(datediff(dd,w.schedfinish,w.actfinish)) > @over90daykpi)))
> > THEN wonum ELSE null END) AS COMP_EARLY_LATE,
> >
> > count(CASE WHEN (w.status = 'CLOSE' or w.status = 'COMP' or w.status =
> > 'COMPCLOSE')
> > and actfinish is null
> > THEN wonum ELSE null END) AS NOACTFINISH,
> >
> > count(wonum) AS TOTAL
> >
> > FROM workorder w
> > left outer join pm p on (p.pmnum = w.pmnum and p.orgid = w.orgid and
> > p.siteid = w.siteid
> > and (Case p.frequnit
> > when 'DAYS' then Isnull(p.frequency,0)
> > when 'WEEKS' then (Isnull(p.frequency,0) * 7)
> > when 'MONTHS' then (Isnull(p.frequency,0) * 30)
> > when 'YEARS' then (Isnull(p.frequency,0) * 365)
> > else 0
> > end) >= '28')
> > join financialperiods f on (w.schedfinish >= f.periodstart and
> w.schedfinish
> > < f.periodend and w.orgid = f.orgid)
> > left outer join pmsequence s on (s.jpnum = w.jpnum and s.pmnum = w.pmnum
> and
> > s.orgid = f.orgid and s.siteid = w.siteid)
> > WHERE (istask = 0 and w.pmnum is not null) and (woclass = 'WORKORDER' or
> > woclass = 'ACTIVITY')
> > and (w.worktype = 'PM' or w.worktype = 'PMYR' or w.worktype = 'REG')
> > and ((Case p.frequnit
> > when 'DAYS' then Isnull(p.frequency,0)
> > when 'WEEKS' then (Isnull(p.frequency,0) * 7)
> > when 'MONTHS' then (Isnull(p.frequency,0) * 30)
> > when 'YEARS' then (Isnull(p.frequency,0) * 365)
> > else 0
> > end)*Isnull(s.interval,1) >= '28')
> > and (left(financialperiod,4) >= (@fiscalyr - 5))
> > and w.wonum not in (select wonum from wostatus where status =
'DELSCHED')
> > GROUP BY financialperiod, periodstart, periodend, w.siteid, w.orgid
> > order by financialperiod
> >
> > /*Stored Prodecure Code End*/
> >
> > /*Table Create Code Begins*/
> >
> > USE [maxprod]
> > GO
> > /****** Object: Table [dbo].[OSC_KPI_MSP] Script Date: 12/09/2011
08:46:34
> > ******/
> > SET ANSI_NULLS ON
> > GO
> > SET QUOTED_IDENTIFIER ON
> > GO
> > SET ANSI_PADDING ON
> > GO
> > CREATE TABLE [dbo].[OSC_KPI_MSP](
> > [poolyear] AS (left([poolperiod],(4))) PERSISTED,
> > [poolperiod] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> > [periodstart] [smalldatetime] NULL,
> > [periodend] [smalldatetime] NULL,
> > [siteid] [nchar](24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> > [orgid] [nchar](24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> > [comp] [decimal](5, 0) NULL,
> > [can] [decimal](5, 0) NULL,
> > [open] [decimal](5, 0) NULL,
> > [open_late] [decimal](5, 0) NULL,
> > [comp_ot] [decimal](5, 0) NULL,
> > [comp_early_late] [decimal](5, 0) NULL,
> > [noactfinish] [decimal](5, 0) NULL,
> > [total] [decimal](5, 0) NULL,
> > [comp_ot_cal] AS ([comp_ot]/(([comp]+[open_late])+[can])) PERSISTED
> > ) ON [PRIMARY]
> >
> > GO
> > SET ANSI_PADDING OFF
> >
> > /*Table Create Code Ends*/
> >
> > /*Report Query Code Begins*/
> >
> > maximoDataSet =
> > MXReportDataSetProvider.create(this.getDataSource().getName(),
> > this.getName());
> > maximoDataSet.open();
> >
> > var temppoolyearString = new String();
> > var tempsiteidString = new String();
> >
> > if(params["poolyear"].value)
> > {
> > temppoolyearString =
> > reportContext.getParameterValue("poolyear").toUpperCase();
> > }
> > else
> > {
> > temppoolyearString = "%";
> > }
> >
> > if(params["siteid"].value)
> > {
> > tempsiteidString =
> reportContext.getParameterValue("siteid").toUpperCase();
> > }
> > else
> > {
> > tempsiteidString = "%";
> > }
> >
> > var sqlText = new String();
> >
> > // Add query to sqlText variable.
> > sqlText = "select poolyear, poolperiod, periodstart, periodend, siteid,
> > orgid, comp, can, [open], open_late, "
> > + " comp_ot, comp_early_late, noactfinish, total, comp_ot_cal, getdate()
> as
> > 'currentdate' "
> > + " from osc_kpi_pm "
> > + " where " + params["where"]
> > + " and siteid like '%" + tempsiteidString + "%' "
> > + " and poolyear like '" + temppoolyearString + "' "
> > + " order by siteid, poolperiod"
> > ;
> >
> > maximoDataSet.setQuery(sqlText);
> >
> > /*Report Query Code Ends*/
> >
> > --- In MAXIMO@yahoogroups.com <mailto:MAXIMO%40yahoogroups.com>
<mailto:MAXIMO%40yahoogroups.com>
> <mailto:MAXIMO%40yahoogroups.com>
> > <mailto:MAXIMO%40yahoogroups.com>, Chad Stringer <cstringer@
> > <mailto:cstringer@%3cmailto:cstringer@> <mailto:cstringer@>> wrote:
> > >
> > > You might try getting a hold of an SMRP (Society of Maintenance and
> > Reliability Professionals) handbook. They have a lot of great metrics
that
> > are industry standards to look into. That is a great place to start, and
> > where I pull most of my ideas for KPIs.
> > > The SQL code is a whole different matter. Many companies use custom
> fields
> > or different parameters based on how they measure things. I'm sure there
> are
> > some really good base queries available to use though. Best of luck, and
> > keep us all in the loop on your progress.
> > >
> > > CJ Stringer
> > >
> > > From: MAXIMO@yahoogroups.com <mailto:MAXIMO%40yahoogroups.com>
<mailto:MAXIMO%40yahoogroups.com>
> <mailto:MAXIMO%40yahoogroups.com>
> > <mailto:MAXIMO%40yahoogroups.com> [mailto:MAXIMO@yahoogroups.com
<mailto:MAXIMO%40yahoogroups.com>
> <mailto:MAXIMO%40yahoogroups.com>
> > <mailto:MAXIMO%40yahoogroups.com> <mailto:MAXIMO%40yahoogroups.com>] On
> > Behalf Of john_gould14
> > > Sent: Friday, December 09, 2011 8:28 AM
> > > To: MAXIMO@yahoogroups.com <mailto:MAXIMO%40yahoogroups.com>
<mailto:MAXIMO%40yahoogroups.com>
> <mailto:MAXIMO%40yahoogroups.com>
> > <mailto:MAXIMO%40yahoogroups.com>
> > > Subject: [MAXIMO List] KPI Roundup
> > >
> > >
> > >
> > > Hello All - in attempting to further develop our start center KPI's to
> get
> > better visualization of our orgnaizations performance, I'm always
> wondering
> > what other KPI's people are using. I have the standard, KPI's like
> > percetnage of work type, average age of work orders, # of work orders
> > reported versus completed, inprogress, open emergencies, PM's overdue -
> > things like that.
> > >
> > > If you have some more 'interesting' ones to share I'd love to hear
about
> > them. I'm a SQL novice so if you have the SQL code - even better. I'm
> > running version 7.5.0 with Oracle DB.
> > >
> > >
> > >
> > >
> > >
> >
> > ________________________________
> > ----Notice Regarding Confidentiality----
> >
> > This email, including any and all attachments, (this "Email") is
intended
> > only for the party to whom it is addressed and may contain information
> that
> > is confidential or privileged. Sierra Systems Group Inc. and its
> affiliates
> > accept no responsibility for any loss or damage suffered by any person
> > resulting from any unauthorized use of or reliance upon this Email. If
you
> > are not the intended recipient, you are hereby notified that any
> > dissemination, copying or other use of this Email is prohibited. Please
> > notify us of the error in communication by return email and destroy all
> > copies of this Email. Thank you.
> >
> >
> >
> >
> >
> >
> >
> >
> >
>
>
>
>
>
>
>