Manufacturing Distribution Open Interface Open Interface Architecture u

Manufacturing & Distribution Open Interface ®

Open Interface Architecture u Three different methods for import and export data(inbound and outbound) Interface Tables(In) 4 Interface Views(Out) 4 Function Calls or Programmatic Interfaces(In/Out) 4 ®

Interface Tables u Requires some validation through a concurrent program 4 You can write directly to the tables if you are familiar with them ®

Interface Views u Views simplify the data relationships for easier processing 4 Ex: mtl_item_quantities_view u Defined in Technical Reference Manual u Dynamic Views for Oracle Quality ®

Function Calls u Tight integration without adding a batch process to move data Ex: ECO API 4 Ex: PO API 4 ®

Inbound Interface Model Source Application Load Maintain Interface Table or Database View Validate Errors Table Process Destination Application ®

Open Interface Components u Source Application u Errors Table u Destination Application u Database View u Interface Table u Load Function u Identifier Columns u Control Columns u Data Columns 4 4 4 Required Columns Derived Columns Optional Columns 4 4 sql loader to temp table temp to interface u Validate Function u Process Function u Maintain Function 4 Ex: open interface window ®

Open Interfaces u Oracle Inventory Item Import 4 Transaction Interface 4 Replenishment 4 Cross-Reference 4 Cycle Count 4 Kanban 4 Lot 4 Reservation 4 Move Order u Oracle Purchasing Requisition Interface 4 Purchase Interface 4 Receiving Interface 4 4 u Oracle Bill of Material BOM Import 4 Routing Import 4 Substitution Import 4 Designator Import 4 ®

Open Interfaces u Oracle Work in Process Job Import 4 Move Transaction 4 Resoruce Transaction u 4 u Forecast Import 4 MPS/MDS Import ECO Interface Order Management Order Import 4 Sales Order API 4 Oracle Master Scheduling 4 Oracle Engineering u Oracle Quality 4 Result Import ®

Item Import u Interface Table 4 u Updated table 4 u mtl_system_items Concurrent Program 4 u mtl_system_items_interface u Required data 4 4 4 item_number organization_code description process_flag(1, Pending) transaction_type(CREATE) Import Items Errors Table 4 mtl_interface_errors ®

Item Table Relationship MTL_DESCRIPTIVE_ELEMENTS MTL_ITEM_CATALOG_GROUPS • ITEM_CATALOG_GROUP_ID • ELEMENT_NAME • INVENTORY_ITEM_ID • ORGANIZATION_ID MTL_DESCR_ELEMENT_VALUES • ELEMENT_NAME • INVENTORY_ITEM_ID MTL_SYSTEM_ITEMS • INVENTORY_ITEM_ID, • ORGANIZATION_ID MTL_CATEGORY_SETS • CATEGORY_SET_ID MTL_ITEM_CATAGORIES • STRUCTURE_ID • CATEGORY_SET_ID • INVENTORY_ITEM_ID • ORGANIZATION_ID FND_ID_FLEX_STRUCTURES • STRUCTURE_ID • CATEGORY_ID MTL_CATEGORIES • STRUCTURE_ID • CATEGORY_ID ®

Transaction Interface u Interface table 4 u Updated table 4 u mtl_material_transactions Concurrent Program 4 u mtl_transactions_interface Process transaction interface Errors Table 4 4 mtl_transaction_interface use pending transaction window to resubmit u Required data 4 4 4 source_code source_header_id source_line_id process_flag transaction_mode organization_id inventory_item_id transaction_quantity transaction_uom transaction_date transaction_type_id who columns ®

Transaction Table Relationship MTL_MATERIAL_TRANSACTIONS MTL_TRANSACTION_TYPES • TRANSACTION_TYPE_ID • TRANSACTION_SOURCE_TYPE_ID • SUBINVENTORY_CODE MTL_SECONDARY_INVENTORIES • SUBINVENTORY_CODE • LOCATOR_ID MTL_TRANSACTION_SOURCE_TYPES • TRANSACTION_SOURCE_TYPE_ID MTL_SYSTEM_ITEMS • INVENTORY_ITEM_ID • TRANSACTION_ID Cost Manager • INVENTORY_ITEM_ID MTL_ITEM_LOCATIONS • SUBINVENTORY_CODE MTL_TRANSACTION_ACCOUNTS • INVENTORY_LOCATION_ID • ITRANSACTION_ID • REFERENCE_ACCOUNT • TRANSACTION_VALUE ®

