oracle ebs工单入库,Oracle EBS 工单工序移动接口开发

FUNCTIONprocess_wip_move(p_init_msg_list IN VARCHAR2

DEFAULT fnd_api.g_false,

p_commit IN VARCHAR2 DEFAULT fnd_api.g_false,

x_return_status

OUT NOCOPYVARCHAR2,

x_msg_count OUT NOCOPY NUMBER,

x_msg_data OUT NOCOPY VARCHAR2,

p_parameter1 IN VARCHAR2) RETURN VARCHAR2 IS

l_api_name CONSTANT VARCHAR2(30) :='process_wip_move';

l_savepoint_name CONSTANT VARCHAR2(30) :='sp_process_wip_move';

l_return_status VARCHAR2(1);

l_msg_data VARCHAR2(2000);

l_rec_move_txn wip_move_txn_interface%ROWTYPE;

l_group_id NUMBER;

l_txn_id NUMBER;

l_available NUMBER;

l_trn_intermtl_transactions_interface%ROWTYPE;

v_transaction_header_id NUMBER DEFAULTNULL;

v_transaction_action_id NUMBER DEFAULTNULL;

v_quantity_flag NUMBER DEFAULT 1;

v_return_status VARCHAR2(3) DEFAULT NULL;

v_return_message VARCHAR2(6000) DEFAULTNULL;

v_on_hand_enough VARCHAR2(1) DEFAULT 'N';

x_over_qty NUMBER;

l_wip_entity_id NUMBER := 265057;

l_organization_id NUMBER := 353;

l_transaction_quantity NUMBER := 3;

--

v_lot_number VARCHAR2(15) := 'zhw007';*\

BEGIN

-- start activity to create savepoint,check compatibility

-- and initialize message list, includedebug message hint to enter

api

x_return_status

:=cux_api.start_activity(p_pkg_name =>

g_pkg_name,

p_api_name => l_api_name,

p_savepoint_name=>

l_savepoint_name,

p_init_msg_list =>p_init_msg_list);

IF (x_return_status =fnd_api.g_ret_sts_unexp_error) THEN

RAISE fnd_api.g_exc_unexpected_error;

ELSIF (x_return_status =fnd_api.g_ret_sts_error) THEN

RAISE fnd_api.g_exc_error;

END IF;

-- API body

-- logging parameters

IF l_debug = 'Y' THEN

cux_log.debug('p_parameter1 : ' ||p_parameter1);

END IF;

-- todo

SELECT wip_transactions_s.nextval INTOl_group_id FROM dual;

--loop

l_txn_id := NULL;

l_rec_move_txn := NULL;

SELECT wip_transactions_s.nextval INTOl_txn_id FROM dual;

l_rec_move_txn.transaction_id :=l_txn_id;

l_rec_move_txn.group_id := l_group_id;

l_rec_move_txn.process_phase := 1;

l_rec_move_txn.process_status := 2;--runing

l_rec_move_txn.created_by := fnd_global.user_id;

l_rec_move_txn.creation_date := SYSDATE;

l_rec_move_txn.last_updated_by := fnd_global.user_id;

l_rec_move_txn.last_update_date := SYSDATE;

l_rec_move_txn.last_update_login :=fnd_global.login_id;

l_rec_move_txn.wip_entity_id :=l_wip_entity_id;

--l_rec_move_txn.wip_entity_name :=rec_grp.wo_no;

l_rec_move_txn.organization_id := l_organization_id;

l_rec_move_txn.transaction_date := SYSDATE;

l_rec_move_txn.transaction_quantity :=

abs(l_transaction_quantity);--不管怎样，事务处理数量始终取正值

l_rec_move_txn.transaction_uom := '个';

IF l_transaction_quantity > 0 THEN

--移动数量大于0时，正向移动，由排队到移动工序号由最小走到最大

l_rec_move_txn.transaction_type := 1;--1.normal move;2.combination

move or completion/return transaction

SELECT MIN(wo.operation_seq_num),MAX(wo.operation_seq_num)

INTOl_rec_move_txn.fm_operation_seq_num,

