not stepping through inner loop correctly

[darfader]

Hi,

I am having some trouble trying to loop within a loop.

I am treating this like a parent/child situation where I want the parent field reproduced once and the child field is reproduced for each of its parent. Unfortunately it is not stepping through the inner loop and the same three child fields are being produced for all parent fields.

table description

desc lah_links
 Name                                                 Null?    Type
 ---------------------------------------------------- -------- ------------
 ID                                                   NOT NULL VARCHAR2(32)
 CONSIGNMENT_NO_PARENT                                NOT NULL VARCHAR2(32)
 INTERNAL_CON_NO_PARENT                               NOT NULL VARCHAR2(32)
 CONSIGNMENT_NO_CHILD                                 NOT NULL VARCHAR2(32)
 INTERNAL_CON_NO_CHILD                                NOT NULL VARCHAR2(32)
 CREATE_DATE                                                   DATE
 LAST_LINK_ATTEMPT                                             DATE
 LINKS_ATTEMPTED                                               NUMBER(10)

data string - formatted for ease of viewing
this is a standard text string without white space and field delimited by curly braces
the fields which have multiple fields are delimited by pipes and percents

timestamp
2004020648484492

    sack internal references
   }R847318601 | R84731701 | R84731902

        sack consignment numbers
       }99763065X | 97763600X | 98763500X

             child consignment internal references
             NOTE: pipe delimited per sack - % delimited per child
            }522244945 % 522244947 % 522244950 | 622244945 % 622244946 % 622244948 | 722244945 % 722244946 % 722244948

                 child consignment numbers
                 NOTE: pipe delimited per sack - % delimited per child
                }99751158X % 9980058X % 99681158X | 999511598X % 9970058X % 99501158X | 905011598X % 9070058X % 90501158X

the procedure

PROCEDURE process_links_home (p_links_home_array  IN  common.t_split_array) IS
  l_tab                   t_lah_links := t_lah_links();
  l_links_home_row        lah_links%ROWTYPE := NULL;
  l_consignment_no_parent lah_links.consignment_no_parent%TYPE;
  l_no_parent_array       common.t_split_array;
  l_con_no_parent_array   common.t_split_array;
  l_no_child_array        common.t_split_array;
  l_con_no_child_array    common.t_split_array;
  l_no_child_mini         common.t_split_array;
  l_con_no_child_mini     common.t_split_array;
  l_links_inside_array     common.t_split_array := p_links_home_array;
  l_large_no_child        VARCHAR2(256);
  l_large_con_no_child    VARCHAR2(256);
  l_timestamp             VARCHAR2(16);
BEGIN
  IF p_links_home_array(consignment_no_parent) IS NOT NULL THEN
    l_no_parent_array     := common.split_text(p_links_home_array(consignment_no_parent), '|');
    l_con_no_parent_array := common.split_text(p_links_home_array(internal_con_no_parent), '|');
    l_no_child_array      := common.split_text(p_links_home_array(consignment_no_child), '|');
    l_con_no_child_array  := common.split_text(p_links_home_array(internal_con_no_child), '|');

    FOR i IN p_links_home_array.first .. p_links_home_array.last LOOP
      l_tab.extend;
      l_tab(l_tab.last).id := new_uuid;
      l_timestamp := p_links_home_array(timestamp);
      l_tab(l_tab.last).consignment_no_parent := p_links_home_array(consignment_no_parent);
      l_tab(l_tab.last).internal_con_no_parent := p_links_home_array(internal_con_no_parent);
      l_large_no_child := p_links_home_array(consignment_no_child);
      l_large_con_no_child := p_links_home_array(internal_con_no_child);

      -- while looping start another for the children of children
      -- need a loop here to a) split on the pipe and b) step through the percent...
      FOR j IN 1..3 LOOP
        l_no_child_mini     := common.split_text(l_large_no_child, '%');
        l_con_no_child_mini := common.split_text(l_large_con_no_child, '%');

        insert_links_home (p_id                     => l_links_home_row.id,
                          p_consignment_no_parent   => l_no_parent_array(i),
                          p_internal_con_no_parent  => l_con_no_parent_array(i),
                          p_consignment_no_child    => strip_pipe(l_no_child_mini(j)),
                          p_internal_con_no_child   => strip_pipe(l_con_no_child_mini(j)));
      END LOOP;
    END LOOP;
  END IF;
EXCEPTION
  WHEN no_data_found THEN
    NULL;
END process_links_home;

I want the data to be inserted as follows

R847318601                       99763065X                        522244945                        99751158X
R847318601                       99763065X                        522244947                        9980058X
R847318601                       99763065X                        522244950                        99681158X
R84731701                        97763600X                        622244945                        999511598X
R84731701                        97763600X                        622244946                        9970058X
R84731701                        97763600X                        622244948                        99501158X
R84731902                        98763500X                        722244945                        905011598X
R84731902                        98763500X                        722244946                        9070058X
R84731902                        98763500X                        722244948                        90501158X

but unfortunately it is appearing like this

R847318601                       99763065X                        522244945                        99751158X
R847318601                       99763065X                        522244947                        9980058X
R847318601                       99763065X                        522244950                        99681158X
R84731701                        97763600X                        522244945                        99751158X
R84731701                        97763600X                        522244947                        9980058X
R84731701                        97763600X                        522244950                        99681158X
R84731902                        98763500X                        522244945                        99751158X
R84731902                        98763500X                        522244947                        9980058X
R84731902                        98763500X                        522244950                        99681158X

tia,