[Home] [Help]
PACKAGE BODY: APPS.WSH_MDC_SRS
Source
1 PACKAGE BODY WSH_MDC_SRS as
2 /* $Header: WSHMDSRB.pls 120.8.12000000.2 2007/01/24 18:13:31 bsadri ship $ */
3
4
5 --===================
6 -- PUBLIC VARIABLES
7 --===================
8 G_PKG_NAME CONSTANT VARCHAR2(50) := 'WSH_MDC_SRS';
9
10
11 --========================================================================
12 -- TYPE : Consolidation_Rec
13 --
14 -- COMMENT : Holds the consolidation records created, along with the list
15 -- of deliveries going into the consolidation.
16 --========================================================================
17 TYPE Consolidation_Rec IS RECORD(
18 consol_index NUMBER,
19 total_weight NUMBER,
20 weight_uom VARCHAR2(3),
21 delivery_tab WSH_NEW_DELIVERIES_PVT.Delivery_Attr_Tbl_Type
22 );
23
24
25 --========================================================================
26 -- TYPE : Consolidation_Tab
27 --
28 -- COMMENT : Table of consolidation records created.
29 --========================================================================
30 TYPE Consolidation_Tab IS TABLE OF Consolidation_Rec INDEX BY binary_integer;
31
32
33 --===================
34 -- PROCEDURES
35 --===================
36
37 --========================================================================
38 -- PROCEDURE : Add_For_Consolidation
39 --
40 -- PARAMETERS: p_delivery_rec Delivery record
41 -- p_del_weight Delivery weight
42 -- p_weight_uom Weight UOM
43 -- p_max_weight Max consolidation trip weight
44 -- p_ignore_weight Ignore consolidation trip weight flag
45 -- x_consol_tab Consolidation records created
46 -- x_consol_index Consolidation record index of the current delivery
47 -- x_return_status Return status
48 --
49 -- COMMENT : This procedure adds the delivery to existing consolidation
50 -- records, or creates a new consolidation record based on the
51 -- delivery weight and maximum weight allowed on the consolidation
52 -- trip. The procedure makes sure that max weight for consolidation
53 -- is not exceeded.
54 --========================================================================
55 PROCEDURE Add_For_Consolidation(
56 p_delivery_rec IN WSH_NEW_DELIVERIES_PVT.Delivery_Rec_Type,
57 p_del_weight IN NUMBER,
58 p_weight_uom IN VARCHAR2,
59 p_max_weight IN NUMBER,
60 p_ignore_weight IN BOOLEAN,
61 x_consol_tab IN OUT NOCOPY consolidation_tab,
62 x_consol_index OUT NOCOPY NUMBER,
63 x_return_status OUT NOCOPY VARCHAR2);
64
65 --========================================================================
66 -- FUNCTION : Copy_Record
67 --
68 -- PARAMETERS: p_del_ccinfo_rec Input delivery record type
69 -- p_del_addnl_rec Input delivery record type containing additional info
70 -- RETRUN type Delivery record
71 --
72 -- COMMENT : This function takes delivery records of types
73 -- WSH_FTE_CONSTRAINT_FRAMEWORK.delivery_ccinfo_rec_type and
74 -- WSH_MDC_SRS.addnl_del_attr_rec_type and copies the information
75 -- to a record of type WSH_NEW_DELIVERIES_PVT.Delivery_Rec_Type
76 -- and returns it.
77 --========================================================================
78 FUNCTION Copy_Record(
79 p_del_ccinfo_rec IN WSH_FTE_CONSTRAINT_FRAMEWORK.delivery_ccinfo_rec_type,
80 p_del_addnl_rec IN addnl_del_attr_rec_type)
81 RETURN WSH_NEW_DELIVERIES_PVT.Delivery_Rec_Type
82 IS
83 x_delivery_rec WSH_NEW_DELIVERIES_PVT.Delivery_Rec_Type;
84 BEGIN
85
86 x_delivery_rec.delivery_id := p_del_ccinfo_rec.delivery_id;
87 x_delivery_rec.name := p_del_ccinfo_rec.name;
88 x_delivery_rec.organization_id := p_del_ccinfo_rec.organization_id;
89 x_delivery_rec.status_code := p_del_ccinfo_rec.status_code;
90 x_delivery_rec.planned_flag := p_del_ccinfo_rec.planned_flag;
91 x_delivery_rec.initial_pickup_date := p_del_ccinfo_rec.initial_pickup_date;
92 x_delivery_rec.initial_pickup_location_id := p_del_ccinfo_rec.initial_pickup_location_id;
93 x_delivery_rec.ultimate_dropoff_location_id := p_del_ccinfo_rec.ultimate_dropoff_location_id;
94 x_delivery_rec.ultimate_dropoff_date := p_del_ccinfo_rec.ultimate_dropoff_date;
95 x_delivery_rec.customer_id := p_del_ccinfo_rec.customer_id;
96 x_delivery_rec.intmed_ship_to_location_id := p_del_ccinfo_rec.intmed_ship_to_location_id;
97 x_delivery_rec.ship_method_code := p_del_ccinfo_rec.ship_method_code;
98 x_delivery_rec.delivery_type := p_del_ccinfo_rec.delivery_type;
99 x_delivery_rec.carrier_id := p_del_ccinfo_rec.carrier_id;
100 x_delivery_rec.service_level := p_del_ccinfo_rec.service_level;
101 x_delivery_rec.mode_of_transport := p_del_ccinfo_rec.mode_of_transport;
102 x_delivery_rec.shipment_direction := p_del_ccinfo_rec.shipment_direction;
103 x_delivery_rec.party_id := p_del_ccinfo_rec.party_id;
104 x_delivery_rec.shipping_control := p_del_ccinfo_rec.shipping_control;
105
106 x_delivery_rec.fob_code := p_del_addnl_rec.fob_code;
107 x_delivery_rec.freight_terms_code := p_del_addnl_rec.freight_terms_code;
108 x_delivery_rec.loading_sequence := p_del_addnl_rec.loading_sequence;
109 x_delivery_rec.gross_weight := p_del_addnl_rec.gross_weight;
110 x_delivery_rec.weight_uom_code := p_del_addnl_rec.weight_uom_code;
111 x_delivery_rec.ignore_for_planning := p_del_addnl_rec.ignore_for_planning;
112
113 RETURN x_delivery_rec;
114 END Copy_Record;
115
116
117 --========================================================================
118 -- PROCEDURE : Schedule_Batch
119 --
120 -- PARAMETERS: errbuf Concurrent request error buffer
121 -- retcode Concurrent request return code
122 -- p_batch_id Concurrent submission batch id
123 -- p_log_level Concurrent request log level
124 --
125 -- COMMENT : This procedure is the entry point of the MDC SRS request.
126 -- The procedure accepts the batch id for which consolidation
127 -- is requested.
128 --========================================================================
129 PROCEDURE Schedule_Batch(
130 errbuf OUT NOCOPY VARCHAR2,
131 retcode OUT NOCOPY VARCHAR2,
132 p_batch_id IN NUMBER,
133 p_log_level IN NUMBER)
134 IS
135
136 l_return_status VARCHAR2(1);
137 l_num_errors NUMBER := 0;
138 l_num_warnings NUMBER := 0;
139
140 l_msg_count NUMBER;
141 l_msg_data VARCHAR2(32767);
142 l_status VARCHAR2(10);
143 l_temp BOOLEAN;
144 l_completion_status VARCHAR2(10);
145
146 l_delivery_tab WSH_FTE_CONSTRAINT_FRAMEWORK.delivery_ccinfo_tab_type;
147 l_delivery_addnl_attr_tab addnl_del_attr_tab_type;
148 l_sel_del_attr select_del_flags_rec_type;
149 l_failed_records WSH_UTIL_CORE.id_tab_type;
150 l_failed_index NUMBER;
151 l_failed_del_id NUMBER;
152 l_group_by_flags group_by_flags_rec_type;
153 l_hash_string VARCHAR2(1000);
154 l_hash_value NUMBER;
155 l_group_index NUMBER;
156 l_group_tab grp_attr_tab_type;
157 l_del_index NUMBER;
158 l_cont_cnt NUMBER;
159 l_trip_id NUMBER;
160 l_unassign_trip_tab WSH_NEW_DELIVERIES_PVT.Delivery_Attr_Tbl_Type;
161 l_action_params WSH_DELIVERIES_GRP.action_parameters_rectype;
162 l_rating_in_param_rec WSH_FTE_INTEGRATION.rate_del_in_param_rec;
163 l_rating_out_param_rec WSH_FTE_INTEGRATION.rate_del_out_param_rec;
164 l_consol_trip_id WSH_UTIL_CORE.id_tab_type;
165 l_consol_del_id WSH_UTIL_CORE.id_tab_type;
166 l_del_all WSH_UTIL_CORE.id_tab_type;
167 l_trips_all WSH_UTIL_CORE.id_tab_type;
168 l_dummy_exception_list WSH_UTIL_CORE.Column_Tab_Type;
169 l_dummy_in_ids WSH_UTIL_CORE.id_tab_type;
170 l_dummy_dlvy_asgnd_lines WSH_FTE_CONSTRAINT_FRAMEWORK.detail_ccinfo_tab_type;
171 l_dummy_tar_trip WSH_FTE_CONSTRAINT_FRAMEWORK.trip_ccinfo_rec_type;
172 l_dummy_tar_tstops WSH_FTE_CONSTRAINT_FRAMEWORK.target_tripstop_cc_rec_type;
173 l_dummy_tar_t_asgn_dels WSH_FTE_CONSTRAINT_FRAMEWORK.delivery_ccinfo_tab_type;
174 l_dummy_tar_t_dlvy_lines WSH_FTE_CONSTRAINT_FRAMEWORK.detail_ccinfo_tab_type;
175 l_dummy_tar_t_incl_stops WSH_FTE_CONSTRAINT_FRAMEWORK.stop_ccinfo_tab_type;
176 l_dummy_val_result VARCHAR2(100);
177 l_valid_hash BOOLEAN := FALSE;
178 l_hash_power NUMBER := 24;
179 l_hash_size NUMBER;
180 l_hash_base NUMBER := 1;
181 l_delivery_out_rec WSH_DELIVERIES_GRP.Delivery_Action_Out_Rec_Type;
182 l_defaults_rec WSH_DELIVERIES_GRP.default_parameters_rectype;
183
184 l_init_msg_list VARCHAR2(1000);
185 l_failed_lines WSH_FTE_COMP_CONSTRAINT_PKG.failed_line_tab_type;
186 l_line_groups WSH_FTE_COMP_CONSTRAINT_PKG.line_group_tab_type;
187 l_group_info WSH_FTE_COMP_CONSTRAINT_PKG.cc_group_tab_type;
188 l_action_prms WSH_TRIPS_GRP.action_parameters_rectype;
189 l_trip_out WSH_TRIPS_GRP.tripActionOutRecType;
190 l_commit VARCHAR2(10);
191 l_api_version_number NUMBER := 1.0;
192 l_debug_on BOOLEAN;
193 l_exc_complete EXCEPTION;
194 l_org_type VARCHAR2(3);
195 l_cms_org_flag BOOLEAN := FALSE;
196 l_del_cms VARCHAR2(3);
197 l_cms_cur_val VARCHAR2(1);
198
199 i NUMBER;
200 j NUMBER;
201 k NUMBER;
202 m NUMBER;
203
204 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'SCHEDULE_BATCH';
205 l_api_session_name CONSTANT VARCHAR2(150) := G_PKG_NAME ||'.' || l_module_name;
206 l_message VARCHAR2(2000);
207
208 CURSOR Get_Exist_trip(p_delivery_id NUMBER)
209 IS
210 SELECT COUNT(DELIVERY_LEG_ID)
211 FROM WSH_DELIVERY_LEGS
212 WHERE DELIVERY_ID = p_delivery_id;
213
214 CURSOR Get_Trip(c_delivery_id NUMBER)
215 IS
216 SELECT WTS.TRIP_ID
217 FROM WSH_TRIP_STOPS WTS, WSH_DELIVERY_LEGS WDL
218 WHERE WTS.STOP_ID = WDL.PICK_UP_STOP_ID
219 AND WDL.DELIVERY_ID = c_delivery_id;
220
221
222 CURSOR Get_Cms_Org_Type(c_org_id NUMBER)
223 IS
224 SELECT CARRIER_MANIFESTING_FLAG
225 FROM MTL_PARAMETERS
226 WHERE ORGANIZATION_ID = c_org_id;
227
228 BEGIN
229
230 IF WSH_UTIL_CORE.G_START_OF_SESSION_API is null
231 THEN
232 WSH_UTIL_CORE.G_START_OF_SESSION_API := l_api_session_name;
233 END IF;
234
235 WSH_UTIL_CORE.Enable_Concurrent_Log_Print;
236
237 IF p_log_level IS NOT NULL
238 THEN
239 WSH_UTIL_CORE.Set_Log_Level(p_log_level);
240 WSH_UTIL_CORE.PrintMsg('p_log_level is ' || to_char(p_log_level));
241 END IF;
242
243 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
244 IF l_debug_on IS NULL
245 THEN
246 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
247 END IF;
248
249 IF p_batch_id IS NULL
250 THEN
251 WSH_UTIL_CORE.PrintMsg('Batch Id is null');
252 WSH_UTIL_CORE.PrintMsg('Error occurred in WSH_MDC_SRS.Schedule_Batch');
253 l_temp := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR','');
254 errbuf := 'Error occurred in WSH_MDC_SRS.Schedule_Batch';
255 retcode := '2';
256 raise FND_API.G_EXC_ERROR;
257 END IF;
258
259 IF l_debug_on THEN
260 WSH_DEBUG_SV.push(l_module_name);
261 WSH_DEBUG_SV.log(l_module_name,'P_BATCH_ID',P_BATCH_ID);
262 WSH_DEBUG_SV.log(l_module_name,'P_LOG_LEVEL',P_LOG_LEVEL);
263 WSH_DEBUG_SV.log(l_module_name,'Current Time is ',SYSDATE);
264 END IF;
265
266
267 l_commit := FND_API.G_FALSE;
268 l_completion_status := 'NORMAL';
269
270 -- get delivery selection parameters for the batch
271 IF l_debug_on THEN
272 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_MDC_SRS.Get_Batch_Parameters',WSH_DEBUG_SV.C_PROC_LEVEL);
273 END IF;
274
275 Get_Batch_Parameters(
276 p_batch_id => p_batch_id,
277 x_sel_del_attr => l_sel_del_attr,
278 x_return_status => l_return_status);
279
280 IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_SUCCESS)
281 THEN
282 IF l_debug_on THEN
283 WSH_DEBUG_SV.logmsg(l_module_name, 'Got Batch Parameters' );
284 END IF;
285 ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR)
286 OR (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)
287 THEN
288 WSH_UTIL_CORE.PrintMsg('Error occurred in WSH_MDC_SRS.Get_Batch_Parameters');
289 l_temp := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR','');
290 errbuf := 'Error occurred in WSH_MDC_SRS.Get_Batch_Parameters';
291 retcode := '2';
292 IF l_debug_on THEN
293 WSH_DEBUG_SV.logmsg(l_module_name, 'Error occurred in WSH_MDC_SRS.Get_Batch_Parameters');
294 END IF;
295 raise FND_API.G_EXC_ERROR;
296 END IF;
297
298
299 -- check if the organization is TPW org
300 IF l_debug_on THEN
301 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_EXTERNAL_INTERFACE.SV.Get_Warehouse_type',WSH_DEBUG_SV.C_PROC_LEVEL);
302 END IF;
303
304 l_org_type := WSH_EXTERNAL_INTERFACE_SV.Get_Warehouse_type(
305 p_organization_id => l_sel_del_attr.org_id,
306 x_return_status => l_return_status);
307
308 IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING)
309 THEN
310 IF l_debug_on THEN
311 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_EXTERNAL_INTERFACE.SV.Get_Warehouse_type returned with warnings');
312 END IF;
313 ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR)
314 OR (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)
315 THEN
316 WSH_UTIL_CORE.PrintMsg('Error occurred in WSH_EXTERNAL_INTERFACE.SV.Get_Warehouse_type');
317 l_temp := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR','WSH_EXTERNAL_INTERFACE.SV.Get_Warehouse_type');
318 errbuf := 'Error occurred in WSH_EXTERNAL_INTERFACE.SV.Get_Warehouse_type';
319 retcode := '2';
320 IF l_debug_on THEN
321 WSH_DEBUG_SV.logmsg(l_module_name, 'Error occurred in WSH_EXTERNAL_INTERFACE.SV.Get_Warehouse_type');
322 END IF;
323 raise FND_API.G_EXC_ERROR;
324 END IF;
325
326 IF l_debug_on THEN
327 WSH_DEBUG_SV.logmsg(l_module_name, 'Organization type '||l_org_type);
328 END IF;
329
330 IF l_org_type = 'TPW'
331 THEN
332 WSH_UTIL_CORE.PrintMsg('The Organization is TPW enabled. Quitting Consolidation.');
333 raise l_exc_complete;
334 END IF;
335
336 -- set the organization cms enabled flag
337
338 OPEN Get_Cms_Org_Type(l_sel_del_attr.org_id);
339 FETCH Get_Cms_Org_Type INTO l_org_type;
340 CLOSE Get_Cms_Org_Type;
341
342 IF l_debug_on THEN
343 WSH_DEBUG_SV.logmsg(l_module_name, 'Organization CMS value '||l_org_type);
344 END IF;
345
346 IF l_org_type = 'Y'
347 THEN
348 l_cms_org_flag := TRUE;
349 END IF;
350
351 -- get deliveries for the set of parameters
352 IF l_debug_on THEN
353 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_MDC_SRS.Get_Deliveries',WSH_DEBUG_SV.C_PROC_LEVEL);
354 END IF;
355
356 Get_Deliveries(
357 p_sel_del_attr => l_sel_del_attr,
358 x_delivery_tab => l_delivery_tab,
359 x_delivery_addnl_attr_tab => l_delivery_addnl_attr_tab,
360 x_return_status => l_return_status);
361
362 IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_SUCCESS)
363 THEN
364 IF l_debug_on THEN
365 WSH_DEBUG_SV.logmsg(l_module_name, 'Got Deliveries' );
366 END IF;
367 ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)
368 THEN
369 WSH_UTIL_CORE.PrintMsg('Error occurred in WSH_MDC_SRS.Get_Deliveries');
370 l_temp := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR','');
371 errbuf := 'Error occurred in WSH_MDC_SRS.Get_Deliveries';
372 retcode := '2';
373 raise FND_API.G_EXC_ERROR;
374 ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR)
375 THEN
376 WSH_UTIL_CORE.PrintMsg('No deliveries to consolidate');
377 l_temp := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR','');
378 errbuf := 'Error occurred in WSH_MDC_SRS.Get_Deliveries';
379 retcode := '2';
380 raise FND_API.G_EXC_ERROR;
381 END IF;
382
383 IF l_delivery_tab.COUNT = 0
384 THEN
385 WSH_UTIL_CORE.PrintMsg('No deliveries selected for consolidation');
386 raise l_exc_complete;
387 ELSE
388 WSH_UTIL_CORE.PrintMsg('The following deliveries are selected for consolidation');
389 i := l_delivery_tab.FIRST;
390 WHILE i IS NOT NULL
391 LOOP
392 WSH_UTIL_CORE.PrintMsg(' Delivery: '||l_delivery_tab(i).delivery_id);
393 i := l_delivery_tab.next(i);
394 END LOOP;
395 END IF;
396
397
398 -- check if deliveries are assigned to trips.
399 -- If delivery has single leg, unassign it
400 -- If delivery has multiple legs, ignore delivery
401 -- IF cms enabled organization and cms enabled carrier, ignore delivery
402
403 l_failed_index := 0;
404 j := 0;
405 IF((l_sel_del_attr.inc_del_assgnd_trip_flag = 'Y')
406 OR(l_cms_org_flag))
407 THEN
408 i := l_delivery_tab.FIRST;
409 WHILE i IS NOT NULL
410 LOOP
411
412 -- check if delivery is carrier manifested
413 IF(l_cms_org_flag)
414 THEN
415
416 IF l_debug_on THEN
417 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_EXTERNAL_INTERFACE.SV.Get_Warehouse_type',WSH_DEBUG_SV.C_PROC_LEVEL);
418 END IF;
419
420 l_del_cms := null;
421 l_del_cms := WSH_EXTERNAL_INTERFACE_SV.Get_Warehouse_Type
422 ( p_organization_id => l_delivery_tab(i).organization_id,
423 p_delivery_id => l_delivery_tab(i).delivery_id,
424 p_carrier_id => l_delivery_tab(i).carrier_id,
425 x_return_status => l_return_status
426 );
427 IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING)
428 THEN
429 IF l_debug_on THEN
430 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_EXTERNAL_INTERFACE.SV.Get_Warehouse_type returned with warnings');
431 END IF;
432 ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR)
433 OR (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)
434 THEN
435 WSH_UTIL_CORE.PrintMsg('Error occurred in WSH_EXTERNAL_INTERFACE.SV.Get_Warehouse_type');
436 WSH_UTIL_CORE.PrintMsg('Failed to get the manifest information. Removing delivery '||l_delivery_tab(i).delivery_id||' from consolidation');
437 l_completion_status := 'WARNING';
438 retcode := '1';
439
440 l_failed_records(l_failed_index) := l_delivery_tab(i).delivery_id;
441 k := l_delivery_tab.next(i);
442 l_delivery_tab.delete(i);
443 l_delivery_addnl_attr_tab.delete(i);
444 i := k;
445 l_failed_index := l_failed_index + 1;
446 GOTO LOOP_END;
447
448 END IF;
449
450
451 IF l_del_cms = 'CMS'
452 THEN
453 WSH_UTIL_CORE.PrintMsg('Delivery '||l_delivery_tab(i).delivery_id||' is carrier manifesting enabled. Removing from consolidation');
454
455 k := l_delivery_tab.next(i);
456 l_delivery_tab.delete(i);
457 l_delivery_addnl_attr_tab.delete(i);
458 i := k;
459 GOTO LOOP_END;
460 END IF;
461 END IF;
462
463
464
465 IF(l_sel_del_attr.inc_del_assgnd_trip_flag = 'Y')
466 THEN
467
468 OPEN Get_Exist_Trip(l_delivery_tab(i).delivery_id);
469 FETCH Get_Exist_Trip INTO l_cont_cnt;
470 CLOSE Get_Exist_Trip;
471
472
473 IF l_cont_cnt > 0
474 THEN
475 -- The delivery is assigned to trips
476 IF l_cont_cnt = 1
477 THEN
478 -- If the delivery has just one leg, unassign it
479 OPEN Get_Trip(l_delivery_tab(i).delivery_id);
480 FETCH Get_Trip INTO l_trip_id;
481 CLOSE Get_Trip;
482
483 IF l_debug_on THEN
484 WSH_DEBUG_SV.logmsg(l_module_name,'Unassigning delivery '||l_delivery_tab(i).delivery_id||' from it''s delivery leg');
485 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERIES_GRP.Delivery_Action',WSH_DEBUG_SV.C_PROC_LEVEL);
486 END IF;
487 l_unassign_trip_tab(1) := Copy_Record(l_delivery_tab(i), l_delivery_addnl_attr_tab(i));
488
489 l_action_params.action_code := 'UNASSIGN-TRIP';
490 l_action_params.caller := 'FTE_CONSOL_SRS';
491 l_action_params.trip_id := l_trip_id;
492 WSH_DELIVERIES_GRP.Delivery_Action
493 ( p_api_version_number => l_api_version_number,
494 p_init_msg_list => l_init_msg_list,
495 p_commit => l_commit,
496 p_action_prms => l_action_params,
497 p_rec_attr_tab => l_unassign_trip_tab,
498 x_delivery_out_rec => l_delivery_out_rec,
499 x_defaults_rec => l_defaults_rec,
500 x_return_status => l_return_status,
501 x_msg_count => l_msg_count,
502 x_msg_data => l_msg_data
503 );
504
505 IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_SUCCESS)
506 THEN
507 IF l_debug_on THEN
508 WSH_DEBUG_SV.logmsg(l_module_name, 'Delivery '||l_delivery_tab(i).delivery_id||' unassigned successfully');
509 END IF;
510 ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR)
511 OR (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)
512 THEN
513 WSH_UTIL_CORE.PrintMsg('Error occurred in WSH_DELIVERIES_GRP.Delivery_Action');
514 FOR m in 1..l_msg_count
515 LOOP
516 l_message := fnd_msg_pub.get(m,'F');
517 l_message := replace(l_message,chr(0),' ');
518 WSH_UTIL_CORE.PrintMsg(l_message);
519 IF l_debug_on THEN
520 WSH_DEBUG_SV.logmsg(l_module_name, l_message);
521 END IF;
522 END LOOP;
523
524 fnd_msg_pub.delete_msg();
525
526 WSH_UTIL_CORE.PrintMsg('Failed to unassign from trip. Removing delivery '||l_delivery_tab(i).delivery_id||' from consolidation');
527 l_completion_status := 'WARNING';
528 retcode := '1';
529
530 l_failed_records(l_failed_index) := l_delivery_tab(i).delivery_id;
531 k := l_delivery_tab.next(i);
532 l_delivery_tab.delete(i);
533 l_delivery_addnl_attr_tab.delete(i);
534 i := k;
535 l_failed_index := l_failed_index + 1;
536 GOTO LOOP_END;
537
538
539 ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING)
540 THEN
541 IF l_debug_on THEN
542 WSH_DEBUG_SV.logmsg(l_module_name, 'Delivery '||l_delivery_tab(i).delivery_id||' unassigned from trip with warnings');
543 FOR m in 1..l_msg_count
544 LOOP
545 l_message := fnd_msg_pub.get(m,'F');
546 l_message := replace(l_message,chr(0),' ');
547 WSH_DEBUG_SV.logmsg(l_module_name, l_message);
548 END LOOP;
549 END IF;
550 fnd_msg_pub.delete_msg();
551 END IF;
552
553 ELSE
554 WSH_UTIL_CORE.PrintMsg('Delivery '||l_delivery_tab(i).delivery_id||' has mulitple legs. Removing from consolidation');
555
556 k := l_delivery_tab.next(i);
557 l_delivery_tab.delete(i);
558 l_delivery_addnl_attr_tab.delete(i);
559 i := k;
560 GOTO LOOP_END;
561 END IF;
562 END IF;
563 END IF;
564
565 i := l_delivery_tab.next(i);
566 <<LOOP_END>>
567 null;
568 END LOOP;
569 END IF;
570
571
572 IF l_debug_on THEN
573 WSH_DEBUG_SV.logmsg(l_module_name,'Checking if FTE is installed');
574 END IF;
575
576 IF WSH_UTIL_CORE.fte_is_installed = 'Y'
577 THEN
578 --{
579 -- group the deliveries based on constraints
580 WSH_UTIL_CORE.PrintMsg('Grouping deliveries based on constraints');
581
582 IF l_debug_on THEN
583 WSH_DEBUG_SV.logmsg(l_module_name,'FTE is installed');
584 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_FTE_CONSTRAINT_FRAMEWORK.validate_constraint_dlvy',WSH_DEBUG_SV.C_PROC_LEVEL);
585 END IF;
586
587 WSH_FTE_CONSTRAINT_FRAMEWORK.validate_constraint_dlvy(
588 p_init_msg_list => l_init_msg_list,
589 p_action_code => 'ACM',
590 p_exception_list => l_dummy_exception_list,
591 p_in_ids => l_dummy_in_ids,
592 p_delivery_info => l_delivery_tab,
593 p_dlvy_assigned_lines => l_dummy_dlvy_asgnd_lines,
594 p_target_trip => l_dummy_tar_trip,
595 p_target_tripstops => l_dummy_tar_tstops,
596 p_target_trip_assign_dels => l_dummy_tar_t_asgn_dels,
597 p_target_trip_dlvy_lines => l_dummy_tar_t_dlvy_lines,
598 p_target_trip_incl_stops => l_dummy_tar_t_incl_stops,
599 x_validate_result => l_dummy_val_result,
600 x_line_groups => l_line_groups,
601 x_group_info => l_group_info,
602 x_failed_lines => l_failed_lines,
603 x_msg_count => l_msg_count,
604 x_msg_data => l_msg_data,
605 x_return_status => l_return_status);
606
607 IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_SUCCESS)
608 OR (l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR)
609 THEN
610 IF l_debug_on THEN
611 WSH_DEBUG_SV.logmsg(l_module_name, 'validate constraints call successful');
612 WSH_DEBUG_SV.logmsg(l_module_name, 'Deliveries grouped based on constraints');
613 END IF;
614 ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)
615 THEN
616 WSH_UTIL_CORE.PrintMsg('Error occurred in WSH_FTE_CONSTRAINT_FRAMEWORK.validate_constraint_dlvy');
617 FOR m in 1..l_msg_count
618 LOOP
619 l_message := fnd_msg_pub.get(m,'F');
620 l_message := replace(l_message,chr(0),' ');
621 WSH_UTIL_CORE.PrintMsg(l_message);
622 IF l_debug_on THEN
623 WSH_DEBUG_SV.logmsg(l_module_name, l_message);
624 END IF;
625 END LOOP;
626
627 fnd_msg_pub.delete_msg();
628 l_temp := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR','');
629 errbuf := 'Error occurred in WSH_FTE_CONSTRAINT_FRAMEWORK.validate_constraint_dlvy';
630 retcode := '2';
631
632 raise FND_API.G_EXC_ERROR;
633 ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING)
634 THEN
635 IF l_debug_on THEN
636 WSH_DEBUG_SV.logmsg(l_module_name, 'validate constraints call successful with following warnings');
637 FOR m in 1..l_msg_count
638 LOOP
639 l_message := fnd_msg_pub.get(m,'F');
640 l_message := replace(l_message,chr(0),' ');
641 WSH_DEBUG_SV.logmsg(l_module_name, l_message);
642 END LOOP;
643 END IF;
644 fnd_msg_pub.delete_msg();
645 END IF;
646
647
648 -- X_line_groups will have the entity_line_id (delivery_id) and line_group_id
649 -- Updating the group_id in p_delivery_tab based on the output from x_line_groups
650 i := l_line_groups.FIRST;
651 -- FOR i in 1 .. l_line_groups.COUNT
652 WHILE i IS NOT NULL
653 LOOP
654 l_delivery_addnl_attr_tab(l_line_groups(i).entity_line_id).group_id := l_line_groups(i).line_group_id;
655 WSH_UTIL_CORE.PrintMsg(' Delivery: '||l_line_groups(i).entity_line_id||', Group: '||l_line_groups(i).line_group_id);
656 i := l_line_groups.NEXT(i);
657 END LOOP;
658
659 -- Remove failed records
660 i := l_failed_lines.FIRST;
661 l_failed_index := l_failed_records.last + 1;
662 -- FOR i in 1 .. l_failed_lines.COUNT
663 WHILE i IS NOT NULL
664 LOOP
665 --{
666 l_completion_status := 'WARNING';
667 retcode := '1';
668
669 l_failed_del_id := l_failed_lines(i).entity_line_id;
670 l_failed_records(l_failed_index) := l_failed_del_id;
671 l_delivery_tab.delete(l_failed_del_id);
672 l_delivery_addnl_attr_tab.delete(l_failed_del_id);
673 l_failed_index := l_failed_index + 1;
674 i := l_failed_lines.NEXT(i);
675 WSH_UTIL_CORE.PrintMsg('Delivery Id: '||l_failed_del_id||' failed constraint validation. Removing from consolidation');
676 --}
677 END LOOP;
678
679 IF l_delivery_tab.COUNT = 0
680 THEN
681 WSH_UTIL_CORE.PrintMsg('All deliveries failed validation. Terminating concurrent request');
682 raise l_exc_complete;
683 END IF;
684
685 --} -- if fte is installed
686 END IF;
687
688
689 -- get grouping rule
690 IF l_debug_on THEN
691 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_MDC_SRS.Get_Grouping_Attrs',WSH_DEBUG_SV.C_PROC_LEVEL);
692 END IF;
693
694 Get_Grouping_Attrs(
695 p_grouping_rule_id => l_sel_del_attr.rule_id,
696 x_group_by_flags => l_group_by_flags,
697 x_return_status => l_return_status);
698
699 IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_SUCCESS)
700 THEN
701 IF l_debug_on THEN
702 WSH_DEBUG_SV.logmsg(l_module_name, 'Got Grouping Attrs' );
703 END IF;
704 ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR)
705 OR (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)
706 THEN
707 WSH_UTIL_CORE.PrintMsg('Error occurred in WSH_MDC_SRS.Get_Grouping_Attrs');
708 l_temp := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR','');
709 errbuf := 'Error occurred in WSH_MDC_SRS.Get_Grouping_Attrs';
710 retcode := '2';
711 raise FND_API.G_EXC_ERROR;
712 END IF;
713
714
715 -- set the intermediate location for the deliveries
716 IF l_debug_on THEN
717 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_MDC_SRS.Set_Intermediate_Location',WSH_DEBUG_SV.C_PROC_LEVEL);
718 END IF;
719
720 Set_Intermediate_Location(
721 p_consol_loc_id => l_sel_del_attr.consol_ship_to_loc_id,
722 p_override_ship_to_flag => l_sel_del_attr.consol_shipto_override_flag,
723 p_rule_zone_id => l_group_by_flags.ship_to_zone,
724 x_delivery_tab => l_delivery_tab,
725 x_delivery_addnl_attr_tab => l_delivery_addnl_attr_tab,
726 x_failed_records => l_failed_records,
727 x_return_status => l_return_status);
728
729 IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_SUCCESS)
730 THEN
731 IF l_debug_on THEN
732 WSH_DEBUG_SV.logmsg(l_module_name, 'Set Intermediate call successful');
733 END IF;
734 ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING)
735 THEN
736 IF l_debug_on THEN
737 WSH_DEBUG_SV.logmsg(l_module_name, 'Set Intermediate Location call completed with warnings');
738 END IF;
739 l_completion_status := 'WARNING';
740 retcode := '1';
741 ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR)
742 OR (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)
743 THEN
744 WSH_UTIL_CORE.PrintMsg('Error occurred in WSH_MDC_SRS.Set_Intermediate_Location');
745 l_temp := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR','');
746 errbuf := 'Error occurred in WSH_MDC_SRS.Set_Intermediate_Location';
747 retcode := '2';
748 raise FND_API.G_EXC_ERROR;
749 END IF;
750
751 IF l_delivery_tab.COUNT = 0
752 THEN
753 WSH_UTIL_CORE.PrintMsg('All deliveries failed to get intermediate location. Terminating concurrent request');
754 raise l_exc_complete;
755 END IF;
756
757
758 WSH_UTIL_CORE.PrintMsg('Grouping deliveries based on the rule');
759
760 WHILE(l_valid_hash <> TRUE)
761 LOOP
762 --{
763 -- reset loop variables, increament the hash size
764 l_valid_hash := TRUE;
765 l_hash_power := l_hash_power + 1;
766 l_hash_size := power(2,l_hash_power);
767 IF l_debug_on THEN
768 WSH_DEBUG_SV.logmsg(l_module_name, 'Creating hash values with hash power = '||l_hash_power);
769 END IF;
770 l_hash_base := 1;
771 l_group_tab.DELETE;
772 j := 0;
773 i := l_delivery_tab.FIRST;
774 --FOR l_index in 1 .. l_delivery_tab.COUNT
775 WHILE i IS NOT NULL
776 LOOP
777 --{
778 -- get hash value for the record
779 --{
780 IF l_debug_on THEN
781 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_MDC_SRS.Get_Hash_Value',WSH_DEBUG_SV.C_PROC_LEVEL);
782 END IF;
783 Get_Hash_Value(
784 x_delivery_rec => l_delivery_tab(i),
785 x_delivery_addnl_attr_rec => l_delivery_addnl_attr_tab(i),
786 p_group_by_flags => l_group_by_flags,
787 p_hash_base => l_hash_base,
788 p_hash_size => l_hash_size,
789 x_hash_string => l_hash_string,
790 x_hash_value => l_hash_value,
791 x_return_status => l_return_status);
792
793 IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_SUCCESS)
794 THEN
795 IF l_debug_on THEN
796 WSH_DEBUG_SV.logmsg(l_module_name, 'Got Hash Value' );
797 END IF;
798 ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR)
799 OR (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)
800 THEN
801 WSH_UTIL_CORE.PrintMsg('Error occurred in WSH_MDC_SRS.Get_Hash_Value');
802 l_temp := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR','');
803 errbuf := 'Error occurred in WSH_MDC_SRS.Get_Hash_Value';
804 retcode := '2';
805 raise FND_API.G_EXC_ERROR;
806 END IF;
807
808
809 l_delivery_addnl_attr_tab(i).hash_string := l_hash_string;
810 l_delivery_addnl_attr_tab(i).hash_value := l_hash_value;
811
812 -- group deliveries based on hash value
813 l_group_index := l_hash_value;
814 IF l_group_tab.EXISTS(l_group_index)
815 THEN
816 --{
817 IF (l_group_tab(l_group_index).hash_string <> l_hash_string)
818 THEN
819 --{
820 -- hash value not unique
821 IF l_debug_on THEN
822 WSH_DEBUG_SV.logmsg(l_module_name, 'Hash value not unique. Rebuilding hash table with bigger hash size');
823 END IF;
824 WSH_UTIL_CORE.PrintMsg('Hash conflict. Dropping and rebuilding rule based grouping.');
825 l_valid_hash := FALSE;
826 EXIT;
827 --}
828 END IF;
829 -- add delivery to existing group
830 IF l_debug_on THEN
831 WSH_DEBUG_SV.logmsg(l_module_name, 'Delivery '||l_delivery_addnl_attr_tab(i).delivery_id||' added to existing hash group '||l_group_tab(l_group_index).group_id);
832 END IF;
833 WSH_UTIL_CORE.PrintMsg(' Delivery: '||l_delivery_addnl_attr_tab(i).delivery_id||', Hash Group: '||l_group_tab(l_group_index).group_id);
834 l_delivery_addnl_attr_tab(i).group_id := l_group_tab(l_group_index).group_id;
835 l_del_index := l_group_tab(l_group_index).delivery_list.LAST;
836 l_group_tab(l_group_index).delivery_list(l_del_index+1) := l_delivery_addnl_attr_tab(i).delivery_id;
837 --}
838 ELSE
839 --{
840 l_group_tab(l_group_index).hash_value := l_delivery_addnl_attr_tab(i).hash_value;
841 l_group_tab(l_group_index).hash_string := l_delivery_addnl_attr_tab(i).hash_string;
842 SELECT WSH_DELIVERY_GROUP_S.nextval INTO l_group_tab(l_group_index).group_id FROM DUAL;
843 l_delivery_addnl_attr_tab(i).group_id := l_group_tab(l_group_index).group_id;
844 IF l_debug_on THEN
845 WSH_DEBUG_SV.logmsg(l_module_name, 'Delivery '||l_delivery_addnl_attr_tab(i).delivery_id||' added to new hash group '||l_group_tab(l_group_index).group_id);
846 END IF;
847 WSH_UTIL_CORE.PrintMsg(' Delivery: '||l_delivery_addnl_attr_tab(i).delivery_id||', Hash Group: '||l_group_tab(l_group_index).group_id);
848 l_group_tab(l_group_index).delivery_list(0) := l_delivery_addnl_attr_tab(i).delivery_id;
849 --}
850 END IF;
851 --}
852
853 i := l_delivery_tab.NEXT(i);
854 --}
855 END LOOP;
856
857 --}
858 END LOOP; -- created hash grouping
859
860
861 -- create consolidations
862 IF l_debug_on THEN
863 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_MDC_SRS.Create_Consolidations',WSH_DEBUG_SV.C_PROC_LEVEL);
864 END IF;
865 Create_Consolidations(
866 x_delivery_tab => l_delivery_tab,
867 x_delivery_addnl_attr_tab => l_delivery_addnl_attr_tab,
868 p_group_tab => l_group_tab,
869 p_max_trip_weight => l_sel_del_attr.max_trip_weight,
870 p_max_weight_uom => l_sel_del_attr.max_trip_weight_uom,
871 p_trip_name_prefix => l_sel_del_attr.trip_name_prefix,
872 x_consol_trip_id => l_consol_trip_id,
873 x_consol_del_id => l_consol_del_id,
874 x_trips_all => l_trips_all,
875 x_failed_records => l_failed_records,
876 x_return_status => l_return_status);
877
878 IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_SUCCESS)
879 THEN
880 IF l_debug_on THEN
881 WSH_DEBUG_SV.logmsg(l_module_name, 'Create Consolidations Successful');
882 END IF;
883 ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING)
884 THEN
885 IF l_debug_on THEN
886 WSH_DEBUG_SV.logmsg(l_module_name, 'Create Consolidations completed with warnings');
887 END IF;
888 l_completion_status := 'WARNING';
889 retcode := '1';
890
891 ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR)
892 OR (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)
893 THEN
894 WSH_UTIL_CORE.PrintMsg('Error occurred in WSH_MDC_SRS.Create_Consolidations');
895 l_temp := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR','');
896 errbuf := 'Error occurred in WSH_MDC_SRS.Create_Consolidations';
897 retcode := '2';
898 raise FND_API.G_EXC_ERROR;
899 END IF;
900
901
902 -- create deconsolidation trips
903 IF(l_sel_del_attr.create_deconsol_trips_flag = 'Y')
904 THEN
905 IF l_debug_on THEN
906 WSH_DEBUG_SV.logmsg(l_module_name,'Creating deconsolidation trips');
907 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_MDC_SRS.Create_Deconsol_Trips',WSH_DEBUG_SV.C_PROC_LEVEL);
908 END IF;
909
910 Create_Deconsol_Trips(
911 x_delivery_tab => l_delivery_tab,
912 x_delivery_addnl_attr_tab => l_delivery_addnl_attr_tab,
913 p_consol_trip_id => l_consol_trip_id,
914 p_trip_name_prefix => l_sel_del_attr.trip_name_prefix,
915 x_trips_all => l_trips_all,
916 x_return_status => l_return_status);
917
918 IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_SUCCESS)
919 THEN
920 IF l_debug_on THEN
921 WSH_DEBUG_SV.logmsg(l_module_name, 'Create Deconsolidation Trips Successful');
922 END IF;
923 ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING)
924 THEN
925 IF l_debug_on THEN
926 WSH_DEBUG_SV.logmsg(l_module_name, 'Create Deconsolidation Trips completed with warnings');
927 END IF;
928 l_completion_status := 'WARNING';
929 retcode := '1';
930 ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR)
931 OR (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)
932 THEN
933 WSH_UTIL_CORE.PrintMsg('Error occurred in WSH_MDC_SRS.Create_Deconsol_Trips');
934 l_completion_status := 'WARNING';
935 retcode := '1';
936 END IF;
937
938 END IF;
939
940
941
942 -- route trips
943 IF(l_sel_del_attr.route_trips_flag = 'Y')
944 THEN
945 WSH_UTIL_CORE.PrintMsg('Routing the trips created');
946 IF l_debug_on THEN
947 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIPS_ACTIONS.PROCESS_CARRIER_SELECTION',WSH_DEBUG_SV.C_PROC_LEVEL);
948 END IF;
949 /*
950 WSH_TRIPS_ACTIONS.PROCESS_CARRIER_SELECTION(
951 p_init_msg_list => l_init_msg_list,
952 p_trip_id_tab => l_trips_all,
953 p_caller => 'FTE',
954 x_msg_count => l_msg_count,
955 x_msg_data => l_msg_data,
956 x_return_status => l_return_status);
957 */
958
959 l_action_prms.caller := 'WSH-GROUP';
960 l_action_prms.action_code := 'SELECT-CARRIER';
961
962 WSH_INTERFACE_GRP.Trip_Action(
963 p_api_version_number => l_api_version_number,
964 p_init_msg_list => l_init_msg_list,
965 p_commit => l_commit,
966 p_entity_id_tab => l_trips_all,
967 p_action_prms => l_action_prms,
968 x_trip_out_rec => l_trip_out,
969 x_return_status => l_return_status,
970 x_msg_count => l_msg_count,
971 x_msg_data => l_msg_data);
972
973 IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_SUCCESS)
974 THEN
975 IF l_debug_on THEN
976 WSH_DEBUG_SV.logmsg(l_module_name, 'Routing Trips Successful');
977 END IF;
978 ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING)
979 THEN
980 IF l_debug_on THEN
981 WSH_DEBUG_SV.logmsg(l_module_name, 'Routing Trips completed with warnings');
982 END IF;
983 FOR m in 1..l_msg_count
984 LOOP
985 l_message := fnd_msg_pub.get(m,'F');
986 l_message := replace(l_message,chr(0),' ');
987 WSH_UTIL_CORE.PrintMsg(l_message);
988 IF l_debug_on THEN
989 WSH_DEBUG_SV.logmsg(l_module_name, l_message);
990 END IF;
991 END LOOP;
992
993 fnd_msg_pub.delete_msg();
994
995 l_completion_status := 'WARNING';
996 retcode := '1';
997 ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR)
998 OR (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)
999 THEN
1000 WSH_UTIL_CORE.PrintMsg('Error occurred in WSH_TRIPS_ACTIONS.PROCESS_CARRIER_SELECTION');
1001 FOR m in 1..l_msg_count
1002 LOOP
1003 l_message := fnd_msg_pub.get(m,'F');
1004 l_message := replace(l_message,chr(0),' ');
1005 WSH_UTIL_CORE.PrintMsg(l_message);
1006 IF l_debug_on THEN
1007 WSH_DEBUG_SV.logmsg(l_module_name, l_message);
1008 END IF;
1009 END LOOP;
1010
1011 fnd_msg_pub.delete_msg();
1012
1013 l_completion_status := 'WARNING';
1014 retcode := '1';
1015 END IF;
1016
1017 END IF;
1018
1019
1020 -- rate deliveries
1021 IF(l_sel_del_attr.rate_trips_flag = 'Y')
1022 THEN
1023
1024 j := 0;
1025 i := l_delivery_tab.FIRST;
1026 WHILE i IS NOT NULL
1027 LOOP
1028 l_del_all(j) := l_delivery_tab(i).delivery_id;
1029 j := j+ 1;
1030 i := l_delivery_tab.NEXT(i);
1031 END LOOP;
1032
1033 i := l_consol_del_id.FIRST;
1034 WHILE i IS NOT NULL
1035 LOOP
1036 l_del_all(j) := l_consol_del_id(i);
1037 j := j+1;
1038 i := l_consol_del_id.NEXT(i);
1039 END LOOP;
1040
1041
1042 WSH_UTIL_CORE.PrintMsg('Rating Deliveries');
1043 IF l_debug_on THEN
1044 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_FTE_INTEGRATION.Rate_Delivery',WSH_DEBUG_SV.C_PROC_LEVEL);
1045 END IF;
1046
1047 l_rating_in_param_rec.seq_tender_flag := 'Y';
1048 l_rating_in_param_rec.action := 'RATE';
1049 l_rating_in_param_rec.delivery_id_list := l_del_all;
1050 WSH_FTE_INTEGRATION.Rate_Delivery (
1051 p_api_version => l_api_version_number,
1052 p_init_msg_list => l_init_msg_list,
1053 p_commit => l_commit,
1054 p_in_param_rec => l_rating_in_param_rec,
1055 x_out_param_rec => l_rating_out_param_rec,
1056 x_return_status => l_return_status,
1057 x_msg_count => l_msg_count,
1058 x_msg_data => l_msg_data);
1059
1060 IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_SUCCESS)
1061 THEN
1062 IF l_debug_on THEN
1063 WSH_DEBUG_SV.logmsg(l_module_name, 'Rating Deliveries Successful');
1064 END IF;
1065 ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING)
1066 THEN
1067 IF l_debug_on THEN
1068 WSH_DEBUG_SV.logmsg(l_module_name, 'Rating Deliveries completed with warnings');
1069 END IF;
1070 l_completion_status := 'WARNING';
1071 retcode := '1';
1072 ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR)
1073 OR (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)
1074 THEN
1075 WSH_UTIL_CORE.PrintMsg('Error occurred in WSH_FTE_INTEGRATION.Rate_Delivery');
1076 FOR m in 1..l_msg_count
1077 LOOP
1078 l_message := fnd_msg_pub.get(m,'F');
1079 l_message := replace(l_message,chr(0),' ');
1080 WSH_UTIL_CORE.PrintMsg(l_message);
1081 END LOOP;
1082
1083 fnd_msg_pub.delete_msg();
1084
1085 l_completion_status := 'WARNING';
1086 retcode := '1';
1087 END IF;
1088
1089 END IF;
1090
1091 IF l_completion_status = 'NORMAL'
1092 THEN
1093 retcode := '0';
1094 WSH_UTIL_CORE.PrintMsg('Delivery Consolidation completed successfully');
1095 IF l_debug_on THEN
1096 WSH_DEBUG_SV.logmsg(l_module_name, 'WSH_MDC_SRS.Schedule_Batch completed successfully');
1097 WSH_DEBUG_SV.logmsg(l_module_name, 'Current time is '||SYSDATE);
1098 END IF;
1099 ELSIF l_completion_status = 'WARNING'
1100 THEN
1101 retcode := '1';
1102 WSH_UTIL_CORE.PrintMsg('Delivery Consolidation completed with warnings');
1103 IF l_debug_on THEN
1104 WSH_DEBUG_SV.logmsg(l_module_name, 'WSH_MDC_SRS.Schedule_Batch completed with warnings');
1105 WSH_DEBUG_SV.logmsg(l_module_name, 'Current time is '||SYSDATE);
1106 END IF;
1107 END IF;
1108 COMMIT;
1109
1110 IF l_debug_on THEN
1111 WSH_DEBUG_SV.pop(l_module_name);
1112 END IF;
1113
1114 EXCEPTION
1115 WHEN FND_API.G_EXC_ERROR
1116 THEN
1117 ROLLBACK;
1118 WSH_UTIL_CORE.PrintMsg('SQLCODE: '||sqlcode||' SQLERRM: '||sqlerrm);
1119 WSH_UTIL_CORE.PrintMsg('Exception occurred in SCHEDULE_BATCH');
1120 l_temp := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR','');
1121 IF errbuf IS NULL
1122 THEN
1123 errbuf := 'Exception occurred in SCHEDULE_BATCH';
1124 END IF;
1125 retcode := '2';
1126
1127 IF l_debug_on THEN
1128 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
1129 END IF;
1130
1131 WHEN l_exc_complete
1132 THEN
1133 retcode := '1';
1134 WSH_UTIL_CORE.PrintMsg('Delivery Consolidation completed successfully');
1135 IF l_debug_on THEN
1136 WSH_DEBUG_SV.logmsg(l_module_name, 'WSH_MDC_SRS.Schedule_Batch completed successfully');
1137 WSH_DEBUG_SV.logmsg(l_module_name, 'Current time is '||SYSDATE);
1138 END IF;
1139
1140 IF l_debug_on THEN
1141 WSH_DEBUG_SV.pop(l_module_name);
1142 END IF;
1143
1144 WHEN OTHERS
1145 THEN
1146 ROLLBACK;
1147 WSH_UTIL_CORE.PrintMsg('SQLCODE: '||sqlcode||' SQLERRM: '||sqlerrm);
1148 WSH_UTIL_CORE.PrintMsg('Exception occurred in SCHEDULE_BATCH');
1149
1150 l_temp := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR','');
1151 errbuf := 'Exception occurred in SCHEDULE_BATCH';
1152 retcode := '2';
1153
1154
1155 IF l_debug_on THEN
1156 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1157 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1158 END IF;
1159
1160 END Schedule_Batch;
1161
1162
1163 --========================================================================
1164 -- PROCEDURE : Get_Batch_Parameters
1165 --
1166 -- PARAMETERS: p_batch_id Concurrent submission batch id
1167 -- x_sel_del_attr Delivery selection parameters
1168 -- x_return_status Return status
1169 --
1170 -- COMMENT : This procedure gets the delivery selection criteria specified
1171 -- for the batch.
1172 --========================================================================
1173 PROCEDURE Get_Batch_Parameters(
1174 p_batch_id IN NUMBER,
1175 x_sel_del_attr OUT NOCOPY select_del_flags_rec_type,
1176 x_return_status OUT NOCOPY VARCHAR2)
1177 IS
1178 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'GET_BATCH_PARAMETERS';
1179 l_debug_on BOOLEAN;
1180
1181 CURSOR Get_Batch(p_batch_id NUMBER) IS
1182 SELECT
1183 ORGANIZATION_ID,
1184 CONSOL_GROUPING_RULE_ID,
1185 CONSOL_SHIP_TO_LOCATION_ID,
1186 SHIP_TO_OVERIDE_FLAG,
1187 DELIVERY_NAME_FROM,
1188 DELIVERY_NAME_TO,
1189 PICKUP_START_DAYS,
1190 PICKUP_END_DAYS,
1191 DROPOFF_START_DAYS,
1192 DROPOFF_END_DAYS,
1193 PR_BATCH_ID,
1194 CUSTOMER_ID,
1195 FOB_CODE,
1196 FREIGHT_TERMS_CODE,
1197 CARRIER_ID,
1198 MODE_OF_TRANSPORT,
1199 SERVICE_LEVEL,
1200 LOADING_SEQUENCE,
1201 INTMED_SHIP_TO_LOCATION_ID,
1202 ULTI_SHIP_TO_LOCATION_ID,
1203 ULTI_SHIP_TO_REGION,
1204 ULTI_SHIP_TO_ZIP_FROM,
1205 ULTI_SHIP_TO_ZIP_TO,
1206 ULTI_SHIP_TO_ZONE,
1207 INCL_STAGED_DEL_FLAG,
1208 INCL_DEL_ASG_TRIPS_FLAG,
1209 CR_TRIP_TO_ULTM_SHIP_TO,
1210 ROUTE_TRIPS_FLAG,
1211 RATE_TRIPS_FLAG,
1212 TRIP_NAME_PREFIX,
1213 MAX_TRIP_WEIGHT,
1214 MAX_TRIP_WEIGHT_UOM
1215 FROM WSH_CONSOL_BATCHES
1216 WHERE BATCH_ID = p_batch_id;
1217
1218 BEGIN
1219
1220 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1221 IF l_debug_on IS NULL
1222 THEN
1223 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1224 END IF;
1225
1226 IF l_debug_on THEN
1227 WSH_DEBUG_SV.push(l_module_name);
1228 WSH_DEBUG_SV.log(l_module_name,'P_BATCH_ID',P_BATCH_ID);
1229 WSH_DEBUG_SV.log(l_module_name,'Current Time is ',SYSDATE);
1230 END IF;
1231
1232 OPEN Get_Batch(p_batch_id);
1233 FETCH Get_Batch INTO x_sel_del_attr;
1234 CLOSE Get_Batch;
1235
1236 IF l_debug_on THEN
1237 WSH_DEBUG_SV.logmsg(l_module_name,'x_sel_del_attr.org_id: '||x_sel_del_attr.org_id);
1238 WSH_DEBUG_SV.logmsg(l_module_name,'x_sel_del_attr.rule_id: '||x_sel_del_attr.rule_id);
1239 WSH_DEBUG_SV.logmsg(l_module_name,'x_sel_del_attr.consol_ship_to_loc_id: '||x_sel_del_attr.consol_ship_to_loc_id);
1240 WSH_DEBUG_SV.logmsg(l_module_name,'x_sel_del_attr.consol_shipto_override_flag: '||x_sel_del_attr.consol_shipto_override_flag);
1241 WSH_DEBUG_SV.logmsg(l_module_name,'x_sel_del_attr.delivery_name_from: '||x_sel_del_attr.delivery_name_from);
1242 WSH_DEBUG_SV.logmsg(l_module_name,'x_sel_del_attr.delivery_name_to: '||x_sel_del_attr.delivery_name_to);
1243 WSH_DEBUG_SV.logmsg(l_module_name,'x_sel_del_attr.pick_up_DATE_starts_within: '||x_sel_del_attr.pick_up_DATE_starts_within);
1244 WSH_DEBUG_SV.logmsg(l_module_name,'x_sel_del_attr.pick_up_DATE_ends_within: '||x_sel_del_attr.pick_up_DATE_ends_within);
1245 WSH_DEBUG_SV.logmsg(l_module_name,'x_sel_del_attr.drop_off_DATE_starts_within: '||x_sel_del_attr.drop_off_DATE_starts_within);
1246 WSH_DEBUG_SV.logmsg(l_module_name,'x_sel_del_attr.drop_off_DATE_ends_within: '||x_sel_del_attr.drop_off_DATE_ends_within);
1247 WSH_DEBUG_SV.logmsg(l_module_name,'x_sel_del_attr.pick_release_batch_id: '||x_sel_del_attr.pick_release_batch_id);
1248 WSH_DEBUG_SV.logmsg(l_module_name,'x_sel_del_attr.customer_id: '||x_sel_del_attr.customer_id);
1249 WSH_DEBUG_SV.logmsg(l_module_name,'x_sel_del_attr.fob_code: '||x_sel_del_attr.fob_code);
1250 WSH_DEBUG_SV.logmsg(l_module_name,'x_sel_del_attr.freight_terms_code: '||x_sel_del_attr.freight_terms_code);
1251 WSH_DEBUG_SV.logmsg(l_module_name,'x_sel_del_attr.carrier_id: '||x_sel_del_attr.carrier_id);
1252 WSH_DEBUG_SV.logmsg(l_module_name,'x_sel_del_attr.mode_of_transport: '||x_sel_del_attr.mode_of_transport);
1253 WSH_DEBUG_SV.logmsg(l_module_name,'x_sel_del_attr.service_level: '||x_sel_del_attr.service_level);
1254 WSH_DEBUG_SV.logmsg(l_module_name,'x_sel_del_attr.loading_sequence: '||x_sel_del_attr.loading_sequence);
1255 WSH_DEBUG_SV.logmsg(l_module_name,'x_sel_del_attr.intmed_ship_to_loc_id: '||x_sel_del_attr.intmed_ship_to_loc_id);
1256 WSH_DEBUG_SV.logmsg(l_module_name,'x_sel_del_attr.ulti_ship_to_loc_id: '||x_sel_del_attr.ulti_ship_to_loc_id);
1257 WSH_DEBUG_SV.logmsg(l_module_name,'x_sel_del_attr.ulti_ship_to_region_id: '||x_sel_del_attr.ulti_ship_to_region_id);
1258 WSH_DEBUG_SV.logmsg(l_module_name,'x_sel_del_attr.ulti_ship_to_zip_from: '||x_sel_del_attr.ulti_ship_to_zip_from);
1259 WSH_DEBUG_SV.logmsg(l_module_name,'x_sel_del_attr.ulti_ship_to_zip_to: '||x_sel_del_attr.ulti_ship_to_zip_to);
1260 WSH_DEBUG_SV.logmsg(l_module_name,'x_sel_del_attr.ulti_ship_to_zone_id: '||x_sel_del_attr.ulti_ship_to_zone_id);
1261 WSH_DEBUG_SV.logmsg(l_module_name,'x_sel_del_attr.inc_staged_del_flag: '||x_sel_del_attr.inc_staged_del_flag);
1262 WSH_DEBUG_SV.logmsg(l_module_name,'x_sel_del_attr.inc_del_assgnd_trip_flag: '||x_sel_del_attr.inc_del_assgnd_trip_flag);
1263 WSH_DEBUG_SV.logmsg(l_module_name,'x_sel_del_attr.create_deconsol_trips_flag: '||x_sel_del_attr.create_deconsol_trips_flag);
1264 WSH_DEBUG_SV.logmsg(l_module_name,'x_sel_del_attr.route_trips_flag: '||x_sel_del_attr.route_trips_flag);
1265 WSH_DEBUG_SV.logmsg(l_module_name,'x_sel_del_attr.rate_trips_flag: '||x_sel_del_attr.rate_trips_flag);
1266 WSH_DEBUG_SV.logmsg(l_module_name,'x_sel_del_attr.trip_name_prefix: '||x_sel_del_attr.trip_name_prefix);
1267 WSH_DEBUG_SV.logmsg(l_module_name,'x_sel_del_attr.max_trip_weight: '||x_sel_del_attr.max_trip_weight);
1268 WSH_DEBUG_SV.logmsg(l_module_name,'x_sel_del_attr.max_trip_weight_uom: '||x_sel_del_attr.max_trip_weight_uom);
1269 END IF;
1270
1271 IF l_debug_on THEN
1272 WSH_DEBUG_SV.pop(l_module_name);
1273 END IF;
1274 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1275 EXCEPTION
1276 WHEN OTHERS
1277 THEN
1278 IF l_debug_on THEN
1279 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1280 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1281 END IF;
1282 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1283 END Get_Batch_Parameters;
1284
1285 --========================================================================
1286 -- PROCEDURE : Get_Deliveries
1287 --
1288 -- PARAMETERS: p_sel_del_attr Delivery selection parameters
1289 -- x_delivery_tab Deliveries selected for consolidation
1290 -- x_delivery_addnl_attr_tab Deliveries selected for consolidation
1291 -- x_return_status Return status
1292 --
1293 -- COMMENT : This procedure fetches the deliveries to be consolidated.
1294 --========================================================================
1295 PROCEDURE Get_Deliveries(
1296 p_sel_del_attr IN select_del_flags_rec_type,
1297 x_delivery_tab OUT NOCOPY WSH_FTE_CONSTRAINT_FRAMEWORK.delivery_ccinfo_tab_type,
1298 x_delivery_addnl_attr_tab OUT NOCOPY addnl_del_attr_tab_type,
1299 x_return_status OUT NOCOPY VARCHAR2)
1300 IS
1301 l_delivery_rec WSH_FTE_CONSTRAINT_FRAMEWORK.delivery_ccinfo_rec_type;
1302 l_delivery_addnl_rec addnl_del_attr_rec_type;
1303 l_delivery_id NUMBER;
1304 l_select_clause varchar2(1000);
1305 l_from_clause varchar2(1000);
1306 l_where_clause varchar2(3000);
1307 l_query varchar2(5000);
1308 l_col_tab WSH_UTIL_CORE.tbl_varchar;
1309 l_col_count NUMBER;
1310 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'GET_DELIVERIES';
1311 l_debug_on BOOLEAN;
1312 l_wc_index NUMBER;
1313
1314
1315 c_deliveries WSH_UTIL_CORE.RefCurType;
1316 BEGIN
1317
1318 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1319 IF l_debug_on IS NULL
1320 THEN
1321 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1322 END IF;
1323
1324 IF l_debug_on THEN
1325 WSH_DEBUG_SV.push(l_module_name);
1326 WSH_DEBUG_SV.log(l_module_name,'Current Time is ',SYSDATE);
1327
1328 WSH_DEBUG_SV.log(l_module_name,'p_sel_del_attr.org_id :',p_sel_del_attr.org_id);
1329 WSH_DEBUG_SV.log(l_module_name,'p_sel_del_attr.rule_id :',p_sel_del_attr.rule_id);
1330 WSH_DEBUG_SV.log(l_module_name,'p_sel_del_attr.consol_ship_to_loc_id :',p_sel_del_attr.consol_ship_to_loc_id);
1331 WSH_DEBUG_SV.log(l_module_name,'p_sel_del_attr.consol_shipto_override_flag :',p_sel_del_attr.consol_shipto_override_flag);
1332 WSH_DEBUG_SV.log(l_module_name,'p_sel_del_attr.delivery_name_from :',p_sel_del_attr.delivery_name_from);
1333 WSH_DEBUG_SV.log(l_module_name,'p_sel_del_attr.delivery_name_to :',p_sel_del_attr.delivery_name_to);
1334 WSH_DEBUG_SV.log(l_module_name,'p_sel_del_attr.pick_up_DATE_starts_within :',p_sel_del_attr.pick_up_DATE_starts_within);
1335 WSH_DEBUG_SV.log(l_module_name,'p_sel_del_attr.pick_up_DATE_ends_within :',p_sel_del_attr.pick_up_DATE_ends_within);
1336 WSH_DEBUG_SV.log(l_module_name,'p_sel_del_attr.drop_off_DATE_starts_within :',p_sel_del_attr.drop_off_DATE_starts_within);
1337 WSH_DEBUG_SV.log(l_module_name,'p_sel_del_attr.drop_off_DATE_ends_within :',p_sel_del_attr.drop_off_DATE_ends_within);
1338 WSH_DEBUG_SV.log(l_module_name,'p_sel_del_attr.pick_release_batch_id :',p_sel_del_attr.pick_release_batch_id);
1339 WSH_DEBUG_SV.log(l_module_name,'p_sel_del_attr.customer_id :',p_sel_del_attr.customer_id);
1340 WSH_DEBUG_SV.log(l_module_name,'p_sel_del_attr.fob_code :',p_sel_del_attr.fob_code);
1341 WSH_DEBUG_SV.log(l_module_name,'p_sel_del_attr.freight_terms_code :',p_sel_del_attr.freight_terms_code);
1342 WSH_DEBUG_SV.log(l_module_name,'p_sel_del_attr.carrier_id :',p_sel_del_attr.carrier_id);
1343 WSH_DEBUG_SV.log(l_module_name,'p_sel_del_attr.mode_of_transport :',p_sel_del_attr.mode_of_transport);
1344 WSH_DEBUG_SV.log(l_module_name,'p_sel_del_attr.service_level :',p_sel_del_attr.service_level);
1345 WSH_DEBUG_SV.log(l_module_name,'p_sel_del_attr.loading_sequence :',p_sel_del_attr.loading_sequence);
1346 WSH_DEBUG_SV.log(l_module_name,'p_sel_del_attr.intmed_ship_to_loc_id :',p_sel_del_attr.intmed_ship_to_loc_id);
1347 WSH_DEBUG_SV.log(l_module_name,'p_sel_del_attr.ulti_ship_to_loc_id :',p_sel_del_attr.ulti_ship_to_loc_id);
1348 WSH_DEBUG_SV.log(l_module_name,'p_sel_del_attr.ulti_ship_to_region_id :',p_sel_del_attr.ulti_ship_to_region_id);
1349 WSH_DEBUG_SV.log(l_module_name,'p_sel_del_attr.ulti_ship_to_zip_from :',p_sel_del_attr.ulti_ship_to_zip_from);
1350 WSH_DEBUG_SV.log(l_module_name,'p_sel_del_attr.ulti_ship_to_zip_to :',p_sel_del_attr.ulti_ship_to_zip_to);
1351 WSH_DEBUG_SV.log(l_module_name,'p_sel_del_attr.ulti_ship_to_zone_id :',p_sel_del_attr.ulti_ship_to_zone_id);
1352 WSH_DEBUG_SV.log(l_module_name,'p_sel_del_attr.inc_staged_del_flag :',p_sel_del_attr.inc_staged_del_flag);
1353 WSH_DEBUG_SV.log(l_module_name,'p_sel_del_attr.inc_del_assgnd_trip_flag :',p_sel_del_attr.inc_del_assgnd_trip_flag);
1354 WSH_DEBUG_SV.log(l_module_name,'p_sel_del_attr.create_deconsol_trips_flag :',p_sel_del_attr.create_deconsol_trips_flag);
1355 WSH_DEBUG_SV.log(l_module_name,'p_sel_del_attr.route_trips_flag :',p_sel_del_attr.route_trips_flag);
1356 WSH_DEBUG_SV.log(l_module_name,'p_sel_del_attr.rate_trips_flag :',p_sel_del_attr.rate_trips_flag);
1357 WSH_DEBUG_SV.log(l_module_name,'p_sel_del_attr.trip_name_prefix :',p_sel_del_attr.trip_name_prefix);
1358 WSH_DEBUG_SV.log(l_module_name,'p_sel_del_attr.max_trip_weight :',p_sel_del_attr.max_trip_weight);
1359 WSH_DEBUG_SV.log(l_module_name,'p_sel_del_attr.max_trip_weight_uom :',p_sel_del_attr.max_trip_weight_uom);
1360 END IF;
1361
1362
1363 l_select_clause := 'SELECT WND.DELIVERY_ID, WND.NAME, WND.ORGANIZATION_ID, WND.STATUS_CODE, WND.PLANNED_FLAG, WND.INITIAL_PICKUP_DATE,';
1364 l_select_clause := l_select_clause || ' WND.INITIAL_PICKUP_LOCATION_ID, WND.ULTIMATE_DROPOFF_LOCATION_ID, WND.ULTIMATE_DROPOFF_DATE,';
1365 l_select_clause := l_select_clause || ' WND.CUSTOMER_ID, WND.INTMED_SHIP_TO_LOCATION_ID, WND.SHIP_METHOD_CODE, WND.DELIVERY_TYPE, WND.CARRIER_ID,';
1366 l_select_clause := l_select_clause || ' WND.SERVICE_LEVEL, WND.MODE_OF_TRANSPORT, WND.SHIPMENT_DIRECTION, WND.PARTY_ID, WND.SHIPPING_CONTROL,';
1367 l_select_clause := l_select_clause || ' WND.FOB_CODE, WND.FREIGHT_TERMS_CODE, WND.LOADING_SEQUENCE, NVL(WND.GROSS_WEIGHT,0) GROSS_WEIGHT, WND.WEIGHT_UOM_CODE, WND.IGNORE_FOR_PLANNING ';
1368 l_from_clause := ' FROM WSH_NEW_DELIVERIES WND';
1369 l_where_clause := ' WHERE WND.STATUS_CODE = :stat_code_open AND WND.PLANNED_FLAG <> :planned_flag_f AND NVL(WND.SHIPMENT_DIRECTION,:ship_dir_o) IN (:ship_dir_o, :ship_dir_io) AND WND.DELIVERY_TYPE = :del_type_stan ';
1370
1371 -- removing deliveries with null dates
1372 l_where_clause := l_where_clause || ' AND WND.INITIAL_PICKUP_DATE IS NOT NULL AND WND.ULTIMATE_DROPOFF_DATE IS NOT NULL ';
1373
1374 --removing empty deliveries
1375 l_where_clause := l_where_clause || ' AND EXISTS( SELECT NULL FROM WSH_DELIVERY_ASSIGNMENTS INNER WHERE INNER.DELIVERY_ID = WND.DELIVERY_ID) ';
1376
1377 l_col_count := 1;
1378
1379 l_col_tab(l_col_count) := 'OP';
1380 l_col_count := l_col_count+1;
1381
1382 l_col_tab(l_col_count) := 'F';
1383 l_col_count := l_col_count+1;
1384
1385 l_col_tab(l_col_count) := 'O';
1386 l_col_count := l_col_count+1;
1387
1388 l_col_tab(l_col_count) := 'O';
1389 l_col_count := l_col_count+1;
1390
1391 l_col_tab(l_col_count) := 'IO';
1392 l_col_count := l_col_count+1;
1393
1394 l_col_tab(l_col_count) := 'STANDARD';
1395 l_col_count := l_col_count+1;
1396
1397
1398 WSH_UTIL_CORE.PrintMsg('Delivery filtering criteria for the consolidation batch: ');
1399
1400 IF p_sel_del_attr.delivery_name_from IS NOT NULL AND p_sel_del_attr.delivery_name_to IS NOT NULL
1401 THEN
1402 WSH_UTIL_CORE.PrintMsg(' Delivery name from: '||p_sel_del_attr.delivery_name_from);
1403 WSH_UTIL_CORE.PrintMsg(' Delivery name to: '||p_sel_del_attr.delivery_name_to);
1404
1405 IF p_sel_del_attr.delivery_name_from = p_sel_del_attr.delivery_name_to
1406 THEN
1407 SELECT INSTR(p_sel_del_attr.delivery_name_from,'%') INTO l_wc_index FROM DUAL;
1408 IF l_wc_index = 0
1409 THEN
1410 l_where_clause := l_where_clause || ' AND WND.NAME = :p_del_name';
1411 l_col_tab(l_col_count) := p_sel_del_attr.delivery_name_from;
1412 l_col_count := l_col_count+1;
1413 ELSE
1414 l_where_clause := l_where_clause || ' AND WND.NAME LIKE :p_del_name';
1415 l_col_tab(l_col_count) := p_sel_del_attr.delivery_name_from;
1416 l_col_count := l_col_count+1;
1417 END IF;
1418 ELSE
1419 l_where_clause := l_where_clause || ' AND WND.NAME BETWEEN :p_del_from AND :p_del_to';
1420 l_col_tab(l_col_count) := p_sel_del_attr.delivery_name_from;
1421 l_col_count := l_col_count+1;
1422 l_col_tab(l_col_count) := p_sel_del_attr.delivery_name_to;
1423 l_col_count := l_col_count+1;
1424 END IF;
1425 END IF;
1426
1427 IF p_sel_del_attr.org_id IS NOT NULL
1428 THEN
1429 WSH_UTIL_CORE.PrintMsg(' Organization: '||p_sel_del_attr.org_id);
1430 l_where_clause := l_where_clause || ' AND WND.ORGANIZATION_ID = :org_id';
1431 l_col_tab(l_col_count) := p_sel_del_attr.org_id;
1432 l_col_count := l_col_count+1;
1433 END IF;
1434
1435 IF p_sel_del_attr.pick_up_date_starts_within IS NOT NULL
1436 THEN
1437 WSH_UTIL_CORE.PrintMsg(' Pick-up date starts within: '||p_sel_del_attr.pick_up_date_starts_within);
1438 l_where_clause := l_where_clause || ' AND WND.INITIAL_PICKUP_DATE >= SYSDATE + :p_pick_up_start_date';
1439 l_col_tab(l_col_count) := p_sel_del_attr.pick_up_date_starts_within;
1440 l_col_count := l_col_count+1;
1441 END IF;
1442
1443 IF p_sel_del_attr.pick_up_date_ends_within IS NOT NULL
1444 THEN
1445 WSH_UTIL_CORE.PrintMsg(' Pick-up date ends within: '||p_sel_del_attr.pick_up_date_ends_within);
1446 l_where_clause := l_where_clause || ' AND WND.INITIAL_PICKUP_DATE <= SYSDATE + :p_pick_up_end_date';
1447 l_col_tab(l_col_count) := p_sel_del_attr.pick_up_date_ends_within;
1448 l_col_count := l_col_count+1;
1449 END IF;
1450
1451 IF p_sel_del_attr.drop_off_date_starts_within IS NOT NULL
1452 THEN
1453 WSH_UTIL_CORE.PrintMsg(' Drop-off date starts within: '||p_sel_del_attr.drop_off_date_starts_within);
1454 l_where_clause := l_where_clause || ' AND WND.ULTIMATE_DROPOFF_DATE >= SYSDATE + :p_drop_off_start_date';
1455 l_col_tab(l_col_count) := p_sel_del_attr.drop_off_date_starts_within;
1456 l_col_count := l_col_count+1;
1457 END IF;
1458
1459 IF p_sel_del_attr.drop_off_date_ends_within IS NOT NULL
1460 THEN
1461 WSH_UTIL_CORE.PrintMsg(' Drop-off date ends within: '||p_sel_del_attr.drop_off_date_ends_within);
1462 l_where_clause := l_where_clause || ' AND WND.ULTIMATE_DROPOFF_DATE <= SYSDATE + :p_drop_off_end_date';
1463 l_col_tab(l_col_count) := p_sel_del_attr.drop_off_date_ends_within;
1464 l_col_count := l_col_count+1;
1465 END IF;
1466
1467 IF p_sel_del_attr.pick_release_batch_id IS NOT NULL
1468 THEN
1469 WSH_UTIL_CORE.PrintMsg(' Pick-Release batch: '||p_sel_del_attr.pick_release_batch_id);
1470 l_where_clause := l_where_clause || ' AND WND.BATCH_ID = :p_batch_id';
1471 l_col_tab(l_col_count) := p_sel_del_attr.pick_release_batch_id;
1472 l_col_count := l_col_count+1;
1473 END IF;
1474
1475 IF p_sel_del_attr.fob_code IS NOT NULL
1476 THEN
1477 WSH_UTIL_CORE.PrintMsg(' FOB Code: '||p_sel_del_attr.fob_code);
1478 l_where_clause := l_where_clause || ' AND WND.FOB_CODE = :p_fob_code';
1479 l_col_tab(l_col_count) := p_sel_del_attr.fob_code;
1480 l_col_count := l_col_count+1;
1481 END IF;
1482
1483 IF p_sel_del_attr.freight_terms_code IS NOT NULL
1484 THEN
1485 WSH_UTIL_CORE.PrintMsg(' Freight Terms Code: '||p_sel_del_attr.freight_terms_code);
1486 l_where_clause := l_where_clause || ' AND WND.FREIGHT_TERMS_CODE = :p_freight_terms_code';
1487 l_col_tab(l_col_count) := p_sel_del_attr.freight_terms_code;
1488 l_col_count := l_col_count+1;
1489 END IF;
1490
1491 IF p_sel_del_attr.carrier_id IS NOT NULL
1492 THEN
1493 WSH_UTIL_CORE.PrintMsg(' Carrier ID: '||p_sel_del_attr.carrier_id);
1494 l_where_clause := l_where_clause || ' AND WND.CARRIER_ID = :p_carrier_id';
1495 l_col_tab(l_col_count) := p_sel_del_attr.carrier_id;
1496 l_col_count := l_col_count+1;
1497 END IF;
1498
1499 IF p_sel_del_attr.mode_of_transport IS NOT NULL
1500 THEN
1501 WSH_UTIL_CORE.PrintMsg(' Mode of Transport: '||p_sel_del_attr.mode_of_transport);
1502 l_where_clause := l_where_clause || ' AND WND.MODE_OF_TRANSPORT = :p_mode_of_transport';
1503 l_col_tab(l_col_count) := p_sel_del_attr.mode_of_transport;
1504 l_col_count := l_col_count+1;
1505 END IF;
1506
1507 IF p_sel_del_attr.service_level IS NOT NULL
1508 THEN
1509 WSH_UTIL_CORE.PrintMsg(' Service level: '||p_sel_del_attr.service_level);
1510 l_where_clause := l_where_clause || ' AND WND.SERVICE_LEVEL = :p_service_level';
1511 l_col_tab(l_col_count) := p_sel_del_attr.service_level;
1512 l_col_count := l_col_count+1;
1513 END IF;
1514
1515 IF p_sel_del_attr.loading_sequence IS NOT NULL
1516 THEN
1517 WSH_UTIL_CORE.PrintMsg(' Loading sequence: '||p_sel_del_attr.loading_sequence);
1518 l_where_clause := l_where_clause || ' AND WND.LOADING_SEQUENCE = :p_loading_sequence';
1519 l_col_tab(l_col_count) := p_sel_del_attr.loading_sequence;
1520 l_col_count := l_col_count+1;
1521 END IF;
1522
1523 IF p_sel_del_attr.customer_id IS NOT NULL
1524 THEN
1525 WSH_UTIL_CORE.PrintMsg(' Customer ID: '||p_sel_del_attr.customer_id);
1526 l_where_clause := l_where_clause || ' AND WND.CUSTOMER_ID = :p_customer_id';
1527 l_col_tab(l_col_count) := p_sel_del_attr.customer_id;
1528 l_col_count := l_col_count+1;
1529 END IF;
1530
1531 IF p_sel_del_attr.intmed_ship_to_loc_id IS NOT NULL
1532 THEN
1533 WSH_UTIL_CORE.PrintMsg(' Intermediate Ship to Location: '||p_sel_del_attr.intmed_ship_to_loc_id);
1534 l_where_clause := l_where_clause || ' AND WND.INTMED_SHIP_TO_LOCATION_ID = :p_intmed_location_id';
1535 l_col_tab(l_col_count) := p_sel_del_attr.intmed_ship_to_loc_id;
1536 l_col_count := l_col_count+1;
1537 END IF;
1538
1539 IF p_sel_del_attr.ulti_ship_to_loc_id IS NOT NULL
1540 THEN
1541 WSH_UTIL_CORE.PrintMsg(' Ulitmate Ship to Location: '||p_sel_del_attr.ulti_ship_to_loc_id);
1542 l_where_clause := l_where_clause || ' AND WND.ULTIMATE_DROPOFF_LOCATION_ID = :p_dropoff_location_id';
1543 l_col_tab(l_col_count) := p_sel_del_attr.ulti_ship_to_loc_id;
1544 l_col_count := l_col_count+1;
1545 END IF;
1546
1547 IF p_sel_del_attr.ulti_ship_to_region_id IS NOT NULL
1548 THEN
1549 WSH_UTIL_CORE.PrintMsg(' Ultimate Ship to Region: '||p_sel_del_attr.ulti_ship_to_region_id);
1550 l_where_clause := l_where_clause || ' AND WND.ULTIMATE_DROPOFF_LOCATION_ID IN (SELECT LOCATION_ID FROM WSH_REGION_LOCATIONS WHERE REGION_ID = :p_region_id)';
1551 l_col_tab(l_col_count) := p_sel_del_attr.ulti_ship_to_region_id;
1552 l_col_count := l_col_count+1;
1553 END IF;
1554
1555 IF p_sel_del_attr.ulti_ship_to_zip_from IS NOT NULL OR p_sel_del_attr.ulti_ship_to_zip_to IS NOT NULL
1556 THEN
1557 l_from_clause := l_from_clause || ', WSH_LOCATIONS WL';
1558 l_where_clause := l_where_clause || ' AND WND.ULTIMATE_DROPOFF_LOCATION_ID = WL.WSH_LOCATION_ID';
1559 END IF;
1560
1561 IF p_sel_del_attr.ulti_ship_to_zip_from IS NOT NULL
1562 THEN
1563 WSH_UTIL_CORE.PrintMsg(' Ultimate ship to zip(from): '||p_sel_del_attr.ulti_ship_to_zip_from);
1564 l_where_clause := l_where_clause || ' AND WL.POSTAL_CODE >= :p_zip_code_from';
1565 l_col_tab(l_col_count) := p_sel_del_attr.ulti_ship_to_zip_from;
1566 l_col_count := l_col_count+1;
1567 END IF;
1568
1569 IF p_sel_del_attr.ulti_ship_to_zip_to IS NOT NULL
1570 THEN
1571 WSH_UTIL_CORE.PrintMsg(' Ultimate ship to zip(to): '||p_sel_del_attr.ulti_ship_to_zip_to);
1572 l_where_clause := l_where_clause || ' AND WL.POSTAL_CODE <= :p_zip_code_to';
1573 l_col_tab(l_col_count) := p_sel_del_attr.ulti_ship_to_zip_to;
1574 l_col_count := l_col_count+1;
1575 END IF;
1576
1577 IF p_sel_del_attr.ulti_ship_to_zone_id IS NOT NULL
1578 THEN
1579 WSH_UTIL_CORE.PrintMsg(' Ultimate ship to zone: '||p_sel_del_attr.ulti_ship_to_zone_id);
1580 l_where_clause := l_where_clause || ' AND WND.ULTIMATE_DROPOFF_LOCATION_ID IN (SELECT LOCATION_ID FROM WSH_REGION_LOCATIONS WHERE REGION_ID IN (SELECT REGION_ID FROM WSH_ZONE_REGIONS WHERE PARENT_REGION_ID = :p_zone_id))';
1581 l_col_tab(l_col_count) := p_sel_del_attr.ulti_ship_to_zone_id;
1582 l_col_count := l_col_count+1;
1583 END IF;
1584
1585 IF p_sel_del_attr.inc_del_assgnd_trip_flag IS NULL
1586 THEN
1587 l_where_clause := l_where_clause || ' AND NOT EXISTS ( SELECT 1 FROM WSH_DELIVERY_LEGS WHERE DELIVERY_ID = WND.DELIVERY_ID AND ROWNUM < 2)';
1588 ELSE
1589 WSH_UTIL_CORE.PrintMsg(' Including deliveries assigned to trip');
1590 END IF;
1591
1592 IF p_sel_del_attr.inc_staged_del_flag IS NULL
1593 THEN
1594 l_where_clause := l_where_clause || ' AND NOT EXISTS ( SELECT 1 FROM WSH_DELIVERY_ASSIGNMENTS WDA, WSH_DELIVERY_DETAILS WDD WHERE WDA.DELIVERY_ID = WND.DELIVERY_ID';
1595 l_where_clause := l_where_clause || ' AND WDA.DELIVERY_DETAIL_ID = WDD.DELIVERY_DETAIL_ID AND WDD.RELEASED_STATUS = :rel_stat_y AND ROWNUM < 2)';
1596 l_col_tab(l_col_count) := 'Y';
1597 l_col_count := l_col_count+1;
1598
1599 ELSE
1600 WSH_UTIL_CORE.PrintMsg(' Including staged deliveries');
1601 END IF;
1602
1603 -- is the delivery already consolidated?
1604 l_where_clause := l_where_clause || ' AND NOT EXISTS (SELECT 1 FROM WSH_DELIVERY_ASSIGNMENTS WDA, WSH_NEW_DELIVERIES CONSOL';
1605 l_where_clause := l_where_clause || ' WHERE WDA.DELIVERY_ID = WND.DELIVERY_ID AND WDA.PARENT_DELIVERY_ID = CONSOL.DELIVERY_ID AND CONSOL.INITIAL_PICKUP_LOCATION_ID = WND.INITIAL_PICKUP_LOCATION_ID AND ROWNUM < 2)';
1606
1607 l_query := l_select_clause||l_from_clause||l_where_clause;
1608
1609 IF l_debug_on THEN
1610 WSH_DEBUG_SV.logmsg(l_module_name,'Query built : '||l_query);
1611 END IF;
1612
1613
1614 IF l_debug_on THEN
1615 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.OpenDynamicCursor',WSH_DEBUG_SV.C_PROC_LEVEL);
1616 END IF;
1617
1618 WSH_UTIL_CORE.OpenDynamicCursor(c_deliveries, l_query, l_col_tab);
1619
1620 IF l_debug_on THEN
1621 WSH_DEBUG_SV.logmsg(l_module_name,'Dynamic cursor open');
1622 END IF;
1623
1624 LOOP
1625 --{
1626 FETCH c_deliveries INTO
1627 l_delivery_rec.delivery_id,
1628 l_delivery_rec.name,
1629 l_delivery_rec.organization_id,
1630 l_delivery_rec.status_code,
1631 l_delivery_rec.planned_flag,
1632 l_delivery_rec.initial_pickup_date,
1633 l_delivery_rec.initial_pickup_location_id,
1634 l_delivery_rec.ultimate_dropoff_location_id,
1635 l_delivery_rec.ultimate_dropoff_date,
1636 l_delivery_rec.customer_id,
1637 l_delivery_rec.intmed_ship_to_location_id,
1638 l_delivery_rec.ship_method_code,
1639 l_delivery_rec.delivery_type,
1640 l_delivery_rec.carrier_id,
1641 l_delivery_rec.service_level,
1642 l_delivery_rec.mode_of_transport,
1643 l_delivery_rec.shipment_direction,
1644 l_delivery_rec.party_id,
1645 l_delivery_rec.shipping_control,
1646 l_delivery_addnl_rec.fob_code,
1647 l_delivery_addnl_rec.freight_terms_code,
1648 l_delivery_addnl_rec.loading_sequence,
1649 l_delivery_addnl_rec.gross_weight,
1650 l_delivery_addnl_rec.weight_uom_code,
1651 l_delivery_addnl_rec.ignore_for_planning;
1652
1653 l_delivery_rec.exists_in_database := 'Y';
1654
1655
1656 IF c_deliveries%NOTFOUND
1657 THEN
1658 EXIT;
1659 END IF;
1660
1661 l_delivery_id := l_delivery_rec.delivery_id;
1662 l_delivery_addnl_rec.delivery_id := l_delivery_id;
1663 x_delivery_tab(l_delivery_id) := l_delivery_rec;
1664 x_delivery_addnl_attr_tab(l_delivery_id) := l_delivery_addnl_rec;
1665
1666 --}
1667 END LOOP;
1668
1669 CLOSE c_deliveries;
1670
1671
1672 IF l_debug_on THEN
1673 WSH_DEBUG_SV.pop(l_module_name);
1674 END IF;
1675 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1676
1677 EXCEPTION
1678 WHEN OTHERS
1679 THEN
1680 IF l_debug_on THEN
1681 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1682 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1683 END IF;
1684 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1685 END Get_Deliveries;
1686
1687 --========================================================================
1688 -- PROCEDURE : Set_Intermediate_Location
1689 --
1690 -- PARAMETERS: p_consol_loc_id Default intermediate location id
1691 -- p_override_ship_to_flag Intermediate location override flag
1692 -- p_rule_zone_id Zone specified in the grouping rule
1693 -- x_delivery_tab Delivery records
1694 -- x_delivery_addnl_attr_tab Delivery records
1695 -- x_failed_records Deliveries which failed to get intermedidate location
1696 -- x_return_status Return status
1697 --
1698 -- COMMENT : This procedure updates the delivery records with the intermediate
1699 -- location. The intermediate location is fecthed by calling
1700 -- constraints engine which futher checks the Regions form
1701 -- for an intermediate location, if required. The default intermediate
1702 -- location is applied if the constraints engine fails to fecth
1703 -- an intermediate location. If the override flag is set,
1704 -- all the deliveries are updated with the default value.
1705 --========================================================================
1706 PROCEDURE Set_Intermediate_Location(
1707 p_consol_loc_id IN NUMBER,
1708 p_override_ship_to_flag IN VARCHAR2,
1709 p_rule_zone_id IN NUMBER,
1710 x_delivery_tab IN OUT NOCOPY WSH_FTE_CONSTRAINT_FRAMEWORK.delivery_ccinfo_tab_type,
1711 x_delivery_addnl_attr_tab IN OUT NOCOPY addnl_del_attr_tab_type,
1712 x_failed_records IN OUT NOCOPY WSH_UTIL_CORE.id_tab_type,
1713 x_return_status OUT NOCOPY VARCHAR2)
1714 IS
1715 l_deconsol_output_tab WSH_FTE_CONSTRAINT_FRAMEWORK.deconsol_output_tab_type;
1716 l_delivery_id NUMBER;
1717 l_failed_index NUMBER;
1718 l_dummy_list WSH_UTIL_CORE.id_tab_type;
1719 l_override_flag BOOLEAN;
1720 l_init_msg_list VARCHAR2(1000);
1721 l_msg_count NUMBER;
1722 l_msg_data VARCHAR2(2000);
1723 l_return_status VARCHAR2(1);
1724 i NUMBER;
1725 m NUMBER;
1726 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'SET_INTERMEDIATE_LOCATION';
1727 l_debug_on BOOLEAN;
1728 l_failed_flag BOOLEAN;
1729 l_message VARCHAR2(2000);
1730 l_int_loc BOOLEAN;
1731 l_dummy_loc NUMBER;
1732 BEGIN
1733
1734 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1735 IF l_debug_on IS NULL
1736 THEN
1737 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1738 END IF;
1739
1740 IF l_debug_on THEN
1741 WSH_DEBUG_SV.push(l_module_name);
1742 WSH_DEBUG_SV.log(l_module_name,'p_consol_loc_id',p_consol_loc_id);
1743 WSH_DEBUG_SV.log(l_module_name,'p_override_ship_to_flag',p_override_ship_to_flag);
1744 WSH_DEBUG_SV.log(l_module_name,'p_rule_zone_id',p_rule_zone_id);
1745 WSH_DEBUG_SV.log(l_module_name,'Current Time is ',SYSDATE);
1746 END IF;
1747
1748 WSH_UTIL_CORE.PrintMsg('Setting intermediate location for the deliveries');
1749 WSH_UTIL_CORE.PrintMsg(' Default intermediate location is '||p_consol_loc_id);
1750 IF p_override_ship_to_flag = 'Y'
1751 THEN
1752 l_override_flag := TRUE;
1753 WSH_UTIL_CORE.PrintMsg(' Note: Override flag is turned on');
1754 ELSE
1755 l_override_flag := FALSE;
1756 WSH_UTIL_CORE.PrintMsg(' Note: Override flag is turned off');
1757 END IF;
1758
1759 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1760
1761 -- call constraints API to set the intermediate location
1762 IF l_debug_on THEN
1763 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_FTE_COMP_CONSTRAINT_PKG.validate_constraint_deconsol',WSH_DEBUG_SV.C_PROC_LEVEL);
1764 END IF;
1765
1766 WSH_FTE_CONSTRAINT_FRAMEWORK.validate_constraint_deconsol(
1767 p_init_msg_list => l_init_msg_list,
1768 p_delivery_info => x_delivery_tab,
1769 p_in_ids => l_dummy_list,
1770 p_rule_to_zone_id => p_rule_zone_id,
1771 p_rule_deconsol_location => p_consol_loc_id,
1772 p_rule_override_deconsol => l_override_flag,
1773 x_output_id_tab => l_deconsol_output_tab,
1774 x_msg_count => l_msg_count,
1775 x_msg_data => l_msg_data,
1776 x_return_status => l_return_status);
1777
1778 IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_SUCCESS)
1779 THEN
1780 IF l_debug_on THEN
1781 WSH_DEBUG_SV.logmsg(l_module_name, 'Intermediate location set successfully for the deliveries');
1782 END IF;
1783 ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING)
1784 THEN
1785 IF l_debug_on THEN
1786 WSH_DEBUG_SV.logmsg(l_module_name, 'Intermediate location set with warnings');
1787 END IF;
1788 ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR)
1789 OR (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)
1790 THEN
1791 WSH_UTIL_CORE.PrintMsg('Error occurred in WSH_FTE_COMP_CONSTRAINT_PKG.validate_constraint_deconsol');
1792 FOR m in 1..l_msg_count
1793 LOOP
1794 l_message := fnd_msg_pub.get(m,'F');
1795 l_message := replace(l_message,chr(0),' ');
1796 IF l_debug_on THEN
1797 WSH_DEBUG_SV.logmsg(l_module_name, l_message);
1798 END IF;
1799 WSH_UTIL_CORE.PrintMsg(l_message);
1800 END LOOP;
1801
1802 fnd_msg_pub.delete_msg();
1803
1804 raise FND_API.G_EXC_ERROR;
1805 END IF;
1806
1807
1808 l_failed_index := x_failed_records.LAST;
1809 IF l_failed_index IS NULL
1810 THEN
1811 l_failed_index := 0;
1812 END IF;
1813
1814 i := l_deconsol_output_tab.FIRST;
1815 --FOR i in 1 .. l_deconsol_output_tab.COUNT
1816 WHILE i IS NOT NULL
1817 LOOP
1818 l_delivery_id := l_deconsol_output_tab(i).entity_id;
1819 x_delivery_tab(l_delivery_id).intmed_ship_to_location_id := l_deconsol_output_tab(i).deconsol_location;
1820
1821 --check if the location is an internal location
1822 IF l_debug_on THEN
1823 WSH_DEBUG_SV.logmsg(l_module_name, 'Internal Location check for '||l_deconsol_output_tab(i).deconsol_location);
1824 END IF;
1825
1826 l_int_loc := FALSE;
1827
1828 IF l_debug_on THEN
1829 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_LOCATIONS_PKG.convert_internal_cust_location',WSH_DEBUG_SV.C_PROC_LEVEL);
1830 END IF;
1831
1832 WSH_LOCATIONS_PKG.convert_internal_cust_location(
1833 p_internal_cust_location_id => l_deconsol_output_tab(i).deconsol_location,
1834 x_internal_org_location_id => l_dummy_loc,
1835 x_return_status => l_return_status);
1836
1837 IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_SUCCESS)
1838 THEN
1839 IF l_debug_on THEN
1840 WSH_DEBUG_SV.logmsg(l_module_name, 'Internal location checked successfully');
1841 END IF;
1842 ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING)
1843 THEN
1844 IF l_debug_on THEN
1845 WSH_DEBUG_SV.logmsg(l_module_name, 'Internal location checked with warnings');
1846 END IF;
1847 ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR)
1848 OR (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)
1849 THEN
1850 IF l_debug_on THEN
1851 WSH_DEBUG_SV.logmsg(l_module_name, 'Internal location check errored out');
1852 END IF;
1853 --removing the delivery record from consolidation
1854 l_int_loc := TRUE;
1855 END IF;
1856
1857 IF l_dummy_loc IS NOT NULL
1858 THEN
1859 l_int_loc := TRUE;
1860 IF l_debug_on THEN
1861 WSH_DEBUG_SV.logmsg(l_module_name, 'Location '||l_deconsol_output_tab(i).deconsol_location||' is an internal location');
1862 END IF;
1863 END IF;
1864
1865 -- Remove validation failed records and records with internal locations
1866 IF ((l_deconsol_output_tab(i).validation_status = 'F')
1867 OR (l_int_loc = TRUE))
1868 THEN
1869 l_failed_flag := TRUE;
1870 l_failed_index := l_failed_index +1;
1871 x_failed_records(l_failed_index) := l_delivery_id;
1872 x_delivery_tab.delete(l_delivery_id);
1873 x_delivery_addnl_attr_tab.delete(l_delivery_id);
1874 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
1875 WSH_UTIL_CORE.PrintMsg('Failed to get Intermediate Location. Removing delivery '||l_delivery_id||' from consolidation');
1876 ELSE
1877 WSH_UTIL_CORE.PrintMsg(' Delivery: '||l_deconsol_output_tab(i).entity_id||', Intermediate location: '||l_deconsol_output_tab(i).deconsol_location);
1878 END IF;
1879 i := l_deconsol_output_tab.NEXT(i);
1880 END LOOP;
1881
1882 IF l_debug_on THEN
1883 WSH_DEBUG_SV.pop(l_module_name);
1884 END IF;
1885 EXCEPTION
1886 WHEN FND_API.G_EXC_ERROR
1887 THEN
1888 IF l_debug_on THEN
1889 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
1890 END IF;
1891 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1892
1893 WHEN OTHERS
1894 THEN
1895 IF l_debug_on THEN
1896 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1897 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1898 END IF;
1899 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1900
1901 END Set_Intermediate_Location;
1902
1903
1904 --========================================================================
1905 -- PROCEDURE : Get_Grouping_Attrs
1906 --
1907 -- PARAMETERS: p_grouping_rule_id Consolidation grouping rule id
1908 -- x_group_by_flags Grouping attributes
1909 -- x_return_status Return status
1910 --
1911 -- COMMENT : This procedure gets the grouping attributes for the
1912 -- rule specified.
1913 --========================================================================
1914 PROCEDURE Get_Grouping_Attrs(
1915 p_grouping_rule_id IN NUMBER,
1916 x_group_by_flags OUT NOCOPY group_by_flags_rec_type,
1917 x_return_status OUT NOCOPY VARCHAR2)
1918 IS
1919 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'GET_GROUPING_ATTRS';
1920 l_debug_on BOOLEAN;
1921 l_rule_name VARCHAR2(30);
1922 CURSOR Get_Attrs( p_rule_id NUMBER) IS
1923 SELECT
1924 SHIP_FROM_FLAG,
1925 CUSTOMER_FLAG,
1926 INTMED_SHIP_TO_FLAG,
1927 CARRIER_FLAG,
1928 MODE_OF_TRANSPORT_FLAG,
1929 SERVICE_LEVEL_FLAG,
1930 FOB_FLAG,
1931 FREIGHT_TERMS_FLAG,
1932 LOADING_SEQUENCE_FLAG,
1933 SHIP_TO_CODE,
1934 SHIP_TO_COUNTRY_FLAG,
1935 SHIP_TO_STATE_FLAG,
1936 SHIP_TO_CITY_FLAG,
1937 SHIP_TO_POSTAL_CODE_FLAG,
1938 SHIP_TO_ZONE
1939 FROM WSH_CONSOL_GROUPING_RULES
1940 WHERE CONSOL_GROUPING_RULE_ID = p_rule_id;
1941
1942 CURSOR Get_Rule_Name( p_rule_id NUMBER) IS
1943 SELECT RULE_NAME
1944 FROM WSH_CONSOL_GROUPING_RULES
1945 WHERE CONSOL_GROUPING_RULE_ID = p_rule_id;
1946 BEGIN
1947
1948 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1949 IF l_debug_on IS NULL
1950 THEN
1951 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1952 END IF;
1953
1954 IF l_debug_on THEN
1955 WSH_DEBUG_SV.push(l_module_name);
1956 WSH_DEBUG_SV.log(l_module_name,'p_grouping_rule_id',p_grouping_rule_id);
1957 WSH_DEBUG_SV.log(l_module_name,'Current Time is ',SYSDATE);
1958 END IF;
1959
1960 OPEN Get_Attrs(p_grouping_rule_id);
1961 FETCH Get_Attrs INTO x_group_by_flags;
1962 CLOSE Get_Attrs;
1963
1964 OPEN Get_Rule_Name(p_grouping_rule_id);
1965 FETCH Get_Rule_Name INTO l_rule_name;
1966 CLOSE Get_Rule_Name;
1967
1968 WSH_UTIL_CORE.PrintMsg('Rule used for consolidation: '''||l_rule_name||'''');
1969
1970 IF l_debug_on THEN
1971 WSH_DEBUG_SV.pop(l_module_name);
1972 END IF;
1973 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1974 EXCEPTION
1975 WHEN OTHERS
1976 THEN
1977 IF l_debug_on THEN
1978 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1979 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1980 END IF;
1981 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1982 END Get_Grouping_Attrs;
1983
1984 --========================================================================
1985 -- PROCEDURE : Get_Hash_Value
1986 --
1987 -- PARAMETERS: p_delivery_rec Delivery record
1988 -- p_delivery_addnl_attr_rec Delivery record
1989 -- p_group_by_flags Grouping attributes
1990 -- p_hash_base Hash base
1991 -- p_hash_size Hash size
1992 -- x_hash_string Hash string
1993 -- x_hash_value Hash value
1994 -- x_return_status Return status
1995 --
1996 -- COMMENT : This procedure takes the delivery records and the
1997 -- grouping attributes and return the hash value. The hash
1998 -- size and base also needs to be passed.
1999 -- The procedure also clears out all the non-grouping
2000 -- attributes from the delivery records
2001 --========================================================================
2002 PROCEDURE Get_Hash_Value(
2003 x_delivery_rec IN OUT NOCOPY WSH_FTE_CONSTRAINT_FRAMEWORK.delivery_ccinfo_rec_type,
2004 x_delivery_addnl_attr_rec IN OUT NOCOPY addnl_del_attr_rec_type,
2005 p_group_by_flags IN group_by_flags_rec_type,
2006 p_hash_base IN NUMBER,
2007 p_hash_size IN NUMBER,
2008 x_hash_string OUT NOCOPY VARCHAR2,
2009 x_hash_value OUT NOCOPY NUMBER,
2010 x_return_status OUT NOCOPY VARCHAR2)
2011 IS
2012 l_country VARCHAR2(120);
2013 l_state VARCHAR2(120);
2014 l_city VARCHAR2(120);
2015 l_postal_code VARCHAR2(60);
2016 l_zone VARCHAR2(1);
2017 l_temp NUMBER;
2018 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'GET_HASH_VALUE';
2019 l_debug_on BOOLEAN;
2020
2021 CURSOR Get_Loc_Details( c_location_id NUMBER) IS
2022 SELECT COUNTRY, STATE, CITY, POSTAL_CODE
2023 FROM WSH_LOCATIONS
2024 WHERE WSH_LOCATION_ID = c_location_id;
2025
2026 CURSOR Check_Zone_Location
2027 ( c_zone_region_id NUMBER,
2028 c_location_id NUMBER)
2029 IS
2030 SELECT REGION_ID
2031 FROM WSH_REGION_LOCATIONS
2032 WHERE LOCATION_ID = c_location_id
2033 AND REGION_ID IN (SELECT REGION_ID
2034 FROM WSH_ZONE_REGIONS
2035 WHERE PARENT_REGION_ID = c_zone_region_id);
2036
2037 BEGIN
2038
2039 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2040 IF l_debug_on IS NULL
2041 THEN
2042 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2043 END IF;
2044
2045 IF l_debug_on THEN
2046 WSH_DEBUG_SV.push(l_module_name);
2047 WSH_DEBUG_SV.log(l_module_name,'Current Time is ',SYSDATE);
2048 WSH_DEBUG_SV.log(l_module_name,'x_delivery_rec.delivery_id :',x_delivery_rec.delivery_id);
2049 WSH_DEBUG_SV.log(l_module_name,'p_group_by_flags.ship_from :',p_group_by_flags.ship_from);
2050 WSH_DEBUG_SV.log(l_module_name,'p_group_by_flags.customer :',p_group_by_flags.customer);
2051 WSH_DEBUG_SV.log(l_module_name,'p_group_by_flags.intmed_ship_to :',p_group_by_flags.intmed_ship_to);
2052 WSH_DEBUG_SV.log(l_module_name,'p_group_by_flags.carrier :',p_group_by_flags.carrier);
2053 WSH_DEBUG_SV.log(l_module_name,'p_group_by_flags.mode_of_transport :',p_group_by_flags.mode_of_transport);
2054 WSH_DEBUG_SV.log(l_module_name,'p_group_by_flags.service_level :',p_group_by_flags.service_level);
2055 WSH_DEBUG_SV.log(l_module_name,'p_group_by_flags.fob :',p_group_by_flags.fob);
2056 WSH_DEBUG_SV.log(l_module_name,'p_group_by_flags.freight_terms :',p_group_by_flags.freight_terms);
2057 WSH_DEBUG_SV.log(l_module_name,'p_group_by_flags.loading_sequence :',p_group_by_flags.loading_sequence);
2058 WSH_DEBUG_SV.log(l_module_name,'p_group_by_flags.ship_to_lcode :',p_group_by_flags.ship_to_code);
2059 WSH_DEBUG_SV.log(l_module_name,'p_group_by_flags.ship_to_country :',p_group_by_flags.ship_to_country);
2060 WSH_DEBUG_SV.log(l_module_name,'p_group_by_flags.ship_to_state :',p_group_by_flags.ship_to_state);
2061 WSH_DEBUG_SV.log(l_module_name,'p_group_by_flags.ship_to_city :',p_group_by_flags.ship_to_city);
2062 WSH_DEBUG_SV.log(l_module_name,'p_group_by_flags.ship_to_postal_code :',p_group_by_flags.ship_to_postal_code);
2063 WSH_DEBUG_SV.log(l_module_name,'p_group_by_flags.ship_to_zone :',p_group_by_flags.ship_to_zone);
2064 END IF;
2065
2066 x_hash_string := x_delivery_rec.initial_pickup_location_id || '-' || x_delivery_rec.intmed_ship_to_location_id
2067 || '-' || x_delivery_addnl_attr_rec.group_id;
2068
2069 IF(p_group_by_flags.customer = 'Y')
2070 THEN
2071 x_hash_string := x_hash_string || '-' || x_delivery_rec.customer_id;
2072 ELSE
2073 x_delivery_rec.customer_id := null;
2074 END IF;
2075
2076 IF(p_group_by_flags.carrier = 'Y')
2077 THEN
2078 x_hash_string := x_hash_string || '-' || x_delivery_rec.carrier_id;
2079 ELSE
2080 x_delivery_rec.carrier_id := null;
2081 END IF;
2082
2083 IF(p_group_by_flags.mode_of_transport = 'Y')
2084 THEN
2085 x_hash_string := x_hash_string || '-' || x_delivery_rec.mode_of_transport;
2086 ELSE
2087 x_delivery_rec.mode_of_transport := null;
2088 END IF;
2089
2090 IF(p_group_by_flags.service_level = 'Y')
2091 THEN
2092 x_hash_string := x_hash_string || '-' || x_delivery_rec.service_level;
2093 ELSE
2094 x_delivery_rec.service_level := null;
2095 END IF;
2096
2097 IF(p_group_by_flags.fob = 'Y')
2098 THEN
2099 x_hash_string := x_hash_string || '-' || x_delivery_addnl_attr_rec.fob_code;
2100 ELSE
2101 x_delivery_addnl_attr_rec.fob_code := null;
2102 END IF;
2103
2104 IF(p_group_by_flags.freight_terms = 'Y')
2105 THEN
2106 x_hash_string := x_hash_string || '-' || x_delivery_addnl_attr_rec.freight_terms_code;
2107 ELSE
2108 x_delivery_addnl_attr_rec.freight_terms_code := null;
2109 END IF;
2110
2111 IF(p_group_by_flags.loading_sequence = 'Y')
2112 THEN
2113 x_hash_string := x_hash_string || '-' || x_delivery_addnl_attr_rec.loading_sequence;
2114 ELSE
2115 x_delivery_addnl_attr_rec.loading_sequence := null;
2116 END IF;
2117
2118 IF(p_group_by_flags.ship_to_code = 'L')
2119 THEN
2120 x_hash_string := x_hash_string || '-' || x_delivery_rec.ultimate_dropoff_location_id;
2121 END IF;
2122
2123 IF(p_group_by_flags.ship_to_code = 'R')
2124 THEN
2125
2126 OPEN Get_Loc_Details(x_delivery_rec.ultimate_dropoff_location_id);
2127 FETCH Get_Loc_Details INTO l_country, l_state, l_city, l_postal_code;
2128 IF(p_group_by_flags.ship_to_country = 'Y')
2129 THEN
2130 x_hash_string := x_hash_string || '-' || l_country;
2131 END IF;
2132
2133 IF(p_group_by_flags.ship_to_state = 'Y')
2134 THEN
2135 x_hash_string := x_hash_string || '-' || l_state;
2136 END IF;
2137
2138 IF(p_group_by_flags.ship_to_city = 'Y')
2139 THEN
2140 x_hash_string := x_hash_string || '-' || l_city;
2141 END IF;
2142
2143 IF(p_group_by_flags.ship_to_postal_code = 'Y')
2144 THEN
2145 x_hash_string := x_hash_string || '-' || l_postal_code;
2146 END IF;
2147
2148 CLOSE Get_Loc_Details;
2149 END IF;
2150
2151 -- handle zone
2152 -- currently grouping is done as follows
2153 -- 1. delivery belongs to the specified zone
2154 -- 2. delivery does not belong to the specified zone
2155 IF(p_group_by_flags.ship_to_code = 'Z')
2156 THEN
2157 IF(p_group_by_flags.ship_to_zone IS NOT NULL)
2158 THEN
2159 l_zone := 'N';
2160 OPEN Check_Zone_Location
2161 (c_zone_region_id => p_group_by_flags.ship_to_zone,
2162 c_location_id => x_delivery_rec.ultimate_dropoff_location_id);
2163 LOOP
2164 FETCH Check_Zone_Location INTO l_temp;
2165 EXIT WHEN Check_Zone_Location%NOTFOUND;
2166 l_zone := 'Y';
2167 EXIT;
2168 END LOOP;
2169 CLOSE Check_Zone_Location;
2170 x_hash_string := x_hash_string || '-' || l_zone;
2171 END IF;
2172 END IF;
2173
2174 IF l_debug_on THEN
2175 WSH_DEBUG_SV.logmsg(l_module_name,'hash string :'||x_hash_string);
2176 END IF;
2177
2178 x_hash_value := dbms_utility.get_hash_value(
2179 name => x_hash_string,
2180 base => p_hash_base,
2181 hash_size => p_hash_size );
2182
2183 IF l_debug_on THEN
2184 WSH_DEBUG_SV.logmsg(l_module_name,'hash value :'||x_hash_value);
2185 END IF;
2186
2187 IF l_debug_on THEN
2188 WSH_DEBUG_SV.pop(l_module_name);
2189 END IF;
2190 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2191
2192 EXCEPTION
2193 WHEN OTHERS
2194 THEN
2195 IF l_debug_on THEN
2196 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2197 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
2198 END IF;
2199 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2200
2201 END Get_Hash_Value;
2202
2203
2204 --========================================================================
2205 -- PROCEDURE : Create_Consolidations
2206 --
2207 -- PARAMETERS: x_delivery_tab Delivery records
2208 -- x_delivery_addnl_attr_tab Delivery records
2209 -- p_group_tab Groups created
2210 -- p_max_trip_weight Max weight of consolidation trip
2211 -- p_max_weight_uom Max weight uom
2212 -- p_trip_name_prefix Consolidation trip name prefix
2213 -- x_consol_trip_id Consolidation trip ids
2214 -- x_consol_del_id Consolidation delivery ids
2215 -- x_trips_all List of all the trips created
2216 -- x_failed_records Records that failed consolidation
2217 -- x_return_status Return status
2218 --
2219 -- COMMENT : This procedure takes the delivery records and the
2220 -- groups created and creates consolidated deliveries.
2221 -- The max consolidation trip weight and trip name prefix
2222 -- can also be specified.
2223 --========================================================================
2224 PROCEDURE Create_Consolidations(
2225 x_delivery_tab IN OUT NOCOPY WSH_FTE_CONSTRAINT_FRAMEWORK.delivery_ccinfo_tab_type,
2226 x_delivery_addnl_attr_tab IN OUT NOCOPY addnl_del_attr_tab_type,
2227 p_group_tab IN grp_attr_tab_type,
2228 p_max_trip_weight IN NUMBER,
2229 p_max_weight_uom IN VARCHAR2,
2230 p_trip_name_prefix IN VARCHAR2,
2231 x_consol_trip_id OUT NOCOPY WSH_UTIL_CORE.id_tab_type,
2232 x_consol_del_id OUT NOCOPY WSH_UTIL_CORE.id_tab_type,
2233 x_trips_all OUT NOCOPY WSH_UTIL_CORE.id_tab_type,
2234 x_failed_records OUT NOCOPY WSH_UTIL_CORE.id_tab_type,
2235 x_return_status OUT NOCOPY VARCHAR2)
2236 IS
2237
2238 l_trip_weight_flag BOOLEAN := FALSE;
2239 l_consol_index NUMBER; -- relative count of record in consol tab
2240 l_del_tab_grp WSH_UTIL_CORE.id_tab_type;
2241 l_d_id NUMBER;
2242 l_del_weight NUMBER;
2243 l_failed_index NUMBER;
2244 l_del_rec WSH_NEW_DELIVERIES_PVT.Delivery_Rec_Type;
2245 l_action_params WSH_DELIVERIES_GRP.Action_Parameters_Rectype;
2246 l_consol_tab consolidation_tab;
2247 l_init_msg_list VARCHAR2(1000);
2248 l_delivery_out_rec WSH_DELIVERIES_GRP.Delivery_Action_Out_Rec_Type;
2249 l_defaults_rec WSH_DELIVERIES_GRP.Default_Parameters_Rectype;
2250 l_index NUMBER;
2251 l_consol_count NUMBER; -- count of consolidations so far
2252 l_count NUMBER; -- l_consol_count + l_consol_index
2253 l_mesg_count NUMBER;
2254 l_mesg_data VARCHAR2(2000);
2255 l_return_status VARCHAR2(1);
2256 l_message VARCHAR2(2000);
2257 l_trips_all_count NUMBER;
2258 l_api_version_number NUMBER := 1.0;
2259 l_commit VARCHAR2(10);
2260 i NUMBER;
2261 j NUMBER;
2262 k NUMBER;
2263 m NUMBER;
2264 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'CREATE_CONSOLIDATIONS';
2265 l_debug_on BOOLEAN;
2266
2267 BEGIN
2268
2269 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2270 IF l_debug_on IS NULL
2271 THEN
2272 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2273 END IF;
2274
2275 IF l_debug_on THEN
2276 WSH_DEBUG_SV.push(l_module_name);
2277 WSH_DEBUG_SV.log(l_module_name,'delivery count',x_delivery_tab.COUNT);
2278 WSH_DEBUG_SV.log(l_module_name,'group count',p_group_tab.COUNT);
2279 WSH_DEBUG_SV.log(l_module_name,'trip max weight',p_max_trip_weight);
2280 WSH_DEBUG_SV.log(l_module_name,'trip weight uom',p_max_weight_uom);
2281 WSH_DEBUG_SV.log(l_module_name,'trip name prefix',p_trip_name_prefix);
2282 WSH_DEBUG_SV.log(l_module_name,'Current Time is ',SYSDATE);
2283 END IF;
2284
2285 l_commit := FND_API.g_false;
2286 IF (p_max_trip_weight IS NOT NULL AND p_max_weight_uom IS NOT NULL)
2287 THEN
2288 l_trip_weight_flag := TRUE;
2289 END IF;
2290
2291 WSH_UTIL_CORE.PrintMsg('Creating consolidations');
2292 IF l_trip_weight_flag
2293 THEN
2294 WSH_UTIL_CORE.PrintMsg(' Note: Consolidation trip weight threshold is '||p_max_trip_weight||' '||p_max_weight_uom);
2295 IF l_debug_on THEN
2296 WSH_DEBUG_SV.logmsg(l_module_name,'Consolidation Trip weight taken into consideration');
2297 END IF;
2298 ELSE
2299 IF l_debug_on THEN
2300 WSH_DEBUG_SV.logmsg(l_module_name,'Consolidation Trip weight ignored');
2301 END IF;
2302 END IF;
2303
2304 l_failed_index := x_failed_records.LAST;
2305 IF l_failed_index IS NULL
2306 THEN
2307 l_failed_index := 0;
2308 END IF;
2309
2310
2311 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2312 l_consol_count := 0;
2313 l_trips_all_count := 0;
2314 i := p_group_tab.FIRST;
2315 -- FOR l_index_i IN 1 .. p_group_tab.COUNT
2316 WHILE i IS NOT NULL
2317 LOOP
2318 --{
2319 --p_consol_del_id is a table of consolidation deliveries created
2320 l_del_tab_grp := p_group_tab(i).delivery_list;
2321 l_consol_tab.DELETE;
2322 j := l_del_tab_grp.FIRST;
2323 WSH_UTIL_CORE.PrintMsg(' Consolidating for group '||p_group_tab(i).group_id);
2324 --FOR l_index_j IN 1 .. l_del_tab_grp.COUNT
2325 WHILE j IS NOT NULL
2326 LOOP
2327 --{
2328 l_d_id := l_del_tab_grp(j);
2329 -- Check for the max trip weight
2330 IF l_trip_weight_flag = TRUE
2331 THEN
2332 l_del_weight := x_delivery_addnl_attr_tab(l_d_id).gross_weight;
2333 -- Convert to the common uom of max trip weight
2334 IF x_delivery_addnl_attr_tab(l_d_id).weight_uom_code <> p_max_weight_uom
2335 THEN
2336 l_del_weight := WSH_WV_UTILS.convert_uom(
2337 x_delivery_addnl_attr_tab(l_d_id).weight_uom_code,
2338 p_max_weight_uom,
2339 x_delivery_addnl_attr_tab(l_d_id).gross_weight,
2340 0);
2341 END IF;
2342
2343 -- weight of current delivery exceeds threshold
2344 IF (l_del_weight > p_max_trip_weight)
2345 THEN
2346 --The delivery's weight exceeds the threshold.
2347 --Delete the delivery
2348 l_failed_index := l_failed_index +1;
2349 x_failed_records(l_failed_index) := l_d_id;
2350 x_delivery_tab.DELETE(l_d_id);
2351 x_delivery_addnl_attr_tab.DELETE(l_d_id);
2352 WSH_UTIL_CORE.PrintMsg('Delivery '||l_d_id||' weight exceeds threshold. Removing from consolidation');
2353 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
2354
2355 ELSE
2356 l_del_rec := Copy_Record(x_delivery_tab(l_d_id),x_delivery_addnl_attr_tab(l_d_id));
2357 IF l_debug_on THEN
2358 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_MDC_SRS.Add_For_Consolidation',WSH_DEBUG_SV.C_PROC_LEVEL);
2359 END IF;
2360
2361 Add_For_Consolidation(
2362 p_delivery_rec => l_del_rec,
2363 p_del_weight => l_del_weight,
2364 p_weight_uom => p_max_weight_uom,
2365 p_max_weight => p_max_trip_weight,
2366 p_ignore_weight => FALSE,
2367 x_consol_tab => l_consol_tab,
2368 x_consol_index => l_consol_index,
2369 x_return_status => l_return_status); -- relative index in the consolidation table
2370
2371 IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_SUCCESS)
2372 THEN
2373 IF l_debug_on THEN
2374 WSH_DEBUG_SV.logmsg(l_module_name, 'Delivery added for consolidation');
2375 END IF;
2376 ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING)
2377 THEN
2378 IF l_debug_on THEN
2379 WSH_DEBUG_SV.logmsg(l_module_name, 'Delivery added with warning');
2380 END IF;
2381 ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR)
2382 OR (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)
2383 THEN
2384 WSH_UTIL_CORE.PrintMsg('Could not add delivery for consolidation');
2385 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2386 raise FND_API.G_EXC_ERROR;
2387 END IF;
2388
2389 x_delivery_addnl_attr_tab(l_d_id).consol_index := l_consol_index + l_consol_count;
2390
2391 END IF;
2392
2393 ELSE -- weight flag is not checked
2394 l_del_rec := Copy_Record(x_delivery_tab(l_d_id),x_delivery_addnl_attr_tab(l_d_id));
2395 IF l_debug_on THEN
2396 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_MDC_SRS.Add_For_Consolidation',WSH_DEBUG_SV.C_PROC_LEVEL);
2397 END IF;
2398
2399 Add_For_Consolidation(
2400 p_delivery_rec => l_del_rec,
2401 p_del_weight => l_del_weight,
2402 p_weight_uom => null,
2403 p_max_weight => -1,
2404 p_ignore_weight => TRUE,
2405 x_consol_tab => l_consol_tab,
2406 x_consol_index => l_consol_index,
2407 x_return_status => l_return_status); -- relative index in the consolidation table
2408
2409 IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_SUCCESS)
2410 THEN
2411 IF l_debug_on THEN
2412 WSH_DEBUG_SV.logmsg(l_module_name, 'Delivery added for consolidation');
2413 END IF;
2414 ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING)
2415 THEN
2416 IF l_debug_on THEN
2417 WSH_DEBUG_SV.logmsg(l_module_name, 'Delivery added with warning');
2418 END IF;
2419 ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR)
2420 OR (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)
2421 THEN
2422 WSH_UTIL_CORE.PrintMsg('Could not add delivery for consolidation');
2423 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2424 raise FND_API.G_EXC_ERROR;
2425 END IF;
2426
2427 x_delivery_addnl_attr_tab(l_d_id).consol_index := l_consol_index + l_consol_count;
2428
2429 END IF; --end if for the trip weight check
2430
2431 j := l_del_tab_grp.NEXT(j);
2432 --}
2433 END LOOP; -- end loop for the deliveries belonging to same group
2434
2435 IF l_consol_tab.COUNT > 0 --if there are consolidations
2436 THEN
2437 --{
2438 l_action_params.action_code := 'CREATE-CONSOL-DEL';
2439 l_action_params.caller := 'WSH_CONSOL_SRS';
2440 l_action_params.trip_name := p_trip_name_prefix;
2441
2442 k := l_consol_tab.FIRST;
2443 WHILE k IS NOT NULL
2444 LOOP
2445 --{
2446
2447 -- Create consol deliveries
2448 IF l_debug_on THEN
2449 WSH_DEBUG_SV.logmsg(l_module_name,'Creating consolidation delivery for consolidation no. '||l_consol_tab(k).consol_index);
2450 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERIES_GRP.Delivery_ACTION',WSH_DEBUG_SV.C_PROC_LEVEL);
2451 END IF;
2452
2453 WSH_DELIVERIES_GRP.Delivery_ACTION(
2454 p_api_version_number => l_api_version_number,
2455 p_init_msg_list => l_init_msg_list,
2456 p_commit => l_commit,
2457 p_action_prms => l_action_params,
2458 p_rec_attr_tab => l_consol_tab(k).delivery_tab,
2459 x_delivery_out_rec => l_delivery_out_rec,
2460 x_defaults_rec => l_defaults_rec,
2461 x_return_status => l_return_status,
2462 x_msg_count => l_mesg_count,
2463 x_msg_data => l_mesg_data);
2464
2465 IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_SUCCESS)
2466 THEN
2467 IF l_debug_on THEN
2468 WSH_DEBUG_SV.logmsg(l_module_name, 'Consolidation Created successfully');
2469 END IF;
2470 ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR)
2471 OR (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)
2472 THEN
2473 WSH_UTIL_CORE.PrintMsg('Error occurred in WSH_DELIVERIES_GRP.Delivery_ACTION');
2474 FOR i in 1..l_mesg_count
2475 LOOP
2476 l_message := fnd_msg_pub.get(m,'F');
2477 l_message := replace(l_message,chr(0),' ');
2478 WSH_UTIL_CORE.PrintMsg(l_message);
2479 END LOOP;
2480
2481 fnd_msg_pub.delete_msg();
2482 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2483 raise FND_API.G_EXC_ERROR;
2484 ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING)
2485 THEN
2486 IF l_debug_on THEN
2487 WSH_DEBUG_SV.logmsg(l_module_name, 'Created consolidations with following warnings');
2488 FOR i in 1..l_mesg_count
2489 LOOP
2490 l_message := fnd_msg_pub.get(m,'F');
2491 l_message := replace(l_message,chr(0),' ');
2492 WSH_DEBUG_SV.logmsg(l_module_name, l_message);
2493 END LOOP;
2494 END IF;
2495 fnd_msg_pub.delete_msg();
2496 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
2497 END IF;
2498
2499 l_index := l_delivery_out_rec.valid_ids_tab.FIRST;
2500 IF l_index IS NOT NULL
2501 THEN
2502 l_count := l_consol_tab(k).consol_index + l_consol_count;
2503 x_consol_del_id(l_count) := l_delivery_out_rec.result_id_tab(l_index);
2504 x_consol_trip_id(l_count) := l_delivery_out_rec.valid_ids_tab(l_index);
2505 -- Store trip id in a table for future calls to rating/ routing
2506 x_trips_all(l_trips_all_count) := x_consol_trip_id(l_count);
2507 l_trips_all_count := l_trips_all_count+1;
2508 END IF;
2509 WSH_UTIL_CORE.PrintMsg(' Consolidation Index: '||l_consol_tab(k).consol_index||', Consol Delivery: '||x_consol_del_id(l_count)||', Consol Trip: '||x_consol_trip_id(l_count));
2510 k := l_consol_tab.NEXT(k);
2511 --}
2512 END LOOP;
2513 --}
2514 END IF;
2515 l_consol_count := l_consol_count + l_consol_tab.COUNT;
2516 i := p_group_tab.NEXT(i);
2517
2518 --}
2519 END LOOP; -- end loop for table of groups
2520
2521 IF l_debug_on THEN
2522 WSH_DEBUG_SV.pop(l_module_name);
2523 END IF;
2524 EXCEPTION
2525 WHEN FND_API.G_EXC_ERROR
2526 THEN
2527 WSH_UTIL_CORE.PrintMsg('Exception occurred in Create_Consolidation');
2528 IF l_debug_on THEN
2529 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
2530 END IF;
2531 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2532 WHEN OTHERS
2533 THEN
2534 WSH_UTIL_CORE.PrintMsg('SQLCODE: '||sqlcode||' SQLERRM: '||sqlerrm);
2535 WSH_UTIL_CORE.PrintMsg('Exception occurred in Create_Consolidation');
2536 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2537 IF l_debug_on THEN
2538 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2539 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
2540 END IF;
2541
2542 END Create_Consolidations;
2543
2544
2545 --========================================================================
2546 -- PROCEDURE : Create_Deconsol_Trips
2547 --
2548 -- PARAMETERS: x_delivery_tab Delivery records
2549 -- x_delivery_addnl_attr_tab Delivery records
2550 -- p_consol_trip_id Consolidation trip ids
2551 -- x_trips_all List of all the trips created
2552 -- x_return_status Return status
2553 --
2554 -- COMMENT : This procedure takes the delivery records and and creates
2555 -- cdeconsolidation trips for the deliveries.
2556 --========================================================================
2557 PROCEDURE Create_Deconsol_Trips(
2558 x_delivery_tab IN OUT NOCOPY WSH_FTE_CONSTRAINT_FRAMEWORK.delivery_ccinfo_tab_type,
2559 x_delivery_addnl_attr_tab IN OUT NOCOPY addnl_del_attr_tab_type,
2560 p_consol_trip_id IN WSH_UTIL_CORE.id_tab_type,
2561 p_trip_name_prefix IN VARCHAR2,
2562 x_trips_all IN OUT NOCOPY WSH_UTIL_CORE.id_tab_type,
2563 x_return_status OUT NOCOPY VARCHAR2)
2564 IS
2565 l_trip_in_rec WSH_TRIPS_GRP.tripInRecType;
2566 l_init_msg_list VARCHAR2(100);
2567 l_api_version_number NUMBER := 1.0;
2568 l_commit VARCHAR2(10);
2569 l_return_status VARCHAR2(1);
2570 l_msg_count NUMBER;
2571 l_msg_data VARCHAR2(2000);
2572 l_trip_info_tab WSH_TRIPS_PVT.Trip_Attr_Tbl_Type;
2573 l_trip_info_rec WSH_TRIPS_PVT.trip_rec_type;
2574 l_trip_out_rec_tab WSH_TRIPS_GRP.Trip_Out_Tab_Type;
2575 l_trip_id NUMBER;
2576 l_del_id NUMBER;
2577 l_trip_name VARCHAR2(30);
2578 l_stop_in_rec WSH_TRIP_STOPS_GRP.stopInRecType;
2579 l_pickup_stop_info WSH_TRIP_STOPS_PVT.Trip_Stop_Rec_Type;
2580 l_pickup_rec_attr_tab WSH_TRIP_STOPS_PVT.Stop_Attr_Tbl_Type;
2581 l_pickup_stop_out_tab WSH_TRIP_STOPS_GRP.stop_out_tab_type;
2582 l_dropoff_stop_info WSH_TRIP_STOPS_PVT.Trip_Stop_Rec_Type;
2583 l_dropoff_rec_attr_tab WSH_TRIP_STOPS_PVT.Stop_Attr_Tbl_Type;
2584 l_dropoff_stop_out_tab WSH_TRIP_STOPS_GRP.stop_out_tab_type;
2585 l_stop_wt_vol_out_tab WSH_TRIP_STOPS_GRP.Stop_Wt_Vol_tab_type;
2586 l_action_prms WSH_DELIVERIES_GRP.action_parameters_rectype;
2587 l_rec_attr_tab WSH_NEW_DELIVERIES_PVT.Delivery_Attr_Tbl_Type;
2588 l_delivery_out_rec WSH_DELIVERIES_GRP.Delivery_Action_Out_Rec_Type;
2589 l_defaults_rec WSH_DELIVERIES_GRP.default_parameters_rectype;
2590 l_unplan_action_prms WSH_TRIPS_GRP.action_parameters_rectype;
2591 l_unplan_rec_attr WSH_TRIPS_PVT.Trip_Attr_Tbl_Type;
2592 l_unplan_dummy_out WSH_TRIPS_GRP.tripActionOutRecType;
2593 l_unplan_dummy_def WSH_TRIPS_GRP.default_parameters_rectype;
2594 l_trips_all_count NUMBER;
2595 l_trip_cr_flag BOOLEAN;
2596 i NUMBER;
2597 m NUMBER;
2598 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'CREATE_DECONSOL_TRIPS';
2599 l_debug_on BOOLEAN;
2600 l_message VARCHAR2(2000);
2601
2602 CURSOR Get_Intermediate_Dropoff_Date(c_trip_id NUMBER) IS
2603 SELECT PLANNED_ARRIVAL_DATE
2604 FROM WSH_TRIP_STOPS
2605 WHERE TRIP_ID = c_trip_id
2606 ORDER BY STOP_SEQUENCE_NUMBER DESC;
2607
2608 BEGIN
2609
2610 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2611 IF l_debug_on IS NULL
2612 THEN
2613 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2614 END IF;
2615
2616 IF l_debug_on THEN
2617 WSH_DEBUG_SV.push(l_module_name);
2618 WSH_DEBUG_SV.log(l_module_name,'Current Time is ',SYSDATE);
2619 END IF;
2620
2621 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2622 WSH_UTIL_CORE.PrintMsg('Creating deconsolidation trips');
2623 i := x_delivery_tab.FIRST;
2624 l_trips_all_count := x_trips_all.LAST+1;
2625
2626 -- FOR i IN x_delivery_tab.FIRST .. x_delivery_tab.LAST
2627 WHILE i IS NOT NULL
2628 LOOP
2629 BEGIN
2630 l_trip_cr_flag := FALSE;
2631
2632 -- Checking if the deconsol location is same as ultimate dropoff
2633 -- Do not create deconsol trip in such case
2634 IF x_delivery_tab(i).intmed_ship_to_location_id = x_delivery_tab(i).ultimate_dropoff_location_id
2635 THEN
2636 WSH_UTIL_CORE.PrintMsg('Not creating deconsol trip for the delivery '||x_delivery_tab(i).delivery_id);
2637 ELSE
2638
2639 SAVEPOINT CREATE_TRIP;
2640 l_del_id := x_delivery_tab(i).delivery_id;
2641 IF l_debug_on THEN
2642 WSH_DEBUG_SV.logmsg(l_module_name,'Creating deconsol trip for delivery '||l_del_id);
2643 END IF;
2644
2645 OPEN Get_Intermediate_Dropoff_Date(p_consol_trip_id(x_delivery_addnl_attr_tab(i).consol_index));
2646 FETCH Get_Intermediate_Dropoff_Date INTO x_delivery_addnl_attr_tab(i).intermediate_pickup_date;
2647 CLOSE Get_Intermediate_Dropoff_Date;
2648
2649 IF x_delivery_tab(i).ultimate_dropoff_date IS NULL
2650 OR x_delivery_tab(i).ultimate_dropoff_date <= x_delivery_addnl_attr_tab(i).intermediate_pickup_date
2651 THEN
2652 x_delivery_tab(i).ultimate_dropoff_date := x_delivery_addnl_attr_tab(i).intermediate_pickup_date + 1;
2653 END IF;
2654
2655 -- Create a trip for the delivery
2656 l_trip_in_rec.caller := 'WSH';
2657 l_trip_in_rec.phase := NULL;
2658 l_trip_in_rec.action_code := 'CREATE';
2659
2660 l_trip_info_tab.DELETE;
2661
2662 l_commit := FND_API.g_false;
2663 IF l_debug_on THEN
2664 WSH_DEBUG_SV.logmsg(l_module_name,'Creating deconsol trip');
2665 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIPS_GRP.Create_Update_Trip',WSH_DEBUG_SV.C_PROC_LEVEL);
2666 END IF;
2667
2668 WSH_TRIPS_GRP.Create_Update_Trip(
2669 p_api_version_number => l_api_version_number,
2670 p_init_msg_list => l_init_msg_list,
2671 p_commit => l_commit,
2672 x_return_status => l_return_status,
2673 x_msg_count => l_msg_count,
2674 x_msg_data => l_msg_data,
2675 p_trip_info_tab => l_trip_info_tab,
2676 p_in_rec => l_trip_in_rec,
2677 x_out_tab => l_trip_out_rec_tab);
2678
2679
2680 IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_SUCCESS)
2681 THEN
2682 IF l_debug_on THEN
2683 WSH_DEBUG_SV.logmsg(l_module_name, 'Created trip succesfully');
2684 END IF;
2685 ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR)
2686 OR (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)
2687 THEN
2688 WSH_UTIL_CORE.PrintMsg('Error occurred in WSH_TRIPS_GRP.Create_Update_Trip');
2689 IF l_debug_on THEN
2690 FOR m in 1..l_msg_count
2691 LOOP
2692 l_message := fnd_msg_pub.get(m,'F');
2693 l_message := replace(l_message,chr(0),' ');
2694 WSH_DEBUG_SV.logmsg(l_module_name,l_message);
2695 END LOOP;
2696 END IF;
2697 fnd_msg_pub.delete_msg();
2698 raise FND_API.G_EXC_ERROR;
2699
2700 ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING)
2701 THEN
2702 IF l_debug_on THEN
2703 WSH_DEBUG_SV.logmsg(l_module_name,'Trip created with warnings');
2704 FOR m in 1..l_msg_count
2705 LOOP
2706 l_message := fnd_msg_pub.get(m,'F');
2707 l_message := replace(l_message,chr(0),' ');
2708 WSH_DEBUG_SV.logmsg(l_module_name,l_message);
2709 END LOOP;
2710 END IF;
2711 fnd_msg_pub.delete_msg();
2712 END IF;
2713
2714 l_trip_id := l_trip_out_rec_tab(l_trip_out_rec_tab.FIRST).trip_id;
2715 l_trip_name := l_trip_out_rec_tab(l_trip_out_rec_tab.FIRST).trip_name;
2716
2717
2718 IF p_trip_name_prefix IS NOT NULL
2719 THEN
2720 -- Rename the trip by attaching the prefix
2721 l_trip_in_rec.caller := 'WSH';
2722 l_trip_in_rec.phase := NULL;
2723 l_trip_in_rec.action_code := 'UPDATE';
2724
2725 l_trip_info_rec.trip_id := l_trip_id;
2726 l_trip_name := p_trip_name_prefix ||'-'||l_trip_name;
2727 l_trip_info_rec.name := l_trip_name;
2728 l_trip_info_tab(0) := l_trip_info_rec;
2729
2730 IF l_debug_on THEN
2731 WSH_DEBUG_SV.logmsg(l_module_name,'Renaming deconsol trip, attaching prefix '||p_trip_name_prefix);
2732 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIPS_GRP.Create_Update_Trip',WSH_DEBUG_SV.C_PROC_LEVEL);
2733 END IF;
2734
2735
2736 WSH_TRIPS_GRP.Create_Update_Trip(
2737 p_api_version_number => l_api_version_number,
2738 p_init_msg_list => l_init_msg_list,
2739 p_commit => l_commit,
2740 x_return_status => l_return_status,
2741 x_msg_count => l_msg_count,
2742 x_msg_data => l_msg_data,
2743 p_trip_info_tab => l_trip_info_tab,
2744 p_in_rec => l_trip_in_rec,
2745 x_out_tab => l_trip_out_rec_tab);
2746
2747
2748 IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_SUCCESS)
2749 THEN
2750 IF l_debug_on THEN
2751 WSH_DEBUG_SV.logmsg(l_module_name, 'Renamed trip succesfully');
2752 END IF;
2753 ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR)
2754 OR (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)
2755 THEN
2756 WSH_UTIL_CORE.PrintMsg('Error occurred in WSH_TRIPS_GRP.Create_Update_Trip');
2757 IF l_debug_on THEN
2758 FOR m in 1..l_msg_count
2759 LOOP
2760 l_message := fnd_msg_pub.get(m,'F');
2761 l_message := replace(l_message,chr(0),' ');
2762 WSH_DEBUG_SV.logmsg(l_module_name,l_message);
2763 END LOOP;
2764 END IF;
2765 fnd_msg_pub.delete_msg();
2766 raise FND_API.G_EXC_ERROR;
2767
2768 ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING)
2769 THEN
2770 IF l_debug_on THEN
2771 WSH_DEBUG_SV.logmsg(l_module_name,'Trip renamed with warnings');
2772 FOR m in 1..l_msg_count
2773 LOOP
2774 l_message := fnd_msg_pub.get(m,'F');
2775 l_message := replace(l_message,chr(0),' ');
2776 WSH_DEBUG_SV.logmsg(l_module_name,l_message);
2777 END LOOP;
2778 END IF;
2779 fnd_msg_pub.delete_msg();
2780 END IF;
2781
2782 END IF;
2783
2784 --Store trip id in a table for future calls to routing/ rating
2785 x_trips_all(l_trips_all_count) := l_trip_id;
2786 l_trips_all_count := l_trips_all_count+1;
2787 l_trip_cr_flag := TRUE;
2788
2789 -- Update delivery tab with trip_id created
2790 x_delivery_addnl_attr_tab(i).deconsol_trip_id := l_trip_id;
2791 x_delivery_addnl_attr_tab(i).deconsol_trip_name := l_trip_name;
2792
2793
2794 -- Ignore trip for planning
2795
2796 l_unplan_action_prms.caller := 'WSH';
2797 l_unplan_action_prms.action_code := 'IGNORE_PLAN';
2798
2799 l_unplan_rec_attr(1).trip_id := l_trip_id;
2800
2801
2802
2803
2804 WSH_TRIPS_GRP.Trip_Action
2805 ( p_api_version_number => l_api_version_number,
2806 p_init_msg_list => l_init_msg_list,
2807 p_commit => l_commit,
2808 p_action_prms => l_unplan_action_prms,
2809 p_rec_attr_tab => l_unplan_rec_attr,
2810 x_trip_out_rec => l_unplan_dummy_out,
2811 x_def_rec => l_unplan_dummy_def,
2812 x_return_status => l_return_status,
2813 x_msg_count => l_msg_count,
2814 x_msg_data => l_msg_data);
2815
2816 IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_SUCCESS)
2817 THEN
2818 IF l_debug_on THEN
2819 WSH_DEBUG_SV.logmsg(l_module_name, 'Trip unplanned succesfully');
2820 END IF;
2821 ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR)
2822 OR (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)
2823 THEN
2824 WSH_UTIL_CORE.PrintMsg('Error occurred in WSH_TRIPS_GRP.Trip_Action');
2825 IF l_debug_on THEN
2826 FOR m in 1..l_msg_count
2827 LOOP
2828 l_message := fnd_msg_pub.get(m,'F');
2829 l_message := replace(l_message,chr(0),' ');
2830 WSH_DEBUG_SV.logmsg(l_module_name,l_message);
2831 END LOOP;
2832 END IF;
2833 fnd_msg_pub.delete_msg();
2834 raise FND_API.G_EXC_ERROR;
2835
2836 ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING)
2837 THEN
2838 IF l_debug_on THEN
2839 WSH_DEBUG_SV.logmsg(l_module_name,'Trip unplanned with warnings');
2840 FOR m in 1..l_msg_count
2841 LOOP
2842 l_message := fnd_msg_pub.get(m,'F');
2843 l_message := replace(l_message,chr(0),' ');
2844 WSH_DEBUG_SV.logmsg(l_module_name,l_message);
2845 END LOOP;
2846 END IF;
2847 fnd_msg_pub.delete_msg();
2848 END IF;
2849
2850
2851
2852 --Create pickup stop for the trip using intermediate_ship_to
2853
2854 l_pickup_stop_info.TRIP_ID := l_trip_id;
2855 l_pickup_stop_info.STOP_LOCATION_ID:= x_delivery_tab(i).intmed_ship_to_location_id;
2856 l_pickup_stop_info.planned_arrival_date := x_delivery_addnl_attr_tab(i).intermediate_pickup_date;
2857 l_pickup_stop_info.planned_departure_date := x_delivery_addnl_attr_tab(i).intermediate_pickup_date;
2858 l_pickup_rec_attr_tab(1):=l_pickup_stop_info;
2859
2860 l_stop_in_rec.caller := 'WSH';
2861 l_stop_in_rec.phase := NULL;
2862 l_stop_in_rec.action_code := 'CREATE';
2863 IF l_debug_on THEN
2864 WSH_DEBUG_SV.logmsg(l_module_name,'Creating pickup stop');
2865 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIP_STOPS_GRP.CREATE_UPDATE_STOP',WSH_DEBUG_SV.C_PROC_LEVEL);
2866 END IF;
2867
2868 WSH_TRIP_STOPS_GRP.CREATE_UPDATE_STOP(
2869 p_api_version_number => l_api_version_number,
2870 p_init_msg_list => l_init_msg_list,
2871 p_commit => l_commit,
2872 p_in_rec => l_stop_in_rec,
2873 p_rec_attr_tab => l_pickup_rec_attr_tab,
2874 x_stop_out_tab => l_pickup_stop_out_tab,
2875 x_return_status => l_return_status,
2876 x_msg_count => l_msg_count,
2877 x_msg_data => l_msg_data,
2878 x_stop_wt_vol_out_tab => l_stop_wt_vol_out_tab);
2879
2880 IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_SUCCESS)
2881 THEN
2882 IF l_debug_on THEN
2883 WSH_DEBUG_SV.logmsg(l_module_name, 'Pickup Stop created succesfully');
2884 END IF;
2885 ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR)
2886 OR (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)
2887 THEN
2888 WSH_UTIL_CORE.PrintMsg('Error occurred in WSH_TRIP_STOPS_GRP.CREATE_UPDATE_STOP');
2889 IF l_debug_on THEN
2890 FOR m in 1..l_msg_count
2891 LOOP
2892 l_message := fnd_msg_pub.get(m,'F');
2893 l_message := replace(l_message,chr(0),' ');
2894 WSH_DEBUG_SV.logmsg(l_module_name,l_message);
2895 END LOOP;
2896 END IF;
2897 fnd_msg_pub.delete_msg();
2898 raise FND_API.G_EXC_ERROR;
2899
2900 ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING)
2901 THEN
2902 IF l_debug_on THEN
2903 WSH_DEBUG_SV.logmsg(l_module_name,'Pickup Stop created with warnings');
2904 FOR m in 1..l_msg_count
2905 LOOP
2906 l_message := fnd_msg_pub.get(m,'F');
2907 l_message := replace(l_message,chr(0),' ');
2908 WSH_DEBUG_SV.logmsg(l_module_name,l_message);
2909 END LOOP;
2910 END IF;
2911 fnd_msg_pub.delete_msg();
2912 END IF;
2913
2914 -- Create Dropoff stop using ultimate_ship_to
2915 l_dropoff_stop_info.TRIP_ID := l_trip_id;
2916 l_dropoff_stop_info.STOP_LOCATION_ID:= x_delivery_tab(i).ultimate_dropoff_location_id;
2917 l_dropoff_stop_info.planned_arrival_date := x_delivery_tab(i).ultimate_dropoff_date;
2918 l_dropoff_stop_info.planned_departure_date := x_delivery_tab(i).ultimate_dropoff_date;
2919 l_dropoff_rec_attr_tab(1):=l_dropoff_stop_info;
2920 IF l_debug_on THEN
2921 WSH_DEBUG_SV.logmsg(l_module_name,'Creating dropoff stop');
2922 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIP_STOPS_GRP.CREATE_UPDATE_STOP',WSH_DEBUG_SV.C_PROC_LEVEL);
2923 END IF;
2924
2925
2926 WSH_TRIP_STOPS_GRP.CREATE_UPDATE_STOP(
2927 p_api_version_number => l_api_version_number,
2928 p_init_msg_list => l_init_msg_list,
2929 p_commit => l_commit,
2930 p_in_rec => l_stop_in_rec,
2931 p_rec_attr_tab => l_dropoff_rec_attr_tab,
2932 x_stop_out_tab => l_dropoff_stop_out_tab,
2933 x_return_status => l_return_status,
2934 x_msg_count => l_msg_count,
2935 x_msg_data => l_msg_data,
2936 x_stop_wt_vol_out_tab => l_stop_wt_vol_out_tab);
2937
2938 IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_SUCCESS)
2939 THEN
2940 IF l_debug_on THEN
2941 WSH_DEBUG_SV.logmsg(l_module_name, 'Dropoff Stop created succesfully');
2942 END IF;
2943 ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR)
2944 OR (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)
2945 THEN
2946 WSH_UTIL_CORE.PrintMsg('Error occurred in WSH_TRIP_STOPS_GRP.CREATE_UPDATE_STOP');
2947 IF l_debug_on THEN
2948 FOR m in 1..l_msg_count
2949 LOOP
2950 l_message := fnd_msg_pub.get(m,'F');
2951 l_message := replace(l_message,chr(0),' ');
2952 WSH_DEBUG_SV.logmsg(l_module_name,l_message);
2953 END LOOP;
2954 END IF;
2955 fnd_msg_pub.delete_msg();
2956 raise FND_API.G_EXC_ERROR;
2957
2958 ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING)
2959 THEN
2960 IF l_debug_on THEN
2961 WSH_DEBUG_SV.logmsg(l_module_name,'Dropoff Stop created with warnings');
2962 FOR m in 1..l_msg_count
2963 LOOP
2964 l_message := fnd_msg_pub.get(m,'F');
2965 l_message := replace(l_message,chr(0),' ');
2966 WSH_DEBUG_SV.logmsg(l_module_name,l_message);
2967 END LOOP;
2968 END IF;
2969 fnd_msg_pub.delete_msg();
2970 END IF;
2971
2972 -- Assign delivery to the trip
2973
2974 l_action_prms.caller :='WSH';
2975 l_action_prms.phase :=NULL;
2976
2977 l_action_prms.action_code := 'ASSIGN-TRIP';
2978 l_action_prms.trip_id := l_trip_id;
2979 l_action_prms.trip_name := l_trip_name;
2980
2981 l_action_prms.pickup_stop_id := l_pickup_stop_out_tab(l_pickup_stop_out_tab.FIRST).stop_id;
2982 l_action_prms.pickup_loc_id := x_delivery_tab(i).intmed_ship_to_location_id;
2983 l_action_prms.pickup_arr_date := x_delivery_addnl_attr_tab(i).intermediate_pickup_date;
2984 l_action_prms.pickup_dep_date := x_delivery_addnl_attr_tab(i).intermediate_pickup_date;
2985
2986 l_action_prms.dropoff_stop_id := l_dropoff_stop_out_tab(l_dropoff_stop_out_tab.FIRST).stop_id;
2987 l_action_prms.dropoff_loc_id := x_delivery_tab(i).ultimate_dropoff_location_id;
2988 l_action_prms.dropoff_arr_date := x_delivery_tab(i).ultimate_dropoff_date;
2989 l_action_prms.dropoff_dep_date := x_delivery_tab(i).ultimate_dropoff_date;
2990
2991 l_rec_attr_tab(1).delivery_id := x_delivery_tab(i).delivery_id;
2992 l_rec_attr_tab(1).organization_id := x_delivery_tab(i).organization_id;
2993 l_rec_attr_tab(1).status_code := x_delivery_tab(i).status_code;
2994 l_rec_attr_tab(1).planned_flag := x_delivery_tab(i).planned_flag;
2995 l_rec_attr_tab(1).NAME := x_delivery_tab(i).NAME;
2996 l_rec_attr_tab(1).INITIAL_PICKUP_DATE := x_delivery_tab(i).INITIAL_PICKUP_DATE;
2997 l_rec_attr_tab(1).INITIAL_PICKUP_LOCATION_ID := x_delivery_tab(i).INITIAL_PICKUP_LOCATION_ID;
2998 l_rec_attr_tab(1).ULTIMATE_DROPOFF_LOCATION_ID := x_delivery_tab(i).ULTIMATE_DROPOFF_LOCATION_ID;
2999 l_rec_attr_tab(1).ULTIMATE_DROPOFF_DATE := x_delivery_tab(i).ULTIMATE_DROPOFF_DATE;
3000 l_rec_attr_tab(1).CUSTOMER_ID := x_delivery_tab(i).CUSTOMER_ID;
3001 l_rec_attr_tab(1).INTMED_SHIP_TO_LOCATION_ID := x_delivery_tab(i).INTMED_SHIP_TO_LOCATION_ID;
3002 l_rec_attr_tab(1).SHIP_METHOD_CODE := x_delivery_tab(i).SHIP_METHOD_CODE;
3003 l_rec_attr_tab(1).DELIVERY_TYPE := x_delivery_tab(i).DELIVERY_TYPE;
3004 l_rec_attr_tab(1).CARRIER_ID := x_delivery_tab(i).CARRIER_ID;
3005 l_rec_attr_tab(1).SERVICE_LEVEL := x_delivery_tab(i).SERVICE_LEVEL;
3006 l_rec_attr_tab(1).MODE_OF_TRANSPORT := x_delivery_tab(i).MODE_OF_TRANSPORT;
3007 l_rec_attr_tab(1).shipment_direction := x_delivery_tab(i).shipment_direction;
3008 l_rec_attr_tab(1).party_id := x_delivery_tab(i).party_id;
3009 l_rec_attr_tab(1).shipping_control := x_delivery_tab(i).shipping_control;
3010
3011 IF l_debug_on THEN
3012 WSH_DEBUG_SV.logmsg(l_module_name,'Assigning delivery to trip');
3013 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERIES_GRP.Delivery_Action',WSH_DEBUG_SV.C_PROC_LEVEL);
3014 END IF;
3015
3016
3017 WSH_DELIVERIES_GRP.Delivery_Action(
3018 p_api_version_number => l_api_version_number,
3019 p_init_msg_list => l_init_msg_list,
3020 p_commit => l_commit,
3021 p_action_prms => l_action_prms,
3022 p_rec_attr_tab => l_rec_attr_tab,
3023 x_delivery_out_rec => l_delivery_out_rec,
3024 x_defaults_rec => l_defaults_rec,
3025 x_return_status => l_return_status,
3026 x_msg_count => l_msg_count,
3027 x_msg_data => l_msg_data);
3028
3029 IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_SUCCESS)
3030 THEN
3031 IF l_debug_on THEN
3032 WSH_DEBUG_SV.logmsg(l_module_name, 'Assigned delivery to trip succesfully');
3033 END IF;
3034 ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR)
3035 OR (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)
3036 THEN
3037 WSH_UTIL_CORE.PrintMsg('Error occurred in WSH_DELIVERIES_GRP.Delivery_Action');
3038 IF l_debug_on THEN
3039 FOR m in 1..l_msg_count
3040 LOOP
3041 l_message := fnd_msg_pub.get(m,'F');
3042 l_message := replace(l_message,chr(0),' ');
3043 WSH_DEBUG_SV.logmsg(l_module_name,l_message);
3044 END LOOP;
3045 END IF;
3046 fnd_msg_pub.delete_msg();
3047 raise FND_API.G_EXC_ERROR;
3048
3049 ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING)
3050 THEN
3051 IF l_debug_on THEN
3052 WSH_DEBUG_SV.logmsg(l_module_name,'Assigned delivery to trip with warnings');
3053 FOR m in 1..l_msg_count
3054 LOOP
3055 l_message := fnd_msg_pub.get(m,'F');
3056 l_message := replace(l_message,chr(0),' ');
3057 WSH_DEBUG_SV.logmsg(l_module_name,l_message);
3058 END LOOP;
3059 END IF;
3060 fnd_msg_pub.delete_msg();
3061 END IF;
3062 WSH_UTIL_CORE.PrintMsg(' Delivery: '||x_delivery_tab(i).delivery_id||', Trip: '||l_trip_name);
3063
3064 END IF;
3065
3066 i := x_delivery_tab.NEXT(i);
3067
3068 EXCEPTION
3069 WHEN FND_API.G_EXC_ERROR
3070 THEN
3071 ROLLBACK TO CREATE_TRIP;
3072 WSH_UTIL_CORE.PrintMsg('Exception occurred while creating deconsol trip for delivery '||l_del_id);
3073 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3074 i := x_delivery_tab.NEXT(i);
3075 IF l_trip_cr_flag
3076 THEN
3077 x_trips_all(l_trips_all_count) := null;
3078 l_trips_all_count := l_trips_all_count-1;
3079 END IF;
3080 WHEN OTHERS
3081 THEN
3082 ROLLBACK TO CREATE_TRIP;
3083 WSH_UTIL_CORE.PrintMsg('Exception occurred while creating deconsol trip for delivery '||l_del_id);
3084 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
3085 IF l_debug_on THEN
3086 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
3087 END IF;
3088 i := x_delivery_tab.NEXT(i);
3089 IF l_trip_cr_flag
3090 THEN
3091 x_trips_all(l_trips_all_count) := null;
3092 l_trips_all_count := l_trips_all_count-1;
3093 END IF;
3094 END;
3095 END LOOP;
3096 IF l_debug_on THEN
3097 WSH_DEBUG_SV.pop(l_module_name);
3098 END IF;
3099
3100 EXCEPTION
3101 WHEN FND_API.G_EXC_ERROR
3102 THEN
3103 WSH_UTIL_CORE.PrintMsg('Exception occurred in Create_Deconsol_Trip');
3104 IF l_debug_on THEN
3105 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
3106 END IF;
3107 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3108 WHEN OTHERS
3109 THEN
3110 WSH_UTIL_CORE.PrintMsg('SQLCODE: '||sqlcode||' SQLERRM: '||sqlerrm);
3111 WSH_UTIL_CORE.PrintMsg('Exception occurred in Create_Deconsol_Trip');
3112 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
3113 IF l_debug_on THEN
3114 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
3115 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
3116 END IF;
3117 END Create_Deconsol_Trips;
3118
3119 PROCEDURE Add_For_Consolidation(
3120 p_delivery_rec IN WSH_NEW_DELIVERIES_PVT.Delivery_Rec_Type,
3121 p_del_weight IN NUMBER,
3122 p_weight_uom IN VARCHAR2,
3123 p_max_weight IN NUMBER,
3124 p_ignore_weight IN BOOLEAN,
3125 x_consol_tab IN OUT NOCOPY consolidation_tab,
3126 x_consol_index OUT NOCOPY NUMBER,
3127 x_return_status OUT NOCOPY VARCHAR2)
3128 IS
3129 i NUMBER;
3130 l_consol_rec consolidation_rec;
3131 l_del_count NUMBER;
3132 l_consol_count NUMBER;
3133 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'ADD_FOR_CONSOLIDATION';
3134 l_debug_on BOOLEAN;
3135
3136 BEGIN
3137
3138 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
3139 IF l_debug_on IS NULL
3140 THEN
3141 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
3142 END IF;
3143
3144 IF l_debug_on THEN
3145 WSH_DEBUG_SV.push(l_module_name);
3146 WSH_DEBUG_SV.log(l_module_name,'delivery_id ',p_delivery_rec.delivery_id);
3147 WSH_DEBUG_SV.log(l_module_name,'Current Time is ',SYSDATE);
3148 END IF;
3149
3150 l_consol_count := x_consol_tab.LAST;
3151 IF l_consol_count IS NULL
3152 THEN
3153 l_consol_count := 0;
3154 END IF;
3155
3156 i := x_consol_tab.FIRST;
3157 WHILE i IS NOT NULL
3158 LOOP
3159 --{
3160 IF x_consol_tab(i).total_weight + p_del_weight <= p_max_weight -- check for weight
3161 OR p_ignore_weight = TRUE -- no need to check weight. add to the first record
3162 THEN
3163 -- found a consolidation which can accomodate the delivery
3164 l_del_count := x_consol_tab(i).delivery_tab.LAST;
3165 x_consol_tab(i).delivery_tab(l_del_count + 1) := p_delivery_rec;
3166 x_consol_tab(i).total_weight := x_consol_tab(i).total_weight + p_del_weight;
3167 x_consol_index := x_consol_tab(i).consol_index;
3168 IF l_debug_on THEN
3169 WSH_DEBUG_SV.logmsg(l_module_name,'Adding to existing consolidation group');
3170 END IF;
3171 EXIT;
3172 END IF;
3173
3174 i := x_consol_tab.NEXT(i);
3175 --}
3176 END LOOP;
3177
3178 -- no match found
3179 IF i IS NULL
3180 THEN
3181 -- make a new rec
3182 l_consol_rec.consol_index := l_consol_count + 1;
3183 l_consol_rec.total_weight := p_del_weight;
3184 l_consol_rec.weight_uom := p_weight_uom;
3185 l_consol_rec.delivery_tab(1) := p_delivery_rec;
3186 x_consol_tab(l_consol_count+1) := l_consol_rec;
3187 x_consol_index := l_consol_rec.consol_index;
3188 IF l_debug_on THEN
3189 WSH_DEBUG_SV.logmsg(l_module_name,'Creating new consolidation group');
3190 END IF;
3191
3192 END IF;
3193
3194 WSH_UTIL_CORE.PrintMsg(' Delivery: '||p_delivery_rec.delivery_id||', Consolidation Index: '||x_consol_index);
3195 IF l_debug_on THEN
3196 WSH_DEBUG_SV.pop(l_module_name);
3197 END IF;
3198 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
3199 EXCEPTION
3200 WHEN OTHERS
3201 THEN
3202 IF l_debug_on THEN
3203 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
3204 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
3205 END IF;
3206 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
3207 END Add_For_Consolidation;
3208
3209 END WSH_MDC_SRS;
3210