[Home] [Help]
PACKAGE BODY: APPS.WSH_INTERFACE_GRP
Source
1 PACKAGE BODY WSH_INTERFACE_GRP as
2 /* $Header: WSHINGPB.pls 120.6 2007/11/16 06:32:22 sankarun noship $ */
3
4
5 --===================
6 -- CONSTANTS
7 --===================
8 G_PKG_NAME CONSTANT VARCHAR2(30) := 'WSH_INTERFACE_GRP';
9 -- add your constants here if any
10 --===================
11 -- PUBLIC VARS
12 --===================
13
14
15 PROCEDURE Rtrim_deliveries_action (
16 p_in_rec IN WSH_DELIVERIES_GRP.action_parameters_rectype,
17 p_out_rec OUT NOCOPY WSH_DELIVERIES_GRP.action_parameters_rectype) IS
18 l_debug_on BOOLEAN;
19 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' ||
20 G_PKG_NAME || '.' || 'Rtrim_deliveries_action';
21 BEGIN
22
23 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
24 --
25 IF l_debug_on IS NULL
26 THEN
27 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
28 END IF;
29 --
30 IF l_debug_on THEN
31 wsh_debug_sv.push (l_module_name, 'Rtrim_deliveries_action');
32 END IF;
33
34 p_out_rec := p_in_rec;
35
36 p_out_rec.caller := RTRIM(p_in_rec.caller);
37 p_out_rec.action_code := RTRIM(p_in_rec.action_code);
38 p_out_rec.trip_name := RTRIM(p_in_rec.trip_name);
39 p_out_rec.pickup_loc_code := RTRIM(p_in_rec.pickup_loc_code);
40 p_out_rec.pickup_stop_status := RTRIM(p_in_rec.pickup_stop_status);
41 p_out_rec.dropoff_loc_code := RTRIM(p_in_rec.dropoff_loc_code);
42 p_out_rec.dropoff_stop_status:= RTRIM(p_in_rec.dropoff_stop_status);
43 p_out_rec.action_flag := RTRIM(p_in_rec.action_flag);
44 p_out_rec.intransit_flag := RTRIM(p_in_rec.intransit_flag);
45 p_out_rec.close_trip_flag := RTRIM(p_in_rec.close_trip_flag);
46 --p_out_rec.create_bol_flag := RTRIM(p_in_rec.create_bol_flag);
47 p_out_rec.stage_del_flag := RTRIM(p_in_rec.stage_del_flag);
48 p_out_rec.bill_of_lading_flag := RTRIM(p_in_rec.bill_of_lading_flag);
49 p_out_rec.override_flag := RTRIM(p_in_rec.override_flag);
50 p_out_rec.defer_interface_flag := RTRIM(p_in_rec.defer_interface_flag);
51 p_out_rec.ship_method_code:= RTRIM(p_in_rec.ship_method_code);
52 p_out_rec.report_set_name:= RTRIM(p_in_rec.report_set_name);
53 p_out_rec.send_945_flag := RTRIM(p_in_rec.send_945_flag);
54 p_out_rec.sc_rule_name:= RTRIM(p_in_rec.sc_rule_name);
55 p_out_rec.action_type := RTRIM(p_in_rec.action_type);
56 p_out_rec.document_type:= RTRIM(p_in_rec.document_type);
57 p_out_rec.reason_of_transport := RTRIM(p_in_rec.reason_of_transport);
58 p_out_rec.description := RTRIM(p_in_rec.description);
59
60 IF l_debug_on THEN
61 WSH_DEBUG_SV.pop(l_module_name);
62 END IF;
63
64 EXCEPTION
65
66 WHEN OTHERS THEN
67 wsh_util_core.default_handler (
68 'WSH_TRIP_STOPS_GRP.Rtrim_deliveries_action', l_module_name);
69 IF l_debug_on THEN
70 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured.'||
71 ' Oracle error message is '|| SQLERRM,
72 WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
73 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
74 END IF;
75 RAISE;
76
77 END Rtrim_deliveries_action;
78
79
80
81 --========================================================================
82 -- PROCEDURE : Derive_sc_rule_details Internal API
83 --
84 -- Keep this a Local API for now
85 -- Ship Confirm Rule can be used from 11.5.10 onwards to Confirm a delivery
86 -- If Ship Confirm Rule is specified, derive all details from the Rule
87 -- If the delivery is not the last open on the trip, then trip options
88 -- should be ignored, this check should already be there in Group API
89 -- Ship Confirm Rule will not be picked by itself as it becomes in UI for
90 -- deliveries belonging to same Organizations.(we use rule specified in
91 -- Shipping Parameters).
92 -- User specified Ship Method,Report Set will also be ignored while using
93 -- SC Rule defaults
94 --========================================================================
95 PROCEDURE derive_sc_rule_details(p_rule_id IN NUMBER,
96 p_rule_name IN VARCHAR2,
97 p_delivery_id_tab IN wsh_util_core.id_tab_type,
98 x_out_params IN OUT NOCOPY WSH_DELIVERIES_GRP.action_parameters_rectype,
99 x_return_status OUT NOCOPY VARCHAR2) IS
100
101 -- Use Ship Confirm Rule for the Organization
102 -- Also, get the default values for the Rule
103 -- actual_dep_date_default is not used
104 -- Send_945 is no longer used
105
106 CURSOR get_sc_rule (p_rule_id NUMBER,p_rule_name VARCHAR2) IS
107 SELECT wsc.name,
108 wsc.ship_confirm_rule_id,
109 wsc.ac_intransit_flag,
110 wsc.ac_close_trip_flag,
111 wsc.ac_bol_flag,
112 wsc.ac_defer_interface_flag,
113 wsc.report_set_id,
114 NVL(wsc.ship_method_default_flag, 'R'), -- frontport bug 4310141
115 wsc.ship_method_code,
116 wsc.effective_end_date,
117 wsc.stage_del_flag,
118 wsc.action_flag
119 FROM wsh_ship_confirm_rules wsc
120 WHERE ((p_rule_id IS NOT NULL AND wsc.ship_confirm_rule_id = p_rule_id)
121 OR (p_rule_name IS NOT NULL AND wsc.name = p_rule_name))
122 AND (sysdate between wsc.effective_start_date
123 and nvl(wsc.effective_end_date,sysdate));
124
125 -- Derive Report Set Name
126 CURSOR Report_Set (p_report_set_id NUMBER) IS
127 SELECT rs.name
128 FROM wsh_report_sets rs
129 WHERE rs.report_set_id = p_report_set_id;
130
131 --frontport bug 4310141: check trip or delivery to default ship method.
132
133 CURSOR Check_Trip (l_delivery_id NUMBER) IS
134 select wts.trip_id
135 from wsh_delivery_legs wdl,
136 wsh_trip_stops wts
137 where wdl.pick_up_stop_id = wts.stop_id
138 and wdl.delivery_id = l_delivery_id
139 and rownum=1;
140
141 CURSOR delivery_ship_method (l_delivery_id NUMBER) IS
142 SELECT ship_method_code
143 FROM wsh_new_deliveries
144 WHERE delivery_id = l_delivery_id;
145
146 -- Bug 4103142 - to get first trip for delivery
147 CURSOR c_first_ship_method (p_delivery_id IN number)IS
148 SELECT wt.ship_method_code
149 FROM wsh_new_deliveries del,
150 wsh_delivery_legs dlg,
151 wsh_trip_stops st,
152 wsh_trips wt
153 WHERE del.delivery_id = dlg.delivery_id
154 AND dlg.pick_up_stop_id = st.stop_id
155 AND del.initial_pickup_location_id = st.stop_location_id
156 AND st.trip_id = wt.trip_id
157 AND del.delivery_id = p_delivery_id
158 AND rownum = 1 ;
159
160 l_sc_rule VARCHAR2(30);
161 l_sc_rule_id NUMBER;
162 l_ac_intransit_flag VARCHAR2(1);
163 l_ac_close_flag VARCHAR2(1);
164 l_ac_bol_flag VARCHAR2(1);
165 l_ac_defer_interface_flag VARCHAR2(1);
166 l_report_set_id NUMBER;
167 l_ship_method_default_flag VARCHAR2(1);
168 l_ship_method_code VARCHAR2(30);
169 l_effective_end_date DATE;
170 l_stage_del_flag VARCHAR2(1);
171 l_action_flag VARCHAR2(1);
172
173 l_trip_id NUMBER;
174 l_dist_ship_method_code VARCHAR2(30);
175 l_temp_ship_method_code VARCHAR2(30);
176 l_first_ship_method BOOLEAN;
177
178 l_api_version_number CONSTANT NUMBER := 1.0;
179 l_api_name CONSTANT VARCHAR2(30) := 'derive_sc_rule_details';
180 l_debug_on BOOLEAN;
181 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'DERIVE_SC_RULE_DETAILS';
182 BEGIN
183 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
184 --
185 IF l_debug_on IS NULL
186 THEN
187 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
188 END IF;
189 IF l_debug_on THEN
190 WSH_DEBUG_SV.push(l_module_name);
191 END IF;
192
193 x_return_status := wsh_util_core.g_ret_sts_success;
194
195 l_first_ship_method := TRUE;
196
197 IF (p_rule_id IS NOT NULL OR p_rule_name IS NOT NULL) THEN
198 OPEN get_sc_rule(p_rule_id,p_rule_name);
199 FETCH get_sc_rule
200 INTO l_sc_rule,
201 l_sc_rule_id,
202 l_ac_intransit_flag,
203 l_ac_close_flag,
204 l_ac_bol_flag,
205 l_ac_defer_interface_flag,
206 l_report_set_id,
207 l_ship_method_default_flag,
208 l_ship_method_code,
209 l_effective_end_date,
210 l_stage_del_flag,
211 l_action_flag;
212
213 IF get_sc_rule%NOTFOUND THEN
214 fnd_message.set_name('WSH','WSH_ACTIVE_SC_RULE');
215 CLOSE get_sc_rule;
216 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
217 IF l_debug_on THEN
218 WSH_DEBUG_SV.logmsg(l_module_name,'No Valid Rule found');
219 WSH_DEBUG_SV.pop(l_module_name);
220 END IF;
221 return;
222 ELSE
223 CLOSE get_sc_rule;
224 END IF;
225
226 IF l_debug_on THEN
227 WSH_DEBUG_SV.log(l_module_name,'Ship Confirm Rule',l_sc_rule);
228 WSH_DEBUG_SV.log(l_module_name,'Ship Confirm Rule id',l_sc_rule_id);
229 WSH_DEBUG_SV.log(l_module_name,'In transit',l_ac_intransit_flag);
230 WSH_DEBUG_SV.log(l_module_name,'Close',l_ac_close_flag);
231 WSH_DEBUG_SV.log(l_module_name,'BOL flag',l_ac_bol_flag);
232 WSH_DEBUG_SV.log(l_module_name,'Defer Interface flag',l_ac_defer_interface_flag);
233 WSH_DEBUG_SV.log(l_module_name,'SC report set',l_report_set_id);
234 WSH_DEBUG_SV.log(l_module_name,'SC ship method',l_ship_method_code);
235 WSH_DEBUG_SV.log(l_module_name,'SC ship method default flag',l_ship_method_default_flag);
236 WSH_DEBUG_SV.log(l_module_name,'Effective end date',l_effective_end_date);
237 WSH_DEBUG_SV.log(l_module_name,'Stage Del flag',l_stage_del_flag);
238 WSH_DEBUG_SV.log(l_module_name,'Action flag',l_action_flag);
239 END IF;
240
241 x_out_params.intransit_flag := l_ac_intransit_flag;
242 x_out_params.close_trip_flag := l_ac_close_flag;
243 x_out_params.report_set_id := l_report_set_id;
244 x_out_params.sc_rule_id := l_sc_rule_id;
245 x_out_params.sc_rule_name := l_sc_rule;
246 x_out_params.defer_interface_flag := l_ac_defer_interface_flag;
247 x_out_params.bill_of_lading_flag := l_ac_bol_flag;
248 x_out_params.stage_del_flag := l_stage_del_flag;
249 x_out_params.action_flag := l_action_flag;
250
251 IF x_out_params.report_set_id IS NOT NULL THEN
252 OPEN report_set(x_out_params.report_set_id);
253 FETCH report_set
254 INTO x_out_params.report_set_name;
255 IF report_set%NOTFOUND THEN
256 x_out_params.report_set_id := NULL;
257 x_out_params.report_set_name := NULL;
258 CLOSE report_set;
259 ELSE
260 CLOSE report_set;
261 END IF;
262 ELSE
263 x_out_params.report_set_name := NULL;
264 END IF;
265 END IF;
266
267
268 -- frontport bug 4319141
269 -- Bug 4234111 : Derive ship method based on the Ship confirm rule's
270 -- defaulting flag
271 IF l_ship_method_default_flag = 'R' THEN
272 x_out_params.ship_method_code := l_ship_method_code;
273 ELSE
274 l_dist_ship_method_code := NULL;
275 FOR i IN 1..p_delivery_id_tab.COUNT LOOP --{
276 -- Find trip for the delivery
277 l_trip_id := NULL;
278 OPEN Check_Trip( p_delivery_id_tab (i));
279 FETCH Check_Trip INTO l_trip_id;
280 IF Check_Trip%NOTFOUND THEN
281 l_trip_id := NULL;
282 END IF;
283 CLOSE Check_Trip;
284
285 -- Check to see if Ship Method has to be set
286 -- for Deliveries with Autocreate Trip
287 IF (l_trip_id IS NULL) THEN --{
288 OPEN delivery_ship_method (p_delivery_id_tab(i));
289 FETCH delivery_ship_method INTO l_ship_method_code;
290 CLOSE delivery_ship_method;
291 IF l_first_ship_method THEN
292 -- Initialize First Applicable Ship Method
293 l_dist_ship_method_code := l_ship_method_code;
294 l_first_ship_method := FALSE;
295 END IF;
296 l_temp_ship_method_code := l_ship_method_code;
297 IF NVL(l_temp_ship_method_code,' ') <> NVL(l_dist_ship_method_code,' ') THEN
298 -- Ship Methods are different for Deliveries,
299 -- so Null Ship Method is returned
300 IF l_debug_on THEN
301 WSH_DEBUG_SV.logmsg(l_module_name,'Ship Methods are different');
302 END IF;
303 l_dist_ship_method_code := NULL;
304 l_ship_method_code := NULL;
305 EXIT;
306 END IF;
307 END IF; --}
308 END LOOP; --}
309
310 -- Defaulted ship method from first trip if there is only a single delivery
311 IF (p_delivery_id_tab.count = 1) THEN
312 OPEN c_first_ship_method (p_delivery_id_tab(1));
313 FETCH c_first_ship_method INTO l_temp_ship_method_code;
314 IF c_first_ship_method%NOTFOUND THEN
315 l_temp_ship_method_code := NULL;
316 ELSE
317 IF l_temp_ship_method_code IS NOT NULL THEN
318 l_ship_method_code := l_temp_ship_method_code;
319 END IF;
320 END IF;
321 CLOSE c_first_ship_method;
322 END IF;
323 END IF;
324 x_out_params.ship_method_code := l_ship_method_code;
325
326 IF l_debug_on THEN
327 WSH_DEBUG_SV.log(l_module_name, 'x_out_params.ship_method_code', x_out_params.ship_method_code);
328 WSH_DEBUG_SV.pop(l_module_name);
329 END IF;
330
331 EXCEPTION
332
333 WHEN others THEN
334 wsh_util_core.default_handler('WSH_DELIVERY_LEGS_GRP.derive_sc_rule_details',l_module_name);
335 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
336 --
337 IF l_debug_on THEN
338 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
339 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
340 END IF;
341
342 END derive_sc_rule_details;
343
344 --========================================================================
345 -- PROCEDURE : Delivery_Action Wrapper API PUBLIC
346 --
347 -- PARAMETERS: p_api_version_number known api version error number
348 -- p_init_msg_list FND_API.G_TRUE to reset list
349 -- x_return_status return status
350 -- x_msg_count number of messages in the list
351 -- x_msg_data text of messages
352 -- p_action_prms Record of caller, phase, action_code and other
353 -- parameters specific to the actions.
354 -- p_rec_attr_tab Table of Attributes for the delivery entity
355 -- x_delivery_out_rec Record of output parameters based on the actions.
356 -- VERSION : current version 1.0
357 -- initial version 1.0
358 -- COMMENT : This procedure is used to perform an action specified
359 -- in p_action_prms.action_code on an existing delivery identified
360 -- by p_rec_attr.delivery_id/p_rec_attr.name.
361 --========================================================================
362 PROCEDURE Delivery_Action
363 ( p_api_version_number IN NUMBER,
364 p_init_msg_list IN VARCHAR2,
365 p_commit IN VARCHAR2 , -- default fnd_api.g_false
366 p_action_prms IN WSH_DELIVERIES_GRP.action_parameters_rectype,
367 p_delivery_id_tab IN wsh_util_core.id_tab_type,
368 x_delivery_out_rec OUT NOCOPY WSH_DELIVERIES_GRP.Delivery_Action_Out_Rec_Type,
369 x_return_status OUT NOCOPY VARCHAR2,
370 x_msg_count OUT NOCOPY NUMBER,
371 x_msg_data OUT NOCOPY VARCHAR2)
372 IS
373 --
374 l_num_warnings NUMBER := 0;
375 l_num_errors NUMBER := 0;
376 l_index NUMBER;
377 l_commit VARCHAR2(100) := FND_API.G_FALSE;
378 l_init_msg_list VARCHAR2(100) := FND_API.G_FALSE;
379 --
380 l_defaults_rec wsh_deliveries_grp.default_parameters_rectype;
381 --
382 l_rec_attr_tab wsh_new_deliveries_pvt.Delivery_Attr_Tbl_Type;
383 l_action_prms WSH_DELIVERIES_GRP.action_parameters_rectype;
384 --
385 l_debug_on BOOLEAN;
386 --
387 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'DELIVERY_ACTION_WRAPPER';
388 --
389 CURSOR l_dlvy_attr_csr( p_delivery_id IN NUMBER ) IS
390 SELECT
391 DELIVERY_ID
392 , ORGANIZATION_ID
393 , STATUS_CODE
394 , PLANNED_FLAG
395 , NAME
396 , INITIAL_PICKUP_DATE
397 , INITIAL_PICKUP_LOCATION_ID
398 , ULTIMATE_DROPOFF_LOCATION_ID
399 , ULTIMATE_DROPOFF_DATE
400 , CUSTOMER_ID
401 , INTMED_SHIP_TO_LOCATION_ID
402 , SHIP_METHOD_CODE
403 , DELIVERY_TYPE
404 , CARRIER_ID
405 , SERVICE_LEVEL
406 , MODE_OF_TRANSPORT
407 , shipment_direction
408 , party_id
409 , shipping_control
410 --Added for bug 6625788
411 , reason_of_transport
412 , description
413 FROM WSH_NEW_DELIVERIES
414 WHERE delivery_id = p_delivery_id;
415 --
416 BEGIN
417 --
418 --
419 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
420 --
421 IF l_debug_on IS NULL
422 THEN
423 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
424 END IF;
425 --
426 SAVEPOINT DELIVERY_ACTION_WRAPPER_GRP;
427 --
428 IF l_debug_on THEN
429 wsh_debug_sv.push(l_module_name);
430 wsh_debug_sv.log(l_module_name,'Caller is ', p_action_prms.caller);
431 wsh_debug_sv.log(l_module_name,'Phase is ', p_action_prms.phase);
432 wsh_debug_sv.log(l_module_name,'Action Code is ', p_action_prms.phase);
433 wsh_debug_sv.log(l_module_name,'Total Number of Delivery Records', p_delivery_id_tab.COUNT);
434 END IF;
435 --
436 IF FND_API.to_Boolean( p_init_msg_list ) THEN
437 FND_MSG_PUB.initialize;
438 END IF;
439 --
440 Rtrim_deliveries_action(p_action_prms,l_action_prms);
441
442 -- For CONFIRM action, Ship Confirm Rule can be used from 11.5.10 onwards
443 IF((p_action_prms.sc_rule_id IS NOT NULL OR p_action_prms.sc_rule_name IS NOT NULL)
444 AND p_action_prms.action_code = 'CONFIRM') THEN
445 derive_sc_rule_details(p_rule_id => l_action_prms.sc_rule_id,
446 p_rule_name => l_action_prms.sc_rule_name,
447 p_delivery_id_tab => p_delivery_id_tab,
448 x_out_params => l_action_prms,
449 x_return_status => x_return_status);
450 IF l_debug_on THEN
451 wsh_debug_sv.log (l_module_name,'After deriving SC Rule details', x_return_status);
452 wsh_debug_sv.log (l_module_name,'x_return_status', x_return_status);
453 END IF;
454
455 wsh_util_core.api_post_call(
456 p_return_status =>x_return_status,
457 x_num_warnings =>l_num_warnings,
458 x_num_errors =>l_num_errors);
459 END IF;
460 -- End of Ship Confirm Rule Changes
461
462 l_index := p_delivery_id_tab.FIRST;
463 while l_index is not null loop
464 open l_dlvy_attr_csr(p_delivery_id_tab(l_index));
465 fetch l_dlvy_attr_csr
466 into l_rec_attr_tab(l_index).delivery_id,
467 l_rec_attr_tab(l_index).organization_id,
468 l_rec_attr_tab(l_index).status_code,
469 l_rec_attr_tab(l_index).planned_flag,
470 l_rec_attr_tab(l_index).NAME,
471 l_rec_attr_tab(l_index).INITIAL_PICKUP_DATE,
472 l_rec_attr_tab(l_index).INITIAL_PICKUP_LOCATION_ID,
473 l_rec_attr_tab(l_index).ULTIMATE_DROPOFF_LOCATION_ID,
474 l_rec_attr_tab(l_index).ULTIMATE_DROPOFF_DATE,
475 l_rec_attr_tab(l_index).CUSTOMER_ID,
476 l_rec_attr_tab(l_index).INTMED_SHIP_TO_LOCATION_ID,
477 l_rec_attr_tab(l_index).SHIP_METHOD_CODE,
478 l_rec_attr_tab(l_index).DELIVERY_TYPE,
479 l_rec_attr_tab(l_index).CARRIER_ID,
480 l_rec_attr_tab(l_index).SERVICE_LEVEL,
481 l_rec_attr_tab(l_index).MODE_OF_TRANSPORT,
482 l_rec_attr_tab(l_index).shipment_direction,
483 l_rec_attr_tab(l_index).party_id,
484 l_rec_attr_tab(l_index).shipping_control,
485 --Bug 6625788
486 l_rec_attr_tab(l_index).reason_of_transport,
487 l_rec_attr_tab(l_index).description;
488
489
490 close l_dlvy_attr_csr;
491 l_index := p_delivery_id_tab.NEXT(l_index);
492 end loop;
493 --
494 wsh_deliveries_grp.Delivery_Action(
495 p_api_version_number => p_api_version_number,
496 p_init_msg_list => l_init_msg_list,
497 p_commit => l_commit,
498 p_action_prms => l_action_prms,
499 p_rec_attr_tab => l_rec_attr_tab,
500 x_delivery_out_rec => x_delivery_out_rec,
501 x_defaults_rec => l_defaults_rec,
502 x_return_status => x_return_status,
503 x_msg_count => x_msg_count,
504 x_msg_data => x_msg_data);
505 --
506 IF l_debug_on THEN
507 wsh_debug_sv.log (l_module_name,'x_return_status', x_return_status);
508 END IF;
509
510 wsh_util_core.api_post_call(
511 p_return_status =>x_return_status,
512 x_num_warnings =>l_num_warnings,
513 x_num_errors =>l_num_errors);
514
515 IF l_num_warnings > 0 THEN
516 RAISE WSH_UTIL_CORE.G_EXC_WARNING;
517 END IF;
518 --
519 IF FND_API.To_Boolean( p_commit ) THEN
520 COMMIT WORK;
521 END IF;
522 --
523 FND_MSG_PUB.Count_And_Get (
524 p_count => x_msg_count,
525 p_data => x_msg_data);
526 --
527 IF l_debug_on THEN
528 WSH_DEBUG_SV.pop(l_module_name);
529 END IF;
530 --
531 EXCEPTION
532 WHEN FND_API.G_EXC_ERROR THEN
533 ROLLBACK TO DELIVERY_ACTION_WRAPPER_GRP;
534 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
535 FND_MSG_PUB.Count_And_Get
536 (
537 p_count => x_msg_count,
538 p_data => x_msg_data,
539 p_encoded => FND_API.G_FALSE
540 );
541 --
542 IF l_debug_on THEN
543 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured ',WSH_DEBUG_SV.C_EXCEP_LEVEL);
544 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
545 END IF;
546 --
547 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
548 ROLLBACK TO DELIVERY_ACTION_WRAPPER_GRP;
549 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
550 FND_MSG_PUB.Count_And_Get
551 (
552 p_count => x_msg_count,
553 p_data => x_msg_data,
554 p_encoded => FND_API.G_FALSE
555 );
556 --
557 IF l_debug_on THEN
558 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
559 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
560 END IF;
561 --
562 WHEN WSH_UTIL_CORE.G_EXC_WARNING THEN
563 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
564 FND_MSG_PUB.Count_And_Get
565 (
566 p_count => x_msg_count,
567 p_data => x_msg_data,
568 p_encoded => FND_API.G_FALSE
569 );
570 --
571 IF l_debug_on THEN
572 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_UTIL_CORE.G_EXC_WARNING exception has occured ',WSH_DEBUG_SV.C_EXCEP_LEVEL);
573 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_UTIL_CORE.G_EXC_WARNING');
574 END IF;
575 --
576 WHEN OTHERS THEN
577 ROLLBACK TO DELIVERY_ACTION_WRAPPER_GRP;
578 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
579 wsh_util_core.default_handler('WSH_INTERFACE_GRP.DELIVERY_ACTION');
580 FND_MSG_PUB.Count_And_Get
581 (
582 p_count => x_msg_count,
583 p_data => x_msg_data,
584 p_encoded => FND_API.G_FALSE
585 );
586 --
587 IF l_debug_on THEN
588 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
589 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
590 END IF;
591 --
592 END Delivery_Action;
593
594
595 PROCEDURE Rtrim_deliveries_blank_space (
596 p_in_rec IN WSH_NEW_DELIVERIES_PVT.Delivery_Rec_Type,
597 p_out_rec OUT NOCOPY WSH_NEW_DELIVERIES_PVT.Delivery_Rec_Type) IS
598 l_debug_on BOOLEAN;
599 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' ||
600 G_PKG_NAME || '.' || 'Rtrim_deliveries_blank_space';
601 BEGIN
602
603 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
604 --
605 IF l_debug_on IS NULL
606 THEN
607 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
608 END IF;
609 --
610 IF l_debug_on THEN
611 wsh_debug_sv.push (l_module_name, 'Rtrim_deliveries_blank_space');
612 END IF;
613
614 p_out_rec := p_in_rec;
615
616 p_out_rec.NAME := RTRIM(p_in_rec.NAME);
617 p_out_rec.DELIVERY_TYPE := RTRIM(p_in_rec.DELIVERY_TYPE);
618 p_out_rec.LOADING_ORDER_FLAG := RTRIM(p_in_rec.LOADING_ORDER_FLAG);
619 p_out_rec.LOADING_ORDER_DESC := RTRIM(p_in_rec.LOADING_ORDER_DESC);
620 p_out_rec.INITIAL_PICKUP_LOCATION_CODE :=
621 RTRIM(p_in_rec.INITIAL_PICKUP_LOCATION_CODE);
622 p_out_rec.ORGANIZATION_CODE := RTRIM(p_in_rec.ORGANIZATION_CODE);
623 p_out_rec.ULTIMATE_DROPOFF_LOCATION_CODE :=
624 RTRIM(p_in_rec.ULTIMATE_DROPOFF_LOCATION_CODE);
625 p_out_rec.CUSTOMER_NUMBER := RTRIM(p_in_rec.CUSTOMER_NUMBER);
626 p_out_rec.INTMED_SHIP_TO_LOCATION_CODE :=
627 RTRIM(p_in_rec.INTMED_SHIP_TO_LOCATION_CODE);
628 p_out_rec.POOLED_SHIP_TO_LOCATION_CODE:=
629 RTRIM(p_in_rec.POOLED_SHIP_TO_LOCATION_CODE);
630 p_out_rec.CARRIER_CODE := RTRIM(p_in_rec.CARRIER_CODE);
631 p_out_rec.SHIP_METHOD_CODE := RTRIM(p_in_rec.SHIP_METHOD_CODE);
632 p_out_rec.SHIP_METHOD_NAME := RTRIM(p_in_rec.SHIP_METHOD_NAME);
633 p_out_rec.FREIGHT_TERMS_CODE := RTRIM(p_in_rec.FREIGHT_TERMS_CODE);
634 p_out_rec.FOB_CODE := RTRIM(p_in_rec.FOB_CODE);
635 p_out_rec.FOB_NAME := RTRIM(p_in_rec.FOB_NAME);
636 p_out_rec.FOB_LOCATION_CODE:= RTRIM(p_in_rec.FOB_LOCATION_CODE);
637 p_out_rec.WAYBILL := RTRIM(p_in_rec.WAYBILL);
638 p_out_rec.DOCK_CODE := RTRIM(p_in_rec.DOCK_CODE);
639 p_out_rec.ACCEPTANCE_FLAG := RTRIM(p_in_rec.ACCEPTANCE_FLAG);
640 p_out_rec.ACCEPTED_BY := RTRIM(p_in_rec.ACCEPTED_BY);
641 p_out_rec.ACKNOWLEDGED_BY:= RTRIM(p_in_rec.ACKNOWLEDGED_BY);
642 p_out_rec.CONFIRMED_BY := RTRIM(p_in_rec.CONFIRMED_BY);
643 p_out_rec.ASN_STATUS_CODE := RTRIM(p_in_rec.ASN_STATUS_CODE);
644 p_out_rec.WEIGHT_UOM_CODE := RTRIM(p_in_rec.WEIGHT_UOM_CODE);
645 p_out_rec.WEIGHT_UOM_DESC := RTRIM(p_in_rec.WEIGHT_UOM_DESC);
646 p_out_rec.VOLUME_UOM_CODE := RTRIM(p_in_rec.VOLUME_UOM_CODE);
647 p_out_rec.VOLUME_UOM_DESC := RTRIM(p_in_rec.VOLUME_UOM_DESC);
648 p_out_rec.ADDITIONAL_SHIPMENT_INFO:=
649 RTRIM(p_in_rec.ADDITIONAL_SHIPMENT_INFO);
650 p_out_rec.CURRENCY_CODE := RTRIM(p_in_rec.CURRENCY_CODE);
651 p_out_rec.CURRENCY_NAME := RTRIM(p_in_rec.CURRENCY_NAME);
652 p_out_rec.ATTRIBUTE_CATEGORY := RTRIM(p_in_rec.ATTRIBUTE_CATEGORY);
653 p_out_rec.ATTRIBUTE1 := RTRIM(p_in_rec.ATTRIBUTE1);
654 p_out_rec.ATTRIBUTE2 := RTRIM(p_in_rec.ATTRIBUTE2);
655 p_out_rec.ATTRIBUTE3 := RTRIM(p_in_rec.ATTRIBUTE3);
656 p_out_rec.ATTRIBUTE4 := RTRIM(p_in_rec.ATTRIBUTE4);
657 p_out_rec.ATTRIBUTE5 := RTRIM(p_in_rec.ATTRIBUTE5);
658 p_out_rec.ATTRIBUTE6 := RTRIM(p_in_rec.ATTRIBUTE6);
659 p_out_rec.ATTRIBUTE7 := RTRIM(p_in_rec.ATTRIBUTE7);
660 p_out_rec.ATTRIBUTE8 := RTRIM(p_in_rec.ATTRIBUTE8);
661 p_out_rec.ATTRIBUTE9 := RTRIM(p_in_rec.ATTRIBUTE9);
662 p_out_rec.ATTRIBUTE10 := RTRIM(p_in_rec.ATTRIBUTE10);
663 p_out_rec.ATTRIBUTE11 := RTRIM(p_in_rec.ATTRIBUTE11);
664 p_out_rec.ATTRIBUTE12:= RTRIM(p_in_rec.ATTRIBUTE12);
665 p_out_rec.ATTRIBUTE13 := RTRIM(p_in_rec.ATTRIBUTE13);
666 p_out_rec.ATTRIBUTE14 := RTRIM(p_in_rec.ATTRIBUTE14);
667 p_out_rec.ATTRIBUTE15 := RTRIM(p_in_rec.ATTRIBUTE15);
668 p_out_rec.TP_ATTRIBUTE_CATEGORY := RTRIM(p_in_rec.TP_ATTRIBUTE_CATEGORY);
669 p_out_rec.TP_ATTRIBUTE1 := RTRIM(p_in_rec.TP_ATTRIBUTE1);
670 p_out_rec.TP_ATTRIBUTE2 := RTRIM(p_in_rec.TP_ATTRIBUTE2);
671 p_out_rec.TP_ATTRIBUTE3 := RTRIM(p_in_rec.TP_ATTRIBUTE3);
672 p_out_rec.TP_ATTRIBUTE4 := RTRIM(p_in_rec.TP_ATTRIBUTE4);
673 p_out_rec.TP_ATTRIBUTE5 := RTRIM(p_in_rec.TP_ATTRIBUTE5);
674 p_out_rec.TP_ATTRIBUTE6 := RTRIM(p_in_rec.TP_ATTRIBUTE6);
675 p_out_rec.TP_ATTRIBUTE7 := RTRIM(p_in_rec.TP_ATTRIBUTE7);
676 p_out_rec.TP_ATTRIBUTE8 := RTRIM(p_in_rec.TP_ATTRIBUTE8);
677 p_out_rec.TP_ATTRIBUTE9 := RTRIM(p_in_rec.TP_ATTRIBUTE9);
678 p_out_rec.TP_ATTRIBUTE10 := RTRIM(p_in_rec.TP_ATTRIBUTE10);
679 p_out_rec.TP_ATTRIBUTE11:= RTRIM(p_in_rec.TP_ATTRIBUTE11);
680 p_out_rec.TP_ATTRIBUTE12 := RTRIM(p_in_rec.TP_ATTRIBUTE12);
681 p_out_rec.TP_ATTRIBUTE13 := RTRIM(p_in_rec.TP_ATTRIBUTE13);
682 p_out_rec.TP_ATTRIBUTE14:= RTRIM(p_in_rec.TP_ATTRIBUTE14);
683 p_out_rec.TP_ATTRIBUTE15 := RTRIM(p_in_rec.TP_ATTRIBUTE15);
684 p_out_rec.GLOBAL_ATTRIBUTE_CATEGORY:= RTRIM(p_in_rec.GLOBAL_ATTRIBUTE_CATEGORY);
685 p_out_rec.GLOBAL_ATTRIBUTE1 := RTRIM(p_in_rec.GLOBAL_ATTRIBUTE1);
686 p_out_rec.GLOBAL_ATTRIBUTE2 := RTRIM(p_in_rec.GLOBAL_ATTRIBUTE2);
687 p_out_rec.GLOBAL_ATTRIBUTE3 := RTRIM(p_in_rec.GLOBAL_ATTRIBUTE3);
688 p_out_rec.GLOBAL_ATTRIBUTE4 := RTRIM(p_in_rec.GLOBAL_ATTRIBUTE4);
689 p_out_rec.GLOBAL_ATTRIBUTE5 := RTRIM(p_in_rec.GLOBAL_ATTRIBUTE5);
690 p_out_rec.GLOBAL_ATTRIBUTE6 := RTRIM(p_in_rec.GLOBAL_ATTRIBUTE6);
691 p_out_rec.GLOBAL_ATTRIBUTE7 := RTRIM(p_in_rec.GLOBAL_ATTRIBUTE7);
692 p_out_rec.GLOBAL_ATTRIBUTE8 := RTRIM(p_in_rec.GLOBAL_ATTRIBUTE8);
693 p_out_rec.GLOBAL_ATTRIBUTE9 := RTRIM(p_in_rec.GLOBAL_ATTRIBUTE9);
694 p_out_rec.GLOBAL_ATTRIBUTE10 := RTRIM(p_in_rec.GLOBAL_ATTRIBUTE10);
695 p_out_rec.GLOBAL_ATTRIBUTE11 := RTRIM(p_in_rec.GLOBAL_ATTRIBUTE11);
696 p_out_rec.GLOBAL_ATTRIBUTE12:= RTRIM(p_in_rec.GLOBAL_ATTRIBUTE12);
697 p_out_rec.GLOBAL_ATTRIBUTE13 := RTRIM(p_in_rec.GLOBAL_ATTRIBUTE13);
698 p_out_rec.GLOBAL_ATTRIBUTE14 := RTRIM(p_in_rec.GLOBAL_ATTRIBUTE14);
699 p_out_rec.GLOBAL_ATTRIBUTE15 := RTRIM(p_in_rec.GLOBAL_ATTRIBUTE15);
700 p_out_rec.GLOBAL_ATTRIBUTE16 := RTRIM(p_in_rec.GLOBAL_ATTRIBUTE16);
701 p_out_rec.GLOBAL_ATTRIBUTE17 := RTRIM(p_in_rec.GLOBAL_ATTRIBUTE17);
702 p_out_rec.GLOBAL_ATTRIBUTE18 := RTRIM(p_in_rec.GLOBAL_ATTRIBUTE18);
703 p_out_rec.GLOBAL_ATTRIBUTE19 := RTRIM(p_in_rec.GLOBAL_ATTRIBUTE19);
704 p_out_rec.GLOBAL_ATTRIBUTE20 := RTRIM(p_in_rec.GLOBAL_ATTRIBUTE20);
705 p_out_rec.COD_CURRENCY_CODE := RTRIM(p_in_rec.COD_CURRENCY_CODE);
706 p_out_rec.COD_REMIT_TO := RTRIM(p_in_rec.COD_REMIT_TO);
707 p_out_rec.COD_CHARGE_PAID_BY := RTRIM(p_in_rec.COD_CHARGE_PAID_BY);
708 p_out_rec.PROBLEM_CONTACT_REFERENCE:= RTRIM(p_in_rec.PROBLEM_CONTACT_REFERENCE);
709 p_out_rec.PORT_OF_LOADING := RTRIM(p_in_rec.PORT_OF_LOADING);
710 p_out_rec.PORT_OF_DISCHARGE := RTRIM(p_in_rec.PORT_OF_DISCHARGE);
711 p_out_rec.FTZ_NUMBER := RTRIM(p_in_rec.FTZ_NUMBER);
712 p_out_rec.ROUTED_EXPORT_TXN := RTRIM(p_in_rec.ROUTED_EXPORT_TXN);
713 p_out_rec.ENTRY_NUMBER := RTRIM(p_in_rec.ENTRY_NUMBER);
714 p_out_rec.ROUTING_INSTRUCTIONS := RTRIM(p_in_rec.ROUTING_INSTRUCTIONS);
715 p_out_rec.IN_BOND_CODE := RTRIM(p_in_rec.IN_BOND_CODE);
716 p_out_rec.SHIPPING_MARKS := RTRIM(p_in_rec.SHIPPING_MARKS);
717 p_out_rec.SERVICE_LEVEL := RTRIM(p_in_rec.SERVICE_LEVEL);
718 p_out_rec.MODE_OF_TRANSPORT:= RTRIM(p_in_rec.MODE_OF_TRANSPORT);
719 p_out_rec.ASSIGNED_TO_FTE_TRIPS := RTRIM(p_in_rec.ASSIGNED_TO_FTE_TRIPS);
720 p_out_rec.AUTO_SC_EXCLUDE_FLAG := RTRIM(p_in_rec.AUTO_SC_EXCLUDE_FLAG);
721 p_out_rec.AUTO_AP_EXCLUDE_FLAG := RTRIM(p_in_rec.AUTO_AP_EXCLUDE_FLAG);
722 /*3667348*/
723 p_out_rec.REASON_OF_TRANSPORT := RTRIM(p_in_rec.REASON_OF_TRANSPORT);
724 p_out_rec.DESCRIPTION := RTRIM(p_in_rec.DESCRIPTION);
725 IF l_debug_on THEN
726 WSH_DEBUG_SV.pop(l_module_name);
727 END IF;
728
729 EXCEPTION
730
731 WHEN OTHERS THEN
732 wsh_util_core.default_handler (
733 'WSH_TRIP_STOPS_GRP.Rtrim_deliveries_blank_space', l_module_name);
734 IF l_debug_on THEN
735 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured.'||
736 ' Oracle error message is '|| SQLERRM,
737 WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
738 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
739 END IF;
740 RAISE;
741
742 END Rtrim_deliveries_blank_space;
743 --========================================================================
744
745 -- I Harmonization: rvishnuv ******* Actions ******
746
747 -- I Harmonization: rvishnuv ******* Create/Update ******
748 --========================================================================
749 -- PROCEDURE : Create_Update_Delivery Wrapper API PUBLIC
750 --
751 -- PARAMETERS: p_api_version_number known api version error buffer
752 -- p_init_msg_list FND_API.G_TRUE to reset list
753 -- x_return_status return status
754 -- x_msg_count number of messages in the list
755 -- x_msg_data text of messages
756 -- p_in_rec Record for caller, phase
757 -- and action_code ( CREATE-UPDATE )
758 -- p_rec_attr_tab Table of Attributes for the delivery entity
759 -- x_del_out_rec Record of delivery_id, and name of new deliveries,
760 -- VERSION : current version 1.0
761 -- initial version 1.0
762 -- COMMENT : Creates or updates a record in wsh_new_deliveries table with information
763 -- specified in p_delivery_info
764 --========================================================================
765 PROCEDURE Create_Update_Delivery
766 ( p_api_version_number IN NUMBER,
767 p_init_msg_list IN VARCHAR2,
768 p_commit IN VARCHAR2 , -- default fnd_api.g_false
769 p_in_rec IN WSH_DELIVERIES_GRP.Del_In_Rec_Type,
770 p_rec_attr_tab IN WSH_NEW_DELIVERIES_PVT.Delivery_Attr_Tbl_Type,
771 x_del_out_rec_tab OUT NOCOPY WSH_DELIVERIES_GRP.Del_Out_Tbl_Type,
772 x_return_status OUT NOCOPY VARCHAR2,
773 x_msg_count OUT NOCOPY NUMBER,
774 x_msg_data OUT NOCOPY VARCHAR2)
775 IS
776 --
777 l_num_warnings NUMBER := 0;
778 l_num_errors NUMBER := 0;
779 --
780 --l_rec_attr_tab wsh_new_deliveries_pvt.Delivery_Attr_Tbl_Type;
781 --l_del_out_rec_tab wsh_delveries_grp.Del_Out_Tbl_Type;
782 --
783 l_debug_on BOOLEAN;
784 --
785 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'CREATE_UPDATE_DELIVERY_WRAPPER';
786 l_commit VARCHAR2(100) := FND_API.G_FALSE;
787 l_init_msg_list VARCHAR2(100) := FND_API.G_FALSE;
788 l_rec_attr_tab WSH_NEW_DELIVERIES_PVT.Delivery_Attr_Tbl_Type;
789 l_index NUMBER;
790 l_in_rec WSH_DELIVERIES_GRP.Del_In_Rec_Type;
791 --
792 BEGIN
793 --
794 --
795 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
796 --
797 IF l_debug_on IS NULL
798 THEN
799 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
800 END IF;
801 --
802 SAVEPOINT CREATE_UPDATE_DEL_WRAP_GRP;
803 --
804 IF l_debug_on THEN
805 wsh_debug_sv.push(l_module_name);
806 wsh_debug_sv.log(l_module_name,'Caller is ', p_in_rec.caller);
807 wsh_debug_sv.log(l_module_name,'Phase is ', p_in_rec.phase);
808 wsh_debug_sv.log(l_module_name,'Action Code is ', p_in_rec.phase);
809 wsh_debug_sv.log(l_module_name,'Number of Records is ', p_rec_attr_tab.COUNT);
810 END IF;
811 --
812 IF FND_API.to_Boolean( p_init_msg_list ) THEN
813 FND_MSG_PUB.initialize;
814 END IF;
815 --
816 l_in_rec := p_in_rec;
817 l_in_rec.caller := RTRIM(p_in_rec.caller);
818 l_in_rec.action_code := RTRIM(p_in_rec.action_code);
819
820 l_index := p_rec_attr_tab.FIRST;
821 WHILE l_index IS NOT NULL LOOP
822 Rtrim_deliveries_blank_space(p_rec_attr_tab(l_index),
823 l_rec_attr_tab(l_index));
824 l_index := p_rec_attr_tab.NEXT(l_index);
825 END LOOP;
826
827 WSH_DELIVERIES_GRP.Create_Update_Delivery(
828 p_api_version_number => p_api_version_number,
829 p_init_msg_list => l_init_msg_list,
830 p_commit => l_commit,
831 p_in_rec => l_in_rec,
832 p_rec_attr_tab => l_rec_attr_tab,
833 x_del_out_rec_tab => x_del_out_rec_tab,
834 x_return_status => x_return_status,
835 x_msg_count => x_msg_count,
836 x_msg_data => x_msg_data);
837 --
838 IF l_debug_on THEN
839 wsh_debug_sv.log (l_module_name,'x_return_status', x_return_status);
840 END IF;
841 --
842 wsh_util_core.api_post_call(
843 p_return_status =>x_return_status,
844 x_num_warnings =>l_num_warnings,
845 x_num_errors =>l_num_errors);
846 --
847 IF l_num_warnings > 0 THEN
848 RAISE WSH_UTIL_CORE.G_EXC_WARNING;
849 END IF;
850 --
851 IF FND_API.To_Boolean( p_commit ) THEN
852 COMMIT WORK;
853 END IF;
854 --
855 FND_MSG_PUB.Count_And_Get (
856 p_count => x_msg_count,
857 p_data => x_msg_data);
858 --
859 IF l_debug_on THEN
860 WSH_DEBUG_SV.pop(l_module_name);
861 END IF;
862 --
863 EXCEPTION
864 WHEN FND_API.G_EXC_ERROR THEN
865 ROLLBACK TO CREATE_UPDATE_DEL_WRAP_GRP;
866 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
867 FND_MSG_PUB.Count_And_Get
868 (
869 p_count => x_msg_count,
870 p_data => x_msg_data,
871 p_encoded => FND_API.G_FALSE
872 );
873 --
874 IF l_debug_on THEN
875 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured ',WSH_DEBUG_SV.C_EXCEP_LEVEL);
876 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
877 END IF;
878 --
879 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
880 ROLLBACK TO CREATE_UPDATE_DEL_WRAP_GRP;
881 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
882 FND_MSG_PUB.Count_And_Get
883 (
884 p_count => x_msg_count,
885 p_data => x_msg_data,
886 p_encoded => FND_API.G_FALSE
887 );
888 --
889 IF l_debug_on THEN
890 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
891 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
892 END IF;
893 --
894 WHEN WSH_UTIL_CORE.G_EXC_WARNING THEN
895 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
896 FND_MSG_PUB.Count_And_Get
897 (
898 p_count => x_msg_count,
899 p_data => x_msg_data,
900 p_encoded => FND_API.G_FALSE
901 );
902 --
903 IF l_debug_on THEN
904 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_UTIL_CORE.G_EXC_WARNING exception has occured ',WSH_DEBUG_SV.C_EXCEP_LEVEL);
905 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_UTIL_CORE.G_EXC_WARNING');
906 END IF;
907 --
908 WHEN OTHERS THEN
909 ROLLBACK TO CREATE_UPDATE_DEL_WRAP_GRP;
910 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
911 wsh_util_core.default_handler('WSH_INTERFACE_GRP.CREATE_UPDATE_DELIVERY');
912 FND_MSG_PUB.Count_And_Get
913 (
914 p_count => x_msg_count,
915 p_data => x_msg_data,
916 p_encoded => FND_API.G_FALSE
917 );
918 --
919 IF l_debug_on THEN
920 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
921 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
922 END IF;
923 --
924
925 END Create_Update_Delivery;
926 --========================================================================
927
928 -- I Harmonization: rvishnuv ******* Create/Update ******
929
930
931 -- ---------------------------------------------------------------------
932 -- Procedure: Delivery_Detail_Action Wrapper API
933 --
934 -- Parameters:
935 --
936 -- Description: This procedure is the wrapper(overloaded) version for the
937 -- main delivery_detail_group API. This is for use by public APIs
938 -- and by other product APIs. This signature does not have
939 -- the form(UI) specific parameters
940 -- Created : Patchset I : Harmonization Project
941 -- Created by: KVENKATE
942 -- -----------------------------------------------------------------------
943 PROCEDURE Delivery_Detail_Action
944 (
945 -- Standard Parameters
946 p_api_version_number IN NUMBER,
947 p_init_msg_list IN VARCHAR2,
948 p_commit IN VARCHAR2,
949 x_return_status OUT NOCOPY VARCHAR2,
950 x_msg_count OUT NOCOPY NUMBER,
951 x_msg_data OUT NOCOPY VARCHAR2,
952
953 -- Procedure specific Parameters
954 p_detail_id_tab IN WSH_UTIL_CORE.id_tab_type,
955 p_action_prms IN WSH_GLBL_VAR_STRCT_GRP.dd_action_parameters_rec_type
956 ,
957 x_action_out_rec OUT NOCOPY WSH_GLBL_VAR_STRCT_GRP.dd_action_out_rec_type
958 ) IS
959
960 l_api_name CONSTANT VARCHAR2(30) := 'delivery_detail_action';
961 l_api_version CONSTANT NUMBER := 1.0;
962 --
963 --
964 l_return_status VARCHAR2(32767);
965 l_msg_count NUMBER;
966 l_msg_data VARCHAR2(32767);
967 l_program_name VARCHAR2(32767);
968 --
969 --
970 l_rec_attr_tab WSH_GLBL_VAR_STRCT_GRP.Delivery_Details_Attr_Tbl_Type;
971 l_action_prms WSH_GLBL_VAR_STRCT_GRP.dd_action_parameters_rec_type
972 ;
973 l_action_out_rec WSH_GLBL_VAR_STRCT_GRP.dd_action_out_rec_type;
974 l_dummy_defaults WSH_GLBL_VAR_STRCT_GRP.dd_default_parameters_rec_type;
975 l_dummy_flag VARCHAR2(32767);
976 --
977 CURSOR det_cur(p_det_id NUMBER) IS
978 SELECT released_status,
979 organization_id,
980 container_flag,
981 source_code,
982 lpn_id,
983 CUSTOMER_ID,
984 INVENTORY_ITEM_ID,
985 SHIP_FROM_LOCATION_ID,
986 SHIP_TO_LOCATION_ID,
987 INTMED_SHIP_TO_LOCATION_ID,
988 DATE_REQUESTED,
989 DATE_SCHEDULED,
990 SHIP_METHOD_CODE,
991 CARRIER_ID,
992 shipping_control,
993 party_id,
994 /*J inbound logistics: new column jckwok */
995 line_direction,
996 source_line_id,
997 move_order_line_id --R12, X-dock
998 FROM wsh_delivery_details
999 WHERE delivery_detail_id = p_det_id;
1000
1001 l_number_of_errors NUMBER := 0;
1002 l_number_of_warnings NUMBER := 0;
1003 --
1004 l_counter NUMBER := 0;
1005 l_index NUMBER;
1006 --
1007 l_debug_on BOOLEAN;
1008 --
1009 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'DELIVERY_DETAIL_ACTION';
1010 --
1011 BEGIN
1012 -- Standard Start of API savepoint
1013 --
1014 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1015 --
1016 IF l_debug_on IS NULL
1017 THEN
1018 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1019 END IF;
1020 --
1021 SAVEPOINT DEL_DETAIL_ACTION_WRAP_GRP;
1022
1023 IF l_debug_on THEN
1024 WSH_DEBUG_SV.push(l_module_name);
1025 --
1026 WSH_DEBUG_SV.log(l_module_name,'P_API_VERSION_NUMBER',P_API_VERSION_NUMBER);
1027 WSH_DEBUG_SV.log(l_module_name,'P_INIT_MSG_LIST',P_INIT_MSG_LIST);
1028 WSH_DEBUG_SV.log(l_module_name,'P_COMMIT',P_COMMIT);
1029 WSH_DEBUG_SV.log(l_module_name, 'Caller', p_action_prms.caller);
1030 WSH_DEBUG_SV.log(l_module_name, 'Phase', p_action_prms.phase);
1031 WSH_DEBUG_SV.log(l_module_name, 'Action Code', p_action_prms.action_code);
1032 WSH_DEBUG_SV.log(l_module_name, 'Input Table count', p_detail_id_tab.count);
1033 END IF;
1034 --
1035
1036 IF FND_API.to_Boolean( p_init_msg_list )
1037 THEN
1038 FND_MSG_PUB.initialize;
1039 END IF;
1040 --
1041 --
1042 -- Initialize API return status to success
1043 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1044 l_number_of_errors := 0;
1045 l_number_of_warnings := 0;
1046
1047
1048 IF(p_detail_id_tab.count = 0) THEN
1049 IF l_debug_on THEN
1050 wsh_debug_sv.log (l_module_name,'Input Table Count Zero');
1051 END IF;
1052 RAISE FND_API.G_EXC_ERROR;
1053 END IF;
1054
1055
1056 --Build the records to pass to the core group api
1057 l_index := p_detail_id_tab.FIRST;
1058 WHILE l_index IS NOT NULL
1059 LOOP
1060 l_rec_attr_tab(l_index).delivery_detail_id := p_detail_id_tab(l_index);
1061
1062 OPEN det_cur(p_detail_id_tab(l_index));
1063 FETCH det_cur
1064 INTO l_rec_attr_tab(l_index).released_status,
1065 l_rec_attr_tab(l_index).organization_id,
1066 l_rec_attr_tab(l_index).container_flag,
1067 l_rec_attr_tab(l_index).source_code,
1068 l_rec_attr_tab(l_index).lpn_id,
1069 l_rec_attr_tab(l_index).CUSTOMER_ID,
1070 l_rec_attr_tab(l_index).INVENTORY_ITEM_ID,
1071 l_rec_attr_tab(l_index).SHIP_FROM_LOCATION_ID,
1072 l_rec_attr_tab(l_index).SHIP_TO_LOCATION_ID,
1073 l_rec_attr_tab(l_index).INTMED_SHIP_TO_LOCATION_ID,
1074 l_rec_attr_tab(l_index).DATE_REQUESTED,
1075 l_rec_attr_tab(l_index).DATE_SCHEDULED,
1076 l_rec_attr_tab(l_index).SHIP_METHOD_CODE,
1077 l_rec_attr_tab(l_index).CARRIER_ID,
1078 l_rec_attr_tab(l_index).shipping_control,
1079 l_rec_attr_tab(l_index).party_id,
1080 l_rec_attr_tab(l_index).line_direction,
1081 l_rec_attr_tab(l_index).source_line_id,
1082 l_rec_attr_tab(l_index).move_order_line_id; -- R12, X-dock
1083
1084 IF det_cur%NOTFOUND THEN
1085 IF l_debug_on THEN
1086 wsh_debug_sv.log (l_module_name,'Invalid Delivery Detail');
1087 END IF;
1088 CLOSE det_cur;
1089 RAISE FND_API.G_EXC_ERROR;
1090 END IF;
1091
1092 CLOSE det_cur;
1093
1094 l_index := p_detail_id_tab.NEXT(l_index);
1095 END LOOP;
1096
1097 l_action_prms := p_action_prms;
1098 l_action_prms.Caller := RTRIM(p_action_prms.Caller);
1099 l_action_prms.Action_Code := RTRIM(p_action_prms.Action_Code);
1100 l_action_prms.delivery_name := RTRIM(p_action_prms.delivery_name);
1101 l_action_prms.wv_override_flag := RTRIM(p_action_prms.wv_override_flag);
1102 l_action_prms.container_name := RTRIM(p_action_prms.container_name);
1103 l_action_prms.container_flag := RTRIM(p_action_prms.container_flag);
1104 l_action_prms.delivery_flag := RTRIM(p_action_prms.delivery_flag);
1105 -- Call the Core Group API
1106
1107 IF l_debug_on THEN
1108 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIERY_DETAILS_GRP.DELIVERY_DETAIL_ACTION',WSH_DEBUG_SV.C_PROC_LEVEL);
1109 END IF;
1110
1111 wsh_delivery_details_grp.Delivery_Detail_Action(
1112 p_api_version_number => p_api_version_number,
1113 p_init_msg_list => FND_API.G_FALSE,
1114 p_commit => FND_API.G_FALSE,
1115 x_return_status => l_return_status,
1116 x_msg_count => l_msg_count,
1117 x_msg_data => l_msg_data,
1118 p_rec_attr_tab => l_rec_attr_tab,
1119 p_action_prms => l_action_prms,
1120 x_defaults => l_dummy_defaults,
1121 x_action_out_rec => x_action_out_rec
1122 );
1123
1124 --
1125 IF l_debug_on THEN
1126 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.API_POST_CALL',WSH_DEBUG_SV.C_PROC_LEVEL);
1127 END IF;
1128
1129 wsh_util_core.api_post_call(
1130 p_return_status => l_return_status,
1131 x_num_warnings => l_number_of_warnings,
1132 x_num_errors => l_number_of_errors,
1133 p_msg_data => l_msg_data
1134 );
1135
1136
1137 IF l_number_of_warnings > 0 THEN
1138 RAISE WSH_UTIL_CORE.G_EXC_WARNING;
1139 END IF;
1140
1141 -- Standard check of p_commit.
1142 IF FND_API.To_Boolean( p_commit ) THEN
1143 IF l_debug_on THEN
1144 WSH_DEBUG_SV.logmsg(l_module_name, 'Commit Work');
1145 END IF;
1146 COMMIT WORK;
1147 END IF;
1148
1149 FND_MSG_PUB.Count_And_Get
1150 (
1151 p_count => x_msg_count,
1152 p_data => x_msg_data
1153 );
1154
1155 IF l_debug_on THEN
1156 WSH_DEBUG_SV.pop(l_module_name);
1157 END IF;
1158 --
1159 EXCEPTION
1160 WHEN FND_API.G_EXC_ERROR THEN
1161 ROLLBACK TO DEL_DETAIL_ACTION_WRAP_GRP;
1162 x_return_status := FND_API.G_RET_STS_ERROR ;
1163 FND_MSG_PUB.Count_And_Get
1164 (
1165 p_count => x_msg_count,
1166 p_data => x_msg_data,
1167 p_encoded => FND_API.G_FALSE
1168 );
1169 --
1170 IF l_debug_on THEN
1171 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1172 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
1173 END IF;
1174 --
1175 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1176 ROLLBACK TO DEL_DETAIL_ACTION_WRAP_GRP;
1177 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1178 FND_MSG_PUB.Count_And_Get
1179 (
1180 p_count => x_msg_count,
1181 p_data => x_msg_data,
1182 p_encoded => FND_API.G_FALSE
1183 );
1184 --
1185 IF l_debug_on THEN
1186 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_UNEXPECTED_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1187 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
1188 END IF;
1189 --
1190 WHEN WSH_UTIL_CORE.G_EXC_WARNING THEN
1191 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
1192 FND_MSG_PUB.Count_And_Get
1193 (
1194 p_count => x_msg_count,
1195 p_data => x_msg_data,
1196 p_encoded => FND_API.G_FALSE
1197 );
1198 --
1199 IF l_debug_on THEN
1200 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_UTIL_CORE.G_EXC_WARNING exception has occured ',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1201 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_UTIL_CORE.G_EXC_WARNING');
1202 END IF;
1203 --
1204 WHEN OTHERS THEN
1205 WSH_UTIL_CORE.default_handler('WSH_INTERFACE_GRP.Delivery_Detail_Action');
1206 ROLLBACK TO DEL_DETAIL_ACTION_WRAP_GRP;
1207 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1208 FND_MSG_PUB.Count_And_Get
1209 (
1210 p_count => x_msg_count,
1211 p_data => x_msg_data,
1212 p_encoded => FND_API.G_FALSE
1213 );
1214 --
1215 IF l_debug_on THEN
1216 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1217 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1218 END IF;
1219 --
1220 --
1221 END Delivery_Detail_Action;
1222
1223 PROCEDURE Rtrim_details_blank_space (
1224 p_in_rec IN WSH_GLBL_VAR_STRCT_GRP.Delivery_Details_Rec_Type,
1225 p_out_rec OUT NOCOPY WSH_GLBL_VAR_STRCT_GRP.Delivery_Details_Rec_Type) IS
1226 l_debug_on BOOLEAN;
1227 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' ||
1228 G_PKG_NAME || '.' || 'Rtrim_details_blank_space';
1229 BEGIN
1230
1231 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1232 --
1233 IF l_debug_on IS NULL
1234 THEN
1235 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1236 END IF;
1237 --
1238 IF l_debug_on THEN
1239 wsh_debug_sv.push (l_module_name, 'Rtrim_details_blank_space');
1240 END IF;
1241
1242 p_out_rec := p_in_rec;
1243
1244 p_out_rec.source_code := RTRIM(p_in_rec.source_code);
1245 p_out_rec.item_description := RTRIM(p_in_rec.item_description);
1246 p_out_rec.country_of_origin := RTRIM(p_in_rec.country_of_origin);
1247 p_out_rec.classification := RTRIM(p_in_rec.classification);
1248 p_out_rec.hold_code := RTRIM(p_in_rec.hold_code);
1249 p_out_rec.requested_quantity_uom := RTRIM(p_in_rec.requested_quantity_uom);
1250 p_out_rec.subinventory := RTRIM(p_in_rec.subinventory);
1251 p_out_rec.revision := RTRIM(p_in_rec.revision);
1252 p_out_rec.lot_number := RTRIM(p_in_rec.lot_number);
1253 p_out_rec.customer_requested_lot_flag := RTRIM(p_in_rec.customer_requested_lot_flag);
1254 -- frontport bug 5049214: trim both leading/trailing space of serial number
1255 p_out_rec.serial_number := LTRIM(RTRIM(p_in_rec.serial_number));
1256 p_out_rec.to_serial_number := LTRIM(RTRIM(p_in_rec.to_serial_number));
1257 p_out_rec.ship_method_code := RTRIM(p_in_rec.ship_method_code);
1258 p_out_rec.freight_terms_code := RTRIM(p_in_rec.freight_terms_code);
1259 p_out_rec.shipment_priority_code := RTRIM(p_in_rec.shipment_priority_code);
1260 p_out_rec.fob_code := RTRIM(p_in_rec.fob_code);
1261 p_out_rec.dep_plan_required_flag := RTRIM(p_in_rec.dep_plan_required_flag);
1262 p_out_rec.customer_prod_seq := RTRIM(p_in_rec.customer_prod_seq);
1263 p_out_rec.customer_dock_code := RTRIM(p_in_rec.customer_dock_code);
1264 p_out_rec.cust_model_serial_number := RTRIM(p_in_rec.cust_model_serial_number);
1265 p_out_rec.customer_job := RTRIM(p_in_rec.customer_job);
1266 p_out_rec.customer_production_line:= RTRIM(p_in_rec.customer_production_line);
1267 p_out_rec.weight_uom_code := RTRIM(p_in_rec.weight_uom_code);
1268 p_out_rec.volume_uom_code := RTRIM(p_in_rec.volume_uom_code);
1269 p_out_rec.tp_attribute_category := RTRIM(p_in_rec.tp_attribute_category);
1270 p_out_rec.tp_attribute1 := RTRIM(p_in_rec.tp_attribute1);
1271 p_out_rec.tp_attribute2 := RTRIM(p_in_rec.tp_attribute2);
1272 p_out_rec.tp_attribute3 := RTRIM(p_in_rec.tp_attribute3);
1273 p_out_rec.tp_attribute4 := RTRIM(p_in_rec.tp_attribute4);
1274 p_out_rec.tp_attribute5 := RTRIM(p_in_rec.tp_attribute5);
1275 p_out_rec.tp_attribute6 := RTRIM(p_in_rec.tp_attribute6);
1276 p_out_rec.tp_attribute7 := RTRIM(p_in_rec.tp_attribute7);
1277 p_out_rec.tp_attribute8 := RTRIM(p_in_rec.tp_attribute8);
1278 p_out_rec.tp_attribute9 := RTRIM(p_in_rec.tp_attribute9);
1279 p_out_rec.tp_attribute10 := RTRIM(p_in_rec.tp_attribute10);
1280 p_out_rec.tp_attribute11 := RTRIM(p_in_rec.tp_attribute11);
1281 p_out_rec.tp_attribute12 := RTRIM(p_in_rec.tp_attribute12);
1282 p_out_rec.tp_attribute13 := RTRIM(p_in_rec.tp_attribute13);
1283 p_out_rec.tp_attribute14 := RTRIM(p_in_rec.tp_attribute14);
1284 p_out_rec.tp_attribute15 := RTRIM(p_in_rec.tp_attribute15);
1285 p_out_rec.attribute_category := RTRIM(p_in_rec.attribute_category);
1286 p_out_rec.attribute1 := RTRIM(p_in_rec.attribute1);
1287 p_out_rec.attribute2 := RTRIM(p_in_rec.attribute2);
1288 p_out_rec.attribute3 := RTRIM(p_in_rec.attribute3);
1289 p_out_rec.attribute4 := RTRIM(p_in_rec.attribute4);
1290 p_out_rec.attribute5 := RTRIM(p_in_rec.attribute5);
1291 p_out_rec.attribute6 := RTRIM(p_in_rec.attribute6);
1292 p_out_rec.attribute7 := RTRIM(p_in_rec.attribute7);
1293 p_out_rec.attribute8 := RTRIM(p_in_rec.attribute8);
1294 p_out_rec.attribute9 := RTRIM(p_in_rec.attribute9);
1295 p_out_rec.attribute10 := RTRIM(p_in_rec.attribute10);
1296 p_out_rec.attribute11 := RTRIM(p_in_rec.attribute11);
1297 p_out_rec.attribute12 := RTRIM(p_in_rec.attribute12);
1298 p_out_rec.attribute13 := RTRIM(p_in_rec.attribute13);
1299 p_out_rec.attribute14 := RTRIM(p_in_rec.attribute14);
1300 p_out_rec.attribute15 := RTRIM(p_in_rec.attribute15);
1301 p_out_rec.mvt_stat_status := RTRIM(p_in_rec.mvt_stat_status);
1302 p_out_rec.released_flag := RTRIM(p_in_rec.released_flag);
1303 p_out_rec.ship_model_complete_flag:= RTRIM(p_in_rec.ship_model_complete_flag);
1304 p_out_rec.source_header_number := RTRIM(p_in_rec.source_header_number);
1305 p_out_rec.source_header_type_name := RTRIM(p_in_rec.source_header_type_name);
1306 p_out_rec.cust_po_number := RTRIM(p_in_rec.cust_po_number);
1307 p_out_rec.src_requested_quantity_uom := RTRIM(p_in_rec.src_requested_quantity_uom);
1308 p_out_rec.tracking_number := RTRIM(p_in_rec.tracking_number);
1309 p_out_rec.shipping_instructions := RTRIM(p_in_rec.shipping_instructions);
1310 p_out_rec.packing_instructions := RTRIM(p_in_rec.packing_instructions);
1311 p_out_rec.oe_interfaced_flag := RTRIM(p_in_rec.oe_interfaced_flag);
1312 p_out_rec.inv_interfaced_flag := RTRIM(p_in_rec.inv_interfaced_flag);
1313 p_out_rec.source_line_number := RTRIM(p_in_rec.source_line_number);
1314 p_out_rec.inspection_flag := RTRIM(p_in_rec.inspection_flag);
1315 p_out_rec.released_status := RTRIM(p_in_rec.released_status);
1316 p_out_rec.container_flag := RTRIM(p_in_rec.container_flag);
1317 p_out_rec.container_type_code := RTRIM(p_in_rec.container_type_code);
1318 p_out_rec.container_name := RTRIM(p_in_rec.container_name);
1319 p_out_rec.master_serial_number := RTRIM(p_in_rec.master_serial_number);
1320 p_out_rec.seal_code := RTRIM(p_in_rec.seal_code);
1321 p_out_rec.unit_number := RTRIM(p_in_rec.unit_number);
1322 p_out_rec.currency_code := RTRIM(p_in_rec.currency_code);
1323 p_out_rec.preferred_grade := RTRIM(p_in_rec.preferred_grade);
1324 p_out_rec.src_requested_quantity_uom2 := RTRIM(p_in_rec.src_requested_quantity_uom2);
1325 p_out_rec.requested_quantity_uom2 := RTRIM(p_in_rec.requested_quantity_uom2);
1326 -- HW OPMCONV - No need for sublot
1327 -- p_out_rec.sublot_number := RTRIM(p_in_rec.sublot_number);
1328 p_out_rec.pickable_flag := RTRIM(p_in_rec.pickable_flag);
1329 p_out_rec.original_subinventory := RTRIM(p_in_rec.original_subinventory);
1330
1331 IF l_debug_on THEN
1332 WSH_DEBUG_SV.pop(l_module_name);
1333 END IF;
1334
1335 EXCEPTION
1336
1337 WHEN OTHERS THEN
1338 wsh_util_core.default_handler (
1339 'WSH_TRIP_STOPS_GRP.Rtrim_details_blank_space', l_module_name);
1340 IF l_debug_on THEN
1341 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured.'||
1342 ' Oracle error message is '|| SQLERRM,
1343 WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1344 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1345 END IF;
1346 RAISE;
1347
1348 END Rtrim_details_blank_space;
1349
1350 -- ---------------------------------------------------------------------
1351 -- Procedure: Create_Update_Delivery_Detail Wrapper API
1352 --
1353 -- Parameters:
1354 --
1355 -- Description: This procedure is the new API for wrapping the logic of CREATE/UPDATE of delivery details
1356 -- Created : Patchset I - Harmonization Project
1357 -- Created By : KVENKATE
1358 -- -----------------------------------------------------------------------
1359
1360 PROCEDURE Create_Update_Delivery_Detail
1361 (
1362 -- Standard Parameters
1363 p_api_version_number IN NUMBER,
1364 p_init_msg_list IN VARCHAR2,
1365 p_commit IN VARCHAR2,
1366 x_return_status OUT NOCOPY VARCHAR2,
1367 x_msg_count OUT NOCOPY NUMBER,
1368 x_msg_data OUT NOCOPY VARCHAR2,
1369
1370 -- Procedure Specific Parameters
1371 p_detail_info_tab IN WSH_GLBL_VAR_STRCT_GRP.Delivery_Details_Attr_Tbl_Type,
1372 p_IN_rec IN WSH_GLBL_VAR_STRCT_GRP.detailInRecType,
1373 x_OUT_rec OUT NOCOPY WSH_GLBL_VAR_STRCT_GRP.detailOutRecType
1374 ) IS
1375
1376 l_api_name CONSTANT VARCHAR2(30) := 'Create_Update_Delivery_Detail';
1377 l_api_version CONSTANT NUMBER := 1.0;
1378 --
1379 --
1380 l_return_status VARCHAR2(32767);
1381 l_msg_count NUMBER;
1382 l_msg_data VARCHAR2(32767);
1383 l_program_name VARCHAR2(32767);
1384 --
1385 l_number_of_errors NUMBER := 0;
1386 l_number_of_warnings NUMBER := 0;
1387 --
1388 l_counter NUMBER := 0;
1389
1390 l_valid_index_tab wsh_util_core.id_tab_type;
1391 l_delivery_id NUMBER;
1392 l_delivery_detail_rec WSH_GLBL_VAR_STRCT_GRP.Delivery_Details_Rec_Type
1393 ;
1394 l_in_rec WSH_GLBL_VAR_STRCT_GRP.detailInRecType;
1395 l_out_rec WSH_GLBL_VAR_STRCT_GRP.detailOutRecType;
1396 l_detail_info_tab WSH_GLBL_VAR_STRCT_GRP.Delivery_Details_Attr_Tbl_Type;
1397 l_index NUMBER;
1398 --
1399 CURSOR det_to_del_cur(p_detail_id NUMBER) IS
1400 SELECT wda.delivery_id
1401 FROM wsh_delivery_assignments_v wda
1402 WHERE wda.delivery_detail_id = p_detail_id;
1403 --
1404 l_debug_on BOOLEAN;
1405 --
1406 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'CREATE_UPDATE_DELIVERY_DETAIL';
1407 --
1408
1409 BEGIN
1410
1411 -- Standard Start of API savepoint
1412 --
1413 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1414 --
1415 IF l_debug_on IS NULL
1416 THEN
1417 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1418 END IF;
1419 --
1420 SAVEPOINT CREATE_UPD_DEL_DET_WRAP_GRP;
1421 --
1422 -- Debug Statements
1423 --
1424 IF l_debug_on THEN
1425 WSH_DEBUG_SV.push(l_module_name);
1426 --
1427 WSH_DEBUG_SV.log(l_module_name,'P_API_VERSION_NUMBER',P_API_VERSION_NUMBER);
1428 WSH_DEBUG_SV.log(l_module_name,'P_INIT_MSG_LIST',P_INIT_MSG_LIST);
1429 WSH_DEBUG_SV.log(l_module_name,'P_COMMIT',P_COMMIT);
1430 END IF;
1431 --
1432 -- Initialize message list if p_init_msg_list is set to TRUE.
1433 IF FND_API.to_Boolean( p_init_msg_list )
1434 THEN
1435 FND_MSG_PUB.initialize;
1436 END IF;
1437 --
1438 --
1439 -- Initialize API return status to success
1440 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1441 l_number_of_errors := 0;
1442 l_number_of_warnings := 0;
1443
1444
1445 l_in_rec := p_in_rec;
1446 l_in_rec.caller := RTRIM(p_in_rec.caller);
1447 l_in_rec.action_code := RTRIM(p_in_rec.action_code);
1448 l_in_rec.container_item_name := RTRIM(p_in_rec.container_item_name);
1449 l_in_rec.organization_code := RTRIM(p_in_rec.organization_code);
1450 l_in_rec.name_prefix := RTRIM(p_in_rec.name_prefix);
1451 l_in_rec.name_suffix := RTRIM(p_in_rec.name_suffix);
1452 l_in_rec.container_name := RTRIM(p_in_rec.container_name);
1453
1454 -- Call Core Group API
1455
1456 IF l_debug_on THEN
1457 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_GRP.CREATE_UPDATE_DELIVERY_DETAIL',WSH_DEBUG_SV.C_PROC_LEVEL);
1458 END IF;
1459 --
1460 l_index := p_detail_info_tab.FIRST;
1461 WHILE l_index IS NOT NULL LOOP
1462 Rtrim_details_blank_space(p_detail_info_tab(l_index),
1463 l_detail_info_tab(l_index));
1464 l_index := p_detail_info_tab.NEXT(l_index);
1465 END LOOP;
1466
1467 wsh_delivery_details_grp.Create_Update_Delivery_Detail(
1468 p_api_version_number => p_api_version_number,
1469 p_init_msg_list => FND_API.G_FALSE,
1470 p_commit => FND_API.G_FALSE,
1471 x_return_status => l_return_status,
1472 x_msg_count => l_msg_count,
1473 x_msg_data => l_msg_data,
1474 p_detail_info_tab => l_detail_info_tab,
1475 p_IN_rec => l_in_rec,
1476 x_OUT_rec => x_out_rec
1477 );
1478
1479 --
1480 IF l_debug_on THEN
1481 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.API_POST_CALL',WSH_DEBUG_SV.C_PROC_LEVEL);
1482 END IF;
1483 --
1484 wsh_util_core.api_post_call(
1485 p_return_status => l_return_status,
1486 x_num_warnings => l_number_of_warnings,
1487 x_num_errors => l_number_of_errors,
1488 p_msg_data => l_msg_data
1489 );
1490
1491 IF l_number_of_warnings > 0 THEN
1492 RAISE WSH_UTIL_CORE.G_EXC_WARNING;
1493 END IF;
1494
1495 -- Standard check of p_commit.
1496 IF FND_API.To_Boolean( p_commit ) THEN
1497 IF l_debug_on THEN
1498 WSH_DEBUG_SV.logmsg(l_module_name, 'Commit Work');
1499 END IF;
1500 COMMIT WORK;
1501 END IF;
1502
1503 FND_MSG_PUB.Count_And_Get
1504 (
1505 p_count => x_msg_count,
1506 p_data => x_msg_data
1507 );
1508
1509 IF l_debug_on THEN
1510 WSH_DEBUG_SV.pop(l_module_name);
1511 END IF;
1512 --
1513 EXCEPTION
1514 WHEN FND_API.G_EXC_ERROR THEN
1515 ROLLBACK TO CREATE_UPD_DEL_DET_WRAP_GRP;
1516 x_return_status := FND_API.G_RET_STS_ERROR ;
1517 FND_MSG_PUB.Count_And_Get
1518 (
1519 p_count => x_msg_count,
1520 p_data => x_msg_data,
1521 p_encoded => FND_API.G_FALSE
1522 );
1523 --
1524 IF l_debug_on THEN
1525 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1526 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
1527 END IF;
1528 --
1529 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1530 ROLLBACK TO CREATE_UPD_DEL_DET_WRAP_GRP;
1531 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1532 FND_MSG_PUB.Count_And_Get
1533 (
1534 p_count => x_msg_count,
1535 p_data => x_msg_data,
1536 p_encoded => FND_API.G_FALSE
1537 );
1538
1539 --
1540 IF l_debug_on THEN
1541 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_UNEXPECTED_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1542 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
1543 END IF;
1544 --
1545 WHEN WSH_UTIL_CORE.G_EXC_WARNING THEN
1546 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
1547 FND_MSG_PUB.Count_And_Get
1548 (
1549 p_count => x_msg_count,
1550 p_data => x_msg_data,
1551 p_encoded => FND_API.G_FALSE
1552 );
1553 --
1554 IF l_debug_on THEN
1555 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_UTIL_CORE.G_EXC_WARNING exception has occured ',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1556 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_UTIL_CORE.G_EXC_WARNING');
1557 END IF;
1558 --
1559 WHEN OTHERS THEN
1560 WSH_UTIL_CORE.default_handler('WSH_INTERFACE_GRP.Create_Update_Delivery_Detail');
1561 ROLLBACK TO CREATE_UPD_DEL_DET_WRAP_GRP;
1562 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1563 FND_MSG_PUB.Count_And_Get
1564 (
1565 p_count => x_msg_count,
1566 p_data => x_msg_data,
1567 p_encoded => FND_API.G_FALSE
1568 );
1569 --
1570
1571 IF l_debug_on THEN
1572 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1573 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1574 END IF;
1575 --
1576 END Create_Update_Delivery_Detail;
1577
1578
1579
1580 /*------------------------------------------------------------
1581 PROCEDURE Trip_Action This is the wrapper for the
1582 Trip action
1583 -------------------------------------------------------------*/
1584
1585 PROCEDURE Trip_Action
1586 ( p_api_version_number IN NUMBER,
1587 p_init_msg_list IN VARCHAR2,
1588 p_commit IN VARCHAR2,
1589 p_entity_id_tab IN wsh_util_core.id_tab_type,
1590 p_action_prms IN WSH_TRIPS_GRP.action_parameters_rectype,
1591 x_trip_out_rec OUT NOCOPY WSH_TRIPS_GRP.tripActionOutRecType,
1592 x_return_status OUT NOCOPY VARCHAR2,
1593 x_msg_count OUT NOCOPY NUMBER,
1594 x_msg_data OUT NOCOPY VARCHAR2)
1595 IS
1596
1597 l_rec_attr_tab WSH_TRIPS_PVT.Trip_Attr_Tbl_Type;
1598 l_action_prms WSH_TRIPS_GRP.action_parameters_rectype;
1599 l_num_warning NUMBER := 0;
1600 l_num_errors NUMBER := 0;
1601 l_next NUMBER;
1602 l_index NUMBER;
1603 l_trip_id NUMBER;
1604 l_status_code wsh_trips.status_code%TYPE;
1605 l_planned_flag wsh_trips.planned_flag%TYPE;
1606 l_lane_id wsh_trips.lane_id%TYPE;
1607 l_load_tender_status wsh_trips.load_tender_status%TYPE;
1608 l_shipments_type_flag VARCHAR2(30);
1609 l_trip_out_rec WSH_TRIPS_GRP.tripActionOutRecType;
1610 l_return_status VARCHAR2(1000);
1611 l_ignore_for_planning WSH_TRIPS.IGNORE_FOR_PLANNING%TYPE; --OTM R12, glog proj
1612
1613 l_debug_on BOOLEAN;
1614 l_def_rec WSH_TRIPS_GRP.default_parameters_rectype;
1615 --
1616 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.'
1617 || 'TRIP_ACTION WRAPPER';
1618
1619 CURSOR c_attributes(p_trip_id NUMBER) IS
1620 SELECT trip_id
1621 ,planned_flag
1622 ,lane_id
1623 ,load_tender_status
1624 ,status_code
1625 ,shipments_type_flag
1626 ,ignore_for_planning -- OTM R12, glog proj
1627 FROM WSH_TRIPS
1628 WHERE trip_id = p_trip_id;
1629
1630
1631 BEGIN
1632 --
1633 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1634 --
1635 IF l_debug_on IS NULL
1636 THEN
1637 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1638 END IF;
1639 --
1640 IF l_debug_on THEN
1641 wsh_debug_sv.push (l_module_name);
1642 wsh_debug_sv.log (l_module_name,'phase', p_action_prms.phase);
1643 END IF;
1644
1645 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1646
1647 l_action_prms := p_action_prms;
1648 l_action_prms.caller := RTRIM(p_action_prms.caller);
1649 l_action_prms.action_code := RTRIM(p_action_prms.action_code);
1650 l_action_prms.override_flag := RTRIM(p_action_prms.override_flag);
1651 l_action_prms.trip_name := RTRIM(p_action_prms.trip_name);
1652
1653 IF l_action_prms.phase IS NULL THEN
1654 l_action_prms.phase := 1;
1655 END IF;
1656
1657 l_index := p_entity_id_tab.FIRST;
1658
1659 WHILE l_index IS NOT NULL LOOP
1660 OPEN c_attributes(p_entity_id_tab(l_index));
1661 FETCH c_attributes INTO
1662 l_trip_id,
1663 l_planned_flag,
1664 l_lane_id,
1665 l_load_tender_status,
1666 l_status_code,
1667 l_shipments_type_flag,
1668 l_ignore_for_planning; -- OTM R12, glog proj
1669 IF (c_attributes%NOTFOUND) THEN
1670 CLOSE c_attributes;
1671 --fnd_message.set_name('WSH','WSH_BAD_ENTITY');
1672 --fnd_message.set_token('ID',p_entity_id_tab(l_index));
1673 IF l_debug_on THEN
1674 wsh_debug_sv.log (l_module_name,'WSH_BAD_ENTITY');
1675 END IF;
1676 RAISE FND_API.G_EXC_ERROR;
1677 END IF;
1678 l_rec_attr_tab(l_index).status_code := l_status_code;
1679 l_rec_attr_tab(l_index).trip_id := l_trip_id;
1680 l_rec_attr_tab(l_index).planned_flag := l_planned_flag;
1681 l_rec_attr_tab(l_index).lane_id := l_lane_id;
1682 l_rec_attr_tab(l_index).load_tender_status := l_load_tender_status;
1683 l_rec_attr_tab(l_index).shipments_type_flag := l_shipments_type_flag;
1684 l_rec_attr_tab(l_index).ignore_for_planning := l_ignore_for_planning;--OTM R12, glog proj
1685
1686 -- OTM R12, glog proj, add debug messages
1687 IF l_debug_on THEN
1688 wsh_debug_sv.log (l_module_name,'Trip id', l_trip_id);
1689 wsh_debug_sv.log (l_module_name,'Status Code', l_status_code);
1690 wsh_debug_sv.log (l_module_name,'Planned Flag', l_planned_flag);
1691 wsh_debug_sv.log (l_module_name,'Lane id', l_lane_id);
1692 wsh_debug_sv.log (l_module_name,'Load Tender Status', l_load_tender_status);
1693 wsh_debug_sv.log (l_module_name,'Shipment Type Flag', l_shipments_type_flag);
1694 wsh_debug_sv.log (l_module_name,'Ignore for Planning', l_ignore_for_planning);
1695 END IF;
1696
1697 CLOSE c_attributes;
1698
1699 l_index := p_entity_id_tab.NEXT(l_index);
1700 END LOOP;
1701
1702 WSH_TRIPS_GRP.Trip_Action
1703 ( p_api_version_number => p_api_version_number,
1704 p_init_msg_list => p_init_msg_list,
1705 p_commit => p_commit,
1706 p_action_prms => l_action_prms,
1707 p_rec_attr_tab => l_rec_attr_tab,
1708 x_trip_out_rec => l_trip_out_rec,
1709 x_def_rec => l_def_rec,
1710 x_return_status => l_return_status,
1711 x_msg_count => x_msg_count,
1712 x_msg_data => x_msg_data
1713 );
1714
1715 IF l_debug_on THEN
1716 wsh_debug_sv.log (l_module_name,'x_return_status', x_return_status);
1717 END IF;
1718
1719 wsh_util_core.api_post_call(p_return_status =>l_return_status,
1720 x_num_warnings =>l_num_warning,
1721 x_num_errors =>l_num_errors);
1722
1723 x_return_status := l_return_status;
1724
1725 IF l_num_warning > 0 THEN
1726 RAISE WSH_UTIL_CORE.G_EXC_WARNING;
1727 END IF;
1728 FND_MSG_PUB.Count_And_Get
1729 ( p_count => x_msg_count
1730 , p_data => x_msg_data
1731 );
1732
1733 IF l_debug_on THEN
1734
1735 WSH_DEBUG_SV.pop(l_module_name);
1736 END IF;
1737
1738 EXCEPTION
1739 WHEN FND_API.G_EXC_ERROR THEN
1740 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1741 FND_MSG_PUB.Count_And_Get
1742 ( p_count => x_msg_count
1743 , p_data => x_msg_data
1744 );
1745 IF l_debug_on THEN
1746 wsh_debug_sv.log (l_module_name,'G_EXC_ERROR');
1747 WSH_DEBUG_SV.pop(l_module_name);
1748 END IF;
1749 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1750 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1751 FND_MSG_PUB.Count_And_Get
1752 ( p_count => x_msg_count
1753 , p_data => x_msg_data
1754 );
1755 IF l_debug_on THEN
1756 wsh_debug_sv.log (l_module_name,'G_EXC_UNEXPECTED_ERROR');
1757 WSH_DEBUG_SV.pop(l_module_name);
1758 END IF;
1759 WHEN WSH_UTIL_CORE.G_EXC_WARNING THEN
1760 FND_MSG_PUB.Count_And_Get
1761 ( p_count => x_msg_count
1762 , p_data => x_msg_data
1763 );
1764 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
1765 IF l_debug_on THEN
1766 wsh_debug_sv.log (l_module_name,'G_EXC_WARNING');
1767 WSH_DEBUG_SV.pop(l_module_name);
1768 END IF;
1769 WHEN OTHERS THEN
1770 wsh_util_core.default_handler('WSH_TRIPS_GRP.TRIP_ACTION',
1771 l_module_name);
1772 FND_MSG_PUB.Count_And_Get
1773 ( p_count => x_msg_count
1774 , p_data => x_msg_data
1775 );
1776
1777 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1778 IF l_debug_on THEN
1779 wsh_debug_sv.log (l_module_name,'Error',substr(sqlerrm,1,200));
1780 WSH_DEBUG_SV.pop(l_module_name);
1781 END IF;
1782
1783 END Trip_Action;
1784
1785
1786 /*------------------------------------------------------------
1787 PROCEDURE Stop_Action This is the wrapper for the
1788 stop action
1789 -------------------------------------------------------------*/
1790 PROCEDURE Stop_Action
1791 ( p_api_version_number IN NUMBER,
1792 p_init_msg_list IN VARCHAR2,
1793 p_commit IN VARCHAR2,
1794 p_entity_id_tab IN wsh_util_core.id_tab_type,
1795 p_action_prms IN WSH_TRIP_STOPS_GRP.action_parameters_rectype,
1796 x_stop_out_rec OUT NOCOPY WSH_TRIP_STOPS_GRP.stopActionOutRecType,
1797 x_return_status OUT NOCOPY VARCHAR2,
1798 x_msg_count OUT NOCOPY NUMBER,
1799 x_msg_data OUT NOCOPY VARCHAR2)
1800 IS
1801
1802 --l_stop_id_tab wsh_util_core.id_tab_type;
1803 l_rec_attr_tab WSH_TRIP_STOPS_PVT.Stop_Attr_Tbl_Type;
1804 l_action_prms WSH_TRIP_STOPS_GRP.action_parameters_rectype;
1805 l_num_warning NUMBER := 0;
1806 l_num_errors NUMBER := 0;
1807 l_return_status varchar2(1000);
1808 l_index NUMBER;
1809 l_stop_id NUMBER;
1810 --l_stop_location_id NUMBER;
1811 l_status_code wsh_trip_stops.status_code%TYPE;
1812 l_stop_out_rec WSH_TRIP_STOPS_GRP.stopActionOutRecType;
1813
1814 l_debug_on BOOLEAN;
1815 --
1816 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.'
1817 || 'STOP_ACTION WRAPPER';
1818 l_def_rec WSH_TRIP_STOPS_GRP.default_parameters_rectype;
1819
1820 CURSOR c_attributes(p_stop_id NUMBER) IS
1821 SELECT stop_id
1822 ,status_code
1823 ,trip_id
1824 ,stop_location_id
1825 ,stop_sequence_number
1826 ,PLANNED_ARRIVAL_DATE
1827 ,PLANNED_DEPARTURE_DATE
1828 ,actual_arrival_date
1829 ,actual_departure_date
1830 ,shipments_type_flag
1831 -- csun 10+ internal location change
1832 ,physical_location_id
1833 ,physical_stop_id
1834 FROM WSH_TRIP_STOPS
1835 WHERE stop_id = p_stop_id;
1836
1837 BEGIN
1838 --
1839 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1840 --
1841 IF l_debug_on IS NULL
1842 THEN
1843 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1844 END IF;
1845 --
1846 IF l_debug_on THEN
1847 wsh_debug_sv.push (l_module_name);
1848 wsh_debug_sv.log (l_module_name,'phase', p_action_prms.phase);
1849 END IF;
1850
1851 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1852
1853 l_action_prms := p_action_prms;
1854 l_action_prms.caller := RTRIM(p_action_prms.caller);
1855 l_action_prms.action_code := RTRIM(p_action_prms.action_code);
1856 l_action_prms.stop_action := RTRIM(p_action_prms.stop_action);
1857 l_action_prms.defer_interface_flag := RTRIM(p_action_prms.defer_interface_flag);
1858 l_action_prms.override_flag := RTRIM(p_action_prms.override_flag);
1859
1860 IF l_action_prms.phase IS NULL THEN
1861 l_action_prms.phase := 1;
1862 END IF;
1863
1864 l_index := p_entity_id_tab.FIRST;
1865 WHILE l_index IS NOT NULL LOOP
1866 OPEN c_attributes(p_entity_id_tab(l_index));
1867 FETCH c_attributes INTO
1868 l_rec_attr_tab(l_index).stop_id,
1869 l_rec_attr_tab(l_index).status_code,
1870 l_rec_attr_tab(l_index).trip_id,
1871 l_rec_attr_tab(l_index).stop_location_id,
1872 l_rec_attr_tab(l_index).stop_sequence_number,
1873 l_rec_attr_tab(l_index).planned_arrival_date,
1874 l_rec_attr_tab(l_index).planned_departure_date,
1875 l_rec_attr_tab(l_index).actual_arrival_date,
1876 l_rec_attr_tab(l_index).actual_departure_date,
1877 l_rec_attr_tab(l_index).shipments_type_flag,
1878 l_rec_attr_tab(l_index).physical_location_id,
1879 l_rec_attr_tab(l_index).physical_stop_id;
1880
1881 IF (c_attributes%NOTFOUND) THEN
1882 CLOSE c_attributes;
1883 IF l_debug_on THEN
1884 wsh_debug_sv.log (l_module_name,'WSH_BAD_ENTITY');
1885 END IF;
1886 RAISE FND_API.G_EXC_ERROR;
1887 END IF;
1888 CLOSE c_attributes;
1889 l_index := p_entity_id_tab.NEXT(l_index);
1890 END LOOP;
1891
1892
1893 WSH_TRIP_STOPS_GRP.Stop_Action (
1894 p_api_version_number => p_api_version_number,
1895 p_init_msg_list => p_init_msg_list,
1896 p_commit => p_commit,
1897 p_action_prms => l_action_prms,
1898 p_rec_attr_tab => l_rec_attr_tab,
1899 x_stop_out_rec => l_stop_out_rec,
1900 x_def_rec => l_def_rec,
1901 x_return_status => l_return_status,
1902 x_msg_count => x_msg_count,
1903 x_msg_data =>x_msg_data
1904 );
1905
1906 IF l_debug_on THEN
1907 wsh_debug_sv.log (l_module_name,'l_return_status', l_return_status);
1908 END IF;
1909
1910 wsh_util_core.api_post_call(p_return_status =>l_return_status,
1911 x_num_warnings =>l_num_warning,
1912 x_num_errors =>l_num_errors);
1913
1914 IF l_num_warning > 0 THEN
1915 RAISE WSH_UTIL_CORE.G_EXC_WARNING;
1916 END IF;
1917
1918 x_return_status := l_return_status;
1919
1920 FND_MSG_PUB.Count_And_Get
1921 ( p_count => x_msg_count
1922 , p_data => x_msg_data
1923 );
1924
1925 IF l_debug_on THEN
1926 WSH_DEBUG_SV.pop(l_module_name);
1927 END IF;
1928
1929 EXCEPTION
1930 WHEN FND_API.G_EXC_ERROR THEN
1931 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1932 FND_MSG_PUB.Count_And_Get
1933 ( p_count => x_msg_count
1934 , p_data => x_msg_data
1935 );
1936 IF l_debug_on THEN
1937 wsh_debug_sv.log (l_module_name,'G_EXC_ERROR');
1938 WSH_DEBUG_SV.pop(l_module_name);
1939 END IF;
1940 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1941 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1942 FND_MSG_PUB.Count_And_Get
1943 ( p_count => x_msg_count
1944 , p_data => x_msg_data
1945 );
1946 IF l_debug_on THEN
1947 wsh_debug_sv.log (l_module_name,'G_EXC_UNEXPECTED_ERROR');
1948 WSH_DEBUG_SV.pop(l_module_name);
1949 END IF;
1950 WHEN WSH_UTIL_CORE.G_EXC_WARNING THEN
1951 FND_MSG_PUB.Count_And_Get
1952 ( p_count => x_msg_count
1953 , p_data => x_msg_data
1954 );
1955 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
1956 IF l_debug_on THEN
1957 wsh_debug_sv.log (l_module_name,'G_EXC_WARNING');
1958 WSH_DEBUG_SV.pop(l_module_name);
1959 END IF;
1960 WHEN OTHERS THEN
1961 wsh_util_core.default_handler('WSH_TRIP_STOPS_GRP.STOP_ACTION',
1962 l_module_name);
1963 FND_MSG_PUB.Count_And_Get
1964 ( p_count => x_msg_count
1965 , p_data => x_msg_data
1966 );
1967
1968 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1969 IF l_debug_on THEN
1970 wsh_debug_sv.log (l_module_name,'Error',substr(sqlerrm,1,200));
1971 WSH_DEBUG_SV.pop(l_module_name);
1972 END IF;
1973
1974 END Stop_Action;
1975
1976 --heali
1977
1978
1979 PROCEDURE Rtrim_stops_blank_space (
1980 p_in_rec IN WSH_TRIP_STOPS_PVT.trip_stop_rec_type,
1981 p_out_rec OUT NOCOPY WSH_TRIP_STOPS_PVT.trip_stop_rec_type) IS
1982 l_debug_on BOOLEAN;
1983 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' ||
1984 G_PKG_NAME || '.' || 'Rtrim_stops_blank_space';
1985 BEGIN
1986
1987 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1988 --
1989 IF l_debug_on IS NULL
1990 THEN
1991 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1992 END IF;
1993 --
1994 IF l_debug_on THEN
1995 wsh_debug_sv.push (l_module_name, 'Rtrim_stops_blank_space');
1996 END IF;
1997
1998 p_out_rec := p_in_rec;
1999
2000 p_out_rec.STATUS_CODE := RTRIM(p_in_rec.STATUS_CODE);
2001 p_out_rec.WEIGHT_UOM_CODE := RTRIM(p_in_rec.WEIGHT_UOM_CODE);
2002 p_out_rec.VOLUME_UOM_CODE := RTRIM(p_in_rec.VOLUME_UOM_CODE);
2003 p_out_rec.DEPARTURE_SEAL_CODE := RTRIM(p_in_rec.DEPARTURE_SEAL_CODE);
2004 p_out_rec.TP_ATTRIBUTE_CATEGORY := RTRIM(p_in_rec.TP_ATTRIBUTE_CATEGORY);
2005 p_out_rec.TP_ATTRIBUTE1 := RTRIM(p_in_rec.TP_ATTRIBUTE1);
2006 p_out_rec.TP_ATTRIBUTE2 := RTRIM(p_in_rec.TP_ATTRIBUTE2);
2007 p_out_rec.TP_ATTRIBUTE3 := RTRIM(p_in_rec.TP_ATTRIBUTE3);
2008 p_out_rec.TP_ATTRIBUTE4 := RTRIM(p_in_rec.TP_ATTRIBUTE4);
2009 p_out_rec.TP_ATTRIBUTE5 := RTRIM(p_in_rec.TP_ATTRIBUTE5);
2010 p_out_rec.TP_ATTRIBUTE6 := RTRIM(p_in_rec.TP_ATTRIBUTE6);
2011 p_out_rec.TP_ATTRIBUTE7 := RTRIM(p_in_rec.TP_ATTRIBUTE7);
2012 p_out_rec.TP_ATTRIBUTE8 := RTRIM(p_in_rec.TP_ATTRIBUTE8);
2013 p_out_rec.TP_ATTRIBUTE9 := RTRIM(p_in_rec.TP_ATTRIBUTE9);
2014 p_out_rec.TP_ATTRIBUTE10 := RTRIM(p_in_rec.TP_ATTRIBUTE10);
2015 p_out_rec.TP_ATTRIBUTE11 := RTRIM(p_in_rec.TP_ATTRIBUTE11);
2016 p_out_rec.TP_ATTRIBUTE12 := RTRIM(p_in_rec.TP_ATTRIBUTE12);
2017 p_out_rec.TP_ATTRIBUTE13 := RTRIM(p_in_rec.TP_ATTRIBUTE13);
2018 p_out_rec.TP_ATTRIBUTE14 := RTRIM(p_in_rec.TP_ATTRIBUTE14);
2019 p_out_rec.TP_ATTRIBUTE15 := RTRIM(p_in_rec.TP_ATTRIBUTE15);
2020 p_out_rec.ATTRIBUTE_CATEGORY := RTRIM(p_in_rec.ATTRIBUTE_CATEGORY);
2021 p_out_rec.ATTRIBUTE1 := RTRIM(p_in_rec.ATTRIBUTE1);
2022 p_out_rec.ATTRIBUTE2 := RTRIM(p_in_rec.ATTRIBUTE2);
2023 p_out_rec.ATTRIBUTE3 := RTRIM(p_in_rec.ATTRIBUTE3);
2024 p_out_rec.ATTRIBUTE4 := RTRIM(p_in_rec.ATTRIBUTE4);
2025 p_out_rec.ATTRIBUTE5 := RTRIM(p_in_rec.ATTRIBUTE5);
2026 p_out_rec.ATTRIBUTE6 := RTRIM(p_in_rec.ATTRIBUTE6);
2027 p_out_rec.ATTRIBUTE7 := RTRIM(p_in_rec.ATTRIBUTE7);
2028 p_out_rec.ATTRIBUTE8 := RTRIM(p_in_rec.ATTRIBUTE8);
2029 p_out_rec.ATTRIBUTE9 := RTRIM(p_in_rec.ATTRIBUTE9);
2030 p_out_rec.ATTRIBUTE10 := RTRIM(p_in_rec.ATTRIBUTE10);
2031 p_out_rec.ATTRIBUTE11 := RTRIM(p_in_rec.ATTRIBUTE11);
2032 p_out_rec.ATTRIBUTE12 := RTRIM(p_in_rec.ATTRIBUTE12);
2033 p_out_rec.ATTRIBUTE13 := RTRIM(p_in_rec.ATTRIBUTE13);
2034 p_out_rec.ATTRIBUTE14 := RTRIM(p_in_rec.ATTRIBUTE14);
2035 p_out_rec.ATTRIBUTE15 := RTRIM(p_in_rec.ATTRIBUTE15);
2036 p_out_rec.TRACKING_DRILLDOWN_FLAG := RTRIM(p_in_rec.TRACKING_DRILLDOWN_FLAG);
2037 p_out_rec.TRACKING_REMARKS := RTRIM(p_in_rec.TRACKING_REMARKS);
2038 p_out_rec.TRIP_NAME := RTRIM(p_in_rec.TRIP_NAME);
2039 p_out_rec.STOP_LOCATION_CODE := RTRIM(p_in_rec.STOP_LOCATION_CODE);
2040 p_out_rec.WEIGHT_UOM_DESC := RTRIM(p_in_rec.WEIGHT_UOM_DESC);
2041 p_out_rec.VOLUME_UOM_DESC := RTRIM(p_in_rec.VOLUME_UOM_DESC);
2042 p_out_rec.PENDING_INTERFACE_FLAG := RTRIM(p_in_rec.PENDING_INTERFACE_FLAG);
2043
2044 IF l_debug_on THEN
2045 WSH_DEBUG_SV.pop(l_module_name);
2046 END IF;
2047
2048 EXCEPTION
2049
2050 WHEN OTHERS THEN
2051 wsh_util_core.default_handler (
2052 'WSH_TRIP_STOPS_GRP.Rtrim_stops_blank_space', l_module_name);
2053 IF l_debug_on THEN
2054 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured.'||
2055 ' Oracle error message is '|| SQLERRM,
2056 WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2057 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
2058 END IF;
2059 RAISE;
2060
2061 END Rtrim_stops_blank_space;
2062
2063
2064 --========================================================================
2065 -- PROCEDURE : Create_Update_Stop Wrapper API
2066 --
2067 -- PARAMETERS: p_api_version_number known api versionerror buffer
2068 -- p_init_msg_list FND_API.G_TRUE to reset list
2069 -- p_commit 'T'/'F'
2070 -- p_in_rec stopInRecType
2071 -- p_rec_attr_tab Table of Attributes for the stop entity
2072 -- p_stop_OUT_tab Table of Output Attributes for the stop entity
2073 -- x_return_status return status
2074 -- x_msg_count number of messages in the list
2075 -- x_msg_data text of messages
2076 -- VERSION : current version 1.0
2077 -- initial version 1.0
2078 -- COMMENT : This calls core API WSH_TRIP_STOPS_GRP.CREATE_UPDATE_STOP
2079 --========================================================================
2080 PROCEDURE CREATE_UPDATE_STOP(
2081 p_api_version_number IN NUMBER,
2082 p_init_msg_list IN VARCHAR2,
2083 p_commit IN VARCHAR2,
2084 p_in_rec IN WSH_TRIP_STOPS_GRP.stopInRecType,
2085 p_rec_attr_tab IN WSH_TRIP_STOPS_PVT.Stop_Attr_Tbl_Type,
2086 x_stop_out_tab OUT NOCOPY WSH_TRIP_STOPS_GRP.stop_out_tab_type,
2087 x_return_status OUT NOCOPY VARCHAR2,
2088 x_msg_count OUT NOCOPY NUMBER,
2089 x_msg_data OUT NOCOPY VARCHAR2) IS
2090
2091 l_api_version_number CONSTANT NUMBER := 1.0;
2092 l_api_name CONSTANT VARCHAR2(30) := 'Create_Update_Stop';
2093 l_debug_on BOOLEAN;
2094 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'CREATE_UPDATE_STOP';
2095
2096 l_num_warnings NUMBER := 0;
2097 l_num_errors NUMBER := 0;
2098 l_rec_attr_tab WSH_TRIP_STOPS_PVT.Stop_Attr_Tbl_Type;
2099 l_index NUMBER;
2100 l_in_rec WSH_TRIP_STOPS_GRP.stopInRecType;
2101 l_stop_wt_vol_out_tab WSH_TRIP_STOPS_GRP.Stop_Wt_Vol_tab_type; --bug 2796095
2102
2103 BEGIN
2104 --
2105 --
2106 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2107 --
2108 IF l_debug_on IS NULL
2109 THEN
2110 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2111 END IF;
2112 --
2113 SAVEPOINT create_update_stop_wrap_grp;
2114
2115 IF l_debug_on THEN
2116 wsh_debug_sv.push(l_module_name);
2117 wsh_debug_sv.log(l_module_name,'Caller is ', p_in_rec.caller);
2118 wsh_debug_sv.log(l_module_name,'Phase is ', p_in_rec.phase);
2119 wsh_debug_sv.log(l_module_name,'Action Code is ', p_in_rec.action_code);
2120 wsh_debug_sv.log(l_module_name,'Number of Records is ', p_rec_attr_tab.COUNT);
2121 END IF;
2122
2123 l_in_rec := p_in_rec;
2124 l_in_rec.caller := RTRIM(p_in_rec.caller);
2125 l_in_rec.action_code := RTRIM(p_in_rec.action_code);
2126
2127 l_index := p_rec_attr_tab.FIRST;
2128 WHILE l_index IS NOT NULL LOOP
2129 Rtrim_stops_blank_space(p_rec_attr_tab(l_index),
2130 l_rec_attr_tab(l_index));
2131 l_index := p_rec_attr_tab.NEXT(l_index);
2132 END LOOP;
2133
2134 WSH_TRIP_STOPS_GRP.CREATE_UPDATE_STOP(
2135 p_api_version_number => p_api_version_number,
2136 p_init_msg_list => p_init_msg_list,
2137 p_commit => p_commit,
2138 p_in_rec => l_in_rec,
2139 p_rec_attr_tab => l_rec_attr_tab,
2140 x_stop_out_tab => x_stop_out_tab,
2141 x_return_status => x_return_status,
2142 x_msg_count => x_msg_count,
2143 x_msg_data => x_msg_data,
2144 x_stop_wt_vol_out_tab => l_stop_wt_vol_out_tab --bug 2796095
2145 );
2146
2147 wsh_util_core.api_post_call(
2148 p_return_status =>x_return_status,
2149 x_num_warnings =>l_num_warnings,
2150 x_num_errors =>l_num_errors);
2151 --
2152 IF l_num_warnings > 0 THEN
2153 RAISE WSH_UTIL_CORE.G_EXC_WARNING;
2154 END IF;
2155 --
2156 FND_MSG_PUB.Count_And_Get (
2157 p_count => x_msg_count,
2158 p_data => x_msg_data);
2159 --
2160 IF l_debug_on THEN
2161 WSH_DEBUG_SV.pop(l_module_name);
2162 END IF;
2163 --
2164 EXCEPTION
2165 WHEN FND_API.G_EXC_ERROR THEN
2166 ROLLBACK TO create_update_stop_wrap_grp;
2167 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2168 FND_MSG_PUB.Count_And_Get
2169 (
2170 p_count => x_msg_count,
2171 p_data => x_msg_data,
2172 p_encoded => FND_API.G_FALSE
2173 );
2174 --
2175 IF l_debug_on THEN
2176 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured ',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2177 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
2178 END IF;
2179 --
2180 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2181 ROLLBACK TO create_update_stop_wrap_grp;
2182 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
2183 FND_MSG_PUB.Count_And_Get
2184 (
2185 p_count => x_msg_count,
2186 p_data => x_msg_data,
2187 p_encoded => FND_API.G_FALSE
2188 );
2189 --
2190 IF l_debug_on THEN
2191 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2192 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
2193 END IF;
2194 --
2195 WHEN WSH_UTIL_CORE.G_EXC_WARNING THEN
2196 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
2197 FND_MSG_PUB.Count_And_Get
2198 (
2199 p_count => x_msg_count,
2200 p_data => x_msg_data,
2201 p_encoded => FND_API.G_FALSE
2202 );
2203 --
2204 IF l_debug_on THEN
2205 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_UTIL_CORE.G_EXC_WARNING exception has occured ',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2206 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_UTIL_CORE.G_EXC_WARNING');
2207 END IF;
2208 WHEN OTHERS THEN
2209 ROLLBACK TO create_update_stop_wrap_grp;
2210 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
2211 wsh_util_core.default_handler('WSH_INTERFACE_GRP.CREATE_UPDATE_STOP');
2212 FND_MSG_PUB.Count_And_Get
2213 (
2214 p_count => x_msg_count,
2215 p_data => x_msg_data,
2216 p_encoded => FND_API.G_FALSE
2217 );
2218 --
2219 IF l_debug_on THEN
2220 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2221 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
2222 END IF;
2223 --
2224 END Create_Update_Stop;
2225
2226
2227 PROCEDURE Rtrim_trips_blank_space (
2228 p_in_rec IN WSH_TRIPS_PVT.trip_rec_type,
2229 p_out_rec OUT NOCOPY WSH_TRIPS_PVT.trip_rec_type) IS
2230 l_debug_on BOOLEAN;
2231 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' ||
2232 G_PKG_NAME || '.' || 'Rtrim_trips_blank_space';
2233 BEGIN
2234
2235 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2236 --
2237 IF l_debug_on IS NULL
2238 THEN
2239 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2240 END IF;
2241 --
2242 IF l_debug_on THEN
2243 wsh_debug_sv.push (l_module_name, 'Rtrim_trips_blank_space');
2244 END IF;
2245
2246 p_out_rec := p_in_rec;
2247
2248 p_out_rec.NAME := RTRIM(p_in_rec.NAME);
2249 p_out_rec.PLANNED_FLAG := RTRIM(p_in_rec.PLANNED_FLAG);
2250 p_out_rec.STATUS_CODE := RTRIM(p_in_rec.STATUS_CODE);
2251 p_out_rec.VEHICLE_NUMBER := RTRIM(p_in_rec.VEHICLE_NUMBER);
2252 p_out_rec.VEHICLE_NUM_PREFIX := RTRIM(p_in_rec.VEHICLE_NUM_PREFIX);
2253 p_out_rec.SHIP_METHOD_CODE := RTRIM(p_in_rec.SHIP_METHOD_CODE);
2254 p_out_rec.ROUTING_INSTRUCTIONS := RTRIM(p_in_rec.ROUTING_INSTRUCTIONS);
2255 p_out_rec.ATTRIBUTE_CATEGORY := RTRIM(p_in_rec.ATTRIBUTE_CATEGORY);
2256 p_out_rec.ATTRIBUTE1 := RTRIM(p_in_rec.ATTRIBUTE1);
2257 p_out_rec.ATTRIBUTE2 := RTRIM(p_in_rec.ATTRIBUTE2);
2258 p_out_rec.ATTRIBUTE3 := RTRIM(p_in_rec.ATTRIBUTE3);
2259 p_out_rec.ATTRIBUTE4 := RTRIM(p_in_rec.ATTRIBUTE4);
2260 p_out_rec.ATTRIBUTE5 := RTRIM(p_in_rec.ATTRIBUTE5);
2261 p_out_rec.ATTRIBUTE6 := RTRIM(p_in_rec.ATTRIBUTE6);
2262 p_out_rec.ATTRIBUTE7 := RTRIM(p_in_rec.ATTRIBUTE7);
2263 p_out_rec.ATTRIBUTE8 := RTRIM(p_in_rec.ATTRIBUTE8);
2264 p_out_rec.ATTRIBUTE9 := RTRIM(p_in_rec.ATTRIBUTE9);
2265 p_out_rec.ATTRIBUTE10 := RTRIM(p_in_rec.ATTRIBUTE10);
2266 p_out_rec.ATTRIBUTE11 := RTRIM(p_in_rec.ATTRIBUTE11);
2267 p_out_rec.ATTRIBUTE12 := RTRIM(p_in_rec.ATTRIBUTE12);
2268 p_out_rec.ATTRIBUTE13 := RTRIM(p_in_rec.ATTRIBUTE13);
2269 p_out_rec.ATTRIBUTE14 := RTRIM(p_in_rec.ATTRIBUTE14);
2270 p_out_rec.ATTRIBUTE15 := RTRIM(p_in_rec.ATTRIBUTE15);
2271 p_out_rec.SERVICE_LEVEL:= RTRIM(p_in_rec.SERVICE_LEVEL);
2272 p_out_rec.MODE_OF_TRANSPORT:= RTRIM(p_in_rec.MODE_OF_TRANSPORT);
2273 p_out_rec.FREIGHT_TERMS_CODE:= RTRIM(p_in_rec.FREIGHT_TERMS_CODE);
2274 p_out_rec.CONSOLIDATION_ALLOWED:= RTRIM(p_in_rec.CONSOLIDATION_ALLOWED);
2275 p_out_rec.LOAD_TENDER_STATUS:= RTRIM(p_in_rec.LOAD_TENDER_STATUS);
2276 p_out_rec.BOOKING_NUMBER:= RTRIM(p_in_rec.BOOKING_NUMBER);
2277 p_out_rec.ARRIVE_AFTER_TRIP_NAME:= RTRIM(p_in_rec.ARRIVE_AFTER_TRIP_NAME);
2278 p_out_rec.SHIP_METHOD_NAME:= RTRIM(p_in_rec.SHIP_METHOD_NAME);
2279 p_out_rec.VEHICLE_ITEM_DESC:= RTRIM(p_in_rec.VEHICLE_ITEM_DESC);
2280 p_out_rec.VEHICLE_ORGANIZATION_CODE:= RTRIM(p_in_rec.VEHICLE_ORGANIZATION_CODE);
2281 p_out_rec.VESSEL := RTRIM(p_in_rec.VESSEL);
2282 p_out_rec.VOYAGE_NUMBER := RTRIM(p_in_rec.VOYAGE_NUMBER);
2283 p_out_rec.PORT_OF_LOADING := RTRIM(p_in_rec.PORT_OF_LOADING);
2284 p_out_rec.PORT_OF_DISCHARGE := RTRIM(p_in_rec.PORT_OF_DISCHARGE);
2285 p_out_rec.WF_NAME := RTRIM(p_in_rec.WF_NAME);
2286 p_out_rec.WF_PROCESS_NAME := RTRIM(p_in_rec.WF_PROCESS_NAME);
2287 p_out_rec.WF_ITEM_KEY := RTRIM(p_in_rec.WF_ITEM_KEY);
2288 p_out_rec.WAIT_TIME_UOM := RTRIM(p_in_rec.WAIT_TIME_UOM);
2289 p_out_rec.CARRIER_RESPONSE := RTRIM(p_in_rec.CARRIER_RESPONSE);
2290 p_out_rec.OPERATOR := RTRIM(p_in_rec.OPERATOR);
2291 p_out_rec.CARRIER_REFERENCE_NUMBER := RTRIM(p_in_rec.CARRIER_REFERENCE_NUMBER);
2292 p_out_rec.CONSIGNEE_CARRIER_AC_NO := RTRIM(p_in_rec.CONSIGNEE_CARRIER_AC_NO);
2293
2294 IF l_debug_on THEN
2295 WSH_DEBUG_SV.pop(l_module_name);
2296 END IF;
2297
2298 EXCEPTION
2299
2300 WHEN OTHERS THEN
2301 wsh_util_core.default_handler (
2302 'WSH_TRIP_STOPS_GRP.Rtrim_trips_blank_space', l_module_name);
2303 IF l_debug_on THEN
2304 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured.'||
2305 ' Oracle error message is '|| SQLERRM,
2306 WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2307 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
2308 END IF;
2309 RAISE;
2310
2311 END Rtrim_trips_blank_space;
2312 --========================================================================
2313 -- PROCEDURE : Create_Update_Trip Wrapper API
2314 --
2315 -- PARAMETERS: p_api_version_number known api versionerror buffer
2316 -- p_init_msg_list FND_API.G_TRUE to reset list
2317 -- x_return_status return status
2318 -- x_msg_count number of messages in the list
2319 -- x_msg_data text of messages
2320 -- p_trip_info_tab Table of Attributes for the trip entity
2321 -- p_IN_rec Input Attributes for the trip entity
2322 -- p_OUT_rec Table of output Attributes for the trip entity
2323 -- VERSION : current version 1.0
2324 -- initial version 1.0
2325 -- COMMENT : This calls Core API WSH_TRIPS_GRP.Create_Update_Trip.
2326 --========================================================================
2327 PROCEDURE Create_Update_Trip(
2328 p_api_version_number IN NUMBER,
2329 p_init_msg_list IN VARCHAR2,
2330 p_commit IN VARCHAR2,
2331 x_return_status OUT NOCOPY VARCHAR2,
2332 x_msg_count OUT NOCOPY NUMBER,
2333 x_msg_data OUT NOCOPY VARCHAR2,
2334 p_trip_info_tab IN WSH_TRIPS_PVT.Trip_Attr_Tbl_Type,
2335 p_In_rec IN WSH_TRIPS_GRP.tripInRecType,
2336 x_Out_Tab OUT NOCOPY WSH_TRIPS_GRP.trip_Out_Tab_Type) IS
2337
2338 l_api_version_number CONSTANT NUMBER := 1.0;
2339 l_api_name CONSTANT VARCHAR2(30) := 'Create_Update_Trip';
2340 l_debug_on BOOLEAN;
2341 l_index NUMBER;
2342 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'CREATE_UPDATE_TRIP';
2343
2344 l_num_warnings NUMBER := 0;
2345 l_num_errors NUMBER := 0;
2346 l_trip_info_tab WSH_TRIPS_PVT.Trip_Attr_Tbl_Type;
2347 l_In_rec WSH_TRIPS_GRP.tripInRecType;
2348
2349 BEGIN
2350 --
2351 --
2352 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2353 --
2354 IF l_debug_on IS NULL
2355 THEN
2356 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2357 END IF;
2358 --
2359 SAVEPOINT create_update_trip_wrap_grp;
2360
2361 IF l_debug_on THEN
2362 wsh_debug_sv.push(l_module_name);
2363 wsh_debug_sv.log(l_module_name,'Caller is ', p_in_rec.caller);
2364 wsh_debug_sv.log(l_module_name,'Phase is ', p_in_rec.phase);
2365 wsh_debug_sv.log(l_module_name,'Action Code is ', p_in_rec.action_code);
2366 wsh_debug_sv.log(l_module_name,'Number of Records is ', p_trip_info_tab.COUNT);
2367 END IF;
2368
2369 l_in_rec := p_in_rec;
2370 l_in_rec.caller := RTRIM(p_in_rec.caller);
2371 l_in_rec.action_code := RTRIM(p_in_rec.action_code);
2372
2373 l_index := p_trip_info_tab.FIRST;
2374 WHILE l_index IS NOT NULL LOOP
2375 Rtrim_trips_blank_space(p_trip_info_tab(l_index),
2376 l_trip_info_tab(l_index));
2377 l_index := p_trip_info_tab.NEXT(l_index);
2378 END LOOP;
2379
2380 WSH_TRIPS_GRP.Create_Update_Trip(
2381 p_api_version_number => p_api_version_number,
2382 p_init_msg_list => p_init_msg_list,
2383 p_commit => p_commit,
2384 x_return_status => x_return_status,
2385 x_msg_count => x_msg_count,
2386 x_msg_data => x_msg_data,
2387 p_trip_info_tab => l_trip_info_tab,
2388 p_In_rec => l_In_rec,
2389 x_Out_tab => x_Out_Tab);
2390
2391 wsh_util_core.api_post_call(
2392 p_return_status =>x_return_status,
2393 x_num_warnings =>l_num_warnings,
2394 x_num_errors =>l_num_errors);
2395 --
2396 IF l_num_warnings > 0 THEN
2397 RAISE WSH_UTIL_CORE.G_EXC_WARNING;
2398 END IF;
2399 --
2400 FND_MSG_PUB.Count_And_Get (
2401 p_count => x_msg_count,
2402 p_data => x_msg_data);
2403 --
2404 IF l_debug_on THEN
2405 WSH_DEBUG_SV.pop(l_module_name);
2406 END IF;
2407 --
2408 EXCEPTION
2409 WHEN FND_API.G_EXC_ERROR THEN
2410 ROLLBACK TO create_update_trip_wrap_grp;
2411 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2412 FND_MSG_PUB.Count_And_Get
2413 (
2414 p_count => x_msg_count,
2415 p_data => x_msg_data,
2416 p_encoded => FND_API.G_FALSE
2417 );
2418 --
2419 IF l_debug_on THEN
2420 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured ',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2421 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
2422 END IF;
2423 --
2424 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2425 ROLLBACK TO create_update_trip_wrap_grp;
2426 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
2427 FND_MSG_PUB.Count_And_Get
2428 (
2429 p_count => x_msg_count,
2430 p_data => x_msg_data,
2431 p_encoded => FND_API.G_FALSE
2432 );
2433 --
2434 IF l_debug_on THEN
2435 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2436 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
2437 END IF;
2438 --
2439 WHEN WSH_UTIL_CORE.G_EXC_WARNING THEN
2440 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
2441 FND_MSG_PUB.Count_And_Get
2442 (
2443 p_count => x_msg_count,
2444 p_data => x_msg_data,
2445 p_encoded => FND_API.G_FALSE
2446 );
2447 --
2448 IF l_debug_on THEN
2449 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_UTIL_CORE.G_EXC_WARNING exception has occured ',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2450 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_UTIL_CORE.G_EXC_WARNING');
2451 END IF;
2452 WHEN OTHERS THEN
2453 ROLLBACK TO create_update_trip_wrap_grp;
2454 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
2455 wsh_util_core.default_handler('WSH_INTERFACE_GRP.CREATE_UPDATE_TRIP');
2456 FND_MSG_PUB.Count_And_Get
2457 (
2458 p_count => x_msg_count,
2459 p_data => x_msg_data,
2460 p_encoded => FND_API.G_FALSE
2461 );
2462 --
2463 IF l_debug_on THEN
2464 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2465 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
2466 END IF;
2467 --
2468 END Create_Update_Trip;
2469
2470
2471 PROCEDURE Rtrim_freightcost_blank_space (
2472 p_in_rec IN WSH_FREIGHT_COSTS_PVT.Freight_Cost_Rec_Type,
2473 p_out_rec OUT NOCOPY WSH_FREIGHT_COSTS_PVT.Freight_Cost_Rec_Type) IS
2474 l_debug_on BOOLEAN;
2475 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' ||
2476 G_PKG_NAME || '.' || 'Rtrim_freightcost_blank_space';
2477 BEGIN
2478
2479 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2480 --
2481 IF l_debug_on IS NULL
2482 THEN
2483 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2484 END IF;
2485 --
2486 IF l_debug_on THEN
2487 wsh_debug_sv.push (l_module_name, 'Rtrim_freightcost_blank_space');
2488 END IF;
2489
2490 p_out_rec := p_in_rec;
2491
2492 p_out_rec.CALCULATION_METHOD := RTRIM(p_in_rec.CALCULATION_METHOD);
2493 p_out_rec.UOM := RTRIM(p_in_rec.UOM);
2494 p_out_rec.CURRENCY_CODE := RTRIM(p_in_rec.CURRENCY_CODE);
2495 p_out_rec.CONVERSION_TYPE_CODE := RTRIM(p_in_rec.CONVERSION_TYPE_CODE);
2496 p_out_rec.ATTRIBUTE_CATEGORY:= RTRIM(p_in_rec.ATTRIBUTE_CATEGORY);
2497 p_out_rec.ATTRIBUTE1:= RTRIM(p_in_rec.ATTRIBUTE1);
2498 p_out_rec.ATTRIBUTE2:= RTRIM(p_in_rec.ATTRIBUTE2);
2499 p_out_rec.ATTRIBUTE3 := RTRIM(p_in_rec.ATTRIBUTE3);
2500 p_out_rec.ATTRIBUTE4 := RTRIM(p_in_rec.ATTRIBUTE4);
2501 p_out_rec.ATTRIBUTE5 := RTRIM(p_in_rec.ATTRIBUTE5);
2502 p_out_rec.ATTRIBUTE6:= RTRIM(p_in_rec.ATTRIBUTE6);
2503 p_out_rec.ATTRIBUTE7:= RTRIM(p_in_rec.ATTRIBUTE7);
2504 p_out_rec.ATTRIBUTE8:= RTRIM(p_in_rec.ATTRIBUTE8);
2505 p_out_rec.ATTRIBUTE9 := RTRIM(p_in_rec.ATTRIBUTE9);
2506 p_out_rec.ATTRIBUTE10 := RTRIM(p_in_rec.ATTRIBUTE10);
2507 p_out_rec.ATTRIBUTE11 := RTRIM(p_in_rec.ATTRIBUTE11);
2508 p_out_rec.ATTRIBUTE12 := RTRIM(p_in_rec.ATTRIBUTE12);
2509 p_out_rec.ATTRIBUTE13 := RTRIM(p_in_rec.ATTRIBUTE13);
2510 p_out_rec.ATTRIBUTE14:= RTRIM(p_in_rec.ATTRIBUTE14);
2511 p_out_rec.ATTRIBUTE15:= RTRIM(p_in_rec.ATTRIBUTE15);
2512 p_out_rec.CHARGE_SOURCE_CODE:= RTRIM(p_in_rec.CHARGE_SOURCE_CODE);
2513 p_out_rec.LINE_TYPE_CODE:= RTRIM(p_in_rec.LINE_TYPE_CODE);
2514 p_out_rec.ESTIMATED_FLAG:= RTRIM(p_in_rec.ESTIMATED_FLAG);
2515 p_out_rec.FREIGHT_CODE := RTRIM(p_in_rec.FREIGHT_CODE);
2516 p_out_rec.TRIP_NAME := RTRIM(p_in_rec.TRIP_NAME);
2517 p_out_rec.DELIVERY_NAME := RTRIM(p_in_rec.DELIVERY_NAME);
2518 p_out_rec.FREIGHT_COST_TYPE := RTRIM(p_in_rec.FREIGHT_COST_TYPE);
2519
2520 IF l_debug_on THEN
2521 WSH_DEBUG_SV.pop(l_module_name);
2522 END IF;
2523
2524 EXCEPTION
2525
2526 WHEN OTHERS THEN
2527 wsh_util_core.default_handler (
2528 'WSH_TRIP_STOPS_GRP.Rtrim_freightcost_blank_space', l_module_name);
2529 IF l_debug_on THEN
2530 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured.'||
2531 ' Oracle error message is '|| SQLERRM,
2532 WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2533 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
2534 END IF;
2535 RAISE;
2536
2537 END Rtrim_freightcost_blank_space;
2538
2539
2540 PROCEDURE Create_Update_Freight_Costs(
2541 p_api_version_number IN NUMBER,
2542 p_init_msg_list IN VARCHAR2,
2543 p_commit IN VARCHAR2,
2544 x_return_status OUT NOCOPY VARCHAR2,
2545 x_msg_count OUT NOCOPY NUMBER,
2546 x_msg_data OUT NOCOPY VARCHAR2,
2547 p_freight_info_tab IN WSH_FREIGHT_COSTS_GRP.freight_rec_tab_type,
2548 p_in_rec IN WSH_FREIGHT_COSTS_GRP.freightInRecType,
2549 x_out_tab OUT NOCOPY WSH_FREIGHT_COSTS_GRP.freight_out_tab_type) IS
2550
2551 l_api_version_number CONSTANT NUMBER := 1.0;
2552 l_api_name CONSTANT VARCHAR2(30) := 'Create_Update_Freight_Costs';
2553 l_debug_on BOOLEAN;
2554 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'Create_Update_Freight_Costs';
2555
2556 l_num_warnings NUMBER := 0;
2557 l_num_errors NUMBER := 0;
2558 l_freight_info_tab WSH_FREIGHT_COSTS_GRP.freight_rec_tab_type;
2559 l_index NUMBER;
2560 l_in_rec WSH_FREIGHT_COSTS_GRP.freightInRecType;
2561
2562 BEGIN
2563 --
2564 --
2565 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2566 --
2567 IF l_debug_on IS NULL
2568 THEN
2569 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2570 END IF;
2571 --
2572 SAVEPOINT Create_Update_Freight_Costs_WG;
2573
2574 IF l_debug_on THEN
2575 wsh_debug_sv.push(l_module_name);
2576 wsh_debug_sv.log(l_module_name,'Caller is ', p_in_rec.caller);
2577 wsh_debug_sv.log(l_module_name,'Phase is ', p_in_rec.phase);
2578 wsh_debug_sv.log(l_module_name,'Action Code is ', p_in_rec.action_code);
2579 END IF;
2580
2581 l_in_rec := p_in_rec;
2582 l_in_rec.caller := RTRIM(p_in_rec.caller);
2583 l_in_rec.action_code := RTRIM(p_in_rec.action_code);
2584
2585 l_index := p_freight_info_tab.FIRST;
2586 WHILE l_index IS NOT NULL LOOP
2587 Rtrim_freightcost_blank_space(p_freight_info_tab(l_index),
2588 l_freight_info_tab(l_index));
2589 l_index := p_freight_info_tab.NEXT(l_index);
2590 END LOOP;
2591
2592 WSH_FREIGHT_COSTS_GRP.Create_Update_Freight_Costs(
2593 p_api_version_number => p_api_version_number,
2594 p_init_msg_list => p_init_msg_list,
2595 p_commit => p_commit,
2596 x_return_status => x_return_status,
2597 x_msg_count => x_msg_count,
2598 x_msg_data => x_msg_data,
2599 p_freight_info_tab => l_freight_info_tab,
2600 p_in_rec => l_in_rec,
2601 x_out_tab => x_out_tab );
2602
2603 wsh_util_core.api_post_call(
2604 p_return_status =>x_return_status,
2605 x_num_warnings =>l_num_warnings,
2606 x_num_errors =>l_num_errors);
2607 --
2608 IF l_num_warnings > 0 THEN
2609 RAISE WSH_UTIL_CORE.G_EXC_WARNING;
2610 END IF;
2611 --
2612 FND_MSG_PUB.Count_And_Get (
2613 p_count => x_msg_count,
2614 p_data => x_msg_data);
2615 --
2616 IF l_debug_on THEN
2617 WSH_DEBUG_SV.pop(l_module_name);
2618 END IF;
2619 --
2620 EXCEPTION
2621 WHEN FND_API.G_EXC_ERROR THEN
2622 ROLLBACK TO Create_Update_Freight_Costs_WG;
2623 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2624 FND_MSG_PUB.Count_And_Get
2625 (
2626 p_count => x_msg_count,
2627 p_data => x_msg_data,
2628 p_encoded => FND_API.G_FALSE
2629 );
2630 --
2631 IF l_debug_on THEN
2632 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured ',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2633 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
2634 END IF;
2635 --
2636 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2637 ROLLBACK TO Create_Update_Freight_Costs_WG;
2638 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
2639 FND_MSG_PUB.Count_And_Get
2640 (
2641 p_count => x_msg_count,
2642 p_data => x_msg_data,
2643 p_encoded => FND_API.G_FALSE
2644 );
2645 --
2646 IF l_debug_on THEN
2647 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2648 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
2649 END IF;
2650 --
2651 WHEN WSH_UTIL_CORE.G_EXC_WARNING THEN
2652 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
2653 FND_MSG_PUB.Count_And_Get
2654 (
2655 p_count => x_msg_count,
2656 p_data => x_msg_data,
2657 p_encoded => FND_API.G_FALSE
2658 );
2659 --
2660 IF l_debug_on THEN
2661 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_UTIL_CORE.G_EXC_WARNING exception has occured ',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2662 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_UTIL_CORE.G_EXC_WARNING');
2663 END IF;
2664 WHEN OTHERS THEN
2665 ROLLBACK TO Create_Update_Freight_Costs_WG;
2666 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
2667 wsh_util_core.default_handler('WSH_INTERFACE_GRP.CREATE_UPDATE_FREIGHT_COSTS');
2668 FND_MSG_PUB.Count_And_Get
2669 (
2670 p_count => x_msg_count,
2671 p_data => x_msg_data,
2672 p_encoded => FND_API.G_FALSE
2673 );
2674 --
2675 IF l_debug_on THEN
2676 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2677 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
2678 END IF;
2679 --
2680 END Create_Update_Freight_costs;
2681 -- heali
2682
2683 --========================================================================
2684 -- PROCEDURE : Update_Delivery_Leg Wrapper API PUBLIC
2685 --
2686 -- PARAMETERS: p_api_version_number known api version error buffer
2687 -- p_init_msg_list FND_API.G_TRUE to reset list
2688 -- x_return_status return status
2689 -- x_msg_count number of messages in the list
2690 -- x_msg_data text of messages
2691 -- p_in_rec Record for caller, phase
2692 -- and action_code ('UPDATE' )
2693 -- p_delivery_leg_tab Table of Attributes for the delivery leg entity
2694 -- x_out_rec for future usage.
2695 -- VERSION : current version 1.0
2696 -- initial version 1.0
2697 -- COMMENT : Updates a record in wsh_delivery_legs table with information
2698 -- specified in p_delivery_leg_tab. Please note that as per perfomance
2699 -- standards, if you need to update a field to null, then use the
2700 -- fnd_api.g_miss_(num/char/date) value for that field. If a field
2701 -- has a null value, it will not be updated.
2702
2703 PROCEDURE Update_Delivery_Leg(
2704 p_api_version_number IN NUMBER,
2705 p_init_msg_list IN VARCHAR2,
2706 p_commit IN VARCHAR2,
2707 p_delivery_leg_tab IN WSH_DELIVERY_LEGS_GRP.dlvy_leg_tab_type,
2708 p_in_rec IN WSH_DELIVERY_LEGS_GRP.action_parameters_rectype,
2709 x_out_rec OUT NOCOPY WSH_DELIVERY_LEGS_GRP.action_out_rec_type,
2710 x_return_status OUT NOCOPY VARCHAR2,
2711 x_msg_count OUT NOCOPY NUMBER,
2712 x_msg_data OUT NOCOPY VARCHAR2) IS
2713
2714
2715 l_api_version_number CONSTANT NUMBER := 1.0;
2716 l_api_name CONSTANT VARCHAR2(30) := 'Update_Delivery_Leg';
2717 l_debug_on BOOLEAN;
2718 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'UPDATE_DELIVERY_LEGS';
2719
2720 BEGIN
2721
2722 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2723 --
2724 IF l_debug_on IS NULL
2725 THEN
2726 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2727 END IF;
2728 IF l_debug_on THEN
2729 WSH_DEBUG_SV.push(l_module_name);
2730 END IF;
2731
2732 WSH_DELIVERY_LEGS_GRP.Update_Delivery_Leg(
2733 p_api_version_number => p_api_version_number,
2734 p_init_msg_list => p_init_msg_list,
2735 p_commit => p_commit,
2736 p_delivery_leg_tab => p_delivery_leg_tab,
2737 p_in_rec => p_in_rec,
2738 x_out_rec => x_out_rec,
2739 x_return_status => x_return_status,
2740 x_msg_count => x_msg_count,
2741 x_msg_data => x_msg_data);
2742
2743 IF l_debug_on THEN
2744 WSH_DEBUG_SV.pop(l_module_name);
2745 END IF;
2746
2747
2748 EXCEPTION
2749
2750 WHEN others THEN
2751 wsh_util_core.default_handler('WSH_DELIVERY_LEGS_GRP.Update_Delivery_Leg',l_module_name);
2752 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2753 --
2754 IF l_debug_on THEN
2755 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2756 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
2757 END IF;
2758
2759 END Update_Delivery_Leg;
2760
2761
2762 END WSH_INTERFACE_GRP;