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內蒙古-興安盟社?？∟K298岑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