API实现分批挑库

通过SQL将数据根据特定的条件进行分组，并把每组的数据进行挑库生成一个物料搬运单。

 

--分批测试挑库DEMO

DECLARE

p_attribute6 VARCHAR2(240) := NULL;

p_attribute10 VARCHAR2(240) := NULL;

x_return_status VARCHAR2(1);

x_msg_count NUMBER;

x_msg_data VARCHAR2(4000);

l_delivery_detail_tab wsh_util_core.id_tab_type;

x_del_rows wsh_util_core.id_tab_type;

v_msg_index_out NUMBER;

l_action_prms wsh_glbl_var_strct_grp.dd_action_parameters_rec_type;

l_action_out_rec wsh_glbl_var_strct_grp.dd_action_out_rec_type;

i NUMBER := 0;

 

--获取本次所有的提货地址和收货地址

CURSOR cur1 IS

SELECT DISTINCT wdd.attribute6,

wdd.attribute10

FROM wsh_delivery_details wdd

WHERE wdd.split_from_delivery_detail_id = 1594660

AND wdd.released_status = 'R';

 

--根据收货地址和提货地址获取id， 同时生成一个批

CURSOR cur2(p_attribute6 IN VARCHAR2,

p_attribute10 IN VARCHAR2) IS

SELECT wdd.delivery_detail_id,

wdd.attribute6,

wdd.attribute10

FROM wsh_delivery_details wdd

WHERE wdd.split_from_delivery_detail_id = 1594660

AND wdd.released_status = 'R'

AND wdd.attribute6 = p_attribute6

AND wdd.attribute10 = p_attribute10;

 

BEGIN

--模拟登陆

BEGIN

fnd_global.apps_initialize(user_id => 16430,

resp_id => 54237,

resp_appl_id => 20003);

mo_global.set_policy_context(p_access_mode => 'S',

p_org_id => 662);

mo_global.init('CUX');

END;

 

l_action_prms.phase := 1;

l_action_prms.caller := 'WSH_PUB';

l_action_prms.action_code := 'PICK-RELEASE';

 

FOR rec1 IN cur1 LOOP

 

FOR rec2 IN cur2(rec1.attribute6,

rec1.attribute10) LOOP

i := i + 1;

l_delivery_detail_tab(i) := rec2.delivery_detail_id;

dbms_output.put_line('l_delivery_detail_tab(' || i || ')' ||

l_delivery_detail_tab(i));

 

END LOOP;

 

fnd_msg_pub.initialize;

wsh_interface_grp.delivery_detail_action(p_api_version_number => 1.0,

p_init_msg_list => fnd_api.g_false,

p_commit => fnd_api.g_false,

x_return_status => x_return_status,

x_msg_count => x_msg_count,

x_msg_data => x_msg_data,

p_detail_id_tab => l_delivery_detail_tab,

p_action_prms => l_action_prms,

x_action_out_rec => l_action_out_rec);

 

dbms_output.put_line(x_return_status);

IF x_msg_count > 0 THEN

FOR v_index IN 1 .. x_msg_count LOOP

fnd_msg_pub.get(p_msg_index => v_index,

p_encoded => 'F',

p_data => x_msg_data,

p_msg_index_out => v_msg_index_out);

dbms_output.put_line(x_msg_data);

END LOOP;

 

END IF;

--循环一次之后清空数据

l_delivery_detail_tab.delete;

--重置i的值

i := 0;

 

END LOOP;

 

END;