#1.search lose request approve record select distinct(a.campaign_id),d.campaign_code,c.type_desc,a.request_id as a_request_id,b.request_id, c.type_id, a.voucher_id from voucher_lkup_type c, voucher_campaign d, voucher_campaign_issue a left join voucher_request_approve b on (a.request_id = b.request_id) where d.campaign_id = a.campaign_id and c.type_id = d.type_id having b.request_id is null #2.Complement complete request approve record select tmp_a.a_request_id as request_id,NULL as batch_id, null as voucher_id, tmp_b.user_id as request_user, tmp_b.action_time as request_time, 30 as request_action, tmp_a.from_stock, tmp_b.remarks as request_remarks , NULL as approve_user,NULL as approve_time, 30 as approve_action, 50 as to_stock,'Y' as is_done,NULL as cancel_user,NULL as cancel_user from ( select a.voucher_id,a.campaign_id,d.campaign_code,c.type_desc,a.request_id as a_request_id,b.request_id, c.type_id, c.from_stock from voucher_lkup_type c, voucher_campaign d, voucher_campaign_issue a left join voucher_request_approve b on (a.request_id = b.request_id) where d.campaign_id = a.campaign_id and c.type_id = d.type_id group by a.campaign_id, a.request_id having b.request_id is null ) tmp_a left join ( #solve issue action repet issue action, use last time record select * from ( select a.user_id, a.action_time, a.voucher_id, a.remarks from voucher_action a, ( select a.voucher_id,b.request_id from voucher_lkup_type c, voucher_campaign d, voucher_campaign_issue a left join voucher_request_approve b on (a.request_id = b.request_id) where d.campaign_id = a.campaign_id and c.type_id = d.type_id group by a.campaign_id, a.request_id having b.request_id is null ) b where a.voucher_id = b.voucher_id and a.action_id = 30 order by action_time desc ) x group by voucher_id ) tmp_b on tmp_a.voucher_id = tmp_b.voucher_id #3.use code create batch_id & insert into request_approve record voucher_stock_batch->add_voucher_batch_by_voucher_ids