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

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

2、IONAL_ROUTINGSBOM工艺路线头信息   BOM_OPERATION_SEQUENCESBOM工艺路线生产信息   WIP_ENTITIES工单信息表   WIP_DISCRETE_JOBS离散工单信息表   WIP_REQUIREMENT_OPERATIONS任务领料需求发放表   WIP_OPERATIONS离散作业工序(操作)表   WIP_OPERATION_RESOURCES生产资源表 

3、;  MTL_MATERIAL_TRANSACTIONS事物处理表   WIP_TRANSACTIONSWIP事物处理表              1.2  接口表大体介绍如下表中列出了与WIP导入相关的接口表和说明：表名说明其他信息mtl_transactions_interface事物处理接口表   mtl_serial_numbers_interface事物处理

4、序列表   mtl_transaction_lots_interface事物处理批次表   cst_comp_snap_interface    wip_move_txn_interface移动事物处理接口表                       1.3 

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

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

7、160;         WHERE bill_sequence_id IN                       (SELECT bill_sequence_id       

8、60;         FROM bom_structures_b                 WHERE assembly_item_id = 24815); 发料方式 Push 推式发料，必须手工通过界面做发料 需严格控制数量或者波动比较大的物料Assembly Pull 拉式发料

9、，装配件完工或报废时自动按标准消耗量触发 消耗比较稳定的物料Operation Pull 拉式发料，工序移动至To Move时自动按标准消耗量触发 消耗比较稳定的物料BOM_COMPONENTS_B.WIP_SUPPLY_TYPE存储发料方式ValueMeaning1Push2Assembly Pull3Operation Pull4Bulk5Supplier6Phantom7Based on Bill 3.2   定义ROUTING路径：Bill Of Materials > Routings> Routings  Routing

10、s(工艺路线)最终解决的问题是生产过程中加工顺序、资源和用量的标准化。Routing是产品/半成品的生产步骤图，定义了生产特定物料所要经历的工序、加工部门（工作中心）、提前期、耗用的资源及其额定数量。Routing头信息存储在表BOM_OPERATIONAL_ROUTINGS中，Routing的Operations信息存储在BOM_OPERATION_SEQUENCES表中，两个表通过字段ROUTING_SEQUENCE_ID字段关联 SELECT * FROM BOM_OPERATIONAL_ROUTINGS WHERE ASSEM

11、BLY_ITEM_ID =  24815;      SELECT *                  FROM bom_operation_sequences             &

12、#160;    WHERE routing_sequence_id IN                       (SELECT routing_sequence_id           &#

13、160;      FROM bom_operational_routings                 WHERE assembly_item_id = 24815); 3.2   定义离散工单路径：WIP > Discrete > DiscreteJobsDiscrete，离散式，一种制造方法，用

14、于装配件的分组或成批制造。制定标准离散任务，输入核心内容：任务名称（工单号）、生产类型、装配件、工单类型、生产数量、开工时间或完工时间。这里在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 asse

15、mblies, 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

16、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>&

17、gt;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 Wo

18、rk 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 LANGU

19、AGE = '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

20、_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_SEQUEN

21、CE_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 re

22、quirement 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 IdentifierO

23、RGANIZATION_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:Comp

24、onent 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 SEL

25、ECT * FROM WIP_ENTITIES WHERE WIP_ENTITY_NAME = 'J'WIP_ENTITIES.PRIMARY_ITEM_ID= 24815与BOM关联     SELECT *             FROM wip_discrete_jobs    

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

27、        WHERE wip_entity_name = 'J');SELECT *             FROM wip_requirement_operations WHERE wip_entity_id =        

28、60;        (SELECT wip_entity_id           FROM wip_entities           WHERE wip_entity_name = 'J');3.2   发料接下来我们就要从库

29、存中给工单发货，这里有两种方法发料到工单方法1：WIP > Material Transactions > WIP Material Transactions这种方式比较简单，直接指定库存并发料方法2：WIP > Discrete > Component Pick Release > Component Pick Releas(Form) / (SRS)当BOM的组件很多，使用方法一发料的话，一个个指定库存会很麻烦， EBS提供了Pick Release的方式来发料，这样用户只要指定合适的Picking Rule,那么系统会自动帮你挑库。系统会产生一个M

30、ove 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_Ty

31、pe_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.SOUR

32、CE_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发生

33、了，并且组件库存数量又恢复到发料前的数量。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 trans

34、action 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研究事例相关接口字段说明参见参考例子里的

35、说明3.1  工单创建PROCEDUREnew_work_order(p_wip_entity_id NUMBER,                                       

36、60;         p_organization_idNUMBER,                                    

37、60;            x_error_status OUTVARCHAR2,                                 

38、                 x_error_message OUTVARCHAR2) IS   l_iface_rec wip.wip_job_schedule_interface%ROWTYPE; -工单任务接口表   CURSOR c_wdj IS        SELECT *

39、             FROM cux_wip_discrete_jobs_temp cwdj            WHERE cwdj.wip_entity_id =p_wip_entity_id            &

40、#160;       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_rec.creation_date := SYSDAT

41、E;   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 Sta

42、ndard 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.sta

43、tus_type;        -根据业务逻辑判断标准非标准工单        IF rec_wdj.job_type = 1 THEN              l_iface_rec.load_type := 1; -1标准      

