Overview

You may have a discrepancy with your banking and the till doesn't balance because of a transaction that shows "DECLINED EFT" for a refund of some amount. You need to void this transaction to correct the discrepancy.

Solution

The till can be balanced by voiding the transaction that is causing the imbalance. Please reach out to the support team asking to void the transaction and providing the following set of information:

• store name
• transaction
• date
• till number

<supportagent>

Determine store name

Store name can be found in the requester's email address in ZD.

For example, this email means the store name is 'L1':

Determine shop_num

One way to find shop_num for voiding scripts is by using this query once you have the store name: 

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

Remember that shop_num is same as loc_num in loc_defs table and each store has it's loc_num defined in loc_defs table.

where the store name is 'L1'.

The shop_num itself can also be found from the front-end on screen RO0A. 

Validation of Imbalance

Below is the query to validate the imbalance. You will need to modify till number, shop_num and date according to the provided information.

select * from (
select ftrans_type, ftrans_pay_type, pos_pay_type, count(*) cnt, sum(pay_ffamount) amount
from pos_grp_payments where shop_num = SSS --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 = SSS and in_date = '06-DEC-20' 
  and till_num = AA
  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 = SSS and in_date = '06-DEC-20'  and till_num = AA
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

where:

• 'AA' is the till number
• 'SSS' is shop_num determined in the previous step.

You may also use this article Till processing crashes leaving a wrong balance as a reference where it focuses on repairing the issue when there's an imbalance.

Voiding Query

In order to complete the void process, we need to implement it on both sides. Void the sale with the following script - these voiding queries balance the received and posted payments:

update pos_grp_defs set  ftrans_run='V',rollup_run='Z'  where pos_num = XXX and till_num=AA and shop_num = SSS;
update pos_defs@s004 set ftrans_run='V' where pos_num=XXX and till_num = AA ;

update pos_grp_ftrans set ftrans_count=0,ftrans_famount=0,ftrans_ffamount=0 where in_date='06-DEC-20'  and shop_num=SSS and till_num='AA' and ftrans_pay_type like 'CARD' and ftrans_type='C' and pos_pay_type = 'VISAS';
update pos_ftrans@s004 set ftrans_count=0,ftrans_famount=0,ftrans_ffamount=0 where in_date='06-DEC-20'  and till_num='11' and ftrans_pay_type like 'CARD' and ftrans_type='C' and pos_pay_type = 'VISAS';

where 'XXX' is the transaction to be voided

Note: The queries will need to be adjusted according to the values and architecture of the customer. 

There are different values for "ftrans_pay_type" or "pos_pay_type" columns, which can be any of the following and would depend on the record and the payment:

</supportagent>

Testing

Prologic Support will inform you when the transaction has been voided. Please check if the balance inconsistency of the till has been solved.