1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

BOM Operational Routing Table Link

Discussion in 'Oracle SCM & Manufacturing' started by mcertini, Aug 30, 2010.

  1. mcertini

    mcertini Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    I am currently working on a project whereby I am linking in MS Access the data in the "MTL_SYSTEM_ITEMS_B" Table, "BOM_OPERATIONAL_ROUTING" Table, "BOM_OPERATION_SEQUENCE" Table and "BOM_OPERATION_RESOURCES" Table.

    As for the link between the "MTL_SYSTEM_ITEMS_B" table and the "BOM_OPERATIONAL_ROUTING" table, there is a one to one relationship. Though for the link between "BOM_OPERATIONAL_ROUTING" (link from) and "BOM_OPERATION_SEQUENCE" (link to), this link is not so straight forward. The BOM Technical Manual shows that these tables are linked by way of the field "ROUTING_SEQUENCE_ID". What makes things complex is that there is a "COMMON_ROUTING_SEQUENCE_ID" field for parts that have similar routings. For parts that have similar routings, the valid routing is the "COMMON_ROUTING_SEQUENCE_ID".

    The question I have is what type of database structure should exist as there are two fields that are possible candiates for linking. When looking at the data in the "BOM_OPERATIONAL_ROUTING" (link from) table in Discoverer, the data in the "ROUTING_SEQUENCE_ID" field is unique, though the data in the "COMMON_ROUTING_SEQUENCE_ID" field is not unique. The field "ROUTING_SEQUENCE_ID" does not contain data in the "BOM_OPERATION_SEQUENCE" (link to) table where data exists in the "COMMON_ROUTING_SEQUENCE_ID" field for the part in "BOM_OPERATIONAL_ROUTING" (link from) table. In other words if you simply link the field "ROUTING_SEQUENCE_ID" that exists between "BOM_OPERATIONAL_ROUTING" and "BOM_OPERATION_SEQUENCE" you will not get operation sequence records for parts that have common routings.

    It is confusing as the Oracle Technical Manual does not spell out this issue in the Diagram 9:Routings or I do not understand how to interpret the relationships.

    Any help in defining the relationship between "BOM_OPERATIONAL_ROUTING" table and "BOM_OPERATION_RESOURCES" table would be greatly appreciated.
     
  2. Sadik

    Sadik Community Moderator Forum Guru

    Messages:
    1,906
    Likes Received:
    252
    Trophy Points:
    1,455
    Hi mcertini

    Routings have operations and operations have resources attached to them. So what you need to do is join your routing to operation table and join operation table with resources table.

    You have routing_sequence_id common between bom_operational_routings and bom_operational_sequences and operation_sequence_id common between bom_operational_sequences and bom_operation_resources. So by finding the operations attached to the routings and resources attached to a operation you can find the resources attached to a routing.

    Hope it helps...
     
  3. mcertini

    mcertini Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    Sadik,

    Thank you for your reply. The issue I have is that I have two fields to choose from in the "BOM_OPERATIONAL_ROUTING" (link from) table in Discoverer, the data in the "ROUTING_SEQUENCE_ID" field is unique, though the data in the "COMMON_ROUTING_SEQUENCE_ID" field is not unique. Because this business uses many common routings for its products it would make sense that I need to link on this field. The problem though is that it has "many" common routing sequence numbers as many products contain the same routing id. If I use the ROUTING_SEQUENCE_ID field to link upon, since many products utilize the COMMON_ROUTING_SEQUENCE_ID field I do not find records in the BOM_OPERATION_SEQUENCE table.

    How it works is that when a non common routing is defined in Oracle, a unique "ROUTING_SEQUENCE_ID" number is originated upon establishment of the new routing that populates the "ROUTING_SEQUENCE_ID" field as well as the "COMMON_ROUTING_SEQUENCE_ID". This takes place in the "BOM_OPERATIONAL_ROUTING" table. There are operation sequences originated in the "BOM_OPERATION_SEQUENCE" table as well.

    When a routing is defined that has a common routing, Oracle puts into its tables a unique "ROUTING_SEQUENCE_ID" number and a "COMMON_ROUTING_SEQUENCE_ID" in the product record in the "BOM_OPERATIONAL_ROUTING" table. In the "BOM_OPERATION_SEQUENCE" table the "ROUTING_SEQUENCE_ID" field is populated with the "COMMON_ROUTING_SEQUENCE_ID" number that was generated in the "BOM_OPERATIONAL_ROUTING" table.

    If I use the "ROUTING_SEQUENCE_ID" field in the "BOM_OPERATIONAL_ROUTING" table I will be missing records for products that have a "COMMON_ROUTING_SEQUENCE_ID" in the "BOM_OPERATION_SEQUENCE" table.

    How do I manage the linking with these two fields.

    I hope I explained this clearly.