Requisition Interface u Interface Table 4 u Updated table 4 4 4 u po_requisition_headers po_requisition_lines po_req_distributions Concurrent Program 4 u po_requisitions_interface_a ll Requisition Import Errors Table 4 po_interface_errors u Required data 4 4 4 4 4 Who Columns interface_source_code source_type_code requisition_type destination_type_code item_id quantity unit_price authorization_status prepare_id four accounts uom code need_by_date gl_date org_id destination_organization_id deliver_to_location_id deliver_to_requestor_id ®

PO/PR Table Relationships PO_HEADERS_ALL PO_REQUISITION_HEADERS_ALL • REQUISITION_HEADER_ID PO_VENDORS • VENDOR_ID • HEADER_ID • VENDOR_SITE_ID PO_REQUISITION_LINES_ALL PO_VENDOR_SITES_ALL PO_LINES_ALL • REQUISITION_HEADER_ID • VENDOR_ID • HEADER_ID • REQUISITION_LINE_ID • VENDOR_SITE_ID • PO_LINE_ID • VENDOR_ID • ITEM_ID • VENDOR_SITE_ID INVENTORY_ITEM_ID PO_LINE_LOCATIONS_ALL PO_REQ_DISTRIBUTIONS_ALL • REQUISITION_LINE_ID • DISTRIBUTION_ID • PO_LINE_ID MTL_SYSTEM_ITEMS • LINE_LOCATION_ID • INVENTORY_ITEM_ID PO_DISTRIBUTIONS_ALL • PO_LINE_ID • LINE_LOCATION_ID • PO_DISTRIBUTION_ID ®

PO Open Interface u Interface Table 4 4 u Updated table 4 4 u po_headers po_line_locations po_distributions Global API 4 u po_interface_headers po_interface_lines po_interface_s. create_docu ments Errors Table u Required data(Header) 4 4 4 interface_header_id interface_source_id batch_id action document_type_code document_subtype group_code vendor_id vendor_site_id agent_id org_id who columns ®

PO Open Interface u Required Data(Lines) 4 4 4 interface header id interface line id action line_num shipment num who columns ®

PO/PR Table Relationships PO_HEADERS_ALL PO_REQUISITION_HEADERS_ALL • REQUISITION_HEADER_ID PO_VENDORS • VENDOR_ID • HEADER_ID • VENDOR_SITE_ID PO_REQUISITION_LINES_ALL PO_VENDOR_SITES_ALL PO_LINES_ALL • REQUISITION_HEADER_ID • VENDOR_ID • HEADER_ID • REQUISITION_LINE_ID • VENDOR_SITE_ID • PO_LINE_ID • VENDOR_ID • ITEM_ID • VENDOR_SITE_ID INVENTORY_ITEM_ID PO_LINE_LOCATIONS PO_REQ_DISTRIBUTIONS_ALL • REQUISITION_LINE_ID • DISTRIBUTION_ID • PO_LINE_ID MTL_SYSTEM_ITEMS • LINE_LOCATION_ID • INVENTORY_ITEM_ID PO_DISTRIBUTIONS • PO_LINE_ID • LINE_LOCATION_ID • PO_DISTRIBUTION_ID ®

Receiving Interface u Interface Table 4 4 u Updated table 4 4 4 u rcv_transactions rcv_shipment_headers rcv_shipment_lines Concurrent Program 4 u rcv_header_interface rcv_transactions_interface u Required data(header) 4 4 4 4 header interface id group id processing status code receipt source code transaction type who columns vendor name validation flag Receiving Transaction Processor Errors Table 4 mtl_interface_errors ®

Receiving Interface u Required Data 4 4 4 interface transaction id group id who columns transaction type transaction date processing status code processing mode code transaction status code quantity unit of measure item description u Required Data 4 4 4 4 auto transact code receipt source code vendor id source document code po header id header interface id validation flag ®

Receiving Table Relationship PO_HEADERS_ALL RCV_SHIPMENT_HEADERS • SHIPMENT_HEADER_ID • RECEIPT_NUM RCV_TRANSACTIONS • HEADER_ID • TRANSACTION_ID • TRANSACTION_TYPE • PO_HEADER_ID RCV_SHIPMENT_LINES • PO_LINE_ID • SHIPMENT_HEADER_ID • SHIIPMENT_LINE_ID • SHIPMENT_LINE_ID • PO_LINE_LOCATION_ID • LOCATION_ID PO_LINES_ALL • HEADER_ID • PO_LINE_ID • INVENTORY_ITEM_ID PO_LINE_LOCATIONS_ALL • PO_LINE_ID • LINE_LOCATION_ID ®

