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