select max(prod_id)
from product
where effdt = (select max(effdt) from product)

##</code><code>##

insert order (prod_id, prod_cnt)
select prod_id, count(*)
from product
group by prod_id