Imagine having both an ERP and a warehouse management sytem on different platforms. Orders are registered within the ERP application and subsequently handled in the warehouse management system (WMS) How will we monitor whether products within the order will not be substituted by others (equivalent ones) because in the mean time the original product became obsolete or out of stock?
In a perfect set-up these problems may never occur. We did experience them.....
Going for a direct comparison in BO appeared to be a much more difficult task then imagined:
1. Only non-equal orders should be shown.
2. comparing product for product within an order failed because no reporter function could be thought of to do the trick
Finally we settled for approaching the problem the other way around. We needed an 'algoritm' to compare orders based on ONE single aspect alone.
Enter the ASCII function.
Observe the following object definition:
SUM(ASCII(SUBSTR(RPAD(ITEM,15,'A'),1,1)) +
ASCII(SUBSTR(RPAD(ITEM,15,'A'),2,1)) +
ASCII(SUBSTR(RPAD(ITEM,15,'A'),3,1)) +
ASCII(SUBSTR(RPAD(ITEM,15,'A'),4,1)) +
ASCII(SUBSTR(RPAD(ITEM,15,'A'),5,1)) +
ASCII(SUBSTR(RPAD(ITEM,15,'A'),6,1)) +
ASCII(SUBSTR(RPAD(ITEM,15,'A'),7,1)) +
ASCII(SUBSTR(RPAD(ITEM,15,'A'),8,1)) +
ASCII(SUBSTR(RPAD(ITEM,15,'A'),9,1)) +
ASCII(SUBSTR(RPAD(ITEM,15,'A'),10,1)) +
ASCII(SUBSTR(RPAD(ITEM,15,'A'),11,1)) +
ASCII(SUBSTR(RPAD(ITEM,15,'A'),12,1)) +
ASCII(SUBSTR(RPAD(ITEM,15,'A'),13,1)) +
ASCII(SUBSTR(RPAD(ITEM,15,'A'),14,1)) +
ASCII(SUBSTR(RPAD(ITEM,15,'A'),15,1))
)
In this case ITEM can be a string of 1 -15 characters.
The ASCII function does not work with spaces, so the strings are padded with an 'A'
So , 'ALV4000R' will generate 960
This is of course a meaningless figure, summing all these figures within an order gives figures like 75816.
Using 2 DP's with these objects will give specific 'fingerprint' for each order.
If the fingerprint is different you are absolutely sure that there is a difference at the product-level.
The opposite is not 100% sure, cause identical fingerprints could be caused by chance (this is however a very small chance)
The keen observer will point out that this method will fail if ALV40RO substitues ALV4000R. (same characters,same fingerprint)
Our item structure prohibts this, but adding some factors will eliminate this chance too:
SUM(ASCII(SUBSTR(RPAD(ITEM,15,'A'),1,1)) +
ASCII(SUBSTR(RPAD(ITEM,15,'A'),2,1))*2 +
ASCII(SUBSTR(RPAD(ITEM,15,'A'),3,1))*3 +
ASCII(SUBSTR(RPAD(ITEM,15,'A'),4,1))*4+
ASCII(SUBSTR(RPAD(ITEM,15,'A'),5,1))*5 +
ASCII(SUBSTR(RPAD(ITEM,15,'A'),6,1))*6 +
ASCII(SUBSTR(RPAD(ITEM,15,'A'),7,1))*7 +
ASCII(SUBSTR(RPAD(ITEM,15,'A'),8,1))*8 +
ASCII(SUBSTR(RPAD(ITEM,15,'A'),9,1))*9 +
ASCII(SUBSTR(RPAD(ITEM,15,'A'),10,1))*10 +
ASCII(SUBSTR(RPAD(ITEM,15,'A'),11,1))*11 +
ASCII(SUBSTR(RPAD(ITEM,15,'A'),12,1))*12 +
ASCII(SUBSTR(RPAD(ITEM,15,'A'),13,1))*13 +
ASCII(SUBSTR(RPAD(ITEM,15,'A'),14,1))*14 +
ASCII(SUBSTR(RPAD(ITEM,15,'A'),15,1))*15
)
At report level this will lead to 2 linked DP's (over ordernumber) A simple check variable will get the desired ones from the okay orders:
= If (<fingerprint1> <> <fingerprint>2) Then 1 Else 0
Unfortunately BO does not allow a filter, so adding a break on the checking variable and some alerters took the desired orders to the top of the report.
Additional DP's added the detail level for the various items.
Tried and tested on ORACLE 7.3.3 database with BO version 5.1.3. full client
Added note on AS400 DP's:
On AS400 there appears to be no such function as the ASCII function mentioned earlier. However with a report variable using Asc,Substr,Rightpad you can work the strategy in at report level. Unfortunately, when doing the aggregation on the ordernumber you create a calculated measure that cannot be set to a detail, losing the ability to apply a break on a checking variable.
Still comparing ORACLE and AS400 lists are possible... |
Talk To Other Members
