[Home] [Help]
PACKAGE BODY: APPS.WMS_DOCK_APPOINTMENTS_PUB
Source
1 PACKAGE BODY WMS_DOCK_APPOINTMENTS_PUB AS
2 /* $Header: WMSDKAPB.pls 120.8 2008/05/02 07:00:15 shikapoo ship $ */
3
4 --Global variable to hold the package name
5 G_PKG_NAME CONSTANT VARCHAR2(30) := 'WMS_DOCK_APPOINTMENTS_PUB';
6
7 --Global variable used in print_debug utility
8 G_VERSION_PRINTED BOOLEAN := FALSE;
9
10 g_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'), 0);
11
12 /* Debug Utility*/
13
14 PROCEDURE print_debug
15 (
16 p_err_msg VARCHAR2
17 , p_level NUMBER
18 ) IS
19 BEGIN
20 IF (g_debug = 1) THEN
21 IF(G_VERSION_PRINTED = FALSE ) THEN
22 inv_log_util.trace (
23 p_message => '$Header: WMSDKAPB.pls 120.8 2008/05/02 07:00:15 shikapoo ship $'
24 , p_module => G_PKG_NAME
25 , p_level => 9);
26 G_VERSION_PRINTED :=TRUE;
27 END IF;
28 inv_log_util.trace (
29 p_message => p_err_msg
30 , p_module => G_PKG_NAME
31 , p_level => p_level);
32 END IF;
33 --dbms_output.put_line(substr(p_err_msg,1,200));
34 END print_debug;
35
36 FUNCTION get_trip_stop
37 (
38 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
39 , x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER
40 , x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
41 , p_dock_locator_id IN NUMBER)
42 RETURN NUMBER
43 IS
44 l_trip_stop NUMBER := -1;
45 BEGIN
46 SELECT trip_stop
47 INTO l_trip_stop
48 FROM wms_dock_appointments_b
49 WHERE dock_id = p_dock_locator_id
50 AND appointment_status = 2 -- occupied
51 AND start_time <= Sysdate
52 AND end_time >= Sysdate;
53
54 RETURN l_trip_stop;
55
56 EXCEPTION
57 WHEN no_data_found THEN
58 x_return_status := FND_API.G_RET_STS_ERROR;
59
60 FND_MESSAGE.SET_NAME('WMS', 'WMS_TRIP_STOP_NOT_FOUND');
61 FND_MSG_PUB.ADD;
62
63 FND_MSG_PUB.Count_And_Get
64 (p_count => x_msg_count,
65 p_data => x_msg_data
66 );
67
68 RETURN l_trip_stop;
69
70 WHEN OTHERS THEN
71 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
72 RETURN l_trip_stop;
73
74 END get_trip_stop;
75
76
77
78 PROCEDURE update_dock_appointment
79 (
80 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
81 x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
82 x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
83 p_dock_appointments_v_rec IN wms_dock_appointments_v%ROWTYPE
84 )
85 IS
86 BEGIN
87 SAVEPOINT update_dock_sp;
88 -- Initialize API return status to success
89 x_return_status := FND_API.G_RET_STS_SUCCESS;
90
91 UPDATE wms_dock_appointments_b
92 SET
93 APPOINTMENT_TYPE = p_dock_appointments_v_rec.APPOINTMENT_TYPE,
94 DOCK_ID = p_dock_appointments_v_rec.DOCK_ID,
95 ORGANIZATION_ID = p_dock_appointments_v_rec.ORGANIZATION_ID,
96 START_TIME = p_dock_appointments_v_rec.START_TIME,
97 END_TIME = p_dock_appointments_v_rec.END_TIME,
98 CARRIER_CODE = p_dock_appointments_v_rec.CARRIER_CODE,
99 TRIP_STOP = p_dock_appointments_v_rec.TRIP_STOP,
100 SOURCE_TYPE = p_dock_appointments_v_rec.SOURCE_TYPE,
101 SOURCE_HEADER_ID = p_dock_appointments_v_rec.SOURCE_HEADER_ID,
102 SOURCE_LINE_ID = p_dock_appointments_v_rec.SOURCE_LINE_ID,
103 CREATED_BY = p_dock_appointments_v_rec.CREATED_BY,
104 CREATION_DATE = p_dock_appointments_v_rec.CREATION_DATE,
105 LAST_UPDATED_BY = p_dock_appointments_v_rec.LAST_UPDATED_BY,
106 LAST_UPDATE_DATE = p_dock_appointments_v_rec.LAST_UPDATE_DATE,
107 LAST_UPDATE_LOGIN = p_dock_appointments_v_rec.LAST_UPDATE_LOGIN
108 WHERE
109 DOCK_APPOINTMENT_ID = p_dock_appointments_v_rec.DOCK_APPOINTMENT_ID;
110
111
112 UPDATE wms_dock_appointments_tl
113 SET
114 SUBJECT = p_dock_appointments_v_rec.SUBJECT,
115 DESCRIPTION = p_dock_appointments_v_rec.DESCRIPTION,
116 SOURCE_LANG = USERENV('LANG'),
117 CREATED_BY = p_dock_appointments_v_rec.CREATED_BY,
118 CREATION_DATE = p_dock_appointments_v_rec.CREATION_DATE,
119 LAST_UPDATED_BY = p_dock_appointments_v_rec.LAST_UPDATED_BY,
120 LAST_UPDATE_DATE = p_dock_appointments_v_rec.LAST_UPDATE_DATE,
121 LAST_UPDATE_LOGIN = p_dock_appointments_v_rec.LAST_UPDATE_LOGIN
122 WHERE
123 DOCK_APPOINTMENT_ID = p_dock_appointments_v_rec.dock_appointment_id
124 AND userenv('LANG') in (LANGUAGE, SOURCE_LANG);
125
126 EXCEPTION
127 WHEN OTHERS THEN
128 ROLLBACK TO update_dock_sp;
129 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
130
131 FND_MESSAGE.SET_NAME('WMS', 'WMS_DOCK_UPDATE_FAIL');
135 (p_count => x_msg_count,
132 FND_MSG_PUB.ADD;
133
134 FND_MSG_PUB.Count_And_Get
136 p_data => x_msg_data
137 );
138
139 END update_dock_appointment;
140
141
142 PROCEDURE update_rep_appointments
143 (
144 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
145 x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
146 x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
147 p_orig_id IN NUMBER,
148 p_dock_appointments_v_rec IN wms_dock_appointments_v%ROWTYPE
149 )
150 IS
151 BEGIN
152 SAVEPOINT update_rep_dock_sp;
153 -- Initialize API return status to success
154 x_return_status := FND_API.G_RET_STS_SUCCESS;
155
156 UPDATE wms_dock_appointments_b
157 SET
158 APPOINTMENT_TYPE = p_dock_appointments_v_rec.APPOINTMENT_TYPE,
159 DOCK_ID = p_dock_appointments_v_rec.DOCK_ID,
160 ORGANIZATION_ID = p_dock_appointments_v_rec.ORGANIZATION_ID,
161 START_TIME = trunc(start_time) + (p_dock_appointments_v_rec.START_TIME - trunc(p_dock_appointments_v_rec.START_TIME)),
162 END_TIME = trunc(end_time) + (p_dock_appointments_v_rec.END_TIME - trunc(p_dock_appointments_v_rec.END_TIME)),
163 CARRIER_CODE = p_dock_appointments_v_rec.CARRIER_CODE,
164 TRIP_STOP = p_dock_appointments_v_rec.TRIP_STOP,
165 SOURCE_TYPE = p_dock_appointments_v_rec.SOURCE_TYPE,
166 SOURCE_HEADER_ID = p_dock_appointments_v_rec.SOURCE_HEADER_ID,
167 SOURCE_LINE_ID = p_dock_appointments_v_rec.SOURCE_LINE_ID,
168 CREATED_BY = p_dock_appointments_v_rec.CREATED_BY,
169 CREATION_DATE = p_dock_appointments_v_rec.CREATION_DATE,
170 LAST_UPDATED_BY = p_dock_appointments_v_rec.LAST_UPDATED_BY,
171 LAST_UPDATE_DATE = p_dock_appointments_v_rec.LAST_UPDATE_DATE,
172 LAST_UPDATE_LOGIN = p_dock_appointments_v_rec.LAST_UPDATE_LOGIN
173 WHERE
174 REP_ORIGIN = p_orig_id;
175
176
177 UPDATE wms_dock_appointments_tl
178 SET
179 SUBJECT = p_dock_appointments_v_rec.SUBJECT,
180 DESCRIPTION = p_dock_appointments_v_rec.DESCRIPTION,
181 SOURCE_LANG = USERENV('LANG'),
182 CREATED_BY = p_dock_appointments_v_rec.CREATED_BY,
183 CREATION_DATE = p_dock_appointments_v_rec.CREATION_DATE,
184 LAST_UPDATED_BY = p_dock_appointments_v_rec.LAST_UPDATED_BY,
185 LAST_UPDATE_DATE = p_dock_appointments_v_rec.LAST_UPDATE_DATE,
186 LAST_UPDATE_LOGIN = p_dock_appointments_v_rec.LAST_UPDATE_LOGIN
187 WHERE
188 DOCK_APPOINTMENT_ID IN
189 (SELECT dock_appointment_id
190 FROM wms_dock_appointments_b
191 WHERE REP_ORIGIN = p_orig_id)
192 AND userenv('LANG') in (LANGUAGE, SOURCE_LANG);
193
194 EXCEPTION
195 WHEN OTHERS THEN
196 ROLLBACK TO update_rep_dock_sp;
197 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
198
199 FND_MESSAGE.SET_NAME('WMS', 'WMS_DOCK_UPDATE_REP_FAIL');
200 FND_MSG_PUB.ADD;
201
202 FND_MSG_PUB.Count_And_Get
203 (p_count => x_msg_count,
204 p_data => x_msg_data
205 );
206
207 END update_rep_appointments;
208
209
210 PROCEDURE insert_dock_appointment
211 (
212 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
213 , x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER
214 , x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
215 , p_dock_appointments_v_rec IN wms_dock_appointments_v%ROWTYPE
216 )
217 IS
218 BEGIN
219 SAVEPOINT insert_dock_sp;
220 -- Initialize API return status to success
221 x_return_status := FND_API.G_RET_STS_SUCCESS;
222
223 INSERT INTO wms_dock_appointments_b
224 (DOCK_APPOINTMENT_ID,
225 APPOINTMENT_TYPE,
226 DOCK_ID,
227 ORGANIZATION_ID,
228 START_TIME,
229 END_TIME,
230 CARRIER_CODE,
231 TRIP_STOP,
232 SOURCE_TYPE,
233 SOURCE_HEADER_ID,
234 SOURCE_LINE_ID,
235 CREATED_BY,
236 CREATION_DATE,
237 LAST_UPDATED_BY,
238 LAST_UPDATE_DATE,
239 LAST_UPDATE_LOGIN,
240 REP_FREQUENCY,
241 REP_START_DATE,
242 REP_END_DATE,
243 REP_ORIGIN,
244 APPOINTMENT_STATUS)
245 VALUES
246 (p_dock_appointments_v_rec.DOCK_APPOINTMENT_ID,
247 p_dock_appointments_v_rec.APPOINTMENT_TYPE,
248 p_dock_appointments_v_rec.DOCK_ID,
249 p_dock_appointments_v_rec.ORGANIZATION_ID,
250 p_dock_appointments_v_rec.START_TIME,
251 p_dock_appointments_v_rec.END_TIME,
252 p_dock_appointments_v_rec.CARRIER_CODE,
253 p_dock_appointments_v_rec.TRIP_STOP,
254 p_dock_appointments_v_rec.SOURCE_TYPE,
255 p_dock_appointments_v_rec.SOURCE_HEADER_ID,
256 p_dock_appointments_v_rec.SOURCE_LINE_ID,
257 p_dock_appointments_v_rec.CREATED_BY,
258 p_dock_appointments_v_rec.CREATION_DATE,
259 p_dock_appointments_v_rec.LAST_UPDATED_BY,
260 p_dock_appointments_v_rec.LAST_UPDATE_DATE,
261 p_dock_appointments_v_rec.LAST_UPDATE_LOGIN,
262 p_dock_appointments_v_rec.REP_FREQUENCY,
263 p_dock_appointments_v_rec.REP_START_DATE,
264 p_dock_appointments_v_rec.REP_END_DATE,
265 p_dock_appointments_v_rec.DOCK_APPOINTMENT_ID,
266 p_dock_appointments_v_rec.APPOINTMENT_STATUS);
267
268 INSERT INTO wms_dock_appointments_tl
269 (DOCK_APPOINTMENT_ID,
270 SUBJECT,
271 DESCRIPTION,
272 LANGUAGE,
273 SOURCE_LANG,
274 CREATED_BY,
278 LAST_UPDATE_LOGIN)
275 CREATION_DATE,
276 LAST_UPDATED_BY,
277 LAST_UPDATE_DATE,
279 SELECT
280 p_dock_appointments_v_rec.DOCK_APPOINTMENT_ID,
281 p_dock_appointments_v_rec.SUBJECT,
282 p_dock_appointments_v_rec.DESCRIPTION,
283 l.language_code,
284 USERENV('LANG'),
285 p_dock_appointments_v_rec.CREATED_BY,
286 p_dock_appointments_v_rec.CREATION_DATE,
287 p_dock_appointments_v_rec.LAST_UPDATED_BY,
288 p_dock_appointments_v_rec.LAST_UPDATE_DATE,
289 p_dock_appointments_v_rec.LAST_UPDATE_LOGIN
290 FROM fnd_languages l
291 WHERE l.installed_flag IN ('I', 'B')
292 AND NOT exists
293 (SELECT NULL
294 FROM wms_dock_appointments_tl t
295 WHERE t.dock_appointment_id = p_dock_appointments_v_rec.dock_appointment_id
296 AND t.language = l.language_code);
297
298 EXCEPTION
299 WHEN OTHERS THEN
300 ROLLBACK TO insert_dock_sp;
301 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
302
303 FND_MESSAGE.SET_NAME('WMS', 'WMS_DOCK_INSERT_FAIL');
304 FND_MSG_PUB.ADD;
305
306 FND_MSG_PUB.Count_And_Get
307 (p_count => x_msg_count,
308 p_data => x_msg_data
309 );
310 END insert_dock_appointment;
311
312
313 PROCEDURE insert_rep_dock_appointments
314 (
315 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
316 , x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER
317 , x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
318 , p_rep_orig_id IN NUMBER
319 , p_dock_appointments_v_rec IN wms_dock_appointments_v%ROWTYPE
320 )
321 IS
322 BEGIN
323 SAVEPOINT insert_rep_dock_sp;
324 -- Initialize API return status to success
325 x_return_status := FND_API.G_RET_STS_SUCCESS;
326
327 INSERT INTO wms_dock_appointments_b
328 (DOCK_APPOINTMENT_ID,
329 APPOINTMENT_TYPE,
330 DOCK_ID,
331 ORGANIZATION_ID,
332 START_TIME,
333 END_TIME,
334 CARRIER_CODE,
335 TRIP_STOP,
336 SOURCE_TYPE,
337 SOURCE_HEADER_ID,
338 SOURCE_LINE_ID,
339 CREATED_BY,
340 CREATION_DATE,
341 LAST_UPDATED_BY,
342 LAST_UPDATE_DATE,
343 LAST_UPDATE_LOGIN,
344 REP_FREQUENCY,
345 REP_START_DATE,
346 REP_END_DATE,
347 REP_ORIGIN,
348 APPOINTMENT_STATUS)
349 VALUES
350 (p_dock_appointments_v_rec.DOCK_APPOINTMENT_ID,
351 p_dock_appointments_v_rec.APPOINTMENT_TYPE,
352 p_dock_appointments_v_rec.DOCK_ID,
353 p_dock_appointments_v_rec.ORGANIZATION_ID,
354 p_dock_appointments_v_rec.START_TIME,
355 p_dock_appointments_v_rec.END_TIME,
356 p_dock_appointments_v_rec.CARRIER_CODE,
357 p_dock_appointments_v_rec.TRIP_STOP,
358 p_dock_appointments_v_rec.SOURCE_TYPE,
359 p_dock_appointments_v_rec.SOURCE_HEADER_ID,
360 p_dock_appointments_v_rec.SOURCE_LINE_ID,
361 p_dock_appointments_v_rec.CREATED_BY,
362 p_dock_appointments_v_rec.CREATION_DATE,
363 p_dock_appointments_v_rec.LAST_UPDATED_BY,
364 p_dock_appointments_v_rec.LAST_UPDATE_DATE,
365 p_dock_appointments_v_rec.LAST_UPDATE_LOGIN,
366 p_dock_appointments_v_rec.REP_FREQUENCY,
367 p_dock_appointments_v_rec.REP_START_DATE,
368 p_dock_appointments_v_rec.REP_END_DATE,
369 p_rep_orig_id,
370 p_dock_appointments_v_rec.APPOINTMENT_STATUS);
371
372 INSERT INTO wms_dock_appointments_tl
373 (DOCK_APPOINTMENT_ID,
374 SUBJECT,
375 DESCRIPTION,
376 LANGUAGE,
377 SOURCE_LANG,
378 CREATED_BY,
379 CREATION_DATE,
380 LAST_UPDATED_BY,
381 LAST_UPDATE_DATE,
382 LAST_UPDATE_LOGIN)
383 SELECT
384 p_dock_appointments_v_rec.DOCK_APPOINTMENT_ID,
385 p_dock_appointments_v_rec.SUBJECT,
386 p_dock_appointments_v_rec.DESCRIPTION,
387 l.language_code,
388 USERENV('LANG'),
389 p_dock_appointments_v_rec.CREATED_BY,
390 p_dock_appointments_v_rec.CREATION_DATE,
391 p_dock_appointments_v_rec.LAST_UPDATED_BY,
392 p_dock_appointments_v_rec.LAST_UPDATE_DATE,
393 p_dock_appointments_v_rec.last_update_login
394 FROM fnd_languages l
395 WHERE l.installed_flag IN ('I', 'B')
396 AND NOT exists
397 (SELECT NULL
398 FROM wms_dock_appointments_tl t
399 WHERE t.dock_appointment_id = p_dock_appointments_v_rec.dock_appointment_id
400 AND t.language = l.language_code);
401
402 EXCEPTION
403 WHEN OTHERS THEN
404 ROLLBACK TO insert_rep_dock_sp;
405 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
406
407 FND_MESSAGE.SET_NAME('WMS', 'WMS_DOCK_INSERT_REP_FAIL');
408 FND_MSG_PUB.ADD;
409
410 FND_MSG_PUB.Count_And_Get
411 (p_count => x_msg_count,
412 p_data => x_msg_data
413 );
414 END insert_rep_dock_appointments;
415
416
417
418 PROCEDURE delete_dock_appointment
419 (
420 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
421 , x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER
422 , x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
423 , p_dock_appointment_id IN NUMBER
424 )
425 IS
426 BEGIN
427 SAVEPOINT delete_dock_sp;
428 -- Initialize API return status to success
432 WHERE DOCK_APPOINTMENT_ID = p_dock_appointment_id;
429 x_return_status := FND_API.G_RET_STS_SUCCESS;
430
431 DELETE wms_dock_appointments_b
433
434 DELETE wms_dock_appointments_tl
435 WHERE DOCK_APPOINTMENT_ID = p_dock_appointment_id;
436
437 EXCEPTION
438 WHEN OTHERS THEN
439 ROLLBACK TO delete_dock_sp;
440 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
441
442 FND_MESSAGE.SET_NAME('WMS', 'WMS_DOCK_DELETE_FAIL');
443 FND_MSG_PUB.ADD;
444
445 FND_MSG_PUB.Count_And_Get
446 (p_count => x_msg_count,
447 p_data => x_msg_data
448 );
449 END delete_dock_appointment;
450
451
452 PROCEDURE delete_rep_dock_appointment
453 (
454 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
455 , x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER
456 , x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
457 , p_rep_orig_id IN NUMBER
458 )
459 IS
460 BEGIN
461 SAVEPOINT delete_rep_dock_sp;
462 -- Initialize API return status to success
463 x_return_status := FND_API.G_RET_STS_SUCCESS;
464
465 DELETE wms_dock_appointments_tl
466 WHERE dock_appointment_id IN
467 (SELECT dock_appointment_id
468 FROM wms_dock_appointments_b
469 WHERE REP_ORIGIN = p_rep_orig_id);
470
471 DELETE wms_dock_appointments_b
472 WHERE REP_ORIGIN = p_rep_orig_id;
473
474 EXCEPTION
475 WHEN OTHERS THEN
476 ROLLBACK TO delete_rep_dock_sp;
477 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
478
479 FND_MESSAGE.SET_NAME('WMS', 'WMS_DOCK_DELETE_REP_FAIL');
480 FND_MSG_PUB.ADD;
481
482 FND_MSG_PUB.Count_And_Get
483 (p_count => x_msg_count,
484 p_data => x_msg_data
485 );
486 END delete_rep_dock_appointment;
487
488 PROCEDURE ADD_LANGUAGE
489 IS
490 BEGIN
491 DELETE FROM WMS_DOCK_APPOINTMENTS_TL T
492 WHERE NOT exists
493 (SELECT NULL
494 FROM WMS_DOCK_APPOINTMENTS_B B
495 WHERE B.DOCK_APPOINTMENT_ID = T.DOCK_APPOINTMENT_ID
496 );
497
498 UPDATE WMS_DOCK_APPOINTMENTS_TL T
499 SET (
500 SUBJECT,
501 DESCRIPTION
502 ) = (SELECT
503 B.SUBJECT,
504 B.DESCRIPTION
505 FROM WMS_DOCK_APPOINTMENTS_TL B
506 WHERE B.DOCK_APPOINTMENT_ID = T.DOCK_APPOINTMENT_ID
507 AND B.LANGUAGE = T.SOURCE_LANG)
508 WHERE (
509 T.DOCK_APPOINTMENT_ID,
510 T.LANGUAGE
511 ) IN (SELECT
512 SUBT.DOCK_APPOINTMENT_ID,
513 SUBT.LANGUAGE
514 FROM WMS_DOCK_APPOINTMENTS_TL SUBB, WMS_DOCK_APPOINTMENTS_TL SUBT
515 WHERE SUBB.DOCK_APPOINTMENT_ID = SUBT.DOCK_APPOINTMENT_ID
516 AND SUBB.LANGUAGE = SUBT.SOURCE_LANG
517 AND (SUBB.SUBJECT <> SUBT.SUBJECT
518 OR SUBB.DESCRIPTION <> SUBT.DESCRIPTION
519 OR (SUBB.DESCRIPTION IS NULL AND SUBT.DESCRIPTION IS NOT NULL)
520 OR (SUBB.DESCRIPTION IS NOT NULL AND SUBT.DESCRIPTION IS NULL)
521 )
522 );
523
524 INSERT INTO WMS_DOCK_APPOINTMENTS_TL
525 (DOCK_APPOINTMENT_ID,
526 SUBJECT,
527 DESCRIPTION,
528 CREATED_BY,
529 CREATION_DATE,
530 LAST_UPDATED_BY,
531 LAST_UPDATE_DATE,
532 LAST_UPDATE_LOGIN,
533 LANGUAGE,
534 SOURCE_LANG
535 ) SELECT /*+ ORDERED */
536 B.DOCK_APPOINTMENT_ID,
537 B.SUBJECT,
538 B.DESCRIPTION,
539 B.CREATED_BY,
540 B.CREATION_DATE,
541 B.LAST_UPDATED_BY,
542 B.LAST_UPDATE_DATE,
543 B.LAST_UPDATE_LOGIN,
544 L.LANGUAGE_CODE,
545 B.SOURCE_LANG
546 FROM WMS_DOCK_APPOINTMENTS_TL B, FND_LANGUAGES L
547 WHERE L.INSTALLED_FLAG IN ('I', 'B')
548 AND B.LANGUAGE = userenv('LANG')
549 AND NOT exists
550 (SELECT NULL
551 FROM WMS_DOCK_APPOINTMENTS_TL T
552 WHERE T.DOCK_APPOINTMENT_ID = B.DOCK_APPOINTMENT_ID
553 AND T.LANGUAGE = L.LANGUAGE_CODE);
554
555 END ADD_LANGUAGE;
556
557
558 procedure LOCK_ROW (
559 x_dock_appointment_id in NUMBER,
560 x_carrier_code in VARCHAR2,
561 x_staging_lane_id in NUMBER,
562 x_trip_stop in NUMBER,
563 x_rep_start_date in DATE,
564 x_rep_end_date in DATE,
565 x_rep_origin in NUMBER,
566 x_rep_frequency in NUMBER,
567 x_appointment_status in NUMBER,
568 x_appointment_type in NUMBER,
569 x_dock_id in NUMBER,
570 x_organization_id in NUMBER,
571 x_start_time in DATE,
572 x_end_time in DATE,
573 x_source_type in NUMBER,
574 x_source_header_id in NUMBER,
575 x_source_line_id in NUMBER,
576 x_subject in VARCHAR2,
577 x_description in VARCHAR2
578 ) is
579 cursor c is select
580 CARRIER_CODE,
581 STAGING_LANE_ID,
582 TRIP_STOP,
583 REP_START_DATE,
584 REP_END_DATE,
585 REP_ORIGIN,
586 REP_FREQUENCY,
587 APPOINTMENT_STATUS,
588 APPOINTMENT_TYPE,
589 DOCK_ID,
590 ORGANIZATION_ID,
591 START_TIME,
592 END_TIME,
593 SOURCE_TYPE,
594 SOURCE_HEADER_ID,
595 SOURCE_LINE_ID
596 from WMS_DOCK_APPOINTMENTS_B
597 where DOCK_APPOINTMENT_ID = X_DOCK_APPOINTMENT_ID
598 for update of DOCK_APPOINTMENT_ID nowait;
599 recinfo c%rowtype;
600
601 cursor c1 is select
602 SUBJECT,
603 DESCRIPTION,
604 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
605 from WMS_DOCK_APPOINTMENTS_TL
606 where DOCK_APPOINTMENT_ID = X_DOCK_APPOINTMENT_ID
607 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
608 for update of DOCK_APPOINTMENT_ID nowait;
609 begin
610 open c;
611 fetch c into recinfo;
612 if (c%notfound) then
613 close c;
614 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
615 app_exception.raise_exception;
616 end if;
617 close c;
618 if ( ((recinfo.CARRIER_CODE = X_CARRIER_CODE)
619 OR ((recinfo.CARRIER_CODE is null) AND (X_CARRIER_CODE is null)))
620 AND ((recinfo.STAGING_LANE_ID = X_STAGING_LANE_ID)
621 OR ((recinfo.STAGING_LANE_ID is null) AND (X_STAGING_LANE_ID is null)))
622 AND ((recinfo.TRIP_STOP = X_TRIP_STOP)
623 OR ((recinfo.TRIP_STOP is null) AND (X_TRIP_STOP is null)))
624 AND ((recinfo.REP_START_DATE = X_REP_START_DATE)
625 OR ((recinfo.REP_START_DATE is null) AND (X_REP_START_DATE is null)))
626 AND ((recinfo.REP_END_DATE = X_REP_END_DATE)
627 OR ((recinfo.REP_END_DATE is null) AND (X_REP_END_DATE is null)))
628 AND ((recinfo.REP_ORIGIN = X_REP_ORIGIN)
629 OR ((recinfo.REP_ORIGIN is null) AND (X_REP_ORIGIN is null)))
630 AND ((recinfo.REP_FREQUENCY = X_REP_FREQUENCY)
631 OR ((recinfo.REP_FREQUENCY is null) AND (X_REP_FREQUENCY is null)))
632 AND ((recinfo.APPOINTMENT_STATUS = X_APPOINTMENT_STATUS)
633 OR ((recinfo.APPOINTMENT_STATUS is null) AND (X_APPOINTMENT_STATUS is null)))
634 AND (recinfo.APPOINTMENT_TYPE = X_APPOINTMENT_TYPE)
635 AND ((recinfo.DOCK_ID = X_DOCK_ID)
636 OR ((recinfo.DOCK_ID is null) AND (X_DOCK_ID is null)))
637 AND ((recinfo.ORGANIZATION_ID = X_ORGANIZATION_ID)
638 OR ((recinfo.ORGANIZATION_ID is null) AND (X_ORGANIZATION_ID is null)))
639 AND ((recinfo.START_TIME = X_START_TIME)
640 OR ((recinfo.START_TIME is null) AND (X_START_TIME is null)))
641 AND ((recinfo.END_TIME = X_END_TIME)
642 OR ((recinfo.END_TIME is null) AND (X_END_TIME is null)))
643 AND ((recinfo.SOURCE_TYPE = X_SOURCE_TYPE)
644 OR ((recinfo.SOURCE_TYPE is null) AND (X_SOURCE_TYPE is null)))
645 AND ((recinfo.SOURCE_HEADER_ID = X_SOURCE_HEADER_ID)
646 OR ((recinfo.SOURCE_HEADER_ID is null) AND (X_SOURCE_HEADER_ID is null)))
647 AND ((recinfo.SOURCE_LINE_ID = X_SOURCE_LINE_ID)
648 OR ((recinfo.SOURCE_LINE_ID is null) AND (X_SOURCE_LINE_ID is null)))
649 ) then
650 null;
651 else
652 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
653 app_exception.raise_exception;
654 end if;
655
656 for tlinfo in c1 loop
657 if (tlinfo.BASELANG = 'Y') then
658 if ( (tlinfo.SUBJECT = X_SUBJECT)
659 AND ((tlinfo.DESCRIPTION = X_DESCRIPTION)
660 OR ((tlinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
661 ) then
662 null;
663 else
664 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
665 app_exception.raise_exception;
666 end if;
667 end if;
668 end loop;
669 return;
670 end LOCK_ROW;
671
672 /*----------------------------------------------------------------------------*
673 * API Name : get_dock_appointment_range
674 * Description: Given appointment attributes and time window
675 fetches a list of dock appointments
676
677 *---------------------------------------------------------------------------*/
678
679 PROCEDURE get_dock_appointment_range (
680 x_return_status OUT NOCOPY VARCHAR2
681 , x_msg_count OUT NOCOPY NUMBER
682 , x_msg_data OUT NOCOPY VARCHAR2
683 , x_dock_appt_list OUT NOCOPY WMS_DOCK_APPOINTMENTS_PUB.dock_appt_tb_tp
684 , p_api_version IN NUMBER DEFAULT 1.0
685 , p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE
686 , p_organization_id IN NUMBER
687 , p_start_date IN DATE
688 , p_end_date IN DATE
689 , p_appointment_type IN NUMBER DEFAULT NULL
690 , p_supplier_id IN NUMBER DEFAULT NULL
691 , p_supplier_site_id IN NUMBER DEFAULT NULL
692 , p_customer_id IN NUMBER DEFAULT NULL
693 , p_customer_site_id IN NUMBER DEFAULT NULL
694 , p_carrier_code IN VARCHAR2 DEFAULT NULL
695 , p_carrier_id IN VARCHAR2 DEFAULT NULL
696 , p_trip_stop_id IN NUMBER DEFAULT NULL
697 , p_waybill_number IN VARCHAR2 DEFAULT NULL
698 , p_bill_of_lading IN VARCHAR2 DEFAULT NULL
699 , p_master_bol IN VARCHAR2 DEFAULT NULL) IS
700
701
702 --Local variables
703 l_progress NUMBER; --Used to check progress of the procedure
704 l_no_trip_rec BOOLEAN := FALSE;
705 l_carrier_code WSH_CARRIERS.FREIGHT_CODE%TYPE;
706
707 BEGIN
708
709 l_progress :=10;
710
711 --Initialize the return status to success
712 x_return_status := FND_API.G_RET_STS_SUCCESS;
713
714 -- Initialize message list to clear any existing messages if p_init_msg_list is set to TRUE
715 IF fnd_api.To_Boolean(p_init_msg_list) THEN
716 fnd_msg_pub.initialize;
717 END IF;
718
719 l_progress := 20;
720
721 IF(g_debug = 1 ) THEN
722 print_debug('Entered procedure GET_DOCK_APPOINTMENT_RANGE at '||TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:DD:SS'),4);
723 print_debug('Organization ID' ||p_organization_id,4);
724 print_debug('Appointment start date' ||p_start_date,4);
725 print_debug('Appointment end date' ||p_end_date,4);
726 print_debug('Appointmnet Type' ||p_appointment_type,4);
727 print_debug('Supplier ID' ||p_supplier_id,4);
728 print_debug('Supplier Site ID' ||p_supplier_site_id,4);
729 print_debug('Customer ID' ||p_customer_id,4);
730 print_debug('Customer Site ID' ||p_customer_site_id,4);
731 print_debug('Carrier Code' ||p_carrier_code,4);
732 print_debug('Carrier Id' ||p_carrier_id,4);
733 print_debug('Trip Stop ID' ||p_trip_stop_id,4);
734 print_debug('Waybill Number' ||p_waybill_number,4);
735 print_debug('Bill of Lading' ||p_bill_of_lading,4);
736 print_debug('Master Bill of Lading' ||p_master_bol,4);
737 END IF;
738
739 --check if atleast one attribute other than time window has a NOT NULL value
740 IF( p_supplier_id IS NULL
741 AND p_supplier_site_id IS NULL
742 AND p_customer_id IS NULL
743 AND p_customer_site_id IS NULL
744 AND p_trip_stop_id IS NULL
745 AND(
746 ( p_carrier_code IS NULL
747 AND p_carrier_id IS NULL
748 )
749 OR p_end_date IS NULL
750 )
751 AND p_waybill_number IS NULL
752 AND p_bill_of_lading IS NULL
753 AND p_master_bol IS NULL
754 ) THEN
755
756 IF (g_debug = 1) THEN
757 print_debug('Failure in get_wms_dock_appointment_range at level'
758 ||l_progress||'.All the attributes passed are NULL', 4);
759 END IF;
760 fnd_message.set_name('INV', 'INV_MISSING_REQUIRED_PARAMETER');
761 fnd_msg_pub.add;
762 RAISE FND_API.G_EXC_ERROR;
763
764 --Check if organization id is null
765 ELSIF( p_organization_id IS NULL ) THEN
766 IF (g_debug = 1) THEN
767 print_debug('Failure in get_wms_dock_appointment_range at level'
768 ||l_progress||'.Organization ID is null', 4);
769 END IF;
770 fnd_message.set_name('INV', 'INV_ITM_MISS_ORG_ID');
771 fnd_msg_pub.add;
772 RAISE FND_API.G_EXC_ERROR;
773
774 --Check if p_start_date is null
775 ELSIF( p_start_date IS NULL ) THEN
776 IF (g_debug = 1) THEN
777 print_debug('Failure in get_wms_dock_appointment_range at level'
778 ||l_progress||'.p_start_date is null', 4);
779 END IF;
780 fnd_message.set_name('INV', 'INV_EAM_GEN_NULL_START_DATE');
781 fnd_msg_pub.add;
782 RAISE FND_API.G_EXC_ERROR;
783
784 -- Check if start date is less than end date
785 ELSIF( p_end_date is not null AND p_start_date > p_end_date ) THEN
786 IF (g_debug = 1) THEN
787 print_debug('Failure in get_wms_dock_appointment_range at level'
788 ||l_progress||'.p_start_date is greater than p_end_date', 4);
789 print_debug('Exiting procedure without fetching appointments', 4);
790 END IF;
791 --Bug #5309213
792 --Instead of raising an error, set the appointment list to NULL
793 --and return control to the calling module.
794 RETURN;
795 END IF;
796
797 l_progress := 30;
798
799 --If carrier id is passed instead of carrier code,
800 --fetching the carrier code from wsh_carriers table
801 BEGIN
802 IF( p_carrier_code IS NULL AND p_carrier_id IS NOT NULL ) THEN
803 SELECT FREIGHT_CODE INTO l_carrier_code
804 FROM WSH_CARRIERS
805 WHERE CARRIER_ID = p_carrier_id;
806 ELSIF (p_carrier_code IS NOT NULL) THEN
807 --If carrier code is passed then using the same carrier code
808 l_carrier_code := p_carrier_code;
809 END IF;
810 EXCEPTION
811 WHEN NO_DATA_FOUND THEN
812 NULL;
813 WHEN TOO_MANY_ROWS THEN
814 NULL;
815 WHEN OTHERS THEN
816 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
817 END;
818
819 l_progress := 40;
820
821 --If trip stop Id is not null then get the first appointment for that trip stop
822 IF( p_trip_stop_id IS NOT NULL ) THEN
823 IF (g_debug = 1) THEN
824 print_debug('Fetching dock appointments for Trip Stop ID:'||p_trip_stop_id, 4);
825 END IF;
826
827 BEGIN
828 SELECT dock_appointment_id
829 , start_time
830 , end_time
831 INTO x_dock_appt_list(1)
832 FROM wms_dock_appointments_b
833 WHERE
834 start_time=(SELECT min(start_time)
835 FROM wms_dock_appointments_b
836 WHERE organization_id = p_organization_id
837 AND appointment_type = NVL(p_appointment_type, appointment_type)
838 AND start_time >= p_start_date
839 AND trip_stop = p_trip_stop_id
840 )
841 AND organization_id = p_organization_id
842 AND appointment_type = NVL(p_appointment_type, appointment_type)
843 AND trip_stop = p_trip_stop_id
844 AND ROWNUM = 1;
845
846 l_progress := 50;
847 IF (g_debug = 1) THEN
848 print_debug('Fetched record for Trip Stop ID with appointment id:'||x_dock_appt_list(1).dock_appointment_id, 4);
849 END IF;
850 EXCEPTION
851 WHEN NO_DATA_FOUND THEN
852 --Setting the trip flag to TRUE to further process by carrier id
853 l_no_trip_rec := TRUE ;
854 WHEN OTHERS THEN
855 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
856 END;
857 END IF;--p_trip_stop_id not null
858
859 /* If trip stop Id is null or if no records are fetched for given trip stop id
860 * and carrier code or carrier id is not null then get all the appointments for the l_carrier_code
861 * within the given time window
862 */
863 IF((p_trip_stop_id IS NULL OR l_no_trip_rec = TRUE )AND l_carrier_code IS NOT NULL ) THEN
864 IF (g_debug = 1) THEN
865 print_debug('Fetching dock appointments for carrier code:'||l_carrier_code, 4);
866 END IF;
867 l_progress := 60;
868
869 SELECT dock_appointment_id
870 , start_time
871 , end_time
872 BULK COLLECT
873 INTO x_dock_appt_list
874 FROM wms_dock_appointments_b
875 WHERE organization_id = p_organization_id
876 AND appointment_type = NVL(p_appointment_type, appointment_type)
877 AND carrier_code = l_carrier_code
878 AND start_time >= p_start_date
879 AND end_time <= p_end_date
880 ORDER BY start_time;
881 l_progress := 70;
882 END IF; --p_trip_stop_id is null and l_carrier_code is not null
883
884 IF (g_debug = 1) THEN
885 print_debug('Procedure get_dock_appointment_range completed successfully at '||TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:DD:SS'), 4);
886 END IF;
887
888 EXCEPTION
889 WHEN FND_API.G_EXC_ERROR THEN
890 x_return_status := FND_API.G_RET_STS_ERROR;
891 IF (g_debug = 1) THEN
892 print_debug('Execution error occured in get_dock_appointment_range at level:'||l_progress, 4);
893 END IF;
894 fnd_msg_pub.count_and_get (
895 p_encoded => FND_API.G_FALSE
896 , p_count => x_msg_count
897 , p_data => x_msg_data );
898 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
899 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
900 fnd_message.set_name('WMS','WMS_UNEXPECTED_ERROR');
901 fnd_msg_pub.add;
902 IF (g_debug = 1) THEN
903 print_debug('Unexpected error occured in get_dock_appointment_range at level:'||l_progress, 4);
904 END IF;
905 fnd_msg_pub.count_and_get (
906 p_encoded => FND_API.G_FALSE
907 , p_count => x_msg_count
908 , p_data => x_msg_data );
909 WHEN OTHERS THEN
910 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
911 fnd_message.set_name('WMS','WMS_UNEXPECTED_ERROR');
912 fnd_msg_pub.add;
913 IF (g_debug = 1) THEN
914 print_debug('Unexpected error occured in get_dock_appointment_range at level:'||l_progress, 4);
915 END IF;
916 fnd_msg_pub.count_and_get (
917 p_encoded => FND_API.G_FALSE
918 , p_count => x_msg_count
919 , p_data => x_msg_data );
920 END get_dock_appointment_range;
921
922 PROCEDURE OTM_Dock_Appointment
923 (
924 p_dock_appt_tab IN DockApptTabType,
925 x_return_status OUT NOCOPY VARCHAR2,
926 x_msg_count OUT NOCOPY NUMBER,
927 x_msg_data OUT NOCOPY VARCHAR2
928 ) IS
929
930 l_dock_appointments_v_rec wms_dock_appointments_v%ROWTYPE;
931 l_dock_appointment_id NUMBER;
932 l_locator_id NUMBER;
933 l_disable_date DATE;
934 l_msg VARCHAR2(100);
935 l_return_status VARCHAR2(1);
936 l_msg_count NUMBER;
937 l_msg_data VARCHAR2(4000);
938
939 CURSOR Get_Appt_Info(v_trip_stop_id number, v_organization_id NUMBER) IS
940 SELECT dock_appointment_id
941 FROM wms_dock_appointments_b
942 WHERE trip_stop = v_trip_stop_id
943 AND organization_id = v_organization_id;
944
945 CURSOR Check_Valid_Locator(v_locator_id NUMBER, v_organization_id NUMBER) IS
946 SELECT inventory_location_id, disable_date
947 FROM mtl_item_locations
948 WHERE inventory_location_id = v_locator_id
949 AND organization_id = v_organization_id
950 AND inventory_location_type = 1
951 AND NVL(disable_date, SYSDATE + 1) > SYSDATE;
952
953 BEGIN
954 IF WMS_CONTROL.G_CURRENT_RELEASE_LEVEL >= 120001 THEN
955 -- Initialize the return status to success
956 x_return_status := FND_API.G_RET_STS_SUCCESS;
957
958 IF g_debug = 1 THEN
959 print_debug('In OTM_Dock_Appointment API ', 4);
960 print_debug('p_dock_appt_tab.COUNT : '|| p_dock_appt_tab.COUNT, 4);
961 END IF;
962 SAVEPOINT otm_dock_sp;
963
964 -- Loop through the dock appointments
965 FOR i in p_dock_appt_tab.FIRST..p_dock_appt_tab.LAST LOOP
966 -- Check for Required Parameters
967 IF p_dock_appt_tab(i).Organization_id IS NULL THEN
968 IF g_debug = 1 THEN
969 print_debug('Organization id is required',4);
970 END IF;
971 FND_MESSAGE.SET_NAME('WMS', 'WMS_DOCK_APPT_PARAM_REQD');
972 FND_MESSAGE.SET_TOKEN('PARAM', 'Organization');
973 FND_MSG_PUB.ADD;
974 RAISE FND_API.G_EXC_ERROR;
975 ELSIF p_dock_appt_tab(i).Trip_Stop_id IS NULL THEN
976 IF g_debug = 1 THEN
977 print_debug('Trip Stop id is required',4);
978 END IF;
979 FND_MESSAGE.SET_NAME('WMS', 'WMS_DOCK_APPT_PARAM_REQD');
980 FND_MESSAGE.SET_TOKEN('PARAM', 'Trip Stop');
981 FND_MSG_PUB.ADD;
982 RAISE FND_API.G_EXC_ERROR;
983 END IF;
984
985 -- Check for Valid dock name
986 IF p_dock_appt_tab(i).Dock_Name IS NOT NULL
987 AND (SUBSTR(p_dock_appt_tab(i).Dock_Name, 1, 5) <> 'DOCK-'
988 OR LENGTH(p_dock_appt_tab(i).Dock_Name) <=5) THEN
989 IF g_debug = 1 THEN
990 print_debug('Dock Door Name is Invalid : '||p_dock_appt_tab(i).Dock_Name ,4);
991 END IF;
992 FND_MESSAGE.SET_NAME('WMS', 'WMS_INVALID_DOCK_DOOR');
993 FND_MESSAGE.SET_TOKEN('DOCK_DOOR', p_dock_appt_tab(i).Dock_Name);
994 FND_MSG_PUB.ADD;
995 RAISE FND_API.G_EXC_ERROR;
996 END IF;
997
998 -- Extract Locator_id from Dock Name and validate the locator
999 -- for the Organization
1000 l_locator_id := SUBSTR(p_dock_appt_tab(i).Dock_Name, 6, 40);
1001 IF g_debug = 1 THEN
1002 print_debug('l_locator_id : '||l_locator_id, 4);
1003 print_debug('Trip Stop : '||p_dock_appt_tab(i).Trip_Stop_id||' , Organization_id : '||p_dock_appt_tab(i).Organization_id, 4);
1004 END IF;
1005
1006 -- Locator can be NULL for deleting appointments for a trip
1007 IF l_locator_id IS NOT NULL THEN
1008 OPEN Check_Valid_Locator(l_locator_id, p_dock_appt_tab(i).Organization_id);
1009 FETCH Check_Valid_Locator INTO l_dock_appointments_v_rec.dock_id, l_disable_date;
1010 IF Check_Valid_Locator%NOTFOUND THEN
1011 CLOSE Check_Valid_Locator;
1012 IF g_debug = 1 THEN
1013 print_debug('Dock Door is Invalid for this Organization ', 4);
1014 END IF;
1015 FND_MESSAGE.SET_NAME('WMS', 'WMS_INVALID_DOCK_DOOR_ORG');
1016 FND_MESSAGE.SET_TOKEN('DOCK_DOOR', p_dock_appt_tab(i).Dock_Name);
1017 FND_MSG_PUB.ADD;
1018 RAISE FND_API.G_EXC_ERROR;
1019 END IF;
1020 CLOSE Check_Valid_Locator;
1021
1022 -- End Date/Time should be greater than Start Date/Time
1023 IF p_dock_appt_tab(i).Start_Time > p_dock_appt_tab(i).End_Time
1024 OR p_dock_appt_tab(i).Start_Time < SYSDATE OR p_dock_appt_tab(i).End_Time < SYSDATE
1025 OR trunc(NVL(l_disable_date, p_dock_appt_tab(i).End_Time + 1)) <= trunc(p_dock_appt_tab(i).End_Time) THEN
1026 FND_MESSAGE.SET_NAME('WMS','WMS_INVALID_DATE');
1027 IF g_debug = 1 THEN
1028 print_debug('Invalid Dates entered', 4);
1029 END IF;
1030 FND_MESSAGE.SET_NAME('WMS', 'WMS_DOCK_APPT_DATE_INVALID');
1031 FND_MSG_PUB.ADD;
1032 RAISE FND_API.G_EXC_ERROR;
1033 END IF;
1034
1035 -- Check if appointment exists for the Locator
1036 OPEN Get_Appt_Info(p_dock_appt_tab(i).Trip_Stop_id, p_dock_appt_tab(i).Organization_id);
1037 FETCH Get_Appt_Info INTO l_dock_appointment_id;
1038 -- Populate appointment record
1039 l_dock_appointments_v_rec.dock_appointment_id := l_dock_appointment_id;
1040 l_dock_appointments_v_rec.trip_stop := p_dock_appt_tab(i).Trip_Stop_id;
1041 l_dock_appointments_v_rec.organization_id := p_dock_appt_tab(i).Organization_id;
1042 l_dock_appointments_v_rec.appointment_type := 2; -- Outbound
1043 l_dock_appointments_v_rec.start_time := p_dock_appt_tab(i).Start_Time;
1044 l_dock_appointments_v_rec.end_time := p_dock_appt_tab(i).End_Time;
1045 l_dock_appointments_v_rec.rep_origin := l_dock_appointments_v_rec.dock_appointment_id; -- same for Non-Repeating appts
1046 l_dock_appointments_v_rec.rep_frequency := 1; -- Never Repeat
1047 l_dock_appointments_v_rec.creation_date := SYSDATE;
1048 l_dock_appointments_v_rec.created_by := FND_GLOBAL.User_Id;
1049 l_dock_appointments_v_rec.last_updated_by := FND_GLOBAL.User_Id;
1050 l_dock_appointments_v_rec.last_update_date := SYSDATE;
1051 l_dock_appointments_v_rec.last_update_login := FND_GLOBAL.Login_Id;
1052
1053 -- Message text "Dock Appt for OTM Trip Stop <Trip Stop>"
1054 FND_MESSAGE.SET_NAME('WMS','WMS_DOCK_APPT_TRIP');
1055 FND_MESSAGE.SET_TOKEN('TRIP_STOP',p_dock_appt_tab(i).trip_stop_id);
1056 l_msg := FND_MESSAGE.GET;
1057 l_dock_appointments_v_rec.subject := 'OTM Planned Dock Appointment';
1058 l_dock_appointments_v_rec.description := l_msg;
1059
1060 IF Get_Appt_Info%NOTFOUND THEN
1061 SELECT wms_dock_appointments_s.nextval
1062 INTO l_dock_appointments_v_rec.DOCK_APPOINTMENT_ID
1063 FROM dual;
1064 l_dock_appointments_v_rec.rep_origin := l_dock_appointments_v_rec.dock_appointment_id; -- same for Non-Repeating appts
1065 IF g_debug = 1 THEN
1066 print_debug('Calling Insert_dock_appointment API', 4);
1067 END IF;
1068 Insert_dock_appointment
1069 ( x_return_status => l_return_status,
1070 x_msg_count => l_msg_count,
1071 x_msg_data => l_msg_data,
1072 p_dock_appointments_v_rec => l_dock_appointments_v_rec);
1073 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1074 IF g_debug = 1 THEN
1075 print_debug('Unable to create Dock Door appt, return status : '||l_return_status, 4);
1076 END IF;
1077 FND_MESSAGE.SET_NAME('WMS','WMS_DOCK_APPT_TRIP');
1078 FND_MSG_PUB.ADD;
1079 RAISE FND_API.G_EXC_ERROR;
1080 END IF;
1081 ELSE
1082 IF g_debug = 1 THEN
1083 print_debug('Calling Update_dock_appointment API', 4);
1084 END IF;
1085 Update_dock_appointment
1086 (p_dock_appointments_v_rec => l_dock_appointments_v_rec,
1087 x_return_status => l_return_status,
1088 x_msg_count => l_msg_count,
1089 x_msg_data => l_msg_data);
1090 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1091 IF g_debug = 1 THEN
1092 print_debug('Unable to update Dock Door appt, return status : '||l_return_status, 4);
1093 END IF;
1094 FND_MESSAGE.SET_NAME('WMS','WMS_DOCK_APPT_TRIP');
1095 FND_MSG_PUB.ADD;
1096 RAISE FND_API.G_EXC_ERROR;
1097 END IF;
1098 END IF;
1099 CLOSE Get_Appt_Info;
1100 ELSE
1101 -- Delete appointment for any locator for the Trip Stop and Organization
1102 OPEN Get_Appt_Info(p_dock_appt_tab(i).trip_stop_id, p_dock_appt_tab(i).organization_id);
1103 FETCH Get_Appt_Info INTO l_dock_appointment_id;
1104 CLOSE Get_Appt_Info;
1105 IF l_dock_appointment_id IS NOT NULL THEN
1106 IF g_debug = 1 THEN
1107 print_debug('Calling Delete_dock_appointment API', 4);
1108 END IF;
1109 DELETE_dock_appointment
1110 (x_return_status => l_return_status,
1111 x_msg_count => l_msg_count,
1112 x_msg_data => l_msg_data,
1113 p_dock_appointment_id => l_dock_appointment_id);
1114 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1115 IF g_debug = 1 THEN
1116 print_debug('Unable to delete Dock Door appt, return status : '||l_return_status, 4);
1117 END IF;
1118 FND_MESSAGE.SET_NAME('WMS','WMS_DOCK_APPT_TRIP');
1119 FND_MSG_PUB.ADD;
1120 RAISE FND_API.G_EXC_ERROR;
1121 END IF;
1122 END IF;
1123 END IF;
1124 END LOOP;
1125
1126 IF g_debug = 1 THEN
1127 print_debug('Exiting OTM_Dock_Door_Appointment API', 4);
1128 END IF;
1129 END IF;
1130
1131 EXCEPTION
1132 WHEN FND_API.G_EXC_ERROR THEN
1133 ROLLBACK TO OTM_DOCK_SP;
1134 IF Get_Appt_Info%ISOPEN THEN
1135 CLOSE Get_Appt_Info;
1136 END IF;
1137 IF Check_Valid_Locator%ISOPEN THEN
1138 CLOSE Check_Valid_Locator;
1139 END IF;
1140 IF g_debug = 1 THEN
1141 print_debug('Expected error occurred, Exiting OTM_Dock_Door_Appointment API', 4);
1142 END IF;
1143 x_return_status := FND_API.G_RET_STS_ERROR;
1144 FND_MSG_PUB.Count_And_Get
1145 (p_count => x_msg_count,
1146 p_data => x_msg_data);
1147 WHEN OTHERS THEN
1148 ROLLBACK TO OTM_DOCK_SP;
1149 IF Get_Appt_Info%ISOPEN THEN
1150 CLOSE Get_Appt_Info;
1151 END IF;
1152 IF Check_Valid_Locator%ISOPEN THEN
1153 CLOSE Check_Valid_Locator;
1154 END IF;
1155 IF g_debug = 1 THEN
1156 print_debug('Unexpected error occurred, Exiting OTM_Dock_Door_Appointment API', 4);
1157 print_debug(SQLERRM, 4);
1158 END IF;
1159 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1160 FND_MSG_PUB.Count_And_Get
1161 (p_count => x_msg_count,
1162 p_data => x_msg_data);
1163 END OTM_Dock_Appointment;
1164
1165 END WMS_DOCK_APPOINTMENTS_PUB;