[Home] [Help]
PACKAGE BODY: APPS.QP_PRICE_FORMULA_UPG_UTIL_PVT
Source
1 PACKAGE BODY QP_PRICE_FORMULA_UPG_UTIL_PVT AS
2 /* $Header: QPXVUPFB.pls 120.2 2006/03/21 11:15:55 rnayani noship $ */
3
4 /***********************************************************************
5 Type Definition for a table of varchar2 to hold multiple contexts
6 ************************************************************************/
7 TYPE contexts_tbl IS TABLE OF VARCHAR2(30)
8 INDEX BY BINARY_INTEGER;
9
10 err_msg VARCHAR2(240);
11 G_Context VARCHAR2(40);
12
13 /***********************************************************************
14 Procedure to Insert a row into the Formula Header tables.
15 ************************************************************************/
16
17 PROCEDURE Insert_Price_Formula(
18 p_price_formula_id NUMBER,
19 p_creation_date DATE,
20 p_created_by NUMBER,
21 p_last_update_date DATE,
22 p_last_updated_by NUMBER,
23 p_last_update_login NUMBER,
24 p_context VARCHAR2,
25 p_attribute1 VARCHAR2,
26 p_attribute2 VARCHAR2,
27 p_attribute3 VARCHAR2,
28 p_attribute4 VARCHAR2,
29 p_attribute5 VARCHAR2,
30 p_attribute6 VARCHAR2,
31 p_attribute7 VARCHAR2,
32 p_attribute8 VARCHAR2,
33 p_attribute9 VARCHAR2,
34 p_attribute10 VARCHAR2,
35 p_attribute11 VARCHAR2,
36 p_attribute12 VARCHAR2,
37 p_attribute13 VARCHAR2,
38 p_attribute14 VARCHAR2,
39 p_attribute15 VARCHAR2,
40 p_formula VARCHAR2,
41 p_start_date_active DATE,
42 p_end_date_active DATE,
43 p_name VARCHAR2,
44 p_description VARCHAR2
45 )
46 IS
47
48 BEGIN
49
50 INSERT INTO qp_price_formulas_b
51 ( price_formula_id,
52 creation_date,
53 created_by,
54 last_update_date,
55 last_updated_by,
56 last_update_login,
57 context,
58 attribute1,
59 attribute2,
60 attribute3,
61 attribute4,
62 attribute5,
63 attribute6,
64 attribute7,
65 attribute8,
66 attribute9,
67 attribute10,
68 attribute11,
69 attribute12,
70 attribute13,
71 attribute14,
72 attribute15,
73 formula,
74 start_date_active,
75 end_date_active
76 )
77 VALUES
78 ( p_price_formula_id,
79 nvl(p_creation_date, sysdate),
80 nvl(p_created_by, -1),
81 nvl(p_last_update_date, sysdate),
82 nvl(p_last_updated_by, -1),
83 nvl(p_last_update_login,-1),
84 p_context,
85 p_attribute1,
86 p_attribute2,
87 p_attribute3,
88 p_attribute4,
89 p_attribute5,
90 p_attribute6,
91 p_attribute7,
92 p_attribute8,
93 p_attribute9,
94 p_attribute10,
95 p_attribute11,
96 p_attribute12,
97 p_attribute13,
98 p_attribute14,
99 p_attribute15,
100 p_formula,
101 p_start_date_active,
102 p_end_date_active
103 );
104
105 INSERT INTO qp_price_formulas_tl
106 ( price_formula_id,
107 language,
108 source_lang,
109 name,
110 description,
111 creation_date,
112 created_by,
113 last_update_date,
114 last_updated_by,
115 last_update_login
116 )
117 SELECT
118 p_price_formula_id,
119 l.LANGUAGE_CODE,
120 userenv('LANG'),
121 p_name,
122 p_description,
123 nvl(p_creation_date, sysdate),
124 nvl(p_created_by, -1),
125 nvl(p_last_update_date, sysdate),
126 nvl(p_last_updated_by, -1),
127 nvl(p_last_update_login,-1)
128 FROM FND_LANGUAGES l
129 WHERE l.INSTALLED_FLAG in ('I', 'B')
130 AND NOT EXISTS (SELECT NULL
131 FROM qp_price_formulas_tl t
132 WHERE t.price_formula_id = p_price_formula_id
133 AND t.language = l.LANGUAGE_CODE);
134
135 EXCEPTION
136
137 WHEN OTHERS THEN
138 err_msg := substr(sqlerrm, 1, 240);
139 rollback;
140 QP_UTIL.Log_Error (
141 p_id1 => 'Price Formula Id' || to_char(p_price_formula_id),
142 p_id2 => 'Formula Name' || p_name,
143 p_error_type => 'FORMULA',
144 p_error_desc => err_msg,
145 p_error_module => 'Insert_Price_Formula');
146 raise;
147
148 END Insert_Price_Formula;
149
150
151
152 /***********************************************************************
153 Procedure to Insert a row into the Formula Lines table.
154 ************************************************************************/
155
156 PROCEDURE Insert_Price_Formula_Line(
157 p_price_formula_line_id NUMBER,
158 p_creation_date DATE,
159 p_created_by NUMBER,
160 p_last_update_date DATE,
161 p_last_updated_by NUMBER,
162 p_last_update_login NUMBER,
163 p_price_formula_id NUMBER,
164 p_price_formula_line_type_code VARCHAR2,
165 p_price_list_line_id NUMBER,
166 p_price_modifier_list_id NUMBER,
167 p_pricing_attribute_context VARCHAR2,
168 p_pricing_attribute VARCHAR2,
169 p_context VARCHAR2,
170 p_attribute1 VARCHAR2,
171 p_attribute2 VARCHAR2,
172 p_attribute3 VARCHAR2,
173 p_attribute4 VARCHAR2,
174 p_attribute5 VARCHAR2,
175 p_attribute6 VARCHAR2,
176 p_attribute7 VARCHAR2,
177 p_attribute8 VARCHAR2,
178 p_attribute9 VARCHAR2,
179 p_attribute10 VARCHAR2,
180 p_attribute11 VARCHAR2,
181 p_attribute12 VARCHAR2,
182 p_attribute13 VARCHAR2,
183 p_attribute14 VARCHAR2,
184 p_attribute15 VARCHAR2,
185 p_start_date_active DATE,
186 p_end_date_active DATE,
187 p_step_number NUMBER,
188 p_numeric_constant NUMBER
189 )
190 IS
191
192 BEGIN
193
194 INSERT INTO qp_price_formula_lines
195 ( price_formula_line_id
196 , creation_date
197 , created_by
198 , last_update_date
199 , last_updated_by
200 , last_update_login
201 , price_formula_id
202 , price_formula_line_type_code
203 , price_list_line_id
204 , price_modifier_list_id
205 , pricing_attribute_context
206 , pricing_attribute
207 , context
208 , attribute1
209 , attribute2
210 , attribute3
211 , attribute4
212 , attribute5
213 , attribute6
214 , attribute7
215 , attribute8
216 , attribute9
217 , attribute10
218 , attribute11
219 , attribute12
220 , attribute13
221 , attribute14
222 , attribute15
223 , start_date_active
224 , end_date_active
225 , step_number
226 , numeric_constant
227 )
228 VALUES
229 ( p_price_formula_line_id
230 , p_creation_date
231 , p_created_by
232 , p_last_update_date
233 , p_last_updated_by
234 , p_last_update_login
235 , p_price_formula_id
236 , p_price_formula_line_type_code
237 , p_price_list_line_id
238 , p_price_modifier_list_id
239 , p_pricing_attribute_context
240 , p_pricing_attribute
241 , p_context
242 , p_attribute1
243 , p_attribute2
244 , p_attribute3
245 , p_attribute4
246 , p_attribute5
247 , p_attribute6
248 , p_attribute7
249 , p_attribute8
250 , p_attribute9
251 , p_attribute10
252 , p_attribute11
253 , p_attribute12
254 , p_attribute13
255 , p_attribute14
256 , p_attribute15
257 , p_start_date_active
258 , p_end_date_active
259 , p_step_number
260 , p_numeric_constant
261 );
262
263 EXCEPTION
264
265 WHEN OTHERS THEN
266 err_msg := substr(sqlerrm, 1, 240);
267 rollback;
268 QP_UTIL.Log_Error (
269 p_id1 => 'Price Formula Id' || to_char(p_price_formula_id),
270 p_id2 => 'Price Formula Line Id' || to_char(p_price_formula_line_id),
271 p_error_type => 'FORMULA',
272 p_error_desc => err_msg,
273 p_error_module => 'Insert_Price_Formula_Line');
274 raise;
275
276 END Insert_Price_Formula_Line;
277
278
279 /***********************************************************************
280 Procedure to Insert a row into the List Header tables.
281 ************************************************************************/
282
283 PROCEDURE Insert_List_Header(
284 p_list_header_id NUMBER,
285 p_creation_date DATE,
286 p_created_by NUMBER,
287 p_last_update_date DATE,
288 p_last_updated_by NUMBER,
289 p_last_update_login NUMBER,
290 p_list_type_code VARCHAR2,
291 p_automatic_flag VARCHAR2,
292 p_currency_code VARCHAR2,
293 p_context VARCHAR2,
294 p_attribute1 VARCHAR2,
295 p_attribute2 VARCHAR2,
296 p_attribute3 VARCHAR2,
297 p_attribute4 VARCHAR2,
298 p_attribute5 VARCHAR2,
299 p_attribute6 VARCHAR2,
300 p_attribute7 VARCHAR2,
301 p_attribute8 VARCHAR2,
302 p_attribute9 VARCHAR2,
303 p_attribute10 VARCHAR2,
304 p_attribute11 VARCHAR2,
305 p_attribute12 VARCHAR2,
306 p_attribute13 VARCHAR2,
307 p_attribute14 VARCHAR2,
308 p_attribute15 VARCHAR2,
309 p_source_system_code VARCHAR2,
310 p_active_flag VARCHAR2,
311 p_ask_for_flag VARCHAR2,
312 p_name VARCHAR2,
313 p_description VARCHAR2,
314 p_version_no VARCHAR2
315 )
316 IS
317 BEGIN
318
319 INSERT INTO qp_list_headers_b (
320 list_header_id,
321 creation_date,
322 created_by,
323 last_update_date,
324 last_updated_by,
325 last_update_login,
326 list_type_code,
327 automatic_flag,
328 currency_code,
329 source_system_code,
330 active_flag,
331 ask_for_flag,
332 context, attribute1, attribute2, attribute3, attribute4,
333 attribute5, attribute6, attribute7, attribute8, attribute9,
334 attribute10, attribute11, attribute12, attribute13, attribute14, attribute15
335 --ENH Upgrade BOAPI for orig_sys...ref RAVI
336 ,ORIG_SYSTEM_HEADER_REF
337 )
338 VALUES(
339 p_list_header_id,
340 p_creation_date,
341 p_created_by,
342 p_last_update_date,
343 p_last_updated_by,
344 p_last_update_login,
345 p_list_type_code,
346 p_automatic_flag,
347 p_currency_code,
348 p_source_system_code,
349 p_active_flag,
350 p_ask_for_flag,
351 p_context, p_attribute1, p_attribute2, p_attribute3, p_attribute4,
352 p_attribute5, p_attribute6, p_attribute7, p_attribute8, p_attribute9,
353 p_attribute10, p_attribute11, p_attribute12, p_attribute13, p_attribute14,
354 p_attribute15
355 --ENH Upgrade BOAPI for orig_sys...ref RAVI
356 ,QP_PRICE_LIST_UTIL.Get_Orig_Sys_Hdr(p_list_header_id)
357 );
358
359 INSERT INTO qp_list_headers_tl (
360 list_header_id,
361 language,
362 source_lang,
363 name,
364 description,
365 creation_date,
366 created_by,
367 last_update_date,
368 last_updated_by,
369 last_update_login,
370 version_no
371 )
372 SELECT
373 p_list_header_id,
374 l.LANGUAGE_CODE,
375 userenv('LANG'),
376 p_name,
377 p_description,
378 p_creation_date,
379 p_created_by,
380 p_last_update_date,
381 p_last_updated_by,
382 p_last_update_login,
383 p_version_no
384 FROM FND_LANGUAGES l
385 WHERE l.INSTALLED_FLAG in ('I', 'B')
386 AND NOT EXISTS (SELECT NULL
387 FROM qp_list_headers_tl t
388 WHERE t.list_header_id = p_list_header_id
389 AND t.language = l.LANGUAGE_CODE);
390 EXCEPTION
391 WHEN OTHERS THEN
392 err_msg := substr(sqlerrm, 1, 240);
393 rollback;
394 QP_UTIL.Log_Error (
395 p_id1 => 'List Header Id' || to_char(p_list_header_id),
396 p_id2 => 'Price Modifier List Name' || p_name,
397 p_error_type => 'FORMULA',
398 p_error_desc => err_msg,
399 p_error_module => 'Insert_List_Header');
400 raise;
401
402 END Insert_List_Header;
403
404
405 /***********************************************************************
406 Procedure to Insert a row into the List Lines table.
407 ************************************************************************/
408
409 PROCEDURE Insert_List_Line(
410 p_list_line_id NUMBER,
411 p_creation_date DATE,
412 p_created_by NUMBER,
413 p_last_update_date DATE,
414 p_last_updated_by NUMBER,
415 p_last_update_login NUMBER,
416 p_list_header_id NUMBER,
417 p_list_line_type_code VARCHAR2,
418 p_automatic_flag VARCHAR2,
419 p_modifier_level_code VARCHAR2,
420 p_arithmetic_operator VARCHAR2,
421 p_operand NUMBER,
422 p_pricing_phase_id NUMBER,
423 p_incompatibility_grp_code VARCHAR2,
424 p_pricing_group_sequence NUMBER,
425 p_accrual_flag VARCHAR2,
426 p_product_precedence NUMBER,
427 p_base_qty NUMBER,
428 p_base_uom_code VARCHAR2,
429 p_recurring_flag VARCHAR2,
430 p_proration_type_code VARCHAR2,
431 p_print_on_invoice_flag VARCHAR2,
432 p_context VARCHAR2,
433 p_attribute1 VARCHAR2,
434 p_attribute2 VARCHAR2,
435 p_attribute3 VARCHAR2,
436 p_attribute4 VARCHAR2,
437 p_attribute5 VARCHAR2,
438 p_attribute6 VARCHAR2,
439 p_attribute7 VARCHAR2,
440 p_attribute8 VARCHAR2,
441 p_attribute9 VARCHAR2,
442 p_attribute10 VARCHAR2,
443 p_attribute11 VARCHAR2,
444 p_attribute12 VARCHAR2,
445 p_attribute13 VARCHAR2,
446 p_attribute14 VARCHAR2,
447 p_attribute15 VARCHAR2
448 )
449 IS
450 BEGIN
451
452 INSERT INTO qp_list_lines (
453 list_line_id,
454 list_line_no,
455 creation_date,
456 created_by,
457 last_update_date,
458 last_updated_by,
459 last_update_login,
460 list_header_id,
461 list_line_type_code,
462 automatic_flag,
463 modifier_level_code,
464 arithmetic_operator,
465 operand,
466 pricing_phase_id,
467 incompatibility_grp_code,
468 pricing_group_sequence,
469 accrual_flag,
470 product_precedence,
471 base_qty,
472 base_uom_code,
473 recurring_flag,
474 proration_type_code,
475 print_on_invoice_flag,
476 context, attribute1, attribute2, attribute3, attribute4, attribute5,
477 attribute6, attribute7, attribute8, attribute9, attribute10,
481 ,ORIG_SYS_HEADER_REF
478 attribute11, attribute12, attribute13, attribute14, attribute15
479 --ENH Upgrade BOAPI for orig_sys...ref RAVI
480 ,ORIG_SYS_LINE_REF
482 )
483 VALUES (
484 p_list_line_id,
485 p_list_line_id,
486 p_creation_date,
487 p_created_by,
488 p_last_update_date,
489 p_last_updated_by,
490 p_last_update_login,
491 p_list_header_id,
492 p_list_line_type_code,
493 p_automatic_flag,
494 p_modifier_level_code,
495 p_arithmetic_operator,
496 p_operand,
497 p_pricing_phase_id,
498 p_incompatibility_grp_code,
499 p_pricing_group_sequence,
500 p_accrual_flag,
501 p_product_precedence,
502 p_base_qty,
503 p_base_uom_code,
504 p_recurring_flag,
505 p_proration_type_code,
506 p_print_on_invoice_flag,
507 p_context,
508 p_attribute1, p_attribute2, p_attribute3, p_attribute4, p_attribute5,
509 p_attribute6, p_attribute7, p_attribute8, p_attribute9, p_attribute10,
510 p_attribute11, p_attribute12, p_attribute13, p_attribute14, p_attribute15
511 --ENH Upgrade BOAPI for orig_sys...ref RAVI
512 ,to_char(p_list_line_id)
513 ,(select h.ORIG_SYSTEM_HEADER_REF from qp_list_headers_b h where h.list_header_id=p_list_header_id)
514 );
515
516 EXCEPTION
517 WHEN OTHERS THEN
518 err_msg := substr(sqlerrm, 1, 240);
519 rollback;
520 QP_UTIL.Log_Error (
521 p_id1 => 'List Header Id' || to_char(p_list_header_id),
522 p_id2 => 'List Line Id' || to_char(p_list_line_id),
523 p_error_type => 'FORMULA',
524 p_error_desc => err_msg,
525 p_error_module => 'Insert_List_List');
526 raise;
527
528 END Insert_List_Line;
529
530
531 /***********************************************************************
532 Procedure to Insert a row into the Pricing Attributes table.
533 ************************************************************************/
534
535 PROCEDURE Insert_Pricing_Attribute(
536 p_creation_date DATE,
537 p_created_by NUMBER,
538 p_last_update_date DATE,
539 p_last_updated_by NUMBER,
540 p_last_update_login NUMBER,
541 p_list_line_id NUMBER,
542 p_excluder_flag VARCHAR2,
543 p_accumulate_flag VARCHAR2,
544 p_pricing_attribute_context VARCHAR2,
545 p_pricing_attribute VARCHAR2,
546 p_pricing_attr_value_from VARCHAR2,
547 p_pricing_attr_value_to VARCHAR2,
548 p_pricing_attribute_datatype VARCHAR2,
549 p_comparison_operator_code VARCHAR2
550 )
551 IS
552 l_pricing_attribute_id NUMBER;
553 l_attribute_grouping_no NUMBER;
554
555 BEGIN
556
557 SELECT QP_PRICING_ATTRIBUTES_S.nextval
558 INTO l_pricing_attribute_id
559 FROM DUAL;
560
561 SELECT QP_PRICING_ATTR_GROUP_NO_S.nextval
562 INTO l_attribute_grouping_no
563 FROM DUAL;
564
565 INSERT INTO qp_pricing_attributes(
566 pricing_attribute_id,
567 creation_date,
568 created_by,
569 last_update_date,
570 last_updated_by,
571 last_update_login,
572 list_line_id,
573 excluder_flag,
574 accumulate_flag,
575 pricing_attribute_context,
576 pricing_attribute,
577 pricing_attr_value_from,
578 pricing_attr_value_to,
579 attribute_grouping_no,
580 pricing_attribute_datatype,
581 comparison_operator_code
582 --ENH Upgrade BOAPI for orig_sys...ref RAVI
583 ,ORIG_SYS_PRICING_ATTR_REF
584 ,ORIG_SYS_LINE_REF
585 ,ORIG_SYS_HEADER_REF
586 )
587 VALUES
588 (
589 l_pricing_attribute_id,
590 p_creation_date,
591 p_created_by,
592 p_last_update_date,
593 p_last_updated_by,
594 p_last_update_login,
595 p_list_line_id,
596 p_excluder_flag,
597 p_accumulate_flag,
598 p_pricing_attribute_context,
599 p_pricing_attribute,
600 p_pricing_attr_value_from,
601 p_pricing_attr_value_to,
602 l_attribute_grouping_no,
603 p_pricing_attribute_datatype,
604 p_comparison_operator_code
605 --ENH Upgrade BOAPI for orig_sys...ref RAVI
606 ,to_char(l_pricing_attribute_id)
607 ,(select l.ORIG_SYS_LINE_REF from qp_list_lines l where l.list_line_id=p_list_line_id)
608 ,(select l.ORIG_SYS_HEADER_REF from qp_list_lines l where l.list_line_id=p_list_line_id)
609 );
610
611 EXCEPTION
612 WHEN OTHERS THEN
613 err_msg := substr(sqlerrm, 1, 240);
614 rollback;
615 QP_UTIL.Log_Error (
616 p_id1 => 'List Line Id' || to_char(p_list_line_id),
617 p_id2 => 'Pricing Attribute Id' || to_char(l_pricing_attribute_id),
618 p_id3 => 'Pricing Attribute Value' || p_pricing_attr_value_from,
619 p_error_type => 'FORMULA',
620 p_error_desc => err_msg,
621 p_error_module => 'Insert_Pricing_Attribute');
622 raise;
623
624 END Insert_Pricing_Attribute;
625
626
627 /***********************************************************************
628 Procedure to Upgrade unused Pricing Rule Components to Factors tables.
629 ************************************************************************/
630
631 PROCEDURE Upgrade_Unused_Components
632 IS
636 l_list_line_id NUMBER;
633
634 l_exists VARCHAR2(1);
635 l_list_header_id NUMBER;
637
638 CURSOR unused_components_cur
639 IS
640 SELECT *
641 FROM so_rule_formula_components c
642 WHERE c.formula_component_id NOT IN
643 (SELECT b.formula_component_id
644 FROM so_pricing_rule_lines b);
645
646 BEGIN
647
648 FOR l_comp_rec IN unused_components_cur
649 LOOP
650 BEGIN -- block around all the code in the components loop
651 l_exists := NULL;
652
653 BEGIN
654 SELECT 'Y'
655 INTO l_exists
656 FROM qp_list_headers_vl v
657 WHERE v.name = l_comp_rec.name;
658 EXCEPTION
659 WHEN NO_DATA_FOUND THEN
660 l_exists := NULL;
661 END;
662
663 IF l_exists IS NULL THEN -- Price Modifier List Id does not already exist
664
665 SELECT QP_LIST_HEADERS_B_S.nextval
666 INTO l_list_header_id
667 FROM DUAL;
668
669 QP_PRICE_FORMULA_UPG_UTIL_PVT.Insert_List_Header(
670 l_list_header_id,
671 l_comp_rec.creation_date,
672 l_comp_rec.created_by,
673 l_comp_rec.last_update_date,
674 l_comp_rec.last_updated_by,
675 l_comp_rec.last_update_login,
676 'PML', --list_type_code
677 'N', --automatic_flag
678 null, --currency_code
679 l_comp_rec.context,
680 l_comp_rec.attribute1,
681 l_comp_rec.attribute2,
682 l_comp_rec.attribute3,
683 l_comp_rec.attribute4,
684 l_comp_rec.attribute5,
685 l_comp_rec.attribute6,
686 l_comp_rec.attribute7,
687 l_comp_rec.attribute8,
688 l_comp_rec.attribute9,
689 l_comp_rec.attribute10,
690 l_comp_rec.attribute11,
691 l_comp_rec.attribute12,
692 l_comp_rec.attribute13,
693 l_comp_rec.attribute14,
694 l_comp_rec.attribute15,
695 'QP', --source_system_code
696 'Y', --active_flag
697 'N', --ask_for_flag
698 l_comp_rec.name,
699 l_comp_rec.description,
700 '1' --version_no
701 );
702
703
704 SELECT QP_LIST_LINES_S.nextval
705 INTO l_list_line_id
706 FROM DUAL;
707
708 QP_PRICE_FORMULA_UPG_UTIL_PVT.Insert_List_Line(
709 l_list_line_id,
710 l_comp_rec.creation_date,
711 l_comp_rec.created_by,
712 l_comp_rec.last_update_date,
713 l_comp_rec.last_updated_by,
714 l_comp_rec.last_update_login,
715 l_list_header_id,
716 'PMR', --list_line_type_code
717 'N', --automatic_flag
718 'NONE',--modifier_level_code
719 null, --arithmetic_operator
720 0, --operand
721 null, --pricing_phase_id
722 null, --incompatibility_grp_code
723 1, --pricing_group_sequence
724 'N', --accrual_flag
725 null, --product_precedence
726 1, --base_qty
727 null, --base_uom_code
728 'Y', --recurring_flag
729 null, --proration_type_code,
730 'N', --print_on_invoice_flag
731 null, --context and 15 attributes
732 null, null, null, null, null,
733 null, null, null, null, null,
734 null, null, null, null, null );
735
736 QP_PRICE_FORMULA_UPG_UTIL_PVT.Insert_Pricing_Attribute(
737 nvl(l_comp_rec.creation_date,sysdate), nvl(l_comp_rec.created_by, -1),
738 nvl(l_comp_rec.last_update_date,sysdate), nvl(l_comp_rec.last_updated_by,-1),
739 nvl(l_comp_rec.last_update_login,-1), l_list_line_id, 'N', 'N',
740 null, null, null, null, 'C', '=');
741
742 END IF;
743 EXCEPTION
744 WHEN OTHERS THEN
745 err_msg := substr(sqlerrm, 1, 240);
746 rollback;
750 p_error_desc => err_msg,
747 QP_UTIL.Log_Error (
748 p_id1 => 'Modifier List Id ' || to_char(l_list_header_id) ,
749 p_error_type => 'FORMULA',
751 p_error_module => 'Upgrade_Unused_Components');
752 END; -- Block around all the code in the components loop
753
754 commit; -- for each successful component
755 END LOOP;
756
757 EXCEPTION
758 WHEN OTHERS THEN
759 err_msg := substr(sqlerrm, 1, 240);
760 rollback;
761 QP_UTIL.Log_Error (
762 p_id1 => 'Modifier List Id ' || to_char(l_list_header_id),
763 p_error_type => 'FORMULA',
764 p_error_desc => err_msg,
765 p_error_module => 'Upgrade_Unused_Components');
766 raise;
767
768 END Upgrade_Unused_Components;
769
770
771 /**********************************************************************
772 Procedure to get database Attribute Value for a given Display Attribute
773 Value.
774 ************************************************************************/
775
776 FUNCTION Get_Attr_Value(p_context IN VARCHAR2,
777 p_attribute IN VARCHAR2,
778 p_attr_display_value IN VARCHAR2)
779 RETURN VARCHAR2
780 IS
781 l_attr_value VARCHAR2(40);
782
783 BEGIN
784 IF p_context = 'ITEM' AND p_attribute = 'PRICING_ATTRIBUTE1' THEN
785 -- If display value is an Item Number
786
787 BEGIN
788 SELECT TO_CHAR(inventory_item_id)
789 INTO l_attr_value
790 FROM mtl_system_items_vl
791 WHERE organization_id = FND_PROFILE.value('SO_ORGANIZATION_ID')
792 AND rtrim(ltrim(concatenated_segments)) =
793 rtrim(ltrim(p_attr_display_value));
794 EXCEPTION
795 WHEN NO_DATA_FOUND THEN
796 l_attr_value := p_attr_display_value;
797 END;
798
799 RETURN l_attr_value;
800
801 ELSIF p_context = 'ITEM' AND p_attribute = 'PRICING_ATTRIBUTE2' THEN
802 -- If display value is an Item Category
803
804 BEGIN
805 SELECT TO_CHAR(category_id)
806 INTO l_attr_value
807 FROM mtl_categories_kfv
808 WHERE rtrim(ltrim(concatenated_segments)) =
809 rtrim(ltrim(p_attr_display_value));
810
811
812 EXCEPTION
813 WHEN NO_DATA_FOUND THEN
814 l_attr_value := p_attr_display_value;
815 END;
816
817 RETURN l_attr_value;
818
819 ELSE
820 --If Others
821
822 l_attr_value := p_attr_display_value;
823
824 RETURN l_attr_value;
825
826 END IF; --If Item Number, Item Category or Other
827
828 EXCEPTION
829 WHEN OTHERS THEN
830 err_msg := substr(sqlerrm, 1, 240);
831 rollback;
832 QP_UTIL.Log_Error (
833 p_id1 => 'Attribute Display Value' || p_attr_display_value,
834 p_error_type => 'FORMULA',
835 p_error_desc => err_msg,
836 p_error_module => 'Get_Attr_Value');
837 raise;
838 END Get_Attr_Value;
839
840
841 /**************************************************************************
842 Procedure to Get Context and Attribute given the entity id, and entity code
843 ***************************************************************************/
844
845 PROCEDURE Get_Ctx_Attr(p_price_formula_id IN NUMBER,
846 p_entity_id IN NUMBER,
847 x_contexts OUT NOCOPY /* file.sql.39 change */ CONTEXTS_TBL,
848 x_attribute OUT NOCOPY /* file.sql.39 change */ VARCHAR2)
849 IS
850 l_ctx_var VARCHAR2(40);
851 l_attribute VARCHAR2(40);
852 l_count NUMBER := 0;
853
854 CURSOR context_cur(a_price_formula_id NUMBER)
855 IS
856 SELECT DISTINCT nvl(pricing_context, 'Upgrade Context')
857 FROM so_price_list_lines_115
858 WHERE pricing_rule_id = a_price_formula_id;
859
860 CURSOR flex_context_cur(a_pricing_attribute VARCHAR2)
861 IS
862 SELECT decode(descriptive_flex_context_code, 'Global Data Elements',
863 'Upgrade Context', descriptive_flex_context_code)
864 FROM fnd_descr_flex_column_usages
865 WHERE descriptive_flexfield_name = 'PRICING_ATTRIBUTES'
866 AND application_column_name = a_pricing_attribute;
867
868 BEGIN
869 IF to_char(p_entity_id) IN ('1001','1045','1208','1020', '1021','1022',
870 '1023','1024','1025','1026','1027','1028',
871 '1029','1030','1031','1032','1033','1034',
872 '1035','1036','1037','1038','1039')
873 THEN
874 --For Item related entity-codes get the Context and Attribute using API
875 QP_UTIL.Get_Context_Attribute( p_entity_id,
876 l_ctx_var,
877 l_attribute);
878 --dbms_output.put_line('In get_ctx..');
879 x_contexts(1) := l_ctx_var;
880 x_attribute := l_attribute;
881
882 ELSE -- If entity id not in list above
883
884 --dbms_output.put_line('In get_ctx..1');
885 SELECT entity_code
886 INTO l_attribute
887 FROM so_entities
888 WHERE entity_id = p_entity_id;
889
890 x_attribute := l_attribute;
891
892 IF G_Context IS NOT NULL THEN
893 x_contexts(1) := G_Context;
894
895 ELSE
896
897 --Context is not known, So get context from Price List Line to
901 OPEN context_cur(p_price_formula_id);
898 --which formula attached or if not found on any Price List Line
899 --get any one Context and attribute from fnd descr flex table
900
902 FETCH context_cur INTO l_ctx_var;
903
904 IF context_cur%FOUND THEN
905 l_count := l_count + 1; -- Do this only for one context. Not
906 x_contexts(l_count) := l_ctx_var; -- in a loop. Loop may be in future.
907 END IF;
908
909 CLOSE context_cur;
910
911 --dbms_output.put_line('In get_ctx..2');
912 IF l_count = 0 THEN
913 OPEN flex_context_cur(l_attribute);
914 FETCH flex_context_cur
915 INTO l_ctx_var;
916
917 --dbms_output.put_line('In get_ctx..3');
918 IF flex_context_cur%NOTFOUND THEN
919 raise NO_DATA_FOUND;
920 END IF;
921
922 x_contexts(1) := l_ctx_var;
923
924 CLOSE flex_context_cur;
925 END IF; --If l_count = 0
926
927 END IF; -- If G_Context is not null
928
929 END IF; -- If entity_id not in list
930
931 EXCEPTION
932 WHEN OTHERS THEN
933 err_msg := substr(sqlerrm, 1, 240);
934 rollback;
935 QP_UTIL.Log_Error (
936 p_id1 => 'Entity Id ' || to_char(p_entity_id),
937 p_error_type => 'FORMULA',
938 p_error_desc => err_msg,
939 p_error_module => 'Get_Ctx_Attr');
940 raise;
941
942 END Get_Ctx_Attr;
943
944
945 /**************************************************************************
946 Procedure to Get Attribute given the entity id. This procedure is meant
947 mainly for those Entity Ids whose Contexts are not Item.
948 ***************************************************************************/
949
950 PROCEDURE Get_Attribute(p_entity_id IN NUMBER,
951 x_attribute OUT NOCOPY /* file.sql.39 change */ VARCHAR2)
952 IS
953 l_attribute VARCHAR2(40);
954
955 BEGIN
956
957 SELECT entity_code
958 INTO l_attribute
959 FROM so_entities
960 WHERE entity_id = p_entity_id;
961
962 x_attribute := l_attribute;
963 EXCEPTION
964 WHEN OTHERS THEN
965 err_msg := substr(sqlerrm, 1, 240);
966 rollback;
967 QP_UTIL.Log_Error (
968 p_id1 => 'Entity Id ' || to_char(p_entity_id),
969 p_error_type => 'FORMULA',
970 p_error_desc => err_msg,
971 p_error_module => 'Get_Attribute');
972 raise;
973
974 END Get_Attribute;
975
976 /**************************************************************************
977 Procedure to divide the pricing rules into partitions that will aid in
978 parallel processing of the upgrade.
979 ***************************************************************************/
980
981 --PROCEDURE Create_Parallel_Slabs (l_workers IN NUMBER := 5) --2422176
982 PROCEDURE Create_Parallel_Slabs (l_workers IN NUMBER)
983 IS
984 l_min_line NUMBER;
985 l_max_line NUMBER;
986 l_worker_start NUMBER;
987 l_worker_end NUMBER;
988
989 BEGIN
990
991 DELETE qp_upg_lines_distribution
992 WHERE line_type = 'PRF'; --line_type for Pricing Formulas
993
994 COMMIT;
995
996 BEGIN
997 SELECT nvl(min(pricing_rule_id),0),
998 nvl(max(pricing_rule_id),0)
999 INTO l_min_line, l_max_line
1000 FROM so_pricing_rules_vl;
1001
1002 EXCEPTION
1003 WHEN OTHERS THEN
1004 NULL;
1005 END;
1006
1007 FOR i IN 1..l_workers LOOP
1008 l_worker_start :=
1009 l_min_line + trunc( (i-1) * (l_max_line-l_min_line)/l_workers);
1010 l_worker_end :=
1011 l_min_line + trunc(i*(l_max_line-l_min_line)/l_workers);
1012
1013 IF i <> l_workers THEN
1014 l_worker_end := l_worker_end - 1;
1015 END IF;
1016
1017 qp_modifier_upgrade_util_pvt.insert_line_distribution
1018 (l_worker => i,
1019 l_start_line => l_worker_start,
1020 l_end_line => l_worker_end,
1021 l_type_var => 'PRF');
1022
1023 END LOOP;
1024
1025 COMMIT;
1026
1027
1028 END Create_Parallel_Slabs;
1029
1030
1031 /***********************************************************************
1032 Procedure to Upgrade from Pricing Rules to Price Formula tables.
1033 ************************************************************************/
1034
1035 --PROCEDURE Upgrade_Price_Formulas (l_worker IN NUMBER := 1) --2422176
1036 PROCEDURE Upgrade_Price_Formulas (l_worker IN NUMBER)
1037 IS
1038
1039 l_item_context_flag BOOLEAN := TRUE;
1040 l_exists VARCHAR2(1);
1041 l_price_formula_line_id NUMBER;
1042 l_list_header_id NUMBER;
1043 l_list_line_id NUMBER;
1044
1045 l_entity1 VARCHAR2(30);
1046 l_entity2 VARCHAR2(30);
1047 l_entity3 VARCHAR2(30);
1048 l_entity4 VARCHAR2(30);
1049 l_entity5 VARCHAR2(30);
1050
1051 l_contexts CONTEXTS_TBL;
1052 l_context1 VARCHAR2(30);
1053 l_context2 VARCHAR2(30);
1054 l_context3 VARCHAR2(30);
1055 l_context4 VARCHAR2(30);
1056 l_context5 VARCHAR2(30);
1057
1061 l_attribute4 VARCHAR2(40);
1058 l_attribute1 VARCHAR2(40);
1059 l_attribute2 VARCHAR2(40);
1060 l_attribute3 VARCHAR2(40);
1062 l_attribute5 VARCHAR2(40);
1063
1064 l_attr_value VARCHAR2(240);
1065
1066 l_min_line NUMBER;
1067 l_max_line NUMBER;
1068
1069 x_context_flag VARCHAR2(1);
1070 x_attribute_flag VARCHAR2(1);
1071 x_value_flag VARCHAR2(1);
1072 x_datatype VARCHAR2(1);
1073 x_precedence NUMBER;
1074 x_error_code NUMBER;
1075
1076 CURSOR so_pricing_rules_cur (a_min_line NUMBER,
1077 a_max_line NUMBER)
1078 IS
1079 SELECT *
1080 FROM so_pricing_rules_vl
1081 WHERE pricing_rule_id BETWEEN a_min_line AND a_max_line;
1082
1083 CURSOR so_pricing_rule_lines_cur (a_pricing_rule_id NUMBER)
1084 IS
1085 SELECT *
1086 FROM so_pricing_rule_lines l
1087 WHERE l.pricing_rule_id = a_pricing_rule_id;
1088
1089 CURSOR so_rule_line_comp_values_cur(a_pricing_rule_id NUMBER,
1090 a_step_number NUMBER)
1091 IS
1092 SELECT l.pricing_rule_id,
1093 v.amount,
1094 v.creation_date, v.created_by, v.last_update_date,
1095 v.last_updated_by, v.last_update_login,
1096 v.context, v.attribute1, v.attribute2, v.attribute3,
1097 v.attribute4, v.attribute5, v.attribute6, v.attribute7,
1098 v.attribute8, v.attribute9, v.attribute10, v.attribute11,
1099 v.attribute12, v.attribute13, v.attribute14, v.attribute15,
1100 c.name, c.description,
1101 c.creation_date c_creation_date, c.created_by c_created_by,
1102 c.last_update_date c_last_update_date, c.last_updated_by c_last_updated_by,
1103 c.last_update_login c_last_update_login,
1104 c.entity_id_1, c.entity_id_2, c.entity_id_3, c.entity_id_4,
1105 c.entity_id_5, v.value1, v.value2, v.value3, v.value4, v.value5,
1106 c.context c_context, c.attribute1 c_attribute1, c.attribute2 c_attribute2,
1107 c.attribute3 c_attribute3, c.attribute4 c_attribute4,
1108 c.attribute5 c_attribute5, c.attribute6 c_attribute6,
1109 c.attribute7 c_attribute7, c.attribute8 c_attribute8,
1110 c.attribute9 c_attribute9, c.attribute10 c_attribute10,
1111 c.attribute11 c_attribute11, c.attribute12 c_attribute12,
1112 c.attribute13 c_attribute13, c.attribute14 c_attribute14,
1113 c.attribute15 c_attribute15
1114
1115 FROM so_pricing_rule_lines l, so_pricing_rule_line_values v,
1116 so_rule_formula_components c
1117 WHERE l.pricing_rule_id = v.pricing_rule_id(+)
1118 AND l.step_number = v.step_number(+)
1119 AND l.formula_component_id = c.formula_component_id
1120 AND l.pricing_rule_id = a_pricing_rule_id
1121 AND l.step_number = a_step_number;
1122
1123 BEGIN
1124
1125 BEGIN
1126 SELECT start_line_id, end_line_id
1127 INTO l_min_line, l_max_line
1128 FROM qp_upg_lines_distribution
1129 WHERE line_type = 'PRF'
1130 AND worker = l_worker;
1131
1132 EXCEPTION
1133 WHEN NO_DATA_FOUND THEN
1134 l_min_line := 0;
1135 l_max_line := 0;
1136
1137 END;
1138
1139 FOR l_rec IN so_pricing_rules_cur(l_min_line, l_max_line)
1140 LOOP
1141
1142 BEGIN -- Block around all code in the Pricing Rules Loop
1143 l_exists := NULL;
1144
1145 BEGIN
1146 SELECT 'Y'
1147 INTO l_exists
1148 FROM qp_price_formulas_b b
1149 WHERE b.price_formula_id = l_rec.pricing_rule_id;
1150 EXCEPTION
1151 WHEN NO_DATA_FOUND THEN
1152 l_exists := NULL;
1153 END;
1154
1155 IF l_exists IS NULL THEN -- Formula Id does not already exist
1156
1157 QP_PRICE_FORMULA_UPG_UTIL_PVT.Insert_Price_Formula(
1158 l_rec.pricing_rule_id, --price_formula_id
1159 l_rec.creation_date,
1160 l_rec.created_by,
1161 l_rec.last_update_date,
1162 l_rec.last_updated_by,
1163 l_rec.last_update_login,
1164 l_rec.context,
1165 l_rec.attribute1,
1166 l_rec.attribute2,
1167 l_rec.attribute3,
1168 l_rec.attribute4,
1169 l_rec.attribute5,
1170 l_rec.attribute6,
1171 l_rec.attribute7,
1172 l_rec.attribute8,
1173 l_rec.attribute9,
1174 l_rec.attribute10,
1175 l_rec.attribute11,
1176 l_rec.attribute12,
1177 l_rec.attribute13,
1178 l_rec.attribute14,
1179 l_rec.attribute15,
1180 l_rec.formula,
1181 l_rec.start_date_active,
1182 l_rec.end_date_active,
1186
1183 l_rec.name,
1184 l_rec.description);
1185
1187 FOR l_lines_rec IN
1188 so_pricing_rule_lines_cur(l_rec.pricing_rule_id)
1189 LOOP
1190
1191 SELECT QP_PRICE_FORMULA_LINES_S.nextval
1192 INTO l_price_formula_line_id
1193 FROM DUAL;
1194
1195 --Before inserting the formula line record, insert a Modifier List
1196 --(Factor List), Modifier(Factor) and Modifier attributes
1197 --(Factor Pricing Attributes). Need to get contexts first.
1198
1199 SELECT entity_id_1, entity_id_2, entity_id_3, entity_id_4, entity_id_5
1200 INTO l_entity1, l_entity2, l_entity3, l_entity4, l_entity5
1201 FROM so_rule_formula_components
1202 WHERE formula_component_id = l_lines_rec.formula_component_id;
1203
1204 --Function to get Context and Attribute based on entity_code.
1205 --If entity_code is not an item-context then fetch contexts from
1206 --price list lines or descriptive flexfield setup
1207
1208 l_contexts.delete; --Empty the plsql table of contexts each time
1209 l_attribute1 := '';-- Reset the attribute values
1210 G_Context := '';
1211
1212 IF l_entity1 IS NOT NULL THEN
1213 Get_Ctx_Attr(l_rec.pricing_rule_id, l_entity1,
1214 l_contexts, l_attribute1);
1215 l_context1 := l_contexts(1);
1216 IF l_context1 <> 'ITEM' THEN
1217 G_Context := l_context1;
1218 END IF;
1219 END IF;
1220
1221 l_contexts.delete; --Empty the plsql table of contexts each time
1222 l_attribute2 := '';-- Reset the attribute values
1223
1224 IF l_entity2 IS NOT NULL THEN
1225 Get_Ctx_Attr(l_rec.pricing_rule_id, l_entity2,
1226 l_contexts, l_attribute2);
1227 l_context2 := l_contexts(1);
1228 IF l_context2 <> 'ITEM' THEN
1229 IF G_Context IS NULL THEN
1230 G_Context := l_context2;
1231 END IF;
1232 END IF;
1233 END IF;
1234
1235 l_contexts.delete; --Empty the plsql table of contexts each time
1236 l_attribute3 := '';-- Reset the attribute values
1237
1238 IF l_entity3 IS NOT NULL THEN
1239 Get_Ctx_Attr(l_rec.pricing_rule_id, l_entity3,
1240 l_contexts, l_attribute3);
1241 l_context3 := l_contexts(1);
1242 IF l_context3 <> 'ITEM' THEN
1243 IF G_Context IS NULL THEN
1244 G_Context := l_context3;
1245 END IF;
1246 END IF;
1247 END IF;
1248
1249 l_contexts.delete; --Empty the plsql table of contexts each time
1250 l_attribute4 := '';-- Reset the attribute values
1251
1252 IF l_entity4 IS NOT NULL THEN
1253 Get_Ctx_Attr(l_rec.pricing_rule_id, l_entity4,
1254 l_contexts, l_attribute4);
1255 l_context4 := l_contexts(1);
1256 IF l_context4 <> 'ITEM' THEN
1257 IF G_Context IS NULL THEN
1258 G_Context := l_context4;
1259 END IF;
1260 END IF;
1261 END IF;
1262
1263 l_contexts.delete; --Empty the plsql table of contexts each time
1264 l_attribute5 := '';-- Reset the attribute values
1265
1266 IF l_entity5 IS NOT NULL THEN
1267 Get_Ctx_Attr(l_rec.pricing_rule_id, l_entity5,
1268 l_contexts, l_attribute5);
1269 l_context5 := l_contexts(1);
1270 IF l_context5 <> 'ITEM' THEN
1271 IF G_Context IS NULL THEN
1272 G_Context := l_context5;
1273 END IF;
1274 END IF;
1275 END IF;
1276
1277 FOR l_value_rec IN so_rule_line_comp_values_cur(l_lines_rec.pricing_rule_id,
1278 l_lines_rec.step_number)
1279 LOOP
1280
1281 IF so_rule_line_comp_values_cur%ROWCOUNT = 1 THEN
1282
1283 SELECT QP_LIST_HEADERS_B_S.nextval
1284 INTO l_list_header_id
1285 FROM DUAL;
1286
1287 --dbms_output.put_line(' list header '||l_list_header_id);
1288 --dbms_output.put_line(l_value_rec.name || to_char(l_price_formula_line_id));
1289 --dbms_output.put_line(l_lines_rec.pricing_rule_id);
1290 --dbms_output.put_line(l_lines_rec.step_number);
1291
1292
1293 QP_PRICE_FORMULA_UPG_UTIL_PVT.Insert_List_Header(
1294 l_list_header_id,
1295 l_value_rec.c_creation_date,
1296 l_value_rec.c_created_by,
1297 l_value_rec.c_last_update_date,
1298 l_value_rec.c_last_updated_by,
1299 l_value_rec.c_last_update_login,
1300 'PML', --list_type_code
1301 'N', --automatic_flag
1302 null, --currency_code
1303 l_value_rec.c_context,
1304 l_value_rec.c_attribute1,
1305 l_value_rec.c_attribute2,
1306 l_value_rec.c_attribute3,
1307 l_value_rec.c_attribute4,
1308 l_value_rec.c_attribute5,
1309 l_value_rec.c_attribute6,
1310 l_value_rec.c_attribute7,
1314 l_value_rec.c_attribute11,
1311 l_value_rec.c_attribute8,
1312 l_value_rec.c_attribute9,
1313 l_value_rec.c_attribute10,
1315 l_value_rec.c_attribute12,
1316 l_value_rec.c_attribute13,
1317 l_value_rec.c_attribute14,
1318 l_value_rec.c_attribute15,
1319 'QP', --source_system_code
1320 'Y', --active_flag
1321 'N', --ask_for_flag
1322 l_value_rec.name || to_char(l_price_formula_line_id),
1323 l_value_rec.description,
1324 '1' --version_no
1325 );
1326
1327 END IF; --If rowcount = 1
1328
1329 SELECT QP_LIST_LINES_S.nextval
1330 INTO l_list_line_id
1331 FROM DUAL;
1332
1333 QP_PRICE_FORMULA_UPG_UTIL_PVT.Insert_List_Line(
1334 l_list_line_id,
1335 nvl(l_value_rec.creation_date,sysdate),
1336 nvl(l_value_rec.created_by,-1),
1337 nvl(l_value_rec.last_update_date, sysdate),
1338 nvl(l_value_rec.last_updated_by,-1),
1339 nvl(l_value_rec.last_update_login, -1),
1340 l_list_header_id,
1341 'PMR', --list_line_type_code
1342 'N', --automatic_flag
1343 'NONE',--modifier_level_code
1344 null, --arithmetic_operator
1345 nvl(l_value_rec.amount, 0), --operand
1346 null, --pricing_phase_id
1347 null, --incompatibility_grp_code
1348 1, --pricing_group_sequence
1349 'N', --accrual_flag
1350 null, --product_precedence
1351 1, --base_qty
1352 null, --base_uom_code
1353 'Y', --recurring_flag
1354 null, --proration_type_code,
1355 'N', --print_on_invoice_flag
1356 l_value_rec.context, l_value_rec.attribute1,
1357 l_value_rec.attribute2, l_value_rec.attribute3,
1358 l_value_rec.attribute4, l_value_rec.attribute5,
1359 l_value_rec.attribute6, l_value_rec.attribute7,
1360 l_value_rec.attribute8, l_value_rec.attribute9,
1361 l_value_rec.attribute10,l_value_rec.attribute11,
1362 l_value_rec.attribute12,l_value_rec.attribute13,
1363 l_value_rec.attribute14,l_value_rec.attribute15
1364 );
1365
1366 IF l_value_rec.entity_id_1 IS NOT NULL THEN
1367
1368 l_attr_value :=
1369 Get_Attr_Value(l_context1, l_attribute1, l_value_rec.value1);
1370
1371 QP_UTIL.validate_qp_flexfield(
1372 flexfield_name => 'QP_ATTR_DEFNS_PRICING',
1373 context => l_context1, attribute => l_attribute1,
1374 value => l_attr_value, application_short_name => 'QP',
1375 context_flag => x_context_flag,
1376 attribute_flag => x_attribute_flag,
1377 value_flag => x_value_flag, datatype => x_datatype,
1378 precedence => x_precedence, error_code => x_error_code);
1379
1380 QP_PRICE_FORMULA_UPG_UTIL_PVT.Insert_Pricing_Attribute(
1381 nvl(l_value_rec.creation_date,sysdate), nvl(l_value_rec.created_by, -1),
1382 nvl(l_value_rec.last_update_date,sysdate), nvl(l_value_rec.last_updated_by,-1),
1383 nvl(l_value_rec.last_update_login,-1), l_list_line_id, 'N', 'N',
1384 l_context1, l_attribute1, l_attr_value, null,
1385 x_datatype, '=');
1386
1387 END IF;
1388
1389
1390 IF l_value_rec.entity_id_2 IS NOT NULL THEN
1391
1392 l_attr_value :=
1393 Get_Attr_Value(l_context2, l_attribute2, l_value_rec.value2);
1394
1395 QP_UTIL.validate_qp_flexfield(
1396 flexfield_name => 'QP_ATTR_DEFNS_PRICING',
1397 context => l_context2, attribute => l_attribute2,
1398 value => l_attr_value, application_short_name => 'QP',
1399 context_flag => x_context_flag,
1400 attribute_flag => x_attribute_flag,
1401 value_flag => x_value_flag, datatype => x_datatype,
1402 precedence => x_precedence, error_code => x_error_code);
1403
1404 QP_PRICE_FORMULA_UPG_UTIL_PVT.Insert_Pricing_Attribute(
1405 nvl(l_value_rec.creation_date,sysdate), nvl(l_value_rec.created_by, -1),
1406 nvl(l_value_rec.last_update_date,sysdate), nvl(l_value_rec.last_updated_by,-1),
1407 nvl(l_value_rec.last_update_login,-1), l_list_line_id, 'N', 'N',
1408 l_context2, l_attribute2, l_attr_value, null,
1409 x_datatype, '=');
1410 END IF;
1411
1412
1413 IF l_value_rec.entity_id_3 IS NOT NULL THEN
1414
1415 l_attr_value :=
1419 flexfield_name => 'QP_ATTR_DEFNS_PRICING',
1416 Get_Attr_Value(l_context3, l_attribute3, l_value_rec.value3);
1417
1418 QP_UTIL.validate_qp_flexfield(
1420 context => l_context3, attribute => l_attribute3,
1421 value => l_attr_value,
1422 application_short_name => 'QP',
1423 context_flag => x_context_flag,
1424 attribute_flag => x_attribute_flag,
1425 value_flag => x_value_flag, datatype => x_datatype,
1426 precedence => x_precedence, error_code => x_error_code);
1427
1428 QP_PRICE_FORMULA_UPG_UTIL_PVT.Insert_Pricing_Attribute(
1429 nvl(l_value_rec.creation_date,sysdate), nvl(l_value_rec.created_by, -1),
1430 nvl(l_value_rec.last_update_date,sysdate), nvl(l_value_rec.last_updated_by,-1),
1431 nvl(l_value_rec.last_update_login,-1), l_list_line_id, 'N', 'N',
1432 l_context3, l_attribute3, l_attr_value, null,
1433 x_datatype, '=');
1434 END IF;
1435
1436
1437 IF l_value_rec.entity_id_4 IS NOT NULL THEN
1438
1439 l_attr_value :=
1440 Get_Attr_Value(l_context4, l_attribute4, l_value_rec.value4);
1441
1442 QP_UTIL.validate_qp_flexfield(
1443 flexfield_name => 'QP_ATTR_DEFNS_PRICING',
1444 context => l_context4, attribute => l_attribute4,
1445 value => l_attr_value,
1446 application_short_name => 'QP',
1447 context_flag => x_context_flag,
1448 attribute_flag => x_attribute_flag,
1449 value_flag => x_value_flag, datatype => x_datatype,
1450 precedence => x_precedence, error_code => x_error_code);
1451
1452 QP_PRICE_FORMULA_UPG_UTIL_PVT.Insert_Pricing_Attribute(
1453 nvl(l_value_rec.creation_date,sysdate), nvl(l_value_rec.created_by, -1),
1454 nvl(l_value_rec.last_update_date,sysdate), nvl(l_value_rec.last_updated_by,-1),
1455 nvl(l_value_rec.last_update_login,-1), l_list_line_id, 'N', 'N',
1456 l_context4, l_attribute4, l_attr_value, null,
1457 x_datatype, '=');
1458 END IF;
1459
1460
1461 IF l_value_rec.entity_id_5 IS NOT NULL THEN
1462
1463 l_attr_value :=
1464 Get_Attr_Value(l_context5, l_attribute5, l_value_rec.value5);
1465
1466 QP_UTIL.validate_qp_flexfield(
1467 flexfield_name => 'QP_ATTR_DEFNS_PRICING',
1468 context => l_context5, attribute => l_attribute5,
1469 value => l_attr_value,
1470 application_short_name => 'QP',
1471 context_flag => x_context_flag,
1472 attribute_flag => x_attribute_flag,
1473 value_flag => x_value_flag, datatype => x_datatype,
1474 precedence => x_precedence, error_code => x_error_code);
1475
1476 QP_PRICE_FORMULA_UPG_UTIL_PVT.Insert_Pricing_Attribute(
1477 nvl(l_value_rec.creation_date,sysdate), nvl(l_value_rec.created_by, -1),
1478 nvl(l_value_rec.last_update_date,sysdate), nvl(l_value_rec.last_updated_by,-1),
1479 nvl(l_value_rec.last_update_login,-1), l_list_line_id, 'N', 'N',
1480 l_context5, l_attribute5, l_attr_value, null,
1481 x_datatype, '=');
1482 END IF;
1483
1484 END LOOP; -- Loop through rule lines values
1485
1486
1487 QP_PRICE_FORMULA_UPG_UTIL_PVT.Insert_Price_Formula_Line(
1488 l_price_formula_line_id, --price_formula_line_id
1489 l_lines_rec.creation_date,
1490 l_lines_rec.created_by,
1491 l_lines_rec.last_update_date,
1492 l_lines_rec.last_updated_by,
1493 l_lines_rec.last_update_login,
1494 l_lines_rec.pricing_rule_id, --price_formula_id
1495 'ML', --price_formula_line_type_code
1496 null, --price_list_line_id
1497 l_list_header_id, --price_modifier_list_id
1498 null, --pricing_attribute_context
1499 null, --pricing_attribute
1500 l_lines_rec.context,
1501 l_lines_rec.attribute1,
1502 l_lines_rec.attribute2,
1503 l_lines_rec.attribute3,
1504 l_lines_rec.attribute4,
1505 l_lines_rec.attribute5,
1506 l_lines_rec.attribute6,
1507 l_lines_rec.attribute7,
1508 l_lines_rec.attribute8,
1509 l_lines_rec.attribute9,
1510 l_lines_rec.attribute10,
1511 l_lines_rec.attribute11,
1512 l_lines_rec.attribute12,
1513 l_lines_rec.attribute13,
1514 l_lines_rec.attribute14,
1515 l_lines_rec.attribute15,
1516 l_lines_rec.start_date_active,
1517 l_lines_rec.end_date_active,
1518 l_lines_rec.step_number,
1519 null --numeric_constant
1520 );
1521
1522 END LOOP; -- Loop through Pricing Rule Lines
1523
1524 END IF; -- If formula does not already exist
1525
1526 EXCEPTION
1527 WHEN NO_DATA_FOUND OR DUP_VAL_ON_INDEX OR VALUE_ERROR THEN
1528 err_msg := substr(sqlerrm, 1, 240);
1529 rollback;
1530 QP_UTIL.Log_Error (
1531 p_id1 => 'Price Formula Id' || to_char(l_rec.pricing_rule_id),
1532 p_error_type => 'FORMULA',
1536 WHEN OTHERS THEN
1533 p_error_desc => err_msg,
1534 p_error_module => 'Upgrade_Price_Formulas');
1535
1537 err_msg := substr(sqlerrm, 1, 240);
1538 rollback;
1539 QP_UTIL.Log_Error (
1540 p_id1 => 'Price Formula Id' || to_char(l_rec.pricing_rule_id),
1541 p_error_type => 'FORMULA',
1542 p_error_desc => err_msg,
1543 p_error_module => 'Upgrade_Price_Formulas');
1544 raise;
1545
1546 END; --Block around all the code in the Pricing Rule Loop
1547
1548 commit; --For each successful Pricing Rule
1549 END LOOP; --Loop through Pricing Rules
1550
1551 Upgrade_Unused_Components; -- Call Proc. to upgrade unused rule components
1552
1553 -- Flexfield Merging
1554 -- Run the utility procedure to upgrade Descriptive Flexfields
1555 -- from Pricing to Price Formulas
1556 -- QP_UTIL.qp_upgrade_context('OE', 'QP', 'SO_PRICING_RULES', 'QP_PRICE_FORMULAS_B');
1557 -- QP_UTIL.qp_upgrade_context('OE', 'QP', 'SO_PRICING_RULE_LINES', 'QP_PRICE_FORMULA_LINES');
1558 -- QP_UTIL.qp_upgrade_context('OE', 'QP', 'SO_RULE_FORMULA_COMPONENTS', 'QP_LIST_HEADERS');
1559 -- QP_UTIL.qp_upgrade_context('OE', 'QP', 'SO_PRICING_RULE_LINE_VALUES', 'QP_LIST_LINES');
1560 commit;
1561
1562 EXCEPTION
1563 WHEN OTHERS THEN
1564 err_msg := substr(sqlerrm, 1, 240);
1565 rollback;
1566 QP_UTIL.Log_Error (
1567 p_id1 => null,
1568 p_error_type => 'FORMULA',
1569 p_error_desc => err_msg,
1570 p_error_module => 'Upgrade_Price_Formulas');
1571 raise;
1572
1573 END Upgrade_Price_Formulas;
1574
1575
1576 END QP_PRICE_FORMULA_UPG_UTIL_PVT;