[Home] [Help]
PACKAGE BODY: APPS.QP_MULTI_CURRENCY_PVT
Source
1 PACKAGE BODY QP_MULTI_CURRENCY_PVT AS
2 /* $Header: QPXCONVB.pls 120.0 2005/06/04 08:48:26 appldev noship $ */
3
4 -- Global constant holding the package name
5
6 G_PKG_NAME CONSTANT VARCHAR2(30) := 'QP_MULTI_CURRENCY_PVT';
7 l_debug VARCHAR2(3);
8
9 -- Procedure to get a value from a given formula
10 PROCEDURE Process_Formula_API
11 (
12 l_insert_into_tmp IN VARCHAR2
13 ,l_price_formula_id IN NUMBER
14 ,l_operand_value IN NUMBER
15 ,l_pricing_effective_date IN DATE
16 ,l_line_index IN NUMBER
17 ,l_modifier_value IN NUMBER
18 ,l_formula_based_value OUT NOCOPY NUMBER
19 ,l_return_status OUT NOCOPY VARCHAR2
20 )
21 IS
22
23 l_status VARCHAR2(1);
24 BEGIN
25 l_debug := QP_PREQ_GRP.G_DEBUG_ENGINE;
26 IF l_debug = FND_API.G_TRUE THEN
27 qp_preq_grp.engine_debug('QP_MULTI_CURRENCY_PVT.Process_Formula_API - l_insert_into_tmp = '
28 || l_insert_into_tmp);
29 qp_preq_grp.engine_debug('QP_MULTI_CURRENCY_PVT.Process_Formula_API - l_price_formula_id = '
30 || l_price_formula_id);
31 qp_preq_grp.engine_debug('QP_MULTI_CURRENCY_PVT.Process_Formula_API - l_operand_value = '
32 || l_operand_value);
33 qp_preq_grp.engine_debug('QP_MULTI_CURRENCY_PVT.Process_Formula_API - l_pricing_effective_date = '
34 || l_pricing_effective_date);
35 qp_preq_grp.engine_debug('QP_MULTI_CURRENCY_PVT.Process_Formula_API - l_line_index = '
36 || l_line_index);
37 qp_preq_grp.engine_debug('QP_MULTI_CURRENCY_PVT.Process_Formula_API - l_modifier_value = '
38 || l_modifier_value);
39 qp_preq_grp.engine_debug('QP_MULTI_CURRENCY_PVT.Process_Formula_API - l_formula_based_value = '
40 || l_formula_based_value);
41 qp_preq_grp.engine_debug('QP_MULTI_CURRENCY_PVT.Process_Formula_API - l_return_status = '
42 || l_return_status);
43
44 END IF;
45 l_return_status := FND_API.G_RET_STS_SUCCESS;
46
47 IF l_insert_into_tmp = 'Y' THEN
48
49 -- Change flexible mask to mask below for formula pattern use
50 qp_number.canonical_mask :=
51 '00999999999999999999999.99999999999999999999999999999999999999';
52
53 delete from qp_preq_line_attrs_formula_tmp;
54
55 IF qp_util.attrmgr_installed = 'Y' THEN
56 -- Insert request line attributes with datatype = 'N'
57 INSERT INTO
58 qp_preq_line_attrs_formula_tmp
59 (
60 line_index
61 ,attribute_type
62 ,context
63 ,attribute
64 ,pricing_status_code
65 ,value_from
66 )
67 SELECT
68 line_index
69 ,attribute_type
70 ,context
71 ,attribute
72 ,pricing_status_code
73 ,qp_number.number_to_canonical(to_number(value_from))
74 FROM
75 qp_npreq_line_attrs_tmp lattr
76 WHERE
77 attribute_type IN ('PRICING', 'PRODUCT')
78 AND
79 pricing_status_code = QP_PREQ_GRP.G_STATUS_UNCHANGED
80 AND
81 EXISTS(
82 SELECT format_type
83 FROM fnd_flex_value_sets VSET,
84 qp_segments_b SEGMENTS, qp_prc_contexts_b PCONTEXTS
85 WHERE vset.flex_value_set_id = segments.user_valueset_id
86 AND segments.application_id = 661
87 AND pcontexts.prc_context_type <> 'QUALIFIER'
88 AND pcontexts.prc_context_code = lattr.context
89 AND segments.segment_mapping_column = lattr.attribute
90 AND segments.prc_context_id = pcontexts.prc_context_id
91 AND vset.format_type = 'N'
92 );
93 -- Insert request line attributes with datatype 'X','Y','C' or null
94 INSERT INTO
95 qp_preq_line_attrs_formula_tmp
96 (
97 line_index
98 ,attribute_type
99 ,context
100 ,attribute
101 ,pricing_status_code
102 ,value_from
103 )
104 SELECT
105 line_index
106 ,attribute_type
107 ,context
108 ,attribute
109 ,pricing_status_code
110 ,value_from
111 FROM
112 qp_npreq_line_attrs_tmp lattr
113 WHERE
114 attribute_type IN ('PRICING', 'PRODUCT')
115 AND
116 pricing_status_code = QP_PREQ_GRP.G_STATUS_UNCHANGED
117 AND
118 NOT EXISTS(
119 SELECT format_type
120 FROM fnd_flex_value_sets VSET,
121 qp_segments_b SEGMENTS, qp_prc_contexts_b PCONTEXTS
122 WHERE vset.flex_value_set_id = segments.user_valueset_id
123 AND segments.application_id = 661
124 AND pcontexts.prc_context_type <> 'QUALIFIER'
125 AND pcontexts.prc_context_code = lattr.context
126 AND segments.segment_mapping_column = lattr.attribute
127 AND segments.prc_context_id = pcontexts.prc_context_id
128 AND vset.format_type = 'N'
129 );
130
131 ELSE
132 -- Insert request line attributes with datatype = 'N'
133 INSERT INTO
134 qp_preq_line_attrs_formula_tmp
135 (
136 line_index
137 ,attribute_type
138 ,context
139 ,attribute
140 ,pricing_status_code
141 ,value_from
142 )
143 SELECT
144 line_index
145 ,attribute_type
146 ,context
147 ,attribute
148 ,pricing_status_code
149 ,qp_number.number_to_canonical(to_number(value_from))
150 FROM
151 qp_npreq_line_attrs_tmp lattr
152 WHERE
153 attribute_type IN ('PRICING', 'PRODUCT')
154 AND
155 pricing_status_code = QP_PREQ_GRP.G_STATUS_UNCHANGED
156 AND
157 EXISTS(
158 SELECT format_type
159 FROM fnd_flex_value_sets vset
160 ,fnd_descr_flex_column_usages dflex
161 WHERE vset.flex_value_set_id = dflex.flex_value_set_id
162 AND dflex.application_id = 661
163 AND dflex.descriptive_flexfield_name = 'QP_ATTR_DEFNS_PRICING'
164 AND dflex.descriptive_flex_context_code = lattr.context
165 AND dflex.application_column_name = lattr.attribute
166 AND vset.format_type = 'N'
167 );
168 -- AND
169 -- NOT EXISTS
170 -- (SELECT 'x' FROM qp_preq_line_attrs_formula_tmp
171 -- WHERE line_index = lattr.line_index);
172
173 -- Insert request line attributes with datatype 'X','Y','C' or null
174 INSERT INTO
175 qp_preq_line_attrs_formula_tmp
176 (
177 line_index
178 ,attribute_type
179 ,context
180 ,attribute
181 ,pricing_status_code
182 ,value_from
183 )
184 SELECT
185 line_index
186 ,attribute_type
187 ,context
188 ,attribute
189 ,pricing_status_code
190 ,value_from
191 FROM
192 qp_npreq_line_attrs_tmp lattr
193 WHERE
194 attribute_type IN ('PRICING', 'PRODUCT')
195 AND
196 pricing_status_code = QP_PREQ_GRP.G_STATUS_UNCHANGED
197 AND
198 NOT EXISTS(
199 SELECT format_type
200 FROM fnd_flex_value_sets vset
201 ,fnd_descr_flex_column_usages dflex
202 WHERE vset.flex_value_set_id = dflex.flex_value_set_id
203 AND dflex.application_id = 661
204 AND dflex.descriptive_flexfield_name = 'QP_ATTR_DEFNS_PRICING'
205 AND dflex.descriptive_flex_context_code = lattr.context
206 AND dflex.application_column_name = lattr.attribute
207 -- AND vset.format_type IN ('X','Y','C')
208 AND vset.format_type = 'N'
209 );
210 --AND
211 -- NOT EXISTS
212 -- (SELECT 'x' FROM qp_preq_line_attrs_formula_tmp
213 -- WHERE line_index = lattr.line_index);
214
215 END IF; -- attribute manager installed
216 --Change mask back to flexible mask.
217 qp_number.canonical_mask :=
218 'FM999999999999999999999.9999999999999999999999999999999999999999';
219
220 END IF;
221
222
223 l_formula_based_value :=
224 QP_FORMULA_PRICE_CALC_PVT.Calculate
225 ( p_price_formula_id => l_price_formula_id
226 ,p_list_price => l_operand_value
227 ,p_price_effective_date => l_pricing_effective_date
228 ,p_line_index => l_line_index
229 ,p_list_line_type_code => NULL
230 ,x_return_status => l_status
231 ,p_modifier_value => l_modifier_value
232 );
233
234 IF l_status <> FND_API.G_RET_STS_SUCCESS THEN
235
236 l_return_status := FND_API.G_RET_STS_ERROR;
237
238 END IF;
239
240
241 IF l_debug = FND_API.G_TRUE THEN
242 QP_PREQ_GRP.engine_debug('Formula return status: '||l_status);
243 QP_PREQ_GRP.engine_debug('Formula base rate: '|| l_formula_based_value);
244
245 IF l_status IS NULL THEN
246 QP_PREQ_GRP.engine_debug('Formula return status is NULL');
247 QP_PREQ_GRP.engine_debug('FND_API.G_RET_STS_ERROR: '||FND_API.G_RET_STS_ERROR);
248 null;
249 END IF;
250
251 END IF;
252 EXCEPTION
253
254 WHEN OTHERS THEN
255
256 IF l_debug = FND_API.G_TRUE THEN
257 qp_preq_grp.engine_debug('QP_MULTI_CURRENCY_PVT.Process_Formula_API - OTHERS exception '
258 || SQLERRM);
259 END IF;
260 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
261
262 --RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
263
264
265 END Process_Formula_API;
266
267
268 PROCEDURE Currency_Conversion_API
269 ( p_user_conversion_rate IN NUMBER
270 ,p_user_conversion_type IN VARCHAR2
271 ,p_function_currency IN VARCHAR2
272 ,p_rounding_flag IN VARCHAR2
273 )
274
275 IS
276
277 l_conversion_rate NUMBER;
278 l_operand_value NUMBER;
279 l_insert_into_tmp VARCHAR2(1);
280 l_formula_based_value NUMBER;
281 l_conversion_date DATE;
282
283 l_process_status VARCHAR2(1);
284 l_formula_status VARCHAR2(1);
285 l_modifier_value NUMBER;
286 l_round_price_status VARCHAR2(1);
287
288 rows NATURAL := 5000;
289
290 TYPE line_index_tab IS TABLE OF qp_npreq_ldets_tmp.line_index%TYPE INDEX BY BINARY_INTEGER;
291 TYPE line_detail_index_tab IS TABLE OF qp_npreq_ldets_tmp.line_detail_index%TYPE INDEX BY BINARY_INTEGER;
292 TYPE operand_value_tab IS TABLE OF qp_npreq_ldets_tmp.operand_value%TYPE INDEX BY BINARY_INTEGER;
293 TYPE operand_calc_code_tab IS TABLE OF qp_npreq_ldets_tmp.operand_calculation_code%TYPE INDEX BY BINARY_INTEGER;
294 TYPE base_currency_code_tab IS TABLE OF qp_npreq_ldets_tmp.base_currency_code%TYPE INDEX BY BINARY_INTEGER;
295 TYPE pricing_effective_date_tab IS TABLE OF qp_npreq_ldets_tmp.pricing_effective_date%TYPE INDEX BY BINARY_INTEGER;
296 TYPE currency_header_id_tab IS TABLE OF qp_currency_details.currency_header_id%TYPE INDEX BY BINARY_INTEGER;
297 TYPE to_currency_code_tab IS TABLE OF qp_currency_details.to_currency_code%TYPE INDEX BY BINARY_INTEGER;
298 TYPE fixed_value_tab IS TABLE OF qp_currency_details.fixed_value%TYPE INDEX BY BINARY_INTEGER;
299 TYPE price_formula_id_tab IS TABLE OF qp_currency_details.price_formula_id%TYPE INDEX BY BINARY_INTEGER;
300 TYPE conversion_type_tab IS TABLE OF qp_currency_details.conversion_type%TYPE INDEX BY BINARY_INTEGER;
301 TYPE conversion_date_type_tab IS TABLE OF qp_currency_details.conversion_date_type%TYPE INDEX BY BINARY_INTEGER;
302 TYPE conversion_date_tab IS TABLE OF qp_currency_details.conversion_date%TYPE INDEX BY BINARY_INTEGER;
303 TYPE rounding_factor_tab IS TABLE OF qp_currency_details.rounding_factor%TYPE INDEX BY BINARY_INTEGER;
304 TYPE markup_operator_tab IS TABLE OF qp_currency_details.markup_operator%TYPE INDEX BY BINARY_INTEGER;
305 TYPE markup_value_tab IS TABLE OF qp_currency_details.markup_value%TYPE INDEX BY BINARY_INTEGER;
306 TYPE markup_formula_id_tab IS TABLE OF qp_currency_details.markup_formula_id%TYPE INDEX BY BINARY_INTEGER;
307 TYPE error_message_tab IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER;
308 TYPE status_code_tab IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
309 TYPE lines_status_code_tab IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
310
311
312 line_index_t line_index_tab;
313 line_detail_index_t line_detail_index_tab;
314 operand_value_t operand_value_tab;
315 base_currency_code_t base_currency_code_tab;
316 pricing_effective_date_t pricing_effective_date_tab;
317 currency_header_id_t currency_header_id_tab;
318 to_currency_code_t to_currency_code_tab;
319 fixed_value_t fixed_value_tab;
320 price_formula_id_t price_formula_id_tab;
321 conversion_type_t conversion_type_tab;
322 conversion_date_type_t conversion_date_type_tab;
323 conversion_date_t conversion_date_tab;
324 rounding_factor_t rounding_factor_tab;
325 markup_operator_t markup_operator_tab;
326 markup_value_t markup_value_tab;
327 markup_formula_id_t markup_formula_id_tab;
328
329 result_operand_value_t operand_value_tab;
330 error_message_t error_message_tab;
331 status_code_t status_code_tab;
332 lines_status_code_t lines_status_code_tab;
333 operand_calc_code_t operand_calc_code_tab;
334
335 -- Added operand_calculation_code in ('UNIT_PRICE','BLOCK_PRICE','BREAKUNIT_PRICE') in the where
336 -- condition. operand_calculation_code in qp_npreq_ldets_tmp is the same as arithmetic_operator in
337 -- qp_list_lines. This is to not process the currency conversion of service items which have
338 -- PERCENT_PRICE as the aritmetic operator . This is because the pricing engine computes the
339 -- price of the parent item after doing the currency conversion and them simply applies the
340 -- percent price on the parent item's price to compute the price of the service item.
341
342 CURSOR c_currency_conversions
343 IS
344
345 SELECT
346 b.line_index
347 ,b.line_detail_index
348 ,b.operand_value
349 ,b.operand_calculation_code
350 ,b.base_currency_code
351 ,b.pricing_effective_date
352 ,a.currency_header_id
353 ,a.to_currency_code
354 ,a.fixed_value
355 ,a.price_formula_id
356 ,a.conversion_type
357 ,a.conversion_date_type
358 ,a.conversion_date
359 ,a.rounding_factor
360 ,a.markup_operator
361 ,a.markup_value
362 ,a.markup_formula_id
363 FROM
364 qp_currency_details a,
365 qp_npreq_ldets_tmp b
366 --j_qp_npreq_ldets_tmp b
367 WHERE
368 a.currency_header_id = b.currency_header_id
369 AND a.to_currency_code = b.order_currency
370 AND a.currency_detail_id = b.currency_detail_id
371 AND TRUNC(b.pricing_effective_date) >= TRUNC(nvl(a.start_date_active,b.pricing_effective_date))
372 AND TRUNC(b.pricing_effective_date) <= TRUNC(nvl(a.end_date_active, b.pricing_effective_date))
373 AND b.created_from_list_type_code IN ('PRL', 'AGR')
374 AND b.operand_calculation_code IN ('UNIT_PRICE','BLOCK_PRICE','BREAKUNIT_PRICE')
375 AND b.pricing_status_code = 'N';
376
377
378 BEGIN
379
380 --If there is a formula, then need to insert pricing attributes once for each run
381 l_debug := QP_PREQ_GRP.G_DEBUG_ENGINE;
382 IF l_debug = FND_API.G_TRUE THEN
383 qp_preq_grp.engine_debug('Currency_Conversion_API - Enter');
384 END IF;
385
386 IF l_debug = FND_API.G_TRUE THEN
387 qp_preq_grp.engine_debug('Currency_Conversion_API - p_user_conversion_rate' || p_user_conversion_rate);
388 qp_preq_grp.engine_debug('Currency_Conversion_API - p_user_conversion_type' || p_user_conversion_type);
389 qp_preq_grp.engine_debug('Currency_Conversion_API - p_function_currency' || p_function_currency);
390 qp_preq_grp.engine_debug('Currency_Conversion_API - p_rounding_flag' || p_rounding_flag);
391
392 END IF;
393 OPEN c_currency_conversions;
394
395 LOOP
396
397 line_index_t.DELETE;
398 line_detail_index_t.DELETE;
399 operand_value_t.DELETE;
400 operand_calc_code_t.DELETE;
401 base_currency_code_t.DELETE;
402 pricing_effective_date_t.DELETE;
403 currency_header_id_t.DELETE;
404 to_currency_code_t.DELETE;
405 fixed_value_t.DELETE;
406 price_formula_id_t.DELETE;
407 conversion_type_t.DELETE;
408 conversion_date_type_t.DELETE;
409 conversion_date_t.DELETE;
410 rounding_factor_t.DELETE;
411 markup_operator_t.DELETE;
412 markup_value_t.DELETE;
413 markup_formula_id_t.DELETE;
414 result_operand_value_t.DELETE;
415 error_message_t.DELETE;
416 status_code_t.DELETE;
417 lines_status_code_t.DELETE;
418
419 -- Bulk Fetch 5000 rows each time for performance
420 FETCH c_currency_conversions BULK COLLECT INTO
421 line_index_t,
422 line_detail_index_t,
423 operand_value_t,
424 operand_calc_code_t,
425 base_currency_code_t,
426 pricing_effective_date_t,
427 currency_header_id_t,
428 to_currency_code_t,
429 fixed_value_t,
430 price_formula_id_t,
431 conversion_type_t,
432 conversion_date_type_t,
433 conversion_date_t,
434 rounding_factor_t,
435 markup_operator_t,
436 markup_value_t,
437 markup_formula_id_t
438 LIMIT rows;
439
440
441 IF l_debug = FND_API.G_TRUE THEN
442 qp_preq_grp.engine_debug('Currency_Conversion_API - record count = '||line_index_t.count);
443 END IF;
444 IF line_index_t.count > 0 THEN
445 --process every record in this fetch
446
447 FOR J IN line_index_t.FIRST..line_index_t.LAST
448 LOOP
449
450 BEGIN
451
452 l_insert_into_tmp := 'Y';
453
454 --l_process_status := FND_API.G_RET_STS_SUCCESS;
455
456 error_message_t(J) := NULL;
457
458 -- status_code_t is used for updating the qp_npreq_ldets_tmp table Bug 2327718
459
460 status_code_t(J) := 'N';
461
462 -- Added lines_status_code_t for updating the qp_npreq_lines_tmp table Bug 2327718
463
464 lines_status_code_t(J) := 'UPDATED';
465
466 l_conversion_rate := NULL;
467 l_operand_value := NULL;
468 result_operand_value_t(J) := NULL;
469
470 IF l_debug = FND_API.G_TRUE THEN
471 qp_preq_grp.engine_debug('Currency_Conversion_API - conversion_type = '||conversion_type_t(J));
472
473 END IF;
474 IF conversion_type_t(J) = 'FIXED' THEN
475
476 -- Use the fixed value
477 l_conversion_rate := fixed_value_t(J);
478
479 IF l_debug = FND_API.G_TRUE THEN
480 qp_preq_grp.engine_debug('Currency_Conversion_API - FIXED l_conversion_rate = '
481 ||l_conversion_rate);
482 END IF;
483
484 ELSIF conversion_type_t(J) = 'TRANSACTION' THEN
485
486 -- Use the conversion type and rate passed from OM
487 IF --p_user_conversion_type = 'USER' AND
488 p_user_conversion_rate is NOT NULL THEN
489
490 IF p_function_currency = base_currency_code_t(J) THEN
491
492 -- Only when function and base currency are same, use the OM rate
493
494 IF l_debug = FND_API.G_TRUE THEN
495 qp_preq_grp.engine_debug('Currency_Conversion_API - TRANSACTION function and base currency same ');
496 END IF;
497
498 -- [julin/4099147] integrating apps users expect entered rate to
499 -- be from transaction currency to functional currency
500 l_conversion_rate := 1/p_user_conversion_rate;
501 IF l_debug = FND_API.G_TRUE THEN
502 qp_preq_grp.engine_debug('Currency_Conversion_API - TRANSACTION l_conversion_rate = '
503 ||l_conversion_rate);
504
505 END IF;
506 ELSE
507
508 IF l_debug = FND_API.G_TRUE THEN
509 qp_preq_grp.engine_debug('Currency_Conversion_API - TRANSACTION function and base currency different ');
510 END IF;
511 -- Function and base currency not same, raise error
512 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
513 FND_MESSAGE.SET_NAME('QP', 'QP_DIFF_FUNC_AND_BASE_CURR');
514 FND_MESSAGE.SET_TOKEN('BASE_CURR', base_currency_code_t(J));
515 FND_MESSAGE.SET_TOKEN('FUNC_CURR', p_function_currency);
516
517 error_message_t(J) := FND_MESSAGE.GET;
518 status_code_t(J) := QP_PREQ_GRP.G_STATUS_OTHER_ERRORS;
519 lines_status_code_t(J) := QP_PREQ_GRP.G_STATUS_OTHER_ERRORS;
520
521 END IF;
522
523 RAISE FND_API.G_EXC_ERROR;
524
525 END IF;
526
527 ELSIF p_user_conversion_type IS NOT NULL THEN
528 -- p_user_conversion_type <> 'USER' THEN
529
530 IF l_debug = FND_API.G_TRUE THEN
531 qp_preq_grp.engine_debug('Currency_Conversion_API - x_from_currency = '||base_currency_code_t(J) );
532 qp_preq_grp.engine_debug('Currency_Conversion_API - x_to_currency = '|| to_currency_code_t(J));
533 qp_preq_grp.engine_debug('Currency_Conversion_API - x_conversion_date = '|| pricing_effective_date_t(J));
534 qp_preq_grp.engine_debug('Currency_Conversion_API - x_conversion_type = '|| p_user_conversion_type);
535
536 END IF;
537 -- use the functional conversion rate defined in GL(from base currency to order currency)
538 l_conversion_rate := gl_currency_api.get_rate_sql
539 (
540 x_from_currency => base_currency_code_t(J)
541 ,x_to_currency => to_currency_code_t(J)
542 ,x_conversion_date => pricing_effective_date_t(J)
543 ,x_conversion_type => p_user_conversion_type
544 );
545
546 IF l_debug = FND_API.G_TRUE THEN
547 qp_preq_grp.engine_debug('Currency_Conversion_API - GL l_conversion_rate = '|| l_conversion_rate);
548 END IF;
549
550 IF l_conversion_rate = -1 THEN
551
552 -- No currency rate found from GL, raise error
553 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
554 FND_MESSAGE.SET_NAME('QP', 'QP_CONV_RATE_NOT_FOUND');
555 FND_MESSAGE.SET_TOKEN('FROM_CURR', base_currency_code_t(J));
556 FND_MESSAGE.SET_TOKEN('TO_CURR', to_currency_code_t(J));
557 FND_MESSAGE.SET_TOKEN('CONV_DATE', pricing_effective_date_t(J));
558 FND_MESSAGE.SET_TOKEN('CONV_TYPE', p_user_conversion_type);
559
560 error_message_t(J) := FND_MESSAGE.GET;
561 status_code_t(J) := QP_PREQ_GRP.G_STATUS_OTHER_ERRORS;
562 lines_status_code_t(J) := QP_PREQ_GRP.G_STATUS_OTHER_ERRORS;
563
564 END IF;
565
566 RAISE FND_API.G_EXC_ERROR;
567
568 ELSIF l_conversion_rate = -2 THEN
569
570 -- Base currency or/and order currency are in valid, raise error
571 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
572 FND_MESSAGE.SET_NAME('QP','QP_INVALID_CURRENCY');
573
574 error_message_t(J) := FND_MESSAGE.GET;
575 status_code_t(J) := QP_PREQ_GRP.G_STATUS_OTHER_ERRORS;
576 lines_status_code_t(J) := QP_PREQ_GRP.G_STATUS_OTHER_ERRORS;
577
578
579 END IF;
580
581 RAISE FND_API.G_EXC_ERROR;
582
583 END IF;
584
585
586 ELSE
587
588 IF l_debug = FND_API.G_TRUE THEN
589 qp_preq_grp.engine_debug('Currency_Conversion_API - TRANSACTION no conversion type ');
590 END IF;
591 -- For 'TRANSACTION' conv, OM did not pass user conversion type, raise error
592 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
593
594 FND_MESSAGE.SET_NAME('QP', 'QP_NO_USER_CONVTYPE_F_TRANSACT');
595
596 error_message_t(J) := FND_MESSAGE.GET;
597 status_code_t(J) := QP_PREQ_GRP.G_STATUS_OTHER_ERRORS;
598 lines_status_code_t(J) := QP_PREQ_GRP.G_STATUS_OTHER_ERRORS;
599
600 END IF;
601
602
603 RAISE FND_API.G_EXC_ERROR;
604
605 END IF;
606
607
608 ELSIF conversion_type_t(J) = 'FORMULA' THEN
609
610 --Call the process_formula_api to return the calculated value
611
612 l_modifier_value := NULL;
613
614 Process_Formula_API
615 (
616 l_insert_into_tmp
617 ,price_formula_id_t(J)
618 ,operand_value_t(J)
619 ,pricing_effective_date_t(J)
620 ,line_index_t(J)
621 ,l_modifier_value
622 ,l_formula_based_value
623 ,l_formula_status
624 );
625
626
627 IF l_formula_status <> FND_API.G_RET_STS_SUCCESS THEN
628
629 IF l_debug = FND_API.G_TRUE THEN
630 qp_preq_grp.engine_debug('Currency_Conversion_API - FORMULA not success');
631 END IF;
632 -- Formula calculation failed, raise error
633 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
634 FND_MESSAGE.SET_NAME('QP', 'QP_FORMULA_CALC_FAILURE');
635
636 error_message_t(J) := FND_MESSAGE.GET;
637 status_code_t(J) := QP_PREQ_GRP.G_STATUS_OTHER_ERRORS;
638 lines_status_code_t(J) := QP_PREQ_GRP.G_STATUS_OTHER_ERRORS;
639
640 END IF;
641
642 RAISE FND_API.G_EXC_ERROR;
643
644 END IF;
645
646
647
648 --Reset the insert flag so it only inserts once for each run
649 l_insert_into_tmp:= 'N';
650
651
652 -- Use the rate returned from the formula calculation
653 l_conversion_rate := l_formula_based_value;
654
655 IF l_debug = FND_API.G_TRUE THEN
656 qp_preq_grp.engine_debug('Currency_Conversion_API - FORMULA l_conversion_rate'
657 || l_conversion_rate);
658 END IF;
659 --End of processing conversion type ='FORMULA'
660
661 ELSIF conversion_type_t(J) IS NULL THEN
662
663 IF l_debug = FND_API.G_TRUE THEN
664 qp_preq_grp.engine_debug('Currency_Conversion_API - null conversion type');
665 END IF;
666 IF to_currency_code_t(J) = base_currency_code_t(J) THEN
667
668 IF l_debug = FND_API.G_TRUE THEN
669 qp_preq_grp.engine_debug('Currency_Conversion_API - null conversion type - curr matches');
670 END IF;
671 --There order currency and base currency are same, the conversion rate is 1
672
673 l_conversion_rate := 1;
674
675 END IF;
676
677
678 ELSE --All conversion types other than FIXED, TRANSACTION and FORMULA
679
680 IF conversion_date_type_t(J) = 'FIXED' THEN
681 IF l_debug = FND_API.G_TRUE THEN
682 qp_preq_grp.engine_debug('Currency_Conversion_API - date type FIXED');
683 END IF;
684 l_conversion_date := conversion_date_t(J);
685 ELSE
686 l_conversion_date := pricing_effective_date_t(J);
687 END IF;
688
689 l_conversion_rate := gl_currency_api.get_rate_sql
690 (
691 x_from_currency => base_currency_code_t(J)
692 ,x_to_currency => to_currency_code_t(J)
693 ,x_conversion_date => l_conversion_date
694 ,x_conversion_type => conversion_type_t(J)
695 );
696
697 IF l_debug = FND_API.G_TRUE THEN
698 qp_preq_grp.engine_debug('Currency_Conversion_API - GL2 l_conversion_rate'
699 || l_conversion_rate);
700 END IF;
701 IF l_conversion_rate = -1 THEN
702
703 -- No currency rate found from GL, raise error
704 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
705 FND_MESSAGE.SET_NAME('QP', 'QP_CONV_RATE_NOT_FOUND');
706 FND_MESSAGE.SET_TOKEN('FROM_CURR', base_currency_code_t(J));
707 FND_MESSAGE.SET_TOKEN('TO_CURR', to_currency_code_t(J));
708 FND_MESSAGE.SET_TOKEN('CONV_DATE', l_conversion_date);
709 FND_MESSAGE.SET_TOKEN('CONV_TYPE', conversion_type_t(J));
710
711 error_message_t(J) := FND_MESSAGE.GET;
712 status_code_t(J) := QP_PREQ_GRP.G_STATUS_OTHER_ERRORS;
713 lines_status_code_t(J) := QP_PREQ_GRP.G_STATUS_OTHER_ERRORS;
714
715 END IF;
716
717 RAISE FND_API.G_EXC_ERROR;
718
719 ELSIF l_conversion_rate = -2 THEN
720
721 -- Base currency or/and order currency are in valid, raise error
722 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
723 FND_MESSAGE.SET_NAME('QP','QP_INVALID_CURRENCY');
724
725 error_message_t(J) := FND_MESSAGE.GET;
726 status_code_t(J) := QP_PREQ_GRP.G_STATUS_OTHER_ERRORS;
727 lines_status_code_t(J) := QP_PREQ_GRP.G_STATUS_OTHER_ERRORS;
728
729 END IF;
730
731 RAISE FND_API.G_EXC_ERROR;
732
733 END IF;
734
735
736 END IF;
737
738 --End of processing of conversion_rate
739
740
741 IF l_debug = FND_API.G_TRUE THEN
742 qp_preq_grp.engine_debug('Currency_Conversion_API - operand_value = '
743 || operand_value_t(J));
744 qp_preq_grp.engine_debug('Currency_Conversion_API - l_conversion_rate = '
745 || l_conversion_rate);
746
747 END IF;
748 -- Bug 2929366 - removed the NVL so that if the operand is null,
749 -- after converting it should remain null and error could be raised
750 -- "Item and uom is not on the pricelist"
751 --result_operand_value_t(J) := NVL(operand_value_t(J), 1) * l_conversion_rate;
752 result_operand_value_t(J) := operand_value_t(J) * l_conversion_rate;
753
754 IF l_debug = FND_API.G_TRUE THEN
755 qp_preq_grp.engine_debug('Currency_Conversion_API - result_operand_value = '
756 || result_operand_value_t(J));
757 END IF;
758 -- Start processing Markup
759
760 IF l_debug = FND_API.G_TRUE THEN
761 qp_preq_grp.engine_debug('Currency_Conversion_API - markup_value = '
762 || markup_value_t(J));
763 qp_preq_grp.engine_debug('Currency_Conversion_API - markup_formula_id = '
764 || markup_formula_id_t(J));
765 qp_preq_grp.engine_debug('Currency_Conversion_API - markup_operator = '
766 || markup_operator_t(J));
767
768 END IF;
769 IF markup_value_t(J) IS NOT NULL and markup_formula_id_t(J) IS NULL THEN
770
771 IF markup_operator_t(J) = '%' THEN
772
773 result_operand_value_t(J) := result_operand_value_t(J) +
774 (result_operand_value_t(J) * (markup_value_t(J) / 100));
775 IF l_debug = FND_API.G_TRUE THEN
776 qp_preq_grp.engine_debug('Currency_Conversion_API - markup % result_operand_value = '
777 || result_operand_value_t(J));
778
779 END IF;
780 ELSIF markup_operator_t(J) = 'AMT' THEN
781
782 result_operand_value_t(J) := result_operand_value_t(J) + markup_value_t(J);
783 IF l_debug = FND_API.G_TRUE THEN
784 qp_preq_grp.engine_debug('Currency_Conversion_API - markup AMT result_operand_value = '
785 || result_operand_value_t(J));
786 END IF;
787
788 END IF;
789
790 END IF;
791
792
793
794 IF markup_formula_id_t(J) IS NOT NULL THEN
795 IF l_debug = FND_API.G_TRUE THEN
796 qp_preq_grp.engine_debug('Currency_Conversion_API - markup formula NOT null ');
797 END IF;
798
799 --call the process formula API
800
801 Process_Formula_API
802 (
803 l_insert_into_tmp
804 ,markup_formula_id_t(J)
805 ,result_operand_value_t(J)
806 ,pricing_effective_date_t(J)
807 ,line_index_t(J)
808 ,markup_value_t(J)
809 ,l_formula_based_value
810 ,l_formula_status
811 );
812
813
814 IF l_formula_status <> FND_API.G_RET_STS_SUCCESS THEN
815
816 IF l_debug = FND_API.G_TRUE THEN
817 qp_preq_grp.engine_debug('Currency_Conversion_API - markup formula fails ');
818 END IF;
819 -- Formula calculation failed, raise error
820 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
821 FND_MESSAGE.SET_NAME('QP', 'QP_FORMULA_CALC_FAILURE');
822
823 error_message_t(J) := FND_MESSAGE.GET;
824 status_code_t(J) := QP_PREQ_GRP.G_STATUS_OTHER_ERRORS;
825 lines_status_code_t(J) := QP_PREQ_GRP.G_STATUS_OTHER_ERRORS;
826
827 END IF;
828
829 RAISE FND_API.G_EXC_ERROR;
830
831 END IF;
832
833
834 l_insert_into_tmp := 'N';
835
836 IF markup_operator_t(J) = '%' THEN
837
838 result_operand_value_t(J) := result_operand_value_t(J) +
839 (result_operand_value_t(J) * (l_formula_based_value/100));
840
841 IF l_debug = FND_API.G_TRUE THEN
842 qp_preq_grp.engine_debug('Currency_Conversion_API - markup % result_operand_value = '
843 || result_operand_value_t(J));
844 END IF;
845 ELSIF markup_operator_t(J) = 'AMT' THEN
846
847 result_operand_value_t(J) := result_operand_value_t(J) + l_formula_based_value;
848 IF l_debug = FND_API.G_TRUE THEN
849 qp_preq_grp.engine_debug('Currency_Conversion_API - markup AMT result_operand_value = '
850 || result_operand_value_t(J));
851
852 END IF;
853 END IF;
854
855 END IF; --markup_formula_id_t(J) IS NOT NULL
856
857
858 -- Call Process Rounding API
859
860 -- l_conversion_rate := l_conversion_rate * Value returned from Rounding;
861 if p_rounding_flag = 'Y' then
862 IF l_debug = FND_API.G_TRUE THEN
863 qp_preq_grp.engine_debug('Currency_Conversion_API - p_rounding_flag = '
864 || p_rounding_flag);
865 END IF;
866 qp_util_pub.round_price(
867 P_OPERAND => result_operand_value_t(J)
868 ,P_ROUNDING_FACTOR => rounding_factor_t(J)
869 ,P_USE_MULTI_CURRENCY => 'Y'
870 ,P_PRICE_LIST_ID => NULL
871 ,P_CURRENCY_CODE => NULL
872 ,P_PRICING_EFFECTIVE_DATE => NULL
873 ,X_ROUNDED_OPERAND => result_operand_value_t(J)
874 ,X_STATUS_CODE => l_round_price_status
875 ,p_operand_type => 'S'
876 );
877
878 IF l_round_price_status <> 'S' THEN
879 IF l_debug = FND_API.G_TRUE THEN
880 qp_preq_grp.engine_debug('Currency_Conversion_API - round_price fails ');
881 END IF;
882
883 -- Formula calculation failed, raise error
884 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
885 error_message_t(J) := FND_MESSAGE.GET;
886 status_code_t(J) := FND_API.G_RET_STS_ERROR;
887 lines_status_code_t(J) := FND_API.G_RET_STS_ERROR;
888
889 END IF;
890
891 RAISE FND_API.G_EXC_ERROR;
892
893 END IF;
894 IF l_debug = FND_API.G_TRUE THEN
895 qp_preq_grp.engine_debug('Currency_Conversion_API - after rounding result_operand_value = '
896 || result_operand_value_t(J));
897
898 END IF;
899 END IF; -- rounding flag
900
901 EXCEPTION
902
903 WHEN FND_API.G_EXC_ERROR THEN
904 IF l_debug = FND_API.G_TRUE THEN
905 qp_preq_grp.engine_debug('Currency_Conversion_API - handle exception FND_API.G_EXC_ERROR');
906
907 END IF;
908 NULL;
909
910 --WHEN OTHERS THEN
911
912 --NULL;
913
914 END;
915
916 END LOOP;
917
918 END IF; --IF line_index_t.count>0
919
920 IF l_debug = FND_API.G_TRUE THEN
921 qp_preq_grp.engine_debug('Currency_Conversion_API - BEFORE updating the temp tables = ');
922 qp_preq_grp.engine_debug('Currency_Conversion_API - line_index_t.count' || line_index_t.count);
923 END IF;
924 --sql statement upd1
925 IF line_index_t.count > 0 THEN
926
927 IF l_debug = FND_API.G_TRUE THEN
928 qp_preq_grp.engine_debug('Currency_Conversion_API - updating the temp tables = ');
929
930 END IF;
931 FORALL K IN line_index_t.FIRST..line_index_t.LAST
932
933 UPDATE qp_npreq_ldets_tmp
934 SET operand_value = result_operand_value_t(K)
935 ,pricing_status_text = error_message_t(K)
936 ,pricing_status_code = status_code_t(K)
937 WHERE line_index = line_index_t(K)
938 AND line_detail_index = line_detail_index_t(K)
939 AND pricing_status_code = 'N';
940
941 FORALL K IN line_index_t.FIRST..line_index_t.LAST
942
943 UPDATE qp_npreq_lines_tmp
944 SET unit_price = decode(operand_calc_code_t(K) , 'LIST_PRICE', result_operand_value_t(K),NULL),
945 percent_price = decode(operand_calc_code_t(K) , 'PERCENT_PRICE', result_operand_value_t(K)
946 , NULL)
947 ,pricing_status_text = error_message_t(K)
948 ,pricing_status_code = lines_status_code_t(K)
949 WHERE line_index = line_index_t(K);
950
951 END IF;
952
953 IF l_debug = FND_API.G_TRUE THEN
954 qp_preq_grp.engine_debug('Currency_Conversion_API - after updating the temp tables ');
955
956 END IF;
957 EXIT WHEN c_currency_conversions%NOTFOUND;
958
959 END LOOP;
960
961 CLOSE c_currency_conversions;
962
963
964
965 EXCEPTION
966
967 /*
968 WHEN FND_API.G_EXC_ERROR THEN
969
970 l_process_status := FND_API.G_RET_STS_ERROR;
971
972 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
973
974 FND_MESSAGE.SET_NAME('QP','QP_CURR_CONV_EXP_ERROR');
975
976 END IF;
977 */
978 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
979
980 l_process_status := FND_API.G_RET_STS_UNEXP_ERROR;
981
982 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
983
984 FND_MESSAGE.SET_NAME('QP','QP_CURR_CONV_UNEXPECTED_ERROR');
985
986 END IF;
987
988 WHEN OTHERS THEN
989
990 IF l_debug = FND_API.G_TRUE THEN
991 qp_preq_grp.engine_debug('Currency_Conversion_API - OTHERS exception SQLERRM' || SQLERRM);
992 END IF;
993 l_process_status := FND_API.G_RET_STS_UNEXP_ERROR;
994
995 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
996
997 FND_MSG_PUB.Add_Exc_Msg
998 (G_PKG_NAME
999 , 'QP_MULTI_CURRENCY_PVT'
1000 );
1001
1002 END IF;
1003
1004 IF c_currency_conversions%ISOPEN THEN
1005 CLOSE c_currency_conversions;
1006 END IF;
1007
1008 IF l_debug = FND_API.G_TRUE THEN
1009 qp_preq_grp.engine_debug('Currency_Conversion_API - End');
1010 END IF;
1011 END Currency_Conversion_Api;
1012
1013 END QP_MULTI_CURRENCY_PVT;