oracle的opm,Oracle EBS OPM 发放生产批

--发放生产批

--created by jenrry

DECLARE

x_return_status VARCHAR2 (1);

l_exception_material_tbl gme_common_pvt.exceptions_tab;

l_batch_header_rec gme_batch_header%ROWTYPE;

l_in_batch_header_rec gme_batch_header%ROWTYPE;

l_org_code VARCHAR2 (3);

l_ignore_exception VARCHAR2 (1);

l_val_ff VARCHAR2 (1);

x_message_count NUMBER;

x_message_list VARCHAR2 (2000);

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 Release_Batch ‘, 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

);

l_in_batch_header_rec.batch_no := ‘20170731‘;

l_org_code := ‘B01‘;

l_in_batch_header_rec.actual_start_date :=

TO_DATE (‘07-31-2017 08:00:00‘, ‘MM-DD-YYYY HH24:MI:SS‘);

l_ignore_exception := fnd_api.g_true;

l_val_ff := fnd_api.g_true;

gme_api_pub.release_batch

(p_api_version => 2.0,

p_validation_level => gme_common_pvt.g_max_errors,

p_init_msg_list => fnd_api.g_true,

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_batch_header_rec => l_in_batch_header_rec,

p_org_code => l_org_code,

p_ignore_exception => l_ignore_exception,

p_validate_flexfields => l_val_ff,

x_batch_header_rec => l_batch_header_rec,

x_exception_material_tbl => l_exception_material_tbl

);

DBMS_OUTPUT.put_line (SUBSTR (‘x_return_status = ‘ || x_return_status,

1,

255

)

);

DBMS_OUTPUT.put_line ( ‘l_batch_header_rec.batch_id = ‘

|| TO_CHAR (l_batch_header_rec.batch_id)

);

DBMS_OUTPUT.put_line ( ‘l_batch_header_rec.organization_id = ‘

|| TO_CHAR (l_batch_header_rec.organization_id)

);

DBMS_OUTPUT.put_line (SUBSTR ( ‘l_batch_header_rec.batch_no = ‘

|| l_batch_header_rec.batch_no,

1,

255

)

);

DBMS_OUTPUT.put_line ( ‘l_batch_header_rec.batch_type = ‘

|| TO_CHAR (l_batch_header_rec.batch_type)

);

DBMS_OUTPUT.put_line ( ‘l_batch_header_rec.plan_start_date = ‘

|| TO_CHAR (l_batch_header_rec.plan_start_date,

‘DD-MON-YYYY HH24:MI:SS‘

)

);

DBMS_OUTPUT.put_line ( ‘l_batch_header_rec.plan_cmplt_date = ‘

|| TO_CHAR (l_batch_header_rec.plan_cmplt_date,

‘DD-MON-YYYY HH24:MI:SS‘

)

);

DBMS_OUTPUT.put_line ( ‘l_batch_header_rec.actual_start_date = ‘

|| TO_CHAR (l_batch_header_rec.actual_start_date,

‘DD-MON-YYYY HH24:MI:SS‘

)

);

DBMS_OUTPUT.put_line ( ‘l_batch_header_rec.actual_cmplt_date = ‘

|| TO_CHAR (l_batch_header_rec.actual_cmplt_date,

‘DD-MON-YYYY HH24:MI:SS‘

)

);

DBMS_OUTPUT.put_line ( ‘l_batch_header_rec.batch_status = ‘

|| TO_CHAR (l_batch_header_rec.batch_status)

);

DBMS_OUTPUT.put_line ( ‘l_batch_header_rec.last_update_date = ‘

|| TO_CHAR (l_batch_header_rec.last_update_date,

‘DD-MON-YYYY HH24:MI:SS‘

)

);

DBMS_OUTPUT.put_line ( ‘l_batch_header_rec.last_updated_by = ‘

|| TO_CHAR (l_batch_header_rec.last_updated_by)

);

DBMS_OUTPUT.put_line ( ‘l_batch_header_rec.creation_date = ‘

|| TO_CHAR (l_batch_header_rec.creation_date,

‘DD-MON-YYYY HH24:MI:SS‘

)

);

DBMS_OUTPUT.put_line ( ‘l_batch_header_rec.created_by = ‘

|| TO_CHAR (l_batch_header_rec.created_by)

);

DBMS_OUTPUT.put_line ( ‘l_batch_header_rec.last_update_login = ‘

|| TO_CHAR (l_batch_header_rec.last_update_login)

);

FOR i IN 1 .. l_exception_material_tbl.COUNT

LOOP

DBMS_OUTPUT.put_line (‘Record i = ‘ || TO_CHAR (i));

DBMS_OUTPUT.put_line

( ‘l_exception_material_tbl(i).ORGANIZATION_ID = ‘

|| TO_CHAR

(l_exception_material_tbl (i).organization_id

)

);

DBMS_OUTPUT.put_line ( ‘l_exception_material_tbl(i).BATCH_ID = ‘

|| TO_CHAR (l_exception_material_tbl (i).batch_id)

);

DBMS_OUTPUT.put_line

( ‘l_exception_material_tbl(i).MATERIAL_DETAIL_ID = ‘

|| TO_CHAR

(l_exception_material_tbl (i).material_detail_id

)

);

DBMS_OUTPUT.put_line

( ‘l_exception_material_tbl(i).PENDING_MOVE_ORDER_IND = ‘

|| TO_CHAR

(l_exception_material_tbl (i).pending_move_order_ind

)

);

DBMS_OUTPUT.put_line

( ‘l_exception_material_tbl(i).PENDING_RESERVATIONS_IND = ‘

|| TO_CHAR

(l_exception_material_tbl (i).pending_reservations_ind

)

);

DBMS_OUTPUT.put_line

( ‘l_exception_material_tbl(i).TRANSACTED_QTY = ‘

|| TO_CHAR

(l_exception_material_tbl (i).transacted_qty

)

);

DBMS_OUTPUT.put_line ( ‘l_exception_material_tbl(i).EXCEPTION_QTY = ‘

|| TO_CHAR

(l_exception_material_tbl (i).exception_qty

)

);

END LOOP;

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)

);

IF x_message_count > 0

THEN

display_messages (x_message_count);

END IF;

EXCEPTION

WHEN OTHERS

THEN

DBMS_OUTPUT.put_line (‘Error ‘ || TO_CHAR (SQLCODE) || ‘: ‘ || SQLERRM);

RAISE;

END;

/

原文：https://www.cnblogs.com/jenrry/p/10021138.html