title: Oracle APEX - Saving Shuttle Item selections to a new table
body: <p>I have a project I am working on where I am dealing with PARTS and ORDERS. Each ORDER may contain many PARTS and each PART has the possiblity of being on more than one ORDER over the course of its life (though not more than one ACTIVE ORDER at a time). My tables are currently as follows:</p>
  
  <pre><code> PARTS_TABLE
        PART_NUMBER varchar2(20)
        ASSIGNED_ORDER_NUMBER number(5)
        ASSIGNED_ORDER_STATUS varchar2(20)
  
  
   ORDER_TABLE
        ORDER_NUMBER number (5)
        ORDER_STATUS varchar2(20)
        ORDER_PARTS_LIST varchar2(4000) //Comma delimited list generated by shuttle item.
  </code></pre>
  
  <p>This current set up is working for us atm (with the help of this previous question: <a href="https://stackoverflow.com/questions/9024336/oracle-apex-database-trigger-problems-with-referencing-database-columns">Previous Question</a>) but it requires duplicating lots of data in both tables and it does not really allow for a PART being on multiple ORDERS over the course of its life. </p>
  
  <p>What I would like to do is create a cross-reference table between these two tables:</p>
  
  <pre><code> ORDER_PARTS_TABLE
        ORDER_NUMBER number(5) (pk1)
        PART_NUMBER varchar2(20) (pk2)
        ORDER_STATUS varchar2(20)
  </code></pre>
  
  <p>Where PART_NUMBER and ORDER_NUMBER create a joint primary key. </p>
  
  <p>My main issue is creating and editing the rows in ORDER_PARTS_TABLE based on the value of a shuttle item. I am able to effectively edit/update with my current set up but in my current set up I am not trying to create rows, I am just referencing the rows in PARTS_TABLE. When a person, using a shuttle item on a form in APEX, adds PARTS to an ORDER new rows should be created in ORDER_PARTS_TABLE i.e.:</p>
  
  <pre><code> ORDER_TABLE
        ORDER_NUMBER     ORDER_PARTS_LIST     ORDER_STATUS
        12345            675:342:871:902      ACTIVE
  
  
   ORDER_PARTS_TABLE
        ORDER_NUMBER     PART_NUMBER     ORDER_STATUS
        12345            675             ACTIVE
        12345            342             ACTIVE
        12345            871             ACTIVE
        12345            902             ACTIVE
  </code></pre>
  
  <p>And if that order is later edited where a part is removed then the row pertaining to that relationship should either be removed from the ORDER_PARTS_TABLE or have its ORDER_STATUS set to 'REMOVED'</p>
  
  <pre><code> ORDER_TABLE
        ORDER_NUMBER     ORDER_PARTS_LIST     ORDER_STATUS
        12345            675:871:902          ACTIVE
  
   either...
   ORDER_PARTS_TABLE
        ORDER_NUMBER     PART_NUMBER     ORDER_STATUS
        12345            675             ACTIVE
        12345            342             REMOVED
        12345            871             ACTIVE
        12345            902             ACTIVE
  
    or...
    ORDER_PARTS_TABLE
        ORDER_NUMBER     PART_NUMBER     ORDER_STATUS
        12345            675             ACTIVE
        12345            871             ACTIVE
        12345            902             ACTIVE
  </code></pre>
  
  <p>Does any of that make sense? My current solution is just using triggers which I have been decently happy with but let me know what the best way to approach this new issue is. Thanks!</p>
  
  <p><strong>EDIT:</strong> I have been doing some continued digging and found something that sounds reasonably promising if it can be adapted. Has anyone here had experience with 
  REGEXP_SUBSTR? <a href="https://forums.oracle.com/forums/thread.jspa?threadID=837253&amp;start=0&amp;tstart=0" rel="nofollow noreferrer">LINK</a> <a href="http://docs.oracle.com/cd/B14117_01/server.101/b10759/functions116.htm" rel="nofollow noreferrer">LINK</a></p>