[Home] [Help]
PACKAGE BODY: APPS.WSH_CONTAINER_GRP
Source
1 PACKAGE BODY WSH_CONTAINER_GRP AS
2 /* $Header: WSHCOGPB.pls 120.6.12010000.2 2008/08/04 12:29:32 suppal ship $ */
3
4 -- standard global constants
5 G_PKG_NAME CONSTANT VARCHAR2(30) := 'WSH_CONTAINER_GRP';
6 p_message_type CONSTANT VARCHAR2(1) := 'E';
7
8
9 ------------------------------------------------------------------------------
10 -- Procedure: Create_Containers
11 --
12 -- Parameters: 1) container_item_id (key flex id)
13 -- 2) container_item_name (concatinated name for container item)
14 -- 3) container_item_seg (flex field seg array for item name)
15 -- 4) organization_id - organization id for container
16 -- 5) organization_code - organization code for container
17 -- 6) name_prefix - container name prefix
18 -- 7) name_suffix - container name suffix
19 -- 8) base_number - starting number for numeric portion of name
20 -- 9) num_digits - precision for number of digits
21 -- 10) quantity - number of containers
22 -- 11) container_name - container name if creating 1 container
23 -- 12) table of container ids - out table of ids
24 -- 13) other standard parameters
25 --
26 -- Description: This procedure takes in a container item id or container item
27 -- name and other necessary parameters to create one or more containers and
28 -- creates the required containers. It returns a table of container instance
29 -- ids (delivery detail ids) along with the standard out parameters.
30 --
31 -- THIS PROCEDURE SHOULD NOT BE USED ANYMORE, IT IS USED ONLY FOR BACKWARD
32 -- COMPATIBILITY.
33 --
34 ------------------------------------------------------------------------------
35
36 PROCEDURE Create_Containers (
37 -- Standard parameters
38 p_api_version IN NUMBER,
39 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
40 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
41 p_validation_level IN NUMBER DEFAULT FND_API.G_VALID_LEVEL_FULL,
42 x_return_status OUT NOCOPY VARCHAR2,
43 x_msg_count OUT NOCOPY NUMBER,
44 x_msg_data OUT NOCOPY VARCHAR2,
45
46 -- program specific parameters
47 p_container_item_id IN NUMBER,
48 p_container_item_name IN VARCHAR2,
49 p_container_item_seg IN FND_FLEX_EXT.SegmentArray,
50 p_organization_id IN NUMBER,
51 p_organization_code IN VARCHAR2,
52 p_name_prefix IN VARCHAR2,
53 p_name_suffix IN VARCHAR2,
54 p_base_number IN NUMBER,
55 p_num_digits IN NUMBER,
56 p_quantity IN NUMBER,
57 p_container_name IN VARCHAR2,
58 p_lpn_ids IN WSH_UTIL_CORE.ID_TAB_TYPE,
59
60 -- program specific out parameters
61 x_container_ids OUT NOCOPY WSH_UTIL_CORE.ID_TAB_TYPE,
62 p_caller IN VARCHAR2 -- Has default 'WMS' in spec
63 ) IS
64
65
66 -- Standard call to check for call compatibility
67 l_api_version CONSTANT NUMBER := 1.0;
68 l_api_name CONSTANT VARCHAR2(30):= 'Create_Containers';
69
70 l_return_status VARCHAR2(30) := NULL;
71 l_cont_item_id NUMBER := NULL;
72 l_cont_item_seg FND_FLEX_EXT.SegmentArray;
73 l_cont_item_name VARCHAR2(30) := NULL;
74 l_cont_name VARCHAR2(30) := NULL;
75
76 l_cont_inst_tab WSH_UTIL_CORE.ID_TAB_TYPE;
77 l_msg_summary VARCHAR2(2000) := NULL;
78 l_msg_details VARCHAR2(4000) := NULL;
79
80 l_organization_id NUMBER := NULL;
81 l_organization_code VARCHAR2(240) := NULL;
82
83 -- Bug fix 2657429
84 l_verify_org_level NUMBER;
85 l_verify_cont_item NUMBER;
86 -- Bug fix 2657429
87
88 /* wms changes begin */
89 wms_return_status VARCHAR2(10);
90 wms_msg_count NUMBER;
91 wms_msg_data VARCHAR2(2000);
92 wms_organization_id NUMBER;
93 wms_enabled BOOLEAN;
94 /* -- wms changes end */
95
96 --lpn conv
97 l_detail_info_tab WSH_GLBL_VAR_STRCT_GRP.delivery_details_Attr_tbl_Type;
98 l_IN_rec WSH_GLBL_VAR_STRCT_GRP.detailInRecType;
99 l_OUT_rec WSH_GLBL_VAR_STRCT_GRP.detailOutRecType;
100
101 l_msg_count NUMBER;
102 l_msg_data VARCHAR2(2000);
103
104 WSH_NO_INV_ITEM EXCEPTION;
105 WSH_INVALID_QTY EXCEPTION;
106 WSH_FAIL_CONT_CREATION EXCEPTION;
107 WSH_LPN_COUNT_INVALID EXCEPTION;
108
109 --
110 l_debug_on BOOLEAN;
111 --
112 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'CREATE_CONTAINERS';
113 --
114 BEGIN
115
116 -- Standard begin of API savepoint
117 --
118 -- Debug Statements
119 --
120 --
121 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
122 --
123 IF l_debug_on IS NULL
124 THEN
125 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
126 END IF;
127 --
128 IF l_debug_on THEN
129 WSH_DEBUG_SV.push(l_module_name);
130 --
131 WSH_DEBUG_SV.log(l_module_name,'P_API_VERSION',P_API_VERSION);
132 WSH_DEBUG_SV.log(l_module_name,'P_INIT_MSG_LIST',P_INIT_MSG_LIST);
133 WSH_DEBUG_SV.log(l_module_name,'P_COMMIT',P_COMMIT);
134 WSH_DEBUG_SV.log(l_module_name,'P_VALIDATION_LEVEL',P_VALIDATION_LEVEL);
135 WSH_DEBUG_SV.log(l_module_name,'P_CONTAINER_ITEM_ID',P_CONTAINER_ITEM_ID);
136 WSH_DEBUG_SV.log(l_module_name,'P_CONTAINER_ITEM_NAME',P_CONTAINER_ITEM_NAME);
137 WSH_DEBUG_SV.log(l_module_name,'P_ORGANIZATION_ID',P_ORGANIZATION_ID);
138 WSH_DEBUG_SV.log(l_module_name,'P_ORGANIZATION_CODE',P_ORGANIZATION_CODE);
139 WSH_DEBUG_SV.log(l_module_name,'P_NAME_PREFIX',P_NAME_PREFIX);
140 WSH_DEBUG_SV.log(l_module_name,'P_NAME_SUFFIX',P_NAME_SUFFIX);
141 WSH_DEBUG_SV.log(l_module_name,'P_BASE_NUMBER',P_BASE_NUMBER);
142 WSH_DEBUG_SV.log(l_module_name,'P_NUM_DIGITS',P_NUM_DIGITS);
143 WSH_DEBUG_SV.log(l_module_name,'P_QUANTITY',P_QUANTITY);
144 WSH_DEBUG_SV.log(l_module_name,'P_CONTAINER_NAME',P_CONTAINER_NAME);
145 END IF;
146 --
147 SAVEPOINT Create_Containers_SP_GRP;
148
149 IF NOT FND_API.compatible_api_call (
150 l_api_version,
151 p_api_version,
152 l_api_name,
153 G_PKG_NAME) THEN
154 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
155 END IF;
156
157 -- Check p_init_msg_list
158 IF FND_API.to_boolean(p_init_msg_list) THEN
159 FND_MSG_PUB.initialize;
160 END IF;
161
162 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
163
164 --lpn conv
165 -- validate quantity
166 IF NVL(p_quantity,0) <> 1 THEN
167 RAISE WSH_INVALID_QTY;
168 END IF;
169
170 IF p_caller NOT LIKE 'WMS%' THEN
171 IF l_debug_on THEN
172 WSH_DEBUG_SV.log(l_module_name,'Invalid caller',p_caller);
173 END IF;
174 RAISE FND_API.G_EXC_ERROR;
175 END IF;
176
177 IF p_lpn_ids.COUNT = 0 THEN
178 IF l_debug_on THEN
179 wsh_debug_sv.logmsg(l_module_name,'lpn_id cannot be null');
180 END IF;
181 FND_MESSAGE.SET_NAME('WSH','WSH_REQUIRED_FIELD_NULL');
182 FND_MESSAGE.SET_TOKEN('FIELD_NAME','LPN_ID');
183 WSH_UTIL_CORE.Add_Message(WSH_UTIL_CORE.G_RET_STS_ERROR,l_module_name);
184 RAISE FND_API.G_EXC_ERROR;
185 ELSIF p_lpn_ids(1) = NULL THEN
186 FND_MESSAGE.SET_NAME('WSH','WSH_REQUIRED_FIELD_NULL');
187 FND_MESSAGE.SET_TOKEN('FIELD_NAME','LPN_ID');
188 WSH_UTIL_CORE.Add_Message(WSH_UTIL_CORE.G_RET_STS_ERROR,l_module_name);
189 RAISE FND_API.G_EXC_ERROR;
190 END IF;
191
192 -- Bug fix 2657429
193 IF p_validation_level <> C_DELIVERY_DETAIL_CALL THEN
194 WSH_ACTIONS_LEVELS.set_validation_level (
195 p_entity => 'DLVB',
196 p_caller => p_caller,
197 p_phase => 1,
198 p_action => 'CREATE',
199 x_return_status => l_return_status);
200
201 END IF;
202
203 l_verify_org_level := WSH_ACTIONS_LEVELS.g_validation_level_tab(WSH_ACTIONS_LEVELS.C_CONTAINER_ORG_LVL);
204 l_verify_cont_item := WSH_ACTIONS_LEVELS.g_validation_level_tab(WSH_ACTIONS_LEVELS.C_CONT_ITEM_LVL);
205 -- Bug fix 2657429
206 -- validate the organization id and organization code
207
208 l_organization_id := p_organization_id;
209 l_organization_code := p_organization_code;
210
211 IF l_verify_org_level = 1 THEN
212 IF l_debug_on THEN
213 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_VALIDATE.VALIDATE_ORG',WSH_DEBUG_SV.C_PROC_LEVEL);
214 END IF;
215 --
216 WSH_UTIL_VALIDATE.Validate_Org (l_organization_id,
217 l_organization_code,
218 x_return_status);
219 IF x_return_status <> wsh_util_core.g_ret_sts_success THEN
220 fnd_message.set_name('WSH', 'WSH_OI_INVALID_ORG');
221 wsh_util_core.add_message(wsh_util_core.g_ret_sts_error, l_module_name);
222 raise fnd_api.g_exc_error;
223 END IF;
224
225 END IF;
226
227 -- validate the key flex valaues (item id and item name)
228
229 l_cont_item_id := p_container_item_id;
230 l_cont_item_seg := p_container_item_seg;
231 l_cont_item_name := p_container_item_name;
232 --
233 IF l_verify_cont_item = 1 THEN
234 /* wms change: Validate Item check is to be skipped if the Org. is WMS enabled */
235
236 IF l_debug_on THEN
237 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WMS_INSTALL.CHECK_INSTALL',WSH_DEBUG_SV.C_PROC_LEVEL);
238 END IF;
239
240 wms_enabled := WMS_INSTALL.check_install(
241 wms_return_status,
242 wms_msg_count,
243 wms_msg_data,
244 l_organization_id);
245
246 IF l_debug_on THEN
247 wsh_debug_sv.log(l_module_name, 'wms enabled', wms_enabled);
248 END IF;
249
250 IF (wms_enabled = FALSE) THEN
251 IF (l_cont_item_id IS NULL
252 AND l_cont_item_name IS NULL
253 AND l_cont_item_seg.count = 0) then
254 fnd_message.set_name('WSH', 'WSH_CONT_INVALID_ITEM');
255 WSH_UTIL_CORE.ADD_MESSAGE(wsh_util_core.g_ret_sts_error, l_module_name);
256 RAISE WSH_NO_INV_ITEM;
257 ELSE
258 IF l_debug_on THEN
259 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_VALIDATE.VALIDATE_ITEM',WSH_DEBUG_SV.C_PROC_LEVEL);
260 END IF;
261 --
262 WSH_UTIL_VALIDATE.Validate_Item
263 (p_inventory_item_id => l_cont_item_id,
264 p_inventory_item => l_cont_item_name,
265 p_organization_id => l_organization_id,
266 p_seg_array => l_cont_item_seg,
267 x_return_status => x_return_status,
268 p_item_type => 'CONT_ITEM');
269
270 IF x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
271 fnd_message.set_name('WSH', 'WSH_CONT_INVALID_ITEM');
272 WSH_UTIL_CORE.ADD_MESSAGE(wsh_util_core.g_ret_sts_error, l_module_name);
273 RAISE WSH_NO_INV_ITEM;
274 END IF;
275 END IF;
276 ELSE
277 fnd_message.set_name('WSH', 'WSH_INCORRECT_ORG');
278 fnd_message.set_token('ORG_CODE', l_organization_code);
279 wsh_util_core.add_message(wsh_util_core.g_ret_sts_error, l_module_name);
280 RAISE FND_API.G_EXC_ERROR;
281 END IF; -- wms_enabled
282 END IF;
283
284
285 l_IN_rec.caller := p_caller;
286 l_IN_rec.action_code := 'CREATE';
287 l_IN_rec.quantity := 1;
288 l_detail_info_tab(1).organization_id := l_organization_id;
289 l_detail_info_tab(1).inventory_item_id := l_cont_item_id;
290 l_detail_info_tab(1).container_name := p_container_name;
291 l_detail_info_tab(1).lpn_id := p_lpn_ids(1);
292
293 WSH_WMS_LPN_GRP.create_update_containers
294 ( p_api_version => p_api_version,
295 p_init_msg_list => p_init_msg_list,
296 p_commit => p_commit,
297 x_return_status => l_return_status,
298 x_msg_count => l_msg_count,
299 x_msg_data => l_msg_data,
300 p_detail_info_tab => l_detail_info_tab,
301 p_IN_rec => l_IN_rec,
302 x_OUT_rec => l_OUT_rec
303 );
304 IF l_return_status NOT IN ( WSH_UTIL_CORE.G_RET_STS_SUCCESS,WSH_UTIL_CORE.G_RET_STS_WARNING)
305 THEN
306 RAISE WSH_FAIL_CONT_CREATION;
307 END IF;
308
309 x_return_status := l_return_status;
310
311 x_container_ids := l_OUT_rec.detail_ids;
312
313
314
315 IF FND_API.TO_BOOLEAN(p_commit) THEN
316 -- dbms_output.put_line('commit');
317 COMMIT;
318 END IF;
319
320 FND_MSG_PUB.Count_And_Get
321 ( p_count => x_msg_count
322 , p_data => x_msg_data
323 , p_encoded => FND_API.G_FALSE
324 );
325
326
327 IF l_debug_on THEN
328 WSH_DEBUG_SV.pop(l_module_name);
329 END IF;
330 --
331 EXCEPTION
332 WHEN FND_API.G_EXC_ERROR THEN
333 ROLLBACK TO Create_Containers_SP_GRP;
334 x_return_status := FND_API.G_RET_STS_ERROR ;
335 FND_MSG_PUB.Count_And_Get
336 (
337 p_count => x_msg_count,
338 p_data => x_msg_data,
339 p_encoded => FND_API.G_FALSE
340 );
341 --
342 IF l_debug_on THEN
343 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
344 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
345 END IF;
346
347 WHEN WSH_NO_INV_ITEM then
348 rollback to Create_Containers_SP_GRP;
349 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
350
351 FND_MSG_PUB.Count_And_Get
352 ( p_count => x_msg_count
353 , p_data => x_msg_data
354 , p_encoded => FND_API.G_FALSE
355 );
356
357
358 IF l_debug_on THEN
359 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_NO_INV_ITEM exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
360 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_NO_INV_ITEM');
361 END IF;
362 --
363 WHEN WSH_INVALID_QTY then
364 rollback to Create_Containers_SP_GRP;
365 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
366 fnd_message.set_name('WSH', 'WSH_CONT_INVALID_QTY');
367 WSH_UTIL_CORE.ADD_MESSAGE(x_return_status);
368
369 FND_MSG_PUB.Count_And_Get
370 ( p_count => x_msg_count
371 , p_data => x_msg_data
372 , p_encoded => FND_API.G_FALSE
373 );
374
375 IF l_debug_on THEN
376 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_INVALID_QTY exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
377 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_INVALID_QTY');
378 END IF;
379 --
380 WHEN WSH_FAIL_CONT_CREATION then
381 rollback to Create_Containers_SP_GRP;
382 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
383 fnd_message.set_name('WSH', 'WSH_CONT_CREATE_ERROR');
384 WSH_UTIL_CORE.ADD_MESSAGE(x_return_status);
385
386 FND_MSG_PUB.Count_And_Get
387 ( p_count => x_msg_count
388 , p_data => x_msg_data
389 , p_encoded => FND_API.G_FALSE
390 );
391
392 IF l_debug_on THEN
393 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_FAIL_CONT_CREATION exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
394 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_FAIL_CONT_CREATION');
395 END IF;
396 --
397 WHEN WSH_LPN_COUNT_INVALID then
398 rollback to Create_Containers_SP_GRP;
399 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
400 fnd_message.set_name('WSH', 'WSH_LPN_COUNT_INVALID');
401 WSH_UTIL_CORE.ADD_MESSAGE(x_return_status);
402
403 FND_MSG_PUB.Count_And_Get
404 ( p_count => x_msg_count
405 , p_data => x_msg_data
406 , p_encoded => FND_API.G_FALSE
407 );
408
409 IF l_debug_on THEN
410 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_LPN_COUNT_INVALID exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
411 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_LPN_COUNT_INVALID');
412 END IF;
413 --
414 WHEN OTHERS then
415 rollback to Create_Containers_SP_GRP;
416 wsh_util_core.default_handler('WSH_CONTAINER_GRP.Create_Containers');
417 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
418 FND_MSG_PUB.Count_And_Get
419 ( p_count => x_msg_count
420 , p_data => x_msg_data
421 , p_encoded => FND_API.G_FALSE
422 );
423
424 IF l_debug_on THEN
425 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
426 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
427 END IF;
428 --
429 END Create_Containers;
430
431
432 ------------------------------------------------------------------------------
433 -- Procedure: Update_Container
434 --
435 -- Parameters: 1) container_rec - container record of type
436 -- wsh_interface.changedattributerectype
437 -- 2) other standard parameters
438 --
439 -- Description: This procedure takes in a record of container attributes that
440 -- contains the name and delivery detail id of container to update the
441 -- container record in WSH_DELIVERY_DETAILS with the attributes input in the
442 -- container rec type. The API validates the container name and detail id and
443 -- calls the wsh_delivery_details_grp.update_shipping_attributes public API.
444
445 -- THIS PROCEDURE SHOULD NOT BE USED ANYMORE, IT IS USED ONLY FOR BACKWARD
446 -- COMPATIBILITY.
447 ------------------------------------------------------------------------------
448
449 PROCEDURE Update_Container (
450 -- Standard parameters
451 p_api_version IN NUMBER,
452 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
453 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
454 p_validation_level IN NUMBER DEFAULT FND_API.G_VALID_LEVEL_FULL,
455 x_return_status OUT NOCOPY VARCHAR2,
456 x_msg_count OUT NOCOPY NUMBER,
457 x_msg_data OUT NOCOPY VARCHAR2,
458
459 -- program specific parameters
460 p_container_rec IN WSH_CONTAINER_GRP.CHANGEDATTRIBUTETABTYPE
461
462 ) IS
463
464 -- Standard call to check for call compatibility
465 l_api_version CONSTANT NUMBER := 1.0;
466 l_api_name CONSTANT VARCHAR2(30):= 'Update_Containers';
467
468 l_return_status VARCHAR2(30) := NULL;
469
470 l_cont_name VARCHAR2(30) := NULL;
471 l_cont_flag VARCHAR2(1);
472 l_cont_instance_id NUMBER;
473
474 l_old_cont_name VARCHAR2(30);
475 l_old_cont_instance_id NUMBER;
476 l_old_lpn_id NUMBER;
477 l_old_rel_status VARCHAR2(1);
478
479
480 l_cont_tab WSH_INTERFACE.CHANGEDATTRIBUTETABTYPE;
481
482 l_msg_summary VARCHAR2(2000) := NULL;
483 l_msg_details VARCHAR2(4000) := NULL;
484
485 l_delivery_id NUMBER;
486 l_del_status VARCHAR2(10);
487 l_detail_info_tab WSH_GLBL_VAR_STRCT_GRP.delivery_details_Attr_tbl_Type;
488 l_IN_rec WSH_GLBL_VAR_STRCT_GRP.detailInRecType;
489 l_OUT_rec WSH_GLBL_VAR_STRCT_GRP.detailOutRecType;
490
491 l_msg_count NUMBER;
492 l_msg_data VARCHAR2(2000);
493
494 WSH_INVALID_CONT EXCEPTION;
495 WSH_INVALID_CONT_UPDATE EXCEPTION;
496 WSH_FAIL_CONT_UPDATE EXCEPTION;
497
498 --lpn conv
499 CURSOR Check_Cont (v_cont_id NUMBER ) IS
500 SELECT container_flag, delivery_detail_id, lpn_id, released_status
501 FROM WSH_DELIVERY_DETAILS
502 WHERE delivery_detail_id = v_cont_id
503 AND nvl(LINE_DIRECTION , 'O') IN ('O', 'IO');
504
505 CURSOR Check_Cont_Name (v_cont_name VARCHAR2) IS
506 SELECT delivery_detail_id, container_name
507 FROM WSH_DELIVERY_DETAILS
508 WHERE container_name = v_cont_name
509 AND nvl(LINE_DIRECTION , 'O') IN ('O', 'IO');
510
511 CURSOR c_get_lpn (l_delivery_detail_id NUMBER) IS
512 SELECT lpn_id
513 FROM wsh_delivery_details
514 WHERE delivery_detail_id = l_delivery_detail_id;
515
516
517 --
518 l_debug_on BOOLEAN;
519 --
520 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'UPDATE_CONTAINER';
521 --
522 BEGIN
523
524 -- Standard begin of API savepoint
525 --
526 -- Debug Statements
527 --
528 --
529 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
530 --
531 IF l_debug_on IS NULL
532 THEN
533 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
534 END IF;
535 --
536 IF l_debug_on THEN
537 WSH_DEBUG_SV.push(l_module_name);
538 --
539 WSH_DEBUG_SV.log(l_module_name,'P_API_VERSION',P_API_VERSION);
540 WSH_DEBUG_SV.log(l_module_name,'P_INIT_MSG_LIST',P_INIT_MSG_LIST);
541 WSH_DEBUG_SV.log(l_module_name,'P_COMMIT',P_COMMIT);
542 WSH_DEBUG_SV.log(l_module_name,'P_VALIDATION_LEVEL',P_VALIDATION_LEVEL);
543 END IF;
544 --
545 SAVEPOINT Update_Containers_SP_Grp;
546
547 IF NOT FND_API.compatible_api_call (
548 l_api_version,
549 p_api_version,
550 l_api_name,
551 G_PKG_NAME) THEN
552 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
553 END IF;
554
555 -- Check p_init_msg_list
556 IF FND_API.to_boolean(p_init_msg_list) THEN
557 FND_MSG_PUB.initialize;
558 END IF;
559
560 /*
561 IF p_caller NOT LIKE 'WMS%' THEN
562 IF l_debug_on THEN
563 WSH_DEBUG_SV.log(l_module_name,'Invalid caller',p_caller);
564 END IF;
565 RAISE FND_API.G_EXC_ERROR;
566 END IF;
567 p_caller does not exist
568 */
569
570 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
571
572 -- check to make sure that the container record being input for update
573 -- does not have null delivery detail ids or container name or the
574 -- container flag is null or 'N'
575 IF p_container_rec(1).delivery_detail_id IS NULL OR p_container_rec(1).container_name IS NULL OR nvl(p_container_rec(1).container_flag,'N') = 'N' THEN
576 RAISE WSH_INVALID_CONT_UPDATE;
577 END IF;
578
579 -- Now check for valid container based on the information in the
580 -- container rec.
581
582
583 IF p_container_rec(1).delivery_detail_id IS NOT NULL AND p_container_rec(1).container_name IS NOT NULL THEN
584
585 l_cont_instance_id := p_container_rec(1).delivery_detail_id;
586 l_cont_name := p_container_rec(1).container_name;
587
588 OPEN Check_Cont (l_cont_instance_id );
589
590 FETCH Check_Cont INTO
591 l_cont_flag,
592 l_old_cont_instance_id,
593 l_old_lpn_id,
594 l_old_rel_status;
595
596 IF Check_Cont%NOTFOUND OR Check_Cont%ROWCOUNT > 1 THEN
597 CLOSE Check_Cont;
598 RAISE WSH_INVALID_CONT;
599 END IF;
600
601 IF Check_Cont%ISOPEN THEN
602 CLOSE Check_Cont;
603 END IF;
604
605 -- if the input container instance id does not match the
606 -- delivery detail id in the database (for the given container
607 -- name) then error out..
608
609 IF l_old_cont_instance_id <> l_cont_instance_id OR l_cont_flag <> 'Y' THEN
610 RAISE WSH_INVALID_CONT;
611 END IF;
612 -- Bug 2657803/2911387, Let WMS update container name and null out LPN ID if container has been shipped,
613 -- and LPN id is not null.
614 -- If not, validate delivery status and container name
615 IF l_old_lpn_id IS NULL or l_old_rel_status <> 'C' or p_container_rec(1).lpn_id is NOT NULL THEN
616
617
618 /* lpn conv
619 OPEN Check_Cont_Name (l_cont_name);
620
621 FETCH Check_Cont_Name INTO
622 l_old_cont_instance_id,
623 l_old_cont_name;
624
625 IF Check_Cont_Name%FOUND AND l_old_cont_instance_id <> l_cont_instance_id THEN
626 CLOSE Check_Cont_Name;
627 RAISE WSH_INVALID_CONT_UPDATE;
628 END IF;
629
630 IF Check_Cont_Name%ISOPEN THEN
631 CLOSE Check_Cont_Name;
632 END IF;
633 */
634
635 WSH_CONTAINER_UTILITIES.Get_Delivery_Status (
636 l_old_cont_instance_id,
637 l_delivery_id,
638 l_del_status,
639 x_return_status);
640
641 IF x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
642 RAISE WSH_FAIL_CONT_UPDATE;
643 END IF;
644
645 -- Bug: 1577876.
646 -- 1. Update is allowed even if Container is not assigned to a Delivery.
647 -- 2. if Assigned then Updateable only if Delivery Status not in 'IT', 'CL', 'CO'
648 IF (l_delivery_id <> -99 AND l_del_status in ('IT', 'CL', 'CO', 'SR', 'SC') ) THEN
649 RAISE WSH_FAIL_CONT_UPDATE;
650 END IF;
651
652
653 END IF;
654
655
656 -- call update delivery details public API to update entire
657 -- container record with all validations..
658
659
660 --l_cont_tab(1) := p_container_rec(1);
661
662 --
663 l_IN_rec.caller := WSH_GLBL_VAR_STRCT_GRP.c_skip_miss_info;
664 l_IN_rec.action_code := 'UPDATE';
665 l_detail_info_tab(1).delivery_detail_id := p_container_rec(1).delivery_detail_id ;
666 l_detail_info_tab(1).source_code := p_container_rec(1).source_code ;
667 l_detail_info_tab(1).source_header_id := p_container_rec(1).source_header_id ;
668 l_detail_info_tab(1).source_line_id := p_container_rec(1).source_line_id ;
669 l_detail_info_tab(1).customer_id := p_container_rec(1).customer_id ;
670 l_detail_info_tab(1).sold_to_contact_id := p_container_rec(1).sold_to_contact_id ;
671 l_detail_info_tab(1).inventory_item_id := p_container_rec(1).inventory_item_id ;
672 l_detail_info_tab(1).item_description := p_container_rec(1).item_description ;
673 l_detail_info_tab(1).hazard_class_id := p_container_rec(1).hazard_class_id ;
674 l_detail_info_tab(1).country_of_origin := p_container_rec(1).country_of_origin ;
675 l_detail_info_tab(1).classification := p_container_rec(1).classification ;
676 l_detail_info_tab(1).ship_to_contact_id := p_container_rec(1).ship_to_contact_id ;
677 l_detail_info_tab(1).ship_to_site_use_id := p_container_rec(1).ship_to_site_use_id ;
678 l_detail_info_tab(1).deliver_to_contact_id := p_container_rec(1).deliver_to_contact_id ;
679 l_detail_info_tab(1).intmed_ship_to_contact_id := p_container_rec(1).intmed_ship_to_contact_id ;
680 l_detail_info_tab(1).hold_code := p_container_rec(1).hold_code ;
681 l_detail_info_tab(1).ship_tolerance_above := p_container_rec(1).ship_tolerance_above ;
682 l_detail_info_tab(1).ship_tolerance_below := p_container_rec(1).ship_tolerance_below ;
683 l_detail_info_tab(1).shipped_quantity := p_container_rec(1).shipped_quantity ;
684 l_detail_info_tab(1).delivered_quantity := p_container_rec(1).delivered_quantity ;
685 l_detail_info_tab(1).subinventory := p_container_rec(1).subinventory ;
686 l_detail_info_tab(1).revision := p_container_rec(1).revision ;
687 l_detail_info_tab(1).lot_number := p_container_rec(1).lot_number ;
688 l_detail_info_tab(1).customer_requested_lot_flag := p_container_rec(1).customer_requested_lot_flag ;
689 l_detail_info_tab(1).serial_number := p_container_rec(1).serial_number ;
690 l_detail_info_tab(1).locator_id := p_container_rec(1).locator_id ;
691 l_detail_info_tab(1).date_requested := p_container_rec(1).date_requested ;
692 l_detail_info_tab(1).date_scheduled := p_container_rec(1).date_scheduled ;
693 l_detail_info_tab(1).master_container_item_id := p_container_rec(1).master_container_item_id ;
694 l_detail_info_tab(1).detail_container_item_id := p_container_rec(1).detail_container_item_id ;
695 l_detail_info_tab(1).load_seq_number := p_container_rec(1).load_seq_number ;
696 l_detail_info_tab(1).carrier_id := p_container_rec(1).carrier_id ;
697 l_detail_info_tab(1).freight_terms_code := p_container_rec(1).freight_terms_code ;
698 l_detail_info_tab(1).shipment_priority_code := p_container_rec(1).shipment_priority_code ;
699 l_detail_info_tab(1).fob_code := p_container_rec(1).fob_code ;
700 l_detail_info_tab(1).customer_item_id := p_container_rec(1).customer_item_id ;
701 l_detail_info_tab(1).dep_plan_required_flag := p_container_rec(1).dep_plan_required_flag ;
702 l_detail_info_tab(1).customer_prod_seq := p_container_rec(1).customer_prod_seq ;
703 l_detail_info_tab(1).customer_dock_code := p_container_rec(1).customer_dock_code ;
704 l_detail_info_tab(1).cust_model_serial_number := p_container_rec(1).cust_model_serial_number ;
705 l_detail_info_tab(1).customer_job := p_container_rec(1).customer_job ;
706 l_detail_info_tab(1).customer_production_line := p_container_rec(1).customer_production_line ;
707 l_detail_info_tab(1).net_weight := p_container_rec(1).net_weight ;
708 l_detail_info_tab(1).weight_uom_code := p_container_rec(1).weight_uom_code ;
709 l_detail_info_tab(1).volume := p_container_rec(1).volume ;
710 l_detail_info_tab(1).volume_uom_code := p_container_rec(1).volume_uom_code ;
711 l_detail_info_tab(1).tp_attribute_category := p_container_rec(1).tp_attribute_category ;
712 l_detail_info_tab(1).tp_attribute1 := p_container_rec(1).tp_attribute1 ;
713 l_detail_info_tab(1).tp_attribute2 := p_container_rec(1).tp_attribute2 ;
714 l_detail_info_tab(1).tp_attribute3 := p_container_rec(1).tp_attribute3 ;
715 l_detail_info_tab(1).tp_attribute4 := p_container_rec(1).tp_attribute4 ;
716 l_detail_info_tab(1).tp_attribute5 := p_container_rec(1).tp_attribute5 ;
717 l_detail_info_tab(1).tp_attribute6 := p_container_rec(1).tp_attribute6 ;
718 l_detail_info_tab(1).tp_attribute7 := p_container_rec(1).tp_attribute7 ;
719 l_detail_info_tab(1).tp_attribute8 := p_container_rec(1).tp_attribute8 ;
720 l_detail_info_tab(1).tp_attribute9 := p_container_rec(1).tp_attribute9 ;
721 l_detail_info_tab(1).tp_attribute10 := p_container_rec(1).tp_attribute10 ;
722 l_detail_info_tab(1).tp_attribute11 := p_container_rec(1).tp_attribute11 ;
723 l_detail_info_tab(1).tp_attribute12 := p_container_rec(1).tp_attribute12 ;
724 l_detail_info_tab(1).tp_attribute13 := p_container_rec(1).tp_attribute13 ;
725 l_detail_info_tab(1).tp_attribute14 := p_container_rec(1).tp_attribute14 ;
726 l_detail_info_tab(1).tp_attribute15 := p_container_rec(1).tp_attribute15 ;
727 l_detail_info_tab(1).attribute_category := p_container_rec(1).attribute_category ;
728 l_detail_info_tab(1).attribute1 := p_container_rec(1).attribute1 ;
729 l_detail_info_tab(1).attribute2 := p_container_rec(1).attribute2 ;
730 l_detail_info_tab(1).attribute3 := p_container_rec(1).attribute3 ;
731 l_detail_info_tab(1).attribute4 := p_container_rec(1).attribute4 ;
732 l_detail_info_tab(1).attribute5 := p_container_rec(1).attribute5 ;
733 l_detail_info_tab(1).attribute6 := p_container_rec(1).attribute6 ;
734 l_detail_info_tab(1).attribute7 := p_container_rec(1).attribute7 ;
735 l_detail_info_tab(1).attribute8 := p_container_rec(1).attribute8 ;
736 l_detail_info_tab(1).attribute9 := p_container_rec(1).attribute9 ;
737 l_detail_info_tab(1).attribute10 := p_container_rec(1).attribute10 ;
738 l_detail_info_tab(1).attribute11 := p_container_rec(1).attribute11 ;
739 l_detail_info_tab(1).attribute12 := p_container_rec(1).attribute12 ;
740 l_detail_info_tab(1).attribute13 := p_container_rec(1).attribute13 ;
741 l_detail_info_tab(1).attribute14 := p_container_rec(1).attribute14 ;
742 l_detail_info_tab(1).attribute15 := p_container_rec(1).attribute15 ;
743 l_detail_info_tab(1).request_id := p_container_rec(1).request_id ;
744 l_detail_info_tab(1).mvt_stat_status := p_container_rec(1).mvt_stat_status ;
745 l_detail_info_tab(1).organization_id := p_container_rec(1).organization_id ;
746 l_detail_info_tab(1).transaction_temp_id := p_container_rec(1).transaction_temp_id ;
747 l_detail_info_tab(1).ship_set_id := p_container_rec(1).ship_set_id ;
748 l_detail_info_tab(1).arrival_set_id := p_container_rec(1).arrival_set_id ;
749 l_detail_info_tab(1).ship_model_complete_flag := p_container_rec(1).ship_model_complete_flag ;
750 l_detail_info_tab(1).top_model_line_id := p_container_rec(1).top_model_line_id ;
751 l_detail_info_tab(1).source_header_number := p_container_rec(1).source_header_number ;
752 l_detail_info_tab(1).source_header_type_id := p_container_rec(1).source_header_type_id ;
753 l_detail_info_tab(1).source_header_type_name := p_container_rec(1).source_header_type_name ;
754 l_detail_info_tab(1).cust_po_number := p_container_rec(1).cust_po_number ;
755 l_detail_info_tab(1).ato_line_id := p_container_rec(1).ato_line_id ;
756 l_detail_info_tab(1).src_requested_quantity := p_container_rec(1).src_requested_quantity ;
757 l_detail_info_tab(1).src_requested_quantity_uom := p_container_rec(1).src_requested_quantity_uom ;
758 l_detail_info_tab(1).move_order_line_id := p_container_rec(1).move_order_line_id ;
759 l_detail_info_tab(1).cancelled_quantity := p_container_rec(1).cancelled_quantity ;
760 l_detail_info_tab(1).quality_control_quantity := p_container_rec(1).quality_control_quantity ;
761 l_detail_info_tab(1).cycle_count_quantity := p_container_rec(1).cycle_count_quantity ;
762 l_detail_info_tab(1).tracking_number := p_container_rec(1).tracking_number ;
763 l_detail_info_tab(1).movement_id := p_container_rec(1).movement_id ;
764 l_detail_info_tab(1).shipping_instructions := p_container_rec(1).shipping_instructions ;
765 l_detail_info_tab(1).packing_instructions := p_container_rec(1).packing_instructions ;
766 l_detail_info_tab(1).project_id := p_container_rec(1).project_id ;
767 l_detail_info_tab(1).task_id := p_container_rec(1).task_id ;
768 l_detail_info_tab(1).org_id := p_container_rec(1).org_id ;
769 l_detail_info_tab(1).oe_interfaced_flag := p_container_rec(1).oe_interfaced_flag ;
770 l_detail_info_tab(1).inv_interfaced_flag := p_container_rec(1).inv_interfaced_flag ;
771 l_detail_info_tab(1).inspection_flag := p_container_rec(1).inspection_flag ;
772 l_detail_info_tab(1).released_status := p_container_rec(1).released_status ;
773 l_detail_info_tab(1).container_flag := p_container_rec(1).container_flag ;
774 l_detail_info_tab(1).container_type_code := p_container_rec(1).container_type_code ;
775 l_detail_info_tab(1).container_name := p_container_rec(1).container_name ;
776 l_detail_info_tab(1).fill_percent := p_container_rec(1).fill_percent ;
777 l_detail_info_tab(1).gross_weight := p_container_rec(1).gross_weight ;
778 l_detail_info_tab(1).master_serial_number := p_container_rec(1).master_serial_number ;
779 l_detail_info_tab(1).maximum_load_weight := p_container_rec(1).maximum_load_weight ;
780 l_detail_info_tab(1).maximum_volume := p_container_rec(1).maximum_volume ;
781 l_detail_info_tab(1).minimum_fill_percent := p_container_rec(1).minimum_fill_percent ;
782 l_detail_info_tab(1).seal_code := p_container_rec(1).seal_code ;
783 l_detail_info_tab(1).unit_number := p_container_rec(1).unit_number ;
784 l_detail_info_tab(1).unit_price := p_container_rec(1).unit_price ;
785 l_detail_info_tab(1).currency_code := p_container_rec(1).currency_code ;
786 l_detail_info_tab(1).freight_class_cat_id := p_container_rec(1).freight_class_cat_id ;
787 l_detail_info_tab(1).commodity_code_cat_id := p_container_rec(1).commodity_code_cat_id ;
788 l_detail_info_tab(1).preferred_grade := p_container_rec(1).preferred_grade ;
789 l_detail_info_tab(1).shipped_quantity2 := p_container_rec(1).shipped_quantity2 ;
790 l_detail_info_tab(1).delivered_quantity2 := p_container_rec(1).delivered_quantity2 ;
791 l_detail_info_tab(1).cancelled_quantity2 := p_container_rec(1).cancelled_quantity2 ;
792 l_detail_info_tab(1).quality_control_quantity2 := p_container_rec(1).quality_control_quantity2 ;
793 l_detail_info_tab(1).cycle_count_quantity2 := p_container_rec(1).cycle_count_quantity2 ;
794
795 l_detail_info_tab(1).lpn_id := p_container_rec(1).lpn_id ;
796
797 IF( p_container_rec(1).lpn_id IS NULL ) OR
798 (p_container_rec(1).lpn_id = FND_API.G_MISS_NUM)
799 THEN
800 IF p_container_rec(1).lpn_id IS NULL THEN
801 l_IN_rec.action_code := 'UPDATE_NULL';
802 END IF;
803 OPEN c_get_lpn(p_container_rec(1).delivery_detail_id);
804 FETCH c_get_lpn INTO l_detail_info_tab(1).lpn_id;
805 CLOSE c_get_lpn;
806 END IF;
807
808 l_detail_info_tab(1).pickable_flag := p_container_rec(1).pickable_flag ;
809 l_detail_info_tab(1).original_subinventory := p_container_rec(1).original_subinventory ;
810 l_detail_info_tab(1).to_serial_number := p_container_rec(1).to_serial_number ;
811 l_detail_info_tab(1).picked_quantity := p_container_rec(1).picked_quantity ;
812 l_detail_info_tab(1).picked_quantity2 := p_container_rec(1).picked_quantity2 ;
813 l_detail_info_tab(1).received_quantity := p_container_rec(1).received_quantity ;
814 l_detail_info_tab(1).received_quantity2 := p_container_rec(1).received_quantity2 ;
815 l_detail_info_tab(1).source_line_set_id := p_container_rec(1).source_line_set_id ;
816 l_detail_info_tab(1).ship_from_location_id := FND_API.G_MISS_NUM;
817 l_detail_info_tab(1).ship_to_location_id := FND_API.G_MISS_NUM;
818 l_detail_info_tab(1).deliver_to_location_id := FND_API.G_MISS_NUM;
819 l_detail_info_tab(1).deliver_to_site_use_id := p_container_rec(1).deliver_to_org_id;
820 l_detail_info_tab(1).intmed_ship_to_location_id := FND_API.G_MISS_NUM;
821 l_detail_info_tab(1).requested_quantity := FND_API.G_MISS_NUM;
822 l_detail_info_tab(1).requested_quantity_uom := FND_API.G_MISS_CHAR;
823 l_detail_info_tab(1).ship_method_code := p_container_rec(1).shipping_method_code;
824 l_detail_info_tab(1).created_by := FND_API.G_MISS_NUM;
825 l_detail_info_tab(1).creation_date := FND_API.G_MISS_DATE;
826 l_detail_info_tab(1).last_update_date := FND_API.G_MISS_DATE;
827 l_detail_info_tab(1).last_update_login := FND_API.G_MISS_NUM;
828 l_detail_info_tab(1).last_updated_by := FND_API.G_MISS_NUM;
829 l_detail_info_tab(1).program_application_id := FND_API.G_MISS_NUM;
830 l_detail_info_tab(1).program_id := FND_API.G_MISS_NUM;
831 l_detail_info_tab(1).program_update_date := FND_API.G_MISS_DATE;
832 l_detail_info_tab(1).released_flag := FND_API.G_MISS_CHAR;
833 l_detail_info_tab(1).split_from_detail_id := p_container_rec(1).split_from_delivery_detail_id;
834 l_detail_info_tab(1).source_line_number := FND_API.G_MISS_CHAR;
835 l_detail_info_tab(1).src_requested_quantity2 := FND_API.G_MISS_NUM;
836 l_detail_info_tab(1).src_requested_quantity_uom2 := FND_API.G_MISS_CHAR;
837 l_detail_info_tab(1).requested_quantity2 := FND_API.G_MISS_NUM;
838 l_detail_info_tab(1).requested_quantity_uom2 := FND_API.G_MISS_CHAR;
839 l_detail_info_tab(1).batch_id := FND_API.G_MISS_NUM;
840 --l_detail_info_tab(1).ROWID := FND_API.G_MISS_CHAR;
841 l_detail_info_tab(1).transaction_id := FND_API.G_MISS_NUM;
842 l_detail_info_tab(1).VENDOR_ID := FND_API.G_MISS_NUM;
843 l_detail_info_tab(1).SHIP_FROM_SITE_ID := FND_API.G_MISS_NUM;
844 l_detail_info_tab(1).LINE_DIRECTION := FND_API.G_MISS_CHAR;
845 l_detail_info_tab(1).PARTY_ID := FND_API.G_MISS_NUM;
846 l_detail_info_tab(1).ROUTING_REQ_ID := FND_API.G_MISS_NUM;
847 l_detail_info_tab(1).SHIPPING_CONTROL := FND_API.G_MISS_CHAR;
848 l_detail_info_tab(1).SOURCE_BLANKET_REFERENCE_ID := FND_API.G_MISS_NUM;
849 l_detail_info_tab(1).SOURCE_BLANKET_REFERENCE_NUM := FND_API.G_MISS_NUM;
850 l_detail_info_tab(1).PO_SHIPMENT_LINE_ID := FND_API.G_MISS_NUM;
851 l_detail_info_tab(1).PO_SHIPMENT_LINE_NUMBER := FND_API.G_MISS_NUM;
852 l_detail_info_tab(1).RETURNED_QUANTITY := FND_API.G_MISS_NUM;
853 l_detail_info_tab(1).RETURNED_QUANTITY2 := FND_API.G_MISS_NUM;
854 l_detail_info_tab(1).RCV_SHIPMENT_LINE_ID := FND_API.G_MISS_NUM;
855 l_detail_info_tab(1).SOURCE_LINE_TYPE_CODE := FND_API.G_MISS_CHAR;
856 l_detail_info_tab(1).SUPPLIER_ITEM_NUMBER := FND_API.G_MISS_CHAR;
857 l_detail_info_tab(1).IGNORE_FOR_PLANNING := FND_API.G_MISS_CHAR;
858 l_detail_info_tab(1).EARLIEST_PICKUP_DATE := FND_API.G_MISS_DATE;
859 l_detail_info_tab(1).LATEST_PICKUP_DATE := FND_API.G_MISS_DATE;
860 l_detail_info_tab(1).EARLIEST_DROPOFF_DATE := FND_API.G_MISS_DATE;
861 l_detail_info_tab(1).LATEST_DROPOFF_DATE := FND_API.G_MISS_DATE;
862 l_detail_info_tab(1).REQUEST_DATE_TYPE_CODE := FND_API.G_MISS_CHAR;
863 l_detail_info_tab(1).tp_delivery_detail_id := FND_API.G_MISS_NUM;
864 l_detail_info_tab(1).source_document_type_id := FND_API.G_MISS_NUM;
865 l_detail_info_tab(1).unit_weight := FND_API.G_MISS_NUM;
866 l_detail_info_tab(1).unit_volume := FND_API.G_MISS_NUM;
867 l_detail_info_tab(1).filled_volume := FND_API.G_MISS_NUM;
868 l_detail_info_tab(1).wv_frozen_flag := FND_API.G_MISS_CHAR;
869 l_detail_info_tab(1).mode_of_transport := FND_API.G_MISS_CHAR;
870 l_detail_info_tab(1).service_level := FND_API.G_MISS_CHAR;
871 l_detail_info_tab(1).po_revision_number := FND_API.G_MISS_NUM;
872 l_detail_info_tab(1).release_revision_number := FND_API.G_MISS_NUM;
873
874 WSH_WMS_LPN_GRP.create_update_containers
875 ( p_api_version => p_api_version,
876 p_init_msg_list => p_init_msg_list,
877 p_commit => p_commit,
878 x_return_status => l_return_status,
879 x_msg_count => l_msg_count,
880 x_msg_data => l_msg_data,
881 p_detail_info_tab => l_detail_info_tab,
882 p_IN_rec => l_IN_rec,
883 x_OUT_rec => l_OUT_rec
884 );
885
886
887 IF x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
888 RAISE WSH_FAIL_CONT_UPDATE;
889 END IF;
890
891 END IF; -- if p_container_rec is not null
892
893
894 IF FND_API.TO_BOOLEAN(p_commit) THEN
895 -- dbms_output.put_line('commit');
896 COMMIT;
897 END IF;
898
899 FND_MSG_PUB.Count_And_Get
900 ( p_count => x_msg_count
901 , p_data => x_msg_data
902 , p_encoded => FND_API.G_FALSE
903 );
904
905
906 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
907
908 --
909 -- Debug Statements
910 --
911 IF l_debug_on THEN
912 WSH_DEBUG_SV.pop(l_module_name);
913 END IF;
914 --
915 EXCEPTION
916
917 WHEN WSH_INVALID_CONT_UPDATE then
918 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
919 rollback to Update_Containers_SP_Grp;
920 fnd_message.set_name('WSH', 'WSH_CONT_INVALID_UPDATE');
921 WSH_UTIL_CORE.ADD_MESSAGE(x_return_status);
922 /*
923 WSH_UTIL_CORE.get_messages('Y', l_msg_summary, l_msg_details, x_msg_count);
924 if x_msg_count > 1 then
925 x_msg_data := l_msg_summary || l_msg_details;
926 else
927 x_msg_data := l_msg_summary;
928 end if;
929 */
930 FND_MSG_PUB.Count_And_Get
931 ( p_count => x_msg_count
932 , p_data => x_msg_data
933 , p_encoded => FND_API.G_FALSE
934 );
935
936 --
937 -- Debug Statements
938 --
939 IF l_debug_on THEN
940 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_INVALID_CONT_UPDATE exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
941 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_INVALID_CONT_UPDATE');
942 END IF;
943 --
944 WHEN WSH_INVALID_CONT then
945 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
946 rollback to Update_Containers_SP_Grp;
947 fnd_message.set_name('WSH', 'WSH_CONT_INVALID_NAME');
948 WSH_UTIL_CORE.ADD_MESSAGE(x_return_status);
949 /*
950 WSH_UTIL_CORE.get_messages('Y', l_msg_summary, l_msg_details, x_msg_count);
951 if x_msg_count > 1 then
952 x_msg_data := l_msg_summary || l_msg_details;
953 else
954 x_msg_data := l_msg_summary;
955 end if;
956 */
957 FND_MSG_PUB.Count_And_Get
958 ( p_count => x_msg_count
959 , p_data => x_msg_data
960 , p_encoded => FND_API.G_FALSE
961 );
962 --
963 -- Debug Statements
964 --
965 IF l_debug_on THEN
966 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_INVALID_CONT exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
967 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_INVALID_CONT');
968 END IF;
969 --
970 WHEN WSH_FAIL_CONT_UPDATE then
971 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
972 rollback to Update_Containers_SP_Grp;
973 fnd_message.set_name('WSH', 'WSH_CONT_UPDATE_ERROR');
974 WSH_UTIL_CORE.ADD_MESSAGE(x_return_status);
975 /*
976 WSH_UTIL_CORE.get_messages('Y', l_msg_summary, l_msg_details, x_msg_count);
977 if x_msg_count > 1 then
978 x_msg_data := l_msg_summary || l_msg_details;
979 else
980 x_msg_data := l_msg_summary;
981 end if;
982 */
983 FND_MSG_PUB.Count_And_Get
984 ( p_count => x_msg_count
985 , p_data => x_msg_data
986 , p_encoded => FND_API.G_FALSE
987 );
988
989 --
990 -- Debug Statements
991 --
992 IF l_debug_on THEN
993 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_FAIL_CONT_UPDATE exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
994 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_FAIL_CONT_UPDATE');
995 END IF;
996 --
997 WHEN OTHERS then
998 wsh_util_core.default_handler('WSH_CONTAINER_GRP.Update_Container');
999 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1000 rollback to Update_Containers_SP_Grp;
1001 /*
1002
1003 WSH_UTIL_CORE.get_messages('Y', l_msg_summary, l_msg_details, x_msg_count);
1004 if x_msg_count > 1 then
1005 x_msg_data := l_msg_summary || l_msg_details;
1006 else
1007 x_msg_data := l_msg_summary;
1008 end if;
1009
1010 */
1011 FND_MSG_PUB.Count_And_Get
1012 ( p_count => x_msg_count
1013 , p_data => x_msg_data
1014 , p_encoded => FND_API.G_FALSE
1015 );
1016
1017
1018 --
1019 -- Debug Statements
1020 --
1021 IF l_debug_on THEN
1022 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1023 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1024 END IF;
1025 --
1026 END Update_Container;
1027
1028
1029 ------------------------------------------------------------------------------
1030 -- Procedure: Auto_Pack
1031 --
1032 -- Parameters: 1) entity_tab - table of ids of either lines or containers or
1033 -- deliveries that need to be autopacked
1034 -- 2) entity_type - type of entity id contained in the entity_tab
1035 -- that needs to be autopacked ('L' - lines,
1036 -- 'C' - containers OR 'D' - deliveries)
1037 -- 3) group_id_tab - table of ids (numbers that determine
1038 -- the grouping of lines for packing into containers)
1039 -- 4) container_instance_tab - table of delivery detail ids of
1040 -- containers that are created during the autopacking
1041 -- 5) pack cont flag - a 'Y' or 'N' value to determine whether to
1042 -- to autopack the detail containers that are created into
1043 -- parent containers.
1044 -- 6) other standard parameters
1045 --
1046 -- Description: This procedure takes in a table of ids of either delivery lines
1047 -- or container or deliveries and autopacks the lines/containers/deliveries
1048 -- into detail containers. The grouping id table is used only if the input
1049 -- table of entities are lines or containers only. The packing of lines and
1050 -- containers into parent containers is determined by the grouping id for each
1051 -- line/container. If the grouping id table is not input, the API determines
1052 -- the grouping ids for the lines/containers based on the grouping attributes
1053 -- of the lines/containers. The lines/containers are then autopacked into
1054 -- detail containers and the detail containers are packed into parent/master
1055 -- containers based on whether the pack cont flag is set to 'Y' or 'N'. The
1056 -- API returns a table of container instance ids created during the autopacking
1057 -- operation. If the detail containers are packed into parent containers, the
1058 -- output table of ids will contain both the detail and parent containers'
1059 -- delivery detail ids.
1060 ------------------------------------------------------------------------------
1061
1062 PROCEDURE Auto_Pack (
1063 -- Standard parameters
1064 p_api_version IN NUMBER,
1065 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
1066 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
1067 p_validation_level IN NUMBER DEFAULT FND_API.G_VALID_LEVEL_FULL,
1068 x_return_status OUT NOCOPY VARCHAR2,
1069 x_msg_count OUT NOCOPY NUMBER,
1070 x_msg_data OUT NOCOPY VARCHAR2,
1071
1072 -- program specific parameters
1073 p_entity_tab IN WSH_UTIL_CORE.ID_TAB_TYPE,
1074 p_entity_type IN VARCHAR2,
1075 p_group_id_tab IN WSH_UTIL_CORE.ID_TAB_TYPE,
1076 p_pack_cont_flag IN VARCHAR2,
1077
1078 -- program specific out parameters
1079 x_cont_inst_tab OUT NOCOPY WSH_UTIL_CORE.ID_TAB_TYPE
1080
1081 ) IS
1082
1083 -- Standard call to check for call compatibility
1084 l_api_version CONSTANT NUMBER := 1.0;
1085 l_api_name CONSTANT VARCHAR2(30):= 'Update_Containers';
1086
1087 l_return_status VARCHAR2(30) := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1088
1089 l_det_tab WSH_UTIL_CORE.ID_TAB_TYPE;
1090 l_cont_tab WSH_UTIL_CORE.ID_TAB_TYPE;
1091 l_del_tab WSH_UTIL_CORE.ID_TAB_TYPE;
1092
1093 l_delivery_detail_id NUMBER;
1094 l_delivery_id NUMBER;
1095 l_del_status VARCHAR2(10);
1096 l_delivery_name VARCHAR2(30);
1097
1098 l_cont_name VARCHAR2(30) := NULL;
1099 l_cont_flag VARCHAR2(1);
1100 l_cont_instance_id NUMBER;
1101
1102 l_old_cont_name VARCHAR2(30);
1103 l_old_cont_instance_id NUMBER;
1104
1105 l_det_cnt NUMBER := 0;
1106 l_cont_cnt NUMBER := 0;
1107 l_del_cnt NUMBER := 0;
1108
1109 l_msg_summary VARCHAR2(2000) := NULL;
1110 l_msg_details VARCHAR2(4000) := NULL;
1111 l_ret_status VARCHAR2(30) := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1112
1113
1114
1115 WSH_INVALID_CONT EXCEPTION;
1116 WSH_INVALID_DETAIL EXCEPTION;
1117 WSH_INVALID_DELIVERY EXCEPTION;
1118 WSH_INVALID_ENTITY_TYPE EXCEPTION;
1119 WSH_FAIL_AUTOPACK EXCEPTION;
1120
1121 CURSOR Check_Detail (v_detail_id NUMBER) IS
1122 SELECT delivery_detail_id, container_flag
1123 FROM WSH_DELIVERY_DETAILS
1124 WHERE delivery_detail_id = v_detail_id
1125 AND nvl(line_direction,'O') in ('O','IO'); -- J-IB-NPARIKH;
1126
1127 CURSOR Check_Delivery (v_del_id NUMBER) IS
1128 SELECT delivery_id, name, status_code
1129 FROM WSH_NEW_DELIVERIES
1130 WHERE delivery_id = v_del_id
1131 AND nvl(shipment_direction,'O') in ('O','IO'); -- J-IB-NPARIKH;;
1132
1133
1134 -- K LPN CONV. rv
1135 l_lpn_in_sync_comm_rec WSH_GLBL_VAR_STRCT_GRP.lpn_sync_comm_in_rec_type;
1136 l_lpn_out_sync_comm_rec WSH_GLBL_VAR_STRCT_GRP.lpn_sync_comm_out_rec_type;
1137 l_msg_count NUMBER;
1138 l_msg_data VARCHAR2(32767);
1139 -- K LPN CONV. rv
1140
1141 --
1142 l_debug_on BOOLEAN;
1143 --
1144 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'AUTO_PACK';
1145 --
1146 --Bugfix 4070732
1147 l_api_session_name CONSTANT VARCHAR2(150) := G_PKG_NAME ||'.' || l_module_name;
1148 l_reset_flags BOOLEAN;
1149 l_num_warnings NUMBER;
1150 l_num_errors NUMBER;
1151
1152 BEGIN
1153 --Bugfix 4070732
1154
1155 l_num_warnings := 0;
1156 l_num_errors := 0;
1157
1158 IF WSH_UTIL_CORE.G_START_OF_SESSION_API is null THEN
1159 WSH_UTIL_CORE.G_START_OF_SESSION_API := l_api_session_name;
1160 WSH_UTIL_CORE.G_CALL_FTE_LOAD_TENDER_API := FALSE;
1161 END IF;
1162
1163 -- Standard begin of API savepoint
1164 --
1165 -- Debug Statements
1166 --
1167 --
1168 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1169 --
1170 IF l_debug_on IS NULL
1171 THEN
1172 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1173 END IF;
1174 --
1175 IF l_debug_on THEN
1176 WSH_DEBUG_SV.push(l_module_name);
1177 --
1178 WSH_DEBUG_SV.log(l_module_name,'P_API_VERSION',P_API_VERSION);
1179 WSH_DEBUG_SV.log(l_module_name,'P_INIT_MSG_LIST',P_INIT_MSG_LIST);
1180 WSH_DEBUG_SV.log(l_module_name,'P_COMMIT',P_COMMIT);
1181 WSH_DEBUG_SV.log(l_module_name,'P_VALIDATION_LEVEL',P_VALIDATION_LEVEL);
1182 WSH_DEBUG_SV.log(l_module_name,'P_ENTITY_TYPE',P_ENTITY_TYPE);
1183 WSH_DEBUG_SV.log(l_module_name,'P_PACK_CONT_FLAG',P_PACK_CONT_FLAG);
1184 END IF;
1185 --
1186 SAVEPOINT Autopack_SP_Grp;
1187
1188 IF NOT FND_API.compatible_api_call (
1189 l_api_version,
1190 p_api_version,
1191 l_api_name,
1192 G_PKG_NAME) THEN
1193 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1194 END IF;
1195
1196 -- Check p_init_msg_list
1197 IF FND_API.to_boolean(p_init_msg_list) THEN
1198 FND_MSG_PUB.initialize;
1199 END IF;
1200
1201 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1202
1203 -- first decide which entity it is by checking entity type..
1204 -- based on entity type validate all the entity ids..
1205
1206 IF p_entity_type = 'L' OR p_entity_type = 'C' THEN
1207
1208 FOR i IN 1..p_entity_tab.count LOOP
1209
1210 IF p_entity_tab(i) IS NOT NULL THEN
1211
1212 OPEN Check_Detail(p_entity_tab(i));
1213
1214 FETCH Check_Detail INTO
1215 l_delivery_detail_id,
1216 l_cont_flag;
1217
1218 IF Check_Detail%NOTFOUND THEN
1219 CLOSE Check_Detail;
1220 l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1221 END IF;
1222
1223 IF Check_Detail%ISOPEN THEN
1224 CLOSE Check_Detail;
1225 END IF;
1226
1227 l_det_cnt := l_det_cnt + 1;
1228 l_det_tab(l_det_cnt) := p_entity_tab(i);
1229
1230 ELSE
1231 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
1232 l_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
1233 END IF;
1234 END IF;
1235 END LOOP;
1236
1237 IF l_det_tab.count > 0 THEN
1238
1239 --
1240 -- Debug Statements
1241 --
1242 IF l_debug_on THEN
1243 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DLVB_COMMON_ACTIONS.AUTO_PACK_LINES',WSH_DEBUG_SV.C_PROC_LEVEL);
1244 END IF;
1245 --
1246 WSH_DLVB_COMMON_ACTIONS.Auto_Pack_Lines (
1247 p_group_id_tab => p_group_id_tab,
1248 p_detail_tab => l_det_tab,
1249 p_pack_cont_flag => p_pack_cont_flag,
1250 p_group_api_flag => 'Y',
1251 x_cont_inst_tab => x_cont_inst_tab,
1252 x_return_status => l_ret_status);
1253
1254 IF l_ret_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
1255 IF l_ret_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
1256 -- Bug#4280365 Start
1257 If p_pack_cont_flag = 'Y' then
1258 fnd_message.set_name('WSH', 'WSH_AUTO_PACK_MASTER_MSG');
1259 -- Bug#4280365 End
1260 Else
1261 fnd_message.set_name('WSH', 'WSH_AUTOPACK_ERROR');
1262 End If;
1263 WSH_UTIL_CORE.ADD_MESSAGE(l_return_status);
1264 RAISE WSH_UTIL_CORE.G_EXC_WARNING;
1265 ELSE
1266 RAISE WSH_FAIL_AUTOPACK;
1267 END IF;
1268 ELSE
1269 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1270 IF l_debug_on THEN
1271 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TP_RELEASE.CALCULATE_CONT_DEL_TPDATES',WSH_DEBUG_SV.C_PROC_LEVEL);
1272 END IF;
1273 WSH_TP_RELEASE.calculate_cont_del_tpdates(
1274 p_entity => 'DLVB',
1275 p_entity_ids =>x_cont_inst_tab,
1276 x_return_status => x_return_status);
1277 END IF;
1278 ELSE
1279 RAISE WSH_INVALID_DETAIL;
1280 END IF;
1281
1282
1283
1284 ELSIF p_entity_type = 'D' THEN
1285
1286 FOR i IN 1..p_entity_tab.count LOOP
1287
1288 IF p_entity_tab(i) IS NOT NULL THEN
1289
1290 OPEN Check_Delivery(p_entity_tab(i));
1291
1292 FETCH Check_Delivery INTO
1293 l_delivery_id,
1294 l_delivery_name,
1295 l_del_status;
1296
1297 IF Check_Delivery%NOTFOUND THEN
1298 CLOSE Check_Delivery;
1299 l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1300 END IF;
1301
1302 IF Check_Delivery%ISOPEN THEN
1303 CLOSE Check_Delivery;
1304 END IF;
1305
1306 IF nvl(l_del_status,'OP') IN ('OP', 'SA') THEN
1307 l_del_cnt := l_del_cnt + 1;
1308 l_del_tab(l_del_cnt) := p_entity_tab(i);
1309 ELSE
1310 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
1311 l_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
1312 END IF;
1313 END IF;
1314
1315 ELSE
1316 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
1317 l_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
1318 END IF;
1319 END IF;
1320
1321 END LOOP;
1322
1323 IF l_del_tab.count > 0 THEN
1324
1325 --
1326 -- Debug Statements
1327 --
1328 IF l_debug_on THEN
1329 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_CONTAINER_ACTIONS.AUTO_PACK_DELIVERY',WSH_DEBUG_SV.C_PROC_LEVEL);
1330 END IF;
1331 --
1332 WSH_CONTAINER_ACTIONS.Auto_Pack_Delivery (
1333 l_del_tab,
1334 p_pack_cont_flag,
1335 x_cont_inst_tab,
1336 x_return_status);
1337
1338 IF x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
1339 IF x_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
1340 -- Bug#4280365 Start
1341 If p_pack_cont_flag = 'Y' then
1342 fnd_message.set_name('WSH', 'WSH_AUTO_PACK_MASTER_MSG');
1343 -- Bug#4280365 End
1344 Else
1345 fnd_message.set_name('WSH', 'WSH_AUTOPACK_ERROR');
1346 End If;
1347 WSH_UTIL_CORE.ADD_MESSAGE(l_return_status);
1348 RAISE WSH_UTIL_CORE.G_EXC_WARNING;
1349 ELSE
1350 RAISE WSH_FAIL_AUTOPACK;
1351 END IF;
1352 ELSE
1353 IF l_debug_on THEN
1354 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TP_RELEASE.CALCULATE_CONT_DEL_TPDATES',WSH_DEBUG_SV.C_PROC_LEVEL);
1355 END IF;
1356 WSH_TP_RELEASE.calculate_cont_del_tpdates(
1357 p_entity => 'DLVY',
1358 p_entity_ids =>l_del_tab,
1359 x_return_status => x_return_status);
1360
1361 END IF;
1362
1363 ELSE
1364 RAISE WSH_INVALID_DELIVERY;
1365 END IF;
1366
1367 ELSE
1368 RAISE WSH_INVALID_ENTITY_TYPE;
1369 END IF;
1370
1371 --
1372 -- K LPN CONV. rv
1373 --
1374 IF WSH_WMS_LPN_GRP.G_CALLBACK_REQUIRED = 'Y'
1375 THEN
1376 --{
1377 WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS
1378 (
1379 p_in_rec => l_lpn_in_sync_comm_rec,
1380 x_return_status => l_return_status,
1381 x_out_rec => l_lpn_out_sync_comm_rec
1382 );
1383 --
1384 --
1385 IF l_debug_on THEN
1386 WSH_DEBUG_SV.log(l_module_name,'Return status after calling WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS',l_return_status);
1387 END IF;
1388 --
1389 --
1390 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
1391 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
1392 RAISE WSH_UTIL_CORE.G_EXC_WARNING;
1393 ELSIF l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR THEN
1394 RAISE WSH_FAIL_AUTOPACK;
1395 ELSE
1396 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1397 END IF;
1398 END IF;
1399 --}
1400 END IF;
1401 --
1402 -- K LPN CONV. rv
1403 --
1404 --Bugfix 4070732 {
1405 IF FND_API.TO_BOOLEAN(p_commit) THEN
1406
1407 l_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1408
1409 -- dbms_output.put_line('commit');
1410 IF NOT(WSH_UTIL_CORE.G_CALL_FTE_LOAD_TENDER_API) THEN --{
1411 WSH_UTIL_CORE.Process_stops_for_load_tender(
1412 p_reset_flags => FALSE,
1413 x_return_status => l_return_status);
1414
1415 IF l_debug_on THEN
1416 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
1417 END IF;
1418 END IF; --}
1419 IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_SUCCESS)
1420 OR (l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING) THEN --{
1421
1422 COMMIT;
1423
1424 END IF; --}
1425
1426 wsh_util_core.api_post_call(p_return_status =>l_return_status,
1427 x_num_warnings =>l_num_warnings,
1428 x_num_errors =>l_num_errors);
1429 END IF;
1430 --Bugfix 4070732 }
1431
1432 /*
1433
1434 WSH_UTIL_CORE.get_messages('Y', l_msg_summary, l_msg_details, x_msg_count);
1435 if x_msg_count > 1 then
1436 x_msg_data := l_msg_summary || l_msg_details;
1437 else
1438 x_msg_data := l_msg_summary;
1439 end if;
1440 */
1441 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1442
1443 --Bugfix 4070732 {
1444 IF upper(WSH_UTIL_CORE.G_START_OF_SESSION_API) = upper(l_api_session_name) THEN --{
1445 IF NOT(WSH_UTIL_CORE.G_CALL_FTE_LOAD_TENDER_API) THEN
1446
1447 IF FND_API.TO_BOOLEAN(p_commit) THEN
1448
1449 IF l_debug_on THEN
1450 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.reset_stops_for_load_tender',WSH_DEBUG_SV.C_PROC_LEVEL);
1451 END IF;
1452 WSH_UTIL_CORE.reset_stops_for_load_tender(
1453 p_reset_flags => TRUE,
1454 x_return_status => l_return_status);
1455 ELSE
1456
1457 IF l_debug_on THEN
1458 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.Process_stops_for_load_tender',WSH_DEBUG_SV.C_PROC_LEVEL);
1459 END IF;
1460 WSH_UTIL_CORE.Process_stops_for_load_tender(
1461 p_reset_flags => TRUE,
1462 x_return_status => l_return_status);
1463 END IF;
1464
1465 IF l_debug_on THEN
1466 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
1467 END IF;
1468
1469 IF l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR,
1470 WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
1471 IF NOT(FND_API.TO_BOOLEAN(p_commit)) THEN
1472 rollback to Autopack_SP_Grp;
1473 end if;
1474 END IF;
1475
1476 --x_return_status is set to success before the call to fte load tender
1477 -- setting the return status to l_return_status
1478 x_return_status := l_return_status;
1479
1480 END IF;
1481 END IF; --}
1482
1483 --}
1484 --End of bug 4070732
1485
1486 FND_MSG_PUB.Count_And_Get
1487 ( p_count => x_msg_count
1488 , p_data => x_msg_data
1489 , p_encoded => FND_API.G_FALSE
1490 );
1491
1492
1493 --
1494 -- Debug Statements
1495 --
1496 IF l_debug_on THEN
1497 WSH_DEBUG_SV.pop(l_module_name);
1498 END IF;
1499 --
1500 EXCEPTION
1501
1502 WHEN WSH_INVALID_DETAIL then
1503 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1504 rollback to Autopack_SP_Grp;
1505 fnd_message.set_name('WSH', 'WSH_DET_INVALID_DETAIL');
1506 WSH_UTIL_CORE.ADD_MESSAGE(x_return_status);
1507 /*
1508
1509 WSH_UTIL_CORE.get_messages('Y', l_msg_summary, l_msg_details, x_msg_count);
1510 if x_msg_count > 1 then
1511 x_msg_data := l_msg_summary || l_msg_details;
1512 else
1513 x_msg_data := l_msg_summary;
1514 end if;
1515 */
1516 --Bugfix 4070732 {
1517 IF upper(WSH_UTIL_CORE.G_START_OF_SESSION_API) = upper(l_api_session_name) THEN
1518 IF NOT(WSH_UTIL_CORE.G_CALL_FTE_LOAD_TENDER_API) THEN
1519 IF l_debug_on THEN
1520 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.Reset_stops_for_load_tender',WSH_DEBUG_SV.C_PROC_LEVEL);
1521 END IF;
1522
1523 WSH_UTIL_CORE.Reset_stops_for_load_tender(p_reset_flags => TRUE,
1524 x_return_status => l_return_status);
1525
1526
1527 IF l_debug_on THEN
1528 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
1529 END IF;
1530
1531 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR THEN
1532 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1533 END IF;
1534 END IF;
1535 END IF;
1536 --}
1537 --End of bug 4070732
1538
1539
1540 FND_MSG_PUB.Count_And_Get
1541 ( p_count => x_msg_count
1542 , p_data => x_msg_data
1543 , p_encoded => FND_API.G_FALSE
1544 );
1545
1546 --
1547 -- Debug Statements
1548 --
1549 IF l_debug_on THEN
1550 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_INVALID_DETAIL exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1551 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_INVALID_DETAIL');
1552 END IF;
1553 --
1554
1555 WHEN FND_API.G_EXC_ERROR then
1556 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1557 rollback to Autopack_SP_Grp;
1558
1559 IF upper(WSH_UTIL_CORE.G_START_OF_SESSION_API) = upper(l_api_session_name) THEN
1560 IF NOT(WSH_UTIL_CORE.G_CALL_FTE_LOAD_TENDER_API) THEN
1561 IF l_debug_on THEN
1562 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.Reset_stops_for_load_tender',WSH_DEBUG_SV.C_PROC_LEVEL);
1563 END IF;
1564
1565 WSH_UTIL_CORE.Reset_stops_for_load_tender(p_reset_flags => TRUE,
1566 x_return_status => l_return_status);
1567
1568
1569 IF l_debug_on THEN
1570 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
1571 END IF;
1572
1573 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR THEN
1574 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1575 END IF;
1576 END IF;
1577 END IF;
1578 --}
1579 --End of bug 4070732
1580
1581 FND_MSG_PUB.Count_And_Get
1582 ( p_count => x_msg_count
1583 , p_data => x_msg_data
1584 , p_encoded => FND_API.G_FALSE
1585 );
1586 --Bugfix 4070732 {
1587
1588 --
1589 -- Debug Statements
1590 --
1591 IF l_debug_on THEN
1592 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_INVALID_DETAIL exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1593 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:G_EXC_ERROR');
1594 END IF;
1595 --
1596 WHEN WSH_INVALID_CONT then
1597 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1598 rollback to Autopack_SP_Grp;
1599 fnd_message.set_name('WSH', 'WSH_CONT_INVALID_NAME');
1600 WSH_UTIL_CORE.ADD_MESSAGE(x_return_status);
1601 /*
1602
1603 WSH_UTIL_CORE.get_messages('Y', l_msg_summary, l_msg_details, x_msg_count);
1604 if x_msg_count > 1 then
1605 x_msg_data := l_msg_summary || l_msg_details;
1606 else
1607 x_msg_data := l_msg_summary;
1608 end if;
1609 */
1610 --Bugfix 4070732 {
1611 IF upper(WSH_UTIL_CORE.G_START_OF_SESSION_API) = upper(l_api_session_name) THEN
1612 IF NOT(WSH_UTIL_CORE.G_CALL_FTE_LOAD_TENDER_API) THEN
1613 IF l_debug_on THEN
1614 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.Reset_stops_for_load_tender',WSH_DEBUG_SV.C_PROC_LEVEL);
1615 END IF;
1616
1617 WSH_UTIL_CORE.Reset_stops_for_load_tender(p_reset_flags => TRUE,
1618 x_return_status => l_return_status);
1619
1620
1621 IF l_debug_on THEN
1622 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
1623 END IF;
1624 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR THEN
1625 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1626 END IF;
1627
1628 END IF;
1629 END IF;
1630 --}
1631 --End of bug 4070732
1632
1633
1634 FND_MSG_PUB.Count_And_Get
1635 ( p_count => x_msg_count
1636 , p_data => x_msg_data
1637 , p_encoded => FND_API.G_FALSE
1638 );
1639 --
1640 -- Debug Statements
1641 --
1642 IF l_debug_on THEN
1643 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_INVALID_CONT exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1644 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_INVALID_CONT');
1645 END IF;
1646 --
1647 WHEN WSH_INVALID_DELIVERY then
1648 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1649 rollback to Autopack_SP_Grp;
1650 fnd_message.set_name('WSH', 'WSH_DET_INVALID_DEL');
1651 WSH_UTIL_CORE.ADD_MESSAGE(x_return_status);
1652 /*
1653
1654 WSH_UTIL_CORE.get_messages('Y', l_msg_summary, l_msg_details, x_msg_count);
1655 if x_msg_count > 1 then
1656 x_msg_data := l_msg_summary || l_msg_details;
1657 else
1658 x_msg_data := l_msg_summary;
1659 end if;
1660 */
1661
1662 --Bugfix 4070732 {
1663 IF upper(WSH_UTIL_CORE.G_START_OF_SESSION_API) = upper(l_api_session_name) THEN
1664 IF NOT(WSH_UTIL_CORE.G_CALL_FTE_LOAD_TENDER_API) THEN
1665 IF l_debug_on THEN
1666 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.Reset_stops_for_load_tender',WSH_DEBUG_SV.C_PROC_LEVEL);
1667 END IF;
1668
1669 WSH_UTIL_CORE.Reset_stops_for_load_tender(p_reset_flags => TRUE,
1670 x_return_status => l_return_status);
1671
1672
1673 IF l_debug_on THEN
1674 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
1675 END IF;
1676 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR THEN
1677 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1678 END IF;
1679
1680 END IF;
1681 END IF;
1682 --}
1683 --End of bug 4070732
1684
1685
1686 FND_MSG_PUB.Count_And_Get
1687 ( p_count => x_msg_count
1688 , p_data => x_msg_data
1689 , p_encoded => FND_API.G_FALSE
1690 );
1691 --
1692 -- Debug Statements
1693 --
1694 IF l_debug_on THEN
1695 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_INVALID_DELIVERY exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1696 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_INVALID_DELIVERY');
1697 END IF;
1698 --
1699 WHEN WSH_UTIL_CORE.G_EXC_WARNING then
1700 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
1701 --
1702 -- K LPN CONV. rv
1703 --
1704 IF WSH_WMS_LPN_GRP.G_CALLBACK_REQUIRED = 'Y'
1705 THEN
1706 --{
1707 WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS
1708 (
1709 p_in_rec => l_lpn_in_sync_comm_rec,
1710 x_return_status => l_return_status,
1711 x_out_rec => l_lpn_out_sync_comm_rec
1712 );
1713 --
1714 --
1715 IF l_debug_on THEN
1716 WSH_DEBUG_SV.log(l_module_name,'Return status after calling WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS',l_return_status);
1717 END IF;
1718 --
1719 --
1720 IF (l_return_status IN (WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR, WSH_UTIL_CORE.G_RET_STS_ERROR)) THEN
1721 x_return_status := l_return_status;
1722 END IF;
1723 --
1724 --}
1725 END IF;
1726 --
1727 -- K LPN CONV. rv
1728 --
1729
1730 --Bugfix 4070732 {
1731 IF upper(WSH_UTIL_CORE.G_START_OF_SESSION_API) = upper(l_api_session_name) THEN
1732 IF NOT(WSH_UTIL_CORE.G_CALL_FTE_LOAD_TENDER_API) THEN
1733 IF l_debug_on THEN
1734 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.Process_stops_for_load_tender',WSH_DEBUG_SV.C_PROC_LEVEL);
1735 END IF;
1736
1737 WSH_UTIL_CORE.Process_stops_for_load_tender(p_reset_flags => TRUE,
1738 x_return_status => l_return_status);
1739
1740 IF l_debug_on THEN
1741 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
1742 END IF;
1743
1744 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR
1745 OR l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR THEN
1746 rollback to Autopack_SP_Grp;
1747 X_return_status := l_return_status;
1748 END IF;
1749
1750 END IF;
1751 END IF;
1752 --}
1753 -- End of bug 4070732
1754
1755
1756 FND_MSG_PUB.Count_And_Get
1757 ( p_count => x_msg_count
1758 , p_data => x_msg_data
1759 , p_encoded => FND_API.G_FALSE
1760 );
1761 IF l_debug_on THEN
1762 WSH_DEBUG_SV.logmsg(l_module_name,'G_RET_STS_WARNING exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1763 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:G_RET_STS_WARNING');
1764 END IF;
1765 --
1766 WHEN WSH_FAIL_AUTOPACK then
1767 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1768 rollback to Autopack_SP_Grp;
1769 fnd_message.set_name('WSH', 'WSH_AUTOPACK_ERROR');
1770 WSH_UTIL_CORE.ADD_MESSAGE(x_return_status);
1771 --Bugfix 4070732 {
1772 IF upper(WSH_UTIL_CORE.G_START_OF_SESSION_API) = upper(l_api_session_name) THEN
1773 IF NOT(WSH_UTIL_CORE.G_CALL_FTE_LOAD_TENDER_API) THEN
1774 IF l_debug_on THEN
1775 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.Reset_stops_for_load_tender',WSH_DEBUG_SV.C_PROC_LEVEL);
1776 END IF;
1777
1778 WSH_UTIL_CORE.Reset_stops_for_load_tender(p_reset_flags => TRUE,
1779 x_return_status => l_return_status);
1780
1781
1782 IF l_debug_on THEN
1783 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
1784 END IF;
1785
1786 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR THEN
1787 X_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1788 END IF;
1789 END IF;
1790 END IF;
1791 --}
1792 --End of bug 4070732
1793
1794 FND_MSG_PUB.Count_And_Get
1795 ( p_count => x_msg_count
1796 , p_data => x_msg_data
1797 , p_encoded => FND_API.G_FALSE
1798 );
1799 IF l_debug_on THEN
1800 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_FAIL_AUTOPACK exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1801 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_FAIL_AUTOPACK');
1802 END IF;
1803 --
1804 WHEN WSH_INVALID_ENTITY_TYPE then
1805 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1806 rollback to Autopack_SP_Grp;
1807 fnd_message.set_name('WSH', 'WSH_PUB_CONT_TYPE_ERR');
1808 WSH_UTIL_CORE.ADD_MESSAGE(x_return_status);
1809 /*
1810
1811 WSH_UTIL_CORE.get_messages('Y', l_msg_summary, l_msg_details, x_msg_count);
1812 if x_msg_count > 1 then
1813 x_msg_data := l_msg_summary || l_msg_details;
1814 else
1815 x_msg_data := l_msg_summary;
1816 end if;
1817 */
1818 --Bugfix 4070732 {
1819 IF upper(WSH_UTIL_CORE.G_START_OF_SESSION_API) = upper(l_api_session_name) THEN
1820 IF NOT(WSH_UTIL_CORE.G_CALL_FTE_LOAD_TENDER_API) THEN
1821 IF l_debug_on THEN
1822 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.Reset_stops_for_load_tender',WSH_DEBUG_SV.C_PROC_LEVEL);
1823 END IF;
1824
1825 WSH_UTIL_CORE.Reset_stops_for_load_tender(p_reset_flags => TRUE,
1826 x_return_status => l_return_status);
1827
1828
1829 IF l_debug_on THEN
1830 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
1831 END IF;
1832 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR THEN
1833 X_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1834 END IF;
1835
1836 END IF;
1837 END IF;
1838 --}
1839 --End of bug 4070732
1840
1841
1842 FND_MSG_PUB.Count_And_Get
1843 ( p_count => x_msg_count
1844 , p_data => x_msg_data
1845 , p_encoded => FND_API.G_FALSE
1846 );
1847
1848 --
1849 -- Debug Statements
1850 --
1851 IF l_debug_on THEN
1852 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_INVALID_ENTITY_TYPE exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1853 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_INVALID_ENTITY_TYPE');
1854 END IF;
1855 --
1856 WHEN OTHERS then
1857 wsh_util_core.default_handler('WSH_CONTAINER_PUB.Auto_Pack');
1858 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1859 rollback to Autopack_SP_Grp;
1860
1861 /*
1862
1863 WSH_UTIL_CORE.get_messages('Y', l_msg_summary, l_msg_details, x_msg_count);
1864 if x_msg_count > 1 then
1865 x_msg_data := l_msg_summary || l_msg_details;
1866 else
1867 x_msg_data := l_msg_summary;
1868 end if;
1869
1870 */
1871 --Bugfix 4070732 {
1872 IF upper(WSH_UTIL_CORE.G_START_OF_SESSION_API) = upper(l_api_session_name) THEN
1873 IF NOT(WSH_UTIL_CORE.G_CALL_FTE_LOAD_TENDER_API) THEN
1874 IF l_debug_on THEN
1875 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.Reset_stops_for_load_tender',WSH_DEBUG_SV.C_PROC_LEVEL);
1876 END IF;
1877
1878 WSH_UTIL_CORE.Reset_stops_for_load_tender(p_reset_flags => TRUE,
1879 x_return_status => l_return_status);
1880
1881
1882 IF l_debug_on THEN
1883 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
1884 END IF;
1885
1886 END IF;
1887 END IF;
1888 --}
1889 --End of bug 4070732
1890
1891
1892 FND_MSG_PUB.Count_And_Get
1893 ( p_count => x_msg_count
1894 , p_data => x_msg_data
1895 , p_encoded => FND_API.G_FALSE
1896 );
1897
1898 --
1899 -- Debug Statements
1900 --
1901 IF l_debug_on THEN
1902 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1903 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1904 END IF;
1905 --
1906 END Auto_Pack;
1907
1908
1909 ------------------------------------------------------------------------------
1910 -- Procedure: Container_Actions
1911 --
1912 -- Parameters: 1) detail_tab - input table of delivery detail ids
1913 -- 2) container_instance_id - delivery detail id of parent
1914 -- container that is being packed.
1915 -- 3) container_name - container name if id is not known
1916 -- 4) container_flag - 'Y' or 'N' depending on whether to unpack
1917 -- or not. ('Y' is unpack)
1918 -- 5) delivery_flag - 'Y' or 'N' if container needs to be
1919 -- unassigned from delivery. ('Y' if unassign from del)
1920 -- 6) delivery_id - delivery id to assign container to.
1921 -- 7) delivery_name - name of delivery that container is being
1922 -- assigned to.
1923 -- 8) action_code - action code 'Pack', 'Assign', 'Unpack' or
1924 -- 'Unassign' to specify what action to perform.
1925 -- 9) other standard parameters
1926 --
1927 -- Description: This procedure takes in a table of delivery detail ids and
1928 -- name and/or delivery detail id of container to pack. If the action code is
1929 -- is assign then delivery id and delivery name must be specified. The API
1930 -- determines what action to perform based on the action code and then calls
1931 -- appropriate private pack/assign/unpack/unassign API.
1932 -- The input table of ids could be lines or containers. The delivery lines and
1933 -- containers are separated from the input table and validated before the
1934 -- appropriate private APIs are called
1935 -- THIS PROCEDURE IS ONLY TO BE CALLED FROM
1936 -- WSH_DELIVERY_DETAIL_GRP.Delivery_Detail_Action
1937
1938 ------------------------------------------------------------------------------
1939
1940
1941
1942
1943 PROCEDURE Container_Actions (
1944 -- Standard parameters
1945 p_api_version IN NUMBER,
1946 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
1947 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
1948 p_validation_level IN NUMBER DEFAULT FND_API.G_VALID_LEVEL_FULL,
1949 x_return_status OUT NOCOPY VARCHAR2,
1950 x_msg_count OUT NOCOPY NUMBER,
1951 x_msg_data OUT NOCOPY VARCHAR2,
1952 -- program specific parameters
1953 p_detail_tab IN WSH_UTIL_CORE.ID_TAB_TYPE,
1954 p_container_name IN VARCHAR2 DEFAULT NULL,
1955 p_cont_instance_id IN NUMBER DEFAULT NULL,
1956 p_container_flag IN VARCHAR2 DEFAULT 'N',
1957 p_delivery_flag IN VARCHAR2 DEFAULT 'N',
1958 p_delivery_id IN NUMBER DEFAULT NULL,
1959 p_delivery_name IN VARCHAR2 DEFAULT NULL,
1960 p_action_code IN VARCHAR2 ,
1961 p_caller IN VARCHAR2 DEFAULT 'WMS'
1962
1963 ) IS
1964 -- Standard call to check for call compatibility
1965 l_api_version CONSTANT NUMBER := 1.0;
1966 l_api_name CONSTANT VARCHAR2(30):= 'Update_Containers';
1967
1968 l_detail_tab WSH_UTIL_CORE.ID_TAB_TYPE;
1969 l_cont_tab WSH_UTIL_CORE.ID_TAB_TYPE;
1970
1971 l_delivery_detail_id NUMBER;
1972 l_delivery_id NUMBER;
1973 l_del_sts VARCHAR2(10);
1974 l_delivery_name VARCHAR2(30);
1975
1976 l_cont_name VARCHAR2(30) := NULL;
1977 l_cont_flag VARCHAR2(1);
1978 l_cont_instance_id NUMBER;
1979
1980 l_old_cont_name VARCHAR2(30);
1981 l_old_cont_instance_id NUMBER;
1982
1983 l_det_cnt NUMBER := 0;
1984 l_cont_cnt NUMBER := 0;
1985 l_del_cnt NUMBER := 0;
1986
1987 i NUMBER;
1988
1989 l_msg_summary VARCHAR2(32000) := NULL;
1990 l_msg_details VARCHAR2(32000) := NULL;
1991
1992 l_pack_status VARCHAR2(30);
1993 l_group_api_flag VARCHAR2(1);
1994 l_verify_status_lvl NUMBER ;
1995 l_verify_org NUMBER ;
1996 l_verify_dlvy NUMBER ;
1997 l_wms_enabled_flag VARCHAR2(10);
1998 l_dlvy_status_code wsh_new_deliveries.status_code%TYPE;
1999 l_cont_org_id NUMBER;
2000 l_cont_released_status wsh_delivery_details.released_status%TYPE;
2001 l_return_status VARCHAR2(1) := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2002 l_num_warning NUMBER := 0;
2003 l_num_errors NUMBER := 0;
2004 l_next NUMBER ;
2005
2006 WSH_INVALID_CONT EXCEPTION;
2007 WSH_INVALID_DETAIL EXCEPTION;
2008 WSH_INVALID_DELIVERY EXCEPTION;
2009 WSH_INVALID_ACTION EXCEPTION;
2010 WSH_FAIL_CONT_ACTION EXCEPTION;
2011 WSH_INVALID_ENTITY_TYPE EXCEPTION;
2012
2013 --Bug 4329611. Perf Fix .
2014 CURSOR Get_Detail_Id (v_cont_name VARCHAR2) IS
2015 SELECT delivery_detail_id, container_flag
2016 FROM WSH_DELIVERY_DETAILS
2017 WHERE container_name = v_cont_name
2018 --LPN reuse project
2019 AND released_status = 'X'
2020 AND container_flag = 'Y';
2021
2022 CURSOR Check_Detail (v_detail_id NUMBER) IS
2023 SELECT wdd.delivery_detail_id, wdd.container_flag,wdd.released_status,
2024 wdd.organization_id ,wda.delivery_id
2025 FROM WSH_DELIVERY_DETAILS wdd, wsh_delivery_assignments_v wda
2026 WHERE wdd.delivery_detail_id = v_detail_id
2027 AND wdd.delivery_detail_id = wda.delivery_detail_id
2028 AND nvl(wdd.line_direction,'O') in ('O','IO'); -- J-IB-NPARIKH
2029
2030 CURSOR Check_Delivery_by_id (v_del_id NUMBER) IS
2031 SELECT delivery_id, name, status_code
2032 FROM WSH_NEW_DELIVERIES
2033 WHERE delivery_id = v_del_id;
2034
2035 CURSOR Check_Delivery_by_Name ( v_del_name VARCHAR2) IS
2036 SELECT delivery_id, name, status_code
2037 FROM WSH_NEW_DELIVERIES
2038 WHERE name = v_del_name;
2039
2040 CURSOR c_dlvy(l_delivery_id number) IS
2041 SELECT status_code
2042 FROM wsh_new_deliveries
2043 WHERE delivery_id = l_delivery_id;
2044
2045 -- K LPN CONV. rv
2046 l_lpn_in_sync_comm_rec WSH_GLBL_VAR_STRCT_GRP.lpn_sync_comm_in_rec_type;
2047 l_lpn_out_sync_comm_rec WSH_GLBL_VAR_STRCT_GRP.lpn_sync_comm_out_rec_type;
2048 l_msg_count NUMBER;
2049 l_msg_data VARCHAR2(32767);
2050 -- K LPN CONV. rv
2051
2052 l_debug_on BOOLEAN;
2053 --
2054 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'CONTAINER_ACTIONS';
2055 --
2056 --Bugfix 4070732
2057 l_api_session_name CONSTANT VARCHAR2(150) := G_PKG_NAME ||'.' || l_module_name;
2058 l_reset_flags BOOLEAN;
2059
2060 BEGIN
2061 --
2062 --Bugfix 4070732
2063 IF WSH_UTIL_CORE.G_START_OF_SESSION_API is null THEN
2064 WSH_UTIL_CORE.G_START_OF_SESSION_API := l_api_session_name;
2065 WSH_UTIL_CORE.G_CALL_FTE_LOAD_TENDER_API := FALSE;
2066 END IF;
2067 --
2068 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2069 --
2070 IF l_debug_on IS NULL
2071 THEN
2072 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2073 END IF;
2074 --
2075 SAVEPOINT Container_Action_SP_Grp;
2076 --
2077 IF l_debug_on THEN
2078 WSH_DEBUG_SV.push(l_module_name);
2079 --
2080 WSH_DEBUG_SV.log(l_module_name,'P_API_VERSION',P_API_VERSION);
2081 WSH_DEBUG_SV.log(l_module_name,'P_INIT_MSG_LIST',P_INIT_MSG_LIST);
2082 WSH_DEBUG_SV.log(l_module_name,'P_COMMIT',P_COMMIT);
2083 WSH_DEBUG_SV.log(l_module_name,'P_VALIDATION_LEVEL',P_VALIDATION_LEVEL);
2084 WSH_DEBUG_SV.log(l_module_name,'P_CONTAINER_NAME',P_CONTAINER_NAME);
2085 WSH_DEBUG_SV.log(l_module_name,'P_CONT_INSTANCE_ID',P_CONT_INSTANCE_ID);
2086 WSH_DEBUG_SV.log(l_module_name,'P_CONTAINER_FLAG',P_CONTAINER_FLAG);
2087 WSH_DEBUG_SV.log(l_module_name,'P_DELIVERY_FLAG',P_DELIVERY_FLAG);
2088 WSH_DEBUG_SV.log(l_module_name,'P_DELIVERY_ID',P_DELIVERY_ID);
2089 WSH_DEBUG_SV.log(l_module_name,'P_DELIVERY_NAME',P_DELIVERY_NAME);
2090 WSH_DEBUG_SV.log(l_module_name,'P_ACTION_CODE',P_ACTION_CODE);
2091 WSH_DEBUG_SV.log(l_module_name,'p_caller',p_caller);
2092 END IF;
2093 --
2094 -- Check p_init_msg_list
2095 IF FND_API.to_boolean(p_init_msg_list) THEN
2096 FND_MSG_PUB.initialize;
2097 END IF;
2098
2099 IF NOT FND_API.compatible_api_call (
2100 l_api_version,
2101 p_api_version,
2102 l_api_name,
2103 G_PKG_NAME) THEN
2104 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2105
2106 END IF;
2107
2108 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2109
2110 IF p_caller LIKE 'WMS%' THEN
2111 l_group_api_flag := 'Y';
2112 END IF;
2113
2114
2115
2116 IF nvl(p_detail_tab.COUNT,0) <= 0 THEN
2117 RAISE WSH_INVALID_DETAIL;
2118 END IF;
2119
2120 IF p_validation_level <> C_DELIVERY_DETAIL_CALL THEN
2121 WSH_ACTIONS_LEVELS.set_validation_level (
2122 p_entity => 'DLVB',
2123 p_caller => p_caller,
2124 p_phase => 1,
2125 p_action =>p_action_code ,
2126 x_return_status => l_return_status);
2127
2128 wsh_util_core.api_post_call(p_return_status =>l_return_status,
2129 x_num_warnings =>l_num_warning,
2130 x_num_errors =>l_num_errors);
2131 END IF;
2132
2133 l_verify_status_lvl := WSH_ACTIONS_LEVELS.g_validation_level_tab(WSH_ACTIONS_LEVELS.C_CONTAINER_STATUS_LVL);
2134 l_verify_org := WSH_ACTIONS_LEVELS.g_validation_level_tab(WSH_ACTIONS_LEVELS.C_CONTAINER_ORG_LVL);
2135 l_verify_dlvy := WSH_ACTIONS_LEVELS.g_validation_level_tab(WSH_ACTIONS_LEVELS.C_CONT_DLVY_LVL);
2136
2137 IF l_debug_on THEN
2138 WSH_DEBUG_SV.log(l_module_name,'l_verify_status_lvl'
2139 ,l_verify_status_lvl);
2140 WSH_DEBUG_SV.log(l_module_name,'l_verify_org',l_verify_org);
2141 WSH_DEBUG_SV.log(l_module_name,'l_verify_dlvy',l_verify_dlvy);
2142 END IF;
2143
2144 l_next := p_detail_tab.first;
2145 WHILE l_next IS NOT NULL LOOP
2146
2147 OPEN Check_Detail (p_detail_tab(l_next));
2148 Fetch Check_Detail INTO
2149 l_delivery_detail_id,
2150 l_cont_flag,
2151 l_cont_released_status,
2152 l_cont_org_id,
2153 l_delivery_id;
2154 IF Check_Detail%NOTFOUND THEN
2155 CLOSE Check_Detail;
2156 FND_MESSAGE.SET_NAME('WSH','WSH_DET_INVALID_DETAIL');
2157 l_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
2158 WSH_UTIL_CORE.Add_Message(l_return_status,l_module_name);
2159 ELSE
2160 l_det_cnt := l_det_cnt + 1;
2161 l_detail_tab(l_det_cnt) := l_delivery_detail_id;
2162
2163 END IF;
2164
2165 IF Check_Detail%ISOPEN THEN
2166 CLOSE Check_Detail;
2167 END IF;
2168
2169 l_next := p_detail_tab.NEXT(l_next);
2170
2171 END LOOP;
2172
2173
2174 IF UPPER(p_action_code) = 'ASSIGN' THEN
2175 IF p_delivery_id IS NULL THEN --{
2176 IF p_delivery_name IS NULL THEN
2177 RAISE WSH_INVALID_DELIVERY;
2178 END IF;
2179 OPEN Check_Delivery_by_Name ( p_delivery_name);
2180 FETCH Check_Delivery_by_Name INTO
2181 l_delivery_id,
2182 l_delivery_name,
2183 l_del_sts;
2184
2185 IF Check_Delivery_by_Name%NOTFOUND OR SQL%ROWCOUNT > 1 OR (nvl(l_del_sts,'OP') NOT IN ('OP', 'SA')) THEN
2186 CLOSE Check_Delivery_by_Name;
2187 RAISE WSH_INVALID_DELIVERY;
2188 END IF;
2189
2190 IF Check_Delivery_by_Name%ISOPEN THEN
2191 CLOSE Check_Delivery_by_Name;
2192 END IF;
2193 ELSE --}{
2194 OPEN Check_Delivery_by_id (p_delivery_id);
2195
2196 Fetch Check_Delivery_by_id INTO
2197 l_delivery_id,
2198 l_delivery_name,
2199 l_del_sts;
2200
2201 IF Check_Delivery_by_id%NOTFOUND OR SQL%ROWCOUNT > 1 OR (nvl(l_del_sts,'OP') NOT IN ('OP', 'SA') )THEN
2202 CLOSE Check_Delivery_by_id;
2203 RAISE WSH_INVALID_DELIVERY;
2204 END IF;
2205 -- close the cursor
2206 IF Check_Delivery_by_id%ISOPEN THEN
2207 CLOSE Check_Delivery_by_id;
2208 END IF;
2209 END IF; --}
2210
2211 END IF;
2212 IF UPPER(p_action_code) in ('PACK','UNPACK') THEN
2213 IF l_verify_org=1 THEN
2214 l_wms_enabled_flag := WSH_UTIL_VALIDATE.check_wms_org(l_cont_org_id);
2215 IF l_debug_on THEN
2216 WSH_DEBUG_SV.log(l_module_name,'l_wms_enabled_flag'
2217 ,l_wms_enabled_flag);
2218 END IF;
2219
2220 IF NVL(l_wms_enabled_flag,'N') = 'Y' THEN
2221 RAISE WSH_INVALID_CONT;
2222 END IF;
2223 END IF;
2224 END IF;
2225
2226 IF UPPER(p_action_code) = 'PACK' THEN
2227
2228 IF p_cont_instance_id IS NULL THEN
2229
2230 OPEN Get_Detail_Id (p_container_name);
2231
2232 FETCH Get_Detail_Id INTO
2233 l_cont_instance_id,
2234 l_cont_flag;
2235
2236 IF Get_Detail_Id%NOTFOUND OR SQL%ROWCOUNT > 1 OR (nvl(l_cont_flag,'N') = 'N') THEN
2237 CLOSE Get_Detail_Id;
2238 RAISE WSH_INVALID_CONT;
2239
2240 ELSE
2241 CLOSE Get_Detail_Id;
2242 END IF;
2243 ELSE l_cont_instance_id := p_cont_instance_id;
2244 END IF;
2245 IF l_debug_on THEN
2246 WSH_DEBUG_SV.log(l_module_name,'l_verify_status_lvl'
2247 ,l_verify_status_lvl);
2248 WSH_DEBUG_SV.log(l_module_name,'l_verify_org',l_verify_org);
2249 WSH_DEBUG_SV.log(l_module_name,'l_verify_dlvy',l_verify_dlvy);
2250 WSH_DEBUG_SV.log(l_module_name,'l_cont_instance_id'
2251 ,l_cont_instance_id);
2252 END IF;
2253 IF l_verify_status_lvl =1 OR l_verify_org=1 OR l_verify_dlvy=1 THEN
2254
2255 OPEN Check_Detail (l_cont_instance_id);
2256 Fetch Check_Detail INTO
2257 l_cont_instance_id,
2258 l_cont_flag,
2259 l_cont_released_status,
2260 l_cont_org_id,
2261 l_delivery_id;
2262
2263 IF Check_Detail%NOTFOUND THEN
2264 CLOSE Check_Detail;
2265 RAISE WSH_INVALID_CONT;
2266 ELSE
2267 CLOSE Check_Detail;
2268 END IF;
2269 IF l_debug_on THEN
2270 WSH_DEBUG_SV.log(l_module_name,'l_cont_instance_id'
2271 ,l_cont_instance_id);
2272 WSH_DEBUG_SV.log(l_module_name,'l_cont_flag',l_cont_flag);
2273 WSH_DEBUG_SV.log(l_module_name,'l_cont_released_status'
2274 ,l_cont_released_status);
2275 WSH_DEBUG_SV.log(l_module_name,'l_cont_org_id',l_cont_org_id);
2276 WSH_DEBUG_SV.log(l_module_name,'l_delivery_id',l_delivery_id);
2277 END IF;
2278
2279 IF l_verify_status_lvl=1 THEN
2280 IF (nvl(l_cont_flag,'N') = 'N')
2281 OR (NVL(l_cont_released_status,'Z') <> 'X') THEN
2282 RAISE WSH_INVALID_CONT;
2283 END IF;
2284 END IF;
2285 IF (l_verify_dlvy =1) AND (l_delivery_id IS NOT NULL )THEN
2286 OPEN c_dlvy(l_delivery_id);
2287 FETCH c_dlvy INTO l_dlvy_status_code;
2288 IF c_dlvy%NOTFOUND THEN
2289 CLOSE c_dlvy;
2290 RAISE WSH_INVALID_DELIVERY;
2291 ELSE
2292 CLOSE c_dlvy;
2293 END IF;
2294 IF l_debug_on THEN
2295 WSH_DEBUG_SV.log(l_module_name,'l_dlvy_status_code'
2296 ,l_dlvy_status_code);
2297 END IF;
2298 IF l_dlvy_status_code <> 'OP' THEN
2299 RAISE WSH_INVALID_DELIVERY ;
2300 END IF;
2301 END IF;
2302 END IF;
2303 -- verified call the private APIs to pack..
2304 END IF;
2305
2306 IF UPPER(p_action_code) = 'UNPACK' THEN
2307 IF nvl(p_container_flag,'N') = 'N' THEN
2308 RAISE WSH_INVALID_ACTION;
2309 END IF;
2310 ELSIF UPPER(p_action_code) = 'UNASSIGN' THEN
2311 IF nvl(p_delivery_flag,'N') = 'N' THEN
2312 RAISE WSH_INVALID_ACTION;
2313 END IF;
2314 END IF;
2315 -- call the private APIs to assign/unassign..
2316
2317 IF (UPPER(p_action_code) = 'PACK') OR (UPPER(p_action_code) = 'ASSIGN') THEN
2318 IF l_debug_on THEN
2319 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DLVB_COMMON_ACTIONS.ASSIGN_DETAILS',WSH_DEBUG_SV.C_PROC_LEVEL);
2320 END IF;
2321 --
2322 WSH_DLVB_COMMON_ACTIONS.Assign_Details (
2323 p_detail_tab => l_detail_tab,
2324 p_parent_detail_id => l_cont_instance_id,
2325 p_delivery_id => l_delivery_id,
2326 p_group_api_flag => l_group_api_flag,
2327 x_pack_status => l_pack_status,
2328 x_return_status => x_return_status);
2329
2330 IF x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
2331 IF x_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
2332 RAISE WSH_UTIL_CORE.G_EXC_WARNING;
2333 ELSE
2334 RAISE WSH_FAIL_CONT_ACTION;
2335 END IF;
2336 ELSE
2337 --Start of fix for bug 5234326/5282496
2338 IF ( l_cont_instance_id IS NOT NULL ) THEN
2339 IF l_debug_on THEN
2340 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TP_RELEASE.CALCULATE_CONT_DEL_TPDATES for entity LPN',WSH_DEBUG_SV.C_PROC_LEVEL);
2341 END IF;
2342
2343 l_cont_tab(1) := l_cont_instance_id;
2344 WSH_TP_RELEASE.calculate_cont_del_tpdates(
2345 p_entity => 'LPN',
2346 p_entity_ids =>l_cont_tab,
2347 x_return_status => x_return_status);
2348 ELSE
2349 IF l_debug_on THEN
2350 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TP_RELEASE.CALCULATE_CONT_DEL_TPDATES for entity DLVB',WSH_DEBUG_SV.C_PROC_LEVEL);
2351 END IF;
2352 WSH_TP_RELEASE.calculate_cont_del_tpdates(
2353 p_entity => 'DLVB',
2354 p_entity_ids =>l_detail_tab,
2355 x_return_status => x_return_status);
2356 END IF;
2357 --End of fix for bug 5234326/5282496
2358
2359 END IF;
2360 ELSIF (UPPER(p_action_code) = 'UNPACK') OR (UPPER(p_action_code) = 'UNASSIGN') THEN
2361 IF l_debug_on THEN
2362 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DLVB_COMMON_ACTIONS.UNASSIGN_DETAILS',WSH_DEBUG_SV.C_PROC_LEVEL);
2363 END IF;
2364 WSH_DLVB_COMMON_ACTIONS.Unassign_Details (
2365 p_detail_tab => l_detail_tab,
2366 p_parent_detail_flag => p_container_flag,
2367 p_delivery_flag => p_delivery_flag,
2368 p_group_api_flag => l_group_api_flag,
2369 x_return_status => x_return_status);
2370
2371 IF x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
2372 IF x_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
2373 RAISE WSH_UTIL_CORE.G_EXC_WARNING;
2374 ELSE
2375 RAISE WSH_FAIL_CONT_ACTION;
2376 END IF;
2377 END IF;
2378 ELSE
2379 RAISE WSH_INVALID_ACTION;
2380 END IF;
2381
2382 --
2383 -- K LPN CONV. rv
2384 --
2385 IF WSH_WMS_LPN_GRP.G_CALLBACK_REQUIRED = 'Y'
2386 THEN
2387 --{
2388 WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS
2389 (
2390 p_in_rec => l_lpn_in_sync_comm_rec,
2391 x_return_status => l_return_status,
2392 x_out_rec => l_lpn_out_sync_comm_rec
2393 );
2394 --
2395 --
2396 IF l_debug_on THEN
2397 WSH_DEBUG_SV.log(l_module_name,'Return status after calling WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS',l_return_status);
2398 WSH_DEBUG_SV.log(l_module_name,'Msg Count after calling WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS',l_msg_count);
2399 END IF;
2400 --
2401 --
2402 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
2403 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
2404 RAISE WSH_UTIL_CORE.G_EXC_WARNING;
2405 ELSIF l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR THEN
2406 RAISE WSH_FAIL_CONT_ACTION;
2407 ELSE
2408 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2409 END IF;
2410 END IF;
2411 --}
2412 END IF;
2413 --
2414 -- K LPN CONV. rv
2415 --
2416
2417
2418 --Bugfix 4070732 {
2419 IF FND_API.TO_BOOLEAN(p_commit) THEN
2420 l_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2421 IF NOT(WSH_UTIL_CORE.G_CALL_FTE_LOAD_TENDER_API) THEN
2422 WSH_UTIL_CORE.Process_stops_for_load_tender(
2423 p_reset_flags => FALSE,
2424 x_return_status => l_return_status);
2425 END IF;
2426 IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_SUCCESS)
2427 OR (l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING) THEN --{
2428
2429 COMMIT;
2430
2431 END IF; --}
2432
2433 wsh_util_core.api_post_call(p_return_status =>l_return_status,
2434 x_num_warnings =>l_num_warning,
2435 x_num_errors =>l_num_errors);
2436 END IF;
2437 --Bugfix 4070732 }
2438
2439 /*
2440 WSH_UTIL_CORE.get_messages('Y', l_msg_summary, l_msg_details, x_msg_count);
2441 if x_msg_count > 1 then
2442 x_msg_data := l_msg_summary || l_msg_details;
2443 else
2444 x_msg_data := l_msg_summary;
2445 end if;
2446 */
2447
2448 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2449 --Bugfix 4070732 {
2450 IF upper(WSH_UTIL_CORE.G_START_OF_SESSION_API) <> upper(l_api_session_name)
2451 THEN --{
2452 IF NOT(WSH_UTIL_CORE.G_CALL_FTE_LOAD_TENDER_API) THEN
2453 IF FND_API.TO_BOOLEAN(p_commit) THEN
2454
2455 IF l_debug_on THEN
2456 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.reset_stops_for_load_tender',WSH_DEBUG_SV.C_PROC_LEVEL);
2457 END IF;
2458 WSH_UTIL_CORE.reset_stops_for_load_tender(
2459 p_reset_flags => TRUE,
2460 x_return_status => l_return_status);
2461
2462 ELSE
2463
2464
2465 IF l_debug_on THEN
2466 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.Process_stops_for_load_tender',WSH_DEBUG_SV.C_PROC_LEVEL);
2467 END IF;
2468 WSH_UTIL_CORE.Process_stops_for_load_tender(
2469 p_reset_flags => TRUE,
2470 x_return_status => l_return_status);
2471 END IF;
2472
2473 IF l_debug_on THEN
2474 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
2475 END IF;
2476
2477 IF l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR,
2478 WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
2479 IF NOT(FND_API.TO_BOOLEAN(p_commit)) THEN
2480 rollback to Container_Action_SP_Grp;
2481 end if;
2482 END IF;
2483 --x_return_status is set to success before the call to fte load tender
2484 -- setting the return status to l_return_status
2485 x_return_status := l_return_status;
2486
2487 END IF;
2488 END IF; --}
2489
2490 --}
2491 --End of bug 4070732
2492
2493
2494 FND_MSG_PUB.Count_And_Get
2495 ( p_count => x_msg_count
2496 , p_data => x_msg_data
2497 , p_encoded => FND_API.G_FALSE
2498 );
2499 --
2500 IF l_debug_on THEN
2501 WSH_DEBUG_SV.pop(l_module_name);
2502 END IF;
2503 --
2504 EXCEPTION
2505
2506 WHEN WSH_UTIL_CORE.G_EXC_WARNING then
2507 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
2508 --
2509 -- K LPN CONV. rv
2510 --
2511 IF WSH_WMS_LPN_GRP.G_CALLBACK_REQUIRED = 'Y'
2512 THEN
2513 --{
2514 WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS
2515 (
2516 p_in_rec => l_lpn_in_sync_comm_rec,
2517 x_return_status => l_return_status,
2518 x_out_rec => l_lpn_out_sync_comm_rec
2519 );
2520 --
2521 --
2522 IF l_debug_on THEN
2523 WSH_DEBUG_SV.log(l_module_name,'Return status after calling WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS',l_return_status);
2524 WSH_DEBUG_SV.log(l_module_name,'Msg Count after calling WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS',l_msg_count);
2525 END IF;
2526 --
2527 --
2528 IF (l_return_status IN (WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR, WSH_UTIL_CORE.G_RET_STS_ERROR)) THEN
2529 x_return_status := l_return_status;
2530 END IF;
2531 --
2532 --}
2533 END IF;
2534 --
2535 -- K LPN CONV. rv
2536 --
2537
2538 --Bugfix 4070732 {
2539 IF upper(WSH_UTIL_CORE.G_START_OF_SESSION_API) = upper(l_api_session_name) THEN
2540 IF NOT(WSH_UTIL_CORE.G_CALL_FTE_LOAD_TENDER_API) THEN
2541 IF l_debug_on THEN
2542 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.Process_stops_for_load_tender',WSH_DEBUG_SV.C_PROC_LEVEL);
2543 END IF;
2544
2545 WSH_UTIL_CORE.Process_stops_for_load_tender(p_reset_flags => TRUE,
2546 x_return_status => l_return_status);
2547
2548 IF l_debug_on THEN
2549 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
2550 END IF;
2551
2552 IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)
2553 OR (l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR) THEN
2554 rollback to Container_Action_SP_Grp;
2555 x_return_status := l_return_status;
2556 END IF;
2557 END IF;
2558 END IF;
2559 --}
2560
2561 FND_MSG_PUB.Count_And_Get
2562 ( p_count => x_msg_count
2563 , p_data => x_msg_data
2564 , p_encoded => FND_API.G_FALSE
2565 );
2566 IF l_debug_on THEN
2567 WSH_DEBUG_SV.logmsg(l_module_name,'G_RET_STS_WARNING exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2568 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:G_RET_STS_WARNING');
2569 END IF;
2570
2571 WHEN WSH_INVALID_DETAIL then
2572 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2573 rollback to Container_Action_SP_Grp;
2574 fnd_message.set_name('WSH', 'WSH_DET_INVALID_DETAIL');
2575 WSH_UTIL_CORE.ADD_MESSAGE(x_return_status);
2576 /*
2577 WSH_UTIL_CORE.get_messages('Y', l_msg_summary, l_msg_details, x_msg_count);
2578 if x_msg_count > 1 then
2579 x_msg_data := l_msg_summary || l_msg_details;
2580 else
2581 x_msg_data := l_msg_summary;
2582 end if;
2583 */
2584
2585 --Bugfix 4070732 {
2586 IF upper(WSH_UTIL_CORE.G_START_OF_SESSION_API) = upper(l_api_session_name) THEN
2587 IF NOT(WSH_UTIL_CORE.G_CALL_FTE_LOAD_TENDER_API) THEN
2588 IF l_debug_on THEN
2589 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.Reset_stops_for_load_tender',WSH_DEBUG_SV.C_PROC_LEVEL);
2590 END IF;
2591
2592 WSH_UTIL_CORE.Reset_stops_for_load_tender(p_reset_flags => TRUE,
2593 x_return_status => l_return_status);
2594
2595
2596 IF l_debug_on THEN
2597 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
2598 END IF;
2599
2600 IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
2601 x_return_status := l_return_status;
2602 END IF;
2603
2604 END IF;
2605 END IF;
2606 --}
2607 --End of bug 4070732
2608
2609 FND_MSG_PUB.Count_And_Get
2610 ( p_count => x_msg_count
2611 , p_data => x_msg_data
2612 , p_encoded => FND_API.G_FALSE
2613 );
2614 IF l_debug_on THEN
2615 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_INVALID_DETAIL exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2616 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_INVALID_DETAIL');
2617 END IF;
2618
2619 WHEN WSH_INVALID_CONT then
2620 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2621 rollback to Container_Action_SP_Grp;
2622 fnd_message.set_name('WSH', 'WSH_CONT_INVALID_NAME');
2623 WSH_UTIL_CORE.ADD_MESSAGE(x_return_status);
2624 /*
2625 WSH_UTIL_CORE.get_messages('Y', l_msg_summary, l_msg_details, x_msg_count);
2626 if x_msg_count > 1 then
2627 x_msg_data := l_msg_summary || l_msg_details;
2628 else
2629 x_msg_data := l_msg_summary;
2630 end if;
2631 */
2632
2633 --Bugfix 4070732 {
2634 IF upper(WSH_UTIL_CORE.G_START_OF_SESSION_API) = upper(l_api_session_name) THEN
2635 IF NOT(WSH_UTIL_CORE.G_CALL_FTE_LOAD_TENDER_API) THEN
2636 IF l_debug_on THEN
2637 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.Reset_stops_for_load_tender',WSH_DEBUG_SV.C_PROC_LEVEL);
2638 END IF;
2639
2640 WSH_UTIL_CORE.Reset_stops_for_load_tender(p_reset_flags => TRUE,
2641 x_return_status => l_return_status);
2642
2643
2644 IF l_debug_on THEN
2645 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
2646 END IF;
2647
2648 IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
2649 x_return_status := l_return_status;
2650 END IF;
2651 END IF;
2652 END IF;
2653 --}
2654 --End of bug 4070732
2655
2656 FND_MSG_PUB.Count_And_Get
2657 ( p_count => x_msg_count
2658 , p_data => x_msg_data
2659 , p_encoded => FND_API.G_FALSE
2660 );
2661 IF l_debug_on THEN
2662 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_INVALID_CONT exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2663 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_INVALID_CONT');
2664 END IF;
2665 --
2666 WHEN WSH_INVALID_DELIVERY then
2667 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2668 rollback to Container_Action_SP_Grp;
2669 fnd_message.set_name('WSH', 'WSH_DET_INVALID_DEL');
2670 WSH_UTIL_CORE.ADD_MESSAGE(x_return_status);
2671 /*
2672 WSH_UTIL_CORE.get_messages('Y', l_msg_summary, l_msg_details, x_msg_count);
2673 if x_msg_count > 1 then
2674 x_msg_data := l_msg_summary || l_msg_details;
2675 else
2676 x_msg_data := l_msg_summary;
2677 end if;
2678 */
2679
2680 --Bugfix 4070732 {
2681 IF upper(WSH_UTIL_CORE.G_START_OF_SESSION_API) = upper(l_api_session_name) THEN
2682 IF NOT(WSH_UTIL_CORE.G_CALL_FTE_LOAD_TENDER_API) THEN
2683 IF l_debug_on THEN
2684 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.Reset_stops_for_load_tender',WSH_DEBUG_SV.C_PROC_LEVEL);
2685 END IF;
2686
2687 WSH_UTIL_CORE.Reset_stops_for_load_tender(p_reset_flags => TRUE,
2688 x_return_status => l_return_status);
2689
2690
2691 IF l_debug_on THEN
2692 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
2693 END IF;
2694
2695 IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
2696 x_return_status := l_return_status;
2697 END IF;
2698 END IF;
2699 END IF;
2700 --}
2701 --End of bug 4070732
2702
2703 FND_MSG_PUB.Count_And_Get
2704 ( p_count => x_msg_count
2705 , p_data => x_msg_data
2706 , p_encoded => FND_API.G_FALSE
2707 );
2708 IF l_debug_on THEN
2709 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_INVALID_DELIVERY exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2710 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_INVALID_DELIVERY');
2711 END IF;
2712 --
2713 WHEN WSH_FAIL_CONT_ACTION then
2714 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2715 rollback to Container_Action_SP_Grp;
2716 fnd_message.set_name('WSH', 'WSH_CONT_ACTION_ERROR');
2717 WSH_UTIL_CORE.ADD_MESSAGE(x_return_status);
2718 /*
2719 WSH_UTIL_CORE.get_messages('Y', l_msg_summary, l_msg_details, x_msg_count);
2720 if x_msg_count > 1 then
2721 x_msg_data := l_msg_summary || l_msg_details;
2722 else
2723 x_msg_data := l_msg_summary;
2724 end if;
2725 */
2726
2727 --Bugfix 4070732 {
2728 IF upper(WSH_UTIL_CORE.G_START_OF_SESSION_API) = upper(l_api_session_name) THEN
2729 IF NOT(WSH_UTIL_CORE.G_CALL_FTE_LOAD_TENDER_API) THEN
2730 IF l_debug_on THEN
2731 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.Reset_stops_for_load_tender',WSH_DEBUG_SV.C_PROC_LEVEL);
2732 END IF;
2733
2734 WSH_UTIL_CORE.Reset_stops_for_load_tender(p_reset_flags => TRUE,
2735 x_return_status => l_return_status);
2736
2737
2738 IF l_debug_on THEN
2739 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
2740 END IF;
2741
2742 IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
2743 x_return_status := l_return_status;
2744 END IF;
2745 END IF;
2746 END IF;
2747 --}
2748 --End of bug 4070732
2749
2750 FND_MSG_PUB.Count_And_Get
2751 ( p_count => x_msg_count
2752 , p_data => x_msg_data
2753 , p_encoded => FND_API.G_FALSE
2754 );
2755 IF l_debug_on THEN
2756 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_FAIL_CONT_ACTION exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2757 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_FAIL_CONT_ACTION');
2758 END IF;
2759 --
2760 WHEN WSH_INVALID_ENTITY_TYPE then
2761 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2762 rollback to Container_Action_SP_Grp;
2763 fnd_message.set_name('WSH', 'WSH_PUB_CONT_TYPE_ERR');
2764 WSH_UTIL_CORE.ADD_MESSAGE(x_return_status);
2765 /*
2766 WSH_UTIL_CORE.get_messages('Y', l_msg_summary, l_msg_details, x_msg_count);
2767 if x_msg_count > 1 then
2768 x_msg_data := l_msg_summary || l_msg_details;
2769 else
2770 x_msg_data := l_msg_summary;
2771 end if;
2772 */
2773 --Bugfix 4070732 {
2774 IF upper(WSH_UTIL_CORE.G_START_OF_SESSION_API) = upper(l_api_session_name) THEN
2775 IF NOT(WSH_UTIL_CORE.G_CALL_FTE_LOAD_TENDER_API) THEN
2776 IF l_debug_on THEN
2777 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.Reset_stops_for_load_tender',WSH_DEBUG_SV.C_PROC_LEVEL);
2778 END IF;
2779
2780 WSH_UTIL_CORE.Reset_stops_for_load_tender(p_reset_flags => TRUE,
2781 x_return_status => l_return_status);
2782
2783
2784 IF l_debug_on THEN
2785 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
2786 END IF;
2787
2788 IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
2789 x_return_status := l_return_status;
2790 END IF;
2791 END IF;
2792 END IF;
2793 --}
2794 --End of bug 4070732
2795
2796 FND_MSG_PUB.Count_And_Get
2797 ( p_count => x_msg_count
2798 , p_data => x_msg_data
2799 , p_encoded => FND_API.G_FALSE
2800 );
2801 IF l_debug_on THEN
2802 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_INVALID_ENTITY_TYPE exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2803 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_INVALID_ENTITY_TYPE');
2804 END IF;
2805 --
2806 WHEN FND_API.G_EXC_ERROR THEN
2807 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2808 rollback to Container_Action_SP_Grp;
2809 --Bugfix 4070732 {
2810 IF upper(WSH_UTIL_CORE.G_START_OF_SESSION_API) = upper(l_api_session_name) THEN
2811 IF NOT(WSH_UTIL_CORE.G_CALL_FTE_LOAD_TENDER_API) THEN
2812 IF l_debug_on THEN
2813 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.Reset_stops_for_load_tender',WSH_DEBUG_SV.C_PROC_LEVEL);
2814 END IF;
2815
2816 WSH_UTIL_CORE.Reset_stops_for_load_tender(p_reset_flags => TRUE,
2817 x_return_status => l_return_status);
2818
2819
2820 IF l_debug_on THEN
2821 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
2822 END IF;
2823 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR THEN
2824 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2825 END IF;
2826
2827 END IF;
2828 END IF;
2829 --}
2830 --End of bug 4070732
2831
2832 FND_MSG_PUB.Count_And_Get
2833 ( p_count => x_msg_count
2834 , p_data => x_msg_data
2835 , p_encoded => FND_API.G_FALSE
2836 );
2837
2838 IF l_debug_on THEN
2839 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2840 WSH_DEBUG_SV.pop(l_module_name);
2841 END IF;
2842 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2843 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2844 rollback to Container_Action_SP_Grp;
2845 --Bugfix 4070732 {
2846 IF upper(WSH_UTIL_CORE.G_START_OF_SESSION_API) = upper(l_api_session_name) THEN
2847 IF NOT(WSH_UTIL_CORE.G_CALL_FTE_LOAD_TENDER_API) THEN
2848 IF l_debug_on THEN
2849 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.Reset_stops_for_load_tender',WSH_DEBUG_SV.C_PROC_LEVEL);
2850 END IF;
2851
2852 WSH_UTIL_CORE.Reset_stops_for_load_tender(p_reset_flags => TRUE,
2853 x_return_status => l_return_status);
2854
2855
2856 IF l_debug_on THEN
2857 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
2858 END IF;
2859
2860 END IF;
2861 END IF;
2862 --}
2863 --End of bug 4070732
2864
2865 FND_MSG_PUB.Count_And_Get
2866 ( p_count => x_msg_count
2867 , p_data => x_msg_data
2868 , p_encoded => FND_API.G_FALSE
2869 );
2870
2871 IF l_debug_on THEN
2872 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2873 WSH_DEBUG_SV.pop(l_module_name);
2874 END IF;
2875 WHEN OTHERS then
2876 wsh_util_core.default_handler('WSH_CONTAINER_GRP.Container_Actions');
2877 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2878 rollback to Container_Action_SP_Grp;
2879 /*
2880 WSH_UTIL_CORE.get_messages('Y', l_msg_summary, l_msg_details, x_msg_count);
2881 if x_msg_count > 1 then
2882 x_msg_data := l_msg_summary || l_msg_details;
2883 else
2884 x_msg_data := l_msg_summary;
2885 end if;
2886 */
2887 --Bugfix 4070732 {
2888 IF upper(WSH_UTIL_CORE.G_START_OF_SESSION_API) = upper(l_api_session_name) THEN
2889 IF NOT(WSH_UTIL_CORE.G_CALL_FTE_LOAD_TENDER_API) THEN
2890 IF l_debug_on THEN
2891 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.Reset_stops_for_load_tender',WSH_DEBUG_SV.C_PROC_LEVEL);
2892 END IF;
2893
2894 WSH_UTIL_CORE.Reset_stops_for_load_tender(p_reset_flags => TRUE,
2895 x_return_status => l_return_status);
2896
2897
2898 IF l_debug_on THEN
2899 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
2900 END IF;
2901
2902 END IF;
2903 END IF;
2904 --}
2905 --End of bug 4070732
2906
2907 FND_MSG_PUB.Count_And_Get
2908 ( p_count => x_msg_count
2909 , p_data => x_msg_data
2910 , p_encoded => FND_API.G_FALSE
2911 );
2912
2913 IF l_debug_on THEN
2914 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2915 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
2916 END IF;
2917 --
2918 END Container_Actions;
2919
2920
2921 END WSH_CONTAINER_GRP;