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.
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
- till number
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 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
- '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.
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 and till_num = AA ;
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: