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.
This was raised on linkedin i.e. someone was having performance issues when running re-order
As it seems to come up regularly I thought I'd post my solution
I normally run a select which brings back only records where a re-order is likely to happen then run re-order.
The site I'm on doesn't use purchasing in Maximo so I created the following. I have done a couple of rudimentary tests and it seems ok but anybody deploying it should do their own due diligence.
If anyone has a better example i.e. include approved PR's PO's etc etc please post
The follow was run on 7.5.0.3 sql server therefore it includes fields like inventory.reorder and inventory.STAGEDCURBAL which I don't think was in 6 and 7.1
select * from inventory where exists ( SELECT null
FROM Inventory a JOIN
(SELECT sum(curbal+STAGEDCURBAL) as sumcurbal,itemnum,itemsetid,location FROM invbalances
group by itemnum,itemsetid,location) tt
ON a.itemnum = tt.itemnum and a.itemsetid = tt.itemsetid and a.MINLEVEL >= tt.sumcurbal
and a.location = tt.location
where a.reorder = 1 and inventory.itemnum = tt.itemnum and inventory.itemsetid = tt.itemsetid
and inventory.location = tt.location)
the following is used as the where clause in inventory
exists ( SELECT null
FROM Inventory a JOIN
(SELECT sum(curbal+STAGEDCURBAL) as sumcurbal,itemnum,itemsetid,location FROM invbalances
group by itemnum,itemsetid,location) tt
ON a.itemnum = tt.itemnum and a.itemsetid = tt.itemsetid and a.MINLEVEL >= tt.sumcurbal
and a.location = tt.location
where a.reorder = 1 and inventory.itemnum = tt.itemnum and inventory.itemsetid = tt.itemsetid
and inventory.location = tt.location)
Rgds Ian
GDF SUEZ E&P UK Ltd (Company Number 3386464), registered in England and Wales with a registered office address at: 40 Holborn Viaduct, London, EC1N 2PB.
**************************************************************************************************************
This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed.
If you have received this email in error please notify the system manager.
**************************************************************************************************************
I used the same approach for a Maximo7.1 SqlServer
to reduce Reorder execution time (reduced from 1h to 2 mn).
select * from inventory where
location='YOUR_STOREROOM'
and siteid='YOUR_SITE_ID'
AND (
minlevel +1 -
(select SUM(curbal) from invbalances
where itemnum = inventory.itemnum
and itemsetid = inventory.itemsetid
and location = inventory.location
and siteid='YOUR_SITE_ID')
+
(select SUM(reservedqty) from invreserve
where orgid='YOUR_ORG_ID' and storelocsiteid='YOUR_SITE_ID'
and itemnum = inventory.itemnum
and itemsetid=inventory.itemsetid
and location = inventory.location
and directreq = 0
and ((requireddate - inventory.deliverytime) <= getdate() or (requireddate
is null and (inventory.deliverytime - 7) >= 0)))
-
(select sum(orderqty * conversion ) from prline, pr
where pr.siteid=prline.siteid
and pr.prnum=prline.prnum
and prline.siteid='YOUR_SITE_ID'
and prline.itemnum=inventory.itemnum
and prline.itemsetid = inventory.itemsetid and prline.storeloc =
'YOUR_STOREROOM'
and prline.issue = 0 and prline.ponum is null
and pr.status in (select value from synonymdomain where domainid
='prstatus' AND maxvalue in ('APPR','WAPPR'))
and pr.historyflag=0)
-
(select sum(orderqty - ISNULL(receivedqty, 0) * ISNULL(conversion, 1))
from poline, po
where po.orgid=poline.orgid
and po.ponum=poline.ponum
and poline.orgid='YOUR_ORG_ID'
and poline.tositeid='YOUR_SITE_ID'
and poline.itemnum=inventory.itemnum
and poline.itemsetid=inventory.itemsetid
and poline.storeloc = 'YOUR_STOREROOM'
and poline.issue = 0
and (poline.orderqty > poline.receivedqty or poline.receivedqty is null)
and po.potype in (select value from synonymdomain where domainid ='POTYPE'
AND maxvalue in ('STD','REL','CHG'))
and po.status in (select value from synonymdomain where domainid
='POstatus' AND maxvalue in ('APPR','INPRG','WAPPR'))
and po.historyflag=0
and poline.receiptscomplete=0)
)>0
On Fri, Oct 19, 2012 at 9:25 AM, Ian Wright <ian.wright@gdfsuezep.co.uk>wrote:
> **
>
>
> This was raised on linkedin i.e. someone was having performance issues
> when running re-order
> As it seems to come up regularly I thought I'd post my solution
>
> I normally run a select which brings back only records where a re-order is
> likely to happen then run re-order.
> The site I'm on doesn't use purchasing in Maximo so I created the
> following. I have done a couple of rudimentary tests and it seems ok but
> anybody deploying it should do their own due diligence.
>
> If anyone has a better example i.e. include approved PR's PO's etc etc
> please post
>
> The follow was run on 7.5.0.3 sql server therefore it includes fields like
> inventory.reorder and inventory.STAGEDCURBAL which I don't think was in 6
> and 7.1
>
> select * from inventory where exists ( SELECT null
> FROM Inventory a JOIN
> (SELECT sum(curbal+STAGEDCURBAL) as sumcurbal,itemnum,itemsetid,location
> FROM invbalances
> group by itemnum,itemsetid,location) tt
> ON a.itemnum = tt.itemnum and a.itemsetid = tt.itemsetid and a.MINLEVEL >=
> tt.sumcurbal
> and a.location = tt.location
> where a.reorder = 1 and inventory.itemnum = tt.itemnum and
> inventory.itemsetid = tt.itemsetid
> and inventory.location = tt.location)
>
> the following is used as the where clause in inventory
>
> exists ( SELECT null
> FROM Inventory a JOIN
> (SELECT sum(curbal+STAGEDCURBAL) as sumcurbal,itemnum,itemsetid,location
> FROM invbalances
> group by itemnum,itemsetid,location) tt
> ON a.itemnum = tt.itemnum and a.itemsetid = tt.itemsetid and a.MINLEVEL >=
> tt.sumcurbal
> and a.location = tt.location
> where a.reorder = 1 and inventory.itemnum = tt.itemnum and
> inventory.itemsetid = tt.itemsetid
> and inventory.location = tt.location)
>
> Rgds Ian
>
> GDF SUEZ E&P UK Ltd (Company Number 3386464), registered in England and
> Wales with a registered office address at: 40 Holborn Viaduct, London, EC1N
> 2PB.
>
>
> **************************************************************************************************************
> This email and any files transmitted with it are confidential and intended
> solely for the use of the individual or entity to whom they are addressed.
> If you have received this email in error please notify the system manager.
>
> **************************************************************************************************************
>
>
>
>
>
--
Mohamed Skandaji, MBA, PMP
All,
I have added a Request for Enhancement on IBM's web site to do the following:
***********************
We need the ability to revise an invoice to add additional costs, preferably to either the same vendor or different, potentially multiple times, that will then create transactions for the difference to the original invoice, applying that difference to items in inventory if applicable.
Many times we use freight carriers that are different vendors from the order vendor and we want those costs included in the inventory value. Also, we get many service or other charges like pallet charges, handling, customs, etc. that we need to pay but still want to capture as part of the final loaded cost of the product.
************************
If you would please go to the RFE site and vote for this enhancement, we would very much appreciate it.
http://www.ibm.com/developerworks/rfe/execute?use_case=viewRfe&CR_ID=27921
Thank you,
Ron Bryant
IT Business Consultant
Plant Systems & Controls
Cargill
I'll definitely vote for that one - this has been a long-standing issue
going back to at least (in my experience) version 4. There has never been
any good solution to it.
Thanks for bringing it up!
Shannon Rotz
From: MAXIMO@yahoogroups.com [mailto:MAXIMO@yahoogroups.com] On Behalf Of
ron_bryant@cargill.com
Sent: October-26-12 11:10 AM
To: MAXIMO@yahoogroups.com
Subject: [MAXIMO List] Additional Costs at the invoice level
All,
I have added a Request for Enhancement on IBM's web site to do the
following:
***********************
We need the ability to revise an invoice to add additional costs, preferably
to either the same vendor or different, potentially multiple times, that
will then create transactions for the difference to the original invoice,
applying that difference to items in inventory if applicable.
Many times we use freight carriers that are different vendors from the order
vendor and we want those costs included in the inventory value. Also, we get
many service or other charges like pallet charges, handling, customs, etc.
that we need to pay but still want to capture as part of the final loaded
cost of the product.
************************
If you would please go to the RFE site and vote for this enhancement, we
would very much appreciate it.
http://www.ibm.com/developerworks/rfe/execute?use_case=viewRfe
<http://www.ibm.com/developerworks/rfe/execute?use_case=viewRfe&CR_ID=27921>
&CR_ID=27921
Thank you,
Ron Bryant
IT Business Consultant
Plant Systems & Controls
Cargill
Took me a while, Ron, but I just remembered to vote for this one. This
comes up all the time for me, and there's never a good solution.
Thanks for raising it - here's hoping they accept it.
Shannon Rotz
From: MAXIMO@yahoogroups.com [mailto:MAXIMO@yahoogroups.com] On Behalf Of
ron_bryant@cargill.com
Sent: October-26-12 11:10 AM
To: MAXIMO@yahoogroups.com
Subject: [MAXIMO List] Additional Costs at the invoice level
All,
I have added a Request for Enhancement on IBM's web site to do the
following:
***********************
We need the ability to revise an invoice to add additional costs, preferably
to either the same vendor or different, potentially multiple times, that
will then create transactions for the difference to the original invoice,
applying that difference to items in inventory if applicable.
Many times we use freight carriers that are different vendors from the order
vendor and we want those costs included in the inventory value. Also, we get
many service or other charges like pallet charges, handling, customs, etc.
that we need to pay but still want to capture as part of the final loaded
cost of the product.
************************
If you would please go to the RFE site and vote for this enhancement, we
would very much appreciate it.
http://www.ibm.com/developerworks/rfe/execute?use_case=viewRfe
<http://www.ibm.com/developerworks/rfe/execute?use_case=viewRfe&CR_ID=27921>
&CR_ID=27921
Thank you,
Ron Bryant
IT Business Consultant
Plant Systems & Controls
Cargill