CORE-POS/IS4C

use translog;
DROP VIEW IF EXISTS `memdiscountadd`;

CREATE view memdiscountadd as

Select 
max(datetime) as datetime, 
register_no, 
emp_no, 
trans_no, 
upc, 
description, 
'I' as trans_type, 
'' as trans_subtype, 
'M' as trans_status, 
max(department) as department, 
1 as quantity, 
0 as scale, 
-1 * sum(memDiscount) as unitPrice, 
-1 * sum(memDiscount) as total, 
-1 * sum(memDiscount )as regPrice, 
max(tax) as tax, 
max(foodstamp) as foodstamp, 
0 as discount, 
-1 * sum(memDiscount) as memDiscount, 
3 as discountable, 
20 as discounttype, 
8 as voided,
0 as percentDiscount,
0 as ItemQtty, 
0 as volDiscType, 
0 as volume, 
0 as VolSpecial, 
0 as mixMatch, 
0 as matched, 
 card_no as card_no
from localtemptrans 
where ((discounttype = 2 and unitPrice = regPrice) or trans_status = 'M') 
group by register_no, emp_no, trans_no, upc, description, card_no 
having 
sum(memDiscount)<> 0