Overview

A till in your store has crashed while processing an exchange. As a result, there is a discrepancy with the till's balance as the transaction was logged and there are payment records attached to the order. You want to void the transaction that was logged in order to balance the till. 

Solution

Contact Prologic Support and request the problem transactions to be voided. Make sure to include the following information in your request:

• Till number
• Store name
• Transaction
• Date the of the transaction.

<supportagent>

Determine the shop number using the provided store name:

SELECT loc_num FROM loc_defs 
 WHERE loc_sort = 'STORE NAME HERE';

Find the imbalance.

The example below is for a balance of -£20 in the register. You will need to modify till number, shop_num, and date according to the information provided by the customer.

select * from (
select ftrans_type, ftrans_pay_type, pos_pay_type, count(*) cnt, sum(pay_ffamount) amount
from pos_grp_payments where shop_num = 70001  --and ftrans_pay_type = 'CARD' and pos_pay_type = 'CP'
and (pos_num, ftrans_type) in (
  select pos_num, pos_type from pos_grp_defs 
  where shop_num = 70001 and in_date = '19-APR-18' 
  and till_num = 21
  and ftrans_run <> 'V'
)
group by ftrans_type, ftrans_pay_type, pos_pay_type
) a right join 
--order by pos_pay_type
(
select ftrans_type , ftrans_pay_type, pos_pay_type, sum(ftrans_count) cnt, sum(ftrans_ffamount) amount
from pos_grp_ftrans where shop_num = 70001 and in_date = '19-APR-18' and till_num = 21
group by ftrans_type , ftrans_pay_type, pos_pay_type
) b on a.ftrans_pay_type = b.ftrans_pay_type and a.pos_pay_type = b.pos_pay_type and a.ftrans_type = b.ftrans_type
order by  a.pos_pay_type, b.ftrans_pay_type

Usually, the customer would provide us with the pos_num that caused the imbalance. However, if the information is not provided, we can identify the transactions causing the issue with the query above. 
Based on the result of the query given above, we have:

As the structure should have a matching balance of S & C for the same ftrans_pay_type, it was concluded that this amount caused the difference.

From this, you should drill in more on the given transaction type

select d.pos_num, pos_ref_num, ftrans_run, rollup_run from pos_grp_defs d inner join pos_grp_payments p on d.pos_num = p.pos_num and d.pos_type = p.ftrans_type
  where d.shop_num = 70001 and d.in_date = '19-APR-18' 
  and d.till_num = 21
  and  p.shop_num = 70001
  and p.pos_pay_type = 'EX-S'
  and d.ftrans_run <> 'V'

Result

As shown here, the referenced pos_num 22052189 was referencing a voided transaction, and this caused the difference. We can conclude that the pos_num 22052189 should be voided to match the referenced voided transaction 21843788.

To correct this, the transaction must be voided both in the HO and the shop. The correction script is this (remember to edit according to your own values):

update pos_grp_defs set  ftrans_run='V',rollup_run='Z'  where pos_num = 22052189 and till_num = 21 and shop_num =70001 ;
update pos_grp_ftrans set ftrans_count=2,ftrans_famount=120,ftrans_ffamount=120 where in_date='19-APR-18'  and shop_num=70001 and till_num='21' and ftrans_pay_type like 'EXC' and ftrans_type='S' and pos_pay_type = 'EX-S';


update pos_defs@s001 set ftrans_run='V' where pos_num=22052189;
update pos_ftrans@s001 set ftrans_count=2,ftrans_famount=120,ftrans_ffamount=120 where in_date='19-APR-18'  and till_num='21' and ftrans_pay_type like 'EXC' and ftrans_type='S' and pos_pay_type = 'EX-S';

Now the transaction is voided, and the till is balanced. 

</supportagent>

Testing

You will receive confirmation from Prologic Support when the problem transactions have been voided. Once this happens, the till will be balanced and you will be able to batch it.