ALTER TRIGGER [updateOrderRebatebycusttype] ON [dbo].[oeordlin_sql] 
AFTER INSERT
AS

--This insert statement looks for matching records in the Rebate Table
--It looks for customer type and item_no combinations or price Code type 3.
--If a match is found it writes a record to the OrderRebate table.
begin
insert into OrderRebate(ord_type,
ord_no,
line_seq_no,
item_no,
qty_ordered,
qty_to_ship,
cus_no,
a4ID,
AccountTypeCode,
price,
rebate_pct,
cd_tp)
select inserted.ord_type,
inserted.ord_no,
inserted.line_seq_no,
inserted.item_no,
inserted.qty_ordered,
inserted.qty_to_ship,
inserted.cus_no,
inserted.ID,
cicmpy.AccountTypeCode,
rebate.price,
rebate.rebate_pct,
rebate.cd_tp
from inserted INNER JOIN cicmpy ON inserted.cus_no = cicmpy.debcode INNER join rebate on 
cicmpy.AccountTypeCode = Rebate.cd_tp_3_cus_type AND inserted.item_no = Rebate.item_no
where cicmpy.AccountTypeCode = Rebate.cd_tp_3_cus_type AND inserted.item_no = Rebate.item_no
end



--This insert statement looks for matching records in the Rebate Table
--It looks for customer number and item_no combinations or price Code type 1.
--If a match is found it writes a record to the OrderRebate table.
begin
insert into OrderRebate(ord_type,
ord_no,
line_seq_no,
item_no,
qty_ordered,
qty_to_ship,
cus_no,
a4ID,
AccountTypeCode,
price,
rebate_pct,
cd_tp)
select inserted.ord_type,
inserted.ord_no,
inserted.line_seq_no,
inserted.item_no,
inserted.qty_ordered,
inserted.qty_to_ship,
inserted.cus_no,
inserted.ID,
cicmpy.AccountTypeCode,
rebate.price,
rebate.rebate_pct,
rebate.cd_tp
from inserted INNER JOIN cicmpy ON inserted.cus_no = cicmpy.debcode INNER join rebate on 
inserted.item_no = Rebate.item_no and inserted.cus_no = Rebate.cd_tp_1_cus_no 
where inserted.item_no = Rebate.item_no and  inserted.cus_no = Rebate.cd_tp_1_cus_no 
end



--This update statement calculates the rebate amount and extended rebate amt
-- and updates the orderrebate table with these amounts for price code type 3 records
-- and writes the literal 'Rebate' to freefield3 in the orderrebate table. freefield3 will help
-- determine which records are rebate records that get inserted into the gbkmut table.
begin
update OrderRebate
set  rebate_amt = (rebate.price*(.01*rebate.rebate_pct)),
ext_rebate = round((rebate.price*(.01*rebate.rebate_pct)*inserted.qty_to_ship),2),freefield3 = 'Rebate'
from inserted INNER JOIN cicmpy ON inserted.cus_no = cicmpy.debcode INNER join rebate on 
cicmpy.AccountTypeCode = Rebate.cd_tp_3_cus_type AND inserted.item_no = Rebate.item_no
where inserted.ord_type = OrderRebate.ord_type and inserted.ord_no = OrderRebate.ord_no and 
inserted.line_seq_no = OrderRebate.line_seq_no
end





--This update statement calculates the rebate amount and extended rebate amt
-- and updates the orderrebate table with these amounts for price code type 1 records
-- and writes the literal 'Rebate' to freefield3 in the orderrebate table. freefield3 will help
-- determine which records are rebate records that get inserted into the gbkmut table.
begin
update OrderRebate
set rebate_amt = (rebate.price*(.01*rebate.rebate_pct)),
ext_rebate = Round((rebate.price*(.01*rebate.rebate_pct)*inserted.qty_to_ship),2),
freefield3 = 'Rebate'
from inserted INNER JOIN rebate on 
inserted.cus_no = Rebate.cd_tp_1_cus_no AND inserted.item_no = Rebate.item_no
where inserted.ord_type = OrderRebate.ord_type and inserted.ord_no = OrderRebate.ord_no and 
inserted.line_seq_no = OrderRebate.line_seq_no
end