詳解EBS接口開發(fā)之WIP模塊接口

1、總體說明 文檔目的本文檔針對WIP模塊業(yè)務(wù)功能和接口進(jìn)行分析和研究，對采用并發(fā)請求方式和調(diào)用API方式分別進(jìn)行介紹 內(nèi)容WIP模塊常用標(biāo)準(zhǔn)表簡介WIP事物處理組成WIP相關(guān)業(yè)務(wù)流程WIP相關(guān)API研究事例（十）參考文檔（七）采購相關(guān)的一些知識（一）WIP模塊常用標(biāo)準(zhǔn)表簡介1.1   常用標(biāo)準(zhǔn)表如下表中列出了與WIP導(dǎo)入相關(guān)的表和說明：表名說明其他信息BOM_STRUCTURES_BBOM頭信息   BOM_COMPONENTS_BBOM組件信息   BOM_OPERATIONAL_ROUTING

2、SBOM工藝路線頭信息   BOM_OPERATION_SEQUENCESBOM工藝路線生產(chǎn)信息   WIP_ENTITIES工單信息表   WIP_DISCRETE_JOBS離散工單信息表   WIP_REQUIREMENT_OPERATIONS任務(wù)領(lǐng)料需求發(fā)放表   WIP_OPERATIONS離散作業(yè)工序(操作)表   WIP_OPERATION_RESOURCES生產(chǎn)資源表   

3、MTL_MATERIAL_TRANSACTIONS事物處理表   WIP_TRANSACTIONSWIP事物處理表              1.2  接口表大體介紹如下表中列出了與WIP導(dǎo)入相關(guān)的接口表和說明：表名說明其他信息mtl_transactions_interface事物處理接口表   mtl_serial_numbers_interface事物處理序列表 

4、0; mtl_transaction_lots_interface事物處理批次表   cst_comp_snap_interface    wip_move_txn_interface移動事物處理接口表                       1.3   說明這里只

5、列出了WIP相關(guān)的大多數(shù)常用表。還有一些不經(jīng)常用到的沒有涉及，具體可以參考o(jì)racle網(wǎng)站上的（二）WIP事物處理組成WIP事物處理主要包括：工單生成，工單更改，投料退料，工單移動，工單完工 2.1  WIP事物處理組成工單創(chuàng)建：生成工單工單更改：更改工單信息投料退料：工單的投料以及退料工單移動：工單的正向移動和逆向移動投料退料：工單的完工事物處理    （三）WIP相關(guān)業(yè)務(wù)流程3.1   創(chuàng)建BOM路徑：Bill Of Materials > Bills > Bills后臺數(shù)據(jù) SELECT

6、 * FROM bom_structures_b l WHERE l.assembly_item_id = 24815;SELECT *                  FROM bom_components_b          

7、60;       WHERE bill_sequence_id IN                       (SELECT bill_sequence_id         

8、0;       FROM bom_structures_b                 WHERE assembly_item_id = 24815); 發(fā)料方式 Push 推式發(fā)料，必須手工通過界面做發(fā)料 需嚴(yán)格控制數(shù)量或者波動比較大的物料Assembly Pull 拉式發(fā)料，裝配件完工或報廢時自動按

9、標(biāo)準(zhǔn)消耗量觸發(fā) 消耗比較穩(wěn)定的物料Operation Pull 拉式發(fā)料，工序移動至To Move時自動按標(biāo)準(zhǔn)消耗量觸發(fā) 消耗比較穩(wěn)定的物料BOM_COMPONENTS_B.WIP_SUPPLY_TYPE存儲發(fā)料方式ValueMeaning1Push2Assembly Pull3Operation Pull4Bulk5Supplier6Phantom7Based on Bill 3.2   定義ROUTING路徑：Bill Of Materials > Routings> Routings  Routings(工藝路線)最終解決的問

10、題是生產(chǎn)過程中加工順序、資源和用量的標(biāo)準(zhǔn)化。Routing是產(chǎn)品/半成品的生產(chǎn)步驟圖，定義了生產(chǎn)特定物料所要經(jīng)歷的工序、加工部門（工作中心）、提前期、耗用的資源及其額定數(shù)量。Routing頭信息存儲在表BOM_OPERATIONAL_ROUTINGS中，Routing的Operations信息存儲在BOM_OPERATION_SEQUENCES表中，兩個表通過字段ROUTING_SEQUENCE_ID字段關(guān)聯(lián) SELECT * FROM BOM_OPERATIONAL_ROUTINGS WHERE ASSEMBLY_ITEM_ID =

