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