Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

How can I compare lists of items from different sources?

Complex Universe Objects

How can I compare lists of items from different sources?

by  blom0344  Posted    (Edited  )
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 [color red]ASCII[/color] function.

Observe the following object definition:

Code: 
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 [color blue]ITEM[/color] 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:

Code: 
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 [color red]AS400[/color] 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...
Register to add this FAQ to your archive
Register to rate this FAQ  : BAD 1 2 3 4 5 6 7 8 9 10 GOOD
Please Note: 1 is Bad, 10 is Good :-)

Part and Inventory Search

Back
Top