[Home] [Help]
PACKAGE BODY: APPS.WSH_USA_CATEGORIES_PVT
Source
1 PACKAGE BODY WSH_USA_CATEGORIES_PVT as
2 /* $Header: WSHUSACB.pls 120.21.12020000.3 2012/08/17 13:23:36 skanduku ship $ */
3
4 -- 2071048
5 CURSOR c_cache_attributes_1(p_source_line_id IN NUMBER,
6 p_delivery_detail_id IN NUMBER,
7 p_source_code IN WSH_DELIVERY_DETAILS.SOURCE_CODE%TYPE) IS
8 SELECT source_line_id,
9 source_code,
10 organization_id,
11 inventory_item_id, --bug#6407943
12 original_subinventory subinventory,
13 date_scheduled,
14 ship_set_id,
15 ship_to_site_use_id,
16 intmed_ship_to_location_id,
17 customer_id,
18 src_requested_quantity, --Bug#6077222
19 src_requested_quantity_uom, --Bug#6077222
20 freight_terms_code,
21 fob_code,
22 ship_method_code,
23 carrier_id,
24 requested_quantity,
25 requested_quantity_uom,
26 source_line_set_id, -- Bug 2181132
27 ship_tolerance_above,-- Bug 2181132
28 ship_tolerance_below,-- Bug 2181132
29 request_date_type_code,
30 container_flag,
31 earliest_pickup_date,
32 latest_pickup_date,
33 earliest_dropoff_date,
34 latest_dropoff_date,
35 mode_of_transport,
36 service_level,
37 decode(p_delivery_detail_id,
38 FND_API.G_MISS_NUM, FND_API.G_MISS_NUM,
39 delivery_detail_id) delivery_detail_id,
40 'Y' cache_flag,
41 project_id,
42 task_id
43 FROM wsh_delivery_details
44 WHERE source_line_id = p_source_line_id
45 AND source_code = p_source_code
46 AND delivery_detail_id = DECODE(p_delivery_detail_id, FND_API.G_MISS_NUM,
47 delivery_detail_id, p_delivery_detail_id)
48 AND released_status in ('R', 'N', 'X')
49 AND rownum = 1
50 ORDER BY decode (released_status,
51 'R', 1,
52 'N', 2,
53 'X', 3,
54 4);
55 CURSOR c_cache_attributes_2(p_source_line_id IN NUMBER,
56 p_delivery_detail_id IN NUMBER,
57 p_source_code IN WSH_DELIVERY_DETAILS.SOURCE_CODE%TYPE) IS
58 SELECT source_line_id,
59 source_code,
60 organization_id,
61 inventory_item_id, --bug#6407943
62 original_subinventory subinventory,
63 date_scheduled,
64 ship_set_id,
65 ship_to_site_use_id,
66 intmed_ship_to_location_id,
67 customer_id,
68 src_requested_quantity, --Bug#6077222
69 src_requested_quantity_uom, --Bug#6077222
70 freight_terms_code,
71 fob_code,
72 ship_method_code,
73 carrier_id,
74 requested_quantity,
75 requested_quantity_uom,
76 source_line_set_id, -- Bug 2181132
77 ship_tolerance_above,-- Bug 2181132
78 ship_tolerance_below,-- Bug 2181132
79 request_date_type_code,
80 container_flag,
81 earliest_pickup_date,
82 latest_pickup_date,
83 earliest_dropoff_date,
84 latest_dropoff_date,
85 mode_of_transport,
86 service_level,
87 decode(p_delivery_detail_id,
88 FND_API.G_MISS_NUM, FND_API.G_MISS_NUM,
89 delivery_detail_id) delivery_detail_id,
90 'Y' cache_flag,
91 project_id,
92 task_id
93 FROM wsh_delivery_details
94 WHERE source_line_id = p_source_line_id
95 AND source_code = p_source_code
96 AND delivery_detail_id = DECODE(p_delivery_detail_id, FND_API.G_MISS_NUM,
97 delivery_detail_id, p_delivery_detail_id)
98 AND released_status in ('B', 'Y', 'S')
99 AND rownum = 1
100 ORDER BY decode (released_status,
101 'B', 1,
102 'Y', 2,
103 'S', 3,
104 4);
105 -- 2071048
106
107
108 -- Global Variables
109
110 g_param_info WSH_SHIPPING_PARAMS_PVT.Parameter_Rec_Typ;
111 g_cache_detail_rec c_cache_attributes_1%ROWTYPE;
112
113 g_cache_wms_org_id NUMBER;
114 g_cache_wms_flag VARCHAR2(1);
115
116 -- Private Procedures
117
118
119 --
120 -- Procedure: Log_Exception
121 -- Parameters:
122 -- p_delivery_detail_id delivery detail against which exception is logged.
123 -- p_location_id ship from location where the exception is logged.
124 -- p_exception_name name of exception
125 -- x_return_status return status
126 --
127 -- Description:
128 -- This is a wrapper around wsh_xc_util.log_exception
129 -- to be called by the Change Attribute procedures that
130 -- require exceptions to be logged.
131
132 --
133 G_PKG_NAME CONSTANT VARCHAR2(50) := 'WSH_USA_CATEGORIES_PVT';
134 --
135 PROCEDURE Log_Exception(
136 p_delivery_detail_id IN NUMBER,
137 p_location_id IN NUMBER,
138 p_exception_name IN VARCHAR2,
139 p_entity_name IN VARCHAR2 DEFAULT NULL,
140 p_entity_id IN NUMBER DEFAULT NULL,
141 x_return_status OUT NOCOPY VARCHAR2) IS
142
143
144 l_exception_error_message VARCHAR2(2000) := NULL;
145 l_exception_msg_count NUMBER;
146 l_dummy_exception_id NUMBER;
147 l_delivery_assignment_id NUMBER;
148 l_exception_msg_data VARCHAR2(4000) := NULL;
149 l_msg VARCHAR2(2000);
150 l_entity VARCHAR2(30);
151
152 --variables added for bug 2834274
153 l_name_of_delivery VARCHAR2(100);
154 l_name_of_container VARCHAR2(100);
155 --
156
157 --
158 l_debug_on BOOLEAN;
159 --
160 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'LOG_EXCEPTION';
161 --
162 BEGIN
163 --
164 --
165 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
166 --
167 IF l_debug_on IS NULL
168 THEN
169 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
170 END IF;
171 --
172 IF l_debug_on THEN
173 WSH_DEBUG_SV.push(l_module_name);
174 --
175 WSH_DEBUG_SV.log(l_module_name,'P_DELIVERY_DETAIL_ID',P_DELIVERY_DETAIL_ID);
176 WSH_DEBUG_SV.log(l_module_name,'P_LOCATION_ID',P_LOCATION_ID);
177 WSH_DEBUG_SV.log(l_module_name,'P_EXCEPTION_NAME',P_EXCEPTION_NAME);
178 WSH_DEBUG_SV.log(l_module_name,'P_ENTITY_NAME',P_ENTITY_NAME);
179 WSH_DEBUG_SV.log(l_module_name,'P_ENTITY_ID',P_ENTITY_ID);
180 END IF;
181 --
182 IF p_entity_name IS NOT NULL THEN
183 FND_MESSAGE.SET_NAME('WSH',p_entity_name);
184 l_entity := FND_MESSAGE.GET;
185 END IF;
186
187 FND_MESSAGE.SET_NAME('WSH',p_exception_name);
188 FND_MESSAGE.SET_TOKEN('DETAIL',p_delivery_detail_id);
189
190 --Bug 2834274
191 --{
192 IF p_entity_name IS NOT NULL THEN
193 FND_MESSAGE.SET_TOKEN('ENTITY_NAME',l_entity);
194 IF p_entity_id IS NOT NULL THEN --{
195
196 IF p_entity_name = 'WSH_DELIVERY' THEN
197 l_name_of_delivery := WSH_NEW_DELIVERIES_PVT.Get_Name(p_entity_id);
198 IF l_name_of_delivery IS NOT NULL THEN
199 FND_MESSAGE.SET_TOKEN('ENTITY_ID',l_name_of_delivery);
200 ELSE
201 FND_MESSAGE.SET_TOKEN('ENTITY_ID',p_entity_id);
202 END IF;
203 ELSIF p_entity_name = 'WSH_CONTAINER' THEN
204 l_name_of_container := WSH_CONTAINER_UTILITIES.Get_Cont_Name(p_entity_id);
205 IF l_name_of_container IS NOT NULL THEN
206 FND_MESSAGE.SET_TOKEN('ENTITY_ID',l_name_of_container);
207 ELSE
208 FND_MESSAGE.SET_TOKEN('ENTITY_ID',p_entity_id);
209 END IF;
210 ELSE
211 FND_MESSAGE.SET_TOKEN('ENTITY_ID',p_entity_id);
212 END IF;
213
214 END IF; --}
215 END IF;
216 --}
217
218 l_msg := FND_MESSAGE.GET;
219 wsh_xc_util.log_exception(
220 p_api_version => 1.0,
221 x_return_status => x_return_status,
222 x_msg_count => l_exception_msg_count,
223 x_msg_data => l_exception_msg_data,
224 x_exception_id => l_dummy_exception_id ,
225 p_logged_at_location_id => p_location_id,
226 p_exception_location_id => p_location_id,
227 p_logging_entity => 'SHIPPER',
228 p_logging_entity_id => FND_GLOBAL.USER_ID,
229 p_exception_name => p_exception_name,
230 p_message => l_msg,
231 p_delivery_detail_id => p_delivery_detail_id,
232 p_error_message => l_exception_error_message
233 );
234
235 --
236 IF l_debug_on THEN
237 WSH_DEBUG_SV.pop(l_module_name);
238 END IF;
239 --
240 EXCEPTION
241
242 WHEN OTHERS THEN
243 -- close open cursors as needed
244 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
245 WSH_UTIL_CORE.DEFAULT_HANDLER('WSH_USA_CATEGORIES_PVT.Log_Exception',l_module_name);
246 --
247 IF l_debug_on THEN
248 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
249 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
250 END IF;
251 --
252 END Log_Exception;
253
254 PROCEDURE Cache_Changed_Attributes(p_source_line_id IN NUMBER,
255 p_source_code IN VARCHAR2,
256 p_delivery_detail_id IN NUMBER DEFAULT FND_API.G_MISS_NUM,
257 x_return_status OUT NOCOPY VARCHAR2) IS
258
259
260 --
261 l_debug_on BOOLEAN;
262 --
263 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'CACHE_CHANGED_ATTRIBUTES';
264 --
265 BEGIN
266
267 --
268 --
269 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
270 --
271 IF l_debug_on IS NULL
272 THEN
273 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
274 END IF;
275 --
276 IF l_debug_on THEN
277 WSH_DEBUG_SV.push(l_module_name);
278 --
279 WSH_DEBUG_SV.log(l_module_name,'P_SOURCE_LINE_ID',P_SOURCE_LINE_ID);
280 WSH_DEBUG_SV.log(l_module_name,'P_SOURCE_CODE',P_SOURCE_CODE);
281 WSH_DEBUG_SV.log(l_module_name,'P_DELIVERY_DETAIL_ID',P_DELIVERY_DETAIL_ID);
282 END IF;
283 --
284 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
285
286 IF g_cache_detail_rec.cache_flag = 'Y'
287 AND g_cache_detail_rec.source_line_id = p_source_line_id
288 AND g_cache_detail_rec.source_code = p_source_code
289 AND g_cache_detail_rec.delivery_detail_id = p_delivery_detail_id
290 THEN
291 --
292 IF l_debug_on THEN
293 WSH_DEBUG_SV.pop(l_module_name,'Nothing changed');
294 END IF;
295 --
296 RETURN;
297 END IF;
298
299 OPEN c_cache_attributes_1(p_source_line_id, p_delivery_detail_id, p_source_code);
300
301 FETCH c_cache_attributes_1 INTO g_cache_detail_rec;
302
303 IF c_cache_attributes_1%NOTFOUND THEN
304 CLOSE c_cache_attributes_1;
305 OPEN c_cache_attributes_2(p_source_line_id, p_delivery_detail_id, p_source_code);
306 FETCH c_cache_attributes_2 INTO g_cache_detail_rec;
307 IF c_cache_attributes_2%NOTFOUND THEN
308 CLOSE c_cache_attributes_2;
309 FND_MESSAGE.SET_NAME('WSH', 'WSH_NO_DATA_FOUND');
310 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
311 WSH_UTIL_CORE.Add_Message(x_return_status,l_module_name);
312 --
313 IF l_debug_on THEN
314 WSH_DEBUG_SV.pop(l_module_name,'WSH_NO_DATA_FOUND');
315 END IF;
316 --
317 RETURN;
318 END IF;
319 CLOSE c_cache_attributes_2;
320 END IF;
321
322 IF c_cache_attributes_1%ISOPEN THEN
323 CLOSE c_cache_attributes_1;
324 END IF;
325 --
326 IF l_debug_on THEN
327 WSH_DEBUG_SV.logmsg(l_module_name, 'CACHED ATTRIBUTES FOR '|| G_CACHE_DETAIL_REC.SOURCE_CODE || ' LINE: '|| G_CACHE_DETAIL_REC.SOURCE_LINE_ID || ' DD: '||G_CACHE_DETAIL_REC.DELIVERY_DETAIL_ID );
328 WSH_DEBUG_SV.logmsg(l_module_name, 'ORG : '|| G_CACHE_DETAIL_REC.ORGANIZATION_ID ||' ORIG_SUB: '|| G_CACHE_DETAIL_REC.SUBINVENTORY|| ' DATE_SCHED: '|| G_CACHE_DETAIL_REC.DATE_SCHEDULED );
329 WSH_DEBUG_SV.logmsg(l_module_name, ' SHIP_SET: '|| G_CACHE_DETAIL_REC.SHIP_SET_ID || ' DEL GRP ATTR: ...' );
330 WSH_DEBUG_SV.logmsg(l_module_name,
331 G_CACHE_DETAIL_REC.SHIP_TO_SITE_USE_ID
332 ||' - '
333 || G_CACHE_DETAIL_REC.INTMED_SHIP_TO_LOCATION_ID
334 || ' - '
335 || G_CACHE_DETAIL_REC.CUSTOMER_ID
336 || ' - '
337 || G_CACHE_DETAIL_REC.FREIGHT_TERMS_CODE
338 || ' - '
339 || G_CACHE_DETAIL_REC.FOB_CODE
340 || ' - '
341 || G_CACHE_DETAIL_REC.SHIP_METHOD_CODE
342 || ' - '
343 || G_CACHE_DETAIL_REC.CARRIER_ID );
344 WSH_DEBUG_SV.logmsg(l_module_name, 'REQUESTED_QUANTITY : '|| G_CACHE_DETAIL_REC.REQUESTED_QUANTITY|| ' '|| G_CACHE_DETAIL_REC.REQUESTED_QUANTITY_UOM );
345 WSH_DEBUG_SV.pop(l_module_name);
346 END IF;
347 --
348 EXCEPTION
349
350 WHEN OTHERS THEN
351 IF c_cache_attributes_1%ISOPEN THEN
352 CLOSE c_cache_attributes_1;
353 END IF;
354 IF c_cache_attributes_2%ISOPEN THEN
355 CLOSE c_cache_attributes_2;
356 END IF;
357 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
358 --
359 wsh_util_core.default_handler('WSH_USA_CATEGORIES_PVT.Cache_Changed_Attributes',l_module_name);
360 IF l_debug_on THEN
361 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
362 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
363 END IF;
364 --
365 END Cache_Changed_Attributes;
366
367
368 -- Procedure: Check_WMS
369 -- Parameters:
370 -- p_source_code source code of record to check
371 -- p_oraganization_id organization id to check
372 -- for WMS
373 -- x_wms_flag flag to indicate if the delivery
374 -- lines are under WMS
375 -- 'Y' - org is under WMS,
376 -- 'N' - org is not under WMS
377 -- x_return_status return status
378 --
379 -- Description:
380 -- Calls category APIs to check the attributes being updated.
381
382
383 PROCEDURE Check_WMS(
384 p_organization_id IN NUMBER,
385 x_wms_flag OUT NOCOPY VARCHAR2,
386 x_return_status OUT NOCOPY VARCHAR2) IS
387
388 l_wms_installed BOOLEAN;
389 l_return_status VARCHAR2(1);
390 l_msg_count NUMBER;
391 l_msg_data VARCHAR2(4000);
392 others EXCEPTION;
393
394 --
395 l_debug_on BOOLEAN;
396 --
397 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'CHECK_WMS';
398 --
399 BEGIN
400 --
401 --
402 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
403 --
404 IF l_debug_on IS NULL
405 THEN
406 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
407 END IF;
408 --
409 IF l_debug_on THEN
410 WSH_DEBUG_SV.push(l_module_name);
411 --
412 WSH_DEBUG_SV.log(l_module_name,'P_ORGANIZATION_ID',P_ORGANIZATION_ID);
413 WSH_DEBUG_SV.log(l_module_name,'g_cache_wms_org_id',g_cache_wms_org_id);
414 END IF;
415 --
416 l_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
417 x_wms_flag := 'N';
418
419 IF g_cache_wms_org_id = p_organization_id THEN
420 x_wms_flag := g_cache_wms_flag;
421 --
422 IF l_debug_on THEN
423 WSH_DEBUG_SV.pop(l_module_name);
424 END IF;
425 --
426 RETURN;
427 END IF;
428
429
430 g_cache_wms_org_id := p_organization_id;
431 g_cache_wms_flag := 'N';
432
433 l_wms_installed := WMS_INSTALL.CHECK_INSTALL(
434 x_return_status => l_return_status,
435 x_msg_count => l_msg_count,
436 x_msg_data => l_msg_data,
437 p_organization_id => p_organization_id);
438
439 IF l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
440 --
441 IF l_debug_on THEN
442 WSH_DEBUG_SV.logmsg(l_module_name, 'UNEXPECTED ERROR IN CHECK_WMS' );
443 END IF;
444 --
445 raise others;
446 END IF;
447
448 IF l_wms_installed THEN
449 x_wms_flag := 'Y';
450 g_cache_wms_flag := 'Y';
451 END IF;
452
453 --
454 IF l_debug_on THEN
455 WSH_DEBUG_SV.log(l_module_name,'l_wms_installed',l_wms_installed);
456 WSH_DEBUG_SV.log(l_module_name,'x_wms_flag',x_wms_flag);
457 WSH_DEBUG_SV.pop(l_module_name);
458 END IF;
459 --
460 EXCEPTION
461 WHEN OTHERS THEN
462 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
463 wsh_util_core.default_handler('WSH_USA_CATEGORIES_PVT.Check_WMS',l_module_name);
464 IF l_debug_on THEN
465 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
466 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
467 END IF;
468 --
469 END Check_WMS;
470
471 -- Public Procedures
472
473 PROCEDURE Check_Attributes(
474 p_source_code IN VARCHAR2,
475 p_attributes_rec IN WSH_INTERFACE.ChangedAttributeRecType,
476 x_changed_detail OUT NOCOPY WSH_USA_CATEGORIES_PVT.ChangedDetailRec,
477 x_update_allowed OUT NOCOPY VARCHAR2,
478 x_return_status OUT NOCOPY VARCHAR2)
479 IS
480 update_not_allowed EXCEPTION;
481 others EXCEPTION;
482
483 l_rs VARCHAR2(1) := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
484 l_update_allowed VARCHAR2(1) := 'Y';
485 l_wms_flag VARCHAR2(1);
486 -- HW OPM for OM changes
487 -- HW OPMCONV. Removed OPM variables
488
489 --
490 l_debug_on BOOLEAN;
491 --
492 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'CHECK_ATTRIBUTES';
493 --
494 BEGIN
495 --
496 --
497 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
498 --
499 IF l_debug_on IS NULL
500 THEN
501 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
502 END IF;
503 --
504 IF l_debug_on THEN
505 WSH_DEBUG_SV.push(l_module_name);
506 --
507 WSH_DEBUG_SV.log(l_module_name,'P_SOURCE_CODE',P_SOURCE_CODE);
508 END IF;
509 -- calls to categories should be sequenced correctly to avoid conflicts:
510 -- Each category validates the conditions and performs actions that can change conditions.
511
512 -- always look up attributes because they may have changed in previous records for
513 -- same source_line_id.
514 Cache_Changed_Attributes(p_source_line_id => p_attributes_rec.source_line_id,
515 p_source_code => p_source_code,
516 p_delivery_detail_id => p_attributes_rec.delivery_detail_id,
517 x_return_status => l_rs);
518 IF (l_rs IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)) THEN
519 IF l_debug_on THEN
520 WSH_DEBUG_SV.log(l_module_name,'Cache_Changed_Attributes returns Error');
521 END IF;
522 RAISE update_not_allowed;
523 END IF;
524
525 -- HW OPM for OM changes
526 -- HW OPMCONV. Removed branching
527
528 -- HW end of OM changes
529 Check_WMS(
530 p_organization_id => g_cache_detail_rec.organization_id,
531 x_wms_flag => l_wms_flag,
532 x_return_status => l_rs);
533 IF (l_rs IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)) THEN
534 RAISE others;
535 END IF;
536
537 WSH_USA_CATEGORIES_PVT.Change_Schedule(
538 p_attributes_rec => p_attributes_rec,
539 p_source_code => p_source_code,
540 p_wms_flag => l_wms_flag,
541 x_update_allowed => l_update_allowed,
542 x_return_status => l_rs);
543 IF (l_update_allowed = 'N')
544 OR (l_rs IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)) THEN
545 RAISE update_not_allowed;
546 END IF;
547 WSH_USA_CATEGORIES_PVT.Change_Scheduled_Date(
548 p_attributes_rec => p_attributes_rec,
549 p_source_code => p_source_code,
550 p_wms_flag => l_wms_flag,
551 x_update_allowed => l_update_allowed,
552 x_return_status => l_rs);
553 IF (l_update_allowed = 'N')
554 OR (l_rs IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)) THEN
555 IF l_debug_on THEN
556 WSH_DEBUG_SV.log(l_module_name,'Change_Scheduled_Date returned Error');
557 END IF;
558 RAISE update_not_allowed;
559 END IF;
560
561 -- HW OPM BUG#:2296620 09/27 Need to remove the branch since OPM is supporting shipsets
562
563 WSH_USA_CATEGORIES_PVT.Change_Sets(
564 p_attributes_rec => p_attributes_rec,
565 p_source_code => p_source_code,
566 p_wms_flag => l_wms_flag,
567 x_update_allowed => l_update_allowed,
568 x_return_status => l_rs);
569 IF (l_update_allowed = 'N')
570 OR (l_rs IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)) THEN
571 RAISE update_not_allowed;
572 END IF;
573
574 WSH_USA_CATEGORIES_PVT.Change_Delivery_Group(
575 p_attributes_rec => p_attributes_rec,
576 p_source_code => p_source_code,
577 p_wms_flag => l_wms_flag,
578 x_update_allowed => l_update_allowed,
579 x_return_status => l_rs);
580 IF (l_update_allowed = 'N')
581 OR (l_rs IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)) THEN
582 RAISE update_not_allowed;
583 END IF;
584
585 -- Bug 2181132 for overship tolerance
586 WSH_USA_CATEGORIES_PVT.Change_Ship_Tolerance(
587 p_attributes_rec => p_attributes_rec,
588 p_source_code => p_source_code,
589 p_wms_flag => l_wms_flag,
590 x_update_allowed => l_update_allowed,
591 x_return_status => l_rs);
592 IF (l_update_allowed = 'N')
593 OR (l_rs IN (WSH_UTIL_CORE.G_RET_STS_ERROR,
594 WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)) THEN
595 RAISE update_not_allowed;
596 END IF;
597
598 IF (p_attributes_rec.action_flag <> 'S') THEN
599 -- we check quantity change only if this record is not an action to split.
600 WSH_USA_CATEGORIES_PVT.Change_Quantity( -- Change in Quantity
601 p_attributes_rec => p_attributes_rec,
602 p_source_code => p_source_code,
603 p_wms_flag => l_wms_flag,
604 x_update_allowed => l_update_allowed,
605 x_return_status => l_rs);
606 END IF; -- action_flag <> 'S'
607 IF (l_update_allowed = 'N')
608 OR (l_rs IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)) THEN
609 RAISE update_not_allowed;
610 END IF;
611 -- Standalone Project - Do no call Change_TP_Dates API if instance is running in Standalone mode
612 IF NVL(WMS_DEPLOY.Wms_Deployment_Mode,'I') <> 'D' THEN --{
613 WSH_USA_CATEGORIES_PVT.Change_TP_DATES(
614 p_attributes_rec => p_attributes_rec,
615 p_source_code => p_source_code,
616 x_changed_detail => x_changed_detail,
617 x_update_allowed => l_update_allowed,
618 x_return_status => l_rs);
619 IF(l_update_allowed = 'N')
620 OR (l_rs IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)) THEN
621 RAISE update_not_allowed;
622 END IF;
623 END IF;
624
625 x_return_status := l_rs;
626 x_update_allowed := l_update_allowed;
627 --
628 IF l_debug_on THEN
629 WSH_DEBUG_SV.log(l_module_name,'x_return_status',x_return_status);
630 WSH_DEBUG_SV.log(l_module_name,'x_update_allowed',x_update_allowed);
631 WSH_DEBUG_SV.pop(l_module_name);
632 END IF;
633 --
634 EXCEPTION
635 WHEN update_not_allowed THEN
636 x_update_allowed := l_update_allowed;
637 x_return_status := l_rs;
638 --
639 IF l_debug_on THEN
640 WSH_DEBUG_SV.logmsg(l_module_name,'UPDATE_NOT_ALLOWED exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
641 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:UPDATE_NOT_ALLOWED');
642 END IF;
643 --
644 RETURN;
645 WHEN OTHERS THEN
646 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
647 wsh_util_core.default_handler('WSH_USA_CATEGORIES_PVT.Check_Categories',l_module_name);
648 --
649 IF l_debug_on THEN
650 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
651 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
652 END IF;
653 --
654 END Check_Attributes;
655
656
657
658
659 PROCEDURE Change_TP_Dates(
660 p_attributes_rec IN WSH_INTERFACE.ChangedAttributeRecType,
661 p_source_code IN VARCHAR2,
662 x_changed_detail OUT NOCOPY WSH_USA_CATEGORIES_PVT.ChangedDetailRec,
663 x_update_allowed IN OUT NOCOPY VARCHAR2,
664 x_return_status OUT NOCOPY VARCHAR2) IS
665
666 update_not_allowed EXCEPTION;
667
668 l_datetype VARCHAR2(20);
669
670 l_in_id wsh_util_core.id_tab_type;
671
672 l_earliest_pickup_date DATE;
673 l_latest_pickup_date DATE;
674 l_earliest_dropoff_date DATE;
675 l_latest_dropoff_date DATE;
676
677
678
679 l_schedule_ship_date DATE;
680 l_latest_acceptable_date DATE;
681 l_promise_date DATE;
682 l_schedule_arrival_date DATE;
683 l_earliest_acceptable_date DATE;
684 l_earliest_ship_date DATE;
685
686 l_return_status VARCHAR2(250);
687
688 l_delivery_detail_id NUMBER;
689
690 --
691 l_debug_on BOOLEAN;
692 --
693 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'Change_TP_DATES';
694 --
695 BEGIN
696
697
698
699 --
700 --
701 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
702 --
703 IF l_debug_on IS NULL
704 THEN
705 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
706 END IF;
707 --
708 IF l_debug_on THEN
709 WSH_DEBUG_SV.push(l_module_name);
710 --
711 WSH_DEBUG_SV.log(l_module_name,'P_SOURCE_CODE',P_SOURCE_CODE);
712 WSH_DEBUG_SV.log(l_module_name,'X_UPDATE_ALLOWED',X_UPDATE_ALLOWED);
713 END IF;
714
715 IF (g_cache_detail_rec.container_flag = 'N') THEN
716
717 WSH_TP_RELEASE.calculate_tp_dates(
718 p_request_date_type => g_cache_detail_rec.request_date_type_code,
719 p_latest_acceptable_date => p_attributes_rec.latest_acceptable_date,
720 p_promise_date => p_attributes_rec.promise_date,
721 p_schedule_arrival_date => p_attributes_rec.schedule_arrival_date,
722 p_schedule_ship_date => p_attributes_rec.date_scheduled,
723 p_earliest_acceptable_date => p_attributes_rec.earliest_acceptable_date,
724 p_demand_satisfaction_date => p_attributes_rec.earliest_ship_date,
725 p_source_line_id => p_attributes_rec.source_line_id,
726 p_source_code => p_source_code,
727 p_inventory_item_id => p_attributes_rec.inventory_item_id,
728 p_organization_id => p_attributes_rec.organization_id,
729 x_return_status => l_return_status,
730 x_earliest_pickup_date => l_earliest_pickup_date,
731 x_latest_pickup_date => l_latest_pickup_date,
732 x_earliest_dropoff_date => l_earliest_dropoff_date,
733 x_latest_dropoff_date => l_latest_dropoff_date
734 );
735 IF (l_return_status NOT IN (WSH_UTIL_CORE.G_RET_STS_SUCCESS, WSH_UTIL_CORE.G_RET_STS_WARNING)) THEN
736 WSH_INTERFACE.PrintMsg(name=>'WSH_CALC_TP_DATES',
737 txt=>'Error in calculating TP dates: Date Type : '||l_datetype
738 ||'Latest Acceptable Date : '||p_attributes_rec.latest_acceptable_date
739 ||'Promise Date : '||p_attributes_rec.promise_date
740 ||'Schedule Arr Date : '||p_attributes_rec.schedule_arrival_date
741 ||'Earliest Acceptable Date : '||p_attributes_rec.earliest_acceptable_date
742 ||'Earliest Ship Date : '||p_attributes_rec.earliest_ship_date
743 ||'Schedulde Ship Date : '||p_attributes_rec.date_scheduled
744 );
745 IF l_debug_on THEN
746 WSH_DEBUG_SV.log(l_module_name,'Error in calculating TP dates: Date Type : '||l_datetype
747 ||'Latest Acceptable Date : '||p_attributes_rec.latest_acceptable_date
748 ||'Promise Date : '||p_attributes_rec.promise_date
749 ||'Schedule Arr Date : '||p_attributes_rec.schedule_arrival_date
750 ||'Earliest Acceptable Date : '||p_attributes_rec.earliest_acceptable_date
751 ||'Earliest Ship Date : '||p_attributes_rec.earliest_ship_date
752 ||'Schedulde Ship Date : '||p_attributes_rec.date_scheduled
753 );
754 END IF;
755 ELSE
756
757 IF nvl(l_earliest_pickup_date,to_date('01/01/1970','mm/dd/yyyy'))
758 <> nvl(g_cache_detail_rec.earliest_pickup_date,to_date('01/01/1970','mm/dd/yyyy'))
759 OR nvl(l_latest_pickup_date,to_date('01/01/1970','mm/dd/yyyy'))
760 <> nvl(g_cache_detail_rec.latest_pickup_date,to_date('01/01/1970','mm/dd/yyyy'))
761 OR nvl(l_earliest_dropoff_date,to_date('01/01/1970','mm/dd/yyyy'))
762 <> nvl(g_cache_detail_rec.earliest_dropoff_date,to_date('01/01/1970','mm/dd/yyyy'))
763 OR nvl(l_latest_dropoff_date,to_date('01/01/1970','mm/dd/yyyy'))
764 <> nvl(g_cache_detail_rec.latest_dropoff_date,to_date('01/01/1970','mm/dd/yyyy'))
765 THEN
766
767 x_changed_detail.earliest_pickup_date := l_earliest_pickup_date;
768 x_changed_detail.latest_pickup_date := l_latest_pickup_date;
769 x_changed_detail.earliest_dropoff_date:= l_earliest_dropoff_date;
770 x_changed_detail.latest_dropoff_date := l_latest_dropoff_date;
771 END IF;
772 END IF;
773 END IF; -- g_cache_detail_rec.container_flag
774
775 --
776 IF l_debug_on THEN
777 WSH_DEBUG_SV.log(l_module_name,'x_update_allowed',x_update_allowed);
778 WSH_DEBUG_SV.pop(l_module_name);
779 END IF;
780 --
781 EXCEPTION
782
783 WHEN OTHERS THEN
784 -- close open cursors as needed
785 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
786 x_update_allowed := 'N';
787 WSH_UTIL_CORE.DEFAULT_HANDLER('WSH_USA_CATEGORIES_PVT.Change_TP_DATES',l_module_name);
788 --
789 IF l_debug_on THEN
790 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
791 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
792 END IF;
793 --
794 END Change_TP_Dates;
795
796
797 PROCEDURE Change_Quantity(
798 p_attributes_rec IN WSH_INTERFACE.ChangedAttributeRecType,
799 p_source_code IN VARCHAR2,
800 p_wms_flag IN VARCHAR2,
801 x_update_allowed IN OUT NOCOPY VARCHAR2,
802 x_return_status OUT NOCOPY VARCHAR2) IS
803
804 update_not_allowed EXCEPTION;
805
806 --
807 l_debug_on BOOLEAN;
808 --
809 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'CHANGE_QUANTITY';
810 --
811 BEGIN
812
813 --
814 --
815 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
816 --
817 IF l_debug_on IS NULL
818 THEN
819 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
820 END IF;
821 --
822 IF l_debug_on THEN
823 WSH_DEBUG_SV.push(l_module_name);
824 --
825 WSH_DEBUG_SV.log(l_module_name,'P_SOURCE_CODE',P_SOURCE_CODE);
826 WSH_DEBUG_SV.log(l_module_name,'P_WMS_FLAG',P_WMS_FLAG);
827 WSH_DEBUG_SV.log(l_module_name,'X_UPDATE_ALLOWED',X_UPDATE_ALLOWED);
828 WSH_DEBUG_SV.log(l_module_name,'p_attributes_rec.ship_from_org_id',p_attributes_rec.ship_from_org_id);
829 WSH_DEBUG_SV.log(l_module_name,'g_cache_detail_rec.organization_id',g_cache_detail_rec.organization_id);
830 WSH_DEBUG_SV.log(l_module_name,'p_attributes_rec.inventory_item_id ',p_attributes_rec.inventory_item_id );
831 END IF;
832 --bug#6407943
833 -- There is a possibility of having quantity change on delivery lines when there is a change in org
834 -- value on sales order line and item's primary uom is different in old and new orgs.
835 --BUG#11893915
836 IF ( (p_attributes_rec.ordered_quantity <> FND_API.G_MISS_NUM)
837 AND (p_attributes_rec.order_quantity_uom <> FND_API.G_MISS_CHAR))
838 OR
839 ( (p_attributes_rec.ship_from_org_id <> FND_API.G_MISS_NUM)
840 AND (p_attributes_rec.ship_from_org_id <> g_cache_detail_rec.organization_id)
841 AND ((p_attributes_rec.inventory_item_id = FND_API.G_MISS_NUM ) OR (p_attributes_rec.inventory_item_id = g_cache_detail_rec.inventory_item_id)) ) THEN
842
843 g_cache_detail_rec.cache_flag := 'N';
844 WSH_USA_QUANTITY_PVT.Update_Ordered_Quantity(
845 p_changed_attribute => p_attributes_rec,
846 p_source_code => p_source_code,
847 p_action_flag => 'U',
848 p_wms_flag => p_wms_flag,
849 x_return_status => x_return_status);
850
851
852 END IF;
853 --
854 IF l_debug_on THEN
855 WSH_DEBUG_SV.log(l_module_name,'x_update_allowed',x_update_allowed);
856 WSH_DEBUG_SV.pop(l_module_name);
857 END IF;
858 --
859 EXCEPTION
860
861 WHEN update_not_allowed THEN
862 x_update_allowed := 'N';
863 --
864 IF l_debug_on THEN
865 WSH_DEBUG_SV.logmsg(l_module_name,'UPDATE_NOT_ALLOWED exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
866 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:UPDATE_NOT_ALLOWED');
867 END IF;
868 --
869 RETURN;
870 WHEN OTHERS THEN
871 -- close open cursors as needed
872 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
873 x_update_allowed := 'N';
874 WSH_UTIL_CORE.DEFAULT_HANDLER('WSH_USA_CATEGORIES_PVT.Change_Quantity',l_module_name);
875 --
876 IF l_debug_on THEN
877 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
878 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
879 END IF;
880 --
881 END Change_Quantity;
882
883
884 PROCEDURE Change_Schedule(
885 p_attributes_rec IN WSH_INTERFACE.ChangedAttributeRecType,
886 p_source_code IN VARCHAR2,
887 p_wms_flag IN VARCHAR2,
888 x_update_allowed IN OUT NOCOPY VARCHAR2,
889 x_return_status OUT NOCOPY VARCHAR2)
890 IS
891 -- HW OPMCONV - Added Qty2
892 --bug# 6689448 (replenishment project): added wdd.replenishment_status
893 cursor c_delivery_detail is
894 select wdd.delivery_detail_id
895 , wdd.released_status
896 , wdd.move_order_line_id
897 , wdd.ship_from_location_id
898 , wdd.subinventory
899 , wda.parent_delivery_detail_id
900 , wda.delivery_id
901 , wdd.inventory_item_id
902 , wdd.serial_number
903 , wdd.transaction_temp_id
904 , wdd.requested_quantity
905 , wdd.picked_quantity
906 , wdd.requested_quantity2
907 , wdd.requested_quantity_uom2
908 , wdd.picked_quantity2
909 , wdd.net_weight
910 , wdd.gross_weight
911 , wdd.volume
912 , wdd.weight_uom_code
913 , wdd.volume_uom_code
914 , wdd.original_subinventory
915 , wdd.pickable_flag
916 , NVL(wnd.status_code, 'OP')
917 , NVL(wnd.planned_flag, 'N')
918 , NVL(wnd.tms_interface_flag, 'NS')
919 , wdd.replenishment_status
920 , wdd.client_id -- LSP PROJECT
921 from wsh_delivery_details wdd
922 , wsh_delivery_assignments wda
923 , wsh_new_deliveries wnd
924 where wdd.source_line_id = p_attributes_rec.source_line_id
925 and wdd.source_code = p_source_code
926 and wdd.delivery_detail_id = wda.delivery_detail_id
927 and wda.delivery_id = wnd.delivery_id(+)
928 order by decode(wdd.released_status, 'Y', 1, 2);
929
930 CURSOR c_del_status IS
931 SELECT wnd.status_code,wsp.export_screening_flag
932 FROM wsh_delivery_details wdd, wsh_delivery_assignments wda, wsh_new_deliveries wnd,
933 wsh_shipping_parameters wsp
934 WHERE wdd.source_code = p_source_code
935 AND wdd.source_line_id = p_attributes_rec.source_line_id
936 AND wdd.delivery_detail_id = wda.delivery_detail_id
937 AND wda.delivery_id = wnd.delivery_id
938 AND wnd.organization_id = wsp.organization_id
939 AND ( NVL(wnd.status_code, 'OP') IN ('SR','SA') OR
940 wsp.export_screening_flag IN ('C','A') );
941 CURSOR C_specific_item_info(c_p_inventory_item_id number, c_p_organization_id number) IS
942 SELECT description, hazard_class_id, primary_uom_code, weight_uom_code,
943 unit_weight, volume_uom_code, unit_volume , decode(mtl_transactions_enabled_flag,'Y','Y','N') pickable_flag
944 FROM mtl_system_items
945 WHERE inventory_item_id = c_p_inventory_item_id
946 AND organization_id = c_p_organization_id;
947
948 --Bug#8518110 : item sub. is enabled for backorder dds also
949 CURSOR c_det_status IS
950 SELECT 'N'
951 FROM wsh_delivery_details
952 WHERE source_line_id = p_attributes_rec.source_line_id
953 AND source_code = p_source_code
954 AND released_status IN ('S','Y','C');
955
956 CURSOR c_packed_det IS
957 SELECT 'N'
958 FROM wsh_delivery_details wdd, wsh_delivery_assignments wda
959 WHERE wdd.source_line_id = p_attributes_rec.source_line_id
960 AND wdd.source_code = p_source_code
961 AND wdd.released_status <> 'D'
962 AND wdd.delivery_detail_id = wda.delivery_detail_id
963 AND wda.parent_delivery_detail_id IS NOT NULL;
964
965 --bug#6407943: Begin
966 CURSOR C_item_details(c_organization_id NUMBER,c_item_id NUMBER) IS
967 SELECT primary_uom_code
968 from mtl_system_items
969 where inventory_item_id = c_item_id
970 and organization_id = c_organization_id ;
971 l_create_reservation VARCHAR2(1):='Y';
972 l_primary_uom VARCHAR2(3);
973 --bug#6407943: end
974
975 l_resv_tbl inv_reservation_global.mtl_reservation_tbl_type;
976 l_del_det NUMBER;
977 l_status VARCHAR2(1);
978 l_unsched_flag VARCHAR2(1);
979 l_mo_line NUMBER;
980 l_subinventory VARCHAR2(30);
981 l_orig_sub VARCHAR2(30);
982 l_location_id NUMBER;
983 l_parent_det NUMBER;
984 l_delivery NUMBER;
985 l_item_id NUMBER;
986 l_pickable_flag VARCHAR2(1);
987 l_reservable_flag VARCHAR2(1);
988 l_serial_number VARCHAR2(30);
989 l_trx_temp_id NUMBER;
990 l_inv_controls WSH_DELIVERY_DETAILS_INV.inv_control_flag_rec;
991 l_count NUMBER;
992 l_msg_count NUMBER;
993 l_msg_data VARCHAR2(2000);
994 l_reservation_id NUMBER;
995 l_qty_reserved NUMBER;
996 -- HW OPMCONV. Added l_qty2_reserved
997 l_qty2_reserved NUMBER;
998 l_change_sub_only VARCHAR2(1) := NULL;
999 l_change_resv_flag VARCHAR2(1);
1000 l_client_id NUMBER; -- LSP PROJECT
1001 update_not_allowed EXCEPTION;
1002 -- HW OPM for OM changes
1003 -- HW OPMCONV. Removed OPM variables
1004
1005 -- bug fix 2095105
1006 l_det_req_qty NUMBER;
1007 l_det_pic_qty NUMBER;
1008 -- HW OPMCONV - Added Qty2
1009 l_det_req_qty2 NUMBER;
1010 l_det_req_qty_uom2 VARCHAR2(3);
1011 l_det_pic_qty2 NUMBER;
1012 l_return_status VARCHAR2(30);
1013 -- bug fix 2095105
1014
1015 l_requested_quantity2 NUMBER := 0;
1016 l_requested_quantity_uom2 VARCHAR2(3);
1017
1018 --wrudge
1019 l_delete_dds WSH_UTIL_CORE.Id_Tab_Type ; -- to delete overpicked delivery lines
1020
1021 l_detail_tab WSH_UTIL_CORE.id_tab_type; -- DBI Project
1022 l_dbi_rs VARCHAR2(1); -- DBI Project
1023
1024 /* H projects: pricing integration csun */
1025 i NUMBER := 0;
1026 l_del_tab WSH_UTIL_CORE.Id_Tab_Type ; -- to mark reprice required flag
1027 mark_reprice_error EXCEPTION;
1028 -- deliveryMerge
1029 Adjust_Planned_Flag_Err EXCEPTION;
1030 l_num_warning NUMBER := 0;
1031 --
1032 -- begin item substitution project :bug#6077222
1033 l_change_item VARCHAR2(1) := 'N';
1034 l_export_screening_flag VARCHAR2(1);
1035 l_del_status VARCHAR2(2);
1036 l_del_planned_flag VARCHAR2(1);
1037 l_tms_interface_flag VARCHAR2(3);
1038 l_msg VARCHAR2(2000);
1039 l_exception_msg_count NUMBER;
1040 l_exception_msg_data VARCHAR2(2000);
1041 l_exception_return_status VARCHAR2(30);
1042 l_exception_location_id VARCHAR2(30);
1043 l_dummy_exception_id VARCHAR2(30);
1044 l_exception_error_message VARCHAR2(2000);
1045 l_shipping_param_info WSH_SHIPPING_PARAMS_PVT.Parameter_Rec_Typ;
1046 l_new_tot_requested_quantity NUMBER;
1047 l_old_tot_requested_quantity NUMBER;
1048 l_net_weight NUMBER;
1049 l_gross_weight NUMBER;
1050 l_volume NUMBER;
1051 l_new_weight NUMBER;
1052 l_new_volume NUMBER;
1053 l_weight_uom_code VARCHAR2(3);
1054 l_volume_uom_code VARCHAR2(3);
1055 l_del_planned_tab WSH_UTIL_CORE.ID_TAB_TYPE;
1056 l_otm_del_tab WSH_UTIL_CORE.ID_TAB_TYPE;
1057 l_delivery_id_tab WSH_UTIL_CORE.ID_TAB_TYPE;
1058 l_tms_interface_flag_tab WSH_UTIL_CORE.COLUMN_TAB_TYPE;
1059 l_item_rec c_specific_item_info%ROWTYPE;
1060 l_final_org_id NUMBER;
1061 l_ordered_quantity NUMBER;
1062 l_ordered_quantity_uom VARCHAR2(5);
1063 j NUMBER;
1064 item_update_not_allowed EXCEPTION;
1065 -- end item substitution project :bug#6077222
1066 --bug# 6689448 (replenishment project): begin
1067 l_change_replenish_status VARCHAR2(1) := 'N';
1068 l_sub_change VARCHAR2(1) := 'N';
1069 l_replenish_status VARCHAR2(1);
1070 --bug# 6689448 (replenishment project): end
1071
1072 --
1073 l_debug_on BOOLEAN;
1074 --
1075 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'CHANGE_SCHEDULE';
1076 --
1077 BEGIN
1078 --
1079 --
1080 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1081 --
1082 IF l_debug_on IS NULL
1083 THEN
1084 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1085 END IF;
1086 --
1087 IF l_debug_on THEN
1088 WSH_DEBUG_SV.push(l_module_name);
1089 --
1090 WSH_DEBUG_SV.log(l_module_name,'P_SOURCE_CODE',P_SOURCE_CODE);
1091 WSH_DEBUG_SV.log(l_module_name,'P_WMS_FLAG',P_WMS_FLAG);
1092 WSH_DEBUG_SV.log(l_module_name,'X_UPDATE_ALLOWED',X_UPDATE_ALLOWED);
1093 WSH_DEBUG_SV.logmsg(l_module_name, 'IN CHANGE_SCHEDULE : ' || P_ATTRIBUTES_REC.SHIP_FROM_ORG_ID || 'SUB ' || P_ATTRIBUTES_REC.SUBINVENTORY || ' SCHED DATE ' || P_ATTRIBUTES_REC.DATE_SCHEDULED );
1094 END IF;
1095 --
1096 SAVEPOINT before_changes;
1097 -- Bug 2114166 Do not perform actions if the line is getting cancelled.
1098
1099 IF p_attributes_rec.ordered_quantity = 0 THEN
1100 --
1101 IF l_debug_on THEN
1102 WSH_DEBUG_SV.pop(l_module_name,'line is being cancelled');
1103 END IF;
1104 --
1105 RETURN;
1106 END IF;
1107 -- HW OPM for OM changes
1108 --
1109 -- HW OPMCONV. Removed branching
1110
1111 -- HW end of OM changes
1112
1113 -- 2071048. ADDED the check condition g_cache_detail_rec.subinventory <> FND_API.G_MISS_CHAR to
1114 -- avoid looping through the delivery_details if the released_status of the lines is either
1115 -- 'B' or 'Y' or 'S'.
1116
1117 --bug# 6689448 (replenishment project): begin
1118 IF ( ( p_attributes_rec.subinventory <> FND_API.G_MISS_CHAR) OR (p_attributes_rec.subinventory IS NULL) )
1119 AND
1120 ( NVL(p_attributes_rec.subinventory, FND_API.G_MISS_CHAR) <> NVL(g_cache_detail_rec.subinventory, FND_API.G_MISS_CHAR )) THEN
1121 l_sub_change := 'Y';
1122 END IF;
1123 --bug# 6689448 (replenishment project): end
1124
1125 IF ((p_attributes_rec.ship_from_org_id <> FND_API.G_MISS_NUM) AND
1126 (p_attributes_rec.ship_from_org_id <> g_cache_detail_rec.organization_id))
1127 OR (p_attributes_rec.date_scheduled IS NULL AND g_cache_detail_rec.date_scheduled IS NOT NULL)
1128 THEN
1129
1130 l_change_sub_only := 'N';
1131
1132 ELSIF ( (l_sub_change = 'Y')
1133 OR
1134 ((p_attributes_rec.project_id <> FND_API.G_MISS_NUM) OR (p_attributes_rec.project_id IS NULL)) AND
1135 (NVL(p_attributes_rec.project_id, FND_API.G_MISS_NUM) <> NVL(g_cache_detail_rec.project_id, FND_API.G_MISS_NUM))
1136 OR
1137 ((p_attributes_rec.task_id <> FND_API.G_MISS_NUM) OR (p_attributes_rec.task_id IS NULL)) AND
1138 (NVL(p_attributes_rec.task_id, FND_API.G_MISS_NUM) <> NVL(g_cache_detail_rec.task_id, FND_API.G_MISS_NUM))
1139 )
1140 THEN
1141 l_change_sub_only := 'Y';
1142 END IF;
1143
1144 -- Item substitution project :bug#6077222
1145 --Begin.
1146 --1. Check whether item has been changed.
1147 IF l_debug_on THEN
1148 WSH_DEBUG_SV.logmsg(l_module_name, 'Old inventory_item_id '||g_cache_detail_rec.inventory_item_id ||
1149 ' New inventory_item_id '||p_attributes_rec.inventory_item_id);
1150 END IF;
1151 IF ( (p_attributes_rec.inventory_item_id <> FND_API.G_MISS_NUM)
1152 and(p_attributes_rec.inventory_item_id <> g_cache_detail_rec.inventory_item_id) ) THEN
1153 --{
1154
1155 l_change_item := 'Y';
1156
1157 --2.Check if detail is associated to OPM org - if so, do not allow update of item.
1158 --check for old org.
1159 IF INV_GMI_RSV_BRANCH.Process_Branch(p_organization_id => g_cache_detail_rec.organization_id) THEN
1160 --{
1161 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1162 IF l_debug_on THEN
1163 WSH_DEBUG_SV.logmsg(l_module_name, 'Item Update is not allowed as it is associated to OPM organization.');
1164 END IF;
1165 RAISE item_update_not_allowed;
1166 --}
1167 END IF;
1168 --check for new org.
1169 IF ( (p_attributes_rec.ship_from_org_id <> FND_API.G_MISS_NUM)
1170 AND (p_attributes_rec.ship_from_org_id <> g_cache_detail_rec.organization_id) ) THEN
1171 --{
1172 l_final_org_id:= p_attributes_rec.ship_from_org_id;
1173 IF INV_GMI_RSV_BRANCH.Process_Branch(p_organization_id => l_final_org_id) THEN
1174 --{
1175 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1176 IF l_debug_on THEN
1177 WSH_DEBUG_SV.logmsg(l_module_name, 'Item Update is not allowed as it is associated to OPM organization.');
1178 END IF;
1179 RAISE item_update_not_allowed;
1180 --}
1181 END IF;
1182 ELSE
1183 l_final_org_id:= g_cache_detail_rec.organization_id;
1184 --}
1185 END IF;
1186 --2. end.
1187
1188 --3.Check if detail exists with status other than 'R'/'N' - if so, do not allow update of item
1189 OPEN c_det_status;
1190 FETCH c_det_status INTO x_update_allowed;
1191 IF c_det_status%FOUND THEN
1192 CLOSE c_det_status;
1193 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1194 IF l_debug_on THEN
1195 WSH_DEBUG_SV.logmsg(l_module_name, 'Item Update is not allowed as there are Detail(s) that are already Pick Released ');
1196 END IF;
1197 RAISE item_update_not_allowed;
1198 END IF;
1199 CLOSE c_det_status;
1200
1201 --4. Check if detail is packed into a container - if so, do not allow update of item
1202 OPEN c_packed_det;
1203 FETCH c_packed_det INTO x_update_allowed;
1204 IF c_packed_det%FOUND THEN
1205 CLOSE c_packed_det;
1206 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1207 IF l_debug_on THEN
1208 WSH_DEBUG_SV.logmsg(l_module_name, 'Item Update is not allowed as there are Detail(s) that are packed into Containers ');
1209 END IF;
1210 RAISE item_update_not_allowed;
1211 END IF;
1212 CLOSE c_packed_det;
1213
1214 --5. TPW / Carrier Manifesting Orgs, do not allow update if the delivery is not in 'Open' status {
1215 OPEN c_del_status;
1216 FETCH c_del_status INTO l_del_status,l_export_screening_flag;
1217 IF c_del_status%FOUND THEN
1218 CLOSE c_del_status;
1219 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1220 IF l_debug_on THEN
1221 IF ( l_del_status in ('SA','SR') ) THEN
1222 WSH_DEBUG_SV.logmsg(l_module_name, 'Update is not allowed as there is a Delivery that belongs to tpw/carrier manifesting Organization with status '||l_del_status);
1223 END IF;
1224 IF ( l_export_screening_flag in ('C','A') ) THEN
1225 WSH_DEBUG_SV.logmsg(l_module_name, 'Update is not allowed as export compliance screening is enabled at delivery creation/delivery creation and ship confirm '||l_export_screening_flag);
1226 END IF;
1227 END IF;
1228 RAISE item_update_not_allowed;
1229 END IF;
1230 CLOSE c_del_status; --}
1231
1232 --6. Get Item specific info for new item.
1233 OPEN C_specific_item_info(p_attributes_rec.inventory_item_id, l_final_org_id);
1234 FETCH C_specific_item_info INTO l_item_rec;
1235 CLOSE C_specific_item_info;
1236
1237 --7. Check if the primary UOM of the new Item is different than the primary UOM of old Item - if so
1238 -- do not allow update of item.
1239 IF (g_cache_detail_rec.requested_quantity_uom <> l_item_rec.primary_uom_code ) THEN
1240 --{
1241 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1242 IF l_debug_on THEN
1243 WSH_DEBUG_SV.logmsg(l_module_name, 'Update is not allowed as there is a difference in primary UOM for old and new item');
1244 END IF;
1245 RAISE item_update_not_allowed;
1246 --}
1247 END IF;
1248
1249 --8. Check if UOM conversion (from order UOM to primary uom) is different. if so - do not allow update of item.
1250 IF ( (p_attributes_rec.src_requested_quantity <> FND_API.G_MISS_NUM)
1251 AND (p_attributes_rec.src_requested_quantity <> g_cache_detail_rec.src_requested_quantity) ) THEN
1252 l_ordered_quantity := p_attributes_rec.src_requested_quantity;
1253 ELSE
1254 l_ordered_quantity := g_cache_detail_rec.src_requested_quantity;
1255 END IF;
1256 IF ( (p_attributes_rec.src_requested_quantity_uom <> FND_API.G_MISS_CHAR)
1257 AND (p_attributes_rec.src_requested_quantity_uom <> g_cache_detail_rec.src_requested_quantity_uom) ) THEN
1258 l_ordered_quantity_uom := p_attributes_rec.src_requested_quantity_uom;
1259 ELSE
1260 l_ordered_quantity_uom := g_cache_detail_rec.src_requested_quantity_uom;
1261 END IF;
1262
1263 l_new_tot_requested_quantity := wsh_wv_utils.convert_uom(l_ordered_quantity_uom,
1264 l_item_rec.primary_uom_code,
1265 l_ordered_quantity,
1266 p_attributes_rec.inventory_item_id);
1267
1268 l_old_tot_requested_quantity := wsh_wv_utils.convert_uom(l_ordered_quantity_uom,
1269 l_item_rec.primary_uom_code,
1270 l_ordered_quantity,
1271 g_cache_detail_rec.inventory_item_id);
1272
1273 IF NVL(l_new_tot_requested_quantity, 0) <> nvl(l_old_tot_requested_quantity,0) THEN
1274 --{
1275 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1276 IF l_debug_on THEN
1277 WSH_DEBUG_SV.logmsg(l_module_name, 'Update is not allowed as there is a difference in UOM conversions for old and new item');
1278 END IF;
1279 RAISE item_update_not_allowed;
1280 --}
1281 END IF;
1282 --} Item substitute validations
1283 END IF;
1284
1285 IF l_debug_on THEN
1286 WSH_DEBUG_SV.logmsg(l_module_name, 'l_change_sub_only:'||l_change_sub_only||', l_change_item:'||l_change_item);
1287 END IF;
1288
1289 IF l_change_sub_only IS NOT NULL OR l_change_item = 'Y' THEN
1290 --{
1291
1292 g_cache_detail_rec.cache_flag := 'N';
1293
1294 --
1295 IF l_debug_on THEN
1296 WSH_DEBUG_SV.logmsg(l_module_name, 'IN L_CHANGE_SUB_ONLY IS NOT NULL' );
1297 END IF;
1298 /* moved to inside the loop
1299 --
1300 IF l_change_item = 'Y' THEN
1301 --{
1302 WSH_SHIPPING_PARAMS_PVT.Get(
1303 p_organization_id => l_final_org_id,
1304 x_param_info => l_shipping_param_info,
1305 x_return_status => x_return_status
1306 );
1307
1308 IF (x_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)) THEN
1309 --{
1310 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1311 IF l_debug_on THEN
1312 WSH_DEBUG_SV.logmsg(l_module_name, 'Update is not allowed as there are no shipping parameters defined for the org: '||l_final_org_id);
1313 END IF;
1314 RAISE item_update_not_allowed;
1315 --}
1316 END IF;
1317 IF l_debug_on THEN
1318 WSH_DEBUG_SV.logmsg(l_module_name, 'l_shipping_param_info.otm_enabled: '||l_shipping_param_info.otm_enabled);
1319 END IF;
1320 --}
1321 END IF;
1322 --
1323 */
1324 OPEN c_delivery_detail; -- cursor to get del det/status code from source line.
1325
1326 LOOP
1327
1328 FETCH c_delivery_detail
1329 INTO l_del_det
1330 , l_status
1331 , l_mo_line
1332 , l_location_id
1333 , l_subinventory
1334 , l_parent_det
1335 , l_delivery
1336 , l_item_id
1337 , l_serial_number
1338 , l_trx_temp_id
1339 -- HW OPMCONV - Added Qty2
1340 , l_det_req_qty
1341 , l_det_pic_qty
1342 , l_det_req_qty2
1343 , l_det_req_qty_uom2
1344 , l_det_pic_qty2
1345 , l_net_weight
1346 , l_gross_weight
1347 , l_volume
1348 , l_weight_uom_code
1349 , l_volume_uom_code
1350 , l_orig_sub
1351 , l_pickable_flag
1352 , l_del_status
1353 , l_del_planned_flag
1354 , l_tms_interface_flag
1355 , l_replenish_status
1356 , l_client_id ; -- LSP PROJECT
1357
1358 EXIT WHEN c_delivery_detail%NOTFOUND;
1359 /* H Projects: Pricing integration csun */
1360 -- LSP PROJECT : Begin.
1361 --
1362 IF l_change_item = 'Y' THEN
1363 --{
1364 WSH_SHIPPING_PARAMS_PVT.Get(
1365 p_organization_id => l_final_org_id,
1366 p_client_id => l_client_id, -- LSP PROJECT.
1367 x_param_info => l_shipping_param_info,
1368 x_return_status => x_return_status
1369 );
1370
1371 IF (x_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)) THEN
1372 --{
1373 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1374 IF l_debug_on THEN
1375 WSH_DEBUG_SV.logmsg(l_module_name, 'Update is not allowed as there are no shipping parameters defined for the org: '||l_final_org_id);
1376 END IF;
1377 RAISE item_update_not_allowed;
1378 --}
1379 END IF;
1380 IF l_debug_on THEN
1381 WSH_DEBUG_SV.logmsg(l_module_name, 'l_shipping_param_info.otm_enabled: '||l_shipping_param_info.otm_enabled);
1382 END IF;
1383 --}
1384 END IF;
1385 --
1386 IF l_delivery is NOT NULL THEN
1387 i := i + 1;
1388 l_del_tab(i) := l_delivery;
1389 --
1390 IF l_debug_on THEN
1391 WSH_DEBUG_SV.logmsg(l_module_name, 'ADDING DELIVERY '|| L_DELIVERY || 'TO MARK LIST'||',l_del_planned_flag: '||l_del_planned_flag||',l_tms_interface_flag: '||l_tms_interface_flag);
1392 END IF;
1393 --
1394
1395 IF l_change_item = 'Y' THEN
1396 --{
1397 -- Check for firmed deliveries
1398 IF l_del_planned_flag IN ('Y','F') AND (NOT l_del_planned_tab.exists(l_delivery)) THEN
1399 l_del_planned_tab(l_delivery) := l_delivery;
1400 END IF;
1401 -- Check for tms_interface_flag for OTM deliveries that need to be updated to 'UR'
1402 IF l_shipping_param_info.otm_enabled = 'Y' AND (NOT l_otm_del_tab.exists(l_delivery))
1403 AND l_tms_interface_flag in ('CP','UP','AW','AR') THEN
1404 l_otm_del_tab(l_delivery) := l_delivery;
1405 END IF;
1406 --}
1407 END IF;
1408 --} delivery check
1409 END IF;
1410
1411 ---subinventory specific validations
1412 IF ( l_change_sub_only IS NOT NULL ) THEN
1413 --{
1414 IF l_status = 'Y' AND p_wms_flag = 'Y' THEN
1415 IF l_debug_on THEN
1416 WSH_DEBUG_SV.log(l_module_name,'l_status is set to Y');
1417 END IF;
1418 FND_MESSAGE.SET_NAME('WSH', 'WSH_WMS_UPDATE_NOT_ALLOWED');
1419 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1420 WSH_UTIL_CORE.Add_Message(x_return_status,l_module_name);
1421 RAISE update_not_allowed;
1422 exit; -- exit loop if action breaks WMS.
1423 -- how does it come to exit,when we raise update_not_allowed?
1424 END IF;
1425 --
1426
1427 IF l_change_sub_only = 'Y' THEN
1428 --
1429 IF l_debug_on THEN
1430 WSH_DEBUG_SV.logmsg(l_module_name, 'IN L_CHANGE_SUB_ONLY IS: '||L_CHANGE_SUB_ONLY );
1431 END IF;
1432 --
1433 IF l_status NOT IN ('S','Y') THEN
1434
1435 l_change_sub_only := 'X';
1436
1437 ELSE
1438 l_reservable_flag := WSH_DELIVERY_DETAILS_INV.get_reservable_flag(
1439 x_item_id => l_item_id,
1440 x_organization_id => g_cache_detail_rec.organization_id,
1441 x_pickable_flag => l_pickable_flag);
1442
1443 IF l_reservable_flag = 'N'
1444 AND
1445 (NVL(l_orig_sub, FND_API.G_MISS_CHAR) <> NVL(p_attributes_rec.subinventory, FND_API.G_MISS_CHAR))
1446 AND
1447 (l_status = 'Y' OR (l_status = 'S' AND l_pickable_flag = 'N'))
1448 THEN
1449
1450 l_change_sub_only := 'X';
1451
1452 END IF;
1453
1454 END IF;
1455
1456 END IF;
1457
1458 END IF ;
1459
1460 -- Here, we set l_change_sub_only to 'X' only if we do need to change the subinventory.
1461 -- if l_change_sub_only remains at 'Y' we do not go ahead with the changes.
1462 --
1463 IF l_debug_on THEN
1464 WSH_DEBUG_SV.logmsg(l_module_name, 'IN L_CHANGE_SUB_ONLY IS: '||L_CHANGE_SUB_ONLY );
1465 END IF;
1466 --
1467 IF l_change_sub_only IN ('N', 'X') OR l_change_item = 'Y' THEN
1468
1469 --
1470 IF l_change_sub_only IN ('N', 'X') THEN
1471 --
1472 IF l_debug_on THEN
1473 WSH_DEBUG_SV.logmsg(l_module_name, 'IN CHANGE_SCHEDULE DOING ACTIONS' );
1474 END IF;
1475 l_change_resv_flag := 'Y';
1476
1477 END IF ; --Bug 6732141
1478 --
1479 --wrudge
1480 IF (l_det_req_qty = 0) THEN
1481
1482 -- get this overpicked detail deleted
1483 -- because we can't have req qty = 0 with status 'R'
1484 l_delete_dds( l_delete_dds.count+1 ) := l_del_det;
1485
1486 ELSE
1487
1488 -- subinventory specific validations.
1489 IF ( l_change_sub_only IN ('N', 'X') ) THEN
1490 --{
1491
1492 IF (l_status = 'S') THEN
1493 -- HW OPM code for OM changes. Need to branch
1494 -- HW OPMCONV. Removed branching
1495 --
1496 IF l_debug_on THEN
1497 WSH_DEBUG_SV.log(l_module_name,'l_del_det',l_del_det);
1498 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit INV_MO_CANCEL_PVT.CANCEL_MOVE_ORDER_LINE',WSH_DEBUG_SV.C_PROC_LEVEL);
1499 END IF;
1500 --
1501 INV_MO_Cancel_PVT.Cancel_Move_Order_Line(
1502 p_line_id => l_mo_line,
1503 p_delete_reservations => 'N',
1504 p_txn_source_line_id => p_attributes_rec.source_line_id,
1505 p_delivery_detail_id => l_del_det, -- X-dock
1506 x_return_status => x_return_status,
1507 x_msg_count => l_msg_count,
1508 x_msg_data => l_msg_data
1509 );
1510
1511 IF (x_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)) THEN
1512 IF l_debug_on THEN
1513 WSH_DEBUG_SV.log(l_module_name,'Cancel_Move_Order_Line returned error');
1514 END IF;
1515 RAISE update_not_allowed;
1516 END IF;
1517
1518 END IF; --subinventory specific validation
1519 -- HW OPMCONV. Removed branching
1520 -- OPM org
1521 --wrudge
1522 -- lines released to warehouse or staged need to be unassigned.
1523 -- unreleased lines can stay in their deliveries.
1524 IF l_status IN ('S', 'Y') THEN
1525
1526 IF l_delivery IS NOT NULL THEN
1527 WSH_DELIVERY_DETAILS_ACTIONS.Unassign_Detail_from_Delivery(
1528 p_detail_id => l_del_det,
1529 p_validate_flag => 'N',
1530 x_return_status => x_return_status);
1531
1532 IF (x_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)) THEN
1533
1534 RAISE update_not_allowed;
1535
1536 END IF;
1537
1538 Log_Exception(
1539 p_delivery_detail_id => l_del_det,
1540 p_location_id => l_location_id,
1541 p_exception_name => 'WSH_CHANGE_SCHEDULE',
1542 p_entity_name => 'WSH_DELIVERY',
1543 p_entity_id => l_delivery,
1544 x_return_status => x_return_status);
1545
1546 IF (x_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)) THEN
1547
1548 RAISE update_not_allowed;
1549
1550 END IF;
1551
1552 END IF;
1553
1554 IF l_parent_det IS NOT NULL THEN
1555 WSH_DELIVERY_DETAILS_ACTIONS.Unassign_Detail_from_Cont(
1556 p_detail_id => l_del_det,
1557 p_validate_flag => 'N',
1558 x_return_status => x_return_status);
1559
1560 IF (x_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)) THEN
1561
1562 RAISE update_not_allowed;
1563
1564 END IF;
1565
1566 Log_Exception(
1567 p_delivery_detail_id => l_del_det,
1568 p_location_id => l_location_id,
1569 p_exception_name => 'WSH_CHANGE_SCHEDULE',
1570 p_entity_name => 'WSH_CONTAINER',
1571 p_entity_id => l_parent_det,
1572 x_return_status => x_return_status);
1573
1574 IF (x_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)) THEN
1575
1576 RAISE update_not_allowed;
1577
1578 END IF;
1579
1580 END IF;
1581
1582 END IF; --l_status IN ('S', 'Y')
1583
1584 IF (l_serial_number IS NOT NULL) OR (l_trx_temp_id IS NOT NULL) THEN
1585
1586 WSH_DELIVERY_DETAILS_INV.Fetch_Inv_Controls(
1587 p_delivery_detail_id => l_del_det,
1588 p_inventory_item_id => l_item_id,
1589 p_organization_id => g_cache_detail_rec.organization_id,
1590 p_subinventory => l_subinventory,
1591 x_inv_controls_rec => l_inv_controls,
1592 x_return_status => x_return_status);
1593
1594 IF (x_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)) THEN
1595
1596 RAISE update_not_allowed;
1597
1598 END IF;
1599 WSH_DELIVERY_DETAILS_INV.Unmark_Serial_Number (
1600 p_delivery_detail_id => l_del_det,
1601 p_serial_number_code => l_inv_controls.serial_code,
1602 p_serial_number => l_serial_number,
1603 p_transaction_temp_id => l_trx_temp_id,
1604 x_return_status => x_return_status);
1605
1606 IF (x_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)) THEN
1607
1608 RAISE update_not_allowed;
1609
1610 END IF;
1611
1612 END IF; -- IF l_serial_number IS NOT NULL
1613
1614 -- bug fix 2095105
1615 IF l_debug_on THEN
1616 WSH_DEBUG_SV.logmsg(l_module_name, 'DET PICKED QTY = ' || L_DET_PIC_QTY );
1617 WSH_DEBUG_SV.logmsg(l_module_name, 'DET REQ QTY = ' || L_DET_REQ_QTY );
1618 -- HW OPMCONV -Print Qty2
1619 WSH_DEBUG_SV.logmsg(l_module_name, 'DET PICKED QTY2 = ' || L_DET_PIC_QTY2 );
1620 WSH_DEBUG_SV.logmsg(l_module_name, 'DET REQ QTY2 = ' || L_DET_REQ_QTY2 );
1621 END IF;
1622 ----
1623
1624 IF ( l_det_pic_qty > l_det_req_qty) THEN
1625 IF l_debug_on THEN
1626 WSH_DEBUG_SV.logmsg(l_module_name, 'DET PICKED QTY AGAIN = ' || L_DET_PIC_QTY );
1627 END IF;
1628
1629 -- HW OPMCONV - Pass Qty2
1630 WSH_DELIVERY_DETAILS_ACTIONS.Unreserve_delivery_detail
1631 (
1632 p_delivery_detail_id => l_del_det,
1633 p_quantity_to_unreserve => (l_det_pic_qty - l_det_req_qty),
1634 p_quantity2_to_unreserve => (l_det_pic_qty2 - l_det_req_qty2),
1635 p_unreserve_mode => 'UNRESERVE',
1636 x_return_status => l_return_status
1637 );
1638
1639 IF (l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)) THEN
1640 RAISE update_not_allowed;
1641 END IF;
1642 END IF;
1643 --} Subinventory specific code.
1644 END IF;
1645
1646 --bug# 6719369 (replenishment project) : Need to change the status of replenishment requested/replenishment completed
1647 -- delivery details back to original status when there is change in warehouse/subinventory/item/scheduled to NULL on the sales order line.
1648 IF ( (l_sub_change = 'Y' OR l_change_item = 'Y' OR l_change_sub_only = 'N' ) AND ( l_replenish_status IS NOT NULL)
1649 AND (l_status in ('R','B')) ) THEN
1650 --{
1651 l_change_replenish_status := 'Y';
1652 IF ( l_replenish_status = 'R' ) THEN
1653 --{
1654 IF l_debug_on THEN
1655 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WMS_REPLENISHMENT_PUB.UPDATE_DELIVERY_DETAIL' ,WSH_DEBUG_SV.C_PROC_LEVEL);
1656 END IF;
1657 WMS_REPLENISHMENT_PUB.UPDATE_DELIVERY_DETAIL(
1658 p_delivery_detail_id => l_del_det,
1659 p_primary_quantity => 0, --- WMS will delete the records from WMS table.
1660 x_return_status => x_return_status);
1661 IF x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
1662 --{
1663 IF l_debug_on THEN
1664 WSH_DEBUG_SV.logmsg(l_module_name, 'UNEXPECTED ERROR FROM WMS_REPLENISHMENT_PUB.UPDATE_DELIVERY_DETAIL');
1665 WSH_DEBUG_SV.pop(l_module_name);
1666 END IF;
1667 RETURN;
1668 --}
1669 END IF;
1670 --}
1671 END IF;
1672 ELSE
1673 l_change_replenish_status := 'N';
1674 --}
1675 END IF;
1676 --bug# 6689448 (replenishment project): end
1677
1678 -- Calculate new weight and new volume for each detail
1679 IF l_change_item = 'Y' THEN
1680 --{ item specific code
1681 -- revert old weight and volume information from delivery.
1682 IF l_delivery IS NOT NULL THEN
1683 --{
1684 IF l_debug_on THEN
1685 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_WV_UTILS.DD_WV_Post_Process',WSH_DEBUG_SV.C_PROC_LEVEL);
1686 END IF;
1687 WSH_WV_UTILS.DD_WV_Post_Process(
1688 p_delivery_detail_id => l_del_det,
1689 p_diff_gross_wt => -1 * l_gross_weight,
1690 p_diff_net_wt => -1 * l_net_weight,
1691 p_diff_volume => -1 * l_volume,
1692 p_diff_fill_volume => -1 * l_volume,
1693 x_return_status => l_return_status);
1694 IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
1695 x_return_status := l_return_status;
1696 IF l_debug_on THEN
1697 WSH_DEBUG_SV.log(l_module_name,'Return Status after DD_WV_Post_Process',x_return_status);
1698 END IF;
1699 RAISE item_update_not_allowed;
1700 END IF;
1701 --} delivery is not null.
1702 END IF;
1703 -- calculate new item wieght and volume based on the substite item.
1704 l_new_weight := l_item_rec.unit_weight * l_det_req_qty;
1705 l_new_volume := l_item_rec.unit_volume * l_det_req_qty;
1706
1707 --} item specific code
1708 END IF;
1709
1710 -- bug fix 2095105
1711 --
1712
1713 IF l_debug_on THEN
1714 WSH_DEBUG_SV.logmsg(l_module_name, 'In change_schedule updating wdd' );
1715 WSH_DEBUG_SV.logmsg(l_module_name, 'ordered_quantity2 = '||p_attributes_rec.ordered_quantity2 );
1716 WSH_DEBUG_SV.logmsg(l_module_name, 'ordered_quantity_uom2 = '||p_attributes_rec.ordered_quantity_uom2 );
1717 WSH_DEBUG_SV.logmsg(l_module_name, 'l_det_req_qty2 = '||l_det_req_qty2 );
1718 WSH_DEBUG_SV.logmsg(l_module_name, 'l_det_req_qty_uom2 = '||l_det_req_qty_uom2 );
1719 END IF;
1720 --
1721 -- HW OPM for OM changes. Added preferred_grade
1722 --HW OPMCONV -Added Qty2s
1723
1724 -- bugfix 13392065 start
1725 -- here in the if condition I am handling those cases where there is a change in org from A to B in order line
1726 -- and the item is defined as dual in A but non dual in B
1727 -- we might need to revist the case where there is a change in org from B and
1728 -- item is defined as non dual in B but dual in A and there are multiple wdd for 1 so line.
1729 -- For all other cases we are keeping the requested_quantity2/requested_quantity_uom2 as is.
1730 IF (NVL(l_det_req_qty2,0) = 0 AND NVL(p_attributes_rec.ordered_quantity2,0) >0) OR
1731 (NVL(l_det_req_qty2,0) > 0 AND NVL(p_attributes_rec.ordered_quantity2,0) = 0)
1732 THEN
1733 l_requested_quantity2 := p_attributes_rec.ordered_quantity2;
1734 l_requested_quantity_uom2 := p_attributes_rec.ordered_quantity_uom2;
1735 ELSE
1736 l_requested_quantity2 := l_det_req_qty2;
1737 l_requested_quantity_uom2 := l_det_req_qty_uom2;
1738 END IF;
1739
1740 -- bugfix 13392065 end
1741 IF l_debug_on THEN
1742 WSH_DEBUG_SV.logmsg(l_module_name, 'l_requested_quantity2 = '||l_requested_quantity2 );
1743 WSH_DEBUG_SV.logmsg(l_module_name, 'l_requested_quantity_uom2 = '||l_requested_quantity_uom2 );
1744 END IF;
1745
1746
1747 UPDATE wsh_delivery_details
1748 SET subinventory = NULL
1749 , lot_number = NULL
1750 , locator_id = NULL
1751 , revision = NULL
1752 , move_order_line_id = NULL
1753 -- HW OPMCONV. No need for sublot anymore
1754 -- , sublot_number = NULL
1755 , preferred_grade = NULL
1756 , shipped_quantity = NULL
1757 , cycle_count_quantity = NULL
1758 , shipped_quantity2 = NULL
1759 , cycle_count_quantity2 = NULL
1760 , picked_quantity = NULL
1761 , picked_quantity2 = NULL
1762 , serial_number = NULL
1763 , transaction_temp_id = NULL
1764 , batch_id = NULL
1765 , transaction_id = NULL --- 2803570
1766 , inventory_item_id = DECODE(l_change_item, 'Y', p_attributes_rec.inventory_item_id, inventory_item_id)
1767 , item_description = DECODE(l_change_item, 'Y', l_item_rec.description, item_description)
1768 , unit_weight = DECODE(l_change_item, 'Y', l_item_rec.unit_weight, unit_weight)
1769 , weight_uom_code = DECODE(l_change_item, 'Y', l_item_rec.weight_uom_code, weight_uom_code)
1770 , net_weight = DECODE(l_change_item, 'Y', l_new_weight, net_weight)
1771 , gross_weight = DECODE(l_change_item, 'Y', l_new_weight, gross_weight)
1772 , unit_volume = DECODE(l_change_item, 'Y', l_item_rec.unit_volume, unit_volume)
1773 , volume_uom_code = DECODE(l_change_item, 'Y', l_item_rec.volume_uom_code, volume_uom_code)
1774 , volume = DECODE(l_change_item, 'Y', l_new_volume, volume)
1775 , wv_frozen_flag = DECODE(l_change_item, 'Y', 'N', wv_frozen_flag)
1776 , pickable_flag = DECODE(l_change_item, 'Y', l_item_rec.pickable_flag, pickable_flag)
1777 , hazard_class_id = DECODE(l_change_item, 'Y', l_item_rec.hazard_class_id, hazard_class_id)
1778 , released_status = DECODE(released_status,'X', DECODE(l_change_item, 'Y', DECODE(l_item_rec.pickable_flag, 'N', 'X', 'R'), 'X'),
1779 'B', 'B', DECODE(l_change_item, 'Y', DECODE(l_item_rec.pickable_flag, 'N', 'X', 'R'), 'R'))
1780 , inv_interfaced_flag = DECODE(l_change_item, 'Y', DECODE(l_item_rec.pickable_flag, 'N','X','N'), inv_interfaced_flag)
1781 , requested_quantity2 = l_requested_quantity2
1782 , requested_quantity_uom2= l_requested_quantity_uom2
1783 , replenishment_status = decode(l_change_replenish_status,'Y',NULL,replenishment_status) ----bug# 6689448 (replenishment project)
1784 WHERE delivery_detail_id = l_del_det;
1785
1786 l_detail_tab(l_detail_tab.count+1) := l_del_det ; -- added for DBI Project
1787
1788 IF l_debug_on THEN
1789 WSH_DEBUG_SV.log(l_module_name,'Rows updated:',SQL%ROWCOUNT);
1790 END IF;
1791
1792 IF l_change_item = 'Y' and l_delivery IS NOT NULL THEN --{
1793 -- Propagate new weight and volume for UOMs change
1794 IF l_debug_on THEN
1795 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_WV_UTILS.DD_WV_Post_Process',WSH_DEBUG_SV.C_PROC_LEVEL);
1796 END IF;
1797 WSH_WV_UTILS.DD_WV_Post_Process(
1798 p_delivery_detail_id => l_del_det,
1799 p_diff_gross_wt => l_new_weight,
1800 p_diff_net_wt => l_new_weight,
1801 p_diff_volume => l_new_volume,
1802 p_diff_fill_volume => l_new_volume,
1803 x_return_status => l_return_status);
1804 IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
1805 x_return_status := l_return_status;
1806 IF l_debug_on THEN
1807 WSH_DEBUG_SV.log(l_module_name,'Return Status after DD_WV_Post_Process',x_return_status);
1808 END IF;
1809 RAISE item_update_not_allowed;
1810 END IF;
1811 --} Weight / Volume Propagation for Item change
1812 END IF;
1813
1814 END IF; -- (l_det_req_qty = 0)
1815
1816 END IF; -- l_change_sub_only in ('N','X') OR item_change = 'Y'
1817
1818 END LOOP;
1819
1820 CLOSE c_delivery_detail;
1821 --
1822 -- DBI Project
1823 -- Update of wsh_delivery_details where released_status
1824 -- are changed, call DBI API after the update.
1825 -- This API will also check for DBI Installed or not
1826 IF l_debug_on THEN
1827 WSH_DEBUG_SV.log(l_module_name,'Calling DBI API. delivery details l_detail_tab count',l_detail_tab.count);
1828 END IF;
1829 WSH_INTEGRATION.DBI_Update_Detail_Log
1830 (p_delivery_detail_id_tab => l_detail_tab,
1831 p_dml_type => 'UPDATE',
1832 x_return_status => l_dbi_rs);
1833
1834 IF l_debug_on THEN
1835 WSH_DEBUG_SV.log(l_module_name,'Return Status after DBI Call-',l_dbi_rs);
1836 END IF;
1837 IF l_dbi_rs = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR THEN
1838 x_return_status := l_dbi_rs;
1839 Rollback to before_changes;
1840 -- just pass this return status to caller API
1841 IF l_debug_on THEN
1842 WSH_DEBUG_SV.logmsg(l_module_name,'DBI API Returned Unexpected error '||x_return_status);
1843 WSH_DEBUG_SV.pop(l_module_name);
1844 END IF;
1845 return;
1846 END IF;
1847 -- End of Code for DBI Project
1848 --
1849 --wrudge
1850
1851 --wrudge
1852
1853 -- purge overpicked delivery details
1854 IF l_delete_dds.count > 0 THEN
1855 WSH_INTERFACE.Delete_Details(
1856 p_details_id => l_delete_dds,
1857 x_return_status => x_return_status
1858 );
1859
1860 IF (x_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)) THEN
1861 RAISE update_not_allowed;
1862 END IF;
1863 END IF;
1864 --brana
1865 IF l_change_item = 'Y' THEN
1866 --{
1867 IF l_debug_on THEN
1868 WSH_DEBUG_SV.log(l_module_name,'l_del_planned_tab count',l_del_planned_tab.count);
1869 END IF;
1870 --Intialize the message and location id
1871 IF ( l_del_planned_tab.count > 0) THEN
1872 --{
1873 FND_MESSAGE.SET_NAME('WSH','WSH_ITEM_SUBSTITUTED');
1874 FND_MESSAGE.SET_TOKEN('ITEM1',WSH_UTIL_CORE.Get_Item_Name(p_attributes_rec.inventory_item_id,
1875 l_final_org_id));
1876 FND_MESSAGE.SET_TOKEN('ITEM2',WSH_UTIL_CORE.Get_Item_Name(g_cache_detail_rec.inventory_item_id,
1877 l_final_org_id));
1878 l_msg := FND_MESSAGE.GET;
1879 l_exception_location_id := l_location_id;
1880 --}
1881 END IF;
1882 i := l_del_planned_tab.FIRST;
1883 WHILE i is not null LOOP
1884 --{
1885 IF l_debug_on THEN
1886 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_XC_UTIL.LOG_EXCEPTION for planned delivery '||l_del_planned_tab(i),WSH_DEBUG_SV.C_PROC_LEVEL);
1887 END IF;
1888 WSH_XC_UTIL.Log_Exception(
1889 p_api_version => 1.0,
1890 x_return_status => l_exception_return_status,
1891 x_msg_count => l_exception_msg_count,
1892 x_msg_data => l_exception_msg_data,
1893 x_exception_id => l_dummy_exception_id ,
1894 p_logged_at_location_id => l_exception_location_id,
1895 p_exception_location_id => l_exception_location_id,
1896 p_logging_entity => 'SHIPPER',
1897 p_logging_entity_id => FND_GLOBAL.USER_ID,
1898 p_exception_name => 'WSH_ITEM_SUBSTITUTED',
1899 p_message => l_msg,
1900 p_delivery_id => l_del_planned_tab(i),
1901 p_error_message => l_exception_error_message);
1902 i := l_del_planned_tab.NEXT(i);
1903 IF l_debug_on THEN
1904 WSH_DEBUG_SV.log(l_module_name,'log_exception l_exception_return_status',l_exception_return_status);
1905 END IF;
1906 --}
1907 END LOOP;
1908
1909 -- OTM Deliveries Mark for Update
1910 IF l_debug_on THEN
1911 WSH_DEBUG_SV.log(l_module_name,'l_otm_del_tab count',l_otm_del_tab.count);
1912 END IF;
1913 i := l_otm_del_tab.FIRST;
1914 j := 0;
1915 WHILE i is not null LOOP --{
1916 j := j + 1;
1917 l_delivery_id_tab(j) := l_otm_del_tab(i);
1918 l_tms_interface_flag_tab(j) := 'UR';
1919 i := l_otm_del_tab.NEXT(i);
1920 END LOOP;
1921 IF l_delivery_id_tab.FIRST IS NOT NULL THEN
1922 IF l_debug_on THEN
1923 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_NEW_DELIVERIES_PVT.UPDATE_TMS_INTERFACE_FLAG',WSH_DEBUG_SV.C_PROC_LEVEL);
1924 END IF;
1925 WSH_NEW_DELIVERIES_PVT.Update_TMS_Interface_Flag
1926 (p_delivery_id_tab => l_delivery_id_tab,
1927 p_tms_interface_flag_tab => l_tms_interface_flag_tab,
1928 x_return_status => l_return_status);
1929 IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
1930 x_return_status := l_return_status;
1931 IF l_debug_on THEN
1932 WSH_DEBUG_SV.log(l_module_name,'Return Status from WSH_NEW_DELIVERIES_PVT.Update_Tms_interface_flag',l_return_status);
1933 END IF;
1934 RAISE item_update_not_allowed;
1935 END IF;
1936 END IF; --}
1937 END IF; --}
1938
1939 IF l_change_resv_flag = 'Y' THEN
1940
1941 -- Looks like we have to query resv. all the time since delete resv. needs the table of recs.
1942
1943 -- R12 X-dock, added parameter p_delivery_detail_id
1944 -- this code is called only when l_change_resv_flag = Y
1945 -- when this flag is Y either l_delete_dds is populated or cancel_MOL api is called
1946 -- As stated above, this call is made primarily to delete_reservations
1947 -- Above, we have call to INV_MO_Cancel_PVT.Cancel_Move_Order_Line for released_status of 'S'
1948 -- this takes care of the reservations, hence pass null for delivery_detail_id
1949 WSH_USA_INV_PVT.query_reservations(
1950 p_source_code => p_source_code,
1951 p_source_header_id => p_attributes_rec.source_header_id,
1952 p_source_line_id => p_attributes_rec.source_line_id,
1953 p_organization_id => g_cache_detail_rec.organization_id,
1954 p_delivery_detail_id => null, -- X-dock
1955 x_mtl_reservation_tbl => l_resv_tbl,
1956 x_mtl_reservation_tbl_count => l_count,
1957 x_return_status => x_return_status);
1958
1959 IF (x_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)) THEN
1960 RAISE update_not_allowed;
1961 END IF;
1962
1963 --END IF;
1964 IF l_debug_on THEN
1965 WSH_DEBUG_SV.logmsg(l_module_name, 'IN L_CHANGE_SUB_ONLY IS: '||L_CHANGE_SUB_ONLY );
1966 END IF;
1967 --
1968 --bug#6407943: begin.
1969 --Creation of reservations should be stopped when
1970 --item's primary uom value is different in new organization.
1971 --BUG#11893915
1972 IF (l_count > 0) THEN
1973 --{
1974 IF ( (p_attributes_rec.ship_from_org_id <> FND_API.G_MISS_NUM)
1975 AND (p_attributes_rec.ship_from_org_id <> g_cache_detail_rec.organization_id) )
1976 and ((p_attributes_rec.inventory_item_id = FND_API.G_MISS_NUM ) or
1977 (p_attributes_rec.inventory_item_id = g_cache_detail_rec.inventory_item_id) )THEN
1978 --{
1979 OPEN C_item_details(p_attributes_rec.ship_from_org_id,g_cache_detail_rec.inventory_item_id);
1980 FETCH C_item_details INTO l_primary_uom;
1981 CLOSE C_item_details;
1982 IF l_debug_on THEN
1983 WSH_DEBUG_SV.log(l_module_name,'new primary uom',l_primary_uom);
1984 WSH_DEBUG_SV.log(l_module_name,'old primary uom',g_cache_detail_rec.requested_quantity_uom);
1985 END IF;
1986 IF (l_primary_uom <> g_cache_detail_rec.requested_quantity_uom) THEN
1987 l_create_reservation := 'N';
1988 END IF;
1989 --}
1990 end if;
1991 --}
1992 END IF;
1993 --bug#6407943. end
1994 --
1995 FOR i IN 1.. l_count LOOP
1996
1997 WSH_USA_INV_PVT.delete_reservation (
1998 p_query_input => l_resv_tbl(i),
1999 x_return_status => x_return_status);
2000
2001 IF (x_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)) THEN
2002
2003 RAISE update_not_allowed;
2004
2005 END IF;
2006
2007
2008 IF p_attributes_rec.date_scheduled IS NOT NULL AND l_create_reservation = 'Y' THEN --bug#6407943
2009
2010 --oe_debug_pub.add('In change_schedule : unsched_flag '||l_unsched_flag, 2 );
2011 IF l_change_sub_only = 'N' THEN
2012 IF l_debug_on THEN
2013 WSH_DEBUG_SV.logmsg(l_module_name, 'IN L_CHANGE_SUB_ONLY IS: '||L_CHANGE_SUB_ONLY );
2014 WSH_DEBUG_SV.logmsg(l_module_name, 'NEW ORG: '||P_ATTRIBUTES_REC.SHIP_FROM_ORG_ID );
2015 WSH_DEBUG_SV.logmsg(l_module_name, 'NEW SUB: '||P_ATTRIBUTES_REC.SUBINVENTORY );
2016 END IF;
2017 --
2018 -- HW OPMCONV - Print values
2019 IF l_debug_on THEN
2020
2021 WSH_DEBUG_SV.logmsg(l_module_name, 'p_attributes_rec.ordered_quantity: '||p_attributes_rec.ordered_quantity );
2022 WSH_DEBUG_SV.logmsg(l_module_name, 'p_attributes_rec.ordered_quantity2: '||p_attributes_rec.ordered_quantity2 );
2023 WSH_DEBUG_SV.logmsg(l_module_name, 'p_attributes_rec.ordered_quantity_uom: '||p_attributes_rec.order_quantity_uom );
2024 WSH_DEBUG_SV.logmsg(l_module_name, 'p_attributes_rec.ordered_quantity_uom2: '||p_attributes_rec.ordered_quantity_uom2 );
2025 END IF;
2026 -- Resetting the Reservations Record Structure
2027 l_resv_tbl(i).organization_id := p_attributes_rec.ship_from_org_id;
2028 l_resv_tbl(i).subinventory_code := p_attributes_rec.subinventory;
2029 l_resv_tbl(i).revision := NULL;
2030 l_resv_tbl(i).locator_id := NULL;
2031 l_resv_tbl(i).lot_number := NULL;
2032 l_resv_tbl(i).lpn_id := NULL;
2033 l_resv_tbl(i).demand_source_line_detail := NULL; -- X-dock
2034 l_resv_tbl(i).ship_ready_flag := 2;
2035 l_resv_tbl(i).staged_flag := 'N';
2036 -- HW OPMCONV - Update the Qtys and UOM in case the item are single in one
2037 -- org and dual in a different org
2038 l_resv_tbl(i).primary_reservation_quantity := p_attributes_rec.ordered_quantity;
2039 l_resv_tbl(i).secondary_reservation_quantity := p_attributes_rec.ordered_quantity2;
2040 l_resv_tbl(i).primary_uom_code := p_attributes_rec.order_quantity_uom ;
2041 l_resv_tbl(i).secondary_uom_code := p_attributes_rec.ordered_quantity_uom2;
2042 l_resv_tbl(i).project_id := p_attributes_rec.project_id;
2043 l_resv_tbl(i).task_id := p_attributes_rec.task_id;
2044
2045 -- bug 5225044: reservation's need by date has to be updated
2046 -- in the context of changing both organization
2047 -- and scheduled ship date on the order line.
2048 -- If scheduled ship date is not changed, the need by date
2049 -- will be left alone.
2050 IF (g_cache_detail_rec.date_scheduled IS NULL)
2051 OR (p_attributes_rec.date_scheduled
2052 <> g_cache_detail_rec.date_scheduled)
2053 THEN
2054 l_resv_tbl(i).requirement_date :=
2055 p_attributes_rec.date_scheduled;
2056 END IF;
2057
2058 ELSIF l_change_sub_only = 'X' THEN
2059
2060 -- HW OPMCONV - Print values
2061 IF l_debug_on THEN
2062 WSH_DEBUG_SV.logmsg(l_module_name, 'IN L_CHANGE_SUB_ONLY IS: '||L_CHANGE_SUB_ONLY );
2063 WSH_DEBUG_SV.logmsg(l_module_name, 'NEW SUB: '||P_ATTRIBUTES_REC.SUBINVENTORY );
2064 WSH_DEBUG_SV.logmsg(l_module_name, 'IN L_CHANGE_SUB_ONLY IS: '||L_CHANGE_SUB_ONLY );
2065 WSH_DEBUG_SV.logmsg(l_module_name, 'p_attributes_rec.ordered_quantity: '||p_attributes_rec.ordered_quantity );
2066 WSH_DEBUG_SV.logmsg(l_module_name, 'p_attributes_rec.ordered_quantity2: '||p_attributes_rec.ordered_quantity2 );
2067 WSH_DEBUG_SV.logmsg(l_module_name, 'p_attributes_rec.ordered_quantity_uom: '||p_attributes_rec.order_quantity_uom );
2068 WSH_DEBUG_SV.logmsg(l_module_name, 'p_attributes_rec.ordered_quantity_uom2: '||p_attributes_rec.ordered_quantity_uom2 );
2069 END IF;
2070 --
2071 l_resv_tbl(i).subinventory_code := p_attributes_rec.subinventory;
2072 l_resv_tbl(i).revision := NULL;
2073 l_resv_tbl(i).locator_id := NULL;
2074 l_resv_tbl(i).lot_number := NULL;
2075 l_resv_tbl(i).lpn_id := NULL;
2076 l_resv_tbl(i).ship_ready_flag := 2;
2077 l_resv_tbl(i).staged_flag := 'N';
2078 -- HW OPMCONV - Update the Qtys and UOM in case the item are single in one
2079 -- org and dual in a different org
2080
2081 l_resv_tbl(i).primary_reservation_quantity := p_attributes_rec.ordered_quantity;
2082 l_resv_tbl(i).secondary_reservation_quantity := p_attributes_rec.ordered_quantity2;
2083 l_resv_tbl(i).primary_uom_code := p_attributes_rec.order_quantity_uom ;
2084 l_resv_tbl(i).secondary_uom_code := p_attributes_rec.ordered_quantity_uom2;
2085 l_resv_tbl(i).project_id := p_attributes_rec.project_id;
2086 l_resv_tbl(i).task_id := p_attributes_rec.task_id;
2087
2088 -- bug 5333667: clear attributes for WIP/supply source
2089 -- pass "g_miss_xxx" because we don't care.
2090 -- Bug 14011139 : Commenting the changes done for bug 5333667 as they
2091 -- resulted in regressions.
2092 /* l_resv_tbl(i).supply_source_type_id := FND_API.G_MISS_NUM;
2093 l_resv_tbl(i).supply_source_header_id := FND_API.G_MISS_NUM;
2094 l_resv_tbl(i).supply_source_line_id := FND_API.G_MISS_NUM;
2095 l_resv_tbl(i).supply_source_name := FND_API.G_MISS_CHAR;
2096 l_resv_tbl(i).supply_source_line_detail := FND_API.G_MISS_NUM;
2097 */
2098 END IF;
2099 --
2100 IF l_debug_on THEN
2101 wsh_debug_sv.logmsg(l_module_name, 'Actual reservation record
2102 being passed to INV');
2103 wsh_debug_Sv.log(l_module_name, 'RSV record.staged_flag',
2104 l_resv_tbl(i).staged_flag);
2105 wsh_debug_sv.log(l_module_name, 'RSV record org Id',
2106 l_resv_tbl(i).organization_id);
2107 wsh_debug_sv.log(l_module_name, 'RSV subinventory Code',
2108 l_resv_tbl(i).subinventory_code);
2109 END IF;
2110 --
2111 -- HW OPMCONV. Pass a new parameter p_qty2
2112 WSH_USA_INV_PVT.create_reservation (
2113 p_query_input => l_resv_tbl(i),
2114 p_qty2 => p_attributes_rec.ordered_quantity2,
2115 x_reservation_id => l_reservation_id,
2116 x_qty_reserved => l_qty_reserved,
2117 x_return_status => x_return_status);
2118
2119 -- Continue even if create reservation fails.
2120 END IF; -- if not unsched
2121 END LOOP;
2122
2123 END IF; -- l_change_resv_flag = 'Y'
2124
2125
2126 /* H integration: Pricing integration csun */
2127 IF l_del_tab.count > 0 THEN
2128 WSH_DELIVERY_LEGS_ACTIONS.Mark_Reprice_Required(
2129 p_entity_type => 'DELIVERY',
2130 p_entity_ids => l_del_tab,
2131 x_return_status => l_return_status);
2132 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
2133 raise mark_reprice_error;
2134 END IF;
2135
2136 -- deliveryMerge
2137 IF l_debug_on THEN
2138 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_NEW_DELIVERY_ACTIONS.Adjust_Planned_Flag',WSH_DEBUG_SV.C_PROC_LEVEL);
2139 END IF;
2140 WSH_NEW_DELIVERY_ACTIONS.Adjust_Planned_Flag(
2141 p_delivery_ids => l_del_tab,
2142 p_caller => 'WSH_DLMG',
2143 p_force_appending_limit => 'N',
2144 p_call_lcss => 'Y',
2145 p_event => NULL,
2146 x_return_status => l_return_status);
2147 IF l_debug_on THEN
2148 WSH_DEBUG_SV.log(l_module_name,'Adjust_Planned_Flag l_return_status',l_return_status);
2149 END IF;
2150
2151 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR THEN
2152 raise Adjust_Planned_Flag_Err;
2153 ELSIF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
2154 l_num_warning := l_num_warning + 1;
2155 END IF;
2156
2157 END IF;
2158
2159 END IF;
2160 IF l_num_warning > 0 THEN
2161 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
2162 ELSE
2163 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2164 END IF;
2165 --
2166 IF l_debug_on THEN
2167 WSH_DEBUG_SV.pop(l_module_name);
2168 END IF;
2169 --
2170 EXCEPTION
2171
2172 WHEN item_update_not_allowed THEN
2173 x_update_allowed := 'N';
2174 --
2175 -- For item change case, enter both item names.
2176 FND_MESSAGE.SET_NAME('WSH','WSH_ITEM_SUB_NOT_ALLOWED');
2177 FND_MESSAGE.SET_TOKEN('ITEM1',WSH_UTIL_CORE.Get_Item_Name(g_cache_detail_rec.inventory_item_id,l_final_org_id));
2178 FND_MESSAGE.SET_TOKEN('ITEM2',WSH_UTIL_CORE.Get_Item_Name(p_attributes_rec.inventory_item_id,l_final_org_id));
2179 WSH_UTIL_CORE.add_message (x_return_status,l_module_name);
2180 IF l_debug_on THEN
2181 WSH_DEBUG_SV.logmsg(l_module_name,'UPDATE_NOT_ALLOWED exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2182 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:UPDATE_NOT_ALLOWED');
2183 END IF;
2184 --
2185
2186 WHEN update_not_allowed THEN
2187 x_update_allowed := 'N';
2188 --
2189 IF l_debug_on THEN
2190 WSH_DEBUG_SV.logmsg(l_module_name,'UPDATE_NOT_ALLOWED exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2191 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:UPDATE_NOT_ALLOWED');
2192 END IF;
2193 --
2194 WHEN mark_reprice_error THEN
2195 FND_MESSAGE.Set_Name('WSH', 'WSH_REPRICE_REQUIRED_ERR');
2196 x_return_status := l_return_status;
2197 WSH_UTIL_CORE.add_message (x_return_status,l_module_name);
2198 IF l_debug_on THEN
2199 WSH_DEBUG_SV.logmsg(l_module_name,'MARK_REPRICE_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2200 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:MARK_REPRICE_ERROR');
2201 END IF;
2202 --
2203 WHEN Adjust_Planned_Flag_Err THEN
2204 FND_MESSAGE.SET_NAME('WSH', 'WSH_ADJUST_PLANNED_FLAG_ERR');
2205 WSH_UTIL_CORE.add_message(l_return_status,l_module_name);
2206 x_return_status := l_return_status;
2207
2208 IF l_debug_on THEN
2209 WSH_DEBUG_SV.logmsg(l_module_name,'Adjust_Planned_Flag_Err exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2210 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:Adjust_Planned_Flag_Err');
2211 END IF;
2212
2213 WHEN OTHERS THEN
2214 IF c_delivery_detail%ISOPEN THEN
2215 CLOSE c_delivery_detail;
2216 END IF;
2217
2218 -- close open cursors as needed
2219 --ROLLBACK TO before_changes;
2220 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2221 x_update_allowed := 'N';
2222 WSH_UTIL_CORE.DEFAULT_HANDLER('WSH_USA_CATEGORIES_PVT.Change_Schedule',l_module_name);
2223 --
2224 IF l_debug_on THEN
2225 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2226 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
2227 END IF;
2228 --
2229 END Change_Schedule;
2230
2231 PROCEDURE Change_Scheduled_Date(
2232 p_attributes_rec IN WSH_INTERFACE.ChangedAttributeRecType,
2233 p_source_code IN VARCHAR2,
2234 p_wms_flag IN VARCHAR2,
2235 x_update_allowed IN OUT NOCOPY VARCHAR2,
2236 x_return_status OUT NOCOPY VARCHAR2)
2237 IS
2238
2239 cursor c_delivery_detail is
2240 select delivery_detail_id, ship_from_location_id,released_status,move_order_line_id
2241 from wsh_delivery_details
2242 where source_line_id = p_attributes_rec.source_line_id
2243 and source_code = p_source_code;
2244
2245 l_orig_date DATE;
2246 l_del_det NUMBER;
2247 l_location_id NUMBER;
2248
2249 --Bug 9226187 : Added local variables
2250 l_status VARCHAR2(1);
2251 l_mo_line_id NUMBER;
2252 l_mo_line_rec INV_Move_Order_PUB.Trolin_Rec_Type;
2253 l_trolin_tbl INV_MOVE_ORDER_PUB.Trolin_Tbl_Type;
2254 l_trolin_old_tbl INV_MOVE_ORDER_PUB.Trolin_Tbl_Type;
2255 l_trolin_out_tbl INV_MOVE_ORDER_PUB.Trolin_Tbl_Type;
2256 l_msg_count NUMBER;
2257 l_msg_data VARCHAR2(3000);
2258
2259
2260 update_not_allowed EXCEPTION;
2261
2262 /* H integration: Pricing integration csun */
2263 i NUMBER := 0;
2264 l_det_tab WSH_UTIL_CORE.Id_Tab_Type ; -- to mark reprice required flag
2265 l_return_status VARCHAR2(1);
2266 mark_reprice_error EXCEPTION;
2267
2268
2269 --
2270 l_debug_on BOOLEAN;
2271 --
2272 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'CHANGE_SCHEDULED_DATE';
2273 --
2274 BEGIN
2275
2276 --
2277 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2278 --
2279 IF l_debug_on IS NULL
2280 THEN
2281 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2282 END IF;
2283 --
2284 IF l_debug_on THEN
2285 WSH_DEBUG_SV.push(l_module_name);
2286 --
2287 WSH_DEBUG_SV.log(l_module_name,'P_SOURCE_CODE',P_SOURCE_CODE);
2288 WSH_DEBUG_SV.log(l_module_name,'P_WMS_FLAG',P_WMS_FLAG);
2289 WSH_DEBUG_SV.log(l_module_name,'X_UPDATE_ALLOWED',X_UPDATE_ALLOWED);
2290 END IF;
2291 --
2292 --Bug 9226187: Introduced call to the API INV_MOVE_ORDER_PUB.Process_Move_Order_Line
2293 -- in order to change the Date_Required on MO Line,whenever there is a
2294 -- change in the Dat_Scheduled on SO Line.
2295 -- The call should be made only if the delivery detail is 'Released to Warehouse'
2296 -- and a Move Order line exists.
2297 IF (p_attributes_rec.date_scheduled <> FND_API.G_MISS_DATE) AND
2298 (g_cache_detail_rec.date_scheduled <> p_attributes_rec.date_scheduled) THEN
2299
2300
2301 OPEN c_delivery_detail;
2302
2303 LOOP
2304
2305 FETCH c_delivery_detail into l_del_det, l_location_id,l_status,l_mo_line_id;
2306
2307 EXIT WHEN c_delivery_detail%NOTFOUND;
2308
2309 IF l_status = 'S' AND l_mo_line_id IS NOT NULL THEN
2310 IF l_debug_on THEN
2311 WSH_DEBUG_SV.logmsg(l_module_name, 'PERFORMING Change Dates ' );
2312 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit INV_TROLIN_UTIL.QUERY_ROW',WSH_DEBUG_SV.C_PROC_LEVEL);
2313 END IF;
2314
2315 l_mo_line_rec := INV_Trolin_Util.Query_Row(p_line_id => l_mo_line_id);
2316 l_trolin_tbl(1) := l_mo_line_rec;
2317 l_trolin_old_tbl(1) := l_mo_line_rec;
2318 l_trolin_tbl(1).OPERATION := INV_GLOBALS.G_OPR_UPDATE;
2319 l_trolin_tbl(1).date_required := p_attributes_rec.date_scheduled;
2320
2321 IF l_debug_on THEN
2322 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit INV_MOVE_ORDER_PUB.PROCESS_MOVE_ORDER_LINE',WSH_DEBUG_SV.C_PROC_LEVEL);
2323 END IF;
2324
2325 INV_MOVE_ORDER_PUB.Process_Move_Order_Line(
2326 p_api_version_number => 1.0,
2327 p_commit => FND_API.G_FALSE,
2328 x_return_status => x_return_status,
2329 x_msg_count => l_msg_count,
2330 x_msg_data => l_msg_data,
2331 p_trolin_tbl => l_trolin_tbl,
2332 p_trolin_old_tbl => l_trolin_old_tbl,
2333 x_trolin_tbl => l_trolin_out_tbl);
2334
2335 IF (x_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)) THEN
2336 IF l_debug_on THEN
2337 WSH_DEBUG_SV.log(l_module_name,'Process_Move_Order_Line returns ERROR');
2338 END IF;
2339 RAISE update_not_allowed;
2340 END IF;
2341
2342 END IF; -- of l_status = 'S' and mol is not null
2343
2344 IF (g_cache_detail_rec.date_scheduled < p_attributes_rec.date_scheduled) THEN
2345 g_cache_detail_rec.cache_flag := 'N';
2346 --
2347 IF l_debug_on THEN
2348 WSH_DEBUG_SV.logmsg(l_module_name, 'CHANGE_SCHEDULED_DATE PERFORMING ACTIONS ' );
2349 END IF;
2350 --
2351 i := i+1;
2352 l_det_tab(i) := l_del_det;
2353 --
2354 IF l_debug_on THEN
2355 WSH_DEBUG_SV.logmsg(l_module_name, 'ADDING DELIVERY DETAIL '|| L_DEL_DET || 'TO MARK LIST' );
2356 END IF;
2357 --
2358 Log_Exception(
2359 p_delivery_detail_id => l_del_det,
2360 p_location_id => l_location_id,
2361 p_exception_name => 'WSH_CHANGE_SCHED_DATE',
2362 x_return_status => x_return_status);
2363
2364 IF (x_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)) THEN
2365 RAISE update_not_allowed;
2366 END IF;
2367 END IF;
2368 --End of Changes for bug 9226187
2369 END LOOP;
2370
2371 CLOSE c_delivery_detail;
2372
2373 /* H integration: Pricing integration csun */
2374 IF l_det_tab.count > 0 THEN
2375 WSH_DELIVERY_LEGS_ACTIONS.Mark_Reprice_Required(
2376 p_entity_type => 'DELIVERY_DETAIL',
2377 p_entity_ids => l_det_tab,
2378 x_return_status => l_return_status);
2379 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
2380 raise mark_reprice_error;
2381 END IF;
2382 END IF;
2383 END IF;
2384 --
2385 IF l_debug_on THEN
2386 WSH_DEBUG_SV.pop(l_module_name);
2387 END IF;
2388 --
2389 EXCEPTION
2390
2391 WHEN update_not_allowed THEN
2392 x_update_allowed := 'N';
2393 --
2394 IF l_debug_on THEN
2395 WSH_DEBUG_SV.logmsg(l_module_name,'UPDATE_NOT_ALLOWED exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2396 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:UPDATE_NOT_ALLOWED');
2397 END IF;
2398 --
2399 RETURN;
2400 WHEN mark_reprice_error THEN
2401 FND_MESSAGE.Set_Name('WSH', 'WSH_REPRICE_REQUIRED_ERR');
2402 x_return_status := l_return_status;
2403 WSH_UTIL_CORE.add_message (x_return_status,l_module_name);
2404 --
2405 IF l_debug_on THEN
2406 WSH_DEBUG_SV.logmsg(l_module_name,'MARK_REPRICE_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2407 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:MARK_REPRICE_ERROR');
2408 END IF;
2409 --
2410 WHEN OTHERS THEN
2411 -- close open cursors as needed
2412 IF c_delivery_detail%ISOPEN THEN
2413 close c_delivery_detail;
2414 END IF;
2415 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2416 x_update_allowed := 'N';
2417 WSH_UTIL_CORE.DEFAULT_HANDLER('WSH_USA_CATEGORIES_PVT.Change_Scheduled_Date',l_module_name);
2418 --
2419 IF l_debug_on THEN
2420 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2421 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
2422 END IF;
2423 --
2424 END Change_Scheduled_Date;
2425
2426
2427 PROCEDURE Change_Sets(
2428 p_attributes_rec IN WSH_INTERFACE.ChangedAttributeRecType,
2429 p_source_code IN VARCHAR2,
2430 p_wms_flag IN VARCHAR2,
2431 x_update_allowed IN OUT NOCOPY VARCHAR2,
2432 x_return_status OUT NOCOPY VARCHAR2)
2433 IS
2434 -- Bug 2846006
2435 -- Cursor to check if delivery details exist for a source line
2436 -- but different released status
2437 -- Bug 2995052 : Ship Set can be assigned to a order line even if it contains
2438 -- back order and ready to release delivery details.
2439 cursor c_get_released_status is
2440 select count(distinct(decode(released_status,'B','R',released_status)))
2441 from wsh_delivery_details
2442 where source_line_id = p_attributes_rec.source_line_id
2443 and source_code = p_source_code
2444 and released_status NOT IN ('X','D');
2445
2446 -- bug 2153719: ignore released_status 'X' and 'D'
2447 cursor c_get_details is
2448 select delivery_detail_id, released_status, move_order_line_id
2449 from wsh_delivery_details
2450 where source_line_id = p_attributes_rec.source_line_id
2451 and source_code = p_source_code
2452 and released_status NOT IN ('X', 'D')
2453 order by decode(released_status, 'Y', 1, 2);
2454
2455 l_organization_id NUMBER;
2456 l_del_det NUMBER;
2457 l_status VARCHAR2(1);
2458 l_status_count NUMBER;
2459 l_mo_line_id NUMBER;
2460 l_ship_set NUMBER;
2461 --l_param_info WSH_SHIPPING_PARAMETERS.Parameter_Rec_Type;
2462 l_mo_line_rec INV_Move_Order_PUB.Trolin_Rec_Type;
2463 -- HW OPMCONV. Removed OPM variables
2464
2465 l_trolin_tbl INV_MOVE_ORDER_PUB.Trolin_Tbl_Type;
2466 l_trolin_old_tbl INV_MOVE_ORDER_PUB.Trolin_Tbl_Type;
2467 l_trolin_out_tbl INV_MOVE_ORDER_PUB.Trolin_Tbl_Type;
2468 l_msg_count NUMBER;
2469 l_msg_data VARCHAR2(3000);
2470 update_not_allowed EXCEPTION;
2471
2472 --
2473 l_debug_on BOOLEAN;
2474 --
2475 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'CHANGE_SETS';
2476
2477 -- HW BUG#:2296620 OPM variables
2478
2479 l_api_version_number CONSTANT NUMBER := 1.0;
2480
2481 BEGIN
2482 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2483 IF l_debug_on IS NULL THEN
2484 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2485 END IF;
2486
2487 IF l_debug_on THEN
2488 WSH_DEBUG_SV.push(l_module_name);
2489 WSH_DEBUG_SV.log(l_module_name,'P_SOURCE_CODE',P_SOURCE_CODE);
2490 WSH_DEBUG_SV.log(l_module_name,'P_WMS_FLAG',P_WMS_FLAG);
2491 WSH_DEBUG_SV.log(l_module_name,'X_UPDATE_ALLOWED',X_UPDATE_ALLOWED);
2492 END IF;
2493
2494 IF ((p_attributes_rec.ship_set_id <> FND_API.G_MISS_NUM) OR (p_attributes_rec.ship_set_id IS NULL))AND
2495 (NVL(p_attributes_rec.ship_set_id, FND_API.G_MISS_NUM) <> NVL(g_cache_detail_rec.ship_set_id, FND_API.G_MISS_NUM))
2496 THEN
2497
2498 g_cache_detail_rec.cache_flag := 'N';
2499
2500 IF p_attributes_rec.ship_from_org_id = FND_API.G_MISS_NUM THEN
2501 l_organization_id := g_cache_detail_rec.organization_id;
2502 ELSE
2503 l_organization_id := p_attributes_rec.ship_from_org_id;
2504 END IF;
2505
2506 -- HW BUG#:2296620 Need to check if org is process or discrete
2507 -- HW OPMCONV. Removed branching
2508
2509 IF NVL(g_param_info.organization_id, -999) <> l_organization_id THEN
2510 WSH_SHIPPING_PARAMS_PVT.Get(
2511 p_organization_id => l_organization_id,
2512 x_param_info => g_param_info,
2513 x_return_status => x_return_status
2514 );
2515
2516 IF (x_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)) THEN
2517 RAISE update_not_allowed;
2518 END IF;
2519
2520 END IF;
2521
2522 IF g_param_info.enforce_ship_set_and_smc = 'Y' THEN -- Bug 2243033, if not enforced, allow changes regardless...
2523
2524 OPEN c_get_details;
2525 LOOP
2526 FETCH c_get_details into l_del_det, l_status, l_mo_line_id;
2527 EXIT WHEN c_get_details%NOTFOUND;
2528
2529 IF p_attributes_rec.ship_set_id IS NOT NULL
2530 AND p_attributes_rec.ship_set_id <> NVL(g_cache_detail_rec.ship_set_id, FND_API.G_MISS_NUM) THEN
2531
2532 -- Planned for Crossdocking scenario, ECO 4497224
2533 IF l_status = 'S' AND l_mo_line_id IS NULL THEN
2534 FND_MESSAGE.SET_NAME('WSH','WSH_CHANGE_SET_XDOCK_ERROR');
2535 FND_MESSAGE.SET_TOKEN('DETAIL', l_del_det);
2536 IF l_debug_on THEN
2537 WSH_DEBUG_SV.logmsg(l_module_name, 'CANNOT CHANGE_SETS : LINE IX CROSSDOCKED' );
2538 END IF;
2539 RAISE update_not_allowed;
2540 END IF; -- status = 'S' and MOL is null
2541 -- End of ECO 4497224
2542
2543 IF l_status = 'S' AND l_mo_line_id IS NOT NULL THEN
2544 IF l_debug_on THEN
2545 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit INV_TROLIN_UTIL.QUERY_ROW',WSH_DEBUG_SV.C_PROC_LEVEL);
2546 END IF;
2547
2548 -- HW BUG#:2296620 Need to branch.
2549 -- HW OPMCONV. Removed branching
2550
2551 l_mo_line_rec := INV_Trolin_Util.Query_Row(p_line_id => l_mo_line_id);
2552 IF l_mo_line_rec.quantity_detailed > 0 THEN
2553 FND_MESSAGE.SET_NAME('WSH','WSH_CHANGE_SET_ERROR');
2554 FND_MESSAGE.SET_TOKEN('DETAIL', l_del_det);
2555 IF l_debug_on THEN
2556 WSH_DEBUG_SV.logmsg(l_module_name, 'CANNOT CHANGE_SETS : ALLOCATIONS CREATED' );
2557 END IF;
2558 Raise update_not_allowed;
2559 END IF; -- of discrete quantity_detailed
2560
2561 END IF; -- of l_status = 'S' and MOL is not null
2562 -- HW end of BUG#:2296620 changes
2563 END IF; -- p_attributes_rec.ship_set_id is NULL
2564
2565 -- For ensuring the call is for Released to Warehouse only
2566 -- and not Planned for Crossdocking, Bug 5210926
2567 IF l_status = 'S' AND l_mo_line_id IS NOT NULL THEN
2568 IF l_debug_on THEN
2569 WSH_DEBUG_SV.logmsg(l_module_name, 'PERFORMING CHANGE_SETS ' );
2570 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit INV_TROLIN_UTIL.QUERY_ROW',WSH_DEBUG_SV.C_PROC_LEVEL);
2571 END IF;
2572
2573 -- HW BUG#:2296620 Need to branch
2574 -- HW OPMCONV. Removed branching
2575 l_mo_line_rec := INV_Trolin_Util.Query_Row(p_line_id => l_mo_line_id);
2576 l_trolin_tbl(1) := l_mo_line_rec;
2577 l_trolin_old_tbl(1) := l_mo_line_rec;
2578 l_trolin_tbl(1).OPERATION := INV_GLOBALS.G_OPR_UPDATE;
2579 l_trolin_tbl(1).ship_set_id := p_attributes_rec.ship_set_id;
2580
2581 IF l_debug_on THEN
2582 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit INV_MOVE_ORDER_PUB.PROCESS_MOVE_ORDER_LINE',WSH_DEBUG_SV.C_PROC_LEVEL);
2583 END IF;
2584
2585 INV_MOVE_ORDER_PUB.Process_Move_Order_Line(
2586 p_api_version_number => 1.0,
2587 p_commit => FND_API.G_FALSE,
2588 x_return_status => x_return_status,
2589 x_msg_count => l_msg_count,
2590 x_msg_data => l_msg_data,
2591 p_trolin_tbl => l_trolin_tbl,
2592 p_trolin_old_tbl => l_trolin_old_tbl,
2593 x_trolin_tbl => l_trolin_out_tbl);
2594
2595 IF (x_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)) THEN
2596 IF l_debug_on THEN
2597 WSH_DEBUG_SV.log(l_module_name,'Process_Move_Order_Line returns ERROR');
2598 END IF;
2599 RAISE update_not_allowed;
2600 END IF; -- of x_return_status for Discrete
2601
2602 END IF; -- of l_status = 'S' and mol is not null
2603
2604 END LOOP; -- End Loop for all the lines in source line id,c_get_details
2605
2606 -- HW OPM BUG#:2296620 end of changes
2607 CLOSE c_get_details;
2608
2609 /* shipset is not enforced, allow shipset change regardlessly */
2610 --ELSE
2611 END IF; -- Bug 2243033
2612
2613 END IF;
2614 --
2615 IF l_debug_on THEN
2616 WSH_DEBUG_SV.pop(l_module_name);
2617 END IF;
2618 --
2619 EXCEPTION
2620
2621 WHEN update_not_allowed THEN
2622 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2623 WSH_UTIL_CORE.Add_Message(x_return_status,l_module_name);
2624 x_update_allowed := 'N';
2625 --
2626 IF l_debug_on THEN
2627 WSH_DEBUG_SV.logmsg(l_module_name,'UPDATE_NOT_ALLOWED exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2628 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:UPDATE_NOT_ALLOWED');
2629 END IF;
2630 --
2631 WHEN OTHERS THEN
2632 -- close open cursors as needed
2633 IF c_get_released_status%isopen THEN
2634 close c_get_released_status;
2635 END IF;
2636 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2637 x_update_allowed := 'N';
2638 WSH_UTIL_CORE.DEFAULT_HANDLER('WSH_USA_CATEGORIES_PVT.Change_Sets',l_module_name);
2639 --
2640 IF l_debug_on THEN
2641 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2642 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
2643 END IF;
2644 --
2645 END Change_Sets;
2646
2647 PROCEDURE Change_Delivery_Group(
2648 p_attributes_rec IN WSH_INTERFACE.ChangedAttributeRecType,
2649 p_source_code IN VARCHAR2,
2650 p_wms_flag IN VARCHAR2,
2651 x_update_allowed IN OUT NOCOPY VARCHAR2,
2652 x_return_status OUT NOCOPY VARCHAR2)
2653 IS
2654
2655 l_mandatory_flag VARCHAR2(1) := NULL;
2656 l_shipment_changed_flag VARCHAR2(1) := NULL;
2657 l_status VARCHAR2(1);
2658 l_del_det NUMBER;
2659 l_delivery NUMBER;
2660 l_parent_det NUMBER;
2661 l_location_id NUMBER;
2662 l_organization_id NUMBER;
2663 l_intmed_ship_to_org_flag VARCHAR2(1);
2664 l_sold_to_org_flag VARCHAR2(1);
2665 l_ship_method_flag VARCHAR2(1);
2666 l_freight_terms_flag VARCHAR2(1);
2667 l_fob_flag VARCHAR2(1);
2668 l_carrier_flag VARCHAR2(1);
2669 l_intmed_ship_to_location_id NUMBER;
2670 l_return_status VARCHAR2(1);
2671 l_num_warning NUMBER;
2672 l_num_errors NUMBER;
2673 l_attr_tab Wsh_delivery_autocreate.grp_attr_tab_type;
2674 l_group_tab wsh_delivery_autocreate.grp_attr_tab_type;
2675 l_action_rec wsh_delivery_autocreate.action_rec_type;
2676 l_target_rec wsh_delivery_autocreate.grp_attr_rec_type;
2677 l_matched_entities wsh_util_core.id_tab_type;
2678 l_out_rec wsh_delivery_autocreate.out_rec_type;
2679 l_generic_flag varchar2(1);
2680 l_sm_changed_flag varchar2(1) := 'N';
2681
2682
2683 update_not_allowed EXCEPTION;
2684
2685 cursor c_del_det (p_source_line_id in number, p_source_code in varchar2)is
2686 select wdd.delivery_detail_id, wdd.ship_from_location_id,
2687 wda.delivery_id, wda.parent_delivery_detail_id,
2688 wdd.released_status, wnd.service_level service_level, wnd.mode_of_transport mode_of_transport,
2689 wnd.carrier_id carrier_id, wnd.ship_method_code ship_method_code
2690 from wsh_delivery_details wdd, wsh_delivery_assignments_v wda, wsh_new_deliveries wnd
2691 where wdd.source_line_id = p_source_line_id
2692 and wdd.source_code = p_source_code
2693 and wdd.delivery_detail_id = wda.delivery_detail_id
2694 and wda.delivery_id = wnd.delivery_id
2695 UNION
2696 select wdd.delivery_detail_id, wdd.ship_from_location_id,
2697 wda.delivery_id, wda.parent_delivery_detail_id,
2698 wdd.released_status, wddp.service_level service_level, wddp.mode_of_transport mode_of_transport,
2699 wddp.carrier_id carrier_id, wdd.ship_method_code ship_method_code
2700 from wsh_delivery_details wdd, wsh_delivery_assignments_v wda, wsh_delivery_details wddp
2701 where wdd.source_line_id = p_source_line_id
2702 and wdd.source_code = p_source_code
2703 and wdd.delivery_detail_id = wda.delivery_detail_id
2704 and wda.parent_delivery_detail_id = wddp.delivery_detail_id
2705 and wda.delivery_id is null;
2706
2707 cursor c_get_parent_container(p_child_id in number) is
2708 select parent_delivery_detail_id
2709 from wsh_delivery_assignments_v
2710 where delivery_detail_id = p_child_id;
2711
2712
2713
2714 l_carrier_rec WSH_CARRIERS_GRP.Carrier_Service_InOut_Rec_Type;
2715 l_group_index NUMBER;
2716 l_locked_container NUMBER;
2717 l_parent_container NUMBER;
2718
2719 l_msg_count NUMBER;
2720 l_msg_data VARCHAR2(4000);
2721 l_delivery_ids wsh_util_core.id_tab_type;
2722
2723
2724 l_delivery_info_tab WSH_NEW_DELIVERIES_PVT.Delivery_Attr_Tbl_Type;
2725 l_del_out_rec_tab WSH_DELIVERIES_GRP.Del_Out_Tbl_Type;
2726 l_del_in_rec WSH_DELIVERIES_GRP.Del_In_Rec_Type;
2727
2728 l_detail_info_tab WSH_GLBL_VAR_STRCT_GRP.Delivery_Details_Attr_Tbl_Type;
2729 l_detail_in_rec WSH_GLBL_VAR_STRCT_GRP.detailInRecType;
2730 l_detail_out_rec WSH_GLBL_VAR_STRCT_GRP.detailOutRecType;
2731
2732
2733 l_debug_on BOOLEAN;
2734 --
2735 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'CHANGE_DELIVERY_GROUP';
2736 --
2737 BEGIN
2738 --
2739 --
2740 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2741 --
2742 IF l_debug_on IS NULL
2743 THEN
2744 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2745 END IF;
2746 --
2747 IF l_debug_on THEN
2748 WSH_DEBUG_SV.push(l_module_name);
2749 --
2750 WSH_DEBUG_SV.log(l_module_name,'P_SOURCE_CODE',P_SOURCE_CODE);
2751 WSH_DEBUG_SV.log(l_module_name,'P_WMS_FLAG',P_WMS_FLAG);
2752 WSH_DEBUG_SV.log(l_module_name,'X_UPDATE_ALLOWED',X_UPDATE_ALLOWED);
2753 END IF;
2754 --
2755 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2756 IF p_attributes_rec.ship_from_org_id = FND_API.G_MISS_NUM THEN
2757 l_attr_tab(1).organization_id := g_cache_detail_rec.organization_id;
2758 l_attr_tab(1).ship_from_location_id := g_cache_detail_rec.organization_id;
2759 ELSE
2760 l_attr_tab(1).organization_id := p_attributes_rec.ship_from_org_id;
2761 l_attr_tab(1).ship_from_location_id := p_attributes_rec.ship_from_org_id;
2762 END IF;
2763 l_attr_tab(2).organization_id := g_cache_detail_rec.organization_id;
2764 l_attr_tab(2).ship_from_location_id := g_cache_detail_rec.organization_id;
2765
2766 IF p_attributes_rec.ship_to_org_id = FND_API.G_MISS_NUM THEN
2767
2768 l_attr_tab(1).ship_to_location_id := g_cache_detail_rec.ship_to_site_use_id;
2769 ELSE
2770 l_attr_tab(1).ship_to_location_id := p_attributes_rec.ship_to_org_id;
2771 END IF;
2772 l_attr_tab(2).ship_to_location_id := g_cache_detail_rec.ship_to_site_use_id;
2773
2774
2775 IF l_debug_on THEN
2776 WSH_DEBUG_SV.log(l_module_name,'p_attributes_rec.intmed_ship_to_org_id',p_attributes_rec.intmed_ship_to_org_id);
2777 END IF;
2778
2779 IF p_attributes_rec.intmed_ship_to_org_id = FND_API.G_MISS_NUM THEN
2780
2781 l_intmed_ship_to_location_id := g_cache_detail_rec.intmed_ship_to_location_id;
2782
2783 ELSIF (p_attributes_rec.intmed_ship_to_org_id IS NOT NULL) THEN
2784 WSH_UTIL_CORE.GET_LOCATION_ID('CUSTOMER SITE',
2785 p_attributes_rec.intmed_ship_to_org_id,
2786 l_intmed_ship_to_location_id,
2787 x_return_status);
2788 /* Bug Fix 2852545 passed x_return_status instead of l_return_status to api_post_call */
2789 wsh_util_core.api_post_call(p_return_status =>x_return_status,
2790 x_num_warnings =>l_num_warning,
2791 x_num_errors =>l_num_errors);
2792
2793 END IF;
2794
2795 l_attr_tab(1).intmed_ship_to_location_id := l_intmed_ship_to_location_id;
2796 l_attr_tab(2).intmed_ship_to_location_id := g_cache_detail_rec.intmed_ship_to_location_id;
2797
2798 IF p_attributes_rec.sold_to_org_id = FND_API.G_MISS_NUM THEN
2799 l_attr_tab(1).customer_id := g_cache_detail_rec.customer_id;
2800 ELSE
2801 l_attr_tab(1).customer_id := p_attributes_rec.sold_to_org_id;
2802
2803 IF (NVL(p_attributes_rec.sold_to_org_id, FND_API.G_MISS_NUM) <> NVL(g_cache_detail_rec.customer_id, FND_API.G_MISS_NUM)) THEN
2804
2805 l_shipment_changed_flag := 'Y';
2806
2807 END IF;
2808
2809 END IF;
2810 l_attr_tab(2).customer_id := g_cache_detail_rec.customer_id;
2811
2812
2813 IF p_attributes_rec.freight_terms_code = FND_API.G_MISS_CHAR THEN
2814 l_attr_tab(1).freight_terms_code := g_cache_detail_rec.freight_terms_code;
2815 ELSE
2816 l_attr_tab(1).freight_terms_code := p_attributes_rec.freight_terms_code;
2817
2818 IF (NVL(p_attributes_rec.freight_terms_code, FND_API.G_MISS_CHAR) <> NVL(g_cache_detail_rec.freight_terms_code, FND_API.G_MISS_CHAR)) THEN
2819
2820 l_shipment_changed_flag := 'Y';
2821
2822 END IF;
2823
2824 END IF;
2825 l_attr_tab(2).freight_terms_code := g_cache_detail_rec.freight_terms_code;
2826 IF l_debug_on THEN
2827 WSH_DEBUG_SV.logmsg(l_module_name, 'freight_terms_code 1: '||p_attributes_rec.freight_terms_code );
2828 WSH_DEBUG_SV.logmsg(l_module_name, 'freight_terms_code 2: '||g_cache_detail_rec.freight_terms_code );
2829 END IF;
2830
2831 IF p_attributes_rec.fob_code = FND_API.G_MISS_CHAR THEN
2832 l_attr_tab(1).fob_code := g_cache_detail_rec.fob_code;
2833 ELSE
2834 l_attr_tab(1).fob_code := p_attributes_rec.fob_code;
2835
2836 IF (NVL(p_attributes_rec.fob_code, FND_API.G_MISS_CHAR) <> NVL(g_cache_detail_rec.fob_code, FND_API.G_MISS_CHAR)) THEN
2837
2838 l_shipment_changed_flag := 'Y';
2839
2840 END IF;
2841
2842 END IF;
2843 l_attr_tab(2).fob_code := g_cache_detail_rec.fob_code;
2844
2845 IF ((p_attributes_rec.shipping_method_code <> FND_API.G_MISS_CHAR) OR (p_attributes_rec.shipping_method_code IS NULL)) AND
2846 (NVL(p_attributes_rec.shipping_method_code, FND_API.G_MISS_CHAR) <> NVL(g_cache_detail_rec.ship_method_code,FND_API.G_MISS_CHAR)) THEN
2847
2848 l_shipment_changed_flag := 'Y';
2849 l_sm_changed_flag := 'Y';
2850
2851 IF l_debug_on THEN
2852 WSH_DEBUG_SV.logmsg(l_module_name, 'l_shipment_changed_flag: '||l_shipment_changed_flag );
2853 WSH_DEBUG_SV.logmsg(l_module_name, 'p_attributes_rec.shipping_method_code: '||p_attributes_rec.shipping_method_code);
2854 WSH_DEBUG_SV.logmsg(l_module_name, 'g_cache_detail_rec.ship_method_code: '||g_cache_detail_rec.ship_method_code);
2855 END IF;
2856 l_carrier_rec.ship_method_code := p_attributes_rec.shipping_method_code;
2857
2858 -- 4673778
2859 l_carrier_rec.generic_flag := NULL; -- initializing
2860 IF (p_attributes_rec.shipping_method_code is NOT NULL) THEN
2861 WSH_CARRIERS_GRP.get_carrier_service_mode(
2862 p_carrier_service_inout_rec => l_carrier_rec,
2863 x_return_status => x_return_status);
2864 -- 4673778
2865 IF l_debug_on THEN
2866 WSH_DEBUG_SV.logmsg(l_module_name, 'After get_carrier_service_mode'|| x_return_status);
2867 WSH_DEBUG_SV.logmsg(l_module_name, 'l_carrier_rec.genflag#car-id#sm-code#mode-of-tpt#svc-lvl: '||
2868 l_carrier_rec.generic_flag||'#'|| l_carrier_rec.carrier_id||'#'||
2869 l_carrier_rec.ship_method_code||'#'|| l_carrier_rec.mode_of_transport||'#'||
2870 l_carrier_rec.service_level);
2871 END IF;
2872 END IF;
2873
2874 -- 4673778
2875 IF nvl(l_carrier_rec.generic_flag, 'N') = 'Y' THEN
2876 l_attr_tab(1).carrier_id := NULL;
2877 l_attr_tab(1).ship_method_code := NULL;
2878 ELSE
2879 l_attr_tab(1).carrier_id := l_carrier_rec.carrier_id;
2880 l_attr_tab(1).ship_method_code := l_carrier_rec.ship_method_code;
2881 END IF;
2882 l_attr_tab(1).mode_of_transport := l_carrier_rec.mode_of_transport;
2883 l_attr_tab(1).service_level := l_carrier_rec.service_level;
2884
2885 ELSE
2886
2887 -- If the ship method has not changed, we can simply compare the current grouping attributes
2888 -- of the line with the new attributes of the line to see whether they need to be unassigned
2889 -- or not.
2890
2891 l_action_rec.action := 'MATCH_GROUPS';
2892 l_action_rec.check_single_grp := 'Y';
2893
2894
2895 WSH_DELIVERY_AUTOCREATE.Find_Matching_Groups(p_attr_tab => l_attr_tab,
2896 p_action_rec => l_action_rec,
2897 p_target_rec => l_target_rec,
2898 p_group_tab => l_group_tab,
2899 x_matched_entities => l_matched_entities,
2900 x_out_rec => l_out_rec,
2901 x_return_status => x_return_status);
2902
2903
2904 IF x_return_status = WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
2905
2906 l_mandatory_flag := 'N';
2907
2908 ELSIF l_out_rec.single_group = 'N' THEN
2909
2910 l_mandatory_flag := 'Y';
2911
2912 ELSE
2913
2914 RAISE update_not_allowed;
2915
2916 END IF;
2917
2918 END IF;
2919
2920
2921
2922 FOR dd IN c_del_det(p_source_line_id => p_attributes_rec.source_line_id, p_source_code => p_source_code) LOOP
2923
2924 IF l_sm_changed_flag = 'Y' THEN
2925
2926 -- Bug 3292364.
2927 -- If the ship method has changed, we need to compare the new ship method components against
2928 -- the ship method components of the delivery/container that each line is assigned to, to check
2929 -- if the line needs to be unassigned or not.
2930
2931 l_attr_tab(2).carrier_id := dd.carrier_id;
2932 l_attr_tab(2).mode_of_transport := dd.mode_of_transport;
2933 l_attr_tab(2).service_level := dd.service_level;
2934 l_attr_tab(2).ship_method_code := dd.ship_method_code;
2935
2936 --
2937 l_action_rec.action := 'MATCH_GROUPS';
2938 l_action_rec.check_single_grp := 'Y';
2939
2940
2941 WSH_DELIVERY_AUTOCREATE.Find_Matching_Groups(p_attr_tab => l_attr_tab,
2942 p_action_rec => l_action_rec,
2943 p_target_rec => l_target_rec,
2944 p_group_tab => l_group_tab,
2945 x_matched_entities => l_matched_entities,
2946 x_out_rec => l_out_rec,
2947 x_return_status => x_return_status);
2948
2949 IF l_debug_on THEN
2950 WSH_DEBUG_SV.logmsg(l_module_name, 'return status after Find_Matching_Groups: '||x_return_status );
2951 WSH_DEBUG_SV.logmsg(l_module_name, 'l_group_tab.count: '||l_group_tab.count );
2952 WSH_DEBUG_SV.logmsg(l_module_name, 'single group flag: '|| l_out_rec.single_group );
2953 WSH_DEBUG_SV.logmsg(l_module_name, 'service_level: '||l_group_tab(l_group_tab.first).service_level );
2954 WSH_DEBUG_SV.logmsg(l_module_name, 'mode_of_transport: '||l_group_tab(l_group_tab.first).mode_of_transport );
2955 WSH_DEBUG_SV.logmsg(l_module_name, 'carrier_id: '||l_group_tab(l_group_tab.first).carrier_id );
2956 WSH_DEBUG_SV.logmsg(l_module_name, 'ship_method_code: '||l_group_tab(l_group_tab.first).carrier_id );
2957 END IF;
2958
2959 IF x_return_status = WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
2960
2961 l_mandatory_flag := 'N';
2962
2963 ELSIF l_out_rec.single_group = 'N' THEN
2964
2965 l_mandatory_flag := 'Y';
2966
2967 ELSE
2968
2969 RAISE update_not_allowed;
2970
2971 END IF;
2972
2973 l_group_index := l_group_tab.FIRST;
2974
2975 END IF;
2976
2977 IF l_mandatory_flag = 'Y' THEN
2978
2979 IF p_wms_flag = 'Y' THEN
2980
2981 IF dd.released_status = 'Y' THEN
2982
2983 FND_MESSAGE.SET_NAME('WSH', 'WSH_WMS_UPDATE_NOT_ALLOWED');
2984 WSH_UTIL_CORE.Add_Message(x_return_status,l_module_name);
2985 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2986 RAISE update_not_allowed;
2987
2988 END IF;
2989
2990 END IF;
2991
2992
2993
2994 IF l_debug_on THEN
2995 WSH_DEBUG_SV.logmsg(l_module_name, 'INSIDE CHANGE_DELIVERY_GROUP : DO ACTIONS ' );
2996 END IF;
2997 --
2998
2999 IF dd.delivery_id IS NOT NULL THEN
3000
3001 WSH_DELIVERY_DETAILS_ACTIONS.Unassign_Detail_from_Delivery(
3002 p_detail_id => dd.delivery_detail_id,
3003 p_validate_flag => 'N',
3004 x_return_status => x_return_status);
3005
3006 IF (x_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)) THEN
3007
3008 RAISE update_not_allowed;
3009
3010 END IF;
3011
3012 Log_Exception(
3013 p_delivery_detail_id => dd.delivery_detail_id,
3014 p_location_id => dd.ship_from_location_id,
3015 p_exception_name => 'WSH_CHANGE_DEL_GROUP',
3016 p_entity_name => 'WSH_DELIVERY',
3017 p_entity_id => dd.delivery_id,
3018 x_return_status => x_return_status);
3019
3020 IF (x_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)) THEN
3021
3022 RAISE update_not_allowed;
3023
3024 END IF;
3025 -- csun Pack J deliveryMerge
3026 l_delivery_ids.delete;
3027 l_delivery_ids(1) := dd.delivery_id;
3028 WSH_NEW_DELIVERY_ACTIONS.Adjust_Planned_Flag(
3029 p_delivery_ids => l_delivery_ids,
3030 p_caller => 'WSH_DLMG',
3031 p_force_appending_limit => 'N',
3032 p_call_lcss => 'Y',
3033 p_event => NULL,
3034 x_return_status => l_return_status);
3035
3036 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR THEN
3037 RAISE update_not_allowed;
3038 ELSIF l_return_status = WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
3039 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
3040 ELSE
3041 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
3042 END IF;
3043
3044
3045 END IF;
3046
3047 IF dd.parent_delivery_detail_id IS NOT NULL THEN
3048
3049 WSH_DELIVERY_DETAILS_ACTIONS.Unassign_Detail_from_Cont(
3050 p_detail_id => dd.delivery_detail_id,
3051 p_validate_flag => 'N',
3052 x_return_status => x_return_status);
3053
3054 IF (x_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)) THEN
3055
3056 RAISE update_not_allowed;
3057
3058 END IF;
3059
3060 Log_Exception(
3061 p_delivery_detail_id => dd.delivery_detail_id,
3062 p_location_id => dd.ship_from_location_id,
3063 p_exception_name => 'WSH_CHANGE_DEL_GROUP',
3064 p_entity_name => 'WSH_CONTAINER',
3065 p_entity_id => dd.parent_delivery_detail_id,
3066 x_return_status => x_return_status);
3067
3068 IF (x_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)) THEN
3069
3070 RAISE update_not_allowed;
3071
3072 END IF;
3073
3074 END IF;
3075
3076 ELSE -- IF l_mandatory_flag = 'N'
3077
3078
3079 IF dd.delivery_id is not null and l_sm_changed_flag = 'Y' THEN
3080 -- Bug 3292364
3081 -- Even if the line stays on the delivery, but the shipmethod has changed, make sure the
3082 -- delivery is in sync with the line's shipmethod components.
3083
3084 IF l_group_tab(l_group_index).service_level IS NOT NULL
3085 OR l_group_tab(l_group_index).mode_of_transport IS NOT NULL
3086 OR l_group_tab(l_group_index).carrier_id IS NOT NULL THEN
3087
3088
3089 WSH_NEW_DELIVERIES_PVT.Table_to_Record(
3090 p_delivery_id => dd.delivery_id,
3091 x_delivery_rec => l_delivery_info_tab(1),
3092 x_return_status => x_return_status);
3093
3094 IF x_return_status IN (WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR, WSH_UTIL_CORE.G_RET_STS_ERROR) THEN
3095 RAISE update_not_allowed;
3096 END IF;
3097
3098
3099 IF l_group_tab(l_group_index).service_level IS NOT NULL THEN
3100 l_delivery_info_tab(1).service_level := l_group_tab(l_group_index).service_level;
3101 END IF;
3102 IF l_group_tab(l_group_index).carrier_id IS NOT NULL THEN
3103 l_delivery_info_tab(1).carrier_id := l_group_tab(l_group_index).carrier_id;
3104 END IF;
3105 IF l_group_tab(l_group_index).mode_of_transport IS NOT NULL THEN
3106 l_delivery_info_tab(1).mode_of_transport := l_group_tab(l_group_index).mode_of_transport;
3107 END IF;
3108 IF l_group_tab(l_group_index).ship_method_code IS NOT NULL THEN
3109 l_delivery_info_tab(1).ship_method_code := l_group_tab(l_group_index).ship_method_code;
3110 END IF;
3111
3112 IF l_debug_on THEN
3113 WSH_DEBUG_SV.logmsg(l_module_name, 'updating delivery: '||dd.delivery_id );
3114 END IF;
3115
3116 l_del_in_rec.caller := 'WSH';
3117 l_del_in_rec.action_code := 'UPDATE';
3118
3119 WSH_INTERFACE_GRP.Create_Update_Delivery(p_api_version_number => 1.0,
3120 p_init_msg_list => FND_API.G_FALSE,
3121 p_commit => FND_API.G_FALSE,
3122 p_in_rec => l_del_in_rec,
3123 p_rec_attr_tab => l_delivery_info_tab,
3124 x_del_out_rec_tab => l_del_out_rec_tab,
3125 x_return_status => x_return_status,
3126 x_msg_count => l_msg_count,
3127 x_msg_data => l_msg_data);
3128
3129
3130
3131 IF x_return_status IN (WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR, WSH_UTIL_CORE.G_RET_STS_ERROR) THEN
3132 RAISE update_not_allowed;
3133 END IF;
3134
3135 END IF;
3136
3137 END IF;
3138
3139
3140 IF dd.parent_delivery_detail_id IS NOT NULL THEN
3141
3142 IF l_shipment_changed_flag = 'Y' THEN
3143 Log_Exception(
3144 p_delivery_detail_id => dd.delivery_detail_id,
3145 p_location_id => dd.ship_from_location_id,
3146 p_exception_name => 'WSH_INVALID_PACKING',
3147 p_entity_name => 'WSH_CONTAINER',
3148 p_entity_id => dd.parent_delivery_detail_id,
3149 x_return_status => x_return_status);
3150
3151 IF (x_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)) THEN
3152
3153 RAISE update_not_allowed;
3154
3155 END IF;
3156
3157
3158 END IF;
3159 IF l_sm_changed_flag = 'Y' THEN
3160 -- Bug 3292364
3161 -- Even if the line stays on the container, but the shipmethod has changed, make sure the
3162 -- container is in sync with the line's shipmethod components.
3163
3164 IF l_debug_on THEN
3165 WSH_DEBUG_SV.logmsg(l_module_name, 'locking container: '||dd.parent_delivery_detail_id );
3166 END IF;
3167
3168
3169
3170 IF l_group_tab(l_group_index).service_level IS NOT NULL
3171 OR l_group_tab(l_group_index).mode_of_transport IS NOT NULL
3172 OR l_group_tab(l_group_index).carrier_id IS NOT NULL THEN
3173
3174
3175 WSH_DELIVERY_DETAILS_PKG.Table_to_Record(
3176 p_delivery_detail_id => dd.parent_delivery_detail_id,
3177 x_delivery_detail_rec => l_detail_info_tab(1),
3178 x_return_status => x_return_status);
3179
3180 IF x_return_status IN (WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR, WSH_UTIL_CORE.G_RET_STS_ERROR) THEN
3181 RAISE update_not_allowed;
3182 END IF;
3183
3184 IF l_group_tab(l_group_index).service_level IS NOT NULL THEN
3185 l_detail_info_tab(1).service_level := l_group_tab(l_group_index).service_level;
3186 END IF;
3187 IF l_group_tab(l_group_index).carrier_id IS NOT NULL THEN
3188 l_detail_info_tab(1).carrier_id := l_group_tab(l_group_index).carrier_id;
3189 END IF;
3190 IF l_group_tab(l_group_index).mode_of_transport IS NOT NULL THEN
3191 l_detail_info_tab(1).mode_of_transport := l_group_tab(l_group_index).mode_of_transport;
3192 END IF;
3193 IF l_group_tab(l_group_index).ship_method_code IS NOT NULL THEN
3194 l_detail_info_tab(1).ship_method_code := l_group_tab(l_group_index).ship_method_code;
3195 END IF;
3196
3197 IF l_debug_on THEN
3198 WSH_DEBUG_SV.logmsg(l_module_name, 'updating container: '||dd.parent_delivery_detail_id );
3199 END IF;
3200
3201 l_detail_in_rec.caller := 'WSH_USA';
3202 l_detail_in_rec.action_code := 'UPDATE';
3203
3204 WSH_INTERFACE_GRP.Create_Update_Delivery_Detail(
3205 p_api_version_number => 1.0,
3206 p_init_msg_list => FND_API.G_FALSE,
3207 p_commit => FND_API.G_FALSE,
3208 x_return_status => x_return_status,
3209 x_msg_count => l_msg_count,
3210 x_msg_data => l_msg_data,
3211 p_detail_info_tab => l_detail_info_tab,
3212 p_IN_rec => l_detail_in_rec,
3213 x_OUT_rec => l_detail_out_rec);
3214
3215 IF x_return_status IN (WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR, WSH_UTIL_CORE.G_RET_STS_ERROR) THEN
3216 RAISE update_not_allowed;
3217 END IF;
3218
3219 -- The GRP API does not cascade the changes upwards.
3220 -- So we have to make sure to update the hierarchy.
3221
3222 OPEN c_get_parent_container(dd.parent_delivery_detail_id);
3223 FETCH c_get_parent_container INTO l_parent_container;
3224 CLOSE c_get_parent_container;
3225
3226
3227 IF l_parent_container IS NOT NULL THEN
3228
3229
3230 WSH_CONTAINER_ACTIONS.Update_Cont_Hierarchy(
3231 p_del_detail_id => dd.parent_delivery_detail_id,
3232 p_delivery_id => NULL,
3233 p_container_instance_id => l_parent_container,
3234 x_return_status => x_return_status);
3235
3236 IF x_return_status IN (WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR, WSH_UTIL_CORE.G_RET_STS_ERROR) THEN
3237 RAISE update_not_allowed;
3238 END IF;
3239
3240 END IF;
3241
3242 END IF;
3243
3244 END IF;
3245
3246 END IF;
3247
3248 END IF; -- IF l_mandatory_flag = 'Y'
3249
3250
3251 END LOOP;
3252
3253 --
3254 IF l_debug_on THEN
3255 WSH_DEBUG_SV.pop(l_module_name);
3256 END IF;
3257 --
3258 EXCEPTION
3259
3260 WHEN update_not_allowed THEN
3261 x_update_allowed := 'N';
3262 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3263 --
3264 IF l_debug_on THEN
3265 WSH_DEBUG_SV.logmsg(l_module_name,'UPDATE_NOT_ALLOWED exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
3266 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:UPDATE_NOT_ALLOWED');
3267 END IF;
3268 --
3269 RETURN;
3270 WHEN FND_API.G_EXC_ERROR THEN
3271 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3272 IF l_debug_on THEN
3273 wsh_debug_sv.logmsg(l_module_name,'G_EXC_ERROR');
3274 WSH_DEBUG_SV.pop(l_module_name);
3275 END IF;
3276 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3277 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
3278 IF l_debug_on THEN
3279 wsh_debug_sv.logmsg(l_module_name,'G_EXC_UNEXPECTED_ERROR');
3280 WSH_DEBUG_SV.pop(l_module_name);
3281 END IF;
3282 WHEN OTHERS THEN
3283 -- close open cursors as needed
3284 IF c_del_det%ISOPEN THEN
3285 CLOSE c_del_det;
3286 END IF;
3287 IF c_get_parent_container%ISOPEN THEN
3288 CLOSE c_get_parent_container;
3289 END IF;
3290 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
3291 x_update_allowed := 'N';
3292 WSH_UTIL_CORE.DEFAULT_HANDLER('WSH_USA_CATEGORIES_PVT.Change_Delivery_Group',l_module_name);
3293 --
3294 IF l_debug_on THEN
3295 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
3296 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
3297 END IF;
3298 --
3299 END Change_Delivery_Group;
3300
3301
3302 -- Bug 2181132
3303
3304 PROCEDURE Change_Ship_Tolerance(
3305 p_attributes_rec IN WSH_INTERFACE.ChangedAttributeRecType,
3306 p_source_code IN VARCHAR2,
3307 p_wms_flag IN VARCHAR2,
3308 x_update_allowed IN OUT NOCOPY VARCHAR2,
3309 x_return_status OUT NOCOPY VARCHAR2)
3310 IS
3311
3312 -- this is because source_line_set_id may or may not be populated
3313 -- in that case match using source line id
3314 -- query is driven by released status and souce header id.
3315 -- line_set_id can be NULL or MISS_NUM also , when we need to match
3316 -- using source_line_id
3317 CURSOR c_get_released_status is
3318 SELECT delivery_detail_id
3319 FROM wsh_delivery_details
3320 WHERE (source_line_id = p_attributes_rec.source_line_id
3321 OR source_line_set_id = p_attributes_rec.source_line_set_id)
3322 AND source_code = p_source_code
3323 AND source_header_id = p_attributes_rec.source_header_id
3324 AND released_status IN ('Y', 'C')
3325 AND container_flag = 'N'
3326 AND rownum = 1;
3327
3328 l_delivery_detail_id NUMBER;
3329 update_not_allowed EXCEPTION;
3330
3331 -- assuming that OM does not allow changes for tolerance in Line Set
3332 -- do i need source header id also in the cursor??
3333 -- Cases Tol Value Cached Value
3334 -- Null Null
3335 -- Null Not Null
3336 -- Not Null Null
3337 -- Not Null Not Null (same)
3338 -- Not Null Not Null (different)
3339
3340 --
3341 l_debug_on BOOLEAN;
3342 --
3343 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'CHANGE_SHIP_TOLERANCE';
3344 --
3345 BEGIN
3346 --
3347 --
3348 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
3349 --
3350 IF l_debug_on IS NULL
3351 THEN
3352 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
3353 END IF;
3354 --
3355 IF l_debug_on THEN
3356 WSH_DEBUG_SV.push(l_module_name);
3357 --
3358 WSH_DEBUG_SV.log(l_module_name,'P_SOURCE_CODE',P_SOURCE_CODE);
3359 WSH_DEBUG_SV.log(l_module_name,'P_WMS_FLAG',P_WMS_FLAG);
3360 WSH_DEBUG_SV.log(l_module_name,'X_UPDATE_ALLOWED',X_UPDATE_ALLOWED);
3361 END IF;
3362
3363 -- need to write for both tolerance above and below
3364 -- 2181132
3365 -- the code exists in WSHUSAAB.Update_Attributes to update the tolerance values
3366 -- ******************
3367 -- Consult with PM if the tolerance check is Required or not.
3368 -- ******************
3369
3370 IF (
3371 (((p_attributes_rec.ship_tolerance_above <> FND_API.G_MISS_NUM)
3372 OR (p_attributes_rec.ship_tolerance_above IS NULL))
3373 AND
3374 NVL(p_attributes_rec.ship_tolerance_above, FND_API.G_MISS_NUM) <>
3375 NVL(g_cache_detail_rec.ship_tolerance_above, FND_API.G_MISS_NUM))
3376 OR
3377 (((p_attributes_rec.ship_tolerance_below <> FND_API.G_MISS_NUM)
3378 OR (p_attributes_rec.ship_tolerance_below IS NULL))
3379 AND
3380 NVL(p_attributes_rec.ship_tolerance_below, FND_API.G_MISS_NUM) <>
3381 NVL(g_cache_detail_rec.ship_tolerance_below, FND_API.G_MISS_NUM))
3382 ) THEN
3383
3384 IF l_debug_on THEN
3385 WSH_DEBUG_SV.log(l_module_name,'Tolerance Values are different');
3386 END IF;
3387
3388 -- use cursor to determine if the values can be changed
3389 OPEN c_get_released_status;
3390 FETCH c_get_released_status
3391 INTO l_delivery_detail_id;
3392 IF c_get_released_status%NOTFOUND THEN
3393 l_delivery_detail_id := NULL;
3394 END IF;
3395
3396 CLOSE c_get_released_status;
3397
3398 IF l_delivery_detail_id IS NOT NULL THEN
3399 FND_MESSAGE.SET_NAME('WSH', 'WSH_UPDATE_TOL_NOT_ALLOWED');
3400 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3401 WSH_UTIL_CORE.Add_Message(x_return_status);
3402 RAISE update_not_allowed;
3403 END IF;
3404
3405 END IF;
3406
3407 x_update_allowed := 'Y';
3408 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
3409
3410 IF l_debug_on THEN
3411 WSH_DEBUG_SV.log(l_module_name,'Update Allowed'||x_update_allowed);
3412 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
3413 END IF;
3414
3415 EXCEPTION
3416 WHEN update_not_allowed THEN
3417 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3418 x_update_allowed := 'N';
3419
3420 IF l_debug_on THEN
3421 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
3422 END IF;
3423
3424 RETURN;
3425
3426 WHEN OTHERS THEN
3427 -- close open cursors as needed
3428 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
3429 x_update_allowed := 'N';
3430 WSH_UTIL_CORE.DEFAULT_HANDLER('WSH_USA_CATEGORIES_PVT.Change_Ship_Tolerance',l_module_name);
3431 --
3432 IF l_debug_on THEN
3433 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
3434 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
3435 END IF;
3436 --
3437
3438 END Change_Ship_Tolerance;
3439
3440 END WSH_USA_CATEGORIES_PVT;