select a.*, case 
                when a.box1_code like 'LTK%' then '1'
                when a.box1_code like 'PAHVI' then '1'
                when a.box1_code like 'JAKO2' then '1'
                when a.box1_code like 'PEU%' then '2'
                when a.box1_code like 'EU%' then '2'
                when a.box1_code like 'FI%' then '2'
                when a.box1_code like 'JAKO1' then '2'
            end box
from(
    select part, description, leadtime, location,
            lot_size, supplier_id, supplier,
            max(decode(pivot,1,loadcode,'')) box1_code,
            max(decode(pivot,1,amount,'')) box1_amount,
            max(decode(pivot,2,loadcode,'')) box2_code,
            max(decode(pivot,2,amount,'')) box2_amount
    from(
        select a.part_no part, a.Description description, 
            a.LEADTIME leadtime, b.LOCATION_NO location, 
            c.LOT_SIZE lot_size, d.VENDOR_NO supplier_id, 
            using_api.GET_Name(d.VENDOR_NO) supplier, 
            min(e.code) loadcode, e.value amount,
            rank() over(partition by a.part_no order by e.code) pivot
        from table1 a, table2 b, 
             table3 c, table4 d, 
             table5 e 
        where a.part_no = b.part_no and a.part_no = c.part_no 
        and a.part_no = d.part_no and a.part_no = e.part_no 
         and a.part_no in ('part1','part2') 
        group by a.part_no, a.Description, a.LEADTIME, b.LOCATION_NO, 
        c.LOT_SIZE, d.VENDOR_NO, e.code, e.value order by a.part_no     
    )
     ) a order by box, location