[Home] [Help]
PACKAGE BODY: APPS.INL_LANDEDCOST_PVT
Source
1 PACKAGE BODY INL_LANDEDCOST_PVT AS
2 /* $Header: INLVLCOB.pls 120.36.12020000.5 2012/08/03 14:30:21 aicosta ship $ */
3
4 L_FND_USER_ID CONSTANT NUMBER := fnd_global.user_id; --Bug#9660084
5 L_FND_LOGIN_ID CONSTANT NUMBER := fnd_global.login_id; --Bug#9660084
6
7 L_FND_EXC_ERROR EXCEPTION; --Bug#9660084
8 L_FND_EXC_UNEXPECTED_ERROR EXCEPTION; --Bug#9660084
9
10 L_FND_RET_STS_SUCCESS CONSTANT VARCHAR2(1) := fnd_api.g_ret_sts_success; --Bug#9660084
11 L_FND_RET_STS_ERROR CONSTANT VARCHAR2(1) := fnd_api.g_ret_sts_error; --Bug#9660084
12 L_FND_RET_STS_UNEXP_ERROR CONSTANT VARCHAR2(1) := fnd_api.g_ret_sts_unexp_error;--Bug#9660084
13
14 -- Utility name : InLoop_Association
15 -- Type : Private
16 -- Function : If there are Associations in loop, this function will return TRUE,
17 -- otherwise, will return FALSE.
18 --
19 -- Pre-reqs : None
20 -- Parameters :
21 -- IN : p_ship_header_id IN NUMBER
22 -- p_from_parent_table_name IN VARCHAR2
23 -- p_from_parent_table_id IN NUMBER
24 -- OUT : x_return_status OUT NOCOPY VARCHAR2
25 --
26 -- Version : Current version 1.0
27 --
28 -- Notes :
29
30 FUNCTION InLoop_Association (
31 p_ship_header_id IN NUMBER,
32 p_from_parent_table_name IN VARCHAR2,
33 p_from_parent_table_id IN NUMBER,
34 x_return_status OUT NOCOPY VARCHAR2
35 ) RETURN BOOLEAN IS
36 l_count NUMBER;
37 l_program_name CONSTANT VARCHAR2(30) := 'InLoop_Association';
38 l_debug_info VARCHAR2(240);
39 l_in_loop BOOLEAN;
40
41 BEGIN
42
43 INL_LOGGING_PVT.Log_BeginProc (
44 p_module_name => g_module_name,
45 p_procedure_name => l_program_name);
46
47 --
48 -- Initialize return status to SUCCESS
49 --
50 x_return_status := L_FND_RET_STS_SUCCESS;
51
52 l_debug_info := 'p_ship_header_id';
53 INL_LOGGING_PVT.Log_Variable (
54 p_module_name => g_module_name,
55 p_procedure_name => l_program_name,
56 p_var_name => l_debug_info,
57 p_var_value => TO_CHAR(p_ship_header_id));
58
59 l_debug_info := 'p_from_parent_table_name';
60 INL_LOGGING_PVT.Log_Variable (
61 p_module_name => g_module_name,
62 p_procedure_name => l_program_name,
63 p_var_name => l_debug_info,
64 p_var_value => p_from_parent_table_name);
65
66 l_debug_info := 'p_from_parent_table_id';
67 INL_LOGGING_PVT.Log_Variable (
68 p_module_name => g_module_name,
69 p_procedure_name => l_program_name,
70 p_var_name => l_debug_info,
71 p_var_value => TO_CHAR(p_from_parent_table_id));
72
73 DECLARE
74 LOOP_ERROR EXCEPTION;
75 PRAGMA EXCEPTION_INIT(LOOP_ERROR, -1436);
76 BEGIN
77 SELECT count(*)
78 INTO l_count
79 FROM inl_associations
80 WHERE ship_header_id = p_ship_header_id
81 START WITH FROM_PARENT_TABLE_NAME = P_from_parent_table_name
82 AND FROM_PARENT_TABLE_ID = P_from_parent_table_id
83 CONNECT BY PRIOR TO_PARENT_TABLE_NAME = FROM_PARENT_TABLE_NAME
84 AND PRIOR TO_PARENT_TABLE_ID = FROM_PARENT_TABLE_ID;
85 INL_LOGGING_PVT.Log_Statement (
86 p_module_name => g_module_name,
87 p_procedure_name => l_program_name,
88 p_debug_info => 'IN LOOP association does not exist'
89 );
90 l_in_loop := FALSE;
91 EXCEPTION
92 WHEN LOOP_ERROR THEN
93 INL_LOGGING_PVT.Log_Statement (
94 p_module_name => g_module_name,
95 p_procedure_name => l_program_name,
96 p_debug_info => 'IN LOOP association exists'
97 );
98 l_in_loop := TRUE;
99 END;
100
101 INL_LOGGING_PVT.Log_EndProc(
102 p_module_name => g_module_name,
103 p_procedure_name => l_program_name);
104
105 IF l_in_loop THEN
106 RETURN TRUE;
107 ELSE
108 RETURN FALSE;
109 END IF;
110
111 EXCEPTION
112 WHEN L_FND_EXC_ERROR THEN
113 x_return_status := L_FND_RET_STS_ERROR;
114 WHEN L_FND_EXC_UNEXPECTED_ERROR THEN
115 x_return_status := L_FND_RET_STS_UNEXP_ERROR;
116 WHEN OTHERS THEN
117 x_return_status := L_FND_RET_STS_UNEXP_ERROR;
118 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
119 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,l_program_name);
120 END IF;
121
122 END InLoop_Association;
123
124 -- Utility name : Converted_Price
125 -- Type : Private
126 -- Function : Converts a given Unit Price based on the Unit of Measure
127 --
128 --
129 -- Pre-reqs : None
130 -- Parameters :
131 -- IN : p_unit_price IN NUMBER
132 -- p_organization_id IN NUMBER
133 -- p_inventory_item_id IN NUMBER
134 -- p_from_uom_code IN VARCHAR2
135 -- p_to_uom_code IN VARCHAR2
136 --
137 -- Version : Current version 1.0
138 --
139 -- Notes :
140
141 FUNCTION Converted_Price(
142 p_unit_price IN NUMBER,
143 p_organization_id IN NUMBER,
144 p_inventory_item_id IN NUMBER,
145 p_from_uom_code IN VARCHAR2,
146 p_to_uom_code IN VARCHAR2
147 ) RETURN NUMBER IS
148
149 l_program_name CONSTANT VARCHAR2(30) := 'Converted_Price';
150 l_debug_info VARCHAR2(240);
151 l_msg_data VARCHAR2(2000);
152 l_from_uom_class VARCHAR2(10);
153 l_to_uom_class VARCHAR2(10);
154 l_converted_price NUMBER;
155 l_primary_uom_code VARCHAR2(3);
156 l_primary_uom_class VARCHAR2(10);
157 l_concatenated_segments VARCHAR2(40);
158
159 BEGIN
160 INL_LOGGING_PVT.Log_BeginProc (
161 p_module_name => g_module_name,
162 p_procedure_name => l_program_name);
163
164 l_debug_info := 'p_unit_price';
165 INL_LOGGING_PVT.Log_Variable (
166 p_module_name => g_module_name,
167 p_procedure_name => l_program_name,
168 p_var_name => l_debug_info,
169 p_var_value => TO_CHAR(p_unit_price));
170
171 l_debug_info := 'p_organization_id';
172 INL_LOGGING_PVT.Log_Variable (
173 p_module_name => g_module_name,
174 p_procedure_name => l_program_name,
175 p_var_name => l_debug_info,
176 p_var_value => TO_CHAR(p_organization_id));
177
178 l_debug_info := 'p_inventory_item_id';
179 INL_LOGGING_PVT.Log_Variable (
180 p_module_name => g_module_name,
181 p_procedure_name => l_program_name,
182 p_var_name => l_debug_info,
183 p_var_value => TO_CHAR(p_inventory_item_id));
184
185 l_debug_info := 'p_from_uom_code';
186 INL_LOGGING_PVT.Log_Variable (
187 p_module_name => g_module_name,
188 p_procedure_name => l_program_name,
189 p_var_name => l_debug_info,
190 p_var_value => p_from_uom_code);
191
192 l_debug_info := 'p_to_uom_code';
193 INL_LOGGING_PVT.Log_Variable (
194 p_module_name => g_module_name,
195 p_procedure_name => l_program_name,
196 p_var_name => l_debug_info,
197 p_var_value => p_to_uom_code);
198 SELECT msi.primary_uom_code,
199 uom.uom_class,
200 msi.concatenated_segments
201 INTO l_primary_uom_code,
202 l_primary_uom_class,
203 l_concatenated_segments
204 FROM mtl_units_of_measure uom,
205 mtl_system_items_vl msi
206 WHERE uom.uom_code = msi.primary_uom_code
207 AND msi.organization_id = p_organization_id
208 AND msi.inventory_item_id = p_inventory_item_id;
209
210 l_debug_info := 'l_primary_uom_code';
211 INL_LOGGING_PVT.Log_Variable (
212 p_module_name => g_module_name,
213 p_procedure_name => l_program_name,
214 p_var_name => l_debug_info,
215 p_var_value => l_primary_uom_code);
216 l_debug_info := 'l_primary_uom_class';
217 INL_LOGGING_PVT.Log_Variable (
218 p_module_name => g_module_name,
219 p_procedure_name => l_program_name,
220 p_var_name => l_debug_info,
221 p_var_value => l_primary_uom_class);
222 l_debug_info := 'l_concatenated_segments';
223 INL_LOGGING_PVT.Log_Variable (
224 p_module_name => g_module_name,
225 p_procedure_name => l_program_name,
226 p_var_name => l_debug_info,
227 p_var_value => l_concatenated_segments);
228
229 SELECT uom_class
230 INTO l_from_uom_class
231 FROM mtl_units_of_measure
232 WHERE uom_code = p_from_uom_code;
233
234 l_debug_info := 'l_from_uom_class';
235 INL_LOGGING_PVT.Log_Variable (
236 p_module_name => g_module_name,
237 p_procedure_name => l_program_name,
238 p_var_name => l_debug_info,
239 p_var_value => l_from_uom_class);
240 SELECT uom_class
241 INTO l_to_uom_class
242 FROM mtl_units_of_measure
243 WHERE uom_code = p_to_uom_code;
244
245 l_debug_info := 'l_to_uom_class';
246 INL_LOGGING_PVT.Log_Variable (
247 p_module_name => g_module_name,
248 p_procedure_name => l_program_name,
249 p_var_name => l_debug_info,
250 p_var_value => l_to_uom_class);
251
252 -- When from uom code is different from the primary uom
253 -- first get it converted to the primary
254 IF p_from_uom_code <> l_primary_uom_code THEN
255 SELECT 1/NVL(conversion_rate,0) * NVL(p_unit_price,0)
256 INTO l_converted_price
257 FROM mtl_uom_conversions_view
258 WHERE primary_uom_class = l_primary_uom_class
259 AND primary_uom_code = l_primary_uom_code
260 AND uom_class = l_from_uom_class
261 AND uom_code = p_from_uom_code
262 AND organization_id = p_organization_id
263 AND inventory_item_id = p_inventory_item_id;
264
265 SELECT NVL(conversion_rate,0) * NVL(l_converted_price,0)
266 INTO l_converted_price
267 FROM mtl_uom_conversions_view
268 WHERE primary_uom_class = l_primary_uom_class
269 AND primary_uom_code = l_primary_uom_code
270 AND uom_class = l_to_uom_class
271 AND uom_code = p_to_uom_code
272 AND organization_id = p_organization_id
273 AND inventory_item_id = p_inventory_item_id;
274
275 l_debug_info := 'l_converted_price';
276 INL_LOGGING_PVT.Log_Variable (p_module_name => g_module_name,
277 p_procedure_name=> l_program_name,
278 p_var_name => l_debug_info,
279 p_var_value => l_converted_price);
280 ELSE
281 SELECT (SELECT (conversion_rate * p_unit_price) as conversion_rate
282 FROM mtl_uom_conversions_view
283 WHERE primary_uom_class = l_from_uom_class
284 AND primary_uom_code = p_from_uom_code
285 AND uom_class = l_to_uom_class
286 AND uom_code = p_to_uom_code
287 AND organization_id = p_organization_id
288 AND inventory_item_id = p_inventory_item_id
289 UNION
290 SELECT (1/conversion_rate * p_unit_price) as conversion_rate
291 FROM mtl_uom_conversions_view
292 WHERE primary_uom_class = l_to_uom_class
293 AND primary_uom_code = p_to_uom_code
294 AND uom_class = l_from_uom_class
295 AND uom_code = p_from_uom_code
296 AND organization_id = p_organization_id
297 AND inventory_item_id = p_inventory_item_id)
298 INTO l_converted_price
299 FROM dual;
300
301 l_debug_info := 'l_converted_price';
302 INL_LOGGING_PVT.Log_Variable (p_module_name => g_module_name,
303 p_procedure_name=> l_program_name,
304 p_var_name => l_debug_info,
305 p_var_value => l_converted_price);
306 END IF;
307 RETURN l_converted_price;
308 EXCEPTION
309 WHEN OTHERS THEN
310 INL_LOGGING_PVT.Log_UnexpecError (
311 p_module_name => g_module_name,
312 p_procedure_name => l_program_name);
313
314 FND_MESSAGE.SET_NAME('INL','INL_ERR_QTY_CONV');
315 FND_MESSAGE.SET_TOKEN('CONCATENATED_SEGMENTS',l_concatenated_segments);
316 BEGIN
317 SELECT unit_of_measure_tl
318 INTO l_debug_info
319 FROM mtl_units_of_measure
320 WHERE uom_code = p_from_uom_code;
321 l_debug_info := p_from_uom_code||'-'||l_debug_info;
322 EXCEPTION
323 WHEN OTHERS THEN
324 l_debug_info := p_from_uom_code;
325 END;
326 FND_MESSAGE.SET_TOKEN('FROM_UOM_CODE',l_debug_info);
327 BEGIN
328 SELECT unit_of_measure_tl
329 INTO l_debug_info
330 FROM mtl_units_of_measure
331 WHERE uom_code = p_to_uom_code;
332 l_debug_info := p_to_uom_code||'-'||l_debug_info;
333 EXCEPTION
334 WHEN OTHERS THEN
335 l_debug_info := p_to_uom_code;
336 END;
337 FND_MESSAGE.SET_TOKEN('TO_UOM_CODE',l_debug_info);
338 l_msg_data := FND_MESSAGE.GET;
339 RAISE_APPLICATION_ERROR (-20001, l_msg_data);
340 END Converted_Price;
341
342 -- Utility name : Converted_Qty
343 -- Type : Private
344 -- Function : Converts a given quantity, which can be either a primary quantity, or a volume or a weight, into
345 -- a given Unit Of Measure.
346 --
347 --
348 -- Pre-reqs : None
349 -- Parameters :
350 -- IN : p_organization_id IN NUMBER
351 -- p_inventory_item_id IN NUMBER
352 -- p_qty IN NUMBER
353 -- p_from_uom_code IN VARCHAR2
354 -- P_to_uom_code IN VARCHAR2
355 --
356 -- Version : Current version 1.0
357 --
358 -- Notes :
359 FUNCTION Converted_Qty (
360 p_organization_id IN NUMBER,
361 p_inventory_item_id IN NUMBER,
362 p_qty IN NUMBER,
363 p_from_uom_code IN VARCHAR2,
364 p_to_uom_code IN VARCHAR2
365 ) RETURN NUMBER IS
366 l_program_name CONSTANT VARCHAR2(30) := 'Converted_Qty';
367 l_debug_info VARCHAR2(240);
368
369 l_conv_not_found_excep EXCEPTION;
370
371 l_primary_uom_code VARCHAR2(3);
372 l_primary_uom_class VARCHAR2(10);
373 l_allocation_uom_class VARCHAR2(10);
374 l_to_allocation_uom_class VARCHAR2(10);
375 l_primary_qty NUMBER;
376 l_converted_qty NUMBER;
377 l_concatenated_segments VARCHAR2(240);
378 l_msg_data VARCHAR2(2000);
379
380 BEGIN
381 INL_LOGGING_PVT.Log_BeginProc (
382 p_module_name => g_module_name,
383 p_procedure_name => l_program_name
384 );
385
386 l_debug_info := 'p_organization_id';
387 INL_LOGGING_PVT.Log_Variable (
388 p_module_name => g_module_name,
389 p_procedure_name => l_program_name,
390 p_var_name => l_debug_info,
391 p_var_value => TO_CHAR(p_organization_id)
392 );
393
394 l_debug_info := 'p_inventory_item_id';
395 INL_LOGGING_PVT.Log_Variable (
396 p_module_name => g_module_name,
397 p_procedure_name => l_program_name,
398 p_var_name => l_debug_info,
399 p_var_value => TO_CHAR(p_inventory_item_id)
400 );
401
402 l_debug_info := 'p_qty';
403 INL_LOGGING_PVT.Log_Variable (
404 p_module_name => g_module_name,
405 p_procedure_name => l_program_name,
406 p_var_name => l_debug_info,
407 p_var_value => TO_CHAR(p_qty)
408 );
409
410 l_debug_info := 'p_from_uom_code';
411 INL_LOGGING_PVT.Log_Variable (
412 p_module_name => g_module_name,
413 p_procedure_name => l_program_name,
414 p_var_name => l_debug_info,
415 p_var_value => p_from_uom_code
416 );
417
418 l_debug_info := 'p_to_uom_code';
419 INL_LOGGING_PVT.Log_Variable (
420 p_module_name => g_module_name,
421 p_procedure_name => l_program_name,
422 p_var_name => l_debug_info,
423 p_var_value => p_to_uom_code
424 );
425
426 SELECT
427 msi.primary_uom_code,
428 uom.uom_class,
429 msi.concatenated_segments
430 INTO
431 l_primary_uom_code,
432 l_primary_uom_class,
433 l_concatenated_segments
434 FROM mtl_units_of_measure uom,
435 mtl_system_items_vl msi
436 WHERE uom.uom_code = msi.primary_uom_code
437 AND msi.organization_id = p_organization_id
438 AND msi.inventory_item_id = p_inventory_item_id;
439
440 l_debug_info := 'l_primary_uom_code';
441 INL_LOGGING_PVT.Log_Variable (
442 p_module_name => g_module_name,
443 p_procedure_name => l_program_name,
444 p_var_name => l_debug_info,
445 p_var_value => l_primary_uom_code
446 );
447
448 l_debug_info := 'l_primary_uom_class';
449 INL_LOGGING_PVT.Log_Variable (
450 p_module_name => g_module_name,
451 p_procedure_name => l_program_name,
452 p_var_name => l_debug_info,
453 p_var_value => l_primary_uom_class
454 );
455
456 SELECT uom_class
457 INTO l_allocation_uom_class
458 FROM mtl_units_of_measure
459 WHERE uom_code = p_from_uom_code;
460
461 l_debug_info := 'l_allocation_uom_class';
462 INL_LOGGING_PVT.Log_Variable (
463 p_module_name => g_module_name,
464 p_procedure_name => l_program_name,
465 p_var_name => l_debug_info,
466 p_var_value => l_allocation_uom_class
467 );
468
469 BEGIN
470
471 SELECT NVL(conversion_rate,0) * NVL(p_qty,0)
472 INTO l_primary_qty
473 FROM mtl_uom_conversions_view
474 WHERE
475 primary_uom_class = l_primary_uom_class
476 AND primary_uom_code = l_primary_uom_code
477 AND uom_class = l_allocation_uom_class
478 AND uom_code = p_from_uom_code
479 AND inventory_item_id = p_inventory_item_id
480 AND organization_id = p_organization_id;
481
482 EXCEPTION
483 WHEN NO_DATA_FOUND THEN
484 l_debug_info := 'Conversion not found.';
485 INL_LOGGING_PVT.Log_Statement (
486 p_module_name => g_module_name,
487 p_procedure_name => l_program_name,
488 p_debug_info => l_debug_info
489 );
490 RAISE l_conv_not_found_excep;
491 END;
492 l_debug_info := 'l_primary_qty';
493 INL_LOGGING_PVT.Log_Variable (
494 p_module_name => g_module_name,
495 p_procedure_name => l_program_name,
496 p_var_name => l_debug_info,
497 p_var_value => TO_CHAR(l_primary_qty)
498 );
499
500 SELECT uom_class
501 INTO l_to_allocation_uom_class
502 FROM mtl_units_of_measure
503 WHERE uom_code = P_to_uom_code;
504
505 l_debug_info := 'l_to_allocation_uom_class';
506 INL_LOGGING_PVT.Log_Variable (
507 p_module_name => g_module_name,
508 p_procedure_name => l_program_name,
509 p_var_name => l_debug_info,
510 p_var_value => l_to_allocation_uom_class
511 );
512
513 BEGIN
514
515 SELECT 1/NVL(conversion_rate,0) * NVL(l_primary_qty,0)
516 INTO l_converted_qty
517 FROM mtl_uom_conversions_view
518 WHERE primary_uom_class = l_primary_uom_class
519 AND primary_uom_code = l_primary_uom_code
520 AND uom_class = l_to_allocation_uom_class
521 AND uom_code = p_to_uom_code
522 AND inventory_item_id = p_inventory_item_id
523 AND organization_id = p_organization_id;
524
525 EXCEPTION
526 WHEN NO_DATA_FOUND THEN
527 l_debug_info := 'Conversion not found.';
528 INL_LOGGING_PVT.Log_Statement (
529 p_module_name => g_module_name,
530 p_procedure_name => l_program_name,
531 p_debug_info => l_debug_info
532 );
533 RAISE l_conv_not_found_excep;
534 END;
535
536 l_debug_info := 'l_converted_qty';
537 INL_LOGGING_PVT.Log_Variable (
538 p_module_name => g_module_name,
539 p_procedure_name => l_program_name,
540 p_var_name => l_debug_info,
541 p_var_value => TO_CHAR(l_converted_qty)
542 );
543
544 INL_LOGGING_PVT.Log_EndProc (
545 p_module_name => g_module_name,
546 p_procedure_name => l_program_name
547 );
548
549 RETURN l_converted_qty;
550 EXCEPTION
551 WHEN l_conv_not_found_excep THEN
552 l_debug_info := 'l_conv_not_found_excep Exception.';
553 INL_LOGGING_PVT.Log_Statement (
554 p_module_name => g_module_name,
555 p_procedure_name => l_program_name,
556 p_debug_info => l_debug_info
557 );
558 INL_LOGGING_PVT.Log_UnexpecError (
559 p_module_name => g_module_name,
560 p_procedure_name => l_program_name
561 );
562
563 FND_MESSAGE.SET_NAME('INL','INL_ERR_UOM_NFOUND'); --Bug#9011206
564 FND_MESSAGE.SET_TOKEN('CONCATENATED_SEGMENTS',l_concatenated_segments);
565
566 BEGIN
567 SELECT unit_of_measure_tl
568 INTO l_debug_info
569 FROM mtl_units_of_measure
570 WHERE uom_code = p_from_uom_code;
571 l_debug_info := p_from_uom_code||'-'||l_debug_info;
572 EXCEPTION
573 WHEN OTHERS THEN
574 l_debug_info := p_from_uom_code;
575 END;
576 FND_MESSAGE.SET_TOKEN('FROM_UOM_CODE',l_debug_info);
577 BEGIN
578 SELECT unit_of_measure_tl
579 INTO l_debug_info
580 FROM mtl_units_of_measure
581 WHERE uom_code = p_to_uom_code;
582 l_debug_info := p_to_uom_code||'-'||l_debug_info;
583 EXCEPTION
584 WHEN OTHERS THEN
585 l_debug_info := p_to_uom_code;
586 END;
587 FND_MESSAGE.SET_TOKEN('TO_UOM_CODE',l_debug_info);
588
589 l_msg_data := FND_MESSAGE.GET;
590 RAISE_APPLICATION_ERROR (-20001, l_msg_data);
591 WHEN OTHERS THEN
592 INL_LOGGING_PVT.Log_UnexpecError (
593 p_module_name => g_module_name,
594 p_procedure_name => l_program_name
595 );
596
597 FND_MESSAGE.SET_NAME('INL','INL_ERR_QTY_CONV');
598 FND_MESSAGE.SET_TOKEN('CONCATENATED_SEGMENTS',l_concatenated_segments);
599
600 BEGIN
601 SELECT unit_of_measure_tl
602 INTO l_debug_info
603 FROM mtl_units_of_measure
604 WHERE uom_code = p_from_uom_code;
605 l_debug_info := p_from_uom_code||'-'||l_debug_info;
606 EXCEPTION
607 WHEN OTHERS THEN
608 l_debug_info := p_from_uom_code;
609 END;
610 FND_MESSAGE.SET_TOKEN('FROM_UOM_CODE',l_debug_info);
611 BEGIN
612 SELECT unit_of_measure_tl
613 INTO l_debug_info
614 FROM mtl_units_of_measure
615 WHERE uom_code = p_to_uom_code;
616 l_debug_info := p_to_uom_code||'-'||l_debug_info;
617 EXCEPTION
618 WHEN OTHERS THEN
619 l_debug_info := p_to_uom_code;
620 END;
621 FND_MESSAGE.SET_TOKEN('TO_UOM_CODE',l_debug_info);
622
623 l_msg_data := FND_MESSAGE.GET;
624 RAISE_APPLICATION_ERROR (-20001, l_msg_data);
625
626 END Converted_Qty;
627
628 -- Utility name : Converted_Amt
629 -- Type : Private
630 -- Function : Converts a given amount from one currency to another
631 --
632 -- Pre-reqs : None
633 -- Parameters :
634 -- IN : p_amt IN NUMBER
635 -- p_from_currency_code IN VARCHAR2
636 -- p_to_currency_code IN VARCHAR2
637 -- p_currency_conversion_type IN VARCHAR2
638 -- p_currency_conversion_date IN DATE
639 --
640 -- Version : Current version 1.0
641 --
642 -- Notes :
643 FUNCTION Converted_Amt (
644 p_amt IN NUMBER,
645 p_from_currency_code IN VARCHAR2,
646 p_to_currency_code IN VARCHAR2,
647 p_currency_conversion_type IN VARCHAR2,
648 p_currency_conversion_date IN DATE
649 ) RETURN NUMBER IS
650 l_program_name CONSTANT VARCHAR2(30) := 'Converted_Amt';
651 l_debug_info VARCHAR2(240);
652 l_msg_data VARCHAR2(2000);
653 l_converted_amt NUMBER;
654 l_conversion_rate NUMBER;
655 BEGIN
656
657 l_Converted_amt := p_amt;
658 INL_LOGGING_PVT.Log_BeginProc (
659 p_module_name => g_module_name,
660 p_procedure_name => l_program_name);
661
662 l_converted_amt := Converted_Amt(
663 p_amt => p_amt,
664 p_from_currency_code => p_from_currency_code,
665 p_to_currency_code => p_to_currency_code,
666 p_currency_conversion_type => p_currency_conversion_type,
667 p_currency_conversion_date => p_currency_conversion_date,
668 x_currency_conversion_rate => l_conversion_rate);
669
670 RETURN l_Converted_Amt;
671
672 EXCEPTION
673 WHEN OTHERS THEN
674 INL_LOGGING_PVT.Log_UnexpecError (p_module_name => g_module_name,
675 p_procedure_name => l_program_name);
676 FND_MESSAGE.SET_NAME('INL','INL_ERR_AMT_CONV');
677 FND_MESSAGE.SET_TOKEN('FROM_CURRENCY_CODE',p_from_currency_code);
678 FND_MESSAGE.SET_TOKEN('TO_CURRENCY_CODE',p_to_currency_code);
679 FND_MESSAGE.SET_TOKEN('CURRENCY_CONVERSION_TYPE',p_currency_conversion_type);
680 FND_MESSAGE.SET_TOKEN('CURRENCY_CONVERSTION_DATE',p_currency_conversion_date);
681 l_msg_data := FND_MESSAGE.GET;
682 RAISE_APPLICATION_ERROR (-20002, l_msg_data);
683 END Converted_Amt;
684
685 -- Utility name : Converted_Amt
686 -- Type : Private
687 -- Function : Converts a given amount from one currency to another
688 --
689 -- Pre-reqs : None
690 -- Parameters :
691 -- IN : p_amt IN NUMBER
692 -- p_from_currency_code IN VARCHAR2
693 -- p_to_currency_code IN VARCHAR2
694 -- p_currency_conversion_type IN VARCHAR2
695 -- p_currency_conversion_date IN DATE
696 -- x_currency_conversion_rate OUT NOCOPY NUMBER
697 --
698 -- Version : Current version 1.0
699 --
700 -- Notes :
701 FUNCTION Converted_Amt (
702 p_amt IN NUMBER,
703 p_from_currency_code IN VARCHAR2,
704 p_to_currency_code IN VARCHAR2,
705 p_currency_conversion_type IN VARCHAR2,
706 p_currency_conversion_date IN DATE,
707 x_currency_conversion_rate OUT NOCOPY NUMBER
708 ) RETURN NUMBER IS
709 l_program_name CONSTANT VARCHAR2(30) := 'Converted_Amt2';
710 l_debug_info VARCHAR2(240);
711 l_msg_data VARCHAR2(2000);
712 l_converted_amt NUMBER;
713 l_conversion_rate NUMBER;
714
715 BEGIN
716 INL_LOGGING_PVT.Log_BeginProc (
717 p_module_name => g_module_name,
718 p_procedure_name => l_program_name);
719
720 l_Converted_Amt := p_amt;
721 IF p_from_currency_code <> p_to_currency_code THEN
722 BEGIN
723 SELECT NVL(p_amt,0) * NVL(conversion_rate,0),
724 conversion_rate
725 INTO l_converted_amt,
726 x_currency_conversion_rate
727 FROM gl_daily_rates
728 WHERE from_currency = p_from_currency_code
729 AND to_currency = p_to_currency_code
730 AND conversion_type = p_currency_conversion_type
731 AND TRUNC(conversion_date) = TRUNC(p_currency_conversion_date);
732 EXCEPTION
733 -- Bug #7835356
734 WHEN NO_DATA_FOUND THEN x_currency_conversion_rate := NULL;
735 END;
736 END IF;
737
738 INL_LOGGING_PVT.Log_EndProc (
739 p_module_name => g_module_name,
740 p_procedure_name => l_program_name);
741
742 RETURN l_Converted_Amt;
743 EXCEPTION
744 WHEN OTHERS THEN
745 INL_LOGGING_PVT.Log_UnexpecError (
746 p_module_name => g_module_name,
747 p_procedure_name => l_program_name);
748 FND_MESSAGE.SET_NAME('INL','INL_ERR_AMT_CONV');
749 FND_MESSAGE.SET_TOKEN('FROM_CURRENCY_CODE',p_from_currency_code);
750 FND_MESSAGE.SET_TOKEN('TO_CURRENCY_CODE',p_to_currency_code);
751 FND_MESSAGE.SET_TOKEN('CURRENCY_CONVERSION_TYPE',p_currency_conversion_type);
752 FND_MESSAGE.SET_TOKEN('CURRENCY_CONVERSTION_DATE',p_currency_conversion_date);
753 l_msg_data := FND_MESSAGE.GET;
754 RAISE_APPLICATION_ERROR (-20002, l_msg_data);
755 END Converted_Amt;
756
757 -- Utility name : Get_TotalAmount
758 -- Type : Private
759 -- Function :
760 -- Pre-reqs : None
761 -- Parameters :
762 -- IN : p_ship_header_id IN NUMBER
763 -- p_adjustment_num IN NUMBER
764 -- p_le_currency_code IN VARCHAR2
765 -- p_from_component_name IN VARCHAR2
766 -- p_from_component_id IN NUMBER
767 -- p_to_component_name IN VARCHAR2
768 -- p_to_component_id IN NUMBER
769 -- p_allocation_basis IN VARCHAR2
770 -- p_allocation_uom_code IN VARCHAR2
771 -- OUT : x_total_amt OUT NOCOPY NUMBER
772 -- x_do_proportion OUT NOCOPY VARCHAR2
773 -- x_return_status OUT NOCOPY VARCHAR2
774 --
775 -- Version : Current version 1.0
776 --
777 -- Notes :
778 PROCEDURE Get_TotalAmt (
779 p_ship_header_id IN NUMBER,
780 p_adjustment_num IN NUMBER,
781 p_le_currency_code IN VARCHAR2,
782 p_from_component_name IN VARCHAR2,
783 p_from_component_id IN NUMBER,
784 p_to_component_name IN VARCHAR2,
785 p_to_component_id IN NUMBER,
786 p_allocation_basis IN VARCHAR2,
787 p_allocation_uom_code IN VARCHAR2,
788 x_total_amt OUT NOCOPY NUMBER,
789 x_do_proportion OUT NOCOPY VARCHAR2,
790 x_return_status OUT NOCOPY VARCHAR2)
791 IS
792 l_total_amt NUMBER;
793 l_to_component_amt NUMBER;
794 l_debug_info VARCHAR2(240);
795 l_program_name CONSTANT VARCHAR2(30) := 'Get_TotalAmt';
796 l_inv_org_id NUMBER;
797 l_return_status VARCHAR2(1);
798 l_count NUMBER;
799 l_count_aux NUMBER;
800
801 CURSOR assoc IS
802 SELECT a.association_id,
803 a.to_parent_table_name,
804 DECODE(a.to_parent_table_name,
805 'INL_SHIP_LINES',
806 (SELECT MAX(sl.ship_line_id)
807 FROM inl_ship_lines_all sl,
808 inl_ship_lines_all sl0
809 WHERE sl0.ship_line_id = a.to_parent_table_id
810 AND sl.ship_header_id = sl0.ship_header_id
811 AND sl.ship_line_group_id = sl0.ship_line_group_id
812 AND sl.ship_line_num = sl0.ship_line_num
813 --- SCM-051
814 --- AND (sl.adjustment_num <= p_adjustment_num
815 AND (ABS(sl.adjustment_num) <= ABS(p_adjustment_num)
816 --- SCM-051
817 OR sl.ship_header_id <> p_ship_header_id) --Bug#10221931*
818 ),
819 'INL_CHARGE_LINES',
820 (SELECT MAX(cl.charge_line_id)
821 FROM inl_charge_lines cl
822 --- SCM-051
823 --- WHERE cl.adjustment_num <= p_adjustment_num
824 WHERE ABS(cl.adjustment_num) <= ABS(p_adjustment_num)
825 --- SCM-051
826 START WITH cl.charge_line_id = a.to_parent_table_id
827 CONNECT BY PRIOR cl.charge_line_id = cl.parent_charge_line_id
828 ),
829 a.to_parent_table_id) to_parent_table_id
830 FROM inl_associations a
831 WHERE a.from_parent_table_name = p_from_component_name
832 AND a.from_parent_table_id
833 = DECODE(a.from_parent_table_name,
834 'INL_CHARGE_LINES',
835 (SELECT MIN(cl.charge_line_id)
836 FROM inl_charge_lines cl
837 --- SCM-051
838 --- WHERE cl.adjustment_num <= p_adjustment_num
839 WHERE ABS(cl.adjustment_num) <= ABS(p_adjustment_num)
840 --- SCM-051
841 START WITH cl.charge_line_id = p_from_component_id
842 CONNECT BY PRIOR cl.parent_charge_line_id = cl.charge_line_id),
843 'INL_TAX_LINES',
844 (SELECT MIN(tl.tax_line_id)
845 FROM inl_tax_lines tl
846 --- SCM-051
847 --- WHERE tl.adjustment_num <= p_adjustment_num
848 WHERE ABS(tl.adjustment_num) <= ABS(p_adjustment_num)
849 --- SCM-051
850 START WITH tl.tax_line_id = p_from_component_id
851 CONNECT BY PRIOR tl.parent_tax_line_id = tl.tax_line_id),
852 'INL_SHIP_LINES',
853 (SELECT MIN(sl.ship_line_id)
854 FROM inl_ship_lines sl
855 --- SCM-051
856 --- WHERE (sl.adjustment_num <= p_adjustment_num
857 WHERE (ABS(sl.adjustment_num) <= ABS(p_adjustment_num)
858 --- SCM-051
859 OR sl.ship_header_id <> p_ship_header_id) --Bug#10221931*
860 START WITH sl.ship_line_id = p_from_component_id
861 CONNECT BY PRIOR sl.parent_ship_line_id = sl.ship_line_id))
862 ORDER BY a.association_id;
863
864 TYPE assoc_ListType IS TABLE OF assoc%ROWTYPE; --Bulk implem
865 assoc_List assoc_ListType; --Bulk implem
866
867 --
868 -- Obtains total amount to be passed to the Manage_Proportion routine
869 --
870 BEGIN
871 INL_LOGGING_PVT.Log_BeginProc (
872 p_module_name => g_module_name,
873 p_procedure_name => l_program_name);
874 --
875 -- Initialize return status to SUCCESS
876 --
877 x_return_status := L_FND_RET_STS_SUCCESS;
878 --
879 -- Initialize other output parameters
880 --
881 x_do_proportion := 'N';
882 --
883 -- Initialize other variables
884 --
885 l_total_amt := 0;
886 l_count := 0;
887
888 OPEN assoc;
889 FETCH assoc BULK COLLECT INTO assoc_List; --Bulk implem
890 CLOSE assoc;
891
892 l_debug_info := 'Fetched '||NVL(assoc_List.COUNT, 0)||' association(s).';
893 INL_LOGGING_PVT.Log_Statement(
894 p_module_name => g_module_name,
895 p_procedure_name => l_program_name,
896 p_debug_info => l_debug_info
897 ) ;
898
899 l_count_aux := NVL(assoc_List.COUNT, 0);
900
901 IF l_count_aux = 1 THEN
902 IF assoc_List(1).to_parent_table_name = 'INL_SHIP_HEADERS' THEN
903 SELECT
904 COUNT(*)
905 INTO l_count_aux
906 FROM inl_ship_lines_all ol
907 WHERE ol.ship_header_id = assoc_List(1).to_parent_table_id
908 AND ol.ship_line_id = (SELECT MAX(sl.ship_line_id)
909 FROM inl_ship_lines_all sl
910 WHERE sl.ship_header_id = ol.ship_header_id --Bug#9660084
911 AND sl.ship_line_group_id = ol.ship_line_group_id --Bug#9660084
912 AND sl.ship_line_num = ol.ship_line_num --Bug#9660084
913 --- SCM-051
914 --- AND (sl.adjustment_num <= p_adjustment_num
915 AND (ABS(sl.adjustment_num) <= ABS(p_adjustment_num)
916 --- SCM-051
917 OR sl.ship_header_id <> p_ship_header_id) --Bug#10221931*
918 );
919 INL_LOGGING_PVT.Log_Variable (
920 p_module_name => g_module_name,
921 p_procedure_name => l_program_name,
922 p_var_name => 'A-l_count_aux',
923 p_var_value => l_count_aux);
924
925
926 ELSIF assoc_List(1).to_parent_table_name = 'INL_SHIP_LINE_GROUPS' THEN
927 SELECT
928 COUNT(*)
929 INTO l_count_aux
930 FROM inl_ship_lines_all ol
931 WHERE ol.ship_line_group_id = assoc_List(1).to_parent_table_id
932 AND ol.ship_line_id = (SELECT MAX(sl.ship_line_id)
933 FROM inl_ship_lines_all sl
934 WHERE sl.ship_header_id = ol.ship_header_id --Bug#9660084
935 AND sl.ship_line_group_id = ol.ship_line_group_id --Bug#9660084
936 AND sl.ship_line_num = ol.ship_line_num --Bug#9660084
937 --- SCM-051
938 --- AND (sl.adjustment_num <= p_adjustment_num
939 AND (ABS(sl.adjustment_num) <= ABS(p_adjustment_num)
940 --- SCM-051
941 OR sl.ship_header_id <> p_ship_header_id) --Bug#10221931*
942 );
943 INL_LOGGING_PVT.Log_Variable (
944 p_module_name => g_module_name,
945 p_procedure_name => l_program_name,
946 p_var_name => 'B-l_count_aux',
947 p_var_value => l_count_aux);
948 END IF;
949 ELSIF l_count_aux = 0 THEN
950
951 SELECT
952 COUNT(*)
953 INTO l_count_aux
954 FROM inl_ship_lines_all ol
955 WHERE ol.ship_header_id = DECODE(p_from_component_name,'INL_SHIP_HEADERS',p_from_component_id,ol.ship_header_id)
956 AND ol.ship_line_group_id = DECODE(p_from_component_name,'INL_SHIP_LINE_GROUPS',p_from_component_id,ol.ship_line_group_id)
957 AND ol.ship_line_id = DECODE(p_from_component_name,'INL_SHIP_LINES',p_from_component_id,ol.ship_line_id)
958 AND ol.ship_header_id = p_ship_header_id
959 AND ol.ship_line_id = (SELECT MAX(sl.ship_line_id)
960 FROM inl_ship_lines_all sl
961 WHERE sl.ship_header_id = ol.ship_header_id --Bug#9660084
962 AND sl.ship_line_group_id = ol.ship_line_group_id --Bug#9660084
963 AND sl.ship_line_num = ol.ship_line_num --Bug#9660084
964 --- SCM-051
965 --- AND (sl.adjustment_num <= p_adjustment_num
966 AND (ABS(sl.adjustment_num) <= ABS(p_adjustment_num)
967 --- SCM-051
968 OR sl.ship_header_id <> p_ship_header_id) --Bug#10221931*
969 );
970 END IF;
971
972 INL_LOGGING_PVT.Log_Variable (
973 p_module_name => g_module_name,
974 p_procedure_name => l_program_name,
975 p_var_name => 'l_count_aux',
976 p_var_value => l_count_aux);
977
978 IF l_count_aux <> 1 THEN -- l_count_aux = 0 or l_count_aux>1
979 --
980 -- Get Inventory Organization. It will be used when getting Item info.
981 --
982 SELECT organization_id
983 INTO l_inv_org_id
984 FROM inl_ship_headers_all
985 WHERE ship_header_id = p_ship_header_id;
986
987 INL_LOGGING_PVT.Log_Variable (
988 p_module_name => g_module_name,
989 p_procedure_name => l_program_name,
990 p_var_name => 'l_inv_org_id',
991 p_var_value => l_inv_org_id);
992
993 INL_LOGGING_PVT.Log_Variable (
994 p_module_name => g_module_name,
995 p_procedure_name => l_program_name,
996 p_var_name => 'p_ship_header_id',
997 p_var_value => p_ship_header_id);
998
999 INL_LOGGING_PVT.Log_Variable (
1000 p_module_name => g_module_name,
1001 p_procedure_name => l_program_name,
1002 p_var_name => 'p_adjustment_num',
1003 p_var_value => p_adjustment_num);
1004
1005 INL_LOGGING_PVT.Log_Variable (
1006 p_module_name => g_module_name,
1007 p_procedure_name => l_program_name,
1008 p_var_name => 'p_from_component_name',
1009 p_var_value => p_from_component_name);
1010
1011 INL_LOGGING_PVT.Log_Variable (
1012 p_module_name => g_module_name,
1013 p_procedure_name => l_program_name,
1014 p_var_name => 'p_from_component_id',
1015 p_var_value => p_from_component_id);
1016
1017 INL_LOGGING_PVT.Log_Variable (
1018 p_module_name => g_module_name,
1019 p_procedure_name => l_program_name,
1020 p_var_name => 'p_to_component_name',
1021 p_var_value => p_to_component_name
1022 );
1023
1024 INL_LOGGING_PVT.Log_Variable (
1025 p_module_name => g_module_name,
1026 p_procedure_name => l_program_name,
1027 p_var_name => 'p_to_component_id',
1028 p_var_value => p_to_component_name
1029 );
1030
1031 INL_LOGGING_PVT.Log_Variable (
1032 p_module_name => g_module_name,
1033 p_procedure_name => l_program_name,
1034 p_var_name => 'p_allocation_basis',
1035 p_var_value => p_allocation_basis
1036 );
1037
1038 INL_LOGGING_PVT.Log_Variable (
1039 p_module_name => g_module_name,
1040 p_procedure_name => l_program_name,
1041 p_var_name => 'p_allocation_uom_code',
1042 p_var_value => p_allocation_uom_code
1043 );
1044
1045 END IF;
1046
1047 IF l_count_aux > 1 THEN
1048
1049 IF p_to_component_name <> 'INL_SHIP_DISTS' THEN
1050 --
1051 -- This is for getting the total amount of each component that an associated amount
1052 -- refers to.
1053 --
1054 l_debug_info := 'p_to_component_name <> INL_SHIP_DISTS';
1055 INL_LOGGING_PVT.Log_Statement (
1056 p_module_name => g_module_name,
1057 p_procedure_name => l_program_name,
1058 p_debug_info => l_debug_info);
1059
1060 FOR iAssoc IN 1 .. assoc_List.COUNT --Bulk implem
1061 LOOP
1062
1063 IF assoc_List(iAssoc).to_parent_table_name = 'INL_SHIP_HEADERS' THEN
1064 SELECT SUM(DECODE(p_allocation_basis,'VALUE',NVL(NVL(ol.primary_qty,0)*NVL(ol.primary_unit_price * NVL(ol.currency_conversion_rate,1),0),0),
1065 Converted_Qty (l_inv_org_id,
1066 ol.inventory_item_id,
1067 NVL(ol.primary_qty,0),
1068 ol.primary_uom_code,
1069 p_allocation_uom_code))) + l_total_amt,
1070 COUNT(*) + l_count
1071 INTO l_total_amt,
1072 l_count
1073 FROM inl_ship_lines_all ol
1074 WHERE ol.ship_header_id = assoc_List(iAssoc).to_parent_table_id
1075 AND ol.ship_line_id = (SELECT MAX(sl.ship_line_id)
1076 FROM inl_ship_lines_all sl
1077 WHERE sl.ship_header_id = ol.ship_header_id --Bug#9660084
1078 AND sl.ship_line_group_id = ol.ship_line_group_id --Bug#9660084
1079 AND sl.ship_line_num = ol.ship_line_num --Bug#9660084
1080 --- SCM-051
1081 --- AND (sl.adjustment_num <= p_adjustment_num
1082 AND (ABS(sl.adjustment_num) <= ABS(p_adjustment_num)
1083 --- SCM-051
1084 OR sl.ship_header_id <> p_ship_header_id) --Bug#10221931*
1085 );
1086 l_debug_info := 'A-l_total_amt';
1087 INL_LOGGING_PVT.Log_Variable (
1088 p_module_name => g_module_name,
1089 p_procedure_name => l_program_name,
1090 p_var_name => l_debug_info,
1091 p_var_value => l_total_amt);
1092
1093 l_debug_info := 'A-l_count';
1094 INL_LOGGING_PVT.Log_Variable (
1095 p_module_name => g_module_name,
1096 p_procedure_name => l_program_name,
1097 p_var_name => l_debug_info,
1098 p_var_value => l_count);
1099
1100 ELSIF assoc_List(iAssoc).to_parent_table_name = 'INL_SHIP_LINE_GROUPS' THEN
1101 SELECT SUM(DECODE(p_allocation_basis,'VALUE',NVL(NVL(ol.primary_qty,0)*NVL(ol.primary_unit_price * NVL(ol.currency_conversion_rate,1),0),0),
1102 Converted_Qty (l_inv_org_id,
1103 ol.inventory_item_id,
1104 NVL(ol.primary_qty,0),
1105 ol.primary_uom_code,
1106 p_allocation_uom_code))) + l_total_amt,
1107 COUNT(*) + l_count
1108 INTO l_total_amt,
1109 l_count
1110 FROM inl_ship_lines_all ol
1111 WHERE ol.ship_line_group_id = assoc_List(iAssoc).to_parent_table_id
1112 AND ol.ship_line_id = (SELECT MAX(sl.ship_line_id)
1113 FROM inl_ship_lines_all sl
1114 WHERE sl.ship_header_id = ol.ship_header_id --Bug#9660084
1115 AND sl.ship_line_group_id = ol.ship_line_group_id --Bug#9660084
1116 AND sl.ship_line_num = ol.ship_line_num --Bug#9660084
1117 --- SCM-051
1118 --- AND (sl.adjustment_num <= p_adjustment_num
1119 AND (ABS(sl.adjustment_num) <= ABS(p_adjustment_num)
1120 --- SCM-051
1121 OR sl.ship_header_id <> p_ship_header_id) --Bug#10221931*
1122 );
1123 l_debug_info := 'B-l_total_amt';
1124 INL_LOGGING_PVT.Log_Variable (
1125 p_module_name => g_module_name,
1126 p_procedure_name => l_program_name,
1127 p_var_name => l_debug_info,
1128 p_var_value => l_total_amt);
1129
1130 l_debug_info := 'B-l_count';
1131 INL_LOGGING_PVT.Log_Variable (
1132 p_module_name => g_module_name,
1133 p_procedure_name => l_program_name,
1134 p_var_name => l_debug_info,
1135 p_var_value => l_count);
1136
1137 ELSIF assoc_List(iAssoc).to_parent_table_name = 'INL_SHIP_LINES' THEN
1138 SELECT SUM(DECODE(p_allocation_basis,'VALUE',NVL(NVL(ol.primary_qty,0)*NVL(ol.primary_unit_price * NVL(ol.currency_conversion_rate,1),0),0),
1139 Converted_Qty (l_inv_org_id,
1140 ol.inventory_item_id,
1141 NVL(ol.primary_qty,0),
1142 ol.primary_uom_code,
1143 p_allocation_uom_code))) + l_total_amt,
1144 COUNT(*) + l_count
1145 INTO l_total_amt,
1146 l_count
1147 FROM inl_ship_lines_all ol
1148 WHERE ol.ship_line_id = assoc_List(iAssoc).to_parent_table_id;
1149
1150 l_debug_info := 'C-l_total_amt';
1151 INL_LOGGING_PVT.Log_Variable (
1152 p_module_name => g_module_name,
1153 p_procedure_name => l_program_name,
1154 p_var_name => l_debug_info,
1155 p_var_value => l_total_amt);
1156
1157 l_debug_info := 'C-l_count';
1158 INL_LOGGING_PVT.Log_Variable (
1159 p_module_name => g_module_name,
1160 p_procedure_name => l_program_name,
1161 p_var_name => l_debug_info,
1162 p_var_value => l_count);
1163
1164 ELSIF assoc_List(iAssoc).to_parent_table_name = 'INL_CHARGE_LINES' THEN
1165 IF p_allocation_basis = 'VALUE' THEN
1166 SELECT SUM(NVL(charge_amt,0) * NVL(currency_conversion_rate,1)) + l_total_amt,
1167 COUNT(*) + l_count
1168 INTO l_total_amt,
1169 l_count
1170 FROM inl_charge_lines cl
1171 WHERE cl.charge_line_id = assoc_List(iAssoc).to_parent_table_id;
1172 -- If any errors happen abort API.
1173 IF l_return_status = L_FND_RET_STS_ERROR THEN
1174 RAISE L_FND_EXC_ERROR;
1175 ELSIF l_return_status = L_FND_RET_STS_UNEXP_ERROR THEN
1176 RAISE L_FND_EXC_UNEXPECTED_ERROR;
1177 END IF;
1178 ELSE
1179 FND_MESSAGE.SET_NAME('INL','INL_ERR_CHK_CH_ALLOC');
1180 FND_MSG_PUB.Add;
1181 RAISE L_FND_EXC_ERROR;
1182 END IF;
1183
1184 l_debug_info := 'D-l_total_amt';
1185 INL_LOGGING_PVT.Log_Variable (
1186 p_module_name => g_module_name,
1187 p_procedure_name => l_program_name,
1188 p_var_name => l_debug_info,
1189 p_var_value => l_total_amt);
1190
1191 l_debug_info := 'D-l_count';
1192 INL_LOGGING_PVT.Log_Variable (
1193 p_module_name => g_module_name,
1194 p_procedure_name => l_program_name,
1195 p_var_name => l_debug_info,
1196 p_var_value => l_count);
1197
1198 ELSIF assoc_List(iAssoc).to_parent_table_name = 'INL_TAX_LINES' THEN
1199 IF p_allocation_basis = 'VALUE' THEN
1200 SELECT nvl(SUM(nrec_tax_amt),0) + l_total_amt,
1201 COUNT(*) + l_count
1202 INTO l_total_amt,
1203 l_count
1204 FROM inl_tax_lines tl --BUG#8330505
1205 WHERE tl.tax_line_id = assoc_List(iAssoc).to_parent_table_id;
1206 ELSE
1207 FND_MESSAGE.SET_NAME('INL','INL_ERR_TX_ALLOC');
1208 FND_MSG_PUB.Add;
1209 RAISE L_FND_EXC_ERROR;
1210 END IF;
1211 l_debug_info := 'E-l_total_amt';
1212 INL_LOGGING_PVT.Log_Variable (
1213 p_module_name => g_module_name,
1214 p_procedure_name => l_program_name,
1215 p_var_name => l_debug_info,
1216 p_var_value => l_total_amt);
1217
1218 l_debug_info := 'E-l_count';
1219 INL_LOGGING_PVT.Log_Variable (
1220 p_module_name => g_module_name,
1221 p_procedure_name => l_program_name,
1222 p_var_name => l_debug_info,
1223 p_var_value => l_count);
1224
1225 END IF;
1226 END LOOP;
1227 ELSE -- p_to_component_name = 'INL_SHIP_DISTS' THEN
1228 --
1229 -- This is for getting the total amount of each component the associated amount gets to.
1230 --
1231 l_debug_info := 'p_to_component_name = INL_SHIP_DISTS';
1232 INL_LOGGING_PVT.Log_Statement (
1233 p_module_name => g_module_name,
1234 p_procedure_name => l_program_name,
1235 p_debug_info => l_debug_info);
1236
1237 SELECT SUM(DECODE(p_allocation_basis,'VALUE',NVL(NVL(ol.primary_qty,0)*NVL(ol.primary_unit_price * NVL(ol.currency_conversion_rate,1),0),0),
1238 Converted_Qty (l_inv_org_id,
1239 ol.inventory_item_id,
1240 NVL(ol.primary_qty,0),
1241 ol.primary_uom_code,
1242 p_allocation_uom_code))) + l_total_amt,
1243 COUNT(*)
1244 INTO l_total_amt,
1245 l_count
1246 FROM inl_ship_lines_all ol
1247 WHERE ol.ship_header_id = DECODE(p_from_component_name,'INL_SHIP_HEADERS',p_from_component_id,ol.ship_header_id)
1248 AND ol.ship_line_group_id = DECODE(p_from_component_name,'INL_SHIP_LINE_GROUPS',p_from_component_id,ol.ship_line_group_id)
1249 AND ol.ship_line_id = DECODE(p_from_component_name,'INL_SHIP_LINES',p_from_component_id,ol.ship_line_id)
1250 AND ol.ship_header_id = p_ship_header_id
1251 AND ol.ship_line_id = (SELECT MAX(sl.ship_line_id)
1252 FROM inl_ship_lines_all sl
1253 WHERE sl.ship_header_id = ol.ship_header_id --Bug#9660084
1254 AND sl.ship_line_group_id = ol.ship_line_group_id --Bug#9660084
1255 AND sl.ship_line_num = ol.ship_line_num --Bug#9660084
1256 --- SCM-051
1257 --- AND (sl.adjustment_num <= p_adjustment_num
1258 AND (ABS(sl.adjustment_num) <= ABS(p_adjustment_num)
1259 --- SCM-051
1260 OR sl.ship_header_id <> p_ship_header_id) --Bug#10221931*
1261 );
1262 l_debug_info := 'F-l_total_amt';
1263 INL_LOGGING_PVT.Log_Variable (
1264 p_module_name => g_module_name,
1265 p_procedure_name => l_program_name,
1266 p_var_name => l_debug_info,
1267 p_var_value => l_total_amt);
1268
1269 l_debug_info := 'F-l_count';
1270 INL_LOGGING_PVT.Log_Variable (
1271 p_module_name => g_module_name,
1272 p_procedure_name => l_program_name,
1273 p_var_name => l_debug_info,
1274 p_var_value => l_count);
1275
1276 END IF;
1277 ELSE
1278 l_count := l_count_aux;
1279 END IF;
1280 x_total_amt := l_total_amt;
1281 l_debug_info := 'x_total_amt';
1282 INL_LOGGING_PVT.Log_Variable (
1283 p_module_name => g_module_name,
1284 p_procedure_name => l_program_name,
1285 p_var_name => l_debug_info,
1286 p_var_value => TO_CHAR(x_total_amt));
1287
1288 IF l_count > 1 THEN
1289 x_do_proportion := 'Y';
1290 END IF;
1291 l_debug_info := 'x_do_proportion';
1292 INL_LOGGING_PVT.Log_Variable (
1293 p_module_name => g_module_name,
1294 p_procedure_name => l_program_name,
1295 p_var_name => l_debug_info,
1296 p_var_value => x_do_proportion);
1297
1298 INL_LOGGING_PVT.Log_EndProc (
1299 p_module_name => g_module_name,
1300 p_procedure_name => l_program_name);
1301
1302 EXCEPTION
1303 WHEN L_FND_EXC_ERROR THEN
1304 INL_LOGGING_PVT.Log_ExpecError (
1305 p_module_name => g_module_name,
1306 p_procedure_name => l_program_name
1307 );
1308 x_return_status := L_FND_RET_STS_ERROR;
1309 WHEN L_FND_EXC_UNEXPECTED_ERROR THEN
1310 INL_LOGGING_PVT.Log_UnexpecError (
1311 p_module_name => g_module_name,
1312 p_procedure_name => l_program_name
1313 );
1314 x_return_status := L_FND_RET_STS_UNEXP_ERROR;
1315 WHEN OTHERS THEN
1316 INL_LOGGING_PVT.Log_UnexpecError (
1317 p_module_name => g_module_name,
1318 p_procedure_name => l_program_name
1319 );
1320 x_return_status := L_FND_RET_STS_UNEXP_ERROR;
1321 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1322 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,l_program_name);
1323 END IF;
1324
1325 END Get_TotalAmt;
1326
1327
1328 -- Utility name : Manage_Proportion
1329 -- Type : Private
1330 -- Function :
1331 -- Pre-reqs : None
1332 -- Parameters :
1333 -- IN : p_ship_header_id IN NUMBER
1334 -- p_adjustment_num IN NUMBER
1335 -- p_le_currency_code IN VARCHAR2
1336 -- p_from_component_name IN VARCHAR2
1337 -- p_from_component_id IN NUMBER
1338 -- p_to_component_name IN VARCHAR2
1339 -- p_to_component_id IN NUMBER
1340 -- p_allocation_basis IN VARCHAR2
1341 -- p_allocation_uom_code IN VARCHAR2
1342 -- p_total_amt IN NUMBER,
1343 -- OUT : o_factor OUT NOCOPY NUMBER
1344 -- x_return_status OUT NOCOPY VARCHAR2
1345 --
1346 -- Version : Current version 1.0
1347 --
1348 -- Notes :
1349
1350 PROCEDURE Manage_Proportion (
1351 p_ship_header_id IN NUMBER,
1352 p_adjustment_num IN NUMBER,
1353 p_le_currency_code IN VARCHAR2,
1354 p_from_component_name IN VARCHAR2,
1355 p_from_component_id IN NUMBER,
1356 p_to_component_name IN VARCHAR2,
1357 p_to_component_id IN NUMBER,
1358 p_allocation_basis IN VARCHAR2,
1359 p_allocation_uom_code IN VARCHAR2,
1360 p_total_amt IN VARCHAR2,
1361 o_factor OUT NOCOPY NUMBER,
1362 x_return_status OUT NOCOPY VARCHAR2)
1363 IS
1364 l_total_amt NUMBER;
1365 l_to_component_amt NUMBER;
1366 l_allocation_basis VARCHAR2(30);
1367 l_debug_info VARCHAR2(240);
1368 l_program_name CONSTANT VARCHAR2(30) := 'Manage_Proportion';
1369 l_inv_org_id NUMBER;
1370 l_return_status VARCHAR2(1);
1371
1372 --
1373 -- Obtains the Proportion Factor to be applied to the allocating amount.
1374 -- This is used when one component is associated to more than one component, e.g.
1375 -- One charge amount associated to two shipment lines. In cases like that,
1376 -- each shipment line must proportionally receive its part from the charge amount,
1377 -- according to the allocation basis of the Shipment Type.
1378 --
1379 -- CH1 $10 associated to SL1 and SL2, where SL1 = $40 and SL2 = $60
1380 -- If the allocation basis of the Association = 'VALUE', then
1381 -- the factor for SL1 = .4 and the factor for SL2 = .6, meaning that
1382 -- SL1 will receive $4 from the $10 charge, and SL2 will receive $6 from the $10 charge
1383 --
1384 -- This routine is also called for prorating a final amount that gets to a
1385 -- landed cost component to its corresponding shipment lines.
1386
1387 BEGIN
1388 INL_LOGGING_PVT.Log_BeginProc (
1389 p_module_name => g_module_name,
1390 p_procedure_name => l_program_name);
1391 --
1392 -- Initialize return status to SUCCESS
1393 --
1394 x_return_status := L_FND_RET_STS_SUCCESS;
1395 l_allocation_basis := p_allocation_basis;
1396 --
1397 -- Get Inventory Organization. It will be used when getting Item info.
1398 --
1399 SELECT organization_id
1400 INTO l_inv_org_id
1401 FROM inl_ship_headers_all
1402 WHERE ship_header_id = p_ship_header_id;
1403
1404
1405 INL_LOGGING_PVT.Log_Variable (
1406 p_module_name => g_module_name,
1407 p_procedure_name => l_program_name,
1408 p_var_name => 'p_ship_header_id',
1409 p_var_value => p_ship_header_id
1410 );
1411
1412 INL_LOGGING_PVT.Log_Variable (
1413 p_module_name => g_module_name,
1414 p_procedure_name => l_program_name,
1415 p_var_name => 'p_adjustment_num',
1416 p_var_value => p_adjustment_num
1417 );
1418
1419 INL_LOGGING_PVT.Log_Variable (
1420 p_module_name => g_module_name,
1421 p_procedure_name => l_program_name,
1422 p_var_name => 'p_from_component_name',
1423 p_var_value => p_from_component_name
1424 );
1425
1426 INL_LOGGING_PVT.Log_Variable (
1427 p_module_name => g_module_name,
1428 p_procedure_name => l_program_name,
1429 p_var_name => 'p_from_component_id',
1430 p_var_value => p_from_component_id
1431 );
1432
1433 INL_LOGGING_PVT.Log_Variable (
1434 p_module_name => g_module_name,
1435 p_procedure_name => l_program_name,
1436 p_var_name => 'p_to_component_name',
1437 p_var_value => p_to_component_name
1438 );
1439
1440 INL_LOGGING_PVT.Log_Variable (
1441 p_module_name => g_module_name,
1442 p_procedure_name => l_program_name,
1443 p_var_name => 'p_to_component_id',
1444 p_var_value => p_to_component_id
1445 );
1446
1447 INL_LOGGING_PVT.Log_Variable (
1448 p_module_name => g_module_name,
1449 p_procedure_name => l_program_name,
1450 p_var_name => 'p_allocation_basis',
1451 p_var_value => p_allocation_basis
1452 );
1453
1454 INL_LOGGING_PVT.Log_Variable (
1455 p_module_name => g_module_name,
1456 p_procedure_name => l_program_name,
1457 p_var_name => 'p_allocation_uom_code',
1458 p_var_value => p_allocation_uom_code
1459 );
1460
1461 INL_LOGGING_PVT.Log_Variable (
1462 p_module_name => g_module_name,
1463 p_procedure_name => l_program_name,
1464 p_var_name => 'p_to_component_name',
1465 p_var_value => p_to_component_name
1466 );
1467
1468 INL_LOGGING_PVT.Log_Variable (
1469 p_module_name => g_module_name,
1470 p_procedure_name => l_program_name,
1471 p_var_name => 'p_total_amt',
1472 p_var_value => p_total_amt
1473 );
1474
1475 IF p_to_component_name = 'INL_SHIP_HEADERS' THEN
1476 SELECT SUM(DECODE(l_allocation_basis,'VALUE',NVL(NVL(ol.primary_qty,0)*NVL(ol.primary_unit_price * NVL(ol.currency_conversion_rate,1),0),0),
1477 Converted_Qty (l_inv_org_id,
1478 ol.inventory_item_id,
1479 NVL(ol.primary_qty,0),
1480 ol.primary_uom_code,
1481 p_allocation_uom_code)))
1482 INTO l_to_component_amt
1483 FROM inl_ship_lines_all ol
1484 WHERE ol.ship_header_id = p_to_component_id
1485 AND ol.ship_line_id = (SELECT MAX(sl.ship_line_id)
1486 FROM inl_ship_lines_all sl
1487 WHERE sl.ship_header_id = ol.ship_header_id --Bug#9660084
1488 AND sl.ship_line_group_id = ol.ship_line_group_id --Bug#9660084
1489 AND sl.ship_line_num = ol.ship_line_num --Bug#9660084
1490 --- SCM-051
1491 --- AND (sl.adjustment_num <= p_adjustment_num
1492 AND (ABS(sl.adjustment_num) <= ABS(p_adjustment_num)
1493 --- SCM-051
1494 OR sl.ship_header_id <> p_ship_header_id) --Bug#10221931*
1495 );
1496 ELSIF p_to_component_name = 'INL_SHIP_LINE_GROUPS' THEN
1497 SELECT SUM(DECODE(l_allocation_basis,'VALUE',NVL(NVL(ol.primary_qty,0)*NVL(ol.primary_unit_price * NVL(ol.currency_conversion_rate,1),0),0),
1498 Converted_Qty (l_inv_org_id,
1499 ol.inventory_item_id,
1500 NVL(ol.primary_qty,0),
1501 ol.primary_uom_code,
1502 p_allocation_uom_code)))
1503 INTO l_to_component_amt
1504 FROM inl_ship_lines_all ol
1505 WHERE ol.ship_line_group_id = p_to_component_id
1506 AND ol.ship_line_id = (SELECT MAX(sl.ship_line_id)
1507 FROM inl_ship_lines_all sl
1508 WHERE sl.ship_header_id = ol.ship_header_id --Bug#9660084
1509 AND sl.ship_line_group_id = ol.ship_line_group_id --Bug#9660084
1510 AND sl.ship_line_num = ol.ship_line_num --Bug#9660084
1511 --- SCM-051
1512 --- AND (sl.adjustment_num <= p_adjustment_num
1513 AND (ABS(sl.adjustment_num) <= ABS(p_adjustment_num)
1514 --- SCM-051
1515 OR sl.ship_header_id <> p_ship_header_id) --Bug#10221931*
1516 );
1517 ELSIF p_to_component_name = 'INL_SHIP_LINES' THEN
1518 SELECT SUM(DECODE(l_allocation_basis,'VALUE',NVL(NVL(ol.primary_qty,0)*NVL(ol.primary_unit_price * NVL(ol.currency_conversion_rate,1),0),0),
1519 Converted_Qty (l_inv_org_id,
1520 ol.inventory_item_id,
1521 NVL(ol.primary_qty,0),
1522 ol.primary_uom_code,
1523 p_allocation_uom_code)))
1524 INTO l_to_component_amt
1525 FROM inl_ship_lines_all ol
1526 WHERE ol.ship_line_id = p_to_component_id;
1527 ELSIF p_to_component_name = 'INL_SHIP_DISTS' THEN
1528 SELECT SUM(DECODE(l_allocation_basis,'VALUE',NVL(NVL(ol.primary_qty,0)*NVL(ol.primary_unit_price * NVL(ol.currency_conversion_rate,1),0),0),
1529 Converted_Qty (l_inv_org_id,
1530 ol.inventory_item_id,
1531 NVL(ol.primary_qty,0),
1532 ol.primary_uom_code,
1533 p_allocation_uom_code)))
1534 INTO l_to_component_amt
1535 FROM inl_ship_lines_all ol
1536 WHERE ol.ship_line_id = p_to_component_id;
1537 ELSIF p_to_component_name = 'INL_CHARGE_LINES' THEN
1538 IF l_allocation_basis = 'VALUE' THEN
1539 SELECT SUM(NVL(charge_amt,0) * NVL(currency_conversion_rate,1))
1540 INTO l_to_component_amt
1541 FROM inl_charge_lines
1542 WHERE charge_line_id = p_to_component_id;
1543 ELSE
1544 FND_MESSAGE.SET_NAME('INL','INL_ERR_CHK_CH_ALLOC');
1545 FND_MSG_PUB.Add;
1546 RAISE L_FND_EXC_ERROR;
1547 END IF;
1548 ELSIF p_to_component_name = 'INL_TAX_LINES' THEN
1549 IF l_allocation_basis = 'VALUE' THEN
1550 SELECT NVL(SUM(nrec_tax_amt),0)
1551 INTO l_to_component_amt
1552 FROM inl_tax_lines --BUG#8330505
1553 WHERE tax_line_id = p_to_component_id;
1554 ELSE
1555 FND_MESSAGE.SET_NAME('INL','INL_ERR_TX_ALLOC');
1556 FND_MSG_PUB.Add;
1557 RAISE L_FND_EXC_ERROR;
1558 END IF;
1559 END IF;
1560
1561 l_debug_info := 'l_to_component_amt';
1562 INL_LOGGING_PVT.Log_Variable (
1563 p_module_name => g_module_name,
1564 p_procedure_name => l_program_name,
1565 p_var_name => l_debug_info,
1566 p_var_value => TO_CHAR(l_to_component_amt));
1567
1568
1569 IF p_total_amt = 0 THEN
1570 l_debug_info := 'No value for the basis '||l_allocation_basis;
1571 IF l_allocation_basis <> 'VALUE' THEN
1572 l_allocation_basis := 'VALUE';
1573 l_debug_info := 'No value for the basis '||'Changing allocation basis to '||l_allocation_basis;
1574 ELSE
1575 l_debug_info := 'Component not allocated';
1576 o_factor := 0;
1577 END IF;
1578 INL_LOGGING_PVT.Log_Statement (
1579 p_module_name => g_module_name,
1580 p_procedure_name => l_program_name,
1581 p_debug_info => l_debug_info);
1582 ELSE
1583 o_factor := l_to_component_amt/p_total_amt;
1584 END IF;
1585
1586 IF p_allocation_basis <> 'VALUE' THEN
1587 IF p_to_component_name = 'INL_CHARGE_LINES' THEN
1588 l_debug_info := 'Assumed allocation basis VALUE for charges';
1589 ELSIF p_to_component_name = 'INL_TAX_LINES' THEN
1590 l_debug_info := 'Assumed allocation basis VALUE for taxes';
1591 END IF;
1592 END IF;
1593
1594 l_debug_info := 'o_factor';
1595 INL_LOGGING_PVT.Log_Variable (
1596 p_module_name => g_module_name,
1597 p_procedure_name => l_program_name,
1598 p_var_name => l_debug_info,
1599 p_var_value => TO_CHAR(o_factor));
1600
1601 INL_LOGGING_PVT.Log_EndProc (
1602 p_module_name => g_module_name,
1603 p_procedure_name => l_program_name);
1604
1605 EXCEPTION
1606 WHEN L_FND_EXC_ERROR THEN
1607 INL_LOGGING_PVT.Log_ExpecError (
1608 p_module_name => g_module_name,
1609 p_procedure_name => l_program_name
1610 );
1611 x_return_status := L_FND_RET_STS_ERROR;
1612 WHEN L_FND_EXC_UNEXPECTED_ERROR THEN
1613 INL_LOGGING_PVT.Log_UnexpecError (
1614 p_module_name => g_module_name,
1615 p_procedure_name => l_program_name
1616 );
1617 x_return_status := L_FND_RET_STS_UNEXP_ERROR;
1618 WHEN OTHERS THEN
1619 INL_LOGGING_PVT.Log_UnexpecError (
1620 p_module_name => g_module_name,
1621 p_procedure_name => l_program_name
1622 );
1623 x_return_status := L_FND_RET_STS_UNEXP_ERROR;
1624 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1625 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,l_program_name);
1626 END IF;
1627
1628 END Manage_Proportion;
1629
1630 -- Utility name : Insert_Allocation
1631 -- Type : Private
1632 -- Function :
1633 --
1634 -- Amounts from Landed Cost Shipment Lines as well as amounts from charges and
1635 -- taxes should also generate lines in INL_ALLOCATIONS.
1636 --
1637 -- Landed Cost Shipment Lines that belong to components that are associated to others
1638 -- should generate allocations with LANDED_COST_FLAG = N.
1639 --
1640 -- Charges and taxes will always generate allocations with LANDED_COST_FLAG = N, since
1641 -- they will always end up to be sent to a Shipment Line flagged with LANDED_COST_FLAG = Y.
1642 --
1643 -- Pre-reqs : None
1644 -- Parameters :
1645 -- IN : p_ship_header_id IN NUMBER
1646 -- p_le_currency_code IN VARCHAR2,
1647 -- p_association_id IN NUMBER
1648 -- p_ship_line_id IN NUMBER
1649 -- p_amount IN NUMBER
1650 -- p_from_component_name IN VARCHAR2
1651 -- p_from_component_id IN NUMBER
1652 -- p_to_component_name IN VARCHAR2
1653 -- p_to_component_id IN NUMBER
1654 -- p_lc_flag IN VARCHAR2
1655 -- p_adjustment_num IN NUMBER
1656 -- OUT : x_return_status OUT NOCOPY VARCHAR2
1657 --
1658 -- Version : Current version 1.0
1659 --
1660 -- Notes :
1661 PROCEDURE Insert_Allocation (
1662 p_ship_header_id IN NUMBER,
1663 p_le_currency_code IN VARCHAR2,
1664 p_association_id IN NUMBER,
1665 p_ship_line_id IN NUMBER,
1666 p_amount IN NUMBER,
1667 p_from_component_name IN VARCHAR2,
1668 p_from_component_id IN NUMBER,
1669 p_to_component_name IN VARCHAR2,
1670 p_to_component_id IN NUMBER,
1671 p_lc_flag IN VARCHAR2,
1672 p_adjustment_num IN NUMBER,
1673 x_return_status OUT NOCOPY VARCHAR2
1674 ) IS
1675
1676 l_ship_line_lc_flag VARCHAR2(1);
1677 l_debug_info VARCHAR2(240);
1678 l_program_name CONSTANT VARCHAR2(30) := 'Insert_Allocation';
1679 l_from_component_name VARCHAR2(30);
1680 l_to_component_name VARCHAR2(30);
1681 l_count NUMBER;
1682 l_factor NUMBER;
1683 l_return_status VARCHAR2(1);
1684 l_total_amt NUMBER;
1685 l_do_proportion VARCHAR2(1);
1686
1687 CURSOR assoc IS
1688 SELECT a.association_id,
1689 a.from_parent_table_name,
1690 (SELECT max(sl.ship_line_id)
1691 FROM inl_ship_lines_all sl,
1692 inl_ship_lines_all sl0
1693 WHERE sl0.ship_line_id = a.from_parent_table_id
1694 AND sl.ship_header_id = sl0.ship_header_id
1695 AND sl.ship_line_group_id = sl0.ship_line_group_id
1696 AND sl.ship_line_num = sl0.ship_line_num
1697 --- SCM-051
1698 --- AND (sl.adjustment_num <= p_adjustment_num
1699 AND (ABS(sl.adjustment_num) <= ABS(p_adjustment_num)
1700 --- SCM-051
1701 OR sl.ship_header_id <> p_ship_header_id) --Bug#10221931*
1702 ) from_parent_table_id,
1703 a.to_parent_table_name,
1704 (SELECT max(sl.ship_line_id)
1705 FROM inl_ship_lines_all sl,
1706 inl_ship_lines_all sl0
1707 WHERE sl0.ship_line_id = a.to_parent_table_id
1708 AND sl.ship_header_id = sl0.ship_header_id
1709 AND sl.ship_line_group_id = sl0.ship_line_group_id
1710 AND sl.ship_line_num = sl0.ship_line_num
1711 --- SCM-051
1712 --- AND ( sl.adjustment_num <= p_adjustment_num
1713 AND ( ABS(sl.adjustment_num) <= ABS(p_adjustment_num)
1714 --- SCM-051
1715 OR sl.ship_header_id <> p_ship_header_id) --Bug#10221931*
1716 ) to_parent_table_id,
1717 a.allocation_basis,
1718 a.allocation_uom_code,
1719 a.to_parent_table_id ship_line_id
1720 /* SCM-LCM-010
1721 FROM inl_adj_associations_v a
1722 */
1723 FROM inl_associations a
1724 WHERE a.from_parent_table_name = 'INL_SHIP_LINES'
1725 AND a.from_parent_table_id =
1726 (SELECT MIN(sl.ship_line_id)
1727 FROM inl_ship_lines sl
1728 --- SCM-051
1729 --- WHERE (sl.adjustment_num <= p_adjustment_num
1730 WHERE (ABS(sl.adjustment_num) <= ABS(p_adjustment_num)
1731 --- SCM-051
1732 OR sl.ship_header_id <> p_ship_header_id) --Bug#10221931*
1733 START WITH sl.ship_line_id = p_ship_line_id
1734 CONNECT BY PRIOR sl.parent_ship_line_id = sl.ship_line_id )
1735 AND a.to_parent_table_name = 'INL_SHIP_LINES'
1736 AND a.ship_header_id = p_ship_header_id
1737 ORDER BY a.association_id;
1738 -- rec_assoc assoc%ROWTYPE;
1739 TYPE assoc_ListType IS TABLE OF assoc%ROWTYPE; --Bulk implem
1740 assoc_List assoc_ListType; --Bulk implem
1741
1742 BEGIN
1743
1744 INL_LOGGING_PVT.Log_BeginProc (
1745 p_module_name => g_module_name,
1746 p_procedure_name => l_program_name);
1747
1748 --
1749 -- Initialize return status to SUCCESS
1750 --
1751 x_return_status := L_FND_RET_STS_SUCCESS;
1752
1753 INL_LOGGING_PVT.Log_Variable (
1754 p_module_name => g_module_name,
1755 p_procedure_name => l_program_name,
1756 p_var_name => 'p_ship_header_id',
1757 p_var_value => p_ship_header_id);
1758 INL_LOGGING_PVT.Log_Variable (
1759 p_module_name => g_module_name,
1760 p_procedure_name => l_program_name,
1761 p_var_name => 'p_le_currency_code',
1762 p_var_value => p_le_currency_code);
1763 INL_LOGGING_PVT.Log_Variable (
1764 p_module_name => g_module_name,
1765 p_procedure_name => l_program_name,
1766 p_var_name => 'p_association_id',
1767 p_var_value => p_association_id);
1768 INL_LOGGING_PVT.Log_Variable (
1769 p_module_name => g_module_name,
1770 p_procedure_name => l_program_name,
1771 p_var_name => 'p_ship_line_id',
1772 p_var_value => p_ship_line_id);
1773 INL_LOGGING_PVT.Log_Variable (
1774 p_module_name => g_module_name,
1775 p_procedure_name => l_program_name,
1776 p_var_name => 'p_amount',
1777 p_var_value => p_amount);
1778 INL_LOGGING_PVT.Log_Variable (
1779 p_module_name => g_module_name,
1780 p_procedure_name => l_program_name,
1781 p_var_name => 'p_from_component_name',
1782 p_var_value => p_from_component_name);
1783 INL_LOGGING_PVT.Log_Variable (
1784 p_module_name => g_module_name,
1785 p_procedure_name => l_program_name,
1786 p_var_name => 'p_from_component_id',
1787 p_var_value => p_from_component_id);
1788 INL_LOGGING_PVT.Log_Variable (
1789 p_module_name => g_module_name,
1790 p_procedure_name => l_program_name,
1791 p_var_name => 'p_to_component_name',
1792 p_var_value => p_to_component_name);
1793 INL_LOGGING_PVT.Log_Variable (
1794 p_module_name => g_module_name,
1795 p_procedure_name => l_program_name,
1796 p_var_name => 'p_to_component_id',
1797 p_var_value => p_to_component_id);
1798 INL_LOGGING_PVT.Log_Variable (
1799 p_module_name => g_module_name,
1800 p_procedure_name => l_program_name,
1801 p_var_name => 'p_lc_flag',
1802 p_var_value => p_lc_flag);
1803 INL_LOGGING_PVT.Log_Variable (
1804 p_module_name => g_module_name,
1805 p_procedure_name => l_program_name,
1806 p_var_name => 'p_adjustment_num',
1807 p_var_value => p_adjustment_num);
1808
1809 -- bug 7660824
1810 -- Allocations from Shipment Lines with landed_cost_flag = 'N' should be inserted normally.
1811 -- The only difference is that it should be considered as amount zero, when inserting the
1812 -- corresponding allocation line.
1813 /*
1814 BEGIN
1815 SELECT ol.landed_cost_flag
1816 INTO l_ship_line_lc_flag
1817 FROM inl_adj_ship_lines_v ol
1818 WHERE ol.ship_line_id = p_ship_line_id;
1819 EXCEPTION
1820 WHEN NO_DATA_FOUND THEN
1821 l_ship_line_lc_flag := 'N';
1822 END;
1823 */
1824 l_ship_line_lc_flag := 'Y';
1825 --
1826 INL_LOGGING_PVT.Log_Variable (
1827 p_module_name => g_module_name,
1828 p_procedure_name => l_program_name,
1829 p_var_name => 'l_ship_line_lc_flag',
1830 p_var_value => l_ship_line_lc_flag);
1831
1832 -- For backward compatibility we are still using Distributions in calculations;
1833 -- however, for allocation purposes we'll use Shipment Lines instead.
1834 l_from_component_name := p_from_component_name;
1835 l_to_component_name := p_to_component_name;
1836 IF l_from_component_name = 'INL_SHIP_DISTS' THEN
1837 l_from_component_name := 'INL_SHIP_LINES';
1838 END IF;
1839 IF l_to_component_name = 'INL_SHIP_DISTS' THEN
1840 l_to_component_name := 'INL_SHIP_LINES';
1841 END IF;
1842
1843 -- Check whether the Ship Line of the allocation is associated to other Ship Lines
1844 l_debug_info := 'Check whether the Ship Line of the allocation is associated to other Ship Lines';
1845 INL_LOGGING_PVT.Log_Statement (
1846 p_module_name => g_module_name,
1847 p_procedure_name => l_program_name,
1848 p_debug_info => l_debug_info);
1849
1850 SELECT COUNT(*)
1851 INTO l_count
1852 FROM inl_associations a
1853 WHERE a.from_parent_table_name = 'INL_SHIP_LINES'
1854 AND a.to_parent_table_name = 'INL_SHIP_LINES'
1855 AND a.ship_header_id = p_ship_header_id;
1856
1857 IF nvl(l_count,0)> 0 THEN
1858 l_count := 0;
1859 OPEN assoc;
1860 FETCH assoc BULK COLLECT INTO assoc_List; --Bulk implem
1861 CLOSE assoc;
1862
1863 l_debug_info := 'Fetched '||NVL(assoc_List.COUNT, 0)||' association(s).';
1864 INL_LOGGING_PVT.Log_Statement(
1865 p_module_name => g_module_name,
1866 p_procedure_name => l_program_name,
1867 p_debug_info => l_debug_info
1868 ) ;
1869
1870
1871 IF NVL(assoc_List.COUNT,0) > 0 THEN
1872 FOR iAssoc IN 1 .. assoc_List.COUNT --Bulk implem
1873 LOOP
1874 l_count := l_count + 1;
1875
1876 IF p_amount <> 0 THEN
1877 IF l_count = 1 THEN
1878 l_debug_info := 'Call Get_TotalAmt';
1879 INL_LOGGING_PVT.Log_Statement (
1880 p_module_name => g_module_name,
1881 p_procedure_name => l_program_name,
1882 p_debug_info => l_debug_info);
1883 Get_TotalAmt (
1884 p_ship_header_id => p_ship_header_id,
1885 p_adjustment_num => p_adjustment_num,
1886 p_le_currency_code => p_le_currency_code,
1887 p_from_component_name => 'INL_SHIP_LINES',
1888 p_from_component_id => p_ship_line_id,
1889 p_to_component_name => assoc_List(iAssoc).to_parent_table_name,
1890 p_to_component_id => assoc_List(iAssoc).to_parent_table_id,
1891 p_allocation_basis => assoc_List(iAssoc).allocation_basis,
1892 p_allocation_uom_code => assoc_List(iAssoc).allocation_uom_code,
1893 x_total_amt => l_total_amt,
1894 x_do_proportion => l_do_proportion,
1895 x_return_status => l_return_status);
1896 -- If any errors happen abort API.
1897 IF l_return_status = L_FND_RET_STS_ERROR THEN
1898 RAISE L_FND_EXC_ERROR;
1899 ELSIF l_return_status = L_FND_RET_STS_UNEXP_ERROR THEN
1900 RAISE L_FND_EXC_UNEXPECTED_ERROR;
1901 END IF;
1902 END IF;
1903 IF l_do_proportion = 'Y' THEN
1904 l_debug_info := 'Call Manage_Proportion';
1905 INL_LOGGING_PVT.Log_Statement (
1906 p_module_name => g_module_name,
1907 p_procedure_name => l_program_name,
1908 p_debug_info => l_debug_info);
1909 Manage_Proportion (
1910 p_ship_header_id => p_ship_header_id,
1911 p_adjustment_num => p_adjustment_num,
1912 p_le_currency_code => p_le_currency_code,
1913 p_from_component_name => 'INL_SHIP_LINES',
1914 p_from_component_id => p_ship_line_id,
1915 p_to_component_name => assoc_List(iAssoc).to_parent_table_name,
1916 p_to_component_id => assoc_List(iAssoc).to_parent_table_id,
1917 p_allocation_basis => assoc_List(iAssoc).allocation_basis,
1918 p_allocation_uom_code => assoc_List(iAssoc).allocation_uom_code,
1919 p_total_amt => l_total_amt,
1920 o_factor => l_factor,
1921 x_return_status => l_return_status);
1922 -- If any errors happen abort API.
1923 IF l_return_status = L_FND_RET_STS_ERROR THEN
1924 RAISE L_FND_EXC_ERROR;
1925 ELSIF l_return_status = L_FND_RET_STS_UNEXP_ERROR THEN
1926 RAISE L_FND_EXC_UNEXPECTED_ERROR;
1927 END IF;
1928 ELSE
1929 l_factor := 1;
1930 END IF;
1931 ELSE
1932 l_factor := 0;
1933 END IF;
1934
1935 l_debug_info := 'l_factor';
1936 INL_LOGGING_PVT.Log_Variable (
1937 p_module_name => g_module_name,
1938 p_procedure_name => l_program_name,
1939 p_var_name => l_debug_info,
1940 p_var_value => TO_CHAR(l_factor));
1941
1942 l_debug_info := 'Inserting into inl_allocations';
1943 INL_LOGGING_PVT.Log_Statement (
1944 p_module_name => g_module_name,
1945 p_procedure_name => l_program_name,
1946 p_debug_info => l_debug_info);
1947
1948 l_debug_info := 'assoc_List('||iAssoc||').to_parent_table_id';
1949 INL_LOGGING_PVT.Log_Variable (p_module_name => g_module_name,
1950 p_procedure_name => l_program_name,
1951 p_var_name => l_debug_info,
1952 p_var_value => TO_CHAR(assoc_List(iAssoc).to_parent_table_id));
1953
1954 l_debug_info := 'p_amount * l_factor';
1955 INL_LOGGING_PVT.Log_Variable (p_module_name => g_module_name,
1956 p_procedure_name => l_program_name,
1957 p_var_name => l_debug_info,
1958 p_var_value => TO_CHAR(p_amount * l_factor));
1959
1960 --- Bug 7706718 - Recursive call to Insert_Allocation, until there are no shipment lines to redirect the allocation
1961 Insert_Allocation (
1962 p_ship_header_id => p_ship_header_id,
1963 p_le_currency_code => p_le_currency_code,
1964 p_association_id => p_association_id,
1965 p_ship_line_id => assoc_List(iAssoc).ship_line_id,
1966 p_amount => p_amount * l_factor,
1967 p_from_component_name => l_from_component_name,
1968 p_from_component_id => p_from_component_id,
1969 p_to_component_name => assoc_List(iAssoc).to_parent_table_name,
1970 p_to_component_id => assoc_List(iAssoc).to_parent_table_id,
1971 p_lc_flag => p_lc_flag,
1972 p_adjustment_num => p_adjustment_num,
1973 x_return_status => l_return_status);
1974
1975 -- If any errors happen abort API.
1976 IF l_return_status = L_FND_RET_STS_ERROR THEN
1977 RAISE L_FND_EXC_ERROR;
1978 ELSIF l_return_status = L_FND_RET_STS_UNEXP_ERROR THEN
1979 RAISE L_FND_EXC_UNEXPECTED_ERROR;
1980 END IF;
1981 END LOOP;
1982 END IF;
1983 END IF;
1984 IF l_count = 0 THEN
1985 l_debug_info := 'Inserting into inl_allocations';
1986 INL_LOGGING_PVT.Log_Statement (
1987 p_module_name => g_module_name,
1988 p_procedure_name => l_program_name,
1989 p_debug_info => l_debug_info);
1990 INL_LOGGING_PVT.Log_Variable (
1991 p_module_name => g_module_name,
1992 p_procedure_name => l_program_name,
1993 p_var_name => 'p_ship_line_id',
1994 p_var_value => p_ship_line_id);
1995 INL_LOGGING_PVT.Log_Variable (
1996 p_module_name => g_module_name,
1997 p_procedure_name => l_program_name,
1998 p_var_name => 'p_amount',
1999 p_var_value => p_amount);
2000
2001 INSERT INTO inl_allocations
2002 (allocation_id, /* 01 */
2003 ship_header_id, /* 02 */
2004 association_id, /* 03 */
2005 ship_line_id, /* 04 */
2006 from_parent_table_name, /* 05 */
2007 from_parent_table_id, /* 06 */
2008 to_parent_table_name, /* 07 */
2009 to_parent_table_id, /* 08 */
2010 adjustment_num, /* 09 */
2011 allocation_amt, /* 10 */
2012 landed_cost_flag, /* 11 */
2013 created_by, /* 12 */
2014 creation_date, /* 13 */
2015 last_updated_by, /* 14 */
2016 last_update_date, /* 15 */
2017 last_update_login) /* 16 */
2018 VALUES
2019 (inl_allocations_s.NEXTVAL, /* 01 */
2020 p_ship_header_id, /* 02 */
2021 p_association_id, /* 03 */
2022 p_ship_line_id, /* 04 */
2023 l_from_component_name, /* 05 */
2024 p_from_component_id, /* 06 */
2025 l_to_component_name, /* 07 */
2026 p_to_component_id, /* 08 */
2027 --- SCM-051
2028 ABS(p_adjustment_num), /* 09 */
2029 --- SCM-051
2030 p_amount, /* 10 */
2031 DECODE(p_lc_flag,'N','N','Y',l_ship_line_lc_flag), /* 11 */
2032 L_FND_USER_ID, /* 12 */
2033 SYSDATE, /* 13 */
2034 L_FND_USER_ID, /* 14 */
2035 SYSDATE, /* 15 */
2036 L_FND_LOGIN_ID); /* 16 */
2037 END IF;
2038
2039 INL_LOGGING_PVT.Log_EndProc (
2040 p_module_name => g_module_name,
2041 p_procedure_name => l_program_name);
2042
2043 EXCEPTION
2044 WHEN L_FND_EXC_ERROR THEN
2045 INL_LOGGING_PVT.Log_ExpecError (
2046 p_module_name => g_module_name,
2047 p_procedure_name => l_program_name);
2048 x_return_status := L_FND_RET_STS_ERROR;
2049 WHEN L_FND_EXC_UNEXPECTED_ERROR THEN
2050 INL_LOGGING_PVT.Log_UnexpecError (
2051 p_module_name => g_module_name,
2052 p_procedure_name => l_program_name);
2053 x_return_status := L_FND_RET_STS_UNEXP_ERROR;
2054 WHEN OTHERS THEN
2055 INL_LOGGING_PVT.Log_UnexpecError (
2056 p_module_name => g_module_name,
2057 p_procedure_name => l_program_name);
2058 x_return_status := L_FND_RET_STS_UNEXP_ERROR;
2059 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2060 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,l_program_name);
2061 END IF;
2062
2063 END Insert_Allocation;
2064
2065
2066 -- Utility name : Manage_Allocation
2067 -- Type : Private
2068 -- Function :
2069 --
2070 -- This routine allocates the amount that gets to a landed cost component into its corresponding
2071 -- Shipment Lines. In this routine, the Manage_Proportion routine is also called, this time
2072 -- to prorate the allocating amount into the many Shipment Lines corresponding to the
2073 -- component that is absorbing it.
2074 --
2075 -- Pre-reqs : None
2076 -- Parameters :
2077 -- IN : p_ship_header_id IN NUMBER
2078 -- p_le_currency_code IN VARCHAR2
2079 -- p_association_id IN NUMBER
2080 -- p_allocation_basis IN VARCHAR2
2081 -- p_allocation_uom_code IN VARCHAR2
2082 -- p_amount IN NUMBER
2083 -- p_from_component_name IN VARCHAR2
2084 -- p_from_component_id IN NUMBER
2085 -- p_to_component_name IN VARCHAR2
2086 -- p_to_component_id IN NUMBER
2087 -- p_lc_flag IN VARCHAR2
2088 -- p_adjustment_num IN NUMBER
2089 -- OUT : x_return_status OUT NOCOPY VARCHAR2
2090 --
2091 -- Version : Current version 1.0
2092 --
2093 -- Notes :
2094 PROCEDURE Manage_Allocation (
2095 p_ship_header_id IN NUMBER,
2096 p_le_currency_code IN VARCHAR2,
2097 p_association_id IN NUMBER,
2098 p_allocation_basis IN VARCHAR2,
2099 p_allocation_uom_code IN VARCHAR2,
2100 p_amount IN NUMBER,
2101 p_from_component_name IN VARCHAR2,
2102 p_from_component_id IN NUMBER,
2103 p_to_component_name IN VARCHAR2,
2104 p_to_component_id IN NUMBER,
2105 p_lc_flag IN VARCHAR2,
2106 p_adjustment_num IN NUMBER,
2107 x_return_status OUT NOCOPY VARCHAR2
2108 ) IS
2109 /*
2110 CURSOR dist IS
2111 SELECT ship_line_id
2112 FROM inl_ship_lines_all ol
2113 WHERE ol.ship_header_id = p_ship_header_id
2114 AND ol.ship_line_id = DECODE(p_to_component_name, 'INL_SHIP_LINES', p_to_component_id, ol.ship_line_id)
2115 AND ol.ship_line_group_id = DECODE(p_to_component_name, 'INL_SHIP_LINE_GROUPS', p_to_component_id, ol.ship_line_group_id)
2116 AND ol.ship_line_id = (SELECT MAX(sl.ship_line_id)
2117 FROM inl_ship_lines_all sl
2118 WHERE sl.ship_header_id = ol.ship_header_id --Bug#9660084
2119 AND sl.ship_line_group_id = ol.ship_line_group_id --Bug#9660084
2120 AND sl.ship_line_num = ol.ship_line_num --Bug#9660084
2121 AND (sl.adjustment_num <= p_adjustment_num
2122 OR sl.ship_header_id <> p_ship_header_id) --Bug#10221931*
2123 )
2124
2125 ORDER BY ol.ship_line_id;
2126 */
2127 CURSOR dist IS -- this cursor will be open only for p_to_component_name in 'INL_SHIP_LINE_GROUPS', 'INL_SHIP_HEADERS'
2128 SELECT ship_line_id
2129 FROM inl_ship_lines_all ol
2130 WHERE ol.ship_header_id = p_ship_header_id
2131 AND ol.ship_line_group_id = DECODE(p_to_component_name, 'INL_SHIP_LINE_GROUPS', p_to_component_id, ol.ship_line_group_id)
2132 AND ol.ship_line_id = (SELECT MAX(sl.ship_line_id)
2133 FROM inl_ship_lines_all sl
2134 WHERE sl.ship_header_id = ol.ship_header_id --Bug#9660084
2135 AND sl.ship_line_group_id = ol.ship_line_group_id --Bug#9660084
2136 AND sl.ship_line_num = ol.ship_line_num --Bug#9660084
2137 --- SCM-051
2138 --- AND (sl.adjustment_num <= p_adjustment_num
2139 AND (ABS(sl.adjustment_num) <= ABS(p_adjustment_num)
2140 --- SCM-051
2141 ))
2142 ORDER BY ol.ship_line_id;
2143
2144
2145 -- rec_dist dist%ROWTYPE;
2146 TYPE dist_ListType IS TABLE OF dist%ROWTYPE; --Bulk implem
2147 dist_List dist_ListType; --Bulk implem
2148
2149
2150 l_factor NUMBER;
2151 l_debug_info VARCHAR2(240);
2152 l_program_name CONSTANT VARCHAR2(30) := 'Manage_Allocation';
2153 l_return_status VARCHAR2(1);
2154 l_count NUMBER;
2155 l_total_amt NUMBER;
2156 l_do_proportion VARCHAR2(1);
2157
2158 BEGIN
2159 INL_LOGGING_PVT.Log_BeginProc (
2160 p_module_name => g_module_name,
2161 p_procedure_name => l_program_name);
2162 --
2163 -- Initialize return status to SUCCESS
2164 --
2165 x_return_status := L_FND_RET_STS_SUCCESS;
2166 INL_LOGGING_PVT.Log_Variable (
2167 p_module_name => g_module_name,
2168 p_procedure_name => l_program_name,
2169 p_var_name => 'p_ship_header_id',
2170 p_var_value => p_ship_header_id);
2171 INL_LOGGING_PVT.Log_Variable (
2172 p_module_name => g_module_name,
2173 p_procedure_name => l_program_name,
2174 p_var_name => 'p_le_currency_code',
2175 p_var_value => p_le_currency_code);
2176 INL_LOGGING_PVT.Log_Variable (
2177 p_module_name => g_module_name,
2178 p_procedure_name => l_program_name,
2179 p_var_name => 'p_association_id',
2180 p_var_value => p_association_id);
2181 INL_LOGGING_PVT.Log_Variable (
2182 p_module_name => g_module_name,
2183 p_procedure_name => l_program_name,
2184 p_var_name => 'p_allocation_basis',
2185 p_var_value => p_allocation_basis);
2186 INL_LOGGING_PVT.Log_Variable (
2187 p_module_name => g_module_name,
2188 p_procedure_name => l_program_name,
2189 p_var_name => 'p_allocation_uom_code',
2190 p_var_value => p_allocation_uom_code);
2191 INL_LOGGING_PVT.Log_Variable (
2192 p_module_name => g_module_name,
2193 p_procedure_name => l_program_name,
2194 p_var_name => 'p_amount',
2195 p_var_value => p_amount);
2196 INL_LOGGING_PVT.Log_Variable (
2197 p_module_name => g_module_name,
2198 p_procedure_name => l_program_name,
2199 p_var_name => 'p_from_component_name',
2200 p_var_value => p_from_component_name);
2201 INL_LOGGING_PVT.Log_Variable (
2202 p_module_name => g_module_name,
2203 p_procedure_name => l_program_name,
2204 p_var_name => 'p_from_component_id',
2205 p_var_value => p_from_component_id);
2206 INL_LOGGING_PVT.Log_Variable (
2207 p_module_name => g_module_name,
2208 p_procedure_name => l_program_name,
2209 p_var_name => 'p_to_component_name',
2210 p_var_value => p_to_component_name);
2211 INL_LOGGING_PVT.Log_Variable (
2212 p_module_name => g_module_name,
2213 p_procedure_name => l_program_name,
2214 p_var_name => 'p_to_component_id',
2215 p_var_value => p_to_component_id);
2216 INL_LOGGING_PVT.Log_Variable (
2217 p_module_name => g_module_name,
2218 p_procedure_name => l_program_name,
2219 p_var_name => 'p_adjustment_num',
2220 p_var_value => p_adjustment_num);
2221 l_count := 0;
2222
2223 IF p_to_component_name = 'INL_SHIP_LINES'
2224 THEN
2225 l_do_proportion:='N';
2226 l_total_amt:= 0;
2227 Insert_Allocation (
2228 p_ship_header_id => p_ship_header_id,
2229 p_le_currency_code => p_le_currency_code,
2230 p_association_id => p_association_id,
2231 p_ship_line_id => p_to_component_id,
2232 p_amount => p_amount,
2233 p_from_component_name => p_from_component_name,
2234 p_from_component_id => p_from_component_id,
2235 p_to_component_name => p_to_component_name,
2236 p_to_component_id => p_to_component_id,
2237 p_lc_flag => p_lc_flag,
2238 p_adjustment_num => p_adjustment_num,
2239 x_return_status => l_return_status);
2240
2241 -- If any errors happen abort API.
2242 IF l_return_status = L_FND_RET_STS_ERROR THEN
2243 RAISE L_FND_EXC_ERROR;
2244 ELSIF l_return_status = L_FND_RET_STS_UNEXP_ERROR THEN
2245 RAISE L_FND_EXC_UNEXPECTED_ERROR;
2246 END IF;
2247 ELSE
2248
2249 OPEN dist;
2250 FETCH dist BULK COLLECT INTO dist_List; --Bulk implem
2251 CLOSE dist;
2252
2253 l_debug_info := 'Fetched '||NVL(dist_List.COUNT, 0)||' Line(s).';
2254 INL_LOGGING_PVT.Log_Statement(
2255 p_module_name => g_module_name,
2256 p_procedure_name => l_program_name,
2257 p_debug_info => l_debug_info
2258 ) ;
2259
2260 IF NVL(dist_List.COUNT,0)>0
2261 THEN
2262 FOR idist IN 1 .. dist_List.COUNT --Bulk implem
2263 LOOP
2264
2265 l_count := l_count + 1;
2266
2267 IF p_amount <> 0 THEN
2268 IF l_count = 1 THEN
2269 -- in case of FROM and TO in ('INL_SHIP_DISTS','INL_SHIP_LINES')
2270 -- AND FROM_id = TO_id it is about an allocation from one line to the same line
2271 l_debug_info := 'Call Get_TotalAmt';
2272 INL_LOGGING_PVT.Log_Statement (
2273 p_module_name => g_module_name,
2274 p_procedure_name => l_program_name,
2275 p_debug_info => l_debug_info);
2276 Get_TotalAmt (
2277 p_ship_header_id => p_ship_header_id,
2278 p_adjustment_num => p_adjustment_num,
2279 p_le_currency_code => p_le_currency_code,
2280 p_from_component_name => p_to_component_name,
2281 p_from_component_id => p_to_component_id,
2282 p_to_component_name => 'INL_SHIP_DISTS',
2283 p_to_component_id => dist_List(idist).ship_line_id,
2284 p_allocation_basis => p_allocation_basis,
2285 p_allocation_uom_code => p_allocation_uom_code,
2286 x_total_amt => l_total_amt,
2287 x_do_proportion => l_do_proportion,
2288 x_return_status => l_return_status);
2289 -- If any errors happen abort API.
2290 IF l_return_status = L_FND_RET_STS_ERROR THEN
2291 RAISE L_FND_EXC_ERROR;
2292 ELSIF l_return_status = L_FND_RET_STS_UNEXP_ERROR THEN
2293 RAISE L_FND_EXC_UNEXPECTED_ERROR;
2294 END IF;
2295 END IF;
2296 IF l_do_proportion = 'Y' THEN
2297 l_debug_info := 'Call Manage_Proportion';
2298 INL_LOGGING_PVT.Log_Statement (
2299 p_module_name => g_module_name,
2300 p_procedure_name => l_program_name,
2301 p_debug_info => l_debug_info);
2302 Manage_Proportion (
2303 p_ship_header_id => p_ship_header_id,
2304 p_adjustment_num => p_adjustment_num,
2305 p_le_currency_code => p_le_currency_code,
2306 p_from_component_name => p_to_component_name,
2307 p_from_component_id => p_to_component_id,
2308 p_to_component_name => 'INL_SHIP_DISTS',
2309 p_to_component_id => dist_List(idist).ship_line_id,
2310 p_allocation_basis => p_allocation_basis,
2311 p_allocation_uom_code => p_allocation_uom_code,
2312 p_total_amt => l_total_amt,
2313 o_factor => l_factor,
2314 x_return_status => l_return_status);
2315 -- If any errors happen abort API.
2316 IF l_return_status = L_FND_RET_STS_ERROR THEN
2317 RAISE L_FND_EXC_ERROR;
2318 ELSIF l_return_status = L_FND_RET_STS_UNEXP_ERROR THEN
2319 RAISE L_FND_EXC_UNEXPECTED_ERROR;
2320 END IF;
2321 ELSE
2322 l_factor := 1;
2323 END IF;
2324 ELSE
2325 l_factor := 0;
2326 END IF;
2327
2328 Insert_Allocation (
2329 p_ship_header_id => p_ship_header_id,
2330 p_le_currency_code => p_le_currency_code,
2331 p_association_id => p_association_id,
2332 p_ship_line_id => dist_List(idist).ship_line_id,
2333 p_amount => p_amount * l_factor,
2334 p_from_component_name => p_from_component_name,
2335 p_from_component_id => p_from_component_id,
2336 p_to_component_name => p_to_component_name,
2337 p_to_component_id => p_to_component_id,
2338 p_lc_flag => p_lc_flag,
2339 p_adjustment_num => p_adjustment_num,
2340 x_return_status => l_return_status);
2341
2342 -- If any errors happen abort API.
2343 IF l_return_status = L_FND_RET_STS_ERROR THEN
2344 RAISE L_FND_EXC_ERROR;
2345 ELSIF l_return_status = L_FND_RET_STS_UNEXP_ERROR THEN
2346 RAISE L_FND_EXC_UNEXPECTED_ERROR;
2347 END IF;
2348
2349 END LOOP;
2350 END IF;
2351 END IF;
2352 INL_LOGGING_PVT.Log_EndProc (
2353 p_module_name => g_module_name,
2354 p_procedure_name => l_program_name);
2355
2356 EXCEPTION
2357 WHEN L_FND_EXC_ERROR THEN
2358 INL_LOGGING_PVT.Log_ExpecError (
2359 p_module_name => g_module_name,
2360 p_procedure_name => l_program_name);
2361 x_return_status := L_FND_RET_STS_ERROR;
2362 WHEN L_FND_EXC_UNEXPECTED_ERROR THEN
2363 INL_LOGGING_PVT.Log_UnexpecError (
2364 p_module_name => g_module_name,
2365 p_procedure_name => l_program_name);
2366 x_return_status := L_FND_RET_STS_UNEXP_ERROR;
2367 WHEN OTHERS THEN
2368 INL_LOGGING_PVT.Log_UnexpecError (
2369 p_module_name => g_module_name,
2370 p_procedure_name => l_program_name);
2371 x_return_status := L_FND_RET_STS_UNEXP_ERROR;
2372 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2373 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,l_program_name);
2374 END IF;
2375
2376 END Manage_Allocation;
2377
2378
2379 -- Utility name : Control_Allocation
2380 -- Type : Private
2381 -- Function : This routine controls and redirect, when necessary, the allocation of an amount that
2382 -- comes from a component to another.
2383 --
2384 -- |-> Shipment Line SL1
2385 -- Example: Charge CH1: Shipment SH1 -
2386 -- |-> Shipment Line SL2
2387 --
2388 -- This routine would take care of calling the Manage_Allocation routine
2389 -- for allocating CH1 amount to Shipment SH1, and then for allocating
2390 -- the SH1 portion of CH1 to Shipment Line SL1 and Shipment Line SL1.
2391 --
2392 -- Pre-reqs : None
2393 -- Parameters :
2394 -- IN : p_ship_header_id IN NUMBER
2395 -- p_le_currency_code IN VARCHAR2
2396 -- p_association_id IN NUMBER
2397 -- p_allocation_basis IN VARCHAR2
2398 -- p_allocation_uom_code IN VARCHAR2
2399 -- p_amount IN NUMBER
2400 -- p_from_component_name IN VARCHAR2
2401 -- p_from_component_id IN NUMBER
2402 -- p_to_component_name IN VARCHAR2
2403 -- p_to_component_id IN NUMBER
2404 -- p_adjustment_num IN NUMBER
2405 --
2406 -- OUT x_return_status OUT NOCOPY VARCHAR2
2407 --
2408 -- Version : Current version 1.0
2409 --
2410 -- Notes :
2411
2412 PROCEDURE Control_Allocation (
2413 p_ship_header_id IN NUMBER,
2414 p_le_currency_code IN VARCHAR2,
2415 p_association_id IN NUMBER,
2416 p_allocation_basis IN VARCHAR2,
2417 p_allocation_uom_code IN VARCHAR2,
2418 p_amount IN NUMBER,
2419 p_from_component_name IN VARCHAR2,
2420 p_from_component_id IN NUMBER,
2421 p_to_component_name IN VARCHAR2,
2422 p_to_component_id IN NUMBER,
2423 p_adjustment_num IN NUMBER,
2424 x_return_status OUT NOCOPY VARCHAR2
2425 ) IS
2426 CURSOR component IS
2427 SELECT 1 SEQ_NUM,
2428 'INL_SHIP_LINES' COMPONENT_NAME,
2429 ol.ship_line_id COMPONENT_ID
2430 FROM inl_ship_lines_all ol
2431 WHERE ol.ship_header_id = p_ship_header_id
2432 AND ol.ship_line_id = DECODE(p_to_component_name, 'INL_SHIP_LINES', p_to_component_id, -1)
2433 AND ol.ship_line_id = (SELECT MAX(sl.ship_line_id)
2434 FROM inl_ship_lines_all sl
2435 WHERE sl.ship_header_id = ol.ship_header_id --Bug#9660084
2436 AND sl.ship_line_group_id = ol.ship_line_group_id --Bug#9660084
2437 AND sl.ship_line_num = ol.ship_line_num --Bug#9660084
2438 --- SCM-051
2439 --- AND (sl.adjustment_num <= p_adjustment_num
2440 AND (ABS(sl.adjustment_num) <= ABS(p_adjustment_num)
2441 --- SCM-051
2442 OR sl.ship_header_id <> p_ship_header_id) --Bug#10221931*
2443 )
2444 UNION
2445 SELECT 2 SEQ_NUM,
2446 'INL_SHIP_HEADERS' COMPONENT_NAME,
2447 ol.ship_header_id COMPONENT_ID
2448 FROM inl_ship_lines_all ol
2449 WHERE ol.ship_header_id = p_ship_header_id
2450 AND ol.ship_line_id = DECODE(p_to_component_name, 'INL_SHIP_LINES', p_to_component_id, -1)
2451 AND ol.ship_line_id = (SELECT MAX(sl.ship_line_id)
2452 FROM inl_ship_lines_all sl
2453 WHERE sl.ship_header_id = ol.ship_header_id --Bug#9660084
2454 AND sl.ship_line_group_id = ol.ship_line_group_id --Bug#9660084
2455 AND sl.ship_line_num = ol.ship_line_num --Bug#9660084
2456 --- SCM-051
2457 --- AND (sl.adjustment_num <= p_adjustment_num
2458 AND (ABS(sl.adjustment_num) <= ABS(p_adjustment_num)
2459 --- SCM-051
2460 OR sl.ship_header_id <> p_ship_header_id) --Bug#10221931*
2461 )
2462 UNION
2463 SELECT 1 SEQ_NUM,
2464 p_to_component_name COMPONENT_NAME,
2465 p_to_component_id COMPONENT_ID
2466 FROM dual
2467 ORDER BY seq_num;
2468
2469 TYPE component_ListType IS TABLE OF component%ROWTYPE; --Bulk implem
2470 component_List component_ListType; --Bulk implem
2471
2472 CURSOR assoc (pc_component_name VARCHAR2, pc_component_id NUMBER) IS
2473 SELECT a.association_id,
2474 a.from_parent_table_name,
2475 DECODE(a.from_parent_table_name,
2476 'INL_SHIP_LINES',
2477 (SELECT MAX(sl.ship_line_id)
2478 FROM inl_ship_lines_all sl,
2479 inl_ship_lines_all sl0
2480 WHERE sl0.ship_line_id = a.from_parent_table_id
2481 AND sl.ship_header_id = sl0.ship_header_id
2482 AND sl.ship_line_group_id = sl0.ship_line_group_id
2483 AND sl.ship_line_num = sl0.ship_line_num
2484 --- SCM-051
2485 --- AND (sl.adjustment_num <= p_adjustment_num
2486 AND (ABS(sl.adjustment_num) <= ABS(p_adjustment_num)
2487 --- SCM-051
2488 OR sl.ship_header_id <> p_ship_header_id) --Bug#10221931*
2489 ),
2490 'INL_CHARGE_LINES',
2491 (SELECT MAX(cl.charge_line_id)
2492 FROM inl_charge_lines cl
2493 --- SCM-051
2494 --- WHERE cl.adjustment_num <= p_adjustment_num
2495 WHERE ABS(cl.adjustment_num) <= ABS(p_adjustment_num)
2496 --- SCM-051
2497 START WITH cl.charge_line_id = a.from_parent_table_id
2498 CONNECT BY PRIOR cl.charge_line_id = cl.parent_charge_line_id
2499 ),
2500 'INL_TAX_LINES',
2501 (SELECT MAX(tl.tax_line_id)
2502 FROM inl_tax_lines tl
2503 --- SCM-051
2504 --- WHERE tl.adjustment_num <= p_adjustment_num
2505 WHERE ABS(tl.adjustment_num) <= ABS(p_adjustment_num)
2506 --- SCM-051
2507 START WITH tl.tax_line_id = a.from_parent_table_id
2508 CONNECT BY PRIOR tl.tax_line_id = tl.parent_tax_line_id
2509 ),
2510 a.from_parent_table_id) from_parent_table_id,
2511 a.to_parent_table_name,
2512 DECODE(a.to_parent_table_name,
2513 'INL_SHIP_LINES',
2514 (SELECT MAX(sl.ship_line_id)
2515 FROM inl_ship_lines_all sl,
2516 inl_ship_lines_all sl0
2517 WHERE sl0.ship_line_id = a.to_parent_table_id
2518 AND sl.ship_header_id = sl0.ship_header_id
2519 AND sl.ship_line_group_id = sl0.ship_line_group_id
2520 AND sl.ship_line_num = sl0.ship_line_num
2521 --- SCM-051
2522 --- AND (sl.adjustment_num <= p_adjustment_num
2523 AND (ABS(sl.adjustment_num) <= ABS(p_adjustment_num)
2524 --- SCM-051
2525 OR sl.ship_header_id <> p_ship_header_id) --Bug#10221931*
2526 ),
2527 'INL_CHARGE_LINES',
2528 (SELECT MAX(cl.charge_line_id)
2529 FROM inl_charge_lines cl
2530 --- SCM-051
2531 --- WHERE cl.adjustment_num <= p_adjustment_num
2532 WHERE ABS(cl.adjustment_num) <= ABS(p_adjustment_num)
2533 --- SCM-051
2534 START WITH cl.charge_line_id = a.to_parent_table_id
2535 CONNECT BY PRIOR cl.charge_line_id = cl.parent_charge_line_id
2536 ),
2537 a.to_parent_table_id) to_parent_table_id
2538 FROM inl_associations a
2539 WHERE a.ship_header_id = p_ship_header_id
2540 AND a.from_parent_table_name = pc_component_name
2541 AND a.from_parent_table_id = pc_component_id
2542 ORDER BY a.association_id;
2543
2544 -- rec_assoc assoc%ROWTYPE;
2545 TYPE assoc_ListType IS TABLE OF assoc%ROWTYPE; --Bulk implem
2546 assoc_List assoc_ListType; --Bulk implem
2547
2548 l_next_level_allocation VARCHAR2(1);
2549 l_lc_flag VARCHAR2(1);
2550 l_factor NUMBER;
2551 l_debug_info VARCHAR2(240);
2552 l_program_name CONSTANT VARCHAR2(30) := 'Control_Allocation';
2553 l_return_status VARCHAR2(1);
2554 l_count NUMBER;
2555 l_total_amt NUMBER;
2556 l_do_proportion VARCHAR2(1);
2557 BEGIN
2558
2559 INL_LOGGING_PVT.Log_BeginProc (
2560 p_module_name => g_module_name,
2561 p_procedure_name => l_program_name);
2562 --
2563 -- Initialize return status to SUCCESS
2564 --
2565
2566 x_return_status := L_FND_RET_STS_SUCCESS;
2567
2568 l_next_level_allocation := 'N';
2569
2570 INL_LOGGING_PVT.Log_Variable (
2571 p_module_name => g_module_name,
2572 p_procedure_name => l_program_name,
2573 p_var_name => 'p_ship_header_id',
2574 p_var_value => p_ship_header_id);
2575 INL_LOGGING_PVT.Log_Variable (
2576 p_module_name => g_module_name,
2577 p_procedure_name => l_program_name,
2578 p_var_name => 'p_le_currency_code',
2579 p_var_value => p_le_currency_code);
2580 INL_LOGGING_PVT.Log_Variable (
2581 p_module_name => g_module_name,
2582 p_procedure_name => l_program_name,
2583 p_var_name => 'p_association_id',
2584 p_var_value => p_association_id);
2585 INL_LOGGING_PVT.Log_Variable (
2586 p_module_name => g_module_name,
2587 p_procedure_name => l_program_name,
2588 p_var_name => 'p_allocation_basis',
2589 p_var_value => p_allocation_basis);
2590 INL_LOGGING_PVT.Log_Variable (
2591 p_module_name => g_module_name,
2592 p_procedure_name => l_program_name,
2593 p_var_name => 'p_allocation_uom_code',
2594 p_var_value => p_allocation_uom_code);
2595 INL_LOGGING_PVT.Log_Variable (
2596 p_module_name => g_module_name,
2597 p_procedure_name => l_program_name,
2598 p_var_name => 'p_amount',
2599 p_var_value => p_amount);
2600 INL_LOGGING_PVT.Log_Variable (
2601 p_module_name => g_module_name,
2602 p_procedure_name => l_program_name,
2603 p_var_name => 'p_from_component_name',
2604 p_var_value => p_from_component_name);
2605 INL_LOGGING_PVT.Log_Variable (
2606 p_module_name => g_module_name,
2607 p_procedure_name => l_program_name,
2608 p_var_name => 'p_from_component_id',
2609 p_var_value => p_from_component_id);
2610 INL_LOGGING_PVT.Log_Variable (
2611 p_module_name => g_module_name,
2612 p_procedure_name => l_program_name,
2613 p_var_name => 'p_to_component_name',
2614 p_var_value => p_to_component_name);
2615 INL_LOGGING_PVT.Log_Variable (
2616 p_module_name => g_module_name,
2617 p_procedure_name => l_program_name,
2618 p_var_name => 'p_to_component_id',
2619 p_var_value => p_to_component_id);
2620 INL_LOGGING_PVT.Log_Variable (
2621 p_module_name => g_module_name,
2622 p_procedure_name => l_program_name,
2623 p_var_name => 'p_adjustment_num',
2624 p_var_value => p_adjustment_num);
2625
2626 OPEN component;
2627 FETCH component BULK COLLECT INTO component_List; --Bulk implem
2628 CLOSE component;
2629
2630 l_debug_info := 'Fetched '||NVL(component_List.COUNT, 0)||' component(s).';
2631 INL_LOGGING_PVT.Log_Statement(
2632 p_module_name => g_module_name,
2633 p_procedure_name => l_program_name,
2634 p_debug_info => l_debug_info
2635 ) ;
2636
2637 IF NVL(component_List.COUNT,0)>0
2638 THEN
2639 FOR icomponent IN 1 .. component_List.COUNT --Bulk implem
2640 LOOP
2641
2642 l_count := 0;
2643
2644 OPEN assoc(component_List(icomponent).component_name,
2645 component_List(icomponent).component_id);
2646 FETCH assoc BULK COLLECT INTO assoc_List; --Bulk implem
2647 CLOSE assoc;
2648
2649 l_debug_info := 'Fetched '||NVL(assoc_List.COUNT, 0)||' association(s).';
2650 INL_LOGGING_PVT.Log_Statement(
2651 p_module_name => g_module_name,
2652 p_procedure_name => l_program_name,
2653 p_debug_info => l_debug_info
2654 ) ;
2655 IF NVL(assoc_List.COUNT,0)>0
2656 THEN
2657 FOR iAssoc IN 1 .. assoc_List.COUNT --Bulk implem
2658 LOOP
2659
2660 l_count := l_count + 1;
2661
2662 l_next_level_allocation := 'Y';
2663
2664 IF p_amount <> 0 THEN
2665 IF l_count = 1 THEN
2666 l_debug_info := 'Call Get_TotalAmt';
2667 INL_LOGGING_PVT.Log_Statement (
2668 p_module_name => g_module_name,
2669 p_procedure_name => l_program_name,
2670 p_debug_info => l_debug_info);
2671 Get_TotalAmt (
2672 p_ship_header_id =>p_ship_header_id,
2673 p_adjustment_num =>p_adjustment_num,
2674 p_le_currency_code =>p_le_currency_code,
2675 p_from_component_name =>assoc_List(iAssoc).from_parent_table_name,
2676 p_from_component_id =>assoc_List(iAssoc).from_parent_table_id,
2677 p_to_component_name =>assoc_List(iAssoc).to_parent_table_name,
2678 p_to_component_id =>assoc_List(iAssoc).to_parent_table_id,
2679 p_allocation_basis =>p_allocation_basis,
2680 p_allocation_uom_code =>p_allocation_uom_code,
2681 x_total_amt =>l_total_amt,
2682 x_do_proportion =>l_do_proportion,
2683 x_return_status =>l_return_status);
2684 -- If any errors happen abort API.
2685 IF l_return_status = L_FND_RET_STS_ERROR THEN
2686 RAISE L_FND_EXC_ERROR;
2687 ELSIF l_return_status = L_FND_RET_STS_UNEXP_ERROR THEN
2688 RAISE L_FND_EXC_UNEXPECTED_ERROR;
2689 END IF;
2690 END IF;
2691 IF l_do_proportion = 'Y' THEN
2692 l_debug_info := 'Call Manage_Proportion';
2693 INL_LOGGING_PVT.Log_Statement (
2694 p_module_name => g_module_name,
2695 p_procedure_name => l_program_name,
2696 p_debug_info => l_debug_info);
2697 Manage_Proportion (
2698 p_ship_header_id =>p_ship_header_id,
2699 p_adjustment_num =>p_adjustment_num,
2700 p_le_currency_code =>p_le_currency_code,
2701 p_from_component_name =>assoc_List(iAssoc).from_parent_table_name,
2702 p_from_component_id =>assoc_List(iAssoc).from_parent_table_id,
2703 p_to_component_name =>assoc_List(iAssoc).to_parent_table_name,
2704 p_to_component_id =>assoc_List(iAssoc).to_parent_table_id,
2705 p_allocation_basis =>p_allocation_basis,
2706 p_allocation_uom_code =>p_allocation_uom_code,
2707 p_total_amt =>l_total_amt,
2708 o_factor =>l_factor,
2709 x_return_status =>l_return_status);
2710 -- If any errors happen abort API.
2711 IF l_return_status = L_FND_RET_STS_ERROR THEN
2712 RAISE L_FND_EXC_ERROR;
2713 ELSIF l_return_status = L_FND_RET_STS_UNEXP_ERROR THEN
2714 RAISE L_FND_EXC_UNEXPECTED_ERROR;
2715 END IF;
2716 ELSE
2717 l_factor := 1;
2718 END IF;
2719 ELSE
2720 l_factor := 0;
2721 END IF;
2722
2723 Control_Allocation (
2724 p_ship_header_id => p_ship_header_id,
2725 p_le_currency_code => p_le_currency_code,
2726 p_association_id => p_association_id,
2727 p_allocation_basis => p_allocation_basis,
2728 p_allocation_uom_code => p_allocation_uom_code,
2729 p_amount => p_amount * l_factor,
2730 ---- Bug 7706732, Bug 7708012 - Allocations that come from redirected allocations must be created
2731 ---- with the original "from" component
2732 ---- rec_component.component_name,
2733 ---- rec_component.component_id,
2734 p_from_component_name => p_from_component_name,
2735 p_from_component_id => p_from_component_id,
2736 ---- Bug 7706732, Bug 7708012
2737 p_to_component_name => assoc_List(iAssoc).to_parent_table_name,
2738 p_to_component_id => assoc_List(iAssoc).to_parent_table_id,
2739 p_adjustment_num => p_adjustment_num,
2740 x_return_status => l_return_status);
2741
2742 -- If any errors happen abort API.
2743 IF l_return_status = L_FND_RET_STS_ERROR THEN
2744 RAISE L_FND_EXC_ERROR;
2745 ELSIF l_return_status = L_FND_RET_STS_UNEXP_ERROR THEN
2746 RAISE L_FND_EXC_UNEXPECTED_ERROR;
2747 END IF;
2748
2749 END LOOP;
2750 END IF;
2751 END LOOP;
2752 END IF;
2753
2754 INL_LOGGING_PVT.Log_Variable (
2755 p_module_name => g_module_name,
2756 p_procedure_name => l_program_name,
2757 p_var_name => 'p_to_component_name',
2758 p_var_value => p_to_component_name);
2759
2760 IF p_to_component_name IN ('INL_CHARGE_LINES','INL_TAX_LINES') THEN
2761 Insert_Allocation (
2762 p_ship_header_id => p_ship_header_id,
2763 p_le_currency_code => p_le_currency_code,
2764 p_association_id => p_association_id,
2765 p_ship_line_id => NULL,
2766 p_amount => p_amount,
2767 p_from_component_name => p_from_component_name,
2768 p_from_component_id => p_from_component_id,
2769 p_to_component_name => p_to_component_name,
2770 p_to_component_id => p_to_component_id,
2771 p_lc_flag => 'N',
2772 p_adjustment_num => p_adjustment_num,
2773 x_return_status => l_return_status);
2774
2775 -- If any errors happen abort API.
2776 IF l_return_status = L_FND_RET_STS_ERROR THEN
2777 RAISE L_FND_EXC_ERROR;
2778 ELSIF l_return_status = L_FND_RET_STS_UNEXP_ERROR THEN
2779 RAISE L_FND_EXC_UNEXPECTED_ERROR;
2780 END IF;
2781 ELSE
2782 --Bug#9660084
2783 -- SELECT DECODE(l_next_level_allocation,'N','Y','N')
2784 -- INTO l_lc_flag
2785 -- FROM DUAL;
2786 IF l_next_level_allocation = 'N' THEN
2787 l_lc_flag := 'Y';
2788 ELSE
2789 l_lc_flag := 'N';
2790 END IF;
2791 --Bug#9660084
2792
2793 Manage_Allocation (
2794 p_ship_header_id => p_ship_header_id,
2795 p_le_currency_code => p_le_currency_code,
2796 p_association_id => p_association_id,
2797 p_allocation_basis => p_allocation_basis,
2798 p_allocation_uom_code => p_allocation_uom_code,
2799 p_amount => p_amount,
2800 p_from_component_name => p_from_component_name,
2801 p_from_component_id => p_from_component_id,
2802 p_to_component_name => p_to_component_name,
2803 p_to_component_id => p_to_component_id,
2804 p_lc_flag => l_lc_flag,
2805 p_adjustment_num => p_adjustment_num,
2806 x_return_status => l_return_status);
2807
2808 -- If any errors happen abort API.
2809 IF l_return_status = L_FND_RET_STS_ERROR THEN
2810 RAISE L_FND_EXC_ERROR;
2811 ELSIF l_return_status = L_FND_RET_STS_UNEXP_ERROR THEN
2812 RAISE L_FND_EXC_UNEXPECTED_ERROR;
2813 END IF;
2814
2815 END IF;
2816
2817 INL_LOGGING_PVT.Log_EndProc (
2818 p_module_name => g_module_name,
2819 p_procedure_name => l_program_name);
2820
2821 EXCEPTION
2822 WHEN L_FND_EXC_ERROR THEN
2823 INL_LOGGING_PVT.Log_ExpecError (
2824 p_module_name => g_module_name,
2825 p_procedure_name => l_program_name);
2826 x_return_status := L_FND_RET_STS_ERROR;
2827 WHEN L_FND_EXC_UNEXPECTED_ERROR THEN
2828 INL_LOGGING_PVT.Log_UnexpecError (
2829 p_module_name => g_module_name,
2830 p_procedure_name => l_program_name);
2831 x_return_status := L_FND_RET_STS_UNEXP_ERROR;
2832 WHEN OTHERS THEN
2833 INL_LOGGING_PVT.Log_UnexpecError (
2834 p_module_name => g_module_name,
2835 p_procedure_name => l_program_name);
2836 x_return_status := L_FND_RET_STS_UNEXP_ERROR;
2837 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2838 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,l_program_name);
2839 END IF;
2840
2841 END Control_Allocation;
2842
2843 -- Utility name : Update_Allocation
2844 -- Type : Private
2845 -- Function : For actual amount allocations (adjustment_num > 0), stamp as the parent_allocation_id, the allocation_id
2846 -- of corresponding estimated amount (adjustment_num = 0).
2847 --
2848 -- Pre-reqs : None
2849 -- Parameters :
2850 -- IN : p_ship_header_id IN NUMBER
2851 -- p_adjustment_num IN NUMBER,
2852 -- OUT : x_return_status OUT NOCOPY VARCHAR2
2853 --
2854 --
2855 PROCEDURE Update_Allocation (
2856 p_ship_header_id IN NUMBER,
2857 p_adjustment_num IN NUMBER,
2858 x_return_status OUT NOCOPY VARCHAR2
2859 ) IS
2860 --
2861 --
2862 --
2863 CURSOR updalloc IS
2864 SELECT allocation_id,
2865 parent_allocation_id,
2866 ship_header_id,
2867 association_id,
2868 ship_line_id,
2869 adjustment_num
2870 FROM inl_allocations
2871 --- SCM-051
2872 ---
2873 WHERE ABS(adjustment_num) = ABS(p_adjustment_num)
2874 --- SCM-051
2875 AND ship_header_id = p_ship_header_id
2876 ORDER BY allocation_id;
2877 -- rec_updalloc updalloc%ROWTYPE;
2878 TYPE updalloc_ListType IS TABLE OF updalloc%ROWTYPE; --Bulk implem
2879 updalloc_List updalloc_ListType; --Bulk implem
2880
2881 l_debug_info VARCHAR2(240);
2882 l_program_name CONSTANT VARCHAR2(30) := 'Update_Allocation';
2883 l_return_status VARCHAR2(1);
2884
2885 BEGIN
2886
2887 INL_LOGGING_PVT.Log_BeginProc (
2888 p_module_name => g_module_name,
2889 p_procedure_name => l_program_name);
2890 --
2891 -- Initialize return status to SUCCESS
2892 --
2893
2894 x_return_status := L_FND_RET_STS_SUCCESS;
2895
2896 l_debug_info := 'p_ship_header_id';
2897 INL_LOGGING_PVT.Log_Variable (
2898 p_module_name => g_module_name,
2899 p_procedure_name => l_program_name,
2900 p_var_name => l_debug_info,
2901 p_var_value => TO_CHAR(p_ship_header_id));
2902
2903 OPEN updalloc;
2904 FETCH updalloc BULK COLLECT INTO updalloc_List; --Bulk implem
2905 CLOSE updalloc;
2906
2907 l_debug_info := 'Fetched '||NVL(updalloc_List.COUNT, 0)||' record(s).';
2908 INL_LOGGING_PVT.Log_Statement(
2909 p_module_name => g_module_name,
2910 p_procedure_name => l_program_name,
2911 p_debug_info => l_debug_info
2912 ) ;
2913
2914 IF NVL(updalloc_List.COUNT,0)>0
2915 THEN
2916 FOR iupdalloc IN 1 .. updalloc_List.COUNT --Bulk implem
2917 LOOP
2918
2919 l_debug_info := 'Updating inl_allocations with parent_allocation_id';
2920 INL_LOGGING_PVT.Log_Statement (
2921 p_module_name => g_module_name,
2922 p_procedure_name => l_program_name,
2923 p_debug_info => l_debug_info);
2924
2925 UPDATE inl_allocations a1
2926 SET a1.parent_allocation_id = (SELECT MIN(a2.allocation_id)
2927 FROM inl_allocations a2
2928 WHERE a2.ship_header_id = updalloc_List(iUpdAlloc).ship_header_id
2929 AND NVL(a2.association_id,0) = NVL(updalloc_List(iUpdAlloc).association_id,0)
2930 AND (a2.ship_line_id = updalloc_List(iUpdAlloc).ship_line_id
2931 OR a2.ship_line_id = (SELECT a.parent_ship_line_id
2932 FROM inl_ship_lines_all a
2933 WHERE a.ship_line_id = updalloc_List(iUpdAlloc).ship_line_id))
2934
2935 AND a2.adjustment_num = 0
2936 AND NOT EXISTS (SELECT 'X' FROM inl_allocations a1
2937 ------ bug #7674125
2938 WHERE NVL(a1.parent_allocation_id,0) = NVL(a2.allocation_id,0)
2939 AND a1.ship_header_id = a2.ship_header_id
2940 AND NVL(a1.association_id,0) = NVL(a2.association_id,0)
2941 AND a1.adjustment_num = updalloc_List(iUpdAlloc).adjustment_num
2942 AND a1.landed_cost_flag = 'Y' ))
2943 WHERE a1.allocation_id = updalloc_List(iUpdAlloc).allocation_id
2944 AND a1.adjustment_num = updalloc_List(iUpdAlloc).adjustment_num;
2945
2946 l_debug_info := 'updalloc_List(iUpdAlloc).allocation_id';
2947 INL_LOGGING_PVT.Log_Variable (
2948 p_module_name => g_module_name,
2949 p_procedure_name => l_program_name,
2950 p_var_name => l_debug_info,
2951 p_var_value => TO_CHAR(updalloc_List(iUpdAlloc).allocation_id));
2952
2953 l_debug_info := 'updalloc_List(iUpdAlloc).adjustment_num';
2954 INL_LOGGING_PVT.Log_Variable (
2955 p_module_name => g_module_name,
2956 p_procedure_name => l_program_name,
2957 p_var_name => l_debug_info,
2958 p_var_value => TO_CHAR(updalloc_List(iUpdAlloc).adjustment_num));
2959
2960 END LOOP;
2961 END IF;
2962 INL_LOGGING_PVT.Log_EndProc (
2963 p_module_name => g_module_name,
2964 p_procedure_name => l_program_name);
2965
2966 EXCEPTION
2967 WHEN L_FND_EXC_ERROR THEN
2968 INL_LOGGING_PVT.Log_ExpecError (
2969 p_module_name => g_module_name,
2970 p_procedure_name => l_program_name);
2971 x_return_status := L_FND_RET_STS_ERROR;
2972 WHEN L_FND_EXC_UNEXPECTED_ERROR THEN
2973 INL_LOGGING_PVT.Log_UnexpecError (
2974 p_module_name => g_module_name,
2975 p_procedure_name => l_program_name);
2976 x_return_status := L_FND_RET_STS_UNEXP_ERROR;
2977 WHEN OTHERS THEN
2978 INL_LOGGING_PVT.Log_UnexpecError (
2979 p_module_name => g_module_name,
2980 p_procedure_name => l_program_name);
2981 x_return_status := L_FND_RET_STS_UNEXP_ERROR;
2982 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2983 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,l_program_name);
2984 END IF;
2985 END Update_Allocation;
2986
2987 -- API name : Run_Calculation
2988 -- Type : Private
2989 -- Function : Calculate Landed Costs for a given LCM Shipment.
2990 -- Pre-reqs : None
2991 -- Parameters :
2992 -- IN : p_api_version IN NUMBER Required
2993 -- p_init_msg_list IN VARCHAR2 Optional Default = L_FND_FALSE
2994 -- p_commit IN VARCHAR2 Optional Default = L_FND_FALSE
2995 -- p_validation_level IN NUMBER Optional Default = L_FND_VALID_LEVEL_FULL
2996 -- p_ship_header_id IN NUMBER Required
2997 -- p_calc_scope_code IN NUMBER Required Default = 0
2998 -- 0-Run for all components
2999 -- 1-Run only for Item Price components
3000 -- 2-Run only for Charge components
3001 -- 3-Run only form Tax components
3002 --
3003 -- Although kept in the code, calculation scope is no longer used by this program.
3004 --
3005 -- OUT x_return_status OUT NOCOPY VARCHAR2
3006 -- x_msg_count OUT NOCOPY NUMBER
3007 -- x_msg_data OUT NOCOPY VARCHAR2
3008 --
3009 -- Version : Current version 1.0
3010 --
3011 -- Notes :
3012 PROCEDURE Run_Calculation (
3013 p_api_version IN NUMBER,
3014 p_init_msg_list IN VARCHAR2 := L_FND_FALSE,
3015 p_commit IN VARCHAR2 := L_FND_FALSE,
3016 p_validation_level IN NUMBER := L_FND_VALID_LEVEL_FULL,
3017 p_ship_header_id IN NUMBER,
3018 p_calc_scope_code IN NUMBER,
3019 x_return_status OUT NOCOPY VARCHAR2,
3020 x_msg_count OUT NOCOPY NUMBER,
3021 x_msg_data OUT NOCOPY VARCHAR2) IS
3022
3023 CURSOR assoc (pc_adjustment_num IN NUMBER
3024 ) IS
3025 SELECT a.ship_header_id,
3026 a.from_parent_table_name,
3027 DECODE(a.from_parent_table_name,
3028 'INL_SHIP_LINES',
3029 (SELECT MAX(sl.ship_line_id)
3030 FROM inl_ship_lines_all sl,
3031 inl_ship_lines_all sl0
3032 WHERE sl0.ship_line_id = a.from_parent_table_id
3033 AND sl.ship_header_id = sl0.ship_header_id
3034 AND sl.ship_line_group_id = sl0.ship_line_group_id
3035 AND sl.ship_line_num = sl0.ship_line_num
3036
3037 --- SCM-051
3038 --- AND (sl.adjustment_num <= pc_adjustment_num
3039 AND (ABS(sl.adjustment_num) <= ABS(pc_adjustment_num)
3040 --- SCM-051
3041 OR sl.ship_header_id <> p_ship_header_id) --Bug#10221931*
3042 ),
3043 'INL_CHARGE_LINES',
3044 (SELECT MAX(cl.charge_line_id)
3045 FROM inl_charge_lines cl
3046 --- SCM-051
3047 --- WHERE cl.adjustment_num <= pc_adjustment_num
3048 WHERE ABS(cl.adjustment_num) <= ABS(pc_adjustment_num)
3049 --- SCM-051
3050 START WITH cl.charge_line_id = a.from_parent_table_id
3051 CONNECT BY PRIOR cl.charge_line_id = cl.parent_charge_line_id
3052 ),
3053 'INL_TAX_LINES',
3054 (SELECT MAX(tl.tax_line_id)
3055 FROM inl_tax_lines tl
3056 --- SCM-051
3057 --- WHERE tl.adjustment_num <= pc_adjustment_num
3058 WHERE ABS(tl.adjustment_num) <= ABS(pc_adjustment_num)
3059 --- SCM-051
3060 START WITH tl.tax_line_id = a.from_parent_table_id
3061 CONNECT BY PRIOR tl.tax_line_id = tl.parent_tax_line_id
3062 ),
3063 a.from_parent_table_id) from_parent_table_id,
3064 a.to_parent_table_name,
3065 DECODE(a.to_parent_table_name,
3066 'INL_SHIP_LINES',
3067 (SELECT MAX(sl.ship_line_id)
3068 FROM inl_ship_lines_all sl,
3069 inl_ship_lines_all sl0
3070 WHERE sl0.ship_line_id = a.to_parent_table_id
3071 AND sl.ship_header_id = sl0.ship_header_id
3072 AND sl.ship_line_group_id = sl0.ship_line_group_id
3073 AND sl.ship_line_num = sl0.ship_line_num
3074 --- SCM-051
3075 --- AND (sl.adjustment_num <= pc_adjustment_num
3076 AND (ABS(sl.adjustment_num) <= ABS(pc_adjustment_num)
3077 --- SCM-051
3078 OR sl.ship_header_id <> p_ship_header_id) --Bug#10221931*
3079
3080 ),
3081 'INL_CHARGE_LINES',
3082 (SELECT MAX(cl.charge_line_id)
3083 FROM inl_charge_lines cl
3084 --- SCM-051
3085 --- WHERE cl.adjustment_num <= pc_adjustment_num
3086 WHERE ABS(cl.adjustment_num) <= ABS(pc_adjustment_num)
3087 --- SCM-051
3088 START WITH cl.charge_line_id = a.to_parent_table_id
3089 CONNECT BY PRIOR cl.charge_line_id = cl.parent_charge_line_id
3090 ),
3091 a.to_parent_table_id) to_parent_table_id,
3092 a.allocation_basis,
3093 a.allocation_uom_code,
3094 a.association_id
3095 FROM inl_associations a
3096 WHERE DECODE(a.from_parent_table_name,
3097 'INL_SHIP_LINES',
3098 (SELECT MAX(sl.ship_line_id)
3099 FROM inl_ship_lines_all sl,
3100 inl_ship_lines_all sl0
3101 WHERE sl0.ship_line_id = a.from_parent_table_id
3102 AND sl.ship_header_id = sl0.ship_header_id
3103 AND sl.ship_line_group_id = sl0.ship_line_group_id
3104 AND sl.ship_line_num = sl0.ship_line_num
3105 --- SCM-051
3106 --- AND (sl.adjustment_num <= pc_adjustment_num
3107 AND (ABS(sl.adjustment_num) <= ABS(pc_adjustment_num)
3108 --- SCM-051
3109 OR sl.ship_header_id <> p_ship_header_id) --Bug#10221931*
3110 ),
3111 'INL_CHARGE_LINES',
3112 (SELECT MAX(cl.charge_line_id)
3113 FROM inl_charge_lines cl
3114 --- SCM-051
3115 --- WHERE cl.adjustment_num <= pc_adjustment_num
3116 WHERE ABS(cl.adjustment_num) <= ABS(pc_adjustment_num)
3117 --- SCM-051
3118 START WITH cl.charge_line_id = a.from_parent_table_id
3119 CONNECT BY PRIOR cl.charge_line_id = cl.parent_charge_line_id
3120 ),
3121 'INL_TAX_LINES',
3122 (SELECT MAX(tl.tax_line_id)
3123 FROM inl_tax_lines tl
3124 --- SCM-051
3125 --- WHERE tl.adjustment_num <= pc_adjustment_num
3126 WHERE ABS(tl.adjustment_num) <= ABS(pc_adjustment_num)
3127 --- SCM-051
3128 START WITH tl.tax_line_id = a.from_parent_table_id
3129 CONNECT BY PRIOR tl.tax_line_id = tl.parent_tax_line_id
3130 )) IS NOT NULL
3131 AND ship_header_id = p_ship_header_id
3132 ORDER BY from_parent_table_name,
3133 from_parent_table_id,
3134 to_parent_table_name,
3135 to_parent_table_id;
3136
3137 -- rec_assoc assoc%ROWTYPE;
3138 TYPE assoc_ListType IS TABLE OF assoc%ROWTYPE; --Bulk implem
3139 assoc_List assoc_ListType; --Bulk implem
3140
3141 CURSOR dist (pc_adjustment_num IN NUMBER
3142 ) IS
3143 SELECT ship_header_id,
3144 ship_line_id,
3145 primary_qty,
3146 -- bug 7660824
3147 DECODE(landed_cost_flag,'Y',ol.primary_unit_price * NVL(ol.currency_conversion_rate,1),0) fc_primary_unit_price
3148 FROM inl_ship_lines_all ol
3149 WHERE ol.ship_header_id = p_ship_header_id
3150 AND ol.ship_line_id = (SELECT MAX(sl.ship_line_id)
3151 FROM inl_ship_lines_all sl
3152 WHERE sl.ship_header_id = ol.ship_header_id --Bug#9660084
3153 AND sl.ship_line_group_id = ol.ship_line_group_id --Bug#9660084
3154 AND sl.ship_line_num = ol.ship_line_num --Bug#9660084
3155 --- SCM-051
3156 --- AND (sl.adjustment_num <= pc_adjustment_num
3157 AND (ABS(sl.adjustment_num) <= ABS(pc_adjustment_num)
3158 --- SCM-051
3159 OR sl.ship_header_id <> p_ship_header_id) --Bug#10221931*
3160 )
3161 ORDER BY ship_line_id;
3162
3163 -- rec_dist dist%ROWTYPE;
3164 TYPE dist_ListType IS TABLE OF dist%ROWTYPE; --Bulk implem
3165 dist_List dist_ListType; --Bulk implem
3166
3167
3168 CURSOR charge (pc_adjustment_num IN NUMBER
3169 ) IS
3170 SELECT -- c.charge_amt, --BUG#9719618
3171 DECODE(c.landed_cost_flag,'Y',c.charge_amt,0) charge_amt, --BUG#9719618
3172 c.currency_code,
3173 c.currency_conversion_type,
3174 c.currency_conversion_rate,
3175 c.currency_conversion_date,
3176 c.charge_line_id
3177 FROM inl_charge_lines c,
3178 --Bug#13988746 BEG
3179 (
3180 SELECT DISTINCT a.from_parent_table_id
3181 FROM inl_associations a
3182 WHERE a.from_parent_table_name = 'INL_CHARGE_LINES'
3183 AND a.ship_header_id = p_ship_header_id
3184 ) X
3185 --Bug#13988746 END
3186 --- SCM-051
3187 --- WHERE c.adjustment_num <= pc_adjustment_num --Bug#9660084
3188 WHERE ABS(c.adjustment_num) <= ABS(pc_adjustment_num) --Bug#9660084
3189 --Bug#13988746 BEG
3190 AND c.charge_line_id
3191 = (
3192 SELECT MAX(cl.charge_line_id)
3193 FROM inl_charge_lines cl
3194 WHERE cl.adjustment_num <= ABS(pc_adjustment_num) -- SCM-051
3195 START WITH cl.charge_line_id = x.from_parent_table_id
3196 CONNECT BY PRIOR cl.charge_line_id = cl.parent_charge_line_id
3197 )
3198 --- SCM-051
3199 /*
3200 AND EXISTS (SELECT 'x'
3201 FROM inl_associations x
3202 WHERE x.from_parent_table_name = 'INL_CHARGE_LINES'
3203 AND (SELECT MAX(cl.charge_line_id)
3204 FROM inl_charge_lines cl
3205 --- SCM-051
3206 --- WHERE cl.adjustment_num <= pc_adjustment_num
3207 WHERE ABS(cl.adjustment_num) <= ABS(pc_adjustment_num)
3208 --- SCM-051
3209 START WITH cl.charge_line_id = x.from_parent_table_id
3210 CONNECT BY PRIOR cl.charge_line_id = cl.parent_charge_line_id) = c.charge_line_id
3211 AND x.ship_header_id = p_ship_header_id
3212 AND ROWNUM < 2)*/
3213 --Bug#13988746 END
3214 ORDER BY charge_line_id;
3215 -- rec_charge charge%ROWTYPE;
3216 TYPE charge_ListType IS TABLE OF charge%ROWTYPE; --Bulk implem
3217 charge_List charge_ListType; --Bulk implem
3218
3219 CURSOR tax (pc_adjustment_num IN NUMBER
3220 ) IS
3221 SELECT tax_amt,
3222 tax_line_id
3223 FROM inl_tax_lines t --BUG#8330505
3224 --- SCM-051
3225 --- WHERE t.adjustment_num <= pc_adjustment_num --Bug#9660084
3226 WHERE ABS(t.adjustment_num) <= ABS(pc_adjustment_num) --Bug#9660084
3227 --- SCM-051
3228 AND EXISTS (SELECT 'x'
3229 FROM inl_associations x
3230 WHERE x.from_parent_table_name = 'INL_TAX_LINES'
3231 AND (SELECT MAX(tl.tax_line_id)
3232 FROM inl_tax_lines tl
3233 --- SCM-051
3234 --- WHERE tl.adjustment_num <= pc_adjustment_num
3235 WHERE ABS(tl.adjustment_num) <= ABS(pc_adjustment_num)
3236 --- SCM-051
3237 START WITH tl.tax_line_id = x.from_parent_table_id
3238 CONNECT BY PRIOR tl.tax_line_id = tl.parent_tax_line_id) = t.tax_line_id
3239 AND x.ship_header_id = p_ship_header_id
3240 AND ROWNUM < 2)
3241 ORDER BY tax_line_id;
3242 -- rec_tax tax%ROWTYPE;
3243 TYPE tax_ListType IS TABLE OF tax%ROWTYPE; --Bulk implem
3244 tax_List tax_ListType; --Bulk implem
3245
3246 l_amount NUMBER;
3247 l_from_amount NUMBER;
3248 l_to_amount NUMBER;
3249 l_factor NUMBER;
3250 l_count1 NUMBER;
3251 l_le_currency_code VARCHAR2(3);
3252 l_lc_flag VARCHAR2(1);
3253 l_inclusive_tax_amt NUMBER;
3254 l_debug_info VARCHAR2(240);
3255 l_program_name CONSTANT VARCHAR2(30) := 'Run_Calculation';
3256 l_api_version CONSTANT NUMBER := 1.0;
3257 l_return_status VARCHAR2(1);
3258 l_msg_count NUMBER;
3259 l_msg_data VARCHAR2(2000);
3260 l_first_adjustment_num NUMBER;
3261 l_last_adjustment_num NUMBER;
3262 l_current_adjustment_num NUMBER;
3263 l_ship_status_code VARCHAR2(30);
3264 l_allocation_basis_uom_class VARCHAR2(30);
3265 l_total_amt NUMBER;
3266 l_do_proportion VARCHAR2(1);
3267 l_from_parent_table_name_brk VARCHAR2(30);
3268 l_from_parent_table_id_brk NUMBER;
3269 --- SCM-051
3270 l_i_adj NUMBER;
3271 --- SCM-051
3272
3273 --
3274 -- Landed Cost Calculation engine is a process that captures the many amounts
3275 -- of a given Landed Cost Shipment and prorates them down at the level of
3276 -- Shipment Lines.
3277 -- At the end of this process, all Shipment Lines of all receiving
3278 -- items will have allocations coming from whatever is associated to their
3279 -- parent components, as the example below:
3280 --
3281 -- Shipment SH1 contains 2 lines:
3282 -- SL1 receiving item X, amount $100
3283 -- SL2 receiving item Y, amount $100
3284 --
3285 -- $50 Charge CH1 is associated to SH1, indicating that the amount should be
3286 -- prorated into all its lines (SL1 and SL2)
3287 --
3288 -- At the end of this process, Shipment Lines will have the following allocations:
3289 --
3290 -- SL1: Allocation 1: $25 from CH1
3291 -- Allocation 2: $100 from SL1 itself
3292 --
3293 -- SL2: Allocation 1: $25 from CH1
3294 -- Allocation 2: $100 from SL2 itself
3295 --
3296 -- At the highest level, the logic of this process is divided into 2 steps:
3297 -- STEP 1: Allocation of Associated Amounts (SL1 Allocation 1, SL2 Allocation 1)
3298 -- STEP 2: Allocation of Not Associated Amounts (SL1 Allocation 2, SL2 Allocation 2)
3299 --
3300
3301 BEGIN
3302
3303 INL_LOGGING_PVT.Log_BeginProc (p_module_name => g_module_name,
3304 p_procedure_name => l_program_name);
3305 --
3306 -- Standard Start of API savepoint
3307 --
3308 SAVEPOINT Run_Calculation_PVT;
3309
3310 --
3311 -- Initialize message list if p_init_msg_list is set to TRUE
3312 --
3313 IF FND_API.to_Boolean (p_init_msg_list) THEN
3314 FND_MSG_PUB.initialize;
3315 END IF;
3316
3317 --
3318 -- Standard call to check for call compatibility
3319 --
3320 IF NOT FND_API.Compatible_API_Call (
3321 l_api_version,
3322 p_api_version,
3323 l_program_name,
3324 G_PKG_NAME)
3325 THEN
3326 RAISE L_FND_EXC_UNEXPECTED_ERROR;
3327 END IF;
3328
3329 --
3330 -- Initialize return status to SUCCESS
3331 --
3332 x_return_status := L_FND_RET_STS_SUCCESS;
3333
3334
3335 --- SCM-051
3336 --
3337 -- Get Functional Currency, Last Adjustment and Shipment Status
3338 --
3339
3340 l_debug_info := 'Get Functional Currency, Last Adjustment and Shipment Status';
3341 INL_LOGGING_PVT.Log_Statement (
3342 p_module_name => g_module_name,
3343 p_procedure_name => l_program_name,
3344 p_debug_info => l_debug_info);
3345
3346 SELECT DISTINCT
3347 gl.currency_code,
3348 oh.adjustment_num,
3349 oh.ship_status_code
3350 INTO
3351 l_le_currency_code,
3352 l_last_adjustment_num,
3353 l_ship_status_code
3354 FROM gl_ledgers gl,
3355 xle_fp_ou_ledger_v l,
3356 inl_ship_headers_all oh
3357 WHERE gl.ledger_id = l.ledger_id
3358 AND l.legal_entity_id = oh.legal_entity_id
3359 AND oh.ship_header_id = p_ship_header_id;
3360
3361 INL_LOGGING_PVT.Log_Variable (
3362 p_module_name => g_module_name,
3363 p_procedure_name => l_program_name,
3364 p_var_name => 'l_le_currency_code',
3365 p_var_value => l_le_currency_code);
3366 INL_LOGGING_PVT.Log_Variable (
3367 p_module_name => g_module_name,
3368 p_procedure_name => l_program_name,
3369 p_var_name => 'l_last_adjustment_num',
3370 p_var_value => l_last_adjustment_num);
3371 INL_LOGGING_PVT.Log_Variable (
3372 p_module_name => g_module_name,
3373 p_procedure_name => l_program_name,
3374 p_var_name => 'l_ship_status_code',
3375 p_var_value => l_ship_status_code);
3376 --- SCM-051
3377
3378 --
3379 -- Get First Adjustment to Process
3380 --
3381
3382 --- SCM-051
3383 SELECT MAX(ABS(adjustment_num))
3384 --- SCM-051
3385 INTO l_first_adjustment_num
3386 FROM inl_allocations
3387 WHERE ship_header_id = p_ship_header_id;
3388
3389 INL_LOGGING_PVT.Log_Variable (
3390 p_module_name => g_module_name,
3391 p_procedure_name => l_program_name,
3392 p_var_name => 'l_first_adjustment_num',
3393 p_var_value => l_first_adjustment_num);
3394
3395 IF l_ship_status_code = 'COMPLETED' THEN
3396 ----Bug#10221931
3397 ----There was a charge/tax with association to more than one shipment, in this case,
3398 ----if any item actual impacts one of those shipments, all of them should be recalculated
3399 ----and a new adjustment number should be generated in order to send a new cost variation to
3400 ---- CST
3401 ----The "if" below represents the situation where the shipment is marked as pending_matching but
3402 ----there isn't a new line to be processed then l_first_adjustment_num = l_last_adjustment_num
3403
3404 --- SCM-051
3405 IF ABS(l_first_adjustment_num) = ABS(l_last_adjustment_num) THEN
3406 l_last_adjustment_num := ABS(l_last_adjustment_num) + 1;
3407 --- SCM-051
3408 UPDATE inl_ship_headers_all
3409 SET
3410 adjustment_num = l_last_adjustment_num
3411 WHERE ship_header_id = p_ship_header_id;
3412 END IF;
3413 ----Bug#10221931
3414 l_first_adjustment_num := l_first_adjustment_num + 1;
3415 ELSE
3416 l_first_adjustment_num := 0;
3417 END IF;
3418
3419 INL_LOGGING_PVT.Log_Variable (
3420 p_module_name => g_module_name,
3421 p_procedure_name => l_program_name,
3422 p_var_name => 'l_first_adjustment_num',
3423 p_var_value => l_first_adjustment_num);
3424
3425 INL_LOGGING_PVT.Log_Variable (
3426 p_module_name => g_module_name,
3427 p_procedure_name => l_program_name,
3428 p_var_name => 'l_last_adjustment_num',
3429 p_var_value => l_last_adjustment_num);
3430 ---- SCM-LCM-010
3431 ---- Loop to determine for what Adjustments we have to run the calculation
3432 ----
3433 BEGIN
3434 --- SCM-051
3435 --- FOR i IN l_first_adjustment_num..l_last_adjustment_num LOOP
3436 l_i_adj := l_first_adjustment_num;
3437 LOOP
3438 INL_LOGGING_PVT.Log_Variable (
3439 p_module_name => g_module_name,
3440 p_procedure_name => l_program_name,
3441 p_var_name => 'l_i_adj',
3442 p_var_value => l_i_adj);
3443 EXIT WHEN (ABS(l_i_adj) > ABS(l_last_adjustment_num));
3444 --- SCM-051
3445 /* --Bug#14081759 BEG
3446 SELECT NVL(MIN(x.adjustment_num), l_last_adjustment_num) --Bug#10221931
3447 INTO l_current_adjustment_num
3448 FROM (SELECT sl.adjustment_num adjustment_num
3449 FROM inl_ship_lines_all sl
3450 WHERE sl.ship_header_id = p_ship_header_id
3451 UNION ALL
3452 SELECT sl.adjustment_num adjustment_num
3453 FROM inl_ship_lines_all sl
3454 WHERE EXISTS (SELECT 'X'
3455 FROM inl_associations a
3456 WHERE a.ship_header_id = p_ship_header_id
3457 AND a.from_parent_table_name = 'INL_SHIP_LINES'
3458 AND a.from_parent_table_id = sl.ship_line_id)
3459 UNION ALL
3460 SELECT cl.adjustment_num adjustment_num
3461 FROM inl_charge_lines cl
3462 WHERE EXISTS (SELECT 'X'
3463 FROM inl_associations a
3464 WHERE a.ship_header_id = p_ship_header_id
3465 AND a.from_parent_table_name = 'INL_CHARGE_LINES'
3466 AND a.from_parent_table_id = cl.charge_line_id)
3467 UNION ALL
3468 SELECT tl.adjustment_num adjustment_num
3469 FROM inl_tax_lines tl
3470 WHERE EXISTS (SELECT 'X'
3471 FROM inl_associations a
3472 WHERE a.ship_header_id = p_ship_header_id
3473 AND a.from_parent_table_name = 'INL_TAX_LINES'
3474 AND a.from_parent_table_id = tl.tax_line_id)) x
3475 --- SCM-051
3476 WHERE ABS(x.adjustment_num) >= ABS(l_i_adj)
3477 AND ABS(x.adjustment_num) <= ABS(l_last_adjustment_num);
3478 --- SCM-051
3479 */
3480 SELECT
3481 MIN
3482 (LEAST
3483 (( SELECT NVL(MIN(ABS(sl.adjustment_num)),l_last_adjustment_num)
3484 FROM inl_ship_lines_all sl
3485 WHERE sl.ship_header_id = p_ship_header_id
3486 AND ABS(sl.adjustment_num) >= ABS(l_i_adj)
3487 AND ABS(sl.adjustment_num) <= ABS(l_last_adjustment_num)
3488 ),
3489 ( SELECT NVL(MIN(ABS(cl.adjustment_num)),l_last_adjustment_num)
3490 FROM inl_charge_lines cl
3491 WHERE a.from_parent_table_name = 'INL_CHARGE_LINES'
3492 AND ABS(cl.adjustment_num) >= ABS(l_i_adj)
3493 AND ABS(cl.adjustment_num) <= ABS(l_last_adjustment_num)
3494 START WITH cl.charge_line_id = a.from_parent_table_id
3495 CONNECT BY PRIOR cl.charge_line_id = cl.parent_charge_line_id
3496 ),
3497 ( SELECT NVL(MIN(ABS(tl.adjustment_num)),l_last_adjustment_num)
3498 FROM inl_tax_lines tl
3499 WHERE a.from_parent_table_name = 'INL_TAX_LINES'
3500 AND ABS(tl.adjustment_num) >= ABS(l_i_adj)
3501 AND ABS(tl.adjustment_num) <= ABS(l_last_adjustment_num)
3502 START WITH tl.tax_line_id = a.from_parent_table_id
3503 CONNECT BY PRIOR tl.tax_line_id = tl.parent_tax_line_id
3504 )
3505 )) adj_num
3506 INTO l_current_adjustment_num
3507 FROM
3508 inl_associations a,
3509 inl_ship_headers_all sh
3510 WHERE a.ship_header_id(+) = sh.ship_header_id
3511 AND sh.ship_header_id = p_ship_header_id
3512 ;
3513 --Bug#14081759 END
3514 INL_LOGGING_PVT.Log_Variable (
3515 p_module_name => g_module_name,
3516 p_procedure_name => l_program_name,
3517 p_var_name => 'l_current_adjustment_num',
3518 p_var_value => l_current_adjustment_num);
3519 --- SCM-051
3520 INL_LOGGING_PVT.Log_Variable (
3521 p_module_name => g_module_name,
3522 p_procedure_name => l_program_name,
3523 p_var_name => 'l_i_adj',
3524 p_var_value => l_i_adj);
3525 --- SCM-051
3526
3527 --- SCM-051
3528 IF ABS(l_i_adj) = ABS(l_current_adjustment_num) THEN
3529 --- SCM-051
3530 l_debug_info := 'l_first_adjustment_num';
3531 INL_LOGGING_PVT.Log_Variable (
3532 p_module_name => g_module_name,
3533 p_procedure_name => l_program_name,
3534 p_var_name => l_debug_info,
3535 p_var_value => l_first_adjustment_num);
3536 l_debug_info := 'l_last_adjustment_num';
3537 INL_LOGGING_PVT.Log_Variable (
3538 p_module_name => g_module_name,
3539 p_procedure_name => l_program_name,
3540 p_var_name => l_debug_info,
3541 p_var_value => l_last_adjustment_num);
3542 l_debug_info := 'l_current_adjustment_num';
3543 INL_LOGGING_PVT.Log_Variable (
3544 p_module_name => g_module_name,
3545 p_procedure_name => l_program_name,
3546 p_var_name => l_debug_info,
3547 p_var_value => l_current_adjustment_num);
3548
3549
3550 --
3551 -- For not adjusted shipments, check for Associations in Loop
3552 --
3553
3554 IF l_current_adjustment_num = 0 THEN
3555
3556 l_debug_info := 'Check for Associations in Loop';
3557 INL_LOGGING_PVT.Log_Statement (
3558 p_module_name => g_module_name,
3559 p_procedure_name => l_program_name,
3560 p_debug_info => l_debug_info);
3561
3562 OPEN assoc(l_current_adjustment_num
3563 );
3564 FETCH assoc BULK COLLECT INTO assoc_List; --Bulk implem
3565 CLOSE assoc;
3566
3567 l_debug_info := 'Fetched '||NVL(assoc_List.COUNT, 0)||' association(s).';
3568 INL_LOGGING_PVT.Log_Statement(
3569 p_module_name => g_module_name,
3570 p_procedure_name => l_program_name,
3571 p_debug_info => l_debug_info
3572 ) ;
3573 IF NVL(assoc_List.COUNT,0)>0
3574 THEN
3575 FOR iAssoc IN 1 .. assoc_List.COUNT --Bulk implem
3576 LOOP
3577
3578 IF InLoop_Association (
3579 assoc_List(iAssoc).ship_header_id,
3580 assoc_List(iAssoc).from_parent_table_name,
3581 assoc_List(iAssoc).from_parent_table_id,
3582 l_return_status)
3583 THEN
3584 FND_MESSAGE.SET_NAME('INL','INL_ERR_CHK_IN_LOOP_ASSOC');
3585 FND_MSG_PUB.Add;
3586 RAISE L_FND_EXC_ERROR;
3587 END IF;
3588
3589 -- If any errors happen abort API.
3590 IF l_return_status = L_FND_RET_STS_ERROR THEN
3591 RAISE L_FND_EXC_ERROR;
3592 ELSIF l_return_status = L_FND_RET_STS_UNEXP_ERROR THEN
3593 RAISE L_FND_EXC_UNEXPECTED_ERROR;
3594 END IF;
3595
3596 END LOOP;
3597 END IF;
3598
3599 END IF;
3600
3601 --
3602 -- Remove previous allocations
3603 --
3604
3605 l_debug_info := 'Remove previous allocations';
3606 INL_LOGGING_PVT.Log_Statement (
3607 p_module_name => g_module_name,
3608 p_procedure_name => l_program_name,
3609 p_debug_info => l_debug_info);
3610
3611 DELETE FROM INL_allocations
3612 WHERE ship_header_id = p_ship_header_id
3613 AND ABS(adjustment_num) = ABS(l_current_adjustment_num);
3614 --SCM-051
3615 l_debug_info:=l_debug_info||' => '||sql%ROWCOUNT;
3616 INL_LOGGING_PVT.Log_Statement (
3617 p_module_name => g_module_name,
3618 p_procedure_name => l_program_name,
3619 p_debug_info => l_debug_info);
3620 --SCM-051
3621 --
3622 -- STEP 1 - Allocation of Associated Amounts
3623 --
3624
3625 l_debug_info := 'STEP 1: Allocation of Associated Amounts';
3626 INL_LOGGING_PVT.Log_Statement (
3627 p_module_name => g_module_name,
3628 p_procedure_name => l_program_name,
3629 p_debug_info => l_debug_info);
3630
3631 l_from_parent_table_name_brk := '-1';
3632 l_from_parent_table_id_brk := -1;
3633
3634 OPEN assoc( l_current_adjustment_num
3635 );
3636 FETCH assoc BULK COLLECT INTO assoc_List; --Bulk implem
3637 CLOSE assoc;
3638
3639 l_debug_info := 'Fetched '||NVL(assoc_List.COUNT, 0)||' association(s).';
3640 INL_LOGGING_PVT.Log_Statement(
3641 p_module_name => g_module_name,
3642 p_procedure_name => l_program_name,
3643 p_debug_info => l_debug_info
3644 ) ;
3645 IF NVL(assoc_List.COUNT,0)>0
3646 THEN
3647 FOR iAssoc IN 1 .. assoc_List.COUNT --Bulk implem
3648 LOOP
3649
3650 l_debug_info := 'Fetching Associations cursor';
3651 INL_LOGGING_PVT.Log_Statement (
3652 p_module_name => g_module_name,
3653 p_procedure_name => l_program_name,
3654 p_debug_info => l_debug_info);
3655
3656 l_amount := 0;
3657 l_from_amount := 0;
3658 l_to_amount := 0;
3659
3660 l_debug_info := 'assoc_List(iAssoc).from_parent_table_name';
3661 INL_LOGGING_PVT.Log_Variable (
3662 p_module_name => g_module_name,
3663 p_procedure_name => l_program_name,
3664 p_var_name => l_debug_info,
3665 p_var_value => assoc_List(iAssoc).from_parent_table_name);
3666
3667 l_debug_info := 'assoc_List(iAssoc).from_parent_table_id';
3668 INL_LOGGING_PVT.Log_Variable (
3669 p_module_name => g_module_name,
3670 p_procedure_name => l_program_name,
3671 p_var_name => l_debug_info,
3672 p_var_value => TO_CHAR(assoc_List(iAssoc).from_parent_table_id));
3673
3674 l_debug_info := 'assoc_List(iAssoc).to_parent_table_name';
3675 INL_LOGGING_PVT.Log_Variable (
3676 p_module_name => g_module_name,
3677 p_procedure_name => l_program_name,
3678 p_var_name => l_debug_info,
3679 p_var_value => assoc_List(iAssoc).to_parent_table_name);
3680
3681 l_debug_info := 'assoc_List(iAssoc).to_parent_table_id';
3682 INL_LOGGING_PVT.Log_Variable (
3683 p_module_name => g_module_name,
3684 p_procedure_name => l_program_name,
3685 p_var_name => l_debug_info,
3686 p_var_value => TO_CHAR(assoc_List(iAssoc).to_parent_table_id));
3687
3688 l_debug_info := 'assoc_List(iAssoc).association_id';
3689 INL_LOGGING_PVT.Log_Variable (
3690 p_module_name => g_module_name,
3691 p_procedure_name => l_program_name,
3692 p_var_name => l_debug_info,
3693 p_var_value => assoc_List(iAssoc).association_id);
3694
3695
3696 IF assoc_List(iAssoc).from_parent_table_name = 'INL_CHARGE_LINES' THEN
3697 ---- Bug #9215498
3698 ---- SELECT Converted_Amt (charge_amt,
3699 ---- currency_code,
3700 ---- l_le_currency_code,
3701 ---- currency_conversion_type,
3702 ---- currency_conversion_date)
3703 SELECT DECODE(cl.landed_cost_flag,'Y',NVL(cl.charge_amt,0) * NVL(cl.currency_conversion_rate,1),0) --BUG#9719618
3704 INTO l_from_amount
3705 FROM inl_charge_lines cl
3706 WHERE cl.charge_line_id = assoc_List(iAssoc).from_parent_table_id;
3707 ELSIF assoc_List(iAssoc).from_parent_table_name = 'INL_TAX_LINES' THEN
3708 SELECT NVL(SUM(nrec_tax_amt),0)
3709 INTO l_from_amount
3710 FROM inl_tax_lines tl --BUG#8330505
3711 WHERE tl.tax_line_id = assoc_List(iAssoc).from_parent_table_id;
3712 ELSIF assoc_List(iAssoc).from_parent_table_name = 'INL_SHIP_HEADERS' THEN
3713 ----BUG#971 9618 SELECT SUM(NVL(ol.primary_qty,0)*NVL( ol.primary_unit_price * NVL(ol.currency_conversion_rate,1),0))
3714 SELECT SUM(NVL(ol.primary_qty,0)*NVL(DECODE(ol.landed_cost_flag,'Y',ol.primary_unit_price * NVL(ol.currency_conversion_rate,1),0),0)) --BUG#9719618
3715 INTO l_from_amount
3716 FROM inl_ship_lines_all ol
3717 WHERE ol.ship_header_id = assoc_List(iAssoc).from_parent_table_id;
3718 ELSIF assoc_List(iAssoc).from_parent_table_name = 'INL_SHIP_LINES' THEN
3719 ---- bug 7660824
3720 SELECT SUM(NVL(ol.primary_qty,0)*NVL(DECODE(ol.landed_cost_flag,'Y',ol.primary_unit_price * NVL(ol.currency_conversion_rate,1),0),0))
3721 INTO l_from_amount
3722 FROM inl_ship_lines_all ol
3723 WHERE ol.ship_line_id = assoc_List(iAssoc).from_parent_table_id;
3724 END IF;
3725
3726 l_debug_info := 'l_from_amount';
3727 INL_LOGGING_PVT.Log_Variable (
3728 p_module_name => g_module_name,
3729 p_procedure_name => l_program_name,
3730 p_var_name => l_debug_info,
3731 p_var_value => TO_CHAR(l_from_amount));
3732
3733 l_amount := l_from_amount;
3734
3735 l_debug_info := 'l_amount';
3736 INL_LOGGING_PVT.Log_Variable (
3737 p_module_name => g_module_name,
3738 p_procedure_name => l_program_name,
3739 p_var_name => l_debug_info,
3740 p_var_value => TO_CHAR(l_amount));
3741
3742 IF l_amount <> 0 THEN
3743 IF l_from_parent_table_name_brk <> assoc_List(iAssoc).from_parent_table_name OR
3744 l_from_parent_table_id_brk <> assoc_List(iAssoc).from_parent_table_id
3745 THEN
3746 l_from_parent_table_name_brk := assoc_List(iAssoc).from_parent_table_name;
3747 l_from_parent_table_id_brk := assoc_List(iAssoc).from_parent_table_id;
3748 l_debug_info := 'Call Get_TotalAmt';
3749 INL_LOGGING_PVT.Log_Statement (
3750 p_module_name => g_module_name,
3751 p_procedure_name => l_program_name,
3752 p_debug_info => l_debug_info);
3753 Get_TotalAmt (
3754 p_ship_header_id => assoc_List(iAssoc).ship_header_id,
3755 p_adjustment_num => l_current_adjustment_num,
3756 p_le_currency_code => l_le_currency_code,
3757 p_from_component_name => assoc_List(iAssoc).from_parent_table_name,
3758 p_from_component_id => assoc_List(iAssoc).from_parent_table_id,
3759 p_to_component_name => assoc_List(iAssoc).to_parent_table_name,
3760 p_to_component_id => assoc_List(iAssoc).to_parent_table_id,
3761 p_allocation_basis => assoc_List(iAssoc).allocation_basis,
3762 p_allocation_uom_code => assoc_List(iAssoc).allocation_uom_code,
3763 x_total_amt => l_total_amt,
3764 x_do_proportion => l_do_proportion,
3765 x_return_status => l_return_status);
3766 -- If any errors happen abort API.
3767 IF l_return_status = L_FND_RET_STS_ERROR THEN
3768 RAISE L_FND_EXC_ERROR;
3769 ELSIF l_return_status = L_FND_RET_STS_UNEXP_ERROR THEN
3770 RAISE L_FND_EXC_UNEXPECTED_ERROR;
3771 END IF;
3772 END IF;
3773 IF l_do_proportion = 'Y' THEN
3774 l_debug_info := 'Call Manage_Proportion';
3775 INL_LOGGING_PVT.Log_Statement (
3776 p_module_name => g_module_name,
3777 p_procedure_name => l_program_name,
3778 p_debug_info => l_debug_info);
3779 Manage_Proportion (
3780 p_ship_header_id => assoc_List(iAssoc).ship_header_id,
3781 p_adjustment_num => l_current_adjustment_num,
3782 p_le_currency_code => l_le_currency_code,
3783 p_from_component_name => assoc_List(iAssoc).from_parent_table_name,
3784 p_from_component_id => assoc_List(iAssoc).from_parent_table_id,
3785 p_to_component_name => assoc_List(iAssoc).to_parent_table_name,
3786 p_to_component_id => assoc_List(iAssoc).to_parent_table_id,
3787 p_allocation_basis => assoc_List(iAssoc).allocation_basis,
3788 p_allocation_uom_code => assoc_List(iAssoc).allocation_uom_code,
3789 p_total_amt => l_total_amt,
3790 o_factor => l_factor,
3791 x_return_status => l_return_status);
3792 -- If any errors happen abort API.
3793 IF l_return_status = L_FND_RET_STS_ERROR THEN
3794 RAISE L_FND_EXC_ERROR;
3795 ELSIF l_return_status = L_FND_RET_STS_UNEXP_ERROR THEN
3796 RAISE L_FND_EXC_UNEXPECTED_ERROR;
3797 END IF;
3798 ELSE
3799 l_factor := 1;
3800 END IF;
3801 ELSE
3802 l_factor := 0;
3803 END IF;
3804
3805 l_amount := l_amount * l_factor;
3806
3807 l_debug_info := 'Call to Control_Allocation';
3808 INL_LOGGING_PVT.Log_Statement (p_module_name => g_module_name,
3809 p_procedure_name => l_program_name,
3810 p_debug_info => l_debug_info);
3811
3812 Control_Allocation (
3813 p_ship_header_id => assoc_List(iAssoc).ship_header_id,
3814 p_le_currency_code => l_le_currency_code,
3815 p_association_id => assoc_List(iAssoc).association_id,
3816 p_allocation_basis => assoc_List(iAssoc).allocation_basis,
3817 p_allocation_uom_code => assoc_List(iAssoc).allocation_uom_code,
3818 p_amount => l_amount,
3819 p_from_component_name => assoc_List(iAssoc).from_parent_table_name,
3820 p_from_component_id => assoc_List(iAssoc).from_parent_table_id,
3821 p_to_component_name => assoc_List(iAssoc).to_parent_table_name,
3822 p_to_component_id => assoc_List(iAssoc).to_parent_table_id,
3823 p_adjustment_num => l_current_adjustment_num,
3824 x_return_status => l_return_status);
3825
3826 -- If any errors happen abort API.
3827 IF l_return_status = L_FND_RET_STS_ERROR THEN
3828 RAISE L_FND_EXC_ERROR;
3829 ELSIF l_return_status = L_FND_RET_STS_UNEXP_ERROR THEN
3830 RAISE L_FND_EXC_UNEXPECTED_ERROR;
3831 END IF;
3832
3833 END LOOP;
3834 END IF;
3835
3836 --
3837 -- STEP 2 - Allocation of Not Associated Amounts
3838 --
3839
3840 l_debug_info := 'STEP 2 - Allocation of Not Associated Amounts';
3841 INL_LOGGING_PVT.Log_Statement (
3842 p_module_name => g_module_name,
3843 p_procedure_name => l_program_name,
3844 p_debug_info => l_debug_info);
3845
3846 l_count1 := 0;
3847
3848 OPEN dist(l_current_adjustment_num
3849 );
3850 FETCH dist BULK COLLECT INTO dist_List; --Bulk implem
3851 CLOSE dist;
3852 l_debug_info := 'Fetched '||NVL(dist_List.COUNT, 0)||' line(s).';
3853 INL_LOGGING_PVT.Log_Statement(
3854 p_module_name => g_module_name,
3855 p_procedure_name => l_program_name,
3856 p_debug_info => l_debug_info
3857 ) ;
3858
3859 IF NVL(dist_List.COUNT,0)>0
3860 THEN
3861 FOR idist IN 1 .. dist_List.COUNT --Bulk implem
3862 LOOP
3863
3864 l_count1 := 0;
3865
3866 l_debug_info := 'Fetching Distributions cursor';
3867 INL_LOGGING_PVT.Log_Statement (
3868 p_module_name => g_module_name,
3869 p_procedure_name => l_program_name,
3870 p_debug_info => l_debug_info);
3871
3872 SELECT COUNT(*) + l_count1
3873 INTO l_count1
3874 FROM inl_associations
3875 WHERE from_parent_table_name = 'INL_SHIP_LINES'
3876 AND from_parent_table_id = dist_List(iDist).ship_line_id;
3877
3878 SELECT COUNT(*) + l_count1
3879 INTO l_count1
3880 FROM inl_associations
3881 WHERE from_parent_table_name = 'INL_SHIP_HEADERS'
3882 AND from_parent_table_id = dist_List(iDist).ship_header_id;
3883
3884 --Bug#9660084
3885 -- SELECT DECODE(l_count1,0,'Y','N') INTO l_lc_flag FROM DUAL;
3886 IF l_count1 = 0 THEN
3887 l_lc_flag := 'Y';
3888 ELSE
3889 l_lc_flag := 'N';
3890 END IF;
3891 --Bug#9660084
3892
3893 l_debug_info := 'dist_List('||iDist||').ship_line_id';
3894 INL_LOGGING_PVT.Log_Variable (
3895 p_module_name => g_module_name,
3896 p_procedure_name => l_program_name,
3897 p_var_name => l_debug_info,
3898 p_var_value => TO_CHAR(dist_List(iDist).ship_line_id));
3899
3900 INL_LOGGING_PVT.Log_Variable (
3901 p_module_name => g_module_name,
3902 p_procedure_name => l_program_name,
3903 p_var_name => 'l_lc_flag',
3904 p_var_value => l_lc_flag);
3905
3906 -- Get Inclusive Taxes
3907
3908 l_inclusive_tax_amt := 0;
3909
3910 SELECT SUM(NVL(al.allocation_amt,0))
3911 INTO l_inclusive_tax_amt
3912 FROM
3913 inl_tax_lines t, --BUG#8330505
3914 inl_associations assoc,
3915 inl_allocations al
3916 WHERE t.tax_amt_included_flag = 'Y'
3917 AND t.tax_line_id = (SELECT MAX(tl.tax_line_id)
3918 FROM inl_tax_lines tl
3919 --- SCM-051
3920 --- WHERE tl.adjustment_num <= l_current_adjustment_num
3921 WHERE ABS(tl.adjustment_num) <= ABS(l_current_adjustment_num)
3922 --- SCM-051
3923 START WITH tl.tax_line_id = assoc.from_parent_table_id
3924 CONNECT BY PRIOR tl.tax_line_id = tl.parent_tax_line_id)
3925 AND assoc.from_parent_table_name = 'INL_TAX_LINES'
3926 AND assoc.association_id = al.association_id
3927 AND al.ship_line_id = dist_List(iDist).ship_line_id
3928 AND ABS(al.adjustment_num) = ABS(l_current_adjustment_num);
3929
3930 Insert_Allocation (
3931 p_ship_header_id => p_ship_header_id,
3932 p_le_currency_code => l_le_currency_code,
3933 p_association_id => NULL,
3934 p_ship_line_id => dist_List(iDist).ship_line_id,
3935 p_amount => (NVL(dist_List(iDist).primary_qty,0)*NVL(dist_List(iDist).fc_primary_unit_price,0))-NVL(l_inclusive_tax_amt,0),
3936 p_from_component_name => 'INL_SHIP_DISTS',
3937 p_from_component_id => dist_List(iDist).ship_line_id,
3938 p_to_component_name => 'INL_SHIP_DISTS',
3939 p_to_component_id => dist_List(iDist).ship_line_id,
3940 p_lc_flag => l_lc_flag,
3941 p_adjustment_num => l_current_adjustment_num,
3942 x_return_status => l_return_status
3943 );
3944 -- If any errors happen abort API.
3945 IF l_return_status = L_FND_RET_STS_ERROR THEN
3946 RAISE L_FND_EXC_ERROR;
3947 ELSIF l_return_status = L_FND_RET_STS_UNEXP_ERROR THEN
3948 RAISE L_FND_EXC_UNEXPECTED_ERROR;
3949 END IF;
3950
3951 END LOOP;
3952 END IF;
3953
3954 OPEN charge(l_current_adjustment_num
3955 );
3956 FETCH charge BULK COLLECT INTO charge_List; --Bulk implem
3957 CLOSE charge;
3958 l_debug_info := 'Fetched '||NVL(charge_List.COUNT, 0)||' charge(s).';
3959 INL_LOGGING_PVT.Log_Statement(
3960 p_module_name => g_module_name,
3961 p_procedure_name => l_program_name,
3962 p_debug_info => l_debug_info
3963 ) ;
3964 IF NVL(charge_List.COUNT,0)>0
3965 THEN
3966 FOR icharge IN 1 .. charge_List.COUNT --Bulk implem
3967 LOOP
3968
3969 Insert_Allocation (
3970 p_ship_header_id => p_ship_header_id,
3971 p_le_currency_code => l_le_currency_code,
3972 p_association_id => NULL,
3973 p_ship_line_id => NULL,
3974 p_amount => charge_List(iCharge).charge_amt * NVL(charge_List(iCharge).currency_conversion_rate,1),
3975 p_from_component_name => 'INL_CHARGE_LINES',
3976 p_from_component_id => charge_List(iCharge).charge_line_id,
3977 p_to_component_name => 'INL_CHARGE_LINES',
3978 p_to_component_id => charge_List(iCharge).charge_line_id,
3979 p_lc_flag => 'N',
3980 p_adjustment_num => l_current_adjustment_num,
3981 x_return_status => l_return_status);
3982 -- If any errors happen abort API.
3983 IF l_return_status = L_FND_RET_STS_ERROR THEN
3984 RAISE L_FND_EXC_ERROR;
3985 ELSIF l_return_status = L_FND_RET_STS_UNEXP_ERROR THEN
3986 RAISE L_FND_EXC_UNEXPECTED_ERROR;
3987 END IF;
3988 END LOOP;
3989 END IF;
3990
3991 OPEN tax(l_current_adjustment_num
3992 );
3993 FETCH tax BULK COLLECT INTO tax_List; --Bulk implem
3994 CLOSE tax;
3995 l_debug_info := 'Fetched '||NVL(tax_List.COUNT, 0)||' tax(s).';
3996 INL_LOGGING_PVT.Log_Statement(
3997 p_module_name => g_module_name,
3998 p_procedure_name => l_program_name,
3999 p_debug_info => l_debug_info
4000 ) ;
4001
4002 IF NVL(tax_List.COUNT,0)>0
4003 THEN
4004 FOR itax IN 1 .. tax_List.COUNT --Bulk implem
4005 LOOP
4006
4007 Insert_Allocation (
4008 p_ship_header_id => p_ship_header_id,
4009 p_le_currency_code => l_le_currency_code,
4010 p_association_id => NULL,
4011 p_ship_line_id => NULL,
4012 p_amount => NVL(tax_List(iTax).tax_amt,0),
4013 p_from_component_name => 'INL_TAX_LINES',
4014 p_from_component_id => tax_List(iTax).tax_line_id,
4015 p_to_component_name => 'INL_TAX_LINES',
4016 p_to_component_id => tax_List(iTax).tax_line_id,
4017 p_lc_flag => 'N',
4018 p_adjustment_num => l_current_adjustment_num,
4019 x_return_status => l_return_status);
4020 -- If any errors happen abort API.
4021 IF l_return_status = L_FND_RET_STS_ERROR THEN
4022 RAISE L_FND_EXC_ERROR;
4023 ELSIF l_return_status = L_FND_RET_STS_UNEXP_ERROR THEN
4024 RAISE L_FND_EXC_UNEXPECTED_ERROR;
4025 END IF;
4026
4027 END LOOP;
4028 END IF;
4029
4030 -- bug #7674125
4031 -- Update Allocations with Parent_Allocation_Id
4032 Update_Allocation (
4033 p_ship_header_id => p_ship_header_id,
4034 p_adjustment_num => l_current_adjustment_num,
4035 x_return_status => x_return_status);
4036 -- If any errors happen abort API.
4037 IF l_return_status = L_FND_RET_STS_ERROR THEN
4038 RAISE L_FND_EXC_ERROR;
4039 ELSIF l_return_status = L_FND_RET_STS_UNEXP_ERROR THEN
4040 RAISE L_FND_EXC_UNEXPECTED_ERROR;
4041 END IF;
4042 END IF;-- Bug #8869735
4043 --- SCM-051
4044 l_i_adj := l_i_adj + 1;
4045 --- SCM-051
4046 END LOOP;
4047 END;
4048
4049 --
4050 -- Standard check for commit
4051 --
4052 IF FND_API.To_Boolean (p_commit) THEN
4053 COMMIT WORK;
4054 END IF;
4055
4056 -- Standard call to get message count and if count is 1, get message info.
4057 FND_MSG_PUB.Count_And_Get (
4058 p_encoded => L_FND_FALSE,
4059 p_count => x_msg_count,
4060 p_data => x_msg_data);
4061
4062 INL_LOGGING_PVT.Log_EndProc (
4063 p_module_name => g_module_name,
4064 p_procedure_name => l_program_name);
4065
4066 EXCEPTION
4067 WHEN L_FND_EXC_ERROR THEN
4068 INL_LOGGING_PVT.Log_ExpecError (
4069 p_module_name => g_module_name,
4070 p_procedure_name => l_program_name);
4071 ROLLBACK TO Run_Calculation_PVT;
4072 x_return_status := L_FND_RET_STS_ERROR;
4073 FND_MSG_PUB.Count_And_Get(
4074 p_encoded => L_FND_FALSE,
4075 p_count => x_msg_count,
4076 p_data => x_msg_data);
4077 WHEN L_FND_EXC_UNEXPECTED_ERROR THEN
4078 INL_LOGGING_PVT.Log_UnexpecError (
4079 p_module_name => g_module_name,
4080 p_procedure_name => l_program_name);
4081 ROLLBACK TO Run_Calculation_PVT;
4082 x_return_status := L_FND_RET_STS_UNEXP_ERROR;
4083 FND_MSG_PUB.Count_And_Get(
4084 p_encoded => L_FND_FALSE,
4085 p_count => x_msg_count,
4086 p_data => x_msg_data);
4087 WHEN OTHERS THEN
4088 INL_LOGGING_PVT.Log_UnexpecError (
4089 p_module_name => g_module_name,
4090 p_procedure_name => l_program_name);
4091 ROLLBACK TO Run_Calculation_PVT;
4092 x_return_status := L_FND_RET_STS_UNEXP_ERROR ;
4093 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
4094 THEN
4095 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,l_program_name);
4096 END IF;
4097 FND_MSG_PUB.Count_And_Get(
4098 p_encoded => L_FND_FALSE,
4099 p_count => x_msg_count,
4100 p_data => x_msg_data);
4101 END Run_Calculation;
4102
4103 END INL_LANDEDCOST_PVT;