详解EBS接口开发之WIP模块接口

1、总体说明文档目的本文档针对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工单信

2、息表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_i

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

4、lls > Bills蛆件详细信息 物料控制 订单管理 采购 发运 &ECO主要有效日期行效单位物料序号工序物料说明组件楹块f翼29864 050内蒙古-兴安盟社保卡基版后台数据SELECT* FROM bom_structures_b l WHERE l.assemblyjtemjd =24815;SELECT*FROM bom_components_bWHERE bill_sequence_id IN(SELECTbill_sequence_idFROM bom_structures_bWHERE assembly_item_id =24815 );发料方式Push推式发料，

5、必须手工通过界面做发料需严格控制数量或者波动比较大的物料Assembly Pull拉式发料，装配件完工或报废时自动按标准消耗量触发消耗比较稳定的物料Operation Pull 拉式发料，工序移动至To Move时自动按标准消耗量触发 消耗比较稳定的物料BOM_COMPONENTS_B.WIP_SUPPLY_TYPE 存储发料方式ValueMeaning1Push2Assembly Pull3Operation Pull4Bulk5Supplier6Phantom7Based on Bill3.2 定义 ROUTING路径：Bill Of Materials > RoutingsRout

6、ingsD2014-57-17 1G；52：1371F部门选件相关rrrrr内蒙古-兴安盟社保卡NK298岑4050,置蜜盟）-未今隹可承诺能力将来和当前有京日期参考序号代码r .|jkdoo2r.JKD002r.|rr|rl工艺路统详知资料（D）日期工序产出辜源期工艺路线版本（5）Routings（工艺路线）最终解决的问题是生产过程中加工顺序、资源和用量的标 准化。Routing是产品/半成品的生产步骤图，定义了生产特定物料所要经历的 工序、加工部门（工作中心）、提前期、耗用的资源及其额定数量。Routing 头信息存储在表 BOM_OPERATIONAL_ROUTINGS 中，Routin

7、g 的Operations 信息存储在BOM_OPERATION_SEQUENCES 表中，两个表通过字段 ROUTING_SEQUENCE_ID 字段关联SELECT* FROM BOM_OPERATIONAL_ROUTINGS WHERE ASSEMBLY_ITE M_ID = 24815;SELECT*FROM bom_operation_sequencesWHERE routing_sequence_id IN(SELECT routing_sequence_idFROM bom_operational_routings WHERE assembly_item_id =24815 );

8、3.2定义离散工单路径：WIP > Discrete > DiscreteJobsDiscrete ,离散式，一种制造方法，用于装配件的分组或成批制造。制定标准离散任务，输入核心内容：任务名称(工单号)、生产类型、装配件、工单类型、生产数量、开工时间或完工时间。这里在Discrete Job 里定义job ,保存，并release这个job。工序Ib 救里日期说明V 序号部门自动计费检宣技能1上次移动日期10SHY1 JKD002VD17r0Z0IJ02 JKD0027口lzr0rrrr_ rrrr'' rrrrrrrr1i厂r最小工序代码计数点胤冲 传法11组件涉

9、及到几张表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. Oracle W

10、ork 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 flowschedule reference

11、codeENTITY_TYPE:WIP entity type code1D iscrete 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

12、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 Identifier ORGANIZATION_I

13、D:Organization IdentifierPRIMARY_ITEM_ID:Assembly Item ItemSTATUS_TYPE Status of job可以通过下边的SQL查得code的意义SELECT lookup_code,meaning FROMFND_LOOKUP_VALUESwhere LANGUAGE = 'US' AND Upper(lookup_type) LIKEUpper('WIP_JOB_STATUS')ValueM eaning7Cancelled8Pending Bill Load9Failed Bill Load10P

14、ending Routing Load11Failed Routing Load12Closed13Pending - Mass Loaded14Pending Close15Failed Close1Unreleased3Released4Complete5Complete - No Charges6On HoldJOB_TYPE : Type of discrete jobValueMeaning1Standard3Non-standardWIP_SUPPLY_TYPE : Method of material consumption within WIPValueMeaning1Push

