Copy BOM and Rounting

1. Interface table

A.BOM

BOM_BILL_OF_MTLS_INTERFACE(BOM Header)( BOM_BILL_OF_MATERIALS)

BOM_INVENTORY_COMPS_INTERFACE(BOM component)( BOM_INVENTORY_COMPONENTS)

BOM_SUB_COMPS_INTERFACE(Component substitute)

MTL_ITEM_REVISIONS_INTERFACE(Item Revision)( MTL_ITEM_REVISIONS)

B.Rounting

BOM_OP_ROUTINGS_INTERFACE(Rounting Header)( BOM_OPERATIONAL_ROUTINGS)

BOM_OP_SEQUENCES_INTERFACE(Rounting Operation)( BOM_OPERATION_SEQUENCES)

BOM_OP_RESOURCES_INTERFACE(Rounting Resource)( BOM_OPERATION_RESOURCES)

MTL_RTG_ITEM_REVS_INTERFACE (Rounting Revision)( MTL_RTG_ITEM_REVISIONS)

BOM_OP_NETWORKS_INTERFACE(Operation Network)

BOM_SUB_OP_RESOURCES_INTERFACE(Substitute operation Resource)

C.Others

BOM_REF_DESGS_INTERFACE(Alternate BOM and Rounting)( BOM_ALTERNATE_DESIGNATORS)

BOM_INTERFACE_DELETE_GROUPS(BOM_DELETE_GROUPS)

MTL_INTERFACE_ERRORS(所有interface出錯的信息都被存在這里)

BOM_COMPONENT_OPS_INTERFACE(不了解)

注意點:帶有紅色字體的Interface table是這次用到的,像MTL_ITEM_REVISIONS_INTERFACE和MTL_RTG_ITEM_REVS_INTERFACE在COPY時

不用考慮.原因是BOM的版本也就是料號的版本,也就是料號建立時候,將來BOM版本就默認為料號的版本,制程版本是建立制程時候,系統自動建立

一個A版本給這個制程

2. Import program

Once you load the data into the interface tables, you can launch the Bill and Routing Interface program (BMCOIN)

from the Import Bills and Routings form in Oracle Bills of Material or Oracle Engineering. This program assigns values,

validates the data you include, and then imports the new routings and new bills of material (BOMs)

A. 路徑: BOM responsibility BillsààImportà Bill and Routing Interface

B. 參數說明

All Organizations:à Yes: Run the interface for all organization codes in the bill of material and routing interface tables.

:àNo: Run the Interface program only for the organization the user is in. The interface program only processes

bill of material and routing interface records in the current organization.

Import Routing: à Yes: Import records from the routing interface tables for the current organization or all organizations.

à No: Do not import records from the routing interface tables.

Import Bills of Material:àYes: Import records from the bills of material interface tables for the current organization or all organization.

àNo: Do not import records from the bills of material interface tables.

Delete Processed Rows:àYes: Delete successfully processed rows from the bill of material and routing interface tables.

àNo: Leave all records in the bill of material and routing interface tables for successfully processed rows.

注意點: BOM和制程可以同時導入,但BOM料件要指定的operation一定要存在,不存在時operation_num默認為1;

標準BOM和替代BOM也可以同時導入,但替代BOM導入之前標準BOM一定存在,標準制程和替代制程也是如此;

工程BOM和工程制程也可以用此程式導入,但其與生產BOM和生產制程區別是ASSEMBLY_TYPE和ROUTING_TYPE值在導入時的設定;

3. Setting up for Bills of Material / Routing Import

There are no additional setup steps in Oracle Bills of Material or Oracle Engineering beyond those already required to manually

define manufacturing and engineering bills of material.

· All items and standard comments must be defined before they can be assigned to a manufacturing or engineering bill of material.

· Standard operations, resources and standard instructions must be defined in Oracle Bills of Material before they can be assigned

to a manufacturing or engineering routing.

· Since the Bill and Routing Interface program is launched and managed through the concurrent manager, it must be ensured that

the concurrent manager is running before importing any bills.

注意點:此處主要說明是基礎資料的建立是COPY過程前提條件,COPY制程和BOM的過程有大量的工作用來驗證

目標ORG和源ORG中基礎資料是否一致,以及它的正確性

4. Transaction Type

Transaction_Type column determines the action to be taken to taken. It has following possible values:

· Create - To create bill of material or a Product family or create a routings with operations and resources

· Update - To update bill of material or routing.

· Delete - To Delete bill of material or routing.

注意點:在oracle application 11.5.9或以上版本,用”Create”創建制程沒有問題,11.5.9以下的版本用”Insert”來創建制程,其他的命令不變.

5. Validating and Resolving Failed Interface Table Rows

