BOM Operational Routing

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.

well, not sure what you are looking for. It seems you need some relation between tables like Primary Key and Foreign Key. You can normalize the tables based on your requirement but better ask your teacher if the project is educational project.

Thanks,

vnktummala,

Thank you for the reply. Basically I am trying to replicate the relationships that exist in the Oracle table structure within a MS Access environment. Basically what I am doing is taking data from Discoverer tables that were derived from the table relationships that exist in Oracle and downloading them into MS Access. Because of Sarbanes Oxley restrictions, I cannot originate reports in the Discoverer environment so I have to download the data and replicate the structure within MS Access.

I determined a jerry-rigged solution earlier today for MS Access though I am sure it is not the structure that exists in Oracle. I basically created a junction table that includes only one record for each "common_routing_sequence_id" thereby reducing the relationship between the "BOM_Operational_Routing" table and the junction table to a many to one. I then linked the "common_routing_sequence_id" in a one to many relationship out of the juction table to the "BOM_Operation_Sequence" table.

I cannot find how Oracle handles this table structure though I have reviewed the technical manual showing the relationship. It is not spelled out in the technical manual. All I see is a one to many link on "routing_sequence_id" coming from the "BOM_Operational_Routing" to "BOM_Operation_Sequence".

I think poster is using the word "link" with two different meanings.

If I'm correct first use of "link" refers to mapping Oracle tables from MS-Access a.k.a. "link a table"; while second use of the word "link" refers to referential integrity in the Oracle side.

If the question is about how to figure it out referential integrity in Oracle side please ask your Oracle DBA to disclose FKs related to the base tables you want to "link" to MS-Access.

PAVB,

I am looking for direction regarding the linking of two tables. Thank you for your reply.

Quote:

---

Originally Posted by mcertini

...Blah, blah, blah...exist in Oracle and downloading them into MS Access. Because of Sarbanes Oxley restrictions, I cannot originate reports in the Discoverer environment so I have to download the data and replicate the structure within MS Access.
...Etc...

---

1) I do not see where Sarbanes Oxley "restricts" reports generated from the database using whatever tool and which I assume will NOT be used as "official" financial reporting.

2) If you actually are restricted by some "Company policy" (aka. Sarbanes Oxley mis-implementation) then copying the data from Discoverer to MS Access will make no difference; changing the reporting tool still could be considered "restricted".

So, that "restriction" just seems a bunch of baloney (BS) to me.
:rolleyes:

LKBrwn_DBA,

Sarbanes Oxley revolves around internal controls. In other words you have to prove to auditors that your company has controls in place to protect investors from material misstatements that could appear in financial statements. As for the company I work for, they have an internal control that is related to access rights to an area where Discoverer reports are saved. In other words, various Discoverer reports have been presented to auditors, they have approved the company's financials based upon the analytical content of these reports as well as other information. The company wants restricted access to this directory or area to ensure that a person does not make a misstake and modify a report in this directory. This is what has been explained to me by management.

The issue revolves around security over the existing Discoverer reports not the data that the reports were based upon.

God Bless.

You make my point...As long as you do not save the reports you generate in the "official" reporting area, you still can use Discoverer as your tool.

Also, in Discoverer you can have data sets that are/were generated from views and joins independent from the actual database views and joins created in the schema.

Good luck!
;)