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.
Your finance director asks you for a simple KPI, "List the physical inventory cycle count by each site/plant, and when it was taken".
So you explain that the entries are by date, and would a sum of the month be okay? Assume it is. So, just for a sanity check, you run this:
SELECT COUNT(*) invBalCNT, EXTRACT (YEAR FROM physcntdate) thisYear, siteid
FROM invbalances
WHERE physcntdate IS NOT NULL
AND physcntdate > '01-JAN-2015'
GROUP BY siteid, EXTRACT (YEAR FROM physcntdate)
ORDER BY siteid
And you get 24 rows of various numbers, some as low as 1 (!), some as high as 6900 (out of how many, you may ask, but later, later).
Because you are slightly clever creature, you check transactions:
SELECT COUNT(*), EXTRACT (YEAR FROM transdate) theYear, siteid
FROM invtrans
WHERE invtrans.transtype = 'PCOUNTADJ'
AND invtrans.transdate > '01-JAN-2015'
GROUP BY siteid, EXTRACT (YEAR FROM transdate)
ORDER BY siteid
And you get 17 rows (missing 7 !!! ) with numbers as low as 1 (and common as well) with a max of 10570.
What to make of this? Any guesses? I'm not sure which one is more correct. Maybe I should sum and divide by two?
-C
I guess it comes down to: is he looking for how many times the item was counted, or when it was last counted?
Shannon
Sent from my wonderful BlackBerry Z30 smartphone!
Original Message
From: maximal@wanko.com [MAXIMO]
Sent: Friday, November 13, 2015 10:35 AM
To: MAXIMO@yahoogroups.com
Reply To: MAXIMO@yahoogroups.com
Subject: [MAXIMO List] So which one is correct?
Your finance director asks you for a simple KPI, "List the physical inventory cycle count by each site/plant, and when it was taken".
So you explain that the entries are by date, and would a sum of the month be okay? Assume it is. So, just for a sanity check, you run this:
SELECT COUNT(*) invBalCNT, EXTRACT (YEAR FROM physcntdate) thisYear, siteid
FROM invbalances
WHERE physcntdate IS NOT NULL
AND physcntdate > '01-JAN-2015'
GROUP BY siteid, EXTRACT (YEAR FROM physcntdate)
ORDER BY siteid
And you get 24 rows of various numbers, some as low as 1 (!), some as high as 6900 (out of how many, you may ask, but later, later).
Because you are slightly clever creature, you check transactions:
SELECT COUNT(*), EXTRACT (YEAR FROM transdate) theYear, siteid
FROM invtrans
WHERE invtrans.transtype = 'PCOUNTADJ'
AND invtrans.transdate > '01-JAN-2015'
GROUP BY siteid, EXTRACT (YEAR FROM transdate)
ORDER BY siteid
And you get 17 rows (missing 7 !!! ) with numbers as low as 1 (and common as well) with a max of 10570.
What to make of this? Any guesses? I'm not sure which one is more correct. Maybe I should sum and divide by two?
-C
I'd say the second query is more correct, but you need to adjust the query (add an outer join to the SITE table) to account for sites that aren't doing cycle counts. From the looks of things, you have (at least) 7 sites that are adding items to Inventory but aren't cycle counting.
Travis Herron
Good point about LOJ for sites, Travis. I'll add to make the non-reporting sites percolate to the top.
Shannon, the first seems to catch all physical counts, irrespective of reconciliation. I think we're looking for how many ABC items were counted in the year, and perhaps a look at how often As are being counted. Right now, not often and not on schedule, not even close.
It's long, but should be readable. Should also run on any Maximo implementation.
SELECT siteid,
CASE WHEN pA < 1 THEN 0
ELSE ROUND(1-(pA / Alla),3) * 100
END aaa,
CASE WHEN pB < 1 THEN 0
ELSE ROUND(1-(pB / Allb),3) * 100
END bbb,
CASE WHEN pC < 1 THEN 0
ELSE ROUND(1-(pC / Allc),3) * 100
END ccc
FROM
(SELECT squa.siteid siteid, SUM(squa.quA) Alla, SUM(squa.quB) ALLb, SUM(squa.quC) ALLc, SUM(squb.quA) pA, SUM(squb.quB) pB, SUM(squb.quC) pC
FROM
(SELECT iv.siteid, COUNT(iv.itemnum) quA, 0 quB, 0 quC
FROM inventory iv
WHERE iv.abctype = 'A'
GROUP BY iv.siteid
UNION
SELECT iv.siteid, 0 quA, COUNT(iv.itemnum) quB, 0 quC
FROM inventory iv
WHERE iv.abctype = 'B'
GROUP BY iv.siteid
UNION
SELECT iv.siteid, 0 quA, 0 quB, COUNT(iv.itemnum) quC
FROM inventory iv
WHERE iv.abctype = 'C'
GROUP BY iv.siteid) squa
JOIN
(SELECT iv.siteid, COUNT(iv.itemnum) quA, 0 quB, 0 quC
FROM inventory iv
JOIN invbalances ib ON ib.siteid = ib.siteid AND ib.location = iv.location AND ib.itemnum = iv.itemnum
WHERE ib.physcntdate IS NOT NULL
AND ib.physcntdate > '01-JAN-2015'
AND iv.abctype = 'A'
GROUP BY iv.siteid
UNION
SELECT iv.siteid, 0 quA, COUNT(iv.itemnum) quB, 0 quC
FROM inventory iv
JOIN invbalances ib ON ib.siteid = ib.siteid AND ib.location = iv.location AND ib.itemnum = iv.itemnum
WHERE ib.physcntdate IS NOT NULL
AND ib.physcntdate > '01-JAN-2015'
AND iv.abctype = 'B'
GROUP BY iv.siteid
UNION
SELECT iv.siteid, 0 quA, 0 quB, COUNT(iv.itemnum) quC
FROM inventory iv
JOIN invbalances ib ON ib.siteid = ib.siteid AND ib.location = iv.location AND ib.itemnum = iv.itemnum
WHERE ib.physcntdate IS NOT NULL
AND ib.physcntdate > '01-JAN-2015'
AND iv.abctype = 'C'It's long, it'
GROUP BY iv.siteid) squb ON squa.siteid = squb.siteid GROUP BY squa.siteid) clump
ORDER BY siteid
;
This might be another option for you. I am responsible for creating metrics for our storeroom teams and the below is a query I use to determine how well a site is doing with cycle counting. It uses a wrapper query to pull data from sub-queries for each of the categories. I use the query as the basis for an eSpreadsheet report that analyzes how they are doing. We also have a boolean filed in our inventory records for Critical. Those items are required to be counted as if they are A category so that is the first part of the query and you may not need that.
Combined Query for Cycle Count Check
SELECT ITEMCNT.LOCATION, ITEMCNT.ABC, ITEMCNT.ITEMS, COUNTS.CNTDITMS, ACCCHECK.ACCURATECNT, ACCCHECK.ACCURATECNT/COUNTS.CNTDITMS AS ACCURACY
FROM
(SELECT ITMCNT.LOCATION, ITMCNT.ABC, COUNT(ITMCNT.ITEMNUM) AS ITEMS
FROM
(SELECT INV.LOCATION, DECODE(TO_CHAR(INV.CRITICAL, '9'), '1', 'A',INV.ABCTYPE) AS "ABC", INV.ITEMNUM
FROM PZMAX.INVENTORY INV
WHERE (INV.SITEID=:SiteID) AND (INV.CATEGORY IN('RS','STK','RET')) OR (INV.SITEID=:SiteID) AND (INV.CRITICAL=1) AND (INV.CATEGORY IN('RS','STK','RET'))) ITMCNT
GROUP BY ITMCNT.LOCATION, ITMCNT.ABC
ORDER BY ITMCNT.LOCATION) ITEMCNT,
(SELECT COUNTED.LOCATION, COUNTED.ABC, COUNT(COUNTED.CNTDITEM) AS CNTDITMS
FROM
(SELECT I.LOCATION, DECODE(TO_CHAR(I.CRITICAL, '9'), '1', 'A',I.ABCTYPE) AS "ABC", T1.ITEMNUM AS "CNTDITEM"
FROM PZMAX.INVTRANS T1, PZMAX.INVENTORY I
WHERE T1.ITEMNUM=I.ITEMNUM AND T1.STORELOC=I.LOCATION AND T1.SITEID=I.SITEID AND T1.BINNUM=I.BINNUM AND ((T1.TRANSTYPE='PCOUNTADJ') AND (T1.SITEID=:SiteID) AND (I.CATEGORY IN('RS','STK','RET'))) AND T1.TRANSDATE = (SELECT MIN(T2.TRANSDATE) FROM PZMAX.INVTRANS T2 WHERE T1.ITEMNUM = T2.ITEMNUM AND T1.SITEID = T2.SITEID AND T1.STORELOC=T2.STORELOC AND T1.BINNUM=T2.BINNUM AND (T2.TRANSDATE>=:startDate and T2.TRANSDATE<=:endDate))) COUNTED
GROUP BY COUNTED.LOCATION, COUNTED.ABC) COUNTS,
(SELECT ACCURATE.LOCATION, ACCURATE.ABC, COUNT(ACCURATE.ITEMNUM) AS ACCURATECNT
FROM
(SELECT I.LOCATION, DECODE(TO_CHAR(I.CRITICAL, '9'), '1', 'A',I.ABCTYPE) AS "ABC", T1.ITEMNUM
FROM PZMAX.INVTRANS T1, PZMAX.INVENTORY I
WHERE T1.ITEMNUM=I.ITEMNUM AND T1.STORELOC=I.LOCATION AND T1.SITEID=I.SITEID AND T1.BINNUM=I.BINNUM AND ((T1.TRANSTYPE='PCOUNTADJ') AND (T1.SITEID=:SiteID) AND (I.CATEGORY IN('RS','STK','RET'))) AND T1.TRANSDATE = (SELECT MIN(T2.TRANSDATE) FROM PZMAX.INVTRANS T2 WHERE T1.ITEMNUM = T2.ITEMNUM AND T1.SITEID = T2.SITEID AND T1.STORELOC=T2.STORELOC AND T1.BINNUM=T2.BINNUM AND (T2.TRANSDATE>=:startDate and T2.TRANSDATE<=:endDate)) AND (T1.CURBAL= T1.PHYSCNT)) ACCURATE
GROUP BY ACCURATE.LOCATION, ACCURATE.ABC) ACCCHECK
WHERE ITEMCNT.LOCATION = COUNTS.LOCATION(+) AND ITEMCNT.ABC = COUNTS.ABC(+)
AND ITEMCNT.LOCATION = ACCCHECK.LOCATION(+) AND ITEMCNT.ABC = ACCCHECK.ABC(+)
Old-school outer joins, man... this is generous, thank you.
My approach is this.
I have an old store proc I wrote for another system that I'm in the process of updating. It basically creates a static crosstab table for inserts of statistics each month, and I insert a site/year/month row from system inception to about 20 years out. Then all I'm doing is a series of updates in a procedure, one for every site. Once you get the loop right it's super quick, and this is basically what you do when the company doesn't have a reporting database for you.
I also decided to use invbalances instead of invtrans. I don't care about manual adjustments (yet) and invbalances is going to capture all posted physical counts, and whether it was reconciled or not. If I see a tremendous amount of discrepancies I'll just true-up invbalances from invtrans, Unless someone sees a gross error there..?
I'll post my solution when it's done.
-C
---In MAXIMO@yahoogroups.com, <david_teece@cargill.com> wrote :
This might be another option for you. I am responsible for creating metrics for our storeroom teams and the below is a query I use to determine how well a site is doing with cycle counting. It uses a wrapper query to pull data from sub-queries for each of the categories. I use the query as the basis for an eSpreadsheet report that analyzes how they are doing. We also have a boolean filed in our inventory records for Critical. Those items are required to be counted as if they are A category so that is the first part of the query and you may not need that.
Combined Query for Cycle Count Check
SELECT ITEMCNT.LOCATION, ITEMCNT.ABC, ITEMCNT.ITEMS, COUNTS.CNTDITMS, ACCCHECK.ACCURATECNT, ACCCHECK.ACCURATECNT/COUNTS.CNTDITMS AS ACCURACY
FROM
(SELECT ITMCNT.LOCATION, ITMCNT.ABC, COUNT(ITMCNT.ITEMNUM) AS ITEMS
FROM
(SELECT INV.LOCATION, DECODE(TO_CHAR(INV.CRITICAL, '9'), '1', 'A',INV.ABCTYPE) AS "ABC", INV.ITEMNUM
FROM PZMAX.INVENTORY INV
WHERE (INV.SITEID=:SiteID) AND (INV.CATEGORY IN('RS','STK','RET')) OR (INV.SITEID=:SiteID) AND (INV.CRITICAL=1) AND (INV.CATEGORY IN('RS','STK','RET'))) ITMCNT
GROUP BY ITMCNT.LOCATION, ITMCNT.ABC
ORDER BY ITMCNT.LOCATION) ITEMCNT,
(SELECT COUNTED.LOCATION, COUNTED.ABC, COUNT(COUNTED.CNTDITEM) AS CNTDITMS
FROM
(SELECT I.LOCATION, DECODE(TO_CHAR(I.CRITICAL, '9'), '1', 'A',I.ABCTYPE) AS "ABC", T1.ITEMNUM AS "CNTDITEM"
FROM PZMAX.INVTRANS T1, PZMAX.INVENTORY I
WHERE T1.ITEMNUM=I.ITEMNUM AND T1.STORELOC=I.LOCATION AND T1.SITEID=I.SITEID AND T1.BINNUM=I.BINNUM AND ((T1.TRANSTYPE='PCOUNTADJ') AND (T1.SITEID=:SiteID) AND (I.CATEGORY IN('RS','STK','RET'))) AND T1.TRANSDATE = (SELECT MIN(T2.TRANSDATE) FROM PZMAX.INVTRANS T2 WHERE T1.ITEMNUM = T2.ITEMNUM AND T1.SITEID = T2.SITEID AND T1.STORELOC=T2.STORELOC AND T1.BINNUM=T2.BINNUM AND (T2.TRANSDATE>=:startDate and T2.TRANSDATE<=:endDate))) COUNTED
GROUP BY COUNTED.LOCATION, COUNTED.ABC) COUNTS,
(SELECT ACCURATE.LOCATION, ACCURATE.ABC, COUNT(ACCURATE.ITEMNUM) AS ACCURATECNT
FROM
(SELECT I.LOCATION, DECODE(TO_CHAR(I.CRITICAL, '9'), '1', 'A',I.ABCTYPE) AS "ABC", T1.ITEMNUM
FROM PZMAX.INVTRANS T1, PZMAX.INVENTORY I
WHERE T1.ITEMNUM=I.ITEMNUM AND T1.STORELOC=I.LOCATION AND T1.SITEID=I.SITEID AND T1.BINNUM=I.BINNUM AND ((T1.TRANSTYPE='PCOUNTADJ') AND (T1.SITEID=:SiteID) AND (I.CATEGORY IN('RS','STK','RET'))) AND T1.TRANSDATE = (SELECT MIN(T2.TRANSDATE) FROM PZMAX.INVTRANS T2 WHERE T1.ITEMNUM = T2.ITEMNUM AND T1.SITEID = T2.SITEID AND T1.STORELOC=T2.STORELOC AND T1.BINNUM=T2.BINNUM AND (T2.TRANSDATE>=:startDate and T2.TRANSDATE<=:endDate)) AND (T1.CURBAL= T1.PHYSCNT)) ACCURATE
GROUP BY ACCURATE.LOCATION, ACCURATE.ABC) ACCCHECK
WHERE ITEMCNT.LOCATION = COUNTS.LOCATION(+) AND ITEMCNT.ABC = COUNTS.ABC(+)
AND ITEMCNT.LOCATION = ACCCHECK.LOCATION(+) AND ITEMCNT.ABC = ACCCHECK.ABC(+)