BOM Header Import u Interface Table 4 u 4 Bill and Routing Interface Errors Table 4 process_flag select lookup_code, meaning from mfg_lookups where lookup_type = 'BOM_INTERFACE_STATUS' bom_bill_of_materials Concurrent Program 4 u Required data Updated table 4 u bom_bill_of_mtls_interface u 4 4 4 mtl_interface_errors 4 organization_id assembly_item_id assembly_type – 1 (m-bom), 2(e-bom) transaction_type – Create, Delete , Update ®

BOM Component Import u Interface Table 4 u Updated table 4 u bom_inventory_components Concurrent Program 4 u bom_inventory_comps_inter face Bill and Routing Interface u Required data 4 4 4 4 component_item_id component_sequence_id operation_seq_num effectivity_date bill_sequence_id transaction_type process_flag Errors Table 4 mtl_interface_errors ®

BOM Designator Import u Interface Table 4 u Updated table 4 u bom_reference_designators Concurrent Program 4 u bom_ref_desgs_interface u Required data 4 4 component_reference_desog antor component_sequence_id process_flag transaction_type Bill and Routing Interface Errors Table 4 mtl_interface_errors ®

BOM Substitution Import u Interface Table 4 u Updated table 4 u bom_substitute_components Concurrent Program 4 u bom_sub_comps_interface u Required data 4 4 4 substitute_component_id substitute_item_quantity component_sequence_id process_flag transaction_type Bill and Routing Interface Errors Table 4 mtl_interface_errors ®

BOM Table Relationship BOM_BILL_OF_MATERIALS 1 bill_sequence_id n BOM_INVENTORY_COMPONENTS 1 n 1 component_sequence_id BOM_SUBSTITUTE_COMPONENTS n BOM_REFERENCE_DESIGNATORS ®

Routing Header Import u Interface Table 4 u Updated table 4 u bom_operational_routings Concurrent Program 4 u bom_op_routings_interfac e u Required data 4 4 4 process_flag organization_id assembly_item_id routing_type transaction_type Bill and Routing Interface Errors Table 4 mtl_interface_errors ®

Routing Operation Import u Interface Table 4 u Updated table 4 u bom_operation_sequences Concurrent Program 4 u bom_op_sequences_interfa ce Bill and Routing Interface u Required data 4 4 4 process_flag routing_sequence_id operation_seq_num department_id effectivity_date transaction_type Errors Table 4 mtl_interface_errors ®

Routing Resource Import u Interface Table 4 u Updated table 4 u bom_operation_resources Concurrent Program 4 u bom_op_resources_interfa ce u Required data 4 4 4 process_flag resource_seq_num resource_id operation_sequence_id transaction_type Bill and Routing Interface Errors Table 4 mtl_interface_errors ®

Routing Table Relationship BOM_OPERATIONAL_ROUTINGS 1 routing_sequence_id n BOM_OPERATION_SEQUENCES 1 operation_sequence_id n BOM_OPERATION_RESOURCES ®

Forecast Interface u Interface Table 4 u Updated table 4 4 u mrp_forecast_items mrp_forecast_dates Concurrent Program 4 u mrp_forecast_interface u Required data 4 4 4 planning manager Errors Table 4 mrp_forecast_interface 4 organization_id forecast_designator inventory_item_id forecast_date quantity process_status – mfg_lookup code – MRP_INTERFACE_PROCESS_TYPE confidence_percentage ®

MPS/MDS Interface u Interface Table 4 u Updated table 4 4 u mrp_schedule_items mrp_schedule_dates Concurrent Program 4 u mrp_schedule_interface u Required data 4 4 4 organization_id schedule_designator inventory_item_id schedule_date schedule_quantity process_status planning manager Errors Table 4 mrp_schedule_interface ®

MRP Table Relationship MRP_SCHEUDLE_DESIGNATORS • SCHEDULE_DESIGNATOR MRP_PLANS • COMPILE_DESIGNATOR MRP_DESIGNATORS • COMPILE_DESIGNATOR MRP_SCHEDULE_DATES MRP_SCHEDULE_ITEMS • SCHEDULE_DESIGNATOR • INVENTORY_ITEM_ID • IVENTORY_ITEM_ID MTL_SYSTEM_ITEMS • INVENTORY_ITEM_ID • SCHEDULE_DATE BOM_CALENDAR_DATES • CALENDAR_DATE ®

