WIP 工序移动和完工接口开发

最新推荐文章于 2022-09-22 09:55:54 发布

wangcheng8699

最新推荐文章于 2022-09-22 09:55:54 发布

阅读量2k

点赞数

分类专栏： Oracle EBS 文章标签： WIP

本文链接：https://blog.csdn.net/wangcheng8699/article/details/103694327

版权

Oracle EBS 专栏收录该内容

14 篇文章 3 订阅

订阅专栏

WIP 工序移动和工单完工接口开发

在某个项目中，需要对 WIP 的工单的某工序进行移动，完工，同时可能涉及到超量完工。 Release 之后的工单可以进行工序移动事务处理，在工序移动的同时可以选择报废、（超量）完工。

接口开发简洁

接口开发简述

大量的期初数据如何进入 ERP？如何使 ERP 的系统流程更加自动化？如何迁移/合并两个 Oracle ERP 系统？这些纷繁复杂的海量工作不可能全部通过手工 Key 到系统中，必须通过某种方式灌入 ERP、再现源系统数据风貌。

处理这些业务需求的方式就是“接口” ：通过一组程序把数据批量写入 Oracle ERP 相关表中，达到和在界面输入同样的效果：数据一致性，数据有效性，数据集成性（比如插入 PO 数据，要反馈到 MRP 的需求/供应）。

接口开发主要步骤

采集数据
验证数据：按接口要求格式化数据，验证各个字段数据的合法性
往相应的接口表中插入验证后的数据
ERP 接口处理请求（将数据插入 ERP 数据表中）

工序移动接口和完工接口

接口：

该方法是往移动事务处理接口表 wip_move_txn_interface 中插入验证后的数据，然

后调用标准 API wip_movproc_pub. processinterface 来进行移动和完工。

但是往移动事务处理接口表中插数据的时候我们怎么知道要往哪些字段插数据呢？

首先我们可以导航到 EBS 中的移动事务处理界面

路径：车间管理-》移动事务处理-》移动事务处理

就可以查知那些字段代表了什么意思，该插什么值了。

下是一些关键字段：

字段	说明
transaction_type	1 工序移动（缺省） 2 移动完工入库 3 移动入库返回
process_phase	给 1 1 Move Validation 2 Move Processing 3 Operation Backflush Setup
process_status	给 1 1 Pending 2 Running 3 Error
wip_entity_name	任务名称
organization_id	Current Inv Organization
organization_code	组织代码，这个字段必须给
transaction_date	事务日期，必须 between Release Date and Sysdate
transaction_quantity	移动数量
transaction_uom	单位，可以不是主单位
fm_operation_seq_num	起始工序号
fm_intraoperation_step_type	起始工序内部步骤
to_operation_seq_num	终止工序号
to_intraoperation_step_type	终止工序内部步骤
source_code	随便给，一般用来追踪
last_updated_by_name	必须给，比较特殊
created_by_name	必须给，比较特殊
5 个 who 字段	习惯上都给

在处理的时候，可以判断当前的工序是否是最后一道工序，然后在设置 transaction_type的值，确定是移动还是入库

当往接口表中插入数据之后，就可以调用 wip_movproc_pub. processinterface 来进行后续的处理了;

超量完工

对于超量完工的情况，只需增加多一个字段即可，往接口表中插数据的时候，同时设置overcompletion_transaction_qty 的值为超量的部分的值即可；

代码范例

以下是一个代码范例：

CREATE OR REPLACE PACKAGE BODY cux_wip_dispatch_utl IS

g_mmt_iface mtl_transactions_interface%ROWTYPE;

g_wmti_iface wip_move_txn_interface%ROWTYPE;

g_user_name VARCHAR2(30) := fnd_global. user_name;

PROCEDURE test_mmt(p_id IN NUMBER) IS

l_result NUMBER;

BEGIN

SELECT mti.transaction_quantity

INTO l_result

FROM mtl_transactions_interface mti

WHERE mti.transaction_header_id = p_id;

END test_mmt;

FUNCTION get_wip_entity_name(p_wip_entity_id IN NUMBER,

p_org_id IN NUMBER) RETURN VARCHAR2 IS

l_return wip_entities.wip_entity_name%TYPE;

BEGIN

SELECT wip_entity_name

INTO l_return

FROM wip_entities t

WHERE wip_entity_id = p_wip_entity_id

AND organization_id = p_org_id;