11、  24815;      SELECT *                  FROM bom_operation_sequences               &#

12、160;  WHERE routing_sequence_id IN                       (SELECT routing_sequence_id             

13、60;    FROM bom_operational_routings                 WHERE assembly_item_id = 24815); 3.2   定義離散工單路徑：WIP > Discrete > DiscreteJobsDiscrete，離散式，一種制造方法，用于裝配件的分組或成批制造。

14、制定標(biāo)準(zhǔn)離散任務(wù)，輸入核心內(nèi)容：任務(wù)名稱（工單號）、生產(chǎn)類型、裝配件、工單類型、生產(chǎn)數(shù)量、開工時間或完工時間。這里在Discrete Job里定義job，保存，并release這個job。 工序 組件涉及到幾張表WIP_ENTITIES,WIP_DISCRETE_JOBS,WIP_REQUIREMENT_OPERATIONS和，這幾張表都是通過WIP_ENTITY_ID相互關(guān)聯(lián).<<WIP_ENTITIES>>WIP_ENTITIES stores information about jobs,repetitive assemblies, and f

15、low schedules. Each row includes a unique entityname, the entity type, and the assembly being built. Oracle 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 Identif

16、ierORGANIZATION_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_

17、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 P

18、rocess 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 = &

19、#39;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 ：

20、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：-

21、>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 requireme

22、nt 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 IdentifierORGANIZA

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

24、uantityrequiredQUANTITY_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

25、0;* FROM WIP_ENTITIES WHERE WIP_ENTITY_NAME = 'J2010113'WIP_ENTITIES.PRIMARY_ITEM_ID= 24815與BOM關(guān)聯(lián)     SELECT *             FROM wip_discrete_jobs    

26、60;       WHERE wip_entity_id =                 (SELECT wip_entity_id           FROM wip_entities   

27、        WHERE wip_entity_name = 'J2010113');SELECT *             FROM wip_requirement_operations WHERE wip_entity_id =       &#

28、160;         (SELECT wip_entity_id           FROM wip_entities           WHERE wip_entity_name = 'J2010113');3.2 

29、0; 發(fā)料接下來我們就要從庫存中給工單發(fā)貨，這里有兩種方法發(fā)料到工單方法1：WIP > Material Transactions > WIP Material Transactions這種方式比較簡單，直接指定庫存并發(fā)料方法2：WIP > Discrete > Component Pick Release > Component Pick Releas(Form) / (SRS)當(dāng)BOM的組件很多，使用方法一發(fā)料的話，一個個指定庫存會很麻煩，Oracle EBS提供了Pick Release的方式來發(fā)料，這樣用戶只要指定合適的Picking Rule,

30、那么系統(tǒng)會自動幫你挑庫。系統(tǒng)會產(chǎn)生一個Move Order（Move Order Type：Manufacturing Pick），這一步實際上非常類似于訂單的Pick Release。接下來去Transact Move Order界面，Transact這個Move Order就完成發(fā)料動作了。注意使用方式一發(fā)料的話，后臺是不會產(chǎn)生Move Order的，只有方式二才會有Move Order產(chǎn)生。  3.2   事物處理這個時候可以去Material Transaction form里查看，組件已經(jīng)從庫存中扣減，Transaction Type=WIP

31、Issue(Transaction_Type_id:35,WIPcomponent issue) 這個時候MTL_MATERIAL_TRANSACTIONS表中幾個JOB相關(guān)的字段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.OPERA

32、TION_SEQ_NUMMMT.SOURCE_LINE_ID ->MTL_TXN_REQUEST_LINES.LINE_IDMMT.Move_Order_line_ID->MTL_TXN_REQUEST_LINES.LINE_ID 3.2   退料物料發(fā)到工單后，有可能因為各種原因要把料退回到倉庫，可以使用WIP Return路徑：WIP > Material Transactions > WIPMaterial Transactions，Type選擇 WIP Return查看Material Transaction，就可以看到一條WIP

33、Return的Transaction發(fā)生了，并且組件庫存數(shù)量又恢復(fù)到發(fā)料前的數(shù)量。Transaction_TYPE_ID:43(WIP Component Return) ps:不管是上邊所說的WIP Issue還是WIP Return，在WIP_TRANSACTIONS表中是沒有數(shù)據(jù)產(chǎn)生的。WIP_TRANSACTIONS stores information aboutWIP resource transactions.Each row represents a single resource transaction andincludes a uniquetransaction

