Oracle Item Import (Batch or Multi Thread Approach)

Threading or Batching of Item import is very common practice and extremely effective way to speed up item import/conversion in Oracle apps. 

All you need is to split the item conversion into batches say 100 sets of 5K or 10 K records each.  And update the batch number to set_process_id along the items

Sample:

UPDATE mtl_system_items_interface

   SET set_process_id = ROUND (ROWNUM / 10000, 0) + 1

WHERE process_flag = 1 

Then submit the Item Import concurrent for each batch.

Note : Increase the Processes for standard manager (sysadmin > concurrent > administer) to the maximum possible number.

Now submit the concurrent program for each set_process_id

Sample:

ln_set_process_id:=0

Loop

ln_set_process_id := ln_set_process_id+1;

L_request_id :=

fnd_request.submit_request (application      => 'INV',

                                              program          => 'INCOIN',

                                              description      => NULL,

                                              start_time       => SYSDATE,

                                              argument1        => ln_io,

                                              argument2        => 2,

                                              argument3        => 1,

                                              argument4        => 1,

                                              argument5        => 2,

                                              argument6        => ln_set_process_id,

                                              argument7        => 1

                                             );

COMMIT;

L_request_ids(ln_set_process_id) := L_request_id;

END Loop

That's it.....