15、2Assembly 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

16、_REQUIREMENT_OPERATIONS>>WIP_REQUIREMENT_OPERATIONS storesinformation about the materialrequirements 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

17、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 Identifier ORGANIZATION_ID:Organization Identifier INVENTORY_ITEM_ID:Component Item Id COMPONENT_SEQUENCE_ID:->BOM_COMPONENTS_B.COMPONENT_SEQUENCE

18、_ID WIP_SUPPLY_TYPE : Method of material consumption within WIPValueM eaning1Push2Assembly Pull3Operation Pull4Bulk5Supplier6Phantom7Based on BillREQUIRED_QUANTITY:Component quantityrequiredQUANTITY_ISSUED:Component quantity issuedSUPPLY_SUBINVENTORY:Subinventory used tosupply component toWIPSUPPLY_

19、LOCATOR_ID:Locator used to supplycomponent to WIPSEGMENT1:Component Item segmentQUANTITY_ALLOCATED:Quantity allocatedQUANTITY_BACKORDERED:Quantity backorderedSELECT* FROM WIP_ENTITIES WHERE WIP_ENTITY_NAME = 'J2010113'WIP_ENTITIES.PRIMARY_ITEM_ID= 24815 与 BOM 关联SELECT*FROM wip_discrete_jobsW

20、HERE wip_entity_id =(SELECTwip_entity_idFROM wip_entitiesWHERE wip_entity_name ='J2010113');SELECT*FROM wip_requirement_operationsWHERE wip_entity_id =(SELECTwip_entity_idFROM wip_entitiesWHERE wip_entity_name ='J2010113');3.2 发料接下来我们就要从库存中给工单发货，这里有两种方法发料到工单方法 1 : WIP > Material T

21、ransactions > WIP Material Transactions这种方式比较简单，直接指定库存并发料方法 2 : WIP > Discrete > Component Pick Release > Component PickReleas(Form) / (SRS)当BOM的组件很多，使用方法一发料的话，一个个指定库存会很麻烦,Oracle EBS提供了 Pick Release的方式来发料，这样用户只要指定合适的Picking Rule,那么系统会自动帮你挑库。系统会产生一个 Move Order (Move Order Type : Manufactu

22、ringPick),这一步实际上非常类似于订单的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)移动事为处理(LZB)工，工，稍售订单订单行事与处

23、理类型 口移动(此三成(C)退货(?)生产线装配件任务序号代后自10着单范步骤10部门排队移动事务处理明超量完成何)施科帐户日期别名编号2014-07-18 10:59:06原因参考J存在人工资源(0状态(8)资源(K)保存G)。町F耳工A库现）-C工YjmjrirjHSTt CCYjfr信件三。三。1工工三三。三m长也三工长也三工七三：这个时候MTL_MATERIAL_TRANSACTIONS 表中几个JOB相关的字段MMT.Transaction_Type_id:35MMT.TRANSACTION_ACTION_ID:1MMT.TRANSACTION_SOURCE_TYPE_ID:5MMT

24、.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 退料物料发到工单后，有可能因为各种原因要把料退回到仓库，可以使用 WIPReturn路径：WIP > Ma

25、terial 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 a

26、boutWIP resourcetransactions.Each row represents a single resource transactionandincludes a uniquetransaction Identifier, a transaction date, the jobOracle Work in Process usesorrepetitiveschedule charged, the WIP operation and resource charges, andthenumber of units of measure applied.thisinformati

27、on to track resourcecharges and to calculate the valuesstored 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_job_schedule_interfa

