1 PACKAGE BODY WMS_TRIPSTOPS_STAGELANES_PUB AS
2 /* $Header: WMSDKTSB.pls 120.6 2008/03/20 06:36:40 shikapoo ship $ */
3
4 PROCEDURE trace(p_message IN VARCHAR2) iS
5 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
6 BEGIN
7 inv_pick_wave_pick_confirm_pub.tracelog(err_msg => p_message,
8 module => 'WMSDKTSB: ');
9 END trace;
10
11 PROCEDURE get_stgln_for_tripstop(
12 x_return_status OUT NOCOPY VARCHAR2
13 , x_msg_count OUT NOCOPY NUMBER
14 , x_msg_data OUT NOCOPY VARCHAR2
15 , p_org_id IN NUMBER
16 , p_trip_stop IN NUMBER
17 , x_stg_ln_id OUT NOCOPY NUMBER
18 , x_sub_code OUT NOCOPY VARCHAR2)
19 IS
20 l_chk_dkdr_trpstp_exts BOOLEAN;
21 l_dock_id NUMBER := 0;
22 l_staging_lane_id NUMBER := 0;
23 l_stg_ln_id NUMBER := 0;
24
25 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
26 BEGIN
27 x_return_status := FND_API.G_RET_STS_SUCCESS;
28
29 IF (l_debug = 1) THEN
30 trace('in getstgln for tripstop');
31 trace('org id ' || to_char(p_org_id));
32 trace('trip_stop' || to_char(p_trip_stop));
33 END IF;
34 check_dockdoor_tripstop_exists( x_return_status
35 , x_msg_count
36 , x_msg_data
37 , p_trip_stop
38 , l_dock_id
39 , l_staging_lane_id
40 , l_chk_dkdr_trpstp_exts);
41
42 IF (l_debug = 1) THEN
43 trace ('After check_dockdoor_tripstop_exists.');
44 trace('PROCEDURE get_stgln_for_tripstop dock door id = ' || l_dock_id);
45 trace('PROCEDURE get_stgln_for_tripstop staging lane id = ' || l_staging_lane_id);
46 trace(' return status' || x_return_status);
47 END IF;
48
49
50 /*******************************************************************************************************/
51 /* If a record already exists for trip stop in wms_dock_appointments,_b then 'l_chk_dkdr_trpstp_exts' */
52 /* will be set to TRUE. The trip stop/dock door relationship has to be exist beforehand.If it doesn't */
53 /* exist this API will exit. If the trip stop/dock door relationship already exists, then check if a */
54 /* staging lane is already/*assigned to this trip stop. If a staging lane is not already assigned then */
55 /* 'l_staging_lane_id' will be set to '0' and the /*dock door id will be returned in 'l_dock_id'. */
56 /* If a staging lane is already assigned then 'l_staging_lane_id' will be set to that staging lane and */
57 /* will be returned alongwith the 'l_dock_id'. In this case a message will be given that a staging lane*/
58 /* already exists for this trip stop and will be passed to the calling program. */
59 /*******************************************************************************************************/
60
61 IF (l_chk_dkdr_trpstp_exts AND l_dock_id <> 0 AND nvl(l_staging_lane_id,0)=0)
62 --If the trip stop doesn't have a staging lane assigned then...
63 THEN
64 x_stg_ln_id := get_available_staginglane( x_return_status
65 , x_msg_count
66 , x_msg_data
67 , p_trip_stop
68 , l_dock_id);
69 l_stg_ln_id := x_stg_ln_id;
70
71 IF (l_debug = 1) THEN
72 trace('no staaging lane . so assigned it to l_stg_ln_id = ' || l_stg_ln_id );
73 trace('return status from get available staging lane' || x_return_status);
74 END IF;
75 IF l_stg_ln_id <> -1 THEN
76 x_sub_code := get_subinventory_code( x_return_status
77 , x_msg_count
78 , x_msg_data
79 , p_org_id
80 , l_stg_ln_id);
81 IF (l_debug = 1) THEN
82 trace('return status from get_subinventory code' || x_return_status);
83 END IF;
84 ELSE
85 x_stg_ln_id := NULL;
86 x_sub_code := NULL;
87 END IF;
88
89 IF (l_debug = 1) THEN
90 trace('PROCEDURE get_stgln_for_tripstop - IF (chk_dkdr_trpstp_exts AND l_staging_lane_id = 0)staging lane id returned = ' || l_stg_ln_id);
91 trace('PROCEDURE get_stgln_for_tripstop - IF (chk_dkdr_trpstp_exts AND l_staging_lane_id = 0) Subinventory Code returned = ' || x_sub_code);
92 END IF;
93
94 ELSIF (l_staging_lane_id <> 0)
95 --If a staging lane already exists for this trip stop, then return the staging lane id to the calling
96 --program.
97 THEN
98 FND_MESSAGE.SET_NAME('WMS', 'WMS_STGLN_ASSIGNED');
99 FND_MSG_PUB.ADD;
100 --x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
101 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data);
102
103 IF (l_debug = 1) THEN
104 trace('Staging lane already exists for this trip stop');
105 END IF;
106
107 x_stg_ln_id := l_staging_lane_id;
108 l_stg_ln_id := l_staging_lane_id;
109
110 IF (l_debug = 1) THEN
111 trace('Before call to x_sub_code := get_subinventory_code');
112 END IF;
113
114 x_sub_code := get_subinventory_code( x_return_status
115 , x_msg_count
116 , x_msg_data
117 , p_org_id
118 , x_stg_ln_id);
119
120 IF (l_debug = 1) THEN
121 trace('PROCEDURE get_stgln_for_tripstop - IF (chk_dkdr_trpstp_exts AND l_staging_lane_id = 0)staging lane id returned = ' || l_stg_ln_id);
122 trace('PROCEDURE get_stgln_for_tripstop - IF (chk_dkdr_trpstp_exts AND l_staging_lane_id = 0) Subinventory Code returned = ' || x_sub_code);
123 END IF;
124
125
126 ELSIF ((l_dock_id = 0) AND (l_staging_lane_id <> 0))
127 --check if the trip stop has a dock door assigned.
128 THEN
129 FND_MESSAGE.SET_NAME('WMS', 'WMS_DKDR_NOT_EXISTS');
130 FND_MSG_PUB.ADD;
131 --x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
132 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
133
134 IF (l_debug = 1) THEN
135 trace('Dock Door not associated to trip stop');
136 END IF;
137
138 END IF;
139
140 END get_stgln_for_tripstop;
141
142 ----------------------------------------------------------------------------------------------------------
143 /****************************************************************************************************/
144 /* This procedure checks if any of the staging lanes associated with the dock door is available. */
145 /* The dock door associated with the trip stop and the staging lanes associated with the dock */
146 /* is derived in the function "check_dkdr_trpstp_exists".For every staging lane associated with */
147 /* the dock door, this procedure calls function "check_assigned_staginglanes"( checks for staging */
148 /* lanes which have been already assigned). If an available staging lane is not found, then */
149 /* we check for the earliest available staging lane and return that staging lane to the calling */
150 /* program as well as updated in table "wms_dock_appointments_b" for the trip stop in question. */
151 /****************************************************************************************************/
152
153 FUNCTION get_available_staginglane(
154 x_return_status OUT NOCOPY VARCHAR2
155 , x_msg_count OUT NOCOPY NUMBER
156 , x_msg_data OUT NOCOPY VARCHAR2
157 , p_trip_stop IN NUMBER
158 , p_dock_id IN NUMBER)
159 RETURN NUMBER
160 IS
161 CURSOR get_stglanes_for_dkdr_cur IS
162 SELECT stage_lane_id
163 FROM wms_staginglanes_assignments
164 WHERE dock_door_id = p_dock_id
165 AND enabled = 'Y'
166 -- Bug# 4612553: Available staging lanes should be ordered by the number entry sequence
167 ORDER BY entry_sequence;
168
169 x_staging_lane_id NUMBER:= 0;
170 l_loop_counter NUMBER:= 0;
171
172 get_stglanes_for_dkdr_rec get_stglanes_for_dkdr_cur%rowtype;
173
174 l_cur_staging_lane_id NUMBER;
175
176 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
177 BEGIN
178 OPEN get_stglanes_for_dkdr_cur;
179 IF (l_debug = 1) THEN
180 trace('FUNCTION get_available_staginglane trip stop id = ' || p_trip_stop);
181 trace('FUNCTION get_available_staginglane dock door id = ' || p_dock_id);
182 END IF;
183 LOOP
184 IF (l_debug = 1) THEN
185 trace('loop counter = ' || l_loop_counter);
186 END IF;
187 FETCH get_stglanes_for_dkdr_cur into get_stglanes_for_dkdr_rec;
188 IF (l_debug = 1) THEN
189 trace('after fetch');
190 END IF;
191 EXIT WHEN get_stglanes_for_dkdr_cur%NOTFOUND;
192 l_loop_counter := l_loop_counter + 1;
193 IF (l_debug = 1) THEN
194 trace('loop counter = ' || l_loop_counter);
195 END IF;
196
197 IF (l_debug = 1) THEN
198 trace('get_stglanes_for_dkdr_rec.stage_lane_id = : ' || get_stglanes_for_dkdr_rec.stage_lane_id);
199 END IF;
200 l_cur_staging_lane_id := get_stglanes_for_dkdr_rec.stage_lane_id;
201
202 x_staging_lane_id := check_if_stagelane_assigned( x_return_status
203 , x_msg_count
204 , x_msg_data
205 , get_stglanes_for_dkdr_rec.stage_lane_id
206 , p_trip_stop
207 , p_dock_id);
208
209 IF (l_debug = 1) THEN
210 trace('FUNCTION get_available_staginglane loop counter = ' || l_loop_counter);
211 trace('FUNCTION get_available_staginglane staging lane id = ' || x_staging_lane_id);
212 trace('return status from checkif stagelane assigned= ' || x_return_status);
213 END IF;
214
215 --IF (x_staging_lane_id <> 0) or (x_staging_lane_id <> 1) THEN
216 IF x_staging_lane_id not in (0,1)
217 THEN
218 IF (l_debug = 1) THEN
219 trace('FUNCTION get_available_staginglane- IF (x_staging_lane_id <> 0)- staging lane id = ' || x_staging_lane_id);
220 END IF;
221 CLOSE get_stglanes_for_dkdr_cur;
222 RETURN x_staging_lane_id;
223 END IF;
224
225 END LOOP;
226
227 --CLOSE get_stglanes_for_dkdr_cur;
228
229 IF (l_loop_counter = 0 )
230 THEN
231 IF (l_debug = 1) THEN
232 trace('No staging lanes assigned to Dock Door');
233 END IF;
234 FND_MESSAGE.SET_NAME('WMS', 'WMS_NO_ASSIGNED_STGLNS_FOR_DKDR');
235 FND_MSG_PUB.ADD;
236 --x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
237 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data);
238
239
240 CLOSE get_stglanes_for_dkdr_cur;
241
242 ELSIF (l_loop_counter = 1 ) AND x_staging_lane_id = 0 THEN --bug 5117541
243 --means only one staging lane is associated with the dock-door and
244 -- this staging lane IS occupied BY anohter trip. This CASE will arise
245 -- WHEN the Sales ORDER has multiple trip AND ALL trips have
246 -- concurrent dock-door appointment AT the same dock-door
247
248 IF (l_debug = 1) THEN
249 trace('Only one Staging lane in dock-door and engaged - still ASSIGN it');
250 END IF;
251
252 update_staging_lane_id( x_return_status
253 , x_msg_count
254 , x_msg_data
255 , l_cur_staging_lane_id
256 , p_trip_stop
257 , p_dock_id);
258
259 CLOSE get_stglanes_for_dkdr_cur;
260 RETURN l_cur_staging_lane_id;
261
262 END IF;
263
264 CLOSE get_stglanes_for_dkdr_cur;
265
266 IF (l_debug = 1) THEN
267 trace('Out of the loop means that all the staging lanes are taken.');
268 END IF;
269
270 IF x_staging_lane_id in(0,1)
271 THEN
272 IF (l_debug = 1) THEN
273 trace('Inside IF x_staging_lane_id in(0,1) THEN : dock id = ' || p_dock_id);
274 END IF;
275
276
277 x_staging_lane_id := get_earliest_available_stglane(p_dock_id);
278
279 IF (l_debug = 1) THEN
280 trace('After get_earliest_available_stglane has returned staging lane id = ' || x_staging_lane_id);
281 END IF;
282
283 update_staging_lane_id( x_return_status
284 , x_msg_count
285 , x_msg_data
286 , x_staging_lane_id
287 , p_trip_stop
288 , p_dock_id);
289
290 IF (l_debug = 1) THEN
291 trace('After get_earliest_available_stglane(p_dock_id) Staging lane_id = '|| x_staging_lane_id);
292 END IF;
293 RETURN x_staging_lane_id;
294 IF (l_debug = 1) THEN
295 trace('the status returned from update staging lane id is ' || x_return_status);
296 END IF;
297 END IF;
298
299 EXCEPTION
300 WHEN no_data_found THEN
301 IF (l_debug = 1) THEN
302 trace('No staging lanes exist for dock door assigned to this trip stop');
303 END IF;
304 RETURN -1;
305
306 WHEN others THEN
307 IF (l_debug = 1) THEN
308 trace('SQL error code '|| sqlcode||', error message '||sqlerrm);
309 END IF;
310 RETURN -1;
311
312 END get_available_staginglane;
313 ----------------------------------------------------------------------------------------------------------------
314 /****************************************************************************************************/
315 /* For every staging lane returned by the cursor "get_stglanes_for_dkdr_cur", we check if */
316 /* it exists in table wms_dock_appointments_b. If it exists then we move on to the next */
317 /* staging lane assigned to the dock door. The staging lane (p_stg_lane_id) is returned if */
318 /* this staging lane has not been assigned to any appointments in wms_dock_appointments_b */
319 /****************************************************************************************************/
320
321 FUNCTION check_if_stagelane_assigned(
322 x_return_status OUT NOCOPY VARCHAR2
323 , x_msg_count OUT NOCOPY NUMBER
324 , x_msg_data OUT NOCOPY VARCHAR2
325 , p_stg_lane_id IN NUMBER
326 , p_trip_stop IN NUMBER
327 , p_dock_id IN NUMBER)
328 RETURN NUMBER
329 IS
330 l_dummy NUMBER := 0;
331
332 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
333 BEGIN
334 IF (l_debug = 1) THEN
335 trace('Before select in FUNCTION check_if_stagelane_assigned');
336 END IF;
337
338 -- Bug 4915199, changing 'trunc(end_time)>=trunc(sysdate)' to 'end_time>=sysdate'
339 -- to take time of the day into consideration.
340
341 SELECT 1 into l_dummy
342 FROM DUAL WHERE EXISTS (SELECT staging_lane_id
343 FROM wms_dock_appointments_b
344 WHERE staging_lane_id = p_stg_lane_id
345 and end_time>=sysdate);
346
347 -- Bug 3122401. SQL was considering old appointments also. This is
348 -- incorrect. A Staging lane should only be considered as
349 -- unavailable IF it has an appointment FOR the same day OR
350 -- the future
351 IF (l_debug = 1) THEN
352 trace('Staging lane has been already assigned and so return is 0');
353 END IF;
354
355 RETURN 0;
356
357
358 EXCEPTION
359 WHEN no_data_found THEN
360 update_staging_lane_id( x_return_status
361 , x_msg_count
362 , x_msg_data
363 , p_stg_lane_id
364 , p_trip_stop
365 , p_dock_id);
366 RETURN p_stg_lane_id;
367 IF (l_debug = 1) THEN
368 trace('Exception WHEN NO_DATA_FOUND in FUNCTION check_if_stagelane_assigned');
369 END IF;
370
371 WHEN too_many_rows THEN
372 IF (l_debug = 1) THEN
373 trace('Exception WHEN too_many_rows in FUNCTION check_if_stagelane_assigned');
374 END IF;
375 RETURN 1;
376
377 WHEN others THEN
378 IF (l_debug = 1) THEN
379 trace('Exception WHEN OTHERS in FUNCTION check_if_stagelane_assigned');
380 END IF;
381 RETURN 1;
382
383 END check_if_stagelane_assigned;
384
385
386 /****************************************************************************************************/
387 /* */
388 /* */
389 /* */
390 /****************************************************************************************************/
391
392 FUNCTION get_earliest_available_stglane( p_dock_id IN NUMBER)
393 RETURN NUMBER
394 IS
395 x_stage_id NUMBER := 0;
396 l_date_end_time VARCHAR2(30);
397 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
398 BEGIN
399 IF (l_debug = 1) THEN
400 trace('FUNCTION get_earliest_available_stglane - dock door id passed = ' || p_dock_id);
401 END IF;
402
403
404 -- Bug 4915199, changing 'trunc(end_time)>=trunc(sysdate)' to 'end_time>=sysdate'
405 -- to take time of the day into consideration.
406
407 BEGIN
408 SELECT staging_lane_id, to_char(end_time, 'DD-MON-YY HH:MI:SS')
409 INTO x_stage_id, l_date_end_time
410 FROM wms_dock_appointments_b
411 WHERE end_time = (SELECT min(end_time)
412 FROM wms_dock_appointments_b
413 WHERE dock_id = p_dock_id
414 AND staging_lane_id is not NULL
415 AND end_time>=sysdate)
416 AND staging_lane_id is not NULL
417 AND rownum = 1
418 AND dock_id=p_dock_id;
419 EXCEPTION
420 when others then
421 x_stage_id:= null;
422 l_date_end_time := null;
423 END;
424
425
426 -- Bug 3122401. SQL was considering old appointments also. This is
427 -- incorrect. A Staging lane should only be considered as
428 -- unavailable IF it has an appointment FOR the same day OR
429 -- the future. Also the staging lane selected was not limited to
430 -- the same dock door
431
432
433 IF (l_debug = 1) THEN
434 trace('FUNCTION get_earliest_available_stglane - Stage lane id selected = ' || x_stage_id);
435 trace('FUNCTION get_earliest_available_stglane - End time = ' || l_date_end_time);
436 END IF;
437
438 RETURN x_stage_id;
439
440 EXCEPTION
441 WHEN no_data_found THEN
442 IF (l_debug = 1) THEN
443 trace('WHEN NO_DATA_FOUND in FUNCTION get_earliest_available_stglane');
444 END IF;
445 null;
446
447 WHEN OTHERS THEN
448 IF (l_debug = 1) THEN
449 trace('when other in get_earliest_availabe_stglane');
450 END IF;
451 null;
452 END get_earliest_available_stglane;
453
454
455 /****************************************************************************************************/
456 /* */
457 /* */
458 /* */
459 /****************************************************************************************************/
460
461 PROCEDURE check_dockdoor_tripstop_exists(
462 x_return_status OUT NOCOPY VARCHAR2
463 , x_msg_count OUT NOCOPY NUMBER
464 , x_msg_data OUT NOCOPY VARCHAR2
465 , p_trip_stop IN NUMBER
466 , x_dock_id OUT NOCOPY NUMBER
467 , x_staging_lane_id OUT NOCOPY NUMBER
468 , x_dkdr_trpstp_exists OUT NOCOPY BOOLEAN)
469 IS
470 -- l_dkdr_trpstp_exists BOOLEAN := TRUE;
471 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
472 BEGIN
473 IF (l_debug = 1) THEN
474 trace('Before select in FUNCTION check_dockdoor_tripstop_exists trip stop = ' || p_trip_stop);
475 END IF;
476
477 -- Bug 4915199, adding filter on end_date to choose earliest future date.
478 SELECT nvl(dock_id,0), nvl(staging_lane_id ,0)
479 INTO x_dock_id, x_staging_lane_id
480 FROM wms_dock_appointments_b
481 WHERE trip_stop = p_trip_stop
482 and end_time = (SELECT min(end_time)
483 FROM wms_dock_appointments_b
484 WHERE trip_stop = p_trip_stop
485 and end_time >= sysdate)
486 and rownum = 1;
487
488 IF (l_debug = 1) THEN
489 trace('FUNCTION check_dockdoor_tripstop_exists dock door id ' || x_dock_id);
490 trace('FUNCTION check_dockdoor_tripstop_exists staging lane id ' || x_staging_lane_id);
491 END IF;
492
493 x_dkdr_trpstp_exists := TRUE ;
494 x_return_status := FND_API.G_RET_STS_SUCCESS; --returns success value
495
496 EXCEPTION
497 WHEN no_data_found THEN
498 IF (l_debug = 1) THEN
499 trace('There is no trip stop with this number' || p_trip_stop );
500 END IF;
501 FND_MESSAGE.SET_NAME('WMS', 'WMS_NO_TRIPSTOP_EXISTS');
502 FND_MSG_PUB.ADD;
503 --x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
504 FND_MSG_PUB.Count_And_Get
505 (
506 p_count => x_msg_count
507 , p_data => x_msg_data
508 );
509
510
511 x_dkdr_trpstp_exists := FALSE ;
512 x_dock_id := 0;
513 x_staging_lane_id := 0;
514
515 IF (l_debug = 1) THEN
516 trace('FUNCTION check_dockdoor_tripstop_exists trip stops' || p_trip_stop);
517 trace('FUNCTION check_dockdoor_tripstop_exists dock id' || x_dock_id);
518 trace('FUNCTION check_dockdoor_tripstop_exists staging lane id' || x_staging_lane_id);
519 END IF;
520
521 -- RETURN l_dkdr_trpstp_exists;
522
523 WHEN TOO_MANY_ROWS THEN
524 IF (l_debug = 1) THEN
525 trace('There is more than one record which matches this trip stop');
526 END IF;
527 FND_MESSAGE.SET_NAME('WMS', 'WMS_DUP_TRIPSTOPS');
528 FND_MSG_PUB.ADD;
529 --x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
530 FND_MSG_PUB.Count_And_Get
531 (
532 p_count => x_msg_count
533 , p_data => x_msg_data
534 );
535
536 x_dkdr_trpstp_exists := TRUE ;
537 --x_dock_id := 0;
538 --x_staging_lane_id := 0;
539 --RETURN l_dkdr_trpstp_exists;
540
541 WHEN OTHERS THEN
542 IF (l_debug = 1) THEN
543 trace('Inside When Others');
544 END IF;
545 x_dkdr_trpstp_exists := FALSE ;
546 x_dock_id := 0;
547 x_staging_lane_id := 0;
548 -- RETURN l_dkdr_trpstp_exists;
549
550 END check_dockdoor_tripstop_exists;
551
552
553 /****************************************************************************************************/
554 /* */
555 /* */
556 /* */
557 /****************************************************************************************************/
558
559 PROCEDURE update_staging_lane_id(
560 x_return_status OUT NOCOPY VARCHAR2
561 , x_msg_count OUT NOCOPY NUMBER
562 , x_msg_data OUT NOCOPY VARCHAR2
563 , p_stage_lane_id IN NUMBER
564 , p_trip_stop IN NUMBER
565 , p_dock_id IN NUMBER)
566 IS
567 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
568 BEGIN
569 SAVEPOINT update_dock_sp;
570 --x_return_status := FND_API.G_RET_STS_SUCCESS;
571 IF (l_debug = 1) THEN
572 trace('Before update PROCEDURE update_staging_lane_id = ' || p_stage_lane_id);
573 trace('Before update PROCEDURE update_staging_lane_id = ' || p_trip_stop);
574 trace('Before update PROCEDURE update_staging_lane_id = ' || p_dock_id);
575 END IF;
576 UPDATE wms_dock_appointments_b
577 SET
578 STAGING_LANE_ID = p_stage_lane_id,
579 LAST_UPDATED_BY = 1,
580 LAST_UPDATE_DATE = sysdate,
581 LAST_UPDATE_LOGIN = 1
582 WHERE DOCK_ID = p_dock_id
583 AND TRIP_STOP = p_trip_stop;
584
585 x_return_status := FND_API.G_RET_STS_SUCCESS;
586 IF (l_debug = 1) THEN
587 trace('After update PROCEDURE update_staging_lane_id');
588 END IF;
589
590 COMMIT;
591
592 EXCEPTION
593 WHEN OTHERS THEN
594 ROLLBACK TO update_dock_sp;
595 --x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
596
597 FND_MESSAGE.SET_NAME('WMS', 'WMS_DOCK_UPDATE_FAIL');
598 FND_MSG_PUB.ADD;
599
600 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,p_data => x_msg_data);
601
602 END update_staging_lane_id;
603
604
605 /****************************************************************************************************/
606 /* */
607 /* */
608 /* */
609 /****************************************************************************************************/
610 FUNCTION get_subinventory_code(
611 x_return_status OUT NOCOPY VARCHAR2
612 , x_msg_count OUT NOCOPY NUMBER
613 , x_msg_data OUT NOCOPY VARCHAR2
614 , p_org_id IN NUMBER
615 , p_staging_lane_id IN NUMBER)
616 RETURN VARCHAR2
617 IS
618 l_sub_code VARCHAR2(30);
619 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
620 BEGIN
621 IF (l_debug = 1) THEN
622 trace('Before select in FUNCTION get_subinventory_code Staging lane = '||p_staging_lane_id);
623 END IF;
624
625 SELECT subinventory_code
626 INTO l_sub_code
627 FROM mtl_item_locations
628 WHERE INVENTORY_LOCATION_ID = p_staging_lane_id
629 AND ORGANIZATION_ID = p_org_id;
630
631 IF (l_debug = 1) THEN
632 trace('FUNCTION get_subinventory_code staging lane id = ' || p_staging_lane_id);
633 trace('FUNCTION get_subinventory_code subinventory_code = ' || l_sub_code);
634 trace('FUNCTION get_subinventory_code Organization Id = ' || p_org_id);
635 END IF;
636
637 x_return_status := FND_API.G_RET_STS_SUCCESS; -- returns success values
638 RETURN l_sub_code;
639
640 EXCEPTION
641 WHEN no_data_found THEN
642 IF (l_debug = 1) THEN
643 trace('INSIDE exception no_data_found in FUNCTION get_subinventory_code Staging lane = '||p_staging_lane_id);
644 END IF;
645 l_sub_code := null;
646
647 IF (l_debug = 1) THEN
648 trace('FUNCTION get_subinventory_code staging lane id = ' || p_staging_lane_id);
649 trace('FUNCTION get_subinventory_code subinventory_code = ' || l_sub_code);
650 trace('FUNCTION get_subinventory_code Organization Id = ' || p_org_id);
651 END IF;
652
653 FND_MESSAGE.SET_NAME('WMS', 'WMS_NO_SUBINV_ASSIGNED_TO_LOCATION');
654 FND_MSG_PUB.ADD;
655 --x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
656 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data);
657 RETURN l_sub_code;
658
659 WHEN too_many_rows THEN
660 l_sub_code := null;
661 FND_MESSAGE.SET_NAME('WMS', 'WMS_TOO_MANY_ROWS');
662 FND_MSG_PUB.ADD;
663 --x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
664 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
665
666 RETURN l_sub_code;
667
668 END get_subinventory_code;
669
670
671 END WMS_TRIPSTOPS_STAGELANES_PUB;
672
[Home] [Help]