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