API实现自动处理物料搬运单

最新推荐文章于 2021-11-25 18:47:18 发布

oldbalck

最新推荐文章于 2021-11-25 18:47:18 发布

阅读量177

点赞数

原文链接：http://www.cnblogs.com/liuweicong39/archive/2012/11/15/2771240.html

版权

目的：模拟EBS Form界面的功能，调用标准的API实现自动处理物料搬运单。

实现思路：

1. 先创建一个客户化的表，这个表中用来保存物料搬运单的一些信息。

2. 调用标准的API：inv_loc_wms_pub.create_locator动态创建货位(根据项目、任务和库存三个段来创建)。

3. 调用标准的API：inv_quantity_tree_pub.query_quantities检查某一库存组织的某一子库存下某一物料的现用量和可用量。

4. 调用标准的API：inv_mo_line_detail_util.insert_row在MMTT表中插入一条记录。

5. 调用标准的API：INV_Replenish_Detail_PUB.line_details_pub创建物料搬运单的分配行。

6. 调用标准的API: inv_trolin_util.query_rows得到物料搬运单行的相关信息。

7. 调用标准的API：INV_PICK_WAVE_PICK_CONFIRM_PUB.Pick_Confirm进行自动挑库确认(处理物料搬运单)。

8. 调用标准的API：inv_mo_line_detail_util.delete_row删除MMTT表中的未处理的记录(如果步骤4中的记录没有被处理)。

9. 更新客户化表的处理状态和错误信息。

10. 将处理的结果进行统计，并使用HTML输出。

整个程序包如下：

[c-sharp:collapse] + expand source view plain copy print ?

CREATE OR REPLACE PACKAGE BODY CUX_EDIINT_ITEM_MOVE_IN_PKG IS
--自定义预置文件CUX_MO_DEBUG_FLAG：用来判断是否输出调试信息
--fnd_profile.VALUE('CUX_MO_DEBUG_FLAG')为NULL,不输出调试信息,输出错误信息

