[Home] [Help]
PACKAGE BODY: APPS.FTE_TENDER_WF
Source
1 PACKAGE BODY FTE_TENDER_WF AS
2 /* $Header: FTETEWFB.pls 120.11 2006/06/12 23:29:04 nltan noship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'FTE_TENDER_WF';
5
6
7 PROCEDURE GET_DOCK_CLOSE_DATE (p_loc_id IN NUMBER,
8 p_tender_date IN DATE,
9 x_dock_close_date OUT NOCOPY DATE,
10 x_return_status OUT NOCOPY VARCHAR2) IS
11 l_from_time NUMBER;
12 l_to_time NUMBER;
13 l_dock_close_date DATE;
14 l_dock_close_dt VARCHAR2(100);
15 l_hrs NUMBER;
16 l_minutes NUMBER;
17 l_seconds NUMBER;
18 l_return_status VARCHAR2(1);
19 l_date DATE;
20 l_location_id NUMBER;
21
22 BEGIN
23
24 -- Standard Start of API savepoint
25 SAVEPOINT GET_DOCK_CLOSE_DATE_PUB;
26
27 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
28 l_location_id := p_loc_id;
29 l_date := p_tender_date;
30
31 WSH_CALENDAR_ACTIONS.Get_Shift_Times
32 (p_location_id => l_location_id,
33 p_date => l_date,
34 x_from_time => l_from_time,
35 x_to_time => l_to_time,
36 x_return_status => l_return_status);
37 x_dock_close_date := null;
38
39 if ( l_to_time is not null and l_from_time is not null) then
40 IF l_to_time <= l_from_time THEN
41 l_dock_close_date := p_tender_date + 1;
42 ELSE
43 l_dock_close_date := p_tender_date;
44 END IF;
45
46
47 l_hrs := floor(l_to_time/3600);
48 l_minutes := floor((l_to_time - (l_hrs * 3600))/60);
49 l_seconds := mod(l_to_time,60);
50
51 l_dock_close_dt := to_char(l_dock_close_date,'dd-mm-yyyy')||':'||to_char(l_hrs)||':'||to_char(l_minutes)||':'||to_char(l_seconds);
52 x_dock_close_date := to_date(l_dock_close_dt,'dd-mm-yyyy:hh24:mi:ss');
53 End if ;
54
55 EXCEPTION
56 WHEN OTHERS THEN
57 ROLLBACK TO GET_DOCK_CLOSE_DATE_PUB;
58 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
59
60 END GET_DOCK_CLOSE_DATE;
61
62
63
64
65
66
67
68 PROCEDURE VALIDATE_XML_INFO(
69 p_tender_number IN NUMBER,
70 p_tender_status IN VARCHAR2,
71 p_wf_item_key IN VARCHAR2,
72 p_shipment_status_id IN NUMBER,
73 x_return_status OUT NOCOPY VARCHAR2) IS
74
75 CURSOR c_tender_check (p_tender_id NUMBER)
76 IS
77 SELECT trip_id,wf_item_key
78 FROM wsh_trips
79 WHERE load_tender_number = p_tender_id;
80
81 l_trip_id NUMBER;
82 l_item_key VARCHAR2(240);
83 l_msg_data VARCHAR2(32000);
84 l_msg_count NUMBER;
85 l_return_status VARCHAR2(1);
86
87 BEGIN
88
89 -- Standard Start of API savepoint
90 SAVEPOINT VALIDATE_XML_INFO_PUB;
91
92
93 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
94 l_msg_count := 0;
95 l_msg_data := '';
96
97
98
99
100 ---------------------------------------------
101 -- Check for Load Tender Number and Item Key
102 ---------------------------------------------
103 OPEN c_tender_check(P_TENDER_NUMBER);
104 FETCH c_tender_check into l_trip_id , l_item_key;
105
106 IF c_tender_check%NOTFOUND THEN
107
108 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
109
110 FTE_FPA_UTIL.LOG_FAILURE_REASON(
111 p_init_msg_list => FND_API.G_FALSE,
112 p_parent_name => 'FTE_SHIPMENT_STATUS_HEADERS',
113 p_parent_id => P_SHIPMENT_STATUS_ID,
114 p_failure_type => 'FTE_TENDER_XML_FAILURE',
115 p_failure_reason => 'FTE_XML_INVALID_TENDER',
116 x_return_status => l_return_status,
117 x_msg_count => l_msg_count,
118 x_msg_data => l_msg_data);
119
120
121 ELSE
122
123 IF nvl(l_item_key,'-11') <> nvl(P_WF_ITEM_KEY,'-12') THEN
124
125 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
126
127 FTE_FPA_UTIL.LOG_FAILURE_REASON(
128 p_init_msg_list => FND_API.G_FALSE,
129 p_parent_name => 'FTE_SHIPMENT_STATUS_HEADERS',
130 p_parent_id => P_SHIPMENT_STATUS_ID,
131 p_failure_type => 'FTE_TENDER_XML_FAILURE',
132 p_failure_reason => 'FTE_XML_OLD_MESSAGE',
133 x_return_status => l_return_status,
134 x_msg_count => l_msg_count,
135 x_msg_data => l_msg_data);
136
137
138 END IF;
139
140 END IF;
141
142 CLOSE c_tender_check;
143
144 IF x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
145 RETURN;
146 END IF;
147
148
149 ---------------------------------------------
150 -- Check for Load Tender Status
151 ---------------------------------------------
152 IF P_TENDER_STATUS NOT IN (FTE_TENDER_PVT.S_ACCEPTED,FTE_TENDER_PVT.S_REJECTED) THEN
153
154 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
155
156 FTE_FPA_UTIL.LOG_FAILURE_REASON(
157 p_init_msg_list => FND_API.G_FALSE,
158 p_parent_name => 'FTE_SHIPMENT_STATUS_HEADERS',
159 p_parent_id => P_SHIPMENT_STATUS_ID,
160 p_failure_type => 'FTE_TENDER_XML_FAILURE',
161 p_failure_reason => 'FTE_XML_INVALID_TENDER_STATUS',
162 x_return_status => l_return_status,
163 x_msg_count => l_msg_count,
164 x_msg_data => l_msg_data);
165
166 END IF;
167
168 -- Standard call to get message count and if count is 1,get message info.
169 --
170 FND_MSG_PUB.Count_And_Get
171 (
172 p_count => l_msg_count,
173 p_data => l_msg_data,
174 p_encoded => FND_API.G_FALSE
175 );
176
177 --}
178 EXCEPTION
179 --{
180 WHEN FND_API.G_EXC_ERROR THEN
181 ROLLBACK TO VALIDATE_XML_INFO_PUB;
182 x_return_status := FND_API.G_RET_STS_ERROR;
183 FND_MSG_PUB.Count_And_Get
184 (
185 p_count => l_msg_count,
186 p_data => l_msg_data,
187 p_encoded => FND_API.G_FALSE
188 );
189 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
190 ROLLBACK TO VALIDATE_XML_INFO_PUB;
191 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
192 FND_MSG_PUB.Count_And_Get
193 (
194 p_count => l_msg_count,
195 p_data => l_msg_data,
196 p_encoded => FND_API.G_FALSE
197 );
198 WHEN OTHERS THEN
199 ROLLBACK TO VALIDATE_XML_INFO_PUB;
200 wsh_util_core.default_handler('FTE_TENDER_WF.VALIDATE_XML_INFO_PUB');
201 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
202 FND_MSG_PUB.Count_And_Get
203 (
204 p_count => l_msg_count,
205 p_data => l_msg_data,
206 p_encoded => FND_API.G_FALSE
207 );
208
209 --}
210
211
212 END VALIDATE_XML_INFO;
213
214 PROCEDURE GET_ITEM_INFO(
215 P_ITEM_TYPE IN VARCHAR2,
216 P_ITEM_KEY IN VARCHAR2,
217 X_SHIPPER_NAME OUT NOCOPY VARCHAR2,
218 X_TENDERED_DATE OUT NOCOPY DATE,
219 X_RESPOND_BY_DATE OUT NOCOPY DATE,
220 X_VEHICLE_TYPE OUT NOCOPY VARCHAR2,
221 X_VEHICLE_CLASS OUT NOCOPY VARCHAR2 ) IS
222
223 BEGIN
224
225
226 X_SHIPPER_NAME := wf_engine.GetItemAttrText(P_ITEM_TYPE,P_ITEM_KEY,'SHIPPER_NAME');
227
228 X_TENDERED_DATE := wf_engine.GetItemAttrDate(P_ITEM_TYPE,P_ITEM_KEY,'TENDERED_DATE');
229
230 X_RESPOND_BY_DATE := wf_engine.GetItemAttrDate(P_ITEM_TYPE,P_ITEM_KEY,'RESPOND_BY_DATE');
231
232 X_VEHICLE_TYPE := wf_engine.GetItemAttrText(P_ITEM_TYPE,P_ITEM_KEY,'VEHICLE_TYPE');
233
234 X_VEHICLE_CLASS := wf_engine.GetItemAttrText(P_ITEM_TYPE,P_ITEM_KEY,'VEHICLE_CLASS');
235
236
237
238 END GET_ITEM_INFO;
239
240
241
242 PROCEDURE GET_SERVICE_VEHICLE_INFO(
243 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
244 p_tender_id IN NUMBER,
245 x_vehicle_type OUT NOCOPY VARCHAR2,
246 x_vehicle_class OUT NOCOPY VARCHAR2,
247 x_service_level OUT NOCOPY VARCHAR2) IS
248
249 CURSOR c_vehicle_info(p_tender_id NUMBER) is
250 select
251 msi.segment1 vehicle_type,
252 flvv.meaning vehicle_class
253 from wsh_trips wts,
254 mtl_system_items msi,
255 fnd_lookup_values_vl flvv,
256 fte_vehicle_types fvt
257 where
258 fvt.inventory_item_id = wts.vehicle_item_id
259 and fvt.organization_id = wts.vehicle_organization_id
260 and wts.vehicle_item_id = msi.inventory_item_id
261 and fvt.vehicle_class_code = flvv.lookup_code(+)
262 and flvv.lookup_type(+) = 'FTE_VEHICLE_CLASS'
263 and wts.vehicle_organization_id = msi.organization_id
264 and wts.load_tender_number = p_tender_id;
265
266 CURSOR c_service_info(p_tender_id NUMBER) is
267 select
268 wl.meaning service_level
269 from
270 wsh_trips wts,
271 wsh_lookups wl
272 where
273 wts.service_level = wl.lookup_code(+)
274 and wl.lookup_type(+) = 'WSH_SERVICE_LEVELS'
275 and wts.load_tender_number = p_tender_id;
276
277 BEGIN
278
279 OPEN c_vehicle_info(p_tender_id);
280 FETCH c_vehicle_info into x_vehicle_type,x_vehicle_class;
281 CLOSE c_vehicle_info;
282
283 OPEN c_service_info(p_tender_id);
284 FETCH c_service_info into x_service_level;
285 CLOSE c_service_info;
286
287
288 END GET_SERVICE_VEHICLE_INFO;
289
290 PROCEDURE GET_MBOL_NUMBER(
291 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
292 p_tender_id IN NUMBER,
293 p_generate_mbol IN BOOLEAN,
294 x_mbol_number OUT NOCOPY VARCHAR2) IS
295
296 CURSOR c_mode_of_transport(p_tender_id NUMBER) is
297 SELECT mode_of_transport,trip_id
298 from wsh_trips
299 where load_tender_number = p_tender_id;
300
301 CURSOR c_mbol_info(p_trip_id NUMBER) is
302 select sequence_number
303 from wsh_document_instances
304 where
305 entity_id = p_trip_id
306 and entity_name = 'WSH_TRIPS'
307 and document_type = 'MBOL';
308
309 l_mode_of_transport VARCHAR2(60);
310 l_trip_id NUMBER;
311 l_mbol_number VARCHAR2(100);
312 l_return_status VARCHAR2(1000);
313
314
315 l_generate_mbol BOOLEAN := FALSE;
316
317 BEGIN
318
319
320 OPEN c_mode_of_transport(p_tender_id);
321 FETCH c_mode_of_transport into l_mode_of_transport,l_trip_id;
322 CLOSE c_mode_of_transport;
323
324 IF l_mode_of_transport = 'TRUCK' THEN
325
326 IF (p_generate_mbol = TRUE) THEN
327
328 WSH_MBOLS_PVT.Generate_MBOL(p_trip_id => l_trip_id,
329 x_sequence_number => l_mbol_number,
330 x_return_status => l_return_status);
331
332 IF ( (l_return_status = 'E') OR (l_return_status = 'U') )
333 THEN
334
335 wf_core.TOKEN('ERROR_STRING','Error in MBOL Generation ');
336 wf_core.RAISE('FTE_WF_ERROR_MESSAGE');
337
338 END IF;
339
340
341 ELSE
342 OPEN c_mbol_info(l_trip_id);
343 FETCH c_mbol_info into l_mbol_number;
344 CLOSE c_mbol_info;
345
346 END IF;
347
348 x_mbol_number := l_mbol_number;
349
350 END IF;
351
352 END GET_MBOL_NUMBER;
353
354
355 -- ------------------------------------------------------------------------------- --
356 -- --
357 -- NAME: INITIALIZE_TENDER_REQUEST --
358 -- TYPE: PROCEDURE --
359 -- PARAMETERS (IN): itemtype VARCHAR2 --
360 -- itemkey VARCHAR2 (wf block instance label) --
361 -- actid NUMBER --
362 -- funcmode VARCHAR --
363 --
364 -- PARAMETERS (OUT): resultout VARCHAR2 --
365 -- PARAMETERS (IN OUT): none --
366 -- RETURN: none --
367 -- DESCRIPTION: This procedure will initialize the attributes to initialize--
368 -- Load Tendering Process.
369 -- --
370 -- CHANGE CONTROL LOG --
371 -- ------------------ --
372 -- --
373 -- DATE VERSION BY BUG DESCRIPTION --
374 -- ---------- ------- -------- ------- --------------------------------------- --
375 -- 2002 11.5.8 HBHAGAVA Created --
376 -- 2003 11.5.9 SAMUTHUK Modified --
377 -- ------------------------------------------------------------------------------- --
378
379 PROCEDURE INITIALIZE_TENDER_REQUEST(itemtype in varchar2,
380 itemkey in varchar2,
381 actid in number,
382 funcmode in varchar2,
383 resultout out NOCOPY varchar2) IS
384
385
386
387
388 l_result_code VARCHAR2(10);
389 l_tender_id NUMBER;
390 l_trip_id NUMBER;
391 l_api_name VARCHAR2(30) := 'INITIALIZE_TENDER_REQUEST';
392
393
394 l_return_status VARCHAR2(30000);
395 l_msg_count NUMBER;
396 l_msg_data VARCHAR2(30000);
397 l_msg_string VARCHAR2(30000);
398
399
400 -- Local Variables
401 l_trip_name VARCHAR2(32767);
402 l_wait_time_uom VARCHAR2(32767);
403 l_carrier_id NUMBER;
404 l_mode_of_transport VARCHAR2(100);
405
406 l_shipper_wait_time VARCHAR2(32767);
407 l_shipper_cutoff_time NUMBER;
408 l_shipper_cutoff_time_days NUMBER;
409 l_load_tendered_time DATE;
410
411 l_shipper_name VARCHAR2(32767);
412 l_remaining_time VARCHAR2(32767);
413
414 l_temp_value NUMBER;
415 l_rem_days NUMBER;
416 l_rem_hr NUMBER;
417 l_rem_min NUMBER;
418
419 l_ship_org_name VARCHAR2(100);
420 l_ship_info VARCHAR2(32767);
421
422 l_notif_type VARCHAR2(10);
423 l_auto_accept VARCHAR2(10);
424 l_carrier_site_id NUMBER;
425
426 l_carrier_name VARCHAR2(360);
427
428 cursor get_carrier_name (c_trip_id number) is
429 select hz.party_name from
430 hz_parties hz, wsh_trips wt
431 where wt.carrier_id= hz.party_id AND
432 wt.trip_id = c_trip_id;
433
434 CURSOR get_trip_cur(c_trip_id NUMBER) is
435 SELECT trip_id, name,load_tender_status,load_Tender_number,
436 shipper_wait_time,wait_time_uom,carrier_id,
437 load_tendered_time,mode_of_transport
438 from wsh_trips
439 where trip_id = c_trip_id;
440
441
442 /* Bug 5312853: Query does not join contacts to sites
443 CURSOR get_notif_type_c (l_trip_id NUMBER) IS
444 SELECT car_sites.tender_transmission_method notif_type,
445 car_sites.auto_accept_load_tender auto_accept_flag,car_sites.carrier_site_id
446 FROM hz_contact_points cont, hz_relationships rel,
447 hz_party_sites sites, wsh_Carrier_sites car_sites, wsh_trips trips
448 WHERE owner_table_name = 'HZ_PARTIES'
449 and rel.party_id = owner_table_id
450 and sites.party_id =rel.subject_id
451 and car_sites.carrier_site_id = sites.party_site_id
452 and trips.carrier_contact_id = owner_table_id
453 and trip_id = l_trip_id;
454 */
455
456 CURSOR get_notif_type_c (l_trip_id NUMBER) IS
457 SELECT car_sites.tender_transmission_method notif_type,
458 car_sites.auto_accept_load_tender auto_accept_flag,car_sites.carrier_site_id
459 FROM hz_parties party, hz_relationships rel, hz_party_sites sites,
460 hz_org_contacts cont, hz_contact_points points,
461 wsh_carrier_sites car_sites, wsh_trips trips
462 WHERE rel.object_id = party.party_id
463 AND rel.subject_type = 'ORGANIZATION'
464 AND rel.subject_Table_name = 'HZ_PARTIES'
465 AND sites.party_id = rel.subject_id
466 AND cont.party_site_id = sites.party_site_id
467 AND cont.party_relationship_id = rel.relationship_id
468 AND points.owner_table_id = rel.party_id
469 AND points.owner_table_name = 'HZ_PARTIES'
470 AND points.contact_point_type = 'EMAIL'
471 AND car_sites.carrier_site_id = sites.party_site_id
472 AND trips.carrier_contact_id = points.owner_table_id
473 AND trips.trip_id = l_trip_id;
474 --}
475
476
477 -- Debug paramteres
478 l_debug_on CONSTANT BOOLEAN := WSH_DEBUG_SV.is_debug_enabled;
479 l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.' || 'FTE_TENDER_WF'|| '.' || 'INITIALIZE_TENDER_REQUEST';
480
481 l_userId NUMBER;
482 l_respId NUMBER;
483 l_respAppId NUMBER;
484
485
486 BEGIN
487
488 --wsh_debug_sv.push(l_api_name);
489 --
490 -- RUN mode - normal process execution
491 --
492 IF (funcmode = 'RUN') THEN
493 -- These values should be retrived from database
494
495 IF l_debug_on THEN
496 WSH_DEBUG_SV.push(l_module_name);
497 END IF;
498
499
500 -- Initialize all the attributes to send notification to carrier
501 -- based on the tender id
502 -- Get the tender id first
503 IF l_debug_on THEN
504 WSH_DEBUG_SV.logmsg(l_module_name,'Getting Trip id, tender id, shipper name ',WSH_DEBUG_SV.C_PROC_LEVEL);
505 END IF;
506
507
508 l_userId := wf_engine.GetItemAttrNumber('FTETEREQ',itemKey,'USER_ID');
509 l_respAppId := wf_engine.GetItemAttrNumber('FTETEREQ',itemKey, 'RESP_APPL_ID');
510 l_respId := wf_engine.GetItemAttrNumber('FTETEREQ',itemKey,'RESPONSIBILITY_ID');
511
512
513 IF(l_respAppId IS NULL OR l_respId IS NULL) THEN
514 RAISE no_data_found;
515 ELSE
516 FND_GLOBAL.Apps_Initialize(l_userId, l_respId, l_respAppId);
517 END IF;
518
519
520 IF l_debug_on THEN
521 WSH_DEBUG_SV.logmsg(l_module_name,'Item key' || itemkey,WSH_DEBUG_SV.C_PROC_LEVEL);
522 END IF;
523
524 l_tender_id := wf_engine.GetItemAttrNumber(itemtype,itemkey,'TENDER_ID');
525
526 --l_trip_id := wf_engine.GetItemAttrNumber(itemtype,itemkey,'TRIP_ID');
527 l_trip_id := l_tender_id;
528
529
530 IF l_debug_on THEN
531 WSH_DEBUG_SV.logmsg(l_module_name,'After getting trip id ' || l_trip_id,WSH_DEBUG_SV.C_PROC_LEVEL);
532 END IF;
533
534 l_shipper_name := wf_engine.GetItemAttrText(itemtype,itemkey,'SHIPPER_NAME');
535
536 IF l_debug_on THEN
537 WSH_DEBUG_SV.logmsg(l_module_name,'After Getting Trip id, tender id, shipper name ' ||
538 l_tender_id,WSH_DEBUG_SV.C_PROC_LEVEL);
539 END IF;
540
541 --{
542 -- **************************** Rel 12
543 -- Query up trip information
544
545
546 FOR get_trip_rec IN get_trip_cur(l_trip_id)
547 LOOP
548 --{
549 l_trip_name := get_trip_rec.name;
550 l_shipper_wait_time := get_trip_rec.shipper_wait_time;
551 l_wait_time_uom := get_trip_rec.wait_time_uom;
552 l_load_tendered_time := get_trip_rec.load_tendered_time;
553 l_carrier_id := get_trip_rec.carrier_id;
554 l_mode_of_transport := get_trip_rec.mode_of_transport;
555 --}
556 END LOOP;
557 -- END OF get trip segment info
558 --
559 --
560 IF get_trip_cur%ISOPEN THEN
561 CLOSE get_trip_cur;
562 END IF;
563
564
565 -- Calculate Shipper Cutoff time
566 l_shipper_cutoff_time := FTE_MLS_UTIL.FTE_UOM_CONV(l_shipper_wait_time, l_wait_time_uom,'MIN');
567
568 IF l_debug_on THEN
569 WSH_DEBUG_SV.logmsg(l_module_name,'Gettting shipper cuttoff time in MIN ' || l_shipper_cutoff_time,
570 WSH_DEBUG_SV.C_PROC_LEVEL);
571 END IF;
572
573
574 IF (l_shipper_cutoff_time = -9999)
575 THEN
576 IF l_debug_on THEN
577 WSH_DEBUG_SV.logmsg(l_module_name,'orig wait time:'||l_shipper_wait_time ||
578 l_wait_time_uom,WSH_DEBUG_SV.C_PROC_LEVEL);
579 WSH_DEBUG_SV.logmsg(l_module_name,'conv wait time:'||l_shipper_cutoff_time ||
580 'MIN',WSH_DEBUG_SV.C_PROC_LEVEL);
581 END IF;
582
583 FND_MESSAGE.SET_NAME('FTE','FTE_INVLD_SHIP_WAITTIME');
584 FND_MESSAGE.SET_TOKEN('WAIT_TIME',l_shipper_wait_time);
585 FND_MESSAGE.SET_TOKEN('WAIT_TIME_UOM',l_wait_time_uom);
586 WSH_UTIL_CORE.ADD_MESSAGE(WSH_UTIL_CORE.G_RET_STS_ERROR);
587 l_msg_string := FTE_MLS_UTIL.GET_MESSAGE(l_msg_count,l_msg_data);
588
589
590 wf_core.TOKEN('ERROR_STRING',l_msg_string);
591 wf_core.RAISE('FTE_WF_ERROR_MESSAGE');
592
593 END IF;
594
595
596
597 l_shipper_cutoff_time_days := FTE_MLS_UTIL.FTE_UOM_CONV(l_shipper_wait_time,
598 l_wait_time_uom,'DAY');
599
600 IF l_debug_on THEN
601 WSH_DEBUG_SV.logmsg(l_module_name,
602 'Gettting shipper cuttoff time in days ' || l_shipper_cutoff_time_days,
603 WSH_DEBUG_SV.C_PROC_LEVEL);
604 END IF;
605
606
607 -- **************************** Rel 12
608 --}
609 -- initializing following parameters
610 -- Carrier Name, Shipper Name, Tender Id, Respond By Date, Shipping Org Name,
611 -- Response Url, Tendered Date, Shippment Information, Handling Information
612 -- Carrier Contact Name, Email, Phone, Fax, Pickup Date
613
614 -- Get response by date, tendered date, shipment info, handling info
615 -- pickup date from GET_TENDER_INFO
616
617 ------------------------------------------------------------------
618 -- Samuthuk [ workflow Notifications std ]
619 ------------------------------------------------------------------
620
621 wf_engine.SetItemOwner(itemtype,itemkey,l_shipper_name);
622
623
624 IF l_debug_on THEN
625 WSH_DEBUG_SV.logmsg(l_module_name,'Setting tender id',WSH_DEBUG_SV.C_PROC_LEVEL);
626 END IF;
627
628 wf_engine.SetItemAttrText(itemtype => itemtype,
629 itemkey => itemkey,
630 aname => 'TENDER_TEXT_ID',
631 avalue => to_char(l_tender_id));
632
633 wf_engine.SetItemAttrText(itemtype => itemtype,
634 itemkey => itemkey,
635 aname => 'TENDER_STATUS',
636 avalue => FTE_TENDER_PVT.S_TENDERED);
637
638
639 wf_engine.SetItemAttrNumber(itemtype => itemtype,
640 itemkey => itemkey,
641 aname => 'CARRIER_ID',
642 avalue => l_carrier_id);
643
644 wf_engine.SetItemAttrDate(itemtype => itemtype,
645 itemkey => itemkey,
646 aname => 'RESPOND_BY_DATE',
647 avalue => (l_load_tendered_time+l_shipper_cutoff_time_days));
648
649
650 wf_engine.SetItemAttrDate(itemtype => itemtype,
651 itemkey => itemkey,
652 aname => 'TENDERED_DATE',
653 avalue => l_load_tendered_time);
654
655 wf_engine.SetItemAttrNumber(itemtype => itemtype,
656 itemkey => itemkey,
657 aname => 'SHIPPER_CUTOFF_TIME',
658 avalue => l_shipper_cutoff_time);
659
660 --Added by sharvisa
661 wf_engine.SetItemAttrText(itemtype => itemtype,
662 itemkey => itemkey,
663 aname => 'MODE_OF_TRANSPORT',
664 avalue => l_mode_of_transport);
665
666 ------------------------------------------------------------------
667 -- Samuthuk [ workflow Notifications std ]
668 ------------------------------------------------------------------
669 l_shipper_wait_time := to_char(l_shipper_wait_time)||':'||l_wait_time_uom;
670
671 wf_engine.SetItemAttrText(itemtype => itemtype,
672 itemkey => itemkey,
673 aname => 'SHIPPER_WAIT_TIME',
674 avalue => l_shipper_wait_time);
675
676 ------------------------------------------------------------------
677
678
679 -- org and shipment info
680 FTE_TRIPS_PVT.GET_SHIPMENT_INFORMATION
681 (p_init_msg_list => FND_API.G_FALSE,
682 p_tender_number => l_tender_id,
683 x_return_status => l_return_status,
684 x_msg_count => l_msg_count,
685 x_msg_data => l_msg_data,
686 x_shipment_info => l_ship_info,
687 x_shipping_org_name => l_ship_org_name);
688
689 IF ( (l_return_status = 'E')
690 OR (l_return_status = 'U') )
691 THEN
692 l_msg_string := FTE_MLS_UTIL.GET_MESSAGE(l_msg_count,l_msg_data);
693 wf_core.TOKEN('ERROR_STRING',l_msg_string);
694 wf_core.RAISE('FTE_WF_ERROR_MESSAGE');
695 END IF;
696
697
698 wf_engine.SetItemAttrText(itemtype => itemtype,
699 itemkey => itemkey,
700 aname => 'SHIPPING_ORG_NAME',
701 avalue => l_ship_org_name);
702
703
704 -- Notification and Auto accept info
705 IF l_debug_on THEN
706 WSH_DEBUG_SV.logmsg(l_module_name,' Trip id ');
707 WSH_DEBUG_SV.logmsg(l_module_name,' Auto Accept ' || l_auto_accept,
708 WSH_DEBUG_SV.C_PROC_LEVEL);
709 END IF;
710
711
712 FOR get_notif_type_rec IN get_notif_type_c(l_trip_id)
713 LOOP
714 --{
715 IF l_debug_on THEN
716 WSH_DEBUG_SV.logmsg(l_module_name,' Getting notif and auto accept ',
717 WSH_DEBUG_SV.C_PROC_LEVEL);
718 END IF;
719
720 l_notif_type := get_notif_type_rec.notif_type;
721 l_auto_accept := get_notif_type_rec.auto_accept_flag;
722 l_carrier_site_id := get_notif_type_rec.carrier_site_id;
723 --}
724 END LOOP;
725 -- END OF g
726 --
727 --
728 IF get_notif_type_c%ISOPEN THEN
729 CLOSE get_notif_type_c;
730 END IF;
731
732 IF l_debug_on THEN
733 WSH_DEBUG_SV.logmsg(l_module_name,' Notif Type ' || l_notif_type,
734 WSH_DEBUG_SV.C_PROC_LEVEL);
735 WSH_DEBUG_SV.logmsg(l_module_name,' Auto Accept ' || l_auto_accept,
736 WSH_DEBUG_SV.C_PROC_LEVEL);
737 END IF;
738
739
740 IF (l_notif_type IS NULL)
741 THEN
742 l_notif_type := 'EMAIL';
743 END IF;
744
745
746 IF (l_auto_accept IS NULL)
747 THEN
748 l_auto_accept := 'N';
749 END IF;
750
751 OPEN get_carrier_name(l_trip_id);
752 FETCH get_carrier_name into l_carrier_name;
753 close get_carrier_name;
754
755
756 wf_engine.SetItemAttrText(itemtype => itemtype,
757 itemkey => itemkey,
758 aname => 'CARRIER_NAME',
759 avalue => l_carrier_name);
760
761 wf_engine.SetItemAttrText(itemtype => itemtype,
762 itemkey => itemkey,
763 aname => 'NOTIF_TYPE',
764 avalue => l_notif_type);
765
766 wf_engine.SetItemAttrText(itemtype => itemtype,
767 itemkey => itemkey,
768 aname => 'AUTO_ACCEPT',
769 avalue => l_auto_accept);
770
771 -- Rel 12 Coding....
772 wf_engine.SetItemAttrNumber(itemtype => itemtype,
773 itemkey => itemkey,
774 aname => 'CARRIER_SITE_ID',
775 avalue => l_carrier_site_id);
776
777
778
779
780
781
782 resultout := 'COMPLETE:Y';
783 return;
784
785
786 END IF; --- func mode
787
788
789 --7
790 -- CANCEL mode
791 --
792 IF (funcmode = 'CANCEL') THEN
793 resultout := 'COMPLETE:';
794 return;
795 END IF;
796
797 IF (funcmode = 'TIMEOUT') THEN
798 resultout := 'COMPLETE:';
799 return;
800 END IF;
801
802 EXCEPTION
803 WHEN OTHERS THEN
804 wf_core.context('FTE_TENDER_WF',
805 'INITIALIZE_TENDER_REQUEST',
806 itemtype,
807 itemkey,
808 actid,
809 funcmode);
810 RAISE;
811
812 END INITIALIZE_TENDER_REQUEST;
813
814
815 -- ------------------------------------------------------------------------------- --
816 -- --
817 -- NAME: UPDATE_CARRIER_RESPONSE --
818 -- TYPE: PROCEDURE --
819 -- PARAMETERS (IN): p_tender_id NUMBER --
820 -- p_remarks VARCHAR2 --
821 -- p_initial_pickup_date DATE --
822 -- p_ultimate_dropoff_date DATE --
823 -- --
824 -- PARAMETERS (OUT): --
825 -- x_return_status VARCHAR2 --
826 -- x_msg_count NUMBER --
827 -- x_msg_data VARCHAR2 --
828 -- PARAMETERS (IN OUT): none --
829 -- RETURN: none --
830 -- DESCRIPTION: This procedure Update the Trip/Stops with Carrier Responses --
831 -- --
832 -- CHANGE CONTROL LOG --
833 -- ------------------ --
834 -- --
835 -- DATE VERSION BY BUG DESCRIPTION --
836 -- ---------- ------- -------- ------- --------------------------------------- --
837 -- 2003 11.5.9 SAMUTHUK Created --
838 -- 2005 SHRAVISA Updated --
839 -- ------------------------------------------------------------------------------- --
840
841 PROCEDURE UPDATE_CARRIER_RESPONSE(
842 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
843 p_tender_id IN NUMBER,
844 p_tender_status IN VARCHAR2,
845 p_wf_item_key IN VARCHAR2,
846 p_remarks IN VARCHAR2,
847 p_initial_pickup_date IN DATE,
848 p_ultimate_dropoff_date IN DATE,
849 p_vehicle_number IN VARCHAR2,
850 p_operator IN VARCHAR2,
851 p_carrier_ref_number IN VARCHAR2,
852 p_call_source IN VARCHAR2,
853 x_return_status OUT NOCOPY VARCHAR2,
854 x_msg_count OUT NOCOPY NUMBER,
855 x_msg_data OUT NOCOPY VARCHAR2) IS
856
857 l_api_name VARCHAR2(30) := 'UPDATE_CARRIER_RESPONSE';
858 l_api_version CONSTANT NUMBER := 1.0;
859 l_debug_on BOOLEAN := WSH_DEBUG_SV.is_debug_enabled;
860 l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.' || G_PKG_NAME || '.' || 'UPDATE_CARRIER_RESPONSE';
861
862
863 l_action_out_rec FTE_ACTION_OUT_REC;
864 trip_action_param FTE_TRIP_ACTION_PARAM_REC;
865 l_tender_attr_rec FTE_TENDER_ATTR_REC;
866 l_tender_status VARCHAR2(30);
867 l_tender_process VARCHAR2(100);
868
869 l_file_name VARCHAR2(300);
870 l_return_status VARCHAR2(1);
871 l_msg_count NUMBER;
872 l_msg_data VARCHAR2(2000);
873
874
875
876
877 BEGIN
878
879 wsh_debug_interface.g_Debug := TRUE;
880 WSH_DEBUG_SV.start_debugger
881 (x_file_name => l_file_name,
882 x_return_status => l_return_status,
883 x_msg_count => l_msg_count,
884 x_msg_data => l_msg_data);
885
886 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
887
888 SAVEPOINT UPDATE_CARRIER_RESPONSE_PUB;
889
890 IF FND_API.to_Boolean( p_init_msg_list )
891 THEN
892 FND_MSG_PUB.initialize;
893 END IF;
894 --
895 IF l_debug_on THEN
896 wsh_debug_sv.push(l_module_name);
897 END IF;
898
899 --
900 -- Initialize API return status to success
901 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
902 x_msg_count := 0;
903 x_msg_data := 0;
904
905 WSH_DEBUG_SV.log(l_module_name,' ************ Parameters ********************* ');
906 WSH_DEBUG_SV.log(l_module_name,' p_tender_status ',p_tender_status);
907
908 IF p_tender_status = FTE_TENDER_PVT.S_ACCEPTED THEN
909 l_tender_process := 'TENDER_ACCEPT_PROCESS';
910 ELSIF p_tender_status = FTE_TENDER_PVT.S_REJECTED THEN
911 l_tender_process := 'TENDER_REJECT_PROCESS';
912 END IF;
913
914 WSH_DEBUG_SV.log(l_module_name,' l_tender_process ',l_tender_process);
915
916 l_tender_attr_rec := FTE_TENDER_ATTR_REC(
917 p_tender_id, -- TripId
918 null, -- Trip Name
919 p_tender_id, --tender id
920 p_tender_status, -- status
921 null,-- car_contact_id
922 null, -- car contact name
923 null, -- auto_accept
924 null, -- auto tender
925 null, -- ship wait time
926 null, -- ship time uom
927 'FTETERES', -- wf name
928 l_tender_process, -- wf process name
929 p_wf_item_key, --wf item key
930 p_remarks,
931 p_initial_pickup_date,
932 p_ultimate_dropoff_date,
933 p_vehicle_number,
934 p_operator,
935 p_carrier_ref_number,
936 null,
937 FTE_TENDER_PVT.S_SOURCE_XML,
938 null);
939
940 trip_action_param := FTE_TRIP_ACTION_PARAM_REC(null,p_tender_status,
941 204,null,null,null,null,null,
942 null,null,null,null,null,null,
943 null,null);
944
945
946 FTE_MLS_WRAPPER.Trip_Action (p_api_version_number => 1.0,
947 p_init_msg_list => FND_API.G_TRUE,
948 x_return_status => x_return_status,
949 x_msg_count => x_msg_count,
950 x_msg_data => x_msg_data,
951 x_action_out_rec => l_action_out_rec,
952 p_trip_info_rec => l_tender_attr_rec,
953 p_action_prms => trip_action_param);
954
955
956
957
958 -- Standard call to get message count and if count is 1,get message info.
959 --
960 FND_MSG_PUB.Count_And_Get
961 (
962 p_count => x_msg_count,
963 p_data => x_msg_data,
964 p_encoded => FND_API.G_FALSE
965 );
966 --
967 --
968
969
970 IF l_debug_on THEN
971 WSH_DEBUG_SV.pop(l_module_name);
972 END IF;
973
974
975 EXCEPTION
976
977 WHEN FND_API.G_EXC_ERROR THEN
978 ROLLBACK TO UPDATE_CARRIER_RESPONSE_PUB;
979 x_return_status := FND_API.G_RET_STS_ERROR ;
980 FND_MSG_PUB.Count_And_Get
981 (
982 p_count => x_msg_count,
983 p_data => x_msg_data,
984 p_encoded => FND_API.G_FALSE
985 );
986 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
987 ROLLBACK TO UPDATE_CARRIER_RESPONSE_PUB;
988 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
989 FND_MSG_PUB.Count_And_Get
990 (
991 p_count => x_msg_count,
992 p_data => x_msg_data,
993 p_encoded => FND_API.G_FALSE
994 );
995
996 WHEN OTHERS THEN
997 ROLLBACK TO UPDATE_CARRIER_RESPONSE_PUB;
998 wsh_util_core.default_handler('FTE_TENDER_WF.UPDATE_CARRIER_RESPONSE');
999 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1000 FND_MSG_PUB.Count_And_Get
1001 (
1002 p_count => x_msg_count,
1003 p_data => x_msg_data,
1004 p_encoded => FND_API.G_FALSE
1005 );
1006
1007
1008 END UPDATE_CARRIER_RESPONSE;
1009
1010
1011
1012
1013 -- ------------------------------------------------------------------------------- --
1014 -- --
1015 -- NAME: RAISE_TENDER_ACCEPT --
1016 -- TYPE: PROCEDURE --
1017 -- PARAMETERS (IN): itemtype VARCHAR2 --
1018 -- itemkey VARCHAR2 (wf block instance label) --
1019 -- actid NUMBER --
1020 -- funcmode VARCHAR --
1021 --
1022 -- PARAMETERS (OUT): resultout VARCHAR2 --
1023 -- PARAMETERS (IN OUT): none --
1024 -- RETURN: none --
1025 -- DESCRIPTION: This procedure will trigger the approve notification --
1026 -- Load Tendering Process.
1027 -- --
1028 -- CHANGE CONTROL LOG --
1029 -- ------------------ --
1030 -- --
1031 -- DATE VERSION BY BUG DESCRIPTION --
1032 -- ---------- ------- -------- ------- --------------------------------------- --
1033 -- 2003 11.5.9 SAMUTHUK Created --
1034 -- --
1035 -- ------------------------------------------------------------------------------- --
1036
1037 PROCEDURE RAISE_TENDER_ACCEPT( itemtype in varchar2,
1038 itemkey in varchar2,
1039 actid in number,
1040 funcmode in varchar2,
1041 resultout out NOCOPY varchar2) IS
1042
1043
1044 l_return_status VARCHAR2(30000);
1045 l_tender_status VARCHAR2(2000);
1046 l_msg_count NUMBER;
1047 l_msg_data VARCHAR2(30000);
1048 l_tender_id NUMBER;
1049 l_shipper_name VARCHAR2(2000);
1050 l_carrier_name VARCHAR2(2000);
1051 l_contact_name VARCHAR2(10000);
1052 l_contact_perf VARCHAR2(2000);
1053 l_carrier_response VARCHAR2(2000);
1054 l_initial_pickup_date DATE;
1055 l_ultimate_dropoff_date DATE;
1056 l_carrier_reference_number VARCHAR2(30);
1057 l_vehicle_number VARCHAR2(35);
1058 l_operator VARCHAR2(150);
1059
1060 l_trip_id NUMBER;
1061 l_trip_name VARCHAR2(30000);
1062 l_api_name VARCHAR2(30) := 'RAISE_TENDER_ACCEPT';
1063 l_msg_string VARCHAR2(30000);
1064
1065
1066 l_action_out_rec FTE_ACTION_OUT_REC;
1067 trip_action_param FTE_TRIP_ACTION_PARAM_REC;
1068 l_tender_attr_rec FTE_TENDER_ATTR_REC;
1069 l_responseSource VARCHAR2(30);
1070
1071 l_api_name VARCHAR2(30) := 'RAISE_TENDER_ACCEPT';
1072 l_api_version CONSTANT NUMBER := 1.0;
1073 l_debug_on CONSTANT BOOLEAN := WSH_DEBUG_SV.is_debug_enabled;
1074 l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.' || G_PKG_NAME || '.' || 'RAISE_TENDER_ACCEPT';
1075
1076
1077 BEGIN
1078
1079 IF (funcmode = 'RUN') THEN
1080
1081
1082 IF l_debug_on THEN
1083 WSH_DEBUG_SV.push(l_module_name);
1084 END IF;
1085
1086 l_responseSource := wf_engine.GetItemAttrText(itemtype,itemkey,'RESPONSE_SOURCE');
1087
1088 IF l_debug_on THEN
1089 WSH_DEBUG_SV.logmsg(l_module_name,' Response source ' || l_responseSource,
1090 WSH_DEBUG_SV.C_PROC_LEVEL);
1091 END IF;
1092
1093
1094 IF (l_responseSource = FTE_TENDER_PVT.S_SOURCE_CP OR
1095 l_responseSource = FTE_TENDER_PVT.S_SOURCE_XML)
1096 THEN
1097 RETURN; -- We already have response.
1098 END IF;
1099
1100 l_tender_id := wf_engine.GetItemAttrNumber(itemtype,itemkey,'TENDER_ID');
1101 l_trip_id := wf_engine.GetItemAttrNumber(itemtype,itemkey,'TRIP_ID');
1102
1103 -- TBD
1104 l_trip_id := l_tender_id;
1105 l_carrier_response := wf_engine.GetItemAttrText(itemtype,itemkey,'CARRIER_RESPONSE');
1106
1107 l_tender_status := FTE_TENDER_PVT.S_ACCEPTED;
1108 l_initial_pickup_date := wf_engine.GetItemAttrDate(itemtype,itemkey,'INITIAL_PICKUP_DATE');
1109 l_ultimate_dropoff_date := wf_engine.GetItemAttrDate(itemtype,itemkey,'ULTIMATE_DROPOFF_DATE');
1110 --l_carrier_reference_number := wf_engine.GetItemAttrText(itemtype,itemkey,'Z_CARRIER_REFERENCE_NUMBER');
1111 l_vehicle_number := wf_engine.GetItemAttrText(itemtype,itemkey,'VEHICLE_NUMBER');
1112 l_operator := wf_engine.GetItemAttrText(itemtype,itemkey,'VEHICLE_OPERATOR');
1113
1114 l_tender_attr_rec := FTE_TENDER_ATTR_REC(
1115 l_trip_id, -- TripId
1116 null, -- Trip Name
1117 l_tender_id, --tender id
1118 FTE_TENDER_PVT.S_ACCEPTED, -- status
1119 null,-- car_contact_id
1120 null, -- car contact name
1121 null, -- auto_accept
1122 null, -- auto tender
1123 null, -- ship wait time
1124 null, -- ship time uom
1125 'FTETERES', -- wf name
1126 'TENDER_ACCEPT_PROCESS', -- wf process name
1127 itemkey, --wf item key
1128 l_carrier_response,
1129 l_initial_pickup_date,
1130 l_ultimate_dropoff_date,
1131 l_vehicle_number,
1132 l_operator,
1133 l_carrier_reference_number,
1134 null,
1135 FTE_TENDER_PVT.S_SOURCE_WL,
1136 null);
1137
1138 trip_action_param := FTE_TRIP_ACTION_PARAM_REC(null,FTE_TENDER_PVT.S_ACCEPTED,
1139 null,null,null,null,null,null,
1140 null,null,null,null,null,null,
1141 null,null);
1142
1143
1144 FTE_MLS_WRAPPER.Trip_Action (p_api_version_number => 1.0,
1145 p_init_msg_list => FND_API.G_TRUE,
1146 x_return_status => l_return_status,
1147 x_msg_count => l_msg_count,
1148 x_msg_data => l_msg_data,
1149 x_action_out_rec => l_action_out_rec,
1150 p_trip_info_rec => l_tender_attr_rec,
1151 p_action_prms => trip_action_param);
1152
1153
1154
1155 IF ( (l_return_status = 'E') OR (l_return_status = 'U') ) THEN
1156 l_msg_string := FTE_MLS_UTIL.GET_MESSAGE(l_msg_count,l_msg_data);
1157 wf_core.TOKEN('ERROR_STRING',l_msg_string);
1158 wf_core.RAISE('FTE_WF_ERROR_MESSAGE');
1159 END IF;
1160
1161 IF l_debug_on THEN
1162 WSH_DEBUG_SV.pop(l_module_name);
1163 END IF;
1164
1165
1166 -- Now raise the event
1167
1168 resultout := 'COMPLETE:Y';
1169 return;
1170
1171
1172 END IF;
1173
1174
1175 --
1176 -- CANCEL mode
1177 --
1178 IF (funcmode = 'CANCEL') THEN
1179 resultout := 'COMPLETE:';
1180 return;
1181 END IF;
1182
1183 IF (funcmode = 'TIMEOUT') THEN
1184 resultout := 'COMPLETE:';
1185 return;
1186 END IF;
1187
1188 EXCEPTION
1189 WHEN OTHERS THEN
1190 wf_core.context('FTE_TENDER_WF',
1191 'RAISE_TENDER_ACCEPT',
1192 itemtype,
1193 itemkey,
1194 actid,
1195 funcmode,
1196 itemtype);
1197 RAISE;
1198
1199 END RAISE_TENDER_ACCEPT;
1200
1201
1202
1203 -- ------------------------------------------------------------------------------- --
1204 -- --
1205 -- NAME: RAISE_TENDER_REJECT --
1206 -- TYPE: PROCEDURE --
1207 -- PARAMETERS (IN): itemtype VARCHAR2 --
1208 -- itemkey VARCHAR2 (wf block instance label) --
1209 -- actid NUMBER --
1210 -- funcmode VARCHAR --
1211 --
1212 -- PARAMETERS (OUT): resultout VARCHAR2 --
1213 -- PARAMETERS (IN OUT): none --
1214 -- RETURN: none --
1215 -- DESCRIPTION: This procedure will trigger the reject notification --
1216 -- Process.
1217 -- --
1218 -- CHANGE CONTROL LOG --
1219 -- ------------------ --
1220 -- --
1221 -- DATE VERSION BY BUG DESCRIPTION --
1222 -- ---------- ------- -------- ------- --------------------------------------- --
1223 -- 2003 11.5.9 SAMUTHUK Created --
1224 -- --
1225 -- ------------------------------------------------------------------------------- --
1226
1227 PROCEDURE RAISE_TENDER_REJECT( itemtype in varchar2,
1228 itemkey in varchar2,
1229 actid in number,
1230 funcmode in varchar2,
1231 resultout out NOCOPY varchar2) IS
1232
1233
1234 l_return_status VARCHAR2(30000);
1235 l_tender_status VARCHAR2(2000);
1236 l_msg_count NUMBER;
1237 l_msg_data VARCHAR2(30000);
1238 l_tender_id NUMBER;
1239 l_shipper_name VARCHAR2(2000);
1240 l_carrier_name VARCHAR2(2000);
1241 l_contact_name VARCHAR2(10000);
1242 l_contact_perf VARCHAR2(2000);
1243 l_msg_string VARCHAR2(30000);
1244 l_carrier_response VARCHAR2(2000);
1245 l_trip_id NUMBER;
1246 l_trip_name VARCHAR2(30000);
1247
1248 l_api_name VARCHAR2(30) := 'RAISE_TENDER_REJECT';
1249
1250 l_action_out_rec FTE_ACTION_OUT_REC;
1251 trip_action_param FTE_TRIP_ACTION_PARAM_REC;
1252 l_tender_attr_rec FTE_TENDER_ATTR_REC;
1253
1254 l_responseSource VARCHAR2(30);
1255
1256 BEGIN
1257
1258 IF (funcmode = 'RUN') THEN
1259
1260 l_responseSource := wf_engine.GetItemAttrText(itemtype,itemkey,'RESPONSE_SOURCE');
1261
1262 IF (l_responseSource = FTE_TENDER_PVT.S_SOURCE_CP OR
1263 l_responseSource = FTE_TENDER_PVT.S_SOURCE_XML)
1264 THEN
1265 RETURN; -- We already have response.
1266 END IF;
1267
1268
1269 l_tender_id := wf_engine.GetItemAttrNumber(itemtype,itemkey,'TENDER_ID');
1270 l_trip_id := wf_engine.GetItemAttrNumber(itemtype,itemkey,'TRIP_ID');
1271
1272 -- TBD
1273 l_trip_id := l_tender_id;
1274 l_carrier_response := wf_engine.GetItemAttrText(itemtype,itemkey,'CARRIER_RESPONSE');
1275
1276 l_tender_attr_rec := FTE_TENDER_ATTR_REC(
1277 l_trip_id, -- TripId
1278 null, -- Trip Name
1279 l_tender_id, --tender id
1280 FTE_TENDER_PVT.S_REJECTED, -- status
1281 null,-- car_contact_id
1282 null, -- car contact name
1283 null, -- auto_accept
1284 null, -- auto tender
1285 null, -- ship wait time
1286 null, -- ship time uom
1287 'FTETERES', -- wf name
1288 'TENDER_REJECT_PROCESS', -- wf process name
1289 itemkey, --wf item key
1290 l_carrier_response,
1291 null,
1292 null,
1293 null,
1294 null,
1295 null,
1296 null,
1297 FTE_TENDER_PVT.S_SOURCE_WL,
1298 null);
1299
1300 trip_action_param := FTE_TRIP_ACTION_PARAM_REC(null,FTE_TENDER_PVT.S_REJECTED,
1301 204,null,null,null,null,null,
1302 null,null,null,null,null,null,
1303 null,null);
1304
1305
1306 FTE_MLS_WRAPPER.Trip_Action (p_api_version_number => 1.0,
1307 p_init_msg_list => FND_API.G_TRUE,
1308 x_return_status => l_return_status,
1309 x_msg_count => l_msg_count,
1310 x_msg_data => l_msg_data,
1311 x_action_out_rec => l_action_out_rec,
1312 p_trip_info_rec => l_tender_attr_rec,
1313 p_action_prms => trip_action_param);
1314
1315
1316
1317 IF ( (l_return_status = 'E') OR (l_return_status = 'U') ) THEN
1318 l_msg_string := FTE_MLS_UTIL.GET_MESSAGE(l_msg_count,l_msg_data);
1319 wf_core.TOKEN('ERROR_STRING',l_msg_string);
1320 wf_core.RAISE('FTE_WF_ERROR_MESSAGE');
1321 END IF;
1322
1323 -- Now raise the event
1324
1325 resultout := 'COMPLETE:Y';
1326
1327 END IF;
1328
1329 --
1330 -- CANCEL mode
1331 --
1332 IF (funcmode = 'CANCEL') THEN
1333 resultout := 'COMPLETE:N';
1334 return;
1335 END IF;
1336
1337 IF (funcmode = 'TIMEOUT') THEN
1338 resultout := 'COMPLETE:';
1339 return;
1340 END IF;
1341
1342 EXCEPTION
1343 WHEN OTHERS THEN
1344 wf_core.context('FTE_TENDER_WF',
1345 'RAISE_TENDER_REJECT',
1346 itemtype,
1347 itemkey,
1348 actid,
1349 funcmode,
1350 itemtype);
1351 RAISE;
1352
1353 END RAISE_TENDER_REJECT;
1354
1355
1356 -- ------------------------------------------------------------------------------- --
1357 -- --
1358 -- NAME: FINALIZE_UPDATE_TENDER --
1359 -- TYPE: PROCEDURE --
1360 -- PARAMETERS (IN): itemtype VARCHAR2 --
1361 -- itemkey VARCHAR2 (wf block instance label) --
1362 -- actid NUMBER --
1363 -- funcmode VARCHAR --
1364 --
1365 -- PARAMETERS (OUT): resultout VARCHAR2 --
1366 -- PARAMETERS (IN OUT): none --
1367 -- RETURN: none --
1368 -- DESCRIPTION: This procedure will initialize the attributes for update --
1369 -- Load Tendering Process.
1370 -- --
1371 -- CHANGE CONTROL LOG --
1372 -- ------------------ --
1373 -- --
1374 -- DATE VERSION BY BUG DESCRIPTION --
1375 -- ---------- ------- -------- ------- --------------------------------------- --
1376 -- 2002 11.5.8 HBHAGAVA Created --
1377 -- 2003 11.5.9 SAMUTHUK Modified --
1378 -- ------------------------------------------------------------------------------- --
1379
1380 PROCEDURE FINALIZE_UPDATE_TENDER(itemtype in varchar2,
1381 itemkey in varchar2,
1382 actid in number,
1383 funcmode in varchar2,
1384 resultout out NOCOPY varchar2) IS
1385
1386 l_result_code VARCHAR2(10);
1387 l_tender_id NUMBER;
1388 l_api_name VARCHAR2(30) := 'FINALIZE_UPDATE_TENDER';
1389
1390
1391 l_return_status VARCHAR2(30000);
1392 l_msg_count NUMBER;
1393 l_msg_data VARCHAR2(30000);
1394 l_msg_string VARCHAR2(30000);
1395
1396 l_userId NUMBER;
1397 l_respId NUMBER;
1398 l_respAppId NUMBER;
1399
1400
1401 BEGIN
1402
1403 --
1404 -- RUN mode - normal process execution
1405 --
1406 IF (funcmode = 'RUN') THEN
1407 -- These values should be retrived from database
1408
1409 -- Initialize all the attributes to send notification to carrier
1410 -- based on the tender id
1411 -- Get the tender id first
1412
1413
1414 wf_engine.SetItemAttrText(itemtype => itemtype,
1415 itemkey => itemkey,
1416 aname => 'TENDER_STATUS',
1417 avalue => FTE_TENDER_PVT.S_SHIPPER_UPDATED);
1418
1419 wf_engine.SetItemOwner(itemtype,itemkey,wf_engine.GetItemAttrText(itemtype,
1420 itemkey,'SHIPPER_NAME'));
1421
1422
1423 l_userId := wf_engine.GetItemAttrNumber('FTETEREQ',itemKey,'USER_ID');
1424 l_respAppId := wf_engine.GetItemAttrNumber('FTETEREQ',itemKey, 'RESP_APPL_ID');
1425 l_respId := wf_engine.GetItemAttrNumber('FTETEREQ',itemKey,'RESPONSIBILITY_ID');
1426
1427
1428 IF(l_respAppId IS NULL OR l_respId IS NULL) THEN
1429 RAISE no_data_found;
1430 ELSE
1431 FND_GLOBAL.Apps_Initialize(l_userId, l_respId, l_respAppId);
1432 END IF;
1433
1434
1435 resultout := 'COMPLETE:Y';
1436 return;
1437
1438
1439 END IF; --- func mode
1440
1441
1442 --
1443 -- CANCEL mode
1444 --
1445 IF (funcmode = 'CANCEL') THEN
1446 resultout := 'COMPLETE:';
1447 return;
1448 END IF;
1449
1450 IF (funcmode = 'TIMEOUT') THEN
1451 resultout := 'COMPLETE:';
1452 return;
1453 END IF;
1454
1455 EXCEPTION
1456 WHEN OTHERS THEN
1457 wf_core.context('FTE_TENDER_WF',
1458 'FINALIZE_UPDATE_TENDER',
1459 itemtype,
1460 itemkey,
1461 actid,
1462 funcmode);
1463 RAISE;
1464
1465 END FINALIZE_UPDATE_TENDER;
1466
1467
1468
1469 -- ------------------------------------------------------------------------------- --
1470 -- --
1471 -- NAME: FINALIZE_TENDER_REQUEST --
1472 -- TYPE: PROCEDURE --
1473 -- PARAMETERS (IN): itemtype VARCHAR2 --
1474 -- itemkey VARCHAR2 (wf block instance label) --
1475 -- actid NUMBER --
1476 -- funcmode VARCHAR --
1477 --
1478 -- PARAMETERS (OUT): resultout VARCHAR2 --
1479 -- PARAMETERS (IN OUT): none --
1480 -- RETURN: none --
1481 -- DESCRIPTION: This procedure will finalize the load tender process --
1482 -- This process will check if tender request block is notified--
1483 -- if it is then it will release the block and set's up --
1484 -- other paramters to indicate that load tender request is --
1485 -- completed --
1486 -- If process is blocked by reminder then the block and --
1487 -- release and other paramters are set to indicate that load --
1488 -- tender request is completed --
1489 -- --
1490 -- CHANGE CONTROL LOG --
1491 -- ------------------ --
1492 -- --
1493 -- DATE VERSION BY BUG DESCRIPTION --
1494 -- ---------- ------- -------- ------- ----------------------------------------
1495 -- 2002 11.5.8 HBHAGAVA Created --
1496 -- 2003 11.5.9 SAMUTHUK Modified -- --
1497 -- ----------------------------------------------------------------------------------
1498
1499 PROCEDURE FINALIZE_TENDER_REQUEST(itemtype in varchar2,
1500 itemkey in varchar2,
1501 actid in number,
1502 funcmode in varchar2,
1503 resultout out NOCOPY varchar2) IS
1504
1505 l_api_name CONSTANT VARCHAR2(30) := 'FINALIZE_TENDER_REQUEST';
1506 l_api_version CONSTANT NUMBER := 1.0;
1507 l_debug_on CONSTANT BOOLEAN := WSH_DEBUG_SV.is_debug_enabled;
1508 l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.' || G_PKG_NAME || '.' || l_api_name;
1509
1510 l_trip_id NUMBER;
1511 l_tender_id NUMBER;
1512 l_tender_action VARCHAR2(30);
1513
1514 l_return_status VARCHAR2(30000);
1515 l_msg_count NUMBER;
1516 l_msg_data VARCHAR2(30000);
1517 l_msg_string VARCHAR2(30000);
1518 l_ship_org_name VARCHAR2(100);
1519 l_ship_info VARCHAR2(32767);
1520
1521
1522 l_userId NUMBER;
1523 l_respId NUMBER;
1524 l_respAppId NUMBER;
1525
1526
1527 BEGIN
1528
1529 --
1530 -- RUN mode - normal process execution
1531 --
1532
1533 IF (funcmode = 'RUN') THEN
1534
1535
1536
1537 IF l_debug_on THEN
1538 WSH_DEBUG_SV.push(l_api_name);
1539
1540 END IF;
1541
1542
1543 l_tender_action := wf_engine.getItemAttrText(itemtype, itemkey, 'TENDER_ACTION');
1544
1545 IF l_debug_on THEN
1546 WSH_DEBUG_SV.logmsg(l_api_name,l_tender_action,WSH_DEBUG_SV.C_PROC_LEVEL);
1547 END IF;
1548
1549
1550 l_tender_id := wf_engine.GetItemAttrNumber(itemtype,itemkey,'TENDER_ID');
1551 l_trip_id := wf_engine.GetItemAttrNumber(itemtype,itemkey,'TRIP_ID');
1552 l_trip_id := l_tender_id;
1553
1554
1555 wf_engine.SetItemAttrText(itemtype => itemtype,
1556 itemkey => itemkey,
1557 aname => 'TENDER_TEXT_ID',
1558 avalue => to_char(l_tender_id));
1559
1560
1561 wf_engine.SetItemAttrText(itemtype => itemtype,
1562 itemkey => itemkey,
1563 aname => 'TENDER_STATUS',
1564 avalue => l_tender_action);
1565
1566 wf_engine.SetItemAttrText(itemtype => itemtype,
1567 itemkey => itemkey,
1568 aname => 'SHIPPER_NAME',
1569 avalue => wf_engine.GetItemAttrText(
1570 'FTETEREQ', itemkey, 'SHIPPER_NAME'));
1571
1572 wf_engine.SetItemOwner(itemtype,itemkey,wf_engine.GetItemAttrText(
1573 'FTETEREQ', itemkey, 'SHIPPER_NAME'));
1574
1575 --Addded by sharvisa for R12
1576 wf_engine.SetItemAttrText(itemtype => itemtype,
1577 itemkey => itemkey,
1578 aname => 'MODE_OF_TRANSPORT',
1579 avalue => wf_engine.GetItemAttrText(
1580 'FTETEREQ', itemkey, 'MODE_OF_TRANSPORT'));
1581
1582
1583 wf_engine.SetItemAttrText(itemtype => itemtype,
1584 itemkey => itemkey,
1585 aname => 'SHIPPER_WAIT_TIME',
1586 avalue => wf_engine.GetItemAttrText(
1587 'FTETEREQ', itemkey, 'SHIPPER_WAIT_TIME'));
1588
1589
1590 wf_engine.SetItemAttrDate(itemtype => itemtype,
1591 itemkey => itemkey,
1592 aname => 'TENDERED_DATE',
1593 avalue => wf_engine.GetItemAttrDate(
1594 'FTETEREQ', itemkey, 'TENDERED_DATE'));
1595
1596 wf_engine.SetItemAttrText(itemtype => itemtype,
1597 itemkey => itemkey,
1598 aname => 'MBOL_NUM',
1599 avalue => wf_engine.GetItemAttrText(
1600 'FTETEREQ', itemkey, 'MBOL_NUM'));
1601
1602
1603 -- Rel12 Coding ....
1604 wf_engine.SetItemAttrNumber(itemtype => itemtype,
1605 itemkey => itemkey,
1606 aname => 'CARRIER_SITE_ID',
1607 avalue => wf_engine.GetItemAttrNumber(
1608 'FTETEREQ', itemkey, 'CARRIER_SITE_ID'));
1609
1610 wf_engine.SetItemAttrText(itemtype => itemtype,
1611 itemkey => itemkey,
1612 aname => 'CONTACT_PERFORMER',
1613 avalue => wf_engine.GetItemAttrText(
1614 'FTETEREQ', itemkey, 'CONTACT_PERFORMER'));
1615
1616 wf_engine.SetItemAttrText(itemtype => itemtype,
1617 itemkey => itemkey,
1618 aname => 'SHIPPING_ORG_NAME',
1619 avalue => wf_engine.GetItemAttrText(
1620 'FTETEREQ', itemkey, 'SHIPPING_ORG_NAME'));
1621
1622
1623 wf_engine.SetItemAttrText(itemtype => itemtype,
1624 itemkey => itemkey,
1625 aname => 'CARRIER_NAME',
1626 avalue => wf_engine.GetItemAttrText(
1627 'FTETEREQ', itemkey, 'CARRIER_NAME'));
1628
1629 -- Initialize fnd context.
1630
1631 l_userId := wf_engine.GetItemAttrNumber('FTETEREQ',itemKey,'USER_ID');
1632 l_respAppId := wf_engine.GetItemAttrNumber('FTETEREQ',itemKey, 'RESP_APPL_ID');
1633 l_respId := wf_engine.GetItemAttrNumber('FTETEREQ',itemKey,'RESPONSIBILITY_ID');
1634
1635 IF l_debug_on THEN
1636 WSH_DEBUG_SV.logmsg(l_module_name,' Initializing responsiblity information ' ||
1637 l_userId,WSH_DEBUG_SV.C_PROC_LEVEL);
1638 END IF;
1639
1640
1641 IF(l_respAppId IS NULL OR l_respId IS NULL) THEN
1642 RAISE no_data_found;
1643 ELSE
1644 FND_GLOBAL.Apps_Initialize(l_userId, l_respId, l_respAppId);
1645 END IF;
1646
1647
1648 IF l_debug_on THEN
1649 WSH_DEBUG_SV.pop(l_api_name);
1650 END IF;
1651
1652 resultout := 'COMPLETE:Y';
1653 return;
1654
1655 END IF; --- func mode
1656
1657
1658 --
1659 -- CANCEL mode
1660 --
1661 IF (funcmode = 'CANCEL') THEN
1662 resultout := 'COMPLETE:';
1663 return;
1664 END IF;
1665
1666 IF (funcmode = 'TIMEOUT') THEN
1667 resultout := 'COMPLETE:';
1668 return;
1669 END IF;
1670
1671 EXCEPTION
1672 WHEN OTHERS THEN
1673 wf_core.context('FTE_TENDER_WF',
1674 'FINALIZE_TENDER_REQUEST',
1675 itemtype,
1676 itemkey,
1677 actid,
1678 funcmode,
1679 itemtype);
1680 RAISE;
1681
1682 END FINALIZE_TENDER_REQUEST;
1683
1684 --*******************************************************
1685 -- ------------------------------------------------------------------------------- --
1686 -- --
1687 -- NAME: IS_TENDER_MODIFIED --
1688 -- TYPE: PROCEDURE --
1689 -- PARAMETERS (IN): itemtype VARCHAR2 --
1690 -- itemkey VARCHAR2 (wf block instance label) --
1691 -- actid NUMBER --
1692 -- funcmode VARCHAR2 --
1693 --
1694 -- PARAMETERS (OUT): resultout VARCHAR2 --
1695 -- PARAMETERS (IN OUT): none --
1696 -- RETURN: none --
1697 -- DESCRIPTION: This procedure calculated the remaining block time and --
1698 -- assigns it to tender block. The calculation is based on --
1699 -- the numder of loops and reminder time.
1700 -- This is a temp procedure. Should be changed with actual database check
1701 -- -- This is a temp procedure. Should be changed with actual database check
1702 -- and see if the tender status of delivery is modified
1703 --
1704 -- --
1705 -- CHANGE CONTROL LOG --
1706 -- ------------------ --
1707 -- --
1708 -- DATE VERSION BY BUG DESCRIPTION --
1709 -- ---------- ------- -------- ------- --------------------------------------- --
1710 -- 2002 11.5.8 HBHAGAVA Created --
1711 -- --
1712 -- ------------------------------------------------------------------------------- --
1713
1714
1715
1716 PROCEDURE IS_TENDER_MODIFIED(itemtype in varchar2,
1717 itemkey in varchar2,
1718 actid in number,
1719 funcmode in varchar2,
1720 resultout out NOCOPY varchar2) IS
1721
1722 l_is_modified VARCHAR2(10);
1723
1724 BEGIN
1725
1726 --
1727 -- RUN mode - normal process execution
1728 --
1729 IF (funcmode = 'RUN') THEN
1730 --
1731 -- Call API to get the value of the wf status
1732 --
1733
1734 l_is_modified := wf_engine.GetItemAttrText(itemtype,itemkey,'TENDER_MODIFIED');
1735
1736 IF (l_is_modified = 'YES') THEN
1737 resultout := 'COMPLETE:Y';
1738 return;
1739 ELSE
1740 resultout := 'COMPLETE:N';
1741 return;
1742 END IF;
1743
1744 END IF; --- func mode
1745
1746
1747 EXCEPTION
1748 WHEN OTHERS THEN
1749 wf_core.context('FTE_TENDER_WF',
1750 'IS_TENDER_MODIFIED',
1751 itemtype,
1752 itemkey,
1753 actid,
1754 funcmode,
1755 itemtype);
1756 RAISE;
1757
1758 END IS_TENDER_MODIFIED;
1759
1760 --*******************************************************
1761 -- ------------------------------------------------------------------------------- --
1762 -- --
1763 -- NAME: IS_REMINDER_ENABLED --
1764 -- TYPE: PROCEDURE --
1765 -- PARAMETERS (IN): itemtype VARCHAR2 --
1766 -- itemkey VARCHAR2 (wf block instance label) --
1767 -- actid NUMBER --
1768 -- funcmode VARCHAR2 --
1769 --
1770 -- PARAMETERS (OUT): resultout VARCHAR2 --
1771 -- PARAMETERS (IN OUT): none --
1772 -- RETURN: none --
1773 -- DESCRIPTION: This procedure calculated the remaining block time and --
1774 -- assigns it to tender block. The calculation is based on --
1775 -- the numder of loops and reminder time.
1776 -- This is a temp procedure. Should be changed with actual database check
1777 --
1778 -- --
1779 -- CHANGE CONTROL LOG --
1780 -- ------------------ --
1781 -- --
1782 -- DATE VERSION BY BUG DESCRIPTION --
1783 -- ---------- ------- -------- ------- --------------------------------------- --
1784 -- 2002 11.5.8 HBHAGAVA Created --
1785 -- --
1786 -- ------------------------------------------------------------------------------- --
1787
1788 --
1789
1790 PROCEDURE IS_REMINDER_ENABLED(itemtype in varchar2,
1791 itemkey in varchar2,
1792 actid in number,
1793 funcmode in varchar2,
1794 resultout out NOCOPY varchar2) IS
1795
1796 BEGIN
1797
1798 --
1799 -- RUN mode - normal process execution
1800 --
1801 IF (funcmode = 'RUN') THEN
1802 --
1803 -- Call API to get the value of the wf status
1804 --
1805 -- hbhagava 09/02/2002
1806 -- This value will remain 'COMPLETE:N' since we are not going to
1807 -- implement reminder functionality in Pack I
1808 resultout := 'COMPLETE:N';
1809 return;
1810
1811 END IF; --- func mode
1812
1813
1814 EXCEPTION
1815 WHEN OTHERS THEN
1816 wf_core.context('FTE_TENDER_WF',
1817 'IS_REMINDER_ENABLED',
1818 itemtype,
1819 itemkey,
1820 actid,
1821 funcmode,
1822 itemtype);
1823 RAISE;
1824
1825 END IS_REMINDER_ENABLED;
1826
1827 --*******************************************************
1828 -- ------------------------------------------------------------------------------- --
1829 -- --
1830 -- NAME: CALCULATE_WAIT_TIME --
1831 -- TYPE: PROCEDURE --
1832 -- PARAMETERS (IN): itemtype VARCHAR2 --
1833 -- itemkey VARCHAR2 (wf block instance label) --
1834 -- actid NUMBER --
1835 -- funcmode VARCHAR2 --
1836 --
1837 -- PARAMETERS (OUT): resultout VARCHAR2 --
1838 -- PARAMETERS (IN OUT): none --
1839 -- RETURN: none --
1840 -- DESCRIPTION: This procedure calculated the remaining block time and --
1841 -- assigns it to tender block. The calculation is based on --
1842 -- the numder of loops and reminder time.
1843 -- --
1844 -- CHANGE CONTROL LOG --
1845 -- ------------------ --
1846 -- --
1847 -- DATE VERSION BY BUG DESCRIPTION --
1848 -- ---------- ------- -------- ------- --------------------------------------- --
1849 -- 2002 11.5.8 HBHAGAVA Created --
1850 -- --
1851 -- ------------------------------------------------------------------------------- --
1852
1853 PROCEDURE CALCULATE_WAIT_TIME(itemtype in varchar2,
1854 itemkey in varchar2,
1855 actid in number,
1856 funcmode in varchar2,
1857 resultout out NOCOPY varchar2) IS
1858
1859 l_loop_count NUMBER;
1860 l_max_reminder NUMBER;
1861 l_reminder_time NUMBER;
1862 l_total_reminder_time NUMBER;
1863 l_tender_block_time NUMBER;
1864
1865
1866 BEGIN
1867
1868 --
1869 -- RUN mode - normal process execution
1870 --
1871 IF (funcmode = 'RUN') THEN
1872 --
1873 -- Call API to get the value of the wf status
1874 --
1875
1876 -- Calculate the tender block time / wait time based on
1877 -- number of loops and reminder time. If tender wait time is less than
1878 -- total reminder time then there is no use of reminder
1879 -- just proceed to tender block. So return N
1880
1881 l_loop_count := FTE_WF_UTIL.GET_ATTRIBUTE_NUMBER(
1882 p_item_type => itemtype,
1883 p_item_key => itemkey,
1884 p_aname => 'CYCLE_COUNTER'
1885 );
1886
1887 l_max_reminder := wf_engine.GetItemAttrNumber(
1888 itemtype => itemtype,
1889 itemkey => itemkey,
1890 aname => 'MAX_NO_REMINDER'
1891 );
1892
1893 l_reminder_time := wf_engine.GetItemAttrNumber(
1894 itemtype => itemtype,
1895 itemkey => itemkey,
1896 aname => 'REMINDER_WAIT_TIME'
1897 );
1898
1899 -- wait time to expire load tender request.
1900 l_tender_block_time := wf_engine.GetItemAttrNumber(
1901 itemtype => itemtype,
1902 itemkey => itemkey,
1903 aname => 'SHIPPER_CUTOFF_TIME'
1904 );
1905
1906 -- Left out time for reminders
1907 --l_total_reminder_time := (l_max_reminder - 1) * l_reminder_time;
1908
1909
1910 IF (l_tender_block_time <= l_reminder_time) THEN
1911 resultout := 'COMPLETE:Y';
1912 ELSE
1913 resultout := 'COMPLETE:N';
1914 -- since we going to block the activity for reminder, remove that time from
1915 -- tender block time
1916 l_tender_block_time := l_tender_block_time - l_reminder_time;
1917 END IF;
1918
1919 wf_engine.SetItemAttrNumber(
1920 itemtype => itemtype,
1921 itemkey => itemkey,
1922 aname => 'SHIPPER_CUTOFF_TIME',
1923 avalue => l_tender_block_time
1924 );
1925
1926 return;
1927
1928 END IF; --- func mode
1929
1930
1931 EXCEPTION
1932 WHEN OTHERS THEN
1933 wf_core.context('FTE_TENDER_WF',
1934 'CALCULATE_WAIT_TIME',
1935 itemtype,
1936 itemkey,
1937 actid,
1938 funcmode,
1939 itemtype);
1940 RAISE;
1941
1942 END CALCULATE_WAIT_TIME;
1943
1944
1945 --*******************************************************
1946 -- ------------------------------------------------------------------------------- --
1947 -- --
1948 -- NAME: FINALIZE_NORESPONSE --
1949 -- TYPE: PROCEDURE --
1950 -- PARAMETERS (IN): itemtype VARCHAR2 --
1951 -- itemkey VARCHAR2 (wf block instance label) --
1952 -- actid NUMBER --
1953 -- funcmode VARCHAR2 --
1954 --
1955 -- PARAMETERS (OUT): resultout VARCHAR2 --
1956 -- PARAMETERS (IN OUT): none --
1957 -- RETURN: none --
1958 -- DESCRIPTION: This procedure will finalize the no response tender request--
1959 -- --
1960 -- CHANGE CONTROL LOG --
1961 -- ------------------ --
1962 -- --
1963 -- DATE VERSION BY BUG DESCRIPTION --
1964 -- ---------- ------- -------- ------- --------------------------------------- --
1965 -- 2002 11.5.8 HBHAGAVA Created --
1966 -- --
1967 -- ------------------------------------------------------------------------------- --
1968
1969 PROCEDURE FINALIZE_NORESPONSE(itemtype in varchar2,
1970 itemkey in varchar2,
1971 actid in number,
1972 funcmode in varchar2,
1973 resultout out NOCOPY varchar2) IS
1974
1975 l_api_name CONSTANT VARCHAR2(30) := 'FINALIZE_NORESPONSE';
1976 l_api_version CONSTANT NUMBER := 1.0;
1977
1978 l_tender_id NUMBER;
1979 l_return_status VARCHAR2(1);
1980 l_msg_count NUMBER;
1981 l_msg_data VARCHAR2(30000);
1982
1983 l_msg_string VARCHAR2(30000);
1984 l_trip_id NUMBER;
1985 l_trip_name VARCHAR2(30000);
1986 l_msg_token VARCHAR2(1000);
1987
1988 l_action_out_rec FTE_ACTION_OUT_REC;
1989 trip_action_param FTE_TRIP_ACTION_PARAM_REC;
1990 l_tender_attr_rec FTE_TENDER_ATTR_REC;
1991
1992 l_debug_on CONSTANT BOOLEAN := WSH_DEBUG_SV.is_debug_enabled;
1993 l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.' || G_PKG_NAME || '.' || l_api_name;
1994
1995 l_userId NUMBER;
1996 l_respId NUMBER;
1997 l_respAppId NUMBER;
1998
1999
2000 BEGIN
2001
2002 --wsh_debug_sv.push(l_api_name,'>>FTE: Enterning Procedure');
2003
2004 --
2005 -- RUN mode - normal process execution
2006 --
2007 IF (funcmode = 'RUN') THEN
2008 --
2009
2010 l_tender_id := wf_engine.GetItemAttrNumber(itemtype,itemkey,'TENDER_ID');
2011 l_trip_id := wf_engine.GetItemAttrNumber(itemtype,itemkey,'TRIP_ID');
2012 l_trip_id := l_tender_id;
2013
2014 IF l_debug_on THEN
2015 WSH_DEBUG_SV.logmsg(l_module_name,' Trip Id ' || l_trip_id,WSH_DEBUG_SV.C_PROC_LEVEL);
2016 WSH_DEBUG_SV.logmsg(l_module_name,' Tender Id' || l_tender_id,WSH_DEBUG_SV.C_PROC_LEVEL);
2017 END IF;
2018
2019
2020 wf_engine.SetItemAttrText(itemtype => itemtype,
2021 itemkey => itemkey,
2022 aname => 'TENDER_STATUS',
2023 avalue => FTE_TENDER_PVT.S_NORESPONSE);
2024
2025 -- Rel12 Coding ....
2026 wf_engine.SetItemAttrNumber(itemtype => itemtype,
2027 itemkey => itemkey,
2028 aname => 'CARRIER_SITE_ID',
2029 avalue => wf_engine.GetItemAttrNumber(
2030 'FTETEREQ', itemkey, 'CARRIER_SITE_ID'));
2031
2032 -- INITIALIZE_ APPS CONTEXT
2033
2034 l_userId := wf_engine.GetItemAttrNumber('FTETEREQ',itemKey,'USER_ID');
2035 l_respAppId := wf_engine.GetItemAttrNumber('FTETEREQ',itemKey, 'RESP_APPL_ID');
2036 l_respId := wf_engine.GetItemAttrNumber('FTETEREQ',itemKey,'RESPONSIBILITY_ID');
2037
2038 IF l_debug_on THEN
2039 WSH_DEBUG_SV.logmsg(l_module_name,' Initializing responsiblity information ' ||
2040 l_userId,WSH_DEBUG_SV.C_PROC_LEVEL);
2041 END IF;
2042
2043
2044 IF(l_respAppId IS NULL OR l_respId IS NULL) THEN
2045 RAISE no_data_found;
2046 ELSE
2047 FND_GLOBAL.Apps_Initialize(l_userId, l_respId, l_respAppId);
2048 END IF;
2049
2050
2051
2052 l_tender_attr_rec := FTE_TENDER_ATTR_REC(
2053 l_trip_id, -- TripId
2054 null, -- Trip Name
2055 l_tender_id, --tender id
2056 FTE_TENDER_PVT.S_NORESPONSE, -- status
2057 null,-- car_contact_id
2058 null, -- car contact name
2059 null, -- auto_accept
2060 null, -- auto tender
2061 null, -- ship wait time
2062 null, -- ship time uom
2063 'FTETEREQ', -- wf name
2064 'TENDER_NORESPONSE_PROCESS', -- wf process name
2065 itemkey, --wf item key
2066 null,null,null,null,null,null,null,null,null);
2067
2068 trip_action_param := FTE_TRIP_ACTION_PARAM_REC(null,FTE_TENDER_PVT.S_NORESPONSE,
2069 null,null,null,null,null,null,
2070 null,null,null,null,null,null,
2071 null,null);
2072
2073 IF l_debug_on THEN
2074 WSH_DEBUG_SV.logmsg(l_module_name,' Before Calling FTE_MLS_WRAPPER.TRIP_ACTION ' || l_trip_id,
2075 WSH_DEBUG_SV.C_PROC_LEVEL);
2076 END IF;
2077
2078
2079 FTE_MLS_WRAPPER.Trip_Action (p_api_version_number => 1.0,
2080 p_init_msg_list => FND_API.G_TRUE,
2081 x_return_status => l_return_status,
2082 x_msg_count => l_msg_count,
2083 x_msg_data => l_msg_data,
2084 x_action_out_rec => l_action_out_rec,
2085 p_trip_info_rec => l_tender_attr_rec,
2086 p_action_prms => trip_action_param);
2087
2088
2089
2090 IF ( (l_return_status = 'E') OR (l_return_status = 'U') ) THEN
2091 l_msg_string := FTE_MLS_UTIL.GET_MESSAGE(l_msg_count,l_msg_data);
2092 wf_core.TOKEN('ERROR_STRING',l_msg_string);
2093 wf_core.RAISE('FTE_WF_ERROR_MESSAGE');
2094 END IF;
2095
2096 IF l_debug_on THEN
2097 WSH_DEBUG_SV.logmsg(l_module_name,' l_return_status after
2098 FTE_MLS_WRAPPER.TRIP_ACTION ' ||
2099 l_return_status,WSH_DEBUG_SV.C_PROC_LEVEL);
2100 END IF;
2101
2102
2103 resultout := 'COMPLETE:Y';
2104
2105 IF l_debug_on THEN
2106 wsh_debug_sv.pop(l_api_name);
2107 END IF;
2108 --wsh_debug_sv.pop(l_api_name);
2109
2110 return;
2111
2112 END IF; --- func mode
2113
2114
2115 --
2116 -- CANCEL mode
2117 --
2118 IF (funcmode = 'CANCEL') THEN
2119 resultout := 'COMPLETE:';
2120 --wsh_debug_sv.pop(l_api_name);
2121
2122 return;
2123 END IF;
2124
2125 IF (funcmode = 'TIMEOUT') THEN
2126 resultout := 'COMPLETE:';
2127 --wsh_debug_sv.pop(l_api_name);
2128 return;
2129 END IF;
2130
2131
2132 EXCEPTION
2133 WHEN OTHERS THEN
2134 wf_core.context('FTE_TENDER_WF',
2135 'FINALIZE_NORESPONSE',
2136 itemtype,
2137 itemkey,
2138 actid,
2139 funcmode,
2140 itemtype);
2141 RAISE;
2142
2143 END FINALIZE_NORESPONSE;
2144
2145
2146 PROCEDURE FINALIZE_AUTO_ACCEPT(itemtype in varchar2,
2147 itemkey in varchar2,
2148 actid in number,
2149 funcmode in varchar2,
2150 resultout out NOCOPY varchar2) IS
2151
2152 l_api_name CONSTANT VARCHAR2(30) := 'FINALIZE_AUTO_ACCEPT';
2153 l_api_version CONSTANT NUMBER := 1.0;
2154
2155 l_tender_id NUMBER;
2156 l_return_status VARCHAR2(1);
2157 l_msg_count NUMBER;
2158 l_msg_data VARCHAR2(30000);
2159 l_trip_id NUMBER;
2160 l_trip_name VARCHAR2(30000);
2161 l_msg_string VARCHAR2(30000);
2162
2163
2164 p_trip_info_tab WSH_TRIPS_PVT.Trip_Attr_Tbl_Type;
2165 p_trip_info WSH_TRIPS_PVT.Trip_Rec_Type;
2166 p_trip_in_rec WSH_TRIPS_GRP.TripInRecType;
2167 x_out_tab WSH_TRIPS_GRP.trip_Out_tab_type;
2168
2169 l_number_of_warnings NUMBER;
2170 l_number_of_errors NUMBER;
2171
2172
2173 l_action_out_rec FTE_ACTION_OUT_REC;
2174 trip_action_param FTE_TRIP_ACTION_PARAM_REC;
2175 l_tender_attr_rec FTE_TENDER_ATTR_REC;
2176
2177 l_debug_on CONSTANT BOOLEAN := WSH_DEBUG_SV.is_debug_enabled;
2178 l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.' || G_PKG_NAME || '.' || l_api_name;
2179
2180
2181 BEGIN
2182
2183
2184 --wsh_debug_sv.push(l_api_name,'>>FTE: Enterning Procedure');
2185
2186 --
2187 -- RUN mode - normal process execution
2188 --
2189 IF (funcmode = 'RUN') THEN
2190 --
2191
2192
2193 wf_engine.SetItemAttrText(itemtype => itemtype,
2194 itemkey => itemkey,
2195 aname => 'TENDER_STATUS',
2196 avalue => FTE_TENDER_PVT.S_AUTO_ACCEPTED);
2197
2198 -- Rel12 Coding ....
2199 wf_engine.SetItemAttrNumber(itemtype => itemtype,
2200 itemkey => itemkey,
2201 aname => 'CARRIER_SITE_ID',
2202 avalue => wf_engine.GetItemAttrNumber(
2203 'FTETEREQ', itemkey, 'CARRIER_SITE_ID'));
2204
2205 wf_engine.SetItemAttrText(itemtype => itemtype,
2206 itemkey => itemkey,
2207 aname => 'CARRIER_NAME',
2208 avalue => wf_engine.GetItemAttrText(
2209 'FTETEREQ', itemkey, 'CARRIER_NAME'));
2210
2211 l_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2212 l_number_of_warnings := 0;
2213 l_number_of_errors := 0;
2214
2215 l_tender_id := wf_engine.GetItemAttrNumber(itemtype,itemkey,'TENDER_ID');
2216 l_trip_id := wf_engine.GetItemAttrNumber(itemtype,itemkey,'TRIP_ID');
2217 l_trip_id := l_tender_id;
2218
2219
2220 l_tender_attr_rec := FTE_TENDER_ATTR_REC(
2221 l_trip_id, -- TripId
2222 null, -- Trip Name
2223 l_tender_id, --tender id
2224 FTE_TENDER_PVT.S_AUTO_ACCEPTED, -- status
2225 null,-- car_contact_id
2226 null, -- car contact name
2227 null, -- auto_accept
2228 null, -- auto tender
2229 null, -- ship wait time
2230 null, -- ship time uom
2231 'FTETEREQ', -- wf name
2232 'AUTO_ACCEPT_PROCESS', -- wf process name
2233 itemkey, --wf item key
2234 null,null,null,null,null,null,null,null,null);
2235
2236 trip_action_param := FTE_TRIP_ACTION_PARAM_REC(null,FTE_TENDER_PVT.S_AUTO_ACCEPTED,
2237 204,null,null,null,null,null,
2238 null,null,null,null,null,null,
2239 null,null);
2240
2241
2242 FTE_MLS_WRAPPER.Trip_Action (p_api_version_number => 1.0,
2243 p_init_msg_list => FND_API.G_TRUE,
2244 x_return_status => l_return_status,
2245 x_msg_count => l_msg_count,
2246 x_msg_data => l_msg_data,
2247 x_action_out_rec => l_action_out_rec,
2248 p_trip_info_rec => l_tender_attr_rec,
2249 p_action_prms => trip_action_param);
2250
2251
2252
2253 IF l_debug_on THEN
2254 WSH_DEBUG_SV.logmsg(l_module_name,' l_return_status after
2255 FTE_MLS_WRAPPER.TRIP_ACTION ' ||
2256 l_return_status,WSH_DEBUG_SV.C_PROC_LEVEL);
2257 END IF;
2258
2259
2260 wsh_util_core.api_post_call(
2261 p_return_status =>l_return_status,
2262 x_num_warnings =>l_number_of_warnings,
2263 x_num_errors =>l_number_of_errors,
2264 p_msg_data =>l_msg_data);
2265
2266 IF ( (l_return_status = 'E')
2267 OR (l_return_status = 'U') )
2268 THEN
2269 l_msg_string := 'FINALIZE_AUTO_ACCEPT :-> Tender Id ='||to_char(l_tender_id)||' :'||FTE_MLS_UTIL.GET_MESSAGE(l_msg_count,l_msg_data);
2270 wf_core.TOKEN('ERROR_STRING',l_msg_string);
2271 wf_core.RAISE('FTE_WF_ERROR_MESSAGE');
2272 END IF;
2273
2274 resultout := 'COMPLETE:Y';
2275 --wsh_debug_sv.pop(l_api_name);
2276 return;
2277
2278 END IF; --- func mode
2279
2280
2281 --
2282 -- CANCEL mode
2283 --
2284 IF (funcmode = 'CANCEL') THEN
2285 resultout := 'COMPLETE:';
2286 --wsh_debug_sv.pop(l_api_name);
2287
2288 return;
2289 END IF;
2290
2291 IF (funcmode = 'TIMEOUT') THEN
2292 resultout := 'COMPLETE:';
2293 --wsh_debug_sv.pop(l_api_name);
2294 return;
2295 END IF;
2296
2297
2298 EXCEPTION
2299 WHEN OTHERS THEN
2300 wf_core.context('FTE_TENDER_WF',
2301 'FINALIZE_AUTO_ACCEPT',
2302 itemtype,
2303 itemkey,
2304 actid,
2305 funcmode,
2306 itemtype);
2307 RAISE;
2308
2309 END FINALIZE_AUTO_ACCEPT;
2310
2311
2312
2313 PROCEDURE IS_AUTO_ACCEPT_ENABLED(itemtype in varchar2,
2314 itemkey in varchar2,
2315 actid in number,
2316 funcmode in varchar2,
2317 resultout out NOCOPY varchar2) IS
2318
2319 l_api_name CONSTANT VARCHAR2(30) := 'IS_AUTO_ACCEPT_ENABLED';
2320 l_api_version CONSTANT NUMBER := 1.0;
2321
2322 l_autoAccept VARCHAR2(10);
2323
2324 BEGIN
2325
2326
2327 --wsh_debug_sv.push(l_api_name,'>>FTE: Enterning Procedure');
2328
2329 --
2330 -- RUN mode - normal process execution
2331 --
2332 IF (funcmode = 'RUN') THEN
2333 --
2334
2335 l_autoAccept := wf_engine.GetItemAttrText(itemtype,itemkey,'AUTO_ACCEPT');
2336
2337 IF (l_autoAccept = 'Y')
2338 THEN
2339 resultout := 'COMPLETE:Y';
2340 ELSE
2341 resultout := 'COMPLETE:N';
2342 END IF;
2343
2344 return;
2345
2346 END IF; --- func mode
2347
2348
2349 --
2350 -- CANCEL mode
2351 --
2352 IF (funcmode = 'CANCEL') THEN
2353 resultout := 'COMPLETE:';
2354 --wsh_debug_sv.pop(l_api_name);
2355
2356 return;
2357 END IF;
2358
2359 IF (funcmode = 'TIMEOUT') THEN
2360 resultout := 'COMPLETE:';
2361 --wsh_debug_sv.pop(l_api_name);
2362 return;
2363 END IF;
2364
2365
2366 EXCEPTION
2367 WHEN OTHERS THEN
2368 wf_core.context('FTE_TENDER_WF',
2369 'IS_AUTO_ACCEPT_ENABLED',
2370 itemtype,
2371 itemkey,
2372 actid,
2373 funcmode,
2374 itemtype);
2375 RAISE;
2376
2377 END IS_AUTO_ACCEPT_ENABLED;
2378
2379 -- Get response by date, tendered date, handling info pickup date from GET_TENDER_INFO
2380 -- Reminder time
2381
2382 PROCEDURE GET_TENDER_INFO(
2383 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
2384 p_tender_id IN NUMBER,
2385 x_return_status OUT NOCOPY VARCHAR2,
2386 x_msg_count OUT NOCOPY NUMBER,
2387 x_msg_data OUT NOCOPY VARCHAR2,
2388 x_response_by OUT NOCOPY DATE,
2389 x_shipper_wait_time OUT NOCOPY VARCHAR2,
2390 x_remaining_time OUT NOCOPY VARCHAR2,
2391 x_routing_inst OUT NOCOPY VARCHAR2,
2392 x_tendered_date OUT NOCOPY DATE,
2393 x_carrier_remarks OUT NOCOPY VARCHAR2,
2394 x_mode_of_transport OUT NOCOPY VARCHAR2) IS
2395 --{
2396
2397 l_api_name CONSTANT VARCHAR2(30) := 'GET_TENDER_INFO';
2398 l_api_version CONSTANT NUMBER := 1.0;
2399 l_debug_on CONSTANT BOOLEAN := WSH_DEBUG_SV.is_debug_enabled;
2400 l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.' || G_PKG_NAME || '.' || 'GET_TENDER_INFO';
2401
2402 l_wait_time NUMBER;
2403 l_wait_time_uom VARCHAR(10);
2404 l_temp_value NUMBER;
2405 l_rem_days NUMBER;
2406 l_rem_hr NUMBER;
2407 l_rem_min NUMBER;
2408 l_wait_time_days NUMBER;
2409 l_respond_by_text DATE;
2410
2411 --}
2412
2413 CURSOR get_tender_info_cur (c_tender_id NUMBER)
2414 IS
2415 SELECT routing_instructions, load_tendered_time,
2416 shipper_wait_time,wait_time_uom, carrier_response, mode_of_transport
2417 FROM wsh_trips WHERE load_tender_number = c_tender_id;
2418
2419 --{
2420 BEGIN
2421 --
2422 -- Standard Start of API savepoint
2423 SAVEPOINT GET_TENDER_INFO_PUB;
2424 --
2425 --
2426 -- Initialize message list if p_init_msg_list is set to TRUE.
2427 --
2428 --
2429 IF FND_API.to_Boolean( p_init_msg_list )
2430 THEN
2431 FND_MSG_PUB.initialize;
2432 END IF;
2433 --
2434 IF l_debug_on THEN
2435 wsh_debug_sv.push(l_module_name);
2436 END IF;
2437
2438 --
2439 -- Initialize API return status to success
2440 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2441 x_msg_count := 0;
2442 x_msg_data := 0;
2443
2444 --
2445 --GET TRIP INFO
2446 --
2447 FOR get_tender_info_rec IN get_tender_info_cur(p_tender_id)
2448 LOOP
2449 x_tendered_date := get_tender_info_rec.load_tendered_time;
2450 x_routing_inst := get_tender_info_rec.routing_instructions;
2451 x_carrier_remarks := get_tender_info_rec.carrier_response;
2452 l_wait_time := get_tender_info_rec.shipper_wait_time;
2453 l_wait_time_uom := get_tender_info_rec.wait_time_uom;
2454 x_mode_of_transport := get_tender_info_rec.mode_of_transport;
2455 END LOOP;
2456 -- END OF GET TRIP INFO
2457
2458
2459 IF get_tender_info_cur%ISOPEN THEN
2460 CLOSE get_tender_info_cur;
2461 END IF;
2462
2463 -- Bug 2917554: Use FTE conversion method
2464 --l_wait_time_days := INV_CONVERT.inv_um_convert(null,
2465 -- 5,l_wait_time,
2466 -- l_wait_time_uom,
2467 -- 'DAY',NULL,NULL);
2468
2469 ------------------------------------------------------------------
2470 -- Samuthuk [ workflow Notifications std ]
2471 ------------------------------------------------------------------
2472 x_shipper_wait_time := to_char(l_wait_time)||':'||l_wait_time_uom;
2473 ------------------------------------------------------------------
2474
2475
2476 l_wait_time_days := FTE_MLS_UTIL.FTE_UOM_CONV(l_wait_time, l_wait_time_uom,'DAY');
2477
2478 --l_wait_time_days := FTE_MLS_UTIL.FTE_UOM_CONV(l_wait_time, l_wait_time_uom,'MIN');
2479
2480 IF (l_wait_time_days = -9999)
2481 THEN
2482 FND_MESSAGE.SET_NAME('FTE','FTE_INVLD_SHIP_WAITTIME');
2483 FND_MESSAGE.SET_TOKEN('WAIT_TIME',l_wait_time_days);
2484 FND_MESSAGE.SET_TOKEN('WAIT_TIME_UOM','DAY');
2485 WSH_UTIL_CORE.ADD_MESSAGE(WSH_UTIL_CORE.G_RET_STS_ERROR);
2486 RAISE FND_API.G_EXC_ERROR;
2487 END IF;
2488
2489 x_response_by := (x_tendered_date+l_wait_time_days);
2490 --l_respond_by_text := x_tendered_date + l_wait_time_days/(60*24);
2491
2492 IF l_debug_on
2493 THEN
2494 WSH_DEBUG_SV.logmsg(l_module_name,'Nhi!TenderedDate:'||x_tendered_date,WSH_DEBUG_SV.C_PROC_LEVEL);
2495 WSH_DEBUG_SV.logmsg(l_module_name,'Nhi!WaitTime:'||l_wait_time||l_wait_time_uom,WSH_DEBUG_SV.C_PROC_LEVEL);
2496 --WSH_DEBUG_SV.logmsg(l_module_name,'Nhi!ResponseByText:'||l_respond_by_text,WSH_DEBUG_SV.C_PROC_LEVEL);
2497 END IF;
2498
2499 l_temp_value := trunc(to_number(x_response_by-SYSDATE),2);
2500
2501 -- Bug 2917554: Use FTE conversion method
2502 --l_temp_value := INV_CONVERT.inv_um_convert(null,2,
2503 -- l_temp_value,
2504 -- 'DAY','MIN',
2505 -- NULL,NULL);
2506 l_temp_value := FTE_MLS_UTIL.FTE_UOM_CONV(l_temp_value, 'DAY','MIN');
2507
2508 IF (l_temp_value = -9999)
2509 THEN
2510 FND_MESSAGE.SET_NAME('FTE','FTE_INVLD_SHIP_WAITTIME');
2511 FND_MESSAGE.SET_TOKEN('WAIT_TIME',l_temp_value);
2512 FND_MESSAGE.SET_TOKEN('WAIT_TIME_UOM','MIN');
2513 WSH_UTIL_CORE.ADD_MESSAGE(WSH_UTIL_CORE.G_RET_STS_ERROR);
2514 RAISE FND_API.G_EXC_ERROR;
2515 END IF;
2516
2517 l_rem_days := trunc(l_temp_value/(60*24),0);
2518 l_temp_value := l_temp_value - (l_rem_days*60*24);
2519 l_rem_hr := trunc(l_temp_value/(60),0);
2520 l_rem_min := trunc(l_temp_value - (l_rem_hr*60),0);
2521 x_remaining_time := l_rem_days || 'd:' || l_rem_hr
2522 || 'h:' || l_rem_min || 'm';
2523
2524 IF l_debug_on
2525 THEN
2526 WSH_DEBUG_SV.logmsg(l_module_name,'Nhi!Remainingtime:'||x_remaining_time,WSH_DEBUG_SV.C_PROC_LEVEL);
2527 WSH_DEBUG_SV.logmsg(l_module_name,'Nhi!SYSDATE:'||SYSDATE,WSH_DEBUG_SV.C_PROC_LEVEL);
2528 END IF;
2529
2530 --
2531 --
2532 --
2533 -- Standard call to get message count and if count is 1,get message info.
2534 --
2535 FND_MSG_PUB.Count_And_Get
2536 (
2537 p_count => x_msg_count,
2538 p_data => x_msg_data,
2539 p_encoded => FND_API.G_FALSE
2540 );
2541 --
2542 --
2543 IF l_debug_on THEN
2544 WSH_DEBUG_SV.pop(l_module_name);
2545 END IF;
2546
2547 --}
2548 EXCEPTION
2549 --{
2550 WHEN FND_API.G_EXC_ERROR THEN
2551 ROLLBACK TO GET_TENDER_INFO_PUB;
2552 x_return_status := FND_API.G_RET_STS_ERROR ;
2553 FND_MSG_PUB.Count_And_Get
2554 (
2555 p_count => x_msg_count,
2556 p_data => x_msg_data,
2557 p_encoded => FND_API.G_FALSE
2558 );
2559 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2560 ROLLBACK TO GET_TENDER_INFO_PUB;
2561 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2562 FND_MSG_PUB.Count_And_Get
2563 (
2564 p_count => x_msg_count,
2565 p_data => x_msg_data,
2566 p_encoded => FND_API.G_FALSE
2567 );
2568 WHEN OTHERS THEN
2569 ROLLBACK TO GET_TENDER_INFO_PUB;
2570 wsh_util_core.default_handler('FTE_TENDER_WF.GET_TENDER_INFO');
2571 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2572 FND_MSG_PUB.Count_And_Get
2573 (
2574 p_count => x_msg_count,
2575 p_data => x_msg_data,
2576 p_encoded => FND_API.G_FALSE
2577 );
2578
2579 --}
2580
2581 END GET_TENDER_INFO;
2582
2583
2584 -- Rel 12 HBHAGAVA
2585 PROCEDURE GET_NOTIF_TYPE(itemtype in varchar2,
2586 itemkey in varchar2,
2587 actid in number,
2588 funcmode in varchar2,
2589 resultout out NOCOPY varchar2) IS
2590 --{
2591
2592
2593 l_api_name CONSTANT VARCHAR2(30) := 'GET_NOTIF_TYPE';
2594 l_api_version CONSTANT NUMBER := 1.0;
2595
2596 l_trip_id NUMBER;
2597 l_notif_type VARCHAR2(10);
2598
2599 -- Cursor Definition
2600 --{
2601 --}
2602
2603 BEGIN
2604
2605
2606 --wsh_debug_sv.push(l_api_name,'>>FTE: Enterning Procedure');
2607
2608 --
2609 -- RUN mode - normal process execution
2610 --
2611 IF (funcmode = 'RUN') THEN
2612 --
2613 IF (itemtype ='FTETEUPD') THEN
2614 l_notif_type := wf_engine.GetItemAttrText('FTETEUPD',itemkey,'NOTIF_TYPE');
2615 ELSE
2616 l_notif_type := wf_engine.GetItemAttrText('FTETEREQ',itemkey,'NOTIF_TYPE');
2617 END IF ;
2618
2619 IF (l_notif_type IS NULL OR l_notif_type = 'EMAIL')
2620 THEN
2621 resultout := 'COMPLETE:EMAIL';
2622 ELSE
2623 resultout := 'COMPLETE:XML';
2624 END IF;
2625
2626 return;
2627
2628 END IF; --- func mode
2629
2630
2631 --
2632 -- CANCEL mode
2633 --
2634 IF (funcmode = 'CANCEL') THEN
2635 resultout := 'COMPLETE:';
2636 --wsh_debug_sv.pop(l_api_name);
2637
2638 return;
2639 END IF;
2640
2641 IF (funcmode = 'TIMEOUT') THEN
2642 resultout := 'COMPLETE:';
2643 --wsh_debug_sv.pop(l_api_name);
2644 return;
2645 END IF;
2646 --}
2647
2648 EXCEPTION
2649 WHEN OTHERS THEN
2650 wf_core.context('FTE_TENDER_WF',
2651 'GET_NOTIF_TYPE',
2652 itemtype,
2653 itemkey,
2654 actid,
2655 funcmode,
2656 itemtype);
2657 RAISE;
2658
2659 END GET_NOTIF_TYPE;
2660
2661 --{
2662
2663 -- Rel 12 HBHAGAVA
2664 PROCEDURE LOG_HISTORY(itemtype in varchar2,
2665 itemkey in varchar2,
2666 actid in number,
2667 funcmode in varchar2,
2668 resultout out NOCOPY varchar2) IS
2669 --{
2670
2671
2672 l_api_name CONSTANT VARCHAR2(30) := 'LOG_HISTORY';
2673 l_api_version CONSTANT NUMBER := 1.0;
2674
2675 l_trip_id NUMBER;
2676
2677 l_debug_on CONSTANT BOOLEAN := WSH_DEBUG_SV.is_debug_enabled;
2678 l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.' || G_PKG_NAME || '.' || l_api_name;
2679
2680 l_tender_status VARCHAR2(30);
2681 l_rank_id NUMBER;
2682 l_rank_version NUMBER;
2683 l_shipper_name VARCHAR2(100);
2684 l_shipper_id NUMBER;
2685 l_carrier_contact_name VARCHAR2(100);
2686 l_carrier_contact_id NUMBER;
2687
2688
2689 l_delivery_leg_activity_rec FTE_DELIVERY_ACTIVITY.delivery_leg_activity_rec;
2690
2691
2692 l_return_status VARCHAR2(30000);
2693 l_msg_count NUMBER;
2694 l_msg_data VARCHAR2(30000);
2695 l_msg_string VARCHAR2(30000);
2696
2697
2698 BEGIN
2699
2700
2701 --
2702 -- RUN mode - normal process execution
2703 --
2704 IF (funcmode = 'RUN') THEN
2705 --
2706
2707
2708 l_trip_id := wf_engine.GetItemAttrNumber(itemtype,itemkey,'TRIP_ID');
2709 l_tender_status := wf_engine.GetItemAttrText(itemtype,itemkey,'TENDER_STATUS');
2710 l_rank_id := wf_engine.GetItemAttrNumber(itemtype,itemkey,'RANK_ID');
2711 l_rank_version := wf_engine.GetItemAttrNumber(itemtype,itemkey,'RANK_VERSION');
2712
2713 IF l_debug_on
2714 THEN
2715 WSH_DEBUG_SV.logmsg(l_module_name,' TEnder status ' || l_tender_status,WSH_DEBUG_SV.C_PROC_LEVEL);
2716 END IF;
2717
2718
2719 IF (l_tender_status = FTE_TENDER_PVT.S_TENDERED OR
2720 l_tender_status = FTE_TENDER_PVT.S_SHIPPER_CANCELLED OR
2721 l_tender_status = FTE_TENDER_PVT.S_AUTO_ACCEPTED OR
2722 l_tender_status = FTE_TENDER_PVT.S_NORESPONSE OR
2723 l_tender_status = FTE_TENDER_PVT.S_SHIPPER_UPDATED)
2724 THEN
2725 l_delivery_leg_activity_rec.action_by :=
2726 wf_engine.GetItemAttrNumber(itemtype,itemkey,'SHIPPER_USER_ID');
2727 l_delivery_leg_activity_rec.action_by_name :=
2728 wf_engine.GetItemAttrText(itemtype,itemkey,'SHIPPER_NAME');
2729 ELSE
2730 l_delivery_leg_activity_rec.action_by :=
2731 wf_engine.GetItemAttrNumber(itemtype,itemkey,'CONTACT_USER_ID');
2732 l_delivery_leg_activity_rec.action_by_name :=
2733 wf_engine.GetItemAttrText(itemtype,itemkey,'CONTACT_USER_NAME');
2734 l_delivery_leg_activity_rec.remarks :=
2735 wf_engine.GetItemAttrText(itemtype,itemkey,'CARRIER_REMARKS');
2736
2737 END IF;
2738
2739 -- For shipper update item key is different. so we have to get the original
2740 -- tender wf item key.
2741 IF (l_tender_status = FTE_TENDER_PVT.S_SHIPPER_UPDATED)
2742 THEN
2743 SELECT WF_ITEM_KEY INTO l_delivery_leg_activity_rec.wf_item_key
2744 FROM WSH_TRIPS
2745 WHERE TRIP_ID = l_trip_id;
2746 ELSE
2747 l_delivery_leg_activity_rec.wf_item_key := itemkey;
2748 END IF;
2749
2750
2751 l_delivery_leg_activity_rec.trip_id := l_trip_id;
2752 l_delivery_leg_activity_rec.activity_type := l_tender_status;
2753 l_delivery_leg_activity_rec.rank_id := l_rank_id;
2754 l_delivery_leg_activity_rec.rank_version := l_rank_version;
2755
2756
2757 FTE_DELIVERY_ACTIVITY.ADD_HISTORY(
2758 p_init_msg_list => FND_API.G_FALSE,
2759 p_trip_id => l_trip_id,
2760 p_delivery_leg_activity_rec => l_delivery_leg_activity_rec,
2761 x_return_status => l_return_status,
2762 x_msg_count => l_msg_count,
2763 x_msg_data => l_msg_data);
2764
2765
2766 IF ( (l_return_status = 'E')
2767 OR (l_return_status = 'U') )
2768 THEN
2769 l_msg_string := 'LOG_HISTORY :-> Tender Id ='||to_char(l_trip_id)||' :'||
2770 FTE_MLS_UTIL.GET_MESSAGE(l_msg_count,l_msg_data);
2771 wf_core.TOKEN('ERROR_STRING',l_msg_string);
2772 wf_core.RAISE('FTE_WF_ERROR_MESSAGE');
2773 END IF;
2774
2775
2776
2777 return;
2778
2779 END IF; --- func mode
2780
2781
2782 --
2783 -- CANCEL mode
2784 --
2785 IF (funcmode = 'CANCEL') THEN
2786 resultout := 'COMPLETE:';
2787 --wsh_debug_sv.pop(l_api_name);
2788
2789 return;
2790 END IF;
2791
2792 IF (funcmode = 'TIMEOUT') THEN
2793 resultout := 'COMPLETE:';
2794 --wsh_debug_sv.pop(l_api_name);
2795 return;
2796 END IF;
2797 --}
2798
2799 EXCEPTION
2800 WHEN OTHERS THEN
2801 wf_core.context('FTE_TENDER_WF',
2802 l_api_name,
2803 itemtype,
2804 itemkey,
2805 actid,
2806 funcmode,
2807 itemtype);
2808 RAISE;
2809
2810 END LOG_HISTORY;
2811
2812 --}
2813
2814 --{
2815
2816 -- Rel 12 HBHAGAVA
2817 PROCEDURE RAISE_XML_OUTBOUND(itemtype in varchar2,
2818 itemkey in varchar2,
2819 actid in number,
2820 funcmode in varchar2,
2821 resultout out NOCOPY varchar2) IS
2822 --{
2823
2824
2825 l_api_name CONSTANT VARCHAR2(30) := 'RAISE_XML_OUTBOUND';
2826 l_api_version CONSTANT NUMBER := 1.0;
2827
2828 l_trip_id NUMBER;
2829
2830 l_debug_on CONSTANT BOOLEAN := WSH_DEBUG_SV.is_debug_enabled;
2831 l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.' || G_PKG_NAME || '.' || l_api_name;
2832
2833
2834 BEGIN
2835
2836
2837 --
2838 -- RUN mode - normal process execution
2839 --
2840 IF (funcmode = 'RUN') THEN
2841 --
2842 resultout := 'COMPLETE:Y';
2843 return;
2844
2845 END IF; --- func mode
2846
2847
2848 --
2849 -- CANCEL mode
2850 --
2851 IF (funcmode = 'CANCEL') THEN
2852 resultout := 'COMPLETE:';
2853
2854 return;
2855 END IF;
2856
2857 IF (funcmode = 'TIMEOUT') THEN
2858 resultout := 'COMPLETE:';
2859 --wsh_debug_sv.pop(l_api_name);
2860 return;
2861 END IF;
2862 --}
2863
2864 EXCEPTION
2865 WHEN OTHERS THEN
2866 wf_core.context('FTE_TENDER_WF',
2867 l_api_name,
2868 itemtype,
2869 itemkey,
2870 actid,
2871 funcmode,
2872 itemtype);
2873 RAISE;
2874
2875 END RAISE_XML_OUTBOUND;
2876
2877 -- Rel 12 HBHAGAVA
2878 PROCEDURE EXPAND_RANK_LIST(itemtype in varchar2,
2879 itemkey in varchar2,
2880 actid in number,
2881 funcmode in varchar2,
2882 resultout out NOCOPY varchar2) IS
2883 --{
2884
2885
2886 l_api_name CONSTANT VARCHAR2(30) := 'EXPAND_RANK_LIST';
2887 l_api_version CONSTANT NUMBER := 1.0;
2888
2889 l_trip_id NUMBER;
2890
2891 l_debug_on CONSTANT BOOLEAN := WSH_DEBUG_SV.is_debug_enabled;
2892 l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.' || G_PKG_NAME || '.' || l_api_name;
2893
2894 p_FTE_SS_ATTR_REC FTE_SS_ATTR_REC;
2895
2896 l_LIST_CREATE_TYPE VARCHAR2(10);
2897 l_SS_RATE_SORT_TAB FTE_CARRIER_RANK_LIST_PVT.carrier_rank_list_tbl_type;
2898 l_appendFlag VARCHAR2(1);
2899 l_ruleId NUMBER;
2900
2901 l_return_status VARCHAR2(1);
2902 l_msg_count NUMBER;
2903 l_msg_data VARCHAR2(30000);
2904 l_number_of_warnings NUMBER;
2905 l_number_of_errors NUMBER;
2906 l_msg_string VARCHAR2(30000);
2907
2908 l_RATING_REQUEST_ID NUMBER;
2909
2910 l_arr_date DATE;
2911 l_dept_date DATE;
2912 l_first_stop_id NUMBER;
2913 l_last_stop_id NUMBER;
2914 l_first_stop_loc_id NUMBER;
2915 l_last_stop_loc_id NUMBER;
2916
2917
2918 BEGIN
2919
2920
2921 --
2922 -- RUN mode - normal process execution
2923 --
2924 IF (funcmode = 'RUN') THEN
2925 --
2926
2927 l_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2928 l_number_of_warnings := 0;
2929 l_number_of_errors := 0;
2930
2931
2932 l_trip_id := wf_engine.GetItemAttrNumber(itemtype,itemkey,'TRIP_ID');
2933
2934 SELECT APPEND_FLAG,ROUTING_RULE_ID INTO l_appendFlag, l_ruleId
2935 FROM WSH_TRIPS
2936 WHERE TRIP_ID = l_trip_id;
2937
2938 -- We have to query first stop, last stop information to populate on to
2939 -- trip
2940 FTE_MLS_UTIL.GET_FIRST_LAST_STOP_INFO(x_return_status => l_return_status,
2941 x_arrival_date => l_arr_date,
2942 x_departure_date => l_dept_date,
2943 x_first_stop_id => l_first_stop_id,
2944 x_last_stop_id => l_last_stop_id,
2945 x_first_stop_loc_id => l_first_stop_loc_id,
2946 x_last_stop_loc_id => l_last_stop_loc_id,
2947 p_trip_id => l_trip_id);
2948
2949 wsh_util_core.api_post_call(
2950 p_return_status =>l_return_status,
2951 x_num_warnings =>l_number_of_warnings,
2952 x_num_errors =>l_number_of_errors,
2953 p_msg_data =>l_msg_data);
2954
2955 IF ( (l_return_status = 'E')
2956 OR (l_return_status = 'U') )
2957 THEN
2958 l_msg_string := 'FTE_MSL_UTIL.GET_FIRST_LAST_STOP_INFO :-> Tender Id ='||to_char(l_trip_id)||
2959 ' :'||FTE_MLS_UTIL.GET_MESSAGE(l_msg_count,l_msg_data);
2960 wf_core.TOKEN('ERROR_STRING',l_msg_string);
2961 wf_core.RAISE('FTE_WF_ERROR_MESSAGE');
2962 END IF;
2963
2964
2965 p_FTE_SS_ATTR_REC := FTE_SS_ATTR_REC(null,
2966 null,
2967 l_trip_id,
2968 l_arr_date,l_arr_date,
2969 l_dept_date, -- DEP_DATE_TO
2970 l_dept_date,
2971 l_first_stop_loc_id,
2972 l_last_stop_loc_id,
2973 l_first_stop_id,
2974 l_last_stop_id,
2975 l_first_stop_loc_id, -- PICK_UP_STOP_LOCATION_ID,
2976 l_last_stop_loc_id,
2977 null,null,null,--Carrier
2978 null,null,
2979 null,null,l_ruleId,--rule id
2980 null,l_appendFlag);
2981
2982
2983 FTE_SS_INTERFACE.SEARCH_SERVICES(
2984 P_INIT_MSG_LIST => FND_API.G_FALSE,
2985 P_API_VERSION_NUMBER => 1.0,
2986 P_COMMIT => FND_API.G_FALSE,
2987 P_CALLER => FTE_SS_INTERFACE.S_CALLER_WF,
2988 P_FTE_SS_ATTR_REC => p_FTE_SS_ATTR_REC,
2989 X_RATING_REQUEST_ID => l_rating_request_id,
2990 X_LIST_CREATE_TYPE => l_LIST_CREATE_TYPE,
2991 X_SS_RATE_SORT_TAB => l_SS_RATE_SORT_TAB,
2992 x_return_status => l_return_status,
2993 x_msg_count => l_msg_count,
2994 x_msg_data => l_msg_data);
2995
2996 wsh_util_core.api_post_call(
2997 p_return_status =>l_return_status,
2998 x_num_warnings =>l_number_of_warnings,
2999 x_num_errors =>l_number_of_errors,
3000 p_msg_data =>l_msg_data);
3001
3002 IF ( (l_return_status = 'E')
3003 OR (l_return_status = 'U') )
3004 THEN
3005 l_msg_string := 'FTE_SS_INTERFACE.SEARCH_SERIVCE :-> Tender Id ='||to_char(l_trip_id)||
3006 ' :'||FTE_MLS_UTIL.GET_MESSAGE(l_msg_count,l_msg_data);
3007 wf_core.TOKEN('ERROR_STRING',l_msg_string);
3008 wf_core.RAISE('FTE_WF_ERROR_MESSAGE');
3009 END IF;
3010
3011
3012 -- wf_core.TOKEN('ERROR_STRING','Test Error MEssage ');
3013 -- wf_core.RAISE('FTE_WF_ERROR_MESSAGE');
3014
3015
3016 wf_engine.SetItemAttrText(itemtype => itemtype,
3017 itemkey => itemkey,
3018 aname => 'PRICE_REQUEST_ID',
3019 avalue => l_rating_request_id);
3020
3021 resultout := 'COMPLETE:Y';
3022 return;
3023
3024 END IF; --- func mode
3025
3026
3027 --
3028 -- CANCEL mode
3029 --
3030 IF (funcmode = 'CANCEL') THEN
3031 resultout := 'COMPLETE:';
3032
3033 return;
3034 END IF;
3035
3036 IF (funcmode = 'TIMEOUT') THEN
3037 resultout := 'COMPLETE:';
3038 --wsh_debug_sv.pop(l_api_name);
3039 return;
3040 END IF;
3041 --}
3042
3043 EXCEPTION
3044 WHEN OTHERS THEN
3045 wf_core.context('FTE_TENDER_WF',
3046 l_api_name,
3047 itemtype,
3048 itemkey,
3049 actid,
3050 funcmode,
3051 itemtype);
3052 RAISE;
3053
3054 END EXPAND_RANK_LIST;
3055
3056 --}
3057 -- Rel 12 HBHAGAVA
3058 PROCEDURE IS_RANK_LIST_EXHAUSTED(itemtype in varchar2,
3059 itemkey in varchar2,
3060 actid in number,
3061 funcmode in varchar2,
3062 resultout out NOCOPY varchar2) IS
3063 --{
3064
3065
3066 l_api_name CONSTANT VARCHAR2(30) := 'IS_RANK_LIST_EXHAUSTED';
3067 l_api_version CONSTANT NUMBER := 1.0;
3068
3069 l_trip_id NUMBER;
3070
3071 l_debug_on CONSTANT BOOLEAN := WSH_DEBUG_SV.is_debug_enabled;
3072 l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.' || G_PKG_NAME || '.' || l_api_name;
3073
3074 l_is_exhausted VARCHAR2(10);
3075
3076 l_return_status VARCHAR2(1);
3077 l_msg_count NUMBER;
3078 l_msg_data VARCHAR2(30000);
3079 l_number_of_warnings NUMBER;
3080 l_number_of_errors NUMBER;
3081 l_msg_string VARCHAR2(30000);
3082
3083 BEGIN
3084
3085
3086 --
3087 -- RUN mode - normal process execution
3088 --
3089 IF (funcmode = 'RUN') THEN
3090 --
3091 IF l_debug_on THEN
3092 WSH_DEBUG_SV.push(l_module_name);
3093 END IF;
3094
3095
3096 l_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
3097 l_number_of_warnings := 0;
3098 l_number_of_errors := 0;
3099
3100 l_trip_id := wf_engine.GetItemAttrNumber(itemtype,itemkey,'TRIP_ID');
3101
3102 IF l_debug_on
3103 THEN
3104 WSH_DEBUG_SV.logmsg(l_module_name,' Trip Id ' || l_trip_id,WSH_DEBUG_SV.C_PROC_LEVEL);
3105 END IF;
3106
3107
3108 FTE_CARRIER_RANK_LIST_PVT.IS_RANK_LIST_EXHAUSTED(
3109 p_init_msg_list => FND_API.G_TRUE,
3110 x_is_exhausted => l_is_exhausted,
3111 x_return_status => l_return_status,
3112 x_msg_count => l_msg_count,
3113 x_msg_data => l_msg_data,
3114 p_trip_id => l_trip_id);
3115
3116
3117 IF l_debug_on
3118 THEN
3119 WSH_DEBUG_SV.logmsg(l_module_name,' After calling IS_RANK_LIST_EXHAUSTED ' ||
3120 l_is_exhausted,
3121 WSH_DEBUG_SV.C_PROC_LEVEL);
3122 END IF;
3123
3124 IF ( (l_return_status = 'E')
3125 OR (l_return_status = 'U') )
3126 THEN
3127 l_msg_string := 'FTE_SS_INTERFACE.IS_RANK_LIST_EXHAUSTED :-> Tender Id ='||to_char(l_trip_id)||
3128 ' :'||FTE_MLS_UTIL.GET_MESSAGE(l_msg_count,l_msg_data);
3129 wf_core.TOKEN('ERROR_STRING',l_msg_string);
3130 wf_core.RAISE('FTE_WF_ERROR_MESSAGE');
3131 END IF;
3132
3133 IF l_debug_on
3134 THEN
3135 WSH_DEBUG_SV.logmsg(l_module_name,' Returning back value ',
3136 WSH_DEBUG_SV.C_PROC_LEVEL);
3137 END IF;
3138
3139
3140 IF (l_is_exhausted = 'T')
3141 THEN
3142 resultout := 'COMPLETE:Y';
3143 ELSE
3144 resultout := 'COMPLETE:N';
3145 END IF;
3146
3147 return;
3148
3149 END IF; --- func mode
3150
3151 IF l_debug_on THEN
3152 WSH_DEBUG_SV.pop(l_module_name);
3153 END IF;
3154
3155
3156 --
3157 -- CANCEL mode
3158 --
3159 IF (funcmode = 'CANCEL') THEN
3160 resultout := 'COMPLETE:';
3161
3162 return;
3163 END IF;
3164
3165 IF (funcmode = 'TIMEOUT') THEN
3166 resultout := 'COMPLETE:';
3167 return;
3168 END IF;
3169 --}
3170
3171 EXCEPTION
3172 WHEN OTHERS THEN
3173 wf_core.context('FTE_TENDER_WF',
3174 l_api_name,
3175 itemtype,
3176 itemkey,
3177 actid,
3178 funcmode,
3179 itemtype);
3180 RAISE;
3181
3182 END IS_RANK_LIST_EXHAUSTED;
3183
3184 --}
3185 -- Rel 12 HBHAGAVA
3186 PROCEDURE REMOVE_SERVICE_APPLY_NEXT(itemtype in varchar2,
3187 itemkey in varchar2,
3188 actid in number,
3189 funcmode in varchar2,
3190 resultout out NOCOPY varchar2) IS
3191 --{
3192
3193
3194 l_api_name CONSTANT VARCHAR2(30) := 'REMOVE_SERVICE_APPLY_NEXT';
3195 l_api_version CONSTANT NUMBER := 1.0;
3196
3197 l_trip_id NUMBER;
3198
3199 l_debug_on CONSTANT BOOLEAN := WSH_DEBUG_SV.is_debug_enabled;
3200 l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.' || G_PKG_NAME || '.' || l_api_name;
3201
3202 l_priceRequestId NUMBER;
3203
3204 l_return_status VARCHAR2(1);
3205 l_msg_count NUMBER;
3206 l_msg_data VARCHAR2(30000);
3207 l_number_of_warnings NUMBER;
3208 l_number_of_errors NUMBER;
3209 l_msg_string VARCHAR2(30000);
3210
3211 BEGIN
3212
3213
3214 --
3215 -- RUN mode - normal process execution
3216 --
3217 IF (funcmode = 'RUN') THEN
3218 --
3219
3220 l_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
3221 l_number_of_warnings := 0;
3222 l_number_of_errors := 0;
3223
3224 l_trip_id := wf_engine.GetItemAttrNumber(itemtype,itemkey,'TRIP_ID');
3225 l_priceRequestId := wf_engine.GetItemAttrNumber(itemtype,itemkey,'PRICE_REQUEST_ID');
3226
3227 FTE_CARRIER_RANK_LIST_PVT.REMOVE_SERVICE_APPLY_NEXT(
3228 p_init_msg_list => FND_API.G_TRUE,
3229 x_return_status => l_return_status,
3230 x_msg_count => l_msg_count,
3231 x_msg_data => l_msg_data,
3232 p_trip_id => l_trip_id,
3233 p_price_request_id => l_priceRequestId);
3234
3235 IF ( (l_return_status = 'E')
3236 OR (l_return_status = 'U') )
3237 THEN
3238 l_msg_string := 'FTE_CARRIER_RANK_LIST_PVT.REMOVE_SERVICE_APPLY_NEXT :-> Tender Id ='||to_char(l_trip_id)||
3239 ' :'||FTE_MLS_UTIL.GET_MESSAGE(l_msg_count,l_msg_data);
3240 wf_core.TOKEN('ERROR_STRING',l_msg_string);
3241 wf_core.RAISE('FTE_WF_ERROR_MESSAGE');
3242 END IF;
3243
3244 resultout := 'COMPLETE:Y';
3245
3246 return;
3247
3248 END IF; --- func mode
3249
3250
3251 --
3252 -- CANCEL mode
3253 --
3254 IF (funcmode = 'CANCEL') THEN
3255 resultout := 'COMPLETE:';
3256
3257 return;
3258 END IF;
3259
3260 IF (funcmode = 'TIMEOUT') THEN
3261 resultout := 'COMPLETE:';
3262 --wsh_debug_sv.pop(l_api_name);
3263 return;
3264 END IF;
3265 --}
3266
3267 EXCEPTION
3268 WHEN OTHERS THEN
3269 wf_core.context('FTE_TENDER_WF',
3270 l_api_name,
3271 itemtype,
3272 itemkey,
3273 actid,
3274 funcmode,
3275 itemtype);
3276 RAISE;
3277
3278 END REMOVE_SERVICE_APPLY_NEXT;
3279
3280 --}
3281 -- Rel 12 HBHAGAVA
3282 PROCEDURE AUTO_TENDER_SERVICE(itemtype in varchar2,
3283 itemkey in varchar2,
3284 actid in number,
3285 funcmode in varchar2,
3286 resultout out NOCOPY varchar2) IS
3287 --{
3288
3289
3290 l_api_name CONSTANT VARCHAR2(30) := 'AUTO_TENDER_SERVICE';
3291 l_api_version CONSTANT NUMBER := 1.0;
3292
3293 l_trip_id NUMBER;
3294
3295 l_debug_on CONSTANT BOOLEAN := WSH_DEBUG_SV.is_debug_enabled;
3296 l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.' || G_PKG_NAME || '.' || l_api_name;
3297
3298
3299 l_return_status VARCHAR2(1);
3300 l_msg_count NUMBER;
3301 l_msg_data VARCHAR2(30000);
3302 l_number_of_warnings NUMBER;
3303 l_number_of_errors NUMBER;
3304 l_msg_string VARCHAR2(30000);
3305 l_carrier_id NUMBER;
3306 l_autoTender VARCHAR2(1);
3307
3308 trip_action_param FTE_TRIP_ACTION_PARAM_REC;
3309 l_action_out_rec FTE_ACTION_OUT_REC;
3310
3311
3312 BEGIN
3313
3314
3315 --
3316 -- RUN mode - normal process execution
3317 --
3318 IF (funcmode = 'RUN') THEN
3319 --
3320
3321 l_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
3322 l_number_of_warnings := 0;
3323 l_number_of_errors := 0;
3324
3325 l_trip_id := wf_engine.GetItemAttrNumber(itemtype,itemkey,'TRIP_ID');
3326
3327
3328 SELECT CARRIER_ID INTO l_carrier_id FROM WSH_TRIPS
3329 WHERE TRIP_ID = l_trip_id;
3330
3331 SELECT decode(ENABLE_AUTO_TENDER,null,'N','N','N','Y') AUTO_TENDER
3332 INTO l_autoTender
3333 FROM WSH_CARRIER_SITES
3334 WHERE CARRIER_ID = l_carrier_id
3335 AND ROWNUM = 1;
3336
3337 IF (l_autoTender = 'N') THEN
3338 resultout := 'COMPLETE:N';
3339 ELSE
3340
3341 trip_action_param := FTE_TRIP_ACTION_PARAM_REC(null,'TENDERED',
3342 null,null,null,null,null,null,
3343 null,null,null,null,null,null,
3344 null,null);
3345
3346 FTE_MLS_WRAPPER.Trip_Action (
3347 p_api_version_number => 1.0,
3348 p_init_msg_list => FND_API.G_TRUE,
3349 x_return_status => l_return_status,
3350 x_msg_count => l_msg_count,
3351 x_msg_data => l_msg_data,
3352 x_action_out_rec => l_action_out_rec,
3353 p_tripId => l_trip_id,
3354 p_action_prms => trip_action_param);
3355
3356
3357 IF ( (l_return_status = 'E')
3358 OR (l_return_status = 'U') )
3359 THEN
3360 l_msg_string := 'FTE_MLS_WRAPPER.TRIP_ACTION :-> Tender Id ='||to_char(l_trip_id)||
3361 ' :'||FTE_MLS_UTIL.GET_MESSAGE(l_msg_count,l_msg_data);
3362 wf_core.TOKEN('ERROR_STRING',l_msg_string);
3363 wf_core.RAISE('FTE_WF_ERROR_MESSAGE');
3364 END IF;
3365
3366 resultout := 'COMPLETE:Y';
3367
3368 END IF;
3369
3370 return;
3371
3372 END IF; --- func mode
3373
3374
3375 --
3376 -- CANCEL mode
3377 --
3378 IF (funcmode = 'CANCEL') THEN
3379 resultout := 'COMPLETE:';
3380
3381 return;
3382 END IF;
3383
3384 IF (funcmode = 'TIMEOUT') THEN
3385 resultout := 'COMPLETE:';
3386 --wsh_debug_sv.pop(l_api_name);
3387 return;
3388 END IF;
3389 --}
3390
3391 EXCEPTION
3392 WHEN OTHERS THEN
3393 wf_core.context('FTE_TENDER_WF',
3394 l_api_name,
3395 itemtype,
3396 itemkey,
3397 actid,
3398 funcmode,
3399 itemtype);
3400 RAISE;
3401
3402 END AUTO_TENDER_SERVICE;
3403
3404 --}
3405
3406 -- Rel 12 HBHAGAVA
3407 PROCEDURE FTETENDER_SELECTOR(itemtype in varchar2,
3408 itemkey in varchar2,
3409 actid in number,
3410 funcmode in varchar2,
3411 resultout out NOCOPY varchar2) IS
3412 --{
3413
3414
3415 l_api_name CONSTANT VARCHAR2(30) := 'FTETENDER_SELECTOR';
3416 l_api_version CONSTANT NUMBER := 1.0;
3417
3418 l_trip_id NUMBER;
3419
3420 l_debug_on CONSTANT BOOLEAN := WSH_DEBUG_SV.is_debug_enabled;
3421 l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.' || G_PKG_NAME || '.' || l_api_name;
3422
3423
3424 l_userId NUMBER;
3425 l_respId NUMBER;
3426 l_respAppId NUMBER;
3427
3428
3429 BEGIN
3430
3431
3432 --
3433 -- RUN mode - normal process execution
3434 --
3435 IF (funcmode = 'RUN') THEN
3436 resultout := 'COMPLETE:Y';
3437 ELSIF (funcmode = 'SET_CTX') THEN
3438
3439 IF l_debug_on THEN
3440 WSH_DEBUG_SV.push(l_module_name);
3441 END IF;
3442
3443 l_userId := wf_engine.GetItemAttrNumber(itemtype,itemKey,'USER_ID');
3444 l_respAppId := wf_engine.GetItemAttrNumber(itemtype,itemKey, 'RESP_APPL_ID');
3445 l_respId := wf_engine.GetItemAttrNumber(itemtype,itemKey,'RESPONSIBILITY_ID');
3446
3447 IF l_debug_on THEN
3448 WSH_DEBUG_SV.logmsg(l_module_name,' Initializing responsiblity information ' ||
3449 l_userId,WSH_DEBUG_SV.C_PROC_LEVEL);
3450 END IF;
3451
3452
3453 IF(l_respAppId IS NULL OR l_respId IS NULL) THEN
3454 RAISE no_data_found;
3455 ELSE
3456 FND_GLOBAL.Apps_Initialize(l_userId, l_respId, l_respAppId);
3457 END IF;
3458 --
3459 resultout := 'COMPLETE';
3460
3461
3462 IF l_debug_on THEN
3463 WSH_DEBUG_SV.pop(l_module_name);
3464 END IF;
3465
3466 END IF; --- func mode
3467
3468
3469 --
3470 -- CANCEL mode
3471 --
3472 IF (funcmode = 'CANCEL') THEN
3473 resultout := 'COMPLETE:';
3474
3475 return;
3476 END IF;
3477
3478 IF (funcmode = 'TIMEOUT') THEN
3479 resultout := 'COMPLETE:';
3480 --wsh_debug_sv.pop(l_api_name);
3481 return;
3482 END IF;
3483 --}
3484
3485 EXCEPTION
3486 WHEN OTHERS THEN
3487 wf_core.context('FTE_TENDER_WF',
3488 l_api_name,
3489 itemtype,
3490 itemkey,
3491 actid,
3492 funcmode,
3493 itemtype);
3494 IF l_debug_on THEN
3495 WSH_DEBUG_SV.pop(l_module_name);
3496 END IF;
3497
3498 RAISE;
3499
3500 END FTETENDER_SELECTOR;
3501
3502 --}
3503
3504
3505 END FTE_TENDER_WF;