[Home] [Help]
PACKAGE BODY: APPS.QP_COPY_MODIFIERS_PVT
Source
1 PACKAGE BODY QP_COPY_MODIFIERS_PVT AS
2 /* $Header: QPXVCPMB.pls 120.16.12020000.2 2012/09/04 12:50:14 kdurgasi ship $ */
3
4 -- GLOBAL Constant holding the package name
5
6 --G_PKG_NAME CONSTANT VARCHAR2(30):='QP_COPY_MODIFIERS_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 /* Commented out for 2222562 */
81 /*
82 CURSOR del_dup_cur (a_new_list_header_id NUMBER)
83 IS
84 SELECT *
85 FROM qp_list_lines a
86 WHERE EXISTS (SELECT NULL
87 FROM qp_list_lines b
88 WHERE a.inventory_item_id = b.inventory_item_id
89 AND a.list_line_type_code = b.list_line_type_code
90 AND a.list_header_id = b.list_header_id
91 AND a.list_header_id = a_new_list_header_id
92 AND a.list_line_id < b.list_line_id
93 AND nvl(a.automatic_flag,'x') = nvl(b.automatic_flag,'x')
94 AND nvl(a.modifier_level_code,'x') =
95 nvl(b.modifier_level_code,'x')
96 AND nvl(a.list_price,-1) = nvl(b.list_price,-1)
97 AND nvl(a.primary_uom_flag,'x') =
98 nvl(b.primary_uom_flag,'x')
99 AND nvl(a.organization_id,-1) = nvl(b.organization_id,-1)
100 AND nvl(a.related_item_id,-1) = nvl(b.related_item_id,-1)
101 AND nvl(a.relationship_type_id,-1) =
102 nvl(b.relationship_type_id,-1)
103 AND nvl(a.substitution_context,'x') =
104 nvl(b.substitution_context,'x')
105 AND nvl(a.substitution_attribute,'x') =
106 nvl(b.substitution_attribute,'x')
107 AND nvl(a.substitution_value,'x') =
108 nvl(b.substitution_value,'x')
109 AND nvl(a.context,'x') = nvl(b.context,'x')
110 AND nvl(a.attribute1,'x') = nvl(b.attribute1, 'x')
111 AND nvl(a.attribute2,'x') = nvl(b.attribute2, 'x')
112 AND nvl(a.comments,'x') = nvl(b.comments,'x')
113 AND nvl(a.attribute3,'x') = nvl(b.attribute3,'x')
114 AND nvl(a.attribute4,'x') = nvl(b.attribute4,'x')
115 AND nvl(a.attribute5,'x') = nvl(b.attribute5,'x')
116 AND nvl(a.attribute6,'x') = nvl(b.attribute6,'x')
117 AND nvl(a.attribute7,'x') = nvl(b.attribute7,'x')
118 AND nvl(a.attribute8,'x') = nvl(b.attribute8,'x')
119 AND nvl(a.attribute9,'x') = nvl(b.attribute9,'x')
120 AND nvl(a.attribute10,'x') = nvl(b.attribute10,'x')
121 AND nvl(a.attribute11,'x') = nvl(b.attribute11,'x')
122 AND nvl(a.attribute12,'x') = nvl(b.attribute12,'x')
123 AND nvl(a.attribute13,'x') = nvl(b.attribute13,'x')
124 AND nvl(a.attribute14,'x') = nvl(b.attribute14,'x')
125 AND nvl(a.attribute15,'x') = nvl(b.attribute15,'x')
126 AND nvl(a.price_break_type_code,'x') =
127 nvl(b.price_break_type_code,'x')
128 AND nvl(a.percent_price,-1) = nvl(b.percent_price,-1)
129 AND nvl(a.price_by_formula_id,-1) =
130 nvl(b.price_by_formula_id,-1)
131 AND nvl(a.number_effective_periods,-1) =
132 nvl(b.number_effective_periods,-1)
133 AND nvl(a.effective_period_uom,'x') =
134 nvl(b.effective_period_uom,'x')
135 AND nvl(a.arithmetic_operator,'x') =
136 nvl(b.arithmetic_operator,'x')
137 AND nvl(a.operand,-1) = nvl(b.operand,-1)
138 AND nvl(a.override_flag,'x') = nvl(b.override_flag,'x')
139 AND nvl(a.print_on_invoice_flag,'x') =
140 nvl(b.print_on_invoice_flag,'x')
141 AND nvl(a.rebate_transaction_type_code,'x') =
142 nvl(b.rebate_transaction_type_code,'x')
143 AND nvl(a.estim_accrual_rate,-1) =
144 nvl(b.estim_accrual_rate,-1)
145 AND nvl(a.generate_using_formula_id,-1) =
146 nvl(b.generate_using_formula_id,-1)
147 AND nvl(a.reprice_flag,'x') = nvl(b.reprice_flag,'x')
148 AND nvl(a.accrual_flag, 'x') = nvl(b.accrual_flag, 'x')
149 AND nvl(a.pricing_group_sequence, -1) =
150 nvl(b.pricing_group_sequence, -1)
151 AND nvl(a.incompatibility_grp_code, 'x') =
152 nvl(b.incompatibility_grp_code, 'x')
153 AND nvl(a.list_line_no, 'x') = nvl(b.list_line_no, 'x')
154 AND nvl(a.product_precedence, -1) =
155 nvl(b.product_precedence, -1)
156 AND nvl(a.pricing_phase_id, -1) = nvl(b.pricing_phase_id, -1)
157 AND nvl(a.number_expiration_periods, -1) =
158 nvl(b.number_expiration_periods, -1)
159 AND nvl(a.expiration_period_uom, 'x') =
160 nvl(b.expiration_period_uom, 'x')
161 AND nvl(a.estim_gl_value, -1) = nvl(b.estim_gl_value, -1)
162 AND nvl(a.accrual_conversion_rate, -1) =
163 nvl(b.accrual_conversion_rate, -1)
164 AND nvl(a.benefit_price_list_line_id, -1) =
165 nvl(b.benefit_price_list_line_id, -1)
166 AND nvl(a.proration_type_code, 'x') =
167 nvl(b.proration_type_code, 'x')
168 AND nvl(a.benefit_qty, -1) = nvl(b.benefit_qty, -1)
169 AND nvl(a.benefit_uom_code, 'x') = nvl(b.benefit_uom_code, 'x')
170 AND nvl(a.charge_type_code, 'x') = nvl(b.charge_type_code, 'x')
171 AND nvl(a.charge_subtype_code, 'x') =
172 nvl(b.charge_subtype_code, 'x')
173 AND nvl(a.benefit_limit, -1) = nvl(b.benefit_limit, -1)
174 AND nvl(a.include_on_returns_flag, 'x') =
175 nvl(b.include_on_returns_flag, 'x')
176 AND nvl(a.qualification_ind, -1) = nvl(b.qualification_ind, -1)
177 ) FOR UPDATE;
178 */
179 /* Added for 2222562 */
180
181 CURSOR del_dup_cur(a_new_list_header_id NUMBER)
182 IS
183 SELECT distinct qll.list_line_id,qpa.product_attribute_context,qpa.product_attribute,qpa.product_attr_value
184 FROM qp_list_lines qll,qp_pricing_attributes qpa
185 WHERE qll.list_header_id=a_new_list_header_id
186 AND qll.list_line_id=qpa.list_line_id(+);
187
188 l_status BOOLEAN := TRUE;
189 l_rows number := 0;
190 l_effdates boolean := FALSE;
191 l_qp_status VARCHAR2(1);
192 l_gsa_indicator VARCHAR2(1);
193
194 BEGIN
195
196 --If the Retain Effective Dates flag is not checked then copied price list
197 --lines will have null effective dates. This will mean that there is a
198 --possibility that lines may be duplicated. To prevent this, all but one
199 --duplicate lines are deleted here.
200
201 l_qp_status := QP_UTIL.GET_QP_STATUS;
202
203 --fix for bug 4673872
204 SELECT GSA_INDICATOR INTO l_gsa_indicator FROM QP_LIST_HEADERS_ALL_B
205 WHERE list_header_id=p_new_list_header_id;
206
207 IF (fnd_profile.value('QP_ALLOW_DUPLICATE_MODIFIERS') <> 'Y'
208 AND (l_qp_status = 'S' OR l_gsa_indicator = 'Y')) THEN
209
210 /* Modified for 2222562 */
211 IF p_effective_dates_flag = 'N' THEN
212
213 FOR l_del_dup_cur_rec IN del_dup_cur(p_new_list_header_id)
214 LOOP
215
216 l_status := QP_VALIDATE_PRICING_ATTR.Mod_Dup(NULL,
217 NULL,
218 l_del_dup_cur_rec.list_line_id,
219 p_new_list_header_id,
220 l_del_dup_cur_rec.product_attribute_context,
221 l_del_dup_cur_rec.product_attribute,
222 l_del_dup_cur_rec.product_attr_value,
223 l_rows,
224 l_effdates);
225
226 IF l_status= FALSE THEN
227 DELETE FROM qp_rltd_modifiers
228 WHERE from_RLTD_MODIFIER_ID=l_del_dup_cur_rec.list_line_id;
229
230 DELETE qp_pricing_Attributes
231 where list_line_id=l_del_dup_cur_rec.list_line_id;
232
233 delete qp_qualifiers
234 where list_line_id=l_del_dup_cur_rec.list_line_id;
235
236 DELETE qp_list_lines
237 where list_line_id=l_del_dup_cur_rec.list_line_id;
238 END IF;
239
240 END LOOP;
241 END IF; /* End of IF p_effective_dates_flag = 'N' */
242
243 /*
244 IF p_effective_dates_flag = 'N' THEN
245
246 FOR l_del_dup_cur_rec IN del_dup_cur(p_new_list_header_id)
247 LOOP
248
249 DELETE qp_pricing_attributes pa
250 WHERE pa.list_line_id = l_del_dup_cur_rec.list_line_id;
251
252 DELETE qp_list_lines
253 WHERE CURRENT OF del_dup_cur;
254
255 END LOOP;
256
257 END IF;
258 */
259 END IF;
260 EXCEPTION
261
262 WHEN OTHERS THEN
263
264 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
265 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, 'Error in Deleting Duplicate Lines');
266 END IF;
267
268 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
269
270 END Delete_Duplicate_Lines;
271
272
273 /***********************************************************************/
274 /* Procedure to copy discounts - headers, lines, attributes, qualifiers*/
275 /***********************************************************************/
276
277 PROCEDURE Copy_Discounts
278 (
279 errbuf OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
280 retcode OUT NOCOPY /* file.sql.39 change */ NUMBER,
281 p_from_list_header_id NUMBER,
282 p_new_price_list_name IN VARCHAR2,
283 p_description IN VARCHAR2,
284 p_start_date_active IN VARCHAR2, --DATE, 2752295
285 p_end_date_active IN VARCHAR2, --DATE, 2752295
286 p_rounding_factor IN NUMBER,
287 p_effective_dates_flag IN VARCHAR2,
288 --added for moac bug 4673872
289 p_global_flag IN VARCHAR2,
290 p_org_id IN NUMBER
291 )
292 IS
293
294 l_mapping_tbl mapping_tbl;
295
296 l_name VARCHAR2(240);
297 l_description VARCHAR2(2000);
298 l_version_no VARCHAR2(30);
299 l_new_discount_header_id NUMBER;
300 l_new_qualifier_id NUMBER;
301 l_new_discount_line_id NUMBER;
302 l_new_list_header_id NUMBER;
303 l_new_pricing_attribute_id NUMBER;
304 l_new_rltd_modifier_id NUMBER;
305
306 l_count NUMBER := 0;
307 l_new_from_id NUMBER;
308 l_new_to_id NUMBER;
309
310 l_pric_attr_value_from_number NUMBER := NULL;
311 l_pric_attr_value_to_number NUMBER := NULL;
312
313 l_list_type_code VARCHAR2(30) := '';
314 l_active_flag VARCHAR2(1) := '';
315 l_qual_attr_value_from_number NUMBER := NULL;
316 l_qual_attr_value_to_number NUMBER := NULL;
317 ---
318 l_conc_request_id NUMBER := -1;
319 l_conc_program_application_id NUMBER := -1;
320 l_conc_program_id NUMBER := -1;
321 l_conc_login_id NUMBER := -1;
322 l_user_id NUMBER := -1;
323 x_result varchar2(1);
324 l_qp_status VARCHAR2(1);
325 insert_flag varchar2(1);
326 l_cnt number:=0;
327 l_line_id number;
328 l_min_list_line_id NUMBER;
329 l_max_list_line_id NUMBER;
330
331 --Continuous Price Breaks
332 l_non_cont_pbh_id_tbl QP_UTIL.price_brk_attr_val_tab;
333 l_non_cont_count NUMBER := 0;
334 l_return_status VARCHAR2(1);
335
336 /*
337 CURSOR qp_from_discounts_cur(p_from_list_header_id NUMBER, p_context VARCHAR2,
338 p_attribute VARCHAR2)
339 IS
340 SELECT list_header_id
341 FROM qp_qualifiers a
342 WHERE a.qualifier_context = p_context
343 AND a.qualifier_attribute = p_attribute
344 AND a.qualifier_attr_value = TO_CHAR(p_from_list_header_id)
345 AND a.list_header_id IN
346 (SELECT list_header_id
347 --fix for bug 4673872
348 FROM qp_list_headers_ALL_b
349 WHERE list_type_code = 'DLT');
350 */
351 CURSOR qp_from_discounts_cur(p_from_list_header_id NUMBER)
352 IS
353 SELECT list_header_id
354 --fix for bug 4673872
355 FROM qp_list_headers_ALL_b
356 WHERE list_header_id = p_from_list_header_id
357 AND list_type_code in ('DLT','DEL','CHARGES','PRO','SLT');
358
359 CURSOR qp_qualifiers_cur(p_from_discount_header_id NUMBER)
360 IS
361 SELECT *
362 FROM qp_qualifiers
363 WHERE list_header_id = p_from_discount_header_id and
364 list_line_id = -1;
365
366 CURSOR qp_line_qualifiers_cur(p_from_discount_header_id NUMBER,
367 p_from_discount_line_id NUMBER)
368 IS
369 SELECT *
370 FROM qp_qualifiers
371 WHERE list_header_id = p_from_discount_header_id and
372 list_line_id = p_from_discount_line_id ;
373
374 CURSOR qp_discount_lines_cur(p_from_discount_header_id NUMBER)
375 IS
376 SELECT *
377 FROM qp_list_lines
378 WHERE list_header_id = p_from_discount_header_id
379 AND ((end_date_active IS NULL) OR (trunc(end_date_active) >= trunc(sysdate))); --Added for 2476973
380
381 CURSOR qp_pricing_attributes_cur(p_from_discount_line_id NUMBER)
382 IS
383 SELECT *
384 FROM qp_pricing_attributes
385 WHERE list_line_id = p_from_discount_line_id;
386
387 CURSOR qp_rltd_modifiers_cur(a_list_line_id NUMBER)
388 IS
389 SELECT *
390 FROM qp_rltd_modifiers
391 WHERE from_rltd_modifier_id = a_list_line_id;
392
393
394 BEGIN
395 l_conc_request_id := FND_GLOBAL.CONC_REQUEST_ID;
396 l_conc_program_id := FND_GLOBAL.CONC_PROGRAM_ID;
397 l_user_id := FND_GLOBAL.USER_ID;
398 l_conc_login_id := FND_GLOBAL.CONC_LOGIN_ID;
399 l_conc_program_application_id := FND_GLOBAL.PROG_APPL_ID;
400 l_qp_status := QP_UTIL.get_qp_status;
401
402 FOR qp_from_discounts_rec IN qp_from_discounts_cur(p_from_list_header_id)
403 LOOP
404 /* For every old(from) discount, Copy discount header records */
405
406 l_count := 0; --Reset the mapping table count for each discount header
407
408 --Select next discount_header_id
409
410 SELECT qp_list_headers_b_s.nextval
411 INTO l_new_discount_header_id
412 FROM dual;
413
414 --Discount Header Information
415
416 --if fnd_profile.value('QP_ATTRIBUTE_MANAGER_INSTALLED') = 'Y' then
417 if QP_UTIL.Attrmgr_Installed = 'Y' then
418 INSERT INTO qp_list_headers_all_b
419 (
420 list_header_id,
421 creation_date,
422 created_by,
423 last_update_date,
424 last_updated_by,
425 last_update_login,
426 program_application_id,
427 program_id,
428 program_update_date,
429 request_id,
430 list_type_code,
431 start_date_active,
432 end_date_active,
433 automatic_flag,
434 -- exclusive_flag,
435 currency_code,
436 rounding_factor,
437 ship_method_code,
438 freight_terms_code,
439 terms_id,
440 context,
441 attribute1,
442 attribute2,
443 attribute3,
444 attribute4,
445 attribute5,
446 attribute6,
447 attribute7,
448 attribute8,
449 attribute9,
450 attribute10,
451 attribute11,
452 attribute12,
453 attribute13,
454 attribute14,
455 attribute15,
456 comments,
457 discount_lines_flag,
458 gsa_indicator,
459 prorate_flag,
460 source_system_code,
461 active_flag,
462 parent_list_header_id,
463 start_date_active_first,
464 end_date_active_first,
465 active_date_first_type,
466 start_date_active_second,
467 end_date_active_second,
468 active_date_second_type,
469 ask_for_flag,
470 pte_code,
471 global_flag,
472 orig_org_id
473 --ENH Upgrade BOAPI for orig_sys...ref RAVI
474 --,ORIG_SYSTEM_HEADER_REF
475 )
476
477 SELECT
478 l_new_discount_header_id,
479 sysdate,
480 l_user_id,
481 sysdate,
482 l_user_id,
483 l_conc_login_id,
484 l_conc_program_application_id,
485 l_conc_program_id,
486 sysdate,
487 l_conc_request_id,
488 list_type_code,
489 --decode(p_start_date_active,null,start_date_active,p_start_date_active),
490 --decode(p_end_date_active,null,end_date_active,p_end_date_active),
491 --p_start_date_active,
492 --p_end_date_active,
493 fnd_date.canonical_to_date(p_start_date_active), --2752295
494 fnd_date.canonical_to_date(p_end_date_active), --2752295
495 automatic_flag,
496 -- exclusive_flag,
497 currency_code,
498 rounding_factor,
499 ship_method_code,
500 freight_terms_code,
501 terms_id,
502 context,
503 attribute1,
504 attribute2,
505 attribute3,
506 attribute4,
507 attribute5,
508 attribute6,
509 attribute7,
510 attribute8,
511 attribute9,
512 attribute10,
513 attribute11,
514 attribute12,
515 attribute13,
516 attribute14,
517 attribute15,
518 comments,
519 discount_lines_flag,
520 gsa_indicator,
521 prorate_flag,
522 --source_system_code,
523 fnd_profile.value('QP_SOURCE_SYSTEM_CODE'),
524 DECODE(l_qp_status,'S','Y','N'), --2707484
525 --active_flag, -- bug 2180582 Was harcoded to N earlier
526 parent_list_header_id,
527 start_date_active_first,
528 end_date_active_first,
529 --decode(p_start_date_active,null,null,start_date_active_first),
530 --decode(p_end_date_active,null,null,end_date_active_first),
531 active_date_first_type,
532 start_date_active_second,
533 end_date_active_second,
534 --decode(p_start_date_active,null,null,start_date_active_second),
535 --decode(p_end_date_active,null,null,end_date_active_second),
536 active_date_second_type,
537 ask_for_flag,
538 fnd_profile.value('QP_PRICING_TRANSACTION_ENTITY'),
539 p_global_flag,
540 --added for MOAC
541 p_org_id
542 --ENH Upgrade BOAPI for orig_sys...ref RAVI
543 --,nvl(ORIG_SYSTEM_HEADER_REF,QP_PRICE_LIST_UTIL.Get_Orig_Sys_Hdr(l_new_discount_header_id))
544 --fix for bug 4673872
545 FROM qp_list_headers_ALL_b
546 WHERE list_header_id = qp_from_discounts_rec.list_header_id;
547 else
548 INSERT INTO qp_list_headers_all_b
549 (
550 list_header_id,
551 creation_date,
552 created_by,
553 last_update_date,
554 last_updated_by,
555 last_update_login,
556 program_application_id,
557 program_id,
558 program_update_date,
559 request_id,
560 list_type_code,
561 start_date_active,
562 end_date_active,
563 automatic_flag,
564 -- exclusive_flag,
565 currency_code,
566 rounding_factor,
567 ship_method_code,
568 freight_terms_code,
569 terms_id,
570 context,
571 attribute1,
572 attribute2,
573 attribute3,
574 attribute4,
575 attribute5,
576 attribute6,
577 attribute7,
578 attribute8,
579 attribute9,
580 attribute10,
581 attribute11,
582 attribute12,
583 attribute13,
584 attribute14,
585 attribute15,
586 comments,
587 discount_lines_flag,
588 gsa_indicator,
589 prorate_flag,
590 source_system_code,
591 active_flag,
592 parent_list_header_id,
593 start_date_active_first,
594 end_date_active_first,
595 active_date_first_type,
596 start_date_active_second,
597 end_date_active_second,
598 active_date_second_type,
599 ask_for_flag,
600 global_flag,
601 orig_org_id
602 --ENH Upgrade BOAPI for orig_sys...ref RAVI
603 --,ORIG_SYSTEM_HEADER_REF
604 )
605
606 SELECT
607 l_new_discount_header_id,
608 sysdate,
609 l_user_id,
610 sysdate,
611 l_user_id,
612 l_conc_login_id,
613 l_conc_program_application_id,
614 l_conc_program_id,
615 sysdate,
616 l_conc_request_id,
617 list_type_code,
618 --decode(p_start_date_active,null,start_date_active,p_start_date_active),
619 --decode(p_end_date_active,null,end_date_active,p_end_date_active),
620 --p_start_date_active,
621 --p_end_date_active,
622 fnd_date.canonical_to_date(p_start_date_active), --2752295
623 fnd_date.canonical_to_date(p_end_date_active), --2752295
624 automatic_flag,
625 -- exclusive_flag,
626 currency_code,
627 rounding_factor,
628 ship_method_code,
629 freight_terms_code,
630 terms_id,
631 context,
632 attribute1,
633 attribute2,
634 attribute3,
635 attribute4,
636 attribute5,
637 attribute6,
638 attribute7,
639 attribute8,
640 attribute9,
641 attribute10,
642 attribute11,
643 attribute12,
644 attribute13,
645 attribute14,
646 attribute15,
647 comments,
648 discount_lines_flag,
649 gsa_indicator,
650 prorate_flag,
651 --source_system_code,
652 fnd_profile.value('QP_SOURCE_SYSTEM_CODE'),
653 --active_flag, -- bug 2180582 Was harcoded to N earlier
654 DECODE(l_qp_status,'S','Y','N'), --2707484
655 parent_list_header_id,
656 start_date_active_first,
657 end_date_active_first,
658 --decode(p_start_date_active,null,null,start_date_active_first),
659 --decode(p_end_date_active,null,null,end_date_active_first),
660 active_date_first_type,
661 start_date_active_second,
662 end_date_active_second,
663 --decode(p_start_date_active,null,null,start_date_active_second),
664 --decode(p_end_date_active,null,null,end_date_active_second),
665 active_date_second_type,
666 ask_for_flag,
667 p_global_flag,
668 --added for MOAC
669 p_org_id
670 --ENH Upgrade BOAPI for orig_sys...ref RAVI
671 --,nvl(ORIG_SYSTEM_HEADER_REF,QP_PRICE_LIST_UTIL.Get_Orig_Sys_Hdr(l_new_discount_header_id))
672 --fix for bug 4673872
673 FROM qp_list_headers_ALL_b
674 WHERE list_header_id = qp_from_discounts_rec.list_header_id;
675 end if;
676 --
677 SELECT version_no
678 INTO l_version_no
679 FROM qp_list_headers_tl
680 WHERE list_header_id = qp_from_discounts_rec.list_header_id
681 AND LANGUAGE=USERENV('LANG');
682
683 INSERT INTO qp_list_headers_tl
684 (last_update_login,
685 name,
686 description,
687 creation_date,
688 created_by,
689 last_update_date,
690 last_updated_by,
691 list_header_id,
692 language,
693 source_lang,
694 version_no
695 )
696 SELECT
697 l_conc_login_id,
698 p_new_price_list_name,
699 p_description,
700 sysdate,
701 l_user_id,
702 sysdate,
703 l_user_id,
704 l_new_discount_header_id,
705 l.language_code,
706 userenv('LANG'),
707 l_version_no
708 FROM fnd_languages l
709 WHERE l.installed_flag IN ('I', 'B')
710 AND NOT EXISTS (SELECT NULL
711 FROM qp_list_headers_tl t
712 WHERE t.list_header_id = l_new_discount_header_id
713 AND t.language = l.language_code);
714
715
716
717 /* Copy all qualifiers for the discount and in case of the qualifier
718 being the from-pricelist replace it with the new pricelist*/
719
720 --
721 -- Calling Object Security to grant default grants to newly created modifier.
722 QP_Security.create_default_grants( p_instance_type => QP_Security.G_MODIFIER_OBJECT,
723 p_instance_pk1 => l_new_discount_header_id,
724 x_return_status => x_result);
725 --
726 FOR l_qp_qualifiers_rec IN
727 qp_qualifiers_cur(qp_from_discounts_rec.list_header_id)
728 LOOP
729
730 --Get new qualifier_id
731 SELECT qp_qualifiers_s.nextval
732 INTO l_new_qualifier_id
733 FROM dual;
734
735 /*
736 IF l_qp_qualifiers_rec.qualifier_attr_value =
737 TO_CHAR(p_from_list_header_id) AND
738 l_qp_qualifiers_rec.qualifier_context = p_context AND
739 l_qp_qualifiers_rec.qualifier_attribute = p_attribute
740 THEN
741 l_qp_qualifiers_rec.qualifier_attr_value :=
742 TO_CHAR(p_new_list_header_id);
743 END IF;
744 */
745
746 BEGIN
747
748 SELECT ACTIVE_FLAG, LIST_TYPE_CODE
749 INTO l_active_flag, l_list_type_code
750 --fix for bug 4673872
751 FROM QP_LIST_HEADERS_ALL_B
752 WHERE LIST_HEADER_ID = l_new_discount_header_id;
753
754 EXCEPTION
755 WHEN OTHERS THEN
756 NULL;
757 END;
758
759 IF l_qp_qualifiers_rec.qualifier_datatype = 'N'
760 then
761
762 BEGIN
763
764 l_qual_attr_value_from_number :=
765 qp_number.canonical_to_number(l_qp_qualifiers_rec.qualifier_attr_value);
766
767 l_qual_attr_value_to_number :=
768 qp_number.canonical_to_number(l_qp_qualifiers_rec.qualifier_attr_value_to);
769
770 EXCEPTION
771 WHEN VALUE_ERROR THEN
772 NULL;
773 WHEN OTHERS THEN
774 NULL;
775 END;
776
777 end if;
778
779 --Insert new qualifier
780 INSERT INTO qp_qualifiers
781 (
782 qualifier_id,
783 creation_date,
784 created_by,
785 last_update_date,
786 last_updated_by,
787 last_update_login,
788 program_application_id,
789 program_id,
790 program_update_date,
791 request_id,
792 excluder_flag,
793 comparison_operator_code,
794 qualifier_context,
795 qualifier_attribute,
796 context,
797 attribute1,
798 attribute2,
799 attribute3,
800 attribute4,
801 attribute5,
802 attribute6,
803 attribute7,
804 attribute8,
805 attribute9,
806 attribute10,
807 attribute11,
808 attribute12,
809 attribute13,
810 attribute14,
811 attribute15,
812 qualifier_rule_id,
813 qualifier_grouping_no,
814 qualifier_attr_value,
815 list_header_id,
816 list_line_id,
817 created_from_rule_id,
818 start_date_active,
819 end_date_active,
820 qualifier_precedence,
821 qualifier_datatype,
822 qualifier_attr_value_to,
823 active_flag,
824 list_type_code,
825 qual_attr_value_from_number,
826 qual_attr_value_to_number,
827 search_ind,
828 distinct_row_count,
829 qualifier_group_cnt,
830 header_quals_exist_flag,
831 qualify_hier_descendents_flag -- Added for TCA
832 --ENH Upgrade BOAPI for orig_sys...ref RAVI
833 --,ORIG_SYS_QUALIFIER_REF
834 --,ORIG_SYS_LINE_REF
835 --,ORIG_SYS_HEADER_REF
836 )
837 VALUES
838 (
839 l_new_qualifier_id,
840 sysdate,
841 l_user_id,
842 sysdate,
843 l_user_id,
844 l_conc_login_id,
845 l_conc_program_application_id,
846 l_conc_program_id,
847 sysdate,
848 l_conc_request_id,
849 l_qp_qualifiers_rec.excluder_flag,
850 l_qp_qualifiers_rec.comparison_operator_code,
851 l_qp_qualifiers_rec.qualifier_context,
852 l_qp_qualifiers_rec.qualifier_attribute,
853 l_qp_qualifiers_rec.context,
854 l_qp_qualifiers_rec.attribute1,
855 l_qp_qualifiers_rec.attribute2,
856 l_qp_qualifiers_rec.attribute3,
857 l_qp_qualifiers_rec.attribute4,
858 l_qp_qualifiers_rec.attribute5,
859 l_qp_qualifiers_rec.attribute6,
860 l_qp_qualifiers_rec.attribute7,
861 l_qp_qualifiers_rec.attribute8,
862 l_qp_qualifiers_rec.attribute9,
863 l_qp_qualifiers_rec.attribute10,
864 l_qp_qualifiers_rec.attribute11,
865 l_qp_qualifiers_rec.attribute12,
866 l_qp_qualifiers_rec.attribute13,
867 l_qp_qualifiers_rec.attribute14,
868 l_qp_qualifiers_rec.attribute15,
869 l_qp_qualifiers_rec.qualifier_rule_id,
870 l_qp_qualifiers_rec.qualifier_grouping_no,
871 l_qp_qualifiers_rec.qualifier_attr_value,
872 l_new_discount_header_id,
873 --l_qp_qualifiers_rec.list_line_id,
874 -1, ---l_qp_qualifiers_rec.list_line_id,
875 l_qp_qualifiers_rec.created_from_rule_id,
876 l_qp_qualifiers_rec.start_date_active,
877 l_qp_qualifiers_rec.end_date_active,
878 l_qp_qualifiers_rec.qualifier_precedence,
879 l_qp_qualifiers_rec.qualifier_datatype,
880 l_qp_qualifiers_rec.qualifier_attr_value_to,
881 l_active_flag,
882 l_list_type_code,
883 l_qual_attr_value_from_number,
884 l_qual_attr_value_to_number,
885 l_qp_qualifiers_rec.search_ind,
886 l_qp_qualifiers_rec.distinct_row_count,
887 l_qp_qualifiers_rec.qualifier_group_cnt,
888 l_qp_qualifiers_rec.header_quals_exist_flag,
889 l_qp_qualifiers_rec.qualify_hier_descendents_flag -- Added for TCA
890 --ENH Upgrade BOAPI for orig_sys...ref RAVI
891 --,to_char(l_new_qualifier_id)
892 --,null
893 --,(select h.ORIG_SYSTEM_HEADER_REF from qp_list_headers_b h where h.list_header_id=l_new_discount_header_id)
894 );
895
896 END LOOP; /* For copying List qualifiers */
897
898
899 /* Copy all lines for the discount */
900
901 FOR l_qp_discount_lines_rec IN
902 qp_discount_lines_cur (qp_from_discounts_rec.list_header_id)
903 LOOP
904 /* Added for 2734611.When modifier line is end dated,its related lines e.g. child lines
905 are still active. Cursor qp_discount_lines_cur selects all active lines, therefore,
906 orphaned child lines are also copied to new modifier list.The following logic
907 excludes such orphaned lines from being copied. */
908
909 insert_flag :='N';
910 BEGIN
911 select from_rltd_modifier_id into l_line_id
912 from qp_rltd_modifiers
913 where to_rltd_modifier_id=l_qp_discount_lines_rec.list_line_id;
914 Exception
915 when no_data_found then
916 l_line_id:=null;
917 End;
918
919 If l_line_id is null then
920 insert_flag :='Y';
921 Else
922 Begin
923 select count(*) into l_cnt from qp_list_lines where list_line_id=l_line_id
924 AND ((end_date_active IS NULL) OR (trunc(end_date_active) >= trunc(sysdate)));
925 Exception
926 when no_data_found then
927 l_cnt:=0;
928 End;
929
930 If l_cnt > 0 then
931 insert_flag :='Y';
932 End if;
933 End if;
934
935 If insert_flag ='Y' then --end 2734611 changes
936
937 --Get New Discount Line Id
938 SELECT qp_list_lines_s.nextval
939 INTO l_new_discount_line_id
940 FROM dual;
941
942 --Insert Discount Line
943 INSERT INTO qp_list_lines
944 (
945 list_line_id,
946 creation_date,
947 created_by,
948 last_update_date,
949 last_updated_by,
950 last_update_login,
951 program_application_id,
952 program_id,
953 program_update_date,
954 request_id,
955 list_header_id,
956 list_line_type_code,
957 start_date_active,
958 end_date_active,
959 automatic_flag,
960 modifier_level_code,
961 list_price,
962 primary_uom_flag,
963 inventory_item_id,
964 organization_id,
965 related_item_id,
966 relationship_type_id,
967 substitution_context,
968 substitution_attribute,
969 substitution_value,
970 revision,
971 revision_date,
972 revision_reason_code,
973 context,
974 attribute1,
975 attribute2,
976 comments,
977 attribute3,
978 attribute4,
979 attribute5,
980 attribute6,
981 attribute7,
982 attribute8,
983 attribute9,
984 attribute10,
985 attribute11,
986 attribute12,
987 attribute13,
988 attribute14,
989 attribute15,
990 price_break_type_code,
991 percent_price,
992 price_by_formula_id,
993 number_effective_periods,
994 effective_period_uom,
995 arithmetic_operator,
996 operand,
997 override_flag,
998 print_on_invoice_flag,
999 rebate_transaction_type_code,
1000 estim_accrual_rate,
1001 generate_using_formula_id,
1002 reprice_flag,
1003 accrual_flag,
1004 pricing_group_sequence,
1005 incompatibility_grp_code,
1006 list_line_no,
1007 product_precedence,
1008 pricing_phase_id,
1009 expiration_period_start_date,
1010 number_expiration_periods,
1011 expiration_period_uom,
1012 expiration_date,
1013 estim_gl_value,
1014 accrual_conversion_rate,
1015 benefit_price_list_line_id,
1016 proration_type_code,
1017 benefit_qty,
1018 benefit_uom_code,
1019 charge_type_code,
1020 charge_subtype_code,
1021 benefit_limit,
1022 include_on_returns_flag,
1023 qualification_ind,
1024 net_amount_flag,
1025 accum_context,
1026 accum_attribute,
1027 accum_attr_run_src_flag,
1028 continuous_price_break_flag --Continuous Price Breaks
1029 --ENH Upgrade BOAPI for orig_sys...ref RAVI
1030 --,ORIG_SYS_LINE_REF
1031 --,ORIG_SYS_HEADER_REF
1032 )
1033 VALUES
1034 (
1035 l_new_discount_line_id,
1036 sysdate,
1037 l_user_id,
1038 sysdate,
1039 l_user_id,
1040 l_conc_login_id,
1041 l_conc_program_application_id,
1042 l_conc_program_id,
1043 sysdate,
1044 l_conc_request_id,
1045 l_new_discount_header_id,
1046 l_qp_discount_lines_rec.list_line_type_code,
1047 DECODE (p_effective_dates_flag,
1048 'Y', l_qp_discount_lines_rec.start_date_active,
1049 NULL), /* If flag='Y', retain start date from copied line */
1050 /* else default start date */
1051 DECODE (p_effective_dates_flag,
1052 'Y', l_qp_discount_lines_rec.end_date_active,
1053 NULL), /* If flag='Y', retain end date from copied line */
1054 /* else default end date */
1055 --l_qp_discount_lines_rec.start_date_active,
1056 --l_qp_discount_lines_rec.end_date_active,
1057 l_qp_discount_lines_rec.automatic_flag,
1058 l_qp_discount_lines_rec.modifier_level_code,
1059 l_qp_discount_lines_rec.list_price,
1060 l_qp_discount_lines_rec.primary_uom_flag,
1061 l_qp_discount_lines_rec.inventory_item_id,
1062 l_qp_discount_lines_rec.organization_id,
1063 l_qp_discount_lines_rec.related_item_id,
1064 l_qp_discount_lines_rec.relationship_type_id,
1065 l_qp_discount_lines_rec.substitution_context,
1066 l_qp_discount_lines_rec.substitution_attribute,
1067 l_qp_discount_lines_rec.substitution_value,
1068 l_qp_discount_lines_rec.revision,
1069 l_qp_discount_lines_rec.revision_date,
1070 l_qp_discount_lines_rec.revision_reason_code,
1071 l_qp_discount_lines_rec.context,
1072 l_qp_discount_lines_rec.attribute1,
1073 l_qp_discount_lines_rec.attribute2,
1074 l_qp_discount_lines_rec.comments,
1075 l_qp_discount_lines_rec.attribute3,
1076 l_qp_discount_lines_rec.attribute4,
1077 l_qp_discount_lines_rec.attribute5,
1078 l_qp_discount_lines_rec.attribute6,
1079 l_qp_discount_lines_rec.attribute7,
1080 l_qp_discount_lines_rec.attribute8,
1081 l_qp_discount_lines_rec.attribute9,
1082 l_qp_discount_lines_rec.attribute10,
1083 l_qp_discount_lines_rec.attribute11,
1084 l_qp_discount_lines_rec.attribute12,
1085 l_qp_discount_lines_rec.attribute13,
1086 l_qp_discount_lines_rec.attribute14,
1087 l_qp_discount_lines_rec.attribute15,
1088 l_qp_discount_lines_rec.price_break_type_code,
1089 l_qp_discount_lines_rec.percent_price,
1090 l_qp_discount_lines_rec.price_by_formula_id,
1091 l_qp_discount_lines_rec.number_effective_periods,
1092 l_qp_discount_lines_rec.effective_period_uom,
1093 l_qp_discount_lines_rec.arithmetic_operator,
1094 l_qp_discount_lines_rec.operand,
1095 l_qp_discount_lines_rec.override_flag,
1096 l_qp_discount_lines_rec.print_on_invoice_flag,
1097 l_qp_discount_lines_rec.rebate_transaction_type_code,
1098 l_qp_discount_lines_rec.estim_accrual_rate,
1099 l_qp_discount_lines_rec.generate_using_formula_id,
1100 l_qp_discount_lines_rec.reprice_flag,
1101 l_qp_discount_lines_rec.accrual_flag,
1102 l_qp_discount_lines_rec.pricing_group_sequence,
1103 l_qp_discount_lines_rec.incompatibility_grp_code,
1104 l_qp_discount_lines_rec.list_line_no,
1105 l_qp_discount_lines_rec.product_precedence,
1106 l_qp_discount_lines_rec.pricing_phase_id,
1107 l_qp_discount_lines_rec.expiration_period_start_date,
1108 l_qp_discount_lines_rec.number_expiration_periods,
1109 l_qp_discount_lines_rec.expiration_period_uom,
1110 l_qp_discount_lines_rec.expiration_date,
1111 l_qp_discount_lines_rec.estim_gl_value,
1112 l_qp_discount_lines_rec.accrual_conversion_rate,
1113 l_qp_discount_lines_rec.benefit_price_list_line_id,
1114 l_qp_discount_lines_rec.proration_type_code,
1115 l_qp_discount_lines_rec.benefit_qty,
1116 l_qp_discount_lines_rec.benefit_uom_code,
1117 l_qp_discount_lines_rec.charge_type_code,
1118 l_qp_discount_lines_rec.charge_subtype_code,
1119 l_qp_discount_lines_rec.benefit_limit,
1120 l_qp_discount_lines_rec.include_on_returns_flag,
1121 l_qp_discount_lines_rec.qualification_ind,
1122 l_qp_discount_lines_rec.net_amount_flag,
1123 l_qp_discount_lines_rec.accum_context,
1124 l_qp_discount_lines_rec.accum_attribute,
1125 l_qp_discount_lines_rec.accum_attr_run_src_flag,
1126 decode(nvl(FND_PROFILE.VALUE('QP_CONTINUOUS_PB'),'Y'),'Y','Y','N') -- Continuous Price Breaks -- 14103598
1127 --ENH Upgrade BOAPI for orig_sys...ref RAVI
1128 --,to_char(l_new_discount_line_id)
1129 --,(select h.ORIG_SYSTEM_HEADER_REF from qp_list_headers_b h where h.list_header_id=l_new_discount_header_id)
1130 );
1131
1132 /* Insert line qualifiers */
1133 If l_qp_discount_lines_rec.list_line_id is not null and
1134 l_qp_discount_lines_rec.list_line_id <> -1 then
1135 FOR l_qp_line_qualifiers_rec IN
1136 qp_line_qualifiers_cur(qp_from_discounts_rec.list_header_id,
1137 l_qp_discount_lines_rec.list_line_id)
1138 LOOP
1139
1140 --Get new qualifier_id
1141 SELECT qp_qualifiers_s.nextval
1142 INTO l_new_qualifier_id
1143 FROM dual;
1144
1145 /*
1146 IF l_qp_line_qualifiers_rec.qualifier_attr_value =
1147 TO_CHAR(p_from_list_header_id) AND
1148 l_qp_line_qualifiers_rec.qualifier_context = p_context AND
1149 l_qp_line_qualifiers_rec.qualifier_attribute = p_attribute
1150 THEN
1151 l_qp_line_qualifiers_rec.qualifier_attr_value :=
1152 TO_CHAR(p_new_list_header_id);
1153 END IF;
1154 */
1155
1156 BEGIN
1157
1158 SELECT ACTIVE_FLAG, LIST_TYPE_CODE
1159 INTO l_active_flag, l_list_type_code
1160 --fix for bug 4673872
1161 FROM QP_LIST_HEADERS_ALL_B
1162 WHERE LIST_HEADER_ID = l_new_discount_header_id;
1163
1164 EXCEPTION
1165 WHEN OTHERS THEN
1166 NULL;
1167 END;
1168
1169 IF l_qp_line_qualifiers_rec.qualifier_datatype = 'N'
1170 then
1171
1172 BEGIN
1173
1174 l_qual_attr_value_from_number :=
1175 qp_number.canonical_to_number(l_qp_line_qualifiers_rec.qualifier_attr_value);
1176
1177 l_qual_attr_value_to_number :=
1178 qp_number.canonical_to_number(l_qp_line_qualifiers_rec.qualifier_attr_value_to);
1179
1180 EXCEPTION
1181 WHEN VALUE_ERROR THEN
1182 NULL;
1183 WHEN OTHERS THEN
1184 NULL;
1185 END;
1186
1187 end if;
1188
1189 --Insert new qualifier
1190 INSERT INTO qp_qualifiers
1191 (
1192 qualifier_id,
1193 creation_date,
1194 created_by,
1195 last_update_date,
1196 last_updated_by,
1197 last_update_login,
1198 program_application_id,
1199 program_id,
1200 program_update_date,
1201 request_id,
1202 excluder_flag,
1203 comparison_operator_code,
1204 qualifier_context,
1205 qualifier_attribute,
1206 context,
1207 attribute1,
1208 attribute2,
1209 attribute3,
1210 attribute4,
1211 attribute5,
1212 attribute6,
1213 attribute7,
1214 attribute8,
1215 attribute9,
1216 attribute10,
1217 attribute11,
1218 attribute12,
1219 attribute13,
1220 attribute14,
1221 attribute15,
1222 qualifier_rule_id,
1223 qualifier_grouping_no,
1224 qualifier_attr_value,
1225 list_header_id,
1226 list_line_id,
1227 created_from_rule_id,
1228 start_date_active,
1229 end_date_active,
1230 qualifier_precedence,
1231 qualifier_datatype,
1232 qualifier_attr_value_to,
1233 active_flag,
1234 list_type_code,
1235 qual_attr_value_from_number,
1236 qual_attr_value_to_number,
1237 search_ind,
1238 distinct_row_count,
1239 qualifier_group_cnt,
1240 header_quals_exist_flag,
1241 qualify_hier_descendents_flag -- Added for TCA
1242 --ENH Upgrade BOAPI for orig_sys...ref RAVI
1243 --,ORIG_SYS_QUALIFIER_REF
1244 --,ORIG_SYS_LINE_REF
1245 --,ORIG_SYS_HEADER_REF
1246 )
1247 VALUES
1248 (
1249 l_new_qualifier_id,
1250 sysdate,
1251 l_user_id,
1252 sysdate,
1253 l_user_id,
1254 l_conc_login_id,
1255 l_conc_program_application_id,
1256 l_conc_program_id,
1257 sysdate,
1258 l_conc_request_id,
1259 l_qp_line_qualifiers_rec.excluder_flag,
1260 l_qp_line_qualifiers_rec.comparison_operator_code,
1261 l_qp_line_qualifiers_rec.qualifier_context,
1262 l_qp_line_qualifiers_rec.qualifier_attribute,
1263 l_qp_line_qualifiers_rec.context,
1264 l_qp_line_qualifiers_rec.attribute1,
1265 l_qp_line_qualifiers_rec.attribute2,
1266 l_qp_line_qualifiers_rec.attribute3,
1267 l_qp_line_qualifiers_rec.attribute4,
1268 l_qp_line_qualifiers_rec.attribute5,
1269 l_qp_line_qualifiers_rec.attribute6,
1270 l_qp_line_qualifiers_rec.attribute7,
1271 l_qp_line_qualifiers_rec.attribute8,
1272 l_qp_line_qualifiers_rec.attribute9,
1273 l_qp_line_qualifiers_rec.attribute10,
1274 l_qp_line_qualifiers_rec.attribute11,
1275 l_qp_line_qualifiers_rec.attribute12,
1276 l_qp_line_qualifiers_rec.attribute13,
1277 l_qp_line_qualifiers_rec.attribute14,
1278 l_qp_line_qualifiers_rec.attribute15,
1279 l_qp_line_qualifiers_rec.qualifier_rule_id,
1280 l_qp_line_qualifiers_rec.qualifier_grouping_no,
1281 l_qp_line_qualifiers_rec.qualifier_attr_value,
1282 l_new_discount_header_id,
1283 l_new_discount_line_id,
1284 --l_qp_line_qualifiers_rec.list_line_id,
1285 l_qp_line_qualifiers_rec.created_from_rule_id,
1286 l_qp_line_qualifiers_rec.start_date_active,
1287 l_qp_line_qualifiers_rec.end_date_active,
1288 l_qp_line_qualifiers_rec.qualifier_precedence,
1289 l_qp_line_qualifiers_rec.qualifier_datatype,
1290 l_qp_line_qualifiers_rec.qualifier_attr_value_to,
1291 l_active_flag,
1292 l_list_type_code,
1293 l_qual_attr_value_from_number,
1294 l_qual_attr_value_to_number,
1295 l_qp_line_qualifiers_rec.search_ind,
1296 l_qp_line_qualifiers_rec.distinct_row_count,
1297 l_qp_line_qualifiers_rec.qualifier_group_cnt,
1298 l_qp_line_qualifiers_rec.header_quals_exist_flag,
1299 l_qp_line_qualifiers_rec.qualify_hier_descendents_flag -- Added for TCA
1300 --ENH Upgrade BOAPI for orig_sys...ref RAVI
1301 --,to_char(l_new_qualifier_id)
1302 --,(select l.ORIG_SYS_LINE_REF from qp_list_lines l where l.list_line_id=l_new_discount_line_id)
1303 --,(select h.ORIG_SYSTEM_HEADER_REF from qp_list_headers_b h where h.list_header_id=l_new_discount_header_id)
1304 );
1305
1306 END LOOP;
1307 end if;
1308 /* For copying Line qualifiers */
1309
1310 /*If the discount_line_rec is a Price Break Parent Line or Price Break Line
1311 then store the old and new discountlineid in a mapping-array for later use*/
1312
1313 ---IF l_qp_discount_lines_rec.list_line_type_code = 'PBH' OR
1314 IF l_qp_discount_lines_rec.list_line_type_code in ('PBH','OID','PRG','CIE') OR
1315 Price_Break_Line(l_qp_discount_lines_rec.list_line_id)
1316 THEN
1317 l_count := l_count + 1;
1318 l_mapping_tbl(l_count).list_line_type_code :=
1319 l_qp_discount_lines_rec.list_line_type_code;
1320 l_mapping_tbl(l_count).old_list_line_id :=
1321 l_qp_discount_lines_rec.list_line_id;
1322 l_mapping_tbl(l_count).new_list_line_id := l_new_discount_line_id;
1323 END IF;
1324
1325 IF l_qp_discount_lines_rec.list_line_type_code = 'PBH' AND
1326 (l_qp_discount_lines_rec.continuous_price_break_flag IS NULL OR l_qp_discount_lines_rec.continuous_price_break_flag <> 'Y')
1327 THEN
1328 l_non_cont_count := l_non_cont_count + 1;
1329 l_non_cont_pbh_id_tbl(l_non_cont_count).price_break_header_id := l_new_discount_line_id;
1330 l_non_cont_pbh_id_tbl(l_non_cont_count).list_line_no := l_qp_discount_lines_rec.list_line_no;
1331
1332 IF p_effective_dates_flag = 'Y' THEN
1333 l_non_cont_pbh_id_tbl(l_non_cont_count).start_date_active := l_qp_discount_lines_rec.start_date_active;
1334 l_non_cont_pbh_id_tbl(l_non_cont_count).end_date_active := l_qp_discount_lines_rec.end_date_active;
1335 END IF;
1336
1337 END IF;
1338
1339
1340 /* Copy the qp_pricing_attributes records for each discount line being
1341 copied */
1342
1343 FOR l_qp_pricing_attributes_rec IN qp_pricing_attributes_cur
1344 (l_qp_discount_lines_rec.list_line_id)
1345 -- basically the from_discount_list_line_id
1346 LOOP
1347
1348 -- Get next pricing_attribute_id
1349 SELECT qp_pricing_attributes_s.nextval
1350 INTO l_new_pricing_attribute_id
1351 FROM dual;
1352
1353 IF l_qp_discount_lines_rec.list_line_type_code = 'PBH' AND
1354 (l_qp_discount_lines_rec.continuous_price_break_flag IS NULL OR l_qp_discount_lines_rec.continuous_price_break_flag <> 'Y')
1355 AND l_qp_pricing_attributes_rec.list_line_id = l_qp_discount_lines_rec.list_line_id
1356 THEN
1357 l_non_cont_pbh_id_tbl(l_non_cont_count).product_attribute :=
1358 l_qp_pricing_attributes_rec.product_attribute;
1359 l_non_cont_pbh_id_tbl(l_non_cont_count).product_attr_value:=
1360 l_qp_pricing_attributes_rec.product_attr_value;
1361 END IF;
1362
1363
1364
1365 IF l_qp_pricing_attributes_rec.pricing_attribute_datatype = 'N'
1366 then
1367
1368 BEGIN
1369
1370 l_pric_attr_value_from_number :=
1371 qp_number.canonical_to_number(l_qp_pricing_attributes_rec.pricing_attr_value_from);
1372
1373 l_pric_attr_value_to_number :=
1374 qp_number.canonical_to_number(l_qp_pricing_attributes_rec.pricing_attr_value_to);
1375
1376 EXCEPTION
1377 WHEN VALUE_ERROR THEN
1378 NULL;
1379 WHEN OTHERS THEN
1380 NULL;
1381 END;
1382
1383 end if;
1384
1385 INSERT INTO qp_pricing_attributes
1386 (pricing_attribute_id,
1387 creation_date,
1388 created_by,
1389 last_update_date,
1390 last_updated_by,
1391 last_update_login,
1392 program_application_id,
1393 program_id,
1394 program_update_date,
1395 request_id,
1396 list_line_id,
1397 list_header_id,
1398 pricing_phase_id,
1399 qualification_ind,
1400 excluder_flag,
1401 accumulate_flag,
1402 product_attribute_context,
1403 product_attribute,
1404 product_attr_value,
1405 product_uom_code,
1406 pricing_attribute_context,
1407 pricing_attribute,
1408 pricing_attr_value_from,
1409 pricing_attr_value_to,
1410 attribute_grouping_no,
1411 context,
1412 attribute1,
1413 attribute2,
1414 attribute3,
1415 attribute4,
1416 attribute5,
1417 attribute6,
1418 attribute7,
1419 attribute8,
1420 attribute9,
1421 attribute10,
1422 attribute11,
1423 attribute12,
1424 attribute13,
1425 attribute14,
1426 attribute15,
1427 product_attribute_datatype,
1428 pricing_attribute_datatype,
1429 comparison_operator_code,
1430 pricing_attr_value_from_number,
1431 pricing_attr_value_to_number
1432 --ENH Upgrade BOAPI for orig_sys...ref RAVI
1433 --,ORIG_SYS_PRICING_ATTR_REF
1434 --,ORIG_SYS_LINE_REF
1435 --,ORIG_SYS_HEADER_REF
1436 )
1437 VALUES
1438 (l_new_pricing_attribute_id,
1439 sysdate,
1440 l_user_id,
1441 sysdate,
1442 l_user_id,
1443 l_conc_login_id,
1444 l_conc_program_application_id,
1445 l_conc_program_id,
1446 sysdate,
1447 l_conc_request_id,
1448 l_new_discount_line_id, /* new discount line id */
1449 l_new_discount_header_id,
1450 l_qp_pricing_attributes_rec.pricing_phase_id,
1451 l_qp_pricing_attributes_rec.qualification_ind,
1452 l_qp_pricing_attributes_rec.excluder_flag,
1453 l_qp_pricing_attributes_rec.accumulate_flag,
1454 l_qp_pricing_attributes_rec.product_attribute_context,
1455 l_qp_pricing_attributes_rec.product_attribute,
1456 l_qp_pricing_attributes_rec.product_attr_value,
1457 l_qp_pricing_attributes_rec.product_uom_code,
1458 l_qp_pricing_attributes_rec.pricing_attribute_context,
1459 l_qp_pricing_attributes_rec.pricing_attribute,
1460 l_qp_pricing_attributes_rec.pricing_attr_value_from,
1461 l_qp_pricing_attributes_rec.pricing_attr_value_to,
1462 l_qp_pricing_attributes_rec.attribute_grouping_no,
1463 l_qp_pricing_attributes_rec.context,
1464 l_qp_pricing_attributes_rec.attribute1,
1465 l_qp_pricing_attributes_rec.attribute2,
1466 l_qp_pricing_attributes_rec.attribute3,
1467 l_qp_pricing_attributes_rec.attribute4,
1468 l_qp_pricing_attributes_rec.attribute5,
1469 l_qp_pricing_attributes_rec.attribute6,
1470 l_qp_pricing_attributes_rec.attribute7,
1471 l_qp_pricing_attributes_rec.attribute8,
1472 l_qp_pricing_attributes_rec.attribute9,
1473 l_qp_pricing_attributes_rec.attribute10,
1474 l_qp_pricing_attributes_rec.attribute11,
1475 l_qp_pricing_attributes_rec.attribute12,
1476 l_qp_pricing_attributes_rec.attribute13,
1477 l_qp_pricing_attributes_rec.attribute14,
1478 l_qp_pricing_attributes_rec.attribute15,
1479 l_qp_pricing_attributes_rec.product_attribute_datatype,
1480 l_qp_pricing_attributes_rec.pricing_attribute_datatype,
1481 l_qp_pricing_attributes_rec.comparison_operator_code,
1482 l_pric_attr_value_from_number,
1483 l_pric_attr_value_to_number
1484 --ENH Upgrade BOAPI for orig_sys...ref RAVI
1485 --,to_char(l_new_pricing_attribute_id)
1486 --,(select l.ORIG_SYS_LINE_REF from qp_list_lines l where l.list_line_id=l_new_discount_line_id)
1487 --,(select h.ORIG_SYSTEM_HEADER_REF from qp_list_headers_b h where h.list_header_id=l_new_discount_header_id)
1488 );
1489
1490 END LOOP; /* For copying pricing attributes for each discount line */
1491 END IF;
1492 END LOOP; /* For copying discount lines*/
1493
1494 /* Copy qp_rltd_modifiers for the Price Break Parent list_lines chosen
1495 above which are stored in the mapping table */
1496
1497 IF l_mapping_tbl.COUNT > 0 THEN
1498 FOR l_count IN 1..l_mapping_tbl.COUNT
1499 LOOP
1500
1501 --IF l_mapping_tbl(l_count).list_line_type_code = 'PBH' THEN
1502 IF l_mapping_tbl(l_count).list_line_type_code in ('PBH','OID','PRG','CIE') THEN
1503
1504 FOR l_qp_rltd_modifiers_rec IN qp_rltd_modifiers_cur(
1505 l_mapping_tbl(l_count).old_list_line_id)
1506 LOOP
1507
1508 SELECT qp_rltd_modifiers_s.nextval
1509 INTO l_new_rltd_modifier_id
1510 FROM dual;
1511
1512 l_new_from_id := Get_New_Id(
1513 l_qp_rltd_modifiers_rec.from_rltd_modifier_id,
1514 l_mapping_tbl);
1515 l_new_to_id := Get_New_Id(
1516 l_qp_rltd_modifiers_rec.to_rltd_modifier_id,
1517 l_mapping_tbl);
1518 INSERT INTO qp_rltd_modifiers
1519 (creation_date,
1520 created_by,
1521 last_update_date,
1522 last_updated_by,
1523 last_update_login,
1524 context,
1525 attribute1,
1526 attribute2,
1527 attribute3,
1528 attribute4,
1529 attribute5,
1530 attribute6,
1531 attribute7,
1532 attribute8,
1533 attribute9,
1534 attribute10,
1535 attribute11,
1536 attribute12,
1537 attribute13,
1538 attribute14,
1539 attribute15,
1540 rltd_modifier_id,
1541 rltd_modifier_grp_no,
1542 from_rltd_modifier_id,
1543 to_rltd_modifier_id,
1544 rltd_modifier_grp_type
1545 )
1546 VALUES
1547 (sysdate,
1548 l_user_id,
1549 sysdate,
1550 l_user_id,
1551 l_conc_login_id,
1552 l_qp_rltd_modifiers_rec.context,
1553 l_qp_rltd_modifiers_rec.attribute1,
1554 l_qp_rltd_modifiers_rec.attribute2,
1555 l_qp_rltd_modifiers_rec.attribute3,
1556 l_qp_rltd_modifiers_rec.attribute4,
1557 l_qp_rltd_modifiers_rec.attribute5,
1558 l_qp_rltd_modifiers_rec.attribute6,
1559 l_qp_rltd_modifiers_rec.attribute7,
1560 l_qp_rltd_modifiers_rec.attribute8,
1561 l_qp_rltd_modifiers_rec.attribute9,
1562 l_qp_rltd_modifiers_rec.attribute10,
1563 l_qp_rltd_modifiers_rec.attribute11,
1564 l_qp_rltd_modifiers_rec.attribute12,
1565 l_qp_rltd_modifiers_rec.attribute13,
1566 l_qp_rltd_modifiers_rec.attribute14,
1567 l_qp_rltd_modifiers_rec.attribute15,
1568 l_new_rltd_modifier_id,
1569 l_qp_rltd_modifiers_rec.rltd_modifier_grp_no,
1570 l_new_from_id,
1571 l_new_to_id,
1572 l_qp_rltd_modifiers_rec.rltd_modifier_grp_type
1573 );
1574
1575 END LOOP; -- Loop through rltd modifiers records
1576 END IF; --For lines that are Parent Price Break lines
1577
1578 END LOOP; --Loop through l_mapping_tbl
1579 END IF; --If l_mapping_tbl has any records
1580
1581 END LOOP; /* for copying discount headers*/
1582 ----
1583 --Delete_Duplicate_Lines(p_effective_dates_flag, l_new_list_header_id);
1584 Delete_Duplicate_Lines(p_effective_dates_flag, l_new_discount_header_id); --for 2222562
1585
1586 /* This code will call the API to update the denormalized columns on QP_QUALIFIERS*/
1587 QP_MAINTAIN_DENORMALIZED_DATA.UPDATE_QUALIFIERS
1588 (ERR_BUFF => errbuf,
1589 RETCODE => retcode,
1590 --P_LIST_HEADER_ID => l_new_list_header_id);
1591 P_LIST_HEADER_ID => l_new_discount_header_id);
1592
1593
1594 if retcode = 2 then
1595 --error from update denormalized columns
1596 fnd_file.put_line(FND_FILE.LOG,'Error in Update of denormalized columns in QP_Qualifiers');
1597 else
1598 fnd_file.put_line(FND_FILE.LOG,'Update of denormalized columns in QP_Qualifiers completed successfully');
1599 end if;
1600
1601 --Upgrade Non-Continuous Price Breaks
1602
1603 IF (l_non_cont_pbh_id_tbl.COUNT > 0) and (nvl(FND_PROFILE.VALUE('QP_CONTINUOUS_PB'),'Y')= 'Y') THEN
1604 fnd_file.put_line(FND_FILE.LOG,'New List Name : '||p_new_price_list_name);
1605
1606 FOR i IN l_non_cont_pbh_id_tbl.FIRST..l_non_cont_pbh_id_tbl.LAST
1607 LOOP
1608
1609 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);
1610
1611 qp_delayed_requests_PVT.log_request
1612 ( p_entity_code => QP_GLOBALS.G_ENTITY_PRICING_ATTR
1613 , p_entity_id => l_non_cont_pbh_id_tbl(i).price_break_header_id
1614 , p_requesting_entity_code => QP_GLOBALS.G_ENTITY_PRICING_ATTR
1615 , p_requesting_entity_id => l_non_cont_pbh_id_tbl(i).price_break_header_id
1616 , p_request_type => QP_Globals.G_UPGRADE_PRICE_BREAKS
1617 , p_param1 => l_non_cont_pbh_id_tbl(i).list_line_no
1618 , p_param2 => l_non_cont_pbh_id_tbl(i).product_attribute
1619 , p_param3 => l_non_cont_pbh_id_tbl(i).product_attr_value
1620 , p_param4 => 'MODIFIER'
1621 , p_param5 => l_non_cont_pbh_id_tbl(i).start_date_active
1622 , p_param6 => l_non_cont_pbh_id_tbl(i).end_date_active
1623 , x_return_status => l_return_status);
1624
1625 END LOOP;
1626 QP_DELAYED_REQUESTS_PVT.Process_Request_for_Entity
1627 ( p_entity_code => QP_GLOBALS.G_ENTITY_PRICING_ATTR
1628 , x_return_status => l_return_status);
1629
1630 END IF;
1631
1632 fnd_file.put_line(FND_FILE.LOG,'Price list copy completed successfully');
1633
1634 IF QP_JAVA_ENGINE_UTIL_PUB.Java_Engine_Installed = 'Y' THEN
1635 select min(list_line_id), max(list_line_id)
1636 into l_min_list_line_id, l_max_list_line_id
1637 from qp_list_lines
1638 where list_header_id = l_new_discount_header_id;
1639
1640 QP_ATTR_GRP_PVT.Update_Qual_Segment_id(l_new_discount_header_id, null, -1, -1); -- Bug No 4331910
1641 QP_ATTR_GRP_PVT.Update_Qual_Segment_id(l_new_discount_header_id,
1642 null,
1643 l_min_list_line_id,
1644 l_max_list_line_id);
1645 QP_ATTR_GRP_PVT.Update_Prod_Pric_Segment_id(l_new_discount_header_id,
1646 l_min_list_line_id,l_max_list_line_id);
1647 QP_ATTR_GRP_PVT.generate_hp_atgrps(l_new_discount_header_id,null);
1648 QP_ATTR_GRP_PVT.generate_lp_atgrps(l_new_discount_header_id,null,
1649 l_min_list_line_id,l_max_list_line_id);
1650 QP_ATTR_GRP_PVT.update_pp_lines(l_new_discount_header_id,
1651 l_min_list_line_id,l_max_list_line_id);
1652 END IF;
1653 --- jagan PL/SQL pattern engine
1654 IF QP_JAVA_ENGINE_UTIL_PUB.Java_Engine_Installed = 'N' THEN
1655 IF FND_PROFILE.VALUE('QP_PATTERN_SEARCH') = 'M' OR FND_PROFILE.VALUE('QP_PATTERN_SEARCH') = 'B' THEN
1656 select min(list_line_id), max(list_line_id)
1657 into l_min_list_line_id, l_max_list_line_id
1658 from qp_list_lines
1659 where list_header_id = l_new_discount_header_id; --bug12784298
1660 QP_PS_ATTR_GRP_PVT.g_call_from_setup := 'Y'; --bug12784298
1661 QP_PS_ATTR_GRP_PVT.Update_Qual_Segment_id(l_new_discount_header_id,
1662 null,
1663 l_min_list_line_id,
1664 l_max_list_line_id);
1665 QP_PS_ATTR_GRP_PVT.Update_Prod_Pric_Segment_id(l_new_discount_header_id,
1666 l_min_list_line_id,l_max_list_line_id);
1667 QP_PS_ATTR_GRP_PVT.generate_hp_atgrps(l_new_discount_header_id,null);
1668 QP_PS_ATTR_GRP_PVT.generate_lp_atgrps(l_new_discount_header_id,null,
1669 l_min_list_line_id,l_max_list_line_id);
1670 QP_PS_ATTR_GRP_PVT.update_pp_lines(l_new_discount_header_id,
1671 l_min_list_line_id,l_max_list_line_id);
1672 END IF;
1673 END IF;
1674 commit;
1675
1676 errbuf := '';
1677 retcode := 0;
1678
1679
1680 EXCEPTION
1681
1682 WHEN OTHERS THEN
1683 fnd_file.put_line(FND_FILE.LOG,'Error in Copy Price list Routine ');
1684 fnd_file.put_line(FND_FILE.LOG,substr(sqlerrm,1,300));
1685 retcode := 2;
1686
1687 END Copy_Discounts;
1688
1689 END QP_COPY_MODIFIERS_PVT;