[Home] [Help]
PACKAGE BODY: APPS.WSH_OTM_INBOUND_GRP
Source
1 PACKAGE BODY WSH_OTM_INBOUND_GRP as
2 /* $Header: WSHOTMIB.pls 120.2.12010000.3 2008/10/23 12:32:42 mvudugul ship $ */
3
4 -- Global Variables
5 G_PKG_NAME CONSTANT VARCHAR2(30) := 'WSH_OTM_INBOUND_GRP';
6
7 TYPE WSH_OTM_DEL_INTERFACE IS RECORD (
8 delivery_interface_id NUMBER,
9 leg_created_flag VARCHAR2(1)
10 );
11
12 TYPE WSH_OTM_DEL_INTERFACES IS TABLE OF WSH_OTM_DEL_INTERFACE INDEX BY VARCHAR2(32767);
13
14 TYPE WSH_OTM_STOP_INTERFACES IS TABLE OF NUMBER INDEX BY VARCHAR2(32767);
15
16 --***************************************************************************--
17 --========================================================================
18 -- PROCEDURE : initiate_planned_shipment Called by the WSH Inbound BPEL Process
19 --
20 -- PARAMETERS: p_int_trip_info Table of trip records to process into the interface table
21 -- The trip record is a nested object containing
22 -- Stop, releases, Legs and Freight Costs
23 -- x_output_request_id Concurrent request_id of the instance launched for WSHOTMRL
24 -- x_msg_count Number of messages
25 -- x_msg_data Message Data
26 -- x_return_status Return status
27 -- COMMENT : This procedure is used to perform for following actions
28 -- Takes a table of trip records to process
29 -- Processes the trip and children stops, releases and legs
30 -- and inserts into the WSH Interface tables
31 -- Then launches the WSHOTMRL concurrent program
32 --========================================================================
33
34 PROCEDURE initiate_planned_shipment( p_int_trip_info IN WSH_OTM_TRIP_TAB,
35 x_output_request_id OUT NOCOPY NUMBER,
36 x_return_status OUT NOCOPY VARCHAR2,
37 x_msg_count OUT NOCOPY NUMBER,
38 x_msg_data OUT NOCOPY VARCHAR2) IS
39 CURSOR c_get_del_revision(c_delivery_id IN NUMBER) IS
40 SELECT tms_version_number,name,status_code
41 FROM wsh_new_deliveries
42 WHERE delivery_id = c_delivery_id;
43
44 i NUMBER := 0;
45 j NUMBER;
46 k NUMBER;
47 l NUMBER;
48 m NUMBER;
49 p NUMBER;
50 q NUMBER;
51 t NUMBER;
52 l_count NUMBER;
53 l_skip_trip BOOLEAN := FALSE;
54 l_tp_plan_name WSH_UTIL_CORE.column_tab_type;
55 l_carrier_id WSH_UTIL_CORE.id_tab_type;
56 l_mode_of_transport WSH_UTIL_CORE.column_tab_type;
57 l_service_level WSH_UTIL_CORE.column_tab_type;
58 l_freight_terms_code WSH_UTIL_CORE.column_tab_type;
59 l_vehicle_item_id WSH_UTIL_CORE.id_tab_type;
60 l_vehicle_item_name WSH_UTIL_CORE.column_tab_type;
61 l_vehicle_number WSH_UTIL_CORE.column_tab_type;
62 l_vehicle_number_prefix WSH_UTIL_CORE.column_tab_type;
63 l_vessel WSH_UTIL_CORE.column_tab_type;
64 l_voyage_number WSH_UTIL_CORE.column_tab_type;
65 l_stop_location_id WSH_UTIL_CORE.column_tab_type;
66 l_stop_sequence_number WSH_UTIL_CORE.id_tab_type;
67 l_stop_timezone_code WSH_UTIL_CORE.column_tab_type;
68 l_stop_trip_int_id WSH_UTIL_CORE.column_tab_type;
69 /*
70 l_stop_pa_date WSH_UTIL_CORE.column_tab_type;
71 l_stop_pd_date WSH_UTIL_CORE.column_tab_type;
72 l_stop_aa_date WSH_UTIL_CORE.column_tab_type;
73 l_stop_ad_date WSH_UTIL_CORE.column_tab_type;
74 */
75
76 l_stop_pa_date WSH_UTIL_CORE.date_tab_type;
77 l_stop_pd_date WSH_UTIL_CORE.date_tab_type;
78 l_stop_aa_date WSH_UTIL_CORE.date_tab_type;
79 l_stop_ad_date WSH_UTIL_CORE.date_tab_type;
80 -- bug 6700792: OTM Dock Door App Sched Proj
81 l_stop_dock_door_id WSH_UTIL_CORE.column_tab_type;
82 l_stop_start_time WSH_UTIL_CORE.date_tab_type;
83 l_stop_end_time WSH_UTIL_CORE.date_tab_type;
84
85 l_delivery_name WSH_UTIL_CORE.column_tab_type;
86 l_release_xid WSH_UTIL_CORE.column_tab_type;
87 l_ship_from_location_xid WSH_UTIL_CORE.column_tab_type;
88 l_ship_to_location_xid WSH_UTIL_CORE.column_tab_type;
89 /*
90 l_earliest_pickup_date WSH_UTIL_CORE.column_tab_type;
91 l_latest_pickup_date WSH_UTIL_CORE.column_tab_type;
92 l_earliest_dropoff_date WSH_UTIL_CORE.column_tab_type;
93 l_latest_dropoff_date WSH_UTIL_CORE.column_tab_type;
94 */
95 l_earliest_pickup_date WSH_UTIL_CORE.date_tab_type;
96 l_latest_pickup_date WSH_UTIL_CORE.date_tab_type;
97 l_earliest_dropoff_date WSH_UTIL_CORE.date_tab_type;
98 l_latest_dropoff_date WSH_UTIL_CORE.date_tab_type;
99
100 l_release_refnum WSH_UTIL_CORE.column_tab_type;
101 l_release_freight_cost WSH_UTIL_CORE.column_tab_type;
102 l_release_freight_currency WSH_UTIL_CORE.column_tab_type;
103 l_release_freight_del_id WSH_UTIL_CORE.column_tab_type;
104 l_release_freight_dint_id WSH_UTIL_CORE.id_tab_type;
105
106 l_delivery_tab WSH_TMS_RELEASE.delivery_tab;
107 l_dummy_del_id_tab WSH_UTIL_CORE.id_tab_type;
108 l_return_status VARCHAR2(1);
109
110 l_delivery_id VARCHAR2(32767) := NULL;
111 l_local_release_tab WSH_OTM_DEL_INTERFACES;
112 l_stop_loc_tab WSH_OTM_STOP_INTERFACES;
113
114 l_interface_act_code VARCHAR2(30) := WSH_TMS_RELEASE.g_tms_release_code;
115 l_del_refnum NUMBER := NULL;
116 l_del_id_number NUMBER := 0;
117 l_release_id VARCHAR2(100);
118 l_dleg_pu_loc_id VARCHAR2(100);
119 l_dleg_do_loc_id VARCHAR2(100);
120 l_del_revision_number NUMBER := 0;
121 l_fc_count NUMBER := 0;
122 l_int_del_cnt NUMBER := 0;
123 l_int_stop_cnt NUMBER := 0;
124 l_del_status_code VARCHAR2(2);
125
126 l_dleg_int_act_code WSH_UTIL_CORE.column_tab_type;
127 l_dleg_del_id WSH_UTIL_CORE.column_tab_type;
128 l_dleg_del_int_id WSH_UTIL_CORE.id_tab_type;
129 l_dleg_pustop_id WSH_UTIL_CORE.id_tab_type;
130 l_dleg_dostop_id WSH_UTIL_CORE.id_tab_type;
131 l_group_id NUMBER ;
132 l_request_id NUMBER ;
133 l_user_id NUMBER;
134 l_resp_id NUMBER;
135
136 --l_process_trip_cnt NUMBER := 0;
137 l_cnt NUMBER := p_int_trip_info.COUNT;
138 l_inp_count VARCHAR2(30) := 'p_int_trip_info COUNT '||p_int_trip_info.COUNT;
139 l_stop_count NUMBER := 1;
140 l_release_count NUMBER := 1;
141 l_ship_unit_count NUMBER := 1;
142 l_del_int_id_tab WSH_UTIL_CORE.id_tab_type;
143 l_trip_int_id WSH_UTIL_CORE.id_tab_type;
144 l_stop_int_id_tab WSH_UTIL_CORE.id_tab_type;
145 -- Bug#7491598(ER,defer planned shipment iface):
146 l_global_params WSH_SHIPPING_PARAMS_PVT.Global_Parameters_Rec_Typ;
147 -- Bug#7491598(ER,defer planned shipment iface):
148
149 l_debug_on CONSTANT BOOLEAN := WSH_DEBUG_SV.is_debug_enabled;
150 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'initiate_planned_shipment';
151 BEGIN
152
153 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
154 IF l_debug_on THEN
155 WSH_DEBUG_SV.push (l_module_name);
156 WSH_DEBUG_SV.logmsg(l_module_name,'-------------- START ----------------');
157 WSH_DEBUG_SV.logmsg(l_module_name,'p_int_trip_info COUNT : '||l_cnt);
158 END IF;
159
160 SAVEPOINT before_insert;
161
162 l_user_id := FND_PROFILE.value('WSH_OTM_DEFAULT_APPS_USER');
163 l_resp_id := FND_PROFILE.value('WSH_OTM_DEFAULT_APPS_RESP');
164
165 IF l_debug_on THEN
166 WSH_DEBUG_SV.logmsg(l_module_name,'Profile User Id : '||l_user_id);
167 WSH_DEBUG_SV.logmsg(l_module_name,'Profile Responsibility Id : '||l_resp_id);
168 WSH_DEBUG_SV.logmsg(l_module_name,'Calling FND_GLOBAL.APPS_INITIALIZE ');
169 END IF;
170
171 -- security_group_id is optional
172 --fnd_global.apps_initialize(l_user_id,l_resp_id,l_resp_appl_id,l_security_group_id);
173
174 FND_GLOBAL.APPS_INITIALIZE(l_user_id,l_resp_id,660);
175
176 /*
177
178 -- Construct delivery legs for groups of ship units
179 -- shipunit -> (pick first one) shipunitcontent -> release xid
180 -- How to derive group_id for this transaction ?
181 -- new sequence
182 -- where to store incoming vehicle item name ?
183 -- create a new column in trips_interface
184 -- populate interface action code
185 -- populate trips, stops, deliveries, legs and freight costs interface tables
186
187 */
188
189 l_delivery_tab.DELETE;
190 l_ship_unit_count := 0;
191
192 i := p_int_trip_info.FIRST;
193 IF i IS NOT NULL THEN
194 LOOP
195 l_skip_trip := FALSE;
196 IF l_debug_on THEN
197 WSH_DEBUG_SV.logmsg(l_module_name,'current trip record is i = '||i);
198 WSH_DEBUG_SV.logmsg(l_module_name,'p_int_trip_info.shipment_stops COUNT : '||p_int_trip_info(i).shipment_stops.COUNT);
199 WSH_DEBUG_SV.logmsg(l_module_name,'p_int_trip_info.shipment_releases COUNT : '||p_int_trip_info(i).shipment_releases.COUNT);
200 WSH_DEBUG_SV.logmsg(l_module_name,'p_int_trip_info.shipment_ship_units COUNT : '||p_int_trip_info(i).shipment_ship_units.COUNT);
201 WSH_DEBUG_SV.logmsg(l_module_name,'p_int_trip_info.stop_locations. COUNT : '||p_int_trip_info(i).stop_locations.COUNT);
202 END IF;
203
204 -- Call anshuman's API to derive list of EBS delivery ids and versions for the input tp_plan_name for a trip
205 -- Check versions for each of the deliveries in a trip
206 -- Error out / Skip a trip if any delivery in a trip has a version less than that of the EBS version
207 -- What to do if all trips fail version check ?
208 -- no need to launch concurrent program
209
210 -- Returns x_delivery_tab.COUNT = 0 if WSH trip corresponding to p_tp_plan_name does not exist
211 -- In that case will have to query delivery by delivery to derive revision number
212
213 --IF nvl(p_int_trip_info(i).transaction_code,'CREATE') <> 'DELETE' THEN
214 IF nvl(p_int_trip_info(i).transaction_code,'CREATE') <> 'D' THEN
215
216 WSH_TMS_RELEASE.find_deliveries_for_trip(
217 p_trip_id => NULL,
218 p_tp_plan_name => p_int_trip_info(i).shipment_xid,
219 x_delivery_tab => l_delivery_tab,
220 x_delivery_id_tab => l_dummy_del_id_tab,
221 x_return_status => l_return_status);
222
223 IF l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR,WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
224 raise FND_API.G_EXC_UNEXPECTED_ERROR;
225 ELSE
226 IF l_debug_on THEN
227 WSH_DEBUG_SV.logmsg(l_module_name,'Returned from WSH_TMS_RELEASE.FIND_DELIVERIES_FOR_TRIP x_return_status : '||l_return_status);
228 WSH_DEBUG_SV.logmsg(l_module_name,'l_delivery_tab.COUNT : '||l_delivery_tab.COUNT);
229 END IF;
230
231 END IF;
232
233
234 -- This API will return an assciative array indexed by delivery_id
235 -- This record structure has revision_number
236
237 -- Delete all tables of local release/delivery
238 l_local_release_tab.DELETE;
239 l_del_int_id_tab.DELETE;
240 l_release_xid.DELETE;
241 l_ship_from_location_xid.DELETE;
242 l_ship_to_location_xid.DELETE;
243 l_release_refnum.DELETE;
244 l_earliest_pickup_date.DELETE;
245 l_latest_pickup_date.DELETE;
246 l_earliest_dropoff_date.DELETE;
247 l_latest_dropoff_date.DELETE;
248 l_release_freight_cost.delete;
249 l_release_freight_currency.delete;
250 l_fc_count := 0;
251 l_del_refnum := NULL;
252 l_del_revision_number := NULL;
253
254 l := p_int_trip_info(i).shipment_releases.FIRST;
255 IF l IS NOT NULL THEN
256 LOOP
257
258 IF l_debug_on THEN
259 WSH_DEBUG_SV.logmsg(l_module_name,'current release record is l = '||l);
260 WSH_DEBUG_SV.logmsg(l_module_name,'Release_xid : '||p_int_trip_info(i).shipment_releases(l).release_xid);
261 WSH_DEBUG_SV.logmsg(l_module_name,'Release_refnum : '||p_int_trip_info(i).shipment_releases(l).release_refnum);
262 WSH_DEBUG_SV.logmsg(l_module_name,'Early Pickup Date : '||p_int_trip_info(i).shipment_releases(l).early_pickup_date);
263 WSH_DEBUG_SV.logmsg(l_module_name,'Late Pickup Date : '||p_int_trip_info(i).shipment_releases(l).late_pickup_date);
264 WSH_DEBUG_SV.logmsg(l_module_name,'Early Delivery Date : '||p_int_trip_info(i).shipment_releases(l).early_delivery_date);
265 WSH_DEBUG_SV.logmsg(l_module_name,'Late Delivery Date : '||p_int_trip_info(i).shipment_releases(l).late_delivery_date);
266 WSH_DEBUG_SV.logmsg(l_module_name,'p_int_trip_info.shipment_releases.release_freight_costs COUNT : '||p_int_trip_info(i).shipment_releases(l).release_freight_costs.COUNT);
267 END IF;
268
269 l_del_status_code := NULL;
270 -- Derive delivery's revision_number if could not be defined for the trip earlier
271 l_del_id_number := to_number(p_int_trip_info(i).shipment_releases(l).release_xid);
272 IF l_delivery_tab.COUNT = 0 OR (NOT l_delivery_tab.EXISTS(p_int_trip_info(i).shipment_releases(l).release_xid)) THEN
273 IF l_debug_on THEN
274 WSH_DEBUG_SV.logmsg(l_module_name,'l_del_id_number : '||l_del_id_number);
275 WSH_DEBUG_SV.logmsg(l_module_name,'Opening c_get_del_revision for release_xid '||p_int_trip_info(i).shipment_releases(l).release_xid);
276 END IF;
277
278 OPEN c_get_del_revision(l_del_id_number);
279 FETCH c_get_del_revision INTO l_del_revision_number,l_delivery_name(l), l_del_status_code;
280 IF c_get_del_revision%NOTFOUND THEN
281 IF l_debug_on THEN
282 WSH_DEBUG_SV.logmsg(l_module_name,'WSH revision not found for release_xid : '||p_int_trip_info(i).shipment_releases(l).release_xid);
283 WSH_DEBUG_SV.logmsg(l_module_name,'Skipping this trip ');
284 END IF;
285 l_skip_trip := TRUE;
286 CLOSE c_get_del_revision;
287 EXIT;
288 END IF;
289 IF l_debug_on THEN
290 WSH_DEBUG_SV.logmsg(l_module_name,'l_del_revision_number : '||l_del_revision_number);
291 WSH_DEBUG_SV.logmsg(l_module_name,'l_del_name : '||l_delivery_name(l));
292 WSH_DEBUG_SV.logmsg(l_module_name,'l_del_status : '||l_del_status_code);
293 END IF;
294 CLOSE c_get_del_revision;
295
296 ELSE
297 l_del_revision_number := l_delivery_tab(p_int_trip_info(i).shipment_releases(l).release_xid).tms_version_number;
298 l_delivery_name(l) := l_delivery_tab(p_int_trip_info(i).shipment_releases(l).release_xid).name;
299 l_del_status_code := l_delivery_tab(p_int_trip_info(i).shipment_releases(l).release_xid).status_code;
300 IF l_debug_on THEN
301 WSH_DEBUG_SV.logmsg(l_module_name,'l_del_revision_number : '||l_del_revision_number);
302 WSH_DEBUG_SV.logmsg(l_module_name,'l_del_name : '||l_delivery_name(l));
303 WSH_DEBUG_SV.logmsg(l_module_name,'l_del_status : '||l_del_status_code);
304 END IF;
305 END IF;
306 l_del_refnum := to_number(p_int_trip_info(i).shipment_releases(l).release_refnum);
307 IF l_debug_on THEN
308 WSH_DEBUG_SV.logmsg(l_module_name,'l_del_refnum : '||l_del_refnum);
309 END IF;
310 IF (nvl(l_del_refnum,0) < nvl(l_del_revision_number,0) OR l_del_status_code <> 'OP') THEN
311 l_skip_trip := TRUE;
312 EXIT;
313 END IF;
314
315 l_delivery_id := p_int_trip_info(i).shipment_releases(l).release_xid;
316
317 -- Create a local delivery record type l_local_release_tab indexed by delivery_id with attributes
318 -- delivery_interface_id and leg_created_flag
319
320 l_local_release_tab(l_delivery_id).delivery_interface_id := NULL;
321 l_local_release_tab(l_delivery_id).leg_created_flag := 'N';
322
323 l_release_xid(l) := p_int_trip_info(i).shipment_releases(l).release_xid;
324 l_ship_from_location_xid(l) := substrb(p_int_trip_info(i).shipment_releases(l).ship_from_location_xid,instrb(p_int_trip_info(i).shipment_releases(l).ship_from_location_xid,'-',1,2) + 1);
325 l_ship_to_location_xid(l) := substrb(p_int_trip_info(i).shipment_releases(l).ship_to_location_xid,instrb(p_int_trip_info(i).shipment_releases(l).ship_to_location_xid,'-',1,2) + 1);
326 l_earliest_pickup_date(l) := to_date(p_int_trip_info(i).shipment_releases(l).early_pickup_date,'YYYYMMDDHH24MISS');
327 l_latest_pickup_date(l) := to_date(p_int_trip_info(i).shipment_releases(l).late_pickup_date,'YYYYMMDDHH24MISS');
328 l_earliest_dropoff_date(l) := to_date(p_int_trip_info(i).shipment_releases(l).early_delivery_date,'YYYYMMDDHH24MISS');
332 IF l_debug_on THEN
329 l_latest_dropoff_date(l) := to_date(p_int_trip_info(i).shipment_releases(l).late_delivery_date,'YYYYMMDDHH24MISS');
330 l_release_refnum(l) := p_int_trip_info(i).shipment_releases(l).release_refnum;
331
333 WSH_DEBUG_SV.logmsg(l_module_name,'Populated release record structure ');
334 WSH_DEBUG_SV.logmsg(l_module_name,'l_release_xid : '||l_release_xid(l));
335 END IF;
336 -- LOOP over freight cost records for a release
337 -- create wsh_fc_interface record structures
338 --l_release_freight_cost(l) := 0;
339 p := p_int_trip_info(i).shipment_releases(l).release_freight_costs.FIRST;
340 IF p IS NOT NULL THEN
341 LOOP
342 l_fc_count := l_fc_count + 1;
343 IF l_debug_on THEN
344 WSH_DEBUG_SV.logmsg(l_module_name,'current freight cost record is p = '||p);
345 WSH_DEBUG_SV.logmsg(l_module_name,'for shipment_xid : '||p_int_trip_info(i).shipment_releases(l).release_freight_costs(p).shipment_xid);
346 END IF;
347 -- Insert each record
348
349 l_release_freight_del_id(l_fc_count) := p_int_trip_info(i).shipment_releases(l).release_xid;
350 l_release_freight_cost(l_fc_count) := p_int_trip_info(i).shipment_releases(l).release_freight_costs(p).monetary_amount;
351 l_release_freight_currency(l_fc_count) := p_int_trip_info(i).shipment_releases(l).release_freight_costs(p).currency_code;
352
353 EXIT WHEN p = p_int_trip_info(i).shipment_releases(l).release_freight_costs.LAST;
354 p := p_int_trip_info(i).shipment_releases(l).release_freight_costs.NEXT(p);
355 END LOOP;
356 END IF;
357
358 EXIT WHEN l = p_int_trip_info(i).shipment_releases.LAST;
359 l := p_int_trip_info(i).shipment_releases.NEXT(l);
360 END LOOP;
361 END IF;
362
363 IF l_skip_trip THEN
364 IF l_debug_on THEN
365 WSH_DEBUG_SV.logmsg(l_module_name,'Skipping this trip ');
366 WSH_DEBUG_SV.logmsg(l_module_name,'Could not find one or more'||
367 ' deliveries associated with this trip OR trip has one or '||
368 'more deliveries that are not in open status.');
369 END IF;
370 GOTO next_trip;
371 END IF;
372
373 ELSE
374 l_interface_act_code := WSH_TMS_RELEASE.g_tms_delete_code;
375 END IF; -- Transaction_code <> 'DELETE'
376
377 --l_process_trip_cnt := l_process_trip_cnt + 1;
378
379 -- Call purge_interface_tables API :
380 -- Delete if any record exits in interface tables for the input deliveries
381 -- What about the exceptions for those deliveries ? -- This API takes care
382
383 WSH_TMS_RELEASE.purge_interface_data(
384 p_tp_plan_name => p_int_trip_info(i).shipment_xid,
385 p_commit_flag => 'N',
386 x_return_status => l_return_status);
387
388 IF l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR,WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
389 raise FND_API.G_EXC_UNEXPECTED_ERROR;
390 END IF;
391
392 --IF nvl(p_int_trip_info(i).transaction_code,'CREATE') <> 'DELETE' THEN
393 IF nvl(p_int_trip_info(i).transaction_code,'CREATE') <> 'D' THEN
394
395 -- Bulk insert delivery interface records here
396 -- returning delivery_interface_id s BULK COLLECT INTO l_del_int_id_tab
397 IF l_debug_on THEN
398 WSH_DEBUG_SV.logmsg(l_module_name,'Bulk Inserting into wsh_new_del_interface ');
399 END IF;
400
401 forall j in p_int_trip_info(i).shipment_releases.FIRST .. p_int_trip_info(i).shipment_releases.LAST
402 Insert into WSH_NEW_DEL_INTERFACE
403 (DELIVERY_INTERFACE_ID,
404 INTERFACE_ACTION_CODE,
405 DELIVERY_ID,
406 NAME,
407 PLANNED_FLAG,
408 STATUS_CODE,
409 INITIAL_PICKUP_LOCATION_ID,
410 ULTIMATE_DROPOFF_LOCATION_ID,
411 EARLIEST_PICKUP_DATE,
412 LATEST_PICKUP_DATE,
413 EARLIEST_DROPOFF_DATE,
414 LATEST_DROPOFF_DATE,
415 DELIVERY_TYPE,
416 TMS_VERSION_NUMBER,
417 CREATION_DATE,
418 CREATED_BY,
419 LAST_UPDATE_DATE,
420 LAST_UPDATED_BY)
421 Values (WSH_NEW_DEL_INTERFACE_S.nextval,
422 l_interface_act_code,
423 l_release_xid(j),
424 l_delivery_name(j),
425 'Y',
426 'OP',
427 l_ship_from_location_xid(j),
428 l_ship_to_location_xid(j),
429 l_earliest_pickup_date(j),
430 l_latest_pickup_date(j),
431 l_earliest_dropoff_date(j),
432 l_latest_dropoff_date(j),
433 'STANDARD',
434 l_release_refnum(j),
435 SYSDATE,
436 1,
437 SYSDATE,
438 1)
439 RETURNING DELIVERY_INTERFACE_ID BULK COLLECT INTO l_del_int_id_tab;
440
441 IF l_debug_on THEN
445 -- Create an associative array of delivery_interface_id s indexed by delivery_id
442 WSH_DEBUG_SV.logmsg(l_module_name,'interface id count l_del_int_id_tab : '||l_del_int_id_tab.COUNT);
443 END IF;
444
446 -- Populate l_local_release_tab
447 l_int_del_cnt := 0;
448 l := p_int_trip_info(i).shipment_releases.FIRST;
449 IF l IS NOT NULL THEN
450 LOOP
451
452 l_int_del_cnt := l_int_del_cnt + 1;
453 l_local_release_tab(p_int_trip_info(i).shipment_releases(l).release_xid).delivery_interface_id := l_del_int_id_tab(l_int_del_cnt);
454 l_local_release_tab(p_int_trip_info(i).shipment_releases(l).release_xid).leg_created_flag := 'N';
455 IF l_debug_on THEN
456 WSH_DEBUG_SV.logmsg(l_module_name,'Creating local release for release_xid : '||p_int_trip_info(i).shipment_releases(l).release_xid);
457 WSH_DEBUG_SV.logmsg(l_module_name,'delivery_interface_id : '||l_local_release_tab(p_int_trip_info(i).shipment_releases(l).release_xid).delivery_interface_id);
458 WSH_DEBUG_SV.logmsg(l_module_name,'leg_created_flag : '||l_local_release_tab(p_int_trip_info(i).shipment_releases(l).release_xid).leg_created_flag);
459 END IF;
460
461 EXIT WHEN l = p_int_trip_info(i).shipment_releases.LAST;
462 l := p_int_trip_info(i).shipment_releases.NEXT(l);
463 END LOOP;
464 END IF;
465
466 IF l_fc_count > 0 THEN
467 for p in 1 .. l_fc_count
468 LOOP
469 l_release_freight_dint_id(p) := l_local_release_tab(l_release_freight_del_id(p)).delivery_interface_id;
470 END LOOP;
471 END IF;
472
473 -- Bulk insert fc interface record by deriving delivery_interface_id s for each of the record
474 -- from the above associative array
475
476 IF l_debug_on THEN
477 WSH_DEBUG_SV.logmsg(l_module_name,'Inserting into wsh_freight_costs_inetrface ');
478 END IF;
479
480 forall j in 1 .. l_fc_count
481 Insert into WSH_FREIGHT_COSTS_INTERFACE
482 ( FREIGHT_COST_INTERFACE_ID,
483 INTERFACE_ACTION_CODE,
484 UNIT_AMOUNT,
485 TOTAL_AMOUNT,
486 CURRENCY_CODE,
487 DELIVERY_ID,
488 DELIVERY_INTERFACE_ID,
489 CREATION_DATE,
490 CREATED_BY,
491 LAST_UPDATE_DATE,
492 LAST_UPDATED_BY)
493 VALUES
494 ( WSH_FREIGHT_COSTS_INTERFACE_S.nextval,
495 l_interface_act_code,
496 l_release_freight_cost(j),
497 l_release_freight_cost(j),
498 l_release_freight_currency(j),
499 l_release_freight_del_id(j),
500 l_release_freight_dint_id(j),
501 SYSDATE,
502 1,
503 SYSDATE,
504 1);
505
506 END IF; -- Transaction_code <> 'DELETE'
507
508
509 l_tp_plan_name(i) := p_int_trip_info(i).shipment_xid;
510 l_carrier_id(i) := substrb(p_int_trip_info(i).service_provider_xid,5);
511 l_mode_of_transport(i) := p_int_trip_info(i).transport_mode_xid;
512 l_service_level(i) := p_int_trip_info(i).rate_service_xid;
513 l_freight_terms_code(i) := p_int_trip_info(i).payment_code_xid;
514 --l_vehicle_item_name(i) := p_int_trip_info(i).equipment_xid;
515 l_vehicle_item_name(i) := p_int_trip_info(i).equipment_group_xid;
516 l_vehicle_number(i) := p_int_trip_info(i).equipment_number;
517 l_vehicle_number_prefix(i) := p_int_trip_info(i).equipment_initial;
518 l_vessel(i) := p_int_trip_info(i).vessel_xid;
519 l_voyage_number(i) := p_int_trip_info(i).voyage_xid;
520
521 -- Insert the trip_interface here returning the trip_interface_id into l_trip_int_id(i)
522
523 IF l_debug_on THEN
524 WSH_DEBUG_SV.logmsg(l_module_name,'Inserting into wsh_trips interface ');
525 END IF;
526
527 Insert into WSH_TRIPS_INTERFACE
528 (TRIP_INTERFACE_ID,
529 INTERFACE_ACTION_CODE,
530 NAME,
531 GROUP_ID,
532 TP_PLAN_NAME,
533 CARRIER_ID,
534 MODE_OF_TRANSPORT,
535 SERVICE_LEVEL,
536 FREIGHT_TERMS_CODE,
537 VEHICLE_ITEM_NAME,
538 VEHICLE_NUMBER,
539 VEHICLE_NUM_PREFIX,
540 --VESSEL,
541 --VOYAGE_NUMBER,
542 CREATION_DATE,
543 CREATED_BY,
544 LAST_UPDATE_DATE,
545 LAST_UPDATED_BY)
546 Values ( WSH_TRIPS_INTERFACE_S.nextval,
547 l_interface_act_code,
548 NULL,
549 nvl(l_group_id,WSH_TRIPS_INTERFACE_GROUP_S.nextval),
550 l_tp_plan_name(i),
551 l_carrier_id(i),
552 l_mode_of_transport(i),
553 l_service_level(i),
554 l_freight_terms_code(i),
555 l_vehicle_item_name(i),
556 l_vehicle_number(i),
557 l_vehicle_number_prefix(i),
558 --l_vessel(i),
559 --l_voyage_number(i),
560 SYSDATE,
564 RETURNING TRIP_INTERFACE_ID,GROUP_ID INTO l_trip_int_id(i),l_group_id;
561 1,
562 SYSDATE,
563 1)
565
566 IF l_debug_on THEN
567 WSH_DEBUG_SV.logmsg(l_module_name,'interface id l_trip_int_id(i) : '||l_trip_int_id(i));
568 WSH_DEBUG_SV.logmsg(l_module_name,'group id : '||l_group_id);
569 END IF;
570
571 --IF nvl(p_int_trip_info(i).transaction_code,'CREATE') <> 'DELETE' THEN
572 IF nvl(p_int_trip_info(i).transaction_code,'CREATE') <> 'D' THEN
573
574 -- Delete stop tables
575 l_stop_loc_tab.DELETE;
576 l_stop_int_id_tab.DELETE;
577 l_stop_location_id.DELETE;
578 l_stop_sequence_number.DELETE;
579 l_stop_trip_int_id.DELETE;
580 l_stop_pa_date.DELETE;
581 l_stop_pd_date.DELETE;
582 l_stop_aa_date.DELETE;
583 l_stop_ad_date.DELETE;
584 --bug 6700792: OTM Dock Door App Sched Proj
585 l_stop_dock_door_id.DELETE;
586 l_stop_start_time.DELETE;
587 l_stop_end_time.DELETE;
588
589
590 k := p_int_trip_info(i).shipment_stops.FIRST;
591 IF k IS NOT NULL THEN
592 LOOP
593 -- Need to insert trip_interface_id
594 IF l_debug_on THEN
595 WSH_DEBUG_SV.logmsg(l_module_name,'current stop record is k = '||k);
596 WSH_DEBUG_SV.logmsg(l_module_name,'stop_location_xid : '||p_int_trip_info(i).shipment_stops(k).stop_location_xid);
597 WSH_DEBUG_SV.logmsg(l_module_name,'stop_sequence_number : '||p_int_trip_info(i).shipment_stops(k).stop_sequence_number);
598 WSH_DEBUG_SV.logmsg(l_module_name,'pa_date : '||p_int_trip_info(i).shipment_stops(k).planned_arrival_time);
599 WSH_DEBUG_SV.logmsg(l_module_name,'pd_date : '||p_int_trip_info(i).shipment_stops(k).planned_departure_time);
600 WSH_DEBUG_SV.logmsg(l_module_name,'aa_date : '||p_int_trip_info(i).shipment_stops(k).actual_arrival_time);
601 WSH_DEBUG_SV.logmsg(l_module_name,'ad_date : '||p_int_trip_info(i).shipment_stops(k).actual_departure_time);
602 --bug 6700792: OTM Dock Door App Sched Proj
603 WSH_DEBUG_SV.logmsg(l_module_name,'dock_name : '||p_int_trip_info(i).shipment_stops(k).dock_door_xid);
604 WSH_DEBUG_SV.logmsg(l_module_name,'start_time : '||p_int_trip_info(i).shipment_stops(k).start_time);
605 WSH_DEBUG_SV.logmsg(l_module_name,'end_time : '||p_int_trip_info(i).shipment_stops(k).end_time);
606 END IF;
607
608 l_stop_location_id(k) := substrb(p_int_trip_info(i).shipment_stops(k).stop_location_xid,instrb(p_int_trip_info(i).shipment_stops(k).stop_location_xid,'-',1,2) + 1);
609 l_stop_sequence_number(k) := p_int_trip_info(i).shipment_stops(k).stop_sequence_number;
610 l_stop_trip_int_id(k) := l_trip_int_id(i);
611
612 -- Timezone conversion
613 -- Create timezone_code as a new column in wsh_trip_stops_interface
614
615 -- LOOP over p_int_trip_info(i).locations
616 -- IF LocationGid.Xid matches stop_location_xid THEN store Address.TimeZoneGid.Xid of that record as TimezoneCode
617 -- Exit LOOP
618
619 t := p_int_trip_info(i).stop_locations.FIRST;
620 IF t IS NOT NULL THEN
621 LOOP
622 IF l_debug_on THEN
623 WSH_DEBUG_SV.logmsg(l_module_name,'tz location xid : '||p_int_trip_info(i).stop_locations(t).location_xid);
624 END IF;
625 IF p_int_trip_info(i).stop_locations(t).location_xid = p_int_trip_info(i).shipment_stops(k).stop_location_xid THEN
626 l_stop_timezone_code(k) := p_int_trip_info(i).stop_locations(t).timezone_xid;
627 IF l_debug_on THEN
628 WSH_DEBUG_SV.logmsg(l_module_name,'Stop timezone_xid : '||l_stop_timezone_code(k));
629 END IF;
630 EXIT;
631 END IF;
632
633 EXIT WHEN t = p_int_trip_info(i).stop_locations.LAST;
634 t := p_int_trip_info(i).stop_locations.NEXT(t);
635 END LOOP;
636 END IF;
637
638 l_stop_pa_date(k) := to_date(p_int_trip_info(i).shipment_stops(k).planned_arrival_time,'YYYYMMDDHH24MISS');
639 l_stop_pd_date(k) := to_date(p_int_trip_info(i).shipment_stops(k).planned_departure_time,'YYYYMMDDHH24MISS');
640
641 l_stop_aa_date(k) := to_date(p_int_trip_info(i).shipment_stops(k).actual_arrival_time,'YYYYMMDDHH24MISS');
642 l_stop_ad_date(k) := to_date(p_int_trip_info(i).shipment_stops(k).actual_departure_time,'YYYYMMDDHH24MISS');
643 --bug 6700792: OTM Dock Door App Sched Proj
644 l_stop_dock_door_id(k) := p_int_trip_info(i).shipment_stops(k).dock_door_xid;
645 l_stop_start_time(k) := to_date(p_int_trip_info(i).shipment_stops(k).start_time,'YYYYMMDDHH24MISS');
646 l_stop_end_time(k) := to_date(p_int_trip_info(i).shipment_stops(k).end_time,'YYYYMMDDHH24MISS');
647 --
648
649 EXIT WHEN k = p_int_trip_info(i).shipment_stops.LAST;
650 k := p_int_trip_info(i).shipment_stops.NEXT(k);
651 END LOOP;
652 END IF;
653
654 -- Bulk insert stop interface records here
655 -- returning stop_interface_id s BULK COLLECT INTO l_stop_int_id_tab
656 IF l_debug_on THEN
657 WSH_DEBUG_SV.logmsg(l_module_name,'Inserting into wsh_trip_stops_interface ');
658 END IF;
659
660 forall j in p_int_trip_info(i).shipment_stops.FIRST .. p_int_trip_info(i).shipment_stops.LAST
661 -- bug 6700792: OTM Dock Door App Sched Info
662 -- Inserting columns dock_name, start_time and end_time
663 Insert into WSH_TRIP_STOPS_INTERFACE
667 STOP_LOCATION_ID,
664 (STOP_INTERFACE_ID,
665 INTERFACE_ACTION_CODE,
666 TRIP_INTERFACE_ID,
668 STOP_SEQUENCE_NUMBER,
669 TIMEZONE_CODE,
670 PLANNED_ARRIVAL_DATE,
671 PLANNED_DEPARTURE_DATE,
672 DOCK_NAME,
673 START_TIME,
674 END_TIME,
675 CREATION_DATE,
676 CREATED_BY,
677 LAST_UPDATE_DATE,
678 LAST_UPDATED_BY)
679 Values (WSH_TRIP_STOPS_INTERFACE_S.nextval,
680 l_interface_act_code,
681 l_stop_trip_int_id(j),
682 l_stop_location_id(j),
683 l_stop_sequence_number(j),
684 l_stop_timezone_code(j),
685 l_stop_pa_date(j),
686 l_stop_pd_date(j),
687 l_stop_dock_door_id(j),
688 l_stop_start_time(j),
689 l_stop_end_time(j),
690 SYSDATE,
691 1,
692 SYSDATE,
693 1)
694 Returning STOP_INTERFACE_ID BULK COLLECT into l_stop_int_id_tab;
695
696 IF l_debug_on THEN
697 WSH_DEBUG_SV.logmsg(l_module_name,'interface id count l_stop_int_id_tab : '||l_stop_int_id_tab.COUNT);
698 END IF;
699
700 -- Create an associative array of stop_interface_id s indexed by stop_location_id
701 -- l_stop_loc_tab
702
703 l_int_stop_cnt := 0;
704 k := p_int_trip_info(i).shipment_stops.FIRST;
705 IF k IS NOT NULL THEN
706 LOOP
707
708 l_int_stop_cnt := l_int_stop_cnt + 1;
709 l_stop_loc_tab(p_int_trip_info(i).shipment_stops(k).stop_location_xid) := l_stop_int_id_tab(l_int_stop_cnt);
710 IF l_debug_on THEN
711 WSH_DEBUG_SV.logmsg(l_module_name,'stop_location_xid : '||p_int_trip_info(i).shipment_stops(k).stop_location_xid);
712 WSH_DEBUG_SV.logmsg(l_module_name,'l_stop_loc_tab data : '||l_stop_loc_tab(p_int_trip_info(i).shipment_stops(k).stop_location_xid));
713 END IF;
714
715 EXIT WHEN k = p_int_trip_info(i).shipment_stops.LAST;
716 k := p_int_trip_info(i).shipment_stops.NEXT(k);
717 END LOOP;
718 END IF;
719
720 IF l_debug_on THEN
721 WSH_DEBUG_SV.logmsg(l_module_name,'Created l_stop_loc_tab : '||l_stop_loc_tab.COUNT);
722 END IF;
723
724 m := p_int_trip_info(i).shipment_ship_units.FIRST;
725 IF m IS NOT NULL THEN
726 LOOP
727 -- Construct delivery legs for groups of ship units
728 -- shipunit -> (pick first one) shipunitcontent -> release xid
729
730 l_release_id := p_int_trip_info(i).shipment_ship_units(m).release_xid;
731 l_dleg_pu_loc_id := p_int_trip_info(i).shipment_ship_units(m).ship_from_location_xid;
732 l_dleg_do_loc_id := p_int_trip_info(i).shipment_ship_units(m).ship_to_location_xid;
733
734 IF l_debug_on THEN
735 WSH_DEBUG_SV.logmsg(l_module_name,'shipunit count : '||m);
736 WSH_DEBUG_SV.logmsg(l_module_name,'ship_unit_xid : '||p_int_trip_info(i).shipment_ship_units(m).ship_unit_xid);
737 WSH_DEBUG_SV.logmsg(l_module_name,'l_release_id : '||l_release_id);
738 WSH_DEBUG_SV.logmsg(l_module_name,'l_dleg_pu_loc_id : '||l_dleg_pu_loc_id);
739 WSH_DEBUG_SV.logmsg(l_module_name,'l_dleg_do_loc_id : '||l_dleg_do_loc_id);
740 END IF;
741
742 -- 0. Skip the current ship_unit if the leg created flag for this ship_unit's delivery_id is Yes
743
744 IF l_local_release_tab(l_release_id).leg_created_flag = 'Y' THEN
745 IF l_debug_on THEN
746 WSH_DEBUG_SV.logmsg(l_module_name,'Skipping as alreasy created shunit xid : '||p_int_trip_info(i).shipment_ship_units(m).ship_unit_xid);
747 END IF;
748 GOTO next_shipunit;
749 END IF;
750 IF l_debug_on THEN
751 WSH_DEBUG_SV.logmsg(l_module_name,'Using shunit xid : '||p_int_trip_info(i).shipment_ship_units(m).ship_unit_xid);
752 END IF;
753 l_ship_unit_count := l_ship_unit_count + 1;
754
755 -- 1. derive delivery_interface_id from delivery_id associative array
756
757 l_dleg_int_act_code(l_ship_unit_count) := l_interface_act_code;
758 l_dleg_del_int_id(l_ship_unit_count) := l_local_release_tab(l_release_id).delivery_interface_id;
759 l_dleg_del_id(l_ship_unit_count) := l_release_id;
760 IF l_debug_on THEN
761 WSH_DEBUG_SV.logmsg(l_module_name,'After Using l_local_release_tab ');
762 END IF;
763
764 -- 2. derive stop_interface_id from location_id associative array
765
766 l_dleg_pustop_id(l_ship_unit_count) := l_stop_loc_tab(l_dleg_pu_loc_id);
767 l_dleg_dostop_id(l_ship_unit_count) := l_stop_loc_tab(l_dleg_do_loc_id);
768 IF l_debug_on THEN
769 WSH_DEBUG_SV.logmsg(l_module_name,'After Using l_stop_loc_tab ');
770 END IF;
771
772 -- 3. Mark the leg created flag for the current delivery_id as Yes
773
774 l_local_release_tab(l_release_id).leg_created_flag := 'Y';
775
776 <<next_shipunit>>
777
778 EXIT WHEN m = p_int_trip_info(i).shipment_ship_units.LAST;
779 m := p_int_trip_info(i).shipment_ship_units.NEXT(m);
780 END LOOP;
781 END IF;
785 END IF;
782
783 IF l_debug_on THEN
784 WSH_DEBUG_SV.logmsg(l_module_name,'Out of ship_units loop l_ship_unit_count : '||l_ship_unit_count);
786
787 END IF; -- Transaction_code <> 'DELETE'
788 <<next_trip>>
789
790 EXIT WHEN i = p_int_trip_info.LAST;
791 i := p_int_trip_info.NEXT(i);
792 END LOOP;
793 END IF;
794
795 IF l_ship_unit_count > 0 THEN
796
797 -- Bulk insert into wsh_del_legs_interface
798 IF l_debug_on THEN
799 WSH_DEBUG_SV.logmsg(l_module_name,'Inserting into wsh_del_legs_interface ');
800 END IF;
801
802 forall j in 1 .. l_ship_unit_count
803 Insert into WSH_DEL_LEGS_INTERFACE
804 (DELIVERY_LEG_INTERFACE_ID,
805 INTERFACE_ACTION_CODE,
806 DELIVERY_ID,
807 DELIVERY_INTERFACE_ID,
808 PICK_UP_STOP_INTERFACE_ID,
809 DROP_OFF_STOP_INTERFACE_ID,
810 CREATION_DATE,
811 CREATED_BY,
812 LAST_UPDATE_DATE,
813 LAST_UPDATED_BY)
814 Values (WSH_DEL_LEGS_INTERFACE_S.nextval,
815 l_dleg_int_act_code(j),
816 l_dleg_del_id(j),
817 l_dleg_del_int_id(j),
818 l_dleg_pustop_id(j),
819 l_dleg_dostop_id(j),
820 SYSDATE,
821 1,
822 SYSDATE,
823 1);
824 END IF; -- l_ship_unit_count > 0
825
826 COMMIT;
827
828 -- Launch Anshuman's concurrent program passing l_group_id as input
829 -- Launch only if atleast 1 trip was processed into interface table
830 IF l_group_id IS NOT NULL THEN
831 --{
832 -- Bug#7491598(ER,defer planned shipment iface): Check whether planned shipment is deferred or not.
833 --
834 IF l_debug_on THEN
835 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_SHIPPING_PARAMS_PVT.GET_GLOBAL_PARAMETERS',WSH_DEBUG_SV.C_PROC_LEVEL);
836 END IF;
837 WSH_SHIPPING_PARAMS_PVT.Get_Global_Parameters(x_Param_Info => l_global_params,
838 x_return_status => l_return_status);
839 IF l_debug_on THEN
840 WSH_DEBUG_SV.log(l_module_name,'L_RETURN_STATUS',l_return_status);
841 END IF;
842 IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
843 x_return_status := l_return_status;
844 IF l_debug_on THEN
845 WSH_DEBUG_SV.pop(l_module_name);
846 END IF;
847 RETURN;
848 END IF;
849 --
850 IF l_debug_on THEN
851 WSH_DEBUG_SV.log(l_module_name,'defer_planned_shipment_flag',l_global_params.defer_plan_shipment_interface);
852 END IF;
853 IF nvl(l_global_params.defer_plan_shipment_interface,'N') = 'N' THEN
854 --{
855
856 IF l_debug_on THEN
857 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit FND_REQUEST.Submit_Request',WSH_DEBUG_SV.C_PROC_LEVEL);
858 WSH_DEBUG_SV.log(l_module_name,'Current Time is ',SYSDATE);
859 END IF;
860 l_request_id := FND_REQUEST.Submit_Request(
861 application => 'WSH',
862 program => 'WSHOTMRL',
863 argument1 => l_group_id);
864 IF l_request_id = 0 THEN
865 -- If request submission failed, exit with error.
866 IF l_debug_on THEN
867 WSH_DEBUG_SV.logmsg(l_module_name,'Request submission failed ');
868 END IF;
869 x_msg_data := FND_MESSAGE.GET_STRING('WSH','WSH_OTM_IB_CONC_ERROR');
870 --x_msg_data := 'Concurrent Request submission failed';
871 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
872 ELSE
873 x_output_request_id := l_request_id;
874 IF l_debug_on THEN
875 WSH_DEBUG_SV.logmsg(l_module_name,'Request '||l_request_id||' submitted successfully');
876 END IF;
877 END IF;
878 --}
879 END IF;
880
881 ELSE
882 x_msg_data := FND_MESSAGE.GET_STRING('WSH','WSH_OTM_IB_NO_ELIG_DLVY');
883 --x_msg_data := 'No Eligible Deliveries Found';
884 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
885
886 END IF; -- l_group_id IS NOT NULL
887
888 IF l_debug_on THEN
889 WSH_DEBUG_SV.logmsg(l_module_name,'Returning x_return_status : '|| x_return_status);
890 WSH_DEBUG_SV.logmsg(l_module_name,'-------------- END ----------------');
891 WSH_DEBUG_SV.pop(l_module_name);
892 END IF;
893
894 EXCEPTION
895 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
896 ROLLBACK TO SAVEPOINT before_insert;
897 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
898 --
899 IF l_debug_on THEN
900 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
901 WSH_DEBUG_SV.logmsg(l_module_name,'-------------- END ----------------');
902 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
903 END IF;
904 --
905 x_msg_data := 'Oracle error message is '|| SQLERRM;
906 WHEN others THEN
907 ROLLBACK TO SAVEPOINT before_insert;
908 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
909 x_msg_data := 'Oracle error message is '|| SQLERRM;
910 WSH_UTIL_CORE.default_handler('WSH_OTM_INBOUND_GRP.initiate_planned_shipment');
911 --
912 IF l_debug_on THEN
913 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
914 WSH_DEBUG_SV.logmsg(l_module_name,'-------------- END ----------------');
915 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
916 END IF;
917 --
918 END initiate_planned_shipment;
919
920 END WSH_OTM_INBOUND_GRP;
921