The Bill and Routing Interface program validate the required data for 6 (Bills) and 5 (Routing) interface tables. Bill of material validation ensures that

each row has an included or defaulted value for all required columns and verifies any interdependent relationships.

When a bill is imported, the Bill and Routing Interface program validate all rows in the Interface tables that have a PROCESS_FLAG set to 1 (PENDING)

If the Bill and Routing Interface program cannot assign a value to a row or validate that row, the program sets the PROCESS_FLAG for the row to 3

(Assign/Validation failed) and inserts a row in the MTL_INTERFACE_ERRORS table.

If the Bill and Routing Interface program cannot insert the row into one of the production tables, the program sets the

PROCESS_FLAG for that row to 4 (Import Failed).

After the Bill and Routing Interface program successfully creates a bill of Material and components or a routing in the production tables,

it sets the PROCESS_FLAG to 7 (Import Succeeded).

To identify the error message for a failed row, the Interface program automatically populates the UNIQUE_ID column in the error interface table

with the same value as the TRANSACTION_ID value. Each error also has a value for MESSAGE_NAME and REQUEST_ID columns in the error interface table.

The MESSAGE_NAME column corresponds to message stored in Oracle Application Message Dictionary and REQUEST_ID column

stores the concurrent request id. If the program detects any internal database error, the program stores the internal error in the MESSAGE_NAME column

and stores the specific database error message in the ERROR_MESSAGE column.

If a bill of material with multiple components / operations is imported and one of the components / operation fails validation,

the Bill and Routing Interface program imports the bill of material/routing and other validates components.

The program only fails the record where the error occurred. If however, the row in the BOM_BILL_OF_MTLS_INTERFACE/BOM_OP_ROUTINGS_INTERFACE

table fails the bill of material and all of its details (routing and any operation, resource) are not imported.

The rows in the interface tables can be reviewed and reported using SQL*Plus. Since all rows in the interface table have a value for PROCESS_FLAG,

records can be easily identified that are successfully imported into Oracle Bills of Material and Oracle Engineering, or records that

failed validation or import. These records can be identified by the unique value for the TRANSACTION_ID column.

SELECT ERR.TRANSACTION_ID,ERROR_MESSAGE, MESSAGE_NAME

FROM MTL_INTERFACE_ERRORS ERR,

BOM_BILL_OF_MTLS_INTERFACE BOI

WHERE ERR.TRANSACTION_ID = BOI.TRANSACTION_ID

Any row from the interface tables can be updated using SQL*Plus. If a row is updated to resolve invalid data, the PROCESS_FLAG

must be set to 1 (Pending) for that row. When the Bill and Routing Interface is resubmitted all rows pending validation are processed.

注意點: Interface table中有一個欄位PROCESS_FLAG

1 = Pending(無論對BOM和制程的create,update,delete)

3 = Assign/Validation failed

4 = Import Failed

7 = Import Succeeded

注意點:當導入的過程中有錯誤發生時,就用上述SQL可以查詢出來錯誤的原因;

如錯誤發生在COPY制程和BOM的單頭時候,數據是完全不能導入的;若錯誤發生在單身,僅僅是單身中某一部分成功,並不影響其他的單身導入;

.

6. BOM_ INTERFACE_DELETE_GROUPS

When deleting a Bill, Component, Routing, Operation or Resource a record must be inserted in

BOM_INTERFACE_DELETE_GROUPS with the following values:

· ENTITY_NAME =

BOM_BILL_OF_MTLS_INTERFACE

BOM_INVENTORY_COMPS_INTERFACE

BOM_OP_ROUTINS_INTERFACE

BOM_OP_SEQUENCES_INTERFACE

BOM_OPRESOURCES_INTERFACE

· DELETE_GROUP_NAME New name or name of an existing delete group.

· DESCRIPTION Required if using a new delete group.

· Deleting a Component would require Delete Group information. However for deleting a Product Family Member Delete Group Information is not needed.

注意點:經我的測試,刪除Routing的程式有兩種,而刪除BOM只有一種寫法:

刪除Rounting: A. INSERT INTO BOM_INTERFACE_DELETE_GROUPS (entity_name,delete_group_name,Description)

VALUES('BOM_OP_ROUTINGS_INTERFACE','Jarli_del','BOM_OP_ROUTINGS_INTERFACE')

其中delete_group_name可以為新的或已經存在的

B.INSERT INTO BOM_OP_ROUTINGS_INTERFACE

(assembly_item_id,organization_id,routing_type,transaction_type,process_flag,delete _group_name)

VALUES (165684,13,1,'Delete',1,’Jarli_del’);

其中delete_group_name一定存在

刪除BOM: INSERT INTO bom_bill_of_mtls_interface

(organization_id, assembly_item_id, Assembly_type, Transaction_type, Process_flag,delete_group_name )

