x_return_status OUT NOCOPY VARCHAR2, x_msg_data OUT NOCOPY VARCHAR2) IS l_exists NUMBER; l_primary_item_id NUMBER; BEGIN
--Validate oraganization BEGIN
SELECT organization_id
INTO g_wmti_iface.organization_id FROM org_organization_definitions o
WHERE organization_id = p_organization_id;
g_wmti_iface.organization_id := p_organization_id; EXCEPTION
WHEN OTHERS THEN
x_return_status := fnd_api.g_ret_sts_error;
x_msg_data := '非法的库存组织:' || p_organization_id; RETURN; END;
--Validate dj_name BEGIN
SELECT t.wip_entity_id, t.primary_item_id
INTO g_wmti_iface.wip_entity_id, l_primary_item_id FROM wip_entities t
WHERE t.wip_entity_id = p_wip_entity_id AND organization_id = p_organization_id; EXCEPTION
WHEN OTHERS THEN
x_return_status := fnd_api.g_ret_sts_error;
x_msg_data := 'Invalid wip entity:' || p_wip_entity_id; RETURN; END;
--Validate wip entity must have ASSEMBLY IF l_primary_item_id IS NULL THEN
x_return_status := fnd_api.g_ret_sts_error; x_msg_data := 'Wip entity assembly is null:' || p_wip_entity_id; RETURN; END IF;
--Validate Transaction date must after release date and in open inv period IF check_txn_date_in_period(p_transaction_date, p_organization_id) = 'Y' THEN
g_wmti_iface.transaction_date := p_transaction_date; ELSE
x_return_status := fnd_api.g_ret_sts_error; x_msg_data := '非法的事务处理日期:' ||
to_char(p_transaction_date,
'yyyy-mm-dd hh24:mi:ss'); RETURN; END IF;
--Validate DJ status must be released SELECT COUNT(1) INTO l_exists
FROM wip_discrete_jobs
WHERE wip_entity_id = g_wmti_iface.wip_entity_id AND organization_id = p_organization_id AND status_type = '3'; IF l_exists = 0 THEN
x_return_status := fnd_api.g_ret_sts_error;
x_msg_data := 'DJ status must be released.'; RETURN; END IF;
--Validate quantity
IF p_complete_qty IS NULL OR p_complete_qty <= 0 THEN
x_return_status := fnd_api.g_ret_sts_error;
x_msg_data := '非法的事务处理数量.:' || p_complete_qty; RETURN;
ELSE
g_wmti_iface.transaction_quantity := p_complete_qty; END IF; --
--Validate UOM SELECT COUNT(1) INTO l_exists
FROM mtl_units_of_measure_vl WHERE uom_code = p_uom; IF l_exists = 0 THEN
x_return_status := fnd_api.g_ret_sts_error; x_msg_data := '非法的单位:' || p_uom; RETURN; ELSE
g_wmti_iface.transaction_uom := p_uom; END IF;
END validate_wip_complete;
PROCEDURE ins_move_txn_iface(p_txn_type IN VARCHAR2, p_is_overcome IN NUMBER,
p_is_max_operation_seq_num IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2, x_msg_data OUT NOCOPY VARCHAR2) IS
l_fm_operation_seq_num NUMBER; l_to_operation_seq_num NUMBER; l_transaction_type NUMBER; l_fm_step NUMBER; l_to_step NUMBER; BEGIN
SELECT wip_transactions_s.nextval INTO g_wmti_iface.transaction_id FROM dual;
--get seq num max(min) BEGIN
SELECT MAX(operation_seq_num), MIN(operation_seq_num) INTO l_to_operation_seq_num, l_fm_operation_seq_num FROM wip_operations
WHERE wip_entity_id = g_wmti_iface.wip_entity_id
AND organization_id = g_wmti_iface.organization_id; EXCEPTION
WHEN OTHERS THEN
x_return_status := fnd_api.g_ret_sts_error; x_msg_data := '工单已完成或者未发放！.'; RETURN; END;
IF p_txn_type = 'COMPLETE' THEN
IF p_is_max_operation_seq_num = 'Y' THEN l_transaction_type := 2; --COMPLETE ELSE
l_transaction_type := 1; END IF;
l_fm_step := 1; --Queue l_to_step := 3; --To Move
ELSIF p_txn_type = 'RETURN' THEN
l_fm_operation_seq_num := g_wmti_iface.to_operation_seq_num; l_to_operation_seq_num := g_wmti_iface.fm_operation_seq_num; l_transaction_type := 3; --RETURN l_fm_step := 3; --To Move l_to_step := 1; --Queue ELSE
x_return_status := fnd_api.g_ret_sts_error; x_msg_data := 'Invalid arguments for ins_move_txn_iface.p_txn_type'; END IF;
g_wmti_iface.last_update_date := SYSDATE;
g_wmti_iface.last_updated_by := fnd_global.user_id;
g_wmti_iface.creation_date := SYSDATE;
g_wmti_iface.created_by := fnd_global.user_id; g_wmti_iface.last_update_login := -1;
g_wmti_iface.last_updated_by_name := g_user_name; g_wmti_iface.created_by_name := g_user_name; g_wmti_iface.process_phase := 1; g_wmti_iface.process_status := 2;
g_wmti_iface.transaction_type := l_transaction_type;
g_wmti_iface.fm_operation_seq_num := l_fm_operation_seq_num; g_wmti_iface.fm_intraoperation_step_type := l_fm_step; g_wmti_iface.to_intraoperation_step_type := l_to_step;
g_wmti_iface.to_operation_seq_num := l_to_operation_seq_num; g_wmti_iface.source_code := 'Etrace'; g_wmti_iface.source_line_id := -1;
IF nvl(p_is_overcome, 0) = 0 THEN
INSERT INTO wip_move_txn_interface VALUES g_wmti_iface; ELSE
--超量完工
g_wmti_iface.overcompletion_transaction_qty := p_is_overcome; INSERT INTO wip_move_txn_interface VALUES g_wmti_iface; END IF;
EXCEPTION
WHEN OTHERS THEN
x_return_status := fnd_api.g_ret_sts_unexp_error;
x_msg_data := 'cux_wip_dispatch_utl.ins_move_txn_iface Unhandled exception:' ||
SQLERRM; END ins_move_txn_iface;
PROCEDURE handle_mmt_iface(o_success_flag OUT VARCHAR2, o_error_mssg OUT VARCHAR2, p_table IN VARCHAR2) IS l_result NUMBER;
l_return_status VARCHAR2(2); l_msg_count NUMBER;
l_msg_data VARCHAR2(2000); l_trans_count NUMBER; BEGIN
o_success_flag := 'Y'; o_error_mssg := NULL;
--Call api process transaction
l_result := inv_txn_manager_pub.process_transactions(p_api_version => 1.0,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data, --error message
x_trans_count => l_trans_count, --record processed
p_table => 1, --interface
p_header_id => g_mmt_iface.transaction_header_id);
IF l_result = 0 THEN
o_success_flag := 'Y'; o_error_mssg := NULL; ELSE
o_success_flag := 'N'; IF p_table = '1' THEN
--retreve error from interface
FOR i IN (SELECT DISTINCT error_explanation FROM mtl_transactions_interface WHERE transaction_header_id =
g_mmt_iface.transaction_header_id) LOOP o_error_mssg := o_error_mssg || '.Error:' || i.error_explanation; END LOOP; ELSE
o_error_mssg := l_msg_data;
END IF; END IF; EXCEPTION
WHEN OTHERS THEN
o_success_flag := 'N';
o_error_mssg := 'Occur Exception:' || SQLERRM; END handle_mmt_iface;
PROCEDURE handle_wmti_iface(x_return_status OUT NOCOPY VARCHAR2, x_msg_data OUT NOCOPY VARCHAR2) IS BEGIN
--call api process wmti
wip_movproc_pub.processinterface(p_txn_id => g_wmti_iface.transaction_id,
p_do_backflush => fnd_api.g_true, p_commit => fnd_api.g_false, x_returnstatus => x_return_status, x_errormsg => x_msg_data);
dbms_output.put_line('x_return_status:' || x_return_status); EXCEPTION
WHEN OTHERS THEN
x_return_status := fnd_api.g_ret_sts_unexp_error;
x_msg_data := 'cux_wip_dispatch_utl.handle_wmti_iface Unhandled exception:' ||
SQLERRM; END handle_wmti_iface;
PROCEDURE process_wip_complete(p_wip_entity_id IN NUMBER, p_organization_id IN NUMBER, p_transaction_date IN DATE, p_complete_qty IN NUMBER, p_uom IN VARCHAR2,
p_is_max_operation_seq_num IN VARCHAR2, p_is_overcome IN NUMBER, p_sourcr_line_id IN NUMBER, x_return_status OUT NOCOPY VARCHAR2,
x_msg_data OUT NOCOPY VARCHAR2) IS BEGIN
x_return_status := fnd_api.g_ret_sts_success; --setp 1 : validate data
validate_wip_complete(p_wip_entity_id => p_wip_entity_id, p_organization_id => p_organization_id, p_transaction_date => p_transaction_date, p_complete_qty => p_complete_qty, p_uom => p_uom,
x_return_status => x_return_status, x_msg_data => x_msg_data); IF x_return_status <> fnd_api.g_ret_sts_success THEN RETURN; END IF;
g_wmti_iface.source_line_id := p_sourcr_line_id;
--setp 2 : insert wip_move_txn_interface interface
ins_move_txn_iface(p_txn_type => 'COMPLETE', p_is_overcome => p_is_overcome, p_is_max_operation_seq_num => p_is_max_operation_seq_num,
x_return_status => x_return_status, x_msg_data => x_msg_data); IF x_return_status <> fnd_api.g_ret_sts_success THEN RETURN; END IF;
--setp 3 : call online process wip_move_txn_interface handle_wmti_iface(x_return_status, x_msg_data);
IF x_return_status <> fnd_api.g_ret_sts_success THEN RETURN;
END IF;
EXCEPTION
WHEN OTHERS THEN
x_return_status := fnd_api.g_ret_sts_unexp_error;
x_msg_data := 'cux_wip_dispatch_utl.process_wip_complete Unhandled exception:' ||
SQLERRM; END process_wip_complete;
END cux_wip_dispatch_utl;
未决与已结问题
未决问题
序号 问题 解决方案 负责人 目标日期 实际日期 1 2
已结问题
序号 问题 解决方案 负责人 目标日期 实际日期