API for Pick Pack
and Ship Confirm in Oracle Apps
In order to call these API’s we only need to pass the
required parameters. It is mandatory that all the required parameters need to
be initialized before they are passed to the called shipping API. The optional
parameters are optional and can be passed as additional information if needed.
The parameters can also be classified as standard parameters
and specific parameters.
Standard parameters are almost common to all the shipping
API’s and the Specific Parameters are specific to a particular shipping API and
are discussed separately for each of the API called in the flow. These standard
and specific parameters can be one of the mandatory or option parameter.
Below example is done on Oracle Vision Operations system
with the hard code values according to the system. While using below code,
values to be passed according to the system you are working on. If any concerns
please refer Oracle metalink.
Flow
Sequence:
Following business flow shows the usage of various shipping
public API’s during Pick, Pack and Ship activities. The diagram also
shows the relevant shipping API’s used during each of these specific steps.
Standard
Parameters in APIs:
1. p_api_version_number: This is used to compare the incoming API call's version
number with the current version number (always set to 1.0).
2. p_init_msg_list: This is used to Initialize message list and
use FND_API.G_TRUE to
reset the list.
3. p_commit: Will commit the changes to the database. The
Default Value is
FND_API.G_FALSE
4. x_return_status: Requests that the API return the status of
the data after it completes
its
function.
Valid values include:
• Success : FND_API.G_RET_STS_SUCCESS
• Error : FND_API.G_RET_STS_ERROR
• Unexpected Error: FND_API.G_RET_STS_UNEXP_ERROR
5. x_msg_count: Indicates number of error messages API has encountered.
6. x_msg_data: Returns error message text. If the
x_msg_count is equal to 1, then this
contains the actual message.
Setting the application
context:
FND_GLOBAL.APPS_INITIALIZE()
procedure in the beginning is used to initialize the environment.
Detailed
Flow Sequence:
Each of the steps created in the flow are discussed in
detail under the following headings.
Book
order:
Create an Order from order management Super User, Vision
Operations (USA) responsibility with the following details
Customer Number :1006
Warehouse
: M1
Ship Method :
DHL
Line Item
: AS54888
Ordered Quantity : 2
API
to Create Delivery in Oracle Apps:
As a next step to booking, a new delivery needs to be
created. In order to create a new delivery we need to call the shipping API
“WSH_DELIVERIES_PUB.Create_update_delivery”
by passing the required and optional
parameter values.
Specific
parameters:
·
P_api_version_number
=> 1.0
·
p_action_code => CREATE -- for creating new delivery
·
p_delivery_info => Attributes of the delivery entity of
type Delivery_Pub_Rec_Type
The sample script attached here can be used to create a new
delivery.
DECLARE
-- Standard Parameters.
p_api_version NUMBER;
init_msg_list VARCHAR2(30);
p_commit VARCHAR2(30);
-- Sepcific Parameters for
WSH_DELIVERIES_PUB.create_update_delivery
p_action_code VARCHAR2(15);
delivery_id NUMBER;
delivery_info WSH_DELIVERIES_PUB.DELIVERY_PUB_REC_TYPE;
delivery_name VARCHAR2(30);
-- out parameters
x_return_status VARCHAR2(10);
x_msg_count NUMBER;
x_msg_data VARCHAR2(2000);
x_msg_details VARCHAR2(3000);
x_msg_summary VARCHAR2(3000);
-- Handle exceptions
fail_api EXCEPTION;
BEGIN
-- Initialize return status
x_return_status :=
WSH_UTIL_CORE.G_RET_STS_SUCCESS;
-- Call this procedure to initialize
applications parameters.
FND_GLOBAL.APPS_INITIALIZE(
user_id => 1318
,
resp_id => 21623
,
resp_appl_id => 660);
-- Create a new delivery for the following
delivery_info.name := 'TEST-001'; --
Pass delivery name
delivery_info.organization_id := 207; -- Pass Organization ID
delivery_info.initial_pickup_location_id := 207; -- Pass the Pick up location ID
delivery_info.ultimate_dropoff_location_id := 1091;
-- pass the Drop off location ID
delivery_info.ship_method_code := 'DHL'; -- pass Ship Method
p_action_code := 'CREATE';
-- Action Code
-- Call to
WSH_DELIVERIES_PUB.create_update_delivery
WSH_DELIVERIES_PUB.create_update_delivery(
p_api_version_number => 1.0,
p_init_msg_list => init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_action_code => p_action_code,
p_delivery_info => delivery_info,
p_delivery_name => delivery_name,
x_delivery_id => delivery_id,
x_name => delivery_name );
-- If the return status is not success(S)
then raise exception
if (x_return_status <>
WSH_UTIL_CORE.G_RET_STS_SUCCESS) then
raise fail_api;
else
dbms_output.put_line('New Delivery
ID : '||delivery_id);
dbms_output.put_line('New Delivery Name:
'||delivery_name);
end if;
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;
Dbms_output.put_line('Message Data
: '||x_msg_data);
else
x_msg_data := x_msg_summary;
Dbms_output.put_line('Message Data
: '||x_msg_data);
end if;
END;
/
Assign delivery detail to delivery:
The delivery detail can be assigned to the newly created
delivery by using the public API
“WSH_DELIVERY_DETAILS_PUB. Detail_to_Delivery”.
Specific Parameters:
·
p_TabOfDelDets => Table of Delivery Detail
id’s
·
p_action => ASSIGN
·
p_delivery_id => Delivery id
·
delivery_name => Delivery name to which
the detail lines will be assigned
The sample script attached here can be used to assign the
delivery detail to the newly created delivery.
DECLARE
-- Standard Parameters.
p_api_version NUMBER;
p_init_msg_list VARCHAR2(30);
p_commit VARCHAR2(30);
-- Parameters for
WSH_DELIVERY_DETAILS_PUB.Detail_to_Delivery
p_delivery_id NUMBER;
p_delivery_name VARCHAR2(30);
p_TabOfDelDet
WSH_DELIVERY_DETAILS_PUB.id_tab_type;
p_action VARCHAR2(30);
-- out parameters
x_return_status VARCHAR2(10);
x_msg_count NUMBER;
x_msg_data VARCHAR2(2000);
x_msg_details VARCHAR2(3000);
x_msg_summary VARCHAR2(3000);
-- Handle exceptions
fail_api EXCEPTION;
BEGIN
-- Initialize return status
x_return_status :=
WSH_UTIL_CORE.G_RET_STS_SUCCESS;
-- Call this procedure to initialize
applications parameters.
FND_GLOBAL.APPS_INITIALIZE(
user_id => 1318,
resp_id => 21623,
resp_appl_id => 660);
-- Values for
WSH_DELIVERY_DETAILS_PUB.Detail_to_Delivery
p_delivery_id := 1997596;
p_delivery_name :='TEST-001';
p_TabOfDelDet(1) := 2295572;
p_action := 'ASSIGN';
-- Call to
WSH_DELIVERY_DETAILS_PUB.Detail_to_Delivery.
WSH_DELIVERY_DETAILS_PUB.Detail_to_Delivery(
p_api_version => 1.0,
p_init_msg_list => p_init_msg_list,
p_commit => p_commit,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_TabOfDelDets => p_TabOfDelDet,
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;
else
dbms_output.put_line('Detail
'||p_TabOfDelDet(1)|| ' assignment to the delivery '|| p_delivery_name ||' is
successful');
end if;
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;
Dbms_output.put_line('Message Data :
'||x_msg_data);
else
x_msg_data := x_msg_summary;
Dbms_output.put_line('Message Data :
'||x_msg_data);
end if;
END;
/
Pick
Release:
The shipping public
API "WSH_DELIVERIES_PUB. Delivery_Action”, enables
pick release of the
sales order line. The relevant pick
release parameters are retrieved from the Shipping and Organization Parameter
setup.
Specific Parameters:
- p_action_code = > PICK_RELEASE
- p_delivery_id/p_delivery_name => Id/name of delivery
Refer to the attached PICK_RELEASE.sql sample script which
will pick releases lines for a given delivery.
DECLARE
-- Standard Parameters.
p_api_version NUMBER;
p_init_msg_list VARCHAR2(30);
p_commit VARCHAR2(30);
--Parameters for
WSH_DELIVERIES_PUB.Delivery_Action.
p_action_code VARCHAR2(15);
p_delivery_id NUMBER;
p_delivery_name VARCHAR2(30);
p_asg_trip_id NUMBER;
p_asg_trip_name VARCHAR2(30);
p_asg_pickup_stop_id NUMBER;
p_asg_pickup_loc_id NUMBER;
p_asg_pickup_loc_code VARCHAR2(30);
p_asg_pickup_arr_date DATE;
p_asg_pickup_dep_date DATE;
p_asg_dropoff_stop_id NUMBER;
p_asg_dropoff_loc_id NUMBER;
p_asg_dropoff_loc_code VARCHAR2(30);
p_asg_dropoff_arr_date DATE;
p_asg_dropoff_dep_date DATE;
p_sc_action_flag VARCHAR2(10);
p_sc_close_trip_flag VARCHAR2(10);
p_sc_create_bol_flag VARCHAR2(10);
p_sc_stage_del_flag VARCHAR2(10);
p_sc_trip_ship_method VARCHAR2(30);
p_sc_actual_dep_date VARCHAR2(30);
p_sc_report_set_id NUMBER;
p_sc_report_set_name VARCHAR2(60);
p_wv_override_flag VARCHAR2(10);
x_trip_id VARCHAR2(30);
x_trip_name VARCHAR2(30);
-- outparameters
x_return_status VARCHAR2(10);
x_msg_count NUMBER;
x_msg_data VARCHAR2(2000);
x_msg_details VARCHAR2(3000);
x_msg_summary VARCHAR2(3000);
-- Handle exceptions
fail_api EXCEPTION;
BEGIN
-- Initialize return status
x_return_status :=
WSH_UTIL_CORE.G_RET_STS_SUCCESS;
-- Call this procedure to initialize
applications parameters.
FND_GLOBAL.APPS_INITIALIZE(
user_id => 1318
,
resp_id => 21623
,
resp_appl_id => 660);
-- Values for
WSH_DELIVERIES_PUB.delivery_action
p_action_code := 'PICK-RELEASE'; -- Releases Lines related to a delivery
p_delivery_id := 1997596; -- delivery ID that action is
performed on
-- Call to WSH_DELIVERIES_PUB.Delivery_Action.
WSH_DELIVERIES_PUB.Delivery_Action(
p_api_version_number => 1.0,
p_init_msg_list => P_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_action_code => p_action_code,
p_delivery_id => p_delivery_id,
p_delivery_name => p_delivery_name,
p_asg_trip_id => p_asg_trip_id,
p_asg_trip_name => p_asg_trip_name,
p_asg_pickup_stop_id => p_asg_pickup_stop_id,
p_asg_pickup_loc_id => p_asg_pickup_loc_id,
p_asg_pickup_loc_code => p_asg_pickup_loc_code,
p_asg_pickup_arr_date => p_asg_pickup_arr_date,
p_asg_pickup_dep_date => p_asg_pickup_dep_date,
p_asg_dropoff_stop_id => p_asg_dropoff_stop_id,
p_asg_dropoff_loc_id => p_asg_dropoff_loc_id,
p_asg_dropoff_loc_code => p_asg_dropoff_loc_code,
p_asg_dropoff_arr_date => p_asg_dropoff_arr_date,
p_asg_dropoff_dep_date => p_asg_dropoff_dep_date,
p_sc_action_flag => p_sc_action_flag,
p_sc_close_trip_flag => p_sc_close_trip_flag,
p_sc_create_bol_flag => p_sc_create_bol_flag,
p_sc_stage_del_flag => p_sc_stage_del_flag,
p_sc_trip_ship_method => p_sc_trip_ship_method,
p_sc_actual_dep_date => p_sc_actual_dep_date,
p_sc_report_set_id => p_sc_report_set_id,
p_sc_report_set_name => p_sc_report_set_name,
p_wv_override_flag => p_wv_override_flag,
x_trip_id => x_trip_id,
x_trip_name => x_trip_name);
if (x_return_status <>
WSH_UTIL_CORE.G_RET_STS_SUCCESS) then
raise fail_api;
else
dbms_output.put_line('The delivery
'||p_delivery_id || ' is successfully pick released');
end if;
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;
Dbms_output.put_line('Message Data :
'||x_msg_data);
else
x_msg_data := x_msg_summary;
Dbms_output.put_line('Message Data :
'||x_msg_data);
end if;
END;
Manual Pack:
Before advancing to this step, Please refer to Oracle metalink (Containerization in Shipping Execution) for all the necessary needed for packing/containerization
Manual Packing involves two steps
1. Creation
of LPN’s:
LPN creation is done using the public API “WSH_CONTAINER_PUB.
Create_Container”
Specific Parameters:
·
p_container_item_id => Key flexfield Id for the container
·
p_organization_id => Organization ID for the container.
·
p_quantity => Number of containers created
·
p_container_name => Container name if creating just one container
·
x_container_ids => Table of the newly created container IDs of
type
WSH_UTIL_CORE.ID_TAB_TYPE, which is
a table of
type number indexed by binary integers
Refer to the attached CREATE_CONTAINER.sql sample script,
which will enable the creation of containers
DECLARE
--
Standard Parameters.
p_api_version NUMBER;
p_init_msg_list VARCHAR2(30) ;
p_commit VARCHAR2(30);
-- Parameters for
WSH_CONTAINER_PUB.create_containers
p_container_item_id NUMBER;
p_container_item_name VARCHAR2(2000) ;
p_container_item_seg FND_FLEX_EXT.SEGMENTARRAY ;
p_organization_id NUMBER;
p_organization_code VARCHAR2(2000) ;
p_name_prefix VARCHAR2(2000) ;
p_name_suffix VARCHAR2(2000) ;
p_base_number NUMBER ;
p_num_digits NUMBER ;
p_quantity NUMBER ;
p_container_name VARCHAR2(2000) ;
x_container_ids WSH_UTIL_CORE.ID_TAB_TYPE ;
--out parameters
x_return_status VARCHAR2(10);
x_msg_count NUMBER;
x_msg_data VARCHAR2(2000);
x_msg_details VARCHAR2(3000);
x_msg_summary VARCHAR2(3000);
--Handle exceptions
fail_api EXCEPTION;
BEGIN
-- Initialize return status
x_return_status :=
WSH_UTIL_CORE.G_RET_STS_SUCCESS;
-- Call this procedure to initialize
applications parameters.
FND_GLOBAL.APPS_INITIALIZE(
user_id => 1318
,
resp_id => 21623
,
resp_appl_id => 660);
-- Values for
WSH_CONTAINER_PUB.CREATE_CONTAINERS
p_container_item_id := 4556;
-- inventory item id of the container item defined
p_container_name :='CT-13';-- name of the new LPN to be
created
p_organization_id := 207;
-- orgnization id to which the container is associated
p_quantity := 1; -- denotes the number of LPN to be
created.
-- Call to
WSH_CONTAINER_PUB.CREATE_CONTAINERS
WSH_CONTAINER_PUB.create_containers(
p_api_version => 1.0
,
p_init_msg_list =>
p_init_msg_list
,
p_commit =>
p_commit
,
x_return_status =>
x_return_status
,
x_msg_count =>
x_msg_count
,
x_msg_data =>
x_msg_data
,
p_container_item_id =>
p_container_item_id
,
p_container_item_name => p_container_item_name
,
p_container_item_seg =>
p_container_item_seg
,
p_organization_id =>
p_organization_id
,
p_organization_code =>
p_organization_code
,
p_name_prefix =>
p_name_prefix
,
p_name_suffix =>
p_name_suffix
,
p_base_number =>
p_base_number
,
p_num_digits =>
p_num_digits
,
p_quantity =>
p_quantity
,
p_container_name =>
p_container_name
,
x_container_ids =>
x_container_ids
);
if (x_return_status <>
WSH_UTIL_CORE.G_RET_STS_SUCCESS) then
raise fail_api;
else
dbms_output.put_line('Container
'||p_container_name||' is successfully created');
end if;
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;
dbms_output.put_line('Message Data :
'||x_msg_data);
else
x_msg_data := x_msg_summary;
dbms_output.put_line('Message Data :
'||x_msg_data);
end if;
END;
/
2. Perform
Manual Pack operation using the LPN created previously
In order to pack the delivery
detail line item using the LPN created previously, we can use the public API “WSH_CONTAINER_PUB.Container_Actions”.
Specific Parameters:
p_container_name => Name of the container
p_action_code => Action
code ‘PACK’
p_detail_tab =>
Delivery detail to be packed. Input table of delivery detail ids of
type
WSH_UTIL_CORE.ID_TAB_TYPE, which is a table of
type
Number indexed by binary integers.
Refer to the attached MANUAL_PACK.sql sample script, which
will enable the packing action on the created LPN’s.
DECLARE
-- Standard Parameters.
p_api_version NUMBER;
p_init_msg_list VARCHAR2(30) ;
p_commit VARCHAR2(30);
-- Parameters for
WSH_CONTAINER_PUB.container_actions
p_detail_tab WSH_UTIL_CORE.ID_TAB_TYPE ;
p_container_name VARCHAR2(2000);
p_cont_instance_id NUMBER ;
p_container_flag VARCHAR2(2000) ;
p_delivery_flag VARCHAR2(2000) ;
p_delivery_id NUMBER ;
p_delivery_name VARCHAR2(2000) ;
p_action_code VARCHAR2(2000);
-- out parameters
x_return_status VARCHAR2(10);
x_msg_count NUMBER;
x_msg_data VARCHAR2(2000);
x_msg_details VARCHAR2(3000);
x_msg_summary VARCHAR2(3000);
-- Handle exceptions
fail_api EXCEPTION;
BEGIN
-- Initialize return status
x_return_status :=
WSH_UTIL_CORE.G_RET_STS_SUCCESS;
-- Call this procedure to initialize
applications parameters.
FND_GLOBAL.APPS_INITIALIZE(
user_id => 1318
,
resp_id => 21623
,
resp_appl_id => 660);
-- Values for
WSH_CONTAINER_PUB.CONTAINER_ACTIONS
p_container_name := 'CT-13'; -- Container_name
p_action_code := 'PACK'; -- Container action code (PACK)
p_detail_tab(1) :=
2325652; -- Delivery detail to be packed
-- Call to
WSH_CONTAINER_PUB.CONTAINER_ACTIONS
WSH_CONTAINER_PUB.container_actions(
p_api_version => 1.0
,
p_init_msg_list =>
p_init_msg_list
,
p_commit => p_commit
,
x_return_status =>
x_return_status
,
x_msg_count =>
x_msg_count
,
x_msg_data =>
x_msg_data
,
p_detail_tab =>
p_detail_tab
,
p_container_name =>
p_container_name
,
p_cont_instance_id =>
p_cont_instance_id
,
p_container_flag =>
p_container_flag
,
p_delivery_flag =>
p_delivery_flag
,
p_delivery_id =>
p_delivery_id
,
p_delivery_name =>
p_delivery_name
,
p_action_code => p_action_code
);
if (x_return_status <>
WSH_UTIL_CORE.G_RET_STS_SUCCESS) then
raise fail_api;
else
dbms_output.put_line('The container
'||p_container_name||' is successfully packed');
end if;
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;
Dbms_output.put_line('Message Data :
'||x_msg_data);
else
x_msg_data := x_msg_summary;
Dbms_output.put_line('Message Data :
'||x_msg_data);
end if;
END;
/
Unpack:
We can unpack the packed delivery line by making call to the
container public API “WSH_CONTAINER_PUB.Container_Actions”
Specific Parameters:
p_container_name=> Name of the container
p_action_code => Action code ‘UNPACK’
p_detail_tab => Delivery detail to be unpacked. Input
table of delivery detail ids of type
WSH_UTIL_CORE.ID_TAB_TYPE which is a table of type Number
indexed by binary
integers.
DECLARE
-- Standard Parameters.
p_api_version NUMBER;
p_init_msg_list VARCHAR2(30) ;
p_commit VARCHAR2(30);
-- Parameters for
WSH_CONTAINER_PUB.container_actions
p_detail_tab WSH_UTIL_CORE.ID_TAB_TYPE ;
p_container_name VARCHAR2(2000);
p_cont_instance_id NUMBER ;
p_container_flag VARCHAR2(2000) ;
p_delivery_flag VARCHAR2(2000) ;
p_delivery_id NUMBER ;
p_delivery_name VARCHAR2(2000) ;
p_action_code VARCHAR2(2000);
--out parameters
x_return_status VARCHAR2(10);
x_msg_count NUMBER;
x_msg_data VARCHAR2(2000);
x_msg_details VARCHAR2(3000);
x_msg_summary VARCHAR2(3000);
--Handle exceptions
fail_api EXCEPTION;
BEGIN
-- Initialize return status
x_return_status :=
WSH_UTIL_CORE.G_RET_STS_SUCCESS;
-- Call this procedure to initialize
applications parameters
FND_GLOBAL.APPS_INITIALIZE(
user_id => 1318
,
resp_id => 21623
,
resp_appl_id => 660);
-- Values for WSH_CONTAINER_PUB.CONTAINER_ACTIONS
p_container_name := 'CT-1'; -- Container_name
p_action_code := 'UNPACK'; -- Container Action(UNPACK)
p_detail_tab(1) := 2325633;
-- Delivery detail to be unpacked
-- Call to
WSH_CONTAINER_PUB.container_actions
WSH_CONTAINER_PUB.container_actions(
p_api_version => 1.0
,
p_init_msg_list =>
p_init_msg_list
,
p_commit =>
p_commit
,
x_return_status => x_return_status
,
x_msg_count =>
x_msg_count
,
x_msg_data =>
x_msg_data
,
p_detail_tab =>
p_detail_tab
,
p_container_name =>
p_container_name
,
p_cont_instance_id =>
p_cont_instance_id
,
p_container_flag =>
p_container_flag
,
p_delivery_flag =>
p_delivery_flag
,
p_delivery_id =>
p_delivery_id
,
p_delivery_name =>
p_delivery_name
,
p_action_code =>
p_action_code
);
if (x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS)
then
raise fail_api;
else
dbms_output.put_line('The container
'||p_container_name|| ' unpack action is successful');
end if;
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;
Dbms_output.put_line('Message Data :
'||x_msg_data);
else
x_msg_data := x_msg_summary;
Dbms_output.put_line('Message Data :
'||x_msg_data);
end if;
END;
/
Unassign
the unpacked LPN line from delivery:
The delivery detail for the LPN line, which was unpacked,
previously needs to be unassigned from the delivery.
Using “WSH_DELIVERY_DETAILS_PUB.Detail_to_Delivery” shipping
API, the delivery detail is unassigned from the delivery.
Specific parameters:
·
p_TabOfDelDets => Table of
Delivery Detail id’s
·
p_action =>
Action UNASSIGN
·
p_delivery_id
or delivery_name => Delivery id or
delivery name to which the detail lines will be assigned
DECLARE
-- Standard Parameters.
p_api_version NUMBER;
p_init_msg_list VARCHAR2(30);
p_commit VARCHAR2(30);
-- Parameters for
WSH_DELIVERY_DETAILS_PUB.Detail_to_Delivery
p_delivery_id NUMBER;
delivery_name VARCHAR2(30);
p_TabOfDelDet WSH_DELIVERY_DETAILS_PUB.id_tab_type;
p_action VARCHAR2(30);
-- out parameters
x_return_status VARCHAR2(10);
x_msg_count NUMBER;
x_msg_data VARCHAR2(2000);
x_msg_details VARCHAR2(3000);
x_msg_summary VARCHAR2(3000);
-- Handle exceptions
fail_api EXCEPTION;
BEGIN
-- Initialize return status
x_return_status :=
WSH_UTIL_CORE.G_RET_STS_SUCCESS;
-- Call this procedure to initialize
applications parameters
FND_GLOBAL.APPS_INITIALIZE(
user_id => 1318
,
resp_id => 21623
,
resp_appl_id => 660);
-- Values for
WSH_DELIVERY_DETAILS_PUB.Detail_to_Delivery
p_delivery_id := 2021620; -- delivery ID
p_TabOfDelDet(1) := 2325641; -- delivery Detail ID
p_action := 'UNASSIGN'; -- Action
(UNASSIGN)
-- Call to
WSH_DELIVERY_DETAILS_PUB.Detail_to_Delivery.
WSH_DELIVERY_DETAILS_PUB.Detail_to_Delivery(
p_api_version => 1.0,
p_init_msg_list => p_init_msg_list,
p_commit => p_commit,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_TabOfDelDets => p_TabOfDelDet,
p_action => p_action,
p_delivery_id => p_delivery_id,
p_delivery_name => delivery_name);
if (x_return_status <>
WSH_UTIL_CORE.G_RET_STS_SUCCESS) then
raise fail_api;
else
dbms_output.put_line('the unpacked
container line '||p_TabOfDelDet(1)|| ' is unassigned successfully from delivery
'|| p_delivery_id);
end if;
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;
Dbms_output.put_line('Message Data :
'||x_msg_data);
else
x_msg_data := x_msg_summary;
Dbms_output.put_line('Message Data :
'||x_msg_data);
end if;
END;
/
Auto
Pack Master:
The Auto-Pack
Master behaves very much similar to Auto-Pack except that it goes one step
further and packs the created “detail” containers into one or more “parent
(master)” container(s).
The
parameter “p_pack_cont_flag” set
to ‘Y’ or ‘N’ will decide whether to autopack the detail containers that are
created into parent containers.
For Auto pack master option, set this parameter to ‘Y’
Specific Parameters:
·
P_entity_tab
=> Table of ids of either
lines or containers or deliveries that need to
be
autopacked of type WSH_UTIL_CORE.ID_TAB_TYPE
which is
a table of type Number indexed by binary integers.
·
P_entity_type
=> Type of entity id
contained in the entity_tab that needs to be
autopacked ('L' - lines, 'C' -containers or 'D' - deliveries).
·
P_group_id_tab => Table of ids (numbers that determine the grouping of lines for
packing
into containers) of type
WSH_UTIL_CORE.ID_TAB_TYPE which is a table of type
Number
indexed by binary integers.
·
P_pack_cont_flag=> A 'Y' or 'N' value to determine whether to autopack the detail
containers that are created into parent
containers.
·
X_cont_inst_tab => Table of container IDs created during the autopacking process of
type
DECLARE
--Standard Parameters.
p_api_version_number NUMBER;
init_msg_list VARCHAR2(30);
p_commit VARCHAR2(30);
--Parameters for
WSH_CONTAINER_PUB.Auto_Pack.
p_entity_tab WSH_UTIL_CORE.id_tab_type;
p_entity_type VARCHAR2(30);
p_group_id_tab WSH_UTIL_CORE.id_tab_type;
p_pack_cont_flag VARCHAR2(30):='Y';
x_cont_inst_tab WSH_UTIL_CORE.id_tab_type;
--out parameters
x_return_status VARCHAR2(10);
x_msg_count NUMBER;
x_msg_data VARCHAR2(5000);
x_msg_details VARCHAR2(3000);
x_msg_summary VARCHAR2(3000);
-- Handle exceptions
fail_api EXCEPTION;
BEGIN
-- Initialize return status
x_return_status :=
WSH_UTIL_CORE.G_RET_STS_SUCCESS;
-- Call this procedure to initialize
applications parameters
FND_GLOBAL.APPS_INITIALIZE(
user_id => 1318
,
resp_id => 21623
,
resp_appl_id => 660);
-- Values for autopacking the delivery
details to a container
p_group_id_tab(1) :=1;
p_entity_tab(1) := '2325652';
--Call to WSH_CONTAINER_PUB.Auto_Pack
WSH_CONTAINER_PUB.Auto_Pack(
p_api_version => 1.0,
p_init_msg_list => init_msg_list,
p_commit => p_commit,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_entity_tab =>
p_entity_tab,
p_entity_type => 'L',
p_group_id_tab => p_group_id_tab,
p_pack_cont_flag => p_pack_cont_flag,
x_cont_inst_tab => x_cont_inst_tab);
if (x_return_status <>
WSH_UTIL_CORE.G_RET_STS_SUCCESS) then
raise fail_api;
else
dbms_output.put_line('Autopack Master
action on delivery detail '||p_entity_tab(1)|| ' is completed successfully');
end if;
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;
Dbms_output.put_line('Message Data :
'||x_msg_data);
else
x_msg_data := x_msg_summary;
Dbms_output.put_line('Message Data :
'||x_msg_data);
end if;
END;
Update
Delivery Details:
The “WSH_DELIVERY_DETAILS_PUB.Update_Shipping_Attributes”
API enables you to modify data in wsh_delivery_details.
In this specific example we have used this API to update the
shipped_quantity field in the wsh_delivery_details table to ship all
quantities.
Specific Parameters:
·
p_changed_attributes=>Attributes
of Changed Attributes Tab Type that are to be updated.
·
p_source_code => Code
for source system which updates wsh_delivery_details table(always set to OE)
In order to specify in your logic about backorder or staged
quantities you can use the
following logic
-- Ship all quantities
changed_attributes(1).delivery_detail_id :=
<<Enter the detail id>>;
changed_attributes(1).shipped_quantity :=
<<Enter the full quantity to be shipped>>;
-- Back order all quantities
changed_attributes(2).delivery_detail_id :=
<<Enter the detail id>>;
changed_attributes(2).shipped_quantity := 0;
changed_attributes(2).cycle_count_quantity :=
<<Enter the full quantity to be shipped>>;
-- Stage all the quantities
changed_attributes(3).delivery_detail_id :=
<<Enter the full quantity to be shipped>>;
changed_attributes(3).shipped_quantity := 0;
changed_attributes(3).cycle_count_quantity
:= 0;
DECLARE
--Standard Parameters.
p_api_version NUMBER;
init_msg_list VARCHAR2(30);
p_commit VARCHAR2(30);
--Parameters for
WSH_DELIVERY_DETAILS_PUB.update_shipping_attributes.
source_code VARCHAR2(15);
changed_attributes
WSH_DELIVERY_DETAILS_PUB.ChangedAttributeTabType;
--out parameters
x_return_status VARCHAR2(10);
x_msg_count NUMBER;
x_msg_data VARCHAR2(2000);
x_msg_details VARCHAR2(3000);
x_msg_summary VARCHAR2(3000);
-- Handle exceptions
fail_api EXCEPTION;
BEGIN
-- Initialize return status
x_return_status :=
WSH_UTIL_CORE.G_RET_STS_SUCCESS;
-- Call this procedure to initialize
applications parameters
FND_GLOBAL.APPS_INITIALIZE(
user_id => 1318
,
resp_id => 21623
,
resp_appl_id => 660);
/* Values for updating delivery details to
ship all quantities in the
first line, stage everything in the
second line, and back order all in
the third. It is assumed that the user
knows the quantities in each
line.
*/
source_code := 'OE'; -- The only
source code that should be used by the API
changed_attributes(1).delivery_detail_id
:= 2325652; -- Ship All quantities in this detail.
changed_attributes(1).shipped_quantity := 2;
-- update the shipped quantity to 2
--Call to
WSH_DELIVERY_DETAILS_PUB.Update_Shipping_Attributes.
WSH_DELIVERY_DETAILS_PUB.Update_Shipping_Attributes(
p_api_version_number => 1.0,
p_init_msg_list => init_msg_list,
p_commit => p_commit,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_changed_attributes => changed_attributes,
p_source_code => source_code);
if (x_return_status <>
WSH_UTIL_CORE.G_RET_STS_SUCCESS) then
raise fail_api;
else
dbms_output.put_line('The shipped
quantity is updated with '||changed_attributes(1).shipped_quantity|| ' for the
delivery detail '||changed_attributes(1).delivery_detail_id );
end if;
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;
Dbms_output.put_line('Message Data :
'||x_msg_data);
else
x_msg_data := x_msg_summary;
Dbms_output.put_line('Message Data :
'||x_msg_data);
end if;
END;
/
Ship
Confirm:
As a final step, call “WSH_DELIVERIES_PUB.Delivery_Action”
API in order to ship confirm the delivery.
Specific Parameters:
p_action_code(Required) => action
to be performed on Delivery
p_delivery_id (Required) => delivery id on which the action is
performed
p_trip_name
=> Trip identifier for
assignment of trip to delivery
p_asg_pickup_loc_code => Stop location
code for pickup assignment
p_asg_pickup_dep_date => Stop location
departure date for pickup assignment
p_asg_dropoff_loc_code=> Stop location
code for dropoff assignment
p_asg_dropoff_dep_date=> Stop location
departure date for dropoff assignment
p_sc_action_flag => Ship Confirm option - S, B,
T, A, C. Used p_sc_intransit_flag
=> Ship Confirm set in-transit flag.
p_sc_close_trip_flag => Ship Confirm close trip flag.
p_sc_create_bol_flag => Ship Confirm create Bill of Lading
flag
p_sc_stage_del_flag => Ship Confirm create delivery for
stage quantity flag
p_sc_trip_ship_method => Ship Confirm trip ship method.
p_wv_override_flag => Override flag for weight volume
calculations.
x_trip_name => Name of autocreated
trip.
DECLARE
--Standard Parameters.
p_api_version NUMBER;
p_init_msg_list VARCHAR2(30);
p_commit VARCHAR2(30);
--Parameters for
WSH_DELIVERIES_PUB.Delivery_Action.
p_action_code VARCHAR2(15);
p_delivery_id NUMBER;
p_delivery_name VARCHAR2(30);
p_asg_trip_id NUMBER;
p_asg_trip_name VARCHAR2(30);
p_asg_pickup_stop_id NUMBER;
p_asg_pickup_loc_id NUMBER;
p_asg_pickup_loc_code VARCHAR2(30);
p_asg_pickup_arr_date DATE;
p_asg_pickup_dep_date DATE;
p_asg_dropoff_stop_id NUMBER;
p_asg_dropoff_loc_id NUMBER;
p_asg_dropoff_loc_code VARCHAR2(30);
p_asg_dropoff_arr_date DATE;
p_asg_dropoff_dep_date DATE;
p_sc_action_flag VARCHAR2(10);
p_sc_close_trip_flag VARCHAR2(10);
p_sc_create_bol_flag VARCHAR2(10);
p_sc_stage_del_flag VARCHAR2(10);
p_sc_trip_ship_method VARCHAR2(30);
p_sc_actual_dep_date VARCHAR2(30);
p_sc_report_set_id NUMBER;
p_sc_report_set_name VARCHAR2(60);
p_wv_override_flag VARCHAR2(10);
p_sc_defer_interface_flag VARCHAR2(1);
x_trip_id VARCHAR2(30);
x_trip_name VARCHAR2(30);
--out parameters
x_return_status VARCHAR2(10);
x_msg_count NUMBER;
x_msg_data VARCHAR2(2000);
x_msg_details VARCHAR2(3000);
x_msg_summary VARCHAR2(3000);
-- Handle exceptions
fail_api EXCEPTION;
BEGIN
-- Initialize return status
x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
-- Call this procedure to initialize
applications parameters
FND_GLOBAL.APPS_INITIALIZE(
user_id => 1318
,
resp_id => 21623
,
resp_appl_id => 660);
-- Values for Ship Confirming the delivery
p_action_code := 'CONFIRM'; -- The action
code for ship confirm
p_delivery_id := 2023619; -- The delivery that needs to be confirmed
p_sc_action_flag := 'S'; -- Ship entered quantity.
p_sc_close_trip_flag := 'Y'; -- Close the trip after ship confirm
p_sc_trip_ship_method := 'DHL'; -- The ship method code
p_sc_defer_interface_flag := 'N';
-- Call to
WSH_DELIVERIES_PUB.Delivery_Action.
WSH_DELIVERIES_PUB.Delivery_Action(
p_api_version_number => 1.0,
p_init_msg_list => p_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_action_code => p_action_code,
p_delivery_id => p_delivery_id,
p_delivery_name => p_delivery_name,
p_asg_trip_id => p_asg_trip_id,
p_asg_trip_name => p_asg_trip_name,
p_asg_pickup_stop_id => p_asg_pickup_stop_id,
p_asg_pickup_loc_id => p_asg_pickup_loc_id,
p_asg_pickup_loc_code => p_asg_pickup_loc_code,
p_asg_pickup_arr_date => p_asg_pickup_arr_date,
p_asg_pickup_dep_date => p_asg_pickup_dep_date,
p_asg_dropoff_stop_id => p_asg_dropoff_stop_id,
p_asg_dropoff_loc_id => p_asg_dropoff_loc_id,
p_asg_dropoff_loc_code => p_asg_dropoff_loc_code,
p_asg_dropoff_arr_date => p_asg_dropoff_arr_date,
p_asg_dropoff_dep_date => p_asg_dropoff_dep_date,
p_sc_action_flag => p_sc_action_flag,
p_sc_close_trip_flag => p_sc_close_trip_flag,
p_sc_create_bol_flag => p_sc_create_bol_flag,
p_sc_stage_del_flag => p_sc_stage_del_flag,
p_sc_trip_ship_method => p_sc_trip_ship_method,
p_sc_actual_dep_date => p_sc_actual_dep_date,
p_sc_report_set_id => p_sc_report_set_id,
p_sc_report_set_name => p_sc_report_set_name,
p_wv_override_flag => p_wv_override_flag,
p_sc_defer_interface_flag => p_sc_defer_interface_flag ,
x_trip_id => x_trip_id,
x_trip_name => x_trip_name);
if (x_return_status <>
WSH_UTIL_CORE.G_RET_STS_SUCCESS) then
raise fail_api;
else
dbms_output.put_line('The confirm action
on the delivery '||p_delivery_id||' is successful');
end if;
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;
Dbms_output.put_line('Message Data :
'||x_msg_data);
else
x_msg_data := x_msg_summary;
Dbms_output.put_line('Message Data :
'||x_msg_data);
end if;
END;
/