WIP Job Import u Interface Table 4 u Updated table 4 4 4 u wip_entities wip_discrete_jobs wip_operations wip_requirement_operations wip_operation_resources Concurrent Program 4 u wip_job_schedule_interface WIP Mass Load Errors Table 4 wip_interface_errors u Required data 4 4 4 group_id process_type – code: wip_job_status organization_id load_type (std, rep, non-std) status_type(release, etc) primary_item_id job_name start_quantity process_phase(2) process_status(1) first_unit_start_date who columns ®

Move Transaction Interface u Interface Table 4 u Updated table 4 4 u wip_move_transactions wip_cost_txn_interface others Concurrent Program 4 u wip_move_txn_interface WIP Move Transaction Manager u Required data 4 4 4 4 4 process_phase process_status organization_code transaction_date fm_operation_seq_num fm_intraoperation_step_type to_operation_seq_num to_intraoperation_step_type transaction_quantity transaction_uom Errors Table 4 wip_move_txn_interface ®

Resource Transaction Interface u Interface Table 4 u Updated table 4 4 u wip_transactions others Required data 4 4 4 Concurrent Program 4 Cost Management 4 4 u wip_cost_txn_interface u Errors Table 4 wip_cost_txn_interface 4 4 4 process_phase process_status transaction_type organization_code wip_entity_name transaction_date operation_seq_num resource_seq_num transaction_quantity transaction_uom ®

WIP Job Table Relationship WIP_ENTITIES 1 wip_entity_id n WIP_DISCRETE_JOBS 1 n wip_entity_id WIP_OPERATIONS 1 wip_entity_id 1 n operation_seq_num n WIP_OPERATION_RESOURCES WIP_REQUIREMENT_OPERATIONS ®

WIP Transaction Relationship Batch move transaction update on line move transaction update WIP_MOVE_TRANSACTIONS WIP_MOVE_TXN_INTERFACE WIP_TRANSACTIONS WIP transaction manager select when cost_update_id is null or -1 Cost Manager accumulate cost when same account_period_id and wip_entity_id WIP_PERIOD_BALANCES WIP_TRANSACTION_ACCOUNTS WIP_COST_TXN_INTERFACE OSP PO Delivered ®

ECO Import u Interface Table 4 4 4 u Update Table 4 4 4 u eng_changes_interface eng_revised_items_interface bom_inventory_comps_inter face bom_ref_desgs_interface bom_sub_comps_interface eng_engineering_changes eng_revised_items eng_revised_components u Required Data 4 4 ECO Header – ECO Number – organization id – status type – change order type ECO Revised Item – Change Number – Revised Item id – effectivity date Global API ®

ECO Table Relationship ENG_CHANGE_ORDER_TYPES BOM_BILL_OF_MATERIALS ENG_ENGINEERING_CHANGES BOM_INVENTORY_COMPONENTS ENG_REVISED_ITEMS Implement BOM SUBSTITUTE BOM REFERENCE COMPONENTS DESIGNATORS ENG_REVISED_COMPONENTS ®

Quality u Interface 4 QA_RESULTS_INTERFACE – q_<plan_name>_iv u Manager 4 Collection Import Manager ®

q_<plan_name>_iv u Updated table 4 u Required data 4 Concurrent Program 4 u qa_results u collection import manager Errors Table 4 qa_interface_errors 4 4 4 process_status – pending, runnint, – error, complete organization_code plan_name insert_type – 1, null: insert – 2: update ®

Sales Order Import u Inteface Tables 4 4 u Update Tables 4 4 u oe_headers_iface_all oe_lines_interface om_order_headers_all om_order_lines_all Concurrent Program 4 u Required Data(Headers) 4 4 4 orig_sys_document_ref order type order source tax who columns operation code Order Import ®

Order Import u Required Data(Lines) 4 4 4 4 4 order source id orig_sys_document_ref orig_sys_line_ref orig_sys_shipment_ref inventory item requested date delivery lead time delivery id ordered quantity order quantity uom ®

Sales Order API u Sales Order Object 4 4 4 4 4 Headers Order Price Adjustments Order Sales Credits Line Price Adjustments Line Sales Credits Lot Serial Number Pricing Attributes Adjustment Association u Operations(Process_Order) 4 4 4 Pricing Scheduling/Reservation Return Lines Sets(Ship, Arrival, fulfillment) Tax Book Split Cancel Apply Automatic Attachments Apply Hold Release Hold Delink Config ®