VALUES (13,191446,1,'Delete',1,’BOM_DEL’); 其中delete_group_name一定存在

當刪除BOM和制程的單頭時,其對應的單身也會一同刪除;對單身的刪除動作只影響其本身.

7. Inserting into the INTERFACE table

The data that is loaded in the above mentioned interface table can be broadly be classified into Three types –Required Data, Derived Data and Optional Data.

Required Data - Columns marked as required must always be entered.

Derived data - are those columns which the interface program automatically imports the row with the derived or defaulted value.

For example, CREATION_DATE column is a derived data column. Bill and Routing Interface program automatically defaults to the current date.

Some of the Interface table contain user-friendly columns that allows the user to enter data easily in the interface table.

The Bill and Routing Interface program uses the values entered for these user-friendly columns to derive unique identifiers.

To import a bill of material, a value for ORGANIZATION_CODE or ORGANIZATION_ID can be included. If ORGANIZATION_CODE is entered,

the Bill and Routing Interface derives the value for ORGANIZATION_ID.

Optional Data – The user can assign a value or let the Bill and Routing Interface program default a value.

注意:插入Interface表中數據欄位有三種: Required Data, Derived Data and Optional Data.

這里僅僅說明一下所用到Derived Data

A. BOM_BILL_OF_MTLS_INTERFACE

Organization_Code-------à(Organization_id)

Item_Number -------à (Assembly_Item_Id)

Common_Org_Code ----à (Common_Organization_Id)

Common_Item_Numberà( Common_Assembly_Item_Id)

common_bill_sequence_idà( Common_Item_Id,Common_Organization_Id,Alternate_Bom_Designator)

Bill_Sequence_Id--à( Assembly_Item_Id,Organization_Id,Alternate_Bom_Designator)

Bill_Sequence_Id--à( Assembly_Item_Id,Organization_Id)

B. BOM_INVENTORY_COMPS_INTERFACE

Organization_Codeà Organization_Id

Assembly_Item_Number àAssembly_Item_Id

Component_Item_Numberà Component_Item_Id

Substitute_Comp_Number àSubstitute_Comp_Id

Location_Name-à Supply_Locator_id

Component_Sequence_Idà(Bill_sequence_id,Component_item_id,Operation_seq_num,Effectivity_date)

Bill_Sequence_Id--à( Assembly_Item_Id,Organization_Id,Alternate_Bom_Designator)

C. BOM_REF_DESGS_INTERFACE

Component_Sequence_Idà(Bill_sequence_id,Component_item_id,Operation_seq_num,Effectivity_date)

Bill_Sequence_Id--à( Assembly_Item_Id,Organization_Id,Alternate_Bom_Designator)

D. BOM_SUB_COMPS_INTERFACE

Component_Sequence_Idà(Bill_sequence_id,Component_item_id,Operation_seq_num,Effectivity_date)

Bill_Sequence_Id--à( Assembly_Item_Id,Organization_Id,Alternate_Bom_Designator)

E. MTL_ITEM_REVISIONS_INTERFACE: The Bill and Routing Interface program does not allow the deleting of item revisions.

F.BOM_OP_ROUTINGS_INTERFACE

Organization_Codeà Organization_Id

Assembly_Item_Number àAssembly_Item_Id

Common_Item_Numberà Common_Assembly_Item_Id

Location_Nameà Completion_Locator_Id

Line_codeà Line_id

Routing_Sequence_Idà Assembly_Item_Id,Organization_Id,Alternate_Routing_Designator

G.BOM_OP_SEQUENCES_INTERFACE

Operation_Codeà Standard_Operation_Id

Department_Codeà Department_Id

Organization_Codeà Organization_Id

Assembly_Item_Numberà Assembly_Item_Id

Resoure_Code1à Resource_Id1

Resoure_Code2à Resource_Id2

Resoure_Code3à Resource_Id3

Operation_Sequence_Idà Operation_Seq_Num,Effectivity_Date,Routing_Sequence_Id

Routing_Sequence_Idà Assembly_Item_Id,Organization_Id,Alternate_Routing_Designator

H. BOM_OP_RESOURCES_INTERFACE

Assembly_Item_Numberà Assembly_Item_Id

Resoure_Codeà Resource_Id

Organization_Codeà Organization_Id

Activityà Activity_Id

Operation_Sequence_Idà Operation_Seq_Num,Effectivity_Date,Routing_Sequence_Id

Routing_Sequence_Idà Assembly_Item_Id,Organization_Id,Alternate_Routing_Designator

I. MTL_RTG_ITEM_REVS_INTERFACE

8. CREATING BOM and ROUTING

I.Create Routing 的過程(參考文件CreateRouting.txt)

II.Create BOM的過程(參考文件CreateBOM.txt)