CRM Loyalty: Is anyone else experiencing mismatches between points total and balance?

[former_member235846]

We have been live for some three months and have experienced that on our system there are mismatches between the balance on a point account and the sum total of transactions posted into that account.

We have also checked with another client, and they have confirmed that this problem occurs on their system too, though to a lesser extent. In our case, some of the differences are quite large.

We have seen that when you do a merge on a count, the MERGE post has a zero value causing a balance mismatch, but even when you exclude merges, there are still instances of mismatches.

We have run the following query in DB01 -> SQL Editor to get a list of accounts where such a mismatch occurs. Please try on your side and see if you also get mismatches.

select a.object_id, a.balance, sum(tx.points),  a.balance - sum(tx.points) difference
from loyd_pt_acct as a
join loyd_pt_txn as tx
  on tx.ref_guid = a.guid
where a.object_id in (
select a.object_id
from loyd_pt_acct as a
join loyd_pt_txn as txn
  on txn.ref_guid = a.guid
group by a.object_id, a.balance
having a.balance <> sum(txn.points)
minus
select a.object_id
from loyd_pt_acct as a
join loyd_pt_txn as txn
  on txn.ref_guid = a.guid
where txn.txn_reason = 'MERGE'
group by a.object_id )
group by a.object_id, a.balance 

(Note that we are running large volumes of member activities through the loyalty engine in batch mode via program RLOY_ENGINE_SCHEDULE; we wonder if this has anything to do with our specific problem).

[former_member571745]

Experienced similar and interested to hear how you may have solved.

[Former Member]

Hi Craig,

We also encounter the same problem, we are also running large volumes of member activities through the loyalty engine in batch mode via program RLOY_ENGINE_SCHEDULE. How did you solve this mismatches then? Any feedback will be greatly appreciated!