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.
I'm curious how many of you use the out of the box BIRT reports for conducting cycle counts on inventory or if you have created a custom report for your site.
I'm looking at revamping our process for monthly cycle counting and have run into a few surprises in our custom report.
HI,
We don't use the reports out of the box.
We have saved queries that the users run then export the list to Excel.
This one in SQL Server finds the cycle counts they have to perform in the next five days to stay on schedule. Target is to count all A items twice a year, all B once a year and all C once every two years.
If you divide the top(x) by five that gives you counts due each day.
It's a rolling count so if you do some of the counts today then run the query tomorrow you get all of the ones you did not finish plus more added into each type.
(status != 'OBSOLETE' and siteid = (select defsite from maxuser where userid = :USER)) and (inventoryid in
((select top(40)
v.inventoryid
from inventory v
join invbalances b on b.itemnum = v.itemnum and b.siteid = v.siteid
where v.siteid = (select defsite from maxuser where userid = :USER)
and v.abctype = 'A'
and (isnull(b.physcntdate,'1/1/1800') < getdate()-150)
order by physcntdate)
union
(select top(50)
v.inventoryid
from inventory v
join invbalances b on b.itemnum = v.itemnum and b.siteid = v.siteid
where v.siteid = (select defsite from maxuser where userid = :USER)
and v.abctype = 'B'
and (isnull(b.physcntdate,'1/1/1800') < getdate()-330)
order by physcntdate)
union
(select top(1.5) percent
v.inventoryid
from inventory v
join invbalances b on b.itemnum = v.itemnum and b.siteid = v.siteid
where v.siteid = (select defsite from maxuser where userid = :USER)
and v.abctype = 'C'
and (isnull(b.physcntdate,'1/1/1800') < getdate()-510)
order by physcntdate)))
We have a custom table we added to calculate the cycle counts and a stored procedure to update the ABC TYPE for inventory. It's listed below and I run it once a year.
Dave Bone
Ocean Spray Cranberries
USE [maxprod]
GO
/****** Object: StoredProcedure [dbo].[OSC_ABCTypeUpdate] Script Date: 09/19/2011 15:29:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[OSC_ABCTypeUpdate]
AS
BEGIN
SET NOCOUNT ON;
Declare
@inventoryid as integer,
@siteid as varchar (8),
@itemnum as varchar (60),
@cost as decimal(15,2),
@quantity as decimal(15,2),
@reccount as integer,
@ABCtype as varchar(8),
@ABCtypeOLD as varchar(8),
@category as varchar(8),
@status as varchar(12),
@curbaltotal as decimal(15,2),
@critical as smallint
/*Use the drop and create table only if needed to modify the table*/
--DROP TABLE OSC_ABCstack
--
--CREATE TABLE OSC_ABCstack (inventoryid int,
-- siteid varchar(8),
-- itemnum varchar(60),
-- cost decimal(15,2),
-- quantity decimal(15,2),
-- reccount int,
-- ABCtype varchar(8),
-- ABCtypeOLD varchar(8),
-- category varchar(8),
-- status varchar(12),
-- curbaltotal decimal(15,2),
-- critical smallint)
--
--CREATE UNIQUE NONCLUSTERED INDEX [abcstackindx1] ON [dbo].[OSC_ABCstack]
--([inventoryid] ASC)WITH (PAD_INDEX = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF,
--IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
--
--CREATE UNIQUE CLUSTERED INDEX [abcstackindx2] ON [dbo].[OSC_ABCstack]
--([inventoryid] ASC,[siteid] ASC)WITH (PAD_INDEX = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF,
--IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
/*Clear out the old data*/
Delete from OSC_ABCstack
/*Get the current data and put it in OSC_ABCstack*/
DECLARE InsertABCstackInventory CURSOR FOR
select v.inventoryid, v.siteid, v.itemnum, v.abctype as ABCtypeOLD, v.category, v.status, v.critical, sum(IsNull(curbal,0)) as curbaltotal
from inventory v
left join invbalances b on b.itemnum = v.itemnum and b.siteid = v.siteid
group by v.inventoryid, v.siteid, v.itemnum, v.abctype , v.category, v.status, v.critical
order by v.inventoryid
OPEN InsertABCstackInventory
FETCH next from InsertABCstackInventory
INTO @inventoryid, @siteid, @itemnum, @ABCtypeOLD, @category, @status, @critical, @curbaltotal
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO OSC_ABCstack (inventoryid,siteid,itemnum,ABCtypeOLD,category,status,critical,curbaltotal)
VALUES (@inventoryid, @siteid, @itemnum, @ABCtypeOLD, @category, @status, @critical, @curbaltotal)
FETCH next from InsertABCstackInventory
INTO @inventoryid, @siteid, @itemnum, @ABCtypeOLD, @category, @status, @critical, @curbaltotal
END
CLOSE InsertABCstackInventory
DEALLOCATE InsertABCstackInventory
/*Update all costs and counts to 0 so we can use them to add upon themselves*/
/*Update OSC_ABCstack so abctype field is null for all records*/
update OSC_ABCstack
set cost = 0,
quantity = 0,
reccount = 0,
ABCtype = null
/*Get all of the Matusetrans for only Stocked items and update OSC_ABCstack*/
DECLARE ABCstackMatuseUpdate CURSOR FOR
select v.inventoryid, sum(((Isnull(m.quantity,0))*-1)*(IsNull(m.unitcost,0))) as cost, sum((IsNull(m.quantity,0))*-1) as quantity, count(*) as reccount
from inventory v
join matusetrans m on v.itemnum = m.itemnum and v.siteid = m.siteid
where v.status <> 'OBSOLETE'
and (m.issuetype = 'ISSUE' or m.issuetype = 'RETURN')
and (m.transdate > getdate()-360)
and v.category in
(select value
from synonymdomain
where domainid = 'CATEGORY'
and maxvalue = 'STK')
group by v.inventoryid
order by v.inventoryid
OPEN ABCstackMatuseUpdate
FETCH next from ABCstackMatuseUpdate
INTO @inventoryid, @cost, @quantity, @reccount
WHILE @@FETCH_STATUS = 0
BEGIN
Update OSC_ABCstack
set cost = cost + @cost,
quantity = quantity + @quantity,
reccount = reccount + @reccount
Where inventoryid = @inventoryid
FETCH next from ABCstackMatuseUpdate
INTO @inventoryid, @cost, @quantity, @reccount
END
CLOSE ABCstackMatuseUpdate
DEALLOCATE ABCstackMatuseUpdate
/*Get all of the Invtrans for only Stocked items and update OSC_ABCstack*/
DECLARE ABCstackInvTransUpdate CURSOR FOR
select v.inventoryid, sum((IsNull(m.linecost,0))*-1) as cost, sum((IsNull(m.quantity,0))*-1) as quantity, count(*) as reccount
from inventory v
join invtrans m on v.itemnum = m.itemnum and v.siteid = m.siteid
where v.status <> 'OBSOLETE'
and (m.transtype = 'CURBALADJ' or m.transtype = 'RECBALADJ')
and (m.transdate > getdate()-360)
and v.category in
(select value
from synonymdomain
where domainid = 'CATEGORY'
and maxvalue = 'STK')
group by v.inventoryid, v.itemnum
order by v.inventoryid
OPEN ABCstackInvTransUpdate
FETCH next from ABCstackInvTransUpdate
INTO @inventoryid, @cost, @quantity, @reccount
WHILE @@FETCH_STATUS = 0
BEGIN
Update OSC_ABCstack
set cost = cost + @cost,
quantity = quantity + @quantity,
reccount = reccount + @reccount
Where inventoryid = @inventoryid
FETCH next from ABCstackInvTransUpdate
INTO @inventoryid, @cost, @quantity, @reccount
END
CLOSE ABCstackInvTransUpdate
DEALLOCATE ABCstackInvTransUpdate
/*Get all of the MatRectrans for only Stocked items and update OSC_ABCstack*/
DECLARE ABCstackMatRecUpdate CURSOR FOR
select v.inventoryid, sum((IsNull(m.linecost,0))) as cost, sum((IsNull(m.quantity,0))) as quantity, count(*) as reccount
from inventory v
join matrectrans m on v.itemnum = m.itemnum and v.siteid = m.siteid
where v.status <> 'OBSOLETE'
and (m.issuetype = 'RECEIPT')
and (m.transdate > getdate()-360)
and v.category in
(select value
from synonymdomain
where domainid = 'CATEGORY'
and maxvalue = 'STK')
group by v.inventoryid
order by v.inventoryid
OPEN ABCstackMatRecUpdate
FETCH next from ABCstackMatRecUpdate
INTO @inventoryid, @cost, @quantity, @reccount
WHILE @@FETCH_STATUS = 0
BEGIN
Update OSC_ABCstack
set cost = cost + @cost,
quantity = quantity + @quantity,
reccount = reccount + @reccount
Where inventoryid = @inventoryid
FETCH next from ABCstackMatRecUpdate
INTO @inventoryid, @cost, @quantity, @reccount
END
CLOSE ABCstackMatRecUpdate
DEALLOCATE ABCstackMatRecUpdate
/*Update OSC_ABCstack so abctype field is A for all critical records*/
update OSC_ABCstack
set ABCtype = 'A'
where critical = 1
/*Update OSC_ABCstack so abctype field is S where category is some type of obsolete*/
update OSC_ABCstack
set ABCtype = 'S'
where abctype is null
and (category in
('NSOB','SPOB','OBS')
or status = 'OBSOLETE')
/*Update OSC_ABCstack so abctype field is N where category is NS or SP with no curbal*/
update OSC_ABCstack
set ABCtype = 'N'
where abctype is null
and curbaltotal <=0
and (category in
('NS','SP')
or status = 'OBSOLETE')
/*Update OSC_ABCstack so abctype field is new value for all records where abctype is still null*/
DECLARE ABCstackABCTypeUpdate CURSOR FOR
select distinct siteid from site
OPEN ABCstackABCTypeUpdate
FETCH next from ABCstackABCTypeUpdate
INTO @siteid
WHILE @@FETCH_STATUS = 0
BEGIN
/*Update OSC_ABCstack so abctype field is A for all records where abctype is still null and they are in top 1000 by cost*/
update OSC_ABCstack
set ABCtype = 'A'
where ABCtype is null
and siteid = @siteid
and inventoryid in
(select TOP (1000) inventoryid
from OSC_ABCstack
where siteid = @siteid
and ABCtype is null
order by cost desc)
/*Update OSC_ABCstack so abctype field is B for the next 2500 records where abctype is still null and they are in top 2500 by cost*/
update OSC_ABCstack
set ABCtype = 'B'
where ABCtype is null
and siteid = @siteid
and inventoryid in
(select TOP (2500) inventoryid
from OSC_ABCstack
where siteid = @siteid
and ABCtype is null
order by cost desc)
/*Update OSC_ABCstack so abctype field is C for records where abctype is still null*/
update OSC_ABCstack
set ABCtype = 'C'
where ABCtype is null
and siteid = @siteid
FETCH next from ABCstackABCTypeUpdate
INTO @siteid
END
CLOSE ABCstackABCTypeUpdate
DEALLOCATE ABCstackABCTypeUpdate
/*Update inventory set abctype to null*/
update inventory
set abctype = null
/*Update inventory where category is some type of obsolete*/
update inventory
set abctype = 'S'
where category in
('NSOB','SPOB','OBS')
or status = 'OBSOLETE'
/*Update inventory where critical is 1 and abctype is null*/
update inventory
set abctype = 'A',
ccf = '180'
where abctype is null
and critical = 1
and category not in
('NSOB','SPOB','OBS')
and status <> 'OBSOLETE'
/*Update inventory to new abctype where abctype is still null*/
update inventory
set abctype = a.abctype
from inventory v
join OSC_ABCstack a on a.inventoryid = v.inventoryid
where v.abctype is null
/*Update inventory to new abc count frequecny where abctype is still null*/
update inventory
set ccf = 180
where abctype = 'A'
update inventory
set ccf = 360
where abctype = 'B'
update inventory
set ccf = 540
where abctype = 'C'
END
--- In MAXIMO@yahoogroups.com, "Jason Verly" <jason.verly@...> wrote:
>
> I'm curious how many of you use the out of the box BIRT reports for conducting cycle counts on inventory or if you have created a custom report for your site.
>
> I'm looking at revamping our process for monthly cycle counting and have run into a few surprises in our custom report.
>