query hangs .. Need help

Hi,
we have code in a application that does insert on a table via a sub query , the total data retrieved by the sub query is around 11900 records and its retrieved in 20 to 30 seconds . When run independently it works fine.

the sub query is
------------------------
SELECT DECODE
             (clp.ctnr_load_pln_stat_c,
              'CLOSD', 1,
              pkg_otm_archival.fn_late_po_elig_for_load_cload (l.load_i,
                                                               NULL,
                                                               'L',
                                                               30
                                                              )
             ) LOAD
  FROM LOAD l, book b, tran t, ctnr_load_pln clp, po_header_e pe
 WHERE l.load_i = clp.load_i
   AND l.load_i = b.prim_load_i