--取消生产批
--created by jenrry
SET serveroutput on;
DECLARE
p_batch_header_rec gme_batch_header%ROWTYPE;
x_batch_header_rec gme_batch_header%ROWTYPE;
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);
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 Cancel_batch', l_api_name);
END display_messages;
BEGIN
DBMS_OUTPUT.ENABLE (20000);
l_user_name := 'PROCESS_OPS';
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_batch_header_rec.organization_id := 1381;
p_batch_header_rec.batch_id := 217707;
gme_api_pub.cancel_batch
(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 => 'PR1',
p_batch_header_rec => p_batch_header_rec,
x_batch_header_rec => x_batch_header_rec
);
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)
);
DBMS_OUTPUT.put_line (SUBSTR ( 'x_batch_header_rec.batch_id = '
|| TO_CHAR (x_batch_header_rec.batch_id),
1,
255
)
);
DBMS_OUTPUT.put_line (SUBSTR ( 'x_batch_header_rec.batch_status = '
|| TO_CHAR (x_batch_header_rec.batch_status),
1,
255
)
);
DBMS_OUTPUT.put_line (SUBSTR ( 'x_batch_header_rec.batch_no = '
|| TO_CHAR (x_batch_header_rec.batch_no),
1,
255
)
);
DBMS_OUTPUT.put_line (SUBSTR ( 'x_batch_header_rec.LAST_UPDATE_DATE = '
|| TO_CHAR
(x_batch_header_rec.last_update_date),
1,
255
)
);
DBMS_OUTPUT.put_line (SUBSTR ( 'x_batch_header_rec.LAST_UPDATED_BY = '
|| TO_CHAR
(x_batch_header_rec.last_updated_by),
1,
255
)
);
DBMS_OUTPUT.put_line (SUBSTR ( 'x_batch_header_rec.CREATION_DATE = '
|| TO_CHAR (x_batch_header_rec.creation_date),
1,
255
)
);
DBMS_OUTPUT.put_line (SUBSTR ( 'x_batch_header_rec.CREATED_BY = '
|| TO_CHAR (x_batch_header_rec.created_by),
1,
255
)
);
DBMS_OUTPUT.put_line
(SUBSTR ( 'x_batch_header_rec.LAST_UPDATE_LOGIN = '
|| TO_CHAR
(x_batch_header_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;
/