l_rec_move_txn.to_operation_seq_num

FROM wip_operations wo

WHERE wo.wip_entity_id =l_wip_entity_id

AND wo.organization_id =l_organization_id;

l_rec_move_txn.fm_intraoperation_step_type

:= 1; --排队

l_rec_move_txn.to_intraoperation_step_type

:= 3; --移动

l_available :=get_available_to_move_qty(p_wip_entity_id =>

l_wip_entity_id,

p_opr_seq_num => l_rec_move_txn.fm_operation_seq_num,

p_organization_id

=> l_organization_id,

p_intraopr_step => 1);

IF l_available

--如果可移动数量小于移动数量，则启用超量移动

l_rec_move_txn.overcompletion_transaction_qty

:= l_transaction_quantity-

l_available;

l_rec_move_txn.overcompletion_primary_qty := l_transaction_quantity -

l_available;

x_over_qty :=l_transaction_quantity - l_available;

END IF; --IF l_available

ELSE

--移动数量小于0时，反向移动，由移动到排队工序号由最大走到最小

l_rec_move_txn.transaction_type := 1;--1.normal move;2.combination

move or completion/return transaction

SELECT MAX(wo.operation_seq_num),MIN(wo.operation_seq_num)

INTO l_rec_move_txn.fm_operation_seq_num,

l_rec_move_txn.to_operation_seq_num

FROM wip_operations wo

WHERE wo.wip_entity_id =l_wip_entity_id

AND wo.organization_id =l_organization_id;

l_rec_move_txn.fm_intraoperation_step_type:= 3; --移动

l_rec_move_txn.to_intraoperation_step_type

:= 1; --排队

END IF; -- IF p_move_qty > 0 THEN

l_rec_move_txn.last_updated_by_name :=fnd_global.user_name;

l_rec_move_txn.created_by_name := fnd_global.user_name;

--以下两字段视业务逻辑取数

l_rec_move_txn.source_code := 'CXY_MES_WIP_MOVE_TEST';

l_rec_move_txn.source_line_id := 265057;

BEGIN

INSERT INTO wip_move_txn_interfaceVALUES l_rec_move_txn;

EXCEPTION

WHEN OTHERS THEN

cux_conc_utl.log_msg('insert intowip_move_txn_interface error : '

||

SQLERRM);

RAISE fnd_api.g_exc_error;

END;

wip_movproc_pub.processinterface(p_txn_id => l_txn_id,

p_commit => 'F',

x_returnstatus

=> l_return_status,

x_errormsg => l_msg_data);

--END IF;

-- API end body

-- end activity, include debug messagehint to exit api

x_return_status :=cux_api.end_activity(p_pkg_name =>g_pkg_name,

p_api_name => l_api_name,

p_commit => p_commit,

x_msg_count

=> x_msg_count,

x_msg_data => x_msg_data);

RETURN l_return_status || l_msg_data;

EXCEPTION

WHEN fnd_api.g_exc_error THEN

x_return_status

:=cux_api.handle_exceptions(p_pkg_name =>

g_pkg_name,

p_api_name => l_api_name,

p_savepoint_name

=> l_savepoint_name,

p_exc_name =>cux_api.g_exc_name_error,

x_msg_count =>x_msg_count,

x_msg_data =>x_msg_data);

WHEN fnd_api.g_exc_unexpected_error THEN

x_return_status

:=cux_api.handle_exceptions(p_pkg_name =>

g_pkg_name,

p_api_name => l_api_name,

p_savepoint_name=>

l_savepoint_name,

p_exc_name =>cux_api.g_exc_name_unexp,

x_msg_count =>x_msg_count,

x_msg_data =>x_msg_data);

WHEN OTHERS THEN

x_return_status

:=cux_api.handle_exceptions(p_pkg_name =>

g_pkg_name,

p_api_name => l_api_name,

p_savepoint_name

=> l_savepoint_name,

p_exc_name =>cux_api.g_exc_name_others,

x_msg_count => x_msg_count,

x_msg_data =>x_msg_data);

END

process_wip_move;