详解EBS接口开发之WIP模块接口(共84页).docx

《详解EBS接口开发之WIP模块接口(共84页).docx》由会员分享，可在线阅读，更多相关《详解EBS接口开发之WIP模块接口(共84页).docx（84页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、精选优质文档-倾情为你奉上总体说明文档目的本文档针对WIP模块业务功能和接口进行分析和研究，对采用并发请求方式和调用API方式分别进行介绍内容WIP模块常用标准表简介WIP事物处理组成WIP相关业务流程WIP相关API研究事例（十）参考文档（七）采购相关的一些知识（一）WIP模块常用标准表简介1.1 常用标准表如下表中列出了与WIP导入相关的表和说明：表名说明其他信息BOM_STRUCTURES_BBOM头信息BOM_COMPONENTS_BBOM组件信息BOM_OPERATIONAL_ROUTINGSBOM工艺路线头信息BOM_OPERATION_SEQUENCESBOM工艺路线生产信息WI

2、P_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_trans

3、action_lots_interface事物处理批次表cst_comp_snap_interfacewip_move_txn_interface移动事物处理接口表1.3 说明这里只列出了WIP相关的大多数常用表。还有一些不经常用到的没有涉及，具体可以参考oracle网站上的（二）WIP事物处理组成WIP事物处理主要包括：工单生成，工单更改，投料退料，工单移动，工单完工2.1 WIP事物处理组成工单创建：生成工单工单更改：更改工单信息投料退料：工单的投料以及退料工单移动：工单的正向移动和逆向移动投料退料：工单的完工事物处理（三）WIP相关业务流程3.1 创建BOM路径：Bill Of Mate

4、rials Bills Bills后台数据SELECT*FROMbom_structures_b lWHEREl.assembly_item_id =24815;SELECT*FROMbom_components_bWHEREbill_sequence_idIN (SELECTbill_sequence_idFROMbom_structures_bWHEREassembly_item_id =24815);发料方式Push 推式发料，必须手工通过界面做发料 需严格控制数量或者波动比较大的物料Assembly Pull 拉式发料，装配件完工或报废时自动按标准消耗量触发 消耗比较稳定的物料Oper

5、ation Pull 拉式发料，工序移动至To Move时自动按标准消耗量触发 消耗比较稳定的物料BOM_COMPONENTS_B.WIP_SUPPLY_TYPE存储发料方式ValueMeaning1Push2Assembly Pull3Operation Pull4Bulk5Supplier6Phantom7Based on Bill3.2 定义ROUTING路径：Bill Of Materials Routings RoutingsRoutings(工艺路线)最终解决的问题是生产过程中加工顺序、资源和用量的标准化。Routing是产品/半成品的生产步骤图，定义了生产特定物料所要经历的工序、

6、加工部门（工作中心）、提前期、耗用的资源及其额定数量。Routing头信息存储在表BOM_OPERATIONAL_ROUTINGS中，Routing的Operations信息存储在BOM_OPERATION_SEQUENCES表中，两个表通过字段ROUTING_SEQUENCE_ID字段关联SELECT*FROMBOM_OPERATIONAL_ROUTINGSWHEREASSEMBLY_ITEM_ID =24815;SELECT*FROMbom_operation_sequencesWHERErouting_sequence_idIN (SELECTrouting_sequence_id FR

7、OMbom_operational_routingsWHEREassembly_item_id =24815);3.2 定义离散工单路径：WIP Discrete DiscreteJobsDiscrete，离散式，一种制造方法，用于装配件的分组或成批制造。制定标准离散任务，输入核心内容：任务名称（工单号）、生产类型、装配件、工单类型、生产数量、开工时间或完工时间。这里在Discrete Job里定义job，保存，并release这个job。工序组件涉及到几张表WIP_ENTITIES,WIP_DISCRETE_JOBS,WIP_REQUIREMENT_OPERATIONS和，这几张表都是通过W

8、IP_ENTITY_ID相互关联.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

9、 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

10、_ITEM_ID:Assembly Item ItemWIP_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 jobs

11、chedule 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

12、_VALUESwhere 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_T

13、YPE ：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

14、_ID：-BOM_COMPONENTS_B.BILL_SEQUENCE_IDCOMMON_ROUTING_SEQUENCE_ID:-BOM_OPERATIONAL_ROUTINGS.ROUTING_SEQUENCE_IDWIP_REQUIREMENT_OPERATIONS storesinformation about the material requirements of jobs and schedules. Each rowrepresents a material requirement and contains information about the componentitem

15、, 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:Co

16、mponent 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 issue

17、dSUPPLY_SUBINVENTORY:Subinventory used tosupply component to WIPSUPPLY_LOCATOR_ID:Locator used to supplycomponent to WIPSEGMENT1:Component Item segmentQUANTITY_ALLOCATED:Quantity allocatedQUANTITY_BACKORDERED:Quantity backorderedSELECT*FROMWIP_ENTITIESWHEREWIP_ENTITY_NAME =J;WIP_ENTITIES.PRIMARY_ITE

18、M_ID= 24815与BOM关联SELECT* FROMwip_discrete_jobsWHEREwip_entity_id = (SELECTwip_entity_idFROMwip_entitiesWHEREwip_entity_name =J);SELECT* FROMwip_requirement_operationsWHEREwip_entity_id = (SELECTwip_entity_idFROMwip_entitiesWHEREwip_entity_name =J);3.2 发料接下来我们就要从库存中给工单发货，这里有两种方法发料到工单方法1：WIP Material

19、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 Rel

20、ease。接下来去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

21、: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_ID3.2 退料物料发到工单后

22、，有可能因为各种原因要把料退回到仓库，可以使用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

23、 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

24、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) ISl_iface_rec wip.wip_j

25、ob_schedule_interface%ROWTYPE; -工单任务接口表CURSOR c_wdj ISSELECT *FROM cux_wip_discrete_jobs_temp cwdjWHERE cwdj.wip_entity_id =p_wip_entity_idAND 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_

26、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 Job2 Create Repetitive Schedule3 Update Discrete Job4 Create Nonstandard Job=*/FOR rec_wdj IN c_wdj LOOPg_status_type := rec

27、_wdj.status_type;-根据业务逻辑判断标准非标准工单IF rec_wdj.job_type = 1 THENl_iface_rec.load_type := 1; -1标准ELSEl_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

28、.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_dat

29、e :=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_

30、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.t

31、emp_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

32、_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;-APIwip_massload_pub.massloadjobs(p_groupid = l_iface_rec.group_id, -Group IDp_validationlevel = 2, - Validation Levelp_commitfl

33、ag= 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_er

34、ror_message OUT VARCHAR2) ISl_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);BEGINBEGINSELECT 1INTO l_tempFROM wip_discrete_jobs wdjWHERE wdj.wip_entity_id =p_wip_entity_idAND wdj.orga

35、nization_id =p_organization_idAND wdj.status_type = p_status_type;x_error_status := S;RETURN;EXCEPTIONWHEN no_data_found THENNULL;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_if

36、ace_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 Job2 Create Repetitive Schedule3 Update Discrete Job4 Create Nonstandard Job=*/l_schedule_iface_rec.load_type := 3; -U

37、pdate standard or non-standardDiscrete Jobl_schedule_iface_rec.process_phase := 2; -Validationl_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 IDp_validationlevel = 2, - Validation Level