[Home] [Help]
PACKAGE BODY: APPS.FTE_TENDER_PVT
Source
1 PACKAGE BODY FTE_TENDER_PVT AS
2 /* $Header: FTETEPVB.pls 120.7.12000000.2 2007/07/03 09:55:42 ueshanka ship $ */
3
4 G_TENDER_NOTIFIED CONSTANT VARCHAR2(10) := 'NOTIFIED';
5 G_TENDER_APPROVED CONSTANT VARCHAR2(10) := 'APPROVED';
6 G_TENDER_REJECTED CONSTANT VARCHAR2(10) := 'REJECTED';
7 G_TENDER_ABORT CONSTANT VARCHAR2(10) := 'eng_force';
8
9 G_PKG_NAME CONSTANT VARCHAR2(30) := 'FTE_TENDER_PVT';
10
11
12 PROCEDURE RAISE_TENDER_REQUEST(
13 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
14 x_return_status OUT NOCOPY VARCHAR2,
15 x_msg_count OUT NOCOPY NUMBER,
16 x_msg_data OUT NOCOPY VARCHAR2,
17 p_tender_id IN NUMBER,
18 p_item_key IN VARCHAR2,
19 p_shipper_wait_time IN NUMBER,
20 p_shipper_name IN VARCHAR2,
21 p_carrier_name IN VARCHAR2,
22 p_contact_name IN VARCHAR2,
23 p_autoaccept IN VARCHAR2,
24 p_contact_perf IN VARCHAR2,
25 p_url IN VARCHAR2) IS
26 --{
27
28 l_parameter_list wf_parameter_list_t;
29
30 l_api_name CONSTANT VARCHAR2(30) := 'RAISE_TENDER_REQUEST';
31 l_api_version CONSTANT NUMBER := 1.0;
32 l_debug_on CONSTANT BOOLEAN := WSH_DEBUG_SV.is_debug_enabled;
33 l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.' || 'FTE_TENDER_PVT'|| '.' || 'RAISE_TENDER_REQUEST';
34
35 BEGIN
36
37
38 SAVEPOINT RAISE_TENDER_REQUEST_PUB;
39 IF l_debug_on THEN
40 WSH_DEBUG_SV.push(l_module_name);
41 END IF;
42
43 IF FND_API.to_Boolean( p_init_msg_list )
44 THEN
45 FND_MSG_PUB.initialize;
46 END IF;
47
48 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
49 x_msg_count := 0;
50 x_msg_data := 0;
51
52 -- raise event
53
54 -- should save the weight volume snap shot
55
56 wf_event.AddParameterToList(p_name=>'SHIPPER_CUTOFF_TIME',
57 p_value=> p_shipper_wait_time,
58 p_parameterlist=>l_parameter_list);
59
60 wf_event.AddParameterToList(p_name=>'TENDER_ID',
61 p_value=> p_tender_id,
62 p_parameterlist=>l_parameter_list);
63
64 wf_event.AddParameterToList(p_name=>'SHIPPER_NAME',
65 p_value=> p_shipper_name,
66 p_parameterlist=>l_parameter_list);
67
68 wf_event.AddParameterToList(p_name=> 'CARRIER_NAME',
69 p_value=> p_carrier_name,
70 p_parameterlist=>l_parameter_list);
71
72 wf_event.AddParameterToList(p_name=> 'CONTACT_NAME',
73 p_value=> p_contact_name,
74 p_parameterlist=>l_parameter_list);
75
76 wf_event.AddParameterToList(p_name=> 'CONTACT_PERFORMER',
77 p_value=> p_contact_perf,
78 p_parameterlist=>l_parameter_list);
79
80
81 --// We have to add user that is associated with this contact to
82 -- a adhoc role that is created in the name of contact name HZ_PARTY:XYZ
83 -- This is to resolve the issue with carrier user not able to
84 -- see notifications in worklist
85
86
87
88 wf_event.AddParameterToList(p_name=> 'AUTO_ACCEPT',
89 p_value=> p_autoaccept,
90 p_parameterlist=>l_parameter_list);
91
92 wf_event.AddParameterToList(p_name=> 'RESPONSE_URL',
93 p_value=> p_url,
94 p_parameterlist=>l_parameter_list);
95
96 IF l_debug_on THEN
97 WSH_DEBUG_SV.logmsg(l_module_name,'P1DEBUG:22 ',WSH_DEBUG_SV.C_PROC_LEVEL);
98 END IF;
99 -- event key should be based on some other value
100
101 wf_event.raise(
102 p_event_name => 'oracle.apps.fte.lt.tenderrequest',
103 p_event_key => p_item_key,
104 p_parameters => l_parameter_list
105 );
106
107 IF l_debug_on THEN
108 WSH_DEBUG_SV.logmsg(l_module_name,'P1DEBUG:23 ',WSH_DEBUG_SV.C_PROC_LEVEL);
109 END IF;
110
111 l_parameter_list.DELETE;
112
113 -- Standard call to get message count and if count is 1,get message info.
114 --
115 IF l_debug_on THEN
116 WSH_DEBUG_SV.logmsg(l_module_name,'P1DEBUG:24 ',WSH_DEBUG_SV.C_PROC_LEVEL);
117 END IF;
118 FND_MSG_PUB.Count_And_Get
119 (
120 p_count => x_msg_count,
121 p_data => x_msg_data,
122 p_encoded => FND_API.G_FALSE
123 );
124
125
126 --
127 --
128
129 IF l_debug_on THEN
130 WSH_DEBUG_SV.pop(l_module_name);
131 END IF;
132
133 --}
134 EXCEPTION
135 --{
136 WHEN FND_API.G_EXC_ERROR THEN
137 ROLLBACK TO RAISE_TENDER_REQUEST_PUB;
138 x_return_status := FND_API.G_RET_STS_ERROR ;
139 FND_MSG_PUB.Count_And_Get
140 (
141 p_count => x_msg_count,
142 p_data => x_msg_data,
143 p_encoded => FND_API.G_FALSE
144 );
145 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
146 ROLLBACK TO RAISE_TENDER_REQUEST_PUB;
147 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
148 FND_MSG_PUB.Count_And_Get
149 (
150 p_count => x_msg_count,
151 p_data => x_msg_data,
152 p_encoded => FND_API.G_FALSE
153 );
154 WHEN OTHERS THEN
155 ROLLBACK TO RAISE_TENDER_REQUEST_PUB;
156 wsh_util_core.default_handler('FTE_TENDER_PVT.RAISE_TENDER_REQUEST');
157 x_return_status := FND_API.G_RET_STS_ERROR;
158 FND_MSG_PUB.Count_And_Get
159 (
160 p_count => x_msg_count,
161 p_data => x_msg_data,
162 p_encoded => FND_API.G_FALSE
163 );
164
165 --}
166
167 END RAISE_TENDER_REQUEST;
168
169
170
171 -- ------------------------------------------------------------------------------- --
172 -- --
173 -- NAME: COMPLETE_TENDER_NOTIFICATION --
174 -- TYPE: PROCEDURE --
175 -- PARAMETERS (IN): p_trip_seg_status VARCHAR2 --
176 -- p_tender_action VARCHAR2 --
177 --
178 -- PARAMETERS (OUT): --
179 -- x_current_status : This returns the Notification Activity Status --
180 -- --
181 -- PARAMETERS (IN OUT) :
182 -- p_item_key : This identifies the Load Tendering Process Instance --
183 -- DESCRIPTION :This procedure checks if the Notification Status --
184 -- and completes the Notification Activity same if found --
185 -- incomplete. --
186 -- CHANGE CONTROL LOG --
187 -- ------------------ --
188 -- --
189 -- DATE VERSION BY BUG DESCRIPTION --
190 -- ---------- ------- -------- ------- --------------------------------------- --
191 -- 2003 11.5.9 SAMUTHUK Created --
192 -- --
193 -- ------------------------------------------------------------------------------- --
194
195 PROCEDURE COMPLETE_TENDER_NOTIFICATION(
196 p_item_key IN VARCHAR2,
197 p_completion_status IN VARCHAR2,
198 x_current_status OUT NOCOPY VARCHAR2) IS
199
200 BEGIN
201 -- Check for Status of Tender Request Notification
202
203 FTE_WF_UTIL.GET_BLOCK_STATUS(
204 itemtype => 'FTETEREQ',
205 itemkey => p_item_key,
206 p_workflow_process => 'TENDER_REQUEST_PROCESS',
207 p_block_label => 'TENDER_REQUEST_NTF',
208 x_return_status => x_current_status);
209
210
211 IF (x_current_status = G_TENDER_NOTIFIED) THEN
212
213 wf_engine.CompleteActivity(
214 itemtype => 'FTETEREQ',
215 itemkey => p_item_key,
216 activity => 'TENDER_REQUEST_PROCESS:TENDER_REQUEST_NTF',
217 result => p_completion_status);
218
219 END IF;
220
221
222 END COMPLETE_TENDER_NOTIFICATION;
223
224
225 PROCEDURE RAISE_TENDER_CANCEL(
226 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
227 x_return_status OUT NOCOPY VARCHAR2,
228 x_msg_count OUT NOCOPY NUMBER,
229 x_msg_data OUT NOCOPY VARCHAR2,
230 p_item_key IN VARCHAR2,
231 p_tender_id IN NUMBER,
232 p_shipper_name IN VARCHAR2,
233 p_carrier_name IN VARCHAR2,
234 p_contact_name IN VARCHAR2,
235 p_contact_perf IN VARCHAR2) IS
236 --{
237
238 l_parameter_list wf_parameter_list_t;
239
240 l_api_name CONSTANT VARCHAR2(30) := 'RAISE_TENDER_CANCEL';
241 l_api_version CONSTANT NUMBER := 1.0;
242 --samuthuk
243 x_old_status VARCHAR2(10);
244
245 BEGIN
246
247
248 SAVEPOINT RAISE_TENDER_CANCEL_PUB;
249
250 IF FND_API.to_Boolean( p_init_msg_list )
251 THEN
252 FND_MSG_PUB.initialize;
253 END IF;
254
255 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
256 x_msg_count := 0;
257 x_msg_data := 0;
258
259 ------------------------------------------------------------------
260 -- Samuthuk [ workflow Notifications std ]
261 ------------------------------------------------------------------
262 COMPLETE_TENDER_NOTIFICATION(
263 p_item_key => p_item_key,
264 p_completion_status => G_TENDER_ABORT,
265 x_current_status => x_old_status);
266
267 ------------------------------------------------------------------
268 ---Remove the fields from Cancel Tender for
269
270
271
272 -- raise event
273
274 wf_event.AddParameterToList(p_name=>'TENDER_ID',
275 p_value=> p_tender_id,
276 p_parameterlist=>l_parameter_list);
277
278
279 wf_event.AddParameterToList(p_name=>'SHIPPER_NAME',
280 p_value=> p_shipper_name,
281 p_parameterlist=>l_parameter_list);
282
283 wf_event.AddParameterToList(p_name=> 'CARRIER_NAME',
284 p_value=> p_carrier_name,
285 p_parameterlist=>l_parameter_list);
286
287 wf_event.AddParameterToList(p_name=> 'CONTACT_NAME',
288 p_value=> p_contact_name,
289 p_parameterlist=>l_parameter_list);
290
291 wf_event.AddParameterToList(p_name=> 'CONTACT_PERFORMER',
292 p_value=> p_contact_perf,
293 p_parameterlist=>l_parameter_list);
294
295 wf_event.AddParameterToList(p_name => 'TENDER_ACTION',
296 p_value => FTE_TENDER_PVT.S_SHIPPER_CANCELLED,
297 p_parameterlist => l_parameter_list);
298
299 wf_event.raise(
300 p_event_name => 'oracle.apps.fte.lt.tendercancel',
301 p_event_key => p_item_key,
302 p_parameters => l_parameter_list
303 );
304
305 l_parameter_list.DELETE;
306
307
308 -- should delete tender snap shot
309
310 -- Standard call to get message count and if count is 1,get message info.
311 --
312 FND_MSG_PUB.Count_And_Get
313 (
314 p_count => x_msg_count,
315 p_data => x_msg_data,
316 p_encoded => FND_API.G_FALSE
317 );
318 --
319 --
320
321
322 --}
323 EXCEPTION
324 --{
325 WHEN FND_API.G_EXC_ERROR THEN
326 ROLLBACK TO RAISE_TENDER_CANCEL_PUB;
327 x_return_status := FND_API.G_RET_STS_ERROR ;
328 FND_MSG_PUB.Count_And_Get
329 (
330 p_count => x_msg_count,
331 p_data => x_msg_data,
332 p_encoded => FND_API.G_FALSE
333 );
334 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
335 ROLLBACK TO RAISE_TENDER_CANCEL_PUB;
336 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
337 FND_MSG_PUB.Count_And_Get
338 (
339 p_count => x_msg_count,
340 p_data => x_msg_data,
341 p_encoded => FND_API.G_FALSE
342 );
343 WHEN OTHERS THEN
344 ROLLBACK TO RAISE_TENDER_CANCEL_PUB;
345 wsh_util_core.default_handler('FTE_TENDER_PVT.RAISE_TENDER_CANCEL');
346 x_return_status := FND_API.G_RET_STS_ERROR;
347 FND_MSG_PUB.Count_And_Get
348 (
349 p_count => x_msg_count,
350 p_data => x_msg_data,
351 p_encoded => FND_API.G_FALSE
352 );
353
354 --}
355
356 END RAISE_TENDER_CANCEL;
357
358
359
360
361 PROCEDURE RAISE_TENDER_ACCEPT(
362 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
363 x_return_status OUT NOCOPY VARCHAR2,
364 x_msg_count OUT NOCOPY NUMBER,
365 x_msg_data OUT NOCOPY VARCHAR2,
366 p_item_key IN VARCHAR2,
367 p_tender_id IN NUMBER,
368 p_shipper_name IN VARCHAR2,
369 p_carrier_name IN VARCHAR2,
370 p_contact_name IN VARCHAR2,
371 p_contact_perf IN VARCHAR2) IS
372 --{
373
374 l_parameter_list wf_parameter_list_t;
375
376 l_api_name CONSTANT VARCHAR2(30) := 'RAISE_TENDER_ACCEPT';
377 l_api_version CONSTANT NUMBER := 1.0;
378
379 -- Samuthuk
380 x_old_status VARCHAR2(10);
381
382 --shravisa
383 l_mode_transport VARCHAR2(80);
384
385 BEGIN
386 SAVEPOINT RAISE_TENDER_ACCEPT_PUB;
387
388 IF FND_API.to_Boolean( p_init_msg_list )
389 THEN
390 FND_MSG_PUB.initialize;
391 END IF;
392
393 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
394 x_msg_count := 0;
395 x_msg_data := 0;
396
397
398
399
400 ------------------------------------------------------------------
401 -- Samuthuk [ workflow Notifications std ]
402 ------------------------------------------------------------------
403 COMPLETE_TENDER_NOTIFICATION(
404 p_item_key => p_item_key,
405 p_completion_status => G_TENDER_APPROVED,
406 x_current_status => x_old_status);
407
408 IF (x_old_status = G_TENDER_NOTIFIED) THEN
409 RETURN;
410 END IF;
411 ------------------------------------------------------------------
412
413
414
415 -- raise event
416
417
418
419 wf_event.AddParameterToList(p_name=>'TENDER_ID',
420 p_value=> p_tender_id,
421 p_parameterlist=>l_parameter_list);
422
423
424 wf_event.AddParameterToList(p_name=>'SHIPPER_NAME',
425 p_value=> p_shipper_name,
426 p_parameterlist=>l_parameter_list);
427
428 wf_event.AddParameterToList(p_name=> 'CARRIER_NAME',
429 p_value=> p_carrier_name,
430 p_parameterlist=>l_parameter_list);
431
432 wf_event.AddParameterToList(p_name=> 'CONTACT_NAME',
433 p_value=> p_contact_name,
434 p_parameterlist=>l_parameter_list);
435
436 wf_event.AddParameterToList(p_name=> 'CONTACT_PERFORMER',
437 p_value=> p_contact_perf,
438 p_parameterlist=>l_parameter_list);
439
440 wf_event.AddParameterToList(p_name => 'TENDER_ACTION',
441 p_value => FTE_TENDER_PVT.S_ACCEPTED,
442 p_parameterlist => l_parameter_list);
443
444 --Rel 12 Changes for Freight Class
445 l_mode_transport := wf_engine.GetItemAttrText('FTETEREQ', p_item_key, 'MODE_OF_TRANSPORT');
446
447 wf_event.AddParameterToList(p_name => 'MODE_OF_TRANSPORT',
448 p_value => l_mode_transport,
449 p_parameterlist => l_parameter_list);
450
451
452 wf_event.raise(
453 p_event_name => 'oracle.apps.fte.lt.tenderaccept',
454 p_event_key => p_item_key,
455 p_parameters => l_parameter_list
456 );
457
458 l_parameter_list.DELETE;
459
460 -- Standard call to get message count and if count is 1,get message info.
461 --
462 FND_MSG_PUB.Count_And_Get
463 (
464 p_count => x_msg_count,
465 p_data => x_msg_data,
466 p_encoded => FND_API.G_FALSE
467 );
468 --
469 --
470
471
472 --}
473 EXCEPTION
474 --{
475 WHEN FND_API.G_EXC_ERROR THEN
476 ROLLBACK TO RAISE_TENDER_ACCEPT_PUB;
477 x_return_status := FND_API.G_RET_STS_ERROR ;
478 FND_MSG_PUB.Count_And_Get
479 (
480 p_count => x_msg_count,
481 p_data => x_msg_data,
482 p_encoded => FND_API.G_FALSE
483 );
484 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
485 ROLLBACK TO RAISE_TENDER_ACCEPT_PUB;
486 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
487 FND_MSG_PUB.Count_And_Get
488 (
489 p_count => x_msg_count,
490 p_data => x_msg_data,
491 p_encoded => FND_API.G_FALSE
492 );
493 WHEN OTHERS THEN
494 ROLLBACK TO RAISE_TENDER_ACCEPT_PUB;
495 wsh_util_core.default_handler('FTE_TENDER_PVT.RAISE_TENDER_ACCEPT');
496 x_return_status := FND_API.G_RET_STS_ERROR;
497 FND_MSG_PUB.Count_And_Get
498 (
499 p_count => x_msg_count,
500 p_data => x_msg_data,
501 p_encoded => FND_API.G_FALSE
502 );
503
504 --}
505
506 END RAISE_TENDER_ACCEPT;
507
508 --
509 --Update tender does not use p_item_key passed in because
510 -- we have to raise update event when ever there is a change in the
511 --weight/vol.And item key used while raising the tender event
512 --should not be updated. Because this is the key to identify the
513 -- workflow.
514
515 PROCEDURE RAISE_TENDER_UPDATE(
516 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
517 x_return_status OUT NOCOPY VARCHAR2,
518 x_msg_count OUT NOCOPY NUMBER,
519 x_msg_data OUT NOCOPY VARCHAR2,
520 p_item_key IN VARCHAR2,
521 p_tender_id IN NUMBER,
522 p_contact_perf IN VARCHAR2) IS
523 --{
524
525 l_parameter_list wf_parameter_list_t;
526
527 l_api_name CONSTANT VARCHAR2(30) := 'RAISE_TENDER_UPDATE';
528 l_api_version CONSTANT NUMBER := 1.0;
529 l_response_url VARCHAR2(30000);
530 l_shipper_name VARCHAR2(2000);
531 l_carrier_id NUMBER;
532 l_carrier_site_id NUMBER;
533 l_notif_type VARCHAR2(10);
534 l_trip_id NUMBER;
535 l_rank_id NUMBER;
536 l_rank_version NUMBER;
537 l_tender_status VARCHAR2(30);
538 l_shipper_user_id NUMBER;
539 l_mode_of_transport VARCHAR2(80);
540
541 BEGIN
542 SAVEPOINT RAISE_TENDER_UPDATE_PUB;
543
544 IF FND_API.to_Boolean( p_init_msg_list )
545 THEN
546 FND_MSG_PUB.initialize;
547 END IF;
548
549
550 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
551 x_msg_count := 0;
552 x_msg_data := 0;
553
554
555
556 -- raise event
557
558 -------------------------------------------------------------------------------------
559 -- Samuthuk [ workflow Notifications std ]
560 -------------------------------------------------------------------------------------
561 l_shipper_name := wf_engine.GetItemAttrText('FTETEREQ', p_item_key, 'SHIPPER_NAME');
562 l_response_url := wf_engine.GetItemAttrText('FTETEREQ', p_item_key, 'RESPONSE_URL');
563
564 -- Rel 12
565 l_carrier_id := wf_engine.GetItemAttrNumber('FTETEREQ', p_item_key, 'CARRIER_ID');
566 l_carrier_site_id := wf_engine.GetItemAttrNumber('FTETEREQ', p_item_key, 'CARRIER_SITE_ID');
567 l_notif_type := wf_engine.GetItemAttrText('FTETEREQ', p_item_key, 'NOTIF_TYPE');
568 l_trip_id := wf_engine.GetItemAttrNumber('FTETEREQ', p_item_key, 'TRIP_ID');
569 l_rank_id := wf_engine.GetItemAttrNumber('FTETEREQ', p_item_key, 'RANK_ID');
570 l_rank_version := wf_engine.GetItemAttrNumber('FTETEREQ', p_item_key, 'RANK_VERSION');
571 l_tender_status := wf_engine.GetItemAttrText('FTETEREQ', p_item_key, 'TENDER_STATUS');
572 l_shipper_user_id := wf_engine.GetItemAttrNumber('FTETEREQ', p_item_key,'SHIPPER_USER_ID');
573 l_mode_of_transport := wf_engine.GetItemAttrText('FTETEREQ', p_item_key,'MODE_OF_TRANSPORT');
574
575 --------------------------------------------------------------------------------------
576
577
578 wf_event.AddParameterToList(p_name=>'TENDER_ID',
579 p_value=> p_tender_id,
580 p_parameterlist=>l_parameter_list);
581
582 wf_event.AddParameterToList(p_name=>'SHIPPER_NAME',
583 p_value=> l_shipper_name,
584 p_parameterlist=>l_parameter_list);
585
586
587 wf_event.AddParameterToList(p_name=>'CONTACT_PERFORMER',
588 p_value=> p_contact_perf,
589 p_parameterlist=>l_parameter_list);
590
591 wf_event.AddParameterToList(p_name=>'CARRIER_ID',
592 p_value=> l_carrier_id,
593 p_parameterlist=>l_parameter_list);
594
595 wf_event.AddParameterToList(p_name=>'CARRIER_SITE_ID',
596 p_value=> l_carrier_site_id,
597 p_parameterlist=>l_parameter_list);
598
599 wf_event.AddParameterToList(p_name=>'RESPONSE_URL',
600 p_value=> l_response_url,
601 p_parameterlist=>l_parameter_list);
602
603 wf_event.AddParameterToList(p_name=>'NOTIF_TYPE',
604 p_value=> l_notif_type,
605 p_parameterlist=>l_parameter_list);
606
607
608 wf_event.AddParameterToList(p_name=>'TRIP_ID',
609 p_value=> l_trip_id,
610 p_parameterlist=>l_parameter_list);
611
612 wf_event.AddParameterToList(p_name=>'RANK_ID',
613 p_value=> l_rank_id,
614 p_parameterlist=>l_parameter_list);
615
616 wf_event.AddParameterToList(p_name=>'RANK_VERSION',
617 p_value=> l_rank_version,
618 p_parameterlist=>l_parameter_list);
619
620 wf_event.AddParameterToList(p_name=>'TENDER_STATUS',
621 p_value=> l_tender_status,
622 p_parameterlist=>l_parameter_list);
623
624 wf_event.AddParameterToList(p_name=>'SHIPPER_USER_ID',
625 p_value=> l_shipper_user_id,
626 p_parameterlist=>l_parameter_list);
627
628 wf_event.AddParameterToList(p_name=>'MODE_OF_TRANSPORT',
629 p_value=> l_mode_of_transport,
630 p_parameterlist=>l_parameter_list);
631
632
633 wf_event.raise(
634 p_event_name => 'oracle.apps.fte.lt.tenderupdate',
635 p_event_key => GET_ITEM_KEY(p_tender_id),--p_item_key,
636 p_parameters => l_parameter_list
637 );
638
639 l_parameter_list.DELETE;
640
641 -- Standard call to get message count and if count is 1,get message info.
642 --
643 FND_MSG_PUB.Count_And_Get
644 (
645 p_count => x_msg_count,
646 p_data => x_msg_data,
647 p_encoded => FND_API.G_FALSE
648 );
649 --
650 --
651
652
653 --}
654 EXCEPTION
655 --{
656 WHEN FND_API.G_EXC_ERROR THEN
657 ROLLBACK TO RAISE_TENDER_UPDATE_PUB;
658 x_return_status := FND_API.G_RET_STS_ERROR ;
659 FND_MSG_PUB.Count_And_Get
660 (
661 p_count => x_msg_count,
662 p_data => x_msg_data,
663 p_encoded => FND_API.G_FALSE
664 );
665 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
666 ROLLBACK TO RAISE_TENDER_UPDATE_PUB;
667 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
668 FND_MSG_PUB.Count_And_Get
669 (
670 p_count => x_msg_count,
671 p_data => x_msg_data,
672 p_encoded => FND_API.G_FALSE
673 );
674 WHEN OTHERS THEN
675 ROLLBACK TO RAISE_TENDER_UPDATE_PUB;
676 wsh_util_core.default_handler('FTE_TENDER_PVT.RAISE_TENDER_UPDATE');
677 x_return_status := FND_API.G_RET_STS_ERROR;
678 FND_MSG_PUB.Count_And_Get
679 (
680 p_count => x_msg_count,
681 p_data => x_msg_data,
682 p_encoded => FND_API.G_FALSE
683 );
684
685 --}
686
687 END RAISE_TENDER_UPDATE;
688
689
690 PROCEDURE RAISE_TENDER_REJECT(
691 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
692 x_return_status OUT NOCOPY VARCHAR2,
693 x_msg_count OUT NOCOPY NUMBER,
694 x_msg_data OUT NOCOPY VARCHAR2,
695 p_item_key IN VARCHAR2,
696 p_tender_id IN NUMBER,
697 p_shipper_name IN VARCHAR2,
698 p_carrier_name IN VARCHAR2,
699 p_contact_name IN VARCHAR2,
700 p_contact_perf IN VARCHAR2) IS
701 --{
702
703 l_parameter_list wf_parameter_list_t;
704
705 l_api_name CONSTANT VARCHAR2(30) := 'RAISE_TENDER_REJECT';
706 l_api_version CONSTANT NUMBER := 1.0;
707
708 --Samuthuk
709 x_old_status VARCHAR2(10);
710
711 --shravisa
712 l_mode_transport VARCHAR2(80);
713
714 BEGIN
715
716 SAVEPOINT RAISE_TENDER_REJECT_PUB;
717
718 IF FND_API.to_Boolean( p_init_msg_list )
719 THEN
720 FND_MSG_PUB.initialize;
721 END IF;
722
723
724 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
725 x_msg_count := 0;
726 x_msg_data := 0;
727
728 ------------------------------------------------------------------
729 -- Samuthuk [ workflow Notifications std ]
730 ------------------------------------------------------------------
731 COMPLETE_TENDER_NOTIFICATION(
732 p_item_key => p_item_key,
733 p_completion_status => G_TENDER_REJECTED,
734 x_current_status => x_old_status);
735
736 IF (x_old_status = G_TENDER_NOTIFIED) THEN
737 RETURN;
738 END IF;
739 ------------------------------------------------------------------
740
741
742 -- raise event
743
744 wf_event.AddParameterToList(p_name=>'TENDER_ID',
745 p_value=> p_tender_id,
746 p_parameterlist=>l_parameter_list);
747
748
749 wf_event.AddParameterToList(p_name=>'SHIPPER_NAME',
750 p_value=> p_shipper_name,
751 p_parameterlist=>l_parameter_list);
752
753 wf_event.AddParameterToList(p_name=> 'CARRIER_NAME',
754 p_value=> p_carrier_name,
755 p_parameterlist=>l_parameter_list);
756
757 wf_event.AddParameterToList(p_name=> 'CONTACT_NAME',
758 p_value=> p_contact_name,
759 p_parameterlist=>l_parameter_list);
760
761 wf_event.AddParameterToList(p_name=> 'CONTACT_PERFORMER',
762 p_value=> p_contact_perf,
763 p_parameterlist=>l_parameter_list);
764
765 wf_event.AddParameterToList(p_name => 'TENDER_ACTION',
766 p_value => FTE_TENDER_PVT.S_REJECTED,
767 p_parameterlist => l_parameter_list);
768
769 --Rel 12 Changes for Freight Class
770 l_mode_transport := wf_engine.GetItemAttrText('FTETEREQ', p_item_key, 'MODE_OF_TRANSPORT');
771 wf_event.AddParameterToList(p_name => 'MODE_OF_TRANSPORT',
772 p_value => l_mode_transport,
773 p_parameterlist => l_parameter_list);
774
775
776 wf_event.raise(
777 p_event_name => 'oracle.apps.fte.lt.tenderreject',
778 p_event_key => p_item_key,
779 p_parameters => l_parameter_list
780 );
781
782 l_parameter_list.DELETE;
783
784 -- Standard call to get message count and if count is 1,get message info.
785 --
786 FND_MSG_PUB.Count_And_Get
787 (
788 p_count => x_msg_count,
789 p_data => x_msg_data,
790 p_encoded => FND_API.G_FALSE
791 );
792 --
793 --
794
795 --}
796 EXCEPTION
797 --{
798 WHEN FND_API.G_EXC_ERROR THEN
799 ROLLBACK TO RAISE_TENDER_REJECT_PUB;
800 x_return_status := FND_API.G_RET_STS_ERROR ;
801 FND_MSG_PUB.Count_And_Get
802 (
803 p_count => x_msg_count,
804 p_data => x_msg_data,
805 p_encoded => FND_API.G_FALSE
806 );
807 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
808 ROLLBACK TO RAISE_TENDER_REJECT_PUB;
809 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
810 FND_MSG_PUB.Count_And_Get
811 (
812 p_count => x_msg_count,
813 p_data => x_msg_data,
814 p_encoded => FND_API.G_FALSE
815 );
816 WHEN OTHERS THEN
817 ROLLBACK TO RAISE_TENDER_REJECT_PUB;
818 wsh_util_core.default_handler('FTE_TENDER_PVT.RAISE_TENDER_REJECT');
819 x_return_status := FND_API.G_RET_STS_ERROR;
820 FND_MSG_PUB.Count_And_Get
821 (
822 p_count => x_msg_count,
823 p_data => x_msg_data,
824 p_encoded => FND_API.G_FALSE
825 );
826
827 --}
828
829 END RAISE_TENDER_REJECT;
830
831
832
833 -- ------------------------------------------------------------------------------- --
834 -- --
835 -- NAME: CAN_PERFORM_THIS_ACTION --
836 -- TYPE: FUNCTION --
837 -- PARAMETERS (IN): p_trip_seg_status VARCHAR2
838 -- p_tender_action VARCHAR2
839 --
840 -- PARAMETERS (OUT):
841 -- PARAMETERS (IN OUT): none --
842 -- RETURN: TRUE/FALSE --
843 -- DESCRIPTION: This procedure checks if you can perform an action --
844 -- on a trip segment --
845 -- CHANGE CONTROL LOG --
846 -- ------------------ --
847 -- --
848 -- DATE VERSION BY BUG DESCRIPTION --
849 -- ---------- ------- -------- ------- --------------------------------------- --
850 -- 2002 11.5.8 HBHAGAVA Created --
851 -- --
852 -- ------------------------------------------------------------------------------- --
853
854
855 FUNCTION CAN_PERFORM_THIS_ACTION (
856 p_trip_seg_status VARCHAR2,
857 p_tender_action VARCHAR2)
858 RETURN BOOLEAN
859 IS
860
861 l_trip_seg_status VARCHAR2(30);
862 l_tender_action VARCHAR2(30);
863 l_debug_on CONSTANT BOOLEAN := WSH_DEBUG_SV.is_debug_enabled;
864 l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.' || 'FTE_TENDER_PVT' || '.' || 'CAN_PERFORM_THIS_ACTION';
865
866 l_return_value BOOLEAN;
867
868 BEGIN
869 --{
870 IF l_debug_on THEN
871 WSH_DEBUG_SV.push(l_module_name);
872 END IF;
873 l_trip_seg_status := upper(p_trip_seg_status);
874 l_tender_action := upper(p_tender_action);
875 l_return_value := FALSE;
876
877 IF ( (l_tender_action <> FTE_TENDER_PVT.S_TENDERED)
878 AND (l_tender_action <> FTE_TENDER_PVT.S_RETENDERED)
879 AND (l_tender_action <> FTE_TENDER_PVT.S_ACCEPTED)
880 AND (l_tender_action <> FTE_TENDER_PVT.S_AUTO_ACCEPTED)
881 AND (l_tender_action <> FTE_TENDER_PVT.S_SHIPPER_CANCELLED)
882 AND (l_tender_action <> FTE_TENDER_PVT.S_REJECTED)
883 AND (l_tender_action <> FTE_TENDER_PVT.S_NORESPONSE)
884 AND (l_tender_action <> FTE_TENDER_PVT.S_SHIPPER_UPDATED))
885 THEN
886 l_return_value := FALSE;
887 --return FALSE;
888 END IF;
889
890 IF (l_trip_seg_status IS NULL)
891 THEN
892
893 IF ((l_tender_action IS NOT NULL)
894 AND (l_tender_action <> FTE_TENDER_PVT.S_TENDERED))
895 THEN
896 l_return_value := FALSE;
897 --return FALSE;
898 ELSE
899 l_return_value := TRUE;
900 --return TRUE;
901 END IF;
902 ELSIF ( (l_trip_seg_status = FTE_TENDER_PVT.S_TENDERED)
903 OR (l_trip_seg_status = FTE_TENDER_PVT.S_RETENDERED))
904 THEN
905 IF ((l_tender_action IS NULL )
906 OR (l_tender_action = FTE_TENDER_PVT.S_TENDERED)
907 OR (l_tender_action = FTE_TENDER_PVT.S_RETENDERED))
908 THEN
909 l_return_value := FALSE;
910 --return FALSE;
911 ELSE
912 l_return_value := TRUE;
913 --return TRUE;
914 END IF;
915
916 ELSIF ( (l_trip_seg_status = FTE_TENDER_PVT.S_ACCEPTED)
917 OR (l_trip_seg_status = FTE_TENDER_PVT.S_AUTO_ACCEPTED))
918 THEN
919 IF ((l_tender_action IS NULL )
920 OR (l_tender_action = FTE_TENDER_PVT.S_TENDERED)
921 OR (l_tender_action = FTE_TENDER_PVT.S_REJECTED)
922 OR (l_tender_action = FTE_TENDER_PVT.S_AUTO_ACCEPTED)
923 OR (l_tender_action = FTE_TENDER_PVT.S_ACCEPTED)
924 OR (l_tender_action = FTE_TENDER_PVT.S_NORESPONSE))
925 THEN
926 l_return_value := FALSE;
927 --return FALSE;
928 ELSE
929 l_return_value := TRUE;
930 --return TRUE;
931 END IF;
932 ELSIF (l_trip_seg_status = FTE_TENDER_PVT.S_REJECTED)
933 THEN
934 IF ((l_tender_action IS NULL )
935 OR (l_tender_action = FTE_TENDER_PVT.S_RETENDERED)
936 OR (l_tender_action = FTE_TENDER_PVT.S_ACCEPTED)
937 OR (l_tender_action = FTE_TENDER_PVT.S_AUTO_ACCEPTED)
938 OR (l_tender_action = FTE_TENDER_PVT.S_REJECTED)
939 OR (l_tender_action = FTE_TENDER_PVT.S_SHIPPER_CANCELLED)
940 OR (l_tender_action = FTE_TENDER_PVT.S_NORESPONSE))
941 THEN
942 l_return_value := FALSE;
943 --return FALSE;
944 ELSE
945 l_return_value := TRUE;
946 --return TRUE;
947 END IF;
948 ELSIF (l_trip_seg_status = FTE_TENDER_PVT.S_SHIPPER_CANCELLED)
949 THEN
950 IF ( (l_tender_action IS NULL)
951 OR (l_tender_action = FTE_TENDER_PVT.S_SHIPPER_CANCELLED)
952 OR (l_tender_action = FTE_TENDER_PVT.S_ACCEPTED)
953 OR (l_tender_action = FTE_TENDER_PVT.S_RETENDERED)
954 OR (l_tender_action = FTE_TENDER_PVT.S_REJECTED)
955 OR (l_tender_action = FTE_TENDER_PVT.S_AUTO_ACCEPTED))
956 THEN
957 l_return_value := FALSE;
958 --return FALSE;
959 ELSE
960 l_return_value := TRUE;
961 --return TRUE;
962 END IF;
963 ELSIF (l_trip_seg_status = FTE_TENDER_PVT.S_NORESPONSE)
964 THEN
965 IF ( (l_tender_action IS NULL)
966 OR (l_tender_action = FTE_TENDER_PVT.S_SHIPPER_CANCELLED)
967 OR (l_tender_action = FTE_TENDER_PVT.S_ACCEPTED)
968 OR (l_tender_action = FTE_TENDER_PVT.S_RETENDERED)
969 OR (l_tender_action = FTE_TENDER_PVT.S_REJECTED)
970 OR (l_tender_action = FTE_TENDER_PVT.S_AUTO_ACCEPTED)
971 OR (l_tender_action = FTE_TENDER_PVT.S_NORESPONSE))
972 THEN
973 l_return_value := FALSE;
974 --return FALSE;
975 ELSE
976 l_return_value := TRUE;
977 --return TRUE;
978 END IF;
979 END IF;
980 IF l_debug_on THEN
981 WSH_DEBUG_SV.pop(l_module_name);
982 END IF;
983
984 RETURN l_return_value;
985
986 --}
987
988 EXCEPTION
989 --{
990 WHEN OTHERS THEN
991 RAISE;
992 --}
993
994 END CAN_PERFORM_THIS_ACTION;
995
996 FUNCTION CAN_PERFORM_THIS_ACTION_STR (
997 p_trip_seg_status VARCHAR2,
998 p_tender_action VARCHAR2)
999 RETURN VARCHAR2
1000 IS
1001 BEGIN
1002
1003 IF (NOT FTE_TENDER_PVT.CAN_PERFORM_THIS_ACTION(p_trip_seg_status,
1004 p_tender_action))
1005 THEN
1006 RETURN 'N';
1007 ELSE
1008 RETURN 'Y';
1009 END IF;
1010
1011 EXCEPTION
1012 WHEN OTHERS THEN
1013 RAISE;
1014
1015
1016 END CAN_PERFORM_THIS_ACTION_STR;
1017
1018 --*******************************************************
1019
1020 -- ------------------------------------------------------------------------------- --
1021 -- --
1022 -- NAME: IS_AUTO_ACCEPT_ENABLED --
1023 -- TYPE: FUNCTION --
1024 -- PARAMETERS (IN): p_carrier_name VARCHAR2
1025 --
1026 -- PARAMETERS (OUT):
1027 -- PARAMETERS (IN OUT): none --
1028 -- RETURN: TRUE/FALSE --
1029 -- DESCRIPTION: This procedure checks if you carrier is enabled for --
1030 -- auto tender
1031 -- CHANGE CONTROL LOG --
1032 -- ------------------ --
1033 -- --
1034 -- DATE VERSION BY BUG DESCRIPTION --
1035 -- ---------- ------- -------- ------- --------------------------------------- --
1036 -- 2002 11.5.8 HBHAGAVA Created --
1037 -- --
1038 -- ------------------------------------------------------------------------------- --
1039
1040
1041 FUNCTION IS_AUTO_ACCEPT_ENABLED (p_carrier_id VARCHAR2)
1042 RETURN BOOLEAN
1043 IS
1044
1045 BEGIN
1046 --{
1047
1048 RETURN (FALSE);
1049
1050 --}
1051
1052 EXCEPTION
1053 --{
1054 WHEN OTHERS THEN
1055 RAISE;
1056 --}
1057
1058 END IS_AUTO_ACCEPT_ENABLED ;
1059
1060
1061 FUNCTION GET_ITEM_KEY(p_trip_seg_id NUMBER)
1062 RETURN VARCHAR2
1063 IS
1064
1065 l_next_seq NUMBER;
1066
1067 BEGIN
1068
1069 SELECT fte_tender_id_s.nextval
1070 INTO l_next_seq
1071 FROM DUAL;
1072
1073 RETURN (p_trip_seg_id || '' || l_next_seq);
1074
1075 EXCEPTION
1076 WHEN OTHERS THEN
1077 RAISE;
1078 END GET_ITEM_KEY;
1079
1080 PROCEDURE VALIDATE_TENDER_REQUEST(
1081 p_api_version_number IN NUMBER,
1082 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
1083 x_return_status OUT NOCOPY VARCHAR2,
1084 x_msg_count OUT NOCOPY NUMBER,
1085 x_msg_data OUT NOCOPY VARCHAR2,
1086 p_trip_id IN NUMBER,
1087 p_action_code IN VARCHAR2,
1088 p_tender_action IN VARCHAR2,
1089 p_trip_name IN VARCHAR2 DEFAULT NULL)
1090
1091 AS
1092
1093 l_shipper_wait_time NUMBER;
1094 l_trip_id NUMBER;
1095 l_trip_name VARCHAR2(240);
1096 l_trip_status VARCHAR2(30);
1097 l_api_name VARCHAR2(30) := 'VALIDATE_TENDER_REQUEST';
1098 x_value VARCHAR2(200);
1099
1100 cursor get_trip_cur(c_trip_id NUMBER) is
1101 select trip_id, name,load_tender_status
1102 from wsh_trips
1103 where trip_id = c_trip_id;
1104
1105 l_debug_on CONSTANT BOOLEAN := WSH_DEBUG_SV.is_debug_enabled;
1106
1107 l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.' || 'FTE_TENDER_PVT' || '.' || l_api_name;
1108
1109 BEGIN
1110 --{
1111
1112
1113 SAVEPOINT VALIDATE_TENDER_REQUEST_PUB;
1114
1115
1116 IF FND_API.to_Boolean( p_init_msg_list )
1117 THEN
1118 FND_MSG_PUB.initialize;
1119 END IF;
1120 --
1121 IF l_debug_on THEN
1122 wsh_debug_sv.push(l_module_name);
1123 END IF;
1124 --
1125 -- Initialize API return status to success
1126 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1127 x_msg_count := 0;
1128 x_msg_data := 0;
1129
1130 IF (p_action_code = 'CREATE') THEN
1131 return;
1132 FND_MSG_PUB.Count_And_Get
1133 (
1134 p_count => x_msg_count,
1135 p_data => x_msg_data,
1136 p_encoded => FND_API.G_FALSE
1137 );
1138 END IF;
1139 IF l_debug_on THEN
1140 WSH_DEBUG_SV.logmsg(l_module_name,'P1DEBUG:26 ',WSH_DEBUG_SV.C_PROC_LEVEL);
1141 END IF;
1142
1143 IF l_debug_on THEN
1144 WSH_DEBUG_SV.logmsg(l_module_name,' Trip id ' || p_trip_id,WSH_DEBUG_SV.C_PROC_LEVEL);
1145 END IF;
1146
1147 FOR get_trip_rec IN get_trip_cur(p_trip_id)
1148 LOOP
1149 --{
1150 l_trip_id := get_trip_rec.trip_id;
1151 l_trip_name := get_trip_rec.name;
1152 l_trip_status := get_trip_rec.load_tender_status;
1153 --}
1154 END LOOP;
1155 -- END OF get trip segment info
1156 --
1157 --
1158 IF l_debug_on THEN
1159 WSH_DEBUG_SV.logmsg(l_module_name,'P1DEBUG:27 ',WSH_DEBUG_SV.C_PROC_LEVEL);
1160 END IF;
1161
1162 IF get_trip_cur%ISOPEN THEN
1163 CLOSE get_trip_cur;
1164 END IF;
1165 --
1166
1167 IF l_debug_on THEN
1168 WSH_DEBUG_SV.logmsg(l_module_name,' Current Tender Status ' || l_trip_status,
1169 WSH_DEBUG_SV.C_PROC_LEVEL);
1170 WSH_DEBUG_SV.logmsg(l_module_name,' REquested tener action ' || p_tender_action,
1171 WSH_DEBUG_SV.C_PROC_LEVEL);
1172 END IF;
1173 -- check if trip id is null
1174 -- If trip id is null then was unable to find trip based on
1175 -- tender id
1176 IF l_trip_id IS NULL THEN
1177 IF l_debug_on THEN
1178 WSH_DEBUG_SV.logmsg(l_module_name,'P1DEBUG:28 ',WSH_DEBUG_SV.C_PROC_LEVEL);
1179 END IF;
1180
1181 FND_MESSAGE.SET_NAME('FTE','FTE_INVALID_TENDER_ID');
1182 --FND_MESSAGE.SET_TOKEN('TENDER_ID',p_trip_id);
1183 WSH_UTIL_CORE.ADD_MESSAGE(WSH_UTIL_CORE.G_RET_STS_ERROR);
1184
1185 IF l_debug_on THEN
1186 wsh_debug_sv.pop(l_api_name);
1187 END IF;
1188
1189 RAISE FND_API.G_EXC_ERROR;
1190
1191 END IF;
1192
1193
1194 -- If trip id is null then was unable to find trip based on
1195 -- tender id
1196 IF NOT CAN_PERFORM_THIS_ACTION(l_trip_status,p_tender_action) THEN
1197 IF l_debug_on THEN
1198 WSH_DEBUG_SV.logmsg(l_module_name,'NOT CAN_PERFORM_THIS_ACTION', WSH_DEBUG_SV.C_PROC_LEVEL);
1199 END IF;
1200
1201
1202 FND_MESSAGE.SET_NAME('FTE','FTE_INVALID_TENDER_STATUS');
1203
1204
1205 FND_MESSAGE.SET_TOKEN('TENDER_ACTION',
1206 WSH_UTIL_CORE.Get_Lookup_Meaning('WSH_TENDER_STATUS',
1207 p_tender_action));
1208 FND_MESSAGE.SET_TOKEN('TRIP_SEG_LT_STATUS',
1209 WSH_UTIL_CORE.Get_Lookup_Meaning('WSH_TENDER_STATUS',
1210 l_trip_status));
1211 FND_MESSAGE.SET_TOKEN('TRIP_SEG_NAME',l_trip_name);
1212 WSH_UTIL_CORE.ADD_MESSAGE(WSH_UTIL_CORE.G_RET_STS_ERROR);
1213 RAISE FND_API.G_EXC_ERROR;
1214
1215 END IF;
1216
1217 IF l_debug_on THEN
1218 WSH_DEBUG_SV.logmsg(l_module_name,'after CAN_PERFORM_THIS_ACTION', WSH_DEBUG_SV.C_PROC_LEVEL);
1219 END IF;
1220
1221 -- Standard call to get message count and if count is 1,get message info.
1222 --
1223
1224 FND_MSG_PUB.Count_And_Get
1225 (
1226 p_count => x_msg_count,
1227 p_data => x_msg_data,
1228 p_encoded => FND_API.G_FALSE
1229 );
1230
1231 IF l_debug_on THEN
1232 WSH_DEBUG_SV.pop(l_module_name);
1233 END IF;
1234
1235 --
1236 --
1237 --}
1238
1239 EXCEPTION
1240 WHEN FND_API.G_EXC_ERROR THEN
1241 IF l_debug_on THEN
1242 WSH_DEBUG_SV.logmsg(l_module_name,'P1DEBUG:29 ',WSH_DEBUG_SV.C_PROC_LEVEL);
1243 END IF;
1244 ROLLBACK TO VALIDATE_TENDER_REQUEST_PUB;
1245 x_return_status := FND_API.G_RET_STS_ERROR ;
1246 FND_MSG_PUB.Count_And_Get
1247 (
1248 p_count => x_msg_count,
1249 p_data => x_msg_data,
1250 p_encoded => FND_API.G_FALSE
1251 );
1252 IF l_debug_on THEN
1253 WSH_DEBUG_SV.pop(l_module_name);
1254 END IF;
1255 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1256 ROLLBACK TO VALIDATE_TENDER_REQUEST_PUB;
1257 IF l_debug_on THEN
1258 WSH_DEBUG_SV.logmsg(l_module_name,'P1DEBUG:30 ',WSH_DEBUG_SV.C_PROC_LEVEL);
1259 END IF;
1260 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1261 FND_MSG_PUB.Count_And_Get
1262 (
1263 p_count => x_msg_count,
1264 p_data => x_msg_data,
1265 p_encoded => FND_API.G_FALSE
1266 );
1267 IF l_debug_on THEN
1268 WSH_DEBUG_SV.pop(l_module_name);
1269 END IF;
1270 WHEN OTHERS THEN
1271 IF l_debug_on THEN
1272 WSH_DEBUG_SV.logmsg(l_module_name,'P1DEBUG:31 ',WSH_DEBUG_SV.C_PROC_LEVEL);
1273 END IF;
1274 ROLLBACK TO VALIDATE_TENDER_REQUEST_PUB;
1275 wsh_util_core.default_handler('FTE_TENDER_PVT.VALIDATE_TENDER_REQUEST');
1276 x_return_status := FND_API.G_RET_STS_ERROR;
1277 FND_MSG_PUB.Count_And_Get
1278 (
1279 p_count => x_msg_count,
1280 p_data => x_msg_data,
1281 p_encoded => FND_API.G_FALSE
1282 );
1283 IF l_debug_on THEN
1284 WSH_DEBUG_SV.pop(l_module_name);
1285 END IF;
1286
1287 END VALIDATE_TENDER_REQUEST;
1288
1289
1290 --*******************************************************
1291
1292 -- ------------------------------------------------------------------------------- --
1293 -- --
1294 -- NAME: DELETE_TENDER_SNAPSHOT --
1295 -- TYPE: PROCEDURE --
1296 -- PARAMETERS (IN): P_init_msg_list VARCHAR2
1297 -- p_tender_id NUMBER
1298 --
1299 -- PARAMETERS (OUT): X_return_status VARCHAR2 --
1300 -- X_msg_count VARCHAR2 --
1301 -- X_msg_data VARCHAR2 --
1302 -- PARAMETERS (IN OUT): none --
1303 -- RETURN: none --
1304 -- DESCRIPTION: --
1305 -- --
1306 -- CHANGE CONTROL LOG --
1307 -- ------------------ --
1308 -- --
1309 -- DATE VERSION BY BUG DESCRIPTION --
1310 -- ---------- ------- -------- ------- --------------------------------------- --
1311 -- 2002 Created --
1312 -- --
1313 -- ------------------------------------------------------------------------------- --
1314
1315
1316 PROCEDURE DELETE_TENDER_SNAPSHOT(
1317 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
1318 p_tender_id IN NUMBER,
1319 x_return_status OUT NOCOPY VARCHAR2,
1320 x_msg_count OUT NOCOPY NUMBER,
1321 x_msg_data OUT NOCOPY VARCHAR2) IS
1322 --{
1323
1324 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_TENDER_SNAPSHOT';
1325 l_api_version CONSTANT NUMBER := 1.0;
1326
1327 --}
1328
1329 l_temp_id NUMBER;
1330
1331 --{
1332 BEGIN
1333 --
1334 -- Standard Start of API savepoint
1335 SAVEPOINT DELETE_TENDER_SNAPSHOT_PUB;
1336 --
1337 --
1338 -- Initialize message list if p_init_msg_list is set to TRUE.
1339 --
1340 --
1341 IF FND_API.to_Boolean( p_init_msg_list )
1342 THEN
1343 FND_MSG_PUB.initialize;
1344 END IF;
1345 --
1346 --
1347 -- Initialize API return status to success
1348 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1349 x_msg_count := 0;
1350 x_msg_data := 0;
1351
1352
1353 -- first delete snapshot then create
1354 SELECT count(*) INTO l_temp_id FROM FTE_TENDER_SNAPSHOT
1355 WHERE load_tender_number = p_tender_id and rownum = 1;
1356
1357 IF (l_temp_id > 0) THEN
1358 DELETE FROM FTE_TENDER_SNAPSHOT
1359 WHERE LOAD_TENDER_NUMBER = p_tender_id;
1360 END IF;
1361
1362 -- Standard call to get message count and if count is 1,get message info.
1363 --
1364 FND_MSG_PUB.Count_And_Get
1365 (
1366 p_count => x_msg_count,
1367 p_data => x_msg_data,
1368 p_encoded => FND_API.G_FALSE
1369 );
1370 --
1371 --
1372
1373
1374 --}
1375 EXCEPTION
1376 --{
1377 WHEN FND_API.G_EXC_ERROR THEN
1378 ROLLBACK TO DELETE_TENDER_SNAPSHOT_PUB;
1379 x_return_status := FND_API.G_RET_STS_ERROR ;
1380 FND_MSG_PUB.Count_And_Get
1381 (
1382 p_count => x_msg_count,
1383 p_data => x_msg_data,
1384 p_encoded => FND_API.G_FALSE
1385 );
1386 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1387 ROLLBACK TO DELETE_TENDER_SNAPSHOT_PUB;
1388 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1389 FND_MSG_PUB.Count_And_Get
1390 (
1391 p_count => x_msg_count,
1392 p_data => x_msg_data,
1393 p_encoded => FND_API.G_FALSE
1394 );
1395 WHEN OTHERS THEN
1396 ROLLBACK TO DELETE_TENDER_SNAPSHOT_PUB;
1397 wsh_util_core.default_handler('FTE_TENDER_PVT.DELETE_TENDER_SNAPSHOT');
1398 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1399 FND_MSG_PUB.Count_And_Get
1400 (
1401 p_count => x_msg_count,
1402 p_data => x_msg_data,
1403 p_encoded => FND_API.G_FALSE
1404 );
1405
1406 --}
1407
1408 END DELETE_TENDER_SNAPSHOT;
1409
1410 --*******************************************************
1411
1412 -- ------------------------------------------------------------------------------- --
1413 -- --
1414 -- NAME: TAKE_TENDER_SNAPSHOT_PUB --
1415 -- TYPE: PROCEDURE --
1416 -- PARAMETERS (IN): P_init_msg_list VARCHAR2
1417 -- p_tender_id NUMBER
1418 --
1419 -- PARAMETERS (OUT): X_return_status VARCHAR2 --
1420 -- X_msg_count VARCHAR2 --
1421 -- X_msg_data VARCHAR2 --
1422 -- PARAMETERS (IN OUT): none --
1423 -- RETURN: none --
1424 -- DESCRIPTION: --
1425 -- --
1426 -- CHANGE CONTROL LOG --
1427 -- ------------------ --
1428 -- --
1429 -- DATE VERSION BY BUG DESCRIPTION --
1430 -- ---------- ------- -------- ------- --------------------------------------- --
1431 -- 2002 Created --
1432 -- --
1433 -- ------------------------------------------------------------------------------- --
1434
1435
1436 PROCEDURE TAKE_TENDER_SNAPSHOT(
1437 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
1438 p_tender_id IN NUMBER,
1439 p_trip_id IN NUMBER,
1440 p_stop_id IN NUMBER,
1441 p_total_weight IN NUMBER,
1442 p_total_volume IN NUMBER,
1443 p_weight_uom IN VARCHAR2,
1444 p_volume_uom IN VARCHAR2,
1445 p_session_value IN VARCHAR2,
1446 p_action IN VARCHAR2,
1447 x_return_status OUT NOCOPY VARCHAR2,
1448 x_msg_count OUT NOCOPY NUMBER,
1449 x_msg_data OUT NOCOPY VARCHAR2) IS
1450 --{
1451
1452 l_api_name CONSTANT VARCHAR2(30) := 'TAKE_TENDER_SNAPSHOT';
1453 l_api_version CONSTANT NUMBER := 1.0;
1454
1455 --}
1456
1457
1458 --{
1459 BEGIN
1460 --
1461 -- Standard Start of API savepoint
1462 SAVEPOINT TAKE_TENDER_SNAPSHOT_PUB;
1463 --
1464 --
1465 -- Initialize message list if p_init_msg_list is set to TRUE.
1466 --
1467 --
1468 IF FND_API.to_Boolean( p_init_msg_list )
1469 THEN
1470 FND_MSG_PUB.initialize;
1471 END IF;
1472 --
1473 --
1474 -- Initialize API return status to success
1475 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1476 x_msg_count := 0;
1477 x_msg_data := 0;
1478
1479 IF(p_action = 'CREATE')
1480 THEN
1481 INSERT INTO FTE_TENDER_SNAPSHOT(
1482 LOAD_TENDER_NUMBER,
1483 TRIP_ID,
1484 STOP_ID,
1485 TOTAL_WEIGHT,
1486 TOTAL_VOLUME,
1487 WEIGHT_UOM,
1488 VOLUME_UOM,
1489 CREATION_DATE,
1490 CREATED_BY,
1491 LAST_UPDATE_DATE,
1492 LAST_UPDATED_BY,
1493 LAST_UPDATE_LOGIN,
1494 SESSION_VALUE)
1495 VALUES(
1496 p_tender_id,
1497 p_trip_id,
1498 p_stop_id,
1499 p_total_weight,
1500 p_total_volume,
1501 p_weight_uom,
1502 p_volume_uom,
1503 SYSDATE,
1504 FND_GLOBAL.USER_ID,
1505 SYSDATE,
1506 FND_GLOBAL.USER_ID,
1507 FND_GLOBAL.LOGIN_ID,
1508 p_session_value);
1509 ELSIF (p_action = 'UPDATE') THEN
1510
1511 UPDATE FTE_TENDER_SNAPSHOT SET
1512 TOTAL_WEIGHT = decode(p_total_weight,
1513 NULL,total_weight,
1514 FND_API.G_MISS_NUM,NULL,
1515 p_total_weight),
1516 TOTAL_VOLUME = decode(p_total_volume,
1517 NULL,total_volume,
1518 FND_API.G_MISS_NUM,NULL,
1519 p_total_volume),
1520 WEIGHT_UOM =decode(p_weight_uom,
1521 NULL,weight_uom,
1522 FND_API.G_MISS_CHAR,NULL,
1523 p_weight_uom),
1524 VOLUME_UOM =decode(p_volume_uom,
1525 NULL,volume_uom,
1526 FND_API.G_MISS_CHAR,NULL,
1527 p_volume_uom),
1528 SESSION_VALUE =decode(p_session_value,
1529 NULL,SESSION_VALUE,
1530 FND_API.G_MISS_CHAR,null,
1531 p_session_value),
1532 LAST_UPDATE_DATE = SYSDATE,
1533 LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
1534 LAST_UPDATE_LOGIN = FND_GLOBAL.USER_ID
1535 WHERE TRIP_ID = p_trip_id
1536 AND STOP_ID = p_stop_id
1537 AND LOAD_TENDER_NUMBER = p_tender_id;
1538 ELSIF (p_action = 'DELETE') THEN
1539 DELETE FTE_TENDER_SNAPSHOT
1540 WHERE LOAD_TENDER_NUMBER = p_tender_id;
1541 END IF;
1542
1543
1544 -- Standard call to get message count and if count is 1,get message info.
1545 --
1546 FND_MSG_PUB.Count_And_Get
1547 (
1548 p_count => x_msg_count,
1549 p_data => x_msg_data,
1550 p_encoded => FND_API.G_FALSE
1551 );
1552 --
1553 --
1554
1555
1556 --}
1557 EXCEPTION
1558 --{
1559 WHEN FND_API.G_EXC_ERROR THEN
1560 ROLLBACK TO TAKE_TENDER_SNAPSHOT_PUB;
1561 x_return_status := FND_API.G_RET_STS_ERROR;
1562 FND_MSG_PUB.Count_And_Get
1563 (
1564 p_count => x_msg_count,
1565 p_data => x_msg_data,
1566 p_encoded => FND_API.G_FALSE
1567 );
1568 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1569 ROLLBACK TO TAKE_TENDER_SNAPSHOT_PUB;
1570 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1571 FND_MSG_PUB.Count_And_Get
1572 (
1573 p_count => x_msg_count,
1574 p_data => x_msg_data,
1575 p_encoded => FND_API.G_FALSE
1576 );
1577 WHEN OTHERS THEN
1578 ROLLBACK TO TAKE_TENDER_SNAPSHOT_PUB;
1579 wsh_util_core.default_handler('FTE_TENDER_PVT.TAKE_TENDER_SNAPSHOT_PUB');
1580 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1581 FND_MSG_PUB.Count_And_Get
1582 (
1583 p_count => x_msg_count,
1584 p_data => x_msg_data,
1585 p_encoded => FND_API.G_FALSE
1586 );
1587
1588 --}
1589
1590 END TAKE_TENDER_SNAPSHOT;
1591
1592
1593 --{
1594
1595 PROCEDURE TAKE_TENDER_SNAPSHOT(
1596 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
1597 p_trip_id IN NUMBER,
1598 p_action IN VARCHAR2,
1599 x_return_status OUT NOCOPY VARCHAR2,
1600 x_msg_count OUT NOCOPY NUMBER,
1601 x_msg_data OUT NOCOPY VARCHAR2) IS
1602 --{
1603
1604 l_api_name CONSTANT VARCHAR2(30) := 'TAKE_TENDER_SNAPSHOT';
1605 l_api_version CONSTANT NUMBER := 1.0;
1606
1607 --}
1608
1609 -- Cursor to get trip stop weight volume
1610 CURSOR get_trip_stop_info IS
1611 SELECT STOP_ID, DEPARTURE_GROSS_WEIGHT, WEIGHT_UOM_CODE,
1612 DEPARTURE_VOLUME, VOLUME_UOM_CODE
1613 FROM WSH_TRIP_STOPS
1614 WHERE TRIP_ID = p_trip_id
1615 ORDER BY PLANNED_DEPARTURE_DATE, STOP_SEQUENCE_NUMBER,STOP_ID;
1616
1617 l_session_value VARCHAR2(30);
1618
1619 l_return_status VARCHAR2(32767);
1620 l_msg_count NUMBER;
1621 l_msg_data VARCHAR2(32767);
1622 l_number_of_warnings NUMBER;
1623 l_number_of_errors NUMBER;
1624
1625 l_action VARCHAR2(30);
1626
1627 BEGIN
1628 --
1629 -- Standard Start of API savepoint
1630 SAVEPOINT TAKE_TENDER_SNAPSHOT_PUB;
1631 --
1632 --
1633 -- Initialize message list if p_init_msg_list is set to TRUE.
1634 --
1635 --
1636 IF FND_API.to_Boolean( p_init_msg_list )
1637 THEN
1638 FND_MSG_PUB.initialize;
1639 END IF;
1640 --
1641 --
1642 -- Initialize API return status to success
1643 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1644 x_msg_count := 0;
1645 x_msg_data := 0;
1646 l_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1647 l_number_of_warnings := 0;
1648 l_number_of_errors := 0;
1649
1650 l_action := p_action;
1651
1652 IF (p_action = 'REPLACE')
1653 THEN
1654 DELETE_TENDER_SNAPSHOT(
1655 p_init_msg_list => FND_API.G_FALSE,
1656 p_tender_id => p_trip_id,
1657 x_return_status => l_return_status,
1658 x_msg_count => l_msg_count,
1659 x_msg_data => l_msg_data);
1660
1661 wsh_util_core.api_post_call(
1662 p_return_status =>l_return_status,
1663 x_num_warnings =>l_number_of_warnings,
1664 x_num_errors =>l_number_of_errors,
1665 p_msg_data =>l_msg_data);
1666
1667 IF l_number_of_errors > 0
1668 THEN
1669 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1670 RAISE FND_API.G_EXC_ERROR;
1671 ELSIF l_number_of_warnings > 0
1672 THEN
1673 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
1674 ELSE
1675 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1676 END IF;
1677
1678 l_action := 'CREATE';
1679 END IF;
1680
1681
1682 FOR get_trip_stop_info_rec IN get_trip_stop_info
1683 LOOP
1684 --{
1685
1686 TAKE_TENDER_SNAPSHOT(
1687 p_init_msg_list => FND_API.G_FALSE,
1688 p_tender_id => p_trip_id,
1689 p_trip_id => p_trip_id,
1690 p_stop_id => get_trip_stop_info_rec.stop_id,
1691 p_total_weight => get_trip_stop_info_rec.departure_gross_weight,
1692 p_total_volume => get_trip_stop_info_rec.departure_volume,
1693 p_weight_uom => get_trip_stop_info_rec.weight_uom_code,
1694 p_volume_uom => get_trip_stop_info_rec.volume_uom_code,
1695 p_session_value => userenv('SESSIONID'),
1696 p_action => l_action,
1697 x_return_status => l_return_status,
1698 x_msg_count => l_msg_count,
1699 x_msg_data => l_msg_data);
1700
1701 wsh_util_core.api_post_call(
1702 p_return_status =>l_return_status,
1703 x_num_warnings =>l_number_of_warnings,
1704 x_num_errors =>l_number_of_errors,
1705 p_msg_data =>l_msg_data);
1706
1707 --}
1708 END LOOP;
1709
1710 IF l_number_of_errors > 0
1711 THEN
1712 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1713 RAISE FND_API.G_EXC_ERROR;
1714 ELSIF l_number_of_warnings > 0 THEN
1715 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
1716 ELSE
1717 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1718 END IF;
1719
1720
1721 -- Standard call to get message count and if count is 1,get message info.
1722 --
1723 FND_MSG_PUB.Count_And_Get
1724 (
1725 p_count => x_msg_count,
1726 p_data => x_msg_data,
1727 p_encoded => FND_API.G_FALSE
1728 );
1729 --
1730 --
1731
1732
1733 --}
1734 EXCEPTION
1735 --{
1736 WHEN FND_API.G_EXC_ERROR THEN
1737 ROLLBACK TO TAKE_TENDER_SNAPSHOT_PUB;
1738 x_return_status := FND_API.G_RET_STS_ERROR;
1739 FND_MSG_PUB.Count_And_Get
1740 (
1741 p_count => x_msg_count,
1742 p_data => x_msg_data,
1743 p_encoded => FND_API.G_FALSE
1744 );
1745 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1746 ROLLBACK TO TAKE_TENDER_SNAPSHOT_PUB;
1747 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1748 FND_MSG_PUB.Count_And_Get
1749 (
1750 p_count => x_msg_count,
1751 p_data => x_msg_data,
1752 p_encoded => FND_API.G_FALSE
1753 );
1754 WHEN OTHERS THEN
1755 ROLLBACK TO TAKE_TENDER_SNAPSHOT_PUB;
1756 wsh_util_core.default_handler('FTE_TENDER_PVT.TAKE_TENDER_SNAPSHOT_PUB');
1757 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1758 FND_MSG_PUB.Count_And_Get
1759 (
1760 p_count => x_msg_count,
1761 p_data => x_msg_data,
1762 p_encoded => FND_API.G_FALSE
1763 );
1764
1765 --}
1766
1767 END TAKE_TENDER_SNAPSHOT;
1768
1769
1770 --}
1771
1772 --*******************************************************
1773
1774 -- ------------------------------------------------------------------------------- --
1775 -- --
1776 -- NAME: CHECK_THRESHOLD_FOR_STOP --
1777 -- TYPE: PROCEDURE --
1778 -- PARAMETERS (IN): P_api_version IN NUMBER,
1779 -- P_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
1780 -- P_trip_segment_rec IN WSH_TRIPS_GRP.Trip_Pub_Rec_Type,
1781 -- P_old_segment_stop_rec IN WSH_TRIP_STOPS_GRP.Trip_Stop_Pub_Rec_Type,
1782 -- P_new_segment_stop_rec IN WSH_TRIP_STOPS_GRP.Trip_Stop_Pub_Rec_Type
1783 --
1784 -- PARAMETERS (OUT): X_return_status VARCHAR2 --
1785 -- X_msg_count VARCHAR2 --
1786 -- X_msg_data VARCHAR2 --
1787 -- PARAMETERS (IN OUT): none --
1788 -- RETURN: none --
1789 -- DESCRIPTION: this procedure will check if trip is tender or accepted. If it
1790 -- then it will check if the stop weight is within the threshold value
1791 -- specified by carrier. If it exceeds that value then carrier
1792 -- will get an update notification
1793 -- Since this API is checking for one stop at a time
1794 -- we will save a session value into the threshold table
1795 -- As long as session value is same we will not send an update
1796 -- notification. If we do not do this, it will send duplicate
1797 -- notification.
1798 -- --
1799 -- CHANGE CONTROL LOG --
1800 -- ------------------ --
1801 -- --
1802 -- DATE VERSION BY BUG DESCRIPTION --
1803 -- ---------- ------- -------- ------- --------------------------------------- --
1804 -- 2002 11.5.8 HBHAGAVA Created --
1805 -- --
1806 -- ------------------------------------------------------------------------------- --
1807
1808
1809 PROCEDURE CHECK_THRESHOLD_FOR_STOP(
1810 P_api_version IN NUMBER,
1811 P_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
1812 X_return_status OUT NOCOPY VARCHAR2,
1813 X_msg_count OUT NOCOPY NUMBER,
1814 X_msg_data OUT NOCOPY VARCHAR2,
1815 P_trip_segment_rec IN WSH_TRIPS_PVT.Trip_Rec_Type,
1816 P_new_segment_stop_rec IN WSH_TRIP_STOPS_PVT.trip_stop_rec_type)
1817 IS
1818
1819 --{
1820
1821 l_api_name CONSTANT VARCHAR2(30) := 'CHECK_THRESHOLD_FOR_STOP';
1822 l_api_version CONSTANT NUMBER := 1.0;
1823
1824 l_tender_status VARCHAR2(30) := NULL;
1825 l_tender_number NUMBER;
1826 l_trip_id NUMBER;
1827 l_stop_id NUMBER;
1828 l_snap_session_value VARCHAR2(30);
1829 l_snap_tot_weight NUMBER;
1830 l_snap_tot_volume NUMBER;
1831 l_snap_weight_uom VARCHAR2(10);
1832 l_snap_volume_uom VARCHAR2(10);
1833 l_stop_weight_uom VARCHAR2(10);
1834 l_stop_volume_uom VARCHAR2(10);
1835 l_found BOOLEAN := FALSE;
1836 l_org_id NUMBER;
1837 l_carrier_id NUMBER;
1838 l_is_threshold_crossed BOOLEAN := FALSE;
1839 l_send_update BOOLEAN := FALSE;
1840 l_session_count NUMBER;
1841 l_is_session_found BOOLEAN := TRUE;
1842 l_session_value VARCHAR2(30);
1843 l_wf_item_key VARCHAR2(240);
1844 l_carrier_contact_id NUMBER;
1845 x_trip_name VARCHAR2(30);
1846 x_trip_id NUMBER;
1847
1848 l_site_trans_rec WSH_CREATE_CARRIERS_PKG.Site_Rec_Type;
1849
1850
1851 --}
1852 ---
1853 cursor get_snapshot_cur(c_trip_id NUMBER, c_stop_id NUMBER) is
1854 select trip_id, stop_id, load_tender_number,session_value,
1855 total_weight,total_volume,weight_uom,volume_uom
1856 from fte_tender_snapshot
1857 where trip_id = c_trip_id
1858 and stop_id = c_stop_id;
1859 ---
1860 cursor get_tender_number_cur(c_trip_id NUMBER) is
1861 select load_tender_number, wf_item_key,carrier_contact_id
1862 from wsh_trips
1863 where trip_id = c_trip_id;
1864 ---
1865 --
1866 cursor get_snapshot_session_cur(c_trip_id NUMBER, c_tender_number NUMBER,
1867 c_session_value VARCHAR2) is
1868 select session_value
1869 from fte_tender_snapshot
1870 where trip_id = c_trip_id
1871 and load_tender_number = c_tender_number
1872 and session_value = c_session_value;
1873
1874 l_debug_on CONSTANT BOOLEAN := WSH_DEBUG_SV.is_debug_enabled;
1875
1876 l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.' || 'FTE_TENDER_PVT' || '.' || 'CHECK_THRESHOLD_FOR_STOP';
1877
1878 l_number_of_warnings NUMBER;
1879 l_number_of_errors NUMBER;
1880 l_return_status VARCHAR2(32767);
1881 l_msg_count NUMBER;
1882 l_msg_data VARCHAR2(32767);
1883 l_stop_location VARCHAR2(32767);
1884
1885 l_tender_string VARCHAR2(80);
1886
1887 --{
1888 BEGIN
1889 --
1890 -- Standard Start of API savepoint
1891 SAVEPOINT CHECK_THRESHOLD_FOR_STOP_PUB;
1892 --
1893 x_return_status := FND_API.G_RET_STS_SUCCESS;
1894
1895 IF l_debug_on THEN
1896 wsh_debug_sv.push(l_module_name);
1897 END IF;
1898 --
1899 -- Initialize message list if p_init_msg_list is set to TRUE.
1900 --
1901 --
1902 IF FND_API.to_Boolean( p_init_msg_list )
1903 THEN
1904 FND_MSG_PUB.initialize;
1905 END IF;
1906 --
1907 --
1908 -- Initialize API return status to success
1909 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1910 x_msg_count := 0;
1911 x_msg_data := null;
1912 l_number_of_warnings := 0;
1913 l_number_of_errors := 0;
1914
1915
1916 -- check if the trip is tendered
1917 l_tender_status := P_trip_segment_rec.LOAD_TENDER_STATUS;
1918 l_trip_id := P_trip_segment_rec.TRIP_ID;
1919 l_stop_id := P_new_segment_stop_rec.STOP_ID;
1920
1921
1922 IF l_debug_on THEN
1923 WSH_DEBUG_SV.logmsg(l_module_name,'TRIP ID => ' || l_trip_id,WSH_DEBUG_SV.C_PROC_LEVEL);
1924 WSH_DEBUG_SV.logmsg(l_module_name,'STOP ID => ' || l_stop_id,WSH_DEBUG_SV.C_PROC_LEVEL);
1925 WSH_DEBUG_SV.logmsg(l_module_name,'TENDER STATUS => ' || l_tender_status,WSH_DEBUG_SV.C_PROC_LEVEL);
1926 END IF;
1927
1928 IF (l_trip_id IS NULL)
1929 THEN
1930 FND_MESSAGE.SET_NAME('FTE','FTE_INVALID_TRIP_ID_CHK');
1931 WSH_UTIL_CORE.ADD_MESSAGE(WSH_UTIL_CORE.G_RET_STS_ERROR);
1932 RAISE FND_API.G_EXC_ERROR;
1933 END IF;
1934
1935
1936 -- If status is not tendered or accepted then
1937 -- just return back
1938 IF ((l_tender_status IS NULL)
1939 OR (l_tender_status = FND_API.G_MISS_CHAR)
1940 OR (l_tender_status <> FTE_TENDER_PVT.S_TENDERED)
1941 AND (l_tender_status <> FTE_TENDER_PVT.S_ACCEPTED)
1942 AND (l_tender_status <> FTE_TENDER_PVT.S_AUTO_ACCEPTED))
1943 THEN
1944 IF l_debug_on THEN
1945
1946 WSH_DEBUG_SV.logmsg(l_module_name,'TRIP NOT TENDERED OR ACCEPTED',
1947 WSH_DEBUG_SV.C_PROC_LEVEL);
1948 WSH_DEBUG_SV.pop(l_module_name);
1949 END IF;
1950
1951 RETURN;
1952 END IF;
1953
1954 IF (l_stop_id IS NULL)
1955 THEN
1956 -- new stop. Throw error. We cannot take it
1957 l_tender_string := WSH_UTIL_CORE.Get_Lookup_Meaning('WSH_TENDER_STATUS',
1958 l_tender_status);
1959 FND_MESSAGE.SET_NAME('FTE','FTE_INVALID_ACT_CNT_ADD_STOP');
1960 FND_MESSAGE.SET_TOKEN('TRIP_NAME',p_trip_segment_rec.name);
1961 FND_MESSAGE.SET_TOKEN('TENDER_STATUS',l_tender_string);
1962 WSH_UTIL_CORE.ADD_MESSAGE(WSH_UTIL_CORE.G_RET_STS_ERROR);
1963 RAISE FND_API.G_EXC_ERROR;
1964 END IF;
1965
1966
1967 --[HBHAGAVA 10+ Location fix ]
1968 -- If stop is a dummy one then there is no point in doing any of these
1969 -- checks so just return back
1970 IF (p_new_segment_stop_rec.PHYSICAL_STOP_ID IS NOT NULL) THEN
1971 IF l_debug_on THEN
1972
1973 WSH_DEBUG_SV.logmsg(l_module_name,' Stop at internal location return back (10+ Fix )',WSH_DEBUG_SV.C_PROC_LEVEL);
1974 WSH_DEBUG_SV.pop(l_module_name);
1975 END IF;
1976 RETURN;
1977 END IF;
1978
1979
1980
1981
1982 -- Get snap shot info
1983
1984 FOR get_snapshot_rec IN get_snapshot_cur(l_trip_id,l_stop_id)
1985 LOOP
1986 --{
1987 l_found := TRUE;
1988 l_snap_session_value := get_snapshot_rec.session_value;
1989 l_snap_tot_weight := get_snapshot_rec.total_weight;
1990 l_snap_tot_volume := get_snapshot_rec.total_volume;
1991 l_snap_weight_uom := get_snapshot_rec.weight_uom;
1992 l_snap_volume_uom := get_snapshot_rec.volume_uom;
1993 --}
1994 END LOOP;
1995 -- END OF get trip segment info
1996 --
1997 --
1998 IF get_snapshot_cur%ISOPEN THEN
1999 CLOSE get_snapshot_cur;
2000 END IF;
2001 --
2002
2003 -- Get load tender number
2004 OPEN get_tender_number_cur (l_trip_id);
2005 FETCH get_tender_number_cur
2006 INTO l_tender_number,
2007 l_wf_item_key,
2008 l_carrier_contact_id;
2009 CLOSE get_tender_number_cur;
2010
2011
2012 IF (l_tender_number IS NULL)
2013 THEN
2014 FND_MESSAGE.SET_NAME('FTE','FTE_INVALID_TENDER_NUMBER');
2015 WSH_UTIL_CORE.ADD_MESSAGE(WSH_UTIL_CORE.G_RET_STS_ERROR);
2016 RAISE FND_API.G_EXC_ERROR;
2017 END IF;
2018
2019
2020 -- if found then proceed otherwise just take the snapshot
2021 -- and go back
2022 IF (NOT l_found) THEN
2023 -- take snap shot
2024 IF l_debug_on THEN
2025 WSH_DEBUG_SV.logmsg(l_module_name,'No snapshot of the stop. take the snapshot',WSH_DEBUG_SV.C_PROC_LEVEL);
2026 END IF;
2027
2028 TAKE_TENDER_SNAPSHOT(
2029 p_init_msg_list => FND_API.G_FALSE,
2030 p_tender_id => l_tender_number,
2031 p_trip_id => l_trip_id,
2032 p_stop_id => l_stop_id,
2033 p_total_weight => P_new_segment_stop_rec.DEPARTURE_GROSS_WEIGHT,
2034 p_total_volume => P_new_segment_stop_rec.departure_volume,
2035 p_weight_uom => P_new_segment_stop_rec.weight_uom_code,
2036 p_volume_uom => P_new_segment_stop_rec.volume_uom_code,
2037 p_session_value => null,
2038 p_action => 'CREATE',
2039 x_return_status => l_return_status,
2040 x_msg_count => l_msg_count,
2041 x_msg_data => l_msg_data);
2042
2043 wsh_util_core.api_post_call(
2044 p_return_status =>l_return_status,
2045 x_num_warnings =>l_number_of_warnings,
2046 x_num_errors =>l_number_of_errors,
2047 p_msg_data =>l_msg_data);
2048
2049 IF l_number_of_errors > 0
2050 THEN
2051 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2052 RAISE FND_API.G_EXC_ERROR;
2053 ELSIF l_number_of_warnings > 0 THEN
2054 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
2055 ELSE
2056 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2057 END IF;
2058
2059
2060 IF l_debug_on THEN
2061 WSH_DEBUG_SV.pop(l_module_name);
2062 END IF;
2063
2064 RETURN;
2065 END IF;
2066
2067 -- Get the threshold value based on the carrier, org id values
2068 -- Get org id from delivery
2069 l_org_id := FTE_MLS_UTIL.GET_PICKUP_DLVY_ORGID_BY_TRIP(l_trip_id);
2070 l_carrier_id := P_trip_segment_rec.carrier_id;
2071
2072
2073 -- If org id is null then assume that there is no delivery on this
2074 -- trip at the pickup location so cancel the tender
2075 -- send an warning message to shipper
2076 IF (l_org_id IS NULL)
2077 THEN
2078 IF l_debug_on THEN
2079 WSH_DEBUG_SV.logmsg(l_module_name,'No org id. So no delivery. ' ||
2080 'Cancel the tender notification. Warn Shipper',
2081 WSH_DEBUG_SV.C_PROC_LEVEL);
2082 END IF;
2083
2084 FTE_MLS_WRAPPER.CREATE_UPDATE_TRIP
2085 ( p_api_version_number => 1.0,
2086 p_init_msg_list => FND_API.G_FALSE,
2087 p_action_code => 'UPDATE',
2088 p_action => FTE_TENDER_PVT.S_SHIPPER_CANCELLED,
2089 p_rec_TRIP_ID => l_trip_id,
2090 x_return_status => l_return_status,
2091 x_msg_count => l_msg_count,
2092 x_msg_data => l_msg_data,
2093 x_trip_id => x_trip_id,
2094 x_trip_name => x_trip_name);
2095
2096 wsh_util_core.api_post_call(
2097 p_return_status =>l_return_status,
2098 x_num_warnings =>l_number_of_warnings,
2099 x_num_errors =>l_number_of_errors,
2100 p_msg_data =>l_msg_data);
2101
2102 /**
2103 -- Delete tender snapshot
2104 TAKE_TENDER_SNAPSHOT(
2105 p_init_msg_list => FND_API.G_FALSE,
2106 p_tender_id => l_tender_number,
2107 p_trip_id => l_trip_id,
2108 p_stop_id => l_stop_id,
2109 p_total_weight => null,
2110 p_total_volume => null,
2111 p_weight_uom => null,
2112 p_volume_uom => null,
2113 p_session_value => null,
2114 p_action => 'DELETE',
2115 x_return_status => l_return_status,
2116 x_msg_count => l_msg_count,
2117 x_msg_data => l_msg_data);
2118
2119 wsh_util_core.api_post_call(
2120 p_return_status =>l_return_status,
2121 x_num_warnings =>l_number_of_warnings,
2122 x_num_errors =>l_number_of_errors,
2123 p_msg_data =>l_msg_data);
2124 */
2125
2126 FTE_MLS_UTIL.get_location_info(
2127 p_location_id => l_stop_id,
2128 x_location => l_stop_location,
2129 x_return_status => l_return_status);
2130
2131 wsh_util_core.api_post_call(
2132 p_return_status =>l_return_status,
2133 x_num_warnings =>l_number_of_warnings,
2134 x_num_errors =>l_number_of_errors,
2135 p_msg_data =>l_msg_data);
2136
2137 FND_MESSAGE.SET_NAME('FTE','FTE_NO_DLVYFND_CANCEL_TENDER');
2138 FND_MESSAGE.SET_TOKEN('STOP_LOC',l_stop_location);
2139 FND_MESSAGE.SET_TOKEN('TENDER_ID',l_tender_number);
2140 WSH_UTIL_CORE.ADD_MESSAGE(WSH_UTIL_CORE.G_RET_STS_WARNING);
2141 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
2142 FND_MSG_PUB.Count_And_Get
2143 (
2144 p_count => x_msg_count,
2145 p_data => x_msg_data,
2146 p_encoded => FND_API.G_FALSE
2147 );
2148
2149
2150 IF l_debug_on THEN
2151 WSH_DEBUG_SV.pop(l_module_name);
2152 END IF;
2153 RETURN;
2154
2155 END IF;
2156
2157
2158 -- If weights on stop and snapshot are null then there
2159 -- there is no need to call threshold because we cannot compare anything
2160 IF (l_snap_tot_weight IS NULL
2161 AND l_snap_tot_volume IS NULL
2162 AND p_new_segment_stop_rec.departure_gross_weight = FND_API.G_MISS_NUM
2163 AND p_new_segment_stop_rec.departure_volume = FND_API.G_MISS_NUM)
2164 THEN
2165 IF l_debug_on THEN
2166 WSH_DEBUG_SV.logmsg(l_module_name,'Weights/volume at stop and snap shot are null. No need to check anything. Just return back',WSH_DEBUG_SV.C_PROC_LEVEL);
2167 WSH_DEBUG_SV.pop(l_module_name);
2168 END IF;
2169
2170 RETURN;
2171 END IF;
2172
2173 IF l_debug_on THEN
2174 WSH_DEBUG_SV.logmsg(l_module_name,' Organization Id to get site info ' || l_org_id,
2175 WSH_DEBUG_SV.C_PROC_LEVEL);
2176 END IF;
2177
2178
2179 WSH_CREATE_CARRIERS_PKG.Get_Site_Trans_Details(
2180 p_carrier_id => l_carrier_id,
2181 p_organization_id => l_org_id,
2182 x_site_trans_rec => l_site_trans_rec,
2183 x_return_status => x_return_status);
2184
2185 IF (x_return_status = 'E') THEN
2186 FND_MESSAGE.SET_NAME('FTE','FTE_NO_THRESHOLD_INFO');
2187 WSH_UTIL_CORE.ADD_MESSAGE(WSH_UTIL_CORE.G_RET_STS_WARNING);
2188 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
2189 FND_MSG_PUB.Count_And_Get
2190 (
2191 p_count => x_msg_count,
2192 p_data => x_msg_data,
2193 p_encoded => FND_API.G_FALSE
2194 );
2195 IF l_debug_on THEN
2196 WSH_DEBUG_SV.pop(l_module_name);
2197 END IF;
2198 RETURN;
2199 ELSIF (x_return_status = 'U') THEN
2200 FND_MESSAGE.SET_NAME('FTE','FTE_INVALID_THRESHOLD_INFO');
2201 WSH_UTIL_CORE.ADD_MESSAGE(WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR);
2202 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2203 END IF;
2204
2205 IF (l_site_trans_rec.WEIGHT_THRESHOLD_LOWER = NULL
2206 AND l_site_trans_rec.WEIGHT_THRESHOLD_UPPER = NULL
2207 AND l_site_trans_rec.VOLUME_THRESHOLD_LOWER = NULL
2208 AND l_site_trans_rec.VOLUME_THRESHOLD_UPPER = NULL)
2209 THEN
2210 IF l_debug_on THEN
2211 WSH_DEBUG_SV.logmsg(l_module_name,'No Threshold value specified for this carrier.',WSH_DEBUG_SV.C_PROC_LEVEL);
2212 END IF;
2213
2214 -- do we update the snapshot of this stop and just return back
2215
2216 RETURN;
2217 END IF;
2218
2219 IF l_debug_on THEN
2220 WSH_DEBUG_SV.logmsg(l_module_name,' Weight Threshold Lower' ||
2221 l_site_trans_rec.WEIGHT_THRESHOLD_LOWER,WSH_DEBUG_SV.C_PROC_LEVEL);
2222 WSH_DEBUG_SV.logmsg(l_module_name,' Weight Threshold Upper' ||
2223 l_site_trans_rec.WEIGHT_THRESHOLD_UPPER,WSH_DEBUG_SV.C_PROC_LEVEL);
2224 WSH_DEBUG_SV.logmsg(l_module_name,' Volume Threshold Lower' ||
2225 l_site_trans_rec.WEIGHT_THRESHOLD_LOWER,WSH_DEBUG_SV.C_PROC_LEVEL);
2226 WSH_DEBUG_SV.logmsg(l_module_name,' Volume Threshold Upper' ||
2227 l_site_trans_rec.WEIGHT_THRESHOLD_UPPER,WSH_DEBUG_SV.C_PROC_LEVEL);
2228 END IF;
2229
2230
2231 -- Snap shot found so now check the values
2232 -- If weight, weight uom, volume, volume uom are null
2233 -- then check if the stop has them
2234 -- if stop has them then update with stop values
2235 -- if stop does not have them then technically we should throw
2236 -- error. but still need to be decided so for time being
2237 -- we just check the threshold value and see if it crossed with out
2238 -- uom conversions.
2239 l_stop_weight_uom := p_new_segment_stop_rec.weight_uom_code;
2240 l_stop_volume_uom := p_new_segment_stop_rec.volume_uom_code;
2241
2242 IF (l_stop_weight_uom = FND_API.G_MISS_CHAR)
2243 THEN
2244
2245 -- Snap stop weight uom is not null
2246 -- convert stop weight and compare
2247 IF l_debug_on THEN
2248 WSH_DEBUG_SV.logmsg(l_module_name,'Stop weight uom is null. Convert stop weight to snap weight',WSH_DEBUG_SV.C_PROC_LEVEL);
2249 END IF;
2250
2251 l_stop_weight_uom := FND_API.G_MISS_CHAR; --l_snap_weight_uom;
2252
2253 ELSIF (l_stop_weight_uom <> FND_API.G_MISS_CHAR)
2254 THEN
2255
2256 IF (l_snap_weight_uom IS NULL)
2257 THEN
2258 l_snap_weight_uom := l_stop_weight_uom;
2259
2260 ELSIF (l_snap_weight_uom <> l_stop_weight_uom)
2261 THEN
2262 -- convert snap to stop
2263 IF (l_snap_tot_weight IS NOT NULL)
2264 THEN
2265 l_snap_tot_weight :=
2266 INV_CONVERT.inv_um_convert(null,
2267 2,l_snap_tot_weight,
2268 l_snap_weight_uom,
2269 l_stop_weight_uom,NULL,NULL);
2270 END IF;
2271 l_snap_weight_uom := l_stop_weight_uom;
2272
2273 END IF;
2274
2275 END IF;
2276
2277 -- check volume
2278 IF (l_stop_volume_uom = FND_API.G_MISS_CHAR)
2279 THEN
2280 -- Snap stop volume uom is not null
2281 -- convert stop volume and compare
2282 IF l_debug_on THEN
2283 WSH_DEBUG_SV.logmsg(l_module_name,'Stop volume uom is null. Convert stop volume to snap volume',WSH_DEBUG_SV.C_PROC_LEVEL);
2284 END IF;
2285
2286 l_stop_volume_uom := FND_API.G_MISS_CHAR; --l_snap_volume_uom;
2287
2288 ELSIF (l_stop_volume_uom <> FND_API.G_MISS_CHAR)
2289 THEN
2290
2291 IF (l_snap_volume_uom IS NULL)
2292 THEN
2293 l_snap_volume_uom := l_stop_volume_uom;
2294
2295 ELSIF (l_snap_volume_uom <> l_stop_volume_uom)
2296 THEN
2297 -- convert snap to stop
2298 IF (l_snap_tot_volume IS NOT NULL)
2299 THEN
2300 l_snap_tot_volume :=
2301 INV_CONVERT.inv_um_convert(null,
2302 2,l_snap_tot_volume,
2303 l_snap_volume_uom,
2304 l_stop_volume_uom,NULL,NULL);
2305 END IF;
2306 l_snap_volume_uom := l_stop_volume_uom;
2307
2308 END IF;
2309
2310 END IF;
2311
2312
2313 IF ((p_new_segment_stop_rec.departure_gross_weight <> FND_API.G_MISS_NUM)
2314 AND (l_snap_tot_weight IS NULL))
2315 THEN
2316 IF l_debug_on THEN
2317 WSH_DEBUG_SV.logmsg(l_module_name,' stop departure gross weight is not G_MISS_NUMM ',WSH_DEBUG_SV.C_PROC_LEVEL);
2318 WSH_DEBUG_SV.logmsg(l_module_name,' Snapshot total weight is null send update ',WSH_DEBUG_SV.C_PROC_LEVEL);
2319 END IF;
2320
2321 l_send_update := TRUE;
2322
2323 ELSIF ((p_new_segment_stop_rec.departure_gross_weight = FND_API.G_MISS_NUM)
2324 AND (l_snap_tot_weight IS NOT NULL))
2325 THEN
2326 IF l_debug_on THEN
2327 WSH_DEBUG_SV.logmsg(l_module_name,' stop departure gross weight is G_MISS_NUM',WSH_DEBUG_SV.C_PROC_LEVEL);
2328 WSH_DEBUG_SV.logmsg(l_module_name,' Snapshot total weight is not null send update ',WSH_DEBUG_SV.C_PROC_LEVEL);
2329 END IF;
2330 l_send_update := TRUE;
2331
2332 END IF;
2333
2334
2335 IF ((p_new_segment_stop_rec.departure_volume <> FND_API.G_MISS_NUM)
2336 AND (l_snap_tot_volume IS NULL))
2337 THEN
2338 IF l_debug_on THEN
2339 WSH_DEBUG_SV.logmsg(l_module_name,' Stop departure volume is not G_MISS_NUM ',WSH_DEBUG_SV.C_PROC_LEVEL);
2340 WSH_DEBUG_SV.logmsg(l_module_name,' Snapshot total volume is null send update ',WSH_DEBUG_SV.C_PROC_LEVEL);
2341 END IF;
2342 l_send_update := TRUE;
2343
2344 ELSIF ((p_new_segment_stop_rec.departure_volume = FND_API.G_MISS_NUM)
2345 AND (l_snap_tot_volume IS NOT NULL))
2346 THEN
2347 IF l_debug_on THEN
2348 WSH_DEBUG_SV.logmsg(l_module_name,' Stop departure volume is G_MISS_NUM ',WSH_DEBUG_SV.C_PROC_LEVEL);
2349 WSH_DEBUG_SV.logmsg(l_module_name,' Snapshot total volume is not null send update ',WSH_DEBUG_SV.C_PROC_LEVEL);
2350 END IF;
2351 l_send_update := TRUE;
2352 END IF;
2353
2354
2355 IF (NOT l_send_update)
2356 THEN
2357 -- check threshold value
2358 IF (l_site_trans_rec.WEIGHT_THRESHOLD_LOWER <> FND_API.G_MISS_NUM)
2359 THEN
2360 -- Fix for Bug 2783938
2361 IF ( (ABS(l_snap_tot_weight - (l_snap_tot_weight*l_site_trans_rec.WEIGHT_THRESHOLD_LOWER*0.01)))
2362 > p_new_segment_stop_rec.departure_gross_weight)
2363 THEN
2364 IF l_debug_on THEN
2365 WSH_DEBUG_SV.logmsg(l_module_name,' Send Message Because of WEIGHT_THRESHOLD_LOWER ',WSH_DEBUG_SV.C_PROC_LEVEL);
2366 END IF;
2367 l_send_update := TRUE;
2368
2369 END IF;
2370 END IF;
2371
2372 IF (l_site_trans_rec.WEIGHT_THRESHOLD_UPPER <> FND_API.G_MISS_NUM)
2373 THEN
2374 -- Fix for Bug 2783938
2375 IF ( (ABS(l_snap_tot_weight + (l_snap_tot_weight*l_site_trans_rec.WEIGHT_THRESHOLD_UPPER*0.01)))
2376 < p_new_segment_stop_rec.departure_gross_weight)
2377 THEN
2378 IF l_debug_on THEN
2379 WSH_DEBUG_SV.logmsg(l_module_name,' Send Message Because of WEIGHT_THRESHOLD_UPPER ',WSH_DEBUG_SV.C_PROC_LEVEL);
2380 END IF;
2381 l_send_update := TRUE;
2382 END IF;
2383 END IF;
2384
2385 END IF;
2386
2387 IF (NOT l_send_update)
2388 THEN
2389
2390 -- check threshold value
2391 IF (l_site_trans_rec.VOLUME_THRESHOLD_LOWER <> FND_API.G_MISS_NUM)
2392 THEN
2393
2394 -- Fix for Bug 2783938
2395 IF ( (ABS(l_snap_tot_volume - (l_snap_tot_volume*l_site_trans_rec.VOLUME_THRESHOLD_LOWER*0.01)))
2396 > p_new_segment_stop_rec.departure_volume)
2397 THEN
2398 IF l_debug_on THEN
2399 WSH_DEBUG_SV.logmsg(l_module_name,' Send Message Because of VOLUME_THRESHOLD_LOWER ',WSH_DEBUG_SV.C_PROC_LEVEL);
2400 END IF;
2401
2402 l_send_update := TRUE;
2403 END IF;
2404 END IF;
2405
2406 IF (l_site_trans_rec.VOLUME_THRESHOLD_UPPER <> FND_API.G_MISS_NUM)
2407 THEN
2408 -- Fix for Bug 2783938
2409 IF ( (ABS(l_snap_tot_volume + (l_snap_tot_volume*l_site_trans_rec.VOLUME_THRESHOLD_UPPER*0.01)))
2410 < p_new_segment_stop_rec.departure_volume)
2411 THEN
2412 IF l_debug_on THEN
2413 WSH_DEBUG_SV.logmsg(l_module_name,' Send Message Because of VOLUME_THRESHOLD_UPPER ',WSH_DEBUG_SV.C_PROC_LEVEL);
2414 END IF;
2415
2416 l_send_update := TRUE;
2417 END IF;
2418 END IF;
2419
2420
2421 END IF;
2422
2423 -- update the snapshot with current information
2424 IF l_debug_on THEN
2425 WSH_DEBUG_SV.logmsg(l_module_name,' Tender Number ' || l_tender_number,WSH_DEBUG_SV.C_PROC_LEVEL);
2426 WSH_DEBUG_SV.logmsg(l_module_name,' Trip id ' || l_trip_id,WSH_DEBUG_SV.C_PROC_LEVEL);
2427 WSH_DEBUG_SV.logmsg(l_module_name,' Trip stop id ' || l_stop_id,WSH_DEBUG_SV.C_PROC_LEVEL);
2428
2429 WSH_DEBUG_SV.logmsg(l_module_name,'Update Snapshot with stop info ',WSH_DEBUG_SV.C_PROC_LEVEL);
2430
2431 WSH_DEBUG_SV.logmsg(l_module_name,'New Weight ' || p_new_segment_stop_rec.departure_gross_weight,WSH_DEBUG_SV.C_PROC_LEVEL);
2432
2433 WSH_DEBUG_SV.logmsg(l_module_name,'New Weight Uom ' || l_stop_weight_uom,WSH_DEBUG_SV.C_PROC_LEVEL);
2434
2435 WSH_DEBUG_SV.logmsg(l_module_name,'New Volume ' || p_new_segment_stop_rec.departure_volume,WSH_DEBUG_SV.C_PROC_LEVEL);
2436
2437 WSH_DEBUG_SV.logmsg(l_module_name,'New Volume code ' || l_stop_volume_uom,WSH_DEBUG_SV.C_PROC_LEVEL);
2438
2439 WSH_DEBUG_SV.logmsg(l_module_name,'Carrier Contact Id ' || l_carrier_contact_id,WSH_DEBUG_SV.C_PROC_LEVEL);
2440
2441 END IF;
2442
2443 TAKE_TENDER_SNAPSHOT(
2444 p_init_msg_list => FND_API.G_FALSE,
2445 p_tender_id => l_tender_number,
2446 p_trip_id => l_trip_id,
2447 p_stop_id => l_stop_id,
2448 p_total_weight => P_new_segment_stop_rec.DEPARTURE_GROSS_WEIGHT,
2449 p_total_volume => P_new_segment_stop_rec.departure_volume,
2450 p_weight_uom => l_stop_weight_uom,
2451 p_volume_uom => l_stop_volume_uom,
2452 p_session_value => null,
2453 p_action => 'UPDATE',
2454 x_return_status => l_return_status,
2455 x_msg_count => l_msg_count,
2456 x_msg_data => l_msg_data);
2457
2458 wsh_util_core.api_post_call(
2459 p_return_status =>l_return_status,
2460 x_num_warnings =>l_number_of_warnings,
2461 x_num_errors =>l_number_of_errors,
2462 p_msg_data =>l_msg_data);
2463
2464
2465 IF (x_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR OR
2466 x_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)
2467 THEN
2468 IF l_debug_on THEN
2469 WSH_DEBUG_SV.pop(l_module_name);
2470 END IF;
2471 RETURN;
2472 END IF;
2473
2474 IF (l_send_update)
2475 THEN
2476 l_is_session_found := FALSE;
2477
2478 l_session_value := userenv('SESSIONID');
2479 IF l_debug_on THEN
2480 WSH_DEBUG_SV.logmsg(l_module_name,' New Session value' || l_session_value ,WSH_DEBUG_SV.C_PROC_LEVEL);
2481 END IF;
2482
2483 FOR get_snapshot_session_rec IN
2484 get_snapshot_session_cur(l_trip_id,l_tender_number,
2485 l_session_value)
2486 LOOP
2487 --{
2488 l_is_session_found := TRUE;
2489 --}
2490 END LOOP;
2491
2492
2493 IF (NOT l_is_session_found)
2494 THEN
2495 -- update tender snapshot with session value
2496 -- so that we do not send another message to carrier
2497 TAKE_TENDER_SNAPSHOT(
2498 p_init_msg_list => FND_API.G_FALSE,
2499 p_tender_id => l_tender_number,
2500 p_trip_id => l_trip_id,
2501 p_stop_id => l_stop_id,
2502 p_total_weight => NULL,
2503 p_total_volume => NULL,
2504 p_weight_uom => NULL,
2505 p_volume_uom => NULL,
2506 p_session_value => userenv('SESSIONID') || '',
2507 p_action => 'UPDATE',
2508 x_return_status => l_return_status,
2509 x_msg_count => l_msg_count,
2510 x_msg_data => l_msg_data);
2511
2512 wsh_util_core.api_post_call(
2513 p_return_status =>l_return_status,
2514 x_num_warnings =>l_number_of_warnings,
2515 x_num_errors =>l_number_of_errors,
2516 p_msg_data =>l_msg_data);
2517
2518 IF (x_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR OR
2519 x_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)
2520 THEN
2521 IF l_debug_on THEN
2522 WSH_DEBUG_SV.pop(l_module_name);
2523 END IF;
2524 RETURN;
2525 END IF;
2526
2527 IF l_debug_on THEN
2528 WSH_DEBUG_SV.logmsg(l_module_name,'Send Notification ',WSH_DEBUG_SV.C_PROC_LEVEL);
2529 WSH_DEBUG_SV.logmsg(l_module_name,' l_trip_id ' || l_trip_id,WSH_DEBUG_SV.C_PROC_LEVEL);
2530 END IF;
2531
2532 FTE_MLS_WRAPPER.CREATE_UPDATE_TRIP
2533 ( p_api_version_number => 1.0,
2534 p_init_msg_list => FND_API.G_FALSE,
2535 p_action_code => 'UPDATE',
2536 p_action => FTE_TENDER_PVT.S_SHIPPER_UPDATED,
2537 p_rec_TRIP_ID => l_trip_id,
2538 x_return_status => l_return_status,
2539 x_msg_count => l_msg_count,
2540 x_msg_data => l_msg_data,
2541 x_trip_id => x_trip_id,
2542 x_trip_name => x_trip_name);
2543
2544 wsh_util_core.api_post_call(
2545 p_return_status =>l_return_status,
2546 x_num_warnings =>l_number_of_warnings,
2547 x_num_errors =>l_number_of_errors,
2548 p_msg_data =>l_msg_data);
2549
2550
2551 IF l_debug_on THEN
2552 WSH_DEBUG_SV.logmsg(l_module_name,' x_return_status after
2553 CREATE_UPDATE_TRIP ' || x_return_status,WSH_DEBUG_SV.C_PROC_LEVEL);
2554 WSH_DEBUG_SV.logmsg(l_module_name,' x_msg_data
2555 CREATE_UPDATE_TRIP ' || x_msg_data,WSH_DEBUG_SV.C_PROC_LEVEL);
2556
2557 END IF;
2558
2559 IF (x_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR OR
2560 x_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)
2561 THEN
2562 IF l_debug_on THEN
2563 WSH_DEBUG_SV.pop(l_module_name);
2564 END IF;
2565 RETURN;
2566 END IF;
2567
2568
2569 ELSE
2570 IF l_debug_on THEN
2571 WSH_DEBUG_SV.logmsg(l_module_name,'Carrier is notified ',WSH_DEBUG_SV.C_PROC_LEVEL);
2572 END IF;
2573
2574 END IF;
2575
2576 END IF;
2577
2578
2579 IF l_number_of_errors > 0
2580 THEN
2581 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2582 ELSIF l_number_of_warnings > 0
2583 THEN
2584 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
2585 ELSE
2586 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2587 END IF;
2588
2589 IF l_debug_on THEN
2590 WSH_DEBUG_SV.logmsg(l_module_name,'x_return_status:' || x_return_status);
2591 WSH_DEBUG_SV.logmsg(l_module_name,'x_msg_count:' || x_msg_count);
2592 END IF;
2593
2594
2595 IF l_debug_on THEN
2596 WSH_DEBUG_SV.pop(l_module_name);
2597 END IF;
2598
2599 -- Standard call to get message count and if count is 1,get message info.
2600 --
2601 FND_MSG_PUB.Count_And_Get
2602 (
2603 p_count => x_msg_count,
2604 p_data => x_msg_data,
2605 p_encoded => FND_API.G_FALSE
2606 );
2607 --
2608 --
2609
2610 --}
2611 EXCEPTION
2612 --{
2613 WHEN FND_API.G_EXC_ERROR THEN
2614 ROLLBACK TO CHECK_THRESHOLD_FOR_STOP_PUB;
2615 x_return_status := FND_API.G_RET_STS_ERROR ;
2616 FND_MSG_PUB.Count_And_Get
2617 (
2618 p_count => x_msg_count,
2619 p_data => x_msg_data,
2620 p_encoded => FND_API.G_FALSE
2621 );
2622 IF l_debug_on THEN
2623 WSH_DEBUG_SV.pop(l_module_name);
2624 END IF;
2625
2626 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2627 ROLLBACK TO CHECK_THRESHOLD_FOR_STOP_PUB;
2628 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2629 FND_MSG_PUB.Count_And_Get
2630 (
2631 p_count => x_msg_count,
2632 p_data => x_msg_data,
2633 p_encoded => FND_API.G_FALSE
2634 );
2635 IF l_debug_on THEN
2636 WSH_DEBUG_SV.pop(l_module_name);
2637 END IF;
2638 WHEN OTHERS THEN
2639 ROLLBACK TO CHECK_THRESHOLD_FOR_STOP_PUB;
2640 wsh_util_core.default_handler('FTE_TENDER_PVT.CHECK_THRESHOLD_FOR_STOP');
2641 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2642 FND_MSG_PUB.Count_And_Get
2643 (
2644 p_count => x_msg_count,
2645 p_data => x_msg_data,
2646 p_encoded => FND_API.G_FALSE
2647 );
2648 IF l_debug_on THEN
2649 WSH_DEBUG_SV.pop(l_module_name);
2650 END IF;
2651 --}
2652
2653 END CHECK_THRESHOLD_FOR_STOP;
2654
2655
2656 -- ------------------------------------------------------------------------------- --
2657 -- --
2658 -- NAME: HANDLE_WF_ROLES --
2659 -- TYPE: PROCEDURE --
2660 -- PARAMETERS (IN): P_init_msg_list VARCHAR2
2661 --
2662 --
2663 --
2664 --
2665 -- PARAMETERS (OUT): X_return_status VARCHAR2 --
2666 -- X_msg_count VARCHAR2 --
2667 -- X_msg_data VARCHAR2 --
2668 -- PARAMETERS (IN OUT): none --
2669 -- RETURN: none --
2670 -- DESCRIPTION:
2671 -- --
2672 -- CHANGE CONTROL LOG --
2673 -- ------------------ --
2674 -- --
2675 -- DATE VERSION BY BUG DESCRIPTION --
2676 -- ---------- ------- -------- ------- --------------------------------------- --
2677 -- 2004 11.5.10 HBHAGAVA Created --
2678 -- 12-Feb-04 11.5.10 SAMUTHUK Modified --
2679 -- ------------------------------------------------------------------------------- --
2680
2681
2682 PROCEDURE HANDLE_WF_ROLES(
2683 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
2684 x_return_status OUT NOCOPY VARCHAR2,
2685 x_msg_count OUT NOCOPY NUMBER,
2686 x_msg_data OUT NOCOPY VARCHAR2,
2687 x_role_name OUT NOCOPY VARCHAR2,
2688 p_contact_name IN VARCHAR2) IS
2689 --{
2690
2691
2692 l_api_name CONSTANT VARCHAR2(30) := 'HANDLE_WF_ROLES';
2693 l_api_version CONSTANT NUMBER := 1.0;
2694
2695
2696 l_contact_id NUMBER;
2697 l_role_name VARCHAR2(100);
2698 l_display_name VARCHAR2(1000);
2699 hz_party_display_name varchar2(1000);
2700 l_email_address VARCHAR2(1000);
2701 -- Bug 6142080
2702 l_role_email_address VARCHAR2(1000);
2703 l_notif VARCHAR2(1000);
2704 l_lang VARCHAR2(100);
2705 l_ter VARCHAR2(1000);
2706 l_user_list FTE_NAME_TAB_TYPE;
2707 l_user_name VARCHAR2(10000);
2708 idx NUMBER;
2709 l_user_exists BOOLEAN;
2710
2711
2712
2713 --Samuthuk
2714 l_orig_system_id VARCHAR2(100);
2715 l_orig_system VARCHAR2(100);
2716 x_hz_party_user_name VARCHAR2(100);
2717 x_hz_party_display_name VARCHAR2(100);
2718
2719
2720
2721 l_debug_on CONSTANT BOOLEAN := WSH_DEBUG_SV.is_debug_enabled;
2722
2723 l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.' || 'FTE_TENDER_PVT' || '.' || 'HANDLE_WF_ROLES';
2724
2725 CURSOR user_party_cur (carrier_contact_id NUMBER)
2726 IS
2727 SELECT user_name
2728 FROM fnd_user
2729 WHERE customer_id = carrier_contact_id;
2730
2731 /*
2732 New Query for Rel 12 Changes
2733 CURSOR user_party_cur (carrier_contact_id NUMBER)
2734 IS
2735 SELECT user_name
2736 FROM fnd_user
2737 WHERE person_party_id = carrier_contact_id;
2738 */
2739
2740 BEGIN
2741
2742 SAVEPOINT HANDLE_WF_ROLES_PUB;
2743
2744
2745 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2746 x_msg_count := 0;
2747 x_msg_data := 0;
2748
2749 IF l_debug_on THEN
2750 WSH_DEBUG_SV.logmsg(l_module_name,
2751 ' Get HZ_PARTY Role Name ',WSH_DEBUG_SV.C_PROC_LEVEL);
2752 WSH_DEBUG_SV.logmsg(l_module_name,
2753 ' Calling WF_DIRECTORY.GETROLEINFO ',WSH_DEBUG_SV.C_PROC_LEVEL);
2754 END IF;
2755
2756 WF_DIRECTORY.GETROLEINFO (
2757 role => p_contact_name,
2758 display_name => hz_party_display_name,
2759 email_address => l_email_address,
2760 notification_preference => l_notif,
2761 language => l_lang,
2762 territory => l_ter);
2763
2764 IF l_debug_on THEN
2765 WSH_DEBUG_SV.logmsg(l_module_name,
2766 ' HZ_PARTY Role Name ' || hz_party_display_name,
2767 WSH_DEBUG_SV.C_PROC_LEVEL);
2768 END IF;
2769
2770
2771 l_contact_id := to_number(substr(p_contact_name,instr(p_contact_name,':')+1));
2772 l_role_name := 'FTE_TENDER_' || l_contact_id;
2773 x_role_name := l_role_name;
2774
2775 IF l_debug_on THEN
2776 WSH_DEBUG_SV.logmsg(l_module_name,
2777 ' Role Name ' || l_role_name,WSH_DEBUG_SV.C_PROC_LEVEL);
2778 WSH_DEBUG_SV.logmsg(l_module_name,
2779 ' Calling WF_DIRECTORY.GETROLEINFO ',WSH_DEBUG_SV.C_PROC_LEVEL);
2780 END IF;
2781
2782
2783 WF_DIRECTORY.GETROLEINFO (
2784 role => l_role_name,
2785 display_name => l_display_name,
2786 --Modified for bug 6142080
2787 --email_address => l_email_address,
2788 email_address => l_role_email_address,
2789 notification_preference => l_notif,
2790 language => l_lang,
2791 territory => l_ter);
2792
2793 IF l_debug_on THEN
2794 WSH_DEBUG_SV.logmsg(l_module_name,
2795 ' Role Display Name ' || l_display_name,WSH_DEBUG_SV.C_PROC_LEVEL);
2796 END IF;
2797
2798
2799 -- get all users associated with this party
2800 l_user_list := FTE_NAME_TAB_TYPE();
2801
2802 -- add the carrier contact too to the list. Thats how email will be send to
2803 -- carrier contact.
2804 l_user_list.EXTEND;
2805 l_user_list(1) := p_contact_name;
2806
2807 idx := 2;
2808 FOR user_party_rec IN user_party_cur(l_contact_id)
2809 LOOP
2810 --{
2811 l_user_list.EXTEND;
2812 IF l_debug_on THEN
2813 WSH_DEBUG_SV.logmsg(l_module_name,
2814 ' Adding user name to list ' || user_party_rec.user_name,
2815 WSH_DEBUG_SV.C_PROC_LEVEL);
2816 END IF;
2817 l_user_list(idx) := user_party_rec.user_name;
2818 l_user_name := l_user_name || user_party_rec.user_name || ',';
2819 idx := idx+1;
2820 --}
2821 END LOOP;
2822
2823
2824 l_user_name := substr(l_user_name,1,length(l_user_name)-1);
2825 IF l_debug_on THEN
2826 WSH_DEBUG_SV.logmsg(l_module_name,
2827 ' User List ' || l_user_name,
2828 WSH_DEBUG_SV.C_PROC_LEVEL);
2829 END IF;
2830
2831 IF (l_display_name IS NULL)
2832 THEN
2833
2834 IF l_debug_on THEN
2835 WSH_DEBUG_SV.logmsg(l_module_name,
2836 ' Role Does not exists Role name is ' || hz_party_display_name,
2837 WSH_DEBUG_SV.C_PROC_LEVEL);
2838 END IF;
2839 WF_DIRECTORY.CreateAdHocRole(
2840 role_name => l_role_name,
2841 role_display_name => hz_party_display_name,
2842 role_users => l_user_name,
2843 email_address => l_email_address,
2844 expiration_date => null);
2845 ELSE
2846 IF l_debug_on THEN
2847 WSH_DEBUG_SV.logmsg(l_module_name,
2848 ' Role Exists So add users ',
2849 WSH_DEBUG_SV.C_PROC_LEVEL);
2850 END IF;
2851
2852 IF l_user_list.count > 0
2853 THEN
2854 idx:= l_user_list.FIRST;
2855
2856 WHILE idx IS NOT NULL
2857 LOOP
2858 l_user_name := l_user_list(idx);
2859 IF l_debug_on THEN
2860 WSH_DEBUG_SV.logmsg(l_module_name,
2861 ' Cheking User ' || l_user_name,
2862 WSH_DEBUG_SV.C_PROC_LEVEL);
2863 END IF;
2864 l_user_exists := WF_DIRECTORY.IsPerformer(
2865 l_user_name, l_role_name);
2866 if (l_user_exists) THEN
2867 IF l_debug_on THEN
2868 WSH_DEBUG_SV.logmsg(l_module_name,
2869 ' User Exists do not create ',
2870 WSH_DEBUG_SV.C_PROC_LEVEL);
2871 END IF;
2872 ELSE
2873
2874 IF l_debug_on THEN
2875 WSH_DEBUG_SV.logmsg(l_module_name,
2876 ' User does not Exists create ',
2877 WSH_DEBUG_SV.C_PROC_LEVEL);
2878 END IF;
2879 WF_DIRECTORY.AddUsersToAdHocRole(
2880 role_name => l_role_name,
2881 role_users => l_user_name);
2882 END IF;
2883 idx := l_user_list.next(idx);
2884
2885 END LOOP;
2886 END IF;
2887
2888 END IF;
2889
2890 /**
2891 ------------------------- Samuthuk : Workflow Notification -------------------------
2892 --This Section Updates the display name of Newly/Existing Fte_Tender:<contact_id> --
2893 --with the display name of Existing HZ_PARTY:<contact_id> . --
2894 ------------------------------------------------------------------------------------
2895 l_orig_system := 'HZ_PARTY';
2896 l_orig_system_id := to_char(l_contact_id);
2897
2898 WF_DIRECTORY.GetUserName(p_orig_system => l_orig_system,
2899 p_orig_system_id => l_orig_system_id,
2900 p_name => x_hz_party_user_name,
2901 p_display_name => x_hz_party_display_name);
2902
2903 IF x_hz_party_display_name is NOT NULL THEN
2904
2905 WF_DIRECTORY.SetAdHocRoleAttr(role_name => l_role_name,
2906 display_name => x_hz_party_display_name);
2907 END IF;
2908
2909 IF l_debug_on THEN
2910 WSH_DEBUG_SV.logmsg(l_module_name,
2911 ' Display Name of '||l_role_name||' has been updated',
2912 WSH_DEBUG_SV.C_PROC_LEVEL);
2913 END IF;
2914 -----------------------------------------------------------------------------------
2915 */
2916
2917 -- Standard call to get message count and if count is 1,get message info.
2918 --
2919 FND_MSG_PUB.Count_And_Get
2920 (
2921 p_count => x_msg_count,
2922 p_data => x_msg_data,
2923 p_encoded => FND_API.G_FALSE
2924 );
2925 --
2926 --
2927
2928 --}
2929 EXCEPTION
2930 --{
2931 WHEN FND_API.G_EXC_ERROR THEN
2932 ROLLBACK TO HANDLE_WF_ROLES_PUB;
2933 x_return_status := FND_API.G_RET_STS_ERROR ;
2934 FND_MSG_PUB.Count_And_Get
2935 (
2936 p_count => x_msg_count,
2937 p_data => x_msg_data,
2938 p_encoded => FND_API.G_FALSE
2939 );
2940 IF l_debug_on THEN
2941 WSH_DEBUG_SV.pop(l_module_name);
2942 END IF;
2943
2944 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2945 ROLLBACK TO HANDLE_WF_ROLES_PUB;
2946 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2947 FND_MSG_PUB.Count_And_Get
2948 (
2949 p_count => x_msg_count,
2950 p_data => x_msg_data,
2951 p_encoded => FND_API.G_FALSE
2952 );
2953 IF l_debug_on THEN
2954 WSH_DEBUG_SV.pop(l_module_name);
2955 END IF;
2956 WHEN OTHERS THEN
2957 ROLLBACK TO HANDLE_WF_ROLES_PUB;
2958 wsh_util_core.default_handler('FTE_TENDER_PVT.HANDLE_WF_ROLES');
2959 x_return_status := FND_API.G_RET_STS_ERROR;
2960 FND_MSG_PUB.Count_And_Get
2961 (
2962 p_count => x_msg_count,
2963 p_data => x_msg_data,
2964 p_encoded => FND_API.G_FALSE
2965 );
2966 IF l_debug_on THEN
2967 WSH_DEBUG_SV.pop(l_module_name);
2968 END IF;
2969 --}
2970
2971 END HANDLE_WF_ROLES;
2972
2973
2974
2975 -- ------------------------------------------------------------------------------- --
2976 -- --
2977 -- NAME: RAISE_TENDER_EVENT --
2978 -- TYPE: PROCEDURE --
2979 -- PARAMETERS (IN): P_init_msg_list VARCHAR2
2980 -- p_trip_segment_id NUMBER
2981 -- p_trip_segment_name VARCHAR2
2982 -- p_tender_id NUMBER
2983 --
2984 -- PARAMETERS (OUT): X_return_status VARCHAR2 --
2985 -- X_msg_count VARCHAR2 --
2986 -- X_msg_data VARCHAR2 --
2987 -- X_tender_id VARCHAR2 --
2988 -- PARAMETERS (IN OUT): none --
2989 -- RETURN: none --
2990 -- DESCRIPTION: This procedure will start load tendering process by taking --
2991 -- in a trip segment id. This procedure will identify if --
2992 -- we are going to issue multiple tenders for a trip segment
2993 -- or single tender based on consolidation flag. This procedure--
2994 -- will also generate tender id based on the consolidation flag--
2995 -- If tender id is passed to this procedure then that id will --
2996 -- be used to tender. If tender id passed is less than 0 then --
2997 -- a new tender id will be generated and will be returned --
2998 -- back to the calling API --
2999 -- calling function should commit the transaction --
3000 -- --
3001 -- CHANGE CONTROL LOG --
3002 -- ------------------ --
3003 -- --
3004 -- DATE VERSION BY BUG DESCRIPTION --
3005 -- ---------- ------- -------- ------- --------------------------------------- --
3006 -- 2002 11.5.8 HBHAGAVA Created --
3007 -- --
3008 -- ------------------------------------------------------------------------------- --
3009
3010
3011 PROCEDURE RAISE_TENDER_EVENT(
3012 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
3013 x_return_status OUT NOCOPY VARCHAR2,
3014 x_msg_count OUT NOCOPY NUMBER,
3015 x_msg_data OUT NOCOPY VARCHAR2,
3016 p_tender_id IN NUMBER,
3017 p_item_key IN VARCHAR2,
3018 p_shipper_wait_time IN NUMBER,
3019 p_shipper_name IN VARCHAR2,
3020 p_carrier_name IN VARCHAR2,
3021 p_contact_perf IN VARCHAR2,
3022 p_contact_name IN VARCHAR2,
3023 p_autoaccept IN VARCHAR2,
3024 p_action IN VARCHAR2,
3025 p_url IN VARCHAR2) IS
3026 --{
3027
3028
3029 l_api_name CONSTANT VARCHAR2(30) := 'RAISE_TENDER_EVENT';
3030 l_api_version CONSTANT NUMBER := 1.0;
3031
3032 l_number_of_warnings NUMBER;
3033 l_number_of_errors NUMBER;
3034 l_return_status VARCHAR2(32767);
3035 l_msg_count NUMBER;
3036 l_msg_data VARCHAR2(32767);
3037 l_role_name VARCHAR2(10000);
3038
3039 BEGIN
3040
3041 SAVEPOINT RAISE_TENDER_EVENT_PUB;
3042
3043
3044 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
3045 x_msg_count := 0;
3046 x_msg_data := 0;
3047
3048 IF FND_API.to_Boolean( p_init_msg_list )
3049 THEN
3050 FND_MSG_PUB.initialize;
3051 END IF;
3052
3053 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
3054 x_msg_count := 0;
3055 x_msg_data := 0;
3056 l_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
3057 l_number_of_warnings := 0;
3058 l_number_of_errors := 0;
3059
3060 -- As per PackJ to show notifications for carrier user, in worklist
3061 -- Call HANDLE_WF_ROLE procedure. Get the role_name and assign that
3062 -- as the performer p_contact_perf.
3063
3064 IF (p_action = FTE_TENDER_PVT.S_TENDERED OR
3065 p_action = FTE_TENDER_PVT.S_SHIPPER_CANCELLED OR
3066 p_action = FTE_TENDER_PVT.S_SHIPPER_UPDATED)
3067 THEN
3068
3069 HANDLE_WF_ROLES(
3070 p_init_msg_list => FND_API.G_FALSE,
3071 x_return_status => l_return_status,
3072 x_msg_count => l_msg_count,
3073 x_msg_data => l_msg_data,
3074 x_role_name => l_role_name,
3075 p_contact_name => p_contact_perf);
3076
3077 wsh_util_core.api_post_call(
3078 p_return_status =>l_return_status,
3079 x_num_warnings =>l_number_of_warnings,
3080 x_num_errors =>l_number_of_errors,
3081 p_msg_data =>l_msg_data);
3082
3083 IF l_number_of_errors > 0
3084 THEN
3085 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3086 RAISE FND_API.G_EXC_ERROR;
3087 ELSIF l_number_of_warnings > 0
3088 THEN
3089 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
3090 ELSE
3091 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
3092 END IF;
3093 END IF;
3094
3095 IF (p_action = FTE_TENDER_PVT.S_TENDERED) THEN
3096
3097 RAISE_TENDER_REQUEST(
3098 p_init_msg_list => p_init_msg_list,
3099 x_return_status => x_return_status,
3100 x_msg_count => x_msg_count,
3101 x_msg_data => x_msg_data,
3102 p_tender_id => p_tender_id,
3103 p_item_key => p_item_key,
3104 p_shipper_wait_time => p_shipper_wait_time,
3105 p_shipper_name => p_shipper_name,
3106 p_carrier_name => p_carrier_name,
3107 p_contact_name => p_contact_name,
3108 p_autoaccept => p_autoaccept,
3109 --p_contact_perf => p_contact_perf,
3110 p_contact_perf => l_role_name,
3111 p_url => p_url);
3112 ELSIF (p_action = FTE_TENDER_PVT.S_ACCEPTED) THEN
3113 -- For accept / reject we do not have to send
3114 -- role name. Because it is not changed.
3115 RAISE_TENDER_ACCEPT(
3116 p_init_msg_list => p_init_msg_list,
3117 x_return_status => x_return_status,
3118 x_msg_count => x_msg_count,
3119 x_msg_data => x_msg_data,
3120 p_item_key => p_item_key,
3121 p_tender_id => p_tender_id,
3122 p_shipper_name => p_shipper_name,
3123 p_carrier_name => p_carrier_name,
3124 p_contact_name => p_contact_name,
3125 p_contact_perf => p_contact_perf);
3126 --p_contact_perf => l_role_name);
3127 ELSIF (p_action = FTE_TENDER_PVT.S_REJECTED) THEN
3128 RAISE_TENDER_REJECT(
3129 p_init_msg_list => p_init_msg_list,
3130 x_return_status => x_return_status,
3131 x_msg_count => x_msg_count,
3132 x_msg_data => x_msg_data,
3133 p_item_key => p_item_key,
3134 p_tender_id => p_tender_id,
3135 p_shipper_name => p_shipper_name,
3136 p_carrier_name => p_carrier_name,
3137 p_contact_name => p_contact_name,
3138 p_contact_perf => p_contact_perf);
3139 --p_contact_perf => l_role_name);
3140 ELSIF (p_action = FTE_TENDER_PVT.S_SHIPPER_CANCELLED) THEN
3141 RAISE_TENDER_CANCEL(
3142 p_init_msg_list => p_init_msg_list,
3143 x_return_status => x_return_status,
3144 x_msg_count => x_msg_count,
3145 x_msg_data => x_msg_data,
3146 p_item_key => p_item_key,
3147 p_tender_id => p_tender_id,
3148 p_shipper_name => p_shipper_name,
3149 p_carrier_name => p_carrier_name,
3150 p_contact_name => p_contact_name,
3151 --p_contact_perf => p_contact_perf);
3152 p_contact_perf => l_role_name);
3153 ELSIF (p_action = FTE_TENDER_PVT.S_SHIPPER_UPDATED) THEN
3154 RAISE_TENDER_UPDATE(
3155 p_init_msg_list => p_init_msg_list,
3156 x_return_status => x_return_status,
3157 x_msg_count => x_msg_count,
3158 x_msg_data => x_msg_data,
3159 p_item_key => p_item_key,
3160 p_tender_id => p_tender_id,
3161 --p_contact_perf => p_contact_perf);
3162 p_contact_perf => l_role_name);
3163 END IF;
3164
3165
3166 -- Standard call to get message count and if count is 1,get message info.
3167 --
3168 FND_MSG_PUB.Count_And_Get
3169 (
3170 p_count => x_msg_count,
3171 p_data => x_msg_data,
3172 p_encoded => FND_API.G_FALSE
3173 );
3174 --
3175 --
3176
3177 --}
3178 EXCEPTION
3179 --{
3180 WHEN FND_API.G_EXC_ERROR THEN
3181 ROLLBACK TO RAISE_TENDER_EVENT_PUB;
3182 x_return_status := FND_API.G_RET_STS_ERROR ;
3183 FND_MSG_PUB.Count_And_Get
3184 (
3185 p_count => x_msg_count,
3186 p_data => x_msg_data,
3187 p_encoded => FND_API.G_FALSE
3188 );
3189 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3190 ROLLBACK TO RAISE_TENDER_EVENT_PUB;
3191 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3192 FND_MSG_PUB.Count_And_Get
3193 (
3194 p_count => x_msg_count,
3195 p_data => x_msg_data,
3196 p_encoded => FND_API.G_FALSE
3197 );
3198 WHEN OTHERS THEN
3199 ROLLBACK TO RAISE_TENDER_EVENT_PUB;
3200 wsh_util_core.default_handler('FTE_TENDER_PVT.RAISE_TENDER_EVENT');
3201 x_return_status := FND_API.G_RET_STS_ERROR;
3202 FND_MSG_PUB.Count_And_Get
3203 (
3204 p_count => x_msg_count,
3205 p_data => x_msg_data,
3206 p_encoded => FND_API.G_FALSE
3207 );
3208
3209 --}
3210
3211 END RAISE_TENDER_EVENT;
3212
3213
3214 PROCEDURE LOG_CARRIER_ARR_EXC(
3215 p_tender_id IN NUMBER,
3216 p_planned_arrival_date IN DATE,
3217 p_carrier_est_arrival_date IN DATE,
3218 p_first_stop_location_id in Number,
3219 P_planned_departure_date in date,
3220 P_carrier_est_departure_date in date,
3221 P_last_stop_location_id IN NUMBER,
3222 x_return_status OUT NOCOPY VARCHAR2,
3223 x_msg_count OUT NOCOPY NUMBER,
3224 x_msg_data OUT NOCOPY VARCHAR2
3225 )IS
3226
3227 l_arrival_time number;
3228
3229 --local variable for logging exception at Pickup
3230 l_pexception_msg_count NUMBER;
3231 l_pexception_name varchar2(100);
3232 l_pexception_msg_data varchar2(2000);
3233 l_pdummy_exception_id NUMBER;
3234 l_preturn_status VARCHAR2(1);
3235 l_pmsg varchar2(2000);
3236
3237 --To Handle Errors
3238 l_number_of_pwarnings NUMBER;
3239 l_number_of_perrors NUMBER;
3240 l_number_of_dwarnings NUMBER;
3241 l_number_of_derrors NUMBER;
3242
3243 --local variable for logging exception at Dropoff
3244 l_dexception_msg_count NUMBER;
3245 l_dexception_name varchar2(100);
3246 l_dexception_msg_data varchar2(2000);
3247 l_ddummy_exception_id NUMBER;
3248 l_dreturn_status VARCHAR2(1);
3249 l_dmsg varchar2(2000);
3250
3251 l_debug_on CONSTANT BOOLEAN := WSH_DEBUG_SV.is_debug_enabled;
3252 l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.' || 'FTE_TENDER_PVT'|| '.' || 'CHECK_CARRIER_ARRIVAL_TIME';
3253
3254
3255 BEGIN
3256
3257 SAVEPOINT LOG_CARRIER_ARR_EXC_PUB;
3258 l_arrival_time := fnd_profile.value('FTE_CARRIER_ARR_WINDOW');
3259 IF l_debug_on THEN
3260 WSH_DEBUG_SV.push(l_module_name);
3261 END IF;
3262
3263 if (p_carrier_est_departure_date is not null ) then
3264 if ( (ABS(p_planned_departure_date - p_carrier_est_departure_date)* 24) > l_arrival_time) then
3265 l_pmsg := FND_MESSAGE.Get_String('FTE', 'FTE_CARRIER_PTIME');
3266 l_pexception_name :='FTE_CARRIER_PTIME';
3267
3268
3269
3270 wsh_xc_util.log_exception(
3271 p_api_version => 1.0,
3272 x_return_status => l_preturn_status,
3273 x_msg_count => l_pexception_msg_count,
3274 x_msg_data => l_pexception_msg_data,
3275 x_exception_id => l_pdummy_exception_id ,
3276 p_logged_at_location_id => p_first_stop_location_id,
3277 p_exception_location_id => p_first_stop_location_id,
3278 p_logging_entity => 'SHIPPER',
3279 p_logging_entity_id => FND_GLOBAL.USER_ID,
3280 p_exception_name => l_pexception_name,
3281 p_message => l_pmsg,
3282 p_trip_id => p_tender_id
3283 );
3284
3285
3286
3287 --handling Errors
3288 FND_MSG_PUB.Count_And_Get
3289 (
3290 p_count => x_msg_count,
3291 p_data => x_msg_data,
3292 p_encoded => FND_API.G_FALSE
3293 );
3294
3295 wsh_util_core.api_post_call(
3296 p_return_status =>l_preturn_status,
3297 x_num_warnings =>l_number_of_pwarnings,
3298 x_num_errors =>l_number_of_perrors,
3299 p_msg_data =>x_msg_data);
3300
3301 IF l_number_of_perrors > 0 THEN
3302 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3303 RAISE FND_API.G_EXC_ERROR;
3304 ELSIF l_number_of_pwarnings > 0 THEN
3305 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
3306 ELSE
3307 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
3308 END IF;
3309 end if ;
3310 end if ;
3311
3312 if (p_carrier_est_arrival_date is not null ) then
3313 if ( (ABS(p_planned_arrival_date - p_carrier_est_arrival_date)* 24) > l_arrival_time) then
3314 l_dmsg := FND_MESSAGE.Get_String('FTE', 'FTE_CARRIER_DTIME');
3315 l_dexception_name :='FTE_CARRIER_DTIME';
3316
3317 wsh_xc_util.log_exception(
3318 p_api_version => 1.0,
3319 x_return_status => l_dreturn_status,
3320 x_msg_count => l_dexception_msg_count,
3321 x_msg_data => l_dexception_msg_data,
3322 x_exception_id => l_ddummy_exception_id ,
3323 p_logged_at_location_id => p_last_stop_location_id,
3324 p_exception_location_id => p_last_stop_location_id,
3325 p_logging_entity => 'SHIPPER',
3326 p_logging_entity_id => FND_GLOBAL.USER_ID,
3327 p_exception_name => l_dexception_name,
3328 p_message => l_dmsg,
3329 p_trip_id => p_tender_id
3330 );
3331
3332 IF l_debug_on THEN
3333 WSH_DEBUG_SV.logmsg(l_module_name,' Inside 4 '||l_dreturn_status,
3334 WSH_DEBUG_SV.C_PROC_LEVEL);
3335 END IF ;
3336 --handling Errors
3337 FND_MSG_PUB.Count_And_Get
3338 (
3339 p_count => x_msg_count,
3340 p_data => x_msg_data,
3341 p_encoded => FND_API.G_FALSE
3342 );
3343
3344 wsh_util_core.api_post_call(
3345 p_return_status =>l_dreturn_status,
3346 x_num_warnings =>l_number_of_dwarnings,
3347 x_num_errors =>l_number_of_derrors,
3348 p_msg_data =>x_msg_data);
3349
3350 IF l_number_of_derrors > 0 THEN
3351 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3352 RAISE FND_API.G_EXC_ERROR;
3353 ELSIF l_number_of_dwarnings > 0 THEN
3354 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
3355 ELSE
3356 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
3357 END IF;
3358 END IF ;
3359 END IF ;
3360
3361
3362 IF l_debug_on THEN
3363 WSH_DEBUG_SV.pop(l_module_name);
3364 END IF;
3365
3366
3367 EXCEPTION
3368
3369 WHEN FND_API.G_EXC_ERROR THEN
3370 ROLLBACK TO LOG_CARRIER_ARR_EXC_pub;
3371 x_return_status := FND_API.G_RET_STS_ERROR ;
3372 FND_MSG_PUB.Count_And_Get
3373 (
3374 p_count => x_msg_count,
3375 p_data => x_msg_data,
3376 p_encoded => FND_API.G_FALSE
3377 );
3378 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3379 ROLLBACK TO LOG_CARRIER_ARR_EXC_pub;
3380 x_return_status := FND_API.G_RET_STS_ERROR ;
3381 FND_MSG_PUB.Count_And_Get
3382 (
3383 p_count => x_msg_count,
3384 p_data => x_msg_data,
3385 p_encoded => FND_API.G_FALSE
3386 );
3387 WHEN OTHERS THEN
3388 ROLLBACK TO LOG_CARRIER_ARR_EXC_pub;
3389 wsh_util_core.default_handler('FTE_TENDER_PVT.LOG_CARRIER_ARR_EXC');
3390 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
3391 FND_MSG_PUB.Count_And_Get
3392 (
3393 p_count => x_msg_count,
3394 p_data => x_msg_data,
3395 p_encoded => FND_API.G_FALSE
3396 );
3397 END LOG_CARRIER_ARR_EXC;
3398
3399
3400
3401
3402 PROCEDURE CHECK_CARRIER_ARRIVAL_TIME(
3403 p_tender_id IN NUMBER,
3404 x_return_status OUT NOCOPY VARCHAR2,
3405 x_msg_count OUT NOCOPY NUMBER,
3406 x_msg_data OUT NOCOPY VARCHAR2
3407 )IS
3408
3409 l_arrival_time number;
3410
3411 --local variables for first pick up stop
3412 l_planned_arrival_date date;
3413 l_carrier_est_arrival_date date;
3414 l_first_stop_location_id Number;
3415
3416 --local variables for last stop
3417 l_planned_departure_date date;
3418 l_carrier_est_departure_date date;
3419 l_last_stop_location_id Number;
3420
3421 l_debug_on CONSTANT BOOLEAN := WSH_DEBUG_SV.is_debug_enabled;
3422 l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.' || 'FTE_TENDER_PVT'|| '.' || 'CHECK_CARRIER_ARRIVAL_TIME';
3423
3424 l_return_status VARCHAR2(32767);
3425 l_msg_count NUMBER;
3426 l_msg_data VARCHAR2(32767);
3427
3428 --Picks up the first stop information
3429 CURSOR c_first_stop_of_trip( p_tender_id NUMBER) IS
3430 SELECT PLANNED_DEPARTURE_DATE, CARRIER_EST_DEPARTURE_DATE, STOP_LOCATION_ID FROM wsh_trip_stops
3431 WHERE trip_id = p_tender_id AND
3432 PLANNED_DEPARTURE_DATE = (SELECT MIN(PLANNED_DEPARTURE_DATE)
3433 FROM wsh_trip_stops
3434 WHERE trip_id = p_tender_id );
3435
3436 --Picks up the last stop information
3437 CURSOR c_last_stop_of_trip( p_tender_id NUMBER) IS
3438 SELECT PLANNED_ARRIVAL_DATE, CARRIER_EST_ARRIVAL_DATE, STOP_LOCATION_ID FROM wsh_trip_stops
3439 WHERE trip_id = p_tender_id AND
3440 PLANNED_ARRIVAL_DATE = (SELECT MAX(PLANNED_ARRIVAL_DATE)
3441 FROM wsh_trip_stops
3442 WHERE trip_id = p_tender_id );
3443
3444
3445 BEGIN
3446
3447 SAVEPOINT CHECK_CARRIER_ARRIVAL_TIME_PUB;
3448 l_arrival_time := fnd_profile.value('FTE_CARRIER_ARR_WINDOW');
3449
3450 OPEN c_first_stop_of_trip (p_tender_id);
3451 FETCH c_first_stop_of_trip into l_planned_departure_date,l_carrier_est_departure_date, l_first_stop_location_id;
3452 CLOSE c_first_stop_of_trip;
3453
3454 OPEN c_last_stop_of_trip (p_tender_id);
3455 FETCH c_last_stop_of_trip into l_planned_arrival_date,
3456 l_carrier_est_arrival_date,l_last_stop_location_id;
3457 CLOSE c_last_stop_of_trip;
3458
3459 IF l_debug_on THEN
3460 WSH_DEBUG_SV.push(l_module_name);
3461 END IF;
3462
3463 LOG_CARRIER_ARR_EXC(
3464 p_tender_id =>p_tender_id,
3465 p_planned_arrival_date =>l_planned_arrival_date,
3466 p_carrier_est_arrival_date =>l_carrier_est_arrival_date ,
3467 P_last_stop_location_id =>l_last_stop_location_id,
3468
3469 P_planned_departure_date =>l_planned_departure_date,
3470 P_carrier_est_departure_date =>l_carrier_est_arrival_date,
3471 p_first_stop_location_id =>l_first_stop_location_id ,
3472 x_return_status =>l_return_status,
3473 x_msg_count =>l_msg_count,
3474 x_msg_data =>l_msg_data
3475 );
3476
3477
3478 IF l_debug_on THEN
3479 WSH_DEBUG_SV.pop(l_module_name);
3480 END IF;
3481
3482
3483 EXCEPTION
3484
3485 WHEN FND_API.G_EXC_ERROR THEN
3486 ROLLBACK TO CHECK_CARRIER_ARRIVAL_TIME_PUB;
3487 x_return_status := FND_API.G_RET_STS_ERROR ;
3488 FND_MSG_PUB.Count_And_Get
3489 (
3490 p_count => x_msg_count,
3491 p_data => x_msg_data,
3492 p_encoded => FND_API.G_FALSE
3493 );
3494 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3495 ROLLBACK TO CHECK_CARRIER_ARRIVAL_TIME_PUB;
3496 x_return_status := FND_API.G_RET_STS_ERROR ;
3497 FND_MSG_PUB.Count_And_Get
3498 (
3499 p_count => x_msg_count,
3500 p_data => x_msg_data,
3501 p_encoded => FND_API.G_FALSE
3502 );
3503 WHEN OTHERS THEN
3504 ROLLBACK TO CHECK_CARRIER_ARRIVAL_TIME_PUB;
3505 wsh_util_core.default_handler('FTE_TENDER_PVT.CHECK_CARRIER_ARRIVAL_TIME');
3506 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
3507 FND_MSG_PUB.Count_And_Get
3508 (
3509 p_count => x_msg_count,
3510 p_data => x_msg_data,
3511 p_encoded => FND_API.G_FALSE
3512 );
3513 END CHECK_CARRIER_ARRIVAL_TIME;
3514
3515
3516
3517 PROCEDURE COMPLETE_CANCEL_TENDER (
3518 p_tender_id IN NUMBER,
3519 x_return_status OUT NOCOPY VARCHAR2,
3520 x_msg_count OUT NOCOPY NUMBER,
3521 x_msg_data OUT NOCOPY VARCHAR2
3522 ) IS
3523
3524 l_exception_msg_count NUMBER;
3525 l_exception_msg_data varchar2(2000);
3526 l_dummy_exception_id NUMBER;
3527 l_return_status VARCHAR2(1) := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
3528 l_msg VARCHAR2(2000);
3529 l_validation_level NUMBER := FND_API.G_VALID_LEVEL_FULL;
3530
3531 l_msg varchar2(2000);
3532 l_msg_count NUMBER ;
3533 l_msg_data VARCHAR2(200);
3534 l_exception_id NUMBER ;
3535 l_new_status VARCHAR2(100);
3536 l_status VARCHAR2(100);
3537
3538 l_number_of_warnings NUMBER;
3539 l_number_of_errors NUMBER;
3540
3541 l_debug_on CONSTANT BOOLEAN := WSH_DEBUG_SV.is_debug_enabled;
3542 l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.' || 'FTE_TENDER_PVT'|| '.' || 'COMPLETE_CANCEL_TENDER';
3543
3544 cursor check_exception_on_trip(p_tender_id Number) is
3545 select exception_id, status from
3546 wsh_exceptions we, wsh_trips wt where we.trip_id = wt.trip_id and
3547 (we.exception_name = 'FTE_CARRIER_PTIME' OR
3548 we.exception_name = 'FTE_CARRIER_DTIME') AND
3549 we.status <> 'CLOSED' AND
3550 wt.trip_id = p_tender_id;
3551
3552
3553 BEGIN
3554
3555 SAVEPOINT COMPLETE_CANCEL_TENDER_PUB;
3556
3557 IF l_debug_on THEN
3558 WSH_DEBUG_SV.push(l_module_name);
3559 END IF;
3560
3561 UPDATE wsh_trip_stops
3562 SET CARRIER_EST_DEPARTURE_DATE = null
3563 where stop_id in
3564 (
3565 select stop_id from wsh_trip_stops
3566 where trip_id = p_tender_id and
3567 PLANNED_DEPARTURE_DATE = (
3568 select min(PLANNED_DEPARTURE_DATE) from wsh_trip_stops
3569 where trip_id = p_tender_id
3570 )
3571 );
3572
3573
3574 UPDATE wsh_trip_stops
3575 SET CARRIER_EST_ARRIVAL_DATE = null
3576 where stop_id in
3577 (
3578 select stop_id from wsh_trip_stops
3579 where trip_id = p_tender_id and
3580 PLANNED_ARRIVAL_DATE = (
3581 select max(PLANNED_ARRIVAL_DATE) from wsh_trip_stops
3582 where trip_id = p_tender_id
3583 )
3584 );
3585
3586
3587 OPEN check_exception_on_trip(p_tender_id);
3588 LOOP
3589 FETCH check_exception_on_trip into l_exception_id,l_status;
3590 EXIT WHEN check_exception_on_trip%NOTFOUND;
3591
3592 l_return_status := NULL;
3593 l_new_status := 'CLOSED';
3594 l_msg_count := NULL;
3595 l_msg_data := NULL;
3596
3597 WSH_XC_UTIL.change_status (
3598 p_api_version => 1.0,
3599 p_init_msg_list => FND_API.g_false,
3600 p_commit => FND_API.g_false,
3601 p_validation_level => l_validation_level,
3602 x_return_status => l_return_status,
3603 x_msg_count => l_msg_count,
3604 x_msg_data => l_msg_data,
3605 p_exception_id => l_exception_id,
3606 p_old_status => l_status,
3607 p_set_default_status => FND_API.G_FALSE,
3608 x_new_status => l_new_status
3609 );
3610
3611 END LOOP;
3612 CLOSE check_exception_on_trip;
3613
3614 --
3615 FND_MSG_PUB.Count_And_Get
3616 (
3617 p_count => x_msg_count,
3618 p_data => x_msg_data,
3619 p_encoded => FND_API.G_FALSE
3620 );
3621 wsh_util_core.api_post_call(
3622 p_return_status =>l_return_status,
3623 x_num_warnings =>l_number_of_warnings,
3624 x_num_errors =>l_number_of_errors,
3625 p_msg_data =>x_msg_data);
3626
3627 IF l_number_of_errors > 0
3628 THEN
3629 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3630 RAISE FND_API.G_EXC_ERROR;
3631 ELSIF l_number_of_warnings > 0
3632 THEN
3633 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
3634 ELSE
3635 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
3636 END IF;
3637
3638
3639 IF l_debug_on THEN
3640 WSH_DEBUG_SV.pop(l_module_name);
3641 END IF;
3642
3643
3644 EXCEPTION
3645
3646 WHEN FND_API.G_EXC_ERROR THEN
3647 ROLLBACK TO COMPLETE_CANCEL_TENDER_PUB;
3648 x_return_status := FND_API.G_RET_STS_ERROR ;
3649 FND_MSG_PUB.Count_And_Get
3650 (
3651 p_count => x_msg_count,
3652 p_data => x_msg_data,
3653 p_encoded => FND_API.G_FALSE
3654 );
3655
3656 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3657 ROLLBACK TO COMPLETE_CANCEL_TENDER_PUB;
3658 x_return_status := FND_API.G_RET_STS_ERROR ;
3659 FND_MSG_PUB.Count_And_Get
3660 (
3661 p_count => x_msg_count,
3662 p_data => x_msg_data,
3663 p_encoded => FND_API.G_FALSE
3664 );
3665
3666 WHEN OTHERS THEN
3667 ROLLBACK TO COMPLETE_CANCEL_TENDER_PUB;
3668 wsh_util_core.default_handler('FTE_TENDER_PVT.COMPLETE_CANCEL_TENDER');
3669 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
3670 FND_MSG_PUB.Count_And_Get
3671 (
3672 p_count => x_msg_count,
3673 p_data => x_msg_data,
3674 p_encoded => FND_API.G_FALSE
3675 );
3676
3677 END COMPLETE_CANCEL_TENDER;
3678
3679
3680
3681 -- For Rel 12 HBHAGAVA
3682
3683
3684 PROCEDURE RAISE_TENDER_EVENT(
3685 p_init_msg_list IN VARCHAR2,
3686 x_return_status OUT NOCOPY VARCHAR2,
3687 x_msg_count OUT NOCOPY NUMBER,
3688 x_msg_data OUT NOCOPY VARCHAR2,
3689 p_trip_info IN FTE_TENDER_ATTR_REC,
3690 p_mbol_number IN VARCHAR2)
3691 IS
3692 --{
3693
3694
3695 --{ Local variables
3696
3697 l_parameter_list wf_parameter_list_t;
3698
3699 l_api_name CONSTANT VARCHAR2(30) := 'RAISE_TENDER_EVENT';
3700 l_api_version CONSTANT NUMBER := 1.0;
3701 l_debug_on CONSTANT BOOLEAN := WSH_DEBUG_SV.is_debug_enabled;
3702 l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.' || G_PKG_NAME || '.' || l_api_name;
3703
3704
3705 l_return_status VARCHAR2(32767);
3706 l_msg_count NUMBER;
3707 l_msg_data VARCHAR2(32767);
3708 l_number_of_warnings NUMBER;
3709 l_number_of_errors NUMBER;
3710
3711 l_role_name VARCHAR2(32767);
3712
3713
3714 --}
3715
3716 BEGIN
3717
3718
3719 SAVEPOINT RAISE_TENDER_EVENT_PUB;
3720 IF l_debug_on THEN
3721 WSH_DEBUG_SV.push(l_module_name);
3722 END IF;
3723
3724 IF FND_API.to_Boolean( p_init_msg_list )
3725 THEN
3726 FND_MSG_PUB.initialize;
3727 END IF;
3728
3729 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
3730 x_msg_count := 0;
3731 x_msg_data := 0;
3732 l_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
3733 l_number_of_warnings := 0;
3734 l_number_of_errors := 0;
3735
3736
3737 HANDLE_WF_ROLES(
3738 p_init_msg_list => FND_API.G_FALSE,
3739 x_return_status => l_return_status,
3740 x_msg_count => l_msg_count,
3741 x_msg_data => l_msg_data,
3742 x_role_name => l_role_name,
3743 p_contact_name => 'HZ_PARTY:' || p_trip_info.car_contact_id);
3744
3745 wsh_util_core.api_post_call(
3746 p_return_status =>l_return_status,
3747 x_num_warnings =>l_number_of_warnings,
3748 x_num_errors =>l_number_of_errors,
3749 p_msg_data =>l_msg_data);
3750
3751
3752 -- Take a snapshot
3753 TAKE_TENDER_SNAPSHOT(
3754 p_init_msg_list => FND_API.G_FALSE,
3755 p_trip_id => p_trip_info.trip_id,
3756 p_action => 'REPLACE',
3757 x_return_status => l_return_status,
3758 x_msg_count => l_msg_count,
3759 x_msg_data => l_msg_data);
3760
3761 wsh_util_core.api_post_call(
3762 p_return_status =>l_return_status,
3763 x_num_warnings =>l_number_of_warnings,
3764 x_num_errors =>l_number_of_errors,
3765 p_msg_data =>l_msg_data);
3766
3767 IF l_number_of_errors > 0
3768 THEN
3769 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3770 RAISE FND_API.G_EXC_ERROR;
3771 ELSIF l_number_of_warnings > 0
3772 THEN
3773 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
3774 ELSE
3775 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
3776 END IF;
3777
3778 IF l_debug_on THEN
3779 WSH_DEBUG_SV.logmsg(l_module_name,' TRIP Id ' || p_trip_info.trip_id,
3780 WSH_DEBUG_SV.C_PROC_LEVEL);
3781 END IF;
3782
3783
3784
3785 wf_event.AddParameterToList(p_name=>'TRIP_ID',
3786 p_value=> p_trip_info.trip_id,
3787 p_parameterlist=>l_parameter_list);
3788
3789
3790 wf_event.AddParameterToList(p_name=>'TENDER_ID',
3791 p_value=> p_trip_info.tender_id,
3792 p_parameterlist=>l_parameter_list);
3793
3794
3795 wf_event.AddParameterToList(p_name=>'MBOL_NUM',
3796 p_value=> p_mbol_number,
3797 p_parameterlist=>l_parameter_list);
3798
3799 wf_event.AddParameterToList(p_name=>'SHIPPER_NAME',
3800 p_value=> FND_GLOBAL.USER_NAME,
3801 p_parameterlist=>l_parameter_list);
3802
3803 wf_event.AddParameterToList(p_name=> 'SHIPPER_USER_ID',
3804 p_value=> FND_GLOBAL.USER_ID,
3805 p_parameterlist=>l_parameter_list);
3806
3807 wf_event.AddParameterToList(p_name=> 'USER_ID',
3808 p_value=> FND_GLOBAL.USER_ID,
3809 p_parameterlist=>l_parameter_list);
3810
3811 wf_event.AddParameterToList(p_name=> 'RESPONSIBILITY_ID',
3812 p_value=> FND_GLOBAL.RESP_ID,
3813 p_parameterlist=>l_parameter_list);
3814
3815 wf_event.AddParameterToList(p_name=> 'RESP_APPL_ID',
3816 p_value=> FND_GLOBAL.RESP_APPL_ID,
3817 p_parameterlist=>l_parameter_list);
3818
3819
3820 wf_event.AddParameterToList(p_name=> 'SHIPPER_RESP_ID',
3821 p_value=> FND_GLOBAL.RESP_ID,
3822 p_parameterlist=>l_parameter_list);
3823
3824
3825 wf_event.AddParameterToList(p_name => 'CONTACT_PERFORMER',
3826 p_value => l_role_name,
3827 p_parameterlist => l_parameter_list);
3828
3829
3830
3831 IF l_debug_on THEN
3832 WSH_DEBUG_SV.logmsg(l_module_name,' Before raising oracle.apps.fte.lt.tenderrequest ',
3833 WSH_DEBUG_SV.C_PROC_LEVEL);
3834 WSH_DEBUG_SV.logmsg(l_module_name,' Event Key ' || p_trip_info.wf_item_key,
3835 WSH_DEBUG_SV.C_PROC_LEVEL);
3836 END IF;
3837
3838
3839 wf_event.raise(
3840 p_event_name => 'oracle.apps.fte.lt.tenderrequest',
3841 p_event_key => p_trip_info.wf_item_key,
3842 p_parameters => l_parameter_list
3843 );
3844
3845
3846
3847 l_parameter_list.DELETE;
3848
3849 -- Standard call to get message count and if count is 1,get message info.
3850 --
3851 FND_MSG_PUB.Count_And_Get
3852 (
3853 p_count => x_msg_count,
3854 p_data => x_msg_data,
3855 p_encoded => FND_API.G_FALSE
3856 );
3857
3858
3859 --
3860 --
3861
3862 IF l_debug_on THEN
3863 WSH_DEBUG_SV.pop(l_module_name);
3864 END IF;
3865
3866 --}
3867 EXCEPTION
3868 --{
3869 WHEN FND_API.G_EXC_ERROR THEN
3870 ROLLBACK TO RAISE_TENDER_EVENT_PUB;
3871 x_return_status := FND_API.G_RET_STS_ERROR ;
3872 FND_MSG_PUB.Count_And_Get
3873 (
3874 p_count => x_msg_count,
3875 p_data => x_msg_data,
3876 p_encoded => FND_API.G_FALSE
3877 );
3878 IF l_debug_on THEN
3879 WSH_DEBUG_SV.pop(l_module_name);
3880 END IF;
3881
3882
3883 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3884 ROLLBACK TO RAISE_TENDER_EVENT_PUB;
3885 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3886 FND_MSG_PUB.Count_And_Get
3887 (
3888 p_count => x_msg_count,
3889 p_data => x_msg_data,
3890 p_encoded => FND_API.G_FALSE
3891 );
3892 IF l_debug_on THEN
3893 WSH_DEBUG_SV.pop(l_module_name);
3894 END IF;
3895
3896
3897 WHEN OTHERS THEN
3898 ROLLBACK TO RAISE_TENDER_EVENT_PUB;
3899 wsh_util_core.default_handler('FTE_TENDER_PVT.RAISE_TENDER_EVENT');
3900 x_return_status := FND_API.G_RET_STS_ERROR;
3901 FND_MSG_PUB.Count_And_Get
3902 (
3903 p_count => x_msg_count,
3904 p_data => x_msg_data,
3905 p_encoded => FND_API.G_FALSE
3906 );
3907 IF l_debug_on THEN
3908 WSH_DEBUG_SV.pop(l_module_name);
3909 END IF;
3910
3911
3912 --}
3913
3914 END RAISE_TENDER_EVENT;
3915
3916 --}
3917
3918 -- For Rel 12 HBHAGAVA
3919
3920
3921 --}
3922
3923 -- ------------------------------------------------------------------------------- --
3924 -- --
3925 -- NAME: UPDATE_CARRIER_RESPONSE --
3926 -- TYPE: PROCEDURE --
3927 -- PARAMETERS (IN): p_tender_id NUMBER --
3928 -- p_remarks VARCHAR2 --
3929 -- p_initial_pickup_date DATE --
3930 -- p_ultimate_dropoff_date DATE --
3931 -- --
3932 -- PARAMETERS (OUT): --
3933 -- x_return_status VARCHAR2 --
3934 -- x_msg_count NUMBER --
3935 -- x_msg_data VARCHAR2 --
3936 -- PARAMETERS (IN OUT): none --
3937 -- RETURN: none --
3938 -- DESCRIPTION: This procedure Update the Trip/Stops with Carrier Responses --
3939 -- --
3940 -- CHANGE CONTROL LOG --
3941 -- ------------------ --
3942 -- --
3943 -- DATE VERSION BY BUG DESCRIPTION --
3944 -- ---------- ------- -------- ------- --------------------------------------- --
3945 -- 2003 11.5.9 SAMUTHUK Created --
3946 -- 2005 SHRAVISA Updated --
3947 -- ------------------------------------------------------------------------------- --
3948
3949 PROCEDURE UPDATE_CARRIER_RESPONSE(
3950 p_init_msg_list IN VARCHAR2 ,
3951 p_carrier_response_rec IN FTE_TENDER_ATTR_REC,
3952 x_return_status OUT NOCOPY VARCHAR2,
3953 x_msg_count OUT NOCOPY NUMBER,
3954 x_msg_data OUT NOCOPY VARCHAR2) IS
3955
3956 l_api_name VARCHAR2(30) := 'UPDATE_CARRIER_RESPONSE';
3957 l_api_version CONSTANT NUMBER := 1.0;
3958 l_debug_on CONSTANT BOOLEAN := WSH_DEBUG_SV.is_debug_enabled;
3959 l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.' || G_PKG_NAME || '.' || 'UPDATE_CARRIER_RESPONSE';
3960
3961 l_trip_id NUMBER;
3962 l_trip_name VARCHAR2(30000);
3963
3964 l_return_status VARCHAR2(30000);
3965 l_msg_count NUMBER;
3966 l_msg_data VARCHAR2(30000);
3967 l_number_of_warnings NUMBER;
3968 l_number_of_errors NUMBER;
3969
3970 --{Trip update parameters
3971 p_trip_info_tab WSH_TRIPS_PVT.Trip_Attr_Tbl_Type;
3972 p_trip_info WSH_TRIPS_PVT.Trip_Rec_Type;
3973 p_trip_in_rec WSH_TRIPS_GRP.TripInRecType;
3974 x_out_tab WSH_TRIPS_GRP.trip_Out_tab_type;
3975 --}
3976
3977
3978 BEGIN
3979
3980 -- p_call_source to be used by harish appropriately to
3981 -- to release the XML Block.
3982 -- 11i11 Code
3983
3984 SAVEPOINT UPDATE_CARRIER_RESPONSE_PUB;
3985
3986 IF FND_API.to_Boolean( p_init_msg_list )
3987 THEN
3988 FND_MSG_PUB.initialize;
3989 END IF;
3990 --
3991 IF l_debug_on THEN
3992 wsh_debug_sv.push(l_module_name);
3993 END IF;
3994
3995 --
3996 -- Initialize API return status to success
3997 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
3998 x_msg_count := 0;
3999 x_msg_data := 0;
4000 l_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
4001 l_number_of_warnings := 0;
4002 l_number_of_errors := 0;
4003
4004 IF p_carrier_response_rec.tender_status = S_ACCEPTED
4005 THEN
4006
4007 UPDATE wsh_trip_stops
4008 SET CARRIER_EST_DEPARTURE_DATE =
4009 p_carrier_response_rec.carrier_pickup_date
4010 where stop_id in
4011 (
4012 select stop_id from wsh_trip_stops
4013 where trip_id = p_carrier_response_rec.trip_id and
4014 PLANNED_DEPARTURE_DATE = (
4015 select min(PLANNED_DEPARTURE_DATE) from wsh_trip_stops
4016 where trip_id = p_carrier_response_rec.trip_id
4017 )
4018 );
4019
4020 UPDATE wsh_trip_stops
4021 SET CARRIER_EST_ARRIVAL_DATE =
4022 p_carrier_response_rec.carrier_dropoff_date
4023 where stop_id in
4024 (
4025 select stop_id from wsh_trip_stops
4026 where trip_id = p_carrier_response_rec.trip_id and
4027 PLANNED_ARRIVAL_DATE = (
4028 select max(PLANNED_ARRIVAL_DATE) from wsh_trip_stops
4029 where trip_id = p_carrier_response_rec.trip_id
4030 )
4031 );
4032
4033 --Added for Rel 12 Shravisa
4034 -- Check carrier arrival time.
4035 IF l_debug_on THEN
4036 WSH_DEBUG_SV.logmsg(l_module_name,' Abt to call CHECK CARRIER ARRIVAL TIME PROCEDURE ',
4037 WSH_DEBUG_SV.C_PROC_LEVEL);
4038 END IF ;
4039
4040
4041 CHECK_CARRIER_ARRIVAL_TIME(
4042 p_tender_id => p_carrier_response_rec.tender_id,
4043 x_return_status => x_return_status,
4044 x_msg_count => x_msg_count ,
4045 x_msg_data => x_msg_data
4046 );
4047
4048 IF l_debug_on THEN
4049 WSH_DEBUG_SV.logmsg(l_module_name,' Exited out of CHECK CARRIER ARRIVAL TIME PROCEDURE '||x_return_status ,
4050 WSH_DEBUG_SV.C_PROC_LEVEL);
4051 END IF ;
4052
4053 FTE_MLS_WRAPPER.INITIALIZE_TRIP_REC(x_trip_info => p_trip_info);
4054
4055 -- Update trip information
4056 p_trip_info.TRIP_ID := p_carrier_response_rec.trip_id;
4057 p_trip_info.wf_name := p_carrier_response_rec.WF_NAME;
4058 p_trip_info.wf_process_name := p_carrier_response_rec.wf_process_name;
4059 p_trip_info.carrier_Response := p_carrier_response_rec.remarks;
4060 p_trip_info.operator := p_carrier_response_Rec.operator;
4061 p_trip_info.vehicle_number := p_carrier_response_rec.VEHICLE_NUMBER;
4062 p_trip_info.load_tender_status := FTE_TENDER_PVT.S_ACCEPTED;
4063 p_trip_info.carrier_reference_number := p_carrier_response_rec.carrier_ref_number;
4064
4065 p_trip_info_tab(1) :=p_trip_info;
4066 p_trip_in_rec.caller :='FTE_MLS_WRAPPER';
4067 p_trip_in_rec.phase :=NULL;
4068 p_trip_in_rec.action_code :='UPDATE';
4069
4070 WSH_INTERFACE_GRP.Create_Update_Trip
4071 (
4072 p_api_version_number =>1.0,
4073 p_init_msg_list =>FND_API.G_FALSE,
4074 p_commit =>FND_API.G_FALSE,
4075 x_return_status =>l_return_status,
4076 x_msg_count =>l_msg_count,
4077 x_msg_data =>l_msg_data,
4078 p_trip_info_tab =>p_trip_info_tab,
4079 p_in_rec =>p_trip_in_rec,
4080 x_out_tab =>x_out_tab
4081 );
4082
4083
4084 IF l_debug_on
4085 THEN
4086 WSH_DEBUG_SV.logmsg(l_module_name,' Return Status aftere calling WSH_INTERFACE_GRP.CREATE_UPDATE_TRIP ' ||
4087 l_return_status,
4088 WSH_DEBUG_SV.C_PROC_LEVEL);
4089 END IF;
4090
4091 wsh_util_core.api_post_call(
4092 p_return_status =>l_return_status,
4093 x_num_warnings =>l_number_of_warnings,
4094 x_num_errors =>l_number_of_errors,
4095 p_msg_data =>l_msg_data);
4096
4097 --End of Rel 12 Shravisa
4098
4099 ELSIF p_carrier_response_rec.tender_status = S_REJECTED THEN
4100
4101 --- Code to Be added by harish for Reject Status
4102 -- 11i11 Code
4103 -- Update trip informatio
4104
4105 FTE_MLS_WRAPPER.INITIALIZE_TRIP_REC(x_trip_info => p_trip_info);
4106
4107 p_trip_info.TRIP_ID := p_carrier_response_rec.trip_id;
4108 p_trip_info.wf_name := p_carrier_response_rec.WF_NAME;
4109 p_trip_info.wf_process_name := p_carrier_response_rec.wf_process_name;
4110 p_trip_info.carrier_Response := p_carrier_response_rec.remarks;
4111 p_trip_info.load_tender_status := FTE_TENDER_PVT.S_REJECTED;
4112
4113
4114 p_trip_info_tab(1) :=p_trip_info;
4115 p_trip_in_rec.caller :='FTE_MLS_WRAPPER';
4116 p_trip_in_rec.phase :=NULL;
4117 p_trip_in_rec.action_code :='UPDATE';
4118
4119 WSH_INTERFACE_GRP.Create_Update_Trip
4120 (
4121 p_api_version_number =>1.0,
4122 p_init_msg_list =>FND_API.G_FALSE,
4123 p_commit =>FND_API.G_FALSE,
4124 x_return_status =>l_return_status,
4125 x_msg_count =>l_msg_count,
4126 x_msg_data =>l_msg_data,
4127 p_trip_info_tab =>p_trip_info_tab,
4128 p_in_rec =>p_trip_in_rec,
4129 x_out_tab =>x_out_tab
4130 );
4131
4132
4133 IF l_debug_on
4134 THEN
4135 WSH_DEBUG_SV.logmsg(l_module_name,' Return Status aftere calling WSH_INTERFACE_GRP.CREATE_UPDATE_TRIP ' ||
4136 l_return_status,
4137 WSH_DEBUG_SV.C_PROC_LEVEL);
4138 END IF;
4139
4140 wsh_util_core.api_post_call(
4141 p_return_status =>l_return_status,
4142 x_num_warnings =>l_number_of_warnings,
4143 x_num_errors =>l_number_of_errors,
4144 p_msg_data =>l_msg_data);
4145
4146
4147 END IF;
4148
4149 IF l_number_of_errors > 0
4150 THEN
4151 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
4152 RAISE FND_API.G_EXC_ERROR;
4153 ELSIF l_number_of_warnings > 0
4154 THEN
4155 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
4156 ELSE
4157 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
4158 END IF;
4159
4160
4161 -- Standard call to get message count and if count is 1,get message info.
4162 --
4163 FND_MSG_PUB.Count_And_Get
4164 (
4165 p_count => x_msg_count,
4166 p_data => x_msg_data,
4167 p_encoded => FND_API.G_FALSE
4168 );
4169 --
4170 --
4171
4172 wsh_util_core.api_post_call(
4173 p_return_status =>l_return_status,
4174 x_num_warnings =>l_number_of_warnings,
4175 x_num_errors =>l_number_of_errors,
4176 p_msg_data =>l_msg_data);
4177
4178 IF l_number_of_errors > 0
4179 THEN
4180 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
4181 RAISE FND_API.G_EXC_ERROR;
4182 ELSIF l_number_of_warnings > 0
4183 THEN
4184 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
4185 ELSE
4186 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
4187 END IF;
4188
4189
4190
4191 IF l_debug_on THEN
4192 WSH_DEBUG_SV.pop(l_module_name);
4193 END IF;
4194
4195 EXCEPTION
4196
4197 WHEN FND_API.G_EXC_ERROR THEN
4198 ROLLBACK TO UPDATE_CARRIER_RESPONSE_PUB;
4199 x_return_status := FND_API.G_RET_STS_ERROR ;
4200 FND_MSG_PUB.Count_And_Get
4201 (
4202 p_count => x_msg_count,
4203 p_data => x_msg_data,
4204 p_encoded => FND_API.G_FALSE
4205 );
4206 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4207 ROLLBACK TO UPDATE_CARRIER_RESPONSE_PUB;
4208 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4209 FND_MSG_PUB.Count_And_Get
4210 (
4211 p_count => x_msg_count,
4212 p_data => x_msg_data,
4213 p_encoded => FND_API.G_FALSE
4214 );
4215
4216 WHEN OTHERS THEN
4217 ROLLBACK TO UPDATE_CARRIER_RESPONSE_PUB;
4218 wsh_util_core.default_handler('FTE_TENDER_PVT.UPDATE_CARRIER_RESPONSE');
4219 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
4220 FND_MSG_PUB.Count_And_Get
4221 (
4222 p_count => x_msg_count,
4223 p_data => x_msg_data,
4224 p_encoded => FND_API.G_FALSE
4225 );
4226
4227
4228 END UPDATE_CARRIER_RESPONSE;
4229
4230
4231 --}
4232
4233 --{ Rel 12 HBHAGAVA
4234
4235 PROCEDURE RELEASE_TENDER_BLOCK(
4236 p_init_msg_list IN VARCHAR2,
4237 x_return_status OUT NOCOPY VARCHAR2,
4238 x_msg_count OUT NOCOPY NUMBER,
4239 x_msg_data OUT NOCOPY VARCHAR2,
4240 p_trip_info IN FTE_TENDER_ATTR_REC)
4241 IS
4242 --{
4243
4244
4245 --{ Local variables
4246
4247 l_parameter_list wf_parameter_list_t;
4248
4249 l_api_name CONSTANT VARCHAR2(30) := 'RELEASE_TENDER_BLOCK';
4250 l_api_version CONSTANT NUMBER := 1.0;
4251 l_debug_on CONSTANT BOOLEAN := WSH_DEBUG_SV.is_debug_enabled;
4252 l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.' || G_PKG_NAME || '.' || l_api_name;
4253
4254
4255 l_return_status VARCHAR2(32767);
4256 l_msg_count NUMBER;
4257 l_msg_data VARCHAR2(32767);
4258 l_number_of_warnings NUMBER;
4259 l_number_of_errors NUMBER;
4260
4261 l_notif_type VARCHAR2(10);
4262 l_result_code VARCHAR2(32767);
4263
4264 --}
4265
4266 BEGIN
4267
4268
4269 SAVEPOINT RELEASE_TENDER_BLOCK_PUB;
4270 IF l_debug_on THEN
4271 WSH_DEBUG_SV.push(l_module_name);
4272 END IF;
4273
4274 IF FND_API.to_Boolean( p_init_msg_list )
4275 THEN
4276 FND_MSG_PUB.initialize;
4277 END IF;
4278
4279 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
4280 x_msg_count := 0;
4281 x_msg_data := 0;
4282 l_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
4283 l_number_of_warnings := 0;
4284 l_number_of_errors := 0;
4285
4286
4287 l_notif_type := wf_engine.GetItemAttrText('FTETEREQ',
4288 p_trip_info.wf_item_key, 'NOTIF_TYPE');
4289
4290 -- Set response source
4291
4292
4293 wf_engine.SetItemAttrText('FTETEREQ',p_trip_info.wf_item_key,
4294 'RESPONSE_SOURCE',p_trip_info.response_source);
4295
4296 IF (l_notif_type = 'XML') THEN
4297 --{ release tender block
4298 FTE_WF_UTIL.GET_BLOCK_STATUS(
4299 itemtype => 'FTETEREQ',
4300 itemkey => p_trip_info.wf_item_key,
4301 p_workflow_process => 'TENDER_REQUEST_PROCESS',
4302 p_block_label => 'TENDER_REQUEST_BLOCK',
4303 x_return_status => l_result_code);
4304
4305 IF (l_result_code = G_TENDER_NOTIFIED) THEN
4306
4307 wf_engine.CompleteActivity(
4308 itemtype => 'FTETEREQ',
4309 itemkey => p_trip_info.wf_item_key,
4310 activity => 'TENDER_REQUEST_PROCESS:TENDER_REQUEST_BLOCK',
4311 result => 'null');
4312
4313 END IF;
4314 --}
4315 ELSIF (l_notif_type = 'EMAIL') THEN
4316 --{
4317 FTE_WF_UTIL.GET_BLOCK_STATUS(
4318 itemtype => 'FTETEREQ',
4319 itemkey => p_trip_info.wf_item_key,
4320 p_workflow_process => 'TENDER_REQUEST_PROCESS',
4321 p_block_label => 'TENDER_REQUEST_NTF',
4322 x_return_status => l_result_code);
4323
4324
4325 IF (l_result_code = G_TENDER_NOTIFIED) THEN
4326
4327 IF (p_trip_info.tender_status = FTE_TENDER_PVT.S_ACCEPTED) THEN
4328 wf_engine.CompleteActivity(
4329 itemtype => 'FTETEREQ',
4330 itemkey => p_trip_info.wf_item_key,
4331 activity => 'TENDER_REQUEST_PROCESS:TENDER_REQUEST_NTF',
4332 result => G_TENDER_APPROVED);
4333 ELSIF (p_trip_info.tender_status = FTE_TENDER_PVT.S_REJECTED) THEN
4334 wf_engine.CompleteActivity(
4335 itemtype => 'FTETEREQ',
4336 itemkey => p_trip_info.wf_item_key,
4337 activity => 'TENDER_REQUEST_PROCESS:TENDER_REQUEST_NTF',
4338 result => G_TENDER_REJECTED);
4339 ELSE
4340 -- This is for cancel scenario
4341 wf_engine.CompleteActivity(
4342 itemtype => 'FTETEREQ',
4343 itemkey => p_trip_info.wf_item_key,
4344 activity => 'TENDER_REQUEST_PROCESS:TENDER_REQUEST_NTF',
4345 result => G_TENDER_ABORT);
4346 END IF;
4347
4348 END IF;
4349 --}
4350 END IF;
4351
4352
4353
4354 -- Standard call to get message count and if count is 1,get message info.
4355 --
4356 FND_MSG_PUB.Count_And_Get
4357 (
4358 p_count => x_msg_count,
4359 p_data => x_msg_data,
4360 p_encoded => FND_API.G_FALSE
4361 );
4362
4363
4364 --
4365 --
4366
4367 IF l_debug_on THEN
4368 WSH_DEBUG_SV.pop(l_module_name);
4369 END IF;
4370
4371 --}
4372 EXCEPTION
4373 --{
4374 WHEN FND_API.G_EXC_ERROR THEN
4375 ROLLBACK TO RELEASE_TENDER_BLOCK_PUB;
4376 x_return_status := FND_API.G_RET_STS_ERROR ;
4377 FND_MSG_PUB.Count_And_Get
4378 (
4379 p_count => x_msg_count,
4380 p_data => x_msg_data,
4381 p_encoded => FND_API.G_FALSE
4382 );
4383 IF l_debug_on THEN
4384 WSH_DEBUG_SV.pop(l_module_name);
4385 END IF;
4386
4387
4388 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4389 ROLLBACK TO RELEASE_TENDER_BLOCK_PUB;
4390 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4391 FND_MSG_PUB.Count_And_Get
4392 (
4393 p_count => x_msg_count,
4394 p_data => x_msg_data,
4395 p_encoded => FND_API.G_FALSE
4396 );
4397 IF l_debug_on THEN
4398 WSH_DEBUG_SV.pop(l_module_name);
4399 END IF;
4400
4401
4402 WHEN OTHERS THEN
4403 ROLLBACK TO RELEASE_TENDER_BLOCK_PUB;
4404 wsh_util_core.default_handler('FTE_TENDER_PVT.RELEASE_TENDER_BLOCK');
4405 x_return_status := FND_API.G_RET_STS_ERROR;
4406 FND_MSG_PUB.Count_And_Get
4407 (
4408 p_count => x_msg_count,
4409 p_data => x_msg_data,
4410 p_encoded => FND_API.G_FALSE
4411 );
4412 IF l_debug_on THEN
4413 WSH_DEBUG_SV.pop(l_module_name);
4414 END IF;
4415
4416
4417 --}
4418
4419 END RELEASE_TENDER_BLOCK;
4420
4421
4422 -- For Rel 12 HBHAGAVA
4423
4424
4425 PROCEDURE HANDLE_TENDER_RESPONSE(
4426 p_init_msg_list IN VARCHAR2,
4427 x_return_status OUT NOCOPY VARCHAR2,
4428 x_msg_count OUT NOCOPY NUMBER,
4429 x_msg_data OUT NOCOPY VARCHAR2,
4430 p_trip_info IN FTE_TENDER_ATTR_REC)
4431 IS
4432 --{
4433
4434
4435 --{ Local variables
4436
4437 l_parameter_list wf_parameter_list_t;
4438
4439 l_api_name CONSTANT VARCHAR2(30) := 'HANDLE_TENDER_RESPONSE';
4440 l_api_version CONSTANT NUMBER := 1.0;
4441 l_debug_on CONSTANT BOOLEAN := WSH_DEBUG_SV.is_debug_enabled;
4442 l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.' || G_PKG_NAME || '.' || l_api_name;
4443
4444
4445 l_return_status VARCHAR2(32767);
4446 l_msg_count NUMBER;
4447 l_msg_data VARCHAR2(32767);
4448 l_number_of_warnings NUMBER;
4449 l_number_of_errors NUMBER;
4450 l_mode_of_transport VARCHAR2(80);
4451
4452 l_role_name VARCHAR2(32767);
4453
4454 --}
4455
4456 BEGIN
4457
4458
4459 SAVEPOINT HANDLE_TENDER_RESPONSE_PUB;
4460 IF l_debug_on THEN
4461 WSH_DEBUG_SV.push(l_module_name);
4462 END IF;
4463
4464 IF FND_API.to_Boolean( p_init_msg_list )
4465 THEN
4466 FND_MSG_PUB.initialize;
4467 END IF;
4468
4469 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
4470 x_msg_count := 0;
4471 x_msg_data := 0;
4472 l_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
4473 l_number_of_warnings := 0;
4474 l_number_of_errors := 0;
4475
4476
4477 UPDATE_CARRIER_RESPONSE(
4478 p_init_msg_list => FND_API.G_FALSE,
4479 p_carrier_response_rec => p_trip_info,
4480 x_return_status => l_return_status,
4481 x_msg_count => l_msg_count,
4482 x_msg_data => l_msg_data);
4483
4484
4485 IF l_debug_on
4486 THEN
4487 WSH_DEBUG_SV.logmsg(l_module_name,' Return Status aftere calling CREATE_UPDATE_TRIP ' ||
4488 l_return_status,
4489 WSH_DEBUG_SV.C_PROC_LEVEL);
4490 END IF;
4491
4492 wsh_util_core.api_post_call(
4493 p_return_status =>l_return_status,
4494 x_num_warnings =>l_number_of_warnings,
4495 x_num_errors =>l_number_of_errors,
4496 p_msg_data =>l_msg_data);
4497
4498 IF l_number_of_errors > 0
4499 THEN
4500 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
4501 RAISE FND_API.G_EXC_ERROR;
4502 ELSIF l_number_of_warnings > 0
4503 THEN
4504 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
4505 ELSE
4506 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
4507 END IF;
4508
4509
4510 -- If source is S_SOURCE_CP or S_SOURCE_XML we have to release the block
4511 -- So call Release block API
4512 IF (p_trip_info.response_source = FTE_TENDER_PVT.S_SOURCE_CP OR
4513 p_trip_info.response_source = FTE_TENDER_PVT.S_SOURCE_XML)
4514 THEN
4515
4516 RELEASE_TENDER_BLOCK(
4517 p_init_msg_list => FND_API.G_FALSE,
4518 p_trip_info => p_trip_info,
4519 x_return_status => l_return_status,
4520 x_msg_count => l_msg_count,
4521 x_msg_data => l_msg_data);
4522 END IF;
4523
4524 wsh_util_core.api_post_call(
4525 p_return_status =>l_return_status,
4526 x_num_warnings =>l_number_of_warnings,
4527 x_num_errors =>l_number_of_errors,
4528 p_msg_data =>l_msg_data);
4529
4530 IF l_number_of_errors > 0
4531 THEN
4532 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
4533 RAISE FND_API.G_EXC_ERROR;
4534 ELSIF l_number_of_warnings > 0
4535 THEN
4536 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
4537 ELSE
4538 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
4539 END IF;
4540
4541
4542 wf_event.AddParameterToList(p_name => 'TENDER_ACTION',
4543 p_value => p_trip_info.tender_status,
4544 p_parameterlist => l_parameter_list);
4545
4546 IF l_debug_on THEN
4547 WSH_DEBUG_SV.logmsg(l_module_name,' Setting load tender status ' ||
4548 p_trip_info.tender_status,
4549 WSH_DEBUG_SV.C_PROC_LEVEL);
4550 END IF;
4551
4552
4553 wf_event.AddParameterToList(p_name => 'TENDER_STATUS',
4554 p_value => p_trip_info.tender_status,
4555 p_parameterlist => l_parameter_list);
4556
4557 wf_event.AddParameterToList(p_name => 'TENDER_ID',
4558 p_value => p_trip_info.tender_id,
4559 p_parameterlist => l_parameter_list);
4560
4561 wf_event.AddParameterToList(p_name => 'TRIP_ID',
4562 p_value => p_trip_info.trip_id,
4563 p_parameterlist => l_parameter_list);
4564
4565 wf_event.AddParameterToList(p_name => 'CONTACT_USER_NAME',
4566 p_value => FND_GLOBAL.USER_NAME,
4567 p_parameterlist => l_parameter_list);
4568
4569 wf_event.AddParameterToList(p_name => 'CONTACT_USER_ID',
4570 p_value => FND_GLOBAL.USER_ID,
4571 p_parameterlist => l_parameter_list);
4572
4573
4574 wf_event.AddParameterToList(p_name => 'CARRIER_REMARKS',
4575 p_value => p_trip_info.remarks,
4576 p_parameterlist => l_parameter_list);
4577
4578
4579 wf_event.AddParameterToList(p_name=> 'USER_ID',
4580 p_value=> wf_engine.GetItemAttrNumber('FTETEREQ',
4581 p_trip_info.wf_item_key,'USER_ID'),
4582 p_parameterlist=>l_parameter_list);
4583
4584
4585 wf_event.AddParameterToList(p_name=> 'RESPONSIBILITY_ID',
4586 p_value=> wf_engine.GetItemAttrNumber('FTETEREQ',
4587 p_trip_info.wf_item_key,'RESPONSIBILITY_ID'),
4588 p_parameterlist=>l_parameter_list);
4589
4590 wf_event.AddParameterToList(p_name=> 'RESP_APPL_ID',
4591 p_value=> wf_engine.GetItemAttrNumber('FTETEREQ',
4592 p_trip_info.wf_item_key,'RESP_APPL_ID'),
4593 p_parameterlist=>l_parameter_list);
4594
4595
4596 IF (p_trip_info.tender_status = FTE_TENDER_PVT.S_ACCEPTED) THEN
4597 wf_event.raise(
4598 p_event_name => 'oracle.apps.fte.lt.tenderaccept',
4599 p_event_key => p_trip_info.wf_item_key,
4600 p_parameters => l_parameter_list
4601 );
4602
4603 ELSIF (p_trip_info.tender_status = FTE_TENDER_PVT.S_REJECTED) THEN
4604 wf_event.raise(
4605 p_event_name => 'oracle.apps.fte.lt.tenderreject',
4606 p_event_key => p_trip_info.wf_item_key,
4607 p_parameters => l_parameter_list);
4608 END IF;
4609
4610
4611 l_parameter_list.DELETE;
4612
4613
4614 -- Standard call to get message count and if count is 1,get message info.
4615 --
4616 FND_MSG_PUB.Count_And_Get
4617 (
4618 p_count => x_msg_count,
4619 p_data => x_msg_data,
4620 p_encoded => FND_API.G_FALSE
4621 );
4622
4623
4624 --
4625 --
4626
4627 IF l_debug_on THEN
4628 WSH_DEBUG_SV.pop(l_module_name);
4629 END IF;
4630
4631 --}
4632 EXCEPTION
4633 --{
4634 WHEN FND_API.G_EXC_ERROR THEN
4635 ROLLBACK TO HANDLE_TENDER_RESPONSE_PUB;
4636 x_return_status := FND_API.G_RET_STS_ERROR ;
4637 FND_MSG_PUB.Count_And_Get
4638 (
4639 p_count => x_msg_count,
4640 p_data => x_msg_data,
4641 p_encoded => FND_API.G_FALSE
4642 );
4643 IF l_debug_on THEN
4644 WSH_DEBUG_SV.pop(l_module_name);
4645 END IF;
4646
4647
4648 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4649 ROLLBACK TO HANDLE_TENDER_RESPONSE_PUB;
4650 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4651 FND_MSG_PUB.Count_And_Get
4652 (
4653 p_count => x_msg_count,
4654 p_data => x_msg_data,
4655 p_encoded => FND_API.G_FALSE
4656 );
4657 IF l_debug_on THEN
4658 WSH_DEBUG_SV.pop(l_module_name);
4659 END IF;
4660
4661
4662 WHEN OTHERS THEN
4663 ROLLBACK TO HANDLE_TENDER_RESPONSE_PUB;
4664 wsh_util_core.default_handler('FTE_TENDER_PVT.HANDLE_TENDER_RESPONSE');
4665 x_return_status := FND_API.G_RET_STS_ERROR;
4666 FND_MSG_PUB.Count_And_Get
4667 (
4668 p_count => x_msg_count,
4669 p_data => x_msg_data,
4670 p_encoded => FND_API.G_FALSE
4671 );
4672 IF l_debug_on THEN
4673 WSH_DEBUG_SV.pop(l_module_name);
4674 END IF;
4675
4676
4677 --}
4678
4679 END HANDLE_TENDER_RESPONSE;
4680
4681
4682
4683 PROCEDURE HANDLE_CANCEL_TENDER(
4684 p_init_msg_list IN VARCHAR2,
4685 x_return_status OUT NOCOPY VARCHAR2,
4686 x_msg_count OUT NOCOPY NUMBER,
4687 x_msg_data OUT NOCOPY VARCHAR2,
4688 p_trip_info IN FTE_TENDER_ATTR_REC)
4689 IS
4690 --{ Local variables
4691
4692 l_parameter_list wf_parameter_list_t;
4693
4694 l_api_name CONSTANT VARCHAR2(30) := 'HANDLE_CANCEL_TENDER';
4695 l_api_version CONSTANT NUMBER := 1.0;
4696 l_debug_on CONSTANT BOOLEAN := WSH_DEBUG_SV.is_debug_enabled;
4697 l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.' || G_PKG_NAME || '.' || l_api_name;
4698
4699
4700 l_return_status VARCHAR2(32767);
4701 l_msg_count NUMBER;
4702 l_msg_data VARCHAR2(32767);
4703
4704 l_return_status_complete VARCHAR2(32767);
4705 l_msg_count_complete NUMBER;
4706 l_msg_data_complete VARCHAR2(32767);
4707
4708
4709 l_number_of_warnings NUMBER;
4710 l_number_of_errors NUMBER;
4711
4712 l_role_name VARCHAR2(32767);
4713
4714 --{Trip update parameters
4715 p_trip_info_tab WSH_TRIPS_PVT.Trip_Attr_Tbl_Type;
4716 p_wsh_trip_info WSH_TRIPS_PVT.Trip_Rec_Type;
4717 p_trip_in_rec WSH_TRIPS_GRP.TripInRecType;
4718 x_out_tab WSH_TRIPS_GRP.trip_Out_tab_type;
4719 --}
4720
4721
4722 BEGIN
4723
4724
4725 SAVEPOINT HANDLE_CANCEL_TENDER_PUB;
4726 IF l_debug_on THEN
4727 WSH_DEBUG_SV.push(l_module_name);
4728 END IF;
4729
4730 IF FND_API.to_Boolean( p_init_msg_list )
4731 THEN
4732 FND_MSG_PUB.initialize;
4733 END IF;
4734
4735 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
4736 x_msg_count := 0;
4737 x_msg_data := 0;
4738 l_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
4739 l_number_of_warnings := 0;
4740 l_number_of_errors := 0;
4741
4742 FTE_MLS_WRAPPER.INITIALIZE_TRIP_REC(x_trip_info => p_wsh_trip_info);
4743
4744 -- Update trip information
4745 p_wsh_trip_info.TRIP_ID := p_trip_info.trip_id;
4746 p_wsh_trip_info.wf_name := p_trip_info.WF_NAME;
4747 p_wsh_trip_info.wf_process_name := p_trip_info.wf_process_name;
4748 p_wsh_trip_info.carrier_Response := NULL;
4749 p_wsh_trip_info.operator := NULL;
4750 p_wsh_trip_info.vehicle_number := NULL;
4751 p_wsh_trip_info.load_tender_status := FTE_TENDER_PVT.S_SHIPPER_CANCELLED;
4752 p_wsh_trip_info.carrier_reference_number := NULL;
4753
4754
4755 p_trip_info_tab(1) :=p_wsh_trip_info;
4756 p_trip_in_rec.caller :='FTE_MLS_WRAPPER';
4757 p_trip_in_rec.phase :=NULL;
4758 p_trip_in_rec.action_code :='UPDATE';
4759
4760 WSH_INTERFACE_GRP.Create_Update_Trip
4761 (
4762 p_api_version_number =>1.0,
4763 p_init_msg_list =>FND_API.G_FALSE,
4764 p_commit =>FND_API.G_FALSE,
4765 x_return_status =>l_return_status,
4766 x_msg_count =>l_msg_count,
4767 x_msg_data =>l_msg_data,
4768 p_trip_info_tab =>p_trip_info_tab,
4769 p_in_rec =>p_trip_in_rec,
4770 x_out_tab =>x_out_tab
4771 );
4772
4773
4774 IF l_debug_on
4775 THEN
4776 WSH_DEBUG_SV.logmsg(l_module_name,' Return Status aftere calling WSH_INTERFACE_GRP.CREATE_UPDATE_TRIP ' ||
4777 l_return_status,
4778 WSH_DEBUG_SV.C_PROC_LEVEL);
4779 END IF;
4780
4781 wsh_util_core.api_post_call(
4782 p_return_status =>l_return_status,
4783 x_num_warnings =>l_number_of_warnings,
4784 x_num_errors =>l_number_of_errors,
4785 p_msg_data =>l_msg_data);
4786
4787
4788 COMPLETE_CANCEL_TENDER
4789 (
4790 p_tender_id =>p_trip_info.tender_id ,
4791 x_return_status =>l_return_status_complete,
4792 x_msg_count =>l_msg_count_complete ,
4793 x_msg_data => l_msg_data_complete
4794 );
4795
4796
4797 wsh_util_core.api_post_call(
4798 p_return_status =>l_return_status,
4799 x_num_warnings =>l_number_of_warnings,
4800 x_num_errors =>l_number_of_errors,
4801 p_msg_data =>l_msg_data);
4802
4803
4804 RELEASE_TENDER_BLOCK(
4805 p_init_msg_list => FND_API.G_FALSE,
4806 p_trip_info => p_trip_info,
4807 x_return_status => l_return_status,
4808 x_msg_count => l_msg_count,
4809 x_msg_data => l_msg_data);
4810
4811 wsh_util_core.api_post_call(
4812 p_return_status =>l_return_status,
4813 x_num_warnings =>l_number_of_warnings,
4814 x_num_errors =>l_number_of_errors,
4815 p_msg_data =>l_msg_data);
4816
4817 IF l_number_of_errors > 0
4818 THEN
4819 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
4820 RAISE FND_API.G_EXC_ERROR;
4821 ELSIF l_number_of_warnings > 0
4822 THEN
4823 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
4824 ELSE
4825 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
4826 END IF;
4827
4828
4829 wf_event.AddParameterToList(p_name => 'TENDER_ACTION',
4830 p_value => p_trip_info.tender_status,
4831 p_parameterlist => l_parameter_list);
4832
4833 IF l_debug_on THEN
4834 WSH_DEBUG_SV.logmsg(l_module_name,' Setting load tender status ' ||
4835 p_trip_info.tender_status,
4836 WSH_DEBUG_SV.C_PROC_LEVEL);
4837 END IF;
4838
4839
4840 wf_event.AddParameterToList(p_name => 'TENDER_STATUS',
4841 p_value => p_trip_info.tender_status,
4842 p_parameterlist => l_parameter_list);
4843
4844 wf_event.AddParameterToList(p_name => 'TENDER_ID',
4845 p_value => p_trip_info.tender_id,
4846 p_parameterlist => l_parameter_list);
4847
4848 wf_event.AddParameterToList(p_name => 'TRIP_ID',
4849 p_value => p_trip_info.trip_id,
4850 p_parameterlist => l_parameter_list);
4851
4852
4853 wf_event.AddParameterToList(p_name=>'SHIPPER_NAME',
4854 p_value=> wf_engine.GetItemAttrText('FTETEREQ',
4855 p_trip_info.wf_item_key,'SHIPPER_NAME'),
4856 p_parameterlist=>l_parameter_list);
4857
4858 wf_event.AddParameterToList(p_name=> 'SHIPPER_USER_ID',
4859 p_value=> wf_engine.GetItemAttrNumber('FTETEREQ',
4860 p_trip_info.wf_item_key,'USER_ID'),
4861 p_parameterlist=>l_parameter_list);
4862
4863 wf_event.AddParameterToList(p_name => 'CONTACT_PERFORMER',
4864 p_value => wf_engine.getItemAttrText(
4865 'FTETEREQ', p_trip_info.wf_item_key,
4866 'CONTACT_PERFORMER'),
4867 p_parameterlist => l_parameter_list);
4868
4869 wf_event.AddParameterToList(p_name=> 'USER_ID',
4870 p_value=> wf_engine.GetItemAttrNumber('FTETEREQ',
4871 p_trip_info.wf_item_key,'USER_ID'),
4872 p_parameterlist=>l_parameter_list);
4873
4874
4875 wf_event.AddParameterToList(p_name=> 'RESPONSIBILITY_ID',
4876 p_value=> wf_engine.GetItemAttrNumber('FTETEREQ',
4877 p_trip_info.wf_item_key,'RESPONSIBILITY_ID'),
4878 p_parameterlist=>l_parameter_list);
4879
4880 wf_event.AddParameterToList(p_name=> 'RESP_APPL_ID',
4881 p_value=> wf_engine.GetItemAttrNumber('FTETEREQ',
4882 p_trip_info.wf_item_key,'RESP_APPL_ID'),
4883 p_parameterlist=>l_parameter_list);
4884
4885
4886 wf_event.raise(
4887 p_event_name => 'oracle.apps.fte.lt.tendercancel',
4888 p_event_key => p_trip_info.wf_item_key,
4889 p_parameters => l_parameter_list
4890 );
4891
4892
4893 l_parameter_list.DELETE;
4894
4895
4896 -- Standard call to get message count and if count is 1,get message info.
4897 --
4898 FND_MSG_PUB.Count_And_Get
4899 (
4900 p_count => x_msg_count,
4901 p_data => x_msg_data,
4902 p_encoded => FND_API.G_FALSE
4903 );
4904
4905
4906 --
4907 --
4908
4909 IF l_debug_on THEN
4910 WSH_DEBUG_SV.pop(l_module_name);
4911 END IF;
4912
4913 --}
4914 EXCEPTION
4915 --{
4916 WHEN FND_API.G_EXC_ERROR THEN
4917 ROLLBACK TO HANDLE_CANCEL_TENDER_PUB;
4918 x_return_status := FND_API.G_RET_STS_ERROR ;
4919 FND_MSG_PUB.Count_And_Get
4920 (
4921 p_count => x_msg_count,
4922 p_data => x_msg_data,
4923 p_encoded => FND_API.G_FALSE
4924 );
4925 IF l_debug_on THEN
4926 WSH_DEBUG_SV.pop(l_module_name);
4927 END IF;
4928
4929
4930 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4931 ROLLBACK TO HANDLE_CANCEL_TENDER_PUB;
4932 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4933 FND_MSG_PUB.Count_And_Get
4934 (
4935 p_count => x_msg_count,
4936 p_data => x_msg_data,
4937 p_encoded => FND_API.G_FALSE
4938 );
4939 IF l_debug_on THEN
4940 WSH_DEBUG_SV.pop(l_module_name);
4941 END IF;
4942
4943
4944 WHEN OTHERS THEN
4945 ROLLBACK TO HANDLE_CANCEL_TENDER_PUB;
4946 wsh_util_core.default_handler('FTE_TENDER_PVT.HANDLE_CANCEL_TENDER');
4947 x_return_status := FND_API.G_RET_STS_ERROR;
4948 FND_MSG_PUB.Count_And_Get
4949 (
4950 p_count => x_msg_count,
4951 p_data => x_msg_data,
4952 p_encoded => FND_API.G_FALSE
4953 );
4954 IF l_debug_on THEN
4955 WSH_DEBUG_SV.pop(l_module_name);
4956 END IF;
4957
4958
4959 --}
4960
4961 END HANDLE_CANCEL_TENDER;
4962
4963 --{
4964
4965 --
4966 --Update tender does not use p_item_key passed in because
4967 -- we have to raise update event when ever there is a change in the
4968 --weight/vol.And item key used while raising the tender event
4969 --should not be updated. Because this is the key to identify the
4970 -- workflow.
4971
4972 PROCEDURE HANDLE_UPDATE_TENDER(
4973 p_init_msg_list IN VARCHAR2,
4974 x_return_status OUT NOCOPY VARCHAR2,
4975 x_msg_count OUT NOCOPY NUMBER,
4976 x_msg_data OUT NOCOPY VARCHAR2,
4977 p_trip_info IN FTE_TENDER_ATTR_REC) IS
4978 --{
4979
4980 l_parameter_list wf_parameter_list_t;
4981
4982 l_api_name CONSTANT VARCHAR2(30) := 'HANDLE_UPDATE_TENDER';
4983 l_api_version CONSTANT NUMBER := 1.0;
4984
4985 l_debug_on CONSTANT BOOLEAN := WSH_DEBUG_SV.is_debug_enabled;
4986 l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.' || G_PKG_NAME || '.' || l_api_name;
4987 l_item_key VARCHAR2(240);
4988
4989 BEGIN
4990 SAVEPOINT RAISE_TENDER_UPDATE_PUB;
4991
4992 IF FND_API.to_Boolean( p_init_msg_list )
4993 THEN
4994 FND_MSG_PUB.initialize;
4995 END IF;
4996
4997
4998 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
4999 x_msg_count := 0;
5000 x_msg_data := 0;
5001
5002
5003 IF l_debug_on THEN
5004 WSH_DEBUG_SV.logmsg(l_module_name,' Sending update tender notification. ',
5005 WSH_DEBUG_SV.C_PROC_LEVEL);
5006 END IF;
5007
5008 l_item_key := p_trip_info.WF_ITEM_KEY;
5009
5010
5011 wf_event.AddParameterToList( p_name => 'MBOL_NUM',
5012 p_value => wf_engine.GetItemAttrText('FTETEREQ',
5013 l_item_key,'MBOL_NUM'),
5014 p_parameterlist => l_parameter_list);
5015
5016 wf_event.AddParameterToList( p_name => 'TENDER_TEXT_ID',
5017 p_value => wf_engine.GetItemAttrText('FTETEREQ',
5018 l_item_key,'TENDER_TEXT_ID'),
5019 p_parameterlist => l_parameter_list);
5020
5021
5022 wf_event.AddParameterToList( p_name => 'RESPOND_BY_DATE',
5023 p_value => wf_engine.GetItemAttrDate('FTETEREQ',
5024 l_item_key,'RESPOND_BY_DATE'),
5025 p_parameterlist => l_parameter_list);
5026
5027 wf_event.AddParameterToList( p_name => 'TENDERED_DATE',
5028 p_value => wf_engine.GetItemAttrDate('FTETEREQ',
5029 l_item_key,'TENDERED_DATE'),
5030 p_parameterlist => l_parameter_list);
5031
5032 wf_event.AddParameterToList( p_name => 'MODE_OF_TRANSPORT',
5033 p_value => wf_engine.GetItemAttrText('FTETEREQ',
5034 l_item_key,'MODE_OF_TRANSPORT'),
5035 p_parameterlist => l_parameter_list);
5036
5037 wf_event.AddParameterToList( p_name => 'SHIPPER_WAIT_TIME',
5038 p_value => wf_engine.GetItemAttrText('FTETEREQ',
5039 l_item_key,'SHIPPER_WAIT_TIME'),
5040 p_parameterlist => l_parameter_list);
5041
5042
5043 wf_event.AddParameterToList( p_name => 'SHIPPING_ORG_NAME',
5044 p_value => wf_engine.GetItemAttrText('FTETEREQ',
5045 l_item_key,'SHIPPING_ORG_NAME'),
5046 p_parameterlist => l_parameter_list);
5047
5048 wf_event.AddParameterToList( p_name => 'TENDER_ID',
5049 p_value => wf_engine.GetItemAttrNumber('FTETEREQ',
5050 l_item_key,'TENDER_ID'),
5051 p_parameterlist => l_parameter_list);
5052
5053 wf_event.AddParameterToList( p_name => 'SHIPPER_NAME',
5054 p_value => wf_engine.GetItemAttrText('FTETEREQ',
5055 l_item_key,'SHIPPER_NAME'),
5056 p_parameterlist => l_parameter_list);
5057
5058 wf_event.AddParameterToList( p_name => 'CONTACT_PERFORMER',
5059 p_value => wf_engine.GetItemAttrText('FTETEREQ',
5060 l_item_key,'CONTACT_PERFORMER'),
5061 p_parameterlist => l_parameter_list);
5062
5063 wf_event.AddParameterToList( p_name => 'CARRIER_ID',
5064 p_value => wf_engine.GetItemAttrNumber('FTETEREQ',
5065 l_item_key,'CARRIER_ID'),
5066 p_parameterlist => l_parameter_list);
5067
5068 wf_event.AddParameterToList( p_name => 'CARRIER_SITE_ID',
5069 p_value => wf_engine.GetItemAttrNumber('FTETEREQ',
5070 l_item_key,'CARRIER_SITE_ID'),
5071 p_parameterlist => l_parameter_list);
5072
5073 wf_event.AddParameterToList( p_name => 'RESPONSE_URL',
5074 p_value => wf_engine.GetItemAttrText('FTETEREQ',
5075 l_item_key,'RESPONSE_URL'),
5076 p_parameterlist => l_parameter_list);
5077
5078 wf_event.AddParameterToList( p_name => 'NOTIF_TYPE',
5079 p_value => wf_engine.GetItemAttrText('FTETEREQ',
5080 l_item_key,'NOTIF_TYPE'),
5081 p_parameterlist => l_parameter_list);
5082
5083 wf_event.AddParameterToList( p_name => 'TRIP_ID',
5084 p_value => wf_engine.GetItemAttrNumber('FTETEREQ',
5085 l_item_key,'TRIP_ID'),
5086 p_parameterlist => l_parameter_list);
5087
5088 wf_event.AddParameterToList( p_name => 'SHIPPER_USER_ID',
5089 p_value => wf_engine.GetItemAttrNumber('FTETEREQ',
5090 l_item_key,'SHIPPER_USER_ID'),
5091 p_parameterlist => l_parameter_list);
5092
5093 wf_event.AddParameterToList(p_name=> 'USER_ID',
5094 p_value=> wf_engine.GetItemAttrNumber('FTETEREQ',
5095 l_item_key,'USER_ID'),
5096 p_parameterlist=>l_parameter_list);
5097
5098
5099 wf_event.AddParameterToList(p_name=> 'RESPONSIBILITY_ID',
5100 p_value=> wf_engine.GetItemAttrNumber('FTETEREQ',
5101 l_item_key,'RESPONSIBILITY_ID'),
5102 p_parameterlist=>l_parameter_list);
5103
5104 wf_event.AddParameterToList(p_name=> 'RESP_APPL_ID',
5105 p_value=> wf_engine.GetItemAttrNumber('FTETEREQ',
5106 l_item_key,'RESP_APPL_ID'),
5107 p_parameterlist=>l_parameter_list);
5108
5109
5110 wf_event.raise(
5111 p_event_name => 'oracle.apps.fte.lt.tenderupdate',
5112 p_event_key => GET_ITEM_KEY(p_trip_info.trip_id),--p_item_key,
5113 p_parameters => l_parameter_list
5114 );
5115
5116 -- Standard call to get message count and if count is 1,get message info.
5117 --
5118 FND_MSG_PUB.Count_And_Get
5119 (
5120 p_count => x_msg_count,
5121 p_data => x_msg_data,
5122 p_encoded => FND_API.G_FALSE
5123 );
5124 --
5125 --
5126
5127
5128 --}
5129 EXCEPTION
5130 --{
5131 WHEN FND_API.G_EXC_ERROR THEN
5132 ROLLBACK TO HANDLE_UPDATE_TENDER_PUB;
5133 x_return_status := FND_API.G_RET_STS_ERROR ;
5134 FND_MSG_PUB.Count_And_Get
5135 (
5136 p_count => x_msg_count,
5137 p_data => x_msg_data,
5138 p_encoded => FND_API.G_FALSE
5139 );
5140 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
5141 ROLLBACK TO HANDLE_UPDATE_TENDER_PUB;
5142 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
5143 FND_MSG_PUB.Count_And_Get
5144 (
5145 p_count => x_msg_count,
5146 p_data => x_msg_data,
5147 p_encoded => FND_API.G_FALSE
5148 );
5149 WHEN OTHERS THEN
5150 ROLLBACK TO HANDLE_UPDATE_TENDER_PUB;
5151 wsh_util_core.default_handler('FTE_TENDER_PVT.HANDLE_UPDATE_TENDER');
5152 x_return_status := FND_API.G_RET_STS_ERROR;
5153 FND_MSG_PUB.Count_And_Get
5154 (
5155 p_count => x_msg_count,
5156 p_data => x_msg_data,
5157 p_encoded => FND_API.G_FALSE
5158 );
5159
5160 --}
5161
5162 END HANDLE_UPDATE_TENDER;
5163
5164
5165 --}
5166
5167 END FTE_TENDER_PVT;