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