[Home] [Help]
PACKAGE BODY: APPS.INL_LANDEDCOST_PVT
Source
1 PACKAGE BODY INL_LANDEDCOST_PVT AS
2 /* $Header: INLVLCOB.pls 120.9.12010000.11 2009/01/20 17:20:38 ebarbosa ship $ */
3
4 -- Utility name : InLoop_Association
5 -- Type : Private
6 -- Function : If there are Associations in loop, this function will return TRUE,
7 -- otherwise, will return FALSE.
8 --
9 -- Pre-reqs : None
10 -- Parameters :
11 -- IN : p_ship_header_id IN NUMBER
12 -- p_from_parent_table_name IN VARCHAR2
13 -- p_from_parent_table_id IN NUMBER
14 -- OUT : x_return_status OUT NOCOPY VARCHAR2
15 --
16 -- Version : Current version 1.0
17 --
18 -- Notes :
19
20 FUNCTION InLoop_Association (p_ship_header_id IN NUMBER,
21 p_from_parent_table_name IN VARCHAR2,
22 p_from_parent_table_id IN NUMBER,
23 x_return_status OUT NOCOPY VARCHAR2) RETURN BOOLEAN
24 IS
25 l_count NUMBER;
26 l_function_name CONSTANT VARCHAR2(30) := 'InLoop_Association';
27 l_debug_info VARCHAR2(240);
28 l_in_loop BOOLEAN;
29
30 BEGIN
31
32 INL_LOGGING_PVT.Log_BeginProc (p_module_name => g_module_name,
33 p_procedure_name => l_function_name);
34
35 --
36 -- Initialize return status to SUCCESS
37 --
38 x_return_status := FND_API.G_RET_STS_SUCCESS;
39
40 l_debug_info := 'p_ship_header_id';
41 INL_LOGGING_PVT.Log_Variable (p_module_name => g_module_name,
42 p_procedure_name => l_function_name,
43 p_var_name => l_debug_info,
44 p_var_value => TO_CHAR(p_ship_header_id));
45
46 l_debug_info := 'p_from_parent_table_name';
47 INL_LOGGING_PVT.Log_Variable (p_module_name => g_module_name,
48 p_procedure_name => l_function_name,
49 p_var_name => l_debug_info,
50 p_var_value => p_from_parent_table_name);
51
52 l_debug_info := 'p_from_parent_table_id';
53 INL_LOGGING_PVT.Log_Variable (p_module_name => g_module_name,
54 p_procedure_name => l_function_name,
55 p_var_name => l_debug_info,
56 p_var_value => TO_CHAR(p_from_parent_table_id));
57
58 DECLARE
59 LOOP_ERROR EXCEPTION;
60 PRAGMA EXCEPTION_INIT(LOOP_ERROR, -1436);
61 BEGIN
62 SELECT count(*)
63 INTO l_count
64 FROM inl_associations
65 WHERE ship_header_id = p_ship_header_id
66 START WITH FROM_PARENT_TABLE_NAME = P_from_parent_table_name AND FROM_PARENT_TABLE_ID = P_from_parent_table_id
67 CONNECT BY PRIOR TO_PARENT_TABLE_NAME = FROM_PARENT_TABLE_NAME AND PRIOR TO_PARENT_TABLE_ID = FROM_PARENT_TABLE_ID;
68 l_in_loop := FALSE;
69 EXCEPTION
70 WHEN LOOP_ERROR THEN
71 l_in_loop := TRUE;
72 END;
73
74 INL_LOGGING_PVT.Log_EndProc (p_module_name => g_module_name,
75 p_procedure_name => l_function_name);
76
77 IF l_in_loop THEN
78 RETURN TRUE;
79 ELSE
80 RETURN FALSE;
81 END IF;
82
83 EXCEPTION
84 WHEN FND_API.G_EXC_ERROR THEN
85 x_return_status := FND_API.G_RET_STS_ERROR;
86 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
87 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
88 WHEN OTHERS THEN
89 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
90 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
91 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,l_function_name);
92 END IF;
93
94 END InLoop_Association;
95
96 -- Utility name : Converted_Price
97 -- Type : Private
98 -- Function : Converts a given Unit Price based on the Unit of Measure
99 --
100 --
101 -- Pre-reqs : None
102 -- Parameters :
103 -- IN : p_unit_price IN NUMBER
104 -- p_organization_id IN NUMBER
105 -- p_inventory_item_id IN NUMBER
106 -- p_from_uom_code IN VARCHAR2
107 -- p_to_uom_code IN VARCHAR2
108 --
109 -- Version : Current version 1.0
110 --
111 -- Notes :
112
113 FUNCTION Converted_Price(p_unit_price IN NUMBER,
114 p_organization_id IN NUMBER,
115 p_inventory_item_id IN NUMBER,
116 p_from_uom_code IN VARCHAR2,
117 p_to_uom_code IN VARCHAR2) RETURN NUMBER
118 IS
119
120 l_function_name CONSTANT VARCHAR2(30) := 'Converted_Price';
121 l_debug_info VARCHAR2(240);
122 l_msg_data VARCHAR2(2000);
123 l_from_uom_class VARCHAR2(10);
124 l_to_uom_class VARCHAR2(10);
125 l_converted_price NUMBER;
126 l_primary_uom_code VARCHAR2(3);
127 l_primary_uom_class VARCHAR2(10);
128 l_concatenated_segments VARCHAR2(40);
129
130 BEGIN
131 INL_LOGGING_PVT.Log_BeginProc (p_module_name => g_module_name,
132 p_procedure_name => l_function_name);
133
134 l_debug_info := 'p_unit_price';
135 INL_LOGGING_PVT.Log_Variable (p_module_name => g_module_name,
136 p_procedure_name => l_function_name,
137 p_var_name => l_debug_info,
138 p_var_value => TO_CHAR(p_unit_price));
139
140 l_debug_info := 'p_organization_id';
141 INL_LOGGING_PVT.Log_Variable (p_module_name => g_module_name,
142 p_procedure_name => l_function_name,
143 p_var_name => l_debug_info,
144 p_var_value => TO_CHAR(p_organization_id));
145
146 l_debug_info := 'p_inventory_item_id';
147 INL_LOGGING_PVT.Log_Variable (p_module_name => g_module_name,
148 p_procedure_name => l_function_name,
149 p_var_name => l_debug_info,
150 p_var_value => TO_CHAR(p_inventory_item_id));
151
152 l_debug_info := 'p_from_uom_code';
153 INL_LOGGING_PVT.Log_Variable (p_module_name => g_module_name,
154 p_procedure_name => l_function_name,
155 p_var_name => l_debug_info,
156 p_var_value => p_from_uom_code);
157
158 l_debug_info := 'p_to_uom_code';
159 INL_LOGGING_PVT.Log_Variable (p_module_name => g_module_name,
160 p_procedure_name => l_function_name,
161 p_var_name => l_debug_info,
162 p_var_value => p_to_uom_code);
163 SELECT msi.primary_uom_code,
164 uom.uom_class,
165 msi.concatenated_segments
166 INTO l_primary_uom_code,
167 l_primary_uom_class,
168 l_concatenated_segments
169 FROM mtl_units_of_measure uom,
170 mtl_system_items_vl msi
171 WHERE uom.uom_code = msi.primary_uom_code
172 AND msi.organization_id = p_organization_id
173 AND msi.inventory_item_id = p_inventory_item_id;
174
175 l_debug_info := 'l_primary_uom_code';
176 INL_LOGGING_PVT.Log_Variable (p_module_name => g_module_name,
177 p_procedure_name => l_function_name,
178 p_var_name => l_debug_info,
179 p_var_value => l_primary_uom_code);
180 l_debug_info := 'l_primary_uom_class';
181 INL_LOGGING_PVT.Log_Variable (p_module_name => g_module_name,
182 p_procedure_name => l_function_name,
183 p_var_name => l_debug_info,
184 p_var_value => l_primary_uom_class);
185 l_debug_info := 'l_concatenated_segments';
186 INL_LOGGING_PVT.Log_Variable (p_module_name => g_module_name,
187 p_procedure_name => l_function_name,
188 p_var_name => l_debug_info,
189 p_var_value => l_concatenated_segments);
190
191 SELECT uom_class
192 INTO l_from_uom_class
193 FROM mtl_units_of_measure
194 WHERE uom_code = p_from_uom_code;
195
196 l_debug_info := 'l_from_uom_class';
197 INL_LOGGING_PVT.Log_Variable (p_module_name => g_module_name,
198 p_procedure_name => l_function_name,
199 p_var_name => l_debug_info,
200 p_var_value => l_from_uom_class);
201 SELECT uom_class
202 INTO l_to_uom_class
203 FROM mtl_units_of_measure
204 WHERE uom_code = p_to_uom_code;
205
206 l_debug_info := 'l_to_uom_class';
207 INL_LOGGING_PVT.Log_Variable (p_module_name => g_module_name,
208 p_procedure_name => l_function_name,
209 p_var_name => l_debug_info,
210 p_var_value => l_to_uom_class);
211
212 -- When from uom code is different from the primary uom
213 -- first get it converted to the primary
214 IF p_from_uom_code <> l_primary_uom_code THEN
215 SELECT 1/NVL(conversion_rate,0) * NVL(p_unit_price,0)
216 INTO l_converted_price
217 FROM mtl_uom_conversions_view
218 WHERE primary_uom_class = l_primary_uom_class
219 AND primary_uom_code = l_primary_uom_code
220 AND uom_class = l_from_uom_class
221 AND uom_code = p_from_uom_code
222 AND organization_id = p_organization_id
223 AND inventory_item_id = p_inventory_item_id;
224
225 SELECT NVL(conversion_rate,0) * NVL(l_converted_price,0)
226 INTO l_converted_price
227 FROM mtl_uom_conversions_view
228 WHERE primary_uom_class = l_primary_uom_class
229 AND primary_uom_code = l_primary_uom_code
230 AND uom_class = l_to_uom_class
231 AND uom_code = p_to_uom_code
232 AND organization_id = p_organization_id
233 AND inventory_item_id = p_inventory_item_id;
234
235 l_debug_info := 'l_converted_price';
236 INL_LOGGING_PVT.Log_Variable (p_module_name => g_module_name,
237 p_procedure_name => l_function_name,
238 p_var_name => l_debug_info,
239 p_var_value => l_converted_price);
240 ELSE
241 SELECT (SELECT (conversion_rate * p_unit_price) as conversion_rate
242 FROM mtl_uom_conversions_view
243 WHERE primary_uom_class = l_from_uom_class
244 AND primary_uom_code = p_from_uom_code
245 AND uom_class = l_to_uom_class
246 AND uom_code = p_to_uom_code
247 AND organization_id = p_organization_id
248 AND inventory_item_id = p_inventory_item_id
249 UNION
250 SELECT (1/conversion_rate * p_unit_price) as conversion_rate
251 FROM mtl_uom_conversions_view
252 WHERE primary_uom_class = l_to_uom_class
253 AND primary_uom_code = p_to_uom_code
254 AND uom_class = l_from_uom_class
255 AND uom_code = p_from_uom_code
256 AND organization_id = p_organization_id
257 AND inventory_item_id = p_inventory_item_id)
258 INTO l_converted_price
259 FROM dual;
260
261 l_debug_info := 'l_converted_price';
262 INL_LOGGING_PVT.Log_Variable (p_module_name => g_module_name,
263 p_procedure_name => l_function_name,
264 p_var_name => l_debug_info,
265 p_var_value => l_converted_price);
266 END IF;
267 RETURN l_converted_price;
268 EXCEPTION
269 WHEN OTHERS THEN
270 INL_LOGGING_PVT.Log_UnexpecError (p_module_name => g_module_name,
271 p_procedure_name => l_function_name);
272
273 FND_MESSAGE.SET_NAME('INL','INL_ERR_QTY_CONV');
274 FND_MESSAGE.SET_TOKEN('CONCATENATED_SEGMENTS',l_concatenated_segments);
275 FND_MESSAGE.SET_TOKEN('ALLOCATION_UOM_CODE',p_from_uom_code);
276 l_msg_data := FND_MESSAGE.GET;
277 RAISE_APPLICATION_ERROR (-20001, l_msg_data);
278 END Converted_Price;
279
280 -- Utility name : Converted_Qty
281 -- Type : Private
282 -- Function : Converts a given quantity, which can be either a primary quantity, or a volume or a weight, into
283 -- a given Unit Of Measure.
284 --
285 --
286 -- Pre-reqs : None
287 -- Parameters :
288 -- IN : p_organization_id IN NUMBER
289 -- p_inventory_item_id IN NUMBER
290 -- p_qty IN NUMBER
291 -- p_from_uom_code IN VARCHAR2
292 -- P_to_uom_code IN VARCHAR2
293 --
294 -- Version : Current version 1.0
295 --
296 -- Notes :
297 FUNCTION Converted_Qty (p_organization_id IN NUMBER,
298 p_inventory_item_id IN NUMBER,
299 p_qty IN NUMBER,
300 p_from_uom_code IN VARCHAR2,
301 P_to_uom_code IN VARCHAR2) RETURN NUMBER
302 IS
303 l_function_name CONSTANT VARCHAR2(30) := 'Converted_Qty';
304 l_debug_info VARCHAR2(240);
305 l_primary_uom_code VARCHAR2(3);
306 l_primary_uom_class VARCHAR2(10);
307 l_allocation_uom_class VARCHAR2(10);
308 l_to_allocation_uom_class VARCHAR2(10);
309 l_primary_qty NUMBER;
310 l_converted_qty NUMBER;
311 l_concatenated_segments VARCHAR2(240);
312 l_msg_data VARCHAR2(2000);
313
314 BEGIN
315 INL_LOGGING_PVT.Log_BeginProc (p_module_name => g_module_name,
316 p_procedure_name => l_function_name);
317
318 l_debug_info := 'p_organization_id';
319 INL_LOGGING_PVT.Log_Variable (p_module_name => g_module_name,
320 p_procedure_name => l_function_name,
321 p_var_name => l_debug_info,
322 p_var_value => TO_CHAR(p_organization_id));
323
324 l_debug_info := 'p_inventory_item_id';
325 INL_LOGGING_PVT.Log_Variable (p_module_name => g_module_name,
326 p_procedure_name => l_function_name,
327 p_var_name => l_debug_info,
328 p_var_value => TO_CHAR(p_inventory_item_id));
329
330 l_debug_info := 'p_qty';
331 INL_LOGGING_PVT.Log_Variable (p_module_name => g_module_name,
332 p_procedure_name => l_function_name,
333 p_var_name => l_debug_info,
334 p_var_value => TO_CHAR(p_qty));
335
336 l_debug_info := 'p_from_uom_code';
337 INL_LOGGING_PVT.Log_Variable (p_module_name => g_module_name,
338 p_procedure_name => l_function_name,
339 p_var_name => l_debug_info,
340 p_var_value => p_from_uom_code);
341
342 l_debug_info := 'p_to_uom_code';
343 INL_LOGGING_PVT.Log_Variable (p_module_name => g_module_name,
344 p_procedure_name => l_function_name,
345 p_var_name => l_debug_info,
346 p_var_value => p_to_uom_code);
347
348 SELECT msi.primary_uom_code,
349 uom.uom_class,
350 msi.concatenated_segments
351 INTO l_primary_uom_code,
352 l_primary_uom_class,
353 l_concatenated_segments
354 FROM mtl_units_of_measure uom,
355 mtl_system_items_vl msi
356 WHERE uom.uom_code = msi.primary_uom_code
357 AND msi.organization_id = p_organization_id
358 AND msi.inventory_item_id = p_inventory_item_id;
359
360 l_debug_info := 'l_primary_uom_code';
361 INL_LOGGING_PVT.Log_Variable (p_module_name => g_module_name,
362 p_procedure_name => l_function_name,
363 p_var_name => l_debug_info,
364 p_var_value => l_primary_uom_code);
365
366 l_debug_info := 'l_primary_uom_class';
367 INL_LOGGING_PVT.Log_Variable (p_module_name => g_module_name,
368 p_procedure_name => l_function_name,
369 p_var_name => l_debug_info,
370 p_var_value => l_primary_uom_class);
371
372 SELECT uom_class
373 INTO l_allocation_uom_class
374 FROM mtl_units_of_measure
375 WHERE uom_code = p_from_uom_code;
376
377 l_debug_info := 'l_allocation_uom_class';
378 INL_LOGGING_PVT.Log_Variable (p_module_name => g_module_name,
379 p_procedure_name => l_function_name,
380 p_var_name => l_debug_info,
381 p_var_value => l_allocation_uom_class);
382
383 SELECT NVL(conversion_rate,0) * NVL(p_qty,0)
384 INTO l_primary_qty
385 FROM mtl_uom_conversions_view
386 WHERE primary_uom_class = l_primary_uom_class
387 AND primary_uom_code = l_primary_uom_code
388 AND uom_class = l_allocation_uom_class
389 AND uom_code = p_from_uom_code
390 AND inventory_item_id = p_inventory_item_id
391 AND organization_id = p_organization_id;
392
393 l_debug_info := 'l_primary_qty';
394 INL_LOGGING_PVT.Log_Variable (p_module_name => g_module_name,
395 p_procedure_name => l_function_name,
396 p_var_name => l_debug_info,
397 p_var_value => TO_CHAR(l_primary_qty));
398
399 SELECT uom_class
400 INTO l_to_allocation_uom_class
401 FROM mtl_units_of_measure
402 WHERE uom_code = P_to_uom_code;
403
404 l_debug_info := 'l_to_allocation_uom_class';
405 INL_LOGGING_PVT.Log_Variable (p_module_name => g_module_name,
406 p_procedure_name => l_function_name,
407 p_var_name => l_debug_info,
408 p_var_value => l_to_allocation_uom_class);
409
410 SELECT 1/NVL(conversion_rate,0) * NVL(l_primary_qty,0)
411 INTO l_converted_qty
412 FROM mtl_uom_conversions_view
413 WHERE primary_uom_class = l_primary_uom_class
414 AND primary_uom_code = l_primary_uom_code
415 AND uom_class = l_to_allocation_uom_class
416 AND uom_code = p_to_uom_code
417 AND inventory_item_id = p_inventory_item_id
418 AND organization_id = p_organization_id;
419
420
421 l_debug_info := 'l_converted_qty';
422 INL_LOGGING_PVT.Log_Variable (p_module_name => g_module_name,
423 p_procedure_name => l_function_name,
424 p_var_name => l_debug_info,
425 p_var_value => TO_CHAR(l_converted_qty));
426
427 INL_LOGGING_PVT.Log_EndProc (p_module_name => g_module_name,
428 p_procedure_name => l_function_name);
429
430 RETURN l_converted_qty;
431 EXCEPTION
432 WHEN OTHERS THEN
433 INL_LOGGING_PVT.Log_UnexpecError (p_module_name => g_module_name,
434 p_procedure_name => l_function_name);
435
436 FND_MESSAGE.SET_NAME('INL','INL_ERR_QTY_CONV');
437 FND_MESSAGE.SET_TOKEN('CONCATENATED_SEGMENTS',l_concatenated_segments);
438 FND_MESSAGE.SET_TOKEN('ALLOCATION_UOM_CODE',p_from_uom_code);
439 l_msg_data := FND_MESSAGE.GET;
440 RAISE_APPLICATION_ERROR (-20001, l_msg_data);
441
442 END Converted_Qty;
443
444 -- Utility name : Converted_Amt
445 -- Type : Private
446 -- Function : Converts a given amount from one currency to another
447 --
448 -- Pre-reqs : None
449 -- Parameters :
450 -- IN : p_amt IN NUMBER
451 -- p_from_currency_code IN VARCHAR2
452 -- p_to_currency_code IN VARCHAR2
453 -- p_currency_conversion_type IN VARCHAR2
454 -- p_currency_conversion_date IN DATE
455 --
456 -- Version : Current version 1.0
457 --
458 -- Notes :
459 FUNCTION Converted_Amt (p_amt IN NUMBER,
460 p_from_currency_code IN VARCHAR2,
461 p_to_currency_code IN VARCHAR2,
462 p_currency_conversion_type IN VARCHAR2,
463 p_currency_conversion_date IN DATE) RETURN NUMBER
464
465 IS
466 l_function_name CONSTANT VARCHAR2(30) := 'Converted_Amt';
467 l_debug_info VARCHAR2(240);
468 l_msg_data VARCHAR2(2000);
469 l_converted_amt NUMBER;
470 l_conversion_rate NUMBER;
471 BEGIN
472
473 l_Converted_amt := p_amt;
474 INL_LOGGING_PVT.Log_BeginProc (p_module_name => g_module_name,
475 p_procedure_name => l_function_name);
476
477 l_converted_amt := Converted_Amt(p_amt => p_amt,
478 p_from_currency_code => p_from_currency_code,
479 p_to_currency_code => p_to_currency_code,
480 p_currency_conversion_type => p_currency_conversion_type,
481 p_currency_conversion_date => p_currency_conversion_date,
482 x_currency_conversion_rate => l_conversion_rate);
483
484 RETURN l_Converted_Amt;
485
486 EXCEPTION
487 WHEN OTHERS THEN
488 INL_LOGGING_PVT.Log_UnexpecError (p_module_name => g_module_name,
489 p_procedure_name => l_function_name);
490 FND_MESSAGE.SET_NAME('INL','INL_ERR_AMT_CONV');
491 FND_MESSAGE.SET_TOKEN('FROM_CURRENCY_CODE',p_from_currency_code);
492 FND_MESSAGE.SET_TOKEN('TO_CURRENCY_CODE',p_to_currency_code);
493 FND_MESSAGE.SET_TOKEN('CURRENCY_CONVERSION_TYPE',p_currency_conversion_type);
494 FND_MESSAGE.SET_TOKEN('CURRENCY_CONVERSTION_DATE',p_currency_conversion_date);
495 l_msg_data := FND_MESSAGE.GET;
496 RAISE_APPLICATION_ERROR (-20002, l_msg_data);
497 END Converted_Amt;
498
499 -- Utility name : Converted_Amt
500 -- Type : Private
501 -- Function : Converts a given amount from one currency to another
502 --
503 -- Pre-reqs : None
504 -- Parameters :
505 -- IN : p_amt IN NUMBER
506 -- p_from_currency_code IN VARCHAR2
507 -- p_to_currency_code IN VARCHAR2
508 -- p_currency_conversion_type IN VARCHAR2
509 -- p_currency_conversion_date IN DATE
510 -- x_currency_conversion_rate OUT NOCOPY NUMBER
511 --
512 -- Version : Current version 1.0
513 --
514 -- Notes :
515 FUNCTION Converted_Amt (p_amt IN NUMBER,
516 p_from_currency_code IN VARCHAR2,
517 p_to_currency_code IN VARCHAR2,
518 p_currency_conversion_type IN VARCHAR2,
519 p_currency_conversion_date IN DATE,
520 x_currency_conversion_rate OUT NOCOPY NUMBER) RETURN NUMBER
521
522 IS
523 l_function_name CONSTANT VARCHAR2(30) := 'Converted_Amt';
524 l_debug_info VARCHAR2(240);
525 l_msg_data VARCHAR2(2000);
526 l_converted_amt NUMBER;
527 l_conversion_rate NUMBER;
528
529 BEGIN
530 INL_LOGGING_PVT.Log_BeginProc (p_module_name => g_module_name,
531 p_procedure_name => l_function_name);
532
533 l_Converted_Amt := p_amt;
534 IF p_from_currency_code <> p_to_currency_code THEN
535 SELECT NVL(p_amt,0) * NVL(conversion_rate,0),
536 conversion_rate
537 INTO l_converted_amt,
538 x_currency_conversion_rate
539 FROM gl_daily_rates
540 WHERE from_currency = p_from_currency_code
541 AND to_currency = p_to_currency_code
542 AND conversion_type = p_currency_conversion_type
543 AND TRUNC(conversion_date) = TRUNC(p_currency_conversion_date);
544 END IF;
545
546 INL_LOGGING_PVT.Log_EndProc (p_module_name => g_module_name,
547 p_procedure_name => l_function_name);
548
549 RETURN l_Converted_Amt;
550 EXCEPTION
551 WHEN OTHERS THEN
552 INL_LOGGING_PVT.Log_UnexpecError (p_module_name => g_module_name,
553 p_procedure_name => l_function_name);
554 FND_MESSAGE.SET_NAME('INL','INL_ERR_AMT_CONV');
555 FND_MESSAGE.SET_TOKEN('FROM_CURRENCY_CODE',p_from_currency_code);
556 FND_MESSAGE.SET_TOKEN('TO_CURRENCY_CODE',p_to_currency_code);
557 FND_MESSAGE.SET_TOKEN('CURRENCY_CONVERSION_TYPE',p_currency_conversion_type);
558 FND_MESSAGE.SET_TOKEN('CURRENCY_CONVERSTION_DATE',p_currency_conversion_date);
559 l_msg_data := FND_MESSAGE.GET;
560 RAISE_APPLICATION_ERROR (-20002, l_msg_data);
561 END Converted_Amt;
562
563
564 -- Utility name : Manage_Proportion
565 -- Type : Private
566 -- Function :
567 -- Pre-reqs : None
568 -- Parameters :
569 -- IN : p_ship_header_id IN NUMBER
570 -- p_le_currency_code IN VARCHAR2
571 -- p_from_component_name IN VARCHAR2
572 -- p_from_component_id IN NUMBER
573 -- p_to_component_name IN VARCHAR2
574 -- p_to_component_id IN NUMBER
575 -- p_allocation_basis IN VARCHAR2
576 -- p_allocation_uom_code IN VARCHAR2
577 -- OUT : o_factor OUT NOCOPY NUMBER
578 -- x_return_status OUT NOCOPY VARCHAR2
579 --
580 -- Version : Current version 1.0
581 --
582 -- Notes :
583 PROCEDURE Manage_Proportion (p_ship_header_id IN NUMBER,
584 p_le_currency_code IN VARCHAR2,
585 p_from_component_name IN VARCHAR2,
586 p_from_component_id IN NUMBER,
587 p_to_component_name IN VARCHAR2,
588 p_to_component_id IN NUMBER,
589 p_allocation_basis IN VARCHAR2,
590 p_allocation_uom_code IN VARCHAR2,
591 o_factor OUT NOCOPY NUMBER,
592 x_return_status OUT NOCOPY VARCHAR2)
593 IS
594 l_total_amt NUMBER;
595 l_to_component_amt NUMBER;
596 l_allocation_basis VARCHAR2(30);
597 l_debug_info VARCHAR2(240);
598 l_procedure_name CONSTANT VARCHAR2(30) := 'Manage_Proportion';
599 l_inv_org_id NUMBER;
600 l_return_status VARCHAR2(1);
601
602 CURSOR assoc IS
603 SELECT association_id,
604 to_parent_table_name,
605 to_parent_table_id
606 FROM inl_adj_associations_v
607 WHERE from_parent_table_name = p_from_component_name
608 AND from_parent_table_id = p_from_component_id
609 ORDER BY association_id;
610 rec_assoc assoc%ROWTYPE;
611
612 --
613 -- Obtains the Proportion Factor to be applied to the allocating amount.
614 -- This is used when one component is associated to more than one component, e.g.
615 -- One charge amount associated to two shipment lines. In cases like that,
616 -- each shipment line must proportionally receive its part from the charge amount,
617 -- according to the allocation basis of the Shipment Type.
618 --
619 -- CH1 $10 associated to SL1 and SL2, where SL1 = $40 and SL2 = $60
620 -- If the allocation basis of the Association = 'VALUE', then
621 -- the factor for SL1 = .4 and the factor for SL2 = .6, meaning that
622 -- SL1 will receive $4 from the $10 charge, and SL2 will receive $6 from the $10 charge
623 --
624 -- This routine is also called for prorating a final amount that gets to a
625 -- landed cost component to its corresponding shipment lines.
626
627 BEGIN
628
629 INL_LOGGING_PVT.Log_BeginProc (p_module_name => g_module_name,
630 p_procedure_name => l_procedure_name);
631
632 --
633 -- Initialize return status to SUCCESS
634 --
635
636 x_return_status := FND_API.G_RET_STS_SUCCESS;
637
638 l_allocation_basis := p_allocation_basis;
639
640 --
641 -- Get Inventory Organization. It will be used when getting Item info.
642 --
643 SELECT organization_id
644 INTO l_inv_org_id
645 FROM inl_ship_headers
646 WHERE ship_header_id = p_ship_header_id;
647
648 l_total_amt := 0;
649
650 l_debug_info := 'p_ship_header_id';
651 INL_LOGGING_PVT.Log_Variable (p_module_name => g_module_name,
652 p_procedure_name => l_procedure_name,
653 p_var_name => l_debug_info,
657 INL_LOGGING_PVT.Log_Variable (p_module_name => g_module_name,
654 p_var_value => TO_CHAR(p_ship_header_id));
655
656 l_debug_info := 'p_from_component_name';
658 p_procedure_name => l_procedure_name,
659 p_var_name => l_debug_info,
660 p_var_value => p_from_component_name);
661
662 l_debug_info := 'p_from_component_id';
663 INL_LOGGING_PVT.Log_Variable (p_module_name => g_module_name,
664 p_procedure_name => l_procedure_name,
665 p_var_name => l_debug_info,
666 p_var_value => TO_CHAR(p_from_component_id));
667
668 l_debug_info := 'p_to_component_name';
669 INL_LOGGING_PVT.Log_Variable (p_module_name => g_module_name,
670 p_procedure_name => l_procedure_name,
671 p_var_name => l_debug_info,
672 p_var_value => p_to_component_name);
673
674 l_debug_info := 'p_to_component_id';
675 INL_LOGGING_PVT.Log_Variable (p_module_name => g_module_name,
676 p_procedure_name => l_procedure_name,
677 p_var_name => l_debug_info,
678 p_var_value => TO_CHAR(p_to_component_id));
679
680 l_debug_info := 'p_allocation_basis';
681 INL_LOGGING_PVT.Log_Variable (p_module_name => g_module_name,
682 p_procedure_name => l_procedure_name,
683 p_var_name => l_debug_info,
684 p_var_value => TO_CHAR(p_allocation_basis));
685
686 l_debug_info := 'p_allocation_uom_code';
687 INL_LOGGING_PVT.Log_Variable (p_module_name => g_module_name,
688 p_procedure_name => l_procedure_name,
689 p_var_name => l_debug_info,
690 p_var_value => TO_CHAR(p_allocation_uom_code));
691
692 l_debug_info := 'p_to_component_name';
693 INL_LOGGING_PVT.Log_Variable (p_module_name => g_module_name,
694 p_procedure_name => l_procedure_name,
695 p_var_name => l_debug_info,
696 p_var_value => p_to_component_name);
697
698 IF p_to_component_name <> 'INL_SHIP_DISTS' THEN
699 --
700 -- This is for getting the amount proportional to the
701 -- "to component", when the "from component" is associated to more than 1 component.
702 --
703 OPEN assoc;
704 LOOP
705 FETCH assoc INTO rec_assoc;
706 EXIT WHEN assoc%NOTFOUND;
707
708 IF rec_assoc.to_parent_table_name = 'INL_SHIP_HEADERS' THEN
709 SELECT SUM(DECODE(l_allocation_basis,'VALUE',NVL(NVL(ol.primary_qty,0)*NVL(ol.fc_primary_unit_price,0),0),
710 Converted_Qty (l_inv_org_id,
711 ol.inventory_item_id,
712 NVL(ol.primary_qty,0),
713 ol.primary_uom_code,
714 p_allocation_uom_code))) + l_total_amt
715 INTO l_total_amt
716 FROM inl_adj_ship_lines_v ol
717 WHERE ol.ship_header_id = rec_assoc.to_parent_table_id;
718
719 ELSIF rec_assoc.to_parent_table_name = 'INL_SHIP_LINE_GROUPS' THEN
720 SELECT SUM(DECODE(l_allocation_basis,'VALUE',NVL(NVL(ol.primary_qty,0)*NVL(ol.fc_primary_unit_price,0),0),
721 Converted_Qty (l_inv_org_id,
722 ol.inventory_item_id,
723 NVL(ol.primary_qty,0),
724 -- Bug #7674125
725 ol.primary_uom_code,
726 p_allocation_uom_code))) + l_total_amt
727 INTO l_total_amt
728 FROM inl_adj_ship_lines_v ol
729 WHERE ol.ship_line_group_id = rec_assoc.to_parent_table_id;
730
731 ELSIF rec_assoc.to_parent_table_name = 'INL_SHIP_LINES' THEN
732 SELECT SUM(DECODE(l_allocation_basis,'VALUE',NVL(NVL(ol.primary_qty,0)*NVL(ol.fc_primary_unit_price,0),0),
733 Converted_Qty (l_inv_org_id,
734 ol.inventory_item_id,
735 NVL(ol.primary_qty,0),
736 -- Bug #7674125
737 ol.primary_uom_code,
738 p_allocation_uom_code))) + l_total_amt
739 INTO l_total_amt
740 FROM inl_adj_ship_lines_v ol
741 WHERE ol.ship_line_id = rec_assoc.to_parent_table_id;
742
743 ELSIF rec_assoc.to_parent_table_name = 'INL_CHARGE_LINES' THEN
744 IF l_allocation_basis = 'VALUE' THEN
745 SELECT SUM(Converted_Amt (NVL(charge_amt,0),
746 currency_code,
747 p_le_currency_code,
748 currency_conversion_type,
749 currency_conversion_date)) + l_total_amt
750 INTO l_total_amt
751 FROM INL_adj_charge_lines_v
752 WHERE charge_line_id = rec_assoc.to_parent_table_id;
753
754 -- If any errors happen abort API.
755 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
759 END IF;
756 RAISE FND_API.G_EXC_ERROR;
757 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
758 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
760
761 ELSE
762 FND_MESSAGE.SET_NAME('INL','INL_ERR_CHK_CH_ALLOC');
763 FND_MSG_PUB.Add;
764 RAISE FND_API.G_EXC_ERROR;
765
766 END IF;
767 ELSIF rec_assoc.to_parent_table_name = 'INL_TAX_LINES' THEN
768 IF l_allocation_basis = 'VALUE' THEN
769 SELECT nvl(SUM(nrec_tax_amt),0) + l_total_amt
770 INTO l_total_amt
771 FROM INL_tax_lines
772 WHERE tax_line_id = rec_assoc.to_parent_table_id;
773 ELSE
774 FND_MESSAGE.SET_NAME('INL','INL_ERR_TX_ALLOC');
775 FND_MSG_PUB.Add;
776 RAISE FND_API.G_EXC_ERROR;
777 END IF;
778 END IF;
779 END LOOP;
780 CLOSE assoc;
781
782 l_debug_info := 'NOT INL_SHIP_DISTS l_total_amt';
783 INL_LOGGING_PVT.Log_Variable (p_module_name => g_module_name,
784 p_procedure_name => l_procedure_name,
785 p_var_name => l_debug_info,
786 p_var_value => TO_CHAR(l_total_amt));
787
788 ELSE
789 --
790 -- This is for prorating a final amount that gets to a
791 -- landed cost component to its corresponding shipment lines.
792 --
793 SELECT SUM(DECODE(l_allocation_basis,'VALUE',NVL(NVL(ol.primary_qty,0)*NVL(ol.fc_primary_unit_price,0),0),
794 Converted_Qty (l_inv_org_id,
795 ol.inventory_item_id,
796 NVL(ol.primary_qty,0),
797 ol.primary_uom_code,
798 p_allocation_uom_code))) + l_total_amt
799 INTO l_total_amt
800 FROM inl_adj_ship_lines_v ol
801 WHERE ol.ship_header_id = DECODE(p_from_component_name,'INL_SHIP_HEADERS',p_from_component_id,ol.ship_header_id)
802 --- bug 7654293
803 AND ol.ship_line_group_id = DECODE(p_from_component_name,'INL_SHIP_LINE_GROUPS',p_from_component_id,ol.ship_line_group_id)
804 AND ol.ship_line_id = DECODE(p_from_component_name,'INL_SHIP_LINES',p_from_component_id,ol.ship_line_id)
805 AND ol.ship_header_id = p_ship_header_id;
806 l_debug_info := 'INL_SHIP_DISTS l_total_amt';
807 INL_LOGGING_PVT.Log_Variable (p_module_name => g_module_name,
808 p_procedure_name => l_procedure_name,
809 p_var_name => l_debug_info,
810 p_var_value => TO_CHAR(l_total_amt));
811
812 END IF;
813 IF p_to_component_name = 'INL_SHIP_HEADERS' THEN
814 SELECT SUM(DECODE(l_allocation_basis,'VALUE',NVL(NVL(ol.primary_qty,0)*NVL(ol.fc_primary_unit_price,0),0),
815 Converted_Qty (l_inv_org_id,
816 ol.inventory_item_id,
817 NVL(ol.primary_qty,0),
818 ol.primary_uom_code,
819 p_allocation_uom_code)))
820 INTO l_to_component_amt
821 FROM inl_adj_ship_lines_v ol
822 WHERE ol.ship_header_id = p_to_component_id;
823
824 ELSIF p_to_component_name = 'INL_SHIP_LINE_GROUPS' THEN
825 SELECT SUM(DECODE(l_allocation_basis,'VALUE',NVL(NVL(ol.primary_qty,0)*NVL(ol.fc_primary_unit_price,0),0),
826 Converted_Qty (l_inv_org_id,
827 ol.inventory_item_id,
828 NVL(ol.primary_qty,0),
829 ol.primary_uom_code,
830 p_allocation_uom_code)))
831 INTO l_to_component_amt
832 FROM inl_adj_ship_lines_v ol
833 WHERE ol.ship_line_group_id = p_to_component_id;
834
835 ELSIF p_to_component_name = 'INL_SHIP_LINES' THEN
836 SELECT SUM(DECODE(l_allocation_basis,'VALUE',NVL(NVL(ol.primary_qty,0)*NVL(ol.fc_primary_unit_price,0),0),
837 Converted_Qty (l_inv_org_id,
838 ol.inventory_item_id,
839 NVL(ol.primary_qty,0),
840 ol.primary_uom_code,
841 p_allocation_uom_code)))
842 INTO l_to_component_amt
843 FROM inl_adj_ship_lines_v ol
844 WHERE ol.ship_line_id = p_to_component_id;
845 ELSIF p_to_component_name = 'INL_SHIP_DISTS' THEN
846 SELECT SUM(DECODE(l_allocation_basis,'VALUE',NVL(NVL(ol.primary_qty,0)*NVL(ol.fc_primary_unit_price,0),0),
847 Converted_Qty (l_inv_org_id,
848 ol.inventory_item_id,
849 NVL(ol.primary_qty,0),
850 ol.primary_uom_code,
851 p_allocation_uom_code)))
852 INTO l_to_component_amt
853 FROM inl_adj_ship_lines_v ol
854 WHERE ol.ship_line_id = p_to_component_id;
855 ELSIF p_to_component_name = 'INL_CHARGE_LINES' THEN
856 IF l_allocation_basis = 'VALUE' THEN
857 SELECT SUM(Converted_Amt (NVL(charge_amt,0),
858 currency_code,
859 P_le_currency_code,
863 FROM inl_adj_charge_lines_v
860 currency_conversion_type,
861 currency_conversion_date))
862 INTO l_to_component_amt
864 WHERE charge_line_id = p_to_component_id;
865 ELSE
866 FND_MESSAGE.SET_NAME('INL','INL_ERR_CHK_CH_ALLOC');
867 FND_MSG_PUB.Add;
868 RAISE FND_API.G_EXC_ERROR;
869
870 END IF;
871 ELSIF p_to_component_name = 'INL_TAX_LINES' THEN
872 IF l_allocation_basis = 'VALUE' THEN
873 SELECT NVL(SUM(nrec_tax_amt),0)
874 INTO l_to_component_amt
875 FROM INL_tax_lines
876 WHERE tax_line_id = p_to_component_id;
877 ELSE
878 FND_MESSAGE.SET_NAME('INL','INL_ERR_TX_ALLOC');
879 FND_MSG_PUB.Add;
880 RAISE FND_API.G_EXC_ERROR;
881
882 END IF;
883 END IF;
884
885 l_debug_info := 'l_to_component_amt';
886 INL_LOGGING_PVT.Log_Variable (p_module_name => g_module_name,
887 p_procedure_name => l_procedure_name,
888 p_var_name => l_debug_info,
889 p_var_value => TO_CHAR(l_to_component_amt));
890
891
892 IF l_total_amt = 0 THEN
893 l_debug_info := 'No value for the basis '||l_allocation_basis;
894 IF l_allocation_basis <> 'VALUE' THEN
895 l_allocation_basis := 'VALUE';
896 l_debug_info := 'No value for the basis '||'Changing allocation basis to '||l_allocation_basis;
897 ELSE
898 l_debug_info := 'Component not allocated';
899 o_factor := 0;
900 END IF;
901 INL_LOGGING_PVT.Log_Statement (p_module_name => g_module_name,
902 p_procedure_name => l_procedure_name,
903 p_debug_info => l_debug_info);
904 ELSE
905 o_factor := l_to_component_amt/l_total_amt;
906 END IF;
907
908 IF p_allocation_basis <> 'VALUE' THEN
909 IF p_to_component_name = 'INL_CHARGE_LINES' THEN
910 l_debug_info := 'Assumed allocation basis VALUE for charges';
911 ELSIF p_to_component_name = 'INL_TAX_LINES' THEN
912 l_debug_info := 'Assumed allocation basis VALUE for taxes';
913 END IF;
914 END IF;
915
916 l_debug_info := 'o_factor';
917 INL_LOGGING_PVT.Log_Variable (p_module_name => g_module_name,
918 p_procedure_name => l_procedure_name,
919 p_var_name => l_debug_info,
920 p_var_value => TO_CHAR(o_factor));
921
922 INL_LOGGING_PVT.Log_EndProc (p_module_name => g_module_name,
923 p_procedure_name => l_procedure_name);
924
925 EXCEPTION
926 WHEN FND_API.G_EXC_ERROR THEN
927 INL_LOGGING_PVT.Log_ExpecError (p_module_name => g_module_name,
928 p_procedure_name => l_procedure_name);
929 x_return_status := FND_API.G_RET_STS_ERROR;
930 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
931 INL_LOGGING_PVT.Log_UnexpecError (p_module_name => g_module_name,
932 p_procedure_name => l_procedure_name);
933 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
934 WHEN OTHERS THEN
935 INL_LOGGING_PVT.Log_UnexpecError (p_module_name => g_module_name,
936 p_procedure_name => l_procedure_name);
937 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
938 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
939 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,l_procedure_name);
940 END IF;
941
942 END Manage_Proportion;
943
944 -- Utility name : Insert_Allocation
945 -- Type : Private
946 -- Function :
947 --
948 -- Amounts from Landed Cost Shipment Lines as well as amounts from charges and
949 -- taxes should also generate lines in INL_ALLOCATIONS.
950 --
951 -- Landed Cost Shipment Lines that belong to components that are associated to others
952 -- should generate allocations with LANDED_COST_FLAG = N.
953 --
954 -- Charges and taxes will always generate allocations with LANDED_COST_FLAG = N, since
955 -- they will always end up to be sent to a Shipment Line flagged with LANDED_COST_FLAG = Y.
956 --
957 -- Pre-reqs : None
958 -- Parameters :
959 -- IN : p_ship_header_id IN NUMBER
960 -- p_le_currency_code IN VARCHAR2,
961 -- p_association_id IN NUMBER
962 -- p_ship_line_id IN NUMBER
963 -- p_amount IN NUMBER
964 -- p_from_component_name IN VARCHAR2
965 -- p_from_component_id IN NUMBER
966 -- p_to_component_name IN VARCHAR2
967 -- p_to_component_id IN NUMBER
968 -- p_lc_flag IN VARCHAR2
969 -- p_adjustment_num IN NUMBER
970 -- OUT : x_return_status OUT NOCOPY VARCHAR2
971 --
972 -- Version : Current version 1.0
973 --
974 -- Notes :
975 PROCEDURE Insert_Allocation (p_ship_header_id IN NUMBER,
976 p_le_currency_code IN VARCHAR2,
977 p_association_id IN NUMBER,
978 p_ship_line_id IN NUMBER,
979 p_amount IN NUMBER,
980 p_from_component_name IN VARCHAR2,
981 p_from_component_id IN NUMBER,
982 p_to_component_name IN VARCHAR2,
983 p_to_component_id IN NUMBER,
984 p_lc_flag IN VARCHAR2,
985 p_adjustment_num IN NUMBER,
986 x_return_status OUT NOCOPY VARCHAR2)
987 IS
988
989 l_ship_line_lc_flag VARCHAR2(1);
990 l_debug_info VARCHAR2(240);
991 l_procedure_name CONSTANT VARCHAR2(30) := 'Insert_Allocation';
992 l_from_component_name VARCHAR2(30);
993 l_to_component_name VARCHAR2(30);
994 l_count NUMBER;
995 l_factor NUMBER;
996 l_return_status VARCHAR2(1);
997
998 CURSOR assoc IS
999 SELECT a.association_id,
1000 a.from_parent_table_name,
1001 a.from_parent_table_id,
1002 a.to_parent_table_name,
1003 a.to_parent_table_id,
1004 a.allocation_basis,
1005 a.allocation_uom_code,
1006 a.to_parent_table_id ship_line_id
1007 FROM inl_adj_associations_v a
1011 AND a.ship_header_id = p_ship_header_id
1008 WHERE a.from_parent_table_name = 'INL_SHIP_LINES'
1009 AND a.from_parent_table_id = p_ship_line_id
1010 AND a.to_parent_table_name = 'INL_SHIP_LINES'
1012 ORDER BY a.association_id;
1013 rec_assoc assoc%ROWTYPE;
1014
1015 BEGIN
1016
1017
1018 INL_LOGGING_PVT.Log_BeginProc (p_module_name => g_module_name,
1019 p_procedure_name => l_procedure_name);
1020
1021 --
1022 -- Initialize return status to SUCCESS
1023 --
1024
1025 x_return_status := FND_API.G_RET_STS_SUCCESS;
1026
1027 l_debug_info := 'p_ship_header_id';
1028 INL_LOGGING_PVT.Log_Variable (p_module_name => g_module_name,
1029 p_procedure_name => l_procedure_name,
1030 p_var_name => l_debug_info,
1031 p_var_value => TO_CHAR(p_ship_header_id));
1032
1033 l_debug_info := 'p_le_currency_code';
1034 INL_LOGGING_PVT.Log_Variable (p_module_name => g_module_name,
1035 p_procedure_name => l_procedure_name,
1036 p_var_name => l_debug_info,
1037 p_var_value => p_le_currency_code);
1038
1039 l_debug_info := 'p_association_id';
1040 INL_LOGGING_PVT.Log_Variable (p_module_name => g_module_name,
1041 p_procedure_name => l_procedure_name,
1042 p_var_name => l_debug_info,
1043 p_var_value => TO_CHAR(p_association_id));
1044
1045 l_debug_info := 'p_ship_line_id';
1046 INL_LOGGING_PVT.Log_Variable (p_module_name => g_module_name,
1047 p_procedure_name => l_procedure_name,
1048 p_var_name => l_debug_info,
1049 p_var_value => TO_CHAR(p_ship_line_id));
1050
1051 l_debug_info := 'p_amount';
1052 INL_LOGGING_PVT.Log_Variable (p_module_name => g_module_name,
1053 p_procedure_name => l_procedure_name,
1054 p_var_name => l_debug_info,
1055 p_var_value => TO_CHAR(p_amount));
1056
1057 l_debug_info := 'p_from_component_name';
1058 INL_LOGGING_PVT.Log_Variable (p_module_name => g_module_name,
1059 p_procedure_name => l_procedure_name,
1060 p_var_name => l_debug_info,
1061 p_var_value => p_from_component_name);
1062
1063 l_debug_info := 'p_from_component_id';
1064 INL_LOGGING_PVT.Log_Variable (p_module_name => g_module_name,
1065 p_procedure_name => l_procedure_name,
1066 p_var_name => l_debug_info,
1067 p_var_value => TO_CHAR(p_from_component_id));
1068
1069 l_debug_info := 'p_to_component_name';
1070 INL_LOGGING_PVT.Log_Variable (p_module_name => g_module_name,
1071 p_procedure_name => l_procedure_name,
1072 p_var_name => l_debug_info,
1073 p_var_value => p_to_component_name);
1074
1075 l_debug_info := 'p_to_component_id';
1076 INL_LOGGING_PVT.Log_Variable (p_module_name => g_module_name,
1077 p_procedure_name => l_procedure_name,
1078 p_var_name => l_debug_info,
1079 p_var_value => TO_CHAR(p_to_component_id));
1080
1081 l_debug_info := 'p_lc_flag';
1082 INL_LOGGING_PVT.Log_Variable (p_module_name => g_module_name,
1083 p_procedure_name => l_procedure_name,
1084 p_var_name => l_debug_info,
1085 p_var_value => p_lc_flag);
1086
1087 l_debug_info := 'p_adjustment_num';
1088 INL_LOGGING_PVT.Log_Variable (p_module_name => g_module_name,
1089 p_procedure_name => l_procedure_name,
1090 p_var_name => l_debug_info,
1091 p_var_value => TO_CHAR(p_adjustment_num));
1092 -- bug 7660824
1093 -- Allocations from Shipment Lines with landed_cost_flag = 'N' should be inserted normally.
1094 -- The only difference is that it should be considered as amount zero, when inserting the
1095 -- corresponding allocation line.
1096 /*
1097 BEGIN
1098 SELECT ol.landed_cost_flag
1099 INTO l_ship_line_lc_flag
1100 FROM inl_adj_ship_lines_v ol
1101 WHERE ol.ship_line_id = p_ship_line_id;
1102 EXCEPTION
1103 WHEN NO_DATA_FOUND THEN
1104 l_ship_line_lc_flag := 'N';
1105 END;
1106 */
1107 l_ship_line_lc_flag := 'Y';
1108 --
1109
1110 l_debug_info := 'l_ship_line_lc_flag';
1111 INL_LOGGING_PVT.Log_Variable (p_module_name => g_module_name,
1112 p_procedure_name => l_procedure_name,
1113 p_var_name => l_debug_info,
1114 p_var_value => l_ship_line_lc_flag);
1115
1116 -- For backward compatibility we are still using Distributions in calculations;
1117 -- however, for allocation purposes we'll use Shipment Lines instead.
1118 l_from_component_name := p_from_component_name;
1119 l_to_component_name := p_to_component_name;
1120 IF l_from_component_name = 'INL_SHIP_DISTS' THEN
1121 l_from_component_name := 'INL_SHIP_LINES';
1122 END IF;
1123 IF l_to_component_name = 'INL_SHIP_DISTS' THEN
1124 l_to_component_name := 'INL_SHIP_LINES';
1125 END IF;
1126
1127
1128 -- Check whether the Ship Line of the allocation is associated to other Ship Lines
1132 p_debug_info => l_debug_info);
1129 l_debug_info := 'Check whether the Ship Line of the allocation is associated to other Ship Lines';
1130 INL_LOGGING_PVT.Log_Statement (p_module_name => g_module_name,
1131 p_procedure_name => l_procedure_name,
1133
1134 l_count := 0;
1135 OPEN assoc;
1136 LOOP
1137 FETCH assoc INTO rec_assoc;
1138 EXIT WHEN assoc%NOTFOUND;
1139 l_count := l_count + 1;
1140
1141 l_debug_info := 'Calling Manage_Proportion';
1142 INL_LOGGING_PVT.Log_Statement (p_module_name => g_module_name,
1143 p_procedure_name => l_procedure_name,
1144 p_debug_info => l_debug_info);
1145
1146 Manage_Proportion (p_ship_header_id,
1147 p_le_currency_code,
1148 'INL_SHIP_LINES',
1149 p_ship_line_id,
1150 rec_assoc.to_parent_table_name,
1151 rec_assoc.to_parent_table_id,
1152 rec_assoc.allocation_basis,
1153 rec_assoc.allocation_uom_code,
1154 l_factor,
1155 l_return_status);
1156 -- If any errors happen abort API.
1157 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1158 RAISE FND_API.G_EXC_ERROR;
1159 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1160 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1161 END IF;
1162
1163 l_debug_info := 'l_factor';
1164 INL_LOGGING_PVT.Log_Variable (p_module_name => g_module_name,
1165 p_procedure_name => l_procedure_name,
1166 p_var_name => l_debug_info,
1167 p_var_value => TO_CHAR(l_factor));
1168
1169 l_debug_info := 'Inserting into inl_allocations';
1170 INL_LOGGING_PVT.Log_Statement (p_module_name => g_module_name,
1171 p_procedure_name => l_procedure_name,
1172 p_debug_info => l_debug_info);
1173
1174 l_debug_info := 'rec_assoc.to_parent_table_id';
1175 INL_LOGGING_PVT.Log_Variable (p_module_name => g_module_name,
1176 p_procedure_name => l_procedure_name,
1177 p_var_name => l_debug_info,
1178 p_var_value => TO_CHAR(rec_assoc.to_parent_table_id));
1179
1180 l_debug_info := 'p_amount * l_factor';
1181 INL_LOGGING_PVT.Log_Variable (p_module_name => g_module_name,
1182 p_procedure_name => l_procedure_name,
1183 p_var_name => l_debug_info,
1184 p_var_value => TO_CHAR(p_amount * l_factor));
1185
1186 -- Bug 7706718 - Recursive call to Insert_Allocation, until there are no shipment lines to redirect the allocation
1187 Insert_Allocation (p_ship_header_id,
1188 p_le_currency_code,
1189 p_association_id,
1190 rec_assoc.ship_line_id,
1191 p_amount * l_factor,
1192 l_from_component_name,
1193 p_from_component_id,
1194 rec_assoc.to_parent_table_name,
1195 rec_assoc.to_parent_table_id,
1196 p_lc_flag,
1197 p_adjustment_num,
1198 l_return_status);
1199 -- If any errors happen abort API.
1200 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1201 RAISE FND_API.G_EXC_ERROR;
1202 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1203 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1204 END IF;
1205
1206
1207 END LOOP;
1208 CLOSE assoc;
1209
1210 IF l_count = 0 THEN
1211
1212 l_debug_info := 'Inserting into inl_allocations';
1213 INL_LOGGING_PVT.Log_Statement (p_module_name => g_module_name,
1214 p_procedure_name => l_procedure_name,
1215 p_debug_info => l_debug_info);
1216
1217 l_debug_info := 'p_ship_line_id';
1218 INL_LOGGING_PVT.Log_Variable (p_module_name => g_module_name,
1219 p_procedure_name => l_procedure_name,
1220 p_var_name => l_debug_info,
1221 p_var_value => TO_CHAR(p_ship_line_id));
1222
1223 l_debug_info := 'p_amount';
1224 INL_LOGGING_PVT.Log_Variable (p_module_name => g_module_name,
1225 p_procedure_name => l_procedure_name,
1226 p_var_name => l_debug_info,
1227 p_var_value => TO_CHAR(p_amount));
1228
1229 INSERT INTO inl_allocations
1230 (allocation_id,
1231 ship_header_id,
1232 association_id,
1233 ship_line_id,
1234 from_parent_table_name,
1235 from_parent_table_id,
1236 to_parent_table_name,
1237 to_parent_table_id,
1238 adjustment_num,
1239 allocation_amt,
1240 landed_cost_flag,
1241 created_by,
1242 creation_date,
1243 last_updated_by,
1244 last_update_date,
1245 last_update_login)
1246 VALUES
1247 (inl_allocations_s.NEXTVAL,
1248 p_ship_header_id,
1249 p_association_id,
1250 p_ship_line_id,
1251 l_from_component_name,
1252 p_from_component_id,
1253 l_to_component_name,
1254 p_to_component_id,
1258 fnd_global.user_id,
1255 p_adjustment_num,
1256 p_amount,
1257 DECODE(p_lc_flag,'N','N','Y',l_ship_line_lc_flag),
1259 SYSDATE,
1260 fnd_global.user_id,
1261 SYSDATE,
1262 fnd_global.login_id);
1263 END IF;
1264
1265 INL_LOGGING_PVT.Log_EndProc (p_module_name => g_module_name,
1266 p_procedure_name => l_procedure_name);
1267
1268 EXCEPTION
1269 WHEN FND_API.G_EXC_ERROR THEN
1270 INL_LOGGING_PVT.Log_ExpecError (p_module_name => g_module_name,
1271 p_procedure_name => l_procedure_name);
1272 x_return_status := FND_API.G_RET_STS_ERROR;
1273 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1274 INL_LOGGING_PVT.Log_UnexpecError (p_module_name => g_module_name,
1275 p_procedure_name => l_procedure_name);
1276 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1277 WHEN OTHERS THEN
1278 INL_LOGGING_PVT.Log_UnexpecError (p_module_name => g_module_name,
1279 p_procedure_name => l_procedure_name);
1280 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1281 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1282 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,l_procedure_name);
1283 END IF;
1284
1285 END Insert_Allocation;
1286
1287
1288 -- Utility name : Manage_Allocation
1289 -- Type : Private
1290 -- Function :
1291 --
1292 -- This routine allocates the amount that gets to a landed cost component into its corresponding
1293 -- Shipment Lines. In this routine, the Manage_Proportion routine is also called, this time
1294 -- to prorate the allocating amount into the many Shipment Lines corresponding to the
1295 -- component that is absorbing it.
1296 --
1297 -- Pre-reqs : None
1298 -- Parameters :
1299 -- IN : p_ship_header_id IN NUMBER
1300 -- p_le_currency_code IN VARCHAR2
1301 -- p_association_id IN NUMBER
1302 -- p_allocation_basis IN VARCHAR2
1303 -- p_allocation_uom_code IN VARCHAR2
1304 -- p_amount IN NUMBER
1305 -- p_from_component_name IN VARCHAR2
1306 -- p_from_component_id IN NUMBER
1307 -- p_to_component_name IN VARCHAR2
1308 -- p_to_component_id IN NUMBER
1309 -- p_lc_flag IN VARCHAR2
1310 -- p_adjustment_num IN NUMBER
1311 -- OUT : x_return_status OUT NOCOPY VARCHAR2
1312 --
1313 -- Version : Current version 1.0
1314 --
1315 -- Notes :
1316 PROCEDURE Manage_Allocation (p_ship_header_id IN NUMBER,
1317 p_le_currency_code IN VARCHAR2,
1318 p_association_id IN NUMBER,
1319 p_allocation_basis IN VARCHAR2,
1320 p_allocation_uom_code IN VARCHAR2,
1321 p_amount IN NUMBER,
1322 p_from_component_name IN VARCHAR2,
1323 p_from_component_id IN NUMBER,
1324 p_to_component_name IN VARCHAR2,
1325 p_to_component_id IN NUMBER,
1326 p_lc_flag IN VARCHAR2,
1327 p_adjustment_num IN NUMBER,
1328 x_return_status OUT NOCOPY VARCHAR2)
1329 IS
1330 CURSOR dist IS
1331 SELECT ship_line_id
1332 FROM inl_adj_ship_lines_v
1333 WHERE ship_header_id = p_ship_header_id
1334 AND ship_line_id = DECODE(p_to_component_name, 'INL_SHIP_LINES', p_to_component_id, ship_line_id)
1335 --- bug 7654293
1336 AND ship_line_group_id = DECODE(p_to_component_name, 'INL_SHIP_LINE_GROUPS', p_to_component_id, ship_line_group_id);
1337
1338 rec_dist dist%ROWTYPE;
1339
1340 l_factor NUMBER;
1341 l_debug_info VARCHAR2(240);
1342 l_procedure_name CONSTANT VARCHAR2(30) := 'Manage_Allocation';
1343 l_return_status VARCHAR2(1);
1344
1345 BEGIN
1346
1347 INL_LOGGING_PVT.Log_BeginProc (p_module_name => g_module_name,
1348 p_procedure_name => l_procedure_name);
1349
1350 --
1351 -- Initialize return status to SUCCESS
1352 --
1353
1354 x_return_status := FND_API.G_RET_STS_SUCCESS;
1355
1356 l_debug_info := 'p_ship_header_id';
1357 INL_LOGGING_PVT.Log_Variable (p_module_name => g_module_name,
1358 p_procedure_name => l_procedure_name,
1359 p_var_name => l_debug_info,
1360 p_var_value => TO_CHAR(p_ship_header_id));
1361
1362 l_debug_info := 'p_le_currency_code';
1363 INL_LOGGING_PVT.Log_Variable (p_module_name => g_module_name,
1364 p_procedure_name => l_procedure_name,
1365 p_var_name => l_debug_info,
1366 p_var_value => p_le_currency_code);
1367
1368 l_debug_info := 'p_association_id';
1369 INL_LOGGING_PVT.Log_Variable (p_module_name => g_module_name,
1370 p_procedure_name => l_procedure_name,
1371 p_var_name => l_debug_info,
1372 p_var_value => TO_CHAR(p_association_id));
1373
1374 l_debug_info := 'p_allocation_basis';
1375 INL_LOGGING_PVT.Log_Variable (p_module_name => g_module_name,
1376 p_procedure_name => l_procedure_name,
1377 p_var_name => l_debug_info,
1378 p_var_value => p_allocation_basis);
1382 p_procedure_name => l_procedure_name,
1379
1380 l_debug_info := 'p_allocation_uom_code';
1381 INL_LOGGING_PVT.Log_Variable (p_module_name => g_module_name,
1383 p_var_name => l_debug_info,
1384 p_var_value => p_allocation_uom_code);
1385
1386 l_debug_info := 'p_amount';
1387 INL_LOGGING_PVT.Log_Variable (p_module_name => g_module_name,
1388 p_procedure_name => l_procedure_name,
1389 p_var_name => l_debug_info,
1390 p_var_value => TO_CHAR(p_amount));
1391
1392 l_debug_info := 'p_from_component_name';
1393 INL_LOGGING_PVT.Log_Variable (p_module_name => g_module_name,
1394 p_procedure_name => l_procedure_name,
1395 p_var_name => l_debug_info,
1396 p_var_value => p_from_component_name);
1397
1398 l_debug_info := 'p_from_component_id';
1399 INL_LOGGING_PVT.Log_Variable (p_module_name => g_module_name,
1400 p_procedure_name => l_procedure_name,
1401 p_var_name => l_debug_info,
1402 p_var_value => TO_CHAR(p_from_component_id));
1403
1404 l_debug_info := 'p_to_component_name';
1405 INL_LOGGING_PVT.Log_Variable (p_module_name => g_module_name,
1406 p_procedure_name => l_procedure_name,
1407 p_var_name => l_debug_info,
1408 p_var_value => p_to_component_name);
1409
1410 l_debug_info := 'p_to_component_id';
1411 INL_LOGGING_PVT.Log_Variable (p_module_name => g_module_name,
1412 p_procedure_name => l_procedure_name,
1413 p_var_name => l_debug_info,
1414 p_var_value => TO_CHAR(p_to_component_id));
1415
1416 l_debug_info := 'p_adjustment_num';
1417 INL_LOGGING_PVT.Log_Variable (p_module_name => g_module_name,
1418 p_procedure_name => l_procedure_name,
1419 p_var_name => l_debug_info,
1420 p_var_value => TO_CHAR(p_adjustment_num));
1421
1422 OPEN dist;
1423 LOOP
1424 FETCH dist INTO rec_dist;
1425 EXIT WHEN dist%NOTFOUND;
1426
1427 Manage_Proportion (p_ship_header_id,
1428 p_le_currency_code,
1429 p_to_component_name,
1430 p_to_component_id,
1431 'INL_SHIP_DISTS',
1432 rec_dist.ship_line_id,
1433 p_allocation_basis,
1434 p_allocation_uom_code,
1435 l_factor,
1436 l_return_status);
1437 -- If any errors happen abort API.
1438 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1439 RAISE FND_API.G_EXC_ERROR;
1440 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1441 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1442 END IF;
1443
1444 Insert_Allocation (p_ship_header_id,
1445 p_le_currency_code,
1446 p_association_id,
1447 rec_dist.ship_line_id,
1448 p_amount * l_factor,
1449 p_from_component_name,
1450 p_from_component_id,
1451 p_to_component_name,
1452 p_to_component_id,
1453 p_lc_flag,
1454 p_adjustment_num,
1455 l_return_status);
1456 -- If any errors happen abort API.
1457 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1458 RAISE FND_API.G_EXC_ERROR;
1459 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1460 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1461 END IF;
1462
1463 END LOOP;
1464 CLOSE dist;
1465
1466 INL_LOGGING_PVT.Log_EndProc (p_module_name => g_module_name,
1467 p_procedure_name => l_procedure_name);
1468
1469 EXCEPTION
1470 WHEN FND_API.G_EXC_ERROR THEN
1471 INL_LOGGING_PVT.Log_ExpecError (p_module_name => g_module_name,
1472 p_procedure_name => l_procedure_name);
1473 x_return_status := FND_API.G_RET_STS_ERROR;
1474 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1475 INL_LOGGING_PVT.Log_UnexpecError (p_module_name => g_module_name,
1476 p_procedure_name => l_procedure_name);
1477 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1478 WHEN OTHERS THEN
1479 INL_LOGGING_PVT.Log_UnexpecError (p_module_name => g_module_name,
1480 p_procedure_name => l_procedure_name);
1481 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1482 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1483 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,l_procedure_name);
1484 END IF;
1485
1486 END Manage_Allocation;
1487
1488 -- Utility name : Control_Allocation
1489 -- Type : Private
1490 -- Function : This routine controls and redirect, when necessary, the allocation of an amount that
1491 -- comes from a component to another.
1492 --
1493 -- |-> Shipment Line SL1
1494 -- Example: Charge CH1: Shipment SH1 -
1495 -- |-> Shipment Line SL2
1496 --
1497 -- This routine would take care of calling the Manage_Allocation routine
1501 -- Pre-reqs : None
1498 -- for allocating CH1 amount to Shipment SH1, and then for allocating
1499 -- the SH1 portion of CH1 to Shipment Line SL1 and Shipment Line SL1.
1500 --
1502 -- Parameters :
1503 -- IN : p_ship_header_id IN NUMBER
1504 -- p_le_currency_code IN VARCHAR2
1505 -- p_association_id IN NUMBER
1506 -- p_allocation_basis IN VARCHAR2
1507 -- p_allocation_uom_code IN VARCHAR2
1508 -- p_amount IN NUMBER
1509 -- p_from_component_name IN VARCHAR2
1510 -- p_from_component_id IN NUMBER
1511 -- p_to_component_name IN VARCHAR2
1512 -- p_to_component_id IN NUMBER
1513 -- p_adjustment_num IN NUMBER
1514 --
1515 -- OUT x_return_status OUT NOCOPY VARCHAR2
1516 --
1517 -- Version : Current version 1.0
1518 --
1519 -- Notes :
1520
1521 PROCEDURE Control_Allocation (p_ship_header_id IN NUMBER,
1522 p_le_currency_code IN VARCHAR2,
1523 p_association_id IN NUMBER,
1524 p_allocation_basis IN VARCHAR2,
1525 p_allocation_uom_code IN VARCHAR2,
1526 p_amount IN NUMBER,
1527 p_from_component_name IN VARCHAR2,
1528 p_from_component_id IN NUMBER,
1529 p_to_component_name IN VARCHAR2,
1530 p_to_component_id IN NUMBER,
1531 p_adjustment_num IN NUMBER,
1532 x_return_status OUT NOCOPY VARCHAR2)
1533 IS
1534 CURSOR component IS
1535 SELECT 1 SEQ_NUM,
1536 'INL_SHIP_LINES' COMPONENT_NAME,
1537 ol.ship_line_id COMPONENT_ID
1538 FROM inl_adj_ship_lines_v ol
1539 WHERE ol.ship_header_id = p_ship_header_id
1540 AND ol.ship_line_id = DECODE(p_to_component_name, 'INL_SHIP_LINES', p_to_component_id, -1)
1541 UNION
1542 SELECT 2 SEQ_NUM,
1543 'INL_SHIP_HEADERS' COMPONENT_NAME,
1544 ol.ship_header_id COMPONENT_ID
1545 FROM inl_adj_ship_lines_v ol
1546 WHERE ol.ship_header_id = p_ship_header_id
1547 AND ol.ship_line_id = DECODE(p_to_component_name, 'INL_SHIP_LINES', p_to_component_id, -1)
1548 UNION
1549 SELECT 1 SEQ_NUM,
1550 p_to_component_name COMPONENT_NAME,
1551 p_to_component_id COMPONENT_ID
1552 FROM dual
1553 ORDER BY seq_num;
1554 rec_component component%ROWTYPE;
1555
1556 CURSOR assoc IS
1557 SELECT association_id,
1558 from_parent_table_name,
1559 from_parent_table_id,
1560 to_parent_table_name,
1561 to_parent_table_id
1562 FROM INL_adj_associations_v
1563 WHERE ship_header_id = p_ship_header_id
1564 AND from_parent_table_name = rec_component.component_name
1565 AND from_parent_table_id = rec_component.component_id
1566 ORDER BY association_id;
1567 rec_assoc assoc%ROWTYPE;
1568 l_next_level_allocation VARCHAR2(1);
1569 l_lc_flag VARCHAR2(1);
1570 l_factor NUMBER;
1571 l_debug_info VARCHAR2(240);
1572 l_procedure_name CONSTANT VARCHAR2(30) := 'Control_Allocation';
1573 l_return_status VARCHAR2(1);
1574
1575 BEGIN
1576
1577 INL_LOGGING_PVT.Log_BeginProc (p_module_name => g_module_name,
1578 p_procedure_name => l_procedure_name);
1579 --
1580 -- Initialize return status to SUCCESS
1581 --
1582
1583 x_return_status := FND_API.G_RET_STS_SUCCESS;
1584
1585 l_next_level_allocation := 'N';
1586
1587 l_debug_info := 'p_ship_header_id';
1588 INL_LOGGING_PVT.Log_Variable (p_module_name => g_module_name,
1589 p_procedure_name => l_procedure_name,
1590 p_var_name => l_debug_info,
1591 p_var_value => TO_CHAR(p_ship_header_id));
1592
1593 l_debug_info := 'p_le_currency_code';
1594 INL_LOGGING_PVT.Log_Variable (p_module_name => g_module_name,
1595 p_procedure_name => l_procedure_name,
1596 p_var_name => l_debug_info,
1597 p_var_value => p_le_currency_code);
1598
1599 l_debug_info := 'p_association_id';
1600 INL_LOGGING_PVT.Log_Variable (p_module_name => g_module_name,
1601 p_procedure_name => l_procedure_name,
1602 p_var_name => l_debug_info,
1603 p_var_value => TO_CHAR(p_association_id));
1604
1605 l_debug_info := 'p_allocation_basis';
1606 INL_LOGGING_PVT.Log_Variable (p_module_name => g_module_name,
1607 p_procedure_name => l_procedure_name,
1608 p_var_name => l_debug_info,
1609 p_var_value => p_allocation_basis);
1610
1611 l_debug_info := 'p_allocation_uom_code';
1612 INL_LOGGING_PVT.Log_Variable (p_module_name => g_module_name,
1613 p_procedure_name => l_procedure_name,
1614 p_var_name => l_debug_info,
1615 p_var_value => p_allocation_uom_code);
1616
1617 l_debug_info := 'p_amount';
1618 INL_LOGGING_PVT.Log_Variable (p_module_name => g_module_name,
1619 p_procedure_name => l_procedure_name,
1620 p_var_name => l_debug_info,
1621 p_var_value => TO_CHAR(p_amount));
1622
1623 l_debug_info := 'p_from_component_name';
1624 INL_LOGGING_PVT.Log_Variable (p_module_name => g_module_name,
1625 p_procedure_name => l_procedure_name,
1626 p_var_name => l_debug_info,
1627 p_var_value => p_from_component_name);
1628
1629 l_debug_info := 'p_from_component_id';
1630 INL_LOGGING_PVT.Log_Variable (p_module_name => g_module_name,
1631 p_procedure_name => l_procedure_name,
1632 p_var_name => l_debug_info,
1633 p_var_value => TO_CHAR(p_from_component_id));
1634
1635 l_debug_info := 'p_to_component_name';
1636 INL_LOGGING_PVT.Log_Variable (p_module_name => g_module_name,
1637 p_procedure_name => l_procedure_name,
1638 p_var_name => l_debug_info,
1639 p_var_value => p_to_component_name);
1640
1641 l_debug_info := 'p_to_component_id';
1642 INL_LOGGING_PVT.Log_Variable (p_module_name => g_module_name,
1643 p_procedure_name => l_procedure_name,
1644 p_var_name => l_debug_info,
1645 p_var_value => TO_CHAR(p_to_component_id));
1646
1647 l_debug_info := 'p_adjustment_num';
1648 INL_LOGGING_PVT.Log_Variable (p_module_name => g_module_name,
1649 p_procedure_name => l_procedure_name,
1650 p_var_name => l_debug_info,
1651 p_var_value => TO_CHAR(p_adjustment_num));
1652
1653 OPEN component;
1654 LOOP
1655 FETCH component INTO rec_component;
1656 EXIT WHEN component%NOTFOUND;
1657
1658 OPEN assoc;
1659 LOOP
1660 FETCH assoc INTO rec_assoc;
1661 EXIT WHEN assoc%NOTFOUND;
1662
1663 l_next_level_allocation := 'Y';
1664
1665 l_debug_info := 'Call Proportion procedure ';
1666 INL_LOGGING_PVT.Log_Statement (p_module_name => g_module_name,
1667 p_procedure_name => l_procedure_name,
1668 p_debug_info => l_debug_info);
1669
1670 Manage_Proportion (p_ship_header_id,
1671 p_le_currency_code,
1672 rec_assoc.from_parent_table_name,
1673 rec_assoc.from_parent_table_id,
1674 rec_assoc.to_parent_table_name,
1675 rec_assoc.to_parent_table_id,
1676 p_allocation_basis,
1677 p_allocation_uom_code,
1678 l_factor,
1679 l_return_status);
1680 -- If any errors happen abort API.
1681 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1682 RAISE FND_API.G_EXC_ERROR;
1683 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1684 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1685 END IF;
1686
1687 Control_Allocation (p_ship_header_id,
1688 p_le_currency_code,
1689 p_association_id,
1690 p_allocation_basis,
1691 p_allocation_uom_code,
1692 p_amount * l_factor,
1693 -- Bug 7706732, Bug 7708012 - Allocations that come from redirected allocations must be created
1694 -- with the original "from" component
1695 -- rec_component.component_name,
1696 -- rec_component.component_id,
1697 p_from_component_name,
1698 p_from_component_id,
1699 -- Bug 7706732, Bug 7708012
1700 rec_assoc.to_parent_table_name,
1701 rec_assoc.to_parent_table_id,
1702 p_adjustment_num,
1703 l_return_status);
1704 -- If any errors happen abort API.
1705 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1706 RAISE FND_API.G_EXC_ERROR;
1707 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1708 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1709 END IF;
1710
1711 END LOOP;
1712 CLOSE assoc;
1713 END LOOP;
1714 CLOSE component;
1715
1716 l_debug_info := 'p_to_component_name';
1717 INL_LOGGING_PVT.Log_Variable (p_module_name => g_module_name,
1718 p_procedure_name => l_procedure_name,
1719 p_var_name => l_debug_info,
1720 p_var_value => p_to_component_name);
1721
1722 IF p_to_component_name IN ('INL_CHARGE_LINES','INL_TAX_LINES') THEN
1723 Insert_Allocation (p_ship_header_id,
1724 p_le_currency_code,
1725 p_association_id,
1726 NULL,
1727 p_amount,
1728 p_from_component_name,
1729 p_from_component_id,
1730 p_to_component_name,
1731 p_to_component_id,
1732 'N',
1733 p_adjustment_num,
1734 l_return_status);
1735 -- If any errors happen abort API.
1736 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1737 RAISE FND_API.G_EXC_ERROR;
1738 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1739 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1740 END IF;
1741 ELSE
1742 SELECT DECODE(l_next_level_allocation,'N','Y','N')
1743 INTO l_lc_flag
1744 FROM DUAL;
1745
1746 Manage_Allocation (p_ship_header_id,
1747 p_le_currency_code,
1748 p_association_id,
1749 p_allocation_basis,
1750 p_allocation_uom_code,
1751 p_amount,
1752 p_from_component_name,
1753 p_from_component_id,
1754 p_to_component_name,
1755 p_to_component_id,
1756 l_lc_flag,
1757 p_adjustment_num,
1758 l_return_status);
1759 -- If any errors happen abort API.
1760 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1761 RAISE FND_API.G_EXC_ERROR;
1762 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1763 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1764 END IF;
1765
1766 END IF;
1767
1768 INL_LOGGING_PVT.Log_EndProc (p_module_name => g_module_name,
1769 p_procedure_name => l_procedure_name);
1770
1771 EXCEPTION
1772 WHEN FND_API.G_EXC_ERROR THEN
1773 INL_LOGGING_PVT.Log_ExpecError (p_module_name => g_module_name,
1774 p_procedure_name => l_procedure_name);
1775 x_return_status := FND_API.G_RET_STS_ERROR;
1776 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1777 INL_LOGGING_PVT.Log_UnexpecError (p_module_name => g_module_name,
1778 p_procedure_name => l_procedure_name);
1782 p_procedure_name => l_procedure_name);
1779 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1780 WHEN OTHERS THEN
1781 INL_LOGGING_PVT.Log_UnexpecError (p_module_name => g_module_name,
1783 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1784 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1785 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,l_procedure_name);
1786 END IF;
1787
1788 END Control_Allocation;
1789
1790 -- Utility name : Update_Allocation
1791 -- Type : Private
1792 -- Function : For actual amount allocations (adjustment_num > 0), stamp as the parent_allocation_id, the allocation_id
1793 -- of corresponding estimated amount (adjustment_num = 0).
1794 --
1795 -- Pre-reqs : None
1796 -- Parameters :
1797 -- IN : p_ship_header_id IN NUMBER
1798 -- p_adjustment_num IN NUMBER,
1799 -- OUT : x_return_status OUT NOCOPY VARCHAR2
1800 --
1801 --
1802 PROCEDURE Update_Allocation (p_ship_header_id IN NUMBER,
1803 p_adjustment_num IN NUMBER,
1804 x_return_status OUT NOCOPY VARCHAR2) IS
1805 --
1806 --
1807 --
1808 CURSOR updalloc IS
1809 SELECT allocation_id,
1810 parent_allocation_id,
1811 ship_header_id,
1812 association_id,
1813 ship_line_id,
1814 adjustment_num
1815 FROM inl_allocations
1816 WHERE adjustment_num = p_adjustment_num
1817 AND ship_header_id = p_ship_header_id
1818 ORDER BY allocation_id;
1819 rec_updalloc updalloc%ROWTYPE;
1820
1821 l_debug_info VARCHAR2(240);
1822 l_procedure_name CONSTANT VARCHAR2(30) := 'Update_Allocation';
1823 l_return_status VARCHAR2(1);
1824
1825 BEGIN
1826
1827 INL_LOGGING_PVT.Log_BeginProc (p_module_name => g_module_name,
1828 p_procedure_name => l_procedure_name);
1829 --
1830 -- Initialize return status to SUCCESS
1831 --
1832
1833 x_return_status := FND_API.G_RET_STS_SUCCESS;
1834
1835 l_debug_info := 'p_ship_header_id';
1836 INL_LOGGING_PVT.Log_Variable (p_module_name => g_module_name,
1837 p_procedure_name => l_procedure_name,
1838 p_var_name => l_debug_info,
1839 p_var_value => TO_CHAR(p_ship_header_id));
1840
1841 OPEN updalloc;
1842 LOOP
1843 FETCH updalloc INTO rec_updalloc;
1844 EXIT WHEN updalloc%NOTFOUND;
1845
1846 l_debug_info := 'Updating inl_allocations with parent_allocation_id';
1847 INL_LOGGING_PVT.Log_Statement (p_module_name => g_module_name,
1848 p_procedure_name => l_procedure_name,
1849 p_debug_info => l_debug_info);
1850
1851 UPDATE inl_allocations a1
1852 SET a1.parent_allocation_id = (SELECT MIN(a2.allocation_id)
1853 FROM inl_allocations a2
1854 WHERE a2.ship_header_id = rec_updalloc.ship_header_id
1855 AND NVL(a2.association_id,0) = NVL(rec_updalloc.association_id,0)
1856 AND (a2.ship_line_id = rec_updalloc.ship_line_id
1857 OR a2.ship_line_id = (SELECT a.parent_ship_line_id
1858 FROM inl_ship_lines a
1859 WHERE a.ship_line_id = rec_updalloc.ship_line_id))
1860 AND a2.adjustment_num = 0
1861 AND NOT EXISTS (SELECT 'X' FROM inl_allocations a1
1862 -- bug #7674125
1863 WHERE NVL(a1.parent_allocation_id,0) = NVL(a2.allocation_id,0)
1864 AND a1.ship_header_id = a2.ship_header_id
1865 AND NVL(a1.association_id,0) = NVL(a2.association_id,0)
1866 AND a1.adjustment_num = rec_updalloc.adjustment_num
1867 AND a1.landed_cost_flag = 'Y' ))
1868 WHERE a1.allocation_id = rec_updalloc.allocation_id
1869 AND a1.adjustment_num = rec_updalloc.adjustment_num;
1870
1871 l_debug_info := 'rec_updalloc.allocation_id';
1872 INL_LOGGING_PVT.Log_Variable (p_module_name => g_module_name,
1873 p_procedure_name => l_procedure_name,
1874 p_var_name => l_debug_info,
1875 p_var_value => TO_CHAR(rec_updalloc.allocation_id));
1876
1877 l_debug_info := 'rec_updalloc.adjustment_num';
1878 INL_LOGGING_PVT.Log_Variable (p_module_name => g_module_name,
1879 p_procedure_name => l_procedure_name,
1880 p_var_name => l_debug_info,
1881 p_var_value => TO_CHAR(rec_updalloc.adjustment_num));
1882
1883 END LOOP;
1884 CLOSE updalloc;
1885
1886 INL_LOGGING_PVT.Log_EndProc (p_module_name => g_module_name,
1887 p_procedure_name => l_procedure_name);
1888
1889 EXCEPTION
1890 WHEN FND_API.G_EXC_ERROR THEN
1891 INL_LOGGING_PVT.Log_ExpecError (p_module_name => g_module_name,
1892 p_procedure_name => l_procedure_name);
1893 x_return_status := FND_API.G_RET_STS_ERROR;
1894 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1895 INL_LOGGING_PVT.Log_UnexpecError (p_module_name => g_module_name,
1896 p_procedure_name => l_procedure_name);
1897 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1901 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1898 WHEN OTHERS THEN
1899 INL_LOGGING_PVT.Log_UnexpecError (p_module_name => g_module_name,
1900 p_procedure_name => l_procedure_name);
1902 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1903 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,l_procedure_name);
1904 END IF;
1905 END Update_Allocation;
1906
1907 -- API name : Run_Calculation
1908 -- Type : Private
1909 -- Function : Calculate Landed Costs for a given LCM Shipment.
1910 -- Pre-reqs : None
1911 -- Parameters :
1912 -- IN : p_api_version IN NUMBER Required
1913 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API.G_FALSE
1914 -- p_commit IN VARCHAR2 Optional Default = FND_API.G_FALSE
1915 -- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
1916 -- p_ship_header_id IN NUMBER Required
1917 -- p_calc_scope_code IN NUMBER Required Default = 0
1918 -- 0-Run for all components
1919 -- 1-Run only for Item Price components
1920 -- 2-Run only for Charge components
1921 -- 3-Run only form Tax components
1922 --
1923 -- OUT x_return_status OUT NOCOPY VARCHAR2
1924 -- x_msg_count OUT NOCOPY NUMBER
1925 -- x_msg_data OUT NOCOPY VARCHAR2
1926 --
1927 -- Version : Current version 1.0
1928 --
1929 -- Notes :
1930 PROCEDURE Run_Calculation (p_api_version IN NUMBER,
1931 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1932 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1933 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1934 p_ship_header_id IN NUMBER,
1935 p_calc_scope_code IN NUMBER,
1936 x_return_status OUT NOCOPY VARCHAR2,
1937 x_msg_count OUT NOCOPY NUMBER,
1938 x_msg_data OUT NOCOPY VARCHAR2) IS
1939
1940 CURSOR assoc IS
1941 SELECT ship_header_id,
1942 from_parent_table_name,
1943 from_parent_table_id,
1944 to_parent_table_name,
1945 to_parent_table_id,
1946 allocation_basis,
1947 allocation_uom_code,
1948 association_id
1949 FROM inl_adj_associations_v
1950 WHERE ship_header_id = p_ship_header_id
1951 AND ((p_calc_scope_code = 3 AND from_parent_table_name = 'INL_TAX_LINES') OR
1952 (p_calc_scope_code = 2 AND from_parent_table_name = 'INL_CHARGE_LINES') OR
1953 (p_calc_scope_code = 1 AND from_parent_table_name IN ('INL_SHIP_HEADERS','INL_SHIP_LINES')) OR
1954 p_calc_scope_code = 0)
1955 ORDER BY association_id;
1956 rec_assoc assoc%ROWTYPE;
1957
1958 CURSOR dist IS
1959 SELECT ship_header_id,
1960 ship_line_id,
1961 primary_qty,
1962 -- bug 7660824
1963 DECODE(landed_cost_flag,'Y',fc_primary_unit_price,0) fc_primary_unit_price
1964 FROM inl_adj_ship_lines_v
1965 WHERE ship_header_id = p_ship_header_id
1966 AND p_calc_scope_code in (0,1)
1967 ORDER BY ship_line_id;
1968 rec_dist dist%ROWTYPE;
1969
1970 CURSOR charge IS
1971 SELECT charge_amt,
1972 currency_code,
1973 currency_conversion_type,
1974 currency_conversion_rate,
1975 currency_conversion_date,
1976 charge_line_id
1977 FROM INL_adj_charge_lines_v c
1978 WHERE EXISTS (SELECT 'x'
1979 FROM inl_adj_associations_v x
1980 WHERE x.from_parent_table_name = 'INL_CHARGE_LINES'
1981 AND x.from_parent_table_id = c.charge_line_id
1982 AND x.ship_header_id = p_ship_header_id
1983 AND p_calc_scope_code in (0,2)
1984 AND ROWNUM < 2)
1985 ORDER BY charge_line_id;
1986 rec_charge charge%ROWTYPE;
1987
1988 CURSOR tax IS
1989 SELECT tax_amt,
1990 tax_line_id
1991 FROM INL_tax_lines t
1992 WHERE EXISTS (SELECT 'x'
1993 FROM INL_adj_associations_v x
1994 WHERE x.from_parent_table_name = 'INL_TAX_LINES'
1995 AND x.from_parent_table_id = t.tax_line_id
1996 AND x.ship_header_id = p_ship_header_id
1997 AND p_calc_scope_code in (0,3)
1998 AND ROWNUM < 2)
1999 ORDER BY tax_line_id;
2000 rec_tax tax%ROWTYPE;
2001
2002 l_amount NUMBER;
2003 l_from_amount NUMBER;
2004 l_to_amount NUMBER;
2005 l_factor NUMBER;
2009 l_inclusive_tax_amt NUMBER;
2006 l_count NUMBER;
2007 l_le_currency_code VARCHAR2(3);
2008 l_lc_flag VARCHAR2(1);
2010 l_debug_info VARCHAR2(240);
2011 l_api_name CONSTANT VARCHAR2(30) := 'Run_Calculation';
2012 l_api_version CONSTANT NUMBER := 1.0;
2013 l_return_status VARCHAR2(1);
2014 l_msg_count NUMBER;
2015 l_msg_data VARCHAR2(2000);
2016 l_adjustment_num NUMBER;
2017
2018 --
2019 -- Landed Cost Calculation engine is a process that captures the many amounts
2020 -- of a given Landed Cost Shipment and prorates them down at the level of
2021 -- Shipment Lines.
2022 -- At the end of this process, all Shipment Lines of all receiving
2023 -- items will have allocations coming from whatever is associated to their
2024 -- parent components, as the example below:
2025 --
2026 -- Shipment SH1 contains 2 lines:
2027 -- SL1 receiving item X, amount $100
2028 -- SL2 receiving item Y, amount $100
2029 --
2030 -- $50 Charge CH1 is associated to SH1, indicating that the amount should be
2031 -- prorated into all its lines (SL1 and SL2)
2032 --
2033 -- At the end of this process, Shipment Lines will have the following allocations:
2034 --
2035 -- SL1: Allocation 1: $25 from CH1
2036 -- Allocation 2: $100 from SL1 itself
2037 --
2038 -- SL2: Allocation 1: $25 from CH1
2039 -- Allocation 2: $100 from SL2 itself
2040 --
2041 -- At the highest level, the logic of this process is divided into 2 steps:
2042 -- STEP 1: Allocation of Associated Amounts (SL1 Allocation 1, SL2 Allocation 1)
2043 -- STEP 2: Allocation of Not Associated Amounts (SL1 Allocation 2, SL2 Allocation 2)
2044 --
2045 -- Calculation Scope: 0-Run for all components
2046 -- 1-Run only for Item Price components
2047 -- 2-Run only for Charge components
2048 -- 3-Run only form Tax components
2049 --
2050
2051 BEGIN
2052
2053 INL_LOGGING_PVT.Log_BeginProc (p_module_name => g_module_name,
2054 p_procedure_name => l_api_name);
2055 --
2056 -- Standard Start of API savepoint
2057 --
2058 SAVEPOINT Run_Calculation_PVT;
2059
2060 --
2061 -- Initialize message list if p_init_msg_list is set to TRUE
2062 --
2063 IF FND_API.to_Boolean (p_init_msg_list) THEN
2064 FND_MSG_PUB.initialize;
2065 END IF;
2066
2067 --
2068 -- Standard call to check for call compatibility
2069 --
2070 IF NOT FND_API.Compatible_API_Call (l_api_version,
2071 p_api_version,
2072 l_api_name,
2073 G_PKG_NAME)
2074 THEN
2075 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2076 END IF;
2077
2078 --
2079 -- Initialize return status to SUCCESS
2080 --
2081 x_return_status := FND_API.G_RET_STS_SUCCESS;
2082
2083 --
2084 -- Obtain Adjustment Number
2085 -- This is for obtaining the latest adjustment done to the LCM shipment
2086 --
2087
2088 SELECT adjustment_num
2089 INTO l_adjustment_num
2090 FROM inl_ship_headers
2091 WHERE ship_header_id = p_ship_header_id;
2092
2093 --
2094 -- For not adjusted shipments, check for Associations in Loop
2095 --
2096
2097 IF l_adjustment_num = 0 THEN
2098
2099 l_debug_info := 'Check for Associations in Loop';
2100 INL_LOGGING_PVT.Log_Statement (p_module_name => g_module_name,
2101 p_procedure_name => l_api_name,
2102 p_debug_info => l_debug_info);
2103
2104 OPEN assoc;
2105 LOOP
2106 FETCH assoc INTO rec_assoc;
2107 EXIT WHEN assoc%NOTFOUND;
2108
2109 IF InLoop_Association (rec_assoc.ship_header_id,
2110 rec_assoc.from_parent_table_name,
2111 rec_assoc.from_parent_table_id,
2112 l_return_status)
2113 THEN
2114 FND_MESSAGE.SET_NAME('INL','INL_ERR_CHK_IN_LOOP_ASSOC');
2115 FND_MSG_PUB.Add;
2116 RAISE FND_API.G_EXC_ERROR;
2117 END IF;
2118
2119 -- If any errors happen abort API.
2120 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2121 RAISE FND_API.G_EXC_ERROR;
2122 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2123 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2124 END IF;
2125
2126 END LOOP;
2127 CLOSE assoc;
2128
2129 END IF;
2130
2131 --
2132 -- Remove previous allocations
2133 --
2134
2135 l_debug_info := 'Remove previous allocations';
2136 INL_LOGGING_PVT.Log_Statement (p_module_name => g_module_name,
2137 p_procedure_name => l_api_name,
2138 p_debug_info => l_debug_info);
2139
2140 DELETE FROM INL_allocations
2141 WHERE ship_header_id = p_ship_header_id
2142 AND adjustment_num = l_adjustment_num
2143 AND ((p_calc_scope_code = 3 AND from_parent_table_name = 'INL_TAX_LINES') OR
2144 (p_calc_scope_code = 2 AND from_parent_table_name = 'INL_CHARGE_LINES') OR
2145 (p_calc_scope_code = 1 AND from_parent_table_name IN ('INL_SHIP_HEADERS',
2146 'INL_SHIP_LINES',
2147 'INL_SHIP_DISTS')) OR
2148 p_calc_scope_code = 0);
2149
2150
2151 --
2152 -- Get Functional Currency
2153 --
2154
2155 l_debug_info := 'Get Functional Currency';
2159
2156 INL_LOGGING_PVT.Log_Statement (p_module_name => g_module_name,
2157 p_procedure_name => l_api_name,
2158 p_debug_info => l_debug_info);
2160 SELECT DISTINCT gl.currency_code
2161 INTO l_le_currency_code
2162 FROM gl_ledgers gl,
2163 xle_fp_ou_ledger_v l,
2164 inl_ship_headers oh
2165 WHERE gl.ledger_id = l.ledger_id
2166 AND l.legal_entity_id = oh.legal_entity_id
2167 AND oh.ship_header_id = p_ship_header_id;
2168
2169 --
2170 -- STEP 1 - Allocation of Associated Amounts
2171 --
2172
2173 l_debug_info := 'STEP 1: Allocation of Associated Amounts';
2174 INL_LOGGING_PVT.Log_Statement (p_module_name => g_module_name,
2175 p_procedure_name => l_api_name,
2176 p_debug_info => l_debug_info);
2177
2178 OPEN assoc;
2179 LOOP
2180 FETCH assoc INTO rec_assoc;
2181 EXIT WHEN assoc%NOTFOUND;
2182
2183 l_debug_info := 'Fetching Associations cursor';
2184 INL_LOGGING_PVT.Log_Statement (p_module_name => g_module_name,
2185 p_procedure_name => l_api_name,
2186 p_debug_info => l_debug_info);
2187
2188 l_amount := 0;
2189 l_from_amount := 0;
2190 l_to_amount := 0;
2191
2192 l_debug_info := 'rec_assoc.from_parent_table_name';
2193 INL_LOGGING_PVT.Log_Variable (p_module_name => g_module_name,
2194 p_procedure_name => l_api_name,
2195 p_var_name => l_debug_info,
2196 p_var_value => rec_assoc.from_parent_table_name);
2197
2198 l_debug_info := 'rec_assoc.from_parent_table_id';
2199 INL_LOGGING_PVT.Log_Variable (p_module_name => g_module_name,
2200 p_procedure_name => l_api_name,
2201 p_var_name => l_debug_info,
2202 p_var_value => TO_CHAR(rec_assoc.from_parent_table_id));
2203
2204
2205 l_debug_info := 'rec_assoc.to_parent_table_name';
2206 INL_LOGGING_PVT.Log_Variable (p_module_name => g_module_name,
2207 p_procedure_name => l_api_name,
2208 p_var_name => l_debug_info,
2209 p_var_value => rec_assoc.to_parent_table_name);
2210
2211
2212 l_debug_info := 'rec_assoc.to_parent_table_id';
2213 INL_LOGGING_PVT.Log_Variable (p_module_name => g_module_name,
2214 p_procedure_name => l_api_name,
2215 p_var_name => l_debug_info,
2216 p_var_value => TO_CHAR(rec_assoc.to_parent_table_id));
2217
2218 l_debug_info := 'rec_assoc.association_id';
2219 INL_LOGGING_PVT.Log_Variable (p_module_name => g_module_name,
2220 p_procedure_name => l_api_name,
2221 p_var_name => l_debug_info,
2222 p_var_value => rec_assoc.association_id);
2223
2224
2225 IF rec_assoc.from_parent_table_name = 'INL_CHARGE_LINES' THEN
2226 SELECT Converted_Amt (charge_amt,
2227 currency_code,
2228 l_le_currency_code,
2229 currency_conversion_type,
2230 currency_conversion_date)
2231 INTO l_from_amount
2232 FROM INL_adj_charge_lines_v
2233 WHERE charge_line_id = rec_assoc.from_parent_table_id;
2234 ELSIF rec_assoc.from_parent_table_name = 'INL_TAX_LINES' THEN
2235 SELECT NVL(SUM(nrec_tax_amt),0)
2236 INTO l_from_amount
2237 FROM INL_tax_lines
2238 WHERE tax_line_id = rec_assoc.from_parent_table_id;
2239 ELSIF rec_assoc.from_parent_table_name = 'INL_SHIP_HEADERS' THEN
2240 SELECT SUM(NVL(primary_qty,0)*NVL(fc_primary_unit_price,0))
2241 INTO l_from_amount
2242 FROM inl_adj_ship_lines_v
2243 WHERE ship_header_id = rec_assoc.from_parent_table_id;
2244 ELSIF rec_assoc.from_parent_table_name = 'INL_SHIP_LINES' THEN
2245 -- bug 7660824
2246 SELECT SUM(NVL(primary_qty,0)*NVL(DECODE(landed_cost_flag,'Y',fc_primary_unit_price,0),0))
2247 INTO l_from_amount
2248 FROM inl_adj_ship_lines_v
2249 WHERE ship_line_id = rec_assoc.from_parent_table_id;
2250 END IF;
2251
2252 l_debug_info := 'l_from_amount';
2253 INL_LOGGING_PVT.Log_Variable (p_module_name => g_module_name,
2254 p_procedure_name => l_api_name,
2255 p_var_name => l_debug_info,
2256 p_var_value => TO_CHAR(l_from_amount));
2257
2258 l_debug_info := 'rec_assoc.to_parent_table_name';
2259 INL_LOGGING_PVT.Log_Variable (p_module_name => g_module_name,
2260 p_procedure_name => l_api_name,
2261 p_var_name => l_debug_info,
2262 p_var_value => rec_assoc.to_parent_table_name);
2263
2264 l_amount := l_from_amount;
2265
2266 l_debug_info := 'l_amount';
2267 INL_LOGGING_PVT.Log_Variable (p_module_name => g_module_name,
2268 p_procedure_name => l_api_name,
2269 p_var_name => l_debug_info,
2270 p_var_value => TO_CHAR(l_amount));
2271
2272
2273 l_debug_info := 'Call to Manage_Proportion';
2274 INL_LOGGING_PVT.Log_Statement (p_module_name => g_module_name,
2278 Manage_Proportion (rec_assoc.ship_header_id,
2275 p_procedure_name => l_api_name,
2276 p_debug_info => l_debug_info);
2277
2279 l_le_currency_code,
2280 rec_assoc.from_parent_table_name,
2281 rec_assoc.from_parent_table_id,
2282 rec_assoc.to_parent_table_name,
2283 rec_assoc.to_parent_table_id,
2284 rec_assoc.allocation_basis,
2285 rec_assoc.allocation_uom_code,
2286 l_factor,
2287 l_return_status);
2288 -- If any errors happen abort API.
2289 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2290 RAISE FND_API.G_EXC_ERROR;
2291 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2292 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2293 END IF;
2294
2295 l_amount := l_amount * l_factor;
2296
2297 l_debug_info := 'Call to Control_Allocation';
2298 INL_LOGGING_PVT.Log_Statement (p_module_name => g_module_name,
2299 p_procedure_name => l_api_name,
2300 p_debug_info => l_debug_info);
2301
2302 Control_Allocation (rec_assoc.ship_header_id,
2303 l_le_currency_code,
2304 rec_assoc.association_id,
2305 rec_assoc.allocation_basis,
2306 rec_assoc.allocation_uom_code,
2307 l_amount,
2308 rec_assoc.from_parent_table_name,
2309 rec_assoc.from_parent_table_id,
2310 rec_assoc.to_parent_table_name,
2311 rec_assoc.to_parent_table_id,
2312 l_adjustment_num,
2313 l_return_status);
2314 -- If any errors happen abort API.
2315 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2316 RAISE FND_API.G_EXC_ERROR;
2320 END LOOP;
2317 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2318 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2319 END IF;
2321 CLOSE assoc;
2322
2323 --
2324 -- STEP 2 - Allocation of Not Associated Amounts
2325 --
2326
2327 l_debug_info := 'STEP 2 - Allocation of Not Associated Amounts';
2328 INL_LOGGING_PVT.Log_Statement (p_module_name => g_module_name,
2329 p_procedure_name => l_api_name,
2330 p_debug_info => l_debug_info);
2331
2332 l_count := 0;
2333
2334 OPEN dist;
2335 LOOP
2336 FETCH dist INTO rec_dist;
2337 EXIT WHEN dist%NOTFOUND;
2338
2339 l_count := 0;
2340
2341 l_debug_info := 'Fetching Distributions cursor';
2342 INL_LOGGING_PVT.Log_Statement (p_module_name => g_module_name,
2343 p_procedure_name => l_api_name,
2344 p_debug_info => l_debug_info);
2345
2346 SELECT COUNT(*) + l_count
2347 INTO l_count
2348 FROM INL_adj_associations_v
2349 WHERE from_parent_table_name = 'INL_SHIP_LINES'
2350 AND from_parent_table_id = rec_dist.ship_line_id;
2351
2352 SELECT COUNT(*) + l_count
2353 INTO l_count
2354 FROM INL_adj_associations_v
2355 WHERE from_parent_table_name = 'INL_SHIP_HEADERS'
2356 AND from_parent_table_id = rec_dist.ship_header_id;
2357
2358 SELECT DECODE(l_count,0,'Y','N') INTO l_lc_flag FROM DUAL;
2359
2360 l_debug_info := 'rec_dist.ship_line_id';
2361 INL_LOGGING_PVT.Log_Variable (p_module_name => g_module_name,
2362 p_procedure_name => l_api_name,
2363 p_var_name => l_debug_info,
2364 p_var_value => TO_CHAR(rec_dist.ship_line_id));
2365
2366 l_debug_info := 'l_lc_flag';
2367 INL_LOGGING_PVT.Log_Variable (p_module_name => g_module_name,
2368 p_procedure_name => l_api_name,
2369 p_var_name => l_debug_info,
2370 p_var_value => l_lc_flag);
2371
2372 -- Get Inclusive Taxes
2373
2374 l_inclusive_tax_amt := 0;
2375
2376 SELECT SUM(NVL(al.allocation_amt,0))
2377 INTO l_inclusive_tax_amt
2378 FROM inl_tax_lines t,
2379 inl_adj_associations_v assoc,
2380 inl_allocations al
2381 WHERE t.tax_amt_included_flag = 'Y'
2382 AND t.tax_line_id = assoc.from_parent_table_id
2383 AND assoc.from_parent_table_name = 'INL_TAX_LINES'
2384 AND assoc.association_id = al.association_id
2385 AND al.ship_line_id = rec_dist.ship_line_id
2386 AND al.adjustment_num = l_adjustment_num;
2387
2388 Insert_Allocation (p_ship_header_id,
2389 l_le_currency_code,
2390 NULL,
2391 rec_dist.ship_line_id,
2392 (NVL(rec_dist.primary_qty,0)*NVL(rec_dist.fc_primary_unit_price,0))-NVL(l_inclusive_tax_amt,0),
2393 'INL_SHIP_DISTS',
2394 rec_dist.ship_line_id,
2395 'INL_SHIP_DISTS',
2396 rec_dist.ship_line_id,
2397 l_lc_flag,
2398 l_adjustment_num,
2399 l_return_status);
2400 -- If any errors happen abort API.
2401 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2402 RAISE FND_API.G_EXC_ERROR;
2403 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2404 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2405 END IF;
2406
2407 END LOOP;
2408 CLOSE dist;
2409
2410 OPEN charge;
2411 LOOP
2412 FETCH charge INTO rec_charge;
2413 EXIT WHEN charge%NOTFOUND;
2414
2415 Insert_Allocation (p_ship_header_id,
2416 l_le_currency_code,
2417 NULL,
2418 NULL,
2419 Converted_Amt (rec_charge.charge_amt,
2420 rec_charge.currency_code,
2421 l_le_currency_code,
2422 rec_charge.currency_conversion_type,
2423 rec_charge.currency_conversion_date),
2424 'INL_CHARGE_LINES',
2425 rec_charge.charge_line_id,
2426 'INL_CHARGE_LINES',
2427 rec_charge.charge_line_id,
2428 'N',
2429 l_adjustment_num,
2430 l_return_status);
2431 -- If any errors happen abort API.
2432 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2433 RAISE FND_API.G_EXC_ERROR;
2434 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2435 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2436 END IF;
2437
2438 END LOOP;
2439 CLOSE charge;
2440
2441 OPEN tax;
2442 LOOP
2443 FETCH tax INTO rec_tax;
2444 EXIT WHEN tax%NOTFOUND;
2445
2446 Insert_Allocation (p_ship_header_id,
2447 l_le_currency_code,
2448 NULL,
2449 NULL,
2450 NVL(rec_tax.tax_amt,0),
2451 'INL_TAX_LINES',
2452 rec_tax.tax_line_id,
2453 'INL_TAX_LINES',
2454 rec_tax.tax_line_id,
2455 'N',
2456 l_adjustment_num,
2457 l_return_status);
2458 -- If any errors happen abort API.
2459 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2460 RAISE FND_API.G_EXC_ERROR;
2461 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2462 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2463 END IF;
2464
2465 END LOOP;
2466 CLOSE tax;
2467
2468 -- bug #7674125
2469 -- Update Allocations with Parent_Allocation_Id
2470 Update_Allocation (p_ship_header_id,
2471 l_adjustment_num,
2472 x_return_status);
2473 -- If any errors happen abort API.
2474 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2475 RAISE FND_API.G_EXC_ERROR;
2476 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2477 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2478 END IF;
2479
2480 --
2481 -- Standard check for commit
2482 --
2483 IF FND_API.To_Boolean (p_commit) THEN
2484 COMMIT WORK;
2485 END IF;
2486
2487 -- Standard call to get message count and if count is 1, get message info.
2488 FND_MSG_PUB.Count_And_Get (p_encoded => FND_API.g_false,
2489 p_count => x_msg_count,
2490 p_data => x_msg_data);
2491
2492 INL_LOGGING_PVT.Log_EndProc (p_module_name => g_module_name,
2493 p_procedure_name => l_api_name);
2494
2495 EXCEPTION
2496 WHEN FND_API.G_EXC_ERROR THEN
2497 INL_LOGGING_PVT.Log_ExpecError (p_module_name => g_module_name,
2498 p_procedure_name => l_api_name);
2499 ROLLBACK TO Run_Calculation_PVT;
2500 x_return_status := FND_API.G_RET_STS_ERROR;
2501 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.g_false,
2502 p_count => x_msg_count,
2503 p_data => x_msg_data);
2504 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2505 INL_LOGGING_PVT.Log_UnexpecError (p_module_name => g_module_name,
2506 p_procedure_name => l_api_name);
2507 ROLLBACK TO Run_Calculation_PVT;
2508 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2509 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.g_false,
2510 p_count => x_msg_count,
2511 p_data => x_msg_data);
2512 WHEN OTHERS THEN
2513 INL_LOGGING_PVT.Log_UnexpecError (p_module_name => g_module_name,
2514 p_procedure_name => l_api_name);
2515 ROLLBACK TO Run_Calculation_PVT;
2516 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2517 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2518 THEN
2519 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,l_api_name);
2520 END IF;
2521 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.g_false,
2522 p_count => x_msg_count,
2523 p_data => x_msg_data);
2524
2525 END Run_Calculation;
2526 END INL_LANDEDCOST_PVT;