CORE-POS/IS4C

use translog;
DROP VIEW IF EXISTS `staffdiscountadd`;
CREATE VIEW `staffdiscountadd` AS 

select max(`datetime`) AS `datetime`,
`register_no` AS `register_no`,
`emp_no` AS `emp_no`,
`trans_no` AS `trans_no`,
`upc` AS `upc`,
`description` AS `description`,
'I' AS `trans_type`,
'' AS `trans_subtype`,
'S' 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`,40 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 `translog`.`localtemptrans` 
where (((`discounttype` = 4) and (`unitPrice` = `regPrice`)) or (`trans_status` = 'S')) 
group by `register_no`,`emp_no`,`trans_no`,`upc`,`description`,`card_no` having (sum(`memDiscount`) <> 0);