I am involved in a major data conversion effort. Below are the details.

Due to some interface changes and new systems installed, all the item_id values in our database needs to be changed to new values. The item_id column is referenced in almost every table and is in the primary key.

To make things complicated multiple old item_id values can be mapped to a single new item_id value. So an aggregation needs to happen for all the records that have same new item_id. The columns which cannot be aggregated, where we take the attributes of a record which has priority over other items.

Just wanted to know how to approach this conversion.

example:
--------

table (xref_item), columns(old_item_id, new_item_id, priority)
data:
item1 itema 1
item2 itema 2
item3 itema 3
item4 itemb 1
item5 itemb 2
item6 itemb 3

application table
table(po_details), columns (po_nbr, item_id, req_qty, round, country)
data:
po1 item1 10 Y US
po1 item2 15 N US
po1 item3 20 Y UK
po1 item4 10 N UK
po1 item5 15 Y US
.
.
.

at the end of the conversion.
I should have
po1 itema 45 Y US
po1 itemb 25 N UK

the last 2 column values are picked from po_record's item_id based on the priority.

The stmt to do so is

insert into po_details_new /*+ APPEND */
select item_id, po_nbr, sum(req_qty), min(round), min(country)
from (
select ix.new_item_id item_id,
po.po_nbr po_nbr,
first_value(po.round) over (partition by ix.new_item_id, po.po_nbr order by ix.priority) round,
first_value(po.county) over (partition by ix.new_item_id, po.po_nbr order by ix.priority) country,
po.req_qty req_qty,
from po_details po,
item_xref ix
where po.item_id = ix.old_item_id)
group by item_id, po_nbr