RETURN l_return;

END get_wip_entity_name;

FUNCTION get_work_order_item_id(p_wip_entity_id IN NUMBER,

p_organization_id IN NUMBER) RETURN NUMBER AS

CURSOR c_work_order IS

SELECT t.primary_item_id

FROM wip_entities t

WHERE t.wip_entity_id = p_wip_entity_id

AND t.organization_id = p_organization_id;

l_item_id NUMBER;

BEGIN

OPEN c_work_order;

FETCH c_work_order

INTO l_item_id;

CLOSE c_work_order;

RETURN l_item_id;

END get_work_order_item_id;

FUNCTION check_txn_date_in_period(p_transaction_date IN DATE,

p_organization_id IN NUMBER)

RETURN VARCHAR2 AS

CURSOR c_period IS

SELECT ' Y'

FROM org_acct_periods t

WHERE t.open_flag = ' Y'

AND t.organization_id = p_organization_id AND

trunc(p_transaction_date) BETWEEN t.

period_start_date

AND t.schedule_close_date;

l_valid_flag VARCHAR2(1);

BEGIN

OPEN c_period;

FETCH c_period

INTO l_valid_flag;

CLOSE c_period;

RETURN l_valid_flag;

END check_txn_date_in_period;

PROCEDURE validate_wip_complete(p_wip_entity_id IN NUMBER,

p_organization_id IN NUMBER,

p_transaction_date IN DATE,

p_complete_qty IN NUMBER,

p_uom IN VARCHAR2,

x_return_status OUT NOCOPY VARCHAR2,

x_msg_data OUT NOCOPY VARCHAR2) IS

l_exists NUMBER;

l_primary_item_id NUMBER;

BEGIN

--Validate oraganization

BEGIN

SELECT organization_id

INTO g_wmti_iface.organization_id

FROM org_organization_definitions o

WHERE organization_id = p_organization_id;

g_wmti_iface.organization_id := p_organization_id;

EXCEPTION

WHEN OTHERS THEN

x_return_status := fnd_api.g_ret_sts_error;

x_msg_data := ' 非法的库存组织: ' || p_organization_id;

RETURN;

END;

--Validate dj_name

BEGIN

SELECT t.wip_entity_id, t.primary_item_id

INTO g_wmti_iface. wip_entity_id, l_primary_item_id

FROM wip_entities t

WHERE t.wip_entity_id = p_wip_entity_id

AND organization_id = p_organization_id;

EXCEPTION

WHEN OTHERS THEN

x_return_status := fnd_api.g_ret_sts_error;

x_msg_data := ' Invalid wip entity: ' || p_wip_entity_id;

RETURN;

END;

--Validate wip entity must have ASSEMBLY

IF l_primary_item_id IS NULL THEN

x_return_status := fnd_api.g_ret_sts_error;

x_msg_data := ' Wip entity assembly is null: ' ||

p_wip_entity_id;

RETURN;

END IF;

--Validate Transaction date must after release date and in open inv period

IF check_txn_date_in_period(p_transaction_date, p_organization_id) = ' Y' THEN

g_wmti_iface.transaction_date := p_transaction_date;

ELSE

x_return_status := fnd_api.g_ret_sts_error;

x_msg_data := ' 非法的事务处理日期: ' ||

to_char(p_transaction_date,

' yyyy-mm-dd hh24: mi: ss');

RETURN;

END IF;

--Validate DJ status must be released

SELECT COUNT(1)

INTO l_exists

FROM wip_discrete_jobs

WHERE wip_entity_id = g_wmti_iface.wip_entity_id

AND organization_id = p_organization_id

AND status_type = ' 3';

IF l_exists = 0 THEN

x_return_status := fnd_api.g_ret_sts_error;

x_msg_data := ' DJ status must be released. ';

RETURN;

END IF;

--Validate quantity

IF p_complete_qty IS NULL OR p_complete_qty <= 0 THEN

x_return_status := fnd_api.g_ret_sts_error;

x_msg_data := ' 非法的事务处理数量. : ' || p_complete_qty;

RETURN;

ELSE

g_wmti_iface.transaction_quantity := p_complete_qty;

END IF;

--Validate UOM

SELECT COUNT(1)

INTO l_exists

FROM mtl_units_of_measure_vl

WHERE uom_code = p_uom;

IF l_exists = 0 THEN

x_return_status := fnd_api.g_ret_sts_error;