28、ce%ROWTYPE;-工单任务接 口表CURSOR c_wdj ISSELECT *FROM cux_wip_discrete_jobs_temp cwdjWHERE cwdj.wip_entity_id =p_wip_entity_idAND anization_id =p_organization_id;BEGINl_iface_rec.last_update_date := SYSDATE;l_iface_rec.last_updated_by := fnd_global.user_id;l_iface_rec.creation_date := SYSDATE;l_if

29、ace_rec.created_by := fnd_global.user_id;l_iface_rec.group_id := wip.wip_job_schedule_interface_s.nextval;/*WIP_LOAD_TYPE MFG_LOOKUPS1 Create Standard Job2 Create Repetitive Schedule3 Update Discrete Job4 Create Non -standard Job=*/FOR rec_wdj IN c_wdj LOOPg_status_type := rec_wdj.status_type;-根据业务逻

30、辑判断标准非标准工单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_cess_phase := '2'l_iface_cess_status := '1'l_iface_rec.status_type := '3'/*rec_wdj.status_type7”-已

31、发放l_iface_rec.job_name := rec_wdj.job_num;l_iface_anization_id := rec_anization_id;l_iface_rec.class_code krec_wdj.class_code;l_iface_rec.primary_item_id :=rec_wdj.primary_item_id;l_iface_rec.start_quantity krec_wdj.plan_quantity;l_iface_rec.scheduling_method := '1'l_iface_rec.

32、first_unit_start_date :=rec_wdj.scheduled_start_date;l_iface_rec.first_unit_completion_date 尸rec_wdj.scheduled_completi on_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_r

33、ec.attribute3 krec_wdj.temp_attribute3;l_iface_rec.attribute4 krec_wdj.temp_attribute4;l_iface_rec.attribute5 krec_wdj.temp_attribute5;l_iface_rec.attribute6 krec_wdj.temp_attribute6;l_iface_rec.attribute7 krec_wdj.temp_attribute7;l_iface_rec.attribute8 krec_wdj.temp_attribute8;l_iface_rec.attribute

34、9 := 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.attribu

35、te15 尸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;-APIwip_massload_pub.massloadjobs(p_groupid => l_iface_rec.group_id,-Group IDp_validationlevel => 2, - V

36、alidation Levelp_commitflag=> 0, - Commit 1 =Yes , 0 =' nox_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.

37、nextval,x_error_status OUT VARCHAR2,x_error_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

38、_entity_id =p_wip_entity_idAND anization_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

39、_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_LOOKUPS1 Create Standard Job2 Create Repetitive Schedule3 Update Discrete Job4 Create Non -standard

40、Job=*/l_schedule_iface_rec.load_type := 3; -Update standard or nonstandardDiscrete Jobl_schedule_iface_cess_phase := 2; -Validationl_schedule_iface_cess_status := 1; -Pendingl_schedule_iface_rec.wip_entity_id := p_wip_entity_id;l_schedule_iface_anization_id := p_organization_id;

41、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 Levelp_commitflag=> 0, - Commit 1 =Yes , 0 ='No'x_retu

42、rnstatus=> x_error_status,x_errormsg=> x_error_message);END;3.3工单移动工单创建完成后，若不进行移动事务处理，则无法进行完工事务处理。移动数 量需要按照完工数量的多少进行移动。完工事务处理中有超量完工的概念，因 此，移动事务处理也有超量移动。需要一个function 获取可移动数量/*=* FUNCTION / PROCEDURE* get_available_to_move_qty* DESCRIPTION:* 获取可移动数量* ARGUMENT:* RETURN: * HISTORY:* 1.00 2014-7-11

43、 cxy*/FUNCTIONget_available_to_move_qty(p_wip_entity_id IN NUMBER,p_opr_seq_num IN NUMBER,p_organization_id IN NUMBER,p_intraopr_step IN NUMBER)RETURN NUMBER ISl_available_to_move_qty NUMBER;CURSOR csr_wip_operations ISSELECT decode(p_intraopr_step,1,wo.quantity_in_queue,2,wo.quantity_running,3,wo.q

44、uantity_waiting_to_move,4,wo.quantity_rejected,5,wo.quantity_scrapped,wo.quantity_completed)FROM wip_operations woWHERE wo.wip_entity_id = p_wip_entity_idAND wo.operation_seq_num =p_opr_seq_numAND anization_id =p_organization_idAND wo.repetitive_schedule_id ISNULL;BEGINOPEN csr_wip_operations;

