[Home] [Help]
PACKAGE BODY: APPS.QP_COPY_MODIFIERS_PVT
Source
1 PACKAGE BODY QP_COPY_MODIFIERS_PVT AS
2 /* $Header: QPXVCPMB.pls 120.14 2006/04/04 16:36:22 rnayani 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;
400 l_qp_status := QP_UTIL.get_qp_status;
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;
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,
560 list_type_code,
557 program_id,
558 program_update_date,
559 request_id,
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.
725 --
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);
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,
884 l_qual_attr_value_to_number,
881 l_active_flag,
882 l_list_type_code,
883 l_qual_attr_value_from_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,
1044 l_conc_request_id,
1041 l_conc_program_application_id,
1042 l_conc_program_id,
1043 sysdate,
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(l_qp_discount_lines_rec.list_line_type_code,'PBH','Y',
1127 l_qp_discount_lines_rec.continuous_price_break_flag) --Continuous Price Breaks
1128 --ENH Upgrade BOAPI for orig_sys...ref RAVI
1129 --,to_char(l_new_discount_line_id)
1130 --,(select h.ORIG_SYSTEM_HEADER_REF from qp_list_headers_b h where h.list_header_id=l_new_discount_header_id)
1131 );
1132
1133 /* Insert line qualifiers */
1134 If l_qp_discount_lines_rec.list_line_id is not null and
1135 l_qp_discount_lines_rec.list_line_id <> -1 then
1136 FOR l_qp_line_qualifiers_rec IN
1137 qp_line_qualifiers_cur(qp_from_discounts_rec.list_header_id,
1138 l_qp_discount_lines_rec.list_line_id)
1139 LOOP
1140
1141 --Get new qualifier_id
1142 SELECT qp_qualifiers_s.nextval
1143 INTO l_new_qualifier_id
1144 FROM dual;
1145
1146 /*
1147 IF l_qp_line_qualifiers_rec.qualifier_attr_value =
1148 TO_CHAR(p_from_list_header_id) AND
1149 l_qp_line_qualifiers_rec.qualifier_context = p_context AND
1150 l_qp_line_qualifiers_rec.qualifier_attribute = p_attribute
1151 THEN
1155 */
1152 l_qp_line_qualifiers_rec.qualifier_attr_value :=
1153 TO_CHAR(p_new_list_header_id);
1154 END IF;
1156
1157 BEGIN
1158
1159 SELECT ACTIVE_FLAG, LIST_TYPE_CODE
1160 INTO l_active_flag, l_list_type_code
1161 --fix for bug 4673872
1162 FROM QP_LIST_HEADERS_ALL_B
1163 WHERE LIST_HEADER_ID = l_new_discount_header_id;
1164
1165 EXCEPTION
1166 WHEN OTHERS THEN
1167 NULL;
1168 END;
1169
1170 IF l_qp_line_qualifiers_rec.qualifier_datatype = 'N'
1171 then
1172
1173 BEGIN
1174
1175 l_qual_attr_value_from_number :=
1176 qp_number.canonical_to_number(l_qp_line_qualifiers_rec.qualifier_attr_value);
1177
1178 l_qual_attr_value_to_number :=
1179 qp_number.canonical_to_number(l_qp_line_qualifiers_rec.qualifier_attr_value_to);
1180
1181 EXCEPTION
1182 WHEN VALUE_ERROR THEN
1183 NULL;
1184 WHEN OTHERS THEN
1185 NULL;
1186 END;
1187
1188 end if;
1189
1190 --Insert new qualifier
1191 INSERT INTO qp_qualifiers
1192 (
1193 qualifier_id,
1194 creation_date,
1195 created_by,
1196 last_update_date,
1197 last_updated_by,
1198 last_update_login,
1199 program_application_id,
1200 program_id,
1201 program_update_date,
1202 request_id,
1203 excluder_flag,
1204 comparison_operator_code,
1205 qualifier_context,
1206 qualifier_attribute,
1207 context,
1208 attribute1,
1209 attribute2,
1210 attribute3,
1211 attribute4,
1212 attribute5,
1213 attribute6,
1214 attribute7,
1215 attribute8,
1216 attribute9,
1217 attribute10,
1218 attribute11,
1219 attribute12,
1220 attribute13,
1221 attribute14,
1222 attribute15,
1223 qualifier_rule_id,
1224 qualifier_grouping_no,
1225 qualifier_attr_value,
1226 list_header_id,
1227 list_line_id,
1228 created_from_rule_id,
1229 start_date_active,
1230 end_date_active,
1231 qualifier_precedence,
1232 qualifier_datatype,
1233 qualifier_attr_value_to,
1234 active_flag,
1235 list_type_code,
1236 qual_attr_value_from_number,
1237 qual_attr_value_to_number,
1238 search_ind,
1239 distinct_row_count,
1240 qualifier_group_cnt,
1241 header_quals_exist_flag,
1242 qualify_hier_descendents_flag -- Added for TCA
1243 --ENH Upgrade BOAPI for orig_sys...ref RAVI
1244 --,ORIG_SYS_QUALIFIER_REF
1245 --,ORIG_SYS_LINE_REF
1246 --,ORIG_SYS_HEADER_REF
1247 )
1248 VALUES
1249 (
1250 l_new_qualifier_id,
1251 sysdate,
1252 l_user_id,
1253 sysdate,
1254 l_user_id,
1255 l_conc_login_id,
1256 l_conc_program_application_id,
1257 l_conc_program_id,
1258 sysdate,
1259 l_conc_request_id,
1260 l_qp_line_qualifiers_rec.excluder_flag,
1261 l_qp_line_qualifiers_rec.comparison_operator_code,
1262 l_qp_line_qualifiers_rec.qualifier_context,
1263 l_qp_line_qualifiers_rec.qualifier_attribute,
1264 l_qp_line_qualifiers_rec.context,
1265 l_qp_line_qualifiers_rec.attribute1,
1266 l_qp_line_qualifiers_rec.attribute2,
1267 l_qp_line_qualifiers_rec.attribute3,
1268 l_qp_line_qualifiers_rec.attribute4,
1269 l_qp_line_qualifiers_rec.attribute5,
1270 l_qp_line_qualifiers_rec.attribute6,
1271 l_qp_line_qualifiers_rec.attribute7,
1272 l_qp_line_qualifiers_rec.attribute8,
1273 l_qp_line_qualifiers_rec.attribute9,
1274 l_qp_line_qualifiers_rec.attribute10,
1275 l_qp_line_qualifiers_rec.attribute11,
1276 l_qp_line_qualifiers_rec.attribute12,
1277 l_qp_line_qualifiers_rec.attribute13,
1278 l_qp_line_qualifiers_rec.attribute14,
1279 l_qp_line_qualifiers_rec.attribute15,
1280 l_qp_line_qualifiers_rec.qualifier_rule_id,
1281 l_qp_line_qualifiers_rec.qualifier_grouping_no,
1282 l_qp_line_qualifiers_rec.qualifier_attr_value,
1283 l_new_discount_header_id,
1284 l_new_discount_line_id,
1285 --l_qp_line_qualifiers_rec.list_line_id,
1286 l_qp_line_qualifiers_rec.created_from_rule_id,
1287 l_qp_line_qualifiers_rec.start_date_active,
1288 l_qp_line_qualifiers_rec.end_date_active,
1289 l_qp_line_qualifiers_rec.qualifier_precedence,
1290 l_qp_line_qualifiers_rec.qualifier_datatype,
1291 l_qp_line_qualifiers_rec.qualifier_attr_value_to,
1292 l_active_flag,
1293 l_list_type_code,
1294 l_qual_attr_value_from_number,
1295 l_qual_attr_value_to_number,
1296 l_qp_line_qualifiers_rec.search_ind,
1297 l_qp_line_qualifiers_rec.distinct_row_count,
1298 l_qp_line_qualifiers_rec.qualifier_group_cnt,
1299 l_qp_line_qualifiers_rec.header_quals_exist_flag,
1300 l_qp_line_qualifiers_rec.qualify_hier_descendents_flag -- Added for TCA
1301 --ENH Upgrade BOAPI for orig_sys...ref RAVI
1302 --,to_char(l_new_qualifier_id)
1303 --,(select l.ORIG_SYS_LINE_REF from qp_list_lines l where l.list_line_id=l_new_discount_line_id)
1304 --,(select h.ORIG_SYSTEM_HEADER_REF from qp_list_headers_b h where h.list_header_id=l_new_discount_header_id)
1305 );
1309 /* For copying Line qualifiers */
1306
1307 END LOOP;
1308 end if;
1310
1311 /*If the discount_line_rec is a Price Break Parent Line or Price Break Line
1312 then store the old and new discountlineid in a mapping-array for later use*/
1313
1314 ---IF l_qp_discount_lines_rec.list_line_type_code = 'PBH' OR
1315 IF l_qp_discount_lines_rec.list_line_type_code in ('PBH','OID','PRG','CIE') OR
1316 Price_Break_Line(l_qp_discount_lines_rec.list_line_id)
1317 THEN
1318 l_count := l_count + 1;
1319 l_mapping_tbl(l_count).list_line_type_code :=
1320 l_qp_discount_lines_rec.list_line_type_code;
1321 l_mapping_tbl(l_count).old_list_line_id :=
1322 l_qp_discount_lines_rec.list_line_id;
1323 l_mapping_tbl(l_count).new_list_line_id := l_new_discount_line_id;
1324 END IF;
1325
1326 IF l_qp_discount_lines_rec.list_line_type_code = 'PBH' AND
1327 (l_qp_discount_lines_rec.continuous_price_break_flag IS NULL OR l_qp_discount_lines_rec.continuous_price_break_flag <> 'Y')
1328 THEN
1329 l_non_cont_count := l_non_cont_count + 1;
1330 l_non_cont_pbh_id_tbl(l_non_cont_count).price_break_header_id := l_new_discount_line_id;
1331 l_non_cont_pbh_id_tbl(l_non_cont_count).list_line_no := l_qp_discount_lines_rec.list_line_no;
1332
1333 IF p_effective_dates_flag = 'Y' THEN
1334 l_non_cont_pbh_id_tbl(l_non_cont_count).start_date_active := l_qp_discount_lines_rec.start_date_active;
1335 l_non_cont_pbh_id_tbl(l_non_cont_count).end_date_active := l_qp_discount_lines_rec.end_date_active;
1336 END IF;
1337
1338 END IF;
1339
1340
1341 /* Copy the qp_pricing_attributes records for each discount line being
1342 copied */
1343
1344 FOR l_qp_pricing_attributes_rec IN qp_pricing_attributes_cur
1345 (l_qp_discount_lines_rec.list_line_id)
1346 -- basically the from_discount_list_line_id
1347 LOOP
1348
1349 -- Get next pricing_attribute_id
1350 SELECT qp_pricing_attributes_s.nextval
1351 INTO l_new_pricing_attribute_id
1352 FROM dual;
1353
1354 IF l_qp_discount_lines_rec.list_line_type_code = 'PBH' AND
1355 (l_qp_discount_lines_rec.continuous_price_break_flag IS NULL OR l_qp_discount_lines_rec.continuous_price_break_flag <> 'Y')
1356 AND l_qp_pricing_attributes_rec.list_line_id = l_qp_discount_lines_rec.list_line_id
1357 THEN
1358 l_non_cont_pbh_id_tbl(l_non_cont_count).product_attribute :=
1359 l_qp_pricing_attributes_rec.product_attribute;
1360 l_non_cont_pbh_id_tbl(l_non_cont_count).product_attr_value:=
1361 l_qp_pricing_attributes_rec.product_attr_value;
1362 END IF;
1363
1364
1365
1366 IF l_qp_pricing_attributes_rec.pricing_attribute_datatype = 'N'
1367 then
1368
1369 BEGIN
1370
1371 l_pric_attr_value_from_number :=
1372 qp_number.canonical_to_number(l_qp_pricing_attributes_rec.pricing_attr_value_from);
1373
1374 l_pric_attr_value_to_number :=
1375 qp_number.canonical_to_number(l_qp_pricing_attributes_rec.pricing_attr_value_to);
1376
1377 EXCEPTION
1378 WHEN VALUE_ERROR THEN
1379 NULL;
1380 WHEN OTHERS THEN
1381 NULL;
1382 END;
1383
1384 end if;
1385
1386 INSERT INTO qp_pricing_attributes
1387 (pricing_attribute_id,
1388 creation_date,
1389 created_by,
1390 last_update_date,
1391 last_updated_by,
1392 last_update_login,
1393 program_application_id,
1394 program_id,
1395 program_update_date,
1396 request_id,
1397 list_line_id,
1398 list_header_id,
1399 pricing_phase_id,
1400 qualification_ind,
1401 excluder_flag,
1402 accumulate_flag,
1403 product_attribute_context,
1404 product_attribute,
1405 product_attr_value,
1406 product_uom_code,
1407 pricing_attribute_context,
1408 pricing_attribute,
1409 pricing_attr_value_from,
1410 pricing_attr_value_to,
1411 attribute_grouping_no,
1412 context,
1413 attribute1,
1414 attribute2,
1415 attribute3,
1416 attribute4,
1417 attribute5,
1418 attribute6,
1419 attribute7,
1420 attribute8,
1421 attribute9,
1422 attribute10,
1423 attribute11,
1424 attribute12,
1425 attribute13,
1426 attribute14,
1427 attribute15,
1428 product_attribute_datatype,
1429 pricing_attribute_datatype,
1430 comparison_operator_code,
1431 pricing_attr_value_from_number,
1432 pricing_attr_value_to_number
1433 --ENH Upgrade BOAPI for orig_sys...ref RAVI
1434 --,ORIG_SYS_PRICING_ATTR_REF
1435 --,ORIG_SYS_LINE_REF
1436 --,ORIG_SYS_HEADER_REF
1437 )
1438 VALUES
1439 (l_new_pricing_attribute_id,
1440 sysdate,
1441 l_user_id,
1442 sysdate,
1443 l_user_id,
1444 l_conc_login_id,
1445 l_conc_program_application_id,
1446 l_conc_program_id,
1447 sysdate,
1448 l_conc_request_id,
1449 l_new_discount_line_id, /* new discount line id */
1450 l_new_discount_header_id,
1451 l_qp_pricing_attributes_rec.pricing_phase_id,
1452 l_qp_pricing_attributes_rec.qualification_ind,
1453 l_qp_pricing_attributes_rec.excluder_flag,
1454 l_qp_pricing_attributes_rec.accumulate_flag,
1455 l_qp_pricing_attributes_rec.product_attribute_context,
1456 l_qp_pricing_attributes_rec.product_attribute,
1457 l_qp_pricing_attributes_rec.product_attr_value,
1458 l_qp_pricing_attributes_rec.product_uom_code,
1459 l_qp_pricing_attributes_rec.pricing_attribute_context,
1460 l_qp_pricing_attributes_rec.pricing_attribute,
1461 l_qp_pricing_attributes_rec.pricing_attr_value_from,
1462 l_qp_pricing_attributes_rec.pricing_attr_value_to,
1463 l_qp_pricing_attributes_rec.attribute_grouping_no,
1464 l_qp_pricing_attributes_rec.context,
1465 l_qp_pricing_attributes_rec.attribute1,
1466 l_qp_pricing_attributes_rec.attribute2,
1467 l_qp_pricing_attributes_rec.attribute3,
1468 l_qp_pricing_attributes_rec.attribute4,
1469 l_qp_pricing_attributes_rec.attribute5,
1470 l_qp_pricing_attributes_rec.attribute6,
1471 l_qp_pricing_attributes_rec.attribute7,
1472 l_qp_pricing_attributes_rec.attribute8,
1473 l_qp_pricing_attributes_rec.attribute9,
1474 l_qp_pricing_attributes_rec.attribute10,
1475 l_qp_pricing_attributes_rec.attribute11,
1476 l_qp_pricing_attributes_rec.attribute12,
1477 l_qp_pricing_attributes_rec.attribute13,
1478 l_qp_pricing_attributes_rec.attribute14,
1479 l_qp_pricing_attributes_rec.attribute15,
1480 l_qp_pricing_attributes_rec.product_attribute_datatype,
1481 l_qp_pricing_attributes_rec.pricing_attribute_datatype,
1482 l_qp_pricing_attributes_rec.comparison_operator_code,
1483 l_pric_attr_value_from_number,
1484 l_pric_attr_value_to_number
1485 --ENH Upgrade BOAPI for orig_sys...ref RAVI
1486 --,to_char(l_new_pricing_attribute_id)
1487 --,(select l.ORIG_SYS_LINE_REF from qp_list_lines l where l.list_line_id=l_new_discount_line_id)
1488 --,(select h.ORIG_SYSTEM_HEADER_REF from qp_list_headers_b h where h.list_header_id=l_new_discount_header_id)
1489 );
1490
1491 END LOOP; /* For copying pricing attributes for each discount line */
1492 END IF;
1493 END LOOP; /* For copying discount lines*/
1494
1495 /* Copy qp_rltd_modifiers for the Price Break Parent list_lines chosen
1496 above which are stored in the mapping table */
1497
1498 IF l_mapping_tbl.COUNT > 0 THEN
1499 FOR l_count IN 1..l_mapping_tbl.COUNT
1500 LOOP
1501
1502 --IF l_mapping_tbl(l_count).list_line_type_code = 'PBH' THEN
1503 IF l_mapping_tbl(l_count).list_line_type_code in ('PBH','OID','PRG','CIE') THEN
1504
1505 FOR l_qp_rltd_modifiers_rec IN qp_rltd_modifiers_cur(
1506 l_mapping_tbl(l_count).old_list_line_id)
1507 LOOP
1508
1509 SELECT qp_rltd_modifiers_s.nextval
1510 INTO l_new_rltd_modifier_id
1511 FROM dual;
1512
1513 l_new_from_id := Get_New_Id(
1514 l_qp_rltd_modifiers_rec.from_rltd_modifier_id,
1515 l_mapping_tbl);
1516 l_new_to_id := Get_New_Id(
1517 l_qp_rltd_modifiers_rec.to_rltd_modifier_id,
1518 l_mapping_tbl);
1519 INSERT INTO qp_rltd_modifiers
1520 (creation_date,
1521 created_by,
1522 last_update_date,
1523 last_updated_by,
1524 last_update_login,
1525 context,
1526 attribute1,
1527 attribute2,
1528 attribute3,
1529 attribute4,
1530 attribute5,
1531 attribute6,
1532 attribute7,
1533 attribute8,
1534 attribute9,
1535 attribute10,
1536 attribute11,
1537 attribute12,
1538 attribute13,
1539 attribute14,
1540 attribute15,
1541 rltd_modifier_id,
1542 rltd_modifier_grp_no,
1543 from_rltd_modifier_id,
1544 to_rltd_modifier_id,
1545 rltd_modifier_grp_type
1546 )
1547 VALUES
1548 (sysdate,
1549 l_user_id,
1550 sysdate,
1551 l_user_id,
1552 l_conc_login_id,
1553 l_qp_rltd_modifiers_rec.context,
1554 l_qp_rltd_modifiers_rec.attribute1,
1555 l_qp_rltd_modifiers_rec.attribute2,
1556 l_qp_rltd_modifiers_rec.attribute3,
1557 l_qp_rltd_modifiers_rec.attribute4,
1558 l_qp_rltd_modifiers_rec.attribute5,
1559 l_qp_rltd_modifiers_rec.attribute6,
1560 l_qp_rltd_modifiers_rec.attribute7,
1561 l_qp_rltd_modifiers_rec.attribute8,
1562 l_qp_rltd_modifiers_rec.attribute9,
1563 l_qp_rltd_modifiers_rec.attribute10,
1564 l_qp_rltd_modifiers_rec.attribute11,
1565 l_qp_rltd_modifiers_rec.attribute12,
1566 l_qp_rltd_modifiers_rec.attribute13,
1567 l_qp_rltd_modifiers_rec.attribute14,
1568 l_qp_rltd_modifiers_rec.attribute15,
1569 l_new_rltd_modifier_id,
1570 l_qp_rltd_modifiers_rec.rltd_modifier_grp_no,
1571 l_new_from_id,
1572 l_new_to_id,
1573 l_qp_rltd_modifiers_rec.rltd_modifier_grp_type
1574 );
1575
1576 END LOOP; -- Loop through rltd modifiers records
1577 END IF; --For lines that are Parent Price Break lines
1578
1579 END LOOP; --Loop through l_mapping_tbl
1580 END IF; --If l_mapping_tbl has any records
1581
1582 END LOOP; /* for copying discount headers*/
1583 ----
1584 --Delete_Duplicate_Lines(p_effective_dates_flag, l_new_list_header_id);
1585 Delete_Duplicate_Lines(p_effective_dates_flag, l_new_discount_header_id); --for 2222562
1586
1587 /* This code will call the API to update the denormalized columns on QP_QUALIFIERS*/
1588 QP_MAINTAIN_DENORMALIZED_DATA.UPDATE_QUALIFIERS
1589 (ERR_BUFF => errbuf,
1590 RETCODE => retcode,
1591 --P_LIST_HEADER_ID => l_new_list_header_id);
1592 P_LIST_HEADER_ID => l_new_discount_header_id);
1593
1594
1595 if retcode = 2 then
1596 --error from update denormalized columns
1597 fnd_file.put_line(FND_FILE.LOG,'Error in Update of denormalized columns in QP_Qualifiers');
1598 else
1599 fnd_file.put_line(FND_FILE.LOG,'Update of denormalized columns in QP_Qualifiers completed successfully');
1600 end if;
1601
1602 --Upgrade Non-Continuous Price Breaks
1603
1604 IF l_non_cont_pbh_id_tbl.COUNT > 0 THEN
1605 fnd_file.put_line(FND_FILE.LOG,'New List Name : '||p_new_price_list_name);
1606
1607 FOR i IN l_non_cont_pbh_id_tbl.FIRST..l_non_cont_pbh_id_tbl.LAST
1608 LOOP
1609
1610 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);
1611
1612 qp_delayed_requests_PVT.log_request
1613 ( p_entity_code => QP_GLOBALS.G_ENTITY_PRICING_ATTR
1614 , p_entity_id => l_non_cont_pbh_id_tbl(i).price_break_header_id
1615 , p_requesting_entity_code => QP_GLOBALS.G_ENTITY_PRICING_ATTR
1616 , p_requesting_entity_id => l_non_cont_pbh_id_tbl(i).price_break_header_id
1617 , p_request_type => QP_Globals.G_UPGRADE_PRICE_BREAKS
1618 , p_param1 => l_non_cont_pbh_id_tbl(i).list_line_no
1619 , p_param2 => l_non_cont_pbh_id_tbl(i).product_attribute
1620 , p_param3 => l_non_cont_pbh_id_tbl(i).product_attr_value
1621 , p_param4 => 'MODIFIER'
1622 , p_param5 => l_non_cont_pbh_id_tbl(i).start_date_active
1623 , p_param6 => l_non_cont_pbh_id_tbl(i).end_date_active
1624 , x_return_status => l_return_status);
1625
1626 END LOOP;
1627 QP_DELAYED_REQUESTS_PVT.Process_Request_for_Entity
1628 ( p_entity_code => QP_GLOBALS.G_ENTITY_PRICING_ATTR
1629 , x_return_status => l_return_status);
1630
1631 END IF;
1632
1633 fnd_file.put_line(FND_FILE.LOG,'Price list copy completed successfully');
1634
1635 IF QP_JAVA_ENGINE_UTIL_PUB.Java_Engine_Installed = 'Y' THEN
1636 select min(list_line_id), max(list_line_id)
1637 into l_min_list_line_id, l_max_list_line_id
1638 from qp_list_lines
1639 where list_header_id = l_new_discount_header_id;
1640
1641 QP_ATTR_GRP_PVT.Update_Qual_Segment_id(l_new_discount_header_id, null, -1, -1); -- Bug No 4331910
1642 QP_ATTR_GRP_PVT.Update_Qual_Segment_id(l_new_discount_header_id,
1643 null,
1644 l_min_list_line_id,
1645 l_max_list_line_id);
1646 QP_ATTR_GRP_PVT.Update_Prod_Pric_Segment_id(l_new_discount_header_id,
1647 l_min_list_line_id,l_max_list_line_id);
1648 QP_ATTR_GRP_PVT.generate_hp_atgrps(l_new_discount_header_id,null);
1649 QP_ATTR_GRP_PVT.generate_lp_atgrps(l_new_discount_header_id,null,
1650 l_min_list_line_id,l_max_list_line_id);
1651 QP_ATTR_GRP_PVT.update_pp_lines(l_new_discount_header_id,
1652 l_min_list_line_id,l_max_list_line_id);
1653 END IF;
1654
1655 commit;
1656
1657 errbuf := '';
1658 retcode := 0;
1659
1660
1661 EXCEPTION
1662
1663 WHEN OTHERS THEN
1664 fnd_file.put_line(FND_FILE.LOG,'Error in Copy Price list Routine ');
1665 fnd_file.put_line(FND_FILE.LOG,substr(sqlerrm,1,300));
1666 retcode := 2;
1667
1668 END Copy_Discounts;
1669
1670 END QP_COPY_MODIFIERS_PVT;