首页 > 代码库 > 详解EBS接口开发之WIP模块接口
详解EBS接口开发之WIP模块接口
总体说明
文档目的
本文档针对WIP模块业务功能和接口进行分析和研究，对采用并发请求方式和调用API方式分别进行介绍
内容
WIP模块常用标准表简介
WIP事物处理组成
WIP相关业务流程
WIP相关API研究事例
（十）参考文档（七）采购相关的一些知识
（一）WIP模块常用标准表简介
1.1 常用标准表
如下表中列出了与WIP导入相关的表和说明：
表名 | 说明 | 其他信息 | ||
BOM_STRUCTURES_B | BOM头信息 |
|
|
|
BOM_COMPONENTS_B | BOM组件信息 |
|
|
|
BOM_OPERATIONAL_ROUTINGS | BOM工艺路线头信息 |
|
|
|
BOM_OPERATION_SEQUENCES | BOM工艺路线生产信息 |
|
|
|
WIP_ENTITIES | 工单信息表 |
|
|
|
WIP_DISCRETE_JOBS | 离散工单信息表 |
|
|
|
WIP_REQUIREMENT_OPERATIONS | 任务领料需求发放表 |
|
|
|
WIP_OPERATIONS | 离散作业工序(操作)表 |
|
|
|
WIP_OPERATION_RESOURCES | 生产资源表 |
|
|
|
MTL_MATERIAL_TRANSACTIONS | 事物处理表 |
|
|
|
WIP_TRANSACTIONS | WIP事物处理表 |
|
|
|
|
|
|
|
|
|
|
|
|
|
1.2 接口表大体介绍
如下表中列出了与WIP导入相关的接口表和说明：
表名 | 说明 | 其他信息 | ||
mtl_transactions_interface | 事物处理接口表 |
|
|
|
mtl_serial_numbers_interface | 事物处理序列表 |
|
|
|
mtl_transaction_lots_interface | 事物处理批次表 |
|
|
|
cst_comp_snap_interface |
|
|
|
|
wip_move_txn_interface | 移动事物处理接口表 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
1.3 说明
这里只列出了WIP相关的大多数常用表。还有一些不经常用到的没有涉及，具体可以参考oracle网站上的
（二）WIP事物处理组成
WIP事物处理主要包括：工单生成，工单更改，投料退料，工单移动，工单完工
2.1 WIP事物处理组成
工单创建：生成工单
工单更改：更改工单信息
投料退料：工单的投料以及退料
工单移动：工单的正向移动和逆向移动
投料退料：工单的完工事物处理
（三）WIP相关业务流程
3.1 创建BOM
路径：Bill Of Materials > Bills > Bills
后台数据
SELECT *FROM bom_structures_b lWHERE l.assembly_item_id =24815;
SELECT *
FROM bom_components_b
WHERE bill_sequence_id IN
(SELECT bill_sequence_id
FROM bom_structures_b
WHERE assembly_item_id = 24815);
发料方式
Push 推式发料，必须手工通过界面做发料 需严格控制数量或者波动比较大的物料
Assembly Pull 拉式发料，装配件完工或报废时自动按标准消耗量触发 消耗比较稳定的物料
Operation Pull 拉式发料，工序移动至To Move时自动按标准消耗量触发 消耗比较稳定的物料
BOM_COMPONENTS_B.WIP_SUPPLY_TYPE存储发料方式
Value | Meaning |
1 | Push |
2 | Assembly Pull |
3 | Operation Pull |
4 | Bulk |
5 | Supplier |
6 | Phantom |
7 | Based on Bill |
3.2 定义ROUTING
路径：Bill Of Materials > Routings> Routings
Routings(工艺路线)最终解决的问题是生产过程中加工顺序、资源和用量的标准化。Routing是产品/半成品的生产步骤图，定义了生产特定物料所要经历的工序、加工部门（工作中心）、提前期、耗用的资源及其额定数量。
Routing头信息存储在表BOM_OPERATIONAL_ROUTINGS中，Routing的Operations信息存储在BOM_OPERATION_SEQUENCES表中，两个表通过字段ROUTING_SEQUENCE_ID字段关联
SELECT *FROM BOM_OPERATIONAL_ROUTINGSWHERE ASSEMBLY_ITEM_ID = 24815;
SELECT *
FROM bom_operation_sequences
WHERE routing_sequence_id IN
(SELECT routing_sequence_id
FROM bom_operational_routings
WHERE assembly_item_id = 24815);
3.2 定义离散工单
路径：WIP > Discrete > DiscreteJobs
Discrete，离散式，一种制造方法，用于装配件的分组或成批制造。制定标准离散任务，输入核心内容：任务名称（工单号）、生产类型、装配件、工单类型、生产数量、开工时间或完工时间。
这里在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 assemblies, and flow 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 Identifier
ORGANIZATION_ID:Organization Identifier
WIP_ENTITY_NAME:WIP job or repetitiveassembly name or flow schedule reference code
ENTITY_TYPE:WIP entity type code
1 | Discrete job |
2 | Repetitive assembly |
3 | Closed discrete job |
4 | Flow schedule |
PRIMARY_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 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_ID:Organization Identifier
PRIMARY_ITEM_ID:Assembly Item Item
STATUS_TYPE :Status of job
可以通过下边的SQL查得code的意义
SELECT lookup_code,meaning FROMFND_LOOKUP_VALUES
where LANGUAGE = ‘US‘ AND Upper(lookup_type) LIKE Upper(‘WIP_JOB_STATUS‘)
Value | Meaning |
7 | Cancelled |
8 | Pending Bill Load |
9 | Failed Bill Load |
10 | Pending Routing Load |
11 | Failed Routing Load |
12 | Closed |
13 | Pending - Mass Loaded |
14 | Pending Close |
15 | Failed Close |
1 | Unreleased |
3 | Released |
4 | Complete |
5 | Complete - No Charges |
6 | On Hold |
JOB_TYPE ：Type of discrete job
Value | Meaning |
1 | Standard |
3 | Non-standard |
WIP_SUPPLY_TYPE ：Method of material consumption within WIP
Value | Meaning |
1 | Push |
2 | Assembly Pull |
3 | Operation Pull |
4 | Bulk |
5 | Supplier |
6 | Phantom |
7 | Based on Bill |
START_QUANTITY：Job start quantity
QUANTITY_COMPLETED：Current job quantity completed
COMMON_BOM_SEQUENCE_ID：-->BOM_COMPONENTS_B.BILL_SEQUENCE_ID
COMMON_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 requirement 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 Identifier
ORGANIZATION_ID:Organization Identifier
INVENTORY_ITEM_ID:Component Item Id
COMPONENT_SEQUENCE_ID:-->BOM_COMPONENTS_B.COMPONENT_SEQUENCE_ID
WIP_SUPPLY_TYPE ：Method of material consumption within WIP
Value | Meaning |
1 | Push |
2 | Assembly Pull |
3 | Operation Pull |
4 | Bulk |
5 | Supplier |
6 | Phantom |
7 | Based on Bill |
REQUIRED_QUANTITY:Component quantityrequired
QUANTITY_ISSUED:Component quantity issued
SUPPLY_SUBINVENTORY:Subinventory used tosupply component to WIP
SUPPLY_LOCATOR_ID:Locator used to supplycomponent to WIP
SEGMENT1:Component Item segment
QUANTITY_ALLOCATED:Quantity allocated
QUANTITY_BACKORDERED:Quantity backordered
SELECT *FROM WIP_ENTITIESWHERE WIP_ENTITY_NAME =‘J2010113‘;
WIP_ENTITIES.PRIMARY_ITEM_ID= 24815与BOM关联
SELECT *
FROM wip_discrete_jobs
WHERE wip_entity_id =
(SELECT wip_entity_id
FROM wip_entities
WHERE wip_entity_name = ‘J2010113‘);
SELECT *
FROM wip_requirement_operations
WHERE wip_entity_id =
(SELECT wip_entity_id
FROM wip_entities
WHERE wip_entity_name = ‘J2010113‘);
3.2 发料
接下来我们就要从库存中给工单发货，这里有两种方法发料到工单
方法1：WIP > Material Transactions > WIP Material Transactions
这种方式比较简单，直接指定库存并发料
方法2：WIP > Discrete > Component Pick Release > Component Pick Releas(Form) / (SRS)
当BOM的组件很多，使用方法一发料的话，一个个指定库存会很麻烦，Oracle EBS提供了Pick Release的方式来发料，这样用户只要指定合适的Picking Rule,那么系统会自动帮你挑库。
系统会产生一个Move 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_Type_id:35,WIPcomponent issue)
这个时候MTL_MATERIAL_TRANSACTIONS表中几个JOB相关的字段
MMT.Transaction_Type_id:35
MMT.TRANSACTION_ACTION_ID:1
MMT.TRANSACTION_SOURCE_TYPE_ID:5
MMT.TRANSACTION_SOURCE_ID-->WIP_DISCRETE_JOBS.WIP_ENTITY_ID
MMT.TRX_SOURCE_LINE_ID-->WIP_REQUIREMENT_OPERATIONS.OPERATION_SEQ_NUM
MMT.SOURCE_LINE_ID -->MTL_TXN_REQUEST_LINES.LINE_ID
MMT.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发生了，并且组件库存数量又恢复到发料前的数量。
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 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.
（四）WIP相关API研究事例
相关接口字段说明参见参考例子里的说明
3.1 工单创建
PROCEDUREnew_work_order(p_wip_entity_id NUMBER,
p_organization_idNUMBER,
x_error_status OUTVARCHAR2,
x_error_message OUTVARCHAR2) IS
l_iface_rec wip.wip_job_schedule_interface%ROWTYPE; --工单任务接口表
CURSOR c_wdj IS
SELECT *
FROM cux_wip_discrete_jobs_temp cwdj
WHERE cwdj.wip_entity_id =p_wip_entity_id
AND 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_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 Job
2 Create Repetitive Schedule
3 Update Discrete Job
4 Create Non–standard Job
================================================*/
FOR rec_wdj IN c_wdj LOOP
g_status_type := rec_wdj.status_type;
--根据业务逻辑判断标准非标准工单
IF rec_wdj.job_type = 1 THEN
l_iface_rec.load_type := 1; --1标准
ELSE
l_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.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_date :=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_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.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.attribute15 :=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;
--API
wip_massload_pub.massloadjobs(p_groupid => l_iface_rec.group_id, --Group ID
p_validationlevel => 2, -- Validation Level
p_commitflag=> 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_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 VARCHAR2(3);
BEGIN
BEGIN
SELECT 1
INTO l_temp
FROM wip_discrete_jobs wdj
WHERE wdj.wip_entity_id =p_wip_entity_id
AND wdj.organization_id =p_organization_id
AND wdj.status_type = p_status_type;
x_error_status := ‘S‘;
RETURN;
EXCEPTION
WHEN no_data_found THEN
NULL;
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_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_LOOKUPS
------------ --------------
1 Create Standard Job
2 Create Repetitive Schedule
3 Update Discrete Job
4 Create Non–standard Job
================================================*/
l_schedule_iface_rec.load_type := 3; --Update standard or non-standardDiscrete Job
l_schedule_iface_rec.process_phase := 2; --Validation
l_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 ID
p_validationlevel => 2, -- Validation Level
p_commitflag=> 0, -- Commit 1 =Yes , 0 =‘No‘
x_returnstatus=> 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 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 IS
l_available_to_move_qty NUMBER;
CURSOR csr_wip_operations IS
SELECT decode(p_intraopr_step,
1,
wo.quantity_in_queue,
2,
wo.quantity_running,
3,
wo.quantity_waiting_to_move,
4,
wo.quantity_rejected,
5,
wo.quantity_scrapped,
wo.quantity_completed)
FROM wip_operations wo
WHERE wo.wip_entity_id = p_wip_entity_id
AND wo.operation_seq_num =p_opr_seq_num
AND wo.organization_id =p_organization_id
AND wo.repetitive_schedule_id ISNULL;
BEGIN
OPEN csr_wip_operations;
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;
--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
-- 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 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 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 columns
* should have to same value. Callershould always insert wmti.process_phase =
* 1(Validation) to make sure that thedata inserted is valid, and insert
* wmti.process_status = 2(Running) toprevent Move Manager from picking up
* this record.
*
* PARAMETER:
*
* p_txn_id transaction_id inWIP_MOVE_TXN_INTERFACE
* p_do_backflush this parameter determine whether moveprocesor has to
* backflush pull componentor not. Some customers use
* third party software toinsert backflush components, so
* they do not want moveprocessor to backflush them
* again. The defaultvalue is null. If the callers do not
* pass this parameter orpass 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_commmit commit the change to the databaseif succesfully
* processing ? pass
* fnd_api.g_true orfnd_api.g_false
* x_returnStatus There are 2 possible values
* *fnd_api.g_ret_sts_success*
* means the movetransaction succesfully processed
* *fnd_api.g_ret_sts_unexp_error*
* means anexception occurred
* The size of this variableshould be VARCHAR2(1)
* x_errorMsg contains the error message onfailure. null on success.
* The size of this variableshould be VARCHAR2(1000)
* because there may beerrors in several columns.
*
* 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.
* 3. The caller does not 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 Call Wip_movproc_pub.processinterface()‘);
l_groupid := p_transaction_id;
/*SELECT group_id
INTO l_groupid
FROM wip_move_txn_interface
WHERE transaction_id = p_transaction_id
AND process_phase =wip_constants.move_val
AND process_status =wip_constants.running
AND group_id IS NOT NULL;*/
--wip_move_validator.validate(p_group_id=> l_groupid, p_initmsglist => fnd_api.g_true);
/*--Wip_Movproc_Priv.move_worker()
wip_movproc_priv.move_worker(errbuf => l_errbuf,
retcode => l_retcode,
p_group_id => l_groupid,
p_proc_phase=> wip_constants.move_proc,
p_time_out => 30,
p_seq_move =>wip_constants.yes);*/
-- This procedure should be called if caller want to do batch processing formultiple records in WMTI.
/*wip_movproc_pub.processinterface(p_group_id => l_groupid,
p_do_backflush => fnd_api.g_false,
p_commit => fnd_api.g_false,
x_returnstatus => x_return_status);*/
-- This procedure should be called if caller want to process one record at atime.
wip_movproc_pub.processinterface(p_txn_id => p_transaction_id,
p_do_backflush => fnd_api.g_false,
p_commit =>fnd_api.g_false,
x_returnstatus => x_return_status,
x_errormsg =>x_error_message);
IF x_return_status =fnd_api.g_ret_sts_success THEN
NULL;
cux_conc_utl.log_msg(p_msg =>‘ The Transaction_ID : ‘ ||
p_transaction_id ||
‘Succesfully Processed‘);
--COMMIT;
ELSE
cux_conc_utl.log_msg(p_msg =>‘ The Transaction_ID : ‘ ||
p_transaction_id ||
‘ WIP APIError : ‘ ||
x_error_message);
cux_conc_utl.out_msg(p_msg =>‘ The Transaction_ID : ‘ ||
p_transaction_id ||
‘ WIP API Error : ‘||
x_error_message);
--RAISE fnd_api.g_exc_error;
--ROLLBACK;
END IF;
cux_conc_utl.log_msg(p_msg => ‘ End Call Wip_movproc_pub.processinterface()‘);
END process_wip_move_txn;
/*=============================================
* FUNCTION / PROCEDURE
* process_wip_move
* DESCRIPTION:
* 工单移动事物处理
* ARGUMENT:
* RETURN:
*
* HISTORY:
* 1.00 2014-7-18 cxy
*=============================================*/
FUNCTIONprocess_wip_move(p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_false,
p_commit IN VARCHAR2 DEFAULT fnd_api.g_false,
x_return_status OUT NOCOPYVARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_parameter1 IN VARCHAR2) RETURN VARCHAR2 IS
l_api_name CONSTANT VARCHAR2(30) :=‘process_wip_move‘;
l_savepoint_name CONSTANT VARCHAR2(30) :=‘sp_process_wip_move‘;
l_return_status VARCHAR2(1);
l_msg_data VARCHAR2(2000);
l_rec_move_txn wip_move_txn_interface%ROWTYPE;
l_group_id NUMBER;
l_txn_id NUMBER;
l_available NUMBER;
l_trn_intermtl_transactions_interface%ROWTYPE;
v_transaction_header_id NUMBER DEFAULTNULL;
v_transaction_action_id NUMBER DEFAULTNULL;
v_quantity_flag NUMBER DEFAULT 1;
v_return_status VARCHAR2(3) DEFAULT NULL;
v_return_message VARCHAR2(6000) DEFAULTNULL;
v_on_hand_enough VARCHAR2(1) DEFAULT ‘N‘;
x_over_qty NUMBER;
l_wip_entity_id NUMBER := 265057;
l_organization_id NUMBER := 353;
l_transaction_quantity NUMBER := 3;
-- v_lot_number VARCHAR2(15) := ‘zhw007‘;*\
BEGIN
-- start activity to create savepoint,check compatibility
-- and initialize message list, includedebug message hint to enter api
x_return_status :=cux_api.start_activity(p_pkg_name => g_pkg_name,
p_api_name => l_api_name,
p_savepoint_name=> l_savepoint_name,
p_init_msg_list =>p_init_msg_list);
IF (x_return_status =fnd_api.g_ret_sts_unexp_error) THEN
RAISE fnd_api.g_exc_unexpected_error;
ELSIF (x_return_status =fnd_api.g_ret_sts_error) THEN
RAISE fnd_api.g_exc_error;
END IF;
-- API body
-- logging parameters
IF l_debug = ‘Y‘ THEN
cux_log.debug(‘p_parameter1 : ‘ ||p_parameter1);
END IF;
-- todo
SELECT wip_transactions_s.nextval INTOl_group_id FROM dual;
--loop
l_txn_id := NULL;
l_rec_move_txn := NULL;
SELECT wip_transactions_s.nextval INTOl_txn_id FROM dual;
l_rec_move_txn.transaction_id :=l_txn_id;
l_rec_move_txn.group_id := l_group_id;
l_rec_move_txn.process_phase := 1;
l_rec_move_txn.process_status := 2;--runing
l_rec_move_txn.created_by := fnd_global.user_id;
l_rec_move_txn.creation_date := SYSDATE;
l_rec_move_txn.last_updated_by := fnd_global.user_id;
l_rec_move_txn.last_update_date := SYSDATE;
l_rec_move_txn.last_update_login :=fnd_global.login_id;
l_rec_move_txn.wip_entity_id :=l_wip_entity_id;
--l_rec_move_txn.wip_entity_name :=rec_grp.wo_no;
l_rec_move_txn.organization_id := l_organization_id;
l_rec_move_txn.transaction_date := SYSDATE;
l_rec_move_txn.transaction_quantity := abs(l_transaction_quantity);--不管怎样，事务处理数量始终取正值
l_rec_move_txn.transaction_uom := ‘个‘;
IF l_transaction_quantity > 0 THEN
--移动数量大于0时，正向移动，由排队到移动工序号由最小走到最大
l_rec_move_txn.transaction_type := 1;--1.normal move;2.combination move or completion/return transaction
SELECT MIN(wo.operation_seq_num),MAX(wo.operation_seq_num)
INTOl_rec_move_txn.fm_operation_seq_num,
l_rec_move_txn.to_operation_seq_num
FROM wip_operations wo
WHERE wo.wip_entity_id =l_wip_entity_id
AND wo.organization_id =l_organization_id;
l_rec_move_txn.fm_intraoperation_step_type := 1; --排队
l_rec_move_txn.to_intraoperation_step_type := 3; --移动
l_available :=get_available_to_move_qty(p_wip_entity_id => l_wip_entity_id,
p_opr_seq_num => l_rec_move_txn.fm_operation_seq_num,
p_organization_id => l_organization_id,
p_intraopr_step => 1);
IF l_available <l_transaction_quantity THEN
--如果可移动数量小于移动数量，则启用超量移动
l_rec_move_txn.overcompletion_transaction_qty := l_transaction_quantity-
l_available;
l_rec_move_txn.overcompletion_primary_qty := l_transaction_quantity -
l_available;
x_over_qty :=l_transaction_quantity - l_available;
END IF; --IF l_available <p_move_qty THEN
ELSE
--移动数量小于0时，反向移动，由移动到排队工序号由最大走到最小
l_rec_move_txn.transaction_type := 1;--1.normal move;2.combination move or completion/return transaction
SELECT MAX(wo.operation_seq_num),MIN(wo.operation_seq_num)
INTO l_rec_move_txn.fm_operation_seq_num,
l_rec_move_txn.to_operation_seq_num
FROM wip_operations wo
WHERE wo.wip_entity_id =l_wip_entity_id
AND wo.organization_id =l_organization_id;
l_rec_move_txn.fm_intraoperation_step_type:= 3; --移动
l_rec_move_txn.to_intraoperation_step_type := 1; --排队
END IF; -- IF p_move_qty > 0 THEN
l_rec_move_txn.last_updated_by_name :=fnd_global.user_name;
l_rec_move_txn.created_by_name := fnd_global.user_name;
--以下两字段视业务逻辑取数
l_rec_move_txn.source_code := ‘CXY_MES_WIP_MOVE_TEST‘;
l_rec_move_txn.source_line_id := 265057;
BEGIN
INSERT INTO wip_move_txn_interfaceVALUES l_rec_move_txn;
EXCEPTION
WHEN OTHERS THEN
cux_conc_utl.log_msg(‘insert intowip_move_txn_interface error : ‘ ||
SQLERRM);
RAISE fnd_api.g_exc_error;
END;
wip_movproc_pub.processinterface(p_txn_id => l_txn_id,
p_commit => ‘F‘,
x_returnstatus => l_return_status,
x_errormsg => l_msg_data);
--END IF;
-- API end body
-- end activity, include debug messagehint to exit api
x_return_status :=cux_api.end_activity(p_pkg_name =>g_pkg_name,
p_api_name => l_api_name,
p_commit => p_commit,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
RETURN l_return_status || l_msg_data;
EXCEPTION
WHEN fnd_api.g_exc_error THEN
x_return_status :=cux_api.handle_exceptions(p_pkg_name => g_pkg_name,
p_api_name => l_api_name,
p_savepoint_name => l_savepoint_name,
p_exc_name =>cux_api.g_exc_name_error,
x_msg_count =>x_msg_count,
x_msg_data =>x_msg_data);
WHEN fnd_api.g_exc_unexpected_error THEN
x_return_status :=cux_api.handle_exceptions(p_pkg_name => g_pkg_name,
p_api_name => l_api_name,
p_savepoint_name=> l_savepoint_name,
p_exc_name =>cux_api.g_exc_name_unexp,
x_msg_count =>x_msg_count,
x_msg_data =>x_msg_data);
WHEN OTHERS THEN
x_return_status :=cux_api.handle_exceptions(p_pkg_name => g_pkg_name,
p_api_name => l_api_name,
p_savepoint_name => l_savepoint_name,
p_exc_name =>cux_api.g_exc_name_others,
x_msg_count => x_msg_count,
x_msg_data =>x_msg_data);
END process_wip_move;
3.4 工单投料退料
/*=============================================
* FUNCTION / PROCEDURE
* process_wip_issues_returns
* DESCRIPTION:
* 工单投料&退料事物处理
* ARGUMENT:
* RETURN:
*
* HISTORY:
* 1.00 2014-7-18 cxy
*=============================================*/
FUNCTIONprocess_wip_issues_returns(p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_false,
p_commit IN VARCHAR2DEFAULT fnd_api.g_false,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPYVARCHAR2,
p_parameter1 IN VARCHAR2)
RETURN VARCHAR2 IS
l_api_name CONSTANT VARCHAR2(30) :=‘process_wip_issues_returns‘;
l_savepoint_name CONSTANT VARCHAR2(30) :=‘sp_process_wip_issues_returns‘;
l_iface_recinv.mtl_transactions_interface%ROWTYPE;
l_trn_serial mtl_serial_numbers_interface%ROWTYPE;
x_error_message VARCHAR2(2000);
l_request_id NUMBER;
l_return_status VARCHAR2(2);
x_error_status VARCHAR(2);
l_msg_count NUMBER;
l_msg_data VARCHAR2(2000);
l_trans_count VARCHAR2(20000);
l_transaction_source_id NUMBER := 265056;--WIP_DISCRETE_JOBS.WIP_ENTITY_ID
l_inventory_item_id NUMBER := 325265; --投的料
l_organization_id NUMBER := 353;
l_subinventory_code VARCHAR2(30) := ‘YC01‘;
l_transaction_quantity NUMBER := -4;
BEGIN
-- start activity to create savepoint,check compatibility
-- and initialize message list, includedebug message hint to enter api
x_return_status :=cux_api.start_activity(p_pkg_name => g_pkg_name,
p_api_name => l_api_name,
p_savepoint_name => l_savepoint_name,
p_init_msg_list =>p_init_msg_list);
IF (x_return_status =fnd_api.g_ret_sts_unexp_error) THEN
RAISE fnd_api.g_exc_unexpected_error;
ELSIF (x_return_status =fnd_api.g_ret_sts_error) THEN
RAISE fnd_api.g_exc_error;
END IF;
-- API body
-- logging parameters
IF l_debug = ‘Y‘ THEN
cux_log.debug(‘p_parameter1 : ‘ ||p_parameter1);
END IF;
-- todo
--cux_debug.test_log(‘1‘, ‘1‘, ‘beginprocess‘);
SELECTmtl_material_transactions_s.nextval
INTO l_iface_rec.transaction_interface_id
FROM dual;
/* l_trn_serial := NULL;
l_trn_serial.transaction_interface_id :=l_iface_rec.transaction_interface_id;
l_trn_serial.source_code := v_source_code;
l_trn_serial.source_line_id := v_source_line_id;
l_trn_serial.creation_date := SYSDATE;
l_trn_serial.created_by := g_user_id;
l_trn_serial.last_update_date := SYSDATE;
l_trn_serial.last_updated_by := g_user_id;
l_trn_serial.last_update_login := g_login_id;
l_trn_serial.program_application_id := g_prog_appl_id;
l_trn_serial.program_id := g_conc_program_id;
l_trn_serial.program_update_date := SYSDATE;
l_trn_serial.request_id := g_request_id;
l_trn_serial.fm_serial_number := NULL; --redo
l_trn_serial.to_serial_number := NULL; -- redo
l_trn_serial.process_flag := 1;
BEGIN
INSERT INTOmtl_serial_numbers_interface VALUES l_trn_serial;
EXCEPTION
WHEN OTHERS THEN
cux_conc_utl.log_msg(‘insert intomtl_serial_numbers_interface error : ‘ ||
SQLERRM);
RAISE fnd_api.g_exc_error;
END;*/
l_iface_rec.last_update_date := SYSDATE;
l_iface_rec.last_updated_by := fnd_global.user_id;
l_iface_rec.creation_date := SYSDATE;
l_iface_rec.created_by := fnd_global.user_id;
l_iface_rec.last_update_login := fnd_global.user_id;
l_iface_rec.transaction_header_id :=l_iface_rec.transaction_interface_id;
/************transaction_mode*********************
* transaction_mode
*2 Concurrent, Process transactioninterface不处理，
* 需要程序调用Inventory transaction worker处理
*3 Background,由Process transaction interface处理
***************************************************/
l_iface_rec.transaction_mode := 3;
/************process_flag*********************
* process_flag
*1 Yes
*2 No
*3 Error
***************************************************/
l_iface_rec.process_flag := 1;
IF l_transaction_quantity >= 0 THEN
l_iface_rec.transaction_type_id := 35;--mtl_transaction_types--WIP Issue--投料
ELSE
l_iface_rec.transaction_type_id := 43;--wip return退料
END IF;
l_iface_rec.transaction_source_type_id :=5; --mtl_txn_source_types--生产管理
/*************************************************
*账户别名杂项，用别名ID即disposition_id
*账户杂项，用账户ID即code_combination_id
*WIP物料事务，用任务ID即wip_entity_id
*SO物料事务，用mtl_sales_orders.sales_order_id
*子库转移或组织间转移，为空
************************************************/
/*SELECT wdj.wip_entity_id
INTO l_iface_rec.transaction_source_id
FROM wip_discrete_jobs wdj, wip_entitieswe
WHERE (wdj.source_line_id =rec_issue.job_id OR
(wdj.wip_entity_id,wdj.organization_id) IN
(SELECTcwdj.wip_entity_id, cwdj.organization_id
FROM cux_wip_discrete_jobs_vcwdj
WHERE cwdj.job_id =rec_issue.job_id
AND cwdj.order_wip_entity_idIS NULL))
AND we.wip_entity_id = wdj.wip_entity_id
AND we.organization_id =wdj.organization_id;*/
l_iface_rec.transaction_source_id :=l_transaction_source_id;
l_iface_rec.inventory_item_id := l_inventory_item_id;
l_iface_rec.organization_id := l_organization_id;
l_iface_rec.subinventory_code := l_subinventory_code;
l_iface_rec.locator_id := NULL;
l_iface_rec.transaction_quantity := -l_transaction_quantity;
l_iface_rec.transaction_uom := ‘个‘;
l_iface_rec.reason_id := NULL;
l_iface_rec.transaction_date := SYSDATE;
l_iface_rec.source_code := ‘CXY_MES_WIP_TEST_CODE‘;
l_iface_rec.source_header_id := 4;
l_iface_rec.source_line_id := 4;
l_iface_rec.final_completion_flag := ‘N‘;
--cux_debug.test_log(‘1‘, ‘1‘, ‘begininsert mtl‘);
BEGIN
INSERT INTOinv.mtl_transactions_interface VALUES l_iface_rec;
EXCEPTION
WHEN OTHERS THEN
cux_conc_utl.log_msg(‘insert intomtl_transactions_interface error : ‘ ||
SQLERRM);
RAISE fnd_api.g_exc_error;
END;
--cux_debug.test_log(‘1‘, ‘1‘, ‘endinsert mtl‘);
l_request_id := inv_txn_manager_pub.process_transactions(p_api_version => 1,
p_init_msg_list =>fnd_api.g_false,
p_commit => fnd_api.g_true,
p_validation_level => fnd_api.g_valid_level_full,
x_return_status =>l_return_status,
x_msg_count => l_msg_count,
x_msg_data =>l_msg_data,
x_trans_count =>l_trans_count,
p_table => 1,
p_header_id =>l_iface_rec.transaction_header_id);
x_error_status := l_return_status;
BEGIN
SELECT mti.error_code || ‘,‘ ||mti.error_explanation || ‘,‘ ||
mli.error_code
INTO x_error_message
FROM mtl_transactions_interface mti,
mtl_transaction_lots_interfacemli
WHERE mti.transaction_interface_id =
l_iface_rec.transaction_interface_id
AND mli.transaction_interface_id(+)=
mti.transaction_interface_id;
EXCEPTION
WHEN no_data_found THEN
NULL;
END;
IF x_error_status != ‘S‘ THEN
/*DELETEmtl_transaction_lots_interface t
WHERE t.source_code =‘cux_wip_issue_txn‘
AND t.source_line_id =rec_issue.wip_issue_txn_id;*/
/*DELETE mtl_transactions_interface t
WHERE t.source_code =‘CXY_MES_WIP_TEST_CODE‘
AND t.source_line_id = 4
AND t.source_header_id = 4;*/
NULL;
END IF;
--END IF;
RETURN x_error_status || x_error_message;
-- API end body
-- end activity, include debug messagehint to exit api
x_return_status :=cux_api.end_activity(p_pkg_name =>g_pkg_name,
p_api_name => l_api_name,
p_commit => p_commit,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
EXCEPTION
WHEN fnd_api.g_exc_error THEN
x_return_status :=cux_api.handle_exceptions(p_pkg_name => g_pkg_name,
p_api_name => l_api_name,
p_savepoint_name => l_savepoint_name,
p_exc_name =>cux_api.g_exc_name_error,
x_msg_count =>x_msg_count,
x_msg_data => x_msg_data);
WHEN fnd_api.g_exc_unexpected_error THEN
x_return_status :=cux_api.handle_exceptions(p_pkg_name => g_pkg_name,
p_api_name => l_api_name,
p_savepoint_name => l_savepoint_name,
p_exc_name =>cux_api.g_exc_name_unexp,
x_msg_count =>x_msg_count,
x_msg_data =>x_msg_data);
WHEN OTHERS THEN
x_return_status :=cux_api.handle_exceptions(p_pkg_name => g_pkg_name,
p_api_name => l_api_name,
p_savepoint_name => l_savepoint_name,
p_exc_name => cux_api.g_exc_name_others,
x_msg_count =>x_msg_count,
x_msg_data =>x_msg_data);
END process_wip_issues_returns;
3.4 工单完工
/*=============================================
* FUNCTION / PROCEDURE
* process_wip_completions
* DESCRIPTION:
* 工单完成事物处理
* ARGUMENT:
* RETURN:
*
* HISTORY:
* 1.00 2014-7-18 cxy
*=============================================*/
FUNCTIONprocess_wip_completions(p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_false,
p_commit IN VARCHAR2DEFAULT fnd_api.g_false,
x_return_status OUT NOCOPYVARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPYVARCHAR2,
p_parameter1 IN VARCHAR2)
RETURN VARCHAR2 IS
l_api_name CONSTANT VARCHAR2(30) :=‘process_wip_completions‘;
l_savepoint_name CONSTANT VARCHAR2(30) :=‘sp_process_wip_completions‘;
l_iface_rec inv.mtl_transactions_interface%ROWTYPE;
l_cst_rec cst_comp_snap_interface%ROWTYPE;
l_trn_serialmtl_serial_numbers_interface%ROWTYPE;
l_return_status VARCHAR2(1);
l_msg_count NUMBER;
l_msg_data VARCHAR2(2000);
l_trans_count NUMBER;
x_error_message VARCHAR2(2000);
l_transaction_quantity NUMBER := 3;
l_transaction_source_id NUMBER := 265057;--wip_entity_id
l_inventory_item_id NUMBER := 325264; --CXY_制造件
l_organization_id NUMBER := 353;
l_subinventory_code VARCHAR2(30) := ‘CP01‘;
l_request_id NUMBER;
BEGIN
-- start activity to create savepoint,check compatibility
-- and initialize message list, includedebug message hint to enter api
x_return_status := cux_api.start_activity(p_pkg_name => g_pkg_name,
p_api_name => l_api_name,
p_savepoint_name => l_savepoint_name,
p_init_msg_list => p_init_msg_list);
IF (x_return_status =fnd_api.g_ret_sts_unexp_error) THEN
RAISE fnd_api.g_exc_unexpected_error;
ELSIF (x_return_status =fnd_api.g_ret_sts_error) THEN
RAISE fnd_api.g_exc_error;
END IF;
-- API body
-- logging parameters
IF l_debug = ‘Y‘ THEN
cux_log.debug(‘p_parameter1 : ‘ ||p_parameter1);
END IF;
-- todo
SELECTmtl_material_transactions_s.nextval
INTO l_iface_rec.transaction_interface_id
FROM dual;
l_iface_rec.transaction_header_id :=l_iface_rec.transaction_interface_id;
/* l_trn_serial := NULL;
l_trn_serial.transaction_interface_id :=l_iface_rec.transaction_interface_id;
l_trn_serial.source_code := v_source_code;
l_trn_serial.source_line_id := v_source_line_id;
l_trn_serial.creation_date := SYSDATE;
l_trn_serial.created_by := g_user_id;
l_trn_serial.last_update_date := SYSDATE;
l_trn_serial.last_updated_by := g_user_id;
l_trn_serial.last_update_login := g_login_id;
l_trn_serial.program_application_id := g_prog_appl_id;
l_trn_serial.program_id := g_conc_program_id;
l_trn_serial.program_update_date := SYSDATE;
l_trn_serial.request_id := g_request_id;
l_trn_serial.fm_serial_number := NULL; --redo
l_trn_serial.to_serial_number := NULL; -- redo
l_trn_serial.process_flag := 1;
BEGIN
INSERT INTOmtl_serial_numbers_interface VALUES l_trn_serial;
EXCEPTION
WHEN OTHERS THEN
cux_conc_utl.log_msg(‘insert intomtl_serial_numbers_interface error : ‘ ||
SQLERRM);
RAISE fnd_api.g_exc_error;
END;*/
l_iface_rec.last_update_date := SYSDATE;
l_iface_rec.last_updated_by := fnd_global.user_id;
l_iface_rec.creation_date := SYSDATE;
l_iface_rec.created_by := fnd_global.user_id;
l_iface_rec.last_update_login :=fnd_global.user_id;
/************transaction_mode*********************
* transaction_mode
*2 Concurrent, Process transactioninterface不处理，
* 需要程序调用Inventory transaction worker处理
*3 Background,由Process transaction interface处理
***************************************************/
l_iface_rec.transaction_mode := 3;
/************process_flag*********************
* process_flag
*1 Yes
*2 No
*3 Error
***************************************************/
l_iface_rec.process_flag := 1;
IF l_transaction_quantity >= 0 THEN
l_iface_rec.transaction_type_id := 44;--mtl_transaction_types--WIP Completion完工事务处理
ELSE
l_iface_rec.transaction_type_id := 17;--如果数量为负数17 WIP Completion Return完工事务处理退回
END IF;
l_iface_rec.transaction_source_type_id :=5; --mtl_txn_source_types--生产管理
/*************************************************
*账户别名杂项，用别名ID即disposition_id
*账户杂项，用账户ID即code_combination_id
*WIP物料事务，用任务ID即wip_entity_id
*SO物料事务，用mtl_sales_orders.sales_order_id
*子库转移或组织间转移，为空
************************************************/
/* BEGIN
SELECT wdj.wip_entity_id,we.primary_item_id
INTOl_iface_rec.transaction_source_id,
l_iface_rec.inventory_item_id
FROM wip_discrete_jobs wdj, wip_entitieswe
WHERE (wdj.source_line_id =rec_completion.job_id AND
wdj.source_code = ‘wipplatform‘)
AND we.wip_entity_id =wdj.wip_entity_id
AND we.organization_id =wdj.organization_id;
EXCEPTION
WHEN no_data_found THEN
NULL;
END;*/
l_iface_rec.transaction_source_id :=l_transaction_source_id;
l_iface_rec.inventory_item_id := l_inventory_item_id;
IF l_transaction_quantity > 0 THEN
--若数量大于零，需要先进行移动事务处理
l_msg_data :=process_wip_move(p_init_msg_list => fnd_api.g_false,
p_commit =>fnd_api.g_false,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_parameter1 => NULL);
IF l_return_status != ‘S‘ THEN
cux_debug.test_log(‘MOVE_COM‘ ||l_return_status,
l_msg_count,
l_msg_data);
NULL;
END IF; --IF x_error_status != ‘S‘THEN
END IF; -- IF rec_completion.quantity> 0 THEN
--移动成功则继续
l_iface_rec.organization_id := l_organization_id;
l_iface_rec.subinventory_code := l_subinventory_code;
l_iface_rec.locator_id := NULL;
l_iface_rec.transaction_quantity := l_transaction_quantity;
l_iface_rec.transaction_uom := ‘个‘;
l_iface_rec.transaction_date := SYSDATE;
l_iface_rec.final_completion_flag := ‘N‘;
l_iface_rec.wip_entity_type := 1;
--l_iface_rec.transaction_header_id :=l_iface_rec.transaction_interface_id;
--以下三字段，视业务逻辑不同取数
l_iface_rec.source_code :=‘CXY_MES_WIP_COM_TEST1‘;
l_iface_rec.source_header_id := 265057;
l_iface_rec.source_line_id := 265057;
BEGIN
INSERT INTOinv.mtl_transactions_interface VALUES l_iface_rec;
EXCEPTION
WHEN OTHERS THEN
cux_conc_utl.log_msg(‘insert intomtl_transactions_interface error : ‘ ||
SQLERRM);
RAISE fnd_api.g_exc_error;
END;
l_cst_rec.transaction_interface_id :=l_iface_rec.transaction_header_id;
l_cst_rec.wip_entity_id := 265057;
l_cst_rec.operation_seq_num := 10;
l_cst_rec.last_update_date := SYSDATE;
l_cst_rec.last_updated_by := fnd_global.user_id;
l_cst_rec.creation_date := SYSDATE;
l_cst_rec.created_by := fnd_global.user_id;
l_cst_rec.quantity_completed := l_transaction_quantity; --QuantityCompleted
l_cst_rec.primary_quantity := l_transaction_quantity; --PriamryQuantity
BEGIN
INSERT INTO cst_comp_snap_interfaceVALUES l_cst_rec;
EXCEPTION
WHEN OTHERS THEN
cux_conc_utl.log_msg(‘insert intocst_comp_snap_interface error : ‘ ||
SQLERRM);
RAISE fnd_api.g_exc_error;
END;
l_request_id :=inv_txn_manager_pub.process_transactions(p_api_version => 1,
p_init_msg_list =>fnd_api.g_false,
p_commit =>fnd_api.g_true,
p_validation_level=> fnd_api.g_valid_level_full,
x_return_status =>l_return_status,
x_msg_count => l_msg_count,
x_msg_data =>l_msg_data,
x_trans_count =>l_trans_count,
p_table => 1,
p_header_id =>l_iface_rec.transaction_header_id);
x_return_status := l_return_status;
BEGIN
SELECT mti.error_code || ‘,‘ ||mti.error_explanation || ‘,‘ ||
mli.error_code
INTO x_error_message
FROM mtl_transactions_interface mti,
mtl_transaction_lots_interfacemli
WHERE mti.transaction_interface_id =
l_iface_rec.transaction_interface_id
AND mli.transaction_interface_id(+)=
mti.transaction_interface_id;
EXCEPTION
WHEN no_data_found THEN
NULL;
END;
IF x_return_status != ‘S‘ THEN
NULL;
/*DELETE mtl_transactions_interface t
WHERE t.source_code =‘cux_wip_issue_txn‘
AND t.source_line_id =rec_issue.wip_issue_txn_id
AND t.source_header_id =rec_issue.job_id;*/
ENDIF; --IF x_error_status != ‘S‘ THEN
--END IF;
-- API end body
-- end activity, include debug messagehint to exit api
x_return_status :=cux_api.end_activity(p_pkg_name =>g_pkg_name,
p_api_name => l_api_name,
p_commit => p_commit,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
RETURN l_return_status ||x_error_message;
EXCEPTION
WHEN fnd_api.g_exc_error THEN
x_return_status :=cux_api.handle_exceptions(p_pkg_name => g_pkg_name,
p_api_name => l_api_name,
p_savepoint_name => l_savepoint_name,
p_exc_name =>cux_api.g_exc_name_error,
x_msg_count =>x_msg_count,
x_msg_data =>x_msg_data);
WHEN fnd_api.g_exc_unexpected_error THEN
x_return_status := cux_api.handle_exceptions(p_pkg_name => g_pkg_name,
p_api_name => l_api_name,
p_savepoint_name => l_savepoint_name,
p_exc_name => cux_api.g_exc_name_unexp,
x_msg_count =>x_msg_count,
x_msg_data =>x_msg_data);
WHEN OTHERS THEN
x_return_status :=cux_api.handle_exceptions(p_pkg_name => g_pkg_name,
p_api_name => l_api_name,
p_savepoint_name => l_savepoint_name,
p_exc_name =>cux_api.g_exc_name_others,
x_msg_count =>x_msg_count,
x_msg_data => x_msg_data);
END process_wip_completions;