x_msg_data := ' 非法的单位: ' || p_uom;

RETURN;

ELSE

g_wmti_iface.transaction_uom := p_uom;

END IF;

END validate_wip_complete;

PROCEDURE ins_move_txn_iface(p_txn_type IN VARCHAR2,

p_is_overcome IN NUMBER,

p_is_max_operation_seq_num IN VARCHAR2,

x_return_status OUT NOCOPY VARCHAR2,

x_msg_data OUT NOCOPY VARCHAR2) IS

l_fm_operation_seq_num NUMBER;

l_to_operation_seq_num NUMBER;

l_transaction_type NUMBER;

l_fm_step NUMBER;

l_to_step NUMBER;

BEGIN

SELECT wip_transactions_s.nextval

INTO g_wmti_iface.transaction_id

FROM dual;

--get seq num max(min)

BEGIN

SELECT MAX(operation_seq_num), MIN(operation_seq_num)

INTO l_to_operation_seq_num, l_fm_operation_seq_num

FROM wip_operations

WHERE wip_entity_id = g_wmti_iface.

wip_entity_id

AND organization_id = g_wmti_iface.organization_id;

EXCEPTION

WHEN OTHERS THEN

x_return_status := fnd_api.g_ret_sts_error;

x_msg_data := ' 工单已完成或者未发放！ . ';

RETURN;

END;

IF p_txn_type = ' COMPLETE' THEN

IF p_is_max_operation_seq_num = ' Y' THEN

l_transaction_type := 2; --COMPLETE

ELSE

l_transaction_type := 1;

END IF;

l_fm_step := 1; --Queue

l_to_step := 3; --To Move

ELSIF p_txn_type = ' RETURN' THEN

l_fm_operation_seq_num := g_wmti_iface.to_operation_seq_num;

l_to_operation_seq_num := g_wmti_iface.fm_operation_seq_num;

l_transaction_type := 3; --RETURN

l_fm_step := 3; --To Move

l_to_step := 1; --Queue

ELSE

x_return_status := fnd_api.g_ret_sts_error;

x_msg_data := ' Invalid arguments for ins_move_txn_iface. p_txn_type';

END IF;

g_wmti_iface.last_update_date := SYSDATE;

g_wmti_iface.last_updated_by := fnd_global. user_id;

g_wmti_iface.creation_date := SYSDATE;

g_wmti_iface.created_by := fnd_global. user_id;

g_wmti_iface.last_update_login := -1;

g_wmti_iface.last_updated_by_name := g_user_name;

g_wmti_iface.created_by_name := g_user_name;

g_wmti_iface.process_phase := 1;

g_wmti_iface.process_status := 2;

g_wmti_iface.transaction_type := l_transaction_type;

g_wmti_iface.fm_operation_seq_num := l_fm_operation_seq_num;

g_wmti_iface.fm_intraoperation_step_type := l_fm_step;

g_wmti_iface.to_intraoperation_step_type := l_to_step;

g_wmti_iface.to_operation_seq_num := l_to_operation_seq_num;

g_wmti_iface.source_code := ' Etrace';

g_wmti_iface.source_line_id := -1;

IF nvl(p_is_overcome, 0) = 0 THEN

INSERT INTO wip_move_txn_interface VALUES g_wmti_iface;

ELSE

-- 超量完工

g_wmti_iface.overcompletion_transaction_qty := p_is_overcome;

INSERT INTO wip_move_txn_interface VALUES g_wmti_iface;

END IF;

EXCEPTION

WHEN OTHERS THEN

x_return_status := fnd_api.g_ret_sts_unexp_error;

x_msg_data := ' cux_wip_dispatch_utl. ins_move_txn_iface Unhandled exception: ' ||

SQLERRM;

END ins_move_txn_iface;

PROCEDURE handle_mmt_iface(o_success_flag OUT VARCHAR2,

o_error_mssg OUT VARCHAR2,

p_table IN VARCHAR2) IS

l_result NUMBER;

l_return_status VARCHAR2(2);

l_msg_count NUMBER;

l_msg_data VARCHAR2(2000);

l_trans_count NUMBER;

BEGIN

o_success_flag := ' Y';

o_error_mssg := NULL;

--Call api process transaction