45、FETCH csr_wip_operationsINTO l_available_to_move_qty;CLOSE csr_wip_operations;RETURN l_available_to_move_qty;END get_available_to_move_qty;- -Call API Process WIP Move Transaction-Fixed:-TRANSACTION_ID/GROUP_ID/PROCESS_PHASE/PROCESS_STATUS not valid. Please re-enter-SELECT group_id- -FROM wip_move_t

46、xn_interface- -WHERE transaction_id = p_txn_id- -AND process_phase = WIP_CONSTANTS.MOVE_VAL- -AND process_status = WIP_CONSTANTS.RUNNING- -AND group_id IS NOT NULL;- -Doc ID: 363753.1-PROCEDUREprocess_wip_move_txn(p_transaction_id IN NUMBER,p_commit IN VARCHAR2 := fnd_api.g_false, x_return_status IN

47、 OUT VARCHAR2, x_error_message IN OUT VARCHAR2)IS* -PRAGMA AUTONOMOUS_TRANSACTION;l_groupid NUMBER;l_errbuf VARCHAR2(1000);l_retcode NUMBER;BEGIN* -Call the procedure/* This procedure will be used to do move,easy-return, easy- completion, and* scrap transaction for Discrete, OSFMjobs, and Repetitive

48、 Schedule. The* caller need to insert the record intoWIP_MOVE_TXN_INTERFACE before calling* this routine. Caller can generatewmti.transaction_id and wmti.group_id* from the sequence wip_transactions_s.Caller need to insert both group_id* and transaction_id before calling theprocedure below. These 2

49、columns* should have to same value. Callershould always insert cess_phase =* (Validation) to make sure that thedata inserted is valid, and insert* cess_status = 2(Running) toprevent Move Manager from picking up* this record.* PARAMETER:* p_txn_idtransaction_id inWIP_MOVE_TXN_INTERFAC

50、E* p_do_backflush this parameter determine whethermoveprocesor has to* backflush pull componentor not. Some customers use*third party software toinsert backflush components, sothey do not want moveprocessor to backflush themagain. The defaultvalue is null. If the callers do not pass this parameter o

51、rpass fnd_api.g_true, we will backflush pull component.Otherwise, we will not backflush them. pass fnd_api.g_true orfnd_api.g_false.* p_commmitcommit the change to the databaseifsuccesfullyprocessing ? pass* xreturnStatusfnd_api.g_true orfnd_api.g_falseThere are 2 possible values*fnd_api.g_ret_sts_s

52、uccess NOTE:* 1. This procedure should be called ifcaller want to process one record at* a time such as Online transaction.* 2. This procedure will returnfnd_api.g_ret_sts_unexp_error if this records* failed. The caller can check the error message from x_errorMsg.means the movetransaction succesfull

53、y processed *fnd_api.g_ret_sts_unexp_error* means anexception occurredThe size of this variableshould be VARCHAR2(1)* x_errorMsgcontains the error message onfailure. null onsuccess.The size of this variableshould be VARCHAR2(1000)because there may beerrors in several columns.* 3. The caller does not

54、 have to insertchild record for overmove/* overcompletion. This API will take care everything. The caller does not* have to call QA API either.*/cux_conc_utl.log_msg(p_msg => ' Begin CallWip_movproc_cessinterface()');l_groupid := p_transaction_id;"SELECT group_idINTO l_groupidFROM wip_move_txn_interfaceWHERE transaction_id = p_transaction_idAND process_phase =wip_constants.move_valAND process_status =wip_constants.runningAND g