详解EBS接口开发之WIP模块接口(共84页).docx
精选优质文档-倾情为你奉上总体说明 文档目的本文档针对WIP模块业务功能和接口进行分析和研究，对采用并发请求方式和调用API方式分别进行介绍 内容WIP模块常用标准表简介WIP事物处理组成WIP相关业务流程WIP相关API研究事例（十）参考文档（七）采购相关的一些知识（一）WIP模块常用标准表简介1.1 常用标准表如下表中列出了与WIP导入相关的表和说明：表名说明其他信息BOM_STRUCTURES_BBOM头信息 BOM_COMPONENTS_BBOM组件信息 BOM_OPERATIONAL_ROUTINGSBOM工艺路线头信息 BOM_OPERATION_SEQUENCESBOM工艺路线生产信息 WIP_ENTITIES工单信息表 WIP_DISCRETE_JOBS离散工单信息表 WIP_REQUIREMENT_OPERATIONS任务领料需求发放表 WIP_OPERATIONS离散作业工序(操作)表 WIP_OPERATION_RESOURCES生产资源表 MTL_MATERIAL_TRANSACTIONS事物处理表 WIP_TRANSACTIONSWIP事物处理表 1.2 接口表大体介绍如下表中列出了与WIP导入相关的接口表和说明：表名说明其他信息mtl_transactions_interface事物处理接口表 mtl_serial_numbers_interface事物处理序列表 mtl_transaction_lots_interface事物处理批次表 cst_comp_snap_interface wip_move_txn_interface移动事物处理接口表 1.3 说明这里只列出了WIP相关的大多数常用表。还有一些不经常用到的没有涉及，具体可以参考oracle网站上的（二）WIP事物处理组成WIP事物处理主要包括：工单生成，工单更改，投料退料，工单移动，工单完工 2.1 WIP事物处理组成工单创建：生成工单工单更改：更改工单信息投料退料：工单的投料以及退料工单移动：工单的正向移动和逆向移动投料退料：工单的完工事物处理 （三）WIP相关业务流程3.1 创建BOM路径：Bill Of Materials > Bills > Bills后台数据 SELECT * FROM bom_structures_b l WHERE l.assembly_item_id = 24815;SELECT * FROM bom_components_b WHERE bill_sequence_id IN (SELECT bill_sequence_id FROM bom_structures_b WHERE assembly_item_id = 24815); 发料方式 Push 推式发料，必须手工通过界面做发料 需严格控制数量或者波动比较大的物料Assembly Pull 拉式发料，装配件完工或报废时自动按标准消耗量触发 消耗比较稳定的物料Operation Pull 拉式发料，工序移动至To Move时自动按标准消耗量触发 消耗比较稳定的物料BOM_COMPONENTS_B.WIP_SUPPLY_TYPE存储发料方式ValueMeaning1Push2Assembly Pull3Operation Pull4Bulk5Supplier6Phantom7Based on Bill 3.2 定义ROUTING路径：Bill Of Materials > Routings> Routings Routings(工艺路线)最终解决的问题是生产过程中加工顺序、资源和用量的标准化。Routing是产品/半成品的生产步骤图，定义了生产特定物料所要经历的工序、加工部门（工作中心）、提前期、耗用的资源及其额定数量。Routing头信息存储在表BOM_OPERATIONAL_ROUTINGS中，Routing的Operations信息存储在BOM_OPERATION_SEQUENCES表中，两个表通过字段ROUTING_SEQUENCE_ID字段关联 SELECT * FROM BOM_OPERATIONAL_ROUTINGS WHERE ASSEMBLY_ITEM_ID = 24815; SELECT * FROM bom_operation_sequences WHERE routing_sequence_id IN (SELECT routing_sequence_id FROM bom_operational_routings WHERE assembly_item_id = 24815); 3.2 定义离散工单路径：WIP > Discrete > DiscreteJobsDiscrete，离散式，一种制造方法，用于装配件的分组或成批制造。制定标准离散任务，输入核心内容：任务名称（工单号）、生产类型、装配件、工单类型、生产数量、开工时间或完工时间。这里在Discrete Job里定义job，保存，并release这个job。 工序 组件涉及到几张表WIP_ENTITIES,WIP_DISCRETE_JOBS,WIP_REQUIREMENT_OPERATIONS和，这几张表都是通过WIP_ENTITY_ID相互关联.<<WIP_ENTITIES>>WIP_ENTITIES stores information about jobs,repetitive assemblies, and flow schedules. Each row includes a unique entityname, the entity type, and the assembly being built. Work in Process uses this information to controlproduction activities and to ensure that entities with duplicate names are notcreated.Key Fields:WIP_ENTITY_ID:Job or schedule IdentifierORGANIZATION_ID:Organization IdentifierWIP_ENTITY_NAME:WIP job or repetitiveassembly name or flow schedule reference codeENTITY_TYPE:WIP entity type code1Discrete job2Repetitive assembly3Closed discrete job4Flow schedulePRIMARY_ITEM_ID:Assembly Item Item <<WIP_DISCRETE_JOBS>>WIP_DISCRETE_JOBS stores discrete jobinformation. Each row represents a discrete job, and contains information aboutthe assembly being built, the revision of the assembly, the job quantity, thestatus of the job, the material control method, accounting information, and jobschedule dates. Oracle Work in Process uses this information to controldiscrete production.Key Fields:WIP_ENTITY_ID:Job or schedule IdentifierORGANIZATION_ID:Organization IdentifierPRIMARY_ITEM_ID:Assembly Item ItemSTATUS_TYPE :Status of job可以通过下边的SQL查得code的意义SELECT lookup_code,meaning FROMFND_LOOKUP_VALUES where LANGUAGE = 'US' AND Upper(lookup_type) LIKE Upper('WIP_JOB_STATUS')ValueMeaning7Cancelled8Pending Bill Load9Failed Bill Load10Pending Routing Load11Failed Routing Load12Closed13Pending - Mass Loaded14Pending Close15Failed Close1Unreleased3Released4Complete5Complete - No Charges6On HoldJOB_TYPE ：Type of discrete jobValueMeaning1Standard3Non-standardWIP_SUPPLY_TYPE ：Method of material consumption within WIPValueMeaning1Push2Assembly Pull3Operation Pull4Bulk5Supplier6Phantom7Based on BillSTART_QUANTITY：Job start quantityQUANTITY_COMPLETED：Current job quantity completedCOMMON_BOM_SEQUENCE_ID：->BOM_COMPONENTS_B.BILL_SEQUENCE_IDCOMMON_ROUTING_SEQUENCE_ID:->BOM_OPERATIONAL_ROUTINGS.ROUTING_SEQUENCE_ID <<WIP_REQUIREMENT_OPERATIONS>>WIP_REQUIREMENT_OPERATIONS storesinformation about the material requirements of jobs and schedules. Each rowrepresents a material requirement and contains information about the componentitem, its usage quantities, the using department, requirement date, and thematerial control method. Oracle Work in Process uses this information to trackthe material usage of jobs and schedules.Key Fields:WIP_ENTITY_ID:Job or schedule IdentifierORGANIZATION_ID:Organization IdentifierINVENTORY_ITEM_ID:Component Item IdCOMPONENT_SEQUENCE_ID:->BOM_COMPONENTS_B.COMPONENT_SEQUENCE_IDWIP_SUPPLY_TYPE ：Method of material consumption within WIPValueMeaning1Push2Assembly Pull3Operation Pull4Bulk5Supplier6Phantom7Based on BillREQUIRED_QUANTITY:Component quantityrequiredQUANTITY_ISSUED:Component quantity issuedSUPPLY_SUBINVENTORY:Subinventory used tosupply component to WIPSUPPLY_LOCATOR_ID:Locator used to supplycomponent to WIPSEGMENT1:Component Item segmentQUANTITY_ALLOCATED:Quantity allocatedQUANTITY_BACKORDERED:Quantity backordered SELECT * FROM WIP_ENTITIES WHERE WIP_ENTITY_NAME = 'J'WIP_ENTITIES.PRIMARY_ITEM_ID= 24815与BOM关联 SELECT * FROM wip_discrete_jobs WHERE wip_entity_id = (SELECT wip_entity_id FROM wip_entities WHERE wip_entity_name = 'J');SELECT * FROM wip_requirement_operations WHERE wip_entity_id = (SELECT wip_entity_id FROM wip_entities WHERE wip_entity_name = 'J');3.2 发料接下来我们就要从库存中给工单发货，这里有两种方法发料到工单方法1：WIP > Material Transactions > WIP Material Transactions这种方式比较简单，直接指定库存并发料方法2：WIP > Discrete > Component Pick Release > Component Pick Releas(Form) / (SRS)当BOM的组件很多，使用方法一发料的话，一个个指定库存会很麻烦， EBS提供了Pick Release的方式来发料，这样用户只要指定合适的Picking Rule,那么系统会自动帮你挑库。系统会产生一个Move Order（Move Order Type：Manufacturing Pick），这一步实际上非常类似于订单的Pick Release。接下来去Transact Move Order界面，Transact这个Move Order就完成发料动作了。注意使用方式一发料的话，后台是不会产生Move Order的，只有方式二才会有Move Order产生。 3.2 事物处理这个时候可以去Material Transaction form里查看，组件已经从库存中扣减，Transaction Type=WIP Issue(Transaction_Type_id:35,WIPcomponent issue) 这个时候MTL_MATERIAL_TRANSACTIONS表中几个JOB相关的字段MMT.Transaction_Type_id:35MMT.TRANSACTION_ACTION_ID:1MMT.TRANSACTION_SOURCE_TYPE_ID:5MMT.TRANSACTION_SOURCE_ID->WIP_DISCRETE_JOBS.WIP_ENTITY_IDMMT.TRX_SOURCE_LINE_ID->WIP_REQUIREMENT_OPERATIONS.OPERATION_SEQ_NUMMMT.SOURCE_LINE_ID ->MTL_TXN_REQUEST_LINES.LINE_IDMMT.Move_Order_line_ID->MTL_TXN_REQUEST_LINES.LINE_ID 3.2 退料物料发到工单后，有可能因为各种原因要把料退回到仓库，可以使用WIP Return路径：WIP > Material Transactions > WIPMaterial Transactions，Type选择 WIP Return查看Material Transaction，就可以看到一条WIP Return的Transaction发生了，并且组件库存数量又恢复到发料前的数量。Transaction_TYPE_ID:43(WIP Component Return) ps:不管是上边所说的WIP Issue还是WIP Return，在WIP_TRANSACTIONS表中是没有数据产生的。WIP_TRANSACTIONS stores information aboutWIP resource transactions.Each row represents a single resource transaction andincludes a uniquetransaction Identifier, a transaction date, the job orrepetitiveschedule charged, the WIP operation and resource charges, andthenumber of units of measure applied. Work in Process uses thisinformation to track resourcecharges and to calculate the values stored in WIP_TRANSACTION_ACCOUNTS.（四）WIP相关API研究事例相关接口字段说明参见参考例子里的说明3.1 工单创建PROCEDUREnew_work_order(p_wip_entity_id NUMBER, p_organization_idNUMBER, x_error_status OUTVARCHAR2, x_error_message OUTVARCHAR2) IS l_iface_rec wip.wip_job_schedule_interface%ROWTYPE; -工单任务接口表 CURSOR c_wdj IS SELECT * FROM cux_wip_discrete_jobs_temp cwdj WHERE cwdj.wip_entity_id =p_wip_entity_id AND cwdj.organization_id =p_organization_id; BEGIN - l_iface_rec.last_update_date := SYSDATE; l_iface_rec.last_updated_by := fnd_global.user_id; l_iface_rec.creation_date := SYSDATE; l_iface_rec.created_by := fnd_global.user_id; - l_iface_rec.group_id := wip.wip_job_schedule_interface_s.nextval; /*= WIP_LOAD_TYPE MFG_LOOKUPS - - 1 Create Standard Job 2 Create Repetitive Schedule 3 Update Discrete Job 4 Create Nonstandard Job =*/ FOR rec_wdj IN c_wdj LOOP g_status_type := rec_wdj.status_type; -根据业务逻辑判断标准非标准工单 IF rec_wdj.job_type = 1 THEN l_iface_rec.load_type := 1; -1标准 ELSE l_iface_rec.load_type := 4; -非标准 END IF; l_iface_rec.allow_explosion := 'Y' l_iface_rec.process_phase := '2' l_iface_rec.process_status := '1' l_iface_rec.status_type := '3'/*rec_wdj.status_type*/ -已发放 l_iface_rec.job_name := rec_wdj.job_num; l_iface_rec.organization_id := rec_wdj.organization_id; l_iface_rec.class_code :=rec_wdj.class_code; l_iface_rec.primary_item_id :=rec_wdj.primary_item_id; l_iface_rec.start_quantity :=rec_wdj.plan_quantity; l_iface_rec.scheduling_method := '1' l_iface_rec.first_unit_start_date :=rec_wdj.scheduled_start_date; l_iface_rec.first_unit_completion_date :=rec_wdj.scheduled_completion_date; l_iface_rec.attribute_category :=rec_wdj.temp_attribute_category; l_iface_rec.attribute1 :=rec_wdj.temp_attribute1; l_iface_rec.attribute2 :=rec_wdj.temp_attribute2; l_iface_rec.attribute3 :=rec_wdj.temp_attribute3; l_iface_rec.attribute4 :=rec_wdj.temp_attribute4; l_iface_rec.attribute5 :=rec_wdj.temp_attribute5; l_iface_rec.attribute6 :=rec_wdj.temp_attribute6; l_iface_rec.attribute7 :=rec_wdj.temp_attribute7; l_iface_rec.attribute8 :=rec_wdj.temp_attribute8; l_iface_rec.attribute9 := rec_wdj.temp_attribute9; l_iface_rec.attribute10 :=rec_wdj.temp_attribute10; l_iface_rec.attribute11 :=rec_wdj.temp_attribute11; l_iface_rec.attribute12 :=rec_wdj.temp_attribute12; l_iface_rec.attribute13 := rec_wdj.temp_attribute13; l_iface_rec.attribute14 :=rec_wdj.temp_attribute14; l_iface_rec.attribute15 :=rec_wdj.temp_attribute15; l_iface_rec.source_code := 'wip test' l_iface_rec.source_line_id :=rec_wdj.job_id; INSERT INTO wip.wip_job_schedule_interfaceVALUES l_iface_rec; END LOOP; -API wip_massload_pub.massloadjobs(p_groupid => l_iface_rec.group_id, -Group ID p_validationlevel => 2, - Validation Level p_commitflag=> 0, - Commit 1 =Yes , 0 =' no ' x_returnstatus=> x_error_status, x_errormsg=> x_error_message); END;3.2 工单更改PROCEDUREchange_work_order_status(p_wip_entity_id NUMBER, p_organization_id NUMBER, p_status_type NUMBER, p_group_idNUMBER := wip.wip_job_schedule_interface_s.nextval, x_error_status OUT VARCHAR2, x_error_message OUT VARCHAR2) IS l_schedule_iface_rec wip.wip_job_schedule_interface%ROWTYPE; -工单任务接口表 -l_group_id NUMBER; /*l_returnstatus VARCHAR2(40); l_errormsg VARCHAR2(2000);*/ l_temp VARCHAR2(3); BEGIN BEGIN SELECT 1 INTO l_temp FROM wip_discrete_jobs wdj WHERE wdj.wip_entity_id =p_wip_entity_id AND wdj.organization_id =p_organization_id AND wdj.status_type = p_status_type; x_error_status := 'S' RETURN; EXCEPTION WHEN no_data_found THEN NULL; END; l_schedule_iface_rec.group_id := p_group_id; - l_schedule_iface_rec.last_update_date := SYSDATE; l_schedule_iface_rec.last_updated_by := fnd_global.user_id; l_schedule_iface_rec.creation_date := SYSDATE; l_schedule_iface_rec.created_by := fnd_global.user_id; - - l_schedule_iface_rec.group_id := l_group_id; /*= WIP_LOAD_TYPE MFG_LOOKUPS - - 1 Create Standard Job 2 Create Repetitive Schedule 3 Update Discrete Job 4 Create Nonstandard Job =*/ l_schedule_iface_rec.load_type := 3; -Update standard or non-standardDiscrete Job l_schedule_iface_rec.process_phase := 2; -Validation l_schedule_iface_rec.process_status := 1; -Pending - l_schedule_iface_rec.wip_entity_id := p_wip_entity_id; l_schedule_iface_rec.organization_id := p_organization_id; l_schedule_iface_rec.status_type := p_status_type; -目标状态 INSERT INTO wip.wip_job_schedule_interface VALUES l_schedule_iface_rec; wip_massload_pub.massloadjobs(p_groupid => p_group_id, - Group ID p_validationlevel => 2, - Validation Level