44、;     ELSE              l_iface_rec.load_type := 4; -非标准           END IF;        l_iface_rec.allow_explosion

45、:= 'Y'        l_iface_cess_phase := '2'        l_iface_cess_status := '1'        l_iface_rec.status_type := '3'/*rec_wdj.status_type*/

46、             -已发放        l_iface_rec.job_name := rec_wdj.job_num;        l_iface_anization_id := rec_anization_id;     &#

47、160;  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、60; 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;  

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

50、0;       l_iface_rec.attribute3 :=rec_wdj.temp_attribute3;        l_iface_rec.attribute4 :=rec_wdj.temp_attribute4;        l_iface_rec.attribute5 :=rec_wdj.temp_attribute5;  &

51、#160;     l_iface_rec.attribute6 :=rec_wdj.temp_attribute6;        l_iface_rec.attribute7 :=rec_wdj.temp_attribute7;        l_iface_rec.attribute8 :=rec_wdj.temp_attribute8;   

52、0;    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、160;   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、0;   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、160;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、#160;                                                 p

57、_validationlevel => 2, - Validation Level                                          

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

59、160;                                              x_returnstatus=>

60、x_error_status,                                               

61、60;                  x_errormsg=> x_error_message); END;3.2   工单更改PROCEDUREchange_work_order_status(p_wip_entity_id NUMBER,           

62、60;                                                 

63、60;      p_organization_id NUMBER,                                       &#

64、160;                            p_status_type NUMBER,                  

65、;                                                  

66、; p_group_idNUMBER := wip.wip_job_schedule_interface_s.nextval,                                      

67、0;                              x_error_status OUT VARCHAR2,               

68、                                                  

69、   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 V

70、ARCHAR2(3); BEGIN   BEGIN        SELECT 1             INTO l_temp             FROM wip_discrete_jobs wdj 

71、           WHERE wdj.wip_entity_id =p_wip_entity_id                    AND anization_id =p_organization_id     &#

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

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

74、dule_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_sch

75、edule_iface_rec.group_id := l_group_id;   /*=    WIP_LOAD_TYPE MFG_LOOKUPS    - -    1 Create Standard Job    2 Create Repetitive Schedule    3 Update Discrete Job   

76、0;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_status := 1; -Pending 

77、0; -   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; -目标状态   INSERT INTO wip.wip_job_schedule_interface VALUES l

78、_schedule_iface_rec;   wip_massload_pub.massloadjobs(p_groupid => p_group_id, - Group ID                                &#

79、160;                               p_validationlevel => 2, - Validation Level           

80、;                                                  

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

82、;                            x_returnstatus=> x_error_status,                

83、                                                 x_errormsg=

84、> x_error_message); END; 3.3  工单移动 工单创建完成后，若不进行移动事务处理，则无法进行完工事务处理。移动数量需要按照完工数量的多少进行移动。完工事务处理中有超量完工的概念，因此，移动事务处理也有超量移动。需要一个function 获取 可移动数量      /*=   *  FUNCTION / PROCEDURE   *     

85、; get_available_to_move_qty   *  DESCRIPTION:   *       获取可移动数量   *  ARGUMENT:            *  RETURN:   * 

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

87、;                         p_opr_seq_num     IN NUMBER,                 

88、;                    p_organization_id IN NUMBER,                         &

89、#160;           p_intraopr_step   IN NUMBER)      RETURN NUMBER IS        l_available_to_move_qty NUMBER;        CURSOR csr_w

90、ip_operations IS         SELECT decode(p_intraopr_step,                       1,          

91、             wo.quantity_in_queue,                       2,          &

92、#160;            wo.quantity_running,                       3,          

93、60;            wo.quantity_waiting_to_move,                       4,         

94、60;             wo.quantity_rejected,                       5,         

95、0;             wo.quantity_scrapped,                       wo.quantity_completed)      

96、60;    FROM wip_operations wo          WHERE wo.wip_entity_id = p_wip_entity_id            AND wo.operation_seq_num =p_opr_seq_num      

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

98、; FETCH csr_wip_operations         INTO l_available_to_move_qty;      CLOSE csr_wip_operations;      RETURN l_available_to_move_qty;    END get_available_to_move_qty;

99、0;  -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_interface   -&#

100、160;  WHERE transaction_id = p_txn_id   -     AND process_phase = WIP_CONSTANTS.MOVE_VAL   -     AND process_status = WIP_CONSTANTS.RUNNING   -     AND group_id IS NOT NU

101、LL;   -Doc ID:  .1   -PROCEDUREprocess_wip_move_txn(p_transaction_id IN NUMBER,                              &

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

103、60;  x_return_status  IN OUT VARCHAR2,                                 x_error_message  IN OUT VARCHAR2)IS 

104、60;    -PRAGMA AUTONOMOUS_TRANSACTION;      l_groupid NUMBER;      l_errbuf VARCHAR2(1000);      l_retcode NUMBER;  BEGIN      - Call the procedu

105、re     /*      * This procedure will be used to do move,easy-return, easy-completion, and      * scrap transaction for Discrete, OSFMjobs, and Repetitive Schedule. The      * caller nee

106、d 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   

107、   * and transaction_id before calling theprocedure below. These 2 columns      * should have to same value. Callershould always insert cess_phase =      * 1(Validation) to make sure that thedata inserted is valid, and inse