Adding Many-to-Many Relationship in Siebel Data Structure

Siebel's front end interface provides Multi-Value Group (MVG) to present many-to-many (M:M) relationship between two tables through a link table.  In many cases, we would want to make a customization in Siebel 8.1.1.11 Call Center to add a many-to-many relationship between two components.  For example, we would consider a M:M link between Activity (S_EVT_ACT) and Order Item in the CRM system. With this relationship, we could to add a new Multi-Value Group (MVG) field in Activity business component to make each Activity record associate with multiple order item records.   But this would be a custom design whcich requires some courage and efforts.

To begin with this task, we first look for an obvious intersection table with a name like S_ACT_ORD_ITEM or S_ORDITM_ACT, but they do not exist.  Thus we need to I look for tables that have foreign key columns for both tables:

Look in Siebel Tools / Object Explorer flat view on the column object.
Do a query on the Foreign Key Table Name property like this 'S_ORDER_ITEM' or 'S_EVT_ACT' and then do a sort on the Parent Table Name property.
Look for records that come back with 2 records, one for each table.

I did this for 8.1.1.11 SIA standard / vanilla repository and I did find some tables that had foreign keys for both of these tables..

S_ACTPART_MVMT
S_ACT_PRDINT
S_INVOICE_CHRG
S_INVOICE_ITEM
S_INV_TEMP

Technically they can be used as the link table for the M:M relationship.  But obviously these tables are there in the current configuration or for past or future use. You can review how these tables are used as a BC table or as an intersection table.

In general you don't want to "re-use" a table, it is cleaner to create a new custom CX_ intersection table that will not affect upgrades or other config that already uses the existing table.  Siebel Tools has a new Table wizard that can create the intersection table. It is better to create a new M:M link between two tables / two BCs where it does not already exist.

Then you can create the MVG config as a proof of concept.

Creating the new table, new BC, new M:M link and MVGs are straightforward, but integrating then with Order Management would be a more complex / design question.  Also, Order Management functionality has a lot of set up and specialized behavior that might need to be considered and Expert Services is going to consider that with your requirements.  Thus, Oracle Support Solution Engineering/ Oracle Siebel Expert Services should be engaged to look at the business requirements and make recommendations on creating a new M:M relationships in the data model.