时间:2021-07-01 10:21:17 帮助过:3人阅读
- --生产批创建事物处理
- --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;
- /
Oracle EBS OPM 生产批创建事务处理
标签:ant wip end creat return const time org fail