select 
em_employee_id,
etf_tax_group,
etf_1_tax_code_1,
etf_1_lqtd_tax_amount_1,
etf_1_lqtd_taxable_wages_1,
etf_1_qtd_tax_amount_1,
etf_1_qtd_taxable_wages_1,
etf_1_tax_code_2,
etf_1_lqtd_tax_amount_2,
etf_1_lqtd_taxable_wages_2,
etf_1_qtd_tax_amount_2,
etf_1_qtd_taxable_wages_2,
etf_1_tax_code_3,
etf_1_lqtd_tax_amount_3,
etf_1_lqtd_taxable_wages_3
etf_1_qtd_tax_amount_3,
etf_1_qtd_taxable_wages_3,
etf_1_tax_code_4,
etf_1_lqtd_tax_amount_4,
etf_1_lqtd_taxable_wages_4,
etf_1_qtd_tax_amount_4,
etf_1_qtd_taxable_wages_4,
etf_1_tax_code_5,
etf_1_lqtd_tax_amount_5,
etf_1_lqtd_taxable_wages_5,
etf_1_qtd_tax_amount_5,
etf_1_qtd_taxable_wages_5,
etf_1_tax_code_6,
etf_1_lqtd_tax_amount_6,
etf_1_lqtd_taxable_wages_6,
etf_1_qtd_tax_amount_6,
etf_1_qtd_taxable_wages_6,
etf_1_tax_code_7,
etf_1_lqtd_tax_amount_7,
etf_1_lqtd_taxable_wages_7,
etf_1_qtd_tax_amount_7,
etf_1_qtd_taxable_wages_7,
etf_1_tax_code_8,
etf_1_lqtd_tax_amount_8,
etf_1_lqtd_taxable_wages_8,
etf_1_qtd_tax_amount_8,
etf_1_qtd_taxable_wages_8,
etf_lqtd_gross_pay,
etf_qtd_gross_pay,
etf_lqtd_net_pay,
etf_qtd_net_pay,
etf_lqtd_report_wages,
etf_qtd_report_wages
from etf
where em_employee_id = 'employee id'
  and etf_tax_group  = 'example tax group';


-- oasdi
Update etf set
etf_1_lqtd_tax_amount_1    = etf_1_lqtd_tax_amount_1    -   949.31 ,
etf_1_qtd_tax_amount_1     = etf_1_qtd_tax_amount_1     +   949.31 ,
etf_1_lqtd_taxable_wages_1 = etf_1_lqtd_taxable_wages_1 - 15311.47 ,
etf_1_qtd_taxable_wages_1  = etf_1_qtd_taxable_wages_1  + 15311.47
where em_employee_id = 'employee id'
  and etf_tax_group  = 'example tax group';
  
-- eoasdi
Update etf set
etf_1_lqtd_tax_amount_2    = etf_1_lqtd_tax_amount_2    -   949.31 ,
etf_1_qtd_tax_amount_2     = etf_1_qtd_tax_amount_2     +   949.31 ,
etf_1_lqtd_taxable_wages_2 = etf_1_lqtd_taxable_wages_2 - 15311.47 ,
etf_1_qtd_taxable_wages_2  = etf_1_qtd_taxable_wages_2  + 15311.47
where em_employee_id = 'employee id'
  and etf_tax_group  = 'example tax group';  
  
-- medcare
Update etf set
etf_1_lqtd_tax_amount_3    = etf_1_lqtd_tax_amount_3    -   222.02 ,
etf_1_qtd_tax_amount_3     = etf_1_qtd_tax_amount_3     +   222.02 ,
etf_1_lqtd_taxable_wages_3 = etf_1_lqtd_taxable_wages_3 - 15311.47 ,
etf_1_qtd_taxable_wages_3  = etf_1_qtd_taxable_wages_3  + 15311.47
where em_employee_id = 'employee id'
  and etf_tax_group  = 'example tax group'; 
  
