[Home] [Help]
PACKAGE BODY: APPS.FTE_MLS_TEST_NT
Source
1 PACKAGE BODY FTE_MLS_TEST_NT as
2 /* $Header: FTEMLTEB.pls 120.4 2005/07/14 08:18 nltan noship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'FTE_MLS_TEST_NT';
5 --
6 --========================================================================
7 -- PROCEDURE : ASSIGN_SERVICE_TENDER FTE wrapper
8 --
9 -- COMMENT : Procedure assigns service, creates/updates ranked list,
10 -- tenders, and deletes rates. TripId should exist in the db.
11 -- If FTE_SS_ATTR_REC.DELIVERY_ID and
12 -- FTE_SS_ATTR_REC.DELIVERY_LEG_ID are null, then it means the
13 -- caller is TWB. Otherwise, the caller is DWB or ManItinerary
14 -- CALLER : FTE UI: TripWB, DeliveryWB, ManageItinerary
15 --========================================================================
16 --
17 PROCEDURE ASSIGN_SERVICE_TENDER
18 (
19 p_API_VERSION_NUMBER IN NUMBER,
20 p_INIT_MSG_LIST IN VARCHAR2,
21 p_COMMIT IN VARCHAR2,
22 p_SS_ATTR_REC IN FTE_SS_ATTR_REC,
23 p_SS_RATE_SORT_TAB IN OUT NOCOPY FTE_SS_RATE_SORT_TAB_TYPE,
24 p_TENDER_ATTR_REC IN FTE_TENDER_ATTR_REC,
25 p_REQUEST_ID IN NUMBER,
26 p_SERVICE_ACTION IN VARCHAR2,
27 p_LIST_ACTION IN VARCHAR2,
28 x_RETURN_STATUS OUT NOCOPY VARCHAR2,
29 x_MSG_COUNT OUT NOCOPY NUMBER,
30 x_MSG_DATA OUT NOCOPY VARCHAR2)
31 IS
32 l_number_of_warnings NUMBER;
33 l_number_of_errors NUMBER;
34 l_return_status VARCHAR2(32767);
35 l_msg_count NUMBER;
36 l_msg_data VARCHAR2(32767);
37
38 l_trip_id NUMBER;
39 l_delivery_leg_id NUMBER;
40 l_delivery_id NUMBER;
41 l_lane_id NUMBER;
42 l_carrier_id NUMBER;
43 l_mode VARCHAR2(30);
44 l_service_level VARCHAR2(30);
45 l_veh_item_id NUMBER;
46 l_veh_org_id NUMBER;
47 l_rank_id NUMBER;
48 l_schedule_id NUMBER;
49
50 l_ret_trip_name VARCHAR2(30);
51 l_ret_trip_id NUMBER;
52 l_list_action VARCHAR2(30);
53
54 l_ss_rate_sort_rec FTE_SS_RATE_SORT_REC;
55 l_action_out_rec FTE_ACTION_OUT_REC;
56 l_trip_action_param FTE_TRIP_ACTION_PARAM_REC;
57
58 l_debug_on CONSTANT BOOLEAN := WSH_DEBUG_SV.is_debug_enabled;
59 l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.'|| G_PKG_NAME ||'.'||'ASSIGN_SERVICE_TENDER';
60
61 BEGIN
62
63 SAVEPOINT ASSIGN_SERVICE_TENDER_PUB;
64
65 -- Initialize message list if p_init_msg_list is set to TRUE.
66 --
67 IF FND_API.to_Boolean( p_init_msg_list )
68 THEN
69 FND_MSG_PUB.initialize;
70 END IF;
71 --
72 IF l_debug_on THEN
73 wsh_debug_sv.push(l_module_name);
74 END IF;
75 --
76 --
77 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
78 x_msg_count := 0;
79
80 -- local variables used to check API return values
81 l_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
82 l_number_of_warnings := 0;
83 l_number_of_errors := 0;
84
85 l_trip_id := p_ss_attr_rec.trip_id;
86 l_delivery_leg_id := p_ss_attr_rec.delivery_leg_id;
87 l_delivery_id := p_ss_attr_rec.delivery_id;
88 l_list_action := p_list_action;
89
90 IF l_debug_on THEN
91 WSH_DEBUG_SV.logmsg(l_module_name, 'TripId:LegId:DeliveryId:'
92 ||l_trip_id||':'||l_delivery_leg_id||':'||l_delivery_id);
93 WSH_DEBUG_SV.logmsg(l_module_name, 'Service Action:List Action:'
94 ||p_service_action||':'||p_list_action);
95 END IF;
96
97 -- Step 1: Check if old service exists on trip
98 -- If p_service_action indicates service is currently assigned, delete old rates
99 -- and raise appropriate business event
100 IF ( p_service_action = 'UPDATE' AND l_trip_id IS NOT NULL
101 AND l_delivery_leg_id IS NULL ) THEN
102
103 IF l_debug_on THEN
104 WSH_DEBUG_SV.logmsg(l_module_name, 'Deleting Main Records for:'||l_trip_id);
105 END IF;
106
107 FTE_TRIP_RATING_GRP.DELETE_MAIN_RECORDS(
108 p_trip_id => l_trip_id,
109 x_return_status => l_return_status);
110
111
112 WSH_UTIL_CORE.API_POST_CALL(
113 p_return_status =>l_return_status,
114 x_num_warnings =>l_number_of_warnings,
115 x_num_errors =>l_number_of_errors,
116 p_msg_data =>l_msg_data);
117 END IF;
118
119 -- Step 2: Check if l_list_action is correct.
120 -- Get the first record in the service table.
121 -- If LaneId is null, change l_list_action = 'SET_CURRENT'.
122
123 IF l_debug_on THEN
124 WSH_DEBUG_SV.logmsg(l_module_name, 'List Action:'||p_list_action);
125 WSH_DEBUG_SV.logmsg(l_module_name, ' Service List Size:'||p_ss_rate_sort_tab.count);
126 END IF;
127
128 IF ( l_list_action = 'APPEND' ) THEN
129 l_ss_rate_sort_rec := p_ss_rate_sort_tab(1);
130
131 IF l_debug_on THEN
132 WSH_DEBUG_SV.logmsg(l_module_name, 'Append Check Rank Id:'||l_ss_rate_sort_rec.rank_id);
133 WSH_DEBUG_SV.logmsg(l_module_name, 'Append Check Lane Id:'||l_ss_rate_sort_rec.lane_id);
134 END IF;
135
136 IF (l_ss_rate_sort_rec.rank_id IS NOT NULL AND l_ss_rate_sort_rec.lane_id IS NULL) THEN
137 -- l_ss_rate_sort_rec.schedule_id IS NULL AND
138 l_rank_id := l_ss_rate_sort_rec.rank_id;
139 l_list_action := 'SET_CURRENT';
140
141 IF l_debug_on THEN
142 WSH_DEBUG_SV.logmsg(l_module_name, 'Only rank id populated:'||l_rank_id);
143 END IF;
144 END IF;
145 END IF;
146
147 IF l_debug_on THEN
148 WSH_DEBUG_SV.logmsg(l_module_name, ' New List Action:'||l_list_action);
149 END IF;
150
151
152 -- Step 3: Create, Append or SetCurrent Ranked List depending on list action_code
153 -- Values will be CREATE OR APPEND. Modify APPEND to be either APPEND or SET_CURRENT
154 -- CREATE: Manual condition, set IS_CURRENT for selected service
155 -- SET_CURRENT: Only pass user entry as current. Version is increased. Do no
156 -- pass service tab, only rankId
157 -- APPEND: Pass in one record service tab (entry has no rankId or sequence)
158 -- Entry appended to existing list with next ranked seq. Set IS_CURRENT on record.
159 -- OUT param is the new rankId to be stored on the trip
160
161 IF ( l_list_action IS NOT NULL AND l_trip_id IS NOT NULL) THEN
162 IF ( p_list_action = 'SET_CURRENT' ) THEN
163 FTE_CARRIER_RANK_LIST_PVT.RANK_LIST_ACTION_UIWRAPPER(
164 p_API_VERSION_NUMBER => 1.0,
165 p_INIT_MSG_LIST => FND_API.G_TRUE,
166 p_ACTION_CODE => l_list_action,
167 p_RANKLIST => p_ss_rate_sort_tab,
168 p_RANK_ID => l_rank_id,
169 p_TRIP_ID => l_trip_id,
170 x_RETURN_STATUS => l_return_status,
171 x_MSG_COUNT => l_msg_count,
172 x_MSG_DATA => l_msg_data);
173 ELSE
174 FTE_CARRIER_RANK_LIST_PVT.RANK_LIST_ACTION_UIWRAPPER(
175 p_API_VERSION_NUMBER => 1.0,
176 p_INIT_MSG_LIST => FND_API.G_TRUE,
177 p_ACTION_CODE => l_list_action,
178 p_RANKLIST => p_ss_rate_sort_tab,
179 p_RANK_ID => l_rank_id,
180 p_TRIP_ID => l_trip_id,
181 x_RETURN_STATUS => l_return_status,
182 x_MSG_COUNT => l_msg_count,
183 x_MSG_DATA => l_msg_data);
184 END IF;
185
186 WSH_UTIL_CORE.API_POST_CALL(
187 p_return_status => l_return_status,
188 x_num_warnings => l_number_of_warnings,
189 x_num_errors => l_number_of_errors,
190 p_msg_data => l_msg_data);
191
192 END IF;
193
194 IF l_debug_on THEN
195 WSH_DEBUG_SV.logmsg(l_module_name, 'New Rank Id:'||l_rank_id);
196 END IF;
197
198 -- Step 4: Update Trip
199 -- If delivery_id is null, this call is coming from TripWB. Update Trip with
200 -- current service from FTE_SS_ATTR_REC and rank id.
201 -- Else this call is coming from DWB or MI. Update Trip with Rank Id only.
202
203 IF (l_delivery_id IS NULL ) THEN -- Coming from TWB
204
205 l_lane_id := p_ss_attr_rec.lane_id;
206 l_schedule_id := p_ss_attr_rec.schedule_id;
207 l_carrier_id := p_ss_attr_rec.carrier_id;
208 l_mode := p_ss_attr_rec.mode_of_transport;
209 l_service_level := p_ss_attr_rec.service_level;
210 l_veh_item_id := p_ss_attr_rec.vehicle_item_id;
211 l_veh_org_id := p_ss_attr_rec.vehicle_org_id;
212
213 FTE_MLS_WRAPPER.UPDATE_SERVICE_ON_TRIP(
214 p_API_VERSION_NUMBER => 1.0,
215 p_INIT_MSG_LIST => FND_API.G_TRUE,
216 p_COMMIT => FND_API.G_FALSE,
217 p_CALLER => 'FTE',
218 p_SERVICE_ACTION => p_service_action,
219 p_DELIVERY_ID => l_delivery_id,
220 p_DELIVERY_LEG_ID => l_delivery_leg_id,
221 p_TRIP_ID => l_trip_id,
222 p_LANE_ID => l_lane_id,
223 p_SCHEDULE_ID => null, -- Need to change to real schedule
224 p_CARRIER_ID => l_carrier_id,
225 p_SERVICE_LEVEL => l_service_level,
226 p_MODE_OF_TRANSPORT => l_mode,
227 p_VEHICLE_ITEM_ID => l_veh_item_id,
228 p_VEHICLE_ORG_ID => l_veh_org_id,
229 p_CONSIGNEE_CARRIER_AC_NO => FND_API.G_MISS_CHAR,
230 p_FREIGHT_TERMS_CODE => FND_API.G_MISS_CHAR,
231 x_RETURN_STATUS => l_return_status,
232 x_MSG_COUNT => l_msg_count,
233 x_MSG_DATA => l_msg_data);
234
235 ELSE -- Coming from DWB/MI
236 FTE_MLS_WRAPPER.UPDATE_SERVICE_ON_TRIP(
237 p_API_VERSION_NUMBER => 1.0,
238 p_INIT_MSG_LIST => FND_API.G_TRUE,
239 p_COMMIT => FND_API.G_FALSE,
240 p_CALLER => 'FTE',
241 p_SERVICE_ACTION => p_service_action,
242 p_DELIVERY_ID => l_delivery_id,
243 p_DELIVERY_LEG_ID => l_delivery_leg_id,
244 p_TRIP_ID => l_trip_id,
245 p_LANE_ID => FND_API.G_MISS_NUM,
246 p_SCHEDULE_ID => FND_API.G_MISS_NUM,
247 p_CARRIER_ID => FND_API.G_MISS_NUM,
248 p_SERVICE_LEVEL => FND_API.G_MISS_CHAR,
249 p_MODE_OF_TRANSPORT => FND_API.G_MISS_CHAR,
250 p_VEHICLE_ITEM_ID => FND_API.G_MISS_NUM,
251 p_VEHICLE_ORG_ID => FND_API.G_MISS_NUM,
252 p_CONSIGNEE_CARRIER_AC_NO => FND_API.G_MISS_CHAR,
253 p_FREIGHT_TERMS_CODE => FND_API.G_MISS_CHAR,
254 x_RETURN_STATUS => l_return_status,
255 x_MSG_COUNT => l_msg_count,
256 x_MSG_DATA => l_msg_data);
257 END IF;
258
259 -- Step 5: Move FC Rates
260 -- If delivery_id is null, this call is coming from TripWB.
261
262 IF l_debug_on THEN
263 WSH_DEBUG_SV.logmsg(l_module_name,'Calling Move FC TEMP Request Id:'||p_request_id);
264 WSH_DEBUG_SV.logmsg(l_module_name,' If Delivery Id is NULL, from TWB:'||l_delivery_id);
265 END IF;
266
267 IF ( p_request_id IS NOT NULL ) THEN
268
269 IF (l_delivery_id IS NULL ) THEN -- Coming from TWB
270
271 FTE_TRIP_RATING_GRP.Move_Records_To_Main(
272 p_trip_id => l_trip_id,
273 p_lane_id => l_lane_id,
274 p_schedule_id => l_schedule_id,
275 p_service_type_code => l_service_level,
276 p_comparison_request_id => p_request_id,
277 x_return_status => l_return_status);
278 /*
279 ELSE -- Coming from DWB/MI
280
281 FTE_FREIGHT_PRICING.MOVE_FC_TEMP_TO_MAIN(
282 p_init_msg_list => FND_API.G_FALSE,
283 p_request_id => p_request_id,
284 p_trip_id => l_trip_id,
285 p_lane_id => l_lane_id,
286 p_schedule_id => l_schedule_id,
287 p_service_type_code => l_service_level,
288 x_return_status => l_return_status);
289 */
290
291 END IF;
292
293 WSH_UTIL_CORE.API_POST_CALL(
294 p_return_status =>l_return_status,
295 x_num_warnings =>l_number_of_warnings,
296 x_num_errors =>l_number_of_errors,
297 p_msg_data =>l_msg_data);
298
299 END IF;
300
301 IF l_debug_on THEN
302 WSH_DEBUG_SV.logmsg(l_module_name,'Pre Raising Tender Event CarrierContactId:'
303 ||p_tender_attr_rec.car_contact_id);
304 END IF;
305
306 -- Step 6: Raise Tender event
307 IF ( p_tender_attr_rec IS NOT NULL AND
308 p_tender_attr_rec.car_contact_id IS NOT NULL) THEN
309
310 IF l_debug_on THEN
311 WSH_DEBUG_SV.logmsg(l_module_name,'Raising Tender Event CarrierContactId:'
312 ||p_tender_attr_rec.car_contact_id);
313 END IF;
314
315 -- Create Trip Actions Tab
316 l_trip_action_param := FTE_TRIP_ACTION_PARAM_REC(null,'TENDERED',
317 null,null,null,null,null,null,
318 null,null,null,null,null,null,
319 null,null);
320
321 FTE_MLS_WRAPPER.TRIP_ACTION(
322 p_api_version_number => 1.0,
323 p_init_msg_list => FND_API.G_TRUE,
324 p_action_prms => l_trip_action_param,
325 p_trip_info_rec => p_tender_attr_rec,
326 x_action_out_rec => l_action_out_rec,
327 x_return_status => l_return_status,
328 x_msg_count => l_msg_count,
329 x_msg_data => l_msg_data);
330
331 WSH_UTIL_CORE.API_POST_CALL(
332 p_return_status =>l_return_status,
333 x_num_warnings =>l_number_of_warnings,
334 x_num_errors =>l_number_of_errors,
335 p_msg_data =>l_msg_data);
336 END IF;
337
338
339 IF l_debug_on THEN
340 WSH_DEBUG_SV.POP(l_module_name);
341 END IF;
342
343 EXCEPTION
344 WHEN FND_API.G_EXC_ERROR THEN
345 ROLLBACK TO ASSIGN_SERVICE_TENDER_PUB;
346 x_return_status := FND_API.G_RET_STS_ERROR;
347 FND_MSG_PUB.Count_And_Get
348 (
349 p_count => x_msg_count,
350 p_data => x_msg_data,
351 p_encoded => FND_API.G_FALSE
352 );
353 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
354 ROLLBACK TO ASSIGN_SERVICE_TENDER_PUB;
355 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
356 FND_MSG_PUB.Count_And_Get
357 (
358 p_count => x_msg_count,
359 p_data => x_msg_data,
360 p_encoded => FND_API.G_FALSE
361 );
362 WHEN OTHERS THEN
363 ROLLBACK TO ASSIGN_SERVICE_TENDER_PUB;
364 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
365 FND_MSG_PUB.Count_And_Get
366 (
367 p_count => x_msg_count,
368 p_data => x_msg_data,
369 p_encoded => FND_API.G_FALSE
370 );
371 END ASSIGN_SERVICE_TENDER;
372 --
373 PROCEDURE SEARCH_SERVICES
374 (
375 p_API_VERSION_NUMBER IN NUMBER,
376 p_INIT_MSG_LIST IN VARCHAR2,
377 p_COMMIT IN VARCHAR2,
378 p_CALLER IN VARCHAR2,
379 p_FTE_SS_ATTR_REC IN FTE_SS_ATTR_REC,
380 x_LIST_CREATE_TYPE OUT NOCOPY VARCHAR2,
381 x_SS_RATE_SORT_TAB OUT NOCOPY FTE_SS_RATE_SORT_TAB_TYPE,
382 x_PRICING_REQUEST_ID OUT NOCOPY NUMBER,
383 x_RETURN_STATUS OUT NOCOPY VARCHAR2,
384 x_MSG_COUNT OUT NOCOPY NUMBER,
385 x_MSG_DATA OUT NOCOPY VARCHAR2
386 )
387 IS
388 l_ss_rate_sort_rec FTE_SS_RATE_SORT_REC;
389 l_ss_rate_sort_rec2 FTE_SS_RATE_SORT_REC;
390 l_ss_rate_sort_rec3 FTE_SS_RATE_SORT_REC;
391 l_debug_on CONSTANT BOOLEAN := WSH_DEBUG_SV.is_debug_enabled;
392 l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.'|| G_PKG_NAME ||'.'||'SEARCH_SERVICES';
393
394 BEGIN
395
396 SAVEPOINT SEARCH_SERVICES_PUB;
397
398
399 -- Initialize message list if p_init_msg_list is set to TRUE.
400 --
401 --IF FND_API.to_Boolean( p_init_msg_list )
402 --THEN
403 FND_MSG_PUB.initialize;
404 --END IF;
405 --
406 IF l_debug_on THEN
407 wsh_debug_sv.push(l_module_name);
408 END IF;
409 --
410 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
411 x_msg_count := 0;
412 x_msg_data := 0;
413
414 x_SS_RATE_SORT_TAB := FTE_SS_RATE_SORT_TAB_TYPE();
415 --x_LIST_CREATE_TYPE := 'MAN';
416 x_LIST_CREATE_TYPE := 'RG';
417 --
418
419 l_ss_rate_sort_rec3 := FTE_SS_RATE_SORT_REC(
420 3, 3,
421 14003, null, 67278, 'LTL', 'LTL', null, null,
422 null, 'MAN', 650, 'USD',
423 0, 'DAY', null, null,
424 'N', 0, 205, null, null);
425
426 x_SS_RATE_SORT_TAB.EXTEND;
427 x_SS_RATE_SORT_TAB(1) := l_ss_rate_sort_rec3;
428
429 l_ss_rate_sort_rec := FTE_SS_RATE_SORT_REC(
430 1, 1,
431 1408, null, 14012, 'LTL', 'LTL', null, null,
432 null, 'RG', 900, 'KRW',
433 5, 'DAY', null, null,
434 'N', 1, 100, null, null);
435
436 x_SS_RATE_SORT_TAB.EXTEND;
437 x_SS_RATE_SORT_TAB(2) := l_ss_rate_sort_rec;
438
439 l_ss_rate_sort_rec2 := FTE_SS_RATE_SORT_REC(
440 2, 2,
441 1490, null, 49275, 'TRUCK', 'STANDARD', 16817, 204,
442 null, 'MAN', 643, 'USD',
443 '2', 'DAY', null, null,
444 'Y', 0, 200, null, null);
445
446 x_SS_RATE_SORT_TAB.EXTEND;
447 x_SS_RATE_SORT_TAB(3) := l_ss_rate_sort_rec2;
448
449 x_PRICING_REQUEST_ID := 21;
450
451 --
452 EXCEPTION
453 WHEN FND_API.G_EXC_ERROR THEN
454 ROLLBACK TO SEARCH_SERVICES_PUB;
455 x_return_status := FND_API.G_RET_STS_ERROR;
456 FND_MSG_PUB.Count_And_Get
457 (
458 p_count => x_msg_count,
459 p_data => x_msg_data,
460 p_encoded => FND_API.G_FALSE
461 );
462 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
463 ROLLBACK TO SEARCH_SERVICES_PUB;
464 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
465 FND_MSG_PUB.Count_And_Get
466 (
467 p_count => x_msg_count,
468 p_data => x_msg_data,
469 p_encoded => FND_API.G_FALSE
470 );
471 WHEN OTHERS THEN
472 ROLLBACK TO SEARCH_SERVICES_PUB;
473 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
474 FND_MSG_PUB.Count_And_Get
475 (
476 p_count => x_msg_count,
477 p_data => x_msg_data,
478 p_encoded => FND_API.G_FALSE
479 );
480 END SEARCH_SERVICES;
481 --
482 END FTE_MLS_TEST_NT;