1 / 45

Manufacturing & Distribution Open Interface

Manufacturing & Distribution Open Interface. Open Interface Architecture. Three different methods for import and export data(inbound and outbound) Interface Tables(In) Interface Views(Out) Function Calls or Programmatic Interfaces(In/Out). Interface Tables.

kittym
Download Presentation

Manufacturing & Distribution Open Interface

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Manufacturing & Distribution Open Interface

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

  3. Interface Tables • Requires some validation through a concurrent program • You can write directly to the tables if you are familiar with them

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

  5. Function Calls • Tight integration without adding a batch process to move data • Ex: ECO API • Ex: PO API

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

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

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

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

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

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

  12. Interface table mtl_transactions_interface Updated table mtl_material_transactions Concurrent Program Process transaction interface Errors Table mtl_transaction_interface use pending transaction window to resubmit Required data 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 Interface

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

  14. Interface Table po_requisitions_interface_all Updated table po_requisition_headers po_requisition_lines po_req_distributions Concurrent Program Requisition Import Errors Table po_interface_errors Required data 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 Requisition Interface

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

  16. Interface Table po_interface_headers po_interface_lines Updated table po_headers po_lines po_line_locations po_distributions Global API po_interface_s.create_documents Errors Table po_interface_errors Required data(Header) 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

  17. Required Data(Lines) interface header id interface line id action line_num shipment num who columns PO Open Interface

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

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

  20. Required Data 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 Required Data auto transact code receipt source code vendor id source document code po header id header interface id validation flag Receiving Interface

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

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

  23. Interface Table bom_inventory_comps_interface Updated table bom_inventory_components Concurrent Program Bill and Routing Interface Errors Table mtl_interface_errors Required data component_item_id component_sequence_id operation_seq_num effectivity_date bill_sequence_id transaction_type process_flag BOM Component Import

  24. Interface Table bom_ref_desgs_interface Updated table bom_reference_designators Concurrent Program Bill and Routing Interface Errors Table mtl_interface_errors Required data component_reference_desogantor component_sequence_id process_flag transaction_type BOM Designator Import

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

  26. 1 n 1 1 n n BOM Table Relationship BOM_BILL_OF_MATERIALS bill_sequence_id BOM_INVENTORY_COMPONENTS component_sequence_id BOM_SUBSTITUTE_COMPONENTS BOM_REFERENCE_DESIGNATORS

  27. Interface Table bom_op_routings_interface Updated table bom_operational_routings Concurrent Program Bill and Routing Interface Errors Table mtl_interface_errors Required data process_flag organization_id assembly_item_id routing_type transaction_type Routing Header Import

  28. Interface Table bom_op_sequences_interface Updated table bom_operation_sequences Concurrent Program Bill and Routing Interface Errors Table mtl_interface_errors Required data process_flag routing_sequence_id operation_seq_num department_id effectivity_date transaction_type Routing Operation Import

  29. Interface Table bom_op_resources_interface Updated table bom_operation_resources Concurrent Program Bill and Routing Interface Errors Table mtl_interface_errors Required data process_flag resource_seq_num resource_id operation_sequence_id transaction_type Routing Resource Import

  30. 1 n 1 n Routing Table Relationship BOM_OPERATIONAL_ROUTINGS routing_sequence_id BOM_OPERATION_SEQUENCES operation_sequence_id BOM_OPERATION_RESOURCES

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

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

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

  34. Interface Table wip_job_schedule_interface Updated table wip_entities wip_discrete_jobs wip_operations wip_requirement_operations wip_operation_resources Concurrent Program WIP Mass Load Errors Table wip_interface_errors Required data 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 WIP Job Import

  35. Interface Table wip_move_txn_interface Updated table wip_move_transactions wip_transactions wip_cost_txn_interface others Concurrent Program WIP Move Transaction Manager Errors Table wip_move_txn_interface Required data 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 Move Transaction Interface

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

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

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

  39. Interface Table eng_eng_changes_interface eng_revised_items_interface bom_inventory_comps_interface bom_ref_desgs_interface bom_sub_comps_interface Update Table eng_engineering_changes eng_revised_items eng_revised_components Global API ENG_Eco_PUB Required Data ECO Header ECO Number organization id status type change order type ECO Revised Item Change Number Revised Item id effectivity date ECO Import

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

  41. Quality • Interface • QA_RESULTS_INTERFACE • q_<plan_name>_iv • Manager • Collection Import Manager

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

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

  44. Required Data(Lines) 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 Order Import

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

More Related