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.
It's around that time of year when we look back and say, "I gotta do WHAT?"
ABC analysis. EOQs. And exactly when did I last issue stock from this bin?
1) I got a little tired of the looping in the ABC analysis report. I dunno, I figure you should let the database server do all the heavy lifting. Here's an example of figuring out the ABC of your items directly; you can pump this into an UPDATE if you like.
SELECT itemnum, ytd_issuecost, cumSum,
CASE
WHEN ABCPCT <= 0.80 THEN 'A'
WHEN ABCPCT >= 0.95 THEN 'C'
ELSE 'B'
END tryme
FROM
(SELECT itemnum, ytd_issuecost,
SUM(ytd_issuecost) OVER (ORDER BY ytd_issuecost DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) cumSum,
(SUM(ytd_issuecost) OVER (ORDER BY ytd_issuecost DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) / (SELECT SUM(NVL(inventory.issueytd,0) * NVL(invcost.lastcost,0))
FROM inventory LEFT OUTER JOIN invcost ON inventory.LOCATION = invcost.LOCATION AND inventory.itemnum = invcost.itemnum AND inventory.itemsetid = invcost.itemsetid AND inventory.siteid = invcost.siteid
WHERE inventory.siteid= :theSite AND inventory.LOCATION = :theStore AND (inventory.abctype IN('A','B','C') OR inventory.abctype IS NULL))) ABCPCT
FROM
(SELECT inventory.itemnum, SUM( NVL(inventory.issueytd,0) * NVL(invcost.lastcost,0)) AS ytd_issuecost FROM inventory
LEFT OUTER JOIN invcost ON inventory.LOCATION = invcost.LOCATION AND inventory.itemnum = invcost.itemnum AND inventory.itemsetid = invcost.itemsetid AND inventory.siteid = invcost.siteid
WHERE inventory.siteid= :theSite AND inventory.LOCATION = :theStore AND (inventory.abctype IN('A','B','C') OR inventory.abctype IS NULL)
GROUP BY inventory.itemnum ) xx
) yy
;;
2) Calculating asset costs against assethierarchy is a little painful. I rolled this comparison SELECT to tell me if my transactions were more accurate than the running workorder totals. TL;DR I think it's better to gather transactions.
SELECT ass.siteid, ass.assetnum, ass.DESCRIPTION, ass.PARENT,
NVL(lt.linecost,0) laborC, NVL(mut.linecost,0) matC, NVL(srt.linecost,0) servC, NVL(tt.linecost,0) toolC, ( NVL(lt.linecost,0) + NVL(mut.linecost,0) + NVL(srt.linecost,0) + NVL(tt.linecost,0) ) calcC,
wo.actL, wo.actM, wo.actS, wo.actT, wo.allCosts
FROM asset ass
LEFT OUTER JOIN
(SELECT siteid, EXTRACT(YEAR FROM transdate) AS theYr, assetnum, NVL(SUM(linecost),0) linecost FROM labtrans
WHERE assetnum IS NOT NULL
AND transdate > :theDate AND (linecost != 0 AND linecost IS NOT NULL) AND genapprservreceipt = 1
GROUP BY siteid, EXTRACT(YEAR FROM transdate), assetnum) lt ON ass.siteid = lt.siteid AND ass.assetnum = lt.assetnum
LEFT OUTER JOIN
(SELECT siteid, EXTRACT(YEAR FROM transdate) AS theYr, assetnum, NVL(SUM(linecost),0) linecost FROM matusetrans
WHERE issuetype IN ('RETURN','ISSUE') AND assetnum IS NOT NULL
AND transdate > :theDate AND (linecost != 0 AND linecost IS NOT NULL)
GROUP BY siteid, EXTRACT(YEAR FROM transdate), assetnum) mut ON ass.siteid = mut.siteid AND ass.assetnum = mut.assetnum
LEFT OUTER JOIN
(SELECT siteid, EXTRACT(YEAR FROM transdate) AS theYr, assetnum, NVL(SUM(linecost),0) linecost FROM servrectrans
WHERE assetnum IS NOT NULL
AND transdate > :theDate AND (linecost != 0 AND linecost IS NOT NULL)
GROUP BY siteid, EXTRACT(YEAR FROM transdate), assetnum) srt ON ass.siteid = srt.siteid AND ass.assetnum = srt.assetnum
LEFT OUTER JOIN
(SELECT siteid, EXTRACT(YEAR FROM transdate) AS theYr, assetnum, NVL(SUM(linecost),0) linecost FROM tooltrans
WHERE assetnum IS NOT NULL
AND transdate > :theDate AND (linecost != 0 AND linecost IS NOT NULL)
GROUP BY siteid, EXTRACT(YEAR FROM transdate), assetnum) tt ON ass.siteid = tt.siteid AND ass.assetnum = tt.assetnum
LEFT OUTER JOIN
(SELECT siteid, EXTRACT(YEAR FROM reportdate) AS theYr, assetnum, NVL(SUM(actlabcost),0) actL, NVL(SUM(actmatcost),0) actM, NVL(SUM(actservcost),0) actS, NVL(SUM(acttoolcost),0) actT,
(NVL(SUM(actlabcost),0) + NVL(SUM(actmatcost),0) + NVL(SUM(actservcost),0) + NVL(SUM(acttoolcost),0)) allCosts FROM workorder
WHERE status != 'CAN' AND assetnum IS NOT NULL AND reportdate > :theDate AND (NVL(actlabcost,0) + NVL(actmatcost,0) + NVL(actservcost,0) + NVL(acttoolcost,0)) != 0
GROUP BY siteid, EXTRACT(YEAR FROM reportdate), assetnum) wo ON ass.siteid = wo.siteid AND ass.assetnum = wo.assetnum
WHERE
ass.siteid = :theSite
;;
Obviously you can change the date selection to use BETWEEN. The EXTRACT is a leftover of a prior query to percolate the year to the outer query. I kept it in as a stub for anyone who wanted ton extend the solution to a window of years.
Here's a faster version with just the values; use this as a materialized table for additional SELECTs.
SELECT ass.siteid, ass.assetnum,
(SELECT NVL(SUM(linecost),0) linecost FROM labtrans
WHERE assetnum IS NOT NULL AND transdate > :theDate AND (linecost != 0 AND linecost IS NOT NULL) AND genapprservreceipt = 1
AND ass.siteid = siteid AND ass.assetnum = assetnum) labC,
(SELECT NVL(SUM(linecost),0) linecost FROM matusetrans
WHERE issuetype IN ('RETURN','ISSUE') AND assetnum IS NOT NULL AND transdate > :theDate AND (linecost != 0 AND linecost IS NOT NULL)
AND ass.siteid = siteid AND ass.assetnum = assetnum) matC,
(SELECT NVL(SUM(linecost),0) linecost FROM servrectrans
WHERE assetnum IS NOT NULL AND transdate > :theDate AND (linecost != 0 AND linecost IS NOT NULL)
AND ass.siteid = siteid AND ass.assetnum = assetnum) servC,
(SELECT NVL(SUM(linecost),0) linecost FROM tooltrans
WHERE assetnum IS NOT NULL AND transdate > :theDate AND (linecost != 0 AND linecost IS NOT NULL)
AND ass.siteid = siteid AND ass.assetnum = assetnum) toolC
FROM asset ass
WHERE ass.siteid = :theSite
GROUP BY ass.siteid, ass.assetnum
;;
Yes, I know my aliases are juvenile.
3) Once you have a specific asset's totals, you can shove them in eq5, eq7, or eq12 if you don't trust me (don't trust me, always run these in a dev environment and verify for yourself). Now, let's roll them up.
WITH
asset_data AS
(SELECT assetnum, PARENT, NVL(eq5,0) eq5 FROM asset WHERE siteid = :theSite),
asset_tree AS
(SELECT assetnum, PARENT, CONNECT_BY_ROOT assetnum AS rootAsset, LEVEL AS lvl, eq5 FROM asset_data
CONNECT BY PARENT = PRIOR assetnum
START WITH PARENT IS NULL)
SELECT assetnum, PARENT, eq5, rolledupCost FROM asset_tree
MODEL
PARTITION BY (rootAsset)
DIMENSION BY (PARENT, assetnum)
MEASURES (lvl, eq5, 0 rolledupCost)
( rolledupCost[ANY,ANY] ORDER BY lvl DESC, assetnum = ( NVL(eq5[cv(),cv()],0) + NVL(SUM(rolledupCost)[cv(assetnum),ANY],0) ) )
ORDER BY rootAsset, PARENT NULLS FIRST, assetnum
;
When you see how fast this executes, you'll never want to use the IBM report again. If you don't shove your computed costs in eq5 like I do, then just replace NVL(eq5,0) in asset_data with NVL(totalcost,0) and watch the result. I hacked the hell out of these analytic functions to make magic, so in 2018 my mission is to really understand just what it is I created!
4) Hey, did you ever get this question: "What are all the items I use for each asset? Oh and can I have it all on one line?"
Yeah, me too! This one uses LISTAGGs, regular expression replacements (and some implicit counting), and more of those data warehouse techniques I barely understand. Once I put it all together though, it was so cool.
SELECT DISTINCT siteid, assetnum, (1 + LENGTH(itemo) - LENGTH(REPLACE(itemo,';',''))) AS itemCnt,
itemo
FROM (
SELECT siteid, assetnum, regexp_replace((LISTAGG(itemnum,';') WITHIN GROUP (ORDER BY itemnum)), '([^;]+)(;\1)*(;|$)', '\1\3') AS itemo
FROM
((SELECT DISTINCT siteid, assetnum, itemnum FROM matusetrans WHERE issuetype = 'ISSUE' AND itemnum IS NOT NULL AND assetnum IS NOT NULL
AND transdate > ('01-JAN-'||(EXTRACT(YEAR FROM SYSDATE) - 3)) ) MATCH_RECOGNIZE (
PARTITION BY assetnum
ORDER BY itemnum
MEASURES match_number() AS mho
ALL ROWS PER MATCH
AFTER MATCH SKIP PAST LAST ROW
PATTERN (S B*)
DEFINE B AS LENGTHB(S.itemnum) + SUM(LENGTHB(B.itemnum) + LENGTHB(';')) <= 2500)
) h
GROUP BY siteid, assetnum, mho
) f
ORDER BY 1,3 DESC,2
;
I hope this helps some of you out there. It feels a little lonely here, but I guess everyone is on IBM's forums or polishing their reputations on LinkedIn. Can't blame any of ya, this mailing list was started in 1999 and I'm frankly amazed anyone still reads it. I'm still thankful for all of you people contributing and reading this over the years. It's done some good, and maybe there's still more good left to do.
If I don't talk to you in the next week, Happy New Year too!
-C
Thank you Remo for these gifts :) Happy New year for you too :)
Hey Chris! I'm still here ...
Question: are you using your Asset cost calculations as an alternative to
the DB rollup?
Shannon
From: MAXIMO@yahoogroups.com [mailto:MAXIMO@yahoogroups.com]
Sent: Tuesday, December 26, 2017 11:36 AM
To: MAXIMO@yahoogroups.com
Subject: [MAXIMO List] Some late XMas gifts
It's around that time of year when we look back and say, "I gotta do WHAT?"
ABC analysis. EOQs. And exactly when did I last issue stock from this bin?
1) I got a little tired of the looping in the ABC analysis report. I dunno,
I figure you should let the database server do all the heavy lifting. Here's
an example of figuring out the ABC of your items directly; you can pump this
into an UPDATE if you like.
SELECT itemnum, ytd_issuecost, cumSum,
CASE
WHEN ABCPCT <= 0.80 THEN 'A'
WHEN ABCPCT >= 0.95 THEN 'C'
ELSE 'B'
END tryme
FROM
(SELECT itemnum, ytd_issuecost,
SUM(ytd_issuecost) OVER (ORDER BY ytd_issuecost DESC ROWS BETWEEN UNBOUNDED
PRECEDING AND CURRENT ROW) cumSum,
(SUM(ytd_issuecost) OVER (ORDER BY ytd_issuecost DESC ROWS BETWEEN UNBOUNDED
PRECEDING AND CURRENT ROW) / (SELECT SUM(NVL(inventory.issueytd,0) *
NVL(invcost.lastcost,0))
FROM inventory LEFT OUTER JOIN invcost ON inventory.LOCATION =
invcost.LOCATION AND inventory.itemnum = invcost.itemnum AND
inventory.itemsetid = invcost.itemsetid AND inventory.siteid =
invcost.siteid
WHERE inventory.siteid= :theSite AND inventory.LOCATION = :theStore AND
(inventory.abctype IN('A','B','C') OR inventory.abctype IS NULL))) ABCPCT
FROM
(SELECT inventory.itemnum, SUM( NVL(inventory.issueytd,0) *
NVL(invcost.lastcost,0)) AS ytd_issuecost FROM inventory
LEFT OUTER JOIN invcost ON inventory.LOCATION = invcost.LOCATION AND
inventory.itemnum = invcost.itemnum AND inventory.itemsetid =
invcost.itemsetid AND inventory.siteid = invcost.siteid
WHERE inventory.siteid= :theSite AND inventory.LOCATION = :theStore AND
(inventory.abctype IN('A','B','C') OR inventory.abctype IS NULL)
GROUP BY inventory.itemnum ) xx
) yy
;;
2) Calculating asset costs against assethierarchy is a little painful. I
rolled this comparison SELECT to tell me if my transactions were more
accurate than the running workorder totals. TL;DR I think it's better to
gather transactions.
SELECT ass.siteid, ass.assetnum, ass.DESCRIPTION, ass.PARENT,
NVL(lt.linecost,0) laborC, NVL(mut.linecost,0) matC, NVL(srt.linecost,0)
servC, NVL(tt.linecost,0) toolC, ( NVL(lt.linecost,0) + NVL(mut.linecost,0)
+ NVL(srt.linecost,0) + NVL(tt.linecost,0) ) calcC,
wo.actL, wo.actM, wo.actS, wo.actT, wo.allCosts
FROM asset ass
LEFT OUTER JOIN
(SELECT siteid, EXTRACT(YEAR FROM transdate) AS theYr, assetnum,
NVL(SUM(linecost),0) linecost FROM labtrans
WHERE assetnum IS NOT NULL
AND transdate > :theDate AND (linecost != 0 AND linecost IS NOT NULL) AND
genapprservreceipt = 1
GROUP BY siteid, EXTRACT(YEAR FROM transdate), assetnum) lt ON ass.siteid =
lt.siteid AND ass.assetnum = lt.assetnum
LEFT OUTER JOIN
(SELECT siteid, EXTRACT(YEAR FROM transdate) AS theYr, assetnum,
NVL(SUM(linecost),0) linecost FROM matusetrans
WHERE issuetype IN ('RETURN','ISSUE') AND assetnum IS NOT NULL
AND transdate > :theDate AND (linecost != 0 AND linecost IS NOT NULL)
GROUP BY siteid, EXTRACT(YEAR FROM transdate), assetnum) mut ON ass.siteid =
mut.siteid AND ass.assetnum = mut.assetnum
LEFT OUTER JOIN
(SELECT siteid, EXTRACT(YEAR FROM transdate) AS theYr, assetnum,
NVL(SUM(linecost),0) linecost FROM servrectrans
WHERE assetnum IS NOT NULL
AND transdate > :theDate AND (linecost != 0 AND linecost IS NOT NULL)
GROUP BY siteid, EXTRACT(YEAR FROM transdate), assetnum) srt ON ass.siteid =
srt.siteid AND ass.assetnum = srt.assetnum
LEFT OUTER JOIN
(SELECT siteid, EXTRACT(YEAR FROM transdate) AS theYr, assetnum,
NVL(SUM(linecost),0) linecost FROM tooltrans
WHERE assetnum IS NOT NULL
AND transdate > :theDate AND (linecost != 0 AND linecost IS NOT NULL)
GROUP BY siteid, EXTRACT(YEAR FROM transdate), assetnum) tt ON ass.siteid =
tt.siteid AND ass.assetnum = tt.assetnum
LEFT OUTER JOIN
(SELECT siteid, EXTRACT(YEAR FROM reportdate) AS theYr, assetnum,
NVL(SUM(actlabcost),0) actL, NVL(SUM(actmatcost),0) actM,
NVL(SUM(actservcost),0) actS, NVL(SUM(acttoolcost),0) actT,
(NVL(SUM(actlabcost),0) + NVL(SUM(actmatcost),0) + NVL(SUM(actservcost),0) +
NVL(SUM(acttoolcost),0)) allCosts FROM workorder
WHERE status != 'CAN' AND assetnum IS NOT NULL AND reportdate > :theDate AND
(NVL(actlabcost,0) + NVL(actmatcost,0) + NVL(actservcost,0) +
NVL(acttoolcost,0)) != 0
GROUP BY siteid, EXTRACT(YEAR FROM reportdate), assetnum) wo ON ass.siteid =
wo.siteid AND ass.assetnum = wo.assetnum
WHERE
ass.siteid = :theSite
;;
Obviously you can change the date selection to use BETWEEN. The EXTRACT is a
leftover of a prior query to percolate the year to the outer query. I kept
it in as a stub for anyone who wanted ton extend the solution to a window of
years.
Here's a faster version with just the values; use this as a materialized
table for additional SELECTs.
SELECT ass.siteid, ass.assetnum,
(SELECT NVL(SUM(linecost),0) linecost FROM labtrans
WHERE assetnum IS NOT NULL AND transdate > :theDate AND (linecost != 0 AND
linecost IS NOT NULL) AND genapprservreceipt = 1
AND ass.siteid = siteid AND ass.assetnum = assetnum) labC,
(SELECT NVL(SUM(linecost),0) linecost FROM matusetrans
WHERE issuetype IN ('RETURN','ISSUE') AND assetnum IS NOT NULL AND transdate
> :theDate AND (linecost != 0 AND linecost IS NOT NULL)
AND ass.siteid = siteid AND ass.assetnum = assetnum) matC,
(SELECT NVL(SUM(linecost),0) linecost FROM servrectrans
WHERE assetnum IS NOT NULL AND transdate > :theDate AND (linecost != 0 AND
linecost IS NOT NULL)
AND ass.siteid = siteid AND ass.assetnum = assetnum) servC,
(SELECT NVL(SUM(linecost),0) linecost FROM tooltrans
WHERE assetnum IS NOT NULL AND transdate > :theDate AND (linecost != 0 AND
linecost IS NOT NULL)
AND ass.siteid = siteid AND ass.assetnum = assetnum) toolC
FROM asset ass
WHERE ass.siteid = :theSite
GROUP BY ass.siteid, ass.assetnum
;;
Yes, I know my aliases are juvenile.
3) Once you have a specific asset's totals, you can shove them in eq5, eq7,
or eq12 if you don't trust me (don't trust me, always run these in a dev
environment and verify for yourself). Now, let's roll them up.
WITH
asset_data AS
(SELECT assetnum, PARENT, NVL(eq5,0) eq5 FROM asset WHERE siteid =
:theSite),
asset_tree AS
(SELECT assetnum, PARENT, CONNECT_BY_ROOT assetnum AS rootAsset, LEVEL AS
lvl, eq5 FROM asset_data
CONNECT BY PARENT = PRIOR assetnum
START WITH PARENT IS NULL)
SELECT assetnum, PARENT, eq5, rolledupCost FROM asset_tree
MODEL
PARTITION BY (rootAsset)
DIMENSION BY (PARENT, assetnum)
MEASURES (lvl, eq5, 0 rolledupCost)
( rolledupCost[ANY,ANY] ORDER BY lvl DESC, assetnum = (
NVL(eq5[cv(),cv()],0) + NVL(SUM(rolledupCost)[cv(assetnum),ANY],0) ) )
ORDER BY rootAsset, PARENT NULLS FIRST, assetnum
;
When you see how fast this executes, you'll never want to use the IBM report
again. If you don't shove your computed costs in eq5 like I do, then just
replace NVL(eq5,0) in asset_data with NVL(totalcost,0) and watch the result.
I hacked the hell out of these analytic functions to make magic, so in 2018
my mission is to really understand just what it is I created!
4) Hey, did you ever get this question: "What are all the items I use for
each asset? Oh and can I have it all on one line?"
Yeah, me too! This one uses LISTAGGs, regular expression replacements (and
some implicit counting), and more of those data warehouse techniques I
barely understand. Once I put it all together though, it was so cool.
SELECT DISTINCT siteid, assetnum, (1 + LENGTH(itemo) -
LENGTH(REPLACE(itemo,';',''))) AS itemCnt,
itemo
FROM (
SELECT siteid, assetnum, regexp_replace((LISTAGG(itemnum,';') WITHIN GROUP
(ORDER BY itemnum)), '([^;]+)(;\1)*(;|$)', '\1\3') AS itemo
FROM
((SELECT DISTINCT siteid, assetnum, itemnum FROM matusetrans WHERE issuetype
= 'ISSUE' AND itemnum IS NOT NULL AND assetnum IS NOT NULL
AND transdate > ('01-JAN-'||(EXTRACT(YEAR FROM SYSDATE) - 3)) )
MATCH_RECOGNIZE (
PARTITION BY assetnum
ORDER BY itemnum
MEASURES match_number() AS mho
ALL ROWS PER MATCH
AFTER MATCH SKIP PAST LAST ROW
PATTERN (S B*)
DEFINE B AS LENGTHB(S.itemnum) + SUM(LENGTHB(B.itemnum) + LENGTHB(';')) <=
2500)
) h
GROUP BY siteid, assetnum, mho
) f
ORDER BY 1,3 DESC,2
;
I hope this helps some of you out there. It feels a little lonely here, but
I guess everyone is on IBM's forums or polishing their reputations on
LinkedIn. Can't blame any of ya, this mailing list was started in 1999 and
I'm frankly amazed anyone still reads it. I'm still thankful for all of you
people contributing and reading this over the years. It's done some good,
and maybe there's still more good left to do.
If I don't talk to you in the next week, Happy New Year too!
-C
> Hey Chris! I'm still here ...
I'm glad to see!
> Question: are you using your Asset cost calculations as an alternative to
> the DB rollup?
Yup. I figured out what it was doing, and why, and decided to roll my own. I saw missing transactions for assets that bypassed workorders, and in a very few cases, saw workorder totals without matching receipts (!) So I reasoned that if a transaction was missing, the charge was not "provable" and so could not be considered.
The more I thought about that the better I felt about rolling my own. The cost summary from WOTRACK is a very good matrix but I think it can be wrong. Gathering the transactions and accounting for the details is traceable and auditable, so I feel the picture is more accurate and precise. Let me know if you use it and if it works for you.
Note that these are all Oracle-derived, so if I'm using analytic functions you have to translate them. I try to stick to ANSI-SQL as much as possible but like I mentioned, I want my db engine to do the work.
-C
Thanks - I'll let you know. One of my clients may be interested.
BTW: I know Pam Denny has initiated an RFE to eliminate the DB Cost rollup
and replace it with something more efficient. I don't know how far that
initiative has gone though, since I haven't checked the RFE list lately.
Shannon
From: MAXIMO@yahoogroups.com [mailto:MAXIMO@yahoogroups.com]
Sent: Wednesday, December 27, 2017 11:38 AM
To: MAXIMO@yahoogroups.com
Subject: RE: [MAXIMO List] Some late XMas gifts
> Hey Chris! I'm still here ...
I'm glad to see!
> Question: are you using your Asset cost calculations as an alternative to
> the DB rollup?
Yup. I figured out what it was doing, and why, and decided to roll my own. I
saw missing transactions for assets that bypassed workorders, and in a very
few cases, saw workorder totals without matching receipts (!) So I reasoned
that if a transaction was missing, the charge was not "provable" and so
could not be considered.
The more I thought about that the better I felt about rolling my own. The
cost summary from WOTRACK is a very good matrix but I think it can be wrong.
Gathering the transactions and accounting for the details is traceable and
auditable, so I feel the picture is more accurate and precise. Let me know
if you use it and if it works for you.
Note that these are all Oracle-derived, so if I'm using analytic functions
you have to translate them. I try to stick to ANSI-SQL as much as possible
but like I mentioned, I want my db engine to do the work.
-C