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