--fnd_profile.VALUE('CUX_MO_DEBUG_FLAG')为'N',不输出调试信息,输出错误信息
--fnd_profile.VALUE('CUX_MO_DEBUG_FLAG')为'Y', 输出调试信息,输出错误信息
l_mo_debug_falg VARCHAR2(1) := NVL(fnd_profile.VALUE('CUX_MO_DEBUG_FLAG'),
'N');
--日志
PROCEDURE put_log(p_msg IN VARCHAR2) IS
BEGIN
fnd_file.put_line(fnd_file.log, p_msg);
END put_log;
--输出
PROCEDURE put_file(p_msg IN VARCHAR2) IS
BEGIN
fnd_file.put_line(fnd_file.OUTPUT, p_msg);
END put_file;
/*=======================================================
* FUNCTION / PROCEDURE
* get_org_count
* DESCRIPTION:
* 得到本次请求处理(成功或者失败)的物料搬运单的所有OU 数量,0表示不存在，1表示存在。
* ARGUMENT:
* p_org_id :OU id
* p_process_id :物料搬运单处理状态
* RETURN:
* N/A
* HISTORY:
* 1.00 27/10/2010 cxy
=========================================================*/
FUNCTION get_org_count(p_org_id NUMBER, p_process_id NUMBER) RETURN NUMBER IS
l_org_count NUMBER;
BEGIN
SELECT COUNT(1)
INTO l_org_count
FROM cux_ediint_item_move_in ceimi
WHERE ceimi.ou_org_id = p_org_id
AND ceimi.process_id = p_process_id
AND ceimi.request_id = fnd_global.conc_request_id
AND ROWNUM = 1;
RETURN l_org_count;
EXCEPTION
WHEN OTHERS THEN
put_log('得到OU关联的处理条数时候出现错误:' || SQLERRM);
RETURN NULL;
END get_org_count;
/*=======================================================
* FUNCTION / PROCEDURE
* output_move_order_info
* DESCRIPTION:
* 输出处理信息
* ARGUMENT:
* p_line_count ：本次请求处理的记录总数量
* p_line_seccess_count : 本次请求处理成功的记录数量
* RETURN:
* N/A
* HISTORY:
* 1.00 27/10/2010 cxy
=========================================================*/
PROCEDURE output_move_order_info(p_line_count IN NUMBER,
p_line_success_count IN NUMBER) IS
--取出物料搬运单客户化表中的业务实体的信息,不取重复的记录
CURSOR cur_org_info IS
SELECT DISTINCT
hou.organization_id org_id, --库存组织id
hou.name org_name --库存组织名称
FROM cux_ediint_item_move_in ceimi,
mtl_txn_request_lines mtrl,
mtl_txn_request_headers mtrh,
org_organization_definitions ood,
hr_operating_units hou
WHERE ceimi.line_id = mtrl.line_id
AND mtrl.header_id = mtrh.header_id
AND ood.organization_id = mtrl.organization_id
AND ood.operating_unit = hou.organization_id
AND ceimi.request_id = fnd_global.conc_request_id;
--处理成功的物料搬运单行信息
CURSOR cur_move_order_success(p_org_id NUMBER) IS
SELECT hou.name org_name, --OU名称
ceimi.header_id header_id, --物料搬运单头id
mtrh.request_number request_number, --物料搬运单编号
ml.meaning move_order_type_meaning, --物料搬运单类型
ceimi.line_id line_id, --物料搬运单行id
mtrl.line_number line_number, --行编号
msib.segment1 item_code, --物料编码
ceimi.transaction_quantity transaction_quantity, --事务处理数量
ood.organization_code organization_code, --库存组织代码
ood.organization_name organization_name, --库存组织名称
mtrl.from_subinventory_code from_subinventory_code, --来源子库存
mtrl.to_subinventory_code to_subinventory_code --目标子库存
FROM cux_ediint_item_move_in ceimi,
mtl_txn_request_lines mtrl,
mtl_txn_request_headers mtrh,
org_organization_definitions ood,
hr_operating_units hou,
mfg_lookups ml,
mtl_system_items_b msib
WHERE ceimi.line_id = mtrl.line_id
AND mtrl.header_id = mtrh.header_id
AND ood.organization_id = mtrl.organization_id
AND ood.operating_unit = hou.organization_id
AND mtrh.move_order_type = ml.lookup_code
AND ml.lookup_type = 'MOVE_ORDER_TYPE'
AND msib.organization_id = mtrl.organization_id
AND msib.inventory_item_id = mtrl.inventory_item_id
AND ceimi.process_id = 4 --处理成功的记录
AND ceimi.request_id = fnd_global.conc_request_id
AND hou.organization_id = p_org_id;
--出错的物料搬运单行信息
CURSOR cur_move_order_error(p_org_id NUMBER) IS
SELECT hou.name org_name, --OU名称
ceimi.header_id header_id, --物料搬运单头id
mtrh.request_number request_number, --物料搬运单编号
ml.meaning move_order_type_meaning,--物料搬运单类型
ceimi.line_id line_id, --物料搬运单行id
mtrl.line_number line_number, --行编号
msib.segment1 item_code, --物料编码
ceimi.transaction_quantity transaction_quantity, --事务处理数量
ood.organization_code organization_code, --库存组织代码
ood.organization_name organization_name, --库存组织名称
mtrl.from_subinventory_code from_subinventory_code,--来源子库存
mtrl.to_subinventory_code to_subinventory_code, --目标子库存
ceimi.error_message error_message --错误信息
FROM cux_ediint_item_move_in ceimi,
mtl_txn_request_lines mtrl,
mtl_txn_request_headers mtrh,
org_organization_definitions ood,
hr_operating_units hou,
mfg_lookups ml,
mtl_system_items_b msib
WHERE ceimi.line_id = mtrl.line_id
AND mtrl.header_id = mtrh.header_id
AND ood.organization_id = mtrl.organization_id
AND ood.operating_unit = hou.organization_id
AND mtrh.move_order_type = ml.lookup_code
AND ml.lookup_type = 'MOVE_ORDER_TYPE'
AND msib.organization_id = mtrl.organization_id
AND msib.inventory_item_id = mtrl.inventory_item_id
AND ceimi.process_id = 3 --处理失败的记录
AND ceimi.request_id = fnd_global.conc_request_id
AND hou.organization_id = p_org_id;
l_user_name VARCHAR2(100); --用户名
l_success_index NUMBER; --循环标志
l_error_index NUMBER; --循环标志
l_count NUMBER; --循环标志
l_org_count NUMBER; --循环标志
BEGIN
SELECT fu.user_name
INTO l_user_name
FROM fnd_user fu
WHERE fu.user_id = NVL(fnd_global.user_id, -1);
put_file(chr(13) || chr(10));
put_file('提交人:' || rpad(l_user_name, 20, ' '));
put_file('报表日期:' || TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'));
put_file(chr(13) || chr(10));
put_file('本次处理的总的记录条数为: ' || to_char(p_line_count) || ' 条');
put_file('处理成功的记录条数为: ' || to_char(p_line_success_count) || ' 条');
IF (p_line_count > p_line_success_count) THEN
put_file('处理出错的记录条数为: ' ||
to_char(p_line_count - p_line_success_count) || ' 条');
END IF;
put_file(chr(13) || chr(10));
IF p_line_success_count <> 0 THEN
put_file('处理成功的物料搬运单');
put_file(lpad('-', 190, '-'));
--输出处理成功的物料搬运单的信息
FOR rec_org_info IN cur_org_info LOOP
l_org_count := get_org_count(rec_org_info.org_id, 4);
IF l_org_count <> 0 THEN
put_file('业务实体:' || rec_org_info.org_name);
put_file(rpad('-', 190, '-'));
put_file(rpad('物料搬运单编号', 30, ' ') || rpad('类型', 20, ' ') ||
rpad('行号', 20, ' ') || rpad('物料', 40, ' ') ||
rpad('来源子库存', 20, ' ') || rpad('目标子库存', 20, ' ') ||
rpad('事务处理数量', 40, ' '));
put_file(chr(13) || chr(10));
END IF;
FOR rec_move_order_succes IN cur_move_order_success(rec_org_info.org_id) LOOP
put_file(rpad(rec_move_order_succes.request_number, 30, ' ') ||
rpad(rec_move_order_succes.move_order_type_meaning,
20,
' ') ||
rpad(rec_move_order_succes.line_number, 20, ' ') ||
rpad(rec_move_order_succes.item_code, 40, ' ') ||
rpad(rec_move_order_succes.from_subinventory_code,
20,
' ') || rpad(rec_move_order_succes.to_subinventory_code,
20,
' ') ||
rpad(rec_move_order_succes.transaction_quantity,
40,
' '));
put_file(chr(13) || chr(10));
END LOOP; --FOR rec_hold_order_info IN cur_hold_order_info LOOP
put_file(chr(13) || chr(10));
END LOOP; --FOR rec_org_info IN cur_org_info LOOP
put_file(lpad('-', 95, '-') || rpad('-', 95, '-'));
END IF;
put_file(chr(13) || chr(10));
--输出处理失败的物料搬运单
IF p_line_count - p_line_success_count > 0 THEN
put_file('处理出错的物料搬运单');
put_file(lpad('处理出错的', 145, '-') || rpad('物料搬运单', 145, '-'));
IF (p_line_count >= p_line_success_count) THEN
--如果存在错误的记录
--输出处理成功的物料搬运单的信息
FOR rec_org_info IN cur_org_info LOOP
l_org_count := get_org_count(rec_org_info.org_id, 3);
IF l_org_count <> 0 THEN
put_file('业务实体:' || rec_org_info.org_name);
put_file(rpad('-', 290, '-'));
put_file(rpad('物料搬运单编号', 30, ' ') || rpad('类型', 20, ' ') ||
rpad('行号', 20, ' ') || rpad('物料', 40, ' ') ||
rpad('来源子库存', 20, ' ') || rpad('目标子库存', 20, ' ') ||
rpad('事务处理数量', 40, ' ') || rpad('错误信息', 100, ' '));
put_file(chr(13) || chr(10));
END IF;
FOR rec_move_order_error IN cur_move_order_error(rec_org_info.org_id) LOOP
put_file(rpad(rec_move_order_error.request_number, 30, ' ') ||
rpad(rec_move_order_error.move_order_type_meaning,
20,
' ') ||
rpad(rec_move_order_error.line_number, 20, ' ') ||
rpad(rec_move_order_error.item_code, 40, ' ') ||
rpad(rec_move_order_error.from_subinventory_code,
20,
' ') || rpad(rec_move_order_error.to_subinventory_code,
20,
' ') ||
rpad(rec_move_order_error.transaction_quantity,
40,
' ') ||
rpad(rec_move_order_error.error_message, 100, ' '));
put_file(chr(13) || chr(10));
END LOOP; --FOR rec_hold_order_info IN cur_hold_order_info LOOP
END LOOP; --FOR rec_org_info IN cur_org_info LOOP
END IF;
IF p_line_count - p_line_success_count > 0 THEN
put_file(lpad('-', 145, '-') || rpad('-', 145, '-'));
END IF;
END IF;
END output_move_order_info;
/*=======================================================
* FUNCTION / PROCEDURE
* get_move_order
* DESCRIPTION:
* 得到物料搬运单的一条记录(一个物料搬运单头和对应的所有的物表搬运单的行)
* ARGUMENT:
* x_return_status :状态
* x_msg_count :错误信息数量
* x_msg_data :错误信息
* x_trohdr_rec :物料搬运单头的一行记录
* x_trohdr_val_rec :物料搬运单头的一行记录对应的值
* x_trolin_tbl :一个物料搬运单头对应的所有物料搬运单行
* x_trolin_val_tbl :一个物料搬运单头对应的所有物料搬运单行的值
* p_header_id :物料搬运单头id
* RETURN:
* N/A
* HISTORY:
* 1.00 27/10/2010 cxy
=========================================================*/
PROCEDURE get_move_order(x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
x_trohdr_rec OUT NOCOPY INV_MOVE_ORDER_PUB.Trohdr_Rec_Type,
x_trohdr_val_rec OUT NOCOPY INV_MOVE_ORDER_PUB.Trohdr_Val_Rec_Type,
x_trolin_tbl OUT NOCOPY INV_MOVE_ORDER_PUB.Trolin_Tbl_Type,
x_trolin_val_tbl OUT NOCOPY INV_MOVE_ORDER_PUB.Trolin_Val_Tbl_Type,
p_header_id IN NUMBER) IS
BEGIN
--调用系统标准的API得到物料搬运单的相关信息
INV_Move_Order_PUB.Get_Move_Order(p_api_version_number => 1.0,
p_init_msg_list => fnd_api.G_FALSE,
p_return_values => fnd_api.G_FALSE,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_header_id => p_header_id,
p_header => fnd_api.G_MISS_CHAR,
x_trohdr_rec => x_trohdr_rec,
x_trohdr_val_rec => x_trohdr_val_rec,
x_trolin_tbl => x_trolin_tbl,
x_trolin_val_tbl => x_trolin_val_tbl);
EXCEPTION
WHEN OTHERS THEN
put_log('得到物料搬运单时出现异常:' || SQLERRM);
END get_move_order;
/*=======================================================
* FUNCTION / PROCEDURE
* get_locator_type
* DESCRIPTION:
* 得到货位控制类型
* ARGUMENT:
* p_organization_id :库存组织id
* p_secondary_inventory_code:子库存代码
* RETURN:
* 货位控制类型
* HISTORY:
* 1.00 27/10/2010 cxy
=========================================================*/
FUNCTION get_locator_type(p_organization_id IN NUMBER,
p_secondary_inventory_code IN VARCHAR2)
RETURN NUMBER IS
l_locator_type_id NUMBER; --货位控制类型
BEGIN
SELECT msi.locator_type
INTO l_locator_type_id
FROM mtl_secondary_inventories msi
WHERE msi.organization_id = p_organization_id
AND msi.secondary_inventory_name = p_secondary_inventory_code;
RETURN l_locator_type_id;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN NULL;
WHEN TOO_MANY_ROWS THEN
put_log('得到货位控制类型时返回的多个行!');
WHEN OTHERS THEN
put_log('得到货位控制类型时出现异常:' || SQLERRM);
END get_locator_type;
/*=======================================================
* FUNCTION / PROCEDURE
* create_locator
* DESCRIPTION:
* 自动创建货位
* ARGUMENT:
* x_return_status :状态
* x_msg_count :错误信息数量
* x_msg_data :错误信息
* x_locator_id :货位id
* x_locator_exists :是否存在货位
* p_organization_id :库存组织id
* p_subinventory_code :子库存代码
* p_project_id :项目id
* p_task_id :任务id
* p_locator_type :货位类型
* RETURN:
* N/A
* HISTORY:
* 1.00 27/10/2010 cxy
=========================================================*/
PROCEDURE create_locator(x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
x_locator_id OUT NUMBER,
x_locator_exists OUT VARCHAR2,
p_organization_id IN NUMBER,
p_subinventory_code IN VARCHAR2,
p_project_id IN NUMBER,
p_task_id IN NUMBER,
p_locator_type IN NUMBER) IS
l_segment_delimiter VARCHAR2(1); --键弹性域段分隔符
l_concatenated_segments VARCHAR2(100); --库存货位组合
l_project_number VARCHAR2(25); --项目编号
l_task_number VARCHAR2(25); --任务编号
l_user_id NUMBER; --登录的用户
l_resp_id NUMBER; --职位id
l_resp_appl_id NUMBER; --职责应用id
BEGIN
--设置MFG_ORGANIZATION_ID预置文件的值
fnd_profile.put('MFG_ORGANIZATION_ID', p_organization_id);
--得到段分隔符
SELECT fifs.concatenated_segment_delimiter
INTO l_segment_delimiter
FROM fnd_id_flex_structures fifs
WHERE fifs.id_flex_structure_code = 'STOCK_LOCATORS';
--得到项目编号
SELECT ppa.segment1
INTO l_project_number
FROM pa_projects_all ppa
WHERE ppa.project_id = p_project_id;
--得到任务编号
SELECT pt.task_number
INTO l_task_number
FROM pa_tasks pt
WHERE pt.task_id = p_task_id;
--拼接子库存货位组合
IF l_mo_debug_falg = 'Y' THEN
--输出调试信息
put_log('库存组织id:' || p_organization_id);
put_log('货位类型:' || p_locator_type);
put_log('子库存:' || p_subinventory_code);
END IF;
l_concatenated_segments := p_subinventory_code || l_segment_delimiter ||
l_project_number || l_segment_delimiter ||
l_task_number;
IF l_mo_debug_falg = 'Y' THEN--输出调试信息
put_log('弹性域组合:' || l_concatenated_segments);
END IF;
--调用API自动创建货位
inv_loc_wms_pub.create_locator(x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_inventory_location_id => x_locator_id,
x_locator_exists => x_locator_exists,
p_organization_id => p_organization_id,
p_organization_code => NULL,
p_concatenated_segments => l_concatenated_segments,
p_description => NULL,
p_inventory_location_type => p_locator_type,
p_picking_order => NULL,
p_location_maximum_units => NULL,
p_subinventory_code => p_subinventory_code,
p_location_weight_uom_code => NULL,
p_max_weight => NULL,
p_volume_uom_code => NULL,
p_max_cubic_area => NULL,
p_x_coordinate => NULL,
p_y_coordinate => NULL,
p_z_coordinate => NULL,
p_physical_location_id => NULL,
p_pick_uom_code => NULL,
p_dimension_uom_code => NULL,
p_length => NULL,
p_width => NULL,
p_height => NULL,
p_status_id => NULL,
p_dropping_order => NULL);
IF l_mo_debug_falg = 'Y' THEN--输出调试信息
put_log('自动生成的货位返回状态:' || x_return_status);
put_log('自动生成的货位错误条数:' || x_msg_count);
put_log('自动生成的货位错误信息:' || x_msg_data);
put_log('自动生成的货位id为:' || x_locator_id);
END IF;
IF x_locator_id IS NULL THEN
put_log('自动创建货位时出错:' || x_msg_data);
END IF;
EXCEPTION
WHEN OTHERS THEN
put_log('自动创建货位时出现异常:' || SQLERRM);
END create_locator;
/*=======================================================
* FUNCTION / PROCEDURE
* check_and_create_locator
* DESCRIPTION:
* 检查和生成货位
* ARGUMENT:
* x_return_status :状态
* x_msg_count :错误信息数量
* x_msg_data :错误信息
* x_locator_type :货位类型
* x_from_locator_id :来源货位id
* x_to_locator_id :目标货位id
* p_trolin_rec :物料搬运单行的一行记录
* RETURN:
* N/A
* HISTORY:
* 1.00 27/10/2010 cxy
=========================================================*/
PROCEDURE check_and_create_locator(x_return_status OUT VARCHAR2,
x_msg_data OUT VARCHAR2,
x_locator_type OUT VARCHAR2,
x_from_locator_id OUT NUMBER,
x_to_locator_id OUT NUMBER,
p_trolin_rec IN INV_MOVE_ORDER_PUB.Trolin_Rec_Type) IS
l_from_inv_locator_type NUMBER; --源子库存的货位控制类型
l_to_inv_locator_type NUMBER; --目标子库存的货位控制类型
x_msg_count NUMBER; --错误信息数
x_locator_id NUMBER; --货位id
x_locator_exists VARCHAR2(1); --货位是否存在标志
BEGIN
--来源子库存的货位控制类型
l_from_inv_locator_type := get_locator_type(p_trolin_rec.organization_id,
p_trolin_rec.from_subinventory_code);
--目标子库存的货位控制类型
l_to_inv_locator_type := get_locator_type(p_trolin_rec.organization_id,
p_trolin_rec.to_subinventory_code);
IF l_mo_debug_falg = 'Y' THEN--输出调试信息
put_log(p_trolin_rec.from_subinventory_code || '的来源字库货位类型为:' ||
l_from_inv_locator_type);
put_log(p_trolin_rec.to_subinventory_code || '的来源字库货位类型为:' ||
l_to_inv_locator_type);
END IF;
IF l_from_inv_locator_type = l_to_inv_locator_type THEN
IF l_from_inv_locator_type <> 0 THEN--启用了货位控制
x_locator_type := l_from_inv_locator_type;
IF p_trolin_rec.from_locator_id IS NOT NULL THEN --货位不为空
x_return_status := fnd_api.G_RET_STS_SUCCESS;
x_from_locator_id := p_trolin_rec.from_locator_id;
ELSE--货位为空
--自动创建货位(源库存货位)
create_locator(x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_locator_id => x_from_locator_id,
x_locator_exists => x_locator_exists,
p_organization_id => p_trolin_rec.organization_id,
p_subinventory_code => p_trolin_rec.from_subinventory_code,
p_project_id => p_trolin_rec.project_id,
p_task_id => p_trolin_rec.task_id,
p_locator_type => x_locator_type);
IF l_mo_debug_falg = 'Y' THEN--输出调试信息
put_log('x_return_status:' || x_return_status);
put_log('x_msg_count:' || x_msg_count);
put_log('x_msg_data:' || x_msg_data);
put_log('x_from_locator_id:' || x_from_locator_id);
put_log('x_locator_exists:' || x_locator_exists);
END IF;
IF x_from_locator_id IS NULL THEN
x_msg_data := '自动创建货位时错误异常,请确认是否已经包含子库存、项目和任务的信息';
END IF;
END IF;
ELSE--未启用货位控制
x_locator_type := 0;
END IF;
IF l_to_inv_locator_type <> 0 THEN --启用了货位控制
x_locator_type := l_to_inv_locator_type;
IF p_trolin_rec.to_locator_id IS NOT NULL THEN--货位不为空
x_return_status := fnd_api.G_RET_STS_SUCCESS;
x_to_locator_id := p_trolin_rec.to_locator_id;
ELSE--货位为空
--自动创建货位(目标库存货位)
create_locator(x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_locator_id => x_to_locator_id,
x_locator_exists => x_locator_exists,
p_organization_id => p_trolin_rec.organization_id,
p_subinventory_code => p_trolin_rec.to_subinventory_code,
p_project_id => p_trolin_rec.project_id,
p_task_id => p_trolin_rec.task_id,
p_locator_type => x_locator_type);
IF x_from_locator_id IS NULL THEN
x_msg_data := '自动创建货位时错误异常,请确认是否已经包含子库存、项目和任务的信息';
END IF;
END IF;
ELSE --未启用货位控制
x_locator_type := 0;
END IF;
ELSE
put_log('源子库存和目标子库存的货位控制类型不一致:');
END IF;
EXCEPTION
WHEN OTHERS THEN
put_log('检查和生成货位时出现异常:' || SQLERRM);
END check_and_create_locator;
/*=======================================================
* FUNCTION / PROCEDURE
* validate_subinv_quantity
* DESCRIPTION:
* 验证子库存的现用量和可用量
* ARGUMENT:
* x_return_status :状态
* x_msg_count :错误信息数量
* x_msg_data :错误信息
* p_trolin_rec :物料搬运单行的一行记录
* p_transaction_quantity :事务处理数量
* p_transaction_date :事务处理日期
* RETURN:
* N/A
* HISTORY:
* 1.00 27/10/2010 cxy
=========================================================*/
PROCEDURE validate_subinv_quantity(x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_trolin_rec IN INV_MOVE_ORDER_PUB.Trolin_Rec_Type,
p_transaction_quantity IN NUMBER,
p_transaction_date IN DATE) IS
x_qoh NUMBER; --quantity on hand
x_rqoh NUMBER; --reservable quantity on hand
x_qr NUMBER; --quantity reserved
x_qs NUMBER; --quantity suggested
x_att NUMBER; --available to transact
x_atr NUMBER := 0; --available to reserve
x_sqoh NUMBER; --secondary quantity on hand -- invConv change
x_srqoh NUMBER; --secondary reservable quantity on hand -- invConv change
x_sqr NUMBER; --secondary quantity reserved -- invConv change
x_sqs NUMBER; --secondare quantity suggested -- invConv change
x_satt NUMBER; --secondary available to transact -- invConv change
x_satr NUMBER; --secondary available to reserve -- invConv change
l_lot_control_flag BOOLEAN := FALSE;
l_organization_code VARCHAR2(3); --库存组织代码
l_item_code VARCHAR2(40); --物料代码
x_locator_type VARCHAR2(60); --货位控制类型
x_from_locator_id NUMBER; --来源货位
x_to_locator_id NUMBER; --目标货位
l_locator_id NUMBER; --子库存的货位
BEGIN
--得到库存代码
SELECT mp.organization_code
INTO l_organization_code
FROM mtl_parameters mp
WHERE mp.organization_id = p_trolin_rec.organization_id;
--得到物料代码
SELECT msib.segment1
INTO l_item_code
FROM mtl_system_items_b msib
WHERE msib.organization_id = p_trolin_rec.organization_id
AND msib.inventory_item_id = p_trolin_rec.inventory_item_id;
--调用系统的api检查某一库存组织某一字库的某一个物料的现有量、可用量等数量
inv_quantity_tree_pub.query_quantities(p_api_version_number => 1.0,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_organization_id => p_trolin_rec.organization_id, --库存组织
p_inventory_item_id => p_trolin_rec.inventory_item_id, --item id
p_tree_mode => 2,
p_is_revision_control => FALSE,
p_is_lot_control => l_lot_control_flag, --是否启用批次控制
p_lot_expiration_date => p_transaction_date, --事务处理数量
p_is_serial_control => FALSE,
p_grade_code => NULL, -- INVCONV NOT NEEDED NOW
p_revision => NULL, --版本号
p_lot_number => NULL, --批次控制
p_subinventory_code => p_trolin_rec.from_subinventory_code, --来源子库存代码
p_locator_id => NULL, --货位id
x_qoh => x_qoh, --现有量
x_rqoh => x_rqoh, --现有包流量
x_qr => x_qr, --保留量
x_qs => x_qs, --建议保留量
x_att => x_att, --可处理数量
x_atr => x_atr, --保留量
x_sqoh => x_sqoh, -- INVCONV
x_srqoh => x_srqoh, -- INVCONV
x_sqr => x_sqr, -- INVCONV
x_sqs => x_sqs, -- INVCONV
x_satt => x_satt, -- INVCONV
x_satr => x_satr); -- INVCONV
IF l_mo_debug_falg = 'Y' THEN
--输出调试信息
put_log('得到的库存现用量的状态为:' || x_return_status);
put_log('得到的库存现用量为:' || x_atr);
END IF;
IF x_atr <= 0 THEN
x_return_status := fnd_api.G_RET_STS_UNEXP_ERROR;
x_msg_data := '库存组织 ' || l_organization_code || ' 的子库存 ' ||
p_trolin_rec.from_subinventory_code || ' 中物料 ' ||
l_item_code || ' 的可用量为' || x_atr;
RETURN;
END IF;
--检查并创建货位
check_and_create_locator(x_return_status => x_return_status,
x_msg_data => x_msg_data,
x_locator_type => x_locator_type,
x_from_locator_id => x_from_locator_id,
x_to_locator_id => x_to_locator_id,
p_trolin_rec => p_trolin_rec);
IF x_locator_type <> 0 THEN
l_locator_id := x_from_locator_id;
ELSE
l_locator_id := NULL;
END IF;
IF x_return_status IS NULL THEN
x_return_status := fnd_api.G_RET_STS_UNEXP_ERROR;
x_msg_data := x_msg_data;
RETURN;
END IF;
--调用系统的api检查某一库存组织某一字库的某一个物料的现有量、可用量等数量
x_qoh := NULL;
x_rqoh := NULL;
x_qr := NULL;
x_qs := NULL;
x_att := NULL;
x_atr := NULL;
x_sqoh := NULL;
x_srqoh := NULL;
x_sqr := NULL;
x_sqs := NULL;
x_satt := NULL;
x_satr := NULL;
--调用API得到库存下的物料的现用量和可用量
inv_quantity_tree_pub.query_quantities(p_api_version_number => 1.0,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_organization_id => p_trolin_rec.organization_id, --库存组织
p_inventory_item_id => p_trolin_rec.inventory_item_id, --item id
p_tree_mode => 2,
p_is_revision_control => FALSE,
p_is_lot_control => l_lot_control_flag, --是否启用批次控制
p_lot_expiration_date => p_transaction_date, --事务处理数量
p_is_serial_control => FALSE,
p_grade_code => NULL, -- INVCONV NOT NEEDED NOW
p_revision => NULL, --版本号
p_lot_number => NULL, --批次控制
p_subinventory_code => p_trolin_rec.from_subinventory_code, --来源子库存代码
p_locator_id => l_locator_id, --货位id
x_qoh => x_qoh, --现有量
x_rqoh => x_rqoh, --现有包流量
x_qr => x_qr, --保留量
x_qs => x_qs, --建议保留量
x_att => x_att, --可处理数量
x_atr => x_atr, --保留量
x_sqoh => x_sqoh, -- INVCONV
x_srqoh => x_srqoh, -- INVCONV
x_sqr => x_sqr, -- INVCONV
x_sqs => x_sqs, -- INVCONV
x_satt => x_satt, -- INVCONV
x_satr => x_satr); -- INVCONV
IF l_mo_debug_falg = 'Y' THEN
--输出调试信息
put_log('库存组织');
END IF;
IF x_return_status <> fnd_api.G_RET_STS_SUCCESS THEN
x_return_status := fnd_api.G_RET_STS_UNEXP_ERROR;
x_msg_data := '获取库存组织 ' || l_organization_code || ' 的子库存 ' ||
p_trolin_rec.from_subinventory_code || ' 中物料 ' ||
l_item_code || ' 的可用量时出现异常!';
END IF;
IF l_mo_debug_falg = 'Y' THEN
--输出调试信息
put_log('得到的库存现用量的状态为:' || x_return_status);
put_log('得到的库存现用量为:' || x_atr);
END IF;
IF x_atr <= 0 THEN
x_return_status := fnd_api.G_RET_STS_UNEXP_ERROR;
x_msg_data := '库存组织 ' || l_organization_code || ' 的子库存 ' ||
p_trolin_rec.from_subinventory_code || ' 中物料 ' ||
l_item_code || ' 的可用量为 ' || x_atr;
END IF;
IF x_atr <> 0 AND x_atr < p_transaction_quantity THEN
x_return_status := fnd_api.G_RET_STS_UNEXP_ERROR;
x_msg_data := '库存组织 ' || l_organization_code || ' 的子库存 ' ||
p_trolin_rec.from_subinventory_code || ' 中物料 ' ||
l_item_code || ' 的可用量 ' || x_atr || ' 小于处理数量 ' ||
p_transaction_quantity;
END IF;
EXCEPTION
WHEN OTHERS THEN
put_log('检查库存的现用量和可用量时出现异常:' || SQLERRM);
END validate_subinv_quantity;
/*=======================================================
* FUNCTION / PROCEDURE
* validate_data
* DESCRIPTION:
* 验证客户化临时表的数据
* ARGUMENT:
* x_return_status :状态
* x_msg_count :错误信息数量
* x_msg_data :错误信息
* p_trolin_rec :物料搬运单行的一行记录
* p_line_id :物料搬运单行id
* p_transaction_quantity :事务处理数量
* p_transaction_date :事务处理日期
* RETURN:
* N/A
* HISTORY:
* 1.00 27/10/2010 cxy
=========================================================*/
PROCEDURE validate_data(x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_trolin_rec IN INV_MOVE_ORDER_PUB.Trolin_Rec_Type,
p_line_id IN NUMBER,
p_transaction_quantity IN NUMBER,
p_transaction_date IN DATE) IS
l_error_flag VARCHAR2(1) := 'N'; --错误标志
l_error_message VARCHAR2(1000) := NULL;--错误信息
BEGIN
--初始化
l_error_flag := 'N';
IF p_trolin_rec.quantity <> p_transaction_quantity THEN
--接口中的数量和EBS系统中的剩余的数量不一致
l_error_flag := 'Y';
l_error_message := '接口中的数量' || p_transaction_quantity ||
'与EBS系统中的剩余的数量' || p_trolin_rec.quantity || '不一致';
GOTO end_error;
END IF;
--检查并验证库存的现用量和可用量
validate_subinv_quantity(x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_trolin_rec => p_trolin_rec,
p_transaction_quantity => p_transaction_quantity,
p_transaction_date => p_transaction_date);
IF x_return_status <> fnd_api.G_RET_STS_SUCCESS THEN
x_return_status := fnd_api.G_RET_STS_UNEXP_ERROR;
RETURN;
END IF;
NULL; --添加一个可执行语句，防止GOTO语句出错
<<end_error>>
IF l_error_flag = 'N' THEN
x_return_status := fnd_api.G_RET_STS_SUCCESS;
ELSE
x_return_status := fnd_api.G_RET_STS_UNEXP_ERROR;
x_msg_count := 1;
x_msg_data := l_error_message;
END IF;
EXCEPTION
WHEN OTHERS THEN
put_log('进行数据有效性验证时出现异常:' || SQLERRM);
END validate_data;
/*=======================================================
* FUNCTION / PROCEDURE
* get_trolin_rec
* DESCRIPTION:
* 得到物料搬运单的一行记录
* ARGUMENT:
* x_return_status :状态
* x_trolin_rec :物料搬运单行的一行记录
* p_trolin_tbl :一条物料搬运单的所有搬运单行
* p_line_id :物料搬运单行id
* RETURN:
* N/A
* HISTORY:
* 1.00 27/10/2010 cxy
=========================================================*/
PROCEDURE get_trolin_rec(x_return_status OUT NOCOPY VARCHAR2,
x_trolin_rec OUT NOCOPY INV_MOVE_ORDER_PUB.Trolin_Rec_Type,
p_trolin_tbl IN INV_MOVE_ORDER_PUB.Trolin_Tbl_Type,
p_line_id IN NUMBER) IS
l_table_index NUMBER := 0;
BEGIN
FOR l_table_index IN 1 .. p_trolin_tbl.COUNT LOOP
IF p_line_id = p_trolin_tbl(l_table_index).line_id THEN
x_trolin_rec := p_trolin_tbl(l_table_index);
x_return_status := fnd_api.G_RET_STS_SUCCESS;
RETURN;
END IF;
END LOOP; --FOR x_table_index IN x_trolin_tbl.COUNT LOOP
x_return_status := fnd_api.G_RET_STS_UNEXP_ERROR;
EXCEPTION
WHEN OTHERS THEN
put_log('得到物料搬运单时出现异常:' || SQLERRM);
END get_trolin_rec;
/*=======================================================
* FUNCTION / PROCEDURE
* get_transaction_action_id
* DESCRIPTION:
* 得到事务处理的活动id
* ARGUMENT:
* p_transaction_type_id:事务处理类型id
* RETURN:
* 事务处理的活动id
* HISTORY:
* 1.00 27/10/2010 cxy
=========================================================*/
FUNCTION get_transaction_action_id(p_transaction_type_id IN NUMBER)
RETURN NUMBER IS
l_transaction_action_id NUMBER;--事务处理的活动id
BEGIN
SELECT mtt.transaction_action_id
INTO l_transaction_action_id
FROM mtl_transaction_types mtt
WHERE mtt.transaction_type_id = p_transaction_type_id;
RETURN l_transaction_action_id;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN NULL;
WHEN TOO_MANY_ROWS THEN
put_log('得到事务处理的活动id时返回的多个行!');
WHEN OTHERS THEN
put_log('得到物料搬运单活动id时出现异常:' || SQLERRM);
END get_transaction_action_id;
/*=======================================================
* FUNCTION / PROCEDURE
* get_acct_period_id
* DESCRIPTION:
* 得到库存会计期id
* ARGUMENT:
* p_organization_id :库存组织id
* p_transaction_date:事务处理日期
* RETURN:
* 库存会计期id
* HISTORY:
* 1.00 27/10/2010 cxy
=========================================================*/
FUNCTION get_acct_period_id(p_organization_id IN NUMBER,
p_transaction_date IN DATE) RETURN NUMBER IS
l_acct_period_id NUMBER;--库存会计期id
BEGIN
SELECT oap.acct_period_id
INTO l_acct_period_id
FROM org_acct_periods oap
WHERE oap.organization_id = p_organization_id
AND (p_transaction_date BETWEEN oap.period_start_date AND
oap.schedule_close_date);
RETURN l_acct_period_id;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN NULL;
WHEN TOO_MANY_ROWS THEN
put_log('得到库存会计期id时返回的多个行!');
WHEN OTHERS THEN
put_log('得到库存会计期id时出现异常:' || SQLERRM);
END get_acct_period_id;
/*=======================================================
* FUNCTION / PROCEDURE
* insert_mmtt_row
* DESCRIPTION:
* 插入某一物料搬运单事务处理临时表的一条记录
* ARGUMENT:
* x_return_status :状态
* x_transaction_temp_id :临时表id
* x_msg_data :错误信息
* x_trohdr_rec :物料搬运单头的一行记录
* x_trohdr_val_rec :物料搬运单头的一行记录对应的值
* x_trolin_tbl :一个物料搬运单头对应的所有物料搬运单行
* x_trolin_val_tbl :一个物料搬运单头对应的所有物料搬运单行的值
* p_header_id :物料搬运单头id
* p_line_id :物料搬运单行id
* p_transaction_quantity :事务处理数量
* p_transaction_date :事务处理日期
* RETURN:
* N/A
* HISTORY:
* 1.00 27/10/2010 cxy
=========================================================*/
PROCEDURE insert_mmtt_row(x_return_status OUT NOCOPY VARCHAR2,
x_transaction_temp_id OUT NUMBER,
p_trohdr_rec IN INV_MOVE_ORDER_PUB.Trohdr_Rec_Type,
p_trohdr_val_rec IN INV_MOVE_ORDER_PUB.Trohdr_Val_Rec_Type,
p_trolin_tbl IN INV_MOVE_ORDER_PUB.Trolin_Tbl_Type,
p_trolin_val_tbl IN INV_MOVE_ORDER_PUB.Trolin_Val_Tbl_Type,
p_header_id IN NUMBER,
p_line_id IN NUMBER,
p_transaction_quantity IN NUMBER,
p_transaction_date IN DATE) IS
l_mo_line_detail_rec inv_mo_line_detail_util.g_mmtt_rec;
l_transaction_temp_id NUMBER; --临时表的id
l_trolin_rec INV_MOVE_ORDER_PUB.Trolin_Rec_Type;
x_locator_type VARCHAR2(60); --货位控制类型
x_from_locator_id NUMBER; --来源货位
x_to_locator_id NUMBER; --目标货位
x_msg_data VARCHAR2(2000); --错误信息
BEGIN
--得到某一物料搬运单行的信息
get_trolin_rec(x_return_status => x_return_status,
x_trolin_rec => l_trolin_rec,
p_trolin_tbl => p_trolin_tbl,
p_line_id => p_line_id);
--检查是否启用货位控制,如果启用，则动态生成货位
SELECT mtl_material_transactions_s.nextval
INTO l_transaction_temp_id
FROM DUAL;
--判断货位是否已经存在，如果存在,则自动创建货位
check_and_create_locator(x_return_status => x_return_status,
x_msg_data => x_msg_data,
x_locator_type => x_locator_type,
x_from_locator_id => x_from_locator_id,
x_to_locator_id => x_to_locator_id,
p_trolin_rec => l_trolin_rec);
IF l_mo_debug_falg = 'Y' THEN
--输出调试信息
put_log('项目号:' || l_trolin_rec.project_id);
put_log('项目号:' || l_trolin_rec.task_id);
put_log('来源货位id:' || x_from_locator_id);
put_log('目标货位id:' || x_to_locator_id);
END IF;
l_mo_line_detail_rec.locator_id := x_from_locator_id; --源子库存货位
l_mo_line_detail_rec.transfer_to_location := x_to_locator_id; --目标子库存货位
l_mo_line_detail_rec.transaction_header_id := NULL;
l_mo_line_detail_rec.transaction_temp_id := l_transaction_temp_id;
l_mo_line_detail_rec.source_code := NULL;
l_mo_line_detail_rec.source_line_id := NULL;
l_mo_line_detail_rec.transaction_mode := 1;
l_mo_line_detail_rec.lock_flag := 'N';
l_mo_line_detail_rec.last_update_date := SYSDATE;
l_mo_line_detail_rec.last_updated_by := fnd_global.user_id;
l_mo_line_detail_rec.creation_date := SYSDATE;
l_mo_line_detail_rec.created_by := fnd_global.user_id;
l_mo_line_detail_rec.last_update_login := fnd_global.user_id;
l_mo_line_detail_rec.request_id := NULL;
l_mo_line_detail_rec.program_application_id := NULL;
l_mo_line_detail_rec.program_id := NULL;
l_mo_line_detail_rec.program_update_date := NULL;
--从物料搬运单得到item_id
l_mo_line_detail_rec.inventory_item_id := l_trolin_rec.inventory_item_id;
l_mo_line_detail_rec.revision := NULL;
l_mo_line_detail_rec.organization_id := l_trolin_rec.organization_id;
l_mo_line_detail_rec.subinventory_code := l_trolin_rec.from_subinventory_code;
--l_mo_line_detail_rec.locator_id := l_trolin_rec.from_locator_id;
l_mo_line_detail_rec.transaction_quantity := p_transaction_quantity;
l_mo_line_detail_rec.primary_quantity := p_transaction_quantity;
l_mo_line_detail_rec.transaction_uom := l_trolin_rec.uom_code;
l_mo_line_detail_rec.transaction_cost := NULL;
l_mo_line_detail_rec.transaction_type_id := l_trolin_rec.transaction_type_id;
l_mo_line_detail_rec.transaction_action_id := get_transaction_action_id(l_trolin_rec.transaction_type_id);
l_mo_line_detail_rec.transaction_source_type_id := l_trolin_rec.transaction_source_type_id;
l_mo_line_detail_rec.transaction_source_id := p_header_id;
l_mo_line_detail_rec.transaction_source_name := NULL;
l_mo_line_detail_rec.transaction_date := p_transaction_date;
l_mo_line_detail_rec.acct_period_id := get_acct_period_id(l_trolin_rec.organization_id,
p_transaction_date); --账户期间id
l_mo_line_detail_rec.distribution_account_id := NULL;
l_mo_line_detail_rec.transaction_reference := NULL;
l_mo_line_detail_rec.requisition_line_id := NULL;
l_mo_line_detail_rec.requisition_distribution_id := NULL;
l_mo_line_detail_rec.reason_id := l_trolin_rec.reason_id;
l_mo_line_detail_rec.lot_number := l_trolin_rec.lot_number;
l_mo_line_detail_rec.lot_expiration_date := NULL;
l_mo_line_detail_rec.serial_number := NULL;
l_mo_line_detail_rec.receiving_document := NULL;
l_mo_line_detail_rec.demand_id := NULL;
l_mo_line_detail_rec.rcv_transaction_id := NULL;
l_mo_line_detail_rec.move_transaction_id := NULL;
l_mo_line_detail_rec.completion_transaction_id := NULL;
l_mo_line_detail_rec.wip_entity_type := NULL;
l_mo_line_detail_rec.schedule_id := NULL;
l_mo_line_detail_rec.repetitive_line_id := NULL;
l_mo_line_detail_rec.employee_code := NULL;
l_mo_line_detail_rec.primary_switch := NULL;
l_mo_line_detail_rec.schedule_update_code := NULL;
l_mo_line_detail_rec.setup_teardown_code := NULL;
l_mo_line_detail_rec.item_ordering := NULL;
l_mo_line_detail_rec.negative_req_flag := NULL;
l_mo_line_detail_rec.operation_seq_num := NULL;
l_mo_line_detail_rec.picking_line_id := NULL;
l_mo_line_detail_rec.trx_source_line_id := p_line_id;
l_mo_line_detail_rec.trx_source_delivery_id := NULL;
l_mo_line_detail_rec.physical_adjustment_id := NULL;
l_mo_line_detail_rec.cycle_count_id := NULL;
l_mo_line_detail_rec.rma_line_id := NULL;
l_mo_line_detail_rec.customer_ship_id := NULL;
l_mo_line_detail_rec.currency_code := NULL;
l_mo_line_detail_rec.currency_conversion_rate := NULL;
l_mo_line_detail_rec.currency_conversion_type := NULL;
l_mo_line_detail_rec.currency_conversion_date := NULL;
l_mo_line_detail_rec.ussgl_transaction_code := NULL;
l_mo_line_detail_rec.vendor_lot_number := NULL;
l_mo_line_detail_rec.encumbrance_account := NULL;
l_mo_line_detail_rec.encumbrance_amount := NULL;
l_mo_line_detail_rec.ship_to_location := NULL;
l_mo_line_detail_rec.shipment_number := NULL;
l_mo_line_detail_rec.transfer_cost := NULL;
l_mo_line_detail_rec.transportation_cost := NULL;
l_mo_line_detail_rec.transportation_account := NULL;
l_mo_line_detail_rec.freight_code := NULL;
l_mo_line_detail_rec.containers := NULL;
l_mo_line_detail_rec.waybill_airbill := NULL;
l_mo_line_detail_rec.expected_arrival_date := NULL;
l_mo_line_detail_rec.transfer_subinventory := l_trolin_rec.to_subinventory_code;
l_mo_line_detail_rec.transfer_organization := NULL;
l_mo_line_detail_rec.new_average_cost := NULL;
l_mo_line_detail_rec.value_change := NULL;
l_mo_line_detail_rec.percentage_change := NULL;
l_mo_line_detail_rec.material_allocation_temp_id := NULL;
l_mo_line_detail_rec.demand_source_header_id := p_header_id;
l_mo_line_detail_rec.demand_source_line := to_char(p_line_id);
l_mo_line_detail_rec.demand_source_delivery := NULL;
l_mo_line_detail_rec.item_segments := NULL;
l_mo_line_detail_rec.item_description := NULL;
l_mo_line_detail_rec.item_trx_enabled_flag := NULL;
l_mo_line_detail_rec.item_location_control_code := NULL;
l_mo_line_detail_rec.item_restrict_subinv_code := NULL;
l_mo_line_detail_rec.item_restrict_locators_code := NULL;
l_mo_line_detail_rec.item_revision_qty_control_code := NULL;
l_mo_line_detail_rec.item_primary_uom_code := NULL;
l_mo_line_detail_rec.item_uom_class := NULL;
l_mo_line_detail_rec.item_shelf_life_code := NULL;
l_mo_line_detail_rec.item_shelf_life_days := NULL;
l_mo_line_detail_rec.item_lot_control_code := NULL;
l_mo_line_detail_rec.item_serial_control_code := NULL;
l_mo_line_detail_rec.item_inventory_asset_flag := NULL;
l_mo_line_detail_rec.allowed_units_lookup_code := NULL;
l_mo_line_detail_rec.department_id := NULL;
l_mo_line_detail_rec.department_code := NULL;
l_mo_line_detail_rec.wip_supply_type := NULL;
l_mo_line_detail_rec.supply_subinventory := NULL;
l_mo_line_detail_rec.supply_locator_id := NULL;
l_mo_line_detail_rec.valid_subinventory_flag := NULL;
l_mo_line_detail_rec.valid_locator_flag := NULL;
l_mo_line_detail_rec.locator_segments := NULL;
l_mo_line_detail_rec.current_locator_control_code := NULL;
l_mo_line_detail_rec.number_of_lots_entered := NULL;
l_mo_line_detail_rec.wip_commit_flag := NULL;
l_mo_line_detail_rec.next_lot_number := NULL;
l_mo_line_detail_rec.lot_alpha_prefix := NULL;
l_mo_line_detail_rec.next_serial_number := NULL;
l_mo_line_detail_rec.serial_alpha_prefix := NULL;
l_mo_line_detail_rec.shippable_flag := NULL;
l_mo_line_detail_rec.posting_flag := 'Y';
l_mo_line_detail_rec.required_flag := NULL;
l_mo_line_detail_rec.process_flag := 'Y';
l_mo_line_detail_rec.error_code := NULL;
l_mo_line_detail_rec.error_explanation := NULL;
l_mo_line_detail_rec.attribute_category := NULL;
l_mo_line_detail_rec.attribute1 := NULL;
l_mo_line_detail_rec.attribute2 := NULL;
l_mo_line_detail_rec.attribute3 := NULL;
l_mo_line_detail_rec.attribute4 := NULL;
l_mo_line_detail_rec.attribute5 := NULL;
l_mo_line_detail_rec.attribute6 := NULL;
l_mo_line_detail_rec.attribute7 := NULL;
l_mo_line_detail_rec.attribute8 := NULL;
l_mo_line_detail_rec.attribute9 := NULL;
l_mo_line_detail_rec.attribute10 := NULL;
l_mo_line_detail_rec.attribute11 := NULL;
l_mo_line_detail_rec.attribute12 := NULL;
l_mo_line_detail_rec.attribute13 := NULL;
l_mo_line_detail_rec.attribute14 := NULL;
l_mo_line_detail_rec.attribute15 := NULL;
l_mo_line_detail_rec.movement_id := NULL;
l_mo_line_detail_rec.reservation_quantity := NULL;
l_mo_line_detail_rec.shipped_quantity := NULL;
l_mo_line_detail_rec.transaction_line_number := NULL;
l_mo_line_detail_rec.task_id := NULL;
l_mo_line_detail_rec.to_task_id := NULL;
l_mo_line_detail_rec.source_task_id := NULL;
l_mo_line_detail_rec.project_id := NULL;
l_mo_line_detail_rec.source_project_id := NULL;
l_mo_line_detail_rec.pa_expenditure_org_id := NULL;
l_mo_line_detail_rec.to_project_id := NULL;
l_mo_line_detail_rec.expenditure_type := NULL;
l_mo_line_detail_rec.final_completion_flag := 'N';
l_mo_line_detail_rec.transfer_percentage := NULL;
l_mo_line_detail_rec.transaction_sequence_id := NULL;
l_mo_line_detail_rec.material_account := NULL;
l_mo_line_detail_rec.material_overhead_account := NULL;
l_mo_line_detail_rec.resource_account := NULL;
l_mo_line_detail_rec.outside_processing_account := NULL;
l_mo_line_detail_rec.overhead_account := NULL;
l_mo_line_detail_rec.flow_schedule := NULL;
l_mo_line_detail_rec.cost_group_id := NULL;
l_mo_line_detail_rec.demand_class := NULL;
l_mo_line_detail_rec.qa_collection_id := NULL;
l_mo_line_detail_rec.kanban_card_id := NULL;
l_mo_line_detail_rec.overcompletion_transaction_id := NULL;
l_mo_line_detail_rec.overcompletion_primary_qty := NULL;
l_mo_line_detail_rec.overcompletion_transaction_qty := NULL;
l_mo_line_detail_rec.end_item_unit_number := NULL;
l_mo_line_detail_rec.scheduled_payback_date := NULL;
l_mo_line_detail_rec.line_type_code := NULL;
l_mo_line_detail_rec.parent_transaction_temp_id := NULL;
l_mo_line_detail_rec.put_away_strategy_id := NULL;
l_mo_line_detail_rec.put_away_rule_id := NULL;
l_mo_line_detail_rec.pick_strategy_id := l_trolin_rec.pick_strategy_id;
l_mo_line_detail_rec.pick_rule_id := NULL;
l_mo_line_detail_rec.common_bom_seq_id := NULL;
l_mo_line_detail_rec.common_routing_seq_id := NULL;
l_mo_line_detail_rec.cost_type_id := NULL;
l_mo_line_detail_rec.org_cost_group_id := NULL;
l_mo_line_detail_rec.move_order_line_id := p_line_id;
l_mo_line_detail_rec.task_group_id := NULL;
l_mo_line_detail_rec.pick_slip_number := l_trolin_rec.pick_slip_number;
l_mo_line_detail_rec.reservation_id := NULL;
l_mo_line_detail_rec.transaction_status := 2; -- --Indicates if it has to be processed or just saved. NULL or 1 is default behavior. 2 is save only. 3 is ready to process
l_mo_line_detail_rec.transfer_cost_group_id := NULL;
l_mo_line_detail_rec.lpn_id := NULL;
l_mo_line_detail_rec.transfer_lpn_id := NULL;
l_mo_line_detail_rec.pick_slip_date := l_trolin_rec.pick_slip_date;
l_mo_line_detail_rec.content_lpn_id := NULL;
l_mo_line_detail_rec.secondary_transaction_quantity := NULL;
l_mo_line_detail_rec.secondary_uom_code := NULL;
--调用标准的API对MMTT进行插入
inv_mo_line_detail_util.insert_row(x_return_status => x_return_status,
p_mo_line_detail_rec => l_mo_line_detail_rec);
IF l_mo_debug_falg = 'Y' THEN
--输出调试信息
put_log('x_return_status:' || x_return_status);
END IF;
IF x_return_status = fnd_api.G_RET_STS_SUCCESS THEN
x_transaction_temp_id := l_transaction_temp_id; --回传mmtt的唯一性标识
END IF;
EXCEPTION
WHEN OTHERS THEN
put_log('插入mmtt处理行时出现异常:' || SQLERRM);
END insert_mmtt_row;
/*=======================================================
* FUNCTION / PROCEDURE
* delete_mmtt_row
* DESCRIPTION:
* 删除某一物料搬运单事务处理临时表的一条记录
* ARGUMENT:
* p_header_id:物料搬运单头id
* p_line_id :物料搬运单行id
* RETURN:
* N/A
* HISTORY:
* 1.00 27/10/2010 cxy
=========================================================*/
PROCEDURE delete_mmtt_row(x_return_status OUT VARCHAR2,
p_line_id IN NUMBER,
p_transaction_temp_id IN NUMBER) IS
BEGIN
inv_mo_line_detail_util.delete_row(x_return_status => x_return_status,
p_line_id => p_line_id,
p_line_detail_id => p_transaction_temp_id);
EXCEPTION
WHEN OTHERS THEN
put_log('删除某一物料搬运单的处理行时出现异常:' || SQLERRM);
END delete_mmtt_row;
/*=======================================================
* FUNCTION / PROCEDURE
* process_data
* DESCRIPTION:
* 自动处理物料搬运单
* ARGUMENT:
* x_return_status :返回状态
* x_msg_count :返回的行数
* x_msg_data :返回的信息
* p_line_id :物料搬运单行id
* RETURN:
* N/A
* HISTORY:
* 1.00 27/10/2010 cxy
=========================================================*/
PROCEDURE process_data(x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_line_id IN NUMBER) IS
x_number_of_rows NUMBER;
x_detailed_qty NUMBER;
x_revision VARCHAR2(3);
x_locator_id NUMBER;
x_transfer_to_location NUMBER;
x_lot_number VARCHAR2(80);
x_expiration_date DATE;
x_transaction_temp_id NUMBER;
l_trolin_tbl INV_MOVE_ORDER_PUB.Trolin_Tbl_Type;
l_mold_tbl INV_MO_LINE_DETAIL_UTIL.g_mmtt_tbl_type;
x_mmtt_tbl INV_MO_LINE_DETAIL_UTIL.g_mmtt_tbl_type;
x_trolin_tbl INV_Move_Order_PUB.Trolin_Tbl_Type;
l_mo_line_detail_rec inv_mo_line_detail_util.g_mmtt_rec;
BEGIN
IF l_mo_debug_falg = 'Y' THEN
--输出调试信息
put_log('--------创建物料搬运单分配行开始----------');
END IF;
INV_Replenish_Detail_PUB.line_details_pub(p_line_id => p_line_id,
x_number_of_rows => x_number_of_rows,
x_detailed_qty => x_detailed_qty,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_revision => x_revision,
x_locator_id => x_locator_id,
x_transfer_to_location => x_transfer_to_location,
x_lot_number => x_lot_number,
x_expiration_date => x_expiration_date,
x_transaction_temp_id => x_transaction_temp_id,
p_transaction_header_id => NULL,
p_transaction_mode => NULL,
p_move_order_type => 1,
p_serial_flag => FND_API.G_TRUE,
p_plan_tasks => NULL,
p_auto_pick_confirm => NULL,
p_commit => NULL);
IF l_mo_debug_falg = 'Y' THEN
--输出调试信息
put_log('x_number_of_rows:' || x_number_of_rows);
put_log('x_return_status:' || x_return_status);
put_log('--------创建物料搬运单分配行结束----------');
END IF;
IF x_number_of_rows > 0 AND x_return_status = fnd_api.G_RET_STS_SUCCESS THEN
--得到物料搬运单行信息
IF l_mo_debug_falg = 'Y' THEN --输出调试信息
put_log('--------获得物料搬运单行信息开始----------');
END IF;
l_trolin_tbl := inv_trolin_util.query_rows(p_line_id => p_line_id);
IF l_mo_debug_falg = 'Y' THEN--输出调试信息
put_log('--------获得物料搬运单行信息结束----------');
put_log('--------自动挑库确认开始----------');
END IF;
--自动挑库确认
INV_PICK_WAVE_PICK_CONFIRM_PUB.Pick_Confirm(p_api_version_number => 1.0,
p_init_msg_list => fnd_api.G_FALSE,
p_commit => fnd_api.G_FALSE,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_move_order_type => 1,
p_transaction_mode => 1,
p_trolin_tbl => l_trolin_tbl,
p_mold_tbl => l_mold_tbl,
x_mmtt_tbl => x_mmtt_tbl,
x_trolin_tbl => x_trolin_tbl,
p_transaction_date => NULL);
IF l_mo_debug_falg = 'Y' THEN--输出调试信息
put_log('x_return_status:' || x_return_status);
put_log('x_msg_count:' || x_msg_count);
put_log('x_msg_data:' || x_msg_data);
put_log('--------自动挑库确认结束----------');
END IF;
END IF;
EXCEPTION
WHEN OTHERS THEN
put_log('自动处理物料搬运单时出错:' || SQLERRM);
END process_data;
/*=======================================================
* FUNCTION / PROCEDURE
* process_history_data
* DESCRIPTION:
* 处理上一次错误的历史记录
* ARGUMENT:
* x_processd_flag:上一次的错误记录是否已经被处理
* p_line_id :物料搬运单行id
* RETURN:
* N/A
* HISTORY:
* 1.00 27/10/2010 cxy
=========================================================*/
PROCEDURE process_history_data(x_processd_flag OUT VARCHAR2,
p_line_id IN NUMBER) IS
CURSOR cur_processed_order_info IS
SELECT mtrl.header_id header_id, --物料搬运单头id,
mtrh.request_number request_number, --物料搬运单编号
mtrl.line_id line_id, --物料搬运单行id
mtrl.line_number line_number, --行号
mtrl.organization_id organization_id, --库存组织ID
mtrl.inventory_item_id inventory_item_id, --物料ID
mtrh.transaction_type_id transaction_type_id, --事务处理类型ID
mtt.transaction_type_name transaction_type_name, --事务处理类型名称
mlt.lookup_code line_status, --行状态
mlt.meaning line_status_meaning --行状态含义
FROM mtl_txn_request_lines mtrl,
mtl_txn_request_headers mtrh,
mtl_transaction_types mtt,
mfg_lookups mlt
WHERE mtrl.header_id = mtrh.header_id
AND mtrl.transaction_type_id = mtt.transaction_type_id
AND mtrl.line_status = mlt.lookup_code
AND mlt.lookup_type = 'MTL_TXN_REQUEST_STATUS'
AND mtrl.line_status = 5 --表示已经关闭,表示上次错误的记录已经成功处理
AND mtt.transaction_action_id = 2 --事务处理的活动为子库存转移的记录
AND mtrl.line_id = p_line_id;
l_processed_count NUMBER := 0;
v_processd_flag VARCHAR2(1); --上次错误的记录是否已经被处理
BEGIN
x_processd_flag := 'N'; --默认为上次的记录没有被处理
FOR rec_processed_order_info IN cur_processed_order_info LOOP
l_processed_count := l_processed_count + 1;
IF l_processed_count <> 0 THEN
v_processd_flag := 'Y';
EXIT;
END IF;
END LOOP;
IF l_processed_count = 0 THEN
v_processd_flag := 'N';
END IF;
IF v_processd_flag = 'Y' THEN
x_processd_flag := 'Y';
--如果上一次的错误记录已经被处理,则更新客户化表中的记录的状态
UPDATE cux_ediint_item_move_in ceimi
SET ceimi.process_id = 4, --将3改成4,表示此条记录已经被处理，将这条记录的状态改成已经处理
ceimi.error_message = NULL, --错误信息修改成空
ceimi.attribute15 = '上一次处理出错,已经通过系统手工或者其他方式进行处理!',
ceimi.last_update_date = SYSDATE,
ceimi.last_updated_by = fnd_global.user_id,
ceimi.last_update_login = fnd_global.user_id
WHERE ceimi.line_id = p_line_id
AND ceimi.process_id IN (1, 3); --错误的状态
END IF;
EXCEPTION
WHEN OTHERS THEN
put_log('删除某一物料搬运单的处理行时出现异常:' || SQLERRM);
END process_history_data;
/*=======================================================
* FUNCTION / PROCEDURE
* main
* DESCRIPTION:
* 并发程序入口
* ARGUMENT:
* errbuf :并发程序的系统参数，返回错误信息
* retcode :并发程序的系统参数, 执行状态
* RETURN:
* N/A
* HISTORY:
* 1.00 27/10/2010 cxy
=========================================================*/
/*================================================================
* 主程序的步骤：
* 10 初始化
* 20 处理历史记录
* 30 验证数据有效性
* 40 在MMTT表中插入一条记录
* 50 自动处理物料搬运单
* 60 更新客户化表的状态和错误信息
* 70 输出处理结果
=================================================================*/
PROCEDURE main(errbuf OUT NOCOPY VARCHAR2, retcode OUT NOCOPY VARCHAR2) IS
--使用cursor取出客户化事务处理表中的所有物料搬运单头信息,不取重复的记录
CURSOR cur_item_move_imp_header IS
SELECT DISTINCT ceimi.header_id header_id, --物料搬运单头id
ceimi.request_number request_number --物料搬运单编号
FROM cux_ediint_item_move_in ceimi
WHERE ceimi.process_id IN (0, 3); ----处理新增或者上次出错的记录,0表示没有处理的记录,3表示上一次处理出错的记录
--使用cursor 取出客户化表中的物料搬运单行信息
CURSOR cur_item_move_imp_line(p_header_id NUMBER) IS
SELECT ceimi.header_id header_id, --物料搬运单头id
ceimi.request_number request_number, --物料搬运单编号
ceimi.line_id line_id, --物料搬运单行id
ceimi.transaction_quantity transaction_quantity, --事务处理数量
ceimi.transaction_date transaction_date --事务处理日期
FROM cux_ediint_item_move_in ceimi
WHERE ceimi.process_id IN (0, 3) --处理新增或者上次出错的记录,0表示没有处理的记录,3表示上一次处理出错的记录
AND ceimi.header_id = p_header_id
ORDER BY ceimi.line_id;
x_return_status VARCHAR2(10); --处理状态
x_msg_count NUMBER; --错误信息数量
x_msg_data VARCHAR2(2000); --错误信息
x_trohdr_rec INV_MOVE_ORDER_PUB.Trohdr_Rec_Type; --物料搬运单头记录
x_trohdr_val_rec INV_MOVE_ORDER_PUB.Trohdr_Val_Rec_Type; --物料搬运单头对应的记录
x_trolin_tbl INV_MOVE_ORDER_PUB.Trolin_Tbl_Type; --物料搬运单的所有行记录
x_trolin_val_tbl INV_MOVE_ORDER_PUB.Trolin_Val_Tbl_Type; --物料搬运单的所有行对应的记录
x_trolin_rec INV_MOVE_ORDER_PUB.Trolin_Rec_Type; --一条物料搬运单行
l_line_count NUMBER := 0; --处理的总行数
l_line_success_count NUMBER := 0; --处理成功的行数
x_transaction_temp_id NUMBER; --取得mmtt表的唯一性标识
x_processd_flag VARCHAR2(1); --上一次的历史记录是否被手工处理
BEGIN
--循环所有的物料搬运单
FOR rec_item_move_imp_header IN cur_item_move_imp_header LOOP
/*============================================================
* 10 初始化
* ------------------------------------------------------------
* 描述：初始化变量,并得到物料搬运单的相关信息
=============================================================*/
x_trohdr_rec := NULL; --物料搬运单头记录
x_trohdr_val_rec := NULL;
--得到物料搬运单信息
IF l_mo_debug_falg = 'Y' THEN--输出调试信息
put_log('---------------获取物料搬运单信息开始---------------------------');
END IF;
x_return_status := NULL; --处理状态
get_move_order(x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_trohdr_rec => x_trohdr_rec,
x_trohdr_val_rec => x_trohdr_val_rec,
x_trolin_tbl => x_trolin_tbl,
x_trolin_val_tbl => x_trolin_val_tbl,
p_header_id => rec_item_move_imp_header.header_id);
IF l_mo_debug_falg = 'Y' THEN--输出调试信息
put_log('---------------获取物料搬运单行状态：x_return_status:' ||
x_return_status);
put_log('---------------错误信息条数：x_msg_count:' || x_msg_count);
put_log('---------------错误信息：x_msg_data:' || x_msg_data);
put_log('---------------获取物料搬运单信息结束---------------------------');
END IF;
--物料搬运单头状态正常,则处理此物料搬运单的所有行记录
IF x_return_status = fnd_api.G_RET_STS_SUCCESS THEN
--循环物料搬运单的行,自动处理物料搬运单按照行进行处理
FOR rec_item_move_imp_line IN cur_item_move_imp_line(rec_item_move_imp_header.header_id) LOOP
--进行初始化
x_return_status := NULL; --处理状态
x_msg_count := NULL; --错误信息条数
x_msg_data := NULL; --错误信息
--为此执行块创建一个SAVEPOINT
BEGIN
--创建SAVEPOINT cux_item_move_line
SAVEPOINT cux_item_move_line;
UPDATE cux_ediint_item_move_in ceimi
SET ceimi.process_id = 1, --1表示程序正在处理
ceimi.request_id = fnd_global.conc_request_id,
ceimi.last_update_date = SYSDATE,
ceimi.last_updated_by = fnd_global.user_id,
ceimi.last_update_login = fnd_global.user_id
WHERE ceimi.line_id = rec_item_move_imp_line.line_id;
/*=========================================================
* 20 处理历史记录
* ---------------------------------------------------------
* 描述：检测历史数据是否已经被处理，
* 并更新客户表的状态和错误信息
==========================================================*/
process_history_data(x_processd_flag => x_processd_flag,
p_line_id => rec_item_move_imp_line.line_id);
IF x_processd_flag = 'N' THEN
--等于‘N'表示上次处理错误的记录没有被手工处理
--得到某一物料搬运单行的信息
get_trolin_rec(x_return_status => x_return_status,
x_trolin_rec => x_trolin_rec,
p_trolin_tbl => x_trolin_tbl,
p_line_id => rec_item_move_imp_line.line_id);
IF l_mo_debug_falg = 'Y' THEN--输出调试信息
put_log('得到某一物料搬运单行的状态:' || x_return_status);
END IF;
IF x_return_status = fnd_api.G_RET_STS_SUCCESS THEN
l_line_count := l_line_count + 1; --此次处理的总条数
/*==========================================================
* 30 验证数据有效性
* ----------------------------------------------------------
* 描述：验证客户化表中的数据有效性，验证接口表中的数量是否
* 等于物料搬运单的数量。并验证库存现用量和可用量。
============================================================*/
IF l_mo_debug_falg = 'Y' THEN--输出调试信息
put_log('----------------开始验证---------------------------');
END IF;
--验证数据的有效性,包括是否存在货位，物料可用量等信息
validate_data(x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_trolin_rec => x_trolin_rec,
p_line_id => rec_item_move_imp_line.line_id,
p_transaction_quantity => rec_item_move_imp_line.transaction_quantity,
p_transaction_date => rec_item_move_imp_line.transaction_date);
IF x_return_status <> fnd_api.G_RET_STS_SUCCESS THEN
put_log('错误信息为:' || x_msg_data);
--如果未通过验证,则结束本次循环
GOTO line_end;
END IF;
IF l_mo_debug_falg = 'Y' THEN --输出调试信息
put_log('---------------验证结束---------------------------');
END IF;
/*=============================================================
* 40 在MMTT表中插入一条记录
* -------------------------------------------------------------
* 描述：数据验证通过后，给MMTT表中的字段赋值，并
* 在MMTT表中插入一条记录。
===============================================================*/
IF l_mo_debug_falg = 'Y' THEN--输出调试信息
put_log('---------------插入临时表MMTT表的一条记录开始---------------------------');
END IF;
--插入一条MMTT的记录
insert_mmtt_row(x_return_status => x_return_status,
x_transaction_temp_id => x_transaction_temp_id,
p_trohdr_rec => x_trohdr_rec,
p_trohdr_val_rec => x_trohdr_val_rec,
p_trolin_tbl => x_trolin_tbl,
p_trolin_val_tbl => x_trolin_val_tbl,
p_header_id => rec_item_move_imp_line.header_id,
p_line_id => rec_item_move_imp_line.line_id,
p_transaction_quantity => rec_item_move_imp_line.transaction_quantity,
p_transaction_date => rec_item_move_imp_line.transaction_date);
IF l_mo_debug_falg = 'Y' THEN--输出调试信息
put_log('---------------插入临时表MMTT表的一条记录：x_return_status:' ||
x_return_status);
put_log('---------------插入临时表MMTT表的一条记录结束---------------------------');
END IF;
/*===============================================================
* 50 自动处理物料搬运单
* ---------------------------------------------------------------
* 描述：调用API自动处理物料搬运单
================================================================*/
IF x_return_status = fnd_api.G_RET_STS_SUCCESS THEN
IF l_mo_debug_falg = 'Y' THEN--输出调试信息
put_log('---------------自动处理物料搬运单开始---------------------------');
END IF;
--处理物料搬运单
process_data(x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_line_id => rec_item_move_imp_line.line_id);
IF l_mo_debug_falg = 'Y' THEN--输出调试信息
put_log('---------------x_return_status:' ||
x_return_status);
put_log('---------------x_msg_count:' || x_msg_count);
put_log('---------------x_msg_data:' || x_msg_data);
put_log('---------------自动处理物料搬运单结束---------------------------');
END IF;
ELSE
EXIT;
put_log('错误信息为:' || x_msg_data);
put_log('CUX:物料搬运单事务处理时,发生异常:' || SQLERRM);
END IF;
--本次循环结束标志
<<line_end>>
/*========================================================================
* 60 更新客户化表的状态和错误信息
* ------------------------------------------------------------------------
* 描述：更新客户化表的状态：
* process_flag =0,表示为新的记录,需要进行处理。
* process_flag =1,表示正在处理
* process_flag =3,程序处理异常或者错误,并将错误信息赋值给error_message
* process_flag =4,表示处理成功。
========================================================================*/
IF x_return_status = fnd_api.G_RET_STS_SUCCESS THEN --处理成功
l_line_success_count := l_line_success_count + 1; --处理成功的记录
--表示处理成功
UPDATE cux_ediint_item_move_in ceimi
SET ceimi.process_id = 4, --表示处理成功
ceimi.error_message = NULL,
ceimi.last_update_date = SYSDATE,
ceimi.last_updated_by = fnd_global.user_id,
ceimi.last_update_login = fnd_global.user_id
WHERE ceimi.line_id = rec_item_move_imp_line.line_id;
END IF;
IF x_return_status = fnd_api.G_RET_STS_UNEXP_ERROR THEN--处理失败
UPDATE cux_ediint_item_move_in ceimi
SET ceimi.process_id = 3, --程序出现异常
ceimi.error_message = TRIM(x_msg_data), --错误信息
ceimi.last_update_date = SYSDATE,
ceimi.last_updated_by = fnd_global.user_id,
ceimi.last_update_login = fnd_global.user_id
WHERE ceimi.line_id = rec_item_move_imp_line.line_id;
--删除MMTT表中未被成功处理的记录
delete_mmtt_row(x_return_status => x_return_status,
p_line_id => rec_item_move_imp_line.line_id,
p_transaction_temp_id => x_transaction_temp_id);
END IF;
END IF;
ELSE--上次处理错误的记录被手工处理
l_line_count := l_line_count + 1; --此次处理的总条数
l_line_success_count := l_line_success_count + 1; --处理成功的记录
END IF;
--一条物料搬运单行处理结束,进行事务处理的提交
COMMIT;
EXCEPTION --物料搬运行出现异常
WHEN OTHERS THEN
--如果此条记录出现异常,则回滚到SAVEPOINT cux_item_move_line
ROLLBACK TO cux_item_move_line;
put_log('处理物料搬运单行时,发生异常:' || SQLERRM);
END; --end begin
END LOOP; --FOR rec_item_move_imp_line IN cur_item_move_imp_line LOOP
ELSE
retcode := 1; --如果存在没有成功处理的物料搬运单，则显示警告
put_log('得到物料搬运单头时发生异常:' || x_msg_data);
END IF;
END LOOP; --FOR rec_item_move_imp_header IN cur_item_move_imp_header LOOP
--输出处理的信息(包括成功的信息和错误的信息)
IF l_mo_debug_falg = 'Y' THEN--输出调试信息
put_log('总行数:' || l_line_count);
put_log('处理成功的总行数:' || l_line_success_count);
END IF;
/*========================================================================
* 70 输出处理结果
* ------------------------------------------------------------------------
* 描述：处理物料搬运单的处理结果包括：
* 1.处理错误的记录
* 2.处理失败的记录及其错误信息
========================================================================*/
output_move_order_info(p_line_count => l_line_count,
p_line_success_count => l_line_success_count);
IF (l_line_count <> 0) AND (l_line_count <> l_line_success_count) THEN--如果存在处理错误的信息,则请求显示警告
retcode := 1; --如果存在没有成功处理的物料搬运单，则显示警告
END IF;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
retcode := 2;
put_log('错误信息为:' || x_msg_data);
put_log('CUX:物料搬运单事务处理时,发生异常:' || SQLERRM);
END main;
END CUX_EDIINT_ITEM_MOVE_IN_PKG;

CREATE OR REPLACE PACKAGE BODY CUX_EDIINT_ITEM_MOVE_IN_PKG IS --自定义预置文件CUX_MO_DEBUG_FLAG：用来判断是否输出调试信息 --fnd_profile.VALUE('CUX_MO_DEBUG_FLAG')为NULL,不输出调试信息,输出错误信息 --fnd_profile.VALUE('CUX_MO_DEBUG_FLAG')为'N',不输出调试信息,输出错误信息 --fnd_profile.VALUE('CUX_MO_DEBUG_FLAG')为'Y', 输出调试信息,输出错误信息 l_mo_debug_falg VARCHAR2(1) := NVL(fnd_profile.VALUE('CUX_MO_DEBUG_FLAG'), 'N'); --日志 PROCEDURE put_log(p_msg IN VARCHAR2) IS BEGIN fnd_file.put_line(fnd_file.log, p_msg); END put_log; --输出 PROCEDURE put_file(p_msg IN VARCHAR2) IS BEGIN fnd_file.put_line(fnd_file.OUTPUT, p_msg); END put_file; /*======================================================= * FUNCTION / PROCEDURE * get_org_count * DESCRIPTION: * 得到本次请求处理(成功或者失败)的物料搬运单的所有OU 数量,0表示不存在，1表示存在。 * ARGUMENT: * p_org_id :OU id * p_process_id :物料搬运单处理状态 * RETURN: * N/A * HISTORY: * 1.00 27/10/2010 cxy =========================================================*/ FUNCTION get_org_count(p_org_id NUMBER, p_process_id NUMBER) RETURN NUMBER IS l_org_count NUMBER; BEGIN SELECT COUNT(1) INTO l_org_count FROM cux_ediint_item_move_in ceimi WHERE ceimi.ou_org_id = p_org_id AND ceimi.process_id = p_process_id AND ceimi.request_id = fnd_global.conc_request_id AND ROWNUM = 1; RETURN l_org_count; EXCEPTION WHEN OTHERS THEN put_log('得到OU关联的处理条数时候出现错误:' || SQLERRM); RETURN NULL; END get_org_count; /*======================================================= * FUNCTION / PROCEDURE * output_move_order_info * DESCRIPTION: * 输出处理信息 * ARGUMENT: * p_line_count ：本次请求处理的记录总数量 * p_line_seccess_count : 本次请求处理成功的记录数量 * RETURN: * N/A * HISTORY: * 1.00 27/10/2010 cxy =========================================================*/ PROCEDURE output_move_order_info(p_line_count IN NUMBER, p_line_success_count IN NUMBER) IS --取出物料搬运单客户化表中的业务实体的信息,不取重复的记录 CURSOR cur_org_info IS SELECT DISTINCT hou.organization_id org_id, --库存组织id hou.name org_name --库存组织名称 FROM cux_ediint_item_move_in ceimi, mtl_txn_request_lines mtrl, mtl_txn_request_headers mtrh, org_organization_definitions ood, hr_operating_units hou WHERE ceimi.line_id = mtrl.line_id AND mtrl.header_id = mtrh.header_id AND ood.organization_id = mtrl.organization_id AND ood.operating_unit = hou.organization_id AND ceimi.request_id = fnd_global.conc_request_id; --处理成功的物料搬运单行信息 CURSOR cur_move_order_success(p_org_id NUMBER) IS SELECT hou.name org_name, --OU名称 ceimi.header_id header_id, --物料搬运单头id mtrh.request_number request_number, --物料搬运单编号 ml.meaning move_order_type_meaning, --物料搬运单类型 ceimi.line_id line_id, --物料搬运单行id mtrl.line_number line_number, --行编号 msib.segment1 item_code, --物料编码 ceimi.transaction_quantity transaction_quantity, --事务处理数量 ood.organization_code organization_code, --库存组织代码 ood.organization_name organization_name, --库存组织名称 mtrl.from_subinventory_code from_subinventory_code, --来源子库存 mtrl.to_subinventory_code to_subinventory_code --目标子库存 FROM cux_ediint_item_move_in ceimi, mtl_txn_request_lines mtrl, mtl_txn_request_headers mtrh, org_organization_definitions ood, hr_operating_units hou, mfg_lookups ml, mtl_system_items_b msib WHERE ceimi.line_id = mtrl.line_id AND mtrl.header_id = mtrh.header_id AND ood.organization_id = mtrl.organization_id AND ood.operating_unit = hou.organization_id AND mtrh.move_order_type = ml.lookup_code AND ml.lookup_type = 'MOVE_ORDER_TYPE' AND msib.organization_id = mtrl.organization_id AND msib.inventory_item_id = mtrl.inventory_item_id AND ceimi.process_id = 4 --处理成功的记录 AND ceimi.request_id = fnd_global.conc_request_id AND hou.organization_id = p_org_id; --出错的物料搬运单行信息 CURSOR cur_move_order_error(p_org_id NUMBER) IS SELECT hou.name org_name, --OU名称 ceimi.header_id header_id, --物料搬运单头id mtrh.request_number request_number, --物料搬运单编号 ml.meaning move_order_type_meaning,--物料搬运单类型 ceimi.line_id line_id, --物料搬运单行id mtrl.line_number line_number, --行编号 msib.segment1 item_code, --物料编码 ceimi.transaction_quantity transaction_quantity, --事务处理数量 ood.organization_code organization_code, --库存组织代码 ood.organization_name organization_name, --库存组织名称 mtrl.from_subinventory_code from_subinventory_code,--来源子库存 mtrl.to_subinventory_code to_subinventory_code, --目标子库存 ceimi.error_message error_message --错误信息 FROM cux_ediint_item_move_in ceimi, mtl_txn_request_lines mtrl, mtl_txn_request_headers mtrh, org_organization_definitions ood, hr_operating_units hou, mfg_lookups ml, mtl_system_items_b msib WHERE ceimi.line_id = mtrl.line_id AND mtrl.header_id = mtrh.header_id AND ood.organization_id = mtrl.organization_id AND ood.operating_unit = hou.organization_id AND mtrh.move_order_type = ml.lookup_code AND ml.lookup_type = 'MOVE_ORDER_TYPE' AND msib.organization_id = mtrl.organization_id AND msib.inventory_item_id = mtrl.inventory_item_id AND ceimi.process_id = 3 --处理失败的记录 AND ceimi.request_id = fnd_global.conc_request_id AND hou.organization_id = p_org_id; l_user_name VARCHAR2(100); --用户名 l_success_index NUMBER; --循环标志 l_error_index NUMBER; --循环标志 l_count NUMBER; --循环标志 l_org_count NUMBER; --循环标志 BEGIN SELECT fu.user_name INTO l_user_name FROM fnd_user fu WHERE fu.user_id = NVL(fnd_global.user_id, -1); put_file(chr(13) || chr(10)); put_file('提交人:' || rpad(l_user_name, 20, ' ')); put_file('报表日期:' || TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS')); put_file(chr(13) || chr(10)); put_file('本次处理的总的记录条数为: ' || to_char(p_line_count) || ' 条'); put_file('处理成功的记录条数为: ' || to_char(p_line_success_count) || ' 条'); IF (p_line_count > p_line_success_count) THEN put_file('处理出错的记录条数为: ' || to_char(p_line_count - p_line_success_count) || ' 条'); END IF; put_file(chr(13) || chr(10)); IF p_line_success_count <> 0 THEN put_file('处理成功的物料搬运单'); put_file(lpad('-', 190, '-')); --输出处理成功的物料搬运单的信息 FOR rec_org_info IN cur_org_info LOOP l_org_count := get_org_count(rec_org_info.org_id, 4); IF l_org_count <> 0 THEN put_file('业务实体:' || rec_org_info.org_name); put_file(rpad('-', 190, '-')); put_file(rpad('物料搬运单编号', 30, ' ') || rpad('类型', 20, ' ') || rpad('行号', 20, ' ') || rpad('物料', 40, ' ') || rpad('来源子库存', 20, ' ') || rpad('目标子库存', 20, ' ') || rpad('事务处理数量', 40, ' ')); put_file(chr(13) || chr(10)); END IF; FOR rec_move_order_succes IN cur_move_order_success(rec_org_info.org_id) LOOP put_file(rpad(rec_move_order_succes.request_number, 30, ' ') || rpad(rec_move_order_succes.move_order_type_meaning, 20, ' ') || rpad(rec_move_order_succes.line_number, 20, ' ') || rpad(rec_move_order_succes.item_code, 40, ' ') || rpad(rec_move_order_succes.from_subinventory_code, 20, ' ') || rpad(rec_move_order_succes.to_subinventory_code, 20, ' ') || rpad(rec_move_order_succes.transaction_quantity, 40, ' ')); put_file(chr(13) || chr(10)); END LOOP; --FOR rec_hold_order_info IN cur_hold_order_info LOOP put_file(chr(13) || chr(10)); END LOOP; --FOR rec_org_info IN cur_org_info LOOP put_file(lpad('-', 95, '-') || rpad('-', 95, '-')); END IF; put_file(chr(13) || chr(10)); --输出处理失败的物料搬运单 IF p_line_count - p_line_success_count > 0 THEN put_file('处理出错的物料搬运单'); put_file(lpad('处理出错的', 145, '-') || rpad('物料搬运单', 145, '-')); IF (p_line_count >= p_line_success_count) THEN --如果存在错误的记录 --输出处理成功的物料搬运单的信息 FOR rec_org_info IN cur_org_info LOOP l_org_count := get_org_count(rec_org_info.org_id, 3); IF l_org_count <> 0 THEN put_file('业务实体:' || rec_org_info.org_name); put_file(rpad('-', 290, '-')); put_file(rpad('物料搬运单编号', 30, ' ') || rpad('类型', 20, ' ') || rpad('行号', 20, ' ') || rpad('物料', 40, ' ') || rpad('来源子库存', 20, ' ') || rpad('目标子库存', 20, ' ') || rpad('事务处理数量', 40, ' ') || rpad('错误信息', 100, ' ')); put_file(chr(13) || chr(10)); END IF; FOR rec_move_order_error IN cur_move_order_error(rec_org_info.org_id) LOOP put_file(rpad(rec_move_order_error.request_number, 30, ' ') || rpad(rec_move_order_error.move_order_type_meaning, 20, ' ') || rpad(rec_move_order_error.line_number, 20, ' ') || rpad(rec_move_order_error.item_code, 40, ' ') || rpad(rec_move_order_error.from_subinventory_code, 20, ' ') || rpad(rec_move_order_error.to_subinventory_code, 20, ' ') || rpad(rec_move_order_error.transaction_quantity, 40, ' ') || rpad(rec_move_order_error.error_message, 100, ' ')); put_file(chr(13) || chr(10)); END LOOP; --FOR rec_hold_order_info IN cur_hold_order_info LOOP END LOOP; --FOR rec_org_info IN cur_org_info LOOP END IF; IF p_line_count - p_line_success_count > 0 THEN put_file(lpad('-', 145, '-') || rpad('-', 145, '-')); END IF; END IF; END output_move_order_info; /*======================================================= * FUNCTION / PROCEDURE * get_move_order * DESCRIPTION: * 得到物料搬运单的一条记录(一个物料搬运单头和对应的所有的物表搬运单的行) * ARGUMENT: * x_return_status :状态 * x_msg_count :错误信息数量 * x_msg_data :错误信息 * x_trohdr_rec :物料搬运单头的一行记录 * x_trohdr_val_rec :物料搬运单头的一行记录对应的值 * x_trolin_tbl :一个物料搬运单头对应的所有物料搬运单行 * x_trolin_val_tbl :一个物料搬运单头对应的所有物料搬运单行的值 * p_header_id :物料搬运单头id * RETURN: * N/A * HISTORY: * 1.00 27/10/2010 cxy =========================================================*/ PROCEDURE get_move_order(x_return_status OUT NOCOPY VARCHAR2, x_msg_count OUT NOCOPY NUMBER, x_msg_data OUT NOCOPY VARCHAR2, x_trohdr_rec OUT NOCOPY INV_MOVE_ORDER_PUB.Trohdr_Rec_Type, x_trohdr_val_rec OUT NOCOPY INV_MOVE_ORDER_PUB.Trohdr_Val_Rec_Type, x_trolin_tbl OUT NOCOPY INV_MOVE_ORDER_PUB.Trolin_Tbl_Type, x_trolin_val_tbl OUT NOCOPY INV_MOVE_ORDER_PUB.Trolin_Val_Tbl_Type, p_header_id IN NUMBER) IS BEGIN --调用系统标准的API得到物料搬运单的相关信息 INV_Move_Order_PUB.Get_Move_Order(p_api_version_number => 1.0, p_init_msg_list => fnd_api.G_FALSE, p_return_values => fnd_api.G_FALSE, x_return_status => x_return_status, x_msg_count => x_msg_count, x_msg_data => x_msg_data, p_header_id => p_header_id, p_header => fnd_api.G_MISS_CHAR, x_trohdr_rec => x_trohdr_rec, x_trohdr_val_rec => x_trohdr_val_rec, x_trolin_tbl => x_trolin_tbl, x_trolin_val_tbl => x_trolin_val_tbl); EXCEPTION WHEN OTHERS THEN put_log('得到物料搬运单时出现异常:' || SQLERRM); END get_move_order; /*======================================================= * FUNCTION / PROCEDURE * get_locator_type * DESCRIPTION: * 得到货位控制类型 * ARGUMENT: * p_organization_id :库存组织id * p_secondary_inventory_code:子库存代码 * RETURN: * 货位控制类型 * HISTORY: * 1.00 27/10/2010 cxy =========================================================*/ FUNCTION get_locator_type(p_organization_id IN NUMBER, p_secondary_inventory_code IN VARCHAR2) RETURN NUMBER IS l_locator_type_id NUMBER; --货位控制类型 BEGIN SELECT msi.locator_type INTO l_locator_type_id FROM mtl_secondary_inventories msi WHERE msi.organization_id = p_organization_id AND msi.secondary_inventory_name = p_secondary_inventory_code; RETURN l_locator_type_id; EXCEPTION WHEN NO_DATA_FOUND THEN RETURN NULL; WHEN TOO_MANY_ROWS THEN put_log('得到货位控制类型时返回的多个行!'); WHEN OTHERS THEN put_log('得到货位控制类型时出现异常:' || SQLERRM); END get_locator_type; /*======================================================= * FUNCTION / PROCEDURE * create_locator * DESCRIPTION: * 自动创建货位 * ARGUMENT: * x_return_status :状态 * x_msg_count :错误信息数量 * x_msg_data :错误信息 * x_locator_id :货位id * x_locator_exists :是否存在货位 * p_organization_id :库存组织id * p_subinventory_code :子库存代码 * p_project_id :项目id * p_task_id :任务id * p_locator_type :货位类型 * RETURN: * N/A * HISTORY: * 1.00 27/10/2010 cxy =========================================================*/ PROCEDURE create_locator(x_return_status OUT NOCOPY VARCHAR2, x_msg_count OUT NOCOPY NUMBER, x_msg_data OUT NOCOPY VARCHAR2, x_locator_id OUT NUMBER, x_locator_exists OUT VARCHAR2, p_organization_id IN NUMBER, p_subinventory_code IN VARCHAR2, p_project_id IN NUMBER, p_task_id IN NUMBER, p_locator_type IN NUMBER) IS l_segment_delimiter VARCHAR2(1); --键弹性域段分隔符 l_concatenated_segments VARCHAR2(100); --库存货位组合 l_project_number VARCHAR2(25); --项目编号 l_task_number VARCHAR2(25); --任务编号 l_user_id NUMBER; --登录的用户 l_resp_id NUMBER; --职位id l_resp_appl_id NUMBER; --职责应用id BEGIN --设置MFG_ORGANIZATION_ID预置文件的值 fnd_profile.put('MFG_ORGANIZATION_ID', p_organization_id); --得到段分隔符 SELECT fifs.concatenated_segment_delimiter INTO l_segment_delimiter FROM fnd_id_flex_structures fifs WHERE fifs.id_flex_structure_code = 'STOCK_LOCATORS'; --得到项目编号 SELECT ppa.segment1 INTO l_project_number FROM pa_projects_all ppa WHERE ppa.project_id = p_project_id; --得到任务编号 SELECT pt.task_number INTO l_task_number FROM pa_tasks pt WHERE pt.task_id = p_task_id; --拼接子库存货位组合 IF l_mo_debug_falg = 'Y' THEN --输出调试信息 put_log('库存组织id:' || p_organization_id); put_log('货位类型:' || p_locator_type); put_log('子库存:' || p_subinventory_code); END IF; l_concatenated_segments := p_subinventory_code || l_segment_delimiter || l_project_number || l_segment_delimiter || l_task_number; IF l_mo_debug_falg = 'Y' THEN--输出调试信息 put_log('弹性域组合:' || l_concatenated_segments); END IF; --调用API自动创建货位 inv_loc_wms_pub.create_locator(x_return_status => x_return_status, x_msg_count => x_msg_count, x_msg_data => x_msg_data, x_inventory_location_id => x_locator_id, x_locator_exists => x_locator_exists, p_organization_id => p_organization_id, p_organization_code => NULL, p_concatenated_segments => l_concatenated_segments, p_description => NULL, p_inventory_location_type => p_locator_type, p_picking_order => NULL, p_location_maximum_units => NULL, p_subinventory_code => p_subinventory_code, p_location_weight_uom_code => NULL, p_max_weight => NULL, p_volume_uom_code => NULL, p_max_cubic_area => NULL, p_x_coordinate => NULL, p_y_coordinate => NULL, p_z_coordinate => NULL, p_physical_location_id => NULL, p_pick_uom_code => NULL, p_dimension_uom_code => NULL, p_length => NULL, p_width => NULL, p_height => NULL, p_status_id => NULL, p_dropping_order => NULL); IF l_mo_debug_falg = 'Y' THEN--输出调试信息 put_log('自动生成的货位返回状态:' || x_return_status); put_log('自动生成的货位错误条数:' || x_msg_count); put_log('自动生成的货位错误信息:' || x_msg_data); put_log('自动生成的货位id为:' || x_locator_id); END IF; IF x_locator_id IS NULL THEN put_log('自动创建货位时出错:' || x_msg_data); END IF; EXCEPTION WHEN OTHERS THEN put_log('自动创建货位时出现异常:' || SQLERRM); END create_locator; /*======================================================= * FUNCTION / PROCEDURE * check_and_create_locator * DESCRIPTION: * 检查和生成货位 * ARGUMENT: * x_return_status :状态 * x_msg_count :错误信息数量 * x_msg_data :错误信息 * x_locator_type :货位类型 * x_from_locator_id :来源货位id * x_to_locator_id :目标货位id * p_trolin_rec :物料搬运单行的一行记录 * RETURN: * N/A * HISTORY: * 1.00 27/10/2010 cxy =========================================================*/ PROCEDURE check_and_create_locator(x_return_status OUT VARCHAR2, x_msg_data OUT VARCHAR2, x_locator_type OUT VARCHAR2, x_from_locator_id OUT NUMBER, x_to_locator_id OUT NUMBER, p_trolin_rec IN INV_MOVE_ORDER_PUB.Trolin_Rec_Type) IS l_from_inv_locator_type NUMBER; --源子库存的货位控制类型 l_to_inv_locator_type NUMBER; --目标子库存的货位控制类型 x_msg_count NUMBER; --错误信息数 x_locator_id NUMBER; --货位id x_locator_exists VARCHAR2(1); --货位是否存在标志 BEGIN --来源子库存的货位控制类型 l_from_inv_locator_type := get_locator_type(p_trolin_rec.organization_id, p_trolin_rec.from_subinventory_code); --目标子库存的货位控制类型 l_to_inv_locator_type := get_locator_type(p_trolin_rec.organization_id, p_trolin_rec.to_subinventory_code); IF l_mo_debug_falg = 'Y' THEN--输出调试信息 put_log(p_trolin_rec.from_subinventory_code || '的来源字库货位类型为:' || l_from_inv_locator_type); put_log(p_trolin_rec.to_subinventory_code || '的来源字库货位类型为:' || l_to_inv_locator_type); END IF; IF l_from_inv_locator_type = l_to_inv_locator_type THEN IF l_from_inv_locator_type <> 0 THEN--启用了货位控制 x_locator_type := l_from_inv_locator_type; IF p_trolin_rec.from_locator_id IS NOT NULL THEN --货位不为空 x_return_status := fnd_api.G_RET_STS_SUCCESS; x_from_locator_id := p_trolin_rec.from_locator_id; ELSE--货位为空 --自动创建货位(源库存货位) create_locator(x_return_status => x_return_status, x_msg_count => x_msg_count, x_msg_data => x_msg_data, x_locator_id => x_from_locator_id, x_locator_exists => x_locator_exists, p_organization_id => p_trolin_rec.organization_id, p_subinventory_code => p_trolin_rec.from_subinventory_code, p_project_id => p_trolin_rec.project_id, p_task_id => p_trolin_rec.task_id, p_locator_type => x_locator_type); IF l_mo_debug_falg = 'Y' THEN--输出调试信息 put_log('x_return_status:' || x_return_status); put_log('x_msg_count:' || x_msg_count); put_log('x_msg_data:' || x_msg_data); put_log('x_from_locator_id:' || x_from_locator_id); put_log('x_locator_exists:' || x_locator_exists); END IF; IF x_from_locator_id IS NULL THEN x_msg_data := '自动创建货位时错误异常,请确认是否已经包含子库存、项目和任务的信息'; END IF; END IF; ELSE--未启用货位控制 x_locator_type := 0; END IF; IF l_to_inv_locator_type <> 0 THEN --启用了货位控制 x_locator_type := l_to_inv_locator_type; IF p_trolin_rec.to_locator_id IS NOT NULL THEN--货位不为空 x_return_status := fnd_api.G_RET_STS_SUCCESS; x_to_locator_id := p_trolin_rec.to_locator_id; ELSE--货位为空 --自动创建货位(目标库存货位) create_locator(x_return_status => x_return_status, x_msg_count => x_msg_count, x_msg_data => x_msg_data, x_locator_id => x_to_locator_id, x_locator_exists => x_locator_exists, p_organization_id => p_trolin_rec.organization_id, p_subinventory_code => p_trolin_rec.to_subinventory_code, p_project_id => p_trolin_rec.project_id, p_task_id => p_trolin_rec.task_id, p_locator_type => x_locator_type); IF x_from_locator_id IS NULL THEN x_msg_data := '自动创建货位时错误异常,请确认是否已经包含子库存、项目和任务的信息'; END IF; END IF; ELSE --未启用货位控制 x_locator_type := 0; END IF; ELSE put_log('源子库存和目标子库存的货位控制类型不一致:'); END IF; EXCEPTION WHEN OTHERS THEN put_log('检查和生成货位时出现异常:' || SQLERRM); END check_and_create_locator; /*======================================================= * FUNCTION / PROCEDURE * validate_subinv_quantity * DESCRIPTION: * 验证子库存的现用量和可用量 * ARGUMENT: * x_return_status :状态 * x_msg_count :错误信息数量 * x_msg_data :错误信息 * p_trolin_rec :物料搬运单行的一行记录 * p_transaction_quantity :事务处理数量 * p_transaction_date :事务处理日期 * RETURN: * N/A * HISTORY: * 1.00 27/10/2010 cxy =========================================================*/ PROCEDURE validate_subinv_quantity(x_return_status OUT NOCOPY VARCHAR2, x_msg_count OUT NOCOPY NUMBER, x_msg_data OUT NOCOPY VARCHAR2, p_trolin_rec IN INV_MOVE_ORDER_PUB.Trolin_Rec_Type, p_transaction_quantity IN NUMBER, p_transaction_date IN DATE) IS x_qoh NUMBER; --quantity on hand x_rqoh NUMBER; --reservable quantity on hand x_qr NUMBER; --quantity reserved x_qs NUMBER; --quantity suggested x_att NUMBER; --available to transact x_atr NUMBER := 0; --available to reserve x_sqoh NUMBER; --secondary quantity on hand -- invConv change x_srqoh NUMBER; --secondary reservable quantity on hand -- invConv change x_sqr NUMBER; --secondary quantity reserved -- invConv change x_sqs NUMBER; --secondare quantity suggested -- invConv change x_satt NUMBER; --secondary available to transact -- invConv change x_satr NUMBER; --secondary available to reserve -- invConv change l_lot_control_flag BOOLEAN := FALSE; l_organization_code VARCHAR2(3); --库存组织代码 l_item_code VARCHAR2(40); --物料代码 x_locator_type VARCHAR2(60); --货位控制类型 x_from_locator_id NUMBER; --来源货位 x_to_locator_id NUMBER; --目标货位 l_locator_id NUMBER; --子库存的货位 BEGIN --得到库存代码 SELECT mp.organization_code INTO l_organization_code FROM mtl_parameters mp WHERE mp.organization_id = p_trolin_rec.organization_id; --得到物料代码 SELECT msib.segment1 INTO l_item_code FROM mtl_system_items_b msib WHERE msib.organization_id = p_trolin_rec.organization_id AND msib.inventory_item_id = p_trolin_rec.inventory_item_id; --调用系统的api检查某一库存组织某一字库的某一个物料的现有量、可用量等数量 inv_quantity_tree_pub.query_quantities(p_api_version_number => 1.0, x_return_status => x_return_status, x_msg_count => x_msg_count, x_msg_data => x_msg_data, p_organization_id => p_trolin_rec.organization_id, --库存组织 p_inventory_item_id => p_trolin_rec.inventory_item_id, --item id p_tree_mode => 2, p_is_revision_control => FALSE, p_is_lot_control => l_lot_control_flag, --是否启用批次控制 p_lot_expiration_date => p_transaction_date, --事务处理数量 p_is_serial_control => FALSE, p_grade_code => NULL, -- INVCONV NOT NEEDED NOW p_revision => NULL, --版本号 p_lot_number => NULL, --批次控制 p_subinventory_code => p_trolin_rec.from_subinventory_code, --来源子库存代码 p_locator_id => NULL, --货位id x_qoh => x_qoh, --现有量 x_rqoh => x_rqoh, --现有包流量 x_qr => x_qr, --保留量 x_qs => x_qs, --建议保留量 x_att => x_att, --可处理数量 x_atr => x_atr, --保留量 x_sqoh => x_sqoh, -- INVCONV x_srqoh => x_srqoh, -- INVCONV x_sqr => x_sqr, -- INVCONV x_sqs => x_sqs, -- INVCONV x_satt => x_satt, -- INVCONV x_satr => x_satr); -- INVCONV IF l_mo_debug_falg = 'Y' THEN --输出调试信息 put_log('得到的库存现用量的状态为:' || x_return_status); put_log('得到的库存现用量为:' || x_atr); END IF; IF x_atr <= 0 THEN x_return_status := fnd_api.G_RET_STS_UNEXP_ERROR; x_msg_data := '库存组织 ' || l_organization_code || ' 的子库存 ' || p_trolin_rec.from_subinventory_code || ' 中物料 ' || l_item_code || ' 的可用量为' || x_atr; RETURN; END IF; --检查并创建货位 check_and_create_locator(x_return_status => x_return_status, x_msg_data => x_msg_data, x_locator_type => x_locator_type, x_from_locator_id => x_from_locator_id, x_to_locator_id => x_to_locator_id, p_trolin_rec => p_trolin_rec); IF x_locator_type <> 0 THEN l_locator_id := x_from_locator_id; ELSE l_locator_id := NULL; END IF; IF x_return_status IS NULL THEN x_return_status := fnd_api.G_RET_STS_UNEXP_ERROR; x_msg_data := x_msg_data; RETURN; END IF; --调用系统的api检查某一库存组织某一字库的某一个物料的现有量、可用量等数量 x_qoh := NULL; x_rqoh := NULL; x_qr := NULL; x_qs := NULL; x_att := NULL; x_atr := NULL; x_sqoh := NULL; x_srqoh := NULL; x_sqr := NULL; x_sqs := NULL; x_satt := NULL; x_satr := NULL; --调用API得到库存下的物料的现用量和可用量 inv_quantity_tree_pub.query_quantities(p_api_version_number => 1.0, x_return_status => x_return_status, x_msg_count => x_msg_count, x_msg_data => x_msg_data, p_organization_id => p_trolin_rec.organization_id, --库存组织 p_inventory_item_id => p_trolin_rec.inventory_item_id, --item id p_tree_mode => 2, p_is_revision_control => FALSE, p_is_lot_control => l_lot_control_flag, --是否启用批次控制 p_lot_expiration_date => p_transaction_date, --事务处理数量 p_is_serial_control => FALSE, p_grade_code => NULL, -- INVCONV NOT NEEDED NOW p_revision => NULL, --版本号 p_lot_number => NULL, --批次控制 p_subinventory_code => p_trolin_rec.from_subinventory_code, --来源子库存代码 p_locator_id => l_locator_id, --货位id x_qoh => x_qoh, --现有量 x_rqoh => x_rqoh, --现有包流量 x_qr => x_qr, --保留量 x_qs => x_qs, --建议保留量 x_att => x_att, --可处理数量 x_atr => x_atr, --保留量 x_sqoh => x_sqoh, -- INVCONV x_srqoh => x_srqoh, -- INVCONV x_sqr => x_sqr, -- INVCONV x_sqs => x_sqs, -- INVCONV x_satt => x_satt, -- INVCONV x_satr => x_satr); -- INVCONV IF l_mo_debug_falg = 'Y' THEN --输出调试信息 put_log('库存组织'); END IF; IF x_return_status <> fnd_api.G_RET_STS_SUCCESS THEN x_return_status := fnd_api.G_RET_STS_UNEXP_ERROR; x_msg_data := '获取库存组织 ' || l_organization_code || ' 的子库存 ' || p_trolin_rec.from_subinventory_code || ' 中物料 ' || l_item_code || ' 的可用量时出现异常!'; END IF; IF l_mo_debug_falg = 'Y' THEN --输出调试信息 put_log('得到的库存现用量的状态为:' || x_return_status); put_log('得到的库存现用量为:' || x_atr); END IF; IF x_atr <= 0 THEN x_return_status := fnd_api.G_RET_STS_UNEXP_ERROR; x_msg_data := '库存组织 ' || l_organization_code || ' 的子库存 ' || p_trolin_rec.from_subinventory_code || ' 中物料 ' || l_item_code || ' 的可用量为 ' || x_atr; END IF; IF x_atr <> 0 AND x_atr < p_transaction_quantity THEN x_return_status := fnd_api.G_RET_STS_UNEXP_ERROR; x_msg_data := '库存组织 ' || l_organization_code || ' 的子库存 ' || p_trolin_rec.from_subinventory_code || ' 中物料 ' || l_item_code || ' 的可用量 ' || x_atr || ' 小于处理数量 ' || p_transaction_quantity; END IF; EXCEPTION WHEN OTHERS THEN put_log('检查库存的现用量和可用量时出现异常:' || SQLERRM); END validate_subinv_quantity; /*======================================================= * FUNCTION / PROCEDURE * validate_data * DESCRIPTION: * 验证客户化临时表的数据 * ARGUMENT: * x_return_status :状态 * x_msg_count :错误信息数量 * x_msg_data :错误信息 * p_trolin_rec :物料搬运单行的一行记录 * p_line_id :物料搬运单行id * p_transaction_quantity :事务处理数量 * p_transaction_date :事务处理日期 * RETURN: * N/A * HISTORY: * 1.00 27/10/2010 cxy =========================================================*/ PROCEDURE validate_data(x_return_status OUT NOCOPY VARCHAR2, x_msg_count OUT NOCOPY NUMBER, x_msg_data OUT NOCOPY VARCHAR2, p_trolin_rec IN INV_MOVE_ORDER_PUB.Trolin_Rec_Type, p_line_id IN NUMBER, p_transaction_quantity IN NUMBER, p_transaction_date IN DATE) IS l_error_flag VARCHAR2(1) := 'N'; --错误标志 l_error_message VARCHAR2(1000) := NULL;--错误信息 BEGIN --初始化 l_error_flag := 'N'; IF p_trolin_rec.quantity <> p_transaction_quantity THEN --接口中的数量和EBS系统中的剩余的数量不一致 l_error_flag := 'Y'; l_error_message := '接口中的数量' || p_transaction_quantity || '与EBS系统中的剩余的数量' || p_trolin_rec.quantity || '不一致'; GOTO end_error; END IF; --检查并验证库存的现用量和可用量 validate_subinv_quantity(x_return_status => x_return_status, x_msg_count => x_msg_count, x_msg_data => x_msg_data, p_trolin_rec => p_trolin_rec, p_transaction_quantity => p_transaction_quantity, p_transaction_date => p_transaction_date); IF x_return_status <> fnd_api.G_RET_STS_SUCCESS THEN x_return_status := fnd_api.G_RET_STS_UNEXP_ERROR; RETURN; END IF; NULL; --添加一个可执行语句，防止GOTO语句出错 <<end_error>> IF l_error_flag = 'N' THEN x_return_status := fnd_api.G_RET_STS_SUCCESS; ELSE x_return_status := fnd_api.G_RET_STS_UNEXP_ERROR; x_msg_count := 1; x_msg_data := l_error_message; END IF; EXCEPTION WHEN OTHERS THEN put_log('进行数据有效性验证时出现异常:' || SQLERRM); END validate_data; /*======================================================= * FUNCTION / PROCEDURE * get_trolin_rec * DESCRIPTION: * 得到物料搬运单的一行记录 * ARGUMENT: * x_return_status :状态 * x_trolin_rec :物料搬运单行的一行记录 * p_trolin_tbl :一条物料搬运单的所有搬运单行 * p_line_id :物料搬运单行id * RETURN: * N/A * HISTORY: * 1.00 27/10/2010 cxy =========================================================*/ PROCEDURE get_trolin_rec(x_return_status OUT NOCOPY VARCHAR2, x_trolin_rec OUT NOCOPY INV_MOVE_ORDER_PUB.Trolin_Rec_Type, p_trolin_tbl IN INV_MOVE_ORDER_PUB.Trolin_Tbl_Type, p_line_id IN NUMBER) IS l_table_index NUMBER := 0; BEGIN FOR l_table_index IN 1 .. p_trolin_tbl.COUNT LOOP IF p_line_id = p_trolin_tbl(l_table_index).line_id THEN x_trolin_rec := p_trolin_tbl(l_table_index); x_return_status := fnd_api.G_RET_STS_SUCCESS; RETURN; END IF; END LOOP; --FOR x_table_index IN x_trolin_tbl.COUNT LOOP x_return_status := fnd_api.G_RET_STS_UNEXP_ERROR; EXCEPTION WHEN OTHERS THEN put_log('得到物料搬运单时出现异常:' || SQLERRM); END get_trolin_rec; /*======================================================= * FUNCTION / PROCEDURE * get_transaction_action_id * DESCRIPTION: * 得到事务处理的活动id * ARGUMENT: * p_transaction_type_id:事务处理类型id * RETURN: * 事务处理的活动id * HISTORY: * 1.00 27/10/2010 cxy =========================================================*/ FUNCTION get_transaction_action_id(p_transaction_type_id IN NUMBER) RETURN NUMBER IS l_transaction_action_id NUMBER;--事务处理的活动id BEGIN SELECT mtt.transaction_action_id INTO l_transaction_action_id FROM mtl_transaction_types mtt WHERE mtt.transaction_type_id = p_transaction_type_id; RETURN l_transaction_action_id; EXCEPTION WHEN NO_DATA_FOUND THEN RETURN NULL; WHEN TOO_MANY_ROWS THEN put_log('得到事务处理的活动id时返回的多个行!'); WHEN OTHERS THEN put_log('得到物料搬运单活动id时出现异常:' || SQLERRM); END get_transaction_action_id; /*======================================================= * FUNCTION / PROCEDURE * get_acct_period_id * DESCRIPTION: * 得到库存会计期id * ARGUMENT: * p_organization_id :库存组织id * p_transaction_date:事务处理日期 * RETURN: * 库存会计期id * HISTORY: * 1.00 27/10/2010 cxy =========================================================*/ FUNCTION get_acct_period_id(p_organization_id IN NUMBER, p_transaction_date IN DATE) RETURN NUMBER IS l_acct_period_id NUMBER;--库存会计期id BEGIN SELECT oap.acct_period_id INTO l_acct_period_id FROM org_acct_periods oap WHERE oap.organization_id = p_organization_id AND (p_transaction_date BETWEEN oap.period_start_date AND oap.schedule_close_date); RETURN l_acct_period_id; EXCEPTION WHEN NO_DATA_FOUND THEN RETURN NULL; WHEN TOO_MANY_ROWS THEN put_log('得到库存会计期id时返回的多个行!'); WHEN OTHERS THEN put_log('得到库存会计期id时出现异常:' || SQLERRM); END get_acct_period_id; /*======================================================= * FUNCTION / PROCEDURE * insert_mmtt_row * DESCRIPTION: * 插入某一物料搬运单事务处理临时表的一条记录 * ARGUMENT: * x_return_status :状态 * x_transaction_temp_id :临时表id * x_msg_data :错误信息 * x_trohdr_rec :物料搬运单头的一行记录 * x_trohdr_val_rec :物料搬运单头的一行记录对应的值 * x_trolin_tbl :一个物料搬运单头对应的所有物料搬运单行 * x_trolin_val_tbl :一个物料搬运单头对应的所有物料搬运单行的值 * p_header_id :物料搬运单头id * p_line_id :物料搬运单行id * p_transaction_quantity :事务处理数量 * p_transaction_date :事务处理日期 * RETURN: * N/A * HISTORY: * 1.00 27/10/2010 cxy =========================================================*/ PROCEDURE insert_mmtt_row(x_return_status OUT NOCOPY VARCHAR2, x_transaction_temp_id OUT NUMBER, p_trohdr_rec IN INV_MOVE_ORDER_PUB.Trohdr_Rec_Type, p_trohdr_val_rec IN INV_MOVE_ORDER_PUB.Trohdr_Val_Rec_Type, p_trolin_tbl IN INV_MOVE_ORDER_PUB.Trolin_Tbl_Type, p_trolin_val_tbl IN INV_MOVE_ORDER_PUB.Trolin_Val_Tbl_Type, p_header_id IN NUMBER, p_line_id IN NUMBER, p_transaction_quantity IN NUMBER, p_transaction_date IN DATE) IS l_mo_line_detail_rec inv_mo_line_detail_util.g_mmtt_rec; l_transaction_temp_id NUMBER; --临时表的id l_trolin_rec INV_MOVE_ORDER_PUB.Trolin_Rec_Type; x_locator_type VARCHAR2(60); --货位控制类型 x_from_locator_id NUMBER; --来源货位 x_to_locator_id NUMBER; --目标货位 x_msg_data VARCHAR2(2000); --错误信息 BEGIN --得到某一物料搬运单行的信息 get_trolin_rec(x_return_status => x_return_status, x_trolin_rec => l_trolin_rec, p_trolin_tbl => p_trolin_tbl, p_line_id => p_line_id); --检查是否启用货位控制,如果启用，则动态生成货位 SELECT mtl_material_transactions_s.nextval INTO l_transaction_temp_id FROM DUAL; --判断货位是否已经存在，如果存在,则自动创建货位 check_and_create_locator(x_return_status => x_return_status, x_msg_data => x_msg_data, x_locator_type => x_locator_type, x_from_locator_id => x_from_locator_id, x_to_locator_id => x_to_locator_id, p_trolin_rec => l_trolin_rec); IF l_mo_debug_falg = 'Y' THEN --输出调试信息 put_log('项目号:' || l_trolin_rec.project_id); put_log('项目号:' || l_trolin_rec.task_id); put_log('来源货位id:' || x_from_locator_id); put_log('目标货位id:' || x_to_locator_id); END IF; l_mo_line_detail_rec.locator_id := x_from_locator_id; --源子库存货位 l_mo_line_detail_rec.transfer_to_location := x_to_locator_id; --目标子库存货位 l_mo_line_detail_rec.transaction_header_id := NULL; l_mo_line_detail_rec.transaction_temp_id := l_transaction_temp_id; l_mo_line_detail_rec.source_code := NULL; l_mo_line_detail_rec.source_line_id := NULL; l_mo_line_detail_rec.transaction_mode := 1; l_mo_line_detail_rec.lock_flag := 'N'; l_mo_line_detail_rec.last_update_date := SYSDATE; l_mo_line_detail_rec.last_updated_by := fnd_global.user_id; l_mo_line_detail_rec.creation_date := SYSDATE; l_mo_line_detail_rec.created_by := fnd_global.user_id; l_mo_line_detail_rec.last_update_login := fnd_global.user_id; l_mo_line_detail_rec.request_id := NULL; l_mo_line_detail_rec.program_application_id := NULL; l_mo_line_detail_rec.program_id := NULL; l_mo_line_detail_rec.program_update_date := NULL; --从物料搬运单得到item_id l_mo_line_detail_rec.inventory_item_id := l_trolin_rec.inventory_item_id; l_mo_line_detail_rec.revision := NULL; l_mo_line_detail_rec.organization_id := l_trolin_rec.organization_id; l_mo_line_detail_rec.subinventory_code := l_trolin_rec.from_subinventory_code; --l_mo_line_detail_rec.locator_id := l_trolin_rec.from_locator_id; l_mo_line_detail_rec.transaction_quantity := p_transaction_quantity; l_mo_line_detail_rec.primary_quantity := p_transaction_quantity; l_mo_line_detail_rec.transaction_uom := l_trolin_rec.uom_code; l_mo_line_detail_rec.transaction_cost := NULL; l_mo_line_detail_rec.transaction_type_id := l_trolin_rec.transaction_type_id; l_mo_line_detail_rec.transaction_action_id := get_transaction_action_id(l_trolin_rec.transaction_type_id); l_mo_line_detail_rec.transaction_source_type_id := l_trolin_rec.transaction_source_type_id; l_mo_line_detail_rec.transaction_source_id := p_header_id; l_mo_line_detail_rec.transaction_source_name := NULL; l_mo_line_detail_rec.transaction_date := p_transaction_date; l_mo_line_detail_rec.acct_period_id := get_acct_period_id(l_trolin_rec.organization_id, p_transaction_date); --账户期间id l_mo_line_detail_rec.distribution_account_id := NULL; l_mo_line_detail_rec.transaction_reference := NULL; l_mo_line_detail_rec.requisition_line_id := NULL; l_mo_line_detail_rec.requisition_distribution_id := NULL; l_mo_line_detail_rec.reason_id := l_trolin_rec.reason_id; l_mo_line_detail_rec.lot_number := l_trolin_rec.lot_number; l_mo_line_detail_rec.lot_expiration_date := NULL; l_mo_line_detail_rec.serial_number := NULL; l_mo_line_detail_rec.receiving_document := NULL; l_mo_line_detail_rec.demand_id := NULL; l_mo_line_detail_rec.rcv_transaction_id := NULL; l_mo_line_detail_rec.move_transaction_id := NULL; l_mo_line_detail_rec.completion_transaction_id := NULL; l_mo_line_detail_rec.wip_entity_type := NULL; l_mo_line_detail_rec.schedule_id := NULL; l_mo_line_detail_rec.repetitive_line_id := NULL; l_mo_line_detail_rec.employee_code := NULL; l_mo_line_detail_rec.primary_switch := NULL; l_mo_line_detail_rec.schedule_update_code := NULL; l_mo_line_detail_rec.setup_teardown_code := NULL; l_mo_line_detail_rec.item_ordering := NULL; l_mo_line_detail_rec.negative_req_flag := NULL; l_mo_line_detail_rec.operation_seq_num := NULL; l_mo_line_detail_rec.picking_line_id := NULL; l_mo_line_detail_rec.trx_source_line_id := p_line_id; l_mo_line_detail_rec.trx_source_delivery_id := NULL; l_mo_line_detail_rec.physical_adjustment_id := NULL; l_mo_line_detail_rec.cycle_count_id := NULL; l_mo_line_detail_rec.rma_line_id := NULL; l_mo_line_detail_rec.customer_ship_id := NULL; l_mo_line_detail_rec.currency_code := NULL; l_mo_line_detail_rec.currency_conversion_rate := NULL; l_mo_line_detail_rec.currency_conversion_type := NULL; l_mo_line_detail_rec.currency_conversion_date := NULL; l_mo_line_detail_rec.ussgl_transaction_code := NULL; l_mo_line_detail_rec.vendor_lot_number := NULL; l_mo_line_detail_rec.encumbrance_account := NULL; l_mo_line_detail_rec.encumbrance_amount := NULL; l_mo_line_detail_rec.ship_to_location := NULL; l_mo_line_detail_rec.shipment_number := NULL; l_mo_line_detail_rec.transfer_cost := NULL; l_mo_line_detail_rec.transportation_cost := NULL; l_mo_line_detail_rec.transportation_account := NULL; l_mo_line_detail_rec.freight_code := NULL; l_mo_line_detail_rec.containers := NULL; l_mo_line_detail_rec.waybill_airbill := NULL; l_mo_line_detail_rec.expected_arrival_date := NULL; l_mo_line_detail_rec.transfer_subinventory := l_trolin_rec.to_subinventory_code; l_mo_line_detail_rec.transfer_organization := NULL; l_mo_line_detail_rec.new_average_cost := NULL; l_mo_line_detail_rec.value_change := NULL; l_mo_line_detail_rec.percentage_change := NULL; l_mo_line_detail_rec.material_allocation_temp_id := NULL; l_mo_line_detail_rec.demand_source_header_id := p_header_id; l_mo_line_detail_rec.demand_source_line := to_char(p_line_id); l_mo_line_detail_rec.demand_source_delivery := NULL; l_mo_line_detail_rec.item_segments := NULL; l_mo_line_detail_rec.item_description := NULL; l_mo_line_detail_rec.item_trx_enabled_flag := NULL; l_mo_line_detail_rec.item_location_control_code := NULL; l_mo_line_detail_rec.item_restrict_subinv_code := NULL; l_mo_line_detail_rec.item_restrict_locators_code := NULL; l_mo_line_detail_rec.item_revision_qty_control_code := NULL; l_mo_line_detail_rec.item_primary_uom_code := NULL; l_mo_line_detail_rec.item_uom_class := NULL; l_mo_line_detail_rec.item_shelf_life_code := NULL; l_mo_line_detail_rec.item_shelf_life_days := NULL; l_mo_line_detail_rec.item_lot_control_code := NULL; l_mo_line_detail_rec.item_serial_control_code := NULL; l_mo_line_detail_rec.item_inventory_asset_flag := NULL; l_mo_line_detail_rec.allowed_units_lookup_code := NULL; l_mo_line_detail_rec.department_id := NULL; l_mo_line_detail_rec.department_code := NULL; l_mo_line_detail_rec.wip_supply_type := NULL; l_mo_line_detail_rec.supply_subinventory := NULL; l_mo_line_detail_rec.supply_locator_id := NULL; l_mo_line_detail_rec.valid_subinventory_flag := NULL; l_mo_line_detail_rec.valid_locator_flag := NULL; l_mo_line_detail_rec.locator_segments := NULL; l_mo_line_detail_rec.current_locator_control_code := NULL; l_mo_line_detail_rec.number_of_lots_entered := NULL; l_mo_line_detail_rec.wip_commit_flag := NULL; l_mo_line_detail_rec.next_lot_number := NULL; l_mo_line_detail_rec.lot_alpha_prefix := NULL; l_mo_line_detail_rec.next_serial_number := NULL; l_mo_line_detail_rec.serial_alpha_prefix := NULL; l_mo_line_detail_rec.shippable_flag := NULL; l_mo_line_detail_rec.posting_flag := 'Y'; l_mo_line_detail_rec.required_flag := NULL; l_mo_line_detail_rec.process_flag := 'Y'; l_mo_line_detail_rec.error_code := NULL; l_mo_line_detail_rec.error_explanation := NULL; l_mo_line_detail_rec.attribute_category := NULL; l_mo_line_detail_rec.attribute1 := NULL; l_mo_line_detail_rec.attribute2 := NULL; l_mo_line_detail_rec.attribute3 := NULL; l_mo_line_detail_rec.attribute4 := NULL; l_mo_line_detail_rec.attribute5 := NULL; l_mo_line_detail_rec.attribute6 := NULL; l_mo_line_detail_rec.attribute7 := NULL; l_mo_line_detail_rec.attribute8 := NULL; l_mo_line_detail_rec.attribute9 := NULL; l_mo_line_detail_rec.attribute10 := NULL; l_mo_line_detail_rec.attribute11 := NULL; l_mo_line_detail_rec.attribute12 := NULL; l_mo_line_detail_rec.attribute13 := NULL; l_mo_line_detail_rec.attribute14 := NULL; l_mo_line_detail_rec.attribute15 := NULL; l_mo_line_detail_rec.movement_id := NULL; l_mo_line_detail_rec.reservation_quantity := NULL; l_mo_line_detail_rec.shipped_quantity := NULL; l_mo_line_detail_rec.transaction_line_number := NULL; l_mo_line_detail_rec.task_id := NULL; l_mo_line_detail_rec.to_task_id := NULL; l_mo_line_detail_rec.source_task_id := NULL; l_mo_line_detail_rec.project_id := NULL; l_mo_line_detail_rec.source_project_id := NULL; l_mo_line_detail_rec.pa_expenditure_org_id := NULL; l_mo_line_detail_rec.to_project_id := NULL; l_mo_line_detail_rec.expenditure_type := NULL; l_mo_line_detail_rec.final_completion_flag := 'N'; l_mo_line_detail_rec.transfer_percentage := NULL; l_mo_line_detail_rec.transaction_sequence_id := NULL; l_mo_line_detail_rec.material_account := NULL; l_mo_line_detail_rec.material_overhead_account := NULL; l_mo_line_detail_rec.resource_account := NULL; l_mo_line_detail_rec.outside_processing_account := NULL; l_mo_line_detail_rec.overhead_account := NULL; l_mo_line_detail_rec.flow_schedule := NULL; l_mo_line_detail_rec.cost_group_id := NULL; l_mo_line_detail_rec.demand_class := NULL; l_mo_line_detail_rec.qa_collection_id := NULL; l_mo_line_detail_rec.kanban_card_id := NULL; l_mo_line_detail_rec.overcompletion_transaction_id := NULL; l_mo_line_detail_rec.overcompletion_primary_qty := NULL; l_mo_line_detail_rec.overcompletion_transaction_qty := NULL; l_mo_line_detail_rec.end_item_unit_number := NULL; l_mo_line_detail_rec.scheduled_payback_date := NULL; l_mo_line_detail_rec.line_type_code := NULL; l_mo_line_detail_rec.parent_transaction_temp_id := NULL; l_mo_line_detail_rec.put_away_strategy_id := NULL; l_mo_line_detail_rec.put_away_rule_id := NULL; l_mo_line_detail_rec.pick_strategy_id := l_trolin_rec.pick_strategy_id; l_mo_line_detail_rec.pick_rule_id := NULL; l_mo_line_detail_rec.common_bom_seq_id := NULL; l_mo_line_detail_rec.common_routing_seq_id := NULL; l_mo_line_detail_rec.cost_type_id := NULL; l_mo_line_detail_rec.org_cost_group_id := NULL; l_mo_line_detail_rec.move_order_line_id := p_line_id; l_mo_line_detail_rec.task_group_id := NULL; l_mo_line_detail_rec.pick_slip_number := l_trolin_rec.pick_slip_number; l_mo_line_detail_rec.reservation_id := NULL; l_mo_line_detail_rec.transaction_status := 2; -- --Indicates if it has to be processed or just saved. NULL or 1 is default behavior. 2 is save only. 3 is ready to process l_mo_line_detail_rec.transfer_cost_group_id := NULL; l_mo_line_detail_rec.lpn_id := NULL; l_mo_line_detail_rec.transfer_lpn_id := NULL; l_mo_line_detail_rec.pick_slip_date := l_trolin_rec.pick_slip_date; l_mo_line_detail_rec.content_lpn_id := NULL; l_mo_line_detail_rec.secondary_transaction_quantity := NULL; l_mo_line_detail_rec.secondary_uom_code := NULL; --调用标准的API对MMTT进行插入 inv_mo_line_detail_util.insert_row(x_return_status => x_return_status, p_mo_line_detail_rec => l_mo_line_detail_rec); IF l_mo_debug_falg = 'Y' THEN --输出调试信息 put_log('x_return_status:' || x_return_status); END IF; IF x_return_status = fnd_api.G_RET_STS_SUCCESS THEN x_transaction_temp_id := l_transaction_temp_id; --回传mmtt的唯一性标识 END IF; EXCEPTION WHEN OTHERS THEN put_log('插入mmtt处理行时出现异常:' || SQLERRM); END insert_mmtt_row; /*======================================================= * FUNCTION / PROCEDURE * delete_mmtt_row * DESCRIPTION: * 删除某一物料搬运单事务处理临时表的一条记录 * ARGUMENT: * p_header_id:物料搬运单头id * p_line_id :物料搬运单行id * RETURN: * N/A * HISTORY: * 1.00 27/10/2010 cxy =========================================================*/ PROCEDURE delete_mmtt_row(x_return_status OUT VARCHAR2, p_line_id IN NUMBER, p_transaction_temp_id IN NUMBER) IS BEGIN inv_mo_line_detail_util.delete_row(x_return_status => x_return_status, p_line_id => p_line_id, p_line_detail_id => p_transaction_temp_id); EXCEPTION WHEN OTHERS THEN put_log('删除某一物料搬运单的处理行时出现异常:' || SQLERRM); END delete_mmtt_row; /*======================================================= * FUNCTION / PROCEDURE * process_data * DESCRIPTION: * 自动处理物料搬运单 * ARGUMENT: * x_return_status :返回状态 * x_msg_count :返回的行数 * x_msg_data :返回的信息 * p_line_id :物料搬运单行id * RETURN: * N/A * HISTORY: * 1.00 27/10/2010 cxy =========================================================*/ PROCEDURE process_data(x_return_status OUT NOCOPY VARCHAR2, x_msg_count OUT NOCOPY NUMBER, x_msg_data OUT NOCOPY VARCHAR2, p_line_id IN NUMBER) IS x_number_of_rows NUMBER; x_detailed_qty NUMBER; x_revision VARCHAR2(3); x_locator_id NUMBER; x_transfer_to_location NUMBER; x_lot_number VARCHAR2(80); x_expiration_date DATE; x_transaction_temp_id NUMBER; l_trolin_tbl INV_MOVE_ORDER_PUB.Trolin_Tbl_Type; l_mold_tbl INV_MO_LINE_DETAIL_UTIL.g_mmtt_tbl_type; x_mmtt_tbl INV_MO_LINE_DETAIL_UTIL.g_mmtt_tbl_type; x_trolin_tbl INV_Move_Order_PUB.Trolin_Tbl_Type; l_mo_line_detail_rec inv_mo_line_detail_util.g_mmtt_rec; BEGIN IF l_mo_debug_falg = 'Y' THEN --输出调试信息 put_log('--------创建物料搬运单分配行开始----------'); END IF; INV_Replenish_Detail_PUB.line_details_pub(p_line_id => p_line_id, x_number_of_rows => x_number_of_rows, x_detailed_qty => x_detailed_qty, x_return_status => x_return_status, x_msg_count => x_msg_count, x_msg_data => x_msg_data, x_revision => x_revision, x_locator_id => x_locator_id, x_transfer_to_location => x_transfer_to_location, x_lot_number => x_lot_number, x_expiration_date => x_expiration_date, x_transaction_temp_id => x_transaction_temp_id, p_transaction_header_id => NULL, p_transaction_mode => NULL, p_move_order_type => 1, p_serial_flag => FND_API.G_TRUE, p_plan_tasks => NULL, p_auto_pick_confirm => NULL, p_commit => NULL); IF l_mo_debug_falg = 'Y' THEN --输出调试信息 put_log('x_number_of_rows:' || x_number_of_rows); put_log('x_return_status:' || x_return_status); put_log('--------创建物料搬运单分配行结束----------'); END IF; IF x_number_of_rows > 0 AND x_return_status = fnd_api.G_RET_STS_SUCCESS THEN --得到物料搬运单行信息 IF l_mo_debug_falg = 'Y' THEN --输出调试信息 put_log('--------获得物料搬运单行信息开始----------'); END IF; l_trolin_tbl := inv_trolin_util.query_rows(p_line_id => p_line_id); IF l_mo_debug_falg = 'Y' THEN--输出调试信息 put_log('--------获得物料搬运单行信息结束----------'); put_log('--------自动挑库确认开始----------'); END IF; --自动挑库确认 INV_PICK_WAVE_PICK_CONFIRM_PUB.Pick_Confirm(p_api_version_number => 1.0, p_init_msg_list => fnd_api.G_FALSE, p_commit => fnd_api.G_FALSE, x_return_status => x_return_status, x_msg_count => x_msg_count, x_msg_data => x_msg_data, p_move_order_type => 1, p_transaction_mode => 1, p_trolin_tbl => l_trolin_tbl, p_mold_tbl => l_mold_tbl, x_mmtt_tbl => x_mmtt_tbl, x_trolin_tbl => x_trolin_tbl, p_transaction_date => NULL); IF l_mo_debug_falg = 'Y' THEN--输出调试信息 put_log('x_return_status:' || x_return_status); put_log('x_msg_count:' || x_msg_count); put_log('x_msg_data:' || x_msg_data); put_log('--------自动挑库确认结束----------'); END IF; END IF; EXCEPTION WHEN OTHERS THEN put_log('自动处理物料搬运单时出错:' || SQLERRM); END process_data; /*======================================================= * FUNCTION / PROCEDURE * process_history_data * DESCRIPTION: * 处理上一次错误的历史记录 * ARGUMENT: * x_processd_flag:上一次的错误记录是否已经被处理 * p_line_id :物料搬运单行id * RETURN: * N/A * HISTORY: * 1.00 27/10/2010 cxy =========================================================*/ PROCEDURE process_history_data(x_processd_flag OUT VARCHAR2, p_line_id IN NUMBER) IS CURSOR cur_processed_order_info IS SELECT mtrl.header_id header_id, --物料搬运单头id, mtrh.request_number request_number, --物料搬运单编号 mtrl.line_id line_id, --物料搬运单行id mtrl.line_number line_number, --行号 mtrl.organization_id organization_id, --库存组织ID mtrl.inventory_item_id inventory_item_id, --物料ID mtrh.transaction_type_id transaction_type_id, --事务处理类型ID mtt.transaction_type_name transaction_type_name, --事务处理类型名称 mlt.lookup_code line_status, --行状态 mlt.meaning line_status_meaning --行状态含义 FROM mtl_txn_request_lines mtrl, mtl_txn_request_headers mtrh, mtl_transaction_types mtt, mfg_lookups mlt WHERE mtrl.header_id = mtrh.header_id AND mtrl.transaction_type_id = mtt.transaction_type_id AND mtrl.line_status = mlt.lookup_code AND mlt.lookup_type = 'MTL_TXN_REQUEST_STATUS' AND mtrl.line_status = 5 --表示已经关闭,表示上次错误的记录已经成功处理 AND mtt.transaction_action_id = 2 --事务处理的活动为子库存转移的记录 AND mtrl.line_id = p_line_id; l_processed_count NUMBER := 0; v_processd_flag VARCHAR2(1); --上次错误的记录是否已经被处理 BEGIN x_processd_flag := 'N'; --默认为上次的记录没有被处理 FOR rec_processed_order_info IN cur_processed_order_info LOOP l_processed_count := l_processed_count + 1; IF l_processed_count <> 0 THEN v_processd_flag := 'Y'; EXIT; END IF; END LOOP; IF l_processed_count = 0 THEN v_processd_flag := 'N'; END IF; IF v_processd_flag = 'Y' THEN x_processd_flag := 'Y'; --如果上一次的错误记录已经被处理,则更新客户化表中的记录的状态 UPDATE cux_ediint_item_move_in ceimi SET ceimi.process_id = 4, --将3改成4,表示此条记录已经被处理，将这条记录的状态改成已经处理 ceimi.error_message = NULL, --错误信息修改成空 ceimi.attribute15 = '上一次处理出错,已经通过系统手工或者其他方式进行处理!', ceimi.last_update_date = SYSDATE, ceimi.last_updated_by = fnd_global.user_id, ceimi.last_update_login = fnd_global.user_id WHERE ceimi.line_id = p_line_id AND ceimi.process_id IN (1, 3); --错误的状态 END IF; EXCEPTION WHEN OTHERS THEN put_log('删除某一物料搬运单的处理行时出现异常:' || SQLERRM); END process_history_data; /*======================================================= * FUNCTION / PROCEDURE * main * DESCRIPTION: * 并发程序入口 * ARGUMENT: * errbuf :并发程序的系统参数，返回错误信息 * retcode :并发程序的系统参数, 执行状态 * RETURN: * N/A * HISTORY: * 1.00 27/10/2010 cxy =========================================================*/ /*================================================================ * 主程序的步骤： * 10 初始化 * 20 处理历史记录 * 30 验证数据有效性 * 40 在MMTT表中插入一条记录 * 50 自动处理物料搬运单 * 60 更新客户化表的状态和错误信息 * 70 输出处理结果 =================================================================*/ PROCEDURE main(errbuf OUT NOCOPY VARCHAR2, retcode OUT NOCOPY VARCHAR2) IS --使用cursor取出客户化事务处理表中的所有物料搬运单头信息,不取重复的记录 CURSOR cur_item_move_imp_header IS SELECT DISTINCT ceimi.header_id header_id, --物料搬运单头id ceimi.request_number request_number --物料搬运单编号 FROM cux_ediint_item_move_in ceimi WHERE ceimi.process_id IN (0, 3); ----处理新增或者上次出错的记录,0表示没有处理的记录,3表示上一次处理出错的记录 --使用cursor 取出客户化表中的物料搬运单行信息 CURSOR cur_item_move_imp_line(p_header_id NUMBER) IS SELECT ceimi.header_id header_id, --物料搬运单头id ceimi.request_number request_number, --物料搬运单编号 ceimi.line_id line_id, --物料搬运单行id ceimi.transaction_quantity transaction_quantity, --事务处理数量 ceimi.transaction_date transaction_date --事务处理日期 FROM cux_ediint_item_move_in ceimi WHERE ceimi.process_id IN (0, 3) --处理新增或者上次出错的记录,0表示没有处理的记录,3表示上一次处理出错的记录 AND ceimi.header_id = p_header_id ORDER BY ceimi.line_id; x_return_status VARCHAR2(10); --处理状态 x_msg_count NUMBER; --错误信息数量 x_msg_data VARCHAR2(2000); --错误信息 x_trohdr_rec INV_MOVE_ORDER_PUB.Trohdr_Rec_Type; --物料搬运单头记录 x_trohdr_val_rec INV_MOVE_ORDER_PUB.Trohdr_Val_Rec_Type; --物料搬运单头对应的记录 x_trolin_tbl INV_MOVE_ORDER_PUB.Trolin_Tbl_Type; --物料搬运单的所有行记录 x_trolin_val_tbl INV_MOVE_ORDER_PUB.Trolin_Val_Tbl_Type; --物料搬运单的所有行对应的记录 x_trolin_rec INV_MOVE_ORDER_PUB.Trolin_Rec_Type; --一条物料搬运单行 l_line_count NUMBER := 0; --处理的总行数 l_line_success_count NUMBER := 0; --处理成功的行数 x_transaction_temp_id NUMBER; --取得mmtt表的唯一性标识 x_processd_flag VARCHAR2(1); --上一次的历史记录是否被手工处理 BEGIN --循环所有的物料搬运单 FOR rec_item_move_imp_header IN cur_item_move_imp_header LOOP /*============================================================ * 10 初始化 * ------------------------------------------------------------ * 描述：初始化变量,并得到物料搬运单的相关信息 =============================================================*/ x_trohdr_rec := NULL; --物料搬运单头记录 x_trohdr_val_rec := NULL; --得到物料搬运单信息 IF l_mo_debug_falg = 'Y' THEN--输出调试信息 put_log('---------------获取物料搬运单信息开始---------------------------'); END IF; x_return_status := NULL; --处理状态 get_move_order(x_return_status => x_return_status, x_msg_count => x_msg_count, x_msg_data => x_msg_data, x_trohdr_rec => x_trohdr_rec, x_trohdr_val_rec => x_trohdr_val_rec, x_trolin_tbl => x_trolin_tbl, x_trolin_val_tbl => x_trolin_val_tbl, p_header_id => rec_item_move_imp_header.header_id); IF l_mo_debug_falg = 'Y' THEN--输出调试信息 put_log('---------------获取物料搬运单行状态：x_return_status:' || x_return_status); put_log('---------------错误信息条数：x_msg_count:' || x_msg_count); put_log('---------------错误信息：x_msg_data:' || x_msg_data); put_log('---------------获取物料搬运单信息结束---------------------------'); END IF; --物料搬运单头状态正常,则处理此物料搬运单的所有行记录 IF x_return_status = fnd_api.G_RET_STS_SUCCESS THEN --循环物料搬运单的行,自动处理物料搬运单按照行进行处理 FOR rec_item_move_imp_line IN cur_item_move_imp_line(rec_item_move_imp_header.header_id) LOOP --进行初始化 x_return_status := NULL; --处理状态 x_msg_count := NULL; --错误信息条数 x_msg_data := NULL; --错误信息 --为此执行块创建一个SAVEPOINT BEGIN --创建SAVEPOINT cux_item_move_line SAVEPOINT cux_item_move_line; UPDATE cux_ediint_item_move_in ceimi SET ceimi.process_id = 1, --1表示程序正在处理 ceimi.request_id = fnd_global.conc_request_id, ceimi.last_update_date = SYSDATE, ceimi.last_updated_by = fnd_global.user_id, ceimi.last_update_login = fnd_global.user_id WHERE ceimi.line_id = rec_item_move_imp_line.line_id; /*========================================================= * 20 处理历史记录 * --------------------------------------------------------- * 描述：检测历史数据是否已经被处理， * 并更新客户表的状态和错误信息 ==========================================================*/ process_history_data(x_processd_flag => x_processd_flag, p_line_id => rec_item_move_imp_line.line_id); IF x_processd_flag = 'N' THEN --等于‘N'表示上次处理错误的记录没有被手工处理 --得到某一物料搬运单行的信息 get_trolin_rec(x_return_status => x_return_status, x_trolin_rec => x_trolin_rec, p_trolin_tbl => x_trolin_tbl, p_line_id => rec_item_move_imp_line.line_id); IF l_mo_debug_falg = 'Y' THEN--输出调试信息 put_log('得到某一物料搬运单行的状态:' || x_return_status); END IF; IF x_return_status = fnd_api.G_RET_STS_SUCCESS THEN l_line_count := l_line_count + 1; --此次处理的总条数 /*========================================================== * 30 验证数据有效性 * ---------------------------------------------------------- * 描述：验证客户化表中的数据有效性，验证接口表中的数量是否 * 等于物料搬运单的数量。并验证库存现用量和可用量。 ============================================================*/ IF l_mo_debug_falg = 'Y' THEN--输出调试信息 put_log('----------------开始验证---------------------------'); END IF; --验证数据的有效性,包括是否存在货位，物料可用量等信息 validate_data(x_return_status => x_return_status, x_msg_count => x_msg_count, x_msg_data => x_msg_data, p_trolin_rec => x_trolin_rec, p_line_id => rec_item_move_imp_line.line_id, p_transaction_quantity => rec_item_move_imp_line.transaction_quantity, p_transaction_date => rec_item_move_imp_line.transaction_date); IF x_return_status <> fnd_api.G_RET_STS_SUCCESS THEN put_log('错误信息为:' || x_msg_data); --如果未通过验证,则结束本次循环 GOTO line_end; END IF; IF l_mo_debug_falg = 'Y' THEN --输出调试信息 put_log('---------------验证结束---------------------------'); END IF; /*============================================================= * 40 在MMTT表中插入一条记录 * ------------------------------------------------------------- * 描述：数据验证通过后，给MMTT表中的字段赋值，并 * 在MMTT表中插入一条记录。 ===============================================================*/ IF l_mo_debug_falg = 'Y' THEN--输出调试信息 put_log('---------------插入临时表MMTT表的一条记录开始---------------------------'); END IF; --插入一条MMTT的记录 insert_mmtt_row(x_return_status => x_return_status, x_transaction_temp_id => x_transaction_temp_id, p_trohdr_rec => x_trohdr_rec, p_trohdr_val_rec => x_trohdr_val_rec, p_trolin_tbl => x_trolin_tbl, p_trolin_val_tbl => x_trolin_val_tbl, p_header_id => rec_item_move_imp_line.header_id, p_line_id => rec_item_move_imp_line.line_id, p_transaction_quantity => rec_item_move_imp_line.transaction_quantity, p_transaction_date => rec_item_move_imp_line.transaction_date); IF l_mo_debug_falg = 'Y' THEN--输出调试信息 put_log('---------------插入临时表MMTT表的一条记录：x_return_status:' || x_return_status); put_log('---------------插入临时表MMTT表的一条记录结束---------------------------'); END IF; /*=============================================================== * 50 自动处理物料搬运单 * --------------------------------------------------------------- * 描述：调用API自动处理物料搬运单 ================================================================*/ IF x_return_status = fnd_api.G_RET_STS_SUCCESS THEN IF l_mo_debug_falg = 'Y' THEN--输出调试信息 put_log('---------------自动处理物料搬运单开始---------------------------'); END IF; --处理物料搬运单 process_data(x_return_status => x_return_status, x_msg_count => x_msg_count, x_msg_data => x_msg_data, p_line_id => rec_item_move_imp_line.line_id); IF l_mo_debug_falg = 'Y' THEN--输出调试信息 put_log('---------------x_return_status:' || x_return_status); put_log('---------------x_msg_count:' || x_msg_count); put_log('---------------x_msg_data:' || x_msg_data); put_log('---------------自动处理物料搬运单结束---------------------------'); END IF; ELSE EXIT; put_log('错误信息为:' || x_msg_data); put_log('CUX:物料搬运单事务处理时,发生异常:' || SQLERRM); END IF; --本次循环结束标志 <<line_end>> /*======================================================================== * 60 更新客户化表的状态和错误信息 * ------------------------------------------------------------------------ * 描述：更新客户化表的状态： * process_flag =0,表示为新的记录,需要进行处理。 * process_flag =1,表示正在处理 * process_flag =3,程序处理异常或者错误,并将错误信息赋值给error_message * process_flag =4,表示处理成功。 ========================================================================*/ IF x_return_status = fnd_api.G_RET_STS_SUCCESS THEN --处理成功 l_line_success_count := l_line_success_count + 1; --处理成功的记录 --表示处理成功 UPDATE cux_ediint_item_move_in ceimi SET ceimi.process_id = 4, --表示处理成功 ceimi.error_message = NULL, ceimi.last_update_date = SYSDATE, ceimi.last_updated_by = fnd_global.user_id, ceimi.last_update_login = fnd_global.user_id WHERE ceimi.line_id = rec_item_move_imp_line.line_id; END IF; IF x_return_status = fnd_api.G_RET_STS_UNEXP_ERROR THEN--处理失败 UPDATE cux_ediint_item_move_in ceimi SET ceimi.process_id = 3, --程序出现异常 ceimi.error_message = TRIM(x_msg_data), --错误信息 ceimi.last_update_date = SYSDATE, ceimi.last_updated_by = fnd_global.user_id, ceimi.last_update_login = fnd_global.user_id WHERE ceimi.line_id = rec_item_move_imp_line.line_id; --删除MMTT表中未被成功处理的记录 delete_mmtt_row(x_return_status => x_return_status, p_line_id => rec_item_move_imp_line.line_id, p_transaction_temp_id => x_transaction_temp_id); END IF; END IF; ELSE--上次处理错误的记录被手工处理 l_line_count := l_line_count + 1; --此次处理的总条数 l_line_success_count := l_line_success_count + 1; --处理成功的记录 END IF; --一条物料搬运单行处理结束,进行事务处理的提交 COMMIT; EXCEPTION --物料搬运行出现异常 WHEN OTHERS THEN --如果此条记录出现异常,则回滚到SAVEPOINT cux_item_move_line ROLLBACK TO cux_item_move_line; put_log('处理物料搬运单行时,发生异常:' || SQLERRM); END; --end begin END LOOP; --FOR rec_item_move_imp_line IN cur_item_move_imp_line LOOP ELSE retcode := 1; --如果存在没有成功处理的物料搬运单，则显示警告 put_log('得到物料搬运单头时发生异常:' || x_msg_data); END IF; END LOOP; --FOR rec_item_move_imp_header IN cur_item_move_imp_header LOOP --输出处理的信息(包括成功的信息和错误的信息) IF l_mo_debug_falg = 'Y' THEN--输出调试信息 put_log('总行数:' || l_line_count); put_log('处理成功的总行数:' || l_line_success_count); END IF; /*======================================================================== * 70 输出处理结果 * ------------------------------------------------------------------------ * 描述：处理物料搬运单的处理结果包括： * 1.处理错误的记录 * 2.处理失败的记录及其错误信息 ========================================================================*/ output_move_order_info(p_line_count => l_line_count, p_line_success_count => l_line_success_count); IF (l_line_count <> 0) AND (l_line_count <> l_line_success_count) THEN--如果存在处理错误的信息,则请求显示警告 retcode := 1; --如果存在没有成功处理的物料搬运单，则显示警告 END IF; EXCEPTION WHEN OTHERS THEN ROLLBACK; retcode := 2; put_log('错误信息为:' || x_msg_data); put_log('CUX:物料搬运单事务处理时,发生异常:' || SQLERRM); END main; END CUX_EDIINT_ITEM_MOVE_IN_PKG;

转载于:https://www.cnblogs.com/liuweicong39/archive/2012/11/15/2771240.html

oldbalck

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
API实现自动处理物料搬运单

目的：模拟EBS Form界面的功能，调用标准的API实现自动处理物料搬运单。实现思路：1. 先创建一个客户化的表，这个表中用来保存物料搬运单的一些信息。2. 调用标准的API：inv_loc_wms_pub.create_locator动态创建货位(根据项目、任务和库存三个段来创建)。3. 调用标准的API：inv_quantity_tree_pub.query_quan...
复制链接

扫一扫