-- emedcare
Update etf set
etf_1_lqtd_tax_amount_4    = etf_1_lqtd_tax_amount_4    -   222.02 ,
etf_1_qtd_tax_amount_4     = etf_1_qtd_tax_amount_4     +   222.02 ,
etf_1_lqtd_taxable_wages_4 = etf_1_lqtd_taxable_wages_4 - 15311.47 ,
etf_1_qtd_taxable_wages_4  = etf_1_qtd_taxable_wages_4  + 15311.47
where em_employee_id = 'employee id'
  and etf_tax_group  = 'example tax group';  
  
-- fit
Update etf set
etf_1_lqtd_tax_amount_5    = etf_1_lqtd_tax_amount_5    -   503.27 ,
etf_1_qtd_tax_amount_5     = etf_1_qtd_tax_amount_5     +   503.27 ,
etf_1_lqtd_taxable_wages_5 = etf_1_lqtd_taxable_wages_5 -  3969.17 ,
etf_1_qtd_taxable_wages_5  = etf_1_qtd_taxable_wages_5  +  3969.17
where em_employee_id = 'employee id'
  and etf_tax_group  = 'example tax group';
  
-- sui06 is zero in occurance 6

-- sit06
Update etf set
etf_1_lqtd_tax_amount_7    = etf_1_lqtd_tax_amount_7    -   230.09 ,
etf_1_qtd_tax_amount_7     = etf_1_qtd_tax_amount_7     +   230.09 ,
etf_1_lqtd_taxable_wages_7 = etf_1_lqtd_taxable_wages_7 -  3969.17 ,
etf_1_qtd_taxable_wages_7  = etf_1_qtd_taxable_wages_7  +  3969.17
where em_employee_id = 'employee id'
  and etf_tax_group  = 'example tax group';
  
-- sdi06
Update etf set
etf_1_lqtd_tax_amount_8    = etf_1_lqtd_tax_amount_8    -    91.86 ,
etf_1_qtd_tax_amount_8     = etf_1_qtd_tax_amount_8     +    91.86 ,
etf_1_lqtd_taxable_wages_8 = etf_1_lqtd_taxable_wages_8 - 15311.47 ,
etf_1_qtd_taxable_wages_8  = etf_1_qtd_taxable_wages_8  + 15311.47
where em_employee_id = 'employee id'
  and etf_tax_group  = 'example tax group';
    
-- gross
update etf set
etf_lqtd_gross_pay = etf_lqtd_gross_pay - 15311.47  ,
etf_qtd_gross_pay  = etf_qtd_gross_pay  + 15311.47  
where em_employee_id = 'employee id'
  and etf_tax_group  = 'example tax group';
  
-- net
update etf set
etf_lqtd_net_pay = etf_lqtd_net_pay - 1972.62  ,
etf_qtd_net_pay  = etf_qtd_net_pay  + 1972.62  
where em_employee_id = 'employee id'
  and etf_tax_group  = 'example tax group';  
  
-- report wages
update etf set
etf_lqtd_report_wages = etf_lqtd_report_wages - 15311.47  ,
etf_qtd_report_wages  = etf_qtd_report_wages  + 15311.47  
where em_employee_id = 'employee id'
  and etf_tax_group  = 'example tax group';    
  
  

-- select qtd pay amounts all should be zero  
select
em_employee_id,
pmf_pay_code,
epf_qtd_dollars_paid
from epf
where em_employee_id = 'employee id'
  and pmf_pay_code in ('00', '14', '49', '901')
  and epf_eff_inverse_date = ' ';
  
update epf
set epf_qtd_dollars_paid = epf_qtd_dollars_paid +   1165.55
where em_employee_id = 'employee id'
  and pmf_pay_code = '00'
  and epf_eff_inverse_date = ' ';
  
update epf
set epf_qtd_dollars_paid = epf_qtd_dollars_paid +    343.35
where em_employee_id = 'employee id'
and pmf_pay_code = '14'
and epf_eff_inverse_date = ' ';

update epf
set epf_qtd_dollars_paid = epf_qtd_dollars_paid +  11655.50
where em_employee_id = 'employee id'
and pmf_pay_code = '49'
and epf_eff_inverse_date = ' ';

update epf
set epf_qtd_dollars_paid = epf_qtd_dollars_paid +   2147.07
where em_employee_id = 'employee id'
and pmf_pay_code = '901'
and epf_eff_inverse_date = ' ';