34、 Identifier, a transaction date, the job orrepetitiveschedule charged, the WIP operation and resource charges, andthenumber of units of measure applied. Oracle Work in Process uses thisinformation to track resourcecharges and to calculate the values stored in WIP_TRANSACTION_ACCOUNTS.（四）WI

35、P相關(guān)API研究事例相關(guān)接口字段說明參見參考例子里的說明3.1  工單創(chuàng)建PROCEDUREnew_work_order(p_wip_entity_id NUMBER,                                   &

36、#160;             p_organization_idNUMBER,                                &

37、#160;                x_error_status OUTVARCHAR2,                            

38、0;                     x_error_message OUTVARCHAR2) IS   l_iface_rec wip.wip_job_schedule_interface%ROWTYPE; -工單任務(wù)接口表   CURSOR c_wdj IS     

39、   SELECT *             FROM cux_wip_discrete_jobs_temp cwdj            WHERE cwdj.wip_entity_id =p_wip_entity_id        

40、;            AND anization_id =p_organization_id; BEGIN   -   l_iface_rec.last_update_date := SYSDATE;   l_iface_rec.last_updated_by := fnd_global.user_id;   l_iface_r

41、ec.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    - - 

42、0;  1 Create Standard Job    2 Create Repetitive Schedule    3 Update Discrete Job    4 Create Nonstandard Job    =*/   FOR rec_wdj IN c_wdj LOOP        g_

43、status_type := rec_wdj.status_type;        -根據(jù)業(yè)務(wù)邏輯判斷標(biāo)準(zhǔn)非標(biāo)準(zhǔn)工單        IF rec_wdj.job_type = 1 THEN              l_iface_rec.load_type := 1; -1標(biāo)準(zhǔn) 

44、60;         ELSE              l_iface_rec.load_type := 4; -非標(biāo)準(zhǔn)           END IF;        l_

45、iface_rec.allow_explosion := 'Y'        l_iface_cess_phase := '2'        l_iface_cess_status := '1'        l_iface_rec.status_type := '3&#

46、39;/*rec_wdj.status_type*/             -已發(fā)放        l_iface_rec.job_name := rec_wdj.job_num;        l_iface_anization_id := rec_anization_id; 

47、       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;  &

48、#160;     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_c

49、ompletion_date;        l_iface_rec.attribute_category :=rec_wdj.temp_attribute_category;        l_iface_rec.attribute1 :=rec_wdj.temp_attribute1;        l_iface_rec.attribute2 :=re

50、c_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.tem

51、p_attribute5;        l_iface_rec.attribute6 :=rec_wdj.temp_attribute6;        l_iface_rec.attribute7 :=rec_wdj.temp_attribute7;        l_iface_rec.attribute8 :=rec_wdj.temp_attribu

52、te8;        l_iface_rec.attribute9 := rec_wdj.temp_attribute9;        l_iface_rec.attribute10 :=rec_wdj.temp_attribute10;        l_iface_rec.attribute11 :=rec_wdj.temp_attribute11;

53、        l_iface_rec.attribute12 :=rec_wdj.temp_attribute12;        l_iface_rec.attribute13 := rec_wdj.temp_attribute13;        l_iface_rec.attribute14 :=rec_wdj.temp_attribute14;&#

54、160;       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;   

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

56、;                                                  

57、;    p_validationlevel => 2, - Validation Level                                      &#

58、160;                          p_commitflag=> 0, - Commit 1 =Yes , 0 =' no '              

59、                                                  

60、 x_returnstatus=> x_error_status,                                           &

61、#160;                      x_errormsg=> x_error_message); END;3.2   工單更改PROCEDUREchange_work_order_status(p_wip_entity_id NUMBER,       &

62、#160;                                                 &

63、#160;          p_organization_id NUMBER,                                   

64、                                 p_status_type NUMBER,             

65、60;                                                 

66、60;     p_group_idNUMBER := wip.wip_job_schedule_interface_s.nextval,                                  &#

67、160;                                  x_error_status OUT VARCHAR2,          

68、0;                                                 

69、0;       x_error_message OUT VARCHAR2) IS   l_schedule_iface_rec wip.wip_job_schedule_interface%ROWTYPE; -工單任務(wù)接口表   -l_group_id NUMBER;   /*l_returnstatus VARCHAR2(40);    l_errormsg VARCHAR2(2000);*/

70、   l_temp VARCHAR2(3); BEGIN   BEGIN        SELECT 1             INTO l_temp             FROM w