l_result := inv_txn_manager_pub.process_transactions(p_api_version => 1. 0,

x_return_status => l_return_status,

x_msg_count => l_msg_count,

x_msg_data => l_msg_data, --error message

x_trans_count => l_trans_count, --record processed

p_table => 1, --interface

p_header_id => g_mmt_iface.transaction_header_id);

IF l_result = 0 THEN

o_success_flag := ' Y';

o_error_mssg := NULL;

ELSE

o_success_flag := ' N';

IF p_table = '1' THEN

--retreve error from interface

FOR i IN (SELECT DISTINCT error_explanation

FROM mtl_transactions_interface

WHERE transaction_header_id =

g_mmt_iface.transaction_header_id) LOOP

o_error_mssg := o_error_mssg || ' . Error: ' ||

i.error_explanation;

END LOOP;

ELSE

o_error_mssg := l_msg_data;

END IF;

EXCEPTION

WHEN OTHERS THEN

o_success_flag := ' N';

o_error_mssg := ' Occur Exception: ' || SQLERRM;

END handle_mmt_iface;

PROCEDURE handle_wmti_iface(x_return_status OUT NOCOPY VARCHAR2,

x_msg_data OUT NOCOPY VARCHAR2) IS

BEGIN

--call api process wmti

wip_movproc_pub. processinterface(p_txn_id => g_wmti_iface.transaction_id,

p_do_backflush => fnd_api.g_true,

p_commit => fnd_api.g_false,

x_returnstatus => x_return_status,

x_errormsg => x_msg_data);

dbms_output. put_line(' x_return_status: ' || x_return_status);

EXCEPTION

WHEN OTHERS THEN

x_return_status := fnd_api.g_ret_sts_unexp_error;

x_msg_data := ' cux_wip_dispatch_utl. handle_wmti_iface Unhandled exception: ' ||

SQLERRM;

END handle_wmti_iface;

PROCEDURE process_wip_complete(p_wip_entity_id IN NUMBER,

p_organization_id IN NUMBER,

p_transaction_date IN DATE,

p_complete_qty IN NUMBER,

p_uom IN VARCHAR2,

p_is_max_operation_seq_num IN VARCHAR2,

p_is_overcome IN NUMBER,

p_sourcr_line_id IN NUMBER,

x_return_status OUT NOCOPY VARCHAR2,

x_msg_data OUT NOCOPY VARCHAR2) IS

BEGIN

x_return_status := fnd_api.g_ret_sts_success;

--setp 1 : validate data

validate_wip_complete(p_wip_entity_id => p_wip_entity_id,

p_organization_id => p_organization_id,

p_transaction_date => p_transaction_date,

p_complete_qty => p_complete_qty,

p_uom => p_uom,

x_return_status => x_return_status,

x_msg_data => x_msg_data);

IF x_return_status <> fnd_api.g_ret_sts_success THEN

RETURN;

END IF;

g_wmti_iface.source_line_id := p_sourcr_line_id;

--setp 2 : insert wip_move_txn_interface interface

ins_move_txn_iface(p_txn_type => ' COMPLETE',

p_is_overcome => p_is_overcome,

p_is_max_operation_seq_num => p_is_max_operation_seq_num,

x_return_status => x_return_status,

x_msg_data => x_msg_data);

IF x_return_status <> fnd_api.g_ret_sts_success THEN

RETURN;

END IF;

--setp 3 : call online process wip_move_txn_interface

handle_wmti_iface(x_return_status, x_msg_data);

IF x_return_status <> fnd_api.g_ret_sts_success THEN

RETURN;

END IF;

EXCEPTION

WHEN OTHERS THEN

x_return_status := fnd_api.g_ret_sts_unexp_error;

x_msg_data := ' cux_wip_dispatch_utl. process_wip_complete Unhandled exception: ' ||

SQLERRM;

END process_wip_complete;

END cux_wip_dispatch_utl;

wangcheng8699

关注

0
点赞
踩
6

收藏

觉得还不错? 一键收藏
0
评论
WIP 工序移动和完工接口开发

WIP 工序移动和工单完工接口开发在某个项目中，需要对 WIP 的工单的某工序进行移动，完工，同时可能涉及到超量完工。 Release 之后的工单可以进行工序移动事务处理，在工序移动的同时可以选择报废、（超量）完工。接口开发简洁接口开发简述大量的期初数据如何进入 ERP？如何使 ERP 的系统流程更加自动化？如何迁移/合并两个 Oracle ERP 系统？这些...
复制链接

扫一扫