[Home] [Help]
PACKAGE BODY: APPS.QP_COPY_PRICELIST_PVT
Source
1 PACKAGE BODY QP_COPY_PRICELIST_PVT AS
2 /* $Header: QPXVCPLB.pls 120.15.12010000.2 2008/09/09 11:09:59 jputta ship $ */
3
4 -- GLOBAL Constant holding the package name
5
6 --G_PKG_NAME CONSTANT VARCHAR2(30):='QP_COPY_PRICELIST_PVT';
7
8
9 /***************************************************************
10 * Function to check if a list_line is a Price Break Line *
11 ****************************************************************/
12
13 FUNCTION Price_Break_Line(p_list_line_id IN NUMBER)
14 RETURN BOOLEAN
15 IS
16
17 l_return BOOLEAN := FALSE;
18 l_to_rltd_modifier_id NUMBER;
19
20 CURSOR price_break_line_cur(a_list_line_id NUMBER)
21 IS
22 SELECT to_rltd_modifier_id
23 FROM qp_rltd_modifiers
24 WHERE to_rltd_modifier_id = a_list_line_id;
25
26 BEGIN
27
28 OPEN price_break_line_cur(p_list_line_id);
29 FETCH price_break_line_cur
30 INTO l_to_rltd_modifier_id;
31
32 IF price_break_line_cur%FOUND THEN
33 l_return := TRUE;
34 ELSE
35 l_return := FALSE;
36 END IF;
37
38 CLOSE price_break_line_cur;
39
40 RETURN l_return;
41
42 END Price_Break_Line;
43
44
45 /************************************************************************
46 *Function to Get New Id for an Old list_line_id from the mapping table *
47 *************************************************************************/
48
49 FUNCTION Get_New_Id (a_list_line_id IN NUMBER,
50 a_mapping_tbl IN mapping_tbl)
51 RETURN NUMBER
52 IS
53 l_return NUMBER := 0;
54
55 BEGIN
56 FOR i IN 1..a_mapping_tbl.COUNT
57 LOOP
58
59 IF a_mapping_tbl(i).old_list_line_id = a_list_line_id THEN
60 l_return := a_mapping_tbl(i).new_list_line_id;
61 EXIT;
62 END IF;
63
64 END LOOP;
65
66 RETURN l_return;
67
68 END Get_New_Id;
69
70
71 /***********************************************************************/
72 /* Procedure to Delete Duplicate Lines potentially created effective */
73 /* dates not retained while copying lines from one price list to */
74 /* another. */
75 /***********************************************************************/
76
77 PROCEDURE Delete_Duplicate_Lines (p_effective_dates_flag VARCHAR2,
78 p_new_list_header_id NUMBER)
79 IS
80
81
82 /* Commented out bu dhgupta for bug 2100785 */
83 /*
84 CURSOR del_dup_cur (a_new_list_header_id NUMBER)
85 IS
86 SELECT *
87 FROM qp_list_lines a
88 WHERE EXISTS (SELECT NULL
89 FROM qp_list_lines b
90 WHERE a.inventory_item_id = b.inventory_item_id
91 AND a.list_line_type_code = b.list_line_type_code
92 AND a.list_header_id = b.list_header_id
93 AND a.list_header_id = a_new_list_header_id
94 AND a.list_line_id < b.list_line_id
95 AND nvl(a.automatic_flag,'x') = nvl(b.automatic_flag,'x')
96 AND nvl(a.modifier_level_code,'x') =
97 nvl(b.modifier_level_code,'x')
98 AND nvl(a.list_price,-1) = nvl(b.list_price,-1)
99 AND nvl(a.primary_uom_flag,'x') =
100 nvl(b.primary_uom_flag,'x')
101 AND nvl(a.organization_id,-1) = nvl(b.organization_id,-1)
102 AND nvl(a.related_item_id,-1) = nvl(b.related_item_id,-1)
103 AND nvl(a.relationship_type_id,-1) =
104 nvl(b.relationship_type_id,-1)
105 AND nvl(a.substitution_context,'x') =
106 nvl(b.substitution_context,'x')
107 AND nvl(a.substitution_attribute,'x') =
108 nvl(b.substitution_attribute,'x')
109 AND nvl(a.substitution_value,'x') =
110 nvl(b.substitution_value,'x')
111 AND nvl(a.context,'x') = nvl(b.context,'x')
112 AND nvl(a.attribute1,'x') = nvl(b.attribute1, 'x')
113 AND nvl(a.attribute2,'x') = nvl(b.attribute2, 'x')
114 AND nvl(a.comments,'x') = nvl(b.comments,'x')
115 AND nvl(a.attribute3,'x') = nvl(b.attribute3,'x')
116 AND nvl(a.attribute4,'x') = nvl(b.attribute4,'x')
117 AND nvl(a.attribute5,'x') = nvl(b.attribute5,'x')
118 AND nvl(a.attribute6,'x') = nvl(b.attribute6,'x')
119 AND nvl(a.attribute7,'x') = nvl(b.attribute7,'x')
120 AND nvl(a.attribute8,'x') = nvl(b.attribute8,'x')
121 AND nvl(a.attribute9,'x') = nvl(b.attribute9,'x')
122 AND nvl(a.attribute10,'x') = nvl(b.attribute10,'x')
123 AND nvl(a.attribute11,'x') = nvl(b.attribute11,'x')
124 AND nvl(a.attribute12,'x') = nvl(b.attribute12,'x')
125 AND nvl(a.attribute13,'x') = nvl(b.attribute13,'x')
126 AND nvl(a.attribute14,'x') = nvl(b.attribute14,'x')
127 AND nvl(a.attribute15,'x') = nvl(b.attribute15,'x')
128 AND nvl(a.price_break_type_code,'x') =
129 nvl(b.price_break_type_code,'x')
130 AND nvl(a.percent_price,-1) = nvl(b.percent_price,-1)
131 AND nvl(a.price_by_formula_id,-1) =
132 nvl(b.price_by_formula_id,-1)
133 AND nvl(a.number_effective_periods,-1) =
134 nvl(b.number_effective_periods,-1)
135 AND nvl(a.effective_period_uom,'x') =
136 nvl(b.effective_period_uom,'x')
137 AND nvl(a.arithmetic_operator,'x') =
138 nvl(b.arithmetic_operator,'x')
139 AND nvl(a.operand,-1) = nvl(b.operand,-1)
140 AND nvl(a.override_flag,'x') = nvl(b.override_flag,'x')
141 AND nvl(a.print_on_invoice_flag,'x') =
142 nvl(b.print_on_invoice_flag,'x')
143 AND nvl(a.rebate_transaction_type_code,'x') =
144 nvl(b.rebate_transaction_type_code,'x')
145 AND nvl(a.estim_accrual_rate,-1) =
146 nvl(b.estim_accrual_rate,-1)
147 AND nvl(a.generate_using_formula_id,-1) =
148 nvl(b.generate_using_formula_id,-1)
149 AND nvl(a.reprice_flag,'x') = nvl(b.reprice_flag,'x')
150 AND nvl(a.accrual_flag, 'x') = nvl(b.accrual_flag, 'x')
151 AND nvl(a.pricing_group_sequence, -1) =
152 nvl(b.pricing_group_sequence, -1)
153 AND nvl(a.incompatibility_grp_code, 'x') =
154 nvl(b.incompatibility_grp_code, 'x')
155 AND nvl(a.list_line_no, 'x') = nvl(b.list_line_no, 'x')
156 AND nvl(a.product_precedence, -1) =
157 nvl(b.product_precedence, -1)
158 AND nvl(a.pricing_phase_id, -1) = nvl(b.pricing_phase_id, -1)
159 AND nvl(a.number_expiration_periods, -1) =
160 nvl(b.number_expiration_periods, -1)
161 AND nvl(a.expiration_period_uom, 'x') =
162 nvl(b.expiration_period_uom, 'x')
163 AND nvl(a.estim_gl_value, -1) = nvl(b.estim_gl_value, -1)
164 AND nvl(a.accrual_conversion_rate, -1) =
165 nvl(b.accrual_conversion_rate, -1)
166 AND nvl(a.benefit_price_list_line_id, -1) =
167 nvl(b.benefit_price_list_line_id, -1)
168 AND nvl(a.proration_type_code, 'x') =
169 nvl(b.proration_type_code, 'x')
170 AND nvl(a.benefit_qty, -1) = nvl(b.benefit_qty, -1)
171 AND nvl(a.benefit_uom_code, 'x') = nvl(b.benefit_uom_code, 'x')
172 AND nvl(a.charge_type_code, 'x') = nvl(b.charge_type_code, 'x')
173 AND nvl(a.charge_subtype_code, 'x') =
174 nvl(b.charge_subtype_code, 'x')
175 AND nvl(a.benefit_limit, -1) = nvl(b.benefit_limit, -1)
176 AND nvl(a.include_on_returns_flag, 'x') =
177 nvl(b.include_on_returns_flag, 'x')
178 AND nvl(a.qualification_ind, -1) = nvl(b.qualification_ind, -1)
179 ) FOR UPDATE;
180
181 */
182
183 /* Added by dhgupta for 2100785 */
184
185 CURSOR list_lines_cur (a_new_list_header_id NUMBER)
186 IS
187 SELECT list_line_id
188 FROM qp_list_lines
189 WHERE list_header_id=a_new_list_header_id;
190
191 l_status BOOLEAN := TRUE;
192 l_rows number := 0;
193 l_revision boolean := FALSE;
194 l_effdates boolean := FALSE;
195 l_dup_sdate DATE := NULL;
196 l_dup_edate DATE := NULL;
197 l_PRICE_LIST_LINE_tbl QP_Price_List_PUB.Price_List_Line_Tbl_Type;
198 l_x_PRICE_LIST_rec QP_Price_List_PUB.Price_List_Rec_Type;
199 l_x_QUALIFIERS_tbl QP_Qualifier_Rules_Pub.Qualifiers_Tbl_Type;
200 l_x_PRICING_ATTR_tbl QP_Price_List_PUB.Pricing_Attr_Tbl_Type;
201 l_return_status VARCHAR2(1);
202 l_x_PRICE_LIST_LINE_tbl QP_Price_List_PUB.Price_List_Line_Tbl_Type;
203 x_msg_count NUMBER:=0;
204 x_msg_data VARCHAR2(2000);
205 K NUMBER:=1;
206
207 BEGIN
208
209 --If the Retain Effective Dates flag is not checked then copied price list
210 --lines will have null effective dates. This will mean that there is a
211 --possibility that lines may be duplicated. To prevent this, all but one
212 --duplicate lines are deleted here.
213
214 IF p_effective_dates_flag = 'N' THEN
215
216 /* Added by dhgupta for 2100785 */
217
218 FOR l_list_lines_id IN list_lines_cur(p_new_list_header_id)
219 LOOP
220 l_status := QP_VALIDATE_PLL_PRICING_ATTR.Check_Dup_Pra(NULL,
221 NULL,
222 NULL,
223 l_list_lines_id.list_line_id,
224 p_new_list_header_id,
225 l_rows,
226 l_revision,
227 l_effdates,
228 l_dup_sdate,
229 l_dup_edate);
230 IF NVL(l_rows,0) = 0 THEN
231
232 /* Added for 2397463 */
233
234 l_PRICE_LIST_LINE_tbl(K).list_line_id := l_list_lines_id.list_line_id;
235 l_PRICE_LIST_LINE_tbl(K).operation := QP_GLOBALS.G_OPR_DELETE;
236
237 QP_LIST_HEADERS_PVT.Process_PRICE_LIST
238 ( p_api_version_number => 1.0
239 , p_init_msg_list => FND_API.G_TRUE
240 , x_return_status => l_return_status
241 , x_msg_count => x_msg_count
242 , x_msg_data => x_msg_data
243 , p_PRICE_LIST_LINE_tbl => l_PRICE_LIST_LINE_tbl
244 , x_PRICE_LIST_LINE_tbl => l_x_PRICE_LIST_LINE_tbl
245 , x_PRICE_LIST_rec => l_x_PRICE_LIST_rec
246 , x_QUALIFIERS_tbl => l_x_QUALIFIERS_tbl
247 , x_PRICING_ATTR_tbl => l_x_PRICING_ATTR_tbl
248 );
249
250 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
251 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
252 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
253 RAISE FND_API.G_EXC_ERROR;
254 END IF;
255
256 /* Commented out for 2397463 */
257 /*
258 DELETE qp_pricing_attributes pa
259 WHERE pa.list_line_id = l_list_lines_id.list_line_id;
260
261 DELETE qp_list_lines
262 WHERE list_line_id = l_list_lines_id.list_line_id;
263 */
264 END IF;
265
266 END LOOP;
267
268 /* Commented out by dhgupta for 2100785 */
269 /*
270 FOR l_del_dup_cur_rec IN del_dup_cur(p_new_list_header_id)
271 LOOP
272
273 DELETE qp_pricing_attributes pa
274 WHERE pa.list_line_id = l_del_dup_cur_rec.list_line_id;
275
276 DELETE qp_list_lines
277 WHERE CURRENT OF del_dup_cur;
278
279 END LOOP;
280 */
281 END IF; /* End of IF p_effective_dates_flag = 'N' */
282
283 EXCEPTION
284
285 WHEN OTHERS THEN
286
287 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
288 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, 'Error in Deleting Duplicate Lines');
289 END IF;
290
291 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
292
293 END Delete_Duplicate_Lines;
294
295
296 /***********************************************************************/
297 /* Procedure to copy discounts - headers, lines, attributes, qualifiers*/
298 /***********************************************************************/
299
300 PROCEDURE Copy_Discounts
301 (
302 p_from_list_header_id NUMBER,
303 p_new_list_header_id NUMBER,
304 p_context VARCHAR2,
305 p_attribute VARCHAR2,
306 p_user_id NUMBER,
307 p_conc_login_id NUMBER,
308 p_conc_program_application_id NUMBER,
309 p_conc_program_id NUMBER,
310 p_conc_request_id NUMBER
311 )
312 IS
313
314 l_mapping_tbl mapping_tbl;
315
316 l_name VARCHAR2(240);
317 l_description VARCHAR2(2000);
318 l_version_no VARCHAR2(30);
319 l_new_discount_header_id NUMBER;
320 l_new_qualifier_id NUMBER;
321 l_new_discount_line_id NUMBER;
322 l_new_pricing_attribute_id NUMBER;
323 l_new_rltd_modifier_id NUMBER;
324
325 l_count NUMBER := 0;
326 l_new_from_id NUMBER;
327 l_new_to_id NUMBER;
328
329 l_pric_attr_value_from_number NUMBER := NULL;
330 l_pric_attr_value_to_number NUMBER := NULL;
331
332 l_list_type_code VARCHAR2(30) := '';
333 l_active_flag VARCHAR2(1) := '';
334 l_qual_attr_value_from_number NUMBER := NULL;
335 l_qual_attr_value_to_number NUMBER := NULL;
336
337 x_result VARCHAR2(1);
338
339 --bug#6636843
340 l_new_orig_system_hrd_Ref VARCHAR2(50);
341
342 CURSOR qp_from_discounts_cur(p_from_list_header_id NUMBER, p_context VARCHAR2,
343 p_attribute VARCHAR2)
344 IS
345 SELECT list_header_id
346 FROM qp_qualifiers a
347 WHERE a.qualifier_context = p_context
348 AND a.qualifier_attribute = p_attribute
349 AND a.qualifier_attr_value = TO_CHAR(p_from_list_header_id)
350 AND a.list_header_id IN
351 (SELECT list_header_id
352 --fix for bug 4673872
353 FROM qp_list_headers_all_b
354 WHERE list_type_code = 'DLT');
355
356 CURSOR qp_qualifiers_cur(p_from_discount_header_id NUMBER)
357 IS
358 SELECT *
359 FROM qp_qualifiers
360 WHERE list_header_id = p_from_discount_header_id;
361
362 CURSOR qp_discount_lines_cur(p_from_discount_header_id NUMBER)
363 IS
364 SELECT *
365 FROM qp_list_lines
366 WHERE list_header_id = p_from_discount_header_id;
367
368 CURSOR qp_pricing_attributes_cur(p_from_discount_line_id NUMBER)
369 IS
370 SELECT *
371 FROM qp_pricing_attributes
372 WHERE list_line_id = p_from_discount_line_id;
373
374 CURSOR qp_rltd_modifiers_cur(a_list_line_id NUMBER)
375 IS
376 SELECT *
377 FROM qp_rltd_modifiers
378 WHERE from_rltd_modifier_id = a_list_line_id;
379
380 --bug#6636843
381 CURSOR qp_list_headers_tl_cur(a_list_hdr_id qp_list_headers_tl.list_header_id%TYPE)
382 IS
383 SELECT name, description, version_no
384 FROM qp_list_headers_tl
385 WHERE list_header_id = a_list_hdr_id;
386
387
388 BEGIN
389
390 FOR qp_from_discounts_rec IN qp_from_discounts_cur(p_from_list_header_id,
391 p_context,
392 p_attribute)
393 LOOP
394 /* For every old(from) discount, Copy discount header records */
395
396 l_count := 0; --Reset the mapping table count for each discount header
397
398 --Select next discount_header_id
399
400 SELECT qp_list_headers_b_s.nextval
401 INTO l_new_discount_header_id
402 FROM dual;
403
404 --bug#6636843
405 l_new_orig_system_hrd_Ref := QP_PRICE_LIST_UTIL.Get_Orig_Sys_Hdr(l_new_discount_header_id);
406
407 --Discount Header Information
408
409 INSERT INTO qp_list_headers_all_b
410 (
411 list_header_id,
412 creation_date,
413 created_by,
414 last_update_date,
415 last_updated_by,
416 last_update_login,
417 program_application_id,
418 program_id,
419 program_update_date,
420 request_id,
421 list_type_code,
422 start_date_active,
423 end_date_active,
424 automatic_flag,
425 -- exclusive_flag,
426 currency_code,
427 rounding_factor,
428 ship_method_code,
429 freight_terms_code,
430 terms_id,
431 context,
432 attribute1,
433 attribute2,
434 attribute3,
435 attribute4,
436 attribute5,
437 attribute6,
438 attribute7,
439 attribute8,
440 attribute9,
441 attribute10,
442 attribute11,
443 attribute12,
444 attribute13,
445 attribute14,
446 attribute15,
447 comments,
448 discount_lines_flag,
449 gsa_indicator,
450 prorate_flag,
451 source_system_code,
452 active_flag,
453 parent_list_header_id,
454 start_date_active_first,
455 end_date_active_first,
456 active_date_first_type,
457 start_date_active_second,
458 end_date_active_second,
459 active_date_second_type,
460 ask_for_flag,
461 currency_header_id, -- Multi-Currency SunilPandey
462 pte_code -- Attribute Manager, Giri
463 --ENH Upgrade BOAPI for orig_sys...ref RAVI
464 ,ORIG_SYSTEM_HEADER_REF
465 )
466
467 SELECT
468 l_new_discount_header_id,
469 sysdate,
470 p_user_id,
471 sysdate,
472 p_user_id,
473 p_conc_login_id,
474 p_conc_program_application_id,
475 p_conc_program_id,
476 sysdate,
477 p_conc_request_id,
478 list_type_code,
479 start_date_active,
480 end_date_active,
481 automatic_flag,
482 -- exclusive_flag,
483 currency_code,
484 rounding_factor,
485 ship_method_code,
486 freight_terms_code,
487 terms_id,
488 context,
489 attribute1,
490 attribute2,
491 attribute3,
492 attribute4,
493 attribute5,
494 attribute6,
495 attribute7,
496 attribute8,
497 attribute9,
498 attribute10,
499 attribute11,
500 attribute12,
501 attribute13,
502 attribute14,
503 attribute15,
504 comments,
505 discount_lines_flag,
506 gsa_indicator,
507 prorate_flag,
508 source_system_code,
509 active_flag,
510 parent_list_header_id,
511 start_date_active_first,
512 end_date_active_first,
513 active_date_first_type,
514 start_date_active_second,
515 end_date_active_second,
516 active_date_second_type,
517 ask_for_flag,
518 currency_header_id, -- Multi-Currency SunilPandey
519 pte_code -- Attribute Manager, Giri
520 --ENH Upgrade BOAPI for orig_sys...ref RAVI
521 --,nvl(ORIG_SYSTEM_HEADER_REF,QP_PRICE_LIST_UTIL.Get_Orig_Sys_Hdr(l_new_discount_header_id))
522 -- Bug 5201918
523 --bug#6636843. Moving this function call to before the query and storing the value in a
524 --variable and then using it.
525 --,QP_PRICE_LIST_UTIL.Get_Orig_Sys_Hdr(l_new_discount_header_id)
526 ,l_new_orig_system_hrd_Ref
527 --fix for bug 4673872
528 FROM qp_list_headers_b
529 WHERE list_header_id = qp_from_discounts_rec.list_header_id;
530
531
532 -- Object Security - sfiresto
533 QP_security.create_default_grants( p_instance_type => QP_security.G_MODIFIER_OBJECT,
534 p_instance_pk1 => l_new_discount_header_id,
535 x_return_status => x_result);
536 -- End addition for Object Security
537
538 --bug#6636843. Loop is added to traverse through table qp_list_headers_tl
539 -- for the list being copied since there might be multiple rows in this
540 -- table for that list header.
541 FOR l_qp_list_headers_tl_rec IN
542 qp_list_headers_tl_cur(qp_from_discounts_rec.list_header_id)
543 LOOP
544
545 -- query is commented to fix bug 6636843
546 -- SELECT name, description, version_no
547 -- INTO l_name, l_description, l_version_no
548 --fix for bug 4673872
549 --FROM qp_list_headers_tl
550 --WHERE list_header_id = qp_from_discounts_rec.list_header_id;
551
552 INSERT INTO qp_list_headers_tl
553 (last_update_login,
554 name,
555 description,
556 creation_date,
557 created_by,
558 last_update_date,
559 last_updated_by,
560 list_header_id,
561 language,
562 source_lang,
563 version_no
564 )
565 SELECT
566 p_conc_login_id,
567 --l_name || to_char(l_new_discount_header_id),
568 l_qp_list_headers_tl_rec.name || to_char(l_new_discount_header_id), --bug#6636843.
569 --l_description,
570 l_qp_list_headers_tl_rec.description, --bug#6636843.
571 sysdate,
572 p_user_id,
573 sysdate,
574 p_user_id,
575 l_new_discount_header_id,
576 l.language_code,
577 userenv('LANG'),
578 --l_version_no
579 l_qp_list_headers_tl_rec.version_no --bug#6636843.
580 FROM fnd_languages l
581 WHERE l.installed_flag IN ('I', 'B')
582 AND NOT EXISTS (SELECT NULL
583 FROM qp_list_headers_tl t
584 WHERE t.list_header_id = l_new_discount_header_id
585 AND t.language = l.language_code);
586
587
588 END LOOP;
589
590 /* Copy all qualifiers for the discount and in case of the qualifier
591 being the from-pricelist replace it with the new pricelist*/
592
593 FOR l_qp_qualifiers_rec IN
594 qp_qualifiers_cur(qp_from_discounts_rec.list_header_id)
595 LOOP
596
597 --Get new qualifier_id
598 SELECT qp_qualifiers_s.nextval
599 INTO l_new_qualifier_id
600 FROM dual;
601
602 IF l_qp_qualifiers_rec.qualifier_attr_value =
603 TO_CHAR(p_from_list_header_id) AND
604 l_qp_qualifiers_rec.qualifier_context = p_context AND
605 l_qp_qualifiers_rec.qualifier_attribute = p_attribute
606 THEN
607 l_qp_qualifiers_rec.qualifier_attr_value :=
608 TO_CHAR(p_new_list_header_id);
609 END IF;
610
611 BEGIN
612
613 SELECT ACTIVE_FLAG, LIST_TYPE_CODE
614 INTO l_active_flag, l_list_type_code
615 --fix for bug 4673872
616 FROM QP_LIST_HEADERS_ALL_B
617 WHERE LIST_HEADER_ID = l_new_discount_header_id;
618
619 EXCEPTION
620 WHEN OTHERS THEN
621 NULL;
622 END;
623
624 IF l_qp_qualifiers_rec.qualifier_datatype = 'N'
625 then
626
627 BEGIN
628
629 l_qual_attr_value_from_number :=
630 qp_number.canonical_to_number(l_qp_qualifiers_rec.qualifier_attr_value);
631
632 l_qual_attr_value_to_number :=
633 qp_number.canonical_to_number(l_qp_qualifiers_rec.qualifier_attr_value_to);
634
635 EXCEPTION
636 WHEN VALUE_ERROR THEN
637 NULL;
638 WHEN OTHERS THEN
639 NULL;
640 END;
641
642 end if;
643
644 --Insert new qualifier
645 INSERT INTO qp_qualifiers
646 (
647 qualifier_id,
648 creation_date,
649 created_by,
650 last_update_date,
651 last_updated_by,
652 last_update_login,
653 program_application_id,
654 program_id,
655 program_update_date,
656 request_id,
657 excluder_flag,
658 comparison_operator_code,
659 qualifier_context,
660 qualifier_attribute,
661 context,
662 attribute1,
663 attribute2,
664 attribute3,
665 attribute4,
666 attribute5,
667 attribute6,
668 attribute7,
669 attribute8,
670 attribute9,
671 attribute10,
672 attribute11,
673 attribute12,
674 attribute13,
675 attribute14,
676 attribute15,
677 qualifier_rule_id,
678 qualifier_grouping_no,
679 qualifier_attr_value,
680 list_header_id,
681 list_line_id,
682 created_from_rule_id,
683 start_date_active,
684 end_date_active,
685 qualifier_precedence,
686 qualifier_datatype,
687 qualifier_attr_value_to,
688 active_flag,
689 list_type_code,
690 qual_attr_value_from_number,
691 qual_attr_value_to_number,
692 search_ind,
693 distinct_row_count,
694 qualifier_group_cnt,
695 header_quals_exist_flag,
696 qualify_hier_descendents_flag -- Added for TCA
697 --ENH Upgrade BOAPI for orig_sys...ref RAVI
698 ,ORIG_SYS_QUALIFIER_REF
699 ,ORIG_SYS_LINE_REF
700 ,ORIG_SYS_HEADER_REF
701 )
702 VALUES
703 (
704 l_new_qualifier_id,
705 sysdate,
706 p_user_id,
707 sysdate,
708 p_user_id,
709 p_conc_login_id,
710 p_conc_program_application_id,
711 p_conc_program_id,
712 sysdate,
713 p_conc_request_id,
714 l_qp_qualifiers_rec.excluder_flag,
715 l_qp_qualifiers_rec.comparison_operator_code,
716 l_qp_qualifiers_rec.qualifier_context,
717 l_qp_qualifiers_rec.qualifier_attribute,
718 l_qp_qualifiers_rec.context,
719 l_qp_qualifiers_rec.attribute1,
720 l_qp_qualifiers_rec.attribute2,
721 l_qp_qualifiers_rec.attribute3,
722 l_qp_qualifiers_rec.attribute4,
723 l_qp_qualifiers_rec.attribute5,
724 l_qp_qualifiers_rec.attribute6,
725 l_qp_qualifiers_rec.attribute7,
726 l_qp_qualifiers_rec.attribute8,
727 l_qp_qualifiers_rec.attribute9,
728 l_qp_qualifiers_rec.attribute10,
729 l_qp_qualifiers_rec.attribute11,
730 l_qp_qualifiers_rec.attribute12,
731 l_qp_qualifiers_rec.attribute13,
732 l_qp_qualifiers_rec.attribute14,
733 l_qp_qualifiers_rec.attribute15,
734 l_qp_qualifiers_rec.qualifier_rule_id,
735 l_qp_qualifiers_rec.qualifier_grouping_no,
736 l_qp_qualifiers_rec.qualifier_attr_value,
737 l_new_discount_header_id,
738 l_qp_qualifiers_rec.list_line_id,
739 l_qp_qualifiers_rec.created_from_rule_id,
740 l_qp_qualifiers_rec.start_date_active,
741 l_qp_qualifiers_rec.end_date_active,
742 l_qp_qualifiers_rec.qualifier_precedence,
743 l_qp_qualifiers_rec.qualifier_datatype,
744 l_qp_qualifiers_rec.qualifier_attr_value_to,
745 l_active_flag,
746 l_list_type_code,
747 l_qual_attr_value_from_number,
748 l_qual_attr_value_to_number,
749 l_qp_qualifiers_rec.search_ind,
750 l_qp_qualifiers_rec.distinct_row_count,
751 l_qp_qualifiers_rec.qualifier_group_cnt,
752 l_qp_qualifiers_rec.header_quals_exist_flag,
753 l_qp_qualifiers_rec.qualify_hier_descendents_flag -- Added for TCA
754 --ENH Upgrade BOAPI for orig_sys...ref RAVI
755 ,to_char(l_new_qualifier_id)
756 ,(select l.ORIG_SYS_LINE_REF from qp_list_lines l where l.list_line_id=l_qp_qualifiers_rec.list_line_id)
757 ,(select h.ORIG_SYSTEM_HEADER_REF from qp_list_headers_b h where h.list_header_id=l_new_discount_header_id)
758 );
759
760 END LOOP; /* For copying qualifiers */
761
762
763 /* Copy all lines for the discount */
764
765 FOR l_qp_discount_lines_rec IN
766 qp_discount_lines_cur (qp_from_discounts_rec.list_header_id)
767 LOOP
768
769 --Get New Discount Line Id
770 SELECT qp_list_lines_s.nextval
771 INTO l_new_discount_line_id
772 FROM dual;
773
774 --Insert Discount Line
775 INSERT INTO qp_list_lines
776 (
777 list_line_id,
778 creation_date,
779 created_by,
780 last_update_date,
781 last_updated_by,
782 last_update_login,
783 program_application_id,
784 program_id,
785 program_update_date,
786 request_id,
787 list_header_id,
788 list_line_type_code,
789 start_date_active,
790 end_date_active,
791 automatic_flag,
792 modifier_level_code,
793 list_price,
794 primary_uom_flag,
795 inventory_item_id,
796 organization_id,
797 related_item_id,
798 relationship_type_id,
799 substitution_context,
800 substitution_attribute,
801 substitution_value,
802 revision,
803 revision_date,
804 revision_reason_code,
805 context,
806 attribute1,
807 attribute2,
808 comments,
809 attribute3,
810 attribute4,
811 attribute5,
812 attribute6,
813 attribute7,
814 attribute8,
815 attribute9,
816 attribute10,
817 attribute11,
818 attribute12,
819 attribute13,
820 attribute14,
821 attribute15,
822 price_break_type_code,
823 percent_price,
824 price_by_formula_id,
825 number_effective_periods,
826 effective_period_uom,
827 arithmetic_operator,
828 operand,
829 override_flag,
830 print_on_invoice_flag,
831 rebate_transaction_type_code,
832 estim_accrual_rate,
833 generate_using_formula_id,
834 reprice_flag,
835 accrual_flag,
836 pricing_group_sequence,
837 incompatibility_grp_code,
838 list_line_no,
839 product_precedence,
840 pricing_phase_id,
841 expiration_period_start_date,
842 number_expiration_periods,
843 expiration_period_uom,
844 expiration_date,
845 estim_gl_value,
846 accrual_conversion_rate,
847 benefit_price_list_line_id,
848 proration_type_code,
849 benefit_qty,
850 benefit_uom_code,
851 charge_type_code,
852 charge_subtype_code,
853 benefit_limit,
854 include_on_returns_flag,
855 qualification_ind
856 --ENH Upgrade BOAPI for orig_sys...ref RAVI
857 ,ORIG_SYS_LINE_REF
858 ,ORIG_SYS_HEADER_REF
859 )
860 VALUES
861 (
862 l_new_discount_line_id,
863 sysdate,
864 p_user_id,
865 sysdate,
866 p_user_id,
867 p_conc_login_id,
868 p_conc_program_application_id,
869 p_conc_program_id,
870 sysdate,
871 p_conc_request_id,
872 l_new_discount_header_id,
873 l_qp_discount_lines_rec.list_line_type_code,
874 l_qp_discount_lines_rec.start_date_active,
875 l_qp_discount_lines_rec.end_date_active,
876 l_qp_discount_lines_rec.automatic_flag,
877 l_qp_discount_lines_rec.modifier_level_code,
878 l_qp_discount_lines_rec.list_price,
879 l_qp_discount_lines_rec.primary_uom_flag,
880 l_qp_discount_lines_rec.inventory_item_id,
881 l_qp_discount_lines_rec.organization_id,
882 l_qp_discount_lines_rec.related_item_id,
883 l_qp_discount_lines_rec.relationship_type_id,
884 l_qp_discount_lines_rec.substitution_context,
885 l_qp_discount_lines_rec.substitution_attribute,
886 l_qp_discount_lines_rec.substitution_value,
887 l_qp_discount_lines_rec.revision,
888 l_qp_discount_lines_rec.revision_date,
889 l_qp_discount_lines_rec.revision_reason_code,
890 l_qp_discount_lines_rec.context,
891 l_qp_discount_lines_rec.attribute1,
892 l_qp_discount_lines_rec.attribute2,
893 l_qp_discount_lines_rec.comments,
894 l_qp_discount_lines_rec.attribute3,
895 l_qp_discount_lines_rec.attribute4,
896 l_qp_discount_lines_rec.attribute5,
897 l_qp_discount_lines_rec.attribute6,
898 l_qp_discount_lines_rec.attribute7,
899 l_qp_discount_lines_rec.attribute8,
900 l_qp_discount_lines_rec.attribute9,
901 l_qp_discount_lines_rec.attribute10,
902 l_qp_discount_lines_rec.attribute11,
903 l_qp_discount_lines_rec.attribute12,
904 l_qp_discount_lines_rec.attribute13,
905 l_qp_discount_lines_rec.attribute14,
906 l_qp_discount_lines_rec.attribute15,
907 l_qp_discount_lines_rec.price_break_type_code,
908 l_qp_discount_lines_rec.percent_price,
909 l_qp_discount_lines_rec.price_by_formula_id,
910 l_qp_discount_lines_rec.number_effective_periods,
911 l_qp_discount_lines_rec.effective_period_uom,
912 l_qp_discount_lines_rec.arithmetic_operator,
913 l_qp_discount_lines_rec.operand,
914 l_qp_discount_lines_rec.override_flag,
915 l_qp_discount_lines_rec.print_on_invoice_flag,
916 l_qp_discount_lines_rec.rebate_transaction_type_code,
917 l_qp_discount_lines_rec.estim_accrual_rate,
918 l_qp_discount_lines_rec.generate_using_formula_id,
919 l_qp_discount_lines_rec.reprice_flag,
920 l_qp_discount_lines_rec.accrual_flag,
921 l_qp_discount_lines_rec.pricing_group_sequence,
922 l_qp_discount_lines_rec.incompatibility_grp_code,
923 l_qp_discount_lines_rec.list_line_no,
924 l_qp_discount_lines_rec.product_precedence,
925 l_qp_discount_lines_rec.pricing_phase_id,
926 l_qp_discount_lines_rec.expiration_period_start_date,
927 l_qp_discount_lines_rec.number_expiration_periods,
928 l_qp_discount_lines_rec.expiration_period_uom,
929 l_qp_discount_lines_rec.expiration_date,
930 l_qp_discount_lines_rec.estim_gl_value,
931 l_qp_discount_lines_rec.accrual_conversion_rate,
932 l_qp_discount_lines_rec.benefit_price_list_line_id,
933 l_qp_discount_lines_rec.proration_type_code,
934 l_qp_discount_lines_rec.benefit_qty,
935 l_qp_discount_lines_rec.benefit_uom_code,
936 l_qp_discount_lines_rec.charge_type_code,
937 l_qp_discount_lines_rec.charge_subtype_code,
938 l_qp_discount_lines_rec.benefit_limit,
939 l_qp_discount_lines_rec.include_on_returns_flag,
940 l_qp_discount_lines_rec.qualification_ind
941 --ENH Upgrade BOAPI for orig_sys...ref RAVI
942 ,to_char(l_new_discount_line_id)
943 ,(select h.ORIG_SYSTEM_HEADER_REF from qp_list_headers_b h where h.list_header_id=l_new_discount_header_id)
944 );
945
946
947 /*If the discount_line_rec is a Price Break Parent Line or Price Break Line
948 then store the old and new discountlineid in a mapping-array for later use*/
949
950 IF l_qp_discount_lines_rec.list_line_type_code = 'PBH' OR
951 Price_Break_Line(l_qp_discount_lines_rec.list_line_id)
952 THEN
953 l_count := l_count + 1;
954 l_mapping_tbl(l_count).list_line_type_code :=
955 l_qp_discount_lines_rec.list_line_type_code;
956 l_mapping_tbl(l_count).old_list_line_id :=
957 l_qp_discount_lines_rec.list_line_id;
958 l_mapping_tbl(l_count).new_list_line_id := l_new_discount_line_id;
959 END IF;
960
961
962 /* Copy the qp_pricing_attributes records for each discount line being
963 copied */
964
965 FOR l_qp_pricing_attributes_rec IN qp_pricing_attributes_cur
966 (l_qp_discount_lines_rec.list_line_id)
967 -- basically the from_discount_list_line_id
968 LOOP
969
970 -- Get next pricing_attribute_id
971 SELECT qp_pricing_attributes_s.nextval
972 INTO l_new_pricing_attribute_id
973 FROM dual;
974
975
976 IF l_qp_pricing_attributes_rec.pricing_attribute_datatype = 'N'
977 then
978
979 BEGIN
980
981 l_pric_attr_value_from_number :=
982 qp_number.canonical_to_number(l_qp_pricing_attributes_rec.pricing_attr_value_from);
983
984 l_pric_attr_value_to_number :=
985 qp_number.canonical_to_number(l_qp_pricing_attributes_rec.pricing_attr_value_to);
986
987 EXCEPTION
988 WHEN VALUE_ERROR THEN
989 NULL;
990 WHEN OTHERS THEN
991 NULL;
992 END;
993
994 end if;
995
996
997 INSERT INTO qp_pricing_attributes
998 (pricing_attribute_id,
999 creation_date,
1000 created_by,
1001 last_update_date,
1002 last_updated_by,
1003 last_update_login,
1004 program_application_id,
1005 program_id,
1006 program_update_date,
1007 request_id,
1008 list_line_id,
1009 list_header_id,
1010 pricing_phase_id,
1011 qualification_ind,
1012 excluder_flag,
1013 accumulate_flag,
1014 product_attribute_context,
1015 product_attribute,
1016 product_attr_value,
1017 product_uom_code,
1018 pricing_attribute_context,
1019 pricing_attribute,
1020 pricing_attr_value_from,
1021 pricing_attr_value_to,
1022 attribute_grouping_no,
1023 context,
1024 attribute1,
1025 attribute2,
1026 attribute3,
1027 attribute4,
1028 attribute5,
1029 attribute6,
1030 attribute7,
1031 attribute8,
1032 attribute9,
1033 attribute10,
1034 attribute11,
1035 attribute12,
1036 attribute13,
1037 attribute14,
1038 attribute15,
1039 product_attribute_datatype,
1040 pricing_attribute_datatype,
1041 comparison_operator_code,
1042 pricing_attr_value_from_number,
1043 pricing_attr_value_to_number
1044 --ENH Upgrade BOAPI for orig_sys...ref RAVI
1045 ,ORIG_SYS_PRICING_ATTR_REF
1046 ,ORIG_SYS_LINE_REF
1047 ,ORIG_SYS_HEADER_REF
1048 )
1049 VALUES
1050 (l_new_pricing_attribute_id,
1051 sysdate,
1052 p_user_id,
1053 sysdate,
1054 p_user_id,
1055 p_conc_login_id,
1056 p_conc_program_application_id,
1057 p_conc_program_id,
1058 sysdate,
1059 p_conc_request_id,
1060 l_new_discount_line_id, /* new discount line id */
1061 l_new_discount_header_id,
1062 l_qp_pricing_attributes_rec.pricing_phase_id,
1063 l_qp_pricing_attributes_rec.qualification_ind,
1064 l_qp_pricing_attributes_rec.excluder_flag,
1065 l_qp_pricing_attributes_rec.accumulate_flag,
1066 l_qp_pricing_attributes_rec.product_attribute_context,
1067 l_qp_pricing_attributes_rec.product_attribute,
1068 l_qp_pricing_attributes_rec.product_attr_value,
1069 l_qp_pricing_attributes_rec.product_uom_code,
1070 l_qp_pricing_attributes_rec.pricing_attribute_context,
1071 l_qp_pricing_attributes_rec.pricing_attribute,
1072 l_qp_pricing_attributes_rec.pricing_attr_value_from,
1073 l_qp_pricing_attributes_rec.pricing_attr_value_to,
1074 l_qp_pricing_attributes_rec.attribute_grouping_no,
1075 l_qp_pricing_attributes_rec.context,
1076 l_qp_pricing_attributes_rec.attribute1,
1077 l_qp_pricing_attributes_rec.attribute2,
1078 l_qp_pricing_attributes_rec.attribute3,
1079 l_qp_pricing_attributes_rec.attribute4,
1080 l_qp_pricing_attributes_rec.attribute5,
1081 l_qp_pricing_attributes_rec.attribute6,
1082 l_qp_pricing_attributes_rec.attribute7,
1083 l_qp_pricing_attributes_rec.attribute8,
1084 l_qp_pricing_attributes_rec.attribute9,
1085 l_qp_pricing_attributes_rec.attribute10,
1086 l_qp_pricing_attributes_rec.attribute11,
1087 l_qp_pricing_attributes_rec.attribute12,
1088 l_qp_pricing_attributes_rec.attribute13,
1089 l_qp_pricing_attributes_rec.attribute14,
1090 l_qp_pricing_attributes_rec.attribute15,
1091 l_qp_pricing_attributes_rec.product_attribute_datatype,
1092 l_qp_pricing_attributes_rec.pricing_attribute_datatype,
1093 l_qp_pricing_attributes_rec.comparison_operator_code,
1094 l_pric_attr_value_from_number,
1095 l_pric_attr_value_to_number
1096 --ENH Upgrade BOAPI for orig_sys...ref RAVI
1097 ,to_char(l_new_pricing_attribute_id)
1098 ,(select l.ORIG_SYS_LINE_REF from qp_list_lines l where l.list_line_id=l_new_discount_line_id)
1099 ,(select h.ORIG_SYSTEM_HEADER_REF from qp_list_headers_b h where h.list_header_id=l_new_discount_header_id)
1100 );
1101
1102 END LOOP; /* For copying pricing attributes for each discount line */
1103
1104 END LOOP; /* For copying discount lines*/
1105
1106 /* Copy qp_rltd_modifiers for the Price Break Parent list_lines chosen
1107 above which are stored in the mapping table */
1108
1109 IF l_mapping_tbl.COUNT > 0 THEN
1110 FOR l_count IN 1..l_mapping_tbl.COUNT
1111 LOOP
1112
1113 IF l_mapping_tbl(l_count).list_line_type_code = 'PBH' THEN
1114
1115 FOR l_qp_rltd_modifiers_rec IN qp_rltd_modifiers_cur(
1116 l_mapping_tbl(l_count).old_list_line_id)
1117 LOOP
1118
1119 SELECT qp_rltd_modifiers_s.nextval
1120 INTO l_new_rltd_modifier_id
1121 FROM dual;
1122
1123 l_new_from_id := Get_New_Id(
1124 l_qp_rltd_modifiers_rec.from_rltd_modifier_id,
1125 l_mapping_tbl);
1126 l_new_to_id := Get_New_Id(
1127 l_qp_rltd_modifiers_rec.to_rltd_modifier_id,
1128 l_mapping_tbl);
1129 INSERT INTO qp_rltd_modifiers
1130 (creation_date,
1131 created_by,
1132 last_update_date,
1133 last_updated_by,
1134 last_update_login,
1135 context,
1136 attribute1,
1137 attribute2,
1138 attribute3,
1139 attribute4,
1140 attribute5,
1141 attribute6,
1142 attribute7,
1143 attribute8,
1144 attribute9,
1145 attribute10,
1146 attribute11,
1147 attribute12,
1148 attribute13,
1149 attribute14,
1150 attribute15,
1151 rltd_modifier_id,
1152 rltd_modifier_grp_no,
1153 from_rltd_modifier_id,
1154 to_rltd_modifier_id,
1155 rltd_modifier_grp_type
1156 )
1157 VALUES
1158 (sysdate,
1159 p_user_id,
1160 sysdate,
1161 p_user_id,
1162 p_conc_login_id,
1163 l_qp_rltd_modifiers_rec.context,
1164 l_qp_rltd_modifiers_rec.attribute1,
1165 l_qp_rltd_modifiers_rec.attribute2,
1166 l_qp_rltd_modifiers_rec.attribute3,
1167 l_qp_rltd_modifiers_rec.attribute4,
1168 l_qp_rltd_modifiers_rec.attribute5,
1169 l_qp_rltd_modifiers_rec.attribute6,
1170 l_qp_rltd_modifiers_rec.attribute7,
1171 l_qp_rltd_modifiers_rec.attribute8,
1172 l_qp_rltd_modifiers_rec.attribute9,
1173 l_qp_rltd_modifiers_rec.attribute10,
1174 l_qp_rltd_modifiers_rec.attribute11,
1175 l_qp_rltd_modifiers_rec.attribute12,
1176 l_qp_rltd_modifiers_rec.attribute13,
1177 l_qp_rltd_modifiers_rec.attribute14,
1178 l_qp_rltd_modifiers_rec.attribute15,
1179 l_new_rltd_modifier_id,
1180 l_qp_rltd_modifiers_rec.rltd_modifier_grp_no,
1181 l_new_from_id,
1182 l_new_to_id,
1183 l_qp_rltd_modifiers_rec.rltd_modifier_grp_type
1184 );
1185
1186 END LOOP; -- Loop through rltd modifiers records
1187 END IF; --For lines that are Parent Price Break lines
1188
1189 END LOOP; --Loop through l_mapping_tbl
1190 END IF; --If l_mapping_tbl has any records
1191
1192 END LOOP; /* for copying discount headers*/
1193
1194 END Copy_Discounts;
1195
1196
1197
1198 PROCEDURE Copy_Price_List
1199 (
1200 -- p_api_version_number IN NUMBER,
1201 -- p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1202 -- p_commit IN VARCHAR2 := FND_API.G_FALSE,
1203 -- x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
1204 -- x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
1205 -- x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
1206 errbuf OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
1207 retcode OUT NOCOPY /* file.sql.39 change */ NUMBER,
1208 p_from_list_header_id IN NUMBER,
1209 p_new_price_list_name IN VARCHAR2,
1210 p_description IN VARCHAR2,
1211 p_start_date_active IN VARCHAR2, --DATE, 2752276
1212 p_end_date_active IN VARCHAR2, --DATE, 2752276
1213 p_discount_flag IN VARCHAR2,
1214 p_segment1_lohi IN VARCHAR2,
1215 p_segment2_lohi IN VARCHAR2,
1216 p_segment3_lohi IN VARCHAR2,
1217 p_segment4_lohi IN VARCHAR2,
1218 p_segment5_lohi IN VARCHAR2,
1219 p_segment6_lohi IN VARCHAR2,
1220 p_segment7_lohi IN VARCHAR2,
1221 p_segment8_lohi IN VARCHAR2,
1222 p_segment9_lohi IN VARCHAR2,
1223 p_segment10_lohi IN VARCHAR2,
1224 p_segment11_lohi IN VARCHAR2,
1225 p_segment12_lohi IN VARCHAR2,
1226 p_segment13_lohi IN VARCHAR2,
1227 p_segment14_lohi IN VARCHAR2,
1228 p_segment15_lohi IN VARCHAR2,
1229 p_segment16_lohi IN VARCHAR2,
1230 p_segment17_lohi IN VARCHAR2,
1231 p_segment18_lohi IN VARCHAR2,
1232 p_segment19_lohi IN VARCHAR2,
1233 p_segment20_lohi IN VARCHAR2,
1234 -- p_org_id IN NUMBER,
1235 p_category_id IN NUMBER,
1236 p_category_set_id IN NUMBER, -- bug 4127037
1237 p_rounding_factor IN NUMBER,
1238 p_effective_dates_flag IN VARCHAR2,
1239 --added for moac bug 4673872
1240 p_global_flag IN VARCHAR2,
1241 p_org_id IN NUMBER
1242 )
1243 IS
1244
1245 --l_api_version_number CONSTANT NUMBER := 1.0;
1246 --l_api_name CONSTANT VARCHAR2(30) := 'Copy_Price_List';
1247 --l_return_status VARCHAR2(1);
1248 --l_msg_count NUMBER;
1249 --l_msg_buf VARCHAR2(2000);
1250 l_conc_request_id NUMBER := -1;
1251 l_conc_program_application_id NUMBER := -1;
1252 l_conc_program_id NUMBER := -1;
1253 l_conc_login_id NUMBER := -1;
1254 l_user_id NUMBER := -1;
1255 l_new_list_header_id NUMBER;
1256 l_new_qualifier_id NUMBER;
1257 l_new_list_line_id NUMBER;
1258 l_new_pricing_attribute_id NUMBER;
1259 l_new_rltd_modifier_id NUMBER;
1260 x_result VARCHAR2(1);
1261 insert_flag varchar2(1);
1262 l_cnt number:=0;
1263 l_line_id number:=null;
1264 l_min_list_line_id NUMBER;
1265 l_max_list_line_id NUMBER;
1266
1267
1268
1269 TYPE qp_list_lines_rec IS RECORD (
1270 list_line_id QP_LIST_LINES.list_line_id%TYPE,
1271 creation_date QP_LIST_LINES.creation_date%TYPE,
1272 created_by QP_LIST_LINES.created_by%TYPE,
1273 last_update_date QP_LIST_LINES.last_update_date%TYPE,
1274 last_updated_by QP_LIST_LINES.last_updated_by%TYPE,
1275 last_update_login QP_LIST_LINES.last_update_login%TYPE,
1276 program_application_id QP_LIST_LINES.program_application_id%TYPE,
1277 program_id QP_LIST_LINES.program_id%TYPE,
1278 program_update_date QP_LIST_LINES.program_update_date%TYPE,
1279 request_id QP_LIST_LINES.request_id%TYPE,
1280 list_header_id QP_LIST_LINES.list_header_id%TYPE,
1281 list_line_type_code QP_LIST_LINES.list_line_type_code%TYPE,
1282 automatic_flag QP_LIST_LINES.automatic_flag%TYPE,
1283 modifier_level_code QP_LIST_LINES.modifier_level_code%TYPE,
1284 list_price QP_LIST_LINES.list_price%TYPE,
1285 primary_uom_flag QP_LIST_LINES.primary_uom_flag%TYPE,
1286 inventory_item_id QP_LIST_LINES.inventory_item_id%TYPE,
1287 organization_id QP_LIST_LINES.organization_id%TYPE,
1288 related_item_id QP_LIST_LINES.related_item_id%TYPE,
1289 relationship_type_id QP_LIST_LINES.relationship_type_id%TYPE,
1290 substitution_context QP_LIST_LINES.substitution_context%TYPE,
1291 substitution_attribute QP_LIST_LINES.substitution_attribute%TYPE,
1292 substitution_value QP_LIST_LINES.substitution_value%TYPE,
1293 revision QP_LIST_LINES.revision%TYPE,
1294 revision_date QP_LIST_LINES.revision_date%TYPE,
1295 revision_reason_code QP_LIST_LINES.revision_reason_code%TYPE,
1296 context QP_LIST_LINES.context%TYPE,
1297 attribute1 QP_LIST_LINES.attribute1%TYPE,
1298 attribute2 QP_LIST_LINES.attribute2%TYPE,
1299 comments QP_LIST_LINES.comments%TYPE,
1300 attribute3 QP_LIST_LINES.attribute3%TYPE,
1301 attribute4 QP_LIST_LINES.attribute4%TYPE,
1302 attribute5 QP_LIST_LINES.attribute5%TYPE,
1303 attribute6 QP_LIST_LINES.attribute6%TYPE,
1304 attribute7 QP_LIST_LINES.attribute7%TYPE,
1305 attribute8 QP_LIST_LINES.attribute8%TYPE,
1306 attribute9 QP_LIST_LINES.attribute9%TYPE,
1307 attribute10 QP_LIST_LINES.attribute10%TYPE,
1308 attribute11 QP_LIST_LINES.attribute11%TYPE,
1309 attribute12 QP_LIST_LINES.attribute12%TYPE,
1310 attribute13 QP_LIST_LINES.attribute13%TYPE,
1311 attribute14 QP_LIST_LINES.attribute14%TYPE,
1312 attribute15 QP_LIST_LINES.attribute15%TYPE,
1313 price_break_type_code QP_LIST_LINES.price_break_type_code%TYPE,
1314 percent_price QP_LIST_LINES.percent_price%TYPE,
1315 price_by_formula_id QP_LIST_LINES.price_by_formula_id%TYPE,
1316 number_effective_periods QP_LIST_LINES.number_effective_periods%TYPE,
1317 effective_period_uom QP_LIST_LINES.effective_period_uom%TYPE,
1318 arithmetic_operator QP_LIST_LINES.arithmetic_operator%TYPE,
1319 operand QP_LIST_LINES.operand%TYPE,
1320 override_flag QP_LIST_LINES.override_flag%TYPE,
1321 print_on_invoice_flag QP_LIST_LINES.print_on_invoice_flag%TYPE,
1322 rebate_transaction_type_code QP_LIST_LINES.rebate_transaction_type_code%TYPE,
1323 estim_accrual_rate QP_LIST_LINES.estim_accrual_rate%TYPE,
1324 generate_using_formula_id QP_LIST_LINES.generate_using_formula_id%TYPE,
1325 start_date_active QP_LIST_LINES.start_date_active%TYPE,
1326 end_date_active QP_LIST_LINES.end_date_active%TYPE,
1327 reprice_flag QP_LIST_LINES.reprice_flag%TYPE,
1328 accrual_flag QP_LIST_LINES.accrual_flag%TYPE,
1329 pricing_group_sequence QP_LIST_LINES.pricing_group_sequence%TYPE,
1330 incompatibility_grp_code QP_LIST_LINES.incompatibility_grp_code%TYPE,
1331 list_line_no QP_LIST_LINES.list_line_no%TYPE,
1332 product_precedence QP_LIST_LINES.product_precedence%TYPE,
1333 pricing_phase_id QP_LIST_LINES.pricing_phase_id%TYPE,
1334 expiration_period_start_date QP_LIST_LINES.expiration_period_start_date%TYPE,
1335 number_expiration_periods QP_LIST_LINES.number_expiration_periods%TYPE,
1336 expiration_period_uom QP_LIST_LINES.expiration_period_uom%TYPE,
1337 expiration_date QP_LIST_LINES.expiration_date%TYPE,
1338 estim_gl_value QP_LIST_LINES.estim_gl_value%TYPE,
1339 accrual_conversion_rate QP_LIST_LINES.accrual_conversion_rate%TYPE,
1340 benefit_price_list_line_id QP_LIST_LINES.benefit_price_list_line_id%TYPE,
1341 proration_type_code QP_LIST_LINES.proration_type_code%TYPE,
1342 benefit_qty QP_LIST_LINES.benefit_qty%TYPE,
1343 benefit_uom_code QP_LIST_LINES.benefit_uom_code%TYPE,
1344 charge_type_code QP_LIST_LINES.charge_type_code%TYPE,
1345 charge_subtype_code QP_LIST_LINES.charge_subtype_code%TYPE,
1346 benefit_limit QP_LIST_LINES.benefit_limit%TYPE,
1347 include_on_returns_flag QP_LIST_LINES.include_on_returns_flag%TYPE,
1348 qualification_ind QP_LIST_LINES.qualification_ind%TYPE,
1349 recurring_value QP_LIST_LINES.recurring_value%TYPE, -- block pricing
1350 continuous_price_break_flag QP_LIST_LINES.continuous_price_break_flag%TYPE
1351 --Continuous Price Breaks
1352 );
1353
1354 l_non_cont_pbh_id_tbl QP_UTIL.price_brk_attr_val_tab; --Continuous Price Breaks
1355 l_non_cont_count NUMBER := 0; --Continuous Price Breaks
1356 l_return_status VARCHAR2(1);
1357
1358 l_mapping_tbl mapping_tbl;
1359
1360 l_select_stmt VARCHAR2(9000);
1361 l_qp_list_lines_rec qp_list_lines_rec;
1362 l_context VARCHAR2(30);
1363 l_attribute VARCHAR2(30);
1364
1365 l_exists NUMBER := 0;
1366 l_count NUMBER := 0;
1367 l_new_from_id NUMBER;
1368 l_new_to_id NUMBER;
1369
1370 l_pric_attr_value_from_number NUMBER := NULL;
1371 l_pric_attr_value_to_number NUMBER := NULL;
1372
1373 l_list_type_code VARCHAR2(30) := '';
1374 v_list_type_code VARCHAR2(30) := '';
1375 v_pte_code VARCHAR2(30) := '';
1376 v_source_system_code VARCHAR2(30) := '';
1377 l_active_flag VARCHAR2(1) := '';
1378 l_qual_attr_value_from_number NUMBER := NULL;
1379 l_qual_attr_value_to_number NUMBER := NULL;
1380
1381 TYPE lines_cur_typ IS REF CURSOR;
1382 qp_list_lines_cv lines_cur_typ;
1383
1384 CURSOR qp_pricing_attributes_cur(p_from_list_line_id NUMBER)
1385 IS
1386 SELECT *
1387 FROM qp_pricing_attributes
1388 WHERE list_line_id = p_from_list_line_id;
1389
1390 /* First part of cursor qp_qualifiers_cur selects qualifiers while the second part
1391 selects secondary price list */
1392
1393 CURSOR qp_qualifiers_cur(p_from_list_header_id NUMBER, p_context VARCHAR2,
1394 p_attribute VARCHAR2, p_discount_flag VARCHAR2)
1395 IS
1396 SELECT *
1397 FROM qp_qualifiers q
1398 WHERE (q.list_header_id = p_from_list_header_id AND
1399 q.qualifier_attribute <> p_attribute AND --Added for 2200425
1400 Exists (Select Null
1401 --fix for bug 4673872
1402 From qp_list_headers_all_b a
1403 Where a.list_header_id = p_from_list_header_id
1404 And a.list_type_code = 'PRL'
1405 )
1406 )
1407 OR
1408 (q.qualifier_context = p_context AND
1409 q.qualifier_attribute = p_attribute AND
1410 q.qualifier_attr_value = TO_CHAR(p_from_list_header_id) AND
1411 --fix for bug 4673872
1412 EXISTS (select null from qp_list_headers_all_b a --Added for 2200425
1413 where a.list_header_id =q.list_header_id
1414 And a.list_type_code = 'PRL')
1415 /* and --Commented out for 2200425
1416 Exists (Select Null
1417 From qp_list_headers_b a
1418 Where a.list_header_id = p_from_list_header_id
1419 And a.list_type_code = 'PRL'
1420 )*/
1421 );
1422
1423 CURSOR qp_rltd_modifiers_cur(a_list_line_id NUMBER)
1424 IS
1425 SELECT *
1426 FROM qp_rltd_modifiers
1427 WHERE from_rltd_modifier_id = a_list_line_id;
1428
1429 BEGIN
1430
1431 IF MO_GLOBAL.get_access_mode is null THEN
1432 MO_GLOBAL.Init('QP');
1433 END IF;--MO_GLOBAL
1434
1435 l_conc_request_id := FND_GLOBAL.CONC_REQUEST_ID;
1436 l_conc_program_id := FND_GLOBAL.CONC_PROGRAM_ID;
1437 l_user_id := FND_GLOBAL.USER_ID;
1438 l_conc_login_id := FND_GLOBAL.CONC_LOGIN_ID;
1439 l_conc_program_application_id := FND_GLOBAL.PROG_APPL_ID;
1440
1441 -- Standard call to check for API compatibility
1442
1443 /*IF NOT FND_API.Compatible_API_Call( l_api_version_number,
1444 p_api_version_number,
1445 l_api_name,
1446 G_PKG_NAME
1447 )
1448 THEN
1449 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1450 END IF;
1451 */
1452
1453 -- Initialize message list if p_init_msg_list is set to TRUE;
1454
1455 /*IF FND_API.to_Boolean(p_init_msg_list) THEN
1456 FND_MSG_PUB.initialize;
1457 END IF;*/
1458
1459 -- Initialize x_return_status
1460
1461 /*x_return_status := FND_API.G_RET_STS_SUCCESS;*/
1462
1463 --Check if a pricelist with requested new price list name already exists
1464
1465 SELECT COUNT(*) INTO l_exists
1466 --fix for bug 4673872
1467 FROM qp_secu_list_headers_vl plh
1468 /* WHERE plh.list_type_code = 'PRL' */
1469 /* Commented the above line for bug 1343801 */
1470 WHERE plh.list_type_code in ('PRL' , 'AGR')
1471 AND plh.name = p_new_price_list_name;
1472
1473 IF (l_exists > 0) THEN
1474 -- Error Message that a Price List with the name already exists
1475 NULL;
1476 END IF;
1477
1478
1479 /** Following code inserts price list header information **/
1480
1481 -- Get next list_header_id
1482
1483 SELECT qp_list_headers_b_s.nextval
1484 INTO l_new_list_header_id
1485 FROM dual;
1486
1487 -- Insert Price List Header information
1488
1489 INSERT INTO qp_list_headers_all_b
1490 (
1491 list_header_id,
1492 creation_date,
1493 created_by,
1494 last_update_date,
1495 last_updated_by,
1496 last_update_login,
1497 program_application_id,
1498 program_id,
1499 program_update_date,
1500 request_id,
1501 list_type_code,
1502 start_date_active,
1503 end_date_active,
1504 automatic_flag,
1505 -- exclusive_flag,
1506 currency_code,
1507 rounding_factor,
1508 ship_method_code,
1509 freight_terms_code,
1510 terms_id,
1511 context,
1512 attribute1,
1513 attribute2,
1514 attribute3,
1515 attribute4,
1516 attribute5,
1517 attribute6,
1518 attribute7,
1519 attribute8,
1520 attribute9,
1521 attribute10,
1522 attribute11,
1523 attribute12,
1524 attribute13,
1525 attribute14,
1526 attribute15,
1527 comments,
1528 discount_lines_flag,
1529 gsa_indicator,
1530 prorate_flag,
1531 source_system_code,
1532 active_flag,
1533 parent_list_header_id,
1534 start_date_active_first,
1535 end_date_active_first,
1536 active_date_first_type,
1537 start_date_active_second,
1538 end_date_active_second,
1539 active_date_second_type,
1540 ask_for_flag,
1541 currency_header_id, -- Multi-Currency SunilPandey
1542 pte_code, -- Attribute Manager, Giri
1543 global_flag, -- sfiresto 2853767
1544 orig_org_id -- sfiresto 2853767
1545 --ENH Upgrade BOAPI for orig_sys...ref RAVI
1546 ,ORIG_SYSTEM_HEADER_REF
1547 )
1548
1549 SELECT
1550 l_new_list_header_id,
1551 sysdate,
1552 l_user_id,
1553 sysdate,
1554 l_user_id,
1555 l_conc_login_id,
1556 l_conc_program_application_id,
1557 l_conc_program_id,
1558 sysdate,
1559 l_conc_request_id,
1560 list_type_code, /* can be changed to 'PRL' but is implicit */
1561 fnd_date.canonical_to_date(p_start_date_active), --2735911
1562 fnd_date.canonical_to_date(p_end_date_active), --2735911
1563 automatic_flag,
1564 -- exclusive_flag,
1565 currency_code,
1566 rounding_factor,
1567 ship_method_code,
1568 freight_terms_code,
1569 terms_id,
1570 context,
1571 attribute1,
1572 attribute2,
1573 attribute3,
1574 attribute4,
1575 attribute5,
1576 attribute6,
1577 attribute7,
1578 attribute8,
1579 attribute9,
1580 attribute10,
1581 attribute11,
1582 attribute12,
1583 attribute13,
1584 attribute14,
1585 attribute15,
1586 comments,
1587 discount_lines_flag,
1588 gsa_indicator,
1589 prorate_flag,
1590 source_system_code,
1591 active_flag,
1592 parent_list_header_id,
1593 start_date_active_first,
1594 end_date_active_first,
1595 active_date_first_type,
1596 start_date_active_second,
1597 end_date_active_second,
1598 active_date_second_type,
1599 ask_for_flag,
1600 currency_header_id, -- Multi-Currency SunilPandey
1601 pte_code, -- Attribute Manager, Giri
1602 p_global_flag, -- sfiresto 2853767
1603 p_org_id -- sfiresto 2853767
1604 --ENH Upgrade BOAPI for orig_sys...ref RAVI
1605 --,nvl(ORIG_SYSTEM_HEADER_REF,to_char(l_new_list_header_id))
1606 -- Bug 5201918
1607 ,to_char(list_header_id) --7309992
1608
1609 --fix for bug 4673872
1610 FROM qp_list_headers_all_b
1611 WHERE list_header_id = p_from_list_header_id;
1612
1613 -- Object Security - sfiresto
1614
1615 SELECT list_type_code,pte_code,source_system_code
1616 INTO v_list_type_code,v_pte_code,v_source_system_code
1617 --fix for bug 4673872
1618 FROM qp_list_headers_all_b
1619 WHERE list_header_id = p_from_list_header_id;
1620
1621 IF v_list_type_code = 'AGR' THEN
1622 QP_security.create_default_grants( p_instance_type => QP_security.G_AGREEMENT_OBJECT,
1623 p_instance_pk1 => l_new_list_header_id,
1624 x_return_status => x_result);
1625
1626 ELSE
1627 QP_security.create_default_grants( p_instance_type => QP_security.G_PRICELIST_OBJECT,
1628 p_instance_pk1 => l_new_list_header_id,
1629 x_return_status => x_result);
1630 END IF;
1631 -- End addition for Object Security
1632
1633
1634
1635 INSERT INTO qp_list_headers_tl
1636 (last_update_login,
1637 name,
1638 description,
1639 creation_date,
1640 created_by,
1641 last_update_date,
1642 last_updated_by,
1643 list_header_id,
1644 language,
1645 source_lang,
1646 version_no
1647 )
1648 SELECT
1649 l_conc_login_id,
1650 p_new_price_list_name,
1651 p_description,
1652 sysdate,
1653 l_user_id,
1654 sysdate,
1655 l_user_id,
1656 l_new_list_header_id,
1657 l.language_code,
1658 userenv('LANG'),
1659 '1'
1660
1661 FROM fnd_languages l
1662 WHERE l.installed_flag IN ('I', 'B')
1663 AND NOT EXISTS (SELECT NULL
1664 FROM qp_list_headers_tl t
1665 WHERE t.list_header_id = l_new_list_header_id
1666 AND t.language = l.language_code);
1667
1668
1669 /* Copy all qualifiers having a code of PRICE_LIST_ID and price_list_id =
1670 from_list_header_id. This will copy secondary list as well as
1671 Self-Qualifier for the 'from' price list */
1672
1673 --Added the IF condition below for Attrmrg_Installed = 'Y' for bug 2434362.
1674 IF QP_UTIL.Attrmgr_Installed = 'Y' THEN
1675 QP_UTIL.Get_Context_Attribute('PRICE_LIST', l_context, l_attribute);
1676 ELSE
1677 QP_UTIL.Get_Context_Attribute('PRICE_LIST_ID', l_context, l_attribute);
1678 END IF;
1679
1680 FOR l_qp_qualifiers_rec IN qp_qualifiers_cur(p_from_list_header_id, l_context,
1681 l_attribute, p_discount_flag)
1682 LOOP
1683
1684 --Get new qualifier_id
1685 SELECT qp_qualifiers_s.nextval
1686 INTO l_new_qualifier_id
1687 FROM dual;
1688
1689 --To associate newly copied qualifiers (including secondary pricelists)
1690 --to the new price list
1691 IF p_from_list_header_id = l_qp_qualifiers_rec.list_header_id THEN
1692 l_qp_qualifiers_rec.list_header_id := l_new_list_header_id;
1693 END IF;
1694
1695 --If From PriceList is Self-Qualifier
1696 IF l_qp_qualifiers_rec.qualifier_context = l_context AND
1697 l_qp_qualifiers_rec.qualifier_attribute = l_attribute AND
1698 l_qp_qualifiers_rec.qualifier_attr_value = TO_CHAR(p_from_list_header_id)
1699 THEN
1700 l_qp_qualifiers_rec.qualifier_attr_value := TO_CHAR(l_new_list_header_id);
1701 END IF;
1702
1703 BEGIN
1704
1705 SELECT ACTIVE_FLAG, LIST_TYPE_CODE
1706 INTO l_active_flag, l_list_type_code
1707 --fix for bug 4673872
1708 FROM QP_LIST_HEADERS_ALL_B
1709 WHERE LIST_HEADER_ID = l_qp_qualifiers_rec.list_header_id;
1710
1711 EXCEPTION
1712 WHEN OTHERS THEN
1713 NULL;
1714 END;
1715
1716 IF l_qp_qualifiers_rec.qualifier_datatype = 'N'
1717 then
1718
1719 BEGIN
1720
1721 l_qual_attr_value_from_number :=
1722 qp_number.canonical_to_number(l_qp_qualifiers_rec.qualifier_attr_value);
1723
1724 l_qual_attr_value_to_number :=
1725 qp_number.canonical_to_number(l_qp_qualifiers_rec.qualifier_attr_value_to);
1726
1727 EXCEPTION
1728 WHEN VALUE_ERROR THEN
1729 NULL;
1730 WHEN OTHERS THEN
1731 NULL;
1732 END;
1733
1734 end if;
1735
1736 --Insert into qp_qualifiers
1737 INSERT INTO qp_qualifiers
1738 (
1739 qualifier_id,
1740 creation_date,
1741 created_by,
1742 last_update_date,
1743 last_updated_by,
1744 last_update_login,
1745 program_application_id,
1746 program_id,
1747 program_update_date,
1748 request_id,
1749 excluder_flag,
1750 comparison_operator_code,
1751 qualifier_context,
1752 qualifier_attribute,
1753 context,
1754 attribute1,
1755 attribute2,
1756 attribute3,
1757 attribute4,
1758 attribute5,
1759 attribute6,
1760 attribute7,
1761 attribute8,
1762 attribute9,
1763 attribute10,
1764 attribute11,
1765 attribute12,
1766 attribute13,
1767 attribute14,
1768 attribute15,
1769 qualifier_rule_id,
1770 qualifier_grouping_no,
1771 qualifier_attr_value,
1772 list_header_id,
1773 list_line_id,
1774 created_from_rule_id,
1775 start_date_active,
1776 end_date_active,
1777 qualifier_precedence,
1778 qualifier_datatype,
1779 qualifier_attr_value_to,
1780 active_flag,
1781 list_type_code,
1782 qual_attr_value_from_number,
1783 qual_attr_value_to_number,
1784 search_ind,
1785 distinct_row_count,
1786 qualifier_group_cnt,
1787 header_quals_exist_flag,
1788 qualify_hier_descendents_flag -- Added for TCA
1789 --ENH Upgrade BOAPI for orig_sys...ref RAVI
1790 ,ORIG_SYS_QUALIFIER_REF
1791 ,ORIG_SYS_LINE_REF
1792 ,ORIG_SYS_HEADER_REF
1793 )
1794 VALUES
1795 (
1796 l_new_qualifier_id,
1797 sysdate,
1798 l_user_id,
1799 sysdate,
1800 l_user_id,
1801 l_conc_login_id,
1802 l_conc_program_application_id,
1803 l_conc_program_id,
1804 sysdate,
1805 l_conc_request_id,
1806 l_qp_qualifiers_rec.excluder_flag,
1807 l_qp_qualifiers_rec.comparison_operator_code,
1808 l_qp_qualifiers_rec.qualifier_context,
1809 l_qp_qualifiers_rec.qualifier_attribute,
1810 l_qp_qualifiers_rec.context,
1811 l_qp_qualifiers_rec.attribute1,
1812 l_qp_qualifiers_rec.attribute2,
1813 l_qp_qualifiers_rec.attribute3,
1814 l_qp_qualifiers_rec.attribute4,
1815 l_qp_qualifiers_rec.attribute5,
1816 l_qp_qualifiers_rec.attribute6,
1817 l_qp_qualifiers_rec.attribute7,
1818 l_qp_qualifiers_rec.attribute8,
1819 l_qp_qualifiers_rec.attribute9,
1820 l_qp_qualifiers_rec.attribute10,
1821 l_qp_qualifiers_rec.attribute11,
1822 l_qp_qualifiers_rec.attribute12,
1823 l_qp_qualifiers_rec.attribute13,
1824 l_qp_qualifiers_rec.attribute14,
1825 l_qp_qualifiers_rec.attribute15,
1826 l_qp_qualifiers_rec.qualifier_rule_id,
1827 l_qp_qualifiers_rec.qualifier_grouping_no,
1828 l_qp_qualifiers_rec.qualifier_attr_value,
1829 l_qp_qualifiers_rec.list_header_id,
1830 l_qp_qualifiers_rec.list_line_id,
1831 l_qp_qualifiers_rec.created_from_rule_id,
1832 l_qp_qualifiers_rec.start_date_active,
1833 l_qp_qualifiers_rec.end_date_active,
1834 l_qp_qualifiers_rec.qualifier_precedence,
1835 l_qp_qualifiers_rec.qualifier_datatype,
1836 l_qp_qualifiers_rec.qualifier_attr_value_to,
1837 l_active_flag,
1838 l_list_type_code,
1839 l_qual_attr_value_from_number,
1840 l_qual_attr_value_to_number,
1841 l_qp_qualifiers_rec.search_ind,
1842 l_qp_qualifiers_rec.distinct_row_count,
1843 l_qp_qualifiers_rec.qualifier_group_cnt,
1844 l_qp_qualifiers_rec.header_quals_exist_flag,
1845 l_qp_qualifiers_rec.qualify_hier_descendents_flag -- Added for TCA
1846 --ENH Upgrade BOAPI for orig_sys...ref RAVI
1847 ,to_char(l_new_qualifier_id)
1848 ,(select l.ORIG_SYS_LINE_REF from qp_list_lines l where l.list_line_id=l_qp_qualifiers_rec.list_line_id)
1849 ,(select h.ORIG_SYSTEM_HEADER_REF from qp_list_headers_b h where h.list_header_id=l_qp_qualifiers_rec.list_header_id)
1850 );
1851
1852
1853 END LOOP;
1854
1855 /* If p_discount_flag = 'Y' then Copy Discounts */
1856
1857 IF p_discount_flag = 'Y' THEN
1858
1859 Copy_Discounts(p_from_list_header_id, l_new_list_header_id,
1860 l_context, l_attribute, l_user_id,
1861 l_conc_login_id, l_conc_program_application_id,
1862 l_conc_program_id, l_conc_request_id);
1863
1864 END IF;
1865
1866
1867 /** Following code inserts price list lines information **/
1868
1869 --copy only price list lines
1870
1871 /* Need to copy only those price list lines whose end-date is not less than
1872 sysdate */
1873
1874 l_select_stmt :=
1875 'SELECT
1876 q.list_line_id,
1877 q.creation_date,
1878 q.created_by,
1879 q.last_update_date,
1880 q.last_updated_by,
1881 q.last_update_login,
1882 q.program_application_id,
1883 q.program_id,
1884 q.program_update_date,
1885 q.request_id,
1886 q.list_header_id,
1887 q.list_line_type_code,
1888 q.automatic_flag,
1889 q.modifier_level_code,
1890 q.list_price,
1891 q.primary_uom_flag,
1892 q.inventory_item_id,
1893 q.organization_id,
1894 q.related_item_id,
1895 q.relationship_type_id,
1896 q.substitution_context,
1897 q.substitution_attribute,
1898 q.substitution_value,
1899 q.revision,
1900 q.revision_date,
1901 q.revision_reason_code,
1902 q.context,
1903 q.attribute1,
1904 q.attribute2,
1905 q.comments,
1906 q.attribute3,
1907 q.attribute4,
1908 q.attribute5,
1909 q.attribute6,
1910 q.attribute7,
1911 q.attribute8,
1912 q.attribute9,
1913 q.attribute10,
1914 q.attribute11,
1915 q.attribute12,
1916 q.attribute13,
1917 q.attribute14,
1918 q.attribute15,
1919 q.price_break_type_code,
1920 q.percent_price,
1921 q.price_by_formula_id,
1922 q.number_effective_periods,
1923 q.effective_period_uom,
1924 q.arithmetic_operator,
1925 q.operand,
1926 q.override_flag,
1927 q.print_on_invoice_flag,
1928 q.rebate_transaction_type_code,
1929 q.estim_accrual_rate,
1930 q.generate_using_formula_id,
1931 q.start_date_active,
1932 q.end_date_active,
1933 q.reprice_flag,
1934 q.accrual_flag,
1935 q.pricing_group_sequence,
1936 q.incompatibility_grp_code,
1937 q.list_line_no,
1938 q.product_precedence,
1939 q.pricing_phase_id,
1940 q.expiration_period_start_date,
1941 q.number_expiration_periods,
1942 q.expiration_period_uom,
1943 q.expiration_date,
1944 q.estim_gl_value,
1945 q.accrual_conversion_rate,
1946 q.benefit_price_list_line_id,
1947 q.proration_type_code,
1948 q.benefit_qty,
1949 q.benefit_uom_code,
1950 q.charge_type_code,
1951 q.charge_subtype_code,
1952 q.benefit_limit,
1953 q.include_on_returns_flag,
1954 q.qualification_ind,
1955 q.recurring_value, -- block pricing
1956 q.continuous_price_break_flag --Continuous Price Breaks
1957
1958 FROM qp_list_lines q
1959 WHERE q.list_header_id = :frm
1960 AND (q.end_date_active IS NULL OR trunc(q.end_date_active) >= trunc(sysdate)) --Modified by dhgupta for 2100785
1961 AND q.list_line_id IN
1962 (SELECT DISTINCT a.list_line_id
1963 FROM qp_pricing_attributes a
1964 WHERE a.list_line_id = q.list_line_id ';
1965
1966 IF (nvl(p_category_id, 0) <> 0 OR nvl(p_category_set_id, 0) <> 0) -- bug 4127037
1967 OR (p_segment1_lohi <> ''''' AND ''''') OR (p_segment2_lohi <> ''''' AND ''''')
1968 OR (p_segment3_lohi <> ''''' AND ''''') OR (p_segment4_lohi <> ''''' AND ''''')
1969 OR (p_segment5_lohi <> ''''' AND ''''') OR (p_segment6_lohi <> ''''' AND ''''')
1970 OR (p_segment7_lohi <> ''''' AND ''''') OR (p_segment8_lohi <> ''''' AND ''''')
1971 OR (p_segment9_lohi <> ''''' AND ''''') OR (p_segment10_lohi <> ''''' AND ''''')
1972 OR (p_segment11_lohi <> ''''' AND ''''')
1973 OR (p_segment12_lohi <> ''''' AND ''''')
1974 OR (p_segment13_lohi <> ''''' AND ''''')
1975 OR (p_segment14_lohi <> ''''' AND ''''')
1976 OR (p_segment15_lohi <> ''''' AND ''''')
1977 OR (p_segment16_lohi <> ''''' AND ''''')
1978 OR (p_segment17_lohi <> ''''' AND ''''')
1979 OR (p_segment18_lohi <> ''''' AND ''''')
1980 OR (p_segment19_lohi <> ''''' AND ''''')
1981 OR (p_segment20_lohi <> ''''' AND ''''') THEN
1982 /* Commented the following statement and replaced it with a new statement to fix the bug 1586265 */
1983 /*
1984 l_select_stmt := l_select_stmt ||
1985 'AND TO_NUMBER(a.product_attr_value) IN
1986 (SELECT inventory_item_id
1987 FROM mtl_system_items m
1988 WHERE (m.inventory_item_id = TO_NUMBER(a.product_attr_value)) ';
1989 */
1990
1991 l_select_stmt := l_select_stmt ||
1992 'AND a.product_attribute_context = ''ITEM''
1993 AND a.product_attribute = ''PRICING_ATTRIBUTE1''
1994 AND EXISTS
1995 (SELECT ''X''
1996 FROM mtl_system_items m
1997 WHERE (m.inventory_item_id = TO_NUMBER(a.product_attr_value)) ';
1998
1999
2000 IF (p_segment1_lohi <> ''''' AND ''''') THEN
2001 l_select_stmt := l_select_stmt ||
2002 'AND (m.segment1 BETWEEN ' || p_segment1_lohi || ') ';
2003 END IF;
2004
2005 IF (p_segment2_lohi <> ''''' AND ''''') THEN
2006 l_select_stmt := l_select_stmt ||
2007 'AND (m.segment2 BETWEEN ' || p_segment2_lohi || ') ';
2008 END IF;
2009
2010 IF (p_segment3_lohi <> ''''' AND ''''') THEN
2011 l_select_stmt := l_select_stmt ||
2012 'AND (m.segment3 BETWEEN ' || p_segment3_lohi || ') ';
2013 END IF;
2014
2015 IF (p_segment4_lohi <> ''''' AND ''''') THEN
2016 l_select_stmt := l_select_stmt ||
2017 'AND (m.segment4 BETWEEN ' || p_segment4_lohi || ') ';
2018 END IF;
2019
2020 IF (p_segment5_lohi <> ''''' AND ''''') THEN
2021 l_select_stmt := l_select_stmt ||
2022 'AND (m.segment5 BETWEEN ' || p_segment5_lohi || ') ';
2023 END IF;
2024
2025 IF (p_segment6_lohi <> ''''' AND ''''') THEN
2026 l_select_stmt := l_select_stmt ||
2027 'AND (m.segment6 BETWEEN ' || p_segment6_lohi || ') ';
2028 END IF;
2029
2030 IF (p_segment7_lohi <> ''''' AND ''''') THEN
2031 l_select_stmt := l_select_stmt ||
2032 'AND (m.segment7 BETWEEN ' || p_segment7_lohi || ') ';
2033 END IF;
2034
2035 IF (p_segment8_lohi <> ''''' AND ''''') THEN
2036 l_select_stmt := l_select_stmt ||
2037 'AND (m.segment8 BETWEEN ' || p_segment8_lohi || ') ';
2038 END IF;
2039
2040 IF (p_segment9_lohi <> ''''' AND ''''') THEN
2041 l_select_stmt := l_select_stmt ||
2042 'AND (m.segment9 BETWEEN ' || p_segment9_lohi || ') ';
2043 END IF;
2044
2045 IF (p_segment10_lohi <> ''''' AND ''''') THEN
2046 l_select_stmt := l_select_stmt ||
2047 'AND (m.segment10 BETWEEN ' || p_segment10_lohi || ') ';
2048 END IF;
2049
2050 IF (p_segment11_lohi <> ''''' AND ''''') THEN
2051 l_select_stmt := l_select_stmt ||
2052 'AND (m.segment10 BETWEEN ' || p_segment11_lohi || ') ';
2053 END IF;
2054
2055 IF (p_segment12_lohi <> ''''' AND ''''') THEN
2056 l_select_stmt := l_select_stmt ||
2057 'AND (m.segment12 BETWEEN ' || p_segment12_lohi || ') ';
2058 END IF;
2059
2060 IF (p_segment13_lohi <> ''''' AND ''''') THEN
2061 l_select_stmt := l_select_stmt ||
2062 'AND (m.segment13 BETWEEN ' || p_segment13_lohi || ') ';
2063 END IF;
2064
2065 IF (p_segment14_lohi <> ''''' AND ''''') THEN
2066 l_select_stmt := l_select_stmt ||
2067 'AND (m.segment14 BETWEEN ' || p_segment14_lohi || ') ';
2068 END IF;
2069
2070 IF (p_segment15_lohi <> ''''' AND ''''') THEN
2071 l_select_stmt := l_select_stmt ||
2072 'AND (m.segment15 BETWEEN ' || p_segment15_lohi || ') ';
2073 END IF;
2074
2075 IF (p_segment16_lohi <> ''''' AND ''''') THEN
2076 l_select_stmt := l_select_stmt ||
2077 'AND (m.segment16 BETWEEN ' || p_segment16_lohi || ') ';
2078 END IF;
2079
2080 IF (p_segment17_lohi <> ''''' AND ''''') THEN
2081 l_select_stmt := l_select_stmt ||
2082 'AND (m.segment17 BETWEEN ' || p_segment17_lohi || ') ';
2083 END IF;
2084
2085 IF (p_segment18_lohi <> ''''' AND ''''') THEN
2086 l_select_stmt := l_select_stmt ||
2087 'AND (m.segment18 BETWEEN ' || p_segment18_lohi || ') ';
2088 END IF;
2089
2090 IF (p_segment19_lohi <> ''''' AND ''''') THEN
2091 l_select_stmt := l_select_stmt ||
2092 'AND (m.segment19 BETWEEN ' || p_segment19_lohi || ') ';
2093 END IF;
2094
2095 IF (p_segment20_lohi <> ''''' AND ''''') THEN
2096 l_select_stmt := l_select_stmt ||
2097 'AND (m.segment20 BETWEEN ' || p_segment20_lohi || ') ';
2098 END IF;
2099
2100 -- begin fix bug 4127037
2101 -- debug
2102 fnd_file.put_line(FND_FILE.LOG,'>>>>>>>>>>>>>>>>category_set_id: '||nvl(p_category_set_id, 0));
2103 fnd_file.put_line(FND_FILE.LOG,'>>>>>>>>>>>>>>>>category_id: '||nvl(p_category_id, 0));
2104
2105 IF ( nvl(p_category_id, 0) <> 0 AND nvl(p_category_set_id, 0) <> 0) THEN
2106 l_select_stmt := l_select_stmt ||
2107 'AND m.inventory_item_id IN
2108 ( SELECT ic.inventory_item_id
2109 FROM mtl_item_categories ic
2110 WHERE ic.inventory_item_id = m.inventory_item_id
2111 AND ic.organization_id = m.organization_id
2112 AND (ic.category_id = :category_id or ic.category_id in ( select parent_id
2113 FROM eni_denorm_hierarchies
2114 WHERE child_id = :category_id and
2115 organization_id = ic.organization_id and
2116 exists (select ''Y'' from QP_SOURCESYSTEM_FNAREA_MAP A, qp_pte_source_systems B ,
2117 mtl_default_category_sets c, mtl_category_sets d
2118 where A.PTE_SOURCE_SYSTEM_ID = B.PTE_SOURCE_SYSTEM_ID and
2119 B.PTE_CODE = :pte_code and
2120 B.APPLICATION_SHORT_NAME = :source_system_code and
2121 A.FUNCTIONAL_AREA_ID = c.FUNCTIONAL_AREA_ID and
2122 c.CATEGORY_SET_ID = d.CATEGORY_SET_ID and
2123 d.HIERARCHY_ENABLED = ''Y'' and
2124 A.ENABLED_FLAG = ''Y'' and B.ENABLED_FLAG = ''Y'')))
2125 AND ic.category_set_id = :category_set_id
2126 ) ';
2127 END IF;
2128 IF ( nvl(p_category_id, 0) = 0 AND nvl(p_category_set_id, 0) <> 0) THEN
2129 l_select_stmt := l_select_stmt ||
2130 'AND m.inventory_item_id IN
2131 ( SELECT ic.inventory_item_id
2132 FROM mtl_item_categories ic
2133 WHERE ic.inventory_item_id = m.inventory_item_id
2134 AND ic.organization_id = m.organization_id
2135 AND ic.category_set_id = :category_set_id
2136 ) ';
2137 END IF;
2138 -- end fix bug 4127037
2139
2140 l_select_stmt := l_select_stmt || ') )';
2141 ELSE
2142 l_select_stmt := l_select_stmt || ') ';
2143 END IF;
2144
2145 -- debug
2146 fnd_file.put_line(FND_FILE.LOG,'>>>>>>>>>>>>>>>>'||l_select_stmt);
2147
2148 IF (p_category_id is null) and (p_category_set_id is null) then
2149 OPEN qp_list_lines_cv FOR l_select_stmt USING p_from_list_header_id;
2150 end if;
2151 IF ( nvl(p_category_id, 0) <> 0 AND nvl(p_category_set_id, 0) <> 0) THEN
2152 OPEN qp_list_lines_cv FOR l_select_stmt USING p_from_list_header_id,p_category_id,p_category_id,v_pte_code,v_source_system_code,p_category_set_id;
2153 END IF;
2154 IF ( nvl(p_category_id, 0) = 0 AND nvl(p_category_set_id, 0) <> 0) THEN
2155 OPEN qp_list_lines_cv FOR l_select_stmt USING p_from_list_header_id,p_category_set_id;
2156 END IF;
2157 fnd_file.put_line(FND_FILE.LOG,'>>>>>>>>>>>>>>>>'||'opened list_lines cursor');
2158
2159 --This is the fetch loop
2160 LOOP
2161 fnd_file.put_line(FND_FILE.LOG,'>>>>>>>>>>>>>>>>'||'inside loop ');
2162
2163 --Insert each fetched record(Price List Line without Discount Line)
2164 --of the from_price_list into qp_list_lines(for the New Price List)
2165 --provided the segment values of the lines lie in the input ranges.
2166
2167 FETCH qp_list_lines_cv INTO l_qp_list_lines_rec;
2168 fnd_file.put_line(FND_FILE.LOG,'>>>>>>>>>>>>>>>>'||'after fetch ');
2169 fnd_file.put_line(FND_FILE.LOG,'>>>>>>>>>>>>>>>>'||'row count ' || qp_list_lines_cv%ROWCOUNT);
2170
2171 EXIT WHEN qp_list_lines_cv%NOTFOUND;
2172 fnd_file.put_line(FND_FILE.LOG,'>>>>>>>>>>>>>>>>'||'row count ' || qp_list_lines_cv%ROWCOUNT);
2173
2174
2175 /* Added for 3067774.When price listr line is end dated,its related lines e.g. child lines
2176 are still active. Cursor qp_list_lines_cv selects all active lines, therefore,
2177 orphaned child lines are also copied to new price list.The following logic
2178 excludes such orphaned lines from being copied. */
2179
2180
2181 insert_flag :='N';
2182 BEGIN
2183 select from_rltd_modifier_id into l_line_id
2184 from qp_rltd_modifiers
2185 where to_rltd_modifier_id=l_qp_list_lines_rec.list_line_id;
2186 Exception
2187 when no_data_found then
2188 l_line_id:=null;
2189 End;
2190
2191 If l_line_id is null then
2192 insert_flag :='Y';
2193 Else
2194 Begin
2195 select count(*) into l_cnt from qp_list_lines where list_line_id=l_line_id
2196 AND ((end_date_active IS NULL) OR (trunc(end_date_active) >= trunc(sysdate)));
2197 Exception
2198 when no_data_found then
2199 l_cnt:=0;
2200 End;
2201
2202 If l_cnt > 0 then
2203 insert_flag :='Y';
2204 End if;
2205 End if;
2206 fnd_file.put_line(FND_FILE.LOG,'>>>>>>>>>>>>>>>>'||'insert flag is ' || insert_flag);
2207
2208 If insert_flag ='Y' then /* end changes for bug3067774 */
2209
2210
2211
2212 -- Get next list_line_id
2213
2214 SELECT qp_list_lines_s.nextval
2215 INTO l_new_list_line_id
2216 FROM dual;
2217
2218 INSERT INTO qp_list_lines
2219 (
2220 list_line_id,
2221 creation_date,
2222 created_by,
2223 last_update_date,
2224 last_updated_by,
2225 last_update_login,
2226 program_application_id,
2227 program_id,
2228 program_update_date,
2229 request_id,
2230 list_header_id,
2231 list_line_type_code,
2232 start_date_active,
2233 end_date_active,
2234 automatic_flag,
2235 modifier_level_code,
2236 list_price,
2237 primary_uom_flag,
2238 inventory_item_id,
2239 organization_id,
2240 related_item_id,
2241 relationship_type_id,
2242 substitution_context,
2243 substitution_attribute,
2244 substitution_value,
2245 revision,
2246 revision_date,
2247 revision_reason_code,
2248 context,
2249 attribute1,
2250 attribute2,
2251 comments,
2252 attribute3,
2253 attribute4,
2254 attribute5,
2255 attribute6,
2256 attribute7,
2257 attribute8,
2258 attribute9,
2259 attribute10,
2260 attribute11,
2261 attribute12,
2262 attribute13,
2263 attribute14,
2264 attribute15,
2265 price_break_type_code,
2266 percent_price,
2267 price_by_formula_id,
2268 number_effective_periods,
2269 effective_period_uom,
2270 arithmetic_operator,
2271 operand,
2272 override_flag,
2273 print_on_invoice_flag,
2274 rebate_transaction_type_code,
2275 estim_accrual_rate,
2276 generate_using_formula_id,
2277 reprice_flag,
2278 accrual_flag,
2279 pricing_group_sequence,
2280 incompatibility_grp_code,
2281 list_line_no,
2282 product_precedence,
2283 pricing_phase_id,
2284 expiration_period_start_date,
2285 number_expiration_periods,
2286 expiration_period_uom,
2287 expiration_date,
2288 estim_gl_value,
2289 accrual_conversion_rate,
2290 benefit_price_list_line_id,
2291 proration_type_code,
2292 benefit_qty,
2293 benefit_uom_code,
2294 charge_type_code,
2295 charge_subtype_code,
2296 benefit_limit,
2297 include_on_returns_flag,
2298 qualification_ind,
2299 recurring_value, -- block pricing
2300 continuous_price_break_flag --Continuous Price Breaks
2301 --ENH Upgrade BOAPI for orig_sys...ref RAVI
2302 ,ORIG_SYS_LINE_REF
2303 ,ORIG_SYS_HEADER_REF
2304 )
2305 VALUES
2306 (
2307 l_new_list_line_id,
2308 sysdate,
2309 l_user_id,
2310 sysdate,
2311 l_user_id,
2312 l_conc_login_id,
2313 l_conc_program_application_id,
2314 l_conc_program_id,
2315 sysdate,
2316 l_conc_request_id,
2317 l_new_list_header_id,
2318 l_qp_list_lines_rec.list_line_type_code,
2319 DECODE (p_effective_dates_flag,
2320 'Y', l_qp_list_lines_rec.start_date_active,
2321 NULL), /* If flag='Y', retain start date from copied line */
2322 /* else default start date */
2323 DECODE (p_effective_dates_flag,
2324 'Y', l_qp_list_lines_rec.end_date_active,
2325 NULL), /* If flag='Y', retain end date from copied line */
2326 /* else default end date */
2327 l_qp_list_lines_rec.automatic_flag,
2328 l_qp_list_lines_rec.modifier_level_code,
2329 l_qp_list_lines_rec.list_price,
2330 l_qp_list_lines_rec.primary_uom_flag,
2331 l_qp_list_lines_rec.inventory_item_id,
2332 l_qp_list_lines_rec.organization_id,
2333 l_qp_list_lines_rec.related_item_id,
2334 l_qp_list_lines_rec.relationship_type_id,
2335 l_qp_list_lines_rec.substitution_context,
2336 l_qp_list_lines_rec.substitution_attribute,
2337 l_qp_list_lines_rec.substitution_value,
2338 l_qp_list_lines_rec.revision,
2339 l_qp_list_lines_rec.revision_date,
2340 l_qp_list_lines_rec.revision_reason_code,
2341 l_qp_list_lines_rec.context,
2342 l_qp_list_lines_rec.attribute1,
2343 l_qp_list_lines_rec.attribute2,
2344 l_qp_list_lines_rec.comments,
2345 l_qp_list_lines_rec.attribute3,
2346 l_qp_list_lines_rec.attribute4,
2347 l_qp_list_lines_rec.attribute5,
2348 l_qp_list_lines_rec.attribute6,
2349 l_qp_list_lines_rec.attribute7,
2350 l_qp_list_lines_rec.attribute8,
2351 l_qp_list_lines_rec.attribute9,
2352 l_qp_list_lines_rec.attribute10,
2353 l_qp_list_lines_rec.attribute11,
2354 l_qp_list_lines_rec.attribute12,
2355 l_qp_list_lines_rec.attribute13,
2356 l_qp_list_lines_rec.attribute14,
2357 l_qp_list_lines_rec.attribute15,
2358 l_qp_list_lines_rec.price_break_type_code,
2359 l_qp_list_lines_rec.percent_price,
2360 l_qp_list_lines_rec.price_by_formula_id,
2361 l_qp_list_lines_rec.number_effective_periods,
2362 l_qp_list_lines_rec.effective_period_uom,
2363 l_qp_list_lines_rec.arithmetic_operator,
2364 l_qp_list_lines_rec.operand,
2365 l_qp_list_lines_rec.override_flag,
2366 l_qp_list_lines_rec.print_on_invoice_flag,
2367 l_qp_list_lines_rec.rebate_transaction_type_code,
2368 l_qp_list_lines_rec.estim_accrual_rate,
2369 l_qp_list_lines_rec.generate_using_formula_id,
2370 l_qp_list_lines_rec.reprice_flag,
2371 l_qp_list_lines_rec.accrual_flag,
2372 l_qp_list_lines_rec.pricing_group_sequence,
2373 l_qp_list_lines_rec.incompatibility_grp_code,
2374 l_qp_list_lines_rec.list_line_no,
2375 l_qp_list_lines_rec.product_precedence,
2376 l_qp_list_lines_rec.pricing_phase_id,
2377 l_qp_list_lines_rec.expiration_period_start_date,
2378 l_qp_list_lines_rec.number_expiration_periods,
2379 l_qp_list_lines_rec.expiration_period_uom,
2380 l_qp_list_lines_rec.expiration_date,
2381 l_qp_list_lines_rec.estim_gl_value,
2382 l_qp_list_lines_rec.accrual_conversion_rate,
2383 l_qp_list_lines_rec.benefit_price_list_line_id,
2384 l_qp_list_lines_rec.proration_type_code,
2385 l_qp_list_lines_rec.benefit_qty,
2386 l_qp_list_lines_rec.benefit_uom_code,
2387 l_qp_list_lines_rec.charge_type_code,
2388 l_qp_list_lines_rec.charge_subtype_code,
2389 l_qp_list_lines_rec.benefit_limit,
2390 l_qp_list_lines_rec.include_on_returns_flag,
2391 l_qp_list_lines_rec.qualification_ind,
2392 l_qp_list_lines_rec.recurring_value, -- block pricing
2393 decode(l_qp_list_lines_rec.list_line_type_code,'PBH','Y',
2394 l_qp_list_lines_rec.continuous_price_break_flag) -- Continuous Price Breaks
2395 --ENH Upgrade BOAPI for orig_sys...ref RAVI
2396 ,to_char(l_qp_list_lines_rec.list_line_id) --7309992
2397 ,to_char(l_qp_list_lines_rec.list_header_id) --7309992
2398 --(select h.ORIG_SYSTEM_HEADER_REF from qp_list_headers_b h where h.list_header_id=l_new_list_header_id)
2399 );
2400 fnd_file.put_line(FND_FILE.LOG,'Inserted line');
2401 fnd_file.put_line(FND_FILE.LOG,'list_line_id ' || l_new_list_line_id);
2402 fnd_file.put_line(FND_FILE.LOG,'list_line_type_code ' || l_qp_list_lines_rec.list_line_type_code);
2403
2404
2405 /*If the list_line_rec is a Price Break Parent Line or Price Break Line then
2406 store the old and new list line id in a mapping-array for later use*/
2407
2408 IF l_qp_list_lines_rec.list_line_type_code = 'PBH' OR
2409 Price_Break_Line(l_qp_list_lines_rec.list_line_id)
2410 THEN
2411 l_count := l_count + 1;
2412 l_mapping_tbl(l_count).list_line_type_code :=
2413 l_qp_list_lines_rec.list_line_type_code;
2414 l_mapping_tbl(l_count).old_list_line_id :=
2415 l_qp_list_lines_rec.list_line_id;
2416 l_mapping_tbl(l_count).new_list_line_id := l_new_list_line_id;
2417 END IF;
2418
2419 IF l_qp_list_lines_rec.list_line_type_code = 'PBH' AND
2420 (l_qp_list_lines_rec.continuous_price_break_flag IS NULL OR l_qp_list_lines_rec.continuous_price_break_flag <> 'Y')
2421 THEN
2422 l_non_cont_count := l_non_cont_count + 1;
2423 l_non_cont_pbh_id_tbl(l_non_cont_count).price_break_header_id := l_new_list_line_id;
2424 l_non_cont_pbh_id_tbl(l_non_cont_count).list_line_no := l_qp_list_lines_rec.list_line_no;
2425
2426 IF p_effective_dates_flag = 'Y' THEN
2427 l_non_cont_pbh_id_tbl(l_non_cont_count).start_date_active := l_qp_list_lines_rec.start_date_active;
2428 l_non_cont_pbh_id_tbl(l_non_cont_count).end_date_active := l_qp_list_lines_rec.end_date_active;
2429 END IF;
2430
2431 END IF;
2432
2433
2434 /*Also copy the Pricing Attributes for the copied line of the
2435 from_price_list to the new pricelist and associate it with the
2436 new_list_line_id*/
2437
2438 /* Select qp_pricing_attributes records for the 'from' list_line_id */
2439 FOR l_qp_pricing_attributes_rec IN qp_pricing_attributes_cur (
2440 l_qp_list_lines_rec.list_line_id)
2441 LOOP
2442
2443 -- Get next pricing_attribute_id
2444 SELECT qp_pricing_attributes_s.nextval
2445 INTO l_new_pricing_attribute_id
2446 FROM dual;
2447
2448 IF l_qp_list_lines_rec.list_line_type_code = 'PBH' AND
2449 (l_qp_list_lines_rec.continuous_price_break_flag IS NULL OR l_qp_list_lines_rec.continuous_price_break_flag <> 'Y')
2450 AND l_qp_pricing_attributes_rec.list_line_id = l_qp_list_lines_rec.list_line_id
2451 THEN
2452 l_non_cont_pbh_id_tbl(l_non_cont_count).product_attribute :=
2453 l_qp_pricing_attributes_rec.product_attribute;
2454 l_non_cont_pbh_id_tbl(l_non_cont_count).product_attr_value:=
2455 l_qp_pricing_attributes_rec.product_attr_value;
2456 END IF;
2457
2458 IF l_qp_pricing_attributes_rec.pricing_attribute_datatype = 'N'
2459 then
2460
2461 BEGIN
2462
2463 l_pric_attr_value_from_number :=
2464 qp_number.canonical_to_number(l_qp_pricing_attributes_rec.pricing_attr_value_from);
2465
2466 l_pric_attr_value_to_number :=
2467 qp_number.canonical_to_number(l_qp_pricing_attributes_rec.pricing_attr_value_to);
2468
2469 EXCEPTION
2470 WHEN VALUE_ERROR THEN
2471 NULL;
2472 WHEN OTHERS THEN
2473 NULL;
2474 END;
2475
2476 end if;
2477
2478
2479 INSERT INTO qp_pricing_attributes
2480 (pricing_attribute_id,
2481 creation_date,
2482 created_by,
2483 last_update_date,
2484 last_updated_by,
2485 last_update_login,
2486 program_application_id,
2487 program_id,
2488 program_update_date,
2489 request_id,
2490 list_line_id,
2491 list_header_id,
2492 pricing_phase_id,
2493 qualification_ind,
2494 excluder_flag,
2495 accumulate_flag,
2496 product_attribute_context,
2497 product_attribute,
2498 product_attr_value,
2499 product_uom_code,
2500 pricing_attribute_context,
2501 pricing_attribute,
2502 pricing_attr_value_from,
2503 pricing_attr_value_to,
2504 attribute_grouping_no,
2505 context,
2506 attribute1,
2507 attribute2,
2508 attribute3,
2509 attribute4,
2510 attribute5,
2511 attribute6,
2512 attribute7,
2513 attribute8,
2514 attribute9,
2515 attribute10,
2516 attribute11,
2517 attribute12,
2518 attribute13,
2519 attribute14,
2520 attribute15,
2521 product_attribute_datatype,
2522 pricing_attribute_datatype,
2523 comparison_operator_code,
2524 pricing_attr_value_from_number,
2525 pricing_attr_value_to_number
2526 --ENH Upgrade BOAPI for orig_sys...ref RAVI
2527 ,ORIG_SYS_PRICING_ATTR_REF
2528 ,ORIG_SYS_LINE_REF
2529 ,ORIG_SYS_HEADER_REF
2530 )
2531 VALUES
2532 (l_new_pricing_attribute_id,
2533 sysdate,
2534 l_user_id,
2535 sysdate,
2536 l_user_id,
2537 l_conc_login_id,
2538 l_conc_program_application_id,
2539 l_conc_program_id,
2540 sysdate,
2541 l_conc_request_id,
2542 l_new_list_line_id, /* new list line id */
2543 l_new_list_header_id,
2544 l_qp_pricing_attributes_rec.pricing_phase_id,
2545 l_qp_pricing_attributes_rec.qualification_ind,
2546 l_qp_pricing_attributes_rec.excluder_flag,
2547 l_qp_pricing_attributes_rec.accumulate_flag,
2548 l_qp_pricing_attributes_rec.product_attribute_context,
2549 l_qp_pricing_attributes_rec.product_attribute,
2550 l_qp_pricing_attributes_rec.product_attr_value,
2551 l_qp_pricing_attributes_rec.product_uom_code,
2552 l_qp_pricing_attributes_rec.pricing_attribute_context,
2553 l_qp_pricing_attributes_rec.pricing_attribute,
2554 l_qp_pricing_attributes_rec.pricing_attr_value_from,
2555 l_qp_pricing_attributes_rec.pricing_attr_value_to,
2556 l_qp_pricing_attributes_rec.attribute_grouping_no,
2557 l_qp_pricing_attributes_rec.context,
2558 l_qp_pricing_attributes_rec.attribute1,
2559 l_qp_pricing_attributes_rec.attribute2,
2560 l_qp_pricing_attributes_rec.attribute3,
2561 l_qp_pricing_attributes_rec.attribute4,
2562 l_qp_pricing_attributes_rec.attribute5,
2563 l_qp_pricing_attributes_rec.attribute6,
2564 l_qp_pricing_attributes_rec.attribute7,
2565 l_qp_pricing_attributes_rec.attribute8,
2566 l_qp_pricing_attributes_rec.attribute9,
2567 l_qp_pricing_attributes_rec.attribute10,
2568 l_qp_pricing_attributes_rec.attribute11,
2569 l_qp_pricing_attributes_rec.attribute12,
2570 l_qp_pricing_attributes_rec.attribute13,
2571 l_qp_pricing_attributes_rec.attribute14,
2572 l_qp_pricing_attributes_rec.attribute15,
2573 l_qp_pricing_attributes_rec.product_attribute_datatype,
2574 l_qp_pricing_attributes_rec.pricing_attribute_datatype,
2575 l_qp_pricing_attributes_rec.comparison_operator_code,
2576 l_pric_attr_value_from_number,
2577 l_pric_attr_value_to_number
2578 --ENH Upgrade BOAPI for orig_sys...ref RAVI
2579 ,to_char(l_new_pricing_attribute_id)
2580 ,(select l.ORIG_SYS_LINE_REF from qp_list_lines l where l.list_line_id=l_new_list_line_id)
2581 ,(select h.ORIG_SYSTEM_HEADER_REF from qp_list_headers_b h where h.list_header_id=l_new_list_header_id)
2582 );
2583 fnd_file.put_line(FND_FILE.LOG,'Inserted pricing attribute');
2584 fnd_file.put_line(FND_FILE.LOG,'product_attribute_context ' || l_qp_pricing_attributes_rec.product_attribute_context);
2585 fnd_file.put_line(FND_FILE.LOG,'product_attribute ' || l_qp_pricing_attributes_rec.product_attribute);
2586 fnd_file.put_line(FND_FILE.LOG,'product_attr_value ' || l_qp_pricing_attributes_rec.product_attr_value);
2587 fnd_file.put_line(FND_FILE.LOG,'product_uom_code ' || l_qp_pricing_attributes_rec.product_uom_code);
2588
2589 END LOOP; /* Cursor qp_pricing_attributes_cur LOOP */
2590 end if; --bug3067774
2591 END LOOP; /* Cursor qp_list_lines_cv LOOP */
2592
2593 CLOSE qp_list_lines_cv;
2594
2595
2596 /* Copy qp_rltd_modifiers for the Price Break Parent list_lines chosen
2597 above which are stored in the mapping table */
2598 IF l_mapping_tbl.COUNT > 0 THEN
2599 FOR l_count IN 1..l_mapping_tbl.COUNT
2600 LOOP
2601
2602 IF l_mapping_tbl(l_count).list_line_type_code = 'PBH' THEN
2603
2604 FOR l_qp_rltd_modifiers_rec IN qp_rltd_modifiers_cur(
2605 l_mapping_tbl(l_count).old_list_line_id)
2606 LOOP
2607
2608 SELECT qp_rltd_modifiers_s.nextval
2609 INTO l_new_rltd_modifier_id
2610 FROM dual;
2611
2612 l_new_from_id := Get_New_Id(
2613 l_qp_rltd_modifiers_rec.from_rltd_modifier_id,
2614 l_mapping_tbl);
2615 l_new_to_id := Get_New_Id(
2616 l_qp_rltd_modifiers_rec.to_rltd_modifier_id,
2617 l_mapping_tbl);
2618 INSERT INTO qp_rltd_modifiers
2619 (creation_date,
2620 created_by,
2621 last_update_date,
2622 last_updated_by,
2623 last_update_login,
2624 context,
2625 attribute1,
2626 attribute2,
2627 attribute3,
2628 attribute4,
2629 attribute5,
2630 attribute6,
2631 attribute7,
2632 attribute8,
2633 attribute9,
2634 attribute10,
2635 attribute11,
2636 attribute12,
2637 attribute13,
2638 attribute14,
2639 attribute15,
2640 rltd_modifier_id,
2641 rltd_modifier_grp_no,
2642 from_rltd_modifier_id,
2643 to_rltd_modifier_id,
2644 rltd_modifier_grp_type
2645 )
2646 VALUES
2647 (sysdate,
2648 l_user_id,
2649 sysdate,
2650 l_user_id,
2651 l_conc_login_id,
2652 l_qp_rltd_modifiers_rec.context,
2653 l_qp_rltd_modifiers_rec.attribute1,
2654 l_qp_rltd_modifiers_rec.attribute2,
2655 l_qp_rltd_modifiers_rec.attribute3,
2656 l_qp_rltd_modifiers_rec.attribute4,
2657 l_qp_rltd_modifiers_rec.attribute5,
2658 l_qp_rltd_modifiers_rec.attribute6,
2659 l_qp_rltd_modifiers_rec.attribute7,
2660 l_qp_rltd_modifiers_rec.attribute8,
2661 l_qp_rltd_modifiers_rec.attribute9,
2662 l_qp_rltd_modifiers_rec.attribute10,
2663 l_qp_rltd_modifiers_rec.attribute11,
2664 l_qp_rltd_modifiers_rec.attribute12,
2665 l_qp_rltd_modifiers_rec.attribute13,
2666 l_qp_rltd_modifiers_rec.attribute14,
2667 l_qp_rltd_modifiers_rec.attribute15,
2668 l_new_rltd_modifier_id,
2669 l_qp_rltd_modifiers_rec.rltd_modifier_grp_no,
2670 l_new_from_id,
2671 l_new_to_id,
2672 l_qp_rltd_modifiers_rec.rltd_modifier_grp_type
2673 );
2674
2675 END LOOP; -- Loop through rltd modifiers records
2676 END IF; --For lines that are Parent Price Break lines
2677
2678 END LOOP; --Loop through l_mapping_tbl
2679 END IF; --If l_mapping_tbl has any records
2680
2681
2682 Delete_Duplicate_Lines(p_effective_dates_flag, l_new_list_header_id);
2683
2684 /* This code will call the API to update the denormalized columns on QP_QUALIFIERS*/
2685 QP_MAINTAIN_DENORMALIZED_DATA.UPDATE_QUALIFIERS
2686 (ERR_BUFF => errbuf,
2687 RETCODE => retcode,
2688 P_LIST_HEADER_ID => l_new_list_header_id);
2689
2690
2691 if retcode = 2 then
2692 --error from update denormalized columns
2693 fnd_file.put_line(FND_FILE.LOG,'Error in Update of denormalized columns in QP_Qualifiers');
2694 else
2695 fnd_file.put_line(FND_FILE.LOG,'Update of denormalized columns in QP_Qualifiers completed successfully');
2696 end if;
2697
2698
2699 --Upgrade Non-Continuous Price Breaks
2700
2701 IF l_non_cont_pbh_id_tbl.COUNT > 0 THEN
2702 fnd_file.put_line(FND_FILE.LOG,'New List Name : '||p_new_price_list_name);
2703
2704 FOR i IN l_non_cont_pbh_id_tbl.FIRST..l_non_cont_pbh_id_tbl.LAST
2705 LOOP
2706
2707 fnd_file.put_line(FND_FILE.LOG,'Upgrading non-continuous price breaks to continuous price breaks for the product : '||l_non_cont_pbh_id_tbl(i).product_attr_value);
2708
2709 qp_delayed_requests_PVT.log_request
2710 ( p_entity_code => QP_GLOBALS.G_ENTITY_PRICING_ATTR
2711 , p_entity_id => l_non_cont_pbh_id_tbl(i).price_break_header_id
2712 , p_requesting_entity_code => QP_GLOBALS.G_ENTITY_PRICING_ATTR
2713 , p_requesting_entity_id => l_non_cont_pbh_id_tbl(i).price_break_header_id
2714 , p_request_type => QP_Globals.G_UPGRADE_PRICE_BREAKS
2715 , p_param1 => l_non_cont_pbh_id_tbl(i).list_line_no
2716 , p_param2 => l_non_cont_pbh_id_tbl(i).product_attribute
2717 , p_param3 => l_non_cont_pbh_id_tbl(i).product_attr_value
2718 , p_param4 => 'PRICELIST'
2719 , p_param5 => l_non_cont_pbh_id_tbl(i).start_date_active
2720 , p_param6 => l_non_cont_pbh_id_tbl(i).end_date_active
2721 , x_return_status => l_return_status);
2722
2723 END LOOP;
2724 QP_DELAYED_REQUESTS_PVT.Process_Request_for_Entity
2725 ( p_entity_code => QP_GLOBALS.G_ENTITY_PRICING_ATTR
2726 , x_return_status => l_return_status);
2727
2728 END IF;
2729 commit;
2730
2731 fnd_file.put_line(FND_FILE.LOG,'Price list copy completed successfully');
2732 IF QP_JAVA_ENGINE_UTIL_PUB.Java_Engine_Installed = 'Y' THEN
2733 select min(list_line_id), max(list_line_id)
2734 into l_min_list_line_id, l_max_list_line_id
2735 from qp_list_lines
2736 where list_header_id = l_new_list_header_id;
2737
2738
2739 QP_ATTR_GRP_PVT.Update_Qual_Segment_id(l_new_list_header_id,
2740 null,
2741 l_min_list_line_id,
2742 l_max_list_line_id);
2743 QP_ATTR_GRP_PVT.Update_Prod_Pric_Segment_id(l_new_list_header_id,
2744 l_min_list_line_id,l_max_list_line_id);
2745 QP_ATTR_GRP_PVT.generate_hp_atgrps(l_new_list_header_id,null);
2746 QP_ATTR_GRP_PVT.update_pp_lines(l_new_list_header_id,
2747 l_min_list_line_id,l_max_list_line_id);
2748 END IF;
2749 errbuf := '';
2750 retcode := 0;
2751
2752 EXCEPTION
2753
2754 WHEN OTHERS THEN
2755
2756
2757 fnd_file.put_line(FND_FILE.LOG,'Error in Copy Price list Routine ');
2758 fnd_file.put_line(FND_FILE.LOG,substr(sqlerrm,1,300));
2759 retcode := 2;
2760
2761 END Copy_Price_List;
2762
2763 END QP_COPY_PRICELIST_PVT;