12 September 2012

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.....