oracle成批事务处理,Oracle EBS OPM 生产批创建事务处理
--生产批创建事物处理
--created by jenrry
DECLARE
p_mmti_rec mtl_transactions_interface%ROWTYPE;
p_mmli_tbl gme_common_pvt.mtl_trans_lots_inter_tbl;
x_return_status VARCHAR2 (2000);
p_validation_level NUMBER;
p_init_msg_list VARCHAR2 (2000);
p_commit VARCHAR2 (2000);
x_message_count NUMBER;
x_message_list VARCHAR2 (2000);
l_msg_count NUMBER;
l_msg_data VARCHAR2 (2000);
x_mmt_rec mtl_material_transactions%ROWTYPE;
x_mmln_tbl gme_common_pvt.mtl_trans_lots_num_tbl;
l_txn_count NUMBER;
l_count_t NUMBER;
l_count_i NUMBER;
setup_failure EXCEPTION;
p_org_code VARCHAR2 (3) := ‘PR1‘;
g_debug VARCHAR2 (5) := fnd_profile.VALUE (‘AFLOG_LEVEL‘);
l_user_name VARCHAR2 (80);
l_user_id NUMBER;
CURSOR get_user_id (v_user_name IN VARCHAR2)
IS
SELECT user_id
FROM fnd_user
WHERE user_name = v_user_name;
PROCEDURE display_messages (p_msg_count IN NUMBER)
IS
MESSAGE VARCHAR2 (2000);
dummy NUMBER;
l_api_name CONSTANT VARCHAR2 (30) := ‘DISPLAY_MESSAGES‘;
BEGIN
FOR i IN 1 .. p_msg_count
LOOP
fnd_msg_pub.get (p_msg_index => i,
p_data => MESSAGE,
p_encoded => ‘F‘,
p_msg_index_out => dummy
);
DBMS_OUTPUT.put_line (‘Message ‘ || TO_CHAR (i) || ‘ ‘ || MESSAGE);
END LOOP;
EXCEPTION
WHEN OTHERS
THEN
fnd_msg_pub.add_exc_msg (‘wrapper for Create_Material_txn‘, l_api_name);
END display_messages;
BEGIN
DBMS_OUTPUT.ENABLE (20000);
l_user_name := ‘SYSADMIN‘;
OPEN get_user_id (l_user_name);
FETCH get_user_id
INTO l_user_id;
IF get_user_id%NOTFOUND
THEN
DBMS_OUTPUT.put_line (‘Invalid User ‘ || l_user_name);
CLOSE get_user_id;
RAISE NO_DATA_FOUND;
END IF;
CLOSE get_user_id;
fnd_profile.initialize (l_user_id);
fnd_global.apps_initialize (user_id => l_user_id,
resp_id => NULL,
resp_appl_id => NULL
);
x_return_status := fnd_api.g_ret_sts_success;
fnd_msg_pub.initialize;
gme_common_pvt.g_error_count := 0;
gme_common_pvt.set_timestamp;
gme_common_pvt.g_move_to_temp := fnd_api.g_false;
p_mmti_rec.organization_id := 1199;
p_mmti_rec.source_code := ‘OPM‘;
p_mmti_rec.source_header_id := 147;
p_mmti_rec.transaction_source_id := 414962;
p_mmti_rec.trx_source_line_id := 576266;
--p_mmti_rec.last_updated_by := gme_common_pvt.g_user_ident;
--p_mmti_rec.last_update_login := gme_common_pvt.g_user_ident ;
--p_mmti_rec.last_update_date := gme_common_pvt.g_timestamp ;
--p_mmti_rec.creation_date := gme_common_pvt.g_timestamp ;
--p_mmti_rec.created_by := gme_common_pvt.g_user_ident;
p_mmti_rec.inventory_item_id := 234657;
p_mmti_rec.revision := NULL;
p_mmti_rec.organization_id := 1199;
p_mmti_rec.transaction_date := SYSDATE;
p_mmti_rec.transaction_type_id := 44/*gme_common_pvt.g_ing_issue*/;
p_mmti_rec.transaction_action_id := gme_common_pvt.g_ing_issue_txn_action;
p_mmti_rec.transaction_quantity := 20;
--p_mmti_rec.primary_quantity :=
p_mmti_rec.transaction_uom := ‘袋‘;
p_mmti_rec.subinventory_code := ‘CWK‘;
p_mmti_rec.locator_id := 983;
p_mmti_rec.transaction_source_type_id := 5;
--p_mmti_rec.transaction_source_name :=
p_mmti_rec.wip_entity_type := 10;
--p_mmti_rec.reason_id :=
p_mmli_tbl(1).last_update_date := gme_common_pvt.g_timestamp ;
p_mmli_tbl(1).last_updated_by := gme_common_pvt.g_user_ident ;
p_mmli_tbl(1).creation_date := gme_common_pvt.g_timestamp ;
p_mmli_tbl(1).created_by := gme_common_pvt.g_user_ident ;
p_mmli_tbl(1).lot_number := ‘L20170814‘ ;
--p_mmli_tbl(1).parent_lot_number := ‘GRNEWPARENTAPI‘ ;
p_mmli_tbl(1).transaction_quantity := 20 ;
gme_api_pub.create_material_txn
(p_api_version => 2.0,
p_validation_level => gme_common_pvt.g_max_errors,
p_init_msg_list => fnd_api.g_false,
p_commit => fnd_api.g_true,
x_message_count => x_message_count,
x_message_list => x_message_list,
x_return_status => x_return_status,
p_org_code => ‘F06‘,
p_mmti_rec => p_mmti_rec,
p_mmli_tbl => p_mmli_tbl,
p_batch_no => NULL,
p_line_no => NULL,
p_line_type => NULL,
p_create_lot => fnd_api.g_true,
p_generate_lot => NULL,
p_generate_parent_lot => NULL,
x_mmt_rec => x_mmt_rec,
x_mmln_tbl => x_mmln_tbl
);
DBMS_OUTPUT.put_line ( ‘msg_count from process trxns=‘
|| TO_CHAR (l_msg_count)
);
IF l_msg_count > 1
THEN
display_messages (l_msg_count);
END IF;
DBMS_OUTPUT.put_line ( ‘after process transactions x_return_status=‘
|| TO_CHAR (x_return_status)
);
-- Output the results
DBMS_OUTPUT.put_line (SUBSTR ( ‘x_mmt_rec.transaction_id = ‘
|| TO_CHAR (x_mmt_rec.transaction_id),
1,
255
)
);
DBMS_OUTPUT.put_line (SUBSTR ( ‘x_mmt_rec.transaction_type_id = ‘
|| TO_CHAR (x_mmt_rec.transaction_type_id),
1,
255
)
);
DBMS_OUTPUT.put_line (SUBSTR ( ‘x_mmt_rec.transaction_action_id = ‘
|| TO_CHAR (x_mmt_rec.transaction_action_id),
1,
255
)
);
DBMS_OUTPUT.put_line (SUBSTR ( ‘x_mmt_rec.transaction_source_id = ‘
|| TO_CHAR (x_mmt_rec.transaction_source_id),
1,
255
)
);
DBMS_OUTPUT.put_line (SUBSTR ( ‘x_mmt_rec.trx_source_line_id = ‘
|| TO_CHAR (x_mmt_rec.trx_source_line_id),
1,
255
)
);
DBMS_OUTPUT.put_line (SUBSTR ( ‘x_mmt_rec.source_line_id = ‘
|| TO_CHAR (x_mmt_rec.source_line_id),
1,
255
)
);
DBMS_OUTPUT.put_line (SUBSTR ( ‘x_mmt_rec.transaction_quantity = ‘
|| TO_CHAR (x_mmt_rec.transaction_quantity),
1,
255
)
);
DBMS_OUTPUT.put_line (SUBSTR ( ‘x_mmt_rec.transaction_uom = ‘
|| TO_CHAR (x_mmt_rec.transaction_uom),
1,
255
)
);
DBMS_OUTPUT.put_line (SUBSTR ( ‘x_mmt_rec.transaction_date = ‘
|| TO_CHAR (x_mmt_rec.transaction_date),
1,
255
)
);
DBMS_OUTPUT.put_line (SUBSTR ( ‘x_mmln_tbl.count = ‘
|| TO_CHAR (x_mmln_tbl.COUNT),
1,
255
)
);
IF (x_mmln_tbl.COUNT > 0)
THEN
FOR i IN 1 .. x_mmln_tbl.COUNT
LOOP
DBMS_OUTPUT.put_line (SUBSTR ( ‘x_mmln_tbl((i).lot_number = ‘
|| TO_CHAR (x_mmln_tbl.COUNT),
1,
255
)
);
END LOOP;
END IF;
DBMS_OUTPUT.put_line (SUBSTR ( ‘x_mmt_rec.LAST_UPDATE_DATE = ‘
|| TO_CHAR (x_mmt_rec.last_update_date),
1,
255
)
);
DBMS_OUTPUT.put_line (SUBSTR ( ‘x_mmt_rec.LAST_UPDATED_BY = ‘
|| TO_CHAR (x_mmt_rec.last_updated_by),
1,
255
)
);
DBMS_OUTPUT.put_line (SUBSTR ( ‘x_mmt_rec.CREATION_DATE = ‘
|| TO_CHAR (x_mmt_rec.creation_date),
1,
255
)
);
DBMS_OUTPUT.put_line (SUBSTR ( ‘x_mmt_rec.CREATED_BY = ‘
|| TO_CHAR (x_mmt_rec.created_by),
1,
255
)
);
DBMS_OUTPUT.put_line (SUBSTR ( ‘x_mmt_rec.LAST_UPDATE_LOGIN = ‘
|| TO_CHAR (x_mmt_rec.last_update_login),
1,
255
)
);
DBMS_OUTPUT.put_line (SUBSTR (‘x_return_status = ‘ || x_return_status,
1,
255
)
);
DBMS_OUTPUT.put_line (‘x_message_count = ‘ || TO_CHAR (x_message_count));
DBMS_OUTPUT.put_line (SUBSTR (‘x_message_list = ‘ || x_message_list, 1,
255)
);
gme_common_pvt.count_and_get (x_count => x_message_count,
p_encoded => fnd_api.g_false,
x_data => x_message_list
);
IF x_message_count > 1
THEN
display_messages (x_message_count);
END IF;
EXCEPTION
WHEN setup_failure
THEN
DBMS_OUTPUT.put_line (‘ in setup failure‘);
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (SUBSTR ( ‘Error ‘
|| TO_CHAR (SQLCODE)
|| ‘: ‘
|| SQLERRM,
1,
255
)
);
RAISE;
END;
/
原文：https://www.cnblogs.com/jenrry/p/10021108.html