[Home] [Help]
PACKAGE BODY: APPS.QP_ARCHIVE_ENTITY_PVT
Source
1 PACKAGE BODY QP_ARCHIVE_ENTITY_PVT AS
2 /* $Header: QPXARCVB.pls 120.5 2011/11/23 11:16:13 jputta ship $ */
3
4 --GLOBAL Constant holding the package name
5
6 --G_PKG_NAME CONSTANT VARCHAR2(30):='QP_ARCHIVE_ENTITY_PVT';
7 g_count_header_b NUMBER := 0;
8 g_count_header_tl NUMBER := 0;
9 g_count_pricing_att NUMBER := 0;
10 g_count_qualifier NUMBER := 0;
11 g_count_rldt NUMBER := 0;
12 g_count_list_line NUMBER := 0;
13
14
15 /***********************************************************************
16 * Function to check if a list_line is a benefit line for a coupon line *
17 ************************************************************************/
18
19 FUNCTION LINE_EXISTS_IN_RLTD(p_list_line_id IN NUMBER)
20 RETURN BOOLEAN
21 IS
22
23 l_return BOOLEAN := FALSE;
24 l_to_rltd_modifier_id NUMBER;
25
26 CURSOR coupon_line_cur(a_list_line_id NUMBER)
27 IS
28 SELECT to_rltd_modifier_id
29 FROM qp_rltd_modifiers
30 WHERE to_rltd_modifier_id = a_list_line_id
31 AND rltd_modifier_grp_type = 'COUPON';
32
33 BEGIN
34
35 OPEN coupon_line_cur(p_list_line_id);
36 FETCH coupon_line_cur
37 INTO l_to_rltd_modifier_id;
38
39 IF coupon_line_cur%FOUND THEN
40 l_return := TRUE;
41 ELSE
42 l_return := FALSE;
43 END IF;
44
45 CLOSE coupon_line_cur;
46
47 RETURN l_return;
48
49 END LINE_EXISTS_IN_RLTD;
50
51 /***************************************************************
52 * Procedure to insert records into the criteria tables *
53 ****************************************************************/
54
55 Procedure INSERT_CRITERIA
56 (
57 p_archive_name VARCHAR2,
58 p_entity_type VARCHAR2,
59 p_source_system_code VARCHAR2,
60 p_entity NUMBER,
61 p_all_lines VARCHAR2,
62 p_product_context VARCHAR2,
63 p_product_attribute VARCHAR2,
64 p_product_attr_value_from VARCHAR2,
65 p_product_attr_value_to VARCHAR2,
66 p_start_date_active VARCHAR2,
67 p_end_date_active VARCHAR2,
68 p_creation_date VARCHAR2,
69 p_created_by NUMBER,
70 p_user_id NUMBER,
71 p_conc_request_id NUMBER,
72 p_result_status VARCHAR2
73 )
74 IS
75 BEGIN
76
77 insert into QP_ARCH_CRITERIA_HEADERS
78 (REQUEST_ID,
79 REQUEST_NAME,
80 REQUEST_TYPE,
81 SOURCE_SYSTEM,
82 CREATION_DATE,
83 CREATED_BY,
84 REQUEST_STATUS,
85 purge_flag)
86 values
87 (p_conc_request_id,
88 p_archive_name,
89 'ARCHIVE',
90 p_source_system_code,
91 sysdate,
92 p_user_id,
93 p_result_status,
94 'N');
95
96 insert into QP_ARCH_CRITERIA_LINES
97 (request_id,
98 parameter_name,
99 parameter_value)
100 values
101 (p_conc_request_id,
102 'ENTITY_TYPE',
103 p_entity_type);
104
105 insert into QP_ARCH_CRITERIA_LINES
106 (request_id,
107 parameter_name,
108 parameter_value)
109 values
110 (p_conc_request_id,
111 'ENTITY',
112 p_entity);
113
114 IF nvl(p_all_lines,'N') = 'Y' THEN
115
116 insert into QP_ARCH_CRITERIA_LINES
117 (request_id,
118 parameter_name,
119 parameter_value)
120 values
121 (p_conc_request_id,
122 'ALL_LINES',
123 p_all_lines);
124
125
126 ELSE --All Lines not checked
127
128 IF p_product_context is not null THEN
129
130 insert into QP_ARCH_CRITERIA_LINES
131 (request_id,
132 parameter_name,
133 parameter_value)
134 values
135 (p_conc_request_id,
136 'PRODUCT_CONTEXT',
137 p_product_context);
138
139 END IF;
140
141 IF p_product_attribute is not null THEN
142
143 insert into QP_ARCH_CRITERIA_LINES
144 (request_id,
145 parameter_name,
146 parameter_value)
147 values
148 (p_conc_request_id,
149 'PRODUCT_ATTRIBUTE',
150 p_product_attribute);
151
152 END IF;
153
154 IF p_product_attr_value_from is not null THEN
155
156 insert into QP_ARCH_CRITERIA_LINES
157 (request_id,
158 parameter_name,
159 parameter_value)
160 values
161 (p_conc_request_id,
162 'PRODUCT_ATTR_VALUE_FROM',
163 p_product_attr_value_from);
164
165 END IF;
166
167 IF p_product_attr_value_to is not null THEN
168
169 insert into QP_ARCH_CRITERIA_LINES
170 (request_id,
171 parameter_name,
172 parameter_value)
173 values
174 (p_conc_request_id,
175 'PRODUCT_ATTR_VALUE_TO',
176 p_product_attr_value_to);
177
178 END IF;
179
180 IF p_start_date_active is not null THEN
181
182 insert into QP_ARCH_CRITERIA_LINES
183 (request_id,
184 parameter_name,
185 parameter_value)
186 values
187 (p_conc_request_id,
188 'START_DATE_ACTIVE',
189 fnd_date.canonical_to_date(p_start_date_active));
190
191 END IF;
195 insert into QP_ARCH_CRITERIA_LINES
192
193 IF p_end_date_active is not null THEN
194
196 (request_id,
197 parameter_name,
198 parameter_value)
199 values
200 (p_conc_request_id,
201 'END_DATE_ACTIVE',
202 fnd_date.canonical_to_date(p_end_date_active));
203
204 END IF;
205
206 IF p_created_by is not null THEN
207
208 insert into QP_ARCH_CRITERIA_LINES
209 (request_id,
210 parameter_name,
211 parameter_value)
212 values
213 (p_conc_request_id,
214 'CREATED_BY',
215 p_created_by);
216
217 END IF;
218
219 IF p_creation_date is not null THEN
220
221 insert into QP_ARCH_CRITERIA_LINES
222 (request_id,
223 parameter_name,
224 parameter_value)
225 values
226 (p_conc_request_id,
227 'CREATION_DATE',
228 fnd_date.canonical_to_date(p_creation_date));
229
230 END IF;
231
232 END IF; -- all lines check
233
234 END INSERT_CRITERIA;
235
236
237 /**********************************************************************
238 * Procedure to delete the non endated child lines of PBH/PRG/OID
239 parent lines because these are not selected by the archive criteria
240 if the end date is specified in the archive criteria *
241 ***********************************************************************/
242
243
244 Procedure DELETE_CHILD
245 (
246 p_to_rltd_modifier_id NUMBER,
247 p_conc_request_id NUMBER
248 )
249 IS
250
251 l_pric_attr_value_from_number NUMBER := NULL;
252 l_pric_attr_value_to_number NUMBER := NULL;
253
254
255 CURSOR qp_pricing_attributes_cur(p_from_list_line_id NUMBER)
256 IS
257 SELECT *
258 FROM qp_pricing_attributes
259 WHERE list_line_id = p_from_list_line_id;
260
261 BEGIN
262
263 --Insert into QP_ARCH_LIST_LINES
264
265 INSERT INTO QP_ARCH_LIST_LINES
266 (
267 list_line_id,
268 creation_date,
269 created_by,
270 last_update_date,
271 last_updated_by,
272 last_update_login,
273 program_application_id,
274 program_id,
275 program_update_date,
276 request_id,
277 list_header_id,
278 list_line_type_code,
279 start_date_active,
280 end_date_active,
281 automatic_flag,
282 modifier_level_code,
283 list_price,
284 primary_uom_flag,
285 inventory_item_id,
286 organization_id,
287 related_item_id,
288 relationship_type_id,
289 substitution_context,
290 substitution_attribute,
291 substitution_value,
292 revision,
293 revision_date,
294 revision_reason_code,
295 context,
296 attribute1,
297 attribute2,
298 comments,
299 attribute3,
300 attribute4,
301 attribute5,
302 attribute6,
303 attribute7,
304 attribute8,
305 attribute9,
306 attribute10,
307 attribute11,
308 attribute12,
309 attribute13,
310 attribute14,
311 attribute15,
312 price_break_type_code,
313 percent_price,
314 price_by_formula_id,
315 number_effective_periods,
316 effective_period_uom,
317 arithmetic_operator,
318 operand,
319 override_flag,
320 print_on_invoice_flag,
321 rebate_transaction_type_code,
322 estim_accrual_rate,
323 generate_using_formula_id,
324 reprice_flag,
325 accrual_flag,
326 pricing_group_sequence,
327 incompatibility_grp_code,
328 list_line_no,
329 product_precedence,
330 pricing_phase_id,
331 expiration_period_start_date,
332 number_expiration_periods,
333 expiration_period_uom,
334 expiration_date,
335 estim_gl_value,
336 accrual_conversion_rate,
337 benefit_price_list_line_id,
338 proration_type_code,
339 benefit_qty,
340 benefit_uom_code,
341 charge_type_code,
342 charge_subtype_code,
343 benefit_limit,
344 include_on_returns_flag,
345 qualification_ind,
346 recurring_value,
347 LIST_PRICE_UOM_CODE,
348 BASE_QTY,
349 BASE_UOM_CODE,
350 ACCRUAL_QTY,
351 ACCRUAL_UOM_CODE,
352 RECURRING_FLAG,
353 LIMIT_EXISTS_FLAG,
354 GROUP_COUNT,
355 NET_AMOUNT_FLAG,
356 CUSTOMER_ITEM_ID
357 ,ACCUM_CONTEXT
358 ,ACCUM_ATTRIBUTE
359 ,ACCUM_ATTR_RUN_SRC_FLAG
360 ,BREAK_UOM_CODE
361 ,BREAK_UOM_CONTEXT
362 ,BREAK_UOM_ATTRIBUTE
363 ,PATTERN_ID
364 ,PRODUCT_UOM_CODE
365 ,PRICING_ATTRIBUTE_COUNT
366 ,HASH_KEY
367 ,CACHE_KEY
368 ,ARCH_PURG_REQUEST_ID
369 )
370 SELECT
371 list_line_id,
372 creation_date,
373 created_by,
374 last_update_date,
375 last_updated_by,
376 last_update_login,
377 program_application_id,
378 program_id,
379 program_update_date,
380 request_id,
381 list_header_id,
382 list_line_type_code,
383 start_date_active,
384 end_date_active,
385 automatic_flag,
389 inventory_item_id,
386 modifier_level_code,
387 list_price,
388 primary_uom_flag,
390 organization_id,
391 related_item_id,
392 relationship_type_id,
393 substitution_context,
394 substitution_attribute,
395 substitution_value,
396 revision,
397 revision_date,
398 revision_reason_code,
399 context,
400 attribute1,
401 attribute2,
402 comments,
403 attribute3,
404 attribute4,
405 attribute5,
406 attribute6,
407 attribute7,
408 attribute8,
409 attribute9,
410 attribute10,
411 attribute11,
412 attribute12,
413 attribute13,
414 attribute14,
415 attribute15,
416 price_break_type_code,
417 percent_price,
418 price_by_formula_id,
419 number_effective_periods,
420 effective_period_uom,
421 arithmetic_operator,
422 operand,
423 override_flag,
424 print_on_invoice_flag,
425 rebate_transaction_type_code,
426 estim_accrual_rate,
427 generate_using_formula_id,
428 reprice_flag,
429 accrual_flag,
430 pricing_group_sequence,
431 incompatibility_grp_code,
432 list_line_no,
433 product_precedence,
434 pricing_phase_id,
435 expiration_period_start_date,
436 number_expiration_periods,
437 expiration_period_uom,
438 expiration_date,
439 estim_gl_value,
440 accrual_conversion_rate,
441 benefit_price_list_line_id,
442 proration_type_code,
443 benefit_qty,
444 benefit_uom_code,
445 charge_type_code,
446 charge_subtype_code,
447 benefit_limit,
448 include_on_returns_flag,
449 qualification_ind,
450 recurring_value,
451 LIST_PRICE_UOM_CODE,
452 BASE_QTY,
453 BASE_UOM_CODE,
454 ACCRUAL_QTY,
455 ACCRUAL_UOM_CODE,
456 RECURRING_FLAG,
457 LIMIT_EXISTS_FLAG,
458 GROUP_COUNT,
459 NET_AMOUNT_FLAG,
460 CUSTOMER_ITEM_ID
461 ,ACCUM_CONTEXT
462 ,ACCUM_ATTRIBUTE
463 ,ACCUM_ATTR_RUN_SRC_FLAG
464 ,BREAK_UOM_CODE
465 ,BREAK_UOM_CONTEXT
466 ,BREAK_UOM_ATTRIBUTE
467 ,PATTERN_ID
468 ,PRODUCT_UOM_CODE
469 ,PRICING_ATTRIBUTE_COUNT
470 ,HASH_KEY
471 ,CACHE_KEY
472 ,p_conc_request_id
473 FROM QP_LIST_LINES
474 WHERE list_line_id = p_to_rltd_modifier_id;
475 g_count_list_line:=g_count_list_line + sql%rowcount;
476
477 --Delete from QP_LIST_LINES
478
479 DELETE FROM QP_LIST_LINES WHERE list_line_id = p_to_rltd_modifier_id;
480
481 --Archive the pricing attributes
482
483 FOR l_qp_pricing_attributes_rec IN qp_pricing_attributes_cur (p_to_rltd_modifier_id)
484 LOOP
485 IF l_qp_pricing_attributes_rec.pricing_attribute_datatype = 'N'
486 THEN
487
488 BEGIN
489
490 l_pric_attr_value_from_number :=
491 qp_number.canonical_to_number(l_qp_pricing_attributes_rec.pricing_attr_value_from);
492
493 l_pric_attr_value_to_number :=
494 qp_number.canonical_to_number(l_qp_pricing_attributes_rec.pricing_attr_value_to);
495
496 EXCEPTION
497 WHEN VALUE_ERROR THEN
498 NULL;
499 WHEN OTHERS THEN
500 NULL;
501 END;
502
503 END IF;
504
505 --Insert into QP_ARCH_PRICING_ATTRIBUTES
506
507 INSERT INTO QP_ARCH_PRICING_ATTRIBUTES
508 (pricing_attribute_id,
509 creation_date,
510 created_by,
511 last_update_date,
512 last_updated_by,
513 last_update_login,
514 program_application_id,
515 program_id,
516 program_update_date,
517 request_id,
518 list_line_id,
519 list_header_id,
520 pricing_phase_id,
521 qualification_ind,
522 excluder_flag,
523 accumulate_flag,
524 product_attribute_context,
525 product_attribute,
526 product_attr_value,
527 product_uom_code,
528 pricing_attribute_context,
529 pricing_attribute,
530 pricing_attr_value_from,
531 pricing_attr_value_to,
532 attribute_grouping_no,
533 context,
534 attribute1,
535 attribute2,
536 attribute3,
537 attribute4,
538 attribute5,
539 attribute6,
540 attribute7,
541 attribute8,
542 attribute9,
543 attribute10,
544 attribute11,
545 attribute12,
546 attribute13,
547 attribute14,
548 attribute15,
549 product_attribute_datatype,
550 pricing_attribute_datatype,
551 comparison_operator_code,
552 pricing_attr_value_from_number,
553 pricing_attr_value_to_number,
554 DISTINCT_ROW_COUNT,
555 SEARCH_IND,
556 PATTERN_VALUE_FROM_POSITIVE,
557 PATTERN_VALUE_TO_POSITIVE,
558 PATTERN_VALUE_FROM_NEGATIVE,
559 PATTERN_VALUE_TO_NEGATIVE,
560 PRODUCT_SEGMENT_ID,
561 PRICING_SEGMENT_ID,
562 ARCH_PURG_REQUEST_ID
563 )
564 VALUES
565 (l_qp_pricing_attributes_rec.pricing_attribute_id,
566 l_qp_pricing_attributes_rec.creation_date,
570 l_qp_pricing_attributes_rec.last_update_login,
567 l_qp_pricing_attributes_rec.created_by,
568 l_qp_pricing_attributes_rec.last_update_date,
569 l_qp_pricing_attributes_rec.last_updated_by,
571 l_qp_pricing_attributes_rec.program_application_id,
572 l_qp_pricing_attributes_rec.program_id,
573 l_qp_pricing_attributes_rec.program_update_date,
574 l_qp_pricing_attributes_rec.request_id,
575 l_qp_pricing_attributes_rec.list_line_id,
576 l_qp_pricing_attributes_rec.list_header_id,
577 l_qp_pricing_attributes_rec.pricing_phase_id,
578 l_qp_pricing_attributes_rec.qualification_ind,
579 l_qp_pricing_attributes_rec.excluder_flag,
580 l_qp_pricing_attributes_rec.accumulate_flag,
581 l_qp_pricing_attributes_rec.product_attribute_context,
582 l_qp_pricing_attributes_rec.product_attribute,
583 l_qp_pricing_attributes_rec.product_attr_value,
584 l_qp_pricing_attributes_rec.product_uom_code,
585 l_qp_pricing_attributes_rec.pricing_attribute_context,
586 l_qp_pricing_attributes_rec.pricing_attribute,
587 l_qp_pricing_attributes_rec.pricing_attr_value_from,
588 l_qp_pricing_attributes_rec.pricing_attr_value_to,
589 l_qp_pricing_attributes_rec.attribute_grouping_no,
590 l_qp_pricing_attributes_rec.context,
591 l_qp_pricing_attributes_rec.attribute1,
592 l_qp_pricing_attributes_rec.attribute2,
593 l_qp_pricing_attributes_rec.attribute3,
594 l_qp_pricing_attributes_rec.attribute4,
595 l_qp_pricing_attributes_rec.attribute5,
596 l_qp_pricing_attributes_rec.attribute6,
597 l_qp_pricing_attributes_rec.attribute7,
598 l_qp_pricing_attributes_rec.attribute8,
599 l_qp_pricing_attributes_rec.attribute9,
600 l_qp_pricing_attributes_rec.attribute10,
601 l_qp_pricing_attributes_rec.attribute11,
602 l_qp_pricing_attributes_rec.attribute12,
603 l_qp_pricing_attributes_rec.attribute13,
604 l_qp_pricing_attributes_rec.attribute14,
605 l_qp_pricing_attributes_rec.attribute15,
606 l_qp_pricing_attributes_rec.product_attribute_datatype,
607 l_qp_pricing_attributes_rec.pricing_attribute_datatype,
608 l_qp_pricing_attributes_rec.comparison_operator_code,
609 l_pric_attr_value_from_number,
610 l_pric_attr_value_to_number,
611 l_qp_pricing_attributes_rec.DISTINCT_ROW_COUNT,
612 l_qp_pricing_attributes_rec.SEARCH_IND,
613 l_qp_pricing_attributes_rec.PATTERN_VALUE_FROM_POSITIVE,
614 l_qp_pricing_attributes_rec.PATTERN_VALUE_TO_POSITIVE,
615 l_qp_pricing_attributes_rec.PATTERN_VALUE_FROM_NEGATIVE,
616 l_qp_pricing_attributes_rec.PATTERN_VALUE_TO_NEGATIVE,
617 l_qp_pricing_attributes_rec.PRODUCT_SEGMENT_ID,
618 l_qp_pricing_attributes_rec.PRICING_SEGMENT_ID,
619 p_conc_request_id
620 );
621 g_count_pricing_att := g_count_pricing_att + sql%rowcount;
622
623 --Delete the records from qp_pricing_attributes table
624
625 /* DELETE FROM QP_PRICING_ATTRIBUTES WHERE pricing_attribute_id = l_qp_pricing_attributes_rec.pricing_attribute_id
626 and list_line_id = l_qp_pricing_attributes_rec.list_line_id
627 and list_header_id = l_qp_pricing_attributes_rec.list_header_id; */ --bug#13045009
628
629 END LOOP; /* Cursor qp_pricing_attributes_cur LOOP */
630
631 ---bug#13045009
632 DELETE FROM QP_PRICING_ATTRIBUTES
633 WHERE-- pricing_attribute_id = l_qp_pricing_attributes_rec.pricing_attribute_id and
634 list_line_id = p_to_rltd_modifier_id; --l_qp_pricing_attributes_rec.list_line_id;
635 --and list_header_id = l_qp_pricing_attributes_rec.list_header_id;
636 ---bug#13045009
637
638 END DELETE_CHILD;
639
640
641 /************************************************************************
642 *Procedure to Archive The Price list or the Modifier List *
643 *************************************************************************/
644
645
646 PROCEDURE ARCHIVE_ENTITY
647 (
648 errbuf OUT NOCOPY VARCHAR2,
649 retcode OUT NOCOPY NUMBER,
650 p_archive_name IN VARCHAR2,
651 p_entity_type IN VARCHAR2,
652 p_source_system_code IN VARCHAR2,
653 p_entity IN NUMBER,
654 p_all_lines IN VARCHAR2,
655 p_product_context IN VARCHAR2,
656 p_product_attribute IN VARCHAR2,
657 p_product_attr_value_from IN VARCHAR2,
658 p_product_attr_value_to IN VARCHAR2,
659 p_start_date_active IN VARCHAR2,
660 p_end_date_active IN VARCHAR2,
661 p_creation_date IN VARCHAR2,
662 p_created_by IN NUMBER,
663 p_segment1_lohi IN VARCHAR2,
664 p_segment2_lohi IN VARCHAR2,
665 p_segment3_lohi IN VARCHAR2,
666 p_segment4_lohi IN VARCHAR2,
667 p_segment5_lohi IN VARCHAR2,
668 p_segment6_lohi IN VARCHAR2,
669 p_segment7_lohi IN VARCHAR2,
670 p_segment8_lohi IN VARCHAR2,
671 p_segment9_lohi IN VARCHAR2,
672 p_segment10_lohi IN VARCHAR2,
673 p_segment11_lohi IN VARCHAR2,
674 p_segment12_lohi IN VARCHAR2,
675 p_segment13_lohi IN VARCHAR2,
676 p_segment14_lohi IN VARCHAR2,
677 p_segment15_lohi IN VARCHAR2,
678 p_segment16_lohi IN VARCHAR2,
679 p_segment17_lohi IN VARCHAR2,
680 p_segment18_lohi IN VARCHAR2,
681 p_segment19_lohi IN VARCHAR2,
682 p_segment20_lohi IN VARCHAR2
683 )
684 IS
685
686 l_conc_request_id NUMBER := -1;
687 l_conc_program_application_id NUMBER := -1;
691
688 l_conc_program_id NUMBER := -1;
689 l_conc_login_id NUMBER := -1;
690 l_user_id NUMBER := -1;
692 l_insert_flag varchar2(1);
693 l_cnt number:=0;
694 l_err_count number:=0;
695
696 TYPE qp_list_lines_rec IS RECORD (
697 list_line_id QP_LIST_LINES.list_line_id%TYPE,
698 creation_date QP_LIST_LINES.creation_date%TYPE,
699 created_by QP_LIST_LINES.created_by%TYPE,
700 last_update_date QP_LIST_LINES.last_update_date%TYPE,
701 last_updated_by QP_LIST_LINES.last_updated_by%TYPE,
702 last_update_login QP_LIST_LINES.last_update_login%TYPE,
703 program_application_id QP_LIST_LINES.program_application_id%TYPE,
704 program_id QP_LIST_LINES.program_id%TYPE,
705 program_update_date QP_LIST_LINES.program_update_date%TYPE,
706 request_id QP_LIST_LINES.request_id%TYPE,
707 list_header_id QP_LIST_LINES.list_header_id%TYPE,
708 list_line_type_code QP_LIST_LINES.list_line_type_code%TYPE,
709 automatic_flag QP_LIST_LINES.automatic_flag%TYPE,
710 modifier_level_code QP_LIST_LINES.modifier_level_code%TYPE,
711 list_price QP_LIST_LINES.list_price%TYPE,
712 primary_uom_flag QP_LIST_LINES.primary_uom_flag%TYPE,
713 inventory_item_id QP_LIST_LINES.inventory_item_id%TYPE,
714 organization_id QP_LIST_LINES.organization_id%TYPE,
715 related_item_id QP_LIST_LINES.related_item_id%TYPE,
716 relationship_type_id QP_LIST_LINES.relationship_type_id%TYPE,
717 substitution_context QP_LIST_LINES.substitution_context%TYPE,
718 substitution_attribute QP_LIST_LINES.substitution_attribute%TYPE,
719 substitution_value QP_LIST_LINES.substitution_value%TYPE,
720 revision QP_LIST_LINES.revision%TYPE,
721 revision_date QP_LIST_LINES.revision_date%TYPE,
722 revision_reason_code QP_LIST_LINES.revision_reason_code%TYPE,
723 context QP_LIST_LINES.context%TYPE,
724 attribute1 QP_LIST_LINES.attribute1%TYPE,
725 attribute2 QP_LIST_LINES.attribute2%TYPE,
726 comments QP_LIST_LINES.comments%TYPE,
727 attribute3 QP_LIST_LINES.attribute3%TYPE,
728 attribute4 QP_LIST_LINES.attribute4%TYPE,
729 attribute5 QP_LIST_LINES.attribute5%TYPE,
730 attribute6 QP_LIST_LINES.attribute6%TYPE,
731 attribute7 QP_LIST_LINES.attribute7%TYPE,
732 attribute8 QP_LIST_LINES.attribute8%TYPE,
733 attribute9 QP_LIST_LINES.attribute9%TYPE,
734 attribute10 QP_LIST_LINES.attribute10%TYPE,
735 attribute11 QP_LIST_LINES.attribute11%TYPE,
736 attribute12 QP_LIST_LINES.attribute12%TYPE,
737 attribute13 QP_LIST_LINES.attribute13%TYPE,
738 attribute14 QP_LIST_LINES.attribute14%TYPE,
739 attribute15 QP_LIST_LINES.attribute15%TYPE,
740 price_break_type_code QP_LIST_LINES.price_break_type_code%TYPE,
741 percent_price QP_LIST_LINES.percent_price%TYPE,
742 price_by_formula_id QP_LIST_LINES.price_by_formula_id%TYPE,
743 number_effective_periods QP_LIST_LINES.number_effective_periods%TYPE,
744 effective_period_uom QP_LIST_LINES.effective_period_uom%TYPE,
745 arithmetic_operator QP_LIST_LINES.arithmetic_operator%TYPE,
746 operand QP_LIST_LINES.operand%TYPE,
747 override_flag QP_LIST_LINES.override_flag%TYPE,
748 print_on_invoice_flag QP_LIST_LINES.print_on_invoice_flag%TYPE,
749 rebate_transaction_type_code QP_LIST_LINES.rebate_transaction_type_code%TYPE,
750 estim_accrual_rate QP_LIST_LINES.estim_accrual_rate%TYPE,
751 generate_using_formula_id QP_LIST_LINES.generate_using_formula_id%TYPE,
752 start_date_active QP_LIST_LINES.start_date_active%TYPE,
753 end_date_active QP_LIST_LINES.end_date_active%TYPE,
754 reprice_flag QP_LIST_LINES.reprice_flag%TYPE,
755 accrual_flag QP_LIST_LINES.accrual_flag%TYPE,
756 pricing_group_sequence QP_LIST_LINES.pricing_group_sequence%TYPE,
757 incompatibility_grp_code QP_LIST_LINES.incompatibility_grp_code%TYPE,
758 list_line_no QP_LIST_LINES.list_line_no%TYPE,
759 product_precedence QP_LIST_LINES.product_precedence%TYPE,
760 pricing_phase_id QP_LIST_LINES.pricing_phase_id%TYPE,
761 expiration_period_start_date QP_LIST_LINES.expiration_period_start_date%TYPE,
762 number_expiration_periods QP_LIST_LINES.number_expiration_periods%TYPE,
763 expiration_period_uom QP_LIST_LINES.expiration_period_uom%TYPE,
764 expiration_date QP_LIST_LINES.expiration_date%TYPE,
765 estim_gl_value QP_LIST_LINES.estim_gl_value%TYPE,
766 accrual_conversion_rate QP_LIST_LINES.accrual_conversion_rate%TYPE,
767 benefit_price_list_line_id QP_LIST_LINES.benefit_price_list_line_id%TYPE,
768 proration_type_code QP_LIST_LINES.proration_type_code%TYPE,
769 benefit_qty QP_LIST_LINES.benefit_qty%TYPE,
770 benefit_uom_code QP_LIST_LINES.benefit_uom_code%TYPE,
771 charge_type_code QP_LIST_LINES.charge_type_code%TYPE,
772 charge_subtype_code QP_LIST_LINES.charge_subtype_code%TYPE,
773 benefit_limit QP_LIST_LINES.benefit_limit%TYPE,
774 include_on_returns_flag QP_LIST_LINES.include_on_returns_flag%TYPE,
775 qualification_ind QP_LIST_LINES.qualification_ind%TYPE,
776 recurring_value QP_LIST_LINES.recurring_value%TYPE,
777 LIST_PRICE_UOM_CODE QP_LIST_LINES.LIST_PRICE_UOM_CODE%TYPE,
778 BASE_QTY QP_LIST_LINES.BASE_QTY%TYPE,
779 BASE_UOM_CODE QP_LIST_LINES.BASE_UOM_CODE%TYPE,
780 ACCRUAL_QTY QP_LIST_LINES.ACCRUAL_QTY%TYPE,
781 ACCRUAL_UOM_CODE QP_LIST_LINES.ACCRUAL_UOM_CODE%TYPE,
782 RECURRING_FLAG QP_LIST_LINES.RECURRING_FLAG%TYPE,
783 LIMIT_EXISTS_FLAG QP_LIST_LINES.LIMIT_EXISTS_FLAG%TYPE,
784 GROUP_COUNT QP_LIST_LINES.GROUP_COUNT%TYPE
785 ,NET_AMOUNT_FLAG QP_LIST_LINES.NET_AMOUNT_FLAG%TYPE
786 ,CUSTOMER_ITEM_ID QP_LIST_LINES.CUSTOMER_ITEM_ID%TYPE
787 ,ACCUM_CONTEXT QP_LIST_LINES.ACCUM_CONTEXT%TYPE
788 ,ACCUM_ATTRIBUTE QP_LIST_LINES.ACCUM_ATTRIBUTE%TYPE
789 ,ACCUM_ATTR_RUN_SRC_FLAG QP_LIST_LINES.ACCUM_ATTR_RUN_SRC_FLAG%TYPE
790 ,BREAK_UOM_CODE QP_LIST_LINES.BREAK_UOM_CODE%TYPE
791 ,BREAK_UOM_CONTEXT QP_LIST_LINES.BREAK_UOM_CONTEXT%TYPE
792 ,BREAK_UOM_ATTRIBUTE QP_LIST_LINES.BREAK_UOM_ATTRIBUTE%TYPE
793 ,PATTERN_ID QP_LIST_LINES.PATTERN_ID%TYPE
794 ,PRODUCT_UOM_CODE QP_LIST_LINES.PRODUCT_UOM_CODE%TYPE
795 ,PRICING_ATTRIBUTE_COUNT QP_LIST_LINES.PRICING_ATTRIBUTE_COUNT%TYPE
796 ,HASH_KEY QP_LIST_LINES.HASH_KEY%TYPE
797 ,CACHE_KEY QP_LIST_LINES.CACHE_KEY%TYPE
798 );
799
800 l_mapping_tbl mapping_tbl;
801 l_select_stmt VARCHAR2(9000);
802 l_qp_list_lines_rec QP_LIST_LINES_REC;
803 l_context VARCHAR2(30);
804 l_attribute VARCHAR2(30);
805 l_count NUMBER := 0;
806
807 TYPE l_list_line_id_tbl_type IS TABLE OF QP_LIST_LINES.list_line_id%TYPE INDEX BY BINARY_INTEGER; --bug#13045009
808
809 l_list_line_id_tbl l_list_line_id_tbl_type; --bug#13045009
810 l_id_count NUMBER := 0; --bug#13045009
811
812
813 /* 9680271 */
814 l_segment_array_low fnd_flex_ext.SegmentArray;
815 segment_count_low number;
816 l_segment_array_high fnd_flex_ext.SegmentArray;
817 segment_count_high number;
818 segment_low_count number;
819 SEGMENT_LOHI VARCHAR2(50) := NULL;
820 /* 9680271 */
821 l_pric_attr_value_from_number NUMBER := NULL;
822 l_pric_attr_value_to_number NUMBER := NULL;
823
824 l_list_type_code VARCHAR2(30) := '';
825 l_qual_attr_value_from_number NUMBER := NULL;
826 l_qual_attr_value_to_number NUMBER := NULL;
827 l_min_date date := to_date('01/01/1900','DD/MM/YYYY');
828 l_max_date date := to_date('31/12/9999','DD/MM/YYYY');
829
830 TYPE lines_cur_typ IS REF CURSOR;
831 qp_list_lines_cv lines_cur_typ;
832
833 CURSOR qp_pricing_attributes_cur(p_from_list_line_id NUMBER)
834 IS
835 SELECT *
836 FROM qp_pricing_attributes
837 WHERE list_line_id = p_from_list_line_id;
838
839 CURSOR qp_headers_qualifiers_cur(p_from_discount_header_id NUMBER)
840 IS
841 SELECT *
842 FROM qp_qualifiers
843 WHERE list_header_id = p_from_discount_header_id and
844 list_line_id = -1;
845
846 CURSOR qp_line_qualifiers_cur(p_from_discount_header_id NUMBER,
847 p_from_discount_line_id NUMBER)
848 IS
849 SELECT *
850 FROM qp_qualifiers
851 WHERE list_header_id = p_from_discount_header_id and
852 list_line_id = p_from_discount_line_id ;
853
854
855 /* First part of cursor qp_qualifiers_cur selects qualifiers while the second part
856 selects secondary price list */
857
858 CURSOR qp_qualifiers_cur(p_from_list_header_id NUMBER, p_context VARCHAR2,
859 p_attribute VARCHAR2)
860 IS
861 SELECT *
862 FROM qp_qualifiers q
863 WHERE (q.list_header_id = p_from_list_header_id AND
864 q.qualifier_attribute <> p_attribute AND
865 Exists (Select Null
866 From qp_list_headers_b a
867 Where a.list_header_id = p_from_list_header_id
868 And a.list_type_code = 'PRL'
869 )
870 )
871 OR
872 (q.qualifier_context = p_context AND
873 q.qualifier_attribute = p_attribute AND
874 q.qualifier_attr_value = TO_CHAR(p_from_list_header_id) AND
875 EXISTS (select null from qp_list_headers_b a
876 where a.list_header_id =q.list_header_id
877 And a.list_type_code = 'PRL')
878 );
879
880 CURSOR qp_rltd_modifiers_cur(a_list_line_id NUMBER)
881 IS
882 SELECT *
883 FROM qp_rltd_modifiers
884 WHERE from_rltd_modifier_id = a_list_line_id;
885
886 BEGIN
887
888 l_conc_request_id := FND_GLOBAL.CONC_REQUEST_ID;
892 l_conc_program_application_id := FND_GLOBAL.PROG_APPL_ID;
889 l_conc_program_id := FND_GLOBAL.CONC_PROGRAM_ID;
890 l_user_id := FND_GLOBAL.USER_ID;
891 l_conc_login_id := FND_GLOBAL.CONC_LOGIN_ID;
893
894 /* Validate number of list lines selected. If there are zero lines selected for the given search criteria, throw error message. */
895
896 l_select_stmt :=
897 'SELECT
898 q.list_line_id,
899 q.creation_date,
900 q.created_by,
901 q.last_update_date,
902 q.last_updated_by,
903 q.last_update_login,
904 q.program_application_id,
905 q.program_id,
906 q.program_update_date,
907 q.request_id,
908 q.list_header_id,
909 q.list_line_type_code,
910 q.automatic_flag,
911 q.modifier_level_code,
912 q.list_price,
913 q.primary_uom_flag,
914 q.inventory_item_id,
915 q.organization_id,
916 q.related_item_id,
917 q.relationship_type_id,
918 q.substitution_context,
919 q.substitution_attribute,
920 q.substitution_value,
921 q.revision,
922 q.revision_date,
923 q.revision_reason_code,
924 q.context,
925 q.attribute1,
926 q.attribute2,
927 q.comments,
928 q.attribute3,
929 q.attribute4,
930 q.attribute5,
931 q.attribute6,
932 q.attribute7,
933 q.attribute8,
934 q.attribute9,
935 q.attribute10,
936 q.attribute11,
937 q.attribute12,
938 q.attribute13,
939 q.attribute14,
940 q.attribute15,
941 q.price_break_type_code,
942 q.percent_price,
943 q.price_by_formula_id,
944 q.number_effective_periods,
945 q.effective_period_uom,
946 q.arithmetic_operator,
947 q.operand,
948 q.override_flag,
949 q.print_on_invoice_flag,
950 q.rebate_transaction_type_code,
951 q.estim_accrual_rate,
952 q.generate_using_formula_id,
953 q.start_date_active,
954 q.end_date_active,
955 q.reprice_flag,
956 q.accrual_flag,
957 q.pricing_group_sequence,
958 q.incompatibility_grp_code,
959 q.list_line_no,
960 q.product_precedence,
961 q.pricing_phase_id,
962 q.expiration_period_start_date,
963 q.number_expiration_periods,
964 q.expiration_period_uom,
965 q.expiration_date,
966 q.estim_gl_value,
967 q.accrual_conversion_rate,
968 q.benefit_price_list_line_id,
969 q.proration_type_code,
970 q.benefit_qty,
971 q.benefit_uom_code,
972 q.charge_type_code,
973 q.charge_subtype_code,
974 q.benefit_limit,
975 q.include_on_returns_flag,
976 q.qualification_ind,
977 q.recurring_value,
978 q.LIST_PRICE_UOM_CODE,
979 q.BASE_QTY,
980 q.BASE_UOM_CODE,
981 q.ACCRUAL_QTY,
982 q.ACCRUAL_UOM_CODE,
983 q.RECURRING_FLAG,
984 q.LIMIT_EXISTS_FLAG,
985 q.GROUP_COUNT
986 ,q.NET_AMOUNT_FLAG
987 ,q.CUSTOMER_ITEM_ID
988 ,q.ACCUM_CONTEXT
989 ,q.ACCUM_ATTRIBUTE
990 ,q.ACCUM_ATTR_RUN_SRC_FLAG
991 ,q.BREAK_UOM_CODE
992 ,q.BREAK_UOM_CONTEXT
993 ,q.BREAK_UOM_ATTRIBUTE
994 ,q.PATTERN_ID
995 ,q.PRODUCT_UOM_CODE
996 ,q.PRICING_ATTRIBUTE_COUNT
997 ,q.HASH_KEY
998 ,q.CACHE_KEY
999 FROM qp_list_lines q
1000 WHERE q.list_header_id = :hdr
1001 and nvl(trunc(q.start_date_active),fnd_date.canonical_to_date('''||fnd_date.date_to_canonical(l_min_date)||'''))
1002 >=nvl(trunc(fnd_date.canonical_to_date(:sdat)),
1003 nvl(trunc(q.start_date_active),fnd_date.canonical_to_date('''||fnd_date.date_to_canonical(l_min_date)||''')))
1004 and nvl(trunc(q.end_date_active),fnd_date.canonical_to_date('''||fnd_date.date_to_canonical(l_max_date)||'''))
1005 <=nvl(trunc(fnd_date.canonical_to_date(:edat)),
1006 nvl(trunc(q.end_date_active),fnd_date.canonical_to_date('''||fnd_date.date_to_canonical(l_max_date)||''')))
1007 and q.created_by=nvl(:usr,q.created_by)
1008 and trunc(q.creation_date)=nvl(trunc(fnd_date.canonical_to_date(:cdat)),trunc(q.creation_date))';
1009
1010 /* Check whether all_lines check box is checked if yes then archive all the lines in the pricing entity */
1011 IF nvl(p_all_lines,'N') = 'N' THEN
1012 IF p_product_context is not NULL THEN
1013 l_select_stmt := l_select_stmt || 'AND q.list_line_id IN
1014 (SELECT DISTINCT a.list_line_id
1015 FROM qp_pricing_attributes a
1016 WHERE a.list_line_id = q.list_line_id
1017 and EXCLUDER_FLAG =''N'''; --This would take care of Exculded Items.
1018
1019 IF p_product_attribute = 'PRICING_ATTRIBUTE1' THEN
1020 IF (p_segment1_lohi <> ''''' AND ''''') OR (p_segment2_lohi <> ''''' AND ''''')
1021 OR (p_segment3_lohi <> ''''' AND ''''') OR (p_segment4_lohi <> ''''' AND ''''')
1022 OR (p_segment5_lohi <> ''''' AND ''''') OR (p_segment6_lohi <> ''''' AND ''''')
1023 OR (p_segment7_lohi <> ''''' AND ''''') OR (p_segment8_lohi <> ''''' AND ''''')
1024 OR (p_segment9_lohi <> ''''' AND ''''') OR (p_segment10_lohi <> ''''' AND ''''')
1025 OR (p_segment11_lohi <> ''''' AND ''''')
1026 OR (p_segment12_lohi <> ''''' AND ''''')
1027 OR (p_segment13_lohi <> ''''' AND ''''')
1028 OR (p_segment14_lohi <> ''''' AND ''''')
1032 OR (p_segment18_lohi <> ''''' AND ''''')
1029 OR (p_segment15_lohi <> ''''' AND ''''')
1030 OR (p_segment16_lohi <> ''''' AND ''''')
1031 OR (p_segment17_lohi <> ''''' AND ''''')
1033 OR (p_segment19_lohi <> ''''' AND ''''')
1034 OR (p_segment20_lohi <> ''''' AND ''''') THEN
1035 l_select_stmt := l_select_stmt ||
1036 'AND a.product_attribute_context = ''ITEM''
1037 AND a.product_attribute = ''PRICING_ATTRIBUTE1''
1038 AND EXISTS
1039 (SELECT ''X''
1040 FROM mtl_system_items m
1041 WHERE (m.inventory_item_id = TO_NUMBER(a.product_attr_value)) ';
1042
1043 IF (p_segment1_lohi <> ''''' AND ''''') THEN
1044 l_select_stmt := l_select_stmt ||
1045 'AND (m.segment1 BETWEEN ' || p_segment1_lohi || ') ';
1046 END IF;
1047 IF (p_segment2_lohi <> ''''' AND ''''') THEN
1048 l_select_stmt := l_select_stmt ||
1049 'AND (m.segment2 BETWEEN ' || p_segment2_lohi || ') ';
1050 END IF;
1051 IF (p_segment3_lohi <> ''''' AND ''''') THEN
1052 l_select_stmt := l_select_stmt ||
1053 'AND (m.segment3 BETWEEN ' || p_segment3_lohi || ') ';
1054 END IF;
1055 IF (p_segment4_lohi <> ''''' AND ''''') THEN
1056 l_select_stmt := l_select_stmt ||
1057 'AND (m.segment4 BETWEEN ' || p_segment4_lohi || ') ';
1058 END IF;
1059 IF (p_segment5_lohi <> ''''' AND ''''') THEN
1060 l_select_stmt := l_select_stmt ||
1061 'AND (m.segment5 BETWEEN ' || p_segment5_lohi || ') ';
1062 END IF;
1063 IF (p_segment6_lohi <> ''''' AND ''''') THEN
1064 l_select_stmt := l_select_stmt ||
1065 'AND (m.segment6 BETWEEN ' || p_segment6_lohi || ') ';
1066 END IF;
1067 IF (p_segment7_lohi <> ''''' AND ''''') THEN
1068 l_select_stmt := l_select_stmt ||
1069 'AND (m.segment7 BETWEEN ' || p_segment7_lohi || ') ';
1070 END IF;
1071 IF (p_segment8_lohi <> ''''' AND ''''') THEN
1072 l_select_stmt := l_select_stmt ||
1073 'AND (m.segment8 BETWEEN ' || p_segment8_lohi || ') ';
1074 END IF;
1075 IF (p_segment9_lohi <> ''''' AND ''''') THEN
1076 l_select_stmt := l_select_stmt ||
1077 'AND (m.segment9 BETWEEN ' || p_segment9_lohi || ') ';
1078 END IF;
1079 IF (p_segment10_lohi <> ''''' AND ''''') THEN
1080 l_select_stmt := l_select_stmt ||
1081 'AND (m.segment10 BETWEEN ' || p_segment10_lohi || ') ';
1082 END IF;
1083 IF (p_segment11_lohi <> ''''' AND ''''') THEN
1084 l_select_stmt := l_select_stmt ||
1085 'AND (m.segment10 BETWEEN ' || p_segment11_lohi || ') ';
1086 END IF;
1087 IF (p_segment12_lohi <> ''''' AND ''''') THEN
1088 l_select_stmt := l_select_stmt ||
1089 'AND (m.segment12 BETWEEN ' || p_segment12_lohi || ') ';
1090 END IF;
1091 IF (p_segment13_lohi <> ''''' AND ''''') THEN
1092 l_select_stmt := l_select_stmt ||
1093 'AND (m.segment13 BETWEEN ' || p_segment13_lohi || ') ';
1094 END IF;
1095 IF (p_segment14_lohi <> ''''' AND ''''') THEN
1096 l_select_stmt := l_select_stmt ||
1097 'AND (m.segment14 BETWEEN ' || p_segment14_lohi || ') ';
1098 END IF;
1099 IF (p_segment15_lohi <> ''''' AND ''''') THEN
1100 l_select_stmt := l_select_stmt ||
1101 'AND (m.segment15 BETWEEN ' || p_segment15_lohi || ') ';
1102 END IF;
1103 IF (p_segment16_lohi <> ''''' AND ''''') THEN
1104 l_select_stmt := l_select_stmt ||
1105 'AND (m.segment16 BETWEEN ' || p_segment16_lohi || ') ';
1106 END IF;
1107 IF (p_segment17_lohi <> ''''' AND ''''') THEN
1108 l_select_stmt := l_select_stmt ||
1109 'AND (m.segment17 BETWEEN ' || p_segment17_lohi || ') ';
1110 END IF;
1111 IF (p_segment18_lohi <> ''''' AND ''''') THEN
1112 l_select_stmt := l_select_stmt ||
1113 'AND (m.segment18 BETWEEN ' || p_segment18_lohi || ') ';
1114 END IF;
1115 IF (p_segment19_lohi <> ''''' AND ''''') THEN
1116 l_select_stmt := l_select_stmt ||
1117 'AND (m.segment19 BETWEEN ' || p_segment19_lohi || ') ';
1118 END IF;
1119 IF (p_segment20_lohi <> ''''' AND ''''') THEN
1120 l_select_stmt := l_select_stmt ||
1121 'AND (m.segment20 BETWEEN ' || p_segment20_lohi || ') ';
1122 END IF;
1123
1124 l_select_stmt := l_select_stmt || ') )';
1125 ELSE
1126 l_select_stmt := l_select_stmt || ') ';
1127 END IF;
1128
1132 l_select_stmt := l_select_stmt ||
1129 ELSIF p_product_attribute = 'PRICING_ATTRIBUTE2' THEN
1130 /* IF (p_product_attr_value_from <> ''''' AND ''''') THEN --9680271
1131 --IF (p_product_attr_value_from is not null) THEN
1133 'AND a.product_attribute_context = ''ITEM''
1134 AND a.product_attribute = ''PRICING_ATTRIBUTE2''
1135 AND a.product_attr_value = ''' || p_product_attr_value_from || ''') ';
1136 END IF;*/
1137 --9680271
1138 IF (p_product_attr_value_from IS NOT NULL ) AND (p_product_attr_value_to IS NULL ) THEN
1139 l_select_stmt := l_select_stmt ||
1140 ' AND a.product_attribute_context = ''ITEM''
1141 AND a.product_attribute = ''PRICING_ATTRIBUTE2''
1142 AND EXISTS
1143 (SELECT ''X''
1144 FROM MTL_CATEGORIES m
1145 WHERE (m.CATEGORY_ID = TO_NUMBER(a.product_attr_value)) ';
1146 segment_count_low := fnd_flex_ext.breakup_segments (p_product_attr_value_from,'.',l_segment_array_low );
1147 FOR i IN 1..segment_count_low LOOP
1148 IF (l_segment_array_low(i) IS NOT NULL) THEN
1149 l_select_stmt:= l_select_stmt ||
1150 'AND m.segment' || (i) ||' = ''' || l_segment_array_low(i) || '''';
1151 END IF;
1152 END LOOP;
1153 l_select_stmt := l_select_stmt || ') )';
1154 ELSIF (p_product_attr_value_from IS NOT NULL ) AND (p_product_attr_value_to IS NOT NULL ) THEN
1155 l_select_stmt := l_select_stmt ||
1156 ' AND a.product_attribute_context = ''ITEM''
1157 AND a.product_attribute = ''PRICING_ATTRIBUTE2''
1158 AND EXISTS
1159 (SELECT ''X''
1160 FROM MTL_CATEGORIES m
1161 WHERE (m.CATEGORY_ID = TO_NUMBER(a.product_attr_value)) ';
1162 segment_count_low := fnd_flex_ext.breakup_segments (p_product_attr_value_from,'.',l_segment_array_low );
1163 segment_count_high := fnd_flex_ext.breakup_segments (p_product_attr_value_to,'.',l_segment_array_high );
1164 IF(segment_count_low < segment_count_high) THEN
1165 segment_low_count := segment_count_low;
1166 ELSE
1167 segment_low_count := segment_count_high;
1168 END IF;
1169 FOR i IN 1..segment_low_count LOOP
1170 IF (l_segment_array_low(i)IS NOT NULL AND l_segment_array_high(i) IS NOT NULL ) THEN
1171 SEGMENT_LOHI := ''''
1172 || l_segment_array_low(i) || ''' AND '''
1173 || l_segment_array_high(i) || '''';
1174 l_select_stmt:= l_select_stmt ||
1175 'AND (m.segment' || (i) ||' BETWEEN ' || SEGMENT_LOHI || ') ';
1176 SEGMENT_LOHI := NULL;
1177 END IF;
1178 END LOOP;
1179 l_select_stmt := l_select_stmt || ') )';
1180 END IF;
1181 --9680271
1182 /*
1183 IF (p_segment1_lohi <> ''''' AND ''''') OR (p_segment2_lohi <> ''''' AND ''''')
1184 OR (p_segment3_lohi <> ''''' AND ''''') OR (p_segment4_lohi <> ''''' AND ''''')
1185 OR (p_segment5_lohi <> ''''' AND ''''') OR (p_segment6_lohi <> ''''' AND ''''')
1186 OR (p_segment7_lohi <> ''''' AND ''''') OR (p_segment8_lohi <> ''''' AND ''''')
1187 OR (p_segment9_lohi <> ''''' AND ''''') OR (p_segment10_lohi <> ''''' AND ''''')
1188 OR (p_segment11_lohi <> ''''' AND ''''')
1189 OR (p_segment12_lohi <> ''''' AND ''''')
1190 OR (p_segment13_lohi <> ''''' AND ''''')
1191 OR (p_segment14_lohi <> ''''' AND ''''')
1192 OR (p_segment15_lohi <> ''''' AND ''''')
1193 OR (p_segment16_lohi <> ''''' AND ''''')
1194 OR (p_segment17_lohi <> ''''' AND ''''')
1195 OR (p_segment18_lohi <> ''''' AND ''''')
1196 OR (p_segment19_lohi <> ''''' AND ''''')
1197 OR (p_segment20_lohi <> ''''' AND ''''') THEN
1198
1199 l_select_stmt := l_select_stmt ||
1200 'AND a.product_attribute_context = ''ITEM''
1201 AND a.product_attribute = ''PRICING_ATTRIBUTE2''
1202 AND EXISTS
1203 (SELECT ''X''
1204 FROM MTL_CATEGORIES m
1205 WHERE (m.CATEGORY_ID = TO_NUMBER(a.product_attr_value)) ';
1206
1207 IF (p_segment1_lohi <> ''''' AND ''''') THEN
1208 l_select_stmt := l_select_stmt ||
1209 'AND (m.segment1 BETWEEN ' || p_segment1_lohi || ') ';
1210 END IF;
1211 IF (p_segment2_lohi <> ''''' AND ''''') THEN
1212 l_select_stmt := l_select_stmt ||
1213 'AND (m.segment2 BETWEEN ' || p_segment2_lohi || ') ';
1214 END IF;
1215 IF (p_segment3_lohi <> ''''' AND ''''') THEN
1216 l_select_stmt := l_select_stmt ||
1217 'AND (m.segment3 BETWEEN ' || p_segment3_lohi || ') ';
1218 END IF;
1219 IF (p_segment4_lohi <> ''''' AND ''''') THEN
1220 l_select_stmt := l_select_stmt ||
1221 'AND (m.segment4 BETWEEN ' || p_segment4_lohi || ') ';
1222 END IF;
1223 IF (p_segment5_lohi <> ''''' AND ''''') THEN
1224 l_select_stmt := l_select_stmt ||
1225 'AND (m.segment5 BETWEEN ' || p_segment5_lohi || ') ';
1226 END IF;
1227 IF (p_segment6_lohi <> ''''' AND ''''') THEN
1228 l_select_stmt := l_select_stmt ||
1229 'AND (m.segment6 BETWEEN ' || p_segment6_lohi || ') ';
1230 END IF;
1231 IF (p_segment7_lohi <> ''''' AND ''''') THEN
1232 l_select_stmt := l_select_stmt ||
1236 l_select_stmt := l_select_stmt ||
1233 'AND (m.segment7 BETWEEN ' || p_segment7_lohi || ') ';
1234 END IF;
1235 IF (p_segment8_lohi <> ''''' AND ''''') THEN
1237 'AND (m.segment8 BETWEEN ' || p_segment8_lohi || ') ';
1238 END IF;
1239 IF (p_segment9_lohi <> ''''' AND ''''') THEN
1240 l_select_stmt := l_select_stmt ||
1241 'AND (m.segment9 BETWEEN ' || p_segment9_lohi || ') ';
1242 END IF;
1243 IF (p_segment10_lohi <> ''''' AND ''''') THEN
1244 l_select_stmt := l_select_stmt ||
1245 'AND (m.segment10 BETWEEN ' || p_segment10_lohi || ') ';
1246 END IF;
1247 IF (p_segment11_lohi <> ''''' AND ''''') THEN
1248 l_select_stmt := l_select_stmt ||
1249 'AND (m.segment10 BETWEEN ' || p_segment11_lohi || ') ';
1250 END IF;
1251 IF (p_segment12_lohi <> ''''' AND ''''') THEN
1252 l_select_stmt := l_select_stmt ||
1253 'AND (m.segment12 BETWEEN ' || p_segment12_lohi || ') ';
1254 END IF;
1255 IF (p_segment13_lohi <> ''''' AND ''''') THEN
1256 l_select_stmt := l_select_stmt ||
1257 'AND (m.segment13 BETWEEN ' || p_segment13_lohi || ') ';
1258 END IF;
1259 IF (p_segment14_lohi <> ''''' AND ''''') THEN
1260 l_select_stmt := l_select_stmt ||
1261 'AND (m.segment14 BETWEEN ' || p_segment14_lohi || ') ';
1262 END IF;
1263 IF (p_segment15_lohi <> ''''' AND ''''') THEN
1264 l_select_stmt := l_select_stmt ||
1265 'AND (m.segment15 BETWEEN ' || p_segment15_lohi || ') ';
1266 END IF;
1267 IF (p_segment16_lohi <> ''''' AND ''''') THEN
1268 l_select_stmt := l_select_stmt ||
1269 'AND (m.segment16 BETWEEN ' || p_segment16_lohi || ') ';
1270 END IF;
1271 IF (p_segment17_lohi <> ''''' AND ''''') THEN
1272 l_select_stmt := l_select_stmt ||
1273 'AND (m.segment17 BETWEEN ' || p_segment17_lohi || ') ';
1274 END IF;
1275 IF (p_segment18_lohi <> ''''' AND ''''') THEN
1276 l_select_stmt := l_select_stmt ||
1277 'AND (m.segment18 BETWEEN ' || p_segment18_lohi || ') ';
1278 END IF;
1279 IF (p_segment19_lohi <> ''''' AND ''''') THEN
1280 l_select_stmt := l_select_stmt ||
1281 'AND (m.segment19 BETWEEN ' || p_segment19_lohi || ') ';
1282 END IF;
1283 IF (p_segment20_lohi <> ''''' AND ''''') THEN
1284 l_select_stmt := l_select_stmt ||
1285 'AND (m.segment20 BETWEEN ' || p_segment20_lohi || ') ';
1286 END IF;
1287
1288 l_select_stmt := l_select_stmt || ') )';
1289 ELSE
1290 l_select_stmt := l_select_stmt || ') ';
1291 END IF;
1292 */
1293 ELSE
1294 l_select_stmt := l_select_stmt ||
1295 'AND a.product_attribute_context = ''ITEM''
1296 AND (a.product_attribute = ''' || p_product_attribute|| ''')
1297 AND (a.product_attr_value = ''' || p_product_attr_value_from|| ''') ';
1298 l_select_stmt := l_select_stmt || ') ';
1299 END IF; --Pricing Attribute end
1300 ELSE --Product_context is null
1301 -- changed the not in condition to in for the bug 7315038
1302 l_select_stmt := l_select_stmt || 'AND q.list_line_id in (SELECT DISTINCT a.list_line_id
1303 FROM qp_pricing_attributes a
1304 WHERE a.list_line_id = q.list_line_id)';
1305 END IF; -- Product_context end
1306 ELSE
1307 l_select_stmt := l_select_stmt || 'AND q.list_line_id not in (SELECT DISTINCT a.to_rltd_modifier_id
1308 FROM qp_rltd_modifiers a
1309 WHERE a.to_rltd_modifier_id = q.list_line_id)';
1310 END IF; --all_lines end
1311
1312 OPEN qp_list_lines_cv FOR l_select_stmt USING p_entity,p_start_date_active,p_end_date_active,p_created_by,p_creation_date;
1313 FETCH qp_list_lines_cv INTO l_qp_list_lines_rec;
1314 if(qp_list_lines_cv%NOTFOUND) THEN -- No list lines found satisfying the search criteria entered.
1315 CLOSE qp_list_lines_cv;
1316 RAISE NO_DATA_FOUND;
1317 end if;
1318 CLOSE qp_list_lines_cv;
1319
1320 /** Following code inserts pricing entity list header information into QP_ARCH_LIST_HEADERS_B **/
1321
1322 INSERT INTO QP_ARCH_LIST_HEADERS_B
1323 (
1324 list_header_id,
1325 creation_date,
1326 created_by,
1327 last_update_date,
1328 last_updated_by,
1329 last_update_login,
1330 program_application_id,
1331 program_id,
1332 program_update_date,
1333 request_id,
1334 list_type_code,
1335 start_date_active,
1336 end_date_active,
1337 automatic_flag,
1338 currency_code,
1339 rounding_factor,
1340 ship_method_code,
1341 freight_terms_code,
1342 terms_id,
1343 context,
1344 attribute1,
1345 attribute2,
1346 attribute3,
1347 attribute4,
1351 attribute8,
1348 attribute5,
1349 attribute6,
1350 attribute7,
1352 attribute9,
1353 attribute10,
1354 attribute11,
1355 attribute12,
1356 attribute13,
1357 attribute14,
1358 attribute15,
1359 comments,
1360 discount_lines_flag,
1361 gsa_indicator,
1362 prorate_flag,
1363 source_system_code,
1364 active_flag,
1365 parent_list_header_id,
1366 start_date_active_first,
1367 end_date_active_first,
1368 active_date_first_type,
1369 start_date_active_second,
1370 end_date_active_second,
1371 active_date_second_type,
1372 ask_for_flag,
1373 currency_header_id,
1374 pte_code,
1375 global_flag,
1376 orig_org_id,
1377 LIMIT_EXISTS_FLAG,
1378 MOBILE_DOWNLOAD,
1379 LIST_SOURCE_CODE,
1380 ORIG_SYSTEM_HEADER_REF,
1381 SHAREABLE_FLAG,
1382 SOLD_TO_ORG_ID,
1383 ARCH_PURG_REQUEST_ID
1384 )
1385 SELECT
1386 list_header_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_type_code,
1397 start_date_active,
1398 end_date_active,
1399 automatic_flag,
1400 currency_code,
1401 rounding_factor,
1402 ship_method_code,
1403 freight_terms_code,
1404 terms_id,
1405 context,
1406 attribute1,
1407 attribute2,
1408 attribute3,
1409 attribute4,
1410 attribute5,
1411 attribute6,
1412 attribute7,
1413 attribute8,
1414 attribute9,
1415 attribute10,
1416 attribute11,
1417 attribute12,
1418 attribute13,
1419 attribute14,
1420 attribute15,
1421 comments,
1422 discount_lines_flag,
1423 gsa_indicator,
1424 prorate_flag,
1425 source_system_code,
1426 active_flag,
1427 parent_list_header_id,
1428 start_date_active_first,
1429 end_date_active_first,
1430 active_date_first_type,
1431 start_date_active_second,
1432 end_date_active_second,
1433 active_date_second_type,
1434 ask_for_flag,
1435 currency_header_id,
1436 pte_code,
1437 global_flag,
1438 orig_org_id,
1439 LIMIT_EXISTS_FLAG,
1440 MOBILE_DOWNLOAD,
1441 LIST_SOURCE_CODE,
1442 ORIG_SYSTEM_HEADER_REF,
1443 SHAREABLE_FLAG,
1444 SOLD_TO_ORG_ID,
1445 l_conc_request_id
1446 FROM qp_list_headers_b
1447 WHERE list_header_id = p_entity;
1448
1449 --Insert the count of records from QP_ARCH_LIST_HEADERS_B into QP_ARCH_ROW_COUNTS
1450 g_count_header_b:=sql%rowcount;
1451 insert into QP_ARCH_ROW_COUNTS (request_id,table_name,row_count) values (l_conc_request_id,'QP_ARCH_LIST_HEADERS_B',g_count_header_b);
1452
1453 /** Following code inserts pricing entity list header information into QP_ARCH_LIST_HEADERS_TL **/
1454 INSERT INTO QP_ARCH_LIST_HEADERS_TL
1455 (last_update_login,
1456 name,
1457 description,
1458 creation_date,
1459 created_by,
1460 last_update_date,
1461 last_updated_by,
1462 list_header_id,
1463 language,
1464 source_lang,
1465 version_no,
1466 ARCH_PURG_REQUEST_ID
1467 )
1468 SELECT
1469 last_update_login,
1470 name,
1471 description,
1472 creation_date,
1473 created_by,
1474 last_update_date,
1475 last_updated_by,
1476 list_header_id,
1477 language,
1478 source_lang,
1479 version_no,
1480 l_conc_request_id
1481 FROM qp_list_headers_tl
1482 WHERE list_header_id = p_entity;
1483
1484 --Insert the count of records from QP_ARCH_LIST_HEADERS_TL into QP_ARCH_ROW_COUNTS
1485 g_count_header_tl:=sql%rowcount;
1486 insert into QP_ARCH_ROW_COUNTS (request_id,table_name,row_count) values (l_conc_request_id,'QP_ARCH_LIST_HEADERS_TL',g_count_header_tl);
1487
1488 /** Following code inserts pricing entity header qualifier information **/
1489 BEGIN
1490 SELECT LIST_TYPE_CODE
1491 INTO l_list_type_code
1492 FROM QP_LIST_HEADERS_B
1493 WHERE LIST_HEADER_ID = p_entity;
1494
1495 EXCEPTION
1496 WHEN OTHERS THEN
1497 NULL;
1498 END;
1499
1500 --Following code archives Price List header qualifier information
1501 IF l_list_type_code = 'PRL' THEN
1502 IF QP_UTIL.Attrmgr_Installed = 'Y' THEN
1503 QP_UTIL.Get_Context_Attribute('PRICE_LIST', l_context, l_attribute);
1504 ELSE
1505 QP_UTIL.Get_Context_Attribute('PRICE_LIST_ID', l_context, l_attribute);
1506 END IF;
1507
1508 FOR l_qp_qualifiers_rec IN qp_qualifiers_cur(p_entity, l_context,l_attribute)
1509 LOOP
1510 IF l_qp_qualifiers_rec.qualifier_datatype = 'N' THEN
1511 BEGIN
1512 l_qual_attr_value_from_number := qp_number.canonical_to_number(l_qp_qualifiers_rec.qualifier_attr_value);
1513 l_qual_attr_value_to_number := qp_number.canonical_to_number(l_qp_qualifiers_rec.qualifier_attr_value_to);
1514 EXCEPTION
1515 WHEN VALUE_ERROR THEN
1516 NULL;
1517 WHEN OTHERS THEN
1518 NULL;
1519 END;
1520 END IF;
1521
1522 --Insert into QP_ARCH_QUALIFIERS
1523 INSERT INTO QP_ARCH_QUALIFIERS
1524 (
1525 qualifier_id,
1526 creation_date,
1527 created_by,
1528 last_update_date,
1529 last_updated_by,
1530 last_update_login,
1531 program_application_id,
1532 program_id,
1536 comparison_operator_code,
1533 program_update_date,
1534 request_id,
1535 excluder_flag,
1537 qualifier_context,
1538 qualifier_attribute,
1539 context,
1540 attribute1,
1541 attribute2,
1542 attribute3,
1543 attribute4,
1544 attribute5,
1545 attribute6,
1546 attribute7,
1547 attribute8,
1548 attribute9,
1549 attribute10,
1550 attribute11,
1551 attribute12,
1552 attribute13,
1553 attribute14,
1554 attribute15,
1555 qualifier_rule_id,
1556 qualifier_grouping_no,
1557 qualifier_attr_value,
1558 list_header_id,
1559 list_line_id,
1560 created_from_rule_id,
1561 start_date_active,
1562 end_date_active,
1563 qualifier_precedence,
1564 qualifier_datatype,
1565 qualifier_attr_value_to,
1566 active_flag,
1567 list_type_code,
1568 qual_attr_value_from_number,
1569 qual_attr_value_to_number,
1570 search_ind,
1571 distinct_row_count,
1572 qualifier_group_cnt,
1573 header_quals_exist_flag,
1574 OTHERS_GROUP_CNT,
1575 SEGMENT_ID,
1576 ARCH_PURG_REQUEST_ID
1577 )
1578 VALUES
1579 (
1580 l_qp_qualifiers_rec.qualifier_id,
1581 l_qp_qualifiers_rec.creation_date,
1582 l_qp_qualifiers_rec.created_by,
1583 l_qp_qualifiers_rec.last_update_date,
1584 l_qp_qualifiers_rec.last_updated_by,
1585 l_qp_qualifiers_rec.last_update_login,
1586 l_qp_qualifiers_rec.program_application_id,
1587 l_qp_qualifiers_rec.program_id,
1588 l_qp_qualifiers_rec.program_update_date,
1589 l_qp_qualifiers_rec.request_id,
1590 l_qp_qualifiers_rec.excluder_flag,
1591 l_qp_qualifiers_rec.comparison_operator_code,
1592 l_qp_qualifiers_rec.qualifier_context,
1593 l_qp_qualifiers_rec.qualifier_attribute,
1594 l_qp_qualifiers_rec.context,
1595 l_qp_qualifiers_rec.attribute1,
1596 l_qp_qualifiers_rec.attribute2,
1597 l_qp_qualifiers_rec.attribute3,
1598 l_qp_qualifiers_rec.attribute4,
1599 l_qp_qualifiers_rec.attribute5,
1600 l_qp_qualifiers_rec.attribute6,
1601 l_qp_qualifiers_rec.attribute7,
1602 l_qp_qualifiers_rec.attribute8,
1603 l_qp_qualifiers_rec.attribute9,
1604 l_qp_qualifiers_rec.attribute10,
1605 l_qp_qualifiers_rec.attribute11,
1606 l_qp_qualifiers_rec.attribute12,
1607 l_qp_qualifiers_rec.attribute13,
1608 l_qp_qualifiers_rec.attribute14,
1609 l_qp_qualifiers_rec.attribute15,
1610 l_qp_qualifiers_rec.qualifier_rule_id,
1611 l_qp_qualifiers_rec.qualifier_grouping_no,
1612 l_qp_qualifiers_rec.qualifier_attr_value,
1613 l_qp_qualifiers_rec.list_header_id,
1614 l_qp_qualifiers_rec.list_line_id,
1615 l_qp_qualifiers_rec.created_from_rule_id,
1616 l_qp_qualifiers_rec.start_date_active,
1617 l_qp_qualifiers_rec.end_date_active,
1618 l_qp_qualifiers_rec.qualifier_precedence,
1619 l_qp_qualifiers_rec.qualifier_datatype,
1620 l_qp_qualifiers_rec.qualifier_attr_value_to,
1621 l_qp_qualifiers_rec.active_flag,
1622 l_qp_qualifiers_rec.list_type_code,
1623 l_qual_attr_value_from_number,
1624 l_qual_attr_value_to_number,
1625 l_qp_qualifiers_rec.search_ind,
1626 l_qp_qualifiers_rec.distinct_row_count,
1627 l_qp_qualifiers_rec.qualifier_group_cnt,
1628 l_qp_qualifiers_rec.header_quals_exist_flag,
1629 l_qp_qualifiers_rec.others_group_cnt,
1630 l_qp_qualifiers_rec.segment_id,
1631 l_conc_request_id
1632 );
1633
1634 g_count_qualifier := g_count_qualifier + sql%rowcount;
1635 END LOOP;
1636
1637 ELSE --Modifier and agreement price list
1638 --Following code archives the Modifier header qualifier information
1639 FOR l_qp_qualifiers_rec IN qp_headers_qualifiers_cur(p_entity)
1640 LOOP
1641 IF l_qp_qualifiers_rec.qualifier_datatype = 'N' THEN
1642 BEGIN
1643 l_qual_attr_value_from_number := qp_number.canonical_to_number(l_qp_qualifiers_rec.qualifier_attr_value);
1644 l_qual_attr_value_to_number := qp_number.canonical_to_number(l_qp_qualifiers_rec.qualifier_attr_value_to);
1645 EXCEPTION
1646 WHEN VALUE_ERROR THEN
1647 NULL;
1648 WHEN OTHERS THEN
1649 NULL;
1650 END;
1651 END IF;
1652
1653 --Insert into QP_ARCH_QUALIFIERS
1654 INSERT INTO QP_ARCH_QUALIFIERS
1655 (
1656 qualifier_id,
1657 creation_date,
1658 created_by,
1659 last_update_date,
1660 last_updated_by,
1661 last_update_login,
1662 program_application_id,
1663 program_id,
1664 program_update_date,
1665 request_id,
1666 excluder_flag,
1667 comparison_operator_code,
1668 qualifier_context,
1669 qualifier_attribute,
1670 context,
1671 attribute1,
1672 attribute2,
1673 attribute3,
1674 attribute4,
1675 attribute5,
1676 attribute6,
1677 attribute7,
1678 attribute8,
1679 attribute9,
1680 attribute10,
1681 attribute11,
1682 attribute12,
1683 attribute13,
1684 attribute14,
1688 qualifier_attr_value,
1685 attribute15,
1686 qualifier_rule_id,
1687 qualifier_grouping_no,
1689 list_header_id,
1690 list_line_id,
1691 created_from_rule_id,
1692 start_date_active,
1693 end_date_active,
1694 qualifier_precedence,
1695 qualifier_datatype,
1696 qualifier_attr_value_to,
1697 active_flag,
1698 list_type_code,
1699 qual_attr_value_from_number,
1700 qual_attr_value_to_number,
1701 search_ind,
1702 distinct_row_count,
1703 qualifier_group_cnt,
1704 header_quals_exist_flag,
1705 OTHERS_GROUP_CNT,
1706 segment_id,
1707 ARCH_PURG_REQUEST_ID
1708 )
1709 VALUES
1710 (
1711 l_qp_qualifiers_rec.qualifier_id,
1712 l_qp_qualifiers_rec.creation_date,
1713 l_qp_qualifiers_rec.created_by,
1714 l_qp_qualifiers_rec.last_update_date,
1715 l_qp_qualifiers_rec.last_updated_by,
1716 l_qp_qualifiers_rec.last_update_login,
1717 l_qp_qualifiers_rec.program_application_id,
1718 l_qp_qualifiers_rec.program_id,
1719 l_qp_qualifiers_rec.program_update_date,
1720 l_qp_qualifiers_rec.request_id,
1721 l_qp_qualifiers_rec.excluder_flag,
1722 l_qp_qualifiers_rec.comparison_operator_code,
1723 l_qp_qualifiers_rec.qualifier_context,
1724 l_qp_qualifiers_rec.qualifier_attribute,
1725 l_qp_qualifiers_rec.context,
1726 l_qp_qualifiers_rec.attribute1,
1727 l_qp_qualifiers_rec.attribute2,
1728 l_qp_qualifiers_rec.attribute3,
1729 l_qp_qualifiers_rec.attribute4,
1730 l_qp_qualifiers_rec.attribute5,
1731 l_qp_qualifiers_rec.attribute6,
1732 l_qp_qualifiers_rec.attribute7,
1733 l_qp_qualifiers_rec.attribute8,
1734 l_qp_qualifiers_rec.attribute9,
1735 l_qp_qualifiers_rec.attribute10,
1736 l_qp_qualifiers_rec.attribute11,
1737 l_qp_qualifiers_rec.attribute12,
1738 l_qp_qualifiers_rec.attribute13,
1739 l_qp_qualifiers_rec.attribute14,
1740 l_qp_qualifiers_rec.attribute15,
1741 l_qp_qualifiers_rec.qualifier_rule_id,
1742 l_qp_qualifiers_rec.qualifier_grouping_no,
1743 l_qp_qualifiers_rec.qualifier_attr_value,
1744 l_qp_qualifiers_rec.list_header_id,
1745 l_qp_qualifiers_rec.list_line_id,
1746 l_qp_qualifiers_rec.created_from_rule_id,
1747 l_qp_qualifiers_rec.start_date_active,
1748 l_qp_qualifiers_rec.end_date_active,
1749 l_qp_qualifiers_rec.qualifier_precedence,
1750 l_qp_qualifiers_rec.qualifier_datatype,
1751 l_qp_qualifiers_rec.qualifier_attr_value_to,
1752 l_qp_qualifiers_rec.active_flag,
1753 l_qp_qualifiers_rec.list_type_code,
1754 l_qual_attr_value_from_number,
1755 l_qual_attr_value_to_number,
1756 l_qp_qualifiers_rec.search_ind,
1757 l_qp_qualifiers_rec.distinct_row_count,
1758 l_qp_qualifiers_rec.qualifier_group_cnt,
1759 l_qp_qualifiers_rec.header_quals_exist_flag,
1760 l_qp_qualifiers_rec.others_group_cnt,
1761 l_qp_qualifiers_rec.segment_id,
1762 l_conc_request_id
1763 );
1764
1765 g_count_qualifier := g_count_qualifier + sql%rowcount;
1766 END LOOP;
1767
1768 END IF;
1769
1770 /** Following code Archives price list lines information.**/
1771 OPEN qp_list_lines_cv FOR l_select_stmt USING p_entity,p_start_date_active,p_end_date_active,p_created_by,p_creation_date;
1772 LOOP
1773 FETCH qp_list_lines_cv INTO l_qp_list_lines_rec;
1774 EXIT WHEN qp_list_lines_cv%NOTFOUND;
1775
1776 l_insert_flag := 'N'; --Reset the flag
1777
1778 --Check if limit exists
1779 IF nvl(l_qp_list_lines_rec.LIMIT_EXISTS_FLAG,'N') = 'N' THEN
1780 l_insert_flag :='Y';
1781
1782 -- Check if line part of a formula
1783 SELECT count(*) into l_cnt
1784 FROM QP_PRICE_FORMULA_LINES
1785 WHERE PRICE_FORMULA_LINE_TYPE_CODE = 'PLL'
1786 AND PRICE_LIST_LINE_ID=l_qp_list_lines_rec.list_line_id;
1787
1788 IF l_cnt=0 THEN
1789 l_insert_flag :='Y';
1790 ELSE
1791 l_insert_flag :='N';
1792 fnd_file.put_line(FND_FILE.LOG,'Price List line used in formula.Do not archive : '||l_qp_list_lines_rec.list_line_id);
1793 l_err_count := l_err_count+1;
1794 END IF;
1795
1796 IF l_insert_flag ='Y' THEN
1797 --Check if line a Coupon or a Benefit line or a parent to the same
1798 IF l_list_type_code = 'PRO' or l_list_type_code = 'DEL' THEN
1799 IF (l_qp_list_lines_rec.list_line_type_code = 'CIE') OR
1800 LINE_EXISTS_IN_RLTD(l_qp_list_lines_rec.list_line_id) THEN
1801 l_insert_flag :='N';
1802 fnd_file.put_line(FND_FILE.LOG,'COUPON or related BENEFIT line.Do not archive : '||l_qp_list_lines_rec.list_line_id);
1803 l_err_count := l_err_count+1;
1804 END IF;
1805 END IF;
1806 END IF;
1807
1808 IF l_insert_flag ='Y' THEN
1809 INSERT INTO QP_ARCH_LIST_LINES
1810 (
1811 list_line_id,
1812 creation_date,
1813 created_by,
1814 last_update_date,
1815 last_updated_by,
1816 last_update_login,
1817 program_application_id,
1818 program_id,
1819 program_update_date,
1820 request_id,
1821 list_header_id,
1822 list_line_type_code,
1823 start_date_active,
1824 end_date_active,
1825 automatic_flag,
1829 inventory_item_id,
1826 modifier_level_code,
1827 list_price,
1828 primary_uom_flag,
1830 organization_id,
1831 related_item_id,
1832 relationship_type_id,
1833 substitution_context,
1834 substitution_attribute,
1835 substitution_value,
1836 revision,
1837 revision_date,
1838 revision_reason_code,
1839 context,
1840 attribute1,
1841 attribute2,
1842 comments,
1843 attribute3,
1844 attribute4,
1845 attribute5,
1846 attribute6,
1847 attribute7,
1848 attribute8,
1849 attribute9,
1850 attribute10,
1851 attribute11,
1852 attribute12,
1853 attribute13,
1854 attribute14,
1855 attribute15,
1856 price_break_type_code,
1857 percent_price,
1858 price_by_formula_id,
1859 number_effective_periods,
1860 effective_period_uom,
1861 arithmetic_operator,
1862 operand,
1863 override_flag,
1864 print_on_invoice_flag,
1865 rebate_transaction_type_code,
1866 estim_accrual_rate,
1867 generate_using_formula_id,
1868 reprice_flag,
1869 accrual_flag,
1870 pricing_group_sequence,
1871 incompatibility_grp_code,
1872 list_line_no,
1873 product_precedence,
1874 pricing_phase_id,
1875 expiration_period_start_date,
1876 number_expiration_periods,
1877 expiration_period_uom,
1878 expiration_date,
1879 estim_gl_value,
1880 accrual_conversion_rate,
1881 benefit_price_list_line_id,
1885 charge_type_code,
1882 proration_type_code,
1883 benefit_qty,
1884 benefit_uom_code,
1886 charge_subtype_code,
1887 benefit_limit,
1888 include_on_returns_flag,
1889 qualification_ind,
1890 recurring_value,
1891 LIST_PRICE_UOM_CODE,
1892 BASE_QTY,
1893 BASE_UOM_CODE,
1894 ACCRUAL_QTY,
1895 ACCRUAL_UOM_CODE,
1896 RECURRING_FLAG,
1897 LIMIT_EXISTS_FLAG,
1898 GROUP_COUNT,
1899 NET_AMOUNT_FLAG,
1900 CUSTOMER_ITEM_ID
1901 ,ACCUM_CONTEXT
1902 ,ACCUM_ATTRIBUTE
1903 ,ACCUM_ATTR_RUN_SRC_FLAG
1904 ,BREAK_UOM_CODE
1905 ,BREAK_UOM_CONTEXT
1906 ,BREAK_UOM_ATTRIBUTE
1907 ,PATTERN_ID
1908 ,PRODUCT_UOM_CODE
1909 ,PRICING_ATTRIBUTE_COUNT
1910 ,HASH_KEY
1911 ,CACHE_KEY
1912 ,ARCH_PURG_REQUEST_ID
1913 )
1914 VALUES
1915 (
1916 l_qp_list_lines_rec.list_line_id,
1917 l_qp_list_lines_rec.creation_date,
1918 l_qp_list_lines_rec.created_by,
1919 l_qp_list_lines_rec.last_update_date,
1920 l_qp_list_lines_rec.last_updated_by,
1921 l_qp_list_lines_rec.last_update_login,
1922 l_qp_list_lines_rec.program_application_id,
1923 l_qp_list_lines_rec.program_id,
1924 l_qp_list_lines_rec.program_update_date,
1925 l_qp_list_lines_rec.request_id,
1926 p_entity,
1927 l_qp_list_lines_rec.list_line_type_code,
1928 l_qp_list_lines_rec.start_date_active,
1929 l_qp_list_lines_rec.end_date_active,
1930 l_qp_list_lines_rec.automatic_flag,
1931 l_qp_list_lines_rec.modifier_level_code,
1932 l_qp_list_lines_rec.list_price,
1933 l_qp_list_lines_rec.primary_uom_flag,
1934 l_qp_list_lines_rec.inventory_item_id,
1935 l_qp_list_lines_rec.organization_id,
1936 l_qp_list_lines_rec.related_item_id,
1937 l_qp_list_lines_rec.relationship_type_id,
1938 l_qp_list_lines_rec.substitution_context,
1939 l_qp_list_lines_rec.substitution_attribute,
1940 l_qp_list_lines_rec.substitution_value,
1941 l_qp_list_lines_rec.revision,
1942 l_qp_list_lines_rec.revision_date,
1943 l_qp_list_lines_rec.revision_reason_code,
1944 l_qp_list_lines_rec.context,
1945 l_qp_list_lines_rec.attribute1,
1949 l_qp_list_lines_rec.attribute4,
1946 l_qp_list_lines_rec.attribute2,
1947 l_qp_list_lines_rec.comments,
1948 l_qp_list_lines_rec.attribute3,
1950 l_qp_list_lines_rec.attribute5,
1951 l_qp_list_lines_rec.attribute6,
1952 l_qp_list_lines_rec.attribute7,
1953 l_qp_list_lines_rec.attribute8,
1954 l_qp_list_lines_rec.attribute9,
1955 l_qp_list_lines_rec.attribute10,
1956 l_qp_list_lines_rec.attribute11,
1957 l_qp_list_lines_rec.attribute12,
1958 l_qp_list_lines_rec.attribute13,
1959 l_qp_list_lines_rec.attribute14,
1960 l_qp_list_lines_rec.attribute15,
1961 l_qp_list_lines_rec.price_break_type_code,
1962 l_qp_list_lines_rec.percent_price,
1963 l_qp_list_lines_rec.price_by_formula_id,
1964 l_qp_list_lines_rec.number_effective_periods,
1965 l_qp_list_lines_rec.effective_period_uom,
1966 l_qp_list_lines_rec.arithmetic_operator,
1967 l_qp_list_lines_rec.operand,
1968 l_qp_list_lines_rec.override_flag,
1969 l_qp_list_lines_rec.print_on_invoice_flag,
1970 l_qp_list_lines_rec.rebate_transaction_type_code,
1971 l_qp_list_lines_rec.estim_accrual_rate,
1972 l_qp_list_lines_rec.generate_using_formula_id,
1973 l_qp_list_lines_rec.reprice_flag,
1974 l_qp_list_lines_rec.accrual_flag,
1975 l_qp_list_lines_rec.pricing_group_sequence,
1976 l_qp_list_lines_rec.incompatibility_grp_code,
1977 l_qp_list_lines_rec.list_line_no,
1978 l_qp_list_lines_rec.product_precedence,
1979 l_qp_list_lines_rec.pricing_phase_id,
1980 l_qp_list_lines_rec.expiration_period_start_date,
1981 l_qp_list_lines_rec.number_expiration_periods,
1982 l_qp_list_lines_rec.expiration_period_uom,
1983 l_qp_list_lines_rec.expiration_date,
1984 l_qp_list_lines_rec.estim_gl_value,
1985 l_qp_list_lines_rec.accrual_conversion_rate,
1986 l_qp_list_lines_rec.benefit_price_list_line_id,
1987 l_qp_list_lines_rec.proration_type_code,
1988 l_qp_list_lines_rec.benefit_qty,
1989 l_qp_list_lines_rec.benefit_uom_code,
1990 l_qp_list_lines_rec.charge_type_code,
1991 l_qp_list_lines_rec.charge_subtype_code,
1992 l_qp_list_lines_rec.benefit_limit,
1993 l_qp_list_lines_rec.include_on_returns_flag,
1994 l_qp_list_lines_rec.qualification_ind,
1995 l_qp_list_lines_rec.recurring_value,
1996 l_qp_list_lines_rec.LIST_PRICE_UOM_CODE,
1997 l_qp_list_lines_rec.BASE_QTY,
1998 l_qp_list_lines_rec.BASE_UOM_CODE,
1999 l_qp_list_lines_rec.ACCRUAL_QTY,
2000 l_qp_list_lines_rec.ACCRUAL_UOM_CODE,
2001 l_qp_list_lines_rec.RECURRING_FLAG,
2002 l_qp_list_lines_rec.LIMIT_EXISTS_FLAG,
2003 l_qp_list_lines_rec.GROUP_COUNT,
2004 l_qp_list_lines_rec.NET_AMOUNT_FLAG,
2005 l_qp_list_lines_rec.CUSTOMER_ITEM_ID,
2006 l_qp_list_lines_rec.ACCUM_CONTEXT,
2007 l_qp_list_lines_rec.ACCUM_ATTRIBUTE,
2008 l_qp_list_lines_rec.ACCUM_ATTR_RUN_SRC_FLAG,
2009 l_qp_list_lines_rec.BREAK_UOM_CODE,
2010 l_qp_list_lines_rec.BREAK_UOM_CONTEXT,
2011 l_qp_list_lines_rec.BREAK_UOM_ATTRIBUTE,
2012 l_qp_list_lines_rec.PATTERN_ID ,
2013 l_qp_list_lines_rec.PRODUCT_UOM_CODE,
2014 l_qp_list_lines_rec.PRICING_ATTRIBUTE_COUNT,
2015 l_qp_list_lines_rec.HASH_KEY ,
2016 l_qp_list_lines_rec.CACHE_KEY,
2017 l_conc_request_id
2018 );
2019
2020 g_count_list_line:=g_count_list_line + sql%rowcount;
2021
2022 /*If the list_line_rec is a Price Break Parent Line or a promotional or other item discount parent line then
2023 store the list line id in a mapping-array for later use*/
2024
2025 IF l_qp_list_lines_rec.list_line_type_code = 'PBH' OR
2026 l_qp_list_lines_rec.list_line_type_code = 'OID' OR
2030 l_mapping_tbl(l_count).list_line_type_code := l_qp_list_lines_rec.list_line_type_code;
2027 l_qp_list_lines_rec.list_line_type_code = 'PRG' THEN
2028
2029 l_count := l_count + 1;
2031 l_mapping_tbl(l_count).list_line_id := l_qp_list_lines_rec.list_line_id;
2032 END IF;
2033
2034 DELETE FROM QP_LIST_LINES WHERE list_line_id = l_qp_list_lines_rec.list_line_id and list_header_id = p_entity;
2035
2036 /*Insert line level qualifiers in case of Modifiers */
2037 IF l_list_type_code NOT IN ('PRL','AGR') THEN
2038 IF l_qp_list_lines_rec.list_line_id is not null AND
2039 l_qp_list_lines_rec.list_line_id <> -1 THEN
2040
2041 FOR l_qp_qualifiers_rec IN qp_line_qualifiers_cur(p_entity,l_qp_list_lines_rec.list_line_id)
2042 LOOP
2043 IF l_qp_qualifiers_rec.qualifier_datatype = 'N' then
2044 BEGIN
2045 l_qual_attr_value_from_number := qp_number.canonical_to_number(l_qp_qualifiers_rec.qualifier_attr_value);
2046 l_qual_attr_value_to_number := qp_number.canonical_to_number(l_qp_qualifiers_rec.qualifier_attr_value_to);
2047 EXCEPTION
2048 WHEN VALUE_ERROR THEN
2049 NULL;
2050 WHEN OTHERS THEN
2051 NULL;
2052 END;
2053 END IF;
2054
2055 --Insert into qp_qualifiers
2056 INSERT INTO QP_ARCH_QUALIFIERS
2057 (
2058 qualifier_id,
2059 creation_date,
2060 created_by,
2061 last_update_date,
2062 last_updated_by,
2063 last_update_login,
2064 program_application_id,
2065 program_id,
2066 program_update_date,
2067 request_id,
2068 excluder_flag,
2069 comparison_operator_code,
2070 qualifier_context,
2071 qualifier_attribute,
2072 context,
2073 attribute1,
2074 attribute2,
2075 attribute3,
2076 attribute4,
2077 attribute5,
2078 attribute6,
2079 attribute7,
2080 attribute8,
2081 attribute9,
2082 attribute10,
2083 attribute11,
2084 attribute12,
2085 attribute13,
2086 attribute14,
2087 attribute15,
2088 qualifier_rule_id,
2089 qualifier_grouping_no,
2090 qualifier_attr_value,
2091 list_header_id,
2092 list_line_id,
2093 created_from_rule_id,
2094 start_date_active,
2095 end_date_active,
2096 qualifier_precedence,
2097 qualifier_datatype,
2098 qualifier_attr_value_to,
2099 active_flag,
2100 list_type_code,
2101 qual_attr_value_from_number,
2102 qual_attr_value_to_number,
2103 search_ind,
2104 distinct_row_count,
2105 qualifier_group_cnt,
2106 header_quals_exist_flag,
2107 OTHERS_GROUP_CNT,
2108 segment_id,
2109 ARCH_PURG_REQUEST_ID
2110 )
2111 VALUES
2112 (
2113 l_qp_qualifiers_rec.qualifier_id,
2114 l_qp_qualifiers_rec.creation_date,
2115 l_qp_qualifiers_rec.created_by,
2116 l_qp_qualifiers_rec.last_update_date,
2117 l_qp_qualifiers_rec.last_updated_by,
2118 l_qp_qualifiers_rec.last_update_login,
2119 l_qp_qualifiers_rec.program_application_id,
2120 l_qp_qualifiers_rec.program_id,
2121 l_qp_qualifiers_rec.program_update_date,
2122 l_qp_qualifiers_rec.request_id,
2123 l_qp_qualifiers_rec.excluder_flag,
2124 l_qp_qualifiers_rec.comparison_operator_code,
2125 l_qp_qualifiers_rec.qualifier_context,
2126 l_qp_qualifiers_rec.qualifier_attribute,
2127 l_qp_qualifiers_rec.context,
2128 l_qp_qualifiers_rec.attribute1,
2129 l_qp_qualifiers_rec.attribute2,
2130 l_qp_qualifiers_rec.attribute3,
2131 l_qp_qualifiers_rec.attribute4,
2132 l_qp_qualifiers_rec.attribute5,
2133 l_qp_qualifiers_rec.attribute6,
2134 l_qp_qualifiers_rec.attribute7,
2138 l_qp_qualifiers_rec.attribute11,
2135 l_qp_qualifiers_rec.attribute8,
2136 l_qp_qualifiers_rec.attribute9,
2137 l_qp_qualifiers_rec.attribute10,
2139 l_qp_qualifiers_rec.attribute12,
2140 l_qp_qualifiers_rec.attribute13,
2141 l_qp_qualifiers_rec.attribute14,
2142 l_qp_qualifiers_rec.attribute15,
2143 l_qp_qualifiers_rec.qualifier_rule_id,
2144 l_qp_qualifiers_rec.qualifier_grouping_no,
2145 l_qp_qualifiers_rec.qualifier_attr_value,
2146 l_qp_qualifiers_rec.list_header_id,
2147 l_qp_qualifiers_rec.list_line_id,
2148 l_qp_qualifiers_rec.created_from_rule_id,
2149 l_qp_qualifiers_rec.start_date_active,
2150 l_qp_qualifiers_rec.end_date_active,
2151 l_qp_qualifiers_rec.qualifier_precedence,
2152 l_qp_qualifiers_rec.qualifier_datatype,
2153 l_qp_qualifiers_rec.qualifier_attr_value_to,
2154 l_qp_qualifiers_rec.active_flag,
2155 l_qp_qualifiers_rec.list_type_code,
2156 l_qual_attr_value_from_number,
2157 l_qual_attr_value_to_number,
2158 l_qp_qualifiers_rec.search_ind,
2159 l_qp_qualifiers_rec.distinct_row_count,
2160 l_qp_qualifiers_rec.qualifier_group_cnt,
2161 l_qp_qualifiers_rec.header_quals_exist_flag,
2162 l_qp_qualifiers_rec.others_group_cnt,
2163 l_qp_qualifiers_rec.segment_id,
2164 l_conc_request_id
2168
2165 );
2166
2167 g_count_qualifier := g_count_qualifier + sql%rowcount;
2169 DELETE FROM qp_qualifiers
2170 WHERE qualifier_id = l_qp_qualifiers_rec.qualifier_id
2171 and list_header_id = l_qp_qualifiers_rec.list_header_id
2172 and list_line_id = l_qp_qualifiers_rec.list_line_id;
2173 END LOOP;
2174
2175 END IF;
2176 END IF; -- List type code end
2177
2178 /* Archive List lines pricing attributes */
2179 FOR l_qp_pricing_attributes_rec IN qp_pricing_attributes_cur (l_qp_list_lines_rec.list_line_id) LOOP
2180 IF l_qp_pricing_attributes_rec.pricing_attribute_datatype = 'N' then
2181 BEGIN
2182 l_pric_attr_value_from_number := qp_number.canonical_to_number(l_qp_pricing_attributes_rec.pricing_attr_value_from);
2183 l_pric_attr_value_to_number := qp_number.canonical_to_number(l_qp_pricing_attributes_rec.pricing_attr_value_to);
2184 EXCEPTION
2185 WHEN VALUE_ERROR THEN
2186 NULL;
2187 WHEN OTHERS THEN
2188 NULL;
2189 END;
2190 END IF;
2191
2192 -- Insert into QP_ARCH_PRICING_ATTRIBUTES
2193 INSERT INTO QP_ARCH_PRICING_ATTRIBUTES
2194 (
2195 pricing_attribute_id,
2196 creation_date,
2197 created_by,
2198 last_update_date,
2199 last_updated_by,
2200 last_update_login,
2201 program_application_id,
2202 program_id,
2203 program_update_date,
2204 request_id,
2205 list_line_id,
2206 list_header_id,
2207 pricing_phase_id,
2208 qualification_ind,
2209 excluder_flag,
2210 accumulate_flag,
2211 product_attribute_context,
2212 product_attribute,
2213 product_attr_value,
2214 product_uom_code,
2215 pricing_attribute_context,
2216 pricing_attribute,
2217 pricing_attr_value_from,
2218 pricing_attr_value_to,
2219 attribute_grouping_no,
2220 context,
2221 attribute1,
2222 attribute2,
2223 attribute3,
2224 attribute4,
2225 attribute5,
2226 attribute6,
2227 attribute7,
2228 attribute8,
2229 attribute9,
2230 attribute10,
2231 attribute11,
2232 attribute12,
2233 attribute13,
2234 attribute14,
2235 attribute15,
2236 product_attribute_datatype,
2237 pricing_attribute_datatype,
2238 comparison_operator_code,
2239 pricing_attr_value_from_number,
2240 pricing_attr_value_to_number,
2241 DISTINCT_ROW_COUNT,
2242 SEARCH_IND,
2243 PATTERN_VALUE_FROM_POSITIVE,
2247 PRODUCT_SEGMENT_ID,
2244 PATTERN_VALUE_TO_POSITIVE,
2245 PATTERN_VALUE_FROM_NEGATIVE,
2246 PATTERN_VALUE_TO_NEGATIVE,
2248 PRICING_SEGMENT_ID,
2249 ARCH_PURG_REQUEST_ID
2250 )
2251 VALUES
2252 (
2253 l_qp_pricing_attributes_rec.pricing_attribute_id,
2254 l_qp_pricing_attributes_rec.creation_date,
2255 l_qp_pricing_attributes_rec.created_by,
2256 l_qp_pricing_attributes_rec.last_update_date,
2257 l_qp_pricing_attributes_rec.last_updated_by,
2258 l_qp_pricing_attributes_rec.last_update_login,
2259 l_qp_pricing_attributes_rec.program_application_id,
2260 l_qp_pricing_attributes_rec.program_id,
2261 l_qp_pricing_attributes_rec.program_update_date,
2262 l_qp_pricing_attributes_rec.request_id,
2263 l_qp_list_lines_rec.list_line_id,
2264 p_entity,
2265 l_qp_pricing_attributes_rec.pricing_phase_id,
2266 l_qp_pricing_attributes_rec.qualification_ind,
2267 l_qp_pricing_attributes_rec.excluder_flag,
2268 l_qp_pricing_attributes_rec.accumulate_flag,
2269 l_qp_pricing_attributes_rec.product_attribute_context,
2270 l_qp_pricing_attributes_rec.product_attribute,
2271 l_qp_pricing_attributes_rec.product_attr_value,
2272 l_qp_pricing_attributes_rec.product_uom_code,
2273 l_qp_pricing_attributes_rec.pricing_attribute_context,
2274 l_qp_pricing_attributes_rec.pricing_attribute,
2275 l_qp_pricing_attributes_rec.pricing_attr_value_from,
2276 l_qp_pricing_attributes_rec.pricing_attr_value_to,
2277 l_qp_pricing_attributes_rec.attribute_grouping_no,
2278 l_qp_pricing_attributes_rec.context,
2279 l_qp_pricing_attributes_rec.attribute1,
2280 l_qp_pricing_attributes_rec.attribute2,
2281 l_qp_pricing_attributes_rec.attribute3,
2282 l_qp_pricing_attributes_rec.attribute4,
2283 l_qp_pricing_attributes_rec.attribute5,
2284 l_qp_pricing_attributes_rec.attribute6,
2285 l_qp_pricing_attributes_rec.attribute7,
2286 l_qp_pricing_attributes_rec.attribute8,
2287 l_qp_pricing_attributes_rec.attribute9,
2288 l_qp_pricing_attributes_rec.attribute10,
2289 l_qp_pricing_attributes_rec.attribute11,
2290 l_qp_pricing_attributes_rec.attribute12,
2291 l_qp_pricing_attributes_rec.attribute13,
2292 l_qp_pricing_attributes_rec.attribute14,
2293 l_qp_pricing_attributes_rec.attribute15,
2294 l_qp_pricing_attributes_rec.product_attribute_datatype,
2295 l_qp_pricing_attributes_rec.pricing_attribute_datatype,
2296 l_qp_pricing_attributes_rec.comparison_operator_code,
2297 l_pric_attr_value_from_number,
2298 l_pric_attr_value_to_number,
2299 l_qp_pricing_attributes_rec.DISTINCT_ROW_COUNT,
2300 l_qp_pricing_attributes_rec.SEARCH_IND,
2301 l_qp_pricing_attributes_rec.PATTERN_VALUE_FROM_POSITIVE,
2302 l_qp_pricing_attributes_rec.PATTERN_VALUE_TO_POSITIVE,
2303 l_qp_pricing_attributes_rec.PATTERN_VALUE_FROM_NEGATIVE,
2304 l_qp_pricing_attributes_rec.PATTERN_VALUE_TO_NEGATIVE,
2305 l_qp_pricing_attributes_rec.PRODUCT_SEGMENT_ID,
2306 l_qp_pricing_attributes_rec.PRICING_SEGMENT_ID,
2307 l_conc_request_id
2308 );
2309
2310 g_count_pricing_att := g_count_pricing_att + sql%rowcount;
2311
2312 ---bug#13045009
2313 /* DELETE FROM qp_pricing_attributes
2314 WHERE pricing_attribute_id = l_qp_pricing_attributes_rec.pricing_attribute_id
2318 END LOOP; /* Cursor qp_pricing_attributes_cur LOOP */
2315 AND list_line_id = l_qp_list_lines_rec.list_line_id and list_header_id = p_entity; */
2316 ---bug#13045009
2317
2319 -- fnd_file.put_line(FND_FILE.LOG,'JP TEST --' || l_id_count);
2320 l_list_line_id_tbl(l_id_count) := l_qp_list_lines_rec.list_line_id;
2321 l_id_count := l_id_count+1;
2322
2323 END IF; --Insert flag
2324 ELSE --Limit exists
2325 fnd_file.put_line(FND_FILE.LOG,'Limit exists for this line and it cannot be archived : '||l_qp_list_lines_rec.list_line_id);
2326 l_err_count := l_err_count+1;
2327 END IF;
2328 END LOOP; /* Cursor qp_list_lines_cv LOOP */
2329 ---bug#13045009
2330 FORALL i IN l_list_line_id_tbl.first .. l_list_line_id_tbl.last
2331 DELETE FROM QP_PRICING_ATTRIBUTES
2332 WHERE list_line_id = l_list_line_id_tbl(i)
2333 AND list_header_id = p_entity;
2334 ---bug#13045009
2335 CLOSE qp_list_lines_cv;
2336
2337 /* Archive the qp_rltd_modifiers for the Price Break Parent list_lines chosen
2338 above which are stored in the mapping table */
2339
2340 IF l_mapping_tbl.COUNT > 0 THEN
2341 FOR l_count IN 1..l_mapping_tbl.COUNT
2342 LOOP
2343 IF l_mapping_tbl(l_count).list_line_type_code = 'PBH' OR
2344 l_mapping_tbl(l_count).list_line_type_code = 'OID' OR
2345 l_mapping_tbl(l_count).list_line_type_code = 'PRG' THEN
2346
2347 FOR l_qp_rltd_modifiers_rec IN qp_rltd_modifiers_cur(l_mapping_tbl(l_count).list_line_id)
2348 LOOP
2349 INSERT INTO QP_ARCH_RLTD_MODIFIERS
2350 (
2351 creation_date,
2352 created_by,
2353 last_update_date,
2354 last_updated_by,
2355 last_update_login,
2356 context,
2357 attribute1,
2358 attribute2,
2359 attribute3,
2360 attribute4,
2361 attribute5,
2362 attribute6,
2363 attribute7,
2364 attribute8,
2365 attribute9,
2366 attribute10,
2367 attribute11,
2368 attribute12,
2369 attribute13,
2370 attribute14,
2371 attribute15,
2372 rltd_modifier_id,
2373 rltd_modifier_grp_no,
2374 from_rltd_modifier_id,
2375 to_rltd_modifier_id,
2376 rltd_modifier_grp_type,
2377 ARCH_PURG_REQUEST_ID
2378 )
2379 VALUES
2380 (
2381 l_qp_rltd_modifiers_rec.creation_date,
2382 l_qp_rltd_modifiers_rec.created_by,
2383 l_qp_rltd_modifiers_rec.last_update_date,
2384 l_qp_rltd_modifiers_rec.last_updated_by,
2385 l_qp_rltd_modifiers_rec.last_update_login,
2386 l_qp_rltd_modifiers_rec.context,
2387 l_qp_rltd_modifiers_rec.attribute1,
2388 l_qp_rltd_modifiers_rec.attribute2,
2389 l_qp_rltd_modifiers_rec.attribute3,
2390 l_qp_rltd_modifiers_rec.attribute4,
2391 l_qp_rltd_modifiers_rec.attribute5,
2392 l_qp_rltd_modifiers_rec.attribute6,
2393 l_qp_rltd_modifiers_rec.attribute7,
2394 l_qp_rltd_modifiers_rec.attribute8,
2395 l_qp_rltd_modifiers_rec.attribute9,
2396 l_qp_rltd_modifiers_rec.attribute10,
2397 l_qp_rltd_modifiers_rec.attribute11,
2398 l_qp_rltd_modifiers_rec.attribute12,
2399 l_qp_rltd_modifiers_rec.attribute13,
2400 l_qp_rltd_modifiers_rec.attribute14,
2401 l_qp_rltd_modifiers_rec.attribute15,
2402 l_qp_rltd_modifiers_rec.rltd_modifier_id,
2403 l_qp_rltd_modifiers_rec.rltd_modifier_grp_no,
2404 l_qp_rltd_modifiers_rec.from_rltd_modifier_id,
2405 l_qp_rltd_modifiers_rec.to_rltd_modifier_id,
2406 l_qp_rltd_modifiers_rec.rltd_modifier_grp_type,
2407 l_conc_request_id
2408 );
2409 g_count_rldt:=g_count_rldt+sql%rowcount;
2410
2411 DELETE_CHILD(l_qp_rltd_modifiers_rec.to_rltd_modifier_id,l_conc_request_id);
2412
2413 DELETE FROM QP_RLTD_MODIFIERS
2414 WHERE rltd_modifier_id = l_qp_rltd_modifiers_rec.rltd_modifier_id;
2415 END LOOP; -- Loop through rltd modifiers records
2416 END IF; --For lines that are Parent Price Break lines
2417 END LOOP; --Loop through l_mapping_tbl
2418 END IF; --If l_mapping_tbl has any records
2419
2420 /* Insert the count of records inserted into QP_ARCH_LIST_LINES,QP_ARCH_PRICING_ATTRIBUTES,QP_ARCH_QUALIFIERS,QP_ARCH_RLTD_MODIFIERS
2421 into QP_ARCH_ROW_COUNTS
2422 */
2423 insert into QP_ARCH_ROW_COUNTS (request_id,table_name,row_count) values (l_conc_request_id,'QP_ARCH_LIST_LINES',g_count_list_line);
2424 insert into QP_ARCH_ROW_COUNTS (request_id,table_name,row_count) values (l_conc_request_id,'QP_ARCH_PRICING_ATTRIBUTES',g_count_pricing_att);
2425 insert into QP_ARCH_ROW_COUNTS (request_id,table_name,row_count) values (l_conc_request_id,'QP_ARCH_QUALIFIERS',g_count_qualifier);
2426 insert into QP_ARCH_ROW_COUNTS (request_id,table_name,row_count) values (l_conc_request_id,'QP_ARCH_RLTD_MODIFIERS',g_count_rldt);
2427
2428 fnd_file.put_line(FND_FILE.LOG, 'Number of list header records inserted into QP_ARCH_LIST_HEADERS_B: '|| g_count_header_b);
2429 fnd_file.put_line(FND_FILE.LOG, 'Number of list header translation records inserted into QP_ARCH_LIST_HEADERS_TL: '|| g_count_header_tl);
2430 fnd_file.put_line(FND_FILE.LOG, 'Number of list header and line qualifiers inserted into QP_ARCH_QUALIFIERS: '|| g_count_qualifier);
2434
2431 fnd_file.put_line(FND_FILE.LOG, 'Number of list lines archived into QP_ARCH_LIST_LINES: '|| g_count_list_line);
2432 fnd_file.put_line(FND_FILE.LOG, 'Number of list line pricing attributes archived into QP_ARCH_PRICING_ATTRIBUTES: '|| g_count_pricing_att);
2433 fnd_file.put_line(FND_FILE.LOG, 'Number of related lines archived into QP_ARCH_RLTD_MODIFIERS: '|| g_count_rldt);
2435 IF (g_count_list_line > 0) THEN -- Invoke update_qualifiers only if number of list lines archived > 0.
2436 fnd_file.put_line(FND_FILE.LOG, 'Before calling QP_MAINTAIN_DENORMALIZED_DATA.UPDATE_QUALIFIERS.');
2437 /* This code will call the API to update the denormalized columns on QP_QUALIFIERS*/
2438 QP_MAINTAIN_DENORMALIZED_DATA.UPDATE_QUALIFIERS( ERR_BUFF => errbuf,
2439 RETCODE => retcode,
2440 P_LIST_HEADER_ID => p_entity);
2441 fnd_file.put_line(FND_FILE.LOG, 'After calling QP_MAINTAIN_DENORMALIZED_DATA.UPDATE_QUALIFIERS. Return code: '|| retcode);
2442
2443 IF retcode = 2 THEN
2444 fnd_file.put_line(FND_FILE.LOG,'Error in Update of denormalized columns in QP_Qualifiers');
2445 ELSE
2446 fnd_file.put_line(FND_FILE.LOG,'Update of denormalized columns in QP_Qualifiers completed successfully');
2447 END IF;
2448 END IF;
2452 fnd_file.put_line(FND_FILE.LOG, 'Number of list lines errored out and not processed: '|| l_err_count);
2449
2450 COMMIT;
2451
2453
2454 IF l_err_count = 0 THEN
2455 fnd_file.put_line(FND_FILE.LOG,'Pricing entity archive completed successfully');
2456
2457 errbuf := '';
2458 retcode := 0;
2459
2460 --Call INSERT_CRITERIA to insert records into QP_ARCH_CRITERIA_HEADERS and QP_ARCH_CRITERIA_LINES
2461 INSERT_CRITERIA(p_archive_name,p_entity_type,p_source_system_code,p_entity,p_all_lines,p_product_context,
2462 p_product_attribute,p_product_attr_value_from,p_product_attr_value_to,p_start_date_active,
2463 p_end_date_active,p_creation_date,p_created_by,l_user_id,l_conc_request_id,'S');
2464 ELSE
2465 fnd_file.put_line(FND_FILE.LOG,'Pricing entity archive completed successfully');
2466 fnd_file.put_line(FND_FILE.LOG,'A few lines matching the archive criteria were not archived');
2467
2468 errbuf := 'Few lines matching the criteria were not Archived';
2469 retcode := 0;
2470
2471 --Call INSERT_CRITERIA to insert records into QP_ARCH_CRITERIA_HEADERS and QP_ARCH_CRITERIA_LINES
2472 INSERT_CRITERIA(p_archive_name,p_entity_type,p_source_system_code,p_entity,p_all_lines,p_product_context,
2473 p_product_attribute,p_product_attr_value_from,p_product_attr_value_to,p_start_date_active,
2474 p_end_date_active,p_creation_date,p_created_by,l_user_id,l_conc_request_id,'S');
2475 END IF; --l_err_count
2476
2477 EXCEPTION
2478 WHEN NO_DATA_FOUND THEN
2479 fnd_file.put_line(FND_FILE.LOG,'Pricing entity archive completed with Warnings');
2480 fnd_file.put_line(FND_FILE.LOG,'No Data Found - 0 Records Archived');
2481
2482 errbuf := 'No Data Found - 0 Records Archived';
2483 retcode := 1;
2484
2485 --Call INSERT_CRITERIA to insert records into QP_ARCH_CRITERIA_HEADERS and QP_ARCH_CRITERIA_LINES
2486 INSERT_CRITERIA(p_archive_name,p_entity_type,p_source_system_code,p_entity,p_all_lines,p_product_context,
2487 p_product_attribute,p_product_attr_value_from,p_product_attr_value_to,p_start_date_active,
2488 p_end_date_active,p_creation_date,p_created_by,l_user_id,l_conc_request_id,'W');
2489 WHEN OTHERS THEN
2490 fnd_file.put_line(FND_FILE.LOG,'Error in Pricing entity archive Routine ');
2491 fnd_file.put_line(FND_FILE.LOG,substr(sqlerrm,1,300));
2492
2493 retcode := 2;
2494
2495 --Call INSERT_CRITERIA to insert records into QP_ARCH_CRITERIA_HEADERS and QP_ARCH_CRITERIA_LINES
2496 INSERT_CRITERIA(p_archive_name,p_entity_type,p_source_system_code,p_entity,p_all_lines,p_product_context,
2497 p_product_attribute,p_product_attr_value_from,p_product_attr_value_to,p_start_date_active,
2498 p_end_date_active,p_creation_date,p_created_by,l_user_id,l_conc_request_id,'F');
2499 END ARCHIVE_ENTITY;
2500 END QP_ARCHIVE_ENTITY_PVT;