DBA Data[Home] [Help]

PACKAGE BODY: APPS.QP_COPY_PRICELIST_PVT

Source


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