[Home] [Help]
PACKAGE BODY: APPS.QP_ARCHIVE_ENTITY_PVT
Source
1 PACKAGE BODY QP_ARCHIVE_ENTITY_PVT AS
2 /* $Header: QPXARCVB.pls 120.2.12010000.2 2008/08/18 11:47:52 kdurgasi 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;
192
193 IF p_end_date_active is not null THEN
194
195 insert into QP_ARCH_CRITERIA_LINES
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,
386 modifier_level_code,
387 list_price,
388 primary_uom_flag,
389 inventory_item_id,
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
509 creation_date,
506
507 INSERT INTO QP_ARCH_PRICING_ATTRIBUTES
508 (pricing_attribute_id,
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,
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,
570 l_qp_pricing_attributes_rec.last_update_login,
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;
628
629 END LOOP; /* Cursor qp_pricing_attributes_cur LOOP */
630
631 END DELETE_CHILD;
632
633
634 /************************************************************************
635 *Procedure to Archive The Price list or the Modifier List *
639 PROCEDURE ARCHIVE_ENTITY
636 *************************************************************************/
637
638
640 (
641 errbuf OUT NOCOPY VARCHAR2,
642 retcode OUT NOCOPY NUMBER,
643 p_archive_name IN VARCHAR2,
644 p_entity_type IN VARCHAR2,
645 p_source_system_code IN VARCHAR2,
646 p_entity IN NUMBER,
647 p_all_lines IN VARCHAR2,
648 p_product_context IN VARCHAR2,
649 p_product_attribute IN VARCHAR2,
650 p_product_attr_value_from IN VARCHAR2,
651 p_product_attr_value_to IN VARCHAR2,
652 p_start_date_active IN VARCHAR2,
653 p_end_date_active IN VARCHAR2,
654 p_creation_date IN VARCHAR2,
655 p_created_by IN NUMBER,
656 p_segment1_lohi IN VARCHAR2,
657 p_segment2_lohi IN VARCHAR2,
658 p_segment3_lohi IN VARCHAR2,
659 p_segment4_lohi IN VARCHAR2,
660 p_segment5_lohi IN VARCHAR2,
661 p_segment6_lohi IN VARCHAR2,
662 p_segment7_lohi IN VARCHAR2,
663 p_segment8_lohi IN VARCHAR2,
664 p_segment9_lohi IN VARCHAR2,
665 p_segment10_lohi IN VARCHAR2,
666 p_segment11_lohi IN VARCHAR2,
667 p_segment12_lohi IN VARCHAR2,
668 p_segment13_lohi IN VARCHAR2,
669 p_segment14_lohi IN VARCHAR2,
670 p_segment15_lohi IN VARCHAR2,
671 p_segment16_lohi IN VARCHAR2,
672 p_segment17_lohi IN VARCHAR2,
673 p_segment18_lohi IN VARCHAR2,
674 p_segment19_lohi IN VARCHAR2,
675 p_segment20_lohi IN VARCHAR2
676 )
677 IS
678
679 l_conc_request_id NUMBER := -1;
680 l_conc_program_application_id NUMBER := -1;
681 l_conc_program_id NUMBER := -1;
682 l_conc_login_id NUMBER := -1;
683 l_user_id NUMBER := -1;
684
685 l_insert_flag varchar2(1);
686 l_cnt number:=0;
687 l_err_count number:=0;
688
689 TYPE qp_list_lines_rec IS RECORD (
690 list_line_id QP_LIST_LINES.list_line_id%TYPE,
691 creation_date QP_LIST_LINES.creation_date%TYPE,
692 created_by QP_LIST_LINES.created_by%TYPE,
693 last_update_date QP_LIST_LINES.last_update_date%TYPE,
694 last_updated_by QP_LIST_LINES.last_updated_by%TYPE,
695 last_update_login QP_LIST_LINES.last_update_login%TYPE,
696 program_application_id QP_LIST_LINES.program_application_id%TYPE,
697 program_id QP_LIST_LINES.program_id%TYPE,
698 program_update_date QP_LIST_LINES.program_update_date%TYPE,
699 request_id QP_LIST_LINES.request_id%TYPE,
700 list_header_id QP_LIST_LINES.list_header_id%TYPE,
701 list_line_type_code QP_LIST_LINES.list_line_type_code%TYPE,
702 automatic_flag QP_LIST_LINES.automatic_flag%TYPE,
703 modifier_level_code QP_LIST_LINES.modifier_level_code%TYPE,
704 list_price QP_LIST_LINES.list_price%TYPE,
705 primary_uom_flag QP_LIST_LINES.primary_uom_flag%TYPE,
706 inventory_item_id QP_LIST_LINES.inventory_item_id%TYPE,
707 organization_id QP_LIST_LINES.organization_id%TYPE,
708 related_item_id QP_LIST_LINES.related_item_id%TYPE,
709 relationship_type_id QP_LIST_LINES.relationship_type_id%TYPE,
710 substitution_context QP_LIST_LINES.substitution_context%TYPE,
711 substitution_attribute QP_LIST_LINES.substitution_attribute%TYPE,
712 substitution_value QP_LIST_LINES.substitution_value%TYPE,
713 revision QP_LIST_LINES.revision%TYPE,
714 revision_date QP_LIST_LINES.revision_date%TYPE,
715 revision_reason_code QP_LIST_LINES.revision_reason_code%TYPE,
716 context QP_LIST_LINES.context%TYPE,
717 attribute1 QP_LIST_LINES.attribute1%TYPE,
718 attribute2 QP_LIST_LINES.attribute2%TYPE,
719 comments QP_LIST_LINES.comments%TYPE,
720 attribute3 QP_LIST_LINES.attribute3%TYPE,
721 attribute4 QP_LIST_LINES.attribute4%TYPE,
722 attribute5 QP_LIST_LINES.attribute5%TYPE,
723 attribute6 QP_LIST_LINES.attribute6%TYPE,
724 attribute7 QP_LIST_LINES.attribute7%TYPE,
725 attribute8 QP_LIST_LINES.attribute8%TYPE,
726 attribute9 QP_LIST_LINES.attribute9%TYPE,
727 attribute10 QP_LIST_LINES.attribute10%TYPE,
728 attribute11 QP_LIST_LINES.attribute11%TYPE,
729 attribute12 QP_LIST_LINES.attribute12%TYPE,
730 attribute13 QP_LIST_LINES.attribute13%TYPE,
731 attribute14 QP_LIST_LINES.attribute14%TYPE,
732 attribute15 QP_LIST_LINES.attribute15%TYPE,
733 price_break_type_code QP_LIST_LINES.price_break_type_code%TYPE,
734 percent_price QP_LIST_LINES.percent_price%TYPE,
735 price_by_formula_id QP_LIST_LINES.price_by_formula_id%TYPE,
736 number_effective_periods QP_LIST_LINES.number_effective_periods%TYPE,
737 effective_period_uom QP_LIST_LINES.effective_period_uom%TYPE,
738 arithmetic_operator QP_LIST_LINES.arithmetic_operator%TYPE,
739 operand QP_LIST_LINES.operand%TYPE,
740 override_flag QP_LIST_LINES.override_flag%TYPE,
741 print_on_invoice_flag QP_LIST_LINES.print_on_invoice_flag%TYPE,
742 rebate_transaction_type_code QP_LIST_LINES.rebate_transaction_type_code%TYPE,
743 estim_accrual_rate QP_LIST_LINES.estim_accrual_rate%TYPE,
744 generate_using_formula_id QP_LIST_LINES.generate_using_formula_id%TYPE,
745 start_date_active QP_LIST_LINES.start_date_active%TYPE,
746 end_date_active QP_LIST_LINES.end_date_active%TYPE,
747 reprice_flag QP_LIST_LINES.reprice_flag%TYPE,
751 list_line_no QP_LIST_LINES.list_line_no%TYPE,
748 accrual_flag QP_LIST_LINES.accrual_flag%TYPE,
749 pricing_group_sequence QP_LIST_LINES.pricing_group_sequence%TYPE,
750 incompatibility_grp_code QP_LIST_LINES.incompatibility_grp_code%TYPE,
752 product_precedence QP_LIST_LINES.product_precedence%TYPE,
753 pricing_phase_id QP_LIST_LINES.pricing_phase_id%TYPE,
754 expiration_period_start_date QP_LIST_LINES.expiration_period_start_date%TYPE,
755 number_expiration_periods QP_LIST_LINES.number_expiration_periods%TYPE,
756 expiration_period_uom QP_LIST_LINES.expiration_period_uom%TYPE,
757 expiration_date QP_LIST_LINES.expiration_date%TYPE,
758 estim_gl_value QP_LIST_LINES.estim_gl_value%TYPE,
759 accrual_conversion_rate QP_LIST_LINES.accrual_conversion_rate%TYPE,
760 benefit_price_list_line_id QP_LIST_LINES.benefit_price_list_line_id%TYPE,
761 proration_type_code QP_LIST_LINES.proration_type_code%TYPE,
762 benefit_qty QP_LIST_LINES.benefit_qty%TYPE,
763 benefit_uom_code QP_LIST_LINES.benefit_uom_code%TYPE,
764 charge_type_code QP_LIST_LINES.charge_type_code%TYPE,
765 charge_subtype_code QP_LIST_LINES.charge_subtype_code%TYPE,
766 benefit_limit QP_LIST_LINES.benefit_limit%TYPE,
767 include_on_returns_flag QP_LIST_LINES.include_on_returns_flag%TYPE,
768 qualification_ind QP_LIST_LINES.qualification_ind%TYPE,
769 recurring_value QP_LIST_LINES.recurring_value%TYPE,
770 LIST_PRICE_UOM_CODE QP_LIST_LINES.LIST_PRICE_UOM_CODE%TYPE,
771 BASE_QTY QP_LIST_LINES.BASE_QTY%TYPE,
772 BASE_UOM_CODE QP_LIST_LINES.BASE_UOM_CODE%TYPE,
773 ACCRUAL_QTY QP_LIST_LINES.ACCRUAL_QTY%TYPE,
774 ACCRUAL_UOM_CODE QP_LIST_LINES.ACCRUAL_UOM_CODE%TYPE,
775 RECURRING_FLAG QP_LIST_LINES.RECURRING_FLAG%TYPE,
776 LIMIT_EXISTS_FLAG QP_LIST_LINES.LIMIT_EXISTS_FLAG%TYPE,
777 GROUP_COUNT QP_LIST_LINES.GROUP_COUNT%TYPE
778 ,NET_AMOUNT_FLAG QP_LIST_LINES.NET_AMOUNT_FLAG%TYPE
779 ,CUSTOMER_ITEM_ID QP_LIST_LINES.CUSTOMER_ITEM_ID%TYPE
780 ,ACCUM_CONTEXT QP_LIST_LINES.ACCUM_CONTEXT%TYPE
781 ,ACCUM_ATTRIBUTE QP_LIST_LINES.ACCUM_ATTRIBUTE%TYPE
782 ,ACCUM_ATTR_RUN_SRC_FLAG QP_LIST_LINES.ACCUM_ATTR_RUN_SRC_FLAG%TYPE
783 ,BREAK_UOM_CODE QP_LIST_LINES.BREAK_UOM_CODE%TYPE
784 ,BREAK_UOM_CONTEXT QP_LIST_LINES.BREAK_UOM_CONTEXT%TYPE
785 ,BREAK_UOM_ATTRIBUTE QP_LIST_LINES.BREAK_UOM_ATTRIBUTE%TYPE
786 ,PATTERN_ID QP_LIST_LINES.PATTERN_ID%TYPE
787 ,PRODUCT_UOM_CODE QP_LIST_LINES.PRODUCT_UOM_CODE%TYPE
788 ,PRICING_ATTRIBUTE_COUNT QP_LIST_LINES.PRICING_ATTRIBUTE_COUNT%TYPE
789 ,HASH_KEY QP_LIST_LINES.HASH_KEY%TYPE
790 ,CACHE_KEY QP_LIST_LINES.CACHE_KEY%TYPE
791 );
792
793 l_mapping_tbl mapping_tbl;
794 l_select_stmt VARCHAR2(9000);
795 l_qp_list_lines_rec QP_LIST_LINES_REC;
796 l_context VARCHAR2(30);
797 l_attribute VARCHAR2(30);
798 l_count NUMBER := 0;
799
800 l_pric_attr_value_from_number NUMBER := NULL;
801 l_pric_attr_value_to_number NUMBER := NULL;
802
803 l_list_type_code VARCHAR2(30) := '';
804 l_qual_attr_value_from_number NUMBER := NULL;
805 l_qual_attr_value_to_number NUMBER := NULL;
806 l_min_date date := to_date('01/01/1900','DD/MM/YYYY');
807 l_max_date date := to_date('31/12/9999','DD/MM/YYYY');
808
809 TYPE lines_cur_typ IS REF CURSOR;
810 qp_list_lines_cv lines_cur_typ;
811
812 CURSOR qp_pricing_attributes_cur(p_from_list_line_id NUMBER)
813 IS
814 SELECT *
815 FROM qp_pricing_attributes
816 WHERE list_line_id = p_from_list_line_id;
817
818 CURSOR qp_headers_qualifiers_cur(p_from_discount_header_id NUMBER)
819 IS
820 SELECT *
821 FROM qp_qualifiers
822 WHERE list_header_id = p_from_discount_header_id and
823 list_line_id = -1;
824
825 CURSOR qp_line_qualifiers_cur(p_from_discount_header_id NUMBER,
826 p_from_discount_line_id NUMBER)
827 IS
828 SELECT *
829 FROM qp_qualifiers
830 WHERE list_header_id = p_from_discount_header_id and
831 list_line_id = p_from_discount_line_id ;
832
833
834 /* First part of cursor qp_qualifiers_cur selects qualifiers while the second part
835 selects secondary price list */
836
837 CURSOR qp_qualifiers_cur(p_from_list_header_id NUMBER, p_context VARCHAR2,
838 p_attribute VARCHAR2)
839 IS
840 SELECT *
841 FROM qp_qualifiers q
842 WHERE (q.list_header_id = p_from_list_header_id AND
843 q.qualifier_attribute <> p_attribute AND
844 Exists (Select Null
845 From qp_list_headers_b a
846 Where a.list_header_id = p_from_list_header_id
847 And a.list_type_code = 'PRL'
848 )
849 )
850 OR
851 (q.qualifier_context = p_context AND
852 q.qualifier_attribute = p_attribute AND
853 q.qualifier_attr_value = TO_CHAR(p_from_list_header_id) AND
854 EXISTS (select null from qp_list_headers_b a
858
855 where a.list_header_id =q.list_header_id
856 And a.list_type_code = 'PRL')
857 );
859 CURSOR qp_rltd_modifiers_cur(a_list_line_id NUMBER)
860 IS
861 SELECT *
862 FROM qp_rltd_modifiers
863 WHERE from_rltd_modifier_id = a_list_line_id;
864
865 BEGIN
866
867 l_conc_request_id := FND_GLOBAL.CONC_REQUEST_ID;
868 l_conc_program_id := FND_GLOBAL.CONC_PROGRAM_ID;
869 l_user_id := FND_GLOBAL.USER_ID;
870 l_conc_login_id := FND_GLOBAL.CONC_LOGIN_ID;
871 l_conc_program_application_id := FND_GLOBAL.PROG_APPL_ID;
872
873 /* Validate number of list lines selected. If there are zero lines selected for the given search criteria, throw error message. */
874
875 l_select_stmt :=
876 'SELECT
877 q.list_line_id,
878 q.creation_date,
879 q.created_by,
880 q.last_update_date,
881 q.last_updated_by,
882 q.last_update_login,
883 q.program_application_id,
884 q.program_id,
885 q.program_update_date,
886 q.request_id,
887 q.list_header_id,
888 q.list_line_type_code,
889 q.automatic_flag,
890 q.modifier_level_code,
891 q.list_price,
892 q.primary_uom_flag,
893 q.inventory_item_id,
894 q.organization_id,
895 q.related_item_id,
896 q.relationship_type_id,
897 q.substitution_context,
898 q.substitution_attribute,
899 q.substitution_value,
900 q.revision,
901 q.revision_date,
902 q.revision_reason_code,
903 q.context,
904 q.attribute1,
905 q.attribute2,
906 q.comments,
907 q.attribute3,
908 q.attribute4,
909 q.attribute5,
910 q.attribute6,
911 q.attribute7,
912 q.attribute8,
913 q.attribute9,
914 q.attribute10,
915 q.attribute11,
916 q.attribute12,
917 q.attribute13,
918 q.attribute14,
919 q.attribute15,
920 q.price_break_type_code,
921 q.percent_price,
922 q.price_by_formula_id,
923 q.number_effective_periods,
924 q.effective_period_uom,
925 q.arithmetic_operator,
926 q.operand,
927 q.override_flag,
928 q.print_on_invoice_flag,
929 q.rebate_transaction_type_code,
930 q.estim_accrual_rate,
931 q.generate_using_formula_id,
932 q.start_date_active,
933 q.end_date_active,
934 q.reprice_flag,
935 q.accrual_flag,
936 q.pricing_group_sequence,
937 q.incompatibility_grp_code,
938 q.list_line_no,
939 q.product_precedence,
940 q.pricing_phase_id,
941 q.expiration_period_start_date,
942 q.number_expiration_periods,
943 q.expiration_period_uom,
944 q.expiration_date,
945 q.estim_gl_value,
946 q.accrual_conversion_rate,
947 q.benefit_price_list_line_id,
948 q.proration_type_code,
949 q.benefit_qty,
950 q.benefit_uom_code,
951 q.charge_type_code,
952 q.charge_subtype_code,
953 q.benefit_limit,
954 q.include_on_returns_flag,
955 q.qualification_ind,
956 q.recurring_value,
957 q.LIST_PRICE_UOM_CODE,
958 q.BASE_QTY,
959 q.BASE_UOM_CODE,
960 q.ACCRUAL_QTY,
961 q.ACCRUAL_UOM_CODE,
962 q.RECURRING_FLAG,
963 q.LIMIT_EXISTS_FLAG,
964 q.GROUP_COUNT
965 ,q.NET_AMOUNT_FLAG
966 ,q.CUSTOMER_ITEM_ID
967 ,q.ACCUM_CONTEXT
968 ,q.ACCUM_ATTRIBUTE
969 ,q.ACCUM_ATTR_RUN_SRC_FLAG
970 ,q.BREAK_UOM_CODE
971 ,q.BREAK_UOM_CONTEXT
972 ,q.BREAK_UOM_ATTRIBUTE
973 ,q.PATTERN_ID
974 ,q.PRODUCT_UOM_CODE
975 ,q.PRICING_ATTRIBUTE_COUNT
976 ,q.HASH_KEY
977 ,q.CACHE_KEY
978 FROM qp_list_lines q
979 WHERE q.list_header_id = :hdr
980 and nvl(trunc(q.start_date_active),fnd_date.canonical_to_date('''||fnd_date.date_to_canonical(l_min_date)||'''))
981 >=nvl(trunc(fnd_date.canonical_to_date(:sdat)),
982 nvl(trunc(q.start_date_active),fnd_date.canonical_to_date('''||fnd_date.date_to_canonical(l_min_date)||''')))
983 and nvl(trunc(q.end_date_active),fnd_date.canonical_to_date('''||fnd_date.date_to_canonical(l_max_date)||'''))
984 <=nvl(trunc(fnd_date.canonical_to_date(:edat)),
985 nvl(trunc(q.end_date_active),fnd_date.canonical_to_date('''||fnd_date.date_to_canonical(l_max_date)||''')))
986 and q.created_by=nvl(:usr,q.created_by)
987 and trunc(q.creation_date)=nvl(trunc(fnd_date.canonical_to_date(:cdat)),trunc(q.creation_date))';
988
989 /* Check whether all_lines check box is checked if yes then archive all the lines in the pricing entity */
990 IF nvl(p_all_lines,'N') = 'N' THEN
991 IF p_product_context is not NULL THEN
992 l_select_stmt := l_select_stmt || 'AND q.list_line_id IN
993 (SELECT DISTINCT a.list_line_id
994 FROM qp_pricing_attributes a
995 WHERE a.list_line_id = q.list_line_id
996 and EXCLUDER_FLAG =''N'''; --This would take care of Exculded Items.
997
1001 OR (p_segment5_lohi <> ''''' AND ''''') OR (p_segment6_lohi <> ''''' AND ''''')
998 IF p_product_attribute = 'PRICING_ATTRIBUTE1' THEN
999 IF (p_segment1_lohi <> ''''' AND ''''') OR (p_segment2_lohi <> ''''' AND ''''')
1000 OR (p_segment3_lohi <> ''''' AND ''''') OR (p_segment4_lohi <> ''''' AND ''''')
1002 OR (p_segment7_lohi <> ''''' AND ''''') OR (p_segment8_lohi <> ''''' AND ''''')
1003 OR (p_segment9_lohi <> ''''' AND ''''') OR (p_segment10_lohi <> ''''' AND ''''')
1004 OR (p_segment11_lohi <> ''''' AND ''''')
1005 OR (p_segment12_lohi <> ''''' AND ''''')
1006 OR (p_segment13_lohi <> ''''' AND ''''')
1007 OR (p_segment14_lohi <> ''''' AND ''''')
1008 OR (p_segment15_lohi <> ''''' AND ''''')
1009 OR (p_segment16_lohi <> ''''' AND ''''')
1010 OR (p_segment17_lohi <> ''''' AND ''''')
1011 OR (p_segment18_lohi <> ''''' AND ''''')
1012 OR (p_segment19_lohi <> ''''' AND ''''')
1013 OR (p_segment20_lohi <> ''''' AND ''''') THEN
1014 l_select_stmt := l_select_stmt ||
1015 'AND a.product_attribute_context = ''ITEM''
1016 AND a.product_attribute = ''PRICING_ATTRIBUTE1''
1017 AND EXISTS
1018 (SELECT ''X''
1019 FROM mtl_system_items m
1020 WHERE (m.inventory_item_id = TO_NUMBER(a.product_attr_value)) ';
1021
1022 IF (p_segment1_lohi <> ''''' AND ''''') THEN
1023 l_select_stmt := l_select_stmt ||
1024 'AND (m.segment1 BETWEEN ' || p_segment1_lohi || ') ';
1025 END IF;
1026 IF (p_segment2_lohi <> ''''' AND ''''') THEN
1027 l_select_stmt := l_select_stmt ||
1028 'AND (m.segment2 BETWEEN ' || p_segment2_lohi || ') ';
1029 END IF;
1030 IF (p_segment3_lohi <> ''''' AND ''''') THEN
1031 l_select_stmt := l_select_stmt ||
1032 'AND (m.segment3 BETWEEN ' || p_segment3_lohi || ') ';
1033 END IF;
1034 IF (p_segment4_lohi <> ''''' AND ''''') THEN
1035 l_select_stmt := l_select_stmt ||
1036 'AND (m.segment4 BETWEEN ' || p_segment4_lohi || ') ';
1037 END IF;
1038 IF (p_segment5_lohi <> ''''' AND ''''') THEN
1039 l_select_stmt := l_select_stmt ||
1040 'AND (m.segment5 BETWEEN ' || p_segment5_lohi || ') ';
1041 END IF;
1042 IF (p_segment6_lohi <> ''''' AND ''''') THEN
1043 l_select_stmt := l_select_stmt ||
1044 'AND (m.segment6 BETWEEN ' || p_segment6_lohi || ') ';
1045 END IF;
1046 IF (p_segment7_lohi <> ''''' AND ''''') THEN
1047 l_select_stmt := l_select_stmt ||
1048 'AND (m.segment7 BETWEEN ' || p_segment7_lohi || ') ';
1049 END IF;
1050 IF (p_segment8_lohi <> ''''' AND ''''') THEN
1051 l_select_stmt := l_select_stmt ||
1052 'AND (m.segment8 BETWEEN ' || p_segment8_lohi || ') ';
1053 END IF;
1054 IF (p_segment9_lohi <> ''''' AND ''''') THEN
1055 l_select_stmt := l_select_stmt ||
1056 'AND (m.segment9 BETWEEN ' || p_segment9_lohi || ') ';
1057 END IF;
1058 IF (p_segment10_lohi <> ''''' AND ''''') THEN
1059 l_select_stmt := l_select_stmt ||
1060 'AND (m.segment10 BETWEEN ' || p_segment10_lohi || ') ';
1061 END IF;
1062 IF (p_segment11_lohi <> ''''' AND ''''') THEN
1063 l_select_stmt := l_select_stmt ||
1064 'AND (m.segment10 BETWEEN ' || p_segment11_lohi || ') ';
1065 END IF;
1066 IF (p_segment12_lohi <> ''''' AND ''''') THEN
1067 l_select_stmt := l_select_stmt ||
1068 'AND (m.segment12 BETWEEN ' || p_segment12_lohi || ') ';
1069 END IF;
1070 IF (p_segment13_lohi <> ''''' AND ''''') THEN
1071 l_select_stmt := l_select_stmt ||
1072 'AND (m.segment13 BETWEEN ' || p_segment13_lohi || ') ';
1073 END IF;
1074 IF (p_segment14_lohi <> ''''' AND ''''') THEN
1075 l_select_stmt := l_select_stmt ||
1076 'AND (m.segment14 BETWEEN ' || p_segment14_lohi || ') ';
1077 END IF;
1078 IF (p_segment15_lohi <> ''''' AND ''''') THEN
1079 l_select_stmt := l_select_stmt ||
1080 'AND (m.segment15 BETWEEN ' || p_segment15_lohi || ') ';
1081 END IF;
1082 IF (p_segment16_lohi <> ''''' AND ''''') THEN
1083 l_select_stmt := l_select_stmt ||
1087 l_select_stmt := l_select_stmt ||
1084 'AND (m.segment16 BETWEEN ' || p_segment16_lohi || ') ';
1085 END IF;
1086 IF (p_segment17_lohi <> ''''' AND ''''') THEN
1088 'AND (m.segment17 BETWEEN ' || p_segment17_lohi || ') ';
1089 END IF;
1090 IF (p_segment18_lohi <> ''''' AND ''''') THEN
1091 l_select_stmt := l_select_stmt ||
1092 'AND (m.segment18 BETWEEN ' || p_segment18_lohi || ') ';
1093 END IF;
1094 IF (p_segment19_lohi <> ''''' AND ''''') THEN
1095 l_select_stmt := l_select_stmt ||
1096 'AND (m.segment19 BETWEEN ' || p_segment19_lohi || ') ';
1097 END IF;
1098 IF (p_segment20_lohi <> ''''' AND ''''') THEN
1099 l_select_stmt := l_select_stmt ||
1100 'AND (m.segment20 BETWEEN ' || p_segment20_lohi || ') ';
1101 END IF;
1102
1103 l_select_stmt := l_select_stmt || ') )';
1104 ELSE
1105 l_select_stmt := l_select_stmt || ') ';
1106 END IF;
1107
1108 ELSIF p_product_attribute = 'PRICING_ATTRIBUTE2' THEN
1109 IF (p_product_attr_value_from <> ''''' AND ''''') THEN
1110 --IF (p_product_attr_value_from is not null) THEN
1111 l_select_stmt := l_select_stmt ||
1112 'AND a.product_attribute_context = ''ITEM''
1113 AND a.product_attribute = ''PRICING_ATTRIBUTE2''
1114 AND a.product_attr_value = ''' || p_product_attr_value_from || ''') ';
1115 END IF;
1116 /*
1117 IF (p_segment1_lohi <> ''''' AND ''''') OR (p_segment2_lohi <> ''''' AND ''''')
1118 OR (p_segment3_lohi <> ''''' AND ''''') OR (p_segment4_lohi <> ''''' AND ''''')
1119 OR (p_segment5_lohi <> ''''' AND ''''') OR (p_segment6_lohi <> ''''' AND ''''')
1120 OR (p_segment7_lohi <> ''''' AND ''''') OR (p_segment8_lohi <> ''''' AND ''''')
1121 OR (p_segment9_lohi <> ''''' AND ''''') OR (p_segment10_lohi <> ''''' AND ''''')
1122 OR (p_segment11_lohi <> ''''' AND ''''')
1123 OR (p_segment12_lohi <> ''''' AND ''''')
1124 OR (p_segment13_lohi <> ''''' AND ''''')
1125 OR (p_segment14_lohi <> ''''' AND ''''')
1126 OR (p_segment15_lohi <> ''''' AND ''''')
1127 OR (p_segment16_lohi <> ''''' AND ''''')
1128 OR (p_segment17_lohi <> ''''' AND ''''')
1129 OR (p_segment18_lohi <> ''''' AND ''''')
1130 OR (p_segment19_lohi <> ''''' AND ''''')
1131 OR (p_segment20_lohi <> ''''' AND ''''') THEN
1132
1133 l_select_stmt := l_select_stmt ||
1134 'AND a.product_attribute_context = ''ITEM''
1135 AND a.product_attribute = ''PRICING_ATTRIBUTE2''
1136 AND EXISTS
1137 (SELECT ''X''
1138 FROM MTL_CATEGORIES m
1139 WHERE (m.CATEGORY_ID = TO_NUMBER(a.product_attr_value)) ';
1140
1141 IF (p_segment1_lohi <> ''''' AND ''''') THEN
1142 l_select_stmt := l_select_stmt ||
1143 'AND (m.segment1 BETWEEN ' || p_segment1_lohi || ') ';
1144 END IF;
1145 IF (p_segment2_lohi <> ''''' AND ''''') THEN
1146 l_select_stmt := l_select_stmt ||
1147 'AND (m.segment2 BETWEEN ' || p_segment2_lohi || ') ';
1148 END IF;
1149 IF (p_segment3_lohi <> ''''' AND ''''') THEN
1150 l_select_stmt := l_select_stmt ||
1151 'AND (m.segment3 BETWEEN ' || p_segment3_lohi || ') ';
1152 END IF;
1153 IF (p_segment4_lohi <> ''''' AND ''''') THEN
1154 l_select_stmt := l_select_stmt ||
1155 'AND (m.segment4 BETWEEN ' || p_segment4_lohi || ') ';
1156 END IF;
1157 IF (p_segment5_lohi <> ''''' AND ''''') THEN
1158 l_select_stmt := l_select_stmt ||
1159 'AND (m.segment5 BETWEEN ' || p_segment5_lohi || ') ';
1160 END IF;
1161 IF (p_segment6_lohi <> ''''' AND ''''') THEN
1162 l_select_stmt := l_select_stmt ||
1163 'AND (m.segment6 BETWEEN ' || p_segment6_lohi || ') ';
1164 END IF;
1165 IF (p_segment7_lohi <> ''''' AND ''''') THEN
1166 l_select_stmt := l_select_stmt ||
1167 'AND (m.segment7 BETWEEN ' || p_segment7_lohi || ') ';
1168 END IF;
1169 IF (p_segment8_lohi <> ''''' AND ''''') THEN
1170 l_select_stmt := l_select_stmt ||
1171 'AND (m.segment8 BETWEEN ' || p_segment8_lohi || ') ';
1172 END IF;
1173 IF (p_segment9_lohi <> ''''' AND ''''') THEN
1174 l_select_stmt := l_select_stmt ||
1178 l_select_stmt := l_select_stmt ||
1175 'AND (m.segment9 BETWEEN ' || p_segment9_lohi || ') ';
1176 END IF;
1177 IF (p_segment10_lohi <> ''''' AND ''''') THEN
1179 'AND (m.segment10 BETWEEN ' || p_segment10_lohi || ') ';
1180 END IF;
1181 IF (p_segment11_lohi <> ''''' AND ''''') THEN
1182 l_select_stmt := l_select_stmt ||
1183 'AND (m.segment10 BETWEEN ' || p_segment11_lohi || ') ';
1184 END IF;
1185 IF (p_segment12_lohi <> ''''' AND ''''') THEN
1186 l_select_stmt := l_select_stmt ||
1187 'AND (m.segment12 BETWEEN ' || p_segment12_lohi || ') ';
1188 END IF;
1189 IF (p_segment13_lohi <> ''''' AND ''''') THEN
1190 l_select_stmt := l_select_stmt ||
1191 'AND (m.segment13 BETWEEN ' || p_segment13_lohi || ') ';
1192 END IF;
1193 IF (p_segment14_lohi <> ''''' AND ''''') THEN
1194 l_select_stmt := l_select_stmt ||
1195 'AND (m.segment14 BETWEEN ' || p_segment14_lohi || ') ';
1196 END IF;
1197 IF (p_segment15_lohi <> ''''' AND ''''') THEN
1198 l_select_stmt := l_select_stmt ||
1199 'AND (m.segment15 BETWEEN ' || p_segment15_lohi || ') ';
1200 END IF;
1201 IF (p_segment16_lohi <> ''''' AND ''''') THEN
1202 l_select_stmt := l_select_stmt ||
1203 'AND (m.segment16 BETWEEN ' || p_segment16_lohi || ') ';
1204 END IF;
1205 IF (p_segment17_lohi <> ''''' AND ''''') THEN
1206 l_select_stmt := l_select_stmt ||
1207 'AND (m.segment17 BETWEEN ' || p_segment17_lohi || ') ';
1208 END IF;
1209 IF (p_segment18_lohi <> ''''' AND ''''') THEN
1210 l_select_stmt := l_select_stmt ||
1211 'AND (m.segment18 BETWEEN ' || p_segment18_lohi || ') ';
1212 END IF;
1213 IF (p_segment19_lohi <> ''''' AND ''''') THEN
1214 l_select_stmt := l_select_stmt ||
1215 'AND (m.segment19 BETWEEN ' || p_segment19_lohi || ') ';
1216 END IF;
1217 IF (p_segment20_lohi <> ''''' AND ''''') THEN
1218 l_select_stmt := l_select_stmt ||
1219 'AND (m.segment20 BETWEEN ' || p_segment20_lohi || ') ';
1220 END IF;
1221
1222 l_select_stmt := l_select_stmt || ') )';
1223 ELSE
1224 l_select_stmt := l_select_stmt || ') ';
1225 END IF;
1226 */
1227 ELSE
1228 l_select_stmt := l_select_stmt ||
1229 'AND a.product_attribute_context = ''ITEM''
1230 AND (a.product_attribute = ''' || p_product_attribute|| ''')
1231 AND (a.product_attr_value = ''' || p_product_attr_value_from|| ''') ';
1232 l_select_stmt := l_select_stmt || ') ';
1233 END IF; --Pricing Attribute end
1234 ELSE --Product_context is null
1235 -- changed the not in condition to in for the bug 7315038
1236 l_select_stmt := l_select_stmt || 'AND q.list_line_id in (SELECT DISTINCT a.list_line_id
1237 FROM qp_pricing_attributes a
1238 WHERE a.list_line_id = q.list_line_id)';
1239 END IF; -- Product_context end
1240 ELSE
1241 l_select_stmt := l_select_stmt || 'AND q.list_line_id not in (SELECT DISTINCT a.to_rltd_modifier_id
1242 FROM qp_rltd_modifiers a
1243 WHERE a.to_rltd_modifier_id = q.list_line_id)';
1244 END IF; --all_lines end
1245
1246 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;
1247 FETCH qp_list_lines_cv INTO l_qp_list_lines_rec;
1248 if(qp_list_lines_cv%NOTFOUND) THEN -- No list lines found satisfying the search criteria entered.
1249 CLOSE qp_list_lines_cv;
1250 RAISE NO_DATA_FOUND;
1251 end if;
1252 CLOSE qp_list_lines_cv;
1253
1254 /** Following code inserts pricing entity list header information into QP_ARCH_LIST_HEADERS_B **/
1255
1256 INSERT INTO QP_ARCH_LIST_HEADERS_B
1257 (
1258 list_header_id,
1259 creation_date,
1260 created_by,
1261 last_update_date,
1262 last_updated_by,
1263 last_update_login,
1264 program_application_id,
1265 program_id,
1266 program_update_date,
1267 request_id,
1268 list_type_code,
1269 start_date_active,
1270 end_date_active,
1271 automatic_flag,
1272 currency_code,
1273 rounding_factor,
1274 ship_method_code,
1275 freight_terms_code,
1276 terms_id,
1277 context,
1278 attribute1,
1279 attribute2,
1280 attribute3,
1281 attribute4,
1282 attribute5,
1283 attribute6,
1284 attribute7,
1285 attribute8,
1286 attribute9,
1287 attribute10,
1288 attribute11,
1289 attribute12,
1290 attribute13,
1291 attribute14,
1292 attribute15,
1293 comments,
1297 source_system_code,
1294 discount_lines_flag,
1295 gsa_indicator,
1296 prorate_flag,
1298 active_flag,
1299 parent_list_header_id,
1300 start_date_active_first,
1301 end_date_active_first,
1302 active_date_first_type,
1303 start_date_active_second,
1304 end_date_active_second,
1305 active_date_second_type,
1306 ask_for_flag,
1307 currency_header_id,
1308 pte_code,
1309 global_flag,
1310 orig_org_id,
1311 LIMIT_EXISTS_FLAG,
1312 MOBILE_DOWNLOAD,
1313 LIST_SOURCE_CODE,
1314 ORIG_SYSTEM_HEADER_REF,
1315 SHAREABLE_FLAG,
1316 SOLD_TO_ORG_ID,
1317 ARCH_PURG_REQUEST_ID
1318 )
1319 SELECT
1320 list_header_id,
1321 creation_date,
1322 created_by,
1323 last_update_date,
1324 last_updated_by,
1325 last_update_login,
1326 program_application_id,
1327 program_id,
1328 program_update_date,
1329 request_id,
1330 list_type_code,
1331 start_date_active,
1332 end_date_active,
1333 automatic_flag,
1334 currency_code,
1335 rounding_factor,
1336 ship_method_code,
1337 freight_terms_code,
1338 terms_id,
1339 context,
1340 attribute1,
1341 attribute2,
1342 attribute3,
1343 attribute4,
1344 attribute5,
1345 attribute6,
1346 attribute7,
1347 attribute8,
1348 attribute9,
1349 attribute10,
1350 attribute11,
1351 attribute12,
1352 attribute13,
1353 attribute14,
1354 attribute15,
1355 comments,
1356 discount_lines_flag,
1357 gsa_indicator,
1358 prorate_flag,
1359 source_system_code,
1360 active_flag,
1361 parent_list_header_id,
1362 start_date_active_first,
1363 end_date_active_first,
1364 active_date_first_type,
1365 start_date_active_second,
1366 end_date_active_second,
1367 active_date_second_type,
1368 ask_for_flag,
1369 currency_header_id,
1370 pte_code,
1371 global_flag,
1372 orig_org_id,
1373 LIMIT_EXISTS_FLAG,
1374 MOBILE_DOWNLOAD,
1375 LIST_SOURCE_CODE,
1376 ORIG_SYSTEM_HEADER_REF,
1377 SHAREABLE_FLAG,
1378 SOLD_TO_ORG_ID,
1379 l_conc_request_id
1380 FROM qp_list_headers_b
1381 WHERE list_header_id = p_entity;
1382
1383 --Insert the count of records from QP_ARCH_LIST_HEADERS_B into QP_ARCH_ROW_COUNTS
1384 g_count_header_b:=sql%rowcount;
1385 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);
1386
1387 /** Following code inserts pricing entity list header information into QP_ARCH_LIST_HEADERS_TL **/
1388 INSERT INTO QP_ARCH_LIST_HEADERS_TL
1389 (last_update_login,
1390 name,
1391 description,
1392 creation_date,
1393 created_by,
1394 last_update_date,
1395 last_updated_by,
1396 list_header_id,
1397 language,
1398 source_lang,
1399 version_no,
1400 ARCH_PURG_REQUEST_ID
1401 )
1402 SELECT
1403 last_update_login,
1404 name,
1405 description,
1406 creation_date,
1407 created_by,
1408 last_update_date,
1409 last_updated_by,
1410 list_header_id,
1411 language,
1412 source_lang,
1413 version_no,
1414 l_conc_request_id
1415 FROM qp_list_headers_tl
1416 WHERE list_header_id = p_entity;
1417
1418 --Insert the count of records from QP_ARCH_LIST_HEADERS_TL into QP_ARCH_ROW_COUNTS
1419 g_count_header_tl:=sql%rowcount;
1420 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);
1421
1422 /** Following code inserts pricing entity header qualifier information **/
1423 BEGIN
1424 SELECT LIST_TYPE_CODE
1425 INTO l_list_type_code
1426 FROM QP_LIST_HEADERS_B
1427 WHERE LIST_HEADER_ID = p_entity;
1428
1429 EXCEPTION
1430 WHEN OTHERS THEN
1431 NULL;
1432 END;
1433
1434 --Following code archives Price List header qualifier information
1435 IF l_list_type_code = 'PRL' THEN
1436 IF QP_UTIL.Attrmgr_Installed = 'Y' THEN
1437 QP_UTIL.Get_Context_Attribute('PRICE_LIST', l_context, l_attribute);
1438 ELSE
1439 QP_UTIL.Get_Context_Attribute('PRICE_LIST_ID', l_context, l_attribute);
1440 END IF;
1441
1442 FOR l_qp_qualifiers_rec IN qp_qualifiers_cur(p_entity, l_context,l_attribute)
1443 LOOP
1444 IF l_qp_qualifiers_rec.qualifier_datatype = 'N' THEN
1445 BEGIN
1446 l_qual_attr_value_from_number := qp_number.canonical_to_number(l_qp_qualifiers_rec.qualifier_attr_value);
1447 l_qual_attr_value_to_number := qp_number.canonical_to_number(l_qp_qualifiers_rec.qualifier_attr_value_to);
1448 EXCEPTION
1449 WHEN VALUE_ERROR THEN
1450 NULL;
1451 WHEN OTHERS THEN
1452 NULL;
1453 END;
1454 END IF;
1455
1456 --Insert into QP_ARCH_QUALIFIERS
1457 INSERT INTO QP_ARCH_QUALIFIERS
1458 (
1459 qualifier_id,
1460 creation_date,
1461 created_by,
1462 last_update_date,
1463 last_updated_by,
1464 last_update_login,
1465 program_application_id,
1466 program_id,
1467 program_update_date,
1468 request_id,
1469 excluder_flag,
1470 comparison_operator_code,
1471 qualifier_context,
1472 qualifier_attribute,
1473 context,
1474 attribute1,
1475 attribute2,
1479 attribute6,
1476 attribute3,
1477 attribute4,
1478 attribute5,
1480 attribute7,
1481 attribute8,
1482 attribute9,
1483 attribute10,
1484 attribute11,
1485 attribute12,
1486 attribute13,
1487 attribute14,
1488 attribute15,
1489 qualifier_rule_id,
1490 qualifier_grouping_no,
1491 qualifier_attr_value,
1492 list_header_id,
1493 list_line_id,
1494 created_from_rule_id,
1495 start_date_active,
1496 end_date_active,
1497 qualifier_precedence,
1498 qualifier_datatype,
1499 qualifier_attr_value_to,
1500 active_flag,
1501 list_type_code,
1502 qual_attr_value_from_number,
1503 qual_attr_value_to_number,
1504 search_ind,
1505 distinct_row_count,
1506 qualifier_group_cnt,
1507 header_quals_exist_flag,
1508 OTHERS_GROUP_CNT,
1509 SEGMENT_ID,
1510 ARCH_PURG_REQUEST_ID
1511 )
1512 VALUES
1513 (
1514 l_qp_qualifiers_rec.qualifier_id,
1515 l_qp_qualifiers_rec.creation_date,
1516 l_qp_qualifiers_rec.created_by,
1517 l_qp_qualifiers_rec.last_update_date,
1518 l_qp_qualifiers_rec.last_updated_by,
1519 l_qp_qualifiers_rec.last_update_login,
1520 l_qp_qualifiers_rec.program_application_id,
1521 l_qp_qualifiers_rec.program_id,
1522 l_qp_qualifiers_rec.program_update_date,
1523 l_qp_qualifiers_rec.request_id,
1524 l_qp_qualifiers_rec.excluder_flag,
1525 l_qp_qualifiers_rec.comparison_operator_code,
1526 l_qp_qualifiers_rec.qualifier_context,
1527 l_qp_qualifiers_rec.qualifier_attribute,
1528 l_qp_qualifiers_rec.context,
1529 l_qp_qualifiers_rec.attribute1,
1530 l_qp_qualifiers_rec.attribute2,
1531 l_qp_qualifiers_rec.attribute3,
1532 l_qp_qualifiers_rec.attribute4,
1533 l_qp_qualifiers_rec.attribute5,
1534 l_qp_qualifiers_rec.attribute6,
1535 l_qp_qualifiers_rec.attribute7,
1536 l_qp_qualifiers_rec.attribute8,
1537 l_qp_qualifiers_rec.attribute9,
1538 l_qp_qualifiers_rec.attribute10,
1539 l_qp_qualifiers_rec.attribute11,
1540 l_qp_qualifiers_rec.attribute12,
1541 l_qp_qualifiers_rec.attribute13,
1542 l_qp_qualifiers_rec.attribute14,
1543 l_qp_qualifiers_rec.attribute15,
1544 l_qp_qualifiers_rec.qualifier_rule_id,
1545 l_qp_qualifiers_rec.qualifier_grouping_no,
1546 l_qp_qualifiers_rec.qualifier_attr_value,
1547 l_qp_qualifiers_rec.list_header_id,
1548 l_qp_qualifiers_rec.list_line_id,
1549 l_qp_qualifiers_rec.created_from_rule_id,
1550 l_qp_qualifiers_rec.start_date_active,
1551 l_qp_qualifiers_rec.end_date_active,
1552 l_qp_qualifiers_rec.qualifier_precedence,
1553 l_qp_qualifiers_rec.qualifier_datatype,
1554 l_qp_qualifiers_rec.qualifier_attr_value_to,
1555 l_qp_qualifiers_rec.active_flag,
1556 l_qp_qualifiers_rec.list_type_code,
1557 l_qual_attr_value_from_number,
1558 l_qual_attr_value_to_number,
1559 l_qp_qualifiers_rec.search_ind,
1560 l_qp_qualifiers_rec.distinct_row_count,
1561 l_qp_qualifiers_rec.qualifier_group_cnt,
1562 l_qp_qualifiers_rec.header_quals_exist_flag,
1563 l_qp_qualifiers_rec.others_group_cnt,
1564 l_qp_qualifiers_rec.segment_id,
1565 l_conc_request_id
1566 );
1567
1568 g_count_qualifier := g_count_qualifier + sql%rowcount;
1569 END LOOP;
1570
1571 ELSE --Modifier and agreement price list
1572 --Following code archives the Modifier header qualifier information
1573 FOR l_qp_qualifiers_rec IN qp_headers_qualifiers_cur(p_entity)
1574 LOOP
1575 IF l_qp_qualifiers_rec.qualifier_datatype = 'N' THEN
1576 BEGIN
1577 l_qual_attr_value_from_number := qp_number.canonical_to_number(l_qp_qualifiers_rec.qualifier_attr_value);
1578 l_qual_attr_value_to_number := qp_number.canonical_to_number(l_qp_qualifiers_rec.qualifier_attr_value_to);
1579 EXCEPTION
1580 WHEN VALUE_ERROR THEN
1581 NULL;
1582 WHEN OTHERS THEN
1583 NULL;
1584 END;
1585 END IF;
1586
1587 --Insert into QP_ARCH_QUALIFIERS
1588 INSERT INTO QP_ARCH_QUALIFIERS
1589 (
1590 qualifier_id,
1591 creation_date,
1592 created_by,
1593 last_update_date,
1594 last_updated_by,
1595 last_update_login,
1596 program_application_id,
1597 program_id,
1598 program_update_date,
1599 request_id,
1600 excluder_flag,
1601 comparison_operator_code,
1602 qualifier_context,
1603 qualifier_attribute,
1604 context,
1605 attribute1,
1606 attribute2,
1607 attribute3,
1608 attribute4,
1609 attribute5,
1610 attribute6,
1611 attribute7,
1612 attribute8,
1613 attribute9,
1614 attribute10,
1615 attribute11,
1616 attribute12,
1617 attribute13,
1618 attribute14,
1619 attribute15,
1620 qualifier_rule_id,
1621 qualifier_grouping_no,
1622 qualifier_attr_value,
1623 list_header_id,
1627 end_date_active,
1624 list_line_id,
1625 created_from_rule_id,
1626 start_date_active,
1628 qualifier_precedence,
1629 qualifier_datatype,
1630 qualifier_attr_value_to,
1631 active_flag,
1632 list_type_code,
1633 qual_attr_value_from_number,
1634 qual_attr_value_to_number,
1635 search_ind,
1636 distinct_row_count,
1637 qualifier_group_cnt,
1638 header_quals_exist_flag,
1639 OTHERS_GROUP_CNT,
1640 segment_id,
1641 ARCH_PURG_REQUEST_ID
1642 )
1643 VALUES
1644 (
1645 l_qp_qualifiers_rec.qualifier_id,
1646 l_qp_qualifiers_rec.creation_date,
1647 l_qp_qualifiers_rec.created_by,
1648 l_qp_qualifiers_rec.last_update_date,
1649 l_qp_qualifiers_rec.last_updated_by,
1650 l_qp_qualifiers_rec.last_update_login,
1651 l_qp_qualifiers_rec.program_application_id,
1652 l_qp_qualifiers_rec.program_id,
1653 l_qp_qualifiers_rec.program_update_date,
1654 l_qp_qualifiers_rec.request_id,
1655 l_qp_qualifiers_rec.excluder_flag,
1656 l_qp_qualifiers_rec.comparison_operator_code,
1657 l_qp_qualifiers_rec.qualifier_context,
1658 l_qp_qualifiers_rec.qualifier_attribute,
1659 l_qp_qualifiers_rec.context,
1660 l_qp_qualifiers_rec.attribute1,
1661 l_qp_qualifiers_rec.attribute2,
1662 l_qp_qualifiers_rec.attribute3,
1663 l_qp_qualifiers_rec.attribute4,
1664 l_qp_qualifiers_rec.attribute5,
1665 l_qp_qualifiers_rec.attribute6,
1666 l_qp_qualifiers_rec.attribute7,
1667 l_qp_qualifiers_rec.attribute8,
1668 l_qp_qualifiers_rec.attribute9,
1669 l_qp_qualifiers_rec.attribute10,
1670 l_qp_qualifiers_rec.attribute11,
1671 l_qp_qualifiers_rec.attribute12,
1672 l_qp_qualifiers_rec.attribute13,
1673 l_qp_qualifiers_rec.attribute14,
1674 l_qp_qualifiers_rec.attribute15,
1675 l_qp_qualifiers_rec.qualifier_rule_id,
1676 l_qp_qualifiers_rec.qualifier_grouping_no,
1677 l_qp_qualifiers_rec.qualifier_attr_value,
1678 l_qp_qualifiers_rec.list_header_id,
1679 l_qp_qualifiers_rec.list_line_id,
1680 l_qp_qualifiers_rec.created_from_rule_id,
1681 l_qp_qualifiers_rec.start_date_active,
1682 l_qp_qualifiers_rec.end_date_active,
1683 l_qp_qualifiers_rec.qualifier_precedence,
1684 l_qp_qualifiers_rec.qualifier_datatype,
1685 l_qp_qualifiers_rec.qualifier_attr_value_to,
1686 l_qp_qualifiers_rec.active_flag,
1687 l_qp_qualifiers_rec.list_type_code,
1688 l_qual_attr_value_from_number,
1689 l_qual_attr_value_to_number,
1690 l_qp_qualifiers_rec.search_ind,
1691 l_qp_qualifiers_rec.distinct_row_count,
1692 l_qp_qualifiers_rec.qualifier_group_cnt,
1693 l_qp_qualifiers_rec.header_quals_exist_flag,
1694 l_qp_qualifiers_rec.others_group_cnt,
1695 l_qp_qualifiers_rec.segment_id,
1696 l_conc_request_id
1697 );
1698
1699 g_count_qualifier := g_count_qualifier + sql%rowcount;
1700 END LOOP;
1701
1702 END IF;
1703
1704 /** Following code Archives price list lines information.**/
1705 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;
1706 LOOP
1707 FETCH qp_list_lines_cv INTO l_qp_list_lines_rec;
1708 EXIT WHEN qp_list_lines_cv%NOTFOUND;
1709
1710 l_insert_flag := 'N'; --Reset the flag
1711
1712 --Check if limit exists
1713 IF nvl(l_qp_list_lines_rec.LIMIT_EXISTS_FLAG,'N') = 'N' THEN
1714 l_insert_flag :='Y';
1715
1716 -- Check if line part of a formula
1717 SELECT count(*) into l_cnt
1718 FROM QP_PRICE_FORMULA_LINES
1719 WHERE PRICE_FORMULA_LINE_TYPE_CODE = 'PLL'
1720 AND PRICE_LIST_LINE_ID=l_qp_list_lines_rec.list_line_id;
1721
1722 IF l_cnt=0 THEN
1723 l_insert_flag :='Y';
1724 ELSE
1725 l_insert_flag :='N';
1726 fnd_file.put_line(FND_FILE.LOG,'Price List line used in formula.Do not archive : '||l_qp_list_lines_rec.list_line_id);
1727 l_err_count := l_err_count+1;
1728 END IF;
1729
1730 IF l_insert_flag ='Y' THEN
1731 --Check if line a Coupon or a Benefit line or a parent to the same
1732 IF l_list_type_code = 'PRO' or l_list_type_code = 'DEL' THEN
1733 IF (l_qp_list_lines_rec.list_line_type_code = 'CIE') OR
1734 LINE_EXISTS_IN_RLTD(l_qp_list_lines_rec.list_line_id) THEN
1735 l_insert_flag :='N';
1736 fnd_file.put_line(FND_FILE.LOG,'COUPON or related BENEFIT line.Do not archive : '||l_qp_list_lines_rec.list_line_id);
1737 l_err_count := l_err_count+1;
1738 END IF;
1739 END IF;
1740 END IF;
1741
1742 IF l_insert_flag ='Y' THEN
1743 INSERT INTO QP_ARCH_LIST_LINES
1744 (
1745 list_line_id,
1746 creation_date,
1747 created_by,
1748 last_update_date,
1749 last_updated_by,
1750 last_update_login,
1751 program_application_id,
1752 program_id,
1753 program_update_date,
1754 request_id,
1755 list_header_id,
1756 list_line_type_code,
1760 modifier_level_code,
1757 start_date_active,
1758 end_date_active,
1759 automatic_flag,
1761 list_price,
1762 primary_uom_flag,
1763 inventory_item_id,
1764 organization_id,
1765 related_item_id,
1766 relationship_type_id,
1767 substitution_context,
1768 substitution_attribute,
1769 substitution_value,
1770 revision,
1771 revision_date,
1772 revision_reason_code,
1773 context,
1774 attribute1,
1775 attribute2,
1776 comments,
1777 attribute3,
1778 attribute4,
1779 attribute5,
1780 attribute6,
1781 attribute7,
1782 attribute8,
1783 attribute9,
1784 attribute10,
1785 attribute11,
1786 attribute12,
1787 attribute13,
1788 attribute14,
1789 attribute15,
1790 price_break_type_code,
1791 percent_price,
1792 price_by_formula_id,
1793 number_effective_periods,
1794 effective_period_uom,
1795 arithmetic_operator,
1796 operand,
1797 override_flag,
1798 print_on_invoice_flag,
1799 rebate_transaction_type_code,
1800 estim_accrual_rate,
1801 generate_using_formula_id,
1802 reprice_flag,
1803 accrual_flag,
1804 pricing_group_sequence,
1805 incompatibility_grp_code,
1806 list_line_no,
1807 product_precedence,
1808 pricing_phase_id,
1809 expiration_period_start_date,
1810 number_expiration_periods,
1811 expiration_period_uom,
1812 expiration_date,
1813 estim_gl_value,
1814 accrual_conversion_rate,
1815 benefit_price_list_line_id,
1816 proration_type_code,
1817 benefit_qty,
1818 benefit_uom_code,
1819 charge_type_code,
1820 charge_subtype_code,
1821 benefit_limit,
1822 include_on_returns_flag,
1823 qualification_ind,
1824 recurring_value,
1825 LIST_PRICE_UOM_CODE,
1826 BASE_QTY,
1827 BASE_UOM_CODE,
1828 ACCRUAL_QTY,
1829 ACCRUAL_UOM_CODE,
1830 RECURRING_FLAG,
1831 LIMIT_EXISTS_FLAG,
1832 GROUP_COUNT,
1833 NET_AMOUNT_FLAG,
1834 CUSTOMER_ITEM_ID
1835 ,ACCUM_CONTEXT
1836 ,ACCUM_ATTRIBUTE
1837 ,ACCUM_ATTR_RUN_SRC_FLAG
1838 ,BREAK_UOM_CODE
1839 ,BREAK_UOM_CONTEXT
1840 ,BREAK_UOM_ATTRIBUTE
1841 ,PATTERN_ID
1842 ,PRODUCT_UOM_CODE
1843 ,PRICING_ATTRIBUTE_COUNT
1844 ,HASH_KEY
1845 ,CACHE_KEY
1846 ,ARCH_PURG_REQUEST_ID
1847 )
1848 VALUES
1849 (
1850 l_qp_list_lines_rec.list_line_id,
1851 l_qp_list_lines_rec.creation_date,
1852 l_qp_list_lines_rec.created_by,
1853 l_qp_list_lines_rec.last_update_date,
1854 l_qp_list_lines_rec.last_updated_by,
1855 l_qp_list_lines_rec.last_update_login,
1856 l_qp_list_lines_rec.program_application_id,
1857 l_qp_list_lines_rec.program_id,
1858 l_qp_list_lines_rec.program_update_date,
1859 l_qp_list_lines_rec.request_id,
1860 p_entity,
1861 l_qp_list_lines_rec.list_line_type_code,
1862 l_qp_list_lines_rec.start_date_active,
1863 l_qp_list_lines_rec.end_date_active,
1864 l_qp_list_lines_rec.automatic_flag,
1865 l_qp_list_lines_rec.modifier_level_code,
1866 l_qp_list_lines_rec.list_price,
1867 l_qp_list_lines_rec.primary_uom_flag,
1868 l_qp_list_lines_rec.inventory_item_id,
1869 l_qp_list_lines_rec.organization_id,
1870 l_qp_list_lines_rec.related_item_id,
1871 l_qp_list_lines_rec.relationship_type_id,
1872 l_qp_list_lines_rec.substitution_context,
1873 l_qp_list_lines_rec.substitution_attribute,
1874 l_qp_list_lines_rec.substitution_value,
1875 l_qp_list_lines_rec.revision,
1876 l_qp_list_lines_rec.revision_date,
1877 l_qp_list_lines_rec.revision_reason_code,
1878 l_qp_list_lines_rec.context,
1879 l_qp_list_lines_rec.attribute1,
1880 l_qp_list_lines_rec.attribute2,
1881 l_qp_list_lines_rec.comments,
1882 l_qp_list_lines_rec.attribute3,
1883 l_qp_list_lines_rec.attribute4,
1884 l_qp_list_lines_rec.attribute5,
1885 l_qp_list_lines_rec.attribute6,
1886 l_qp_list_lines_rec.attribute7,
1887 l_qp_list_lines_rec.attribute8,
1888 l_qp_list_lines_rec.attribute9,
1889 l_qp_list_lines_rec.attribute10,
1890 l_qp_list_lines_rec.attribute11,
1891 l_qp_list_lines_rec.attribute12,
1892 l_qp_list_lines_rec.attribute13,
1893 l_qp_list_lines_rec.attribute14,
1894 l_qp_list_lines_rec.attribute15,
1895 l_qp_list_lines_rec.price_break_type_code,
1896 l_qp_list_lines_rec.percent_price,
1897 l_qp_list_lines_rec.price_by_formula_id,
1901 l_qp_list_lines_rec.operand,
1898 l_qp_list_lines_rec.number_effective_periods,
1899 l_qp_list_lines_rec.effective_period_uom,
1900 l_qp_list_lines_rec.arithmetic_operator,
1902 l_qp_list_lines_rec.override_flag,
1903 l_qp_list_lines_rec.print_on_invoice_flag,
1904 l_qp_list_lines_rec.rebate_transaction_type_code,
1905 l_qp_list_lines_rec.estim_accrual_rate,
1906 l_qp_list_lines_rec.generate_using_formula_id,
1907 l_qp_list_lines_rec.reprice_flag,
1908 l_qp_list_lines_rec.accrual_flag,
1909 l_qp_list_lines_rec.pricing_group_sequence,
1910 l_qp_list_lines_rec.incompatibility_grp_code,
1911 l_qp_list_lines_rec.list_line_no,
1912 l_qp_list_lines_rec.product_precedence,
1913 l_qp_list_lines_rec.pricing_phase_id,
1914 l_qp_list_lines_rec.expiration_period_start_date,
1915 l_qp_list_lines_rec.number_expiration_periods,
1916 l_qp_list_lines_rec.expiration_period_uom,
1917 l_qp_list_lines_rec.expiration_date,
1918 l_qp_list_lines_rec.estim_gl_value,
1919 l_qp_list_lines_rec.accrual_conversion_rate,
1920 l_qp_list_lines_rec.benefit_price_list_line_id,
1921 l_qp_list_lines_rec.proration_type_code,
1922 l_qp_list_lines_rec.benefit_qty,
1923 l_qp_list_lines_rec.benefit_uom_code,
1924 l_qp_list_lines_rec.charge_type_code,
1925 l_qp_list_lines_rec.charge_subtype_code,
1926 l_qp_list_lines_rec.benefit_limit,
1927 l_qp_list_lines_rec.include_on_returns_flag,
1928 l_qp_list_lines_rec.qualification_ind,
1929 l_qp_list_lines_rec.recurring_value,
1930 l_qp_list_lines_rec.LIST_PRICE_UOM_CODE,
1931 l_qp_list_lines_rec.BASE_QTY,
1932 l_qp_list_lines_rec.BASE_UOM_CODE,
1933 l_qp_list_lines_rec.ACCRUAL_QTY,
1934 l_qp_list_lines_rec.ACCRUAL_UOM_CODE,
1935 l_qp_list_lines_rec.RECURRING_FLAG,
1936 l_qp_list_lines_rec.LIMIT_EXISTS_FLAG,
1937 l_qp_list_lines_rec.GROUP_COUNT,
1938 l_qp_list_lines_rec.NET_AMOUNT_FLAG,
1939 l_qp_list_lines_rec.CUSTOMER_ITEM_ID,
1940 l_qp_list_lines_rec.ACCUM_CONTEXT,
1941 l_qp_list_lines_rec.ACCUM_ATTRIBUTE,
1942 l_qp_list_lines_rec.ACCUM_ATTR_RUN_SRC_FLAG,
1943 l_qp_list_lines_rec.BREAK_UOM_CODE,
1944 l_qp_list_lines_rec.BREAK_UOM_CONTEXT,
1945 l_qp_list_lines_rec.BREAK_UOM_ATTRIBUTE,
1946 l_qp_list_lines_rec.PATTERN_ID ,
1947 l_qp_list_lines_rec.PRODUCT_UOM_CODE,
1948 l_qp_list_lines_rec.PRICING_ATTRIBUTE_COUNT,
1949 l_qp_list_lines_rec.HASH_KEY ,
1950 l_qp_list_lines_rec.CACHE_KEY,
1951 l_conc_request_id
1952 );
1953
1954 g_count_list_line:=g_count_list_line + sql%rowcount;
1955
1956 /*If the list_line_rec is a Price Break Parent Line or a promotional or other item discount parent line then
1957 store the list line id in a mapping-array for later use*/
1958
1959 IF l_qp_list_lines_rec.list_line_type_code = 'PBH' OR
1960 l_qp_list_lines_rec.list_line_type_code = 'OID' OR
1961 l_qp_list_lines_rec.list_line_type_code = 'PRG' THEN
1962
1963 l_count := l_count + 1;
1964 l_mapping_tbl(l_count).list_line_type_code := l_qp_list_lines_rec.list_line_type_code;
1965 l_mapping_tbl(l_count).list_line_id := l_qp_list_lines_rec.list_line_id;
1966 END IF;
1967
1968 DELETE FROM QP_LIST_LINES WHERE list_line_id = l_qp_list_lines_rec.list_line_id and list_header_id = p_entity;
1969
1970 /*Insert line level qualifiers in case of Modifiers */
1971 IF l_list_type_code NOT IN ('PRL','AGR') THEN
1972 IF l_qp_list_lines_rec.list_line_id is not null AND
1973 l_qp_list_lines_rec.list_line_id <> -1 THEN
1974
1975 FOR l_qp_qualifiers_rec IN qp_line_qualifiers_cur(p_entity,l_qp_list_lines_rec.list_line_id)
1976 LOOP
1977 IF l_qp_qualifiers_rec.qualifier_datatype = 'N' then
1978 BEGIN
1979 l_qual_attr_value_from_number := qp_number.canonical_to_number(l_qp_qualifiers_rec.qualifier_attr_value);
1980 l_qual_attr_value_to_number := qp_number.canonical_to_number(l_qp_qualifiers_rec.qualifier_attr_value_to);
1981 EXCEPTION
1982 WHEN VALUE_ERROR THEN
1983 NULL;
1984 WHEN OTHERS THEN
1985 NULL;
1986 END;
1987 END IF;
1988
1989 --Insert into qp_qualifiers
1990 INSERT INTO QP_ARCH_QUALIFIERS
1991 (
1992 qualifier_id,
1993 creation_date,
1994 created_by,
1995 last_update_date,
1996 last_updated_by,
1997 last_update_login,
1998 program_application_id,
1999 program_id,
2000 program_update_date,
2001 request_id,
2002 excluder_flag,
2003 comparison_operator_code,
2004 qualifier_context,
2005 qualifier_attribute,
2006 context,
2007 attribute1,
2008 attribute2,
2012 attribute6,
2009 attribute3,
2010 attribute4,
2011 attribute5,
2013 attribute7,
2014 attribute8,
2015 attribute9,
2016 attribute10,
2017 attribute11,
2018 attribute12,
2019 attribute13,
2020 attribute14,
2021 attribute15,
2022 qualifier_rule_id,
2023 qualifier_grouping_no,
2024 qualifier_attr_value,
2025 list_header_id,
2026 list_line_id,
2027 created_from_rule_id,
2028 start_date_active,
2029 end_date_active,
2030 qualifier_precedence,
2031 qualifier_datatype,
2032 qualifier_attr_value_to,
2033 active_flag,
2034 list_type_code,
2035 qual_attr_value_from_number,
2036 qual_attr_value_to_number,
2037 search_ind,
2038 distinct_row_count,
2039 qualifier_group_cnt,
2040 header_quals_exist_flag,
2041 OTHERS_GROUP_CNT,
2042 segment_id,
2043 ARCH_PURG_REQUEST_ID
2044 )
2045 VALUES
2046 (
2047 l_qp_qualifiers_rec.qualifier_id,
2048 l_qp_qualifiers_rec.creation_date,
2049 l_qp_qualifiers_rec.created_by,
2050 l_qp_qualifiers_rec.last_update_date,
2051 l_qp_qualifiers_rec.last_updated_by,
2052 l_qp_qualifiers_rec.last_update_login,
2053 l_qp_qualifiers_rec.program_application_id,
2054 l_qp_qualifiers_rec.program_id,
2055 l_qp_qualifiers_rec.program_update_date,
2056 l_qp_qualifiers_rec.request_id,
2057 l_qp_qualifiers_rec.excluder_flag,
2058 l_qp_qualifiers_rec.comparison_operator_code,
2059 l_qp_qualifiers_rec.qualifier_context,
2060 l_qp_qualifiers_rec.qualifier_attribute,
2061 l_qp_qualifiers_rec.context,
2062 l_qp_qualifiers_rec.attribute1,
2063 l_qp_qualifiers_rec.attribute2,
2064 l_qp_qualifiers_rec.attribute3,
2065 l_qp_qualifiers_rec.attribute4,
2066 l_qp_qualifiers_rec.attribute5,
2067 l_qp_qualifiers_rec.attribute6,
2068 l_qp_qualifiers_rec.attribute7,
2069 l_qp_qualifiers_rec.attribute8,
2070 l_qp_qualifiers_rec.attribute9,
2071 l_qp_qualifiers_rec.attribute10,
2072 l_qp_qualifiers_rec.attribute11,
2073 l_qp_qualifiers_rec.attribute12,
2074 l_qp_qualifiers_rec.attribute13,
2075 l_qp_qualifiers_rec.attribute14,
2076 l_qp_qualifiers_rec.attribute15,
2077 l_qp_qualifiers_rec.qualifier_rule_id,
2078 l_qp_qualifiers_rec.qualifier_grouping_no,
2079 l_qp_qualifiers_rec.qualifier_attr_value,
2080 l_qp_qualifiers_rec.list_header_id,
2081 l_qp_qualifiers_rec.list_line_id,
2082 l_qp_qualifiers_rec.created_from_rule_id,
2083 l_qp_qualifiers_rec.start_date_active,
2084 l_qp_qualifiers_rec.end_date_active,
2085 l_qp_qualifiers_rec.qualifier_precedence,
2086 l_qp_qualifiers_rec.qualifier_datatype,
2087 l_qp_qualifiers_rec.qualifier_attr_value_to,
2088 l_qp_qualifiers_rec.active_flag,
2089 l_qp_qualifiers_rec.list_type_code,
2090 l_qual_attr_value_from_number,
2091 l_qual_attr_value_to_number,
2092 l_qp_qualifiers_rec.search_ind,
2093 l_qp_qualifiers_rec.distinct_row_count,
2094 l_qp_qualifiers_rec.qualifier_group_cnt,
2095 l_qp_qualifiers_rec.header_quals_exist_flag,
2096 l_qp_qualifiers_rec.others_group_cnt,
2097 l_qp_qualifiers_rec.segment_id,
2098 l_conc_request_id
2099 );
2100
2101 g_count_qualifier := g_count_qualifier + sql%rowcount;
2102
2103 DELETE FROM qp_qualifiers
2104 WHERE qualifier_id = l_qp_qualifiers_rec.qualifier_id
2105 and list_header_id = l_qp_qualifiers_rec.list_header_id
2106 and list_line_id = l_qp_qualifiers_rec.list_line_id;
2107 END LOOP;
2108
2109 END IF;
2110 END IF; -- List type code end
2111
2112 /* Archive List lines pricing attributes */
2113 FOR l_qp_pricing_attributes_rec IN qp_pricing_attributes_cur (l_qp_list_lines_rec.list_line_id) LOOP
2114 IF l_qp_pricing_attributes_rec.pricing_attribute_datatype = 'N' then
2118 EXCEPTION
2115 BEGIN
2116 l_pric_attr_value_from_number := qp_number.canonical_to_number(l_qp_pricing_attributes_rec.pricing_attr_value_from);
2117 l_pric_attr_value_to_number := qp_number.canonical_to_number(l_qp_pricing_attributes_rec.pricing_attr_value_to);
2119 WHEN VALUE_ERROR THEN
2120 NULL;
2121 WHEN OTHERS THEN
2122 NULL;
2123 END;
2124 END IF;
2125
2126 -- Insert into QP_ARCH_PRICING_ATTRIBUTES
2127 INSERT INTO QP_ARCH_PRICING_ATTRIBUTES
2128 (
2129 pricing_attribute_id,
2130 creation_date,
2131 created_by,
2132 last_update_date,
2133 last_updated_by,
2134 last_update_login,
2135 program_application_id,
2136 program_id,
2137 program_update_date,
2138 request_id,
2139 list_line_id,
2140 list_header_id,
2141 pricing_phase_id,
2142 qualification_ind,
2143 excluder_flag,
2144 accumulate_flag,
2145 product_attribute_context,
2146 product_attribute,
2147 product_attr_value,
2148 product_uom_code,
2149 pricing_attribute_context,
2150 pricing_attribute,
2151 pricing_attr_value_from,
2152 pricing_attr_value_to,
2153 attribute_grouping_no,
2154 context,
2155 attribute1,
2156 attribute2,
2157 attribute3,
2158 attribute4,
2159 attribute5,
2160 attribute6,
2161 attribute7,
2162 attribute8,
2163 attribute9,
2164 attribute10,
2165 attribute11,
2166 attribute12,
2167 attribute13,
2168 attribute14,
2169 attribute15,
2170 product_attribute_datatype,
2171 pricing_attribute_datatype,
2172 comparison_operator_code,
2173 pricing_attr_value_from_number,
2174 pricing_attr_value_to_number,
2175 DISTINCT_ROW_COUNT,
2176 SEARCH_IND,
2177 PATTERN_VALUE_FROM_POSITIVE,
2178 PATTERN_VALUE_TO_POSITIVE,
2179 PATTERN_VALUE_FROM_NEGATIVE,
2180 PATTERN_VALUE_TO_NEGATIVE,
2181 PRODUCT_SEGMENT_ID,
2182 PRICING_SEGMENT_ID,
2183 ARCH_PURG_REQUEST_ID
2184 )
2185 VALUES
2186 (
2187 l_qp_pricing_attributes_rec.pricing_attribute_id,
2188 l_qp_pricing_attributes_rec.creation_date,
2189 l_qp_pricing_attributes_rec.created_by,
2190 l_qp_pricing_attributes_rec.last_update_date,
2191 l_qp_pricing_attributes_rec.last_updated_by,
2192 l_qp_pricing_attributes_rec.last_update_login,
2193 l_qp_pricing_attributes_rec.program_application_id,
2194 l_qp_pricing_attributes_rec.program_id,
2195 l_qp_pricing_attributes_rec.program_update_date,
2196 l_qp_pricing_attributes_rec.request_id,
2197 l_qp_list_lines_rec.list_line_id,
2198 p_entity,
2199 l_qp_pricing_attributes_rec.pricing_phase_id,
2200 l_qp_pricing_attributes_rec.qualification_ind,
2201 l_qp_pricing_attributes_rec.excluder_flag,
2202 l_qp_pricing_attributes_rec.accumulate_flag,
2203 l_qp_pricing_attributes_rec.product_attribute_context,
2204 l_qp_pricing_attributes_rec.product_attribute,
2205 l_qp_pricing_attributes_rec.product_attr_value,
2206 l_qp_pricing_attributes_rec.product_uom_code,
2207 l_qp_pricing_attributes_rec.pricing_attribute_context,
2208 l_qp_pricing_attributes_rec.pricing_attribute,
2209 l_qp_pricing_attributes_rec.pricing_attr_value_from,
2210 l_qp_pricing_attributes_rec.pricing_attr_value_to,
2211 l_qp_pricing_attributes_rec.attribute_grouping_no,
2212 l_qp_pricing_attributes_rec.context,
2213 l_qp_pricing_attributes_rec.attribute1,
2214 l_qp_pricing_attributes_rec.attribute2,
2215 l_qp_pricing_attributes_rec.attribute3,
2216 l_qp_pricing_attributes_rec.attribute4,
2217 l_qp_pricing_attributes_rec.attribute5,
2218 l_qp_pricing_attributes_rec.attribute6,
2219 l_qp_pricing_attributes_rec.attribute7,
2220 l_qp_pricing_attributes_rec.attribute8,
2221 l_qp_pricing_attributes_rec.attribute9,
2222 l_qp_pricing_attributes_rec.attribute10,
2223 l_qp_pricing_attributes_rec.attribute11,
2224 l_qp_pricing_attributes_rec.attribute12,
2225 l_qp_pricing_attributes_rec.attribute13,
2226 l_qp_pricing_attributes_rec.attribute14,
2227 l_qp_pricing_attributes_rec.attribute15,
2228 l_qp_pricing_attributes_rec.product_attribute_datatype,
2229 l_qp_pricing_attributes_rec.pricing_attribute_datatype,
2230 l_qp_pricing_attributes_rec.comparison_operator_code,
2231 l_pric_attr_value_from_number,
2232 l_pric_attr_value_to_number,
2233 l_qp_pricing_attributes_rec.DISTINCT_ROW_COUNT,
2234 l_qp_pricing_attributes_rec.SEARCH_IND,
2235 l_qp_pricing_attributes_rec.PATTERN_VALUE_FROM_POSITIVE,
2239 l_qp_pricing_attributes_rec.PRODUCT_SEGMENT_ID,
2236 l_qp_pricing_attributes_rec.PATTERN_VALUE_TO_POSITIVE,
2237 l_qp_pricing_attributes_rec.PATTERN_VALUE_FROM_NEGATIVE,
2238 l_qp_pricing_attributes_rec.PATTERN_VALUE_TO_NEGATIVE,
2240 l_qp_pricing_attributes_rec.PRICING_SEGMENT_ID,
2241 l_conc_request_id
2242 );
2243
2244 g_count_pricing_att := g_count_pricing_att + sql%rowcount;
2245
2246 DELETE FROM qp_pricing_attributes
2247 WHERE pricing_attribute_id = l_qp_pricing_attributes_rec.pricing_attribute_id
2248 AND list_line_id = l_qp_list_lines_rec.list_line_id and list_header_id = p_entity;
2249
2250 END LOOP; /* Cursor qp_pricing_attributes_cur LOOP */
2251
2252 END IF; --Insert flag
2253 ELSE --Limit exists
2254 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);
2255 l_err_count := l_err_count+1;
2256 END IF;
2257 END LOOP; /* Cursor qp_list_lines_cv LOOP */
2258
2259 CLOSE qp_list_lines_cv;
2260
2261 /* Archive the qp_rltd_modifiers for the Price Break Parent list_lines chosen
2262 above which are stored in the mapping table */
2263
2264 IF l_mapping_tbl.COUNT > 0 THEN
2265 FOR l_count IN 1..l_mapping_tbl.COUNT
2266 LOOP
2267 IF l_mapping_tbl(l_count).list_line_type_code = 'PBH' OR
2268 l_mapping_tbl(l_count).list_line_type_code = 'OID' OR
2269 l_mapping_tbl(l_count).list_line_type_code = 'PRG' THEN
2270
2271 FOR l_qp_rltd_modifiers_rec IN qp_rltd_modifiers_cur(l_mapping_tbl(l_count).list_line_id)
2272 LOOP
2273 INSERT INTO QP_ARCH_RLTD_MODIFIERS
2274 (
2275 creation_date,
2276 created_by,
2277 last_update_date,
2278 last_updated_by,
2279 last_update_login,
2280 context,
2281 attribute1,
2282 attribute2,
2283 attribute3,
2284 attribute4,
2285 attribute5,
2286 attribute6,
2287 attribute7,
2288 attribute8,
2289 attribute9,
2290 attribute10,
2291 attribute11,
2292 attribute12,
2293 attribute13,
2294 attribute14,
2295 attribute15,
2296 rltd_modifier_id,
2297 rltd_modifier_grp_no,
2298 from_rltd_modifier_id,
2299 to_rltd_modifier_id,
2300 rltd_modifier_grp_type,
2301 ARCH_PURG_REQUEST_ID
2302 )
2303 VALUES
2304 (
2305 l_qp_rltd_modifiers_rec.creation_date,
2306 l_qp_rltd_modifiers_rec.created_by,
2307 l_qp_rltd_modifiers_rec.last_update_date,
2308 l_qp_rltd_modifiers_rec.last_updated_by,
2309 l_qp_rltd_modifiers_rec.last_update_login,
2310 l_qp_rltd_modifiers_rec.context,
2311 l_qp_rltd_modifiers_rec.attribute1,
2312 l_qp_rltd_modifiers_rec.attribute2,
2313 l_qp_rltd_modifiers_rec.attribute3,
2314 l_qp_rltd_modifiers_rec.attribute4,
2315 l_qp_rltd_modifiers_rec.attribute5,
2316 l_qp_rltd_modifiers_rec.attribute6,
2317 l_qp_rltd_modifiers_rec.attribute7,
2318 l_qp_rltd_modifiers_rec.attribute8,
2319 l_qp_rltd_modifiers_rec.attribute9,
2320 l_qp_rltd_modifiers_rec.attribute10,
2321 l_qp_rltd_modifiers_rec.attribute11,
2322 l_qp_rltd_modifiers_rec.attribute12,
2323 l_qp_rltd_modifiers_rec.attribute13,
2324 l_qp_rltd_modifiers_rec.attribute14,
2325 l_qp_rltd_modifiers_rec.attribute15,
2326 l_qp_rltd_modifiers_rec.rltd_modifier_id,
2327 l_qp_rltd_modifiers_rec.rltd_modifier_grp_no,
2328 l_qp_rltd_modifiers_rec.from_rltd_modifier_id,
2329 l_qp_rltd_modifiers_rec.to_rltd_modifier_id,
2330 l_qp_rltd_modifiers_rec.rltd_modifier_grp_type,
2331 l_conc_request_id
2332 );
2333 g_count_rldt:=g_count_rldt+sql%rowcount;
2334
2335 DELETE_CHILD(l_qp_rltd_modifiers_rec.to_rltd_modifier_id,l_conc_request_id);
2336
2337 DELETE FROM QP_RLTD_MODIFIERS
2338 WHERE rltd_modifier_id = l_qp_rltd_modifiers_rec.rltd_modifier_id;
2339 END LOOP; -- Loop through rltd modifiers records
2340 END IF; --For lines that are Parent Price Break lines
2341 END LOOP; --Loop through l_mapping_tbl
2342 END IF; --If l_mapping_tbl has any records
2343
2344 /* Insert the count of records inserted into QP_ARCH_LIST_LINES,QP_ARCH_PRICING_ATTRIBUTES,QP_ARCH_QUALIFIERS,QP_ARCH_RLTD_MODIFIERS
2345 into QP_ARCH_ROW_COUNTS
2346 */
2347 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);
2348 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);
2349 insert into QP_ARCH_ROW_COUNTS (request_id,table_name,row_count) values (l_conc_request_id,'QP_ARCH_QUALIFIERS',g_count_qualifier);
2353 fnd_file.put_line(FND_FILE.LOG, 'Number of list header translation records inserted into QP_ARCH_LIST_HEADERS_TL: '|| g_count_header_tl);
2350 insert into QP_ARCH_ROW_COUNTS (request_id,table_name,row_count) values (l_conc_request_id,'QP_ARCH_RLTD_MODIFIERS',g_count_rldt);
2351
2352 fnd_file.put_line(FND_FILE.LOG, 'Number of list header records inserted into QP_ARCH_LIST_HEADERS_B: '|| g_count_header_b);
2354 fnd_file.put_line(FND_FILE.LOG, 'Number of list header and line qualifiers inserted into QP_ARCH_QUALIFIERS: '|| g_count_qualifier);
2355 fnd_file.put_line(FND_FILE.LOG, 'Number of list lines archived into QP_ARCH_LIST_LINES: '|| g_count_list_line);
2356 fnd_file.put_line(FND_FILE.LOG, 'Number of list line pricing attributes archived into QP_ARCH_PRICING_ATTRIBUTES: '|| g_count_pricing_att);
2357 fnd_file.put_line(FND_FILE.LOG, 'Number of related lines archived into QP_ARCH_RLTD_MODIFIERS: '|| g_count_rldt);
2358
2359 IF (g_count_list_line > 0) THEN -- Invoke update_qualifiers only if number of list lines archived > 0.
2360 fnd_file.put_line(FND_FILE.LOG, 'Before calling QP_MAINTAIN_DENORMALIZED_DATA.UPDATE_QUALIFIERS.');
2361 /* This code will call the API to update the denormalized columns on QP_QUALIFIERS*/
2362 QP_MAINTAIN_DENORMALIZED_DATA.UPDATE_QUALIFIERS( ERR_BUFF => errbuf,
2363 RETCODE => retcode,
2364 P_LIST_HEADER_ID => p_entity);
2365 fnd_file.put_line(FND_FILE.LOG, 'After calling QP_MAINTAIN_DENORMALIZED_DATA.UPDATE_QUALIFIERS. Return code: '|| retcode);
2366
2367 IF retcode = 2 THEN
2368 fnd_file.put_line(FND_FILE.LOG,'Error in Update of denormalized columns in QP_Qualifiers');
2369 ELSE
2370 fnd_file.put_line(FND_FILE.LOG,'Update of denormalized columns in QP_Qualifiers completed successfully');
2371 END IF;
2372 END IF;
2373
2374 COMMIT;
2375
2376 fnd_file.put_line(FND_FILE.LOG, 'Number of list lines errored out and not processed: '|| l_err_count);
2377
2378 IF l_err_count = 0 THEN
2379 fnd_file.put_line(FND_FILE.LOG,'Pricing entity archive completed successfully');
2380
2381 errbuf := '';
2382 retcode := 0;
2383
2384 --Call INSERT_CRITERIA to insert records into QP_ARCH_CRITERIA_HEADERS and QP_ARCH_CRITERIA_LINES
2385 INSERT_CRITERIA(p_archive_name,p_entity_type,p_source_system_code,p_entity,p_all_lines,p_product_context,
2386 p_product_attribute,p_product_attr_value_from,p_product_attr_value_to,p_start_date_active,
2387 p_end_date_active,p_creation_date,p_created_by,l_user_id,l_conc_request_id,'S');
2388 ELSE
2389 fnd_file.put_line(FND_FILE.LOG,'Pricing entity archive completed successfully');
2390 fnd_file.put_line(FND_FILE.LOG,'A few lines matching the archive criteria were not archived');
2391
2392 errbuf := 'Few lines matching the criteria were not Archived';
2393 retcode := 0;
2394
2395 --Call INSERT_CRITERIA to insert records into QP_ARCH_CRITERIA_HEADERS and QP_ARCH_CRITERIA_LINES
2396 INSERT_CRITERIA(p_archive_name,p_entity_type,p_source_system_code,p_entity,p_all_lines,p_product_context,
2397 p_product_attribute,p_product_attr_value_from,p_product_attr_value_to,p_start_date_active,
2398 p_end_date_active,p_creation_date,p_created_by,l_user_id,l_conc_request_id,'S');
2399 END IF; --l_err_count
2400
2401 EXCEPTION
2402 WHEN NO_DATA_FOUND THEN
2403 fnd_file.put_line(FND_FILE.LOG,'Pricing entity archive completed with Warnings');
2404 fnd_file.put_line(FND_FILE.LOG,'No Data Found - 0 Records Archived');
2405
2406 errbuf := 'No Data Found - 0 Records Archived';
2407 retcode := 1;
2408
2409 --Call INSERT_CRITERIA to insert records into QP_ARCH_CRITERIA_HEADERS and QP_ARCH_CRITERIA_LINES
2410 INSERT_CRITERIA(p_archive_name,p_entity_type,p_source_system_code,p_entity,p_all_lines,p_product_context,
2411 p_product_attribute,p_product_attr_value_from,p_product_attr_value_to,p_start_date_active,
2412 p_end_date_active,p_creation_date,p_created_by,l_user_id,l_conc_request_id,'W');
2413 WHEN OTHERS THEN
2414 fnd_file.put_line(FND_FILE.LOG,'Error in Pricing entity archive Routine ');
2415 fnd_file.put_line(FND_FILE.LOG,substr(sqlerrm,1,300));
2416
2417 retcode := 2;
2418
2419 --Call INSERT_CRITERIA to insert records into QP_ARCH_CRITERIA_HEADERS and QP_ARCH_CRITERIA_LINES
2420 INSERT_CRITERIA(p_archive_name,p_entity_type,p_source_system_code,p_entity,p_all_lines,p_product_context,
2421 p_product_attribute,p_product_attr_value_from,p_product_attr_value_to,p_start_date_active,
2422 p_end_date_active,p_creation_date,p_created_by,l_user_id,l_conc_request_id,'F');
2423 END ARCHIVE_ENTITY;
2424 END QP_ARCHIVE_ENTITY_PVT;