自动挑库发放api

最新推荐文章于 2021-04-05 19:47:24 发布

cmn83251

最新推荐文章于 2021-04-05 19:47:24 发布

阅读量195

点赞数

文章标签：数据库

以下为通过2个例子，来说明用2个不同的api接口实现订单自动挑库发放的功能，方法1能够实现多条记录同时操作，较方便，方法2每次只能操作一条记录，且没操作一次提交一个请求，较麻烦；

方法1：允许多行同时挑库发放，此时生成的挑库单号一样

declare
p_init_msg_list    VARCHAR2(30);
p_commit           VARCHAR2(30);
p_validation_level NUMBER;
x_return_status    VARCHAR2(30);
x_msg_count        NUMBER;
x_msg_data         VARCHAR2(30);
p_TabOfDelDets     WSH_DELIVERY_DETAILS_PUB.id_tab_type;
p_action           VARCHAR2(30);
p_delivery_id      NUMBER;
p_delivery_name    VARCHAR2(30);

x_msg_details VARCHAR2(3000);
x_msg_summary VARCHAR2(3000);

detail_record number := 1;

fail_api EXCEPTION;

cursor cur_detail_id is
    select distinct wdd.delivery_detail_id detail_id
      from wsh_delivery_details wdd
     where wdd.source_header_id = 194211;

BEGIN
FND_GLOBAL.APPS_INITIALIZE(user_id      => user_id,
                             resp_id      => resp_id,
                             resp_appl_id => resp_appl_id );
x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;

p_action := 'PICK-RELEASE'; --'ASSIGN';

for rec_detail_id in cur_detail_id loop
    p_TabOfDelDets(detail_record) := rec_detail_id.detail_id;
    dbms_output.put_line(detail_record || ':' ||
                         p_TabOfDelDets(detail_record));
    detail_record := detail_record + 1;
end loop;

WSH_DELIVERY_DETAILS_PUB.Detail_to_Delivery(p_api_version      => 1.0,
                                              p_init_msg_list    => p_init_msg_list,
                                              p_commit           => p_commit,
                                              p_validation_level => p_validation_level,
                                              x_return_status    => x_return_status,
                                              x_msg_count        => x_msg_count,
                                              x_msg_data         => x_msg_data,
                                              p_TabOfDelDets     => p_TabOfDelDets,
                                              p_action           => p_action,
                                              p_delivery_id      => p_delivery_id,
                                              p_delivery_name    => P_delivery_name);
if (x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) then
    raise fail_api;
end if;
dbms_output.put_line('success');
commit;
exception
when fail_api then
    WSH_UTIL_CORE.get_messages('Y',
                               x_msg_summary,
                               x_msg_details,
                               x_msg_count);
    if x_msg_count > 1 then
      x_msg_data := x_msg_summary || x_msg_details;
    else
      x_msg_data := x_msg_summary;
    end if;
    dbms_output.put_line('error:' || x_msg_data);
    rollback;
when others then
    dbms_output.put_line('other error:' || sqlerrm);
    rollback;
END;

方法2：一次只能操作一条记录，且没操作一次提交一个请求，较麻烦

declare
--l_Delivery_detail_Id wsh_picking_batches.Delivery_Detail_Id%TYPE;
l_Batch_Rec     Wsh_Picking_Batches_Pub.Batch_Info_Rec;
l_Batch_Id      Number;
x_Return_Status Varchar2(1);
x_Msg_Count     Number;
x_Msg_Data      Varchar2(3500);

x_msg_details VARCHAR2(3000);
x_msg_summary VARCHAR2(3000);

Api_Failed Exception;
l_Request_Id Number;
p_Error_Msg Varchar2(3500);
Begin
dbms_output.put_line('begin');
FND_GLOBAL.APPS_INITIALIZE(user_id      => user_id,
                             resp_id      => resp_id,
                             resp_appl_id =>resp_appl_id);
l_Batch_Rec.Delivery_detail_Id := 1955083;
--l_Batch_Rec.Autodetail_Pr_Flag      := 'Y';
--l_Batch_Rec.Existing_Rsvs_Only_Flag := 'Y';
---l_Batch_Rec.Backorders_Only_Flag := 'I';
--l_Batch_Rec.Auto_Pick_Confirm_Flag := 'N';
Wsh_Picking_Batches_Pub.Create_Batch(p_Api_Version   => '1.0',
                                       p_Init_Msg_List => Fnd_Api.g_True,
                                       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_Batch_Rec     => l_Batch_Rec,
                                       x_Batch_Id      => l_Batch_Id);
if (x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) then
    raise Api_Failed;
end if;
dbms_output.put_line('create success');
Wsh_Picking_Batches_Pub.Release_Batch(p_Api_Version   => '1.0',
                                        p_Init_Msg_List => Fnd_Api.g_True,
                                        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_Release_Mode => 'Concurrent',--'ONLINE',
                                        p_Batch_Id   => l_Batch_Id,
                                        p_log_level => 0,
                                        x_Request_Id => l_Request_Id);
if (x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) then
    raise Api_Failed;
end if;
dbms_output.put_line('release success');
dbms_output.put_line('end');
commit;
Exception
when Api_Failed then
    WSH_UTIL_CORE.get_messages('Y',
                               x_msg_summary,
                               x_msg_details,
                               x_msg_count);
    if x_msg_count > 1 then
      x_msg_data := x_msg_summary || x_msg_details;
    else
      x_msg_data := x_msg_summary;
    end if;
    dbms_output.put_line(x_return_status);
    dbms_output.put_line(substr(x_Msg_Data, 1, 200));
    dbms_output.put_line(substr(x_Msg_Data, 200, 200));
    rollback;
when others then
    dbms_output.put_line('other exception');
    rollback;
End Pick_Release;