71、ip_discrete_jobs wdj            WHERE wdj.wip_entity_id =p_wip_entity_id                    AND anization_id =p_organization_id 

72、                   AND wdj.status_type = p_status_type;        x_error_status := 'S'        RETURN;   

73、 EXCEPTION        WHEN no_data_found THEN              NULL;       END;   l_schedule_iface_rec.group_id := p_group_id;   

74、;-   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;   

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

76、 Job    4 Create Nonstandard Job    =*/   l_schedule_iface_rec.load_type := 3; -Update standard or non-standardDiscrete Job   l_schedule_iface_cess_phase := 2; -Validation   l_schedule_iface_cess_statu

77、s := 1; -Pending   -   l_schedule_iface_rec.wip_entity_id := p_wip_entity_id;   l_schedule_iface_anization_id := p_organization_id;   l_schedule_iface_rec.status_type := p_status_type; -目標(biāo)狀態(tài)   INSERT INTO wip.wip_job_s

78、chedule_interface VALUES l_schedule_iface_rec;   wip_massload_pub.massloadjobs(p_groupid => p_group_id, - Group ID                            

79、                                    p_validationlevel => 2, - Validation Level      

80、60;                                                 

81、60;        p_commitflag=> 0, - Commit 1 =Yes , 0 ='No'                                

82、60;                                x_returnstatus=> x_error_status,           

83、0;                                                 

84、0;   x_errormsg=> x_error_message); END; 3.3  工單移動 工單創(chuàng)建完成后，若不進(jìn)行移動事務(wù)處理，則無法進(jìn)行完工事務(wù)處理。移動數(shù)量需要按照完工數(shù)量的多少進(jìn)行移動。完工事務(wù)處理中有超量完工的概念，因此，移動事務(wù)處理也有超量移動。需要一個function 獲取 可移動數(shù)量      /*=   *  FUNCTION / PROCEDURE   *

85、60;     get_available_to_move_qty   *  DESCRIPTION:   *       獲取可移動數(shù)量   *  ARGUMENT:            *  RETURN

86、:   *         *  HISTORY:   *       1.00 2014-7-11 cxy*=*/FUNCTIONget_available_to_move_qty(p_wip_entity_id  IN NUMBER,        

87、60;                             p_opr_seq_num     IN NUMBER,            

88、60;                        p_organization_id IN NUMBER,                     

89、;                p_intraopr_step   IN NUMBER)      RETURN NUMBER IS        l_available_to_move_qty NUMBER;     

90、0;  CURSOR csr_wip_operations IS         SELECT decode(p_intraopr_step,                       1,     

91、0;                 wo.quantity_in_queue,                       2,      

92、;                 wo.quantity_running,                       3,      &

93、#160;                wo.quantity_waiting_to_move,                       4,     &

94、#160;                 wo.quantity_rejected,                       5,     &#

95、160;                 wo.quantity_scrapped,                       wo.quantity_completed)  &

96、#160;        FROM wip_operations wo          WHERE wo.wip_entity_id = p_wip_entity_id            AND wo.operation_seq_num =p_opr_seq_num  &#

97、160;         AND anization_id =p_organization_id            AND wo.repetitive_schedule_id ISNULL;    BEGIN      OPEN csr_wip_operations;

98、60;     FETCH csr_wip_operations         INTO l_available_to_move_qty;      CLOSE csr_wip_operations;      RETURN l_available_to_move_qty;    END get_

99、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_txn_inte

100、rface   -   WHERE transaction_id = p_txn_id   -     AND process_phase = WIP_CONSTANTS.MOVE_VAL   -     AND process_status = WIP_CONSTANTS.RUNNING   -    &#

101、160;AND group_id IS NOT NULL;   -Doc ID:  363753.1   -PROCEDUREprocess_wip_move_txn(p_transaction_id IN NUMBER,                         

102、;         p_commit         IN VARCHAR2 := fnd_api.g_false,                          &

103、#160;       x_return_status  IN OUT VARCHAR2,                                 x_error_message&#

104、160; IN OUT VARCHAR2)IS      -PRAGMA AUTONOMOUS_TRANSACTION;      l_groupid NUMBER;      l_errbuf VARCHAR2(1000);      l_retcode NUMBER;  BEGIN   

105、0;  - Call the procedure     /*      * This procedure will be used to do move,easy-return, easy-completion, and      * scrap transaction for Discrete, OSFMjobs, and Repetitive Schedule. The  

106、0;   * 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 columns      * should have to same value. Callershould always insert cess_phase =      * 1