[Home] [Help]
PACKAGE BODY: APPS.FTE_SOURCE_LINE_CONSOLIDATION
Source
1 PACKAGE BODY FTE_SOURCE_LINE_CONSOLIDATION AS
2 /* $Header: FTELNCNB.pls 120.2 2008/01/11 08:50:00 sankarun ship $ */
3
4 g_weight_uom_tab uom_tab; -- will cache default weight uom for an org
5 g_volume_uom_tab uom_tab; -- will cache default volume uom for an org
6
7 g_hash_base NUMBER := 1;
8 g_hash_size NUMBER := power(2, 25); -- do ours need to be so big? how large?
9
10 TYPE source_header_hash_rec IS RECORD
11 (con_id NUMBER,
12 hash_string VARCHAR2(1000));
13
14 TYPE source_header_hash_tab IS TABLE OF source_header_hash_rec
15 INDEX BY BINARY_INTEGER;
16
17 g_source_header_hash_tab source_header_hash_tab;
18
19 -- cache the weight and volume uom for orgs
20 --
21 G_PKG_NAME CONSTANT VARCHAR2(50) := 'FTE_SOURCE_LINE_CONSOLIDATION';
22 --
23
24 -- Pack J OM-DisplayChoices
25 CURSOR c_get_generic_carrier_flag (c_ship_method_code VARCHAR2) IS
26 SELECT a.generic_flag
27 FROM wsh_carriers a, wsh_carrier_services b
28 WHERE a.carrier_id = b.carrier_id
29 AND b.ship_method_code = c_ship_method_code;
30
31 PROCEDURE get_org_default_uoms(p_org_id IN NUMBER,
32 x_weight_uom_code OUT NOCOPY VARCHAR2,
33 x_volume_uom_code OUT NOCOPY VARCHAR2,
34 x_return_status OUT NOCOPY VARCHAR2) IS
35
36 l_weight_uom_code VARCHAR2(3);
37 l_volume_uom_code VARCHAR2(3);
38 l_status VARCHAR(1);
39
40 --
41 l_debug_on BOOLEAN;
42 --
43 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'GET_ORG_DEFAULT_UOMS';
44 --
45 BEGIN
46
47 --
48 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
49 --
50 IF l_debug_on IS NULL
51 THEN
52 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
53 END IF;
54 --
55 --
56 -- Debug Statements
57 --
58 IF l_debug_on THEN
59 WSH_DEBUG_SV.push(l_module_name);
60 --
61 WSH_DEBUG_SV.log(l_module_name,'P_ORG_ID',P_ORG_ID);
62 END IF;
63 --
64 l_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
65
66 IF (g_weight_uom_tab.EXISTS(p_org_id)) THEN
67 l_weight_uom_code := g_weight_uom_tab(p_org_id);
68 END IF;
69
70 IF (g_volume_uom_tab.EXISTS(p_org_id)) THEN
71 l_volume_uom_code := g_volume_uom_tab(p_org_id);
72 END IF;
73
74 IF (l_weight_uom_code is null OR l_volume_uom_code is null) THEN
75
76 --
77 -- Debug Statements
78 --
79 IF l_debug_on THEN
80 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_WV_UTILS.GET_DEFAULT_UOMS',WSH_DEBUG_SV.C_PROC_LEVEL);
81 END IF;
82 --
83 WSH_WV_UTILS.get_default_uoms(p_org_id,
84 l_weight_uom_code,
85 l_volume_uom_code,
86 l_status);
87
88 IF (l_status is null) THEN
89 l_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
90 END IF;
91
92 g_weight_uom_tab(p_org_id) := l_weight_uom_code;
93 g_volume_uom_tab(p_org_id) := l_volume_uom_code;
94
95 END IF;
96
97 x_weight_uom_code := l_weight_uom_code;
98 x_volume_uom_code := l_volume_uom_code;
99 x_return_status := l_status;
100
101 --
102 -- Debug Statements
103 --
104 IF l_debug_on THEN
105 WSH_DEBUG_SV.logmsg(l_module_name, 'Returning org default uoms:');
106 WSH_DEBUG_SV.log(l_module_name, 'weight_uom_code', x_weight_uom_code);
107 WSH_DEBUG_SV.log(l_module_name, 'volume_uom_code', x_volume_uom_code);
108 WSH_DEBUG_SV.pop(l_module_name);
109 END IF;
110 --
111 END get_org_default_uoms;
112
113
114 --
115 -- PROCEDURE Create_Hash
116 -- ---------------------
117 -- Creates a hash value for a record passed in.
118 -- An attribute is part of the hash string only if it is part of the grouping attributes
119 --
120 --
121 -- CHANGE LOG
122 -- ----------
123 -- [2003/02/05][PACK I][ABLUNDEL][BUG:2763944]
124 -- Added arrival_date to the hash string as for this bug the
125 -- some line could have been scheduled previously and got one arrival date
126 -- then all lines are scheduled, giving the previously unscheduled lines
127 -- a different arrival date:
128 -- added line "to_char(trunc(p_source_line_rec.arrival_date)) || '-' ||"
129 --
130 --
131
132 --
133 -- Create Hash needs to be changed to return l_group_by_flags
134 -- Only if flag is populated we should propogate the value to header level
135 --
136 -- R12 Sachin
137 PROCEDURE Create_Hash(p_source_line_rec IN FTE_PROCESS_REQUESTS.fte_source_line_rec,
138 p_action IN VARCHAR2,
139 x_hash_value OUT NOCOPY NUMBER,
140 x_hash_string OUT NOCOPY VARCHAR2,
141 x_group_by_flags OUT NOCOPY WSH_DELIVERY_AUTOCREATE.group_by_flags_rec_type) IS
142
143 l_hash_string VARCHAR2(1000) := NULL;
144 l_hash_value NUMBER;
145
146 l_group_by_flags WSH_DELIVERY_AUTOCREATE.group_by_flags_rec_type;
147 l_status VARCHAR2(10);
148
149 l_con_hash_rec source_header_hash_rec;
150
151 l_counter PLS_INTEGER;
152 l_no_hash BOOLEAN := TRUE;
153 l_generic_carrier VARCHAR2(1);
154
155 --
156 l_debug_on BOOLEAN;
157 --
158 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'CREATE_HASH';
159 --
160 BEGIN
161
162 --
163 -- Get grouping attributes for this org, but ignoring ship method, carrier
164 --
165 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
166 --
167 IF l_debug_on IS NULL
168 THEN
169 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
170 END IF;
171 --
172 --
173 -- Debug Statements
174 --
175 IF l_debug_on THEN
176 WSH_DEBUG_SV.push(l_module_name);
177 --
178 WSH_DEBUG_SV.log(l_module_name,'P_ACTION',P_ACTION);
179 END IF;
180 --
181 --
182 -- Debug Statements
183 --
184 IF l_debug_on THEN
185 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_AUTOCREATE.GET_GROUP_BY_ATTR',WSH_DEBUG_SV.C_PROC_LEVEL);
186 END IF;
187 --
188 WSH_DELIVERY_AUTOCREATE.Get_Group_By_Attr(p_organization_id => p_source_line_rec.ship_from_org_id,
189 x_group_by_flags => l_group_by_flags,
190 x_return_status => l_status);
191
192 --
193 -- [2003/02/05][I][ABLUNDEL][BUG:2763944]
194 -- Added arrival_date to the hash string as for this bug the
195 -- some line could have been scheduled previously and got one arrival date
196 -- then all lines are scheduled, giving the previously unscheduled lines
197 -- a different arrival date:
198 --
199 -- added line "to_char(trunc(p_source_line_rec.arrival_date)) || '-' ||"
200 --
201
202 --
203 -- these 5 inputs are always in the grouping rule
204 --
205 l_hash_string := to_char(p_source_line_rec.ship_from_org_id) || '-' ||
206 to_char(p_source_line_rec.ship_to_location_id) || '-' ||
207 to_char(trunc(p_source_line_rec.ship_date)) || '-' ||
208 to_char(trunc(p_source_line_rec.arrival_date)) || '-' ||
209 p_source_line_rec.scheduled_flag;
210
211 IF (l_group_by_flags.customer = 'Y') THEN
212 l_hash_string := l_hash_string ||'-'||to_char(p_source_line_rec.customer_id);
213 IF l_debug_on THEN
214 WSH_DEBUG_SV.logmsg(l_module_name, 'Using customer in grouping rule');
215 END IF;
216 END IF;
217
218 IF (l_group_by_flags.intmed = 'Y') THEN
219 l_hash_string := l_hash_string ||'-'||to_char(p_source_line_rec.intmed_ship_to_loc_id);
220 IF l_debug_on THEN
221 WSH_DEBUG_SV.logmsg(l_module_name, 'Using intermediate ship to in grouping rule');
222 END IF;
223 END IF;
224
225 IF (l_group_by_flags.fob = 'Y') THEN
226 l_hash_string := l_hash_string ||'-'||p_source_line_rec.fob_code;
227 IF l_debug_on THEN
228 WSH_DEBUG_SV.logmsg(l_module_name, 'Using fob in grouping rule');
229 END IF;
230 END IF;
231
232 IF (l_group_by_flags.freight_terms = 'Y') THEN
233 l_hash_string := l_hash_string ||'-'||p_source_line_rec.freight_terms;
234 IF l_debug_on THEN
235 WSH_DEBUG_SV.logmsg(l_module_name, 'Using freight terms in grouping rule');
236 END IF;
237 END IF;
238
239 IF (p_action = 'R' AND l_group_by_flags.ship_method = 'Y') THEN
240 l_hash_string := l_hash_string ||'-'||p_source_line_rec.ship_method_code;
241 IF l_debug_on THEN
242 WSH_DEBUG_SV.logmsg(l_module_name, 'Using ship method in grouping rule');
243 END IF;
244 END IF;
245
246 -- Pack J OM-DisplayChoices
247 IF (p_action = 'GET_GROUP') THEN
248
249 IF l_debug_on THEN
250 WSH_DEBUG_SV.logmsg(l_module_name, 'for GET_GROUP action...');
251 WSH_DEBUG_SV.logmsg(l_module_name, 'ship_method_code='||p_source_line_rec.ship_method_code);
252 END IF;
253
254 OPEN c_get_generic_carrier_flag(p_source_line_rec.ship_method_code);
255 FETCH c_get_generic_carrier_flag INTO l_generic_carrier;
256 CLOSE c_get_generic_carrier_flag;
257
258 IF l_debug_on THEN
259 WSH_DEBUG_SV.logmsg(l_module_name, 'l_generic_carrier='||l_generic_carrier);
260 END IF;
261
262 IF l_generic_carrier = 'Y' THEN
263 l_hash_string := l_hash_string ||'-'||p_source_line_rec.ship_method_code;
264 IF l_debug_on THEN
265 WSH_DEBUG_SV.logmsg(l_module_name, 'generic carrier, Using ship method in grouping rule');
266 END IF;
267 ELSIF p_source_line_rec.override_ship_method = 'Y' THEN
268 IF l_debug_on THEN
269 WSH_DEBUG_SV.logmsg(l_module_name, 'not Using ship method in grouping rule');
270 END IF;
271 ELSE
272 l_hash_string := l_hash_string ||'-'||p_source_line_rec.ship_method_code;
273 IF l_debug_on THEN
274 WSH_DEBUG_SV.logmsg(l_module_name, 'override_ship_method=N, Using ship method in grouping rule');
275 END IF;
276 END IF;
277 END IF;
278
279 -- get hash value, check to make sure no hash collision
280 l_counter := 0;
281
282 WHILE (l_no_hash) LOOP
283
284 l_hash_value := dbms_utility.get_hash_value(name => l_hash_string,
285 base => g_hash_base,
286 hash_size => g_hash_size + l_counter);
287
288 IF (g_source_header_hash_tab.EXISTS(l_hash_value) = FALSE) THEN
289 l_no_hash := FALSE;
290 ELSE
291 l_con_hash_rec := g_source_header_hash_tab(l_hash_value);
292 IF (l_con_hash_rec.hash_string = l_hash_string) THEN
293 l_no_hash := FALSE;
294 ELSE -- hash collision, same hash value, different hash string
295 l_counter := l_counter + 1;
296 END IF;
297 END IF;
298
299 END LOOP;
300
301 x_hash_value := l_hash_value;
302 x_hash_string := l_hash_string;
303 x_group_by_flags := l_group_by_flags;
304 --
305 -- Debug Statements
306 --
307 IF l_debug_on THEN
308 WSH_DEBUG_SV.log(l_module_name, 'hash_string', l_hash_string);
309 WSH_DEBUG_SV.log(l_module_name, 'hash_value', l_hash_value);
310 WSH_DEBUG_SV.pop(l_module_name);
311 END IF;
312 --
313 END Create_Hash;
314
315 -- main procedure that will take in a table of source line inputs
316 -- and consolidate them based on grouping rules and sum the weights and volume
317 -- across lines and return back the consolidations
318 PROCEDURE Consolidate_Lines(p_source_line_tab IN OUT NOCOPY FTE_PROCESS_REQUESTS.fte_source_line_tab,
319 p_source_header_tab IN OUT NOCOPY FTE_PROCESS_REQUESTS.fte_source_header_tab,
320 p_action IN VARCHAR2,
321 x_return_status OUT NOCOPY VARCHAR2,
322 x_msg_count OUT NOCOPY NUMBER,
323 x_msg_data OUT NOCOPY VARCHAR2) IS
324
325 l_counter PLS_INTEGER;
326 l_con_id_seq PLS_INTEGER := 1;
327 l_con_id PLS_INTEGER;
328
329 l_source_header_rec FTE_PROCESS_REQUESTS.fte_source_header_rec;
330 l_hash_string VARCHAR2(1000);
331 l_con_hash_rec source_header_hash_rec;
332
333 l_weight_uom_code VARCHAR2(3);
334 l_volume_uom_code VARCHAR2(3);
335 l_status VARCHAR2(1);
336 l_msg_count PLS_INTEGER := 0;
337
338 l_converted_weight NUMBER;
339 l_converted_volume NUMBER;
340 -- l_ship_from_loc_id NUMBER;
341 l_customer_site_id NUMBER;
342 l_hash_value NUMBER;
343 l_generic_carrier VARCHAR2(1);
344 l_override_ship_method BOOLEAN;
345
346 l_group_by_flags WSH_DELIVERY_AUTOCREATE.group_by_flags_rec_type;
347
348 --bug 6707893: Added p_inv_id parameter in WSH_WV_UTILS.convert_uom called in the cursor
349 CURSOR get_converted_wv(p_org_id NUMBER, p_inv_id NUMBER, p_source_qty NUMBER,
350 p_source_qty_uom VARCHAR2, p_weight_uom VARCHAR2,
351 p_volume_uom VARCHAR2) IS
352 SELECT WSH_WV_UTILS.convert_uom(weight_uom_code,
353 p_weight_uom,
354 nvl(unit_weight, 0) * WSH_WV_UTILS.convert_uom(p_source_qty_uom,
355 primary_uom_code,
356 p_source_qty, p_inv_id), p_inv_id),
357 WSH_WV_UTILS.convert_uom(volume_uom_code,
358 p_volume_uom,
359 nvl(unit_volume, 0) * WSH_WV_UTILS.convert_uom(p_source_qty_uom,
360 primary_uom_code,
361 p_source_qty,p_inv_id),p_inv_id)
362 FROM mtl_system_items
363 WHERE organization_id = p_org_id
364 AND inventory_item_id = p_inv_id;
365
366 --
367 l_debug_on BOOLEAN;
368 --
369 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'CONSOLIDATE_LINES';
370 --
371 BEGIN
372
373 --
374 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
375 --
376 IF l_debug_on IS NULL
377 THEN
378 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
379 END IF;
380 --
381 --
382 -- Debug Statements
383 --
384 IF l_debug_on THEN
385 WSH_DEBUG_SV.push(l_module_name);
386 --
387 WSH_DEBUG_SV.log(l_module_name,'P_ACTION',P_ACTION);
388 END IF;
389 --
390
391 IF l_debug_on THEN
392 WSH_DEBUG_SV.logmsg(l_module_name,'loop through p_source_line_tab',WSH_DEBUG_SV.C_PROC_LEVEL);
393 END IF;
394
395 g_source_header_hash_tab.DELETE;
396
397 FOR l_counter IN p_source_line_tab.FIRST..p_source_line_tab.LAST LOOP
398
399 IF l_debug_on THEN
400 WSH_DEBUG_SV.logmsg(l_module_name,'l_counter = '||l_counter,WSH_DEBUG_SV.C_PROC_LEVEL);
401 END IF;
402
403 -- reset variables
404 l_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
405 l_weight_uom_code := NULL;
406 l_volume_uom_code := NULL;
407 l_converted_weight := NULL;
408 l_converted_volume := NULL;
409
410 -- get default base uoms for weight and volume
411 get_org_default_uoms(p_source_line_tab(l_counter).ship_from_org_id,
412 l_weight_uom_code,
413 l_volume_uom_code,
414 l_status);
415
416 IF (l_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
417 p_source_line_tab(l_counter).status := l_status;
418 FND_MESSAGE.SET_NAME('FTE', 'FTE_SEL_NO_DEFAULT_UOM');
419 p_source_line_tab(l_counter).message_data := FND_MESSAGE.GET;
420 END IF;
421
422 -- calculated weight and volume for this inventory item against requested quantity
423 IF (l_status = WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
424
425 OPEN get_converted_wv(p_source_line_tab(l_counter).ship_from_org_id,
426 p_source_line_tab(l_counter).inventory_item_id,
427 p_source_line_tab(l_counter).source_quantity,
428 p_source_line_tab(l_counter).source_quantity_uom,
429 l_weight_uom_code,
430 l_volume_uom_code);
431
432 FETCH get_converted_wv INTO l_converted_weight, l_converted_volume;
433 CLOSE get_converted_wv;
434
438 -- Debug Statements
435 -- obtain ship-from location id from ship-from org id
436 IF (p_source_line_tab(l_counter).ship_from_location_id is null) THEN
437 --
439 --
440 IF l_debug_on THEN
441 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.GET_LOCATION_ID',WSH_DEBUG_SV.C_PROC_LEVEL);
442 END IF;
443 --
444 WSH_UTIL_CORE.Get_Location_Id('ORG',
445 p_source_line_tab(l_counter).ship_from_org_id,
446 p_source_line_tab(l_counter).ship_from_location_id,
447 l_status);
448 END IF;
449
450 IF (l_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
451 p_source_line_tab(l_counter).status := l_status;
452 FND_MESSAGE.SET_NAME('WSH', 'WSH_ORG_LOCATION_UNDEFINED');
453 p_source_line_tab(l_counter).message_data := FND_MESSAGE.GET;
454 END IF;
455
456 -- obtain ship-to location id from ship-to cust site id (use 'CUSTOMER SITE')
457 IF (p_source_line_tab(l_counter).ship_to_location_id is null) THEN
458 --
459 -- Debug Statements
460 --
461 IF l_debug_on THEN
462 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.GET_LOCATION_ID',WSH_DEBUG_SV.C_PROC_LEVEL);
463 END IF;
464 --
465 WSH_UTIL_CORE.Get_Location_Id('CUSTOMER SITE',
466 p_source_line_tab(l_counter).ship_to_site_id,
467 p_source_line_tab(l_counter).ship_to_location_id,
468 l_status);
469 END IF;
470
471 IF (l_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
472 p_source_line_tab(l_counter).status := l_status;
473 FND_MESSAGE.SET_NAME('WSH', 'WSH_SITE_LOCATION_UNDEFINED');
474 p_source_line_tab(l_counter).message_data := FND_MESSAGE.GET;
475 END IF;
476
477 -- obtain intermediate ship-to location id from intermediate ship-to org id (use 'CUSTOMER SITE')
478 IF (p_source_line_tab(l_counter).intmed_ship_to_loc_id is null AND
479 p_source_line_tab(l_counter).intmed_ship_to_site_id is not null) THEN
480 --
481 -- Debug Statements
482 --
483 IF l_debug_on THEN
484 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.GET_LOCATION_ID',WSH_DEBUG_SV.C_PROC_LEVEL);
485 END IF;
486 --
487 WSH_UTIL_CORE.Get_Location_Id('CUSTOMER SITE',
488 p_source_line_tab(l_counter).intmed_ship_to_site_id,
489 p_source_line_tab(l_counter).intmed_ship_to_loc_id,
490 l_status);
491 END IF;
492
493 IF (l_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
494 p_source_line_tab(l_counter).status := l_status;
495 FND_MESSAGE.SET_NAME('WSH', 'WSH_SITE_LOCATION_UNDEFINED');
496 p_source_line_tab(l_counter).message_data := FND_MESSAGE.GET;
497 END IF;
498
499 IF (l_status = WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
500 -- get hash value for this source line
501 Create_Hash(p_source_line_rec => p_source_line_tab(l_counter),
502 p_action => p_action,
503 x_hash_value => l_hash_value,
504 x_hash_string => l_hash_string,
505 x_group_by_flags => l_group_by_flags);
506
507 -- check if consolidation is previously created
508 IF (g_source_header_hash_tab.EXISTS(l_hash_value)) THEN
509
510 l_con_id := g_source_header_hash_tab(l_hash_value).con_id;
511 l_source_header_rec := p_source_header_tab(l_con_id);
512
513 -- add weight and volume to existing consolidation
514 l_source_header_rec.total_weight := l_source_header_rec.total_weight + l_converted_weight;
515 l_source_header_rec.total_volume := l_source_header_rec.total_volume + l_converted_volume;
516
517 l_override_ship_method := false;
518
519 IF (p_action = 'GET_GROUP') THEN
520
521 IF l_debug_on THEN
522 WSH_DEBUG_SV.logmsg(l_module_name,'for GET_GROUP action...',WSH_DEBUG_SV.C_PROC_LEVEL);
523 WSH_DEBUG_SV.logmsg(l_module_name,'source line ship_method_code='||p_source_line_tab(l_counter).ship_method_code,WSH_DEBUG_SV.C_PROC_LEVEL);
524 WSH_DEBUG_SV.logmsg(l_module_name,'source line override_ship_method='||p_source_line_tab(l_counter).override_ship_method,WSH_DEBUG_SV.C_PROC_LEVEL);
525 END IF;
526
527 OPEN c_get_generic_carrier_flag(p_source_line_tab(l_counter).ship_method_code);
528 FETCH c_get_generic_carrier_flag INTO l_generic_carrier;
529 CLOSE c_get_generic_carrier_flag;
530
531 IF l_debug_on THEN
532 WSH_DEBUG_SV.logmsg(l_module_name,'l_generic_carrier='||l_generic_carrier,WSH_DEBUG_SV.C_PROC_LEVEL);
533 END IF;
534
535 IF (l_generic_carrier is null OR l_generic_carrier <> 'Y')
536 AND p_source_line_tab(l_counter).override_ship_method = 'Y' THEN
537 l_override_ship_method := true;
538 END IF;
539
540 END IF; -- p_action = 'GET_GROUP'
541
542 IF l_override_ship_method THEN
543
544 IF p_source_line_tab(l_counter).scheduled_flag = 'Y'
545 AND p_source_line_tab(l_counter).delivery_lead_time > 0 THEN
546
547 IF l_source_header_rec.scheduled_flag is NULL
548 OR l_source_header_rec.delivery_lead_time is NULL
549 OR l_source_header_rec.delivery_lead_time <= 0 THEN
550
554 IF l_debug_on THEN
551 l_source_header_rec.delivery_lead_time := p_source_line_tab(l_counter).delivery_lead_time;
552 l_source_header_rec.scheduled_flag := p_source_line_tab(l_counter).scheduled_flag;
553
555 WSH_DEBUG_SV.logmsg(l_module_name, 'override delivery lead time to '||p_source_line_tab(l_counter).delivery_lead_time);
556 END IF;
557 ELSE
558 IF p_source_line_tab(l_counter).delivery_lead_time
559 < l_source_header_rec.delivery_lead_time THEN
560 l_source_header_rec.delivery_lead_time :=
561 p_source_line_tab(l_counter).delivery_lead_time;
562 IF l_debug_on THEN
563 WSH_DEBUG_SV.logmsg(l_module_name, 'override delivery lead time to '||p_source_line_tab(l_counter).delivery_lead_time);
564 END IF;
565 END IF;
566 END IF;
567 END IF;
568 END IF; -- l_override_ship_method true
569
570 p_source_header_tab(l_con_id) := l_source_header_rec;
571
572 -- none was found, create new entry
573 ELSE
574
575 l_con_id := l_con_id_seq; -- obtain consolidation id
576
577 IF l_debug_on THEN
578 WSH_DEBUG_SV.logmsg(l_module_name,'create new group l_con_id = '||l_con_id,WSH_DEBUG_SV.C_PROC_LEVEL);
579 END IF;
580
581 -- copy input attributes to the consolidation line
582 l_source_header_rec.consolidation_id := l_con_id;
583 l_source_header_rec.ship_from_org_id := p_source_line_tab(l_counter).ship_from_org_id;
584 l_source_header_rec.ship_from_location_id := p_source_line_tab(l_counter).ship_from_location_id;
585 l_source_header_rec.ship_to_site_id := p_source_line_tab(l_counter).ship_to_site_id;
586 l_source_header_rec.ship_to_location_id := p_source_line_tab(l_counter).ship_to_location_id;
587
588 --
589 -- if (CUSTOMER) is in the grouping criteria then
590 -- copy it
591 -- Else
592 -- keep it null;
593 IF (l_group_by_flags.customer ='Y') THEN
594 l_source_header_rec.customer_id := p_source_line_tab(l_counter).customer_id;
595 END IF;
596
597 l_source_header_rec.ship_date := p_source_line_tab(l_counter).ship_date;
598 l_source_header_rec.arrival_date := p_source_line_tab(l_counter).arrival_date;
599 l_source_header_rec.delivery_lead_time := p_source_line_tab(l_counter).delivery_lead_time;
600 l_source_header_rec.currency := p_source_line_tab(l_counter).currency;
601 l_source_header_rec.currency_conversion_type := p_source_line_tab(l_counter).currency_conversion_type;
602
603 IF l_debug_on THEN
604 WSH_DEBUG_SV.logmsg(l_module_name, 'group initial delivery lead time '||p_source_line_tab(l_counter).delivery_lead_time);
605 END IF;
606 l_source_header_rec.scheduled_flag := p_source_line_tab(l_counter).scheduled_flag;
607 l_source_header_rec.total_weight := l_converted_weight;
608 l_source_header_rec.weight_uom_code := l_weight_uom_code;
609 l_source_header_rec.total_volume := l_converted_volume;
610 l_source_header_rec.volume_uom_code := l_volume_uom_code;
611
612 --
613 -- if (FREIGHT_TERM) is in the grouping criteria then
614 -- copy it
615 -- Else
616 -- keep it null;
617 IF (l_group_by_flags.freight_terms ='Y') THEN
618 l_source_header_rec.freight_terms := p_source_line_tab(l_counter).freight_terms;
619 END IF;
620
621 --
622 -- if (FOB CODE) is in the grouping criteria then
623 -- copy it
624 -- Else
625 -- keep it null;
626 -- R12 - Sachin
627 IF (l_group_by_flags.fob ='Y') THEN
628 l_source_header_rec.fob_code := p_source_line_tab(l_counter).fob_code;
629 END IF;
630
631 IF (p_action = 'GET_GROUP') THEN
632 l_source_header_rec.enforce_lead_time := 'Y';
633 ELSE
634 l_source_header_rec.enforce_lead_time := 'N';
635 END IF;
636
637 l_override_ship_method := false;
638
639 IF (p_action = 'GET_GROUP') THEN
640
641 IF l_debug_on THEN
642 WSH_DEBUG_SV.logmsg(l_module_name,'for GET_GROUP action...',WSH_DEBUG_SV.C_PROC_LEVEL);
643 WSH_DEBUG_SV.logmsg(l_module_name,'source line ship_method_code='||p_source_line_tab(l_counter).ship_method_code,WSH_DEBUG_SV.C_PROC_LEVEL);
644 WSH_DEBUG_SV.logmsg(l_module_name,'source line override_ship_method='||p_source_line_tab(l_counter).override_ship_method,WSH_DEBUG_SV.C_PROC_LEVEL);
645 END IF;
646
647 OPEN c_get_generic_carrier_flag(p_source_line_tab(l_counter).ship_method_code);
648 FETCH c_get_generic_carrier_flag INTO l_generic_carrier;
649 CLOSE c_get_generic_carrier_flag;
650
651 IF l_debug_on THEN
652 WSH_DEBUG_SV.logmsg(l_module_name,'l_generic_carrier='||l_generic_carrier,WSH_DEBUG_SV.C_PROC_LEVEL);
653 END IF;
654
655 IF (l_generic_carrier is null OR l_generic_carrier <> 'Y')
656 AND p_source_line_tab(l_counter).override_ship_method = 'Y' THEN
657 l_override_ship_method := true;
658 END IF;
659
660 END IF; -- p_action = 'GET_GROUP'
661
662 IF l_override_ship_method THEN
663
664 IF l_debug_on THEN
665 WSH_DEBUG_SV.logmsg(l_module_name, 'set group ship method null');
666 END IF;
667 l_source_header_rec.ship_method_code := null;
668 l_source_header_rec.carrier_id := null;
669 l_source_header_rec.service_level := null;
670 l_source_header_rec.mode_of_transport := null;
671
672 ELSE
673
674 IF l_debug_on THEN
675 WSH_DEBUG_SV.logmsg(l_module_name, 'set group ship method as source line ship method');
676 END IF;
677 l_source_header_rec.ship_method_code := p_source_line_tab(l_counter).ship_method_code;
678 l_source_header_rec.carrier_id := p_source_line_tab(l_counter).carrier_id;
679 l_source_header_rec.service_level := p_source_line_tab(l_counter).service_level;
680 l_source_header_rec.mode_of_transport := p_source_line_tab(l_counter).mode_of_transport;
681
682 END IF;
683
684 l_con_hash_rec.con_id := l_con_id;
685 l_con_hash_rec.hash_string := l_hash_string;
686 g_source_header_hash_tab(l_hash_value) := l_con_hash_rec;
687 p_source_header_tab(l_con_id) := l_source_header_rec;
688
689 l_con_id_seq := l_con_id_seq + 1; -- increment for next consolidation
690
691 END IF;
692
693 END IF; -- ending if successful after getting loc id from org id
694
695 END IF; -- ending if successful after getting default uoms
696
697 -- copy calculated attributes to source line rec e.g. weight/volume/consolidation id
698 p_source_line_tab(l_counter).weight := l_converted_weight;
699 p_source_line_tab(l_counter).weight_uom_code := l_weight_uom_code;
700 p_source_line_tab(l_counter).volume := l_converted_volume;
701 p_source_line_tab(l_counter).volume_uom_code := l_volume_uom_code;
702 p_source_line_tab(l_counter).freight_rate := null;
703 p_source_line_tab(l_counter).freight_rate_currency := null;
704 p_source_line_tab(l_counter).status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
705 p_source_line_tab(l_counter).message_data := null;
706 p_source_line_tab(l_counter).consolidation_id := l_con_id;
707
708 END LOOP;
709
710 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
711
712 --
713 -- Debug Statements
714 --
715 IF l_debug_on THEN
716 WSH_DEBUG_SV.pop(l_module_name);
717 END IF;
718 --
719 EXCEPTION
720 WHEN OTHERS THEN
721 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
722 x_msg_data := SQLERRM;
723 --
724 -- Debug Statements
725 --
726 IF l_debug_on THEN
727 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
728 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
729 END IF;
730 --
731 RAISE;
732
733
734 --
735 -- Debug Statements
736 --
737 IF l_debug_on THEN
738 WSH_DEBUG_SV.pop(l_module_name);
739 END IF;
740 --
741 END Consolidate_Lines;
742
743 END FTE_SOURCE_LINE_CONSOLIDATION;